JW/EMiS.DataLogic/EducationSchedule/EducationMissionClassSchedulingASdultDAL.cs

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

namespace EMIS.DataLogic.EducationSchedule
{
    public class EducationMissionClassSchedulingASdultDAL
    {
        public EducationMissionClassRepository EducationMissionClassRepository { get; set; }
        public DictionaryItemRepository DictionaryItemRepository { get; set; }
        public EducationMissionClassSettingsRepository EducationMissionClassSettingsRepository { get; set; }
        public EducationMissionClassTeachingSettingRepository EducationMissionClassTeachingSettingRepository { get; set; }
        public EducationMissionRepository EducationMissionRepository { 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 CourseTimeHoursRepository CourseTimeHoursRepository { get; set; }
        public CoursematerialRepository CoursematerialRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public DepartmentRepository DepartmentRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }

        public EducationMissionExamTimeRepository EducationMissionExamTimeRepository { get; set; }
        public EducationMissionExamWeekNumRepository EducationMissionExamWeekNumRepository { get; set; }
        public EducationSchedulingWeekNumRepository EducationSchedulingWeekNumRepository { get; set; }
        public AdultEducationScheduleRepository AdultEducationScheduleRepository{get;set;}
        public AdultEducationScheduleTeacherRepository AdultEducationScheduleTeacherRepository{get;set;}
        public EducationSchedulingRepository EducationSchedulingRepository { get; set; }

        
        /// <summary>
        /// 查询教学任务班数据
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<EducationMissionImportClassView> GetEducationMissionClassViewQuery(Expression<Func<EM_EducationMissionClass, bool>> missionClassExp,
            Expression<Func<EM_ExecutablePlan, bool>> executablePlanExp, Expression<Func<EM_ExecutableOptionalCourse, bool>> executableOptionalCourseExp,
            Expression<Func<EM_ExecutableFreeSelectionCouse, bool>> executableFreeSelectionCourseCourseExp)
        {
            TimeSpan s = new TimeSpan();
            DateTime d = new DateTime();
            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 department in DepartmentRepository.Entities on mission.DepartmentID equals department.DepartmentID 
                        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
                                group sc by sc.EducationMissionClassID into g
                                select new
                                {
                                    EducationMissionClassID = g.Key,
                                    ClassNum = g.Sum(x => x.CF_Student.Count())
                                }
                                ) on missionClass.EducationMissionClassID equals schedulingClass.EducationMissionClassID into dschedulingClass
                        from schedulingClass in dschedulingClass.DefaultIfEmpty()
                        join courseProcess 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 courseProcess.EducationMissionClassID into dcourseProcess
                        from courseProcess in dcourseProcess.DefaultIfEmpty()
                        join week in
                            (from schedule in AdultEducationScheduleRepository.Entities
                             group schedule by schedule.EM_EducationSchedulingClass.EducationMissionClassID into g
                             select new
                             {
                                 EducationMissionClassID = g.Key,
                                 ScheduledTimes = g.Select(x => x.ScheduleDate).ToList(),
                                 ClassroomName = g.Select(x => x.CF_Classroom.Name).FirstOrDefault()
                             }) on missionClass.EducationMissionClassID equals week.EducationMissionClassID into dweek
                        from week in dweek.DefaultIfEmpty()
                        join assistant in UserRepository.Entities on classmajor.UserID equals assistant.UserID into dassistant
                        from assistant in dassistant.DefaultIfEmpty()
                        join examtime in EducationMissionExamTimeRepository.Entities on missionClass.EducationMissionID equals examtime.EducationMissionID into dexamtime
                        from examtime in dexamtime.DefaultIfEmpty()
                        join examweek in
                            (from ew in EducationMissionExamWeekNumRepository.Entities
                             group ew by ew.EducationMissionID into g
                             select new
                             {
                                 EducationMissionID = g.Key,
                                 WeeklyNum = g.Select(x => x.WeeklyNum).ToList()
                             })
                            on missionClass.EducationMissionID equals examweek.EducationMissionID into dexamweek
                        from examweek in dexamweek.DefaultIfEmpty()
                        from courseTime in missionClass.EM_CoursesTime.DefaultIfEmpty()
                        join hours in CourseTimeHoursRepository.Entities
                        on new { CoursesTimeID = (Guid?)courseTime.CoursesTimeID, GradeYearID = grade.GradeID } equals new { CoursesTimeID = hours.CoursesTimeID, GradeYearID = hours.Years }
                        into dhours
                        from hours in dhours.DefaultIfEmpty() 
                        join classroom in ClassroomRepository.Entities on missionClass.ClassroomID equals classroom.ClassroomID into dclassroom 
                        from classroom in dclassroom.DefaultIfEmpty() 
                        select new EducationMissionImportClassView
                        {
                            TimeID = courseTime.TimesSegment,
                            StartHour = courseTime.StartHour,
                            StartMinutes = courseTime.StartMinutes,
                            EndHour = courseTime.EndHour,
                            EndMinutes = courseTime.EndMinutes,
                            scheduleClassroomName = week.ClassroomName,
                            EducationID = faculty.EducationID,
                            LearningformID = faculty.LearningformID,
                            Assistant = assistant.Name,
                            StandardName = grade.Name,
                            TDate = week.ScheduledTimes,
                            ExaWeek = examweek.WeeklyNum,
                            EndTime = examtime.EndTime ?? s,
                            StarTime = examtime.StartTime ?? s,
                            ExamDate = examtime.ExamDate ?? d,
                            EducationMissionID = missionClass.EducationMissionID,
                            EducationMissionClassID = missionClass.EducationMissionClassID,
                            MainScheduleClassID = missionClass.MainScheduleClassID,
                            TeachingModeID = missionClass.TeachingModeID ?? 1,
                            SchoolyearID = mission.SchoolyearID,
                            SchoolyearCode = schoolyear.Code,
                            Name = classmajor.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 = department.Name,
                            GrademajorID = grade.GrademajorID,
                            StandardID = faculty.StandardID,
                            GradeYearID = grade.GradeID,
                            ClassroomID = missionClass.ClassroomID,
                            ClassroomName = 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 = (schedulingClass.EducationMissionClassID == null ? 0 : schedulingClass.ClassNum),
                            MainScheduleClassGrademajorID = classmajor.GrademajorID,
                            MainScheduleClassCollegeID = classmajor.CF_Grademajor.CF_Facultymajor.CollegeID,
                            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,
                            TeachingDay = week.ScheduledTimes.Count().ToString(),
                            Hours = hours.Hours,
                            TeachingHours = (week.ScheduledTimes.Count() * (hours.Hours == null ? (courseTime.EndTimes - courseTime.StartTimes + 1) : hours.Hours)).ToString(),
                            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 = courseProcess.CourseProcessTimes,
                            CreateTime = missionClass.CreateTime,
                            CreateUserID = missionClass.CreateUserID,
                            Remark = missionClass.Remark
                        };
            return query;
        }
        //导出用
        /// <summary>
        /// 查询教学任务班数据
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<EducationMissionImportClassView> GetEducationMissionClassViewQueryblExcel(Expression<Func<EM_EducationMissionClass, bool>> missionClassExp,
            Expression<Func<EM_ExecutablePlan, bool>> executablePlanExp, Expression<Func<EM_ExecutableOptionalCourse, bool>> executableOptionalCourseExp,
            Expression<Func<EM_ExecutableFreeSelectionCouse, bool>> executableFreeSelectionCourseCourseExp)
        {
            TimeSpan s = new TimeSpan();
            DateTime d = new DateTime();
            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 department in DepartmentRepository.Entities on mission.DepartmentID equals department.DepartmentID
                        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
                                group sc by sc.EducationMissionClassID into g
                                select new
                                {
                                    EducationMissionClassID = g.Key,
                                    ClassNum = g.Sum(x => x.CF_Student.Count())
                                }
                                ) on missionClass.EducationMissionClassID equals schedulingClass.EducationMissionClassID into dschedulingClass
                        from schedulingClass in dschedulingClass.DefaultIfEmpty()
                        join courseProcess 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 courseProcess.EducationMissionClassID into dcourseProcess
                        from courseProcess in dcourseProcess.DefaultIfEmpty()
                        join week in
                            (from es in AdultEducationScheduleRepository.Entities
                             group es by new { es.EM_EducationSchedulingClass.EducationMissionClassID,es.ClassroomID }
                                 into g
                                 select new
                                 {
                                     EducationMissionClassID = g.Key.EducationMissionClassID,
                                     ScheduledTimes = g.Select(x => x.ScheduleDate).ToList(),
                                     ClassroomName = g.Select(x => x.CF_Classroom.Name).FirstOrDefault()

                                 }) on missionClass.EducationMissionClassID equals week.EducationMissionClassID into dweek
                        from week in dweek.DefaultIfEmpty()
                        join assistant in UserRepository.Entities on classmajor.UserID equals assistant.UserID into dassistant
                        from assistant in dassistant.DefaultIfEmpty()
                        join examtime in EducationMissionExamTimeRepository.Entities on missionClass.EducationMissionID equals examtime.EducationMissionID into dexamtime
                        from examtime in dexamtime.DefaultIfEmpty()
                        join examweek in
                            (from ew in EducationMissionExamWeekNumRepository.Entities
                             group ew by ew.EducationMissionID into g
                             select new
                             {
                                 EducationMissionID = g.Key,
                                 WeeklyNum = g.Select(x => x.WeeklyNum).ToList()
                             })
                            on missionClass.EducationMissionID equals examweek.EducationMissionID into dexamweek
                        from examweek in dexamweek.DefaultIfEmpty()
                        from courseTime in missionClass.EM_CoursesTime.DefaultIfEmpty()
                        join hours in CourseTimeHoursRepository.Entities
                        on new { CoursesTimeID = (Guid?)courseTime.CoursesTimeID, GradeYearID = grade.GradeID } equals new { CoursesTimeID = hours.CoursesTimeID, GradeYearID = hours.Years }
                            //on cou.CoursesTimeID equals h.CoursesTimeID
                        into dhours
                        from hours in dhours.DefaultIfEmpty()
                        join classroom in ClassroomRepository.Entities on missionClass.ClassroomID equals classroom.ClassroomID into dclassroom
                        from classroom in dclassroom.DefaultIfEmpty() 
                        select new EducationMissionImportClassView
                        {
                            TimeID = courseTime.TimesSegment,
                            StartHour = courseTime.StartHour,
                            StartMinutes = courseTime.StartMinutes,
                            EndHour = courseTime.EndHour,
                            EndMinutes = courseTime.EndMinutes,
                            scheduleClassroomName = week.ClassroomName,
                            EducationID = faculty.EducationID,
                            LearningformID = faculty.LearningformID,
                            Assistant = assistant.Name,
                            StandardName = grade.Name,
                            TDate = week.ScheduledTimes,
                            ExaWeek = examweek.WeeklyNum,
                            EndTime = examtime.EndTime ?? s,
                            StarTime = examtime.StartTime ?? s,
                            ExamDate = examtime.ExamDate ?? d,
                            EducationMissionID = missionClass.EducationMissionID,
                            EducationMissionClassID = missionClass.EducationMissionClassID,
                            MainScheduleClassID = missionClass.MainScheduleClassID,
                            TeachingModeID = missionClass.TeachingModeID ?? 1,
                            SchoolyearID = mission.SchoolyearID,
                            SchoolyearCode = schoolyear.Code,
                            Name = missionClass.Name,
                            CoursematerialID = missionClass.CoursematerialID,
                            CourseCode = course.CourseCode,
                            CourseName = course.CourseName,
                            CampusID = college.CampusID,
                            CollegeID = mission.CollegeID,
                            CollegeNo = college.No,
                            CollegeName = college.Name,
                            DepartmentID = mission.DepartmentID,
                            DepartmentName = department.Name,
                            GrademajorID = grade.GrademajorID,
                            StandardID = faculty.StandardID,
                            GradeYearID = grade.GradeID,
                            ClassroomID = missionClass.ClassroomID,
                            ClassroomName = 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 = (schedulingClass.EducationMissionClassID == null ? 0 : schedulingClass.ClassNum),
                            MainScheduleClassGrademajorID = classmajor.GrademajorID,
                            MainScheduleClassCollegeID = faculty.CollegeID,
                            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,
                            TeachingDay = week.ScheduledTimes.Count().ToString(),
                            Hours = hours.Hours,
                            TeachingHours = (week.ScheduledTimes.Count() * (hours.Hours == null ? (courseTime.EndTimes - courseTime.StartTimes + 1) : hours.Hours)).ToString(),
                            TheoryWeeklyNum = teachSetting.TheoryWeeklyNum,
                            PracticeWeeklyNum = teachSetting.PracticeWeeklyNum,
                            TrialWeeklyNum = teachSetting.TrialWeeklyNum,
                            WeeklyNum = teachSetting.WeeklyNum,
                            WeeklyHours = teachSetting.WeeklyHours,
                            ApprovalStatus = missionClass.RecordStatus,
                            CourseProcessTimes = courseProcess.CourseProcessTimes,
                            CreateTime = missionClass.CreateTime,
                            CreateUserID = missionClass.CreateUserID,
                            Remark = missionClass.Remark
                        });
            return query;
        }
        //检查教师是否不能排课
        public IQueryable<TeacherIsOnWorkResultView> CheckTeacherIsOnWork(Expression<Func<ES_AdultEducationSchedule, bool>> schedulingExp,
            Expression<Func<ES_AdultEducationScheduleTeacher, bool>> staffExp)
        {
            var query=from es in AdultEducationScheduleRepository.GetList(schedulingExp)
                      join et in AdultEducationScheduleTeacherRepository.GetList(staffExp) on es.AdultEducationScheduleID equals et.AdultEducationScheduleID into ed
                      from eset in ed.DefaultIfEmpty()
                      select new TeacherIsOnWorkResultView{
                          AdultUserId = eset.UserID
                      };
            
            return query;
        }
    }
}