JW/EMiS.DataLogic/UniversityManage/AdministrativeOrgan/CollegeDAL.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.AdministrativeOrgan;
using EMIS.ViewModel.UniversityManage.SpecialtyClassManage;
using EMIS.ViewModel.UniversityManage.TeacherManage;
using EMIS.DataLogic.Repositories;

namespace EMIS.DataLogic.UniversityManage.AdministrativeOrgan
{
    public class CollegeDAL
    {
        public CollegeRepository CollegeRepository { get; set; }
        public CollegeProfileRepository CollegeProfileRepository { get; set; }
        public CampusRepository CampusRepository { get; set; }
        public DepartmentRepository DepartmentRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public StaffRepository StaffRepository { get; set; }
        public StaffProfileRepository StaffProfileRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public DictionaryItemRepository DictionaryItemRepository { get; set; }

        /// <summary>
        /// 查询对应的院系所信息CollegeView
        /// </summary>
        /// <returns></returns>
        public IQueryable<CollegeView> GetCollegeViewQueryable()
        {
            var query = from col in CollegeRepository.Entities
                        join cam in CampusRepository.Entities
                        on col.CampusID equals cam.CampusID
                        join pm in UserRepository.Entities
                        on col.CF_CollegeProfile.PoliticalManager equals pm.UserID into temppm
                        from pmus in temppm.DefaultIfEmpty()
                        join am in UserRepository.Entities
                        on col.CF_CollegeProfile.AdministrativeManager equals am.UserID into tempam
                        from amus in tempam.DefaultIfEmpty()
                        select new CollegeView
                        {
                            CollegeID = col.CollegeID,
                            No = col.No,
                            Name = col.Name,
                            SimpleName = col.SimpleName,
                            EnglishName = col.EnglishName,
                            CampusID = col.CampusID,
                            CampusNo = cam.No,
                            CampusName = cam.Name,
                            PoliticalManager = col.CF_CollegeProfile.PoliticalManager,
                            PoliticalManagerName = pmus.Name,
                            AdministrativeManager = col.CF_CollegeProfile.AdministrativeManager,
                            AdministrativeManagerName = amus.Name,
                            UnitCategoryID = col.CF_CollegeProfile.UnitCategoryID,
                            CollegeTypeID = col.CF_CollegeProfile.CollegeTypeID,
                            CollegeCategoryID = col.CF_CollegeProfile.CollegeCategoryID,
                            RunByCategoryID = col.CF_CollegeProfile.RunByCategoryID,
                            FoundDate = col.CF_CollegeProfile.FoundDate,
                            Officephone = col.CF_CollegeProfile.Officephone,
                            Remark = col.Remark,
                            RecordStatus = col.RecordStatus,
                            CreateUserID = col.CreateUserID,
                            CreateTime = col.CreateTime,
                            ModifyUserID = col.ModifyUserID,
                            ModifyTime = col.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的院系所信息CollegeView(统计对应的教研室数、教师人数、院系专业数)
        /// </summary>
        /// <param name="expCollege"></param> 
        /// <returns></returns>
        public IQueryable<CollegeView> GetCollegeViewQueryable(Expression<Func<CF_College, bool>> expCollege)
        {
            var query = from col in CollegeRepository.GetList(expCollege)
                        join cam in CampusRepository.Entities
                        on col.CampusID equals cam.CampusID
                        join pm in UserRepository.Entities
                        on col.CF_CollegeProfile.PoliticalManager equals pm.UserID into temppm
                        from pmus in temppm.DefaultIfEmpty()
                        join am in UserRepository.Entities
                        on col.CF_CollegeProfile.AdministrativeManager equals am.UserID into tempam
                        from amus in tempam.DefaultIfEmpty()
                        join dep in
                            (
                                from dep in DepartmentRepository.Entities
                                group dep by dep.CollegeID into depgr
                                select new
                                {
                                    CollegeID = depgr.Key,
                                    DepartmentCount = depgr.Count()
                                }
                            )
                        on col.CollegeID equals dep.CollegeID into tempdep
                        from depcol in tempdep.DefaultIfEmpty()
                        join sta in
                            (
                                from sta in StaffRepository.Entities
                                group sta by sta.CollegeID into stagr
                                select new
                                {
                                    CollegeID = stagr.Key,
                                    StaffCount = stagr.Count()
                                }
                            )
                        on col.CollegeID equals sta.CollegeID into tempsta
                        from stacol in tempsta.DefaultIfEmpty()
                        join fa in
                            (
                                from fa in FacultymajorRepository.Entities
                                group fa by fa.CollegeID into fagr
                                select new
                                {
                                    CollegeID = fagr.Key,
                                    FacultymajorCount = fagr.Count()
                                }
                            )
                        on col.CollegeID equals fa.CollegeID into tempfa
                        from facol in tempfa.DefaultIfEmpty()
                        select new CollegeView
                        {
                            CollegeID = col.CollegeID,
                            No = col.No,
                            Name = col.Name,
                            SimpleName = col.SimpleName,
                            EnglishName = col.EnglishName,
                            CampusID = col.CampusID,
                            CampusNo = cam.No,
                            CampusName = cam.Name,
                            PoliticalManager = col.CF_CollegeProfile.PoliticalManager,
                            PoliticalManagerName = pmus.Name,
                            AdministrativeManager = col.CF_CollegeProfile.AdministrativeManager,
                            AdministrativeManagerName = amus.Name,
                            UnitCategoryID = col.CF_CollegeProfile.UnitCategoryID,
                            CollegeTypeID = col.CF_CollegeProfile.CollegeTypeID,
                            CollegeCategoryID = col.CF_CollegeProfile.CollegeCategoryID,
                            RunByCategoryID = col.CF_CollegeProfile.RunByCategoryID,
                            FoundDate = col.CF_CollegeProfile.FoundDate,
                            Officephone = col.CF_CollegeProfile.Officephone,
                            DepartmentCount = depcol.DepartmentCount == null ? 0 : depcol.DepartmentCount,
                            StaffCount = stacol.StaffCount == null ? 0 : stacol.StaffCount,
                            FacultymajorCount = facol.FacultymajorCount == null ? 0 : facol.FacultymajorCount,
                            Remark = col.Remark,
                            RecordStatus = col.RecordStatus,
                            CreateUserID = col.CreateUserID,
                            CreateTime = col.CreateTime,
                            ModifyUserID = col.ModifyUserID,
                            ModifyTime = col.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的院系所信息CollegeView(只显示院、系、部类别的院系所)
        /// </summary>
        /// <param name="expCollege"></param>
        /// <returns></returns>
        public IQueryable<CollegeView> GetOnlyCollegeViewQueryable(Expression<Func<CF_College, bool>> expCollege)
        {
            var query = from col in CollegeRepository.GetList(expCollege)
                        join cam in CampusRepository.Entities
                        on col.CampusID equals cam.CampusID
                        where col.CF_CollegeProfile.UnitCategoryID == (int)CF_UnitCategory.College
                        select new CollegeView
                        {
                            CollegeID = col.CollegeID,
                            No = col.No,
                            Name = col.Name,
                            SimpleName = col.SimpleName,
                            EnglishName = col.EnglishName,
                            CampusID = col.CampusID,
                            CampusNo = cam.No,
                            CampusName = cam.Name,
                            UnitCategoryID = col.CF_CollegeProfile.UnitCategoryID,
                            CollegeTypeID = col.CF_CollegeProfile.CollegeTypeID,
                            CollegeCategoryID = col.CF_CollegeProfile.CollegeCategoryID,
                            RunByCategoryID = col.CF_CollegeProfile.RunByCategoryID,
                            FoundDate = col.CF_CollegeProfile.FoundDate,
                            Officephone = col.CF_CollegeProfile.Officephone,
                            Remark = col.Remark,
                            RecordStatus = col.RecordStatus,
                            CreateUserID = col.CreateUserID,
                            CreateTime = col.CreateTime,
                            ModifyUserID = col.ModifyUserID,
                            ModifyTime = col.ModifyTime
                        };

            return query;

        }

        /// <summary>
        /// 查询对应的教研室信息CollegeView(统计对应的教师人数)
        /// </summary>
        /// <param name="expCollege"></param>
        /// <returns></returns>
        public IQueryable<DepartmentView> GetDepartmentViewQueryable(Expression<Func<CF_College, bool>> expCollege)
        {
            var query = from col in CollegeRepository.GetList(expCollege)
                        join dep in DepartmentRepository.Entities
                        on col.CollegeID equals dep.CollegeID
                        join c in UserRepository.Entities
                        on dep.CF_DepartmentProfile.DirectorID equals c.UserID into gu
                        from guic in gu.DefaultIfEmpty()
                        join d in UserRepository.Entities
                        on dep.CF_DepartmentProfile.DeputyDirectorID equals d.UserID into gude
                        from gudeic in gude.DefaultIfEmpty()
                        join sta in
                            (
                                from sta in StaffRepository.Entities
                                group sta by sta.DepartmentID into stagr
                                select new
                                {
                                    DepartmentID = stagr.Key,
                                    StaffCount = stagr.Count()
                                }
                            )
                        on dep.DepartmentID equals sta.DepartmentID into tempsta
                        from stadep in tempsta.DefaultIfEmpty()
                        select new DepartmentView
                        {
                            DepartmentID = dep.DepartmentID,
                            No = dep.No,
                            Name = dep.Name,
                            SimpleName = dep.SimpleName,
                            EnglishName = dep.EnglishName,
                            CampusID = col.CampusID,
                            CampusNo = col.CF_Campus.No,
                            CampusName = col.CF_Campus.Name,
                            CollegeID = dep.CollegeID,
                            CollegeCode = col.No,
                            CollegeName = col.Name,
                            HierarchyID = dep.HierarchyID,
                            DirectorID = dep.CF_DepartmentProfile.DirectorID,
                            DirectorName = guic.Name,
                            DeputyDirectorID = dep.CF_DepartmentProfile.DeputyDirectorID,
                            DeputyDirectorName = gudeic.Name,
                            FoundDate = dep.CF_DepartmentProfile.FoundDate,
                            StaffCount = stadep.StaffCount == null ? 0 : stadep.StaffCount,
                            Remark = dep.Remark,
                            RecordStatus = dep.RecordStatus,
                            CreateUserID = dep.CreateUserID,
                            CreateTime = dep.CreateTime,
                            ModifyUserID = dep.ModifyUserID,
                            ModifyTime = dep.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的教师信息StaffView
        /// </summary>
        /// <param name="expCollege"></param>
        /// <returns></returns>
        public IQueryable<StaffView> GetStaffViewQueryable(Expression<Func<CF_College, bool>> expCollege)
        {
            var query = from col in CollegeRepository.GetList(expCollege)
                        join sf in StaffRepository.Entities
                        on col.CollegeID equals sf.CollegeID
                        join pr in StaffProfileRepository.Entities
                        on sf.UserID equals pr.UserID into temppr
                        from sfpr in temppr.DefaultIfEmpty()
                        join dep in DepartmentRepository.Entities
                        on sf.DepartmentID equals dep.DepartmentID into tempdep
                        from sfdep in tempdep.DefaultIfEmpty()
                        join us in UserRepository.Entities
                        on sf.UserID equals us.UserID
                        select new StaffView
                        {
                            UserID = sf.UserID,
                            StaffCode = us.LoginID,
                            Name = us.Name,
                            UsedName = sfpr.UsedName,
                            CampusID = col.CampusID,
                            CampusCode = col.CF_Campus.No,
                            CampusName = col.CF_Campus.Name,
                            CollegeID = sf.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            DepartmentID = sf.DepartmentID,
                            DepartmentCode = sfdep.No,
                            DepartmentName = sfdep.Name,
                            Nationality = sfpr.Nationality,
                            Place = sfpr.Place,
                            SexID = sf.SexID,
                            BirthDate = sf.BirthDate,
                            NationID = sf.NationID,
                            PoliticsID = sf.PoliticsID,
                            CertificatesType = sf.CertificatesType,
                            CertificatesNum = sf.CertificatesNum,
                            TeacherTypeID = sf.TeacherTypeID,
                            IncumbencyState = sf.IncumbencyState,
                            SituationID = sf.SituationID,
                            TitleID = sf.TitleID,
                            LiteracyLevelID = sf.LiteracyLevelID,
                            LearnPositionID = sf.LearnPositionID,
                            IsDualTeacher = sf.IsDualTeacher ?? false,
                            PaymentLevelID = sf.PaymentLevelID,
                            WorkDate = sf.WorkDate,
                            ComeSchoolDate = sf.ComeSchoolDate,
                            Email = sfpr.Email,
                            ZIPCode = sfpr.ZIPCode,
                            WeChatNum = sfpr.WeChatNum,
                            QQ = sfpr.QQ,
                            Telephone = sfpr.Telephone,
                            OfficeTelephone = sfpr.OfficeTelephone,
                            HousePhone = sfpr.HousePhone,
                            Mobile = sfpr.Mobile,
                            Speciality = sfpr.Speciality,
                            HealthStateID = sfpr.HealthStateID,
                            ReligionID = sfpr.ReligionID,
                            Account = sfpr.Account,
                            Residence = sfpr.Residence,
                            HomeAddress = sfpr.HomeAddress,
                            Address = sfpr.Address,
                            NowAddress = sfpr.NowAddress,
                            PhotoUrl = sf.PhotoUrl,
                            PhotoHasValue = (sf.PhotoUrl == null || sf.PhotoUrl == "") ? false : true,
                            Profile = sf.Profile,
                            Remark = sf.Remark,
                            RecordStatus = sf.RecordStatus,
                            CreateUserID = sf.CreateUserID,
                            CreateTime = sf.CreateTime,
                            ModifyUserID = sf.ModifyUserID,
                            ModifyTime = sf.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的院系专业信息FacultymajorView(统计对应的年级专业数、学生人数)
        /// </summary>
        /// <param name="expCollege"></param>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<FacultymajorView> GetFacultymajorViewQueryable(Expression<Func<CF_College, bool>> expCollege, Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from col in CollegeRepository.GetList(expCollege)
                        join fa in FacultymajorRepository.Entities
                        on col.CollegeID equals fa.CollegeID
                        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 = col.CF_Campus.No,
                            CampusName = col.CF_Campus.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;
        }

    }
}