LIOT/EMiS.DataLogic/ScoreManage/ExaminationScoreDAL.cs

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

namespace EMIS.DataLogic.ScoreManage
{
    public class ExaminationScoreDAL
    {
        public ExaminationScoreRepository examinationScoreRepository { get; set; }
        public ExaminationBatchProjectRepository examinationBatchProjectRepository { get; set; }
        public ExaminationBatchRepository examinationBatchRepository { get; set; }
        public ExaminationProjectRepository examinationProjectRepository { get; set; }
        public ExaminationProjectSubjectRepository examinationProjectSubjectRepository { get; set; }
        public ExaminationTypeRepository examinationTypeRepository { get; set; }
        public UserRepository userRepository { get; set; }
        public StudentRepository studentRepository { get; set; }
        public ClassmajorRepository classmajorRepository { get; set; }
        public GrademajorRepository grademajorRepository { get; set; }
        public FacultymajorRepository facultymajorRepository { get; set; }
        public CollegeRepository collegeRepository { get; set; }
        public SchoolyearRepository schoolyearRepository { get; set; }
        public ResitRepository resitRepository { get; set; }
        public ProjectScoreRepository projectScoreRepository { get; set; }

        public IQueryable<ExaminationScoreView> GetExaminationScoreView(Expression<Func<ER_ExaminationScore, bool>> esexp, Expression<Func<EX_ExaminationBatchProject, bool>> ebrexp,
            Expression<Func<EX_ExaminationBatch, bool>> ebexp, Expression<Func<EX_ExaminationProject, bool>> epexp, Expression<Func<CF_Classmajor, bool>> claexp,
            Expression<Func<CF_Grademajor, bool>> graexp, Expression<Func<CF_Facultymajor, bool>> facexp, Expression<Func<CF_College, bool>> colexp)
        {
            var query = from esr in examinationScoreRepository.Entities.Where(esexp)
                        from ebpr in examinationBatchProjectRepository.Entities.Where(ebrexp).Where(x => x.ExaminationBatchProjectID == esr.ExaminationBatchProjectID)
                        from ebr in examinationBatchRepository.Entities.Where(ebexp).Where(x => x.ExaminationBatchID == ebpr.ExaminationBatchID)
                        from sch in schoolyearRepository.Entities.Where(x => x.SchoolyearID == ebr.SchoolyearID)
                        from epr in examinationProjectRepository.Entities.Where(epexp).Where(x => x.ExaminationProjectID == ebpr.ExaminationProjectID)
                        from etr in examinationTypeRepository.Entities.Where(x => x.ExaminationTypeID == epr.ExaminationTypeID)
                        from us in userRepository.Entities.Where(x => x.UserID == esr.UserID)
                        from stu in studentRepository.Entities.Where(x => x.UserID == esr.UserID)
                        from cla in classmajorRepository.Entities.Where(claexp).Where(x => x.ClassmajorID == stu.ClassmajorID)
                        from gra in grademajorRepository.Entities.Where(graexp).Where(x => x.GrademajorID == cla.GrademajorID)
                        from fac in facultymajorRepository.Entities.Where(facexp).Where(x => x.FacultymajorID == gra.FacultymajorID)
                        from col in collegeRepository.Entities.Where(colexp).Where(x => x.CollegeID == fac.CollegeID)
                        select new ExaminationScoreView
                        {
                            ExaminationScoreID = esr.ExaminationScoreID,
                            ExaminationBatchProjectID = esr.ExaminationBatchProjectID,
                            SchoolyearID = ebr.SchoolyearID,
                            SchoolyearCode = sch.Code,
                            ExaminationBatchID = ebpr.ExaminationBatchID,
                            ExaminationBatchName = ebr.Name,
                            ExaminationTypeID = epr.ExaminationTypeID,
                            ExaminationTypeName = etr.Name,
                            ExaminationProjectID = epr.ExaminationProjectID,
                            ExaminationProjectName = epr.Name,
                            ExaminationSubjectID = esr.ExaminationSubjectID,
                            CollegeID = col.CollegeID,
                            CollegeName = col.Name,
                            SchoolyearNumID = gra.SchoolyearID,
                            StandardID = fac.StandardID,
                            ClassmajorID = cla.ClassmajorID,
                            ClassmajorName = cla.Name,
                            StudentNo = stu.StudentCardNo,
                            UserID = us.UserID,
                            UserIDForIDNumber = us.UserID,
                            UserName = us.Name,
                            CertificatesType = stu.CertificatesType,
                            IDNumber = stu.IDNumber,
                            Score = esr.Score,
                            Expire = esr.Expire,
                            CampusID = col.CampusID,
                            Remark = esr.Remark,
                            CreateTime = esr.CreateTime,
                            SchoolAreaID = gra.SchoolAreaID,
                        };
            return query;
        }

        public IQueryable<ER_ProjectScore> GetProjectScoreByExaminationScore(Expression<Func<ER_ExaminationScore, bool>> exp)
        {
            var today = DateTime.Today;

            var sql = (from score in examinationScoreRepository.GetList(exp) 
                       from batchProject in examinationBatchProjectRepository.Entities.Where(x => score.ExaminationBatchProjectID == x.ExaminationBatchProjectID) 
                       from projectScore in projectScoreRepository.Entities.Where(x => batchProject.ExaminationProjectID == x.ExaminationProjectID && x.UserID == score.UserID)
                       where score.Expire == null || score.Expire >= today 
                       select projectScore);

            return sql;
        }

        public IQueryable<ExaminationScoreView> GetExaminationScoreViewForProjectScore(Expression<Func<ER_ProjectScore, bool>> exp)
        {
            var curDate = DateTime.Today;
            var query = (
                from projectScore in projectScoreRepository.GetList(exp)
                from subject in examinationProjectSubjectRepository.Entities.Where(x => x.ExaminationProjectID == projectScore.ExaminationProjectID)
                from score in (
                    from batchProject in examinationBatchProjectRepository.Entities.Where(x => x.ExaminationProjectID == projectScore.ExaminationProjectID)
                    from score in examinationScoreRepository.Entities.Where(x => x.ExaminationBatchProjectID == batchProject.ExaminationBatchProjectID
                        && x.UserID == projectScore.UserID && x.ExaminationSubjectID == subject.ExaminationSubjectID 
                        && x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                    from batch in examinationBatchRepository.Entities.Where(x => x.ExaminationBatchID == batchProject.ExaminationBatchID)
                    from schoolyear in schoolyearRepository.Entities.Where(x => x.SchoolyearID == batch.SchoolyearID)
                    from project in examinationProjectRepository.Entities.Where(x => x.ExaminationProjectID == batchProject.ExaminationProjectID)
                    from type in examinationTypeRepository.Entities.Where(x => x.ExaminationTypeID == project.ExaminationTypeID)
                    from user in userRepository.Entities.Where(x => x.UserID == score.UserID)
                    from student in studentRepository.Entities.Where(x => x.UserID == score.UserID)
                    from classmajor in classmajorRepository.Entities.Where(x => x.ClassmajorID == student.ClassmajorID)
                    from grade in grademajorRepository.Entities.Where(x => x.GrademajorID == classmajor.GrademajorID)
                    from faculty in facultymajorRepository.Entities.Where(x => x.FacultymajorID == grade.FacultymajorID)
                    from college in collegeRepository.Entities.Where(x => x.CollegeID == faculty.CollegeID)
                    where !score.Expire.HasValue || score.Expire >= curDate
                    orderby score.Score descending
                    select new ExaminationScoreView
                    {
                        ExaminationScoreID = score.ExaminationScoreID,
                        ExaminationBatchProjectID = score.ExaminationBatchProjectID,
                        SchoolyearID = batch.SchoolyearID,
                        SchoolyearCode = schoolyear.Code,
                        ExaminationBatchID = batchProject.ExaminationBatchID,
                        ExaminationBatchName = batch.Name,
                        ExaminationTypeID = project.ExaminationTypeID,
                        ExaminationTypeName = type.Name,
                        ExaminationProjectID = project.ExaminationProjectID,
                        ExaminationProjectName = project.Name,
                        ExaminationSubjectID = score.ExaminationSubjectID,
                        CollegeID = college.CollegeID,
                        CollegeName = college.Name,
                        SchoolyearNumID = grade.SchoolyearID,
                        StandardID = faculty.StandardID,
                        ClassmajorID = classmajor.ClassmajorID,
                        ClassmajorName = classmajor.Name,
                        StudentNo = student.StudentCardNo,
                        UserID = user.UserID,
                        UserIDForIDNumber = user.UserID,
                        UserName = user.Name,
                        CertificatesType = student.CertificatesType,
                        IDNumber = student.IDNumber,
                        Score = score.Score,
                        Expire = score.Expire,
                        CampusID = college.CampusID,
                        Remark = score.Remark,
                        CreateTime = score.CreateTime,
                        SchoolAreaID = grade.SchoolAreaID,
                    }
                    ).Take(1)
                select score
                );
            return query;
        }
    }
}