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

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

namespace EMIS.DataLogic.Common.Students.HighBaseTable
{
    public class RecruitStatusDAL
    {
        public RecruitStatusRepository RecruitStatusRepository { get; set; }
        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 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 StudentReportRepository StudentReportRepository { get; set; }

        public IQueryable<RecruitStatusView> GetRecruitStatusViewQueryable(int? schoolYear)
        {
            //var schYear = SchoolYearDAL.GetSchoolYearQueryable(x => x.IsCurrent).FirstOrDefault().Years;
            var query = from student in StudentRepository.Entities
                        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 edutype in EducationTypeSettingRepository.Entities on famajor.EducationID equals edutype.EducationID
                        join entyear in SchoolyearRepository.Entities on recruit.EnteringSchoolYearID equals entyear.SchoolyearID 
                        join inschool in InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true) on student.InSchoolStatusID equals inschool.InSchoolStatusID into dinschool 
                        from inschool in dinschool.DefaultIfEmpty() 
                        //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 into tmp
                        //from report in tmp.DefaultIfEmpty()
                        //join schyear in SchoolyearRepository.GetList(x => x.IsCurrent == true) on schyear.
                        //join diction in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(CF_STUDENTTYPE).Name) on student.StudentType equals diction.Value
                        //into tmp from diction in tmp.DefaultIfEmpty()
                        //join diction1 in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(CF_StudentStatus).Name) on student.StudentStatus equals diction1.Value
                        //into tmp1 from diction1 in tmp1.DefaultIfEmpty()
                        //join diction2 in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(CF_EducationType).Name) on edutype.EducationTypeID equals diction2.Value
                        //into tmp2 from diction2 in tmp2.DefaultIfEmpty()
                        //join diction3 in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(CF_Province).Name) on recruit.Territorial equals diction3.Value
                        //into tmp3 from diction3 in tmp3.DefaultIfEmpty()

                        select new RecruitStatusView
                        {
                            //Year = Year,
                            StudentType = student.StudentType.Value,
                            Territorial = recruit.TerritorialID.Value,
                            EducationTypeID = edutype.EducationTypeID.Value,
                            IsRecruitStudent = (entyear.Years == schoolYear && inschool.InSchoolSettingID != null) ? true : false,
                            IsInSchoolStudent = (inschool.InSchoolSettingID != null),
                            //RecruitCount=
                            //InschoolCount

                        }
                            ;
            return query;
        }

        public IQueryable<RecruitStatusView> GetRecruitStatusInschoolCount(int? schYear)
        {
            var query = from student in StudentRepository.GetList(x => x.InSchoolStatusID == (int)EMIS.ViewModel.CF_InschoolStatus.InSchool)
                        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 edutype in EducationTypeSettingRepository.Entities on famajor.EducationID equals edutype.EducationID
                        join entyear in SchoolyearRepository.Entities on recruit.EnteringSchoolYearID equals entyear.SchoolyearID
                        //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 == schYear
                        //        select report.UserID
                        //        ).Distinct() on student.UserID equals report

                        group student by new { recruit.TerritorialID, edutype.EducationTypeID } into g
                        select new RecruitStatusView
                        {
                            //StudentType = g.Key.StudentType,
                            Territorial = g.Key.TerritorialID,
                            EducationTypeID = g.Key.EducationTypeID,
                            InschoolCount = g.Count()
                        };
            return query;
        }
        public IQueryable<RecruitStatusView> GetRecruitStatusRecruitCount(int? schoolYear)
        {
            //var schYear = SchoolYearDAL.GetSchoolYearQueryable(x => x.IsCurrent).FirstOrDefault().Years;
            var query = from student in StudentRepository.GetList(x => x.InSchoolStatusID == (int)EMIS.ViewModel.CF_InschoolStatus.InSchool)
                        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 edutype in EducationTypeSettingRepository.Entities on famajor.EducationID equals edutype.EducationID
                        join entyear in SchoolyearRepository.GetList(x => x.Years == schoolYear) on recruit.EnteringSchoolYearID equals entyear.SchoolyearID
                        //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 { recruit.TerritorialID, edutype.EducationTypeID } into g
                        select new RecruitStatusView
                        {
                            //StudentType = g.Key.StudentType,
                            Territorial = g.Key.TerritorialID,
                            EducationTypeID = g.Key.EducationTypeID,
                            RecruitCount = g.Count()
                        };
            return query;
        }
    }
}