JW/EMiS.DataLogic/EducationManage/ExecutablePlanDAL.cs

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

namespace EMIS.DataLogic.EducationManage
{
    public class ExecutablePlanDAL
    {
        public ExecutablePlanRepository ExecutablePlanRepository { get; set; }
        public ExecutablePlanProfileRepository ExecutablePlanProfileRepository { get; set; }
        public ExecutablePlanTeachingSettingRepository ExecutablePlanTeachingSettingRepository { get; set; }
        public ExecutablePlanTeachingModeTypeRepository ExecutablePlanTeachingModeTypeRepository { get; set; }
        public ExecutablePlanTeachingPlaceRepository ExecutablePlanTeachingPlaceRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public CoursematerialRepository CoursematerialRepository { get; set; }
        public ClassGroupingRepository ClassGroupingRepository { get; set; }
        public ClassGroupingSettingsRepository ClassGroupingSettingsRepository { get; set; }
        public DepartmentRepository DepartmentRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public CampusRepository CampusRepository { get; set; }
        public SpecialtyRepository SpecialtyRepository { get; set; }
        public SpecialtyPlanRepository SpecialtyPlanRepository { get; set; }
        public SpecialtyPlanTeachingSettingRepository SpecialtyPlanTeachingSettingRepository { get; set; }
        public EducationMissionRepository EducationMissionRepository { get; set; }
        public EducationMissionClassRepository EducationMissionClassRepository { get; set; }
        public EducationMissionClassTeachingSettingRepository EducationMissionClassTeachingSettingRepository { get; set; }
        public EducationSchedulingClassRepository EducationSchedulingClassRepository { get; set; }
        public DictionaryItemRepository DictionaryItemRepository { get; set; }
        public RetakePlanStudentRepository RetakePlanStudentRepository { get; set; }

        public Lazy<CoursematerialDAL> CoursematerialDAL { get; set; }

        /// <summary>
        /// 查询执行计划信息ExecutablePlanView
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<ExecutablePlanView> GetExecutablePlanViewQueryable(Expression<Func<EM_ExecutablePlan, bool>> exp)
        {
            var query = from ep in ExecutablePlanRepository.GetList(exp)
                        join eppr in ExecutablePlanProfileRepository.Entities
                        on ep.ExecutablePlanID equals eppr.ExecutablePlanID into tempeppr
                        from epeppr in tempeppr.DefaultIfEmpty()
                        join epst in ExecutablePlanTeachingSettingRepository.Entities
                        on ep.ExecutablePlanID equals epst.ExecutablePlanID into tempepst
                        from epepst in tempepst.DefaultIfEmpty()
                        join sy in SchoolyearRepository.Entities
                        on ep.SchoolyearID equals sy.SchoolyearID
                        join cou in CoursematerialRepository.Entities
                        on ep.CoursematerialID equals cou.CoursematerialID
                        join cg in ClassGroupingRepository.Entities
                        on cou.ClassGroupingID equals cg.ClassGroupingID into tempcg
                        from coucg in tempcg.DefaultIfEmpty()
                        join dep in DepartmentRepository.Entities
                        on ep.DepartmentID equals dep.DepartmentID into tempdep
                        from epdep in tempdep.DefaultIfEmpty()
                        join gr in GrademajorRepository.Entities
                        on ep.GrademajorID equals gr.GrademajorID
                        join stsy in SchoolyearRepository.Entities
                        on new { SchoolyearID = gr.GradeID.Value, SchoolcodeID = gr.SemesterID.Value }
                        equals new { SchoolyearID = stsy.Years, SchoolcodeID = stsy.SchoolcodeID } into tempstsy
                        from startsy in tempstsy.DefaultIfEmpty()
                        join fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        join spe in SpecialtyRepository.Entities
                        on new { StandardID = fa.StandardID, EducationID = fa.EducationID, LearningformID = fa.LearningformID, LearnSystem = fa.LearnSystem }
                        equals new { StandardID = spe.StandardID, EducationID = spe.EducationID, LearningformID = spe.LearningformID, LearnSystem = spe.LearnSystem }
                        join dicst in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(CF_Standard).Name)
                        on fa.StandardID equals dicst.Value into tempst
                        from fast in tempst.DefaultIfEmpty()
                        join col in CollegeRepository.Entities
                        on fa.CollegeID equals col.CollegeID
                        join cam in CampusRepository.Entities
                        on col.CampusID equals cam.CampusID
                        select new ExecutablePlanView
                        {
                            ExecutablePlanID = ep.ExecutablePlanID,
                            SourceTypeID = ep.SourceTypeID,
                            DefaultClassName = ep.DefaultClassName,
                            SpecialtyPlanID = ep.SpecialtyPlanID,
                            SchoolyearID = ep.SchoolyearID,
                            SchoolyearCode = sy.Code,
                            YearID = sy.Years,
                            Value = sy.Value,
                            SchoolyearNumID = (sy.Value - ((gr.GradeID * 2 - 1) + gr.SemesterID - 1)) / 2 + 1,
                            SchoolcodeID = sy.SchoolcodeID,
                            StarttermID = sy.Value - ((gr.GradeID * 2 - 1) + gr.SemesterID - 1) + 1,
                            GrademajorID = ep.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            GradeID = gr.GradeID,
                            FacultymajorID = gr.FacultymajorID,
                            FacultymajorCode = fa.Code,
                            FacultymajorName = fa.Name,
                            CollegeID = fa.CollegeID,
                            CollegeCode = col.No,
                            CollegeName = col.Name,
                            CampusID = col.CampusID,
                            CampusNo = cam.No,
                            CampusName = cam.Name,
                            StartSchoolyearID = startsy.SchoolyearID,
                            StartSchoolyearCode = startsy.Code,
                            StartValue = startsy.Value,
                            StartSchoolcodeID = gr.SemesterID,
                            GraduatingSemesterID = gr.GraduateSchoolyearID,
                            SpecialtyID = spe.SpecialtyID,
                            StandardID = fa.StandardID,
                            StandardName = fast.Name,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            CoursematerialID = ep.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            ClassGroupingID = cou.ClassGroupingID,
                            ClassGroupingName = coucg.ClassName,
                            CourseStructureID = ep.CourseStructureID,
                            CourseCategoryID = ep.CourseCategoryID,
                            CourseTypeID = ep.CourseTypeID,
                            CourseQualityID = ep.CourseQualityID,
                            Credit = epepst.Credit ?? 0,
                            TheoryCourse = epepst.TheoryCourse ?? 0,
                            Practicehours = epepst.Practicehours ?? 0,
                            Trialhours = epepst.Trialhours ?? 0,
                            Totalhours = (epepst.TheoryCourse ?? 0) + (epepst.Practicehours ?? 0),
                            TheoryWeeklyNum = epepst.TheoryWeeklyNum ?? 0,
                            PracticeWeeklyNum = epepst.PracticeWeeklyNum ?? 0,
                            TrialWeeklyNum = epepst.TrialWeeklyNum ?? 0,
                            SchoolweeksNum = (epepst.TheoryWeeklyNum ?? 0) + (epepst.PracticeWeeklyNum ?? 0),
                            WeeklyHours = epepst.WeeklyHours ?? 0,
                            WeeklyNum = epepst.WeeklyNum ?? 0,
                            StartWeeklyNum = epepst.StartWeeklyNum ?? 0,
                            EndWeeklyNum = epepst.EndWeeklyNum ?? 0,
                            StartEndWeeklyNum = (epepst.StartWeeklyNum ?? 0) + "-" + (epepst.EndWeeklyNum ?? 0),
                            DepartmentID = ep.DepartmentID,
                            DepartmentCode = epdep.No,
                            DepartmentName = epdep.Name,
                            CourseCollegeID = epdep.CollegeID,
                            CourseCollegeCode = epdep.CF_College.No,
                            CourseCollegeName = epdep.CF_College.Name,
                            IsSpecialtycore = epeppr.IsSpecialtycore ?? false,
                            IsCooperation = epeppr.IsCooperation ?? false,
                            IsRequired = epeppr.IsRequired ?? false,
                            IsElective = epeppr.IsElective ?? false,
                            IsNetworkCourse = epeppr.IsNetworkCourse ?? false,
                            IsMainCourse = epeppr.IsMainCourse ?? false,
                            IsNeedMaterial = ep.IsNeedMaterial ?? false,
                            CourseFineID = epeppr.CourseFineID,
                            PracticeTypeID = epeppr.PracticeTypeID,
                            TeachinglanguageID = epeppr.TeachinglanguageID,
                            ExaminationModeID = epeppr.ExaminationModeID,
                            ResultTypeID = ep.ResultTypeID,
                            HandleModeID = ep.HandleModeID,
                            TeachingModeIDList = ep.EM_ExecutablePlanTeachingModeType.Select(x => x.TeachingModeID).ToList(),
                            TeachingPlaceIDList = ep.EM_ExecutablePlanTeachingPlace.Select(x => x.TeachingPlace).ToList(),
                            PlanTypeID = ep.SpecialtyPlanID == null ? (int)EM_PlanType.Outside : (int)EM_PlanType.Inside,
                            ExecuteStatus = ep.RecordStatus,
                            Remark = ep.Remark,
                            RecordStatus = ep.RecordStatus,
                            CreateUserID = ep.CreateUserID,
                            CreateTime = ep.CreateTime,
                            ModifyUserID = ep.ModifyUserID,
                            ModifyTime = ep.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询执行计划信息ExecutablePlanView（统计对应的计划范围、学生人数）
        /// </summary>
        /// <param name="expExecutablePlan"></param>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<ExecutablePlanView> GetExecutablePlanViewQueryable(Expression<Func<EM_ExecutablePlan, bool>> expExecutablePlan,
                Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from ep in ExecutablePlanRepository.GetList(expExecutablePlan)
                        join eppr in ExecutablePlanProfileRepository.Entities
                        on ep.ExecutablePlanID equals eppr.ExecutablePlanID into tempeppr
                        from epeppr in tempeppr.DefaultIfEmpty()
                        join epst in ExecutablePlanTeachingSettingRepository.Entities
                        on ep.ExecutablePlanID equals epst.ExecutablePlanID into tempepst
                        from epepst in tempepst.DefaultIfEmpty()
                        join sy in SchoolyearRepository.Entities
                        on ep.SchoolyearID equals sy.SchoolyearID
                        join cou in CoursematerialRepository.Entities
                        on ep.CoursematerialID equals cou.CoursematerialID
                        join cg in ClassGroupingRepository.Entities
                        on cou.ClassGroupingID equals cg.ClassGroupingID into tempcg
                        from coucg in tempcg.DefaultIfEmpty()
                        join dep in DepartmentRepository.Entities
                        on ep.DepartmentID equals dep.DepartmentID into tempdep
                        from epdep in tempdep.DefaultIfEmpty()
                        join gr in GrademajorRepository.Entities
                        on ep.GrademajorID equals gr.GrademajorID
                        join stsy in SchoolyearRepository.Entities
                        on new { SchoolyearID = gr.GradeID.Value, SchoolcodeID = gr.SemesterID.Value }
                        equals new { SchoolyearID = stsy.Years, SchoolcodeID = stsy.SchoolcodeID } into tempstsy
                        from startsy in tempstsy.DefaultIfEmpty()
                        join fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        join spe in SpecialtyRepository.Entities
                        on new { StandardID = fa.StandardID, EducationID = fa.EducationID, LearningformID = fa.LearningformID, LearnSystem = fa.LearnSystem }
                        equals new { StandardID = spe.StandardID, EducationID = spe.EducationID, LearningformID = spe.LearningformID, LearnSystem = spe.LearnSystem }
                        join dicst in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(CF_Standard).Name)
                        on fa.StandardID equals dicst.Value into tempst
                        from fast in tempst.DefaultIfEmpty()
                        join col in CollegeRepository.Entities
                        on fa.CollegeID equals col.CollegeID
                        join cam in CampusRepository.Entities
                        on col.CampusID equals cam.CampusID
                        join pr in
                            (
                                from cl in ClassmajorRepository.Entities
                                group cl by cl.GrademajorID into gpr
                                select new
                                {
                                    GrademajorID = gpr.Key,
                                    PlanRange = gpr.Count()
                                }
                            )
                        on gr.GrademajorID equals pr.GrademajorID into temppr
                        from grpr in temppr.DefaultIfEmpty()
                        join stu in
                            (
                                from stu in StudentRepository.GetList(expStudent)
                                group stu by stu.CF_Classmajor.CF_Grademajor.GrademajorID into gstu
                                select new
                                {
                                    GrademajorID = gstu.Key,
                                    StudentCount = gstu.Count()
                                }
                            )
                        on gr.GrademajorID equals stu.GrademajorID into tempstu
                        from grstu in tempstu.DefaultIfEmpty()
                        select new ExecutablePlanView
                        {
                            ExecutablePlanID = ep.ExecutablePlanID,
                            SourceTypeID = ep.SourceTypeID,
                            DefaultClassName = ep.DefaultClassName,
                            SpecialtyPlanID = ep.SpecialtyPlanID,
                            SchoolyearID = ep.SchoolyearID,
                            SchoolyearCode = sy.Code,
                            YearID = sy.Years,
                            Value = sy.Value,
                            SchoolyearNumID = (sy.Value - ((gr.GradeID * 2 - 1) + gr.SemesterID - 1)) / 2 + 1,
                            SchoolcodeID = sy.SchoolcodeID,
                            StarttermID = sy.Value - ((gr.GradeID * 2 - 1) + gr.SemesterID - 1) + 1,
                            GrademajorID = ep.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            GradeID = gr.GradeID,
                            FacultymajorID = gr.FacultymajorID,
                            FacultymajorCode = fa.Code,
                            FacultymajorName = fa.Name,
                            CollegeID = fa.CollegeID,
                            CollegeCode = col.No,
                            CollegeName = col.Name,
                            CampusID = col.CampusID,
                            CampusNo = cam.No,
                            CampusName = cam.Name,
                            StartSchoolyearID = startsy.SchoolyearID,
                            StartSchoolyearCode = startsy.Code,
                            StartValue = startsy.Value,
                            StartSchoolcodeID = gr.SemesterID,
                            GraduatingSemesterID = gr.GraduateSchoolyearID,
                            SpecialtyID = spe.SpecialtyID,
                            StandardID = fa.StandardID,
                            StandardName = fast.Name,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            CoursematerialID = ep.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            ClassGroupingID = cou.ClassGroupingID,
                            ClassGroupingName = coucg.ClassName,
                            CourseStructureID = ep.CourseStructureID,
                            CourseCategoryID = ep.CourseCategoryID,
                            CourseTypeID = ep.CourseTypeID,
                            CourseQualityID = ep.CourseQualityID,
                            Credit = epepst.Credit ?? 0,
                            TheoryCourse = epepst.TheoryCourse ?? 0,
                            Practicehours = epepst.Practicehours ?? 0,
                            Trialhours = epepst.Trialhours ?? 0,
                            Totalhours = (epepst.TheoryCourse ?? 0) + (epepst.Practicehours ?? 0),
                            TheoryWeeklyNum = epepst.TheoryWeeklyNum ?? 0,
                            PracticeWeeklyNum = epepst.PracticeWeeklyNum ?? 0,
                            TrialWeeklyNum = epepst.TrialWeeklyNum ?? 0,
                            SchoolweeksNum = (epepst.TheoryWeeklyNum ?? 0) + (epepst.PracticeWeeklyNum ?? 0),
                            WeeklyHours = epepst.WeeklyHours ?? 0,
                            WeeklyNum = epepst.WeeklyNum ?? 0,
                            StartWeeklyNum = epepst.StartWeeklyNum ?? 0,
                            EndWeeklyNum = epepst.EndWeeklyNum ?? 0,
                            StartEndWeeklyNum = (epepst.StartWeeklyNum ?? 0) + "-" + (epepst.EndWeeklyNum ?? 0),
                            DepartmentID = ep.DepartmentID,
                            DepartmentCode = epdep.No,
                            DepartmentName = epdep.Name,
                            CourseCollegeID = epdep.CollegeID,
                            CourseCollegeCode = epdep.CF_College.No,
                            CourseCollegeName = epdep.CF_College.Name,
                            IsSpecialtycore = epeppr.IsSpecialtycore ?? false,
                            IsCooperation = epeppr.IsCooperation ?? false,
                            IsRequired = epeppr.IsRequired ?? false,
                            IsElective = epeppr.IsElective ?? false,
                            IsNetworkCourse = epeppr.IsNetworkCourse ?? false,
                            IsMainCourse = epeppr.IsMainCourse ?? false,
                            IsNeedMaterial = ep.IsNeedMaterial ?? false,
                            CourseFineID = epeppr.CourseFineID,
                            PracticeTypeID = epeppr.PracticeTypeID,
                            TeachinglanguageID = epeppr.TeachinglanguageID,
                            ExaminationModeID = epeppr.ExaminationModeID,
                            ResultTypeID = ep.ResultTypeID,
                            HandleModeID = ep.HandleModeID,
                            TeachingModeIDList = ep.EM_ExecutablePlanTeachingModeType.Select(x => x.TeachingModeID).ToList(),
                            TeachingPlaceIDList = ep.EM_ExecutablePlanTeachingPlace.Select(x => x.TeachingPlace).ToList(),
                            PlanRange = grpr.PlanRange == null ? 0 : grpr.PlanRange,
                            StudentCount = grstu.StudentCount == null ? 0 : grstu.StudentCount,
                            PlanTypeID = ep.SpecialtyPlanID == null ? (int)EM_PlanType.Outside : (int)EM_PlanType.Inside,
                            ExecuteStatus = ep.RecordStatus,
                            Remark = ep.Remark,
                            RecordStatus = ep.RecordStatus,
                            CreateUserID = ep.CreateUserID,
                            CreateTime = ep.CreateTime,
                            ModifyUserID = ep.ModifyUserID,
                            ModifyTime = ep.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的未新增专业计划信息（统计对应的学生人数）
        /// 注：普教版本（年级专业）
        /// </summary>
        /// <param name="expSpecialtyPlan"></param>
        /// <param name="expGrademajor"></param>
        /// <param name="expStudent"></param>
        /// <param name="expExecutablePlan"></param>
        /// <returns></returns>
        public IQueryable<SpecialtyPlanView> GetSpecialtyPlanViewNoAddQueryable(Expression<Func<EM_SpecialtyPlan, bool>> expSpecialtyPlan,
                Expression<Func<CF_Grademajor, bool>> expGrademajor, Expression<Func<CF_Student, bool>> expStudent, 
                Expression<Func<EM_ExecutablePlan, bool>> expExecutablePlan)
        {
            var query = from sp in SpecialtyPlanRepository.GetList(expSpecialtyPlan)
                        join spst in SpecialtyPlanTeachingSettingRepository.Entities
                        on sp.SpecialtyPlanID equals spst.SpecialtyPlanID into tempspst
                        from spspst in tempspst.DefaultIfEmpty()
                        join sy in SchoolyearRepository.Entities
                        on sp.SchoolyearID equals sy.SchoolyearID
                        join cou in CoursematerialRepository.Entities
                        on sp.CoursematerialID equals cou.CoursematerialID
                        join dep in DepartmentRepository.Entities
                        on sp.DepartmentID equals dep.DepartmentID into tempdep
                        from spdep in tempdep.DefaultIfEmpty()
                        join gr in GrademajorRepository.GetList(expGrademajor)
                        on sp.GrademajorID equals gr.GrademajorID
                        join stsy in SchoolyearRepository.Entities
                        on new { SchoolyearID = gr.GradeID.Value, SchoolcodeID = gr.SemesterID.Value }
                        equals new { SchoolyearID = stsy.Years, SchoolcodeID = stsy.SchoolcodeID } into tempstsy
                        from startsy in tempstsy.DefaultIfEmpty()
                        join grsy in SchoolyearRepository.Entities
                        on gr.GraduateSchoolyearID equals grsy.SchoolyearID into tempgrsy
                        from gradsy in tempgrsy.DefaultIfEmpty()
                        join fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        join spe in SpecialtyRepository.Entities
                        on new { StandardID = fa.StandardID, EducationID = fa.EducationID, LearningformID = fa.LearningformID, LearnSystem = fa.LearnSystem }
                        equals new { StandardID = spe.StandardID, EducationID = spe.EducationID, LearningformID = spe.LearningformID, LearnSystem = spe.LearnSystem }
                        join dicst in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(CF_Standard).Name)
                        on fa.StandardID equals dicst.Value into tempst
                        from fast in tempst.DefaultIfEmpty()
                        join col in CollegeRepository.Entities
                        on fa.CollegeID equals col.CollegeID
                        join cam in CampusRepository.Entities
                        on col.CampusID equals cam.CampusID
                        join stu in
                            (
                                from stu in StudentRepository.GetList(expStudent)
                                join cl in ClassmajorRepository.Entities
                                on stu.ClassmajorID equals cl.ClassmajorID
                                join gr in GrademajorRepository.GetList(expGrademajor)
                                on cl.GrademajorID equals gr.GrademajorID
                                group new { stu, gr } by gr.GrademajorID into gstu
                                select new
                                {
                                    GrademajorID = gstu.Key,
                                    StudentCount = gstu.Count()
                                }
                            )
                        on gr.GrademajorID equals stu.GrademajorID into tempstu
                        from grstu in tempstu.DefaultIfEmpty()
                        join ep in
                            (
                                from ep in ExecutablePlanRepository.GetList(expExecutablePlan)
                                join eppr in ExecutablePlanProfileRepository.Entities
                                on ep.ExecutablePlanID equals eppr.ExecutablePlanID into tempeppr
                                from epeppr in tempeppr.DefaultIfEmpty()
                                join epst in ExecutablePlanTeachingSettingRepository.Entities
                                on ep.ExecutablePlanID equals epst.ExecutablePlanID into tempepst
                                from epepst in tempepst.DefaultIfEmpty()
                                select new
                                {
                                    ExecutablePlanID = ep.ExecutablePlanID,
                                    SpecialtyPlanID = ep.SpecialtyPlanID,
                                    SchoolyearID = ep.SchoolyearID,
                                    GrademajorID = ep.GrademajorID,
                                    CoursematerialID = ep.CoursematerialID,
                                    CourseTypeID = ep.CourseTypeID,
                                    Credit = epepst.Credit,
                                    DepartmentID = ep.DepartmentID,
                                    HandleModeID = ep.HandleModeID
                                }
                            )
                        on new { SchoolyearID = sp.SchoolyearID, GrademajorID = sp.GrademajorID, CoursematerialID = sp.CoursematerialID }
                        equals new { SchoolyearID = ep.SchoolyearID, GrademajorID = ep.GrademajorID, CoursematerialID = ep.CoursematerialID } into tempep
                        from spep in tempep.DefaultIfEmpty()
                        where spep.ExecutablePlanID == null
                        select new SpecialtyPlanView
                        {
                            SpecialtyPlanID = sp.SpecialtyPlanID,
                            PlanApplicationID = sp.PlanApplicationID,
                            SchoolyearID = sp.SchoolyearID,
                            SchoolyearCode = sy.Code,
                            YearID = sy.Years,
                            Value = sy.Value,
                            SchoolyearNumID = (sy.Value - ((gr.GradeID * 2 - 1) + gr.SemesterID - 1)) / 2 + 1,
                            SchoolcodeID = sy.SchoolcodeID,
                            StarttermID = sy.Value - ((gr.GradeID * 2 - 1) + gr.SemesterID - 1) + 1,
                            GrademajorID = sp.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            GradeID = gr.GradeID,
                            FacultymajorID = gr.FacultymajorID,
                            FacultymajorCode = fa.Code,
                            FacultymajorName = fa.Name,
                            CollegeID = fa.CollegeID,
                            CollegeCode = col.No,
                            CollegeName = col.Name,
                            CampusID = col.CampusID,
                            CampusNo = cam.No,
                            CampusName = cam.Name,
                            StartSchoolyearID = startsy.SchoolyearID,
                            StartSchoolyearCode = startsy.Code,
                            StartValue = startsy.Value,
                            StartSchoolcodeID = gr.SemesterID,
                            GraduatingSemesterID = gr.GraduateSchoolyearID,
                            GraduatingSemesterCode = gradsy.Code,
                            GraduatingSemesterValue = gradsy.Value,
                            SpecialtyID = spe.SpecialtyID,
                            StandardID = fa.StandardID,
                            StandardName = fast.Name,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            CoursematerialID = sp.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            CourseStructureID = sp.CourseStructureID,
                            CourseCategoryID = sp.CourseCategoryID,
                            CourseTypeID = sp.CourseTypeID,
                            CourseQualityID = sp.CourseQualityID,
                            Credit = spspst.Credit ?? 0,
                            TheoryCourse = spspst.TheoryCourse ?? 0,
                            Practicehours = spspst.Practicehours ?? 0,
                            Trialhours = spspst.Trialhours ?? 0,
                            Totalhours = (spspst.TheoryCourse ?? 0) + (spspst.Practicehours ?? 0),
                            TheoryWeeklyNum = spspst.TheoryWeeklyNum ?? 0,
                            PracticeWeeklyNum = spspst.PracticeWeeklyNum ?? 0,
                            TrialWeeklyNum = spspst.TrialWeeklyNum ?? 0,
                            SchoolweeksNum = (spspst.TheoryWeeklyNum ?? 0) + (spspst.PracticeWeeklyNum ?? 0),
                            WeeklyHours = spspst.WeeklyHours ?? 0,
                            WeeklyNum = spspst.WeeklyNum ?? 0,
                            StartWeeklyNum = spspst.StartWeeklyNum ?? 0,
                            EndWeeklyNum = spspst.EndWeeklyNum ?? 0,
                            StartEndWeeklyNum = (spspst.StartWeeklyNum ?? 0) + "-" + (spspst.EndWeeklyNum ?? 0),
                            DepartmentID = sp.DepartmentID,
                            DepartmentCode = spdep.No,
                            DepartmentName = spdep.Name,
                            CourseCollegeID = spdep.CollegeID,
                            CourseCollegeCode = spdep.CF_College.No,
                            CourseCollegeName = spdep.CF_College.Name,
                            IsSpecialtycore = sp.IsSpecialtycore ?? false,
                            IsCooperation = sp.IsCooperation ?? false,
                            IsRequired = sp.IsRequired ?? false,
                            IsElective = sp.IsElective ?? false,
                            IsNetworkCourse = sp.IsNetworkCourse ?? false,
                            IsMainCourse = sp.IsMainCourse ?? false,
                            IsNeedMaterial = sp.IsNeedMaterial ?? false,
                            CourseFineID = sp.CourseFineID,
                            PracticeTypeID = sp.PracticeTypeID,
                            TeachinglanguageID = sp.TeachinglanguageID,
                            ExaminationModeID = sp.ExaminationModeID,
                            ResultTypeID = sp.ResultTypeID,
                            HandleModeID = sp.HandleModeID,
                            TeachingModeIDList = sp.EM_SpecialtyPlanTeachingModeType.Select(x => x.TeachingModeID).ToList(),
                            TeachingPlaceIDList = sp.EM_SpecialtyPlanTeachingPlace.Select(x => x.TeachingPlace).ToList(),
                            StudentCount = grstu.StudentCount == null ? 0 : grstu.StudentCount,
                            Remark = sp.Remark,
                            RecordStatus = sp.RecordStatus,
                            CreateUserID = sp.CreateUserID,
                            CreateTime = sp.CreateTime,
                            ModifyUserID = sp.ModifyUserID,
                            ModifyTime = sp.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的未新增专业计划信息（统计对应的学生人数）
        /// 注：成教版本（入学学年学期、专业信息）
        /// </summary>
        /// <param name="expGrademajor"></param>
        /// <param name="expSpecialtyPlan"></param>
        /// <param name="expStudent"></param>
        /// <param name="expExecutablePlan"></param>
        /// <returns></returns>
        public IQueryable<SpecialtyPlanView> GetAdultSpecialtyPlanViewNoAddQueryable(Expression<Func<CF_Grademajor, bool>> expGrademajor,
                Expression<Func<EM_SpecialtyPlan, bool>> expSpecialtyPlan, Expression<Func<CF_Student, bool>> expStudent, 
                Expression<Func<EM_ExecutablePlan, bool>> expExecutablePlan)
        {
            var query = from gr in GrademajorRepository.GetList(expGrademajor)
                        join startsy in SchoolyearRepository.Entities
                        on new { GradeID = gr.GradeID.Value, StartSchoolcodeID = gr.SemesterID.Value }
                        equals new { GradeID = startsy.Years, StartSchoolcodeID = startsy.SchoolcodeID }
                        join grsy in SchoolyearRepository.Entities
                        on gr.GraduateSchoolyearID equals grsy.SchoolyearID into tempgrsy
                        from gradsy in tempgrsy.DefaultIfEmpty()
                        join fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        join spe in SpecialtyRepository.Entities
                        on new { StandardID = fa.StandardID, EducationID = fa.EducationID, LearningformID = fa.LearningformID, LearnSystem = fa.LearnSystem }
                        equals new { StandardID = spe.StandardID, EducationID = spe.EducationID, LearningformID = spe.LearningformID, LearnSystem = spe.LearnSystem }
                        join dicst in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(CF_Standard).Name)
                        on fa.StandardID equals dicst.Value into tempst
                        from fast in tempst.DefaultIfEmpty()
                        join col in CollegeRepository.Entities
                        on fa.CollegeID equals col.CollegeID
                        join cam in CampusRepository.Entities
                        on col.CampusID equals cam.CampusID
                        join sp in SpecialtyPlanRepository.GetList(expSpecialtyPlan)
                        on new { StartSchoolyearID = startsy.SchoolyearID, SpecialtyID = spe.SpecialtyID }
                        equals new { StartSchoolyearID = sp.StartSchoolyearID.Value, SpecialtyID = sp.SpecialtyID.Value }
                        join spst in SpecialtyPlanTeachingSettingRepository.Entities
                        on sp.SpecialtyPlanID equals spst.SpecialtyPlanID into tempspst
                        from spspst in tempspst.DefaultIfEmpty()
                        join sy in SchoolyearRepository.Entities
                        on sp.SchoolyearID equals sy.SchoolyearID
                        join cou in CoursematerialRepository.Entities
                        on sp.CoursematerialID equals cou.CoursematerialID
                        join dep in DepartmentRepository.Entities
                        on sp.DepartmentID equals dep.DepartmentID into tempdep
                        from spdep in tempdep.DefaultIfEmpty()
                        join stu in
                            (
                                from stu in StudentRepository.GetList(expStudent)
                                join cl in ClassmajorRepository.Entities
                                on stu.ClassmajorID equals cl.ClassmajorID
                                join gr in GrademajorRepository.GetList(expGrademajor)
                                on cl.GrademajorID equals gr.GrademajorID
                                group new { stu, gr } by gr.GrademajorID into gstu
                                select new
                                {
                                    GrademajorID = gstu.Key,
                                    StudentCount = gstu.Count()
                                }
                            )
                        on gr.GrademajorID equals stu.GrademajorID into tempstu
                        from grstu in tempstu.DefaultIfEmpty()
                        join ep in
                            (
                                from ep in ExecutablePlanRepository.GetList(expExecutablePlan)
                                join eppr in ExecutablePlanProfileRepository.Entities
                                on ep.ExecutablePlanID equals eppr.ExecutablePlanID into tempeppr
                                from epeppr in tempeppr.DefaultIfEmpty()
                                join epst in ExecutablePlanTeachingSettingRepository.Entities
                                on ep.ExecutablePlanID equals epst.ExecutablePlanID into tempepst
                                from epepst in tempepst.DefaultIfEmpty()
                                select new
                                {
                                    ExecutablePlanID = ep.ExecutablePlanID,
                                    SpecialtyPlanID = ep.SpecialtyPlanID,
                                    SchoolyearID = ep.SchoolyearID,
                                    GrademajorID = ep.GrademajorID,
                                    CoursematerialID = ep.CoursematerialID,
                                    CourseTypeID = ep.CourseTypeID,
                                    Credit = epepst.Credit,
                                    DepartmentID = ep.DepartmentID,
                                    HandleModeID = ep.HandleModeID
                                }
                            )
                        on new { SchoolyearID = sp.SchoolyearID, GrademajorID = gr.GrademajorID, CoursematerialID = sp.CoursematerialID }
                        equals new { SchoolyearID = ep.SchoolyearID, GrademajorID = ep.GrademajorID.Value, CoursematerialID = ep.CoursematerialID } into tempep
                        from spep in tempep.DefaultIfEmpty()
                        where spep.ExecutablePlanID == null
                        select new SpecialtyPlanView
                        {
                            SpecialtyPlanID = sp.SpecialtyPlanID,
                            PlanApplicationID = sp.PlanApplicationID,
                            SchoolyearID = sp.SchoolyearID,
                            SchoolyearCode = sy.Code,
                            YearID = sy.Years,
                            Value = sy.Value,
                            SchoolyearNumID = (sy.Value - ((gr.GradeID * 2 - 1) + gr.SemesterID - 1)) / 2 + 1,
                            SchoolcodeID = sy.SchoolcodeID,
                            StarttermID = sy.Value - ((gr.GradeID * 2 - 1) + gr.SemesterID - 1) + 1,
                            GrademajorID = gr.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            GradeID = gr.GradeID,
                            FacultymajorID = gr.FacultymajorID,
                            FacultymajorCode = fa.Code,
                            FacultymajorName = fa.Name,
                            CollegeID = fa.CollegeID,
                            CollegeCode = col.No,
                            CollegeName = col.Name,
                            CampusID = col.CampusID,
                            CampusNo = cam.No,
                            CampusName = cam.Name,
                            StartSchoolyearID = sp.StartSchoolyearID,
                            StartSchoolyearCode = startsy.Code,
                            StartValue = startsy.Value,
                            StartSchoolcodeID = gr.SemesterID,
                            GraduatingSemesterID = gr.GraduateSchoolyearID,
                            GraduatingSemesterCode = gradsy.Code,
                            GraduatingSemesterValue = gradsy.Value,
                            SpecialtyID = sp.SpecialtyID,
                            StandardID = fa.StandardID,
                            StandardName = fast.Name,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            CoursematerialID = sp.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            CourseStructureID = sp.CourseStructureID,
                            CourseCategoryID = sp.CourseCategoryID,
                            CourseTypeID = sp.CourseTypeID,
                            CourseQualityID = sp.CourseQualityID,
                            Credit = spspst.Credit ?? 0,
                            TheoryCourse = spspst.TheoryCourse ?? 0,
                            Practicehours = spspst.Practicehours ?? 0,
                            Trialhours = spspst.Trialhours ?? 0,
                            Totalhours = (spspst.TheoryCourse ?? 0) + (spspst.Practicehours ?? 0),
                            TheoryWeeklyNum = spspst.TheoryWeeklyNum ?? 0,
                            PracticeWeeklyNum = spspst.PracticeWeeklyNum ?? 0,
                            TrialWeeklyNum = spspst.TrialWeeklyNum ?? 0,
                            SchoolweeksNum = (spspst.TheoryWeeklyNum ?? 0) + (spspst.PracticeWeeklyNum ?? 0),
                            WeeklyHours = spspst.WeeklyHours ?? 0,
                            WeeklyNum = spspst.WeeklyNum ?? 0,
                            StartWeeklyNum = spspst.StartWeeklyNum ?? 0,
                            EndWeeklyNum = spspst.EndWeeklyNum ?? 0,
                            StartEndWeeklyNum = (spspst.StartWeeklyNum ?? 0) + "-" + (spspst.EndWeeklyNum ?? 0),
                            DepartmentID = sp.DepartmentID,
                            DepartmentCode = spdep.No,
                            DepartmentName = spdep.Name,
                            CourseCollegeID = spdep.CollegeID,
                            CourseCollegeCode = spdep.CF_College.No,
                            CourseCollegeName = spdep.CF_College.Name,
                            IsSpecialtycore = sp.IsSpecialtycore ?? false,
                            IsCooperation = sp.IsCooperation ?? false,
                            IsRequired = sp.IsRequired ?? false,
                            IsElective = sp.IsElective ?? false,
                            IsNetworkCourse = sp.IsNetworkCourse ?? false,
                            IsMainCourse = sp.IsMainCourse ?? false,
                            IsNeedMaterial = sp.IsNeedMaterial ?? false,
                            CourseFineID = sp.CourseFineID,
                            PracticeTypeID = sp.PracticeTypeID,
                            TeachinglanguageID = sp.TeachinglanguageID,
                            ExaminationModeID = sp.ExaminationModeID,
                            ResultTypeID = sp.ResultTypeID,
                            HandleModeID = sp.HandleModeID,
                            TeachingModeIDList = sp.EM_SpecialtyPlanTeachingModeType.Select(x => x.TeachingModeID).ToList(),
                            TeachingPlaceIDList = sp.EM_SpecialtyPlanTeachingPlace.Select(x => x.TeachingPlace).ToList(),
                            StudentCount = grstu.StudentCount == null ? 0 : grstu.StudentCount,
                            Remark = sp.Remark,
                            RecordStatus = sp.RecordStatus,
                            CreateUserID = sp.CreateUserID,
                            CreateTime = sp.CreateTime,
                            ModifyUserID = sp.ModifyUserID,
                            ModifyTime = sp.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的计划范围信息(根据执行计划信息，统计各班级信息对应的学生人数)
        /// </summary>
        /// <param name="expExecutablePlan"></param>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<ClassmajorView> GetPlanRangeViewQueryable(Expression<Func<EM_ExecutablePlan, bool>> expExecutablePlan,
                Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from ep in ExecutablePlanRepository.GetList(expExecutablePlan)
                        join gr in GrademajorRepository.Entities
                        on ep.GrademajorID equals gr.GrademajorID
                        join cl in ClassmajorRepository.Entities
                        on gr.GrademajorID equals cl.GrademajorID
                        join fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        join col in CollegeRepository.Entities
                        on fa.CollegeID equals col.CollegeID
                        join sc in
                            (
                            from stu in StudentRepository.GetList(expStudent)
                            group stu by stu.CF_Classmajor.ClassmajorID into gstu
                            select new
                            {
                                ClassmajorID = gstu.Key,
                                StudentCount = gstu.Count()
                            })
                        on cl.ClassmajorID equals sc.ClassmajorID into tempsc
                        from clsc in tempsc.DefaultIfEmpty()
                        join us in UserRepository.Entities
                        on cl.UserID equals us.UserID into tempus
                        from clus in tempus.DefaultIfEmpty()
                        join au in UserRepository.Entities
                        on cl.AssistantUserID equals au.UserID into tempau
                        from clau in tempau.DefaultIfEmpty()
                        select new ClassmajorView
                        {
                            ClassmajorID = cl.ClassmajorID,
                            No = cl.No,
                            Name = cl.Name,
                            Abbreviation = cl.Abbreviation,
                            EnglishName = cl.EnglishName,
                            ClassNum = cl.ClassNum ?? 1,
                            FacultymajorID = gr.FacultymajorID,
                            FacultymajorCode = fa.Code,
                            FacultymajorName = fa.Name,
                            GrademajorID = gr.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            CampusID = col.CampusID,
                            CampusNo = col.CF_Campus.No,
                            CampusName = col.CF_Campus.Name,
                            CollegeID = fa.CollegeID,
                            CollegeCode = col.No,
                            CollegeName = col.Name,
                            GradeID = gr.GradeID,
                            SemesterID = gr.SemesterID,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            GraduateSchoolyearID = gr.GraduateSchoolyearID,
                            GraduateValue = gr.CF_Schoolyear.Value,
                            GraduateSchoolyearCode = gr.CF_Schoolyear.Code,
                            UserID = cl.UserID,
                            UserName = clus.Name,
                            AssistantUserID = cl.AssistantUserID,
                            AssistantUserName = clau.Name,
                            Fixedclassroom = cl.Fixedclassroom,
                            StudentCount = clsc.StudentCount == null ? 0 : clsc.StudentCount,
                            Remark = cl.Remark,
                            RecordStatus = cl.RecordStatus,
                            CreateUserID = cl.CreateUserID,
                            CreateTime = cl.CreateTime,
                            ModifyUserID = cl.ModifyUserID,
                            ModifyTime = cl.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询执行计划对应的学生信息BaseStudentView(根据执行计划信息、学生信息)
        /// </summary>
        /// <param name="expExecutablePlan"></param>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<BaseStudentView> GetBaseStudentViewQueryable(Expression<Func<EM_ExecutablePlan, bool>> expExecutablePlan,
                Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from ep in ExecutablePlanRepository.GetList(expExecutablePlan)
                        join gr in GrademajorRepository.Entities
                        on ep.GrademajorID equals gr.GrademajorID
                        join fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        join col in CollegeRepository.Entities
                        on fa.CollegeID equals col.CollegeID
                        join cl in ClassmajorRepository.Entities
                        on gr.GrademajorID equals cl.GrademajorID
                        join stu in StudentRepository.GetList(expStudent)
                        on cl.ClassmajorID equals stu.ClassmajorID
                        join us in UserRepository.Entities
                        on stu.UserID equals us.UserID
                        select new BaseStudentView
                        {
                            UserID = stu.UserID,
                            LoginID = us.LoginID,
                            UserName = us.Name,
                            SexID = stu.SexID,
                            IDNumber = stu.IDNumber,
                            StudentStatus = stu.StudentStatus,
                            InSchoolStatusID = stu.InSchoolStatusID,
                            ClassmajorID = stu.ClassmajorID,
                            ClassmajorCode = cl.No,
                            ClassmajorName = cl.Name,
                            ClassNum = cl.ClassNum == null ? 1 : cl.ClassNum,
                            SchoolyearID = gr.GradeID,
                            GrademajorID = cl.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            FacultymajorID = gr.FacultymajorID,
                            FacultymajorCode = fa.Code,
                            FacultymajorName = fa.Name,
                            CollegeID = fa.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name
                        };

            return query;
        }

        /// <summary>
        /// 查询执行计划信息对应的授课方式
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<int?> GetTeachingModeTypeQueryble(Expression<Func<EM_ExecutablePlan, bool>> exp)
        {
            var query = from ep in ExecutablePlanRepository.GetList(exp)
                        join eptm in ExecutablePlanTeachingModeTypeRepository.Entities
                        on ep.ExecutablePlanID equals eptm.ExecutablePlanID
                        join dictm in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(CF_TeachingMode).Name)
                        on eptm.TeachingModeID equals dictm.Value
                        select dictm.Value;

            return query;
        }

        /// <summary>
        /// 查询执行计划信息对应的授课方式
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<int?> GetTeachingPlaceQueryble(Expression<Func<EM_ExecutablePlan, bool>> exp)
        {
            var query = from ep in ExecutablePlanRepository.GetList(exp)
                        join eptp in ExecutablePlanTeachingPlaceRepository.Entities
                        on ep.ExecutablePlanID equals eptp.ExecutablePlanID
                        join dictp in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(EMIS.ViewModel.EM_TeachingPlace).Name)
                        on eptp.TeachingPlace equals dictp.Value
                        select dictp.Value;

            return query;
        }

        /// <summary>
        /// 查询对应的专业信息CF_Specialty（根据年级专业信息）
        /// </summary>
        /// <param name="expGrademajor"></param>
        /// <returns></returns>
        public IQueryable<CF_Specialty> GetSpecialtyQueryable(Expression<Func<CF_Grademajor, bool>> expGrademajor)
        {
            var query = from gr in GrademajorRepository.GetList(expGrademajor)
                        join fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        join spe in SpecialtyRepository.Entities
                        on new { StandardID = fa.StandardID, EducationID = fa.EducationID, LearningformID = fa.LearningformID, LearnSystem = fa.LearnSystem }
                        equals new { StandardID = spe.StandardID, EducationID = spe.EducationID, LearningformID = spe.LearningformID, LearnSystem = spe.LearnSystem }
                        select spe;

            return query;
        }

        /// <summary>
        /// 查询年级专业对应的班级范围信息(根据年级专业信息)
        /// </summary>
        /// <param name="expGrademajor"></param>
        /// <returns></returns>
        public IQueryable<CF_Classmajor> GetClassmajorRangeQueryable(Expression<Func<CF_Grademajor, bool>> expGrademajor)
        {
            var query = from gr in GrademajorRepository.GetList(expGrademajor)
                        join cl in ClassmajorRepository.Entities
                        on gr.GrademajorID equals cl.GrademajorID
                        select cl;

            return query;
        }

        /// <summary>
        /// 查询年级专业对应的学生范围信息(根据年级专业信息，不区分在校、非在校)
        /// </summary>
        /// <param name="expGrademajor"></param>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<CF_Student> GetStudentRangeQueryable(Expression<Func<CF_Grademajor, bool>> expGrademajor)
        {
            var query = from gr in GrademajorRepository.GetList(expGrademajor)
                        join cl in ClassmajorRepository.Entities
                        on gr.GrademajorID equals cl.GrademajorID
                        join stu in StudentRepository.Entities
                        on cl.ClassmajorID equals stu.ClassmajorID
                        select stu;

            return query;
        }

        /// <summary>
        /// 查询年级专业对应的学生范围信息(根据年级专业信息、学生信息)
        /// </summary>
        /// <param name="expGrademajor"></param>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<CF_Student> GetStudentRangeQueryable(Expression<Func<CF_Grademajor, bool>> expGrademajor, 
                Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from gr in GrademajorRepository.GetList(expGrademajor)
                        join cl in ClassmajorRepository.Entities
                        on gr.GrademajorID equals cl.GrademajorID
                        join stu in StudentRepository.GetList(expStudent)
                        on cl.ClassmajorID equals stu.ClassmajorID
                        select stu;

            return query;
        }

        /// <summary>
        /// 查询对应的教学任务班信息EM_EducationMissionClass(根据执行计划信息)
        /// </summary>
        /// <param name="expExecutablePlan"></param>
        /// <returns></returns>
        public IQueryable<EM_EducationMissionClass> GetEducationMissionClassQueryable(Expression<Func<EM_ExecutablePlan, bool>> exp)
        {
            var query = (
                            from ep in ExecutablePlanRepository.GetList(exp)
                            join gr in GrademajorRepository.Entities
                            on ep.GrademajorID equals gr.GrademajorID
                            join cl in ClassmajorRepository.Entities
                            on gr.GrademajorID equals cl.GrademajorID
                            join educl in EducationMissionClassRepository.Entities
                            on new { SchoolyearID = ep.SchoolyearID, CoursematerialID = ep.CoursematerialID }
                            equals new { SchoolyearID = educl.EM_EducationMission.SchoolyearID, CoursematerialID = educl.CoursematerialID }
                            from edu_cl in educl.CF_Classmajor
                            where cl.ClassmajorID == edu_cl.ClassmajorID
                            select educl
                        )
                     .Include(x => x.EM_EducationMission)
                     .Include(x => x.EM_EducationMissionClassTeachingSetting)
                     .Include(x => x.CF_Classmajor)
                     .Include(x => x.EM_EducationSchedulingClass)
                     .Include(x => x.EM_EducationSchedulingClass.Select(s => s.CF_Student))
                     .Include(x => x.EM_EducationMissionClassSettings)
                     .Include(x => x.EM_MissionClassTeacher)
                     .Include(x => x.EM_CourseProcess);

            return query;
        }

        /// <summary>
        /// 查询课程信息中授课方式分班信息（根据课程信息）
        /// </summary>
        /// <param name="expCoursematerial"></param>
        /// <returns></returns>
        public IQueryable<int?> GetSeparateModeTypeQueryble(Expression<Func<EM_Coursematerial, bool>> expCoursematerial)
        {
            var query = from cou in CoursematerialRepository.GetList(expCoursematerial)
                        join cg in ClassGroupingRepository.Entities
                        on cou.ClassGroupingID equals cg.ClassGroupingID
                        join cgst in ClassGroupingSettingsRepository.Entities
                        on cg.ClassGroupingID equals cgst.ClassGroupingID
                        join dictm in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(CF_TeachingMode).Name)
                        on cgst.TeachingModeID equals dictm.Value
                        select dictm.Value;

            return query;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="studentExp"></param>
        /// <returns></returns>
        public IQueryable<CoursematerialView> GetLastCoursematerialViewQueryable(Expression<Func<CF_Student, bool>> studentExp)
        {
            var courseQuery = CoursematerialDAL.Value.GetCoursematerialViewQueryable(x => true);
            var query = (from plan in ExecutablePlanRepository.Entities
                         from schoolyear in SchoolyearRepository.Entities.Where(x => x.SchoolyearID == plan.SchoolyearID)
                         from classmajor in ClassmajorRepository.Entities.Where(x => x.GrademajorID == plan.GrademajorID)
                         from student in StudentRepository.Entities.Where(x => x.ClassmajorID == classmajor.ClassmajorID)
                             .Where(studentExp)
                         from course in courseQuery.Where(x => x.CoursematerialID == plan.CoursematerialID)
                         group new { course, schoolyear } by plan.CoursematerialID into g
                         select g.OrderByDescending(x => x.schoolyear.Value)
                             .Select(x => x.course)
                             .FirstOrDefault()
                             );

            return query;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public IQueryable<LastCourseStarttermView> GetLastCourseStarttermViewQueryable()
        {
            var query = (from plan in ExecutablePlanRepository.Entities 
                         from profile in ExecutablePlanProfileRepository.Entities.Where(x => x.ExecutablePlanID == plan.ExecutablePlanID) 
                         from setting in ExecutablePlanTeachingSettingRepository.Entities.Where(x => x.ExecutablePlanID == plan.ExecutablePlanID)
                         from schoolyear in SchoolyearRepository.Entities.Where(x => x.SchoolyearID == plan.SchoolyearID)
                         from classmajor in ClassmajorRepository.Entities.Where(x => x.GrademajorID == plan.GrademajorID)
                         from student in classmajor.CF_Student
                         from grade in GrademajorRepository.Entities.Where(x => x.GrademajorID == classmajor.GrademajorID)
                         from gradeSchoolyear in SchoolyearRepository.Entities.Where(x => x.Years == grade.GradeID && x.SchoolcodeID == grade.SemesterID)
                         from temp in
                             (
                                 from plan1 in ExecutablePlanRepository.Entities
                                 from schoolyear1 in SchoolyearRepository.Entities.Where(x => x.SchoolyearID == plan1.SchoolyearID)
                                 from classmajor1 in ClassmajorRepository.Entities.Where(x => x.GrademajorID == plan1.GrademajorID)
                                 from student1 in classmajor1.CF_Student
                                 group new { student1.UserID, plan1.CoursematerialID, SchoolyearValue = schoolyear1.Value }
                                 by new { student1.UserID, plan1.CoursematerialID } into g
                                 select new { UserID = g.Key.UserID, CoursematerialID = g.Key.CoursematerialID, MaxSchoolyearValue = g.Max(x => x.SchoolyearValue) })
                                 .Where(x => x.CoursematerialID == plan.CoursematerialID && x.MaxSchoolyearValue == schoolyear.Value
                                 && x.UserID == student.UserID) 
                         select new
                         {
                             SchoolyearID = plan.SchoolyearID,
                             SchoolyearNumID = ((schoolyear.Value - gradeSchoolyear.Value) / 2) + 1,
                             CoursematerialID = plan.CoursematerialID,
                             DepartmentID = plan.DepartmentID,
                             ClassName = plan.DefaultClassName,
                             CourseTypeID = plan.CourseTypeID,
                             ExaminationModeID = profile.ExaminationModeID,
                             HandleModeID = plan.HandleModeID,
                             Credit = setting.Credit,
                             TotalHours = (setting.TheoryCourse ?? 0) + (setting.Practicehours ?? 0),
                             ResultTypeID = plan.ResultTypeID,
                             StarttermID = (schoolyear.Value - gradeSchoolyear.Value + 1),
                             UserID = student.UserID
                         } into s
                         select new LastCourseStarttermView
                         {
                             SchoolyearID = s.SchoolyearID,
                             SchoolyearNumID = s.SchoolyearNumID,
                             CoursematerialID = s.CoursematerialID,
                             DepartmentID = s.DepartmentID,
                             ClassName = s.ClassName,
                             CourseTypeID = s.CourseTypeID,
                             ExaminationModeID = s.ExaminationModeID,
                             HandleModeID = s.HandleModeID,
                             Credit = s.Credit,
                             TotalHours = s.TotalHours,
                             ResultTypeID = s.ResultTypeID,
                             StarttermID = s.StarttermID,
                             UserID = s.UserID
                         }
                         );

            return query;
        }

    }
}