JW/EMiS.DataLogic/DegreeManage/DegreeSetting/DegreeConditionDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using EMIS.DataLogic.Repositories;
using EMIS.Entities;
using EMIS.ViewModel;
using EMIS.ViewModel.DegreeManage.DegreeSetting;
using EMIS.ViewModel.EducationManage;

namespace EMIS.DataLogic.DegreeManage.DegreeSetting
{
    public class DegreeConditionDAL
    {
        public DegreeConditionRepository DegreeConditionRepository { get; set; }
        public LevelScoreRepository LevelScoreRepository { get; set; }
        public ExaminationSubjectRepository ExaminationSubjectRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public DegreeForeignLanguageRepository DegreeForeignLanguageRepository { get; set; }
        public ExecutablePlanRepository ExecutablePlanRepository { get; set; }
        public ExecutablePlanProfileRepository ExecutablePlanProfileRepository { get; set; }
        public ExecutablePlanTeachingSettingRepository ExecutablePlanTeachingSettingRepository { get; set; }
        public CoursematerialRepository CoursematerialRepository { get; set; }
        public FinallyScoreRepository FinallyScoreRepository { get; set; }

        /// <summary>
        /// 查询对应的学位条件信息View
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<DegreeConditionView> GetDegreeConditionViewQueryable(Expression<Func<ER_DegreeCondition, bool>> exp)
        {
            var query = from dc in DegreeConditionRepository.GetList(exp)
                        select new DegreeConditionView
                        {
                            DegreeConditionID = dc.DegreeConditionID,
                            OrderNo = dc.OrderNo,
                            Title = dc.Title,
                            MethodFullName = dc.MethodFullName,
                            IsEnable = dc.RecordStatus.Value > 0 ? true : false,
                            RecordStatus = dc.RecordStatus,
                            CreateUserID = dc.CreateUserID,
                            CreateTime = dc.CreateTime,
                            ModifyUserID = dc.ModifyUserID,
                            ModifyTime = dc.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的学位外语成绩信息DegreeForeignLanguageScoreView（根据学生信息）
        /// 注：同一学生、同一考试科目，以最新的考试日期对应的成绩信息作为最终成绩（只取最终成绩，用户ID、考试科目ID分组）
        /// 注：根据学位外语设置信息进行内联
        /// </summary>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<DegreeForeignLanguageScoreView> GetDegreeForeignLanguageScoreViewQueryable(Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from stu in StudentRepository.GetList(expStudent)
                        join ls in LevelScoreRepository.Entities
                        on stu.UserID equals ls.UserID
                        join dl in DegreeForeignLanguageRepository.Entities
                        on ls.ExaminationSubjectID equals dl.ExaminationSubjectID
                        join examsub in ExaminationSubjectRepository.Entities
                        on ls.ExaminationSubjectID equals examsub.ExaminationSubjectID
                        join sy in SchoolyearRepository.Entities
                        on ls.SchoolyearID equals sy.SchoolyearID
                        join cl in ClassmajorRepository.Entities
                        on stu.ClassmajorID equals cl.ClassmajorID
                        join gr in GrademajorRepository.Entities
                        on cl.GrademajorID equals gr.GrademajorID
                        join fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        join col in CollegeRepository.Entities
                        on fa.CollegeID equals col.CollegeID
                        join us in UserRepository.Entities
                        on stu.UserID equals us.UserID
                        join finls in
                            (
                                from stu in StudentRepository.GetList(expStudent)
                                join levsco in LevelScoreRepository.Entities
                                on stu.UserID equals levsco.UserID
                                group new { stu, levsco } by new { stu.UserID, levsco.ExaminationSubjectID } into grls
                                select new
                                {
                                    UserID = grls.Key.UserID,
                                    ExaminationSubjectID = grls.Key.ExaminationSubjectID,
                                    ExaminationDate = grls.Max(x => x.levsco.ExaminationDate)
                                }
                            )
                        on new { UserID = stu.UserID, ExaminationSubjectID = ls.ExaminationSubjectID, ExaminationDate = ls.ExaminationDate }
                        equals
                        new { UserID = finls.UserID, ExaminationSubjectID = finls.ExaminationSubjectID, ExaminationDate = finls.ExaminationDate }
                        select new DegreeForeignLanguageScoreView
                        {
                            LevelScoreID = ls.LevelScoreID,
                            SchoolyearID = ls.SchoolyearID,
                            SchoolyearCode = sy.Code,
                            ExaminationSubjectID = ls.ExaminationSubjectID,
                            ExaminationSubjectName = examsub.Name,
                            UserID = stu.UserID,
                            StudentNo = us.LoginID,
                            UserName = us.Name,
                            SexID = stu.SexID,
                            IDNumber = stu.IDNumber,
                            InSchoolStatusID = stu.InSchoolStatusID,
                            StudentStatusID = stu.StudentStatus,
                            YearID = gr.GradeID,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            ClassmajorID = stu.ClassmajorID,
                            ClassmajorCode = cl.No,
                            ClassmajorName = cl.Name,
                            GrademajorID = cl.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            FacultymajorID = gr.FacultymajorID,
                            FacultymajorCode = fa.Code,
                            FacultymajorName = fa.Name,
                            CollegeID = fa.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            ScoreNo = ls.ScoreNo,
                            ExaminationDate = ls.ExaminationDate,
                            //ValidDate = ls.ValidDate,
                            TotalScore = ls.TotalScore,
                            ApprovalStatus = ls.ApprovalStatus,
                            PassScore = dl.PassScore,
                            Remark = ls.Remark,
                            RecordStatus = ls.RecordStatus,
                            CreateUserID = ls.CreateUserID,
                            CreateTime = ls.CreateTime,
                            ModifyUserID = ls.ModifyUserID,
                            ModifyTime = ls.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的学生学位课程执行计划信息StudentExecutablePlanView（根据学生信息，只查询相应的学位课程信息）
        /// 注：由于存在在不同的学年学期参加补考的情况，匹配学生最终成绩时，采用开学学期ID匹配（学年学期暂以执行计划为准）
        /// </summary>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<StudentExecutablePlanView> GetStudentDegreeCourseExecutablePlanViewQueryable(Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from stu in StudentRepository.GetList(expStudent)
                        join cl in ClassmajorRepository.Entities
                        on stu.ClassmajorID equals cl.ClassmajorID
                        join gr in GrademajorRepository.Entities
                        on cl.GrademajorID equals gr.GrademajorID
                        join ep in ExecutablePlanRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                        on gr.GrademajorID equals ep.GrademajorID
                        join sy in SchoolyearRepository.Entities
                        on ep.SchoolyearID equals sy.SchoolyearID
                        join cou in CoursematerialRepository.Entities
                        on ep.CoursematerialID equals cou.CoursematerialID
                        join pf in ExecutablePlanProfileRepository.Entities
                        on ep.ExecutablePlanID equals pf.ExecutablePlanID into temppf
                        from eppro in temppf.DefaultIfEmpty()
                        join st in ExecutablePlanTeachingSettingRepository.Entities
                        on ep.ExecutablePlanID equals st.ExecutablePlanID into tempst
                        from epst in tempst.DefaultIfEmpty()
                        where eppro.IsMainCourse == true
                        select new StudentExecutablePlanView
                        {
                            UserID = stu.UserID,
                            ExecutablePlanID = ep.ExecutablePlanID,
                            SpecialtyPlanID = ep.SpecialtyPlanID,
                            SchoolyearID = ep.SchoolyearID,
                            SchoolyearCode = sy.Code,
                            GrademajorID = ep.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            CoursematerialID = ep.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            CourseTypeID = ep.CourseTypeID,
                            Credit = epst.Credit,
                            TheoryCourse = epst.TheoryCourse,
                            Practicehours = epst.Practicehours,
                            Trialhours = epst.Trialhours,
                            SchoolyearNumID = (sy.Value - ((gr.GradeID * 2 - 1) + gr.SemesterID - 1)) / 2 + 1,
                            SchoolcodeID = sy.SchoolcodeID,
                            StarttermID = sy.Value - ((gr.GradeID * 2 - 1) + gr.SemesterID - 1) + 1,
                            IsSpecialtycore = eppro.IsSpecialtycore == null ? false : eppro.IsSpecialtycore.Value,
                            IsCooperation = eppro.IsCooperation == null ? false : eppro.IsCooperation.Value,
                            IsRequired = eppro.IsRequired == null ? false : eppro.IsRequired.Value,
                            IsElective = eppro.IsElective == null ? false : eppro.IsElective.Value,
                            IsNetworkCourse = eppro.IsNetworkCourse == null ? false : eppro.IsNetworkCourse.Value,
                            IsMainCourse = eppro.IsMainCourse == null ? false : eppro.IsMainCourse.Value,
                            ResultTypeID = ep.ResultTypeID
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的学生最终成绩信息DegreeFinallyScoreView（根据学位申请信息匹配学位课程执行计划,只查询相应的学位课程成绩信息）
        /// 注：学生学位课程最终成绩以学位课程执行计划为准（左连），未修习成绩默认为0分
        /// 注：由于存在在不同的学年学期参加补考的情况，匹配学生最终成绩时，采用开学学期ID匹配
        /// 注：成绩为空时，学年学期以执行计划为准，否则以最终成绩为准
        /// 注：匹配暂时不考虑总学时TotalHours
        /// </summary>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<DegreeFinallyScoreView> GetDegreeCourseFinallyScoreExecutablePlanViewQueryable(Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from stu in StudentRepository.GetList(expStudent)
                        join us in UserRepository.Entities
                        on stu.UserID equals us.UserID
                        join cl in ClassmajorRepository.Entities
                        on stu.ClassmajorID equals cl.ClassmajorID
                        join gr in GrademajorRepository.Entities
                        on cl.GrademajorID equals gr.GrademajorID
                        join ep in ExecutablePlanRepository.Entities
                        on gr.GrademajorID equals ep.GrademajorID
                        join sy in SchoolyearRepository.Entities
                        on ep.SchoolyearID equals sy.SchoolyearID
                        join cou in CoursematerialRepository.Entities
                        on ep.CoursematerialID equals cou.CoursematerialID
                        join pf in ExecutablePlanProfileRepository.Entities
                        on ep.ExecutablePlanID equals pf.ExecutablePlanID into temppf
                        from eppro in temppf.DefaultIfEmpty()
                        join st in ExecutablePlanTeachingSettingRepository.Entities
                        on ep.ExecutablePlanID equals st.ExecutablePlanID into tempst
                        from epst in tempst.DefaultIfEmpty()
                        join epfinsco in
                            (
                                from stu in StudentRepository.GetList(expStudent)
                                join finsco in FinallyScoreRepository.Entities
                                on stu.UserID equals finsco.UserID
                                join finsy in SchoolyearRepository.Entities
                                on finsco.SchoolyearID equals finsy.SchoolyearID
                                select new
                                {
                                    FinallyScoreID = finsco.FinallyScoreID,
                                    SchoolyearID = finsco.SchoolyearID,
                                    SchoolyearCode = finsy.Code,
                                    ClassName = finsco.ClassName,
                                    ExaminationTypeID = finsco.ExaminationType,
                                    UserID = finsco.UserID,
                                    CoursematerialID = finsco.CoursematerialID,
                                    CourseTypeID = finsco.CourseTypeID,
                                    SchoolyearNumID = finsco.SchoolyearNumID,
                                    StarttermID = finsco.StarttermID,
                                    ResultTypeID = finsco.ResultTypeID,
                                    CreateUserID = finsco.CreateUserID,
                                    Credit = finsco.Credit,
                                    TotalHours = finsco.TotalHours,
                                    ExamsCategoryID = finsco.ExamsCategoryID,
                                    ExamsStateID = finsco.ExamsStateID,
                                    TotalScore = finsco.TotalScore,
                                    ScoreCredit = finsco.ScoreCredit,
                                    GradePoint = finsco.GradePoint,
                                    Remark = finsco.Remark,
                                    CreateTime = finsco.CreateTime
                                }
                            )
                        on
                        new { UserID = stu.UserID, StarttermID = (sy.Value - ((gr.GradeID * 2 - 1) + gr.SemesterID - 1) + 1), CoursematerialID = ep.CoursematerialID, CourseTypeID = ep.CourseTypeID, Credit = epst.Credit }
                        equals
                        new { UserID = epfinsco.UserID.Value, StarttermID = epfinsco.StarttermID, CoursematerialID = epfinsco.CoursematerialID, CourseTypeID = epfinsco.CourseTypeID, Credit = epfinsco.Credit }
                        into tempepfinsco
                        from epfinsco in tempepfinsco.DefaultIfEmpty()
                        where eppro.IsMainCourse == true
                        select new DegreeFinallyScoreView
                        {
                            FinallyScoreID = epfinsco.FinallyScoreID,
                            SchoolyearID = epfinsco.SchoolyearID,
                            SchoolyearCode = epfinsco.FinallyScoreID == null ? sy.Code : epfinsco.SchoolyearCode,
                            ClassName = epfinsco.ClassName,
                            ExaminationTypeID = epfinsco.ExaminationTypeID,
                            ExaminationModeID = eppro.ExaminationModeID,
                            CoursematerialID = ep.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            CourseTypeID = ep.CourseTypeID,
                            IsMainCourse = eppro.IsMainCourse == null ? false : eppro.IsMainCourse.Value,
                            Credit = epst.Credit,
                            TheoryCourse = epst.TheoryCourse,
                            Practicehours = epst.Practicehours,
                            Trialhours = epst.Trialhours,
                            Totalhours = (epst.TheoryCourse ?? 0) + (epst.Practicehours ?? 0),
                            ResultTypeID = ep.ResultTypeID,
                            DepartmentID = ep.DepartmentID,
                            SchoolyearNumID = (sy.Value - ((gr.GradeID * 2 - 1) + gr.SemesterID - 1)) / 2 + 1,
                            SchoolcodeID = sy.SchoolcodeID,
                            StarttermID = sy.Value - ((gr.GradeID * 2 - 1) + gr.SemesterID - 1) + 1,
                            UserID = stu.UserID,
                            LoginID = us.LoginID,
                            UserName = us.Name,
                            ClassmajorID = stu.ClassmajorID,
                            ClassmajorCode = cl.No,
                            ClassmajorName = cl.Name,
                            GrademajorID = cl.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            ExamsCategoryID = epfinsco.ExamsCategoryID == null ? (int)CF_ExamsCategory.FinalExam : epfinsco.ExamsCategoryID,
                            ExamsStateID = epfinsco.ExamsStateID == null ? (int)CF_ExamsState.NormalExams : epfinsco.ExamsStateID,
                            TotalScore = epfinsco.TotalScore ?? 0,
                            ScoreCredit = epfinsco.ScoreCredit ?? 0,
                            GradePoint = epfinsco.GradePoint ?? 0,
                            Remark = epfinsco.Remark,
                            CreateTime = epfinsco.CreateTime
                        };

            return query;
        }

    }
}