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

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

namespace EMIS.DataLogic.ChargeManage.ChargeSituation
{
   public class StudentChargeDAL
    {
       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 ChargeDelayRepository ChargeDelayRepository { get; set; }
       public StudentChargePaymentRepository StudentChargePaymentRepository { get; set; }
       public ChargeProjectRepository ChargeProjectRepository { get; set; }

       /// <summary>
       /// 查询应收名单信息
       /// </summary>
       /// <param name="expStudentCharge"></param>
       /// <param name="expStudent"></param>
       /// <returns></returns>
       public IQueryable<StudentChargeView> GetStudentChargeViewQueryAble(Expression<Func<EC_StudentCharge, bool>> expStudentCharge, 
            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 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()
                       select new StudentChargeView
                       {
                           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,
                           IsDream = stu.CF_StudentProfile.IsDreamProject == true ? 1 : 0,
                           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,
                           Remark = sc.Remark,
                           PaidAmount = schcp.AmountCount == null ? 0 : schcp.AmountCount,
                           IsArrear = ((schcp.AmountCount == null ? 0 : schcp.AmountCount) - sc.ActualAmount) >= 0 ? 0 : 1
                       };

           return query;
       }

       /// <summary>
       /// 查询学生信息View
       /// </summary>
       /// <param name="exp"></param>
       /// <returns></returns>
       public IQueryable<StudentsView> GetStudentViewQueryAble(Expression<Func<CF_Student, bool>> exp)
       {
           var query = from stu in StudentRepository.GetList(exp)
                       join cl in ClassmajorRepository.Entities
                       on stu.ClassmajorID equals cl.ClassmajorID
                       join gr in GrademajorRepository.Entities
                       on cl.GrademajorID equals gr.GrademajorID
                       join us in UserRepository.Entities
                       on stu.UserID equals us.UserID
                       select new StudentsView
                       {
                           UserID = stu.UserID,
                           LoginID = us.LoginID,
                           UserName = us.Name,
                           SexID = stu.SexID,
                           InSchoolStatusID = stu.InSchoolStatusID,
                           StudentStatus = stu.StudentStatus,
                           CertificatesType = stu.CertificatesType,
                           IDNumber = stu.IDNumber,
                           ClassMajorID = stu.ClassmajorID,
                           ClassMajorCode = cl.No,
                           ClassMajorName = cl.Name,
                           GradeMajorID = cl.GrademajorID,
                           GradeMajorCode = gr.Code,
                           GradeMajorName = gr.Name
                       };

           return query;
       }

       /// <summary>
       /// 查询应收名单对应的收费标准信息View
       /// </summary>
       /// <param name="expChargeStandard"></param>
       /// <returns></returns>
       public IQueryable<ChargeStandardView> GetStudentChargeChargeStandard(Expression<Func<EC_ChargeStandard, bool>> expChargeStandard)
       {
           var query = from ch in ChargeStandardRepository.GetList(expChargeStandard)
                       join pr in ChargeProjectRepository.Entities
                       on ch.ChargeProjectID equals pr.ChargeProjectID
                       join gr in GrademajorRepository.Entities
                       on ch.GrademajorID equals gr.GrademajorID
                       join fa in FacultymajorRepository.Entities
                       on gr.FacultymajorID equals fa.FacultymajorID
                       select new ChargeStandardView
                       {
                           ChargeStandardID = ch.ChargeStandardID,
                           GrademajorID = ch.GrademajorID,
                           GradeMajorCode = gr.Code,
                           GrademajorStr = gr.Name,
                           ChargeProjectID = ch.ChargeProjectID,
                           ChargeProjectStr = pr.Name,
                           ChargeYear = ch.ChargeYear,
                           Amount = ch.Amount,
                           CollegeID = fa.CollegeID,
                           CollegeCode = fa.CF_College.No,
                           CollegeStr = fa.CF_College.Name,
                           GradeStr = gr.GradeID,
                           StandardID = fa.StandardID,
                           EducationID = fa.EducationID,
                           LearningformID = fa.LearningformID,
                           LearnSystem = fa.LearnSystem
                       };

           return query;
       }

       /// <summary>
       /// 查询应收名单对应的学生缴费信息View
       /// </summary>
       /// <param name="expStudentCharge"></param>
       /// <returns></returns>
       public IQueryable<StudentChargePaymenView> GetStudentChargePaymentView(Expression<Func<EC_StudentCharge, bool>> expStudentCharge)
       {
           var query = from sc in StudentChargeRepository.GetList(expStudentCharge)
                       join pr in ChargeProjectRepository.Entities
                       on sc.ChargeProjectID equals pr.ChargeProjectID
                       join sp in StudentChargePaymentRepository.Entities
                       on sc.StudentChargeID equals sp.StudentChargeID
                       join us in UserRepository.Entities
                       on sc.UserID equals us.UserID
                       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
                       select new StudentChargePaymenView
                       {
                           StudentChargePaymentID = sp.StudentChargePaymentID,
                           StudentChargeID = sc.StudentChargeID,
                           UserID = sc.UserID,
                           StudentNo = us.LoginID,
                           UserName = us.Name,
                           SexID = stu.SexID,
                           InSchoolStatusID = stu.InSchoolStatusID,
                           StudentStatusID = stu.StudentStatus,
                           CollegeID = fa.CollegeID,
                           CollegeCode = fa.CF_College.No,
                           CollegeName = fa.CF_College.Name,
                           ClassID = cl.ClassmajorID,
                           ClassNo = cl.No,
                           ClassName = cl.Name,
                           GradeMajorID = gr.GrademajorID,
                           GradeMajorCode = gr.Code,
                           GradeMajorStr = gr.Name,
                           GradeStr = gr.GradeID,
                           StandardID = fa.StandardID,
                           EducationID = fa.EducationID,
                           LearningformID = fa.LearningformID,
                           LearnSystem = fa.LearnSystem,
                           ChargeYear = sc.ChargeYear,
                           ChargeProjectID = sc.ChargeProjectID,
                           ChargeProjectStr = pr.Name,
                           Amount = sp.Amount, //学生缴费金额
                           ChargeDate = sp.ChargeDate,
                           Remark = sp.Remark
                       };

           return query;
       }

       /// <summary>
       /// 查询应收名单中对应的缴费信息View(对应的缴费信息全部查询，内联)
       /// </summary>
       /// <param name="expStudentCharge"></param>
       /// <param name="expStudentChargePayment"></param>
       /// <returns></returns>
       public IQueryable<ChargeDelayView> GetStudentChargeChargeDelayView(Expression<Func<EC_StudentCharge, bool>> expStudentCharge)
       {
           var query = from sc in StudentChargeRepository.GetList(expStudentCharge)
                       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 cd in ChargeDelayRepository.Entities
                       on sc.StudentChargeID equals cd.StudentChargeID
                       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()
                       select new ChargeDelayView
                       {
                           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,
                           ChargeDelayID = cd.ChargeDelayID,
                           DelayAmount = cd.DelayAmount,
                           DelayPercent = cd.DelayPercent,
                           Reason = cd.Reason,
                           RecordStatus = cd.RecordStatus,
                           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> GetStudentChargeChargeDelayView(Expression<Func<EC_StudentCharge, bool>> expStudentCharge,
            Expression<Func<EC_ChargeDelay, bool>> expChargeDelayApply, Expression<Func<EC_ChargeDelay, bool>> expChargeDelayPass)
       {
           var query = from sc in StudentChargeRepository.GetList(expStudentCharge)
                       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 sc.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 sc.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 sc.StudentChargeID equals stucdPass.StudentChargeID
                       into tempcdPass
                       from schcdPass in tempcdPass.DefaultIfEmpty()
                       select new ChargeDelayView
                       {
                           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,
                           ApplyDelayAmount = schcdApply.ApplyDelayAmount == null ? 0 : schcdApply.ApplyDelayAmount,
                           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) > 0 ?
                                            sc.ActualAmount - (schcp.AmountCount == null ? 0 : schcp.AmountCount) - (schcdApply.ApplyDelayAmount == null ? 0 : schcdApply.ApplyDelayAmount) - (schcdPass.PassDelayAmount == null ? 0 : schcdPass.PassDelayAmount) : 0

                       };

           return query;
       }
    }
}