JW/EMiS.DataLogic/ChargeManage/ChargeSituation/StudentChargePaymenDAL.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.ChargeSituation;
namespace EMIS.DataLogic.ChargeManage.ChargeSituation
{
   public class StudentChargePaymenDAL
    {
       public StudentChargePaymentRepository StudentChargePaymentRepository { get; set; }
       public StudentChargeRepository StudentChargeRepository { get; set; }
       public UserRepository UserRepository { get; set; }
       public ChargeProjectRepository ChargeProjectRepository { get; set; }
       public ChargeStandardRepository ChargeStandardRepository { get; set; }
       public StudentRepository StudentRepository { get; set; }
       public ClassmajorRepository ClassmajorRepository { get; set; }
       public GrademajorRepository GrademajorRepository { get; set; }
       public FacultymajorRepository FacultymajorRepository { get; set; }

       /// <summary>
       /// 查询学生缴费信息View
       /// </summary>
       /// <param name="expStudentChargePayment"></param>
       /// <param name="expStudentCharge"></param>
       /// <param name="expStudent"></param>
       /// <returns></returns>
       public IQueryable<StudentChargePaymenView> GetStudentChargePaymentViewTable(Expression<Func<EC_StudentChargePayment, bool>> expStudentChargePayment, 
           Expression<Func<EC_StudentCharge, bool>> expStudentCharge, Expression<Func<CF_Student, bool>> expStudent) 
       {
           var query = from cp in StudentChargePaymentRepository.GetList(expStudentChargePayment)
                       join sc in StudentChargeRepository.GetList(expStudentCharge)
                       on cp.StudentChargeID equals sc.StudentChargeID
                       join pr in ChargeProjectRepository.Entities
                       on sc.ChargeProjectID equals pr.ChargeProjectID
                       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 totalcp in
                           (
                                from pay in StudentChargePaymentRepository.Entities
                                group pay by pay.StudentChargeID into g
                                select new
                                {
                                    StudentChargeID = g.Key,
                                    AmountCount = g.Sum(x => x.Amount)
                                }
                           )
                       on sc.StudentChargeID equals totalcp.StudentChargeID
                       into tempcp
                       from stucp in tempcp.DefaultIfEmpty()
                       select new StudentChargePaymenView
                       {
                           StudentChargePaymentID = cp.StudentChargePaymentID,
                           StudentChargeID = cp.StudentChargeID,
                           ChargeYear = sc.ChargeYear,
                           ChargeProjectID = sc.ChargeProjectID,
                           ChargeProjectStr = pr.Name,
                           ChargeTag = sc.ChargeTag,
                           ChargeDate = cp.ChargeDate,
                           Amount = cp.Amount,
                           StandardAmount = sc.Amount,
                           ChargeAmount = sc.ActualAmount - sc.Amount,
                           ActualAmount = sc.ActualAmount,
                           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,
                           GradeMajorStr = gr.Name,
                           CollegeID = fa.CollegeID,
                           CollegeCode = fa.CF_College.No,
                           CollegeName = fa.CF_College.Name,
                           StandardID = fa.StandardID,
                           EducationID = fa.EducationID,
                           LearningformID = fa.LearningformID,
                           LearnSystem = fa.LearnSystem,
                           Remark = cp.Remark,
                           PaidAmount = stucp.AmountCount == null ? 0 : stucp.AmountCount
                       };

           return query;
       }

       /// <summary>
       /// 查询学生缴费信息View(根据应收名单信息，统计对应的已缴金额)
       /// </summary>
       /// <param name="expStudentCharge"></param>
       /// <returns></returns>
       public IQueryable<StudentChargePaymenView> GetStudentChargePaymentView(Expression<Func<EC_StudentCharge, bool>> expStudentCharge)
       {
           var query = from cp in StudentChargePaymentRepository.Entities
                       join sc in StudentChargeRepository.GetList(expStudentCharge)
                       on cp.StudentChargeID equals sc.StudentChargeID
                       join pr in ChargeProjectRepository.Entities
                       on sc.ChargeProjectID equals pr.ChargeProjectID
                       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 totalcp in
                           (
                                from pay in StudentChargePaymentRepository.Entities
                                group pay by pay.StudentChargeID into g
                                select new
                                {
                                    StudentChargeID = g.Key,
                                    PaidAmount = g.Sum(x => x.Amount)
                                }
                           )
                       on cp.StudentChargeID equals totalcp.StudentChargeID
                       into tempcp
                       from stucp in tempcp.DefaultIfEmpty()
                       select new StudentChargePaymenView
                       {
                           StudentChargePaymentID = cp.StudentChargePaymentID,
                           StudentChargeID = cp.StudentChargeID,
                           ChargeYear = sc.ChargeYear,
                           ChargeProjectID = sc.ChargeProjectID,
                           ChargeProjectStr = pr.Name,
                           ChargeTag = sc.ChargeTag,
                           ChargeDate = cp.ChargeDate,
                           Amount = cp.Amount,
                           StandardAmount = sc.Amount,
                           ChargeAmount = sc.ActualAmount - sc.Amount,
                           ActualAmount = sc.ActualAmount,
                           PaidAmount = stucp.PaidAmount == null ? 0 : stucp.PaidAmount,
                           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,
                           GradeMajorStr = gr.Name,
                           CollegeID = fa.CollegeID,
                           CollegeCode = fa.CF_College.No,
                           CollegeName = fa.CF_College.Name,
                           StandardID = fa.StandardID,
                           EducationID = fa.EducationID,
                           LearningformID = fa.LearningformID,
                           LearnSystem = fa.LearnSystem,
                           Remark = cp.Remark
                       };

           return query;
       }

       /// <summary>
       /// 查询应收名单信息View(根据应收名单信息，统计对应的已缴金额)
       /// </summary>
       /// <param name="expStudentCharge"></param>
       /// <returns></returns>
       public IQueryable<StudentChargeView> GetStudentChargeViewQueryAble(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 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()
                       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
                       };

           return query;
       }

       /// <summary>
       /// 查询学生缴费信息备注信息
       /// </summary>
       /// <param name="exp"></param>
       /// <returns></returns>
       public IQueryable<StudentChargePaymenView> GetRemarkViewTable(Expression<Func<EC_StudentChargePayment, bool>> exp)
       {
           var query = from a in StudentChargePaymentRepository.GetList(exp)
                       select new StudentChargePaymenView
                       {
                           Remark = a.Remark
                       };

           return query.Distinct();

       }

    }
}