JW/EMiS.DataLogic/SelectCourse/ExecutableFreeSelectionCouseDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EMIS.DataLogic.Repositories;
using EMIS.ViewModel.SelectCourse;
using System.Linq.Expressions;
using EMIS.Entities;
using EMIS.ViewModel;
using EMIS.ViewModel.EnrollManage.SpecialtyManage;
using EMIS.ViewModel.EducationManagement;

namespace EMIS.DataLogic.SelectCourse
{
    public class ExecutableFreeSelectionCouseDAL
    {
        public ExecutableFreeSelectionCouseRepository ExecutableFreeSelectionCouseRepository { get; set; }
        public ExecutableFreeSelectionCouseSchedulingRepository ExecutableFreeSelectionCouseSchedulingRepository { get; set; }
        public ExecutableFreeSelectionCouseTeachingSettingRepository ExecutableFreeSelectionCouseTeachingSettingRepository { get; set; }
        public ExecutableFreeSelectionCouseTeacherRepository ExecutableFreeSelectionCouseTeacherRepository { get; set; }
        public FreeSelectionCouseApplyRepository FreeSelectionCouseApplyRepository { get; set; }
        public FreeSelectionCouseApplySchedulingRepository FreeSelectionCouseApplySchedulingRepository { get; set; }
        public FreeSelectionCouseApplyTeachingSettingRepository FreeSelectionCouseApplyTeachingSettingRepository { get; set; }
        public FreeSelectionCouseApplyTeacherRepository FreeSelectionCouseApplyTeacherRepository { get; set; }
        public ExecutableFreeSelectionCouseRepository FreeSelectionCousePlanRepository { get; set; }
        public SpecialtyRepository SpecialtyRepository { get; set; }
        public Lazy<FreeSelectionCourseDAL> FreeSelectionCourseDAL { get; set; }
        public DepartmentRepository DepartmentRepository { get; set; }
        public DictionaryItemRepository DictionaryItemRepository { get; set; }
        public CoursesTimeRepository coursesTimeRepository { get; set; }

        public EducationSchedulingRepository EducationSchedulingRepository { get; set; }
        public EducationSchedulingWeekNumRepository EducationSchedulingWeekNumRepository { get; set; }
        public EducationSchedulingTeacherRepository EducationSchedulingTeacherRepository { get; set; }
        public EducationMissionRepository EducationMissionRepository { get; set; }
        public EducationMissionClassRepository EducationMissionClassRepository { get; set; }
        public EducationSchedulingClassRepository EducationSchedulingClassRepository { get; set; }
        public ClassroomRepository ClassroomRepository { get; set; }

        /// <summary>
        /// 查询所有计划申请表记录
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<ExecutableFreeSelectionCouseView> GetExecutableFreeSelectionCouseViewQueryable(Expression<Func<EM_ExecutableFreeSelectionCouse, bool>> exp)
        {
            var query = from a in ExecutableFreeSelectionCouseRepository.GetList(exp)
                        join b in ExecutableFreeSelectionCouseTeachingSettingRepository.Entities on a.ExecutableFreeSelectionCouseID equals b.ExecutableFreeSelectionCouseID
                        join c in FreeSelectionCourseDAL.Value.GetFreeSelectionCourseViewQueryable(x => true) on a.FreeSelectionCouseID equals c.FreeSelectionCouseID
                        //join se in
                        //    (from a in ExecutableFreeSelectionCouseSchedulingRepository.Entities
                        //     group a by new { a.ExecutableFreeSelectionCouseID, a.ClassroomID } into g
                        //     select new { ExecutableFreeSelectionCouseID = g.Key.ExecutableFreeSelectionCouseID, ClassroomID = g.Key.ClassroomID }
                        //         ) on a.ExecutableFreeSelectionCouseID equals se.ExecutableFreeSelectionCouseID
                        //join se in ExecutableFreeSelectionCouseSchedulingRepository.Entities on a.ExecutableFreeSelectionCouseID equals se.ExecutableFreeSelectionCouseID
                        join d in DepartmentRepository.Entities on a.DepartmentID equals d.DepartmentID into dd
                        from ed in dd.DefaultIfEmpty()
                        //join di in DictionaryItemRepository.GetList(x => x.DictionaryCode == "ES_WeekDay")
                        //on se.Weekday equals di.Value into gs
                        //from di in gs.DefaultIfEmpty()
                        join Scheduling in
                            (from sc in ExecutableFreeSelectionCouseSchedulingRepository.Entities
                             //join b in coursesTimeRepository.Entities on sc.CoursesTimeID equals b.CoursesTimeID
                             //join c in DictionaryItemRepository.Entities on new { b.TimesSegment, DictionaryCode = "CF_TimesSegment" }
                             //equals new { TimesSegment = c.Value.Value, c.DictionaryCode } into gbsta
                             //from gicbsta in gbsta.DefaultIfEmpty()
                             join d in ClassroomRepository.Entities on sc.ClassroomID equals d.ClassroomID
                             group d by new { sc.ExecutableFreeSelectionCouseID } into g
                             select new
                             {
                                 ExecutableFreeSelectionCouseID = g.Key.ExecutableFreeSelectionCouseID,
                                 ClassroomName = g.Select(x => x.Name).FirstOrDefault(),
                             }) on a.ExecutableFreeSelectionCouseID equals Scheduling.ExecutableFreeSelectionCouseID
                             into lj
                        from Scheduling in lj.DefaultIfEmpty()
                        select new ExecutableFreeSelectionCouseView
                        {
                            ExecutableFreeSelectionCouseID = a.ExecutableFreeSelectionCouseID,
                            FreeSelectionCouseID = a.FreeSelectionCouseID,
                            //FreeSelectionCouseNo = a.EM_FreeSelectionCouse.No,
                            DefaultClassName = a.DefaultClassName,
                            SchoolyearID = a.SchoolyearID,
                            SchoolyearCode = a.CF_Schoolyear.Code,
                            SchoolyearName = a.CF_Schoolyear.Code,
                            CourseCode = c.CourseCode,
                            CourseName = c.CourseName,
                            CourseStructureID = a.CourseStructureID,
                            //CourseStructureName = c.CourseStructureName,
                            CourseCategoryID = a.CourseCategoryID,
                            //CourseCategoryName = c.CourseCategoryName,
                            CourseTypeID = a.CourseTypeID,
                            //CourseTypeName = c.CourseTypeName,
                            CourseQualityID = a.CourseQualityID,
                            //CourseQualityName = c.CourseQualityName,
                            DepartmentID = a.DepartmentID,
                            DepartmentName = ed.Name,
                            CollegeID = ed.CollegeID,
                            CollegeName = ed.CF_College.Name,
                            ExaminationModeID = a.ExaminationModeID,
                            //ExaminationModeName = a.ExaminationModeName,
                            TeachinglanguageID = a.TeachinglanguageID,
                            //TeachinglanguageName = a.TeachinglanguageName,
                            PeopleNumlimit = a.PeopleNumlimit,
                            PeopleNumlower = a.PeopleNumlower,
                            IsOpened = a.IsOpened == true,
                            HandleModeID = a.HandleModeID,
                            SchoolyearNumID = c.SchoolyearNumID,
                            SchoolyearNumName = c.SchoolyearNumName,
                            SchoolcodeID = c.SchoolcodeID,
                            SchoolcodeName = c.SchoolcodeName,
                            PracticeTypeID = a.PracticeTypeID,
                            //StarttermID = c.StarttermID,
                            //StarttermName = c.StarttermName,
                            IsEnable = c.IsEnable,
                            Credit = b.Credit.Value,
                            TheoryCourse = b.TheoryCourse,
                            Practicehours = b.Practicehours,
                            Trialhours = b.Trialhours,
                            WeeklyHours = b.WeeklyHours,
                            WeeklyNum = b.WeeklyNum,
                            TheoryWeeklyNum = b.TheoryWeeklyNum,
                            PracticeWeeklyNum = b.PracticeWeeklyNum,
                            TrialWeeklyNum = b.TrialWeeklyNum,
                            StartWeeklyNum = b.StartWeeklyNum,
                            EndWeeklyNum = b.EndWeeklyNum,
                            IsNeedMaterial = (bool)a.IsNeedMaterial,
                            IsNeedMaterialName = a.IsNeedMaterial == true ? "是" : "否",
                            //CoursesTimeID = se.CoursesTimeID,
                            //CoursesTimeName = se.EM_CoursesTime.StartHour.ToString() + ":" + se.EM_CoursesTime.StartMinutes.ToString() + "-" + se.EM_CoursesTime.EndHour.ToString() + ":" + se.EM_CoursesTime.EndMinutes.ToString(),
                            //ClassroomID = se.ClassroomID,
                            ClassroomID = a.EM_ExecutableFreeSelectionCouseScheduling.FirstOrDefault() != null ? a.EM_ExecutableFreeSelectionCouseScheduling.FirstOrDefault().ClassroomID : null,
                            //ClassroomName =a.EM_ExecutableFreeSelectionCouseScheduling.FirstOrDefault() != null ? a.EM_ExecutableFreeSelectionCouseScheduling.FirstOrDefault().CF_Classroom.Name : null ,
                            ClassroomName = Scheduling.ClassroomName,
                            //Weekday = se.Weekday,
                            //WeekdayName = di.Name,
                            CreateUserID = a.CreateUserID,
                            CreateTime = a.CreateTime,
                            ResultTypeID=a.ResultTypeID,
                            Remarks = a.Remarks,
                            RecordStatus = a.RecordStatus
                        };
            return query;
        }

        public IQueryable<ExecutableFreeSelectionCouseTeacherListView> GetExecutableFreeSelectionCouseTeacherViewQueryable(Expression<Func<EM_ExecutableFreeSelectionCouse, bool>> exp)
        {
            var q = (from fsca in ExecutableFreeSelectionCouseRepository.GetList(exp)
                     from fscas in fsca.EM_ExecutableFreeSelectionCouseTeacher
                     select new ExecutableFreeSelectionCouseTeacherListView
                     {
                         ExecutableFreeSelectionCouseID = fsca.ExecutableFreeSelectionCouseID,
                         UserID = fscas.CF_Staff.UserID,
                         LoginID = fscas.CF_Staff.Sys_User.LoginID,
                         Name = fscas.CF_Staff.Sys_User.Name,
                         CollegeID = fscas.CF_Staff.CollegeID,
                         CollegeName = fscas.CF_Staff.CF_College.Name,
                         BirthDate = fscas.CF_Staff.BirthDate,
                         TeacherType = fscas.CF_Staff.TeacherTypeID,
                         IncumbencyState = fscas.CF_Staff.IncumbencyState,
                         Title = fscas.CF_Staff.TitleID,
                         TeachingMethod = fscas.TeachingMethod
                     });

            return q;
        }

        public IQueryable<SpecialtyView> GetExecutableFreeSelectionCouseSpecialtyViewQueryable(Expression<Func<EM_ExecutableFreeSelectionCouse, bool>> exp)
        {
            var q = (from fsca in ExecutableFreeSelectionCouseRepository.GetList(exp)
                     from fssp in fsca.EM_ExecutableFreeSelectionCouseSpecialty
                     join spec in SpecialtyRepository.Entities on fssp.SpecialtyID equals spec.SpecialtyID
                     join b in DictionaryItemRepository.GetList(x => x.DictionaryCode == DictionaryItem.CF_Standard.ToString())
                     on spec.StandardID equals b.Value
                     into gs
                     from gstandard in gs.DefaultIfEmpty()
                     select new SpecialtyView
                     {
                         //ExecutableFreeSelectionCouseID = fsca.ExecutableFreeSelectionCouseID,
                         SpecialtyID = spec.SpecialtyID,
                         StandardID = fssp.CF_Specialty.StandardID,
                         StandardCode = gstandard.Code,
                         StandardName = gstandard.Name,
                         LearnSystem = spec.LearnSystem,
                         ScienceclassID = spec.ScienceclassID,
                         LearningformID = spec.LearningformID,
                         EducationID = spec.EducationID,
                         PropertyID = spec.PropertyID,
                         StandardTitle = spec.StandardTitle,
                         StandardLevel = spec.StandardLevel,
                         Remark = spec.Remark,
                     });

            return q;
        }

        public IQueryable<Sys_DictionaryItem> GetTeachingModeType(Expression<Func<EM_ExecutableFreeSelectionCouse, bool>> exp)
        {
            var query = from fsc in ExecutableFreeSelectionCouseRepository.GetList(exp)
                        from fsctm in fsc.EM_ExecutableFreeSelectionCouseTeachingMode
                        join ditm in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(CF_TeachingMode).Name)
                            on fsctm.TeachingModeID equals ditm.Value
                        into dditm
                        from editm in dditm.DefaultIfEmpty()
                        select editm;

            return query;
        }

        public IQueryable<Sys_DictionaryItem> GetGradeYear(Expression<Func<EM_ExecutableFreeSelectionCouse, bool>> exp)
        {
            var query = from fsc in ExecutableFreeSelectionCouseRepository.GetList(exp)
                        from fscagy in fsc.EM_ExecutableFreeSelectionCouseGradeYear
                        join ditm in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(CF_TeachingMode).Name)
                            on fscagy.GradeYear equals ditm.Value
                        into dditm
                        from editm in dditm.DefaultIfEmpty()
                        select editm;

            return query;
        }

        public IQueryable<ExecutableFreeSelectionCouseSchedulingView> GetSchedulingView(Expression<Func<EM_ExecutableFreeSelectionCouseScheduling, bool>> exp)
        {
            //var query = (from em in educationMissionRepository.GetList(exp)
            //             from emc in em.EM_EducationMissionClass
            //             from cp in emc.EM_CourseProcess
            var query = (from a in ExecutableFreeSelectionCouseSchedulingRepository.GetList(exp)
                         join b in coursesTimeRepository.Entities on a.CoursesTimeID equals b.CoursesTimeID
                         join c in DictionaryItemRepository.Entities on new { b.TimesSegment, DictionaryCode = "CF_TimesSegment" }
                         equals new { TimesSegment = c.Value.Value, c.DictionaryCode } into gbsta
                         from gicbsta in gbsta.DefaultIfEmpty()
                         select new ExecutableFreeSelectionCouseSchedulingView
                         {
                             SchedulingID = a.ExecutableFreeSelectionCouseSchedulingID,
                             ID = a.ExecutableFreeSelectionCouseID,
                             CourseCollegeID = a.EM_ExecutableFreeSelectionCouse.CF_Department.CollegeID,
                             ClassroomID = a.ClassroomID,
                             ClassroomName = a.CF_Classroom.Name,
                             CoursesTimeID = a.CoursesTimeID,
                             TimesSegment = b.TimesSegment,
                             TimesSegmentName = gicbsta.Name,
                             StartTimes = b.StartTimes,
                             EndTimes = b.EndTimes,
                             Weekday = a.Weekday,
                             RecordStatus = a.RecordStatus,
                             CreateTime = a.CreateTime,
                             CreateUserID = a.CreateUserID,
                             ModifyTime = a.ModifyTime,
                             ModifyUserID = a.ModifyUserID
                         });

            return query;
        }

        //任选设定判断教室冲突
        public IQueryable<ExecutableFreeSelectionCouseSchedulingView> GetRepeatExecutableFreeSelectionCouseClassroom(Guid? CoursesTimeID, Guid? ClassroomID, int? Weekday, Guid? SchoolyearID, int? StartWeeklyNum, int? EndWeeklyNum)
        {
            var query = from a in ExecutableFreeSelectionCouseSchedulingRepository.GetList(x => x.CoursesTimeID == CoursesTimeID && x.ClassroomID == ClassroomID && x.Weekday == Weekday && x.CF_Classroom.IsConcurrentUse != true)
                        join b in ExecutableFreeSelectionCouseRepository.GetList(x => x.SchoolyearID == SchoolyearID) on a.ExecutableFreeSelectionCouseID equals b.ExecutableFreeSelectionCouseID
                        join c in coursesTimeRepository.Entities on a.CoursesTimeID equals c.CoursesTimeID
                        join d in DictionaryItemRepository.Entities on new { c.TimesSegment, DictionaryCode = "CF_TimesSegment" }
                         equals new { TimesSegment = d.Value.Value, d.DictionaryCode } into gbsta
                        from gicbsta in gbsta.DefaultIfEmpty()
                        join s in ExecutableFreeSelectionCouseTeachingSettingRepository.GetList(x =>
                            (x.StartWeeklyNum >= StartWeeklyNum && StartWeeklyNum <= x.EndWeeklyNum) ||
                            (x.StartWeeklyNum >= EndWeeklyNum && EndWeeklyNum <= x.EndWeeklyNum) ||
                            (StartWeeklyNum <= x.StartWeeklyNum && x.StartWeeklyNum <= EndWeeklyNum) ||
                            (StartWeeklyNum <= x.EndWeeklyNum && x.EndWeeklyNum <= EndWeeklyNum)
                            ) on b.ExecutableFreeSelectionCouseID equals s.ExecutableFreeSelectionCouseID
                        select new ExecutableFreeSelectionCouseSchedulingView
                         {
                             DefaultClassName = b.DefaultClassName,
                             SchedulingID = a.ExecutableFreeSelectionCouseSchedulingID,
                             ID = a.ExecutableFreeSelectionCouseID,
                             CourseCollegeID = a.EM_ExecutableFreeSelectionCouse.CF_Department.CollegeID,
                             ClassroomID = a.ClassroomID,
                             ClassroomName = a.CF_Classroom.Name,
                             CoursesTimeID = a.CoursesTimeID,
                             TimesSegment = c.TimesSegment,
                             TimesSegmentName = gicbsta.Name,
                             StartTimes=c.StartTimes,
                             EndTimes = c.EndTimes,
                             Weekday = a.Weekday,
                             RecordStatus = b.RecordStatus,
                             CreateTime = b.CreateTime,
                             CreateUserID = b.CreateUserID,
                             ModifyTime = b.ModifyTime,
                             ModifyUserID = b.ModifyUserID
                         };

            return query;
        }

        //任选申判断教室冲突
        public IQueryable<ExecutableFreeSelectionCouseSchedulingView> GetRepeatFreeSelectionCouseApplyClassroom(Guid? CoursesTimeID, Guid? ClassroomID, int? Weekday, Guid? SchoolyearID, int? StartWeeklyNum, int? EndWeeklyNum, int? ApprovalStatus)
        {
            var query = from a in FreeSelectionCouseApplySchedulingRepository.GetList(x => x.CoursesTimeID == CoursesTimeID && x.ClassroomID == ClassroomID && x.Weekday == Weekday && x.CF_Classroom.IsConcurrentUse != true)
                        join b in FreeSelectionCouseApplyRepository.GetList(x => x.SchoolyearID == SchoolyearID && x.ApprovalStatus != ApprovalStatus) on a.FreeSelectionCouseApplyID equals b.FreeSelectionCouseApplyID
                        join c in coursesTimeRepository.Entities on a.CoursesTimeID equals c.CoursesTimeID
                        join d in DictionaryItemRepository.Entities on new { c.TimesSegment, DictionaryCode = "CF_TimesSegment" }
                         equals new { TimesSegment = d.Value.Value, d.DictionaryCode } into gbsta
                        from gicbsta in gbsta.DefaultIfEmpty()
                        join s in FreeSelectionCouseApplyTeachingSettingRepository.GetList(x =>
                            (x.StartWeeklyNum >= StartWeeklyNum && StartWeeklyNum <= x.EndWeeklyNum) ||
                            (x.StartWeeklyNum >= EndWeeklyNum && EndWeeklyNum <= x.EndWeeklyNum) ||
                            (StartWeeklyNum <= x.StartWeeklyNum && x.StartWeeklyNum <= EndWeeklyNum) ||
                            (StartWeeklyNum <= x.EndWeeklyNum && x.EndWeeklyNum <= EndWeeklyNum)
                            ) on b.FreeSelectionCouseApplyID equals s.FreeSelectionCouseApplyID
                        select new ExecutableFreeSelectionCouseSchedulingView
                        {
                            DefaultClassName = b.DefaultClassName,
                            SchedulingID = a.FreeSelectionCouseApplySchedulingID,
                            ID = a.FreeSelectionCouseApplyID,
                            CourseCollegeID = a.EM_FreeSelectionCouseApply.CF_Department.CollegeID,
                            ClassroomID = a.ClassroomID,
                            ClassroomName = a.CF_Classroom.Name,
                            CoursesTimeID = a.CoursesTimeID,
                            TimesSegment = c.TimesSegment,
                            TimesSegmentName = gicbsta.Name,
                            Weekday = a.Weekday,
                            StartTimes=c.StartTimes,
                            EndTimes = c.EndTimes,
                            RecordStatus = b.RecordStatus,
                            CreateTime = b.CreateTime,
                            CreateUserID = b.CreateUserID,
                            ModifyTime = b.ModifyTime,
                            ModifyUserID = b.ModifyUserID
                        };

            return query;
        }

        //任选设定判断教师冲突
        public IQueryable<ExecutableFreeSelectionCouseSchedulingView> GetRepeatExecutableFreeSelectionCouseTeacher(List<Guid> UserIDList, Guid? CoursesTimeID, int? Weekday, Guid? SchoolyearID, int? StartWeeklyNum, int? EndWeeklyNum)
        {
            var query = from a in ExecutableFreeSelectionCouseSchedulingRepository.GetList(x => x.CoursesTimeID == CoursesTimeID && x.Weekday == Weekday)
                        join b in ExecutableFreeSelectionCouseRepository.GetList(x => x.SchoolyearID == SchoolyearID) on a.ExecutableFreeSelectionCouseID equals b.ExecutableFreeSelectionCouseID
                        join c in coursesTimeRepository.Entities on a.CoursesTimeID equals c.CoursesTimeID
                        join d in DictionaryItemRepository.Entities on new { c.TimesSegment, DictionaryCode = "CF_TimesSegment" }
                         equals new { TimesSegment = d.Value.Value, d.DictionaryCode } into gbsta
                        from gicbsta in gbsta.DefaultIfEmpty()
                        join e in ExecutableFreeSelectionCouseTeacherRepository.GetList(x => UserIDList.Contains(x.UserID.Value)) on b.ExecutableFreeSelectionCouseID equals e.ExecutableFreeSelectionCouseID
                        join s in ExecutableFreeSelectionCouseTeachingSettingRepository.GetList(x =>
                            (x.StartWeeklyNum >= StartWeeklyNum && StartWeeklyNum <= x.EndWeeklyNum) ||
                            (x.StartWeeklyNum >= EndWeeklyNum && EndWeeklyNum <= x.EndWeeklyNum) ||
                            (StartWeeklyNum <= x.StartWeeklyNum && x.StartWeeklyNum <= EndWeeklyNum) ||
                            (StartWeeklyNum <= x.EndWeeklyNum && x.EndWeeklyNum <= EndWeeklyNum)
                            ) on b.ExecutableFreeSelectionCouseID equals s.ExecutableFreeSelectionCouseID

                        select new ExecutableFreeSelectionCouseSchedulingView
                        {
                            DefaultClassName = b.DefaultClassName,
                            SchedulingID = a.ExecutableFreeSelectionCouseSchedulingID,
                            ID = a.ExecutableFreeSelectionCouseID,
                            CourseCollegeID = a.EM_ExecutableFreeSelectionCouse.CF_Department.CollegeID,
                            ClassroomID = a.ClassroomID,
                            ClassroomName = a.CF_Classroom.Name,
                            CoursesTimeID = a.CoursesTimeID,
                            TimesSegment = c.TimesSegment,
                            TimesSegmentName = gicbsta.Name,
                            StartTimes = c.StartTimes,
                            EndTimes = c.EndTimes,
                            Weekday = a.Weekday,
                            RecordStatus = b.RecordStatus,
                            CreateTime = b.CreateTime,
                            CreateUserID = b.CreateUserID,
                            ModifyTime = b.ModifyTime,
                            ModifyUserID = b.ModifyUserID
                        };

            return query;
        }

        //任选申请判断教师冲突
        public IQueryable<ExecutableFreeSelectionCouseSchedulingView> GetRepeatFreeSelectionCouseApplyTeacher(List<Guid> UserIDList, Guid? CoursesTimeID, int? Weekday, Guid? SchoolyearID, int? StartWeeklyNum, int? EndWeeklyNum, int? ApprovalStatus)
        {
            var query = from a in FreeSelectionCouseApplySchedulingRepository.GetList(x => x.CoursesTimeID == CoursesTimeID && x.Weekday == Weekday && x.CF_Classroom.IsConcurrentUse != true)
                        join b in FreeSelectionCouseApplyRepository.GetList(x => x.SchoolyearID == SchoolyearID && x.ApprovalStatus != ApprovalStatus) on a.FreeSelectionCouseApplyID equals b.FreeSelectionCouseApplyID
                        join c in coursesTimeRepository.Entities on a.CoursesTimeID equals c.CoursesTimeID
                        join d in DictionaryItemRepository.Entities on new { c.TimesSegment, DictionaryCode = "CF_TimesSegment" }
                         equals new { TimesSegment = d.Value.Value, d.DictionaryCode } into gbsta
                        from gicbsta in gbsta.DefaultIfEmpty()
                        join e in FreeSelectionCouseApplyTeacherRepository.GetList(x => UserIDList.Contains(x.UserID.Value)) on b.FreeSelectionCouseApplyID equals e.FreeSelectionCouseApplyID
                        join s in FreeSelectionCouseApplyTeachingSettingRepository.GetList(x =>
                            (x.StartWeeklyNum >= StartWeeklyNum && StartWeeklyNum <= x.EndWeeklyNum) ||
                            (x.StartWeeklyNum >= EndWeeklyNum && EndWeeklyNum <= x.EndWeeklyNum) ||
                            (StartWeeklyNum <= x.StartWeeklyNum && x.StartWeeklyNum <= EndWeeklyNum) ||
                            (StartWeeklyNum <= x.EndWeeklyNum && x.EndWeeklyNum <= EndWeeklyNum)
                            ) on b.FreeSelectionCouseApplyID equals s.FreeSelectionCouseApplyID
                        select new ExecutableFreeSelectionCouseSchedulingView
                        {
                            DefaultClassName = b.DefaultClassName,
                            SchedulingID = a.FreeSelectionCouseApplySchedulingID,
                            ID = a.FreeSelectionCouseApplyID,
                            CourseCollegeID = a.EM_FreeSelectionCouseApply.CF_Department.CollegeID,
                            ClassroomID = a.ClassroomID,
                            ClassroomName = a.CF_Classroom.Name,
                            CoursesTimeID = a.CoursesTimeID,
                            TimesSegment = c.TimesSegment,
                            TimesSegmentName = gicbsta.Name,
                            StartTimes = c.StartTimes,
                            EndTimes = c.EndTimes,
                            Weekday = a.Weekday,
                            RecordStatus = b.RecordStatus,
                            CreateTime = b.CreateTime,
                            CreateUserID = b.CreateUserID,
                            ModifyTime = b.ModifyTime,
                            ModifyUserID = b.ModifyUserID
                        };

            return query;
        }

        //教学任务判断教室冲突
        public IQueryable<ExecutableFreeSelectionCouseSchedulingView> GetRepeatEducationMissionClassroom(Guid? CoursesTimeID, Guid? ClassroomID, int? Weekday, Guid? SchoolyearID, int? StartWeeklyNum, int? EndWeeklyNum)
        {
            var query = from a in EducationSchedulingRepository.GetList(x => x.CoursesTimeID == CoursesTimeID && x.ClassroomID == ClassroomID && x.Weekday == Weekday && x.CF_Classroom.IsConcurrentUse != true && x.SchoolyearID == SchoolyearID)
                        join b in coursesTimeRepository.Entities on a.CoursesTimeID equals b.CoursesTimeID
                        join s in EducationSchedulingWeekNumRepository.GetList(x =>
                            (x.WeekNum >= StartWeeklyNum && x.WeekNum <= EndWeeklyNum)
                            ) on a.EducationSchedulingID equals s.EducationSchedulingID
                        join e in EducationSchedulingClassRepository.Entities on a.EducationSchedulingClassID equals e.EducationSchedulingClassID
                        join f in EducationMissionClassRepository.Entities on e.EducationMissionClassID equals f.EducationMissionClassID
                        select new ExecutableFreeSelectionCouseSchedulingView
                        {
                            DefaultClassName = f.Name,

                        };

            return query;
        }

        //教学任务判断教师冲突
        public IQueryable<ExecutableFreeSelectionCouseSchedulingView> GetRepeatEducationMissionTeacher(List<Guid> UserIDList, Guid? CoursesTimeID, int? Weekday, Guid? SchoolyearID, int? StartWeeklyNum, int? EndWeeklyNum)
        {
            var query = from a in EducationSchedulingRepository.GetList(x => x.CoursesTimeID == CoursesTimeID && x.Weekday == Weekday && x.SchoolyearID == SchoolyearID)
                        join b in EducationSchedulingTeacherRepository.GetList(x => UserIDList.Contains(x.UserID.Value)) on a.EducationSchedulingID equals b.EducationSchedulingID
                        join s in EducationSchedulingWeekNumRepository.GetList(x =>
                            (x.WeekNum >= StartWeeklyNum && x.WeekNum <= EndWeeklyNum)
                            ) on a.EducationSchedulingID equals s.EducationSchedulingID
                        join e in EducationSchedulingClassRepository.Entities on a.EducationSchedulingClassID equals e.EducationSchedulingClassID
                        join f in EducationMissionClassRepository.Entities on e.EducationMissionClassID equals f.EducationMissionClassID
                        select new ExecutableFreeSelectionCouseSchedulingView
                        {
                            DefaultClassName = f.Name,

                        };
            return query;
        }

        public IQueryable<EM_ExecutableFreeSelectionCouse> GetNoMissionClassExecutableFreeSelectionCourse(int? schoolyearValue, List<Guid> educationMissionClassIDList)
        {
            var q = (from freeSelection in ExecutableFreeSelectionCouseRepository.GetList(x => x.RecordStatus == (int)EMIS.ViewModel.EM_SelectCourseResultStatus.Created &&
                    x.CF_Schoolyear.Value >= schoolyearValue)
                     join missionClass in
                         (
                             from missionClass in EducationMissionClassRepository.GetList(x => x.HandleModeID == (int)CF_HandleMode.SelectionCourse && x.OptionalCourseTypeID == (int)CF_CourseSelectType.FreeSelectionCourse && !educationMissionClassIDList.Contains(x.EducationMissionClassID))
                             join mission in EducationMissionRepository.Entities on missionClass.EducationMissionID equals mission.EducationMissionID
                             from schedulingClass in missionClass.EM_EducationSchedulingClass
                             from student in schedulingClass.CF_Student
                             from freeSelection in student.EM_ExecutableFreeSelectionCouse
                                .Where(x => x.SchoolyearID == mission.SchoolyearID
                                    && x.EM_FreeSelectionCouse.CoursematerialID == missionClass.CoursematerialID)
                             group freeSelection by freeSelection.ExecutableFreeSelectionCouseID into g
                             select new { ExecutableFreeSelectionCouseID = g.Key }
                             )
                         on freeSelection.ExecutableFreeSelectionCouseID equals missionClass.ExecutableFreeSelectionCouseID into dmissionClass
                     from missionClass in dmissionClass.DefaultIfEmpty()
                     where missionClass.ExecutableFreeSelectionCouseID == null
                     select freeSelection);

            return q;
        }

    }
}