using HanHe.Utility.Data;
|
using HH.Redis.ReisModel;
|
using HH.WMS.Entitys;
|
using HH.WMS.Entitys.Check;
|
using HH.WMS.Entitys.Common;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.Common;
|
using System.Data.OracleClient;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
|
namespace HH.WMS.DAL.Check
|
{
|
public class TN_WM_CHECK_LISTDAL : DapperBaseDAL
|
{
|
public DataTable GetGroupDT(string sqlwhere)
|
{
|
//CN_S_LOT_NO注释
|
string sql = "SELECT CN_S_OP_NO,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CN_S_TRAY_CODE,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,CN_S_OWNER,CN_S_MODEL,CN_S_FIGURE_NO,SUM(CN_F_QUANTITY) AS CN_F_QUANTITY,SUM(CN_F_CHECK_QTY) AS CN_F_CHECK_QTY,CN_S_MEASURE_UNIT,'' as CN_S_LOT_NO,SUM(CN_F_CHECK_QTY)-SUM(CN_F_QUANTITY) AS BREAKQUANTITY FROM dbo.tn_wm_check_list WHERE CN_S_ITEM_CODE<>'' {0} GROUP BY CN_S_OP_NO,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CN_S_TRAY_CODE,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,CN_S_OWNER,CN_S_MODEL,CN_S_FIGURE_NO,CN_S_MEASURE_UNIT";
|
sql = string.Format(sql, sqlwhere);
|
return ExecuteDataTable(sql, null);
|
}
|
|
/// <summary>
|
/// 获取列表
|
/// </summary>
|
/// <param name="obj"></param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/8/21</history>
|
public DataTable GetEntity(string sqlwhere)
|
{
|
string sql = "select ROW_NUMBER() OVER (ORDER BY CN_S_OP_NO) AS ROWS,* from TN_WM_CHECK_LIST where {0}";
|
sql = string.Format(sql, sqlwhere);
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
|
//获得列表
|
DataTable dt = DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
|
return dt;
|
}
|
|
|
/// <summary>
|
/// 获取列表
|
/// </summary>
|
/// <param name="obj"></param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/10/25</history>
|
public DataTable GetList(string sqlwhere)
|
{
|
string sql = "SELECT CN_S_OP_NO,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CN_S_TRAY_CODE,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,CN_S_OWNER,CN_S_LOT_NO,CN_S_MODEL,CN_S_FIGURE_NO,SUM(CN_F_QUANTITY) AS CN_F_QUANTITY,SUM(CN_F_CHECK_QTY) AS CN_F_CHECK_QTY,CN_S_MEASURE_UNIT FROM dbo.tn_wm_check_list where {0} GROUP BY CN_S_OP_NO,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CN_S_TRAY_CODE,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,CN_S_OWNER,CN_S_LOT_NO,CN_S_MODEL,CN_S_FIGURE_NO,CN_S_MEASURE_UNIT";
|
sql = string.Format(sql, sqlwhere);
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
|
//获得列表
|
DataTable dt = DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
|
return dt;
|
}
|
|
/// <summary>
|
/// 获取分页列表
|
/// </summary>
|
/// <param name="obj"></param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/7/3</history>
|
public DataTable GetDataTableGroup(string sqlWhere, string OrdeBy, int pageSize, int pageIndex)
|
{
|
string sql = @"(SELECT CN_S_STOCK_CODE,CN_S_OP_NO,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CAST(SUM(CN_F_QUANTITY) AS INT)AS CN_F_QUANTITY,
|
CAST(SUM(CN_F_CHECK_QTY) AS INT) AS CN_F_CHECK_QTY FROM dbo.tn_wm_check_list GROUP BY CN_S_OP_NO,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CN_S_STOCK_CODE
|
) as a";
|
DbCommand cmd = DataAccess.GetStoredProcCommand("prc_query");
|
this.DataAccess.AddInParameter(cmd, "TableName", ComDbType.CHAR, sql);
|
this.DataAccess.AddInParameter(cmd, "WhereStr", ComDbType.CHAR, sqlWhere);
|
this.DataAccess.AddInParameter(cmd, "OrderByStr", ComDbType.CHAR, OrdeBy);
|
this.DataAccess.AddInParameter(cmd, "PageSize", ComDbType.INT, pageSize);
|
this.DataAccess.AddInParameter(cmd, "PageIndex", ComDbType.INT, pageIndex);
|
this.DataAccess.AddOutParameter(cmd, "TotalPage", ComDbType.INT, 4);
|
this.DataAccess.AddOutParameter(cmd, "TotalRecord", ComDbType.INT, 4);
|
|
//如果是oracle 增加特性
|
if (!string.IsNullOrEmpty(Now_dbType) && Now_dbType.Equals("ORACLE"))
|
{
|
//处理游标类型 因DbType无游标类型,游标类型单独处理
|
ComDbType.AddOrcOutParameter(cmd, "v_cur", OracleType.Cursor);
|
}
|
try
|
{
|
//获得列表
|
DataTable dt = DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
|
//obj.PageCount = Convert.ToInt32(this.DataAccess.GetParameterValue(cmd, "TotalRecord"));
|
return dt;
|
}
|
catch (Exception)
|
{
|
|
throw;
|
}
|
}
|
|
#region 获取盘点结果查询
|
/// <summary>
|
/// 获取盘点结果查询
|
/// </summary>
|
/// <param name="sqlwhere">查询条件</param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/8/21</history>
|
public DataTable GetDataTable(string sqlwhere)
|
{
|
string sql = "select * ,(SELECT COUNT(CN_S_UNIQUE_CODE) FROM dbo.TN_WM_B_TRAY_ITEM_DTL WHERE CN_S_UNIQUE_CODE=CN_S_PACK_CODE) AS isexist from TN_WM_CHECK_LIST {0}";
|
sql = string.Format(sql, sqlwhere);
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
return DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
|
}
|
|
|
/// <summary>
|
/// 获取盘点结果查询
|
/// </summary>
|
/// <param name="sqlwhere">查询条件</param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/8/21</history>
|
public List<TN_WM_CHECK_LISTEntity> GetCheckList(string sqlwhere)
|
{
|
string sql = "select * ,(SELECT COUNT(CN_S_UNIQUE_CODE) FROM dbo.TN_WM_B_TRAY_ITEM_DTL WHERE CN_S_UNIQUE_CODE=CN_S_PACK_CODE) AS isexist from TN_WM_CHECK_LIST {0}";
|
sql = string.Format(sql, sqlwhere);
|
return ExecuteQuery<TN_WM_CHECK_LISTEntity>(sql, null);
|
}
|
|
|
/// <summary>
|
/// 根据盘点单位获取盘点明细，托盘号、货位号
|
/// </summary>
|
/// <returns></returns>
|
/// <history>[HanHe(DBS)] CREATED 2019/3/26</history>
|
public List<TN_WM_CHECK_LISTEntity> GetUnitCheckList(string sqlwhere)
|
{
|
string sql = @"SELECT
|
CN_S_STOCK_AREA
|
,CN_S_LOCATION_CODE
|
,CN_S_TRAY_CODE
|
,CN_S_TRAY_GRID
|
,CN_S_ITEM_CODE
|
,CN_S_ITEM_NAME
|
,CN_S_ITEM_STATE
|
,CN_S_MODEL
|
,CN_S_FIGURE_NO
|
,CN_S_MEASURE_UNIT
|
,CN_S_ISCHECK
|
,sum(CN_F_QUANTITY) CN_F_QUANTITY
|
,sum(CN_F_CHECK_QTY) CN_F_CHECK_QTY
|
FROM tn_wm_check_list " + sqlwhere + @"
|
group by CN_S_STOCK_AREA
|
,CN_S_LOCATION_CODE
|
,CN_S_TRAY_CODE
|
,CN_S_TRAY_GRID
|
,CN_S_ITEM_CODE
|
,CN_S_ITEM_NAME
|
,CN_S_ITEM_STATE
|
,CN_S_MODEL
|
,CN_S_FIGURE_NO
|
,CN_S_MEASURE_UNIT
|
,CN_S_ISCHECK";
|
return ExecuteQuery<TN_WM_CHECK_LISTEntity>(sql, null);
|
}
|
|
/// <summary>
|
/// 获取二次盘点盘点结果查询
|
/// </summary>
|
/// <param name="sqlwhere">查询条件</param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/8/21</history>
|
public List<TN_WM_CHECK_LISTEntity> GetChecklist(string sqlwhere)
|
{
|
string sql = @"SELECT CN_S_OP_NO,CN_S_DTL_ROW,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CN_S_TRAY_CODE,CN_S_TRAY_GRID,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,CN_S_OWNER,CN_S_LOT_NO,CN_S_MODEL,CN_S_FIGURE_NO,CN_F_QUANTITY,0.0000 AS CN_F_CHECK_QTY,CN_S_MEASURE_UNIT,CN_S_NOTE,CN_S_ISCHECK,CN_S_ISWIN,CN_S_OPERATOR,CN_T_OPERATE,CN_S_PACK_CODE FROM tn_wm_check_list {0}";
|
sql = string.Format(sql, sqlwhere);
|
return ExecuteQuery<TN_WM_CHECK_LISTEntity>(sql, null);
|
}
|
#endregion
|
|
/// <summary>
|
/// 批量新增
|
/// </summary>
|
/// <param name="entity">实体类</param>
|
/// <param name="trans">事务</param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/8/23</history>
|
public SqlExecuteResult Addlist(List<TN_WM_CHECK_LISTEntity> listentity, DbTransaction trans)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("insert into tn_wm_check_list(");
|
strSql.Append("CN_S_OP_NO,CN_S_DTL_ROW,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CN_S_TRAY_CODE,CN_S_TRAY_GRID,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,CN_S_OWNER,CN_S_LOT_NO,CN_S_MODEL,CN_S_FIGURE_NO,CN_F_QUANTITY,CN_F_CHECK_QTY,CN_S_MEASURE_UNIT,CN_S_NOTE,CN_S_ISCHECK,CN_S_ISWIN,CN_S_OPERATOR,CN_T_OPERATE,CN_S_PACK_CODE,CN_S_PACKING_UNIT,CN_F_PACKING_QTY,CN_S_PRODUCTION_BATCH,CN_S_CREATOR,CN_S_CREATOR_BY,CN_T_CREATE,CN_S_MODIFY,CN_S_MODIFY_BY,CN_T_MODIFY)");
|
strSql.Append(" values (");
|
strSql.Append(":CN_S_OP_NO,:CN_S_DTL_ROW,:CN_S_STOCK_CODE,:CN_S_STOCK_AREA,:CN_S_LOCATION_CODE,:CN_S_TRAY_CODE,:CN_S_TRAY_GRID,:CN_S_ITEM_CODE,:CN_S_ITEM_NAME,:CN_S_ITEM_STATE,:CN_S_OWNER,:CN_S_LOT_NO,:CN_S_MODEL,:CN_S_FIGURE_NO,:CN_F_QUANTITY,:CN_F_CHECK_QTY,:CN_S_MEASURE_UNIT,:CN_S_NOTE,:CN_S_ISCHECK,:CN_S_ISWIN,:CN_S_OPERATOR,:CN_T_OPERATE,:CN_S_PACK_CODE,:CN_S_PACKING_UNIT,:CN_F_PACKING_QTY,:CN_S_PRODUCTION_BATCH,:CN_S_CREATOR,:CN_S_CREATOR_BY,:CN_T_CREATE,:CN_S_MODIFY,:CN_S_MODIFY_BY,:CN_T_MODIFY)");
|
List<DbCommand> listcmd = new List<DbCommand>();
|
int i = 1;
|
foreach (TN_WM_CHECK_LISTEntity entity in listentity)
|
{
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
DataAccess.AddInParameter(cmd, "CN_S_OP_NO", ComDbType.STRING, entity.CN_S_OP_NO);
|
DataAccess.AddInParameter(cmd, "CN_S_DTL_ROW", ComDbType.INT32, i++);
|
DataAccess.AddInParameter(cmd, "CN_S_STOCK_CODE", ComDbType.STRING, entity.CN_S_STOCK_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_STOCK_AREA", ComDbType.STRING, entity.CN_S_STOCK_AREA);
|
DataAccess.AddInParameter(cmd, "CN_S_LOCATION_CODE", ComDbType.STRING, entity.CN_S_LOCATION_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_TRAY_CODE", ComDbType.STRING, entity.CN_S_TRAY_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_TRAY_GRID", ComDbType.STRING, entity.CN_S_TRAY_GRID);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_CODE", ComDbType.STRING, entity.CN_S_ITEM_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_NAME", ComDbType.STRING, entity.CN_S_ITEM_NAME);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_STATE", ComDbType.STRING, entity.CN_S_ITEM_STATE);
|
DataAccess.AddInParameter(cmd, "CN_S_OWNER", ComDbType.STRING, entity.CN_S_OWNER);
|
DataAccess.AddInParameter(cmd, "CN_S_LOT_NO", ComDbType.STRING, entity.CN_S_LOT_NO);
|
DataAccess.AddInParameter(cmd, "CN_S_MODEL", ComDbType.STRING, entity.CN_S_MODEL);
|
DataAccess.AddInParameter(cmd, "CN_S_FIGURE_NO", ComDbType.STRING, entity.CN_S_FIGURE_NO);
|
DataAccess.AddInParameter(cmd, "CN_F_QUANTITY", ComDbType.DECIMAL, entity.CN_F_QUANTITY);
|
DataAccess.AddInParameter(cmd, "CN_F_CHECK_QTY", ComDbType.DECIMAL, entity.CN_F_CHECK_QTY);
|
DataAccess.AddInParameter(cmd, "CN_S_MEASURE_UNIT", ComDbType.STRING, entity.CN_S_MEASURE_UNIT);
|
DataAccess.AddInParameter(cmd, "CN_S_NOTE", ComDbType.STRING, entity.CN_S_NOTE);
|
DataAccess.AddInParameter(cmd, "CN_S_ISCHECK", ComDbType.CHAR, entity.CN_S_ISCHECK);
|
DataAccess.AddInParameter(cmd, "CN_S_ISWIN", ComDbType.CHAR, entity.CN_S_ISWIN);
|
DataAccess.AddInParameter(cmd, "CN_S_OPERATOR", ComDbType.STRING, entity.CN_S_OPERATOR);
|
DataAccess.AddInParameter(cmd, "CN_T_OPERATE", ComDbType.DATE, entity.CN_T_OPERATE);
|
DataAccess.AddInParameter(cmd, "CN_S_PACK_CODE", ComDbType.STRING, entity.CN_S_PACK_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_PACKING_UNIT", ComDbType.STRING, entity.CN_S_PACKING_UNIT);
|
DataAccess.AddInParameter(cmd, "CN_F_PACKING_QTY", ComDbType.DECIMAL, entity.CN_F_PACKING_QTY);
|
DataAccess.AddInParameter(cmd, "CN_S_PRODUCTION_BATCH", ComDbType.STRING, entity.CN_S_PRODUCTION_BATCH);
|
DataAccess.AddInParameter(cmd, "CN_S_CREATOR", ComDbType.STRING, entity.CN_S_CREATOR);
|
DataAccess.AddInParameter(cmd, "CN_S_CREATOR_BY", ComDbType.STRING, entity.CN_S_CREATOR_BY);
|
DataAccess.AddInParameter(cmd, "CN_T_CREATE", ComDbType.DATE, entity.CN_T_CREATE);
|
DataAccess.AddInParameter(cmd, "CN_S_MODIFY", ComDbType.STRING, entity.CN_S_MODIFY);
|
DataAccess.AddInParameter(cmd, "CN_S_MODIFY_BY", ComDbType.STRING, entity.CN_S_MODIFY_BY);
|
DataAccess.AddInParameter(cmd, "CN_T_MODIFY", ComDbType.DATE, entity.CN_T_MODIFY);
|
listcmd.Add(cmd);
|
}
|
|
|
return ExecuteCommands(listcmd, trans);
|
}
|
|
|
/// <summary>
|
/// 批量新增
|
/// </summary>
|
/// <param name="entity">实体类</param>
|
/// <param name="trans">事务</param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/8/23</history>
|
public SqlExecuteResult Addlist(List<TN_WM_CHECK_LISTEntity> listentity, string CN_S_OPERATOR, DateTime CN_T_OPERATE, DbTransaction trans)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("insert into tn_wm_check_list(");
|
strSql.Append("CN_S_OP_NO,CN_S_DTL_ROW,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CN_S_TRAY_CODE,CN_S_TRAY_GRID,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,CN_S_OWNER,CN_S_LOT_NO,CN_S_MODEL,CN_S_FIGURE_NO,CN_F_QUANTITY,CN_F_CHECK_QTY,CN_S_MEASURE_UNIT,CN_S_NOTE,CN_S_ISCHECK,CN_S_ISWIN,CN_S_OPERATOR,CN_T_OPERATE,CN_S_PACK_CODE)");
|
strSql.Append(" values (");
|
strSql.Append(":CN_S_OP_NO,:CN_S_DTL_ROW,:CN_S_STOCK_CODE,:CN_S_STOCK_AREA,:CN_S_LOCATION_CODE,:CN_S_TRAY_CODE,:CN_S_TRAY_GRID,:CN_S_ITEM_CODE,:CN_S_ITEM_NAME,:CN_S_ITEM_STATE,:CN_S_OWNER,:CN_S_LOT_NO,:CN_S_MODEL,:CN_S_FIGURE_NO,:CN_F_QUANTITY,:CN_F_CHECK_QTY,:CN_S_MEASURE_UNIT,:CN_S_NOTE,:CN_S_ISCHECK,:CN_S_ISWIN,:CN_S_OPERATOR,:CN_T_OPERATE,:CN_S_PACK_CODE)");
|
List<DbCommand> listcmd = new List<DbCommand>();
|
|
foreach (TN_WM_CHECK_LISTEntity entity in listentity)
|
{
|
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
DataAccess.AddInParameter(cmd, "CN_S_OP_NO", ComDbType.STRING, entity.CN_S_OP_NO);
|
DataAccess.AddInParameter(cmd, "CN_S_DTL_ROW", ComDbType.INT32, entity.CN_S_DTL_ROW);
|
DataAccess.AddInParameter(cmd, "CN_S_STOCK_CODE", ComDbType.STRING, entity.CN_S_STOCK_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_STOCK_AREA", ComDbType.STRING, entity.CN_S_STOCK_AREA);
|
DataAccess.AddInParameter(cmd, "CN_S_LOCATION_CODE", ComDbType.STRING, entity.CN_S_LOCATION_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_TRAY_CODE", ComDbType.STRING, entity.CN_S_TRAY_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_TRAY_GRID", ComDbType.STRING, entity.CN_S_TRAY_GRID);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_CODE", ComDbType.STRING, entity.CN_S_ITEM_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_NAME", ComDbType.STRING, entity.CN_S_ITEM_NAME);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_STATE", ComDbType.STRING, entity.CN_S_ITEM_STATE);
|
DataAccess.AddInParameter(cmd, "CN_S_OWNER", ComDbType.STRING, entity.CN_S_OWNER);
|
DataAccess.AddInParameter(cmd, "CN_S_LOT_NO", ComDbType.STRING, entity.CN_S_LOT_NO);
|
DataAccess.AddInParameter(cmd, "CN_S_MODEL", ComDbType.STRING, entity.CN_S_MODEL);
|
DataAccess.AddInParameter(cmd, "CN_S_FIGURE_NO", ComDbType.STRING, entity.CN_S_FIGURE_NO);
|
DataAccess.AddInParameter(cmd, "CN_F_QUANTITY", ComDbType.DECIMAL, entity.CN_F_QUANTITY);
|
DataAccess.AddInParameter(cmd, "CN_F_CHECK_QTY", ComDbType.DECIMAL, entity.CN_F_CHECK_QTY);
|
DataAccess.AddInParameter(cmd, "CN_S_MEASURE_UNIT", ComDbType.STRING, entity.CN_S_MEASURE_UNIT);
|
DataAccess.AddInParameter(cmd, "CN_S_NOTE", ComDbType.STRING, entity.CN_S_NOTE);
|
DataAccess.AddInParameter(cmd, "CN_S_ISCHECK", ComDbType.CHAR, entity.CN_S_ISCHECK);
|
DataAccess.AddInParameter(cmd, "CN_S_ISWIN", ComDbType.CHAR, entity.CN_S_ISWIN);
|
DataAccess.AddInParameter(cmd, "CN_S_OPERATOR", ComDbType.STRING, CN_S_OPERATOR);
|
DataAccess.AddInParameter(cmd, "CN_T_OPERATE", ComDbType.DATE, CN_T_OPERATE);
|
DataAccess.AddInParameter(cmd, "CN_S_PACK_CODE", ComDbType.STRING, entity.CN_S_PACK_CODE);
|
listcmd.Add(cmd);
|
}
|
|
|
return ExecuteCommands(listcmd, trans);
|
}
|
|
/// <summary>
|
/// 更新
|
/// </summary>
|
/// <param name="entity">实体类</param>
|
/// <param name="trans">事务</param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/8/23</history>
|
public SqlExecuteResult UpdateQTY(List<TN_WM_CHECK_LISTEntity> entitylist, DbTransaction trans)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("update tn_wm_check_list set "); ;
|
strSql.Append("CN_F_CHECK_QTY=:CN_F_CHECK_QTY,");
|
strSql.Append("CN_S_ISCHECK=:CN_S_ISCHECK,");
|
strSql.Append("CN_S_ISWIN=:CN_S_ISWIN,");
|
|
strSql.Append("CN_S_MODIFY=:CN_S_MODIFY,");
|
strSql.Append("CN_S_MODIFY_BY=:CN_S_MODIFY_BY,");
|
strSql.Append("CN_T_MODIFY=:CN_T_MODIFY");
|
strSql.Append(" where CN_S_OP_NO=:CN_S_OP_NO AND CN_S_TRAY_CODE=:CN_S_TRAY_CODE AND CN_S_PACK_CODE=:CN_S_PACK_CODE AND CN_S_ITEM_CODE=:CN_S_ITEM_CODE");
|
List<DbCommand> cmdlist = new List<DbCommand>();
|
foreach (TN_WM_CHECK_LISTEntity entity in entitylist)
|
{
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
DataAccess.AddInParameter(cmd, "CN_S_OP_NO", ComDbType.STRING, entity.CN_S_OP_NO);
|
DataAccess.AddInParameter(cmd, "CN_S_TRAY_CODE", ComDbType.STRING, entity.CN_S_TRAY_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_PACK_CODE", ComDbType.STRING, entity.CN_S_PACK_CODE);
|
DataAccess.AddInParameter(cmd, "CN_F_CHECK_QTY", ComDbType.DECIMAL, entity.CN_F_CHECK_QTY);
|
DataAccess.AddInParameter(cmd, "CN_S_ISCHECK", ComDbType.CHAR, "Y");
|
DataAccess.AddInParameter(cmd, "CN_S_ISWIN", ComDbType.CHAR, entity.CN_F_QUANTITY < entity.CN_F_CHECK_QTY ? "Y" : "N");
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_CODE", ComDbType.STRING, entity.CN_S_ITEM_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_MODIFY", ComDbType.STRING, entity.CN_S_MODIFY);
|
DataAccess.AddInParameter(cmd, "CN_S_MODIFY_BY", ComDbType.STRING, entity.CN_S_MODIFY_BY);
|
DataAccess.AddInParameter(cmd, "CN_T_MODIFY", ComDbType.DATE, entity.CN_T_MODIFY);
|
cmdlist.Add(cmd);
|
}
|
return ExecuteCommands(cmdlist, trans);
|
}
|
|
|
|
/// <summary>
|
/// 根据盘点单托盘编码更新
|
/// </summary>
|
/// <param name="entity">实体类</param>
|
/// <param name="trans">事务</param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/8/23</history>
|
public SqlExecuteResult UpdateByCode(string CN_S_OP_NO, string CN_S_TRAY_CODE, DbTransaction trans)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("update tn_wm_check_list set "); ;
|
strSql.Append("CN_S_ISCHECK=:CN_S_ISCHECK");
|
strSql.Append(" where CN_S_OP_NO=:CN_S_OP_NO AND CN_S_TRAY_CODE=:CN_S_TRAY_CODE");
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
DataAccess.AddInParameter(cmd, "CN_S_OP_NO", ComDbType.STRING, CN_S_OP_NO);
|
DataAccess.AddInParameter(cmd, "CN_S_TRAY_CODE", ComDbType.STRING, CN_S_TRAY_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_ISCHECK", ComDbType.CHAR, "Y");
|
return ExecuteCommand(cmd, trans);
|
}
|
|
|
/// <summary>
|
/// 更新（二次盘点）
|
/// </summary>
|
/// <param name="entity">实体类</param>
|
/// <param name="trans">事务</param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/8/23</history>
|
public SqlExecuteResult UpdateByTrayCode(string CN_S_OP_NO, string CN_S_TRAY_CODE, DbTransaction trans)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("update tn_wm_check_list set "); ;
|
strSql.Append("CN_F_CHECK_QTY=0,");
|
strSql.Append("CN_S_ISCHECK='N',");
|
strSql.Append("CN_S_ISWIN=''");
|
strSql.Append(" where CN_S_OP_NO='" + CN_S_OP_NO + "' AND CN_S_TRAY_CODE in ('" + CN_S_TRAY_CODE + "')");
|
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
|
return ExecuteCommand(cmd, trans);
|
}
|
|
|
#region 根据盘点查询托盘与物料表
|
/// <summary>
|
/// 根据盘点查询托盘与物料表
|
/// </summary>
|
/// <param name="sqlwhere"></param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/8/24</history>
|
public List<TN_WM_CHECK_LISTEntity> GetTrayitemByOpno(string sqlwhere, string type)
|
{
|
string sql = "";
|
if (type == "货位盘点" || type == "自定义盘点")
|
{
|
sql = @"SELECT a.CN_S_OP_NO,a.CN_N_ROW_NO AS CN_S_DTL_ROW,d.CN_S_STOCK_CODE,a.CN_S_STOCK_AREA,a.CN_S_LOCATION_CODE,ISNULL(c.CN_S_TRAY_CODE,b.CN_S_TRAY_CODE) AS CN_S_TRAY_CODE,c.CN_S_TRAY_GRID,c.CN_S_ITEM_CODE,c.CN_S_ITEM_NAME,c.CN_S_ITEM_STATE,c.CN_S_OWNER,'' AS CN_S_LOT_NO,c.CN_S_MODEL,c.CN_S_FIGURE_NO,c.CN_F_QUANTITY,0 AS CN_F_CHECK_QTY,a.CN_S_MEASURE_UNIT,D.CN_S_NOTE,'N' AS CN_S_ISCHECK,'' AS CN_S_ISWIN,d.CN_S_CREATOR AS CN_S_OPERATOR,GETDATE() AS CN_T_OPERATE,'' AS CN_S_PACK_CODE,'' AS CN_S_PACKING_UNIT,0 AS CN_F_PACKING_QTY,'' AS CN_S_PRODUCTION_BATCH FROM dbo.tn_wm_check_dtl AS a
|
LEFT JOIN dbo.tn_wm_b_tray_location AS b ON a.CN_S_LOCATION_CODE=b.CN_S_LOCATION_CODE
|
--码盘汇总开始
|
INNER JOIN dbo.TN_WM_B_TRAY_ITEM_MST AS c ON
|
ISNULL(a.CN_S_ITEM_CODE,'')=ISNULL(c.CN_S_ITEM_CODE,'') AND ISNULL(c.CN_S_ITEM_NAME,'') = ISNULL(a.CN_S_ITEM_NAME,'') AND ISNULL(a.CN_S_ITEM_STATE,'')=ISNULL(c.CN_S_ITEM_STATE,'') AND ISNULL(a.CN_S_OWNER,'')=ISNULL(c.CN_S_OWNER,'') AND ISNULL(a.CN_S_MODEL,'') = ISNULL(c.CN_S_MODEL,'') AND ISNULL(a.CN_S_FIGURE_NO,'')=ISNULL(c.CN_S_FIGURE_NO,'') AND ISNULL(c.CN_S_MEASURE_UNIT,'') = ISNULL(a.CN_S_MEASURE_UNIT,'')
|
AND ISNULL(c.CN_S_TRAY_CODE,'')=ISNULL(b.CN_S_TRAY_CODE,'')
|
INNER JOIN dbo.tn_wm_check_mst AS d ON a.CN_S_OP_NO=d.CN_S_OP_NO {0}
|
UNION ALL --空货位
|
|
SELECT a.CN_S_OP_NO,a.CN_N_ROW_NO AS CN_S_DTL_ROW,b.CN_S_STOCK_CODE,a.CN_S_STOCK_AREA,a.CN_S_LOCATION_CODE,ISNULL(d.CN_S_TRAY_CODE,'') AS CN_S_TRAY_CODE,'' as CN_S_TRAY_GRID,'' AS CN_S_ITEM_CODE,'' AS CN_S_ITEM_NAME,'' AS CN_S_ITEM_STATE,'' AS CN_S_OWNER,'' AS CN_S_LOT_NO,'' AS CN_S_MODEL,'' AS CN_S_FIGURE_NO,0 AS CN_F_QUANTITY,0 AS CN_F_CHECK_QTY,a.CN_S_MEASURE_UNIT,b.CN_S_NOTE,'N' AS CN_S_ISCHECK,'' AS CN_S_ISWIN,b.CN_S_CREATOR AS CN_S_OPERATOR,GETDATE() AS CN_T_OPERATE,'' AS CN_S_PACK_CODE,'' AS CN_S_PACKING_UNIT,0 AS CN_F_PACKING_QTY,'' AS CN_S_PRODUCTION_BATCH FROM dbo.tn_wm_check_dtl AS a
|
INNER JOIN dbo.tn_wm_check_mst AS b ON a.CN_S_OP_NO=b.CN_S_OP_NO
|
LEFT JOIN dbo.tn_wm_b_location_ext AS c ON c.CN_S_LOCATION_CODE = a.CN_S_LOCATION_CODE
|
LEFT JOIN dbo.tn_wm_b_tray_location AS d ON d.CN_S_LOCATION_CODE=a.CN_S_LOCATION_CODE {0} AND a.CN_S_ITEM_CODE IS NULL";
|
|
}
|
else
|
{
|
sql = @"SELECT a.CN_S_OP_NO,a.CN_N_ROW_NO AS CN_S_DTL_ROW,d.CN_S_STOCK_CODE,a.CN_S_STOCK_AREA,b.CN_S_LOCATION_CODE,ISNULL(c.CN_S_TRAY_CODE,b.CN_S_TRAY_CODE) AS CN_S_TRAY_CODE,c.CN_S_TRAY_GRID,c.CN_S_ITEM_CODE,c.CN_S_ITEM_NAME,c.CN_S_ITEM_STATE,c.CN_S_OWNER,'' AS CN_S_LOT_NO,c.CN_S_MODEL,c.CN_S_FIGURE_NO,c.CN_F_QUANTITY,0 AS CN_F_CHECK_QTY,a.CN_S_MEASURE_UNIT,D.CN_S_NOTE,'N' AS CN_S_ISCHECK,'' AS CN_S_ISWIN,d.CN_S_CREATOR AS CN_S_OPERATOR,GETDATE() AS CN_T_OPERATE,'' AS CN_S_PACK_CODE,'' AS CN_S_PACKING_UNIT,0 AS CN_F_PACKING_QTY,'' AS CN_S_PRODUCTION_BATCH FROM dbo.tn_wm_check_dtl AS a
|
LEFT JOIN dbo.TN_WM_B_TRAY_ITEM_MST AS c ON
|
ISNULL(a.CN_S_ITEM_CODE,'')=ISNULL(c.CN_S_ITEM_CODE,'') AND ISNULL(c.CN_S_ITEM_NAME,'') = ISNULL(a.CN_S_ITEM_NAME,'') AND ISNULL(a.CN_S_ITEM_STATE,'')=ISNULL(c.CN_S_ITEM_STATE,'') AND ISNULL(a.CN_S_OWNER,'')=ISNULL(c.CN_S_OWNER,'') AND ISNULL(a.CN_S_MODEL,'') = ISNULL(c.CN_S_MODEL,'') AND ISNULL(a.CN_S_FIGURE_NO,'')=ISNULL(c.CN_S_FIGURE_NO,'') AND ISNULL(c.CN_S_MEASURE_UNIT,'') = ISNULL(a.CN_S_MEASURE_UNIT,'')
|
INNER JOIN dbo.tn_wm_b_tray_location AS b ON b.CN_S_TRAY_CODE=c.CN_S_TRAY_CODE
|
INNER JOIN dbo.tn_wm_check_mst AS d ON a.CN_S_OP_NO=d.CN_S_OP_NO {0} ";
|
|
//UNION ALL --空托盘
|
//SELECT a.CN_S_OP_NO,a.CN_N_ROW_NO AS CN_S_DTL_ROW,d.CN_S_STOCK_CODE,a.CN_S_STOCK_AREA,b.CN_S_LOCATION_CODE,b.CN_S_TRAY_CODE,'' as CN_S_TRAY_GRID,'' AS CN_S_ITEM_CODE,'' AS CN_S_ITEM_NAME,'' AS CN_S_ITEM_STATE,'' AS CN_S_OWNER,'' AS CN_S_LOT_NO,'' AS CN_S_MODEL,'' AS CN_S_FIGURE_NO,0 AS CN_F_QUANTITY,0 AS CN_F_CHECK_QTY,a.CN_S_MEASURE_UNIT,D.CN_S_NOTE,'N' AS CN_S_ISCHECK,'' AS CN_S_ISWIN,d.CN_S_CREATOR AS CN_S_OPERATOR,GETDATE() AS CN_T_OPERATE,'' AS CN_S_PACK_CODE,'' AS CN_S_PACKING_UNIT,0 AS CN_F_PACKING_QTY,'' AS CN_S_PRODUCTION_BATCH FROM dbo.tn_wm_check_dtl AS a
|
//LEFT JOIN (SELECT *,'' AS CN_S_ITEM_CODE FROM tn_wm_b_tray_location WHERE CN_S_TRAY_CODE NOT IN(SELECT CN_S_TRAY_CODE FROM TN_WM_B_TRAY_ITEM_MST)) AS b ON b.CN_S_ITEM_CODE=a.CN_S_ITEM_CODE AND a.CN_S_STOCK_AREA=b.CN_S_STOCK_AREA
|
//INNER JOIN dbo.tn_wm_check_mst AS d ON a.CN_S_OP_NO=d.CN_S_OP_NO {0}";
|
}
|
sql = string.Format(sql, sqlwhere);
|
return ExecuteQuery<TN_WM_CHECK_LISTEntity>(sql, null);
|
}
|
#endregion
|
|
#region 根据托盘编码获取盘点托盘信息
|
/// <summary>
|
/// 根据托盘编码获取盘点托盘信息
|
/// </summary>
|
/// <param name="sqlwhere">查询条件</param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/8/27</history>
|
public DataTable GetTrayCheckByCode(string sqlwhere)
|
{
|
string sql = @"SELECT b.CN_S_STOCK_CODE,b.CN_S_STOCK_AREA,b.CN_S_OP_NO,b.CN_S_TRAY_CODE,ISNULL(a.CN_S_TRAY_GRID,'') AS CN_S_TRAY_GRID,ISNULL(a.CN_S_PACKING_CODE,'') AS CN_S_PACKING_CODE,ISNULL(a.CN_S_ITEM_CODE,'') AS CN_S_ITEM_CODE,ISNULL(a.CN_S_ITEM_NAME,'') AS CN_S_ITEM_NAME,ISNULL(a.CN_S_ITEM_STATE,'') AS CN_S_ITEM_STATE,CAST(b.CN_F_QUANTITY AS INT) AS CN_F_QUANTITY,ISNULL(CAST(a.CN_F_PACKING_QTY AS INT),0) AS CN_F_PACKING_QTY,b.CN_S_LOCATION_CODE,ISNULL(CAST(d.CN_F_QUANTITY AS INT),0) AS CN_F_QUANTITY_DTL,ISNULL(c.CN_N_ROW_NUM,0) AS CN_N_ROW_NUM,ISNULL(c.CN_N_COLUMN_NUM,0) AS CN_N_COLUMN_NUM,b.CN_S_LOT_NO,b.CN_S_OWNER FROM tn_wm_check_list AS b
|
LEFT JOIN .dbo.tn_wm_b_tray_item_rel AS a ON a.CN_S_TRAY_CODE =b.CN_S_TRAY_CODE AND b.CN_S_TRAY_GRID = a.CN_S_TRAY_GRID AND a.CN_S_PACKING_CODE=b.CN_S_PACK_CODE
|
LEFT JOIN dbo.tn_wm_tray_info AS c ON a.CN_S_TRAY_CODE=c.CN_S_TRAY_CODE
|
LEFT JOIN (SELECT CN_S_OP_NO,CN_S_TRAY_CODE,SUM(CN_F_QUANTITY) AS CN_F_QUANTITY,CN_S_ITEM_CODE FROM tn_wm_check_list GROUP BY CN_S_OP_NO,CN_S_TRAY_CODE,CN_S_ITEM_CODE) AS d ON b.CN_S_OP_NO=d.CN_S_OP_NO AND d.CN_S_TRAY_CODE = b.CN_S_TRAY_CODE AND d.CN_S_ITEM_CODE = b.CN_S_ITEM_CODE {0}";
|
sql = string.Format(sql, sqlwhere);
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
return DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
|
}
|
#endregion
|
|
#region 根据托盘编码获取盘点托盘信息
|
/// <summary>
|
/// 根据托盘编码获取盘点托盘信息
|
/// </summary>
|
/// <param name="sqlwhere">查询条件</param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/8/27</history>
|
public DataTable GetTrayItemByCode(string sqlwhere)
|
{
|
string sql = @"SELECT a.CN_S_TRAY_CODE,a.CN_S_TRAY_GRID,CN_F_PACKING_QTY,b.CN_S_OP_NO,ISNULL(b.CN_F_QUANTITY,0) AS CN_F_QUANTITY FROM (SELECT CN_S_TRAY_CODE,CN_S_TRAY_GRID,CAST(SUM(CN_F_PACKING_QTY) AS INT) AS CN_F_PACKING_QTY FROM dbo.tn_wm_b_tray_item_rel GROUP BY CN_S_TRAY_CODE,CN_S_TRAY_GRID) AS a LEFT JOIN (SELECT CN_S_TRAY_CODE,CN_S_TRAY_GRID,CAST(SUM(CN_F_QUANTITY) AS INT) AS CN_F_QUANTITY,CN_S_OP_NO FROM dbo.tn_wm_check_list GROUP BY CN_S_TRAY_CODE,CN_S_TRAY_GRID,CN_S_OP_NO) AS b ON a.CN_S_TRAY_CODE=b.CN_S_TRAY_CODE AND b.CN_S_TRAY_GRID = a.CN_S_TRAY_GRID {0}";
|
sql = string.Format(sql, sqlwhere);
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
return DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
|
}
|
#endregion
|
|
#region 根据货位获取托盘信息
|
/// <summary>
|
/// 根据托盘编码获取盘点托盘信息
|
/// </summary>
|
/// <param name="sqlwhere">查询条件</param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/8/27</history>
|
public DataTable GetTrayByLocationCode(string sqlwhere)
|
{
|
string sql = @"SELECT CN_S_OP_NO,CN_S_LOCATION_CODE,a.CN_S_TRAY_CODE,b.CN_N_ROW_NUM,b.CN_N_COLUMN_NUM,b.CN_S_TRAY_TYPE FROM (SELECT CN_S_OP_NO,CN_S_LOCATION_CODE,CN_S_TRAY_CODE FROM dbo.tn_wm_check_list GROUP BY CN_S_OP_NO,CN_S_LOCATION_CODE,CN_S_TRAY_CODE) AS a INNER JOIN dbo.tn_wm_tray_info AS b ON a.CN_S_TRAY_CODE=b.CN_S_TRAY_CODE {0} ";
|
sql = string.Format(sql, sqlwhere);
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
return DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
|
}
|
#endregion
|
|
#region 盘点单最大行号
|
/// <summary>
|
/// 盘点单最大行号
|
/// </summary>
|
/// <param name="sqlwhere"></param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/7/3</history>
|
public int GetMaxRow(string sqlwhere)
|
{
|
string sql = "SELECT ISNULL(MAX(CN_S_DTL_ROW),0) AS CN_S_DTL_ROW FROM dbo.tn_wm_check_list {0} GROUP BY CN_S_OP_NO";
|
sql = string.Format(sql, sqlwhere);
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
DataTable dt = DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
|
return Convert.ToInt32(dt.Rows[0]["CN_S_DTL_ROW"]);
|
}
|
#endregion
|
|
#region 是否存在已经执行数据
|
/// <summary>
|
/// 是否存在已经执行数据
|
/// </summary>
|
/// <param name="sqlwhere"></param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/7/3</history>
|
public DataTable GetExist(string sqlwhere)
|
{
|
string sql = "SELECT * FROM dbo.tn_wm_check_mst WHERE CN_S_STATE='执行' {0}";
|
sql = string.Format(sql, sqlwhere);
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
DataTable dt = DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
|
return dt;
|
}
|
#endregion
|
|
#region 快速盘点
|
#region 根据货位获取托盘中物料
|
/// <summary>
|
/// 根据货位获取托盘中物料
|
/// </summary>
|
/// <param name="sqlwhere">查询条件</param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2019/1/2</history>
|
public DataTable GetTrayitemBylocation(string locationCode)
|
{
|
string sql = @"SELECT a.CN_S_STOCK_CODE,a.CN_S_AREA_CODE,a.CN_S_LOCATION_CODE,b.CN_S_TRAY_CODE,c.CN_S_TRAY_GRID,c.CN_S_ITEM_CODE,c.CN_S_ITEM_NAME,c.CN_S_ITEM_STATE,c.CN_S_OWNER,c.CN_S_MODEL,c.CN_S_FIGURE_NO,c.CN_S_MEASURE_UNIT,c.CN_F_QUANTITY,CAST(d.CN_F_PACKING_QTY AS INT) AS CN_F_BOXPACKING_QTY,d.CN_S_PACKING_UNIT AS CN_F_BOXPACKING_UNIT
|
FROM dbo.tn_wm_b_location_ext AS a
|
LEFT JOIN dbo.tn_wm_b_tray_location AS b ON a.CN_S_LOCATION_CODE=b.CN_S_LOCATION_CODE
|
LEFT JOIN dbo.TN_WM_B_TRAY_ITEM_MST AS c ON b.CN_S_TRAY_CODE=c.CN_S_TRAY_CODE
|
LEFT JOIN (SELECT TOP 1 CN_S_ITEM_CODE,CN_F_PACKING_QTY,CN_S_PACKING_UNIT FROM dbo.TN_WM_B_UNIQUE_BARCODE WHERE CN_S_PARENT_CODE='' GROUP BY CN_S_ITEM_CODE,CN_F_PACKING_QTY,CN_S_PACKING_UNIT) AS d ON c.CN_S_ITEM_CODE=d.CN_S_ITEM_CODE
|
where a.CN_S_LOCATION_CODE=@CN_S_LOCATION_CODE";
|
|
return ExecuteDataTable(sql, new { CN_S_LOCATION_CODE = locationCode });
|
}
|
#endregion
|
|
#endregion
|
|
#region 获取子表明细表最大行号
|
/// <summary>
|
/// 获取子表明细表最大行号
|
/// </summary>
|
/// <param name="sqlwhere">查询条件</param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2019/1/17</history>
|
public List<TN_WM_CHECK_LISTEntity> GetMaxCheckRow()
|
{
|
string sql = @"SELECT MAX(b.CN_N_ROW_NO) AS CN_S_DTL_ROW,a.CN_S_OP_NO FROM dbo.tn_wm_check_mst AS a INNER JOIN
|
dbo.tn_wm_check_dtl AS b ON a.CN_S_OP_NO=b.CN_S_OP_NO WHERE a.CN_S_STATE='执行' AND a.CN_S_TYPE='快速盘点' GROUP BY a.CN_S_OP_NO
|
UNION ALL
|
SELECT MAX(b.CN_S_DTL_ROW) AS CN_S_DTL_ROW,a.CN_S_OP_NO FROM dbo.tn_wm_check_mst AS a INNER JOIN
|
dbo.tn_wm_check_list AS b ON a.CN_S_OP_NO=b.CN_S_OP_NO WHERE a.CN_S_STATE='执行' AND a.CN_S_TYPE='快速盘点' GROUP BY a.CN_S_OP_NO";
|
return ExecuteQuery<TN_WM_CHECK_LISTEntity>(sql, null);
|
}
|
#endregion
|
|
|
/// <summary>
|
/// 批量新增
|
/// </summary>
|
/// <param name="entity">实体类</param>
|
/// <param name="trans">事务</param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/8/23</history>
|
public OperateResult AddCheckLists(List<TN_WM_CHECK_LISTEntity> list, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
foreach (TN_WM_CHECK_LISTEntity entity in list)
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append(" IF EXISTS(SELECT * FROM dbo.TN_WM_CHECK_LIST " + entity.sqlWhere + ") ");
|
strSql.Append("UPDATE TN_WM_CHECK_LIST SET CN_F_CHECK_QTY=@CN_F_CHECK_QTY,CN_S_ISCHECK=@CN_S_ISCHECK,CN_S_ISWIN=@CN_S_ISWIN " + entity.sqlWhere + "");
|
strSql.Append(" else ");
|
strSql.Append(" insert into tn_wm_check_list(");
|
strSql.Append("CN_S_OP_NO,CN_S_DTL_ROW,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CN_S_TRAY_CODE,CN_S_TRAY_GRID,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,CN_S_OWNER,CN_S_LOT_NO,CN_S_MODEL,CN_S_FIGURE_NO,CN_F_QUANTITY,CN_F_CHECK_QTY,CN_S_MEASURE_UNIT,CN_S_NOTE,CN_S_ISCHECK,CN_S_ISWIN,CN_S_OPERATOR,CN_T_OPERATE,CN_S_PACK_CODE,CN_S_PACKING_UNIT,CN_F_PACKING_QTY,CN_S_PRODUCTION_BATCH,CN_S_CREATOR,CN_S_CREATOR_BY,CN_T_CREATE,CN_S_MODIFY,CN_S_MODIFY_BY,CN_T_MODIFY)");
|
strSql.Append(" values (");
|
strSql.Append("@CN_S_OP_NO,@CN_S_DTL_ROW,@CN_S_STOCK_CODE,@CN_S_STOCK_AREA,@CN_S_LOCATION_CODE,@CN_S_TRAY_CODE,@CN_S_TRAY_GRID,@CN_S_ITEM_CODE,@CN_S_ITEM_NAME,@CN_S_ITEM_STATE,@CN_S_OWNER,@CN_S_LOT_NO,@CN_S_MODEL,@CN_S_FIGURE_NO,@CN_F_QUANTITY,@CN_F_CHECK_QTY,@CN_S_MEASURE_UNIT,@CN_S_NOTE,@CN_S_ISCHECK,@CN_S_ISWIN,@CN_S_OPERATOR,@CN_T_OPERATE,@CN_S_PACK_CODE,@CN_S_PACKING_UNIT,@CN_F_PACKING_QTY,@CN_S_PRODUCTION_BATCH,@CN_S_CREATOR,@CN_S_CREATOR_BY,@CN_T_CREATE,@CN_S_MODIFY,@CN_S_MODIFY_BY,@CN_T_MODIFY)");
|
|
result = ExecuteTranSql(strSql.ToString(), new
|
{
|
CN_S_OP_NO = entity.CN_S_OP_NO,
|
CN_S_DTL_ROW = entity.CN_S_DTL_ROW,
|
CN_S_STOCK_CODE = entity.CN_S_STOCK_CODE,
|
CN_S_STOCK_AREA = entity.CN_S_STOCK_AREA,
|
CN_S_LOCATION_CODE = entity.CN_S_LOCATION_CODE,
|
CN_S_TRAY_CODE = entity.CN_S_TRAY_CODE,
|
CN_S_TRAY_GRID = entity.CN_S_TRAY_GRID,
|
CN_S_ITEM_CODE = entity.CN_S_ITEM_CODE,
|
CN_S_ITEM_NAME = entity.CN_S_ITEM_NAME,
|
CN_S_ITEM_STATE = entity.CN_S_ITEM_STATE,
|
CN_S_OWNER = entity.CN_S_OWNER,
|
CN_S_LOT_NO = entity.CN_S_LOT_NO,
|
CN_S_MODEL = entity.CN_S_MODEL,
|
CN_S_FIGURE_NO = entity.CN_S_FIGURE_NO,
|
CN_F_QUANTITY = entity.CN_F_QUANTITY,
|
CN_F_CHECK_QTY = entity.CN_F_CHECK_QTY,
|
CN_S_MEASURE_UNIT = entity.CN_S_MEASURE_UNIT,
|
CN_S_NOTE = entity.CN_S_NOTE,
|
CN_S_ISCHECK = entity.CN_S_ISCHECK,
|
CN_S_ISWIN = entity.CN_F_QUANTITY < entity.CN_F_CHECK_QTY ? "Y" : "N",
|
CN_S_OPERATOR = entity.CN_S_OPERATOR,
|
CN_T_OPERATE = entity.CN_T_OPERATE,
|
CN_S_PACK_CODE = entity.CN_S_PACK_CODE,
|
CN_S_PACKING_UNIT = entity.CN_S_PACKING_UNIT,
|
CN_F_PACKING_QTY = entity.CN_F_PACKING_QTY,
|
CN_S_PRODUCTION_BATCH = entity.CN_S_PRODUCTION_BATCH,
|
CN_S_CREATOR = entity.CN_S_CREATOR,
|
CN_S_CREATOR_BY = entity.CN_S_CREATOR_BY,
|
CN_T_CREATE = entity.CN_T_CREATE,
|
CN_S_MODIFY = entity.CN_S_MODIFY,
|
CN_S_MODIFY_BY = entity.CN_S_MODIFY_BY,
|
CN_T_MODIFY = entity.CN_T_MODIFY
|
}, trans);
|
if (result.Status == ResultStatus.Error)
|
return result;
|
}
|
return result;
|
}
|
|
#region 删除
|
/// <summary>
|
/// 删除
|
/// </summary>
|
/// <param name="sqlwhere"></param>
|
/// <returns></returns>
|
public OperateResult DeleteCheckLists(string sqlwhere, IDbTransaction trans)
|
{
|
string sql = "delete dbo.tn_wm_check_list {0}";
|
sql = string.Format(sql, sqlwhere);
|
return ExecuteTranSql(sql, null, trans);
|
}
|
#endregion
|
/// <summary>
|
/// 获取列表
|
/// </summary>
|
/// <param name="obj"></param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/10/25</history>
|
public List<TN_WM_CHECK_LISTEntity> CheckList(string locationCode)
|
{
|
StringBuilder sbStr = new StringBuilder();
|
sbStr.Append("SELECT * FROM tn_wm_check_list WHERE CN_S_OP_NO!=''");
|
if (!string.IsNullOrEmpty(locationCode))
|
{
|
sbStr.Append(" AND CN_S_LOCATION_CODE='" + locationCode + "' ");
|
}
|
return ExecuteQuery<TN_WM_CHECK_LISTEntity>(sbStr.ToString());
|
}
|
|
/// <summary>
|
/// 更新盘点结果
|
/// </summary>
|
/// <param name="planCheck">盘亏数据</param>
|
/// <param name="trans"></param>
|
public OperateResult UpdateCheckResult(List<TN_WM_CHECK_LISTEntity> planCheck, RedisUserEntity user, IDbTransaction trans)
|
{
|
OperateResult or = new OperateResult();
|
string sql = @"UPDATE TN_WM_CHECK_LIST SET
|
CN_F_CHECK_QTY=@CN_F_CHECK_QTY,
|
CN_S_ISCHECK=@CN_S_ISCHECK,
|
CN_S_ISWIN=@CN_S_ISWIN,
|
CN_S_MODIFY=@CN_S_MODIFY,
|
CN_S_MODIFY_BY=@CN_S_MODIFY_BY,
|
CN_T_MODIFY=@CN_T_MODIFY
|
where CN_S_LOCATION_CODE=@CN_S_LOCATION_CODE
|
and CN_S_ITEM_CODE=@CN_S_ITEM_CODE
|
and CN_S_PACKING_UNIT=@CN_S_PACKING_UNIT
|
and CN_F_PACKING_QTY=@CN_F_PACKING_QTY";
|
foreach (TN_WM_CHECK_LISTEntity m in planCheck)
|
{
|
or = ExecuteTranSql(sql, new
|
{
|
CN_F_CHECK_QTY = m.CN_F_CHECK_QTY,
|
CN_S_ISCHECK = "Y",
|
CN_S_ISWIN = "N",
|
CN_S_MODIFY = user.CN_S_LOGIN,
|
CN_S_MODIFY_BY = user.CN_S_NAME,
|
CN_T_MODIFY = m.CN_T_MODIFY,
|
CN_S_LOCATION_CODE = m.CN_S_LOCATION_CODE,
|
CN_S_ITEM_CODE = m.CN_S_ITEM_CODE,
|
CN_S_PACKING_UNIT = m.CN_S_PACKING_UNIT,
|
CN_F_PACKING_QTY = m.CN_F_PACKING_QTY
|
}, trans);
|
if (or.Status != ResultStatus.Success)
|
return or;
|
}
|
return OperateResult.Succeed();
|
}
|
}
|
}
|
