using HanHe.Utility.Data;
|
using HH.WMS.Entitys.Basic;
|
using HH.WMS.Entitys.Common;
|
using HH.WMS.Entitys.External;
|
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_STOCK_QTYDAL : BaseDAL
|
{
|
#region 根据Guid获取
|
/// <summary>
|
/// 根据Guid获取
|
/// </summary>
|
/// <param name="guidList"></param>
|
/// <returns></returns>
|
public List<TN_WM_B_STOCK_QTYEntity> GetStockByGuid(List<string> guidList)
|
{
|
string sql = @"SELECT * FROM TN_WM_B_STOCK_QTY WHERE CN_GUID IN ('" + string.Join("','", guidList) + "')";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
return DataAccessExtensive.ExecuteListEntity<TN_WM_B_STOCK_QTYEntity>(this.DataAccess, cmd,
|
(e, r) =>
|
{
|
SetEntityUti(e, "CN_GUID", "CN_GUID", r); //GUID
|
SetEntityUti(e, "CN_F_QUANTITY", "CN_F_QUANTITY", r); //库存数量
|
SetEntityUti(e, "CN_F_PLANNED_QTY", "CN_F_PLANNED_QTY", r); //在单量
|
SetEntityUti(e, "CN_F_ALLOC_QTY", "CN_F_ALLOC_QTY", r); //分配量
|
});
|
}
|
#endregion
|
|
#region 影响仓库量表
|
public SqlExecuteResult AddStockQty(List<TN_WM_B_AREA_QTYEntity> stockQtyList, DbTransaction trans)
|
{
|
SqlExecuteResult result = new SqlExecuteResult();
|
foreach (TN_WM_B_AREA_QTYEntity model in stockQtyList)
|
{
|
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 + "' ";
|
|
strSql.Append(" if exists(select * from TN_WM_B_STOCK_QTY " + sqlWhere + " )");
|
strSql.Append("Update TN_WM_B_STOCK_QTY SET CN_F_QUANTITY=CN_F_QUANTITY+" + model.CN_F_QUANTITY + " " + sqlWhere + " ");
|
strSql.Append(" else ");
|
strSql.Append("insert into TN_WM_B_STOCK_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_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_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_NOTE", ComDbType.STRING, model.CN_S_NOTE);
|
result = ExecuteCommand(cmd, trans);
|
if (!result.Success)
|
{
|
break;
|
}
|
}
|
return result;
|
}
|
//降暂仓库量表
|
public SqlExecuteResult reduceStockQty(List<TN_WM_B_AREA_QTYEntity> AreaQtyList, DbTransaction trans)
|
{
|
SqlExecuteResult result = new SqlExecuteResult();
|
|
|
foreach (TN_WM_B_AREA_QTYEntity 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 + "'";
|
|
strSql.Append(" if exists(select * from TN_WM_B_STOCK_QTY " + sqlWhere + " and CN_F_QUANTITY-" + model.CN_F_QUANTITY + ">0)");
|
strSql.Append("Update TN_WM_B_STOCK_QTY SET CN_F_QUANTITY=CN_F_QUANTITY-" + model.CN_F_QUANTITY + " " + sqlWhere + " ");
|
strSql.Append(" else ");
|
strSql.Append("DELETE FROM TN_WM_B_STOCK_QTY " + sqlWhere + " ");
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
result = ExecuteCommand(cmd, trans);
|
if (!result.Success)
|
{
|
break;
|
}
|
}
|
return result;
|
}
|
public SqlExecuteResult AddStockAllocQty(List<string> trayCodes, string stockCode, string areaCode, DbTransaction trans)
|
{
|
var trayItemRelList = DALCreator.Create<TN_WM_B_TRAY_ITEM_RELDAL>().GetListByTrays(trayCodes);
|
|
//增加库存分配数量
|
List<TN_WM_B_AREA_QTYEntity> stockQtyList = new List<TN_WM_B_AREA_QTYEntity>();
|
foreach (TN_WM_B_TRAY_ITEM_RELEntity itemRel in trayItemRelList)
|
{
|
TN_WM_B_AREA_QTYEntity entityQty = new 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;
|
stockQtyList.Add(entityQty);
|
}
|
SqlExecuteResult result = new SqlExecuteResult();
|
foreach (TN_WM_B_AREA_QTYEntity model in stockQtyList)
|
{
|
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 + "' ";
|
|
strSql.Append(" if exists(select * from TN_WM_B_STOCK_QTY " + sqlWhere + " )");
|
strSql.Append("Update TN_WM_B_STOCK_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 reduceStockAllocQty(List<string> trayCodes, string stockCode, string areaCode, DbTransaction trans)
|
{
|
var trayItemRelList = DALCreator.Create<TN_WM_B_TRAY_ITEM_RELDAL>().GetListByTrays(trayCodes);
|
|
//增加库存分配数量
|
List<TN_WM_B_AREA_QTYEntity> stockQtyList = new List<TN_WM_B_AREA_QTYEntity>();
|
foreach (TN_WM_B_TRAY_ITEM_RELEntity itemRel in trayItemRelList)
|
{
|
TN_WM_B_AREA_QTYEntity entityQty = new 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;
|
stockQtyList.Add(entityQty);
|
|
}
|
SqlExecuteResult result = new SqlExecuteResult();
|
foreach (TN_WM_B_AREA_QTYEntity model in stockQtyList)
|
{
|
|
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 + "'";
|
|
strSql.Append(" if exists(select * from TN_WM_B_STOCK_QTY " + sqlWhere + " and CN_F_ALLOC_QTY-" + model.CN_F_ALLOC_QTY + ">0)");
|
strSql.Append("Update TN_WM_B_STOCK_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
|
|
}
|
}
|
