JW/EMiS.DataLogic/UniversityManage/SpecialtyClassManage/GrademajorDAL.cs

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

namespace EMIS.DataLogic.UniversityManage.SpecialtyClassManage
{
    public class GrademajorDAL
    {
        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 SchoolyearRepository SchoolyearRepository { get; set; }
        public DictionaryItemRepository DictionaryItemRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }
        public UserRepository UserRepository { get; set; }

        /// <summary>
        /// 查询对应的年级专业信息View
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<GrademajorView> GetGrademajorViwQueryable(Expression<Func<CF_Grademajor, bool>> expGrademajor)
        {
            var query = from gr in GrademajorRepository.GetList(expGrademajor)
                        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 gy in SchoolyearRepository.Entities
                        on gr.GraduateSchoolyearID equals gy.SchoolyearID into tempgy
                        from gdsy in tempgy.DefaultIfEmpty()
                        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,
                            GraduateSchoolyearID = gr.GraduateSchoolyearID,
                            GraduateValue = gdsy.Value,
                            GraduateSchoolyearCode = gdsy.Code,
                            Professional = gr.Professional,
                            Remark = gr.Remark,
                            RecordStatus = gr.RecordStatus,
                            CreateUserID = gr.CreateUserID,
                            CreateTime = gr.CreateTime,
                            ModifyUserID = gr.ModifyUserID,
                            ModifyTime = gr.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的年级专业信息View(统计对应的班级个数、学生人数)
        /// </summary>
        /// <param name="expGrademajor"></param>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<GrademajorView> GetGrademajorViwQueryable(Expression<Func<CF_Grademajor, bool>> expGrademajor, Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from gr in GrademajorRepository.GetList(expGrademajor)
                        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()
                        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,
                            GraduateSchoolyearID = gr.GraduateSchoolyearID,
                            GraduateValue = gr.CF_Schoolyear.Value,
                            GraduateSchoolyearCode = gr.CF_Schoolyear.Code,
                            Professional = gr.Professional,
                            ClassmajorCount = clgr.ClassmajorCount == null ? 0 : clgr.ClassmajorCount,
                            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;
        }

        /// <summary>
        /// 查询对应的年级专业信息GrademajorView(根据班级信息)
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<GrademajorView> GetGrademajorViwByClassQueryable(Expression<Func<CF_Classmajor, bool>> expClassmajor)
        {
            var query = from gr in GrademajorRepository.Entities
                        join cl in ClassmajorRepository.GetList(expClassmajor)
                        on gr.GrademajorID equals cl.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
                        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,
                            GraduateSchoolyearID = gr.GraduateSchoolyearID,
                            GraduateValue = gr.CF_Schoolyear.Value,
                            GraduateSchoolyearCode = gr.CF_Schoolyear.Code,
                            Professional = gr.Professional,
                            Remark = gr.Remark,
                            RecordStatus = gr.RecordStatus,
                            CreateUserID = gr.CreateUserID,
                            CreateTime = gr.CreateTime,
                            ModifyUserID = gr.ModifyUserID,
                            ModifyTime = gr.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的班级信息View(根据年级专业信息，统计各班级信息对应的学生人数)
        /// </summary>
        /// <param name="expGrademajor"></param>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<ClassmajorView> GetClassmajorViewQueryable(Expression<Func<CF_Grademajor, bool>> expGrademajor, Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from gr in GrademajorRepository.GetList(expGrademajor)
                        join cl in ClassmajorRepository.Entities
                        on gr.GrademajorID equals cl.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 sc in
                            (
                            from sc in StudentRepository.GetList(expStudent)
                            group sc by sc.CF_Classmajor.ClassmajorID into g
                            select new
                            {
                                ClassmajorID = g.Key,
                                StudentCount = g.Count()
                            })
                        on cl.ClassmajorID equals sc.ClassmajorID into dsc
                        from esc in dsc.DefaultIfEmpty()
                        join c in UserRepository.Entities
                        on cl.UserID equals c.UserID into gu
                        from guser in gu.DefaultIfEmpty()
                        join d in UserRepository.Entities
                        on cl.AssistantUserID equals d.UserID into ga
                        from gassis in ga.DefaultIfEmpty()
                        select new ClassmajorView
                        {
                            ClassmajorID = cl.ClassmajorID,
                            No = cl.No,
                            Name = cl.Name,
                            Abbreviation = cl.Abbreviation,
                            EnglishName = cl.EnglishName,
                            ClassNum = cl.ClassNum,
                            FacultymajorID = gr.FacultymajorID,
                            FacultymajorCode = fa.Code,
                            FacultymajorName = fa.Name,
                            GrademajorID = gr.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.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,
                            GraduateSchoolyearID = gr.GraduateSchoolyearID,
                            GraduateValue = gr.CF_Schoolyear.Value,
                            GraduateSchoolyearCode = gr.CF_Schoolyear.Code,
                            UserID = cl.UserID,
                            UserName = guser.Name,
                            AssistantUserID = cl.AssistantUserID,
                            AssistantUserName = gassis.Name,
                            Fixedclassroom = cl.Fixedclassroom,
                            StudentCount = esc.StudentCount == null ? 0 : esc.StudentCount,
                            Remark = cl.Remark,
                            RecordStatus = cl.RecordStatus,
                            CreateUserID = cl.CreateUserID,
                            CreateTime = cl.CreateTime,
                            ModifyUserID = cl.ModifyUserID,
                            ModifyTime = cl.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询年级专业所对应的学生信息BaseStudentView
        /// </summary>
        /// <param name="expGrademajor"></param>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<StudentBaseView> GetStudentBaseViewQueryable(Expression<Func<CF_Grademajor, bool>> expGrademajor, Expression<Func<CF_Student, bool>> exp)
        {
            var query = from stu in StudentRepository.GetList(exp)
                        join cl in ClassmajorRepository.Entities
                        on stu.ClassmajorID equals cl.ClassmajorID
                        join gr in GrademajorRepository.GetList(expGrademajor)
                        on cl.GrademajorID equals gr.GrademajorID
                        join fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        join us in UserRepository.Entities
                        on stu.UserID equals us.UserID
                        select new StudentBaseView
                        {
                            UserID = stu.UserID,
                            LoginID = us.LoginID,
                            UserName = us.Name,
                            SexID = stu.SexID,
                            IDNumber = stu.IDNumber,
                            StudentStatus = stu.StudentStatus,
                            InSchoolStatusID = stu.InSchoolStatusID,
                            ClassmajorID = stu.ClassmajorID,
                            ClassmajorNo = cl.No,
                            ClassmajorName = cl.Name,
                            ClassNum = cl.ClassNum == null ? 1 : cl.ClassNum,
                            GradeID = gr.GradeID,
                            SemesterID = gr.SemesterID,
                            GrademajorID = cl.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            FacultymajorID = gr.FacultymajorID,
                            FacultymajorCode = fa.Code,
                            FacultymajorName = fa.Name
                        };

            return query;
        }

        /// <summary>
        /// 查询年级专业对应的专业名称信息FacultymajorView(去重)
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<FacultymajorView> GetStandardViewQueryable(Expression<Func<CF_Grademajor, bool>> exp)
        {
            var query = from gr in GrademajorRepository.GetList(exp)
                        join fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        join dtstan in DictionaryItemRepository.GetList(x => x.DictionaryCode == DictionaryItem.CF_Standard.ToString())
                        on fa.StandardID equals dtstan.Value
                        select new FacultymajorView
                        {
                            StandardID = fa.StandardID,
                            StandardName = dtstan.Name
                        };

            return query.Distinct();
        }

        /// <summary>
        /// 查询年级专业对应的年级信息GrademajorView(去重)
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<GrademajorView> GetGradeViewQueryable(Expression<Func<CF_Grademajor, bool>> exp)
        {
            var query = from gr in GrademajorRepository.GetList(exp)
                        select new GrademajorView
                        {
                            GradeID = gr.GradeID
                        };

            return query.Distinct();
        }

    }
}