LIOT/EMiS.DataLogic/UniversityInformationManage/Specialtyclass/GrademajorDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EMIS.DataLogic.Repositories;
using EMIS.ViewModel.Specialtyclass;
using System.Linq.Expressions;
using EMIS.Entities;
using EMIS.ViewModel.Students;
using EMIS.ViewModel;
using EMIS.ViewModel.Cache;
using EMIS.ViewModel.UniversityInformationManage.Specialtyclass;

namespace EMIS.DataLogic.Common.Specialtyclass
{
    public class GrademajorDAL
    {
        public GrademajorRepository GrademajorRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { 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)
                        select new GrademajorView
                        {
                            GrademajorID = gr.GrademajorID,
                            Code = gr.Code,
                            Name = gr.Name,
                            Abbreviation = gr.Abbreviation,
                            FacultymajorID = gr.CF_Facultymajor.FacultymajorID,
                            FacultymajorCode = gr.CF_Facultymajor.Code,
                            FacultymajorName = gr.CF_Facultymajor.Name,
                            CampusID = gr.CF_Facultymajor.CF_College.CampusID,
                            CampusNo = gr.CF_Facultymajor.CF_College.CF_Campus.No,
                            CampusName = gr.CF_Facultymajor.CF_College.CF_Campus.Name,
                            CollegeID = gr.CF_Facultymajor.CollegeID,
                            CollegeCode = gr.CF_Facultymajor.CF_College.No,
                            CollegeName = gr.CF_Facultymajor.CF_College.Name,
                            SchoolyearID = gr.SchoolyearID,
                            SchoolcodeID = gr.SchoolcodeID,
                            StandardID = gr.CF_Facultymajor.StandardID,
                            EducationID = gr.CF_Facultymajor.EducationID,
                            LearningformID = gr.CF_Facultymajor.LearningformID,
                            LearnSystem = gr.CF_Facultymajor.LearnSystem,
                            GraduatingSemesterID = gr.GraduatingSemesterID,
                            Value = gr.CF_Schoolyear.Value,
                            GraduatingSemesterCode = gr.CF_Schoolyear.Code,
                            Professional = gr.Professional,
                            SchoolAreaID = gr.SchoolAreaID,
                            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 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.CF_Facultymajor.FacultymajorID,
                            FacultymajorCode = gr.CF_Facultymajor.Code,
                            FacultymajorName = gr.CF_Facultymajor.Name,
                            CampusID = gr.CF_Facultymajor.CF_College.CampusID,
                            CampusNo = gr.CF_Facultymajor.CF_College.CF_Campus.No,
                            CampusName = gr.CF_Facultymajor.CF_College.CF_Campus.Name,
                            CollegeID = gr.CF_Facultymajor.CollegeID,
                            CollegeCode = gr.CF_Facultymajor.CF_College.No,
                            CollegeName = gr.CF_Facultymajor.CF_College.Name,
                            SchoolyearID = gr.SchoolyearID,
                            SchoolcodeID = gr.SchoolcodeID,
                            StandardID = gr.CF_Facultymajor.StandardID,
                            EducationID = gr.CF_Facultymajor.EducationID,
                            LearningformID = gr.CF_Facultymajor.LearningformID,
                            LearnSystem = gr.CF_Facultymajor.LearnSystem,
                            GraduatingSemesterID = gr.GraduatingSemesterID,
                            Value = gr.CF_Schoolyear.Value,
                            GraduatingSemesterCode = gr.CF_Schoolyear.Code,
                            Professional = gr.Professional,
                            ClassmajorCount = clgr.ClassmajorCount == null ? 0 : clgr.ClassmajorCount,
                            StudentCount = grstu.StudentCount == null ? 0 : grstu.StudentCount,
                            SchoolAreaID = gr.SchoolAreaID,
                            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
                        select new GrademajorView
                        {
                            GrademajorID = gr.GrademajorID,
                            Code = gr.Code,
                            Name = gr.Name,
                            Abbreviation = gr.Abbreviation,
                            FacultymajorID = gr.CF_Facultymajor.FacultymajorID,
                            FacultymajorCode = gr.CF_Facultymajor.Code,
                            FacultymajorName = gr.CF_Facultymajor.Name,
                            CampusID = gr.CF_Facultymajor.CF_College.CampusID,
                            CampusNo = gr.CF_Facultymajor.CF_College.CF_Campus.No,
                            CampusName = gr.CF_Facultymajor.CF_College.CF_Campus.Name,
                            CollegeID = gr.CF_Facultymajor.CollegeID,
                            CollegeCode = gr.CF_Facultymajor.CF_College.No,
                            CollegeName = gr.CF_Facultymajor.CF_College.Name,
                            SchoolyearID = gr.SchoolyearID,
                            SchoolcodeID = gr.SchoolcodeID,
                            StandardID = gr.CF_Facultymajor.StandardID,
                            EducationID = gr.CF_Facultymajor.EducationID,
                            LearningformID = gr.CF_Facultymajor.LearningformID,
                            LearnSystem = gr.CF_Facultymajor.LearnSystem,
                            GraduatingSemesterID = gr.GraduatingSemesterID,
                            Value = gr.CF_Schoolyear.Value,
                            GraduatingSemesterCode = gr.CF_Schoolyear.Code,
                            Professional = gr.Professional,
                            SchoolAreaID = gr.SchoolAreaID,
                            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 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 = cl.CF_Grademajor.CF_Facultymajor.FacultymajorID,
                            FacultymajorCode = cl.CF_Grademajor.CF_Facultymajor.Code,
                            FacultymajorName = cl.CF_Grademajor.CF_Facultymajor.Name,
                            GrademajorID = cl.GrademajorID,
                            GrademajorCode = cl.CF_Grademajor.Code,
                            GrademajorName = cl.CF_Grademajor.Name,
                            CampusID = cl.CF_Grademajor.CF_Facultymajor.CF_College.CampusID,
                            CampusNo = cl.CF_Grademajor.CF_Facultymajor.CF_College.CF_Campus.No,
                            CampusName = cl.CF_Grademajor.CF_Facultymajor.CF_College.CF_Campus.Name,
                            CollegeID = cl.CF_Grademajor.CF_Facultymajor.CollegeID,
                            CollegeCode = cl.CF_Grademajor.CF_Facultymajor.CF_College.No,
                            CollegeName = cl.CF_Grademajor.CF_Facultymajor.CF_College.Name,
                            SchoolyearID = cl.CF_Grademajor.SchoolyearID,
                            SchoolcodeID = cl.CF_Grademajor.SchoolcodeID,
                            StandardID = cl.CF_Grademajor.CF_Facultymajor.StandardID,
                            EducationID = cl.CF_Grademajor.CF_Facultymajor.EducationID,
                            LearningformID = cl.CF_Grademajor.CF_Facultymajor.LearningformID,
                            LearnSystem = cl.CF_Grademajor.CF_Facultymajor.LearnSystem,
                            GraduatingSemesterID = cl.CF_Grademajor.GraduatingSemesterID,
                            Value = cl.CF_Grademajor.CF_Schoolyear.Value,
                            GraduatingSemesterCode = cl.CF_Grademajor.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<BaseStudentView> GetBaseStudentViewQueryable(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 BaseStudentView
                        {
                            UserID = stu.UserID,
                            LoginID = us.LoginID,
                            UserName = us.Name,
                            Sex = stu.Sex,
                            IDNumber = stu.IDNumber,
                            StudentStatus = stu.StudentStatus,
                            InSchoolStatusID = stu.InSchoolStatusID,
                            ClassmajorID = stu.ClassmajorID,
                            ClassmajorCode = cl.No,
                            ClassmajorName = cl.Name,
                            ClassNum = cl.ClassNum == null ? 1 : cl.ClassNum,
                            SchoolyearID = gr.SchoolyearID,
                            GrademajorID = cl.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            FacultymajorID = gr.FacultymajorID,
                            FacultymajorCode = fa.Code,
                            FacultymajorName = fa.Name
                        };

            return query;
        }

        /// <summary>
        /// 查询年级专业对应的专业信息StandardView(去重)
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<StandardView> GetStandardQueryable(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 dicst in DictionaryItemRepository.GetList(x => x.DictionaryCode == DictionaryItem.CF_Standard.ToString())
                        on fa.StandardID equals dicst.Value into tempst
                        from standard in tempst.DefaultIfEmpty()
                        select new StandardView
                        {
                            StandardID = fa.StandardID,
                            StandardName = standard.Name
                        };

            return query.Distinct();
        }

        /// <summary>
        /// 查询年级专业对应的专业信息StandardView(去重)
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<GrademajorView> GetStandardQueryableExcludeNotHaveStudent(Expression<Func<CF_Grademajor, bool>> exp, Expression<Func<CF_Student, bool>> expStudent)
        {
             var query = from gr in GrademajorRepository.GetList(exp)
                        join cl in ClassmajorRepository.Entities
                        on gr.GrademajorID equals cl.GrademajorID
                        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
                        join fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        join dicst in DictionaryItemRepository.GetList(x => x.DictionaryCode == DictionaryItem.CF_Standard.ToString())
                        on fa.StandardID equals dicst.Value into tempst
                        from standard in tempst.DefaultIfEmpty()
                        where sc.StudentCount>0
                        select new GrademajorView
                        {
                            StandardID = gr.CF_Facultymajor.StandardID,
                            StandardNameStr = standard.Name
                        };

            return query.Distinct();
        }

        /// <summary>
        /// 查询年级专业对应的专业信息StandardView(联动专业下拉，去重)
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<StandardView> GetStandardForComboGridQueryable(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 dicst in DictionaryItemRepository.GetList(x => x.DictionaryCode == DictionaryItem.CF_Standard.ToString())
                        on fa.StandardID equals dicst.Value into tempst
                        from standard in tempst.DefaultIfEmpty()
                        select new StandardView
                        {
                            StandardID = fa.StandardID,
                            StandardName = standard.Name
                        };

            return query.Distinct();
        }

        /// <summary>
        /// 查询年级专业对应的年级信息SchoolyearView(联动专业下拉，去重)
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<SchoolyearView> GetSchoolyearForComboGridQueryable(Expression<Func<CF_Grademajor, bool>> exp)
        {
            var query = from gr in GrademajorRepository.GetList(exp)
                        select new SchoolyearView
                        {
                            SchoolyearID = gr.SchoolyearID == null ? "" : gr.SchoolyearID.Value.ToString()
                        };

            return query.Distinct();
        }

    }
}