using System; using System.Collections.Generic; using System.Linq; using System.Linq.Expressions; using System.Text; using System.Data.Entity; using EMIS.DataLogic.SystemDAL; using EMIS.ViewModel; using EMIS.DataLogic.Common.CalendarManage; using EMIS.DataLogic.Repositories; using EMIS.Entities; using EMIS.ViewModel.SelectCourse.SelectCourseResult; using EMIS.ViewModel.SelectCourse; using EMIS.ViewModel.Students; namespace EMIS.DataLogic.SelectCourse.SelectCourseResult { public class SelectCourseResultDAL { public FinallyScoreRepository FinallyScoreRepository { get; set; } public StudentRepository StudentRepository { get; set; } public ClassmajorRepository ClassmajorRepository { get; set; } public GrademajorRepository GrademajorRepository { get; set; } public FacultymajorRepository FacultymajorRepository { get; set; } public SpecialtyRepository SpecialtyRepository { get; set; } public DictionaryItemRepository DictionaryItemRepository { get; set; } public SchoolyearRepository schoolyearRepository { get; set; } public SelectCourseTypeSettingRepository SelectCourseTypeSettingRepository { get; set; } public SelectCourseOpenControlSettingRepository SelectCourseOpenControlSettingRepository { get; set; } public Lazy ParamterDAL { get; set; } public Lazy SchoolYearDAL { get; set; } public CampusRepository campusRepository { get; set; } public CollegeRepository collegeRepository { get; set; } public DepartmentRepository departmentRepository { get; set; } public CoursematerialRepository CoursematerialRepository { get; set; } public ExecutableOptionalCourseRepository ExecutableOptionalCourseRepository { get; set; }//限选设定 public ExecutableFreeSelectionCouseRepository ExecutableFreeSelectionCouseRepository { get; set; }//任选设定 public OptionalCoursePlanRepository OptionalCoursePlanRepository { get; set; } //限修计划 public FreeSelectionCourseGradeYearSettingRepository FreeSelectionCourseGradeYearSettingRepository { get; set; } //任选控制表 public ExecutableOptionalCourseTeacherRepository ExecutableOptionalCourseTeacherRepository { get; set; } //限选设定任课老师表 public ExecutableOptionalCourseTeachingModeRepository ExecutableOptionalCourseTeachingModeRepository { get; set; } //限选设定授课方式表 public ExecutableOptionalCourseTeachingSettingRepository ExecutableOptionalCourseTeachingSettingRepository { get; set; } //限选设定教学设置表 public ExecutableFreeSelectionCouseTeacherRepository ExecutableFreeSelectionCouseTeacherRepository { get; set; } //任选课程教师表 public ExecutableFreeSelectionCouseTeachingModeRepository ExecutableFreeSelectionCouseTeachingModeRepository { get; set; } //任选设定授课方式表 public ExecutableFreeSelectionCouseTeachingSettingRepository ExecutableFreeSelectionCouseTeachingSettingRepository { get; set; } //任选设定教学设置表 public ExecutableFreeSelectionCouseSpecialtyRepository ExecutableFreeSelectionCouseSpecialtyRepository { get; set; } public ExecutableFreeSelectionCouseGradeYearRepository ExecutableFreeSelectionCouseGradeYearRepository { get; set; } public ExecutableFreeSelectionCouseSchedulingRepository ExecutableFreeSelectionCouseSchedulingRepository { get; set; } public CoursesTimeRepository coursesTimeRepository { get; set; } public ClassroomRepository ClassroomRepository { get; set; } /// /// 查询选课结果，0为限选，1为任选 /// /// /// /// public IQueryable GetSelectCourseResultViewQueryable(Expression> eocExp, Expression> efsExp) { //1.0 获取限选设定的所有学生数据 var executableOptionalCourseResult = (from eoc in ExecutableOptionalCourseRepository.GetList(eocExp) //from eoct in eoc.EM_ExecutableOptionalCourseTeacher //join a in(ExecutableOptionalCourseTeacherRepository.Entities.Where(x=>x.EM_ExecutableOptionalCourse=)) //join eoct in ExecutableOptionalCourseTeacherRepository.Entities on eoc.ExecutableOptionalCourseID equals eoct.ExecutableOptionalCourseID //into tmp1 //join eoctm in ExecutableOptionalCourseTeachingModeRepository.Entities on eoc.ExecutableOptionalCourseID equals eoctm.ExecutableOptionalCourseID //into tmp //from eoctm1 in tmp.DefaultIfEmpty() //join eocts in ExecutableOptionalCourseTeachingSettingRepository.Entities on eoc.ExecutableOptionalCourseID equals eocts.ExecutableOptionalCourseID //into tmp1 from eocts1 in tmp1.DefaultIfEmpty() select new SelectCourseResultView { ClassroomName = "", ID = eoc.ExecutableOptionalCourseID, StudentTotalNum = eoc.CF_Student.Count(), PeopleNumlower = eoc.PeopleNumlower, PeopleNumlimit = eoc.PeopleNumlimit, SchoolyearID = eoc.SchoolyearID, SchoolYearCode = null, CollegeID = eoc.CF_Grademajor.CF_Facultymajor.CollegeID, DepartmentID = eoc.DepartmentID, DepartmentName = eoc.EM_OptionalCoursePlan.CF_Department.Name, DefaultClassName = eoc.DefaultClassName, CoursematerialID = eoc.EM_OptionalCoursePlan.CoursematerialID, CourseCode = eoc.EM_OptionalCoursePlan.EM_Coursematerial.CourseCode, CourseName = eoc.EM_OptionalCoursePlan.EM_Coursematerial.CourseName, CourseTypeID = eoc.CourseTypeID, //CourseTypeName = "", CourseQualityID = eoc.CourseQualityID, SelectCourseType = 0, TheoryCourse = eoc.EM_ExecutableOptionalCourseTeachingSetting.TheoryCourse ?? 0, Practicehours = eoc.EM_ExecutableOptionalCourseTeachingSetting.Practicehours ?? 0, Trialhours = eoc.EM_ExecutableOptionalCourseTeachingSetting.Trialhours ?? 0, Credit = eoc.EM_ExecutableOptionalCourseTeachingSetting.Credit ?? 0, StartWeeklyNum = eoc.EM_ExecutableOptionalCourseTeachingSetting.StartWeeklyNum ?? 0, EndWeeklyNum = eoc.EM_ExecutableOptionalCourseTeachingSetting.EndWeeklyNum ?? 0, //StartEndWeeklyNum = eoc.EM_ExecutableOptionalCourseTeachingSetting.StartWeeklyNum != null && eoc.EM_ExecutableOptionalCourseTeachingSetting.EndWeeklyNum != null ? eoc.EM_ExecutableOptionalCourseTeachingSetting.StartWeeklyNum + "-" + eoc.EM_ExecutableOptionalCourseTeachingSetting.EndWeeklyNum : "", //TheoryCourse = eocts1.TheoryCourse, //Practicehours = eocts1.Practicehours, //Trialhours = eocts1.Trialhours, //Credit = eocts1.Credit??0, SchoolcodeID = eoc.EM_OptionalCoursePlan.SchoolcodeID, TeacherUserID = null, TeacherName = "",//eoct.CF_Staff.Sys_User.Name, //TeachingModeID = eoctm1.TeachingModeID, RecordStatus = eoc.RecordStatus, IsOpened = eoc.IsOpened.Value, }); //2.0 获取任选设定的所有学生数据 var executableFreeSelectionCouseResult = (from efs in ExecutableFreeSelectionCouseRepository.GetList(efsExp) 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 efs.ExecutableFreeSelectionCouseID equals Scheduling.ExecutableFreeSelectionCouseID into lj from Scheduling in lj.DefaultIfEmpty() //from efsct in efs.EM_ExecutableFreeSelectionCouseTeacher //join efsct in ExecutableFreeSelectionCouseTeacherRepository.Entities on efs.ExecutableFreeSelectionCouseID equals efsct.ExecutableFreeSelectionCouseID //into tmp1 from efsct in tmp1.DefaultIfEmpty() //join efsctm in ExecutableFreeSelectionCouseTeachingModeRepository.Entities on efs.ExecutableFreeSelectionCouseID equals efsctm.ExecutableFreeSelectionCouseID //into tmp //from efsctm1 in tmp.DefaultIfEmpty() //join efscts in ExecutableFreeSelectionCouseTeachingSettingRepository.Entities on efs.ExecutableFreeSelectionCouseID equals efscts.ExecutableFreeSelectionCouseID //into tmp1 from efscts1 in tmp1.DefaultIfEmpty() select new SelectCourseResultView { ClassroomName = Scheduling.ClassroomName, ID = efs.ExecutableFreeSelectionCouseID, StudentTotalNum = efs.CF_Student.Count(), PeopleNumlower = efs.PeopleNumlower, PeopleNumlimit = efs.PeopleNumlimit, SchoolyearID = efs.SchoolyearID, SchoolYearCode = null, CollegeID = efs.CF_Department.CollegeID, DepartmentID = efs.DepartmentID, DepartmentName = efs.EM_FreeSelectionCouse.CF_Department.Name, DefaultClassName = efs.DefaultClassName, CoursematerialID = efs.EM_FreeSelectionCouse.CoursematerialID, CourseCode = efs.EM_FreeSelectionCouse.EM_Coursematerial.CourseCode, CourseName = efs.EM_FreeSelectionCouse.EM_Coursematerial.CourseName, CourseTypeID = efs.CourseTypeID, //CourseTypeName = "", CourseQualityID = efs.CourseQualityID, SelectCourseType = 1, TheoryCourse = efs.EM_ExecutableFreeSelectionCouseTeachingSetting.TheoryCourse ?? 0, Practicehours = efs.EM_ExecutableFreeSelectionCouseTeachingSetting.Practicehours ?? 0, Trialhours = efs.EM_ExecutableFreeSelectionCouseTeachingSetting.Trialhours ?? 0, Credit = efs.EM_ExecutableFreeSelectionCouseTeachingSetting.Credit ?? 0, StartWeeklyNum = efs.EM_ExecutableFreeSelectionCouseTeachingSetting.StartWeeklyNum ?? 0, EndWeeklyNum = efs.EM_ExecutableFreeSelectionCouseTeachingSetting.EndWeeklyNum ?? 0, //StartEndWeeklyNum = efs.EM_ExecutableFreeSelectionCouseTeachingSetting.StartWeeklyNum != null && efs.EM_ExecutableFreeSelectionCouseTeachingSetting.EndWeeklyNum != null ? efs.EM_ExecutableFreeSelectionCouseTeachingSetting.StartWeeklyNum + "-" + efs.EM_ExecutableFreeSelectionCouseTeachingSetting.EndWeeklyNum : "", //TheoryCourse = efscts1.TheoryCourse, //Practicehours = efscts1.Practicehours, //Trialhours = efscts1.Trialhours, //Credit = efscts1.Credit??0, SchoolcodeID = efs.EM_FreeSelectionCouse.SchoolcodeID, TeacherUserID = null, TeacherName = "",//efsct.CF_Staff.Sys_User.Name, //TeachingModeID = efsctm1.TeachingModeID, RecordStatus = efs.RecordStatus, IsOpened = true, }); //3.0 合并限选/任选 var result = from a in executableOptionalCourseResult.Concat(executableFreeSelectionCouseResult) join s in schoolyearRepository.Entities on a.SchoolyearID equals s.SchoolyearID //join dis in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(CF_CourseType).Name) on a.CourseTypeID equals dis.Value into ddis //from edis in ddis.DefaultIfEmpty() //join c in CoursematerialRepository.Entities on a.CourseCode equals c.CourseCode select new SelectCourseResultView { ClassroomName = a.ClassroomName, ID = a.ID, StudentTotalNum = a.StudentTotalNum, PeopleNumlower = a.PeopleNumlower, PeopleNumlimit = a.PeopleNumlimit, SchoolyearID = a.SchoolyearID, SchoolYearCode = s.Code, CollegeID = a.CollegeID, DepartmentID = a.DepartmentID, DepartmentName = a.DepartmentName, DefaultClassName = a.DefaultClassName, CoursematerialID = a.CoursematerialID, CourseCode = a.CourseCode, CourseName = a.CourseName, CourseTypeID = a.CourseTypeID, //CourseTypeName = a.CourseTypeName, CourseQualityID = a.CourseQualityID, SelectCourseType = a.SelectCourseType, TheoryCourse = a.TheoryCourse ?? 0, Practicehours = a.Practicehours ?? 0, Trialhours = a.Trialhours ?? 0, Credit = a.Credit ?? 0, StartWeeklyNum = a.StartWeeklyNum ?? 0, EndWeeklyNum = a.EndWeeklyNum ?? 0, //StartEndWeeklyNum = a.StartEndWeeklyNum, SchoolcodeID = a.SchoolcodeID, TeacherUserID = null, TeacherName = a.TeacherName, //TeachingModeID = a.TeachingModeID, RecordStatus = a.RecordStatus, IsOpened = a.IsOpened, //TeachingModeName = GetSelectCourseResultTeachingMode1(a.ID) }; return result; } public IQueryable GetSelectCourseResultTeacher(Expression> eocExp, Expression> efsExp) { //1.0 获取限选设定数据 var executableOptionalCourseResult = (from eoc in ExecutableOptionalCourseRepository.Entities from eoct in eoc.EM_ExecutableOptionalCourseTeacher group eoc by new { ID = eoc.ExecutableOptionalCourseID, SchoolyearID = eoc.SchoolyearID, CourseTypeID = eoc.CourseTypeID, //eoct.CF_Staff.UserID, eoct.CF_Staff.Sys_User.Name, RecordStatus = eoc.RecordStatus, } into g select new SelectCourseResultView { ID = g.Key.ID, SchoolyearID = g.Key.SchoolyearID, CourseTypeID = g.Key.CourseTypeID, SelectCourseType = 0, //TeacherUserID = g.Key.UserID, TeacherName = g.Key.Name, RecordStatus = g.Key.RecordStatus, }); //2.0 获取任选设定数据 var executableFreeSelectionCouseResult = executableOptionalCourseResult.Concat( from efs in ExecutableFreeSelectionCouseRepository.Entities from efsct in efs.EM_ExecutableFreeSelectionCouseTeacher group efs by new { ID = efs.ExecutableFreeSelectionCouseID, SchoolyearID = efs.SchoolyearID, CourseTypeID = efs.CourseTypeID, //efsct.CF_Staff.UserID, efsct.CF_Staff.Sys_User.Name, RecordStatus = efs.RecordStatus, } into g select new SelectCourseResultView { ID = g.Key.ID, SchoolyearID = g.Key.SchoolyearID, CourseTypeID = g.Key.CourseTypeID, SelectCourseType = 1, //TeacherUserID = g.Key.UserID, TeacherName = g.Key.Name, RecordStatus = g.Key.RecordStatus, }); return executableFreeSelectionCouseResult; } public string GetSelectCourseResultTeachingMode1(Guid? ID) { var query = (from eoc in ExecutableOptionalCourseRepository.Entities.Where(x => x.ExecutableOptionalCourseID == ID) from eoct in eoc.EM_ExecutableOptionalCourseTeachingMode select new { //ID = eoc.ExecutableOptionalCourseID, //SchoolyearID = g.Key.SchoolyearID, //CourseTypeID = g.Key.CourseTypeID, //SelectCourseType = 0, //TeacherUserID = g.Key.UserID, TeachingModeID = eoct.TeachingModeID }).ToList(); string a = string.Join(",", query); return a; } public IQueryable GetSelectCourseResultTeachingMode(Expression> eocExp, Expression> efsExp) { //1.0 获取限选设定数据 var executableOptionalCourseResult = (from eoc in ExecutableOptionalCourseRepository.Entities from eoct in eoc.EM_ExecutableOptionalCourseTeachingMode group eoc by new { ID = eoc.ExecutableOptionalCourseID, //SchoolyearID = eoc.SchoolyearID, //CourseTypeID = eoc.CourseTypeID, TeachingModeID = eoct.TeachingModeID, RecordStatus = eoc.RecordStatus, } into g select new SelectCourseResultView { ID = g.Key.ID, //SchoolyearID = g.Key.SchoolyearID, //CourseTypeID = g.Key.CourseTypeID, SelectCourseType = 0, //TeacherUserID = g.Key.UserID, TeachingModeID = g.Key.TeachingModeID, RecordStatus = g.Key.RecordStatus, }); //2.0 获取任选设定数据 var executableFreeSelectionCouseResult = executableOptionalCourseResult.Concat( from efs in ExecutableFreeSelectionCouseRepository.Entities from efsct in efs.EM_ExecutableFreeSelectionCouseTeachingMode group efs by new { ID = efs.ExecutableFreeSelectionCouseID, //SchoolyearID = efs.SchoolyearID, //CourseTypeID = efs.CourseTypeID, //efsct.CF_Staff.UserID, //efsct.CF_Staff.Sys_User.Name, TeachingModeID = efsct.TeachingModeID, RecordStatus = efs.RecordStatus, } into g select new SelectCourseResultView { ID = g.Key.ID, //SchoolyearID = g.Key.SchoolyearID, //CourseTypeID = g.Key.CourseTypeID, SelectCourseType = 1, //TeacherUserID = g.Key.UserID, //TeacherName = g.Key.Name, TeachingModeID = g.Key.TeachingModeID, RecordStatus = g.Key.RecordStatus, }); return executableFreeSelectionCouseResult; } public Guid GetSelectCourseSchoolyear() { var selectCourseSchoolyear = ParamterDAL.Value.ParameterRepository.GetSingle(x => x.ParameterTypeID == (int)CF_ParameterType.SelectCourseSchoolyear); if (selectCourseSchoolyear != null && selectCourseSchoolyear.Value != null) { return new Guid(selectCourseSchoolyear.Value); } else { return this.SchoolYearDAL.Value.GetSchoolYearQueryable(x => x.IsCurrent).FirstOrDefault().SchoolYearID.Value; } } public IQueryable GetStudentSelectCourseResultView(Guid userID) { DateTime nowTime = DateTime.Now; var selectCourseSchoolyearID = this.GetSelectCourseSchoolyear(); var student = GetBaseStudentViewQueryable(userID, selectCourseSchoolyearID).FirstOrDefault(); List OptionalCourseStatus = new List { (int)EM_SelectCourseResultStatus.Opened }; var q = ( from s in StudentRepository.GetList(x => x.UserID == userID) join eoc in ExecutableOptionalCourseRepository.Entities on s.CF_Classmajor.GrademajorID equals eoc.GrademajorID join eocts in ExecutableOptionalCourseTeachingSettingRepository.Entities on eoc.ExecutableOptionalCourseID equals eocts.ExecutableOptionalCourseID into tmp from ecots1 in tmp.DefaultIfEmpty() join depart in departmentRepository.Entities on eoc.DepartmentID equals depart.DepartmentID into tmp2 from de1 in tmp2.DefaultIfEmpty() join college in collegeRepository.Entities on de1.CollegeID equals college.CollegeID into tmp3 from co1 in tmp3.DefaultIfEmpty() join campus in campusRepository.Entities on co1.CampusID equals campus.CampusID into tmp4 from ca1 in tmp4.DefaultIfEmpty() from eoccc in eoc.CF_Classmajor.Where(x => x.ClassmajorID == s.ClassmajorID).DefaultIfEmpty() //from efsc in ExecutableFreeSelectionCouseRepository.Entities join scts in SelectCourseTypeSettingRepository .GetList(x => x.SchoolyearID == selectCourseSchoolyearID) on (int)EM_SelectCourseType.OptionalCourse equals scts.SelectCourseTypeID into dscts from scts in dscts.DefaultIfEmpty() join scocs in SelectCourseOpenControlSettingRepository .GetList(x => x.SchoolyearID == selectCourseSchoolyearID) on eoc.GrademajorID equals scocs.GrademajorID into dscocs from escocs in dscocs.DefaultIfEmpty() from eoccs in eoc.CF_Student.Where(x => x.UserID == userID).DefaultIfEmpty() join eoccs1 in ( from eoc in ExecutableOptionalCourseRepository.Entities from eoccs in eoc.CF_Student group eoc by eoc.ExecutableOptionalCourseID into g select new { ExecutableOptionalCourseID = g.Key, StudentCount = g.Count() } ) on eoc.ExecutableOptionalCourseID equals eoccs1.ExecutableOptionalCourseID into deoccs1 from eeoccs1 in deoccs1.DefaultIfEmpty() where eoc.SchoolyearID == selectCourseSchoolyearID //&& eoc.IsOpened == true && OptionalCourseStatus.Contains(eoc.RecordStatus) && eoccc.ClassmajorID != null //&& scts.StartTime <= nowTime && scts.EndTime >= nowTime //&& ((escocs.SelectCourseOpenControlSettingID == null) || (escocs.StartTime <= nowTime && escocs.EndTime >= nowTime)) //|| eoccs.UserID != null select new StudentSelectCourseView { ClassroomName = "", ID = eoc.ExecutableOptionalCourseID, CampusName = ca1.Name, DepartmentID = eoc.DepartmentID, DepartmentName = de1.Name, StartWeeklyNum = ecots1.StartWeeklyNum ?? 0, EndWeeklyNum = ecots1.EndWeeklyNum ?? 0, StartTime=scts.StartTime, EndTime=scts.EndTime, //IsNeedMaterial = efsc.IsNeedMaterial ?? false, SelectCourseID = eoc.ExecutableOptionalCourseID, CoursematerialID = eoc.EM_OptionalCoursePlan.CoursematerialID ?? Guid.Empty, CourseName = eoc.EM_OptionalCoursePlan.EM_Coursematerial.CourseName, Credit = eoc.EM_ExecutableOptionalCourseTeachingSetting.Credit, ClassName = eoc.DefaultClassName, SelectCourseTypeID = (int)EM_SelectCourseType.OptionalCourse, SelectCourseTypeOrderID = 0, SelectCourseTypeName = "限选课", PeopleNumlimit = eoc.PeopleNumlimit, StudentCount = (eeoccs1.ExecutableOptionalCourseID == null ? 0 : eeoccs1.StudentCount), IsSelected = (eoccs.UserID != null) } ); //任选 q = q.Concat(from efsc in ExecutableFreeSelectionCouseRepository.Entities join scts in SelectCourseTypeSettingRepository .GetList(x => x.SchoolyearID == selectCourseSchoolyearID) on (int)EM_SelectCourseType.FreeSelectionCouse equals scts.SelectCourseTypeID into dscts from scts in dscts.DefaultIfEmpty() from efsccs in efsc.CF_Student.Where(x => x.UserID == userID).DefaultIfEmpty() join efsccs1 in ( from efsc in ExecutableFreeSelectionCouseRepository.Entities from efsccs in efsc.CF_Student group efsc by efsc.ExecutableFreeSelectionCouseID into g select new { ExecutableFreeSelectionCouseID = g.Key, StudentCount = g.Count() } ) on efsc.ExecutableFreeSelectionCouseID equals efsccs1.ExecutableFreeSelectionCouseID into defsccs1 from eefsccs1 in defsccs1.DefaultIfEmpty() join eocts in ExecutableFreeSelectionCouseTeachingSettingRepository.Entities on efsc.ExecutableFreeSelectionCouseID equals eocts.ExecutableFreeSelectionCouseID into tmp from ecots1 in tmp.DefaultIfEmpty() join de in departmentRepository.Entities on efsc.DepartmentID equals de.DepartmentID into tmp2 from de1 in tmp2.DefaultIfEmpty() join college in collegeRepository.Entities on de1.CollegeID equals college.CollegeID into tmp3 from co1 in tmp3.DefaultIfEmpty() join campus in campusRepository.Entities on co1.CampusID equals campus.CampusID into tmp4 from ca1 in tmp4.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 efsc.ExecutableFreeSelectionCouseID equals Scheduling.ExecutableFreeSelectionCouseID into lj from Scheduling in lj.DefaultIfEmpty() //任选设定可选专业表 join efscs in ExecutableFreeSelectionCouseSpecialtyRepository.Entities on new { efsc.ExecutableFreeSelectionCouseID, student.SpecialtyID } equals new { ExecutableFreeSelectionCouseID = efscs.ExecutableFreeSelectionCouseID.Value, SpecialtyID = efscs.SpecialtyID } into tmp1 from efscs in tmp1.DefaultIfEmpty() //任选设定可选年级表 join efscgy in ExecutableFreeSelectionCouseGradeYearRepository.Entities on new { ExecutableFreeSelectionCouseID = efsc.ExecutableFreeSelectionCouseID, student.GradeYear } equals new { ExecutableFreeSelectionCouseID = efscgy.ExecutableFreeSelectionCouseID.Value, efscgy.GradeYear } into tmp5 from efscgy in tmp5.DefaultIfEmpty() where efsc.SchoolyearID == selectCourseSchoolyearID && OptionalCourseStatus.Contains(efsc.RecordStatus) //&& efsc.IsOpened == true //&& ((scts.SelectCourseTypeSettingID == null) || (scts.StartTime <= nowTime && scts.EndTime >= nowTime)) && (efsc.EM_ExecutableFreeSelectionCouseSpecialty.Count == 0 || efscs.SpecialtyID == student.SpecialtyID) && (efsc.EM_ExecutableFreeSelectionCouseGradeYear.Count == 0 || efscgy.GradeYear == student.GradeYear) //|| efsccs.UserID != null select new StudentSelectCourseView { ClassroomName = Scheduling.ClassroomName, ID = efsc.ExecutableFreeSelectionCouseID, CampusName = ca1.Name, DepartmentID = efsc.DepartmentID, DepartmentName = de1.Name, StartWeeklyNum = ecots1.StartWeeklyNum ?? 0, EndWeeklyNum = ecots1.EndWeeklyNum ?? 0, StartTime = scts.StartTime, EndTime = scts.EndTime, //IsNeedMaterial = efsc.IsNeedMaterial ?? false, SelectCourseID = efsc.ExecutableFreeSelectionCouseID, CoursematerialID = efsc.EM_FreeSelectionCouse.CoursematerialID ?? Guid.Empty, CourseName = efsc.EM_FreeSelectionCouse.EM_Coursematerial.CourseName, Credit = efsc.EM_ExecutableFreeSelectionCouseTeachingSetting.Credit, ClassName = efsc.DefaultClassName, SelectCourseTypeID = (int)EM_SelectCourseType.FreeSelectionCouse, SelectCourseTypeOrderID = (int)EM_SelectCourseType.FreeSelectionCouse, SelectCourseTypeName = "任选课", PeopleNumlimit = efsc.PeopleNumlimit, StudentCount = (eefsccs1.ExecutableFreeSelectionCouseID == null ? 0 : eefsccs1.StudentCount), IsSelected = (efsccs.UserID != null) }); //if (student.InSchoolStatusID != (int)EMIS.ViewModel.CF_InschoolStatus.InSchool)//不是在校状态的直接null，不显示 // throw new Exception("不是在校状态不能选课"); return q; } public IQueryable GetStudentSelectedCourseResultView(Expression> eocExp, Expression> efsExp) { //1.0 获取限选设定的所有学生数据 var executableOptionalCourseResult = (from eoc in ExecutableOptionalCourseRepository.GetList(eocExp) //from eoct in eoc.EM_ExecutableOptionalCourseTeacher //join a in(ExecutableOptionalCourseTeacherRepository.Entities.Where(x=>x.EM_ExecutableOptionalCourse=)) //join eoct in ExecutableOptionalCourseTeacherRepository.Entities on eoc.ExecutableOptionalCourseID equals eoct.ExecutableOptionalCourseID //into tmp1 //join eoctm in ExecutableOptionalCourseTeachingModeRepository.Entities on eoc.ExecutableOptionalCourseID equals eoctm.ExecutableOptionalCourseID //into tmp //from eoctm1 in tmp.DefaultIfEmpty() //join eocts in ExecutableOptionalCourseTeachingSettingRepository.Entities on eoc.ExecutableOptionalCourseID equals eocts.ExecutableOptionalCourseID //into tmp1 from eocts1 in tmp1.DefaultIfEmpty() select new StudentSelectCourseView { ID = eoc.ExecutableOptionalCourseID, //StudentTotalNum = eoc.CF_Student.Count(), //PeopleNumlower = eoc.PeopleNumlower, //PeopleNumlimit = eoc.PeopleNumlimit, SchoolyearID = eoc.SchoolyearID, SchoolYearCode = null, CampusName = eoc.CF_Grademajor.CF_Facultymajor.CF_College.CF_Campus.Name, CollegeID = eoc.CF_Grademajor.CF_Facultymajor.CollegeID, DefaultClassName = eoc.DefaultClassName, CoursematerialID = eoc.EM_OptionalCoursePlan.CoursematerialID, CourseCode = eoc.EM_OptionalCoursePlan.EM_Coursematerial.CourseCode, CourseName = eoc.EM_OptionalCoursePlan.EM_Coursematerial.CourseName, CourseTypeID = eoc.CourseTypeID, //CourseTypeName = "", CourseQualityID = eoc.CourseQualityID, SelectCourseTypeID = (int)EM_SelectCourseType.OptionalCourse, SelectCourseTypeName = "限选课", TheoryCourse = eoc.EM_ExecutableOptionalCourseTeachingSetting.TheoryCourse ?? 0, Practicehours = eoc.EM_ExecutableOptionalCourseTeachingSetting.Practicehours ?? 0, Trialhours = eoc.EM_ExecutableOptionalCourseTeachingSetting.Trialhours ?? 0, Credit = eoc.EM_ExecutableOptionalCourseTeachingSetting.Credit ?? 0, WeeklyNum = eoc.EM_ExecutableOptionalCourseTeachingSetting.WeeklyNum, StartWeeklyNum = eoc.EM_ExecutableOptionalCourseTeachingSetting.StartWeeklyNum ?? 0, EndWeeklyNum = eoc.EM_ExecutableOptionalCourseTeachingSetting.EndWeeklyNum ?? 0, //TheoryCourse = eocts1.TheoryCourse, //Practicehours = eocts1.Practicehours, //Trialhours = eocts1.Trialhours, //Credit = eocts1.Credit??0, //CoursesTimeID = null, //CoursesTimeName = "", //ClassroomID = null, ClassroomName = "", SchoolcodeID = eoc.EM_OptionalCoursePlan.SchoolcodeID, TeacherUserID = null, TeacherName = "",//eoct.CF_Staff.Sys_User.Name, //TeachingModeID = eoctm1.TeachingModeID, RecordStatus = eoc.RecordStatus, IsOpened = eoc.IsOpened.Value, }); //2.0 获取任选设定的所有学生数据 var executableFreeSelectionCouseResult = (from efs in ExecutableFreeSelectionCouseRepository.GetList(efsExp) 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 efs.ExecutableFreeSelectionCouseID equals Scheduling.ExecutableFreeSelectionCouseID into lj from Scheduling in lj.DefaultIfEmpty() //from efsct in efs.EM_ExecutableFreeSelectionCouseTeacher //join efsct in ExecutableFreeSelectionCouseTeacherRepository.Entities on efs.ExecutableFreeSelectionCouseID equals efsct.ExecutableFreeSelectionCouseID //into tmp1 from efsct in tmp1.DefaultIfEmpty() //join efsctm in ExecutableFreeSelectionCouseTeachingModeRepository.Entities on efs.ExecutableFreeSelectionCouseID equals efsctm.ExecutableFreeSelectionCouseID //into tmp //from efsctm1 in tmp.DefaultIfEmpty() //join efscts in ExecutableFreeSelectionCouseTeachingSettingRepository.Entities on efs.ExecutableFreeSelectionCouseID equals efscts.ExecutableFreeSelectionCouseID //into tmp1 from efscts1 in tmp1.DefaultIfEmpty() select new StudentSelectCourseView { ID = efs.ExecutableFreeSelectionCouseID, //StudentTotalNum = efs.CF_Student.Count(), //PeopleNumlower = efs.PeopleNumlower, //PeopleNumlimit = efs.PeopleNumlimit, SchoolyearID = efs.SchoolyearID, SchoolYearCode = null, CampusName = efs.CF_Department.CF_College.CF_Campus.Name, CollegeID = efs.CF_Department.CollegeID, DefaultClassName = efs.DefaultClassName, CoursematerialID = efs.EM_FreeSelectionCouse.CoursematerialID, CourseCode = efs.EM_FreeSelectionCouse.EM_Coursematerial.CourseCode, CourseName = efs.EM_FreeSelectionCouse.EM_Coursematerial.CourseName, CourseTypeID = efs.CourseTypeID, //CourseTypeName = "", CourseQualityID = efs.CourseQualityID, SelectCourseTypeID = (int)EM_SelectCourseType.FreeSelectionCouse, SelectCourseTypeName = "任选课", TheoryCourse = efs.EM_ExecutableFreeSelectionCouseTeachingSetting.TheoryCourse ?? 0, Practicehours = efs.EM_ExecutableFreeSelectionCouseTeachingSetting.Practicehours ?? 0, Trialhours = efs.EM_ExecutableFreeSelectionCouseTeachingSetting.Trialhours ?? 0, Credit = efs.EM_ExecutableFreeSelectionCouseTeachingSetting.Credit ?? 0, WeeklyNum = efs.EM_ExecutableFreeSelectionCouseTeachingSetting.WeeklyNum, StartWeeklyNum = efs.EM_ExecutableFreeSelectionCouseTeachingSetting.StartWeeklyNum ?? 0, EndWeeklyNum = efs.EM_ExecutableFreeSelectionCouseTeachingSetting.EndWeeklyNum ?? 0, //TheoryCourse = efscts1.TheoryCourse, //Practicehours = efscts1.Practicehours, //Trialhours = efscts1.Trialhours, //Credit = efscts1.Credit??0, //CoursesTimeID = Scheduling.CoursesTimeID, //CoursesTimeName = Scheduling.EM_CoursesTime.StartHour.ToString() + ":" + se.EM_CoursesTime.StartMinutes.ToString() + "-" + se.EM_CoursesTime.EndHour.ToString() + ":" + se.EM_CoursesTime.EndMinutes.ToString(), //ClassroomID = Scheduling.ClassroomID, ClassroomName = Scheduling.ClassroomName, SchoolcodeID = efs.EM_FreeSelectionCouse.SchoolcodeID, TeacherUserID = null, TeacherName = "",//efsct.CF_Staff.Sys_User.Name, //TeachingModeID = efsctm1.TeachingModeID, RecordStatus = efs.RecordStatus, IsOpened = true, }); //3.0 合并限选/任选 var result = from a in executableOptionalCourseResult.Concat(executableFreeSelectionCouseResult) join s in schoolyearRepository.Entities on a.SchoolyearID equals s.SchoolyearID //join dis in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(CF_CourseType).Name) on a.CourseTypeID equals dis.Value into ddis //from edis in ddis.DefaultIfEmpty() //join c in CoursematerialRepository.Entities on a.CourseCode equals c.CourseCode select new StudentSelectCourseView { ID = a.ID, //StudentTotalNum = a.StudentTotalNum, //PeopleNumlower = a.PeopleNumlower, //PeopleNumlimit = a.PeopleNumlimit, SchoolyearID = a.SchoolyearID, SchoolYearCode = s.Code, CampusName = a.CampusName, CollegeID = a.CollegeID, DefaultClassName = a.DefaultClassName, CoursematerialID = a.CoursematerialID, CourseCode = a.CourseCode, CourseName = a.CourseName, CourseTypeID = a.CourseTypeID, //CourseTypeName = a.CourseTypeName, CourseQualityID = a.CourseQualityID, SelectCourseTypeID = a.SelectCourseTypeID, SelectCourseTypeName = a.SelectCourseTypeName, TheoryCourse = a.TheoryCourse ?? 0, Practicehours = a.Practicehours ?? 0, Trialhours = a.Trialhours ?? 0, Credit = a.Credit ?? 0, WeeklyNum = a.WeeklyNum, StartWeeklyNum = a.StartWeeklyNum ?? 0, EndWeeklyNum = a.EndWeeklyNum ?? 0, //CoursesTimeID = a.CoursesTimeID, //CoursesTimeName = a.CoursesTimeName, //ClassroomID = a.ClassroomID, ClassroomName = a.ClassroomName, SchoolcodeID = a.SchoolcodeID, TeacherUserID = null, TeacherName = a.TeacherName, //TeachingModeID = a.TeachingModeID, RecordStatus = a.RecordStatus, IsOpened = a.IsOpened, //TeachingModeName = GetSelectCourseResultTeachingMode1(a.ID) }; return result; } public IQueryable GetStudentSelectCourseTeacherResultView(Guid userID) { DateTime nowTime = DateTime.Now; Guid selectCourseSchoolyearID = this.GetSelectCourseSchoolyear(); List OptionalCourseStatus = new List { (int)EM_SelectCourseResultStatus.Opened }; var studentOpenControl = (from s in StudentRepository.GetList(x => x.UserID == userID) join scocs in SelectCourseOpenControlSettingRepository .GetList(x => x.SchoolyearID == selectCourseSchoolyearID) on s.CF_Classmajor.GrademajorID equals scocs.GrademajorID select scocs).FirstOrDefault(); var q = (from s in StudentRepository.GetList(x => x.UserID == userID) join eoc in ExecutableOptionalCourseRepository.Entities on s.CF_Classmajor.GrademajorID equals eoc.GrademajorID from eoccc in eoc.CF_Classmajor.Where(x => x.ClassmajorID == s.ClassmajorID).DefaultIfEmpty() join scts in SelectCourseTypeSettingRepository .GetList(x => x.SchoolyearID == selectCourseSchoolyearID) on (int)EM_SelectCourseType.OptionalCourse equals scts.SelectCourseTypeID join scocs in SelectCourseOpenControlSettingRepository .GetList(x => x.SchoolyearID == selectCourseSchoolyearID) on eoc.GrademajorID equals scocs.GrademajorID into dscocs from escocs in dscocs.DefaultIfEmpty() from eoccs in eoc.CF_Student.Where(x => x.UserID == userID).DefaultIfEmpty() from eoct in eoc.EM_ExecutableOptionalCourseTeacher.Where(x => x.UserID != null) where eoc.SchoolyearID == selectCourseSchoolyearID //&& eoc.IsOpened == true && OptionalCourseStatus.Contains(eoc.RecordStatus) && eoccc.ClassmajorID != null && scts.StartTime <= nowTime && scts.EndTime >= nowTime && ((escocs.SelectCourseOpenControlSettingID == null) || (escocs.StartTime <= nowTime && escocs.EndTime >= nowTime)) || eoccs.UserID != null group eoc by new { SelectCourseID = eoc.ExecutableOptionalCourseID, eoct.CF_Staff.UserID, eoct.CF_Staff.Sys_User.Name } into g select new StudentCourseTeacherView { SelectCourseID = g.Key.SelectCourseID, UserID = g.Key.UserID, Name = g.Key.Name }); q = q.Concat(from efsc in ExecutableFreeSelectionCouseRepository.Entities join scts in SelectCourseTypeSettingRepository .GetList(x => x.SchoolyearID == selectCourseSchoolyearID) on (int)EM_SelectCourseType.FreeSelectionCouse equals scts.SelectCourseTypeID from efsccs in efsc.CF_Student.Where(x => x.UserID == userID).DefaultIfEmpty() from efsct in efsc.EM_ExecutableFreeSelectionCouseTeacher.Where(x => x.UserID != null) where efsc.SchoolyearID == selectCourseSchoolyearID //&& efsc.IsOpened == true && ((scts.SelectCourseTypeSettingID == null) || (scts.StartTime <= nowTime && scts.EndTime >= nowTime)) || efsccs.UserID != null group efsc by new { SelectCourseID = efsc.ExecutableFreeSelectionCouseID, efsct.CF_Staff.UserID, efsct.CF_Staff.Sys_User.Name } into g select new StudentCourseTeacherView { SelectCourseID = g.Key.SelectCourseID, UserID = g.Key.UserID, Name = g.Key.Name }); return q; } public StudentSelectCourseBaseView GetStudentSelectCourseBaseView(Guid userID) { Guid selectCourseSchoolyearID = this.GetSelectCourseSchoolyear(); var q = (from s in StudentRepository.GetList(x => x.UserID == userID) from eoc in s.EM_ExecutableOptionalCourse.Where(x => x.RecordStatus != (int)EMIS.ViewModel.EM_SelectCourseResultStatus.CancleCreate && x.SchoolyearID == selectCourseSchoolyearID) group eoc by new { s.InSchoolStatusID, s.UserID, s.Sys_User.Name, s.ClassmajorID, ClassmajorName = s.CF_Classmajor.Name, s.CF_Classmajor.GrademajorID, GrademajorName = s.CF_Classmajor.CF_Grademajor.Name, GrademajorYear = s.CF_Classmajor.CF_Grademajor.GradeID } into g select new StudentSelectCourseBaseView { UserID = g.Key.UserID, Name = g.Key.Name, ClassmajorID = g.Key.ClassmajorID, ClassmajorName = g.Key.ClassmajorName, GrademajorID = g.Key.GrademajorID, GrademajorName = g.Key.GrademajorName, GrademajorYear = g.Key.GrademajorYear, OptionalTotalSelectCount = g.Count(), OptionalTotalCredit = g.Sum(x => x.EM_ExecutableOptionalCourseTeachingSetting.Credit), FreeSelectCourseTotalSelectCount = 0, FreeSelectCourseTotalCredit = 0, InSchoolStatusID = g.Key.InSchoolStatusID }).Concat( from s in StudentRepository.GetList(x => x.UserID == userID) from efsc in s.EM_ExecutableFreeSelectionCouse.Where(x => x.RecordStatus != (int)EMIS.ViewModel.EM_SelectCourseResultStatus.CancleCreate && x.SchoolyearID == selectCourseSchoolyearID) group efsc by new { s.InSchoolStatusID, s.UserID, s.Sys_User.Name, s.ClassmajorID, ClassmajorName = s.CF_Classmajor.Name, s.CF_Classmajor.GrademajorID, GrademajorName = s.CF_Classmajor.CF_Grademajor.Name, GrademajorYear = s.CF_Classmajor.CF_Grademajor.GradeID } into g select new StudentSelectCourseBaseView { UserID = g.Key.UserID, Name = g.Key.Name, ClassmajorID = g.Key.ClassmajorID, ClassmajorName = g.Key.ClassmajorName, GrademajorID = g.Key.GrademajorID, GrademajorName = g.Key.GrademajorName, GrademajorYear = g.Key.GrademajorYear, OptionalTotalSelectCount = 0, OptionalTotalCredit = 0, FreeSelectCourseTotalSelectCount = g.Count(), FreeSelectCourseTotalCredit = g.Sum(x => x.EM_ExecutableFreeSelectionCouseTeachingSetting.Credit), InSchoolStatusID = g.Key.InSchoolStatusID }); var groupQuery = (from s in StudentRepository.GetList(x => x.UserID == userID) join sc in q on s.UserID equals sc.UserID into dsc from esc in dsc.DefaultIfEmpty() group esc by new { s.InSchoolStatusID, s.UserID, s.Sys_User.Name, s.ClassmajorID, ClassmajorName = s.CF_Classmajor.Name, s.CF_Classmajor.GrademajorID, GrademajorName = s.CF_Classmajor.CF_Grademajor.Name, GrademajorYear = s.CF_Classmajor.CF_Grademajor.GradeID, } into g select new StudentSelectCourseBaseView { UserID = g.Key.UserID, Name = g.Key.Name, ClassmajorID = g.Key.ClassmajorID, ClassmajorName = g.Key.ClassmajorName, GrademajorID = g.Key.GrademajorID, GrademajorName = g.Key.GrademajorName, GrademajorYear = g.Key.GrademajorYear, OptionalTotalSelectCount = g.Sum(x => x.OptionalTotalSelectCount ?? 0), OptionalTotalCredit = g.Sum(x => x.OptionalTotalCredit ?? 0), FreeSelectCourseTotalSelectCount = g.Sum(x => x.FreeSelectCourseTotalSelectCount ?? 0), FreeSelectCourseTotalCredit = g.Sum(x => x.FreeSelectCourseTotalCredit ?? 0), InSchoolStatusID = g.Key.InSchoolStatusID }); return groupQuery.FirstOrDefault(); } public IQueryable GetBaseStudentViewQueryable(Guid UserID, Guid selectCourseSchoolyearID) { var Schoolyear = schoolyearRepository.Entities.Where(x => x.SchoolyearID == selectCourseSchoolyearID).FirstOrDefault(); var query = from stud in StudentRepository.GetList(x => x.UserID == UserID) join clas in ClassmajorRepository.Entities on stud.ClassmajorID equals clas.ClassmajorID join grad in GrademajorRepository.Entities on clas.GrademajorID equals grad.GrademajorID join faul in FacultymajorRepository.Entities on grad.FacultymajorID equals faul.FacultymajorID join spec in SpecialtyRepository.Entities on new { faul.StandardID, faul.LearningformID, faul.LearnSystem, faul.EducationID } equals new { spec.StandardID, spec.LearningformID, spec.LearnSystem, spec.EducationID } join startYear in schoolyearRepository.Entities on new { SchoolyearID = grad.GradeID.Value, SchoolcodeID = grad.SemesterID.Value } equals new { SchoolyearID = startYear.Years, startYear.SchoolcodeID } select new BaseStudentView { UserID = stud.UserID, SpecialtyID = spec.SpecialtyID, FacultymajorID = faul.FacultymajorID, GrademajorID = grad.GrademajorID, ClassmajorID = clas.ClassmajorID, GradeYear = (Schoolyear.Value - startYear.Value - (grad.SemesterID == Schoolyear.SchoolcodeID ? 0 : 1)) / 2 + 1 }; return query; } //查询任选设定的具体上课时间 public IQueryable GetScheduling(List IDList) { var query = from a in ExecutableFreeSelectionCouseRepository.GetList(x => IDList.Contains(x.ExecutableFreeSelectionCouseID)) join b in ExecutableFreeSelectionCouseSchedulingRepository.Entities on a.ExecutableFreeSelectionCouseID equals b.ExecutableFreeSelectionCouseID //join c in ExecutableFreeSelectionCouseTeachingSettingRepository.Entities on a.ExecutableFreeSelectionCouseID equals c.ExecutableFreeSelectionCouseID join c in coursesTimeRepository.Entities on b.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 d in gbsta.DefaultIfEmpty() //join e in DictionaryItemRepository.Entities on new { b.Weekday, DictionaryCode = "ES_WeekDay" } //equals new { Weekday = e.Value, e.DictionaryCode } into gbsta1 //from e in gbsta1.DefaultIfEmpty() select new ExecutableFreeSelectionCouseSchedulingView { SchoolyearID = a.SchoolyearID, DefaultClassName = a.DefaultClassName, SchedulingID = b.ExecutableFreeSelectionCouseSchedulingID, ID = a.ExecutableFreeSelectionCouseID, CourseCollegeID = a.CF_Department.CollegeID, ClassroomID = b.ClassroomID, ClassroomName = b.CF_Classroom.Name, CoursesTimeID = b.CoursesTimeID, Weekday = b.Weekday, TimesSegmentName = d.Name, StartTimes = c.StartTimes, EndTimes = c.EndTimes //StartWeeklyNum = c.StartWeeklyNum, //EndWeeklyNum = c.EndWeeklyNum }; return query; } //查询任选设定的具体上课时间 public IQueryable GetSchedulingTime(Guid? ID) { var query = from a in ExecutableFreeSelectionCouseRepository.GetList(x => x.ExecutableFreeSelectionCouseID == ID) join b in ExecutableFreeSelectionCouseSchedulingRepository.Entities on a.ExecutableFreeSelectionCouseID equals b.ExecutableFreeSelectionCouseID join c in ExecutableFreeSelectionCouseTeachingSettingRepository.Entities on a.ExecutableFreeSelectionCouseID equals c.ExecutableFreeSelectionCouseID select new ExecutableFreeSelectionCouseSchedulingView { SchoolyearID = a.SchoolyearID, DefaultClassName = a.DefaultClassName, SchedulingID = b.ExecutableFreeSelectionCouseSchedulingID, ID = a.ExecutableFreeSelectionCouseID, CourseCollegeID = a.CF_Department.CollegeID, ClassroomID = b.ClassroomID, ClassroomName = b.CF_Classroom.Name, CoursesTimeID = b.CoursesTimeID, Weekday = b.Weekday, StartWeeklyNum = c.StartWeeklyNum, EndWeeklyNum = c.EndWeeklyNum }; return query; } /// /// 查询任选设定的具体上课时间，选课结果和任选设定页面查询 /// /// /// public IQueryable GetScheduling(Expression> efsExp) { var query = from a in ExecutableFreeSelectionCouseRepository.GetList(efsExp) join b in ExecutableFreeSelectionCouseSchedulingRepository.Entities on a.ExecutableFreeSelectionCouseID equals b.ExecutableFreeSelectionCouseID //join c in ExecutableFreeSelectionCouseTeachingSettingRepository.Entities on a.ExecutableFreeSelectionCouseID equals c.ExecutableFreeSelectionCouseID join c in coursesTimeRepository.Entities on b.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 d in gbsta.DefaultIfEmpty() //join e in DictionaryItemRepository.Entities on new { b.Weekday, DictionaryCode = "ES_WeekDay" } //equals new { Weekday = e.Value, e.DictionaryCode } into gbsta1 //from e in gbsta1.DefaultIfEmpty() select new ExecutableFreeSelectionCouseSchedulingView { SchoolyearID = a.SchoolyearID, DefaultClassName = a.DefaultClassName, SchedulingID = b.ExecutableFreeSelectionCouseSchedulingID, ID = a.ExecutableFreeSelectionCouseID, CourseCollegeID = a.CF_Department.CollegeID, ClassroomID = b.ClassroomID, ClassroomName = b.CF_Classroom.Name, CoursesTimeID = b.CoursesTimeID, Weekday = b.Weekday, TimesSegmentName = d.Name, StartTimes = c.StartTimes, EndTimes = c.EndTimes //StartWeeklyNum = c.StartWeeklyNum, //EndWeeklyNum = c.EndWeeklyNum }; return query; } //任选设定判断教室冲突 public IQueryable GetRepeatTime(Guid? UserID, Guid? ID, 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 // && x.RecordStatus != (int)EMIS.ViewModel.EM_SelectCourseResultStatus.CancleCreate //&& x.CF_Student.Any(w => w.UserID == UserID)) ) on a.ExecutableFreeSelectionCouseID equals b.ExecutableFreeSelectionCouseID from st in b.CF_Student.Where(x => x.UserID == UserID) 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, Weekday = a.Weekday, RecordStatus = b.RecordStatus, CreateTime = b.CreateTime, CreateUserID = b.CreateUserID, ModifyTime = b.ModifyTime, ModifyUserID = b.ModifyUserID }; return query; } public IQueryable GetCheckEnouthNumSelectCourseResult(IList IDs) { //1.0 获取限选设定的所有学生数据 var executableOptionalCourseResult = (from eoc in ExecutableOptionalCourseRepository.GetList(x => IDs.Contains(x.ExecutableOptionalCourseID)) select new SelectCourseResultView { ID = eoc.ExecutableOptionalCourseID, StudentTotalNum = eoc.CF_Student.Count(), PeopleNumlower = eoc.PeopleNumlower, PeopleNumlimit = eoc.PeopleNumlimit, }); //2.0 获取任选设定的所有学生数据 var executableFreeSelectionCouseResult = (from efs in ExecutableFreeSelectionCouseRepository.GetList(x => IDs.Contains(x.ExecutableFreeSelectionCouseID)) select new SelectCourseResultView { ID = efs.ExecutableFreeSelectionCouseID, StudentTotalNum = efs.CF_Student.Count(), PeopleNumlower = efs.PeopleNumlower, PeopleNumlimit = efs.PeopleNumlimit, }); //3.0 合并限选/任选 var result = from a in executableOptionalCourseResult.Concat(executableFreeSelectionCouseResult) select new SelectCourseResultView { ID = a.ID, StudentTotalNum = a.StudentTotalNum, PeopleNumlower = a.PeopleNumlower, PeopleNumlimit = a.PeopleNumlimit, }; return result; } } }