using HH.WMS.Common;
|
using HH.WMS.Common.External;
|
using HH.WMS.Entitys.Common;
|
using System;
|
using System.Collections.Generic;
|
using System.Data.Common;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
|
namespace HH.WMS.DAL.External
|
{
|
/// <summary>
|
/// 库区库存数量数据处理类
|
/// </summary>
|
/// <history>[HanHe(HHC)] CREATED 2018/3/5</history>
|
public class TN_WM_B_AREA_QTYDAL : BaseDAL
|
{
|
#region 影响库区量表
|
public SqlExecuteResult AddAreaQty(List<HH.WMS.Entitys.Basic.TN_WM_B_AREA_QTYEntity> stockAreaQtyList, DbTransaction trans)
|
{
|
SqlExecuteResult result = new SqlExecuteResult();
|
foreach (var model in stockAreaQtyList)
|
{
|
StringBuilder strSql = new StringBuilder();
|
string sqlWhere = " Where CN_S_STOCK_CODE='" + model.CN_S_STOCK_CODE + @"'
|
and CN_S_ITEM_CODE='" + model.CN_S_ITEM_CODE + @"' and CN_S_STOCK_AREA='" + model.CN_S_STOCK_AREA + @"'
|
and CN_S_ITEM_STATE='" + model.CN_S_ITEM_STATE + @"'
|
and CN_S_LOT_NO='" + model.CN_S_LOT_NO + "' ";
|
|
strSql.Append(" if exists(select * from TN_WM_B_AREA_QTY " + sqlWhere + " )");
|
// strSql.Append("Update TN_WM_B_AREA_QTY SET CN_F_QUANTITY=CN_F_QUANTITY+" + model.CN_F_QUANTITY + ",CN_F_ALLOC_QTY=CN_F_ALLOC_QTY+" + model.CN_F_ALLOC_QTY + " " + sqlWhere + " ");
|
strSql.Append("Update TN_WM_B_AREA_QTY SET CN_F_QUANTITY=CN_F_QUANTITY+" + model.CN_F_QUANTITY + " " + sqlWhere + " ");
|
strSql.Append(" else ");
|
strSql.Append("insert into TN_WM_B_AREA_QTY(");
|
strSql.Append("CN_GUID,CN_S_OWNER,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,CN_S_LOT_NO,CN_S_STOCK_CODE,CN_F_QUANTITY,CN_F_ALLOC_QTY,CN_F_PLANNED_QTY,CN_S_STOCK_AREA,CN_S_NOTE)");
|
strSql.Append(" values (");
|
strSql.Append(":CN_GUID,:CN_S_OWNER,:CN_S_ITEM_CODE,:CN_S_ITEM_NAME,:CN_S_ITEM_STATE,:CN_S_LOT_NO,:CN_S_STOCK_CODE,:CN_F_QUANTITY,:CN_F_ALLOC_QTY,:CN_F_PLANNED_QTY,:CN_S_STOCK_AREA,:CN_S_NOTE)");
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
DataAccess.AddInParameter(cmd, "CN_GUID", ComDbType.STRING, model.CN_GUID);
|
DataAccess.AddInParameter(cmd, "CN_S_OWNER", ComDbType.STRING, model.CN_S_OWNER);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_CODE", ComDbType.STRING, model.CN_S_ITEM_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_NAME", ComDbType.STRING, model.CN_S_ITEM_NAME);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_STATE", ComDbType.STRING, model.CN_S_ITEM_STATE);
|
DataAccess.AddInParameter(cmd, "CN_S_LOT_NO", ComDbType.STRING, model.CN_S_LOT_NO);
|
DataAccess.AddInParameter(cmd, "CN_S_STOCK_CODE", ComDbType.STRING, model.CN_S_STOCK_CODE);
|
DataAccess.AddInParameter(cmd, "CN_F_QUANTITY", ComDbType.DECIMAL, model.CN_F_QUANTITY);
|
DataAccess.AddInParameter(cmd, "CN_F_ALLOC_QTY", ComDbType.DECIMAL, model.CN_F_ALLOC_QTY);
|
DataAccess.AddInParameter(cmd, "CN_F_PLANNED_QTY", ComDbType.DECIMAL, model.CN_F_PLANNED_QTY);
|
DataAccess.AddInParameter(cmd, "CN_S_STOCK_AREA", ComDbType.STRING, model.CN_S_STOCK_AREA);
|
DataAccess.AddInParameter(cmd, "CN_S_NOTE", ComDbType.STRING, model.CN_S_NOTE);
|
//Log.Info("AddAreaQty", "sql语句：" + strSql.ToString());
|
Log.Info(LogType.CheckTempService, "AddAreaQty！sql语句：" + strSql.ToString());
|
result = ExecuteCommand(cmd, trans);
|
if (!result.Success)
|
{
|
break;
|
}
|
}
|
return result;
|
}
|
|
//降暂存区库区量表
|
public SqlExecuteResult reduceAreaQty(List<HH.WMS.Entitys.Basic.TN_WM_B_AREA_QTYEntity> AreaQtyList, DbTransaction trans)
|
{
|
SqlExecuteResult result = new SqlExecuteResult();
|
|
|
foreach (var model in AreaQtyList)
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
string sqlWhere = " Where CN_S_STOCK_CODE='" + model.CN_S_STOCK_CODE + @"'
|
and CN_S_ITEM_CODE='" + model.CN_S_ITEM_CODE + @"'
|
and CN_S_ITEM_STATE='" + model.CN_S_ITEM_STATE + @"'
|
and CN_S_LOT_NO='" + model.CN_S_LOT_NO + "' and CN_S_STOCK_AREA = '" + model.CN_S_STOCK_AREA + "' ";
|
|
strSql.Append(" if exists(select * from TN_WM_B_AREA_QTY " + sqlWhere + " and CN_F_QUANTITY-" + model.CN_F_QUANTITY + ">0)");
|
strSql.Append("Update TN_WM_B_AREA_QTY SET CN_F_QUANTITY=CN_F_QUANTITY-" + model.CN_F_QUANTITY + " " + sqlWhere + " ");
|
strSql.Append(" else ");
|
strSql.Append("DELETE FROM TN_WM_B_AREA_QTY " + sqlWhere + " ");
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
result = ExecuteCommand(cmd, trans);
|
if (!result.Success)
|
{
|
break;
|
}
|
}
|
return result;
|
}
|
|
public SqlExecuteResult addAreaAllocQty(List<string> trayCodes, string stockCode, string areaCode, DbTransaction trans)
|
{
|
var trayItemRelList = DALCreator.Create<TN_WM_B_TRAY_ITEM_RELDAL>().GetListByTrays(trayCodes);
|
|
//增加库存分配数量
|
List<HH.WMS.Entitys.Basic.TN_WM_B_AREA_QTYEntity> stockAreaQtyList = new List<HH.WMS.Entitys.Basic.TN_WM_B_AREA_QTYEntity>();
|
foreach (var itemRel in trayItemRelList)
|
{
|
HH.WMS.Entitys.Basic.TN_WM_B_AREA_QTYEntity entityQty = new HH.WMS.Entitys.Basic.TN_WM_B_AREA_QTYEntity();
|
entityQty.CN_S_ITEM_STATE = itemRel.CN_S_ITEM_STATE;
|
entityQty.CN_F_QUANTITY = itemRel.CN_F_QUANTITY;
|
entityQty.CN_GUID = Guid.NewGuid().ToString();
|
entityQty.CN_S_ITEM_CODE = itemRel.CN_S_ITEM_CODE;
|
entityQty.CN_S_ITEM_NAME = itemRel.CN_S_ITEM_NAME;
|
entityQty.CN_S_LOT_NO = itemRel.CN_S_PRODUCTION_BATCH;
|
entityQty.CN_S_OWNER = itemRel.CN_S_OWNER;
|
entityQty.CN_S_STOCK_AREA = areaCode;
|
entityQty.CN_S_STOCK_CODE = stockCode;
|
stockAreaQtyList.Add(entityQty);
|
}
|
SqlExecuteResult result = new SqlExecuteResult();
|
foreach (HH.WMS.Entitys.Basic.TN_WM_B_AREA_QTYEntity model in stockAreaQtyList)
|
{
|
StringBuilder strSql = new StringBuilder();
|
string sqlWhere = " Where CN_S_STOCK_CODE='" + model.CN_S_STOCK_CODE + @"'
|
and CN_S_ITEM_CODE='" + model.CN_S_ITEM_CODE + @"'
|
and CN_S_ITEM_STATE='" + model.CN_S_ITEM_STATE + @"'
|
and CN_S_LOT_NO='" + model.CN_S_LOT_NO + "' and CN_S_STOCK_AREA = '" + model.CN_S_STOCK_AREA + "' ";
|
|
strSql.Append(" if exists(select * from TN_WM_B_AREA_QTY " + sqlWhere + " and CN_F_ALLOC_QTY-" + model.CN_F_ALLOC_QTY + ">0)");
|
strSql.Append("Update TN_WM_B_AREA_QTY SET CN_F_ALLOC_QTY=CN_F_ALLOC_QTY +" + model.CN_F_ALLOC_QTY + " " + sqlWhere + " ");
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
result = ExecuteCommand(cmd, trans);
|
if (!result.Success)
|
{
|
break;
|
}
|
}
|
return result;
|
|
}
|
public SqlExecuteResult reduceAreaAllocQty(List<string> trayCodes, string stockCode, string areaCode, DbTransaction trans)
|
{
|
var trayItemRelList = DALCreator.Create<TN_WM_B_TRAY_ITEM_RELDAL>().GetListByTrays(trayCodes);
|
|
//增加库存分配数量
|
var stockAreaQtyList = new List<HH.WMS.Entitys.Basic.TN_WM_B_AREA_QTYEntity>();
|
foreach (var itemRel in trayItemRelList)
|
{
|
var entityQty = new HH.WMS.Entitys.Basic.TN_WM_B_AREA_QTYEntity();
|
entityQty.CN_S_ITEM_STATE = itemRel.CN_S_ITEM_STATE;
|
entityQty.CN_F_QUANTITY = itemRel.CN_F_QUANTITY;
|
entityQty.CN_GUID = Guid.NewGuid().ToString();
|
entityQty.CN_S_ITEM_CODE = itemRel.CN_S_ITEM_CODE;
|
entityQty.CN_S_ITEM_NAME = itemRel.CN_S_ITEM_NAME;
|
entityQty.CN_S_LOT_NO = itemRel.CN_S_PRODUCTION_BATCH;
|
entityQty.CN_S_OWNER = itemRel.CN_S_OWNER;
|
entityQty.CN_S_STOCK_AREA = areaCode;
|
entityQty.CN_S_STOCK_CODE = stockCode;
|
stockAreaQtyList.Add(entityQty);
|
|
}
|
SqlExecuteResult result = new SqlExecuteResult();
|
foreach (var model in stockAreaQtyList)
|
{
|
|
|
StringBuilder strSql = new StringBuilder();
|
string sqlWhere = " Where CN_S_STOCK_CODE='" + model.CN_S_STOCK_CODE + @"'
|
and CN_S_ITEM_CODE='" + model.CN_S_ITEM_CODE + @"'
|
and CN_S_ITEM_STATE='" + model.CN_S_ITEM_STATE + @"'
|
and CN_S_LOT_NO='" + model.CN_S_LOT_NO + "' and CN_S_STOCK_AREA = '" + model.CN_S_STOCK_AREA + "' ";
|
|
strSql.Append(" if exists(select * from TN_WM_B_AREA_QTY " + sqlWhere + " and CN_F_ALLOC_QTY-" + model.CN_F_ALLOC_QTY + ">0)");
|
strSql.Append("Update TN_WM_B_AREA_QTY SET CN_F_ALLOC_QTY=CN_F_ALLOC_QTY-" + model.CN_F_ALLOC_QTY + " " + sqlWhere + " ");
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
result = ExecuteCommand(cmd, trans);
|
if (!result.Success)
|
{
|
break;
|
}
|
}
|
return result;
|
|
}
|
|
#endregion
|
}
|
}
|
