JW/EMiS.DataLogic/ChargeManage/ChargeSituation/ChargeDelayDAL.cs

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

namespace EMIS.DataLogic.ChargeManage.ChargeSituation
{
    public class ChargeDelayDAL
    {
        public ChargeDelayRepository ChargeDelayRepository { get; set; }
        public StudentChargeRepository StudentChargeRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public ChargeStandardRepository ChargeStandardRepository { get; set; }
        public StudentChargePaymentRepository StudentChargePaymentRepository { get; set; }
        public ChargeProjectRepository ChargeProjectRepository { get; set; }

        /// <summary>
        /// 查询缓交名单信息View（关联应收名单、学生缴费）
        /// </summary>
        /// <param name="expChargeDelay"></param>
        /// <param name="expStudentCharge"></param>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<ChargeDelayView> GetChargeDelayQueryAble(Expression<Func<EC_ChargeDelay, bool>> expChargeDelay, 
                Expression<Func<EC_StudentCharge, bool>> expStudentCharge, Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from cd in ChargeDelayRepository.GetList(expChargeDelay)
                        join sc in StudentChargeRepository.GetList(expStudentCharge)
                        on cd.StudentChargeID equals sc.StudentChargeID
                        join stu in StudentRepository.GetList(expStudent)
                        on sc.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 sc.UserID equals us.UserID
                        join stucp in
                            (
                                 from cp in StudentChargePaymentRepository.Entities
                                 group cp by cp.StudentChargeID into g
                                 select new
                                 {
                                     StudentChargeID = g.Key,
                                     AmountCount = g.Sum(x => x.Amount)
                                 }
                            )
                        on cd.StudentChargeID equals stucp.StudentChargeID
                        into tempcp
                        from schcp in tempcp.DefaultIfEmpty()
                        select new ChargeDelayView
                        {
                            ChargeDelayID = cd.ChargeDelayID,
                            StudentChargeID = sc.StudentChargeID,
                            ChargeYear = sc.ChargeYear,
                            ChargeProjectID = sc.ChargeProjectID,
                            ChargeProjectStr = sc.EC_ChargeProject.Name,
                            Amount = sc.Amount,
                            ChargeAmount = sc.ActualAmount - sc.Amount,
                            ActualAmount = sc.ActualAmount,
                            ChargeTag = sc.ChargeTag,
                            UserID = sc.UserID,
                            StudentNo = us.LoginID,
                            UserName = us.Name,
                            SexID = stu.SexID,
                            InSchoolStatusID = stu.InSchoolStatusID,
                            StudentStatusID = stu.StudentStatus,
                            ClassID = stu.ClassmajorID,
                            ClassNo = cl.No,
                            ClassName = cl.Name,
                            GradeStr = gr.GradeID,
                            GradeMajorID = gr.GrademajorID,
                            GradeMajorCode = gr.Code,
                            GradeMajoyStr = gr.Name,
                            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,
                            DelayAmount = cd.DelayAmount,
                            DelayPercent = cd.DelayPercent,
                            Reason = cd.Reason,
                            RecordStatus = cd.RecordStatus,
                            CreateTime = cd.CreateTime,
                            ModifyTime = cd.ModifyTime,
                            PaidAmount = schcp.AmountCount == null ? 0 : schcp.AmountCount
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的缓交名单信息View（根据缓交信息）
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<ChargeDelayView> GetChargeDelayQueryAble(Expression<Func<EC_ChargeDelay, bool>> exp)
        {
            var query = from cd in ChargeDelayRepository.GetList(exp)
                        join sc in StudentChargeRepository.Entities
                        on cd.StudentChargeID equals sc.StudentChargeID
                        join stu in StudentRepository.Entities
                        on sc.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 sc.UserID equals us.UserID
                        join stucp in
                            (
                                 from cp in StudentChargePaymentRepository.Entities
                                 group cp by cp.StudentChargeID into g
                                 select new
                                 {
                                     StudentChargeID = g.Key,
                                     AmountCount = g.Sum(x => x.Amount)
                                 }
                            )
                        on cd.StudentChargeID equals stucp.StudentChargeID
                        into tempcp
                        from schcp in tempcp.DefaultIfEmpty()
                        select new ChargeDelayView
                        {
                            ChargeDelayID = cd.ChargeDelayID,
                            StudentChargeID = sc.StudentChargeID,
                            ChargeYear = sc.ChargeYear,
                            ChargeProjectID = sc.ChargeProjectID,
                            ChargeProjectStr = sc.EC_ChargeProject.Name,
                            Amount = sc.Amount,
                            ChargeAmount = sc.ActualAmount - sc.Amount,
                            ActualAmount = sc.ActualAmount,
                            ChargeTag = sc.ChargeTag,
                            UserID = sc.UserID,
                            StudentNo = us.LoginID,
                            UserName = us.Name,
                            SexID = stu.SexID,
                            InSchoolStatusID = stu.InSchoolStatusID,
                            StudentStatusID = stu.StudentStatus,
                            ClassID = stu.ClassmajorID,
                            ClassNo = cl.No,
                            ClassName = cl.Name,
                            GradeStr = gr.GradeID,
                            GradeMajorID = gr.GrademajorID,
                            GradeMajorCode = gr.Code,
                            GradeMajoyStr = gr.Name,
                            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,
                            DelayAmount = cd.DelayAmount,
                            DelayPercent = cd.DelayPercent,
                            Reason = cd.Reason,
                            RecordStatus = cd.RecordStatus,
                            CreateTime = cd.CreateTime,
                            ModifyTime = cd.ModifyTime,
                            PaidAmount = schcp.AmountCount == null ? 0 : schcp.AmountCount
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的缴交信息View(对应的应收名单信息全部查询，左联-待缓交金额、已缓交金额)
        /// </summary>
        /// <param name="expStudentCharge"></param>
        /// <param name="expChargeDelayApply"></param>
        /// <param name="expChargeDelayPass"></param>
        /// <returns></returns>
        public IQueryable<ChargeDelayView> GetChargeDelayQueryView(Expression<Func<EC_ChargeDelay, bool>> expChargeDelay,
             Expression<Func<EC_ChargeDelay, bool>> expChargeDelayApply, Expression<Func<EC_ChargeDelay, bool>> expChargeDelayPass)
        {
            var query = from cd in ChargeDelayRepository.GetList(expChargeDelay)
                        join sc in StudentChargeRepository.Entities
                        on cd.StudentChargeID equals sc.StudentChargeID
                        join stu in StudentRepository.Entities
                        on sc.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 sc.UserID equals us.UserID
                        join stucp in
                            (
                                 from cp in StudentChargePaymentRepository.Entities
                                 group cp by cp.StudentChargeID into g
                                 select new
                                 {
                                     StudentChargeID = g.Key,
                                     AmountCount = g.Sum(x => x.Amount)
                                 }
                            )
                        on cd.StudentChargeID equals stucp.StudentChargeID
                        into tempcp
                        from schcp in tempcp.DefaultIfEmpty()
                        join stucdApply in
                            (
                                 from cdApply in ChargeDelayRepository.GetList(expChargeDelayApply)
                                 group cdApply by cdApply.StudentChargeID into g
                                 select new
                                 {
                                     StudentChargeID = g.Key,
                                     ApplyDelayAmount = g.Sum(x => x.DelayAmount)
                                 }
                            )
                        on cd.StudentChargeID equals stucdApply.StudentChargeID
                        into tempcdApply
                        from schcdApply in tempcdApply.DefaultIfEmpty()
                        join stucdPass in
                            (
                                 from cdPass in ChargeDelayRepository.GetList(expChargeDelayPass)
                                 group cdPass by cdPass.StudentChargeID into g
                                 select new
                                 {
                                     StudentChargeID = g.Key,
                                     PassDelayAmount = g.Sum(x => x.DelayAmount)
                                 }
                            )
                        on cd.StudentChargeID equals stucdPass.StudentChargeID
                        into tempcdPass
                        from schcdPass in tempcdPass.DefaultIfEmpty()
                        select new ChargeDelayView
                        {
                            ChargeDelayID = cd.ChargeDelayID,
                            StudentChargeID = sc.StudentChargeID,
                            ChargeYear = sc.ChargeYear,
                            ChargeProjectID = sc.ChargeProjectID,
                            ChargeProjectStr = sc.EC_ChargeProject.Name,
                            Amount = sc.Amount,
                            ChargeAmount = sc.ActualAmount - sc.Amount,
                            ActualAmount = sc.ActualAmount,
                            ChargeTag = sc.ChargeTag,
                            UserID = sc.UserID,
                            StudentNo = us.LoginID,
                            UserName = us.Name,
                            SexID = stu.SexID,
                            InSchoolStatusID = stu.InSchoolStatusID,
                            StudentStatusID = stu.StudentStatus,
                            ClassID = stu.ClassmajorID,
                            ClassNo = cl.No,
                            ClassName = cl.Name,
                            GradeStr = gr.GradeID,
                            GradeMajorID = gr.GrademajorID,
                            GradeMajorCode = gr.Code,
                            GradeMajoyStr = gr.Name,
                            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,
                            PaidAmount = schcp.AmountCount == null ? 0 : schcp.AmountCount,
                            DelayAmount = cd.DelayAmount,
                            DelayPercent = cd.DelayPercent,
                            Reason = cd.Reason,
                            RecordStatus = cd.RecordStatus,
                            CreateTime = cd.CreateTime,
                            ModifyTime = cd.ModifyTime,
                            ApplyDelayAmount = (schcdApply.ApplyDelayAmount == null ? 0 : schcdApply.ApplyDelayAmount) - (cd.DelayAmount == null ? 0 : cd.DelayAmount),
                            PassDelayAmount = schcdPass.PassDelayAmount == null ? 0 : schcdPass.PassDelayAmount,
                            CanDelayAmount = sc.ActualAmount - (schcp.AmountCount == null ? 0 : schcp.AmountCount) - (schcdApply.ApplyDelayAmount == null ? 0 : schcdApply.ApplyDelayAmount) - (schcdPass.PassDelayAmount == null ? 0 : schcdPass.PassDelayAmount) + (cd.DelayAmount == null ? 0 : cd.DelayAmount) > 0 ?
                                             sc.ActualAmount - (schcp.AmountCount == null ? 0 : schcp.AmountCount) - (schcdApply.ApplyDelayAmount == null ? 0 : schcdApply.ApplyDelayAmount) - (schcdPass.PassDelayAmount == null ? 0 : schcdPass.PassDelayAmount) + (cd.DelayAmount == null ? 0 : cd.DelayAmount) : 0

                        };

            return query;
        }

    }
}