EleKitService/RDH.Data/ItemLotBLL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ServiceModel;
using RDH.Data.Models;
using System.Data;

namespace RDH.Data.BLL
{
    public partial class ItemLotBLL : BaseOrclBLL<ItemLot>, IItemLotBLL
    {
        public ItemLotBLL() : base()
        {
            AliasTableName = "il";
        }
        internal override String TABLE_NAME { get { return "ITEM_LOT"; } }//TB_NAME
        internal override string ENTITY_NAME { get { return "ItemLot"; } }
        internal override String KEY_COLUMN { get { return "KEY"; } }//KEY_COLUMN
        internal override void InitMaps()
        {
            ColumnPropMaps.Add("KEY", "Key");
            ColumnPropMaps.Add("EXP_DATE", "ExpDate");
            ColumnPropMaps.Add("LOT_NO", "LotNo");
            ColumnPropMaps.Add("IS_ACTIVE", "IsActive");
            ColumnPropMaps.Add("CREATE_BY", "CreateBy");
            ColumnPropMaps.Add("CREATE_TIME", "CreateTime");
            ColumnPropMaps.Add("ITEM_KEY", "ItemKey");
            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("ITEM_ID", "ItemId");//COLUMN_MAPS
        }
        internal override object GetParam(ItemLot itemlot)
        {
            return new
            {
                Key = itemlot.Key,
                ExpDate = itemlot.ExpDate,
                LotNo = itemlot.LotNo,
                IsActive = itemlot.IsActive,
                IsDelete = itemlot.IsDelete,
                CreateBy = itemlot.CreateBy,
                CreateTime = itemlot.CreateTime,
                ItemKey = itemlot.ItemKey,
                Flag = itemlot.Flag,
                ClientTime = itemlot.ClientTime,
                ServerTime = itemlot.ServerTime,
                Ext01 = itemlot.Ext01,
                Ext02 = itemlot.Ext02,
                Ext03 = itemlot.Ext03,
                Ext04 = itemlot.Ext04,
                Ext05 = itemlot.Ext05,
                Ext06 = itemlot.Ext06,
                Ext07 = itemlot.Ext07,
                Ext08 = itemlot.Ext08,
                Ext09 = itemlot.Ext09,
                Ext10 = itemlot.Ext10,
                ItemId = itemlot.ItemId,   //Params
            };
        }
        public int GetCount(string filter)
        {
            String sql = "SELECT COUNT(il.key)"
                + "  FROM item i"
                + "  LEFT JOIN item_lot il ON i.key=il.item_key"
                + "  WHERE 1=1";
            if (!string.IsNullOrWhiteSpace(filter))
            {
                sql += string.Format(" AND (UPPER(i.ITEM_COMMON_NAME) like '%{0}%' OR UPPER(i.ITEM_BRAND_NAME) LIKE '%{0}%' OR UPPER(i.PIN_YIN_NAME) LIKE '%{0}%')",
                    filter.ToUpper());
            }
            using (IDbCommand command = ConnectionFactory.Current.GetSessionConnection().CreateCommand())
            {
                command.CommandText = sql;
                command.CommandType = System.Data.CommandType.Text;
                return Convert.ToInt32(command.ExecuteScalar());
            }
        }
        public List<ItemLot> ListItemLots(String filter, Int32 index, Int32 paseSize)
        {
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append("SELECT t2.*,'' spiltor1,");
            ItemBLL bllItem = new ItemBLL();
            foreach (KeyValuePair<String, String> pair in bllItem.ColumnPropMaps)
            {
                sqlBuilder.AppendFormat("i.{0} {1},", pair.Key, pair.Value);
            }
            sqlBuilder.Remove(sqlBuilder.Length - 1, 1);
            sqlBuilder.Append(" FROM (SELECT t1.*,ROWNUM rn FROM (SELECT ");
            foreach (KeyValuePair<String, String> pair in this.ColumnPropMaps)
            {
                sqlBuilder.AppendFormat("{0}.{1} {2},", this.TABLE_NAME, pair.Key, pair.Value);
            }
            sqlBuilder.Remove(sqlBuilder.Length - 1, 1);
            sqlBuilder.AppendFormat(" FROM {0}"
                + " RIGHT JOIN {1} ON {1}.KEY={0}.ITEM_KEY ",
                //+ " WHERE {0}.IS_ACTIVE = 'True'", 
                this.TABLE_NAME, bllItem.TABLE_NAME);
            if (!String.IsNullOrEmpty(filter))
            {
                sqlBuilder.AppendFormat(" AND (UPPER({0}.PIN_YIN_NAME) LIKE '%{1}%' OR {0}.ITEM_COMMON_NAME LIKE '%{1}%' OR {0}.ITEM_BRAND_NAME LIKE '%{1}%')",
                    bllItem.TABLE_NAME, filter.ToUpper());
            }
            sqlBuilder.Append(" ORDER BY ITEM.Item_Common_Name) t1) t2");
            sqlBuilder.AppendFormat(" RIGHT JOIN {0} i ON i.KEY=t2.ItemKey ", bllItem.TABLE_NAME);
            sqlBuilder.AppendFormat(" WHERE t2.rn>{0} AND t2.rn<={1}", index.ToString(), (index + paseSize).ToString());
            return SqlMapper.Query<ItemLot, Item, ItemLot>(ConnectionFactory.Current.GetSessionConnection(),
                sqlBuilder.ToString(),
                (l, i) =>
                {
                    if (l == null)
                    {
                        l = new ItemLot();
                    }
                    l.Item = i;
                    return l;
                },
                splitOn: "spiltor1").ToList();
        }
    }

}