EleKitService/RDH.Data/OperationRoomBLL.ext.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using RDH.Data.Models;

namespace RDH.Data.BLL
{

    public partial class OperationRoomBLL : IOperationRoomBLL
    {
        private Dictionary<string, string> UserInfoColumnPropMaps = new Dictionary<string, string>();
        private void InitUserInfoMaps()
        {
            UserInfoColumnPropMaps.Add("SNAPSHOT_KEY", "SnapshotKey");
            UserInfoColumnPropMaps.Add("USER_ID", "UserId");
            UserInfoColumnPropMaps.Add("PASSWORD", "Password");
            UserInfoColumnPropMaps.Add("IF_CARD_ID", "IfCardId");
            UserInfoColumnPropMaps.Add("PINGER_PRINT_DATA", "PingerPrintData");
            UserInfoColumnPropMaps.Add("LOING_TRY_TIMES", "LoingTryTimes");
            UserInfoColumnPropMaps.Add("IS_ACTIVE", "IsActive");
            UserInfoColumnPropMaps.Add("LOCATION_KEY", "LocationKey");
            UserInfoColumnPropMaps.Add("NAME", "Name");
            UserInfoColumnPropMaps.Add("SEX", "Sex");
            UserInfoColumnPropMaps.Add("AGE", "Age");
            UserInfoColumnPropMaps.Add("MOBILE_PHONE", "MobilePhone");
            UserInfoColumnPropMaps.Add("TELPHONE", "Telphone");
            UserInfoColumnPropMaps.Add("ADDRESS", "Address");
            UserInfoColumnPropMaps.Add("CREATE_BY", "CreateBy");
            UserInfoColumnPropMaps.Add("CREATE_TIME", "CreateTime");
            UserInfoColumnPropMaps.Add("KEY", "Key");
            UserInfoColumnPropMaps.Add("IS_SUPPER_USER", "IsSupperUser");//COLUMN_MAPS
        }


        private string GenerateJoinSql()
        {
            StringBuilder OperationRoomValues = new StringBuilder();
            foreach (var c in ColumnPropMaps)
            {
                OperationRoomValues.AppendFormat("oroom.\"{0}\" \"{1}\",", c.Key, c.Value);
            }
            OperationRoomValues.Remove(OperationRoomValues.Length - 1, 1);

            StringBuilder UserInfoValues = new StringBuilder();
            foreach (var c in UserInfoColumnPropMaps)
            {
                UserInfoValues.AppendFormat("ui.\"{0}\" \"{1}\",", c.Key, c.Value);
            }
            UserInfoValues.Remove(UserInfoValues.Length - 1, 1);



            StringBuilder from = new StringBuilder();
            from.Append(" OPERATION_ROOM oroom  ");
            from.Append("  LEFT JOIN ");
            from.Append("USER_INFO ui ");
            from.Append(" on OROOM.CREATE_BY = UI.KEY  ");

            return string.Format("SELECT {0},'' as split1,{1} FROM {2}", OperationRoomValues, UserInfoValues, from);
        }
        public IEnumerable<OperationRoom> ListOperationRoomWithSection()
        {
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append("SELECT ");
            foreach (KeyValuePair<String, String> pair in this.ColumnPropMaps) 
            {
                sqlBuilder.AppendFormat("r.{0} {1},", pair.Key, pair.Value);
            }
            //section
            sqlBuilder.Append("'' spiltor,");
            SectionBLL sectionBLL = new SectionBLL();
            foreach (KeyValuePair<String, String> pair in sectionBLL.ColumnPropMaps) 
            {
                sqlBuilder.AppendFormat("s.{0} {1},", pair.Key, pair.Value);
            }
            sqlBuilder.Remove(sqlBuilder.Length - 1, 1);
            sqlBuilder.AppendFormat(" FROM {0} r"
                + " LEFT JOIN {1} s ON s.key=r.ext01",
                this.TABLE_NAME, sectionBLL.TABLE_NAME);
            return SqlMapper.Query<OperationRoom, Section, OperationRoom>(
                ConnectionFactory.Current.GetSessionConnection(), sqlBuilder.ToString(),
                (r, s) =>
                {
                    r.SectionInfo = s;
                    return r;
                }, splitOn: "spiltor");
        }
        public IEnumerable<OperationRoom> ListOperationRoom()
        {
            InitUserInfoMaps();
            string sql = GenerateJoinSql();
            return SqlMapper.Query<OperationRoom, UserInfo, OperationRoom>(ConnectionFactory.Current.GetSessionConnection(), sql, (or, ui) =>
                 {
                     or.UserInfo = ui;
                     return or;
                 }, splitOn: "split1");
        }

        public IEnumerable<OperationRoom> ListNoDeviceRoom(Guid? deviceKey)
        {
            string sql = this.BuildBaseSql();

            if (deviceKey == null || deviceKey.Value == default(Guid))
            {
                ///没有手术间
                sql += " WHERE KEY NOT IN (SELECT D.OPERATION_ROOM_KEY FROM DISPENSING_DEVICE D WHERE D.OPERATION_ROOM_KEY IS NOT NULL AND D.ENABEL_FLAG='True')";
            }
            else
            {
                sql += "WHERE KEY NOT IN (SELECT D.OPERATION_ROOM_KEY FROM DISPENSING_DEVICE D WHERE D.OPERATION_ROOM_KEY IS NOT NULL AND D.ENABEL_FLAG='True' AND D.KEY<>:deviceKey)";
            }
            //string sql = this.BuildBaseSql() + " WHERE KEY NOT IN (SELECT D.OPERATION_ROOM_KEY FROM DISPENSING_DEVICE D WHERE D.OPERATION_ROOM_KEY IS NOT NULL)";

            return SqlMapper.Query<OperationRoom>(ConnectionFactory.Current.GetSessionConnection(), sql,
                new { deviceKey = deviceKey.GetValueOrDefault().ToString() });
        }

        public IEnumerable<OperationRoom> ListDeviceRooms()
        {
            string sql = this.BuildBaseSql();
            ///绑定麻车的手术间

            sql += " WHERE KEY IN (SELECT DD.OPERATION_ROOM_KEY FROM DISPENSING_DEVICE DD WHERE DD.ENABEL_FLAG='True')";

            return SqlMapper.Query<OperationRoom>(ConnectionFactory.Current.GetSessionConnection(), sql);
        }

        public OperationRoom GetByOperationRoomId(string roomId)
        {
            var sql = BuildBaseSql(); 
            return SqlMapper.Query<OperationRoom>(ConnectionFactory.Current.GetSessionConnection(), sql + "  WHERE ROOM_ID = :RoomId", new { RoomId = roomId }).FirstOrDefault();

        }
    }

}