using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.ServiceModel; using RDH.Data.Models; namespace RDH.Data.BLL { public partial class ItemBLL : BaseOrclSnapshotBLL, IItemBLL { public ItemBLL() : base() { AliasTableName = "i"; } internal override String TABLE_NAME { get { return "ITEM"; } }//TB_NAME internal override string ENTITY_NAME { get { return "Item"; } } internal override String KEY_COLUMN { get { return "SNAPSHOT_KEY"; } }//KEY_COLUMN internal override string SNAPSHOT_BASE { get { return "ITEM_BASE"; } } internal override void InitMaps() { ColumnPropMaps.Add("SNAPSHOT_KEY", "SnapshotKey"); ColumnPropMaps.Add("CREATE_BY", "CreateBy"); ColumnPropMaps.Add("CREATE_TIME", "CreateTime"); ColumnPropMaps.Add("ITEM_ID", "ItemId"); ColumnPropMaps.Add("BAR_CODE", "BarCode"); ColumnPropMaps.Add("ITEM_COMMON_NAME", "ItemCommonName"); ColumnPropMaps.Add("ITEM_BRAND_NAME", "ItemBrandName"); ColumnPropMaps.Add("PIN_YIN_NAME", "PinYinName"); ColumnPropMaps.Add("IS_ACTIVE", "IsActive"); ColumnPropMaps.Add("ITEM_TYPE", "ItemType"); ColumnPropMaps.Add("IS_MEDCINE", "IsMedcine"); ColumnPropMaps.Add("IS_SUPPLY", "IsSupply"); ColumnPropMaps.Add("STANDARD", "Standard"); ColumnPropMaps.Add("DOSAGE_CODE", "DosageCode"); ColumnPropMaps.Add("DOSAGE_NAME", "DosageName"); ColumnPropMaps.Add("WEIGHT", "Weight"); ColumnPropMaps.Add("VOLUMN", "Volumn"); ColumnPropMaps.Add("WEIGHT_UNIT", "WeightUnit"); ColumnPropMaps.Add("VOLUMN_UNIT", "VolumnUnit"); ColumnPropMaps.Add("USE_UNIT", "UseUnit"); ColumnPropMaps.Add("PACK_STANDARD", "PackStandard"); ColumnPropMaps.Add("PACK_SIZE", "PackSize"); ColumnPropMaps.Add("PACK_UNIT", "PackUnit"); ColumnPropMaps.Add("MANUFACTORY", "Manufactory"); 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"); ColumnPropMaps.Add("CHARGE_TYPE", "ChargeType");//COLUMN_MAPS } internal override object GetParam(Item item) { return new { SnapshotKey = item.SnapshotKey, CreateBy = item.CreateBy, CreateTime = item.CreateTime, ItemId = item.ItemId, BarCode = item.BarCode, ItemCommonName = item.ItemCommonName, ItemBrandName = item.ItemBrandName, PinYinName = item.PinYinName, IsActive = item.IsActive, IsDelete = item.IsDelete, ItemType = item.ItemType, IsMedcine = item.IsMedcine, IsSupply = item.IsSupply, Standard = item.Standard, DosageCode = item.DosageCode, DosageName = item.DosageName, Weight = item.Weight, Volumn = item.Volumn, WeightUnit = item.WeightUnit, VolumnUnit = item.VolumnUnit, UseUnit = item.UseUnit, PackStandard = item.PackStandard, PackSize = item.PackSize, PackUnit = item.PackUnit, Manufactory = item.Manufactory, 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, ChargeType = item.ChargeType, //Params }; } public IEnumerable ListItems(string filter, int index, int size) { string sqlWhere = string.Format(" WHERE IS_ACTIVE = 'True' AND (UPPER(PIN_YIN_NAME) LIKE '%{0}%' OR ITEM_COMMON_NAME LIKE '%{0}%' OR ITEM_BRAND_NAME LIKE '%{0}%') ", filter == null ? "" : filter.ToUpper()); var sql = BuildPageSql(sqlWhere, index, size); return SqlMapper.Query(ConnectionFactory.Current.GetSessionConnection(), sql); } public Item GetByItemId(string itemId) { var sql = BuildBaseSql(); return SqlMapper.Query(ConnectionFactory.Current.GetSessionConnection(), sql + " WHERE ITEM_ID = :ItemId", new { ItemId = itemId }).FirstOrDefault(); } public Item GetByItemCurrentId(string itemId) { var sql = BuildBaseSql(); return SqlMapper.Query(ConnectionFactory.Current.GetSessionConnection(), sql + " WHERE EXT01 = :Ext01", new { Ext01 = itemId }).FirstOrDefault(); } public Item GetIsActiveItem(Item _itemKey) { var sql = string.Format("{0} WHERE KEY=:itemKey", BuildBaseSql()); return SqlMapper.Query(ConnectionFactory.Current.GetSessionConnection(), sql, new { itemKey = _itemKey.Key.GetValueOrDefault().ToString() }).FirstOrDefault(); } public int GetCount(string filter) { var sql = this.BuildPageCountSql(" WHERE IS_ACTIVE = 'True'"); if (!string.IsNullOrWhiteSpace(filter)) { sql += string.Format(" AND (UPPER(ITEM_COMMON_NAME) like '%{0}%' OR UPPER(ITEM_BRAND_NAME) LIKE '%{0}%' OR UPPER(PIN_YIN_NAME) LIKE '%{0}%')", filter.ToUpper()); } var command = ConnectionFactory.Current.GetSessionConnection().CreateCommand(); command.CommandText = sql; command.CommandType = System.Data.CommandType.Text; return Convert.ToInt32(command.ExecuteScalar()); } public List ListItemsWithItemLot(string filter, int index, int size) { StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder.Append("SELECT "); foreach (KeyValuePair pair in this.ColumnPropMaps) { sqlBuilder.AppendFormat("{0}.{1} {2},", this.TABLE_NAME, pair.Key, pair.Value); } sqlBuilder.Append("'' spiltor1,"); ItemLotBLL bllLot = new ItemLotBLL(); foreach (KeyValuePair pair in bllLot.ColumnPropMaps) { sqlBuilder.AppendFormat("{0}.{1} {2},", bllLot.TABLE_NAME, pair.Key, pair.Value); } sqlBuilder.Remove(sqlBuilder.Length - 1, 1); sqlBuilder.AppendFormat(" FROM {0}" + " LEFT JOIN {1} ON {0}.KEY={1}.ITEM_KEY" + " WHERE {0}.IS_ACTIVE='True'", this.TABLE_NAME, bllLot.TABLE_NAME); if (!string.IsNullOrWhiteSpace(filter)) { sqlBuilder.AppendFormat(" AND (UPPER({0}.ITEM_COMMON_NAME) like '%{1}%' OR UPPER({0}.ITEM_BRAND_NAME) LIKE '%{1}%' OR UPPER({0}.PIN_YIN_NAME) LIKE '%{1}%')", this.TABLE_NAME, filter.ToUpper()); } List listResult = new List(); SqlMapper.Query(ConnectionFactory.Current.GetSessionConnection(), BuildPageSql(sqlBuilder.ToString(), index, size), (i, il) => { Item existItem = listResult.Find(x => x.Key == i.Key); if (existItem == null) { existItem = i; listResult.Add(i); i.ListItemLots = new List(); } if (il != null) { existItem.ListItemLots.Add(il); } return i; }, splitOn: "spiltor1"); return listResult; } public IEnumerable ListItemsWithConfigCount(DispensingDevice device, String filter) { SqlBuilder builder = new SqlBuilder(this); builder.AppendSelectionPhrases("(SELECT COUNT(*) FROM STORAGE_SPACE_ITEM si WHERE si.item_key=i.KEY AND si.device_key=:deviceKey) AS ConfigedCount"); if (!String.IsNullOrWhiteSpace(filter)) { filter = filter.Trim().ToUpper(); } builder.AppendWherePhrases(String.Format("(UPPER(i.PIN_YIN_NAME) LIKE '%{0}%' OR i.ITEM_COMMON_NAME LIKE '%{0}%' OR i.ITEM_BRAND_NAME LIKE '%{0}%')", filter.Trim().ToUpper())); return SqlMapper.Query(ConnectionFactory.Current.GetSessionConnection(), builder.ToString(), new { deviceKey = device.Key }); } } }