JW/EMiS.DataLogic/ScoreManage/ScoreDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EMIS.DataLogic.Repositories;
using EMIS.ViewModel.ScoreManage;
using EMIS.Entities;
using System.Linq.Expressions;
using EMIS.ViewModel;
using EMIS.DataLogic.ChargeManage.ArrearsSituation;

namespace EMIS.DataLogic.ScoreManage
{
    public class ScoreDAL
    {
        public FinalExaminationRepository finalExaminationRepository { get; set; }
        public FinalExaminationStudentRepository finalExaminationStudentRepository { get; set; }
        public ScoreRepository ScoreRepository { get; set; }
        public ScoreDetailRepository ScoreDetailRepository { get; set; }
        public UserRepository userRepository { get; set; }
        public FinallyScoreDetailRepository FinallyScoreDetailRepository { get; set; }
        public FinallyScoreRepository finallyScoreRepository { get; set; }
        public ResultTypeDetailRepository ResultTypeDetailRepository { get; set; } 
        public SubmitedScoreRepository submitedScoreRepository { get; set; }
        public ScoreFormulaRepository scoreFormulaRepository { get; set; }
        public GradePointFormulaRepository gradePointFormulaRepository { get; set; }
        public CreditFormulaRepository creditFormulaRepository { get; set; }
        public Lazy<ExamsStateSettingDAL> ExamsStateSettingDAL { get; set; }
        public Lazy<ArrearsListDAL> ArrearsListDAL { get; set; }
        public ExaminationSuspensionRepository ExaminationSuspensionRepository { get; set; }
        public ExaminationExemptionRepository ExaminationExemptionRepository { get; set; }
        public MisconductRepository MisconductRepository { get; set; }
        public ScoreParameterCollegeSettingRepository ScoreParameterCollegeSettingRepository { get; set; }
        public EducationMissionClassRepository EducationMissionClassRepository { get; set; }
        public EducationMissionRepository EducationMissionRepository { get; set; }
        public ScoreConvertByLevelScoreRepository scoreConvertByLevelScoreRepository { get; set; }
        public ScoreConvertByReplaceRepository scoreConvertByReplaceRepository { get; set; }
        public ScoreConvertByApplyRepository scoreConvertByApplyRepository { get; set; }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<FinalExaminationView> GetScoreViewQueryable(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,
                            SchoolyearCode = a.CF_Schoolyear.Code,
                            GradeYearID = a.GradeYearID,
                            CourseCollegeID = a.CF_Department.CollegeID,
                            CollegeID = a.CollegeID,
                            CollegeName = a.CF_College.Name,
                            DepartmentID = a.DepartmentID,
                            DepartmentName = a.CF_Department.Name,
                            ClassName = a.ClassName,
                            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,
                            ExamsDatetime = a.ExamsDatetime,
                            ResultTypeID = a.ResultTypeID,
                            CreatorUserID = a.CreatorUserID,
                            CreatorUserNo = guser.LoginID,
                            CreatorUserName = guser.Name,
                            EntryDeadlineTime = a.EntryDeadlineTime,
                            //IsEntry = a.ApprovalStatus == (int)EMIS.ViewModel.CF_ScoreState.Submitted || a.ApprovalStatus == (int)EMIS.ViewModel.CF_ScoreState.Approved || a.ApprovalStatus == 6 ? false : a.IsEntry ?? false,
                            IsEntry = a.IsEntry ?? false,
                            StudentCount = a.ER_FinalExaminationStudent.Count,
                            SAPunlogStudentCount = a.ER_FinalExaminationStudent.Count - a.ER_Score.Count,
                            ExaminationType = a.ExaminationType,
                            ApprovalStatus = a.ApprovalStatus,
                            Remarks = a.Remark,
                            CreateTime = a.CreateTime,
                            CreateUserID = a.CreateUserID,
                            ScoreFormulaID = a.ScoreFormulaID,
                            TotalHours = a.TotalHours,
                        };
            return query;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="finalExaminationID"></param>
        /// <param name="suspensionEndStatusID"></param>
        /// <param name="misconductEndStatusID"></param>
        /// <param name="exemptionEndStatusID"></param>
        /// <param name="chargeDelayEndStatusID"></param>
        /// <param name="isChargeControl"></param>
        /// <returns></returns>
        public IQueryable<StudentScoreInputView> GetStudentScoreInputViewQuqeryable(Guid? finalExaminationID, int? suspensionEndStatusID,
            int? misconductEndStatusID, int? exemptionEndStatusID, int? chargeDelayEndStatusID, bool isChargeControl, int? applyConvertEndStatus, int? replaceConvertEndStatus)
        {
            var normalExamsStateID = ExamsStateSettingDAL.Value.GetNormalID();
            var suspensionStateID = ExamsStateSettingDAL.Value.GetExaminationSuspensionID();
            var exemptionStateID = ExamsStateSettingDAL.Value.GetExaminationExemptionID();

            var query = from exam in finalExaminationRepository.GetList(x => x.FinalExaminationID == finalExaminationID)
                        from student in exam.ER_FinalExaminationStudent
                        join score in ScoreRepository.Entities
                            on new { exam.FinalExaminationID, student.UserID }
                            equals new { FinalExaminationID = (Guid)score.FinalExaminationID, score.UserID } into gs
                        from score in gs.DefaultIfEmpty()
                        join suspension in ExaminationSuspensionRepository.Entities.Where(x => x.RecordStatus == suspensionEndStatusID)
                            on new { exam.SchoolyearID, exam.ExamsCategoryID, exam.CoursematerialID, student.UserID }
                            equals new { suspension.SchoolyearID, suspension.ExamsCategoryID, suspension.CoursematerialID, suspension.UserID }
                            into dsuspension
                        from suspension in dsuspension.DefaultIfEmpty()
                        join misconduct in MisconductRepository.Entities.Where(x => x.RecordStatus == misconductEndStatusID)
                            on new { exam.SchoolyearID, exam.ExamsCategoryID, exam.CoursematerialID, student.UserID }
                            equals new { misconduct.SchoolyearID, misconduct.ExamsCategoryID, misconduct.CoursematerialID, misconduct.UserID }
                            into dmisconduct
                        from misconduct in dmisconduct.DefaultIfEmpty()
                        join exemption in ExaminationExemptionRepository.Entities.Where(x => x.RecordStatus == exemptionEndStatusID)
                            on new { exam.SchoolyearID, exam.ExamsCategoryID, exam.CoursematerialID, student.UserID }
                            equals new { exemption.SchoolyearID, exemption.ExamsCategoryID, exemption.CoursematerialID, exemption.UserID }
                            into dexemption
                        from exemption in dexemption.DefaultIfEmpty()
                        //join level in scoreConvertByLevelScoreRepository.Entities.Where(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                        //    on new { exam.SchoolyearID, exam.CoursematerialID, student.UserID }
                        //    equals new { level.SchoolyearID, level.CoursematerialID, level.ER_LevelScore.UserID}
                        //    into dlevel
                        //from flevel in dlevel.DefaultIfEmpty()
                        //join apply in scoreConvertByApplyRepository.Entities.Where(x => x.RecordStatus == applyConvertEndStatus)
                        //    on new { exam.SchoolyearID, exam.CoursematerialID, student.UserID }
                        //    equals new { apply.SchoolyearID, apply.CoursematerialID, apply.UserID }
                        //    into dapply
                        //from fapply in dapply.DefaultIfEmpty()
                        //join replace in scoreConvertByReplaceRepository.Entities.Where(x => x.RecordStatus == replaceConvertEndStatus)
                        //    on new { exam.SchoolyearID, CoursematerialID = exam.CoursematerialID, student.UserID }
                        //    equals new { replace.SchoolyearID, CoursematerialID = replace.TargetCoursematerialID, replace.UserID }
                        //    into dreplace
                        //from freplace in dreplace.DefaultIfEmpty()
                        join final in finallyScoreRepository.Entities //有问题，后续修复。。。
                            on new { exam.SchoolyearID, CoursematerialID = exam.CoursematerialID, student.UserID }
                            equals new { final.SchoolyearID, CoursematerialID = final.CoursematerialID, final.UserID }
                            into dfianl
                        from ffinal in dfianl.DefaultIfEmpty()
                        select new StudentScoreInputView
                        {
                            ScoreID = score.ScoreID,
                            CoursematerialID = exam.CoursematerialID,
                            FinalExaminationID = exam.FinalExaminationID,
                            UserID = student.UserID,
                            LoginID = student.CF_Student.Sys_User.LoginID,
                            UserName = student.CF_Student.Sys_User.Name,
                            ExamsCategoryID = exam.ExamsCategoryID,
                            ExamsStateID = score.ExamsStateID == null ?
                                (misconduct.MisconductID != null ? misconduct.ExamsStateID :
                                    (exemption.ExaminationExemptionID != null ? exemptionStateID :
                                        ((suspension.ExaminationSuspensionID != null && exam.ExamsCategoryID == (int)CF_ExamsCategory.FinalExam) ? suspensionStateID : normalExamsStateID)
                                    )
                                 )
                                : score.ExamsStateID,
                            TotalScore = score.TotalScore,
                            //成绩录入页面读取成绩时，如果是成绩认定得到的成绩，则取成绩认定的成绩，并且不可修改。如果有不止一种成绩认定，那么取时间最大(也就是最晚认定的)那一个的成绩
                            //TotalScore = (flevel == null && fapply == null && freplace == null) ? score.TotalScore : 
                            //(
                            //(flevel != null && fapply != null && freplace != null) ? (flevel.ModifyTime > fapply.ModifyTime && flevel.ModifyTime > freplace.ModifyTime ? flevel.TotalScore : (fapply.ModifyTime > freplace.ModifyTime ? fapply.TotalScore : freplace.TotalScore)) :
                            //((flevel != null && fapply == null && freplace == null) ? flevel.TotalScore :
                            // ((flevel != null && fapply != null && freplace == null) ? (flevel.ModifyTime > fapply.ModifyTime ? flevel.TotalScore : fapply.TotalScore):
                            //  ((flevel == null && fapply != null && freplace == null) ? fapply.TotalScore :
                            //   ((flevel == null && fapply != null && freplace != null) ? (fapply.ModifyTime > freplace.ModifyTime ? fapply.TotalScore : freplace.TotalScore):
                            //    ((flevel == null && fapply == null && freplace != null) ? freplace.TotalScore : score.TotalScore
                            //    )
                            //   )
                            //  )
                            // )
                            //)
                            //),
                            //flevel != null ? flevel.TotalScore : (fapply != null ? fapply.TotalScore : (freplace != null ? freplace.TotalScore : score.TotalScore)),
                            //flevel != null ? (fapply != null ? (freplace != null ? ):):
                            Credit = score.Credit,
                            GradePoint = score.GradePoint,
                            Remarks = score.Remark,
                            StarttermID = student.StarttermID,
                            RecordStatus = score.RecordStatus == null ? (int)SYS_STATUS.USABLE : score.RecordStatus,
                            //这里是整条记录的可编辑状态，不是针对每个明细的，暂时只有一种情况整行都不可编辑，那就是重录时未涉及的学生
                            //成绩认定的学生成绩也不可编辑
                            IsCanEdit = (score.RecordStatus == null ? (int)EMIS.ViewModel.SYS_STATUS.USABLE : score.RecordStatus) > (int)SYS_STATUS.UNUSABLE,
                            //IsConvert = flevel != null || fapply != null || freplace != null ? true : false
                        };

            //如果有缴费控制的话，是否可编辑会受影响，是否直接获得学分、是否计算总分也会受影响
            if (isChargeControl)
            {
                query = (from score in query
                         join arrear in ArrearsListDAL.Value
                         .GetArrearListViewAble(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE, 
                            x => x.RecordStatus == chargeDelayEndStatusID)
                         on score.UserID equals arrear.UserID
                         into darrear
                         from arrear in darrear.DefaultIfEmpty()
                         select new StudentScoreInputView
                         {
                             ScoreID = score.ScoreID,
                             CoursematerialID = score.CoursematerialID,
                             FinalExaminationID = score.FinalExaminationID,
                             UserID = score.UserID,
                             LoginID = score.LoginID,
                             UserName = score.UserName,
                             ExamsCategoryID = score.ExamsCategoryID,
                             ExamsStateID = score.ExamsStateID,
                             TotalScore = score.TotalScore,
                             Credit = score.Credit,
                             GradePoint = score.GradePoint,
                             Remarks = (arrear.UserID == null) ? score.Remarks : "该学生由于欠费，无法录入。",
                             StarttermID = score.StarttermID,
                             RecordStatus = score.RecordStatus,
                             IsCanEdit = (arrear.UserID == null) ? score.IsCanEdit : false,
                             //IsConvert = score.IsConvert,
                         });
            }

            return query;
        }

        public IQueryable<StudentScoreInputDetailView> GetStudentScoreInputDetailViewQuqeryable(Guid? finalExaminationID)
        {
            var sql = (from detail in ScoreDetailRepository.Entities
                       join score in ScoreRepository.GetList(x => x.FinalExaminationID == finalExaminationID)
                        on detail.ScoreID equals score.ScoreID
                       join exam in finalExaminationRepository.Entities on score.FinalExaminationID equals exam.FinalExaminationID
                       join yearSetting in ScoreParameterCollegeSettingRepository.GetList(x => x.CollegeID == null)
                        on new { Years = exam.GradeYearID, detail.ScoreTypeID }
                        equals new { yearSetting.Years, yearSetting.ScoreTypeID } into dyearSetting
                       from yearSetting in dyearSetting.DefaultIfEmpty()
                       join detailSetting in ScoreParameterCollegeSettingRepository.Entities
                        on new { Years = exam.GradeYearID, exam.CollegeID, detail.ScoreTypeID }
                        equals new { detailSetting.Years, detailSetting.CollegeID, detailSetting.ScoreTypeID } into ddetailSetting
                       from detailSetting in ddetailSetting.DefaultIfEmpty() 
                       select new StudentScoreInputDetailView
                       {
                           UserID = score.UserID,
                           ScoreTypeID = detail.ScoreTypeID,
                           Score = detail.Score,
                           IsCanEdit = ((detailSetting.ScoreParameterCollegeSettingID == null && yearSetting.ScoreParameterCollegeSettingID == null) || exam.ExamsCategoryID != (int)CF_ExamsCategory.FinalExam),
                           RecordStatus = score.RecordStatus
                       });

            return sql;
        }

        public IQueryable<StudentScoreInputDetailView> GetLastPassedStudentScoreForResit(Guid? finalExaminationID)
        {
            var query = from resitStudent in finalExaminationStudentRepository.Entities.Where(x => x.FinalExaminationID == finalExaminationID)
                        join resit in finalExaminationRepository.Entities on resitStudent.FinalExaminationID equals resit.FinalExaminationID
                        join finaly in finallyScoreRepository.Entities //.Where(x => x.ExamsCategoryID == (int)CF_ExamsCategory.FinalExam)
                            on new { resit.CoursematerialID, resitStudent.UserID, resitStudent.StarttermID, resitStudent.SchoolyearNumID }
                            equals new { finaly.CoursematerialID, finaly.UserID, finaly.StarttermID, finaly.SchoolyearNumID }
                        join finalyDetail in FinallyScoreDetailRepository.Entities on finaly.FinallyScoreID equals finalyDetail.FinallyScoreID 
                        where (finalyDetail.Score >= 60 || finalyDetail.ScoreTypeID == (int)CF_ScoreType.Peacetime)
                        select new StudentScoreInputDetailView
                        {
                            UserID = resitStudent.UserID,
                            ScoreTypeID = finalyDetail.ScoreTypeID,
                            Score = finalyDetail.Score,
                            IsCanEdit = true,
                            RecordStatus = finalyDetail.RecordStatus
                        };
            return query;
        }

        /// <summary>
        /// 删除已提交成绩的方法(所有涉及删除成绩的地方都是调用此方法)
        /// </summary>
        /// <param name="finalExaminationID"></param>
        /// <param name="listUserID"></param>
        /// <returns></returns>
        public IQueryable<StudentScoreView> GetDeleteSubmittedScore(Guid finalExaminationID, List<Guid?> listUserID)
        {
            var query = from score in submitedScoreRepository.GetList(x => x.FinalExaminationID == finalExaminationID)
                        where listUserID.Contains(score.UserID)
                        select new StudentScoreView
                        {
                            SubmitedScoreID = score.SubmitedScoreID,
                            UserID = score.UserID,
                            ExamsCategoryID = score.ExamsCategoryID,
                            StarttermID = score.StarttermID
                        };
            return query;
        }

        /// <summary>
        /// 删除已提交成绩的方法(所有涉及删除成绩的地方都是调用此方法)
        /// </summary>
        /// <param name="finalExaminationID"></param>
        /// <param name="listUserID"></param>
        /// <returns></returns>
        public IQueryable<StudentScoreView> GetDeleteSubmittedScore(List<Guid> finalExaminationIDList, List<Guid?> listUserID)
        {
            var query = from score in submitedScoreRepository.GetList(x => finalExaminationIDList.Contains(x.FinalExaminationID ?? Guid.Empty))
                        where listUserID.Contains(score.UserID)
                        select new StudentScoreView
                        {
                            SubmitedScoreID = score.SubmitedScoreID,
                            UserID = score.UserID,
                            ExamsCategoryID = score.ExamsCategoryID,
                            StarttermID = score.StarttermID
                        };
            return query;
        }

        /// <summary>
        /// 删除最终成绩表中的学生成绩
        /// </summary>
        /// <param name="finalExaminationID"></param>
        /// <param name="listUserID"></param>
        /// <returns></returns>
        public IQueryable<StudentScoreView> GetDeleteFinallyScore(Guid finalExaminationID, List<Guid?> listUserID)
        {
            var query = from final in finalExaminationRepository.GetList(x => x.FinalExaminationID == finalExaminationID)
                        join stude in finalExaminationStudentRepository.GetList(x => listUserID.Contains(x.UserID)) on final.FinalExaminationID equals stude.FinalExaminationID
                        join score in finallyScoreRepository.Entities on new { final.ExamsCategoryID, final.CoursematerialID, stude.StarttermID, stude.UserID }
                        equals new { score.ExamsCategoryID, score.CoursematerialID, score.StarttermID, score.UserID }
                        select new StudentScoreView
                        {
                            FinallyScoreID = score.FinallyScoreID,
                            UserID = score.UserID,
                            ExamsCategoryID = score.ExamsCategoryID,
                            StarttermID = score.StarttermID
                        };
            return query;
        }

        public IQueryable<ER_FinalExamination> GetFinalExaminationByExaminationSuspension(Expression<Func<ER_ExaminationSuspension, bool>> examinationSuspensionExp,
            Expression<Func<ER_FinalExamination, bool>> finalExmainationExp)
        {
            var query = (from suspension in ExaminationSuspensionRepository.GetList(examinationSuspensionExp) 
                         join finalExam in finalExaminationRepository.Entities.Where(finalExmainationExp) 
                             on new { suspension.SchoolyearID, suspension.CoursematerialID, suspension.ExamsCategoryID } 
                             equals new { finalExam.SchoolyearID, finalExam.CoursematerialID, finalExam.ExamsCategoryID } 
                         from examStudent in finalExam.ER_FinalExaminationStudent 
                         where suspension.UserID == examStudent.UserID 
                         select finalExam);

            return query;
        }

        public IQueryable<ER_Score> GetScoreByExaminationSuspension(Expression<Func<ER_ExaminationSuspension, bool>> examinationSuspensionExp)
        {
            var query = (from suspension in ExaminationSuspensionRepository.GetList(examinationSuspensionExp)
                         join finalExam in finalExaminationRepository.Entities
                             on new { suspension.SchoolyearID, suspension.CoursematerialID, suspension.ExamsCategoryID }
                             equals new { finalExam.SchoolyearID, finalExam.CoursematerialID, finalExam.ExamsCategoryID }
                         from score in finalExam.ER_Score
                         where suspension.UserID == score.UserID
                         select score);

            return query;
        }

        public IQueryable<ER_Score> GetScoreByExaminationExemption(Expression<Func<ER_ExaminationExemption, bool>> examinationExemptsionExp)
        {
            var query = (from exemption in ExaminationExemptionRepository.GetList(examinationExemptsionExp)
                         join finalExam in finalExaminationRepository.Entities 
                             on new { exemption.SchoolyearID, exemption.CoursematerialID, exemption.ExamsCategoryID }
                             equals new { finalExam.SchoolyearID, finalExam.CoursematerialID, finalExam.ExamsCategoryID }
                         from score in finalExam.ER_Score
                         where exemption.UserID == score.UserID
                         select score);

            return query;
        }
        //获取来源于期末设定的免修申请信息
        //public IQueryable<FinalExaminationView> GetExaminationExemptionFromFinalExamination(Expression<Func<ER_ExaminationExemption, bool>> examinationExemptsionExp)
        //{
        //    var query = from exemption in ExaminationExemptionRepository.GetList(examinationExemptsionExp)
        //                join finalExam in finalExaminationRepository.Entities
        //                on new { exemption.SchoolyearID, exemption.CoursematerialID, exemption.ExamsCategoryID }
        //                equals new { finalExam.SchoolyearID, finalExam.CoursematerialID, finalExam.ExamsCategoryID }
        //                from finalStu in finalExam.ER_FinalExaminationStudent
        //                where exemption.UserID == finalStu.UserID
        //                select new FinalExaminationView
        //                {
        //                    CollegeID=finalStu.CF_Student.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CollegeID,
        //                    SchoolyearID=finalExam.SchoolyearID,
        //                    DepartmentID=finalExam.DepartmentID,
        //                    ClassName=finalExam.ClassName,
        //                    CoursematerialID=finalExam.CoursematerialID,
        //                    CourseTypeID=finalExam.CourseTypeID,
        //                    ExamsCategoryID=finalExam.ExamsCategoryID,
        //                    ExaminationModeID=finalExam.ExaminationModeID,
        //                    Credit=finalExam.Credit,
        //                    ExamsDatetime=finalExam.ExamsDatetime,
        //                    ResultTypeID=finalExam.ResultTypeID,
        //                    EntryDeadlineTime=finalExam.EntryDeadlineTime,
        //                    IsEntry=finalExam.IsEntry?? false,
        //                    ExaminationType=finalExam.ExaminationType,
        //                    FinalExaminationID = finalExam.FinalExaminationID,
        //                    UserID = exemption.UserID,
        //                    StarttermID = finalStu.StarttermID,
        //                    SchooolYearNumID=finalStu.SchoolyearNumID,
        //                    GradeYearID=finalExam.GradeYearID,
        //                    TotalHours=finalExam.TotalHours
        //                };
        //    return query;
        //}
        //获取来源于教学任务的免修申请信息
        public IQueryable<FinalExaminationView> GetExaminationExemptionFromEducationMission(Expression<Func<ER_ExaminationExemption, bool>> examinationExemptsionExp)
        {
            var query = from exemption in ExaminationExemptionRepository.GetList(examinationExemptsionExp)
                        join emc in EducationMissionClassRepository.Entities
                        on exemption.CoursematerialID equals emc.CoursematerialID
                        join em in EducationMissionRepository.Entities
                        on new { EducationMission =(Guid)emc.EducationMissionID, exemption.SchoolyearID } equals new { EducationMission=em.EducationMissionID, em.SchoolyearID }
                        from esc in emc.EM_EducationSchedulingClass
                        from esccs in esc.CF_Student
                        from emcs in emc.EM_EducationMissionClassSettings
                        where exemption.UserID == esccs.UserID
                        select new FinalExaminationView
                        {
                            CollegeID = esccs.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CollegeID,
                            SchoolyearID = em.SchoolyearID,
                            DepartmentID = em.DepartmentID,
                            ClassName = emc.Name,
                            CoursematerialID = emc.CoursematerialID,
                            CourseTypeID = emc.CourseTypeID,
                            ExaminationModeID = emc.ExaminationModeID,
                            Credit =  emc.EM_EducationMissionClassTeachingSetting.Credit,
                            ResultTypeID = emc.ResultTypeID,
                            UserID = exemption.UserID,
                            //StarttermID = finalStu.StarttermID,
                            //SchooolYearNumID = finalStu.SchoolyearNumID,
                            GradeYearID = esccs.CF_Classmajor.CF_Grademajor.GradeID,
                            TotalHours = emc.EM_EducationMissionClassTeachingSetting.TheoryCourse + emc.EM_EducationMissionClassTeachingSetting.Practicehours + emc.EM_EducationMissionClassTeachingSetting.Trialhours
                        };
            return query;
        } 

        public IQueryable<ER_Score> GetScoreByMisconduct(Expression<Func<ER_Misconduct, bool>> misconductExp)
        {
            var query = (from exemption in MisconductRepository.GetList(misconductExp)
                         join finalExam in finalExaminationRepository.Entities
                             on new { exemption.SchoolyearID, exemption.CoursematerialID, exemption.ExamsCategoryID }
                             equals new { finalExam.SchoolyearID, finalExam.CoursematerialID, finalExam.ExamsCategoryID }
                         from score in finalExam.ER_Score
                         where exemption.UserID == score.UserID
                         select score);

            return query;
        }
    }
}