LIOT/EMiS.DataLogic/FeeManage/FeeDAL.cs

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

namespace EMIS.DataLogic.FeeManage
{
    public class FeeDAL
    {
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public ExaminationBatchRepository ExaminationBatchRepository { get; set; }
        public ExaminationBatchProjectRepository ExaminationBatchProjectRepository { get; set; }
        public ExaminationProjectRepository ExaminationProjectRepository { get; set; }
        public ExaminationTypeRepository ExaminationTypeRepository { get; set; }
        public ExaminationRegistrationRepository ExaminationRegistrationRepository { get; set; }
        public ExaminationRegistrationConfirmRepository ExaminationRegistrationConfirmRepository { get; set; }
        public ExaminationProjectFeeTypeRepository ExaminationProjectFeeTypeRepository { get; set; }

        public StudentRepository StudentRepository { get; set; }
        public StudentAccountRepository StudentAccountRepository { get; set; }
        public StudentContactRepository StudentContactRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public WechatPayListRepository WechatPayListRepository { get; set; }

        public DictionaryItemRepository DictionaryItemRepository { get; set; }

        public IQueryable<StudentFeeView> GetStudentFeeViewQueryable(Expression<Func<EX_ExaminationBatch, bool>> batchExp,
            Expression<Func<EX_ExaminationType, bool>> typeExp, Expression<Func<EX_ExaminationProject, bool>> projectExp,
            Expression<Func<EX_ExaminationRegistration, bool>> registExp, Expression<Func<CF_College, bool>> collegeExp,
            Expression<Func<CF_Facultymajor, bool>> facultExp, Expression<Func<CF_Grademajor, bool>> grademajorExp, Expression<Func<CF_Classmajor, bool>> classmajorExp)
        {
            var query = (from batch in ExaminationBatchRepository.GetList(batchExp)
                         from batchProject in ExaminationBatchProjectRepository.Entities.Where(x => x.ExaminationBatchID == batch.ExaminationBatchID)
                         from project in ExaminationProjectRepository.Entities.Where(projectExp).Where(x => x.ExaminationProjectID == batchProject.ExaminationProjectID)
                         from type in ExaminationTypeRepository.Entities.Where(typeExp).Where(x => x.ExaminationTypeID == project.ExaminationTypeID)
                         from schoolyear in SchoolyearRepository.Entities.Where(x => x.SchoolyearID == batch.SchoolyearID)
                         from regist in ExaminationRegistrationRepository.Entities.Where(registExp).Where(x => x.ExaminationBatchProjectID == batchProject.ExaminationBatchProjectID)
                         from confirm in ExaminationRegistrationConfirmRepository.Entities.Where(x => x.ExaminationRegistrationConfirmID == regist.ExaminationRegistrationConfirmID)
                            .DefaultIfEmpty()
                         from feeType in ExaminationProjectFeeTypeRepository.Entities.Where(x => x.ExaminationProjectFeeTypeID == regist.ExaminationProjectFeeTypeID)
                         from user in UserRepository.Entities.Where(x => x.UserID == regist.UserID)
                         from student in StudentRepository.Entities.Where(x => x.UserID == regist.UserID)
                         from account in StudentAccountRepository.Entities.Where(x => x.UserID == student.UserID)
                         from classmajor in ClassmajorRepository.Entities.Where(classmajorExp).Where(x => x.ClassmajorID == student.ClassmajorID)
                         from grademajor in GrademajorRepository.Entities.Where(grademajorExp).Where(x => x.GrademajorID == classmajor.GrademajorID)
                         from facultmajor in FacultymajorRepository.Entities.Where(facultExp).Where(x => x.FacultymajorID == grademajor.FacultymajorID)
                         from college in CollegeRepository.Entities.Where(collegeExp).Where(x => x.CollegeID == facultmajor.CollegeID)
                         from refundUser in UserRepository.Entities.Where(x => x.UserID == regist.RefundUserID)
                            .DefaultIfEmpty()
                         from refundConfirmUser in UserRepository.Entities.Where(x => x.UserID == regist.RefundConfirmUserID)
                            .DefaultIfEmpty()
                         from pay in WechatPayListRepository.Entities.Where(x => x.ExaminationRegistrationID == regist.ExaminationRegistrationID)
                            .DefaultIfEmpty()
                         select new StudentFeeView
                         {
                             ExaminationRegistrationID = regist.ExaminationRegistrationID,
                             ExaminationBatchProjectID = batchProject.ExaminationBatchProjectID,
                             ExaminationProjectID = project.ExaminationProjectID,
                             ExaminationTypeName = project.EX_ExaminationType.Name,
                             ExaminationRegistrationConfirmID = confirm.ExaminationRegistrationConfirmID,
                             No = confirm.No,
                             ReceiptNo = confirm.ReceiptNo,
                             ExaminationProjectName = project.Name,
                             ExaminationBatchName = batch.Name,
                             SchoolyearID = schoolyear.SchoolyearID,
                             SchoolyearCode = schoolyear.Code,
                             ExaminationProjectFeeTypeID = feeType.ExaminationProjectFeeTypeID,
                             FeeTypeID = feeType.FeeTypeID,
                             UserID = student.UserID,
                             CollegeID = college.CollegeID,
                             ClassmajorID = classmajor.ClassmajorID,
                             ClassmajorName = classmajor.Name,
                             LoginID = user.LoginID,
                             StudentNo = student.StudentCardNo,
                             UserName = user.Name,
                             Sex = student.Sex,
                             CertificatesType = student.CertificatesType,
                             IDNumber = student.IDNumber,
                             PhoneNumber = student.CF_StudentContact.Mobile,
                             Fee = regist.Fee,
                             Remark = regist.Remark,
                             RecordStatus = regist.RecordStatus,
                             PayTime = regist.PayTime,
                             BankName = (regist.BankName == null && regist.CardNo == null) ? account.BankName : regist.BankName,
                             CardNo = (regist.BankName == null && regist.CardNo == null) ? account.CardNo : regist.CardNo,
                             PayWay = regist.RecordStatus != (int)EX_ExaminationRegistrationStatus.ForPay ? (confirm.ExaminationRegistrationConfirmID != null ? (int)EX_PayWay.Offline : (int)EX_PayWay.Online) : (int?)null,
                             SchoolAreaID = grademajor.SchoolAreaID,
                             RefundUserID = regist.RefundUserID,
                             RefundUserName = refundUser.Name,
                             RefundTime = regist.RefundTime,
                             RefundConfirmUserID = regist.RefundConfirmUserID,
                             RefundConfirmUserName = refundConfirmUser.Name,
                             RefundConfirmTime = regist.RefundConfirmTime,
                             WechatOrderID = pay.WechatOrderID,
                             RefundOrderID = pay.RefundOrderID
                         }
                         );

            return query;
        }

        public IQueryable<StudentFeeDetailView> GetStudentFeeDetailViewQueryable(Expression<Func<EX_ExaminationRegistrationConfirm, bool>> confirmExp)
        {
            var query = (from confirm in ExaminationRegistrationConfirmRepository.GetList(confirmExp) 
                         from regist in ExaminationRegistrationRepository.Entities.Where(x => x.ExaminationRegistrationConfirmID == confirm.ExaminationRegistrationConfirmID) 
                         from batchProject in ExaminationBatchProjectRepository.Entities.Where(x => x.ExaminationBatchProjectID == regist.ExaminationBatchProjectID) 
                         from project in ExaminationProjectRepository.Entities.Where(x => x.ExaminationProjectID == batchProject.ExaminationProjectID) 
                         from type in ExaminationTypeRepository.Entities.Where(x => x.ExaminationTypeID == project.ExaminationTypeID) 
                         from user in UserRepository.Entities.Where(x => x.UserID == regist.UserID) 
                         from student in StudentRepository.Entities.Where(x => x.UserID == user.UserID) 
                         from classmajor in ClassmajorRepository.Entities.Where(x => x.ClassmajorID == student.ClassmajorID)
                         from feetype in ExaminationProjectFeeTypeRepository.Entities.Where(x => x.ExaminationProjectFeeTypeID == regist.ExaminationProjectFeeTypeID)
                         select new StudentFeeDetailView
                         {
                             ExaminationRegistrationID = regist.ExaminationRegistrationID,
                             ExaminationTypeID = type.ExaminationTypeID,
                             ExaminationTypeName = type.Name,
                             ExaminationProjectID = project.ExaminationProjectID,
                             ExaminationProjectName = project.Name,
                             UserID = user.UserID,
                             ClassmajorID = student.ClassmajorID,
                             ClassmajorName = classmajor.Name,
                             LoginID = user.LoginID,
                             UserName = user.Name,
                             Sex = student.Sex,
                             IDNumber = student.IDNumber,
                             FeeTypeID = feetype.FeeTypeID,
                             Fee = regist.Fee
                         }
                         );

            return query;
        }

        public IQueryable<PrintView> GetPrintViewQueryable(Expression<Func<EX_ExaminationRegistrationConfirm, bool>> exp)
        {
            var query = (
                from confirm in ExaminationRegistrationConfirmRepository.GetList(exp) 
                from firstRegist in (from regist in ExaminationRegistrationRepository.Entities.Where(x => x.ExaminationRegistrationConfirmID == confirm.ExaminationRegistrationConfirmID) 
                                     from user in UserRepository.Entities.Where(x => x.UserID == regist.UserID) 
                                     from student in StudentRepository.Entities.Where(x => x.UserID == user.UserID) 
                                     from classmajor in ClassmajorRepository.Entities.Where(x => x.ClassmajorID == student.ClassmajorID)
                                     from batchProject in ExaminationBatchProjectRepository.Entities.Where(x => x.ExaminationBatchProjectID == regist.ExaminationBatchProjectID)
                                     from project in ExaminationProjectRepository.Entities.Where(x => x.ExaminationProjectID == batchProject.ExaminationProjectID)
                                     orderby user.LoginID
                                     select new { ClassmajorName = classmajor.Name, ExaminationProjectName = project.Name, user.LoginID }).Take(1) 
                from total in (from regist in ExaminationRegistrationRepository.Entities.Where(x => x.ExaminationRegistrationConfirmID == confirm.ExaminationRegistrationConfirmID) 
                               group regist by regist.ExaminationRegistrationConfirmID into g 
                               select g.Sum(x => x.Fee))
                select new PrintView
                {
                    ReceiptNo = "收据号：" + confirm.ReceiptNo,
                    Classmajor = firstRegist.ClassmajorName + "&nbsp;" + firstRegist.ExaminationProjectName,
                    Total = total
                });

            return query;
        }

        public IQueryable<PrintGDCXView> GetPrintGDCXViewQueryable(Expression<Func<EX_ExaminationRegistrationConfirm, bool>> exp)
        {
            var query = from confirm in ExaminationRegistrationConfirmRepository.GetList(exp)
                        from regist in ExaminationRegistrationRepository.Entities.Where(x => x.ExaminationRegistrationConfirmID == confirm.ExaminationRegistrationConfirmID)
                        from stu in StudentRepository.Entities.Where(x => x.UserID == regist.UserID)
                        from cla in ClassmajorRepository.Entities.Where(x => x.ClassmajorID == stu.ClassmajorID)
                        from gra in GrademajorRepository.Entities.Where(x => x.GrademajorID == cla.GrademajorID)
                        from fac in FacultymajorRepository.Entities.Where(x => x.FacultymajorID == gra.FacultymajorID)
                        from col in CollegeRepository.Entities.Where(x => x.CollegeID == fac.CollegeID)
                        group regist by new { confirm.No, confirm.ReceiptNo, col.CollegeID, collegeName = col.Name, cla.ClassmajorID, ClassName = cla.Name }
                        into gregist
                        select new PrintGDCXView
                        {
                            ReceiptNo = gregist.Key.ReceiptNo,
                            ConfirmNo = gregist.Key.No,
                            College = gregist.Key.collegeName,
                            Classmajor = gregist.Key.ClassName,
                            Total = gregist.Sum(x => x.Fee),
                        };
            return query;
        }

        public IQueryable<PrintProjectView> GetPrintProjectViewQueryable(Expression<Func<EX_ExaminationRegistrationConfirm, bool>> exp)
        {
            var query = from confirm in ExaminationRegistrationConfirmRepository.GetList(exp)
                        from regist in ExaminationRegistrationRepository.Entities.Where(x => x.ExaminationRegistrationConfirmID == confirm.ExaminationRegistrationConfirmID)
                        from ebpr in ExaminationBatchProjectRepository.Entities.Where(x => x.ExaminationBatchProjectID == regist.ExaminationBatchProjectID)
                        from ebr in ExaminationProjectRepository.Entities.Where(x => x.ExaminationProjectID == ebpr.ExaminationProjectID)
                        from epftp in ExaminationProjectFeeTypeRepository.Entities.Where(x => x.ExaminationProjectFeeTypeID == regist.ExaminationProjectFeeTypeID)
                        from dic in DictionaryItemRepository.Entities.Where(x => x.Value == epftp.FeeTypeID && x.DictionaryCode == "EX_ExaminationProjectFeeType")
                        from stu in StudentRepository.Entities.Where(x => x.UserID == regist.UserID)
                        group regist by new { ebr.ExaminationProjectID, ExaminationProjectName = ebr.Name, epftp.FeeTypeID, FeeTypeName = dic.Name }
                        into gregist
                        select new PrintProjectView
                        {
                            ExaminationProjectName = gregist.Key.ExaminationProjectName,
                            ExaminationProjectFeeName = gregist.Key.FeeTypeName,
                            StudentCount = gregist.Count(),
                            Fee = gregist.Sum(x => x.Fee)
                        };
            return query;
        }

        public IQueryable<RefundView> GetRefundViewQueryable(Expression<Func<EX_ExaminationRegistration, bool>> exp)
        {
            var query = (from regist in ExaminationRegistrationRepository.GetList(exp)
                         from confirm in ExaminationRegistrationConfirmRepository.Entities.Where(x => x.ExaminationRegistrationConfirmID == regist.ExaminationRegistrationConfirmID).DefaultIfEmpty()
                         from user in UserRepository.Entities.Where(x => x.UserID == regist.UserID)
                         from student in StudentRepository.Entities.Where(x => x.UserID == user.UserID) 
                         from classmajor in ClassmajorRepository.Entities.Where(x => x.ClassmajorID == student.ClassmajorID)
                         from grademajor in GrademajorRepository.Entities.Where(x => x.GrademajorID == classmajor.GrademajorID)
                         from facultmajor in FacultymajorRepository.Entities.Where(x => x.FacultymajorID == grademajor.FacultymajorID)
                         from college in CollegeRepository.Entities.Where(x => x.CollegeID == facultmajor.CollegeID) 
                         from account in StudentAccountRepository.Entities.Where(x => x.UserID == student.UserID) 
                         from contact in StudentContactRepository.Entities.Where(x => x.UserID == student.UserID) 
                         from batchProject in ExaminationBatchProjectRepository.Entities.Where(x => x.ExaminationBatchProjectID == regist.ExaminationBatchProjectID)
                         from batch in ExaminationBatchRepository.Entities.Where(x => x.ExaminationBatchID == batchProject.ExaminationBatchID)
                         from project in ExaminationProjectRepository.Entities.Where(x => x.ExaminationProjectID == batchProject.ExaminationProjectID)
                         from feetype in ExaminationProjectFeeTypeRepository.Entities.Where(x => x.ExaminationProjectFeeTypeID == regist.ExaminationProjectFeeTypeID) 
                         from refundUser in UserRepository.Entities.Where(x => x.UserID == regist.RefundUserID)
                            .DefaultIfEmpty() 
                         select new RefundView
                         {
                             ExaminationRegistrationID = regist.ExaminationRegistrationID,
                             UserID = regist.UserID,
                             UserName = user.Name,
                             IDNumber = student.IDNumber,
                             Mobile = contact.Mobile,
                             Year = grademajor.SchoolyearID,
                             CollegeID = college.CollegeID,
                             CollegeName = college.Name,
                             ClassmajorID = classmajor.ClassmajorID,
                             ClassmajorName = classmajor.Name,
                             ExaminationBatchID = batch.ExaminationBatchID,
                             ExaminationBatchName = batch.Name,
                             ExaminationProjectID = project.ExaminationProjectID,
                             ExaminationProjectName = project.Name,
                             FeeTypeID = feetype.FeeTypeID,
                             Fee = regist.Fee,
                             ReceiptNo = confirm.ReceiptNo,
                             BankName = (regist.BankName == null && regist.CardNo == null) ? account.BankName : regist.BankName,
                             CardNo = (regist.BankName == null && regist.CardNo == null) ? account.CardNo : regist.CardNo,
                             RefundReason = regist.RefundReason,
                             RefundUserID = regist.RefundUserID,
                             RefundUserName = refundUser.Name,
                             RefundTime = regist.RefundTime,
                             ConfirmNo = confirm.No,
                             SchoolYearID = batch.SchoolyearID,
                             SchoolYearCode = batch.CF_Schoolyear.Code,
                             StudentNo = student.StudentCardNo,
                             RecordStatus = regist.RecordStatus
                         }
                         );

            return query;
        }

        public IQueryable<StudentFeeClassStatisticView> GetStudentFeeClassStatisticViewQueryable(Expression<Func<EX_ExaminationBatch, bool>> batchExp, 
            Expression<Func<EX_ExaminationProject, bool>> projectExp, Expression<Func<CF_Facultymajor, bool>> facultExp, 
            Expression<Func<CF_Grademajor, bool>> grademajorExp, Expression<Func<CF_Classmajor, bool>> classmajorExp)
        {
            var query = (from batch in ExaminationBatchRepository.GetList(batchExp)
                         from batchProject in ExaminationBatchProjectRepository.Entities.Where(x => x.ExaminationBatchID == batch.ExaminationBatchID)
                         from project in ExaminationProjectRepository.Entities.Where(projectExp).Where(x => x.ExaminationProjectID == batchProject.ExaminationProjectID)
                         from type in ExaminationTypeRepository.Entities.Where(x => x.ExaminationTypeID == project.ExaminationTypeID)
                         from schoolyear in SchoolyearRepository.Entities.Where(x => x.SchoolyearID == batch.SchoolyearID)
                         from regist in ExaminationRegistrationRepository.Entities.Where(x => x.ExaminationBatchProjectID == batchProject.ExaminationBatchProjectID)
                         from feeType in ExaminationProjectFeeTypeRepository.Entities.Where(x => x.ExaminationProjectFeeTypeID == regist.ExaminationProjectFeeTypeID)
                         from student in StudentRepository.Entities.Where(x => x.UserID == regist.UserID)
                         from classmajor in ClassmajorRepository.Entities.Where(classmajorExp).Where(x => x.ClassmajorID == student.ClassmajorID)
                         from grademajor in GrademajorRepository.Entities.Where(grademajorExp).Where(x => x.GrademajorID == classmajor.GrademajorID)
                         from facultmajor in FacultymajorRepository.Entities.Where(facultExp).Where(x => x.FacultymajorID == grademajor.FacultymajorID)
                         from college in CollegeRepository.Entities.Where(x => x.CollegeID == facultmajor.CollegeID) 
                         group regist by new
                         {
                             Year = grademajor.SchoolyearID,
                             CollegeID = facultmajor.CollegeID,
                             CollegeName = college.Name,
                             ClassmajorID = classmajor.ClassmajorID,
                             ClassmajorName = classmajor.Name,
                             ExaminationTypeID = type.ExaminationTypeID,
                             ExaminationTypeName = type.Name,
                             ExaminationProjectID = project.ExaminationProjectID,
                             ExaminationProjectName = project.Name,
                             FeeTypeID = feeType.FeeTypeID
                         } into g
                         select new StudentFeeClassStatisticView
                         {
                             Year = g.Key.Year,
                             CollegeID = g.Key.CollegeID,
                             CollegeName = g.Key.CollegeName,
                             ClassmajorID = g.Key.ClassmajorID,
                             ClassmajorName = g.Key.ClassmajorName,
                             ExaminationTypeID = g.Key.ExaminationTypeID,
                             ExaminationTypeName = g.Key.ExaminationTypeName,
                             ExaminationProjectID = g.Key.ExaminationProjectID,
                             ExaminationProjectName = g.Key.ExaminationProjectName,
                             FeeTypeID = g.Key.FeeTypeID,
                             TotalRegistCount = g.Sum(x => 1),
                             TotalRegistAmount = g.Sum(x => x.Fee),
                             TotalPaidCount = g.Sum(x => x.RecordStatus == (int)EX_ExaminationRegistrationStatus.Paid ? 1 : 0),
                             TotalPaidAmount = g.Sum(x => x.RecordStatus == (int)EX_ExaminationRegistrationStatus.Paid ? x.Fee : 0),
                             TotalRefundCount = g.Sum(x => x.RecordStatus == (int)EX_ExaminationRegistrationStatus.Refunded ? 1 : 0),
                             TotalRefundAmount = g.Sum(x => x.RecordStatus == (int)EX_ExaminationRegistrationStatus.Refunded ? x.Fee : 0),
                             TotalRefundApplyCount = g.Sum(x => x.RecordStatus == (int)EX_ExaminationRegistrationStatus.RefundApply ? 1 : 0),
                             TotalRefundApplyAmount = g.Sum(x => x.RecordStatus == (int)EX_ExaminationRegistrationStatus.RefundApply ? x.Fee : 0),
                             TotalRefundingCount = g.Sum(x => (x.RecordStatus == (int)EX_ExaminationRegistrationStatus.OnRefund || x.RecordStatus == (int)EX_ExaminationRegistrationStatus.Refunding) ? 1 : 0),
                             TotalRefundingAmount = g.Sum(x => (x.RecordStatus == (int)EX_ExaminationRegistrationStatus.OnRefund || x.RecordStatus == (int)EX_ExaminationRegistrationStatus.Refunding) ? x.Fee : 0),
                         }
                         );

            return query;
        }

        public IQueryable<StudentFeeProjectStatisticView> GetStudentFeeProjectStatisticViewQueryable(Expression<Func<EX_ExaminationBatch, bool>> batchExp,
            Expression<Func<EX_ExaminationProject, bool>> projectExp, Expression<Func<CF_Facultymajor, bool>> facultExp,
            Expression<Func<CF_Grademajor, bool>> grademajorExp, Expression<Func<CF_Classmajor, bool>> classmajorExp,
            Expression<Func<EX_ExaminationRegistration, bool>> registExp)
        {
            var query = (from batch in ExaminationBatchRepository.GetList(batchExp)
                         from batchProject in ExaminationBatchProjectRepository.Entities.Where(x => x.ExaminationBatchID == batch.ExaminationBatchID)
                         from project in ExaminationProjectRepository.Entities.Where(projectExp).Where(x => x.ExaminationProjectID == batchProject.ExaminationProjectID)
                         from type in ExaminationTypeRepository.Entities.Where(x => x.ExaminationTypeID == project.ExaminationTypeID)
                         from schoolyear in SchoolyearRepository.Entities.Where(x => x.SchoolyearID == batch.SchoolyearID)
                         from regist in ExaminationRegistrationRepository.Entities.Where(registExp).Where(x => x.ExaminationBatchProjectID == batchProject.ExaminationBatchProjectID)
                         from feeType in ExaminationProjectFeeTypeRepository.Entities.Where(x => x.ExaminationProjectFeeTypeID == regist.ExaminationProjectFeeTypeID)
                         from student in StudentRepository.Entities.Where(x => x.UserID == regist.UserID)
                         from classmajor in ClassmajorRepository.Entities.Where(classmajorExp).Where(x => x.ClassmajorID == student.ClassmajorID)
                         from grademajor in GrademajorRepository.Entities.Where(grademajorExp).Where(x => x.GrademajorID == classmajor.GrademajorID)
                         from facultmajor in FacultymajorRepository.Entities.Where(facultExp).Where(x => x.FacultymajorID == grademajor.FacultymajorID)
                         from college in CollegeRepository.Entities.Where(x => x.CollegeID == facultmajor.CollegeID)
                         group regist by new
                         {
                             ExaminationBatchID = batch.ExaminationBatchID,
                             ExaminationBatchName = batch.Name,
                             ExaminationTypeID = type.ExaminationTypeID,
                             ExaminationTypeName = type.Name,
                             ExaminationProjectID = project.ExaminationProjectID,
                             ExaminationProjectName = project.Name,
                             FeeTypeID = feeType.FeeTypeID
                         } into g
                         select new StudentFeeProjectStatisticView
                         {
                             ExaminationBatchID = g.Key.ExaminationBatchID,
                             ExaminationBatchName = g.Key.ExaminationBatchName,
                             ExaminationTypeID = g.Key.ExaminationTypeID,
                             ExaminationTypeName = g.Key.ExaminationTypeName,
                             ExaminationProjectID = g.Key.ExaminationProjectID,
                             ExaminationProjectName = g.Key.ExaminationProjectName,
                             FeeTypeID = g.Key.FeeTypeID,
                             TotalRegistCount = g.Sum(x => 1),
                             TotalRegistAmount = g.Sum(x => x.Fee),
                             TotalPaidCount = g.Sum(x => x.RecordStatus == (int)EX_ExaminationRegistrationStatus.Paid ? 1 : 0),
                             TotalPaidAmount = g.Sum(x => x.RecordStatus == (int)EX_ExaminationRegistrationStatus.Paid ? x.Fee : 0),
                             TotalRefundCount = g.Sum(x => x.RecordStatus == (int)EX_ExaminationRegistrationStatus.Refunded ? 1 : 0),
                             TotalRefundAmount = g.Sum(x => x.RecordStatus == (int)EX_ExaminationRegistrationStatus.Refunded ? x.Fee : 0),
                             TotalRefundApplyCount = g.Sum(x => x.RecordStatus == (int)EX_ExaminationRegistrationStatus.RefundApply ? 1 : 0),
                             TotalRefundApplyAmount = g.Sum(x => x.RecordStatus == (int)EX_ExaminationRegistrationStatus.RefundApply ? x.Fee : 0),
                             TotalRefundingCount = g.Sum(x => (x.RecordStatus == (int)EX_ExaminationRegistrationStatus.OnRefund || x.RecordStatus == (int)EX_ExaminationRegistrationStatus.Refunding) ? 1 : 0),
                             TotalRefundingAmount = g.Sum(x => (x.RecordStatus == (int)EX_ExaminationRegistrationStatus.OnRefund || x.RecordStatus == (int)EX_ExaminationRegistrationStatus.Refunding) ? x.Fee : 0),
                         }
                         );

            return query;
        }

        public IQueryable<FinanceReportView> GetFinanceReportViewQueryable(Expression<Func<EX_ExaminationBatch, bool>> batchExp,
            Expression<Func<EX_ExaminationProject, bool>> projectExp,
            Expression<Func<EX_ExaminationRegistration, bool>> registExp)
        {
            var sql = (from batch in ExaminationBatchRepository.GetList(batchExp)
                       from batchProject in ExaminationBatchProjectRepository.Entities.Where(x => x.ExaminationBatchID == batch.ExaminationBatchID)
                       from project in ExaminationProjectRepository.Entities.Where(projectExp).Where(x => x.ExaminationProjectID == batchProject.ExaminationProjectID)
                       from regist in ExaminationRegistrationRepository.Entities.Where(registExp).Where(x => x.ExaminationBatchProjectID == batchProject.ExaminationBatchProjectID)
                       from feeType in ExaminationProjectFeeTypeRepository.Entities.Where(x => x.ExaminationProjectFeeTypeID == regist.ExaminationProjectFeeTypeID)
                       from student in StudentRepository.Entities.Where(x => x.UserID == regist.UserID)
                       from classmajor in ClassmajorRepository.Entities.Where(x => x.ClassmajorID == student.ClassmajorID)
                       from confirm in ExaminationRegistrationConfirmRepository.Entities.Where(x => x.ExaminationRegistrationConfirmID == regist.ExaminationRegistrationConfirmID)
                        .DefaultIfEmpty()
                       from receiver in UserRepository.Entities.Where(x => x.UserID == confirm.Receiver)
                        .DefaultIfEmpty()
                       group regist by new
                       {
                           confirm.ReceiptNo,
                           regist.PayTime,
                           classmajor.ClassmajorID,
                           ClassmajorName = classmajor.Name,
                           ExaminationRegistrationConfirmNo = confirm.No,
                           project.ExaminationProjectID,
                           ExaminationProjectName = project.Name,
                           feeType.FeeTypeID,
                           regist.Fee,
                           confirm.Receiver,
                           ReceiverName = receiver.Name
                       } into g
                       select new FinanceReportView
                       {
                           ReceiptNo = g.Key.ReceiptNo,
                           PayTime = g.Key.PayTime,
                           ClassmajorID = g.Key.ClassmajorID,
                           ClassmajorName = g.Key.ClassmajorName,
                           ExaminationRegistrationConfirmNo = g.Key.ExaminationRegistrationConfirmNo,
                           ExaminationProjectID = g.Key.ExaminationProjectID,
                           ExaminationProjectName = g.Key.ExaminationProjectName,
                           FeeTypeID = g.Key.FeeTypeID,
                           Fee = g.Key.Fee,
                           StudentCount = g.Sum(x => 1),
                           Amount = g.Sum(x => (x.RecordStatus == (int)EX_ExaminationRegistrationStatus.Paid ? x.Fee : 0)),
                           Receiver = g.Key.Receiver,
                           ReceiverName = g.Key.ReceiverName,
                           PayTimeOrder = g.Key.PayTime.HasValue ? "" : g.Key.PayTime.ToString().Substring(0, 10)
                       }
                );

            return sql;
        }
    }
}