JW/EMiS.DataLogic/Common/Students/HighBaseTable/BachelorSpecialtyStudentCountDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EMIS.DataLogic.Repositories;
using EMIS.DataLogic.Common.CalendarManage;
using EMIS.ViewModel.Students.HighBaseTable;

namespace EMIS.DataLogic.Common.Students.HighBaseTable
{
    public class BachelorSpecialtyStudentCountDAL
    {
        public DictionaryItemRepository DictionaryItemRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }
        public StudentProfileRepository StudentProfileRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public SpecialtyRepository SpecialtyRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public RecruitstudentsRepository RecruitstudentsRepository { get; set; }
        public InSchoolSettingRepository InSchoolSettingRepository { get; set; }
        public EducationTypeSettingRepository EducationTypeSettingRepository { get; set; }
        public SchoolYearDAL SchoolYearDAL { get; set; }
        public GraduationApplyRepository GraduationApplyRepository { get; set; }
        public DegreeApplyRepository DegreeApplyRepository { get; set; }
        public StudentReportRepository StudentReportRepository { get; set; }

        public IQueryable<BachelorSpecialtyStudentCountView> GetStudent()
        {
            var query = from student in StudentRepository.GetList(x => true)
                        join clmajor in ClassmajorRepository.Entities on student.ClassmajorID equals clmajor.ClassmajorID
                        join grmajor in GrademajorRepository.Entities on clmajor.GrademajorID equals grmajor.GrademajorID
                        join famajor in FacultymajorRepository.Entities on grmajor.FacultymajorID equals famajor.FacultymajorID
                        //join special in SpecialtyRepository.Entities on new { famajor.StandardID, famajor.EducationID, famajor.LearnSystem, famajor.LearningformID }
                        //equals new { special.StandardID, special.EducationID, special.LearnSystem, special.LearningformID }
                        join edutype in EducationTypeSettingRepository.GetList(x => x.EducationTypeID == (int)EMIS.ViewModel.CF_EducationType.UnderGraduate) on famajor.EducationID equals edutype.EducationID
                        group student by new
                        {
                            //student.Sex,
                            //edutype.EducationTypeID,
                            //special.SpecialtyID,
                            //famajor.TeacherIdentification,
                            famajor.StandardID,
                            famajor.EducationID,
                            famajor.LearnSystem,
                            famajor.LearningformID
                        } into g
                        select new BachelorSpecialtyStudentCountView
                        {
                            //SpecialtyID = special.SpecialtyID,
                            StandardID = g.Key.StandardID,
                            EducationID = g.Key.EducationID,
                            LearningformID = g.Key.LearningformID,
                            LearnSystem = g.Key.LearnSystem,
                        };
            return query.Distinct();
        }

        public IQueryable<BachelorSpecialtyStudentCountView> GetInschoolCount(int? schoolYear)
        {
            var schYear = SchoolYearDAL.GetSchoolYearQueryable(x => x.IsCurrent).FirstOrDefault();
            var query = from student in StudentRepository.Entities 
                        join inschool in InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true) 
                            on student.InSchoolStatusID equals inschool.InSchoolStatusID 
                        join profile in StudentProfileRepository.Entities on student.UserID equals profile.UserID
                        join recruit in RecruitstudentsRepository.Entities on student.UserID equals recruit.UserID
                        join clmajor in ClassmajorRepository.Entities on student.ClassmajorID equals clmajor.ClassmajorID
                        join grmajor in GrademajorRepository.Entities on clmajor.GrademajorID equals grmajor.GrademajorID
                        join famajor in FacultymajorRepository.Entities on grmajor.FacultymajorID equals famajor.FacultymajorID
                        //join special in SpecialtyRepository.Entities on new { famajor.StandardID, famajor.EducationID, famajor.LearnSystem, famajor.LearningformID }
                        //equals new { special.StandardID, special.EducationID, special.LearnSystem, special.LearningformID }
                        join edutype in EducationTypeSettingRepository.GetList(x => x.EducationTypeID == (int)EMIS.ViewModel.CF_EducationType.UnderGraduate) on famajor.EducationID equals edutype.EducationID
                        join grayear in SchoolyearRepository.Entities on new { SchoolcodeID = grmajor.SemesterID.Value, Years = grmajor.GradeID.Value } equals new { grayear.SchoolcodeID, grayear.Years }
                        //join report in
                        //    (
                        //        from report in StudentReportRepository.GetList(x => x.RecordStatus > (int)EMIS.ViewModel.SYS_STATUS.UNUSABLE
                        //            && x.ReportStatusID == (int)EMIS.ViewModel.CF_ReportStatus.Havetoreportforduty)
                        //        join schoolyear in SchoolyearRepository.Entities on report.SchoolyearID equals schoolyear.SchoolyearID
                        //        where schoolyear.Years == schoolYear
                        //        select report.UserID
                        //        ).Distinct() on student.UserID equals report
                        group student by new
                        {
                            student.SexID,
                            edutype.EducationTypeID,
                            //special.SpecialtyID,
                            famajor.TeacherIdentification,
                            famajor.StandardID,
                            famajor.EducationID,
                            famajor.LearnSystem,
                            famajor.LearningformID,
                            SchoolyearNumID = ((schYear.Value - (schYear.SchoolcodeID == grmajor.SemesterID ? 0 : 1)) - grayear.Value) / 2 + 1
                        } into g
                        select new BachelorSpecialtyStudentCountView
                        {
                            Sex = g.Key.SexID,
                            //SpecialtyID = g.Key.SpecialtyID,
                            SchoolyearNumID = g.Key.SchoolyearNumID,
                            StandardID = g.Key.StandardID,
                            EducationID = g.Key.EducationID,
                            LearnSystem = g.Key.LearnSystem,
                            LearningformID = g.Key.LearningformID,
                            EducationTypeID = g.Key.EducationTypeID,
                            InschoolCount = g.Count()
                        };
            return query;
        }

        //招生数
        public IQueryable<BachelorSpecialtyStudentCountView> GetRecruitCount(int? schoolYear)
        {
            //var schYear = SchoolYearDAL.GetSchoolYearQueryable(x => x.IsCurrent).FirstOrDefault().Years;
            var query = from student in StudentRepository.Entities
                        join inschool in InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true)
                            on student.InSchoolStatusID equals inschool.InSchoolStatusID 
                        join profile in StudentProfileRepository.Entities on student.UserID equals profile.UserID
                        join recruit in RecruitstudentsRepository.Entities on student.UserID equals recruit.UserID
                        join clmajor in ClassmajorRepository.Entities on student.ClassmajorID equals clmajor.ClassmajorID
                        join grmajor in GrademajorRepository.Entities on clmajor.GrademajorID equals grmajor.GrademajorID
                        join famajor in FacultymajorRepository.Entities on grmajor.FacultymajorID equals famajor.FacultymajorID
                        //join special in SpecialtyRepository.Entities on new { famajor.StandardID, famajor.EducationID, famajor.LearnSystem, famajor.LearningformID }
                        //equals new { special.StandardID, special.EducationID, special.LearnSystem, special.LearningformID }
                        join edutype in EducationTypeSettingRepository.GetList(x => x.EducationTypeID == (int)EMIS.ViewModel.CF_EducationType.UnderGraduate) on famajor.EducationID equals edutype.EducationID
                        join entyear in SchoolyearRepository.GetList(x => x.Years == schoolYear) on recruit.EnteringSchoolYearID equals entyear.SchoolyearID
                        group student by new
                        {
                            student.SexID,
                            edutype.EducationTypeID,
                            //special.SpecialtyID,
                            famajor.TeacherIdentification,
                            famajor.StandardID,
                            famajor.EducationID,
                            famajor.LearnSystem,
                            famajor.LearningformID
                        } into g
                        select new BachelorSpecialtyStudentCountView
                        {
                            Sex = g.Key.SexID,
                            //SpecialtyID = g.Key.SpecialtyID,
                            StandardID = g.Key.StandardID,
                            EducationID = g.Key.EducationID,
                            LearnSystem = g.Key.LearnSystem,
                            LearningformID = g.Key.LearningformID,
                            EducationTypeID = g.Key.EducationTypeID,
                            RecruitCount = g.Count()
                        };
            return query;
        }

        //春季招生数，普教需要
        public IQueryable<BachelorSpecialtyStudentCountView> SpringRecruitCount(int? schoolYear)
        {
            //var schYear = SchoolYearDAL.GetSchoolYearQueryable(x => x.IsCurrent).FirstOrDefault().Years;
            var query = from student in StudentRepository.Entities
                        join inschool in InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true)
                            on student.InSchoolStatusID equals inschool.InSchoolStatusID 
                        join profile in StudentProfileRepository.Entities on student.UserID equals profile.UserID
                        join recruit in RecruitstudentsRepository.Entities on student.UserID equals recruit.UserID
                        join clmajor in ClassmajorRepository.Entities on student.ClassmajorID equals clmajor.ClassmajorID
                        join grmajor in GrademajorRepository.Entities on clmajor.GrademajorID equals grmajor.GrademajorID
                        join famajor in FacultymajorRepository.Entities on grmajor.FacultymajorID equals famajor.FacultymajorID
                        //join special in SpecialtyRepository.Entities on new { famajor.StandardID, famajor.EducationID, famajor.LearnSystem, famajor.LearningformID }
                        //equals new { special.StandardID, special.EducationID, special.LearnSystem, special.LearningformID }
                        join edutype in EducationTypeSettingRepository.GetList(x => x.EducationTypeID == (int)EMIS.ViewModel.CF_EducationType.UnderGraduate) on famajor.EducationID equals edutype.EducationID
                        join entyear in SchoolyearRepository.GetList(x => x.Years == schoolYear && x.SchoolcodeID == (int)EMIS.ViewModel.CF_Semester.Spring) on recruit.EnteringSchoolYearID equals entyear.SchoolyearID
                        group student by new
                        {
                            student.SexID,
                            edutype.EducationTypeID,
                            //special.SpecialtyID,
                            famajor.TeacherIdentification,
                            famajor.StandardID,
                            famajor.EducationID,
                            famajor.LearnSystem,
                            famajor.LearningformID
                        } into g
                        select new BachelorSpecialtyStudentCountView
                        {
                            Sex = g.Key.SexID,
                            //SpecialtyID = g.Key.SpecialtyID,
                            StandardID = g.Key.StandardID,
                            EducationID = g.Key.EducationID,
                            LearnSystem = g.Key.LearnSystem,
                            LearningformID = g.Key.LearningformID,
                            EducationTypeID = g.Key.EducationTypeID,
                            SpringRecruitCount = g.Count()
                        };
            return query;
        }

        //预计毕业数
        public IQueryable<BachelorSpecialtyStudentCountView> PlanGraduationCount(int? schoolYear)
        {
            //var schYear = SchoolYearDAL.GetSchoolYearQueryable(x => x.IsCurrent).FirstOrDefault().Years;
            var query = from student in StudentRepository.Entities
                        join inschool in InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true)
                            on student.InSchoolStatusID equals inschool.InSchoolStatusID 
                        join profile in StudentProfileRepository.Entities on student.UserID equals profile.UserID
                        join recruit in RecruitstudentsRepository.Entities on student.UserID equals recruit.UserID
                        join clmajor in ClassmajorRepository.Entities on student.ClassmajorID equals clmajor.ClassmajorID
                        join grmajor in GrademajorRepository.Entities on clmajor.GrademajorID equals grmajor.GrademajorID
                        join famajor in FacultymajorRepository.Entities on grmajor.FacultymajorID equals famajor.FacultymajorID
                        //join special in SpecialtyRepository.Entities on new { famajor.StandardID, famajor.EducationID, famajor.LearnSystem, famajor.LearningformID }
                        //equals new { special.StandardID, special.EducationID, special.LearnSystem, special.LearningformID }
                        join edutype in EducationTypeSettingRepository.GetList(x => x.EducationTypeID == (int)EMIS.ViewModel.CF_EducationType.UnderGraduate) on famajor.EducationID equals edutype.EducationID
                        //年级专业毕业学年等于当前学年
                        join grayear in SchoolyearRepository.GetList(x => x.Years == schoolYear) on grmajor.GraduateSchoolyearID equals grayear.SchoolyearID


                        group student by new
                        {
                            student.SexID,
                            edutype.EducationTypeID,
                            //special.SpecialtyID,
                            famajor.TeacherIdentification,
                            famajor.StandardID,
                            famajor.EducationID,
                            famajor.LearnSystem,
                            famajor.LearningformID
                        } into g
                        select new BachelorSpecialtyStudentCountView
                        {
                            Sex = g.Key.SexID,
                            //SpecialtyID = g.Key.SpecialtyID,
                            StandardID = g.Key.StandardID,
                            EducationID = g.Key.EducationID,
                            LearnSystem = g.Key.LearnSystem,
                            LearningformID = g.Key.LearningformID,
                            EducationTypeID = g.Key.EducationTypeID,
                            PlanGraduationCount = g.Count()
                        };
            return query;
        }

        //毕业数，上一学年毕业的学生数，学籍状态为毕业，并且有上一学年的毕业申请
        public IQueryable<BachelorSpecialtyStudentCountView> GetGraduationCount(int? schoolYear)
        {
            var query = from student in StudentRepository.GetList(x => x.StudentStatus == (int)EMIS.ViewModel.CF_StudentStatus.Graduation)
                        //join profile in StudentProfileRepository.Entities on student.UserID equals profile.UserID
                        //join recruit in RecruitstudentsRepository.Entities on student.UserID equals recruit.UserID
                        join clmajor in ClassmajorRepository.Entities on student.ClassmajorID equals clmajor.ClassmajorID
                        join grmajor in GrademajorRepository.Entities on clmajor.GrademajorID equals grmajor.GrademajorID
                        join famajor in FacultymajorRepository.Entities on grmajor.FacultymajorID equals famajor.FacultymajorID
                        //join special in SpecialtyRepository.Entities on new { famajor.StandardID, famajor.EducationID, famajor.LearnSystem, famajor.LearningformID }
                        //equals new { special.StandardID, special.EducationID, special.LearnSystem, special.LearningformID }
                        join edutype in EducationTypeSettingRepository.GetList(x => x.EducationTypeID == (int)EMIS.ViewModel.CF_EducationType.UnderGraduate) on famajor.EducationID equals edutype.EducationID
                        //有上一学年的毕业申请
                        //join gradApply in GraduationApplyRepository.Entities on student.UserID equals gradApply.UserID
                        //join grayear in SchoolyearRepository.GetList(x => x.Years == schoolYear - 1) on gradApply.SchoolyearID equals grayear.SchoolyearID
                        join gradApply in
                            (
                                from graduationApply in GraduationApplyRepository.Entities
                                join schoolyear in SchoolyearRepository.Entities 
                                on graduationApply.ApplySchoolyearID equals schoolyear.SchoolyearID
                                group schoolyear by new { graduationApply.UserID, schoolyear.Years } into g
                                select new { UserID = g.Key.UserID.Value, graduationYear = g.Max(x => x.Years) }
                             ) on new { student.UserID, schoolYear = schoolYear.Value } equals new { gradApply.UserID, schoolYear = gradApply.graduationYear + 1 }

                        group student by new
                        {
                            student.SexID,
                            edutype.EducationTypeID,
                            //special.SpecialtyID,
                            famajor.TeacherIdentification,
                            famajor.StandardID,
                            famajor.EducationID,
                            famajor.LearnSystem,
                            famajor.LearningformID
                        } into g
                        select new BachelorSpecialtyStudentCountView
                        {
                            Sex = g.Key.SexID,
                            //SpecialtyID = g.Key.SpecialtyID,
                            StandardID = g.Key.StandardID,
                            EducationID = g.Key.EducationID,
                            LearnSystem = g.Key.LearnSystem,
                            LearningformID = g.Key.LearningformID,
                            EducationTypeID = g.Key.EducationTypeID,
                            GraduationCount = g.Count()
                        };
            return query;
        }

        //应届毕业生数,上一学年毕业的学生，并且为正常的预计毕业学期（如3年制的学生，从入学学期到毕业刚好6个学期）
        public IQueryable<BachelorSpecialtyStudentCountView> GetGraduatingCount(int? schoolYear)
        {
            var query = from student in StudentRepository.GetList(x => x.StudentStatus == (int)EMIS.ViewModel.CF_StudentStatus.Graduation)
                        join recruit in RecruitstudentsRepository.Entities on student.UserID equals recruit.UserID
                        join clmajor in ClassmajorRepository.Entities on student.ClassmajorID equals clmajor.ClassmajorID
                        join grmajor in GrademajorRepository.Entities on clmajor.GrademajorID equals grmajor.GrademajorID
                        join famajor in FacultymajorRepository.Entities on grmajor.FacultymajorID equals famajor.FacultymajorID
                        join enteringSchoolYear in SchoolyearRepository.Entities on recruit.EnteringSchoolYearID equals enteringSchoolYear.SchoolyearID
                        join edutype in EducationTypeSettingRepository.GetList(x => x.EducationTypeID == (int)EMIS.ViewModel.CF_EducationType.UnderGraduate) on famajor.EducationID equals edutype.EducationID
                        join gradApply in
                            (
                                from graduationApply in GraduationApplyRepository.Entities
                                join schoolyear in SchoolyearRepository.Entities on graduationApply.ApplySchoolyearID equals schoolyear.SchoolyearID
                                group schoolyear by new { graduationApply.UserID, schoolyear.Years } into g
                                select new { UserID = g.Key.UserID.Value, graduationYear = g.Max(x => x.Years), schoolYearValue = g.Max(x => x.Value) }
                             ) on new
                             {
                                 student.UserID,
                                 schoolYear = schoolYear.Value,
                                 schoolYearValue = enteringSchoolYear.Value + (int)(famajor.LearnSystem * 2) - 1
                             } equals new
                             {
                                 gradApply.UserID,
                                 schoolYear = gradApply.graduationYear + 1,
                                 gradApply.schoolYearValue
                             }

                        group student by new
                        {
                            student.SexID,
                            edutype.EducationTypeID,
                            //special.SpecialtyID,
                            famajor.TeacherIdentification,
                            famajor.StandardID,
                            famajor.EducationID,
                            famajor.LearnSystem,
                            famajor.LearningformID
                        } into g
                        select new BachelorSpecialtyStudentCountView
                        {
                            Sex = g.Key.SexID,
                            //SpecialtyID = g.Key.SpecialtyID,
                            StandardID = g.Key.StandardID,
                            EducationID = g.Key.EducationID,
                            LearnSystem = g.Key.LearnSystem,
                            LearningformID = g.Key.LearningformID,
                            EducationTypeID = g.Key.EducationTypeID,
                            GraduatingCount = g.Count()
                        };
            return query;
        }

        //授予学位数，上一学年的授予学位数，学籍状态为毕业，并且有上一学年的学位申请，学位证号不为空
        public IQueryable<BachelorSpecialtyStudentCountView> GetDegreedCount(int? schoolYear)
        {
            var query = from student in StudentRepository.GetList(x => x.StudentStatus == (int)EMIS.ViewModel.CF_StudentStatus.Graduation)
                        //join profile in StudentProfileRepository.Entities on student.UserID equals profile.UserID
                        //join recruit in RecruitstudentsRepository.Entities on student.UserID equals recruit.UserID
                        join clmajor in ClassmajorRepository.Entities on student.ClassmajorID equals clmajor.ClassmajorID
                        join grmajor in GrademajorRepository.Entities on clmajor.GrademajorID equals grmajor.GrademajorID
                        join famajor in FacultymajorRepository.Entities on grmajor.FacultymajorID equals famajor.FacultymajorID
                        //join special in SpecialtyRepository.Entities on new { famajor.StandardID, famajor.EducationID, famajor.LearnSystem, famajor.LearningformID }
                        //equals new { special.StandardID, special.EducationID, special.LearnSystem, special.LearningformID }
                        join edutype in EducationTypeSettingRepository.GetList(x => x.EducationTypeID == (int)EMIS.ViewModel.CF_EducationType.UnderGraduate) on famajor.EducationID equals edutype.EducationID
                        join gradApply in GraduationApplyRepository.Entities on student.UserID equals gradApply.UserID
                        //join grayear in SchoolyearRepository.GetList(x => x.Years == schoolYear - 1) on gradApply.SchoolyearID equals grayear.SchoolyearID
                        join degrApply in
                            (
                                from graduationApply in GraduationApplyRepository.Entities
                                join DegreeApply in DegreeApplyRepository.GetList(x => x.DegreeNo != null && x.DegreeNo.Trim() != "") on graduationApply.GraduationApplyID equals DegreeApply.GraduationApplyID
                                join schoolyear in SchoolyearRepository.Entities on DegreeApply.ApplySchoolyearID equals schoolyear.SchoolyearID
                                group schoolyear by new { graduationApply.UserID, schoolyear.Years } into g
                                select new { UserID = g.Key.UserID.Value, graduationYear = g.Max(x => x.Years) }
                             ) on new { student.UserID, schoolYear = schoolYear.Value } equals new { degrApply.UserID, schoolYear = degrApply.graduationYear + 1 }

                        group student by new
                        {
                            student.SexID,
                            edutype.EducationTypeID,
                            //special.SpecialtyID,
                            famajor.TeacherIdentification,
                            famajor.StandardID,
                            famajor.EducationID,
                            famajor.LearnSystem,
                            famajor.LearningformID
                        } into g
                        select new BachelorSpecialtyStudentCountView
                        {
                            Sex = g.Key.SexID,
                            //SpecialtyID = g.Key.SpecialtyID,
                            StandardID = g.Key.StandardID,
                            EducationID = g.Key.EducationID,
                            LearnSystem = g.Key.LearnSystem,
                            LearningformID = g.Key.LearningformID,
                            EducationTypeID = g.Key.EducationTypeID,
                            DegreeCount = g.Count()
                        };
            return query;
        }
    }
}