JW/EMiS.DataLogic/SelectCourse/OptionalCourseSettingDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
using EMIS.DataLogic.Repositories;
using EMIS.ViewModel.SelectCourse;
using System.Linq.Expressions;
using EMIS.Entities;
using EMIS.ViewModel;
using EMIS.ViewModel.CultureplanManage.PlanManagement;
using EMIS.ViewModel.Students;
using EMIS.ViewModel.UniversityManage.SpecialtyClassManage;
using EMIS.ViewModel.EducationManage;
using EMIS.ViewModel.SelectCourse.SelectCourseResult;


namespace EMIS.DataLogic.SelectCourse
{
    public class OptionalCourseSettingDAL
    {
        public SelectCourseOpenControlSettingRepository SelectCourseOpenControlSettingRepository { get; set; }
        public ExecutableOptionalCourseRepository ExecutableOptionalCourseRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public DictionaryItemRepository DictionaryItemRepository { get; set; }
        public OptionalCoursePlanRepository OptionalCoursePlanRepository { get; set; }
        public SpecialtyCourseRepository SpecialtyCourseRepository { get; set; }
        public SpecialtyRepository SpecialtyRepository { get; set; }
        public CoursematerialRepository CoursematerialRepository { get; set; }
        public DepartmentRepository DepartmentRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public ExecutableOptionalCourseTeachingSettingRepository ExecutableOptionalCourseTeachingSettingRepository { get; set; }
        public ExecutableOptionalCourseTeacherRepository ExecutableOptionalCourseTeacherRepository { get; set; }
        public ExecutableOptionalCourseTeachingModeRepository ExecutableOptionalCourseTeachingModeRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }
        public ExecutableFreeSelectionCouseRepository ExecutableFreeSelectionCouseRepository { get; set; }//任选设定
        public StaffRepository StaffRepository { get; set; }
        public EducationMissionClassRepository EducationMissionClassRepository { get; set; }    //教学任务班表
        public UserRepository UserRepository { get; set; }
        public EducationMissionRepository EducationMissionRepository { get; set; }

        public IQueryable<OptionalCourseSettingView> GetOpenControlSettingView(
            Expression<Func<EM_ExecutableOptionalCourse, bool>> openCourseSettingExp,
            Expression<Func<EM_OptionalCoursePlan, bool>> exp,
            Expression<Func<EMIS.Entities.CF_Schoolyear, bool>> schoolYearExp,
            Expression<Func<CF_Grademajor, bool>> gradeMajorExp)
        {

            var q = from eoc in ExecutableOptionalCourseRepository.GetList(openCourseSettingExp)
                    join ocp in OptionalCoursePlanRepository.Entities on eoc.OptionalCourseID equals ocp.OptionalCourseID
                    into tmp
                    from ocpss in tmp.DefaultIfEmpty()
                    join gr in GrademajorRepository.Entities on eoc.GrademajorID equals gr.GrademajorID
                    into tmp1
                    from gr in tmp1.DefaultIfEmpty()
                    //from cl in eoc.CF_Classmajor.DefaultIfEmpty()
                    //from st in eoc.CF_Student.DefaultIfEmpty()
                    //join cl in classmajorRepository.Entities on gr.GrademajorID equals  cl.GrademajorID
                    join sc in SchoolyearRepository.Entities on new { Years = gr.GradeID.Value, SchoolcodeID = gr.SemesterID.Value } equals new { Years = sc.Years, SchoolcodeID = sc.SchoolcodeID }
                    join sc1 in SchoolyearRepository.Entities on eoc.SchoolyearID equals sc1.SchoolyearID
                    join co in CoursematerialRepository.Entities on ocpss.CoursematerialID equals co.CoursematerialID
                    join de in DepartmentRepository.Entities on eoc.DepartmentID equals de.DepartmentID
                    into tmp4
                    from de1 in tmp4.DefaultIfEmpty()
                    join sp in SpecialtyRepository.Entities on ocpss.SpecialtyID equals sp.SpecialtyID
                    join dis in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(CF_Standard).Name) on sp.StandardID equals dis.Value into ddis
                    from edis in ddis.DefaultIfEmpty()
                    //join eoct in executableOptionalCourseTeacherRepository.Entities.GroupBy(x => new { x.ExecutableOptionalCourseID })
                    //    .Select(x => new { ExecutableOptionalCourseID = x.Key.ExecutableOptionalCourseID, TeacherName = x.Join()//string.Join(",", x.Select(g => g.CF_Staff.Sys_User.Name).ToArray()) })
                    //    on eoc.ExecutableOptionalCourseID equals eoct.ExecutableOptionalCourseID
                    //    into tmp1
                    //from ecot1 in tmp1.DefaultIfEmpty()
                    //join eoct in executableOptionalCourseTeacherRepository.Entities on eoc.ExecutableOptionalCourseID equals eoct.ExecutableOptionalCourseID
                    //into tmp1
                    //from ecot1 in tmp1.DefaultIfEmpty()
                    //join eoctm in executableOptionalCourseTeachingModeRepository.Entities on eoc.ExecutableOptionalCourseID equals eoctm.ExecutableOptionalCourseID
                    //into tmp2 from eoctm1 in tmp2.DefaultIfEmpty()
                    join eocts in ExecutableOptionalCourseTeachingSettingRepository.Entities on eoc.ExecutableOptionalCourseID equals eocts.ExecutableOptionalCourseID
                    into tmp3 from ecots1 in tmp3.DefaultIfEmpty()
                    select new OptionalCourseSettingView
                    {
                        ExecutableOptionalCourseID = eoc.ExecutableOptionalCourseID,
                        //ExecutableOptionalCourseTeacherID = ecot1.ExecutableOptionalCourseTeacherID,
                        //TeacherNames=ecot1.TeacherName,
                        //ExecutableOptionalCourseTeachingModeID = eoctm1.ExecutableOptionalCourseTeachingModeID,
                        OptionalCourseID = ocpss.OptionalCourseID,
                        SchoolyearID = eoc.SchoolyearID,
                        DefaultClassName = eoc.DefaultClassName,
                        //ClassmajorID = cl.ClassmajorID,
                        //StandardID=sp.StandardID,
                        SchoolYearCode = sc1.Code,
                        YearID = sc1.Years,
                        GSchoolYearCode = sc.Code,
                        GrademajorID = gr.GrademajorID,
                        GrademajorCode = gr.Code,
                        GrademajorName = gr.Name,
                        GradeID = gr.GradeID,
                        CampusID = gr.CF_Facultymajor.CF_College.CampusID,
                        CollegeID = gr.CF_Facultymajor.CollegeID,
                        CollegeName = gr.CF_Facultymajor.CF_College.Name,
                        DepartmentID = eoc.DepartmentID,
                        DepartmentName = de1.Name,
                        //SpecialtyCourseID = sc.SpecialtyCourseID,
                        SpecialtyID = ocpss.SpecialtyID,
                        StandardID = ocpss.CF_Specialty.StandardID,
                        CoursematerialID = ocpss.CoursematerialID,
                        CourseCode = co.CourseCode,
                        CourseName = co.CourseName,
                        //Credit = sc.EM_SpecialtyCourseTeachingSetting.Credit,
                        //EndWeeklyNum = ocp.EM_SpecialtyCourse.EM_SpecialtyCourseTeachingSetting.EndWeeklyNum,
                        ExaminationModeID = eoc.ExaminationModeID,
                        TeachinglanguageID = eoc.TeachinglanguageID,
                        //HandleModeID = ocp.HandleModeID,
                        //No = ocp.No,
                        //DefaultClassName = ocp.DefaultClassName,
                        //SchoolyearID = ocp.SchoolyearID,
                        //SchoolyearDesc = ocp.CF_Schoolyear.Code,
                        //IsNeedMaterial = ocp.IsNeedMaterial ?? false,
                        PeopleNumlower = eoc.PeopleNumlower,
                        PeopleNumlimit = eoc.PeopleNumlimit,
                        IsEnable = eoc.IsEnable ?? true,
                        IsOpened = eoc.IsOpened ?? true,
                        Remarks = eoc.Remarks,
                        RecordStatus = eoc.RecordStatus,
                        CreateUserID = eoc.CreateUserID,
                        CreateTime = eoc.CreateTime,
                        ModifyUserID = eoc.ModifyUserID,
                        ModifyTime = eoc.ModifyTime,
                        //Schoolyear = gs.SchoolyearID,
                        //Sys_User = ocp.EM_OptionalCoursePlanStaff.Select(x => x.CF_Staff.Sys_User),
                        CourseStructureID = eoc.CourseStructureID,
                        CourseTypeID = eoc.CourseTypeID,
                        //TeachingModeID = eoctm1.TeachingModeID,
                        Credit = ecots1.Credit ?? 0,
                        TheoryCourse = ecots1.TheoryCourse??0,
                        Practicehours = ecots1.Practicehours??0,
                        Trialhours = ecots1.Trialhours??0,
                        WeeklyNum=ecots1.WeeklyNum??0,
                        TheoryWeeklyNum = ecots1.TheoryWeeklyNum ?? 0,
                        PracticeWeeklyNum = ecots1.PracticeWeeklyNum ?? 0,
                        TrialWeeklyNum = ecots1.TrialWeeklyNum ?? 0,
                        StartWeeklyNum = ecots1.StartWeeklyNum ?? 0,
                        EndWeeklyNum = ecots1.EndWeeklyNum ?? 0,
                        WeeklyHours = ecots1.WeeklyHours ?? 0,
                        SchoolyearNumID = ocpss.SchoolyearNumID,
                        SchoolcodeID = ocpss.SchoolcodeID,
                        StarttermID = ((ocpss.SchoolyearNumID - 1) * 2) + (ocpss.SchoolcodeID == eoc.CF_Grademajor.SemesterID ? 0 : 1) + 1,
                        CourseCategoryID = eoc.CourseCategoryID,
                        //IsMainCourse = ocp.EM_SpecialtyCourse.IsMainCourse ?? false,
                        CourseQualityID = eoc.CourseQualityID,
                        PracticeTypeID = eoc.PracticeTypeID,
                        //StandardDesc=edis.Name,
                        //StandardCode = edis.Code,
                        Years=gr.GradeID,
                        ClassCount = eoc.CF_Classmajor.Count,
                        ResultTypeID=eoc.ResultTypeID
                    };

            //var q = (from scocs in SelectCourseOpenControlSettingRepository.GetList(openControlSettingExp)
            //         join sy in SchoolyearRepository.GetList(schoolYearExp) on scocs.SchoolyearID equals sy.SchoolyearID 
            //         join gm in GrademajorRepository.GetList(gradeMajorExp) on scocs.GrademajorID equals gm.GrademajorID 
            //         join dis in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(CF_Standard).Name) on gm.CF_Facultymajor.StandardID equals dis.Value into ddis
            //         from edis in ddis.DefaultIfEmpty()
            //         join disy in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(EMIS.ViewModel.CF_Schoolyear).Name) on gm.SchoolyearID equals disy.Value into ddisy
            //         from edisy in ddisy.DefaultIfEmpty()
            //         select new SelectCourseOpenControlSettingView
            //         {
            //             SelectCourseOpenControlSettingID = scocs.SelectCourseOpenControlSettingID,
            //             SchoolyearID = scocs.SchoolyearID,
            //             SchoolyearCode = sy.Code,
            //             GrademajorID = scocs.GrademajorID,
            //             GrademajorName = gm.Name,
            //             YearID = gm.SchoolyearID,
            //             YearName = edisy.Name,
            //             StandardID = gm.CF_Facultymajor.StandardID,
            //             StandardDesc = edis.Name,
            //             CollegeID = gm.CF_Facultymajor.CollegeID,
            //             CollegeName = gm.CF_Facultymajor.CF_College.Name,
            //             StartTime = scocs.StartTime,
            //             EndTime = scocs.EndTime,
            //             MaxSelectCount = scocs.MaxSelectCount,
            //             MinSelectCount = scocs.MinSelectCount,
            //             MaxCredit = scocs.MaxCredit,
            //             MinCredit = scocs.MinCredit,
            //             Remark = scocs.Remark,
            //             RecordStatus = scocs.RecordStatus,
            //             CreateUserID = scocs.CreateUserID,
            //             CreateTime = scocs.CreateTime,
            //             ModifyUserID = scocs.ModifyUserID,
            //             ModifyTime = scocs.ModifyTime
            //         });

            return q;
        }

        public IQueryable<OptionalCourseSettingView> GetOpenControlSettingClassmajorView(
            Expression<Func<EM_ExecutableOptionalCourse, bool>> openCourseSettingExp)
        {

            var q = from ocps in ExecutableOptionalCourseRepository.GetList(openCourseSettingExp)
                  
                    from cl in ocps.CF_Classmajor
                    
                    select new OptionalCourseSettingView
                    {
                        ExecutableOptionalCourseID = ocps.ExecutableOptionalCourseID,
                       
                        DefaultClassName = ocps.DefaultClassName,
                        ClassmajorID = cl.ClassmajorID,

                        ExaminationModeID = ocps.ExaminationModeID,
                        TeachinglanguageID = ocps.TeachinglanguageID,
                       
                        PeopleNumlower = ocps.PeopleNumlower,
                        PeopleNumlimit = ocps.PeopleNumlimit,
                        IsEnable = ocps.IsEnable ?? false,
                        IsOpened = ocps.IsOpened ?? false,
                        Remarks = ocps.Remarks,
                        RecordStatus = ocps.RecordStatus,
                        CreateUserID = ocps.CreateUserID,
                        CreateTime = ocps.CreateTime,
                        ModifyUserID = ocps.ModifyUserID,
                        ModifyTime = ocps.ModifyTime,
                        
                        CourseStructureID = ocps.CourseStructureID,
                        CourseTypeID = ocps.CourseTypeID,
                       
                        //StarttermID = ((ocp.EM_SpecialtyCourse.SchoolyearNumID - 1) * 2) + (ocp.EM_SpecialtyCourse.SchoolcodeID == ocp.CF_Grademajor.SchoolcodeID ? 0 : 1) + 1,
                        CourseCategoryID = ocps.CourseCategoryID,
                        //IsMainCourse = ocp.EM_SpecialtyCourse.IsMainCourse ?? false,
                        CourseQualityID = ocps.CourseQualityID,
                        PracticeTypeID = ocps.PracticeTypeID
                    };

            return q;
        }


        

        public List<string> GetTeachingModeTypeQueryble(Guid? ExecutableOptionalCourseID)
        {
            var query = from a in ExecutableOptionalCourseTeachingModeRepository.Entities.Where(x => x.ExecutableOptionalCourseID == ExecutableOptionalCourseID)
                        join b in DictionaryItemRepository.Entities on new { a.TeachingModeID, DictionaryCode = DictionaryItem.CF_TeachingMode.ToString() }
                        equals new { TeachingModeID = b.Value, b.DictionaryCode }
                        select b.Value.Value.ToString();
            return query.ToList();
        }

        /// <summary>
        /// 获取任务班
        /// </summary>
        /// <param name="educationMissionClassID"></param>
        /// <returns></returns>
        public IQueryable<OptionalCourseClassListView> GetExecutableOptionalCourseClassmajorList()
        {
            var query = from a in ExecutableOptionalCourseRepository.Entities 
                        from b in a.CF_Classmajor
                        join c in OptionalCoursePlanRepository.Entities on a.OptionalCourseID equals c.OptionalCourseID
                        //join b in executableOptionalCourseTeacherRepository.Entities
                        //on a.ExecutableOptionalCourseID equals b.ExecutableOptionalCourseID
                        select new OptionalCourseClassListView
                        {
                            ExecutableOptionalCourseID = a.ExecutableOptionalCourseID,
                            ClassmajorID = b.ClassmajorID,
                            Name=b.Name,
                            //EducationMissionClassID = a.EducationMissionClassID,
                            UserID = b.UserID,
                            No = b.No,
                            yearID = b.CF_Grademajor.GradeID,
                            StandardID = b.CF_Grademajor.CF_Facultymajor.StandardID,
                            GrademajorCode = b.CF_Grademajor.Code,
                            GrademajorName = b.CF_Grademajor.Name,
                            LearnSystem = b.CF_Grademajor.CF_Facultymajor.LearnSystem, 
                            EducationID = b.CF_Grademajor.CF_Facultymajor.EducationID,
                            LearningformID = b.CF_Grademajor.CF_Facultymajor.LearningformID,
                            CoursematerialID=c.CoursematerialID,
                            CourseCode = c.EM_Coursematerial.CourseCode,
                            CourseName = c.EM_Coursematerial.CourseName,
                            SchoolyearID=a.SchoolyearID,
                            DefaultClassName=a.DefaultClassName,
                            StudentCount = b.CF_Student.Count(),
                        };
            return query;
        }

        /// <summary>
        /// 提交时查询限选设定及相关班级
        /// </summary>
        /// <param name="ExecutableOptionalCourseID"></param>
        /// <param name="SchoolyearID"></param>
        /// <param name="CoursematerialID"></param>
        /// <returns></returns>
        public IQueryable<OptionalCourseClassListView> GetSubmitcompareClassmajor( )
        {
            var query = from a in ExecutableOptionalCourseRepository.GetList(x => true
                //x.ExecutableOptionalCourseID != ExecutableOptionalCourseID &&
                //x.RecordStatus>(int)EMIS.ViewModel.EM_SelectCourseResultStatus.NotSubmit&&
                //x.SchoolyearID == SchoolyearID && x.EM_OptionalCoursePlan.CoursematerialID == CoursematerialID
                )
                        from b in a.CF_Classmajor
                        join c in OptionalCoursePlanRepository.Entities on a.OptionalCourseID equals c.OptionalCourseID
                        //join d in executableOptionalCourseTeacherRepository.Entities on a.ExecutableOptionalCourseID equals d.ExecutableOptionalCourseID
                        select new OptionalCourseClassListView
                        {
                            ExecutableOptionalCourseID = a.ExecutableOptionalCourseID,
                            ClassmajorID = b.ClassmajorID,
                            Name = b.Name,
                            //EducationMissionClassID = a.EducationMissionClassID,
                            UserID = b.UserID,
                            No = b.No,
                            CoursematerialID = c.CoursematerialID,
                            SchoolyearID = a.SchoolyearID,
                            DefaultClassName = a.DefaultClassName,
                            StudentCount = b.CF_Student.Count(),
                            //TeacherID = d.UserID.Value,
                            
                            RecordStatus = a.RecordStatus,
                        };
            return query;
        }

        /// <summary>
        /// 获取任务班授课老师
        /// </summary>
        /// <param name="educationMissionClassID"></param>
        /// <returns></returns>
        public IQueryable<OptionalCourseTeacherListView> GetEducationMissionClassTeacherListViewQueryble()
        {
            var query = from a in ExecutableOptionalCourseRepository.Entities
                        join b in ExecutableOptionalCourseTeacherRepository.Entities
                        on a.ExecutableOptionalCourseID equals b.ExecutableOptionalCourseID
                        select new OptionalCourseTeacherListView
                        {
                            ExecutableOptionalCourseID = a.ExecutableOptionalCourseID,
                            
                            //EducationMissionClassID = a.EducationMissionClassID,
                            UserID = b.CF_Staff.UserID,
                            LoginID = b.CF_Staff.Sys_User.LoginID,
                            Name = b.CF_Staff.Sys_User.Name,
                            CollegeID = b.CF_Staff.CF_College.CollegeID,
                            CollegeName = b.CF_Staff.CF_College.Name,
                            BirthDate = b.CF_Staff.BirthDate,
                            TeacherType = b.CF_Staff.TeacherTypeID,
                            IncumbencyState = b.CF_Staff.IncumbencyState,
                            Title = b.CF_Staff.TitleID,
                            TeachingMethod = b.TeachingMethod,
                        };
            return query;
        }

        /// <summary>
        /// 获取限选任务班授课老师
        /// </summary>
        /// <param name="educationMissionClassID"></param>
        /// <returns></returns>
        public IQueryable<OptionalCourseTeacherListView> GetTeacherList()
        {
            var query = from a in ExecutableOptionalCourseRepository.Entities
                        join b in ExecutableOptionalCourseTeacherRepository.Entities
                        on a.ExecutableOptionalCourseID equals b.ExecutableOptionalCourseID
                        select new OptionalCourseTeacherListView
                        {
                            ID = a.ExecutableOptionalCourseID,

                            //EducationMissionClassID = a.EducationMissionClassID,
                            UserID = b.CF_Staff.UserID,
                            LoginID = b.CF_Staff.Sys_User.LoginID,
                            Name = b.CF_Staff.Sys_User.Name,
                            CollegeID = b.CF_Staff.CF_College.CollegeID,
                            CollegeName = b.CF_Staff.CF_College.Name,
                            BirthDate = b.CF_Staff.BirthDate,
                            TeacherType = b.CF_Staff.TeacherTypeID,
                            IncumbencyState = b.CF_Staff.IncumbencyState,
                            Title = b.CF_Staff.TitleID,
                            TeachingMethod = b.TeachingMethod,
                        };
            return query;
        }

        /// <summary>
        /// 获取任选任务班授课老师
        /// </summary>
        /// <returns></returns>
        public IQueryable<OptionalCourseTeacherListView> GetTeacherList1()
        {
            var query = from a in ExecutableFreeSelectionCouseRepository.Entities
                        from b in a.EM_ExecutableFreeSelectionCouseTeacher
                        //on a.ExecutableOptionalCourseID equals b.ExecutableOptionalCourseID
                        select new OptionalCourseTeacherListView
                        {
                            ID = a.ExecutableFreeSelectionCouseID,

                            //EducationMissionClassID = a.EducationMissionClassID,
                            UserID = b.CF_Staff.UserID,
                            LoginID = b.CF_Staff.Sys_User.LoginID,
                            Name = b.CF_Staff.Sys_User.Name,
                            CollegeID = b.CF_Staff.CF_College.CollegeID,
                            CollegeName = b.CF_Staff.CF_College.Name,
                            BirthDate = b.CF_Staff.BirthDate,
                            TeacherType = b.CF_Staff.TeacherTypeID,
                            IncumbencyState = b.CF_Staff.IncumbencyState,
                            Title = b.CF_Staff.TitleID,
                            TeachingMethod = b.TeachingMethod,
                        };
            return query;
        }

        /// <summary>
        /// 获取老师
        /// </summary>
        /// <param name="educationMissionClassID"></param>
        /// <returns></returns>
        public IQueryable<EMIS.ViewModel.EducationManage.BaseTeacherTeachingView> GetBaseTeacherListViewQueryble()
        {
            var query = from a in StaffRepository.Entities
                        select new OptionalCourseTeacherListView
                        {
                            //ExecutableOptionalCourseID = a.ExecutableOptionalCourseID,

                            //EducationMissionClassID = a.EducationMissionClassID,
                            UserID = a.UserID,
                            LoginID = a.Sys_User.LoginID,
                            Name = a.Sys_User.Name,
                            CollegeID = a.CF_College.CollegeID,
                            CollegeName = a.CF_College.Name,
                            BirthDate = a.BirthDate,
                            TeacherType = a.TeacherTypeID,
                            IncumbencyState = a.IncumbencyState,
                            Title = a.TitleID,
                            //TeachingMethod = a.TeachingMethod,
                        };
            return query;
        }

        /// <summary>
        /// 选课结果页获取课程的学生名单
        /// </summary>
        /// <param name="type">0限选，1任选</param>
        /// <returns></returns>
        public IQueryable<BaseStudentView> GetSelectCourseStudent(Expression<Func<EM_ExecutableOptionalCourse, bool>> exp)
        {
            var q = from a in ExecutableOptionalCourseRepository.GetList(exp)
                    from b in a.CF_Student.DefaultIfEmpty()

                    join c in StudentRepository.Entities on b.UserID equals c.UserID
                    select new BaseStudentView
                    {
                        UserID = c.UserID,
                        LoginID = c.Sys_User.LoginID,
                        UserName = c.Sys_User.Name,
                        SexID = c.SexID,
                        IDNumber = c.IDNumber,
                        StudentStatus = c.StudentStatus,
                        InSchoolStatusID = c.InSchoolStatusID,
                        ClassmajorName = c.CF_Classmajor.Name,
                        SchoolYearCode = a.CF_Schoolyear.Code,
                        DefaultClassName = a.DefaultClassName,
                        CoursematerialCode = a.EM_OptionalCoursePlan.EM_Coursematerial.CourseCode,
                        CoursematerialName = a.EM_OptionalCoursePlan.EM_Coursematerial.CourseName,
                        CourseTypeID = a.EM_OptionalCoursePlan.CourseTypeID
                    };
            return q;
        }
        public IQueryable<BaseStudentView> GetSelectCourseStudent1(Expression<Func<EM_ExecutableFreeSelectionCouse, bool>> exp)
        {
            var q = from a in ExecutableFreeSelectionCouseRepository.GetList(exp)
                    from b in a.CF_Student.DefaultIfEmpty()
                    
                    join c in StudentRepository.Entities on b.UserID equals c.UserID
                    
                    select new BaseStudentView
                    {
                        UserID = c.UserID,
                        LoginID = c.Sys_User.LoginID,
                        UserName = c.Sys_User.Name,
                        SexID = c.SexID,
                        IDNumber = c.IDNumber,
                        StudentStatus = c.StudentStatus,
                        InSchoolStatusID = c.InSchoolStatusID,
                        ClassmajorName = c.CF_Classmajor.Name,
                        SchoolYearCode = a.CF_Schoolyear.Code,
                        DefaultClassName = a.DefaultClassName,
                        CoursematerialCode = a.EM_FreeSelectionCouse.EM_Coursematerial.CourseCode,
                        CoursematerialName = a.EM_FreeSelectionCouse.EM_Coursematerial.CourseName,
                        CourseTypeID = a.EM_FreeSelectionCouse.CourseTypeID
                    };


            return q;
        }

        public IQueryable<DuplicateOptionCourseView> GetDuplicateOptionCourseViewQueryable(Expression<Func<EM_ExecutableOptionalCourse, bool>> exp)
        {
            var q = from a in ExecutableOptionalCourseRepository.GetList(exp)
                    from b in a.CF_Student
                    from c in ExecutableOptionalCourseRepository.Entities
                        .Where(x => x.SchoolyearID == a.SchoolyearID && x.EM_OptionalCoursePlan.CoursematerialID == a.EM_OptionalCoursePlan.CoursematerialID
                        && x.ExecutableOptionalCourseID != a.ExecutableOptionalCourseID && x.RecordStatus == (int)EMIS.ViewModel.EM_SelectCourseResultStatus.Opened)
                    from d in c.CF_Student.Where(x => x.UserID == b.UserID) 
                    group a by new { SourceName = a.DefaultClassName, DestinationName = c.DefaultClassName } into g 
                    select new DuplicateOptionCourseView
                    {
                        SourceName = g.Key.SourceName,
                        DestinationName = g.Key.DestinationName
                    };
            return q;
        }

        /// <summary>
        /// 选课结果页面，生成任务班时
        /// </summary>
        /// <param name="executableOptionalCourseIDList"></param>
        /// <returns></returns>
        public IQueryable<EducationMissionClassStudentView> GetEducationMissionClassStudentByExecutableOptionalCourseID(IList<Guid?> executableOptionalCourseIDList)
        {
            var q = (from ep in ExecutableOptionalCourseRepository.GetList(x => executableOptionalCourseIDList.Contains(x.ExecutableOptionalCourseID))
                     join emc in EducationMissionClassRepository.Entities on
                         new
                         {
                             ep.SchoolyearID,
                             ep.EM_OptionalCoursePlan.CoursematerialID,
                             HandleModeID = (int?)CF_HandleMode.SelectionCourse
                         } equals new
                         {
                             emc.EM_EducationMission.SchoolyearID,
                             emc.CoursematerialID,
                             emc.HandleModeID
                         }
                     from esc in emc.EM_EducationSchedulingClass 
                     from s in esc.CF_Student
                     join u in UserRepository.Entities
                            on s.UserID equals u.UserID
                    select new EducationMissionClassStudentView
                    {
                        SchoolyearID = emc.EM_EducationMission.SchoolyearID,
                        CoursematerialID = emc.CoursematerialID,
                        CourseSelectTypeID = emc.OptionalCourseTypeID,
                        HandleModeID = emc.HandleModeID,
                        UserID = u.UserID,
                        LoginID = u.LoginID,
                        Name = u.Name,
                        ClassName = s.CF_Classmajor.Name,
                        StudentStatus = s.StudentStatus
                    });
            return q;
        }

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

            return q;
        }
    }
}