public StockLocationProductInfo GetModelByJoin(Guid Id) { var cmdText = @"select sl.Id,sl.Code,sl.Named,sl.Volume,slp.StockLocationId,slp.ProductAttr,slp.MaxVolume from StockLocation sl left join StockLocationProduct slp on slp.StockLocationId = sl.Id where sl.Id = @Id "; var parm = new SqlParameter("@Id", Id); StockLocationProductInfo model = null; using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.WmsDbConnString, CommandType.Text, cmdText, parm)) { if (reader != null && reader.HasRows) { if (reader.Read()) { model = new StockLocationProductInfo(); model.StockLocationId = reader.GetGuid(0); model.StockLocationCode = reader.GetString(1); model.StockLocationName = reader.GetString(2); model.Volume = reader.GetDouble(3); model.IsHas = !reader.IsDBNull(4); model.ProductAttr = reader.IsDBNull(5) ? "" : reader.GetString(5); model.MaxVolume = reader.IsDBNull(6) ? 0 : reader.GetDouble(6); } } } return(model); }
public IList <StockLocationProductInfo> GetList(string sqlWhere, params SqlParameter[] cmdParms) { StringBuilder sb = new StringBuilder(500); sb.Append(@"select StockLocationId,ProductAttr,MaxVolume from StockLocationProduct "); if (!string.IsNullOrEmpty(sqlWhere)) { sb.AppendFormat(" where 1=1 {0} ", sqlWhere); } sb.Append("order by LastUpdatedDate "); IList <StockLocationProductInfo> list = new List <StockLocationProductInfo>(); using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.WmsDbConnString, CommandType.Text, sb.ToString(), cmdParms)) { if (reader != null && reader.HasRows) { while (reader.Read()) { StockLocationProductInfo model = new StockLocationProductInfo(); model.StockLocationId = reader.GetGuid(0); model.ProductAttr = reader.GetString(1); model.MaxVolume = reader.GetDouble(2); list.Add(model); } } } return(list); }
public StockLocationProductInfo GetModel(Guid stockLocationId) { StockLocationProductInfo model = null; StringBuilder sb = new StringBuilder(300); sb.Append(@"select top 1 StockLocationId,ProductAttr,MaxVolume from StockLocationProduct where StockLocationId = @StockLocationId " ); SqlParameter[] parms = { new SqlParameter("@StockLocationId", SqlDbType.UniqueIdentifier) }; parms[0].Value = stockLocationId; using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.WmsDbConnString, CommandType.Text, sb.ToString(), parms)) { if (reader != null) { if (reader.Read()) { model = new StockLocationProductInfo(); model.StockLocationId = reader.GetGuid(0); model.ProductAttr = reader.GetString(1); model.MaxVolume = reader.GetDouble(2); } } } return(model); }
public IList <StockLocationProductInfo> GetList() { StringBuilder sb = new StringBuilder(300); sb.Append(@"select StockLocationId,ProductAttr,MaxVolume from StockLocationProduct order by LastUpdatedDate " ); IList <StockLocationProductInfo> list = new List <StockLocationProductInfo>(); using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.WmsDbConnString, CommandType.Text, sb.ToString())) { if (reader != null && reader.HasRows) { while (reader.Read()) { StockLocationProductInfo model = new StockLocationProductInfo(); model.StockLocationId = reader.GetGuid(0); model.ProductAttr = reader.GetString(1); model.MaxVolume = reader.GetDouble(2); list.Add(model); } } } return(list); }
public IList <StockLocationProductInfo> GetList(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 LastUpdatedDate) as RowNumber, StockLocationId,ProductAttr,MaxVolume from StockLocationProduct " ); if (!string.IsNullOrEmpty(sqlWhere)) { sb.AppendFormat(" where 1=1 {0} ", sqlWhere); } sb.AppendFormat(@")as objTable where RowNumber between {0} and {1} ", startIndex, endIndex); IList <StockLocationProductInfo> list = new List <StockLocationProductInfo>(); using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.WmsDbConnString, CommandType.Text, sb.ToString(), cmdParms)) { if (reader != null && reader.HasRows) { while (reader.Read()) { StockLocationProductInfo model = new StockLocationProductInfo(); model.StockLocationId = reader.GetGuid(1); model.ProductAttr = reader.GetString(2); model.MaxVolume = reader.GetDouble(3); list.Add(model); } } } return(list); }
public IList <StockLocationProductInfo> GetListForOrderSendProduct() { var cmdText = @"select sl.Id,sl.Code,sl.Named,slp.ProductAttr from StockLocation sl join StockLocationProduct slp on slp.StockLocationId = sl.Id join Zone z on z.Id = sl.ZoneId where z.ZoneCode = 'FinishedZ' and ProductAttr != '' "; var list = new List <StockLocationProductInfo>(); using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.WmsDbConnString, CommandType.Text, cmdText)) { if (reader != null && reader.HasRows) { var pDal = new Product(); while (reader.Read()) { var paList = JsonConvert.DeserializeObject <List <StockLocationProductAttrInfo> >(reader.GetString(3)); if (paList.Count > 0) { var q = paList.Where(m => m.Qty > 0).OrderByDescending(m => m.LastUpdatedDate); if (q != null && q.Count() > 0) { foreach (var item in q) { var model = new StockLocationProductInfo(); model.StockLocationId = reader.GetGuid(0); model.StockLocationCode = reader.GetString(1); model.StockLocationName = reader.GetString(2); model.ProductId = item.ProductId; model.MaxQty = item.Qty; list.Add(model); } } } } } } return(list); }
public IList <StockLocationProductInfo> GetListByUsable(Guid productId, double minVolume) { StringBuilder sb = new StringBuilder(300); sb.AppendFormat(@"select sl.Id,sl.Code,sl.Named,isnull(slp.MaxVolume,sl.Volume) MaxVolume,slp.ProductAttr from StockLocation sl left join StockLocationProduct slp on slp.StockLocationId = sl.Id join Zone z on z.Id = sl.ZoneId where z.ZoneCode = 'FinishedZ' and isnull(slp.MaxVolume,sl.Volume) >= {0} ", minVolume); var list = new List <StockLocationProductInfo>(); using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.WmsDbConnString, CommandType.Text, sb.ToString())) { if (reader != null && reader.HasRows) { while (reader.Read()) { var model = new StockLocationProductInfo(); model.StockLocationId = reader.GetGuid(0); model.StockLocationCode = reader.GetString(1); model.StockLocationName = reader.GetString(2); model.MaxVolume = reader.GetDouble(3); if (!reader.IsDBNull(4)) { var slpaList = JsonConvert.DeserializeObject <List <StockLocationProductInfo> >(reader.GetString(4)); var qpList = slpaList.Where(m => m.ProductId.Equals(productId)).OrderByDescending(m => m.LastUpdatedDate); if (qpList != null && qpList.Count() > 0) { model.ProductId = productId; model.LastUpdatedDate = qpList.First().LastUpdatedDate; } } model.MaxQty = model.MaxVolume / minVolume; list.Add(model); } } } return(list); }
public int Update(StockLocationProductInfo model) { StringBuilder sb = new StringBuilder(500); sb.Append(@"update StockLocationProduct set ProductAttr = @ProductAttr,MaxVolume = @MaxVolume where StockLocationId = @StockLocationId " ); SqlParameter[] parms = { new SqlParameter("@StockLocationId", SqlDbType.UniqueIdentifier), new SqlParameter("@ProductAttr", SqlDbType.VarChar), new SqlParameter("@MaxVolume", SqlDbType.Float) }; parms[0].Value = model.StockLocationId; parms[1].Value = model.ProductAttr; parms[2].Value = model.MaxVolume; return(SqlHelper.ExecuteNonQuery(SqlHelper.WmsDbConnString, CommandType.Text, sb.ToString(), parms)); }
public int Insert(StockLocationProductInfo model) { StringBuilder sb = new StringBuilder(300); sb.Append(@"insert into StockLocationProduct (StockLocationId,ProductAttr,MaxVolume) values (@StockLocationId,@ProductAttr,@MaxVolume) " ); SqlParameter[] parms = { new SqlParameter("@StockLocationId", SqlDbType.UniqueIdentifier), new SqlParameter("@ProductAttr", SqlDbType.VarChar), new SqlParameter("@MaxVolume", SqlDbType.Float) }; parms[0].Value = model.StockLocationId; parms[1].Value = model.ProductAttr; parms[2].Value = model.MaxVolume; return(SqlHelper.ExecuteNonQuery(SqlHelper.WmsDbConnString, CommandType.Text, sb.ToString(), parms)); }
public IList <StockLocationProductInfo> GetListForOrderPickProduct(Guid productId) { StringBuilder sb = new StringBuilder(300); sb.AppendFormat(@"select sl.Id,sl.Code,sl.Named,isnull(slp.MaxVolume,sl.Volume) MaxVolume,slp.ProductAttr from StockLocationProduct slp join StockLocation sl on sl.Id = slp.StockLocationId join Zone z on z.Id = sl.ZoneId where z.ZoneCode = 'FinishedZ' and CHARINDEX('{0}', slp.ProductAttr) > 0 ", productId); var list = new List <StockLocationProductInfo>(); using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.WmsDbConnString, CommandType.Text, sb.ToString())) { if (reader != null && reader.HasRows) { while (reader.Read()) { var paList = JsonConvert.DeserializeObject <List <StockLocationProductAttrInfo> >(reader.GetString(4)); var item = paList.FirstOrDefault(m => m.ProductId.Equals(productId)); if (item != null && item.FreezeQty > 0) { var model = new StockLocationProductInfo(); model.StockLocationId = reader.GetGuid(0); model.StockLocationCode = reader.GetString(1); model.StockLocationName = reader.GetString(2); model.MaxVolume = reader.GetDouble(3); model.MaxQty = item.FreezeQty; model.LastUpdatedDate = item.LastUpdatedDate; list.Add(model); } } } } return(list); }
public int Update(StockLocationProductInfo model) { return(dal.Update(model)); }
public int Insert(StockLocationProductInfo model) { return(dal.Insert(model)); }
public void DoProduct(Guid slId, Guid productId, bool isIncrease, double qty, double freezeQty) { var slpInfo = dal.GetModel(slId); if (!isIncrease) { #region 对库位货物“减”处理 if (slpInfo != null) { var slpaList = JsonConvert.DeserializeObject <List <StockLocationProductAttrInfo> >(slpInfo.ProductAttr); var slpaInfo = slpaList.FirstOrDefault(m => m.ProductId.Equals(productId)); if (slpaInfo != null) { if (qty > 0) { slpaInfo.Qty -= qty; if (slpaInfo.Qty < 0) { throw new ArgumentException(MC.GetString(MC.Request_InvalidQty, qty.ToString())); } } if (freezeQty > 0) { slpaInfo.FreezeQty -= freezeQty; if (slpaInfo.FreezeQty < 0) { throw new ArgumentException(MC.GetString(MC.Request_InvalidQty, freezeQty.ToString())); } } if (slpaInfo.Qty <= 0 && slpaInfo.FreezeQty <= 0) { slpaList.Remove(slpaInfo); } slpInfo.ProductAttr = JsonConvert.SerializeObject(slpaList); dal.Update(slpInfo); } } #endregion } else { #region 对库位货物“增”处理 if (slpInfo != null) { #region 已存在相应数据,则执行修改操作 var slpaList = JsonConvert.DeserializeObject <List <StockLocationProductAttrInfo> >(slpInfo.ProductAttr); var slpaInfo = slpaList.FirstOrDefault(m => m.ProductId.Equals(productId)); if (slpaInfo != null) { if (qty > 0) { slpaInfo.Qty += qty; } if (freezeQty > 0) { slpaInfo.FreezeQty += freezeQty; } } else { slpaList.Add(new StockLocationProductAttrInfo(productId, qty, freezeQty, DateTime.Now)); } slpInfo.ProductAttr = JsonConvert.SerializeObject(slpaList); dal.Update(slpInfo); #endregion } else { #region 否则执行新增操作 var slpaList = new List <StockLocationProductAttrInfo>(); slpaList.Add(new StockLocationProductAttrInfo(productId, qty > 0 ? qty : 0, freezeQty > 0 ? freezeQty : 0, DateTime.Now)); var slBll = new StockLocation(); var slInfo = slBll.GetModel(slId); var maxVolume = slInfo.Volume; if (qty > 0) { maxVolume -= qty; } slpInfo = new StockLocationProductInfo(slId, JsonConvert.SerializeObject(slpaList), maxVolume); dal.Insert(slpInfo); #endregion } #endregion } }