JW/EMiS.DataLogic/CultureplanManage/PlanManagement/PlanApplicationDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Linq.Expressions;
using EMIS.Entities;
using EMIS.ViewModel;
using EMIS.ViewModel.CacheManage;
using EMIS.ViewModel.CultureplanManage;
using EMIS.DataLogic.Repositories;
using EMIS.ViewModel.CultureplanManage.PlanManagement;

namespace EMIS.DataLogic.CultureplanManage.PlanManagement
{
    public class PlanApplicationDAL
    {
        public PlanApplicationRepository PlanApplicationRepository { get; set; }
        public PlanApplicationTeachingSettingRepository PlanApplicationTeachingSettingRepository { get; set; }
        public PlanApplicationTeachingModeTypeRepository PlanApplicationTeachingModeTypeRepository { get; set; }
        public PlanApplicationTeachingPlaceRepository PlanApplicationTeachingPlaceRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public CoursematerialRepository CoursematerialRepository { get; set; }
        public DepartmentRepository DepartmentRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public CampusRepository CampusRepository { get; set; }
        public SpecialtyRepository SpecialtyRepository { get; set; }
        public SpecialtyCourseRepository SpecialtyCourseRepository { get; set; }
        public SpecialtyCourseTeachingSettingRepository SpecialtyCourseTeachingSettingRepository { get; set; }
        public SpecialtyPlanRepository SpecialtyPlanRepository { get; set; }
        public SpecialtyPlanTeachingSettingRepository SpecialtyPlanTeachingSettingRepository { get; set; }
        public DictionaryItemRepository DictionaryItemRepository { get; set; }

        /// <summary>
        /// 查询计划申请信息PlanApplicationView
        /// 注：普教版本（年级专业）
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<PlanApplicationView> GetPlanApplicationViewQueryable(Expression<Func<EM_PlanApplication, bool>> exp)
        {
            var query = from pa in PlanApplicationRepository.GetList(exp)
                        join past in PlanApplicationTeachingSettingRepository.Entities
                        on pa.PlanApplicationID equals past.PlanApplicationID into temppast
                        from papast in temppast.DefaultIfEmpty()
                        join sy in SchoolyearRepository.Entities
                        on pa.SchoolyearID equals sy.SchoolyearID
                        join cou in CoursematerialRepository.Entities
                        on pa.CoursematerialID equals cou.CoursematerialID
                        join dep in DepartmentRepository.Entities
                        on pa.DepartmentID equals dep.DepartmentID into tempdep
                        from padep in tempdep.DefaultIfEmpty()
                        join gr in GrademajorRepository.Entities
                        on pa.GrademajorID equals gr.GrademajorID
                        join stsy in SchoolyearRepository.Entities
                        on new { SchoolyearID = gr.GradeID.Value, SchoolcodeID = gr.SemesterID.Value }
                        equals new { SchoolyearID = stsy.Years, SchoolcodeID = stsy.SchoolcodeID } into tempstsy
                        from startsy in tempstsy.DefaultIfEmpty()
                        join fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        join spe in SpecialtyRepository.Entities
                        on new { StandardID = fa.StandardID, EducationID = fa.EducationID, LearningformID = fa.LearningformID, LearnSystem = fa.LearnSystem }
                        equals new { StandardID = spe.StandardID, EducationID = spe.EducationID, LearningformID = spe.LearningformID, LearnSystem = spe.LearnSystem }
                        join dicst in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(CF_Standard).Name)
                        on fa.StandardID equals dicst.Value into tempst
                        from fast in tempst.DefaultIfEmpty()
                        join col in CollegeRepository.Entities
                        on fa.CollegeID equals col.CollegeID
                        join cam in CampusRepository.Entities
                        on col.CampusID equals cam.CampusID
                        select new PlanApplicationView
                        {
                            PlanApplicationID = pa.PlanApplicationID,
                            SchoolyearID = pa.SchoolyearID,
                            SchoolyearCode = sy.Code,
                            YearID = sy.Years,
                            Value = sy.Value,
                            SchoolyearNumID = (sy.Value - ((gr.GradeID * 2 - 1) + gr.SemesterID - 1)) / 2 + 1,
                            SchoolcodeID = sy.SchoolcodeID,
                            StarttermID = sy.Value - ((gr.GradeID * 2 - 1) + gr.SemesterID - 1) + 1,
                            GrademajorID = pa.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            GradeID = gr.GradeID,
                            FacultymajorID = gr.FacultymajorID,
                            FacultymajorCode = fa.Code,
                            FacultymajorName = fa.Name,
                            CollegeID = fa.CollegeID,
                            CollegeCode = col.No,
                            CollegeName = col.Name,
                            CampusID = col.CampusID,
                            CampusNo = cam.No,
                            CampusName = cam.Name,
                            StartSchoolyearID = startsy.SchoolyearID,
                            StartSchoolyearCode = startsy.Code,
                            StartValue = startsy.Value,
                            StartSchoolcodeID = gr.SemesterID,
                            SpecialtyID = spe.SpecialtyID,
                            StandardID = fa.StandardID,
                            StandardName = fast.Name,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            CoursematerialID = pa.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            CourseStructureID = pa.CourseStructureID,
                            CourseCategoryID = pa.CourseCategoryID,
                            CourseTypeID = pa.CourseTypeID,
                            CourseQualityID = pa.CourseQualityID,
                            Credit = papast.Credit ?? 0,
                            TheoryCourse = papast.TheoryCourse ?? 0,
                            Practicehours = papast.Practicehours ?? 0,
                            Trialhours = papast.Trialhours ?? 0,
                            Totalhours = (papast.TheoryCourse ?? 0) + (papast.Practicehours ?? 0),
                            TheoryWeeklyNum = papast.TheoryWeeklyNum ?? 0,
                            PracticeWeeklyNum = papast.PracticeWeeklyNum ?? 0,
                            TrialWeeklyNum = papast.TrialWeeklyNum ?? 0,
                            SchoolweeksNum = (papast.TheoryWeeklyNum ?? 0) + (papast.PracticeWeeklyNum ?? 0),
                            WeeklyHours = papast.WeeklyHours ?? 0,
                            WeeklyNum = papast.WeeklyNum ?? 0,
                            StartWeeklyNum = papast.StartWeeklyNum ?? 0,
                            EndWeeklyNum = papast.EndWeeklyNum ?? 0,
                            StartEndWeeklyNum = (papast.StartWeeklyNum ?? 0) + "-" + (papast.EndWeeklyNum ?? 0),
                            DepartmentID = pa.DepartmentID,
                            DepartmentCode = padep.No,
                            DepartmentName = padep.Name,
                            CourseCollegeID = padep.CollegeID,
                            CourseCollegeCode = padep.CF_College.No,
                            CourseCollegeName = padep.CF_College.Name,
                            IsSpecialtycore = pa.IsSpecialtycore ?? false,
                            IsCooperation = pa.IsCooperation ?? false,
                            IsRequired = pa.IsRequired ?? false,
                            IsElective = pa.IsElective ?? false,
                            IsNetworkCourse = pa.IsNetworkCourse ?? false,
                            IsMainCourse = pa.IsMainCourse ?? false,
                            IsNeedMaterial = pa.IsNeedMaterial ?? false,
                            CourseFineID = pa.CourseFineID,
                            PracticeTypeID = pa.PracticeTypeID,
                            TeachinglanguageID = pa.TeachinglanguageID,
                            ExaminationModeID = pa.ExaminationModeID,
                            ResultTypeID = pa.ResultTypeID,
                            HandleModeID = pa.HandleModeID,
                            TeachingModeIDList = pa.EM_PlanApplicationTeachingModeType.Select(x => x.TeachingModeID).ToList(),
                            TeachingPlaceIDList = pa.EM_PlanApplicationTeachingPlace.Select(x => x.TeachingPlace).ToList(),
                            Remark = pa.Remark,
                            ApprovalStatus = pa.RecordStatus,
                            RecordStatus = pa.RecordStatus,
                            CreateUserID = pa.CreateUserID,
                            CreateTime = pa.CreateTime,
                            ModifyUserID = pa.ModifyUserID,
                            ModifyTime = pa.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的未申请专业课程信息
        /// 注：普教版本（年级专业）
        /// </summary>
        /// <param name="expSpecialtyCourse"></param>
        /// <param name="expGrademajor"></param>
        /// <param name="expPlanApplication"></param>
        /// <param name="expSpecialtyPlan"></param>
        /// <returns></returns>
        public IQueryable<SpecialtyCourseView> GetSpecialtyCourseViewNoApplyQueryable(Expression<Func<EM_SpecialtyCourse, bool>> expSpecialtyCourse,
                Expression<Func<CF_Grademajor, bool>> expGrademajor, Expression<Func<EM_PlanApplication, bool>> expPlanApplication, 
                Expression<Func<EM_SpecialtyPlan, bool>> expSpecialtyPlan)
        {
            var query = from sc in SpecialtyCourseRepository.GetList(expSpecialtyCourse)
                        join spe in SpecialtyRepository.Entities
                        on sc.SpecialtyID equals spe.SpecialtyID
                        join fa in FacultymajorRepository.Entities
                        on new { StandardID = spe.StandardID, EducationID = spe.EducationID, LearningformID = spe.LearningformID, LearnSystem = spe.LearnSystem }
                        equals new { StandardID = fa.StandardID, EducationID = fa.EducationID, LearningformID = fa.LearningformID, LearnSystem = fa.LearnSystem }
                        join gr in GrademajorRepository.GetList(expGrademajor)
                        on fa.FacultymajorID equals gr.FacultymajorID
                        join dicst in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(CF_Standard).Name)
                        on spe.StandardID equals dicst.Value into tempst
                        from spest in tempst.DefaultIfEmpty()
                        join cou in CoursematerialRepository.Entities
                        on sc.CoursematerialID equals cou.CoursematerialID
                        join scts in SpecialtyCourseTeachingSettingRepository.Entities
                        on sc.SpecialtyCourseID equals scts.SpecialtyCourseID into tempscts
                        from scscts in tempscts.DefaultIfEmpty()
                        join dep in DepartmentRepository.Entities
                        on sc.DepartmentID equals dep.DepartmentID into tempdep
                        from scdep in tempdep.DefaultIfEmpty()
                        join col in CollegeRepository.Entities
                        on scdep.CollegeID equals col.CollegeID into tempcol
                        from sccol in tempcol.DefaultIfEmpty()
                        join pa in
                            (
                                from pa in PlanApplicationRepository.GetList(expPlanApplication)
                                join sy in SchoolyearRepository.Entities
                                on pa.SchoolyearID equals sy.SchoolyearID
                                join gr in GrademajorRepository.Entities
                                on pa.GrademajorID equals gr.GrademajorID
                                join fa in FacultymajorRepository.Entities
                                on gr.FacultymajorID equals fa.FacultymajorID
                                join spe in SpecialtyRepository.Entities
                                on new { StandardID = fa.StandardID, EducationID = fa.EducationID, LearningformID = fa.LearningformID, LearnSystem = fa.LearnSystem }
                                equals new { StandardID = spe.StandardID, EducationID = spe.EducationID, LearningformID = spe.LearningformID, LearnSystem = spe.LearnSystem }
                                join past in PlanApplicationTeachingSettingRepository.Entities
                                on pa.PlanApplicationID equals past.PlanApplicationID into temppast
                                from papast in temppast.DefaultIfEmpty()
                                join dep in DepartmentRepository.Entities
                                on pa.DepartmentID equals dep.DepartmentID into tempdep
                                from padep in tempdep.DefaultIfEmpty()
                                select new
                                {
                                    PlanApplicationID = pa.PlanApplicationID,
                                    SchoolyearNumID = (sy.Value - ((gr.GradeID * 2 - 1) + gr.SemesterID - 1)) / 2 + 1,
                                    SchoolcodeID = sy.SchoolcodeID,
                                    StarttermID = sy.Value - ((gr.GradeID * 2 - 1) + gr.SemesterID - 1) + 1,
                                    SpecialtyID = spe.SpecialtyID,
                                    GrademajorID = pa.GrademajorID,
                                    CoursematerialID = pa.CoursematerialID,
                                    CourseTypeID = pa.CourseTypeID,
                                    Credit = papast.Credit,
                                    DepartmentID = padep.DepartmentID
                                }
                            )
                        on new { GrademajorID = gr.GrademajorID, StarttermID = sc.StarttermID, CoursematerialID = sc.CoursematerialID }
                        equals new { GrademajorID = pa.GrademajorID.Value, StarttermID = pa.StarttermID, CoursematerialID = pa.CoursematerialID } into temppa
                        from scpa in temppa.DefaultIfEmpty()
                        join sp in
                            (
                                from sp in SpecialtyPlanRepository.GetList(expSpecialtyPlan)
                                join sy in SchoolyearRepository.Entities
                                on sp.SchoolyearID equals sy.SchoolyearID
                                join gr in GrademajorRepository.Entities
                                on sp.GrademajorID equals gr.GrademajorID
                                join fa in FacultymajorRepository.Entities
                                on gr.FacultymajorID equals fa.FacultymajorID
                                join spe in SpecialtyRepository.Entities
                                on new { StandardID = fa.StandardID, EducationID = fa.EducationID, LearningformID = fa.LearningformID, LearnSystem = fa.LearnSystem }
                                equals new { StandardID = spe.StandardID, EducationID = spe.EducationID, LearningformID = spe.LearningformID, LearnSystem = spe.LearnSystem }
                                join spst in SpecialtyPlanTeachingSettingRepository.Entities
                                on sp.SpecialtyPlanID equals spst.SpecialtyPlanID into tempspst
                                from spspst in tempspst.DefaultIfEmpty()
                                join dep in DepartmentRepository.Entities
                                on sp.DepartmentID equals dep.DepartmentID into tempdep
                                from spdep in tempdep.DefaultIfEmpty()
                                select new
                                {
                                    SpecialtyPlanID = sp.SpecialtyPlanID,
                                    SchoolyearNumID = (sy.Value - ((gr.GradeID * 2 - 1) + gr.SemesterID - 1)) / 2 + 1,
                                    SchoolcodeID = sy.SchoolcodeID,
                                    StarttermID = sy.Value - ((gr.GradeID * 2 - 1) + gr.SemesterID - 1) + 1,
                                    SpecialtyID = spe.SpecialtyID,
                                    GrademajorID = sp.GrademajorID,
                                    CoursematerialID = sp.CoursematerialID,
                                    CourseTypeID = sp.CourseTypeID,
                                    Credit = spspst.Credit,
                                    DepartmentID = spdep.DepartmentID
                                }
                            )
                        on new { GrademajorID = gr.GrademajorID, StarttermID = sc.StarttermID, CoursematerialID = sc.CoursematerialID }
                        equals new { GrademajorID = sp.GrademajorID.Value, StarttermID = sp.StarttermID, CoursematerialID = sp.CoursematerialID } into tempsp
                        from scsp in tempsp.DefaultIfEmpty()
                        where scpa.PlanApplicationID == null && scsp.SpecialtyPlanID == null
                        select new SpecialtyCourseView
                        {
                            SpecialtyCourseID = sc.SpecialtyCourseID,
                            SpecialtyID = sc.SpecialtyID,
                            StandardID = spe.StandardID,
                            StandardName = spest.Name,
                            EducationID = spe.EducationID,
                            LearningformID = spe.LearningformID,
                            LearnSystem = spe.LearnSystem,
                            StarttermID = sc.StarttermID,
                            CoursematerialID = sc.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            CourseStructureID = sc.CourseStructureID,
                            CourseCategoryID = sc.CourseCategoryID,
                            CourseTypeID = sc.CourseTypeID,
                            CourseQualityID = sc.CourseQualityID,
                            Credit = scscts.Credit ?? 0,
                            TheoryCourse = scscts.TheoryCourse ?? 0,
                            Practicehours = scscts.Practicehours ?? 0,
                            Trialhours = scscts.Trialhours ?? 0,
                            Totalhours = (scscts.TheoryCourse ?? 0) + (scscts.Practicehours ?? 0),
                            TheoryWeeklyNum = scscts.TheoryWeeklyNum ?? 0,
                            PracticeWeeklyNum = scscts.PracticeWeeklyNum ?? 0,
                            TrialWeeklyNum = scscts.TrialWeeklyNum ?? 0,
                            SchoolweeksNum = (scscts.TheoryWeeklyNum ?? 0) + (scscts.PracticeWeeklyNum ?? 0),
                            WeeklyHours = scscts.WeeklyHours ?? 0,
                            WeeklyNum = scscts.WeeklyNum ?? 0,
                            StartWeeklyNum = scscts.StartWeeklyNum ?? 0,
                            EndWeeklyNum = scscts.EndWeeklyNum ?? 0,
                            StartEndWeeklyNum = (scscts.StartWeeklyNum ?? 0) + "-" + (scscts.EndWeeklyNum ?? 0),
                            DepartmentID = sc.DepartmentID,
                            DepartmentCode = scdep.No,
                            DepartmentName = scdep.Name,
                            CollegeID = scdep.CollegeID,
                            CollegeCode = sccol.No,
                            CollegeName = sccol.Name,
                            IsSpecialtycore = sc.IsSpecialtycore ?? false,
                            IsCooperation = sc.IsCooperation ?? false,
                            IsRequired = sc.IsRequired ?? false,
                            IsElective = sc.IsElective ?? false,
                            IsNetworkCourse = sc.IsNetworkCourse ?? false,
                            IsMainCourse = sc.IsMainCourse ?? false,
                            IsNeedMaterial = sc.IsNeedMaterial ?? false,
                            CourseFineID = sc.CourseFineID,
                            PracticeTypeID = sc.PracticeTypeID,
                            TeachinglanguageID = sc.TeachinglanguageID,
                            ExaminationModeID = sc.ExaminationModeID,
                            ResultTypeID = sc.ResultTypeID,
                            HandleModeID = sc.HandleModeID,
                            TeachingModeIDList = sc.EM_TeachingModeType.Select(x => x.TeachingModeID).ToList(),
                            TeachingPlaceIDList = sc.EM_TeachingPlace.Select(x => x.TeachingPlace).ToList(),
                            IsEnable = sc.IsEnable ?? false,
                            Remark = sc.Remark,
                            RecordStatus = sc.RecordStatus,
                            CreateUserID = sc.CreateUserID,
                            CreateTime = sc.CreateTime,
                            ModifyUserID = sc.ModifyUserID,
                            ModifyTime = sc.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询计划申请信息对应的授课方式
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<int?> GetTeachingModeTypeQueryble(Expression<Func<EM_PlanApplication, bool>> exp)
        {
            var query = from pa in PlanApplicationRepository.GetList(exp)
                        join patm in PlanApplicationTeachingModeTypeRepository.Entities
                        on pa.PlanApplicationID equals patm.PlanApplicationID
                        join dictm in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(CF_TeachingMode).Name)
                        on patm.TeachingModeID equals dictm.Value
                        select dictm.Value;

            return query;
        }

        /// <summary>
        /// 查询计划申请信息对应的授课方式
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<int?> GetTeachingPlaceQueryble(Expression<Func<EM_PlanApplication, bool>> exp)
        {
            var query = from pa in PlanApplicationRepository.GetList(exp)
                        join patp in PlanApplicationTeachingPlaceRepository.Entities
                        on pa.PlanApplicationID equals patp.PlanApplicationID
                        join dictp in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(EMIS.ViewModel.EM_TeachingPlace).Name)
                        on patp.TeachingPlace equals dictp.Value
                        select dictp.Value;

            return query;
        }

    }
}