JW/EMiS.DataLogic/UniversityManage/AdministrativeOrgan/CampusDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using EMIS.Entities;
using EMIS.ViewModel.UniversityManage.AdministrativeOrgan;
using EMIS.DataLogic.Repositories;

namespace EMIS.DataLogic.UniversityManage.AdministrativeOrgan
{
    public class CampusDAL
    {
        public CampusRepository CampusRepository { get; set; }
        public CampusProfileRepository CampusProfileRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public DepartmentRepository DepartmentRepository { get; set; }
        public StaffRepository StaffRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public UserRepository UserRepository { get; set; }

        /// <summary>
        /// 查询对应的校区信息View
        /// </summary>
        /// <param name="expCampus"></param>
        /// <returns></returns>
        public IQueryable<CampusView> GetCampusViewQueryable(Expression<Func<CF_Campus, bool>> expCampus)
        {
            var query = from a in CampusRepository.GetList(expCampus)
                        join c in UserRepository.Entities
                        on a.CF_CampusProfile.Generalsuper equals c.UserID into gg
                        from gGeneralsuper in gg.DefaultIfEmpty()
                        join d in UserRepository.Entities
                        on a.CF_CampusProfile.Recruitstudents equals d.UserID into gr
                        from gRecruitstudents in gr.DefaultIfEmpty()
                        join e in UserRepository.Entities
                        on a.CF_CampusProfile.Placesuper equals e.UserID into gp
                        from gPlacesuper in gp.DefaultIfEmpty()
                        join f in UserRepository.Entities
                        on a.CF_CampusProfile.Teachingsuper equals f.UserID into gt
                        from gTeachingsuper in gt.DefaultIfEmpty()
                        join g in UserRepository.Entities
                        on a.CF_CampusProfile.Studentcharge equals g.UserID into gsc
                        from gStudentcharge in gsc.DefaultIfEmpty()
                        join col in
                            (
                                from col in CollegeRepository.Entities
                                group col by col.CampusID into colgr
                                select new
                                {
                                    CampusID = colgr.Key,
                                    CollegeCount = colgr.Count()
                                }
                            )
                        on a.CampusID equals col.CampusID into tempcol
                        from colcam in tempcol.DefaultIfEmpty()
                        select new CampusView
                        {
                            CampusID = a.CampusID,
                            UniversityID = a.UniversityID,
                            No = a.No,
                            Name = a.Name,
                            SimpleName = a.SimpleName,
                            EnglishName = a.EnglishName,
                            UniversityCode = a.CF_University.Code,
                            UniversityName = a.CF_University.Name,
                            Generalsuper = a.CF_CampusProfile.Generalsuper,
                            GeneralsuperName = gGeneralsuper.Name,
                            Recruitstudents = a.CF_CampusProfile.Recruitstudents,
                            RecruitstudentsName = gRecruitstudents.Name,
                            Placesuper = a.CF_CampusProfile.Placesuper,
                            PlacesuperName = gPlacesuper.Name,
                            Teachingsuper = a.CF_CampusProfile.Teachingsuper,
                            TeachingsuperName = gTeachingsuper.Name,
                            Studentcharge = a.CF_CampusProfile.Studentcharge,
                            StudentchargeName = gStudentcharge.Name,
                            ZIPCode = a.CF_CampusProfile.ZIPCode,
                            Officephone = a.CF_CampusProfile.Officephone,
                            FAX = a.CF_CampusProfile.FAX,
                            Email = a.CF_CampusProfile.Email,
                            Evolution = a.CF_CampusProfile.Evolution,
                            CollegeCount = colcam.CollegeCount == null ? 0 : colcam.CollegeCount,
                            Remark = a.Remark,
                            RecordStatus = a.RecordStatus,
                            CreateUserID = a.CreateUserID,
                            CreateTime = a.CreateTime,
                            ModifyUserID = a.ModifyUserID,
                            ModifyTime = a.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的院系所信息View(统计教研室数、教师人数、院系专业数)
        /// </summary>
        /// <param name="expCampus"></param>
        /// <returns></returns>
        public IQueryable<CollegeView> GetCollegeViewQueryable(Expression<Func<CF_Campus, bool>> expCampus)
        {
            var query = from cam in CampusRepository.GetList(expCampus)
                        join col in CollegeRepository.Entities
                        on cam.CampusID equals col.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;
        }

    }
}