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