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

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

namespace EMIS.DataLogic.Common.Students.HighBaseTable
{
    public class StudentChangeDAL
    {
        public StudentChangeRepository StudentChangeRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }
        public InSchoolSettingRepository InSchoolSettingRepository { get; set; }
        public RecruitstudentsRepository RecruitstudentsRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public EducationTypeSettingRepository EducationTypeSettingRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public StudentReportRepository StudentReportRepository { get; set; }
        public GraduationApplyRepository GraduationApplyRepository { get; set; }
        public DifferentDynamicRepository DifferentDynamicRepository { get; set; }
        public DifferentDynamicTypeInOutSettingRepository DifferentDynamicTypeInOutSettingRepository { get; set; }

        public IQueryable<StudentChangeStudentCountView> GetRecruitStudentCount(int year)
        {
            var q = (
                from student in StudentRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE) 
                join inschool in InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true) 
                    on student.InSchoolStatusID equals inschool.InSchoolStatusID
                join classmajor in ClassmajorRepository.Entities on student.ClassmajorID equals classmajor.ClassmajorID
                join grade in GrademajorRepository.Entities on classmajor.GrademajorID equals grade.GrademajorID
                join faculty in FacultymajorRepository.Entities on grade.FacultymajorID equals faculty.FacultymajorID
                join educationType in EducationTypeSettingRepository.Entities on faculty.EducationID equals educationType.EducationID
                join recruit in RecruitstudentsRepository.Entities on student.UserID equals recruit.UserID
                join schoolyear in SchoolyearRepository.Entities on recruit.EnteringSchoolYearID equals schoolyear.SchoolyearID
                //join report in
                //    (
                //        from report in StudentReportRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                //        join schoolyear in SchoolyearRepository.Entities on report.SchoolyearID equals schoolyear.SchoolyearID
                //        where schoolyear.Years == year
                //        select report.UserID
                //        ).Distinct() on student.UserID equals report
                where schoolyear.Years == year
                group student by educationType.EducationTypeID into g
                select new StudentChangeStudentCountView 
                { 
                    EducationTypeID = g.Key,
                    StudentCount = g.Sum(x => 1)
                }
                );

            return q;
        }

        public IQueryable<StudentChangeStudentCountView> GetGraduationStudentCount(int year)
        {
            var q = (
                from student in StudentRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                join classmajor in ClassmajorRepository.Entities on student.ClassmajorID equals classmajor.ClassmajorID
                join grade in GrademajorRepository.Entities on classmajor.GrademajorID equals grade.GrademajorID
                join faculty in FacultymajorRepository.Entities on grade.FacultymajorID equals faculty.FacultymajorID
                join educationType in EducationTypeSettingRepository.Entities on faculty.EducationID equals educationType.EducationID
                join graduation in GraduationApplyRepository.Entities on student.UserID equals graduation.UserID
                join schoolyear in SchoolyearRepository.Entities on graduation.ApplySchoolyearID equals schoolyear.SchoolyearID
                where schoolyear.Years == year && student.StudentStatus == (int)CF_StudentStatus.Graduation 
                group student by educationType.EducationTypeID into g
                select new StudentChangeStudentCountView
                {
                    EducationTypeID = g.Key,
                    StudentCount = g.Sum(x => 1)
                }
                );

            return q;
        }

        public IQueryable<StudentChangeStudentCountView> GetGraduationOverStudentCount(int year)
        {
            var q = (
                from student in StudentRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                join classmajor in ClassmajorRepository.Entities on student.ClassmajorID equals classmajor.ClassmajorID
                join grade in GrademajorRepository.Entities on classmajor.GrademajorID equals grade.GrademajorID
                join faculty in FacultymajorRepository.Entities on grade.FacultymajorID equals faculty.FacultymajorID
                join educationType in EducationTypeSettingRepository.Entities on faculty.EducationID equals educationType.EducationID
                join graduation in GraduationApplyRepository.Entities on student.UserID equals graduation.UserID
                join schoolyear in SchoolyearRepository.Entities on graduation.ApplySchoolyearID equals schoolyear.SchoolyearID
                where schoolyear.Years == year && student.StudentStatus == (int)CF_StudentStatus.Complete
                group student by educationType.EducationTypeID into g
                select new StudentChangeStudentCountView
                {
                    EducationTypeID = g.Key,
                    StudentCount = g.Sum(x => 1)
                }
                );

            return q;
        }

        public IQueryable<HB_StudentChange> GetLastInschoolStudentCount(int year)
        {
            return StudentChangeRepository.GetList(x => x.Year == (year - 1));
        }

        public IQueryable<StudentChangeStudentCountView> GetCurrentInschoolStudentCount()
        {
            var q = (
                from student in StudentRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                join classmajor in ClassmajorRepository.Entities on student.ClassmajorID equals classmajor.ClassmajorID
                join grade in GrademajorRepository.Entities on classmajor.GrademajorID equals grade.GrademajorID
                join faculty in FacultymajorRepository.Entities on grade.FacultymajorID equals faculty.FacultymajorID
                join educationType in EducationTypeSettingRepository.Entities on faculty.EducationID equals educationType.EducationID
                where student.StudentStatus == (int)CF_StudentStatus.Registration
                group student by educationType.EducationTypeID into g
                select new StudentChangeStudentCountView
                {
                    EducationTypeID = g.Key,
                    StudentCount = g.Sum(x => 1)
                }
                );

            return q;
        }

        public IQueryable<StudentChangeDetailStudentCountView> GetStudentChangeCount(int year, int? differentDynamicEndStatusID)
        { 
            var q = (
                from change in DifferentDynamicRepository.GetList(x => x.ApprovalStatus == differentDynamicEndStatusID) 
                join student in StudentRepository.Entities on change.UserID equals student.UserID 
                join classmajor in ClassmajorRepository.Entities on student.ClassmajorID equals classmajor.ClassmajorID
                join grade in GrademajorRepository.Entities on classmajor.GrademajorID equals grade.GrademajorID
                join faculty in FacultymajorRepository.Entities on grade.FacultymajorID equals faculty.FacultymajorID
                join educationType in EducationTypeSettingRepository.Entities on faculty.EducationID equals educationType.EducationID 
                join schoolyear in SchoolyearRepository.Entities on change.SchoolyearID equals schoolyear.SchoolyearID 
                //只有配置表里面有的才会被统计
                join inout in DifferentDynamicTypeInOutSettingRepository.Entities on change.ChangeTypeID equals inout.ChangeTypeID 
                where schoolyear.Years == year
                group new { change, inout } by new { educationType.EducationTypeID, change.ChangeTypeID } into g 
                select new StudentChangeDetailStudentCountView
                {
                    EducationTypeID = g.Key.EducationTypeID,
                    DifferentDynamicType = g.Key.ChangeTypeID,
                    StudentInCount = g.Sum(x => (x.change.AfterClassmajorID != null && x.inout.IsIn == true) ? 1 : 0),
                    StudentOutCount = g.Sum(x => (x.change.AfterClassmajorID == null && x.inout.IsOut == true) ? 1 : 0)
                }
                );

            return q;
        }
    }
}