JW/EMiS.DataLogic/PaymentManage/WorktimeAdjustmentDAL.cs

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

namespace EMIS.DataLogic.PaymentManage
{
    public class WorktimeAdjustmentDAL
    {
        public CoursesTimeRepository CoursesTimeRepository { get; set; }
        public WorktimeAdjustmentRepository WorktimeAdjustmentRepository { get; set; }
        public EducationMissionClassRepository EducationMissionClassRepository { get; set; }
        public EducationSchedulingTeacherRepository EducationSchedulingTeacherRepository { get; set; }
        public EducationMissionRepository EducationMissionRepository { get; set; }
        public EducationSchedulingClassRepository EducationSchedulingClassRepository { get; set; }
        public EducationSchedulingRepository EducationSchedulingRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public CoursematerialRepository CoursematerialRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public EducationSchedulingStopRepository EducationSchedulingStopRepository { get; set; }
        public EducationSchedulingStopTeacherRepository EducationSchedulingStopTeacherRepository { get; set; }
        public EducationSchedulingAdjustmentTeacherRepository EducationSchedulingAdjustmentTeacherRepository { get; set; }
        public EducationSchedulingAdjustmentRepository EducationSchedulingAdjustmentRepository { get; set; }

        public IQueryable<WorktimeAdjustmentView> GetWorktimeAdjustmentViewQueryable(Expression<Func<TP_WorktimeAdjustment, bool>> exp,
            Expression<Func<EM_Coursematerial, bool>> courseExp,
            Expression<Func<CF_Schoolyear, bool>> schoolyearExp)
        {
            var sql = (
                from adjust in WorktimeAdjustmentRepository.GetList(exp)
                join missionClass in EducationMissionClassRepository.Entities on adjust.EducationMissionClassID equals missionClass.EducationMissionClassID
                join classmajor in ClassmajorRepository.Entities on missionClass.MainScheduleClassID equals classmajor.ClassmajorID into dclassmajor
                from classmajor in dclassmajor.DefaultIfEmpty()
                join grade in GrademajorRepository.Entities on classmajor.GrademajorID equals grade.GrademajorID into dgrade
                from grade in dgrade.DefaultIfEmpty()
                join faculty in FacultymajorRepository.Entities on grade.FacultymajorID equals faculty.FacultymajorID into dfaculty
                from faculty in dfaculty.DefaultIfEmpty()
                join mission in EducationMissionRepository.Entities on missionClass.EducationMissionID equals mission.EducationMissionID
                join schoolyear in SchoolyearRepository.Entities.Where(schoolyearExp) on mission.SchoolyearID equals schoolyear.SchoolyearID
                join course in CoursematerialRepository.Entities.Where(courseExp) on missionClass.CoursematerialID equals course.CoursematerialID
                join teacher in UserRepository.Entities on adjust.UserID equals teacher.UserID
                select new WorktimeAdjustmentView
                {
                    WorktimeAdjustmentID = adjust.WorktimeAdjustmentID,
                    SchoolyearID = schoolyear.SchoolyearID,
                    SchoolyearCode = schoolyear.Code,
                    WorktimeAdjustmentTypeID = adjust.WorktimeAdjustmentTypeID,
                    AdjustDate = adjust.AdjustDate,
                    EducationMissionClassID = adjust.EducationMissionClassID,
                    EducationMissionClassName = missionClass.Name,
                    GradeYearID = grade.GradeID,
                    StandardID = faculty.StandardID,
                    CollegeID = faculty.CollegeID,
                    CoursematerialID = missionClass.CoursematerialID,
                    CourseCode = course.CourseCode,
                    CourseName = course.CourseName,
                    UserID = adjust.UserID,
                    TeacherName = teacher.Name,
                    Worktime = adjust.Worktime,
                    CreateTime = adjust.CreateTime
                }
                );

            return sql;
        }

        public IQueryable<EducationMissionClassComboView> GetEducationMissionClassViewQueryable(
            Expression<Func<EM_EducationMissionClass, bool>> exp,
            Expression<Func<ES_EducationSchedulingTeacher, bool>> teacherExp, Expression<Func<ES_EducationScheduling, bool>> scheduleExp)
        {
            var sql = (from scheduling in EducationSchedulingRepository.GetList(scheduleExp)
                       join scheduleClass in EducationSchedulingClassRepository.Entities on scheduling.EducationSchedulingClassID equals scheduleClass.EducationSchedulingClassID
                       join missionClass in EducationMissionClassRepository.Entities.Where(exp) on scheduleClass.EducationMissionClassID equals missionClass.EducationMissionClassID
                       join teacher in EducationSchedulingTeacherRepository.Entities.Where(teacherExp) on scheduling.EducationSchedulingID equals teacher.EducationSchedulingID
                       select new EducationMissionClassComboView
                       {
                           EducationMissionClassID = missionClass.EducationMissionClassID,
                           Name = missionClass.Name
                       });

            return sql;
        }

        public IQueryable<WorktimeSchedulingView> GetWorktimeSchedulingViewQueryable(Expression<Func<ES_EducationScheduling, bool>> exp)
        {
            var sql = (from scheduling in EducationSchedulingRepository.GetList(exp)
                       join scheduleClass in EducationSchedulingClassRepository.Entities on scheduling.EducationSchedulingClassID equals scheduleClass.EducationSchedulingClassID
                       join missionClass in EducationMissionClassRepository.Entities on scheduleClass.EducationMissionClassID equals missionClass.EducationMissionClassID
                       join teacher in EducationSchedulingTeacherRepository.Entities on scheduling.EducationSchedulingID equals teacher.EducationSchedulingID
                       from weeknum in scheduling.ES_EducationSchedulingWeekNum
                       join courseTime in CoursesTimeRepository.Entities on scheduling.CoursesTimeID equals courseTime.CoursesTimeID
                       group courseTime by new
                       {
                           missionClass.EducationMissionClassID,
                           UserID = teacher.UserID,
                           Weekday = scheduling.Weekday,
                           WeekNum = weeknum.WeekNum
                       } into g
                       select new WorktimeSchedulingView
                       {
                           EducationMissionClassID = g.Key.EducationMissionClassID,
                           UserID = g.Key.UserID,
                           Weekday = g.Key.Weekday,
                           WeekNum = g.Key.WeekNum,
                           Worktime = g.Sum(x => x.EndTimes - x.StartTimes + 1)
                       }
                       );

            return sql;
        }

        public IQueryable<WorktimeSchedulingView> GetWorktimeSchedulingViewByStopQueryable(
            Expression<Func<ES_EducationSchedulingStop, bool>> exp,
            Expression<Func<EM_EducationMission, bool>> missionExp)
        {
            var sql = (from stop in EducationSchedulingStopRepository.GetList(exp)
                       join missionClass in EducationMissionClassRepository.Entities on stop.EducationMissionClassID equals missionClass.EducationMissionClassID
                       join mission in EducationMissionRepository.GetList(missionExp) on missionClass.EducationMissionID equals mission.EducationMissionID
                       join teacher in EducationSchedulingStopTeacherRepository.Entities on stop.EducationSchedulingStopID equals teacher.EducationSchedulingStopID
                       join courseTime in CoursesTimeRepository.Entities on stop.CoursesTimeID equals courseTime.CoursesTimeID
                       group courseTime by new
                       {
                           missionClass.EducationMissionClassID,
                           UserID = teacher.UserID,
                           Weekday = stop.Weekday,
                           WeekNum = stop.WeekNum
                       } into g
                       select new WorktimeSchedulingView
                       {
                           EducationMissionClassID = g.Key.EducationMissionClassID,
                           UserID = g.Key.UserID,
                           Weekday = g.Key.Weekday,
                           WeekNum = g.Key.WeekNum,
                           Worktime = g.Sum(x => x.EndTimes - x.StartTimes + 1)
                       });

            return sql;
        }

        public IQueryable<WorktimeSchedulingView> GetWorktimeSchedulingViewByAdjustmentQueryable(
            Expression<Func<ES_EducationSchedulingAdjustment, bool>> exp,
            Expression<Func<EM_EducationMission, bool>> missionExp)
        {
            var sql = (from adju in EducationSchedulingAdjustmentRepository.GetList(exp)
                       join missionClass in EducationMissionClassRepository.Entities on adju.EducationMissionClassID equals missionClass.EducationMissionClassID
                       join mission in EducationMissionRepository.GetList(missionExp) on missionClass.EducationMissionID equals mission.EducationMissionID
                       join teacher in EducationSchedulingAdjustmentTeacherRepository.Entities on adju.EducationSchedulingAdjustmentID equals teacher.EducationSchedulingAdjustmentID
                       join courseTime in CoursesTimeRepository.Entities on adju.CoursesTimeID equals courseTime.CoursesTimeID
                       group courseTime by new
                       {
                           missionClass.EducationMissionClassID,
                           UserID = teacher.UserID,
                           Weekday = adju.Weekday,
                           WeekNum = adju.WeekNum
                       } into g
                       select new WorktimeSchedulingView
                       {
                           EducationMissionClassID = g.Key.EducationMissionClassID,
                           UserID = g.Key.UserID,
                           Weekday = g.Key.Weekday,
                           WeekNum = g.Key.WeekNum,
                           Worktime = g.Sum(x => x.EndTimes - x.StartTimes + 1)
                       });

            return sql;
        }
    }
}