JW/EMiS.DataLogic/Common/Students/DifferentDynamicDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EMIS.DataLogic.Repositories;
using EMIS.Entities;
using EMIS.ViewModel.DifferentDynamic;
using System.Linq.Expressions;
using EMIS.DataLogic.Common.CalendarManage;
using EMIS.DataLogic.SystemDAL;
using EMIS.ViewModel;
using EMIS.DataLogic.UniversityManage.SpecialtyClassManage;
using EMIS.Utility;
using EMIS.ViewModel.Students;
using EMIS.ViewModel.ExaminationManage;

namespace EMIS.DataLogic.Common.Students
{
    public class DifferentDynamicDAL
    {
        public DifferentDynamicRepository DifferentDynamicRepository { get; set; }
        public DictionaryDAL DictionaryDAL { 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 CollegeRepository CollegeRepository { get; set; }
        public CampusRepository CampusRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public StudentReportRepository StudentReportRepository { get; set; }
        public EducationMissionRepository EducationMissionRepository { get; set; }//教学任务班主表
        public EducationSchedulingClassRepository EducationSchedulingClassRepository { get; set; }
        public ExaminationPlanRepository ExaminationPlanRepository { get; set; }//考场安排
        public EvaluationSettingRepository EvaluationSettingRepository { get; set; }//教学评价
        public FinalExaminationRepository FinalExaminationRepository { get; set; } //期末设定
        public EducationMissionClassRepository EducationMissionClassRepository { get; set; }
        public ExaminationRoomStudentRepository ExaminationRoomStudentRepository { get; set; }
        public ExaminationRoomLayoutRepository ExaminationRoomLayoutRepository { get; set; }
        public ScoreRepository ScoreRepository { get; set; }
        public SpecialtyPlanRepository SpecialtyPlanRepository { get; set; }
        public StudentsOrderRepository StudentsOrderRepository { get; set; }
        public StudentDistributeRepository StudentDistributeRepository { get; set; }
        public ExecutablePlanRepository ExecutablePlanRepository { get; set; }
        public DifferentDynamicReportRepository DifferentDynamicReportRepository { get; set; }
        public InSchoolSettingRepository InSchoolSettingRepository { get; set; }

        public IQueryable<DifferentDynamicView> GetDifferentDynamicQueryable(Expression<Func<CF_DifferentDynamic, bool>> filter,
            Expression<Func<CF_Grademajor, bool>> gradeFilter = null,
            Expression<Func<CF_Facultymajor, bool>> facultyFilter = null,
            Expression<Func<CF_College, bool>> collegeFilter = null)
        {
            if (gradeFilter == null)
            {
                gradeFilter = (x => true);
            }
            if (facultyFilter == null)
            {
                facultyFilter = (x => true);
            }
            if (collegeFilter == null)
            {
                collegeFilter = (x => true);
            }
            var currentStudentReportQuery = from c in StudentReportRepository.GetList(w => w.CF_Schoolyear.IsCurrent == true)
                                            join d in DictionaryDAL.GetDictionaryItemQuery(DictionaryItem.CF_ReportStatus) on c.ReportStatus equals d.Value
                                            select new
                                            {
                                                c.UserID,
                                                c.ReportStatus,
                                                d.Name,
                                            };


            var query = from c in DifferentDynamicRepository.Entities.Where(filter)
                        join studentUser in UserRepository.Entities on c.UserID equals studentUser.UserID 
                        join student in StudentRepository.Entities on studentUser.UserID equals student.UserID 
                        join studentClass in ClassmajorRepository.Entities on student.ClassmajorID equals studentClass.ClassmajorID
                        join grade in GrademajorRepository.Entities.Where(gradeFilter) on studentClass.GrademajorID equals grade.GrademajorID 
                        join faculty in FacultymajorRepository.Entities.Where(facultyFilter) on grade.FacultymajorID equals faculty.FacultymajorID
                        join college in CollegeRepository.Entities.Where(collegeFilter) on faculty.CollegeID equals college.CollegeID 
                        join creator in UserRepository.Entities on c.CreateUserID equals creator.UserID into tmp from creator in tmp.DefaultIfEmpty()
                        join j1 in ClassmajorRepository.Entities on c.BeforeClassmajorID equals j1.ClassmajorID into jv1
                        from classmajor in jv1.DefaultIfEmpty()
                        join aj1 in ClassmajorRepository.Entities on c.AfterClassmajorID equals aj1.ClassmajorID into ajv1
                        from afterClassmajor in ajv1.DefaultIfEmpty()
                        join schoolyear in SchoolyearRepository.Entities on c.SchoolyearID equals schoolyear.SchoolyearID
                        join rd5 in SchoolyearRepository.Entities on c.ReturnSchoolyearID equals rd5.SchoolyearID into dv5
                        from rschoolyear in dv5.DefaultIfEmpty()
                        join j2 in currentStudentReportQuery on c.UserID equals j2.UserID into jv2
                        from currentStudentReport in jv2.DefaultIfEmpty()

                        select new DifferentDynamicView
                        {
                            EntityID = c.StudentChangeID,
                            Name = studentUser.Name,
                            SchoolyearID = c.SchoolyearID,
                            SchoolyearCode = schoolyear.Code,
                            UserID = c.UserID,
                            LoginID = studentUser.LoginID,
                            ApplyTypeID = c.ChangeApplyTypeID,
                            ApplyRemark = c.Description,
                            //Source = c.Source,
                            DifferentDynamicType = c.ChangeTypeID,
                            //Direction = c.Direction,
                            AfterClassmajorID = c.AfterClassmajorID,
                            AfterInSchoolStatusID = c.AfterInSchoolStatusID,
                            ClassmajorID = c.BeforeClassmajorID,
                            ClassmajorName = classmajor.Name,
                            InSchoolStatusID = c.BeforeInSchoolStatusID,
                            //AfterStandardID = c.AfterStandardID,
                            AfterStudentStatus = c.AfterStudentStatus,
                            EntityApprovalStatus = c.ApprovalStatus,
                            BeginTime = c.ChangeDate,
                            EndTime = c.ChangeDate,
                            Reason = c.ChangeReasonID,
                            Remark = c.Remark,
                            ReturnSchoolyearID = c.ReturnSchoolyearID,
                            ReturnSchoolyearCode = rschoolyear.Code,
                            //StandardID = c.StandardID,
                            StudentStatus = c.BeforeStudentStatus,
                            AfterClassmajorName = afterClassmajor.Name,
                            EntityCreateTime = c.ChangeDate,
                            EntityApplyDate = c.ChangeDate,
                            EntityCreateUserID = c.CreateUserID,
                            CreateUser = creator.Name,
                            CollegeID = c.CF_Student.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CollegeID,
                            ReportStatus = currentStudentReport.ReportStatus,
                            ReportStatusName = currentStudentReport.Name
                        };

            return query;
        }

        public IQueryable<DifferentDynamicStatisticsView> GetDifferentDynamicStatisticsQueryable(Expression<Func<CF_DifferentDynamic, bool>> filter)
        {
            var query = from c in DifferentDynamicRepository.GetList(filter)
                        join d in DictionaryDAL.GetDictionaryItemQuery(typeof(CF_ChangeType).Name) on c.ChangeTypeID equals d.Value
                        group c by new
                        {
                            c.CF_Student.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CF_College.CampusID,
                            c.CF_Student.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CollegeID,
                            CollegeName = c.CF_Student.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CF_College.Name,
                            c.ChangeTypeID,
                            DifferentDynamicTypeName = d.Name
                        } into g
                        select new DifferentDynamicStatisticsView
                        {
                            CampusID = g.Key.CampusID,
                            CollegeID = g.Key.CollegeID,
                            CollegeName = g.Key.CollegeName,
                            DifferentDynamicType = g.Key.ChangeTypeID,
                            DifferentDynamicTypeName = g.Key.DifferentDynamicTypeName,
                            Quantity = g.Count(),
                        };

            return query;
        }

        #region 异动审核完成时对于异动前班级对应的记录，删掉那个异动的学生的名单，对于异动后班级对应的记录，增加这个学生的名单
        #region 1.0 教学任务
        //教学任务
        public IQueryable<StudentsView> GetEducationSchedulingClassStudent(Expression<Func<CF_DifferentDynamic, bool>> filter)
        {
            var query = from df in DifferentDynamicRepository.GetList(filter)
                        join iss in InSchoolSettingRepository.Entities on df.AfterInSchoolStatusID equals iss.InSchoolStatusID into diss
                        from iss in diss.DefaultIfEmpty()
                        join biss in InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true) 
                        on df.BeforeInSchoolStatusID equals biss.InSchoolStatusID 
                        join sy in SchoolyearRepository.Entities on df.SchoolyearID equals sy.SchoolyearID
                        from em in EducationMissionRepository.Entities.Where(x => x.CF_Schoolyear.Value >= sy.Value)
                        join emc in EducationMissionClassRepository.Entities on em.EducationMissionID equals emc.EducationMissionID
                        from emccc in emc.CF_Classmajor.Where(x => x.ClassmajorID == df.BeforeClassmajorID)
                        join eds in EducationSchedulingClassRepository.Entities on emc.EducationMissionClassID equals eds.EducationMissionClassID
                        from edscc in eds.CF_Student.Where(x => x.UserID == df.UserID.Value)
                        where df.AfterClassmajorID != df.BeforeClassmajorID || !(iss.IsSelected ?? false)
                        select new StudentsView
                        {
                            TepTableID = eds.EducationSchedulingClassID,
                            UserID = df.UserID.Value,
                        };

            return query;
        }

        #endregion

        #region 2.0 考场安排
        // 考场安排
        public IQueryable<StudentsView> GetExaminationPlanStudent(Expression<Func<CF_DifferentDynamic, bool>> filter)
        {
            //期末设定、补考设定为未开放及可录入状态的才进行学生信息调整
            //对于补考的记录，由于并没有明确的任务班，因此名单无法删除
            Expression<Func<EX_ExaminationPlan, bool>> exp =
                x => x.RecordStatus != (int)CF_ApprovalStatus.Submitted;

            var query = from df in DifferentDynamicRepository.GetList(filter)
                        join iss in InSchoolSettingRepository.Entities on df.AfterInSchoolStatusID equals iss.InSchoolStatusID into diss
                        from iss in diss.DefaultIfEmpty()
                        join biss in InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true) on df.BeforeInSchoolStatusID equals biss.InSchoolStatusID 
                        join sy in SchoolyearRepository.Entities on df.SchoolyearID equals sy.SchoolyearID
                        from em in EducationMissionRepository.Entities.Where(x => x.CF_Schoolyear.Value >= sy.Value)
                        //on df.CF_Schoolyear.Value >= schoolYearValue equals em.CF_Schoolyear.Value >= schoolYearValue
                        join emc in EducationMissionClassRepository.Entities on em.EducationMissionID equals emc.EducationMissionID
                        from emccc in emc.CF_Classmajor.Where(x => x.ClassmajorID == df.BeforeClassmajorID)
                        join explan in ExaminationPlanRepository.GetList(exp) on (Guid)emc.EducationMissionID equals (Guid)explan.EducationMissionID
                        from exStudent in explan.CF_Student.Where(x => x.UserID == df.UserID.Value)
                        where df.AfterClassmajorID != df.BeforeClassmajorID || !(iss.IsSelected ?? false)
                        select new StudentsView
                        {
                            TepTableID = explan.ExaminationPlanID,
                            UserID = df.UserID.Value,
                        };

            return query;
        }

        public IQueryable<StudentsView> GetExaminationRoomStudent(Expression<Func<CF_DifferentDynamic, bool>> filter)
        {
            //期末设定、补考设定为未开放及可录入状态的才进行学生信息调整      
            //对于补考的记录，由于并没有明确的任务班，因此名单无法删除 
            Expression<Func<EX_ExaminationPlan, bool>> exp =
                x => x.RecordStatus != (int)CF_ApprovalStatus.Submitted;

            var query = from df in DifferentDynamicRepository.GetList(filter)
                        join iss in InSchoolSettingRepository.Entities on df.AfterInSchoolStatusID equals iss.InSchoolStatusID into diss
                        from iss in diss.DefaultIfEmpty()
                        join biss in InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true) on df.AfterInSchoolStatusID equals biss.InSchoolStatusID 
                        join sy in SchoolyearRepository.Entities on df.SchoolyearID equals sy.SchoolyearID
                        from em in EducationMissionRepository.Entities.Where(x => x.CF_Schoolyear.Value >= sy.Value)
                        //on df.CF_Schoolyear.Value >= schoolYearValue equals em.CF_Schoolyear.Value >= schoolYearValue
                        join emc in EducationMissionClassRepository.Entities on em.EducationMissionID equals emc.EducationMissionID
                        from emccc in emc.CF_Classmajor.Where(x => x.ClassmajorID == df.BeforeClassmajorID)
                        join explan in ExaminationPlanRepository.GetList(exp) on (Guid)emc.EducationMissionID equals (Guid)explan.EducationMissionID
                        from exRoomLayout in explan.EX_ExaminationRoomLayout.DefaultIfEmpty()
                        from exStudent in exRoomLayout.EX_ExaminationRoomStudent.Where(x => x.UserID == df.UserID)
                        where df.AfterClassmajorID != df.BeforeClassmajorID || !(iss.IsSelected ?? false)
                        select new StudentsView
                        {
                            TepTableID = exStudent.ExaminationRoomStudentID,
                            UserID = df.UserID.Value,
                        };

            return query;
        }
        #endregion

        #region 3.0 成绩管理
        //成绩管理
        public IQueryable<StudentsView> GetScoreStudent(Expression<Func<CF_DifferentDynamic, bool>> filter)
        {
            //期末设定、补考设定为未开放及可录入状态的才进行学生信息调整       
            var query = from df in DifferentDynamicRepository.GetList(filter)
                        join iss in InSchoolSettingRepository.Entities on df.AfterInSchoolStatusID equals iss.InSchoolStatusID into diss
                        from iss in diss.DefaultIfEmpty()
                        join biss in InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true) on df.BeforeInSchoolStatusID equals biss.InSchoolStatusID 
                        join sy in SchoolyearRepository.Entities on df.SchoolyearID equals sy.SchoolyearID
                        from em in EducationMissionRepository.Entities.Where(x => x.CF_Schoolyear.Value >= sy.Value)
                        //on df.CF_Schoolyear.Value >= schoolYearValue equals em.CF_Schoolyear.Value >= schoolYearValue
                        join emc in EducationMissionClassRepository.Entities on em.EducationMissionID equals emc.EducationMissionID
                        from emccc in emc.CF_Classmajor.Where(x => x.ClassmajorID == df.BeforeClassmajorID)
                        from fex in FinalExaminationRepository.Entities.Where(x => (x.ApprovalStatus == (int)CF_ScoreState.CanInput 
                            || x.ApprovalStatus == (int)CF_ScoreState.NoCanInput)
                            && x.ExaminationType == (int)CF_ExaminationType.FinalExamination && x.EducationMissionID == em.EducationMissionID 
                            && x.CF_Schoolyear.Value >= sy.Value)
                        //on em.EducationMissionID equals fex.EducationMissionID
                        from fexcc in fex.ER_FinalExaminationStudent.Where(x => x.UserID == df.UserID.Value)
                        where df.AfterClassmajorID != df.BeforeClassmajorID || !(iss.IsSelected ?? false)
                        select new StudentsView
                        {
                            TepTableID = fex.FinalExaminationID,
                            UserID = df.UserID.Value,
                        };

            return query;

        }
        #endregion

        #region 4.0 教学评价
        //教学评价
        public IQueryable<StudentsView> GetEvaluationSettingStudent(Expression<Func<CF_DifferentDynamic, bool>> filter)
        {
            //评价设定为未开放状态才进行学生信息调整
            Expression<Func<EM_EvaluationSetting, bool>> exp = x => x.OpenStatus == (int)CF_YesOrNoStatus.No;
            var query = from df in DifferentDynamicRepository.GetList(filter)
                        join iss in InSchoolSettingRepository.Entities on df.AfterInSchoolStatusID equals iss.InSchoolStatusID into diss
                        from iss in diss.DefaultIfEmpty()
                        join biss in InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true) on df.BeforeInSchoolStatusID equals biss.InSchoolStatusID 
                        join sy in SchoolyearRepository.Entities on df.SchoolyearID equals sy.SchoolyearID
                        from em in EducationMissionRepository.Entities.Where(x => x.CF_Schoolyear.Value >= sy.Value)
                        //  on df.CF_Schoolyear.Value >= schoolYearValue equals em.CF_Schoolyear.Value >= schoolYearValue
                        join emc in EducationMissionClassRepository.Entities on em.EducationMissionID equals emc.EducationMissionID
                        from emccc in emc.CF_Classmajor.Where(x => x.ClassmajorID == df.BeforeClassmajorID)
                        join eds in EvaluationSettingRepository.GetList(exp) on emc.EducationMissionClassID equals eds.EducationMissionClassID
                        from edscc in eds.CF_Student.Where(x => x.UserID == df.UserID.Value)
                        where df.AfterClassmajorID != df.BeforeClassmajorID || !(iss.IsSelected ?? false)
                        select new StudentsView
                        {
                            TepTableID = eds.EvaluationSettingID,
                            UserID = df.UserID.Value,
                        };

            return query;
        }
        #endregion

        #region 5.0 教学任务Add

        //教学任务
        public IQueryable<StudentRelateCourseView> GetStudentRelateCourseViewQueryable(Expression<Func<CF_DifferentDynamic, bool>> filter)
        {
            var sql = (from change in DifferentDynamicRepository.GetList(filter)
                       join student in StudentRepository.Entities on change.UserID equals student.UserID 
                       join user in UserRepository.Entities on student.UserID equals user.UserID
                       join classmajor in ClassmajorRepository.Entities on change.AfterClassmajorID equals classmajor.ClassmajorID
                       join currentYear in SchoolyearRepository.Entities on change.SchoolyearID equals currentYear.SchoolyearID 
                       from schoolyear in SchoolyearRepository.Entities.Where(x => x.Value >= currentYear.Value)
                       //查出相关的课程
                       join missionClass in
                           (
                               from mission in EducationMissionRepository.Entities
                               join missionClass in EducationMissionClassRepository.Entities on mission.EducationMissionID equals missionClass.EducationMissionID
                               from classmajor in missionClass.CF_Classmajor
                               join schedulingClass in EducationSchedulingClassRepository.Entities on missionClass.EducationMissionClassID equals schedulingClass.EducationMissionClassID
                               group new { missionClass, schedulingClass }
                               by new { mission.SchoolyearID, missionClass.CoursematerialID, missionClass.TeachingModeID, classmajor.ClassmajorID, missionClass.OptionalCourseTypeID, missionClass.HandleModeID }
                                   into g
                                   select new
                                   {
                                       g.Key.SchoolyearID,
                                       g.Key.CoursematerialID,
                                       g.Key.ClassmajorID,
                                       g.Key.TeachingModeID,
                                       g.Key.OptionalCourseTypeID,
                                       g.Key.HandleModeID,
                                       SchedulingClassList = g.Select(x => new StudentRelateSchedulingClassView
                                       {
                                           EducationSchedulingClassID = x.schedulingClass.EducationSchedulingClassID,
                                           EducationMissionClassName = x.missionClass.Name,
                                           ClassmajorIDList = x.schedulingClass.CF_Student.Select(w => w.ClassmajorID),
                                           StudentCount = x.schedulingClass.CF_Student.Count(),
                                           MinLoginID = x.schedulingClass.CF_Student.Min(w => w.Sys_User.LoginID),
                                           MaxLoginID = x.schedulingClass.CF_Student.Max(w => w.Sys_User.LoginID)
                                       })
                                   }) on new { SchoolyearID = (Guid?)schoolyear.SchoolyearID, classmajor.ClassmajorID }
                                equals new { missionClass.SchoolyearID, missionClass.ClassmajorID }
                       //查已经加入的任务班并排除，防止重复插入
                       join schedulingClass in
                           (
                               from mission in EducationMissionRepository.Entities
                               join missionClass in EducationMissionClassRepository.Entities on mission.EducationMissionID equals missionClass.EducationMissionID
                               from classmajor in missionClass.CF_Classmajor
                               join schedulingClass in EducationSchedulingClassRepository.Entities on missionClass.EducationMissionClassID equals schedulingClass.EducationMissionClassID
                               from student in schedulingClass.CF_Student
                               select new { mission.SchoolyearID, missionClass.CoursematerialID, missionClass.TeachingModeID, classmajor.ClassmajorID, missionClass.OptionalCourseTypeID, missionClass.HandleModeID, student.UserID }
                               ) on new { missionClass.SchoolyearID, missionClass.CoursematerialID, missionClass.TeachingModeID, missionClass.ClassmajorID, missionClass.OptionalCourseTypeID, missionClass.HandleModeID, student.UserID }
                           equals new { schedulingClass.SchoolyearID, schedulingClass.CoursematerialID, schedulingClass.TeachingModeID, schedulingClass.ClassmajorID, schedulingClass.OptionalCourseTypeID, schedulingClass.HandleModeID, schedulingClass.UserID }
                           into dschedulingClass
                       from schedulingClass in dschedulingClass.DefaultIfEmpty()
                       where schedulingClass.SchoolyearID == null
                       select new StudentRelateCourseView
                       {
                           UserID = student.UserID,
                           LoginID = user.LoginID,
                           ClassmajorID = student.ClassmajorID,
                           SchoolyearID = missionClass.SchoolyearID,
                           CoursematerialID = missionClass.CoursematerialID,
                           TeacheModeID = missionClass.TeachingModeID,
                           OptionalCourseTypeID = missionClass.OptionalCourseTypeID,
                           HandleModeID = missionClass.HandleModeID,
                           StudentRelateSchedulingClassViewList = missionClass.SchedulingClassList
                       });

            return sql;
        }

        //教学任务
        public IList<StudentsView> GetEducationSchedulingClassStudentInsert(Expression<Func<CF_DifferentDynamic, bool>> filter)
        {
            var result = new List<StudentsView>();
            //这块只针对必修课，因为只有必修课才会强制必须要上，选修课需要学生自己去报名
            #region 情况1：如果找到同班学生，并且该组有学号小于他的人的，加入最后一组
            var query1 = from tem in
                             (from dd in DifferentDynamicRepository.GetList(filter)
                             join u in UserRepository.Entities on dd.UserID equals u.UserID
                             join sy in SchoolyearRepository.Entities on dd.SchoolyearID equals sy.SchoolyearID
                             from em in
                                 (
                                     from em in EducationMissionRepository.Entities
                                     where em.CF_Schoolyear.Value >= sy.Value
                                     select new { em.EducationMissionID, em.SchoolyearID, em.CF_Schoolyear.Value }
                                     )
                             join emc in EducationMissionClassRepository.GetList(x => x.HandleModeID == (int)CF_HandleMode.RequiredCourse) on em.EducationMissionID equals emc.EducationMissionID
                             from emcc in emc.CF_Classmajor.Where(x => x.ClassmajorID == dd.AfterClassmajorID)
                             join esc in EducationSchedulingClassRepository.Entities on emc.EducationMissionClassID equals esc.EducationMissionClassID
                             from s in esc.CF_Student.Where(x => x.ClassmajorID == dd.AfterClassmajorID)
                             from su in UserRepository.Entities.Where(x => s.UserID == x.UserID && u.LoginID.CompareTo(x.LoginID) > 0)
                             group emc by new { ID = dd.StudentChangeID, em.SchoolyearID, emcc.ClassmajorID, emc.CoursematerialID, emc.TeachingModeID } into g
                             select new
                             {
                                 g.Key.ID,
                                 g.Key.SchoolyearID,
                                 g.Key.ClassmajorID,
                                 g.Key.CoursematerialID,
                                 g.Key.TeachingModeID,
                                 MinName = g.Min(x => x.Name)
                             }
                                )
                         join dd in DifferentDynamicRepository.Entities on tem.ID equals dd.StudentChangeID
                        join iss in InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true) on dd.AfterInSchoolStatusID equals iss.InSchoolStatusID
                        join em in EducationMissionRepository.Entities on tem.SchoolyearID equals em.SchoolyearID
                        join emc in EducationMissionClassRepository.GetList(x => x.HandleModeID == (int)CF_HandleMode.RequiredCourse)
                          on new { EducationMissionID = (Guid?)em.EducationMissionID, tem.TeachingModeID, Name = tem.MinName }
                          equals new { emc.EducationMissionID, emc.TeachingModeID, emc.Name }
                        join esc in EducationSchedulingClassRepository.Entities on emc.EducationMissionClassID equals esc.EducationMissionClassID
                         group esc by new { ID = dd.StudentChangeID, dd.UserID, em.SchoolyearID, emc.CoursematerialID, emc.TeachingModeID } into g
                        select new
                        {
                            g.Key.ID,
                            g.Key.UserID,
                            g.Key.SchoolyearID,
                            g.Key.CoursematerialID,
                            g.Key.TeachingModeID,
                            MinEducationSchedulingClassID = g.Min(x => x.EducationSchedulingClassID.ToString())
                        };
            result.AddRange(query1.ToList().Select(x => new StudentsView { TepTableID = new Guid(x.MinEducationSchedulingClassID), UserID = x.UserID.Value }).ToList());
            #endregion

            #region 情况2：如果找到同班学生，并且没有学号小于他的人的，加入第一组
            var query2MissionClassQuery = (from tdd in DifferentDynamicRepository.Entities
                              join sy in SchoolyearRepository.Entities on tdd.SchoolyearID equals sy.SchoolyearID
                              from em in
                                  (
                                      from em in EducationMissionRepository.Entities
                                      where em.CF_Schoolyear.Value >= sy.Value
                                      select new { em.EducationMissionID, em.SchoolyearID, em.CF_Schoolyear.Value }
                                      )
                              join emc in EducationMissionClassRepository.GetList(x => x.HandleModeID == (int)CF_HandleMode.RequiredCourse) on em.EducationMissionID equals emc.EducationMissionID
                              from emcc in emc.CF_Classmajor
                              join esc in EducationSchedulingClassRepository.Entities on emc.EducationMissionClassID equals esc.EducationMissionClassID
                              from s in esc.CF_Student
                              join su in UserRepository.Entities on s.UserID equals su.UserID 
                              where emcc.ClassmajorID == tdd.AfterClassmajorID && s.ClassmajorID == tdd.AfterClassmajorID  
                              group new { emc, su } by new { ID = tdd.StudentChangeID, em.SchoolyearID, emcc.ClassmajorID, emc.CoursematerialID, emc.TeachingModeID } into g
                              select new
                              {
                                  g.Key.ID,
                                  g.Key.SchoolyearID,
                                  g.Key.ClassmajorID,
                                  g.Key.CoursematerialID,
                                  g.Key.TeachingModeID,
                                  MinName = g.Max(x => x.emc.Name),
                                  MinLoginID = g.Min(x => x.su.LoginID)
                              }
                                 );
            var query2 = from dd in DifferentDynamicRepository.GetList(filter)
                         join iss in InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true) on dd.AfterInSchoolStatusID equals iss.InSchoolStatusID
                         join u in UserRepository.Entities on dd.UserID equals u.UserID 
                         from tem in
                             query2MissionClassQuery.Where(x => dd.StudentChangeID == x.ID && u.LoginID.CompareTo(x.MinLoginID) < 0)
                         join em in EducationMissionRepository.Entities on tem.SchoolyearID equals em.SchoolyearID
                         join emc in EducationMissionClassRepository.GetList(x => x.HandleModeID == (int)CF_HandleMode.RequiredCourse)
                           on new { EducationMissionID = (Guid?)em.EducationMissionID, tem.TeachingModeID, Name = tem.MinName }
                           equals new { emc.EducationMissionID, emc.TeachingModeID, emc.Name }
                         join esc in EducationSchedulingClassRepository.Entities on emc.EducationMissionClassID equals esc.EducationMissionClassID
                         group esc by new { ID = dd.StudentChangeID, dd.UserID, em.SchoolyearID, emc.CoursematerialID, emc.TeachingModeID } into g
                         select new
                         {
                             g.Key.ID,
                             g.Key.UserID,
                             g.Key.SchoolyearID,
                             g.Key.CoursematerialID,
                             g.Key.TeachingModeID,
                             MinEducationSchedulingClassID = g.Max(x => x.EducationSchedulingClassID.ToString())
                         };
            result.AddRange(query2.ToList().Select(x => new StudentsView { TepTableID = new Guid(x.MinEducationSchedulingClassID), UserID = x.UserID.Value }).ToList());
            #endregion

            #region 情况3：没有找到同班学生，加入最少人的第一组（这种情况通常有人的组都是另一个班的）
            var query3MissionClassQuery = (from tdd in DifferentDynamicRepository.Entities
                              join sy in SchoolyearRepository.Entities on tdd.SchoolyearID equals sy.SchoolyearID
                              from em in
                                  (
                                      from em in EducationMissionRepository.Entities
                                      where em.CF_Schoolyear.Value >= sy.Value
                                      select new { em.EducationMissionID, em.SchoolyearID, em.CF_Schoolyear.Value }
                                      )
                              join emc in EducationMissionClassRepository.GetList(x => x.HandleModeID == (int)CF_HandleMode.RequiredCourse) on em.EducationMissionID equals emc.EducationMissionID
                              from emcc in emc.CF_Classmajor.Where(x => x.ClassmajorID == tdd.AfterClassmajorID)
                              where !emc.EM_EducationSchedulingClass.SelectMany(x => x.CF_Student).Any(x => x.ClassmajorID == tdd.AfterClassmajorID)
                              group emc by new { ID = tdd.StudentChangeID, em.SchoolyearID, emcc.ClassmajorID, emc.CoursematerialID, emc.TeachingModeID } into g
                              select new
                              {
                                  g.Key.ID,
                                  g.Key.SchoolyearID,
                                  g.Key.ClassmajorID,
                                  g.Key.CoursematerialID,
                                  g.Key.TeachingModeID,
                                  MinStudentCount = g.Min(x => x.EM_EducationSchedulingClass.SelectMany(w => w.CF_Student).Count())
                              }
                                 );
            var query3 = from dd in DifferentDynamicRepository.GetList(filter)
                         join iss in InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true) on dd.AfterInSchoolStatusID equals iss.InSchoolStatusID
                         join u in UserRepository.Entities on dd.UserID equals u.UserID
                         from tem in
                             query3MissionClassQuery.Where(x => x.ID == dd.StudentChangeID)
                         join em in EducationMissionRepository.Entities on tem.SchoolyearID equals em.SchoolyearID
                         join emc in EducationMissionClassRepository.GetList(x => x.HandleModeID == (int)CF_HandleMode.RequiredCourse)
                           on new { EducationMissionID = (Guid?)em.EducationMissionID, tem.TeachingModeID }
                           equals new { emc.EducationMissionID, emc.TeachingModeID }
                         from emccc in emc.CF_Classmajor.Where(x => x.ClassmajorID == tem.ClassmajorID)
                         join emcsc in
                             (
                                 from emc in EducationMissionClassRepository.Entities
                                 join esc in EducationSchedulingClassRepository.Entities on emc.EducationMissionClassID equals esc.EducationMissionClassID into desc
                                 from eesc in desc.DefaultIfEmpty()
                                 from esccs in eesc.CF_Student.DefaultIfEmpty()
                                 group esccs by emc.EducationMissionClassID into g
                                 select new { EducationMissionClassID = g.Key, StudentCount = g.Sum(x => x.UserID == null ? 0 : 1) }
                                 ) on new { emc.EducationMissionClassID, StudentCount = tem.MinStudentCount }
                                equals new { emcsc.EducationMissionClassID, emcsc.StudentCount }
                         join esc in EducationSchedulingClassRepository.Entities on emc.EducationMissionClassID equals esc.EducationMissionClassID
                         group esc by new { ID = dd.StudentChangeID, dd.UserID, em.SchoolyearID, emc.CoursematerialID, emc.TeachingModeID } into g
                         select new
                         {
                             g.Key.ID,
                             g.Key.UserID,
                             g.Key.SchoolyearID,
                             g.Key.CoursematerialID,
                             g.Key.TeachingModeID,
                             MinEducationSchedulingClassID = g.Min(x => x.EducationSchedulingClassID.ToString())
                         };
            result.AddRange(query3.ToList().Select(x => new StudentsView { TepTableID = new Guid(x.MinEducationSchedulingClassID), UserID = x.UserID.Value }).ToList());
            #endregion

            //var query = from df in DifferentDynamicRepository.GetList(filter)
            //            join em in EducationMissionRepository.GetList(x => x.CF_Schoolyear.Value >= schoolYearValue)
            //           on df.CF_Schoolyear.Value >= schoolYearValue equals em.CF_Schoolyear.Value >= schoolYearValue
            //            join emc in EducationMissionClassRepository.Entities on em.EducationMissionID equals emc.EducationMissionID
            //            from emccc in emc.CF_Classmajor.Where(x => x.ClassmajorID == df.AfterClassmajorID)
            //            join eds in EducationSchedulingClassRepository.Entities on emc.EducationMissionClassID equals eds.EducationMissionClassID
            //            from edscc in eds.CF_Student.Where(x => x.UserID == df.UserID).DefaultIfEmpty()
            //            where edscc.UserID == null
            //            && df.AfterClassmajorID != df.ClassmajorID
            //            select new StudentsView
            //            {
            //                TepTableID = eds.EducationSchedulingClassID,
            //                UserID = df.UserID,
            //            };

            return result;
        }

        #endregion

        #region 6.0 考场安排Add
        // 考场安排
        public IQueryable<StudentsView> GetExaminationPlanStudentInsert(Expression<Func<CF_DifferentDynamic, bool>> filter, Expression<Func<EM_EducationMissionClass, bool>> missionClassFilter)
        {
            var q = (from df in DifferentDynamicRepository.GetList(filter)
                     join iss in InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true) on df.AfterInSchoolStatusID equals iss.InSchoolStatusID
                     join sy in SchoolyearRepository.Entities on df.SchoolyearID equals sy.SchoolyearID
                     from em in EducationMissionRepository.Entities.Where(x => x.CF_Schoolyear.Value >= sy.Value)
                     //on df.CF_Schoolyear.Value >= schoolYearValue equals em.CF_Schoolyear.Value >= schoolYearValue
                     join emc in EducationMissionClassRepository.Entities.Where(missionClassFilter) on em.EducationMissionID equals emc.EducationMissionID
                     join ep in ExaminationPlanRepository.GetList(x => x.RecordStatus == (int)CF_ApprovalStatus.NotSubmitted) on emc.EducationMissionID equals ep.EducationMissionID
                     //如果有异动后班级，考试计划只插入到该班。（原来有1、2班的数据的话会两个班都插入）
                     where (df.AfterClassmajorID == null ? 1 == 1 : ep.ClassmajorID == df.AfterClassmajorID)
                     select new StudentsView 
                     { 
                         TepTableID = ep.ExaminationPlanID,
                         UserID = df.UserID.Value
                     }
                );
            return q;
        }
        #endregion

        #region 7.0 成绩管理Add
        //成绩管理
        public IQueryable<StudentsView> GetScoreStudentInsert(Expression<Func<CF_DifferentDynamic, bool>> filter, Expression<Func<EM_EducationMissionClass, bool>> missionClassFilter)
        {
            //期末设定、补考设定为未开放及可录入状态的才进行学生信息调整
            var query = from df in DifferentDynamicRepository.GetList(filter)
                        join iss in InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true) on df.AfterInSchoolStatusID equals iss.InSchoolStatusID
                        join sy in SchoolyearRepository.Entities on df.SchoolyearID equals sy.SchoolyearID
                        from em in EducationMissionRepository.Entities.Where(x => x.CF_Schoolyear.Value >= sy.Value)
                        //on df.CF_Schoolyear.Value >= schoolYearValue equals em.CF_Schoolyear.Value >= schoolYearValue
                        join emc in EducationMissionClassRepository.Entities.Where(missionClassFilter) on em.EducationMissionID equals emc.EducationMissionID
                        from emccc in emc.CF_Classmajor.Where(x => x.ClassmajorID == df.AfterClassmajorID)
                        from fex in FinalExaminationRepository.Entities.Where(x => (
                            x.ApprovalStatus == (int)CF_ScoreState.CanInput || x.ApprovalStatus == (int)CF_ScoreState.NoCanInput)
                            && x.ExaminationType == (int)CF_ExaminationType.FinalExamination && x.CF_Schoolyear.Value >= sy.Value 
                            && x.EducationMissionID == em.EducationMissionID)
                        //on em.EducationMissionID equals fex.EducationMissionID
                        from fexcc in fex.ER_FinalExaminationStudent.Where(x => x.UserID == df.UserID).DefaultIfEmpty()
                        where fexcc.UserID == null
                        select new StudentsView
                        {
                            TepTableID = fex.FinalExaminationID,
                            UserID = df.UserID.Value,
                        };
            return query;

        }
        #endregion

        #region 8.0 教学评价Add
        //教学评价
        public IQueryable<StudentsView> GetEvaluationSettingStudentInsert(Expression<Func<CF_DifferentDynamic, bool>> filter)
        {
            //评价设定为未开放状态才进行学生信息调整
            Expression<Func<EM_EvaluationSetting, bool>> exp = x => x.OpenStatus == (int)CF_YesOrNoStatus.No;
            var query = from df in DifferentDynamicRepository.GetList(filter)
                        join iss in InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true) on df.AfterInSchoolStatusID equals iss.InSchoolStatusID
                        join sy in SchoolyearRepository.Entities on df.SchoolyearID equals sy.SchoolyearID
                        from em in EducationMissionRepository.Entities.Where(x => x.CF_Schoolyear.Value >= sy.Value)
                        //on df.CF_Schoolyear.Value >= schoolYearValue equals em.CF_Schoolyear.Value >= schoolYearValue
                        join emc in EducationMissionClassRepository.Entities on em.EducationMissionID equals emc.EducationMissionID
                        from emccc in emc.CF_Classmajor.Where(x => x.ClassmajorID == df.AfterClassmajorID)
                        join eds in EvaluationSettingRepository.GetList(exp) on emc.EducationMissionClassID equals eds.EducationMissionClassID
                        from edscc in eds.CF_Student.Where(x => x.UserID == df.UserID.Value).DefaultIfEmpty()
                        where edscc.UserID == null
                        select new StudentsView
                        {
                            TepTableID = eds.EvaluationSettingID,
                            UserID = df.UserID.Value,
                        };

            return query;
        }
        #endregion

        #region 9.0 学生征订
        // 学生征订
        public IQueryable<ET_StudentsOrder> GetAddStudentsOrder(Expression<Func<CF_DifferentDynamic, bool>> filter)
        {
            //学生征订未征订状态的才进行信息调整       
            Expression<Func<ET_StudentsOrder, bool>> exp =
                x => x.IsOrdered == false;//未征订

            var query = from df in DifferentDynamicRepository.GetList(filter)
                        join biss in InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true) on df.AfterInSchoolStatusID equals biss.InSchoolStatusID 
                        join ac in ClassmajorRepository.Entities on df.AfterClassmajorID equals ac.ClassmajorID 
                        join sy in SchoolyearRepository.Entities on df.SchoolyearID equals sy.SchoolyearID
                        from sp in SpecialtyPlanRepository.Entities.Where(x => x.CF_Schoolyear.Value >= sy.Value 
                            && x.GrademajorID == ac.GrademajorID)
                        join stdOrd in StudentsOrderRepository.GetList(exp) on sp.SpecialtyPlanID equals stdOrd.SpecialtyPlanID 
                        select stdOrd;

            return query;
        }

        public IQueryable<ET_StudentsOrder> GetDecreaseStudentsOrder(Expression<Func<CF_DifferentDynamic, bool>> filter)
        {
            Expression<Func<ET_StudentsOrder, bool>> exp =
                           x => x.IsOrdered == false;//未征订

            var query = from df in DifferentDynamicRepository.GetList(filter)
                        join biss in InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true) on df.BeforeInSchoolStatusID equals biss.InSchoolStatusID 
                        join iss in InSchoolSettingRepository.Entities on df.AfterInSchoolStatusID equals iss.InSchoolStatusID into diss 
                        from iss in diss.DefaultIfEmpty()
                        join c in ClassmajorRepository.Entities on df.BeforeClassmajorID equals c.ClassmajorID
                        join ac in ClassmajorRepository.Entities on df.AfterClassmajorID equals ac.ClassmajorID into dac
                        from ac in dac.DefaultIfEmpty() 
                        join sy in SchoolyearRepository.Entities on df.SchoolyearID equals sy.SchoolyearID
                        from sp in SpecialtyPlanRepository.Entities.Where(x => x.CF_Schoolyear.Value >= sy.Value && x.GrademajorID == c.GrademajorID)
                        //on df.CF_Schoolyear.Value >= schoolYearValue equals sp.CF_Schoolyear.Value >= schoolYearValue
                        join stdOrd in StudentsOrderRepository.GetList(exp) on (Guid)sp.SpecialtyPlanID equals (Guid)stdOrd.SpecialtyPlanID
                        where ac.GrademajorID != c.GrademajorID || !(iss.IsSelected ?? false)
                        select stdOrd;

            return query;
        }
        #endregion

        #region 10.0 学生发放
        // 学生发放
        public IQueryable<ET_StudentDistribute> GetAddStudentDistribute(Expression<Func<CF_DifferentDynamic, bool>> filter)
        {
            //学生发放未发放状态的才进行信息调整       
            Expression<Func<ET_StudentDistribute, bool>> exp =
                x => x.RecordStatus == (int)CF_YesOrNoStatus.No;//未发放

            var query = from df in DifferentDynamicRepository.GetList(filter)
                        join biss in InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true) on df.AfterInSchoolStatusID equals biss.InSchoolStatusID 
                        join ac in ClassmajorRepository.Entities on df.AfterClassmajorID equals ac.ClassmajorID 
                        join sy in SchoolyearRepository.Entities on df.SchoolyearID equals sy.SchoolyearID
                        from sp in SpecialtyPlanRepository.Entities.Where(x => x.CF_Schoolyear.Value >= sy.Value 
                            && x.GrademajorID == ac.GrademajorID)
                        //on df.CF_Schoolyear.Value >= schoolYearValue equals sp.CF_Schoolyear.Value >= schoolYearValue
                        join sd in StudentDistributeRepository.GetList(exp) on (Guid)sp.SpecialtyPlanID equals (Guid)sd.SpecialtyPlanID
                        where df.BeforeClassmajorID != df.AfterClassmajorID
                        select sd;

            return query;
        }

        public IQueryable<ET_StudentDistribute> GetDecreaseStudentDistribute(Expression<Func<CF_DifferentDynamic, bool>> filter)
        {
            //学生发放未发放状态的才进行信息调整       
            Expression<Func<ET_StudentDistribute, bool>> exp =
                x => x.RecordStatus == (int)CF_YesOrNoStatus.No;//未发放

            var query = from df in DifferentDynamicRepository.GetList(filter)
                        join biss in InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true) on df.BeforeInSchoolStatusID equals biss.InSchoolStatusID
                        join iss in InSchoolSettingRepository.Entities on df.AfterInSchoolStatusID equals iss.InSchoolStatusID into diss
                        from iss in diss.DefaultIfEmpty()
                        join c in ClassmajorRepository.Entities on df.BeforeClassmajorID equals c.ClassmajorID
                        join ac in ClassmajorRepository.Entities on df.AfterClassmajorID equals ac.ClassmajorID into dac 
                        from ac in dac.DefaultIfEmpty() 
                        join sy in SchoolyearRepository.Entities on df.SchoolyearID equals sy.SchoolyearID
                        from sp in SpecialtyPlanRepository.Entities.Where(x => x.CF_Schoolyear.Value >= sy.Value
                            && x.GrademajorID == c.GrademajorID)
                        //on df.CF_Schoolyear.Value >= schoolYearValue equals sp.CF_Schoolyear.Value >= schoolYearValue
                        join sd in StudentDistributeRepository.GetList(exp) on (Guid)sp.SpecialtyPlanID equals (Guid)sd.SpecialtyPlanID
                        where ac.GrademajorID != c.GrademajorID || !(iss.IsSelected ?? false)
                        select sd;

            return query;
        }
        #endregion

        
        #endregion
        
        /// <summary>
        /// 获取报表下拉(已弃用)
        /// </summary>
        /// <returns></returns>
        //public IQueryable<DifferentDynamicView> GetSelectReport()
        //{
        //    var query = from c in DifferentDynamicReportRepository.GetList(x => true)
        //                select new DifferentDynamicView
        //                {
        //                    ReportName = c.Name,
        //                    ReportUrl = c.Url,
        //                };
        //    return query;
        //}

        public IQueryable<StudentReentryView> GetNeedReturnList(Expression<Func<CF_DifferentDynamic, bool>> filter,
            int correctEndStatusID,
            Expression<Func<CF_Grademajor, bool>> gradeFilter = null,
            Expression<Func<CF_Facultymajor, bool>> facultyFilter = null,
            Expression<Func<CF_College, bool>> collegeFilter = null)
        {
            if (gradeFilter == null)
            {
                gradeFilter = (x => true);
            }
            if (facultyFilter == null)
            {
                facultyFilter = (x => true);
            }
            if (collegeFilter == null)
            {
                collegeFilter = (x => true);
            }

            var query = (from change in DifferentDynamicRepository.Entities.Where(filter)
                         join studentUser in UserRepository.Entities on change.UserID equals studentUser.UserID
                         join beforeClass in ClassmajorRepository.Entities on change.BeforeClassmajorID equals beforeClass.ClassmajorID
                         join grade in GrademajorRepository.Entities.Where(gradeFilter) on beforeClass.GrademajorID equals grade.GrademajorID
                         join faculty in FacultymajorRepository.Entities.Where(facultyFilter) on grade.FacultymajorID equals faculty.FacultymajorID
                         join college in CollegeRepository.Entities.Where(collegeFilter) on faculty.CollegeID equals college.CollegeID 
                         join campus in CampusRepository.Entities on college.CampusID equals campus.CampusID 
                         join creator in UserRepository.Entities on change.CreateUserID equals creator.UserID into tmp
                         from creator in tmp.DefaultIfEmpty()
                         join afterClass in ClassmajorRepository.Entities on change.AfterClassmajorID equals afterClass.ClassmajorID into dafterClass
                         from afterClassmajor in dafterClass.DefaultIfEmpty()
                         join reentry in DifferentDynamicRepository.Entities
                         on new { SchoolyearID = change.ReturnSchoolyearID, UserID = studentUser.UserID, DifferentDynamicType = (int)CF_ChangeType.Reentry, ApprovalStatus = correctEndStatusID }
                         equals new { SchoolyearID = reentry.SchoolyearID, UserID = reentry.UserID.Value, DifferentDynamicType = reentry.ChangeTypeID.Value, ApprovalStatus = reentry.ApprovalStatus.Value } into dreentry 
                         from reentry in dreentry.DefaultIfEmpty()
                         where change.ReturnSchoolyearID != null && change.ApprovalStatus == correctEndStatusID
                         select new StudentReentryView 
                         {
                             ID = change.StudentChangeID,
                             UserID = studentUser.UserID,
                             LoginID = studentUser.LoginID,
                             UserName = studentUser.Name,
                             //EndDate = change.EndTime,
                             DifferentDynamicTypeID = change.ChangeTypeID,
                             ClassmajorID = change.BeforeClassmajorID,
                             ClassmajorName = beforeClass.Name,
                             StudentStatusID = change.BeforeStudentStatus,
                             AfterClassmajorID = change.AfterClassmajorID,
                             AfterClassmajorName = afterClassmajor.Name,
                             AfterStudentStatusID = change.AfterStudentStatus,
                             ReasonID = change.ChangeReasonID,
                             IsReentried = (reentry.StudentChangeID != null),
                             CollegeID = faculty.CollegeID,
                             CollegeName = college.Name,
                             CampusID = college.CampusID,
                             CampusName = campus.Name,
                             CreateTime = change.CreateTime
                         });

            return query;
        }

    }
}