JW/EMiS.DataLogic/RetakeManage/RetakePlanTaskDAL.cs

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

namespace EMIS.DataLogic.RetakeManage
{
    public class RetakePlanTaskDAL
    {
        public RetakeConditionRepository RetakConditionRepository { get; set; }
        public RetakeConditionParameterRepository RetakeConditionParameterRepository { get; set; }
        public RetakePlanRepository RetakePlanRepository { get; set; }
        public RetakePlanStudentRepository RetakePlanStudentRepository { get; set; }
        public RetakeExamsCatagoryRepository RetakeExamsCatagoryRepository { get; set; }
        public RetakePlanSettingRepository RetakePlanSettingRepository { get; set; }
        public RetakePlanTeachingModeTypeRepository RetakePlanTeachingModeTypeRepository { get; set; }
        public RetakePlanTeachingPlaceRepository RetakePlanTeachingPlaceRepository { get; set; }
        public RetakePlanTeachingSettingRepository RetakePlanTeachingSettingRepository { get; set; }
        public FinallyScoreRepository FinallyScoreRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public InSchoolSettingRepository InSchoolSettingRepository { get; set; }
        public ParameterRepository ParameterRepository { get; set; }
        public CoursematerialRepository CoursematerialRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public DepartmentRepository DepartmentRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public DictionaryItemRepository DictionaryItemRepository { get; set; }
        public RetakePlanSettingSchedulingRepository RetakePlanSettingSchedulingRepository { get; set; }
        public ClassroomRepository ClassroomRepository { 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 RetakePlanTeacherRepository RetakePlanTeacherRepository { get; set; }
        public StaffRepository StaffRepository { get; set; }


        /// <summary>
        /// 学生成绩预查信息View查询(不及格成绩，小于60分)
        /// </summary>
        /// <param name="expFinallyScore"></param>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<RetakeStudentListView> GetRetakePreStudentView(Expression<Func<ER_FinallyScore, bool>> expFinallyScore,
                Expression<Func<CF_Student, bool>> expStudent, Expression<Func<ER_RetakePlan, bool>> expRetakePlan)
        {
            //注：需要对重修设置中的重修学年学期、重修考试性质进行判断（不能为空）
            var query = from finSco in FinallyScoreRepository.GetList(expFinallyScore)
                        join rec in RetakeExamsCatagoryRepository.Entities
                        on finSco.ExamsCategoryID equals rec.ExamsCategoryID
                        join stu in StudentRepository.GetList(expStudent)
                        on finSco.UserID equals stu.UserID
                        join sy in SchoolyearRepository.Entities
                        on finSco.SchoolyearID equals sy.SchoolyearID
                        join cou in CoursematerialRepository.Entities
                        on finSco.CoursematerialID equals cou.CoursematerialID
                        join dep in DepartmentRepository.Entities
                        on finSco.DepartmentID equals dep.DepartmentID
                        join cl in ClassmajorRepository.Entities
                        on stu.ClassmajorID equals cl.ClassmajorID
                        join gr in GrademajorRepository.Entities
                        on cl.GrademajorID equals gr.GrademajorID
                        join fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        join col in CollegeRepository.Entities
                        on fa.CollegeID equals col.CollegeID
                        join us in UserRepository.Entities
                        on stu.UserID equals us.UserID
                        join rePlan in
                            (
                              from rp in RetakePlanRepository.GetList(expRetakePlan)
                              join rpStu in RetakePlanStudentRepository.Entities
                              on rp.RetakePlanID equals rpStu.RetakePlanID
                              select new
                              {
                                  RetakePlanID = rp.RetakePlanID,
                                  SchoolyearID = rp.SchoolyearID,
                                  CoursematerialID = rp.CoursematerialID,
                                  CourseTypeID = rp.CourseTypeID,
                                  Credit = rp.Credit,
                                  RetakePlanStudentID = rpStu.RetakePlanStudentID,
                                  UserID = (Guid?)rpStu.UserID,
                                  SchoolyearNumID = rpStu.SchoolyearNumID,
                                  StarttermID = rpStu.StarttermID
                              }
                            )
                        on
                        new { finSco.CoursematerialID, finSco.CourseTypeID, finSco.Credit, finSco.UserID, finSco.SchoolyearNumID, finSco.StarttermID }
                        equals
                        new { rePlan.CoursematerialID, rePlan.CourseTypeID, rePlan.Credit, rePlan.UserID, rePlan.SchoolyearNumID, rePlan.StarttermID }
                        into rePlanTemp
                        from rePlanStu in rePlanTemp.DefaultIfEmpty()
                        where finSco.TotalScore < 60 && rePlanStu.RetakePlanID == null
                        select new RetakeStudentListView
                        {
                            FinallyScoreID = finSco.FinallyScoreID,
                            SchoolyearID = finSco.SchoolyearID,
                            SchoolyearCode = sy.Code,
                            DepartmentID = finSco.DepartmentID, //开课教研室
                            DepartmentCode = dep.No,
                            DepartmentName = dep.Name,
                            UserID = finSco.UserID,
                            UserName = us.Name,
                            LoginID = us.LoginID,
                            SexID = stu.SexID,
                            InSchoolStatusID = stu.InSchoolStatusID,
                            StudentStatusID = stu.StudentStatus,
                            ClassmajorID = stu.ClassmajorID,
                            ClassmajorCode = cl.No,
                            ClassmajorName = cl.Name,
                            GrademajorID = cl.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            CollegeID = fa.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            Gradeyear = gr.GradeID,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            ExamsCategoryID = finSco.ExamsCategoryID,
                            ExamsStateID = finSco.ExamsStateID,
                            CoursematerialID = finSco.CoursematerialID,
                            Abbreviation = cou.Abbreviation,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            CourseTypeID = finSco.CourseTypeID,
                            CourseCredit = finSco.Credit ?? 0,
                            Credit = finSco.ScoreCredit ?? 0,
                            TotalScore = finSco.TotalScore,
                            SchoolyearNumID = finSco.SchoolyearNumID,
                            StarttermID = finSco.StarttermID
                        };

            return query;

        }

        /// <summary>
        /// 查询重修计划信息View(统计对应的重修计划名单人数-计划人数)
        /// </summary>
        /// <param name="expRetakePlan"></param>
        /// <returns></returns>
        public IQueryable<RetakePlanTaskView> GetRetakePlanTaskView(Expression<Func<ER_RetakePlan, bool>> expRetakePlan)
        {
            var query = from rePlan in RetakePlanRepository.GetList(expRetakePlan)
                        join cou in CoursematerialRepository.Entities
                        on rePlan.CoursematerialID equals cou.CoursematerialID
                        join sy in SchoolyearRepository.Entities
                        on rePlan.SchoolyearID equals sy.SchoolyearID
                        join reStu in
                            (
                                from stu in RetakePlanStudentRepository.Entities
                                group stu by stu.RetakePlanID into g
                                select new
                                {
                                    RetakePlanID = g.Key,
                                    PlanNumber = g.Count()
                                }
                            )
                        on rePlan.RetakePlanID equals reStu.RetakePlanID
                        into reStuTemp
                        from rePlanStu in reStuTemp.DefaultIfEmpty()
                        join reSet in RetakePlanSettingRepository.Entities
                        on rePlan.RetakePlanID equals reSet.RetakePlanID
                        into reSetTemp
                        from rePlanSet in reSetTemp.DefaultIfEmpty()
                        join dep in DepartmentRepository.Entities
                        on rePlanSet.DepartmentID equals dep.DepartmentID
                        into reDepTemp
                        from reDep in reDepTemp.DefaultIfEmpty()
                        join reTeaching in RetakePlanTeachingSettingRepository.Entities
                        on rePlan.RetakePlanID equals reTeaching.RetakePlanID
                        into reTeachingTemp
                        from rePlanTeaching in reTeachingTemp.DefaultIfEmpty()
                        select new RetakePlanTaskView
                        {
                            RetakePlanID = rePlan.RetakePlanID,
                            SchoolyearID = rePlan.SchoolyearID,
                            SchoolyearCode = sy.Code,
                            RetakeTypeID = rePlan.RetakeTypeID,
                            ClassName = rePlan.ClassName,
                            CoursematerialID = rePlan.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            DepartmentID = rePlanSet.DepartmentID,
                            DepartmentCode = reDep.No,
                            DepartmentName = reDep.Name,
                            CourseCollegeID = reDep.CF_College.CollegeID,
                            CourseCollegeNo = reDep.CF_College.No,
                            CourseCollegeName = reDep.CF_College.Name,
                            CourseTypeID = rePlan.CourseTypeID,
                            Credit = rePlan.Credit ?? 0,
                            PeopleNumlimit = rePlan.PeopleNumlimit ?? 0,
                            PeopleNumlower = rePlan.PeopleNumlower ?? 0,
                            TheoryCourse = rePlanTeaching.TheoryCourse ?? 0,
                            Practicehours = rePlanTeaching.Practicehours ?? 0,
                            Trialhours = rePlanTeaching.Trialhours ?? 0,
                            StartWeeklyNum = rePlanTeaching.StartWeeklyNum,
                            EndWeeklyNum = rePlanTeaching.EndWeeklyNum,
                            PlanNumber = rePlanStu != null ? rePlanStu.PlanNumber : 0,
                            RecordStatus = rePlan.RecordStatus
                        };

            return query;
        }

        /// <summary>
        /// 查询重修计划相关信息View(含相关设置信息、排课教室等)
        /// 注：排课明细中目前只支持同一教室的情况
        /// </summary>
        /// <param name="expRetakePlan"></param>
        /// <returns></returns>
        public IQueryable<RetakePlanTaskSettingView> GetRetakePlanTaskSettingView(Expression<Func<ER_RetakePlan, bool>> expRetakePlan)
        {
            var query = from rePlan in RetakePlanRepository.GetList(expRetakePlan)
                        join cou in CoursematerialRepository.Entities
                        on rePlan.CoursematerialID equals cou.CoursematerialID
                        join sy in SchoolyearRepository.Entities
                        on rePlan.SchoolyearID equals sy.SchoolyearID
                        join reSet in RetakePlanSettingRepository.Entities
                        on rePlan.RetakePlanID equals reSet.RetakePlanID
                        into reSetTemp
                        from rePlanSet in reSetTemp.DefaultIfEmpty()
                        join dep in DepartmentRepository.Entities
                        on rePlanSet.DepartmentID equals dep.DepartmentID
                        into reDepTemp
                        from reDep in reDepTemp.DefaultIfEmpty()
                        join reTeaching in RetakePlanTeachingSettingRepository.Entities
                        on rePlan.RetakePlanID equals reTeaching.RetakePlanID
                        into reTeachingTemp
                        from rePlanTeaching in reTeachingTemp.DefaultIfEmpty()
                        select new RetakePlanTaskSettingView
                        {
                            RetakePlanID = rePlan.RetakePlanID,
                            SchoolyearID = rePlan.SchoolyearID,
                            SchoolyearCode = sy.Code,
                            RetakeTypeID = rePlan.RetakeTypeID,
                            ClassName = rePlan.ClassName,
                            CoursematerialID = rePlan.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            DepartmentID = rePlanSet.DepartmentID,
                            DepartmentCode = reDep.No,
                            DepartmentName = reDep.Name,
                            CourseCollegeID = reDep.CF_College.CollegeID,
                            CourseCollegeNo = reDep.CF_College.No,
                            CourseCollegeName = reDep.CF_College.Name,
                            Credit = rePlan.Credit ?? 0,
                            CourseTypeID = rePlan.CourseTypeID,
                            CourseStructureID = rePlanSet.CourseStructureID,
                            CourseCategoryID = rePlanSet.CourseCategoryID,
                            CourseQualityID = rePlanSet.CourseQualityID,
                            ExaminationModeID = rePlanSet.ExaminationModeID,
                            TeachinglanguageID = rePlanSet.TeachinglanguageID,
                            IsNeedMaterial = rePlanSet.IsNeedMaterial ?? false,
                            PracticeTypeID = rePlanSet.PracticeTypeID,
                            CourseFineID = rePlanSet.CourseFineID,
                            IsRequired = rePlanSet.IsRequired ?? false,
                            IsSpecialtycore = rePlanSet.IsSpecialtycore ?? false,
                            IsCooperation = rePlanSet.IsCooperation ?? false,
                            IsElective = rePlanSet.IsElective ?? false,
                            IsNetworkCourse = rePlanSet.IsNetworkCourse ?? false,
                            IsMainCourse = rePlanSet.IsMainCourse ?? false,
                            TheoryCourse = rePlanTeaching.TheoryCourse ?? 0,
                            Practicehours = rePlanTeaching.Practicehours ?? 0,
                            Trialhours = rePlanTeaching.Trialhours ?? 0,
                            PeopleNumlimit = rePlan.PeopleNumlimit ?? 0,
                            PeopleNumlower = rePlan.PeopleNumlower ?? 0,
                            TheoryWeeklyNum = rePlanTeaching.TheoryWeeklyNum ?? 0,
                            PracticeWeeklyNum = rePlanTeaching.PracticeWeeklyNum ?? 0,
                            TrialWeeklyNum = rePlanTeaching.TrialWeeklyNum ?? 0,
                            StartWeeklyNum = rePlanTeaching.StartWeeklyNum ?? 0,
                            EndWeeklyNum = rePlanTeaching.EndWeeklyNum ?? 0,
                            WeeklyHours = rePlanTeaching.WeeklyHours ?? 0,
                            WeeklyNum = rePlanTeaching.WeeklyNum ?? 0,
                            RecordStatus = rePlan.RecordStatus,
                            Remarks = rePlanSet.Remarks,
                            ClassroomID = rePlan.ER_RetakePlanSettingScheduling.FirstOrDefault() != null ? rePlan.ER_RetakePlanSettingScheduling.FirstOrDefault().ClassroomID : null
                        };

            return query;
        }

        /// <summary>
        /// 查询重修计划名单信息View
        /// </summary>
        /// <param name="expRetakePlan"></param>
        /// <returns></returns>
        public IQueryable<RetakeStudentListView> GetRetakePlanTaskStudentView(Expression<Func<ER_RetakePlan, bool>> expRetakePlan)
        {
            var query = from rePlan in RetakePlanRepository.GetList(expRetakePlan)
                        join sy in SchoolyearRepository.Entities
                        on rePlan.SchoolyearID equals sy.SchoolyearID
                        join cou in CoursematerialRepository.Entities
                        on rePlan.CoursematerialID equals cou.CoursematerialID
                        join rePlanStu in RetakePlanStudentRepository.Entities
                        on rePlan.RetakePlanID equals rePlanStu.RetakePlanID
                        join reSet in RetakePlanSettingRepository.Entities
                        on rePlan.RetakePlanID equals reSet.RetakePlanID
                        into reSetTemp
                        from rePlanSet in reSetTemp.DefaultIfEmpty()
                        join dep in DepartmentRepository.Entities
                        on rePlanSet.DepartmentID equals dep.DepartmentID
                        into reDepTemp
                        from reDep in reDepTemp.DefaultIfEmpty()
                        join stu in StudentRepository.Entities
                        on rePlanStu.UserID equals stu.UserID
                        join us in UserRepository.Entities
                        on stu.UserID equals us.UserID
                        join cl in ClassmajorRepository.Entities
                        on stu.ClassmajorID equals cl.ClassmajorID
                        join gr in GrademajorRepository.Entities
                        on cl.GrademajorID equals gr.GrademajorID
                        join fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        join col in CollegeRepository.Entities
                        on fa.CollegeID equals col.CollegeID
                        select new RetakeStudentListView
                        {
                            RetakePlanID = rePlan.RetakePlanID,
                            RetakePlanStudentID = rePlanStu.RetakePlanStudentID,
                            RetakeTypeID = rePlan.RetakeTypeID,
                            SchoolyearID = rePlan.SchoolyearID,
                            SchoolyearCode = sy.Code,
                            ClassName = rePlan.ClassName,
                            CoursematerialID = rePlan.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            DepartmentID = rePlanSet.DepartmentID,
                            DepartmentCode = reDep.No,
                            DepartmentName = reDep.Name,
                            CourseTypeID = rePlan.CourseTypeID,
                            CourseCredit = rePlan.Credit ?? 0,
                            SchoolyearNumID = rePlanStu.SchoolyearNumID,
                            StarttermID = rePlanStu.StarttermID,
                            UserID = rePlanStu.UserID,
                            LoginID = us.LoginID,
                            UserName = us.Name,
                            SexID = stu.SexID,
                            InSchoolStatusID = stu.InSchoolStatusID,
                            StudentStatusID = stu.StudentStatus,
                            ClassmajorID = stu.ClassmajorID,
                            ClassmajorCode = cl.No,
                            ClassmajorName = cl.Name,
                            GrademajorID = cl.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            CollegeID = fa.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            Gradeyear = gr.GradeID,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            RecordStatus = rePlan.RecordStatus,
                            ApplyStatus = rePlan.CF_Student.Any(x => x.UserID == rePlanStu.UserID) ? true : false
                        };

            return query;
        }

        /// <summary>
        /// 查询重修计划名单信息View
        /// </summary>
        /// <param name="expRetakePlan"></param>
        /// <param name="expRetakePlanStudent"></param>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<RetakeStudentListView> GetRetakePlanTaskStudentView(Expression<Func<ER_RetakePlan, bool>> expRetakePlan,
                Expression<Func<ER_RetakePlanStudent, bool>> expRetakePlanStudent, Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from rePlan in RetakePlanRepository.GetList(expRetakePlan)
                        join sy in SchoolyearRepository.Entities
                        on rePlan.SchoolyearID equals sy.SchoolyearID
                        join cou in CoursematerialRepository.Entities
                        on rePlan.CoursematerialID equals cou.CoursematerialID
                        join rePlanStu in RetakePlanStudentRepository.GetList(expRetakePlanStudent)
                        on rePlan.RetakePlanID equals rePlanStu.RetakePlanID
                        join reSet in RetakePlanSettingRepository.Entities
                        on rePlan.RetakePlanID equals reSet.RetakePlanID
                        into reSetTemp
                        from rePlanSet in reSetTemp.DefaultIfEmpty()
                        join dep in DepartmentRepository.Entities
                        on rePlanSet.DepartmentID equals dep.DepartmentID
                        into reDepTemp
                        from reDep in reDepTemp.DefaultIfEmpty()
                        join stu in StudentRepository.GetList(expStudent)
                        on rePlanStu.UserID equals stu.UserID
                        join us in UserRepository.Entities
                        on stu.UserID equals us.UserID
                        join cl in ClassmajorRepository.Entities
                        on stu.ClassmajorID equals cl.ClassmajorID
                        join gr in GrademajorRepository.Entities
                        on cl.GrademajorID equals gr.GrademajorID
                        join fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        join col in CollegeRepository.Entities
                        on fa.CollegeID equals col.CollegeID
                        select new RetakeStudentListView
                        {
                            RetakePlanID = rePlan.RetakePlanID,
                            RetakePlanStudentID = rePlanStu.RetakePlanStudentID,
                            RetakeTypeID = rePlan.RetakeTypeID,
                            SchoolyearID = rePlan.SchoolyearID,
                            SchoolyearCode = sy.Code,
                            ClassName = rePlan.ClassName,
                            CoursematerialID = rePlan.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            DepartmentID = rePlanSet.DepartmentID,
                            DepartmentCode = reDep.No,
                            DepartmentName = reDep.Name,
                            CourseTypeID = rePlan.CourseTypeID,
                            CourseCredit = rePlan.Credit ?? 0,
                            SchoolyearNumID = rePlanStu.SchoolyearNumID,
                            StarttermID = rePlanStu.StarttermID,
                            UserID = rePlanStu.UserID,
                            LoginID = us.LoginID,
                            UserName = us.Name,
                            SexID = stu.SexID,
                            InSchoolStatusID = stu.InSchoolStatusID,
                            StudentStatusID = stu.StudentStatus,
                            ClassmajorID = stu.ClassmajorID,
                            ClassmajorCode = cl.No,
                            ClassmajorName = cl.Name,
                            GrademajorID = cl.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            CollegeID = fa.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            Gradeyear = gr.GradeID,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            RecordStatus = rePlan.RecordStatus,
                            ApplyStatus = rePlan.CF_Student.Any(x => x.UserID == rePlanStu.UserID) ? true : false
                        };

            return query;
        }

        /// <summary>
        /// 查询重修计划课程表信息
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<RetakePlanSettingSchedulingView> GetSchedulingView(Expression<Func<ER_RetakePlanSettingScheduling, bool>> exp)
        {
            var query = from rePlanSch in RetakePlanSettingSchedulingRepository.GetList(exp)
                        join rePlan in RetakePlanRepository.Entities
                        on rePlanSch.RetakePlanID equals rePlan.RetakePlanID
                        join rePlanSet in RetakePlanSettingRepository.Entities
                        on rePlan.RetakePlanID equals rePlanSet.RetakePlanID
                        join couTime in coursesTimeRepository.Entities
                        on rePlanSch.CoursesTimeID equals couTime.CoursesTimeID
                        join item in DictionaryItemRepository.Entities
                        on
                        new { couTime.TimesSegment, DictionaryCode = typeof(EMIS.ViewModel.CF_TimesSegment).Name }
                        equals
                        new { TimesSegment = item.Value.Value, item.DictionaryCode }
                        into couTimeTemp
                        from rePlanTime in couTimeTemp.DefaultIfEmpty()
                        join room in ClassroomRepository.Entities
                        on rePlanSch.ClassroomID equals room.ClassroomID
                        into roomTemp
                        from rePlanRoom in roomTemp.DefaultIfEmpty()
                        join dep in DepartmentRepository.Entities
                       on rePlanSet.DepartmentID equals dep.DepartmentID
                       into reDepTemp
                        from reDep in reDepTemp.DefaultIfEmpty()
                        select new RetakePlanSettingSchedulingView
                        {
                            SchedulingID = rePlanSch.RetakePlanSettingSchedulingID,
                            ID = rePlanSch.RetakePlanID,
                            CoursesTimeID = rePlanSch.CoursesTimeID,
                            Weekday = rePlanSch.Weekday,
                            TimesSegment = couTime.TimesSegment,
                            TimesSegmentName = rePlanTime.Name,
                            StartTimes = couTime.StartTimes,
                            EndTimes = couTime.EndTimes,
                            ClassroomID = rePlanSch.ClassroomID,
                            ClassroomName = rePlanRoom.Name,
                            CourseDepartmentID = rePlanSet.DepartmentID,
                            CourseCollegeID = reDep.CollegeID,
                            RecordStatus = rePlanSch.RecordStatus,
                            CreateTime = rePlanSch.CreateTime,
                            CreateUserID = rePlanSch.CreateUserID,
                            ModifyTime = rePlanSch.ModifyTime,
                            ModifyUserID = rePlanSch.ModifyUserID
                        };

            return query;
        }

        /// <summary>
        /// 查询重修计划任课老师信息
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<RetakePlanTeacherListView> GetRetakePlanTeacherViewQueryable(Expression<Func<ER_RetakePlan, bool>> exp)
        {
            var query = from rePlan in RetakePlanRepository.GetList(exp)
                        join reTea in RetakePlanTeacherRepository.Entities
                        on rePlan.RetakePlanID equals reTea.RetakePlanID
                        join staff in StaffRepository.Entities
                        on reTea.UserID equals staff.UserID
                        join us in UserRepository.Entities
                        on staff.UserID equals us.UserID
                        select new RetakePlanTeacherListView
                        {
                            RetakePlanTeacherID = reTea.RetakePlanTeacherID,
                            RetakePlanID = rePlan.RetakePlanID,
                            UserID = reTea.UserID.Value,
                            LoginID = us.LoginID,
                            Name = us.Name,
                            Sex = staff.SexID,
                            BirthDate = staff.BirthDate,
                            TeacherType = staff.TeacherTypeID,
                            IncumbencyState = staff.IncumbencyState,
                            Title = staff.TitleID,
                            TeachingMethod = reTea.TeachType,
                            DepartmentID = staff.DepartmentID,
                            DepartmentCode = staff.CF_Department.No,
                            DepartmentName = staff.CF_Department.Name,
                            CollegeID = staff.CollegeID,
                            CollegeNo = staff.CF_College.No,
                            CollegeName = staff.CF_College.Name
                        };

            return query;
        }

        /// <summary>
        /// 查询重修计划授课方式信息
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<Sys_DictionaryItem> GetTeachingModeType(Expression<Func<ER_RetakePlan, bool>> exp)
        {
            var query = from rePlan in RetakePlanRepository.GetList(exp)
                        join reTeachingMode in RetakePlanTeachingModeTypeRepository.Entities
                        on rePlan.RetakePlanID equals reTeachingMode.RetakePlanID
                        join item in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(CF_TeachingMode).Name)
                        on reTeachingMode.TeachingModeID equals item.Value
                        into itemTemp
                        from dicItem in itemTemp.DefaultIfEmpty()
                        select dicItem;

            return query;
        }

        /// <summary>
        /// 查询重修计划授课地点信息
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<Sys_DictionaryItem> GetTeachingPlace(Expression<Func<ER_RetakePlan, bool>> exp)
        {
            var query = from rePlan in RetakePlanRepository.GetList(exp)
                        join reTeachingPlace in RetakePlanTeachingPlaceRepository.Entities
                        on rePlan.RetakePlanID equals reTeachingPlace.RetakePlanID
                        join item in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(EMIS.ViewModel.EM_TeachingPlace).Name)
                        on reTeachingPlace.TeachingPlace equals item.Value
                        into itemTemp
                        from dicItem in itemTemp.DefaultIfEmpty()
                        select dicItem;

            return query;
        }

        /// <summary>
        /// 重修排课判断（教室时间）
        /// </summary>
        /// <param name="CoursesTimeID"></param>
        /// <param name="ClassroomID"></param>
        /// <param name="Weekday"></param>
        /// <param name="SchoolyearID"></param>
        /// <param name="StartWeeklyNum"></param>
        /// <param name="EndWeeklyNum"></param>
        /// <returns></returns>
        public IQueryable<RetakePlanSettingSchedulingView> GetRepeatRetakePlanSettingClassroom(Guid? CoursesTimeID, Guid? ClassroomID, 
                int? Weekday, Guid? SchoolyearID, int? StartWeeklyNum, int? EndWeeklyNum)
        {
            var query = from a in RetakePlanSettingSchedulingRepository.GetList(x => x.CoursesTimeID == CoursesTimeID && x.ClassroomID == ClassroomID && x.Weekday == Weekday)
                        join b in RetakePlanSettingRepository.GetList(x => x.ER_RetakePlan.SchoolyearID == SchoolyearID) 
                        on a.RetakePlanID equals b.RetakePlanID
                        join c in coursesTimeRepository.Entities 
                        on a.CoursesTimeID equals c.CoursesTimeID
                        join d in DictionaryItemRepository.Entities 
                        on 
                        new { c.TimesSegment, DictionaryCode = typeof(EMIS.ViewModel.CF_TimesSegment).Name }
                        equals 
                        new { TimesSegment = d.Value.Value, d.DictionaryCode } 
                        into gbsta
                        from gicbsta in gbsta.DefaultIfEmpty()
                        join e in ClassroomRepository.GetList(x => x.IsConcurrentUse != true) 
                        on a.ClassroomID equals e.ClassroomID
                        join f in DepartmentRepository.Entities 
                        on b.DepartmentID equals f.DepartmentID
                        join s in RetakePlanTeachingSettingRepository.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.RetakePlanID equals s.RetakePlanID
                        select new RetakePlanSettingSchedulingView
                        {
                            DefaultClassName = b.ER_RetakePlan.ClassName,
                            SchedulingID = a.RetakePlanSettingSchedulingID,
                            ID = a.RetakePlanID,
                            CourseCollegeID = f.CollegeID,
                            ClassroomID = a.ClassroomID,
                            ClassroomName = e.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;
        }

        /// <summary>
        /// 重修排课判断（教室时间、教师时间）
        /// </summary>
        /// <param name="UserIDList"></param>
        /// <param name="CoursesTimeID"></param>
        /// <param name="Weekday"></param>
        /// <param name="SchoolyearID"></param>
        /// <param name="StartWeeklyNum"></param>
        /// <param name="EndWeeklyNum"></param>
        /// <returns></returns>
        public IQueryable<RetakePlanSettingSchedulingView> GetRepeatRetakePlanTeacher(List<Guid> UserIDList, Guid? CoursesTimeID, 
                int? Weekday, Guid? SchoolyearID, int? StartWeeklyNum, int? EndWeeklyNum)
        {
            var query = from a in RetakePlanSettingSchedulingRepository.GetList(x => x.CoursesTimeID == CoursesTimeID && x.Weekday == Weekday)
                        join b in RetakePlanSettingRepository.GetList(x => x.ER_RetakePlan.SchoolyearID == SchoolyearID) 
                        on a.RetakePlanID equals b.RetakePlanID
                        join c in coursesTimeRepository.Entities 
                        on a.CoursesTimeID equals c.CoursesTimeID
                        join d in DictionaryItemRepository.Entities 
                        on 
                        new { c.TimesSegment, DictionaryCode = typeof(EMIS.ViewModel.CF_TimesSegment).Name }
                        equals 
                        new { TimesSegment = d.Value.Value, d.DictionaryCode } 
                        into gbsta
                        from gicbsta in gbsta.DefaultIfEmpty()
                        join e in RetakePlanTeacherRepository.GetList(x => UserIDList.Contains(x.UserID.Value)) 
                        on b.RetakePlanID equals e.RetakePlanID
                        join f in ClassroomRepository.GetList(x => x.IsConcurrentUse != true) 
                        on a.ClassroomID equals f.ClassroomID
                        join g in DepartmentRepository.Entities 
                        on b.DepartmentID equals g.DepartmentID
                        join s in RetakePlanTeachingSettingRepository.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.RetakePlanID equals s.RetakePlanID
                        select new RetakePlanSettingSchedulingView
                        {
                            DefaultClassName = b.ER_RetakePlan.ClassName,
                            SchedulingID = a.RetakePlanSettingSchedulingID,
                            ID = a.RetakePlanID,
                            CourseCollegeID = g.CollegeID,
                            ClassroomID = a.ClassroomID,
                            ClassroomName = f.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;
        }


        /// <summary>
        /// 教学任务排课判断（教室时间）
        /// </summary>
        /// <param name="CoursesTimeID"></param>
        /// <param name="ClassroomID"></param>
        /// <param name="Weekday"></param>
        /// <param name="SchoolyearID"></param>
        /// <param name="StartWeeklyNum"></param>
        /// <param name="EndWeeklyNum"></param>
        /// <returns></returns>
        public IQueryable<RetakePlanSettingSchedulingView> 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 RetakePlanSettingSchedulingView
                        {
                            DefaultClassName = f.Name,

                        };

            return query;
        }

        /// <summary>
        /// 教学任务排课判断（教室时间、教师时间）
        /// </summary>
        /// <param name="UserIDList"></param>
        /// <param name="CoursesTimeID"></param>
        /// <param name="Weekday"></param>
        /// <param name="SchoolyearID"></param>
        /// <param name="StartWeeklyNum"></param>
        /// <param name="EndWeeklyNum"></param>
        /// <returns></returns>
        public IQueryable<RetakePlanSettingSchedulingView> 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 RetakePlanSettingSchedulingView
                        {
                            DefaultClassName = f.Name,

                        };
            return query;
        }

    }
}