JW/EMiS.DataLogic/EducationSchedule/ClassroomScheduleDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Bowin.Common.Linq;
using EMIS.DataLogic.Repositories;
using EMIS.ViewModel;
using EMIS.Entities;
using System.Linq.Expressions;
using EMIS.ViewModel.EducationSchedule;
using EMIS.Utility;
using EMIS.ViewModel.UniversityManage.ClassroomManage;
using EMIS.DataLogic.UniversityManage.ClassroomManage;

namespace EMIS.DataLogic.EducationSchedule
{
    public class ClassroomScheduleDAL
    {
        public Lazy<ClassroomDAL> ClassroomDAL { get; set; }
        public ClassroomRepository ClassroomRepository { get; set; }
        public CoursesTimeRepository CoursesTimeRepository { get; set; }
        public ClassroomReserveRepository ClassroomReserveRepository { get; set; }
        public ClassroomScheduleSettingRepository ClassroomScheduleSettingRepository { get; set; }
        public ClassroomExcessiveUseRepository ClassroomExcessiveUseRepository { get; set; }
        public ClassroomExcessiveUseSchedulingRepository ClassroomExcessiveUseSchedulingRepository { get; set; }
        public ClassroomExcessiveUseSchedulingWeekNumRepository ClassroomExcessiveUseSchedulingWeekNumRepository { get; set; }
        public CollegeClassroomRepository CollegeClassroomRepository { get; set; }
        public EducationSchedulingRepository EducationSchedulingRepository { get; set; }

        public IQueryable<ClassroomView> GetAvailableClassroom()
        {
            var sql = (from c in ClassroomDAL.Value.GetClassroomViewQueryable(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                       join cr in ClassroomReserveRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE
                            && x.IsReserved == true)
                            on c.ClassroomID equals cr.ClassroomID into dcr
                       from ecr in dcr.DefaultIfEmpty()
                       where ecr.ClassroomReserveID == null
                       select c);

            return sql;
        }

        public IQueryable<ClassroomView> GetAvailableClassroom(Guid collegeID, Guid schoolYearID, IList<int?> schedulingWeekList,
            int weekday, Guid courseTimeID, int? studentNum, int? classroomTypeID = null)
        {
            Expression<Func<CF_Classroom, bool>> exp = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE && x.IsAvailable == true);

            if (classroomTypeID.HasValue)
            {
                exp = exp.And(x => x.CF_ClassroomType.Any(w => w.ClassroomType == classroomTypeID));
            }
            var sql = (from c in ClassroomDAL.Value.GetClassroomViewQueryable(exp)
                       join cc in CollegeClassroomRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE) on c.ClassroomID equals cc.ClassroomID
                       join cr in ClassroomReserveRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                            on new { c.ClassroomID, IsReserved = (bool?)true } equals new { cr.ClassroomID, cr.IsReserved } into dcr
                       from ecr in dcr.DefaultIfEmpty()
                       join css in ClassroomScheduleSettingRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE && x.IsOnWork == false)
                            on new { c.ClassroomID, Weekday = (int?)weekday, CoursesTimeID = (Guid?)courseTimeID }
                            equals new { css.ClassroomID, css.Weekday, css.CoursesTimeID } into dcss
                       from ecss in dcss.DefaultIfEmpty()
                       join es in
                           (
                               from es in EducationSchedulingRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                               from eswn in es.ES_EducationSchedulingWeekNum
                               where schedulingWeekList.Contains(eswn.WeekNum)
                               select es
                               ) on new { c.ClassroomID, Weekday = (int?)weekday, CoursesTimeID = (Guid?)courseTimeID, SchoolyearID = (Guid?)schoolYearID }
                       equals new { es.ClassroomID, es.Weekday, es.CoursesTimeID, es.SchoolyearID } into des
                       from ees in des.DefaultIfEmpty()
                       join ceu in
                           (
                               from ceu in ClassroomExcessiveUseRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                               from ceus in ceu.ES_ClassroomExcessiveUseScheduling
                               from ceusw in ceus.ES_ClassroomExcessiveUseSchedulingWeekNum
                               where schedulingWeekList.Contains(ceusw.WeekNum)
                               select new { ceu.ClassroomID, ceus.Weekday, ceus.CoursesTimeID, ceu.SchoolyearID }
                               ) on new { c.ClassroomID, Weekday = (int?)weekday, CoursesTimeID = (Guid?)courseTimeID, SchoolyearID = (Guid?)schoolYearID }
                            equals new { ceu.ClassroomID, ceu.Weekday, ceu.CoursesTimeID, ceu.SchoolyearID } into dceu
                       from eceu in dceu.DefaultIfEmpty()
                       where cc.CollegeID == collegeID
                        && ((c.Totalseating ?? 0) == 0 || studentNum == null || c.Totalseating >= studentNum)
                        && ecr.ClassroomReserveID == null
                        && (ecss.ClassroomScheduleSettingID == null)
                        && (c.IsConcurrentUse == true || (ees.EducationSchedulingID == null && eceu.ClassroomID == null))
                       group c by c.ClassroomID into g
                       select g.FirstOrDefault()
                );

            return sql;
        }

        public IQueryable<ClassroomView> GetAvailableClassroom(Guid schoolYearID, IList<int?> schedulingWeekList,
            int weekday, Guid courseTimeID, int? studentNum, int? classroomTypeID = null)
        {
            Expression<Func<CF_Classroom, bool>> exp = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE && x.IsAvailable == true);

            if (classroomTypeID.HasValue)
            {
                exp = exp.And(x => x.CF_ClassroomType.Any(w => w.ClassroomType == classroomTypeID));
            }
            var sql = (from c in ClassroomDAL.Value.GetClassroomViewQueryable(exp)
                       join cr in ClassroomReserveRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                            on new { c.ClassroomID, IsReserved = (bool?)true } equals new { cr.ClassroomID, cr.IsReserved } into dcr
                       from ecr in dcr.DefaultIfEmpty()
                       join css in ClassroomScheduleSettingRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE && x.IsOnWork == false)
                            on new { c.ClassroomID, Weekday = (int?)weekday, CoursesTimeID = (Guid?)courseTimeID }
                            equals new { css.ClassroomID, css.Weekday, css.CoursesTimeID } into dcss
                       from ecss in dcss.DefaultIfEmpty()
                       join es in
                           (
                               from es in EducationSchedulingRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                               from eswn in es.ES_EducationSchedulingWeekNum
                               where schedulingWeekList.Contains(eswn.WeekNum)
                               select es
                               ) on new { c.ClassroomID, Weekday = (int?)weekday, CoursesTimeID = (Guid?)courseTimeID, SchoolyearID = (Guid?)schoolYearID }
                       equals new { es.ClassroomID, es.Weekday, es.CoursesTimeID, es.SchoolyearID } into des
                       from ees in des.DefaultIfEmpty()
                       join ceu in
                           (
                               from ceu in ClassroomExcessiveUseRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                               from ceus in ceu.ES_ClassroomExcessiveUseScheduling
                               from ceusw in ceus.ES_ClassroomExcessiveUseSchedulingWeekNum
                               where schedulingWeekList.Contains(ceusw.WeekNum)
                               select new { ceu.ClassroomID, ceus.Weekday, ceus.CoursesTimeID, ceu.SchoolyearID }
                               ) on new { c.ClassroomID, Weekday = (int?)weekday, CoursesTimeID = (Guid?)courseTimeID, SchoolyearID = (Guid?)schoolYearID }
                            equals new { ceu.ClassroomID, ceu.Weekday, ceu.CoursesTimeID, ceu.SchoolyearID } into dceu
                       from eceu in dceu.DefaultIfEmpty()
                       where ((c.Totalseating ?? 0) == 0 || studentNum == null || c.Totalseating >= studentNum)
                        && ecr.ClassroomReserveID == null
                        && ecss.ClassroomScheduleSettingID == null
                        && (c.IsConcurrentUse == true || (ees.EducationSchedulingID == null && eceu.ClassroomID == null))
                       group c by c.ClassroomID into g
                       select g.FirstOrDefault()
                );

            return sql;
        }

        public IQueryable<ClassroomView> GetAvailableClassroom(Guid collegeID, int? studentNum, Expression<Func<CF_Classroom, bool>> exp, int? classroomTypeID = null)
        {
            exp = exp.And(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE && x.IsAvailable == true);

            if (classroomTypeID.HasValue)
            {
                exp = exp.And(x => x.CF_ClassroomType.Any(w => w.ClassroomType == classroomTypeID));
            }
            var classroomQuery = ClassroomDAL.Value.GetClassroomViewQueryable(exp);
            if (!Const.LOCAL_SETTING_ISCLOSESCHEDULINGMANAGE)
            {
                classroomQuery = (from c in classroomQuery
                                  join cc in CollegeClassroomRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE) on c.ClassroomID equals cc.ClassroomID
                                  where cc.CollegeID == collegeID
                                  select c);
            }
            var sql = (from c in classroomQuery
                       join cr in ClassroomReserveRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                            on new { c.ClassroomID, IsReserved = (bool?)true } equals new { cr.ClassroomID, cr.IsReserved } into dcr
                       from ecr in dcr.DefaultIfEmpty()
                       where ((c.Totalseating ?? 0) == 0 || studentNum == null || c.Totalseating >= studentNum)
                        && ecr.ClassroomReserveID == null
                       group c by c.ClassroomID into g
                       select g.FirstOrDefault()
                );

            return sql;
        }

        public IQueryable<ClassroomView> GetAvailableClassroomForAdjustment(Guid schoolYearID, IList<int?> schedulingWeekList,
            int weekday, Guid courseTimeID, Guid curEducationSchedulingID, int? studentNum, int? classroomTypeID = null)
        {
            Expression<Func<CF_Classroom, bool>> exp = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE && x.IsAvailable == true);

            if (classroomTypeID.HasValue)
            {
                exp = exp.And(x => x.CF_ClassroomType.Any(w => w.ClassroomType == classroomTypeID));
            }
            var sql = (from c in ClassroomDAL.Value.GetClassroomViewQueryable(exp)
                       join cr in ClassroomReserveRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                            on new { c.ClassroomID, IsReserved = (bool?)true } equals new { cr.ClassroomID, cr.IsReserved } into dcr
                       from ecr in dcr.DefaultIfEmpty()
                       join css in ClassroomScheduleSettingRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE && x.IsOnWork == false)
                            on new { c.ClassroomID, Weekday = (int?)weekday, CoursesTimeID = (Guid?)courseTimeID }
                            equals new { css.ClassroomID, css.Weekday, css.CoursesTimeID } into dcss
                       from ecss in dcss.DefaultIfEmpty()
                       join es in
                           (
                               from es in EducationSchedulingRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                               from eswn in es.ES_EducationSchedulingWeekNum
                               where schedulingWeekList.Contains(eswn.WeekNum)
                                && es.EducationSchedulingID != curEducationSchedulingID
                               select es
                               ) on new { c.ClassroomID, Weekday = (int?)weekday, CoursesTimeID = (Guid?)courseTimeID, SchoolyearID = (Guid?)schoolYearID }
                       equals new { es.ClassroomID, es.Weekday, es.CoursesTimeID, es.SchoolyearID } into des
                       from ees in des.DefaultIfEmpty()
                       join ceu in
                           (
                               from ceu in ClassroomExcessiveUseRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                               from ceus in ceu.ES_ClassroomExcessiveUseScheduling
                               from ceusw in ceus.ES_ClassroomExcessiveUseSchedulingWeekNum
                               where schedulingWeekList.Contains(ceusw.WeekNum)
                               select new { ceu.ClassroomID, ceus.Weekday, ceus.CoursesTimeID, ceu.SchoolyearID }
                               ) on new { c.ClassroomID, Weekday = (int?)weekday, CoursesTimeID = (Guid?)courseTimeID, SchoolyearID = (Guid?)schoolYearID }
                            equals new { ceu.ClassroomID, ceu.Weekday, ceu.CoursesTimeID, ceu.SchoolyearID } into dceu
                       from eceu in dceu.DefaultIfEmpty()
                       where ((c.Totalseating ?? 0) == 0 || studentNum == null || c.Totalseating >= studentNum)
                        && ecr.ClassroomReserveID == null
                        && (ecss.ClassroomScheduleSettingID == null)
                        && (c.IsConcurrentUse == true || (ees.EducationSchedulingID == null && eceu.ClassroomID == null))
                       group c by c.ClassroomID into g
                       select g.FirstOrDefault()
                );
            return sql;
        }

        public IQueryable<ClassroomView> GetAvailableClassroomForAdjustmentNew(Guid schoolYearID, IList<int?> schedulingWeekList,
            int weekday, Guid courseTimeID, Guid curEducationSchedulingID, int? studentNum, int? classroomTypeID = null)
        {
            Expression<Func<CF_Classroom, bool>> exp = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE && x.IsAvailable == true);

            if (classroomTypeID.HasValue)
            {
                exp = exp.And(x => x.CF_ClassroomType.Any(w => w.ClassroomType == classroomTypeID));
            }
            var sql = from a in
                          (from clar in ClassroomRepository.GetList(exp)
                           join cr in ClassroomReserveRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                                on new { ClassroomID = clar.ClassroomID, IsReserved = (bool?)true } equals new { ClassroomID = cr.ClassroomID.Value, cr.IsReserved } into dcr
                           from ecr in dcr.DefaultIfEmpty()
                           join css in ClassroomScheduleSettingRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE && x.IsOnWork == false)
                                on new { ClassroomID = clar.ClassroomID, Weekday = (int?)weekday, CoursesTimeID = (Guid?)courseTimeID }
                                equals new { ClassroomID = css.ClassroomID.Value, css.Weekday, css.CoursesTimeID } into dcss
                           from ecss in dcss.DefaultIfEmpty()
                           join es in
                               (
                                   from es in EducationSchedulingRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                                   from eswn in es.ES_EducationSchedulingWeekNum
                                   where schedulingWeekList.Contains(eswn.WeekNum)
                                    && es.EducationSchedulingID != curEducationSchedulingID
                                   select es
                                   ) on new { ClassroomID = clar.ClassroomID, Weekday = (int?)weekday, CoursesTimeID = (Guid?)courseTimeID, SchoolyearID = (Guid?)schoolYearID }
                           equals new { ClassroomID = es.ClassroomID.Value, es.Weekday, es.CoursesTimeID, es.SchoolyearID } into des
                           from ees in des.DefaultIfEmpty()
                           join ceu in
                               (
                                   from ceu in ClassroomExcessiveUseRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                                   from ceus in ceu.ES_ClassroomExcessiveUseScheduling
                                   from ceusw in ceus.ES_ClassroomExcessiveUseSchedulingWeekNum
                                   where schedulingWeekList.Contains(ceusw.WeekNum)
                                   select new { ceu.ClassroomID, ceus.Weekday, ceus.CoursesTimeID, ceu.SchoolyearID }
                                   ) on new { ClassroomID = clar.ClassroomID, Weekday = (int?)weekday, CoursesTimeID = (Guid?)courseTimeID, SchoolyearID = (Guid?)schoolYearID }
                                equals new { ClassroomID = ceu.ClassroomID.Value, ceu.Weekday, ceu.CoursesTimeID, ceu.SchoolyearID } into dceu
                           from eceu in dceu.DefaultIfEmpty()
                           where ((clar.Totalseating ?? 0) == 0 || studentNum == null || clar.Totalseating >= studentNum)
                            && ecr.ClassroomReserveID == null
                            && (ecss.ClassroomScheduleSettingID == null)
                            && (clar.IsConcurrentUse == true || (ees.EducationSchedulingID == null && eceu.ClassroomID == null))
                           group clar by clar.ClassroomID into g
                           select g.FirstOrDefault())
                      join clag in ClassroomRepository.Entities
                      on a.ClassroomID equals clag.ClassroomID
                      join re in ClassroomReserveRepository.Entities
                      on a.ClassroomID equals re.ClassroomID into tempre
                      from crre in tempre.DefaultIfEmpty()
                      join cc in
                          (
                              from cc in CollegeClassroomRepository.Entities
                              group cc by cc.ClassroomID into gcr
                              select new
                              {
                                  ClassroomID = gcr.Key,
                                  ScheduleCollegeCount = gcr.Count()
                              }
                          )
                      on a.ClassroomID equals cc.ClassroomID into tempcc
                      from colcr in tempcc.DefaultIfEmpty()
                      select new ClassroomView
                      {
                          ClassroomID = clag.ClassroomID,
                          Code = clag.Code,
                          Name = clag.Name,
                          BuildingsInfoID = clag.BuildingsInfoID,
                          BuildingsInfoCode = clag.CF_BuildingsInfo.Code,
                          BuildingsInfoName = clag.CF_BuildingsInfo.Name,
                          CampusID = clag.CF_BuildingsInfo.CampusID,
                          CampusCode = clag.CF_BuildingsInfo.CF_Campus.No,
                          CampusName = clag.CF_BuildingsInfo.CF_Campus.Name,
                          CollegeID = clag.CollegeID,
                          CollegeCode = clag.CF_College.No,
                          CollegeName = clag.CF_College.Name,
                          UnitCategoryID = clag.CF_College.CF_CollegeProfile.UnitCategoryID,
                          CollegeCampusID = clag.CF_College.CampusID,
                          CollegeCampusCode = clag.CF_College.CF_Campus.No,
                          CollegeCampusName = clag.CF_College.CF_Campus.Name,
                          //CF_ClassroomType = clag.CF_ClassroomType,
                          //ClassroomTypeIDList = clag.CF_ClassroomType.Select(x => x.ClassroomType).ToList(),
                          LayoutTypeID = clag.LayoutTypeID,
                          RoomUseID = clag.RoomUseID,
                          FloorLevel = clag.FloorLevel,
                          Acreage = clag.Acreage,
                          RowCout = clag.RowCout,
                          ColumnCount = clag.ColumnCount,
                          Totalseating = clag.Totalseating,
                          Effectiveseating = clag.Effectiveseating,
                          Examinationseating = clag.Examinationseating,
                          IsWrittenExam = clag.IsWrittenExam ?? false,
                          IsMachinetest = clag.IsMachinetest ?? false,
                          IsConcurrentUse = clag.IsConcurrentUse ?? false,
                          IsReserve = crre.IsReserved == true ? true : false,
                          IsAvailable = clag.IsAvailable ?? false,
                          ScheduleCollegeCount = colcr.ScheduleCollegeCount == null ? 0 : colcr.ScheduleCollegeCount,
                          Remark = clag.Remark,
                          RecordStatus = clag.RecordStatus,
                          CreateUserID = clag.CreateUserID,
                          CreateTime = clag.CreateTime,
                          ModifyUserID = clag.ModifyUserID,
                          ModifyTime = clag.ModifyTime
                      };
            return sql;
        }
    }
}