JW/EMiS.DataLogic/RetakeManage/RetakePlanStudentApplyDAL.cs

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

namespace EMIS.DataLogic.RetakeManage
{
    public class RetakePlanStudentApplyDAL
    {
        public RetakeOpenControlRepository RetakeOpenControlRepository { get; set; }
        public RetakeConditionRepository RetakConditionRepository { get; set; }
        public RetakeConditionParameterRepository RetakeConditionParameterRepository { get; set; }
        public RetakePlanRepository RetakePlanRepository { get; set; }
        public RetakePlanStudentRepository RetakePlanStudentRepository { get; set; }
        public RetakeExamsCatagoryRepository RetakeExamsCatagoryRepository { get; set; }
        public RetakePlanSettingRepository RetakePlanSettingRepository { get; set; }
        public RetakePlanTeachingModeTypeRepository RetakePlanTeachingModeTypeRepository { get; set; }
        public RetakePlanTeachingPlaceRepository RetakePlanTeachingPlaceRepository { get; set; }
        public RetakePlanTeachingSettingRepository RetakePlanTeachingSettingRepository { get; set; }
        public RetakePlanSettingSchedulingRepository RetakePlanSettingSchedulingRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public InSchoolSettingRepository InSchoolSettingRepository { get; set; }
        public ParameterRepository ParameterRepository { get; set; }

        public CoursematerialRepository CoursematerialRepository { get; set; }
        public FinallyScoreRepository FinallyScoreRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { 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 DictionaryItemRepository DictionaryItemRepository { get; set; }
        public CoursesTimeRepository coursesTimeRepository { get; set; }
        public ClassroomRepository ClassroomRepository { get; set; }

        public ExecutableFreeSelectionCouseRepository ExecutableFreeSelectionCouseRepository { get; set; }//任选设定
        public ExecutableFreeSelectionCouseTeachingSettingRepository ExecutableFreeSelectionCouseTeachingSettingRepository { get; set; }    //任选设定教学设置表
        public ExecutableFreeSelectionCouseSchedulingRepository ExecutableFreeSelectionCouseSchedulingRepository { get; set; }
        public EducationMissionClassRepository EducationMissionClassRepository { get; set; }
        public EducationSchedulingRepository EducationSchedulingRepository { get; set; }
        public EducationSchedulingClassRepository EducationSchedulingClassRepository { get; set; }
        public EducationSchedulingWeekNumRepository EducationSchedulingWeekNumRepository { get; set; }

        /// <summary>
        /// 查询进入报名信息View（学生平台，需要关联重修控制表-内联、排除已报名结束的信息）
        /// </summary>
        /// <param name="expRetakePlan"></param>
        /// <param name="expRetakePlanStudent"></param>
        /// <returns></returns>
        public IQueryable<RetakePlanStudentApplyView> GetRetakePlanStudentApplyView(Expression<Func<ER_RetakePlan, bool>> expRetakePlan,
                Expression<Func<ER_RetakePlanStudent, bool>> expRetakePlanStudent)
        {
            var query = from rePlanStu in RetakePlanStudentRepository.GetList(expRetakePlanStudent)
                        join  rePlan in RetakePlanRepository.GetList(expRetakePlan)
                        on rePlanStu.RetakePlanID equals rePlan.RetakePlanID
                        join stu in StudentRepository.Entities
                        on rePlanStu.UserID equals stu.UserID
                        join us in UserRepository.Entities
                        on stu.UserID equals us.UserID
                        join cl in ClassmajorRepository.Entities
                        on stu.ClassmajorID equals cl.ClassmajorID
                        join gr in GrademajorRepository.Entities
                        on cl.GrademajorID equals gr.GrademajorID
                        join fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        join col in CollegeRepository.Entities
                        on fa.CollegeID equals col.CollegeID
                        join sy in SchoolyearRepository.Entities
                        on rePlan.SchoolyearID equals sy.SchoolyearID
                        join cou in CoursematerialRepository.Entities
                        on rePlan.CoursematerialID equals cou.CoursematerialID
                        join reSet in RetakePlanSettingRepository.Entities
                        on rePlan.RetakePlanID equals reSet.RetakePlanID
                        into reSetTemp
                        from rePlanSet in reSetTemp.DefaultIfEmpty()
                        join dep in DepartmentRepository.Entities
                        on rePlanSet.DepartmentID equals dep.DepartmentID
                        into reDepTemp
                        from reDep in reDepTemp.DefaultIfEmpty()
                        join reTeaching in RetakePlanTeachingSettingRepository.Entities
                        on rePlan.RetakePlanID equals reTeaching.RetakePlanID
                        into reTeachingTemp
                        from rePlanTeaching in reTeachingTemp.DefaultIfEmpty()
                        join reOpenControl in RetakeOpenControlRepository.Entities
                        on rePlan.SchoolyearID equals reOpenControl.SchoolyearID
                        where reOpenControl.EndDate >= DateTime.Now
                        select new RetakePlanStudentApplyView
                        {
                            RetakePlanID = rePlan.RetakePlanID,
                            RetakePlanStudentID = rePlanStu.RetakePlanStudentID,
                            RetakeTypeID = rePlan.RetakeTypeID,
                            SchoolyearID = rePlan.SchoolyearID,
                            SchoolyearCode = sy.Code,
                            ClassName = rePlan.ClassName,
                            CoursematerialID = rePlan.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            DepartmentID = rePlanSet.DepartmentID,
                            DepartmentCode = reDep.No,
                            DepartmentName = reDep.Name,
                            CourseCollegeID = reDep.CollegeID,
                            CourseCollegeNo = reDep.CF_College.No,
                            CourseCollegeName = reDep.CF_College.Name,
                            CourseTypeID = rePlan.CourseTypeID,
                            Credit = rePlan.Credit ?? 0,
                            PeopleNumlimit = rePlan.PeopleNumlimit ?? 0,
                            PeopleNumlower = rePlan.PeopleNumlower ?? 0,
                            TheoryCourse = rePlanTeaching.TheoryCourse ?? 0,
                            Practicehours = rePlanTeaching.Practicehours ?? 0,
                            Trialhours = rePlanTeaching.Trialhours ?? 0,
                            StartWeeklyNum = rePlanTeaching.StartWeeklyNum,
                            EndWeeklyNum = rePlanTeaching.EndWeeklyNum,
                            SchoolyearNumID = rePlanStu.SchoolyearNumID,
                            StarttermID = rePlanStu.StarttermID,
                            UserID = rePlanStu.UserID,
                            LoginID = us.LoginID,
                            UserName = us.Name,
                            SexID = stu.SexID,
                            InSchoolStatusID = stu.InSchoolStatusID,
                            StudentStatusID = stu.StudentStatus,
                            ClassmajorID = stu.ClassmajorID,
                            ClassmajorCode = cl.No,
                            ClassmajorName = cl.Name,
                            GrademajorID = cl.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            CollegeID = fa.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            Gradeyear = gr.GradeID,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            RecordStatus = rePlan.RecordStatus,
                            ApplyNumber = rePlan.CF_Student.Count,
                            RetakeOpenControlID = reOpenControl.RetakeOpenControlID,
                            StartDate = reOpenControl.StartDate,
                            EndDate = reOpenControl.EndDate,
                            CanSelect = true,
                            ApplyStatus = rePlan.CF_Student.Any(x => x.UserID == rePlanStu.UserID) ? true : false
                        };

            return query;
        }

        /// <summary>
        /// 查询重修课程信息View（学生平台，暂不排除未开放状态的重修计划）
        /// </summary>
        /// <param name="expRetakePlan"></param>
        /// <param name="expRetakePlanStudent"></param>
        /// <returns></returns>
        public IQueryable<RetakePlanStudentApplyView> GetStudentRetakeCourseView(Expression<Func<ER_RetakePlan, bool>> expRetakePlan,
                Expression<Func<ER_RetakePlanStudent, bool>> expRetakePlanStudent)
        {
            var query = from rePlan in RetakePlanRepository.GetList(expRetakePlan)
                        from applyStu in rePlan.CF_Student
                        join rePlanStu in RetakePlanStudentRepository.GetList(expRetakePlanStudent)
                        on
                        new { applyStu.UserID, rePlan.RetakePlanID }
                        equals
                        new { rePlanStu.UserID, rePlanStu.RetakePlanID }
                        join stu in StudentRepository.Entities
                        on rePlanStu.UserID equals stu.UserID
                        join us in UserRepository.Entities
                        on stu.UserID equals us.UserID
                        join cl in ClassmajorRepository.Entities
                        on stu.ClassmajorID equals cl.ClassmajorID
                        join gr in GrademajorRepository.Entities
                        on cl.GrademajorID equals gr.GrademajorID
                        join fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        join col in CollegeRepository.Entities
                        on fa.CollegeID equals col.CollegeID
                        join sy in SchoolyearRepository.Entities
                        on rePlan.SchoolyearID equals sy.SchoolyearID
                        join cou in CoursematerialRepository.Entities
                        on rePlan.CoursematerialID equals cou.CoursematerialID
                        join reSet in RetakePlanSettingRepository.Entities
                        on rePlan.RetakePlanID equals reSet.RetakePlanID
                        into reSetTemp
                        from rePlanSet in reSetTemp.DefaultIfEmpty()
                        join dep in DepartmentRepository.Entities
                        on rePlanSet.DepartmentID equals dep.DepartmentID
                        into reDepTemp
                        from reDep in reDepTemp.DefaultIfEmpty()
                        join reTeaching in RetakePlanTeachingSettingRepository.Entities
                        on rePlan.RetakePlanID equals reTeaching.RetakePlanID
                        into reTeachingTemp
                        from rePlanTeaching in reTeachingTemp.DefaultIfEmpty()
                        select new RetakePlanStudentApplyView
                        {
                            RetakePlanID = rePlan.RetakePlanID,
                            RetakePlanStudentID = rePlanStu.RetakePlanStudentID,
                            RetakeTypeID = rePlan.RetakeTypeID,
                            SchoolyearID = rePlan.SchoolyearID,
                            SchoolyearCode = sy.Code,
                            ClassName = rePlan.ClassName,
                            CoursematerialID = rePlan.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            DepartmentID = rePlanSet.DepartmentID,
                            DepartmentCode = reDep.No,
                            DepartmentName = reDep.Name,
                            CourseCollegeID = reDep.CollegeID,
                            CourseCollegeNo = reDep.CF_College.No,
                            CourseCollegeName = reDep.CF_College.Name,
                            CourseTypeID = rePlan.CourseTypeID,
                            Credit = rePlan.Credit ?? 0,
                            PeopleNumlimit = rePlan.PeopleNumlimit ?? 0,
                            PeopleNumlower = rePlan.PeopleNumlower ?? 0,
                            TheoryCourse = rePlanTeaching.TheoryCourse ?? 0,
                            Practicehours = rePlanTeaching.Practicehours ?? 0,
                            Trialhours = rePlanTeaching.Trialhours ?? 0,
                            StartWeeklyNum = rePlanTeaching.StartWeeklyNum,
                            EndWeeklyNum = rePlanTeaching.EndWeeklyNum,
                            SchoolyearNumID = rePlanStu.SchoolyearNumID,
                            StarttermID = rePlanStu.StarttermID,
                            UserID = rePlanStu.UserID,
                            LoginID = us.LoginID,
                            UserName = us.Name,
                            SexID = stu.SexID,
                            InSchoolStatusID = stu.InSchoolStatusID,
                            StudentStatusID = stu.StudentStatus,
                            ClassmajorID = stu.ClassmajorID,
                            ClassmajorCode = cl.No,
                            ClassmajorName = cl.Name,
                            GrademajorID = cl.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            CollegeID = fa.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            Gradeyear = gr.GradeID,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            RecordStatus = rePlan.RecordStatus,
                            ApplyNumber = rePlan.CF_Student.Count,
                            ApplyStatus = true
                        };

            return query;
        }


        /// <summary>
        /// 检测排课冲突（学生上课冲突）
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="RetakePlanID"></param>
        /// <param name="CoursesTimeID"></param>
        /// <param name="Weekday"></param>
        /// <param name="SchoolyearID"></param>
        /// <param name="StartWeeklyNum"></param>
        /// <param name="EndWeeklyNum"></param>
        /// <returns></returns>
        public IQueryable<RetakePlanSettingSchedulingView> GetRepeatTime(Guid? UserID, Guid? RetakePlanID, Guid? CoursesTimeID, 
                int? Weekday, Guid? SchoolyearID, int? StartWeeklyNum, int? EndWeeklyNum)
        {
            var RetakePlan = from a in RetakePlanSettingSchedulingRepository.GetList(x => 
                                 x.CoursesTimeID == CoursesTimeID && x.Weekday == Weekday)
                             join b in RetakePlanRepository.GetList(x => 
                                 x.SchoolyearID == SchoolyearID
                                 // && x.RecordStatus != (int)EMIS.ViewModel.EM_SelectCourseResultStatus.CancleCreate
                                 //&& x.CF_Student.Any(w => w.UserID == UserID)) 
                             ) on a.RetakePlanID equals b.RetakePlanID
                             from st in b.CF_Student.Where(x => x.UserID == UserID)
                             join c in coursesTimeRepository.Entities 
                             on a.CoursesTimeID equals c.CoursesTimeID
                             join d in DictionaryItemRepository.Entities 
                             on 
                             new { c.TimesSegment, DictionaryCode = "CF_TimesSegment" }
                             equals 
                             new { TimesSegment = d.Value.Value, d.DictionaryCode } 
                             into gbsta
                             from gicbsta in gbsta.DefaultIfEmpty()
                             join s in RetakePlanTeachingSettingRepository.GetList(x =>
                                  x.StartWeeklyNum == StartWeeklyNum || x.StartWeeklyNum == EndWeeklyNum ||
                                    x.EndWeeklyNum == StartWeeklyNum || x.EndWeeklyNum == EndWeeklyNum ||
                                   (x.StartWeeklyNum > StartWeeklyNum && x.StartWeeklyNum < EndWeeklyNum) ||
                                   (x.EndWeeklyNum > StartWeeklyNum && x.EndWeeklyNum < EndWeeklyNum)
                                 // x.StartWeeklyNum == StartWeeklyNum || x.StartWeeklyNum == EndWeeklyNum ||
                                 // x.EndWeeklyNum == StartWeeklyNum || x.EndWeeklyNum == EndWeeklyNum ||
                                 //(x.StartWeeklyNum > StartWeeklyNum && x.StartWeeklyNum < EndWeeklyNum) ||
                                 //(x.EndWeeklyNum > StartWeeklyNum && x.EndWeeklyNum < EndWeeklyNum)
                                 ) on b.RetakePlanID equals s.RetakePlanID
                             select new RetakePlanSettingSchedulingView
                             {
                                 Type = 1,
                                 DefaultClassName = b.ClassName,
                                 SchedulingID = a.RetakePlanSettingSchedulingID,
                                 ID = a.RetakePlanID,
                                 //CourseCollegeID = a.EM_ExecutableFreeSelectionCouse.CF_Department.CollegeID,
                                 ClassroomID = a.ClassroomID,
                                 //ClassroomName = b.ER_RetakePlanSetting.CF_Classroom.Name,
                                 CoursesTimeID = a.CoursesTimeID,
                                 TimesSegment = c.TimesSegment,
                                 TimesSegmentName = gicbsta.Name,
                                 Weekday = a.Weekday,
                             };
            
            var FreeSelection = from a in ExecutableFreeSelectionCouseSchedulingRepository.GetList(x => 
                                    x.CoursesTimeID == CoursesTimeID && x.Weekday == Weekday)
                                join b in ExecutableFreeSelectionCouseRepository.GetList(x =>
                                    x.SchoolyearID == SchoolyearID && x.RecordStatus != (int)EMIS.ViewModel.EM_SelectCourseResultStatus.CancleCreate
                                ) on a.ExecutableFreeSelectionCouseID equals b.ExecutableFreeSelectionCouseID
                                from st in b.CF_Student.Where(x => x.UserID == UserID)
                                join c in coursesTimeRepository.Entities on a.CoursesTimeID equals c.CoursesTimeID
                                join d in DictionaryItemRepository.Entities on new { c.TimesSegment, DictionaryCode = "CF_TimesSegment" }
                                 equals new { TimesSegment = d.Value.Value, d.DictionaryCode } into gbsta
                                from gicbsta in gbsta.DefaultIfEmpty()
                                join s in ExecutableFreeSelectionCouseTeachingSettingRepository.GetList(x =>
                                    x.StartWeeklyNum == StartWeeklyNum || x.StartWeeklyNum == EndWeeklyNum ||
                                    x.EndWeeklyNum == StartWeeklyNum || x.EndWeeklyNum == EndWeeklyNum ||
                                   (x.StartWeeklyNum > StartWeeklyNum && x.StartWeeklyNum < EndWeeklyNum) ||
                                   (x.EndWeeklyNum > StartWeeklyNum && x.EndWeeklyNum < EndWeeklyNum)
                                    ) on b.ExecutableFreeSelectionCouseID equals s.ExecutableFreeSelectionCouseID
                                select new RetakePlanSettingSchedulingView
                                {
                                    Type = 2,
                                    DefaultClassName = b.DefaultClassName,
                                    SchedulingID = a.ExecutableFreeSelectionCouseSchedulingID,
                                    ID = a.ExecutableFreeSelectionCouseID,
                                    //CourseCollegeID = a.EM_ExecutableFreeSelectionCouse.CF_Department.CollegeID,
                                    ClassroomID = a.ClassroomID,
                                    //ClassroomName = a.CF_Classroom.Name,
                                    CoursesTimeID = a.CoursesTimeID,
                                    TimesSegment = c.TimesSegment,
                                    TimesSegmentName = gicbsta.Name,
                                    Weekday = a.Weekday,
                                };
            
            var Education = from schedulingClass in EducationSchedulingClassRepository.GetList(x => 
                                x.CF_Student.Any(w => w.UserID == UserID))
                            join scheduling in EducationSchedulingRepository.GetList(x => 
                                x.CoursesTimeID == CoursesTimeID && x.Weekday == Weekday && x.SchoolyearID == SchoolyearID) 
                            on schedulingClass.EducationSchedulingClassID equals scheduling.EducationSchedulingClassID
                            join schWeekNum in EducationSchedulingWeekNumRepository.GetList(x =>
                             x.WeekNum >= StartWeeklyNum && x.WeekNum <= EndWeeklyNum) 
                            on scheduling.EducationSchedulingID equals schWeekNum.EducationSchedulingID
                            join education in EducationMissionClassRepository.Entities 
                            on schedulingClass.EducationMissionClassID equals education.EducationMissionClassID
                            select new RetakePlanSettingSchedulingView
                            {
                                Type = 3,
                                DefaultClassName = education.Name,
                                SchedulingID = scheduling.EducationSchedulingID,
                                ID = education.EducationMissionClassID,
                                //CourseCollegeID = null,
                                ClassroomID = null,
                                //ClassroomName = "",
                                CoursesTimeID = null,
                                TimesSegment = null,
                                TimesSegmentName = "",
                                Weekday = null,
                            };

            var query = RetakePlan.Concat(FreeSelection).Concat(Education);

            return query;
        }
    }
}