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

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

namespace EMIS.DataLogic.ChargeManage.ChargeSituation
{
    public class ChargeStandardDAL
    {
        public DictionaryItemRepository dictionaryItemRepository { get; set; }
        public ChargeProjectRepository ChargeProjectRepository { get; set; }
        public ChargeStandardRepository ChargeStandardRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public StudentChargePaymentRepository StudentChargePaymentRepository { get; set; }
        public StudentChargeRepository StudentChargeRepository { get; set; }
        public InSchoolSettingRepository InSchoolSetingRepository { get; set; }

        /// <summary>
        /// 查询收费标准信息(全部在校状态学生)
        /// </summary>
        /// <param name="expChargeStandard"></param>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<ChargeStandardView> GetChargeStandarViewQueryAble(Expression<Func<EC_ChargeStandard, bool>> expChargeStandard, Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from ch in ChargeStandardRepository.GetList(expChargeStandard)
                        join pr in ChargeProjectRepository.Entities
                        on ch.ChargeProjectID equals pr.ChargeProjectID
                        join sctoal in
                            (
                              from stu in StudentRepository.GetList(expStudent)
                              join cl in ClassmajorRepository.Entities
                              on stu.ClassmajorID equals cl.ClassmajorID
                              group cl by cl.GrademajorID into g
                              select new
                              {
                                  GrademajorID = (Guid?)g.Key,
                                  StudentCount = g.Count()
                              }
                            )
                        on ch.GrademajorID equals sctoal.GrademajorID
                        into chtemp
                        from chsc in chtemp.DefaultIfEmpty()
                        join sc in
                            (
                                from stuch in StudentChargeRepository.Entities
                                join stu in StudentRepository.Entities
                                on stuch.UserID equals stu.UserID
                                join cl in ClassmajorRepository.Entities
                                on stu.ClassmajorID equals cl.ClassmajorID
                                join ch in ChargeStandardRepository.Entities
                                on
                                new { cl.GrademajorID, stuch.ChargeProjectID, stuch.ChargeYear }
                                equals
                                new { ch.GrademajorID, ch.ChargeProjectID, ch.ChargeYear }
                                into temp
                                from stuchcre in temp.DefaultIfEmpty()
                                group stuchcre by stuchcre.ChargeStandardID into g
                                select new
                                {
                                    ChargeStandardID = (Guid?)g.Key,
                                    StudentChargeCount = g.Count()
                                }
                            )
                        on ch.ChargeStandardID equals sc.ChargeStandardID
                        into stuchtemp
                        from stuch in stuchtemp.DefaultIfEmpty()
                        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,
                            StudentChargeCount = stuch.StudentChargeCount == null ? 0 : stuch.StudentChargeCount,
                            StudentCount = chsc.StudentCount == null ? 0 : chsc.StudentCount
                        };

            return query;
        }

        /// <summary>
        /// 根据缴费学年、收费项目查询年级专业信息(全部在校状态学生)
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<GrademajorView> GetGradeMajorViewQueryAble(Expression<Func<EC_ChargeStandard, bool>> expChargeStandard, Expression<Func<CF_Student, bool>> expStudent)
        {
            //注：年级专业与以学生信息表对应的年级专业统计要左连接(防止学生人数为0时，查询不出年级专业信息)
            var query = from gr in GrademajorRepository.Entities
                        join sc in
                            (
                                from stu in StudentRepository.GetList(expStudent)
                                join cl in ClassmajorRepository.Entities
                                on stu.ClassmajorID equals cl.ClassmajorID
                                group cl by cl.GrademajorID into g
                                select new
                                {
                                    GrademajorID = (Guid)g.Key,
                                    StudentCount = g.Count()
                                }
                            )
                        on gr.GrademajorID equals sc.GrademajorID into grtemp
                        from grsc in grtemp.DefaultIfEmpty()
                        join ch in ChargeStandardRepository.GetList(expChargeStandard)
                        on gr.GrademajorID equals ch.GrademajorID into chtemp
                        from chsc in chtemp.DefaultIfEmpty()
                        join fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        where chsc.ChargeStandardID == null
                        select new GrademajorView
                        {
                            GrademajorID = gr.GrademajorID,
                            Code = gr.Code,
                            Name = gr.Name,
                            Abbreviation = gr.Abbreviation,
                            GradeID = gr.GradeID,
                            SemesterID = gr.SemesterID,
                            FacultymajorID = fa.FacultymajorID,
                            FacultymajorCode = fa.Code,
                            FacultymajorName = fa.Name,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            StandardID = fa.StandardID,
                            CollegeID = fa.CF_College.CollegeID,
                            CollegeName = fa.CF_College.Name,
                            CollegeCode = fa.CF_College.No,
                            GraduateSchoolyearID = gr.GraduateSchoolyearID,
                            GraduateValue = gr.CF_Schoolyear.Value,
                            GraduateSchoolyearCode = gr.CF_Schoolyear.Code,
                            Professional = gr.Professional,
                            StudentCount = grsc.StudentCount == null ? 0 : grsc.StudentCount
                        };

            return query;

        }

        /// <summary>
        /// 查询年级专业对应的各在校状态学生信息
        /// </summary>
        /// <param name="expGrademajor"></param>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<StudentsView> GetGradeMajorStudentViewQueryAble(Expression<Func<CF_Grademajor, bool>> expGrademajor, Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from gr in GrademajorRepository.GetList(expGrademajor)
                        join cl in ClassmajorRepository.Entities
                        on gr.GrademajorID equals cl.GrademajorID
                        join stu in StudentRepository.GetList(expStudent)
                        on cl.ClassmajorID equals stu.ClassmajorID
                        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,
                            GradeMajorID = gr.GrademajorID,
                            GradeMajorCode = gr.Code,
                            GradeMajorName = gr.Name,
                            ClassMajorID = cl.ClassmajorID,
                            ClassMajorCode = cl.No,
                            ClassMajorName = cl.Name
                        };

            return query;
        }

        /// <summary>
        /// 查询应收名单信息(根据应收名单、年级专业查询)
        /// </summary>
        /// <param name="expStudentCharge"></param>
        /// <param name="expGrademajor"></param>
        /// <returns></returns>
        public IQueryable<StudentChargeView> GetGradeMajorStudentChargeViewQueryAble(Expression<Func<EC_StudentCharge, bool>> expStudentCharge, Expression<Func<CF_Grademajor, bool>> expGrademajor)
        {
            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 us in UserRepository.Entities
                        on stu.UserID equals us.UserID
                        join cl in ClassmajorRepository.Entities
                        on stu.ClassmajorID equals cl.ClassmajorID
                        join gr in GrademajorRepository.GetList(expGrademajor)
                        on cl.GrademajorID equals gr.GrademajorID
                        join fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        select new StudentChargeView
                        {
                            StudentChargeID = sc.StudentChargeID,
                            UserID = us.UserID,
                            StudentNo = us.LoginID,
                            UserName = us.Name,
                            SexID = stu.SexID,
                            InSchoolStatusID = stu.InSchoolStatusID,
                            StudentStatusID = stu.StudentStatus,
                            ClassID = cl.ClassmajorID,
                            ClassNo = cl.No,
                            ClassName = cl.Name,
                            GradeMajorID = gr.GrademajorID,
                            GradeMajorCode = gr.Code,
                            GradeMajoyStr = gr.Name,
                            GradeStr = gr.GradeID,
                            CollegeID = fa.CF_College.CollegeID,
                            CollegeCode = fa.CF_College.No,
                            CollegeStr = fa.CF_College.Name,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            ChargeYear = sc.ChargeYear,
                            ChargeProjectID = pr.ChargeProjectID,
                            ChargeProjectStr = pr.Name,
                            Amount = sc.Amount,
                            //ActualAmount = sc.Amount, //通过计算得出
                            ChargeTag = sc.ChargeTag,
                            Remark = sc.Remark
                        };

            return query;
        }

    }
}