JW/EMiS.DataLogic/RetakeManage/RetakePlanDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EMIS.DataLogic.Repositories;
using EMIS.ViewModel.RetakeManage;
using System.Linq.Expressions;
using EMIS.Entities;
using EMIS.ViewModel;
using EMIS.DataLogic.ChargeManage.ArrearsSituation;
using EMIS.ViewModel.ChargeManage.ChargeSituation;
using EMIS.ViewModel.EducationManagement;

namespace EMIS.DataLogic.RetakeManage
{
    public class RetakePlanDAL
    {
        public RetakePlanRepository RetakePlanRepository { get; set; }
        public RetakePlanStudentRepository RetakePlanStudentRepository { get; set; }
        public RetakeExamsCatagoryRepository RetakeExamsCatagoryRepository { get; set; }
        public CoursematerialRepository CoursematerialRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public FinallyScoreRepository FinallyScoreRepository { get; set; }
        public StudentChargeRepository StudentChargeRepository { get; set; }
        public ParameterRepository ParameterRepository { get; set; }
        public EducationMissionRepository EducationMissionRepository { get; set; }
        public EducationMissionClassRepository EducationMissionClassRepository { get; set; }
        public EducationMissionClassTeachingSettingRepository EducationMissionClassTeachingSettingRepository { get; set; }
        public EducationSchedulingClassRepository EducationSchedulingClassRepository { get; set; }
        public AdultEducationScheduleRepository AdultEducationScheduleRepository { get; set; }
        public AdultEducationScheduleTeacherRepository AdultEducationScheduleTeacherRepository { get; set; }
        public CourseTimeHoursRepository CourseTimeHoursRepository { get; set; }
        public Lazy<ChargeControlDAL> ChargeControlDAL { get; set; }

        public IQueryable<RetakeStudentListView> GetRetakeStudentListViewQuery(Expression<Func<ER_RetakePlan, bool>> exp,
            Expression<Func<ER_RetakePlanStudent, bool>> studentExp)
        {
            var query = (from plan in RetakePlanRepository.GetList(exp)
                         join schoolyear in SchoolyearRepository.Entities on plan.SchoolyearID equals schoolyear.SchoolyearID
                         join planstudent in RetakePlanStudentRepository.GetList(studentExp) on plan.RetakePlanID equals planstudent.RetakePlanID
                         join student in StudentRepository.Entities on planstudent.UserID equals student.UserID
                         join user in UserRepository.Entities on planstudent.UserID equals user.UserID
                         join classmajor in ClassmajorRepository.Entities on student.ClassmajorID equals classmajor.ClassmajorID
                         join grade in GrademajorRepository.Entities on classmajor.GrademajorID equals grade.GrademajorID
                         join faculty in FacultymajorRepository.Entities on grade.FacultymajorID equals faculty.FacultymajorID
                         join college in CollegeRepository.Entities on faculty.CollegeID equals college.CollegeID
                         join course in CoursematerialRepository.Entities on plan.CoursematerialID equals course.CoursematerialID
                         join nopass in
                             (
                                 from score in FinallyScoreRepository.GetList(x => x.TotalScore < 60 && x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                                 join type in RetakeExamsCatagoryRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE) on score.ExamsCategoryID equals type.ExamsCategoryID
                                 group score by score.UserID into g
                                 select new
                                 {
                                     UserID = g.Key,
                                     NoPassCount = g.Count()
                                 }
                                 ) on planstudent.UserID equals nopass.UserID into dnopass
                         from enopass in dnopass.DefaultIfEmpty()

                         //此处有误，后续修复。。。
                         join arrears in ChargeControlDAL.Value.GetReatkePlanListViewAble(x => x.ChargeTag == (int)EC_ChargeTag.Retake)
                                .GroupBy(x => x.UserID).Select(x => new { UserID = x.Key })
                            on planstudent.UserID equals arrears.UserID into darrears
                         from earrears in darrears.DefaultIfEmpty()
                         join s in ChargeControlDAL.Value.StudentChargeRepository.Entities.Where(x => x.ChargeTag == (int)EC_ChargeTag.Retake)
                         on planstudent.UserID equals s.UserID into d
                         from e in d.DefaultIfEmpty()
                         select new RetakeStudentListView
                         {
                             CourseTypeID = plan.CourseTypeID,
                             CourseCode = course.CourseCode,
                             CourseName = course.CourseName,
                             SchoolyearID = plan.SchoolyearID,
                             SchoolyearCode = schoolyear.Code,
                             UserID = planstudent.UserID,
                             LoginID = user.LoginID,
                             UserName = user.Name,
                             CollegeID = faculty.CollegeID,
                             CollegeNo = college.No,
                             CollegeName = college.Name,
                             Gradeyear = grade.GradeID,
                             StandardID = faculty.StandardID,
                             ClassmajorID = student.ClassmajorID,
                             ClassName = classmajor.Name,
                             NoPassCount = enopass.UserID == null ? 0 : enopass.NoPassCount,
                             IsNoArrears = earrears.UserID == null,
                             AmountSum = e.Amount
                         });

            return query.Distinct();
        }

        public IQueryable<RetakeStudentListView> GetRetakeStudentDetailListViewQuery(Expression<Func<ER_RetakePlan, bool>> exp,
        Expression<Func<ER_RetakePlanStudent, bool>> studentExp)
        {
            var query = (from plan in RetakePlanRepository.GetList(exp)
                         join schoolyear in SchoolyearRepository.Entities on plan.SchoolyearID equals schoolyear.SchoolyearID
                         join planstudent in RetakePlanStudentRepository.GetList(studentExp) on plan.RetakePlanID equals planstudent.RetakePlanID
                         join student in StudentRepository.Entities on planstudent.UserID equals student.UserID
                         join user in UserRepository.Entities on planstudent.UserID equals user.UserID
                         join classmajor in ClassmajorRepository.Entities on student.ClassmajorID equals classmajor.ClassmajorID
                         join grade in GrademajorRepository.Entities on classmajor.GrademajorID equals grade.GrademajorID
                         join faculty in FacultymajorRepository.Entities on grade.FacultymajorID equals faculty.FacultymajorID
                         join college in CollegeRepository.Entities on faculty.CollegeID equals college.CollegeID
                         join course in CoursematerialRepository.Entities on plan.CoursematerialID equals course.CoursematerialID
                         join nopass in
                             (
                                 from score in FinallyScoreRepository.GetList(x => x.TotalScore < 60 && x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                                 join type in RetakeExamsCatagoryRepository.GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE) on score.ExamsCategoryID equals type.ExamsCategoryID
                                 group score by score.UserID into g
                                 select new
                                 {
                                     UserID = g.Key,
                                     NoPassCount = g.Count()
                                 }
                                 ) on planstudent.UserID equals nopass.UserID into dnopass
                         from enopass in dnopass.DefaultIfEmpty()

                         //此处有误，后续修复。。。
                         join arrears in ChargeControlDAL.Value.GetArrearListViewAble(x => x.ChargeTag == (int)EC_ChargeTag.Retake)
                                .GroupBy(x => x.UserID).Select(x => new { UserID = x.Key })
                            on planstudent.UserID equals arrears.UserID into darrears
                         from earrears in darrears.DefaultIfEmpty()
                         select new RetakeStudentListView
                         {
                             RetakePlanStudentID = planstudent.RetakePlanStudentID,
                             CoursematerialID = plan.CoursematerialID,
                             CourseCode = course.CourseCode,
                             Abbreviation = course.Abbreviation,
                             CourseName = course.CourseName,
                             SchoolyearID = plan.SchoolyearID,
                             SchoolyearCode = schoolyear.Code,
                             CourseTypeID = plan.CourseTypeID,
                             Credit = plan.Credit,
                             UserID = planstudent.UserID,
                             LoginID = user.LoginID,
                             UserName = user.Name,
                             CollegeID = faculty.CollegeID,
                             CollegeNo = college.No,
                             CollegeName = college.Name,
                             Gradeyear = grade.GradeID,
                             StandardID = faculty.StandardID,
                             ClassmajorID = student.ClassmajorID,
                             ClassName = plan.ClassName,
                             SchoolyearNumID = planstudent.SchoolyearNumID,
                             StarttermID = planstudent.StarttermID,
                             NoPassCount = enopass.UserID == null ? 0 : enopass.NoPassCount,
                             IsNoArrears = earrears.UserID == null
                         });

            return query.Distinct();
        }
        public IQueryable<StudentChargeView> GetStudentChargeQueryable(Expression<Func<ER_RetakePlan, bool>> exp,
            Expression<Func<ER_RetakePlanStudent, bool>> studentExp)
        {
            var chargeProjectIDStr = ParameterRepository.GetList(x => x.ParameterTypeID == (int)CF_ParameterType.RetakeChargeProjectID)
                            .Select(x => x.Value).FirstOrDefault();
            Guid? chargeProjectID = null;
            if (chargeProjectIDStr != null)
            {
                chargeProjectID = new Guid(chargeProjectIDStr);
            }
            else
            {
                throw new Exception("请先在重修参数菜单中指定推送的收费项目。");
            }

            var query = (from planStudent in this.GetRetakeStudentListViewQuery(exp, studentExp)
                         join schoolyear in SchoolyearRepository.Entities on planStudent.SchoolyearID equals schoolyear.SchoolyearID
                         join charge in StudentChargeRepository.Entities
                             on new { planStudent.UserID, ChargeYear = (int?)schoolyear.Years, ChargeProjectID = chargeProjectID }
                             equals new { charge.UserID, charge.ChargeYear, charge.ChargeProjectID }
                         into dcharge
                         from echarge in dcharge.DefaultIfEmpty()
                         where echarge.StudentChargeID == null
                         select new StudentChargeView
                         {
                             StudentChargeID = Guid.NewGuid(),
                             UserID = planStudent.UserID,
                             ChargeProjectID = chargeProjectID,
                             ChargeYear = schoolyear.Years,
                             Amount = 0,
                             ActualAmount = 0,
                             ChargeTag = (int)EC_ChargeTag.Retake,
                             Remark = ""
                         });

            return query;
        }

        public IQueryable<ER_RetakePlan> GetRetakePlanByStudentQueryable(IQueryable<RetakeStudentView> studentQueryable, int? startStatusID)
        {
            var schoolyearIDStr = ParameterRepository.GetList(x => x.ParameterTypeID == (int)CF_ParameterType.RetakeSchoolyearID)
                            .Select(x => x.Value).FirstOrDefault();
            Guid? schoolyearID = null;
            if (schoolyearIDStr != null)
            {
                schoolyearID = new Guid(schoolyearIDStr);
            }
            else
            {
                throw new Exception("未设置重修学年学期，无法生成重修学生名单，请在重修参数菜单中设置重修学年学期。");
            }

            var query = (from planStudent in studentQueryable
                         group planStudent by new { planStudent.CoursematerialID, planStudent.CourseTypeID, planStudent.Credit } into g
                         join plan in RetakePlanRepository.GetList(x => x.RecordStatus == startStatusID
                                                                    && x.SchoolyearID == schoolyearID)
                             on new { g.Key.CoursematerialID, g.Key.CourseTypeID, g.Key.Credit }
                             equals new { plan.CoursematerialID, plan.CourseTypeID, plan.Credit }
                         select plan);

            return query;
        }

        public IQueryable<RetakeStudentApplyView> GetRetakeStudentApplyViewQueryable(Expression<Func<ER_RetakePlanStudent, bool>> studentExp)
        {
            var query = (from plan in RetakePlanRepository.Entities
                         join schoolyear in SchoolyearRepository.Entities on plan.SchoolyearID equals schoolyear.SchoolyearID
                         join planstudent in RetakePlanStudentRepository.GetList(studentExp) on plan.RetakePlanID equals planstudent.RetakePlanID
                         join student in StudentRepository.Entities on planstudent.UserID equals student.UserID
                         join user in UserRepository.Entities on planstudent.UserID equals user.UserID
                         join classmajor in ClassmajorRepository.Entities on student.ClassmajorID equals classmajor.ClassmajorID
                         join grade in GrademajorRepository.Entities on classmajor.GrademajorID equals grade.GrademajorID
                         join faculty in FacultymajorRepository.Entities on grade.FacultymajorID equals faculty.FacultymajorID
                         join college in CollegeRepository.Entities on faculty.CollegeID equals college.CollegeID
                         join course in CoursematerialRepository.Entities on plan.CoursematerialID equals course.CoursematerialID
                         join mission in EducationMissionRepository.Entities on planstudent.EducationMissionID equals mission.EducationMissionID into dmission
                         from emission in dmission.DefaultIfEmpty()
                         join missionClass in EducationMissionClassRepository.Entities on emission.EducationMissionID equals missionClass.EducationMissionID into dmissionClass
                         from emissionClass in dmissionClass.DefaultIfEmpty() 
                         join teachSetting in EducationMissionClassTeachingSettingRepository.Entities on emissionClass.EducationMissionClassID equals teachSetting.EducationMissionClassID into dTeachSetting 
                         from eteachSetting in dTeachSetting.DefaultIfEmpty() 
                         from coursesTime in emissionClass.EM_CoursesTime.DefaultIfEmpty()
                         join schedule in
                             (
                                 from schedule in AdultEducationScheduleRepository.Entities
                                 group schedule by new { schedule.EM_EducationSchedulingClass.EM_EducationMissionClass.EducationMissionID, schedule.ClassroomID, ClassroomName = schedule.CF_Classroom.Name } into g
                                 select new { g.Key.EducationMissionID, g.Key.ClassroomID, g.Key.ClassroomName }
                                 ) on emissionClass.EducationMissionID
                             equals schedule.EducationMissionID into dschedule
                         from eschedule in dschedule.DefaultIfEmpty()
                         select new RetakeStudentApplyView
                         {
                             RetakePlanStudentID = planstudent.RetakePlanStudentID,
                             CoursematerialID = plan.CoursematerialID,
                             CourseCode = course.CourseCode,
                             Abbreviation = course.Abbreviation,
                             CourseName = course.CourseName,
                             SchoolyearID = plan.SchoolyearID,
                             CourseTypeID = plan.CourseTypeID,
                             Credit = plan.Credit,
                             UserID = planstudent.UserID,
                             SchoolyearNumID = planstudent.SchoolyearNumID,
                             StarttermID = planstudent.StarttermID,
                             EducationMissionID = planstudent.EducationMissionID,
                             TimeID = coursesTime.TimesSegment,
                             CourseTimeID = coursesTime.CoursesTimeID,
                             ScheduledTimes = (eteachSetting.TheoryCourse ?? 0) + (eteachSetting.Practicehours ?? 0),
                             ClassroomID = eschedule.ClassroomID,
                             ClassroomName = eschedule.ClassroomName,
                             RecordStatus = planstudent.RecordStatus
                         });

            return query;
        }

        public IQueryable<RetakePlanStudentTeacherView> GetRetakePlanStudentTeacherViewQueryable(Expression<Func<ER_RetakePlanStudent, bool>> studentExp)
        {
            var query = (from plan in RetakePlanRepository.Entities
                         join schoolyear in SchoolyearRepository.Entities on plan.SchoolyearID equals schoolyear.SchoolyearID
                         join planstudent in RetakePlanStudentRepository.GetList(studentExp) on plan.RetakePlanID equals planstudent.RetakePlanID
                         join mission in EducationMissionRepository.Entities on planstudent.EducationMissionID equals mission.EducationMissionID
                         join missionClass in EducationMissionClassRepository.Entities on mission.EducationMissionID equals missionClass.EducationMissionID
                         join schedulingClass in EducationSchedulingClassRepository.Entities on missionClass.EducationMissionClassID equals schedulingClass.EducationSchedulingClassID
                         join schedule in AdultEducationScheduleRepository.Entities on schedulingClass.EducationSchedulingClassID equals schedule.EducationSchedulingClassID
                         join teacher in AdultEducationScheduleTeacherRepository.Entities on schedule.AdultEducationScheduleID equals teacher.AdultEducationScheduleID
                         join user in UserRepository.Entities on teacher.UserID equals user.UserID
                         select new RetakePlanStudentTeacherView
                         {
                             RetakePlanStudentID = planstudent.RetakePlanStudentID,
                             UserID = teacher.UserID,
                             Name = user.Name
                         });

            return query;
        }

        public IQueryable<RetakeStudentApproveView> GetRetakeStudentApproveViewQueryable(Expression<Func<ER_RetakePlan, bool>> exp,
            Expression<Func<ER_RetakePlanStudent, bool>> studentExp)
        {
            var query = (from plan in RetakePlanRepository.GetList(exp)
                         join schoolyear in SchoolyearRepository.Entities on plan.SchoolyearID equals schoolyear.SchoolyearID
                         join planstudent in RetakePlanStudentRepository.GetList(studentExp) on plan.RetakePlanID equals planstudent.RetakePlanID
                         join course in CoursematerialRepository.Entities on plan.CoursematerialID equals course.CoursematerialID
                         join student in StudentRepository.Entities on planstudent.UserID equals student.UserID
                         join user in UserRepository.Entities on planstudent.UserID equals user.UserID
                         join classmajor in ClassmajorRepository.Entities on student.ClassmajorID equals classmajor.ClassmajorID
                         join grade in GrademajorRepository.Entities on classmajor.GrademajorID equals grade.GrademajorID
                         join facult in FacultymajorRepository.Entities on grade.FacultymajorID equals facult.FacultymajorID
                         join college in CollegeRepository.Entities on facult.CollegeID equals college.CollegeID
                         join mission in EducationMissionRepository.Entities on planstudent.EducationMissionID equals mission.EducationMissionID
                         join missionclass in EducationMissionClassRepository.Entities on mission.EducationMissionID equals missionclass.EducationMissionID
                         join teachSetting in EducationMissionClassTeachingSettingRepository.Entities on missionclass.EducationMissionClassID equals teachSetting.EducationMissionClassID 
                         join scheduleclass in EducationSchedulingClassRepository.Entities on missionclass.EducationMissionClassID equals scheduleclass.EducationMissionClassID
                         from coursetime in missionclass.EM_CoursesTime
                         join classroom in
                             (
                                 from schedule in AdultEducationScheduleRepository.Entities
                                 group schedule by new { schedule.EM_EducationSchedulingClass.EM_EducationMissionClass.EducationMissionID, schedule.ClassroomID, ClassroomName = schedule.CF_Classroom.Name } into g
                                 select new { g.Key.EducationMissionID, g.Key.ClassroomID, g.Key.ClassroomName }
                                 ) on mission.EducationMissionID equals classroom.EducationMissionID
                         select new RetakeStudentApproveView
                         {
                             RetakePlanStudentID = planstudent.RetakePlanStudentID,
                             SchoolyearCode = schoolyear.Code,
                             CoursematerialID = plan.CoursematerialID,
                             Abbreviation = course.Abbreviation,
                             CourseCode = course.CourseCode,
                             CourseName = course.CourseName,
                             SchoolyearID = schoolyear.SchoolyearID,
                             CourseTypeID = plan.CourseTypeID,
                             Credit = plan.Credit,
                             UserID = planstudent.UserID,
                             SchoolyearNumID = planstudent.SchoolyearNumID,
                             StarttermID = planstudent.StarttermID,
                             EducationMissionID = planstudent.EducationMissionID,
                             LoginID = user.LoginID,
                             UserName = user.Name,
                             ClassmajorID = student.ClassmajorID,
                             ClassmajorName = classmajor.Name,
                             CollegeID = facult.CollegeID,
                             CollegeNo = college.No,
                             CollegeName = college.Name,
                             ClassName = mission.ClassName,
                             RetakePlanStudentTeacherView = scheduleclass.ES_AdultEducationSchedule.SelectMany(x => x.ES_AdultEducationScheduleTeacher.Select(w => w.CF_Staff.Sys_User)),
                             ScheduleDate = scheduleclass.ES_AdultEducationSchedule.Select(x => x.ScheduleDate),
                             TimeID = coursetime.TimesSegment,
                             CourseTimeID = coursetime.CoursesTimeID,
                             TotalHours = (teachSetting.TheoryCourse ?? 0)
                                        + (teachSetting.Practicehours ?? 0),
                             ClassroomID = classroom.ClassroomID,
                             ClassroomName = classroom.ClassroomName,
                             RecordStatus = planstudent.RecordStatus
                         }
                         );

            return query;
        }
    }
}