JW/EMiS.DataLogic/Common/Students/HighBaseTable/FacultymajorStudentCountDAL.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 FacultymajorStudentCountDAL
    {
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }
        public RecruitstudentsRepository RecruitstudentsRepository { get; set; }
        public InSchoolSettingRepository InSchoolSettingRepository { get; set; }
        public EducationTypeSettingRepository EducationTypeSettingRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public ExamineeTypeSettingRepository ExamineeTypeSettingRepository { get; set; }

        public IQueryable<FacultymajorStudentCountView> GetFacultymajorStudentCountViewQueryable(int year)
        {
            var dataSql = (from faculty in FacultymajorRepository.Entities
                           join graGrade in GrademajorRepository.Entities
                                on new { FacultymajorID = (Guid?)faculty.FacultymajorID, Gradeyear = Math.Floor((double)year - (double)faculty.LearnSystem) }
                                equals new { graGrade.FacultymajorID, Gradeyear = (double)graGrade.GradeID }
                           join classmajor in ClassmajorRepository.Entities on graGrade.GrademajorID equals classmajor.ClassmajorID
                           join student in StudentRepository.Entities.Where(x => x.StudentStatus == (int)CF_StudentStatus.Graduation)
                                on classmajor.ClassmajorID equals student.ClassmajorID
                           group faculty by new { faculty.FacultymajorID, student.SexID } into g
                           select new
                           {
                               FacultymajorID = g.Key.FacultymajorID,
                               SexID = g.Key.SexID,
                               LastGraduateCount = g.Count(),
                               RecruitstudentsCount = 0,
                               FreshGraduateCount = 0,
                               GradeOneCount = 0,
                               GradeTwoCount = 0,
                               GradeThreeCount = 0,
                               AfterGradeFourCount = 0,
                               PlanGraduateCount = 0
                           }).Concat(
                           from student in StudentRepository.Entities
                           join inschool in InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true)
                                on student.InSchoolStatusID equals inschool.InSchoolStatusID
                           join recruit in RecruitstudentsRepository.Entities on student.UserID equals recruit.UserID
                           join schoolyear in SchoolyearRepository.Entities.Where(x => x.Years == year)
                                on recruit.EnteringSchoolYearID equals schoolyear.SchoolyearID
                           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 examineeType in ExamineeTypeSettingRepository.Entities on recruit.ExamineeType equals examineeType.ExamineeTypeID into dexamineeType
                           from examineeType in dexamineeType.DefaultIfEmpty()
                           group examineeType by new { faculty.FacultymajorID, student.SexID } into g
                           select new
                           {
                               FacultymajorID = g.Key.FacultymajorID,
                               SexID = g.Key.SexID,
                               LastGraduateCount = 0,
                               RecruitstudentsCount = g.Count(),
                               FreshGraduateCount = g.Sum(x => x.IsFreshGraduate == true ? 1 : 0),
                               GradeOneCount = 0,
                               GradeTwoCount = 0,
                               GradeThreeCount = 0,
                               AfterGradeFourCount = 0,
                               PlanGraduateCount = 0
                           }).Concat(
                           from student in StudentRepository.Entities
                           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 graSchoolyear in SchoolyearRepository.Entities on grade.GraduateSchoolyearID equals graSchoolyear.SchoolyearID into dgraSchoolyear
                           from graSchoolyear in dgraSchoolyear.DefaultIfEmpty()
                           group new { grade.GradeID, GraduateYear = graSchoolyear.Years } by new { faculty.FacultymajorID, student.SexID } into g
                           select new
                           {
                               FacultymajorID = g.Key.FacultymajorID,
                               SexID = g.Key.SexID,
                               LastGraduateCount = 0,
                               RecruitstudentsCount = 0,
                               FreshGraduateCount = 0,
                               GradeOneCount = g.Sum(x => (year - x.GradeID) == 0 ? 1 : 0),
                               GradeTwoCount = g.Sum(x => (year - x.GradeID) == 1 ? 1 : 0),
                               GradeThreeCount = g.Sum(x => (year - x.GradeID) == 2 ? 1 : 0),
                               AfterGradeFourCount = g.Sum(x => (year - x.GradeID) > 2 ? 1 : 0),
                               PlanGraduateCount = g.Sum(x => x.GraduateYear == year ? 1 : 0)
                           });

            var sql = (from faculty in FacultymajorRepository.Entities
                       join educationType in EducationTypeSettingRepository.Entities.Where(x => x.EducationTypeID == (int)CF_EducationType.Special)
                           on faculty.EducationID equals educationType.EducationID
                       join data in dataSql on faculty.FacultymajorID equals data.FacultymajorID
                       group data by new { faculty.FacultymajorID, faculty.StandardID, educationType.EducationTypeID, data.SexID } into g
                       select new FacultymajorStudentCountView
                       {
                           Year = year,
                           EducationTypeID = g.Key.EducationTypeID,
                           StandardID = g.Key.StandardID,
                           FacultymajorID = g.Key.FacultymajorID,
                           SexID = g.Key.SexID,
                           LastGraduateCount = g.Sum(x => x.LastGraduateCount),
                           RecruitstudentsCount = g.Sum(x => x.RecruitstudentsCount),
                           FreshGraduateCount = g.Sum(x => x.FreshGraduateCount),
                           GradeOneCount = g.Sum(x => x.GradeOneCount),
                           GradeTwoCount = g.Sum(x => x.GradeTwoCount),
                           GradeThreeCount = g.Sum(x => x.GradeThreeCount),
                           AfterGradeFourCount = g.Sum(x => x.AfterGradeFourCount),
                           PlanGraduateCount = g.Sum(x => x.PlanGraduateCount)
                       });

            return sql;
        }
    }
}