JW/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.ScoreManage;
using EMIS.Entities;
using System.Linq.Expressions;

namespace EMIS.DataLogic.StudentSystem.ExamineApply
{
    public class ExamineApplyDAL
    {
        public StudentRepository StudentRepository { get; set; }
        public ExaminationSubjectRepository SubjectRepository { get; set; }
        public ExaminationOpenControlRepository OpenControlRepository { get; set; }
        public DictionaryItemRepository DictionaryItemRepository { get; set; }
        public ExaminationRegistrationRepository RegistrationRepository { get; set; }
        public ClassmajorRepository ClassRepository { get; set; }
        public GrademajorRepository GradeRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public SchoolyearRepository schoolyearRepository { get; set; }
        public ExaminationSubjectCollegeControlRepository examinationSubjectCollegeControlRepository { get; set; }
        public LevelScoreRepository levelScoreRepository { get; set; }

        public IList<ExaminationApplyView> GetExaminationSubjectList(Guid UserID)
        {
            var currentSchoolyear=schoolyearRepository.Entities.Where(x=>x.IsCurrent==true).FirstOrDefault();
            var query = from subject in SubjectRepository.Entities
                        //from ex in subject.EX_ExaminationRegistration
                        join control in OpenControlRepository.Entities on subject.ExaminationSubjectID equals control.ExaminationSubjectID 
                        into tmp from control in tmp.DefaultIfEmpty()
                        join student in StudentRepository.GetList(x => x.UserID == UserID) on control.StudentType equals student.StudentType 
                        join cla in ClassRepository.Entities on student.ClassmajorID equals cla.ClassmajorID
                        join grade in GradeRepository.Entities on cla.GrademajorID equals grade.GrademajorID
                        join schoolyear in SchoolyearRepository.Entities
                            on new { Years = grade.GradeID, SchoolcodeID = grade.SemesterID }
                            equals new { Years = (int?)schoolyear.Years, SchoolcodeID = (int?)schoolyear.SchoolcodeID }
                        where control.SchoolyearNumID==(currentSchoolyear.Value - schoolyear.Value - (currentSchoolyear.SchoolcodeID == grade.SemesterID ? 0 : 1)) / 2 + 1
                        join esccr in examinationSubjectCollegeControlRepository.Entities
                        on subject.ExaminationSubjectID equals esccr.ExaminationSubjectID
                        into gesccr
                        from fesccr in gesccr.Where(x => x.FacultymajorID == grade.FacultymajorID && (x.SchoolyearNumID == ((currentSchoolyear.Value - schoolyear.Value - (currentSchoolyear.SchoolcodeID == grade.SemesterID ? 0 : 1)) / 2 + 1) || x.SchoolyearNumID == null)).DefaultIfEmpty()
                        where fesccr.ExaminationSubjectCollegeControlID == null
                        select new ExaminationApplyView
                        {
                            Name = subject.Name,
                            ExaminationSubjectID = subject.ExaminationSubjectID,
                            StartDate = control.StartDate,
                            EndDate = control.EndDate,
                            ExaminationDate = subject.ExaminationDate,
                            PeopleNumLimit = subject.PeopleNumLimit,
                            Cost = subject.Cost,
                            Remark = subject.Remark,
                            RegisterNum = subject.EX_ExaminationRegistration.Where(x => x.ExaminationSchoolyearID == subject.SchoolyearID).Count(),//x => x.SchoolyearID == currentSchoolyear.SchoolyearID改为已选人数统计显示报名表考试学年学期和科目表的考试学年学期相同的总人数
                            BKCS = subject.EX_ExaminationRegistration.Count,
                            IsRister = subject.EX_ExaminationRegistration.Any(q => q.UserID == UserID && q.SchoolyearID == currentSchoolyear.SchoolyearID),//
                        };
            return query.ToList();
        }

        public IList<ExaminationRegisView> GetExaminationRegisterList(Guid UserID)
        {
            var currentSchoolyear = schoolyearRepository.Entities.Where(x => x.IsCurrent == true).FirstOrDefault();
            var query = from register in RegistrationRepository.Entities
                        join student in StudentRepository.Entities on register.UserID equals student.UserID
                        join subject in SubjectRepository.Entities on register.ExaminationSubjectID equals subject.ExaminationSubjectID
                        join CertificatesType in DictionaryItemRepository.Entities on new { CourseTypeID = register.CertificatesType, DictionaryCode = typeof(CF_CertificatesType).Name } equals new { CourseTypeID = CertificatesType.Value, CertificatesType.DictionaryCode }
                        join cla in ClassRepository.Entities on student.ClassmajorID equals cla.ClassmajorID
                        join grade in GradeRepository.Entities on cla.GrademajorID equals grade.GrademajorID
                        join schoolyear in SchoolyearRepository.Entities
                            on new { Years = grade.GradeID, SchoolcodeID = grade.SemesterID }
                            equals new { Years = (int?)schoolyear.Years, SchoolcodeID = (int?)schoolyear.SchoolcodeID }
                        join control in OpenControlRepository.Entities on
                            new { register.ExaminationSubjectID, student.StudentType, SchoolyearNumID = (currentSchoolyear.Value - schoolyear.Value - (currentSchoolyear.SchoolcodeID == grade.SemesterID ? 0 : 1)) / 2 + 1 } 
                            equals new { control.ExaminationSubjectID, control.StudentType, control.SchoolyearNumID } into dcontrol 
                        from econtrol in dcontrol.DefaultIfEmpty() 
                        join sch in schoolyearRepository.Entities
                        on register.SchoolyearID equals sch.SchoolyearID
                        join esch in schoolyearRepository.Entities
                        on register.ExaminationSchoolyearID equals esch.SchoolyearID
                        where register.UserID == UserID 
                        //&& register.SchoolyearID == currentSchoolyear.SchoolyearID
                        select new ExaminationRegisView
                        {
                            ExaminationRegistrationID=register.ExaminationRegistrationID,
                            Name = subject.Name,
                            ExaminationSubjectID = subject.ExaminationSubjectID,
                            StartDate = econtrol.StartDate,
                            EndDate = econtrol.EndDate,
                            ExaminationDate = subject.ExaminationDate,
                            PeopleNumLimit = subject.PeopleNumLimit,
                            Cost = subject.Cost,
                            //Jishu = subject.EX_ExaminationRegistration.Where(x => x.UserID == UserID).Count(),
                            Remark = subject.Remark,
                            CertificatesType = CertificatesType.Name,
                            IDNumber = register.IDNumber,
                            SchoolYearID = sch.SchoolyearID,
                            SchoolYearCode = sch.Code,
                            ExaminationSchoolYearID = esch.SchoolyearID,
                            ExaminationSchoolYearCode = esch.Code
                        };
            var result = query.Distinct();
            return result.ToList();
        }

        public List<ExaminationApplyView> GetExaminationApplyView(Guid? ExaminationSubjectID)
        {
            var currentSchoolyear = schoolyearRepository.Entities.Where(x => x.IsCurrent == true).FirstOrDefault();
            var query = from subject in SubjectRepository.GetList(x => x.ExaminationSubjectID == ExaminationSubjectID)
                        //from ex in subject.EX_ExaminationRegistration
                        join control in OpenControlRepository.Entities on subject.ExaminationSubjectID equals control.ExaminationSubjectID
                        into tmp
                        from control in tmp.DefaultIfEmpty()
                        select new ExaminationApplyView
                        {
                            Name = subject.Name,
                            ExaminationSubjectID = subject.ExaminationSubjectID,
                            StartDate = control.StartDate,
                            EndDate = control.EndDate,
                            ExaminationDate = subject.ExaminationDate,
                            PeopleNumLimit = subject.PeopleNumLimit,
                            Cost = subject.Cost,
                            Remark = subject.Remark,
                            RegisterNum = subject.EX_ExaminationRegistration.Where(x => x.ExaminationSchoolyearID == subject.SchoolyearID).Count(),//x => x.SchoolyearID == currentSchoolyear.SchoolyearID改为已选人数统计显示报名表考试学年学期和科目表的考试学年学期相同的总人数
                            BKCS = subject.EX_ExaminationRegistration.Count,
                            IsRister = subject.EX_ExaminationRegistration.Any(q => q.SchoolyearID == currentSchoolyear.SchoolyearID),
                        };
            return query.ToList();
        }

        public IQueryable<LevelScoreView> GetLevelScoreViewAndRegisterList(Expression<Func<ER_LevelScore, bool>> lelexp, Expression<Func<EX_ExaminationRegistration, bool>> erexp)
        {
            var query = from register in RegistrationRepository.GetList(erexp)
                        join level in levelScoreRepository.GetList(lelexp)
                        on new { register.ExaminationSubjectID, SchoolyearID = register.ExaminationSchoolyearID } equals new { level.ExaminationSubjectID, SchoolyearID = level.SchoolyearID }
                        into re
                        from dre in re.DefaultIfEmpty()
                        join schoolyear in schoolyearRepository.Entities
                        on register.SchoolyearID equals schoolyear.SchoolyearID
                        join stu in StudentRepository.Entities
                        on register.UserID equals stu.UserID
                        join subject in SubjectRepository.Entities
                        on register.ExaminationSubjectID equals subject.ExaminationSubjectID
                        select new LevelScoreView
                        {
                            ExaminationRegistrationID = register.ExaminationRegistrationID,
                            LevelScoreID = dre.LevelScoreID,
                            ExaminationSubjectName = subject.Name,
                            UserName = stu.Sys_User.Name,
                            UserID = register.UserID,
                            LoginID = stu.Sys_User.LoginID,
                            ExaminationDate = subject.ExaminationDate,
                            ExaminationDateStr = subject.ExaminationDate.ToString(),
                            SchoolyearID = register.SchoolyearID,
                            SchoolyearCode = schoolyear.Code,
                            ScoreNo = dre.ScoreNo,
                            TotalScore = dre.TotalScore,
                            //ValidDate = dre.ValidDate,
                            Remark = dre.Remark,
                        };
            return query;
        }

    }
}