EleKitService/RDH.Data/StorageSpaceBLL.cs

using RDH.Data.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;

namespace RDH.Data.BLL
{
    public partial class StorageSpaceBLL : BaseOrclSnapshotBLL<StorageSpace>
    {
        public StorageSpaceBLL() : base()
        {
            AliasTableName = "s";
        }

        internal override String TABLE_NAME { get { return "STORAGE_SPACE"; } }//TB_NAME
        internal override string ENTITY_NAME { get { return "StorageSpace"; } }
        internal override String KEY_COLUMN { get { return "SNAPSHOT_KEY"; } }//KEY_COLUMN
        internal override string SNAPSHOT_BASE { get { return "STORAGE_SPACE_BASE"; } }

        internal override void InitMaps()
        {
            ColumnPropMaps.Add("PARENT_KEY", "ParentKey");
            ColumnPropMaps.Add("SNAPSHOT_KEY", "SnapshotKey");
            ColumnPropMaps.Add("ROW_INDEX", "RowIndex");
            ColumnPropMaps.Add("COLUMN_INDEX", "ColumnIndex");
            ColumnPropMaps.Add("ROW_SPAN", "RowSpan");
            ColumnPropMaps.Add("COLUMN_SPAN", "ColumnSpan");
            ColumnPropMaps.Add("CHILDREN_ROWS", "ChildrenRows");
            ColumnPropMaps.Add("CHILDREN_COLUMNS", "ChildrenColumns");
            ColumnPropMaps.Add("TYPE_CODE", "TypeCode");
            ColumnPropMaps.Add("NAME", "Name");
            ColumnPropMaps.Add("CODE", "Code");
            ColumnPropMaps.Add("SERIAL_NO", "SerialNo");
            ColumnPropMaps.Add("DEVICE_KEY", "DeviceKey");
            ColumnPropMaps.Add("CREATE_BY", "CreateBy");
            ColumnPropMaps.Add("CREATE_TIME", "CreateTime");
            ColumnPropMaps.Add("KEY", "Key");
            ColumnPropMaps.Add("FLAG", "Flag");
            ColumnPropMaps.Add("CLIENT_TIME", "ClientTime");
            ColumnPropMaps.Add("SERVER_TIME", "ServerTime");
            ColumnPropMaps.Add("EXT01", "Ext01");
            ColumnPropMaps.Add("EXT02", "Ext02");
            ColumnPropMaps.Add("EXT03", "Ext03");
            ColumnPropMaps.Add("EXT04", "Ext04");
            ColumnPropMaps.Add("EXT05", "Ext05");
            ColumnPropMaps.Add("EXT06", "Ext06");
            ColumnPropMaps.Add("EXT07", "Ext07");
            ColumnPropMaps.Add("EXT08", "Ext08");
            ColumnPropMaps.Add("EXT09", "Ext09");
            ColumnPropMaps.Add("EXT10", "Ext10");//COLUMN_MAPS
        }

        internal override object GetParam(StorageSpace storagespace)
        {
            return new
            {
                ParentKey = storagespace.ParentKey,
                SnapshotKey = storagespace.SnapshotKey,
                RowIndex = storagespace.RowIndex,
                ColumnIndex = storagespace.ColumnIndex,
                RowSpan = storagespace.RowSpan,
                ColumnSpan = storagespace.ColumnSpan,
                ChildrenRows = storagespace.ChildrenRows,
                ChildrenColumns = storagespace.ChildrenColumns,
                TypeCode = storagespace.TypeCode,
                Name = storagespace.Name,
                Code = storagespace.Code,
                SerialNo = storagespace.SerialNo,
                DeviceKey = storagespace.DeviceKey,
                CreateBy = storagespace.CreateBy,
                CreateTime = storagespace.CreateTime,
                Key = storagespace.Key,
                Flag = storagespace.Flag,
                ClientTime = storagespace.ClientTime,
                ServerTime = storagespace.ServerTime,
                Ext01 = storagespace.Ext01,
                Ext02 = storagespace.Ext02,
                Ext03 = storagespace.Ext03,
                Ext04 = storagespace.Ext04,
                Ext05 = storagespace.Ext05,
                Ext06 = storagespace.Ext06,
                Ext07 = storagespace.Ext07,
                Ext08 = storagespace.Ext08,
                Ext09 = storagespace.Ext09,
                Ext10 = storagespace.Ext10,   //Params
            };
        }
        public StorageSpace GetByRoomIdAndSerialNo(String roomId, String serialNo)
        {
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append("SELECT ");
            foreach (KeyValuePair<String, String> pair in this.ColumnPropMaps)
            {
                sqlBuilder.AppendFormat("s.{0} {1},", pair.Key, pair.Value);
            }
            sqlBuilder.Remove(sqlBuilder.Length - 1, 1);
            sqlBuilder.AppendFormat(" FROM {0} s"
                + " INNER JOIN dispensing_device d ON d.key=s.device_key"
                + " INNER JOIN section sec ON sec.key = d.operation_room_key"
                + " INNER JOIN operation_room r ON r.ext01 = sec.key"
                + " WHERE s.ext02=:serialNo AND r.room_id =:roomId",
                this.TABLE_NAME);
            return SqlMapper.Query<StorageSpace>(ConnectionFactory.Current.GetSessionConnection(),
                sqlBuilder.ToString(),
                new { serialNo = serialNo, roomId = roomId })
                .FirstOrDefault();
        }
        public IEnumerable<StorageSpace> ListMedKitWithDeviceInfo()
        {
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append("SELECT ");
            foreach (KeyValuePair<String, String> pair in this.ColumnPropMaps)
            {
                sqlBuilder.AppendFormat("s.{0} {1},", pair.Key, pair.Value);
            }
            //DispensingDeviceBLL
            sqlBuilder.Append("'' spiltor1,");
            DispensingDeviceBLL dispensingDeviceBLL = new DispensingDeviceBLL();
            foreach (KeyValuePair<String, String> pair in dispensingDeviceBLL.ColumnPropMaps)
            {
                sqlBuilder.AppendFormat("d.{0} {1},", pair.Key, pair.Value);
            }
            //DispensingDeviceBLL
            sqlBuilder.Append("'' spiltor2,");
            SectionBLL sectionBLL = new SectionBLL();
            foreach (KeyValuePair<String, String> pair in sectionBLL.ColumnPropMaps)
            {
                sqlBuilder.AppendFormat("sec.{0} {1},", pair.Key, pair.Value);
            }
            sqlBuilder.Remove(sqlBuilder.Length - 1, 1);
            sqlBuilder.AppendFormat(" FROM {0} s"
                + " INNER JOIN dispensing_device d ON d.key=s.device_key"
                + " LEFT JOIN section sec ON sec.key = d.operation_room_key"
                + " WHERE s.TYPE_CODE=:storageType"
                + " ORDER BY sec.SECTION_NAME,LPAD(s.ext02,2,'0')",
                this.TABLE_NAME);
            return SqlMapper.Query<StorageSpace, DispensingDevice, Section, StorageSpace>(ConnectionFactory.Current.GetSessionConnection(),
                sqlBuilder.ToString(),
                (s, d, sec) =>
                {
                    s.DispensingDevice = d;
                    s.DispensingDevice.SectionInfo = sec;
                    return s;
                },
                new { storageType = StorageSpaceTypeCodes.MedicalKitPocket },
                splitOn: "spiltor1,spiltor2");
        }
    }

}