JW/EMiS.DataLogic/GraduationManage/GraduationRequirement/GraduationCourseDAL.cs

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

namespace EMIS.DataLogic.GraduationManage.GraduationRequirement
{
    public class GraduationCourseDAL
    {
        public GraduationCourseRepository GraduationCourseRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public CoursematerialRepository CoursematerialRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public SpecialtyPlanRepository SpecialtyPlanRepository { get; set; }

        /// <summary>
        /// 查询对应的毕业课程信息View
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<GraduationCourseView> GetGraduationCourseQueryable(Expression<Func<ER_GraduationCourse, bool>> exp)
        {
            var query = from gc in GraduationCourseRepository.GetList(exp)
                        join cou in CoursematerialRepository.Entities
                        on gc.CoursematerialID equals cou.CoursematerialID
                        join gr in GrademajorRepository.Entities
                        on gc.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 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()
                        select new GraduationCourseView
                        {
                            GraduationCourseID = gc.GraduationCourseID,
                            GrademajorID = gc.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            FacultymajorID = gr.FacultymajorID,
                            FacultymajorNo = fa.Code,
                            FacultymajorName = fa.Name,
                            CollegeID = fa.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.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,
                            CoursematerialID = gc.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            CourseTypeID = gc.CourseTypeID,
                            CourseCredit = gc.CourseCredit,
                            RecordStatus = gc.RecordStatus,
                            CreateUserID = gc.CreateUserID,
                            CreateTime = gc.CreateTime,
                            ModifyUserID = gc.ModifyUserID,
                            ModifyTime = gc.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的专业计划信息View（专业计划左连毕业课程，排除对应的毕业课程专业计划信息）
        /// </summary>
        /// <param name="expSpecialtyPlan"></param>
        /// <param name="expGrademajor"></param>
        /// <returns></returns>
        public IQueryable<GradSpecialtyPlanView> GetGradSpecialtyPlanQueryable(Expression<Func<EM_SpecialtyPlan, bool>> expSpecialtyPlan,
                Expression<Func<CF_Grademajor, bool>> expGrademajor)
        {
            var query = from sp in SpecialtyPlanRepository.GetList(expSpecialtyPlan)
                        join gr in GrademajorRepository.GetList(expGrademajor)
                        on sp.GrademajorID equals gr.GrademajorID
                        join sy in SchoolyearRepository.Entities
                        on sp.SchoolyearID equals sy.SchoolyearID
                        join cou in CoursematerialRepository.Entities
                        on sp.CoursematerialID equals cou.CoursematerialID
                        join fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        join col in CollegeRepository.Entities
                        on fa.CollegeID equals col.CollegeID
                        join gc in GraduationCourseRepository.Entities
                        on
                        new { GrademajorID = sp.GrademajorID, CoursematerialID = sp.CoursematerialID, CourseTypeID = sp.CourseTypeID, CourseCredit = sp.EM_SpecialtyPlanTeachingSetting.Credit }
                        equals
                        new { GrademajorID = gc.GrademajorID, CoursematerialID = gc.CoursematerialID, CourseTypeID = gc.CourseTypeID, CourseCredit = gc.CourseCredit }
                        into tempgc
                        from spgc in tempgc.DefaultIfEmpty()
                        where spgc.GraduationCourseID == null
                        select new GradSpecialtyPlanView
                        {
                            SpecialtyPlanID = sp.SpecialtyPlanID,
                            GraduationCourseID = spgc.GraduationCourseID,
                            GrademajorID = gr.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            FacultymajorID = gr.FacultymajorID,
                            FacultymajorNo = fa.Code,
                            FacultymajorName = fa.Name,
                            CollegeID = fa.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            GraduatingSemesterID = gr.CF_Schoolyear.SchoolyearID,
                            GraduatingSemesterCode = gr.CF_Schoolyear.Code,
                            GradeID = gr.GradeID,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            CoursematerialID = sp.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            CourseTypeID = sp.CourseTypeID,
                            Credit = sp.EM_SpecialtyPlanTeachingSetting.Credit,
                            TheoryCourse = sp.EM_SpecialtyPlanTeachingSetting.TheoryCourse,
                            Practicehours = sp.EM_SpecialtyPlanTeachingSetting.Practicehours,
                            Trialhours = sp.EM_SpecialtyPlanTeachingSetting.Trialhours,
                            DepartmentID = sp.DepartmentID,
                            DepartmentNo = sp.CF_Department.No,
                            DepartmentName = sp.CF_Department.Name,
                            SchoolyearID = sp.SchoolyearID,
                            SchoolyearCode = sy.Code,
                            Value = sy.Value,
                            SchoolyearNumID = (sp.CF_Schoolyear.Value - ((gr.GradeID * 2 - 1) + gr.SemesterID - 1)) / 2 + 1,
                            SchoolcodeID = sp.CF_Schoolyear.SchoolcodeID,
                            StarttermID = sp.CF_Schoolyear.Value - ((gr.GradeID * 2 - 1) + gr.SemesterID - 1) + 1
                        };

            return query;
        }

    }
}