using HH.WMS.Entitys;
|
using System;
|
using System.Collections.Generic;
|
using System.Configuration;
|
using System.Data;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
using Dapper;
|
using HH.WMS.Common;
|
using HH.WMS.Entitys.Basic;
|
using HH.WMS.Entitys.Common;
|
|
namespace HH.WMS.DAL.InStock
|
{
|
/// <summary>
|
/// 增加库存业务表-入库单数据处理层
|
/// </summary>
|
public class TN_WM_INCREASE_INVENTORYDAL : DapperBaseDAL
|
{
|
#region 更新入库单状态
|
/// <summary>
|
/// 提交入库单-主表
|
/// </summary>
|
/// <param name="entity">审批实体</param>
|
/// <param name="examineType">审批类型（已提交、已审核、已反审、已驳回）</param>
|
/// <returns></returns>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-14</History>
|
public OperateResult ExamineMst(List<TN_WM_B_EXAMINEEntity> entity, string examineType, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
foreach (var model in entity)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append(" UPDATE TN_WM_INCREASE_INVENTORY_MST SET CN_S_STATE=@CN_S_STATE,CN_S_AUDITOR=@CN_S_AUDITOR,CN_T_AUDIT_TIME=@CN_T_AUDIT_TIME,CN_S_AUDIT_REMARK=@CN_S_AUDIT_REMARK WHERE CN_S_OP_NO=@CN_S_OP_NO");
|
|
result = ExecuteTranSql(strSql.ToString(), new
|
{
|
CN_S_STATE = examineType,
|
CN_S_OP_NO = model.CN_S_FROM_NO,
|
CN_S_AUDITOR = model.CN_S_CREATOR,
|
CN_T_AUDIT_TIME = DateTime.Now,
|
CN_S_AUDIT_REMARK = model.CN_S_OPINION
|
}, trans);
|
if (result.Status == ResultStatus.Error)
|
return result;
|
}
|
return result;
|
}
|
|
/// <summary>
|
/// 提交入库单-子表
|
/// </summary>
|
/// <param name="entity">审批实体</param>
|
/// <param name="examineType">审批类型（已提交、已审核、已反审、已驳回）</param>
|
/// <returns></returns>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-14</History>
|
public OperateResult ExamineDtl(List<TN_WM_B_EXAMINEEntity> entity, string examineType, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
foreach (var model in entity)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append(" UPDATE TN_WM_INCREASE_INVENTORY_DTL SET CN_S_STATE=@CN_S_STATE WHERE CN_S_OP_NO=@CN_S_OP_NO");
|
|
result = ExecuteTranSql(strSql.ToString(), new
|
{
|
CN_S_STATE = examineType,
|
CN_S_OP_NO = model.CN_S_FROM_NO
|
}, trans);
|
if (result.Status == ResultStatus.Error)
|
return result;
|
}
|
return result;
|
}
|
#endregion
|
|
#region 查询数据，返回数据集合
|
//根据入库单号获取数据集合
|
/// <summary>
|
/// 根据入库单号获取数据
|
/// </summary>
|
/// <param name="opNo">入库单号</param>
|
/// <returns></returns>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-15</History>
|
public DataTable GetIncreaseData(string opNo)
|
{
|
StringBuilder sbStr = new StringBuilder();
|
DataTable dt = new DataTable();
|
|
sbStr.Append("SELECT MST.CN_S_VENDOR_NO,MST.CN_S_VENDOR_NAME,MST.CN_S_OP_TYPE,MST.CN_S_FROM_NO,(SELECT TOP 1 CN_S_OPERATION_CODE FROM dbo.tn_wm_operation_type ty WHERE ty.CN_S_OPERATION_NAME=MST.CN_S_OP_TYPE ) CN_S_OPERATION_CODE,MST.CN_S_OWNER,MST.CN_S_STOCK_CODE,MST.CN_S_LOT_NO,DTL.* FROM TN_WM_INCREASE_INVENTORY_DTL DTL LEFT JOIN TN_WM_INCREASE_INVENTORY_MST MST ");
|
sbStr.Append("ON DTL.CN_S_OP_NO =MST.CN_S_OP_NO ");
|
|
if (!string.IsNullOrEmpty(opNo))
|
{
|
sbStr.Append("WHERE MST.CN_S_OP_NO='" + opNo + "' ");
|
}
|
else
|
{
|
return dt;
|
}
|
return ExecuteDataTable(sbStr.ToString());
|
}
|
|
|
public DataTable GetIncreaseList(SearchModel searchModel, out long total, bool isExport)
|
{
|
var condition = searchModel.SearchCondition;
|
string sql = " select * from TN_WM_INCREASE_INVENTORY_MST where 1=1 ";
|
string ConStr = "";
|
if (condition != null)
|
{
|
if (!string.IsNullOrEmpty(condition.CN_S_OP_NO.ToString()))
|
{
|
ConStr = ConStr + " and CN_S_OP_NO like '%" + Util.ToStringInput(condition.CN_S_OP_NO) + "%'";
|
}
|
string state = condition.CN_S_STATE.ToString();
|
if (!string.IsNullOrEmpty(state))
|
{
|
if (state.IndexOf(",") > 0)
|
{
|
state = "'" + state.Replace(",", "','") + "'";
|
ConStr = ConStr + " and CN_S_STATE in (" + state + ")";
|
}
|
else
|
{
|
ConStr = ConStr + " and CN_S_STATE='" + state + "'";
|
}
|
}
|
|
if (!string.IsNullOrEmpty(condition.CN_T_START.ToString()))
|
{
|
ConStr = ConStr + " and CN_T_CREATE >= '" + Util.ToStringInput(condition.CN_T_START) + " 00:00:00'";
|
}
|
if (!string.IsNullOrEmpty(condition.CN_T_END.ToString()))
|
{
|
ConStr = ConStr + " and CN_T_CREATE <= '" + Util.ToStringInput(condition.CN_T_END) + " 23:59:59'";
|
}
|
|
|
ConStr = ConStr + " and CN_S_OP_TYPE != '盈亏入库' and CN_S_OP_TYPE != '盘盈入库'";
|
|
if (!string.IsNullOrEmpty(condition.CN_S_OP_TYPE.ToString()))
|
{
|
ConStr = ConStr + " and CN_S_OP_TYPE = '" + Util.ToStringInput(condition.CN_S_OP_TYPE) + "'";
|
}
|
if (!string.IsNullOrEmpty(condition.CN_S_VENDOR_NAME.ToString()))
|
{
|
ConStr = ConStr + " and CN_S_VENDOR_NAME like '%" + Util.ToStringInput(condition.CN_S_VENDOR_NAME) + "%'";
|
}
|
if (!string.IsNullOrEmpty(condition.IsHandWork.ToString()))
|
{
|
if (condition.IsHandWork.ToString() == "true" || condition.IsHandWork.ToString() == "True")
|
ConStr = ConStr + " and (CN_S_FROM_NO='' or CN_S_FROM_NO IS NULL) ";
|
else
|
ConStr = ConStr + " and (CN_S_FROM_NO!='' and CN_S_FROM_NO IS not NULL) ";
|
}
|
}
|
if (!string.IsNullOrEmpty(ConStr))
|
{
|
sql = sql + ConStr;
|
}
|
OperateResult result = new OperateResult();
|
DataTable dt = new DataTable();
|
if (!isExport)
|
{
|
return ExecutePagingData(" (" + sql + ") t ", searchModel.PageIndex, searchModel.PageSize, out total, "", "");
|
}
|
else
|
{
|
total = 0;//无效
|
return ExecuteDataTable(sql, "");
|
}
|
// return ExecutePagingResult(sql, searchModel.PageIndex, searchModel.PageSize, "", "");
|
}
|
|
|
|
|
#endregion
|
|
#region 获取可上架入库单列表(PDA)
|
/// <summary>
|
/// 获取可上架入库单列表
|
/// </summary>
|
/// <param name="CN_S_OP_NO">模糊查询条件：入库单号</param>
|
/// <returns></returns>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-13</History>
|
public List<TN_WM_INCREASE_INVENTORY_MSTEntity> GetIncreaseInventoryList(string CN_S_OP_NO)
|
{
|
StringBuilder sbStr = new StringBuilder();
|
|
sbStr.Append("SELECT CN_S_OP_NO,CN_S_OP_TYPE,CN_S_STOCK_CODE,CN_S_VENDOR_NO,CN_S_VENDOR_NAME,CN_S_CREATOR,CN_S_CREATOR_BY,CN_T_CREATE from TN_WM_INCREASE_INVENTORY_MST ");
|
sbStr.Append("WHERE CN_S_OP_NO IN (SELECT CN_S_OP_NO FROM TN_WM_INCREASE_INVENTORY_DTL WHERE CN_S_STATE='" + Constants.State_Audit + "' AND CN_S_OP_TYPE!='盘盈入库' ");
|
|
switch (NowDbType)
|
{
|
case DBTYPE.MSSQL:
|
sbStr.Append("AND ISNULL(CN_F_UPSHELF_QTY,0)<ISNULL(CN_F_QUANTITY,0) ");
|
break;
|
case DBTYPE.MYSQL:
|
sbStr.Append("AND IFNULL(CN_F_UPSHELF_QTY,0)<IFNULL(CN_F_QUANTITY,0) ");
|
break;
|
case DBTYPE.ORACLE:
|
sbStr.Append("AND NVL(CN_F_UPSHELF_QTY,0)<NVL(CN_F_QUANTITY,0) ");
|
break;
|
default:
|
break;
|
}
|
sbStr.Append(")");
|
|
if (!string.IsNullOrEmpty(CN_S_OP_NO))
|
{
|
sbStr.Append("AND CN_S_OP_NO LIKE '%" + CN_S_OP_NO + "%' ");
|
}
|
return ExecuteQuery<TN_WM_INCREASE_INVENTORY_MSTEntity>(sbStr.ToString()).ToList();
|
}
|
#endregion
|
|
#region 获取可码盘入库单列表(PDA)
|
/// <summary>
|
/// 获取可码盘入库单列表
|
/// </summary>
|
/// <param name="CN_S_OP_NO">模糊查询条件：入库单号</param>
|
/// <returns></returns>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-12-5</History>
|
public List<TN_WM_INCREASE_INVENTORY_MSTEntity> GetInListWithCodedisc(string CN_S_OP_NO)
|
{
|
string sql = "SELECT CN_S_OP_NO,CN_S_OP_TYPE,CN_S_STOCK_CODE,CN_S_VENDOR_NO,CN_S_VENDOR_NAME,CN_S_CREATOR,CN_S_CREATOR_BY,CN_T_CREATE from TN_WM_INCREASE_INVENTORY_MST WHERE CN_S_OP_NO IN (SELECT CN_S_OP_NO FROM TN_WM_INCREASE_INVENTORY_DTL WHERE ISNULL(CN_F_ADD_ONTRAY_QTY,0)<CN_F_QUANTITY) AND CN_S_STATE ='" + Constants.State_Audit + "' AND CN_S_OP_TYPE!='盘盈入库' ";
|
if (!string.IsNullOrEmpty(CN_S_OP_NO))
|
{
|
sql = sql + " AND CN_S_OP_NO like '%" + CN_S_OP_NO + "%'";
|
}
|
return ExecuteQuery<TN_WM_INCREASE_INVENTORY_MSTEntity>(sql);
|
}
|
#endregion
|
|
#region 根据入库单号获取入库单明细(PDA)
|
/// <summary>
|
/// 根据入库单号获取入库单明细
|
/// </summary>
|
/// <param name="CN_S_OP_NO">入库单号</param>
|
/// <returns></returns>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-13</History>
|
public List<TN_WM_INCREASE_INVENTORY_DTLEntity> GetIncreaseInventoryDetail(string CN_S_OP_NO)
|
{
|
StringBuilder sbStr = new StringBuilder();
|
|
sbStr.Append("SELECT MST.CN_S_OP_TYPE,MST.CN_S_STOCK_CODE,MST.CN_S_VENDOR_NO,MST.CN_S_VENDOR_NAME,DTL.CN_S_OP_NO, ");
|
sbStr.Append("DTL.CN_S_ITEM_CODE,DTL.CN_S_ITEM_NAME,DTL.CN_S_ITEM_STATE,DTL.CN_F_QUANTITY,MST.CN_S_LOT_NO,MST.CN_S_OWNER,DTL.CN_F_ACTUAL_PRICE,DTL.CN_T_PRODUCTION,DTL.CN_T_EXPIRATION,DTL.CN_S_PRODUCTION_BATCH ");
|
sbStr.Append("FROM TN_WM_INCREASE_INVENTORY_DTL DTL LEFT JOIN TN_WM_INCREASE_INVENTORY_MST MST ");
|
sbStr.Append("ON DTL.CN_S_OP_NO=MST.CN_S_OP_NO ");
|
|
if (!string.IsNullOrEmpty(CN_S_OP_NO))
|
{
|
sbStr.Append("WHERE MST.CN_S_OP_NO='" + CN_S_OP_NO.Trim() + "' ");
|
}
|
return ExecuteQuery<TN_WM_INCREASE_INVENTORY_DTLEntity>(sbStr.ToString()).ToList();
|
}
|
#endregion
|
|
#region 更新累积入库数
|
/// <summary>
|
/// 更新累积入库数
|
/// </summary>
|
/// <param name="list">更新实体集合</param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-14</History>
|
public OperateResult UpdateAllocQty(List<ItemRow> list, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
foreach (var item in list)
|
{
|
string sq1 = "UPDATE TN_WM_INCREASE_INVENTORY_DTL SET CN_F_ALLOT_QTY=CN_F_ALLOT_QTY+@CN_F_ALLOT_QTY WHERE CN_S_OP_NO=@CN_S_OP_NO AND CN_S_ITEM_CODE=@CN_S_ITEM_CODE";
|
result = ExecuteTranSql(sq1, new
|
{
|
CN_F_ALLOT_QTY = item.CN_F_QUANTITY,
|
CN_S_OP_NO = item.CN_S_FROM_NO,
|
CN_S_ITEM_CODE = item.CN_S_ITEM_CODE
|
}, trans);
|
if (result.Status == ResultStatus.Error)
|
return result;
|
}
|
return result;
|
}
|
#endregion
|
|
#region 获取入库
|
/// <summary>
|
/// 获取入库
|
/// </summary>
|
/// <param name="sqlwhere">条件</param>
|
/// <returns></returns>
|
/// <History>[HANHE(lt)] CREATED BY 2018-11-15</History>
|
public DataTable GetDataTableInventory(string sqlwhere)
|
{
|
string sql = "SELECT * FROM TN_WM_INCREASE_INVENTORY_MST AS a INNER JOIN dbo.TN_WM_INCREASE_INVENTORY_DTL AS b ON b.CN_S_OP_NO = a.CN_S_OP_NO where {0}";
|
sql = string.Format(sql, sqlwhere);
|
return ExecuteDataTable(sql);
|
}
|
#endregion
|
|
#region 获取入库列表
|
/// <summary>
|
/// 获取入库列表
|
/// </summary>
|
/// <param name="sqlwhere">条件</param>
|
/// <returns></returns>
|
/// <History>[HANHE(lt)] CREATED BY 2018-11-22</History>
|
public DataTable GetList(string sqlwhere)
|
{
|
string sql = "select * from TN_WM_INCREASE_INVENTORY_MST where {0}";
|
sql = string.Format(sql, sqlwhere);
|
return ExecuteDataTable(sql);
|
}
|
#endregion
|
|
#region 获取入库订单列表
|
/// <summary>
|
/// 获取入库订单列表
|
/// </summary>
|
/// <param name="sqlwhere">条件</param>
|
/// <returns></returns>
|
/// <History>[HANHE(lt)] CREATED BY 2018-11-22</History>
|
public DataTable GetInMstList(string sqlwhere)
|
{
|
string sql = "select * from tn_wm_in_mst {0}";
|
sql = string.Format(sql, sqlwhere);
|
return ExecuteDataTable(sql);
|
}
|
|
|
/// <summary>
|
/// 获取入库订单列表
|
/// </summary>
|
/// <param name="sqlwhere">条件</param>
|
/// <returns></returns>
|
/// <History>[HANHE(lt)] CREATED BY 2018-11-22</History>
|
public DataTable GetInDtlList(string sqlwhere)
|
{
|
string sql = "select * from tn_wm_in_dtl {0}";
|
sql = string.Format(sql, sqlwhere);
|
return ExecuteDataTable(sql);
|
}
|
#endregion
|
|
#region 更改入库单状态
|
/// <summary>
|
/// 更改入库单状态(已码盘)
|
/// </summary>
|
/// <param name="list">更新列表</param>
|
/// <param name="state">状态</param>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-17</History>
|
public OperateResult UpdateInventoryState(List<TrayItemRow> list, string state)
|
{
|
OperateResult result = new OperateResult();
|
foreach (var item in list)
|
{
|
StringBuilder sbStr = new StringBuilder();
|
sbStr.Append(" IF EXISTS( SELECT CN_S_OP_NO FROM TN_WM_INCREASE_INVENTORY_DTL P LEFT JOIN ");
|
sbStr.Append(" (SELECT SUM(CN_F_QUANTITY) CN_F_QUANTITY,CN_S_ITEM_CODE,CN_S_LOT_NO FROM TN_WM_B_TRAY_ITEM_REL ");
|
sbStr.Append(" WHERE CN_S_LOT_NO='" + item.CN_S_LOT_NO + "' GROUP BY CN_S_LOT_NO,CN_S_ITEM_CODE) DT ON P.CN_S_ITEM_CODE=DT.CN_S_ITEM_CODE ");
|
sbStr.Append(" WHERE DT.CN_F_QUANTITY>=P.CN_F_QUANTITY AND P.CN_S_OP_NO='" + item.CN_S_OP_NO + "') ");
|
sbStr.Append(" UPDATE TN_WM_INCREASE_INVENTORY_DTL SET CN_S_STATE='" + state + "' WHERE CN_S_OP_NO='" + item.CN_S_OP_NO + "' ");
|
result = ExecuteTranSql(sbStr.ToString(), null, null);
|
if (result.Status == ResultStatus.Error)
|
return result;
|
}
|
return result;
|
}
|
|
|
/// <summary>
|
/// 获取采购单
|
/// </summary>
|
/// <param name="sqlwhere">条件</param>
|
/// <returns></returns>
|
/// <History>[HANHE(lt)] CREATED BY 2018-11-15</History>
|
public DataTable GetMstJoinDtl(string sqlwhere)
|
{
|
string sql = "SELECT * FROM TN_WM_IN_MST AS a INNER JOIN TN_WM_IN_DTL AS b ON b.CN_S_OP_NO = a.CN_S_OP_NO {0}";
|
sql = string.Format(sql, sqlwhere);
|
return ExecuteDataTable(sql);
|
}
|
#endregion
|
|
|
/// <summary>
|
///物料上架-根据单号和物料编码更新累积上架数
|
/// </summary>
|
/// <param name="list"></param>
|
/// <param name="trans"></param>
|
public OperateResult updateUpselefQty(List<ItemRow> list, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
foreach (var item in list)
|
{
|
string sql = @"UPDATE TN_WM_INCREASE_INVENTORY_DTL SET CN_F_UPSHELF_QTY=ISNULL(CN_F_UPSHELF_QTY,0)+" + Convert.ToDecimal(item.CN_F_QUANTITY) + " WHERE CN_S_OP_NO='" + item.CN_S_FROM_NO + "' AND CN_S_ITEM_CODE='" + item.CN_S_ITEM_CODE + "' ";
|
Log.Info("更新累积上架数", sql);
|
result = ExecuteTranSql(sql, null, trans);
|
if (result.Status == ResultStatus.Error)
|
return result;
|
}
|
return result;
|
}
|
/// <summary>
|
/// 物料码盘-根据单号和物料编码更新累积码盘数
|
/// </summary>
|
/// <param name="list"></param>
|
/// <param name="trans"></param>
|
public OperateResult updateOntrayQty(List<TrayItemRow> list, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
foreach (var item in list)
|
{
|
string sql = @"UPDATE TN_WM_INCREASE_INVENTORY_DTL SET CN_F_ADD_ONTRAY_QTY=ISNULL(CN_F_ADD_ONTRAY_QTY,0)+" + Convert.ToDecimal(item.CN_F_QUANTITY) + " WHERE CN_S_OP_NO='" + item.CN_S_OP_NO + "' AND CN_S_ITEM_CODE='" + item.CN_S_ITEM_CODE + "' ";
|
Log.Info("更新累积码盘数", sql);
|
result = ExecuteTranSql(sql, null, trans);
|
if (result.Status == ResultStatus.Error)
|
return result;
|
}
|
return result;
|
}
|
/// <summary>
|
/// 物料上架-根据单号和物料编码更新累积码盘数
|
/// </summary>
|
/// <param name="list"></param>
|
/// <param name="trans"></param>
|
public OperateResult updateOntrayQty(List<ItemRow> list, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
foreach (var item in list)
|
{
|
string sql = @"UPDATE TN_WM_INCREASE_INVENTORY_DTL SET CN_F_ADD_ONTRAY_QTY=ISNULL(CN_F_ADD_ONTRAY_QTY,0)+" + Convert.ToDecimal(item.CN_F_QUANTITY) + " WHERE CN_S_OP_NO='" + item.CN_S_FROM_NO + "' AND CN_S_ITEM_CODE='" + item.CN_S_ITEM_CODE + "' ";
|
Log.Info("更新累积码盘数", sql);
|
result = ExecuteTranSql(sql, null, trans);
|
if (result.Status == ResultStatus.Error)
|
return result;
|
}
|
return result;
|
}
|
/// <summary>
|
///整托上架-根据批次和物料编码更新累积上架数
|
/// </summary>
|
/// <param name="list"></param>
|
/// <param name="trans"></param>
|
public OperateResult UpdateUpselefQtyByLot(List<TrayItemMstAndDtlEntity> Group_List, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
foreach (var item in Group_List)
|
{
|
if (string.IsNullOrEmpty(item.CN_S_LOT_NO)) continue;//没有批次的话，不用更新入库单累积上架数
|
string sql = @"UPDATE TN_WM_INCREASE_INVENTORY_DTL SET CN_F_UPSHELF_QTY=ISNULL(CN_F_UPSHELF_QTY,0)+" + Convert.ToDecimal(item.CN_F_QUANTITY) + " WHERE CN_S_OP_NO in (select CN_S_OP_NO from TN_WM_INCREASE_INVENTORY_MST where CN_S_LOT_NO ='" + item.CN_S_LOT_NO + "') AND CN_S_ITEM_CODE='" + item.CN_S_ITEM_CODE + "' ";
|
|
result = ExecuteTranSql(sql, null, trans);
|
if (result.Status == ResultStatus.Error)
|
return result;
|
}
|
return result;
|
}
|
|
/// <summary>
|
/// 根据入库单号获取可码盘明细
|
/// </summary>
|
/// <param name="CN_S_OP_NO">入库单号</param>
|
/// <returns></returns>
|
public List<TN_WM_INCREASE_INVENTORY_DTLEntity> GetDtlList_OnTray(string CN_S_OP_NO)
|
{
|
StringBuilder sbStr = new StringBuilder();
|
|
sbStr.Append("SELECT * FROM TN_WM_INCREASE_INVENTORY_DTL WHERE 1=1 ");
|
if (!string.IsNullOrEmpty(CN_S_OP_NO))
|
{
|
sbStr.Append("AND CN_S_OP_NO='" + CN_S_OP_NO.Trim() + "' ");
|
}
|
sbStr.Append("AND ISNULL(CN_F_ADD_ONTRAY_QTY,0)!=CN_F_QUANTITY ");
|
|
return ExecuteQuery<TN_WM_INCREASE_INVENTORY_DTLEntity>(sbStr.ToString()).ToList();
|
}
|
|
/// <summary>
|
/// 根据入库单号获取可上架明细
|
/// </summary>
|
/// <param name="CN_S_OP_NO">入库单号</param>
|
/// <returns></returns>
|
public List<TN_WM_INCREASE_INVENTORY_DTLEntity> GetInMstAndDtl_OnShelf(string CN_S_OP_NO)
|
{
|
StringBuilder sbStr = new StringBuilder();
|
|
sbStr.Append("SELECT * FROM TN_WM_INCREASE_INVENTORY_DTL WHERE 1=1 ");
|
if (!string.IsNullOrEmpty(CN_S_OP_NO))
|
{
|
sbStr.Append("AND CN_S_OP_NO='" + CN_S_OP_NO.Trim() + "' ");
|
}
|
sbStr.Append("AND ISNULL(CN_F_UPSHELF_QTY,0)!=CN_F_QUANTITY ");
|
|
return ExecuteQuery<TN_WM_INCREASE_INVENTORY_DTLEntity>(sbStr.ToString()).ToList();
|
}
|
|
/// <summary>
|
/// 更新托盘物料关联
|
/// </summary>
|
/// <param name="transVouchList">物料批次集合</param>
|
/// <returns></returns>
|
public OperateResult UpdateTrayItemState(List<U8_TransVouchEntity> transVouchList, IDbTransaction trans)
|
{
|
string sql = "UPDATE TN_WM_B_TRAY_ITEM_MST SET CN_S_ITEM_STATE=@CN_S_ITEM_STATE WHERE CN_S_ITEM_CODE=@CN_S_ITEM_CODE AND CN_GUID IN (SELECT CN_PARENT_GUID FROM TN_WM_B_TRAY_ITEM_DTL WHERE CN_S_PRODUCTION_BATCH=@CN_S_PRODUCTION_BATCH) ";
|
var result = ExecuteTranSql(sql, transVouchList, trans);
|
return result;
|
}
|
|
/// <summary>
|
/// 更新仓库量表物料状态
|
/// </summary>
|
/// <param name="transVouchList">物料批次集合</param>
|
/// <returns></returns>
|
public OperateResult UpdateStockQtyItemState(List<U8_TransVouchEntity> transVouchList, IDbTransaction trans)
|
{
|
string sql = "UPDATE TN_WM_B_STOCK_QTY SET CN_S_ITEM_STATE=@CN_S_ITEM_STATE WHERE CN_S_ITEM_CODE =@CN_S_ITEM_CODE and CN_S_PRODUCTION_BATCH=@CN_S_PRODUCTION_BATCH ";
|
var result = ExecuteTranSql(sql, transVouchList, trans);
|
return result;
|
}
|
|
/// <summary>
|
/// 更新库区量表物料状态
|
/// </summary>
|
/// <param name="transVouchList">物料批次集合</param>
|
/// <returns></returns>
|
public OperateResult UpdateAreaQtyItemState(List<U8_TransVouchEntity> transVouchList, IDbTransaction trans)
|
{
|
string sql = "UPDATE TN_WM_B_AREA_QTY SET CN_S_ITEM_STATE=@CN_S_ITEM_STATE WHERE CN_S_ITEM_CODE =@CN_S_ITEM_CODE and CN_S_PRODUCTION_BATCH=@CN_S_PRODUCTION_BATCH ";
|
var result = ExecuteTranSql(sql, transVouchList, trans);
|
return result;
|
}
|
|
/// <summary>
|
/// 更新XML表物料状态
|
/// </summary>
|
/// <param name="transVouchList">物料批次集合</param>
|
/// <returns></returns>
|
public OperateResult UpdateXmlItemState(List<U8_TransVouchEntity> transVouchList, IDbTransaction trans)
|
{
|
string sql = "UPDATE TN_WM_B_UNIQUE_BARCODE SET CN_S_ITEM_STATE=@CN_S_ITEM_STATE WHERE CN_S_ITEM_CODE =@CN_S_ITEM_CODE and CN_S_BATCH_NO=@CN_S_BATCH_NO ";
|
var result = ExecuteTranSql(sql, transVouchList, trans);
|
return result;
|
}
|
|
#region 获取未完全打印的入库单记录
|
/// <summary>
|
/// 获取未完全打印的入库单记录
|
/// </summary>
|
/// <param name="stockCode">仓库号</param>
|
/// <returns></returns>
|
/// <History>[HANHE(DBS)] CREATED BY 2019-02-28</History>
|
public OperateResult GetWaitPrintOrder(string stockCode)
|
{
|
string sql = @"SELECT a.CN_S_OP_NO
|
,[CN_S_OP_TYPE]
|
,[CN_S_STOCK_CODE]
|
,[CN_S_OP_FROM]
|
,[CN_S_FROM_NO]
|
,a.[CN_T_CREATE]
|
,[CN_S_DELIVERY_NOTE]
|
,[CN_S_ARRIVAL_NO]
|
,[CN_S_VENDOR_NO]
|
,[CN_S_VENDOR_NAME]
|
,sum(b.CN_F_QUANTITY) CN_F_QUANTITY
|
,sum(b.CN_F_PRINTING_QTY) CN_F_PRINTING_QTY
|
FROM TN_WM_INCREASE_INVENTORY_MST a
|
join TN_WM_INCREASE_INVENTORY_DTL b on a.CN_S_OP_NO=b.CN_S_OP_NO
|
where CN_S_STOCK_CODE=@CN_S_STOCK_CODE and b.CN_F_PRINTING_QTY <b.CN_F_QUANTITY
|
group by a.CN_S_OP_NO
|
,[CN_S_OP_TYPE]
|
,[CN_S_STOCK_CODE]
|
,[CN_S_OP_FROM]
|
,[CN_S_FROM_NO]
|
,a.[CN_T_CREATE]
|
,[CN_S_DELIVERY_NOTE]
|
,[CN_S_ARRIVAL_NO]
|
,[CN_S_VENDOR_NO]
|
,[CN_S_VENDOR_NAME]";
|
DataTable dt = ExecuteDataTable(sql, new { CN_S_STOCK_CODE = stockCode });
|
return OperateResult.Succeed("", dt);
|
}
|
#endregion
|
|
#region 获取所有待上架（暂存区）的物料(到货入+移库入)
|
/// <summary>
|
/// 获取所有待上架（暂存区）的物料(到货入+移库入)
|
/// </summary>
|
/// <param name="itemCode">物料编号</param>
|
/// <param name="zcAreaList">暂存区库区列表</param>
|
/// <returns></returns>
|
/// <History>[HANHE(DBS)] CREATED BY 2019-03-7</History>
|
public List<TN_WM_B_TRAY_ITEM_MSTEntity> GetWaitUpShelvesItems(string itemCode, List<AutoBomStockAreaEntity> zcAreaList)
|
{
|
//暂存区的量-分配量-待上架的量
|
string sql = @"select a.CN_S_ITEM_CODE,a.CN_S_ITEM_NAME,a.CN_S_MODEL,a.CN_S_FIGURE_NO,a.CN_S_MEASURE_UNIT
|
,(a.CN_F_QUANTITY-ISNULL(a.CN_F_ALLOC_QTY,0)-ISNULL(b.CN_F_QUANTITY,0)) CN_F_QUANTITY
|
from tn_wm_b_area_qty a left join
|
tn_wm_tray_alloc b on a.CN_S_ITEM_CODE=b.CN_S_ITEM_CODE
|
where a.CN_S_STOCK_AREA=@CN_S_STOCK_AREA and a.CN_S_ITEM_CODE=@CN_S_ITEM_CODE";
|
return ExecuteQuery<TN_WM_B_TRAY_ITEM_MSTEntity>(sql, new { CN_S_STOCK_AREA = zcAreaList[0].CN_S_AREA_CODE, CN_S_ITEM_CODE = itemCode });
|
// return ExecuteQuery<TN_WM_B_TRAY_ITEM_MSTEntity>(sql, new { CN_S_STOCK_AREA = "SHQ01", CN_S_ITEM_CODE = itemCode });
|
}
|
#endregion
|
|
#region 获取OMS拼接数据
|
/// <summary>
|
/// 获取OMS确认数据
|
/// </summary>
|
/// <param name="ArrivalNo"></param>
|
/// <returns></returns>
|
public DataTable GetDeleteInOmsModel(string opNo)
|
{
|
string sql = @"select distinct arrm.CN_S_OP_TYPE,insd.CN_S_INSPECT_NO,inm.CN_S_FROM_NO,insd.CN_S_ITEM_CODE,insd.CN_F_QUANTITY
|
,insd.CN_F_QUALIFIED,insd.CN_F_UNQUALIFIED,IND.CN_F_ACTUAL_PRICE ,IND.CN_F_ACTUAL_MONEY
|
from tn_wm_inspect_dtl insd
|
left join tn_wm_arrival_mst arrm on arrm.CN_S_ARRIVAL_NO=insd.CN_S_FROM_NO
|
left join tn_wm_in_mst inm on arrm.CN_S_FROM_NO=inm.CN_S_OP_NO
|
LEFT JOIN TN_WM_IN_DTL IND ON IND.CN_S_OP_NO=INM.CN_S_OP_NO and insd.CN_N_ROW_NO=ind.CN_N_ROW_NO
|
left join TN_WM_INCREASE_INVENTORY_MST inMst on inMst.CN_S_ARRIVAL_NO=arrm.CN_S_ARRIVAL_NO
|
where inMst.CN_S_OP_NO='" + opNo + "' and inm.CN_S_FROM_NO is not null";
|
return ExecuteDataTable(sql);
|
}
|
|
/// <summary>
|
/// 入库单审核时调用
|
/// </summary>
|
/// <param name="inNo"></param>
|
/// <returns></returns>
|
public DataTable GetOmsInModel(string inNo)
|
{
|
string sql = @"SELECT INSD.CN_S_OP_NO CN_S_FROM_NO,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_F_QUANTITY,CN_S_OP_TYPE,CN_S_STOCK_CODE,CN_S_VENDOR_NO,CN_S_VENDOR_NAME
|
,CN_F_ACTUAL_PRICE CN_F_PRICE,CN_F_ACTUAL_MONEY CN_F_MONEY_SUM,CN_S_FIGURE_NO,CN_S_MODEL,INMST.CN_S_DELIVERY_NOTE,CN_S_MEASURE_UNIT,INMST.CN_S_NOTE
|
FROM TN_WM_INCREASE_INVENTORY_DTL INSD
|
LEFT JOIN TN_WM_INCREASE_INVENTORY_MST INMST ON INMST.CN_S_OP_NO=INSD.CN_S_OP_NO
|
WHERE INMST.CN_S_OP_NO='" + inNo + "'";
|
return ExecuteDataTable(sql);
|
}
|
|
/// <summary>
|
/// 入库单审核时调用
|
/// </summary>
|
/// <param name="inNo"></param>
|
/// <returns></returns>
|
public DataTable GetOmsInOrderModel(string inNo)
|
{
|
string sql = @"SELECT distinct INSD.CN_S_ITEM_CODE,ad.CN_N_FROM_ROW_NO, INSD.CN_S_ITEM_NAME,INMST.CN_S_OP_TYPE,INMST.CN_S_STOCK_CODE,
|
INSD.CN_F_QUANTITY ,INMST.CN_S_VENDOR_NO,INMST.CN_S_VENDOR_NAME,ARRM.CN_S_FROM_NO,
|
INSD.CN_N_ROW_NO,INM.CN_S_FROM_NO CN_S_OP_NO,INSD.CN_F_ACTUAL_PRICE CN_F_PRICE,INSD.CN_F_ACTUAL_MONEY CN_F_MONEY_SUM,INSD.CN_S_FIGURE_NO,INSD.CN_S_MODEL,ARRM.CN_S_DELIVERY_NOTE
|
FROM TN_WM_INCREASE_INVENTORY_DTL INSD
|
LEFT JOIN TN_WM_INCREASE_INVENTORY_MST INMST ON INMST.CN_S_OP_NO=INSD.CN_S_OP_NO
|
LEFT JOIN TN_WM_ARRIVAL_MST ARRM ON ARRM.CN_S_ARRIVAL_NO=INMST.CN_S_ARRIVAL_NO
|
LEFT JOIN TN_WM_IN_MST INM ON INM.CN_S_OP_NO=ARRM.CN_S_FROM_NO
|
LEFT JOIN tn_wm_arrival_dtl AD ON ARRM.CN_S_ARRIVAL_NO=ad.CN_S_ARRIVAL_NO and INSD.CN_N_ROW_NO=ad.CN_N_ROW_NO
|
WHERE INMST.CN_S_OP_NO='" + inNo + "'";
|
return ExecuteDataTable(sql);
|
}
|
#endregion
|
|
#region PDA接口
|
/// <summary>
|
/// PDA接口-获取待贴码信息
|
/// </summary>
|
/// <param name="printBarCode">打码实体</param>
|
/// <returns></returns>
|
/// <History>[HANHE(XDL)] CREATED BY 2019-03-15</History>
|
public DataTable GetFixedData(MONGO_PRINT_BARCODE printBarCode)
|
{
|
string sql = @"SELECT D.CN_S_ITEM_CODE,D.CN_S_ITEM_NAME,D.CN_S_MODEL,D.CN_S_ARRIVAL_NO,D.CN_F_QUANTITY,M.CN_S_VENDOR_NAME,D.CN_C_IS_FIXED_STATE,D.CN_S_PUT_POSITION
|
FROM TN_WM_ARRIVAL_DTL D LEFT JOIN
|
TN_WM_ARRIVAL_MST M ON D.CN_S_MSTGUID=M.CN_GUID
|
LEFT JOIN TN_WM_INCREASE_INVENTORY_MST INMST ON INMST.CN_S_ARRIVAL_NO=M.CN_S_ARRIVAL_NO
|
WHERE INMST.CN_S_OP_NO='"+ printBarCode.CN_S_IN_ORDER + @"' AND D.CN_S_ITEM_CODE='" + printBarCode.CN_S_ITEM_CODE + @"'
|
AND M.CN_S_LOT_CODE='" + printBarCode.CN_S_LOT_NO + @"' AND D.CN_S_PRODUCTION_BATCH='" + printBarCode.CN_S_PRODUCTION_BATCH + @"'
|
AND D.CN_C_IS_FIXED_STATE!='Y' ";
|
return ExecuteDataTable(sql);
|
}
|
|
/// <summary>
|
/// 贴码确认
|
/// </summary>
|
/// <param name="printBarCode">打码实体</param>
|
/// <returns></returns>
|
/// <History>[HANHE(XDL)] CREATED BY 2019-03-15</History>
|
public OperateResult FixedConfirm(MONGO_PRINT_BARCODE printBarCode)
|
{
|
string sq1 = @" UPDATE TN_WM_ARRIVAL_DTL SET CN_C_IS_FIXED_STATE='Y'
|
FROM TN_WM_ARRIVAL_DTL WHERE CN_S_ARRIVAL_NO = (select CN_S_ARRIVAL_NO from TN_WM_INCREASE_INVENTORY_MST WHERE CN_S_OP_NO = @CN_S_OP_NO and CN_S_LOT_NO = @CN_S_LOT_NO) AND
|
CN_S_ITEM_CODE = @CN_S_ITEM_CODE and CN_S_ITEM_STATE = @CN_S_ITEM_STATE and CN_S_PRODUCTION_BATCH = @CN_S_PRODUCTION_BATCH";
|
OperateResult result = ExecuteTranSql(sq1, new
|
{
|
CN_S_OP_NO = printBarCode.CN_S_IN_ORDER,
|
CN_S_LOT_NO = printBarCode.CN_S_LOT_NO,
|
CN_S_ITEM_CODE = printBarCode.CN_S_ITEM_CODE,
|
CN_S_ITEM_STATE = printBarCode.CN_S_ITEM_STATE,
|
CN_S_PRODUCTION_BATCH = printBarCode.CN_S_PRODUCTION_BATCH
|
}, null);
|
|
return result;
|
}
|
|
/// <summary>
|
/// PDA接口-获取搬运信息
|
/// </summary>
|
/// <param name="printBarCode">打码实体</param>
|
/// <returns></returns>
|
/// <History>[HANHE(XDL)] CREATED BY 2019-03-15</History>
|
public DataTable GetTransData(MONGO_PRINT_BARCODE printBarCode)
|
{
|
string sql = @"SELECT D.CN_S_ITEM_CODE,D.CN_S_ITEM_NAME,D.CN_S_MODEL,D.CN_S_ARRIVAL_NO,D.CN_F_QUANTITY,M.CN_S_VENDOR_NAME,D.CN_C_IS_FIXED_STATE,D.CN_S_PUT_POSITION
|
FROM TN_WM_ARRIVAL_DTL D LEFT JOIN
|
TN_WM_ARRIVAL_MST M ON D.CN_S_MSTGUID=M.CN_GUID
|
LEFT JOIN TN_WM_INCREASE_INVENTORY_MST INMST ON INMST.CN_S_ARRIVAL_NO=M.CN_S_ARRIVAL_NO
|
WHERE INMST.CN_S_OP_NO='" + printBarCode.CN_S_IN_ORDER + @"' AND D.CN_S_ITEM_CODE='" + printBarCode.CN_S_ITEM_CODE + @"'
|
AND M.CN_S_LOT_CODE='" + printBarCode.CN_S_LOT_NO + @"' AND D.CN_S_PRODUCTION_BATCH='" + printBarCode.CN_S_PRODUCTION_BATCH + @"'
|
AND D.CN_C_IS_FIXED_STATE!='Y' ";
|
return ExecuteDataTable(sql);
|
}
|
#endregion
|
|
#region 获取入库单中的物料信息
|
/// <summary>
|
/// 获取托盘中物料的信息，按批次汇总
|
/// </summary>
|
/// <param name="trayCode">托盘号</param>
|
/// <returns></returns>
|
/// <History>[Hanhe(DBS)] CREATED BY 2018/12/8</History>
|
public DataTable GetIncreaseDetail(string CN_S_OP_NO)
|
{
|
string sql = @" select b.CN_S_ITEM_CODE,b.CN_N_ROW_NO,b.CN_S_ITEM_NAME,b.CN_S_MODEL,b.CN_S_ITEM_STATE,a.CN_S_OWNER,b.CN_S_MEASURE_UNIT,a.CN_S_LOT_NO,b.CN_T_PRODUCTION,b.CN_T_EXPIRATION,b.CN_S_SERIAL_NO,a.CN_S_VENDOR_NO
|
,a.CN_S_VENDOR_NAME, b.CN_F_QUANTITY,(b.CN_F_QUANTITY - b.CN_F_ADD_ONTRAY_QTY) as CAN_ENCODE_QTY,b.CN_S_PRODUCTION_BATCH ,b.CN_F_ADD_ONTRAY_QTY from TN_WM_INCREASE_INVENTORY_MST a join
|
TN_WM_INCREASE_INVENTORY_DTL b on a.CN_S_OP_NO=b.CN_S_OP_NO
|
where a.CN_S_OP_NO=@CN_S_OP_NO and b.CN_F_QUANTITY - b.CN_F_ADD_ONTRAY_QTY>0 ";
|
|
return ExecuteDataTable(sql, new { CN_S_OP_NO = CN_S_OP_NO });
|
}
|
#endregion
|
}
|
}
|
