JW/ServiceLogic/EMIS.ExtensionLogic.Adult/CommonDataLogic/GraduationManage/GraduationStandardDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using EMIS.Entities;
using EMIS.DataLogic.Repositories;
using EMIS.ViewModel.CultureplanManage.PlanManagement;
using EMIS.ViewModel.GraduationManage.GraduationSetting;

namespace EMIS.ExtensionLogic.CommonDataLogic.GraduationManage
{
    public class GraduationStandardDAL : EMIS.DataLogic.GraduationManage.GraduationSetting.GraduationStandardDAL
    {
        public SpecialtyRepository SpecialtyRepository { get; set; }

        /// <summary>
        /// 查询对应的毕业标准信息View（门数、各类型学分统计：专业计划、执行计划）
        /// 注：由于学分制实行的程度不同，目前只对专业计划进行统计
        /// 此为成教版本
        /// </summary>
        /// <param name="expGraduationStandard"></param>
        /// <param name="expExecutablePlan"></param>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public override IQueryable<GraduationStandardView> GetGraduationStandardViewQueryable(Expression<Func<ER_GraduationStandard, bool>> expGraduationStandard,
                Expression<Func<EM_ExecutablePlan, bool>> expExecutablePlan, Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from gs in GraduationStandardRepository.GetList(expGraduationStandard)
                        join gr in GrademajorRepository.Entities
                        on gs.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 cam in CampusRepository.Entities
                        on col.CampusID equals cam.CampusID
                        join spe in SpecialtyRepository.Entities
                        on new { fa.StandardID, fa.EducationID, fa.LearningformID, fa.LearnSystem }
                        equals new { spe.StandardID, spe.EducationID, spe.LearningformID, spe.LearnSystem }
                        join sye in SchoolyearRepository.Entities
                        on new { SchoolyearID = gr.GradeID.Value, SchoolcodeID = gr.SemesterID.Value }
                        equals new { SchoolyearID = sye.Years, SchoolcodeID = sye.SchoolcodeID } into tempsyenter
                        from syenter in tempsyenter.DefaultIfEmpty()
                        join stugr in
                            (
                                //年级专业对应的学生人数（区分在校、非在校）
                                from stu in StudentRepository.GetList(expStudent)
                                join cl in ClassmajorRepository.Entities
                                on stu.ClassmajorID equals cl.ClassmajorID
                                group cl by cl.GrademajorID into g
                                select new
                                {
                                    GrademajorID = g.Key,
                                    StudentCount = g.Count()
                                }
                            )
                        on gr.GrademajorID equals stugr.GrademajorID into tempstugr
                        from gsstugr in tempstugr.DefaultIfEmpty()
                        join spgr in
                            (
                                //专业计划(计划门数、计划学分、必修、限选、任选学分统计)
                                from spct in
                                    (
                                        from sp in SpecialtyPlanRepository.Entities
                                        join ct in CourseTypeSelectCourseTypeRepository.Entities
                                        on sp.CourseTypeID equals ct.CourseTypeID into tempct
                                        from spct in tempct.DefaultIfEmpty()
                                        select new
                                        {
                                            StartSchoolyearID = sp.StartSchoolyearID,
                                            SpecialtyID = sp.SpecialtyID,
                                            CourseTypeID = sp.CourseTypeID,
                                            Credit = sp.EM_SpecialtyPlanTeachingSetting.Credit ?? 0,
                                            IsRequired = spct.IsRequired ?? true,
                                            IsOptionalCourse = spct.IsOptionalCourse ?? false,
                                            IsFreeSelectionCourse = spct.IsFreeSelectionCourse ?? false,
                                        }
                                    )
                                group spct by new { spct.StartSchoolyearID, spct.SpecialtyID } into g
                                select new
                                {
                                    StartSchoolyearID = g.Key.StartSchoolyearID,
                                    SpecialtyID = g.Key.SpecialtyID,
                                    PlanCourseCount = g.Count(),
                                    SpecialtyRequireCreditTotal = g.Sum(x => x.IsRequired ? x.Credit : 0),
                                    OptionalCreditTotal = g.Sum(x => x.IsOptionalCourse ? x.Credit : 0),
                                    FreeSelectionCreditTotal = g.Sum(x => x.IsFreeSelectionCourse ? x.Credit : 0),
                                    SpecialtyCreditTotal = g.Sum(x => x.Credit)
                                }
                            )
                        on new { StartSchoolyearID = syenter.SchoolyearID, SpecialtyID = spe.SpecialtyID }
                        equals new { StartSchoolyearID = spgr.StartSchoolyearID.Value, SpecialtyID = spgr.SpecialtyID.Value } into tempspgr
                        from gsspgr in tempspgr.DefaultIfEmpty()
                        join epgr in
                            (
                                //执行计划(执行门数、执行学分)
                                from ep in ExecutablePlanRepository.GetList(expExecutablePlan)
                                join ct in CourseTypeSelectCourseTypeRepository.Entities
                                on ep.CourseTypeID equals ct.CourseTypeID into tempct
                                from epct in tempct.DefaultIfEmpty()
                                group ep by ep.GrademajorID into g
                                select new
                                {
                                    GrademajorID = g.Key,
                                    ExecCourseCount = g.Count(),
                                    ExecutableCreditTotal = g.Sum(x => x.EM_ExecutablePlanTeachingSetting.Credit ?? 0)
                                }
                            )
                        on gr.GrademajorID equals epgr.GrademajorID into tempepgr
                        from gsepgr in tempepgr.DefaultIfEmpty()
                        select new GraduationStandardView
                        {
                            GraduationStandardID = gs.GraduationStandardID,
                            GrademajorID = gs.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            FacultymajorID = fa.FacultymajorID,
                            FacultymajorNo = fa.Code,
                            FacultymajorName = fa.Name,
                            CollegeID = fa.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            CampusID = col.CampusID,
                            CampusNo = cam.No,
                            CampusName = cam.Name,
                            SchoolyearID = gr.GradeID,
                            SchoolcodeID = gr.SemesterID,
                            EnteringSchoolYearID = syenter.SchoolyearID,
                            EnteringSchoolYearCode = syenter.Code,
                            GraduatingSemesterID = gr.CF_Schoolyear.SchoolyearID,
                            GraduatingSemesterCode = gr.CF_Schoolyear.Code,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            StudentCount = gsstugr.StudentCount == null ? 0 : gsstugr.StudentCount,
                            PlanCourseCount = gsspgr.PlanCourseCount == null ? 0 : gsspgr.PlanCourseCount,
                            SpecialtyRequireCreditTotal = gsspgr.SpecialtyRequireCreditTotal == null ? 0 : gsspgr.SpecialtyRequireCreditTotal,
                            OptionalCreditTotal = gsspgr.OptionalCreditTotal == null ? 0 : gsspgr.OptionalCreditTotal,
                            FreeSelectionCreditTotal = gsspgr.FreeSelectionCreditTotal == null ? 0 : gsspgr.FreeSelectionCreditTotal,
                            SpecialtyCreditTotal = gsspgr.SpecialtyCreditTotal == null ? 0 : gsspgr.SpecialtyCreditTotal,
                            ExecCourseCount = gsepgr.ExecCourseCount == null ? 0 : gsepgr.ExecCourseCount,
                            ExecutableCreditTotal = gsepgr.ExecutableCreditTotal == null ? 0 : gsepgr.ExecutableCreditTotal,
                            RequireCourseCount = gs.RequireCourseCount,
                            GraduationCredit = gs.GraduationCredit,
                            RecordStatus = gs.RecordStatus,
                            CreateUserID = gs.CreateUserID,
                            CreateTime = gs.CreateTime,
                            ModifyUserID = gs.ModifyUserID,
                            ModifyTime = gs.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询毕业标准对应的专业计划信息
        /// 此为成教版本
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public override IQueryable<SpecialtyPlanView> GetSpecialtyPlanViewQueryable(Expression<Func<ER_GraduationStandard, bool>> exp)
        {
            var query = from gs in GraduationStandardRepository.GetList(exp)
                        join gr in GrademajorRepository.Entities
                        on gs.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 spe in SpecialtyRepository.Entities
                        on new { fa.StandardID, fa.EducationID, fa.LearningformID, fa.LearnSystem }
                        equals new { spe.StandardID, spe.EducationID, spe.LearningformID, spe.LearnSystem }
                        join sye in SchoolyearRepository.Entities
                        on new { SchoolyearID = gr.GradeID.Value, SchoolcodeID = gr.SemesterID.Value }
                        equals new { SchoolyearID = sye.Years, SchoolcodeID = sye.SchoolcodeID } into tempsyenter
                        from syenter in tempsyenter.DefaultIfEmpty()
                        join sp in SpecialtyPlanRepository.Entities
                        on new { StartSchoolyearID = syenter.SchoolyearID, SpecialtyID = spe.SpecialtyID }
                        equals new { StartSchoolyearID = sp.StartSchoolyearID.Value, SpecialtyID = sp.SpecialtyID.Value }
                        join sy in SchoolyearRepository.Entities
                        on sp.SchoolyearID equals sy.SchoolyearID
                        join cou in CoursematerialRepository.Entities
                        on sp.CoursematerialID equals cou.CoursematerialID
                        join depcou in DepartmentRepository.Entities
                        on sp.DepartmentID equals depcou.DepartmentID into tempdepcou
                        from spdepcou in tempdepcou.DefaultIfEmpty()
                        select new SpecialtyPlanView
                        {
                            SpecialtyPlanID = sp.SpecialtyPlanID,
                            SchoolyearID = sp.SchoolyearID,
                            SchoolyearCode = sy.Code,
                            GrademajorID = gr.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            CollegeID = fa.CollegeID,
                            CollegeCode = col.No,
                            CollegeName = col.Name,
                            CoursematerialID = sp.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            CourseStructureID = sp.CourseStructureID,
                            CourseCategoryID = sp.CourseCategoryID,
                            CourseTypeID = sp.CourseTypeID,
                            CourseQualityID = sp.CourseQualityID,
                            ResultTypeID = sp.ResultTypeID,
                            PracticeTypeID = sp.PracticeTypeID,
                            ExaminationModeID = sp.ExaminationModeID,
                            TeachinglanguageID = sp.TeachinglanguageID,
                            CourseFineID = sp.CourseFineID,
                            IsSpecialtycore = sp.IsSpecialtycore ?? false,
                            IsCooperation = sp.IsCooperation ?? false,
                            IsRequired = sp.IsRequired ?? false,
                            IsElective = sp.IsElective ?? false,
                            IsNetworkCourse = sp.IsNetworkCourse ?? false,
                            IsMainCourse = sp.IsMainCourse ?? false,
                            Credit = sp.EM_SpecialtyPlanTeachingSetting.Credit ?? 0,
                            TheoryCourse = sp.EM_SpecialtyPlanTeachingSetting.TheoryCourse ?? 0,
                            Practicehours = sp.EM_SpecialtyPlanTeachingSetting.Practicehours ?? 0,
                            Trialhours = sp.EM_SpecialtyPlanTeachingSetting.Trialhours ?? 0,
                            Totalhours = (sp.EM_SpecialtyPlanTeachingSetting.TheoryCourse ?? 0) + (sp.EM_SpecialtyPlanTeachingSetting.Practicehours ?? 0),
                            SchoolyearNumID = (sy.Value - ((gr.GradeID * 2 - 1) + gr.SemesterID - 1)) / 2 + 1,
                            SchoolcodeID = sy.SchoolcodeID,
                            StarttermID = sy.Value - ((gr.GradeID * 2 - 1) + gr.SemesterID - 1) + 1,
                            DepartmentID = sp.DepartmentID,
                            DepartmentCode = spdepcou.No,
                            DepartmentName = spdepcou.Name,
                            HandleModeID = sp.HandleModeID,
                            Remark = sp.Remark,
                            RecordStatus = sp.RecordStatus,
                            CreateUserID = sp.CreateUserID,
                            CreateTime = sp.CreateTime,
                            ModifyUserID = sp.ModifyUserID,
                            ModifyTime = sp.ModifyTime
                        };

            return query;
        }

    }
}