using HH.WMS.Common; using HH.WMS.Common.Algorithm.Out; using HH.WMS.Common.External; using HH.WMS.DAL.Basic; using HH.WMS.Entitys; using HH.WMS.Entitys.Basic; using HH.WMS.Entitys.Common; using HH.WMS.Entitys.Entitys; using System; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.Common; using System.Linq; using System.Text; using System.Threading.Tasks; namespace HH.WMS.DAL.InStock { public class TN_WM_B_TRAY_ITEM_RELDAL : DapperBaseDAL { public string DataType = ConfigurationManager.ConnectionStrings["DataType"].ToString(); #region 货位内托盘本物料汇总 /// /// 货位内托盘本物料汇总 /// /// 物料编码 /// 条件 /// /// [HANHE(XDL)] CREATED BY 2018-11-16 public TN_WM_B_TRAY_ITEM_MSTEntity GetInStockQty(string itemCode, string strWhere) { TN_WM_B_TRAY_ITEM_MSTEntity areaQtyEntity = new TN_WM_B_TRAY_ITEM_MSTEntity(); string str1 = ""; string str2 = ""; switch (DataType) { case "MSSQL": str1 = " ISNULL(SUM(CN_F_QUANTITY),0) CN_F_QUANTITY "; str2 = " AND ISNULL(TL.CN_S_LOCATION_CODE,'')!='' "; break; case "MYSQL": str1 = " IFNULL(SUM(CN_F_QUANTITY),0) CN_F_QUANTITY "; str2 = " AND IFNULL(TL.CN_S_LOCATION_CODE,'')!='' "; break; case "ORACLE": str1 = " IFNULL(SUM(CN_F_QUANTITY),0) CN_F_QUANTITY "; str2 = " AND IFNULL(TL.CN_S_LOCATION_CODE,'')!='' "; break; default: break; } StringBuilder sbStr = new StringBuilder(); sbStr.Append("SELECT "); sbStr.Append(str1); sbStr.Append(" FROM TN_WM_B_TRAY_ITEM_MST TIR LEFT JOIN TN_WM_B_TRAY_LOCATION TL ON TIR.CN_S_TRAY_CODE=TL.CN_S_TRAY_CODE WHERE TIR.CN_S_ITEM_CODE='" + itemCode + "' "); sbStr.Append(str2); sbStr.Append(" AND TIR.CN_C_ISIN='Y' "); if (!string.IsNullOrEmpty(strWhere)) { sbStr.Append(strWhere); } return ExecuteSingleEntity(sbStr.ToString()); } #endregion #region 根据货位获取托盘信息 /// /// 根据货位获取托盘信息 /// /// /// /// /// [HANHE(lt)] CREATED BY 2018-12-5 public List GetTrayLocation(string sqlwhere) { string sql = @"SELECT * FROM dbo.tn_wm_b_tray_location AS a LEFT JOIN TN_WM_B_TRAY_ITEM_MST AS b ON a.CN_S_TRAY_CODE=b.CN_S_TRAY_CODE {0}"; sql = string.Format(sql, sqlwhere); return ExecuteQuery(sql, null); } public List GetTrayLocationAndNullLocation(string sqlwhere) { string sql = @"SELECT b.CN_S_TRAY_CODE,a.CN_S_STOCK_CODE,a.CN_S_AREA_CODE AS CN_S_STOCK_AREA,a.CN_S_LOCATION_CODE,CN_N_INDEX,c.* FROM dbo.tn_wm_b_location_ext AS a LEFT JOIN dbo.tn_wm_b_tray_location AS b ON a.CN_S_LOCATION_CODE=b.CN_S_LOCATION_CODE LEFT JOIN dbo.TN_WM_B_TRAY_ITEM_MST AS c ON b.CN_S_TRAY_CODE=c.CN_S_TRAY_CODE LEFT JOIN (SELECT TOP 1 CN_S_ITEM_CODE,CN_F_PACKING_QTY,CN_S_PACKING_UNIT FROM dbo.TN_WM_B_UNIQUE_BARCODE WHERE CN_S_PARENT_CODE='' GROUP BY CN_S_ITEM_CODE,CN_F_PACKING_QTY,CN_S_PACKING_UNIT) AS d ON c.CN_S_ITEM_CODE=d.CN_S_ITEM_CODE {0}"; sql = string.Format(sql, sqlwhere); return ExecuteQuery(sql, null); } #endregion #region 获取主表 public List GetTrayItemMst(string sqlWhere) { string sql = " SELECT * FROM TN_WM_B_TRAY_ITEM_MST WHERE 1=1 " + sqlWhere; return ExecuteQuery(sql, null); } #endregion public List GetDtl(string sqlWhere) { string sql = " SELECT * FROM TN_WM_B_TRAY_ITEM_DTL WHERE 1=1 " + sqlWhere; return ExecuteQuery(sql, null); } public List GetDtlByUniques(string uniqueCodes) { uniqueCodes = uniqueCodes.Replace(",", "','"); string sql = " SELECT * FROM TN_WM_B_TRAY_ITEM_DTL WHERE CN_S_UNIQUE_CODE IN ('" + uniqueCodes + "') "; return ExecuteQuery(sql, null); } #region 通过托盘码和排除已在的料箱码之后改托盘码是否还有其他料箱码 /// /// 通过托盘码和排除已在的料箱码之后改托盘码是否还有其他料箱码 /// /// /// /// public List GetTrayUniqueCode(string trayCode, string UniqueCodes) { string UniqueCode = UniqueCodes.Substring(0, UniqueCodes.Length - 1); string sql = @" SELECT B.CN_S_UNIQUE_CODE FROM TN_WM_B_TRAY_ITEM_MST A,TN_WM_B_TRAY_ITEM_DTL B WHERE A.CN_GUID=B.CN_PARENT_GUID AND A.CN_S_TRAY_CODE='" + trayCode + @"' AND B.CN_S_UNIQUE_CODE NOT IN (" + UniqueCode + ")"; return ExecuteQuery(sql, null); } #endregion #region 获取托盘中物料的信息，按批次汇总 /// /// 获取托盘中物料的信息，按批次汇总 /// /// 托盘号 /// /// [Hanhe(DBS)] CREATED BY 2018/12/8 public DataTable GetTrayItem(string locationCode) { string sql = @"SELECT CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,SUM(B.CN_F_QUANTITY) CN_F_QUANTITY,A.CN_S_MEASURE_UNIT,B.CN_S_PRODUCTION_BATCH, B.CN_S_UNIQUE_CODE,C.CN_S_TRAY_CODE,D.CN_S_USE_STATE,B.CN_S_SERIAL_NO,B.CN_S_LOT_NO,B.CN_S_PRODUCTION_BATCH FROM tn_wm_b_tray_location C LEFT JOIN tn_wm_tray_info D ON C.CN_S_TRAY_CODE=D.CN_S_TRAY_CODE LEFT JOIN TN_WM_B_TRAY_ITEM_MST A ON A.CN_S_TRAY_CODE=C.CN_S_TRAY_CODE LEFT JOIN TN_WM_B_TRAY_ITEM_DTL B ON A.CN_GUID=B.CN_PARENT_GUID WHERE C.CN_S_LOCATION_CODE=@CN_S_LOCATION_CODE GROUP BY CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,B.CN_S_PRODUCTION_BATCH,CN_S_MEASURE_UNIT,B.CN_S_UNIQUE_CODE,C.CN_S_TRAY_CODE,D.CN_S_USE_STATE,B.CN_S_SERIAL_NO,B.CN_S_SERIAL_NO,B.CN_S_LOT_NO"; return ExecuteDataTable(sql, new { CN_S_LOCATION_CODE = locationCode }); } public DataTable GetTrayItemYS(string locationCode) { string sql = @"SELECT CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,SUM(B.CN_F_PACKING_QTY) CN_F_PACKING_QTY,A.CN_S_MEASURE_UNIT,B.CN_S_PRODUCTION_BATCH, B.CN_S_UNIQUE_CODE,C.CN_S_TRAY_CODE,D.CN_S_USE_STATE,B.CN_S_SERIAL_NO,B.CN_S_LOT_NO,B.CN_S_PRODUCTION_BATCH FROM tn_wm_b_tray_location C LEFT JOIN tn_wm_tray_info D ON C.CN_S_TRAY_CODE=D.CN_S_TRAY_CODE LEFT JOIN TN_WM_B_TRAY_ITEM_MST A ON A.CN_S_TRAY_CODE=C.CN_S_TRAY_CODE LEFT JOIN TN_WM_B_TRAY_ITEM_DTL B ON A.CN_GUID=B.CN_PARENT_GUID WHERE C.CN_S_LOCATION_CODE=@CN_S_LOCATION_CODE GROUP BY CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,B.CN_S_PRODUCTION_BATCH,CN_S_MEASURE_UNIT,B.CN_S_UNIQUE_CODE,C.CN_S_TRAY_CODE,D.CN_S_USE_STATE,B.CN_S_SERIAL_NO,B.CN_S_LOT_NO"; return ExecuteDataTable(sql, new { CN_S_LOCATION_CODE = locationCode }); } public DataTable GetTrayItemJx(string locationCode) { string sql = @"SELECT CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,SUM(B.CN_F_PACKING_QTY) CN_F_PACKING_QTY,A.CN_S_MEASURE_UNIT,B.CN_S_PRODUCTION_BATCH, B.CN_S_UNIQUE_CODE,C.CN_S_TRAY_CODE,D.CN_S_USE_STATE,B.CN_S_SERIAL_NO,B.CN_S_LOT_NO,B.CN_S_PRODUCTION_BATCH FROM tn_wm_b_tray_location C LEFT JOIN tn_wm_tray_info D ON C.CN_S_TRAY_CODE=D.CN_S_TRAY_CODE LEFT JOIN TN_WM_B_TRAY_ITEM_MST A ON A.CN_S_TRAY_CODE=C.CN_S_TRAY_CODE LEFT JOIN TN_WM_B_TRAY_ITEM_DTL B ON A.CN_GUID=B.CN_PARENT_GUID WHERE C.CN_S_LOCATION_CODE=@CN_S_LOCATION_CODE GROUP BY CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,B.CN_S_PRODUCTION_BATCH,CN_S_MEASURE_UNIT,B.CN_S_UNIQUE_CODE,C.CN_S_TRAY_CODE,D.CN_S_USE_STATE,B.CN_S_SERIAL_NO,B.CN_S_LOT_NO"; return ExecuteDataTable(sql, new { CN_S_LOCATION_CODE = locationCode }); } public DataTable GetTrayItemJxInLock(string locationCode) { string sql = @"SELECT CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,SUM(B.CN_F_PACKING_QTY) CN_F_PACKING_QTY,A.CN_S_MEASURE_UNIT,B.CN_S_PRODUCTION_BATCH, B.CN_S_UNIQUE_CODE,C.CN_S_TRAY_CODE,'' AS CN_S_USE_STATE,B.CN_S_SERIAL_NO,B.CN_S_LOT_NO,B.CN_S_PRODUCTION_BATCH FROM TN_WM_TASK C LEFT JOIN TN_WM_B_TRAY_ITEM_MST A ON A.CN_S_TRAY_CODE=C.CN_S_TRAY_CODE LEFT JOIN TN_WM_B_TRAY_ITEM_DTL B ON A.CN_GUID=B.CN_PARENT_GUID WHERE C.CN_S_END_BIT=@CN_S_LOCATION_CODE AND C.CN_S_STATE IN ('未执行','执行中','取货完成','卸货完成') GROUP BY CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,B.CN_S_PRODUCTION_BATCH,CN_S_MEASURE_UNIT,B.CN_S_UNIQUE_CODE,C.CN_S_TRAY_CODE,B.CN_S_SERIAL_NO,B.CN_S_LOT_NO"; return ExecuteDataTable(sql, new { CN_S_LOCATION_CODE = locationCode }); } public DataTable GetTrayItemJxOutLock(string locationCode) { string sql = @"SELECT CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,SUM(B.CN_F_PACKING_QTY) CN_F_PACKING_QTY,A.CN_S_MEASURE_UNIT,B.CN_S_PRODUCTION_BATCH, B.CN_S_UNIQUE_CODE,C.CN_S_TRAY_CODE,'' AS CN_S_USE_STATE,B.CN_S_SERIAL_NO,B.CN_S_LOT_NO,B.CN_S_PRODUCTION_BATCH FROM TN_WM_TASK C LEFT JOIN TN_WM_B_TRAY_ITEM_MST A ON A.CN_S_TRAY_CODE=C.CN_S_TRAY_CODE LEFT JOIN TN_WM_B_TRAY_ITEM_DTL B ON A.CN_GUID=B.CN_PARENT_GUID WHERE C.CN_S_START_BIT=@CN_S_LOCATION_CODE AND C.CN_S_STATE IN ('未执行','执行中','取货完成','卸货完成') GROUP BY CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,B.CN_S_PRODUCTION_BATCH,CN_S_MEASURE_UNIT,B.CN_S_UNIQUE_CODE,C.CN_S_TRAY_CODE,B.CN_S_SERIAL_NO,B.CN_S_LOT_NO"; return ExecuteDataTable(sql, new { CN_S_LOCATION_CODE = locationCode }); } #endregion #region 获取托盘中物料的信息，按批次汇总 隆基 /// /// 获取托盘中物料的信息，按批次汇总 /// /// 托盘号 /// /// [Hanhe(DBS)] CREATED BY 2018/12/8 public DataTable GetTrayItemLJ(string locationCode) { // string sql = @"select CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,sum(b.CN_F_QUANTITY) CN_F_QUANTITY,a.CN_S_MEASURE_UNIT,b.CN_S_PRODUCTION_BATCH,b.CN_S_UNIQUE_CODE,a.CN_S_TRAY_CODE,d.CN_S_USE_STATE // from TN_WM_B_TRAY_ITEM_MST a join // TN_WM_B_TRAY_ITEM_DTL b on a.CN_GUID=b.CN_PARENT_GUID // join tn_wm_b_tray_location c on a.CN_S_TRAY_CODE=c.CN_S_TRAY_CODE inner join tn_wm_tray_info d on a.CN_S_TRAY_CODE=d.CN_S_TRAY_CODE // where c.CN_S_LOCATION_CODE=@CN_S_LOCATION_CODE // group by CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,b.CN_S_PRODUCTION_BATCH,CN_S_MEASURE_UNIT,b.CN_S_UNIQUE_CODE,a.CN_S_TRAY_CODE,d.CN_S_USE_STATE "; // string sql = @"SELECT // CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,SUM(B.CN_F_QUANTITY) CN_F_QUANTITY,A.CN_S_MEASURE_UNIT,B.CN_S_PRODUCTION_BATCH, // B.CN_S_UNIQUE_CODE,C.CN_S_TRAY_CODE,D.CN_S_USE_STATE,B.CN_S_SERIAL_NO,B.CN_S_LOT_NO FROM tn_wm_b_tray_location C // INNER JOIN // tn_wm_tray_info D ON C.CN_S_TRAY_CODE=D.CN_S_TRAY_CODE // LEFT JOIN // TN_WM_B_TRAY_ITEM_MST A ON A.CN_S_TRAY_CODE=C.CN_S_TRAY_CODE // LEFT JOIN // TN_WM_B_TRAY_ITEM_DTL B ON A.CN_GUID=B.CN_PARENT_GUID // WHERE C.CN_S_LOCATION_CODE=@CN_S_LOCATION_CODE // GROUP BY // CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,B.CN_S_PRODUCTION_BATCH,CN_S_MEASURE_UNIT,B.CN_S_UNIQUE_CODE,C.CN_S_TRAY_CODE,D.CN_S_USE_STATE,B.CN_S_SERIAL_NO,B.CN_S_LOT_NO"; string sql = @" select CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,sum(b.CN_F_QUANTITY) CN_F_QUANTITY,a.CN_S_MEASURE_UNIT,b.CN_S_PRODUCTION_BATCH,b.CN_S_UNIQUE_CODE,a.CN_S_TRAY_CODE , '' as CN_S_USE_STATE,b.CN_S_SERIAL_NO,b.CN_S_LOT_NO from TN_WM_B_TRAY_ITEM_MST a join TN_WM_B_TRAY_ITEM_DTL b on a.CN_GUID=b.CN_PARENT_GUID join tn_wm_b_tray_location c on a.CN_S_TRAY_CODE=c.CN_S_TRAY_CODE where c.CN_S_LOCATION_CODE=@CN_S_LOCATION_CODE group by CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,b.CN_S_PRODUCTION_BATCH,CN_S_MEASURE_UNIT,b.CN_S_UNIQUE_CODE,a.CN_S_TRAY_CODE,CN_S_SERIAL_NO,CN_S_LOT_NO"; return ExecuteDataTable(sql, new { CN_S_LOCATION_CODE = locationCode }); } #endregion #region 获取托盘中物料的信息，按批次汇总 /// /// 获取托盘中物料的信息，按批次汇总 /// /// 托盘号 /// /// [Hanhe(DBS)] CREATED BY 2018/12/8 public DataTable GetTrayDetail(string trayCode) { string sql = @" select a.CN_S_ITEM_CODE,a.CN_S_ITEM_NAME,a.CN_S_MODEL,a.CN_S_ITEM_STATE,a.CN_S_OWNER,a.CN_S_FIGURE_NO,a.CN_S_MEASURE_UNIT,a.CN_S_IN_AREA_CODE,a.CN_S_MEASURE_UNIT,b.CN_S_LOT_NO,b.CN_S_UNIQUE_CODE,b.CN_T_PRODUCTION,b.CN_T_EXPIRATION,b.CN_S_SERIAL_NO,b.CN_F_PACKING_QTY,b.CN_S_VENDOR_NO,b.CN_S_VENDOR_NAME, b.CN_F_QUANTITY,b.CN_S_PRODUCTION_BATCH from TN_WM_B_TRAY_ITEM_MST a inner join TN_WM_B_TRAY_ITEM_DTL b on a.CN_GUID=b.CN_PARENT_GUID where a.CN_S_TRAY_CODE=@CN_S_TRAY_CODE "; return ExecuteDataTable(sql, new { CN_S_TRAY_CODE = trayCode }); } public DataTable GetTrayDetailManual(string trayCode,string itemCode,string lotNo) { string sql = @" select a.CN_S_ITEM_CODE,a.CN_S_ITEM_NAME,a.CN_S_MODEL,a.CN_S_ITEM_STATE,a.CN_S_OWNER,a.CN_S_FIGURE_NO,a.CN_S_MEASURE_UNIT,a.CN_S_IN_AREA_CODE,a.CN_S_MEASURE_UNIT,b.CN_S_LOT_NO,b.CN_S_UNIQUE_CODE,b.CN_T_PRODUCTION,b.CN_T_EXPIRATION,b.CN_S_SERIAL_NO,b.CN_F_PACKING_QTY,b.CN_S_VENDOR_NO,b.CN_S_VENDOR_NAME, b.CN_F_QUANTITY,b.CN_S_PRODUCTION_BATCH from TN_WM_B_TRAY_ITEM_MST a inner join TN_WM_B_TRAY_ITEM_DTL b on a.CN_GUID=b.CN_PARENT_GUID where a.CN_S_TRAY_CODE=@CN_S_TRAY_CODE and a.CN_S_ITEM_CODE=@CN_S_ITEM_CODE and b.CN_S_LOT_NO=@CN_S_LOT_NO "; return ExecuteDataTable(sql, new { CN_S_TRAY_CODE = trayCode, CN_S_ITEM_CODE = itemCode, CN_S_LOT_NO = lotNo }); } public DataTable GetTrayDetailCheck(string trayCode) { string sql = @" select a.CN_S_ITEM_CODE,a.CN_S_ITEM_NAME,a.CN_S_MODEL,a.CN_S_ITEM_STATE,a.CN_S_OWNER,b.CN_S_EXT1 AS CN_S_FIGURE_NO,a.CN_S_MEASURE_UNIT,a.CN_S_IN_AREA_CODE,a.CN_S_MEASURE_UNIT,b.CN_S_LOT_NO,b.CN_S_UNIQUE_CODE,b.CN_T_PRODUCTION,b.CN_T_EXPIRATION,b.CN_S_SERIAL_NO,b.CN_F_PACKING_QTY,b.CN_S_VENDOR_NO,b.CN_S_VENDOR_NAME, b.CN_F_QUANTITY,b.CN_S_PRODUCTION_BATCH from TN_WM_B_TRAY_ITEM_MST a inner join TN_WM_B_TRAY_ITEM_DTL b on a.CN_GUID=b.CN_PARENT_GUID where a.CN_S_TRAY_CODE=@CN_S_TRAY_CODE "; return ExecuteDataTable(sql, new { CN_S_TRAY_CODE = trayCode }); } #endregion #region 获取托盘中物料的信息，按物料汇总 /// /// 获取托盘中物料的信息，按批次汇总 /// /// 托盘号 /// /// [Hanhe(DBS)] CREATED BY 2018/12/8 public DataTable GetTrayItemDetail(string trayCode) { string sql = @" select CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,CN_S_OWNER,CN_S_MEASURE_UNIT,CN_F_QUANTITY from TN_WM_B_TRAY_ITEM_MST where CN_S_TRAY_CODE=@CN_S_TRAY_CODE "; return ExecuteDataTable(sql, new { CN_S_TRAY_CODE = trayCode }); } #endregion #region 获取托盘中物料的信息，按批次汇总 /// /// 获取托盘中物料的信息，按批次汇总 /// /// 托盘号 /// /// [Hanhe(DBS)] CREATED BY 2018/12/8 public DataTable GetTrayDetailYS(string trayCode) { string sql = @" select a.CN_S_ITEM_CODE,a.CN_S_ITEM_NAME,a.CN_S_MODEL,a.CN_S_ITEM_STATE,a.CN_S_OWNER,a.CN_S_FIGURE_NO,a.CN_S_MEASURE_UNIT,b.CN_S_LOT_NO,b.CN_S_UNIQUE_CODE,b.CN_T_PRODUCTION,b.CN_T_EXPIRATION,b.CN_S_SERIAL_NO,b.CN_S_VENDOR_NO,b.CN_S_VENDOR_NAME, b.CN_F_QUANTITY,b.CN_S_PRODUCTION_BATCH , c.CN_N_GOODUPLINE,b.CN_F_PACKING_QTY,b.CN_S_PACKING_UNIT from TN_WM_B_TRAY_ITEM_MST a inner join TN_WM_B_TRAY_ITEM_DTL b on a.CN_GUID=b.CN_PARENT_GUID inner join tn_wm_tray_info c on c.CN_S_TRAY_CODE = A.CN_S_TRAY_CODE where a.CN_S_TRAY_CODE=@CN_S_TRAY_CODE "; return ExecuteDataTable(sql, new { CN_S_TRAY_CODE = trayCode }); } public int GetGoodTypeInTray(string trayCode) { string sql = @" select COUNT(1) as GOOD_TYPE_NUM from TN_WM_B_TRAY_ITEM_MST a inner join TN_WM_B_TRAY_ITEM_DTL b on a.CN_GUID=b.CN_PARENT_GUID where a.CN_S_TRAY_CODE=@CN_S_TRAY_CODE group by a.CN_S_ITEM_CODE,b.CN_S_LOT_NO "; DataTable dt = ExecuteDataTable(sql, new { CN_S_TRAY_CODE = trayCode }); int goodType = 0; if(dt.Rows.Count >0) { goodType = dt.Rows.Count; } return goodType; } public DataTable GetTrayInfoYS(string trayCode) { string sql = @" select CN_N_GOODUPLINE from tn_wm_tray_info where CN_S_TRAY_CODE=@CN_S_TRAY_CODE "; return ExecuteDataTable(sql, new { CN_S_TRAY_CODE = trayCode }); } #endregion #region 获取托盘内物料及明细（多行） /// /// 获取托盘内物料及明细 /// /// 托盘编码 /// public List TrayItemMstAndDtl(string trayCode) { string sql = @"SELECT CN_S_TRAY_CODE,CN_S_TRAY_GRID,CN_S_OWNER,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_FIGURE_NO,CN_S_MODEL ,CN_S_ITEM_STATE,CN_S_MEASURE_UNIT,CN_S_LOT_NO,CN_S_SERIAL_NO,CN_S_VENDOR_NO,CN_S_VENDOR_NAME,CN_S_PACKING_UNIT, CN_F_PACKING_QTY,DTL.CN_F_QUANTITY,DTL.CN_S_PRODUCTION_BATCH FROM TN_WM_B_TRAY_ITEM_MST MST LEFT JOIN TN_WM_B_TRAY_ITEM_DTL DTL ON MST.CN_GUID=DTL.CN_PARENT_GUID WHERE MST.CN_S_TRAY_CODE='" + trayCode + "'"; return ExecuteQuery(sql); } #endregion #region 根据条件查询托盘数量 /// ///根据条件查询托盘数量 /// /// /// [Hanhe(lt)] CREATED BY 2019/1/17 public List GetTrayItemMstList(string sqlwhere) { string sql = @"SELECT * FROM TN_WM_B_TRAY_ITEM_MST AS a INNER JOIN tn_wm_b_tray_location AS b ON a.CN_S_TRAY_CODE=b.CN_S_TRAY_CODE {0}"; sql = string.Format(sql, sqlwhere); return ExecuteQuery(sql, null); } #endregion /// /// 根据托盘码获取主表 /// /// /// public List GetMstByTrayCode(string trayCode) { string sql = string.Format(@"SELECT * FROM TN_WM_B_TRAY_ITEM_MST WHERE CN_S_TRAY_CODE='{0}'", trayCode); return ExecuteQuery(sql, null); } /// /// 根据托盘码获取子表 /// /// /// public List GetDtlByTrayCode(string trayCode) { string sql = string.Format(@"SELECT B.* FROM TN_WM_B_TRAY_ITEM_MST A INNER JOIN TN_WM_B_TRAY_ITEM_DTL B ON A.CN_GUID=B.CN_PARENT_GUID WHERE A.CN_S_TRAY_CODE='{0}'", trayCode); return ExecuteQuery(sql, null); } public decimal GetWeightByTrayCode(string trayCode) { var trayDtls = GetDtlByTrayCode(trayCode); decimal weight = 0; if (trayDtls != null) { trayDtls.ForEach(e => { weight += Convert.ToDecimal(e.CN_S_SERIAL_NO); }); } return weight; } public decimal GetItemQtyByTrayCode(string trayCode, LogPara logPara = null) { if(logPara == null) logPara = LogType.LogPara("获取托盘数量"); decimal qty = 0; try { var trayMsts = DALCreator.Create>().GetList(new { CN_S_TRAY_CODE = trayCode }); if (trayMsts.Count() > 0) { foreach (var trayMst in trayMsts) { // var item = DALCreator.Create().GetItemEntity(trayMst.CN_S_ITEM_CODE); trayMst.TrayItemDtlList = DALCreator.Create>().GetList(new { CN_PARENT_GUID = trayMst.CN_GUID }); foreach (var trayDtl in trayMst.TrayItemDtlList) { qty += Convert.ToDecimal(trayDtl.CN_F_PACKING_QTY); } } } Log.Detail(logPara, "获取托盘数量为：" + qty); } catch (Exception ex) { Log.Detail(logPara, "获取出错，原因：" + ex.Message); } return qty; } public decimal GetQtyByTrayCode(string trayCode) { decimal qty = 0; var trayMsts = GetMstByTrayCode(trayCode); foreach (var trayMst in trayMsts) { qty += trayMst.CN_F_QUANTITY; } return qty; } #region 判断数据库是否存在某个条件下的数据 /// /// 判断数据库是否存在某个条件下的数据 /// /// 条件实体 /// public List CheckExists(TN_WM_B_TRAY_ITEM_MSTEntity entity) { StringBuilder strSql = new StringBuilder(); strSql.Append("select * from TN_WM_B_TRAY_ITEM_MST "); strSql.Append(" WHERE CN_S_TRAY_CODE='" + entity.CN_S_TRAY_CODE + "' AND CN_S_TRAY_GRID='" + entity.CN_S_TRAY_GRID + "' AND CN_S_OWNER='" + entity.CN_S_OWNER + "' AND CN_S_ITEM_CODE='" + entity.CN_S_ITEM_CODE +// "' AND CN_S_FIGURE_NO='" + entity.CN_S_FIGURE_NO + "' AND CN_S_ITEM_STATE='" + entity.CN_S_ITEM_STATE + "' "); return ExecuteQuery(strSql.ToString()); } public List CheckExistMsts(TN_WM_B_TRAY_ITEM_MSTEntity entity, string packingCode) { StringBuilder strSql = new StringBuilder(); strSql.Append("select * from TN_WM_B_TRAY_ITEM_MST "); strSql.Append(" WHERE CN_S_TRAY_CODE='" + entity.CN_S_TRAY_CODE + "' AND CN_S_TRAY_GRID='" + entity.CN_S_TRAY_GRID + "' AND CN_S_OWNER='" + entity.CN_S_OWNER + "' AND CN_S_ITEM_CODE='" + entity.CN_S_ITEM_CODE + //"' AND CN_S_FIGURE_NO='" + entity.CN_S_FIGURE_NO + "' AND CN_S_ITEM_STATE='" + entity.CN_S_ITEM_STATE + "' and CN_GUID in(select CN_PARENT_GUID from TN_WM_B_TRAY_ITEM_DTL where CN_S_UNIQUE_CODE='" + packingCode + "')"); return ExecuteQuery(strSql.ToString()); } #endregion /// /// 根据条件更新关联表数量 /// /// /// /// /// [HANHE(XDL)] CREATED BY 2018-11-17 public OperateResult UpdateByCondition(TN_WM_B_TRAY_ITEM_DTLEntity model, IDbTransaction trans) { OperateResult result = new OperateResult(); StringBuilder strSql = new StringBuilder(); strSql.Append(" UPDATE TN_WM_B_TRAY_ITEM_DTL SET CN_F_QUANTITY=CN_F_QUANTITY+@CN_F_QUANTITY "); strSql.Append(" WHERE CN_PARENT_GUID=@CN_PARENT_GUID AND CN_S_UNIQUE_CODE=@CN_S_UNIQUE_CODE "); strSql.Append(" AND CN_S_LOT_NO=@CN_S_LOT_NO AND CN_S_SERIAL_NO=@CN_S_SERIAL_NO AND CN_S_PACKING_UNIT=@CN_S_PACKING_UNIT "); strSql.Append(" AND CN_F_PACKING_QTY=@CN_F_PACKING_QTY AND CN_F_PURCHASE_PRICE=@CN_F_PURCHASE_PRICE "); strSql.Append(" AND CN_F_RETAIL_PRICE=@CN_F_RETAIL_PRICE AND CN_S_PRODUCTION_BATCH=@CN_S_PRODUCTION_BATCH "); result = ExecuteTranSql(strSql.ToString(), new { CN_F_QUANTITY = model.CN_F_QUANTITY, CN_PARENT_GUID = model.CN_PARENT_GUID, CN_S_UNIQUE_CODE = string.IsNullOrEmpty(model.CN_S_UNIQUE_CODE) ? "" : model.CN_S_UNIQUE_CODE, CN_S_LOT_NO = string.IsNullOrEmpty(model.CN_S_LOT_NO) ? "" : model.CN_S_LOT_NO, CN_S_SERIAL_NO = string.IsNullOrEmpty(model.CN_S_SERIAL_NO) ? "" : model.CN_S_SERIAL_NO, CN_S_PACKING_UNIT = string.IsNullOrEmpty(model.CN_S_PACKING_UNIT) ? "" : model.CN_S_PACKING_UNIT, CN_F_PACKING_QTY = model.CN_F_PACKING_QTY, CN_F_PURCHASE_PRICE = model.CN_F_PURCHASE_PRICE, CN_F_RETAIL_PRICE = model.CN_F_RETAIL_PRICE, CN_S_PRODUCTION_BATCH = string.IsNullOrEmpty(model.CN_S_PRODUCTION_BATCH) ? "" : model.CN_S_PRODUCTION_BATCH }, trans); return result; } /// /// 码盘修改数量,根据Guid /// /// /// public OperateResult UpdateMstQty(List TrayItemMstlst, IDbTransaction trans) { OperateResult result = new OperateResult(); foreach (var model in TrayItemMstlst) { StringBuilder strSql = new StringBuilder(); strSql.Append(" UPDATE TN_WM_B_TRAY_ITEM_MST SET CN_F_QUANTITY=ISNULL(CN_F_QUANTITY,0)+@CN_F_QUANTITY,CN_F_INQTY=ISNULL(CN_F_INQTY,0)+@CN_F_INQTY "); strSql.Append(" WHERE CN_GUID=@CN_GUID "); result = ExecuteTranSql(strSql.ToString(), new { CN_F_QUANTITY = model.CN_F_QUANTITY, CN_F_INQTY = model.CN_F_INQTY, CN_GUID = model.CN_GUID }, trans); if (result.Status == ResultStatus.Error) return result; } return result; } /// /// 码盘修改分配数量,托盘信息 /// /// /// public OperateResult UpdateMstAllocQty(List list, IDbTransaction trans) { OperateResult result = new OperateResult(); foreach (var model in list) { StringBuilder strSql = new StringBuilder(); strSql.Append(" UPDATE TN_WM_B_TRAY_ITEM_MST SET CN_F_ALLOC_QTY=ISNULL(CN_F_ALLOC_QTY,0)+ " + model.CN_F_ALLOC_QTY + "" + model.sqlwhere + ""); result = ExecuteTranSql(strSql.ToString(), null, trans); if (result.Status == ResultStatus.Error) return result; } return result; } #region 修改货位 /// /// 修改货位 /// /// 起点货位 /// 终点货位 /// /// [HANHE(lt)] CREATED BY 2018-12-5 public SqlExecuteResult UpdateLocation(string startLoca, string endLoca, DbTransaction trans) { string sql = @"UPDATE dbo.tn_wm_b_tray_location SET CN_S_LOCATION_CODE='" + endLoca + "' WHERE CN_S_LOCATION_CODE='" + startLoca + "'"; DbCommand cmd = DataAccess.GetSqlStringCommand(sql); return ExecuteCommand(cmd, trans); } #endregion #region 修改货位拓展表 修改终点直接带入起点状态 /// /// 修改货位拓展表 /// /// 起点货位 /// 终点货位 /// /// [HANHE(lt)] CREATED BY 2018-12-5 public SqlExecuteResult UpdateLocationExt(string startLoca, string endLoca, DbTransaction trans) { string sql = @"UPDATE dbo.tn_wm_b_location_ext SET CN_S_USE_STATE=(SELECT CN_S_USE_STATE FROM tn_wm_b_location_ext WHERE CN_S_LOCATION_CODE='" + startLoca + "') WHERE CN_S_LOCATION_CODE='" + endLoca + "';UPDATE dbo.tn_wm_b_location_ext SET CN_S_USE_STATE='空' WHERE CN_S_LOCATION_CODE='" + startLoca + "'"; DbCommand cmd = DataAccess.GetSqlStringCommand(sql); return ExecuteCommand(cmd, trans); } #endregion #region 修改托盘物料分配量 /// /// 修改托盘物料分配量 /// /// /// /// /// public OperateResult UpdateTrayItemAllocQty(List trayItemList, IDbTransaction trans, string timeStamp = "") { string sql = "UPDATE TN_WM_B_TRAY_ITEM_MST SET CN_F_ALLOC_QTY=@CN_F_ALLOC_QTY,CN_S_TIMESTAMP='{0}' WHERE CN_GUID=@CN_GUID AND (CN_S_TIMESTAMP = @CN_S_TIMESTAMP OR CN_S_TIMESTAMP IS NULL)"; sql = string.Format(sql, string.IsNullOrEmpty(timeStamp) ? DateTime.Now.ToString("yyyyMMddHHmmssffff") : timeStamp); return ExecuteTranSql(sql, trayItemList, trans); } #endregion #region 新增主表 /// ///新增主表数据 /// /// /// [Hanhe(DBS)] CREATED BY 2019/4/15 public OperateResult AddMst(TN_WM_B_TRAY_ITEM_MSTEntity entity, IDbTransaction trans) { string sql = @"IF EXISTS(SELECT * FROM TN_WM_B_TRAY_ITEM_MST 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_ITEM_STATE=@CN_S_ITEM_STATE and CN_S_OWNER=@CN_S_OWNER) UPDATE TN_WM_B_TRAY_ITEM_MST SET CN_F_QUANTITY=CN_F_QUANTITY + @CN_F_QUANTITY FROM TN_WM_B_TRAY_ITEM_MST 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_ITEM_STATE=@CN_S_ITEM_STATE and CN_S_OWNER=@CN_S_OWNER else insert into TN_WM_B_TRAY_ITEM_MST(CN_GUID,CN_S_TRAY_CODE,CN_S_TRAY_GRID,CN_S_OWNER,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_FIGURE_NO,CN_S_MODEL,CN_S_IN_AREA_CODE,CN_S_ITEM_STATE,CN_F_QUANTITY,CN_S_MEASURE_UNIT) values (@CN_GUID,@CN_S_TRAY_CODE,@CN_S_TRAY_GRID,@CN_S_OWNER,@CN_S_ITEM_CODE,@CN_S_ITEM_NAME,@CN_S_FIGURE_NO,@CN_S_MODEL,@CN_S_IN_AREA_CODE,@CN_S_ITEM_STATE,@CN_F_QUANTITY,@CN_S_MEASURE_UNIT)"; return ExecuteTranSql(sql, new { CN_GUID = entity.CN_GUID, CN_S_TRAY_CODE = entity.CN_S_TRAY_CODE, CN_S_TRAY_GRID = entity.CN_S_TRAY_GRID, CN_S_OWNER = entity.CN_S_OWNER, CN_S_ITEM_CODE = entity.CN_S_ITEM_CODE, CN_S_ITEM_NAME = entity.CN_S_ITEM_NAME, CN_S_FIGURE_NO = entity.CN_S_FIGURE_NO, CN_S_MODEL = entity.CN_S_MODEL, CN_S_ITEM_STATE = entity.CN_S_ITEM_STATE, CN_S_IN_AREA_CODE = entity.CN_S_IN_AREA_CODE, CN_F_QUANTITY = entity.CN_F_QUANTITY, CN_S_MEASURE_UNIT = entity.CN_S_MEASURE_UNIT }, trans); } #endregion #region 新增子表 /// ///新增子表 /// /// /// [Hanhe(lt)] CREATED BY 2018/12/14 public SqlExecuteResult AddDtl(List list, IDbTransaction trans) { List cmdlist = new List(); foreach (TN_WM_B_TRAY_ITEM_DTLEntity entity in list) { StringBuilder strSql = new StringBuilder(); //判断是否存在数据 strSql.Append("IF EXISTS(SELECT * FROM TN_WM_B_TRAY_ITEM_DTl " + entity.sqlwhere + ")"); //修改子表 strSql.Append(@"UPDATE TN_WM_B_TRAY_ITEM_DTl SET CN_F_QUANTITY=CN_F_QUANTITY+:CN_F_QUANTITY " + entity.sqlwhere + ""); strSql.Append(" else "); strSql.Append("insert into TN_WM_B_TRAY_ITEM_DTL("); strSql.Append("CN_PARENT_GUID,CN_S_UNIQUE_CODE,CN_S_LOT_NO,CN_S_SERIAL_NO,CN_S_PACKING_UNIT,CN_F_PACKING_QTY,CN_F_QUANTITY,CN_S_VENDOR_NO,CN_S_VENDOR_NAME,CN_F_PURCHASE_PRICE,CN_F_RETAIL_PRICE,CN_T_PRODUCTION,CN_T_EXPIRATION,CN_S_PRODUCTION_BATCH,CN_S_NOTE,CN_S_EXT1,CN_S_EXT2,CN_S_EXT3,CN_S_EXT4,CN_S_EXT5,CN_S_CREATOR,CN_S_CREATOR_BY,CN_T_CREATE,CN_S_MODIFY,CN_S_MODIFY_BY,CN_T_MODIFY,CN_GUID)"); strSql.Append(" values ("); strSql.Append(":CN_PARENT_GUID,:CN_S_UNIQUE_CODE,:CN_S_LOT_NO,:CN_S_SERIAL_NO,:CN_S_PACKING_UNIT,:CN_F_PACKING_QTY,:CN_F_QUANTITY,:CN_S_VENDOR_NO,:CN_S_VENDOR_NAME,:CN_F_PURCHASE_PRICE,:CN_F_RETAIL_PRICE,:CN_T_PRODUCTION,:CN_T_EXPIRATION,:CN_S_PRODUCTION_BATCH,:CN_S_NOTE,:CN_S_EXT1,:CN_S_EXT2,:CN_S_EXT3,:CN_S_EXT4,:CN_S_EXT5,:CN_S_CREATOR,:CN_S_CREATOR_BY,:CN_T_CREATE,:CN_S_MODIFY,:CN_S_MODIFY_BY,:CN_T_MODIFY,:CN_GUID)"); DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString()); this.DataAccess.AddInParameter(cmd, "CN_PARENT_GUID", ComDbType.STRING, entity.CN_PARENT_GUID); this.DataAccess.AddInParameter(cmd, "CN_S_UNIQUE_CODE", ComDbType.STRING, entity.CN_S_UNIQUE_CODE); this.DataAccess.AddInParameter(cmd, "CN_S_LOT_NO", ComDbType.STRING, entity.CN_S_LOT_NO); this.DataAccess.AddInParameter(cmd, "CN_S_SERIAL_NO", ComDbType.STRING, entity.CN_S_SERIAL_NO); this.DataAccess.AddInParameter(cmd, "CN_S_PACKING_UNIT", ComDbType.STRING, entity.CN_S_PACKING_UNIT); this.DataAccess.AddInParameter(cmd, "CN_F_PACKING_QTY", ComDbType.DECIMAL, entity.CN_F_PACKING_QTY); this.DataAccess.AddInParameter(cmd, "CN_F_QUANTITY", ComDbType.DECIMAL, entity.CN_F_QUANTITY); this.DataAccess.AddInParameter(cmd, "CN_S_VENDOR_NO", ComDbType.STRING, entity.CN_S_VENDOR_NO); this.DataAccess.AddInParameter(cmd, "CN_S_VENDOR_NAME", ComDbType.STRING, entity.CN_S_VENDOR_NAME); this.DataAccess.AddInParameter(cmd, "CN_F_PURCHASE_PRICE", ComDbType.DECIMAL, entity.CN_F_PURCHASE_PRICE); this.DataAccess.AddInParameter(cmd, "CN_F_RETAIL_PRICE", ComDbType.DECIMAL, entity.CN_F_RETAIL_PRICE); this.DataAccess.AddInParameter(cmd, "CN_T_PRODUCTION", ComDbType.DATE, entity.CN_T_PRODUCTION); this.DataAccess.AddInParameter(cmd, "CN_T_EXPIRATION", ComDbType.DATE, entity.CN_T_EXPIRATION); this.DataAccess.AddInParameter(cmd, "CN_S_PRODUCTION_BATCH", ComDbType.STRING, entity.CN_S_PRODUCTION_BATCH); this.DataAccess.AddInParameter(cmd, "CN_S_NOTE", ComDbType.STRING, entity.CN_S_NOTE); this.DataAccess.AddInParameter(cmd, "CN_S_EXT1", ComDbType.STRING, entity.CN_S_EXT1); this.DataAccess.AddInParameter(cmd, "CN_S_EXT2", ComDbType.STRING, entity.CN_S_EXT2); this.DataAccess.AddInParameter(cmd, "CN_S_EXT3", ComDbType.STRING, entity.CN_S_EXT3); this.DataAccess.AddInParameter(cmd, "CN_S_EXT4", ComDbType.STRING, entity.CN_S_EXT3); this.DataAccess.AddInParameter(cmd, "CN_S_EXT5", ComDbType.STRING, entity.CN_S_EXT3); this.DataAccess.AddInParameter(cmd, "CN_S_CREATOR", ComDbType.STRING, entity.CN_S_CREATOR); this.DataAccess.AddInParameter(cmd, "CN_S_CREATOR_BY", ComDbType.STRING, entity.CN_S_CREATOR_BY); this.DataAccess.AddInParameter(cmd, "CN_T_CREATE", ComDbType.DATE, entity.CN_T_CREATE); this.DataAccess.AddInParameter(cmd, "CN_S_MODIFY", ComDbType.STRING, entity.CN_S_MODIFY); this.DataAccess.AddInParameter(cmd, "CN_S_MODIFY_BY", ComDbType.STRING, entity.CN_S_MODIFY_BY); this.DataAccess.AddInParameter(cmd, "CN_T_MODIFY", ComDbType.DATE, entity.CN_T_MODIFY); this.DataAccess.AddInParameter(cmd, "CN_GUID", ComDbType.STRING, System.Guid.NewGuid().ToString()); cmdlist.Add(cmd); } return ExecuteCommands(cmdlist, null); } #endregion #region 删除等于数量为0的码数量 /// ///删除等于数量为0的码数量 /// /// /// [Hanhe(lt)] CREATED BY 2018/12/14 public SqlExecuteResult DeleteMstDtl(DbTransaction trans) { string sql = "DELETE TN_WM_B_TRAY_ITEM_MST WHERE CN_F_QUANTITY <= 0;DELETE TN_WM_B_TRAY_ITEM_DTL WHERE CN_F_QUANTITY <= 0"; DbCommand cmd = DataAccess.GetSqlStringCommand(sql); return ExecuteCommand(cmd, trans); } public OperateResult DeleteMstDtl(string trayCodes, IDbTransaction trans) { string sql = "DELETE TN_WM_B_TRAY_ITEM_MST WHERE CN_F_QUANTITY <= 0 AND CN_S_TRAY_CODE in ('" + trayCodes + "');DELETE TN_WM_B_TRAY_ITEM_DTL WHERE CN_F_QUANTITY <= 0;"; return ExecuteTranSql(sql, null, trans); } #endregion #region 修改主表 /// ///新增主表数据 /// /// /// [Hanhe(lt)] CREATED BY 2018/12/14 public SqlExecuteResult UpdateMst(List list, DbTransaction trans) { List cmdlist = new List(); foreach (TN_WM_B_TRAY_ITEM_MSTEntity entity in list) { StringBuilder strSql = new StringBuilder(); //strSql.Append(@"UPDATE TN_WM_B_TRAY_ITEM_MST SET CN_F_QUANTITY=CN_F_QUANTITY - :CN_F_QUANTITY,CN_F_ALLOC_QTY=CN_F_ALLOC_QTY - :CN_F_QUANTITY " + entity.sqlwhere + ""); strSql.Append(" IF EXISTS(SELECT * FROM dbo.TN_WM_B_TRAY_ITEM_MST " + entity.sqlwhere1 + ") "); //修改主子表 strSql.Append(@"UPDATE TN_WM_B_TRAY_ITEM_MST SET CN_F_QUANTITY=CN_F_QUANTITY + :CN_F_QUANTITY,CN_F_INQTY=CN_F_INQTY + :CN_F_QUANTITY FROM TN_WM_B_TRAY_ITEM_MST " + entity.sqlwhere1 + ""); strSql.Append(" else "); strSql.Append("insert into TN_WM_B_TRAY_ITEM_MST("); strSql.Append("CN_GUID,CN_S_TRAY_CODE,CN_S_TRAY_GRID,CN_S_OWNER,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_FIGURE_NO,CN_S_MODEL,CN_S_ITEM_STATE,CN_F_QUANTITY,CN_S_MEASURE_UNIT,CN_S_IN_AREA_CODE,CN_F_INQTY,CN_F_ALLOC_QTY,CN_S_TIMESTAMP)"); strSql.Append(" values ("); strSql.Append(":CN_GUID,:CN_S_TRAY_CODE,:CN_S_TRAY_GRID,:CN_S_OWNER,:CN_S_ITEM_CODE,:CN_S_ITEM_NAME,:CN_S_FIGURE_NO,:CN_S_MODEL,:CN_S_ITEM_STATE,:CN_F_QUANTITY,:CN_S_MEASURE_UNIT,:CN_S_IN_AREA_CODE,:CN_F_INQTY,:CN_F_ALLOC_QTY,:CN_S_TIMESTAMP)"); DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString()); this.DataAccess.AddInParameter(cmd, "CN_GUID", ComDbType.STRING, entity.CN_GUID); this.DataAccess.AddInParameter(cmd, "CN_S_TRAY_CODE", ComDbType.STRING, entity.CN_S_TRAY_CODE); this.DataAccess.AddInParameter(cmd, "CN_S_TRAY_GRID", ComDbType.STRING, entity.CN_S_TRAY_GRID); this.DataAccess.AddInParameter(cmd, "CN_S_OWNER", ComDbType.STRING, entity.CN_S_OWNER); this.DataAccess.AddInParameter(cmd, "CN_S_ITEM_CODE", ComDbType.STRING, entity.CN_S_ITEM_CODE); this.DataAccess.AddInParameter(cmd, "CN_S_ITEM_NAME", ComDbType.STRING, entity.CN_S_ITEM_NAME); this.DataAccess.AddInParameter(cmd, "CN_S_FIGURE_NO", ComDbType.STRING, entity.CN_S_FIGURE_NO); this.DataAccess.AddInParameter(cmd, "CN_S_MODEL", ComDbType.STRING, entity.CN_S_MODEL); this.DataAccess.AddInParameter(cmd, "CN_S_ITEM_STATE", ComDbType.STRING, entity.CN_S_ITEM_STATE); this.DataAccess.AddInParameter(cmd, "CN_F_QUANTITY", ComDbType.DECIMAL, entity.CN_F_QUANTITY); this.DataAccess.AddInParameter(cmd, "CN_S_MEASURE_UNIT", ComDbType.STRING, entity.CN_S_MEASURE_UNIT); this.DataAccess.AddInParameter(cmd, "CN_S_IN_AREA_CODE", ComDbType.STRING, entity.CN_S_IN_AREA_CODE); this.DataAccess.AddInParameter(cmd, "CN_F_INQTY", ComDbType.DECIMAL, entity.CN_F_INQTY); this.DataAccess.AddInParameter(cmd, "CN_F_ALLOC_QTY", ComDbType.DECIMAL, entity.CN_F_ALLOC_QTY); this.DataAccess.AddInParameter(cmd, "CN_S_TIMESTAMP", ComDbType.STRING, entity.CN_S_TIMESTAMP); cmdlist.Add(cmd); } return ExecuteCommands(cmdlist, trans); } #endregion #region 修改子表 /// ///修改子表 /// /// /// [Hanhe(lt)] CREATED BY 2018/12/14 public SqlExecuteResult UpdateDtl(List list, DbTransaction trans) { List cmdlist = new List(); foreach (TN_WM_B_TRAY_ITEM_DTLEntity entity in list) { StringBuilder strSql = new StringBuilder(); //strSql.Append(@"UPDATE TN_WM_B_TRAY_ITEM_DTl SET CN_F_QUANTITY=CN_F_QUANTITY - :CN_F_QUANTITY " + entity.sqlwhere + ""); //判断是否存在数据 strSql.Append(" IF EXISTS(SELECT * FROM TN_WM_B_TRAY_ITEM_DTl " + entity.sqlwhere + ")"); //修改子表 strSql.Append(@"UPDATE TN_WM_B_TRAY_ITEM_DTl SET CN_F_QUANTITY=CN_F_QUANTITY + :CN_F_QUANTITY " + entity.sqlwhere + ""); strSql.Append(" else "); strSql.Append("insert into TN_WM_B_TRAY_ITEM_DTL("); strSql.Append("CN_PARENT_GUID,CN_S_UNIQUE_CODE,CN_S_LOT_NO,CN_S_SERIAL_NO,CN_S_PACKING_UNIT,CN_F_PACKING_QTY,CN_F_QUANTITY,CN_S_VENDOR_NO,CN_S_VENDOR_NAME,CN_F_PURCHASE_PRICE,CN_F_RETAIL_PRICE,CN_T_PRODUCTION,CN_T_EXPIRATION,CN_S_PRODUCTION_BATCH,CN_S_NOTE,CN_S_EXT1,CN_S_EXT2,CN_S_EXT3,CN_S_EXT4,CN_S_EXT5,CN_S_CREATOR,CN_S_CREATOR_BY,CN_T_CREATE,CN_S_MODIFY,CN_S_MODIFY_BY,CN_T_MODIFY,CN_GUID)"); strSql.Append(" values ("); strSql.Append(":CN_PARENT_GUID,:CN_S_UNIQUE_CODE,:CN_S_LOT_NO,:CN_S_SERIAL_NO,:CN_S_PACKING_UNIT,:CN_F_PACKING_QTY,:CN_F_QUANTITY,:CN_S_VENDOR_NO,:CN_S_VENDOR_NAME,:CN_F_PURCHASE_PRICE,:CN_F_RETAIL_PRICE,:CN_T_PRODUCTION,:CN_T_EXPIRATION,:CN_S_PRODUCTION_BATCH,:CN_S_NOTE,:CN_S_EXT1,:CN_S_EXT2,:CN_S_EXT3,:CN_S_EXT4,:CN_S_EXT5,:CN_S_CREATOR,:CN_S_CREATOR_BY,:CN_T_CREATE,:CN_S_MODIFY,:CN_S_MODIFY_BY,:CN_T_MODIFY,:CN_GUID)"); DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString()); this.DataAccess.AddInParameter(cmd, "CN_PARENT_GUID", ComDbType.STRING, entity.CN_PARENT_GUID); this.DataAccess.AddInParameter(cmd, "CN_S_UNIQUE_CODE", ComDbType.STRING, entity.CN_S_UNIQUE_CODE); this.DataAccess.AddInParameter(cmd, "CN_S_LOT_NO", ComDbType.STRING, entity.CN_S_LOT_NO); this.DataAccess.AddInParameter(cmd, "CN_S_SERIAL_NO", ComDbType.STRING, entity.CN_S_SERIAL_NO); this.DataAccess.AddInParameter(cmd, "CN_S_PACKING_UNIT", ComDbType.STRING, entity.CN_S_PACKING_UNIT); this.DataAccess.AddInParameter(cmd, "CN_F_PACKING_QTY", ComDbType.DECIMAL, entity.CN_F_PACKING_QTY == null ? 0 : entity.CN_F_PACKING_QTY); this.DataAccess.AddInParameter(cmd, "CN_F_QUANTITY", ComDbType.DECIMAL, entity.CN_F_QUANTITY); this.DataAccess.AddInParameter(cmd, "CN_S_VENDOR_NO", ComDbType.STRING, entity.CN_S_VENDOR_NO); this.DataAccess.AddInParameter(cmd, "CN_S_VENDOR_NAME", ComDbType.STRING, entity.CN_S_VENDOR_NAME); this.DataAccess.AddInParameter(cmd, "CN_F_PURCHASE_PRICE", ComDbType.DECIMAL, entity.CN_F_PURCHASE_PRICE); this.DataAccess.AddInParameter(cmd, "CN_F_RETAIL_PRICE", ComDbType.DECIMAL, entity.CN_F_RETAIL_PRICE); this.DataAccess.AddInParameter(cmd, "CN_T_PRODUCTION", ComDbType.DATE, entity.CN_T_PRODUCTION); this.DataAccess.AddInParameter(cmd, "CN_T_EXPIRATION", ComDbType.DATE, entity.CN_T_EXPIRATION); this.DataAccess.AddInParameter(cmd, "CN_S_PRODUCTION_BATCH", ComDbType.STRING, entity.CN_S_PRODUCTION_BATCH); this.DataAccess.AddInParameter(cmd, "CN_S_NOTE", ComDbType.STRING, entity.CN_S_NOTE); this.DataAccess.AddInParameter(cmd, "CN_S_EXT1", ComDbType.STRING, entity.CN_S_EXT1); this.DataAccess.AddInParameter(cmd, "CN_S_EXT2", ComDbType.STRING, entity.CN_S_EXT2); this.DataAccess.AddInParameter(cmd, "CN_S_EXT3", ComDbType.STRING, entity.CN_S_EXT3); this.DataAccess.AddInParameter(cmd, "CN_S_EXT4", ComDbType.STRING, entity.CN_S_EXT3); this.DataAccess.AddInParameter(cmd, "CN_S_EXT5", ComDbType.STRING, entity.CN_S_EXT3); this.DataAccess.AddInParameter(cmd, "CN_S_CREATOR", ComDbType.STRING, entity.CN_S_CREATOR); this.DataAccess.AddInParameter(cmd, "CN_S_CREATOR_BY", ComDbType.STRING, entity.CN_S_CREATOR_BY); this.DataAccess.AddInParameter(cmd, "CN_T_CREATE", ComDbType.DATE, entity.CN_T_CREATE); this.DataAccess.AddInParameter(cmd, "CN_S_MODIFY", ComDbType.STRING, entity.CN_S_MODIFY); this.DataAccess.AddInParameter(cmd, "CN_S_MODIFY_BY", ComDbType.STRING, entity.CN_S_MODIFY_BY); this.DataAccess.AddInParameter(cmd, "CN_T_MODIFY", ComDbType.DATE, entity.CN_T_MODIFY); this.DataAccess.AddInParameter(cmd, "CN_GUID", ComDbType.STRING, entity.CN_GUID); cmdlist.Add(cmd); } return ExecuteCommands(cmdlist, trans); } #endregion #region 修改主表 /// ///新增主表数据 /// /// /// [Hanhe(lt)] CREATED BY 2019/1/7 public OperateResult UpdateTrayItemMst(List list, IDbTransaction trans) { OperateResult result = new OperateResult(); foreach (TN_WM_B_TRAY_ITEM_MSTEntity entity in list) { StringBuilder strSql = new StringBuilder(); strSql.Append(@"UPDATE TN_WM_B_TRAY_ITEM_MST SET CN_F_QUANTITY=CN_F_QUANTITY - @CN_F_QUANTITY,CN_F_INQTY=CN_F_INQTY - @CN_F_QUANTITY " + entity.sqlwhere + ""); strSql.Append(" IF EXISTS(SELECT * FROM dbo.TN_WM_B_TRAY_ITEM_MST " + entity.sqlwhere1 + ") "); //修改主子表 strSql.Append(@"UPDATE TN_WM_B_TRAY_ITEM_MST SET CN_F_QUANTITY=CN_F_QUANTITY + @CN_F_QUANTITY ,CN_F_INQTY=CN_F_INQTY + @CN_F_QUANTITY FROM TN_WM_B_TRAY_ITEM_MST " + entity.sqlwhere1 + ""); strSql.Append(" else "); strSql.Append("insert into TN_WM_B_TRAY_ITEM_MST("); strSql.Append("CN_GUID,CN_S_TRAY_CODE,CN_S_TRAY_GRID,CN_S_OWNER,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_FIGURE_NO,CN_S_MODEL,CN_S_ITEM_STATE,CN_F_QUANTITY,CN_S_MEASURE_UNIT,CN_S_IN_AREA_CODE,CN_F_INQTY,CN_F_ALLOC_QTY,CN_S_TIMESTAMP)"); strSql.Append(" values ("); strSql.Append("@CN_GUID,@CN_S_TRAY_CODE,@CN_S_TRAY_GRID,@CN_S_OWNER,@CN_S_ITEM_CODE,@CN_S_ITEM_NAME,@CN_S_FIGURE_NO,@CN_S_MODEL,@CN_S_ITEM_STATE,@CN_F_QUANTITY,@CN_S_MEASURE_UNIT,@CN_S_IN_AREA_CODE,@CN_F_INQTY,@CN_F_ALLOC_QTY,@CN_S_TIMESTAMP)"); result = ExecuteTranSql(strSql.ToString(), new { CN_GUID = entity.CN_GUID, CN_S_TRAY_CODE = entity.CN_S_TRAY_CODE, CN_S_TRAY_GRID = entity.CN_S_TRAY_GRID, CN_S_OWNER = entity.CN_S_OWNER, CN_S_ITEM_CODE = entity.CN_S_ITEM_CODE, CN_S_ITEM_NAME = entity.CN_S_ITEM_NAME, CN_S_FIGURE_NO = entity.CN_S_FIGURE_NO, CN_S_MODEL = entity.CN_S_MODEL, CN_S_ITEM_STATE = entity.CN_S_ITEM_STATE, CN_F_QUANTITY = entity.CN_F_QUANTITY, CN_S_MEASURE_UNIT = entity.CN_S_MEASURE_UNIT, CN_S_IN_AREA_CODE = entity.CN_S_IN_AREA_CODE, CN_F_INQTY = entity.CN_F_INQTY, CN_F_ALLOC_QTY = entity.CN_F_ALLOC_QTY, CN_S_TIMESTAMP = entity.CN_S_TIMESTAMP, }, trans); if (result.Status == ResultStatus.Error) return result; } return result; } #endregion #region 修改子表 /// ///修改子表 /// /// /// [Hanhe(lt)] CREATED BY 2018/12/14 public OperateResult UpdateTrayItemDtl(List list, IDbTransaction trans) { OperateResult result = new OperateResult(); foreach (TN_WM_B_TRAY_ITEM_DTLEntity entity in list) { StringBuilder strSql = new StringBuilder(); strSql.Append(@"UPDATE TN_WM_B_TRAY_ITEM_DTl SET CN_F_QUANTITY=CN_F_QUANTITY - @CN_F_QUANTITY " + entity.sqlwhere + ""); //判断是否存在数据 strSql.Append(" IF EXISTS(SELECT * FROM TN_WM_B_TRAY_ITEM_DTl " + entity.sqlwhere1 + ")"); //修改子表 strSql.Append(@"UPDATE TN_WM_B_TRAY_ITEM_DTl SET CN_F_QUANTITY=CN_F_QUANTITY + @CN_F_QUANTITY " + entity.sqlwhere1 + ""); strSql.Append(" else "); strSql.Append("insert into TN_WM_B_TRAY_ITEM_DTL("); strSql.Append("CN_PARENT_GUID,CN_S_UNIQUE_CODE,CN_S_LOT_NO,CN_S_SERIAL_NO,CN_S_PACKING_UNIT,CN_F_PACKING_QTY,CN_F_QUANTITY,CN_S_VENDOR_NO,CN_S_VENDOR_NAME,CN_F_PURCHASE_PRICE,CN_F_RETAIL_PRICE,CN_T_PRODUCTION,CN_T_EXPIRATION,CN_S_PRODUCTION_BATCH,CN_S_NOTE,CN_S_EXT1,CN_S_EXT2,CN_S_EXT3,CN_S_EXT4,CN_S_EXT5,CN_S_CREATOR,CN_S_CREATOR_BY,CN_T_CREATE,CN_S_MODIFY,CN_S_MODIFY_BY,CN_T_MODIFY,CN_GUID)"); strSql.Append(" values ("); strSql.Append("@CN_PARENT_GUID,@CN_S_UNIQUE_CODE,@CN_S_LOT_NO,@CN_S_SERIAL_NO,@CN_S_PACKING_UNIT,@CN_F_PACKING_QTY,@CN_F_QUANTITY,@CN_S_VENDOR_NO,@CN_S_VENDOR_NAME,@CN_F_PURCHASE_PRICE,@CN_F_RETAIL_PRICE,@CN_T_PRODUCTION,@CN_T_EXPIRATION,@CN_S_PRODUCTION_BATCH,@CN_S_NOTE,@CN_S_EXT1,@CN_S_EXT2,@CN_S_EXT3,@CN_S_EXT4,@CN_S_EXT5,@CN_S_CREATOR,@CN_S_CREATOR_BY,@CN_T_CREATE,@CN_S_MODIFY,@CN_S_MODIFY_BY,@CN_T_MODIFY,@CN_GUID)"); result = ExecuteTranSql(strSql.ToString(), new { CN_PARENT_GUID = entity.CN_PARENT_GUID, CN_S_UNIQUE_CODE = entity.CN_S_UNIQUE_CODE, CN_S_LOT_NO = entity.CN_S_LOT_NO, CN_S_SERIAL_NO = entity.CN_S_SERIAL_NO, CN_S_PACKING_UNIT = entity.CN_S_PACKING_UNIT, CN_F_PACKING_QTY = entity.CN_F_PACKING_QTY == null ? 0 : entity.CN_F_PACKING_QTY, CN_F_QUANTITY = entity.CN_F_QUANTITY, CN_S_VENDOR_NO = entity.CN_S_VENDOR_NO, CN_S_VENDOR_NAME = entity.CN_S_VENDOR_NAME, CN_F_PURCHASE_PRICE = entity.CN_F_PURCHASE_PRICE, CN_F_RETAIL_PRICE = entity.CN_F_RETAIL_PRICE, CN_T_PRODUCTION = entity.CN_T_PRODUCTION, CN_T_EXPIRATION = entity.CN_T_EXPIRATION, CN_S_PRODUCTION_BATCH = entity.CN_S_PRODUCTION_BATCH, CN_S_NOTE = entity.CN_S_NOTE, CN_S_EXT1 = entity.CN_S_EXT1, CN_S_EXT2 = entity.CN_S_EXT2, CN_S_EXT3 = entity.CN_S_EXT3, CN_S_EXT4 = entity.CN_S_EXT3, CN_S_EXT5 = entity.CN_S_EXT3, CN_S_CREATOR = entity.CN_S_CREATOR, CN_S_CREATOR_BY = entity.CN_S_CREATOR_BY, CN_T_CREATE = entity.CN_T_CREATE, CN_S_MODIFY = entity.CN_S_MODIFY, CN_S_MODIFY_BY = entity.CN_S_MODIFY_BY, CN_T_MODIFY = entity.CN_T_MODIFY, CN_GUID = entity.CN_GUID, }, trans); if (result.Status == ResultStatus.Error) return result; } return result; } #endregion #region 新增码盘主子表 /// ///新增码盘主子表 /// /// /// [Hanhe(lt)] CREATED BY 2019/1/7 public OperateResult AddORUpdateTrayItemMst(List list, IDbTransaction trans) { OperateResult result = new OperateResult(); foreach (TN_WM_B_TRAY_ITEM_MSTEntity entity in list) { StringBuilder strSql = new StringBuilder(); strSql.Append(" IF EXISTS(SELECT * FROM dbo.TN_WM_B_TRAY_ITEM_MST " + entity.sqlwhere + ") "); //修改主子表 strSql.Append(@"UPDATE TN_WM_B_TRAY_ITEM_MST SET CN_F_QUANTITY=CN_F_QUANTITY + @CN_F_QUANTITY ,CN_F_INQTY=CN_F_INQTY + @CN_F_QUANTITY FROM TN_WM_B_TRAY_ITEM_MST " + entity.sqlwhere + ""); strSql.Append(" else "); strSql.Append("insert into TN_WM_B_TRAY_ITEM_MST("); strSql.Append("CN_GUID,CN_S_TRAY_CODE,CN_S_TRAY_GRID,CN_S_OWNER,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_FIGURE_NO,CN_S_MODEL,CN_S_ITEM_STATE,CN_F_QUANTITY,CN_S_MEASURE_UNIT,CN_S_IN_AREA_CODE,CN_F_INQTY,CN_F_ALLOC_QTY,CN_S_TIMESTAMP)"); strSql.Append(" values ("); strSql.Append("@CN_GUID,@CN_S_TRAY_CODE,@CN_S_TRAY_GRID,@CN_S_OWNER,@CN_S_ITEM_CODE,@CN_S_ITEM_NAME,@CN_S_FIGURE_NO,@CN_S_MODEL,@CN_S_ITEM_STATE,@CN_F_QUANTITY,@CN_S_MEASURE_UNIT,@CN_S_IN_AREA_CODE,@CN_F_INQTY,@CN_F_ALLOC_QTY,@CN_S_TIMESTAMP)"); result = ExecuteTranSql(strSql.ToString(), new { CN_GUID = entity.CN_GUID, CN_S_TRAY_CODE = entity.CN_S_TRAY_CODE, CN_S_TRAY_GRID = entity.CN_S_TRAY_GRID, CN_S_OWNER = entity.CN_S_OWNER, CN_S_ITEM_CODE = entity.CN_S_ITEM_CODE, CN_S_ITEM_NAME = entity.CN_S_ITEM_NAME, CN_S_FIGURE_NO = entity.CN_S_FIGURE_NO, CN_S_MODEL = entity.CN_S_MODEL, CN_S_ITEM_STATE = entity.CN_S_ITEM_STATE, CN_F_QUANTITY = entity.CN_F_QUANTITY, CN_S_MEASURE_UNIT = entity.CN_S_MEASURE_UNIT, CN_S_IN_AREA_CODE = entity.CN_S_IN_AREA_CODE, CN_F_INQTY = entity.CN_F_INQTY, CN_F_ALLOC_QTY = entity.CN_F_ALLOC_QTY, CN_S_TIMESTAMP = entity.CN_S_TIMESTAMP, }, trans); if (result.Status == ResultStatus.Error) return result; } return result; } /// ///修改子表 /// /// /// [Hanhe(lt)] CREATED BY 2018/12/14 public OperateResult AddORUpdateTrayItemDtl(List list, IDbTransaction trans) { OperateResult result = new OperateResult(); foreach (TN_WM_B_TRAY_ITEM_DTLEntity entity in list) { StringBuilder strSql = new StringBuilder(); //判断是否存在数据 strSql.Append(" IF EXISTS(SELECT * FROM TN_WM_B_TRAY_ITEM_DTl " + entity.sqlwhere + ")"); //修改子表 strSql.Append(@"UPDATE TN_WM_B_TRAY_ITEM_DTl SET CN_F_QUANTITY=CN_F_QUANTITY + @CN_F_QUANTITY " + entity.sqlwhere + ""); strSql.Append(" else "); strSql.Append("insert into TN_WM_B_TRAY_ITEM_DTL("); strSql.Append("CN_PARENT_GUID,CN_S_UNIQUE_CODE,CN_S_LOT_NO,CN_S_SERIAL_NO,CN_S_PACKING_UNIT,CN_F_PACKING_QTY,CN_F_QUANTITY,CN_S_VENDOR_NO,CN_S_VENDOR_NAME,CN_F_PURCHASE_PRICE,CN_F_RETAIL_PRICE,CN_T_PRODUCTION,CN_T_EXPIRATION,CN_S_PRODUCTION_BATCH,CN_S_NOTE,CN_S_EXT1,CN_S_EXT2,CN_S_EXT3,CN_S_EXT4,CN_S_EXT5,CN_S_CREATOR,CN_S_CREATOR_BY,CN_T_CREATE,CN_S_MODIFY,CN_S_MODIFY_BY,CN_T_MODIFY,CN_GUID)"); strSql.Append(" values ("); strSql.Append("@CN_PARENT_GUID,@CN_S_UNIQUE_CODE,@CN_S_LOT_NO,@CN_S_SERIAL_NO,@CN_S_PACKING_UNIT,@CN_F_PACKING_QTY,@CN_F_QUANTITY,@CN_S_VENDOR_NO,@CN_S_VENDOR_NAME,@CN_F_PURCHASE_PRICE,@CN_F_RETAIL_PRICE,@CN_T_PRODUCTION,@CN_T_EXPIRATION,@CN_S_PRODUCTION_BATCH,@CN_S_NOTE,@CN_S_EXT1,@CN_S_EXT2,@CN_S_EXT3,@CN_S_EXT4,@CN_S_EXT5,@CN_S_CREATOR,@CN_S_CREATOR_BY,@CN_T_CREATE,@CN_S_MODIFY,@CN_S_MODIFY_BY,@CN_T_MODIFY,@CN_GUID)"); result = ExecuteTranSql(strSql.ToString(), new { CN_PARENT_GUID = entity.CN_PARENT_GUID, CN_S_UNIQUE_CODE = entity.CN_S_UNIQUE_CODE, CN_S_LOT_NO = entity.CN_S_LOT_NO, CN_S_SERIAL_NO = entity.CN_S_SERIAL_NO, CN_S_PACKING_UNIT = entity.CN_S_PACKING_UNIT, CN_F_PACKING_QTY = entity.CN_F_PACKING_QTY == null ? 0 : entity.CN_F_PACKING_QTY, CN_F_QUANTITY = entity.CN_F_QUANTITY, CN_S_VENDOR_NO = entity.CN_S_VENDOR_NO, CN_S_VENDOR_NAME = entity.CN_S_VENDOR_NAME, CN_F_PURCHASE_PRICE = entity.CN_F_PURCHASE_PRICE, CN_F_RETAIL_PRICE = entity.CN_F_RETAIL_PRICE, CN_T_PRODUCTION = entity.CN_T_PRODUCTION, CN_T_EXPIRATION = entity.CN_T_EXPIRATION, CN_S_PRODUCTION_BATCH = entity.CN_S_PRODUCTION_BATCH, CN_S_NOTE = entity.CN_S_NOTE, CN_S_EXT1 = entity.CN_S_EXT1, CN_S_EXT2 = entity.CN_S_EXT2, CN_S_EXT3 = entity.CN_S_EXT3, CN_S_EXT4 = entity.CN_S_EXT3, CN_S_EXT5 = entity.CN_S_EXT3, CN_S_CREATOR = entity.CN_S_CREATOR, CN_S_CREATOR_BY = entity.CN_S_CREATOR_BY, CN_T_CREATE = entity.CN_T_CREATE, CN_S_MODIFY = entity.CN_S_MODIFY, CN_S_MODIFY_BY = entity.CN_S_MODIFY_BY, CN_T_MODIFY = entity.CN_T_MODIFY, CN_GUID = entity.CN_GUID, }, trans); if (result.Status == ResultStatus.Error) return result; } return result; } #endregion #region 删除托盘物料关联表 /// /// 删除托盘物料关联表 /// /// 托盘编码集合 /// /// public OperateResult DeleteByFromNo(string opTrayCodeList, IDbTransaction trans) { string sql = "DELETE FROM TN_WM_B_TRAY_ITEM_DTL WHERE CN_PARENT_GUID in (select CN_GUID from TN_WM_B_TRAY_ITEM_MST WHERE CN_S_TRAY_CODE IN (" + opTrayCodeList + "))"; OperateResult res = ExecuteTranSql(sql, null, trans); if (res.Success) { string sqlmst = "DELETE FROM TN_WM_B_TRAY_ITEM_MST WHERE CN_S_TRAY_CODE IN (" + opTrayCodeList + ")"; res = ExecuteTranSql(sqlmst, null, trans); return res; } return res; } #endregion #region 获取未分配量=总量-分配量(运动中托盘的可用量=上架量 ，库区中静态的可用量=库存可用量) /// /// 获取未分配量=总量-分配量(运动中托盘的可用量=上架量 ，库区中静态的可用量=库存可用量) /// /// 托盘 /// /// [Hanhe(dbs)] CREATED BY 2019/3/7 public DataTable GetNoAllocQty(string trayCode) { string sql = @"select a.CN_S_ITEM_CODE,a.CN_F_QUANTITY,a.CN_S_ITEM_STATE,a.CN_S_OWNER,b.CN_T_PRODUCTION,b.CN_S_LOT_NO, a.CN_F_QUANTITY-a.CN_F_ALLOC_QTY as UsableQty from TN_WM_B_TRAY_ITEM_MST a join TN_WM_B_TRAY_ITEM_DTL b on a.CN_GUID = b.CN_PARENT_GUID where a.CN_S_TRAY_CODE=@CN_S_TRAY_CODE"; return ExecuteDataTable(sql, new { CN_S_TRAY_CODE = trayCode }); } #endregion #region 获取托盘中库存信息 /// /// 获取托盘中库存信息 /// /// /// /// [Hanhe(dbs)] CREATED BY 2019/3/7 public DataTable GetTrayInventoryQty(string trayCode) { string sql = @"select a.CN_S_ITEM_CODE,a.CN_S_ITEM_NAME,b.CN_F_QUANTITY,a.CN_S_ITEM_STATE,a.CN_S_OWNER,b.CN_T_PRODUCTION,b.CN_S_LOT_NO,b.CN_S_PRODUCTION_BATCH from TN_WM_B_TRAY_ITEM_MST a join TN_WM_B_TRAY_ITEM_DTL b on a.CN_GUID = b.CN_PARENT_GUID where a.CN_S_TRAY_CODE=@CN_S_TRAY_CODE"; return ExecuteDataTable(sql, new { CN_S_TRAY_CODE = trayCode }); } #endregion #region 减少托盘与物料关联量 public OperateResult ReduceMstQty(TN_WM_B_TRAY_ITEM_MSTEntity mst, IDbTransaction trans) { string sql = @"UPDATE TN_WM_B_TRAY_ITEM_MST SET CN_F_QUANTITY=CN_F_QUANTITY-@CN_F_QUANTITY where CN_GUID=@CN_GUID "; return ExecuteTranSql(sql, new { CN_F_QUANTITY = mst.CN_F_QUANTITY, CN_GUID = mst.CN_GUID }, trans); } public OperateResult ReduceDtlQty(TN_WM_B_TRAY_ITEM_DTLEntity dtl, IDbTransaction trans) { string sql = @"UPDATE TN_WM_B_TRAY_ITEM_DTL SET CN_F_QUANTITY=CN_F_QUANTITY-@CN_F_QUANTITY where CN_GUID=@CN_GUID "; return ExecuteTranSql(sql, new { CN_F_QUANTITY = dtl.CN_F_QUANTITY, CN_GUID = dtl.CN_GUID }, trans); } #endregion public OperateResult AddDtlQty(TN_WM_B_TRAY_ITEM_DTLEntity dtl, IDbTransaction trans) { string sql = @"UPDATE TN_WM_B_TRAY_ITEM_DTL SET CN_F_QUANTITY=CN_F_QUANTITY+@CN_F_QUANTITY where CN_GUID=@CN_GUID "; return ExecuteTranSql(sql, new { CN_F_QUANTITY = dtl.CN_F_QUANTITY, CN_GUID = dtl.CN_GUID }, trans); } public OperateResult AddMstQty(TN_WM_B_TRAY_ITEM_MSTEntity mst, IDbTransaction trans) { string sql = @"UPDATE TN_WM_B_TRAY_ITEM_MST SET CN_F_QUANTITY=CN_F_QUANTITY+@CN_F_QUANTITY where CN_GUID=@CN_GUID "; return ExecuteTranSql(sql, new { CN_F_QUANTITY = mst.CN_F_QUANTITY, CN_GUID = mst.CN_GUID }, trans); } #region 删除托盘物料的主子表 public OperateResult DeleteTrayItem(string sqlWhere, IDbTransaction trans) { var sql = string.Format(@"DELETE FROM TN_WM_B_TRAY_ITEM_DTL WHERE CN_PARENT_GUID IN (SELECT CN_GUID FROM TN_WM_B_TRAY_ITEM_MST WHERE {0}) ", sqlWhere); if (ExecuteTranSql(sql, null, null).Success) { sql = string.Format("DELETE FROM TN_WM_B_TRAY_ITEM_MST WHERE {0}", sqlWhere); return ExecuteTranSql(sql, null, trans); } return ExecuteTranSql(sql, null, trans); } #endregion #region 验证料箱码是否属于当前托盘 public bool CheckUniqueIsCurTray(string tryaCode, string uniqueCode) { var sql = string.Format(@"SELECT COUNT(1) FROM TN_WM_B_TRAY_ITEM_MST A INNER JOIN TN_WM_B_TRAY_ITEM_DTL B ON A.CN_GUID=B.CN_PARENT_GUID WHERE A.CN_S_TRAY_CODE='{0}' AND B.CN_S_UNIQUE_CODE='{1}'", tryaCode, uniqueCode); var dt = ExecuteDataTable(sql, null); return Convert.ToInt32(dt.Rows[0][0]) > 0; } #endregion #region 验证料箱码是否属于当前托盘 public bool CheckCurTrayIsHasItem(string tryaCode) { var sql = string.Format(@"SELECT COUNT(1) FROM TN_WM_B_TRAY_ITEM_MST A INNER JOIN TN_WM_B_TRAY_ITEM_DTL B ON A.CN_GUID=B.CN_PARENT_GUID WHERE A.CN_S_TRAY_CODE='{0}' ", tryaCode); var dt = ExecuteDataTable(sql, null); return Convert.ToInt32(dt.Rows[0][0]) > 0; } #endregion } }