JW/EMiS.DataLogic/ChargeManage/ChargeTotal/ChargeTotalDAL.cs

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

namespace EMIS.DataLogic.ChargeManage.ChargeTotal
{
    public class ChargeTotalDAL
    {
        public StudentChargeRepository StudentChargeRepository { get; set; }
        public StudentChargePaymentRepository StudentChargePaymentRepository { get; set; }
        public ChargeDelayRepository ChargeDelayRepository { get; set; }
        public ChargeProjectRepository ChargeProjectRepository { 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<ChargeTotalView> GetChargeTotalView(Expression<Func<EC_StudentCharge, bool>> expStudentCharge,
                Expression<Func<EC_ChargeDelay, bool>> expChargeDelay, Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from g in
                            (
                                from stucharge in
                                    (
                                      from sc in StudentChargeRepository.GetList(expStudentCharge)
                                      join stu in StudentRepository.GetList(expStudent)
                                      on sc.UserID equals stu.UserID
                                      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,
                                                  PaymenAmount = g.Sum(x => x.Amount)
                                              }
                                          )
                                       on sc.StudentChargeID equals stucp.StudentChargeID
                                       into tempcp
                                      from schcpgr in tempcp.DefaultIfEmpty()
                                      select new
                                      {
                                          StudentChargeID = sc.StudentChargeID,
                                          UserID = sc.UserID,
                                          SexID = stu.SexID,
                                          ClassmajorID = stu.ClassmajorID,
                                          InSchoolStatusID = stu.InSchoolStatusID,
                                          StudentStatus = stu.StudentStatus,
                                          ChargeYear = sc.ChargeYear,
                                          ChargeProjectID = sc.ChargeProjectID,
                                          ChargeTag = sc.ChargeTag,
                                          Amount = sc.Amount,
                                          ChargeAmount = sc.ActualAmount - sc.Amount,
                                          ActualAmount = sc.ActualAmount,
                                          DelayCount = schcdPass.StudentChargeID == null ? 0 : 1,
                                          DelayAmount = schcdPass.DelayAmount ?? 0,
                                          PaymenCount = schcpgr.StudentChargeID == null ? 0 : 1,
                                          PaymenAmount = schcpgr.PaymenAmount ?? 0
                                      }
                                    )
                                group stucharge by
                                new { stucharge.ClassmajorID, stucharge.ChargeYear, stucharge.ChargeProjectID }
                                    into gr
                                    select new
                                    {
                                        ClassmajorID = gr.Key.ClassmajorID,
                                        ChargeYear = gr.Key.ChargeYear,
                                        ChargeProjectID = gr.Key.ChargeProjectID,
                                        ChargeCount = gr.Count(),
                                        Amount = gr.Sum(x => x.Amount),
                                        ChargeAmount = gr.Sum(x => x.ChargeAmount),
                                        ActualAmount = gr.Sum(x => x.ActualAmount),
                                        DelayCount = gr.Sum(x => x.DelayCount),
                                        DelayAmount = gr.Sum(x => x.DelayAmount),
                                        PaymenCount = gr.Sum(x => x.PaymenCount),
                                        PaymenAmount = gr.Sum(x => x.PaymenAmount)
                                    }
                            )
                        join pr in ChargeProjectRepository.Entities
                        on g.ChargeProjectID equals pr.ChargeProjectID
                        join cl in ClassmajorRepository.Entities
                        on g.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 head in UserRepository.Entities
                        on cl.UserID equals head.UserID
                        into temphead
                        from headTea in temphead.DefaultIfEmpty()
                        select new ChargeTotalView
                        {
                            ClassID = g.ClassmajorID,
                            ClassNo = cl.No,
                            ClassName = cl.Name,
                            GradeMajorID = cl.GrademajorID,
                            GradeMajorCode = gr.Code,
                            GradeMajoyStr = gr.Name,
                            CollegeID = fa.CollegeID,
                            CollegeCode = fa.CF_College.No,
                            CollegeStr = fa.CF_College.Name,
                            Grade = gr.GradeID,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            ChargeYear = g.ChargeYear,
                            ChargeProjectID = g.ChargeProjectID,
                            ChargeProjectStr = pr.Name,
                            ChargeCount = g.ChargeCount,
                            Amount = g.Amount,
                            ChargeAmount = g.ChargeAmount,
                            ActualAmount = g.ActualAmount,
                            DelayCount = g.DelayCount,
                            DelayAmount = g.DelayAmount,
                            PaymenCount = g.PaymenCount,
                            PaymenAmount = g.PaymenAmount,
                            TeacherID = cl.UserID,
                            TeacherNo = headTea.LoginID,
                            TeacherName = headTea.Name,
                        };

            return query;
        }

    }
}