JW/EMiS.DataLogic/StudentSystem/MyGrade/CourseGradeDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EMIS.Entities;
using EMIS.DataLogic.Repositories;
using EMIS.ViewModel.Students;
using EMIS.ViewModel.ScoreManage;
using EMIS.ViewModel;

namespace EMIS.DataLogic.StudentSystem.MyGrade
{
    public class CourseGradeDAL
    {
        public StudentRepository StudentRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public FinallyScoreRepository ScoreRepository { get; set; }
        public FinallyScoreDetailRepository FinallyScoreDetailRepository { get; set; }
        public SubmitedScoreDetailRepository ScoreDetailRepository { get; set; }
        public SubmitedScoreRepository SubmitedScoreRepository { get; set; }
        public DictionaryItemRepository DictionaryItemRepository { get; set; }
        public ClassmajorRepository ClassRepository { get; set; }
        public GrademajorRepository GradeRepository { get; set; }
        public FacultymajorRepository FacultyRepository { get; set; }
        public StudentProfileRepository ProfileRepository { get; set; }
        public StudentContactRepository ContactRepository { get; set; }
        public RecruitstudentsRepository RecruitsRepository { get; set; }
        public GraduationCourseTypeSettingRepository GraduationCourseTypeSettingRepository { get; set; }
        public ClubCourseRepository ClubCourseRepository { get; set; }
        public CourseTypeSelectCourseTypeRepository CourseTypeSelectCourseTypeRepository { get; set; }
        public FinallyScoreRepository finallyScoreRepository { get; set; }
        public GraduationCourseTypeSettingRepository graduationCourseTypeSettingRepository { get; set; }
        public CourseTypeSelectCourseTypeRepository courseTypeSelectCourseTypeRepository { get; set; }
        public ClubCourseRepository clubCourseRepository { get; set; }
        public ResultTypeDetailRepository ResultTypeDetailRepository { get; set; }
        public ExamsStateSettingRepository examsStateSettingRepository { get; set; }


        public virtual IList<CourseGradeView> GetStudentCourseGrade(Guid UserID)
        {
            List<int?> examsStateList = examsStateSettingRepository.GetList(x => x.IsNormal == true).Select(x => x.ExamsStateID).ToList();
            var query = from fina in ScoreRepository.Entities
                        join student in StudentRepository.Entities 
                        on fina.UserID equals student.UserID
                        join courseColour in CourseTypeSelectCourseTypeRepository.Entities 
                        on fina.CourseTypeID equals courseColour.CourseTypeID 
                        into colour
                        from cc in colour.DefaultIfEmpty()
                        join isClub in ClubCourseRepository.Entities 
                        on fina.EM_Coursematerial.CoursematerialID equals isClub.CoursematerialID 
                        into club
                        from clu in club.DefaultIfEmpty()
                        join ed in DictionaryItemRepository.GetList(x => x.DictionaryCode == "CF_ExamsState") 
                        on fina.ExamsStateID equals ed.Value
                        into ged
                        from f in ged.DefaultIfEmpty()
                        join d in DictionaryItemRepository.GetList(x => x.DictionaryCode == "CF_ResultType") 
                        on fina.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 &&
                                (
                                   (fina.TotalScore <= Result.MaxScore && Result.MinScore <= fina.TotalScore && (Result.MaxScoreOperator == "<=" && Result.MinScoreOperator == "<="))
                                || (fina.TotalScore <= Result.MaxScore && Result.MinScore < fina.TotalScore && (Result.MaxScoreOperator == "<=" && Result.MinScoreOperator == "<"))
                                || (fina.TotalScore < Result.MaxScore && Result.MinScore < fina.TotalScore && (Result.MaxScoreOperator == "<" && Result.MinScoreOperator == "<"))
                                || (fina.TotalScore < Result.MaxScore && Result.MinScore <= fina.TotalScore && (Result.MaxScoreOperator == "<" && Result.MinScoreOperator == "<="))
                                )
                                )
                                || (Result.ResultTypeDetailID == null))
                        &&( fina.UserID == UserID)
                        orderby new { fina.StarttermID, fina.CourseTypeID, fina.EM_Coursematerial.CourseName }
                        select new CourseGradeView()
                        {
                            Club = clu.ClubCourseID,
                            CourseTypeColour=cc.CourseTypeColour,
                            UserID = fina.UserID,
                            CourseName = fina.EM_Coursematerial.CourseName,
                            TotalScore = fina.TotalScore,

                            //暂时按此种逻辑处理。。后续优化（廖兵良）
                            //已经优化--20180517李晓林
                            TotalScoreStr = examsStateList.Any(w => w.Value == fina.ExamsStateID) ? (Result.Name ?? ((double)Math.Round(fina.TotalScore.Value, 1)).ToString()) : f.Name,
                            ScoreCredit = fina.ScoreCredit,
                            CoursematerialID = fina.CoursematerialID,
                            SchoolyearNumID = fina.SchoolyearNumID,
                            SchoolyearID = fina.SchoolyearID,
                            PlanningGraduateDate = student.PlanningGraduateDate,
                            StarttermID = fina.StarttermID
                        };

            return query.ToList();
        }

        /// <summary>
        /// 获取已提交成绩
        /// </summary>
        /// <param name="CoursematerialID"></param>
        /// <param name="SchoolyearID"></param>
        /// <param name="UserID"></param>
        public virtual IList<SubmitedScoreView> GetSubmitedScoreList(Guid? CoursematerialID, int? StartTermID, Guid? UserID)
        {
            int examsState = (int)CF_ExamsState.NormalExams;
            var query = from submited in SubmitedScoreRepository.Entities
                        join coursetype in DictionaryItemRepository.Entities
                        on new { CourseTypeID = submited.CourseTypeID, DictionaryCode = typeof(CF_CourseType).Name }
                        equals new { CourseTypeID = coursetype.Value, coursetype.DictionaryCode }
                        into courseTypedata
                        from courseTypetb in courseTypedata.DefaultIfEmpty()
                        join examscategory in DictionaryItemRepository.Entities
                        on new { CourseTypeID = submited.ExamsCategoryID, DictionaryCode = typeof(CF_ExamsCategory).Name }
                        equals new { CourseTypeID = examscategory.Value, examscategory.DictionaryCode }
                        into examsCategorydata
                        from examsCategorytb in examsCategorydata.DefaultIfEmpty()
                        join examsstate in DictionaryItemRepository.Entities
                        on new { CourseTypeID = submited.ExamsStateID, DictionaryCode = typeof(CF_ExamsState).Name }
                        equals new { CourseTypeID = examsstate.Value, examsstate.DictionaryCode }
                         into examsStatedata
                        from examsStatetb in examsStatedata.DefaultIfEmpty()
                        join opera in UserRepository.Entities on submited.CreatorUserID equals opera.UserID
                        join d in DictionaryItemRepository.GetList(x => x.DictionaryCode == "CF_ResultType") on submited.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 &&
                                (
                                (submited.TotalScore <= Result.MaxScore && Result.MinScore <= submited.TotalScore && (Result.MaxScoreOperator == "<=" && Result.MinScoreOperator == "<="))
                                || (submited.TotalScore <= Result.MaxScore && Result.MinScore < submited.TotalScore && (Result.MaxScoreOperator == "<=" && Result.MinScoreOperator == "<"))
                                || (submited.TotalScore < Result.MaxScore && Result.MinScore < submited.TotalScore && (Result.MaxScoreOperator == "<" && Result.MinScoreOperator == "<"))
                                || (submited.TotalScore < Result.MaxScore && Result.MinScore <= submited.TotalScore && (Result.MaxScoreOperator == "<" && Result.MinScoreOperator == "<="))
                                )
                                )
                                || (Result.ResultTypeDetailID == null)
                              )
                               &&submited.CoursematerialID == CoursematerialID && submited.StarttermID == StartTermID && submited.UserID == UserID
                        select new SubmitedScoreView
                        {
                            SubmitedScoreID = submited.SubmitedScoreID,
                            UserID = submited.UserID,
                            LoginID = submited.CF_Student.Sys_User.LoginID,
                            UserName = submited.CF_Student.Sys_User.Name,
                            SchoolyearID = submited.SchoolyearID,
                            SchoolyearCode = submited.CF_Schoolyear.Code,
                            Schoolyear = submited.CF_Schoolyear.Years,
                            CourseName = submited.EM_Coursematerial.CourseName,
                            CourseCode = submited.EM_Coursematerial.CourseCode,
                            TotalScore = submited.TotalScore,

                            //暂时按此种逻辑处理。。后续优化（廖兵良）
                            TotalScoreStr = (submited.ExamsStateID == examsState || submited.ExamsStateID == 10) ? (Result.Name ?? ((double)Math.Round(submited.TotalScore.Value, 1)).ToString()) : examsStatetb.Name,
                            ScoreCredit = submited.ScoreCredit,
                            CoursetypeName = courseTypetb.Name,
                            CreateTime = submited.CreateTime,
                            OperaUser = opera.Name,
                            ExamsCategoryName = examsCategorytb.Name,
                            ExamsStateName = examsStatetb.Name,
                            CoursematerialID = submited.CoursematerialID,
                            StarttermID = submited.StarttermID,
                            Remarks = submited.Remark,
                            StudentScoreRemark = submited.Remark,
                        };

            return query.ToList();
        }

        public IList<ER_SubmitedScoreDetail> GetScoreDetail(Guid SubmitedScoreID)
        {
            var query = ScoreDetailRepository.GetList(q => q.SubmitedScoreID == SubmitedScoreID);
            return query.ToList();
        }

        public StudentGradView GetStudentInfo(Guid StudentID)
        {
            var userScore = from score in ScoreRepository.Entities
                            //join detail in FinallyScoreDetailRepository.Entities on score.FinallyScoreID equals detail.FinallyScoreID
                            select new
                            {
                                score.UserID,
                                score.TotalScore
                            };
            var query = from student in StudentRepository.Entities
                        join profile in ProfileRepository.Entities on student.UserID equals profile.UserID
                        join contact in ContactRepository.Entities on profile.UserID equals contact.UserID
                        join recruits in RecruitsRepository.Entities on contact.UserID equals recruits.UserID
                        join cla in ClassRepository.Entities on student.ClassmajorID equals cla.ClassmajorID
                        join grade in GradeRepository.Entities on cla.GrademajorID equals grade.GrademajorID
                        join faculty in FacultyRepository.Entities on grade.FacultymajorID equals faculty.FacultymajorID
                        where student.UserID == StudentID
                        select new StudentGradView()
                        {
                            UserID = student.UserID,
                            LoginID = student.Sys_User.LoginID,
                            UserName = student.Sys_User.Name,
                            FacultyName = faculty.Name,
                            GradeName = grade.Name,
                            GradeCode = grade.Code,
                            ClassName = cla.Name,
                            PlanningGraduateDate = student.PlanningGraduateDate,
                            Menshu = student.ER_FinallyScore.Count,
                            AverageGrade = userScore.Where(q => q.UserID == student.UserID).Average(q => q.TotalScore)
                        };
            return query.SingleOrDefault();
        }

        public StudentGradView GetStudentPersonalScore(Guid? userID)
        {
            var userScore = from score in ScoreRepository.Entities
                            select new
                            {
                                score.UserID,
                                score.TotalScore,
                                score.ScoreCredit
                            };
            var query = from student in StudentRepository.Entities.Where(x => x.UserID == userID)
                        select new StudentGradView
                        {
                            UserID = student.UserID,
                            LoginID = student.Sys_User.LoginID,
                            UserName = student.Sys_User.Name,
                            FacultyName = student.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CF_College.Name,
                            ClassName = student.CF_Classmajor.Name,
                            PlanningGraduateDate = student.PlanningGraduateDate,
                            Menshu = student.ER_FinallyScore.Count,
                            AverageGrade = userScore.Where(q => q.UserID == student.UserID).Average(q => q.TotalScore),
                            TotalScore = userScore.Where(q => q.UserID == student.UserID).Sum(q => q.ScoreCredit)
                        };
            return query.FirstOrDefault();
        }

        public StudentGradView GetStudentPersonalScoreGT(Guid? userID)
        {

            //必修学分
            var RequiredCredit = from q in finallyScoreRepository.GetList(x => x.UserID == userID)
                                 join a in courseTypeSelectCourseTypeRepository.Entities
                                 on q.CourseTypeID equals a.CourseTypeID
                                 where a.IsRequired == true
                                 group q by q.UserID
                                     into g
                                     select new
                                     {
                                         UserID = g.Key,
                                         RequiredCredit = g.Sum(x => x.ScoreCredit ?? 0),
                                     };
            //限选学分
            var OptionalCourseCredit = from q in finallyScoreRepository.GetList(x => x.UserID == userID)
                                       join a in courseTypeSelectCourseTypeRepository.Entities.Where(x => x.IsOptionalCourse == true)
                                       on q.CourseTypeID equals a.CourseTypeID
                                       where a.IsOptionalCourse == true
                                       group q by q.UserID
                                           into g
                                           select new
                                           {
                                               UserID = g.Key,
                                               OptionalCourseCredit = g.Sum(x => x.ScoreCredit ?? 0),
                                           };
            //任选学分
            var FreeSelectionCourseCredit = from q in finallyScoreRepository.GetList(x => x.UserID == userID)
                                            join a in courseTypeSelectCourseTypeRepository.Entities.Where(x => x.IsFreeSelectionCourse == true)
                                            on q.CourseTypeID equals a.CourseTypeID
                                            where a.IsFreeSelectionCourse == true
                                            group q by q.UserID
                                                into g
                                                select new
                                                {
                                                    UserID = g.Key,
                                                    FreeSelectionCourseCredit = g.Sum(x => x.ScoreCredit ?? 0),
                                                };
            //通识教育
            var GeneralEducationCredit = from final in finallyScoreRepository.GetList(x => x.UserID == userID)
                                         join gcts in graduationCourseTypeSettingRepository.Entities
                                         on final.CourseTypeID equals gcts.CourseTypeID
                                         into a
                                         from j in a.DefaultIfEmpty()
                                         where j.IsGeneralEducation == true
                                         group final by final.UserID
                                             into g
                                             select new
                                             {
                                                 UserID = g.Key,
                                                 GeneralEducationCredit = g.Sum(x => x.ScoreCredit ?? 0),
                                             };
            //方向选修
            var ElectiveCredit = from final in finallyScoreRepository.GetList(x => x.UserID == userID)
                                 join gcts in graduationCourseTypeSettingRepository.Entities
                                 on final.CourseTypeID equals gcts.CourseTypeID
                                 into a
                                 from j in a.DefaultIfEmpty()
                                 where j.IsElective == true
                                 group final by final.UserID
                                     into g
                                     select new
                                     {
                                         UserID = g.Key,
                                         ElectiveCredit = g.Sum(x => x.ScoreCredit ?? 0),
                                     };
            //公共选修
            var ElectivePublicCredit = from final in finallyScoreRepository.GetList(x => x.UserID == userID)
                                       join gcts in graduationCourseTypeSettingRepository.Entities
                                       on final.CourseTypeID equals gcts.CourseTypeID
                                       into a
                                       from j in a.DefaultIfEmpty()
                                       where j.IsElectivePublic == true
                                       group final by final.UserID
                                           into g
                                           select new
                                           {
                                               UserID = g.Key,
                                               ElectivePublicCredit = g.Sum(x => x.ScoreCredit ?? 0),
                                           };
            //实践环节
            var PractiseCredit = from final in finallyScoreRepository.GetList(x => x.UserID == userID)
                                 join gcts in graduationCourseTypeSettingRepository.Entities
                                 on final.CourseTypeID equals gcts.CourseTypeID
                                 into a
                                 from j in a.DefaultIfEmpty()
                                 where j.IsPractise == true
                                 group final by final.UserID
                                     into g
                                     select new
                                     {
                                         UserID = g.Key,
                                         PractiseCredit = g.Sum(x => x.ScoreCredit ?? 0),
                                     };

            //实践环节并且只包含俱乐部
            var PractiseWithClub = from final in finallyScoreRepository.GetList(x => x.UserID == userID)
                                    join gcts in graduationCourseTypeSettingRepository.Entities
                                    on final.CourseTypeID equals gcts.CourseTypeID
                                    into a
                                    from j in a.DefaultIfEmpty()
                                    join cc in clubCourseRepository.Entities
                                    on final.CoursematerialID equals cc.CoursematerialID
                                    into b
                                    from k in b.DefaultIfEmpty()
                                    where j.IsPractise == true && k.ClubCourseID != null//实践环节并且只包含俱乐部
                                    group final by final.UserID
                                        into g
                                        select new
                                        {
                                            UserID = g.Key,
                                            PractiseWithClub = g.Sum(x => x.ScoreCredit ?? 0),
                                        };
            //实践环节并且不包含俱乐部
            var PractiseNotCredit = from final in finallyScoreRepository.GetList(x => x.UserID == userID)
                                    join gcts in graduationCourseTypeSettingRepository.Entities
                                    on final.CourseTypeID equals gcts.CourseTypeID
                                    into a
                                    from j in a.DefaultIfEmpty()
                                    join cc in clubCourseRepository.Entities
                                    on final.CoursematerialID equals cc.CoursematerialID
                                    into b
                                    from k in b.DefaultIfEmpty()
                                    where j.IsPractise == true && k.ClubCourseID == null//实践环节并且不包含俱乐部
                                    group final by final.UserID
                                        into g
                                        select new
                                        {
                                            UserID = g.Key,
                                            PractiseNotCredit = g.Sum(x => x.ScoreCredit ?? 0),
                                        };
            //校本课
            var Schoolbased = from final in finallyScoreRepository.GetList(x => x.UserID == userID)
                              join gcts in graduationCourseTypeSettingRepository.Entities
                              on final.CourseTypeID equals gcts.CourseTypeID
                              into a
                              from j in a.DefaultIfEmpty()
                              where j.IsSchoolbasedCurriculum == true
                              group final by final.UserID
                                  into g
                                  select new
                                  {
                                      UserID = g.Key,
                                      Schoolbased = g.Sum(x => x.ScoreCredit ?? 0),
                                  };
            //校本课程（只包含俱乐部）
            var SchoolbasedWithClub = from final in finallyScoreRepository.GetList(x => x.UserID == userID)
                                     join gcts in graduationCourseTypeSettingRepository.Entities
                                     on final.CourseTypeID equals gcts.CourseTypeID
                                     into a
                                     from j in a.DefaultIfEmpty()
                                     join cc in clubCourseRepository.Entities
                                     on final.CoursematerialID equals cc.CoursematerialID
                                     into b
                                     from k in b.DefaultIfEmpty()
                                     where j.IsSchoolbasedCurriculum == true && k.ClubCourseID != null
                                     group final by final.UserID
                                         into g
                                         select new
                                         {
                                             UserID = g.Key,
                                             Schoolbased = g.Sum(x => x.ScoreCredit ?? 0),
                                         };
            //校本课程（不包含俱乐部）
            var SchoolbasedNotClub = from final in finallyScoreRepository.GetList(x => x.UserID == userID)
                                     join gcts in graduationCourseTypeSettingRepository.Entities
                                     on final.CourseTypeID equals gcts.CourseTypeID
                                     into a
                                     from j in a.DefaultIfEmpty()
                                     join cc in clubCourseRepository.Entities
                                     on final.CoursematerialID equals cc.CoursematerialID
                                     into b
                                     from k in b.DefaultIfEmpty()
                                     where j.IsSchoolbasedCurriculum == true && k.ClubCourseID == null
                                     group final by final.UserID
                                         into g
                                         select new
                                         {
                                             UserID = g.Key,
                                             Schoolbased = g.Sum(x => x.ScoreCredit ?? 0),
                                         };
            var userScore = from score in ScoreRepository.Entities
                            select new
                            {
                                score.UserID,
                                score.TotalScore,
                                score.ScoreCredit
                            };
            var query = from student in StudentRepository.Entities.Where(x => x.UserID == userID)
                        select new StudentGradView
                        {
                            UserID = student.UserID,
                            LoginID = student.Sys_User.LoginID,
                            UserName = student.Sys_User.Name,
                            FacultyName = student.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CF_College.Name,
                            ClassName = student.CF_Classmajor.Name,
                            PlanningGraduateDate = student.PlanningGraduateDate,
                            Menshu = student.ER_FinallyScore.Count,
                            AverageGrade = userScore.Where(q => q.UserID == student.UserID).Average(q => q.TotalScore),
                            //TotalScore = userScore.Where(q => q.UserID == student.UserID).Sum(q => q.ScoreCredit),
                            Required = RequiredCredit.Select(x => x.RequiredCredit).FirstOrDefault(),
                            OptionalCourseCredit = OptionalCourseCredit.Select(x => x.OptionalCourseCredit).FirstOrDefault(),
                            FreeSelectionCourseCredit = FreeSelectionCourseCredit.Select(x => x.FreeSelectionCourseCredit).FirstOrDefault(),
                            ElectiveCredit = ElectiveCredit.Select(x => x.ElectiveCredit).FirstOrDefault(),
                            ElectivePublicCredit = ElectivePublicCredit.Select(x => x.ElectivePublicCredit).FirstOrDefault(),
                            PractiseNotCredit = PractiseNotCredit.Select(x => x.PractiseNotCredit).FirstOrDefault(),
                            PractiseWithClub = PractiseWithClub.Select(x => x.PractiseWithClub).FirstOrDefault(),
                            PractiseCredit = PractiseCredit.Select(x => x.PractiseCredit).FirstOrDefault(),
                            SchoolbasedCredit = Schoolbased.Select(x => x.Schoolbased).FirstOrDefault(),
                            SchoolbasedWithClub = SchoolbasedWithClub.Select(x => x.Schoolbased).FirstOrDefault(),
                            SchoolbasedNotClub = SchoolbasedNotClub.Select(x => x.Schoolbased).FirstOrDefault()
                        };

            return query.FirstOrDefault();
        }

        public IQueryable<SubmitedScoreDetailView> GetSubmitedScoreViewQueryable()
        {
            var query = from a in SubmitedScoreRepository.Entities
                        select new SubmitedScoreDetailView
                        {
                            SubmitedScoreID = a.SubmitedScoreID,
                            SchoolyearID = a.SchoolyearID,
                            SchoolyearNumID = a.SchoolyearNumID,
                            DepartmentID = a.DepartmentID,
                            ClassName = a.ClassName,
                            CoursematerialID = a.CoursematerialID,
                            CourseTypeID = a.CourseTypeID,
                            ExamsCategoryID = a.ExamsCategoryID,
                            ExaminationModeID = a.ExaminationModeID,
                            Credit = a.Credit,
                            ExamsDatetime = a.ExamsDatetime,
                            CreatorUserID = a.CreatorUserID,
                            EntryDeadlineTime = a.EntryDeadlineTime,
                            ExaminationTypeID = a.ExaminationType,
                            StarttermID = a.StarttermID,
                            ExamsStateID = a.ExamsStateID,
                            TotalScore = a.TotalScore,
                            ScoreCredit = a.ScoreCredit,
                            GradePoint = a.GradePoint,
                            ResultTypeID=a.ResultTypeID,
                            UserID = a.UserID,
                            Remark = a.Remark,
                            CreateUserID = a.CreateUserID,
                            CreateTime = a.CreateTime
                        };
            return query;
        }
    }
}