using HH.WMS.Common;
|
using HH.WMS.Common.External;
|
using HH.WMS.Entitys;
|
using HH.WMS.Entitys.Basic;
|
using HH.WMS.Entitys.Common;
|
using HH.WMS.Entitys.Dto;
|
using HH.WMS.Entitys.Entitys;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.Common;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
|
namespace HH.WMS.DAL.Basic
|
{
|
/// <summary>
|
/// 仓库量表
|
/// </summary>
|
public class TN_WM_B_STOCK_QTYDAL : DapperBaseDAL
|
{
|
/// <summary>
|
/// 获取仓库量表
|
/// </summary>
|
/// <param name="pageIndex"></param>
|
/// <param name="pageSize"></param>
|
/// <param name="stockCode"></param>
|
/// <param name="stockArea"></param>
|
/// <param name="itemCode"></param>
|
/// <param name="owner"></param>
|
/// <returns></returns>
|
public OperateResult GetEnableCheckQty(int pageIndex, int pageSize, string stockCode, string stockArea, string itemCode, string owner)
|
{
|
string where = "";
|
if (!string.IsNullOrEmpty(itemCode))
|
where += " and CN_S_ITEM_CODE like '%" + itemCode + "%' ";
|
if (!string.IsNullOrEmpty(owner))
|
where += " and CN_S_OWNER ='" + owner + "'";
|
|
string sql = @"(SELECT
|
CN_S_STOCK_CODE,CN_S_OWNER,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,
|
CN_S_FIGURE_NO,CN_S_MODEL,CN_S_MEASURE_UNIT,sum(CN_F_QUANTITY) CN_F_QUANTITY
|
FROM tn_wm_b_stock_qty
|
where CN_S_STOCK_CODE=@CN_S_STOCK_CODE " + where + @"
|
group by CN_S_OWNER,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,
|
CN_S_STOCK_CODE,CN_S_FIGURE_NO,CN_S_MODEL,CN_S_MEASURE_UNIT ) t";
|
|
return ExecutePagingResult(sql, pageIndex, pageSize, " order by CN_S_ITEM_CODE", new { CN_S_STOCK_CODE = stockCode });
|
}
|
|
#region 升/降仓库库存
|
|
/// <summary>
|
/// 升仓库库存
|
/// </summary>
|
/// <param name="stockQtyList"></param>
|
/// <param name="trans"></param>
|
/// <param name="isDefaultOwner">是否默认货主</param>
|
/// <returns></returns>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-15</History>
|
public OperateResult AddStockQty(List<TN_WM_B_STOCK_QTYEntity> stockQtyList, IDbTransaction trans, bool isDefaultOwner = false)
|
{
|
OperateResult result = new OperateResult();
|
foreach (var model in stockQtyList)
|
{
|
try
|
{
|
TN_WM_B_STOCK_QTYEntity.CheckNullValue(model);
|
|
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_PRODUCTION_BATCH='" + model.CN_S_PRODUCTION_BATCH
|
+ "' and CN_S_OWNER='" + (isDefaultOwner == true ? Constants.DefaultOwner : model.CN_S_OWNER.Trim()) + "'";
|
|
StringBuilder strSql = new StringBuilder();
|
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 + ",CN_F_ALLOC_QTY=CN_F_ALLOC_QTY+" + model.CN_F_ALLOC_QTY + " " + 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_F_PRELOCK_QTY,CN_S_TIMESTAMP,CN_S_MODEL,CN_S_FIGURE_NO,CN_S_MEASURE_UNIT,CN_S_PRODUCTION_BATCH)");
|
|
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_F_PRELOCK_QTY,@CN_S_TIMESTAMP,@CN_S_MODEL,@CN_S_FIGURE_NO,@CN_S_MEASURE_UNIT,@CN_S_PRODUCTION_BATCH)");
|
|
result = ExecuteTranSql(strSql.ToString(), new
|
{
|
CN_GUID = System.Guid.NewGuid().ToString().ToUpper(),
|
CN_S_OWNER = (isDefaultOwner == true ? Constants.DefaultOwner : model.CN_S_OWNER.Trim()),
|
CN_S_ITEM_CODE = model.CN_S_ITEM_CODE,
|
CN_S_ITEM_NAME = model.CN_S_ITEM_NAME,
|
CN_S_ITEM_STATE = model.CN_S_ITEM_STATE,
|
CN_S_LOT_NO = model.CN_S_LOT_NO == null ? "" : model.CN_S_LOT_NO,
|
CN_S_STOCK_CODE = model.CN_S_STOCK_CODE,
|
CN_F_QUANTITY = model.CN_F_QUANTITY,
|
CN_F_ALLOC_QTY = 0,
|
CN_F_PLANNED_QTY = model.CN_F_PLANNED_QTY,
|
CN_F_PRELOCK_QTY = 0,
|
CN_S_TIMESTAMP = "",
|
CN_S_MODEL = model.CN_S_MODEL,
|
CN_S_FIGURE_NO = model.CN_S_FIGURE_NO,
|
CN_S_MEASURE_UNIT = model.CN_S_MEASURE_UNIT,
|
CN_S_PRODUCTION_BATCH = model.CN_S_PRODUCTION_BATCH == null ? "" : model.CN_S_PRODUCTION_BATCH
|
}, trans);
|
if (result.Status == ResultStatus.Error)
|
return result;
|
}
|
catch (Exception)
|
{
|
|
}
|
}
|
return result;
|
}
|
|
/// <summary>
|
/// 降仓库库存
|
/// </summary>
|
/// <param name="reduceStockQty"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public OperateResult ReduceStockQty(List<TN_WM_B_STOCK_QTYEntity> reduceStockQty, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
foreach (TN_WM_B_STOCK_QTYEntity model in reduceStockQty)
|
{
|
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_PRODUCTION_BATCH='" + model.CN_S_PRODUCTION_BATCH
|
+ "' and CN_S_OWNER='" + model.CN_S_OWNER + "' ";
|
|
strSql.Append(" if exists(select CN_GUID 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 + " ");
|
|
Log.Info("降到货区仓库量表strSql", strSql.ToString());
|
|
result = ExecuteTranSql(strSql.ToString(), null, trans);
|
|
Log.Info("降到货区仓库量表影响行数", result.AffectedRows.ToString());
|
if (result.Status == ResultStatus.Error)
|
return result;
|
}
|
return result;
|
}
|
|
/// <summary>
|
/// 降仓库库存和分配量
|
/// </summary>
|
/// <param name="reduceStockQty"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public OperateResult ReduceStockQtyAndAllocQty(List<TN_WM_B_STOCK_QTYEntity> reduceStockQty, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
foreach (var model in reduceStockQty)
|
{
|
TN_WM_B_STOCK_QTYEntity.CheckNullValue(model);
|
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_PRODUCTION_BATCH='" + model.CN_S_PRODUCTION_BATCH
|
+ "' and CN_S_OWNER='" + model.CN_S_OWNER.Trim() + "' ";
|
|
strSql.Append(" if exists(select CN_GUID 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 + ",CN_F_ALLOC_QTY=CN_F_ALLOC_QTY-" + model.CN_F_QUANTITY + " " + sqlWhere + " ");
|
strSql.Append(" else ");
|
strSql.Append("DELETE FROM TN_WM_B_STOCK_QTY " + sqlWhere + " ");
|
|
result = ExecuteTranSql(strSql.ToString(), null, trans);
|
if (result.Status == ResultStatus.Error)
|
return result;
|
}
|
return result;
|
}
|
/// <summary>
|
/// 降仓库库存和分配量
|
/// </summary>
|
/// <param name="reduceStockQty"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public OperateResult ReduceStockAllocQty(List<TN_WM_B_STOCK_QTYEntity> reduceStockQty, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
foreach (var model in reduceStockQty)
|
{
|
TN_WM_B_STOCK_QTYEntity.CheckNullValue(model);
|
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_PRODUCTION_BATCH='" + model.CN_S_PRODUCTION_BATCH
|
+ "' and CN_S_OWNER='" + model.CN_S_OWNER.Trim() + "' ";
|
|
strSql.Append(" if exists(select CN_GUID from TN_WM_B_STOCK_QTY " + sqlWhere + " and CN_F_ALLOC_QTY-" + model.CN_F_QUANTITY + ">=0)");
|
strSql.Append("Update TN_WM_B_STOCK_QTY SET CN_F_ALLOC_QTY=CN_F_ALLOC_QTY-" + model.CN_F_QUANTITY + " " + sqlWhere + " ");
|
|
strSql.Append(" else ");
|
strSql.Append("Update TN_WM_B_STOCK_QTY SET CN_F_ALLOC_QTY =0 " + sqlWhere + " ");
|
|
result = ExecuteTranSql(strSql.ToString(), null, trans);
|
if (result.Status == ResultStatus.Error)
|
return result;
|
}
|
return result;
|
}
|
|
/// <summary>
|
/// 降货主所在仓库量
|
/// </summary>
|
/// <param name="StockQtyList"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public OperateResult ReduceOwnerStockQty(List<TN_WM_B_STOCK_QTYEntity> StockQtyList, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
foreach (TN_WM_B_STOCK_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 + "' and CN_S_PRODUCTION_BATCH='" + model.CN_S_PRODUCTION_BATCH + "' and CN_S_OWNER='" + model.CN_S_OWNER.Trim() + "' ";
|
|
|
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 + " ");
|
|
Log.Info("降货主所在仓库量sqlWhere", sqlWhere);
|
Log.Info("降货主所在仓库量strSql", strSql.ToString());
|
|
result = ExecuteTranSql(strSql.ToString(), null, trans);
|
|
Log.Info("降货主所在仓库量表影响行数", result.AffectedRows.ToString());
|
if (result.Status == ResultStatus.Error)
|
return result;
|
}
|
return result;
|
}
|
|
#endregion
|
|
#region 升/降仓库分配量
|
|
/// <summary>
|
/// 升仓库分配量
|
/// </summary>
|
/// <param name="stockQty"></param>
|
/// <param name="trans"></param>
|
/// <param name="logPara"></param>
|
/// <returns></returns>
|
public OperateResult AddAllocQty(TN_WM_B_STOCK_QTYEntity stockQty , IDbTransaction trans, LogPara logPara)
|
{
|
string sql = " Update TN_WM_B_STOCK_QTY SET CN_F_ALLOC_QTY=CN_F_ALLOC_QTY+" + stockQty.CN_F_QUANTITY
|
+ " Where CN_S_ITEM_CODE='" + stockQty.CN_S_ITEM_CODE
|
+ "' and CN_S_ITEM_STATE='" + stockQty.CN_S_ITEM_STATE
|
+ "' and CN_S_STOCK_CODE='" + stockQty.CN_S_STOCK_CODE
|
+ "' and CN_S_LOT_NO='" + stockQty.CN_S_LOT_NO
|
+ "' and CN_S_PRODUCTION_BATCH='" + stockQty.CN_S_PRODUCTION_BATCH
|
+ "' ";
|
Log.Detail(logPara, "升仓库分配量执行SQL语句：" + sql);
|
var result = ExecuteTranSql(sql, null, trans);
|
return result;
|
}
|
|
/// <summary>
|
/// 降仓库分配量
|
/// </summary>
|
/// <param name="stockQty"></param>
|
/// <param name="trans"></param>
|
/// <param name="logPara"></param>
|
/// <returns></returns>
|
public OperateResult ReduceAllocQty(TN_WM_B_STOCK_QTYEntity stockQty, IDbTransaction trans, LogPara logPara)
|
{
|
string sql = " Update TN_WM_B_STOCK_QTY SET CN_F_ALLOC_QTY=CN_F_ALLOC_QTY-" + stockQty.CN_F_QUANTITY
|
+ " Where CN_S_ITEM_CODE='" + stockQty.CN_S_ITEM_CODE
|
+ "' and CN_S_ITEM_STATE='" + stockQty.CN_S_ITEM_STATE
|
+ "' and CN_S_STOCK_CODE='" + stockQty.CN_S_STOCK_CODE
|
+ "' and CN_S_LOT_NO='" + stockQty.CN_S_LOT_NO
|
+ "' and CN_S_PRODUCTION_BATCH='" + stockQty.CN_S_PRODUCTION_BATCH
|
+ "' ";
|
Log.Detail(logPara, "降仓库分配量执行SQL语句：" + sql);
|
var result = ExecuteTranSql(sql, null, trans);
|
return result;
|
}
|
|
#endregion
|
|
/// <summary>
|
/// 修改库存编辑量
|
/// </summary>
|
/// <param name="stockList"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public OperateResult UpdateStockEditingQty(List<TN_WM_B_STOCK_QTYEntity> stockList, IDbTransaction trans, string timeStamp = "")
|
{
|
if (!stockList.Any()) return OperateResult.Succeed();
|
string sql = "UPDATE TN_WM_B_STOCK_QTY SET CN_F_EDITING_QTY=@CN_F_EDITING_QTY,CN_S_TIMESTAMP='{0}' WHERE CN_GUID=@CN_GUID AND (CN_S_TIMESTAMP = @CN_S_TIMESTAMP OR CN_S_TIMESTAMP IS NULL)";
|
sql = string.Format(sql, string.IsNullOrEmpty(timeStamp) ? DateTime.Now.ToString("yyyyMMddHHmmssffff") : timeStamp);
|
return ExecuteTranSql(sql, stockList, trans);
|
}
|
|
/// <summary>
|
/// 修改库存分配量
|
/// </summary>
|
/// <param name="stockList"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public OperateResult UpdateStockAllocQty(List<TN_WM_B_STOCK_QTYEntity> stockList, IDbTransaction trans, string timeStamp = "")
|
{
|
if (!stockList.Any()) return OperateResult.Succeed();
|
string sql = "UPDATE TN_WM_B_STOCK_QTY SET CN_F_ALLOC_QTY=@CN_F_ALLOC_QTY,CN_S_TIMESTAMP='{0}' WHERE CN_GUID=@CN_GUID AND (CN_S_TIMESTAMP = @CN_S_TIMESTAMP OR CN_S_TIMESTAMP IS NULL)";
|
sql = string.Format(sql, string.IsNullOrEmpty(timeStamp) ? DateTime.Now.ToString("yyyyMMddHHmmssffff") : timeStamp);
|
return ExecuteTranSql(sql, stockList, trans);
|
}
|
|
/// <summary>
|
/// 修改库存数量
|
/// </summary>
|
/// <param name="stockList"></param>
|
/// <param name="trans"></param>
|
/// <param name="timeStamp"></param>
|
/// <returns></returns>
|
public OperateResult UpdateStockQty(List<TN_WM_B_STOCK_QTYEntity> stockList, IDbTransaction trans, string timeStamp = "")
|
{
|
string sql = "UPDATE TN_WM_B_STOCK_QTY SET CN_F_QUANTITY=@CN_F_QUANTITY,CN_S_TIMESTAMP='{0}' WHERE CN_GUID=@CN_GUID AND (CN_S_TIMESTAMP = @CN_S_TIMESTAMP OR CN_S_TIMESTAMP IS NULL)";
|
sql = string.Format(sql, string.IsNullOrEmpty(timeStamp) ? DateTime.Now.ToString("yyyyMMddHHmmssffff") : timeStamp);
|
return ExecuteTranSql(sql, stockList, trans);
|
}
|
|
/// <summary>
|
/// 降库存
|
/// </summary>
|
/// <param name="lockType"></param>
|
/// <param name="outLockList"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public OperateResult UpdateAllocQtyByLock(string lockType, List<TN_WM_B_OUTLOCKING_DTLEntity> outLockList, IDbTransaction trans)
|
{
|
string sql = string.Empty;
|
if (lockType.Equals(Constants.Rule_OutOrder)) //根据出库单降库存
|
{
|
sql = "UPDATE TN_WM_B_STOCK_QTY SET CN_F_ALLOC_QTY = CN_F_ALLOC_QTY-@CN_F_QUANTITY,CN_F_QUANTITY=CN_F_QUANTITY-@CN_F_QUANTITY WHERE CN_GUID =@CN_S_TABLE_ID";
|
}
|
else if (lockType.Equals(Constants.Rule_SortingNo)) //根据分拣单降库存
|
{
|
sql = "UPDATE TN_WM_B_AREA_QTY SET CN_F_ALLOC_QTY = CN_F_ALLOC_QTY-@CN_F_QUANTITY,CN_F_QUANTITY=CN_F_QUANTITY-@CN_F_QUANTITY WHERE CN_GUID =@CN_S_TABLE_ID";
|
}
|
if (!string.IsNullOrEmpty(sql))
|
return ExecuteTranSql(sql, outLockList, trans);
|
return OperateResult.Succeed();
|
}
|
|
/// <summary>
|
/// 修改多条数据库存量
|
/// </summary>
|
/// <param name="lstModel"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public SqlExecuteResult UpdateListBysqlwhere(List<TN_WM_B_STOCK_QTYEntity> list, DbTransaction trans)
|
{
|
SqlExecuteResult result = new SqlExecuteResult();
|
List<DbCommand> cmdlist = new List<DbCommand>();
|
foreach (TN_WM_B_STOCK_QTYEntity entity in list)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append(" if exists(select * from TN_WM_B_STOCK_QTY " + entity.SqlWhere + " )");
|
if (entity.CN_F_QUANTITY > entity.CN_F_ALLOC_QTY)
|
{
|
strSql.Append("UPDATE tn_wm_b_stock_qty SET CN_F_QUANTITY=CN_F_QUANTITY+:CN_F_QUANTITY WHERE CN_S_OWNER=:CN_S_OWNER AND CN_S_ITEM_CODE=:CN_S_ITEM_CODE AND CN_S_ITEM_STATE=:CN_S_ITEM_STATE AND CN_S_STOCK_CODE=:CN_S_STOCK_CODE AND CN_S_LOT_NO=:CN_S_LOT_NO AND CN_S_PRODUCTION_BATCH=:CN_S_PRODUCTION_BATCH");
|
entity.CN_F_QUANTITY = entity.CN_F_QUANTITY - entity.CN_F_ALLOC_QTY;
|
}
|
else
|
{
|
strSql.Append("UPDATE tn_wm_b_stock_qty SET CN_F_QUANTITY=CN_F_QUANTITY-:CN_F_QUANTITY WHERE CN_S_OWNER=:CN_S_OWNER AND CN_S_ITEM_CODE=:CN_S_ITEM_CODE AND CN_S_ITEM_STATE=:CN_S_ITEM_STATE AND CN_S_STOCK_CODE=:CN_S_STOCK_CODE AND CN_S_LOT_NO=:CN_S_LOT_NO AND CN_S_PRODUCTION_BATCH=:CN_S_PRODUCTION_BATCH");
|
entity.CN_F_QUANTITY = entity.CN_F_ALLOC_QTY - entity.CN_F_QUANTITY;
|
}
|
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_F_PRELOCK_QTY,CN_S_PRODUCTION_BATCH,CN_S_MODEL,CN_S_FIGURE_NO,CN_S_MEASURE_UNIT)");
|
|
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_F_PRELOCK_QTY,:CN_S_PRODUCTION_BATCH,:CN_S_MODEL,:CN_S_FIGURE_NO,:CN_S_MEASURE_UNIT)");
|
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
DataAccess.AddInParameter(cmd, "CN_GUID", ComDbType.STRING, entity.CN_GUID);
|
DataAccess.AddInParameter(cmd, "CN_S_OWNER", ComDbType.STRING, entity.CN_S_OWNER);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_CODE", ComDbType.STRING, entity.CN_S_ITEM_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_NAME", ComDbType.STRING, entity.CN_S_ITEM_NAME);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_STATE", ComDbType.STRING, entity.CN_S_ITEM_STATE);
|
DataAccess.AddInParameter(cmd, "CN_S_LOT_NO", ComDbType.STRING, entity.CN_S_LOT_NO);
|
DataAccess.AddInParameter(cmd, "CN_S_STOCK_CODE", ComDbType.STRING, entity.CN_S_STOCK_CODE);
|
DataAccess.AddInParameter(cmd, "CN_F_QUANTITY", ComDbType.INT32, entity.CN_F_QUANTITY);
|
DataAccess.AddInParameter(cmd, "CN_F_ALLOC_QTY", ComDbType.DECIMAL, 0);
|
DataAccess.AddInParameter(cmd, "CN_F_PLANNED_QTY", ComDbType.DECIMAL, entity.CN_F_PLANNED_QTY);
|
DataAccess.AddInParameter(cmd, "CN_F_PRELOCK_QTY", ComDbType.DECIMAL, entity.CN_F_PRELOCK_QTY);
|
DataAccess.AddInParameter(cmd, "CN_S_PRODUCTION_BATCH", ComDbType.STRING, entity.CN_S_PRODUCTION_BATCH);
|
DataAccess.AddInParameter(cmd, "CN_S_MODEL", ComDbType.STRING, entity.CN_S_MODEL);
|
DataAccess.AddInParameter(cmd, "CN_S_FIGURE_NO", ComDbType.STRING, entity.CN_S_FIGURE_NO);
|
DataAccess.AddInParameter(cmd, "CN_S_MEASURE_UNIT", ComDbType.STRING, entity.CN_S_MEASURE_UNIT);
|
cmdlist.Add(cmd);
|
|
}
|
return ExecuteCommands(cmdlist, trans);
|
}
|
|
/// <summary>
|
/// 删除锁定明细
|
/// </summary>
|
/// <param name="outLockList"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public OperateResult DeleteOutLock(List<TN_WM_B_OUTLOCKING_DTLEntity> outLockList, IDbTransaction trans)
|
{
|
string sql = @"DELETE FROM TN_WM_B_OUTLOCKING_DTL WHERE CN_GUID=@CN_GUID";
|
return ExecuteTranSql(sql, outLockList, trans);
|
}
|
|
/// <summary>
|
/// 根据移库单 修改仓库数量 移出仓库减去数量 移入仓库加上数量
|
/// </summary>
|
/// <param name="lstModel"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
/// <history>[HANHE(LT)] CREATED BY 2018-12-20</history>
|
public SqlExecuteResult MoveInfoUpdateStockQty(List<TN_WM_B_STOCK_QTYEntity> lstModel, DbTransaction trans)
|
{
|
SqlExecuteResult result = new SqlExecuteResult();
|
List<DbCommand> cmdlist = new List<DbCommand>();
|
foreach (TN_WM_B_STOCK_QTYEntity entity in lstModel)
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
//移出库区数量
|
strSql.Append("UPDATE TN_WM_B_STOCK_QTY SET CN_F_QUANTITY=CN_F_QUANTITY-:CN_F_QUANTITY WHERE CN_S_STOCK_CODE='" + entity.CN_S_STOCK_CODE + "' AND " + entity.SqlWhere + "");
|
|
strSql.Append(" if exists(select * from TN_WM_B_STOCK_QTY WHERE " + entity.SqlWhere1 + " )");
|
strSql.Append("UPDATE TN_WM_B_STOCK_QTY SET CN_S_OWNER='" + entity.endBit + "',CN_F_QUANTITY=CN_F_QUANTITY+:CN_F_QUANTITY WHERE CN_S_STOCK_CODE='" + entity.CN_S_STOCK_CODE + "' AND " + entity.SqlWhere1 + "");
|
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_F_PRELOCK_QTY,CN_S_TIMESTAMP,CN_S_PRODUCTION_BATCH)");
|
|
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_F_PRELOCK_QTY,:CN_S_TIMESTAMP,:CN_S_PRODUCTION_BATCH)");
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
DataAccess.AddInParameter(cmd, "CN_GUID", ComDbType.STRING, entity.CN_GUID);
|
DataAccess.AddInParameter(cmd, "CN_S_OWNER", ComDbType.STRING, entity.CN_S_OWNER);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_CODE", ComDbType.STRING, entity.CN_S_ITEM_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_NAME", ComDbType.STRING, entity.CN_S_ITEM_NAME);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_STATE", ComDbType.STRING, entity.CN_S_ITEM_STATE);
|
DataAccess.AddInParameter(cmd, "CN_S_LOT_NO", ComDbType.STRING, entity.CN_S_LOT_NO);
|
DataAccess.AddInParameter(cmd, "CN_S_STOCK_CODE", ComDbType.STRING, entity.CN_S_STOCK_CODE);
|
DataAccess.AddInParameter(cmd, "CN_F_QUANTITY", ComDbType.DECIMAL, entity.CN_F_QUANTITY);
|
DataAccess.AddInParameter(cmd, "CN_F_ALLOC_QTY", ComDbType.DECIMAL, 0);
|
DataAccess.AddInParameter(cmd, "CN_F_PLANNED_QTY", ComDbType.DECIMAL, entity.CN_F_PLANNED_QTY);
|
DataAccess.AddInParameter(cmd, "CN_F_PRELOCK_QTY", ComDbType.DECIMAL, entity.CN_F_PRELOCK_QTY);
|
|
DataAccess.AddInParameter(cmd, "CN_S_TIMESTAMP", ComDbType.STRING, entity.CN_S_TIMESTAMP);
|
DataAccess.AddInParameter(cmd, "CN_S_PRODUCTION_BATCH", ComDbType.STRING, entity.CN_S_PRODUCTION_BATCH);
|
cmdlist.Add(cmd);
|
}
|
return ExecuteCommands(cmdlist, trans);
|
}
|
|
/// <summary>
|
/// 降仓库库存（入库单反审）
|
/// </summary>
|
/// <param name="opNoList"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
/// <History>[HANHE(lt)] CREATED BY 2019-1-21</History>
|
public OperateResult DeleteStockQtys(List<TN_WM_B_STOCK_QTYEntity> list, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
|
foreach (TN_WM_B_STOCK_QTYEntity entity in list)
|
{
|
string sql = @" UPDATE TN_WM_B_STOCK_QTY SET CN_F_QUANTITY =CN_F_QUANTITY-{1} {0}";
|
sql = string.Format(sql, entity.SqlWhere, entity.CN_F_QUANTITY);
|
result = ExecuteTranSql(sql.ToString(), null, trans);
|
if (result.Status == ResultStatus.Error)
|
return result;
|
}
|
return result;
|
}
|
|
/// <summary>
|
/// 删除为0的数据
|
/// </summary>
|
/// <returns></returns>
|
public OperateResult DeleteStockQty(IDbTransaction trans)
|
{
|
string sql = "DELETE dbo.tn_wm_b_stock_qty WHERE CN_F_QUANTITY<=0 ";
|
return ExecuteTranSql(sql, null, trans);
|
}
|
|
/// <summary>
|
/// 入库单反审时降仓库量表
|
/// </summary>
|
/// <param name="opNoList"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-15</History>
|
public OperateResult DeleteStockQty(string opNoList, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
string sql = @" UPDATE TN_WM_B_STOCK_QTY SET CN_F_QUANTITY =Q.CN_F_QUANTITY-IND.CN_F_QUANTITY FROM
|
TN_WM_B_STOCK_QTY Q,TN_WM_INCREASE_INVENTORY_DTL IND,TN_WM_INCREASE_INVENTORY_MST MST
|
WHERE Q.CN_S_ITEM_CODE=IND.CN_S_ITEM_CODE
|
AND Q.CN_S_ITEM_STATE=IND.CN_S_ITEM_STATE
|
AND Q.CN_S_LOT_NO=MST.CN_S_LOT_NO AND Q.CN_S_OWNER=MST.CN_S_OWNER AND IND.CN_S_OP_NO=MST.CN_S_OP_NO
|
AND IND.CN_S_OP_NO in (" + opNoList + ")";
|
result = ExecuteTranSql(sql.ToString(), null, trans);
|
if (result.Success)
|
{
|
string sqlstr = @" DELETE FROM TN_WM_B_STOCK_QTY where CN_F_QUANTITY<=0 ";
|
result = ExecuteTranSql(sqlstr.ToString(), null, trans);
|
}
|
return result;
|
}
|
|
/// <summary>
|
/// 到货单反审时降仓库量表
|
/// </summary>
|
/// <param name="opNoList"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
/// <History>[HANHE(XDL)] CREATED BY 2019-03-04</History>
|
public OperateResult DeleteStockQtyArr(string opNoList, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
string sql = @" UPDATE TN_WM_B_STOCK_QTY SET CN_F_QUANTITY =Q.CN_F_QUANTITY-IND.CN_F_QUANTITY FROM
|
TN_WM_B_STOCK_QTY Q,TN_WM_ARRIVAL_DTL IND,TN_WM_ARRIVAL_MST MST
|
WHERE Q.CN_S_ITEM_CODE=IND.CN_S_ITEM_CODE
|
AND Q.CN_S_ITEM_STATE=IND.CN_S_ITEM_STATE
|
AND Q.CN_S_LOT_NO=MST.CN_S_LOT_NO AND Q.CN_S_OWNER=MST.CN_S_OWNER AND IND.CN_S_OP_NO=MST.CN_S_OP_NO
|
AND IND.CN_S_OP_NO in (" + opNoList + ")";
|
result = ExecuteTranSql(sql.ToString(), null, trans);
|
if (result.Success)
|
{
|
string sqlstr = @" DELETE FROM TN_WM_B_STOCK_QTY where CN_F_QUANTITY<=0 ";
|
result = ExecuteTranSql(sqlstr.ToString(), null, trans);
|
}
|
return result;
|
}
|
}
|
}
|
