using HanHe.Utility.Data;
|
using HH.WMS.Common;
|
using HH.WMS.Entitys;
|
using HH.WMS.Entitys.Common;
|
using HH.WMS.Entitys.Entitys;
|
using HH.WMS.Entitys.External;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.Common;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
|
namespace HH.WMS.DAL.External
|
{
|
public class TN_WM_B_TRAY_ITEM_RELDAL : DapperBaseDAL
|
{
|
#region GET
|
|
public List<TN_WM_B_TRAY_LOCATIONEntity> GetNeedDoubleTray(string areaCode, string customerName, string power, string currentLevel, string level, string color, string poType, string moduleType)
|
{
|
|
StringBuilder sbStr = new StringBuilder();
|
|
sbStr.Append("SELECT a.*,c.CN_S_IN_AREA_CODE FROM TN_WM_B_TRAY_LOCATION a inner join TN_WM_B_TRAY_ITEM_MST c on a.CN_S_TRAY_CODE = c.CN_S_TRAY_CODE inner join tn_wm_b_location_ext b on a.CN_S_LOCATION_CODE = b.CN_S_LOCATION_CODE WHERE b.CN_S_LOCATION_STATE = '正常' ");
|
if (!string.IsNullOrEmpty(areaCode))
|
{
|
sbStr.Append(" AND a.CN_S_STOCK_AREA='" + areaCode + "' ");
|
}
|
if (!string.IsNullOrEmpty(customerName))
|
{
|
sbStr.Append(" AND c.CN_S_ITEM_CODE='" + customerName + "' ");
|
}
|
if (!string.IsNullOrEmpty(power))
|
{
|
sbStr.Append(" AND c.CN_S_MODEL='" + power + "' ");
|
}
|
if (!string.IsNullOrEmpty(currentLevel))
|
{
|
sbStr.Append(" AND c.CN_S_OWNER='" + currentLevel + "' ");
|
}
|
if (!string.IsNullOrEmpty(level))
|
{
|
sbStr.Append(" AND c.CN_S_FIGURE_NO='" + level + "' ");
|
}
|
if (!string.IsNullOrEmpty(color))
|
{
|
sbStr.Append(" AND c.CN_S_ITEM_NAME='" + color + "' ");
|
}
|
if (!string.IsNullOrEmpty(poType))
|
{
|
sbStr.Append(" AND c.CN_S_ITEM_STATE='" + poType + "' ");
|
}
|
if (!string.IsNullOrEmpty(moduleType))
|
{
|
sbStr.Append(" AND c.CN_S_IN_AREA_CODE='" + moduleType + "' ");
|
}
|
Log.Detail("GetNeedDoubleTray", sbStr.ToString());
|
return ExecuteQuery<TN_WM_B_TRAY_LOCATIONEntity>(sbStr.ToString());
|
}
|
|
#endregion
|
|
|
|
#region 托盘产品关联
|
/// <summary>
|
/// 托盘产品关联
|
/// </summary>
|
/// <param name="entity">数据实体</param>
|
/// <param name="trans">事务</param>
|
/// <returns></returns>
|
/// <history>[HanHe(xdl)] CREATED 2018/01/31</history>
|
public SqlExecuteResult AddItemTray(List<TN_WM_B_TRAY_ITEM_RELEntity> listentity, DbTransaction trans)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("INSERT INTO TN_WM_B_TRAY_ITEM_REL ( CN_GUID,");
|
strSql.Append("CN_S_TRAY_CODE, CN_S_TRAY_TYPE,CN_S_TRAY_GRID, CN_S_LOT_NO,CN_S_PRODUCTION_BATCH,CN_S_PACKING_CODE, CN_S_OWNER, CN_S_SERIAL_NO, CN_S_ITEM_CODE, CN_S_ITEM_NAME,");
|
strSql.Append("CN_S_ITEM_STATE,CN_S_PACKING_UNIT, CN_F_PACKING_QTY, CN_S_VENDOR_NO, CN_S_VENDOR_NAME,CN_F_PURCHASE_PRICE,CN_F_RETAIL_PRICE,CN_T_PRODUCTION,");
|
strSql.Append("CN_T_EXPIRATION, CN_S_NOTE, CN_S_CREATOR,CN_S_CREATOR_BY,CN_T_CREATE, CN_S_MODIFY, CN_S_MODIFY_BY, CN_T_MODIFY, CN_S_OP_FROM, CN_S_FROM_NO,");
|
strSql.Append("CN_N_ROW_NO,CN_C_ISIN,CN_S_FIGURE_NO,CN_S_MODEL, CN_S_MEASURE_UNIT, CN_S_EXT1, CN_S_EXT2 )VALUES(");
|
strSql.Append(":CN_GUID,:CN_S_TRAY_CODE,:CN_S_TRAY_TYPE, :CN_S_TRAY_GRID,:CN_S_LOT_NO,:CN_S_PRODUCTION_BATCH,:CN_S_PACKING_CODE, :CN_S_OWNER, :CN_S_SERIAL_NO, :CN_S_ITEM_CODE,");
|
strSql.Append(":CN_S_ITEM_NAME, :CN_S_ITEM_STATE,:CN_S_PACKING_UNIT,:CN_F_PACKING_QTY, :CN_S_VENDOR_NO, :CN_S_VENDOR_NAME, :CN_F_PURCHASE_PRICE, :CN_F_RETAIL_PRICE,");
|
strSql.Append(":CN_T_PRODUCTION,:CN_T_EXPIRATION,:CN_S_NOTE, :CN_S_CREATOR, :CN_S_CREATOR_BY,:CN_T_CREATE, :CN_S_MODIFY,");
|
strSql.Append(":CN_S_MODIFY_BY,:CN_T_MODIFY,:CN_S_OP_FROM,:CN_S_FROM_NO,:CN_N_ROW_NO,:CN_C_ISIN,:CN_S_FIGURE_NO,:CN_S_MODEL, :CN_S_MEASURE_UNIT,:CN_S_EXT1,:CN_S_EXT2)");
|
List<DbCommand> cmds = new List<DbCommand>();
|
foreach (TN_WM_B_TRAY_ITEM_RELEntity entity in listentity)
|
{
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
DataAccess.AddInParameter(cmd, "CN_GUID", ComDbType.CHAR, entity.CN_GUID);
|
DataAccess.AddInParameter(cmd, "CN_S_TRAY_CODE", ComDbType.CHAR, entity.CN_S_TRAY_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_TRAY_TYPE", ComDbType.CHAR, entity.CN_S_TRAY_TYPE);
|
DataAccess.AddInParameter(cmd, "CN_S_TRAY_GRID", ComDbType.CHAR, entity.CN_S_TRAY_GRID);
|
DataAccess.AddInParameter(cmd, "CN_S_LOT_NO", ComDbType.CHAR, entity.CN_S_LOT_NO);
|
DataAccess.AddInParameter(cmd, "CN_S_PRODUCTION_BATCH", ComDbType.CHAR, entity.CN_S_PRODUCTION_BATCH);
|
DataAccess.AddInParameter(cmd, "CN_S_PACKING_CODE", ComDbType.CHAR, entity.CN_S_PACKING_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_OWNER", ComDbType.CHAR, entity.CN_S_OWNER);
|
DataAccess.AddInParameter(cmd, "CN_S_SERIAL_NO", ComDbType.CHAR, entity.CN_S_SERIAL_NO);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_CODE", ComDbType.CHAR, entity.CN_S_ITEM_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_NAME", ComDbType.CHAR, entity.CN_S_ITEM_NAME);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_STATE", ComDbType.CHAR, entity.CN_S_ITEM_STATE);
|
DataAccess.AddInParameter(cmd, "CN_S_PACKING_UNIT", ComDbType.CHAR, entity.CN_S_PACKING_UNIT);
|
DataAccess.AddInParameter(cmd, "CN_F_PACKING_QTY", ComDbType.CHAR, entity.CN_F_QUANTITY);
|
DataAccess.AddInParameter(cmd, "CN_S_VENDOR_NO", ComDbType.CHAR, entity.CN_S_VENDOR_NO);
|
DataAccess.AddInParameter(cmd, "CN_S_VENDOR_NAME", ComDbType.CHAR, entity.CN_S_VENDOR_NAME);
|
DataAccess.AddInParameter(cmd, "CN_F_PURCHASE_PRICE", ComDbType.DECIMAL, entity.CN_F_PURCHASE_PRICE);
|
DataAccess.AddInParameter(cmd, "CN_F_RETAIL_PRICE", ComDbType.DECIMAL, entity.CN_F_RETAIL_PRICE);
|
DataAccess.AddInParameter(cmd, "CN_T_PRODUCTION", ComDbType.DATE, entity.CN_T_PRODUCTION);
|
DataAccess.AddInParameter(cmd, "CN_T_EXPIRATION", ComDbType.DATE, entity.CN_T_EXPIRATION);
|
DataAccess.AddInParameter(cmd, "CN_S_NOTE", ComDbType.CHAR, entity.CN_S_NOTE);
|
DataAccess.AddInParameter(cmd, "CN_S_CREATOR", ComDbType.CHAR, entity.CN_S_CREATOR);
|
DataAccess.AddInParameter(cmd, "CN_S_CREATOR_BY", ComDbType.CHAR, entity.CN_S_CREATOR_BY);
|
DataAccess.AddInParameter(cmd, "CN_T_CREATE", ComDbType.DATE, entity.CN_T_CREATE);
|
DataAccess.AddInParameter(cmd, "CN_S_MODIFY", ComDbType.CHAR, entity.CN_S_MODIFY);
|
DataAccess.AddInParameter(cmd, "CN_S_MODIFY_BY", ComDbType.CHAR, entity.CN_S_MODIFY_BY);
|
DataAccess.AddInParameter(cmd, "CN_T_MODIFY", ComDbType.DATE, entity.CN_T_MODIFY);
|
DataAccess.AddInParameter(cmd, "CN_S_OP_FROM", ComDbType.CHAR, entity.CN_S_OP_FROM);
|
DataAccess.AddInParameter(cmd, "CN_S_FROM_NO", ComDbType.CHAR, entity.CN_S_FROM_NO);
|
DataAccess.AddInParameter(cmd, "CN_N_ROW_NO", ComDbType.CHAR, entity.CN_N_ROW_NO);
|
DataAccess.AddInParameter(cmd, "CN_C_ISIN", ComDbType.CHAR, "N");
|
DataAccess.AddInParameter(cmd, "CN_S_FIGURE_NO", ComDbType.CHAR, entity.CN_S_FIGURE_NO);
|
DataAccess.AddInParameter(cmd, "CN_S_MODEL", ComDbType.CHAR, entity.CN_S_MODEL);
|
DataAccess.AddInParameter(cmd, "CN_S_MEASURE_UNIT", ComDbType.CHAR, entity.CN_S_MEASURE_UNIT);
|
DataAccess.AddInParameter(cmd, "CN_S_EXT1", ComDbType.STRING, entity.CN_S_EXT1);
|
DataAccess.AddInParameter(cmd, "CN_S_EXT2", ComDbType.STRING, entity.CN_S_EXT2);
|
cmds.Add(cmd);
|
}
|
return ExecuteCommands(cmds, trans);
|
}
|
#endregion
|
|
public SqlExecuteResult Add(TN_WM_B_TRAY_ITEM_RELEntity entity, DbTransaction trans)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("INSERT INTO TN_WM_B_TRAY_ITEM_REL ( CN_GUID,");
|
strSql.Append("CN_S_TRAY_CODE, CN_S_TRAY_TYPE,CN_S_TRAY_GRID, CN_S_LOT_NO,CN_S_PRODUCTION_BATCH,CN_S_PACKING_CODE, CN_S_OWNER, CN_S_SERIAL_NO, CN_S_ITEM_CODE, CN_S_ITEM_NAME,");
|
strSql.Append("CN_S_ITEM_STATE,CN_S_PACKING_UNIT, CN_F_PACKING_QTY, CN_S_VENDOR_NO, CN_S_VENDOR_NAME,CN_F_PURCHASE_PRICE,CN_F_RETAIL_PRICE,CN_T_PRODUCTION,");
|
strSql.Append("CN_T_EXPIRATION, CN_S_NOTE, CN_S_CREATOR,CN_S_CREATOR_BY,CN_T_CREATE, CN_S_MODIFY, CN_S_MODIFY_BY, CN_T_MODIFY, CN_S_OP_FROM, CN_S_FROM_NO,");
|
strSql.Append("CN_N_ROW_NO,CN_C_ISIN,CN_S_FIGURE_NO,CN_S_MODEL, CN_S_MEASURE_UNIT, CN_S_EXT1, CN_S_EXT2 )VALUES(");
|
strSql.Append(":CN_GUID,:CN_S_TRAY_CODE,:CN_S_TRAY_TYPE, :CN_S_TRAY_GRID,:CN_S_LOT_NO,:CN_S_PRODUCTION_BATCH,:CN_S_PACKING_CODE, :CN_S_OWNER, :CN_S_SERIAL_NO, :CN_S_ITEM_CODE,");
|
strSql.Append(":CN_S_ITEM_NAME, :CN_S_ITEM_STATE,:CN_S_PACKING_UNIT,:CN_F_PACKING_QTY, :CN_S_VENDOR_NO, :CN_S_VENDOR_NAME, :CN_F_PURCHASE_PRICE, :CN_F_RETAIL_PRICE,");
|
strSql.Append(":CN_T_PRODUCTION,:CN_T_EXPIRATION,:CN_S_NOTE, :CN_S_CREATOR, :CN_S_CREATOR_BY,:CN_T_CREATE, :CN_S_MODIFY,");
|
strSql.Append(":CN_S_MODIFY_BY,:CN_T_MODIFY,:CN_S_OP_FROM,:CN_S_FROM_NO,:CN_N_ROW_NO,:CN_C_ISIN,:CN_S_FIGURE_NO,:CN_S_MODEL, :CN_S_MEASURE_UNIT,:CN_S_EXT1,:CN_S_EXT2)");
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
DataAccess.AddInParameter(cmd, "CN_GUID", ComDbType.CHAR, entity.CN_GUID);
|
DataAccess.AddInParameter(cmd, "CN_S_TRAY_CODE", ComDbType.CHAR, entity.CN_S_TRAY_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_TRAY_TYPE", ComDbType.CHAR, entity.CN_S_TRAY_TYPE);
|
DataAccess.AddInParameter(cmd, "CN_S_TRAY_GRID", ComDbType.CHAR, entity.CN_S_TRAY_GRID);
|
DataAccess.AddInParameter(cmd, "CN_S_LOT_NO", ComDbType.CHAR, entity.CN_S_LOT_NO);
|
DataAccess.AddInParameter(cmd, "CN_S_PRODUCTION_BATCH", ComDbType.CHAR, entity.CN_S_PRODUCTION_BATCH);
|
DataAccess.AddInParameter(cmd, "CN_S_PACKING_CODE", ComDbType.CHAR, entity.CN_S_PACKING_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_OWNER", ComDbType.CHAR, entity.CN_S_OWNER);
|
DataAccess.AddInParameter(cmd, "CN_S_SERIAL_NO", ComDbType.CHAR, entity.CN_S_SERIAL_NO);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_CODE", ComDbType.CHAR, entity.CN_S_ITEM_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_NAME", ComDbType.CHAR, entity.CN_S_ITEM_NAME);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_STATE", ComDbType.CHAR, entity.CN_S_ITEM_STATE);
|
DataAccess.AddInParameter(cmd, "CN_S_PACKING_UNIT", ComDbType.CHAR, entity.CN_S_PACKING_UNIT);
|
DataAccess.AddInParameter(cmd, "CN_F_PACKING_QTY", ComDbType.CHAR, entity.CN_F_QUANTITY);
|
DataAccess.AddInParameter(cmd, "CN_S_VENDOR_NO", ComDbType.CHAR, entity.CN_S_VENDOR_NO);
|
DataAccess.AddInParameter(cmd, "CN_S_VENDOR_NAME", ComDbType.CHAR, entity.CN_S_VENDOR_NAME);
|
DataAccess.AddInParameter(cmd, "CN_F_PURCHASE_PRICE", ComDbType.DECIMAL, entity.CN_F_PURCHASE_PRICE);
|
DataAccess.AddInParameter(cmd, "CN_F_RETAIL_PRICE", ComDbType.DECIMAL, entity.CN_F_RETAIL_PRICE);
|
DataAccess.AddInParameter(cmd, "CN_T_PRODUCTION", ComDbType.DATE, entity.CN_T_PRODUCTION);
|
DataAccess.AddInParameter(cmd, "CN_T_EXPIRATION", ComDbType.DATE, entity.CN_T_EXPIRATION);
|
DataAccess.AddInParameter(cmd, "CN_S_NOTE", ComDbType.CHAR, entity.CN_S_NOTE);
|
DataAccess.AddInParameter(cmd, "CN_S_CREATOR", ComDbType.CHAR, entity.CN_S_CREATOR);
|
DataAccess.AddInParameter(cmd, "CN_S_CREATOR_BY", ComDbType.CHAR, entity.CN_S_CREATOR_BY);
|
DataAccess.AddInParameter(cmd, "CN_T_CREATE", ComDbType.DATE, entity.CN_T_CREATE);
|
DataAccess.AddInParameter(cmd, "CN_S_MODIFY", ComDbType.CHAR, entity.CN_S_MODIFY);
|
DataAccess.AddInParameter(cmd, "CN_S_MODIFY_BY", ComDbType.CHAR, entity.CN_S_MODIFY_BY);
|
DataAccess.AddInParameter(cmd, "CN_T_MODIFY", ComDbType.DATE, entity.CN_T_MODIFY);
|
DataAccess.AddInParameter(cmd, "CN_S_OP_FROM", ComDbType.CHAR, entity.CN_S_OP_FROM);
|
DataAccess.AddInParameter(cmd, "CN_S_FROM_NO", ComDbType.CHAR, entity.CN_S_FROM_NO);
|
DataAccess.AddInParameter(cmd, "CN_N_ROW_NO", ComDbType.CHAR, entity.CN_N_ROW_NO);
|
DataAccess.AddInParameter(cmd, "CN_C_ISIN", ComDbType.CHAR, "N");
|
DataAccess.AddInParameter(cmd, "CN_S_FIGURE_NO", ComDbType.CHAR, entity.CN_S_FIGURE_NO);
|
DataAccess.AddInParameter(cmd, "CN_S_MODEL", ComDbType.CHAR, entity.CN_S_MODEL);
|
DataAccess.AddInParameter(cmd, "CN_S_MEASURE_UNIT", ComDbType.CHAR, entity.CN_S_MEASURE_UNIT);
|
DataAccess.AddInParameter(cmd, "CN_S_EXT1", ComDbType.STRING, entity.CN_S_EXT1);
|
DataAccess.AddInParameter(cmd, "CN_S_EXT2", ComDbType.STRING, entity.CN_S_EXT2);
|
return ExecuteCommand(cmd, trans);
|
}
|
|
#region 获取已码托盘
|
/// <summary>
|
/// 获取已码托盘
|
/// </summary>
|
/// <param name="obj"></param>
|
/// <returns></returns>
|
public List<TN_WM_B_TRAY_ITEM_RELEntity> GetAlreadyCodedSxcc(string trayCode)
|
{
|
List<TN_WM_B_TRAY_ITEM_RELEntity> list = new List<TN_WM_B_TRAY_ITEM_RELEntity>();
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append("SELECT CN_S_TRAY_CODE,CN_S_TRAY_TYPE,CN_S_TRAY_GRID,CN_S_LOT_NO,CN_S_SERIAL_NO,");
|
strSql.Append("CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_VENDOR_NO,CN_S_VENDOR_NAME,CN_S_FIGURE_NO,CN_S_FROM_NO, CN_F_PACKING_QTY,CN_S_PACKING_UNIT from TN_WM_B_TRAY_ITEM_REL ");
|
|
if (!string.IsNullOrEmpty(trayCode))
|
{
|
strSql.Append(" where CN_S_TRAY_CODE = '" + trayCode + "'");
|
}
|
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
try
|
{
|
list = DataAccessExtensive.ExecuteListEntity<TN_WM_B_TRAY_ITEM_RELEntity>(this.DataAccess, cmd, SetRelEntitySxcc);
|
return list;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
public List<TN_WM_B_TRAY_ITEM_RELEntity> GetAlreadyCodedSum(dynamic obj)
|
{
|
List<TN_WM_B_TRAY_ITEM_RELEntity> list = new List<TN_WM_B_TRAY_ITEM_RELEntity>();
|
StringBuilder strSql = new StringBuilder();
|
string sumstr = "";
|
|
strSql.Append("SELECT CN_S_TRAY_CODE,CN_S_TRAY_GRID,CN_S_ITEM_CODE,CN_S_ITEM_NAME,");
|
strSql.Append("SUM(CN_F_PACKING_QTY) CN_F_PACKING_QTY from TN_WM_B_TRAY_ITEM_REL ");
|
|
if (!string.IsNullOrEmpty(obj.sqlWhere.ToString()))
|
{
|
strSql.Append(obj.sqlWhere.ToString());
|
}
|
|
strSql.Append(" GROUP BY CN_S_TRAY_CODE,CN_S_TRAY_GRID,CN_S_ITEM_CODE,CN_S_ITEM_NAME");
|
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
try
|
{
|
list = DataAccessExtensive.ExecuteListEntity<TN_WM_B_TRAY_ITEM_RELEntity>(this.DataAccess, cmd, SetRelSumEntity);
|
return list;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
/// <summary>
|
/// 设置实体的各列
|
/// </summary>
|
private void SetRelEntitySxcc(TN_WM_B_TRAY_ITEM_RELEntity entity, IDataReader reader)
|
{
|
SetEntityUti(entity, "CN_S_TRAY_CODE", "CN_S_TRAY_CODE", 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_F_PACKING_QTY", "CN_F_PACKING_QTY", reader);
|
SetEntityUti(entity, "CN_S_PACKING_UNIT", "CN_S_PACKING_UNIT", reader);
|
SetEntityUti(entity, "CN_S_VENDOR_NO", "CN_S_VENDOR_NO", reader);
|
SetEntityUti(entity, "CN_S_VENDOR_NAME", "CN_S_VENDOR_NAME", reader);
|
SetEntityUti(entity, "CN_S_FIGURE_NO", "CN_S_FIGURE_NO", reader);
|
SetEntityUti(entity, "CN_S_FROM_NO", "CN_S_FROM_NO", reader);
|
}
|
/// <summary>
|
/// 设置实体的各列
|
/// </summary>
|
private void SetRelEntity(TN_WM_B_TRAY_ITEM_RELEntity entity, IDataReader reader)
|
{
|
SetEntityUti(entity, "CN_S_TRAY_CODE", "CN_S_TRAY_CODE", reader);
|
SetEntityUti(entity, "CN_S_TRAY_TYPE", "CN_S_TRAY_TYPE", reader);
|
SetEntityUti(entity, "CN_S_TRAY_GRID", "CN_S_TRAY_GRID", reader);
|
SetEntityUti(entity, "CN_S_LOT_NO", "CN_S_LOT_NO", reader);
|
SetEntityUti(entity, "CN_S_SERIAL_NO", "CN_S_SERIAL_NO", 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_OWNER", "CN_S_OWNER", reader);
|
SetEntityUti(entity, "CN_S_ITEM_STATE", "CN_S_ITEM_STATE", reader);
|
SetEntityUti(entity, "CN_F_QUANTITY", "CN_F_PACKING_QTY", reader);
|
SetEntityUti(entity, "CN_S_FROM_NO", "CN_S_FROM_NO", reader);
|
SetEntityUti(entity, "CN_N_ROW_NO", "CN_N_ROW_NO", reader);
|
}
|
/// <summary>
|
/// 设置实体的各列
|
/// </summary>
|
private void SetRelSumEntity(TN_WM_B_TRAY_ITEM_RELEntity entity, IDataReader reader)
|
{
|
SetEntityUti(entity, "CN_S_TRAY_CODE", "CN_S_TRAY_CODE", reader);
|
SetEntityUti(entity, "CN_S_TRAY_GRID", "CN_S_TRAY_GRID", 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_F_QUANTITY", "CN_F_PACKING_QTY", reader);
|
}
|
|
public DataTable GetDTAlreadyCoded(dynamic obj)
|
{
|
DataTable dt = new DataTable();
|
StringBuilder strSql = new StringBuilder();
|
string sumstr = "";
|
|
strSql.Append("SELECT CN_S_TRAY_CODE,CN_S_TRAY_TYPE,CN_S_TRAY_GRID,CN_S_LOT_NO,CN_S_SERIAL_NO,");
|
strSql.Append("CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_FROM_NO,CN_N_ROW_NO,SUM(CN_F_PACKING_QTY) CN_F_PACKING_QTY from TN_WM_B_TRAY_ITEM_REL ");
|
|
if (!string.IsNullOrEmpty(obj.sqlWhere.ToString()))
|
{
|
strSql.Append(obj.sqlWhere.ToString());
|
}
|
|
strSql.Append(" GROUP BY CN_S_TRAY_CODE,CN_S_TRAY_TYPE,CN_S_TRAY_GRID,CN_S_LOT_NO,CN_S_SERIAL_NO,");
|
strSql.Append("CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_FROM_NO,CN_N_ROW_NO ");
|
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
try
|
{
|
dt = DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
|
return dt;
|
}
|
catch (Exception ex)
|
{
|
return dt;
|
}
|
}
|
#endregion
|
|
|
#region 根据条件获取实体
|
/// <summary>
|
/// 根据条件获取实体
|
/// </summary>
|
/// <param name="obj"></param>
|
/// <param name="loadScope">
|
/// <returns></returns>
|
/// <history>[HanHe(gp)] CREATED 2018/03/02</history>
|
public List<ItemRelEntity> GetItemRelModel(string Where)
|
{
|
string sql = @" select * from TN_WM_B_TRAY_ITEM_REL " + Where;
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
|
//获得列表
|
List<ItemRelEntity> list = DataAccessExtensive.ExecuteListEntity<ItemRelEntity>(this.DataAccess, cmd, SetMastItemRelEntity);
|
return list;
|
|
}
|
/// <summary>
|
/// 设置实体的各列
|
/// <summary>
|
private void SetMastItemRelEntity(ItemRelEntity entity, IDataReader reader)
|
{
|
SetEntityUti(entity, "CN_S_TRAY_CODE", "CN_S_TRAY_CODE", reader);
|
SetEntityUti(entity, "CN_S_TRAY_TYPE", "CN_S_TRAY_TYPE", reader);
|
SetEntityUti(entity, "CN_S_OWNER", "CN_S_OWNER", reader);
|
SetEntityUti(entity, "CN_S_ITEM_NAME", "CN_S_ITEM_NAME", reader);
|
SetEntityUti(entity, "CN_S_ITEM_CODE", "CN_S_ITEM_CODE", 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_F_PACKING_QTY", "CN_F_PACKING_QTY", reader);
|
SetEntityUti(entity, "CN_S_NOTE", "CN_S_NOTE", reader);
|
SetEntityUti(entity, "CN_S_SERIAL_NO", "CN_S_SERIAL_NO", reader);
|
SetEntityUti(entity, "CN_F_PURCHASE_PRICE", "CN_F_PURCHASE_PRICE", reader);
|
SetEntityUti(entity, "CN_T_PRODUCTION", "CN_T_PRODUCTION", reader);
|
SetEntityUti(entity, "CN_S_PACKING_UNIT", "CN_S_PACKING_UNIT", reader);
|
SetEntityUti(entity, "CN_S_PRODUCTION_BATCH", "CN_S_PRODUCTION_BATCH", reader);
|
}
|
#endregion
|
|
#region 判断数据库是否存在某个条件下的数据
|
/// <summary>
|
/// 判断数据库是否存在某个条件下的数据
|
/// </summary>
|
/// <param name="entity">条件实体</param>
|
/// <returns></returns>
|
public DataTable IsExistsTrayRel(TN_WM_B_TRAY_ITEM_RELEntity entity)
|
{
|
StringBuilder strSql = new StringBuilder();
|
string sumstr = "";
|
|
strSql.Append("select * from TN_WM_B_TRAY_ITEM_REL ");
|
|
|
|
strSql.Append(" WHERE CN_S_TRAY_CODE='" + entity.CN_S_TRAY_CODE + "' AND CN_S_ITEM_CODE='" + entity.CN_S_ITEM_CODE + "' AND CN_S_FROM_NO='" + entity.CN_S_FROM_NO + "' AND CN_N_ROW_NO='" + entity.CN_N_ROW_NO + "' ");
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
try
|
{
|
DataTable dt = DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
|
return dt;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
#endregion
|
|
#region 获取该库区中托盘最后一个入库的时间及对应的货位
|
/// <summary>
|
/// 判断数据库是否存在某个条件下的数据
|
/// </summary>
|
/// <param name="entity">条件实体</param>
|
/// <returns></returns>
|
public string GetLastTrayLocationCode(string areaCode, string isEmptyTray, out string itemCodeType)
|
{
|
StringBuilder strSql = new StringBuilder();
|
itemCodeType = "";
|
strSql.Append("select top 1 CN_S_LOCATION_CODE,B.CN_S_ITEM_CODE from TN_WM_B_OBJ_LOCATION A LEFT JOIN ");
|
strSql.Append(" tn_wm_b_tray_item_rel B ON A.CN_S_OBJ_CODE = B.CN_S_TRAY_CODE WHERE A.CN_S_OBJ = '托盘' AND A.CN_S_STOCK_AREA = '" + areaCode + "' ");
|
if (isEmptyTray == "Y")
|
{
|
strSql.Append(" AND B.CN_S_TRAY_CODE IS NULL ");
|
}
|
else
|
{
|
strSql.Append(" AND B.CN_S_TRAY_CODE IS NOT NULL ");
|
}
|
strSql.Append(" ORDER BY A.CN_T_OPERATE DESC ");
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
try
|
{
|
DataRow dRow = DataAccessExtensive.ExecuteSingleData(this.DataAccess, cmd);
|
if (dRow == null)
|
{
|
return "";
|
}
|
else
|
{
|
if (dRow[1] != null)
|
{
|
itemCodeType = dRow[1].ToString();
|
}
|
return dRow[0].ToString();
|
}
|
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
#endregion
|
|
#region 获取该库区中托盘最早一个入库的时间及对应的货位
|
/// <summary>
|
/// 判断数据库是否存在某个条件下的数据
|
/// </summary>
|
/// <param name="entity">条件实体</param>
|
/// <returns></returns>
|
public string GetTrayLocationCodeOut(string firstOrLast, string areaCode, string isEmptyTray, string outItemType, out string itemCode)
|
{
|
itemCode = string.Empty;
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("select top 1 CN_S_LOCATION_CODE,B.CN_S_ITEM_CODE from TN_WM_B_OBJ_LOCATION A LEFT JOIN ");
|
strSql.Append(" tn_wm_b_tray_item_rel B ON A.CN_S_OBJ_CODE = B.CN_S_TRAY_CODE WHERE A.CN_S_OBJ = '托盘' AND A.CN_S_STOCK_AREA = '" + areaCode + "' ");
|
if (isEmptyTray == "Y")
|
{
|
strSql.Append(" AND B.CN_S_TRAY_CODE IS NULL ");
|
}
|
else
|
{
|
if (!string.IsNullOrEmpty(outItemType))
|
{
|
strSql.Append(" AND B.CN_S_ITEM_CODE = '" + outItemType + "'");
|
}
|
strSql.Append(" AND B.CN_S_TRAY_CODE IS NOT NULL ");
|
}
|
if (firstOrLast == "first")
|
{
|
strSql.Append(" ORDER BY A.CN_T_OPERATE ASC ");
|
}
|
else
|
{
|
strSql.Append(" ORDER BY A.CN_T_OPERATE DESC ");
|
}
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
try
|
{
|
DataRow dRow = DataAccessExtensive.ExecuteSingleData(this.DataAccess, cmd);
|
if (dRow == null)
|
{
|
return "";
|
}
|
else
|
{
|
if (dRow[1] != null)
|
{
|
itemCode = dRow[1].ToString();
|
}
|
return dRow[0].ToString();
|
}
|
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
public string GetEmptyTrayCount(string areaCode)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("select count(1) from TN_WM_B_OBJ_LOCATION A LEFT JOIN ");
|
strSql.Append(" tn_wm_b_tray_item_rel B ON A.CN_S_OBJ_CODE = B.CN_S_TRAY_CODE WHERE A.CN_S_OBJ = '托盘' AND A.CN_S_STOCK_AREA = '" + areaCode + "' ");
|
strSql.Append(" AND B.CN_S_TRAY_CODE IS NULL ");
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
try
|
{
|
DataRow dRow = DataAccessExtensive.ExecuteSingleData(this.DataAccess, cmd);
|
if (dRow == null)
|
{
|
return "";
|
}
|
else
|
{
|
return dRow[0].ToString();
|
}
|
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
public DateTime GetOperateTimeByLocation(string locationCode, int index)
|
{
|
DateTime operateTime = DateTime.Now;
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("select CN_T_OPERATE from TN_WM_B_OBJ_LOCATION WHERE ");
|
strSql.Append(" CN_S_LOCATION_CODE = '" + locationCode + "' AND CN_N_INDEX = " + index);
|
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
try
|
{
|
DataRow dRow = DataAccessExtensive.ExecuteSingleData(this.DataAccess, cmd);
|
if (dRow != null)
|
{
|
|
operateTime = DateTime.Parse(dRow[0].ToString());
|
|
}
|
return operateTime;
|
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
#endregion
|
|
#region 更新码盘数据
|
/// <summary>
|
/// 更新码盘数据
|
/// </summary>
|
/// <param name="lstQueryAdd">需要更新的码盘数据</param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public SqlExecuteResult EditCodedDisc(List<TN_WM_B_TRAY_ITEM_RELEntity> lstQueryAdd, DbTransaction trans)
|
{
|
string sql = @"UPDATE TN_WM_B_TRAY_ITEM_REL SET CN_F_PACKING_QTY=CN_F_PACKING_QTY+:CN_F_PACKING_QTY
|
WHERE CN_S_TRAY_CODE=:CN_S_TRAY_CODE AND CN_S_ITEM_CODE=:CN_S_ITEM_CODE AND CN_S_FROM_NO=:CN_S_FROM_NO AND CN_N_ROW_NO=:CN_N_ROW_NO";
|
List<DbCommand> cmds = new List<DbCommand>();
|
foreach (TN_WM_B_TRAY_ITEM_RELEntity entity in lstQueryAdd)
|
{
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
DataAccess.AddInParameter(cmd, "CN_S_TRAY_CODE", ComDbType.STRING, entity.CN_S_TRAY_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_CODE", ComDbType.STRING, entity.CN_S_ITEM_CODE);
|
DataAccess.AddInParameter(cmd, "CN_F_PACKING_QTY", ComDbType.STRING, entity.CN_F_QUANTITY);
|
DataAccess.AddInParameter(cmd, "CN_S_FROM_NO", ComDbType.STRING, entity.CN_S_FROM_NO);
|
DataAccess.AddInParameter(cmd, "CN_N_ROW_NO", ComDbType.STRING, entity.CN_N_ROW_NO);
|
cmds.Add(cmd);
|
}
|
SqlExecuteResult sr = new SqlExecuteResult();
|
try
|
{
|
sr = ExecuteCommands(cmds, trans);
|
return sr;
|
}
|
catch (Exception)
|
{
|
throw;
|
}
|
}
|
#endregion
|
|
#region 根据托盘码或产品序列号获取产品
|
|
public DataTable GetTrayItemAllList(string trayCode)
|
{
|
StringBuilder strSql = new StringBuilder();
|
string strWhere = " where 1=1 ";
|
DataTable dt = new DataTable();
|
|
if (!string.IsNullOrEmpty(trayCode))
|
{
|
strWhere += " and CN_S_TRAY_CODE='" + trayCode + "'";
|
}
|
strSql.Append("SELECT * FROM TN_WM_B_TRAY_ITEM_REL ");
|
strSql.Append(strWhere);
|
|
try
|
{
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
dt = DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
|
return dt;
|
}
|
catch (Exception)
|
{
|
return dt;
|
}
|
|
|
}
|
|
#endregion
|
|
#region 拣货时，解绑物料与托盘的关联
|
/// <summary>
|
/// 拣货时，解绑物料与托盘的关联
|
/// </summary>
|
/// <param name="entitys"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
/// <History>[Hanhe(DBS)] created 2018-4-10</History>
|
public SqlExecuteResult Unbundling(List<TN_WM_B_TRAY_ITEM_RELEntity> entitys, DbTransaction trans)
|
{
|
List<DbCommand> cmds = new List<DbCommand>();
|
|
foreach (TN_WM_B_TRAY_ITEM_RELEntity m in entitys)
|
{
|
if (m.IsUpdateObject)
|
{
|
if (m.CN_F_QUANTITY == 0)
|
{
|
//移除
|
string sql = @"delete from TN_WM_B_TRAY_ITEM_REL where CN_GUID=:CN_GUID";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
DataAccess.AddInParameter(cmd, "CN_GUID", ComDbType.STRING, m.CN_GUID);
|
cmds.Add(cmd);
|
}
|
else
|
{
|
string sql = @"update TN_WM_B_TRAY_ITEM_REL set CN_F_PACKING_QTY=:CN_F_PACKING_QTY where CN_GUID=:CN_GUID";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
DataAccess.AddInParameter(cmd, "CN_F_PACKING_QTY", ComDbType.DECIMAL, m.CN_F_QUANTITY);
|
DataAccess.AddInParameter(cmd, "CN_GUID", ComDbType.STRING, m.CN_GUID);
|
cmds.Add(cmd);
|
}
|
}
|
}
|
SqlExecuteResult result = ExecuteCommands(cmds, trans);
|
return result;
|
// return null;
|
// List<DbCommand> cmds = new List<DbCommand>();
|
// foreach (var v in group)
|
// {
|
// if (!string.IsNullOrEmpty(v.CN_S_PACKING_CODE))
|
// {
|
// //扫描的包装码
|
// string sql = @"update TN_WM_B_TRAY_ITEM_REL set CN_F_PACKING_QTY=CN_F_PACKING_QTY-:PICKQTY
|
// where CN_S_PACKING_CODE=:CN_S_PACKING_CODE
|
// and CN_S_TRAY_CODE=:CN_S_TRAY_CODE
|
// and CN_S_TRAY_GRID=:CN_S_TRAY_GRID";
|
// DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
// DataAccess.AddInParameter(cmd, "PICKQTY", ComDbType.DECIMAL, v.QTY);
|
// DataAccess.AddInParameter(cmd, "CN_S_PACKING_CODE", ComDbType.STRING, v.CN_S_PACKING_CODE);
|
// DataAccess.AddInParameter(cmd, "CN_S_TRAY_CODE", ComDbType.STRING, v.CN_S_TRAY_CODE);
|
// DataAccess.AddInParameter(cmd, "CN_S_TRAY_GRID", ComDbType.STRING, v.CN_S_TRAY_GRID);
|
// cmds.Add(cmd);
|
// }
|
// else if (!string.IsNullOrEmpty(v.CN_S_IN_FROM_NO))
|
// {
|
// //扫描产品码
|
// string sql = @"update TN_WM_B_TRAY_ITEM_REL set CN_F_PACKING_QTY=CN_F_PACKING_QTY-:PICKQTY
|
// where CN_S_FROM_NO=:CN_S_FROM_NO
|
// and CN_N_ROW_NO=:CN_N_ROW_NO
|
// and CN_S_TRAY_CODE=:CN_S_TRAY_CODE
|
// and CN_S_TRAY_GRID=:CN_S_TRAY_GRID";
|
// DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
// DataAccess.AddInParameter(cmd, "PICKQTY", ComDbType.DECIMAL, v.QTY);
|
// DataAccess.AddInParameter(cmd, "CN_S_FROM_NO", ComDbType.STRING, v.CN_S_IN_FROM_NO);
|
// DataAccess.AddInParameter(cmd, "CN_N_ROW_NO", ComDbType.STRING, v.CN_N_IN_ROW_NO);
|
// DataAccess.AddInParameter(cmd, "CN_S_TRAY_CODE", ComDbType.STRING, v.CN_S_TRAY_CODE);
|
// DataAccess.AddInParameter(cmd, "CN_S_TRAY_GRID", ComDbType.STRING, v.CN_S_TRAY_GRID);
|
// cmds.Add(cmd);
|
// }
|
// }
|
// SqlExecuteResult result = ExecuteCommands(cmds, trans);
|
// if (result.Success)
|
// {
|
// foreach (var v in group)
|
// {
|
// if (!string.IsNullOrEmpty(v.CN_S_PACKING_CODE))
|
// {
|
// //扫描的包装码
|
// string sql = @"if exists
|
// (select * from TN_WM_B_TRAY_ITEM_REL where CN_S_PACKING_CODE=:CN_S_PACKING_CODE
|
// and CN_F_PACKING_QTY=0
|
// and CN_S_TRAY_CODE=:CN_S_TRAY_CODE
|
// and CN_S_TRAY_GRID=:CN_S_TRAY_GRID)
|
// begin
|
// delete from TN_WM_B_TRAY_ITEM_REL where CN_S_PACKING_CODE=:CN_S_PACKING_CODE
|
// and CN_S_TRAY_CODE=:CN_S_TRAY_CODE
|
// and CN_S_TRAY_GRID=:CN_S_TRAY_GRID
|
// end";
|
// DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
// DataAccess.AddInParameter(cmd, "CN_S_PACKING_CODE", ComDbType.STRING, v.CN_S_PACKING_CODE);
|
// DataAccess.AddInParameter(cmd, "CN_S_TRAY_CODE", ComDbType.STRING, v.CN_S_TRAY_CODE);
|
// DataAccess.AddInParameter(cmd, "CN_S_TRAY_GRID", ComDbType.STRING, v.CN_S_TRAY_GRID);
|
// cmds.Add(cmd);
|
// }
|
// else if (!string.IsNullOrEmpty(v.CN_S_IN_FROM_NO))
|
// {
|
// //扫描产品码
|
// string sql = @"if exists
|
// (select * from TN_WM_B_TRAY_ITEM_REL where CN_F_PACKING_QTY=0
|
// and CN_S_FROM_NO=:CN_S_FROM_NO
|
// and CN_N_ROW_NO=:CN_N_ROW_NO
|
// and CN_S_TRAY_CODE=:CN_S_TRAY_CODE
|
// and CN_S_TRAY_GRID=:CN_S_TRAY_GRID)
|
// begin
|
// delete from TN_WM_B_TRAY_ITEM_REL where CN_S_FROM_NO=:CN_S_FROM_NO
|
// and CN_N_ROW_NO=:CN_N_ROW_NO
|
// and CN_S_TRAY_CODE=:CN_S_TRAY_CODE
|
// and CN_S_TRAY_GRID=:CN_S_TRAY_GRID
|
// end
|
// ";
|
// DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
// DataAccess.AddInParameter(cmd, "CN_S_FROM_NO", ComDbType.STRING, v.CN_S_IN_FROM_NO);
|
// DataAccess.AddInParameter(cmd, "CN_N_ROW_NO", ComDbType.STRING, v.CN_N_IN_ROW_NO);
|
// DataAccess.AddInParameter(cmd, "CN_S_TRAY_CODE", ComDbType.STRING, v.CN_S_TRAY_CODE);
|
// DataAccess.AddInParameter(cmd, "CN_S_TRAY_GRID", ComDbType.STRING, v.CN_S_TRAY_GRID);
|
// cmds.Add(cmd);
|
// }
|
// }
|
// result = ExecuteCommands(cmds, trans);
|
// }
|
// return result;
|
}
|
#endregion
|
|
#region 判断包装编码是否有过码盘记录
|
public List<TN_WM_B_TRAY_ITEM_RELEntity> isExistCodedDisc(string packingCode)
|
{
|
List<TN_WM_B_TRAY_ITEM_RELEntity> list = new List<TN_WM_B_TRAY_ITEM_RELEntity>();
|
string where = " WHERE 1=1 ";
|
string sql = @" select * from TN_WM_B_TRAY_ITEM_REL ";
|
if (!string.IsNullOrEmpty(packingCode))
|
{
|
packingCode = packingCode.Replace(",", "','");
|
where += " AND CN_S_PACKING_CODE in ('" + packingCode + "')";
|
sql += where;
|
}
|
else
|
{
|
return list;
|
}
|
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
try
|
{
|
//获得列表
|
list = DataAccessExtensive.ExecuteListEntity<TN_WM_B_TRAY_ITEM_RELEntity>(this.DataAccess, cmd, SetRelEntity);
|
return list;
|
}
|
catch (Exception)
|
{
|
|
throw;
|
}
|
}
|
#endregion
|
|
#region 根据条件获取实体
|
/// <summary>
|
/// 根据条件获取实体
|
/// </summary>
|
/// <param name="obj"></param>
|
/// <param name="loadScope">
|
/// <returns></returns>
|
/// <history>[HanHe(gp)] CREATED 2018/03/02</history>
|
public List<ItemRelEntity> ItemRelSummaryList(string Where)
|
{
|
string sql = @" select CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,CN_S_LOT_NO,CN_S_OWNER,SUM(CN_F_PACKING_QTY)as CN_F_PACKING_QTY
|
from TN_WM_B_TRAY_ITEM_REL " + Where + @"
|
group by CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,CN_S_LOT_NO,CN_S_OWNER";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
try
|
{
|
//获得列表
|
List<ItemRelEntity> list = DataAccessExtensive.ExecuteListEntity<ItemRelEntity>(this.DataAccess, cmd, ItemRelSummaryListEntity);
|
return list;
|
}
|
catch (Exception)
|
{
|
|
throw;
|
}
|
}
|
/// <summary>
|
/// 设置实体的各列
|
/// <summary>
|
private void ItemRelSummaryListEntity(ItemRelEntity entity, IDataReader 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_OWNER", "CN_S_OWNER", reader);
|
SetEntityUti(entity, "CN_F_PACKING_QTY", "CN_F_PACKING_QTY", reader);
|
}
|
#endregion
|
#region 判断托盘内是否有物料信息
|
/// <summary>
|
/// 根据条件获取实体
|
/// </summary>
|
/// <param name="obj"></param>
|
/// <param name="loadScope">
|
/// <returns></returns>
|
/// <history>[HanHe(gp)] CREATED 2018/03/02</history>
|
public bool ExistTrayCode(string trayCode)
|
{
|
bool result = false;
|
string sql = @" select count(*)
|
from TN_WM_B_TRAY_ITEM_REL where CN_S_TRAY_CODE = '" + trayCode + "'";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
try
|
{
|
//获得列表
|
DataTable dt = DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd, null);
|
if (dt.Rows[0][0].ToString() != "0")
|
{
|
result = true;
|
}
|
else
|
{
|
result = false;
|
}
|
}
|
catch (Exception)
|
{
|
|
throw;
|
}
|
return result;
|
}
|
|
#endregion
|
#region 判断货位内是否有物料信息
|
/// <summary>
|
/// 根据条件获取实体
|
/// </summary>
|
/// <param name="obj"></param>
|
/// <param name="loadScope">
|
/// <returns></returns>
|
/// <history>[HanHe(gp)] CREATED 2018/03/02</history>
|
public bool ExistItemInLocationCode(string locationCode)
|
{
|
bool result = false;
|
string sql = @" select count(a.CN_S_ITEM_CODE)
|
from TN_WM_B_TRAY_ITEM_REL a inner join TN_WM_B_OBJ_LOCATION b on a.CN_S_TRAY_CODE = b.CN_S_OBJ_CODE
|
where b.CN_S_LOCATION_CODE = '" + locationCode + "'";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
try
|
{
|
//获得列表
|
DataTable dt = DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd, null);
|
if (dt.Rows[0][0].ToString() != "0")
|
{
|
result = true;
|
}
|
else
|
{
|
result = false;
|
}
|
}
|
catch (Exception)
|
{
|
|
throw;
|
}
|
return result;
|
}
|
public bool ExistEmptyTrayInLocationCode(string locationCode)
|
{
|
bool result = false;
|
string sql = @" select count(1) from
|
TN_WM_B_OBJ_LOCATION where CN_S_LOCATION_CODE = '" + locationCode + "'";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
try
|
{
|
//获得列表
|
DataTable dt = DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd, null);
|
if (dt.Rows[0][0].ToString() != "0")
|
{
|
result = true;
|
}
|
else
|
{
|
result = false;
|
}
|
}
|
catch (Exception)
|
{
|
|
throw;
|
}
|
return result;
|
}
|
|
#endregion
|
#region 根据条件获取托盘内产品类型
|
/// <summary>
|
/// 根据条件获取实体
|
/// </summary>
|
/// <param name="obj"></param>
|
/// <param name="loadScope">
|
/// <returns></returns>
|
/// <history>[HanHe(gp)] CREATED 2018/03/02</history>
|
public string GetItemTypeByTrayCode(string trayCode)
|
{
|
string result = "";
|
string sql = @" select DISTINCT CN_S_ITEM_CODE
|
from TN_WM_B_TRAY_ITEM_REL where CN_S_TRAY_CODE = '" + trayCode + "'";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
try
|
{
|
//获得列表
|
DataTable dt = DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd, null);
|
if (dt.Rows.Count > 0)
|
{
|
result = dt.Rows[0][0].ToString();
|
}
|
}
|
catch (Exception)
|
{
|
|
throw;
|
}
|
return result;
|
}
|
|
#endregion
|
|
#region 根据条件获取托盘内产品类型
|
/// <summary>
|
/// 根据条件获取实体
|
/// </summary>
|
/// <param name="obj"></param>
|
/// <param name="loadScope">
|
/// <returns></returns>
|
/// <history>[HanHe(gp)] CREATED 2018/03/02</history>
|
public string GetItemTypeByLocationCode(string locationCode)
|
{
|
string result = "";
|
string sql = @" select DISTINCT CN_S_ITEM_CODE
|
from TN_WM_B_TRAY_ITEM_REL A INNER JOIN TN_WM_B_OBJ_LOCATION B ON A.CN_S_TRAY_CODE = B.CN_S_OBJ_CODE
|
where B.CN_S_OBJ_CODE = '" + locationCode + "'";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
try
|
{
|
//获得列表
|
DataTable dt = DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd, null);
|
if (dt.Rows.Count > 0)
|
{
|
result = dt.Rows[0][0].ToString();
|
}
|
}
|
catch (Exception)
|
{
|
|
throw;
|
}
|
return result;
|
}
|
|
#endregion
|
|
|
#region 根据托盘编码和单据编号 删除托盘产品关联
|
/// <summary>
|
/// 根据托盘编码和单据编号 删除托盘产品关联
|
/// </summary>
|
/// <param name="trayCodes">托盘编码</param>
|
/// <param name="fromNo">业务单据</param>
|
/// <returns></returns>
|
public SqlExecuteResult DeleteTrayItemRel(string trayCodes, string fromNo)
|
{
|
string sql = "DELETE FROM TN_WM_B_TRAY_ITEM_REL WHERE CN_S_TRAY_CODE=:CN_S_TRAY_CODE AND CN_S_FROM_NO=:CN_S_FROM_NO ";
|
List<DbCommand> cmds = new List<DbCommand>();
|
foreach (string m in trayCodes.Split(','))
|
{
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
DataAccess.AddInParameter(cmd, "CN_S_TRAY_CODE", ComDbType.STRING, m);
|
DataAccess.AddInParameter(cmd, "CN_S_FROM_NO", ComDbType.STRING, fromNo);
|
cmds.Add(cmd);
|
}
|
SqlExecuteResult result = ExecuteCommands(cmds, null);
|
return result;
|
}
|
/// <summary>
|
/// 根据托盘编码和PackingCode 删除托盘产品关联
|
/// </summary>
|
/// <param name="trayCodes">托盘编码</param>
|
/// <param name="packingCode">PackingCode</param>
|
/// <returns></returns>
|
public SqlExecuteResult DeleteByPackingCode(string trayCode, List<PackingList> lstpackingQuery)
|
{
|
string sql = "DELETE FROM TN_WM_B_TRAY_ITEM_REL WHERE CN_S_TRAY_CODE=:CN_S_TRAY_CODE AND CN_S_PACKING_CODE=:CN_S_PACKING_CODE ";
|
List<DbCommand> cmds = new List<DbCommand>();
|
foreach (PackingList m in lstpackingQuery)
|
{
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
DataAccess.AddInParameter(cmd, "CN_S_TRAY_CODE", ComDbType.STRING, trayCode);
|
DataAccess.AddInParameter(cmd, "CN_S_PACKING_CODE", ComDbType.STRING, m.CN_S_PACKING_CODE);
|
cmds.Add(cmd);
|
}
|
SqlExecuteResult result = ExecuteCommands(cmds, null);
|
return result;
|
}
|
|
/// <summary>
|
/// PDA物料移除
|
/// </summary>
|
/// <param name="trayCode"></param>
|
/// <param name="lstitemQuery"></param>
|
/// <returns></returns>
|
public SqlExecuteResult DeleteByitemCode(string trayCode, List<ItemList> lstitemQuery)
|
{
|
string sql = "update TN_WM_B_TRAY_ITEM_REL set CN_F_PACKING_QTY=CN_F_PACKING_QTY-:CN_F_QUANTITY ";
|
sql = sql + "WHERE CN_S_TRAY_CODE=:CN_S_TRAY_CODE AND CN_S_TRAY_GRID=:CN_S_TRAY_GRID AND CN_S_ITEM_CODE=:CN_S_ITEM_CODE AND CN_S_PACKING_CODE='' ";
|
List<DbCommand> cmds = new List<DbCommand>();
|
foreach (ItemList m in lstitemQuery)
|
{
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
DataAccess.AddInParameter(cmd, "CN_S_TRAY_CODE", ComDbType.STRING, trayCode);
|
DataAccess.AddInParameter(cmd, "CN_S_TRAY_GRID", ComDbType.STRING, m.CN_S_TRAY_GRID);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_CODE", ComDbType.STRING, m.CN_S_ITEM_CODE);
|
DataAccess.AddInParameter(cmd, "CN_F_QUANTITY", ComDbType.DECIMAL, m.CN_F_QUANTITY);
|
cmds.Add(cmd);
|
}
|
SqlExecuteResult result = ExecuteCommands(cmds, null);
|
|
if (result.Success)
|
{
|
string sqlCear = "DELETE FROM TN_WM_B_TRAY_ITEM_REL where CN_F_PACKING_QTY<=0 ";
|
DbCommand cmdCear = DataAccess.GetSqlStringCommand(sqlCear);
|
result = ExecuteCommand(cmdCear, null);
|
}
|
return result;
|
}
|
#endregion
|
|
#region 根据条件获取数量
|
public List<TN_WM_B_TRAY_ITEM_RELEntity> GetItem(TN_WM_B_TRAY_ITEM_RELEntity entity)
|
{
|
StringBuilder sb = new StringBuilder("select CN_GUID,CN_F_PACKING_QTY from TN_WM_B_TRAY_ITEM_REL where 1=1 ");
|
if (!string.IsNullOrEmpty(entity.CN_S_TRAY_CODE))
|
{
|
sb.Append(" and CN_S_TRAY_CODE='" + entity.CN_S_TRAY_CODE + "'");
|
}
|
if (!string.IsNullOrEmpty(entity.CN_S_TRAY_GRID))
|
{
|
sb.Append(" and CN_S_TRAY_GRID='" + entity.CN_S_TRAY_GRID + "'");
|
}
|
if (!string.IsNullOrEmpty(entity.CN_S_FROM_NO))
|
{
|
sb.Append(" and CN_S_FROM_NO='" + entity.CN_S_FROM_NO + "'");
|
}
|
if (entity.CN_N_ROW_NO != 0)
|
{
|
sb.Append(" and CN_N_ROW_NO='" + entity.CN_N_ROW_NO + "'");
|
}
|
if (!string.IsNullOrEmpty(entity.CN_S_ITEM_CODE))
|
{
|
sb.Append(" and CN_S_ITEM_CODE='" + entity.CN_S_ITEM_CODE + "'");
|
}
|
if (!string.IsNullOrEmpty(entity.CN_S_PACKING_CODE))
|
{
|
sb.Append(" and CN_S_PACKING_CODE='" + entity.CN_S_PACKING_CODE + "'");
|
}
|
sb.Append(" order by CN_S_PACKING_CODE,CN_T_CREATE");
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sb.ToString());
|
List<TN_WM_B_TRAY_ITEM_RELEntity> list = DataAccessExtensive.ExecuteListEntity<TN_WM_B_TRAY_ITEM_RELEntity>(this.DataAccess, cmd, SetQty);
|
return list;
|
}
|
|
private void SetQty(TN_WM_B_TRAY_ITEM_RELEntity entity, IDataReader reader)
|
{
|
entity.CN_GUID = reader["CN_GUID"].ToString();
|
entity.CN_F_QUANTITY = Convert.ToDecimal(reader["CN_F_PACKING_QTY"].ToString());
|
}
|
#endregion
|
|
#region 根据条件获取码盘列表
|
/// <summary>
|
/// 获取实体
|
/// </summary>
|
/// <param name="sqlwhere"></param>
|
/// <returns></returns>
|
public DataTable Getlist(string sqlwhere, DbTransaction trans)
|
{
|
string sql = "select * from TN_WM_B_TRAY_ITEM_REL where 1=1 {0}";
|
sql = string.Format(sql, sqlwhere);
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql.ToString());
|
DataTable dt = DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd, trans);
|
return dt;
|
}
|
|
/// <summary>
|
/// 根据包装编码（产品唯一码）获取码盘列表
|
/// </summary>
|
/// <param name="packingCode">包装编码</param>
|
/// <returns></returns>
|
public List<TN_WM_B_TRAY_ITEM_RELEntity> GetTrayItemList(string packingCode)
|
{
|
List<TN_WM_B_TRAY_ITEM_RELEntity> list = new List<TN_WM_B_TRAY_ITEM_RELEntity>();
|
string sql = "select * from TN_WM_B_TRAY_ITEM_REL WHERE 1=1 ";
|
|
if (!string.IsNullOrEmpty(packingCode))
|
{
|
sql = sql + " AND CN_S_PACKING_CODE= '" + packingCode + "'";
|
}
|
else
|
{
|
return list;
|
}
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
try
|
{
|
//获得列表
|
list = DataAccessExtensive.ExecuteListEntity<TN_WM_B_TRAY_ITEM_RELEntity>(this.DataAccess, cmd, SetRelEntity);
|
return list;
|
}
|
catch (Exception)
|
{
|
|
throw;
|
}
|
}
|
#endregion
|
|
#region 产品上架时更新码盘表
|
/// <summary>
|
/// 产品上架时
|
/// </summary>
|
/// <param name="lstQuery"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public SqlExecuteResult CheckCodedDisc(List<string> sqlList, DbTransaction trans)
|
{
|
|
string sqlupdate = "DELETE TN_WM_B_TRAY_ITEM_REL WHERE CN_F_PACKING_QTY<=0";
|
sqlList.Add(sqlupdate);
|
|
List<DbCommand> cmds = new List<DbCommand>();
|
for (int i = 0; i < sqlList.Count; i++)
|
{
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sqlList[i]);
|
cmds.Add(cmd);
|
}
|
try
|
{
|
SqlExecuteResult sr = ExecuteCommands(cmds, trans);
|
return sr;
|
}
|
catch (Exception)
|
{
|
|
throw;
|
}
|
}
|
|
|
|
#endregion
|
|
#region 删除
|
/// <summary>
|
/// 删除
|
/// </summary>
|
/// <param name="sqlWhere"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public SqlExecuteResult DeleteItemRel(string sqlWhere, DbTransaction trans)
|
{
|
string strSql = "DELETE FROM TN_WM_B_TRAY_ITEM_REL where 1=1 {0} ";
|
strSql = string.Format(strSql, sqlWhere);
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
return ExecuteCommand(cmd, trans);
|
}
|
#endregion
|
|
#region 更新
|
/// <summary>
|
/// 更新数量
|
/// </summary>
|
/// <param name="sqlWhere"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public SqlExecuteResult UpdateItemRel(string sqlWhere, int CN_F_PACKING_QTY, DbTransaction trans)
|
{
|
string strSql = "UPDATE TN_WM_B_TRAY_ITEM_REL SET CN_F_PACKING_QTY={0} WHERE 1=1 {1} ";
|
strSql = string.Format(strSql, CN_F_PACKING_QTY, sqlWhere);
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
return ExecuteCommand(cmd, trans);
|
}
|
|
/// <summary>
|
/// 将4个小时之前的物料的状态从不合格改为合格
|
/// </summary>
|
/// <param name="sqlWhere"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public SqlExecuteResult UpdateItemRelState(DbTransaction trans)
|
{
|
string strSql = "update dbo.tn_wm_b_tray_item_rel set CN_S_ITEM_STATE = '合格' where CN_S_ITEM_STATE = '不合格' and CN_T_PRODUCTION <= DATEADD(hh,-4,getdate()) ";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
return ExecuteCommand(cmd, trans);
|
}
|
|
|
|
/// <summary>
|
/// 更新是否在库
|
/// </summary>
|
/// <param name="trayCodeList"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public SqlExecuteResult UpdateIsIn_Y(List<string> trayCodeList, DbTransaction trans)
|
{
|
string trayCodeStr = "";
|
if (trayCodeList.Count > 0)
|
{
|
for (int i = 0; i < trayCodeList.Count; i++)
|
{
|
trayCodeStr = trayCodeStr + "'" + trayCodeList[i] + "',";
|
}
|
if (!string.IsNullOrEmpty(trayCodeStr))
|
{
|
trayCodeStr = trayCodeStr.Substring(0, trayCodeStr.Length - 1);
|
}
|
}
|
string strSql = "UPDATE TN_WM_B_TRAY_ITEM_REL SET CN_C_ISIN='Y' WHERE CN_C_ISIN='N' and CN_S_TRAY_CODE in(" + trayCodeStr + ")";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
return ExecuteCommand(cmd, trans);
|
}
|
#endregion
|
|
#region 根据托盘获取
|
/// <summary>
|
/// 根据托盘获取
|
/// </summary>
|
/// <param name="trayCodeList"></param>
|
/// <returns></returns>
|
public List<TN_WM_B_TRAY_ITEM_RELEntity> GetListByTrays(List<string> trayCodeList)
|
{
|
string trayCodes = string.Join("','", trayCodeList);
|
|
string sql = @"SELECT * FROM TN_WM_B_TRAY_ITEM_REL WHERE 1=1 ";
|
if (!string.IsNullOrEmpty(trayCodes))
|
{
|
sql += " AND CN_S_TRAY_CODE IN('" + trayCodes + "') ";
|
}
|
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
return DataAccessExtensive.ExecuteListEntity<TN_WM_B_TRAY_ITEM_RELEntity>(this.DataAccess, cmd, (e, r) =>
|
{
|
SetEntityUti(e, "CN_S_TRAY_CODE", "CN_S_TRAY_CODE", r);
|
SetEntityUti(e, "CN_S_OP_FROM", "CN_S_OP_FROM", r);
|
SetEntityUti(e, "CN_S_FROM_NO", "CN_S_FROM_NO", r);
|
SetEntityUti(e, "CN_N_ROW_NO", "CN_N_ROW_NO", r);
|
SetEntityUti(e, "CN_S_OWNER", "CN_S_OWNER", r);
|
SetEntityUti(e, "CN_S_PRODUCTION_BATCH", "CN_S_PRODUCTION_BATCH", r);
|
SetEntityUti(e, "CN_S_ITEM_CODE", "CN_S_ITEM_CODE", r);
|
SetEntityUti(e, "CN_S_ITEM_NAME", "CN_S_ITEM_NAME", r);
|
SetEntityUti(e, "CN_S_ITEM_STATE", "CN_S_ITEM_STATE", r);
|
SetEntityUti(e, "CN_S_LOT_NO", "CN_S_LOT_NO", r);
|
SetEntityUti(e, "CN_F_QUANTITY", "CN_F_PACKING_QTY", r);
|
SetEntityUti(e, "CN_S_PRODUCTION_BATCH", "CN_S_PRODUCTION_BATCH", r);
|
});
|
}
|
#endregion
|
|
|
public SqlExecuteResult Clear(string trayCode, DbTransaction trans)
|
{
|
string strSql = "DELETE FROM TN_WM_B_TRAY_ITEM_REL where CN_S_TRAY_CODE=:CN_S_TRAY_CODE ";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
DataAccess.AddInParameter(cmd, "CN_S_TRAY_CODE", ComDbType.STRING, trayCode);
|
return ExecuteCommand(cmd, trans);
|
}
|
|
#region 托盘减重量
|
/// <summary>
|
/// 托盘减重量
|
/// </summary>
|
/// <param name="trayCode"></param>
|
/// <param name="weight"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public SqlExecuteResult ReduceWeight(string trayCode, decimal weight, DbTransaction trans)
|
{
|
string sql = @"UPDATE TN_WM_TRAY_INFO SET CN_F_WEIGHT=CN_F_WEIGHT-" + weight + " WHERE CN_S_TRAY_CODE=:CN_S_TRAY_CODE";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
DataAccess.AddInParameter(cmd, "CN_S_TRAY_CODE", ComDbType.STRING, trayCode);
|
return ExecuteCommand(cmd, trans);
|
}
|
#endregion
|
|
#region 根据货位获取托盘物料关联
|
/// <summary>
|
/// 根据货位获取托盘物料关联
|
/// </summary>
|
/// <param name="location"></param>
|
/// <returns></returns>
|
public List<TN_WM_B_TRAY_ITEM_RELEntity> GetTrayItemByLocation(string location)
|
{
|
string sql = @" SELECT * FROM TN_WM_B_TRAY_ITEM_REL WHERE CN_S_TRAY_CODE
|
IN( SELECT CN_S_TRAY_CODE FROM TN_WM_B_TRAY_LOCATION WHERE CN_S_LOCATION_CODE=:CN_S_LOCATION_CODE)";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
DataAccess.AddInParameter(cmd, "CN_S_LOCATION_CODE", ComDbType.STRING, location);
|
return DataAccessExtensive.ExecuteListEntity<TN_WM_B_TRAY_ITEM_RELEntity>(this.DataAccess, cmd, (e, r) =>
|
{
|
SetEntityUti(e, "CN_GUID", "CN_GUID", r);
|
SetEntityUti(e, "CN_S_TRAY_CODE", "CN_S_TRAY_CODE", r);
|
SetEntityUti(e, "CN_S_OP_FROM", "CN_S_OP_FROM", r);
|
SetEntityUti(e, "CN_S_FROM_NO", "CN_S_FROM_NO", r);
|
SetEntityUti(e, "CN_N_ROW_NO", "CN_N_ROW_NO", r);
|
SetEntityUti(e, "CN_S_OWNER", "CN_S_OWNER", r);
|
SetEntityUti(e, "CN_S_ITEM_CODE", "CN_S_ITEM_CODE", r);
|
SetEntityUti(e, "CN_S_ITEM_NAME", "CN_S_ITEM_NAME", r);
|
SetEntityUti(e, "CN_S_ITEM_STATE", "CN_S_ITEM_STATE", r);
|
SetEntityUti(e, "CN_S_LOT_NO", "CN_S_LOT_NO", r);
|
SetEntityUti(e, "CN_F_PACKING_QTY", "CN_F_PACKING_QTY", r);
|
SetEntityUti(e, "CN_S_PACKING_CODE", "CN_S_PACKING_CODE", r);
|
});
|
}
|
#endregion
|
|
|
#region 根据货位获取托盘物料关联
|
/// <summary>
|
/// 根据货位获取托盘物料关联
|
/// </summary>
|
/// <param name="location"></param>
|
/// <returns></returns>
|
public List<TN_WM_B_TRAY_ITEM_RELEntity> GetTrayItemByLocation(List<string> location)
|
{
|
string locationCode = string.Join("','", location);
|
string sql = @" SELECT * FROM TN_WM_B_TRAY_ITEM_REL WHERE CN_S_TRAY_CODE
|
IN( SELECT CN_S_TRAY_CODE FROM TN_WM_B_TRAY_LOCATION WHERE CN_S_LOCATION_CODE IN ('" + locationCode + "'))";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
return DataAccessExtensive.ExecuteListEntity<TN_WM_B_TRAY_ITEM_RELEntity>(this.DataAccess, cmd, (e, r) =>
|
{
|
SetEntityUti(e, "CN_GUID", "CN_GUID", r);
|
SetEntityUti(e, "CN_S_TRAY_CODE", "CN_S_TRAY_CODE", r);
|
SetEntityUti(e, "CN_S_OP_FROM", "CN_S_OP_FROM", r);
|
SetEntityUti(e, "CN_S_FROM_NO", "CN_S_FROM_NO", r);
|
SetEntityUti(e, "CN_N_ROW_NO", "CN_N_ROW_NO", r);
|
SetEntityUti(e, "CN_S_OWNER", "CN_S_OWNER", r);
|
SetEntityUti(e, "CN_S_ITEM_CODE", "CN_S_ITEM_CODE", r);
|
SetEntityUti(e, "CN_S_ITEM_NAME", "CN_S_ITEM_NAME", r);
|
SetEntityUti(e, "CN_S_ITEM_STATE", "CN_S_ITEM_STATE", r);
|
SetEntityUti(e, "CN_S_LOT_NO", "CN_S_LOT_NO", r);
|
SetEntityUti(e, "CN_F_PACKING_QTY", "CN_F_PACKING_QTY", r);
|
SetEntityUti(e, "CN_S_PACKING_CODE", "CN_S_PACKING_CODE", r);
|
});
|
}
|
#endregion
|
|
#region 更新小件托盘物料关联
|
/// <summary>
|
/// 更新小件托盘物料关联
|
/// </summary>
|
/// <param name="trayItemList"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public SqlExecuteResult SetPickTrayItem(List<TN_WM_B_TRAY_ITEM_RELEntity> trayItemList, DbTransaction trans)
|
{
|
SqlExecuteResult result = new SqlExecuteResult();
|
foreach (var item in trayItemList)
|
{
|
string sql = string.Empty;
|
if (item.CN_F_PACKING_QTY > 0)
|
{
|
sql = "UPDATE TN_WM_B_TRAY_ITEM_REL SET CN_F_PACKING_QTY=" + item.CN_F_PACKING_QTY + " WHERE CN_GUID=:CN_GUID";
|
}
|
else
|
{
|
sql = "DELETE FROM TN_WM_B_TRAY_ITEM_REL WHERE CN_GUID=:CN_GUID";
|
}
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
DataAccess.AddInParameter(cmd, "CN_GUID", ComDbType.STRING, item.CN_GUID);
|
result = ExecuteCommand(cmd, trans);
|
if (!result.Success)
|
return result;
|
}
|
return result;
|
}
|
#endregion
|
|
|
|
#region 查询托盘与物料关联表
|
/// <summary>
|
/// 查询托盘与物料关联表
|
/// </summary>
|
/// <param name="sqlwehre">查询列表</param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/08/23</history>
|
public List<TN_WM_B_TRAY_ITEM_RELEntity> GetItemRelList(string sqlwehre)
|
{
|
List<TN_WM_B_TRAY_ITEM_RELEntity> list = new List<TN_WM_B_TRAY_ITEM_RELEntity>();
|
string sql = @" select * from TN_WM_B_TRAY_ITEM_REL {0}";
|
sql = string.Format(sql, sqlwehre);
|
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
try
|
{
|
//获得列表
|
list = DataAccessExtensive.ExecuteListEntity<TN_WM_B_TRAY_ITEM_RELEntity>(this.DataAccess, cmd, SetItemEntity);
|
return list;
|
}
|
catch (Exception)
|
{
|
|
throw;
|
}
|
}
|
#endregion
|
/// <summary>
|
/// 设置实体的各列
|
/// </summary>
|
private void SetItemEntity(TN_WM_B_TRAY_ITEM_RELEntity entity, IDataReader reader)
|
{
|
SetEntityUti(entity, "CN_S_TRAY_CODE", "CN_S_TRAY_CODE", reader);
|
SetEntityUti(entity, "CN_S_TRAY_TYPE", "CN_S_TRAY_TYPE", reader);
|
SetEntityUti(entity, "CN_S_TRAY_GRID", "CN_S_TRAY_GRID", reader);
|
SetEntityUti(entity, "CN_S_LOT_NO", "CN_S_LOT_NO", reader);
|
SetEntityUti(entity, "CN_S_SERIAL_NO", "CN_S_SERIAL_NO", 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_OWNER", "CN_S_OWNER", reader);
|
SetEntityUti(entity, "CN_S_ITEM_STATE", "CN_S_ITEM_STATE", reader);
|
SetEntityUti(entity, "CN_F_QUANTITY", "CN_F_PACKING_QTY", reader);
|
SetEntityUti(entity, "CN_S_FROM_NO", "CN_S_FROM_NO", reader);
|
SetEntityUti(entity, "CN_N_ROW_NO", "CN_N_ROW_NO", 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_VENDOR_NO", "CN_S_VENDOR_NO", reader);
|
SetEntityUti(entity, "CN_S_VENDOR_NAME", "CN_S_VENDOR_NAME", reader);
|
SetEntityUti(entity, "CN_S_PRODUCTION_BATCH", "CN_S_PRODUCTION_BATCH", reader);
|
}
|
|
#region 查询数据
|
/// <summary>
|
/// 查询数据
|
/// </summary>
|
/// <param name="sqlwhere"></param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/08/30</history>
|
public DataTable GetDataTableTrayitem(string sqlwhere)
|
{
|
string sql = "select * from dbo.tn_wm_b_tray_item_rel {0}";
|
sql = string.Format(sql, sqlwhere);
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
return DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
|
}
|
#endregion
|
|
public SqlExecuteResult DeleteTrayItem(string TrayCode, DbTransaction trans)
|
{
|
string strSql = "DELETE FROM TN_WM_B_TRAY_ITEM_REL where CN_S_TRAY_CODE=:CN_S_TRAY_CODE";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
DataAccess.AddInParameter(cmd, "CN_S_TRAY_CODE", ComDbType.STRING, TrayCode);
|
return ExecuteCommand(cmd, trans);
|
}
|
|
#region 修改
|
/// <summary>
|
/// 修改
|
/// </summary>
|
/// <param name="list"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
///<history>[HanHe(lt)] CREATED 2018/09/25</history>
|
public SqlExecuteResult UpdateByCode(List<TN_WM_B_TRAY_ITEM_RELEntity> list, DbTransaction trans)
|
{
|
string sql = "UPDATE TN_WM_B_TRAY_ITEM_REL SET CN_S_TRAY_CODE=:CN_S_TRAY_CODE where CN_S_PACKING_CODE=:CN_S_PACKING_CODE";
|
List<DbCommand> cmdlist = new List<DbCommand>();
|
foreach (TN_WM_B_TRAY_ITEM_RELEntity entity in list)
|
{
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
DataAccess.AddInParameter(cmd, "CN_S_TRAY_CODE", ComDbType.STRING, entity.CN_S_TRAY_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_PACKING_CODE", ComDbType.STRING, entity.CN_S_PACKING_CODE);
|
cmdlist.Add(cmd);
|
}
|
return ExecuteCommands(cmdlist, trans);
|
}
|
#endregion
|
|
|
public DataTable GetOldCodeDiscList(string trayCode)
|
{
|
DataTable dt = new DataTable();
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append(" SELECT * FROM TN_WM_B_TRAY_ITEM_REL ");
|
if (!string.IsNullOrEmpty(trayCode))
|
{
|
strSql.Append(" WHERE CN_S_TRAY_CODE ='" + trayCode + "'");
|
}
|
else
|
{
|
return dt;
|
}
|
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
try
|
{
|
dt = DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
|
return dt;
|
}
|
catch (Exception ex)
|
{
|
return dt;
|
}
|
}
|
|
#region 根据托盘更新物料长度
|
/// <summary>
|
/// 根据托盘更新物料长度
|
/// </summary>
|
/// <param name="trayCode"></param>
|
/// <param name="itemQty"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public SqlExecuteResult UpdateItemQtyByTrayCode(string trayCode, string itemQty, DbTransaction trans)
|
{
|
string sql = @"UPDATE TN_WM_B_TRAY_ITEM_REL SET CN_F_PACKING_QTY=" + itemQty + " WHERE CN_S_TRAY_CODE='" + trayCode + "'";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
return ExecuteCommand(cmd, trans);
|
}
|
#endregion
|
|
#region 根据托盘更新托盘重量
|
/// <summary>
|
/// 根据托盘更新托盘重量
|
/// </summary>
|
/// <param name="trayCode"></param>
|
/// <param name="weight"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public SqlExecuteResult UpdateTrayWeight(string trayCode, string weight, DbTransaction trans)
|
{
|
string sql = @"UPDATE TN_WM_B_TRAY_ITEM_REL SET CN_S_EXT1=" + weight + " WHERE CN_S_TRAY_CODE='" + trayCode + "'";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
return ExecuteCommand(cmd, trans);
|
}
|
#endregion
|
}
|
}
|
