JW/EMiS.DataLogic/Common/Students/HighBaseTable/RecruitTerritorialReportDAL.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;

namespace EMIS.DataLogic.Common.Students.HighBaseTable
{
    public class RecruitTerritorialReportDAL
    {
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public DictionaryItemRepository DictionaryItemRepository { get; set; }
        public InSchoolSettingRepository InSchoolSettingRepository { get; set; }
        public EducationTypeSettingRepository EducationTypeSettingRepository { get; set; }
        public ExamineeTypeSettingRepository ExamineeTypeSettingRepository { get; set; }
        public FeaturesSettingRepository FeaturesSettingRepository { get; set; }
        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 IQueryable<RecruitTerritorialReportView> GetRecruitTerritorialReportView(int year)
        {
            var sql = (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 territorial in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(CF_Province).Name)
                           on recruit.TerritorialID equals territorial.Value into dterritorial
                       from territorial in dterritorial.DefaultIfEmpty() 
                       join educationType in EducationTypeSettingRepository.Entities
                            .Where(x => x.EducationTypeID == (int)CF_EducationType.Special)
                            on faculty.EducationID equals educationType.EducationID
                       join recruitCategory in FeaturesSettingRepository.Entities on recruit.FeaturesID equals recruitCategory.FeaturesID into drecruitCategory
                       from recruitCategory in drecruitCategory.DefaultIfEmpty()
                       join examineeType in ExamineeTypeSettingRepository.Entities on recruit.ExamineeType equals examineeType.ExamineeTypeID into dexamineeType
                       from examineeType in dexamineeType.DefaultIfEmpty()
                       group student by new { Territorial = territorial.Value, educationType.EducationTypeID, recruitCategory.RecruitCategoryID, examineeType.IsFreshGraduate, examineeType.IsCountry } into g
                       select new RecruitTerritorialReportView 
                       { 
                           Year = year,
                           Territorial = g.Key.Territorial,
                           EducationTypeID = g.Key.EducationTypeID,
                           RecruitCategoryID = g.Key.RecruitCategoryID,
                           IsFreshGraduate = g.Key.IsFreshGraduate,
                           IsCountry = g.Key.IsCountry,
                           RecruitCount = g.Count()
                       }
                           );

            return sql;
        }
    }
}