using HH.WMS.Entitys;
|
using HH.WMS.Entitys.Basic;
|
using HH.WMS.Entitys.Entitys;
|
using HH.WMS.Common.Algorithm;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
using HanHe.Utility.Data;
|
using System.Data.Common;
|
using HH.WMS.Entitys.Common;
|
using HH.WMS.Common;
|
using HH.WMS.Entitys.Dto;
|
using HH.WMS.Common.External;
|
using HH.WMS.Entitys.External;
|
using HH.WMS.DAL.InStock;
|
|
namespace HH.WMS.DAL.Basic
|
{
|
public class TN_WM_B_AREA_QTYDAL : DapperBaseDAL
|
{
|
/// <summary>
|
/// 降库区量表并降分配量
|
/// </summary>
|
/// <param name="AreaQtyList">库区量实体集合</param>
|
/// <param name="zanCunLocation">暂存区</param>
|
/// <param name="trans"></param>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-14</History>
|
public OperateResult ReduceAreaAllocQty(List<TN_WM_B_AREA_QTYEntity> AreaQtyList, string areaCode, LogPara logPara, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
foreach (var model in AreaQtyList)
|
{
|
TN_WM_B_AREA_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_STOCK_AREA='" + areaCode
|
+ "' 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_AREA_QTY " + sqlWhere + " and CN_F_ALLOC_QTY-" + model.CN_F_QUANTITY + ">=0)");
|
strSql.Append("Update TN_WM_B_AREA_QTY SET CN_F_ALLOC_QTY=CN_F_ALLOC_QTY-" + model.CN_F_QUANTITY + " " + sqlWhere + " ");
|
strSql.Append(" else ");
|
strSql.Append("Update TN_WM_B_AREA_QTY SET CN_F_ALLOC_QTY =0 " + sqlWhere + " ");
|
|
Log.Detail(logPara, "降到货区库区量表sqlWhere：" + sqlWhere);
|
Log.Detail(logPara, "降到货区库区量表strSql：" + strSql.ToString());
|
|
result = ExecuteTranSql(strSql.ToString(), null, trans);
|
|
Log.Detail(logPara, "降到货区库区量表影响行数: " + result.AffectedRows.ToString());
|
if (result.Status == ResultStatus.Error)
|
return result;
|
}
|
Log.Detail(logPara, "降低库区" + areaCode + "库存！结果：" + (result.Success ? "成功！" : ("失败！" + result.Msg)));
|
return result;
|
}
|
/// <summary>
|
/// 获得库区下每个物料的数量
|
/// </summary>
|
/// <param name="stockCode"></param>
|
/// <param name="itemCodes"></param>
|
/// <returns></returns>
|
public List<TN_WM_B_AREA_QTYEntity> GetAreaQty(string strWhere)
|
{
|
var strSql = string.Format(@" select CN_GUID,CN_S_OWNER,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,CN_S_PRODUCTION_BATCH,CN_S_LOT_NO,CN_S_STOCK_CODE,
|
CN_S_STOCK_AREA, CAST(CN_F_QUANTITY AS INT ) AS CN_F_QUANTITY,CAST(CN_F_QUANTITY AS INT) AS CN_F_ALLOC_QTY,CN_S_MODEL,
|
CN_S_FIGURE_NO,CN_S_MEASURE_UNIT
|
from TN_WM_B_AREA_QTY where {0}", strWhere).Replace("\r\n", "");
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql);
|
List<TN_WM_B_AREA_QTYEntity> list = DataAccessExtensive.ExecuteListEntity<TN_WM_B_AREA_QTYEntity>(this.DataAccess, cmd, SetEntity);
|
return list;
|
}
|
|
private void SetEntity(TN_WM_B_AREA_QTYEntity entity, IDataReader reader)
|
{
|
SetEntityUti(entity, "CN_GUID", "CN_GUID", reader);
|
SetEntityUti(entity, "CN_S_OWNER", "CN_S_OWNER", reader);
|
SetEntityUti(entity, "CN_S_ITEM_CODE", "CN_S_ITEM_CODE", reader);
|
SetEntityUti(entity, "CN_S_ITEM_NAME", "CN_S_ITEM_NAME", reader);
|
SetEntityUti(entity, "CN_S_ITEM_STATE", "CN_S_ITEM_STATE", reader);
|
SetEntityUti(entity, "CN_S_LOT_NO", "CN_S_LOT_NO", reader);
|
SetEntityUti(entity, "CN_S_STOCK_CODE", "CN_S_STOCK_CODE", reader);
|
SetEntityUti(entity, "CN_S_STOCK_AREA", "CN_S_STOCK_AREA", reader);
|
SetEntityUti(entity, "CN_F_QUANTITY", "CN_F_QUANTITY", reader);
|
SetEntityUti(entity, "CN_F_ALLOC_QTY", "CN_F_ALLOC_QTY", reader);
|
SetEntityUti(entity, "CN_S_MODEL", "CN_S_MODEL", reader);
|
SetEntityUti(entity, "CN_S_FIGURE_NO", "CN_S_FIGURE_NO", reader);
|
SetEntityUti(entity, "CN_S_MEASURE_UNIT", "CN_S_MEASURE_UNIT", reader);
|
SetEntityUti(entity, "CN_S_PRODUCTION_BATCH", "CN_S_PRODUCTION_BATCH", reader);
|
}
|
|
/// <summary>
|
/// 根据物料汇总当前库存
|
/// </summary>
|
/// <param name="itemCode">物料编码</param>
|
/// <param name="strWhere">条件</param>
|
/// <returns></returns>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-16</History>
|
public TN_WM_B_AREA_QTYEntity GetAreaSummaryQty(string itemCode, string strWhere)
|
{
|
TN_WM_B_AREA_QTYEntity areaQtyEntity = new TN_WM_B_AREA_QTYEntity();
|
|
StringBuilder sbStr = new StringBuilder();
|
|
sbStr.Append("SELECT CN_S_ITEM_CODE,SUM(CN_F_QUANTITY) CN_F_QUANTITY FROM TN_WM_B_AREA_QTY WHERE CN_S_ITEM_CODE='" + itemCode + "' ");
|
if (!string.IsNullOrEmpty(strWhere))
|
{
|
sbStr.Append(strWhere);
|
}
|
sbStr.Append("GROUP BY CN_S_ITEM_CODE");
|
|
return ExecuteSingleEntity<TN_WM_B_AREA_QTYEntity>(sbStr.ToString());
|
}
|
/// <summary>
|
/// 根据物料汇总当前库存
|
/// </summary>
|
/// <param name="itemCode">物料编码</param>
|
/// <param name="strWhere">条件</param>
|
/// <returns></returns>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-16</History>
|
public List<TN_WM_B_AREA_QTY_YSEntity> GetStockQtyInfo(string strWhere)
|
{
|
List<TN_WM_B_AREA_QTY_YSEntity> areaQtyEntity = new List<TN_WM_B_AREA_QTY_YSEntity>();
|
|
StringBuilder sbStr = new StringBuilder();
|
|
sbStr.Append("SELECT CN_S_ITEM_CODE as itemCode ,CN_S_ITEM_NAME as itemName,CN_S_STOCK_CODE as stockCode,CN_S_STOCK_AREA as areaCode,(CN_F_QUANTITY - CN_F_ALLOC_QTY) as availableWeight FROM TN_WM_B_AREA_QTY WHERE 1=1 ");
|
if (!string.IsNullOrEmpty(strWhere))
|
{
|
sbStr.Append(strWhere);
|
}
|
// sbStr.Append("GROUP BY CN_S_ITEM_CODE");
|
|
return ExecuteQuery<TN_WM_B_AREA_QTY_YSEntity>(sbStr.ToString());
|
}
|
|
public List<TN_WM_B_STOCK_DETAIL_YSEntity> GetStockQtyDetailList(string strWhere)
|
{
|
List<TN_WM_B_STOCK_DETAIL_YSEntity> lstStockDetail = new List<TN_WM_B_STOCK_DETAIL_YSEntity>();
|
|
StringBuilder sbStr = new StringBuilder();
|
|
sbStr.Append(" select c.CN_S_STOCK_CODE as stockCode,c.CN_S_STOCK_AREA as areaCode,a.CN_S_TRAY_CODE as trayCode,a.CN_S_ITEM_CODE as itemCode, ");
|
sbStr.Append(" a.CN_S_ITEM_NAME as itemName,a.CN_S_MODEL as itemModel,SUM(cast(b.CN_S_SERIAL_NO as decimal(10,4))) AS itemWeight from dbo.TN_WM_B_TRAY_ITEM_MST a ");
|
sbStr.Append(" inner join dbo.TN_WM_B_TRAY_ITEM_DTL b on a.CN_GUID = b.CN_PARENT_GUID ");
|
sbStr.Append(" inner join dbo.tn_wm_b_tray_location c on a.CN_S_TRAY_CODE = c.CN_S_TRAY_CODE ");
|
sbStr.Append(" group by c.CN_S_STOCK_CODE ,c.CN_S_STOCK_AREA ,a.CN_S_TRAY_CODE ,a.CN_S_ITEM_CODE ,a.CN_S_ITEM_NAME ,a.CN_S_MODEL ");
|
if (!string.IsNullOrEmpty(strWhere))
|
{
|
sbStr.Append(strWhere);
|
}
|
return ExecuteQuery<TN_WM_B_STOCK_DETAIL_YSEntity>(sbStr.ToString());
|
}
|
|
/// <summary>
|
/// 获得库区下每个物料的数量
|
/// </summary>
|
/// <param name="stockCode"></param>
|
/// <param name="itemCodes"></param>
|
/// <returns></returns>
|
public List<OutAreaItemQty> GetAreaQty(string strWhere, string stockCode)
|
{
|
string strSql = @"select
|
SUM(CN_F_QUANTITY) - SUM(CN_F_ALLOC_QTY) as Qty,CN_S_ITEM_CODE as itemCode,CN_S_STOCK_AREA as areaCode
|
from TN_WM_B_AREA_QTY
|
WHERE 1=1 " + strWhere + " AND CN_S_STOCK_CODE='" + stockCode + "' GROUP by CN_S_STOCK_AREA,CN_S_ITEM_CODE ";
|
List<OutAreaItemQty> list = ExecuteQuery<OutAreaItemQty>(strSql.ToString());
|
return list;
|
}
|
|
/// <summary>
|
/// 根据货主获取库存
|
/// </summary>
|
/// <param name="ownerCode"></param>
|
/// <returns></returns>
|
public List<TN_WM_B_AREA_QTYEntity> GetAreaQtyByOwner(string ownerCode)
|
{
|
string strSql = @"select * from (select distinct aq.CN_S_OWNER,aq.CN_S_ITEM_CODE,aq.CN_S_ITEM_NAME,aq.CN_S_ITEM_STATE,aq.CN_S_LOT_NO,aq.CN_S_STOCK_CODE,aq.CN_S_STOCK_AREA,
|
aq.CN_F_QUANTITY-(SELECT ISNULL(SUM(CN_F_QUANTITY),0) FROM TN_WM_ITEMBACK_DTL DTL LEFT JOIN TN_WM_ITEMBACK_MST MST
|
ON MST.CN_S_OP_NO=DTL.CN_S_OP_NO WHERE MST.CN_S_OWNER=aq.CN_S_OWNER
|
and DTL.CN_S_ITEM_CODE=aq.CN_S_ITEM_CODE and dtl.CN_S_LOT_NO=aq.CN_S_LOT_NO
|
and DTL.CN_S_PRODUCTION_BATCH=aq.CN_S_PRODUCTION_BATCH and CN_S_STATE='新建') CN_F_QUANTITY,aq.CN_F_PLANNED_QTY,aq.CN_F_ALLOC_QTY,aq.CN_S_NOTE,aq.CN_S_TIMESTAMP,aq.CN_S_PRODUCTION_BATCH
|
from dbo.tn_wm_b_area_qty aq where aq.CN_S_OWNER='" + ownerCode + "') A where A.CN_F_QUANTITY>0";
|
List<TN_WM_B_AREA_QTYEntity> list = ExecuteQuery<TN_WM_B_AREA_QTYEntity>(strSql.ToString());
|
return list;
|
}
|
|
/// <summary>
|
/// 入库单取消时，判断物料是否可取消
|
/// </summary>
|
/// <param name="opNoList"></param>
|
/// <returns></returns>
|
public List<TN_WM_B_AREA_QTYEntity> GetCancelData(string opNoList)
|
{
|
string strSql = @" select Q.* from TN_WM_B_AREA_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 ISNULL(Q.CN_S_PRODUCTION_BATCH,'')=ISNULL(IND.CN_S_PRODUCTION_BATCH,'') 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 + ")";
|
List<TN_WM_B_AREA_QTYEntity> list = ExecuteQuery<TN_WM_B_AREA_QTYEntity>(strSql.ToString());
|
return list;
|
}
|
|
/// <summary>
|
/// 升库区库存
|
/// </summary>
|
/// <param name="AreaQtyList"></param>
|
/// <param name="isDefaultOwner">是否默认货主</param>
|
/// <param name="trans"></param>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-14</History>
|
public OperateResult AddAreaQty(List<TN_WM_B_AREA_QTYEntity> AreaQtyList, IDbTransaction trans, bool isDefaultOwner = false)
|
{
|
OperateResult result = new OperateResult();
|
foreach (var model in AreaQtyList)
|
{
|
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
|
+ "' 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_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(" 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,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_S_STOCK_AREA,@CN_S_NOTE,@CN_S_TIMESTAMP,@CN_S_MODEL,@CN_S_FIGURE_NO,@CN_S_MEASURE_UNIT,@CN_S_PRODUCTION_BATCH)");
|
Log.Info("===>影响库区量详细", strSql.ToString());
|
result = ExecuteTranSql(strSql.ToString(), new
|
{
|
CN_GUID = model.CN_GUID,
|
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 = model.CN_F_ALLOC_QTY,
|
CN_F_PLANNED_QTY = model.CN_F_PLANNED_QTY,
|
CN_S_STOCK_AREA = model.CN_S_STOCK_AREA,
|
CN_S_NOTE = model.CN_S_NOTE,
|
CN_S_TIMESTAMP = model.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;
|
}
|
return result;
|
}
|
|
/// <summary>
|
/// 升库区库存
|
/// </summary>
|
/// <param name="AreaQtyList"></param>
|
/// <param name="areaCode"></param>
|
/// <param name="logPara"></param>
|
/// <param name="trans"></param>
|
/// <param name="isDefaultOwner"></param>
|
/// <returns></returns>
|
public OperateResult AddAreaQty(List<TN_WM_B_AREA_QTYEntity> AreaQtyList, string areaCode, LogPara logPara, IDbTransaction trans, bool isDefaultOwner = false)
|
{
|
if (logPara == null)
|
logPara = LogType.LogPara("升库区库存量表");
|
OperateResult result = new OperateResult();
|
foreach (var model in AreaQtyList)
|
{
|
TN_WM_B_AREA_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_STOCK_AREA='" + areaCode
|
+ "' 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_AREA_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,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_S_STOCK_AREA,@CN_S_NOTE,@CN_S_TIMESTAMP,@CN_S_MODEL,@CN_S_FIGURE_NO,@CN_S_MEASURE_UNIT,@CN_S_PRODUCTION_BATCH)");
|
Log.Info("===>影响库区量详细", strSql.ToString());
|
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_S_STOCK_AREA = areaCode,
|
CN_S_NOTE = "",
|
CN_S_TIMESTAMP = "",
|
CN_S_MODEL = model.CN_S_MODEL,
|
CN_S_FIGURE_NO = "",
|
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;
|
}
|
Log.Detail(logPara, "增加库区" + areaCode + "库存！结果：" + (result.Success ? "成功！" : ("失败！" + result.Msg)));
|
return result;
|
}
|
|
|
/// <summary>
|
/// 升库区库存
|
/// </summary>
|
/// <param name="AreaQtyList"></param>
|
/// <param name="isDefaultOwner">是否默认货主</param>
|
/// <param name="trans"></param>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-14</History>
|
public OperateResult AddAreaQtyAndAlloc(List<TN_WM_B_AREA_QTYEntity> AreaQtyList, IDbTransaction trans, bool isDefaultOwner = false)
|
{
|
OperateResult result = new OperateResult();
|
foreach (var model in AreaQtyList)
|
{
|
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
|
+ "' 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_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(" 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,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_S_STOCK_AREA,@CN_S_NOTE,@CN_S_TIMESTAMP,@CN_S_MODEL,@CN_S_FIGURE_NO,@CN_S_MEASURE_UNIT,@CN_S_PRODUCTION_BATCH)");
|
Log.Info("===>影响库区量详细", strSql.ToString());
|
result = ExecuteTranSql(strSql.ToString(), new
|
{
|
CN_GUID = model.CN_GUID,
|
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 = model.CN_F_ALLOC_QTY,
|
CN_F_PLANNED_QTY = model.CN_F_PLANNED_QTY,
|
CN_S_STOCK_AREA = model.CN_S_STOCK_AREA,
|
CN_S_NOTE = model.CN_S_NOTE,
|
CN_S_TIMESTAMP = model.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;
|
}
|
return result;
|
}
|
|
/// <summary>
|
/// 升库区库存
|
/// </summary>
|
/// <param name="AreaQtyList"></param>
|
/// <param name="areaCode"></param>
|
/// <param name="logPara"></param>
|
/// <param name="trans"></param>
|
/// <param name="isDefaultOwner"></param>
|
/// <returns></returns>
|
public OperateResult AddAreaQtyAndAlloc(List<TN_WM_B_AREA_QTYEntity> AreaQtyList, string areaCode, LogPara logPara, IDbTransaction trans, bool isDefaultOwner = false)
|
{
|
if (logPara == null)
|
logPara = LogType.LogPara("升库区库存量表");
|
OperateResult result = new OperateResult();
|
foreach (var model in AreaQtyList)
|
{
|
TN_WM_B_AREA_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_STOCK_AREA='" + areaCode
|
+ "' 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_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_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,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_S_STOCK_AREA,@CN_S_NOTE,@CN_S_TIMESTAMP,@CN_S_MODEL,@CN_S_FIGURE_NO,@CN_S_MEASURE_UNIT,@CN_S_PRODUCTION_BATCH)");
|
Log.Info("===>影响库区量详细", strSql.ToString());
|
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_S_STOCK_AREA = areaCode,
|
CN_S_NOTE = "",
|
CN_S_TIMESTAMP = "",
|
CN_S_MODEL = "",
|
CN_S_FIGURE_NO = "",
|
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;
|
}
|
Log.Detail(logPara, "增加库区" + areaCode + "库存！结果：" + (result.Success ? "成功！" : ("失败！" + result.Msg)));
|
return result;
|
}
|
|
|
/// <summary>
|
/// 升分配量
|
/// </summary>
|
/// <param name="areaQty"></param>
|
/// <param name="trans"></param>
|
/// <param name="logPara"></param>
|
/// <returns></returns>
|
public OperateResult AddAllocQty(TN_WM_B_AREA_QTYEntity areaQty, IDbTransaction trans, LogPara logPara)
|
{
|
TN_WM_B_AREA_QTYEntity.CheckNullValue(areaQty);
|
string sql = " Update TN_WM_B_AREA_QTY SET CN_F_ALLOC_QTY=CN_F_ALLOC_QTY+" + areaQty.CN_F_QUANTITY
|
+ " Where CN_S_ITEM_CODE='" + areaQty.CN_S_ITEM_CODE
|
+ "' and CN_S_ITEM_STATE='" + areaQty.CN_S_ITEM_STATE
|
+ "' and CN_S_STOCK_AREA='" + areaQty.CN_S_STOCK_AREA
|
+ "' and CN_S_LOT_NO='" + areaQty.CN_S_LOT_NO
|
+ "' and CN_S_PRODUCTION_BATCH='" + areaQty.CN_S_PRODUCTION_BATCH
|
+ "' ";
|
Log.Detail(logPara, "升库区分配量执行SQL语句：" + sql);
|
var result = ExecuteTranSql(sql, null, trans);
|
return result;
|
}
|
|
/// <summary>
|
/// 降分配量
|
/// </summary>
|
/// <param name="areaQty"></param>
|
/// <param name="trans"></param>
|
/// <param name="logPara"></param>
|
/// <returns></returns>
|
public OperateResult ReduceAllocQty(TN_WM_B_AREA_QTYEntity areaQty, IDbTransaction trans, LogPara logPara)
|
{
|
TN_WM_B_AREA_QTYEntity.CheckNullValue(areaQty);
|
string sql = " Update TN_WM_B_AREA_QTY SET CN_F_ALLOC_QTY=CN_F_ALLOC_QTY-" + areaQty.CN_F_QUANTITY
|
+ " Where CN_S_ITEM_CODE='" + areaQty.CN_S_ITEM_CODE
|
+ "' and CN_S_ITEM_STATE='" + areaQty.CN_S_ITEM_STATE
|
+ "' and CN_S_STOCK_AREA='" + areaQty.CN_S_STOCK_AREA
|
+ "' and CN_S_LOT_NO='" + areaQty.CN_S_LOT_NO
|
+ "' and CN_S_PRODUCTION_BATCH='" + areaQty.CN_S_PRODUCTION_BATCH
|
+ "' ";
|
Log.Detail(logPara, "降库区分配量执行SQL语句：" + sql);
|
var result = ExecuteTranSql(sql, null, trans);
|
return result;
|
}
|
|
/// <summary>
|
/// 降分配量
|
/// </summary>
|
/// <param name="areaQty"></param>
|
/// <param name="trans"></param>
|
/// <param name="logPara"></param>
|
/// <returns></returns>
|
public OperateResult ReduceAllocQty(string areaCode, string trayCode, IDbTransaction trans, LogPara logPara)
|
{
|
var result = new OperateResult();
|
var trayItemMsts = DALCreator.Create<DapperDAL<TN_WM_B_TRAY_ITEM_MSTEntity>>().GetList(new { CN_S_TRAY_CODE = trayCode });
|
foreach (var trayItemMst in trayItemMsts)
|
{
|
trayItemMst.CN_F_QUANTITY = 0;
|
trayItemMst.TrayItemDtlList = DALCreator.Create<DapperDAL<TN_WM_B_TRAY_ITEM_DTLEntity>>().GetList(new { CN_PARENT_GUID = trayItemMst.CN_GUID });
|
foreach (var trayItemDtl in trayItemMst.TrayItemDtlList)
|
{
|
trayItemMst.CN_F_QUANTITY += Convert.ToDecimal(trayItemDtl.CN_S_SERIAL_NO);
|
}
|
|
var areaQty = new TN_WM_B_AREA_QTYEntity
|
{
|
CN_F_QUANTITY = trayItemMst.CN_F_QUANTITY,
|
CN_S_ITEM_CODE = trayItemMst.CN_S_ITEM_CODE,
|
CN_S_ITEM_STATE = trayItemMst.CN_S_ITEM_STATE,
|
CN_S_STOCK_AREA = areaCode,
|
CN_S_LOT_NO = trayItemMst.CN_S_LOT_NO,
|
CN_S_PRODUCTION_BATCH = trayItemMst.CN_S_PRODUCTION_BATCH
|
};
|
TN_WM_B_AREA_QTYEntity.CheckNullValue(areaQty);
|
string sql = " Update TN_WM_B_AREA_QTY SET CN_F_ALLOC_QTY=CN_F_ALLOC_QTY-" + areaQty.CN_F_QUANTITY
|
+ " Where CN_S_ITEM_CODE='" + areaQty.CN_S_ITEM_CODE
|
+ "' and CN_S_ITEM_STATE='" + areaQty.CN_S_ITEM_STATE
|
+ "' and CN_S_STOCK_AREA='" + areaQty.CN_S_STOCK_AREA
|
+ "' and CN_S_LOT_NO='" + areaQty.CN_S_LOT_NO
|
+ "' and CN_S_PRODUCTION_BATCH='" + areaQty.CN_S_PRODUCTION_BATCH
|
+ "' ";
|
Log.Detail(logPara, "降库区分配量执行SQL语句：" + sql);
|
result = ExecuteTranSql(sql, null, trans);
|
|
if (!result.Success) return result;
|
}
|
return result;
|
}
|
|
public OperateResult ReduceAreaAndStockAllocQty(string areaCode, string trayCode, IDbTransaction trans, LogPara logPara)
|
{
|
var result = new OperateResult();
|
var trayItemMsts = DALCreator.Create<DapperDAL<TN_WM_B_TRAY_ITEM_MSTEntity>>().GetList(new { CN_S_TRAY_CODE = trayCode });
|
foreach (var trayItemMst in trayItemMsts)
|
{
|
trayItemMst.CN_F_QUANTITY = 0;
|
trayItemMst.TrayItemDtlList = DALCreator.Create<DapperDAL<TN_WM_B_TRAY_ITEM_DTLEntity>>().GetList(new { CN_PARENT_GUID = trayItemMst.CN_GUID });
|
foreach (var trayItemDtl in trayItemMst.TrayItemDtlList)
|
{
|
trayItemMst.CN_F_QUANTITY += Convert.ToDecimal(trayItemDtl.CN_S_SERIAL_NO);
|
}
|
|
var areaQty = new TN_WM_B_AREA_QTYEntity
|
{
|
CN_F_QUANTITY = trayItemMst.CN_F_QUANTITY,
|
CN_S_ITEM_CODE = trayItemMst.CN_S_ITEM_CODE,
|
CN_S_ITEM_STATE = trayItemMst.CN_S_ITEM_STATE,
|
CN_S_STOCK_AREA = areaCode,
|
CN_S_LOT_NO = trayItemMst.CN_S_LOT_NO,
|
CN_S_PRODUCTION_BATCH = trayItemMst.CN_S_PRODUCTION_BATCH
|
};
|
var stockQty = new TN_WM_B_STOCK_QTYEntity
|
{
|
CN_F_QUANTITY = trayItemMst.CN_F_QUANTITY,
|
CN_S_ITEM_CODE = trayItemMst.CN_S_ITEM_CODE,
|
CN_S_ITEM_STATE = trayItemMst.CN_S_ITEM_STATE,
|
CN_S_STOCK_AREA = areaCode,
|
CN_S_LOT_NO = trayItemMst.CN_S_LOT_NO,
|
CN_S_PRODUCTION_BATCH = trayItemMst.CN_S_PRODUCTION_BATCH
|
};
|
TN_WM_B_AREA_QTYEntity.CheckNullValue(areaQty);
|
TN_WM_B_STOCK_QTYEntity.CheckNullValue(stockQty);
|
string sql = " Update TN_WM_B_AREA_QTY SET CN_F_ALLOC_QTY=CN_F_ALLOC_QTY-" + areaQty.CN_F_QUANTITY
|
+ " Where CN_S_ITEM_CODE='" + areaQty.CN_S_ITEM_CODE
|
+ "' and CN_S_ITEM_STATE='" + areaQty.CN_S_ITEM_STATE
|
+ "' and CN_S_STOCK_AREA='" + areaQty.CN_S_STOCK_AREA
|
+ "' and CN_S_LOT_NO='" + areaQty.CN_S_LOT_NO
|
+ "' and CN_S_PRODUCTION_BATCH='" + areaQty.CN_S_PRODUCTION_BATCH
|
+ "' ";
|
Log.Detail(logPara, "降库区分配量执行SQL语句：" + sql);
|
result = ExecuteTranSql(sql, null, trans);
|
|
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_LOT_NO='" + stockQty.CN_S_LOT_NO
|
+ "' and CN_S_PRODUCTION_BATCH='" + stockQty.CN_S_PRODUCTION_BATCH
|
+ "' ";
|
Log.Detail(logPara, "降仓库分配量执行SQL语句：" + sql);
|
result = ExecuteTranSql(sql, null, trans);
|
|
if (!result.Success) return result;
|
}
|
return result;
|
}
|
|
/// <summary>
|
/// 降暂存区库区量表
|
/// </summary>
|
/// <param name="AreaQtyList">库区量实体集合</param>
|
/// <param name="zanCunLocation">暂存区</param>
|
/// <param name="trans"></param>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-14</History>
|
public OperateResult ReduceAreaQty(List<TN_WM_B_AREA_QTYEntity> AreaQtyList, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
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 + @"'
|
and CN_S_PRODUCTION_BATCH='" + model.CN_S_PRODUCTION_BATCH + @"'
|
and CN_S_OWNER='" + model.CN_S_OWNER + "' and CN_S_STOCK_AREA ='" + model.CN_S_STOCK_AREA.Trim() + "' ";
|
|
|
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 + " ");
|
|
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;
|
}
|
|
/// <summary>
|
/// 降暂存区库区量表
|
/// </summary>
|
/// <param name="AreaQtyList">库区量实体集合</param>
|
/// <param name="zanCunLocation">暂存区</param>
|
/// <param name="trans"></param>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-14</History>
|
public OperateResult ReduceAreaQty(List<TN_WM_B_AREA_QTYEntity> AreaQtyList, string areaCode, LogPara logPara, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
foreach (var model in AreaQtyList)
|
{
|
TN_WM_B_AREA_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_STOCK_AREA='" + areaCode
|
+ "' 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_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 + " ");
|
|
Log.Detail(logPara, "降到货区库区量表sqlWhere：" + sqlWhere);
|
Log.Detail(logPara, "降到货区库区量表strSql" + strSql.ToString());
|
|
result = ExecuteTranSql(strSql.ToString(), null, trans);
|
|
Log.Detail(logPara, "降到货区库区量表影响行数：" + result.AffectedRows.ToString());
|
if (result.Status == ResultStatus.Error)
|
return result;
|
}
|
Log.Detail(logPara, "降低库区" + areaCode + "库存！结果：" + (result.Success ? "成功！" : ("失败！" + result.Msg)));
|
return result;
|
}
|
|
/// <summary>
|
/// 降货主所在库区量
|
/// </summary>
|
/// <param name="AreaQtyList"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public OperateResult ReduceOwnerAreaQty(List<TN_WM_B_AREA_QTYEntity> AreaQtyList, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
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 + "' 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_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 + " ");
|
|
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;
|
}
|
|
/// <summary>
|
/// 降库区量表并降分配量
|
/// </summary>
|
/// <param name="AreaQtyList">库区量实体集合</param>
|
/// <param name="zanCunLocation">暂存区</param>
|
/// <param name="trans"></param>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-14</History>
|
public OperateResult ReduceAreaQtyAndAllocQty(List<TN_WM_B_AREA_QTYEntity> AreaQtyList, string areaCode, LogPara logPara, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
foreach (var model in AreaQtyList)
|
{
|
TN_WM_B_AREA_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_STOCK_AREA='" + areaCode
|
+ "' 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_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 + ",CN_F_ALLOC_QTY=CN_F_ALLOC_QTY-" + model.CN_F_QUANTITY + " " + sqlWhere + " ");
|
strSql.Append(" else ");
|
strSql.Append("DELETE FROM TN_WM_B_AREA_QTY " + sqlWhere + " ");
|
|
Log.Detail(logPara, "降到货区库区量表sqlWhere：" + sqlWhere);
|
Log.Detail(logPara, "降到货区库区量表strSql：" + strSql.ToString());
|
|
result = ExecuteTranSql(strSql.ToString(), null, trans);
|
|
Log.Detail(logPara, "降到货区库区量表影响行数: " + result.AffectedRows.ToString());
|
if (result.Status == ResultStatus.Error)
|
return result;
|
}
|
Log.Detail(logPara, "降低库区" + areaCode + "库存！结果：" + (result.Success ? "成功！" : ("失败！" + result.Msg)));
|
return result;
|
}
|
|
/// <summary>
|
/// 修改库区分配量
|
/// </summary>
|
/// <param name="areaQtyList"></param>
|
/// <returns></returns>
|
public OperateResult UpdateAreaAlloc(List<TN_WM_B_AREA_QTYEntity> areaQtyList, IDbTransaction trans)
|
{
|
string sql = @"UPDATE TN_WM_B_AREA_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, DateTime.Now.ToString("yyyyMMddHHmmssffff"));
|
return ExecuteTranSql(sql, areaQtyList, trans);
|
}
|
|
/// <summary>
|
/// 修改库区数量
|
/// </summary>
|
/// <param name="areaQtyList"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public OperateResult UpdateAreaQty(List<TN_WM_B_AREA_QTYEntity> areaQtyList, IDbTransaction trans)
|
{
|
string sql = @"UPDATE TN_WM_B_AREA_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, DateTime.Now.ToString("yyyyMMddHHmmssffff"));
|
return ExecuteTranSql(sql, areaQtyList, trans);
|
}
|
|
/// <summary>
|
/// 修改库存量和分配量
|
/// </summary>
|
/// <param name="areaQtyList"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public OperateResult UpdateAreaQtyAndAlloc(List<TN_WM_B_AREA_QTYEntity> areaQtyList, IDbTransaction trans)
|
{
|
string sql = @"UPDATE TN_WM_B_AREA_QTY SET CN_F_QUANTITY=@CN_F_QUANTITY,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, DateTime.Now.ToString("yyyyMMddHHmmssffff"));
|
return ExecuteTranSql(sql, areaQtyList, trans);
|
}
|
|
/// <summary>
|
/// 修改多条数据库存量
|
/// </summary>
|
/// <param name="lstModel"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public OperateResult UpdateAreaListAllocBysqlwhere(List<TN_WM_B_AREA_QTYEntity> list, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
foreach (TN_WM_B_AREA_QTYEntity entity in list)
|
{
|
string sql = @"UPDATE TN_WM_B_AREA_QTY SET CN_F_ALLOC_QTY=CN_F_ALLOC_QTY + " + entity.CN_F_ALLOC_QTY + " " + entity.SqlWhere + "";
|
|
result = ExecuteTranSql(sql, null, trans);
|
if (result.Status == ResultStatus.Error)
|
return result;
|
}
|
return result;
|
}
|
|
/// <summary>
|
/// 根据移库单 修改库区数量 移出库区减去数量 移入库区加上数量
|
/// </summary>
|
/// <param name="lstModel"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
/// <history>[HANHE(LT)] CREATED BY 2018-12-20</history>
|
public SqlExecuteResult MoveInfoUpdateAreaQty(List<TN_WM_B_AREA_QTYEntity> lstModel, DbTransaction trans)
|
{
|
SqlExecuteResult result = new SqlExecuteResult();
|
List<DbCommand> cmdlist = new List<DbCommand>();
|
foreach (TN_WM_B_AREA_QTYEntity model in lstModel)
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
//移出库区数量
|
//strSql.Append("UPDATE TN_WM_B_AREA_QTY SET CN_F_QUANTITY=CN_F_QUANTITY-:CN_F_QUANTITY,CN_F_ALLOC_QTY=CN_F_ALLOC_QTY-:CN_F_QUANTITY WHERE CN_S_STOCK_AREA='" + model.startArea + "' AND " + model.SqlWhere + "");
|
|
strSql.Append(" if exists(select * from TN_WM_B_AREA_QTY WHERE CN_S_STOCK_AREA='" + model.endArea + "' AND " + model.SqlWhere1 + " )");
|
strSql.Append("UPDATE TN_WM_B_AREA_QTY SET CN_S_OWNER='" + model.endBit + "',CN_F_QUANTITY=CN_F_QUANTITY+:CN_F_QUANTITY WHERE CN_S_STOCK_AREA='" + model.endArea + "' AND " + model.SqlWhere1 + "");
|
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,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_S_STOCK_AREA,:CN_S_NOTE,:CN_S_TIMESTAMP,:CN_S_PRODUCTION_BATCH)");
|
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, 0);
|
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);
|
DataAccess.AddInParameter(cmd, "CN_S_TIMESTAMP", ComDbType.STRING, model.CN_S_TIMESTAMP);
|
DataAccess.AddInParameter(cmd, "CN_S_PRODUCTION_BATCH", ComDbType.STRING, model.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(XDL)] CREATED BY 2018-11-15</History>
|
public OperateResult DeleteAreaQtys(List<TN_WM_B_AREA_QTYEntity> list, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
|
foreach (TN_WM_B_AREA_QTYEntity entity in list)
|
{
|
string sql = @" UPDATE TN_WM_B_AREA_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>
|
/// 降某个特定库区所在的库区量表
|
/// </summary>
|
/// <param name="arrivalCode">到货单号</param>
|
/// <param name="itemCode">物料编码</param>
|
/// <param name="areaCode">库区编码</param>
|
/// <param name="offSetQuantity">补缺数量</param>
|
/// <param name="trans"></param>
|
public OperateResult DeleteAreaQtyByAreaCode(string arrivalCode, string itemCode, string areaCode, string offSetQuantity, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
string sql = @" UPDATE TN_WM_B_AREA_QTY SET CN_F_QUANTITY =Q.CN_F_QUANTITY-" + Convert.ToDecimal(offSetQuantity) + @" FROM
|
TN_WM_B_AREA_QTY Q,TN_WM_INCREASE_INVENTORY_DTL IND,TN_WM_INCREASE_INVENTORY_MST INMST
|
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=IND.CN_S_LOT_NO AND INMST.CN_S_OP_NO=IND.CN_S_OP_NO
|
AND Q.CN_S_OWNER=IND.CN_S_OWNER AND INMST.CN_S_ARRIVAL_NO='" + arrivalCode + @"'
|
AND Q.CN_S_ITEM_CODE='" + itemCode + "' AND Q.CN_S_STOCK_AREA='" + areaCode + "' ";
|
Log.Info("===>降某个特定库区所在的库区量表", sql.ToString());
|
result = ExecuteTranSql(sql.ToString(), null, trans);
|
if (result.Success)
|
{
|
string sqlstr = @" DELETE FROM TN_WM_B_AREA_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 2018-11-15</History>
|
public OperateResult DeleteAreaQty(string opNoList, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
string sql = @" UPDATE TN_WM_B_AREA_QTY SET CN_F_QUANTITY =Q.CN_F_QUANTITY-IND.CN_F_QUANTITY FROM
|
TN_WM_B_AREA_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 ISNULL(Q.CN_S_PRODUCTION_BATCH,'')=ISNULL(IND.CN_S_PRODUCTION_BATCH,'') 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_AREA_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 DeleteAreaQtyArr(string opNoList, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
string sql = @" UPDATE TN_WM_B_AREA_QTY SET CN_F_QUANTITY =Q.CN_F_QUANTITY-IND.CN_F_QUANTITY FROM
|
TN_WM_B_AREA_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 ISNULL(Q.CN_S_PRODUCTION_BATCH,'')=ISNULL(IND.CN_S_PRODUCTION_BATCH,'') 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_AREA_QTY where CN_F_QUANTITY<=0 ";
|
result = ExecuteTranSql(sqlstr.ToString(), null, trans);
|
}
|
return result;
|
}
|
|
/// <summary>
|
/// 删除为0库区
|
/// </summary>
|
/// <returns></returns>
|
public OperateResult DeleteAreaQty(IDbTransaction trans)
|
{
|
string sql = "DELETE dbo.tn_wm_b_area_qty WHERE CN_F_QUANTITY<=0";
|
return ExecuteTranSql(sql, null, trans);
|
}
|
|
}
|
}
|
