JW/EMiS.DataLogic/ChargeManage/ArrearsSituation/ChargeControlDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EMIS.DataLogic.Repositories;
using EMIS.ViewModel.ChargeManage.ArrearsSituation;
using System.Linq.Expressions;
using EMIS.Entities;
using EMIS.ViewModel;

namespace EMIS.DataLogic.ChargeManage.ArrearsSituation
{
    public class ChargeControlDAL
    {
        public StudentChargeRepository StudentChargeRepository { get; set; }
        public StudentChargePaymentRepository StudentChargePaymentRepository { get; set; }
        public PassStudentRepository PassStudentRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }

        /// <summary>
        /// 查询对应的欠费信息View（左连缴费信息表，只查询欠费金额大于0的欠费信息，不需考虑相应的缓交信息）
        /// 注：目前欠费金额的计算不包括通过缓交申请的缓交金额
        /// </summary>
        /// <param name="expStudentCharge"></param>
        /// <returns></returns>
        public IQueryable<ArrearsListView> GetArrearListViewAble(Expression<Func<EC_StudentCharge, bool>> expStudentCharge)
        {
            var query = from stugr in
                            (
                                from gr in
                                    (
                                        from sc in StudentChargeRepository.GetList(expStudentCharge)
                                        join stucp in
                                            (
                                                    from cp in StudentChargePaymentRepository.Entities
                                                    group cp by cp.StudentChargeID into g
                                                    select new
                                                    {
                                                        StudentChargeID = g.Key,
                                                        PaymentAmount = g.Sum(x => x.Amount)
                                                    }
                                            )
                                        on sc.StudentChargeID equals stucp.StudentChargeID
                                        into tempcp
                                        from schcp in tempcp.DefaultIfEmpty()
                                        select new
                                        {
                                            StudentChargeID = sc.StudentChargeID,
                                            UserID = sc.UserID,
                                            ChargeYear = sc.ChargeYear,
                                            ChargeProjectID = sc.ChargeProjectID,
                                            Amount = sc.Amount,
                                            ChargeAmount = sc.ActualAmount - sc.Amount,
                                            ActualAmount = sc.ActualAmount,
                                            PaymentAmount = schcp.PaymentAmount ?? 0
                                        }
                                    )
                                group gr by gr.UserID into g
                                select new
                                {
                                    UserID = g.Key,
                                    Amount = g.Sum(x => x.Amount),
                                    ChargeAmount = g.Sum(x => x.ChargeAmount),
                                    ActualAmount = g.Sum(x => x.ActualAmount),
                                    PaymentAmount = g.Sum(x => x.PaymentAmount)
                                }
                            )
                        join stu in StudentRepository.Entities
                        on stugr.UserID equals stu.UserID
                        join cl in ClassmajorRepository.Entities
                        on stu.ClassmajorID equals cl.ClassmajorID
                        join gr in GrademajorRepository.Entities
                        on cl.GrademajorID equals gr.GrademajorID
                        join fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        join us in UserRepository.Entities
                        on stu.UserID equals us.UserID
                        join pass in PassStudentRepository.Entities
                        on stugr.UserID equals pass.UserID
                        into temppass
                        from stuPass in temppass.DefaultIfEmpty()
                        where (stugr.ActualAmount - stugr.PaymentAmount) > 0
                        select new ArrearsListView
                        {
                            UserID = stugr.UserID,
                            StudentNo = us.LoginID,
                            UserName = us.Name,
                            SexID = stu.SexID,
                            InSchoolStatus = stu.InSchoolStatusID,
                            StudentStatus = stu.StudentStatus,
                            ClassID = stu.ClassmajorID,
                            ClassNo = cl.No,
                            ClassName = cl.Name,
                            GradeMajorID = cl.GrademajorID,
                            GradeMajorCode = gr.Code,
                            GrademajorStr = gr.Name,
                            GradeStr = gr.GradeID,
                            CollegeID = fa.CollegeID,
                            CollegeCode = fa.CF_College.No,
                            CollegeStr = fa.CF_College.Name,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            Amount = stugr.Amount,
                            ChargeAmount = stugr.ChargeAmount,
                            ActualAmount = stugr.ActualAmount,
                            PaidAmount = stugr.PaymentAmount,
                            IsArrear = (stugr.PaymentAmount - stugr.ActualAmount) >= 0 ? 0 : 1,
                            AmountSum = stugr.ActualAmount - stugr.PaymentAmount,
                            IsControl = stuPass.UserID == null ? 1 : 0
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的控制放行信息View
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<PassStudentView> GetPassStudentViewAble(Expression<Func<EC_PassStudent, bool>> exp)
        {
            var query = from stuPass in PassStudentRepository.GetList(exp)
                        join us in UserRepository.Entities
                        on stuPass.UserID equals us.UserID
                        select new PassStudentView
                        {
                            PassStudentID = stuPass.PassStudentID,
                            UserID = stuPass.UserID,
                            LoginID = us.LoginID,
                            Name = us.Name,
                            Remark = stuPass.Remark,
                            RecordStatus = stuPass.RecordStatus,
                            CreateUserID = stuPass.CreateUserID,
                            CreateTime = stuPass.CreateTime,
                            ModifyUserID = stuPass.ModifyUserID,
                            ModifyTime = stuPass.ModifyTime
                        };

            return query;
        }







        /// <summary>
        /// 此处有误，后续修复。。。
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<ArrearsListView> GetReatkePlanListViewAble(Expression<Func<EC_StudentCharge, bool>> exp)
        {
            var query = from a in StudentChargeRepository.GetList(exp)
                        join b in StudentChargePaymentRepository.Entities
                        on a.StudentChargeID equals b.StudentChargeID
                        into c
                        from p in c.DefaultIfEmpty()
                        where (a.ActualAmount - (p.Amount == null ? 0 : p.Amount)) > 0 || (a.ActualAmount == 0 && a.Amount == 0)
                        select new ArrearsListView
                        {
                            StudentChargeID = a.StudentChargeID,
                            UserName = a.CF_Student.Sys_User.Name,
                            StudentNo = a.CF_Student.Sys_User.LoginID,
                            ClassName = a.CF_Student.CF_Classmajor.Name,
                            UserID = a.UserID,
                            AmountSum = a.ActualAmount - (p.Amount == null ? 0 : p.Amount)
                        };

            return query;
        }

        
        /// <summary>
        /// 此处有误，后续修复。。。
        /// </summary>
        /// <param name="exp"></param>
        /// <param name="year"></param>
        /// <returns></returns>
        public IQueryable<decimal> GetRetakeArrearsAmountView(Expression<Func<CF_Student, bool>> exp, int year)
        {
            var query = from student in StudentRepository.GetList(exp)
                        join charge in StudentChargeRepository.GetList(x => x.ChargeTag == (int)EC_ChargeTag.Retake)
                            on new { UserID = (Guid?)student.UserID, ChargeYear = (int?)year } equals new { charge.UserID, charge.ChargeYear } 
                            into dcharge 
                        from charge in dcharge.DefaultIfEmpty()
                        join payment in StudentChargePaymentRepository.Entities on charge.StudentChargeID equals payment.StudentChargeID
                        into dpayment
                        from payment in dpayment.DefaultIfEmpty()
                        where ((charge.ActualAmount ?? 0) - (payment.Amount ?? 0)) > 0 || (charge.StudentChargeID == null) || (charge.Amount == 0)
                        group new { Charge = charge, Payment = payment } by student.UserID into g
                        select g.Sum(x => (x.Charge.StudentChargeID == null) ? 0 : (x.Charge.ActualAmount ?? 0) - (x.Charge.Amount ?? 0));
            return query;
        }
    }
}