LIOT/EMiS.DataLogic/ScoreManage/ProjectScoreDAL.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.ScoreManage;

namespace EMIS.DataLogic.ScoreManage
{
    public class ProjectScoreDAL
    {
        public ProjectScoreRepository ProjectScoreRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { 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 ExaminationProjectRepository ExaminationProjectRepository { get; set; }
        public ExaminationTypeRepository ExaminationTypeRepository { get; set; }
        public LevelSettingRepository LevelSettingRepository { get; set; }
        public TrainingClassRepository TrainingClassRepository { get; set; }
        public ExaminationBatchProjectRepository ExaminationBatchProjectRepository { get; set; }
        public ExaminationBatchRepository ExaminationBatchRepository { get; set; }
        public ExaminationScoreRepository ExaminationScoreRepository { get; set; }
        public CertisfierDistributeRepository CertisfierDistributeRepository { get; set; }

        public IQueryable<ProjectScoreView> GetProjectScoreViewQueryable(Expression<Func<ER_ProjectScore, bool>> exp,
            Expression<Func<EX_ExaminationProject, bool>> projectExp, Expression<Func<CF_Facultymajor, bool>> facultExp, 
            Expression<Func<CF_Grademajor, bool>> grademajorExp, Expression<Func<CF_Classmajor, bool>> classmajorExp)
        {
            var sql = (from score in ProjectScoreRepository.GetList(exp) 
                       from schoolyear in SchoolyearRepository.Entities.Where(x => x.SchoolyearID == score.SchoolyearID) 
                       from user in UserRepository.Entities.Where(x => x.UserID == score.UserID) 
                       from student in StudentRepository.Entities.Where(x => x.UserID == user.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) 
                       from project in ExaminationProjectRepository.Entities.Where(projectExp).Where(x => x.ExaminationProjectID == score.ExaminationProjectID) 
                       from type in ExaminationTypeRepository.Entities.Where(x => x.ExaminationTypeID == project.ExaminationTypeID) 
                       from level in LevelSettingRepository.Entities.Where(x => x.LevelSettingID == score.LevelSettingID) 
                       from train in (
                            from train1 in TrainingClassRepository.Entities 
                            from student1 in train1.CF_Student 
                            from batchProject1 in ExaminationBatchProjectRepository.Entities.Where(x => x.ExaminationBatchProjectID == train1.ExaminationBatchProjectID)
                            from batch1 in ExaminationBatchRepository.Entities.Where(x => x.ExaminationBatchID == batchProject1.ExaminationBatchID)
                            from schoolyear1 in SchoolyearRepository.Entities.Where(x => x.SchoolyearID == batch1.SchoolyearID)
                            where batchProject1.ExaminationProjectID == project.ExaminationProjectID && student1.UserID == score.UserID 
                            orderby new { schoolyear1.Code, batch1.CreateTime, TrainCreateTime = train1.CreateTime } 
                            select train1
                       ).Take(1).DefaultIfEmpty() 
                       select new ProjectScoreView
                       {
                           ProjectScoreID = score.ProjectScoreID,
                           SchoolyearID = score.SchoolyearID,
                           SchoolyearCode = schoolyear.Code,
                           UserID = score.UserID,
                           UserName = user.Name,
                           CertificatesType = student.CertificatesType,
                           IDNumber = student.IDNumber,
                           CollegeID = faculty.CollegeID,
                           CollegeName = college.Name,
                           Year = grade.SchoolyearID,
                           StandardID = faculty.StandardID,
                           ClassmajorID = student.ClassmajorID,
                           ClassmajorName = classmajor.Name,
                           ExaminationTypeID = project.ExaminationTypeID,
                           ExaminationTypeName = type.Name,
                           ExaminationProjectID = score.ExaminationProjectID,
                           ExaminationProjectName = project.Name,
                           LevelSettingID = score.LevelSettingID,
                           LevelName = level.LevelName,
                           TrainingClassID = train.TrainingClassID,
                           TrainingClassName = train.Name
                       });

            return sql;
        }

        public IQueryable<ProjectScoreGenerateScoreView> GetAllExaminationScoreViewByBatchStudent(Expression<Func<EX_ExaminationBatch, bool>> batchExp,
            Expression<Func<EX_ExaminationProject, bool>> projectExp)
        {
            var sql = (from batch in ExaminationBatchRepository.GetList(batchExp)
                       from batchProject in ExaminationBatchProjectRepository.Entities.Where(x => x.ExaminationBatchID == batch.ExaminationBatchID)
                       from project in ExaminationProjectRepository.Entities.Where(projectExp).Where(x => x.ExaminationProjectID == batchProject.ExaminationProjectID)
                       from curScore in ExaminationScoreRepository.Entities.Where(x => x.ExaminationBatchProjectID == batchProject.ExaminationBatchProjectID)
                       from user in UserRepository.Entities.Where(x => x.UserID == curScore.UserID)
                       from allScore in (
                            from score1 in ExaminationScoreRepository.Entities
                            from batchProject1 in ExaminationBatchProjectRepository.Entities.Where(x => x.ExaminationBatchProjectID == score1.ExaminationBatchProjectID)
                            from batch1 in ExaminationBatchRepository.Entities.Where(x => x.ExaminationBatchID == batchProject1.ExaminationBatchID)
                            select new { Score = score1, ProjectID = batchProject1.ExaminationProjectID, UserID = score1.UserID, BatchStartDate = batch1.StartDate }
                       ).Where(x => x.ProjectID == project.ExaminationProjectID && x.UserID == curScore.UserID)
                       select new ProjectScoreGenerateScoreView
                       {
                           ExaminationScoreID = allScore.Score.ExaminationScoreID,
                           ExaminationProjectID = project.ExaminationProjectID,
                           ExaminationSubjectID = allScore.Score.ExaminationSubjectID,
                           UserID = user.UserID,
                           Score = allScore.Score.Score,
                           IsResit = allScore.Score.IsResit,
                           Expire = allScore.Score.Expire,
                           BatchStartDate = allScore.BatchStartDate,
                           ExaminationBatchProjectID = batchProject.ExaminationBatchProjectID,
                           SchoolYearID = batch.SchoolyearID,
                       });

            return sql;
        }

        public IQueryable<ProjectScoreGenerateScoreView> GetExaminationScoreViewByBatchStudent(Expression<Func<EX_ExaminationBatch, bool>> batchExp,
            Expression<Func<EX_ExaminationProject, bool>> projectExp)
        {
            var nowTime = DateTime.Today;

            var sql = (from batch in ExaminationBatchRepository.GetList(batchExp)
                       from batchProject in ExaminationBatchProjectRepository.Entities.Where(x => x.ExaminationBatchID == batch.ExaminationBatchID)
                       from project in ExaminationProjectRepository.Entities.Where(projectExp).Where(x => x.ExaminationProjectID == batchProject.ExaminationProjectID)
                       from score in ExaminationScoreRepository.Entities.Where(x => x.ExaminationBatchProjectID == batchProject.ExaminationBatchProjectID)
                       from user in UserRepository.Entities.Where(x => x.UserID == score.UserID)
                       select new ProjectScoreGenerateScoreView
                       {
                           ExaminationScoreID = score.ExaminationScoreID,
                           ExaminationProjectID = project.ExaminationProjectID,
                           ExaminationSubjectID = score.ExaminationSubjectID,
                           UserID = user.UserID,
                           Score = score.Score,
                           IsResit = score.IsResit,
                           Expire = score.Expire,
                           BatchStartDate = batch.StartDate
                       });

            return sql;
        }

        public IQueryable<ProjectScoreDetailView> GetProjectScoreDetailViewQueryable(Expression<Func<ER_ProjectScore, bool>> exp)
        {
            var sql = (from score in ProjectScoreRepository.GetList(exp) 
                       from batch in ExaminationBatchRepository.Entities
                        .Where(x => x.SchoolyearID == score.SchoolyearID) 
                       from batchProject in ExaminationBatchProjectRepository.Entities
                        .Where(x => x.ExaminationBatchID == batch.ExaminationBatchID 
                        && x.ExaminationProjectID == score.ExaminationProjectID) 
                       from examScore in ExaminationScoreRepository.Entities
                        .Where(x => x.ExaminationBatchProjectID == batchProject.ExaminationBatchProjectID 
                        && x.UserID == score.UserID) 
                       from schoolyear in SchoolyearRepository.Entities.Where(x => x.SchoolyearID == score.SchoolyearID)
                       select new ProjectScoreDetailView
                       {
                           ExaminationScoreID = examScore.ExaminationScoreID,
                           ExaminationSubjectID = examScore.ExaminationSubjectID,
                           Score = examScore.Score,
                           CreateTime = examScore.CreateTime,
                           SchoolyearID = schoolyear.SchoolyearID,
                           SchoolyearCode = schoolyear.Code,
                           ExaminationBatchID = batch.ExaminationBatchID,
                           ExaminationBatchName = batch.Name
                       });

            return sql;
        }
    }
}