JW/EMiS.DataLogic/StudentManage/StudentChange/StudentChangeDAL.cs

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

namespace EMIS.DataLogic.StudentManage.StudentChange
{
    public class StudentChangeDAL
    {
        public DifferentDynamicRepository StudentChangeRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }        
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public CampusRepository CampusRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public StudentReportRepository StudentReportRepository { get; set; }

        /// <summary>
        /// 查询对应的学生异动信息View
        /// </summary>
        /// <param name="expStudentChange"></param>
        /// <returns></returns>
        public IQueryable<StudentChangeView> GetStudentChangeViewQueryable(Expression<Func<CF_DifferentDynamic, bool>> expStudentChange)
        {
            var query = from stuch in StudentChangeRepository.GetList(expStudentChange)
                        join stu in StudentRepository.Entities
                        on stuch.UserID equals stu.UserID
                        join sy in SchoolyearRepository.Entities
                        on stuch.SchoolyearID equals sy.SchoolyearID
                        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 col in CollegeRepository.Entities
                        on fa.CollegeID equals col.CollegeID
                        join cam in CampusRepository.Entities
                        on col.CampusID equals cam.CampusID
                        join us in UserRepository.Entities
                        on stu.UserID equals us.UserID
                        join stsy in SchoolyearRepository.Entities
                        on new { SchoolyearID = gr.GradeID.Value, SchoolcodeID = gr.SemesterID.Value }
                        equals new { SchoolyearID = stsy.Years, SchoolcodeID = stsy.SchoolcodeID } into tempstsy
                        from startsy in tempstsy.DefaultIfEmpty()
                        join grsy in SchoolyearRepository.Entities
                        on gr.GraduateSchoolyearID equals grsy.SchoolyearID into tempgrsy
                        from grasy in tempgrsy.DefaultIfEmpty()
                        join rensy in SchoolyearRepository.Entities
                        on stuch.ReturnSchoolyearID equals rensy.SchoolyearID into temprensy
                        from rensy in temprensy.DefaultIfEmpty()
                        join bfcl in ClassmajorRepository.Entities
                        on stuch.BeforeClassmajorID equals bfcl.ClassmajorID into tempbfcl
                        from stubfcl in tempbfcl.DefaultIfEmpty()
                        join afcl in ClassmajorRepository.Entities
                        on stuch.AfterClassmajorID equals afcl.ClassmajorID into tempafcl
                        from stuafcl in tempafcl.DefaultIfEmpty()
                        join sture in StudentReportRepository.Entities
                        on new { stuch.SchoolyearID, stuch.UserID } equals new { sture.SchoolyearID, sture.UserID } into tempsture
                        from sturech in tempsture.DefaultIfEmpty()
                        join crus in UserRepository.Entities
                        on stuch.CreateUserID equals crus.UserID into tempcrus
                        from crusch in tempcrus.DefaultIfEmpty()
                        select new StudentChangeView
                        {
                            StudentChangeID = stuch.StudentChangeID,
                            UserID = stuch.UserID,
                            StudentNo = us.LoginID,
                            Name = us.Name,
                            SexID = stu.SexID,
                            BirthDate = stu.BirthDate,
                            NationID = stu.NationID,
                            PoliticsID = stu.PoliticsID,
                            InSchoolStatusID = stu.InSchoolStatusID,
                            StudentStatus = stu.StudentStatus,
                            GradeID = gr.GradeID,
                            SemesterID = gr.SemesterID,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            ClassNum = cl.ClassNum,
                            ClassmajorID = stu.ClassmajorID,
                            ClassmajorNo = cl.No,
                            ClassmajorName = cl.Name,
                            GrademajorID = cl.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            FacultymajorID = gr.FacultymajorID,
                            FacultymajorCode = fa.Code,
                            FacultymajorName = fa.Name,
                            CollegeID = fa.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            CampusID = col.CampusID,
                            CampusCode = cam.No,
                            CampusName = cam.Name,
                            StartSchoolyearID = startsy.SchoolyearID,
                            StartSchoolyearValue = startsy.Value,
                            StartSchoolyearCode = startsy.Code,
                            SchoolyearID = sy.SchoolyearID,
                            SchoolyearValue = sy.Value,
                            SchoolyearCode = sy.Code,
                            GraduateSchoolyearID = gr.GraduateSchoolyearID,
                            GraduateSchoolyearValue = grasy.Value,
                            GraduateSchoolyearCode = grasy.Code,
                            ReportStatus = sturech.ReportStatus ?? (int)CF_ReportStatus.WaitReport,
                            ChangeTypeID = stuch.ChangeTypeID,
                            BeforeCollegeID = stubfcl.CF_Grademajor.CF_Facultymajor.CollegeID,
                            BeforeCollegeNo = stubfcl.CF_Grademajor.CF_Facultymajor.CF_College.No,
                            BeforeCollegeName = stubfcl.CF_Grademajor.CF_Facultymajor.CF_College.Name,
                            BeforeGradeID = stubfcl.CF_Grademajor.GradeID,
                            BeforeStandardID = stubfcl.CF_Grademajor.CF_Facultymajor.StandardID,
                            BeforeEducationID = stubfcl.CF_Grademajor.CF_Facultymajor.EducationID,
                            BeforeLearningformID = stubfcl.CF_Grademajor.CF_Facultymajor.LearningformID,
                            BeforeLearnSystem = stubfcl.CF_Grademajor.CF_Facultymajor.LearnSystem,
                            BeforeClassmajorID = stuch.BeforeClassmajorID,
                            BeforeClassmajorNo = stubfcl.No,
                            BeforeClassmajorName = stubfcl.Name,
                            BeforeInSchoolStatusID = stuch.BeforeInSchoolStatusID,
                            BeforeStudentStatus = stuch.BeforeStudentStatus,
                            AfterCollegeID = stuafcl.CF_Grademajor.CF_Facultymajor.CollegeID,
                            AfterCollegeNo = stuafcl.CF_Grademajor.CF_Facultymajor.CF_College.No,
                            AfterCollegeName = stuafcl.CF_Grademajor.CF_Facultymajor.CF_College.Name,
                            AfterGradeID = stuafcl.CF_Grademajor.GradeID,
                            AfterStandardID = stuafcl.CF_Grademajor.CF_Facultymajor.StandardID,
                            AfterEducationID = stuafcl.CF_Grademajor.CF_Facultymajor.EducationID,
                            AfterLearningformID = stuafcl.CF_Grademajor.CF_Facultymajor.LearningformID,
                            AfterLearnSystem = stuafcl.CF_Grademajor.CF_Facultymajor.LearnSystem,
                            AfterClassmajorID = stuch.AfterClassmajorID,
                            AfterClassmajorNo = stuafcl.No,
                            AfterClassmajorName = stuafcl.Name,
                            AfterInSchoolStatusID = stuch.AfterInSchoolStatusID,
                            AfterStudentStatus = stuch.AfterStudentStatus,
                            ReturnSchoolyearID = stuch.ReturnSchoolyearID,
                            ReturnSchoolyearValue = rensy.Value,
                            ReturnSchoolyearCode = rensy.Code,
                            ChangeApplyTypeID = stuch.ChangeApplyTypeID,
                            ChangeReasonID = stuch.ChangeReasonID,
                            ChangeDate = stuch.ChangeDate,
                            Description = stuch.Description,
                            ApprovalStatus = stuch.ApprovalStatus,
                            Remark = stuch.Remark,
                            RecordStatus = stuch.RecordStatus,
                            CreateUserID = stuch.CreateUserID,
                            CreateUserName = crusch.Name,
                            CreateTime = stuch.CreateTime,
                            ModifyUserID = stuch.ModifyUserID,
                            ModifyTime = stuch.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的学生异动信息View
        /// </summary>
        /// <param name="expStudentChange"></param>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<StudentChangeView> GetStudentChangeViewQueryable(Expression<Func<CF_DifferentDynamic, bool>> expStudentChange, 
                Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from stuch in StudentChangeRepository.GetList(expStudentChange)
                        join stu in StudentRepository.GetList(expStudent)
                        on stuch.UserID equals stu.UserID
                        join sy in SchoolyearRepository.Entities
                        on stuch.SchoolyearID equals sy.SchoolyearID
                        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 col in CollegeRepository.Entities
                        on fa.CollegeID equals col.CollegeID
                        join cam in CampusRepository.Entities
                        on col.CampusID equals cam.CampusID
                        join us in UserRepository.Entities
                        on stu.UserID equals us.UserID
                        join stsy in SchoolyearRepository.Entities
                        on new { SchoolyearID = gr.GradeID.Value, SchoolcodeID = gr.SemesterID.Value }
                        equals new { SchoolyearID = stsy.Years, SchoolcodeID = stsy.SchoolcodeID } into tempstsy
                        from startsy in tempstsy.DefaultIfEmpty()
                        join grsy in SchoolyearRepository.Entities
                        on gr.GraduateSchoolyearID equals grsy.SchoolyearID into tempgrsy
                        from grasy in tempgrsy.DefaultIfEmpty()
                        join rensy in SchoolyearRepository.Entities
                        on stuch.ReturnSchoolyearID equals rensy.SchoolyearID into temprensy
                        from rensy in temprensy.DefaultIfEmpty()
                        join bfcl in ClassmajorRepository.Entities
                        on stuch.BeforeClassmajorID equals bfcl.ClassmajorID into tempbfcl
                        from stubfcl in tempbfcl.DefaultIfEmpty()
                        join afcl in ClassmajorRepository.Entities
                        on stuch.AfterClassmajorID equals afcl.ClassmajorID into tempafcl
                        from stuafcl in tempafcl.DefaultIfEmpty()
                        join sture in StudentReportRepository.Entities
                        on new { stuch.SchoolyearID, stuch.UserID } equals new { sture.SchoolyearID, sture.UserID } into tempsture
                        from sturech in tempsture.DefaultIfEmpty()
                        join crus in UserRepository.Entities
                        on stuch.CreateUserID equals crus.UserID into tempcrus
                        from crusch in tempcrus.DefaultIfEmpty()
                        select new StudentChangeView
                        {
                            StudentChangeID = stuch.StudentChangeID,
                            UserID = stuch.UserID,
                            StudentNo = us.LoginID,
                            Name = us.Name,
                            SexID = stu.SexID,
                            BirthDate = stu.BirthDate,
                            NationID = stu.NationID,
                            PoliticsID = stu.PoliticsID,
                            InSchoolStatusID = stu.InSchoolStatusID,
                            StudentStatus = stu.StudentStatus,
                            GradeID = gr.GradeID,
                            SemesterID = gr.SemesterID,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            ClassNum = cl.ClassNum,
                            ClassmajorID = stu.ClassmajorID,
                            ClassmajorNo = cl.No,
                            ClassmajorName = cl.Name,
                            GrademajorID = cl.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            FacultymajorID = gr.FacultymajorID,
                            FacultymajorCode = fa.Code,
                            FacultymajorName = fa.Name,
                            CollegeID = fa.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            CampusID = col.CampusID,
                            CampusCode = cam.No,
                            CampusName = cam.Name,
                            StartSchoolyearID = startsy.SchoolyearID,
                            StartSchoolyearValue = startsy.Value,
                            StartSchoolyearCode = startsy.Code,
                            SchoolyearID = sy.SchoolyearID,
                            SchoolyearValue = sy.Value,
                            SchoolyearCode = sy.Code,
                            GraduateSchoolyearID = gr.GraduateSchoolyearID,
                            GraduateSchoolyearValue = grasy.Value,
                            GraduateSchoolyearCode = grasy.Code,
                            ReportStatus = sturech.ReportStatus ?? (int)CF_ReportStatus.WaitReport,
                            ChangeTypeID = stuch.ChangeTypeID,
                            BeforeCollegeID = stubfcl.CF_Grademajor.CF_Facultymajor.CollegeID,
                            BeforeCollegeNo = stubfcl.CF_Grademajor.CF_Facultymajor.CF_College.No,
                            BeforeCollegeName = stubfcl.CF_Grademajor.CF_Facultymajor.CF_College.Name,
                            BeforeGradeID = stubfcl.CF_Grademajor.GradeID,
                            BeforeStandardID = stubfcl.CF_Grademajor.CF_Facultymajor.StandardID,
                            BeforeEducationID = stubfcl.CF_Grademajor.CF_Facultymajor.EducationID,
                            BeforeLearningformID = stubfcl.CF_Grademajor.CF_Facultymajor.LearningformID,
                            BeforeLearnSystem = stubfcl.CF_Grademajor.CF_Facultymajor.LearnSystem,
                            BeforeClassmajorID = stuch.BeforeClassmajorID,
                            BeforeClassmajorNo = stubfcl.No,
                            BeforeClassmajorName = stubfcl.Name,
                            BeforeInSchoolStatusID = stuch.BeforeInSchoolStatusID,
                            BeforeStudentStatus = stuch.BeforeStudentStatus,
                            AfterCollegeID = stuafcl.CF_Grademajor.CF_Facultymajor.CollegeID,
                            AfterCollegeNo = stuafcl.CF_Grademajor.CF_Facultymajor.CF_College.No,
                            AfterCollegeName = stuafcl.CF_Grademajor.CF_Facultymajor.CF_College.Name,
                            AfterGradeID = stuafcl.CF_Grademajor.GradeID,
                            AfterStandardID = stuafcl.CF_Grademajor.CF_Facultymajor.StandardID,
                            AfterEducationID = stuafcl.CF_Grademajor.CF_Facultymajor.EducationID,
                            AfterLearningformID = stuafcl.CF_Grademajor.CF_Facultymajor.LearningformID,
                            AfterLearnSystem = stuafcl.CF_Grademajor.CF_Facultymajor.LearnSystem,
                            AfterClassmajorID = stuch.AfterClassmajorID,
                            AfterClassmajorNo = stuafcl.No,
                            AfterClassmajorName = stuafcl.Name,
                            AfterInSchoolStatusID = stuch.AfterInSchoolStatusID,
                            AfterStudentStatus = stuch.AfterStudentStatus,
                            ReturnSchoolyearID = stuch.ReturnSchoolyearID,
                            ReturnSchoolyearValue = rensy.Value,
                            ReturnSchoolyearCode = rensy.Code,
                            ChangeApplyTypeID = stuch.ChangeApplyTypeID,
                            ChangeReasonID = stuch.ChangeReasonID,
                            ChangeDate = stuch.ChangeDate,
                            Description = stuch.Description,
                            ApprovalStatus = stuch.ApprovalStatus,
                            Remark = stuch.Remark,
                            RecordStatus = stuch.RecordStatus,
                            CreateUserID = stuch.CreateUserID,
                            CreateUserName = crusch.Name,
                            CreateTime = stuch.CreateTime,
                            ModifyUserID = stuch.ModifyUserID,
                            ModifyTime = stuch.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的异动统计信息View
        /// </summary>
        /// <param name="expStudentChange"></param>
        /// <returns></returns>
        public IQueryable<ChangeTotalView> GetChangeTotalViewQueryable(Expression<Func<CF_DifferentDynamic, bool>> expStudentChange)
        {
            var query = from stuch in StudentChangeRepository.GetList(expStudentChange)
                        join stu in StudentRepository.Entities
                        on stuch.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 col in CollegeRepository.Entities
                        on fa.CollegeID equals col.CollegeID
                        join cam in CampusRepository.Entities
                        on col.CampusID equals cam.CampusID
                        group new { stuch, fa, col, cam } by new 
                        {
                            CampusID = cam.CampusID,
                            CampusCode = cam.No,
                            CampusName = cam.Name,
                            CollegeID = col.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            ChangeTypeID = stuch.ChangeTypeID
                        } into g
                        select new ChangeTotalView
                        {
                            CampusID = g.Key.CampusID,
                            CampusCode = g.Key.CampusCode,
                            CampusName = g.Key.CampusName,
                            CollegeID = g.Key.CollegeID,
                            CollegeNo = g.Key.CollegeNo,
                            CollegeName = g.Key.CollegeName,
                            ChangeTypeID = g.Key.ChangeTypeID,
                            ChangeTypeCount = g.Count()
                        };

            return query;
        }

    }
}