JW/EMiS.DataLogic/ScoreManage/StudentScoreDAL.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 Bowin.Common.Linq;
using System.Linq.Expressions;
using System.Data.Entity;
using EMIS.ViewModel;
using EMIS.DataLogic.Common.Cultureplan;
using EMIS.ViewModel.Cultureplan;

namespace EMIS.DataLogic.ScoreManage
{
    public class StudentScoreDAL
    {
        public SubmitedScoreRepository submitedScoreRepository { get; set; }
        public SubmitedScoreDetailRepository SubmitedScoreDetailRepository { get; set; }
        public ExaminationExemptionRepository ExaminationExemptionRepository { get; set; }
        public MisconductRepository MisconductRepository { get; set; }
        public FinallyScoreRepository FinallyScoreRepository { get; set; }
        public FinallyScoreDetailRepository FinallyScoreDetailRepository { get; set; }
        public ClassmajorRepository classmajorRepository { get; set; }
        public StudentRepository studentRepository { get; set; }
        public UserRepository userRepository { get; set; }
        public DictionaryItemRepository DictionaryItemRepository { get; set; }
        public ResultTypeDetailRepository ResultTypeDetailRepository { get; set; }
        public ExamsStateSettingRepository examsStateSettingRepository { get; set; }
        public StaffRepository staffRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public DepartmentRepository DepartmentRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public CoursematerialRepository CoursematerialRepository { get; set; }

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

        /// <summary>
        /// 已提交成绩
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public virtual IQueryable<StudentScoreView> GetStudentScoreViewQueryable(Expression<Func<ER_SubmitedScore, bool>> exp)
        {
            List<int?> examsStateList = examsStateSettingRepository.GetList(x => x.IsNormal == true).Select(x => x.ExamsStateID).ToList();
            var query = from x in submitedScoreRepository.GetList(exp)
                        join u in userRepository.Entities on x.CreatorUserID equals u.UserID
                        into user from a in user.DefaultIfEmpty()
                        join ed in DictionaryItemRepository.GetList(x => x.DictionaryCode == "CF_ExamsState") on x.ExamsStateID equals ed.Value
                        into ged from f in ged.DefaultIfEmpty()
                        join d in DictionaryItemRepository.GetList(x => x.DictionaryCode == "CF_ResultType") on x.ResultTypeID equals d.Value
                        join rd in ResultTypeDetailRepository.GetList(x => true) on d.Value equals rd.ResultTypeID
                        into ResultTypeDetail from Result in ResultTypeDetail.DefaultIfEmpty()
                        where (Result.ResultTypeDetailID != null &&
                        ((x.TotalScore <= Result.MaxScore && Result.MinScore <= x.TotalScore && (Result.MaxScoreOperator == "<=" && Result.MinScoreOperator == "<="))
                        || (x.TotalScore <= Result.MaxScore && Result.MinScore < x.TotalScore && (Result.MaxScoreOperator == "<=" && Result.MinScoreOperator == "<"))
                        || (x.TotalScore < Result.MaxScore && Result.MinScore < x.TotalScore && (Result.MaxScoreOperator == "<" && Result.MinScoreOperator == "<"))
                        || (x.TotalScore < Result.MaxScore && Result.MinScore <= x.TotalScore && (Result.MaxScoreOperator == "<" && Result.MinScoreOperator == "<="))
                        ))
                        || (Result.ResultTypeDetailID == null)
                        select new StudentScoreView
                        {
                            SubmitedScoreID = x.SubmitedScoreID,
                            SchoolyearID = x.SchoolyearID,
                            SchoolyearCode = x.CF_Schoolyear.Code,
                            SchoolyearNumID = x.SchoolyearNumID,
                            ClassName = x.ClassName,
                            GrademajorID = x.CF_Student.CF_Classmajor.CF_Grademajor.GrademajorID,
                            GrademajorCode = x.CF_Student.CF_Classmajor.CF_Grademajor.Code,
                            GrademajorName = x.CF_Student.CF_Classmajor.CF_Grademajor.Name,
                            ClassmajorID = x.CF_Student.CF_Classmajor.ClassmajorID,
                            ClassmajorCode = x.CF_Student.CF_Classmajor.No,
                            ClassmajorName = x.CF_Student.CF_Classmajor.Name,
                            CollegeID = x.CF_Student.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CollegeID,
                            CollegeName = x.CF_Student.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CF_College.Name,
                            DepartmentID = x.DepartmentID,
                            DepartmentName = x.CF_Department.Name,
                            CoursematerialID = x.EM_Coursematerial.CoursematerialID,
                            CourseCode = x.EM_Coursematerial.CourseCode,
                            CourseName = x.EM_Coursematerial.CourseName,
                            CourseTypeID = x.CourseTypeID,
                            ExamsCategoryID = x.ExamsCategoryID,
                            ExaminationModeID = x.ExaminationModeID,
                            Credit = x.Credit ?? 0,
                            ExamsDatetime = x.ExamsDatetime,
                            ResultTypeID = x.ResultTypeID,
                            CreatorUserID = x.CreatorUserID,
                            CreatorUserCode = a.LoginID,
                            CreatorUserName = a.Name,
                            EntryDeadlineTime = x.EntryDeadlineTime,
                            ExaminationType = x.ExaminationType,
                            StarttermID = x.StarttermID,
                            UserID = x.UserID,
                            LoginID = x.CF_Student.Sys_User.LoginID,
                            UserName = x.CF_Student.Sys_User.Name,
                            ExamsStateID = x.ExamsStateID,
                            TotalScore = x.TotalScore,

                            //暂时按此种逻辑处理。。后续优化（廖兵良）
                            //已经优化--20180517李晓林
                            TotalScoreStr = examsStateList.Any(w => w.Value == x.ExamsStateID) ? (Result.Name ?? ((double)Math.Round(x.TotalScore.Value, 1)).ToString()) : f.Name,
                            ScoreCredit = x.ScoreCredit ?? 0,
                            GradePoint = x.GradePoint,
                            Remarks = x.Remark,
                            StudentScoreRemark = x.Remark,
                            CreateTime = x.CreateTime,
                            CreateUserID = x.CreateUserID,
                            TotalHours = x.TotalHours,
                            Pingshi = x.ER_SubmitedScoreDetail.Where(q => q.ScoreTypeID == (int)EMIS.ViewModel.CF_ScoreType.Peacetime).FirstOrDefault().Score == null ? (decimal?)null : Math.Round(x.ER_SubmitedScoreDetail.Where(q => q.ScoreTypeID == (int)EMIS.ViewModel.CF_ScoreType.Peacetime).FirstOrDefault().Score ?? 0, 0),
                            Jishu = x.ER_SubmitedScoreDetail.Where(q => q.ScoreTypeID == (int)EMIS.ViewModel.CF_ScoreType.Technique).FirstOrDefault().Score == null ? (decimal?)null : Math.Round(x.ER_SubmitedScoreDetail.Where(q => q.ScoreTypeID == (int)EMIS.ViewModel.CF_ScoreType.Technique).FirstOrDefault().Score ?? 0, 0),
                            Lilun = x.ER_SubmitedScoreDetail.Where(q => q.ScoreTypeID == (int)EMIS.ViewModel.CF_ScoreType.Theoretical).FirstOrDefault().Score == null ? (decimal?)null : Math.Round(x.ER_SubmitedScoreDetail.Where(q => q.ScoreTypeID == (int)EMIS.ViewModel.CF_ScoreType.Theoretical).FirstOrDefault().Score ?? 0, 0),
                            HandleModeID = x.HandleModeID,
                        };
            return query;
        }

        public virtual IQueryable<ER_SubmitedScore> GetLastSubmittedScore(Expression<Func<ER_SubmitedScore, bool>> exp)
        {
            var maxSubmitedScoreList = (
                                        from ss in submitedScoreRepository.Entities
                                        join mss in
                                            (
                                             from ss in submitedScoreRepository.Entities
                                             join mss in
                                                 (
                                                  from nss in submitedScoreRepository.GetList(exp)
                                                  join ssl in submitedScoreRepository.Entities
                                                     on new { nss.CoursematerialID, nss.UserID, nss.StarttermID }
                                                     equals new { ssl.CoursematerialID, ssl.UserID, ssl.StarttermID }
                                                  group ssl by new { ssl.UserID, ssl.CoursematerialID, ssl.StarttermID }
                                                      into g
                                                      select new { g.Key.UserID, g.Key.CoursematerialID, g.Key.StarttermID, MaxTotalScore = g.Max(x => x.TotalScore) })
                                                 on new { ss.UserID, ss.CoursematerialID, ss.StarttermID, ss.TotalScore }
                                                 equals new { mss.UserID, mss.CoursematerialID, mss.StarttermID, TotalScore = mss.MaxTotalScore }
                                             group ss by new { ss.UserID, ss.CoursematerialID, ss.StarttermID, ss.TotalScore } into g
                                             select new { g.Key.UserID, g.Key.CoursematerialID, g.Key.StarttermID, g.Key.TotalScore, CreateTime = g.Max(x => x.CreateTime) }
                                             )
                                            on new { ss.UserID, ss.CoursematerialID, ss.StarttermID, ss.TotalScore, ss.CreateTime }
                                            equals new { mss.UserID, mss.CoursematerialID, mss.StarttermID, TotalScore = mss.TotalScore, mss.CreateTime }
                                        group ss by new { ss.UserID, ss.CoursematerialID, ss.StarttermID } into g
                                        select new { g.Key.UserID, g.Key.CoursematerialID, g.Key.StarttermID, SubmitedScoreID = g.Max(x => x.SubmitedScoreID.ToString()) }
                                        );

            var lastSubmittedScoreList = (from ssl in submitedScoreRepository.Entities
                                          join mss in maxSubmitedScoreList
                                              on ssl.SubmitedScoreID.ToString() equals mss.SubmitedScoreID
                                          select ssl);

            return lastSubmittedScoreList;
        }

        public virtual IQueryable<ER_SubmitedScore> GetLastSubmittedScoreBeforDelete(Expression<Func<ER_SubmitedScore, bool>> exp)
        {
            var maxSubmitedScoreList = (
                                        from ss in submitedScoreRepository.Entities
                                        join nss in submitedScoreRepository.GetList(exp)
                                            on new { ss.UserID, ss.CoursematerialID, ss.StarttermID }
                                            equals new { nss.UserID, nss.CoursematerialID, nss.StarttermID }
                                        join mss in
                                            (from ss in submitedScoreRepository.Entities
                                             join nss in submitedScoreRepository.GetList(exp)
                                                 on new { ss.UserID, ss.CoursematerialID, ss.StarttermID }
                                                 equals new { nss.UserID, nss.CoursematerialID, nss.StarttermID }
                                             join mss in
                                                 (from ssl in submitedScoreRepository.Entities
                                                  join nss in submitedScoreRepository.GetList(exp)
                                                     on new { ssl.UserID, ssl.CoursematerialID, ssl.StarttermID }
                                                     equals new { nss.UserID, nss.CoursematerialID, nss.StarttermID }
                                                  where ssl.SubmitedScoreID != nss.SubmitedScoreID
                                                  group ssl by new { ssl.UserID, ssl.CoursematerialID, ssl.StarttermID }
                                                      into g
                                                      select new { g.Key.UserID, g.Key.CoursematerialID, g.Key.StarttermID, MaxTotalScore = g.Max(x => x.TotalScore) })
                                                 on new { ss.UserID, ss.CoursematerialID, ss.StarttermID, ss.TotalScore }
                                                 equals new { mss.UserID, mss.CoursematerialID, mss.StarttermID, TotalScore = mss.MaxTotalScore }
                                             where ss.SubmitedScoreID != nss.SubmitedScoreID
                                             group ss by new { ss.UserID, ss.CoursematerialID, ss.StarttermID, ss.TotalScore } into g
                                             select new { g.Key.UserID, g.Key.CoursematerialID, g.Key.StarttermID, g.Key.TotalScore, CreateTime = g.Max(x => x.CreateTime) })
                                            on new { ss.UserID, ss.CoursematerialID, ss.StarttermID, ss.TotalScore, ss.CreateTime }
                                            equals new { mss.UserID, mss.CoursematerialID, mss.StarttermID, mss.TotalScore, mss.CreateTime }
                                        where ss.SubmitedScoreID != nss.SubmitedScoreID
                                        group ss by new { ss.UserID, ss.CoursematerialID, ss.StarttermID, ss.TotalScore } into g
                                        select g.Max(x => x.SubmitedScoreID.ToString())
                                        );

            var lastSubmittedScoreList = (from ssl in submitedScoreRepository.Entities
                                          join mss in maxSubmitedScoreList
                                              on ssl.SubmitedScoreID.ToString() equals mss
                                          select ssl);

            return lastSubmittedScoreList;
        }

        public IQueryable<ER_FinallyScore> GetFinallyScoreBySubmittedScore(Expression<Func<ER_SubmitedScore, bool>> exp)
        {
            var lastFinallyScoreList = (from ssl in submitedScoreRepository.GetList(exp)
                                        .Select(x => new { x.UserID, x.CoursematerialID, x.StarttermID }).Distinct()
                                        join fs in FinallyScoreRepository.Entities
                                            on new { ssl.UserID, ssl.CoursematerialID, ssl.StarttermID }
                                            equals new { fs.UserID, fs.CoursematerialID, fs.StarttermID }
                                        select fs).Include(x => x.ER_FinallyScoreDetail);

            return lastFinallyScoreList;
        }

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

            return query;
        }

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

            return query;
        }

        public IQueryable<FinallyScoreView> GetFinallyScoreViewQueryable(Expression<Func<ER_FinallyScore, bool>> exp)
        {
            var query = (from score in FinallyScoreRepository.GetList(exp)
                         from student in studentRepository.Entities.Where(x => x.UserID == score.UserID)
                         from classmajor in classmajorRepository.Entities.Where(x => x.ClassmajorID == student.ClassmajorID)
                         from grade in GrademajorRepository.Entities.Where(x => x.GrademajorID == classmajor.GrademajorID)
                         from faculty in FacultymajorRepository.Entities.Where(x => x.FacultymajorID == grade.FacultymajorID)
                         from college in CollegeRepository.Entities.Where(x => x.CollegeID == faculty.CollegeID)
                         from schoolyear in SchoolyearRepository.Entities.Where(x => x.SchoolyearID == score.SchoolyearID)
                         from department in DepartmentRepository.Entities.Where(x => x.DepartmentID == score.DepartmentID)
                         from sourcecourse in CoursematerialRepository.Entities.Where(x => x.CoursematerialID == score.CoursematerialID)
                         from user in userRepository.Entities.Where(x => x.UserID == score.UserID)
                         from creatoruser in userRepository.Entities.Where(x => x.UserID == score.CreatorUserID).DefaultIfEmpty()
                         from createuser in userRepository.Entities.Where(x => x.UserID == score.CreateUserID).DefaultIfEmpty()
                         where score.TotalScore >= 60
                         select new FinallyScoreView
                         {
                             FinallyScoreID = score.FinallyScoreID,
                             SchoolyearID = score.SchoolyearID,
                             SchoolyearCode = schoolyear.Code,
                             SchoolyearNumID = score.SchoolyearNumID,
                             ClassName = classmajor.Name,
                             CollegeID = faculty.CollegeID,
                             CollegeName = college.Name,
                             DepartmentID = score.DepartmentID,
                             DepartmentName = department.Name,
                             CoursematerialID = score.CoursematerialID,
                             CourseCode = sourcecourse.CourseCode,
                             CourseName = sourcecourse.CourseName,
                             CourseTypeID = score.CourseTypeID,
                             ExamsCategoryID = score.ExamsCategoryID,
                             ExaminationModeID = score.ExaminationModeID,
                             Credit = score.Credit,
                             TotalHours = score.TotalHours,
                             ExamsDatetime = score.ExamsDatetime,
                             ResultTypeID = score.ResultTypeID,
                             CreatorUserID = score.CreatorUserID,
                             CreatorUserName = creatoruser.Name,
                             EntryDeadlineTime = score.EntryDeadlineTime,
                             ExaminationType = score.ExaminationType,
                             StarttermID = score.StarttermID,
                             UserID = score.UserID,
                             LoginID = user.LoginID,
                             UserName = user.Name,
                             ExamsStateID = score.ExamsStateID,
                             TotalScore = score.TotalScore,
                             ScoreCredit = score.ScoreCredit,
                             GradePoint = score.GradePoint,
                             Remarks = score.Remark,
                             CreateTime = score.CreateTime,
                             CreateUserID = score.CreateUserID
                         });

            return query;
        }

        public IQueryable<CoursematerialView> GetCoursematerialViewQueryableByFinallyScore(Expression<Func<ER_FinallyScore, bool>> exp)
        {
            var coursematerialQueryable = this.CoursematerialDAL.Value.GetCoursematerialViewQueryable(x => true);

            var query = (from score in FinallyScoreRepository.GetList(exp)
                         from course in coursematerialQueryable.Where(x => x.CoursematerialID == score.CoursematerialID)
                         where score.TotalScore >= 60
                         select course).Distinct();

            return query;
        }
    }
}