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
{
///
/// 降库区量表并降分配量
///
/// 库区量实体集合
/// 暂存区
///
/// [HANHE(XDL)] CREATED BY 2018-11-14
public OperateResult ReduceAreaAllocQty(List 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;
}
///
/// 获得库区下每个物料的数量
///
///
///
///
public List 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 list = DataAccessExtensive.ExecuteListEntity(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);
}
///
/// 根据物料汇总当前库存
///
/// 物料编码
/// 条件
///
/// [HANHE(XDL)] CREATED BY 2018-11-16
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(sbStr.ToString());
}
///
/// 根据物料汇总当前库存
///
/// 物料编码
/// 条件
///
/// [HANHE(XDL)] CREATED BY 2018-11-16
public List GetStockQtyInfo(string strWhere)
{
List areaQtyEntity = new List();
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(sbStr.ToString());
}
public List GetStockQtyDetailList(string strWhere)
{
List lstStockDetail = new List();
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(sbStr.ToString());
}
///
/// 获得库区下每个物料的数量
///
///
///
///
public List 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 list = ExecuteQuery(strSql.ToString());
return list;
}
///
/// 根据货主获取库存
///
///
///
public List 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 list = ExecuteQuery(strSql.ToString());
return list;
}
///
/// 入库单取消时,判断物料是否可取消
///
///
///
public List 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 list = ExecuteQuery(strSql.ToString());
return list;
}
///
/// 升库区库存
///
///
/// 是否默认货主
///
/// [HANHE(XDL)] CREATED BY 2018-11-14
public OperateResult AddAreaQty(List 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;
}
///
/// 升库区库存
///
///
///
///
///
///
///
public OperateResult AddAreaQty(List 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;
}
///
/// 升库区库存
///
///
/// 是否默认货主
///
/// [HANHE(XDL)] CREATED BY 2018-11-14
public OperateResult AddAreaQtyAndAlloc(List 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;
}
///
/// 升库区库存
///
///
///
///
///
///
///
public OperateResult AddAreaQtyAndAlloc(List 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;
}
///
/// 升分配量
///
///
///
///
///
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;
}
///
/// 降分配量
///
///
///
///
///
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;
}
///
/// 降分配量
///
///
///
///
///
public OperateResult ReduceAllocQty(string areaCode, string trayCode, IDbTransaction trans, LogPara logPara)
{
var result = new OperateResult();
var trayItemMsts = DALCreator.Create>().GetList(new { CN_S_TRAY_CODE = trayCode });
foreach (var trayItemMst in trayItemMsts)
{
trayItemMst.CN_F_QUANTITY = 0;
trayItemMst.TrayItemDtlList = DALCreator.Create>().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>().GetList(new { CN_S_TRAY_CODE = trayCode });
foreach (var trayItemMst in trayItemMsts)
{
trayItemMst.CN_F_QUANTITY = 0;
trayItemMst.TrayItemDtlList = DALCreator.Create>().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;
}
///
/// 降暂存区库区量表
///
/// 库区量实体集合
/// 暂存区
///
/// [HANHE(XDL)] CREATED BY 2018-11-14
public OperateResult ReduceAreaQty(List 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;
}
///
/// 降暂存区库区量表
///
/// 库区量实体集合
/// 暂存区
///
/// [HANHE(XDL)] CREATED BY 2018-11-14
public OperateResult ReduceAreaQty(List 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;
}
///
/// 降货主所在库区量
///
///
///
///
public OperateResult ReduceOwnerAreaQty(List 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;
}
///
/// 降库区量表并降分配量
///
/// 库区量实体集合
/// 暂存区
///
/// [HANHE(XDL)] CREATED BY 2018-11-14
public OperateResult ReduceAreaQtyAndAllocQty(List 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;
}
///
/// 修改库区分配量
///
///
///
public OperateResult UpdateAreaAlloc(List 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);
}
///
/// 修改库区数量
///
///
///
///
public OperateResult UpdateAreaQty(List 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);
}
///
/// 修改库存量和分配量
///
///
///
///
public OperateResult UpdateAreaQtyAndAlloc(List 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);
}
///
/// 修改多条数据库存量
///
///
///
///
public OperateResult UpdateAreaListAllocBysqlwhere(List 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;
}
///
/// 根据移库单 修改库区数量 移出库区减去数量 移入库区加上数量
///
///
///
///
/// [HANHE(LT)] CREATED BY 2018-12-20
public SqlExecuteResult MoveInfoUpdateAreaQty(List lstModel, DbTransaction trans)
{
SqlExecuteResult result = new SqlExecuteResult();
List cmdlist = new List();
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);
}
///
/// 降库区库存量表
///
///
///
///
/// [HANHE(XDL)] CREATED BY 2018-11-15
public OperateResult DeleteAreaQtys(List 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;
}
///
/// 降某个特定库区所在的库区量表
///
/// 到货单号
/// 物料编码
/// 库区编码
/// 补缺数量
///
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;
}
///
/// 入库单反审时降库区量表
///
///
///
///
/// [HANHE(XDL)] CREATED BY 2018-11-15
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;
}
///
/// 到货单反审时降库区量表
///
///
///
///
/// [HANHE(XDL)] CREATED BY 2019-03-04
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;
}
///
/// 删除为0库区
///
///
public OperateResult DeleteAreaQty(IDbTransaction trans)
{
string sql = "DELETE dbo.tn_wm_b_area_qty WHERE CN_F_QUANTITY<=0";
return ExecuteTranSql(sql, null, trans);
}
}
}