JW/EMiS.DataLogic/EducationSchedule/EducationMissionClassScheduleDAL.cs

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

namespace EMIS.DataLogic.EducationSchedule
{
    public class EducationMissionClassScheduleDAL
    {
        public EducationMissionClassRepository EducationMissionClassRepository { get; set; }
        public EducationMissionClassTeachingSettingRepository EducationMissionClassTeachingSettingRepository { get; set; }
        public DictionaryItemRepository DictionaryItemRepository { get; set; }
        public EducationMissionClassSettingsRepository EducationMissionClassSettingsRepository { get; set; }
        public MissionClassTeacherRepository MissionClassTeacherRepository { get; set; }
        public EducationSchedulingClassRepository EducationSchedulingClassRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public CourseProcessRepository CourseProcessRepository { get; set; }
        public EducationMissionRepository EducationMissionRepository { get; set; }
        public ClassroomRepository ClassroomRepository { get; set; }
        public CoursesTimeRepository CoursesTimeRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public CoursematerialRepository CoursematerialRepository { get; set; }
        public EducationSchedulingWeekNumRepository EducationSchedulingWeekNumRepository { get; set; }
        public EducationSchedulingRepository EducationSchedulingRepository { get; set; }

        /// <summary>
        /// 查询教学任务班数据
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<EducationMissionClassView> GetEducationMissionClassViewQueryble(Expression<Func<EM_EducationMissionClass, bool>> missionClassExp,
            Expression<Func<EM_ExecutablePlan, bool>> executablePlanExp, Expression<Func<EM_ExecutableOptionalCourse, bool>> executableOptionalCourseExp,
            Expression<Func<EM_ExecutableFreeSelectionCouse, bool>> executableFreeSelectionCourseCourseExp)
        {
            var query = from missionClass in EducationMissionClassRepository.GetList(missionClassExp)
                        join mission in EducationMissionRepository.Entities on missionClass.EducationMissionID equals mission.EducationMissionID
                        join teachSetting in EducationMissionClassTeachingSettingRepository.Entities on missionClass.EducationMissionClassID equals teachSetting.EducationMissionClassID
                        join classmajor in ClassmajorRepository.Entities on missionClass.MainScheduleClassID equals classmajor.ClassmajorID into dclassmajor 
                        from classmajor in dclassmajor.DefaultIfEmpty() 
                        join grade in GrademajorRepository.Entities on classmajor.GrademajorID equals grade.GrademajorID into dgrade 
                        from grade in dgrade.DefaultIfEmpty()
                        join faculty in FacultymajorRepository.Entities on grade.FacultymajorID equals faculty.FacultymajorID into dfaculty
                        from faculty in dfaculty.DefaultIfEmpty() 
                        join college in CollegeRepository.Entities on mission.CollegeID equals college.CollegeID
                        join schoolyear in SchoolyearRepository.Entities on mission.SchoolyearID equals schoolyear.SchoolyearID
                        join course in CoursematerialRepository.Entities on missionClass.CoursematerialID equals course.CoursematerialID 
                        join sc in
                            (
                                from sc in EducationSchedulingClassRepository.Entities
                                group sc by sc.EducationMissionClassID into g
                                select new
                                {
                                    EducationMissionClassID = g.Key,
                                    ClassNum = g.Sum(x => x.CF_Student.Count())
                                }
                                ) on missionClass.EducationMissionClassID equals sc.EducationMissionClassID into dsc
                        join cp in
                            (from cp in CourseProcessRepository.Entities
                             group cp by cp.EducationMissionClassID into g
                             select new
                             {
                                 EducationMissionClassID = g.Key,
                                 CourseProcessTimes = g.Sum(x => x.Times)
                             }) on missionClass.EducationMissionClassID equals cp.EducationMissionClassID into gcp

                        join week in
                            (from es in EducationSchedulingRepository.Entities 
                             join ct in CoursesTimeRepository.Entities on es.CoursesTimeID equals ct.CoursesTimeID 
                             from eswn in es.ES_EducationSchedulingWeekNum
                             group ct by es.EM_EducationSchedulingClass.EducationMissionClassID into g
                             select new
                             {
                                 EducationMissionClassID = g.Key,
                                 ScheduledTimes = g.Sum(x => x.EndTimes - x.StartTimes + 1)
                             }) on missionClass.EducationMissionClassID equals week.EducationMissionClassID into esp
                        from wgcp in esp.DefaultIfEmpty()
                        from gccp in gcp.DefaultIfEmpty()

                        from esc in dsc.DefaultIfEmpty()
                        join mc in ClassmajorRepository.Entities on missionClass.MainScheduleClassID equals mc.ClassmajorID into dmc
                        from emc in dmc.DefaultIfEmpty()

                        select new EducationMissionClassView
                        {
                            EducationMissionID = missionClass.EducationMissionID,
                            EducationMissionClassID = missionClass.EducationMissionClassID,
                            MainScheduleClassID = missionClass.MainScheduleClassID,
                            TeachingModeID = missionClass.TeachingModeID ?? 1,
                            SchoolyearID = missionClass.EM_EducationMission.SchoolyearID,
                            SchoolyearCode = missionClass.EM_EducationMission.CF_Schoolyear.Code,
                            Name = missionClass.Name,
                            CoursematerialID = missionClass.CoursematerialID,
                            CourseCode = course.CourseCode,
                            CourseName = course.CourseName,
                            CampusID = college.CampusID,
                            CollegeID = college.CollegeID,
                            CollegeNo = college.No,
                            CollegeName = college.Name,
                            DepartmentID = mission.DepartmentID,
                            DepartmentName = mission.CF_Department.Name,
                            GrademajorID = classmajor.GrademajorID,
                            StandardID = faculty.StandardID,
                            GradeYearID = grade.GradeID,
                            ClassroomID = missionClass.ClassroomID,
                            ClassroomName = missionClass.CF_Classroom.Name,
                            ClassroomTypeID = missionClass.ClassroomTypeID,
                            CourseStructureID = missionClass.CourseStructureID,
                            CourseCategoryID = missionClass.CourseCategoryID,
                            CourseTypeID = missionClass.CourseTypeID,
                            CourseQualityID = missionClass.CourseQualityID,
                            ExaminationModeID = missionClass.ExaminationModeID,
                            TeachinglanguageID = missionClass.TeachinglanguageID,
                            HandleModeID = missionClass.HandleModeID,
                            IsNeedMaterial = missionClass.IsNeedMaterial ?? false,
                            OrderNo = missionClass.OrderNo ?? 0,
                            SchedulingClassNum = missionClass.EM_EducationSchedulingClass.Count,
                            ClassNum = (esc.EducationMissionClassID == null ? 0 : esc.ClassNum),
                            MainScheduleClassGrademajorID = emc.GrademajorID,
                            MainScheduleClassCollegeID = emc.CF_Grademajor.CF_Facultymajor.CollegeID,
                            SchedulingClass = missionClass.EM_EducationMission.ClassName, 
                            StartWeeklyNum = teachSetting.StartWeeklyNum,
                            EndWeeklyNum = teachSetting.EndWeeklyNum,
                            StartWeeklyNumReserved = missionClass.EM_EducationMissionClassSettings.Min(w => w.WeeklyNum).Value,
                            EndWeeklyNumReserved = missionClass.EM_EducationMissionClassSettings.Max(w => w.WeeklyNum).Value,
                            Credit = teachSetting.Credit,
                            TheoryCourse = teachSetting.TheoryCourse,
                            Practicehours = teachSetting.Practicehours,
                            Trialhours = teachSetting.Trialhours,
                            TheoryWeeklyNum = teachSetting.TheoryWeeklyNum,
                            PracticeWeeklyNum = teachSetting.PracticeWeeklyNum,
                            ScheduledTimes = wgcp.ScheduledTimes == null ? 0 : wgcp.ScheduledTimes,
                            TrialWeeklyNum = teachSetting.TrialWeeklyNum,
                            WeeklyNum = teachSetting.WeeklyNum,
                            WeeklyHours = teachSetting.WeeklyHours,
                            ApprovalStatus = missionClass.RecordStatus,
                            CourseProcessTimes = gccp.CourseProcessTimes,
                            CreateTime = missionClass.CreateTime,
                            CreateUserID = missionClass.CreateUserID,
                            Remark = missionClass.Remark
                        };
            return query;
        }

    }
}