JW/EMiS.DataLogic/PaymentManage/PaymentDAL.cs

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

namespace EMIS.DataLogic.PaymentManage
{
    public class PaymentDAL
    {
        public StaffRepository StaffRepository { get; set; }
        public StaffProfileRepository StaffProfileRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public CourseTimeHoursRepository CourseTimeHoursRepository { get; set; }
        public CoursematerialRepository CoursematerialRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public EducationMissionRepository EducationMissionRepository { get; set; }
        public EducationMissionClassRepository EducationMissionClassRepository { get; set; }
        public EducationMissionClassTeachingSettingRepository EducationMissionClassTeachingSettingRepository { get; set; }
        public ExaminationExemptionRepository ExaminationExemptionRepository { get; set; }
        public PaymentStandardRepository PaymentStandardRepository { get; set; }
        public LearningformRateRepository LearningformRateRepository { get; set; }
        public StudentCountRateRepository StudentCountRateRepository { get; set; }
        public PracticeRateRepository PracticeRateRepository { get; set; }

        public IQueryable<PaymentDetailView> GetPaymentDetailViewQueryable(Expression<Func<CF_Staff, bool>> staffExp,
            Expression<Func<EMIS.Entities.CF_Schoolyear, bool>> schoolyearExp, 
            Expression<Func<EM_EducationMission, bool>> missionExp,
            Expression<Func<EM_Coursematerial, bool>> coursematerialExp)
        {
            var query = (from staff in StaffRepository.GetList(staffExp)
                         join profile in StaffProfileRepository.Entities on staff.UserID equals profile.UserID
                         join user in UserRepository.Entities on staff.UserID equals user.UserID
                         from teacher in staff.EM_MissionClassTeacher.GroupBy(x => x.MissionClassID).Select(x => x.FirstOrDefault())
                         join missionClass in EducationMissionClassRepository.GetList(x => x.RecordStatus == (int)EM_EducationMissionClassStatus.Scheduled)
                            on teacher.MissionClassID equals missionClass.EducationMissionClassID 
                         join teachSetting in EducationMissionClassTeachingSettingRepository.Entities on missionClass.EducationMissionClassID equals teachSetting.EducationMissionClassID 
                         join mission in EducationMissionRepository.GetList(missionExp) on missionClass.EducationMissionID equals mission.EducationMissionID
                         join college in CollegeRepository.Entities on mission.CollegeID equals college.CollegeID
                         join schoolyear in SchoolyearRepository.GetList(schoolyearExp) on mission.SchoolyearID equals schoolyear.SchoolyearID
                         join course in CoursematerialRepository.GetList(coursematerialExp) on missionClass.CoursematerialID equals course.CoursematerialID
                         join faculty in
                             (
                                 from missionClass in EducationMissionClassRepository.Entities
                                 from studentClass in missionClass.CF_Classmajor
                                 join grade in GrademajorRepository.Entities on studentClass.GrademajorID equals grade.GrademajorID
                                 join faculty in FacultymajorRepository.Entities on grade.FacultymajorID equals faculty.FacultymajorID
                                 group faculty by new { missionClass.EducationMissionClassID, faculty.EducationID, faculty.LearningformID } into g
                                 select new { g.Key.EducationMissionClassID, g.Key.EducationID, g.Key.LearningformID }
                                 ) on missionClass.EducationMissionClassID equals faculty.EducationMissionClassID
                         join timeSegment in
                             (
                                 from missionClass in EducationMissionClassRepository.Entities
                                 from courseTime in missionClass.EM_CoursesTime
                                 join courseTimeHours in CourseTimeHoursRepository.Entities
                                    on new { CoursesTimeID = (Guid?)courseTime.CoursesTimeID, Years = (int?)missionClass.CF_Classmajor.Max(x => x.CF_Grademajor.GradeID) } equals new { courseTimeHours.CoursesTimeID, courseTimeHours.Years }
                                    into dcourseTimeHours 
                                 from courseTimeHours in dcourseTimeHours.DefaultIfEmpty() 
                                 group new { courseTime, courseTimeHours } by new { missionClass.EducationMissionClassID, courseTime.TimesSegment } into g
                                 select new { g.Key.EducationMissionClassID, g.Key.TimesSegment, Hours = g.Sum(x => x.courseTimeHours.Hours ?? (x.courseTime.EndTimes - x.courseTime.StartTimes + 1)) }
                                 ) on missionClass.EducationMissionClassID equals timeSegment.EducationMissionClassID
                         join exemption in
                             (
                                 from exemption in ExaminationExemptionRepository.Entities
                                 from schedulingClass in exemption.CF_Student.EM_EducationSchedulingClass.Where(x => x.EM_EducationMissionClass.CoursematerialID == exemption.CoursematerialID
                                    && exemption.SchoolyearID == x.EM_EducationMissionClass.EM_EducationMission.SchoolyearID)
                                 where exemption.ExamsCategoryID == (int)CF_ExamsCategory.FinalExam
                                 group exemption by new { schedulingClass.EducationMissionClassID } into g
                                 select new { g.Key.EducationMissionClassID, StudentCount = (int?)g.Count() }
                                 ) on missionClass.EducationMissionClassID equals exemption.EducationMissionClassID into dexemption
                         from eexemption in dexemption.DefaultIfEmpty()
                         join paymentStandard in PaymentStandardRepository.Entities on new { staff.TitleID, staff.TeacherTypeID } equals new { TitleID = paymentStandard.Title, TeacherTypeID = paymentStandard.TeacherType }
                         join learnformRate in LearningformRateRepository.Entities on new { faculty.EducationID, faculty.LearningformID } equals new { learnformRate.EducationID, learnformRate.LearningformID }
                         join studentCountRate in StudentCountRateRepository.GetList(x => x.Title.HasValue) on staff.TitleID equals studentCountRate.Title into dstudentCountRate
                         from estudentCountRate in dstudentCountRate.DefaultIfEmpty()
                         join defaultStudentCountRate in StudentCountRateRepository.GetList(x => !x.Title.HasValue) on true equals true into ddefaultStudentCountRate
                         from edefaultStudentCountRate in ddefaultStudentCountRate.DefaultIfEmpty()
                         join practiceRate in PracticeRateRepository.Entities on missionClass.CourseTypeID equals practiceRate.CourseTypeID
                         select new PaymentDetailView
                         {
                             UserID = staff.UserID,
                             LoginID = user.LoginID,
                             UserName = user.Name,
                             TeacherTypeID = staff.TeacherTypeID,
                             TitleID = staff.TitleID,
                             Account = profile.Account,
                             SchoolyearID = schoolyear.SchoolyearID,
                             SchoolyearCode = schoolyear.Code,
                             CollegeID = mission.CollegeID,
                             CollegeNo = college.No,
                             CollegeName = college.Name,
                             EducationID = faculty.EducationID,
                             LearningformID = faculty.LearningformID,
                             StudentCount = missionClass.EM_EducationSchedulingClass.SelectMany(x => x.CF_Student).Count(),
                             ExaminationModeID = missionClass.ExaminationModeID,
                             RetakeStudentCount = mission.ER_RetakePlanStudent.Count(),
                             PracticeHours = teachSetting.Practicehours,
                             CF_Classmajor = missionClass.CF_Classmajor,
                             CoursematerialID = missionClass.CoursematerialID,
                             CoursematerialName = course.CourseName,
                             PlanTotalHours = teachSetting.TheoryCourse + teachSetting.Practicehours,
                             TimeSegmentID = timeSegment.TimesSegment,
                             ScheduleDayCount = missionClass.EM_EducationSchedulingClass
                                                .SelectMany(x => x.ES_AdultEducationSchedule
                                                    .Where(w => w.ES_AdultEducationScheduleTeacher.Any(y => y.UserID == staff.UserID))
                                                ).Count(),
                             CourseTimeHours = timeSegment.Hours,
                             ExemptionCount = eexemption.StudentCount ?? 0,
                             PaymentStandard = paymentStandard.Amount,
                             LearningformRate = learnformRate.Rate,
                             BaseStudentCount = estudentCountRate.BaseStudentCount ?? edefaultStudentCountRate.BaseStudentCount,
                             StudentCountRateLimit = estudentCountRate.Limit ?? edefaultStudentCountRate.Limit,
                             PracticeRate = practiceRate.Rate
                         });

            return query;
        }
    }
}