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

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

namespace EMIS.DataLogic.Common.Students.HighBaseTable
{
    public class InschoolAgeStatusDAL
    {
        public InschoolAgeStatusRepository InschoolAgeStatusRepository { 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<InschoolAgeStatusView> GetStudentBirthDay(int? schoolYear)
        //{
        //    var query = from student in StudentRepository.GetList(x => x.StudentStatus == (int)EMIS.ViewModel.CF_StudentStatus.registration)
        //                join profile in StudentProfileRepository.Entities on student.UserID equals profile.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
        //                select new InschoolAgeStatusView
        //                {
        //                    UserID=student.UserID,
        //                    BirthDate =profile.BirthDate
        //                };
        //    return query;
        //}

        public IQueryable<InschoolAgeStatusView> GetInschoolAgeStatusCount(int? schoolYear)
        {
            //var year = DateTime.Now.Year;
            //var schYear = SchoolYearDAL.GetSchoolYearQueryable(x => x.IsCurrent).FirstOrDefault().Years;
            var query = from student in StudentRepository.Entities
                        join inschool in InSchoolSettingRepository.Entities.Where(x => x.IsSelected == true)
                            on student.InSchoolStatusID equals inschool.InSchoolStatusID 
                        join profile in StudentProfileRepository.Entities on student.UserID equals profile.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 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
                        {
                            student.SexID,
                            edutype.EducationTypeID,
                            age = (schoolYear - student.BirthDate.Value.Year)
                                + ((student.BirthDate.Value.Month >= 9 && student.BirthDate.Value.Day >= 1) ? 0 : 1)
                        } into g
                        select new InschoolAgeStatusView
                        {
                            Age = g.Key.age,
                            //StudentType = g.Key.StudentType,
                            Sex = g.Key.SexID,
                            EducationTypeID = g.Key.EducationTypeID,
                            Count = g.Count()
                        };
            return query;
        }
    }
}