JW/EMiS.DataLogic/ScoreManage/SpecialityScoreSumDAL.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 Bowin.Common.Linq;
using EMIS.ViewModel;
using EMIS.ViewModel.Students;
using EMIS.DataLogic.Common.Students;

namespace EMIS.DataLogic.ScoreManage
{
    public class SpecialityScoreSumDAL
    {
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public CoursematerialRepository CoursematerialRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public ExecutablePlanRepository ExecutablePlanRepository { get; set; }
        public ExecutableOptionalCourseRepository ExecutableOptionalCourseRepository { get; set; }
        public ExecutableFreeSelectionCouseRepository ExecutableFreeSelectionCouseRepository { get; set; }
        public EducationMissionClassRepository EducationMissionClassRepository { get; set; }
        public EducationMissionRepository EducationMissionRepository { get; set; }
        public FinallyScoreRepository finallyScoreRepository { get; set; }
        public GraduationCourseTypeSettingRepository graduationCourseTypeSettingRepository { get; set; }
        public CourseTypeSelectCourseTypeRepository courseTypeSelectCourseTypeRepository { get; set; }
        public ClubCourseRepository clubCourseRepository { get; set; }
        public DictionaryItemRepository dictionaryItemRepository { get; set; }
        public DepartmentRepository departmentRepository { get; set; }
        public DictionaryItemRepository DictionaryItemRepository { get; set; }
        public ResultTypeDetailRepository ResultTypeDetailRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public ExamsStateSettingRepository examsStateSettingRepository { get; set; }

        public IQueryable<CoursematerialPlanView> GetCoursematerialPlanViewQueryable(Expression<Func<CF_Classmajor, bool>> classmajorExp,
            Expression<Func<EM_EducationMissionClass, bool>> missionClassExp)
        {
            //获取课程
            var queryCourse = (from c in ClassmajorRepository.GetList(classmajorExp)
                               join g in GrademajorRepository.Entities on c.GrademajorID equals g.GrademajorID
                               join f in FacultymajorRepository.Entities on g.FacultymajorID equals f.FacultymajorID
                               join college in CollegeRepository.Entities on f.CollegeID equals college.CollegeID
                               from temc in c.EM_EducationMissionClass
                               join emc in EducationMissionClassRepository.GetList(missionClassExp) on temc.EducationMissionClassID equals emc.EducationMissionClassID
                               join em in EducationMissionRepository.Entities on emc.EducationMissionID equals em.EducationMissionID
                               join sy in SchoolyearRepository.Entities on em.SchoolyearID equals sy.SchoolyearID
                               join cm in CoursematerialRepository.Entities on emc.CoursematerialID equals cm.CoursematerialID
                               select new CoursematerialPlanView
                               {
                                   SchoolyearID = sy.SchoolyearID,
                                   SchoolyearCode = sy.Code,
                                   CollegeID = college.CollegeID,
                                   CollegeName = college.Name,
                                   SchoolyearNumID = (sy.Value - (((g.GradeID * 2 - 1) + (g.SemesterID - 1))) + 1 - (sy.SchoolcodeID == g.SemesterID ? 0 : 1)) / 2 + 1,
                                   SchoolcodeID = sy.SchoolcodeID,
                                   StartTermID = ((sy.Value - ((g.GradeID * 2 - 1) + (g.SemesterID - 1))) + 1),
                                   CoursematerialID = cm.CoursematerialID,
                                   CourseCode = cm.CourseCode,
                                   CourseName = cm.CourseName,
                                   TypePlan = 1
                               } into s
                               group s by new { s.StartTermID, s.CoursematerialID, s.TypePlan } into g
                               select g.FirstOrDefault()
                               );

            return queryCourse;
        }

        public IQueryable<ER_FinallyScore> GetFinallyScoreQueryable(Guid? collegeID, int? years, int? schoolyearNumID, int? schoolcodeID, Guid? classmajorID)
        {
            Expression<Func<ER_FinallyScore, bool>> exp = (x => true);
            if (collegeID.HasValue)
                exp = exp.And(x => x.CF_Student.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CollegeID == collegeID);
            if (years.HasValue)
                exp = exp.And(x => x.CF_Student.CF_Classmajor.CF_Grademajor.GradeID == years);
            if (schoolyearNumID.HasValue)
                exp = exp.And(x => x.SchoolyearNumID == schoolyearNumID);
            if (schoolcodeID.HasValue)
                exp = exp.And(x => (x.CF_Student.CF_Classmajor.CF_Grademajor.SemesterID + (x.StarttermID - 1)) % 2 == schoolcodeID % 2);
            if (classmajorID.HasValue)
                exp = exp.And(x => x.CF_Student.ClassmajorID == classmajorID);
            var query = finallyScoreRepository.GetList(exp, (x => x.CF_Student), (x => x.CF_Student.Sys_User), (x => x.CF_Schoolyear), (x => x.CF_Student.CF_Classmajor.CF_Grademajor.CF_Facultymajor), (x => x.CF_Student.CF_Classmajor));

            return query.OrderByDescending(x => x.CF_Schoolyear.Code);
        }
        public virtual IQueryable<StudentScoreView> GetStudentScoreViewQueryable(Guid? collegeID, int? years, int? schoolyearNumID, int? schoolcodeID, Guid? classmajorID)
        {
            List<int?> examsStateList = examsStateSettingRepository.GetList(x => x.IsNormal == true).Select(x => x.ExamsStateID).ToList();

            Expression<Func<ER_FinallyScore, bool>> exp = (x => true);
            if (collegeID.HasValue)
                exp = exp.And(x => x.CF_Student.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CollegeID == collegeID);
            if (years.HasValue)
                exp = exp.And(x => x.CF_Student.CF_Classmajor.CF_Grademajor.GradeID == years);
            if (schoolyearNumID.HasValue)
                exp = exp.And(x => x.SchoolyearNumID == schoolyearNumID);
            if (schoolcodeID.HasValue)
                exp = exp.And(x => (x.CF_Student.CF_Classmajor.CF_Grademajor.SemesterID + (x.StarttermID - 1)) % 2 == schoolcodeID % 2);
            if (classmajorID.HasValue)
                exp = exp.And(x => x.CF_Student.ClassmajorID == classmajorID);
            var query = from x in   finallyScoreRepository.GetList(exp, (x => x.CF_Student), (x => x.CF_Student.Sys_User), (x => x.CF_Schoolyear), (x => x.CF_Student.CF_Classmajor.CF_Grademajor.CF_Facultymajor), (x => x.CF_Student.CF_Classmajor))
                        join ed in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(CF_ExamsState).Name) 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
                        {
                        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_Department.CollegeID,
                        CollegeName = x.CF_Department.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,
                        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,
                        TotalScoreStr = examsStateList.Any(w => w.Value == x.ExamsStateID) ? (Result.Name ?? ((double)x.TotalScore).ToString()) : f.Name,
                        ScoreCredit = x.ScoreCredit ?? 0,
                        GradePoint = x.GradePoint,
                        Remarks = x.Remark,
                        StudentScoreRemark = x.Remark,
                        CreateTime = x.CreateTime,
                        CreateUserID = x.CreateUserID,
                        Pingshi = Math.Round(x.ER_FinallyScoreDetail.Where(q => q.ScoreTypeID == (int)EMIS.ViewModel.CF_ScoreType.Peacetime).FirstOrDefault().Score ?? 0, 0),
                        Jishu = Math.Round(x.ER_FinallyScoreDetail.Where(q => q.ScoreTypeID == (int)EMIS.ViewModel.CF_ScoreType.Technique).FirstOrDefault().Score ?? 0, 0),
                        Lilun = Math.Round(x.ER_FinallyScoreDetail.Where(q => q.ScoreTypeID == (int)EMIS.ViewModel.CF_ScoreType.Theoretical).FirstOrDefault().Score ?? 0, 0)
                    };
            return query.OrderByDescending(x => x.SchoolyearCode);
        }
        public IQueryable<StudentScoreView> GetFinalScoreQueryable(Expression<Func<ER_FinallyScore, bool>> scoreExp, Expression<Func<CF_Student, bool>> studentExp,
            Expression<Func<CF_Grademajor, bool>> gradeExp, Expression<Func<CF_Facultymajor, bool>> facultyExp, Expression<Func<EMIS.Entities.CF_Schoolyear, bool>> schoolyearExp)
        {
            List<int?> examsStateList = examsStateSettingRepository.GetList(x => x.IsNormal == true).Select(x => x.ExamsStateID).ToList();

            var query = from x in finallyScoreRepository.GetList(scoreExp)
                        join student in StudentRepository.Entities.Where(studentExp) on x.UserID equals student.UserID 
                        join user in UserRepository.Entities on student.UserID equals user.UserID 
                        join classmajor in ClassmajorRepository.Entities on student.ClassmajorID equals classmajor.ClassmajorID 
                        join grade in GrademajorRepository.Entities.Where(gradeExp) on classmajor.GrademajorID equals grade.GrademajorID 
                        join faculty in FacultymajorRepository.Entities.Where(facultyExp) on grade.FacultymajorID equals faculty.FacultymajorID 
                        join schoolyear in SchoolyearRepository.Entities.Where(schoolyearExp) on x.SchoolyearID equals schoolyear.SchoolyearID 
                        join ed in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(CF_ExamsState).Name) on x.ExamsStateID equals ed.Value
                        into ged
                        from f in ged.DefaultIfEmpty()
                        join d in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(CF_ResultType).Name) 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
                        {
                            SchoolyearID = x.SchoolyearID,
                            SchoolyearCode = schoolyear.Code,
                            SchoolyearNumID = x.SchoolyearNumID,
                            ClassName = x.ClassName,
                            GrademajorID = grade.GrademajorID,
                            GrademajorCode = grade.Code,
                            GrademajorName = grade.Name,
                            ClassmajorID = classmajor.ClassmajorID,
                            ClassmajorCode = classmajor.No,
                            ClassmajorName = classmajor.Name,
                            CollegeID = x.CF_Department.CollegeID,
                            CollegeName = x.CF_Department.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,
                            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,
                            TotalScoreStr = examsStateList.Any(w => w.Value == x.ExamsStateID) ? (Result.Name ?? ((double)x.TotalScore).ToString()) : f.Name,
                            ScoreCredit = x.ScoreCredit ?? 0,
                            GradePoint = x.GradePoint,
                            Remarks = x.Remark,
                            StudentScoreRemark = x.Remark,
                            CreateTime = x.CreateTime,
                            CreateUserID = x.CreateUserID,
                            Pingshi = Math.Round(x.ER_FinallyScoreDetail.Where(q => q.ScoreTypeID == (int)EMIS.ViewModel.CF_ScoreType.Peacetime).FirstOrDefault().Score ?? 0, 0),
                            Jishu = Math.Round(x.ER_FinallyScoreDetail.Where(q => q.ScoreTypeID == (int)EMIS.ViewModel.CF_ScoreType.Technique).FirstOrDefault().Score ?? 0, 0),
                            Lilun = Math.Round(x.ER_FinallyScoreDetail.Where(q => q.ScoreTypeID == (int)EMIS.ViewModel.CF_ScoreType.Theoretical).FirstOrDefault().Score ?? 0, 0)
                        };
            return query.OrderByDescending(x => x.SchoolyearCode);
        }

        //获取总学分
        public IQueryable<SpecialityCreditView> GetCreditQueryable(Expression<Func<ER_FinallyScore, bool>> scoreExp, Expression<Func<CF_Student, bool>> studentExp,
            Expression<Func<CF_Grademajor, bool>> gradeExp, Expression<Func<CF_Facultymajor, bool>> facultyExp, Expression<Func<EMIS.Entities.CF_Schoolyear, bool>> schoolyearExp)
        {
            var finallyScoreQuery = (from score in finallyScoreRepository.GetList(scoreExp) 
                        join student in StudentRepository.Entities.Where(studentExp) on score.UserID equals student.UserID 
                        join user in UserRepository.Entities on student.UserID equals user.UserID 
                        join classmajor in ClassmajorRepository.Entities on student.ClassmajorID equals classmajor.ClassmajorID 
                        join grade in GrademajorRepository.Entities.Where(gradeExp) on classmajor.GrademajorID equals grade.GrademajorID 
                        join faculty in FacultymajorRepository.Entities.Where(facultyExp) on grade.FacultymajorID equals faculty.FacultymajorID 
                        join schoolyear in SchoolyearRepository.Entities.Where(schoolyearExp) on score.SchoolyearID equals schoolyear.SchoolyearID 
                        select score);
            //总学分（必修、限选、任选学分）
            var TotalCredit = from q in finallyScoreQuery
                                 join a in courseTypeSelectCourseTypeRepository.Entities
                                    on q.CourseTypeID equals a.CourseTypeID
                                 group new { score = q, courseType = a } by q.UserID
                                     into g 
                                 select new SpecialityCreditView 
                                 {
                                    UserID = g.Key,
                                    RequiredCredit = g.Sum(x => (x.courseType.IsRequired == true) ? (x.score.ScoreCredit ?? 0) : 0),
                                    OptionalCourseCredit = g.Sum(x => (x.courseType.IsOptionalCourse == true) ? (x.score.ScoreCredit ?? 0) : 0),
                                    FreeSelectionCourseCredit = g.Sum(x => (x.courseType.IsFreeSelectionCourse == true) ? (x.score.ScoreCredit ?? 0) : 0),
                                    GeneralEducationCredit = 0,
                                    ElectiveCredit = 0,
                                    ElectivePublicCredit = 0,
                                    SchoolbasedWithClub = 0,
                                    SchoolbasedNotCredit = 0,
                                    SchoolbasedCredit = 0,
                                    SchoolbasedRealCredit = 0,
                                    PractiseWithClub = 0,
                                    PractiseNotCredit = 0,
                                    PractiseCredit = 0,
                                    PractiseRealCredit = 0,
                                    AvgScore = 0,
                                    TotalCredit = g.Sum(x => x.score.ScoreCredit ?? 0),//总学分 = 必修 + 限选 + 任选
                                 };
            //通识教育、方向选修、公共选修、校本课(不包含俱乐部)、校本课(包含俱乐部)、校本课(包含俱乐部,大于等于4只取4学分)、实践环节(不包含俱乐部)、实践环节(包含俱乐部)、实践环节(包含俱乐部,大于等于4只取4学分)
            var GraduationCourseTypeCredit = from final in finallyScoreQuery
                                         join gcts in graduationCourseTypeSettingRepository.Entities
                                            on final.CourseTypeID equals gcts.CourseTypeID 
                                         join cc in clubCourseRepository.Entities
                                            on final.CoursematerialID equals cc.CoursematerialID
                                            into b
                                         from k in b.DefaultIfEmpty()
                                         group new { score = final, setting = gcts, club = k } by final.UserID
                                             into g
                                         select new SpecialityCreditView 
                                         {
                                            UserID = g.Key,
                                            RequiredCredit = 0,
                                            OptionalCourseCredit = 0,
                                            FreeSelectionCourseCredit = 0,
                                            GeneralEducationCredit = g.Sum(x => (x.setting.IsGeneralEducation == true) ? (x.score.ScoreCredit ?? 0) : 0),
                                            ElectiveCredit = g.Sum(x => (x.setting.IsElective == true) ? (x.score.ScoreCredit ?? 0) : 0),
                                            ElectivePublicCredit = g.Sum(x => (x.setting.IsElectivePublic == true) ? (x.score.ScoreCredit ?? 0) : 0),
                                            SchoolbasedWithClub = g.Sum(x => (x.setting.IsSchoolbasedCurriculum == true && x.club.ClubCourseID != null) ? (x.score.ScoreCredit ?? 0) : 0),
                                            SchoolbasedNotCredit = g.Sum(x => (x.setting.IsSchoolbasedCurriculum == true && x.club.ClubCourseID == null) ? (x.score.ScoreCredit ?? 0) : 0),
                                            SchoolbasedCredit = g.Sum(x => (x.setting.IsSchoolbasedCurriculum == true) ? (x.score.ScoreCredit ?? 0) : 0),
                                            SchoolbasedRealCredit = (g.Sum(x => (x.setting.IsSchoolbasedCurriculum == true) ? (x.score.ScoreCredit ?? 0) : 0) > 4) ? 4 : g.Sum(x => (x.setting.IsSchoolbasedCurriculum == true) ? (x.score.ScoreCredit ?? 0) : 0),
                                            PractiseWithClub = g.Sum(x => (x.setting.IsPractise == true && x.club.ClubCourseID != null) ? (x.score.ScoreCredit ?? 0) : 0),
                                            PractiseNotCredit = g.Sum(x => (x.setting.IsPractise == true && x.club.ClubCourseID == null) ? (x.score.ScoreCredit ?? 0) : 0),
                                            PractiseCredit = g.Sum(x => (x.setting.IsPractise == true) ? (x.score.ScoreCredit ?? 0) : 0),
                                            PractiseRealCredit = (g.Sum(x => (x.setting.IsPractise == true) ? (x.score.ScoreCredit ?? 0) : 0) > 4) ? 4 : g.Sum(x => (x.setting.IsPractise == true) ? (x.score.ScoreCredit ?? 0) : 0),
                                            AvgScore = 0,
                                            TotalCredit = 0
                                         };

            //平均分
            var AvgScore = from final in finallyScoreQuery
                           group final by final.UserID
                               into a 
                           select new SpecialityCreditView 
                           {
                                UserID = a.Key,
                                RequiredCredit = 0,
                                OptionalCourseCredit = 0,
                                FreeSelectionCourseCredit = 0,
                                GeneralEducationCredit = 0,
                                ElectiveCredit = 0,
                                ElectivePublicCredit = 0,
                                SchoolbasedWithClub = 0,
                                SchoolbasedNotCredit = 0,
                                SchoolbasedCredit = 0,
                                SchoolbasedRealCredit = 0,
                                PractiseWithClub = 0,
                                PractiseNotCredit = 0,
                                PractiseCredit = 0,
                                PractiseRealCredit = 0,
                                AvgScore = a.Average(x => x.TotalScore ?? 0),
                                TotalCredit = 0
                           };

            var queryall = from score in TotalCredit.Concat(GraduationCourseTypeCredit).Concat(AvgScore) 
                           group score by score.UserID into g 
                           select new SpecialityCreditView
                           {
                               UserID = g.Key,
                               RequiredCredit = g.Sum(x => x.RequiredCredit),
                               OptionalCourseCredit = g.Sum(x => x.OptionalCourseCredit),
                               FreeSelectionCourseCredit = g.Sum(x => x.FreeSelectionCourseCredit),
                               GeneralEducationCredit = g.Sum(x => x.GeneralEducationCredit),
                               ElectiveCredit = g.Sum(x => x.ElectiveCredit),
                               ElectivePublicCredit = g.Sum(x => x.ElectivePublicCredit),
                               SchoolbasedWithClub = g.Sum(x => x.SchoolbasedWithClub),
                               SchoolbasedNotCredit = g.Sum(x => x.SchoolbasedNotCredit),
                               SchoolbasedCredit = g.Sum(x => x.SchoolbasedCredit),
                               SchoolbasedRealCredit = g.Sum(x => x.SchoolbasedRealCredit),
                               PractiseWithClub = g.Sum(x => x.PractiseWithClub),
                               PractiseNotCredit = g.Sum(x => x.PractiseNotCredit),
                               PractiseCredit = g.Sum(x => x.PractiseCredit),
                               PractiseRealCredit = g.Sum(x => x.PractiseRealCredit),
                               AvgScore = g.Sum(x => x.AvgScore),
                               TotalCredit = g.Sum(x => x.TotalCredit)
                           };
            return queryall;
        }

        //根据最终成绩获取课程
        public IQueryable<CoursematerialPlanView> GetCoursematerialViewQueryable(Expression<Func<ER_FinallyScore, bool>> scoreExp, 
            Expression<Func<CF_Student, bool>> studentExp,
            Expression<Func<CF_Grademajor, bool>> grademajorExp,
            Expression<Func<CF_Facultymajor, bool>> facultyExp,
            Expression<Func<EMIS.Entities.CF_Schoolyear, bool>> schoolyearExp)
        {
            var queryCourse = (from score in finallyScoreRepository.GetList(scoreExp)
                               join cm in CoursematerialRepository.Entities on score.CoursematerialID equals cm.CoursematerialID 
                               join student in StudentRepository.GetList(studentExp) on score.UserID equals student.UserID 
                               join classmajor in ClassmajorRepository.Entities on student.ClassmajorID equals classmajor.ClassmajorID 
                               join grade in GrademajorRepository.GetList(grademajorExp) on classmajor.GrademajorID equals grade.GrademajorID 
                               join facult in FacultymajorRepository.GetList(facultyExp) on grade.FacultymajorID equals facult.FacultymajorID 
                               join de in departmentRepository.Entities on score.DepartmentID equals de.DepartmentID
                               join college in CollegeRepository.Entities on de.CollegeID equals college.CollegeID
                               join sy in SchoolyearRepository.GetList(schoolyearExp) on score.SchoolyearID equals sy.SchoolyearID
                               select new CoursematerialPlanView
                               {
                                   SchoolyearID = sy.SchoolyearID,
                                   SchoolyearCode = sy.Code,
                                   CollegeID = college.CollegeID,
                                   CollegeName = college.Name,
                                   Credit = score.Credit,
                                   CourseTypeID = score.CourseTypeID,
                                   SchoolyearNumID = score.SchoolyearNumID,
                                   SchoolcodeID = sy.SchoolcodeID,
                                   StartTermID = score.StarttermID,
                                   //StartTermID = ((sy.Value - ((g.SchoolyearID * 2 - 1) + (g.SchoolcodeID - 1))) + 1),
                                   CoursematerialID = cm.CoursematerialID,
                                   CourseCode = cm.CourseCode,
                                   CourseName = cm.CourseName,
                                   //TypePlan = 1
                               } into s
                               group s by new { s.StartTermID, s.CoursematerialID } into g
                               select g.FirstOrDefault()
                               );

            return queryCourse;
        }

        public List<int> GetCourseTypeQueryble()
        {
            var query = from b in dictionaryItemRepository.GetList(x => x.DictionaryCode == "CF_CourseType")
                        join c in courseTypeSelectCourseTypeRepository.Entities
                        on b.Value equals c.CourseTypeID
                        where c.IsRequired == true
                        select b.Value.Value;
            return query.ToList();
        }

        public string GetIsByCourseTypeID(int courseTypeID)
        {
            var query = from b in courseTypeSelectCourseTypeRepository.Entities
                        where b.CourseTypeID == courseTypeID
                        select new
                        {
                            IsRequired = b.IsRequired,
                            IsOptionalCourse = b.IsOptionalCourse,
                            IsFreeSelectionCourse = b.IsFreeSelectionCourse,
                        };

            var a = query.FirstOrDefault();
            if ((bool)a.IsRequired)
            {
                return "必";
            }
            if ((bool)a.IsOptionalCourse)
            {
                return "限";
            }
            if ((bool)a.IsFreeSelectionCourse)
            {
                return "任";
            }
            return "";
        }
    }
}