JW/EMiS.DataLogic/UniversityManage/SpecialtyClassManage/FacultymajorDAL.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 FacultymajorDAL
    {
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public CampusRepository CampusRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public SpecialtyRepository SpecialtyRepository { get; set; }
        public DictionaryItemRepository DictionaryItemRepository { get; set; }

        /// <summary>
        /// 查询对应的院系专业信息View
        /// </summary>
        /// <param name="expFacultymajor"></param>
        /// <returns></returns>
        public IQueryable<FacultymajorView> GetFacultymajorViewQueryable(Expression<Func<CF_Facultymajor, bool>> expFacultymajor)
        {
            var query = from fa in FacultymajorRepository.GetList(expFacultymajor)
                        join col in CollegeRepository.Entities
                        on fa.CollegeID equals col.CollegeID
                        join cam in CampusRepository.Entities
                        on col.CampusID equals cam.CampusID
                        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 FacultymajorView
                        {
                            FacultymajorID = fa.FacultymajorID,
                            Code = fa.Code,
                            Name = fa.Name,
                            Abbreviation = fa.Abbreviation,
                            EnglishName = fa.EnglishName,
                            NameCode = fa.Name + fa.Code,
                            CampusID = col.CampusID,
                            CampusNo = cam.No,
                            CampusName = cam.Name,
                            CollegeID = fa.CollegeID,
                            CollegeCode = col.No,
                            CollegeName = col.Name,
                            StandardID = fa.StandardID,
                            StandardName = standard.Name,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            ScienceclassID = fa.ScienceclassID,
                            LearningstyleID = fa.LearningstyleID,
                            LearnPositionID = fa.LearnPositionID,
                            TeacherIdentification = fa.TeacherIdentification,
                            SetTime = fa.SetTime,
                            Remark = fa.Remark,
                            RecordStatus = fa.RecordStatus,
                            CreateUserID = fa.CreateUserID,
                            CreateTime = fa.CreateTime,
                            ModifyUserID = fa.ModifyUserID,
                            ModifyTime = fa.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的院系专业信息View(统计对应的年级专业数、学生人数)
        /// </summary>
        /// <param name="expFacultymajor"></param>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<FacultymajorView> GetFacultymajorViewQueryable(Expression<Func<CF_Facultymajor, bool>> expFacultymajor, Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from fa in FacultymajorRepository.GetList(expFacultymajor)
                        join col in CollegeRepository.Entities
                        on fa.CollegeID equals col.CollegeID
                        join cam in CampusRepository.Entities
                        on col.CampusID equals cam.CampusID
                        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()
                        join gr in
                            (
                                from gr in GrademajorRepository.Entities
                                group gr by gr.FacultymajorID into ggr
                                select new
                                {
                                    FacultymajorID = ggr.Key,
                                    GrademajorCount = ggr.Count()
                                }
                            )
                        on fa.FacultymajorID equals gr.FacultymajorID into tempgr
                        from grfa in tempgr.DefaultIfEmpty()
                        join sc in
                            (
                                from sc in StudentRepository.GetList(expStudent)
                                group sc by sc.CF_Classmajor.CF_Grademajor.FacultymajorID into gstu
                                select new
                                {
                                    FacultymajorID = gstu.Key,
                                    StudentCount = gstu.Count()
                                }
                            )
                        on fa.FacultymajorID equals sc.FacultymajorID into tempstu
                        from stufa in tempstu.DefaultIfEmpty()
                        select new FacultymajorView
                        {
                            FacultymajorID = fa.FacultymajorID,
                            Code = fa.Code,
                            Name = fa.Name,
                            Abbreviation = fa.Abbreviation,
                            EnglishName = fa.EnglishName,
                            NameCode = fa.Name + fa.Code,
                            CampusID = col.CampusID,
                            CampusNo = cam.No,
                            CampusName = cam.Name,
                            CollegeID = fa.CollegeID,
                            CollegeCode = col.No,
                            CollegeName = col.Name,
                            StandardID = fa.StandardID,
                            StandardName = standard.Name,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            ScienceclassID = fa.ScienceclassID,
                            LearningstyleID = fa.LearningstyleID,
                            LearnPositionID = fa.LearnPositionID,
                            TeacherIdentification = fa.TeacherIdentification,
                            SetTime = fa.SetTime,
                            GrademajorCount = grfa.GrademajorCount == null ? 0 : grfa.GrademajorCount,
                            StudentCount = stufa.StudentCount == null ? 0 : stufa.StudentCount,
                            Remark = fa.Remark,
                            RecordStatus = fa.RecordStatus,
                            CreateUserID = fa.CreateUserID,
                            CreateTime = fa.CreateTime,
                            ModifyUserID = fa.ModifyUserID,
                            ModifyTime = fa.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的年级专业信息View(根据院系专业信息，统计各年级专业对应的学生人数)
        /// </summary>
        /// <param name="expFacultymajor"></param>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<GrademajorView> GetGrademajorViwQueryable(Expression<Func<CF_Facultymajor, bool>> expFacultymajor, Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from fa in FacultymajorRepository.GetList(expFacultymajor)
                        join col in CollegeRepository.Entities
                        on fa.CollegeID equals col.CollegeID
                        join cam in CampusRepository.Entities
                        on col.CampusID equals cam.CampusID
                        join gr in GrademajorRepository.Entities
                        on fa.FacultymajorID equals gr.FacultymajorID
                        join sc in
                            (
                            from sc in StudentRepository.GetList(expStudent)
                            group sc by sc.CF_Classmajor.CF_Grademajor.GrademajorID into g
                            select new
                            {
                                GrademajorID = g.Key,
                                StudentCount = g.Count()
                            }
                            )
                        on gr.GrademajorID equals sc.GrademajorID into dsc
                        from esc in dsc.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,
                            StudentCount = esc.StudentCount == null ? 0 : esc.StudentCount,
                            Remark = gr.Remark,
                            RecordStatus = gr.RecordStatus,
                            CreateUserID = gr.CreateUserID,
                            CreateTime = gr.CreateTime,
                            ModifyUserID = gr.ModifyUserID,
                            ModifyTime = gr.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询院系专业所对应的学生信息StudentBaseView
        /// </summary>
        /// <param name="expFacultymajor"></param>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<StudentBaseView> GetStudentBaseViewQueryable(Expression<Func<CF_Facultymajor, bool>> expFacultymajor, 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.Entities
                        on cl.GrademajorID equals gr.GrademajorID
                        join fa in FacultymajorRepository.GetList(expFacultymajor)
                        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>
        /// 查询院系专业对应的专业信息
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<FacultymajorView> GetStandardViewQueryable(Expression<Func<CF_Facultymajor, bool>> exp)
        {
            var query = from fa in FacultymajorRepository.GetList(exp)
                        join col in CollegeRepository.Entities
                        on fa.CollegeID equals col.CollegeID
                        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>
        /// 查询院系专业对应的学制
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<FacultymajorView> GetLearnSystemViewQueryable(Expression<Func<CF_Facultymajor, bool>> exp)
        {
            var query = from fa in FacultymajorRepository.GetList(exp)
                        select new FacultymajorView
                        {
                            LearnSystem = fa.LearnSystem,
                        };

            return query.Distinct();
        }

    }
}