JW/ServiceLogic/EMIS.ExtensionLogic.Adult/CommonDataLogic/GraduationManage/GraduationApplyDAL.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.GraduationManage.GraduationManage;
using EMIS.ViewModel.CultureplanManage.PlanManagement;

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

        /// <summary>
        /// 查询对应的毕业申请信息View（需查询相关的信息，如：专业计划、执行计划、最终成绩、毕业设计、欠费信息等）
        /// 此为成教版本
        /// </summary>
        /// <param name="expGraduationApply"></param>
        /// <param name="expExecutablePlan"></param>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public override IQueryable<GraduationApplyView> GetGraduationApplyViewQueryable(Expression<Func<ER_GraduationApply, bool>> expGraduationApply,
                Expression<Func<EM_ExecutablePlan, bool>> expExecutablePlan, Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from ga in GraduationApplyRepository.GetList(expGraduationApply)
                        join grsy in SchoolyearRepository.Entities
                        on ga.GraduatingSemesterID equals grsy.SchoolyearID
                        join apsy in SchoolyearRepository.Entities
                        on ga.ApplySchoolyearID equals apsy.SchoolyearID
                        join stu in StudentRepository.GetList(expStudent)
                        on ga.UserID equals stu.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 ets in EducationTypeSettingRepository.Entities
                        on fa.EducationID equals ets.EducationID
                        join col in CollegeRepository.Entities
                        on fa.CollegeID equals col.CollegeID
                        join cam in CampusRepository.Entities
                        on col.CampusID equals cam.CampusID
                        join us in UserRepository.Entities
                        on stu.UserID equals us.UserID
                        join gs in GraduationStandardRepository.Entities
                        on gr.GrademajorID equals gs.GrademajorID into tempgs
                        from gags in tempgs.DefaultIfEmpty()
                        join assistant in UserRepository.Entities
                        on cl.UserID equals assistant.UserID into tempassistant
                        from usassistant in tempassistant.DefaultIfEmpty()
                        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 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 spgr in
                            (
                                //专业计划(计划门数)
                                from sp in SpecialtyPlanRepository.Entities
                                group sp by new { sp.StartSchoolyearID, sp.SpecialtyID } into g
                                select new
                                {
                                    StartSchoolyearID = g.Key.StartSchoolyearID,
                                    SpecialtyID = g.Key.SpecialtyID,
                                    PlanCourseCount = g.Count()
                                }
                            )
                        on new { StartSchoolyearID = syenter.SchoolyearID, SpecialtyID = spe.SpecialtyID }
                        equals new { StartSchoolyearID = spgr.StartSchoolyearID.Value, SpecialtyID = spgr.SpecialtyID.Value } into tempspgr
                        from gaspgr in tempspgr.DefaultIfEmpty()
                        join epgr in
                            (
                                //执行计划(执行门数)
                                from ep in ExecutablePlanRepository.GetList(expExecutablePlan)
                                group ep by ep.GrademajorID into g
                                select new
                                {
                                    GrademajorID = g.Key,
                                    ExecCourseCount = g.Count()
                                }
                            )
                        on gr.GrademajorID equals epgr.GrademajorID into tempepgr
                        from gaepgr in tempepgr.DefaultIfEmpty()
                        join fsgr in
                            (
                                //学生最终成绩(已修门数)
                                from fs in FinallyScoreRepository.Entities
                                group fs by fs.UserID into g
                                select new
                                {
                                    UserID = g.Key,
                                    FinallyScoreCount = g.Count()
                                }
                            )
                        on stu.UserID equals fsgr.UserID into tempfsgr
                        from gafsgr in tempfsgr.DefaultIfEmpty()
                        join gdcgr in
                            (
                                //毕业设计课程(毕设成绩)
                                //根据课程信息、课程类型进行内联(学分、学时暂时不考虑)
                                from gdc in GraduateDesignCoursematerialRepository.Entities
                                join cou in CoursematerialRepository.Entities
                                on gdc.CoursematerialID equals cou.CoursematerialID
                                join finsco in FinallyScoreRepository.Entities
                                on new { CoursematerialID = gdc.CoursematerialID, CourseTypeID = gdc.CourseTypeID }
                                equals new { CoursematerialID = finsco.CoursematerialID, CourseTypeID = finsco.CourseTypeID }
                                join rtd in ResultTypeDetailRepository.Entities
                                on gdc.ResultTypeID equals rtd.ResultTypeID into temprtd
                                from gartd in temprtd.DefaultIfEmpty()
                                where
                                    //大于等于且小于等于
                                (
                                    finsco.TotalScore >= gartd.MinScore
                                    && finsco.TotalScore <= gartd.MaxScore
                                    && gartd.MinScoreOperator == "<="
                                    && gartd.MaxScoreOperator == "<="
                                ) ||
                                    //大于等于且小于
                                (
                                    finsco.TotalScore >= gartd.MinScore
                                    && finsco.TotalScore < gartd.MaxScore
                                    && gartd.MinScoreOperator == "<="
                                    && gartd.MaxScoreOperator == "<"
                                ) ||
                                    //大于且小于
                                (
                                    finsco.TotalScore > gartd.MinScore
                                    && finsco.TotalScore < gartd.MaxScore
                                    && gartd.MinScoreOperator == "<"
                                    && gartd.MaxScoreOperator == "<"
                                ) ||
                                    //大于且小于等于
                                (
                                    finsco.TotalScore > gartd.MinScore
                                    && finsco.TotalScore <= gartd.MaxScore
                                    && gartd.MinScoreOperator == "<"
                                    && gartd.MaxScoreOperator == "<="
                                )
                                select new
                                {
                                    UserID = finsco.UserID,
                                    GraduateDesignCoursematerialID = gdc.GraduateDesignCoursematerialID,
                                    GraduateDesignCourseID = gdc.CoursematerialID,
                                    GraduateDesignCourseCode = cou.CourseCode,
                                    GraduateDesignCourseName = cou.CourseName,
                                    GradDesignScoreLevel = gartd.Name,
                                    GradDesignScore = finsco.TotalScore
                                }
                            )
                        on stu.UserID equals gdcgr.UserID into tempgdc
                        from gagdc in tempgdc.DefaultIfEmpty()
                        join scgr in
                            (
                                //缴费信息(欠费金额)
                                from sc in StudentChargeRepository.Entities
                                join scpgr in
                                    (
                                        from scp in StudentChargePaymentRepository.Entities
                                        group scp by scp.StudentChargeID into g
                                        select new
                                        {
                                            StudentChargeID = g.Key,
                                            PaymentAmount = g.Sum(x => x.Amount)
                                        }
                                    )
                                on sc.StudentChargeID equals scpgr.StudentChargeID into tempscp
                                from scscp in tempscp.DefaultIfEmpty()
                                group new { sc, scscp } by sc.UserID into g
                                select new
                                {
                                    UserID = g.Key,
                                    //Amount = g.Sum(x => x.sc.Amount ?? 0),
                                    //ChargeAmount = g.Sum(x => (x.sc.ActualAmount ?? 0) - (x.sc.Amount ?? 0)),
                                    //ActualAmount = g.Sum(x => x.sc.ActualAmount ?? 0),
                                    //PaymentAmount = g.Sum(x => x.scscp.PaymentAmount ?? 0),
                                    ArrearAmount = g.Sum(x => x.sc.ActualAmount ?? 0) - g.Sum(x => x.scscp.PaymentAmount ?? 0)
                                }
                            )
                        on stu.UserID equals scgr.UserID into tempscgr
                        from gascgr in tempscgr.DefaultIfEmpty()
                        select new GraduationApplyView
                        {
                            GraduationApplyID = ga.GraduationApplyID,
                            UserID = ga.UserID,
                            StudentNo = us.LoginID,
                            UserName = us.Name,
                            ExamineeNum = stu.CF_Recruitstudents.ExamineeNum,
                            IDNumber = stu.IDNumber,
                            SexID = stu.SexID,
                            BirthDate = stu.BirthDate,
                            NationID = stu.NationID,
                            PoliticsID = stu.PoliticsID,
                            EntranceDate = stu.CF_Recruitstudents.EntranceDate,
                            ZIPCode = stu.CF_StudentContact.ZIPCode,
                            Telephone = stu.CF_StudentContact.Telephone,
                            Mobile = stu.CF_StudentContact.Mobile,
                            Address = stu.CF_StudentContact.Address,
                            StudentTypeID = stu.StudentType,
                            InSchoolStatusID = stu.InSchoolStatusID,
                            StudentStatusID = stu.StudentStatus,
                            ClassID = stu.ClassmajorID,
                            ClassNo = cl.No,
                            ClassName = cl.Name,
                            GrademajorID = cl.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,
                            UniversityID = cam.UniversityID,
                            UniversityCode = cam.CF_University.Code,
                            UniversityName = cam.CF_University.Name,
                            SchoolTypeID = cam.CF_University.CF_UniversityProfile.SchoolTypeID,
                            SchoolyearID = gr.GradeID,
                            EnteringSchoolYearID = syenter.SchoolyearID,
                            EnteringSchoolYearCode = syenter.Code,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            EducationCode = ets.EducationCode,
                            EducationTypeID = ets.EducationTypeID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            ScienceclassID = fa.ScienceclassID,
                            GraduationTypeID = ga.GraduationTypeID,
                            GraduatingSemesterID = ga.GraduatingSemesterID,
                            GraduatingSemesterCode = grsy.Code,
                            GraduatingSemesterValue = grsy.Value,
                            GraduationYear = grsy.Years,
                            ApplySchoolyearID = ga.ApplySchoolyearID,
                            ApplySchoolyearCode = apsy.Code,
                            GraduationConditionPackageID = ga.GraduationConditionPackageID,
                            Title = ga.ER_GraduationConditionPackage.Title,
                            BeforeInSchoolStatusID = ga.BeforeInSchoolStatusID,
                            BeforeStudentStatusID = ga.BeforeStudentStatusID,
                            PlanCourseCount = gaspgr.PlanCourseCount == null ? 0 : gaspgr.PlanCourseCount,
                            ExecCourseCount = gaepgr.ExecCourseCount == null ? 0 : gaepgr.ExecCourseCount,
                            FinallyScoreCount = gafsgr.FinallyScoreCount == null ? 0 : gafsgr.FinallyScoreCount,
                            GraduateDesignCoursematerialID = gagdc.GraduateDesignCoursematerialID,
                            GraduateDesignCourseID = gagdc.GraduateDesignCourseID,
                            GraduateDesignCourseCode = gagdc.GraduateDesignCourseCode,
                            GraduateDesignCourseName = gagdc.GraduateDesignCourseName,
                            GradDesignScoreLevel = gagdc.GradDesignScoreLevel == null ? "未录入" : gagdc.GradDesignScoreLevel,
                            GradDesignScore = gagdc.GradDesignScore,
                            ArrearAmount = gascgr.ArrearAmount == null ? 0 : gascgr.ArrearAmount,
                            RequireCourseCount = gags.RequireCourseCount == null ? 0 : gags.RequireCourseCount,
                            GraduationCredit = gags.GraduationCredit == null ? 0 : gags.GraduationCredit,
                            ApprovalStatus = ga.ApprovalStatus,
                            GraduationResult = ga.GraduationResult,
                            ApprovalResult = ga.ApprovalResult,
                            GraduateCardNo = stu.GraduateCardNo,
                            ReplaceGraduateNo = stu.ReplaceGraduateNo,
                            RecordStatus = ga.RecordStatus,
                            Remark = ga.Remark,
                            CreateUserID = ga.CreateUserID,
                            CreateTime = ga.CreateTime,
                            ModifyUserID = ga.ModifyUserID,
                            ModifyTime = ga.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询毕业申请对应的专业计划信息
        /// 此为成教版本
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public override IQueryable<SpecialtyPlanView> GetSpecialtyPlanViewQueryable(Expression<Func<ER_GraduationApply, bool>> exp)
        {
            var query = from ga in GraduationApplyRepository.GetList(exp)
                        join stu in StudentRepository.Entities
                        on ga.UserID equals stu.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 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 = sp.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;
        }

    }
}