JW/EMiS.DataLogic/EducationSchedule/EducationSchedulingAdjustmentDAL.cs

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

namespace EMIS.DataLogic.EducationSchedule
{
    public class EducationSchedulingAdjustmentDAL
    {
        public CourseProcessRepository CourseProcessRepository { get; set; }
        public EducationSchedulingAdjustmentRepository EducationSchedulingAdjustmentRepository { get; set; }
        public EducationSchedulingAdjustmentTeacherRepository EducationSchedulingAdjustmentTeacherRepository { get; set; }
        public EducationSchedulingAdjustmentToTeacherRepository EducationSchedulingAdjustmentToTeacherRepository { get; set; }
        public EducationSchedulingRepository EducationSchedulingRepository { get; set; }
        public EducationMissionClassRepository EducationMissionClassRepository { get; set; }
        public EducationMissionRepository EducationMissionRepository { get; set; }
        public CoursematerialRepository CoursematerialRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public StaffRepository StaffRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public CoursesTimeRepository CoursesTimeRepository { get; set; }
        public ClassroomRepository ClassroomRepository { get; set; }

        public IQueryable<ScheduleAdjustmentView> GetScheduleAdjustmentViewQueryable(Expression<Func<ES_EducationSchedulingAdjustment, bool>> exp,
            Expression<Func<EM_EducationMission, bool>> missionExp,
            Expression<Func<EM_EducationMissionClass, bool>> missionClassExp,
            Expression<Func<CF_Staff, bool>> staffExp)
        {
            var sql = (from adjustment in EducationSchedulingAdjustmentRepository.GetList(exp)
                       join missionClass in EducationMissionClassRepository.Entities.Where(missionClassExp)
                            on adjustment.EducationMissionClassID equals missionClass.EducationMissionClassID
                       join mission in EducationMissionRepository.Entities.Where(missionExp)
                            on missionClass.EducationMissionID equals mission.EducationMissionID
                       join schoolyear in SchoolyearRepository.Entities on mission.SchoolyearID equals schoolyear.SchoolyearID
                       join course in CoursematerialRepository.Entities on missionClass.CoursematerialID equals course.CoursematerialID
                       join teacher in StaffRepository.Entities.Where(staffExp) on adjustment.UserID equals teacher.UserID
                       join user in UserRepository.Entities on teacher.UserID equals user.UserID
                       join college in CollegeRepository.Entities on teacher.CollegeID equals college.CollegeID
                       join courseTime in CoursesTimeRepository.Entities on adjustment.CoursesTimeID equals courseTime.CoursesTimeID
                       join classroom in ClassroomRepository.Entities on adjustment.ClassroomID equals classroom.ClassroomID
                       join touser in UserRepository.Entities on adjustment.ToUserID equals touser.UserID into dtouser
                       from touser in dtouser.DefaultIfEmpty()
                       join tocourseTime in CoursesTimeRepository.Entities on adjustment.ToCoursesTimeID equals tocourseTime.CoursesTimeID
                       join toclassroom in ClassroomRepository.Entities on adjustment.ToClassroomID equals toclassroom.ClassroomID
                       from applyUser in UserRepository.Entities.Where(x => adjustment.ApplyUserID == x.UserID).DefaultIfEmpty()
                       from createUser in UserRepository.Entities.Where(x => adjustment.CreateUserID == x.UserID).DefaultIfEmpty()
                       select new ScheduleAdjustmentView
                       {
                           EducationSchedulingAdjustmentID = adjustment.EducationSchedulingAdjustmentID,
                           SchoolyearID = mission.SchoolyearID,
                           SchoolyearCode = schoolyear.Code,
                           EducationMissionClassName = missionClass.Name,
                           CourseCode = course.CourseCode,
                           CourseName = course.CourseName,
                           DepartmentID = teacher.DepartmentID,
                           CollegeID = teacher.CollegeID,
                           CollegeNo = college.No,
                           CollegeName = college.Name,
                           UserID = adjustment.UserID,
                           UserName = user.Name,
                           WeekNum = adjustment.WeekNum,
                           Weekday = adjustment.Weekday,
                           CoursesTimeID = adjustment.CoursesTimeID,
                           StartTimes = courseTime.StartTimes,
                           EndTimes = courseTime.EndTimes,
                           ClassroomID = adjustment.ClassroomID,
                           ClassroomName = classroom.Name,
                           ToUserID = adjustment.ToUserID,
                           ToUserName = touser.Name,
                           ToWeekNum = adjustment.ToWeekNum,
                           ToWeekday = adjustment.ToWeekday,
                           ToCoursesTimeID = adjustment.ToCoursesTimeID,
                           ToStartTimes = tocourseTime.StartTimes,
                           ToEndTimes = tocourseTime.EndTimes,
                           ToClassroomID = adjustment.ToClassroomID,
                           ToClassroomName = toclassroom.Name,
                           ApplyUserID = adjustment.ApplyUserID,
                           ApplyUserName = applyUser.Name,
                           ApplyTime = adjustment.ApplyTime,
                           CreateUserID = adjustment.CreateUserID,
                           CreateTime = adjustment.CreateTime,
                           CreateUserName = createUser.Name,
                           RecordStatus = adjustment.RecordStatus
                       });

            return sql;
        }

        public IQueryable<ScheduleAdjustmentEditView> GetScheduleAdjustmentEditViewQueryable(Expression<Func<ES_EducationSchedulingAdjustment, bool>> exp,
            Expression<Func<EM_EducationMission, bool>> missionExp,
            Expression<Func<EM_EducationMissionClass, bool>> missionClassExp,
            Expression<Func<CF_Staff, bool>> staffExp)
        {
            var curDate = DateTime.Today;
            var curSchoolyear = SchoolyearRepository.GetSingle(x => x.IsCurrent == true);
            var firstWeek = curSchoolyear.FirstWeek.AddDays(1 - (int)curSchoolyear.FirstWeek.DayOfWeek);
            var nowWeekNum = (curDate.Subtract(firstWeek).Days + 7) / 7;

            var sql = (from adjustment in EducationSchedulingAdjustmentRepository.GetList(exp)
                       join missionClass in EducationMissionClassRepository.Entities.Where(missionClassExp)
                            on adjustment.EducationMissionClassID equals missionClass.EducationMissionClassID
                       join mission in EducationMissionRepository.Entities.Where(missionExp)
                            on missionClass.EducationMissionID equals mission.EducationMissionID
                       join schoolyear in SchoolyearRepository.Entities on mission.SchoolyearID equals schoolyear.SchoolyearID
                       join course in CoursematerialRepository.Entities on missionClass.CoursematerialID equals course.CoursematerialID
                       join teacher in StaffRepository.Entities.Where(staffExp) on adjustment.UserID equals teacher.UserID
                       join user in UserRepository.Entities on teacher.UserID equals user.UserID
                       join college in CollegeRepository.Entities on teacher.CollegeID equals college.CollegeID
                       join courseTime in CoursesTimeRepository.Entities on adjustment.CoursesTimeID equals courseTime.CoursesTimeID
                       join classroom in ClassroomRepository.Entities on adjustment.ClassroomID equals classroom.ClassroomID
                       join touser in UserRepository.Entities on adjustment.ToUserID equals touser.UserID into dtouser
                       from touser in dtouser.DefaultIfEmpty()
                       join tocourseTime in CoursesTimeRepository.Entities on adjustment.ToCoursesTimeID equals tocourseTime.CoursesTimeID
                       join toclassroom in ClassroomRepository.Entities on adjustment.ToClassroomID equals toclassroom.ClassroomID
                       select new ScheduleAdjustmentEditView
                       {
                           EducationSchedulingAdjustmentID = adjustment.EducationSchedulingAdjustmentID,
                           EducationMissionClassID = missionClass.EducationMissionClassID,
                           SchoolyearID = mission.SchoolyearID,
                           EducationMissionClassName = missionClass.Name,
                           CoursematerialID = missionClass.CoursematerialID,
                           CourseCode = course.CourseCode,
                           CourseName = course.CourseName,
                           EducationMissionClassStartWeekNum = (nowWeekNum > 1) ? nowWeekNum : 1,
                           EducationMissionClassEndWeekNum = schoolyear.WeeksNum,
                           UserID = adjustment.UserID,
                           UserName = user.Name,
                           WeekNum = adjustment.WeekNum,
                           Weekday = adjustment.Weekday,
                           TimesSegment = courseTime.TimesSegment,
                           CoursesTimeID = adjustment.CoursesTimeID,
                           StartHour = courseTime.StartHour,
                           StartMinutes = courseTime.StartMinutes,
                           EndHour = courseTime.EndHour,
                           EndMinutes = courseTime.EndMinutes,
                           ToWeekNum = adjustment.ToWeekNum,
                           ToWeekday = adjustment.ToWeekday,
                           ToCoursesTimeID = adjustment.ToCoursesTimeID,
                           ClassroomTypeID = adjustment.ClassroomTypeID,
                           ToClassroomTypeID = adjustment.ToClassroomTypeID,
                           CollegeName = college.Name,
                           ClassroomID = adjustment.ClassroomID,
                           ClassroomName = classroom.Name,
                           ToClassroomID = adjustment.ToClassroomID,
                           ToUserID = adjustment.ToUserID
                       });

            return sql;
        }

        public IQueryable<ScheduleAdjustmentTeacherView> GetEducationSchedulingAdjustmentTeacherQueryable(Expression<Func<ES_EducationSchedulingAdjustment, bool>> exp,
            Expression<Func<EM_EducationMission, bool>> missionExp,
            Expression<Func<EM_EducationMissionClass, bool>> missionClassExp,
            Expression<Func<CF_Staff, bool>> staffExp)
        {
            var sql = (from adjustment in EducationSchedulingAdjustmentRepository.GetList(exp)
                       join missionClass in EducationMissionClassRepository.Entities.Where(missionClassExp)
                            on adjustment.EducationMissionClassID equals missionClass.EducationMissionClassID
                       join mission in EducationMissionRepository.Entities.Where(missionExp)
                            on missionClass.EducationMissionID equals mission.EducationMissionID
                       join teacher in StaffRepository.Entities.Where(staffExp) on adjustment.UserID equals teacher.UserID
                       join teacherList in EducationSchedulingAdjustmentTeacherRepository.Entities
                            on adjustment.EducationSchedulingAdjustmentID equals teacherList.EducationSchedulingAdjustmentID
                       join user in UserRepository.Entities on teacherList.UserID equals user.UserID
                       select new ScheduleAdjustmentTeacherView
                       {
                           EducationSchedulingAdjustmentID = adjustment.EducationSchedulingAdjustmentID,
                           UserID = user.UserID,
                           LoginID = user.LoginID,
                           Name = user.Name,
                           TeachingMethod = teacherList.TeachingMethod
                       });

            return sql;
        }

        public IQueryable<ScheduleAdjustmentTeacherView> GetEducationSchedulingAdjustmentToTeacherQueryable(Expression<Func<ES_EducationSchedulingAdjustment, bool>> exp,
            Expression<Func<EM_EducationMission, bool>> missionExp,
            Expression<Func<EM_EducationMissionClass, bool>> missionClassExp,
            Expression<Func<CF_Staff, bool>> staffExp)
        {
            var sql = (from adjustment in EducationSchedulingAdjustmentRepository.GetList(exp)
                       join missionClass in EducationMissionClassRepository.Entities.Where(missionClassExp)
                            on adjustment.EducationMissionClassID equals missionClass.EducationMissionClassID
                       join mission in EducationMissionRepository.Entities.Where(missionExp)
                            on missionClass.EducationMissionID equals mission.EducationMissionID
                       join teacher in StaffRepository.Entities.Where(staffExp) on adjustment.UserID equals teacher.UserID
                       join teacherList in EducationSchedulingAdjustmentToTeacherRepository.Entities
                            on adjustment.EducationSchedulingAdjustmentID equals teacherList.EducationSchedulingAdjustmentID
                       join user in UserRepository.Entities on teacherList.UserID equals user.UserID
                       select new ScheduleAdjustmentTeacherView
                       {
                           EducationSchedulingAdjustmentID = adjustment.EducationSchedulingAdjustmentID,
                           UserID = user.UserID,
                           LoginID = user.LoginID,
                           Name = user.Name,
                           TeachingMethod = teacherList.TeachingMethod
                       });

            return sql;
        }

        public IQueryable<ES_EducationScheduling> GetEducationSchedulingQueryableByAdjustment(Expression<Func<ES_EducationSchedulingAdjustment, bool>> exp)
        {
            var sql = (from adjustment in EducationSchedulingAdjustmentRepository.GetList(exp)
                       from scheduling in EducationSchedulingRepository.Entities.Where(x => x.Weekday == adjustment.Weekday
                        && x.CoursesTimeID == adjustment.CoursesTimeID
                        && x.EM_EducationSchedulingClass.EducationMissionClassID == adjustment.EducationMissionClassID
                        && x.ES_EducationSchedulingTeacher.Any(w => w.UserID == adjustment.UserID)
                        && x.ES_EducationSchedulingWeekNum.Any(w => adjustment.WeekNum == w.WeekNum))
                       group scheduling by scheduling.EducationSchedulingID into g
                       select g.FirstOrDefault());

            return sql;
        }

        public IQueryable<ES_EducationScheduling> GetNewEducationSchedulingQueryableByAdjustment(Expression<Func<ES_EducationSchedulingAdjustment, bool>> exp)
        {
            var sql = (from adjustment in EducationSchedulingAdjustmentRepository.GetList(exp)
                       from scheduling in EducationSchedulingRepository.Entities.Where(x => x.Weekday == adjustment.ToWeekday
                        && x.CoursesTimeID == adjustment.ToCoursesTimeID
                        && x.EM_EducationSchedulingClass.EducationMissionClassID == adjustment.EducationMissionClassID
                        && x.ES_EducationSchedulingTeacher.Any(w => w.UserID == adjustment.ToUserID)
                        && x.ES_EducationSchedulingWeekNum.Any(w => adjustment.ToWeekNum == w.WeekNum))
                       group scheduling by scheduling.EducationSchedulingID into g
                       select g.FirstOrDefault());

            return sql;
        }

        public IQueryable<ES_EducationSchedulingWeekNum> GetEducationSchedulingWeekNumQueryableByAdjustment(Expression<Func<ES_EducationSchedulingAdjustment, bool>> exp)
        {
            var sql = (from adjustment in EducationSchedulingAdjustmentRepository.GetList(exp)
                       from scheduling in EducationSchedulingRepository.Entities.Where(x => x.Weekday == adjustment.Weekday
                        && x.CoursesTimeID == adjustment.CoursesTimeID
                        && x.EM_EducationSchedulingClass.EducationMissionClassID == adjustment.EducationMissionClassID
                        && x.ES_EducationSchedulingTeacher.Any(w => w.UserID == adjustment.UserID)
                        && x.ES_EducationSchedulingWeekNum.Any(w => adjustment.WeekNum == w.WeekNum))
                       from weekNum in scheduling.ES_EducationSchedulingWeekNum
                       group weekNum by weekNum.EducationSchedulingWeekNumID into g
                       select g.FirstOrDefault());

            return sql;
        }

        public IQueryable<ES_EducationSchedulingWeekNum> GetNewEducationSchedulingWeekNumQueryableByAdjustment(Expression<Func<ES_EducationSchedulingAdjustment, bool>> exp)
        {
            var sql = (from adjustment in EducationSchedulingAdjustmentRepository.GetList(exp)
                       from scheduling in EducationSchedulingRepository.Entities.Where(x => x.Weekday == adjustment.ToWeekday
                        && x.CoursesTimeID == adjustment.ToCoursesTimeID
                        && x.EM_EducationSchedulingClass.EducationMissionClassID == adjustment.EducationMissionClassID
                        && x.ES_EducationSchedulingTeacher.Any(w => w.UserID == adjustment.ToUserID)
                        && x.ES_EducationSchedulingWeekNum.Any(w => adjustment.ToWeekNum == w.WeekNum))
                       from weekNum in scheduling.ES_EducationSchedulingWeekNum
                       group weekNum by weekNum.EducationSchedulingWeekNumID into g
                       select g.FirstOrDefault());

            return sql;
        }

        public IQueryable<ES_EducationSchedulingTeacher> GetEducationSchedulingTeacherQueryableByAdjustment(Expression<Func<ES_EducationSchedulingAdjustment, bool>> exp)
        {
            var sql = (from adjustment in EducationSchedulingAdjustmentRepository.GetList(exp)
                       from scheduling in EducationSchedulingRepository.Entities.Where(x => x.Weekday == adjustment.Weekday
                        && x.CoursesTimeID == adjustment.CoursesTimeID
                        && x.EM_EducationSchedulingClass.EducationMissionClassID == adjustment.EducationMissionClassID
                        && x.ES_EducationSchedulingTeacher.Any(w => w.UserID == adjustment.UserID)
                        && x.ES_EducationSchedulingWeekNum.Any(w => adjustment.WeekNum == w.WeekNum))
                       from teacher in scheduling.ES_EducationSchedulingTeacher
                       group teacher by teacher.EducationSchedulingTeacherID into g
                       select g.FirstOrDefault());

            return sql;
        }

        public IQueryable<ES_EducationSchedulingTeacher> GetNewEducationSchedulingTeacherQueryableByAdjustment(Expression<Func<ES_EducationSchedulingAdjustment, bool>> exp)
        {
            var sql = (from adjustment in EducationSchedulingAdjustmentRepository.GetList(exp)
                       from scheduling in EducationSchedulingRepository.Entities.Where(x => x.Weekday == adjustment.ToWeekday
                        && x.CoursesTimeID == adjustment.ToCoursesTimeID
                        && x.EM_EducationSchedulingClass.EducationMissionClassID == adjustment.EducationMissionClassID
                        && x.ES_EducationSchedulingTeacher.Any(w => w.UserID == adjustment.ToUserID)
                        && x.ES_EducationSchedulingWeekNum.Any(w => adjustment.ToWeekNum == w.WeekNum))
                       from teacher in scheduling.ES_EducationSchedulingTeacher
                       group teacher by teacher.EducationSchedulingTeacherID into g
                       select g.FirstOrDefault());

            return sql;
        }
    }
}