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