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 GetNeedDoubleTray(string areaCode,string customerName, string power, string currentLevel, string level, string color, string poType) { StringBuilder sbStr = new StringBuilder(); sbStr.Append("SELECT a.* 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 + "' "); } Log.Detail("GetNeedDoubleTray", sbStr.ToString()); return ExecuteQuery(sbStr.ToString()); } #endregion #region 托盘产品关联 /// /// 托盘产品关联 /// /// 数据实体 /// 事务 /// /// [HanHe(xdl)] CREATED 2018/01/31 public SqlExecuteResult AddItemTray(List 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 cmds = new List(); 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 获取已码托盘 /// /// 获取已码托盘 /// /// /// public List GetAlreadyCodedSxcc(string trayCode) { List list = new List(); 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(this.DataAccess, cmd, SetRelEntitySxcc); return list; } catch (Exception ex) { throw ex; } } public List GetAlreadyCodedSum(dynamic obj) { List list = new List(); 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(this.DataAccess, cmd, SetRelSumEntity); return list; } catch (Exception ex) { throw ex; } } /// /// 设置实体的各列 /// 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); } /// /// 设置实体的各列 /// 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); } /// /// 设置实体的各列 /// 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 根据条件获取实体 /// /// 根据条件获取实体 /// /// /// /// /// [HanHe(gp)] CREATED 2018/03/02 public List GetItemRelModel(string Where) { string sql = @" select * from TN_WM_B_TRAY_ITEM_REL " + Where; DbCommand cmd = DataAccess.GetSqlStringCommand(sql); //获得列表 List list = DataAccessExtensive.ExecuteListEntity(this.DataAccess, cmd, SetMastItemRelEntity); return list; } /// /// 设置实体的各列 /// 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 判断数据库是否存在某个条件下的数据 /// /// 判断数据库是否存在某个条件下的数据 /// /// 条件实体 /// 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 获取该库区中托盘最后一个入库的时间及对应的货位 /// /// 判断数据库是否存在某个条件下的数据 /// /// 条件实体 /// 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 获取该库区中托盘最早一个入库的时间及对应的货位 /// /// 判断数据库是否存在某个条件下的数据 /// /// 条件实体 /// 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 更新码盘数据 /// /// 更新码盘数据 /// /// 需要更新的码盘数据 /// /// public SqlExecuteResult EditCodedDisc(List 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 cmds = new List(); 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 拣货时，解绑物料与托盘的关联 /// /// 拣货时，解绑物料与托盘的关联 /// /// /// /// /// [Hanhe(DBS)] created 2018-4-10 public SqlExecuteResult Unbundling(List entitys, DbTransaction trans) { List cmds = new List(); 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 cmds = new List(); // 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 isExistCodedDisc(string packingCode) { List list = new List(); 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(this.DataAccess, cmd, SetRelEntity); return list; } catch (Exception) { throw; } } #endregion #region 根据条件获取实体 /// /// 根据条件获取实体 /// /// /// /// /// [HanHe(gp)] CREATED 2018/03/02 public List 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 list = DataAccessExtensive.ExecuteListEntity(this.DataAccess, cmd, ItemRelSummaryListEntity); return list; } catch (Exception) { throw; } } /// /// 设置实体的各列 /// 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 判断托盘内是否有物料信息 /// /// 根据条件获取实体 /// /// /// /// /// [HanHe(gp)] CREATED 2018/03/02 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 判断货位内是否有物料信息 /// /// 根据条件获取实体 /// /// /// /// /// [HanHe(gp)] CREATED 2018/03/02 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 根据条件获取托盘内产品类型 /// /// 根据条件获取实体 /// /// /// /// /// [HanHe(gp)] CREATED 2018/03/02 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 根据条件获取托盘内产品类型 /// /// 根据条件获取实体 /// /// /// /// /// [HanHe(gp)] CREATED 2018/03/02 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 根据托盘编码和单据编号 删除托盘产品关联 /// /// 根据托盘编码和单据编号 删除托盘产品关联 /// /// 托盘编码 /// 业务单据 /// 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 cmds = new List(); 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; } /// /// 根据托盘编码和PackingCode 删除托盘产品关联 /// /// 托盘编码 /// PackingCode /// public SqlExecuteResult DeleteByPackingCode(string trayCode, List 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 cmds = new List(); 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; } /// /// PDA物料移除 /// /// /// /// public SqlExecuteResult DeleteByitemCode(string trayCode, List 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 cmds = new List(); 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 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 list = DataAccessExtensive.ExecuteListEntity(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 根据条件获取码盘列表 /// /// 获取实体 /// /// /// 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; } /// /// 根据包装编码（产品唯一码）获取码盘列表 /// /// 包装编码 /// public List GetTrayItemList(string packingCode) { List list = new List(); 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(this.DataAccess, cmd, SetRelEntity); return list; } catch (Exception) { throw; } } #endregion #region 产品上架时更新码盘表 /// /// 产品上架时 /// /// /// /// public SqlExecuteResult CheckCodedDisc(List sqlList, DbTransaction trans) { string sqlupdate = "DELETE TN_WM_B_TRAY_ITEM_REL WHERE CN_F_PACKING_QTY<=0"; sqlList.Add(sqlupdate); List cmds = new List(); 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 删除 /// /// 删除 /// /// /// /// 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 更新 /// /// 更新数量 /// /// /// /// 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); } /// /// 将4个小时之前的物料的状态从不合格改为合格 /// /// /// /// 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); } /// /// 更新是否在库 /// /// /// /// public SqlExecuteResult UpdateIsIn_Y(List 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 根据托盘获取 /// /// 根据托盘获取 /// /// /// public List GetListByTrays(List 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(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 托盘减重量 /// /// 托盘减重量 /// /// /// /// /// 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 根据货位获取托盘物料关联 /// /// 根据货位获取托盘物料关联 /// /// /// public List 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(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 根据货位获取托盘物料关联 /// /// 根据货位获取托盘物料关联 /// /// /// public List GetTrayItemByLocation(List 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(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 更新小件托盘物料关联 /// /// 更新小件托盘物料关联 /// /// /// /// public SqlExecuteResult SetPickTrayItem(List 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 查询托盘与物料关联表 /// /// 查询托盘与物料关联表 /// /// 查询列表 /// /// [HanHe(lt)] CREATED 2018/08/23 public List GetItemRelList(string sqlwehre) { List list = new List(); 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(this.DataAccess, cmd, SetItemEntity); return list; } catch (Exception) { throw; } } #endregion /// /// 设置实体的各列 /// 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 查询数据 /// /// 查询数据 /// /// /// /// [HanHe(lt)] CREATED 2018/08/30 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 修改 /// /// 修改 /// /// /// /// ///[HanHe(lt)] CREATED 2018/09/25 public SqlExecuteResult UpdateByCode(List 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 cmdlist = new List(); 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 根据托盘更新物料长度 /// /// 根据托盘更新物料长度 /// /// /// /// /// 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 根据托盘更新托盘重量 /// /// 根据托盘更新托盘重量 /// /// /// /// /// 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 } }