JW/EMiS.DataLogic/EducationManage/EducationSchedulingClassDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EMIS.DataLogic.Repositories;
using EMIS.ViewModel.EducationManage;
using System.Linq.Expressions;
using EMIS.Entities;
using EMIS.DataLogic.Common.Students;

namespace EMIS.DataLogic.EducationManage
{
    public class EducationSchedulingClassDAL
    {
        public EducationSchedulingClassRepository EducationSchedulingClassRepository { get; set; } 
        public EducationMissionClassRepository EducationMissionClassRepository { get; set; }
        public EducationMissionRepository EducationMissionRepository { get; set; }
        public EducationMissionClassTeachingSettingRepository EducationMissionClassTeachingSettingRepository { get; set; }
        public CoursematerialRepository CoursematerialRepository { get; set; }
        public DepartmentRepository DepartmentRepository { get; set; }
        public CollegePriorityRepository CollegePriorityRepository { get; set; }
        public ClassroomRepository ClassroomRepository { get; set; }
        public EducationSchedulingRepository EducationSchedulingRepository { get; set; }
        public CoursesTimeRepository CoursesTimeRepository { get; set; }
        public CollegeScheduleStatusRepository CollegeScheduleStatusRepository { get; set; }
 
        //public FreeSelectionCousePlanRepository FreeSelectionCousePlanRepository { get; set; }
        public Lazy<EducationMissionClassDAL> EducationMissionClassDAL { get; set; }

        public IQueryable<EducationSchedulingClassScheduleView> GetEducationSchedulingClassViewQueryable(
            Expression<Func<EM_EducationSchedulingClass, bool>> exp)
        {
            var nowDate = DateTime.Today;

            var sql = (from esc in EducationSchedulingClassRepository.GetList(exp)
                       join emc in EducationMissionClassRepository.Entities on esc.EducationMissionClassID equals emc.EducationMissionClassID
                       join em in EducationMissionRepository.Entities on emc.EducationMissionID equals em.EducationMissionID
                       join emcts in EducationMissionClassTeachingSettingRepository.Entities on emc.EducationMissionClassID equals emcts.EducationMissionClassID
                       join cm in CoursematerialRepository.Entities on emc.CoursematerialID equals cm.CoursematerialID
                       join d in DepartmentRepository.Entities on em.DepartmentID equals d.DepartmentID
                       join c in ClassroomRepository.Entities on emc.ClassroomID equals c.ClassroomID into dc
                       from ec in dc.DefaultIfEmpty()
                       join cp in CollegePriorityRepository.GetList(x => x.StartDate <= nowDate && x.EndDate >= nowDate)
                            on d.CollegeID equals cp.CollegeID into dcp
                       from ecp in dcp.DefaultIfEmpty()
                       join css in CollegeScheduleStatusRepository.Entities on new { em.SchoolyearID, d.CollegeID } equals new { css.SchoolyearID, css.CollegeID } into dcss 
                       from css in dcss.DefaultIfEmpty() 
                       join esh in
                           (
                               from es in EducationSchedulingRepository.Entities
                               join ct in CoursesTimeRepository.Entities on es.CoursesTimeID equals ct.CoursesTimeID
                               from esw in es.ES_EducationSchedulingWeekNum
                               group ct by es.EducationSchedulingClassID into g
                               select new { EducationSchedulingClassID = g.Key, Hours = g.Sum(x => x.EndTimes - x.StartTimes + 1) }
                               ) on esc.EducationSchedulingClassID equals esh.EducationSchedulingClassID into desh
                       from esh in desh.DefaultIfEmpty()
                       select new EducationSchedulingClassScheduleView
                       {
                           StartWeeklyNum = emc.EM_EducationMissionClassSettings.Min(w => w.WeeklyNum).Value,
                           EndWeeklyNum = emc.EM_EducationMissionClassSettings.Max(w => w.WeeklyNum).Value,
                           EducationSchedulingClassID = esc.EducationSchedulingClassID,
                           EducationMissionClassID = esc.EducationMissionClassID,
                           EducationMissionClassName = emc.Name,
                           TeachingModeID = emc.TeachingModeID,
                           EducationMissionClassOrderNo = emc.OrderNo,
                           CoursematerialID = emc.CoursematerialID,
                           DepartmentID = em.DepartmentID,
                           CoursematerialName = cm.CourseName,
                           CourseTypeID = emc.CourseTypeID,
                           WeekTimes = emcts.WeeklyNum,
                           WeeklyHours = emcts.WeeklyHours,
                           CollegeID = em.CollegeID,
                           CourseCollegeID = em.CF_Department.CollegeID,
                           SchoolYearID = em.SchoolyearID,
                           OptionalCourseTypeID = emc.OptionalCourseTypeID,
                           ClassroomID = emc.ClassroomID,
                           ClassroomName = ec.Name,
                           ClassroomTypeID = emc.ClassroomTypeID,
                           TaskGroupName = esc.TaskGroupName,
                           ExecutablePlanCourseTime = (emc.EM_CourseProcess.Sum(x => x.Times) ?? 0),
                           RemainCourseTime = (emc.EM_CourseProcess.Sum(x => x.Times) ?? 0) - esh.Hours,
                           StudentNum = esc.CF_Student.Count(),
                           IsCanEdit = ecp.CollegePriorityID != null,
                           CollegeApproveStatusID = css.RecordStatus
                       }
                       );

            return sql;
        }

        public IQueryable<EducationMissionClassTeacherListView> GetTeacherViewQueryable(
            Expression<Func<EM_EducationSchedulingClass, bool>> exp)
        {
            var q = (from esc in EducationSchedulingClassRepository.GetList(exp)
                     from mct in esc.EM_EducationMissionClass.EM_MissionClassTeacher
                     select new EducationMissionClassTeacherListView
                     {
                         MissionClassTeacherID = mct.MissionClassTeacherID,
                         EducationMissionClassID = esc.EducationMissionClassID,
                         UserID = mct.CF_Staff.UserID,
                         LoginID = mct.CF_Staff.Sys_User.LoginID,
                         Name = mct.CF_Staff.Sys_User.Name,
                         CollegeID = mct.CF_Staff.CollegeID,
                         CollegeName = mct.CF_Staff.CF_College.Name,
                         BirthDate = mct.CF_Staff.BirthDate,
                         TeacherType = mct.CF_Staff.TeacherTypeID,
                         IncumbencyState = mct.CF_Staff.IncumbencyState,
                         Title = mct.CF_Staff.TitleID,
                         TeachingMethod = mct.TeachType
                     }).Distinct();

            return q;
        }

        public IQueryable<EM_EducationMissionClassSettings> GetEducationMissionClassSettingQueryable(
            Expression<Func<EM_EducationSchedulingClass, bool>> exp)
        {
            var q = (from esc in EducationSchedulingClassRepository.GetList(exp)
                     from emcs in esc.EM_EducationMissionClass.EM_EducationMissionClassSettings
                     select emcs);

            return q;
        }



        public IQueryable<EducationMissionClassContainClassView> GetClassmajorQueryable(
            Expression<Func<EM_EducationSchedulingClass, bool>> exp)
        {
            var q = (from esc in EducationSchedulingClassRepository.GetList(exp)
                     from cm in esc.EM_EducationMissionClass.CF_Classmajor
                     select new EducationMissionClassContainClassView
                    {
                        EducationMissionClassID = esc.EM_EducationMissionClass.EducationMissionClassID,
                        ClassMajor = cm
                    });

            return q;
        }

        public IQueryable<CF_Student> GetStudentQueryable(
            Expression<Func<EM_EducationSchedulingClass, bool>> exp)
        {
            var q = (from esc in EducationSchedulingClassRepository.GetList(exp)
                     from s in esc.CF_Student
                     select s);

            return q;
        }

        public IQueryable<CourseProcessView> GetCourseProcessViewQueryable(Expression<Func<EM_EducationSchedulingClass, bool>> exp)
        {
            var q = (from esc in EducationSchedulingClassRepository.GetList(exp)
                     join cp in EducationMissionClassDAL.Value.GetCourseProcessView(x => true)
                        on esc.EducationMissionClassID equals cp.EducationMissionClassID
                     select cp).Distinct();

            return q;
        }

        public IQueryable<CourseProcessTeacherView> GetCourseProcessTeacherViewQueryable(Expression<Func<EM_EducationSchedulingClass, bool>> exp)
        {
            var q = (from esc in EducationSchedulingClassRepository.GetList(exp)
                     join cp in EducationMissionClassDAL.Value.GetCourseProcessView(x => true)
                        on esc.EducationMissionClassID equals cp.EducationMissionClassID
                     join cpt in EducationMissionClassDAL.Value.GetCourseProcessTeacherView(x => true)
                        on cp.CourseProcessID equals cpt.CourseProcessID
                     select cpt).Distinct();

            return q;
        }

        public IQueryable<EducationSchedulingClassStudentView> GetEducationSchedulingClassStudentViewQueryable(Expression<Func<EM_EducationSchedulingClass, bool>> exp)
        { 
            var q = (from schedulingClass in EducationSchedulingClassRepository.GetList(exp)
                     from student in schedulingClass.CF_Student 
                     select new EducationSchedulingClassStudentView
                     {
                         EducationSchedulingClassID = schedulingClass.EducationSchedulingClassID,
                         UserID = student.UserID,
                         Name = student.Sys_User.Name
                     });

            return q;
        }
    }
}