JW/EMiS.DataLogic/EducationSchedule/RealtimeDAL.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;
using EMIS.ViewModel;


namespace EMIS.DataLogic.EducationSchedule
{
    public class RealtimeDAL
    {
        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 EducationSchedulingWeekNumRepository EducationSchedulingWeekNumRepository { get; set; }
        public EducationSchedulingRepository EducationSchedulingRepository { 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 ClassroomExcessiveUseRepository ClassroomExcessiveUseRepository { get; set; }
        public ClassroomExcessiveUseSchedulingRepository ClassroomExcessiveUseSchedulingRepository { get; set; }
        public ClassroomExcessiveUseSchedulingWeekNumRepository ClassroomExcessiveUseSchedulingWeekNumRepository { get; set; }
        public DepartmentRepository DepartmentRepository { get; set; }

        /// <summary>
        /// 查询教学任务班数据
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<EducationMissionClassView> GetEducationMissionClassViewQueryble(Expression<Func<EM_EducationMissionClass, bool>> missionClassExp,
            Expression<Func<ES_EducationScheduling, bool>> exeducationScheduling,
            Expression<Func<ES_EducationSchedulingWeekNum, bool>> exducationSchedulingWeekNum,
            Expression<Func<ES_ClassroomExcessiveUse, bool>> exexcessiveUse,
            Expression<Func<ES_ClassroomExcessiveUseScheduling, bool>> exexcessiveUseScheduling,
            Expression<Func<ES_ClassroomExcessiveUseSchedulingWeekNum, bool>> exexcessiveUseSchedulingWeekNum
            )
        {
            var query = (from missionClass in EducationMissionClassRepository.GetList(missionClassExp)
                         join mission in EducationMissionRepository.Entities on missionClass.EducationMissionID equals mission.EducationMissionID
                         from dep in DepartmentRepository.Entities.Where(x => x.DepartmentID == mission.DepartmentID)
                         from opencol in CollegeRepository.Entities.Where(x => x.CollegeID == dep.CollegeID)
                         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 schedulingClass in
                             (
                                 (from sc in EducationSchedulingClassRepository.Entities
                                  join es in EducationSchedulingRepository.GetList(exeducationScheduling) on sc.EducationSchedulingClassID equals es.EducationSchedulingClassID
                                  join cr in ClassroomRepository.Entities on es.ClassroomID equals cr.ClassroomID
                                  join emt in CoursesTimeRepository.Entities on es.CoursesTimeID equals emt.CoursesTimeID
                                  join esw in EducationSchedulingWeekNumRepository.GetList(exducationSchedulingWeekNum) on es.EducationSchedulingID equals esw.EducationSchedulingID
                                  group sc by new { sc.EducationMissionClassID, esw, es, emt, cr } into g
                                  select new
                                  {
                                      EducationMissionClassID = g.Key.EducationMissionClassID,
                                      ClassNum = g.Sum(x => x.CF_Student.Count()),
                                      Week = g.Key.esw.WeekNum,
                                      WeekDay = g.Key.es.Weekday,
                                      StarTimes = g.Key.emt.StartTimes,
                                      CoursesTimeID = g.Key.emt.CoursesTimeID,
                                      Times = g.Key.emt.StartTimes + "-" + g.Key.emt.EndTimes,
                                      TimesHoure = g.Key.emt.StartHour + ":" + g.Key.emt.StartMinutes + "-" + g.Key.emt.EndHour + ":" + g.Key.emt.EndMinutes,
                                      ClassroomName = g.Key.cr.Name,
                                      ClassroomID = g.Key.cr.ClassroomID
                                  }
                                 )
                                 ) on missionClass.EducationMissionClassID equals schedulingClass.EducationMissionClassID
                         select new EducationMissionClassView
                         {
                             EducationMissionID = missionClass.EducationMissionID,
                             EducationMissionClassID = missionClass.EducationMissionClassID,
                             MainScheduleClassID = missionClass.MainScheduleClassID,
                             TeachingModeID = missionClass.TeachingModeID ?? (int)CF_TeachingMode.Theory,
                             SchoolyearID = mission.SchoolyearID,
                             SchoolyearCode = schoolyear.Code,
                             Name = missionClass.Name,
                             CoursematerialID = missionClass.CoursematerialID,
                             CourseCode = course.CourseCode,
                             CourseName = course.CourseName,
                             CampusID = college.CampusID,
                             CollegeID = faculty.CollegeID,
                             CollegeNo = college.No,
                             CollegeName = college.Name,
                             OpenCollegeID = dep.CollegeID,
                             OpenCollegeNo = opencol.No,
                             OpenCollegeName = opencol.Name,
                             DepartmentID = mission.DepartmentID,
                             DepartmentName = mission.CF_Department.Name,
                             WeekNum = schedulingClass.Week,
                             Weekday = schedulingClass.WeekDay,
                             //CoursesTimeID = schedulingClass.CoursesTimeID,
                             newCoursesTimeID = schedulingClass.CoursesTimeID,
                             StarTimes = schedulingClass.StarTimes,
                             Times = schedulingClass.Times,
                             TimesHoure = schedulingClass.TimesHoure,
                             GrademajorID = grade.GrademajorID,
                             StandardID = faculty.StandardID,
                             GradeYearID = grade.GradeID,
                             ClassroomName = schedulingClass.ClassroomName,
                             ClassroomID = schedulingClass.ClassroomID,
                             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,
                             ClassNum = (schedulingClass.EducationMissionClassID == null ? 0 : schedulingClass.ClassNum),
                             SchedulingClassNum = missionClass.EM_EducationSchedulingClass.Count,
                             SchedulingClass = mission.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,
                             TrialWeeklyNum = teachSetting.TrialWeeklyNum,
                             WeeklyNum = teachSetting.WeeklyNum,
                             WeeklyHours = teachSetting.WeeklyHours,
                             ApprovalStatus = missionClass.RecordStatus,
                             CreateTime = missionClass.CreateTime,
                             CreateUserID = missionClass.CreateUserID,
                             Remark = missionClass.Remark,
                         })
                        .Concat(
                        from eu in ClassroomExcessiveUseRepository.GetList(exexcessiveUse)
                        join schoolyear in SchoolyearRepository.Entities on eu.SchoolyearID equals schoolyear.SchoolyearID
                        join college in CollegeRepository.Entities on eu.CollegeID equals college.CollegeID
                        join cr in ClassroomRepository.Entities on eu.ClassroomID equals cr.ClassroomID
                        join eus in ClassroomExcessiveUseSchedulingRepository.GetList(exexcessiveUseScheduling) on eu.ClassroomExcessiveUseID equals eus.ClassroomExcessiveUseID
                        join emt in CoursesTimeRepository.Entities on eus.CoursesTimeID equals emt.CoursesTimeID
                        join eusw in ClassroomExcessiveUseSchedulingWeekNumRepository.GetList(exexcessiveUseSchedulingWeekNum) on eus.ClassroomExcessiveUseSchedulingID equals eusw.ClassroomExcessiveUseSchedulingID
                        select new EducationMissionClassView
                        {
                            EducationMissionID = null,
                            EducationMissionClassID = null,
                            MainScheduleClassID = null,
                            TeachingModeID = (int)CF_TeachingMode.Theory,
                            SchoolyearID = eu.SchoolyearID,
                            SchoolyearCode = schoolyear.Code,
                            Name = "教室占用",
                            CoursematerialID = null,
                            CourseCode = null,
                            CourseName = null,
                            CampusID = college.CampusID,
                            CollegeID = null,
                            CollegeNo = null,
                            CollegeName = null,
                            OpenCollegeID = eu.CollegeID,
                            OpenCollegeNo = college.No,
                            OpenCollegeName = college.Name,
                            DepartmentID = null,
                            DepartmentName = null,
                            WeekNum = eusw.WeekNum,
                            Weekday = eus.Weekday,
                            //CoursesTimeID = emt.CoursesTimeID,
                            newCoursesTimeID = emt.CoursesTimeID,
                            StarTimes = emt.StartTimes,
                            Times = emt.StartTimes + "-" + emt.EndTimes,
                            TimesHoure = emt.StartHour + ":" + emt.StartMinutes + "-" + emt.EndHour + ":" + emt.EndMinutes,
                            GrademajorID = null,
                            StandardID = null,
                            GradeYearID = null,
                            ClassroomName = cr.Name,
                            ClassroomID = cr.ClassroomID,
                            CourseStructureID = null,
                            CourseCategoryID = null,
                            CourseTypeID = null,
                            CourseQualityID = null,
                            ExaminationModeID = null,
                            TeachinglanguageID = null,
                            HandleModeID = null,
                            IsNeedMaterial = false,
                            OrderNo = 0,
                            ClassNum = 0,
                            SchedulingClassNum = 0,
                            SchedulingClass = null,
                            StartWeeklyNum = 0,
                            EndWeeklyNum = 0,
                            StartWeeklyNumReserved = 0,
                            EndWeeklyNumReserved = 0,
                            Credit = null,
                            TheoryCourse = null,
                            Practicehours = null,
                            Trialhours = null,
                            TheoryWeeklyNum = null,
                            PracticeWeeklyNum = null,
                            TrialWeeklyNum = null,
                            WeeklyNum = null,
                            WeeklyHours = null,
                            ApprovalStatus = null,
                            CreateTime = null,
                            CreateUserID = null,
                            Remark = ""
                        });

            return query.Distinct();
        }

        public IQueryable<EducationMissionClassView> GetNewEducationMissionClassViewQueryble(Expression<Func<EM_EducationMissionClass, bool>> missionClassExp,
            Expression<Func<ES_EducationScheduling, bool>> exeducationScheduling,
            Expression<Func<ES_EducationSchedulingWeekNum, bool>> exducationSchedulingWeekNum,
            Expression<Func<ES_ClassroomExcessiveUse, bool>> exexcessiveUse,
            Expression<Func<ES_ClassroomExcessiveUseScheduling, bool>> exexcessiveUseScheduling,
            Expression<Func<ES_ClassroomExcessiveUseSchedulingWeekNum, bool>> exexcessiveUseSchedulingWeekNum,
            Expression<Func<CF_Classroom, bool>> exeClassroom,
            Expression<Func<EM_CoursesTime, bool>> exeCoursesTime
            )
        {
            var weekNum = from eswn in EducationSchedulingWeekNumRepository.Entities
                              select eswn.WeekNum.Value;
            var weekNumList = weekNum.Distinct();
            var basequery = GetEducationMissionClassViewQueryble(missionClassExp, exeducationScheduling, exducationSchedulingWeekNum, exexcessiveUse, exexcessiveUseScheduling, exexcessiveUseSchedulingWeekNum);
            var query = from selectroom in (from room in ClassroomRepository.Entities.Where(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                        from w in weekNumList
                        from day in DictionaryItemRepository.Entities.Where(x => x.DictionaryCode == "ES_WeekDay")
                        from cou in CoursesTimeRepository.Entities.Where(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                        select new 
                        {
                            room.ClassroomID,
                            room.Name,
                            WeekNum = w,
                            Weekday = day.Value,
                            cou.CoursesTimeID,
                            cou.StartTimes,
                            cou.EndTimes,
                            cou.StartHour,
                            cou.StartMinutes,
                            cou.EndHour,
                            cou.EndMinutes,
                        })
                        from b in basequery.Where(x => x.ClassroomID == selectroom.ClassroomID && x.WeekNum == selectroom.WeekNum && x.Weekday == selectroom.Weekday && x.StarTimes == selectroom.StartTimes).DefaultIfEmpty()
                        select new EducationMissionClassView
                        {
                            EducationMissionID = b.EducationMissionID,
                            EducationMissionClassID = b.EducationMissionClassID,
                            MainScheduleClassID = b.MainScheduleClassID,
                            TeachingModeID = b.TeachingModeID != null ? b.TeachingModeID:(int)CF_TeachingMode.Theory,
                            SchoolyearID = b.SchoolyearID,
                            SchoolyearCode = b.SchoolyearCode != null ? b.SchoolyearCode : "",
                            Name = b.Name != null ? b.Name : "",
                            CoursematerialID = b.CoursematerialID,
                            CourseCode = b.CourseCode != null ? b.CourseCode : "",
                            CourseName = b.CourseName != null ? b.CourseName : "",
                            CampusID = b.CampusID,
                            CollegeID = b.CollegeID,
                            CollegeNo = b.CollegeNo != null ? b.CollegeNo : "",
                            CollegeName = b.CollegeName != null ? b.CollegeName : "",
                            OpenCollegeID = b.CollegeID,
                            OpenCollegeNo = b.OpenCollegeNo != null ? b.OpenCollegeNo : "",
                            OpenCollegeName = b.OpenCollegeName != null ? b.OpenCollegeName : "",
                            DepartmentID = b.DepartmentID,
                            DepartmentName = b.DepartmentName != null ? b.DepartmentName : "",
                            WeekNum = selectroom.WeekNum,
                            Weekday = selectroom.Weekday,
                            newCoursesTimeID = selectroom.CoursesTimeID,
                            StarTimes = selectroom.StartTimes,
                            Times = selectroom.StartTimes + "-" + selectroom.EndTimes,
                            TimesHoure = selectroom.StartHour + ":" + selectroom.StartMinutes + "-" + selectroom.EndHour + ":" + selectroom.EndMinutes,
                            GrademajorID = b.GrademajorID,
                            StandardID = b.StandardID,
                            GradeYearID = b.GradeYearID,
                            ClassroomName = selectroom.Name,
                            ClassroomID = selectroom.ClassroomID,
                            CourseStructureID = b.CourseStructureID,
                            CourseCategoryID = b.CourseCategoryID,
                            CourseTypeID = b.CourseTypeID,
                            CourseQualityID = b.CourseQualityID,
                            ExaminationModeID = b.ExaminationModeID,
                            TeachinglanguageID = b.TeachinglanguageID,
                            HandleModeID = b.HandleModeID,
                            IsNeedMaterial = b.IsNeedMaterial != null ? b.IsNeedMaterial : false,
                            OrderNo = b.OrderNo,
                            ClassNum =  b.ClassNum,
                            SchedulingClassNum = b.SchedulingClassNum,
                            SchedulingClass = b.SchedulingClass != null ? b.SchedulingClass : "",
                            StartWeeklyNum = b.StartWeeklyNum,
                            EndWeeklyNum = b.EndWeeklyNum,
                            StartWeeklyNumReserved = b.StartWeeklyNumReserved,
                            EndWeeklyNumReserved = b.EndWeeklyNumReserved,
                            Credit = b.Credit,
                            TheoryCourse = b.TheoryCourse,
                            Practicehours = b.Practicehours,
                            Trialhours = b.Trialhours,
                            TheoryWeeklyNum = b.TheoryWeeklyNum,
                            PracticeWeeklyNum = b.PracticeWeeklyNum,
                            TrialWeeklyNum = b.TrialWeeklyNum,
                            WeeklyNum = b.WeeklyNum,
                            WeeklyHours = b.WeeklyHours,
                            ApprovalStatus = b.ApprovalStatus,
                            CreateTime = b.CreateTime,
                            CreateUserID = b.CreateUserID,
                            Remark = b.Remark != null ? b.Remark : "",
                        };

            return query;
        }

    }
}