JW/EMiS.DataLogic/EnrollManage/SpecialtyManage/RecruitSpecialtyDAL.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.UniversityManage.SpecialtyClassManage;
using EMIS.ViewModel.EnrollManage.SpecialtyManage;
using EMIS.DataLogic.Repositories;

namespace EMIS.DataLogic.EnrollManage.SpecialtyManage
{
    public class RecruitSpecialtyDAL
    {
        public RecruitSpecialtyRepository recruitSpecialtyRepository { get; set; }
        public CollegeRepository collegeRepository { get; set; }
        public SpecialtyApplyRepository specialtyApplyRepository { get; set; }
        public SpecialtyRepository specialtyRepository { get; set; }
        public SpecialtyClassSettingRepository SpecialtyClassSettingRepository { get; set; }
        public DictionaryItemRepository dictionaryItemRepository { get; set; }
        public FacultymajorRepository facultymajorRepository { get; set; }
        public GrademajorRepository grademajorRepository { get; set; }
        public ClassmajorRepository classmajorRepository { get; set; }
        public SchoolyearRepository schoolyearRepository { get; set; }
        public EducationTypeSettingRepository educationTypeSettingRepository { get; set; }

        /// <summary>
        /// 查询招生专业信息View
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<RecruitSpecialtyView> GetRecruitSpecialtyViewQueryable(Expression<Func<CF_RecruitSpecialty, bool>> exp)
        {
            var query = from rs in recruitSpecialtyRepository.GetList(exp)
                        join sp in specialtyRepository.Entities
                        on rs.SpecialtyID equals sp.SpecialtyID
                        join sc in SpecialtyClassSettingRepository.Entities
                        on rs.SpecialtyID equals sc.SpecialtyID into tempsc
                        from spcs in tempsc.DefaultIfEmpty()
                        join dicst in dictionaryItemRepository.GetList(x => x.DictionaryCode == DictionaryItem.CF_Standard.ToString())
                        on sp.StandardID equals dicst.Value into tempst
                        from standard in tempst.DefaultIfEmpty()
                        select new RecruitSpecialtyView
                        {
                            RecruitSpecialtyID = rs.RecruitSpecialtyID,
                            SpecialtyApplyID = rs.SpecialtyApplyID,
                            CollegeID = rs.CollegeID,
                            CollegeNo = rs.CF_College.No,
                            CollegeName = rs.CF_College.Name,
                            CampusID = rs.CF_College.CampusID,
                            CampusCode = rs.CF_College.CF_Campus.No,
                            CampusName = rs.CF_College.CF_Campus.Name,
                            SpecialtyID = rs.SpecialtyID,
                            StandardID = sp.StandardID,
                            StandardCode = standard.Code,
                            StandardName = standard.Name,
                            EducationID = sp.EducationID,
                            LearningformID = sp.LearningformID,
                            LearnSystem = sp.LearnSystem,
                            ScienceclassID = sp.ScienceclassID,
                            PropertyID = sp.PropertyID,
                            StandardTitle = sp.StandardTitle,
                            StandardLevel = sp.StandardLevel,
                            GradeID = rs.GradeID,
                            SemesterID = rs.SemesterID,
                            ApprovalStatus = rs.ApprovalStatus,
                            ClassNumber = spcs.ClassNumber ?? 1,
                            isGenerated = rs.isGenerated ?? false,
                            RecordStatus = rs.RecordStatus,
                            Remark = rs.Remark,
                            CreateUserID = rs.CreateUserID,
                            CreateTime = rs.CreateTime,
                            ModifyUserID = rs.ModifyUserID,
                            ModifyTime = rs.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询招生专业中未新增的专业信息SpecialtyView
        /// </summary>
        /// <param name="expSpecialty"></param>
        /// <param name="expRecruitSpecialty"></param>
        /// <returns></returns>
        public IQueryable<SpecialtyView> GetSpecialtyViewNoAddQueryable(Expression<Func<CF_Specialty, bool>> expSpecialty, Expression<Func<CF_RecruitSpecialty, bool>> expRecruitSpecialty)
        {
            var query = from sp in specialtyRepository.GetList(expSpecialty)
                        join stdic in dictionaryItemRepository.GetList(x => x.DictionaryCode == DictionaryItem.CF_Standard.ToString())
                        on sp.StandardID equals stdic.Value into tempstdic
                        from standard in tempstdic.DefaultIfEmpty()
                        join rs in recruitSpecialtyRepository.GetList(expRecruitSpecialty)
                        on sp.SpecialtyID equals rs.SpecialtyID into temprs
                        from rsp in temprs.DefaultIfEmpty()
                        where rsp.RecruitSpecialtyID == null
                        select new SpecialtyView
                        {
                            SpecialtyID = sp.SpecialtyID,
                            StandardID = sp.StandardID,
                            StandardCode = standard.Code,
                            StandardName = standard.Name,
                            EducationID = sp.EducationID,
                            LearningformID = sp.LearningformID,
                            LearnSystem = sp.LearnSystem,
                            ScienceclassID = sp.ScienceclassID,
                            PropertyID = sp.PropertyID,
                            StandardTitle = sp.StandardTitle,
                            StandardLevel = sp.StandardLevel,
                            RecordStatus = sp.RecordStatus,
                            Remark = sp.Remark,
                            CreateUserID = sp.CreateUserID,
                            CreateTime = sp.CreateTime,
                            ModifyUserID = sp.ModifyUserID,
                            ModifyTime = sp.ModifyTime,
                        };

            return query;
        }

        /// <summary>
        /// 根据招生专业信息查询对应的院系专业信息(业务主键:专业ID(Value)、培养层次、学习形式、学制、院系所ID)
        /// </summary>
        /// <param name="expRecruitSpecialty"></param>
        /// <returns></returns>
        public IQueryable<RecruitSpecialtyClassView> GetFacultymajorByRecruitSpecialty(Expression<Func<CF_RecruitSpecialty, bool>> expRecruitSpecialty)
        {
            var query = from rs in recruitSpecialtyRepository.GetList(expRecruitSpecialty)
                        join col in collegeRepository.Entities
                        on rs.CollegeID equals col.CollegeID
                        join sp in specialtyRepository.Entities
                        on rs.SpecialtyID equals sp.SpecialtyID
                        join fa in facultymajorRepository.Entities
                        on new { sp.StandardID, sp.EducationID, sp.LearningformID, sp.LearnSystem, rs.CollegeID }
                        equals new { fa.StandardID, fa.EducationID, fa.LearningformID, fa.LearnSystem, fa.CollegeID }
                        select new RecruitSpecialtyClassView
                        {
                            RecruitSpecialtyID = rs.RecruitSpecialtyID,
                            CollegeID = rs.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            GradeID = rs.GradeID,
                            SpecialtyID = rs.SpecialtyID,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            FacultymajorID = fa.FacultymajorID,
                            FacultymajorCode = fa.Code,
                            FacultymajorName = fa.Name,
                            isGenerated = rs.isGenerated ?? false
                        };

            return query;
        }

        /// <summary>
        /// 根据招生专业信息、院系专业信息查询对应的年级专业信息(业务主键:院系专业ID、年级)
        /// </summary>
        /// <param name="expRecruitSpecialty"></param>
        /// <param name="expFacultymajor"></param>
        /// <returns></returns>
        public IQueryable<RecruitSpecialtyClassView> GetGrademajorByRecruitSpecialty(Expression<Func<CF_RecruitSpecialty, bool>> expRecruitSpecialty, 
                Expression<Func<CF_Facultymajor, bool>> expFacultymajor)
        {
            var query = from rs in recruitSpecialtyRepository.GetList(expRecruitSpecialty)
                        join col in collegeRepository.Entities
                        on rs.CollegeID equals col.CollegeID
                        join sp in specialtyRepository.Entities
                        on rs.SpecialtyID equals sp.SpecialtyID
                        join fa in facultymajorRepository.GetList(expFacultymajor)
                        on new { sp.StandardID, sp.EducationID, sp.LearningformID, sp.LearnSystem, rs.CollegeID }
                        equals new { fa.StandardID, fa.EducationID, fa.LearningformID, fa.LearnSystem, fa.CollegeID }
                        join gr in grademajorRepository.Entities
                        on new { FacultymajorID = fa.FacultymajorID, GradeID = rs.GradeID, SemesterID = rs.SemesterID }
                        equals new { FacultymajorID = gr.FacultymajorID.Value, GradeID = gr.GradeID, SemesterID = gr.SemesterID }
                        select new RecruitSpecialtyClassView
                        {
                            RecruitSpecialtyID = rs.RecruitSpecialtyID,
                            CollegeID = rs.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            GradeID = rs.GradeID,
                            SpecialtyID = rs.SpecialtyID,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            FacultymajorID = fa.FacultymajorID,
                            FacultymajorCode = fa.Code,
                            FacultymajorName = fa.Name,
                            GrademajorID = gr.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            SemesterID = rs.SemesterID,
                            isGenerated = rs.isGenerated ?? false
                        };

            return query;
        }

        /// <summary>
        /// 根据招生专业信息、院系专业信息、年级专业信息查询对应的班级信息(业务主键:年级专业ID)
        /// </summary>
        /// <param name="expRecruitSpecialty"></param>
        /// <param name="expFacultymajor"></param>
        /// <param name="expGrademajor"></param>
        /// <returns></returns>
        public IQueryable<RecruitSpecialtyClassView> GetClassmajorByRecruitSpecialty(Expression<Func<CF_RecruitSpecialty, bool>> expRecruitSpecialty, 
                Expression<Func<CF_Facultymajor, bool>> expFacultymajor, Expression<Func<CF_Grademajor, bool>> expGrademajor)
        {
            var query = from rs in recruitSpecialtyRepository.GetList(expRecruitSpecialty)
                        join col in collegeRepository.Entities
                        on rs.CollegeID equals col.CollegeID
                        join sp in specialtyRepository.Entities
                        on rs.SpecialtyID equals sp.SpecialtyID
                        join fa in facultymajorRepository.GetList(expFacultymajor)
                        on new { sp.StandardID, sp.EducationID, sp.LearningformID, sp.LearnSystem, rs.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 = rs.GradeID, SemesterID = rs.SemesterID }
                        equals new { FacultymajorID = gr.FacultymajorID.Value, GradeID = gr.GradeID, SemesterID = gr.SemesterID }
                        join cl in classmajorRepository.Entities
                        on gr.GrademajorID equals cl.GrademajorID
                        select new RecruitSpecialtyClassView
                        {
                            RecruitSpecialtyID = rs.RecruitSpecialtyID,
                            CollegeID = rs.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            GradeID = rs.GradeID,
                            SpecialtyID = rs.SpecialtyID,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            FacultymajorID = fa.FacultymajorID,
                            FacultymajorCode = fa.Code,
                            FacultymajorName = fa.Name,
                            GrademajorID = gr.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            SemesterID = rs.SemesterID,
                            ClassmajorID = cl.ClassmajorID,
                            ClassmajorNo = cl.No,
                            ClassmajorName = cl.Name,
                            isGenerated = rs.isGenerated ?? false
                        };

            return query;
        }

        /// <summary>
        /// 根据院系所、学制查询对应的院系专业信息当前MaxCode（院系所、学制分组查询）
        /// </summary>
        /// <param name="expFacultymajor"></param>
        /// <returns></returns>
        public IQueryable<FacultymajorView> GetFacultymajorMaxCodeByCollegeLearnSystem(Expression<Func<CF_Facultymajor, bool>> expFacultymajor)
        {
            var query = from fa in facultymajorRepository.GetList(expFacultymajor)
                        group fa by new { fa.CollegeID, fa.LearnSystem } into g
                        select new FacultymajorView
                        {
                            CollegeID = g.Key.CollegeID,
                            LearnSystem = g.Key.LearnSystem,
                            Code = g.Max(x => x.Code)
                        };

            return query;
        }

        /// <summary>
        /// 根据院系所、年级查询对应的年级专业信息当前MaxCode（院系所、年级分组查询）
        /// </summary>
        /// <param name="expGrademajor"></param>
        /// <returns></returns>
        public IQueryable<GrademajorView> GetGrademajorMaxCodeByCollegeSchoolyear(Expression<Func<CF_Grademajor, bool>> expGrademajor)
        {
            var query = from gr in grademajorRepository.GetList(expGrademajor)
                        join fa in facultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        group gr by new { fa.CollegeID, gr.GradeID } into g
                        select new GrademajorView
                        {
                            CollegeID = g.Key.CollegeID,
                            GradeID = g.Key.GradeID,
                            Code = g.Max(x => x.Code)
                        };

            return query;
        }

        /// <summary>
        /// 根据年级专业查询对应的班级信息当前MaxCode（年级专业分组查询）
        /// </summary>
        /// <param name="expClassmajor"></param>
        /// <returns></returns>
        public IQueryable<ClassmajorView> GetClassmajorMaxCodeByGrademajor(Expression<Func<CF_Classmajor, bool>> expClassmajor)
        {
            var query = from cl in classmajorRepository.GetList(expClassmajor)
                        group cl by new { cl.GrademajorID, cl.CF_Grademajor.GradeID, cl.CF_Grademajor.CF_Facultymajor.CollegeID } into g
                        select new ClassmajorView
                        {
                            GrademajorID = g.Key.GrademajorID,
                            CollegeID = g.Key.CollegeID,
                            GradeID = g.Key.GradeID,
                            No = g.Max(x => x.No)
                        };

            return query;
        }

    }
}