JW/EMiS.DataLogic/SchedulingManage/SchedulingSettings/CollegeClassroomDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Linq.Expressions;
using EMIS.Entities;
using EMIS.ViewModel.SchedulingManage.SchedulingSettings;
using EMIS.ViewModel.UniversityManage.ClassroomManage;
using EMIS.DataLogic.Repositories;

namespace EMIS.DataLogic.SchedulingManage.SchedulingSettings
{
    public class CollegeClassroomDAL
    {
        public CollegeClassroomRepository CollegeClassroomRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public CampusRepository CampusRepository { get; set; }
        public ClassroomRepository ClassroomRepository { get; set; }
        public ClassroomReserveRepository ClassroomReserveRepository { get; set; }

        /// <summary>
        /// 查询院系教室信息CollegeClassroomView（关联排课中教室预留信息）
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<CollegeClassroomView> GetCollegeClassroomViewQueryable(Expression<Func<ES_CollegeClassroom, bool>> exp)
        {
            var query = from colcr in CollegeClassroomRepository.GetList(exp)
                        join col in CollegeRepository.Entities
                        on colcr.CollegeID equals col.CollegeID
                        join cam in CampusRepository.Entities
                        on col.CampusID equals cam.CampusID
                        join cr in ClassroomRepository.Entities
                        on colcr.ClassroomID equals cr.ClassroomID
                        join re in ClassroomReserveRepository.Entities
                        on cr.ClassroomID equals re.ClassroomID into tempre
                        from crre in tempre.DefaultIfEmpty()
                        select new CollegeClassroomView
                        {
                            CollegeClassroomID = colcr.CollegeClassroomID,
                            CollegeID = colcr.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            UnitCategoryID = col.CF_CollegeProfile.UnitCategoryID,
                            CollegeCampusID = col.CampusID,
                            CollegeCampusNo = cam.No,
                            CollegeCampusName = cam.Name,
                            ClassroomID = colcr.ClassroomID,
                            ClassroomCode = cr.Code,
                            ClassroomName = cr.Name,
                            ClassroomCollegeID = cr.CollegeID,
                            ClassroomCollegeNo = cr.CF_College.No,
                            ClassroomCollegeName = cr.CF_College.Name,
                            BuildingsInfoID = cr.BuildingsInfoID,
                            BuildingsInfoCode = cr.CF_BuildingsInfo.Code,
                            BuildingsInfoName = cr.CF_BuildingsInfo.Name,
                            CampusID = cr.CF_BuildingsInfo.CampusID,
                            CampusNo = cr.CF_BuildingsInfo.CF_Campus.No,
                            CampusName = cr.CF_BuildingsInfo.CF_Campus.Name,
                            CF_ClassroomType = cr.CF_ClassroomType,
                            ClassroomTypeIDList = cr.CF_ClassroomType.Select(x => x.ClassroomType).ToList(),
                            RoomUseID = cr.RoomUseID,
                            Totalseating = cr.Totalseating,
                            Effectiveseating = cr.Effectiveseating,
                            IsConcurrentUse = cr.IsConcurrentUse ?? false,
                            IsReserve = crre.IsReserved == true ? true : false,
                            IsAvailable = cr.IsAvailable ?? false,
                            RecordStatus = colcr.RecordStatus,
                            CreateUserID = colcr.CreateUserID,
                            CreateTime = colcr.CreateTime,
                            ModifyUserID = colcr.ModifyUserID,
                            ModifyTime = colcr.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询院系教室中未新增的教室信息ClassroomView
        /// </summary>
        /// <param name="expClassroom"></param>
        /// <param name="expCollegeClassroom"></param>
        /// <returns></returns>
        public IQueryable<ClassroomView> GetClassroomViewNoAddQueryable(Expression<Func<CF_Classroom, bool>> expClassroom,
                Expression<Func<ES_CollegeClassroom, bool>> expCollegeClassroom)
        {
            var query = from cr in ClassroomRepository.GetList(expClassroom)
                        join re in ClassroomReserveRepository.Entities
                        on cr.ClassroomID equals re.ClassroomID into tempre
                        from crre in tempre.DefaultIfEmpty()
                        join cc in CollegeClassroomRepository.GetList(expCollegeClassroom)
                        on cr.ClassroomID equals cc.ClassroomID into tempcc
                        from colcr in tempcc.DefaultIfEmpty()
                        where colcr.CollegeClassroomID == null
                        select new ClassroomView
                        {
                            ClassroomID = cr.ClassroomID,
                            Code = cr.Code,
                            Name = cr.Name,
                            BuildingsInfoID = cr.BuildingsInfoID,
                            BuildingsInfoCode = cr.CF_BuildingsInfo.Code,
                            BuildingsInfoName = cr.CF_BuildingsInfo.Name,
                            CampusID = cr.CF_BuildingsInfo.CampusID,
                            CampusCode = cr.CF_BuildingsInfo.CF_Campus.No,
                            CampusName = cr.CF_BuildingsInfo.CF_Campus.Name,
                            CollegeID = cr.CollegeID,
                            CollegeCode = cr.CF_College.No,
                            CollegeName = cr.CF_College.Name,
                            LayoutTypeID = cr.LayoutTypeID,
                            CF_ClassroomType = cr.CF_ClassroomType,
                            RoomUseID = cr.RoomUseID,
                            FloorLevel = cr.FloorLevel,
                            Acreage = cr.Acreage,
                            RowCout = cr.RowCout,
                            ColumnCount = cr.ColumnCount,
                            Totalseating = cr.Totalseating,
                            Effectiveseating = cr.Effectiveseating,
                            Examinationseating = cr.Examinationseating,
                            IsWrittenExam = cr.IsWrittenExam ?? false,
                            IsMachinetest = cr.IsMachinetest ?? false,
                            IsConcurrentUse = cr.IsConcurrentUse ?? false,
                            IsReserve = crre.IsReserved == true ? true : false,
                            IsAvailable = cr.IsAvailable ?? false,
                            Remark = cr.Remark,
                            RecordStatus = cr.RecordStatus,
                            CreateUserID = cr.CreateUserID,
                            CreateTime = cr.CreateTime,
                            ModifyUserID = cr.ModifyUserID,
                            ModifyTime = cr.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询未新增的院系教室信息CollegeClassroomView（关联排课中教室预留信息）
        /// 注：交叉连接（暂时无效,有问题）
        /// </summary>
        /// <param name="expCollege"></param>
        /// <param name="expClassroom"></param>
        /// <returns></returns>
        public IQueryable<CollegeClassroomView> GetCollegeClassroomViewNoAddQueryable(Expression<Func<CF_College, bool>> expCollege, 
                Expression<Func<CF_Classroom, bool>> expClassroom)
        {
            //var crossJoinQuery = CollegeRepository.GetList(expCollege)
            //        .SelectMany(x => ClassroomRepository.Entities
            //            .Select(c => new { CollegeID = x.CollegeID, ClassroomID = c.ClassroomID }));

            var crossJoinQuery = from col in CollegeRepository.GetList(expCollege)
                                 from cr in ClassroomRepository.Entities
                                 select new
                                 {
                                     CollegeID = col.CollegeID,
                                     ClassroomID = cr.ClassroomID
                                 };

            var query = from crosscc in crossJoinQuery
                        join cc in CollegeClassroomRepository.Entities
                        on new { CollegeID = crosscc.CollegeID, ClassroomID = crosscc.ClassroomID }
                        equals new { CollegeID = cc.CollegeID.Value, ClassroomID = cc.ClassroomID.Value } into tempcc
                        from colcr in tempcc.DefaultIfEmpty()
                        join col in CollegeRepository.GetList(expCollege)
                        on crosscc.CollegeID equals col.CollegeID
                        join cam in CampusRepository.Entities
                        on col.CampusID equals cam.CampusID
                        join cr in ClassroomRepository.GetList(expClassroom)
                        on crosscc.ClassroomID equals cr.ClassroomID
                        join re in ClassroomReserveRepository.Entities
                        on cr.ClassroomID equals re.ClassroomID into tempre
                        from crre in tempre.DefaultIfEmpty()
                        where colcr.CollegeClassroomID == null
                        select new CollegeClassroomView
                        {
                            CollegeClassroomID = Guid.NewGuid(),
                            CollegeID = col.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            UnitCategoryID = col.CF_CollegeProfile.UnitCategoryID,
                            CollegeCampusID = col.CampusID,
                            CollegeCampusNo = cam.No,
                            CollegeCampusName = cam.Name,
                            ClassroomID = cr.ClassroomID,
                            ClassroomCode = cr.Code,
                            ClassroomName = cr.Name,
                            ClassroomCollegeID = cr.CollegeID,
                            ClassroomCollegeNo = cr.CF_College.No,
                            ClassroomCollegeName = cr.CF_College.Name,
                            BuildingsInfoID = cr.BuildingsInfoID,
                            BuildingsInfoCode = cr.CF_BuildingsInfo.Code,
                            BuildingsInfoName = cr.CF_BuildingsInfo.Name,
                            CampusID = cr.CF_BuildingsInfo.CampusID,
                            CampusNo = cr.CF_BuildingsInfo.CF_Campus.No,
                            CampusName = cr.CF_BuildingsInfo.CF_Campus.Name,
                            CF_ClassroomType = cr.CF_ClassroomType,
                            ClassroomTypeIDList = cr.CF_ClassroomType.Select(x => x.ClassroomType).ToList(),
                            RoomUseID = cr.RoomUseID,
                            Totalseating = cr.Totalseating,
                            Effectiveseating = cr.Effectiveseating,
                            IsConcurrentUse = cr.IsConcurrentUse ?? false,
                            IsReserve = crre.IsReserved == true ? true : false,
                            IsAvailable = cr.IsAvailable ?? false
                        };

            return query;
        }

    }
}