LIOT/EMiS.DataLogic/ExaminationApply/ExaminationSubjectDAL.cs

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

namespace EMIS.DataLogic.ExaminationApply
{
    public class ExaminationSubjectDAL
    {
        public ExaminationRegistrationRepository examinationRegistrationRepository { get; set; }
        public ExaminationProjectRepository examinationProjectRepository { get; set; }
        public ExaminationProjectFeeRepository examinationProjectFeeRepository { get; set; }
        public ExaminationProjectFeeTypeRepository examinationProjectFeeTypeRepository { get; set; }
        public ExaminationBatchProjectRepository examinationBatchProjectRepository { get; set; }
        public ExaminationBatchRepository examinationBatchRepository { get; set; }
        public ExaminationRegistrationConfirmRepository examinationRegistrationConfirmRepository { get; set; }
        public ExaminationBatchProjectControlRepository examinationBatchProjectControlRepository { get; set; }
        public ExaminationBatchProjectPersonControlRepository examinationBatchProjectPersonControlRepository { get; set; }
        public ExaminationTypeRepository examinationTypeRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public ClassmajorRepository classmajorRepository { get; set; }
        public GrademajorRepository grademajorRepository { get; set; }
        public FacultymajorRepository facultymajorRepository { get; set; }
        public StudentRepository studentRepository { get; set; }
        public StudentProfileRepository studentProfileRepository { get; set; }
        public RecruitstudentsRepository recruitstudentsRepository { get; set; }
        public DictionaryItemRepository dictionaryItemRepository { get; set; }
        public SchoolyearRepository schoolyearRepository { get; set; }
        public CollegeRepository collegeRepository { get; set; }
        public CampusRepository campusRepository { get; set; }
        public UniversityRepository universityRepository { get; set; }
        public WechatPayListRepository wechatPayListRepository { get; set; }
        public ExaminationProjectSubjectRepository examinationProjectSubjectRepository { get; set; }

        public IQueryable<StudentListView> GetStudentListView(Expression<Func<EX_ExaminationRegistration, bool>> erexp, Expression<Func<EX_ExaminationProject, bool>> epexp,
            Expression<Func<EX_ExaminationBatchProject, bool>> ebpexp, Expression<Func<CF_Student, bool>> stuexp)
        {
            var currentSchoolyear = schoolyearRepository.Entities.Where(x => x.IsCurrent == true).FirstOrDefault();
            var query = from regist in examinationRegistrationRepository.GetList(erexp)
                        from wplr in wechatPayListRepository.Entities.Where(x => x.ExaminationRegistrationID == regist.ExaminationRegistrationID).DefaultIfEmpty()
                        from batchProject in examinationBatchProjectRepository.Entities.Where(ebpexp).Where(x => regist.ExaminationBatchProjectID == x.ExaminationBatchProjectID)
                        from feeType in examinationProjectFeeTypeRepository.Entities.Where(x => regist.ExaminationProjectFeeTypeID == x.ExaminationProjectFeeTypeID)
                        from fee in examinationProjectFeeRepository.Entities.Where(x => feeType.ExaminationProjectFeeID == x.ExaminationProjectFeeID) 
                        from personalControl in examinationBatchProjectPersonControlRepository.Entities.Where(x => x.ExaminationBatchProjectID == regist.ExaminationBatchProjectID 
                            && x.ExaminationProjectFeeID == feeType.ExaminationProjectFeeID && x.UserID == regist.UserID).DefaultIfEmpty() 
                        from batchControl in examinationBatchProjectControlRepository.Entities.Where(x => x.ExaminationBatchProjectID == regist.ExaminationBatchProjectID 
                            && x.ExaminationProjectFeeID == feeType.ExaminationProjectFeeID && x.SchoolyearID == null && x.StandardID == null && x.CollegeID == null).DefaultIfEmpty() 
                            //join ebpcr in examinationBatchProjectControlRepository.Entities
                            //on new { err.ExaminationBatchProjectID, epft.ExaminationProjectFeeID } equals new { ebpcr.ExaminationBatchProjectID, ebpcr.ExaminationProjectFeeID }
                            //into gebpcr
                            //from ebpcr in gebpcr.DefaultIfEmpty()
                        from project in examinationProjectRepository.Entities.Where(epexp).Where(x => batchProject.ExaminationProjectID == x.ExaminationProjectID)
                        from type in examinationTypeRepository.Entities.Where(x => project.ExaminationTypeID == x.ExaminationTypeID)
                        from batch in examinationBatchRepository.Entities.Where(x => batchProject.ExaminationBatchID == x.ExaminationBatchID)
                        from schoolyear in SchoolyearRepository.Entities.Where(x => batch.SchoolyearID == x.SchoolyearID)
                        from confirm in examinationRegistrationConfirmRepository.Entities.Where(x => regist.ExaminationRegistrationConfirmID == x.ExaminationRegistrationConfirmID)
                            .DefaultIfEmpty()
                        from student in studentRepository.Entities.Where(stuexp).Where(x => regist.UserID == x.UserID)
                        from classmajor in classmajorRepository.Entities.Where(x => student.ClassmajorID == x.ClassmajorID)
                        from grade in grademajorRepository.Entities.Where(x => classmajor.GrademajorID == x.GrademajorID)
                        from gradeYear in schoolyearRepository.Entities.Where(x => x.SchoolcodeID == grade.SchoolcodeID && x.Years == grade.SchoolyearID).DefaultIfEmpty()
                        from faculty in facultymajorRepository.Entities.Where(x => grade.FacultymajorID == x.FacultymajorID)
                        from collegeControl in examinationBatchProjectControlRepository.Entities.Where(x => x.ExaminationBatchProjectID == regist.ExaminationBatchProjectID
                            && x.ExaminationProjectFeeID == feeType.ExaminationProjectFeeID && x.SchoolyearID == grade.SchoolyearID && x.StandardID == faculty.StandardID 
                            && x.CollegeID == faculty.CollegeID).DefaultIfEmpty()
                        select new StudentListView
                        {
                            ExaminationRegistrationID = regist.ExaminationRegistrationID,
                            SchoolyearID = batch.SchoolyearID,
                            SchoolyearCode = schoolyear.Code,
                            ExaminationProjectFeeID = feeType.ExaminationProjectFeeID,
                            ExaminationProjectFeeName = fee.Name,
                            ExaminationProjectFeeTypeID = regist.ExaminationProjectFeeTypeID,
                            ExaminationBatchProjectID = batchProject.ExaminationBatchProjectID,
                            ExaminationBatchID = batchProject.ExaminationBatchID,
                            ExaminationBatchName = batch.Name,
                            ExaminationTypeID = project.ExaminationTypeID,
                            ExaminationTypeName = type.Name,
                            ExaminationProjectID = batchProject.ExaminationProjectID,
                            ExaminationProjectName = project.Name,
                            ExaminationLevelID = project.ExaminationLevelID,
                            Value = feeType.FeeTypeID,
                            FeeTypeID = feeType.FeeTypeID,
                            Fee = feeType.Fee,
                            PayFee = regist.Fee,
                            ClassmajorID = classmajor.ClassmajorID,
                            ClassmajorName = classmajor.Name,
                            ClassmajorNo = classmajor.No,
                            UserID = regist.UserID,
                            UserName = student.Sys_User.Name,
                            Sex = student.Sex,
                            CertificatesType = student.CertificatesType,
                            IDNumber = student.IDNumber,
                            CollegeID = grade.CF_Facultymajor.CollegeID,
                            CollegeName = grade.CF_Facultymajor.CF_College.Name,
                            StudentNo = student.StudentCardNo,
                            ExaminationRegistrationConfirmID = confirm.ExaminationRegistrationConfirmID,
                            ExaminationRegistrationConfirmNo = confirm.No,
                            Remark = regist.Remark,
                            RefundReason = regist.RefundReason,
                            RecordStatus = regist.RecordStatus,
                            StartDate = personalControl != null ? personalControl.StartDate : batchProject.StartDate,
                            EndDate = personalControl != null ? personalControl.EndDate : batchProject.EndDate,
                            SchoolyearNumID = gradeYear == null ? null : ((currentSchoolyear.Value - gradeYear.Value - (currentSchoolyear.SchoolcodeID == grade.SchoolcodeID ? 0 : 1)) / 2 + 1),
                            SchoolAreaID = grade.SchoolAreaID,
                            OrderID = wplr.OrderID,
                            WechatOrderID = wplr.WechatOrderID,
                            RefundOrderID = wplr.RefundOrderID,
                            IsOnlinePay = (personalControl.IsOnlinePay ?? (collegeControl.IsOnlinePay ?? batchControl.IsOnlinePay)) ?? false,
                            PayForm = wplr.PayForm,
                            PayUrl = wplr.PayUrl
                        };
            return query;
        }

        public IQueryable<StudentsView> GetStudentQueryableByRegistration(Expression<Func<EX_ExaminationRegistration, bool>> erexp)
        {
            var query = from exrr in examinationRegistrationRepository.GetList(erexp)
                        join c in studentRepository.Entities
                        on exrr.UserID equals c.UserID
                        join classmajor in classmajorRepository.Entities on c.ClassmajorID equals classmajor.ClassmajorID
                        join grade in grademajorRepository.Entities on classmajor.GrademajorID equals grade.GrademajorID
                        join studentProfile in studentProfileRepository.Entities on c.UserID equals studentProfile.UserID
                        into cstudentProfile
                        from tcstudentProfile in cstudentProfile.DefaultIfEmpty()
                        join recruitStudent in recruitstudentsRepository.Entities on c.UserID equals recruitStudent.UserID
                        into crecruitStudent
                        from tcrecruitStudent in crecruitStudent.DefaultIfEmpty()
                        select new StudentsView
                        {
                            UserID = c.UserID,
                            LoginID = c.Sys_User.LoginID,
                            StudentNo = c.StudentCardNo,
                            UserName = c.Sys_User.Name,
                            IDNumber = c.IDNumber,
                            PhotoUrl = c.PhotoUrl,
                            IsProofread = c.IsProofread,
                            IsPhoto = c.PhotoUrl == null ? (int)CF_YesOrNoStatus.No : (int)CF_YesOrNoStatus.Yes,
                            IsPhotoUrl = c.PhotoUrl == null ? "否" : "是",
                            StudentCardNo = c.StudentCardNo,
                            PlanningGraduateDate = c.PlanningGraduateDate,//预计毕业时间
                            EntranceDate = c.CF_Recruitstudents.EntranceDate,
                            EnteringSchoolYearID = c.CF_Recruitstudents.EnteringSchoolYearID,
                            EnteringSchoolYearCode = c.CF_Recruitstudents.CF_Schoolyear.Code,
                            Score = c.CF_Recruitstudents.Score,
                            ExamineeNum = c.CF_Recruitstudents.ExamineeNum,
                            AdmissionTicketNo = c.CF_Recruitstudents.AdmissionTicketNo,
                            Area = c.CF_Recruitstudents.Area,
                            Placebirth = c.CF_Recruitstudents.PlaceBirth,
                            CardNo = c.CF_StudentAccount.CardNo,
                            BankName = c.CF_StudentAccount.BankName,
                            Territorial = tcrecruitStudent.Territorial,
                            Sex = c.Sex,
                            ClassMajorID = c.ClassmajorID,
                            ClassMajorCode = classmajor.No,
                            ClassMajorName = classmajor.Name,
                            Grade = grade.SchoolyearID,
                            StudentStatus = c.StudentStatus,
                            InSchoolStatusID = c.InSchoolStatusID,
                            UsedName = c.CF_StudentProfile.UsedName,
                            DirectorName = c.CF_StudentProfile.DirectorName,
                            BirthDate = c.CF_StudentProfile.BirthDate,
                            Country = c.CF_StudentProfile.Country,
                            Politics = c.CF_StudentProfile.Politics,
                            Place = c.CF_StudentProfile.Place,
                            //BornPlace = c.CF_StudentProfile.BornPlace,
                            Specialty = c.CF_StudentProfile.Specialty,
                            Height = c.CF_StudentProfile.Height,
                            Weight = c.CF_StudentProfile.Weight,
                            Remarks = c.CF_StudentProfile.Remarks,
                            GraduationPictureUrl = c.CF_StudentProfile.GraduationPictureUrl,
                            Email = c.CF_StudentContact.Email,
                            QQ = c.CF_StudentContact.QQ,
                            Mobile = c.CF_StudentContact.Mobile,
                            Telephone = c.CF_StudentContact.Telephone,
                            MicroMsgNo = c.CF_StudentContact.MicroMsgNo,
                            ZipCode = c.CF_StudentContact.Zipcode,
                            Address = c.CF_StudentContact.Address,
                            WorkUnit = c.CF_StudentContact.WorkUnit,
                            HomeAddress = c.CF_StudentContact.HomeAddress,
                            Recipient = c.CF_StudentContact.Recipient,
                            Dormitory = c.CF_StudentContact.Dormitory,
                            LearnSystem = grade.CF_Facultymajor.LearnSystem,
                            CampusID = grade.CF_Facultymajor.CF_College.CampusID,
                            SchoolTypeID = grade.CF_Facultymajor.CF_College.CF_Campus.CF_University.CF_UniversityProfile.SchoolTypeID,
                            CampusName = grade.CF_Facultymajor.CF_College.CF_Campus.Name,
                            CollegeID = grade.CF_Facultymajor.CollegeID,
                            CollegeCode = grade.CF_Facultymajor.CF_College.No,
                            CollegeName = grade.CF_Facultymajor.CF_College.Name,
                            EducationID = grade.CF_Facultymajor.EducationID,
                            StandardID = grade.CF_Facultymajor.StandardID,
                            LearningformID = grade.CF_Facultymajor.LearningformID,
                            GradeMajorID = classmajor.GrademajorID,
                            GradeMajorName = grade.Name,
                            GradeMajorCode = grade.Code,
                            StudentType = c.StudentType,
                            CultureModel = c.CultureModel,
                            BloodGroup = tcstudentProfile.BloodGroup,
                            CertificatesType = c.CertificatesType,
                            EntranceWay = tcrecruitStudent.EntranceWay,
                            ExamineeType = tcrecruitStudent.ExamineeType,
                            Features = tcrecruitStudent.Features,
                            Healthy = tcstudentProfile.Healthy,
                            Nation = tcstudentProfile.Nation,
                            FacultymajorName = grade.CF_Facultymajor.Name,//院系所专业
                            GraduateCardNo = c.GraduateCardNo,
                            GraduateDate = c.GraduateDate,
                            IsDreamProject = c.IsDreamProject == true,
                            Career = c.Career,
                            ReplaceGraduateNo = c.ReplaceGraduateNo,
                            LiteracyLevels = c.CF_StudentProfile.LiteracyLevels,
                            SchoolAreaID = grade.SchoolAreaID,
                        };
            return query;
        }

        public IQueryable<StudentListForExportView> GetStudentListForExport(Expression<Func<EX_ExaminationRegistration, bool>> erexp, Expression<Func<EX_ExaminationProject, bool>> epexp,
            Expression<Func<EX_ExaminationBatchProject, bool>> ebpexp, Expression<Func<CF_Student, bool>> stuexp)
        {
            var currentSchoolyear = schoolyearRepository.Entities.Where(x => x.IsCurrent == true).FirstOrDefault();
            var query = from err in examinationRegistrationRepository.GetList(erexp)
                        join ebpr in examinationBatchProjectRepository.GetList(ebpexp)
                        on err.ExaminationBatchProjectID equals ebpr.ExaminationBatchProjectID
                        join epft in examinationProjectFeeTypeRepository.Entities
                        on err.ExaminationProjectFeeTypeID equals epft.ExaminationProjectFeeTypeID
                        join epf in examinationProjectFeeRepository.Entities
                        on epft.ExaminationProjectFeeID equals epf.ExaminationProjectFeeID
                        join epr in examinationProjectRepository.GetList(epexp)
                        on ebpr.ExaminationProjectID equals epr.ExaminationProjectID
                        join etr in examinationTypeRepository.Entities
                        on epr.ExaminationTypeID equals etr.ExaminationTypeID
                        join ebr in examinationBatchRepository.Entities
                        on ebpr.ExaminationBatchID equals ebr.ExaminationBatchID
                        join sch in SchoolyearRepository.Entities
                        on ebr.SchoolyearID equals sch.SchoolyearID
                        join ercr in examinationRegistrationConfirmRepository.Entities
                        on err.ExaminationRegistrationConfirmID equals ercr.ExaminationRegistrationConfirmID
                        into dercr
                        from ercr in dercr.DefaultIfEmpty()
                        join stu in studentRepository.GetList(stuexp)
                        on err.UserID equals stu.UserID
                        join recruitStudent in recruitstudentsRepository.Entities on stu.UserID equals recruitStudent.UserID
                            into crecruitStudent
                        from tcrecruitStudent in crecruitStudent.DefaultIfEmpty()
                        join studentProfile in studentProfileRepository.Entities on stu.UserID equals studentProfile.UserID
                            into cstudentProfile
                        from tcstudentProfile in cstudentProfile.DefaultIfEmpty()
                        join cla in classmajorRepository.Entities
                        on stu.ClassmajorID equals cla.ClassmajorID
                        join gra in grademajorRepository.Entities
                        on cla.GrademajorID equals gra.GrademajorID
                        into dgra 
                        from gra in dgra.DefaultIfEmpty()
                        join fac in facultymajorRepository.Entities
                        on gra.FacultymajorID equals fac.FacultymajorID
                        into dfac
                        from fac in dfac.DefaultIfEmpty()
                        join col in collegeRepository.Entities
                        on fac.CollegeID equals col.CollegeID
                        into dcol
                        from col in dcol.DefaultIfEmpty()
                        join campus in campusRepository.Entities
                        on col.CampusID equals campus.CampusID
                        into dcampus
                        from campus in dcampus.DefaultIfEmpty()
                        join uni in universityRepository.Entities
                        on campus.UniversityID equals uni.UniversityID
                        into duni
                        from uni in duni.DefaultIfEmpty()
                        select new StudentListForExportView
                        {
                            SchoolAreaID = gra.SchoolAreaID,
                            ExaminationRegistrationID = err.ExaminationRegistrationID,
                            ExaminationTypeID = epr.ExaminationTypeID,
                            ExaminationTypeName = etr.Name,
                            ExaminationLevelID = epr.ExaminationLevelID,
                            Year = gra.SchoolyearID,
                            CollegeID = fac.CollegeID,
                            CollegeName = col.Name,
                            StandardID = fac.StandardID,
                            StudentNo = stu.StudentCardNo,
                            UserID = err.UserID,
                            UserName = stu.Sys_User.Name,
                            Sex = stu.Sex,
                            IDNumber = stu.IDNumber,
                            QQ = stu.CF_StudentContact.QQ,
                            Email = stu.CF_StudentContact.Email,
                            PhoneNumber = stu.CF_StudentContact.Mobile,
                            BirthDate = stu.CF_StudentProfile.BirthDate,
                            Nation = stu.CF_StudentProfile.Nation,
                            EducationID = fac.EducationID,
                            Residence = stu.CF_StudentProfile.Place,
                            HomeAddress = stu.CF_StudentContact.HomeAddress,
                            Mobile = stu.CF_StudentContact.Mobile,
                            ExaminationProjectID = ebpr.ExaminationProjectID,
                            ExaminationProjectName = epr.Name,
                            Fee = epft.Fee,
                            PayFee = err.Fee,
                            RegistrDate = err.CreateTime,
                            RecordStatus = err.RecordStatus,
                            Remark = err.Remark,

                            Politics = stu.CF_StudentProfile.Politics,
                            Address = stu.CF_StudentContact.Address,

                            WorkPlace = stu.CF_StudentContact.WorkUnit,

                            SchoolyearID = ebr.SchoolyearID,
                            SchoolyearCode = sch.Code,
                            ExaminationProjectFeeID = epft.ExaminationProjectFeeID,
                            ExaminationProjectFeeName = epf.Name,
                            ExaminationProjectFeeTypeID = err.ExaminationProjectFeeTypeID,
                            ExaminationBatchID = ebpr.ExaminationBatchID,
                            ExaminationBatchName = ebr.Name,
                            
                            Value = epft.FeeTypeID,
                            FeeTypeID = epft.FeeTypeID,
                            
                            ClassmajorID = cla.ClassmajorID,
                            ClassmajorName = cla.Name,
                            ClassmajorNo = cla.No,
                            CertificatesType = stu.CertificatesType,
                            
                            ExaminationRegistrationConfirmID = ercr.ExaminationRegistrationConfirmID,
                            ExaminationRegistrationConfirmNo = ercr.No,
                            StartDate = ebpr.StartDate,
                            EndDate = ebpr.EndDate,
                            SchoolyearNumID = ((currentSchoolyear.Value - gra.CF_Schoolyear.Value - (currentSchoolyear.SchoolcodeID == gra.SchoolcodeID ? 0 : 1)) / 2 + 1),

                            ExamineeNum = stu.CF_Recruitstudents.ExamineeNum,
                            PhotoUrl = stu.PhotoUrl,
                            SchoolCode = uni.Code,
                            SchoolName = uni.Name,
                            LearnSystem = gra.CF_Facultymajor.LearnSystem,
                            LearningformID = gra.CF_Facultymajor.LearningformID,
                            EntranceDate = stu.CF_Recruitstudents.EntranceDate,
                            PlanningGraduateDate = stu.PlanningGraduateDate,//预计毕业时间
                            GraduationResult = stu.InSchoolStatusID,
                            CampusName = campus.Name,
                            ClassmajorCode = cla.No,
                        };
            return query;
        }

        public IQueryable<ExaminationRegistrationPayView> GetExaminationRegistrationPayViewQueryable(Expression<Func<EX_ExaminationRegistration, bool>> regExp)
        {
            var sql = (from regist in examinationRegistrationRepository.GetList(regExp) 
                       from pay in wechatPayListRepository.Entities.Where(x => x.ExaminationRegistrationID == regist.ExaminationRegistrationID).DefaultIfEmpty() 
                       select new ExaminationRegistrationPayView
                       {
                           WechatPayListID = pay.WechatPayListID,
                           ExaminationRegistrationID = regist.ExaminationRegistrationID,
                           Fee = regist.Fee,
                           OrderID = pay.OrderID,
                           WechatOrderID = pay.WechatOrderID,
                           RefundOrderID = pay.RefundOrderID,
                           RefundTotal = pay.RefundTotal,
                           RecordStatus = regist.RecordStatus
                       });

            return sql;
        }
    }
}