JW/EMiS.DataLogic/EducationManage/EducationArrange/EducationMissionClassStudentDAL.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.EducationManage.EducationArrange;
using EMIS.DataLogic.Repositories;

namespace EMIS.DataLogic.EducationManage.EducationArrange
{
    public class EducationMissionClassStudentDAL
    {
        public EducationMissionRepository EducationMissionRepository { get; set; }
        public EducationMissionClassRepository EducationMissionClassRepository { get; set; }
        public EducationMissionClassTeachingSettingRepository EducationMissionClassTeachingSettingRepository { get; set; }
        public EducationSchedulingClassRepository EducationSchedulingClassRepository { get; set; }
        public CourseProcessRepository CourseProcessRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public CoursematerialRepository CoursematerialRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public DepartmentRepository DepartmentRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public CampusRepository CampusRepository { get; set; }
        public ClassroomRepository ClassroomRepository { get; set; }
        public UserRepository UserRepository { get; set; }

        /// <summary>
        /// 查询对应的教学任务班学生名单信息
        /// </summary>
        /// <param name="expEducationSchedulingClass"></param>
        /// <param name="expEducationMissionClass"></param>
        /// <param name="expEducationMission"></param>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<EducationMissionClassStudentView> GetEducationMissionClassStudentViewQueryable(Expression<Func<EM_EducationSchedulingClass, bool>> expEducationSchedulingClass,
                Expression<Func<EM_EducationMissionClass, bool>> expEducationMissionClass, Expression<Func<EM_EducationMission, bool>> expEducationMission, Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from esc in EducationSchedulingClassRepository.GetList(expEducationSchedulingClass)
                        join emc in EducationMissionClassRepository.GetList(expEducationMissionClass)
                        on esc.EducationMissionClassID equals emc.EducationMissionClassID
                        from cou in CoursematerialRepository.Entities.Where(x => x.CoursematerialID == emc.CoursematerialID)
                        join em in EducationMissionRepository.GetList(expEducationMission)
                        on emc.EducationMissionID equals em.EducationMissionID
                        from sy in SchoolyearRepository.Entities.Where(x => x.SchoolyearID == em.SchoolyearID)
                        from dep in DepartmentRepository.Entities.Where(x => x.DepartmentID == em.DepartmentID).DefaultIfEmpty()
                        from coucol in CollegeRepository.Entities.Where(x => x.CollegeID == dep.CollegeID).DefaultIfEmpty()
                        from emcst in EducationMissionClassTeachingSettingRepository.Entities.Where(x => x.EducationMissionClassID == emc.EducationMissionClassID).DefaultIfEmpty()
                        from stu in esc.CF_Student.AsQueryable().Where(expStudent)
                        from cl in ClassmajorRepository.Entities.Where(x => x.ClassmajorID == stu.ClassmajorID)
                        from gr in GrademajorRepository.Entities.Where(x => x.GrademajorID == cl.GrademajorID)
                        from fa in FacultymajorRepository.Entities.Where(x => x.FacultymajorID == gr.FacultymajorID)
                        from col in CollegeRepository.Entities.Where(x => x.CollegeID == fa.CollegeID)
                        from cam in CampusRepository.Entities.Where(x => x.CampusID == col.CampusID)
                        from us in UserRepository.Entities.Where(x => x.UserID == stu.UserID)
                        join mscl in ClassmajorRepository.Entities
                        on emc.MainScheduleClassID equals mscl.ClassmajorID into tempmscl
                        from emcmscl in tempmscl.DefaultIfEmpty()
                        join cp in 
                        (
                            from coupr in CourseProcessRepository.Entities
                            group coupr by coupr.EducationMissionClassID into g
                            select new 
                            {
                                EducationMissionClassID = g.Key,
                                ExecHours = g.Sum(x => x.Times ?? 0)
                            }
                        ) on emc.EducationMissionClassID equals cp.EducationMissionClassID into tempcp
                        from emccp in tempcp.DefaultIfEmpty()
                        from emccr in ClassroomRepository.Entities.Where(x => x.ClassroomID == emc.ClassroomID).DefaultIfEmpty()
                        select new EducationMissionClassStudentView
                        {
                            EducationSchedulingClassID = esc.EducationSchedulingClassID,
                            SchedulingClassName = esc.TaskGroupName,
                            UserID = stu.UserID,
                            StudentNo = us.LoginID,
                            Name = us.Name,
                            SexID = stu.SexID,
                            BirthDate = stu.BirthDate,
                            NationID = stu.NationID,
                            PoliticsID = stu.PoliticsID,
                            InSchoolStatusID = stu.InSchoolStatusID,
                            StudentStatus = stu.StudentStatus,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            ClassmajorID = stu.ClassmajorID,
                            ClassmajorNo = cl.No,
                            ClassmajorName = cl.Name,
                            ClassNum = cl.ClassNum,
                            GradeID = gr.GradeID,
                            GrademajorID = cl.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            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,
                            EducationMissionClassID = esc.EducationMissionClassID,
                            EducationMissionID = emc.EducationMissionID,
                            MissionName = em.ClassName,
                            SchoolyearID = em.SchoolyearID,
                            SchoolyearCode = sy.Code,
                            Value = sy.Value,
                            MainScheduleClassID = emc.MainScheduleClassID,
                            MainScheduleClassNo = emcmscl.No,
                            MainScheduleClassName = emcmscl.Name,
                            MissionSourceID = emc.MissionSourceTypeID,
                            MissionTypeID = emc.OptionalCourseTypeID,
                            OrderNo = emc.OrderNo,
                            MissionClassName = emc.Name,
                            CoursematerialID = emc.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            DepartmentID = em.DepartmentID,
                            DepartmentCode = dep.No,
                            DepartmentName = dep.Name,
                            CourseCollegeID = dep.CollegeID,
                            CourseCollegeCode = coucol.No,
                            CourseCollegeName = coucol.Name,
                            CourseStructureID = emc.CourseStructureID,
                            CourseCategoryID = emc.CourseCategoryID,
                            CourseTypeID = emc.CourseTypeID,
                            CourseQualityID = emc.CourseQualityID,
                            Credit = emcst.Credit ?? 0,
                            TheoryCourse = emcst.TheoryCourse ?? 0,
                            Practicehours = emcst.Practicehours ?? 0,
                            Trialhours = emcst.Trialhours ?? 0,
                            Totalhours = (emcst.TheoryCourse ?? 0) + (emcst.Practicehours ?? 0),
                            ExecHours = emccp.ExecHours == null ? 0 : emccp.ExecHours,
                            TheoryWeeklyNum = emcst.TheoryWeeklyNum ?? 0,
                            PracticeWeeklyNum = emcst.PracticeWeeklyNum ?? 0,
                            TrialWeeklyNum = emcst.TrialWeeklyNum ?? 0,
                            SchoolweeksNum = (emcst.TheoryWeeklyNum ?? 0) + (emcst.PracticeWeeklyNum ?? 0),
                            WeeklyHours = emcst.WeeklyHours ?? 0,
                            WeeklyNum = emcst.WeeklyNum ?? 0,
                            StartWeeklyNum = emcst.StartWeeklyNum ?? 0,
                            EndWeeklyNum = emcst.EndWeeklyNum ?? 0,
                            StartEndWeeklyNum = (emcst.StartWeeklyNum ?? 0) + "-" + (emcst.EndWeeklyNum ?? 0),
                            ExecWeeklyNum = emc.EM_EducationMissionClassSettings.Min(x => x.WeeklyNum) 
                                + "-" + emc.EM_EducationMissionClassSettings.Max(x => x.WeeklyNum),
                            ClassroomTypeID = emc.ClassroomTypeID,
                            ClassroomID = emc.ClassroomID,
                            ClassroomCode = emccr.Code,
                            ClassroomName = emccr.Name,
                            IsNeedMaterial = emc.IsNeedMaterial ?? false,
                            TeachinglanguageID = emc.TeachinglanguageID,
                            ExaminationModeID = emc.ExaminationModeID,
                            ResultTypeID = emc.ResultTypeID,
                            HandleModeID = emc.HandleModeID,
                            TeachingModeID = emc.TeachingModeID,
                            ApprovalStatus = emc.RecordStatus,
                            Remark = emc.Remark
                        };

            return query;
        }

    }
}