JW/EMiS.DataLogic/SelectCourse/FreeSelectionCoursePlanDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EMIS.DataLogic.Repositories;
using EMIS.ViewModel.SelectCourse;
using System.Linq.Expressions;
using EMIS.Entities;
using EMIS.ViewModel;
using EMIS.ViewModel.EnrollManage.SpecialtyManage;

namespace EMIS.DataLogic.SelectCourse
{
    public class FreeSelectionCoursePlanDAL
    {
        public FreeSelectionCouseApplyRepository FreeSelectionCouseApplyRepository { get; set; }
        public FreeSelectionCouseApplySchedulingRepository FreeSelectionCouseApplySchedulingRepository { get; set; }
        public FreeSelectionCouseApplyTeachingSettingRepository FreeSelectionCouseApplyTeachingSettingRepository { get; set; }
        public ExecutableFreeSelectionCouseRepository FreeSelectionCousePlanRepository { get; set; }
        public SpecialtyRepository SpecialtyRepository { get; set; }
        public Lazy<FreeSelectionCourseDAL> FreeSelectionCourseDAL { get; set; }
        public DepartmentRepository DepartmentRepository { get; set; }
        public DictionaryItemRepository DictionaryItemRepository { get; set; }
        public CoursesTimeRepository coursesTimeRepository { get; set; }
        public ClassroomRepository ClassroomRepository { get; set; }

        /// <summary>
        /// 查询所有计划申请表记录
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<FreeSelectionCourseApplyView> GetFreeSelectionCourseApplyViewQueryable(Expression<Func<EM_FreeSelectionCouseApply, bool>> exp)
        {
            var query = from a in FreeSelectionCouseApplyRepository.GetList(exp)
                        join b in FreeSelectionCouseApplyTeachingSettingRepository.Entities on a.FreeSelectionCouseApplyID equals b.FreeSelectionCouseApplyID
                        join c in FreeSelectionCourseDAL.Value.GetFreeSelectionCourseViewQueryable(x => true) on a.FreeSelectionCouseID equals c.FreeSelectionCouseID
                        //join e in DictionaryItemRepository.Entities on new { a.ApprovalStatus, DictionaryCode = DictionaryItem.EM_FreeSelectionCouseApplyStatu1s.ToString() } equals new { ApprovalStatus = e.Value, e.DictionaryCode }
                        //into gas
                        //from gApprovalStatus in gas.DefaultIfEmpty()
                        join d in DepartmentRepository.Entities on a.DepartmentID equals d.DepartmentID into dd
                        from ed in dd.DefaultIfEmpty()
                        //join se in
                        //    (from a in FreeSelectionCouseApplySchedulingRepository.Entities
                        //     group a by new { a.FreeSelectionCouseApplyID, a.ClassroomID } into g
                        //     select new { FreeSelectionCouseApplyID = g.Key.FreeSelectionCouseApplyID, ClassroomID = g.Key.ClassroomID }
                        //         ) on a.FreeSelectionCouseApplyID equals se.FreeSelectionCouseApplyID
                        //join se in FreeSelectionCouseApplySchedulingRepository.Entities on a.FreeSelectionCouseApplyID equals se.FreeSelectionCouseApplyID
                        //join di in DictionaryItemRepository.GetList(x => x.DictionaryCode == "ES_WeekDay")
                        //on se.Weekday equals di.Value into gs
                        //from di in gs.DefaultIfEmpty()
                        join Scheduling in
                            (from sc in FreeSelectionCouseApplySchedulingRepository.Entities
                             //join b in coursesTimeRepository.Entities on sc.CoursesTimeID equals b.CoursesTimeID
                             //join c in DictionaryItemRepository.Entities on new { b.TimesSegment, DictionaryCode = "CF_TimesSegment" }
                             //equals new { TimesSegment = c.Value.Value, c.DictionaryCode } into gbsta
                             //from gicbsta in gbsta.DefaultIfEmpty()
                             join d in ClassroomRepository.Entities on sc.ClassroomID equals d.ClassroomID
                             group d by new { sc.FreeSelectionCouseApplyID } into g
                             select new
                             {
                                 ExecutableFreeSelectionCouseID = g.Key.FreeSelectionCouseApplyID,
                                 ClassroomName = g.Select(x => x.Name).FirstOrDefault(),
                             }) on a.FreeSelectionCouseApplyID equals Scheduling.ExecutableFreeSelectionCouseID
                             into lj
                        from Scheduling in lj.DefaultIfEmpty()
                        select new FreeSelectionCourseApplyView
                        {
                            FreeSelectionCouseApplyID = a.FreeSelectionCouseApplyID,
                            FreeSelectionCouseID = a.FreeSelectionCouseID,
                            //FreeSelectionCouseNo = a.EM_FreeSelectionCouse.No,
                            DefaultClassName = a.DefaultClassName,
                            SchoolyearID = a.SchoolyearID,
                            SchoolyearCode = a.CF_Schoolyear.Code,
                            CourseCode = c.CourseCode,
                            CourseName = c.CourseName,
                            CourseStructureID = a.CourseStructureID,
                            //CourseStructureName = c.CourseStructureName,
                            CourseCategoryID = a.CourseCategoryID,
                            //CourseCategoryName = c.CourseCategoryName,
                            CourseTypeID = a.CourseTypeID,
                            //CourseTypeName = c.CourseTypeName,
                            CourseQualityID = a.CourseQualityID,
                            //CourseQualityName = c.CourseQualityName,
                            DepartmentID = a.DepartmentID,
                            DepartmentName = ed.Name,
                            CollegeID = ed.CollegeID,
                            CollegeName = ed.CF_College.Name,
                            ExaminationModeID = a.ExaminationModeID,
                            //ExaminationModeName = a.ExaminationModeName,
                            TeachinglanguageID = a.TeachinglanguageID,
                            //TeachinglanguageName = a.TeachinglanguageName,
                            PeopleNumlimit = a.PeopleNumlimit,
                            PeopleNumlower = a.PeopleNumlower,
                            IsOpened = a.IsOpened == true,
                            HandleModeID = a.HandleModeID,
                            SchoolyearNumID = c.SchoolyearNumID,
                            SchoolyearNumName = c.SchoolyearNumName,
                            SchoolcodeID = c.SchoolcodeID,
                            SchoolcodeName = c.SchoolcodeName,
                            //StarttermID = c.StarttermID,
                            //StarttermName = c.StarttermName,
                            IsEnable = c.IsEnable,
                            Credit = c.Credit,
                            TheoryCourse = b.TheoryCourse,
                            Practicehours = b.Practicehours,
                            Trialhours = b.Trialhours,
                            WeeklyHours = b.WeeklyHours,
                            WeeklyNum = b.WeeklyNum,
                            TheoryWeeklyNum = b.TheoryWeeklyNum,
                            PracticeWeeklyNum = b.PracticeWeeklyNum,
                            TrialWeeklyNum = b.TrialWeeklyNum,
                            StartWeeklyNum = b.StartWeeklyNum,
                            EndWeeklyNum = b.EndWeeklyNum,
                            IsNeedMaterial = (bool)a.IsNeedMaterial,
                            IsNeedMaterialName = a.IsNeedMaterial == true ? "是" : "否",
                            ApprovalStatus = a.ApprovalStatus,
                            //CoursesTimeID = se.CoursesTimeID,
                            //CoursesTimeName = se.EM_CoursesTime.StartHour.ToString() + ":" + se.EM_CoursesTime.StartMinutes.ToString() + "-" + se.EM_CoursesTime.EndHour.ToString() + ":" + se.EM_CoursesTime.EndMinutes.ToString(),
                            //ClassroomID = se.ClassroomID,
                            ClassroomID = a.EM_FreeSelectionCouseApplyScheduling.FirstOrDefault() != null ? a.EM_FreeSelectionCouseApplyScheduling.FirstOrDefault().ClassroomID : null,
                            ClassroomName = Scheduling.ClassroomName,
                            //ClassroomName = se.CF_Classroom.Name,
                            //Weekday = se.Weekday,
                            //WeekdayName = di.Name,
                            CreateUserID = a.CreateUserID,
                            CreateTime = a.CreateTime,
                            ResultTypeID=a.ResultTypeID,
                            Remarks = a.Remarks
                        };
            return query;
        }

        public IQueryable<FreeSelectionCourseApplyTeacherListView> GetFreeSelectionCourseApplyTeacherViewQueryable(Expression<Func<EM_FreeSelectionCouseApply, bool>> exp)
        {
            var q = (from fsca in FreeSelectionCouseApplyRepository.GetList(exp)
                     from fscas in fsca.EM_FreeSelectionCouseApplyTeacher
                     select new FreeSelectionCourseApplyTeacherListView
                     {
                         FreeSelectionCourseApplyID = fsca.FreeSelectionCouseApplyID,
                         UserID = fscas.CF_Staff.UserID,
                         LoginID = fscas.CF_Staff.Sys_User.LoginID,
                         Name = fscas.CF_Staff.Sys_User.Name,
                         CollegeID = fscas.CF_Staff.CollegeID,
                         CollegeName = fscas.CF_Staff.CF_College.Name,
                         BirthDate = fscas.CF_Staff.BirthDate,
                         TeacherType = fscas.CF_Staff.TeacherTypeID,
                         IncumbencyState = fscas.CF_Staff.IncumbencyState,
                         Title = fscas.CF_Staff.TitleID,
                         TeachingMethod = fscas.TeachingMethod
                     });

            return q;
        }

        public IQueryable<SpecialtyView> GetFreeSelectionCourseApplySpecialtyViewQueryable(Expression<Func<EM_FreeSelectionCouseApply, bool>> exp)
        {
            var q = (from fsca in FreeSelectionCouseApplyRepository.GetList(exp)
                     from fssp in fsca.EM_FreeSelectionCouseApplySpecialty
                     join spec in SpecialtyRepository.Entities 
                     on fssp.SpecialtyID equals spec.SpecialtyID
                     join b in DictionaryItemRepository.GetList(x => x.DictionaryCode == DictionaryItem.CF_Standard.ToString())
                     on spec.StandardID equals b.Value
                     into gs
                     from gstandard in gs.DefaultIfEmpty()
                     select new SpecialtyView
                     {
                         //FreeSelectionCourseApplyID = fsca.FreeSelectionCouseApplyID,
                         SpecialtyID = spec.SpecialtyID,
                         StandardID = fssp.CF_Specialty.StandardID,
                         StandardCode = gstandard.Code,
                         StandardName = gstandard.Name,
                         LearnSystem = spec.LearnSystem,
                         ScienceclassID = spec.ScienceclassID,
                         LearningformID = spec.LearningformID,
                         EducationID = spec.EducationID,
                         PropertyID = spec.PropertyID,
                         StandardTitle = spec.StandardTitle,
                         StandardLevel = spec.StandardLevel,
                         Remark = spec.Remark,
                     });

            return q;
        }

        /// <summary>
        /// 查询任选设定的具体上课时间，选课结果页面查询
        /// </summary>
        /// <param name="efsExp"></param>
        /// <returns></returns>
        public IQueryable<ExecutableFreeSelectionCouseSchedulingView> GetScheduling(Expression<Func<EM_FreeSelectionCouseApply, bool>> efsExp)
        {
            var query = from a in FreeSelectionCouseApplyRepository.GetList(efsExp)
                        join b in FreeSelectionCouseApplySchedulingRepository.Entities on a.FreeSelectionCouseApplyID equals b.FreeSelectionCouseApplyID
                        //join c in ExecutableFreeSelectionCouseTeachingSettingRepository.Entities on a.ExecutableFreeSelectionCouseID equals c.ExecutableFreeSelectionCouseID
                        join c in coursesTimeRepository.Entities on b.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 d in gbsta.DefaultIfEmpty()
                        //join e in DictionaryItemRepository.Entities on new { b.Weekday, DictionaryCode = "ES_WeekDay" }
                        //equals new { Weekday = e.Value, e.DictionaryCode } into gbsta1
                        //from e in gbsta1.DefaultIfEmpty()
                        select new ExecutableFreeSelectionCouseSchedulingView
                        {
                            SchoolyearID = a.SchoolyearID,
                            DefaultClassName = a.DefaultClassName,
                            SchedulingID = b.FreeSelectionCouseApplySchedulingID,
                            ID = a.FreeSelectionCouseApplyID,
                            CourseCollegeID = a.CF_Department.CollegeID,
                            ClassroomID = b.ClassroomID,
                            ClassroomName = b.CF_Classroom.Name,
                            CoursesTimeID = b.CoursesTimeID,
                            Weekday = b.Weekday,
                            TimesSegmentName = d.Name,
                            StartTimes = c.StartTimes,
                            EndTimes = c.EndTimes
                            //StartWeeklyNum = c.StartWeeklyNum,
                            //EndWeeklyNum = c.EndWeeklyNum
                        };
            return query;
        }

        /// <summary>
        /// 查询所有计划确认表记录
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<FreeSelectionCourseApplyView> GetFreeSelectionCoursePlanViewQueryable(Expression<Func<EM_ExecutableFreeSelectionCouse, bool>> exp)
        {
            var query = from a in FreeSelectionCousePlanRepository.GetList(exp)
                        join c in FreeSelectionCourseDAL.Value.GetFreeSelectionCourseViewQueryable(x => true) on a.FreeSelectionCouseID equals c.FreeSelectionCouseID
                        join d in DepartmentRepository.Entities on c.DepartmentID equals d.DepartmentID into dd
                        from ed in dd.DefaultIfEmpty()
                        select new FreeSelectionCourseApplyView
                        {
                            FreeSelectionCouseApplyID = a.ExecutableFreeSelectionCouseID,
                            FreeSelectionCouseID = a.FreeSelectionCouseID,
                            //FreeSelectionCouseNo = a.EM_FreeSelectionCouse.No,
                            DefaultClassName = a.DefaultClassName,
                            SchoolyearID = a.SchoolyearID,
                            SchoolyearCode = a.CF_Schoolyear.Code,
                            CourseCode = c.CourseCode,
                            CourseName = c.CourseName,
                            CourseStructureID = a.CourseStructureID,
                            //CourseStructureName = a.CourseStructureName,
                            CourseCategoryID = a.CourseCategoryID,
                            //CourseCategoryName = a.CourseCategoryName,
                            CourseTypeID = a.CourseTypeID,
                            //CourseTypeName = c.CourseTypeName,
                            CourseQualityID = a.CourseQualityID,
                            //CourseQualityName = a.CourseQualityName,
                            DepartmentID = a.DepartmentID,
                            //DepartmentName = a.DepartmentName,
                            CollegeID = ed.CollegeID,
                            CollegeName = ed.CF_College.Name,
                            ExaminationModeID = c.ExaminationModeID,
                            //ExaminationModeName = c.ExaminationModeName,
                            TeachinglanguageID = c.TeachinglanguageID,
                            //TeachinglanguageName = c.TeachinglanguageName,
                            SchoolyearNumID = c.SchoolyearNumID,
                            SchoolyearNumName = c.SchoolyearNumName,
                            SchoolcodeID = c.SchoolcodeID,
                            SchoolcodeName = c.SchoolcodeName,
                            StarttermID = c.StarttermID,
                            StarttermName = c.StarttermName,
                            IsEnable = c.IsEnable,
                            Credit = c.Credit,
                            TheoryCourse = c.TheoryCourse,
                            Practicehours = c.Practicehours,
                            Trialhours = c.Trialhours,
                            WeeklyNum = c.WeeklyNum,
                            TheoryWeeklyNum = c.TheoryWeeklyNum,
                            PracticeWeeklyNum = c.PracticeWeeklyNum,
                            TrialWeeklyNum = c.TrialWeeklyNum,
                            StartWeeklyNum = c.StartWeeklyNum,
                            EndWeeklyNum = c.EndWeeklyNum,
                            IsNeedMaterial = (bool)a.IsNeedMaterial,
                            IsNeedMaterialName = a.IsNeedMaterial == true ? "是" : "否",
                            CreateUserID = a.CreateUserID,
                            CreateTime = a.CreateTime,
                            Remarks = a.Remarks
                        };
            return query;
        }

        public IQueryable<FreeSelectionCoursePlanTeacherListView> GetFreeSelectionCoursePlanTeacherViewQueryable(Expression<Func<EM_ExecutableFreeSelectionCouse, bool>> exp)
        {
            var q = (from fscp in FreeSelectionCousePlanRepository.GetList(exp)
                     from fscps in fscp.EM_ExecutableFreeSelectionCouseTeacher
                     select new FreeSelectionCoursePlanTeacherListView
                     {
                         FreeSelectionCoursePlanID = fscp.ExecutableFreeSelectionCouseID,
                         UserID = fscps.CF_Staff.UserID,
                         LoginID = fscps.CF_Staff.Sys_User.LoginID,
                         Name = fscps.CF_Staff.Sys_User.Name,
                         CollegeID = fscps.CF_Staff.CollegeID,
                         CollegeName = fscps.CF_Staff.CF_College.Name,
                         BirthDate = fscps.CF_Staff.BirthDate,
                         TeacherType = fscps.CF_Staff.TeacherTypeID,
                         IncumbencyState = fscps.CF_Staff.IncumbencyState,
                         Title = fscps.CF_Staff.TitleID,
                         TeachingMethod = fscps.TeachingMethod
                     });

            return q;
        }

        public IQueryable<Sys_DictionaryItem> GetTeachingModeType(Expression<Func<EM_FreeSelectionCouseApply, bool>> exp)
        {
            var query = from fsc in FreeSelectionCouseApplyRepository.GetList(exp)
                        from fsctm in fsc.EM_FreeSelectionCouseApplyTeachingMode
                        join ditm in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(CF_TeachingMode).Name)
                            on fsctm.TeachingModeID equals ditm.Value
                        into dditm
                        from editm in dditm.DefaultIfEmpty()
                        select editm;

            return query;
        }

        public IQueryable<Sys_DictionaryItem> GetGradeYear(Expression<Func<EM_FreeSelectionCouseApply, bool>> exp)
        {
            var query = from fsc in FreeSelectionCouseApplyRepository.GetList(exp)
                        from fscagy in fsc.EM_FreeSelectionCouseApplyGradeYear
                        join ditm in DictionaryItemRepository.GetList(x => x.DictionaryCode == typeof(CF_TeachingMode).Name)
                            on fscagy.GradeYear equals ditm.Value
                        into dditm
                        from editm in dditm.DefaultIfEmpty()
                        select editm;

            return query;
        }

        public IQueryable<ExecutableFreeSelectionCouseSchedulingView> GetSchedulingView(Expression<Func<EM_FreeSelectionCouseApplyScheduling, bool>> exp)
        {
            //var query = (from em in educationMissionRepository.GetList(exp)
            //             from emc in em.EM_EducationMissionClass
            //             from cp in emc.EM_CourseProcess
            var query = (from a in FreeSelectionCouseApplySchedulingRepository.GetList(exp)
                         join b in coursesTimeRepository.Entities on a.CoursesTimeID equals b.CoursesTimeID
                         join c in DictionaryItemRepository.Entities on new { b.TimesSegment, DictionaryCode = "CF_TimesSegment" }
                         equals new { TimesSegment = c.Value.Value, c.DictionaryCode } into gbsta
                         from gicbsta in gbsta.DefaultIfEmpty()
                         select new ExecutableFreeSelectionCouseSchedulingView
                         {
                             SchedulingID = a.FreeSelectionCouseApplySchedulingID,
                             ID = a.FreeSelectionCouseApplyID,
                             CourseCollegeID = a.EM_FreeSelectionCouseApply.CF_Department.CollegeID,
                             ClassroomID = a.ClassroomID,
                             ClassroomName = a.CF_Classroom.Name,
                             CoursesTimeID = a.CoursesTimeID,
                             StartTimes = b.StartTimes,
                             EndTimes = b.EndTimes,
                             TimesSegment = b.TimesSegment,
                             TimesSegmentName = gicbsta.Name,
                             Weekday = a.Weekday,
                             RecordStatus = a.RecordStatus,
                             CreateTime = a.CreateTime,
                             CreateUserID = a.CreateUserID,
                             ModifyTime = a.ModifyTime,
                             ModifyUserID = a.ModifyUserID
                         });

            return query;
        }
    }
}