JW/EMiS.DataLogic/EducationSchedule/EducationSchedulingStopDAL.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 EducationSchedulingStopDAL
    {
        public EducationSchedulingStopRepository EducationSchedulingStopRepository { get; set; }
        public EducationSchedulingStopTeacherRepository EducationSchedulingStopTeacherRepository { get; set; }
        public EducationSchedulingRepository EducationSchedulingRepository { get; set; }
        public EducationSchedulingTeacherRepository EducationSchedulingTeacherRepository { get; set; }
        public EducationSchedulingWeekNumRepository EducationSchedulingWeekNumRepository { get; set; }
        public EducationSchedulingClassRepository EducationSchedulingClassRepository { get; set; }
        public EducationSchedulingAdjustmentRepository EducationSchedulingAdjustmentRepository { get; set; }
        public EducationSchedulingAdjustmentToTeacherRepository EducationSchedulingAdjustmentToTeacherRepository { get; set; }
        public EducationMissionClassRepository EducationMissionClassRepository { get; set; }
        public EducationMissionRepository EducationMissionRepository { get; set; }
        public CoursematerialRepository CoursematerialRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public CoursesTimeRepository CoursesTimeRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public ClassroomRepository ClassroomRepository { get; set; }

        public IQueryable<EducationSchedulingStopView> GetEducationSchedulingStopViewQueryable(
            Expression<Func<ES_EducationSchedulingStop, bool>> exp,
            Expression<Func<EM_EducationMission, bool>> missionExp,
            Expression<Func<EM_EducationMissionClass, bool>> missionClassExp)
        {
            var sql = (from stop in EducationSchedulingStopRepository.GetList(exp)
                       join missionClass in EducationMissionClassRepository.Entities.Where(missionClassExp)
                            on stop.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 courseTime in CoursesTimeRepository.Entities on stop.CoursesTimeID equals courseTime.CoursesTimeID
                       join classroom in ClassroomRepository.Entities on stop.ClassroomID equals classroom.ClassroomID
                       join apply in UserRepository.Entities on stop.ApplyUserID equals apply.UserID into app
                       from applyUser in app.DefaultIfEmpty()
                       join cuser in UserRepository.Entities on stop.CreateUserID equals cuser.UserID into cu
                       from CreateUser in cu.DefaultIfEmpty()
                       select new EducationSchedulingStopView
                       {
                           EducationSchedulingStopID = stop.EducationSchedulingStopID,
                           SchoolyearID = mission.SchoolyearID,
                           SchoolyearCode = schoolyear.Code,
                           CourseCode = course.CourseCode,
                           CourseName = course.CourseName,
                           EducationMissionClassID = missionClass.EducationMissionClassID,
                           EducationMissionClassName = missionClass.Name,
                           WeekNum = stop.WeekNum,
                           Weekday = stop.Weekday,
                           CoursesTimeID = stop.CoursesTimeID,
                           StartTimes = courseTime.StartTimes,
                           EndTimes = courseTime.EndTimes,
                           ClassroomID = stop.ClassroomID,
                           ClassroomName = classroom.Name,
                           CreateUserName = CreateUser.Name,
                           ApplyTime = stop.ApplyTime,
                           applyUserName = applyUser.Name,
                           RecordStatus = stop.RecordStatus
                       });

            return sql;
        }

        public IQueryable<EducationSchedulingStopTeacherView> GetEducationSchedulingStopTeacherViewQueryable(
            Expression<Func<ES_EducationSchedulingStop, bool>> exp,
            Expression<Func<EM_EducationMission, bool>> missionExp,
            Expression<Func<EM_EducationMissionClass, bool>> missionClassExp)
        {
            var sql = (from stop in EducationSchedulingStopRepository.GetList(exp)
                       join teacher in EducationSchedulingStopTeacherRepository.Entities
                            on stop.EducationSchedulingStopID equals teacher.EducationSchedulingStopID
                       join missionClass in EducationMissionClassRepository.Entities.Where(missionClassExp)
                            on stop.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 courseTime in CoursesTimeRepository.Entities on stop.CoursesTimeID equals courseTime.CoursesTimeID
                       join classroom in ClassroomRepository.Entities on stop.ClassroomID equals classroom.ClassroomID
                       join user in UserRepository.Entities on teacher.UserID equals user.UserID
                       select new EducationSchedulingStopTeacherView
                       {
                           EducationSchedulingStopID = stop.EducationSchedulingStopID,
                           UserID = user.UserID,
                           LoginID = user.LoginID,
                           Name = user.Name,
                           TeachingMethod = teacher.TeachingMethod
                       });

            return sql;
        }

        public IQueryable<EducationSchedulingWeekNumStopView> GetEducationSchedulingWeekNumStopView(
            Expression<Func<ES_EducationSchedulingWeekNum, bool>> exp, int stopApproveStatus)
        {
            var sql = (
                from weekNum in EducationSchedulingWeekNumRepository.GetList(exp)
                from scheduling in EducationSchedulingRepository.Entities.Where(x => x.EducationSchedulingID == weekNum.EducationSchedulingID)
                from scheduleClass in EducationSchedulingClassRepository.Entities.Where(x => scheduling.EducationSchedulingClassID == x.EducationSchedulingClassID)
                from missionClass in EducationMissionClassRepository.Entities.Where(x => scheduleClass.EducationMissionClassID == x.EducationMissionClassID)
                from stop in
                    EducationSchedulingStopRepository.Entities.Where(x => x.RecordStatus == stopApproveStatus
                        && x.EducationMissionClassID == missionClass.EducationMissionClassID && x.CoursesTimeID == scheduling.CoursesTimeID
                        && x.Weekday == scheduling.Weekday && x.ClassroomID == scheduling.ClassroomID
                        && x.WeekNum == weekNum.WeekNum
                        && x.ES_EducationSchedulingStopTeacher.Count() == (
                        from est in EducationSchedulingTeacherRepository.Entities
                            .Where(w => w.EducationSchedulingID == scheduling.EducationSchedulingID)
                        from esst in EducationSchedulingStopTeacherRepository.Entities
                            .Where(w => w.EducationSchedulingStopID == x.EducationSchedulingStopID
                                && w.UserID == est.UserID && w.TeachingMethod == est.TeachingMethod)
                        select esst).Count()).DefaultIfEmpty()
                select new EducationSchedulingWeekNumStopView
                {
                    EducationSchedulingWeekNumID = weekNum.EducationSchedulingWeekNumID,
                    IsStop = (stop.EducationSchedulingStopID != null)
                }
                );

            return sql;
        }

        public IQueryable<EducationSchedulingAdjustmentStopView> GetEducationSchedulingAdjustmentStopView(
            Expression<Func<ES_EducationSchedulingAdjustment, bool>> exp, int stopApproveStatus)
        {
            var sql = (
                from adjust in EducationSchedulingAdjustmentRepository.GetList(exp)
                from missionClass in EducationMissionClassRepository.Entities.Where(x => x.EducationMissionClassID == adjust.EducationMissionClassID)
                from mission in EducationMissionRepository.Entities.Where(x => x.EducationMissionID == missionClass.EducationMissionID)
                from schedulingClass in EducationSchedulingClassRepository.Entities.Where(x => x.EducationMissionClassID == missionClass.EducationMissionClassID)
                from teacher in EducationSchedulingAdjustmentToTeacherRepository.Entities.Where(x =>
                    x.EducationSchedulingAdjustmentID == adjust.EducationSchedulingAdjustmentID)
                from user in UserRepository.Entities.Where(x => x.UserID == teacher.UserID)
                from scheduling in EducationSchedulingRepository.Entities.Where(x =>
                            x.EducationSchedulingClassID == schedulingClass.EducationSchedulingClassID
                            && x.SchoolyearID == mission.SchoolyearID && x.Weekday == adjust.Weekday && x.CoursesTimeID == adjust.CoursesTimeID)
                from weekNum in EducationSchedulingWeekNumRepository.Entities.Where(x => x.EducationSchedulingID == scheduling.EducationSchedulingID
                        && x.WeekNum == adjust.WeekNum)
                from stop in
                    EducationSchedulingStopRepository.Entities.Where(x => x.RecordStatus == stopApproveStatus
                        && x.EducationMissionClassID == missionClass.EducationMissionClassID && x.CoursesTimeID == scheduling.CoursesTimeID
                        && x.Weekday == scheduling.Weekday && x.ClassroomID == scheduling.ClassroomID
                        && x.WeekNum == weekNum.WeekNum
                        && x.ES_EducationSchedulingStopTeacher.Count() == (
                        from est in EducationSchedulingTeacherRepository.Entities
                            .Where(w => w.EducationSchedulingID == scheduling.EducationSchedulingID)
                        from esst in EducationSchedulingStopTeacherRepository.Entities
                            .Where(w => w.EducationSchedulingStopID == x.EducationSchedulingStopID
                                && w.UserID == est.UserID && w.TeachingMethod == est.TeachingMethod)
                        select esst).Count()).DefaultIfEmpty()
                select new EducationSchedulingAdjustmentStopView
                {
                    EducationSchedulingAdjustmentID = adjust.EducationSchedulingAdjustmentID,
                    IsStop = (stop.EducationSchedulingStopID != null)
                }
                );

            return sql;
        }
    }
}