JW/EMiS.DataLogic/EducationSchedule/ClassroomExcessiveUseDAL.cs

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

namespace EMIS.DataLogic.EducationSchedule
{
    public class ClassroomExcessiveUseDAL
    {
        public ClassroomExcessiveUseRepository ClassroomExcessiveUseRepository { get; set; }
        public ClassroomExcessiveUseSchedulingRepository ClassroomExcessiveUseSchedulingRepository { get; set; }
        public ClassroomExcessiveUseSchedulingWeekNumRepository ClassroomExcessiveUseSchedulingWeekNumRepository { get; set; }
        public ClassroomRepository ClassroomRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public CoursesTimeRepository CoursesTimeRepository { get; set; }
        public ClassroomReserveRepository ClassroomReserveRepository { get; set; }
        public EducationSchedulingRepository EducationSchedulingRepository { get; set; }
        public ExaminationRoomLayoutRepository ExaminationRoomLayoutRepository { get; set; }
        public ExaminationPlanRepository ExaminationPlanRepository { get; set; }

        public IQueryable<ClassroomExcessiveUseView> GetClassroomExcessiveUseViewQueryable(Expression<Func<ES_ClassroomExcessiveUse, bool>> exp,
            Expression<Func<CF_Classroom, bool>> classroomExp)
        {
            var sql = (from classroomUse in ClassroomExcessiveUseRepository.GetList(exp) 
                       join schoolyear in SchoolyearRepository.Entities on classroomUse.SchoolyearID equals schoolyear.SchoolyearID
                       join classroom in ClassroomRepository.Entities.Where(classroomExp) on classroomUse.ClassroomID equals classroom.ClassroomID 
                       join college in CollegeRepository.Entities on classroomUse.CollegeID equals college.CollegeID into dcollege 
                       from college in dcollege.DefaultIfEmpty() 
                       join user in UserRepository.Entities on classroomUse.UserID equals user.UserID into duser 
                       from user in duser.DefaultIfEmpty()
                       join cre in UserRepository.Entities on classroomUse.CreateUserID equals cre.UserID into dcre
                       from cre in dcre.DefaultIfEmpty()
                       select new ClassroomExcessiveUseView 
                       {
                           ClassroomExcessiveUseID = classroomUse.ClassroomExcessiveUseID,
                           ClassroomID = classroom.ClassroomID,
                           ClassroomName = classroom.Name,
                           SchoolyearID = classroomUse.SchoolyearID,
                           SchoolyearCode = schoolyear.Code,
                           CollegeID = college.CollegeID,
                           CollegeName = college.Name,
                           UserID = classroomUse.UserID,
                           UserName = user.Name,
                           Content = classroomUse.Content,
                           CreateUserID = cre.UserID,
                           CreateUserName = cre.Name,
                           CreateTime = classroomUse.CreateTime
                       });

            return sql;
        }

        public IQueryable<ClassroomExcessiveUseSchedulingView> GetClassroomExcessiveUseSchedulingViewQueryable(Expression<Func<ES_ClassroomExcessiveUse, bool>> exp,
            Expression<Func<CF_Classroom, bool>> classroomExp)
        {
            var sql = (from classroomUse in ClassroomExcessiveUseRepository.GetList(exp)
                       join classroom in ClassroomRepository.Entities.Where(classroomExp) on classroomUse.ClassroomID equals classroom.ClassroomID 
                       join scheduling in ClassroomExcessiveUseSchedulingRepository.Entities on classroomUse.ClassroomExcessiveUseID equals scheduling.ClassroomExcessiveUseID
                       join courseTime in CoursesTimeRepository.Entities on scheduling.CoursesTimeID equals courseTime.CoursesTimeID
                       select new ClassroomExcessiveUseSchedulingView
                       {
                           ClassroomExcessiveUseSchedulingID = scheduling.ClassroomExcessiveUseSchedulingID,
                           ClassroomExcessiveUseID = classroomUse.ClassroomExcessiveUseID,
                           CollegeID = classroomUse.CollegeID,
                           Weekday = scheduling.Weekday,
                           CoursesTimeID = scheduling.CoursesTimeID,
                           StartTimes = courseTime.StartTimes,
                           EndTimes = courseTime.EndTimes,
                           StartHour = courseTime.StartHour,
                           StartMinute = courseTime.StartMinutes,
                           EndHour = courseTime.EndHour,
                           EndMinute = courseTime.EndMinutes,
                           WeekNumList = scheduling.ES_ClassroomExcessiveUseSchedulingWeekNum.Select(x => x.WeekNum)
                       });

            return sql;
        }

        public IQueryable<ClassroomExcessiveUseWeekNumView> GetClassroomExcessiveUseWeekNumViewQueryable(Expression<Func<ES_ClassroomExcessiveUse, bool>> exp,
            Expression<Func<CF_Classroom, bool>> classroomExp)
        {
            var sql = (from classroomUse in ClassroomExcessiveUseRepository.GetList(exp) 
                       join schoolyear in SchoolyearRepository.Entities on classroomUse.SchoolyearID equals schoolyear.SchoolyearID 
                       join classroom in ClassroomRepository.Entities.Where(classroomExp) on classroomUse.ClassroomID equals classroom.ClassroomID
                       join scheduling in ClassroomExcessiveUseSchedulingRepository.Entities on classroomUse.ClassroomExcessiveUseID equals scheduling.ClassroomExcessiveUseID 
                       join weekNum in ClassroomExcessiveUseSchedulingWeekNumRepository.Entities on scheduling.ClassroomExcessiveUseSchedulingID equals weekNum.ClassroomExcessiveUseSchedulingID 
                       select new ClassroomExcessiveUseWeekNumView
                       {
                           ClassroomExcessiveUseSchedulingWeekNumID = weekNum.ClassroomExcessiveUseSchedulingWeekNumID,
                           ClassroomExcessiveUseSchedulingID = scheduling.ClassroomExcessiveUseSchedulingID,
                           ClassroomExcessiveUseID = classroomUse.ClassroomExcessiveUseID,
                           ClassroomID = classroomUse.ClassroomID,
                           ClassroomName = classroom.Name,
                           SchoolyearID = classroomUse.SchoolyearID,
                           SchoolyearCode = schoolyear.Code,
                           CollegeID = classroomUse.CollegeID,
                           WeekNum = weekNum.WeekNum,
                           Weekday = scheduling.Weekday,
                           CoursesTimeID = scheduling.CoursesTimeID
                       });

            return sql;
        }

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

            if (classroomExcessiveUseID.HasValue)
            {
                excessiveUseExp = excessiveUseExp.And(x => x.ClassroomExcessiveUseID != classroomExcessiveUseID);
            }

            var sql = (from c in this.ClassroomRepository.GetList(exp)
                       join cr in ClassroomReserveRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                            on new { ClassroomID = (Guid?)c.ClassroomID, IsReserved = (bool?)true } equals new { cr.ClassroomID, cr.IsReserved } into dcr
                       from ecr in dcr.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 { ClassroomID = (Guid?)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(excessiveUseExp)
                               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 = (Guid?)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 ecr.ClassroomReserveID == null
                        && (c.IsConcurrentUse == true || (ees.EducationSchedulingID == null && eceu.ClassroomID == null))
                       group c by c.ClassroomID into g
                       select g.FirstOrDefault()
                );

            return sql;
        }

        public List<Guid?> GetUsedCourseTime(Guid schoolYearID, IList<int?> schedulingWeekList, int weekday, Guid classroomID)
        {
            Expression<Func<ES_EducationScheduling, bool>> exp = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE && x.SchoolyearID == schoolYearID
                && x.ClassroomID == classroomID && x.Weekday == weekday);
            Expression<Func<ES_ClassroomExcessiveUse, bool>> excessiveUseExp = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE && x.SchoolyearID == schoolYearID
                && x.ClassroomID == classroomID);

            var sql = from esa in
                          (from es in EducationSchedulingRepository.GetList(exp)
                           from eswn in es.ES_EducationSchedulingWeekNum
                           where schedulingWeekList.Contains(eswn.WeekNum)
                           select new { es.ClassroomID, es.CoursesTimeID, es.Weekday, es.SchoolyearID }
                          ).Concat(
                          from ceu in ClassroomExcessiveUseRepository.GetList(excessiveUseExp)
                          from ceus in ceu.ES_ClassroomExcessiveUseScheduling
                          where ceus.Weekday == weekday
                          from ceusw in ceus.ES_ClassroomExcessiveUseSchedulingWeekNum
                          where schedulingWeekList.Contains(ceusw.WeekNum)
                          select new { ceu.ClassroomID, ceus.CoursesTimeID, ceus.Weekday, ceu.SchoolyearID }
                          )
                      select new
                      {
                          esa.CoursesTimeID
                      };
            List<Guid?> coursesTimeList = new List<Guid?>();
            if (sql != null)
            {
                coursesTimeList = sql.Select(x => x.CoursesTimeID).ToList();
            }
            return coursesTimeList;
        }


        public IQueryable<ClassroomConflictView> GetExaminationPlanViewByClassroom(Guid? schoolyearID, Guid? classroomID)
        {
            var query = from erl in ExaminationRoomLayoutRepository.GetList(x => x.ClassroomID == classroomID && x.EX_ExaminationPlan.SchoolyearID == schoolyearID && x.EX_ExaminationPlan.RecordStatus == (int)EX_ExaminationPlanStatus.Submitted)
                        select new ClassroomConflictView
                        {
                            ExaminationPlanID = erl.EX_ExaminationPlan.ExaminationPlanID,
                            ClassroomID = erl.ClassroomID.Value,
                            ExaminationDate = erl.EX_ExaminationPlan.ExaminationDate,
                            StartTime = erl.EX_ExaminationPlan.StartTime,
                            EndTime = erl.EX_ExaminationPlan.EndTime,
                        };
            return query;
        }
    }
}