JW/EMiS.DataLogic/ExaminationManage/ExaminationPlanDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Entity;
using System.Text;
using EMIS.DataLogic.Repositories;
using EMIS.ViewModel.ExaminationManage;
using System.Linq.Expressions;
using EMIS.Entities;
using EMIS.ViewModel;
using EMIS.ViewModel.UniversityManage.TeacherManage;
using EMIS.ViewModel.Students;
using EMIS.DataLogic.Common.Students;
using EMIS.DataLogic.ScoreManage;
using EMIS.DataLogic.Common.CalendarManage;
using EMIS.ViewModel.EducationSchedule;
using EMIS.DataLogic.UniversityManage.TeacherManage;

namespace EMIS.DataLogic.ExaminationManage
{
    public class ExaminationPlanDAL
    {
        public ExaminationPlanRepository ExaminationPlanRepository { get; set; }
        public ExaminationRoomLayoutRepository ExaminationRoomLayoutRepository { get; set; }
        public ExaminationModeSettingRepository ExaminationModeSettingRepository { get; set; }
        public EducationMissionRepository EducationMissionRepository { get; set; }
        public EducationMissionExamTimeRepository EducationMissionExamTimeRepository { get; set; }
        public EducationMissionClassRepository EducationMissionClassRepository { get; set; }
        public ExecutablePlanRepository ExecutablePlanRepository { get; set; }
        public ExecutablePlanProfileRepository ExecutablePlanProfileRepository { get; set; }
        public SpecialtyPlanRepository SpecialtyPlanRepository { get; set; }
        public SpecialtyCourseRepository SpecialtyCourseRepository { get; set; }
        public CoursematerialRepository CoursematerialRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public ClassroomRepository ClassroomRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public StaffRepository staffRepository { get; set; }
        public EducationSchedulingClassRepository educationSchedulingClassRepository { get; set; }
        public EducationSchedulingRepository educationSchedulingRepository { get; set; }
        public EducationSchedulingWeekNumRepository educationSchedulingWeekNumRepository { get; set; }
        public UserRepository userRepository { get; set; }

        public StaffDAL StaffDAL { get; set; }
        public StudentsDAL StudentsDAL { get; set; }
        public Lazy<FinalExaminationDAL> FinalExaminationDAL { get; set; }

        public IQueryable<ExaminationPlanView> GetExaminationPlanView(Expression<Func<EX_ExaminationPlan, bool>> examinationPlanExpression)
        {
            var inschool = StudentsDAL.InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true).Select(x => x.InSchoolStatusID).ToList();
            var q = (from ep in ExaminationPlanRepository.GetList(examinationPlanExpression)
                     join epg in (
                         from ep in ExaminationPlanRepository.GetList(examinationPlanExpression)
                         join emc in EducationMissionClassRepository.Entities on ep.EducationMissionID equals emc.EducationMissionID
                         join escr in educationSchedulingClassRepository.Entities on emc.EducationMissionClassID equals escr.EducationMissionClassID
                         join esr in educationSchedulingRepository.Entities on escr.EducationSchedulingClassID equals esr.EducationSchedulingClassID
                         join eswnr in educationSchedulingWeekNumRepository.Entities on esr.EducationSchedulingID equals eswnr.EducationSchedulingID
                         group new {ep,eswnr}
                         by ep.ExaminationPlanID
                         into gep
                         select new {
                            gep.Key,
                            WeekNum = gep.Max(x => x.eswnr.WeekNum)
                         }
                         )
                         on ep.ExaminationPlanID equals epg.Key into week
                         from epg in week.DefaultIfEmpty()
                     join em in EducationMissionRepository.Entities on ep.EducationMissionID equals em.EducationMissionID into dem 
                     from eem in dem.DefaultIfEmpty()
                     select new ExaminationPlanView
                     {
                         ExaminationPlanID = ep.ExaminationPlanID,
                         SchoolyearID = ep.SchoolyearID,
                         SchoolyearCode = ep.CF_Schoolyear.Code,
                         CampusID = ep.CF_College.CampusID,
                         CampusName = ep.CF_College.CF_Campus.Name,
                         CollegeID = ep.CollegeID,
                         CollegeName = ep.CF_College.Name,
                         CoursematerialID = ep.CoursematerialID,
                         CoursematerialName = ep.EM_Coursematerial.CourseName,
                         ExaminationModeID = ep.ExaminationModeID,
                         ExaminationStyleID = ep.ExaminationStyleID,
                         ExamsCategoryID = ep.ExamsCategoryID,
                         EducationMissionID = ep.EducationMissionID,
                         ClassName = ep.ClassName,
                         ExaminationDate = ep.ExaminationDate,
                         CF_Classroom = ep.EX_ExaminationRoomLayout.Select(x => x.CF_Classroom),
                         StartTime = ep.StartTime,
                         EndTime = ep.EndTime,
                         CourseEndWeekNum = epg.WeekNum,
                         MaxWeekNum = eem.EM_EducationMissionExamWeekNum.Max(x => x.WeeklyNum),
                         WeekNum = eem.EM_EducationMissionExamWeekNum,
                         MissionStudentCount = ep.CF_Student.Count(x => inschool.Contains(x.InSchoolStatusID)),
                         StudentCount = ep.EX_ExaminationRoomLayout.Sum(x => x.EX_ExaminationRoomStudent.Count()),
                         RecordStatus = ep.RecordStatus,
                         CreateUserID = ep.CreateUserID,
                         CreateTime = ep.CreateTime,
                         ModifyUserID = ep.ModifyUserID,
                         ModifyTime = ep.ModifyTime
                     });

            return q;
        }

        public IQueryable<ExaminationPlanGdssView> GetExaminationPlanGdssView(Expression<Func<EX_ExaminationPlan, bool>> examinationPlanExpression)
        {
            var q = (from plan in GetExaminationPlanView(examinationPlanExpression)
                     from layout in ExaminationRoomLayoutRepository.Entities.Where(x => x.ExaminationPlanID == plan.ExaminationPlanID)
                        .Take(1).DefaultIfEmpty() 
                     from classroom in ClassroomRepository.Entities.Where(x => x.ClassroomID == layout.ClassroomID).DefaultIfEmpty()
                     select new ExaminationPlanGdssView
                     {
                         ExaminationPlanID = plan.ExaminationPlanID,
                         SchoolyearID = plan.SchoolyearID,
                         SchoolyearCode = plan.SchoolyearCode,
                         CampusID = plan.CampusID,
                         CampusName = plan.CampusName,
                         CollegeID = plan.CollegeID,
                         CollegeName = plan.CollegeName,
                         CoursematerialID = plan.CoursematerialID,
                         CoursematerialName = plan.CoursematerialName,
                         ExaminationModeID = plan.ExaminationModeID,
                         ExaminationStyleID = plan.ExaminationStyleID,
                         ExamsCategoryID = plan.ExamsCategoryID,
                         EducationMissionID = plan.EducationMissionID,
                         ClassName = plan.ClassName,
                         ExaminationDate = plan.ExaminationDate,
                         CF_Classroom = plan.CF_Classroom,
                         BuildingsInfoID = classroom.BuildingsInfoID,
                         ClassroomID = classroom.ClassroomID,
                         StudentOrderType = layout.StudentOrderType,
                         StartTime = plan.StartTime,
                         EndTime = plan.EndTime,
                         CourseEndWeekNum = plan.CourseEndWeekNum,
                         MaxWeekNum = plan.MaxWeekNum,
                         WeekNum = plan.WeekNum,
                         MissionStudentCount = plan.MissionStudentCount,
                         StudentCount = plan.StudentCount,
                         RecordStatus = plan.RecordStatus,
                         CreateUserID = plan.CreateUserID,
                         CreateTime = plan.CreateTime,
                         ModifyUserID = plan.ModifyUserID,
                         ModifyTime = plan.ModifyTime
                     });

            return q;
        }

        public IQueryable<ExaminationPlanView> GetExaminationPlanViewForAdult(Expression<Func<EX_ExaminationPlan, bool>> examinationPlanExpression)
        {
            var inschool = StudentsDAL.InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true).Select(x => x.InSchoolStatusID).ToList();
            var q = (from ep in ExaminationPlanRepository.GetList(examinationPlanExpression)
                     join epg in
                         (
                             from ep in ExaminationPlanRepository.GetList(examinationPlanExpression)
                             join emc in EducationMissionClassRepository.Entities on ep.EducationMissionID equals emc.EducationMissionID
                             join escr in educationSchedulingClassRepository.Entities on emc.EducationMissionClassID equals escr.EducationMissionClassID
                             join esr in educationSchedulingRepository.Entities on escr.EducationSchedulingClassID equals esr.EducationSchedulingClassID
                             join eswnr in educationSchedulingWeekNumRepository.Entities on esr.EducationSchedulingID equals eswnr.EducationSchedulingID
                             group new { ep, eswnr }
                             by ep.ExaminationPlanID
                                 into gep
                                 select new
                                 {
                                     gep.Key,
                                     WeekNum = gep.Max(x => x.eswnr.WeekNum)
                                 }
                             )
                         on ep.ExaminationPlanID equals epg.Key into week
                     from epg in week.DefaultIfEmpty()
                     join em in EducationMissionRepository.Entities on ep.EducationMissionID equals em.EducationMissionID into dem
                     from eem in dem.DefaultIfEmpty()
                     join erlr in ExaminationRoomLayoutRepository.Entities
                     on ep.ExaminationPlanID equals erlr.ExaminationPlanID into erlem
                     from derlr in erlem.DefaultIfEmpty()
                     select new ExaminationPlanView
                     {
                         ExaminationPlanID = ep.ExaminationPlanID,
                         SchoolyearID = ep.SchoolyearID,
                         SchoolyearCode = ep.CF_Schoolyear.Code,
                         CampusID = ep.CF_College.CampusID,
                         CampusName = ep.CF_College.CF_Campus.Name,
                         CollegeID = ep.CollegeID,
                         CollegeName = ep.CF_College.Name,
                         CoursematerialID = ep.CoursematerialID,
                         CoursematerialName = ep.EM_Coursematerial.CourseName,
                         ExaminationModeID = ep.ExaminationModeID,
                         ExaminationStyleID = ep.ExaminationStyleID,
                         ExamsCategoryID = ep.ExamsCategoryID,
                         EducationMissionID = ep.EducationMissionID,
                         ClassName = ep.ClassName,
                         ExaminationDate = ep.ExaminationDate,
                         CF_Classroom = ep.EX_ExaminationRoomLayout.Select(x => x.CF_Classroom),
                         staffUser = derlr.CF_Staff.Select(x => x.Sys_User),
                         StartTime = ep.StartTime,
                         EndTime = ep.EndTime,
                         CourseEndWeekNum = epg.WeekNum,
                         MaxWeekNum = eem.EM_EducationMissionExamWeekNum.Max(x => x.WeeklyNum),
                         WeekNum = eem.EM_EducationMissionExamWeekNum,
                         MissionStudentCount = ep.CF_Student.Count(x => inschool.Contains(x.InSchoolStatusID)),
                         StudentCount = ep.EX_ExaminationRoomLayout.Sum(x => x.EX_ExaminationRoomStudent.Count()),
                         RecordStatus = ep.RecordStatus,
                         CreateUserID = ep.CreateUserID,
                         CreateTime = ep.CreateTime,
                         ModifyUserID = ep.ModifyUserID,
                         ModifyTime = ep.ModifyTime
                     });

            return q;
        }

        public IQueryable<ExaminationRoomLayoutView> GetExaminationRoomLayoutView(Expression<Func<EX_ExaminationRoomLayout, bool>> examinationRoomLayoutExpression)
        {
            var q = (from erl in ExaminationRoomLayoutRepository.GetList(examinationRoomLayoutExpression)
                     select new ExaminationRoomLayoutView 
                     {
                         ExaminationRoomLayoutID = erl.ExaminationRoomLayoutID,
                         ExaminationPlanID = erl.ExaminationPlanID.Value,
                         ClassroomID = erl.ClassroomID,
                         ClassroomName = erl.CF_Classroom.Name,
                         Totalseating = erl.CF_Classroom.Totalseating,
                         Examinationseating = erl.CF_Classroom.Examinationseating,
                         RowSpacing = erl.RowSpacing,
                         ColumnSpacing = erl.ColumnSpacing,
                         StudentOrderType = erl.StudentOrderType,
                         StudentNum=erl.EX_ExaminationRoomStudent.Count(),
                         Sys_User = erl.CF_Staff.Select(x => x.Sys_User),
                         IsSubmitted = erl.EX_ExaminationPlan.RecordStatus == (int)CF_ApprovalStatus.Submitted,
                         EX_ExaminationRoomStudent = erl.EX_ExaminationRoomStudent,
                         RecordStatus = erl.RecordStatus,
                         CreateTime = erl.CreateTime,
                         CreateUserID = erl.CreateUserID,
                         ModifyTime = erl.ModifyTime,
                         ModifyUserID = erl.ModifyUserID
                     });

            return q;
        }

        public IQueryable<StaffView> GetExaminationTeacherView(Expression<Func<EX_ExaminationRoomLayout, bool>> examinationRoomLayoutExpression,
            Expression<Func<CF_Staff, bool>> staffExpression)
        {
            var q = (from erl in ExaminationRoomLayoutRepository.GetList(examinationRoomLayoutExpression) 
                     from s in erl.CF_Staff
                     join sv in StaffDAL.GetStaffViewQueryable(staffExpression) on s.UserID equals sv.UserID 
                     select sv);

            return q;
        }

        public IQueryable<StudentsView> GetExaminationStudentView(Expression<Func<EX_ExaminationRoomLayout, bool>> examinationRoomLayoutExpression,
            Expression<Func<CF_Student, bool>> studentExpression)
        {
            var q = (from erl in ExaminationRoomLayoutRepository.GetList(examinationRoomLayoutExpression)
                     from s in erl.EX_ExaminationRoomStudent
                     join sv in StudentsDAL.GetStudentQueryable(studentExpression, (x => true), (x => true)) on s.UserID equals sv.UserID
                     select sv);

            return q;
        }

        public IQueryable<ExaminationRoomStudentView> GetExaminationRoomStudentView(Expression<Func<EX_ExaminationRoomLayout, bool>> examinationRoomLayoutExpression)
        {
            var q = (from erl in ExaminationRoomLayoutRepository.GetList(examinationRoomLayoutExpression)
                     from s in erl.EX_ExaminationRoomStudent
                     select new ExaminationRoomStudentView 
                     {
                         ExaminationRoomStudentID = s.ExaminationRoomStudentID,
                         ExaminationRoomLayoutID = s.ExaminationRoomLayoutID,
                         UserID = s.UserID,
                         LoginID = s.CF_Student.Sys_User.LoginID,
                         Name = s.CF_Student.Sys_User.Name,
                         Row = s.Row,
                         Column = s.Column,
                         Remark = s.Remark,
                         RecordStatus = s.RecordStatus,
                         CreateTime = s.CreateTime,
                         CreateUserID = s.CreateUserID,
                         ModifyTime = s.ModifyTime,
                         ModifyUserID = s.ModifyUserID
                     });

            return q;
        }

        public virtual IQueryable<ExaminationPlanView> GetExaminationPlanByEducationMission(Expression<Func<EM_EducationMission, bool>> educationMissionExpression, Expression<Func<EM_EducationMissionClass, bool>> emcExp)
        {
            var missionClassQuery = (
                         from emc in EducationMissionClassRepository.GetList(emcExp) 
                         join cm in CoursematerialRepository.Entities on emc.CoursematerialID equals cm.CoursematerialID 
                         join ems in ExaminationModeSettingRepository.GetList(x => x.IsGeneratePlan == false) 
                            on emc.ExaminationModeID equals ems.ExaminationModeID into dems 
                         from eems in dems.DefaultIfEmpty() 
                         where emc.RecordStatus != (int)EM_EducationMissionClassStatus.NotSubmitted 
                            && eems.ExaminationModeSettingID == null && emc.HandleModeID == (int)CF_HandleMode.RequiredCourse
                         group emc by new { 
                             emc.EducationMissionID,
                             emc.CoursematerialID,
                             cm.CourseName,
                             emc.ExaminationModeID
                         } into g 
                         select g.Key
                         );
            var q = (from em in EducationMissionRepository.GetList(educationMissionExpression) 
                     join c in missionClassQuery 
                         on em.EducationMissionID equals c.EducationMissionID 
                     join ep in ExaminationPlanRepository.Entities 
                        on em.EducationMissionID equals ep.EducationMissionID into dep 
                     from eep in dep.DefaultIfEmpty() 
                     join emet in EducationMissionExamTimeRepository.Entities on em.EducationMissionID equals emet.EducationMissionID into demet 
                     from eemet in demet.DefaultIfEmpty() 
                     where eep.EducationMissionID == null
                     select new ExaminationPlanView 
                     {
                         //ExaminationPlanID = Guid.NewGuid(),
                         SchoolyearID = em.SchoolyearID,
                         CollegeID = em.CollegeID,
                         CoursematerialID = c.CoursematerialID,
                         ExaminationModeID = c.ExaminationModeID,
                         ExaminationStyleID = (int)EX_ExaminationStyle.CloseBook,
                         EducationMissionID = em.EducationMissionID,
                         ClassName = em.ClassName,
                         ExaminationDate = (eemet.EducationMissionExamTimeID != null) ? eemet.ExamDate : null,
                         StartTime = (eemet.EducationMissionExamTimeID != null) ? eemet.StartTime : null,
                         EndTime = (eemet.EducationMissionExamTimeID != null) ? eemet.EndTime : null,
                         Students = em.EM_EducationMissionClass.FirstOrDefault().EM_EducationSchedulingClass.SelectMany(x => x.CF_Student)
                     });

            return q;
        }

        public IQueryable<ExaminationPlanView> GetExaminationPlanByResitStudent(Guid? schoolyearID, Guid? currentSchoolyearID)
        {
            var resitStudent = FinalExaminationDAL.Value.GetNotPassStudent(schoolyearID);

            var q = (from rs in resitStudent
                     from s in StudentRepository.Entities.Where(x => x.UserID == rs.UserID)
                     from eep in
                         (
                             from ep in ExaminationPlanRepository.Entities.Where(x => x.SchoolyearID == currentSchoolyearID
                                 && x.CollegeID == rs.CollegeID && x.CoursematerialID == rs.CoursematerialID && x.ExaminationModeID == rs.ExaminationModeID
                                 && x.ExamsCategoryID == (int?)CF_ExamsCategory.Resit)
                             from eps in ep.CF_Student.Where(x => x.UserID == rs.UserID)
                             select eps
                             ).DefaultIfEmpty()
                     where eep.UserID == null
                     group new { ResitStudent = rs, Students = s } by new
                     {
                         rs.Year,
                         rs.CollegeID,
                         rs.GrademajorID,
                         rs.GrademajorName,
                         rs.CoursematerialID,
                         rs.DepartmentID,
                         rs.CourseName,
                         rs.CourseTypeID,
                         rs.ExaminationModeID,
                         rs.StartTermID,
                         rs.Credit,
                         rs.ResultTypeID
                     } into g
                     select new ExaminationPlanView
                     {
                         //ExaminationPlanID = Guid.NewGuid(),
                         SchoolyearID = currentSchoolyearID,
                         CollegeID = g.Key.CollegeID,
                         CoursematerialID = g.Key.CoursematerialID,
                         ExaminationModeID = g.Key.ExaminationModeID,
                         ExaminationStyleID = (int)EX_ExaminationStyle.CloseBook,
                         Students = g.Select(x => x.Students),
                         ClassName = g.Key.CourseName + " - " + g.Key.GrademajorName + "-补考"
                     });

            return q;
        }

        public IQueryable<ExaminationPlanView> GetExaminationPlanByResitExamination(Guid? currentSchoolyearID)
        {
            var resitExaminationStudent = (
                    from student in FinalExaminationDAL.Value.FinalExaminationStudentRepository.Entities 
                    from exam in FinalExaminationDAL.Value.finalExaminationRepository.Entities.Where(x => x.FinalExaminationID == student.FinalExaminationID 
                        && x.SchoolyearID == currentSchoolyearID 
                        && x.ExamsCategoryID != (int)CF_ExamsCategory.GraduationExam 
                        && x.ExaminationType == (int)CF_ExaminationType.ResitExamination)
                    select new { student.UserID, exam.FinalExaminationID, exam.CollegeID, exam.CoursematerialID, exam.ExaminationModeID }
                        );

            var q = (
                     from resit in resitExaminationStudent 
                     from course in CoursematerialRepository.Entities.Where(x => x.CoursematerialID == resit.CoursematerialID)
                     from student in StudentRepository.Entities.Where(x => x.UserID == resit.UserID) 
                     from classmajor in ClassmajorRepository.Entities.Where(x => x.ClassmajorID == student.ClassmajorID) 
                     from grade in GrademajorRepository.Entities.Where(x => x.GrademajorID == classmajor.GrademajorID) 
                     from plan in student.EX_ExaminationPlan.
                        Where(x => x.SchoolyearID == currentSchoolyearID
                            && x.CollegeID == resit.CollegeID
                            && x.CoursematerialID == resit.CoursematerialID
                            && x.ExaminationModeID == resit.ExaminationModeID
                            && x.ExamsCategoryID == (int)CF_ExamsCategory.Resit).DefaultIfEmpty()
                     where plan.ExaminationPlanID == null
                     group student by new
                     {
                         //resit.FinalExaminationID,
                         resit.CollegeID,
                         resit.CoursematerialID,
                         resit.ExaminationModeID,
                         ClassName = course.CourseName + " - " + grade.Name + "-补考"
                     } into g
                     select new ExaminationPlanView
                     {
                         SchoolyearID = currentSchoolyearID,
                         CollegeID = g.Key.CollegeID,
                         CoursematerialID = g.Key.CoursematerialID,
                         ExaminationModeID = g.Key.ExaminationModeID,
                         ExaminationStyleID = (int)EX_ExaminationStyle.CloseBook,
                         Students = g.Select(x => x),
                         ClassName = g.Key.ClassName
                     }
                     );

            return q;
        }

        public IQueryable<ExaminationPlanView> GetExaminationPlanByGraduateExamStudent(int? yearID, Guid? collegeID, int? standardID, decimal? learnSystem, Guid? currentSchoolyearID)
        {
            var resitStudent = FinalExaminationDAL.Value.GetGraduationExamStudent(yearID);
            if (collegeID.HasValue) //院系所
                resitStudent = resitStudent.Where(x => x.CollegeID == collegeID);
            if (yearID.HasValue && yearID != -1)   //年级
                resitStudent = resitStudent.Where(x => x.Year == yearID);
            if (standardID.HasValue)    //专业
                resitStudent = resitStudent.Where(x => x.StandardID == standardID);
            //if (classmajorID.HasValue)
            //    resitStudent = resitStudent.Where(x => x.ClassmajorID == classmajorID);
            if (learnSystem.HasValue && learnSystem != -1)
                resitStudent = resitStudent.Where(x => x.LearnSystem == learnSystem);

            var q = (from rs in resitStudent
                     from s in StudentRepository.Entities.Where(x => x.UserID == rs.UserID)
                     from eep in (
                        from ep in ExaminationPlanRepository.Entities.Where(x => 
                            x.SchoolyearID == currentSchoolyearID && x.CollegeID == rs.CollegeID && x.CoursematerialID == rs.CoursematerialID 
                            && x.ExaminationModeID == rs.ExaminationModeID && x.ExamsCategoryID == (int?)CF_ExamsCategory.GraduationExam)
                        from eps in ep.CF_Student
                        where eps.UserID == rs.UserID 
                        select eps
                        ).DefaultIfEmpty()
                     where eep.UserID == null
                     group new { ResitStudent = rs, Students = s } by new
                     {
                         rs.Year,
                         rs.CollegeID,
                         rs.GrademajorID,
                         rs.GrademajorName,
                         rs.CoursematerialID,
                         rs.DepartmentID,
                         rs.CourseName,
                         rs.CourseTypeID,
                         rs.ExaminationModeID,
                         rs.StartTermID,
                         rs.Credit,
                         rs.ResultTypeID
                     } into g 
                     select new ExaminationPlanView
                     {
                         //ExaminationPlanID = Guid.NewGuid(),
                         SchoolyearID = currentSchoolyearID,
                         CollegeID = g.Key.CollegeID,
                         CoursematerialID = g.Key.CoursematerialID,
                         ExaminationModeID = g.Key.ExaminationModeID,
                         ExaminationStyleID = (int)EX_ExaminationStyle.CloseBook,
                         Students = g.Select(x => x.Students),
                         ClassName = g.Key.CourseName + " - " + g.Key.GrademajorName + "-毕业补考"
                     });

            return q;
        }

        public IQueryable<ExaminationPlanView> GetExaminationPlanByGraduateExamination(Guid? currentSchoolyearID)
        {
            var graduateExaminationStudent = FinalExaminationDAL.Value.FinalExaminationStudentRepository.GetList(x =>
                                    x.ER_FinalExamination.SchoolyearID == currentSchoolyearID
                                    && x.ER_FinalExamination.ExamsCategoryID == (int)CF_ExamsCategory.GraduationExam);
            var q = (
                     from fs in graduateExaminationStudent
                     join s in StudentRepository.Entities on fs.UserID equals s.UserID
                     from ep in s.EX_ExaminationPlan.
                        Where(x => x.SchoolyearID == currentSchoolyearID
                            && x.CollegeID == fs.ER_FinalExamination.CollegeID
                            && x.CoursematerialID == fs.ER_FinalExamination.CoursematerialID
                            && x.ExaminationModeID == fs.ER_FinalExamination.ExaminationModeID
                            && x.ExamsCategoryID == fs.ER_FinalExamination.ExamsCategoryID).DefaultIfEmpty()
                     where ep.ExaminationPlanID == null
                     group s by new
                     {
                         fs.FinalExaminationID,
                         fs.ER_FinalExamination.CollegeID,
                         fs.ER_FinalExamination.CoursematerialID,
                         fs.ER_FinalExamination.ExaminationModeID,
                         fs.ER_FinalExamination.ClassName
                     } into g
                     select new ExaminationPlanView
                     {
                         SchoolyearID = currentSchoolyearID,
                         CollegeID = g.Key.CollegeID,
                         CoursematerialID = g.Key.CoursematerialID,
                         ExaminationModeID = g.Key.ExaminationModeID,
                         ExaminationStyleID = (int)EX_ExaminationStyle.CloseBook,
                         Students = g.Select(x => x),
                         ClassName = g.Key.ClassName
                     }
                     );

            return q;
        }

        public IQueryable<StudentsView> GetAvailableStudentView(Expression<Func<EX_ExaminationPlan, bool>> examinationPlanExpression,
            Expression<Func<CF_Student, bool>> studentExp)
        {
            var q = (
                from ep in ExaminationPlanRepository.GetList(examinationPlanExpression)
                from eps in ep.CF_Student
                join s in StudentsDAL.GetStudentQueryable(studentExp, (x => true), (x => true)) on eps.UserID equals s.UserID
                join nas in
                    (
                        from epm in ExaminationPlanRepository.GetList(examinationPlanExpression)
                        join erl in ExaminationRoomLayoutRepository.Entities on true equals true
                        where erl.EX_ExaminationPlan.ExaminationDate == epm.ExaminationDate
                            && erl.EX_ExaminationPlan.EndTime >= epm.StartTime && erl.EX_ExaminationPlan.StartTime <= epm.EndTime
                        from s in erl.EX_ExaminationRoomStudent
                        select s
                        ) on eps.UserID equals nas.UserID into dnas
                from enas in dnas.DefaultIfEmpty()
                where enas.UserID == null
                select s
                );

            return q;
        }

        public IQueryable<StudentConflictView> GetConflictStudentView(Expression<Func<EX_ExaminationPlan, bool>> examinationPlanExp,
            DateTime? date, TimeSpan? startTime, TimeSpan? endTime)
        {
            var q = (
                from ep in ExaminationPlanRepository.GetList(examinationPlanExp)
                from erl in ep.EX_ExaminationRoomLayout
                from ers in erl.EX_ExaminationRoomStudent
                join erlt in
                    (
                        from erlt1 in ExaminationRoomLayoutRepository.Entities
                        from erst1 in erlt1.EX_ExaminationRoomStudent
                        select new
                        {
                            UserID = erst1.UserID,
                            ExaminationPlanID = erlt1.EX_ExaminationPlan.ExaminationPlanID,
                            ExaminationPlanName = erlt1.EX_ExaminationPlan.ClassName,
                            erlt1.EX_ExaminationPlan.ExaminationDate,
                            erlt1.EX_ExaminationPlan.StartTime,
                            erlt1.EX_ExaminationPlan.EndTime
                        }
                        ) on ers.UserID equals erlt.UserID
                where ep.ExaminationPlanID != erlt.ExaminationPlanID && erlt.ExaminationDate == date
                    && erlt.EndTime >= startTime && erlt.StartTime <= endTime
                select new StudentConflictView 
                {
                    UserID = ers.CF_Student.UserID,
                    Name = ers.CF_Student.Sys_User.Name,
                    ExaminationPlanID = erlt.ExaminationPlanID,
                    ExaminationPlanName = erlt.ExaminationPlanName,
                    StartTime = erlt.StartTime,
                    EndTime = erlt.EndTime
                }
                );

            return q;
        }

        public IQueryable<AdultExaminationPlanExportView> GetAdultExaminationPlanExportView(Expression<Func<EX_ExaminationPlan, bool>> examinationPlanExpression)
        {
            var inschool = StudentsDAL.InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true).Select(x => x.InSchoolStatusID).ToList();
            var query = (from plan in ExaminationPlanRepository.GetList(examinationPlanExpression)
                         join schoolyear in SchoolyearRepository.Entities on plan.SchoolyearID equals schoolyear.SchoolyearID 
                         join course in CoursematerialRepository.Entities on plan.CoursematerialID equals course.CoursematerialID 
                         join mission in EducationMissionRepository.Entities on plan.EducationMissionID equals mission.EducationMissionID into dmission 
                         from emission in dmission.DefaultIfEmpty() 
                         join layout in ExaminationRoomLayoutRepository.Entities on plan.ExaminationPlanID equals layout.ExaminationPlanID into dlayout 
                         from elayout in dlayout.DefaultIfEmpty() 
                         join classroom in ClassroomRepository.Entities on elayout.ClassroomID equals classroom.ClassroomID into dclassroom 
                         from eclassroom in dclassroom.DefaultIfEmpty() 
                         select new AdultExaminationPlanExportView
                         { 
                             SchoolyearCode = schoolyear.Code,
                             CoursematerialID = plan.CoursematerialID,
                             CoursematerialName = course.CourseName,
                             ClassName = plan.ClassName,
                             MissionStudentCount = plan.CF_Student.Count(x => inschool.Contains(x.InSchoolStatusID)),
                             MaxExaminationWeekNum = emission.EM_EducationMissionExamWeekNum.Max(x => x.WeeklyNum),
                             StudentCount = (elayout.ExaminationRoomLayoutID == null ? 0 : elayout.EX_ExaminationRoomStudent.Count()),
                             ExamsCategoryID = plan.ExamsCategoryID,
                             ExaminationModeID = plan.ExaminationModeID,
                             ExaminationWeekNumList = emission.EM_EducationMissionExamWeekNum.Select(x => x.WeeklyNum),
                             ExaminationDate = plan.ExaminationDate,
                             StartTime = plan.StartTime,
                             EndTime = plan.EndTime,
                             ClassroomID = elayout.ClassroomID,
                             ClassroomName = eclassroom.Name,
                             Sys_User = elayout.CF_Staff.Select(x => x.Sys_User)
                         });

            return query;
        }

        /// <summary>
        /// 获取学生信息
        /// </summary>
        /// <param name="finalExaminationID"></param>
        /// <returns></returns>
        public IQueryable<BaseStudentView> GetExaminationPlanStudentList(Guid examinationPlanID)
        {
            var query = from a in ExaminationPlanRepository.GetList(x => x.ExaminationPlanID == examinationPlanID)
                        from c in a.CF_Student
                        select new BaseStudentView
                        {
                            UserID = c.UserID,
                            LoginID = c.Sys_User.LoginID,
                            UserName = c.Sys_User.Name,
                            SexID = c.SexID,
                            IDNumber = c.IDNumber,
                            StudentStatus = c.StudentStatus,
                            InSchoolStatusID = c.InSchoolStatusID,
                            ClassmajorName = c.CF_Classmajor.Name,
                            CoursematerialName = a.EM_Coursematerial.CourseName
                        };
            return query;
        }


        /// <summary>
        /// 获取所有除了已排进该排考计划的在校学生
        /// </summary>
        /// <param name="examinationPlanID"></param>
        /// <returns></returns>
        public IQueryable<StudentsView> GetGdssStudentListView(Guid? examinationPlanID, Expression<Func<CF_Student, bool>> exp)
        {
            var inschool = StudentsDAL.InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true).Select(x => x.InSchoolStatusID).ToList();
            var userIDList = from ep in ExaminationPlanRepository.GetList(x => x.ExaminationPlanID == examinationPlanID)
                             from epstu in ep.CF_Student
                             select new
                             {
                                 epstu.UserID,
                             };
            var query = from c in StudentRepository.GetList(exp).Where(x => inschool.Contains(x.InSchoolStatusID))
                        where !userIDList.Select(x => x.UserID).ToList().Contains(c.UserID)
                        select new StudentsView
                        {
                            UserID = c.UserID,
                            LoginID = c.Sys_User.LoginID,
                            UserName = c.Sys_User.Name,
                            IDNumber = c.IDNumber,
                            PhotoUrl = c.PhotoUrl,
                            IsProofread = c.IsProofread,
                            IsPhoto = c.PhotoUrl == null ? (int)CF_YesOrNoStatus.No : (int)CF_YesOrNoStatus.Yes,
                            IsPhotoUrl = c.PhotoUrl == null ? "否" : "是",
                            StudentCardNo = c.StudentCardNo,
                            PlanningGraduateDate = c.PlanningGraduateDate,//预计毕业时间
                            EntranceDate = c.CF_Recruitstudents.EntranceDate,
                            EnteringSchoolYearID = c.CF_Recruitstudents.EnteringSchoolYearID,
                            EnteringSchoolYearCode = c.CF_Recruitstudents.CF_Schoolyear.Code,
                            Score = c.CF_Recruitstudents.Score,
                            ExamineeNum = c.CF_Recruitstudents.ExamineeNum,
                            AdmissionTicketNo = c.CF_Recruitstudents.AdmissionTicketNo,
                            Area = c.CF_Recruitstudents.Area,
                            Placebirth = c.CF_StudentProfile.BornPlace,
                            CardNo = c.CF_StudentAccount.CardNo,
                            BankName = c.CF_StudentAccount.BankName,
                            SexID = c.SexID,
                            ClassMajorID = c.ClassmajorID,
                            StudentStatus = c.StudentStatus,
                            InSchoolStatusID = c.InSchoolStatusID,
                            UsedName = c.CF_StudentProfile.UsedName,
                            DirectorName = c.CF_StudentProfile.DirectorName,
                            BirthDate = c.BirthDate,
                            Country = c.CF_StudentProfile.Country,
                            Politics = c.PoliticsID,
                            Place = c.CF_StudentProfile.Place,
                            //BornPlace = c.CF_StudentProfile.BornPlace,
                            Specialty = c.CF_StudentProfile.Specialty,
                            Height = c.CF_StudentProfile.Height,
                            Weight = c.CF_StudentProfile.Weight,
                            Remarks = c.Remark,
                            GraduationPictureUrl = c.GraduationPictureUrl,
                            Email = c.CF_StudentContact.Email,
                            QQ = c.CF_StudentContact.QQ,
                            Mobile = c.CF_StudentContact.Mobile,
                            Telephone = c.CF_StudentContact.Telephone,
                            MicroMsgNo = c.CF_StudentContact.WeChatNum,
                            ZipCode = c.CF_StudentContact.ZIPCode,
                            Address = c.CF_StudentContact.Address,
                            WorkUnit = c.CF_StudentContact.WorkUnit,
                            HomeAddress = c.CF_StudentContact.HomeAddress,
                            Recipient = c.CF_StudentContact.Recipient,
                            Dormitory = c.CF_StudentContact.Dormitory,
                            StudentType = c.StudentType,
                            CultureModel = c.CF_StudentProfile.CultureModelID,
                            CertificatesType = c.CertificatesType,
                            GraduateCardNo = c.GraduateCardNo,
                            GraduateDate = c.GraduateDate,
                            IsDreamProject = c.CF_StudentProfile.IsDreamProject == true,
                            Career = c.Career,
                            ReplaceGraduateNo = c.ReplaceGraduateNo,
                            LiteracyLevels = c.CF_StudentProfile.LiteracyLevelID,
                            ClassMajorName = c.CF_Classmajor.Name,
                            ClassMajorCode = c.CF_Classmajor.No,
                            
                        };
            return query;
                        
        }

        public IQueryable<StudentAndCourseForExcelView> GetStudentAndCourseList(List<Guid?> schedulingID, List<Guid?> userID)
        {
            var query = from scheduling in educationSchedulingRepository.Entities.Where(x => schedulingID.Contains(x.EducationSchedulingID))
                        from escr in educationSchedulingClassRepository.Entities.Where(x => x.EducationSchedulingClassID == scheduling.EducationSchedulingClassID)
                        from stu in escr.CF_Student.Where(x => userID.Contains(x.UserID))
                        from user in userRepository.Entities.Where(x => x.UserID == stu.UserID)
                        select new StudentAndCourseForExcelView
                        {
                            UserID = user.UserID,
                            UserName = user.Name,
                            LoginID = user.LoginID,
                            CollegeID = user.CF_Student.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CollegeID,
                            CollegeName = user.CF_Student.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CF_College.Name,
                            ClassmajorID = user.CF_Student.CF_Classmajor.ClassmajorID,
                            ClassmajorName = user.CF_Student.CF_Classmajor.Name,
                            CourseID = escr.EM_EducationMissionClass.CoursematerialID,
                            CourseName = escr.EM_EducationMissionClass.EM_Coursematerial.CourseName
                        };
            return query;
        }
    }
}