using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Linq.Expressions;
using EMIS.DataLogic.Repositories;
using EMIS.ViewModel.GraduationManage.GraduationSetting;
using EMIS.Entities;
using EMIS.ViewModel;
using EMIS.ViewModel.Students;
using EMIS.ViewModel.CultureplanManage.PlanManagement;
using EMIS.ViewModel.ScoreManage;
using EMIS.ViewModel.GraduationManage.GraduationRequirement;
using EMIS.ViewModel.GraduationManage.GraduationManage;
using EMIS.ViewModel.ChargeManage.ArrearsSituation;
using EMIS.ViewModel.EducationManage;

namespace EMIS.DataLogic.GraduationManage.GraduationSetting
{
    public class GraduationConditionDAL
    {
        public GraduationConditionRepository GraduationConditionRepository { 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 SpecialtyPlanRepository SpecialtyPlanRepository { get; set; }
        public ExecutablePlanRepository ExecutablePlanRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public CoursematerialRepository CoursematerialRepository { get; set; }
        public CourseTypeSelectCourseTypeRepository CourseTypeSelectCourseTypeRepository { get; set; }
        public ClubCourseRepository ClubCourseRepository { get; set; }
        public FinallyScoreRepository FinallyScoreRepository { get; set; }
        public GraduationCourseRepository GraduationCourseRepository { get; set; }
        public GraduateDesignCoursematerialRepository GraduateDesignCoursematerialRepository { get; set; }
        public GraduationCourseTypeRequirementRepository GraduationCourseTypeRequirementRepository { get; set; }
        public GraduationRequirementRepository GraduationRequirementRepository { get; set; }
        public GraduationStandardRepository GraduationStandardRepository { get; set; }
        public GraduationCourseTypeSettingRepository GraduationCourseTypeSettingRepository { get; set; }
        public StudentChargePaymentRepository StudentChargePaymentRepository { get; set; }
        public StudentChargeRepository StudentChargeRepository { get; set; }
        public PassStudentRepository PassStudentRepository { get; set; }

        /// <summary>
        /// 查询对应的毕业条件信息View
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<GraduationConditionView> GetGraduationConditionQueryable(Expression<Func<ER_GraduationCondition, bool>> exp)
        {
            var query = from gc in GraduationConditionRepository.GetList(exp)
                        select new GraduationConditionView
                        {
                            GraduationConditionID = gc.GraduationConditionID,
                            OrderNo = gc.OrderNo,
                            Title = gc.Title,
                            MethodFullName = gc.MethodFullName,
                            IsEnable = gc.RecordStatus.Value > 0 ? true : false,
                            RecordStatus = gc.RecordStatus,
                            CreateUserID = gc.CreateUserID,
                            CreateTime = gc.CreateTime,
                            ModifyUserID = gc.ModifyUserID,
                            ModifyTime = gc.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的学生信息View（BaseStudentView，根据学生信息）
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<BaseStudentView> GetBaseStudentViewQueryable(Expression<Func<CF_Student, bool>> exp)
        {
            var query = from stu in StudentRepository.GetList(exp)
                        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 fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        join col in CollegeRepository.Entities
                        on fa.CollegeID equals col.CollegeID
                        select new BaseStudentView
                        {
                            UserID = stu.UserID,
                            LoginID = us.LoginID,
                            UserName = us.Name,
                            SexID = stu.SexID,
                            IDNumber = stu.IDNumber,
                            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,
                            StandardID = fa.StandardID,
                            CollegeID = fa.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            SchoolyearID = gr.GradeID,
                            SchoolcodeID = gr.SemesterID,
                            InSchoolStatusID = stu.InSchoolStatusID,
                            StudentStatus = stu.StudentStatus
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的学生专业计划信息StudentSpecialtyPlanView（根据学生信息）
        /// 注：此为普教版本，成教版本暂不考虑
        /// 注：由于存在在不同的学年学期参加补考的情况，匹配学生最终成绩时，采用开学学期ID匹配（学年学期暂以执行计划为准）
        /// </summary>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<StudentSpecialtyPlanView> GetStudentSpecialtyPlanViewQueryable(Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from sp in SpecialtyPlanRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                        join stu in this.GetBaseStudentViewQueryable(expStudent)
                        on sp.GrademajorID equals stu.GrademajorID
                        join sy in SchoolyearRepository.Entities
                        on sp.SchoolyearID equals sy.SchoolyearID
                        join cou in CoursematerialRepository.Entities
                        on sp.CoursematerialID equals cou.CoursematerialID
                        select new StudentSpecialtyPlanView
                        {
                            UserID = stu.UserID,
                            SpecialtyPlanID = sp.SpecialtyPlanID,
                            SchoolyearID = sp.SchoolyearID,
                            SchoolyearCode = sy.Code,
                            GrademajorID = sp.GrademajorID,
                            GrademajorCode = stu.GrademajorCode,
                            GrademajorName = stu.GrademajorName,
                            CoursematerialID = sp.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            CourseTypeID = sp.CourseTypeID,
                            Credit = sp.EM_SpecialtyPlanTeachingSetting.Credit,
                            TheoryCourse = sp.EM_SpecialtyPlanTeachingSetting.TheoryCourse,
                            Practicehours = sp.EM_SpecialtyPlanTeachingSetting.Practicehours,
                            Trialhours = sp.EM_SpecialtyPlanTeachingSetting.Trialhours,
                            SchoolyearNumID = (sy.Value - ((stu.SchoolyearID * 2 - 1) + stu.SchoolcodeID - 1)) / 2 + 1,
                            SchoolcodeID = sy.SchoolcodeID,
                            StarttermID = sy.Value - ((stu.SchoolyearID * 2 - 1) + stu.SchoolcodeID - 1) + 1,
                            IsRequired = sp.IsRequired == null ? false : sp.IsRequired.Value,
                            IsElective = sp.IsElective == null ? false : sp.IsElective.Value,
                            IsMainCourse = sp.IsMainCourse == null ? false : sp.IsMainCourse.Value,
                            ResultTypeID = sp.ResultTypeID
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的学生执行计划信息StudentExecutablePlanView（根据学生信息）
        /// 注：由于存在在不同的学年学期参加补考的情况，匹配学生最终成绩时，采用开学学期ID匹配（学年学期暂以执行计划为准）
        /// </summary>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<StudentExecutablePlanView> GetStudentExecutablePlanViewQueryable(Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from stu in this.GetBaseStudentViewQueryable(expStudent)
                        join ep in ExecutablePlanRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                        on stu.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
                        select new StudentExecutablePlanView
                        {
                            UserID = stu.UserID,
                            ExecutablePlanID = ep.ExecutablePlanID,
                            SpecialtyPlanID = ep.SpecialtyPlanID,
                            SchoolyearID = ep.SchoolyearID,
                            SchoolyearCode = sy.Code,
                            GrademajorID = ep.GrademajorID,
                            GrademajorCode = stu.GrademajorCode,
                            GrademajorName = stu.GrademajorName,
                            CoursematerialID = ep.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            CourseTypeID = ep.CourseTypeID,
                            Credit = ep.EM_ExecutablePlanTeachingSetting.Credit,
                            TheoryCourse = ep.EM_ExecutablePlanTeachingSetting.TheoryCourse,
                            Practicehours = ep.EM_ExecutablePlanTeachingSetting.Practicehours,
                            Trialhours = ep.EM_ExecutablePlanTeachingSetting.Trialhours,
                            SchoolyearNumID = (sy.Value - ((stu.SchoolyearID * 2 - 1) + stu.SchoolcodeID - 1)) / 2 + 1,
                            SchoolcodeID = sy.SchoolcodeID,
                            StarttermID = sy.Value - ((stu.SchoolyearID * 2 - 1) + stu.SchoolcodeID - 1) + 1,
                            IsSpecialtycore = ep.EM_ExecutablePlanProfile.IsSpecialtycore == null ? false : ep.EM_ExecutablePlanProfile.IsSpecialtycore.Value,
                            IsCooperation = ep.EM_ExecutablePlanProfile.IsCooperation == null ? false : ep.EM_ExecutablePlanProfile.IsCooperation.Value,
                            IsRequired = ep.EM_ExecutablePlanProfile.IsRequired == null ? false : ep.EM_ExecutablePlanProfile.IsRequired.Value,
                            IsElective = ep.EM_ExecutablePlanProfile.IsElective == null ? false : ep.EM_ExecutablePlanProfile.IsElective.Value,
                            IsNetworkCourse = ep.EM_ExecutablePlanProfile.IsNetworkCourse == null ? false : ep.EM_ExecutablePlanProfile.IsNetworkCourse.Value,
                            IsMainCourse = ep.EM_ExecutablePlanProfile.IsMainCourse == null ? false : ep.EM_ExecutablePlanProfile.IsMainCourse.Value,
                            ResultTypeID = ep.ResultTypeID
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的学生最终成绩信息StudentScoreView（根据学生信息）
        /// </summary>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<StudentScoreView> GetFinallyScoreViewQueryable(Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from finsco in FinallyScoreRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                        join stu in this.GetBaseStudentViewQueryable(expStudent)
                        on finsco.UserID equals stu.UserID
                        join sy in SchoolyearRepository.Entities
                        on finsco.SchoolyearID equals sy.SchoolyearID
                        join cou in CoursematerialRepository.Entities
                        on finsco.CoursematerialID equals cou.CoursematerialID
                        select new StudentScoreView
                        {
                            FinallyScoreID = finsco.FinallyScoreID,
                            SchoolyearID = finsco.SchoolyearID,
                            SchoolyearCode = sy.Code,
                            UserID = finsco.UserID,
                            LoginID = stu.LoginID,
                            UserName = stu.UserName,
                            ClassmajorID = stu.ClassmajorID,
                            ClassmajorCode = stu.ClassmajorCode,
                            ClassmajorName = stu.ClassmajorName,
                            GrademajorID = stu.GrademajorID,
                            GrademajorCode = stu.GrademajorCode,
                            GrademajorName = stu.GrademajorName,
                            CoursematerialID = finsco.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            CourseTypeID = finsco.CourseTypeID,
                            Credit = finsco.Credit,
                            TotalHours = finsco.TotalHours,
                            ExamsCategoryID = finsco.ExamsCategoryID,
                            ExamsStateID = finsco.ExamsStateID,
                            TotalScore = finsco.TotalScore,
                            ScoreCredit = finsco.ScoreCredit,
                            SchoolyearNumID = finsco.SchoolyearNumID,
                            StarttermID = finsco.StarttermID,
                            ResultTypeID = finsco.ResultTypeID,
                            CreateUserID = finsco.CreateUserID,
                            CreateTime = finsco.CreateTime
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的学生最终成绩信息StudentScoreView（根据学生信息并且匹配执行计划）
        /// 注：学生最终成绩以执行计划为准（左连），未修习成绩默认为0分
        /// 注：由于存在在不同的学年学期参加补考的情况，匹配学生最终成绩时，采用开学学期ID匹配（学年学期暂以执行计划为准）
        /// 注：匹配暂时不考虑总学时TotalHours
        /// 注：此为默认匹配规则（可个性化配置）
        /// </summary>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public virtual IQueryable<StudentScoreView> GetFinallyScoreExecutablePlanViewQueryable(Expression<Func<CF_Student, bool>> expStudent)
        {
            var studentFinallyScoreList = from finsco in FinallyScoreRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                                          join stu in StudentRepository.GetList(expStudent)
                                          on finsco.UserID equals stu.UserID
                                          select new
                                          {
                                              FinallyScoreID = finsco.FinallyScoreID,
                                              SchoolyearID = finsco.SchoolyearID,
                                              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,
                                              CreateTime = finsco.CreateTime
                                          };

            var query = from stuep in this.GetStudentExecutablePlanViewQueryable(expStudent)
                        join finsco in studentFinallyScoreList
                        on
                        new { UserID = stuep.UserID, StarttermID = stuep.StarttermID, CoursematerialID = stuep.CoursematerialID, CourseTypeID = stuep.CourseTypeID, Credit = stuep.Credit }
                        equals
                        new { UserID = finsco.UserID.Value, StarttermID = finsco.StarttermID, CoursematerialID = finsco.CoursematerialID, CourseTypeID = finsco.CourseTypeID, Credit = finsco.Credit }
                        into tempepfinsco
                        from epfinsco in tempepfinsco.DefaultIfEmpty()
                        select new StudentScoreView
                        {
                            SchoolyearID = stuep.SchoolyearID,
                            SchoolyearCode = stuep.SchoolyearCode,
                            UserID = stuep.UserID,
                            CoursematerialID = stuep.CoursematerialID,
                            CourseCode = stuep.CourseCode,
                            CourseName = stuep.CourseName,
                            CourseTypeID = stuep.CourseTypeID,
                            SchoolyearNumID = stuep.SchoolyearNumID,
                            StarttermID = stuep.StarttermID,
                            ResultTypeID = stuep.ResultTypeID,
                            Credit = stuep.Credit,
                            //TotalHours = stuep.Totalhours,(执行计划总学时类型不对，暂时不处理)
                            ExamsCategoryID = epfinsco.ExamsCategoryID,
                            ExamsStateID = epfinsco.ExamsStateID,
                            TotalScore = epfinsco.TotalScore ?? 0,
                            ScoreCredit = epfinsco.ScoreCredit ?? 0
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的学生最终成绩信息StudentScoreView（根据学生信息）
        /// 注：内连课程类型设置表
        /// 不包括俱乐部课程
        /// </summary>
        /// <param name="expStudent"></param>
        /// <param name="expCourseTypeSelectCourseType"></param>
        /// <returns></returns>
        public IQueryable<StudentScoreView> GetFinallyScoreNoClubViewQueryable(Expression<Func<CF_Student, bool>> expStudent,
                Expression<Func<EM_CourseTypeSelectCourseType, bool>> expCourseTypeSelectCourseType)
        {
            var query = from finsco in FinallyScoreRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                        join stu in this.GetBaseStudentViewQueryable(expStudent)
                        on finsco.UserID equals stu.UserID
                        join couset in CourseTypeSelectCourseTypeRepository.GetList(expCourseTypeSelectCourseType)
                        on finsco.CourseTypeID equals couset.CourseTypeID
                        join sy in SchoolyearRepository.Entities
                        on finsco.SchoolyearID equals sy.SchoolyearID
                        join cou in CoursematerialRepository.Entities
                        on finsco.CoursematerialID equals cou.CoursematerialID
                        join club in ClubCourseRepository.Entities
                        on finsco.CoursematerialID equals club.CoursematerialID
                        into tempclub
                        from scoclub in tempclub.DefaultIfEmpty()
                        where scoclub.ClubCourseID == null
                        select new StudentScoreView
                        {
                            FinallyScoreID = finsco.FinallyScoreID,
                            SchoolyearID = finsco.SchoolyearID,
                            SchoolyearCode = sy.Code,
                            UserID = finsco.UserID,
                            LoginID = stu.LoginID,
                            UserName = stu.UserName,
                            ClassmajorID = stu.ClassmajorID,
                            ClassmajorCode = stu.ClassmajorCode,
                            ClassmajorName = stu.ClassmajorName,
                            GrademajorID = stu.GrademajorID,
                            GrademajorCode = stu.GrademajorCode,
                            GrademajorName = stu.GrademajorName,
                            CoursematerialID = finsco.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            CourseTypeID = finsco.CourseTypeID,
                            Credit = finsco.Credit,
                            TotalHours = finsco.TotalHours,
                            ExamsCategoryID = finsco.ExamsCategoryID,
                            ExamsStateID = finsco.ExamsStateID,
                            TotalScore = finsco.TotalScore,
                            ScoreCredit = finsco.ScoreCredit,
                            SchoolyearNumID = finsco.SchoolyearNumID,
                            StarttermID = finsco.StarttermID,
                            ResultTypeID = finsco.ResultTypeID
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的学生最终成绩信息StudentScoreView（根据学生信息）
        /// 注：内连课程类型设置表
        /// 包含俱乐部课程
        /// </summary>
        /// <param name="expStudent"></param>
        /// <param name="expCourseTypeSelectCourseType"></param>
        /// <returns></returns>
        public IQueryable<StudentScoreView> GetFinallyScoreViewQueryable(Expression<Func<CF_Student, bool>> expStudent,
                Expression<Func<EM_CourseTypeSelectCourseType, bool>> expCourseTypeSelectCourseType)
        {
            var query = from finsco in FinallyScoreRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                        join stu in this.GetBaseStudentViewQueryable(expStudent)
                        on finsco.UserID equals stu.UserID
                        join couset in CourseTypeSelectCourseTypeRepository.GetList(expCourseTypeSelectCourseType)
                        on finsco.CourseTypeID equals couset.CourseTypeID
                        join sy in SchoolyearRepository.Entities
                        on finsco.SchoolyearID equals sy.SchoolyearID
                        join cou in CoursematerialRepository.Entities
                        on finsco.CoursematerialID equals cou.CoursematerialID
                        select new StudentScoreView
                        {
                            FinallyScoreID = finsco.FinallyScoreID,
                            SchoolyearID = finsco.SchoolyearID,
                            SchoolyearCode = sy.Code,
                            UserID = finsco.UserID,
                            LoginID = stu.LoginID,
                            UserName = stu.UserName,
                            ClassmajorID = stu.ClassmajorID,
                            ClassmajorCode = stu.ClassmajorCode,
                            ClassmajorName = stu.ClassmajorName,
                            GrademajorID = stu.GrademajorID,
                            GrademajorCode = stu.GrademajorCode,
                            GrademajorName = stu.GrademajorName,
                            CoursematerialID = finsco.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            CourseTypeID = finsco.CourseTypeID,
                            Credit = finsco.Credit,
                            TotalHours = finsco.TotalHours,
                            ExamsCategoryID = finsco.ExamsCategoryID,
                            ExamsStateID = finsco.ExamsStateID,
                            TotalScore = finsco.TotalScore,
                            ScoreCredit = finsco.ScoreCredit,
                            SchoolyearNumID = finsco.SchoolyearNumID,
                            StarttermID = finsco.StarttermID,
                            ResultTypeID = finsco.ResultTypeID
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的学生最终成绩学分信息PersonalCreditView（根据学生信息）
        /// 注：默认全部为必修课程
        /// </summary>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<PersonalCreditView> GetFinallyScorePersonalCreditViewQueryable(Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from finsco in this.GetFinallyScoreViewQueryable(expStudent)
                        group finsco by new { finsco.UserID, finsco.GrademajorID }
                            into g
                            select new PersonalCreditView
                            {
                                UserID = g.Key.UserID,
                                GrademajorID = g.Key.GrademajorID,
                                CourseCount = g.Count(),
                                RequiredCredit = g.Sum(x => x.ScoreCredit)
                            };

            return query;
        }

        /// <summary>
        /// 查询对应的学生全部最终成绩信息GraduationScoreTypeCountView（根据学生信息并且区分必修、选修）
        /// 注：内连课程类型设置表(未匹配执行计划)
        /// 包含俱乐部课程
        /// </summary>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<GraduationScoreTypeCountView> GetFinallyScoreTypeViewQueryable(Expression<Func<CF_Student, bool>> expStudent)
        {
            var finallyScoreExecutablePlanViewList = from finsco in FinallyScoreRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                                                     join stu in StudentRepository.GetList(expStudent)
                                                     on finsco.UserID equals stu.UserID
                                                     join cou in CoursematerialRepository.Entities
                                                     on finsco.CoursematerialID equals cou.CoursematerialID
                                                     join cs in CourseTypeSelectCourseTypeRepository.Entities
                                                     on finsco.CourseTypeID equals cs.CourseTypeID
                                                     into tempcs
                                                     from couset in tempcs.DefaultIfEmpty()
                                                     select new
                                                     {
                                                         UserID = finsco.UserID,
                                                         CoursematerialID = finsco.CoursematerialID,
                                                         CourseCode = cou.CourseCode,
                                                         CourseName = cou.CourseName,
                                                         CourseTypeID = finsco.CourseTypeID,
                                                         TotalScore = finsco.TotalScore ?? 0,
                                                         ScoreCredit = finsco.ScoreCredit ?? 0,
                                                         IsRequired = couset.IsRequired,
                                                         IsOptionalCourse = couset.IsOptionalCourse,
                                                         IsFreeSelectionCourse = couset.IsFreeSelectionCourse,
                                                         RequiredPass = (couset.IsRequired.Value && finsco.TotalScore >= 60) ? true : false,
                                                         RequiredNotPass = (couset.IsRequired.Value && finsco.TotalScore < 60) ? true : false,
                                                         OptionalCoursePass = (couset.IsOptionalCourse.Value && finsco.TotalScore >= 60) ? true : false,
                                                         OptionalCourseNotPass = (couset.IsOptionalCourse.Value && finsco.TotalScore < 60) ? true : false,
                                                         FreeSelectionCoursePass = (couset.IsFreeSelectionCourse.Value && finsco.TotalScore >= 60) ? true : false,
                                                         FreeSelectionCourseNotPass = (couset.IsFreeSelectionCourse.Value && finsco.TotalScore < 60) ? true : false
                                                     };

            //学生最终成绩统计(分必修、选修)--必修及格门数、必修不及格门数、选修及格门数、选修不及格门数、已修学分
            var query = from finsco in finallyScoreExecutablePlanViewList
                        group finsco by finsco.UserID into g
                        select new GraduationScoreTypeCountView
                        {
                            UserID = g.Key.Value,
                            ExecCourseCount = g.Count(),
                            RequiredPassCount = g.Count(x => x.RequiredPass),
                            RequiredNotPassCount = g.Count(x => x.RequiredNotPass),
                            RequiredNotPassCourseList = g.Where(x => x.RequiredNotPass).Select(x => x.CourseName).ToList(),
                            OptionalPassCount = g.Count(x => x.OptionalCoursePass) + g.Count(x => x.FreeSelectionCoursePass),
                            OptionalNotPassCount = g.Count(x => x.OptionalCourseNotPass) + g.Count(x => x.FreeSelectionCourseNotPass),
                            TotalCredit = g.Sum(x => x.ScoreCredit)
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的学生全部最终成绩信息GraduationScoreTypeCountView（根据学生信息并且区分必修、选修）
        /// 注：内连课程类型设置表(匹配执行计划)
        /// 包含俱乐部课程
        /// </summary>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<GraduationScoreTypeCountView> GetFinallyScoreTypeExecutablePlanViewQueryable(Expression<Func<CF_Student, bool>> expStudent)
        {
            var finallyScoreExecutablePlanViewList = from finsco in GetFinallyScoreExecutablePlanViewQueryable(expStudent)
                                                     join cs in CourseTypeSelectCourseTypeRepository.Entities
                                                     on finsco.CourseTypeID equals cs.CourseTypeID into tempcs
                                                     from couset in tempcs.DefaultIfEmpty()
                                                     select new
                                                     {
                                                         UserID = finsco.UserID,
                                                         CoursematerialID = finsco.CoursematerialID,
                                                         CourseCode = finsco.CourseCode,
                                                         CourseName = finsco.CourseName,
                                                         CourseTypeID = finsco.CourseTypeID,
                                                         TotalScore = finsco.TotalScore ?? 0,
                                                         ScoreCredit = finsco.ScoreCredit ?? 0,
                                                         IsRequired = couset.IsRequired,
                                                         IsOptionalCourse = couset.IsOptionalCourse,
                                                         IsFreeSelectionCourse = couset.IsFreeSelectionCourse,
                                                         RequiredPass = (couset.IsRequired.Value && finsco.TotalScore >= 60) ? true : false,
                                                         RequiredNotPass = (couset.IsRequired.Value && finsco.TotalScore < 60) ? true : false,
                                                         OptionalCoursePass = (couset.IsOptionalCourse.Value && finsco.TotalScore >= 60) ? true : false,
                                                         OptionalCourseNotPass = (couset.IsOptionalCourse.Value && finsco.TotalScore < 60) ? true : false,
                                                         FreeSelectionCoursePass = (couset.IsFreeSelectionCourse.Value && finsco.TotalScore >= 60) ? true : false,
                                                         FreeSelectionCourseNotPass = (couset.IsFreeSelectionCourse.Value && finsco.TotalScore < 60) ? true : false
                                                     };

            //学生最终成绩统计(分必修、选修)--必修及格门数、必修不及格门数、选修及格门数、选修不及格门数、已修学分
            var query = from finsco in finallyScoreExecutablePlanViewList
                        group finsco by finsco.UserID into g
                        select new GraduationScoreTypeCountView
                        {
                            UserID = g.Key.Value,
                            ExecCourseCount = g.Count(),
                            RequiredPassCount = g.Count(x => x.RequiredPass),
                            RequiredNotPassCount = g.Count(x => x.RequiredNotPass),
                            RequiredNotPassCourseList = g.Where(x => x.RequiredNotPass).Select(x => x.CourseName).ToList(),
                            OptionalPassCount = g.Count(x => x.OptionalCoursePass) + g.Count(x => x.FreeSelectionCoursePass),
                            OptionalNotPassCount = g.Count(x => x.OptionalCourseNotPass) + g.Count(x => x.FreeSelectionCourseNotPass),
                            TotalCredit = g.Sum(x => x.ScoreCredit)
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的毕业学生成绩信息StudentScoreView（根据学生信息）
        /// 注：同一学生成绩库中，相同的课程信息只取课程类型最大的学分（只取一门课程，用户ID、课程信息ID分组）
        /// </summary>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<StudentScoreView> GetGradStudentScoreViewQueryable(Expression<Func<CF_Student, bool>> expStudent)
        {
            var lastScoreList = from stusco in this.GetFinallyScoreViewQueryable(expStudent)
                                join couset in CourseTypeSelectCourseTypeRepository.Entities
                                on stusco.CourseTypeID equals couset.CourseTypeID
                                into tempcouset
                                from stucouset in tempcouset.DefaultIfEmpty()
                                join grsco in
                                    (
                                        from finsco in this.GetFinallyScoreViewQueryable(expStudent)
                                        join set in CourseTypeSelectCourseTypeRepository.Entities
                                        on finsco.CourseTypeID equals set.CourseTypeID
                                        into tempset
                                        from couset in tempset.DefaultIfEmpty()
                                        group new { Score = finsco, SelectCourseType = couset }
                                        by new { finsco.UserID, finsco.CoursematerialID }
                                            into g
                                            select new
                                            {
                                                g.Key.UserID,
                                                g.Key.CoursematerialID,
                                                IsRequired = g.Max(x => x.SelectCourseType.IsRequired.Value ? 1 : 0) == 1,
                                                MaxCreateTime = (g.Max(x => x.SelectCourseType.IsRequired.Value ? 1 : 0) == 1)
                                                    ? g.Max(x => (x.SelectCourseType.IsRequired.Value) ? x.Score.CreateTime : null)
                                                    : g.Max(x => x.Score.CreateTime)
                                            }
                                    )
                                on
                                new { UserID = stusco.UserID, CoursematerialID = stusco.CoursematerialID, IsRequired = stucouset.IsRequired.Value, CreateTime = stusco.CreateTime }
                                equals
                                new { UserID = grsco.UserID, CoursematerialID = grsco.CoursematerialID, IsRequired = grsco.IsRequired, CreateTime = grsco.MaxCreateTime }
                                group stusco by new { stusco.UserID, stusco.CoursematerialID, stusco.StarttermID, stusco.CreateTime }
                                    into g
                                    select g.Max(x => x.FinallyScoreID.ToString());

            var query = from finsco in this.GetFinallyScoreViewQueryable(expStudent)
                        join lsl in lastScoreList
                        on finsco.FinallyScoreID.ToString() equals lsl
                        select finsco;

            return query;
        }

        /// <summary>
        /// 查询对应的毕业学生成绩学分信息PersonalCreditView（根据学生信息）
        /// 注：同一学生成绩库中，相同的课程信息只取课程类型最大的学分（只取一门课程，用户ID、课程信息ID分组）
        /// </summary>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<PersonalCreditView> GetGradStudentScoreCreditViewQueryable(Expression<Func<CF_Student, bool>> expStudent)
        {
            //学分统计
            var stuCreditList = from stusco in this.GetGradStudentScoreViewQueryable(expStudent)
                                join couset in CourseTypeSelectCourseTypeRepository.Entities
                                on stusco.CourseTypeID equals couset.CourseTypeID
                                into tempcouset
                                from stucouset in tempcouset.DefaultIfEmpty()
                                join grset in GraduationCourseTypeSettingRepository.Entities
                                on stusco.CourseTypeID equals grset.CourseTypeID
                                into tempgrset
                                from stugrset in tempgrset.DefaultIfEmpty()
                                join club in ClubCourseRepository.Entities
                                on stusco.CoursematerialID equals club.CoursematerialID
                                into tempclub
                                from stuclub in tempclub.DefaultIfEmpty()
                                group new { stusco, stucouset, stugrset, stuclub } by stusco.UserID into g
                                select new
                                {
                                    UserID = g.Key,
                                    //必修学分
                                    RequiredCredit = g.Sum(x => (x.stucouset.IsRequired.Value ? (x.stusco.ScoreCredit ?? 0) : 0)),
                                    //限选学分
                                    OptionalCredit = g.Sum(x => (x.stucouset.IsOptionalCourse.Value ? (x.stusco.ScoreCredit ?? 0) : 0)),
                                    //任选学分
                                    FreeSelectionCredit = g.Sum(x => (x.stucouset.IsFreeSelectionCourse.Value ? (x.stusco.ScoreCredit ?? 0) : 0)),
                                    //方向选修学分
                                    ElectiveCredit = g.Sum(x => (x.stugrset.IsElective.Value ? (x.stusco.ScoreCredit ?? 0) : 0)),
                                    //公共选修学分
                                    ElectivePublicCredit = g.Sum(x => (x.stugrset.IsElectivePublic.Value ? (x.stusco.ScoreCredit ?? 0) : 0)),
                                    //通识教育学分
                                    GeneralEducationCredit = g.Sum(x => (x.stugrset.IsGeneralEducation.Value ? (x.stusco.ScoreCredit ?? 0) : 0)),
                                    //校本学分（不包含俱乐部）
                                    SchoolbasedNoClubCredit = g.Sum(x => ((x.stugrset.IsSchoolbasedCurriculum.Value && x.stuclub.ClubCourseID == null) ? (x.stusco.ScoreCredit ?? 0) : 0)),
                                    //校本学分（只包含俱乐部）
                                    SchoolbasedClubCredit = g.Sum(x => ((x.stugrset.IsSchoolbasedCurriculum.Value && x.stuclub.ClubCourseID != null) ? (x.stusco.ScoreCredit ?? 0) : 0)),
                                    //实践环节学分（不包含俱乐部）
                                    PractiseNoClubCredit = g.Sum(x => ((x.stugrset.IsPractise.Value && x.stuclub.ClubCourseID == null) ? (x.stusco.ScoreCredit ?? 0) : 0)),
                                    //实践环节学分（只包含俱乐部）
                                    PractiseClubCredit = g.Sum(x => ((x.stugrset.IsPractise.Value && x.stuclub.ClubCourseID != null) ? (x.stusco.ScoreCredit ?? 0) : 0))
                                };

            var query = from stu in this.GetBaseStudentViewQueryable(expStudent)
                        join scl in stuCreditList
                        on stu.UserID equals scl.UserID into tempscl
                        from stuscl in tempscl.DefaultIfEmpty()
                        select new PersonalCreditView
                        {
                            UserID = stu.UserID,
                            StudentNo = stu.LoginID,
                            UserName = stu.UserName,
                            SexID = stu.SexID,
                            InSchoolStatusID = stu.InSchoolStatusID,
                            StudentStatusID = stu.StudentStatus,
                            ClassID = stu.ClassmajorID,
                            ClassNo = stu.ClassmajorCode,
                            ClassName = stu.ClassmajorName,
                            GrademajorID = stu.GrademajorID,
                            GrademajorCode = stu.GrademajorCode,
                            GrademajorName = stu.GrademajorName,
                            FacultymajorID = stu.FacultymajorID,
                            FacultymajorNo = stu.FacultymajorCode,
                            FacultymajorName = stu.FacultymajorName,
                            StandardID = stu.StandardID,
                            CollegeID = stu.CollegeID,
                            CollegeNo = stu.CollegeNo,
                            CollegeName = stu.CollegeName,
                            RequiredCredit = stuscl.RequiredCredit == null ? 0 : stuscl.RequiredCredit,
                            OptionalCourseCredit = stuscl.OptionalCredit == null ? 0 : stuscl.OptionalCredit,
                            FreeSelectionCourseCredit = stuscl.FreeSelectionCredit == null ? 0 : stuscl.FreeSelectionCredit,
                            ElectiveCredit = stuscl.ElectiveCredit == null ? 0 : stuscl.ElectiveCredit,
                            ElectivePublicCredit = stuscl.ElectivePublicCredit == null ? 0 : stuscl.ElectivePublicCredit,
                            GeneralEducationCredit = stuscl.GeneralEducationCredit == null ? 0 : stuscl.GeneralEducationCredit,
                            SchoolbasedNoClubCredit = stuscl.SchoolbasedNoClubCredit == null ? 0 : stuscl.SchoolbasedNoClubCredit,
                            SchoolbasedClubCredit = stuscl.SchoolbasedClubCredit == null ? 0 : stuscl.SchoolbasedClubCredit,
                            PractiseNoClubCredit = stuscl.PractiseNoClubCredit == null ? 0 : stuscl.PractiseNoClubCredit,
                            PractiseClubCredit = stuscl.PractiseClubCredit == null ? 0 : stuscl.PractiseClubCredit
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的毕业设计课程成绩信息StudentScoreView（根据学生信息）
        /// 注：根据毕业设计课程设置信息表中课程信息、课程类型进行内联（学分、学时暂时不考虑）
        /// </summary>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<GraduateDesignCourseScoreView> GetGraduateDesignCourseViewQueryable(Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from finsco in FinallyScoreRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                        join gradc in GraduateDesignCoursematerialRepository.Entities
                        on new { CoursematerialID = finsco.CoursematerialID, CourseTypeID = finsco.CourseTypeID }
                        equals
                        new { CoursematerialID = gradc.CoursematerialID, CourseTypeID = gradc.CourseTypeID }
                        join stu in this.GetBaseStudentViewQueryable(expStudent)
                        on finsco.UserID equals stu.UserID
                        join sy in SchoolyearRepository.Entities
                        on finsco.SchoolyearID equals sy.SchoolyearID
                        join cou in CoursematerialRepository.Entities
                        on finsco.CoursematerialID equals cou.CoursematerialID
                        select new GraduateDesignCourseScoreView
                        {
                            FinallyScoreID = finsco.FinallyScoreID,
                            SchoolyearID = finsco.SchoolyearID,
                            SchoolyearCode = sy.Code,
                            UserID = finsco.UserID,
                            LoginID = stu.LoginID,
                            UserName = stu.UserName,
                            ClassmajorID = stu.ClassmajorID,
                            ClassmajorCode = stu.ClassmajorCode,
                            ClassmajorName = stu.ClassmajorName,
                            GrademajorID = stu.GrademajorID,
                            GrademajorCode = stu.GrademajorCode,
                            GrademajorName = stu.GrademajorName,
                            CoursematerialID = finsco.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            CourseTypeID = finsco.CourseTypeID,
                            Credit = finsco.Credit,
                            Totalhours = finsco.TotalHours,
                            ExamsCategoryID = finsco.ExamsCategoryID,
                            ExamsStateID = finsco.ExamsStateID,
                            TotalScore = finsco.TotalScore,
                            ScoreCredit = finsco.ScoreCredit,
                            SchoolyearNumID = finsco.SchoolyearNumID,
                            StarttermID = finsco.StarttermID,
                            ResultTypeID = finsco.ResultTypeID,
                            CreateUserID = finsco.CreateUserID,
                            CreateTime = finsco.CreateTime
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的毕业课程信息GraduationCourseView（根据学生信息）
        /// </summary>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<GraduationCourseView> GetGraduationCourseViewQueryable(Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from gc in GraduationCourseRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                        join stu in this.GetBaseStudentViewQueryable(expStudent)
                        on gc.GrademajorID equals stu.GrademajorID
                        join cou in CoursematerialRepository.Entities
                        on gc.CoursematerialID equals cou.CoursematerialID
                        select new GraduationCourseView
                        {
                            GraduationCourseID = gc.GraduationCourseID,
                            GrademajorID = stu.GrademajorID,
                            GrademajorCode = stu.GrademajorCode,
                            GrademajorName = stu.GrademajorName,
                            SchoolyearID = stu.SchoolyearID,
                            SchoolcodeID = stu.SchoolcodeID,
                            CoursematerialID = gc.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            CourseTypeID = gc.CourseTypeID,
                            CourseCredit = gc.CourseCredit
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的类型(课程类型)学分信息CourseTypeRequirementView（根据学生信息）
        /// </summary>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<CourseTypeRequirementView> GetGradCourseTypeRequirementViewQueryable(Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from gct in GraduationCourseTypeRequirementRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                        join stu in this.GetBaseStudentViewQueryable(expStudent)
                        on gct.GrademajorID equals stu.GrademajorID
                        join cs in CourseTypeSelectCourseTypeRepository.Entities
                        on gct.CourseTypeID equals cs.CourseTypeID
                        into tempcs
                        from couset in tempcs.DefaultIfEmpty()
                        join gs in GraduationCourseTypeSettingRepository.Entities
                        on gct.CourseTypeID equals gs.CourseTypeID
                        into tempgs
                        from grset in tempgs.DefaultIfEmpty()
                        select new CourseTypeRequirementView
                        {
                            GraduationCourseTypeRequirementID = gct.GraduationCourseTypeRequirementID,
                            GrademajorID = gct.GrademajorID,
                            GrademajorCode = stu.GrademajorCode,
                            GrademajorName = stu.GrademajorName,
                            SchoolyearID = stu.SchoolyearID,
                            SchoolcodeID = stu.SchoolcodeID,
                            CourseTypeID = gct.CourseTypeID,
                            IsRequired = couset.IsRequired,
                            IsOptionalCourse = couset.IsOptionalCourse,
                            IsFreeSelectionCourse = couset.IsFreeSelectionCourse,
                            IsElective = grset.IsElective,
                            IsElectivePublic = grset.IsElectivePublic,
                            IsGeneralEducation = grset.IsGeneralEducation,
                            IsSchoolbasedCurriculum = grset.IsSchoolbasedCurriculum,
                            IsPractise = grset.IsPractise,
                            TotalCredit = gct.TotalCredit
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的毕业学生课程类型学分信息PersonalCourseTypeCreditView（根据学生信息，用户ID、课程类型分组）
        /// </summary>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<PersonalCourseTypeCreditView> GetGradStudentCourseTypeCreditViewQueryable(Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from stusco in this.GetGradStudentScoreViewQueryable(expStudent)
                        join cs in CourseTypeSelectCourseTypeRepository.Entities
                        on stusco.CourseTypeID equals cs.CourseTypeID
                        into tempcs
                        from couset in tempcs.DefaultIfEmpty()
                        join gs in GraduationCourseTypeSettingRepository.Entities
                        on stusco.CourseTypeID equals gs.CourseTypeID
                        into tempgs
                        from grset in tempgs.DefaultIfEmpty()
                        group new { ss = stusco, cs = couset, gs = grset }
                        by new { stusco.UserID, stusco.GrademajorID, stusco.CourseTypeID } into g
                        select new PersonalCourseTypeCreditView
                        {
                            UserID = g.Key.UserID,
                            GrademajorID = g.Key.GrademajorID,
                            CourseTypeID = g.Key.CourseTypeID,
                            IsRequired = g.Max(x => x.cs.IsRequired.Value ? 1 : 0) == 1,
                            IsOptionalCourse = g.Max(x => x.cs.IsOptionalCourse.Value ? 1 : 0) == 1,
                            IsFreeSelectionCourse = g.Max(x => x.cs.IsFreeSelectionCourse.Value ? 1 : 0) == 1,
                            IsElective = g.Max(x => x.gs.IsElective.Value ? 1 : 0) == 1,
                            IsElectivePublic = g.Max(x => x.gs.IsElectivePublic.Value ? 1 : 0) == 1,
                            IsGeneralEducation = g.Max(x => x.gs.IsGeneralEducation.Value ? 1 : 0) == 1,
                            IsSchoolbasedCurriculum = g.Max(x => x.gs.IsSchoolbasedCurriculum.Value ? 1 : 0) == 1,
                            IsPractise = g.Max(x => x.gs.IsPractise.Value ? 1 : 0) == 1,
                            TotalCredit = g.Sum(x => x.ss.ScoreCredit)
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的毕业专业学分信息GrademajorRequirementView
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<GrademajorRequirementView> GetGradGrademajorRequirementViewQueryable(Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from gsr in GraduationRequirementRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                        join stu in this.GetBaseStudentViewQueryable(expStudent)
                        on gsr.GrademajorID equals stu.GrademajorID
                        select new GrademajorRequirementView
                        {
                            GraduationRequirementID = gsr.GraduationRequirementID,
                            GrademajorID = gsr.GrademajorID,
                            GrademajorCode = stu.GrademajorCode,
                            GrademajorName = stu.GrademajorName,
                            SchoolyearID = stu.SchoolyearID,
                            SchoolcodeID = stu.SchoolcodeID,
                            OptionalCourseCredit = gsr.OptionalCourseCredit,
                            RequiredCredit = gsr.RequiredCredit,
                            TotalCredit = gsr.TotalCredit
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的毕业标准信息GraduationStandardView
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<GraduationStandardView> GetGraduationStandardViewQueryable(Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from gs in GraduationStandardRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                        join stu in this.GetBaseStudentViewQueryable(expStudent)
                        on gs.GrademajorID equals stu.GrademajorID
                        join execnum in
                            (
                                //执行计划门数-执行门数
                                from exec in ExecutablePlanRepository.Entities
                                group exec by exec.GrademajorID into g
                                select new
                                {
                                    GrademajorID = g.Key,
                                    ExecCourseCount = g.Count()
                                }
                            )
                        on stu.GrademajorID equals execnum.GrademajorID
                        into tempexecnum
                        from grexecnum in tempexecnum.DefaultIfEmpty()
                        join execcredit in
                            (
                                //执行计划总学分-执行学分
                                from exec in ExecutablePlanRepository.Entities
                                group exec by exec.GrademajorID into g
                                select new
                                {
                                    GrademajorID = g.Key,
                                    ExecutableCreditTotal = g.Sum(x => x.EM_ExecutablePlanTeachingSetting.Credit.Value)
                                }
                            )
                        on stu.GrademajorID equals execcredit.GrademajorID
                        into tempexeccredit
                        from grexeccredit in tempexeccredit.DefaultIfEmpty()
                        select new GraduationStandardView
                        {
                            GraduationStandardID = gs.GraduationStandardID,
                            GrademajorID = gs.GrademajorID,
                            GrademajorCode = stu.GrademajorCode,
                            GrademajorName = stu.GrademajorName,
                            SchoolyearID = stu.SchoolyearID,
                            SchoolcodeID = stu.SchoolcodeID,
                            ExecCourseCount = grexecnum.ExecCourseCount == null ? 0 : grexecnum.ExecCourseCount,
                            ExecutableCreditTotal = grexeccredit.ExecutableCreditTotal == null ? 0 : grexeccredit.ExecutableCreditTotal,
                            RequireCourseCount = gs.RequireCourseCount,
                            GraduationCredit = gs.GraduationCredit
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的学生欠费信息StudentArrearsView（根据学生信息）
        /// 注：目前欠费金额的计算不包括通过缓交申请的缓交金额
        /// </summary>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<StudentArrearsView> GetStudentArrearViewQueryable(Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from gr in
                            (
                                from stu in StudentRepository.GetList(expStudent)
                                join sc in StudentChargeRepository.Entities
                                on stu.UserID equals sc.UserID
                                join stucp in
                                    (
                                            from cp in StudentChargePaymentRepository.Entities
                                            group cp by cp.StudentChargeID into g
                                            select new
                                            {
                                                StudentChargeID = g.Key,
                                                PaymentAmount = g.Sum(x => x.Amount)
                                            }
                                    )
                                on sc.StudentChargeID equals stucp.StudentChargeID
                                into tempcp
                                from schcp in tempcp.DefaultIfEmpty()
                                select new
                                {
                                    StudentChargeID = sc.StudentChargeID,
                                    UserID = stu.UserID,
                                    ChargeYear = sc.ChargeYear,
                                    ChargeProjectID = sc.ChargeProjectID,
                                    Amount = sc.Amount,
                                    ChargeAmount = sc.ActualAmount - sc.Amount,
                                    ActualAmount = sc.ActualAmount,
                                    PaymentAmount = schcp.PaymentAmount ?? 0
                                }
                            )
                        group gr by gr.UserID into g
                        select new StudentArrearsView
                        {
                            UserID = g.Key,
                            Amount = g.Sum(x => x.Amount),
                            ChargeAmount = g.Sum(x => x.ChargeAmount),
                            ActualAmount = g.Sum(x => x.ActualAmount),
                            PaymentAmount = g.Sum(x => x.PaymentAmount),
                            AmountSum = (g.Sum(x => x.ActualAmount) ?? 0) - g.Sum(x => x.PaymentAmount)
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的学生欠费信息ArrearsListView（根据学生信息）
        /// 注：目前欠费金额的计算不包括通过缓交申请的缓交金额
        /// </summary>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<ArrearsListView> GetArrearsListViewQueryable(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 fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        join us in UserRepository.Entities
                        on stu.UserID equals us.UserID
                        join pass in PassStudentRepository.Entities
                        on stu.UserID equals pass.UserID
                        into temppass
                        from stuPass in temppass.DefaultIfEmpty()
                        join stugr in this.GetStudentArrearViewQueryable(expStudent)
                        on stu.UserID equals stugr.UserID
                        into tempstuch
                        from grstuch in tempstuch.DefaultIfEmpty()
                        select new ArrearsListView
                        {
                            UserID = stu.UserID,
                            StudentNo = us.LoginID,
                            UserName = us.Name,
                            SexID = stu.SexID,
                            InSchoolStatus = stu.InSchoolStatusID,
                            StudentStatus = stu.StudentStatus,
                            ClassID = stu.ClassmajorID,
                            ClassNo = cl.No,
                            ClassName = cl.Name,
                            GradeMajorID = cl.GrademajorID,
                            GradeMajorCode = gr.Code,
                            GrademajorStr = gr.Name,
                            GradeStr = gr.GradeID,
                            CollegeID = fa.CollegeID,
                            CollegeCode = fa.CF_College.No,
                            CollegeStr = fa.CF_College.Name,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            Amount = grstuch.Amount ?? 0,
                            ChargeAmount = grstuch.ChargeAmount ?? 0,
                            ActualAmount = grstuch.ActualAmount ?? 0,
                            PaidAmount = grstuch.PaymentAmount ?? 0,
                            IsArrear = (grstuch.PaymentAmount - (grstuch.ActualAmount ?? 0)) >= 0 ? 0 : 1,
                            AmountSum = grstuch.AmountSum ?? 0,
                            IsControl = stuPass.UserID == null ? 1 : 0
                        };

            return query;
        }

    }
}