|
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298 |
- 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();
- }
- }
-
- }
|