JW/EMiS.DataLogic/ScoreManage/FinalExaminationDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Linq.Expressions;
using System.Data.Entity;
using EMIS.DataLogic.Repositories;
using EMIS.ViewModel.ScoreManage;
using EMIS.Entities;
using EMIS.ViewModel.Students;
using EMIS.ViewModel;
using Bowin.Common.Linq;

namespace EMIS.DataLogic.ScoreManage
{
    public class FinalExaminationDAL
    {
        public FinalExaminationRepository finalExaminationRepository { get; set; }
        public DepartmentRepository DepartmentRepository { get; set; }
        public FinalExaminationStudentRepository FinalExaminationStudentRepository { get; set; }

        public UserRepository userRepository { get; set; }

        public InSchoolSettingRepository InSchoolSettingRepository { get; set; }

        public EducationMissionRepository educationMissionRepository { get; set; }

        public SpecialtyPlanRepository specialtyPlanRepository { get; set; }

        public OptionalCoursePlanRepository optionalCoursePlanRepository { get; set; }

        //public FreeSelectionCousePlanRepository freeSelectionCousePlanRepository { get; set; }

        public ClassmajorRepository classmajorRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public StudentRepository studentRepository { get; set; }

        public ClassminorRepository classminorRepository { get; set; }

        public EducationMissionClassRepository educationMissionClassRepository { get; set; }

        public EducationSchedulingClassRepository educationSchedulingClassRepository { get; set; }
        public SubmitedScoreRepository SubmitedScoreRepository { get; set; }
        public FinallyScoreRepository FinallyScoreRepository { get; set; }

        public ParameterRepository ParameterRepository { get; set; }
        public Lazy<ExamsStateSettingDAL> ExamsStateSettingDAL { get; set; }
        public Lazy<MisconductDAL> MisconductDAL { get; set; }

        public CoursematerialRepository CoursematerialRepository { get; set; }
        public ExamsStateSettingRepository ExamsStateSettingRepository { get; set; }

        public ExaminationSuspensionRepository examinationSuspensionRepository { get; set; }

        /// <summary>
        /// 查询期末设定
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<FinalExaminationView> GetFinalExaminationViewQueryable(Expression<Func<ER_FinalExamination, bool>> exp)
        {
            var query = from a in finalExaminationRepository.GetList(exp)
                        join b in userRepository.Entities on a.CreatorUserID equals b.UserID
                        into gu
                        from guser in gu.DefaultIfEmpty()
                        select new FinalExaminationView
                        {
                            FinalExaminationID = a.FinalExaminationID,
                            SchoolyearID = a.SchoolyearID,
                            GradeYearID = a.GradeYearID,
                            SchoolyearCode = a.CF_Schoolyear.Code,
                            CourseCollegeID = a.CF_Department.CollegeID,
                            CollegeID = a.CollegeID,
                            CollegeName = a.CF_College.Name,
                            DepartmentID = a.DepartmentID,
                            DepartmentName = a.CF_Department.Name,
                            ClassName = a.ClassName,
                            ClassmajorName = a.CF_Classmajor.Name,
                            ClassmajorCode = a.CF_Classmajor.No,
                            ClassmajorID = a.ClassmajorID,
                            CoursematerialID = a.CoursematerialID,
                            CourseCode = a.EM_Coursematerial.CourseCode,
                            CourseName = a.EM_Coursematerial.CourseName,
                            CourseTypeID = a.CourseTypeID,
                            ExamsCategoryID = a.ExamsCategoryID,
                            ExaminationModeID = a.ExaminationModeID,
                            Credit = a.Credit,
                            //总学时
                            TotalHours = a.TotalHours,
                            ExamsDatetime = a.ExamsDatetime,
                            ResultTypeID = a.ResultTypeID,
                            CreatorUserID = a.CreatorUserID,
                            CreatorUserNo = guser.LoginID,
                            CreatorUserName = guser.Name,
                            EntryDeadlineTime = a.EntryDeadlineTime,
                            IsEntry = a.IsEntry ?? false,
                            StudentCount = a.ER_FinalExaminationStudent.Count,
                            SAPunlogStudentCount = a.ER_FinalExaminationStudent.Count - a.ER_Score.Count,
                            ExaminationType = a.ExaminationType,
                            ApprovalStatus = a.ApprovalStatus,
                            ScoreFormulaID = a.ScoreFormulaID,
                            CreditFormulaID = a.CreditFormulaID,
                            GradePointFormulaID = a.GradePointFormulaID,
                            Comment = a.Comment,
                            CreateTime = a.CreateTime,
                            CreateUserID = a.CreateUserID
                        };
            return query;
        }

        /// <summary>
        /// 生成期末设定,加上总学时
        /// </summary>
        /// <returns></returns>
        public IQueryable<FinalExaminationView> GetEducationMission(Guid? schoolyearID, Expression<Func<EM_EducationMission, bool>> exp, Expression<Func<EM_EducationMissionClass, bool>> educationMissionClassExp)
        {
            var queryMissionClass = from a in educationMissionRepository.GetList(exp)
                                    from b in a.EM_EducationMissionClass
                                    join emc in educationMissionClassRepository.GetList(educationMissionClassExp) on b.EducationMissionClassID equals emc.EducationMissionClassID
                                    join c in classmajorRepository.Entities on b.MainScheduleClassID equals c.ClassmajorID into dc
                                    from ec in dc.DefaultIfEmpty()
                                    join cm in classminorRepository.Entities on b.MainScheduleClassID equals cm.ClassminorID into dcm
                                    from ecm in dcm.DefaultIfEmpty()
                                    group a by new
                                    {
                                        a.SchoolyearID,
                                        a.EducationMissionID,
                                        GradeYearID = ec == null ? ecm.CF_Grademinor.YearID : ec.CF_Grademajor.GradeID,
                                        StandardID = ec == null ? ecm.CF_Grademinor.StandardID : ec.CF_Grademajor.CF_Facultymajor.StandardID,
                                        a.ClassName,
                                        a.CollegeID,
                                        a.DepartmentID,
                                        b.EM_Coursematerial.CoursematerialID,
                                        b.CourseTypeID,
                                        b.ExaminationModeID,
                                        b.ResultTypeID,
                                        b.EM_EducationMissionClassTeachingSetting.Credit,
                                        //学时
                                        b.EM_EducationMissionClassTeachingSetting.TheoryCourse,
                                        b.EM_EducationMissionClassTeachingSetting.Practicehours,
                                        //b.HandleModeID,
                                        SchoolyearNumID = (ec.ClassmajorID == null ?
                                            //(ecm.ClassminorID == null ? (int?)null : ((int)Math.Floor(((decimal)a.CF_Schoolyear.Value - (decimal)((ecm.CF_Grademinor.YearID * 2 - 1) + (ecm.CF_Grademinor.SchoolcodeID - 1))) / 2) + 1)) :
                                             (int?)null :
                                             ((int)Math.Floor(((decimal)a.CF_Schoolyear.Value - (decimal)((ec.CF_Grademajor.GradeID * 2 - 1) + (ec.CF_Grademajor.SemesterID - 1))) / 2) + 1))
                                    } into g
                                    select new
                                    {
                                        g.Key.SchoolyearID,
                                        g.Key.EducationMissionID,
                                        g.Key.GradeYearID,
                                        g.Key.ClassName,
                                        g.Key.CollegeID,
                                        g.Key.DepartmentID,
                                        g.Key.CoursematerialID,
                                        g.Key.CourseTypeID,
                                        g.Key.ExaminationModeID,
                                        g.Key.ResultTypeID,
                                        g.Key.Credit,
                                        g.Key.StandardID,
                                        //g.Key.HandleModeID,
                                        SchoolyearNumID = (int?)g.Key.SchoolyearNumID,
                                        //总学时
                                        TotalHours = g.Key.TheoryCourse + g.Key.Practicehours,
                                    };
            var query = from a in queryMissionClass
                        join e in finalExaminationRepository.Entities on a.EducationMissionID equals e.EducationMissionID
                        into gf
                        from gfinal in gf.DefaultIfEmpty()
                        where gfinal.EducationMissionID == null && a.SchoolyearID == schoolyearID
                        select new FinalExaminationView
                        {
                            SchoolyearID = a.SchoolyearID,
                            GradeYearID = a.GradeYearID,
                            EducationMissionID = a.EducationMissionID,
                            ClassName = a.ClassName,
                            DepartmentID = a.DepartmentID,
                            CoursematerialID = a.CoursematerialID,
                            CourseTypeID = a.CourseTypeID,
                            ExaminationModeID = a.ExaminationModeID,
                            ResultTypeID=a.ResultTypeID,
                            Credit = a.Credit,
                            CollegeID = a.CollegeID,
                            StandardID = a.StandardID,
                            TotalHours = a.TotalHours,
                            //HandleModeID=a.HandleModeID
                            //ClassmajorID = a.MainScheduleClassID
                        };
            return query;
        }
        /// <summary>
        /// 获取学生信息
        /// </summary>
        /// <param name="finalExaminationID"></param>
        /// <returns></returns>
        public IQueryable<BaseStudentView> GetFinalExaminationStudentList(Guid? finalExaminationID, Guid? userID)
        {

            var query = from a in finalExaminationRepository.GetList(x => x.FinalExaminationID == finalExaminationID)
                        from c in a.ER_FinalExaminationStudent
                        select new BaseStudentView
                        {
                            UserID = c.UserID ?? Guid.Empty,
                            LoginID = c.CF_Student.Sys_User.LoginID,
                            UserName = c.CF_Student.Sys_User.Name,
                            SexID = c.CF_Student.SexID,
                            IDNumber = c.CF_Student.IDNumber,
                            StarttermID = c.StarttermID,
                            InSchoolStatusID = c.CF_Student.InSchoolStatusID,
                            StudentStatus = c.CF_Student.StudentStatus,
                            ClassmajorCode = c.CF_Student.CF_Classmajor.No,
                            ClassmajorName = c.CF_Student.CF_Classmajor.Name,
                            CoursematerialCode = a.EM_Coursematerial.CourseCode,
                            CoursematerialName = a.EM_Coursematerial.CourseName,
                            IsDifferentDynamic = false, //取消教学任务、期末设定中涉及到异动的学生的标红显示功能  2016年11月3日16:08:27
                            RecordStatus = a.ER_Score.Where(x => x.UserID == c.UserID).FirstOrDefault().RecordStatus,
                            // IsDifferentDynamic = c.CF_DifferentDynamic.Where(x => x.ApprovalStatus == (int)EMIS.ViewModel.CF_DifferentDynamicStatus.End).Count() > 0 ? true : false
                        };
            if (userID != null)
            {
                query = from a in finalExaminationRepository.GetList(x => x.FinalExaminationID == finalExaminationID)
                        from c in a.ER_FinalExaminationStudent.Where(x => x.UserID == userID)
                        select new BaseStudentView
                        {
                            UserID = c.UserID ?? Guid.Empty,
                            LoginID = c.CF_Student.Sys_User.LoginID,
                            UserName = c.CF_Student.Sys_User.Name,
                            SexID = c.CF_Student.SexID,
                            IDNumber = c.CF_Student.IDNumber,
                            StarttermID = c.StarttermID,
                            InSchoolStatusID = c.CF_Student.InSchoolStatusID,
                            StudentStatus = c.CF_Student.StudentStatus,
                            ClassmajorCode = c.CF_Student.CF_Classmajor.No,
                            ClassmajorName = c.CF_Student.CF_Classmajor.Name,
                            CoursematerialCode = a.EM_Coursematerial.CourseCode,
                            CoursematerialName = a.EM_Coursematerial.CourseName,
                            IsDifferentDynamic = false, //取消教学任务、期末设定中涉及到异动的学生的标红显示功能  2016年11月3日16:08:27
                            RecordStatus = a.ER_Score.Where(x => x.UserID == c.UserID).FirstOrDefault().RecordStatus,
                            // IsDifferentDynamic = c.CF_DifferentDynamic.Where(x => x.ApprovalStatus == (int)EMIS.ViewModel.CF_DifferentDynamicStatus.End).Count() > 0 ? true : false
                        };
            }
            return query;
        }

        /// <summary>
        /// 查询考试设定相关学生明细表，按兵良要求，暂时用于补考设定学生明细导出功能
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<ResitStudentView> GetFinalExaminationStudentDetailsList(Expression<Func<ER_FinalExamination, bool>> exp)
        {
            var query = from a in finalExaminationRepository.GetList(exp)
                        from c in a.ER_FinalExaminationStudent
                        select new ResitStudentView
                        {
                            SchoolyearID = a.SchoolyearID,
                            SchoolyearCode = a.CF_Schoolyear.Code,
                            UserID = c.UserID ?? Guid.Empty,
                            LoginID = c.CF_Student.Sys_User.LoginID,
                            UserName = c.CF_Student.Sys_User.Name,
                            SexID = c.CF_Student.SexID,
                            Credit=a.Credit,
                            DepartmentID=a.DepartmentID,
                            CollegeID=c.CF_Student.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CF_College.CollegeID,
                            CollegeName=c.CF_Student.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CF_College.Name,
                            StandardID=c.CF_Student.CF_Classmajor.CF_Grademajor.CF_Facultymajor.StandardID,
                            ClassName = a.ClassName,
                            ClassmajorNo = c.CF_Student.CF_Classmajor.No,
                            ClassmajorName = c.CF_Student.CF_Classmajor.Name,
                            Year = c.CF_Student.CF_Classmajor.CF_Grademajor.GradeID,
                            CourseName = a.EM_Coursematerial.CourseName,
                            CourseCode = a.EM_Coursematerial.CourseCode,
                            CourseTypeID = a.CourseTypeID,
                            ExamsCategoryID = a.ExamsCategoryID,
                            Remark = a.Remark,
                        };
            return query;
        }

        /// <summary>
        /// 获取当前学年学期需要补考的学生,按兵良要求，加上院系所、年级、专业下拉条件
        /// </summary>
        /// <param name="schoolyearID"></param>
        /// <returns></returns>
        /// <remarks>
        /// 有违纪行为不能参加补考，直接参加毕业清考，补考可以重复生成，每次只生成未有补考记录的数据
        /// </remarks>
        public IQueryable<ResitStudentView> GetResitFinalExaminationStudent(Guid? schoolyearID)
        {
            List<int?> resitStatus = ExamsStateSettingDAL.Value.GetResitIDList();
            //ER_ExamsStateSetting examsStateSetting = ExamsStateSettingRepository.GetSingle(x => x.ExamsStateID == (int)CF_ExamsState.Suspension);
            var query = from fs in FinallyScoreRepository.GetList(x => x.TotalScore < 60 && x.SchoolyearID == schoolyearID && x.ExamsCategoryID == (int)CF_ExamsCategory.FinalExam && resitStatus.Contains(x.ExamsStateID))
                        join iss in InSchoolSettingRepository.GetList(x => x.IsSelected == true)
                            on fs.CF_Student.InSchoolStatusID equals iss.InSchoolStatusID
                        join exam in ExamsStateSettingRepository.GetList(x => x.ResitCategory == (int)CF_ExamsCategory.Resit)
                            on fs.ExamsStateID equals exam.ExamsStateID
                        join rfes in FinalExaminationStudentRepository.GetList(x => x.ER_FinalExamination.ExamsCategoryID == (int)CF_ExamsCategory.Resit)
                            on new { fs.CoursematerialID, fs.UserID, fs.StarttermID } equals new { rfes.ER_FinalExamination.CoursematerialID, rfes.UserID, rfes.StarttermID }
                            into drfes
                        from erfes in drfes.Where(x => x.CreateTime > fs.CreateTime).DefaultIfEmpty()
                        //join ss in SubmitedScoreRepository.GetList(x => misconductStatus.Contains(x.ExamsStateID))
                        //    on new { fs.CoursematerialID, fs.UserID, fs.StarttermID } equals new { ss.CoursematerialID, ss.UserID, ss.StarttermID }
                        //    into dss
                        //from ess in dss.DefaultIfEmpty()
                        where erfes.FinalExaminationStudentID == null
                        //&& ess.SubmitedScoreID == null
                        select new ResitStudentView
                        {
                            UserID = fs.CF_Student.UserID,
                            Year = fs.CF_Student.CF_Classmajor.CF_Grademajor.GradeID,
                            CourseCollegeID = fs.CF_Department.CollegeID,
                            CollegeID = fs.CF_Student.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CollegeID,
                            StandardID = fs.CF_Student.CF_Classmajor.CF_Grademajor.CF_Facultymajor.StandardID,
                            GrademajorID = fs.CF_Student.CF_Classmajor.GrademajorID,
                            GrademajorName = fs.CF_Student.CF_Classmajor.CF_Grademajor.Name,
                            CoursematerialID = fs.CoursematerialID,
                            DepartmentID = fs.DepartmentID,
                            CourseName = fs.EM_Coursematerial.CourseName,
                            CourseTypeID = fs.CourseTypeID,
                            ExaminationModeID = fs.ExaminationModeID,
                            SchoolyearNumID = fs.SchoolyearNumID,
                            StartTermID = fs.StarttermID,
                            Credit = fs.Credit,
                            ResultTypeID = fs.ResultTypeID,
                            ClassmajorID = fs.CF_Student.CF_Classmajor.ClassmajorID,
                        };
            return query;
        }

        /// <summary>
        /// 获取当前学年学期缓考的学生
        /// </summary>
        /// <param name="schoolyearID"></param>
        /// <returns></returns>
        /// <remarks>
        /// 
        /// </remarks>
        //public IQueryable<ResitStudentView> GetSuspensionStudent(Guid? schoolyearID)
        //{
        //    //List<int?> suspensionStatus = ExamsStateSettingDAL.Value.GetSuspensionIDList();
        //    int? resitCategory = ExamsStateSettingDAL.Value.GetSuspensionResitCategory();
        //    var query = from fs in FinallyScoreRepository.GetList(x => x.TotalScore < 60 && x.SchoolyearID == schoolyearID && x.ExamsCategoryID == (int)CF_ExamsCategory.FinalExam && x.ExamsStateID == (int)CF_ExamsState.Suspension)
        //                join iss in InSchoolSettingRepository.GetList(x => x.IsSelected == true)
        //                    on fs.CF_Student.InSchoolStatusID equals iss.InSchoolStatusID
        //                //join fi in finalExaminationRepository.Entities
        //                //on new { fs.ClassName, fs.CoursematerialID, fs.ExamsCategoryID, fs.ExaminationModeID } equals new { fi.ClassName, fi.CoursematerialID, fi.ExamsCategoryID, fi.ExaminationModeID }
        //                //into a
        //                //from aa in a.DefaultIfEmpty()
        //                join exam in ExamsStateSettingRepository.GetList(x => x.ResitCategory == (int)CF_ExamsCategory.DelayExam)
        //                    on fs.ExamsStateID equals exam.ExamsStateID
        //                join rfes in FinalExaminationStudentRepository.GetList(x => x.ER_FinalExamination.ExamsCategoryID == (int)CF_ExamsCategory.FinalExam && x.ER_FinalExamination.ExaminationType == (int)CF_ExaminationType.ResitExamination)
        //                    on new { fs.CoursematerialID, fs.UserID, fs.StarttermID } equals new { rfes.ER_FinalExamination.CoursematerialID, rfes.UserID, rfes.StarttermID }
        //                    into drfes
        //                from erfes in drfes.DefaultIfEmpty()
        //                //join ss in SubmitedScoreRepository.GetList(x => suspensionStatus.Contains(x.ExamsStateID))
        //                //    on new { fs.CoursematerialID, fs.UserID, fs.StarttermID } equals new { ss.CoursematerialID, ss.UserID, ss.StarttermID }
        //                //    into dss
        //                //from ess in dss.DefaultIfEmpty()
        //                where erfes.FinalExaminationStudentID == null
        //                //&& ess.SubmitedScoreID == null
        //                select new ResitStudentView
        //                {
        //                    UserID = fs.CF_Student.UserID,
        //                    Year = fs.CF_Student.CF_Classmajor.CF_Grademajor.SchoolyearID,
        //                    CourseCollegeID = fs.CF_Department.CollegeID,
        //                    CollegeID = fs.CF_Student.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CollegeID,
        //                    StandardID = fs.CF_Student.CF_Classmajor.CF_Grademajor.CF_Facultymajor.StandardID,
        //                    GrademajorID = fs.CF_Student.CF_Classmajor.GrademajorID,
        //                    GrademajorName = fs.CF_Student.CF_Classmajor.CF_Grademajor.Name,
        //                    CoursematerialID = fs.CoursematerialID,
        //                    DepartmentID = fs.DepartmentID,
        //                    CourseName = fs.EM_Coursematerial.CourseName,
        //                    CourseTypeID = fs.CourseTypeID,
        //                    ExaminationModeID = fs.ExaminationModeID,
        //                    SchoolyearNumID = fs.SchoolyearNumID,
        //                    StartTermID = fs.StarttermID,
        //                    Credit = fs.Credit,
        //                    ResultTypeID = fs.ResultTypeID,
        //                    ClassmajorID = fs.CF_Student.CF_Classmajor.ClassmajorID,
        //                };
        //    return query;
        //}


        /// <summary>
        /// 补考设定，加上学时
        /// </summary>
        /// <param name="schoolyearID"></param>
        /// <returns></returns>
        public virtual IQueryable<ResitStudentView> GetNotPassStudent(Guid? schoolyearID)
        {
            List<int?> resitStatus = ExamsStateSettingDAL.Value.GetResitIDList();
            var query = from fs in FinallyScoreRepository.GetList(x => (x.TotalScore == null || x.TotalScore < 60) && x.SchoolyearID == schoolyearID && x.ExamsCategoryID == (int)CF_ExamsCategory.FinalExam && resitStatus.Contains(x.ExamsStateID))
                        from s in studentRepository.Entities.Where(x => fs.UserID == x.UserID)
                        join iss in InSchoolSettingRepository.GetList(x => x.IsSelected == true)
                            on s.InSchoolStatusID equals iss.InSchoolStatusID
                        join exam in ExamsStateSettingRepository.Entities
                            on fs.ExamsStateID equals exam.ExamsStateID
                        from erfes in (
                            from student in FinalExaminationStudentRepository.Entities 
                            from final in finalExaminationRepository.Entities.Where(x => x.FinalExaminationID == student.FinalExaminationID) 
                            where final.CoursematerialID == fs.CoursematerialID 
                                && student.UserID == fs.UserID && student.StarttermID == fs.StarttermID && final.ExamsCategoryID == exam.ResitCategory 
                                && student.CreateTime > fs.CreateTime
                            select student
                            ).DefaultIfEmpty()
                        ////join ss in SubmitedScoreRepository.GetList(x => misconductStatus.Contains(x.ExamsStateID))
                        ////    on new { fs.CoursematerialID, fs.UserID, fs.StarttermID } equals new { ss.CoursematerialID, ss.UserID, ss.StarttermID }
                        ////    into dss
                        ////from ess in dss.DefaultIfEmpty()
                        where erfes.FinalExaminationStudentID == null
                        //&& ess.SubmitedScoreID == null
                        select new ResitStudentView
                        {
                            UserID = fs.CF_Student.UserID,
                            Year = fs.CF_Student.CF_Classmajor.CF_Grademajor.GradeID,
                            CourseCollegeID = fs.CF_Department.CollegeID,
                            CollegeID = fs.CF_Student.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CollegeID,
                            StandardID = fs.CF_Student.CF_Classmajor.CF_Grademajor.CF_Facultymajor.StandardID,
                            GrademajorID = fs.CF_Student.CF_Classmajor.GrademajorID,
                            GrademajorName = fs.CF_Student.CF_Classmajor.CF_Grademajor.Name,
                            CoursematerialID = fs.CoursematerialID,
                            DepartmentID = fs.DepartmentID,
                            CourseName = fs.EM_Coursematerial.CourseName,
                            CourseTypeID = fs.CourseTypeID,
                            ExaminationModeID = fs.ExaminationModeID,
                            SchoolyearNumID = fs.SchoolyearNumID,
                            StartTermID = fs.StarttermID,
                            Credit = fs.Credit,
                            //加上学时
                            TotalHours = fs.TotalHours,
                            ResultTypeID = fs.ResultTypeID,
                            ClassmajorID = fs.CF_Student.CF_Classmajor.ClassmajorID,
                            ExamsCategoryID = exam.ResitCategory,
                            CreatorUserID = fs.CreatorUserID,
                        };
            return query;
        }

        /// <summary>
        /// 获取需参加毕业清考的学生,加上总学时
        /// </summary>
        /// <param name="schoolyearID"></param>
        /// <returns></returns>
        /// <remarks>
        /// 不管有没有补考，只要成绩不及格（包括违纪导致的不及格），都将纳入毕业清考，毕业清考可以重复生成，每次只生成未有毕业清考记录的数据
        /// </remarks>
        public IQueryable<ResitStudentView> GetGraduationExamStudent(int? year)
        {
            //bool isMisconductCanGraduateExam = true;
            //var parameter = ParameterRepository.GetSingle(x => x.ParameterTypeID == (int)CF_ParameterType.IsMisconductCanGraduateExam);

            //if (parameter != null)
            //{
            //    isMisconductCanGraduateExam = (parameter.Value == "True");
            //}
            //List<int?> misconductStatus = ExamsStateSettingDAL.Value.GetMisconductIDList();

            //var query = from fs in FinallyScoreRepository.Entities.GroupBy(x => new { 
            //                    x.UserID,
            //                    x.CoursematerialID, 
            //                    x.DepartmentID, 
            //                    x.CF_Department.CollegeID,
            //                    x.CourseTypeID, 
            //                    x.ExaminationModeID,
            //                    x.Credit,
            //                    x.ResultTypeID,
            //                })
            //                .Select(x => new
            //                {
            //                    x.Key.UserID,
            //                    x.Key.CoursematerialID,
            //                    x.Key.DepartmentID,
            //                    x.Key.CollegeID,
            //                    x.Key.CourseTypeID,
            //                    x.Key.ExaminationModeID,
            //                    x.Key.Credit,
            //                    x.Key.ResultTypeID,
            //                    TotalScore = x.Max(w => w.TotalScore),
            //                    SchoolyearNumID = x.Max(w => w.SchoolyearNumID),
            //                    StarttermID = x.Max(w => w.StarttermID) })
            //                .Where(x => x.TotalScore < 60)
            //            join s in studentRepository.GetList(x => x.CF_Classmajor.CF_Grademajor.SchoolyearID == year) 
            //                on fs.UserID equals s.UserID 
            //            join cm in CoursematerialRepository.Entities on fs.CoursematerialID equals cm.CoursematerialID 
            //            join iss in InSchoolSettingRepository.GetList(x => x.IsSelected == true)
            //                on s.InSchoolStatusID equals iss.InSchoolStatusID
            //            //关联补考设定，看看有没有重复生成
            //            join rfes in FinalExaminationStudentRepository.GetList(x => x.ER_FinalExamination.ExamsCategoryID == (int)CF_ExamsCategory.GraduationExam)
            //                on new { fs.CoursematerialID, fs.UserID, fs.StarttermID } equals new { rfes.ER_FinalExamination.CoursematerialID, rfes.UserID, rfes.StarttermID }
            //                into drfes
            //            from erfes in drfes.DefaultIfEmpty()
            //            //检查是否有补考违纪行为
            //            join ss in SubmitedScoreRepository.GetList(x => misconductStatus.Contains(x.ExamsStateID) && x.ExamsCategoryID == (int)CF_ExamsCategory.Resit)
            //                on new { fs.CoursematerialID, fs.UserID } equals new { ss.CoursematerialID, ss.UserID }
            //                into dss
            //            from ess in dss.DefaultIfEmpty()
            //            where erfes.FinalExaminationStudentID == null && (isMisconductCanGraduateExam || ess.SubmitedScoreID == null)
            //            select new ResitStudentView
            //            {
            //                UserID = s.UserID,
            //                Year = s.CF_Classmajor.CF_Grademajor.SchoolyearID,
            //                CourseCollegeID = fs.CollegeID,
            //                CollegeID = s.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CollegeID,
            //                GrademajorID = s.CF_Classmajor.GrademajorID,
            //                GrademajorName = s.CF_Classmajor.CF_Grademajor.Name,
            //                CoursematerialID = fs.CoursematerialID,
            //                DepartmentID = fs.DepartmentID,
            //                CourseName = cm.CourseName,
            //                CourseTypeID = fs.CourseTypeID,
            //                ExaminationModeID = fs.ExaminationModeID,
            //                SchoolyearNumID = fs.SchoolyearNumID,
            //                StartTermID = fs.StarttermID,
            //                Credit = fs.Credit,
            //                ResultTypeID = fs.ResultTypeID,
            //                StandardID = s.CF_Classmajor.CF_Grademajor.CF_Facultymajor.StandardID,
            //                ClassmajorID = s.CF_Classmajor.ClassmajorID,
            //            };
            List<int?> canGraduatelist = ExamsStateSettingDAL.Value.GetCanGraduateList();

            var query = from fs in (
                            from fs in FinallyScoreRepository.GetList(x => canGraduatelist.Contains(x.ExamsStateID))
                            join d in DepartmentRepository.Entities on fs.DepartmentID equals d.DepartmentID
                            join s in studentRepository.GetList(x => x.CF_Classmajor.CF_Grademajor.GradeID == year)
                                on fs.UserID equals s.UserID
                            join iss in InSchoolSettingRepository.GetList(x => x.IsSelected == true)
                                on s.InSchoolStatusID equals iss.InSchoolStatusID
                            join c in classmajorRepository.Entities on s.ClassmajorID equals c.ClassmajorID 
                            join g in GrademajorRepository.Entities on c.GrademajorID equals g.GrademajorID 
                            join f in FacultymajorRepository.Entities on g.FacultymajorID equals f.FacultymajorID 
                            group fs by new
                            {
                                s.UserID,
                                Year = g.GradeID,
                                f.CollegeID,
                                c.ClassmajorID,
                                g.GrademajorID,
                                GradeName = g.Name,
                                f.StandardID,
                                fs.CoursematerialID,
                                fs.DepartmentID,
                                CourseCollegeID = d.CollegeID,
                                fs.CourseTypeID,
                                fs.ExaminationModeID,
                                fs.Credit,
                                f.LearnSystem,
                                //总学时
                                fs.TotalHours,
                                fs.ResultTypeID,
                            } into g 
                            select new
                            {
                                g.Key.UserID,
                                g.Key.Year,
                                g.Key.CollegeID,
                                g.Key.ClassmajorID,
                                g.Key.GrademajorID,
                                g.Key.GradeName,
                                g.Key.StandardID,
                                g.Key.CoursematerialID,
                                g.Key.DepartmentID,
                                g.Key.CourseCollegeID,
                                g.Key.CourseTypeID,
                                g.Key.ExaminationModeID,
                                g.Key.Credit,
                                //总学时
                                g.Key.TotalHours,
                                g.Key.ResultTypeID,
                                g.Key.LearnSystem,
                                TotalScore = g.Max(w => w.TotalScore),
                                SchoolyearNumID = g.Max(w => w.SchoolyearNumID),
                                StarttermID = g.Max(w => w.StarttermID),
                                CreatorUserID = g.Max(w => w.CreatorUserID.ToString()),
                            })
                        join cm in CoursematerialRepository.Entities on fs.CoursematerialID equals cm.CoursematerialID 
                        join cu in userRepository.Entities on fs.CreatorUserID equals cu.UserID.ToString() 
                        //关联补考设定，看看有没有重复生成
                        join rfes in (
                            from rfes in FinalExaminationStudentRepository.Entities 
                            join rfe in finalExaminationRepository.Entities on rfes.FinalExaminationID equals rfe.FinalExaminationID
                            where rfe.ExamsCategoryID == (int)CF_ExamsCategory.GraduationExam
                            select rfes
                            )
                            on new { fs.CoursematerialID, UserID = (Guid?)fs.UserID, fs.StarttermID } equals new { rfes.ER_FinalExamination.CoursematerialID, rfes.UserID, rfes.StarttermID }
                            into drfes
                        from erfes in drfes.DefaultIfEmpty()
                        where erfes.FinalExaminationStudentID == null && fs.TotalScore < 60
                        select new ResitStudentView
                        {
                            UserID = fs.UserID,
                            Year = fs.Year,
                            CourseCollegeID = fs.CourseCollegeID,
                            CollegeID = fs.CollegeID,
                            GrademajorID = fs.GrademajorID,
                            GrademajorName = fs.GradeName,
                            CoursematerialID = fs.CoursematerialID,
                            DepartmentID = fs.DepartmentID,
                            LearnSystem = fs.LearnSystem,
                            CourseName = cm.CourseName,
                            CourseTypeID = fs.CourseTypeID,
                            ExaminationModeID = fs.ExaminationModeID,
                            SchoolyearNumID = fs.SchoolyearNumID,
                            StartTermID = fs.StarttermID,
                            Credit = fs.Credit,
                            TotalHours = fs.TotalHours,
                            ResultTypeID = fs.ResultTypeID,
                            StandardID = fs.StandardID,
                            ClassmajorID = fs.ClassmajorID,
                            CreatorUserID = cu.UserID
                        };

            return query;
        }

        public IQueryable<ER_FinalExaminationStudent> GetFinalExaminationByResitStudent(Expression<Func<ER_FinalExamination, bool>> exp, IList<Guid?> userIDList)
        {
            List<int?> resitCategory = new List<int?> { (int)CF_ExamsCategory.Resit, (int)CF_ExamsCategory.GraduationExam };

            exp = exp.And(x => resitCategory.Contains(x.ExamsCategoryID));

            var maxStartTerm = from rfe in finalExaminationRepository.GetList(exp)
                               join fe in finalExaminationRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE
                                   && x.ExamsCategoryID == (int)CF_ExamsCategory.FinalExam)
                                   on rfe.CoursematerialID equals fe.CoursematerialID
                               from fes in fe.ER_FinalExaminationStudent
                               where userIDList.Contains(fes.UserID)
                               group fes by new { fes.UserID, fe.CoursematerialID } into g
                               select new { g.Key.UserID, g.Key.CoursematerialID, StarttermID = g.Max(x => x.StarttermID) };
            var query = from fes in FinalExaminationStudentRepository.GetList(x => x.ER_FinalExamination.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                        join fe in finalExaminationRepository.GetList(x => x.ExamsCategoryID == (int)CF_ExamsCategory.FinalExam)
                            on fes.FinalExaminationID equals fe.FinalExaminationID
                        join mst in maxStartTerm
                            on new { fes.UserID, fe.CoursematerialID, fes.StarttermID }
                            equals new { mst.UserID, mst.CoursematerialID, mst.StarttermID }
                        select fes;

            return query.Include(x => x.ER_FinalExamination);
        }
    }
}