JW/EMiS.DataLogic/EnrollManage/NewStudentManage/NewStudentDAL.cs

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

namespace EMIS.DataLogic.EnrollManage.NewStudentManage
{
    public class NewStudentDAL
    {
        public NewStudentRepository NewStudentRepository { get; set; }
        public SpecialtyRepository SpecialtyRepository { get; set; }
        public DictionaryItemRepository DictionaryItemRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public RecruitSpecialtyRepository RecruitSpecialtyRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }
        public RecruitstudentsRepository RecruitstudentsRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public ParameterRepository ParameterRepository { get; set; }
        public RoleRepository RoleRepository { get; set; }
        public CustomStandardSettingRepository CustomStandardSettingRepository { get; set; }
        public GraduateSchoolyearRepository GraduateSchoolyearRepository { get; set; }

        /// <summary>
        /// 查询对应的新生名单信息View
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<NewStudentView> GetNewStudentViewQueryable(Expression<Func<CF_NewStudent, bool>> exp)
        {
            var query = from nstu in NewStudentRepository.GetList(exp)
                        join sp in SpecialtyRepository.Entities
                        on nstu.SpecialtyID equals sp.SpecialtyID
                        join dicst in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(CF_Standard).Name)
                        on sp.StandardID equals dicst.Value into tempst
                        from standard in tempst.DefaultIfEmpty()
                        select new NewStudentView
                        {
                            NewStudentID = nstu.NewStudentID,
                            ExamineeNum = nstu.ExamineeNum,
                            AdmissionTicketNo = nstu.AdmissionTicketNo,
                            Name = nstu.Name,
                            SexID = nstu.SexID,
                            NationID = nstu.NationID,
                            PoliticsID = nstu.PoliticsID,
                            BirthDate = nstu.BirthDate,
                            CertificatesType = nstu.CertificatesType,
                            IDNumber = nstu.IDNumber,
                            GradeID = nstu.GradeID,
                            SemesterID = nstu.SemesterID,
                            SpecialtyID = nstu.SpecialtyID,
                            StandardID = sp.StandardID,
                            StandardCode = standard.Code,
                            StandardName = standard.Name,
                            EducationID = sp.EducationID,
                            LearningformID = sp.LearningformID,
                            LearnSystem = sp.LearnSystem,
                            EntranceDate = nstu.EntranceDate,
                            Score = nstu.Score,
                            Telephone = nstu.Telephone,
                            ZIPCode = nstu.ZIPCode,
                            EntranceWayID = nstu.EntranceWayID,
                            FeaturesID = nstu.FeaturesID,
                            TerritorialID = nstu.TerritorialID,
                            Address = nstu.Address,
                            Dormitory = nstu.Dormitory,
                            RecruitPictureUrl = nstu.RecruitPictureUrl,
                            PhotoHasValue = (nstu.RecruitPictureUrl == null || nstu.RecruitPictureUrl == "") ? false : true,
                            IsDoubt = nstu.IsDoubt ?? false,
                            AssignStatus = nstu.AssignStatus,
                            Remark = nstu.Remark,
                            RecordStatus = nstu.RecordStatus,
                            CreateUserID = nstu.CreateUserID,
                            CreateTime = nstu.CreateTime,
                            ModifyUserID = nstu.ModifyUserID,
                            ModifyTime = nstu.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的新生分配信息View
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<NewStudentView> GetNewStudentCollegeViewQueryable(Expression<Func<CF_NewStudent, bool>> exp)
        {
            var query = from nstu in NewStudentRepository.GetList(exp)
                        join sp in SpecialtyRepository.Entities
                        on nstu.SpecialtyID equals sp.SpecialtyID
                        join col in CollegeRepository.Entities
                        on nstu.CollegeID equals col.CollegeID
                        join dicst in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(CF_Standard).Name)
                        on sp.StandardID equals dicst.Value into tempst
                        from standard in tempst.DefaultIfEmpty()
                        select new NewStudentView
                        {
                            NewStudentID = nstu.NewStudentID,
                            ExamineeNum = nstu.ExamineeNum,
                            AdmissionTicketNo = nstu.AdmissionTicketNo,
                            Name = nstu.Name,
                            SexID = nstu.SexID,
                            NationID = nstu.NationID,
                            PoliticsID = nstu.PoliticsID,
                            BirthDate = nstu.BirthDate,
                            CertificatesType = nstu.CertificatesType,
                            IDNumber = nstu.IDNumber,
                            GradeID = nstu.GradeID,
                            SemesterID = nstu.SemesterID,
                            SpecialtyID = nstu.SpecialtyID,
                            StandardID = sp.StandardID,
                            StandardCode = standard.Code,
                            StandardName = standard.Name,
                            EducationID = sp.EducationID,
                            LearningformID = sp.LearningformID,
                            LearnSystem = sp.LearnSystem,
                            EntranceDate = nstu.EntranceDate,
                            Score = nstu.Score,
                            Telephone = nstu.Telephone,
                            ZIPCode = nstu.ZIPCode,
                            EntranceWayID = nstu.EntranceWayID,
                            FeaturesID = nstu.FeaturesID,
                            TerritorialID = nstu.TerritorialID,
                            Address = nstu.Address,
                            Dormitory = nstu.Dormitory,
                            EnrollmentNo = nstu.EnrollmentNo,
                            RecruitPictureUrl = nstu.RecruitPictureUrl,
                            PhotoHasValue = (nstu.RecruitPictureUrl == null || nstu.RecruitPictureUrl == "") ? false : true,
                            IsDoubt = nstu.IsDoubt ?? false,
                            AssignStatus = nstu.AssignStatus,
                            OrderNo = nstu.OrderNo,
                            CollegeID = nstu.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            CampusID = col.CampusID,
                            CampusCode = col.CF_Campus.No,
                            CampusName = col.CF_Campus.Name,
                            Remark = nstu.Remark,
                            RecordStatus = nstu.RecordStatus,
                            CreateUserID = nstu.CreateUserID,
                            CreateTime = nstu.CreateTime,
                            ModifyUserID = nstu.ModifyUserID,
                            ModifyTime = nstu.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的录取名单信息View
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<NewStudentView> GetNewStudentEnterViewQueryable(Expression<Func<CF_NewStudent, bool>> exp)
        {
            var query = from nstu in NewStudentRepository.GetList(exp)
                        join sp in SpecialtyRepository.Entities
                        on nstu.SpecialtyID equals sp.SpecialtyID
                        join col in CollegeRepository.Entities
                        on nstu.CollegeID equals col.CollegeID
                        join dicst in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(CF_Standard).Name)
                        on sp.StandardID equals dicst.Value into tempst
                        from standard in tempst.DefaultIfEmpty()
                        join cl in ClassmajorRepository.Entities
                        on nstu.ClassmajorID equals cl.ClassmajorID into tempcl
                        from nstucl in tempcl.DefaultIfEmpty()
                        select new NewStudentView
                        {
                            NewStudentID = nstu.NewStudentID,
                            ExamineeNum = nstu.ExamineeNum,
                            AdmissionTicketNo = nstu.AdmissionTicketNo,
                            Name = nstu.Name,
                            SexID = nstu.SexID,
                            NationID = nstu.NationID,
                            PoliticsID = nstu.PoliticsID,
                            BirthDate = nstu.BirthDate,
                            CertificatesType = nstu.CertificatesType,
                            IDNumber = nstu.IDNumber,
                            GradeID = nstu.GradeID,
                            SemesterID = nstu.SemesterID,
                            SpecialtyID = nstu.SpecialtyID,
                            StandardID = sp.StandardID,
                            StandardCode = standard.Code,
                            StandardName = standard.Name,
                            EducationID = sp.EducationID,
                            LearningformID = sp.LearningformID,
                            LearnSystem = sp.LearnSystem,
                            EntranceDate = nstu.EntranceDate,
                            Score = nstu.Score,
                            Telephone = nstu.Telephone,
                            ZIPCode = nstu.ZIPCode,
                            EntranceWayID = nstu.EntranceWayID,
                            FeaturesID = nstu.FeaturesID,
                            TerritorialID = nstu.TerritorialID,
                            Address = nstu.Address,
                            Dormitory = nstu.Dormitory,
                            EnrollmentNo = nstu.EnrollmentNo,
                            RecruitPictureUrl = nstu.RecruitPictureUrl,
                            PhotoHasValue = (nstu.RecruitPictureUrl == null || nstu.RecruitPictureUrl == "") ? false : true,
                            IsDoubt = nstu.IsDoubt ?? false,
                            AssignStatus = nstu.AssignStatus,
                            OrderNo = nstu.OrderNo,
                            CollegeID = nstu.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            CampusID = col.CampusID,
                            CampusCode = col.CF_Campus.No,
                            CampusName = col.CF_Campus.Name,
                            ClassmajorID = nstu.ClassmajorID,
                            ClassmajorNo = nstucl.No,
                            ClassmajorName = nstucl.Name,
                            GrademajorID = nstucl.GrademajorID,
                            GrademajorCode = nstucl.CF_Grademajor.Code,
                            GrademajorName = nstucl.CF_Grademajor.Name,
                            FacultymajorID = nstucl.CF_Grademajor.FacultymajorID,
                            FacultymajorCode = nstucl.CF_Grademajor.CF_Facultymajor.Code,
                            FacultymajorName = nstucl.CF_Grademajor.CF_Facultymajor.Name,
                            StudentNo = nstu.StudentNo,
                            Remark = nstu.Remark,
                            RecordStatus = nstu.RecordStatus,
                            CreateUserID = nstu.CreateUserID,
                            CreateTime = nstu.CreateTime,
                            ModifyUserID = nstu.ModifyUserID,
                            ModifyTime = nstu.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询最大录取通知书编号(年级、院系所分组)
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<NewStudentView> GetEnrollmentNo(Expression<Func<CF_NewStudent, bool>> exp)
        {
            var query = from st in NewStudentRepository.GetList(exp)
                        join col in CollegeRepository.Entities
                        on st.CollegeID equals col.CollegeID
                        group st by new { st.GradeID, st.CollegeID, col.No } into g
                        select new NewStudentView
                        {
                            GradeID = g.Key.GradeID.Value,
                            CollegeID = g.Key.CollegeID,
                            CollegeNo = g.Key.No,
                            EnrollmentNo = g.Max(x => x.EnrollmentNo)
                        };

            return query;
        }

        /// <summary>
        /// 根据新生信息查询对应的新生院系专业信息(业务主键:专业ID(Value)、培养层次、学习形式、学制、院系所ID)
        /// </summary>
        /// <param name="expNewStudent"></param>
        /// <returns></returns>
        public IQueryable<NewStudentView> GetFacultymajorByNewStudent(Expression<Func<CF_NewStudent, bool>> expNewStudent)
        {
            var query = from st in NewStudentRepository.GetList(expNewStudent)
                        join sp in SpecialtyRepository.Entities
                        on st.SpecialtyID equals sp.SpecialtyID
                        join fa in FacultymajorRepository.Entities
                        on new { sp.StandardID, sp.EducationID, sp.LearningformID, sp.LearnSystem, st.CollegeID }
                        equals new { fa.StandardID, fa.EducationID, fa.LearningformID, fa.LearnSystem, fa.CollegeID }
                        select new NewStudentView
                        {
                            NewStudentID = st.NewStudentID,
                            GradeID = st.GradeID,
                            SemesterID = st.SemesterID,
                            SpecialtyID = st.SpecialtyID,
                            StandardID = sp.StandardID,
                            EducationID = sp.EducationID,
                            LearningformID = sp.LearningformID,
                            LearnSystem = sp.LearnSystem,
                            CollegeID = st.CollegeID,
                            FacultymajorID = fa.FacultymajorID,
                            FacultymajorCode = fa.Code,
                            FacultymajorName = fa.Name
                        };

            return query;
        }

        /// <summary>
        /// 根据新生信息、院系专业信息查询新生对应的年级专业信息(业务主键:院系专业ID、年级)
        /// </summary>
        /// <param name="expNewStudent"></param>
        /// <param name="expFacultymajor"></param>
        /// <returns></returns>
        public IQueryable<NewStudentView> GetGrademajorByNewStudent(Expression<Func<CF_NewStudent, bool>> expNewStudent, Expression<Func<CF_Facultymajor, bool>> expFacultymajor)
        {
            var query = from st in NewStudentRepository.GetList(expNewStudent)
                        join sp in SpecialtyRepository.Entities
                        on st.SpecialtyID equals sp.SpecialtyID
                        join fa in FacultymajorRepository.GetList(expFacultymajor)
                        on new { sp.StandardID, sp.EducationID, sp.LearningformID, sp.LearnSystem, st.CollegeID }
                        equals new { fa.StandardID, fa.EducationID, fa.LearningformID, fa.LearnSystem, fa.CollegeID }
                        join gr in GrademajorRepository.Entities
                        on new { FacultymajorID = fa.FacultymajorID, GradeID = st.GradeID }
                        equals new { FacultymajorID = gr.FacultymajorID.Value, GradeID = gr.GradeID }
                        select new NewStudentView
                        {
                            NewStudentID = st.NewStudentID,
                            GradeID = st.GradeID,
                            SemesterID = st.SemesterID,
                            SpecialtyID = st.SpecialtyID,
                            StandardID = sp.StandardID,
                            EducationID = sp.EducationID,
                            LearningformID = sp.LearningformID,
                            LearnSystem = sp.LearnSystem,
                            CollegeID = st.CollegeID,
                            FacultymajorID = fa.FacultymajorID,
                            FacultymajorCode = fa.Code,
                            FacultymajorName = fa.Name,
                            GrademajorID = gr.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name
                        };

            return query;
        }

        /// <summary>
        /// 根据新生信息、院系专业信息、年级专业信息查询新生对应的班级信息(业务主键:年级专业ID)
        /// </summary>
        /// <param name="expNewStudent"></param>
        /// <param name="expFacultymajor"></param>
        /// <param name="expGrademajor"></param>
        /// <returns></returns>
        public IQueryable<NewStudentView> GetClassmajorByNewStudent(Expression<Func<CF_NewStudent, bool>> expNewStudent, Expression<Func<CF_Facultymajor, bool>> expFacultymajor, 
                Expression<Func<CF_Grademajor, bool>> expGrademajor)
        {
            var query = from st in NewStudentRepository.GetList(expNewStudent)
                        join sp in SpecialtyRepository.Entities
                        on st.SpecialtyID equals sp.SpecialtyID
                        join fa in FacultymajorRepository.GetList(expFacultymajor)
                        on new { sp.StandardID, sp.EducationID, sp.LearningformID, sp.LearnSystem, st.CollegeID }
                        equals new { fa.StandardID, fa.EducationID, fa.LearningformID, fa.LearnSystem, fa.CollegeID }
                        join gr in GrademajorRepository.GetList(expGrademajor)
                        on new { FacultymajorID = fa.FacultymajorID, GradeID = st.GradeID }
                        equals new { FacultymajorID = gr.FacultymajorID.Value, GradeID = gr.GradeID }
                        join cl in ClassmajorRepository.Entities
                        on gr.GrademajorID equals cl.GrademajorID
                        select new NewStudentView
                        {
                            NewStudentID = st.NewStudentID,
                            GradeID = st.GradeID,
                            SemesterID = st.SemesterID,
                            SpecialtyID = st.SpecialtyID,
                            StandardID = sp.StandardID,
                            EducationID = sp.EducationID,
                            LearningformID = sp.LearningformID,
                            LearnSystem = sp.LearnSystem,
                            CollegeID = st.CollegeID,
                            FacultymajorID = fa.FacultymajorID,
                            FacultymajorCode = fa.Code,
                            FacultymajorName = fa.Name,
                            GrademajorID = gr.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            ClassmajorID = cl.ClassmajorID,
                            ClassmajorNo = cl.No,
                            ClassmajorName = cl.Name
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的专业班级信息(手动生成可使用此方法)
        /// </summary>
        /// <param name="expNewStudent"></param>
        /// <param name="expFa"></param>
        /// <param name="expGr"></param>
        /// <param name="expCl"></param>
        /// <returns></returns>
        public virtual IQueryable<NewStudentView> GetNewStudentCollegeClass(Expression<Func<CF_NewStudent, bool>> expNewStudent, Expression<Func<CF_Facultymajor, bool>> expFacultymajor, 
                Expression<Func<CF_Grademajor, bool>> expGrademajor, Expression<Func<CF_Classmajor, bool>> expClassmajor)
        {
            var query = from st in NewStudentRepository.GetList(expNewStudent)
                        join sp in SpecialtyRepository.Entities
                        on st.SpecialtyID equals sp.SpecialtyID
                        join fa in FacultymajorRepository.GetList(expFacultymajor)
                        on
                        new { sp.StandardID, sp.EducationID, sp.LearningformID, sp.LearnSystem, st.CollegeID }
                        equals
                        new { fa.StandardID, fa.EducationID, fa.LearningformID, fa.LearnSystem, fa.CollegeID }
                        join gr in GrademajorRepository.GetList(expGrademajor)
                        on
                        new { FacultymajorID = fa.FacultymajorID, GradeID = st.GradeID }
                        equals
                        new { FacultymajorID = gr.FacultymajorID.Value, GradeID = gr.GradeID }
                        join cl in ClassmajorRepository.GetList(expClassmajor)
                        on gr.GrademajorID equals cl.GrademajorID
                        select new NewStudentView
                        {
                            NewStudentID = st.NewStudentID,
                            GradeID = st.GradeID,
                            SemesterID = st.SemesterID,
                            SpecialtyID = st.SpecialtyID,
                            StandardID = sp.StandardID,
                            EducationID = sp.EducationID,
                            LearningformID = sp.LearningformID,
                            LearnSystem = sp.LearnSystem,
                            CollegeID = st.CollegeID,
                            FacultymajorID = fa.FacultymajorID,
                            FacultymajorCode = fa.Code,
                            FacultymajorName = fa.Name,
                            GrademajorID = gr.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            ClassmajorID = cl.ClassmajorID,
                            ClassmajorNo = cl.No,
                            ClassmajorName = cl.Name
                        };

            return query;
        }

        /// <summary>
        /// 查询新生信息、学生信息中最大学号(以班级信息ID分组，注：学号在两个表中都可能存在-Concat)
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<NewStudentView> GetMaxStudentNoClass(Expression<Func<CF_Classmajor, bool>> exp)
        {
            //新生信息表
            var queryNewStudent = from nst in NewStudentRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                                  join cl in ClassmajorRepository.GetList(exp)
                                  on nst.ClassmajorID equals cl.ClassmajorID
                                  select new NewStudentView
                                  {
                                      ClassmajorID = cl.ClassmajorID,
                                      ClassmajorNo = cl.No,
                                      StudentNo = nst.StudentNo
                                  };
            //学生信息表
            var queryStudent = from st in StudentRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                               join cl in ClassmajorRepository.GetList(exp)
                               on st.ClassmajorID equals cl.ClassmajorID
                               join u in UserRepository.Entities
                               on st.UserID equals u.UserID
                               select new NewStudentView
                               {
                                   ClassmajorID = cl.ClassmajorID,
                                   ClassmajorNo = cl.No,
                                   StudentNo = u.LoginID
                               };
            //合并后分组
            var query = from stu in queryNewStudent.Concat(queryStudent)
                        group stu by new { stu.ClassmajorID, stu.ClassmajorNo } into g
                        select new NewStudentView
                        {
                            ClassmajorID = g.Key.ClassmajorID,
                            ClassmajorNo = g.Key.ClassmajorNo,
                            StudentNo = g.Max(x => x.StudentNo)
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的学生信息（验证：考生号、准考证号）
        /// </summary>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<NewStudentView> GetStudentViewQueryable(Expression<Func<CF_Recruitstudents, bool>> expRecruitstudents)
        {
            var query = from stu in StudentRepository.Entities
                        join rs in RecruitstudentsRepository.GetList(expRecruitstudents)
                        on stu.UserID equals rs.UserID
                        join us in UserRepository.Entities
                        on stu.UserID equals us.UserID
                        select new NewStudentView
                        {
                            StudentNo = us.LoginID,
                            ExamineeNum = rs.ExamineeNum,
                            AdmissionTicketNo = rs.AdmissionTicketNo,
                            IDNumber = stu.IDNumber,
                            ClassmajorID = stu.ClassmajorID
                        };

            return query;
        }

    }
}