JW/EMiS.DataLogic/ChargeManage/ChargeSituation/ChargeAggregateDAL.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;

namespace EMIS.DataLogic.ChargeManage.ChargeSituation
{
    public class ChargeAggregateDAL
    {
        public StudentChargeRepository StudentChargeRepository { get; set; }
        public StudentChargePaymentRepository StudentChargePaymentRepository { get; set; }
        public ChargeDelayRepository ChargeDelayRepository { 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<ChargeAggregateView> GetChargeAggregateView(Expression<Func<EC_StudentCharge, bool>> expStudentCharge,
                Expression<Func<EC_ChargeDelay, bool>> expChargeDelay, Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from sc in StudentChargeRepository.GetList(expStudentCharge)
                        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 head in UserRepository.Entities
                        on cl.UserID equals head.UserID
                        into temphead
                        from headTea in temphead.DefaultIfEmpty()
                        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 sc.StudentChargeID equals stucp.StudentChargeID
                        into tempcp
                        from schcp in tempcp.DefaultIfEmpty()
                        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()
                        select new ChargeAggregateView
                        {
                            StudentChargeID = sc.StudentChargeID,
                            ChargeYear = sc.ChargeYear,
                            ChargeProjectID = sc.ChargeProjectID,
                            ChargeProjectName = sc.EC_ChargeProject.Name,
                            Amount = sc.Amount,
                            ChargeAmount = sc.ActualAmount - sc.Amount,
                            ActualAmount = sc.ActualAmount,
                            ChargeTag = sc.ChargeTag,
                            UserID = sc.UserID,
                            StudentNo = us.LoginID,
                            ExamineeNum = stu.CF_Recruitstudents.ExamineeNum,
                            UserName = us.Name,
                            SexID = stu.SexID,
                            IsDream = stu.CF_StudentProfile.IsDreamProject == true ? 1 : 0,
                            InSchoolStatus = stu.InSchoolStatusID,
                            StudentStatus = stu.StudentStatus,
                            ClassID = stu.ClassmajorID,
                            ClassNo = cl.No,
                            ClassName = cl.Name,
                            TeacherID = cl.UserID,
                            TeacherNo = headTea.LoginID,
                            TeacherName = headTea.Name,
                            GradeStr = gr.GradeID,
                            GradeMajorID = gr.GrademajorID,
                            GradeMajorCode = gr.Code,
                            GrademajorStr = 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 = schcdPass.DelayAmount == null ? 0 : schcdPass.DelayAmount,
                            PaidAmount = schcp.AmountCount == null ? 0 : schcp.AmountCount,
                            IsArrear = ((schcp.AmountCount == null ? 0 : schcp.AmountCount) - sc.ActualAmount) >= 0 ? 0 : 1,
                            AmountSum = (sc.ActualAmount - (schcp.AmountCount == null ? 0 : schcp.AmountCount))
                        };

            return query;
        }
    }
}