public IList <OrderPickProductInfo> GetListByJoin(int pageIndex, int pageSize, string sqlWhere, params SqlParameter[] cmdParms)
{
StringBuilder sb = new StringBuilder(500);
int startIndex = (pageIndex - 1) * pageSize + 1;
int endIndex = pageIndex * pageSize;
sb.Append(@"select * from(select row_number() over(order by opp.LastUpdatedDate desc) as RowNumber,
opp.OrderPickId,opp.OrderId,opp.ProductId,opp.CustomerId,opp.StayQty,opp.Qty,opp.StockLocations,opp.Status,opp.LastUpdatedDate
,op.OrderCode,p.ProductCode,p.ProductName,c.Coded CustomerCode,c.Named CustomerName
from OrderPickProduct opp
left join OrderPicked op on op.Id = opp.OrderPickId
left join Product p on p.Id = opp.ProductId
left join Customer c on c.Id = opp.CustomerId
");
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
sb.AppendFormat(@")as objTable where RowNumber between {0} and {1} ", startIndex, endIndex);
var list = new List <OrderPickProductInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.WmsDbConnString, CommandType.Text, sb.ToString(), cmdParms))
{
if (reader != null && reader.HasRows)
{
var slpBll = new StockLocationProduct();
while (reader.Read())
{
var model = new OrderPickProductInfo();
model.OrderPickId = reader.GetGuid(1);
model.OrderId = reader.GetGuid(2);
model.ProductId = reader.GetGuid(3);
model.CustomerId = reader.GetGuid(4);
model.StayQty = reader.GetDouble(5);
model.Qty = reader.GetDouble(6);
model.StockLocations = reader.GetString(7);
model.Status = reader.GetString(8);
model.LastUpdatedDate = reader.GetDateTime(9);
model.OrderCode = reader.IsDBNull(10) ? "" : reader.GetString(10);
model.ProductCode = reader.IsDBNull(11) ? "" : reader.GetString(11);
model.ProductName = reader.IsDBNull(12) ? "" : reader.GetString(12);
model.CustomerCode = reader.IsDBNull(13) ? "" : reader.GetString(13);
model.CustomerName = reader.IsDBNull(14) ? "" : reader.GetString(14);
model.StockLocations = slpBll.GetNameByProductId(model.ProductId);
list.Add(model);
}
}
}
return(list);
}
public IList <StockProductInfo> GetListByJoin(string sqlWhere, params SqlParameter[] cmdParms)
{
var list = new List <StockProductInfo>();
var sb = new StringBuilder(1000);
sb.Append(@"select sp.ProductId,sp.CustomerId,sp.Qty,sp.UnQty,sp.FreezeQty,sp.StepCode,sp.LastStepName,sp.Status,sp.StockLocations,sp.LastUpdatedDate
,c.Coded CustomerCode,c.Named CustomerName,p.ProductCode,p.ProductName
from StockProduct sp
left join Customer c on c.Id = sp.CustomerId
left join Product p on p.Id = sp.ProductId
");
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
sb.Append("order by LastUpdatedDate desc ");
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.WmsDbConnString, CommandType.Text, sb.ToString(), cmdParms))
{
if (reader != null && reader.HasRows)
{
var slpBll = new StockLocationProduct();
while (reader.Read())
{
var model = new StockProductInfo();
model.ProductId = reader.GetGuid(0);
model.CustomerId = reader.GetGuid(1);
model.Qty = reader.GetDouble(2);
model.UnQty = reader.GetDouble(3);
model.FreezeQty = reader.GetDouble(4);
model.StepCode = reader.GetString(5);
model.LastStepName = reader.GetString(6);
model.Status = reader.GetString(7);
model.StockLocations = reader.GetString(8);
model.LastUpdatedDate = reader.GetDateTime(9);
model.CustomerCode = reader.IsDBNull(10) ? "" : reader.GetString(10);
model.CustomerName = reader.IsDBNull(11) ? "" : reader.GetString(11);
model.ProductCode = reader.IsDBNull(12) ? "" : reader.GetString(12);
model.ProductName = reader.IsDBNull(13) ? "" : reader.GetString(13);
model.StockLocationName = slpBll.GetNameByProductId(model.ProductId);
list.Add(model);
}
}
}
return(list);
}
