JW/EMiS.DataLogic/EvaluationManage/EvaluationSetting/EvaluationGradeControlDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using EMIS.Entities;
using EMIS.ViewModel.EvaluationManage.EvaluationSetting;
using EMIS.ViewModel.UniversityManage.SpecialtyClassManage;
using EMIS.DataLogic.Repositories;

namespace EMIS.DataLogic.EvaluationManage.EvaluationSetting
{
    public class EvaluationGradeControlDAL
    {
        public EvaluationGradeControlRepository EvaluationGradeControlRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public CampusRepository CampusRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }
        public UserRepository UserRepository { get; set; }

        /// <summary>
        /// 查询对应的年级专业评价控制信息EvaluationGradeControlView
        /// </summary>
        /// <param name="expEvaluationGradeControl"></param>
        /// <returns></returns>
        public IQueryable<EvaluationGradeControlView> GetEvaluationGradeControlViewQueryable(Expression<Func<EM_EvaluationGradeControl, bool>> expEvaluationGradeControl)
        {
            var query = from grct in EvaluationGradeControlRepository.GetList(expEvaluationGradeControl)
                        join sy in SchoolyearRepository.Entities
                        on grct.SchoolyearID equals sy.SchoolyearID
                        join gr in GrademajorRepository.Entities
                        on grct.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 stsy in SchoolyearRepository.Entities
                        on new { GradeID = gr.GradeID.Value, SemesterID = gr.SemesterID.Value }
                        equals new { GradeID = stsy.Years, SemesterID = stsy.SchoolcodeID } into tempstsy
                        from startsy in tempstsy.DefaultIfEmpty()
                        join grsy in SchoolyearRepository.Entities
                        on gr.GraduateSchoolyearID equals grsy.SchoolyearID into tempgrsy
                        from grasy in tempgrsy.DefaultIfEmpty()
                        select new EvaluationGradeControlView
                        {
                            EvaluationGradeControlID = grct.EvaluationGradeControlID,
                            SchoolyearID = grct.SchoolyearID,
                            SchoolyearValue = sy.Value,
                            SchoolyearCode = sy.Code,
                            GrademajorID = grct.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,
                            CampusID = col.CampusID,
                            CampusNo = cam.No,
                            CampusName = cam.Name,
                            GradeID = gr.GradeID,
                            SemesterID = gr.SemesterID,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            StartSchoolyearID = startsy.SchoolyearID,
                            StartSchoolyearValue = startsy.Value,
                            StartSchoolyearCode = startsy.Code,
                            GraduateSchoolyearID = gr.GraduateSchoolyearID,
                            GraduateSchoolyearValue = grasy.Value,
                            GraduateSchoolyearCode = grasy.Code,
                            Number = grct.Number,
                            StartTime = grct.StartTime,
                            EndTime = grct.EndTime,
                            RecordStatus = grct.RecordStatus,
                            CreateUserID = grct.CreateUserID,
                            CreateTime = grct.CreateTime,
                            ModifyUserID = grct.ModifyUserID,
                            ModifyTime = grct.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的年级专业评价控制信息EvaluationGradeControlView(统计对应的班级个数、学生人数)
        /// </summary>
        /// <param name="expEvaluationGradeControl"></param>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<EvaluationGradeControlView> GetEvaluationGradeControlViewQueryable(Expression<Func<EM_EvaluationGradeControl, bool>> expEvaluationGradeControl, Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from grct in EvaluationGradeControlRepository.GetList(expEvaluationGradeControl)
                        join sy in SchoolyearRepository.Entities
                        on grct.SchoolyearID equals sy.SchoolyearID
                        join gr in GrademajorRepository.Entities
                        on grct.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 cl in
                            (
                                from cl in ClassmajorRepository.Entities
                                group cl by cl.GrademajorID into gcl
                                select new
                                {
                                    GrademajorID = gcl.Key,
                                    ClassmajorCount = gcl.Count()
                                }
                            )
                        on gr.GrademajorID equals cl.GrademajorID into tempcl
                        from clgr in tempcl.DefaultIfEmpty()
                        join sc in
                            (
                                from sc in StudentRepository.GetList(expStudent)
                                group sc by sc.CF_Classmajor.CF_Grademajor.GrademajorID into gstu
                                select new
                                {
                                    GrademajorID = gstu.Key,
                                    StudentCount = gstu.Count()
                                }
                            )
                        on gr.GrademajorID equals sc.GrademajorID into tempstu
                        from grstu in tempstu.DefaultIfEmpty()
                        join stsy in SchoolyearRepository.Entities
                        on new { GradeID = gr.GradeID.Value, SemesterID = gr.SemesterID.Value }
                        equals new { GradeID = stsy.Years, SemesterID = stsy.SchoolcodeID } into tempstsy
                        from startsy in tempstsy.DefaultIfEmpty()
                        join grsy in SchoolyearRepository.Entities
                        on gr.GraduateSchoolyearID equals grsy.SchoolyearID into tempgrsy
                        from grasy in tempgrsy.DefaultIfEmpty()
                        join usc in UserRepository.Entities
                        on grct.CreateUserID equals usc.UserID into tempusc
                        from uscu in tempusc.DefaultIfEmpty()
                        join usm in UserRepository.Entities
                        on grct.ModifyUserID equals usm.UserID into tempusm
                        from usmu in tempusm.DefaultIfEmpty()
                        select new EvaluationGradeControlView
                        {
                            EvaluationGradeControlID = grct.EvaluationGradeControlID,
                            SchoolyearID = grct.SchoolyearID,
                            SchoolyearValue = sy.Value,
                            SchoolyearCode = sy.Code,
                            GrademajorID = grct.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,
                            CampusID = col.CampusID,
                            CampusNo = cam.No,
                            CampusName = cam.Name,
                            GradeID = gr.GradeID,
                            SemesterID = gr.SemesterID,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            StartSchoolyearID = startsy.SchoolyearID,
                            StartSchoolyearValue = startsy.Value,
                            StartSchoolyearCode = startsy.Code,
                            GraduateSchoolyearID = gr.GraduateSchoolyearID,
                            GraduateSchoolyearValue = grasy.Value,
                            GraduateSchoolyearCode = grasy.Code,
                            ClassmajorCount = clgr.ClassmajorCount == null ? 0 : clgr.ClassmajorCount,
                            StudentCount = grstu.StudentCount == null ? 0 : grstu.StudentCount,
                            Number = grct.Number,
                            StartTime = grct.StartTime,
                            EndTime = grct.EndTime,
                            RecordStatus = grct.RecordStatus,
                            CreateUserID = grct.CreateUserID,
                            CreateUserName = uscu.Name,
                            CreateTime = grct.CreateTime,
                            ModifyUserID = grct.ModifyUserID,
                            ModifyUserName = usmu.Name,
                            ModifyTime = grct.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询年级专业评价控制中未新增的年级专业信息GrademajorView
        /// </summary>
        /// <param name="expEvaluationGradeControl"></param>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<GrademajorView> GetGrademajorViewNoAddQueryable(Expression<Func<EM_EvaluationGradeControl, bool>> expEvaluationGradeControl, Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from gr in GrademajorRepository.Entities
                        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 sc in
                            (
                                from sc in StudentRepository.GetList(expStudent)
                                group sc by sc.CF_Classmajor.CF_Grademajor.GrademajorID into gstu
                                select new
                                {
                                    GrademajorID = gstu.Key,
                                    StudentCount = gstu.Count()
                                }
                            )
                        on gr.GrademajorID equals sc.GrademajorID into tempstu
                        from grstu in tempstu.DefaultIfEmpty()
                        join stsy in SchoolyearRepository.Entities
                        on new { GradeID = gr.GradeID.Value, SemesterID = gr.SemesterID.Value }
                        equals new { GradeID = stsy.Years, SemesterID = stsy.SchoolcodeID } into tempstsy
                        from startsy in tempstsy.DefaultIfEmpty()
                        join grsy in SchoolyearRepository.Entities
                        on gr.GraduateSchoolyearID equals grsy.SchoolyearID into tempgrsy
                        from grasy in tempgrsy.DefaultIfEmpty()
                        join grct in EvaluationGradeControlRepository.GetList(expEvaluationGradeControl)
                        on gr.GrademajorID equals grct.GrademajorID into tempgrct
                        from evgrct in tempgrct.DefaultIfEmpty()
                        where evgrct.EvaluationGradeControlID == null 
                        select new GrademajorView
                        {
                            GrademajorID = gr.GrademajorID,
                            Code = gr.Code,
                            Name = gr.Name,
                            Abbreviation = gr.Abbreviation,
                            FacultymajorID = gr.FacultymajorID,
                            FacultymajorCode = fa.Code,
                            FacultymajorName = fa.Name,
                            CampusID = col.CampusID,
                            CampusNo = cam.No,
                            CampusName = cam.Name,
                            CollegeID = fa.CollegeID,
                            CollegeCode = col.No,
                            CollegeName = col.Name,
                            GradeID = gr.GradeID,
                            SemesterID = gr.SemesterID,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            StartSchoolyearID = startsy.SchoolyearID,
                            StartValue = startsy.Value,
                            StartSchoolyearCode = startsy.Code,
                            GraduateSchoolyearID = gr.GraduateSchoolyearID,
                            GraduateValue = grasy.Value,
                            GraduateSchoolyearCode = grasy.Code,
                            Professional = gr.Professional,
                            StudentCount = grstu.StudentCount == null ? 0 : grstu.StudentCount,
                            Remark = gr.Remark,
                            RecordStatus = gr.RecordStatus,
                            CreateUserID = gr.CreateUserID,
                            CreateTime = gr.CreateTime,
                            ModifyUserID = gr.ModifyUserID,
                            ModifyTime = gr.ModifyTime
                        };

            return query;
        }

    }
}