LIOT/EMiS.DataLogic/EducationManage/ExaminationMessageDAL.cs

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

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

        public IQueryable<ExaminationMessageView> GetExaminationMessageViewQueryable(Expression<Func<EX_ExaminationBatch, bool>> batchExp,
            Expression<Func<EX_ExaminationProject, bool>> projectExp, Expression<Func<CF_Facultymajor, bool>> facultyExp,
            Expression<Func<CF_Grademajor, bool>> gradeExp, Expression<Func<CF_Classmajor, bool>> classExp)
        {
            var sql = (from regist in ExaminationRegistrationRepository.Entities
                       from batchProject in ExaminationBatchProjectRepository.Entities.Where(x => x.ExaminationBatchProjectID == regist.ExaminationBatchProjectID)
                       from project in ExaminationProjectRepository.Entities.Where(projectExp).Where(x => x.ExaminationProjectID == batchProject.ExaminationProjectID)
                       from type in ExaminationTypeRepository.Entities.Where(x => x.ExaminationTypeID == project.ExaminationTypeID)
                       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 student in StudentRepository.Entities.Where(x => x.UserID == regist.UserID)
                       from user in UserRepository.Entities.Where(x => x.UserID == student.UserID)
                       from classmajor in ClassmajorRepository.Entities.Where(classExp).Where(x => x.ClassmajorID == student.ClassmajorID)
                       from grade in GrademajorRepository.Entities.Where(gradeExp).Where(x => x.GrademajorID == classmajor.GrademajorID)
                       from faculty in FacultymajorRepository.Entities.Where(facultyExp).Where(x => x.FacultymajorID == grade.FacultymajorID)
                       from college in CollegeRepository.Entities.Where(x => x.CollegeID == faculty.CollegeID)
                       from message in ExaminationMessageRepository.Entities.Where(x => x.ExaminationRegistrationID == regist.ExaminationRegistrationID).DefaultIfEmpty()
                       where regist.RecordStatus == (int)EX_ExaminationRegistrationStatus.Paid 
                       select new ExaminationMessageView
                       {
                           ExaminationRegistrationID = regist.ExaminationRegistrationID,
                           SchoolyearID = schoolyear.SchoolyearID,
                           SchoolyearCode = schoolyear.Code,
                           ExaminationBatchID = batch.ExaminationBatchID,
                           ExaminationBatchName = batch.Name,
                           ExaminationTypeID = type.ExaminationTypeID,
                           ExaminationTypeName = type.Name,
                           ExaminationProjectID = project.ExaminationProjectID,
                           ExaminationProjectName = project.Name,
                           ClassmajorID = classmajor.ClassmajorID,
                           ClassmajorName = classmajor.Name,
                           UserID = student.UserID,
                           UserName = user.Name,
                           Sex = student.Sex,
                           StudentCardNo = student.StudentCardNo,
                           CertificatesType = student.CertificatesType,
                           IDNumber = student.IDNumber,
                           ExaminationTime = message.ExaminationTime,
                           Remark = message.Remark
                       });

            return sql;
        }
    }
}