JW/EMiS.DataLogic/EducationManage/StudentEducationMissionClassDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EMIS.DataLogic.Repositories;
using EMIS.ViewModel.EducationManagement;
using System.Linq.Expressions;
using EMIS.Entities;
using EMIS.DataLogic.CultureplanManage.PlanManagement;
using System.Data.Entity;
using EMIS.ViewModel.EducationManage;
using EMIS.ViewModel;
using EMIS.DataLogic.UniversityManage.AdministrativeOrgan;

namespace EMIS.DataLogic.EducationManage
{
    public class StudentEducationMissionClassDAL 
    {
        public EducationMissionClassRepository EducationMissionClassRepository { get; set; }
        public EducationMissionClassTeachingSettingRepository EducationMissionClassTeachingSettingRepository { get; set; }
        public DepartmentRepository DepartmentRepository { 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 EducationSchedulingRepository EducationSchedulingRepository { get; set; }
        public ClassroomRepository ClassroomRepository { get; set; }
        public InSchoolSettingRepository InSchoolSettingRepository { get; set; }
        public CollegeScheduleStatusRepository CollegeScheduleStatusRepository { 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 Lazy<CollegeDAL> CollegeDAL { 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 inSchoolStatusIDList = InSchoolSettingRepository.GetList(x => x.IsSelected == true).Select(w => w.InSchoolStatusID).ToList();//获取选中的在校设定状态、
            var query = from missionClass in EducationMissionClassRepository.GetList(missionClassExp)
                        join mission in EducationMissionRepository.Entities on missionClass.EducationMissionID equals mission.EducationMissionID 
                        join department in DepartmentRepository.Entities on mission.DepartmentID equals department.DepartmentID 
                        join teachSetting in EducationMissionClassTeachingSettingRepository.Entities on missionClass.EducationMissionClassID equals teachSetting.EducationMissionClassID
                        join classmajor in ClassmajorRepository.Entities on missionClass.MainScheduleClassID equals classmajor.ClassmajorID
                        join grade in GrademajorRepository.Entities on classmajor.GrademajorID equals grade.GrademajorID
                        join faculty in FacultymajorRepository.Entities on grade.FacultymajorID equals faculty.FacultymajorID
                        join college in CollegeRepository.Entities on department.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.Where(p => inSchoolStatusIDList.Contains(p.InSchoolStatusID)).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
                        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()
                        join css in CollegeScheduleStatusRepository.Entities
                            on new { missionClass.EM_EducationMission.CollegeID, missionClass.EM_EducationMission.SchoolyearID }
                            equals new { css.CollegeID, css.SchoolyearID }
                            into dcss
                        from ecss in dcss.DefaultIfEmpty()
                        select new EducationMissionClassView
                        {
                            
                            EducationMissionID = missionClass.EducationMissionID,
                            EducationMissionClassID = missionClass.EducationMissionClassID,
                            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 = department.CollegeID,
                            CollegeNo = college.No,
                            CollegeName = college.Name,
                            DepartmentID = department.DepartmentID,
                            DepartmentName = department.Name,
                            GrademajorID = grade.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),
                            MainScheduleClassID = missionClass.MainScheduleClassID,
                            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,
                            TrialWeeklyNum = teachSetting.TrialWeeklyNum,
                            WeeklyNum = teachSetting.WeeklyNum,
                            WeeklyHours = teachSetting.WeeklyHours,
                            ApprovalStatus = missionClass.RecordStatus,
                            CourseProcessTimes = gccp.CourseProcessTimes,
                            CreateTime = missionClass.CreateTime,
                            CreateUserID = missionClass.CreateUserID,
                            Remark = missionClass.Remark,
                            
                            IsCollegeScheduleSubmitted = (ecss.RecordStatus == (int)ES_CollegeScheduleStatusCode.Submited)

                        };
            return query;
        }
    }
}