JW/EMiS.DataLogic/Common/Students/StudentsDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Bowin.Common.Linq;
using EMIS.Entities;
using EMIS.DataLogic.Repositories;
using EMIS.ViewModel;
using EMIS.ViewModel.SystemView;
using System.Linq.Expressions;
using EMIS.ViewModel.Students;
using System.Data.Entity;
using EMIS.DataLogic.SystemDAL;

namespace EMIS.DataLogic.Common.Students
{
    public class StudentsDAL
    {
        public StudentRepository StudentRepository { get; set; }
        public DictionaryDAL DictionaryDAL { get; set; }
        public UserRepository UserRepository { get; set; }
        public EducationMissionRepository EducationMissionRepository { get; set; }//教学任务班主表
        public EducationMissionClassRepository EducationMissionClassRepository { get; set; }
        public EducationSchedulingClassRepository EducationSchedulingClassRepository { get; set; }
        public ExaminationPlanRepository ExaminationPlanRepository { get; set; }//考场安排
        public FinalExaminationRepository FinalExaminationRepository { get; set; } //期末设定
        public EvaluationSettingRepository EvaluationSettingRepository { get; set; }//教学评价
        public SpecialtyPlanRepository SpecialtyPlanRepository { get; set; }
        public StudentsOrderRepository StudentsOrderRepository { get; set; }
        public StudentDistributeRepository StudentDistributeRepository { get; set; }
        //public StudentProfileRepository StudentProfileRepository { get; set; }
        //public StudentContactRepository StudentContactRepository { get; set; }
        //public RecruitstudentsRepository RecruitstudentsRepository { get; set; }
        //public StudentAccountRepository StudentAccountRepository { get; set; }
        public InSchoolSettingRepository InSchoolSettingRepository { get; set; }
        public RecruitstudentsRepository RecruitstudentsRepository { get; set; }
        public StudentProfileRepository StudentProfileRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public StudentReportRepository StudentReportRepository { get; set; }
        //public StudentAccountRepository StudentAccountRepository { get; set; }
        //public StudentContactRepository StudentContactRepository { get; set; }
        public UniversityRepository universityRepository { get; set; }
        public UniversityProfileRepository universityProfileRepository { get; set; }

        /// <summary>
        /// 获取当前学年学期所有学生信息
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public IQueryable<StudentsView> GetStudentQueryable(Expression<Func<CF_Student, bool>> filter,
            Expression<Func<CF_Classmajor, bool>> classmajorExp = null,
            Expression<Func<CF_Grademajor, bool>> grademajorExp = null,
            Expression<Func<EM_StudentReport, bool>> reportExp = null)
        {
            if (classmajorExp == null) 
            {
                classmajorExp = (x => true);
            }
            if (grademajorExp == null)
            {
                grademajorExp = (x => true);
            }
            if (reportExp==null)
            {
                reportExp = (x => true);
            }
            var query = from c in StudentRepository.GetList(filter)
                        join classmajor in ClassmajorRepository.GetList(classmajorExp) on c.ClassmajorID equals classmajor.ClassmajorID 
                        join grade in GrademajorRepository.GetList(grademajorExp) on classmajor.GrademajorID equals grade.GrademajorID 
                        join studentProfile in StudentProfileRepository.Entities on c.UserID equals studentProfile.UserID
                        into cstudentProfile
                        from tcstudentProfile in cstudentProfile.DefaultIfEmpty()
                        join recruitStudent in RecruitstudentsRepository.Entities on c.UserID equals recruitStudent.UserID
                        into crecruitStudent
                        from tcrecruitStudent in crecruitStudent.DefaultIfEmpty()
                        from b in c.EM_StudentReport.AsQueryable().Where(w => w.CF_Schoolyear.IsCurrent == true).DefaultIfEmpty() //当前学年学期的报到情况
                        select new StudentsView
                        {
                            UserID = c.UserID,
                            LoginID = c.Sys_User.LoginID,
                            UserName = c.Sys_User.Name,
                            IDNumber = c.IDNumber,
                            PhotoUrl = c.PhotoUrl,
                            IsProofread = c.IsProofread,
                            IsPhoto = c.PhotoUrl == null ? (int)CF_YesOrNoStatus.No : (int)CF_YesOrNoStatus.Yes,
                            IsPhotoUrl = c.PhotoUrl == null ? "否" : "是",
                            StudentCardNo = c.StudentCardNo,
                            PlanningGraduateDate = c.PlanningGraduateDate,//预计毕业时间
                            GraduateDate = c.GraduateDate,
                            EntranceDate = c.CF_Recruitstudents.EntranceDate,
                            EnteringSchoolYearID = c.CF_Recruitstudents.EnteringSchoolYearID,
                            EnteringSchoolYearCode = c.CF_Recruitstudents.CF_Schoolyear.Code,
                            Score = c.CF_Recruitstudents.Score,
                            ExamineeNum = c.CF_Recruitstudents.ExamineeNum,
                            AdmissionTicketNo = c.CF_Recruitstudents.AdmissionTicketNo,
                            Area = c.CF_Recruitstudents.Area,
                            Placebirth = c.CF_StudentProfile.BornPlace,
                            CardNo = c.CF_StudentAccount.CardNo,
                            BankName = c.CF_StudentAccount.BankName,
                            Territorial = tcrecruitStudent.TerritorialID,
                            SexID = c.SexID,
                            ClassMajorID = c.ClassmajorID,
                            ClassMajorCode = classmajor.No,
                            ClassMajorName = classmajor.Name,
                            Grade = grade.GradeID,
                            StudentStatus = c.StudentStatus,
                            InSchoolStatusID = c.InSchoolStatusID,
                            ReportStatusID = b.ReportStatus ?? 1,
                            UsedName = c.CF_StudentProfile.UsedName,
                            DirectorName = c.CF_StudentProfile.DirectorName,
                            BirthDate = c.BirthDate,
                            Country = c.CF_StudentProfile.Country,
                            Politics = c.PoliticsID,
                            Place = c.CF_StudentProfile.Place,
                            //BornPlace = c.CF_StudentProfile.BornPlace,
                            Specialty = c.CF_StudentProfile.Specialty,
                            Height = c.CF_StudentProfile.Height,
                            Weight = c.CF_StudentProfile.Weight,
                            Remarks = c.Remark,
                            GraduationPictureUrl = c.GraduationPictureUrl,
                            Email = c.CF_StudentContact.Email,
                            QQ = c.CF_StudentContact.QQ,
                            Mobile = c.CF_StudentContact.Mobile,
                            Telephone = c.CF_StudentContact.Telephone,
                            MicroMsgNo = c.CF_StudentContact.WeChatNum,
                            ZipCode = c.CF_StudentContact.ZIPCode,
                            Address = c.CF_StudentContact.Address,
                            WorkUnit = c.CF_StudentContact.WorkUnit,
                            HomeAddress = c.CF_StudentContact.HomeAddress,
                            Recipient = c.CF_StudentContact.Recipient,
                            Dormitory = c.CF_StudentContact.Dormitory,
                            LearnSystem = grade.CF_Facultymajor.LearnSystem,
                            CampusID = grade.CF_Facultymajor.CF_College.CampusID,
                            SchoolTypeID = grade.CF_Facultymajor.CF_College.CF_Campus.CF_University.CF_UniversityProfile.SchoolTypeID,
                            CampusName = grade.CF_Facultymajor.CF_College.CF_Campus.Name,
                            CollegeID = grade.CF_Facultymajor.CollegeID,
                            CollegeCode = grade.CF_Facultymajor.CF_College.No,
                            CollegeName = grade.CF_Facultymajor.CF_College.Name,
                            EducationID = grade.CF_Facultymajor.EducationID,
                            StandardID = grade.CF_Facultymajor.StandardID,
                            LearningformID = grade.CF_Facultymajor.LearningformID,
                            GradeMajorID = classmajor.GrademajorID,
                            GradeMajorName = grade.Name,
                            GradeMajorCode = grade.Code,
                            StudentType = c.StudentType,
                            CultureModel = c.CF_StudentProfile.CultureModelID,
                            BloodGroup = tcstudentProfile.BloodGroup,
                            CertificatesType = c.CertificatesType,
                            EntranceWay = tcrecruitStudent.EntranceWayID,
                            ExamineeType = tcrecruitStudent.ExamineeType,
                            Features = tcrecruitStudent.FeaturesID,
                            Healthy = tcstudentProfile.HealthStateID,
                            Nation = c.NationID,
                            FacultymajorName = grade.CF_Facultymajor.Name,//院系所专业
                            GraduateCardNo=c.GraduateCardNo,
                            IsDreamProject = c.CF_StudentProfile.IsDreamProject == true,
                            Career=c.Career,
                            ReplaceGraduateNo=c.ReplaceGraduateNo,
                            LiteracyLevels = c.CF_StudentProfile.LiteracyLevelID,
                            GraduationResult = c.InSchoolStatusID,
                        };
            return query;
        }

        public IQueryable<BaseStudentView> GetBaseStudentViewQueryable(Expression<Func<CF_Student, bool>> filter)
        {
            var query = from c in StudentRepository.GetList(filter)
                        join studentProfile in StudentProfileRepository.Entities on c.UserID equals studentProfile.UserID
                        join recruitStudent in RecruitstudentsRepository.Entities on c.UserID equals recruitStudent.UserID
                        join StudentReport in StudentReportRepository.Entities.Where(w=>w.CF_Schoolyear.IsCurrent==true).DefaultIfEmpty() on c.UserID equals StudentReport.UserID into stur
                        from stu in stur.DefaultIfEmpty()
                        //from b in c.EM_StudentReport.AsQueryable().Where(w => w.CF_Schoolyear.IsCurrent == true).DefaultIfEmpty() //当前学年学期的报到情况
                        select new BaseStudentView
                        {
                            UserID = c.UserID,
                            LoginID = c.Sys_User.LoginID,
                            UserName = c.Sys_User.Name,
                            SexID = c.SexID,
                            IDNumber = c.IDNumber,
                            InSchoolStatusID = c.InSchoolStatusID,
                            StudentStatus = c.StudentStatus,
                            ClassmajorID = c.CF_Classmajor.ClassmajorID,
                            ClassmajorCode = c.CF_Classmajor.No,
                            ClassmajorName = c.CF_Classmajor.Name,
                            GrademajorID = c.CF_Classmajor.GrademajorID,
                            GrademajorName = c.CF_Classmajor.CF_Grademajor.Name,
                            CollegeID = c.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CollegeID
                           // GraduateCardNo=c.GraduateCardNo
                        };
            return query;
        }

        public IQueryable<BaseStudentView> GetInSchoolStudentViewQueryable(Expression<Func<CF_Student, bool>> filter)
        {
            var query = from c in StudentRepository.GetList(filter)
                        //from b in c.EM_StudentReport.AsQueryable().Where(w => w.CF_Schoolyear.IsCurrent == true).DefaultIfEmpty() //当前学年学期的报到情况
                        select new BaseStudentView
                        {
                            UserID = c.UserID,
                            LoginID = c.Sys_User.LoginID,
                            UserName = c.Sys_User.Name,
                            SexID = c.SexID,
                            IDNumber = c.IDNumber,
                            InSchoolStatusID = c.InSchoolStatusID,
                            StudentStatus = c.StudentStatus,
                            ClassmajorID = c.CF_Classmajor.ClassmajorID,
                            ClassmajorCode = c.CF_Classmajor.No,
                            ClassmajorName = c.CF_Classmajor.Name,
                            GrademajorID = c.CF_Classmajor.GrademajorID,
                            GrademajorName = c.CF_Classmajor.CF_Grademajor.Name,
                            CollegeID = c.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CollegeID
                            // GraduateCardNo=c.GraduateCardNo
                        };
            return query;
        }

        public IQueryable<BaseStudentView> GetBaseStudentViewQueryable(Expression<Func<CF_Student, bool>> studentExp,
            Expression<Func<CF_Grademajor, bool>> gradeExp, Expression<Func<CF_Facultymajor, bool>> facultyExp)
        {
            var q = from student in this.GetBaseStudentViewQueryable(studentExp)
                    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
                    select student;

            return q;
        }

        //计算预计毕业设计时间
        public StudentsView GetPlanningGraduateDate(Guid GrademajorID)
        {
            var query = from g in GrademajorRepository.Entities.Where(x => x.GrademajorID == GrademajorID && x.GraduateSchoolyearID != null)
                        join f in FacultymajorRepository.Entities on g.FacultymajorID equals f.FacultymajorID
                        join sn in SchoolyearRepository.Entities on new { Value = (int)((g.GradeID * 2 - 1) + (g.SemesterID - 1) + (f.LearnSystem * 2 - 1)) } equals new { Value = (int)sn.Value }
                        into tsn
                        from ctsn in tsn.DefaultIfEmpty()
                        select new StudentsView { PlanningGraduateDate = (DateTime)ctsn.FirstWeek, WeeksNum = ctsn.WeeksNum * 7 };
            return query.FirstOrDefault();
        }

        //计算预计毕业设计时间 方案二 2016年10月25日14:19:25
        //public StudentsView GetPlanningGraduate(Guid GrademajorID)
        //{
        //    var query = from g in GrademajorRepository.Entities.Where(x => x.GrademajorID == GrademajorID && x.GraduatingSemesterID != null)
        //                join f in FacultymajorRepository.Entities on g.FacultymajorID equals f.FacultymajorID
        //                select new StudentsView { LearnSystem = f.LearnSystem, Years=g.SchoolyearID };
        //    return query.FirstOrDefault();
        //}
        public StudentsView GetPlanningGraduate(Guid GrademajorID)
        {
            var query = from g in GrademajorRepository.Entities.Where(x => x.GrademajorID == GrademajorID && x.GraduateSchoolyearID != null)
                        join f in FacultymajorRepository.Entities on g.FacultymajorID equals f.FacultymajorID
                        join s in SchoolyearRepository.Entities on g.GraduateSchoolyearID equals s.SchoolyearID
                        join d in DictionaryDAL.GetDictionaryItemQuery(typeof(CF_Semester).Name) on s.SchoolcodeID equals d.Value into dv
                        from d in dv.DefaultIfEmpty()
                        select new StudentsView { 
                            LearnSystem = f.LearnSystem,
                            Years = g.GradeID,
                            Grade = g.GradeID, 
                            PlanningGraduateYear=s.Years,
                            PlanningGraduateSchoolYearID=g.GraduateSchoolyearID,
                            PlanningGraduateSchoolCodeID=s.SchoolcodeID,
                            PlanningGraduateSchoolCode = d.Value,
                            PlanningGraduateSchoolCodeName = d.Name,
                        };
            return query.FirstOrDefault();
        }

        public IQueryable<StudentsView> GetPlanningGraduateDictionary()
        {
            var inSchoolStatusIDList = InSchoolSettingRepository.GetList(x => x.IsSelected == true).Select(w => w.InSchoolStatusID).ToList();//获取选中的在校设定状态、
            var query = from a in StudentRepository.GetList(x => inSchoolStatusIDList.Contains(x.InSchoolStatusID))
                        group a by a.PlanningGraduateDate
                        into b
                        select new StudentsView
                        {
                            PlanningGraduateDate = b.Key,
                            //PlanningGraduateStr = b.Key.Value,   
                        };
            return query;
        }

        /// <summary>
        /// 获取当前学年学期所有学生信息
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public IQueryable<StudentsView> GetStudentForExcel(Expression<Func<CF_Student, bool>> filter,
            Expression<Func<CF_Classmajor, bool>> classmajorExp = null,
            Expression<Func<CF_Grademajor, bool>> grademajorExp = null,
            Expression<Func<EM_StudentReport, bool>> reportExp = null)
        {
            if (classmajorExp == null)
            {
                classmajorExp = (x => true);
            }
            if (grademajorExp == null)
            {
                grademajorExp = (x => true);
            }
            if (reportExp == null)
            {
                reportExp = (x => true);
            }
            var schoolquery = from uni in universityRepository.Entities
                              join unip in universityProfileRepository.Entities
                              on uni.UniversityID equals unip.UniversityID
                              select new
                              {
                                  SchoolCode = uni.Code,
                                  SchoolName = uni.Name,
                                  HeadMasterName = unip.HeadMasterName,
                              };
            var query = from c in StudentRepository.GetList(filter)
                        join classmajor in ClassmajorRepository.GetList(classmajorExp) on c.ClassmajorID equals classmajor.ClassmajorID
                        join grade in GrademajorRepository.GetList(grademajorExp) on classmajor.GrademajorID equals grade.GrademajorID
                        join studentProfile in StudentProfileRepository.Entities on c.UserID equals studentProfile.UserID
                        into cstudentProfile
                        from tcstudentProfile in cstudentProfile.DefaultIfEmpty()
                        join recruitStudent in RecruitstudentsRepository.Entities on c.UserID equals recruitStudent.UserID
                        into crecruitStudent
                        from tcrecruitStudent in crecruitStudent.DefaultIfEmpty()
                        from b in c.EM_StudentReport.AsQueryable().Where(w => w.CF_Schoolyear.IsCurrent == true).DefaultIfEmpty() //当前学年学期的报到情况
                        select new StudentsView
                        {
                            UserID = c.UserID,
                            LoginID = c.Sys_User.LoginID,
                            UserName = c.Sys_User.Name,
                            IDNumber = c.IDNumber,
                            PhotoUrl = c.PhotoUrl,
                            IsProofread = c.IsProofread,
                            IsPhoto = c.PhotoUrl == null ? (int)CF_YesOrNoStatus.No : (int)CF_YesOrNoStatus.Yes,
                            IsPhotoUrl = c.PhotoUrl == null ? "否" : "是",
                            StudentCardNo = c.StudentCardNo,
                            PlanningGraduateDate = c.PlanningGraduateDate,//预计毕业时间
                            EntranceDate = c.CF_Recruitstudents.EntranceDate,
                            EnteringSchoolYearID = c.CF_Recruitstudents.EnteringSchoolYearID,
                            EnteringSchoolYearCode = c.CF_Recruitstudents.CF_Schoolyear.Code,
                            Score = c.CF_Recruitstudents.Score,
                            ExamineeNum = c.CF_Recruitstudents.ExamineeNum,
                            AdmissionTicketNo = c.CF_Recruitstudents.AdmissionTicketNo,
                            Area = c.CF_Recruitstudents.Area,
                            Placebirth = c.CF_StudentProfile.BornPlace,
                            CardNo = c.CF_StudentAccount.CardNo,
                            BankName = c.CF_StudentAccount.BankName,
                            Territorial = tcrecruitStudent.TerritorialID,
                            SexID = c.SexID,
                            ClassMajorID = c.ClassmajorID,
                            ClassMajorCode = classmajor.No,
                            ClassMajorName = classmajor.Name,
                            ClassNum = classmajor.ClassNum,
                            Grade = grade.GradeID,
                            StudentStatus = c.StudentStatus,
                            InSchoolStatusID = c.InSchoolStatusID,
                            ReportStatusID = b.ReportStatus ?? 1,
                            UsedName = c.CF_StudentProfile.UsedName,
                            DirectorName = c.CF_StudentProfile.DirectorName,
                            BirthDate = c.BirthDate,
                            Country = c.CF_StudentProfile.Country,
                            Politics = c.PoliticsID,
                            Place = c.CF_StudentProfile.Place,
                            //BornPlace = c.CF_StudentProfile.BornPlace,
                            Specialty = c.CF_StudentProfile.Specialty,
                            Height = c.CF_StudentProfile.Height,
                            Weight = c.CF_StudentProfile.Weight,
                            Remarks = c.Remark,
                            GraduationPictureUrl = c.GraduationPictureUrl,
                            Email = c.CF_StudentContact.Email,
                            QQ = c.CF_StudentContact.QQ,
                            Mobile = c.CF_StudentContact.Mobile,
                            Telephone = c.CF_StudentContact.Telephone,
                            MicroMsgNo = c.CF_StudentContact.WeChatNum,
                            ZipCode = c.CF_StudentContact.ZIPCode,
                            Address = c.CF_StudentContact.Address,
                            WorkUnit = c.CF_StudentContact.WorkUnit,
                            HomeAddress = c.CF_StudentContact.HomeAddress,
                            Recipient = c.CF_StudentContact.Recipient,
                            Dormitory = c.CF_StudentContact.Dormitory,
                            LearnSystem = grade.CF_Facultymajor.LearnSystem,
                            CampusID = grade.CF_Facultymajor.CF_College.CampusID,
                            SchoolTypeID = grade.CF_Facultymajor.CF_College.CF_Campus.CF_University.CF_UniversityProfile.SchoolTypeID,
                            CampusName = grade.CF_Facultymajor.CF_College.CF_Campus.Name,
                            CollegeID = grade.CF_Facultymajor.CollegeID,
                            CollegeCode = grade.CF_Facultymajor.CF_College.No,
                            CollegeName = grade.CF_Facultymajor.CF_College.Name,
                            EducationID = grade.CF_Facultymajor.EducationID,
                            StandardID = grade.CF_Facultymajor.StandardID,
                            LearningformID = grade.CF_Facultymajor.LearningformID,
                            GradeMajorID = classmajor.GrademajorID,
                            GradeMajorName = grade.Name,
                            GradeMajorCode = grade.Code,
                            StudentType = c.StudentType,
                            CultureModel = c.CF_StudentProfile.CultureModelID,
                            BloodGroup = tcstudentProfile.BloodGroup,
                            CertificatesType = c.CertificatesType,
                            EntranceWay = tcrecruitStudent.EntranceWayID,
                            ExamineeType = tcrecruitStudent.ExamineeType,
                            Features = tcrecruitStudent.FeaturesID,
                            Healthy = tcstudentProfile.HealthStateID,
                            Nation = c.NationID,
                            FacultymajorName = grade.CF_Facultymajor.Name,//院系所专业
                            GraduateCardNo = c.GraduateCardNo,
                            IsDreamProject = c.CF_StudentProfile.IsDreamProject == true,
                            Career = c.Career,
                            SchoolCode = schoolquery.FirstOrDefault().SchoolCode,
                            SchoolName = schoolquery.FirstOrDefault().SchoolName,
                            HeadMasterName = schoolquery.FirstOrDefault().HeadMasterName,
                            GraduationResult = c.InSchoolStatusID
                        };
            return query;
        }
        
        #region 5.0 教学任务Add
        //教学任务
        public IQueryable<StudentRelateCourseView> GetStudentRelateCourseViewQueryable(Expression<Func<CF_Student, bool>> filter)
        {
            var sql = (from student in StudentRepository.GetList(filter)
                       join user in UserRepository.Entities on student.UserID equals user.UserID
                       join classmajor in ClassmajorRepository.Entities on student.ClassmajorID equals classmajor.ClassmajorID
                       from currentYear in SchoolyearRepository.Entities.Where(x => x.IsCurrent == true)
                       from schoolyear in SchoolyearRepository.Entities.Where(x => x.Value >= currentYear.Value)
                       //查出相关的课程
                       join missionClass in
                           (
                               from mission in EducationMissionRepository.Entities
                               join missionClass in EducationMissionClassRepository.Entities on mission.EducationMissionID equals missionClass.EducationMissionID
                               from classmajor in missionClass.CF_Classmajor
                               join schedulingClass in EducationSchedulingClassRepository.Entities on missionClass.EducationMissionClassID equals schedulingClass.EducationMissionClassID
                               group new { missionClass, schedulingClass } 
                               by new { mission.SchoolyearID, missionClass.CoursematerialID, missionClass.TeachingModeID, classmajor.ClassmajorID, missionClass.OptionalCourseTypeID, missionClass.HandleModeID }
                                   into g
                                   select new
                                   {
                                       g.Key.SchoolyearID,
                                       g.Key.CoursematerialID,
                                       g.Key.ClassmajorID,
                                       g.Key.TeachingModeID,
                                       g.Key.OptionalCourseTypeID,
                                       g.Key.HandleModeID,
                                       SchedulingClassList = g.Select(x => new StudentRelateSchedulingClassView
                                       {
                                           EducationSchedulingClassID = x.schedulingClass.EducationSchedulingClassID,
                                           EducationMissionClassName = x.missionClass.Name,
                                           ClassmajorIDList = x.schedulingClass.CF_Student.Select(w => w.ClassmajorID),
                                           StudentCount = x.schedulingClass.CF_Student.Count(),
                                           MinLoginID = x.schedulingClass.CF_Student.Min(w => w.Sys_User.LoginID),
                                           MaxLoginID = x.schedulingClass.CF_Student.Max(w => w.Sys_User.LoginID)
                                       })
                                   }) on new { SchoolyearID = (Guid?)schoolyear.SchoolyearID, classmajor.ClassmajorID }
                                equals new { missionClass.SchoolyearID, missionClass.ClassmajorID }
                       //查已经加入的任务班并排除，防止重复插入
                       join schedulingClass in
                           (
                               from mission in EducationMissionRepository.Entities
                               join missionClass in EducationMissionClassRepository.Entities on mission.EducationMissionID equals missionClass.EducationMissionID
                               from classmajor in missionClass.CF_Classmajor
                               join schedulingClass in EducationSchedulingClassRepository.Entities on missionClass.EducationMissionClassID equals schedulingClass.EducationMissionClassID
                               from student in schedulingClass.CF_Student
                               select new { mission.SchoolyearID, missionClass.CoursematerialID, missionClass.TeachingModeID, classmajor.ClassmajorID, missionClass.OptionalCourseTypeID, missionClass.HandleModeID, student.UserID }
                               ) on new { missionClass.SchoolyearID, missionClass.CoursematerialID, missionClass.TeachingModeID, missionClass.ClassmajorID, missionClass.OptionalCourseTypeID, missionClass.HandleModeID, student.UserID }
                           equals new { schedulingClass.SchoolyearID, schedulingClass.CoursematerialID, schedulingClass.TeachingModeID, schedulingClass.ClassmajorID, schedulingClass.OptionalCourseTypeID, schedulingClass.HandleModeID, schedulingClass.UserID }
                           into dschedulingClass
                       from schedulingClass in dschedulingClass.DefaultIfEmpty()
                       where schedulingClass.SchoolyearID == null
                       select new StudentRelateCourseView
                       {
                           UserID = student.UserID,
                           LoginID = user.LoginID,
                           ClassmajorID = student.ClassmajorID,
                           SchoolyearID = missionClass.SchoolyearID,
                           CoursematerialID = missionClass.CoursematerialID,
                           TeacheModeID = missionClass.TeachingModeID,
                           OptionalCourseTypeID = missionClass.OptionalCourseTypeID,
                           HandleModeID = missionClass.HandleModeID,
                           StudentRelateSchedulingClassViewList = missionClass.SchedulingClassList
                       });

            return sql;
        }

        public IList<StudentsView> GetEducationSchedulingClassStudentInsert(Expression<Func<CF_Student, bool>> filter)
        {
            var result = new List<StudentsView>();
            var currentSchoolyear = this.SchoolyearRepository.GetSingle(x => x.IsCurrent == true);
            //这块只针对必修课，因为只有必修课才会强制必须要上，选修课需要学生自己去报名
            #region 情况1：如果找到同班学生，并且该组有学号后于他的人的，加入有学号后于他的第一组
            var query1 = from tem in
                             (from imps in StudentRepository.GetList(filter)
                              from dsy in SchoolyearRepository.Entities.Where(x => x.IsCurrent == true)
                              from sy in SchoolyearRepository.Entities.Where(x => x.Value >= dsy.Value)
                              join c in ClassmajorRepository.Entities on imps.ClassmajorID equals c.ClassmajorID
                              join u in UserRepository.Entities on imps.UserID equals u.UserID
                              //找到同班学生学号比自己后的（其实数字来说是大的）最后一个
                              from su in
                                  (from esc1 in EducationSchedulingClassRepository.Entities
                                   from su1 in esc1.CF_Student
                                   join u1 in UserRepository.Entities on su1.UserID equals u1.UserID
                                   where su1.ClassmajorID == imps.ClassmajorID 
                                   group u1 by new { esc1.EducationSchedulingClassID, su1.ClassmajorID } into g1
                                   select new
                                   {
                                       g1.Key.EducationSchedulingClassID,
                                       g1.Key.ClassmajorID,
                                       LoginID = g1.Max(x => x.LoginID)
                                   }
                              ).Where(x => u.LoginID.CompareTo(x.LoginID) < 0 && x.ClassmajorID == imps.ClassmajorID)
                              join esc in EducationSchedulingClassRepository.Entities on su.EducationSchedulingClassID equals esc.EducationSchedulingClassID 
                              join emc in EducationMissionClassRepository.Entities on esc.EducationMissionClassID equals emc.EducationMissionClassID
                              join em in EducationMissionRepository.Entities on
                                new { emc.EducationMissionID, SchoolyearID = (Guid?)sy.SchoolyearID } equals new { EducationMissionID = (Guid?)em.EducationMissionID, em.SchoolyearID }
                              where emc.HandleModeID == (int)CF_HandleMode.RequiredCourse
                              group emc by new { imps.UserID, em.SchoolyearID, c.ClassmajorID, emc.CoursematerialID, emc.TeachingModeID } into g
                              select new
                              {
                                  g.Key.UserID,
                                  g.Key.SchoolyearID,
                                  g.Key.ClassmajorID,
                                  g.Key.CoursematerialID,
                                  g.Key.TeachingModeID,
                                  MinName = g.Min(x => x.Name)
                              }
                                 )
                         join em in EducationMissionRepository.Entities on tem.SchoolyearID equals em.SchoolyearID
                         join emc in EducationMissionClassRepository.GetList(x => x.HandleModeID == (int)CF_HandleMode.RequiredCourse)
                           on new { EducationMissionID = (Guid?)em.EducationMissionID, tem.CoursematerialID, tem.TeachingModeID, Name = tem.MinName }
                           equals new { emc.EducationMissionID, emc.CoursematerialID, emc.TeachingModeID, emc.Name }
                         from c in emc.CF_Classmajor.Where(x => x.ClassmajorID == tem.ClassmajorID)
                         join esc in EducationSchedulingClassRepository.Entities on emc.EducationMissionClassID equals esc.EducationMissionClassID
                         group esc by new { tem.UserID, em.SchoolyearID, emc.CoursematerialID, emc.TeachingModeID } into g
                         select new
                         {
                             g.Key.UserID,
                             g.Key.SchoolyearID,
                             g.Key.CoursematerialID,
                             g.Key.TeachingModeID,
                             MinEducationSchedulingClassID = g.Min(x => x.EducationSchedulingClassID.ToString())
                         };
            result.AddRange(query1.ToList().Select(x => new StudentsView { TepTableID = new Guid(x.MinEducationSchedulingClassID), UserID = x.UserID }).ToList());
            #endregion

            #region 情况2：如果找到同班学生，并且没有学号后于他的人的，加入最后一组
            var query2 = from tem in
                             (from imps in StudentRepository.GetList(filter)
                              from dsy in SchoolyearRepository.Entities.Where(x => x.IsCurrent == true)
                              from sy in SchoolyearRepository.Entities.Where(x => x.Value >= dsy.Value)
                              join c in ClassmajorRepository.Entities on imps.ClassmajorID equals c.ClassmajorID
                              join u in UserRepository.Entities on imps.UserID equals u.UserID
                              from emc in c.EM_EducationMissionClass
                              from esc in emc.EM_EducationSchedulingClass
                              join em in EducationMissionRepository.Entities
                                on new { emc.EducationMissionID, SchoolyearID = (Guid?)sy.SchoolyearID } 
                              equals new { EducationMissionID = (Guid?)em.EducationMissionID, em.SchoolyearID }
                              //找到同班学生学号比自己前的（其实数字来说是小的）最后一个
                              from su in
                                  (from esc1 in EducationSchedulingClassRepository.Entities
                                   join emc1 in c.EM_EducationMissionClass.AsQueryable() on esc1.EducationMissionClassID equals emc1.EducationMissionClassID
                                   join em1 in EducationMissionRepository.Entities on emc1.EducationMissionID equals em1.EducationMissionID
                                   from su1 in esc1.CF_Student
                                   join u1 in UserRepository.Entities on su1.UserID equals u1.UserID
                                   where su1.ClassmajorID == imps.ClassmajorID && emc1.HandleModeID == (int)CF_HandleMode.RequiredCourse
                                   group u1 by new { su1.ClassmajorID, emc1.CoursematerialID, em1.SchoolyearID, emc1.OptionalCourseTypeID } into g1
                                   select new
                                   {
                                       g1.Key.ClassmajorID,
                                       g1.Key.CoursematerialID,
                                       g1.Key.SchoolyearID,
                                       g1.Key.OptionalCourseTypeID,
                                       LoginID = g1.Max(x => x.LoginID)
                                   }
                              ).Where(x => u.LoginID.CompareTo(x.LoginID) < 0 && x.ClassmajorID == c.ClassmajorID
                                && x.CoursematerialID == emc.CoursematerialID && x.OptionalCourseTypeID == emc.OptionalCourseTypeID
                                && x.SchoolyearID == em.SchoolyearID).DefaultIfEmpty()
                              where emc.HandleModeID == (int)CF_HandleMode.RequiredCourse && su.ClassmajorID == null
                              group emc by new { imps.UserID, em.SchoolyearID, c.ClassmajorID, emc.CoursematerialID, emc.TeachingModeID } into g
                              select new
                              {
                                  g.Key.UserID,
                                  g.Key.SchoolyearID,
                                  g.Key.ClassmajorID,
                                  g.Key.CoursematerialID,
                                  g.Key.TeachingModeID,
                                  MinName = g.Max(x => x.Name)
                              }
                                 )
                         join em in EducationMissionRepository.Entities on tem.SchoolyearID equals em.SchoolyearID
                         join emc in EducationMissionClassRepository.GetList(x => x.HandleModeID == (int)CF_HandleMode.RequiredCourse)
                           on new { EducationMissionID = (Guid?)em.EducationMissionID, tem.CoursematerialID, tem.TeachingModeID, Name = tem.MinName }
                           equals new { emc.EducationMissionID, emc.CoursematerialID, emc.TeachingModeID, emc.Name }
                         from c in emc.CF_Classmajor.Where(x => x.ClassmajorID == tem.ClassmajorID)
                         join esc in EducationSchedulingClassRepository.Entities on emc.EducationMissionClassID equals esc.EducationMissionClassID
                         group esc by new { tem.UserID, em.SchoolyearID, emc.CoursematerialID, emc.TeachingModeID } into g
                         select new
                         {
                             g.Key.UserID,
                             g.Key.SchoolyearID,
                             g.Key.CoursematerialID,
                             g.Key.TeachingModeID,
                             MinEducationSchedulingClassID = g.Max(x => x.EducationSchedulingClassID.ToString())
                         };
            result.AddRange(query2.ToList().Select(x => new StudentsView { TepTableID = new Guid(x.MinEducationSchedulingClassID), UserID = x.UserID }).ToList());
            #endregion

            #region 情况3：没有找到同班学生，加入最少人的第一组（这种情况通常有人的组都是另一个班的）
            var query3MateMissionClassQuery = (
                from ts1 in StudentRepository.GetList(filter)
                join tc1 in ClassmajorRepository.Entities on ts1.ClassmajorID equals tc1.ClassmajorID
                from temc1 in tc1.EM_EducationMissionClass
                join tem1 in EducationMissionRepository.Entities on temc1.EducationMissionID equals tem1.EducationMissionID
                join tsy1 in SchoolyearRepository.Entities on tem1.SchoolyearID equals tsy1.SchoolyearID
                join tesc1 in EducationSchedulingClassRepository.Entities on temc1.EducationMissionClassID equals tesc1.EducationMissionClassID
                from tms1 in tesc1.CF_Student
                where temc1.HandleModeID == (int)CF_HandleMode.RequiredCourse
                && tsy1.Value >= currentSchoolyear.Value
                group tesc1 by new { tc1.ClassmajorID, tsy1.SchoolyearID, temc1.CoursematerialID, temc1.TeachingModeID, temc1.EducationMissionClassID } into g
                select g.Key);

            var query3MissionClassQuery = (from ts in StudentRepository.GetList(filter)
                                           join tc in ClassmajorRepository.Entities on ts.ClassmajorID equals tc.ClassmajorID
                                           from temc in tc.EM_EducationMissionClass
                                           join tem in EducationMissionRepository.Entities on temc.EducationMissionID equals tem.EducationMissionID
                                           join tsy in SchoolyearRepository.Entities on tem.SchoolyearID equals tsy.SchoolyearID
                                           join memc in query3MateMissionClassQuery
                                            on new { tc.ClassmajorID, tsy.SchoolyearID, temc.CoursematerialID, temc.TeachingModeID }
                                            equals new { memc.ClassmajorID, memc.SchoolyearID, memc.CoursematerialID, memc.TeachingModeID } into dmemc 
                                           from memc in dmemc.DefaultIfEmpty() 
                                           join tesc in (
                                            from esc in EducationSchedulingClassRepository.Entities 
                                            group esc by esc.EducationMissionClassID into g 
                                            select new { EducationMissionClassID = g.Key, StudentCount = (int?)g.Sum(x => x.CF_Student.Count) }
                                               ) on temc.EducationMissionClassID equals tesc.EducationMissionClassID
                                           where temc.HandleModeID == (int)CF_HandleMode.RequiredCourse
                                           && tsy.Value >= currentSchoolyear.Value 
                                           && memc.EducationMissionClassID == null
                                           group tesc by new { tc.ClassmajorID, tsy.SchoolyearID, temc.CoursematerialID, temc.TeachingModeID } into g
                                           select new
                                           {
                                               g.Key.ClassmajorID,
                                               g.Key.SchoolyearID,
                                               g.Key.CoursematerialID,
                                               g.Key.TeachingModeID,
                                               MinStudentCount = g.Min(x => x.StudentCount ?? 0)
                                           }
                                 );
            var query3 = from dd in StudentRepository.GetList(filter)
                         join tmp in (
                            from tmp in query3MissionClassQuery
                            join em in EducationMissionRepository.Entities on tmp.SchoolyearID equals em.SchoolyearID 
                            join emc in EducationMissionClassRepository.Entities 
                                on new { EducationMissionID = (Guid?)em.EducationMissionID, tmp.CoursematerialID, tmp.TeachingModeID }
                                equals new { emc.EducationMissionID, emc.CoursematerialID, emc.TeachingModeID } 
                            from emccc in emc.CF_Classmajor.Where(x => x.ClassmajorID == tmp.ClassmajorID) 
                            join esc in EducationSchedulingClassRepository.Entities on emc.EducationMissionClassID equals esc.EducationMissionClassID 
                            join mesc in (
                                from esc in EducationSchedulingClassRepository.Entities 
                                group esc by esc.EducationMissionClassID into g 
                                select new { EducationMissionClassID = g.Key, StudentCount = (int?)g.Sum(x => x.CF_Student.Count) }
                                ) on emc.EducationMissionClassID equals mesc.EducationMissionClassID 
                            where (mesc.StudentCount ?? 0) == tmp.MinStudentCount 
                            group esc by new { tmp.ClassmajorID, tmp.SchoolyearID, tmp.CoursematerialID, tmp.TeachingModeID } into g
                            select new { 
                                g.Key.ClassmajorID, g.Key.SchoolyearID, g.Key.CoursematerialID, g.Key.TeachingModeID,
                                MinEducationSchedulingClassID = g.Min(x => x.EducationSchedulingClassID.ToString())
                            }
                             ) on dd.ClassmajorID equals tmp.ClassmajorID 
                         select new
                         {
                             dd.UserID,
                             tmp.SchoolyearID,
                             tmp.CoursematerialID,
                             tmp.TeachingModeID,
                             MinEducationSchedulingClassID = tmp.MinEducationSchedulingClassID
                         };
            result.AddRange(query3.ToList().Select(x => new StudentsView { TepTableID = new Guid(x.MinEducationSchedulingClassID), UserID = x.UserID }).ToList());
            #endregion

            //var query = from df in DifferentDynamicRepository.GetList(filter)
            //            join em in EducationMissionRepository.GetList(x => x.CF_Schoolyear.Value >= schoolYearValue)
            //           on df.CF_Schoolyear.Value >= schoolYearValue equals em.CF_Schoolyear.Value >= schoolYearValue
            //            join emc in EducationMissionClassRepository.Entities on em.EducationMissionID equals emc.EducationMissionID
            //            from emccc in emc.CF_Classmajor.Where(x => x.ClassmajorID == df.AfterClassmajorID)
            //            join eds in EducationSchedulingClassRepository.Entities on emc.EducationMissionClassID equals eds.EducationMissionClassID
            //            from edscc in eds.CF_Student.Where(x => x.UserID == df.UserID).DefaultIfEmpty()
            //            where edscc.UserID == null
            //            && df.AfterClassmajorID != df.ClassmajorID
            //            select new StudentsView
            //            {
            //                TepTableID = eds.EducationSchedulingClassID,
            //                UserID = df.UserID,
            //            };

            return result;
        }

        #endregion

        #region 6.0 考场安排Add
        // 考场安排
        public IQueryable<EX_ExaminationPlan> GetExaminationPlanStudentInsert(IList<StudentsView> educationMissionResult)
        {
            var educationSchedulingClassIDList = educationMissionResult.Select(x => x.TepTableID).Distinct().ToList();
            var q = (from esc in EducationSchedulingClassRepository.GetList(x => educationSchedulingClassIDList.Contains(x.EducationSchedulingClassID))
                     join ep in ExaminationPlanRepository.Entities on esc.EM_EducationMissionClass.EducationMissionID equals ep.EducationMissionID
                     select ep
                );
            return q;
        }
        #endregion

        #region 7.0 成绩管理Add
        //成绩管理
        public IQueryable<StudentsView> GetScoreStudentInsert(Expression<Func<CF_Student, bool>> filter, Expression<Func<EM_EducationMissionClass, bool>> missionClassFilter)
        {
            //期末设定、补考设定为未开放及可录入状态的才进行学生信息调整
            var query = from df in StudentRepository.GetList(filter)
                        //join c in ClassmajorRepository.Entities on dd.ClassmajorID equals c.ClassmajorID
                        //join g in GrademajorRepository.Entities on c.ClassmajorID equals g.GrademajorID
                        from sy in SchoolyearRepository.Entities.Where(x => x.IsCurrent == true)
                        from em in EducationMissionRepository.Entities.Where(x => x.CF_Schoolyear.Value >= sy.Value)
                        //on df.CF_Schoolyear.Value >= schoolYearValue equals em.CF_Schoolyear.Value >= schoolYearValue
                        join emc in EducationMissionClassRepository.Entities.Where(missionClassFilter) on em.EducationMissionID equals emc.EducationMissionID
                        from emccc in emc.CF_Classmajor.Where(x => x.ClassmajorID == df.ClassmajorID)
                        from fex in FinalExaminationRepository.Entities.Where(x => (
                            x.ApprovalStatus == (int)CF_ScoreState.CanInput || x.ApprovalStatus == (int)CF_ScoreState.NoCanInput)
                            && x.ExaminationType == (int)CF_ExaminationType.FinalExamination && x.CF_Schoolyear.Value >= sy.Value
                            && x.EducationMissionID == em.EducationMissionID)
                        //on em.EducationMissionID equals fex.EducationMissionID
                        from fexcc in fex.ER_FinalExaminationStudent.Where(x => x.UserID == df.UserID).DefaultIfEmpty()
                        where fexcc.UserID == null
                        select new StudentsView
                        {
                            TepTableID = fex.FinalExaminationID,
                            UserID = df.UserID,
                        };
            return query;

        }
        #endregion

        #region 8.0 教学评价Add
        //教学评价
        public IQueryable<StudentsView> GetEvaluationSettingStudentInsert(Expression<Func<CF_Student, bool>> filter)
        {
            //评价设定为未开放状态才进行学生信息调整
            Expression<Func<EM_EvaluationSetting, bool>> exp = x => x.OpenStatus == (int)CF_YesOrNoStatus.No;
            var query = from df in StudentRepository.GetList(filter)
                        //join c in ClassmajorRepository.Entities on dd.ClassmajorID equals c.ClassmajorID
                        //join g in GrademajorRepository.Entities on c.ClassmajorID equals g.GrademajorID
                        from sy in SchoolyearRepository.Entities.Where(x => x.IsCurrent == true)
                        from em in EducationMissionRepository.Entities.Where(x => x.CF_Schoolyear.Value >= sy.Value)
                        //on df.CF_Schoolyear.Value >= schoolYearValue equals em.CF_Schoolyear.Value >= schoolYearValue
                        join emc in EducationMissionClassRepository.Entities on em.EducationMissionID equals emc.EducationMissionID
                        from emccc in emc.CF_Classmajor.Where(x => x.ClassmajorID == df.ClassmajorID)
                        join eds in EvaluationSettingRepository.GetList(exp) on emc.EducationMissionClassID equals eds.EducationMissionClassID
                        from edscc in eds.CF_Student.Where(x => x.UserID == df.UserID).DefaultIfEmpty()
                        where edscc.UserID == null
                        select new StudentsView
                        {
                            TepTableID = eds.EvaluationSettingID,
                            UserID = df.UserID,
                        };

            return query;
        }
        #endregion

        #region 9.0 学生征订
        // 学生征订
        public IQueryable<ET_StudentsOrder> GetAddStudentsOrder(Expression<Func<CF_Student, bool>> filter)
        {
            //学生征订未征订状态的才进行信息调整       
            Expression<Func<ET_StudentsOrder, bool>> exp =
                x => x.IsOrdered == false;//未征订

            var query = from s in StudentRepository.GetList(filter)
                        join biss in InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true) on s.InSchoolStatusID equals biss.InSchoolStatusID
                        join ac in ClassmajorRepository.Entities on s.ClassmajorID equals ac.ClassmajorID
                        from sy in SchoolyearRepository.Entities.Where(x => x.IsCurrent == true)
                        from sp in SpecialtyPlanRepository.Entities.Where(x => x.CF_Schoolyear.Value >= sy.Value
                            && x.GrademajorID == ac.GrademajorID)
                        join stdOrd in StudentsOrderRepository.GetList(exp) on sp.SpecialtyPlanID equals stdOrd.SpecialtyPlanID
                        select stdOrd;

            return query;
        }
        #endregion

        #region 10.0 学生发放
        // 学生发放
        public IQueryable<ET_StudentDistribute> GetAddStudentDistribute(Expression<Func<CF_Student, bool>> filter)
        {
            //学生发放未发放状态的才进行信息调整       
            Expression<Func<ET_StudentDistribute, bool>> exp =
                x => x.RecordStatus == (int)CF_YesOrNoStatus.No;//未发放

            var query = from s in StudentRepository.GetList(filter)
                        join biss in InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true) on s.InSchoolStatusID equals biss.InSchoolStatusID
                        join ac in ClassmajorRepository.Entities on s.ClassmajorID equals ac.ClassmajorID
                        from sy in SchoolyearRepository.Entities.Where(x => x.IsCurrent == true)
                        from sp in SpecialtyPlanRepository.Entities.Where(x => x.CF_Schoolyear.Value >= sy.Value
                            && x.GrademajorID == ac.GrademajorID)
                        //on df.CF_Schoolyear.Value >= schoolYearValue equals sp.CF_Schoolyear.Value >= schoolYearValue
                        join sd in StudentDistributeRepository.GetList(exp) on (Guid)sp.SpecialtyPlanID equals (Guid)sd.SpecialtyPlanID
                        select sd;

            return query;
        }
        #endregion

    }
}