LIOT/EMiS.DataLogic/EducationManage/TrainingClassDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using EMIS.DataLogic.Repositories;
using EMIS.Entities;
using EMIS.ViewModel;
using EMIS.ViewModel.EducationManage;

namespace EMIS.DataLogic.EducationManage
{
    public class TrainingClassDAL
    {
        public TrainingClassRepository TrainingClassRepository { get; set; }
        public ExaminationBatchProjectRepository ExaminationBatchProjectRepository { get; set; }
        public ExaminationBatchRepository ExaminationBatchRepository { get; set; }
        public ExaminationProjectRepository ExaminationProjectRepository { get; set; }
        public ExaminationTypeRepository ExaminationTypeRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public ExaminationRegistrationRepository ExaminationRegistrationRepository { get; set; }
        public ExaminationProjectFeeTypeRepository ExaminationProjectFeeTypeRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }
        public StudentContactRepository StudentContactRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }

        public IQueryable<TrainingClassView> GetTrainingClassViewQueryable(Expression<Func<EM_TrainingClass, bool>> exp,
            Expression<Func<EX_ExaminationBatch, bool>> batchExp, Expression<Func<EX_ExaminationProject, bool>> projectExp)
        {
            var sql = (from train in TrainingClassRepository.GetList(exp) 
                       from batchProject in ExaminationBatchProjectRepository.Entities.Where(x => x.ExaminationBatchProjectID == train.ExaminationBatchProjectID) 
                       from batch in ExaminationBatchRepository.Entities.Where(batchExp).Where(x => x.ExaminationBatchID == batchProject.ExaminationBatchID) 
                       from project in ExaminationProjectRepository.Entities.Where(projectExp).Where(x => x.ExaminationProjectID == batchProject.ExaminationProjectID) 
                       from schoolyear in SchoolyearRepository.Entities.Where(x => x.SchoolyearID == batch.SchoolyearID) 
                       from type in ExaminationTypeRepository.Entities.Where(x => x.ExaminationTypeID == project.ExaminationTypeID) 
                       select new TrainingClassView
                       {
                           TrainingClassID = train.TrainingClassID,
                           SchoolyearID = batch.SchoolyearID,
                           SchoolyearCode = schoolyear.Code,
                           ExaminationBatchID = batchProject.ExaminationBatchID,
                           ExaminationBatchName = batch.Name,
                           ExaminationBatchCreateTime = batch.CreateTime,
                           ExaminationTypeID = project.ExaminationTypeID,
                           ExaminationTypeName = type.Name,
                           ExaminationProjectID = batchProject.ExaminationProjectID,
                           ExaminationProjectName = project.Name,
                           SchoolAreaID = train.SchoolAreaID,
                           Name = train.Name,
                           StudentCount = train.CF_Student.Count()
                       });

            return sql;
        }

        public IQueryable<GenerateTrainingClassView> GetGenerateTrainingClassViewByRegistration(Expression<Func<EX_ExaminationBatch, bool>> batchExp,
            Expression<Func<EX_ExaminationProject, bool>> projectExp, Expression<Func<CF_Grademajor, bool>> gradeExp)
        {
            var sql = (from regist in ExaminationRegistrationRepository.Entities
                       from batchProject in ExaminationBatchProjectRepository.Entities.Where(x => x.ExaminationBatchProjectID == regist.ExaminationBatchProjectID)
                       from batch in ExaminationBatchRepository.Entities.Where(batchExp).Where(x => x.ExaminationBatchID == batchProject.ExaminationBatchID)
                       from schoolyear in SchoolyearRepository.Entities.Where(x => x.SchoolyearID == batch.SchoolyearID)
                       from project in ExaminationProjectRepository.Entities.Where(projectExp).Where(x => x.ExaminationProjectID == batchProject.ExaminationProjectID) 
                       from feeType in ExaminationProjectFeeTypeRepository.Entities.Where(x => x.ExaminationProjectFeeTypeID == regist.ExaminationProjectFeeTypeID)
                       from student in StudentRepository.Entities.Where(x => x.UserID == regist.UserID)
                       from classmajor in ClassmajorRepository.Entities.Where(x => x.ClassmajorID == student.ClassmajorID) 
                       from grade in GrademajorRepository.Entities.Where(gradeExp).Where(x => x.GrademajorID == classmajor.GrademajorID) 
                       from train in student.EM_TrainingClass.Where(x => x.ExaminationBatchProjectID == batchProject.ExaminationBatchProjectID).DefaultIfEmpty() 
                       where train.TrainingClassID == null && feeType.IsTrainingFee == true && regist.RecordStatus == (int)EX_ExaminationRegistrationStatus.Paid 
                       select new GenerateTrainingClassView
                       {
                           SchoolyearID = schoolyear.SchoolyearID,
                           SchoolyearCode = schoolyear.Code,
                           ExaminationBatchProjectID = batchProject.ExaminationBatchProjectID,
                           ExaminationBatchID = batch.ExaminationBatchID,
                           ExaminationBatchName = batch.Name,
                           ExaminationProjectID = project.ExaminationProjectID,
                           ExaminationProjectName = project.Name,
                           SchoolAreaID = grade.SchoolAreaID,
                           UserID = student.UserID
                       }
                       );

            return sql;
        }

        public IQueryable<TrainingClassStudentView> GetTrainingClassStudentViewQueryable(Expression<Func<EM_TrainingClass, bool>> exp, Expression<Func<CF_Facultymajor, bool>> facultExp,
            Expression<Func<CF_Grademajor, bool>> grademajorExp, Expression<Func<CF_Classmajor, bool>> classmajorExp, Expression<Func<Sys_User, bool>> userExp, 
            Expression<Func<CF_Student, bool>> studentExp)
        {
            var sql = (from train in TrainingClassRepository.GetList(exp) 
                       from trainstudent in train.CF_Student 
                       from student in StudentRepository.Entities.Where(studentExp).Where(x => x.UserID == trainstudent.UserID) 
                       from contact in StudentContactRepository.Entities.Where(x => x.UserID == student.UserID) 
                       from user in UserRepository.Entities.Where(userExp).Where(x => x.UserID == student.UserID)
                       from classmajor in ClassmajorRepository.Entities.Where(classmajorExp).Where(x => x.ClassmajorID == student.ClassmajorID)
                       from grade in GrademajorRepository.Entities.Where(grademajorExp).Where(x => x.GrademajorID == classmajor.GrademajorID)
                       from faculty in FacultymajorRepository.Entities.Where(facultExp).Where(x => x.FacultymajorID == grade.FacultymajorID)
                       from college in CollegeRepository.Entities.Where(x => x.CollegeID == faculty.CollegeID) 
                       select new TrainingClassStudentView
                       {
                           UserID = student.UserID,
                           SchoolAreaID = grade.SchoolAreaID,
                           CollegeID = college.CollegeID,
                           CollegeName = college.Name,
                           GrademajorID = grade.GrademajorID,
                           Year = grade.SchoolyearID,
                           ClassmajorID = classmajor.ClassmajorID,
                           ClassmajorName = classmajor.Name,
                           UserName = user.Name,
                           Sex = student.Sex,
                           CertificatesType = student.CertificatesType,
                           IDNumber = student.IDNumber,
                           Mobile = contact.Mobile
                       });

            return sql;
        }

        public IQueryable<TrainingClassExportView> GetTrainingClassExportViewQueryable(Expression<Func<EM_TrainingClass, bool>> exp,
            Expression<Func<EX_ExaminationBatch, bool>> batchExp, Expression<Func<EX_ExaminationProject, bool>> projectExp)
        {
            var sql = (from train in TrainingClassRepository.GetList(exp)
                       from batchProject in ExaminationBatchProjectRepository.Entities.Where(x => x.ExaminationBatchProjectID == train.ExaminationBatchProjectID)
                       from batch in ExaminationBatchRepository.Entities.Where(batchExp).Where(x => x.ExaminationBatchID == batchProject.ExaminationBatchID)
                       from project in ExaminationProjectRepository.Entities.Where(projectExp).Where(x => x.ExaminationProjectID == batchProject.ExaminationProjectID)
                       from schoolyear in SchoolyearRepository.Entities.Where(x => x.SchoolyearID == batch.SchoolyearID)
                       from type in ExaminationTypeRepository.Entities.Where(x => x.ExaminationTypeID == project.ExaminationTypeID)
                       from student in train.CF_Student
                       from contact in StudentContactRepository.Entities.Where(x => x.UserID == student.UserID)
                       from user in UserRepository.Entities.Where(x => x.UserID == student.UserID)
                       from classmajor in ClassmajorRepository.Entities.Where(x => x.ClassmajorID == student.ClassmajorID)
                       from grade in GrademajorRepository.Entities.Where(x => x.GrademajorID == classmajor.GrademajorID)
                       select new TrainingClassExportView
                       {
                           TrainingClassID = train.TrainingClassID,
                           SchoolyearID = batch.SchoolyearID,
                           SchoolyearCode = schoolyear.Code,
                           ExaminationBatchID = batchProject.ExaminationBatchID,
                           ExaminationBatchName = batch.Name,
                           ExaminationBatchCreateTime = batch.CreateTime,
                           ExaminationTypeID = project.ExaminationTypeID,
                           ExaminationTypeName = type.Name,
                           ExaminationProjectID = batchProject.ExaminationProjectID,
                           ExaminationProjectName = project.Name,
                           UserID = student.UserID,
                           GrademajorID = grade.GrademajorID,
                           Year = grade.SchoolyearID,
                           ClassmajorID = classmajor.ClassmajorID,
                           ClassmajorName = classmajor.Name,
                           UserName = user.Name,
                           Sex = student.Sex,
                           CertificatesType = student.CertificatesType,
                           IDNumber = student.IDNumber,
                           Mobile = contact.Mobile,
                           Name = train.Name
                       });

            return sql;
        }

        public IQueryable<TrainingClassSplitView> GetTrainingClassSplitViewQueryable(Expression<Func<EM_TrainingClass, bool>> exp)
        {
            var sql = (from train in TrainingClassRepository.GetList(exp)
                       select new TrainingClassSplitView
                       {
                           TrainingClassID = train.TrainingClassID,
                           Name = train.Name,
                           StudentCount = train.CF_Student.Count(),
                           ClassmajorCount = train.CF_Student.Select(x => x.ClassmajorID).Distinct().Count()
                       });

            return sql;
        }
    }
}