LIOT/EMiS.DataLogic/StudentSystem/Examine/ExamineApplyDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EMIS.DataLogic.Repositories;
using EMIS.ViewModel.Students;
using EMIS.ViewModel;
using EMIS.ViewModel.ExaminationApply;
using EMIS.DataLogic.Common.Students;
using System.Linq.Expressions;

namespace EMIS.DataLogic.StudentSystem.ExamineApply
{
    public class ExamineApplyDAL
    {

        public StudentRepository StudentRepository { get; set; }
        public StudentAccountRepository StudentAccountRepository { get; set; }
        public ExaminationProjectRepository examinationProjectRepository { get; set; }
        public ExaminationTypeRepository examinationTypeRepository { get; set; }
        public ExaminationProjectFeeRepository examinationProjectFeeRepository { get; set; }
        public ExaminationProjectFeeTypeRepository examinationProjectFeeTypeRepository { get; set; }
        public ExaminationRegistrationRepository examinationRegistrationRepository { get; set; }
        public ExaminationProjectCountLimitRepository examinationProjectCountLimitRepository { get; set; }
        public ExaminationProjectCollegeControlRepository examinationProjectCollegeControlRepository { get; set; }
        public ExaminationBatchProjectPersonControlRepository examinationBatchProjectPersonControlRepository { get; set; }
        public ExaminationBatchProjectControlRepository examinationBatchProjectControlRepository { get; set; }
        public ExaminationBatchProjectRepository examinationBatchProjectRepository { get; set; }
        public ExaminationBatchRepository examinationBatchRepository { get; set; }
        public DictionaryItemRepository DictionaryItemRepository { get; set; }
        public ClassmajorRepository ClassRepository { get; set; }
        public GrademajorRepository GradeRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public SchoolyearRepository schoolyearRepository { get; set; }
        public ProjectScoreRepository projectScoreRepository { get; set; }
        public LevelSettingRepository levelSettingRepository { get; set; }
        public StudentsDAL studentsDAL { get; set; }


        public IList<ExaminationApplyView> GetExaminationSubjectList(Guid UserID)
        {
            var currentSchoolyear = schoolyearRepository.Entities.Where(x => x.IsCurrent == true).FirstOrDefault();
            var query = from epr in examinationProjectRepository.Entities
                        from ebpr in examinationBatchProjectRepository.Entities.Where(x => x.ExaminationProjectID == epr.ExaminationProjectID)
                        from ebp in examinationBatchRepository.Entities.Where(x => x.ExaminationBatchID == ebpr.ExaminationBatchID)
                        from sch in schoolyearRepository.Entities.Where(x => x.SchoolyearID == ebp.SchoolyearID)
                        from err in examinationRegistrationRepository.Entities.Where(x => x.ExaminationBatchProjectID == ebpr.ExaminationBatchProjectID && x.UserID == UserID && x.RecordStatus != (int)EX_ExaminationRegistrationStatus.Refunded).DefaultIfEmpty()
                        from etr in examinationTypeRepository.Entities.Where(x => x.ExaminationTypeID == epr.ExaminationTypeID)
                        from stu in StudentRepository.Entities.Where(x => x.UserID == UserID)
                        from cla in ClassRepository.Entities.Where(x => x.ClassmajorID == stu.ClassmajorID)
                        from gra in GradeRepository.Entities.Where(x => x.GrademajorID == cla.GrademajorID)
                        //from school in schoolyearRepository.Entities.Where(x => x.Years == gra.SchoolyearID && x.SchoolcodeID == gra.SchoolcodeID)
                        from fac in FacultymajorRepository.Entities.Where(x => x.FacultymajorID == gra.FacultymajorID)
                            //from col in CollegeRepository.Entities.Where(x => x.CollegeID == fac.CollegeID)
                            //门数控制(考试类型控制)，若该学生已报考门数大于控制门数，则得到不为空的结果（不可以报名）；如果已报考门数小于控制门数，或者没有设置门数控制，则得到为空的结果（可以报名）
                        from epcl in examinationProjectCountLimitRepository.Entities.Where(x => x.ExaminationTypeID == epr.ExaminationTypeID && x.StandardID == fac.StandardID
                            && x.SchoolyearNumID == ((currentSchoolyear.Value - gra.CF_Schoolyear.Value - (currentSchoolyear.SchoolcodeID == gra.SchoolcodeID ? 0 : 1)) / 2 + 1)
                            && x.SubjectCountLimit <= stu.EX_ExaminationRegistration.Count()).DefaultIfEmpty()
                        //from epccr in examinationProjectCollegeControlRepository.Entities.Where(x => x.ExaminationProjectID == epr.ExaminationProjectID && x.StandardID == fac.StandardID)
                        from ebpcr in examinationBatchProjectControlRepository.Entities.Where(x => x.ExaminationBatchProjectID == ebpr.ExaminationBatchProjectID && (x.StandardID == fac.StandardID || x.StandardID == null)
                            && (x.CollegeID == fac.CollegeID || x.CollegeID == null) && (x.SchoolyearID == gra.SchoolyearID || x.SchoolyearID == null)).DefaultIfEmpty()
                            //from err in examinationRegistrationRepository.Entities.Where(x => x.UserID == UserID && x.ExaminationBatchProjectID == ebpr.ExaminationBatchProjectID)
                        from ebppcr in examinationBatchProjectPersonControlRepository.Entities.Where(x => x.ExaminationBatchProjectID == ebpr.ExaminationBatchProjectID && x.UserID == UserID).DefaultIfEmpty()
                        where (epcl.ExaminationSubjectCountLimitID == null && err.ExaminationRegistrationID == null && ebpcr.ExaminationBatchProjectControlID != null)
                        || (epcl.ExaminationSubjectCountLimitID == null && err.ExaminationRegistrationID == null && ebppcr.ExaminationProjectFeeID != null)
                        //批次项目控制不为空或者个人报名控制不为空
                        select new ExaminationApplyView
                        {
                            SchoolYearID = ebp.SchoolyearID,
                            SchoolYearCode = sch.Code,
                            ExaminationProjectID = epr.ExaminationProjectID,
                            ExaminationBatchProjectID = ebpr.ExaminationBatchProjectID,
                            ExaminationTypeID = etr.ExaminationTypeID,
                            ExaminationTypeName = etr.Name,
                            ExaminationLevelID = epr.ExaminationLevelID,
                            ExaminationProjectName = epr.Name,
                            StartDate = ebppcr.ExaminationBatchProjectPersonControlID != null ? ebppcr.StartDate : ebpr.StartDate,
                            EndDate = ebppcr.ExaminationBatchProjectPersonControlID != null ? ebppcr.EndDate : ebpr.EndDate,
                            SimulateTime = ebpr.SimulateTime,
                            MaxRegistCount = ebpr.MaxRegistCount,
                            Remark = ebpr.Remark,
                            RegisterNum = ebpr.EX_ExaminationRegistration.Count(),
                            ExaminationProjectFeeID = ebppcr.ExaminationProjectFeeID == null ? ebpcr.ExaminationProjectFeeID : ebppcr.ExaminationProjectFeeID,
                            ExaminationProjectFeeName = ebppcr.ExaminationProjectFeeID == null ? ebpcr.EX_ExaminationProjectFee.Name : ebppcr.EX_ExaminationProjectFee.Name,
                            TakeCourseTime = ebpr.TakeCourseTime,
                            Training = ebpr.Training,
                            BatchRemark = ebp.Remark,
                        };
            return query.Distinct().ToList();
        }
        public IList<ExaminationRegisView> GetExaminationRegisterList(Guid UserID)
        {
            var currentSchoolyear = schoolyearRepository.Entities.Where(x => x.IsCurrent == true).FirstOrDefault();
            var query = from err in examinationRegistrationRepository.Entities.Where(x => x.UserID == UserID)
                        from stu in StudentRepository.Entities.Where(x => x.UserID == err.UserID)
                        from ebpr in examinationBatchProjectRepository.Entities.Where(x => x.ExaminationBatchProjectID == err.ExaminationBatchProjectID)
                        from ebp in examinationBatchRepository.Entities.Where(x => x.ExaminationBatchID == ebpr.ExaminationBatchID)
                        from epr in examinationProjectRepository.Entities.Where(x => x.ExaminationProjectID == ebpr.ExaminationProjectID)
                        from sch in schoolyearRepository.Entities.Where(x => x.SchoolyearID == ebp.SchoolyearID)
                        from epftr in examinationProjectFeeTypeRepository.Entities.Where(x => x.ExaminationProjectFeeTypeID == err.ExaminationProjectFeeTypeID)
                        from epfr in examinationProjectFeeRepository.Entities.Where(x => x.ExaminationProjectFeeID == epftr.ExaminationProjectFeeID)
                        from feeTypedic in DictionaryItemRepository.Entities.Where(x => x.DictionaryCode == typeof(EX_ExaminationProjectFeeType).Name && x.Value == epftr.FeeTypeID)
                        from ebppcr in examinationBatchProjectPersonControlRepository.Entities.Where(x => x.ExaminationBatchProjectID == ebpr.ExaminationBatchProjectID && x.UserID == UserID).DefaultIfEmpty()
                        select new ExaminationRegisView
                        {
                            ExaminationRegistrationID = err.ExaminationRegistrationID,
                            ExaminationProjectID = epr.ExaminationProjectID,
                            ExaminationProjectName = epr.Name,
                            SchoolYearID = sch.SchoolyearID,
                            SchoolYearCode = sch.Code,
                            StartDate = ebppcr.ExaminationBatchProjectPersonControlID != null ? ebppcr.StartDate : ebpr.StartDate,
                            EndDate = ebppcr.ExaminationBatchProjectPersonControlID != null ? ebppcr.EndDate : ebpr.EndDate,
                            CertificatesType = stu.CertificatesType,
                            IDNumber = stu.IDNumber,
                            FeeTypeID = epftr.FeeTypeID,
                            Fee = epftr.Fee,
                            ExaminationProjectFeeTypeID = err.ExaminationProjectFeeTypeID,
                            RecordStatus = err.RecordStatus,
                            Remark = ebpr.Remark,
                            BatchRemark = ebp.Remark
                        };


            return query.ToList();
        }

        public StudentListView GetRegisterViewByExaminationBatchProjectID(Guid? examinationBatchProjectID, Guid? userID)
        {
            //var curUser = EMIS.Utility.FormValidate.CustomPrincipal.Current;
            var student = studentsDAL.GetStudentQueryable(x => x.UserID == userID, x => true, x => true).FirstOrDefault();
            var query = from ebpr in examinationBatchProjectRepository.Entities.Where(x => x.ExaminationBatchProjectID == examinationBatchProjectID)
                        from ebr in examinationBatchRepository.Entities.Where(x => x.ExaminationBatchID == ebpr.ExaminationBatchID)
                        from epr in examinationProjectRepository.Entities.Where(x => x.ExaminationProjectID == ebpr.ExaminationProjectID)
                        from ebpcr in examinationBatchProjectControlRepository.Entities.Where(x => x.ExaminationBatchProjectID == examinationBatchProjectID && (x.SchoolyearID == student.Years || x.SchoolyearID == null) && (x.StandardID == student.StandardID || x.StandardID == null)).DefaultIfEmpty()
                        from ebppcr in examinationBatchProjectPersonControlRepository.Entities.Where(x => x.ExaminationBatchProjectID == examinationBatchProjectID && x.UserID == userID).DefaultIfEmpty()
                        select new StudentListView
                        {
                            ExaminationBatchProjectID = ebpr.ExaminationBatchProjectID,
                            SchoolyearID = ebr.SchoolyearID,
                            SchoolyearCode = ebr.CF_Schoolyear.Code,
                            ExaminationBatchID = ebr.ExaminationBatchID,
                            ExaminationBatchName = ebr.Name,
                            ExaminationProjectID = epr.ExaminationProjectID,
                            ExaminationProjectName = epr.Name,
                            ExaminationTypeID = epr.ExaminationTypeID,
                            ExaminationTypeName = epr.EX_ExaminationType.Name,
                            StartDate = ebppcr.ExaminationBatchProjectPersonControlID != null ? ebppcr.StartDate : ebpr.StartDate,
                            EndDate = ebppcr.ExaminationBatchProjectPersonControlID != null ? ebppcr.EndDate : ebpr.EndDate,
                            Remark = ebpr.Remark,
                            ExaminationProjectFeeID = ebppcr.ExaminationProjectFeeID == null ? ebpcr.ExaminationProjectFeeID : ebppcr.ExaminationProjectFeeID,
                            ExaminationProjectFeeName = ebppcr.ExaminationProjectFeeID == null ? ebpcr.EX_ExaminationProjectFee.Name : ebppcr.EX_ExaminationProjectFee.Name
                            //RegistView = registView,
                        };
            return query.FirstOrDefault();
        }

        public List<bool?> GetPreposeProjectIsPass(Guid? preposeProjectID, Guid? userID)
        {
            var query = from epr in examinationProjectRepository.Entities.Where(x => x.ExaminationProjectID == preposeProjectID)
                        from psr in projectScoreRepository.Entities.Where(x => x.ExaminationProjectID == epr.ExaminationProjectID && x.UserID == userID)
                        from lsr in levelSettingRepository.Entities.Where(x => x.LevelSettingID == psr.LevelSettingID)
                        select new 
                        {
                            ExaminationProjectID = epr.ExaminationProjectID,
                            ProjectScoreID = psr.ProjectScoreID,
                            LevelSettingID = lsr.LevelSettingID,
                            IsPassed = lsr.IsPassed,
                        };
            return query.Select(x => x.IsPassed).ToList();
        }

        public List<FeeTypeByProjectFeeView> GetFeeTypeByProjectFeeView(Expression<Func<EMIS.Entities.EX_ExaminationProjectFeeType, bool>> epftexp)
        {
            var query = from epftr in examinationProjectFeeTypeRepository.Entities.Where(epftexp)
                        from dic in DictionaryItemRepository.Entities.Where(x => x.DictionaryCode == "EX_ExaminationProjectFeeType" && x.Value == epftr.FeeTypeID)
                        //group dic by epftr.ExaminationProjectFeeID
                        //into gdic
                        select new FeeTypeByProjectFeeView
                        {
                            ExaminationProjectFeeTypeID = epftr.ExaminationProjectFeeTypeID,
                            ExaminationProjectFeeID = epftr.ExaminationProjectFeeID,
                            ExaminationProjectID = epftr.EX_ExaminationProjectFee.ExaminationProjectID,
                            FeeTypeID = epftr.FeeTypeID,
                            FeeTypeName = dic.Name + "(" + epftr.Fee + ")",
                            IsResit = epftr.IsResit,
                            //String.Join("/n",gdic.Select(x => x.Name).ToArray()),
                        };
            return query.Distinct().ToList();
        }
    }
}