JW/EMiS.DataLogic/RetakeManage/RetakePlanResultDAL.cs

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

namespace EMIS.DataLogic.RetakeManage
{
    public class RetakePlanResultDAL
    {
        public RetakeConditionRepository RetakConditionRepository { get; set; }
        public RetakeConditionParameterRepository RetakeConditionParameterRepository { get; set; }
        public RetakePlanRepository RetakePlanRepository { get; set; }
        public RetakePlanStudentRepository RetakePlanStudentRepository { get; set; }
        public RetakeExamsCatagoryRepository RetakeExamsCatagoryRepository { get; set; }
        public RetakePlanSettingRepository RetakePlanSettingRepository { get; set; }
        public RetakePlanTeachingModeTypeRepository RetakePlanTeachingModeTypeRepository { get; set; }
        public RetakePlanTeachingPlaceRepository RetakePlanTeachingPlaceRepository { get; set; }
        public RetakePlanTeachingSettingRepository RetakePlanTeachingSettingRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public InSchoolSettingRepository InSchoolSettingRepository { get; set; }
        public ParameterRepository ParameterRepository { get; set; }
        public CoursematerialRepository CoursematerialRepository { get; set; }
        public FinallyScoreRepository FinallyScoreRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public DepartmentRepository DepartmentRepository { get; set; }
        public ClassroomRepository ClassroomRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public DictionaryItemRepository DictionaryItemRepository { get; set; }
        public CoursesTimeRepository coursesTimeRepository { get; set; }
        public RetakePlanSettingSchedulingRepository RetakePlanSettingSchedulingRepository { get; set; }
        public RetakePlanTeacherRepository RetakePlanTeacherRepository { get; set; }

        /// <summary>
        /// 查询重修班级信息View（统计已报人数，根据重修计划相关信息）
        /// </summary>
        /// <param name="expRetakePlan"></param>
        /// <returns></returns>
        public IQueryable<RetakePlanTaskView> GetRetakePlanResultTaskView(Expression<Func<ER_RetakePlan, bool>> expRetakePlan)
        {
            var query = from rePlan in RetakePlanRepository.GetList(expRetakePlan)
                        join cou in CoursematerialRepository.Entities
                        on rePlan.CoursematerialID equals cou.CoursematerialID
                        join sy in SchoolyearRepository.Entities
                        on rePlan.SchoolyearID equals sy.SchoolyearID
                        join reStu in
                            (
                                from stu in RetakePlanStudentRepository.Entities
                                group stu by stu.RetakePlanID into g
                                select new
                                {
                                    RetakePlanID = g.Key,
                                    PlanNumber = g.Count()
                                }
                            )
                        on rePlan.RetakePlanID equals reStu.RetakePlanID
                        into reStuTemp
                        from rePlanStu in reStuTemp.DefaultIfEmpty()
                        join reSet in RetakePlanSettingRepository.Entities
                        on rePlan.RetakePlanID equals reSet.RetakePlanID
                        into reSetTemp
                        from rePlanSet in reSetTemp.DefaultIfEmpty()
                        join dep in DepartmentRepository.Entities
                        on rePlanSet.DepartmentID equals dep.DepartmentID
                        into reDepTemp
                        from reDep in reDepTemp.DefaultIfEmpty()
                        join reTeaching in RetakePlanTeachingSettingRepository.Entities
                        on rePlan.RetakePlanID equals reTeaching.RetakePlanID
                        into reTeachingTemp
                        from rePlanTeaching in reTeachingTemp.DefaultIfEmpty()
                        select new RetakePlanTaskView
                        {
                            RetakePlanID = rePlan.RetakePlanID,
                            SchoolyearID = rePlan.SchoolyearID,
                            SchoolyearCode = sy.Code,
                            RetakeTypeID = rePlan.RetakeTypeID,
                            ClassName = rePlan.ClassName,
                            CoursematerialID = rePlan.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            DepartmentID = rePlanSet.DepartmentID,
                            DepartmentCode = reDep.No,
                            DepartmentName = reDep.Name,
                            CourseCollegeID = reDep.CF_College.CollegeID,
                            CourseCollegeNo = reDep.CF_College.No,
                            CourseCollegeName = reDep.CF_College.Name,
                            CourseTypeID = rePlan.CourseTypeID,
                            Credit = rePlan.Credit ?? 0,
                            PeopleNumlimit = rePlan.PeopleNumlimit ?? 0,
                            PeopleNumlower = rePlan.PeopleNumlower ?? 0,
                            TheoryCourse = rePlanTeaching.TheoryCourse ?? 0,
                            Practicehours = rePlanTeaching.Practicehours ?? 0,
                            Trialhours = rePlanTeaching.Trialhours ?? 0,
                            StartWeeklyNum = rePlanTeaching.StartWeeklyNum,
                            EndWeeklyNum = rePlanTeaching.EndWeeklyNum,
                            PlanNumber = rePlanStu != null ? rePlanStu.PlanNumber : 0,
                            ApplyNumber = rePlan.CF_Student.Count,
                            RecordStatus = rePlan.RecordStatus
                        };

            return query;
        }

        /// <summary>
        /// 查询重修班级对应的报名名单信息View（已报名，根据重修计划相关信息）
        /// </summary>
        /// <param name="expRetakePlan"></param>
        /// <returns></returns>
        public IQueryable<RetakeStudentListView> GetRetakePlanResultView(Expression<Func<ER_RetakePlan, bool>> expRetakePlan)
        {
            var query = from rePlan in RetakePlanRepository.GetList(expRetakePlan)
                        from applyStu in rePlan.CF_Student
                        join rePlanStu in RetakePlanStudentRepository.Entities
                        on 
                        new { applyStu.UserID, rePlan.RetakePlanID } 
                        equals 
                        new { rePlanStu.UserID, rePlanStu.RetakePlanID }
                        join stu in StudentRepository.Entities
                        on rePlanStu.UserID equals stu.UserID
                        join us in UserRepository.Entities
                        on stu.UserID equals us.UserID
                        join cl in ClassmajorRepository.Entities
                        on stu.ClassmajorID equals cl.ClassmajorID
                        join gr in GrademajorRepository.Entities
                        on cl.GrademajorID equals gr.GrademajorID
                        join fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        join col in CollegeRepository.Entities
                        on fa.CollegeID equals col.CollegeID
                        join sy in SchoolyearRepository.Entities
                        on rePlan.SchoolyearID equals sy.SchoolyearID
                        join cou in CoursematerialRepository.Entities
                        on rePlan.CoursematerialID equals cou.CoursematerialID
                        join reSet in RetakePlanSettingRepository.Entities
                        on rePlan.RetakePlanID equals reSet.RetakePlanID
                        into reSetTemp
                        from rePlanSet in reSetTemp.DefaultIfEmpty()
                        join dep in DepartmentRepository.Entities
                        on rePlanSet.DepartmentID equals dep.DepartmentID
                        into reDepTemp
                        from reDep in reDepTemp.DefaultIfEmpty()
                        select new RetakeStudentListView
                        {
                            RetakePlanID = rePlan.RetakePlanID,
                            RetakePlanStudentID = rePlanStu.RetakePlanStudentID,
                            RetakeTypeID = rePlan.RetakeTypeID,
                            SchoolyearID = rePlan.SchoolyearID,
                            SchoolyearCode = sy.Code,
                            ClassName = rePlan.ClassName,
                            CoursematerialID = rePlan.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            DepartmentID = rePlanSet.DepartmentID,
                            DepartmentCode = reDep.No,
                            DepartmentName = reDep.Name,
                            CourseTypeID = rePlan.CourseTypeID,
                            CourseCredit = rePlan.Credit ?? 0,
                            SchoolyearNumID = rePlanStu.SchoolyearNumID,
                            StarttermID = rePlanStu.StarttermID,
                            UserID = rePlanStu.UserID,
                            LoginID = us.LoginID,
                            UserName = us.Name,
                            SexID = stu.SexID,
                            InSchoolStatusID = stu.InSchoolStatusID,
                            StudentStatusID = stu.StudentStatus,
                            ClassmajorID = stu.ClassmajorID,
                            ClassmajorCode = cl.No,
                            ClassmajorName = cl.Name,
                            GrademajorID = cl.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            CollegeID = fa.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            Gradeyear = gr.GradeID,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            RecordStatus = rePlan.RecordStatus,
                            ApplyStatus = rePlan.CF_Student.Any(x => x.UserID == rePlanStu.UserID) ? true : false
                        };

            return query;
        }

        /// <summary>
        /// 查询重修班级对应的报名名单信息View（已报名，根据重修计划相关信息、学生信息）
        /// </summary>
        /// <param name="expRetakePlan"></param>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<RetakeStudentListView> GetRetakePlanResultStudentView(Expression<Func<ER_RetakePlan, bool>> expRetakePlan,
                Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from rePlan in RetakePlanRepository.GetList(expRetakePlan)
                        from applyStu in rePlan.CF_Student
                        join rePlanStu in RetakePlanStudentRepository.Entities
                        on
                        new { applyStu.UserID, rePlan.RetakePlanID }
                        equals
                        new { rePlanStu.UserID, rePlanStu.RetakePlanID }
                        join stu in StudentRepository.GetList(expStudent)
                        on rePlanStu.UserID equals stu.UserID
                        join us in UserRepository.Entities
                        on stu.UserID equals us.UserID
                        join cl in ClassmajorRepository.Entities
                        on stu.ClassmajorID equals cl.ClassmajorID
                        join gr in GrademajorRepository.Entities
                        on cl.GrademajorID equals gr.GrademajorID
                        join fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        join col in CollegeRepository.Entities
                        on fa.CollegeID equals col.CollegeID
                        join sy in SchoolyearRepository.Entities
                        on rePlan.SchoolyearID equals sy.SchoolyearID
                        join cou in CoursematerialRepository.Entities
                        on rePlan.CoursematerialID equals cou.CoursematerialID
                        join reSet in RetakePlanSettingRepository.Entities
                        on rePlan.RetakePlanID equals reSet.RetakePlanID
                        into reSetTemp
                        from rePlanSet in reSetTemp.DefaultIfEmpty()
                        join dep in DepartmentRepository.Entities
                        on rePlanSet.DepartmentID equals dep.DepartmentID
                        into reDepTemp
                        from reDep in reDepTemp.DefaultIfEmpty()
                        select new RetakeStudentListView
                        {
                            RetakePlanID = rePlan.RetakePlanID,
                            RetakePlanStudentID = rePlanStu.RetakePlanStudentID,
                            RetakeTypeID = rePlan.RetakeTypeID,
                            SchoolyearID = rePlan.SchoolyearID,
                            SchoolyearCode = sy.Code,
                            ClassName = rePlan.ClassName,
                            CoursematerialID = rePlan.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            DepartmentID = rePlanSet.DepartmentID,
                            DepartmentCode = reDep.No,
                            DepartmentName = reDep.Name,
                            CourseTypeID = rePlan.CourseTypeID,
                            CourseCredit = rePlan.Credit ?? 0,
                            SchoolyearNumID = rePlanStu.SchoolyearNumID,
                            StarttermID = rePlanStu.StarttermID,
                            UserID = rePlanStu.UserID,
                            LoginID = us.LoginID,
                            UserName = us.Name,
                            SexID = stu.SexID,
                            InSchoolStatusID = stu.InSchoolStatusID,
                            StudentStatusID = stu.StudentStatus,
                            ClassmajorID = stu.ClassmajorID,
                            ClassmajorCode = cl.No,
                            ClassmajorName = cl.Name,
                            GrademajorID = cl.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            CollegeID = fa.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            Gradeyear = gr.GradeID,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            RecordStatus = rePlan.RecordStatus,
                            ApplyStatus = rePlan.CF_Student.Any(x => x.UserID == rePlanStu.UserID) ? true : false
                        };

            return query;
        }

        /// <summary>
        /// 查询重修班级对应的未报名名单信息View（未报名，根据重修计划相关信息）
        /// </summary>
        /// <param name="expRetakePlan"></param>
        /// <returns></returns>
        public IQueryable<RetakeStudentListView> GetRetakePlanResultTaskStudentView(Expression<Func<ER_RetakePlan, bool>> expRetakePlan)
        {
            var query = from rePlan in RetakePlanRepository.GetList(expRetakePlan)
                        join rePlanStu in RetakePlanStudentRepository.Entities
                        on rePlan.RetakePlanID equals rePlanStu.RetakePlanID
                        join stu in StudentRepository.Entities
                        on rePlanStu.UserID equals stu.UserID
                        join us in UserRepository.Entities
                        on stu.UserID equals us.UserID
                        join cl in ClassmajorRepository.Entities
                        on stu.ClassmajorID equals cl.ClassmajorID
                        join gr in GrademajorRepository.Entities
                        on cl.GrademajorID equals gr.GrademajorID
                        join fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        join col in CollegeRepository.Entities
                        on fa.CollegeID equals col.CollegeID
                        join sy in SchoolyearRepository.Entities
                        on rePlan.SchoolyearID equals sy.SchoolyearID
                        join cou in CoursematerialRepository.Entities
                        on rePlan.CoursematerialID equals cou.CoursematerialID
                        join reSet in RetakePlanSettingRepository.Entities
                        on rePlan.RetakePlanID equals reSet.RetakePlanID
                        into reSetTemp
                        from rePlanSet in reSetTemp.DefaultIfEmpty()
                        join dep in DepartmentRepository.Entities
                        on rePlanSet.DepartmentID equals dep.DepartmentID
                        into reDepTemp
                        from reDep in reDepTemp.DefaultIfEmpty()
                        where !rePlan.CF_Student.Contains(stu)
                        select new RetakeStudentListView
                        {
                            RetakePlanID = rePlan.RetakePlanID,
                            RetakePlanStudentID = rePlanStu.RetakePlanStudentID,
                            RetakeTypeID = rePlan.RetakeTypeID,
                            SchoolyearID = rePlan.SchoolyearID,
                            SchoolyearCode = sy.Code,
                            ClassName = rePlan.ClassName,
                            CoursematerialID = rePlan.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            DepartmentID = rePlanSet.DepartmentID,
                            DepartmentCode = reDep.No,
                            DepartmentName = reDep.Name,
                            CourseTypeID = rePlan.CourseTypeID,
                            CourseCredit = rePlan.Credit ?? 0,
                            SchoolyearNumID = rePlanStu.SchoolyearNumID,
                            StarttermID = rePlanStu.StarttermID,
                            UserID = rePlanStu.UserID,
                            LoginID = us.LoginID,
                            UserName = us.Name,
                            SexID = stu.SexID,
                            InSchoolStatusID = stu.InSchoolStatusID,
                            StudentStatusID = stu.StudentStatus,
                            ClassmajorID = stu.ClassmajorID,
                            ClassmajorCode = cl.No,
                            ClassmajorName = cl.Name,
                            GrademajorID = cl.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            CollegeID = fa.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            Gradeyear = gr.GradeID,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            RecordStatus = rePlan.RecordStatus,
                            ApplyStatus = rePlan.CF_Student.Any(x => x.UserID == rePlanStu.UserID) ? true : false
                        };

            return query;
        }

        /// <summary>
        /// 查询重修计划对应的任课教师信息
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<RetakePlanTaskView> GetRetakePlanTeacherView(Expression<Func<ER_RetakePlan, bool>> exp)
        {
            var query = from plan in RetakePlanRepository.GetList(exp)
                        join teacher in RetakePlanTeacherRepository.Entities 
                        on plan.RetakePlanID equals teacher.RetakePlanID into tmp
                        from teacher in tmp.DefaultIfEmpty()
                        select new RetakePlanTaskView
                        {
                            RetakePlanID = plan.RetakePlanID,
                            TeacherName = teacher.CF_Staff.Sys_User.Name,
                            TeacherUserID = teacher.UserID,
                            TeacherUserCode = teacher.CF_Staff.StaffCode,
                        };
            return query;
        }

    }
}