JW/EMiS.DataLogic/SelectCourse/SelectCourseResult/SelectCourseResultDAL.cs

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<ParameterDAL> ParamterDAL { get; set; }
        public Lazy<SchoolYearDAL> 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; }

        /// <summary>
        /// 查询选课结果，0为限选，1为任选
        /// </summary>
        /// <param name="eocExp"></param>
        /// <param name="efsExp"></param>
        /// <returns></returns>
        public IQueryable<SelectCourseResultView> GetSelectCourseResultViewQueryable(Expression<Func<EM_ExecutableOptionalCourse, bool>>
            eocExp, Expression<Func<EM_ExecutableFreeSelectionCouse, bool>> 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<SelectCourseResultView> GetSelectCourseResultTeacher(Expression<Func<EM_ExecutableOptionalCourse, bool>>
            eocExp, Expression<Func<EM_ExecutableFreeSelectionCouse, bool>> 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<SelectCourseResultView> GetSelectCourseResultTeachingMode(Expression<Func<EM_ExecutableOptionalCourse, bool>>
            eocExp, Expression<Func<EM_ExecutableFreeSelectionCouse, bool>> 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<StudentSelectCourseView> GetStudentSelectCourseResultView(Guid userID)
        {
            DateTime nowTime = DateTime.Now;
            var selectCourseSchoolyearID = this.GetSelectCourseSchoolyear();
            var student = GetBaseStudentViewQueryable(userID, selectCourseSchoolyearID).FirstOrDefault();

            List<int?> OptionalCourseStatus = new List<int?> { (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<StudentSelectCourseView> GetStudentSelectedCourseResultView(Expression<Func<EM_ExecutableOptionalCourse, bool>>
            eocExp, Expression<Func<EM_ExecutableFreeSelectionCouse, bool>> 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<StudentCourseTeacherView> GetStudentSelectCourseTeacherResultView(Guid userID)
        {
            DateTime nowTime = DateTime.Now;
            Guid selectCourseSchoolyearID = this.GetSelectCourseSchoolyear();
            List<int?> OptionalCourseStatus = new List<int?> { (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<BaseStudentView> 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<ExecutableFreeSelectionCouseSchedulingView> GetScheduling(List<Guid> 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<ExecutableFreeSelectionCouseSchedulingView> 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;
        }

        /// <summary>
        /// 查询任选设定的具体上课时间，选课结果和任选设定页面查询
        /// </summary>
        /// <param name="efsExp"></param>
        /// <returns></returns>
        public IQueryable<ExecutableFreeSelectionCouseSchedulingView> GetScheduling(Expression<Func<EM_ExecutableFreeSelectionCouse, bool>> 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<ExecutableFreeSelectionCouseSchedulingView> 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<SelectCourseResultView> GetCheckEnouthNumSelectCourseResult(IList<Guid?> 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;
        }
    }
}