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