EleKitService/RDH.Data/PortableStorageItemBLL.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 PortableStorageItemBLL : BaseOrclBLL<PortableStorageItem>
    {
        public PortableStorageItemBLL() : base()
        {
            AliasTableName = "psi";
        }

        internal override String TABLE_NAME { get { return "PORTABLE_STORAGE_ITEM"; } }//TB_NAME
        internal override string ENTITY_NAME { get { return "PortableStorageItem"; } }
        internal override String KEY_COLUMN { get { return "KEY"; } }//KEY_COLUMN

        internal override void InitMaps()
        {
            ColumnPropMaps.Add("KEY", "Key");
            ColumnPropMaps.Add("PORTABLE_STORAGE_KEY", "PortableStorageKey");
            ColumnPropMaps.Add("MAX_QUANTITY", "MaxQuantity");
            ColumnPropMaps.Add("ALART_QUANTITY", "AlartQuantity");
            ColumnPropMaps.Add("CURRENT_QUANTITY", "CurrentQuantity");
            ColumnPropMaps.Add("ITEM_KEY", "ItemKey");
            ColumnPropMaps.Add("CREATE_BY", "CreateBy");
            ColumnPropMaps.Add("CREATE_TIME", "CreateTime");
            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");
        }

        internal override object GetParam(PortableStorageItem item)
        {
            return new
            {
                AlartQuantity = item.AlartQuantity,
                CurrentQuantity = item.CurrentQuantity,
                MaxQuantity = item.MaxQuantity,
                PortableStorageKey = item.PortableStorageKey,
                ItemKey = item.ItemKey,
                CreateBy = item.CreateBy,
                CreateTime = item.CreateTime,
                Key = item.Key,
                Flag = item.Flag,
                ClientTime = item.ClientTime,
                ServerTime = item.ServerTime,
                Ext01 = item.Ext01,
                Ext02 = item.Ext02,
                Ext03 = item.Ext03,
                Ext04 = item.Ext04,
                Ext05 = item.Ext05,
                Ext06 = item.Ext06,
                Ext07 = item.Ext07,
                Ext08 = item.Ext08,
                Ext09 = item.Ext09,
                Ext10 = item.Ext10,
            };
        }
        public List<PortableStorageItem> ListPortableStorageItemsWithItemLot(Guid? storageKey)
        {
            #region sqlbuilder
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append("SELECT ");
            foreach (KeyValuePair<String, String> pair in this.ColumnPropMaps)
            {
                sqlBuilder.AppendFormat("{0}.{1} {2},", this.TABLE_NAME, pair.Key, pair.Value);
            }
            sqlBuilder.Append("'' spiltor1,");
            ItemBLL bllItem = new ItemBLL();
            foreach (KeyValuePair<String, String> pair in bllItem.ColumnPropMaps)
            {
                sqlBuilder.AppendFormat("{0}.{1} {2},", bllItem.TABLE_NAME, pair.Key, pair.Value);
            }
            sqlBuilder.Append("'' spiltor2,");
            PortableStorageItemLotBLL bllLot = new PortableStorageItemLotBLL();
            foreach (KeyValuePair<String, String> pair in bllLot.ColumnPropMaps)
            {
                sqlBuilder.AppendFormat("{0}.{1} {2},", bllLot.TABLE_NAME, pair.Key, pair.Value);
            }
            sqlBuilder.Append("'' spiltor3,");
            ItemLotBLL bllIL = new ItemLotBLL();
            foreach (KeyValuePair<String, String> pair in bllIL.ColumnPropMaps)
            {
                sqlBuilder.AppendFormat("{0}.{1} {2},", bllIL.TABLE_NAME, pair.Key, pair.Value);
            }
            sqlBuilder.Remove(sqlBuilder.Length - 1, 1);
            sqlBuilder.AppendFormat(" FROM {0}"
                + " INNER JOIN {2} ON {0}.ITEM_KEY={2}.KEY "
                + " LEFT JOIN {1} ON {0}.KEY={1}.PORTABLE_ITEM_KEY"
                + " LEFT JOIN {3} ON {3}.KEY={1}.LOT_KEY"
                + " WHERE {0}.PORTABLE_STORAGE_KEY=:storageKey",
                this.TABLE_NAME, bllLot.TABLE_NAME, bllItem.TABLE_NAME, bllIL.TABLE_NAME);
            #endregion
            List<PortableStorageItem> listResult = new List<PortableStorageItem>();
            SqlMapper.Query<PortableStorageItem, Item, PortableStorageItemLot, ItemLot, PortableStorageItem>(
                ConnectionFactory.Current.GetSessionConnection(),
                sqlBuilder.ToString(),
                (link, i, detail, il) =>
                {
                    PortableStorageItem existItem = listResult.Find(x => x.Key == link.Key);
                    if (existItem == null)
                    {
                        existItem = link;
                        existItem.Item = i;
                        listResult.Add(link);
                        link.ListPortableItemLots = new List<PortableStorageItemLot>();
                    }
                    if (detail != null)
                    {
                        detail.ItemLot = il;
                        detail.Item = i;
                        existItem.ListPortableItemLots.Add(detail);
                    }
                    return link;
                },
                new { storageKey = storageKey.GetValueOrDefault() },
                splitOn: "spiltor1,spiltor2,spiltor3");
            return listResult;
        }

        /// <summary>
        /// 根据指定的药箱状态获取药箱药品配置
        /// </summary>
        public IEnumerable<PortableStorageItem> ListPortableStorageItemsByStorage(StorageSpace storage)
        {
            #region sqlbuilder
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append("SELECT ");
            foreach (KeyValuePair<String, String> pair in this.ColumnPropMaps)
            {
                sqlBuilder.AppendFormat("psi.{0} {1},", pair.Key, pair.Value);
            }
            sqlBuilder.Append("'' spiltor1,");
            ItemBLL bllItem = new ItemBLL();
            foreach (KeyValuePair<String, String> pair in bllItem.ColumnPropMaps)
            {
                sqlBuilder.AppendFormat("i.{0} {1},", pair.Key, pair.Value);
            }
            sqlBuilder.Append("'' spiltor2,");
            StorageSpacePortableLinkBLL storageSpacePortableLinkBll = new StorageSpacePortableLinkBLL();
            foreach (KeyValuePair<String, String> pair in storageSpacePortableLinkBll.ColumnPropMaps)
            {
                sqlBuilder.AppendFormat("l.{0} {1},", pair.Key, pair.Value);
            }
            sqlBuilder.Remove(sqlBuilder.Length - 1, 1);
            sqlBuilder.AppendFormat(" FROM {0} psi"
                + " INNER JOIN {1} i ON psi.ITEM_KEY=i.KEY "
                + " INNER JOIN portable_storage ps ON ps.key=psi.portable_storage_key"
                + " INNER JOIN storage_space_portable_link l ON l.portable_storage_key=ps.key",
                this.TABLE_NAME, bllItem.TABLE_NAME);
            sqlBuilder.Append(" WHERE l.STORAGE_SPACE_KEY=:storageKey");
            #endregion
            return SqlMapper.Query<PortableStorageItem, Item, StorageSpacePortableLink, PortableStorageItem>(
                ConnectionFactory.Current.GetSessionConnection(),
                sqlBuilder.ToString(),
                (psi, i, link) =>
                {
                    psi.Item = i;
                    psi.StoragePortableLinkInfo = link;
                    return psi;
                },
                new { storageKey = storage.Key.GetValueOrDefault() },
                splitOn: "spiltor1,spiltor2");
        }
        /// <summary>
        /// 根据指定的药箱状态获取药箱药品配置
        /// </summary>
        public IEnumerable<PortableStorageItem> ListPortableStorageItemsByPortableState(String state)
        {
            #region sqlbuilder
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append("SELECT ");
            foreach (KeyValuePair<String, String> pair in this.ColumnPropMaps)
            {
                sqlBuilder.AppendFormat("psi.{0} {1},", pair.Key, pair.Value);
            }
            sqlBuilder.Append("'' spiltor1,");
            ItemBLL bllItem = new ItemBLL();
            foreach (KeyValuePair<String, String> pair in bllItem.ColumnPropMaps)
            {
                sqlBuilder.AppendFormat("i.{0} {1},", pair.Key, pair.Value);
            }
            sqlBuilder.Append("'' spiltor2,");
            StorageSpacePortableLinkBLL storageSpacePortableLinkBll = new StorageSpacePortableLinkBLL();
            foreach (KeyValuePair<String, String> pair in storageSpacePortableLinkBll.ColumnPropMaps)
            {
                sqlBuilder.AppendFormat("l.{0} {1},", pair.Key, pair.Value);
            }
            sqlBuilder.Remove(sqlBuilder.Length - 1, 1);
            sqlBuilder.AppendFormat(" FROM {0} psi"
                + " INNER JOIN {1} i ON psi.ITEM_KEY=i.KEY "
                + " INNER JOIN portable_storage ps ON ps.key=psi.portable_storage_key"
                + " INNER JOIN storage_space_portable_link l ON l.portable_storage_key=ps.key",
                this.TABLE_NAME, bllItem.TABLE_NAME);
            if (!String.IsNullOrEmpty(state))
            {
                sqlBuilder.Append(" WHERE l.ext01=:linkState");
            }
            #endregion
            return SqlMapper.Query<PortableStorageItem, Item, StorageSpacePortableLink, PortableStorageItem>(
                ConnectionFactory.Current.GetSessionConnection(),
                sqlBuilder.ToString(),
                (psi, i, link) =>
                {
                    psi.Item = i;
                    psi.StoragePortableLinkInfo = link;
                    return psi;
                },
                new { linkState = state },
                splitOn: "spiltor1,spiltor2");
        }
        public IEnumerable<PortableStorageItem> ListPortableStorageItemStatistics(DispensingDevice device)
        {
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append("SELECT SUM(psi.max_quantity) MaxQuantity,SUM(psi.current_quantity) CurrentQuantity,"
                + " '' spiltor,i.item_common_name ItemCommonName,i.standard Standard,i.use_unit UseUnit,i.manufactory Manufactory,i.pin_yin_name PinYinName"
                + " FROM portable_storage_item psi"
                + " INNER JOIN item i ON i.key = psi.item_key "
                + " INNER JOIN storage_space_portable_link l ON l.portable_storage_key = psi.portable_storage_key"
                + " INNER JOIN storage_space s ON s.key = l.storage_space_key ");
            if (device != null)
            {
                sqlBuilder.Append(" WHERE s.device_key=:deviceKey");
            }
            sqlBuilder.Append(" GROUP BY i.item_common_name, i.standard, i.use_unit, i.manufactory,i.pin_yin_name");
            return SqlMapper.Query<PortableStorageItem, Item, PortableStorageItem>(ConnectionFactory.Current.GetSessionConnection(),
               sqlBuilder.ToString(),
               (psi, i) =>
               {
                   psi.Item = i;
                   return psi;
               },
               new { deviceKey = device == null ? default(Guid) : device.Key },
               splitOn: "spiltor");
        }
        public PortableStorageItem GetByStorageAndItem(Guid portableStorageKey, Guid itemKey) 
        {
            return SqlMapper.Query<PortableStorageItem>(
                ConnectionFactory.Current.GetSessionConnection(),
                BuildBaseSql() + " WHERE PORTABLE_STORAGE_KEY=:storageKey AND ITEM_KEY=:itemKey",
                new
                {
                    storageKey = portableStorageKey,
                    itemKey = itemKey,
                }).FirstOrDefault();
        }
        public PortableStorageItem GetByStorageAndItem(String storageCode, Guid itemKey)
        {
            SqlBuilder sqlBuilder = new SqlBuilder(this);
            StorageSpacePortableLinkBLL storageSpacePortableLinkBLL = new StorageSpacePortableLinkBLL();
            TableJoinInfo ssplTable = new TableJoinInfo
            {
                LeftDal = this,
                LeftColumnName = "portable_storage_key",
                RightDal = storageSpacePortableLinkBLL,
                RightColumnName = "portable_storage_key",
                JoinType = TableJoinTypes.Inner,
                DonotMapFlag = true,
            };
            sqlBuilder.AppendSelectionTable(ssplTable);
            StorageSpaceBLL storageSpaceBLL = new StorageSpaceBLL();
            TableJoinInfo storageTable = new TableJoinInfo
            {
                LeftDal = storageSpacePortableLinkBLL,
                LeftColumnName = "storage_space_key",
                RightDal = storageSpaceBLL,
                RightColumnName = "key",
                JoinType = TableJoinTypes.Inner,
                DonotMapFlag = true,
            };
            sqlBuilder.AppendSelectionTable(storageTable);
            sqlBuilder.AppendWherePhrases($"{storageSpaceBLL.AliasTableName}.ext02=:storageCode AND {this.AliasTableName}.ITEM_KEY=:itemKey");
            return SqlMapper.Query<PortableStorageItem>(
                ConnectionFactory.Current.GetSessionConnection(),
                sqlBuilder.ToString(),
                new
                {
                    storageCode = storageCode,
                    itemKey = itemKey,
                }).FirstOrDefault();
        }
    }

}