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

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

namespace EMIS.DataLogic.Common.Students.HighBaseTable
{
    public class StudentQuitReasonDAL
    {
        public DifferentDynamicRepository DifferentDynamicRepository { get; set; }
        public StudentRepository StudentRepository { 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 StudentChangeReasonTypeDetailRepository StudentChangeReasonTypeDetailRepository { get; set; }

        public IQueryable<StudentQuitStudentCountView> GetStudentQuitStudentCount(int year, int? differentDynamicEndStatusID)
        {
            var differentDynamicTypeIDList = new List<int>
            {
                (int)CF_ChangeType.Dismiss,
                (int)CF_ChangeType.Quit
            };

            var q = (
                from change in DifferentDynamicRepository.GetList(x => x.ApprovalStatus == differentDynamicEndStatusID && x.AfterClassmajorID == null)
                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 reasonType in StudentChangeReasonTypeDetailRepository.Entities on change.ChangeReasonID equals reasonType.DifferentDynamicReason 
                    into dreasonType 
                from reasonType in dreasonType.DefaultIfEmpty()
                where schoolyear.Years == year && differentDynamicTypeIDList.Contains(change.ChangeTypeID.Value)
                group change by new { educationType.EducationTypeID, reasonType.StudentChangeReasonTypeID } into g
                select new StudentQuitStudentCountView
                {
                    EducationTypeID = g.Key.EducationTypeID,
                    StudentChangeReasonTypeID = g.Key.StudentChangeReasonTypeID,
                    StudentCount = g.Sum(x => 1)
                }
                );

            return q;
        }
    }
}