JW/EMiS.DataLogic/EducationManage/EducationMissionClassDAL.cs

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


namespace EMIS.DataLogic.EducationManage
{
    public partial class EducationMissionClassDAL 
    {
        public EducationMissionClassRepository EducationMissionClassRepository { get; set; }
        public EducationMissionClassTeachingSettingRepository EducationMissionClassTeachingSettingRepository { get; set; }
        public ExecutableOptionalCourseRepository ExecutableOptionalCourseRepository { get; set; }
        public ExecutableFreeSelectionCouseRepository ExecutableFreeSelectionCouseRepository { get; set; }
        public ExecutablePlanRepository ExecutablePlanRepository { get; set; }
        public DepartmentRepository DepartmentRepository { get; set; }
        public DictionaryItemRepository DictionaryItemRepository { get; set; }
        public EducationMissionClassSettingsRepository EducationMissionClassSettingsRepository { get; set; }
        public MissionClassTeacherRepository MissionClassTeacherRepository { get; set; }
        public EducationSchedulingClassRepository EducationSchedulingClassRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public CourseProcessRepository CourseProcessRepository { get; set; }
        public EducationMissionRepository EducationMissionRepository { get; set; }
        public EducationSchedulingRepository EducationSchedulingRepository { get; set; }
        public ClassroomRepository ClassroomRepository { get; set; }
        public InSchoolSettingRepository InSchoolSettingRepository { get; set; }
        public CollegeScheduleStatusRepository CollegeScheduleStatusRepository { get; set; }
        public CoursesTimeRepository CoursesTimeRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public CoursematerialRepository CoursematerialRepository { get; set; }
        public RetakePlanStudentRepository RetakePlanStudentRepository { get; set; }
        public EducationSchedulingTeacherRepository EducationSchedulingTeacherRepository { get; set; }
        public EducationSchedulingWeekNumRepository EducationSchedulingWeekNumRepository { get; set; }

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

        /// <summary> 
        /// 查询教学任务班数据
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<EducationMissionClassView> GetEducationMissionClassViewQueryble(Expression<Func<EM_EducationMissionClass, bool>> missionClassExp)
        {
            //媚姐说：教学任务的查询不要过滤教学任务中没有在校学生
            //var inSchoolStatusIDList = inSchoolSettingRepository.GetList(x => x.IsSelected == true).Select(w => w.InSchoolStatusID).ToList();//获取选中的在校设定状态、
            var inSchoolStatusIDList = InSchoolSettingRepository.GetList(x => true).Select(w => w.InSchoolStatusID).ToList();//获取选中的在校设定状态、

            var query = from missionClass in EducationMissionClassRepository.GetList(missionClassExp)
                        from mission in EducationMissionRepository.Entities.Where(x => missionClass.EducationMissionID == x.EducationMissionID)
                        from department in DepartmentRepository.Entities.Where(x => mission.DepartmentID == x.DepartmentID)
                        from teachSetting in EducationMissionClassTeachingSettingRepository.Entities.Where(x => missionClass.EducationMissionClassID == x.EducationMissionClassID)
                        from classmajor in ClassmajorRepository.Entities.Where(x => missionClass.MainScheduleClassID == x.ClassmajorID).DefaultIfEmpty() 
                        from grade in GrademajorRepository.Entities.Where(x => classmajor.GrademajorID == x.GrademajorID).DefaultIfEmpty() 
                        from faculty in FacultymajorRepository.Entities.Where(x => grade.FacultymajorID == x.FacultymajorID).DefaultIfEmpty() 
                        from college in CollegeRepository.Entities.Where(x => department.CollegeID == x.CollegeID)
                        from schoolyear in SchoolyearRepository.Entities.Where(x => mission.SchoolyearID == x.SchoolyearID)
                        from course in CoursematerialRepository.Entities.Where(x => missionClass.CoursematerialID == x.CoursematerialID) 
                        from schedulingClass in
                            (
                                from sc in EducationSchedulingClassRepository.Entities
                                group sc by sc.EducationMissionClassID into g
                                select new
                                {
                                    EducationMissionClassID = g.Key,
                                    ClassNum = g.Sum(x => x.CF_Student.Where(p => inSchoolStatusIDList.Contains(p.InSchoolStatusID)).Count())
                                }
                            ).Where(x => missionClass.EducationMissionClassID == x.EducationMissionClassID).DefaultIfEmpty()
                        from gccp in
                            (from cp in CourseProcessRepository.Entities
                             group cp by cp.EducationMissionClassID into g
                             select new
                                 {
                                     EducationMissionClassID = g.Key,
                                     CourseProcessTimes = g.Sum(x => x.Times),
                                     WeeklyHours=g.Select(x=>x.Times).FirstOrDefault()
                                 }).Where(x => missionClass.EducationMissionClassID == x.EducationMissionClassID).DefaultIfEmpty()
                        from emc in ClassmajorRepository.Entities.Where(x => missionClass.MainScheduleClassID == x.ClassmajorID).DefaultIfEmpty()
                        from ecss in CollegeScheduleStatusRepository.Entities.Where(x => missionClass.EM_EducationMission.CollegeID == x.CollegeID 
                            && missionClass.EM_EducationMission.SchoolyearID == x.SchoolyearID).DefaultIfEmpty() 
                        select new EducationMissionClassView
                        {
                            EducationMissionID = missionClass.EducationMissionID,
                            EducationMissionClassID = missionClass.EducationMissionClassID,
                            TeachingModeID = missionClass.TeachingModeID ?? 1,
                            SchoolyearID = missionClass.EM_EducationMission.SchoolyearID,
                            YearID = missionClass.EM_EducationMission.CF_Schoolyear.Years,
                            SchoolyearCode = missionClass.EM_EducationMission.CF_Schoolyear.Code,
                            Name = missionClass.Name,
                            CoursematerialID = missionClass.CoursematerialID,
                            CourseCode = course.CourseCode,
                            CourseNameAb = course.Abbreviation,
                            CourseName = course.CourseName,
                            CampusID = college.CampusID,
                            CollegeID = college.CollegeID,
                            CollegeNo = college.No,
                            CollegeName = college.Name,
                            DepartmentID = department.DepartmentID,
                            DepartmentCode = department.No,
                            DepartmentName = department.Name,
                            GrademajorID = grade.GrademajorID,
                            GrademajorCode = grade.Code,
                            GrademajorName = grade.Name,
                            StandardID = faculty.StandardID,
                            LearnSystem = faculty.LearnSystem,
                            EducationID = faculty.EducationID,
                            LearningformID = faculty.LearningformID,
                            GradeYearID = grade.GradeID,
                            ClassroomID = missionClass.ClassroomID,
                            ClassroomCode = missionClass.CF_Classroom.Code,
                            ClassroomName = missionClass.CF_Classroom.Name,
                            ClassroomTypeID = missionClass.ClassroomTypeID,
                            CourseStructureID = missionClass.CourseStructureID,
                            CourseCategoryID = missionClass.CourseCategoryID,
                            CourseTypeID = missionClass.CourseTypeID,
                            CourseQualityID = missionClass.CourseQualityID,
                            ExaminationModeID = missionClass.ExaminationModeID,
                            TeachinglanguageID = missionClass.TeachinglanguageID,
                            HandleModeID = missionClass.HandleModeID,
                            IsNeedMaterial = missionClass.IsNeedMaterial ?? false,
                            OrderNo = missionClass.OrderNo ?? 0,
                            SchedulingClassNum = missionClass.EM_EducationSchedulingClass.Count,
                            ClassNum = (schedulingClass.EducationMissionClassID == null ? 0 : schedulingClass.ClassNum),
                            MainScheduleClassID = missionClass.MainScheduleClassID,
                            MainScheduleClassGrademajorID = emc.GrademajorID,
                            MainScheduleClassCollegeID = faculty.CollegeID,
                            SchedulingClass = missionClass.EM_EducationMission.ClassName, 
                            StartWeeklyNum = teachSetting.StartWeeklyNum,
                            EndWeeklyNum = teachSetting.EndWeeklyNum,
                            StartWeeklyNumReserved = missionClass.EM_EducationMissionClassSettings.Min(w => w.WeeklyNum).Value,
                            EndWeeklyNumReserved = missionClass.EM_EducationMissionClassSettings.Max(w => w.WeeklyNum).Value,
                            Credit = teachSetting.Credit,
                            TheoryCourse = teachSetting.TheoryCourse,
                            Practicehours = teachSetting.Practicehours,
                            Trialhours = teachSetting.Trialhours,
                            TheoryWeeklyNum = teachSetting.TheoryWeeklyNum,
                            PracticeWeeklyNum = teachSetting.PracticeWeeklyNum,
                            TrialWeeklyNum = teachSetting.TrialWeeklyNum,
                            WeeklyNum = teachSetting.WeeklyNum,
                            WeeklyHours = gccp.WeeklyHours == null ? teachSetting.WeeklyHours : gccp.WeeklyHours,
                            ApprovalStatus = missionClass.RecordStatus,
                            CourseProcessTimes = gccp.CourseProcessTimes,
                            CreateTime = missionClass.CreateTime,
                            CreateUserID = missionClass.CreateUserID,
                            ResultTypeID=missionClass.ResultTypeID,
                            Remark = missionClass.Remark,
                            IsCollegeScheduleSubmitted = (ecss.RecordStatus == (int)ES_CollegeScheduleStatusCode.Submited)
                        };
            return query;
        }

        /// <summary>
        /// 查询任务班
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<EM_EducationMissionClass> GetEducationMissionClass(Expression<Func<EM_EducationMissionClass, bool>> exp)
        {
            var query = (from emc in EducationMissionClassRepository.GetList(exp)
                         //join remc in educationMissionClassRepository.Entities
                         //   on emc.EducationMissionID equals remc.EducationMissionID
                         select emc)
                         .Include(x => x.EM_EducationMissionClassTeachingSetting)
                         .Include(x => x.EM_EducationMission)
                         .Include(x => x.EM_MissionClassTeacher)
                         .Include(x => x.EM_EducationMissionClassSettings)
                         .Include(x => x.EM_EducationSchedulingClass);

            return query;
        }


        /// <summary>
        /// 根据教学任务班查找班级信息
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public ClassmajorView GetClassmajorByEducationMissionClassID(Expression<Func<EM_EducationMissionClass, bool>> exp)
        {
            var query = from emc in EducationMissionClassRepository.GetList(exp)
                        from cla in emc.CF_Classmajor
                        select new ClassmajorView
                        {
                           ClassmajorID = cla.ClassmajorID,
                           GrademajorID = cla.GrademajorID,
                           CollegeID = emc.CF_Classroom.CollegeID
                        };
            return query.FirstOrDefault();
        }

        /// <summary>
        /// 通过传入教学任务班的查询条件，得到所有和它们相关的执行计划
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<EM_ExecutablePlan> GetRelatedExecutablePlan(Expression<Func<EM_EducationMissionClass, bool>> exp)
        {
            var query = (from emc in EducationMissionClassRepository.GetList(exp)
                         from emccc in emc.CF_Classmajor
                         join ep in ExecutablePlanRepository.Entities
                         on new { emc.EM_EducationMission.SchoolyearID, emccc.GrademajorID, emc.CoursematerialID, emc.HandleModeID }
                         equals new { ep.SchoolyearID, ep.GrademajorID, ep.CoursematerialID, ep.HandleModeID }
                         select ep).Distinct();

            return query;
        }

        public List<MissionClassTeacherView> GetMissionClassTeacherQuery(Expression<Func<EM_EducationMissionClass, bool>> exp)
        {
            var query = (from emc in EducationMissionClassRepository.GetList(exp)
                         from tec in emc.EM_MissionClassTeacher
                         select new MissionClassTeacherView
                         {
                             UserID = tec.CF_Staff.UserID,
                             EducationMissionClassID = emc.EducationMissionClassID,
                             Name = tec.CF_Staff.Sys_User.Name,
                             TeachingMethod=tec.TeachType
                         });
            return query.OrderBy(x=>x.EducationMissionClassID).OrderBy(x=>x.TeachingMethod).ToList();
        }

        /// <summary>
        /// 获取排课对应的老师
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public List<MissionClassTeacherView> GetEducationSchedulingTeacherQuery(Expression<Func<EM_EducationSchedulingClass, bool>> exp)
        {
            var query = (from escr in EducationSchedulingClassRepository.GetList(exp)
                         from esr in EducationSchedulingRepository.Entities.Where(x => x.EducationSchedulingClassID == escr.EducationSchedulingClassID)
                         from eswnr in EducationSchedulingWeekNumRepository.Entities.Where(x => x.EducationSchedulingID == esr.EducationSchedulingID)
                         from tec in EducationSchedulingTeacherRepository.Entities.Where(x => x.EducationSchedulingID == esr.EducationSchedulingID)
                         from cou in CoursesTimeRepository.Entities.Where(x => x.CoursesTimeID == esr.CoursesTimeID && x.RecordStatus > (int?)SYS_STATUS.UNUSABLE)
                         select new MissionClassTeacherView
                         {
                             UserID = tec.CF_Staff.UserID,
                             EducationMissionClassID = escr.EducationMissionClassID,
                             Name = tec.CF_Staff.Sys_User.Name,
                             TeachingMethod = tec.TeachingMethod,
                             Weekday = esr.Weekday,
                             WeekNum = eswnr.WeekNum,
                             StarTimes = cou.StartTimes,
                             CoursesTimeID = esr.CoursesTimeID,
                         });
            return query.OrderBy(x => x.EducationMissionClassID).OrderBy(x => x.TeachingMethod).Distinct().ToList();
        }

        public List<ClassView> GetClassmajorQuery(Expression<Func<EM_EducationMissionClass, bool>> exp)
        {
            var query = (from emc in EducationMissionClassRepository.GetList(exp)
                         from classjor in emc.CF_Classmajor
                         select new ClassView
                         {
                             ClassmajorID = classjor.ClassmajorID,
                             SchoolyearID = emc.EM_EducationMission.SchoolyearID,
                             CoursematerialID = emc.CoursematerialID,
                             TeachingModeID = emc.TeachingModeID ?? 0,
                             ClassmajorName = classjor.Name
                         });
            return query.ToList();
        }

        /// <summary>
        /// 根据专业课程获取授课方式
        /// </summary>
        /// <param name="specialtyCourseID"></param>
        /// <returns></returns>
        public List<string> GetEducationMissionClassSettingsQueryble(Guid? educationMissionClassID)
        {
            var query = from a in EducationMissionClassRepository.Entities
                        join b in EducationMissionClassSettingsRepository.Entities
                        on a.EducationMissionClassID equals b.EducationMissionClassID
                        where a.EducationMissionClassID == educationMissionClassID
                        select b.WeeklyNum.Value.ToString();
            return query.ToList();
        }

        /// <summary>
        /// 获取任务班授课老师
        /// </summary>
        /// <param name="educationMissionClassID"></param>
        /// <returns></returns>
        public IQueryable<EducationMissionClassTeacherListView> GetEducationMissionClassTeacherListViewQueryble()
        {
            var query = from a in EducationMissionClassRepository.Entities
                        join b in MissionClassTeacherRepository.Entities
                        on a.EducationMissionClassID equals b.MissionClassID
                        select new EducationMissionClassTeacherListView
                        {
                            MissionClassTeacherID = b.MissionClassTeacherID,
                            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,
                            Sex = b.CF_Staff.SexID,
                            DepartmentID = b.CF_Staff.DepartmentID,
                            DepartmentName = b.CF_Staff.CF_Department.Name,
                            BirthDate = b.CF_Staff.BirthDate,
                            TeacherType = b.CF_Staff.TeacherTypeID,
                            IncumbencyState = b.CF_Staff.IncumbencyState,
                            Title = b.CF_Staff.TitleID,
                            TeachingMethod = b.TeachType,
                        };
            return query;
        }

        /// <summary>
        /// 获取分班信息
        /// </summary>
        /// <returns></returns>
        public IQueryable<DivideIntoClassesView> GetDivideIntoClassesViewQueryable()
        {
            var inschoolStatus = InSchoolSettingRepository.GetList(w => w.IsSelected == true).Select(s => s.InSchoolStatusID).ToList();
            var query = from a in EducationMissionClassRepository.Entities
                        from b in a.EM_EducationSchedulingClass
                        select new DivideIntoClassesView
                        {
                            EducationMissionClassID = a.EducationMissionClassID,
                            ClassName = a.Name,
                            StudentTotalNum = b.CF_Student.Where(x => inschoolStatus.Contains(x.InSchoolStatusID)).Count(),
                            StudentNum = 0,
                            GroupNum = 0
                        };
            return query;
        }

        /// <summary>
        /// 获取排课班
        /// </summary>
        /// <returns></returns>
        public IQueryable<EducationSchedulingClassView> GetEducationSchedulingClassViewQueryable()
        {
            var query = from a in EducationMissionClassRepository.Entities
                        join b in EducationSchedulingClassRepository.Entities on a.EducationMissionClassID equals b.EducationMissionClassID
                            into gscr
                        from gScheduling in gscr.DefaultIfEmpty()
                        select new EducationSchedulingClassView
                        {
                            EducationSchedulingClassID = gScheduling.EducationSchedulingClassID,
                            EducationMissionClassID = a.EducationMissionClassID,
                            TaskGroupName = gScheduling.TaskGroupName,
                            StudentNum = gScheduling.CF_Student.Count
                        };
            return query;
        }

        /// <summary>
        /// 获取排课班学生信息
        /// </summary>
        /// <param name="educationSchedulingClassID"></param>
        /// <returns></returns>
        public IQueryable<EducationMissionClassStudentView> GetEducationMissionClassStudentViewQueryable(Guid? educationSchedulingClassID)
        {
            var query = from a in EducationSchedulingClassRepository.GetList(x => x.EducationSchedulingClassID == educationSchedulingClassID)
                        from c in a.CF_Student
                        join b in UserRepository.Entities
                            on c.UserID equals b.UserID
                        select new EducationMissionClassStudentView
                        {
                            UserID = b.UserID,
                            LoginID = b.LoginID,
                            Name = b.Name
                        };
            return query;
        }
        
        /// <summary>
        /// 查询任务班对应的排课班所以学生
        /// </summary>
        /// <param name="educationMissionClassID">任务班ID</param>
        /// <returns></returns>
        public IQueryable<EducationMissionClassStudentView> GetEducationMissionClassSchedulingStudentViewQueryble(Guid? educationMissionClassID)
        {
            //该状态下的学生、表示在线状态学生  2016年12月13日16:11:14
            //PS：MM姐：优化执行计划及教学任务的功能，执行计划的人数统计及提交生成教学任务时，过滤非“在校”状态的学生
            var inSchoolStatusIDList = InSchoolSettingRepository.GetList(x => x.IsSelected == true).Select(w => w.InSchoolStatusID).ToList();//获取选中的在校设定状态、
            var query = from a in EducationSchedulingClassRepository.GetList(x => x.EducationMissionClassID == educationMissionClassID)
                        from c in a.CF_Student.Where(p => inSchoolStatusIDList.Contains(p.InSchoolStatusID))
                        join b in UserRepository.Entities
                            on c.UserID equals b.UserID
                        select new EducationMissionClassStudentView
                        {
                            UserID = b.UserID,
                            LoginID = b.LoginID,
                            Name = b.Name,
                            ClassName = c.CF_Classmajor.Name,
                            StudentStatus = c.StudentStatus
                        };
            return query;
        }

        /// <summary>
        /// 查询任务班对应的排课班所有学生（查询多个任务班，暂时用在判断在选课结果页面开班时，是否与教学任务页面对应的任务班有重复学生）
        /// </summary>
        /// <param name="educationMissionClassIDs">任务班ID</param>
        /// <returns></returns>
        public IQueryable<EducationMissionClassStudentView> GetEducationMissionClassSchedulingStudentViewQueryble(List<Guid> educationMissionClassIDs)
        {
            var inSchoolStatusIDList = InSchoolSettingRepository.GetList(x => x.IsSelected == true).Select(w => w.InSchoolStatusID).ToList();//获取选中的在校设定状态、
            var query = from a in EducationSchedulingClassRepository.GetList(x => educationMissionClassIDs.Contains(x.EducationSchedulingClassID))
                        from c in a.CF_Student.Where(p => inSchoolStatusIDList.Contains(p.InSchoolStatusID))
                        join b in UserRepository.Entities
                            on c.UserID equals b.UserID
                        select new EducationMissionClassStudentView
                        {
                            UserID = b.UserID,
                            LoginID = b.LoginID,
                            Name = b.Name,
                            ClassName = c.CF_Classmajor.Name
                        };
            return query;
        }

         
        public IQueryable<EducationMissionView> GetEducationMissionView(Expression<Func<EM_EducationMission, bool>> exp)
        {
            var queryEducationMission = from a in EducationMissionRepository
                                     .GetList(exp)
                                        from b in a.EM_EducationMissionClass
                                        select new EducationMissionView
                                        {
                                            //TeachingModeID = b.TeachingModeID,
                                            EducationMissionID = a.EducationMissionID,
                                            ClassName = a.ClassName,
                                            SchoolyearCode = a.CF_Schoolyear.Code,
                                        };
            return queryEducationMission;
        }

        public IQueryable<CourseProcessView> GetCourseProcessView(Expression<Func<EM_EducationMissionClass, bool>> exp)
        {
            //var query = (from em in educationMissionRepository.GetList(exp)
            //             from emc in em.EM_EducationMissionClass
            //             from cp in emc.EM_CourseProcess
            var query = (from emc in EducationMissionClassRepository.GetList(exp)
                         from cp in emc.EM_CourseProcess
                         select new CourseProcessView
                         {
                             CourseProcessID = cp.CourseProcessID,
                             TeachingModeID = emc.TeachingModeID,
                             CollegeID = emc.EM_EducationMission.CollegeID,
                             CourseCollegeID = emc.EM_EducationMission.CF_Department.CollegeID,
                             EducationMissionClassID = emc.EducationMissionClassID,
                             EducationMissionClassName = emc.Name,
                             Week = cp.Week,
                             Times = cp.Times,
                             ClassroomTypeID = cp.ClassroomTypeID,
                             ClassroomID = cp.ClassroomID,
                             ClassroomName = cp.CF_Classroom.Name,
                             RecordStatus = cp.RecordStatus,
                             CreateTime = cp.CreateTime,
                             CreateUserID = cp.CreateUserID,
                             ModifyTime = cp.ModifyTime,
                             ModifyUserID = cp.ModifyUserID
                         });

            return query;
        }

        public IQueryable<CourseProcessTeacherView> GetCourseProcessTeacherView(Expression<Func<EM_EducationMissionClass, bool>> exp)
        {
            var query = (from emc in EducationMissionClassRepository.GetList(exp)
                         from cp in emc.EM_CourseProcess 
                         from cpt in cp.EM_CourseProcessTeacher 
                         orderby cpt.TeachType,cpt.CF_Staff.Sys_User.Name
                         select new CourseProcessTeacherView
                         {
                             CourseProcessTeacherID = cpt.CourseProcessTeacherID,
                             CourseProcessID = cpt.CourseProcessID,
                             EducationMissionClassID = cp.EducationMissionClassID,
                             SchoolyearID = cp.EM_EducationMissionClass.EM_EducationMission.SchoolyearID,
                             UserID = cpt.CF_Staff.UserID,
                             LoginID = cpt.CF_Staff.Sys_User.LoginID,
                             Name = cpt.CF_Staff.Sys_User.Name,
                             CollegeID = cpt.CF_Staff.CF_College.CollegeID,
                             CollegeName = cpt.CF_Staff.CF_College.Name,
                             Sex = cpt.CF_Staff.SexID,
                             DepartmentID = cpt.CF_Staff.DepartmentID,
                             DepartmentName = cpt.CF_Staff.CF_Department.Name,
                             BirthDate = cpt.CF_Staff.BirthDate,
                             TeacherType = cpt.CF_Staff.TeacherTypeID,
                             IncumbencyState = cpt.CF_Staff.IncumbencyState,
                             Title = cpt.CF_Staff.TitleID,
                             TeachingMethod = cpt.TeachType
                         });

            return query;
        }

        public IQueryable<CourseProcessTeacherView> GetCourseProcessTeacherViewByCourseProcess(Expression<Func<EM_CourseProcess, bool>> exp)
        {
            var query = (from cp in CourseProcessRepository.GetList(exp)
                         from cpt in cp.EM_CourseProcessTeacher
                         orderby cpt.TeachType, cpt.CF_Staff.Sys_User.Name
                         select new CourseProcessTeacherView
                         {
                             CourseProcessTeacherID = cpt.CourseProcessTeacherID,
                             CourseProcessID = cpt.CourseProcessID,
                             EducationMissionClassID = cp.EducationMissionClassID,
                             SchoolyearID = cp.EM_EducationMissionClass.EM_EducationMission.SchoolyearID,
                             UserID = cpt.CF_Staff.UserID,
                             LoginID = cpt.CF_Staff.Sys_User.LoginID,
                             Name = cpt.CF_Staff.Sys_User.Name,
                             CollegeID = cpt.CF_Staff.CF_College.CollegeID,
                             CollegeName = cpt.CF_Staff.CF_College.Name,
                             Sex = cpt.CF_Staff.SexID,
                             DepartmentID = cpt.CF_Staff.DepartmentID,
                             DepartmentName = cpt.CF_Staff.CF_Department.Name,
                             BirthDate = cpt.CF_Staff.BirthDate,
                             TeacherType = cpt.CF_Staff.TeacherTypeID,
                             IncumbencyState = cpt.CF_Staff.IncumbencyState,
                             Title = cpt.CF_Staff.TitleID,
                             TeachingMethod = cpt.TeachType
                         });

            return query;
        }

        public IQueryable<EM_CourseProcess> GetRelatedCourseProcess(Expression<Func<EM_EducationMissionClass, bool>> exp)
        {
            var query = (from emc in EducationMissionClassRepository.GetList(exp)
                         // from remc in emc.EM_EducationMission.EM_EducationMissionClass
                         from cp in emc.EM_CourseProcess
                         select cp).Include(x => x.EM_EducationMissionClass);

            return query;
        }

        public IQueryable<CollegeView> GetSchedulingSubmittedCollege(List<Guid?> educationMissionClassIDList)
        {
            var query = (from emc in EducationMissionClassRepository.GetList(x => educationMissionClassIDList.Contains(x.EducationMissionClassID))
                         join cs in CollegeScheduleStatusRepository.Entities on new { emc.EM_EducationMission.CollegeID, emc.EM_EducationMission.SchoolyearID }
                            equals new { cs.CollegeID, cs.SchoolyearID }
                         join c in CollegeDAL.Value.GetCollegeViewQueryable(x => true) on cs.CollegeID equals c.CollegeID
                         where cs.RecordStatus == (int)ES_CollegeScheduleStatusCode.Submited
                         select c);

            return query;
        }

        [Obsolete]
        public IQueryable<EducationMissionClassView> GetLastCourseEducationMissionClassViewQueryable(Expression<Func<CF_Student, bool>> studentExp)
        {
            var missionClassIDQueryable = (from missionClass in EducationMissionClassRepository.Entities
                             from mission in EducationMissionRepository.Entities.Where(x => x.EducationMissionID == missionClass.EducationMissionID)
                             from schoolyear in SchoolyearRepository.Entities.Where(x => x.SchoolyearID == mission.SchoolyearID)
                             from schedulingClass in EducationSchedulingClassRepository.Entities.Where(x => x.EducationMissionClassID == missionClass.EducationMissionClassID)
                             from student in schedulingClass.CF_Student.AsQueryable().Where(studentExp)
                             group new { missionClass, schoolyear } by missionClass.CoursematerialID into g
                             select 
                                g.OrderByDescending(x => x.schoolyear.Value)
                                .ThenBy(x => x.missionClass.TeachingModeID)
                                .Select(x => x.missionClass.EducationMissionClassID)
                                .FirstOrDefault()
                             );
            var missionClassViewQueryable = this.GetEducationMissionClassViewQueryble(x => true);
            var query = (from view in missionClassViewQueryable
                         from temp in missionClassIDQueryable
                            .Where(x => x == view.EducationMissionClassID)
                         select view);

            return query;
        }

        [Obsolete]
        public IQueryable<LastCourseStarttermView> GetLastCourseStarttermViewQueryable()
        {
            var query = (from missionClass in EducationMissionClassRepository.Entities
                         from mission in EducationMissionRepository.Entities.Where(x => x.EducationMissionID == missionClass.EducationMissionID)
                         from setting in EducationMissionClassTeachingSettingRepository.Entities.Where(x => x.EducationMissionClassID == missionClass.EducationMissionClassID)
                         from schoolyear in SchoolyearRepository.Entities.Where(x => x.SchoolyearID == mission.SchoolyearID)
                         from classmajor in missionClass.CF_Classmajor
                         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 missionClass1 in EducationMissionClassRepository.Entities
                                 from mission1 in EducationMissionRepository.Entities.Where(x => x.EducationMissionID == missionClass1.EducationMissionID)
                                 from schoolyear1 in SchoolyearRepository.Entities.Where(x => x.SchoolyearID == mission1.SchoolyearID)
                                 from classmajor1 in missionClass1.CF_Classmajor
                                 from student1 in classmajor1.CF_Student
                                 group new { student1.UserID, missionClass1.CoursematerialID, SchoolyearValue = schoolyear1.Value }
                                 by new { student1.UserID, missionClass1.CoursematerialID } into g
                                 select new { UserID = g.Key.UserID, CoursematerialID = g.Key.CoursematerialID, MaxSchoolyearValue = g.Max(x => x.SchoolyearValue) })
                                 .Where(x => x.CoursematerialID == missionClass.CoursematerialID && x.MaxSchoolyearValue == schoolyear.Value
                                 && x.UserID == student.UserID) 
                         from retake in RetakePlanStudentRepository.Entities.Where(x => x.EducationMissionID == mission.EducationMissionID).DefaultIfEmpty() 
                         select new
                         {
                             SchoolyearID = mission.SchoolyearID,
                             SchoolyearNumID = ((schoolyear.Value - gradeSchoolyear.Value) / 2) + 1,
                             CoursematerialID = missionClass.CoursematerialID,
                             DepartmentID = mission.DepartmentID,
                             ClassName = mission.ClassName,
                             CourseTypeID = missionClass.CourseTypeID,
                             ExaminationModeID = missionClass.ExaminationModeID,
                             HandleModeID = missionClass.HandleModeID,
                             Credit = setting.Credit,
                             TotalHours = (setting.TheoryCourse ?? 0) + (setting.Practicehours ?? 0),
                             ResultTypeID = missionClass.ResultTypeID,
                             StarttermID = (schoolyear.Value - gradeSchoolyear.Value + 1),
                             UserID = student.UserID,
                             IsRetake = retake.RetakePlanStudentID != null
                         } 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,
                             IsRetake = s.IsRetake
                         }
                         );

            return query;
        }

    }
}