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

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EMIS.ViewModel.ChargeManage.ArrearsSituation;
using EMIS.DataLogic.Repositories;
using System.Linq.Expressions;
using EMIS.Entities;
using EMIS.ViewModel;
namespace EMIS.DataLogic.ChargeManage.ArrearsSituation
{
    public class ArrearsListDAL
    {
        public StudentChargeRepository StudentChargeRepository { get; set; }
        public StudentChargePaymentRepository StudentChargePaymentRepository { get; set; }
        public ChargeDelayRepository ChargeDelayRepository { 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（左连缴费信息表、缓交信息表，欠费信息全部查询）
        /// 注：目前欠费金额的计算不包括通过缓交申请的缓交金额
        /// </summary>
        /// <param name="expStudentCharge"></param>
        /// <param name="expChargeDelay"></param>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<ArrearsListView> GetArrearListView(Expression<Func<EC_StudentCharge, bool>> expStudentCharge,
                Expression<Func<EC_ChargeDelay, bool>> expChargeDelay, Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from stugr in
                            (
                                from gr in
                                    (
                                        from sc in StudentChargeRepository.GetList(expStudentCharge)
                                        join stucd in
                                            (
                                                    from cd in ChargeDelayRepository.GetList(expChargeDelay)
                                                    group cd by cd.StudentChargeID into g
                                                    select new
                                                    {
                                                        StudentChargeID = g.Key,
                                                        DelayAmount = g.Sum(x => x.DelayAmount)
                                                    }
                                            )
                                        on sc.StudentChargeID equals stucd.StudentChargeID
                                        into tempcd
                                        from schcdPass in tempcd.DefaultIfEmpty()
                                        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,
                                            DelayAmount = schcdPass.DelayAmount ?? 0,
                                            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),
                                    DelayAmount = g.Sum(x => x.DelayAmount),
                                    PaymentAmount = g.Sum(x => x.PaymentAmount)
                                }
                            )
                        join stu in StudentRepository.GetList(expStudent)
                        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()
                        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,
                            DelayAmount = stugr.DelayAmount,
                            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（左连缴费信息表、缓交信息表，只查询欠费金额大于0的欠费信息，成绩录入、毕业申请中需考虑）
        /// 注：目前欠费金额的计算不包括通过缓交申请的缓交金额
        /// </summary>
        /// <param name="expStudentCharge"></param>
        /// <param name="expChargeDelay"></param>
        /// <returns></returns>
        public IQueryable<ArrearsListView> GetArrearListViewAble(Expression<Func<EC_StudentCharge, bool>> expStudentCharge,
                Expression<Func<EC_ChargeDelay, bool>> expChargeDelay)
        {
            var query = from stugr in
                            (
                                from gr in
                                    (
                                        from sc in StudentChargeRepository.GetList(expStudentCharge)
                                        join stucd in
                                            (
                                                    from cd in ChargeDelayRepository.GetList(expChargeDelay)
                                                    group cd by cd.StudentChargeID into g
                                                    select new
                                                    {
                                                        StudentChargeID = g.Key,
                                                        DelayAmount = g.Sum(x => x.DelayAmount)
                                                    }
                                            )
                                        on sc.StudentChargeID equals stucd.StudentChargeID
                                        into tempcd
                                        from schcdPass in tempcd.DefaultIfEmpty()
                                        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,
                                            DelayAmount = schcdPass.DelayAmount ?? 0,
                                            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),
                                    DelayAmount = g.Sum(x => x.DelayAmount),
                                    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,
                            DelayAmount = stugr.DelayAmount,
                            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> GetPassStudentView(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;
        }

    }
}