EleKitService/RDH.Data/ItemBLL.cs

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<Item>, 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<Item> 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<Item>(ConnectionFactory.Current.GetSessionConnection(), sql);
        }
        public Item GetByItemId(string itemId)
        {
            var sql = BuildBaseSql();
            return SqlMapper.Query<Item>(ConnectionFactory.Current.GetSessionConnection(),
                sql + "  WHERE ITEM_ID = :ItemId",
                new { ItemId = itemId }).FirstOrDefault();
        }

        public Item GetByItemCurrentId(string itemId)
        {
            var sql = BuildBaseSql();
            return SqlMapper.Query<Item>(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<Item>(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<Item> ListItemsWithItemLot(string filter, int index, int size)
        {
            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,");
            ItemLotBLL bllLot = new ItemLotBLL();
            foreach (KeyValuePair<String, String> 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<Item> listResult = new List<Item>();
            SqlMapper.Query<Item, ItemLot, Item>(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<ItemLot>();
                    }
                    if (il != null)
                    {
                        existItem.ListItemLots.Add(il);
                    }
                    return i;
                },
                splitOn: "spiltor1");
            return listResult;
        }
        public IEnumerable<Item> 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<Item>(ConnectionFactory.Current.GetSessionConnection(),
                builder.ToString(),
                new
                {
                    deviceKey = device.Key
                });
        }
    }

}