JW/EMiS.DataLogic/EducationSchedule/EducationSchedulingDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EMIS.DataLogic.Repositories;
using EMIS.ViewModel.EducationSchedule;
using System.Linq.Expressions;
using EMIS.Entities;
using EMIS.ViewModel.EducationManage;
using EMIS.DataLogic.EducationManage;
using EMIS.ViewModel.CalendarManage;
using EMIS.ViewModel;
using EMIS.DataLogic.Common.CalendarManage;
using EMIS.DataLogic.UniversityManage.ClassroomManage;
using EMIS.ViewModel.UniversityManage.ClassroomManage;
using Bowin.Common.Utility;
using EMIS.ViewModel.EducationManagement;

namespace EMIS.DataLogic.EducationSchedule
{
    public class EducationSchedulingDAL
    {
        public ClassroomRepository ClassroomRepository { get; set; }
        public ClassroomReserveRepository ClassroomReserveRepository { get; set; }
        public ClassroomExcessiveUseRepository ClassroomExcessiveUseRepository { get; set; }
        public ClassroomExcessiveUseSchedulingRepository ClassroomExcessiveUseSchedulingRepository { get; set; }
        public ClassroomExcessiveUseSchedulingWeekNumRepository ClassroomExcessiveUseSchedulingWeekNumRepository { get; set; }
        public ClassroomScheduleSettingRepository ClassroomScheduleSettingRepository { get; set; }
        public ClassmajorScheduleSettingRepository ClassmajorScheduleSettingRepository { get; set; }
        public CourseScheduleSettingRepository CourseScheduleSettingRepository { get; set; }
        public EducationSchedulingAdjustmentRepository EducationSchedulingAdjustmentRepository { get; set; }
        public EducationSchedulingAdjustmentToTeacherRepository EducationSchedulingAdjustmentToTeacherRepository { get; set; }
        public EducationSchedulingStopRepository EducationSchedulingStopRepository { get; set; }
        public EducationSchedulingStopTeacherRepository EducationSchedulingStopTeacherRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public CoursematerialRepository CoursematerialRepository { get; set; }
        public EducationSchedulingRepository EducationSchedulingRepository { get; set; }
        public EducationSchedulingTeacherRepository EducationSchedulingTeacherRepository { get; set; }
        public EducationSchedulingClassRepository EducationSchedulingClassRepository { get; set; }
        public EducationSchedulingWeekNumRepository EducationSchedulingWeekNumRepository { get; set; }
        public EducationMissionClassRepository EducationMissionClassRepository { get; set; }
        public EducationMissionRepository EducationMissionRepository { get; set; }
        public MissionClassTeacherRepository MissionClassTeacherRepository { get; set; }
        public StaffRepository StaffRepository { get; set; }
        public TeacherScheduleSettingRepository TeacherScheduleSettingRepository { get; set; }
        public CollegePriorityRepository CollegePriorityRepository { get; set; }
        public CollegeScheduleStatusRepository CollegeScheduleStatusRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public ArrangementsRepository ArrangementsRepository { get; set; }
        public ExaminationPlanRepository ExaminationPlanRepository { get; set; }
        public CoursesTimeRepository CoursesTimeRepository { get; set; }

        public Lazy<EducationSchedulingClassDAL> EducationSchedulingClassDAL { get; set; }
        public Lazy<EducationMissionClassDAL> EducationMissionClassDAL { get; set; }
        public Lazy<CoursesTimeDAL> CoursesTimeDAL { get; set; }
        public Lazy<ClassroomScheduleDAL> ClassroomScheduleDAL { get; set; }

        public IQueryable<EducationSchedulingView> GetEducationSchedulingViewQueryable(Expression<Func<ES_EducationScheduling, bool>> exp)
        {
            var nowDate = DateTime.Today;
            var q = (from es in EducationSchedulingRepository.GetList(exp) 
                     join esc in EducationSchedulingClassDAL.Value.GetEducationSchedulingClassViewQueryable(x => true) 
                        on es.EducationSchedulingClassID equals esc.EducationSchedulingClassID 
                     select new EducationSchedulingView
                     {
                         EducationSchedulingID = es.EducationSchedulingID,
                         SchoolyearID = es.SchoolyearID,
                         SchoolyearCode = es.CF_Schoolyear.Code,
                         CoursematerialID = esc.CoursematerialID,
                         CoursematerialName = esc.CoursematerialName,
                         TeachingModeID = es.EM_EducationSchedulingClass.EM_EducationMissionClass.TeachingModeID,
                         EducationSchedulingClassID = es.EducationSchedulingClassID,
                         TaskGroupName = es.EM_EducationSchedulingClass.TaskGroupName,
                         EducationMissionClassID = es.EM_EducationSchedulingClass.EducationMissionClassID,
                         EducationMissionClassName = es.EM_EducationSchedulingClass.EM_EducationMissionClass.Name,
                         Weekday = es.Weekday,
                         CoursesTimeID = es.CoursesTimeID,
                         CoursesTime = new ViewModel.CalendarManage.BaseWorkTimeView 
                         {
                             StartTimes = es.EM_CoursesTime.StartTimes,
                             EndTimes = es.EM_CoursesTime.EndTimes,
                             TimesSegment = es.EM_CoursesTime.TimesSegment,
                             StartHour = es.EM_CoursesTime.StartHour,
                             StartMinutes = es.EM_CoursesTime.StartMinutes,
                             EndHour = es.EM_CoursesTime.EndHour,
                             EndMinutes = es.EM_CoursesTime.EndMinutes,
                         },
                         ClassroomTypeID = es.EM_EducationSchedulingClass.EM_EducationMissionClass.ClassroomTypeID,
                         ClassroomID = es.ClassroomID,
                         ClassroomName = es.CF_Classroom.Name,
                         RecordStatus = es.RecordStatus,
                         CreateUserID = es.CreateUserID,
                         CreateTime = es.CreateTime,
                         ModifyUserID = es.ModifyUserID,
                         ModifyTime = es.ModifyTime,
                         CollegeID = es.EM_EducationSchedulingClass.EM_EducationMissionClass.EM_EducationMission.CollegeID,
                         CourseCollegeID = es.EM_EducationSchedulingClass.EM_EducationMissionClass.EM_EducationMission.CF_Department.CollegeID,
                         StudentCount = es.EM_EducationSchedulingClass.CF_Student.Count(),
                         IsCanEdit = esc.IsCanEdit
                     });

            return q;
        }

        public IQueryable<EducationSchedulingWeekNumView> GetEducationSchedulingWeekNumViewQueryable(Expression<Func<ES_EducationSchedulingWeekNum, bool>> exp)
        {
            var nowDate = DateTime.Today;
            var q = (from weekNum in EducationSchedulingWeekNumRepository.GetList(exp)
                     from scheduling in EducationSchedulingRepository.Entities.Where(x => x.EducationSchedulingID == weekNum.EducationSchedulingID) 
                     from schedulingClass in EducationSchedulingClassRepository.Entities.Where(x => x.EducationSchedulingClassID == scheduling.EducationSchedulingClassID) 
                     from stop in EducationSchedulingStopRepository.Entities.Where(x => x.EducationMissionClassID == schedulingClass.EducationMissionClassID 
                        && x.Weekday == scheduling.Weekday && x.CoursesTimeID == scheduling.CoursesTimeID && x.ClassroomID == scheduling.ClassroomID 
                        && x.WeekNum == weekNum.WeekNum).DefaultIfEmpty() 
                     where stop.EducationSchedulingStopID == null
                     select new EducationSchedulingWeekNumView
                     {
                         EducationSchedulingWeekNumID = weekNum.EducationSchedulingWeekNumID,
                         EducationSchedulingID = scheduling.EducationSchedulingID,
                         SchoolyearID = scheduling.SchoolyearID,
                         EducationMissionClassID = schedulingClass.EducationMissionClassID,
                         WeekNum = weekNum.WeekNum,
                         Weekday = scheduling.Weekday,
                         CoursesTimeID = scheduling.CoursesTimeID,
                         ClassroomID = scheduling.ClassroomID
                     });

            return q;
        }

        public IQueryable<EM_EducationMissionClassSettings> GetEducationMissionClassSettingQueryable(
            Expression<Func<ES_EducationScheduling, bool>> exp)
        {
            var q = (from es in EducationSchedulingRepository.GetList(exp)
                     from emcs in es.EM_EducationSchedulingClass.EM_EducationMissionClass.EM_EducationMissionClassSettings
                     select emcs);

            return q;
        }

        public IQueryable<ES_EducationSchedulingWeekNum> GetEducationMissionWeekNumQueryable(
            Expression<Func<ES_EducationScheduling, bool>> exp)
        {
            return this.EducationSchedulingRepository.GetList(exp).SelectMany(x => x.ES_EducationSchedulingWeekNum);
        }

        public IQueryable<EducationSchedulingTeacherListView> GetTeacherViewQueryable(
            Expression<Func<ES_EducationScheduling, bool>> exp)
        {
            var q = (from es in EducationSchedulingRepository.GetList(exp)
                     from s in es.ES_EducationSchedulingTeacher
                     orderby new { TeachingMethod = (s.TeachingMethod ?? 0), s.CF_Staff.Sys_User.Name } 
                     select new EducationSchedulingTeacherListView
                     {
                         EducationSchedulingID = es.EducationSchedulingID,
                         UserID = s.CF_Staff.UserID,
                         LoginID = s.CF_Staff.Sys_User.LoginID,
                         Name = s.CF_Staff.Sys_User.Name,
                         CollegeID = s.CF_Staff.CollegeID,
                         CollegeName = s.CF_Staff.CF_College.Name,
                         BirthDate = s.CF_Staff.BirthDate,
                         TeacherType = s.CF_Staff.TeacherTypeID,
                         IncumbencyState = s.CF_Staff.IncumbencyState,
                         Title = s.CF_Staff.TitleID,
                         TeachingMethod = (s.TeachingMethod ?? 0)
                     });

            return q;
        }

        public IQueryable<EducationMissionClassContainClassView> GetClassmajorQueryable(
            Expression<Func<ES_EducationScheduling, bool>> exp)
        {
            var q = (from es in EducationSchedulingRepository.GetList(exp)
                     from cm in es.EM_EducationSchedulingClass.EM_EducationMissionClass.CF_Classmajor
                     select new EducationMissionClassContainClassView
                     {
                         EducationMissionClassID = es.EM_EducationSchedulingClass.EM_EducationMissionClass.EducationMissionClassID,
                         ClassMajor = cm
                     });

            return q;
        }

        public IQueryable<CourseProcessView> GetCourseProcessQueryable(
            Expression<Func<ES_EducationScheduling, bool>> exp)
        {
            var q = (from es in EducationSchedulingRepository.GetList(exp) 
                     join esc in EducationSchedulingClassDAL.Value.EducationSchedulingClassRepository.Entities on es.EducationSchedulingClassID equals esc.EducationSchedulingClassID
                     join cp in this.EducationMissionClassDAL.Value.GetCourseProcessView(x => true) 
                        on esc.EducationMissionClassID equals cp.EducationMissionClassID 
                     select cp);

            return q;
        }

        public IQueryable<CourseProcessTeacherView> GetCourseProcessTeacherViewQueryable(
            Expression<Func<ES_EducationScheduling, bool>> exp)
        {
            var q = (from es in EducationSchedulingRepository.GetList(exp)
                     join esc in EducationSchedulingClassDAL.Value.EducationSchedulingClassRepository.Entities on es.EducationSchedulingClassID equals esc.EducationSchedulingClassID
                     join cp in this.EducationMissionClassDAL.Value.GetCourseProcessView(x => true) 
                        on esc.EducationMissionClassID equals cp.EducationMissionClassID 
                     join cpt in this.EducationMissionClassDAL.Value.GetCourseProcessTeacherView(x => true) 
                        on cp.CourseProcessID equals cpt.CourseProcessID 
                     select cpt);

            return q;
        }

        public IQueryable<ScheduleAdjustmentEditView> GetScheduleAdjustmentViewQueryable(Expression<Func<CF_Staff, bool>> staffExp,
            Expression<Func<ES_EducationScheduling, bool>> educationSchedulingExp, Expression<Func<EM_EducationMissionClass, bool>> missionClassExp,
            Expression<Func<ES_EducationSchedulingWeekNum, bool>> weeknumExp, 
            int stopApproveStatus)
        {
            var curDate = DateTime.Today;
            var curTime = DateTime.Now;
                //DateTime.Parse("2019-05-07 12:00:00");
                
            var curSchoolyear = SchoolyearRepository.GetSingle(x => x.IsCurrent == true);
            var firstWeek = curSchoolyear.FirstWeek.AddDays(1 - (int)curSchoolyear.FirstWeek.DayOfWeek);
            var nowWeekNum = (curDate.Subtract(firstWeek).Days + 7) / 7;
            var nowWeekday = (int)curDate.DayOfWeek == 0 ? 7 : (int)curDate.DayOfWeek;
            var futureWeekNumList = new List<int?>();
            var nowAndFutureWeekNumList = new List<int?>();
            var beforeWeekdayList = new List<int?>();
            var beforeCourseTimeList = new List<Guid?>();
            var coursesTimeList = CoursesTimeDAL.Value.GetCoursesTimeQueryable(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                .OrderBy(x => x.StartHour).ToList();

            for (int i = (nowWeekNum + 1); i <= curSchoolyear.WeeksNum; i++)
            {
                futureWeekNumList.Add(i);
            }
            nowAndFutureWeekNumList = new List<int?> { nowWeekNum }.Concat(futureWeekNumList).ToList();

            for (int i = 1; i < nowWeekday; i++)
            {
                beforeWeekdayList.Add(i == 7 ? 0 : i);
            }

            foreach (var coursesTime in coursesTimeList)
            {
                if (new TimeSpan(coursesTime.StartHour ?? 0, coursesTime.StartMinutes ?? 0, 0)
                    .CompareTo(new TimeSpan(curTime.Hour, curTime.Minute, curTime.Second)) <= 0)
                {
                    beforeCourseTimeList.Add(coursesTime.CoursesTimeID);
                }
            }

            var q = (from s in StaffRepository.GetList(staffExp)    //如当天是星期4，对于星期1-3，读取当周以后周次的排课
                     from tes in s.ES_EducationSchedulingTeacher
                     join es in EducationSchedulingRepository.GetList(educationSchedulingExp).Where(x => beforeWeekdayList.Contains(x.Weekday) 
                        && x.SchoolyearID == curSchoolyear.SchoolyearID) 
                         on tes.EducationSchedulingID equals es.EducationSchedulingID
                     join eswn in EducationSchedulingWeekNumRepository.Entities.Where(weeknumExp)
                        .Where(x => futureWeekNumList.Contains(x.WeekNum)) on es.EducationSchedulingID equals eswn.EducationSchedulingID 
                     join esc in EducationSchedulingClassRepository.Entities on es.EducationSchedulingClassID equals esc.EducationSchedulingClassID
                     join emc in EducationMissionClassRepository.Entities.Where(missionClassExp) on esc.EducationMissionClassID equals emc.EducationMissionClassID 
                     join c in CoursematerialRepository.Entities on emc.CoursematerialID equals c.CoursematerialID 
                     select new ScheduleAdjustmentEditView
                     {
                         EducationSchedulingID = es.EducationSchedulingID,
                         EducationSchedulingWeekNumID = eswn.EducationSchedulingWeekNumID,
                         EducationMissionClassID = emc.EducationMissionClassID,
                         SchoolyearID = es.SchoolyearID,
                         EducationMissionClassName = es.EM_EducationSchedulingClass.EM_EducationMissionClass.Name,
                         CoursematerialID = emc.CoursematerialID,
                         CourseCode = c.CourseCode,
                         CourseName = c.CourseName,
                         EducationMissionClassStartWeekNum = (nowWeekNum > 1) ? nowWeekNum : 1,
                         EducationMissionClassEndWeekNum = es.CF_Schoolyear.WeeksNum,
                         WeekNum = eswn.WeekNum,
                         Weekday = es.Weekday,
                         TimesSegment = es.EM_CoursesTime.TimesSegment,
                         CoursesTimeID = es.CoursesTimeID,
                         StartHour = es.EM_CoursesTime.StartHour,
                         StartMinutes = es.EM_CoursesTime.StartMinutes,
                         EndHour = es.EM_CoursesTime.EndHour,
                         EndMinutes = es.EM_CoursesTime.EndMinutes,
                         ClassroomTypeID = emc.ClassroomTypeID,
                         ClassroomID = es.ClassroomID,
                         ClassroomName = es.CF_Classroom.Name,
                         CollegeName = es.EM_EducationSchedulingClass.EM_EducationMissionClass.EM_EducationMission.CF_College.Name
                     }).Concat(    //如当天是星期4，排课也是星期4，对于当前时间之前的节次的课，读取当周以后周次的排课
                        (from s in StaffRepository.GetList(staffExp)
                         from tes in s.ES_EducationSchedulingTeacher
                         join es in EducationSchedulingRepository.GetList(educationSchedulingExp)
                            .Where(x => x.Weekday == nowWeekday && beforeCourseTimeList.Contains(x.CoursesTimeID)
                                && x.SchoolyearID == curSchoolyear.SchoolyearID)
                             on tes.EducationSchedulingID equals es.EducationSchedulingID
                         join eswn in EducationSchedulingWeekNumRepository.Entities.Where(weeknumExp)
                            .Where(x => futureWeekNumList.Contains(x.WeekNum)) on es.EducationSchedulingID equals eswn.EducationSchedulingID
                         join esc in EducationSchedulingClassRepository.Entities on es.EducationSchedulingClassID equals esc.EducationSchedulingClassID
                         join emc in EducationMissionClassRepository.Entities.Where(missionClassExp) on esc.EducationMissionClassID equals emc.EducationMissionClassID
                         join c in CoursematerialRepository.Entities on emc.CoursematerialID equals c.CoursematerialID
                         select new ScheduleAdjustmentEditView
                         {
                             EducationSchedulingID = es.EducationSchedulingID,
                             EducationSchedulingWeekNumID = eswn.EducationSchedulingWeekNumID,
                             EducationMissionClassID = emc.EducationMissionClassID,
                             SchoolyearID = es.SchoolyearID,
                             EducationMissionClassName = es.EM_EducationSchedulingClass.EM_EducationMissionClass.Name,
                             CoursematerialID = emc.CoursematerialID,
                             CourseCode = c.CourseCode,
                             CourseName = c.CourseName,
                             EducationMissionClassStartWeekNum = (nowWeekNum > 1) ? nowWeekNum : 1,
                             EducationMissionClassEndWeekNum = es.CF_Schoolyear.WeeksNum,
                             WeekNum = eswn.WeekNum,
                             Weekday = es.Weekday,
                             TimesSegment = es.EM_CoursesTime.TimesSegment,
                             CoursesTimeID = es.CoursesTimeID,
                             StartHour = es.EM_CoursesTime.StartHour,
                             StartMinutes = es.EM_CoursesTime.StartMinutes,
                             EndHour = es.EM_CoursesTime.EndHour,
                             EndMinutes = es.EM_CoursesTime.EndMinutes,
                             ClassroomTypeID = emc.ClassroomTypeID,
                             ClassroomID = es.ClassroomID,
                             ClassroomName = es.CF_Classroom.Name,
                             CollegeName = es.EM_EducationSchedulingClass.EM_EducationMissionClass.EM_EducationMission.CF_College.Name
                         })
                     ).Concat(    //如当天是星期4，对于星期5-日，读取当周及当周以后周次的排课
                        (from s in StaffRepository.GetList(staffExp)
                         from tes in s.ES_EducationSchedulingTeacher
                         join es in EducationSchedulingRepository.GetList(educationSchedulingExp)
                            .Where(x => !beforeWeekdayList.Contains(x.Weekday) && x.Weekday != nowWeekday
                                && x.SchoolyearID == curSchoolyear.SchoolyearID)
                             on tes.EducationSchedulingID equals es.EducationSchedulingID
                         join eswn in EducationSchedulingWeekNumRepository.Entities.Where(weeknumExp)
                            .Where(x => nowAndFutureWeekNumList.Contains(x.WeekNum)) on es.EducationSchedulingID equals eswn.EducationSchedulingID
                         join esc in EducationSchedulingClassRepository.Entities on es.EducationSchedulingClassID equals esc.EducationSchedulingClassID
                         join emc in EducationMissionClassRepository.Entities.Where(missionClassExp) on esc.EducationMissionClassID equals emc.EducationMissionClassID
                         join c in CoursematerialRepository.Entities on emc.CoursematerialID equals c.CoursematerialID
                         select new ScheduleAdjustmentEditView
                         {
                             EducationSchedulingID = es.EducationSchedulingID,
                             EducationSchedulingWeekNumID = eswn.EducationSchedulingWeekNumID,
                             EducationMissionClassID = emc.EducationMissionClassID,
                             SchoolyearID = es.SchoolyearID,
                             EducationMissionClassName = es.EM_EducationSchedulingClass.EM_EducationMissionClass.Name,
                             CoursematerialID = emc.CoursematerialID,
                             CourseCode = c.CourseCode,
                             CourseName = c.CourseName,
                             EducationMissionClassStartWeekNum = (nowWeekNum > 1) ? nowWeekNum : 1,
                             EducationMissionClassEndWeekNum = es.CF_Schoolyear.WeeksNum,
                             WeekNum = eswn.WeekNum,
                             Weekday = es.Weekday,
                             TimesSegment = es.EM_CoursesTime.TimesSegment,
                             CoursesTimeID = es.CoursesTimeID,
                             StartHour = es.EM_CoursesTime.StartHour,
                             StartMinutes = es.EM_CoursesTime.StartMinutes,
                             EndHour = es.EM_CoursesTime.EndHour,
                             EndMinutes = es.EM_CoursesTime.EndMinutes,
                             ClassroomTypeID = emc.ClassroomTypeID,
                             ClassroomID = es.ClassroomID,
                             ClassroomName = es.CF_Classroom.Name,
                             CollegeName = es.EM_EducationSchedulingClass.EM_EducationMissionClass.EM_EducationMission.CF_College.Name
                         })
                     ).Concat(    //如当天是星期4，排课也是星期4，对于当前时间之后的节次的课，读取当周及当周以后周次的排课
                        (from s in StaffRepository.GetList(staffExp)
                         from tes in s.ES_EducationSchedulingTeacher
                         join es in EducationSchedulingRepository.GetList(educationSchedulingExp)
                            .Where(x => x.Weekday == nowWeekday && !beforeCourseTimeList.Contains(x.CoursesTimeID)
                                && x.SchoolyearID == curSchoolyear.SchoolyearID)
                             on tes.EducationSchedulingID equals es.EducationSchedulingID
                         join eswn in EducationSchedulingWeekNumRepository.Entities.Where(weeknumExp)
                            .Where(x => nowAndFutureWeekNumList.Contains(x.WeekNum)) on es.EducationSchedulingID equals eswn.EducationSchedulingID
                         join esc in EducationSchedulingClassRepository.Entities on es.EducationSchedulingClassID equals esc.EducationSchedulingClassID
                         join emc in EducationMissionClassRepository.Entities.Where(missionClassExp) on esc.EducationMissionClassID equals emc.EducationMissionClassID
                         join c in CoursematerialRepository.Entities on emc.CoursematerialID equals c.CoursematerialID
                         select new ScheduleAdjustmentEditView
                         {
                             EducationSchedulingID = es.EducationSchedulingID,
                             EducationSchedulingWeekNumID = eswn.EducationSchedulingWeekNumID,
                             EducationMissionClassID = emc.EducationMissionClassID,
                             SchoolyearID = es.SchoolyearID,
                             EducationMissionClassName = es.EM_EducationSchedulingClass.EM_EducationMissionClass.Name,
                             CoursematerialID = emc.CoursematerialID,
                             CourseCode = c.CourseCode,
                             CourseName = c.CourseName,
                             EducationMissionClassStartWeekNum = (nowWeekNum > 1) ? nowWeekNum : 1,
                             EducationMissionClassEndWeekNum = es.CF_Schoolyear.WeeksNum,
                             WeekNum = eswn.WeekNum,
                             Weekday = es.Weekday,
                             TimesSegment = es.EM_CoursesTime.TimesSegment,
                             CoursesTimeID = es.CoursesTimeID,
                             StartHour = es.EM_CoursesTime.StartHour,
                             StartMinutes = es.EM_CoursesTime.StartMinutes,
                             EndHour = es.EM_CoursesTime.EndHour,
                             EndMinutes = es.EM_CoursesTime.EndMinutes,
                             ClassroomTypeID = emc.ClassroomTypeID,
                             ClassroomID = es.ClassroomID,
                             ClassroomName = es.CF_Classroom.Name,
                             CollegeName = es.EM_EducationSchedulingClass.EM_EducationMissionClass.EM_EducationMission.CF_College.Name
                         })
                     ).Concat(
                         (from s in StaffRepository.GetList(staffExp)    //如当天是星期4，对于星期1-3，读取当周及当周以前停课的排课
                          from tes in s.ES_EducationSchedulingTeacher
                          join es in EducationSchedulingRepository.GetList(educationSchedulingExp).Where(x => beforeWeekdayList.Contains(x.Weekday)
                                && x.SchoolyearID == curSchoolyear.SchoolyearID)
                              on tes.EducationSchedulingID equals es.EducationSchedulingID 
                          join eswn in EducationSchedulingWeekNumRepository.Entities.Where(weeknumExp)
                             .Where(x => !futureWeekNumList.Contains(x.WeekNum)) on es.EducationSchedulingID equals eswn.EducationSchedulingID
                          join esc in EducationSchedulingClassRepository.Entities on es.EducationSchedulingClassID equals esc.EducationSchedulingClassID
                          join emc in EducationMissionClassRepository.Entities.Where(missionClassExp) on esc.EducationMissionClassID equals emc.EducationMissionClassID
                          join c in CoursematerialRepository.Entities on emc.CoursematerialID equals c.CoursematerialID
                          from stop in EducationSchedulingStopRepository.Entities.Where(x => x.RecordStatus == stopApproveStatus 
                            && x.EducationMissionClassID == emc.EducationMissionClassID && x.CoursesTimeID == es.CoursesTimeID 
                            && x.Weekday == es.Weekday && x.ClassroomID == es.ClassroomID 
                            && x.WeekNum == eswn.WeekNum 
                            && x.ES_EducationSchedulingStopTeacher.Count() == (
                                from est in EducationSchedulingTeacherRepository.Entities
                                    .Where(w => w.EducationSchedulingID == es.EducationSchedulingID) 
                                from esst in EducationSchedulingStopTeacherRepository.Entities
                                    .Where(w => w.EducationSchedulingStopID == x.EducationSchedulingStopID 
                                        && w.UserID == est.UserID && w.TeachingMethod == est.TeachingMethod)
                                select esst).Count())
                          select new ScheduleAdjustmentEditView
                          {
                              EducationSchedulingID = es.EducationSchedulingID,
                              EducationSchedulingWeekNumID = eswn.EducationSchedulingWeekNumID,
                              EducationMissionClassID = emc.EducationMissionClassID,
                              SchoolyearID = es.SchoolyearID,
                              EducationMissionClassName = es.EM_EducationSchedulingClass.EM_EducationMissionClass.Name,
                              CoursematerialID = emc.CoursematerialID,
                              CourseCode = c.CourseCode,
                              CourseName = c.CourseName,
                              EducationMissionClassStartWeekNum = (nowWeekNum > 1) ? nowWeekNum : 1,
                              EducationMissionClassEndWeekNum = es.CF_Schoolyear.WeeksNum,
                              WeekNum = eswn.WeekNum,
                              Weekday = es.Weekday,
                              TimesSegment = es.EM_CoursesTime.TimesSegment,
                              CoursesTimeID = es.CoursesTimeID,
                              StartHour = es.EM_CoursesTime.StartHour,
                              StartMinutes = es.EM_CoursesTime.StartMinutes,
                              EndHour = es.EM_CoursesTime.EndHour,
                              EndMinutes = es.EM_CoursesTime.EndMinutes,
                              ClassroomTypeID = emc.ClassroomTypeID,
                              ClassroomID = es.ClassroomID,
                              ClassroomName = es.CF_Classroom.Name,
                              CollegeName = es.EM_EducationSchedulingClass.EM_EducationMissionClass.EM_EducationMission.CF_College.Name
                          })
                     ).Concat(    //如当天是星期4，排课也是星期4，对于当前时间之前的节次的课，读取当周及当周以前停课的排课
                        (from s in StaffRepository.GetList(staffExp)
                         from tes in s.ES_EducationSchedulingTeacher
                         join es in EducationSchedulingRepository.GetList(educationSchedulingExp)
                            .Where(x => x.Weekday == nowWeekday 
                                //&& beforeCourseTimeList.Contains(x.CoursesTimeID)
                                && x.SchoolyearID == curSchoolyear.SchoolyearID)
                             on tes.EducationSchedulingID equals es.EducationSchedulingID
                         join eswn in EducationSchedulingWeekNumRepository.Entities.Where(weeknumExp)
                            .Where(x => !futureWeekNumList.Contains(x.WeekNum)) on es.EducationSchedulingID equals eswn.EducationSchedulingID
                         join esc in EducationSchedulingClassRepository.Entities on es.EducationSchedulingClassID equals esc.EducationSchedulingClassID
                         join emc in EducationMissionClassRepository.Entities.Where(missionClassExp) on esc.EducationMissionClassID equals emc.EducationMissionClassID
                         join c in CoursematerialRepository.Entities on emc.CoursematerialID equals c.CoursematerialID
                         from stop in
                             EducationSchedulingStopRepository.Entities.Where(x => x.RecordStatus == stopApproveStatus
                                 && x.EducationMissionClassID == emc.EducationMissionClassID && x.CoursesTimeID == es.CoursesTimeID
                                 && x.Weekday == es.Weekday && x.ClassroomID == es.ClassroomID
                                 && x.WeekNum == eswn.WeekNum
                                 && x.ES_EducationSchedulingStopTeacher.Count() == (
                                 from est in EducationSchedulingTeacherRepository.Entities
                                     .Where(w => w.EducationSchedulingID == es.EducationSchedulingID)
                                 from esst in EducationSchedulingStopTeacherRepository.Entities
                                     .Where(w => w.EducationSchedulingStopID == x.EducationSchedulingStopID
                                         && w.UserID == est.UserID && w.TeachingMethod == est.TeachingMethod)
                                 select esst).Count())
                         select new ScheduleAdjustmentEditView
                         {
                             EducationSchedulingID = es.EducationSchedulingID,
                             EducationSchedulingWeekNumID = eswn.EducationSchedulingWeekNumID,
                             EducationMissionClassID = emc.EducationMissionClassID,
                             SchoolyearID = es.SchoolyearID,
                             EducationMissionClassName = es.EM_EducationSchedulingClass.EM_EducationMissionClass.Name,
                             CoursematerialID = emc.CoursematerialID,
                             CourseCode = c.CourseCode,
                             CourseName = c.CourseName,
                             EducationMissionClassStartWeekNum = (nowWeekNum > 1) ? nowWeekNum : 1,
                             EducationMissionClassEndWeekNum = es.CF_Schoolyear.WeeksNum,
                             WeekNum = eswn.WeekNum,
                             Weekday = es.Weekday,
                             TimesSegment = es.EM_CoursesTime.TimesSegment,
                             CoursesTimeID = es.CoursesTimeID,
                             StartHour = es.EM_CoursesTime.StartHour,
                             StartMinutes = es.EM_CoursesTime.StartMinutes,
                             EndHour = es.EM_CoursesTime.EndHour,
                             EndMinutes = es.EM_CoursesTime.EndMinutes,
                             ClassroomTypeID = emc.ClassroomTypeID,
                             ClassroomID = es.ClassroomID,
                             ClassroomName = es.CF_Classroom.Name,
                             CollegeName = es.EM_EducationSchedulingClass.EM_EducationMissionClass.EM_EducationMission.CF_College.Name
                         })
                     ).Concat(    //如当天是星期4，排课也是星期5-日，读取当周以前停课的排课（不包括当周）
                        (from s in StaffRepository.GetList(staffExp)
                         from tes in s.ES_EducationSchedulingTeacher
                         join es in EducationSchedulingRepository.GetList(educationSchedulingExp)
                            .Where(x => !beforeWeekdayList.Contains(x.Weekday) && x.Weekday != nowWeekday
                                && x.SchoolyearID == curSchoolyear.SchoolyearID)
                             on tes.EducationSchedulingID equals es.EducationSchedulingID
                         join eswn in EducationSchedulingWeekNumRepository.Entities.Where(weeknumExp)
                            .Where(x => !nowAndFutureWeekNumList.Contains(x.WeekNum)) on es.EducationSchedulingID equals eswn.EducationSchedulingID
                         join esc in EducationSchedulingClassRepository.Entities on es.EducationSchedulingClassID equals esc.EducationSchedulingClassID
                         join emc in EducationMissionClassRepository.Entities.Where(missionClassExp) on esc.EducationMissionClassID equals emc.EducationMissionClassID
                         join c in CoursematerialRepository.Entities on emc.CoursematerialID equals c.CoursematerialID
                         from stop in
                             EducationSchedulingStopRepository.Entities.Where(x => x.RecordStatus == stopApproveStatus
                                 && x.EducationMissionClassID == emc.EducationMissionClassID && x.CoursesTimeID == es.CoursesTimeID
                                 && x.Weekday == es.Weekday && x.ClassroomID == es.ClassroomID
                                 && x.WeekNum == eswn.WeekNum
                                 && x.ES_EducationSchedulingStopTeacher.Count() == (
                                 from est in EducationSchedulingTeacherRepository.Entities
                                     .Where(w => w.EducationSchedulingID == es.EducationSchedulingID)
                                 from esst in EducationSchedulingStopTeacherRepository.Entities
                                     .Where(w => w.EducationSchedulingStopID == x.EducationSchedulingStopID
                                         && w.UserID == est.UserID && w.TeachingMethod == est.TeachingMethod)
                                 select esst).Count())
                         select new ScheduleAdjustmentEditView
                         {
                             EducationSchedulingID = es.EducationSchedulingID,
                             EducationSchedulingWeekNumID = eswn.EducationSchedulingWeekNumID,
                             EducationMissionClassID = emc.EducationMissionClassID,
                             SchoolyearID = es.SchoolyearID,
                             EducationMissionClassName = es.EM_EducationSchedulingClass.EM_EducationMissionClass.Name,
                             CoursematerialID = emc.CoursematerialID,
                             CourseCode = c.CourseCode,
                             CourseName = c.CourseName,
                             EducationMissionClassStartWeekNum = (nowWeekNum > 1) ? nowWeekNum : 1,
                             EducationMissionClassEndWeekNum = es.CF_Schoolyear.WeeksNum,
                             WeekNum = eswn.WeekNum,
                             Weekday = es.Weekday,
                             TimesSegment = es.EM_CoursesTime.TimesSegment,
                             CoursesTimeID = es.CoursesTimeID,
                             StartHour = es.EM_CoursesTime.StartHour,
                             StartMinutes = es.EM_CoursesTime.StartMinutes,
                             EndHour = es.EM_CoursesTime.EndHour,
                             EndMinutes = es.EM_CoursesTime.EndMinutes,
                             ClassroomTypeID = emc.ClassroomTypeID,
                             ClassroomID = es.ClassroomID,
                             ClassroomName = es.CF_Classroom.Name,
                             CollegeName = es.EM_EducationSchedulingClass.EM_EducationMissionClass.EM_EducationMission.CF_College.Name
                         })
                     ).Concat(    //当前学期之后的学期的记录，没有限制
                        (from s in StaffRepository.GetList(staffExp)
                         from tes in s.ES_EducationSchedulingTeacher
                         join es in EducationSchedulingRepository.GetList(educationSchedulingExp)
                             on tes.EducationSchedulingID equals es.EducationSchedulingID
                         join eswn in EducationSchedulingWeekNumRepository.Entities.Where(weeknumExp)
                            .Where(x => !nowAndFutureWeekNumList.Contains(x.WeekNum)) on es.EducationSchedulingID equals eswn.EducationSchedulingID
                         join esc in EducationSchedulingClassRepository.Entities on es.EducationSchedulingClassID equals esc.EducationSchedulingClassID
                         join emc in EducationMissionClassRepository.Entities.Where(missionClassExp) on esc.EducationMissionClassID equals emc.EducationMissionClassID
                         join c in CoursematerialRepository.Entities on emc.CoursematerialID equals c.CoursematerialID 
                         join sy in SchoolyearRepository.Entities on es.SchoolyearID equals sy.SchoolyearID 
                         where sy.Value > curSchoolyear.Value
                         select new ScheduleAdjustmentEditView
                         {
                             EducationSchedulingID = es.EducationSchedulingID,
                             EducationSchedulingWeekNumID = eswn.EducationSchedulingWeekNumID,
                             EducationMissionClassID = emc.EducationMissionClassID,
                             SchoolyearID = es.SchoolyearID,
                             EducationMissionClassName = es.EM_EducationSchedulingClass.EM_EducationMissionClass.Name,
                             CoursematerialID = emc.CoursematerialID,
                             CourseCode = c.CourseCode,
                             CourseName = c.CourseName,
                             EducationMissionClassStartWeekNum = (nowWeekNum > 1) ? nowWeekNum : 1,
                             EducationMissionClassEndWeekNum = es.CF_Schoolyear.WeeksNum,
                             WeekNum = eswn.WeekNum,
                             Weekday = es.Weekday,
                             TimesSegment = es.EM_CoursesTime.TimesSegment,
                             CoursesTimeID = es.CoursesTimeID,
                             StartHour = es.EM_CoursesTime.StartHour,
                             StartMinutes = es.EM_CoursesTime.StartMinutes,
                             EndHour = es.EM_CoursesTime.EndHour,
                             EndMinutes = es.EM_CoursesTime.EndMinutes,
                             ClassroomTypeID = emc.ClassroomTypeID,
                             ClassroomID = es.ClassroomID,
                             ClassroomName = es.CF_Classroom.Name,
                             CollegeName = es.EM_EducationSchedulingClass.EM_EducationMissionClass.EM_EducationMission.CF_College.Name
                         })
                     );

            return q;
        }

        public IQueryable<ScheduleStopEditView> GetScheduleStopViewQueryable(Expression<Func<CF_Staff, bool>> staffExp,
            Expression<Func<ES_EducationScheduling, bool>> educationSchedulingExp, Expression<Func<EM_EducationMissionClass, bool>> missionClassExp,
            Expression<Func<ES_EducationSchedulingWeekNum, bool>> weeknumExp,
            int stopApproveStatus)
        {
            var curDate = DateTime.Today;
            var curTime = DateTime.Now;
            var curSchoolyear = SchoolyearRepository.GetSingle(x => x.IsCurrent == true);
            var firstWeek = curSchoolyear.FirstWeek.AddDays(1 - (int)curSchoolyear.FirstWeek.DayOfWeek);
            var nowWeekNum = (curDate.Subtract(firstWeek).Days + 7) / 7;
            var nowWeekday = (int)curDate.DayOfWeek == 0 ? 7 : (int)curDate.DayOfWeek;
            var futureWeekNumList = new List<int?>();
            var nowAndFutureWeekNumList = new List<int?>();
            var beforeWeekdayList = new List<int?>();
            var beforeCourseTimeList = new List<Guid?>();
            var coursesTimeList = CoursesTimeDAL.Value.GetCoursesTimeQueryable(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                .OrderBy(x => x.StartHour).ToList();

            for (int i = (nowWeekNum + 1); i <= curSchoolyear.WeeksNum; i++)
            {
                futureWeekNumList.Add(i);
            }
            nowAndFutureWeekNumList = new List<int?> { nowWeekNum }.Concat(futureWeekNumList).ToList();

            for (int i = 1; i < nowWeekday; i++)
            {
                beforeWeekdayList.Add(i == 7 ? 0 : i);
            }

            foreach (var coursesTime in coursesTimeList)
            {
                if (new TimeSpan(coursesTime.StartHour ?? 0, coursesTime.StartMinutes ?? 0, 0)
                    .CompareTo(new TimeSpan(curTime.Hour, curTime.Minute, curTime.Second)) <= 0)
                {
                    beforeCourseTimeList.Add(coursesTime.CoursesTimeID);
                }
            }

            var adjustmentQuery = (from s in StaffRepository.GetList(staffExp)    //如当天是星期4，对于星期1-3，读取当周以后周次的排课
                                   from tes in s.ES_EducationSchedulingTeacher
                                   join es in EducationSchedulingRepository.GetList(educationSchedulingExp).Where(x => beforeWeekdayList.Contains(x.Weekday)
                                      && x.SchoolyearID == curSchoolyear.SchoolyearID)
                                       on tes.EducationSchedulingID equals es.EducationSchedulingID
                                   join eswn in EducationSchedulingWeekNumRepository.Entities.Where(weeknumExp)
                                      .Where(x => futureWeekNumList.Contains(x.WeekNum)) on es.EducationSchedulingID equals eswn.EducationSchedulingID
                                   join esc in EducationSchedulingClassRepository.Entities on es.EducationSchedulingClassID equals esc.EducationSchedulingClassID
                                   join emc in EducationMissionClassRepository.Entities.Where(missionClassExp) on esc.EducationMissionClassID equals emc.EducationMissionClassID
                                   join c in CoursematerialRepository.Entities on emc.CoursematerialID equals c.CoursematerialID
                                   select new ScheduleAdjustmentEditView
                                   {
                                       EducationSchedulingID = es.EducationSchedulingID,
                                       EducationSchedulingWeekNumID = eswn.EducationSchedulingWeekNumID,
                                       EducationMissionClassID = emc.EducationMissionClassID,
                                       SchoolyearID = es.SchoolyearID,
                                       EducationMissionClassName = es.EM_EducationSchedulingClass.EM_EducationMissionClass.Name,
                                       CoursematerialID = emc.CoursematerialID,
                                       CourseCode = c.CourseCode,
                                       CourseName = c.CourseName,
                                       EducationMissionClassStartWeekNum = (nowWeekNum > 1) ? nowWeekNum : 1,
                                       EducationMissionClassEndWeekNum = es.CF_Schoolyear.WeeksNum,
                                       WeekNum = eswn.WeekNum,
                                       Weekday = es.Weekday,
                                       TimesSegment = es.EM_CoursesTime.TimesSegment,
                                       CoursesTimeID = es.CoursesTimeID,
                                       StartHour = es.EM_CoursesTime.StartHour,
                                       StartMinutes = es.EM_CoursesTime.StartMinutes,
                                       EndHour = es.EM_CoursesTime.EndHour,
                                       EndMinutes = es.EM_CoursesTime.EndMinutes,
                                       ClassroomTypeID = emc.ClassroomTypeID,
                                       ClassroomID = es.ClassroomID,
                                       ClassroomName = es.CF_Classroom.Name,
                                       CollegeName = es.EM_EducationSchedulingClass.EM_EducationMissionClass.EM_EducationMission.CF_College.Name
                                   }).Concat(    //如当天是星期4，排课也是星期4，对于当前时间之前的节次的课，读取当周以后周次的排课
                        (from s in StaffRepository.GetList(staffExp)
                         from tes in s.ES_EducationSchedulingTeacher
                         join es in EducationSchedulingRepository.GetList(educationSchedulingExp)
                            .Where(x => x.Weekday == nowWeekday && beforeCourseTimeList.Contains(x.CoursesTimeID)
                                && x.SchoolyearID == curSchoolyear.SchoolyearID)
                             on tes.EducationSchedulingID equals es.EducationSchedulingID
                         join eswn in EducationSchedulingWeekNumRepository.Entities.Where(weeknumExp)
                            .Where(x => futureWeekNumList.Contains(x.WeekNum)) on es.EducationSchedulingID equals eswn.EducationSchedulingID
                         join esc in EducationSchedulingClassRepository.Entities on es.EducationSchedulingClassID equals esc.EducationSchedulingClassID
                         join emc in EducationMissionClassRepository.Entities.Where(missionClassExp) on esc.EducationMissionClassID equals emc.EducationMissionClassID
                         join c in CoursematerialRepository.Entities on emc.CoursematerialID equals c.CoursematerialID
                         select new ScheduleAdjustmentEditView
                         {
                             EducationSchedulingID = es.EducationSchedulingID,
                             EducationSchedulingWeekNumID = eswn.EducationSchedulingWeekNumID,
                             EducationMissionClassID = emc.EducationMissionClassID,
                             SchoolyearID = es.SchoolyearID,
                             EducationMissionClassName = es.EM_EducationSchedulingClass.EM_EducationMissionClass.Name,
                             CoursematerialID = emc.CoursematerialID,
                             CourseCode = c.CourseCode,
                             CourseName = c.CourseName,
                             EducationMissionClassStartWeekNum = (nowWeekNum > 1) ? nowWeekNum : 1,
                             EducationMissionClassEndWeekNum = es.CF_Schoolyear.WeeksNum,
                             WeekNum = eswn.WeekNum,
                             Weekday = es.Weekday,
                             TimesSegment = es.EM_CoursesTime.TimesSegment,
                             CoursesTimeID = es.CoursesTimeID,
                             StartHour = es.EM_CoursesTime.StartHour,
                             StartMinutes = es.EM_CoursesTime.StartMinutes,
                             EndHour = es.EM_CoursesTime.EndHour,
                             EndMinutes = es.EM_CoursesTime.EndMinutes,
                             ClassroomTypeID = emc.ClassroomTypeID,
                             ClassroomID = es.ClassroomID,
                             ClassroomName = es.CF_Classroom.Name,
                             CollegeName = es.EM_EducationSchedulingClass.EM_EducationMissionClass.EM_EducationMission.CF_College.Name
                         })
                     ).Concat(    //如当天是星期4，对于星期5-日，读取当周及当周以后周次的排课
                        (from s in StaffRepository.GetList(staffExp)
                         from tes in s.ES_EducationSchedulingTeacher
                         join es in EducationSchedulingRepository.GetList(educationSchedulingExp)
                            .Where(x => !beforeWeekdayList.Contains(x.Weekday) && x.Weekday != nowWeekday
                                && x.SchoolyearID == curSchoolyear.SchoolyearID)
                             on tes.EducationSchedulingID equals es.EducationSchedulingID
                         join eswn in EducationSchedulingWeekNumRepository.Entities.Where(weeknumExp)
                            .Where(x => nowAndFutureWeekNumList.Contains(x.WeekNum)) on es.EducationSchedulingID equals eswn.EducationSchedulingID
                         join esc in EducationSchedulingClassRepository.Entities on es.EducationSchedulingClassID equals esc.EducationSchedulingClassID
                         join emc in EducationMissionClassRepository.Entities.Where(missionClassExp) on esc.EducationMissionClassID equals emc.EducationMissionClassID
                         join c in CoursematerialRepository.Entities on emc.CoursematerialID equals c.CoursematerialID
                         select new ScheduleAdjustmentEditView
                         {
                             EducationSchedulingID = es.EducationSchedulingID,
                             EducationSchedulingWeekNumID = eswn.EducationSchedulingWeekNumID,
                             EducationMissionClassID = emc.EducationMissionClassID,
                             SchoolyearID = es.SchoolyearID,
                             EducationMissionClassName = es.EM_EducationSchedulingClass.EM_EducationMissionClass.Name,
                             CoursematerialID = emc.CoursematerialID,
                             CourseCode = c.CourseCode,
                             CourseName = c.CourseName,
                             EducationMissionClassStartWeekNum = (nowWeekNum > 1) ? nowWeekNum : 1,
                             EducationMissionClassEndWeekNum = es.CF_Schoolyear.WeeksNum,
                             WeekNum = eswn.WeekNum,
                             Weekday = es.Weekday,
                             TimesSegment = es.EM_CoursesTime.TimesSegment,
                             CoursesTimeID = es.CoursesTimeID,
                             StartHour = es.EM_CoursesTime.StartHour,
                             StartMinutes = es.EM_CoursesTime.StartMinutes,
                             EndHour = es.EM_CoursesTime.EndHour,
                             EndMinutes = es.EM_CoursesTime.EndMinutes,
                             ClassroomTypeID = emc.ClassroomTypeID,
                             ClassroomID = es.ClassroomID,
                             ClassroomName = es.CF_Classroom.Name,
                             CollegeName = es.EM_EducationSchedulingClass.EM_EducationMissionClass.EM_EducationMission.CF_College.Name
                         })
                     ).Concat(    //如当天是星期4，排课也是星期4，对于当前时间之后的节次的课，读取当周及当周以后周次的排课
                        (from s in StaffRepository.GetList(staffExp)
                         from tes in s.ES_EducationSchedulingTeacher
                         join es in EducationSchedulingRepository.GetList(educationSchedulingExp)
                            .Where(x => x.Weekday == nowWeekday && !beforeCourseTimeList.Contains(x.CoursesTimeID)
                                && x.SchoolyearID == curSchoolyear.SchoolyearID)
                             on tes.EducationSchedulingID equals es.EducationSchedulingID
                         join eswn in EducationSchedulingWeekNumRepository.Entities.Where(weeknumExp)
                            .Where(x => nowAndFutureWeekNumList.Contains(x.WeekNum)) on es.EducationSchedulingID equals eswn.EducationSchedulingID
                         join esc in EducationSchedulingClassRepository.Entities on es.EducationSchedulingClassID equals esc.EducationSchedulingClassID
                         join emc in EducationMissionClassRepository.Entities.Where(missionClassExp) on esc.EducationMissionClassID equals emc.EducationMissionClassID
                         join c in CoursematerialRepository.Entities on emc.CoursematerialID equals c.CoursematerialID
                         select new ScheduleAdjustmentEditView
                         {
                             EducationSchedulingID = es.EducationSchedulingID,
                             EducationSchedulingWeekNumID = eswn.EducationSchedulingWeekNumID,
                             EducationMissionClassID = emc.EducationMissionClassID,
                             SchoolyearID = es.SchoolyearID,
                             EducationMissionClassName = es.EM_EducationSchedulingClass.EM_EducationMissionClass.Name,
                             CoursematerialID = emc.CoursematerialID,
                             CourseCode = c.CourseCode,
                             CourseName = c.CourseName,
                             EducationMissionClassStartWeekNum = (nowWeekNum > 1) ? nowWeekNum : 1,
                             EducationMissionClassEndWeekNum = es.CF_Schoolyear.WeeksNum,
                             WeekNum = eswn.WeekNum,
                             Weekday = es.Weekday,
                             TimesSegment = es.EM_CoursesTime.TimesSegment,
                             CoursesTimeID = es.CoursesTimeID,
                             StartHour = es.EM_CoursesTime.StartHour,
                             StartMinutes = es.EM_CoursesTime.StartMinutes,
                             EndHour = es.EM_CoursesTime.EndHour,
                             EndMinutes = es.EM_CoursesTime.EndMinutes,
                             ClassroomTypeID = emc.ClassroomTypeID,
                             ClassroomID = es.ClassroomID,
                             ClassroomName = es.CF_Classroom.Name,
                             CollegeName = es.EM_EducationSchedulingClass.EM_EducationMissionClass.EM_EducationMission.CF_College.Name
                         })
                     ).Concat(    //当前学期之后的学期的记录，没有限制
                        (from s in StaffRepository.GetList(staffExp)
                         from tes in s.ES_EducationSchedulingTeacher
                         join es in EducationSchedulingRepository.GetList(educationSchedulingExp)
                             on tes.EducationSchedulingID equals es.EducationSchedulingID
                         join eswn in EducationSchedulingWeekNumRepository.Entities.Where(weeknumExp)
                            .Where(x => !nowAndFutureWeekNumList.Contains(x.WeekNum)) on es.EducationSchedulingID equals eswn.EducationSchedulingID
                         join esc in EducationSchedulingClassRepository.Entities on es.EducationSchedulingClassID equals esc.EducationSchedulingClassID
                         join emc in EducationMissionClassRepository.Entities.Where(missionClassExp) on esc.EducationMissionClassID equals emc.EducationMissionClassID
                         join c in CoursematerialRepository.Entities on emc.CoursematerialID equals c.CoursematerialID
                         join sy in SchoolyearRepository.Entities on es.SchoolyearID equals sy.SchoolyearID
                         where sy.Value > curSchoolyear.Value
                         select new ScheduleAdjustmentEditView
                         {
                             EducationSchedulingID = es.EducationSchedulingID,
                             EducationSchedulingWeekNumID = eswn.EducationSchedulingWeekNumID,
                             EducationMissionClassID = emc.EducationMissionClassID,
                             SchoolyearID = es.SchoolyearID,
                             EducationMissionClassName = es.EM_EducationSchedulingClass.EM_EducationMissionClass.Name,
                             CoursematerialID = emc.CoursematerialID,
                             CourseCode = c.CourseCode,
                             CourseName = c.CourseName,
                             EducationMissionClassStartWeekNum = (nowWeekNum > 1) ? nowWeekNum : 1,
                             EducationMissionClassEndWeekNum = es.CF_Schoolyear.WeeksNum,
                             WeekNum = eswn.WeekNum,
                             Weekday = es.Weekday,
                             TimesSegment = es.EM_CoursesTime.TimesSegment,
                             CoursesTimeID = es.CoursesTimeID,
                             StartHour = es.EM_CoursesTime.StartHour,
                             StartMinutes = es.EM_CoursesTime.StartMinutes,
                             EndHour = es.EM_CoursesTime.EndHour,
                             EndMinutes = es.EM_CoursesTime.EndMinutes,
                             ClassroomTypeID = emc.ClassroomTypeID,
                             ClassroomID = es.ClassroomID,
                             ClassroomName = es.CF_Classroom.Name,
                             CollegeName = es.EM_EducationSchedulingClass.EM_EducationMissionClass.EM_EducationMission.CF_College.Name
                         })
                     );

            var q = (from adjust in adjustmentQuery
                     from stopTeacher in
                         (
                             from stop in EducationSchedulingStopRepository.Entities.Where(x => x.EducationMissionClassID == adjust.EducationMissionClassID
                                 && x.WeekNum == adjust.WeekNum && x.Weekday == adjust.Weekday && x.CoursesTimeID == adjust.CoursesTimeID)
                             from stopTeacher in EducationSchedulingStopTeacherRepository.Entities.Where(x => x.EducationSchedulingStopID == stop.EducationSchedulingStopID)
                             from schedulingTeacher in EducationSchedulingTeacherRepository.Entities.Where(x => x.UserID == stopTeacher.UserID
                                 && x.EducationSchedulingID == adjust.EducationSchedulingID)
                             select schedulingTeacher
                             ).DefaultIfEmpty()
                     where stopTeacher.EducationSchedulingTeacherID == null
                     select new ScheduleStopEditView
                     {
                         EducationSchedulingID = adjust.EducationSchedulingID,
                         EducationSchedulingWeekNumID = adjust.EducationSchedulingWeekNumID,
                         EducationMissionClassID = adjust.EducationMissionClassID,
                         SchoolyearID = adjust.SchoolyearID,
                         EducationMissionClassName = adjust.EducationMissionClassName,
                         CoursematerialID = adjust.CoursematerialID,
                         CourseCode = adjust.CourseCode,
                         CourseName = adjust.CourseName,
                         EducationMissionClassStartWeekNum = adjust.EducationMissionClassStartWeekNum,
                         EducationMissionClassEndWeekNum = adjust.EducationMissionClassEndWeekNum,
                         WeekNum = adjust.WeekNum,
                         Weekday = adjust.Weekday,
                         TimesSegment = adjust.TimesSegment,
                         CoursesTimeID = adjust.CoursesTimeID,
                         StartHour = adjust.StartHour,
                         StartMinutes = adjust.StartMinutes,
                         EndHour = adjust.EndHour,
                         EndMinutes = adjust.EndMinutes,
                         ClassroomTypeID = adjust.ClassroomTypeID,
                         ClassroomID = adjust.ClassroomID,
                         ClassroomName = adjust.ClassroomName,
                         CollegeName = adjust.CollegeName
                     });

            return q;
        }

        public IQueryable<EducationSchedulingStopView> GetEducationSchedulingStopViewQueryable(Expression<Func<CF_Staff, bool>> staffExp,
            Expression<Func<ES_EducationScheduling, bool>> educationSchedulingExp)
        {
            var q = (from s in StaffRepository.GetList(staffExp)
                     from tes in s.ES_EducationSchedulingTeacher
                     join es in EducationSchedulingRepository.GetList(educationSchedulingExp)
                         on tes.EducationSchedulingID equals es.EducationSchedulingID
                     join esc in EducationSchedulingClassRepository.Entities on es.EducationSchedulingClassID equals esc.EducationSchedulingClassID
                     join emc in EducationMissionClassRepository.Entities on esc.EducationMissionClassID equals emc.EducationMissionClassID
                     select new EducationSchedulingStopView
                     {
                         SchoolyearID = es.SchoolyearID,
                         EducationMissionClassID = emc.EducationMissionClassID,
                         EducationMissionClassName = es.EM_EducationSchedulingClass.EM_EducationMissionClass.Name,
                         Weekday = es.Weekday,
                         CoursesTimeID = es.CoursesTimeID,
                         ClassroomID = es.ClassroomID,
                         ClassroomName = es.CF_Classroom.Name
                     });

            return q;
        }

        /// <summary>
        /// 调课时，获取指定星期几的可用课程时间
        /// </summary>
        /// <param name="schoolyearID">学年学期</param>
        /// <param name="weekday">星期几</param>
        /// <param name="weekNumList">查找哪些周次</param>
        /// <param name="educationSchedulingID">需要调课的课程安排ID</param>
        /// <returns></returns>
        public IQueryable<CoursesTimeView> GetNotUseCoursesTime(Guid schoolyearID, int weekday, IList<Guid> teacherList, int? weekNum, Guid educationSchedulingWeekNumID)
        {
            var q = (
                    from ct in this.CoursesTimeDAL.Value.GetCoursesTimeQueryable(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE) 
                    join esct in (
                        from es in this.EducationSchedulingRepository.GetList(x => x.SchoolyearID == schoolyearID
                             && x.Weekday == weekday) 
                        from escs in es.ES_EducationSchedulingTeacher 
                        from escstudent in es.EM_EducationSchedulingClass.CF_Student 
                        join cstudent in
                            (
                                from eswn in EducationSchedulingWeekNumRepository.GetList(x => x.EducationSchedulingWeekNumID == educationSchedulingWeekNumID) 
                                from es in EducationSchedulingRepository.Entities.Where(x => x.EducationSchedulingID == eswn.EducationSchedulingID)
                                from s in es.EM_EducationSchedulingClass.CF_Student
                                select s
                                ) on escstudent.UserID equals cstudent.UserID into dcstudent
                        from ecstudent in dcstudent.DefaultIfEmpty() 
                        where es.ES_EducationSchedulingWeekNum.Any(x => weekNum == x.WeekNum && x.EducationSchedulingWeekNumID != educationSchedulingWeekNumID)
                            && (teacherList.Contains(escs.UserID ?? Guid.Empty) || ecstudent.UserID != null)
                        select es
                        ) on ct.CoursesTimeID equals esct.CoursesTimeID into desct 
                     from eesct in desct.DefaultIfEmpty() 
                     where eesct.EducationSchedulingID == null 
                     select ct
                     );

            return q;
        }

        /// <summary>
        /// 调课时，获取指定具体节次的可用教室
        /// </summary>
        /// <param name="schoolyearID">学年学期</param>
        /// <param name="weekday">星期几</param>
        /// <param name="coursesTimeID">节次</param>
        /// <param name="weekNumList">查找哪些周次</param>
        /// <param name="educationSchedulingID">需要调课的课程安排ID</param>
        /// <returns></returns>
        public IQueryable<ClassroomView> GetNotUseClassroomForAdjustment(Guid schoolyearID, int weekday, Guid coursesTimeID, int classroomTypeID, 
            IList<int?> weekNumList, Guid educationSchedulingID)
        {
            var educationScheduling = this.GetEducationSchedulingViewQueryable(x => x.EducationSchedulingID == educationSchedulingID)
                .FirstOrDefault();
            if (educationScheduling == null)
            {
                throw new Exception("处理异常，无法查询当前排课的信息。");
            }
            var educationSchedulingClass = this.EducationSchedulingClassDAL.Value.GetEducationSchedulingClassViewQueryable(x => x.EducationSchedulingClassID == educationScheduling.EducationSchedulingClassID)
                .FirstOrDefault();
            if (educationSchedulingClass == null)
            {
                throw new Exception("处理异常，无法查询当前排课的信息。");
            }
            var studentList = this.EducationSchedulingClassDAL.Value.GetStudentQueryable(x => x.EducationSchedulingClassID == educationScheduling.EducationSchedulingClassID)
                .ToList();

            return this.ClassroomScheduleDAL.Value.GetAvailableClassroomForAdjustmentNew(
                schoolyearID, weekNumList, weekday, coursesTimeID, educationSchedulingID, studentList.Count, classroomTypeID);
        }

        public IQueryable<TeacherIsOnWorkResultView> CheckTeacherIsOnWork(Expression<Func<ES_EducationScheduling, bool>> schedulingExp,
            Expression<Func<CF_Staff, bool>> staffExp)
        {
            var staffQueryable = (from es in this.EducationSchedulingRepository.GetList(schedulingExp)
                                join s in this.StaffRepository.GetList(staffExp) on true equals true 
                                select new { UserID = s.UserID, es.Weekday, es.CoursesTimeID });
            var teacherIsOnWork = (from tss in this.TeacherScheduleSettingRepository.Entities
                                   join wt in staffQueryable on new { tss.UserID, tss.Weekday, tss.CoursesTimeID }
                                          equals new { UserID = (Guid?)wt.UserID, wt.Weekday, wt.CoursesTimeID }
                                   where tss.IsOnWork == false
                                   select new TeacherIsOnWorkResultView  { 
                                       UserID = tss.UserID ?? Guid.Empty, 
                                       Name = tss.CF_Staff.Sys_User.Name, 
                                       Weekday = tss.Weekday, 
                                       Times = tss.EM_CoursesTime.StartTimes 
                                   })
                                   .OrderBy(x => x.Name).ThenBy(x => (x.Weekday == 0 ? 7 : x.Weekday))
                                   .ThenBy(x => x.Times);

            return teacherIsOnWork;
        }

        public IQueryable<TeacherIsBusyView> CheckTeacherIsBusy(Expression<Func<ES_EducationScheduling, bool>> schedulingExp,
            Expression<Func<CF_Staff, bool>> staffExp)
        {
            var teacherIsBusyQuery = (from es in this.EducationSchedulingRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                                      from escs in es.ES_EducationSchedulingTeacher
                                      from eswn in es.ES_EducationSchedulingWeekNum
                                      join wt in
                                          (
                                              (from es in this.EducationSchedulingRepository.GetList(schedulingExp)
                                               from eswn in es.ES_EducationSchedulingWeekNum
                                               join s in this.StaffRepository.GetList(staffExp) on true equals true 
                                               select new { es.EducationSchedulingClassID, es.SchoolyearID, UserID = s.UserID, es.Weekday, es.CoursesTimeID, eswn.WeekNum })
                                              ) on new { es.SchoolyearID, escs.UserID, es.Weekday, es.CoursesTimeID, eswn.WeekNum }
                                             equals new { wt.SchoolyearID, UserID = (Guid?)wt.UserID, wt.Weekday, wt.CoursesTimeID, wt.WeekNum }
                                      where es.EducationSchedulingClassID != wt.EducationSchedulingClassID 
                                      group new { es, escs } by new 
                                      { 
                                          es.Weekday,
                                          es.EM_CoursesTime.StartTimes, 
                                          escs.UserID,
                                          escs.CF_Staff.Sys_User.Name,
                                      } into g 
                                      select new TeacherIsBusyView 
                                      { 
                                          UserID = g.Key.UserID ?? Guid.Empty,
                                          Name = g.Key.Name,
                                          Weekday = g.Key.Weekday,
                                          Times = g.Key.StartTimes
                                      });

            return teacherIsBusyQuery;
        }

        public void CheckTeacherIsAvailable(Expression<Func<ES_EducationScheduling, bool>> schedulingExp,
            Expression<Func<CF_Staff, bool>> staffExp)
        {
            var teacherIsOnWork = (from tss in this.TeacherScheduleSettingRepository.Entities
                                   join wt in
                                       (
                                           (from es in this.EducationSchedulingRepository.GetList(schedulingExp) 
                                            from s in this.StaffRepository.GetList(staffExp)
                                            select new { UserID = s.UserID, es.Weekday, es.CoursesTimeID })
                                           ) on new { tss.UserID, tss.Weekday, tss.CoursesTimeID }
                                          equals new { UserID = (Guid?)wt.UserID, wt.Weekday, wt.CoursesTimeID }
                                   where tss.IsOnWork == false
                                   select new { tss.UserID, tss.CF_Staff.Sys_User.Name, tss.Weekday, tss.EM_CoursesTime.StartTimes, tss.EM_CoursesTime.EndTimes })
                                   .OrderBy(x => x.Name).ThenBy(x => (x.Weekday == 0 ? 7 : x.Weekday))
                                   .ThenBy(x => x.StartTimes)
                                   .ToList();

            if (teacherIsOnWork.Count > 0)
            {
                var notAvailableTeacherMessage = "";

                teacherIsOnWork.ForEach(x => notAvailableTeacherMessage += string.Format("任务班在{1}第{2}节课有课，但教师{0}在该节次不能排课。\n", 
                    x.Name, 
                    WeekHelper.WeekDictionary[x.Weekday ?? 0],
                    (x.StartTimes == x.EndTimes) ? x.StartTimes.ToString() : x.StartTimes.ToString() + "-" + x.EndTimes.ToString()));

                throw new Exception(notAvailableTeacherMessage);
            }

            var teacherIsBusyQuery = (from es in this.EducationSchedulingRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                                      from escs in es.ES_EducationSchedulingTeacher
                                      from eswn in es.ES_EducationSchedulingWeekNum
                                      join wt in
                                          (
                                              (from es in this.EducationSchedulingRepository.GetList(schedulingExp)
                                               from eswn in es.ES_EducationSchedulingWeekNum
                                               from s in this.StaffRepository.GetList(staffExp)
                                               select new { es.EducationSchedulingClassID, es.SchoolyearID, UserID = s.UserID, es.Weekday, es.CoursesTimeID, eswn.WeekNum })
                                              ) on new { es.SchoolyearID, escs.CF_Staff.UserID, es.Weekday, es.CoursesTimeID, eswn.WeekNum }
                                             equals new { wt.SchoolyearID, wt.UserID, wt.Weekday, wt.CoursesTimeID, wt.WeekNum } 
                                      where es.EducationSchedulingClassID != wt.EducationSchedulingClassID 
                                      select new { es.EducationSchedulingID, escs.UserID, escs.CF_Staff.Sys_User.Name, eswn.WeekNum });


        }

        public IQueryable<EducationSchedulingConflictView> GetConflictAdjustmentSchedule(Expression<Func<ES_EducationSchedulingWeekNum, bool>> exp,
            IList<int?> adjustmentSubmitedStatusList, Guid fromUserID, int? toWeekNum, int? toWeekday, Guid? toUserID, Guid? toCoursesTimeID,
            Guid? toClassroom)
        {
            var adjustSql = (from weekNum in EducationSchedulingWeekNumRepository.GetList(exp)
                             from scheduling in EducationSchedulingRepository.Entities.Where(x => x.EducationSchedulingID == weekNum.EducationSchedulingID)
                             from schedulingClass in EducationSchedulingClassRepository.Entities.Where(x => x.EducationSchedulingClassID == scheduling.EducationSchedulingClassID)
                             from missionClass in EducationMissionClassRepository.Entities.Where(x => x.EducationMissionClassID == schedulingClass.EducationMissionClassID)
                             from teacher in EducationSchedulingTeacherRepository.Entities.Where(x => x.EducationSchedulingID == scheduling.EducationSchedulingID)
                             from schoolyear in SchoolyearRepository.Entities.Where(x => x.SchoolyearID == scheduling.SchoolyearID) 
                             from course in CoursematerialRepository.Entities.Where(x => x.CoursematerialID == missionClass.CoursematerialID) 
                             from courseTime in CoursesTimeDAL.Value.coursesTimeRepository.Entities
                                .Where(x => x.CoursesTimeID == (toCoursesTimeID.HasValue ? toCoursesTimeID : scheduling.CoursesTimeID)) 
                             from classroom in ClassroomRepository.Entities
                                .Where(x => x.ClassroomID == (toClassroom.HasValue ? toClassroom : scheduling.ClassroomID)) 
                             from user in UserRepository.Entities
                                .Where(x => x.UserID == ((teacher.UserID == fromUserID) ? (toUserID.HasValue ? toUserID : teacher.UserID) : teacher.UserID))
                             select new EducationSchedulingConflictView
                             {
                                 EducationSchedulingWeekNumID = weekNum.EducationSchedulingWeekNumID,
                                 EducationSchedulingAdjustmentID = null,
                                 SchoolyearID = scheduling.SchoolyearID,
                                 SchoolyearCode = schoolyear.Code,
                                 EducationSchedulingClassID = scheduling.EducationSchedulingClassID,
                                 EducationMissionClassName = missionClass.Name,
                                 CoursematerialID = missionClass.CoursematerialID,
                                 CourseName = course.CourseName,
                                 WeekNum = toWeekNum.HasValue ? toWeekNum : weekNum.WeekNum,
                                 Weekday = toWeekday.HasValue ? toWeekday : scheduling.Weekday,
                                 CoursesTime = toCoursesTimeID.HasValue ? toCoursesTimeID : scheduling.CoursesTimeID,
                                 StartTimes = courseTime.StartTimes,
                                 EndTimes = courseTime.EndTimes,
                                 ClassroomID = toClassroom.HasValue ? toClassroom : scheduling.ClassroomID,
                                 ClassroomName = classroom.Name,
                                 StudentNum = schedulingClass.CF_Student.Count,
                                 UserID = (teacher.UserID == fromUserID) ? (toUserID.HasValue ? toUserID : teacher.UserID) : teacher.UserID,
                                 UserName = user.Name,
                                 Reason = ""
                             });

            //return GetConflictAdjustmentSchedule(adjustSql, adjustmentSubmitedStatusList);
            return adjustSql;
        }
        public IQueryable<EducationSchedulingConflictView> GetConflictAdjustmentSchedule(Expression<Func<ES_EducationSchedulingAdjustment, bool>> exp,
           IList<int?> adjustmentSubmitedStatusList)
        {
            var adjustSql = (from adjust in EducationSchedulingAdjustmentRepository.GetList(exp)
                             from missionClass in EducationMissionClassRepository.Entities.Where(x => 
                                 x.EducationMissionClassID == adjust.EducationMissionClassID)
                             from mission in EducationMissionRepository.Entities.Where(x => x.EducationMissionID == missionClass.EducationMissionID) 
                             from schedulingClass in EducationSchedulingClassRepository.Entities.Where(x => 
                                 x.EducationMissionClassID == missionClass.EducationMissionClassID) 
                             from teacher in EducationSchedulingAdjustmentToTeacherRepository.Entities.Where(x => 
                                 x.EducationSchedulingAdjustmentID == adjust.EducationSchedulingAdjustmentID)
                             from schoolyear in SchoolyearRepository.Entities.Where(x => x.SchoolyearID == mission.SchoolyearID)
                             from course in CoursematerialRepository.Entities.Where(x => x.CoursematerialID == missionClass.CoursematerialID)
                             from courseTime in CoursesTimeDAL.Value.coursesTimeRepository.Entities.Where(x => x.CoursesTimeID == adjust.ToCoursesTimeID)
                             from classroom in ClassroomRepository.Entities.Where(x => x.ClassroomID == adjust.ToClassroomID)
                             from user in UserRepository.Entities.Where(x => x.UserID == teacher.UserID) 
                             from weekNum in (
                                from scheduling in EducationSchedulingRepository.Entities.Where(x => 
                                    x.EducationSchedulingClassID == schedulingClass.EducationSchedulingClassID 
                                    && x.SchoolyearID == mission.SchoolyearID && x.Weekday == adjust.Weekday && x.CoursesTimeID == adjust.CoursesTimeID)
                                from weekNum in EducationSchedulingWeekNumRepository.Entities.Where(x => x.EducationSchedulingID == scheduling.EducationSchedulingID
                                    && x.WeekNum == adjust.WeekNum)
                                select weekNum).DefaultIfEmpty() 
                             select new EducationSchedulingConflictView
                             {
                                 EducationSchedulingWeekNumID = weekNum.EducationSchedulingWeekNumID,
                                 EducationSchedulingAdjustmentID = adjust.EducationSchedulingAdjustmentID,
                                 SchoolyearID = mission.SchoolyearID,
                                 SchoolyearCode = schoolyear.Code,
                                 EducationSchedulingClassID = schedulingClass.EducationSchedulingClassID,
                                 EducationMissionClassName = missionClass.Name,
                                 CoursematerialID = missionClass.CoursematerialID,
                                 CourseName = course.CourseName,
                                 WeekNum = adjust.ToWeekNum,
                                 Weekday = adjust.ToWeekday,
                                 CoursesTime = adjust.ToCoursesTimeID,
                                 StartTimes = courseTime.StartTimes,
                                 EndTimes = courseTime.EndTimes,
                                 ClassroomID = adjust.ToClassroomID,
                                 ClassroomName = classroom.Name,
                                 StudentNum = schedulingClass.CF_Student.Count,
                                 UserID = teacher.UserID,
                                 UserName = user.Name,
                                 Reason = ""
                             });

            //return GetConflictAdjustmentSchedule(adjustSql, adjustmentSubmitedStatusList);
            return adjustSql;
        }

        public IQueryable<EducationSchedulingConflictView> GetSelfClassroomConflictQueryable(IQueryable<EducationSchedulingConflictView> adjustSql)
        {
            var selfClassroomConflictSql = (from adjust in adjustSql
                                            group adjust by new { adjust.WeekNum, adjust.Weekday, adjust.CoursesTime, adjust.UserID, adjust.ClassroomID } into g
                                            where g.Count() > 1
                                            select g).SelectMany(x => x)
                                                       .Select(x => new EducationSchedulingConflictView
                                                       {
                                                           EducationSchedulingWeekNumID = x.EducationSchedulingWeekNumID,
                                                           EducationSchedulingAdjustmentID = x.EducationSchedulingAdjustmentID,
                                                           SchoolyearID = x.SchoolyearID,
                                                           SchoolyearCode = x.SchoolyearCode,
                                                           EducationSchedulingClassID = x.EducationSchedulingClassID,
                                                           EducationMissionClassName = x.EducationMissionClassName,
                                                           CoursematerialID = x.CoursematerialID,
                                                           CourseName = x.CourseName,
                                                           WeekNum = x.WeekNum,
                                                           Weekday = x.Weekday,
                                                           CoursesTime = x.CoursesTime,
                                                           StartTimes = x.StartTimes,
                                                           EndTimes = x.EndTimes,
                                                           ClassroomID = x.ClassroomID,
                                                           ClassroomName = x.ClassroomName,
                                                           StudentNum = x.StudentNum,
                                                           UserID = x.UserID,
                                                           UserName = x.UserName,
                                                           Reason = "和其他提交的数据存在教室冲突。"
                                                       });

            return selfClassroomConflictSql;
        }

        public IQueryable<EducationSchedulingConflictView> GetSelfConflictQueryable(IQueryable<EducationSchedulingConflictView> adjustSql)
        {
            var result = (from adjust in adjustSql
                          group adjust by new { adjust.EducationSchedulingWeekNumID, adjust.UserID } into g
                          where g.Count() > 1
                          select g).SelectMany(x => x)
                        .Select(x => new EducationSchedulingConflictView
                        {
                            EducationSchedulingWeekNumID = x.EducationSchedulingWeekNumID,
                            EducationSchedulingAdjustmentID = x.EducationSchedulingAdjustmentID,
                            SchoolyearID = x.SchoolyearID,
                            SchoolyearCode = x.SchoolyearCode,
                            EducationSchedulingClassID = x.EducationSchedulingClassID,
                            EducationMissionClassName = x.EducationMissionClassName,
                            CoursematerialID = x.CoursematerialID,
                            CourseName = x.CourseName,
                            WeekNum = x.WeekNum,
                            Weekday = x.Weekday,
                            CoursesTime = x.CoursesTime,
                            StartTimes = x.StartTimes,
                            EndTimes = x.EndTimes,
                            ClassroomID = x.ClassroomID,
                            ClassroomName = x.ClassroomName,
                            StudentNum = x.StudentNum,
                            UserID = x.UserID,
                            UserName = x.UserName,
                            Reason = "涉及了对同一节课的重复调动。"
                        });

            return result;
        }

        public IQueryable<EducationSchedulingConflictView> GetSelfTeacherConflictQueryable(IQueryable<EducationSchedulingConflictView> adjustSql)
        {
            var selfTeacherConflictSql = (from adjust in adjustSql
                                          group adjust by new { adjust.WeekNum, adjust.Weekday, adjust.CoursesTime, adjust.UserID } into g
                                          where g.Count() > 1
                                          select g).SelectMany(x => x)
                                            .Select(x => new EducationSchedulingConflictView
                                            {
                                                EducationSchedulingWeekNumID = x.EducationSchedulingWeekNumID,
                                                EducationSchedulingAdjustmentID = x.EducationSchedulingAdjustmentID,
                                                SchoolyearID = x.SchoolyearID,
                                                SchoolyearCode = x.SchoolyearCode,
                                                EducationSchedulingClassID = x.EducationSchedulingClassID,
                                                EducationMissionClassName = x.EducationMissionClassName,
                                                CoursematerialID = x.CoursematerialID,
                                                CourseName = x.CourseName,
                                                WeekNum = x.WeekNum,
                                                Weekday = x.Weekday,
                                                CoursesTime = x.CoursesTime,
                                                StartTimes = x.StartTimes,
                                                EndTimes = x.EndTimes,
                                                ClassroomID = x.ClassroomID,
                                                ClassroomName = x.ClassroomName,
                                                StudentNum = x.StudentNum,
                                                UserID = x.UserID,
                                                UserName = x.UserName,
                                                Reason = "和其他提交的数据存在教师冲突。"
                                            });

            return selfTeacherConflictSql;
        }

        public IQueryable<EducationSchedulingConflictView> GetSelfStudentConflictQueryable(IQueryable<EducationSchedulingConflictView> adjustSql)
        {
            var selfStudentConflictSql = (from adjust in adjustSql
                                          from schedulingClass in EducationSchedulingClassRepository.Entities
                                            .Where(x => x.EducationSchedulingClassID == adjust.EducationSchedulingClassID)
                                          from student in schedulingClass.CF_Student
                                          group adjust by new { adjust.WeekNum, adjust.Weekday, adjust.CoursesTime, adjust.UserID, StudentUserID = student.UserID } into g
                                          where g.Count() > 1
                                          select g).SelectMany(x => x)
                                            .Select(x => new EducationSchedulingConflictView
                                            {
                                                EducationSchedulingWeekNumID = x.EducationSchedulingWeekNumID,
                                                EducationSchedulingAdjustmentID = x.EducationSchedulingAdjustmentID,
                                                SchoolyearID = x.SchoolyearID,
                                                SchoolyearCode = x.SchoolyearCode,
                                                EducationSchedulingClassID = x.EducationSchedulingClassID,
                                                EducationMissionClassName = x.EducationMissionClassName,
                                                CoursematerialID = x.CoursematerialID,
                                                CourseName = x.CourseName,
                                                WeekNum = x.WeekNum,
                                                Weekday = x.Weekday,
                                                CoursesTime = x.CoursesTime,
                                                StartTimes = x.StartTimes,
                                                EndTimes = x.EndTimes,
                                                ClassroomID = x.ClassroomID,
                                                ClassroomName = x.ClassroomName,
                                                StudentNum = x.StudentNum,
                                                UserID = x.UserID,
                                                UserName = x.UserName,
                                                Reason = "和其他提交的数据存在学生冲突。"
                                            });

            return selfStudentConflictSql;
        }

        public IQueryable<EducationSchedulingConflictView> GetClassroomSeatConflictQueryable(IQueryable<EducationSchedulingConflictView> adjustSql)
        {
            var query = (from adjust in adjustSql
                        from classroom in ClassroomRepository.Entities.Where(x => x.ClassroomID == adjust.ClassroomID)
                        where (classroom.Totalseating ?? 0) != 0 && classroom.Totalseating < adjust.StudentNum
                        select new EducationSchedulingConflictView
                        {
                            EducationSchedulingWeekNumID = adjust.EducationSchedulingWeekNumID,
                            EducationSchedulingAdjustmentID = adjust.EducationSchedulingAdjustmentID,
                            SchoolyearID = adjust.SchoolyearID,
                            SchoolyearCode = adjust.SchoolyearCode,
                            EducationSchedulingClassID = adjust.EducationSchedulingClassID,
                            EducationMissionClassName = adjust.EducationMissionClassName,
                            CoursematerialID = adjust.CoursematerialID,
                            CourseName = adjust.CourseName,
                            WeekNum = adjust.WeekNum,
                            Weekday = adjust.Weekday,
                            CoursesTime = adjust.CoursesTime,
                            StartTimes = adjust.StartTimes,
                            EndTimes = adjust.EndTimes,
                            ClassroomID = adjust.ClassroomID,
                            ClassroomName = adjust.ClassroomName,
                            StudentNum = adjust.StudentNum,
                            UserID = adjust.UserID,
                            UserName = adjust.UserName,
                            Reason = "教室坐席数量不足。"
                        }
                    );

            return query;
        }

        public IQueryable<EducationSchedulingConflictView> GetClassroomReserveQueryable(IQueryable<EducationSchedulingConflictView> adjustSql)
        {
            var query = (
                        from adjust in adjustSql
                        from reserve in ClassroomReserveRepository.Entities.Where(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE
                            && x.IsReserved == true && x.ClassroomID == adjust.ClassroomID)
                        select new EducationSchedulingConflictView
                        {
                            EducationSchedulingWeekNumID = adjust.EducationSchedulingWeekNumID,
                            EducationSchedulingAdjustmentID = adjust.EducationSchedulingAdjustmentID,
                            SchoolyearID = adjust.SchoolyearID,
                            SchoolyearCode = adjust.SchoolyearCode,
                            EducationSchedulingClassID = adjust.EducationSchedulingClassID,
                            EducationMissionClassName = adjust.EducationMissionClassName,
                            CoursematerialID = adjust.CoursematerialID,
                            CourseName = adjust.CourseName,
                            WeekNum = adjust.WeekNum,
                            Weekday = adjust.Weekday,
                            CoursesTime = adjust.CoursesTime,
                            StartTimes = adjust.StartTimes,
                            EndTimes = adjust.EndTimes,
                            ClassroomID = adjust.ClassroomID,
                            ClassroomName = adjust.ClassroomName,
                            StudentNum = adjust.StudentNum,
                            UserID = adjust.UserID,
                            UserName = adjust.UserName,
                            Reason = "教室已被预留。"
                        }
                        );

            return query;
        }

        public IQueryable<EducationSchedulingConflictView> GetClassroomScheduleConflicQueryable(IQueryable<EducationSchedulingConflictView> adjustSql)
        {
            var query = (
                        from adjust in adjustSql
                        from scheduleSet in ClassroomScheduleSettingRepository.Entities.Where(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE
                            && x.IsOnWork == false && x.ClassroomID == adjust.ClassroomID && x.Weekday == adjust.Weekday
                            && x.CoursesTimeID == adjust.CoursesTime)
                        select new EducationSchedulingConflictView
                        {
                            EducationSchedulingWeekNumID = adjust.EducationSchedulingWeekNumID,
                            EducationSchedulingAdjustmentID = adjust.EducationSchedulingAdjustmentID,
                            SchoolyearID = adjust.SchoolyearID,
                            SchoolyearCode = adjust.SchoolyearCode,
                            EducationSchedulingClassID = adjust.EducationSchedulingClassID,
                            EducationMissionClassName = adjust.EducationMissionClassName,
                            CoursematerialID = adjust.CoursematerialID,
                            CourseName = adjust.CourseName,
                            WeekNum = adjust.WeekNum,
                            Weekday = adjust.Weekday,
                            CoursesTime = adjust.CoursesTime,
                            StartTimes = adjust.StartTimes,
                            EndTimes = adjust.EndTimes,
                            ClassroomID = adjust.ClassroomID,
                            ClassroomName = adjust.ClassroomName,
                            StudentNum = adjust.StudentNum,
                            UserID = adjust.UserID,
                            UserName = adjust.UserName,
                            Reason = "教室不在可排时段内。"
                        }
                        );

            return query;
        }

        public IQueryable<EducationSchedulingConflictView> GetClassroomConflicQueryable(IQueryable<EducationSchedulingConflictView> adjustSql)
        {
            var query = (
                        from adjust in adjustSql
                        from classroom in ClassroomRepository.Entities.Where(x => x.ClassroomID == adjust.ClassroomID)
                        from scheduling in EducationSchedulingRepository.Entities.Where(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE
                            && x.ClassroomID == adjust.ClassroomID && x.Weekday == adjust.Weekday && x.CoursesTimeID == adjust.CoursesTime
                            && x.SchoolyearID == adjust.SchoolyearID)
                        from weekNum in EducationSchedulingWeekNumRepository.Entities.Where(x => x.EducationSchedulingID == scheduling.EducationSchedulingID
                            && adjust.WeekNum == x.WeekNum && x.EducationSchedulingWeekNumID != adjust.EducationSchedulingWeekNumID)
                        where classroom.IsConcurrentUse != true
                        select new EducationSchedulingConflictView
                        {
                            EducationSchedulingWeekNumID = adjust.EducationSchedulingWeekNumID,
                            EducationSchedulingAdjustmentID = adjust.EducationSchedulingAdjustmentID,
                            SchoolyearID = adjust.SchoolyearID,
                            SchoolyearCode = adjust.SchoolyearCode,
                            EducationSchedulingClassID = adjust.EducationSchedulingClassID,
                            EducationMissionClassName = adjust.EducationMissionClassName,
                            CoursematerialID = adjust.CoursematerialID,
                            CourseName = adjust.CourseName,
                            WeekNum = adjust.WeekNum,
                            Weekday = adjust.Weekday,
                            CoursesTime = adjust.CoursesTime,
                            StartTimes = adjust.StartTimes,
                            EndTimes = adjust.EndTimes,
                            ClassroomID = adjust.ClassroomID,
                            ClassroomName = adjust.ClassroomName,
                            StudentNum = adjust.StudentNum,
                            UserID = adjust.UserID,
                            UserName = adjust.UserName,
                            Reason = "教室已被其他课程使用。"
                        }
                        );

            return query;
        }

        public IQueryable<EducationSchedulingConflictView> GetClassroomExcessiveUseQueryable(IQueryable<EducationSchedulingConflictView> adjustSql)
        {
            var query = (
                        from adjust in adjustSql
                        from classroom in ClassroomRepository.Entities.Where(x => x.ClassroomID == adjust.ClassroomID)
                        from excessiveUse in ClassroomExcessiveUseRepository.Entities.Where(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE
                            && x.ClassroomID == classroom.ClassroomID && x.SchoolyearID == adjust.SchoolyearID)
                        from scheduling in ClassroomExcessiveUseSchedulingRepository.Entities.Where(x => x.ClassroomExcessiveUseID == excessiveUse.ClassroomExcessiveUseID
                            && x.Weekday == adjust.Weekday && x.CoursesTimeID == adjust.CoursesTime)
                        from weekNum in ClassroomExcessiveUseSchedulingWeekNumRepository.Entities.Where(x => x.ClassroomExcessiveUseSchedulingID == scheduling.ClassroomExcessiveUseSchedulingID
                            && x.WeekNum == adjust.WeekNum)
                        where classroom.IsConcurrentUse != true
                        select new EducationSchedulingConflictView
                        {
                            EducationSchedulingWeekNumID = adjust.EducationSchedulingWeekNumID,
                            EducationSchedulingAdjustmentID = adjust.EducationSchedulingAdjustmentID,
                            SchoolyearID = adjust.SchoolyearID,
                            SchoolyearCode = adjust.SchoolyearCode,
                            EducationSchedulingClassID = adjust.EducationSchedulingClassID,
                            EducationMissionClassName = adjust.EducationMissionClassName,
                            CoursematerialID = adjust.CoursematerialID,
                            CourseName = adjust.CourseName,
                            WeekNum = adjust.WeekNum,
                            Weekday = adjust.Weekday,
                            CoursesTime = adjust.CoursesTime,
                            StartTimes = adjust.StartTimes,
                            EndTimes = adjust.EndTimes,
                            ClassroomID = adjust.ClassroomID,
                            ClassroomName = adjust.ClassroomName,
                            StudentNum = adjust.StudentNum,
                            UserID = adjust.UserID,
                            UserName = adjust.UserName,
                            Reason = "教室已被占用。"
                        }
                        );

            return query;
        }

        public IQueryable<EducationSchedulingConflictView> GetClassroomAdjustmentConflictQueryable(IQueryable<EducationSchedulingConflictView> adjustSql,
            IList<int?> adjustmentSubmitedStatusList)
        {
            var query = (
                        from adjust in adjustSql
                        from classroom in ClassroomRepository.Entities.Where(x => x.ClassroomID == adjust.ClassroomID)
                        from other in EducationSchedulingAdjustmentRepository.Entities.Where(x =>
                            adjustmentSubmitedStatusList.Contains(x.RecordStatus) && x.ToClassroomID == adjust.ClassroomID
                            && x.ToWeekday == adjust.Weekday && x.ToCoursesTimeID == adjust.CoursesTime && x.ToWeekNum == adjust.WeekNum
                            && x.EducationSchedulingAdjustmentID != adjust.EducationSchedulingAdjustmentID)
                        where classroom.IsConcurrentUse != true
                        select new EducationSchedulingConflictView
                        {
                            EducationSchedulingWeekNumID = adjust.EducationSchedulingWeekNumID,
                            EducationSchedulingAdjustmentID = adjust.EducationSchedulingAdjustmentID,
                            SchoolyearID = adjust.SchoolyearID,
                            SchoolyearCode = adjust.SchoolyearCode,
                            EducationSchedulingClassID = adjust.EducationSchedulingClassID,
                            EducationMissionClassName = adjust.EducationMissionClassName,
                            CoursematerialID = adjust.CoursematerialID,
                            CourseName = adjust.CourseName,
                            WeekNum = adjust.WeekNum,
                            Weekday = adjust.Weekday,
                            CoursesTime = adjust.CoursesTime,
                            StartTimes = adjust.StartTimes,
                            EndTimes = adjust.EndTimes,
                            ClassroomID = adjust.ClassroomID,
                            ClassroomName = adjust.ClassroomName,
                            StudentNum = adjust.StudentNum,
                            UserID = adjust.UserID,
                            UserName = adjust.UserName,
                            Reason = "教室已被其他人申请调课占用。"
                        }
                        );

            return query;
        }

        public IQueryable<EducationSchedulingConflictView> GetCourseScheduleConflictQueryable(IQueryable<EducationSchedulingConflictView> adjustSql)
        {
            var query = (from adjust in adjustSql
                         from scheduleSet in CourseScheduleSettingRepository.Entities.Where(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE
                             && x.IsOnWork == false && x.CoursematerialID == adjust.CoursematerialID && x.Weekday == adjust.Weekday
                             && x.CoursesTimeID == adjust.CoursesTime)
                         select new EducationSchedulingConflictView
                         {
                             EducationSchedulingWeekNumID = adjust.EducationSchedulingWeekNumID,
                             EducationSchedulingAdjustmentID = adjust.EducationSchedulingAdjustmentID,
                             SchoolyearID = adjust.SchoolyearID,
                             SchoolyearCode = adjust.SchoolyearCode,
                             EducationSchedulingClassID = adjust.EducationSchedulingClassID,
                             EducationMissionClassName = adjust.EducationMissionClassName,
                             CoursematerialID = adjust.CoursematerialID,
                             CourseName = adjust.CourseName,
                             WeekNum = adjust.WeekNum,
                             Weekday = adjust.Weekday,
                             CoursesTime = adjust.CoursesTime,
                             StartTimes = adjust.StartTimes,
                             EndTimes = adjust.EndTimes,
                             ClassroomID = adjust.ClassroomID,
                             ClassroomName = adjust.ClassroomName,
                             StudentNum = adjust.StudentNum,
                             UserID = adjust.UserID,
                             UserName = adjust.UserName,
                             Reason = "课程不在可排时段内。"
                         });

            return query;
        }

        public IQueryable<EducationSchedulingConflictView> GetTeacherScheduleConflictQueryable(IQueryable<EducationSchedulingConflictView> adjustSql)
        {
            var query = (from adjust in adjustSql
                         from scheduleSet in TeacherScheduleSettingRepository.Entities.Where(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE
                           && x.IsOnWork == false && x.UserID == adjust.UserID && x.Weekday == adjust.Weekday
                           && x.CoursesTimeID == adjust.CoursesTime)
                         select new EducationSchedulingConflictView
                         {
                             EducationSchedulingWeekNumID = adjust.EducationSchedulingWeekNumID,
                             EducationSchedulingAdjustmentID = adjust.EducationSchedulingAdjustmentID,
                             SchoolyearID = adjust.SchoolyearID,
                             SchoolyearCode = adjust.SchoolyearCode,
                             EducationSchedulingClassID = adjust.EducationSchedulingClassID,
                             EducationMissionClassName = adjust.EducationMissionClassName,
                             CoursematerialID = adjust.CoursematerialID,
                             CourseName = adjust.CourseName,
                             WeekNum = adjust.WeekNum,
                             Weekday = adjust.Weekday,
                             CoursesTime = adjust.CoursesTime,
                             StartTimes = adjust.StartTimes,
                             EndTimes = adjust.EndTimes,
                             ClassroomID = adjust.ClassroomID,
                             ClassroomName = adjust.ClassroomName,
                             StudentNum = adjust.StudentNum,
                             UserID = adjust.UserID,
                             UserName = adjust.UserName,
                             Reason = "教师不在可排时段内。"
                         });

            return query;
        }

        public IQueryable<EducationSchedulingConflictView> GetTeacherConflictQueryable(IQueryable<EducationSchedulingConflictView> adjustSql)
        {
            var query = (
                        from adjust in adjustSql
                        from scheduling in EducationSchedulingRepository.Entities.Where(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE
                            && x.ES_EducationSchedulingTeacher.Any(w => w.UserID == adjust.UserID) && x.Weekday == adjust.Weekday
                            && x.CoursesTimeID == adjust.CoursesTime && x.SchoolyearID == adjust.SchoolyearID)
                        from weekNum in EducationSchedulingWeekNumRepository.Entities.Where(x => x.EducationSchedulingID == scheduling.EducationSchedulingID
                            && adjust.WeekNum == x.WeekNum && x.EducationSchedulingWeekNumID != adjust.EducationSchedulingWeekNumID)
                        select new EducationSchedulingConflictView
                        {
                            EducationSchedulingWeekNumID = adjust.EducationSchedulingWeekNumID,
                            EducationSchedulingAdjustmentID = adjust.EducationSchedulingAdjustmentID,
                            SchoolyearID = adjust.SchoolyearID,
                            SchoolyearCode = adjust.SchoolyearCode,
                            EducationSchedulingClassID = adjust.EducationSchedulingClassID,
                            EducationMissionClassName = adjust.EducationMissionClassName,
                            CoursematerialID = adjust.CoursematerialID,
                            CourseName = adjust.CourseName,
                            WeekNum = adjust.WeekNum,
                            Weekday = adjust.Weekday,
                            CoursesTime = adjust.CoursesTime,
                            StartTimes = adjust.StartTimes,
                            EndTimes = adjust.EndTimes,
                            ClassroomID = adjust.ClassroomID,
                            ClassroomName = adjust.ClassroomName,
                            StudentNum = adjust.StudentNum,
                            UserID = adjust.UserID,
                            UserName = adjust.UserName,
                            Reason = "教师在该时段有课。"
                        });

            return query;
        }

        public IQueryable<EducationSchedulingConflictView> GetTeacherAdjustmentConflictQueryable(IQueryable<EducationSchedulingConflictView> adjustSql,
            IList<int?> adjustmentSubmitedStatusList)
        {
            //有BUG，课程调整缺少学年学期的判断
            var query = (
                        from adjust in adjustSql
                        from other in EducationSchedulingAdjustmentRepository.Entities.Where(x => x.ToUserID == adjust.UserID 
                            && x.ToWeekNum == adjust.WeekNum && x.ToWeekday == adjust.Weekday && x.ToCoursesTimeID == adjust.CoursesTime
                            && adjustmentSubmitedStatusList.Contains(x.RecordStatus)
                            && x.EducationSchedulingAdjustmentID != adjust.EducationSchedulingAdjustmentID)
                        select new EducationSchedulingConflictView
                        {
                            EducationSchedulingWeekNumID = adjust.EducationSchedulingWeekNumID,
                            EducationSchedulingAdjustmentID = adjust.EducationSchedulingAdjustmentID,
                            SchoolyearID = adjust.SchoolyearID,
                            SchoolyearCode = adjust.SchoolyearCode,
                            EducationSchedulingClassID = adjust.EducationSchedulingClassID,
                            EducationMissionClassName = adjust.EducationMissionClassName,
                            CoursematerialID = adjust.CoursematerialID,
                            CourseName = adjust.CourseName,
                            WeekNum = adjust.WeekNum,
                            Weekday = adjust.Weekday,
                            CoursesTime = adjust.CoursesTime,
                            StartTimes = adjust.StartTimes,
                            EndTimes = adjust.EndTimes,
                            ClassroomID = adjust.ClassroomID,
                            ClassroomName = adjust.ClassroomName,
                            StudentNum = adjust.StudentNum,
                            UserID = adjust.UserID,
                            UserName = adjust.UserName,
                            Reason = "教师该时段已被其他人申请调课占用。"
                        }
                        );

            return query;
        }

        public IQueryable<EducationSchedulingConflictView> GetStudentScheduleConflictQueryable(IQueryable<EducationSchedulingConflictView> adjustSql)
        {
            var query = (from adjust in adjustSql
                         from schedulingClass in EducationSchedulingClassRepository.Entities.Where(x => x.EducationSchedulingClassID == adjust.EducationSchedulingClassID)
                         from student in schedulingClass.CF_Student
                         from scheduleSet in ClassmajorScheduleSettingRepository.Entities.Where(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE
                           && x.IsOnWork == false && x.ClassmajorID == student.ClassmajorID && x.Weekday == adjust.Weekday
                           && x.CoursesTimeID == adjust.CoursesTime)
                         select new EducationSchedulingConflictView
                         {
                             EducationSchedulingWeekNumID = adjust.EducationSchedulingWeekNumID,
                             EducationSchedulingAdjustmentID = adjust.EducationSchedulingAdjustmentID,
                             SchoolyearID = adjust.SchoolyearID,
                             SchoolyearCode = adjust.SchoolyearCode,
                             EducationSchedulingClassID = adjust.EducationSchedulingClassID,
                             EducationMissionClassName = adjust.EducationMissionClassName,
                             CoursematerialID = adjust.CoursematerialID,
                             CourseName = adjust.CourseName,
                             WeekNum = adjust.WeekNum,
                             Weekday = adjust.Weekday,
                             CoursesTime = adjust.CoursesTime,
                             StartTimes = adjust.StartTimes,
                             EndTimes = adjust.EndTimes,
                             ClassroomID = adjust.ClassroomID,
                             ClassroomName = adjust.ClassroomName,
                             StudentNum = adjust.StudentNum,
                             UserID = adjust.UserID,
                             UserName = adjust.UserName,
                             Reason = "学生的班级不在可排时段内。"
                         });

            return query;
        }

        public IQueryable<EducationSchedulingConflictView> GetStudentConflictQueryable(IQueryable<EducationSchedulingConflictView> adjustSql)
        {
            var query = (
                        from adjust in adjustSql
                        from schedulingClass in EducationSchedulingClassRepository.Entities.Where(x => x.EducationSchedulingClassID == adjust.EducationSchedulingClassID)
                        from student in schedulingClass.CF_Student
                        from scheduling in EducationSchedulingRepository.Entities.Where(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE
                            && x.Weekday == adjust.Weekday && x.CoursesTimeID == adjust.CoursesTime && x.SchoolyearID == adjust.SchoolyearID)
                        from weekNum in EducationSchedulingWeekNumRepository.Entities.Where(x => x.EducationSchedulingID == scheduling.EducationSchedulingID
                            && adjust.WeekNum == x.WeekNum && x.EducationSchedulingWeekNumID != adjust.EducationSchedulingWeekNumID)
                        from otherClass in EducationSchedulingClassRepository.Entities.Where(x => x.EducationSchedulingClassID == scheduling.EducationSchedulingClassID)
                        from otherStudent in otherClass.CF_Student.AsQueryable().Where(x => x.UserID == student.UserID)
                        group adjust by adjust.EducationSchedulingWeekNumID into g
                        select g.FirstOrDefault() into result
                        select new EducationSchedulingConflictView
                        {
                            EducationSchedulingWeekNumID = result.EducationSchedulingWeekNumID,
                            EducationSchedulingAdjustmentID = result.EducationSchedulingAdjustmentID,
                            SchoolyearID = result.SchoolyearID,
                            SchoolyearCode = result.SchoolyearCode,
                            EducationSchedulingClassID = result.EducationSchedulingClassID,
                            EducationMissionClassName = result.EducationMissionClassName,
                            CoursematerialID = result.CoursematerialID,
                            CourseName = result.CourseName,
                            WeekNum = result.WeekNum,
                            Weekday = result.Weekday,
                            CoursesTime = result.CoursesTime,
                            StartTimes = result.StartTimes,
                            EndTimes = result.EndTimes,
                            ClassroomID = result.ClassroomID,
                            ClassroomName = result.ClassroomName,
                            StudentNum = result.StudentNum,
                            UserID = result.UserID,
                            UserName = result.UserName,
                            Reason = "学生在该时段有课。"
                        });

            return query;
        }

        public IQueryable<EducationSchedulingConflictView> GetStudentAdjustmentConflictQueryable(IQueryable<EducationSchedulingConflictView> adjustSql,
            IList<int?> adjustmentSubmitedStatusList)
        {
            var query = (
                        from adjust in adjustSql
                        from schedulingClass in EducationSchedulingClassRepository.Entities.Where(x => x.EducationSchedulingClassID == adjust.EducationSchedulingClassID)
                        from student in schedulingClass.CF_Student
                        from other in EducationSchedulingAdjustmentRepository.Entities.Where(x =>
                            adjustmentSubmitedStatusList.Contains(x.RecordStatus) && x.ToUserID == adjust.UserID
                            && x.ToWeekday == adjust.Weekday && x.ToCoursesTimeID == adjust.CoursesTime && x.ToWeekNum == adjust.WeekNum
                            && x.EducationSchedulingAdjustmentID != adjust.EducationSchedulingAdjustmentID)
                        from otherClass in EducationSchedulingClassRepository.Entities.Where(x => x.EducationMissionClassID == other.EducationMissionClassID)
                        from otherStudent in otherClass.CF_Student.AsQueryable().Where(x => x.UserID == student.UserID)
                        group adjust by adjust.EducationSchedulingWeekNumID into g
                        select g.FirstOrDefault() into result
                        select new EducationSchedulingConflictView
                        {
                            EducationSchedulingWeekNumID = result.EducationSchedulingWeekNumID,
                            EducationSchedulingAdjustmentID = result.EducationSchedulingAdjustmentID,
                            SchoolyearID = result.SchoolyearID,
                            SchoolyearCode = result.SchoolyearCode,
                            EducationSchedulingClassID = result.EducationSchedulingClassID,
                            EducationMissionClassName = result.EducationMissionClassName,
                            CoursematerialID = result.CoursematerialID,
                            CourseName = result.CourseName,
                            WeekNum = result.WeekNum,
                            Weekday = result.Weekday,
                            CoursesTime = result.CoursesTime,
                            StartTimes = result.StartTimes,
                            EndTimes = result.EndTimes,
                            ClassroomID = result.ClassroomID,
                            ClassroomName = result.ClassroomName,
                            StudentNum = result.StudentNum,
                            UserID = result.UserID,
                            UserName = result.UserName,
                            Reason = "学生该时段已被其他人申请调课占用。"
                        }
                        );

            return query;
        }

        //private IQueryable<EducationSchedulingConflictView> GetConflictAdjustmentSchedule(IQueryable<EducationSchedulingConflictView> adjustSql,
        //    IList<int?> adjustmentSubmitedStatusList)
        //{

        //    var selfClassroomConflictSql = (from adjust in adjustSql
        //                                    group adjust by new { adjust.WeekNum, adjust.Weekday, adjust.CoursesTime, adjust.ClassroomID } into g
        //                                    where g.Count() > 1
        //                                    select g).SelectMany(x => x)
        //                                    .Select(x => new EducationSchedulingConflictView
        //                                    {
        //                                        EducationSchedulingWeekNumID = x.EducationSchedulingWeekNumID,
        //                                        EducationSchedulingAdjustmentID = x.EducationSchedulingAdjustmentID,
        //                                        SchoolyearID = x.SchoolyearID,
        //                                        SchoolyearCode = x.SchoolyearCode,
        //                                        EducationSchedulingClassID = x.EducationSchedulingClassID,
        //                                        EducationMissionClassName = x.EducationMissionClassName,
        //                                        CoursematerialID = x.CoursematerialID,
        //                                        CourseName = x.CourseName,
        //                                        WeekNum = x.WeekNum,
        //                                        Weekday = x.Weekday,
        //                                        CoursesTime = x.CoursesTime,
        //                                        StartTimes = x.StartTimes,
        //                                        EndTimes = x.EndTimes,
        //                                        ClassroomID = x.ClassroomID,
        //                                        ClassroomName = x.ClassroomName,
        //                                        StudentNum = x.StudentNum,
        //                                        UserID = x.UserID,
        //                                        UserName = x.UserName,
        //                                        Reason = "和其他提交的数据存在教室冲突。"
        //                                    });

        //    var selfTeacherConflictSql = (from adjust in adjustSql
        //                                  group adjust by new { adjust.WeekNum, adjust.Weekday, adjust.CoursesTime, adjust.UserID } into g
        //                                  where g.Count() > 1
        //                                  select g).SelectMany(x => x)
        //                                    .Select(x => new EducationSchedulingConflictView
        //                                    {
        //                                        EducationSchedulingWeekNumID = x.EducationSchedulingWeekNumID,
        //                                        EducationSchedulingAdjustmentID = x.EducationSchedulingAdjustmentID,
        //                                        SchoolyearID = x.SchoolyearID,
        //                                        SchoolyearCode = x.SchoolyearCode,
        //                                        EducationSchedulingClassID = x.EducationSchedulingClassID,
        //                                        EducationMissionClassName = x.EducationMissionClassName,
        //                                        CoursematerialID = x.CoursematerialID,
        //                                        CourseName = x.CourseName,
        //                                        WeekNum = x.WeekNum,
        //                                        Weekday = x.Weekday,
        //                                        CoursesTime = x.CoursesTime,
        //                                        StartTimes = x.StartTimes,
        //                                        EndTimes = x.EndTimes,
        //                                        ClassroomID = x.ClassroomID,
        //                                        ClassroomName = x.ClassroomName,
        //                                        StudentNum = x.StudentNum,
        //                                        UserID = x.UserID,
        //                                        UserName = x.UserName,
        //                                        Reason = "和其他提交的数据存在教师冲突。"
        //                                    });

        //    var selfStudentConflictSql = (from adjust in adjustSql
        //                                  from schedulingClass in EducationSchedulingClassRepository.Entities
        //                                    .Where(x => x.EducationSchedulingClassID == adjust.EducationSchedulingClassID)
        //                                  from student in schedulingClass.CF_Student
        //                                  group adjust by new { adjust.WeekNum, adjust.Weekday, adjust.CoursesTime, student.UserID } into g
        //                                  where g.Count() > 1
        //                                  select g).SelectMany(x => x)
        //                                    .GroupBy(x => new { x.EducationSchedulingWeekNumID, x.EducationSchedulingAdjustmentID })
        //                                    .Select(x => x.FirstOrDefault())
        //                                    .Select(x => new EducationSchedulingConflictView
        //                                    {
        //                                        EducationSchedulingWeekNumID = x.EducationSchedulingWeekNumID,
        //                                        EducationSchedulingAdjustmentID = x.EducationSchedulingAdjustmentID,
        //                                        SchoolyearID = x.SchoolyearID,
        //                                        SchoolyearCode = x.SchoolyearCode,
        //                                        EducationSchedulingClassID = x.EducationSchedulingClassID,
        //                                        EducationMissionClassName = x.EducationMissionClassName,
        //                                        CoursematerialID = x.CoursematerialID,
        //                                        CourseName = x.CourseName,
        //                                        WeekNum = x.WeekNum,
        //                                        Weekday = x.Weekday,
        //                                        CoursesTime = x.CoursesTime,
        //                                        StartTimes = x.StartTimes,
        //                                        EndTimes = x.EndTimes,
        //                                        ClassroomID = x.ClassroomID,
        //                                        ClassroomName = x.ClassroomName,
        //                                        StudentNum = x.StudentNum,
        //                                        UserID = x.UserID,
        //                                        UserName = x.UserName,
        //                                        Reason = "和其他提交的数据存在教师冲突。"
        //                                    });

        //    var classroomConflictSql = (from adjust in adjustSql 
        //                                from classroom in ClassroomRepository.Entities.Where(x => x.ClassroomID == adjust.ClassroomID)
        //                                where (classroom.Totalseating ?? 0) != 0 && classroom.Totalseating < adjust.StudentNum
        //                                select new EducationSchedulingConflictView
        //                                {
        //                                    EducationSchedulingWeekNumID = adjust.EducationSchedulingWeekNumID,
        //                                    EducationSchedulingAdjustmentID = adjust.EducationSchedulingAdjustmentID,
        //                                    SchoolyearID = adjust.SchoolyearID,
        //                                    SchoolyearCode = adjust.SchoolyearCode,
        //                                    EducationSchedulingClassID = adjust.EducationSchedulingClassID,
        //                                    EducationMissionClassName = adjust.EducationMissionClassName,
        //                                    CoursematerialID = adjust.CoursematerialID,
        //                                    CourseName = adjust.CourseName,
        //                                    WeekNum = adjust.WeekNum,
        //                                    Weekday = adjust.Weekday,
        //                                    CoursesTime = adjust.CoursesTime,
        //                                    StartTimes = adjust.StartTimes,
        //                                    EndTimes = adjust.EndTimes,
        //                                    ClassroomID = adjust.ClassroomID,
        //                                    ClassroomName = adjust.ClassroomName,
        //                                    StudentNum = adjust.StudentNum,
        //                                    UserID = adjust.UserID,
        //                                    UserName = adjust.UserName,
        //                                    Reason = "教室坐席数量不足。"
        //                                }
        //                                ).Concat(
        //                                from adjust in adjustSql 
        //                                from reserve in ClassroomReserveRepository.Entities.Where(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE
        //                                    && x.IsReserved == true && x.ClassroomID == adjust.ClassroomID)
        //                                select new EducationSchedulingConflictView
        //                                {
        //                                    EducationSchedulingWeekNumID = adjust.EducationSchedulingWeekNumID,
        //                                    EducationSchedulingAdjustmentID = adjust.EducationSchedulingAdjustmentID,
        //                                    SchoolyearID = adjust.SchoolyearID,
        //                                    SchoolyearCode = adjust.SchoolyearCode,
        //                                    EducationSchedulingClassID = adjust.EducationSchedulingClassID,
        //                                    EducationMissionClassName = adjust.EducationMissionClassName,
        //                                    CoursematerialID = adjust.CoursematerialID,
        //                                    CourseName = adjust.CourseName,
        //                                    WeekNum = adjust.WeekNum,
        //                                    Weekday = adjust.Weekday,
        //                                    CoursesTime = adjust.CoursesTime,
        //                                    StartTimes = adjust.StartTimes,
        //                                    EndTimes = adjust.EndTimes,
        //                                    ClassroomID = adjust.ClassroomID,
        //                                    ClassroomName = adjust.ClassroomName,
        //                                    StudentNum = adjust.StudentNum,
        //                                    UserID = adjust.UserID,
        //                                    UserName = adjust.UserName,
        //                                    Reason = "教室已被预留。"
        //                                }
        //                                ).Concat(
        //                                from adjust in adjustSql 
        //                                from scheduleSet in ClassroomScheduleSettingRepository.Entities.Where(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE 
        //                                    && x.IsOnWork == false && x.ClassroomID == adjust.ClassroomID && x.Weekday == adjust.Weekday 
        //                                    && x.CoursesTimeID == x.CoursesTimeID)
        //                                select new EducationSchedulingConflictView
        //                                {
        //                                    EducationSchedulingWeekNumID = adjust.EducationSchedulingWeekNumID,
        //                                    EducationSchedulingAdjustmentID = adjust.EducationSchedulingAdjustmentID,
        //                                    SchoolyearID = adjust.SchoolyearID,
        //                                    SchoolyearCode = adjust.SchoolyearCode,
        //                                    EducationSchedulingClassID = adjust.EducationSchedulingClassID,
        //                                    EducationMissionClassName = adjust.EducationMissionClassName,
        //                                    CoursematerialID = adjust.CoursematerialID,
        //                                    CourseName = adjust.CourseName,
        //                                    WeekNum = adjust.WeekNum,
        //                                    Weekday = adjust.Weekday,
        //                                    CoursesTime = adjust.CoursesTime,
        //                                    StartTimes = adjust.StartTimes,
        //                                    EndTimes = adjust.EndTimes,
        //                                    ClassroomID = adjust.ClassroomID,
        //                                    ClassroomName = adjust.ClassroomName,
        //                                    StudentNum = adjust.StudentNum,
        //                                    UserID = adjust.UserID,
        //                                    UserName = adjust.UserName,
        //                                    Reason = "教室不在可排时段内。"
        //                                }
        //                                ).Concat(
        //                                from adjust in adjustSql
        //                                from classroom in ClassroomRepository.Entities.Where(x => x.ClassroomID == adjust.ClassroomID)
        //                                from scheduling in EducationSchedulingRepository.Entities.Where(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE
        //                                    && x.ClassroomID == adjust.ClassroomID && x.Weekday == adjust.Weekday && x.CoursesTimeID == adjust.CoursesTime 
        //                                    && x.SchoolyearID == adjust.SchoolyearID) 
        //                                from weekNum in EducationSchedulingWeekNumRepository.Entities.Where(x => x.EducationSchedulingID == scheduling.EducationSchedulingID
        //                                    && adjust.WeekNum == x.WeekNum && x.EducationSchedulingWeekNumID != adjust.EducationSchedulingWeekNumID)
        //                                where classroom.IsConcurrentUse != true
        //                                select new EducationSchedulingConflictView
        //                                {
        //                                    EducationSchedulingWeekNumID = adjust.EducationSchedulingWeekNumID,
        //                                    EducationSchedulingAdjustmentID = adjust.EducationSchedulingAdjustmentID,
        //                                    SchoolyearID = adjust.SchoolyearID,
        //                                    SchoolyearCode = adjust.SchoolyearCode,
        //                                    EducationSchedulingClassID = adjust.EducationSchedulingClassID,
        //                                    EducationMissionClassName = adjust.EducationMissionClassName,
        //                                    CoursematerialID = adjust.CoursematerialID,
        //                                    CourseName = adjust.CourseName,
        //                                    WeekNum = adjust.WeekNum,
        //                                    Weekday = adjust.Weekday,
        //                                    CoursesTime = adjust.CoursesTime,
        //                                    StartTimes = adjust.StartTimes,
        //                                    EndTimes = adjust.EndTimes,
        //                                    ClassroomID = adjust.ClassroomID,
        //                                    ClassroomName = adjust.ClassroomName,
        //                                    StudentNum = adjust.StudentNum,
        //                                    UserID = adjust.UserID,
        //                                    UserName = adjust.UserName,
        //                                    Reason = "教室已被其他课程使用。"
        //                                }
        //                                ).Concat(
        //                                from adjust in adjustSql
        //                                from classroom in ClassroomRepository.Entities.Where(x => x.ClassroomID == adjust.ClassroomID)
        //                                from excessiveUse in ClassroomExcessiveUseRepository.Entities.Where(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE
        //                                    && x.ClassroomID == classroom.ClassroomID && x.SchoolyearID == adjust.SchoolyearID) 
        //                                from scheduling in ClassroomExcessiveUseSchedulingRepository.Entities.Where(x => x.ClassroomExcessiveUseID == excessiveUse.ClassroomExcessiveUseID
        //                                    && x.Weekday == adjust.Weekday && x.CoursesTimeID == adjust.CoursesTime) 
        //                                from weekNum in ClassroomExcessiveUseSchedulingWeekNumRepository.Entities.Where(x => x.ClassroomExcessiveUseSchedulingID == scheduling.ClassroomExcessiveUseSchedulingID
        //                                    && x.WeekNum == adjust.WeekNum)
        //                                where classroom.IsConcurrentUse != true
        //                                select new EducationSchedulingConflictView
        //                                {
        //                                    EducationSchedulingWeekNumID = adjust.EducationSchedulingWeekNumID,
        //                                    EducationSchedulingAdjustmentID = adjust.EducationSchedulingAdjustmentID,
        //                                    SchoolyearID = adjust.SchoolyearID,
        //                                    SchoolyearCode = adjust.SchoolyearCode,
        //                                    EducationSchedulingClassID = adjust.EducationSchedulingClassID,
        //                                    EducationMissionClassName = adjust.EducationMissionClassName,
        //                                    CoursematerialID = adjust.CoursematerialID,
        //                                    CourseName = adjust.CourseName,
        //                                    WeekNum = adjust.WeekNum,
        //                                    Weekday = adjust.Weekday,
        //                                    CoursesTime = adjust.CoursesTime,
        //                                    StartTimes = adjust.StartTimes,
        //                                    EndTimes = adjust.EndTimes,
        //                                    ClassroomID = adjust.ClassroomID,
        //                                    ClassroomName = adjust.ClassroomName,
        //                                    StudentNum = adjust.StudentNum,
        //                                    UserID = adjust.UserID,
        //                                    UserName = adjust.UserName,
        //                                    Reason = "教室已被占用。"
        //                                }
        //                                ).Concat(
        //                                from adjust in adjustSql
        //                                from classroom in ClassroomRepository.Entities.Where(x => x.ClassroomID == adjust.ClassroomID)
        //                                from other in EducationSchedulingAdjustmentRepository.Entities.Where(x => 
        //                                    adjustmentSubmitedStatusList.Contains(x.RecordStatus) && x.ToClassroomID == adjust.ClassroomID 
        //                                    && x.ToWeekday == adjust.Weekday && x.ToCoursesTimeID == adjust.CoursesTime && x.ToWeekNum == adjust.WeekNum 
        //                                    && x.EducationSchedulingAdjustmentID != adjust.EducationSchedulingAdjustmentID)
        //                                where classroom.IsConcurrentUse != true
        //                                select new EducationSchedulingConflictView
        //                                {
        //                                    EducationSchedulingWeekNumID = adjust.EducationSchedulingWeekNumID,
        //                                    EducationSchedulingAdjustmentID = adjust.EducationSchedulingAdjustmentID,
        //                                    SchoolyearID = adjust.SchoolyearID,
        //                                    SchoolyearCode = adjust.SchoolyearCode,
        //                                    EducationSchedulingClassID = adjust.EducationSchedulingClassID,
        //                                    EducationMissionClassName = adjust.EducationMissionClassName,
        //                                    CoursematerialID = adjust.CoursematerialID,
        //                                    CourseName = adjust.CourseName,
        //                                    WeekNum = adjust.WeekNum,
        //                                    Weekday = adjust.Weekday,
        //                                    CoursesTime = adjust.CoursesTime,
        //                                    StartTimes = adjust.StartTimes,
        //                                    EndTimes = adjust.EndTimes,
        //                                    ClassroomID = adjust.ClassroomID,
        //                                    ClassroomName = adjust.ClassroomName,
        //                                    StudentNum = adjust.StudentNum,
        //                                    UserID = adjust.UserID,
        //                                    UserName = adjust.UserName,
        //                                    Reason = "教室已被其他人申请调课占用。"
        //                                }
        //                                );
        //    var courseConflictSql = (from adjust in adjustSql
        //                            from scheduleSet in CourseScheduleSettingRepository.Entities.Where(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE 
        //                                && x.IsOnWork == false && x.CoursematerialID == adjust.CoursematerialID && x.Weekday == adjust.Weekday 
        //                                && x.CoursesTimeID == x.CoursesTimeID)
        //                             select new EducationSchedulingConflictView
        //                             {
        //                                 EducationSchedulingWeekNumID = adjust.EducationSchedulingWeekNumID,
        //                                 EducationSchedulingAdjustmentID = adjust.EducationSchedulingAdjustmentID,
        //                                 SchoolyearID = adjust.SchoolyearID,
        //                                 SchoolyearCode = adjust.SchoolyearCode,
        //                                 EducationSchedulingClassID = adjust.EducationSchedulingClassID,
        //                                 EducationMissionClassName = adjust.EducationMissionClassName,
        //                                 CoursematerialID = adjust.CoursematerialID,
        //                                 CourseName = adjust.CourseName,
        //                                 WeekNum = adjust.WeekNum,
        //                                 Weekday = adjust.Weekday,
        //                                 CoursesTime = adjust.CoursesTime,
        //                                 StartTimes = adjust.StartTimes,
        //                                 EndTimes = adjust.EndTimes,
        //                                 ClassroomID = adjust.ClassroomID,
        //                                 ClassroomName = adjust.ClassroomName,
        //                                 StudentNum = adjust.StudentNum,
        //                                 UserID = adjust.UserID,
        //                                 UserName = adjust.UserName,
        //                                 Reason = "课程不在可排时段内。"
        //                            });
        //    var teacherConflictSql = (from adjust in adjustSql
        //                              from scheduleSet in TeacherScheduleSettingRepository.Entities.Where(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE 
        //                                && x.IsOnWork == false && x.UserID == adjust.UserID && x.Weekday == adjust.Weekday 
        //                                && x.CoursesTimeID == x.CoursesTimeID)
        //                              select new EducationSchedulingConflictView
        //                              {
        //                                  EducationSchedulingWeekNumID = adjust.EducationSchedulingWeekNumID,
        //                                  EducationSchedulingAdjustmentID = adjust.EducationSchedulingAdjustmentID,
        //                                  SchoolyearID = adjust.SchoolyearID,
        //                                  SchoolyearCode = adjust.SchoolyearCode,
        //                                  EducationSchedulingClassID = adjust.EducationSchedulingClassID,
        //                                  EducationMissionClassName = adjust.EducationMissionClassName,
        //                                  CoursematerialID = adjust.CoursematerialID,
        //                                  CourseName = adjust.CourseName,
        //                                  WeekNum = adjust.WeekNum,
        //                                  Weekday = adjust.Weekday,
        //                                  CoursesTime = adjust.CoursesTime,
        //                                  StartTimes = adjust.StartTimes,
        //                                  EndTimes = adjust.EndTimes,
        //                                  ClassroomID = adjust.ClassroomID,
        //                                  ClassroomName = adjust.ClassroomName,
        //                                  StudentNum = adjust.StudentNum,
        //                                  UserID = adjust.UserID,
        //                                  UserName = adjust.UserName,
        //                                  Reason = "教师不在可排时段内。"
        //                              }).Concat(
        //                              from adjust in adjustSql
        //                              from scheduling in EducationSchedulingRepository.Entities.Where(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE
        //                                  && x.ES_EducationSchedulingTeacher.Any(w => w.UserID == adjust.UserID) && x.Weekday == adjust.Weekday 
        //                                  && x.CoursesTimeID == adjust.CoursesTime && x.SchoolyearID == adjust.SchoolyearID)
        //                              from weekNum in EducationSchedulingWeekNumRepository.Entities.Where(x => x.EducationSchedulingID == scheduling.EducationSchedulingID
        //                                  && adjust.WeekNum == x.WeekNum && x.EducationSchedulingWeekNumID != adjust.EducationSchedulingWeekNumID)
        //                              select new EducationSchedulingConflictView
        //                              {
        //                                  EducationSchedulingWeekNumID = adjust.EducationSchedulingWeekNumID,
        //                                  EducationSchedulingAdjustmentID = adjust.EducationSchedulingAdjustmentID,
        //                                  SchoolyearID = adjust.SchoolyearID,
        //                                  SchoolyearCode = adjust.SchoolyearCode,
        //                                  EducationSchedulingClassID = adjust.EducationSchedulingClassID,
        //                                  EducationMissionClassName = adjust.EducationMissionClassName,
        //                                  CoursematerialID = adjust.CoursematerialID,
        //                                  CourseName = adjust.CourseName,
        //                                  WeekNum = adjust.WeekNum,
        //                                  Weekday = adjust.Weekday,
        //                                  CoursesTime = adjust.CoursesTime,
        //                                  StartTimes = adjust.StartTimes,
        //                                  EndTimes = adjust.EndTimes,
        //                                  ClassroomID = adjust.ClassroomID,
        //                                  ClassroomName = adjust.ClassroomName,
        //                                  StudentNum = adjust.StudentNum,
        //                                  UserID = adjust.UserID,
        //                                  UserName = adjust.UserName,
        //                                  Reason = "教师在该时段有课。"
        //                              }).Concat(
        //                                from adjust in adjustSql
        //                                from other in EducationSchedulingAdjustmentRepository.Entities.Where(x =>
        //                                    adjustmentSubmitedStatusList.Contains(x.RecordStatus) && x.ToUserID == adjust.UserID
        //                                    && x.ToWeekday == adjust.Weekday && x.ToCoursesTimeID == adjust.CoursesTime && x.ToWeekNum == adjust.WeekNum 
        //                                    && x.EducationSchedulingAdjustmentID != adjust.EducationSchedulingAdjustmentID)
        //                                select new EducationSchedulingConflictView
        //                                {
        //                                    EducationSchedulingWeekNumID = adjust.EducationSchedulingWeekNumID,
        //                                    EducationSchedulingAdjustmentID = adjust.EducationSchedulingAdjustmentID,
        //                                    SchoolyearID = adjust.SchoolyearID,
        //                                    SchoolyearCode = adjust.SchoolyearCode,
        //                                    EducationSchedulingClassID = adjust.EducationSchedulingClassID,
        //                                    EducationMissionClassName = adjust.EducationMissionClassName,
        //                                    CoursematerialID = adjust.CoursematerialID,
        //                                    CourseName = adjust.CourseName,
        //                                    WeekNum = adjust.WeekNum,
        //                                    Weekday = adjust.Weekday,
        //                                    CoursesTime = adjust.CoursesTime,
        //                                    StartTimes = adjust.StartTimes,
        //                                    EndTimes = adjust.EndTimes,
        //                                    ClassroomID = adjust.ClassroomID,
        //                                    ClassroomName = adjust.ClassroomName,
        //                                    StudentNum = adjust.StudentNum,
        //                                    UserID = adjust.UserID,
        //                                    UserName = adjust.UserName,
        //                                    Reason = "教师该时段已被其他人申请调课占用。"
        //                                }
        //                                );

        //    var studentConflictSql = (from adjust in adjustSql 
        //                              from schedulingClass in EducationSchedulingClassRepository.Entities.Where(x => x.EducationSchedulingClassID == adjust.EducationSchedulingClassID) 
        //                              from student in schedulingClass.CF_Student 
        //                              from scheduleSet in ClassmajorScheduleSettingRepository.Entities.Where(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE 
        //                                && x.IsOnWork == false && x.ClassmajorID == student.ClassmajorID && x.Weekday == adjust.Weekday 
        //                                && x.CoursesTimeID == x.CoursesTimeID)
        //                              select new EducationSchedulingConflictView
        //                              {
        //                                  EducationSchedulingWeekNumID = adjust.EducationSchedulingWeekNumID,
        //                                  EducationSchedulingAdjustmentID = adjust.EducationSchedulingAdjustmentID,
        //                                  SchoolyearID = adjust.SchoolyearID,
        //                                  SchoolyearCode = adjust.SchoolyearCode,
        //                                  EducationSchedulingClassID = adjust.EducationSchedulingClassID,
        //                                  EducationMissionClassName = adjust.EducationMissionClassName,
        //                                  CoursematerialID = adjust.CoursematerialID,
        //                                  CourseName = adjust.CourseName,
        //                                  WeekNum = adjust.WeekNum,
        //                                  Weekday = adjust.Weekday,
        //                                  CoursesTime = adjust.CoursesTime,
        //                                  StartTimes = adjust.StartTimes,
        //                                  EndTimes = adjust.EndTimes,
        //                                  ClassroomID = adjust.ClassroomID,
        //                                  ClassroomName = adjust.ClassroomName,
        //                                  StudentNum = adjust.StudentNum,
        //                                  UserID = adjust.UserID,
        //                                  UserName = adjust.UserName,
        //                                  Reason = "学生的班级不在可排时段内。"
        //                              }).Concat(
        //                              from adjust in adjustSql
        //                              from schedulingClass in EducationSchedulingClassRepository.Entities.Where(x => x.EducationSchedulingClassID == adjust.EducationSchedulingClassID)
        //                              from student in schedulingClass.CF_Student 
        //                              from scheduling in EducationSchedulingRepository.Entities.Where(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE
        //                                  && x.Weekday == adjust.Weekday && x.CoursesTimeID == adjust.CoursesTime && x.SchoolyearID == adjust.SchoolyearID)
        //                              from weekNum in EducationSchedulingWeekNumRepository.Entities.Where(x => x.EducationSchedulingID == scheduling.EducationSchedulingID
        //                                  && adjust.WeekNum == x.WeekNum && x.EducationSchedulingWeekNumID != adjust.EducationSchedulingWeekNumID) 
        //                              from otherClass in EducationSchedulingClassRepository.Entities.Where(x => x.EducationSchedulingClassID == scheduling.EducationSchedulingClassID) 
        //                              from otherStudent in otherClass.CF_Student.AsQueryable().Where(x => x.UserID == student.UserID)
        //                              group adjust by adjust.EducationSchedulingWeekNumID into g 
        //                              select g.FirstOrDefault() into result
        //                              select new EducationSchedulingConflictView
        //                              {
        //                                  EducationSchedulingWeekNumID = result.EducationSchedulingWeekNumID,
        //                                  EducationSchedulingAdjustmentID = result.EducationSchedulingAdjustmentID,
        //                                  SchoolyearID = result.SchoolyearID,
        //                                  SchoolyearCode = result.SchoolyearCode,
        //                                  EducationSchedulingClassID = result.EducationSchedulingClassID,
        //                                  EducationMissionClassName = result.EducationMissionClassName,
        //                                  CoursematerialID = result.CoursematerialID,
        //                                  CourseName = result.CourseName,
        //                                  WeekNum = result.WeekNum,
        //                                  Weekday = result.Weekday,
        //                                  CoursesTime = result.CoursesTime,
        //                                  StartTimes = result.StartTimes,
        //                                  EndTimes = result.EndTimes,
        //                                  ClassroomID = result.ClassroomID,
        //                                  ClassroomName = result.ClassroomName,
        //                                  StudentNum = result.StudentNum,
        //                                  UserID = result.UserID,
        //                                  UserName = result.UserName,
        //                                  Reason = "学生在该时段有课。"
        //                              }).Concat(
        //                                from adjust in adjustSql
        //                                from schedulingClass in EducationSchedulingClassRepository.Entities.Where(x => x.EducationSchedulingClassID == adjust.EducationSchedulingClassID)
        //                                from student in schedulingClass.CF_Student 
        //                                from other in EducationSchedulingAdjustmentRepository.Entities.Where(x =>
        //                                    adjustmentSubmitedStatusList.Contains(x.RecordStatus) && x.ToUserID == adjust.UserID
        //                                    && x.ToWeekday == adjust.Weekday && x.ToCoursesTimeID == adjust.CoursesTime && x.ToWeekNum == adjust.WeekNum 
        //                                    && x.EducationSchedulingAdjustmentID != adjust.EducationSchedulingAdjustmentID) 
        //                                from otherClass in EducationSchedulingClassRepository.Entities.Where(x => x.EducationMissionClassID == other.EducationMissionClassID)
        //                                from otherStudent in otherClass.CF_Student.AsQueryable().Where(x => x.UserID == student.UserID)
        //                                group adjust by adjust.EducationSchedulingWeekNumID into g
        //                                select g.FirstOrDefault() into result
        //                                select new EducationSchedulingConflictView
        //                                {
        //                                    EducationSchedulingWeekNumID = result.EducationSchedulingWeekNumID,
        //                                    EducationSchedulingAdjustmentID = result.EducationSchedulingAdjustmentID,
        //                                    SchoolyearID = result.SchoolyearID,
        //                                    SchoolyearCode = result.SchoolyearCode,
        //                                    EducationSchedulingClassID = result.EducationSchedulingClassID,
        //                                    EducationMissionClassName = result.EducationMissionClassName,
        //                                    CoursematerialID = result.CoursematerialID,
        //                                    CourseName = result.CourseName,
        //                                    WeekNum = result.WeekNum,
        //                                    Weekday = result.Weekday,
        //                                    CoursesTime = result.CoursesTime,
        //                                    StartTimes = result.StartTimes,
        //                                    EndTimes = result.EndTimes,
        //                                    ClassroomID = result.ClassroomID,
        //                                    ClassroomName = result.ClassroomName,
        //                                    StudentNum = result.StudentNum,
        //                                    UserID = result.UserID,
        //                                    UserName = result.UserName,
        //                                    Reason = "学生该时段已被其他人申请调课占用。"
        //                                }
        //                                );

        //    return selfClassroomConflictSql.Concat(selfTeacherConflictSql).Concat(classroomConflictSql).Concat(courseConflictSql).Concat(teacherConflictSql).Concat(studentConflictSql);
        //}

        public IQueryable<EducationSchedulingConflictView> GetArrangementsQueryable(IQueryable<EducationSchedulingConflictView> adjustSql)
        {
            var query = from adjust in adjustSql
                        from a in ArrangementsRepository.Entities.Where(x => x.CoursesTimeID == adjust.CoursesTime && x.Weekday == adjust.Weekday && !x.IsOnWork.Value)
                        select new EducationSchedulingConflictView
                        {
                            EducationSchedulingWeekNumID = adjust.EducationSchedulingWeekNumID,
                            EducationSchedulingAdjustmentID = adjust.EducationSchedulingAdjustmentID,
                            SchoolyearID = adjust.SchoolyearID,
                            SchoolyearCode = adjust.SchoolyearCode,
                            EducationSchedulingClassID = adjust.EducationSchedulingClassID,
                            EducationMissionClassName = adjust.EducationMissionClassName,
                            CoursematerialID = adjust.CoursematerialID,
                            CourseName = adjust.CourseName,
                            WeekNum = adjust.WeekNum,
                            Weekday = adjust.Weekday,
                            CoursesTime = adjust.CoursesTime,
                            StartTimes = adjust.StartTimes,
                            EndTimes = adjust.EndTimes,
                            ClassroomID = adjust.ClassroomID,
                            ClassroomName = adjust.ClassroomName,
                            StudentNum = adjust.StudentNum,
                            UserID = adjust.UserID,
                            UserName = adjust.UserName,
                            Reason = "周工作时间节次为不可用。"
                        };

            return query;
        }
    }
}