using DapperORMCore.Context.DataContext; using DapperORMCore.Model.CoreModel; using DapperORMCore.Repository.IRepositorys; using Microsoft.Extensions.Configuration; using NXWMS.IService.NXWMS; using NXWMS.IService.NXWMS.Instock; using NXWMS.Model.AppModels.Condition.Instock; using NXWMS.Model.AppModels.Result.Balance; using NXWMS.Model.AppModels.Result.Base; using NXWMS.Model.AppModels.Result.Common; using NXWMS.Model.AppModels.Result.Instock; using NXWMS.Model.AppModels.Result.WmsTask; using NXWMS.Model.Common; using NXWMS.String.Enums; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using WestDistance.DapperORM.Repository.Repositorys; namespace NXWMS.Service.NXWMS.Instock { /// /// 上架单服务 /// [AutoInject(typeof(IWmsInPutawayService), InjectType.Scope)] public class WmsInPutawayService : ServiceBase, IWmsInPutawayService { #region 全局变量、构造注入 /// /// 系统操作仓储中转 /// private IDataRepositoryContext _dataContext; /// /// SQL节点仓储 /// private ISQLNodeRepository _iSQLNodeRepository; /// /// 配置 /// private IConfiguration _configuration; private IERPServer _eRPServer; /// /// 构造注入 /// /// /// /// public WmsInPutawayService(IDataRepositoryContext dataRepositoryContext, IConfiguration configuration, ISQLNodeRepository iSQLNodeRepository, IERPServer eRPServer) { this._dataContext = dataRepositoryContext; this._configuration = configuration; this._iSQLNodeRepository = iSQLNodeRepository; this._eRPServer = eRPServer; } #endregion /// /// 分页查询上架单主表数据 /// /// 上架单查询条件实体类对象 /// public OperateResultInfo> GetWmsInPutAwayListForPage(WmsInPutAwaySearchMd putawaySearchMd) { try { #region SQL语句生成 StringBuilder sqlCondition = new StringBuilder(); if (!string.IsNullOrEmpty(putawaySearchMd.PutawayNoMsg)) { sqlCondition.Append($" AND PUTAWAY_NO = '{putawaySearchMd.PutawayNoMsg}'"); } if (!string.IsNullOrEmpty(putawaySearchMd.PalletNoMsg)) { sqlCondition.Append($" AND PALLET_CODE like '%{putawaySearchMd.PalletNoMsg}%'"); } if (!string.IsNullOrEmpty(putawaySearchMd.TargetBinMsg)) { sqlCondition.Append($" AND EBIN_CODE = '{putawaySearchMd.TargetBinMsg}'"); } if (!string.IsNullOrEmpty(putawaySearchMd.PutawayTypeMsg)) { sqlCondition.Append($" AND PUTAWAY_TYPE = '{putawaySearchMd.PutawayTypeMsg}'"); } if (!string.IsNullOrEmpty(putawaySearchMd.PutawayStatusMsg)) { sqlCondition.Append($" AND PUTAWAY_STATUS = '{putawaySearchMd.PutawayStatusMsg}'"); } if (!string.IsNullOrEmpty(putawaySearchMd.BatchNoMsg)) { sqlCondition.Append($" AND PUTAWAY_ID IN (SELECT PUTAWAY_ID FROM VW_WMS_IN_PUTAWAY_DTL WHERE BATCH_NO = '{putawaySearchMd.BatchNoMsg}')"); } if (!string.IsNullOrEmpty(putawaySearchMd.MaterielMsg)) { sqlCondition.Append($" AND PUTAWAY_ID IN (SELECT PUTAWAY_ID FROM VW_WMS_IN_PUTAWAY_DTL WHERE MATERIEL_CODE LIKE '%{putawaySearchMd.MaterielMsg}%' OR MATERIEL_NAME LIKE '%{putawaySearchMd.MaterielMsg}%' OR MATERIEL_BARCODE LIKE '%{putawaySearchMd.MaterielMsg}%')"); } if (!string.IsNullOrEmpty(putawaySearchMd.SupplierMsg)) { sqlCondition.Append($" AND PUTAWAY_ID IN (SELECT PUTAWAY_ID FROM VW_WMS_IN_PUTAWAY_DTL WHERE SUPPLIER_CODE LIKE '%{putawaySearchMd.SupplierMsg}%' OR SUPPLIER_NAME LIKE '%{putawaySearchMd.SupplierMsg}%')"); } if (!string.IsNullOrEmpty(putawaySearchMd.StartCreatTimeMsg)) { sqlCondition.Append($" AND CREATE_TIME >= '{putawaySearchMd.StartCreatTimeMsg}'"); } if (!string.IsNullOrEmpty(putawaySearchMd.EndCreatTimeMsg)) { sqlCondition.Append($" AND CREATE_TIME <= '{putawaySearchMd.EndCreatTimeMsg}'"); } StringBuilder sqlCountPutAwayData = new StringBuilder($@"SELECT COUNT(1) FROM VW_WMS_IN_PUTAWAY WHERE 1=1"); sqlCountPutAwayData.Append(sqlCondition.ToString()); int pageStartIndex = (putawaySearchMd.PageNum - 1) * putawaySearchMd.EveryPageQty; int pageEndIndex = putawaySearchMd.PageNum * putawaySearchMd.EveryPageQty; StringBuilder sqlQueryPutAwayData = new StringBuilder($@" SELECT PUTAWAY_ID, PUTAWAY_NO, SOURCE_NO, PUTAWAY_TYPE, PUTAWAY_TYPE_NAME, TRAY_CODE, PALLET_CODE, SBIN_CODE, EBIN_CODE, PUTAWAY_PRIORITY, PUTAWAY_STATUS, PUTAWAY_STATUS_NAME, [DESCRIBE], CREATE_BY, CREATE_NAME, CREATE_TIME, UPDATE_BY, UPDATE_NAME, UPDATE_TIME FROM VW_WMS_IN_PUTAWAY WHERE 1=1 {sqlCondition} ORDER BY UPDATE_TIME DESC,CREATE_TIME DESC OFFSET {pageStartIndex} ROWS FETCH NEXT {putawaySearchMd.EveryPageQty} ROWS ONLY "); #endregion int dataCount = Convert.ToInt32(new DataRepository(_dataContext).ExecuteScalar(sqlCountPutAwayData.ToString())); List resultList = new DataRepository(_dataContext).Query(sqlQueryPutAwayData.ToString()).ToList(); OperateResultInfo> retDataMsg = SuccessStatus(resultList); retDataMsg.DataCount = dataCount; return retDataMsg; } catch (Exception ex) { return FailMessageStatus>($"查询上架单数据发生异常，【{ex.Message}】", null); } } /// /// 根据上架单主键Id查询上架单明细数据 /// /// 上架单对象 /// public OperateResultInfo GetWmsInPutAwayDtlListForID(WmsInPutAwayResult wmsInPutAway) { try { #region SQL语句生成 string sqlQueryWmsInPutAway = $@" SELECT PUTAWAY_ID, PUTAWAY_NO, SOURCE_NO, PUTAWAY_TYPE, PUTAWAY_TYPE_NAME, TRAY_CODE, PALLET_CODE, SBIN_CODE, EBIN_CODE, PUTAWAY_PRIORITY, PUTAWAY_STATUS, PUTAWAY_STATUS_NAME, [DESCRIBE], CREATE_BY, CREATE_NAME, CREATE_TIME, UPDATE_BY, UPDATE_NAME, UPDATE_TIME FROM VW_WMS_IN_PUTAWAY WHERE PUTAWAY_ID = '{wmsInPutAway.PutawayId}' "; string strWhere = string.Empty; if (wmsInPutAway.Remarks1 == "查询已删除明细数据") { strWhere = "1=1"; } else { strWhere = "PUTAWAY_DTL_STATUS < 111"; } string sqlQueryWmsPutAwayDtl = $@" SELECT * FROM VW_WMS_IN_PUTAWAY_DTL WHERE PUTAWAY_ID = '{wmsInPutAway.PutawayId}' AND {strWhere} ORDER BY PUTAWAY_DTL_STATUS,PUTAWAY_DTL_ID "; #endregion List resultList = new DataRepository(_dataContext).Query(sqlQueryWmsInPutAway).ToList(); List resultDtlList = new DataRepository(_dataContext).Query(sqlQueryWmsPutAwayDtl).ToList(); wmsInPutAway = resultList[0]; wmsInPutAway.WmsInPutAwayDtlList = resultDtlList; OperateResultInfo retDataMsg = SuccessStatus(wmsInPutAway); return retDataMsg; } catch (Exception ex) { return FailMessageStatus($"查询上架单明细数据发生异常，【{ex.Message}】", null); } } /// /// 新增上架单数据 /// /// 上架单主表对象 /// public OperateResultInfo AddWmsInPutAwayData(WmsInPutAwayResult wmsInPutAway) { try { #region SQL语句生成 List sqlList = new List(); wmsInPutAway.PutawayId = Convert.ToInt32(new DataRepository(_dataContext).GetSequenceMsg("InPutaway_Id")); wmsInPutAway.PutawayNo = new DataRepository(_dataContext).GetSequenceMsg("InPutaway_No"); wmsInPutAway.PutawayStatus = 0; StringBuilder sqlAddWmsPutAway = new StringBuilder(); sqlAddWmsPutAway.Append($@" INSERT INTO [WMS_IN_PUTAWAY] ( [PUTAWAY_ID], [PUTAWAY_NO], [SOURCE_NO], [PUTAWAY_TYPE], [TRAY_CODE], [PALLET_CODE], [SBIN_CODE], [EBIN_CODE], [PUTAWAY_PRIORITY], [PUTAWAY_STATUS], [DESCRIBE], [CREATE_BY], [CREATE_TIME], [UPDATE_BY], [UPDATE_TIME], [DATA_VERSION], [REMARKS1], [REMARKS2], [REMARKS3], [REMARKS4], [REMARKS5] ) VALUES ( '{wmsInPutAway.PutawayId}', '{wmsInPutAway.PutawayNo}', '{wmsInPutAway.SourceNo}', '{wmsInPutAway.PutawayType}', '{wmsInPutAway.TrayCode}', '{wmsInPutAway.PalletCode}', '{wmsInPutAway.SbinCode}', '{wmsInPutAway.EbinCode}', '{wmsInPutAway.PutawayPriority}', '0', '{wmsInPutAway.Describe}', '{wmsInPutAway.CreateBy}', getdate(), '{wmsInPutAway.UpdateBy}', getdate(), 0, NULL, NULL, NULL, NULL, NULL ); "); sqlList.Add(sqlAddWmsPutAway.ToString()); foreach (WmsInPutAwayDtlResult item in wmsInPutAway.WmsInPutAwayDtlList) { StringBuilder sqlAddWmsInPutAwayDtl = new StringBuilder(); item.PUTAWAY_DTL_ID = Convert.ToInt32(new DataRepository(_dataContext).GetSequenceMsg("InPutaway_Dtl_Id")); item.PUTAWAY_ID = wmsInPutAway.PutawayId; item.PUTAWAY_DTL_STATUS = 0; sqlAddWmsInPutAwayDtl.Append($@" INSERT INTO [WMS_IN_PUTAWAY_DTL] ( [PUTAWAY_DTL_ID], [PUTAWAY_ID], [MATERIEL_CODE], [MATERIEL_NAME], [MATERIEL_BARCODE], [MATERIEL_SPEC], [BATCH_NO], [PACKAGE_CODE], [UNIT_CODE], [PUTAWAY_QTY], [SUPPLIER_CODE], [SUPPLIER_NAME], [PRODUCT_DATE], [EXP_DATE], [PUTAWAY_DTL_STATUS], [INSPECTION_RESULT], [ITEM_STATUS], [DESCRIBE], [CREATE_BY], [CREATE_TIME], [UPDATE_BY], [UPDATE_TIME], [DATA_VERSION], [REMARKS1], [REMARKS2], [REMARKS3], [REMARKS4], [REMARKS5] ) VALUES ( '{item.PUTAWAY_DTL_ID}', '{item.PUTAWAY_ID}', '{item.MATERIEL_CODE}', '{item.MATERIEL_NAME}', '{item.MATERIEL_BARCODE}', '{item.MATERIEL_SPEC}', '{item.BATCH_NO}', '{item.PACKAGE_CODE}', '{item.UNIT_CODE}', '{item.PUTAWAY_QTY}', '{item.SUPPLIER_CODE}', '{item.SUPPLIER_NAME}', '{item.PRODUCT_DATE}', '{item.EXP_DATE}', '0', 'Wait', '1', '{item.DESCRIBE}', '{item.CREATE_BY}', getdate(), '{item.UPDATE_BY}', getdate(), 0, NULL, NULL, NULL, NULL, NULL ); "); sqlList.Add(sqlAddWmsInPutAwayDtl.ToString()); } #endregion int row = new DataRepository(_dataContext).ExecSqlListTran(sqlList); if (row > 0) { return SuccessMessageStatus("新增上架单数据成功！", row); } else { return FailMessageStatus("新增上架单数据失败！", row); } } catch (Exception ex) { return FailMessageStatus($"新增上架单数据发生异常，【{ex.Message}】"); } } /// /// 修改上架单数据 /// /// 上架主表对象 /// public OperateResultInfo EditWmsInPutAwayData(WmsInPutAwayResult wmsInPutAway) { try { #region SQL语句生成 List sqlList = new List(); string sqlUpdateWmsInPutAway = $@" UPDATE [WMS_IN_PUTAWAY] SET [PUTAWAY_NO] = '{wmsInPutAway.PutawayNo}', [SOURCE_NO] = '{wmsInPutAway.SourceNo}', [PUTAWAY_TYPE] = '{wmsInPutAway.PutawayType}', [TRAY_CODE] = '{wmsInPutAway.TrayCode}', [PALLET_CODE] = '{wmsInPutAway.PalletCode}', [SBIN_CODE] = '{wmsInPutAway.SbinCode}', [EBIN_CODE] = '{wmsInPutAway.EbinCode}', [PUTAWAY_PRIORITY] = '{wmsInPutAway.PutawayPriority}', [PUTAWAY_STATUS] = '{wmsInPutAway.PutawayStatus}', [DESCRIBE] = '{wmsInPutAway.Describe}', [UPDATE_BY] = '{wmsInPutAway.UpdateBy}', [UPDATE_TIME] = GETDATE(), [DATA_VERSION] = [DATA_VERSION] + 1 WHERE [PUTAWAY_ID] = '{wmsInPutAway.PutawayId}'; "; sqlList.Add(sqlUpdateWmsInPutAway); foreach (WmsInPutAwayDtlResult item in wmsInPutAway.WmsInPutAwayDtlList) { if (item.REMARKS1 == "更新" || item.REMARKS1 == "删除") { string sqlUpdateWmsInPutAwayDtl = $@" UPDATE [WMS_IN_PUTAWAY_DTL] SET [MATERIEL_CODE] = '{item.MATERIEL_CODE}', [MATERIEL_NAME] = '{item.MATERIEL_NAME}', [MATERIEL_BARCODE] = '{item.MATERIEL_BARCODE}', [MATERIEL_SPEC] = '{item.MATERIEL_SPEC}', [BATCH_NO] = '{item.BATCH_NO}', [PACKAGE_CODE] = '{item.PACKAGE_CODE}', [UNIT_CODE] = '{item.UNIT_CODE}', [PUTAWAY_QTY] = '{item.PUTAWAY_QTY}', [SUPPLIER_CODE] = '{item.SUPPLIER_CODE}', [SUPPLIER_NAME] = '{item.SUPPLIER_CODE}', [PRODUCT_DATE] = '{item.PRODUCT_DATE}', [EXP_DATE] = '{item.EXP_DATE}', [PUTAWAY_DTL_STATUS] = '{item.PUTAWAY_DTL_STATUS}', [INSPECTION_RESULT] = '{item.INSPECTION_RESULT}', [ITEM_STATUS] = '{item.ITEM_STATUS}', [DESCRIBE] = '{item.DESCRIBE}', [UPDATE_BY] = '{item.UPDATE_BY}', [UPDATE_TIME] = getdate(), [DATA_VERSION] = [DATA_VERSION] + 1 WHERE [PUTAWAY_DTL_ID] = '{item.PUTAWAY_DTL_ID}' AND [PUTAWAY_ID] = '{wmsInPutAway.PutawayId}'; "; sqlList.Add(sqlUpdateWmsInPutAwayDtl); } if (item.REMARKS1 == "添加") { StringBuilder sqlAddWmsInPutAwayDtl = new StringBuilder(); item.PUTAWAY_DTL_ID = Convert.ToInt32(new DataRepository(_dataContext).GetSequenceMsg("InPutaway_Dtl_Id")); item.PUTAWAY_ID = wmsInPutAway.PutawayId; item.PUTAWAY_DTL_STATUS = 0; sqlAddWmsInPutAwayDtl.Append($@" INSERT INTO [WMS_IN_PUTAWAY_DTL] ( [PUTAWAY_DTL_ID], [PUTAWAY_ID], [MATERIEL_CODE], [MATERIEL_NAME], [MATERIEL_BARCODE], [MATERIEL_SPEC], [BATCH_NO], [PACKAGE_CODE], [UNIT_CODE], [PUTAWAY_QTY], [SUPPLIER_CODE], [SUPPLIER_NAME], [PRODUCT_DATE], [EXP_DATE], [PUTAWAY_DTL_STATUS], [INSPECTION_RESULT], [ITEM_STATUS], [DESCRIBE], [CREATE_BY], [CREATE_TIME], [UPDATE_BY], [UPDATE_TIME], [DATA_VERSION], [REMARKS1], [REMARKS2], [REMARKS3], [REMARKS4], [REMARKS5] ) VALUES ( '{item.PUTAWAY_DTL_ID}', '{item.PUTAWAY_ID}', '{item.MATERIEL_CODE}', '{item.MATERIEL_NAME}', '{item.MATERIEL_BARCODE}', '{item.MATERIEL_SPEC}', '{item.BATCH_NO}', '{item.PACKAGE_CODE}', '{item.UNIT_CODE}', '{item.PUTAWAY_QTY}', '{item.SUPPLIER_CODE}', '{item.SUPPLIER_NAME}', '{item.PRODUCT_DATE}', '{item.EXP_DATE}', '0', 'Wait', '1', '{item.DESCRIBE}', '{item.CREATE_BY}', getdate(), '{item.UPDATE_BY}', getdate(), 0, NULL, NULL, NULL, NULL, NULL ); "); sqlList.Add(sqlAddWmsInPutAwayDtl.ToString()); } } #endregion int row = new DataRepository(_dataContext).ExecSqlListTran(sqlList); if (row > 0) { return SuccessMessageStatus("修改上架单数据成功！", row); } else { return FailMessageStatus("修改上架单数据失败！", row); } } catch (Exception ex) { return FailMessageStatus($"修改上架单数据发生异常，【{ex.Message}】"); } } /// /// 删除上架单数据 /// /// 上架单主表对象 /// public OperateResultInfo DeleteWmsInPutAwayData(WmsInPutAwayResult wmsInPutAway) { try { #region SQL语句生成 string[] putawayIdList = wmsInPutAway.PutawayNo.Split(','); List sqlList = new List(); foreach (string item in putawayIdList) { string sqlDeleteWmsPutAway = $@" UPDATE WMS_IN_PUTAWAY SET PUTAWAY_STATUS = '111', UPDATE_BY = '{wmsInPutAway.UpdateBy}', UPDATE_TIME = GETDATE(), DATA_VERSION = DATA_VERSION + 1 WHERE PUTAWAY_ID = '{item}'; "; string sqlDeleteWmsInPutAwayDtl = $@" UPDATE WMS_IN_PUTAWAY_DTL SET PUTAWAY_DTL_STATUS = '111', UPDATE_BY = '{wmsInPutAway.UpdateBy}', UPDATE_TIME = GETDATE(), DATA_VERSION = DATA_VERSION + 1 WHERE PUTAWAY_ID = '{item}'; "; sqlList.Add(sqlDeleteWmsPutAway); sqlList.Add(sqlDeleteWmsInPutAwayDtl); /* ToDo: 后续增加删除移至历史表中。 */ } #endregion int row = new DataRepository(_dataContext).ExecSqlListTran(sqlList); if (row > 0) { return SuccessMessageStatus("删除上架单数据成功！", row); } else { return FailMessageStatus("删除上架单数据失败！", row); } } catch (Exception ex) { return FailMessageStatus($"删除上架单数据发生异常，【{ex.Message}】"); } } /// /// 获取待上架托盘信息 /// /// public OperateResultInfo> GetPutAwayPalletMsgList() { try { string sql = $@"SELECT * FROM VW_WMS_IN_PUTAWAY_PALLET_MSG"; List resultList = new DataRepository(_dataContext).Query(sql).ToList(); OperateResultInfo> retDataMsg = SuccessStatus(resultList); return retDataMsg; } catch (Exception ex) { return FailMessageStatus>($"查询待上架托盘数据发生异常，【{ex.Message}】", null); } } /// /// 手动上架 /// /// 上架单主表实体类对象 /// public OperateResultInfo ManualPutAwayPallet(WmsInPutAwayResult wmsInPutAway, PutAwayMethodsEnum methodsEnum) { try { #region SQL语句生成 List sqlList = new List(); //检查目标货位是否存在 string sqlQueryRetreatRegionBinMsg = $@"SELECT A.*, B.USER_NAME Create_Name, C.USER_NAME Update_Name FROM BAS_BIN A LEFT JOIN SYS_USER B ON A.CREATE_BY = B.USER_ID LEFT JOIN SYS_USER C ON A.UPDATE_BY = C.USER_ID WHERE A.BIN_CODE = '{wmsInPutAway.EbinCode}'"; List resultBinList = new DataRepository(_dataContext).Query(sqlQueryRetreatRegionBinMsg).ToList(); if (resultBinList.Count <= 0) { return FailMessageStatus($"上架库位【{wmsInPutAway.EbinCode}】不存在，请重新输入或扫描。"); } WmsInPutAwayResult wmsInPutAwayInDataBase; if (methodsEnum == PutAwayMethodsEnum.WMS手动上架 || methodsEnum == PutAwayMethodsEnum.PDA手动上架) { string sqlQueryWmsInPutAway = $@" SELECT PUTAWAY_ID, PUTAWAY_NO, SOURCE_NO, PUTAWAY_TYPE, PUTAWAY_TYPE_NAME, TRAY_CODE, PALLET_CODE, SBIN_CODE, EBIN_CODE, PUTAWAY_PRIORITY, PUTAWAY_STATUS, PUTAWAY_STATUS_NAME, [DESCRIBE], CREATE_BY, CREATE_NAME, CREATE_TIME, UPDATE_BY, UPDATE_NAME, UPDATE_TIME FROM VW_WMS_IN_PUTAWAY WHERE TRAY_CODE = '{wmsInPutAway.TrayCode}' AND PALLET_CODE = '{wmsInPutAway.PalletCode}' AND PUTAWAY_STATUS < 99 "; List resultPuatAwayList = new DataRepository(_dataContext).Query(sqlQueryWmsInPutAway).ToList(); wmsInPutAwayInDataBase = resultPuatAwayList[0]; } else { wmsInPutAwayInDataBase = wmsInPutAway; } string sqlUpdatePutAway = $@" UPDATE WMS_IN_PUTAWAY SET PUTAWAY_STATUS = 99, UPDATE_BY = '{wmsInPutAway.UpdateBy}', UPDATE_TIME = GETDATE(), DATA_VERSION = DATA_VERSION + 1 WHERE PUTAWAY_ID = '{wmsInPutAwayInDataBase.PutawayId}' "; sqlList.Add(sqlUpdatePutAway); string sqlUpdatePutAwayDtl = $@" UPDATE WMS_IN_PUTAWAY_DTL SET PUTAWAY_DTL_STATUS = 99, UPDATE_BY = '{wmsInPutAway.UpdateBy}', UPDATE_TIME = GETDATE(), DATA_VERSION = DATA_VERSION + 1 WHERE PUTAWAY_ID = '{wmsInPutAwayInDataBase.PutawayId}' "; sqlList.Add(sqlUpdatePutAwayDtl); string sqlQueryWmsPutAwayDtl = $@" SELECT * FROM VW_WMS_IN_PUTAWAY_DTL WHERE PUTAWAY_NO = '{wmsInPutAwayInDataBase.PutawayNo}'; "; List resultDtlList = new DataRepository(_dataContext).Query(sqlQueryWmsPutAwayDtl).ToList(); #region 孙亚龙 20210515 舍弃代码 //foreach (WmsInPutAwayDtlResult item in resultDtlList) //{ // if (wmsInPutAway.PutawayType == 1) // { // string sqlQueryReceiptRecord = $@"SELECT * FROM VW_WMS_IN_RECEIPT_RECORD WHERE TRAY_CODE = '{wmsInPutAway.TrayCode}' AND PALLET_CODE = '{wmsInPutAway.PalletCode}' AND RECEIPT_RECORD_STATUS = 0"; // List resultReceiptRecordList = new DataRepository(_dataContext).Query(sqlQueryReceiptRecord).ToList(); // WmsInReceiptRecordResult recRecordMd = resultReceiptRecordList.Find(x => x.MATERIEL_CODE == item.MATERIEL_CODE && x.BATCH_NO == item.BATCH_NO); // string sqlQueryWmsReceiptDtl = $@" // SELECT // * // FROM // VW_WMS_IN_RECEIPT_DTL // WHERE // RECEIPT_DTL_ID = '{recRecordMd.RECEIPT_DTL_ID}' // "; // List resultRetreatDtlList = new DataRepository(_dataContext).Query(sqlQueryWmsReceiptDtl).ToList(); // if (resultRetreatDtlList[0].PUTAWAY_QTY == 0) // { // string sqlUpdateReceiptDtl = $@" // UPDATE WMS_IN_RECEIPT_DTL // SET PUTAWAY_QTY = PUTAWAY_QTY + '{item.PUTAWAY_QTY}', // UPDATE_BY = '{wmsInPutAway.UpdateBy}', // UPDATE_TIME = GETDATE(), // DATA_VERSION = DATA_VERSION + 1 // WHERE // RECEIPT_DTL_ID = '{recRecordMd.RECEIPT_DTL_ID}'; // "; // sqlList.Add(sqlUpdateReceiptDtl); // string sqlUpdateReceiptDtl1 = $@" // IF NOT EXISTS ( // SELECT // 1 // FROM // WMS_IN_RECEIPT_DTL // WHERE // RECEIPT_DTL_QTY < PUTAWAY_QTY // AND RECEIPT_DTL_ID = '{recRecordMd.RECEIPT_DTL_ID}' // ) UPDATE WMS_IN_RECEIPT_DTL // SET RECEIPT_DTL_STATUS = 99 // WHERE // RECEIPT_DTL_ID = '{recRecordMd.RECEIPT_DTL_ID}'; // "; // sqlList.Add(sqlUpdateReceiptDtl1); // } // else // { // string sqlUpdateReceiptDtl = $@" // IF NOT EXISTS ( // SELECT // 1 // FROM // WMS_IN_RECEIPT_DTL // WHERE // RECEIPT_DTL_QTY < PUTAWAY_QTY // AND RECEIPT_DTL_ID = '{recRecordMd.RECEIPT_DTL_ID}' // ) UPDATE WMS_IN_RECEIPT_DTL // SET PUTAWAY_QTY = PUTAWAY_QTY + '{item.PUTAWAY_QTY}', // RECEIPT_DTL_STATUS = 99, // UPDATE_BY = '{wmsInPutAway.UpdateBy}', // UPDATE_TIME = GETDATE(), // DATA_VERSION = DATA_VERSION + 1 // WHERE // RECEIPT_DTL_ID = '{recRecordMd.RECEIPT_DTL_ID}' ; // ELSE // UPDATE WMS_IN_RECEIPT_DTL // SET PUTAWAY_QTY = PUTAWAY_QTY + '{item.PUTAWAY_QTY}', // UPDATE_BY = '{wmsInPutAway.UpdateBy}', // UPDATE_TIME = GETDATE(), // DATA_VERSION = DATA_VERSION + 1 // WHERE // RECEIPT_DTL_ID = '{recRecordMd.RECEIPT_DTL_ID}'; // "; // sqlList.Add(sqlUpdateReceiptDtl); // } // } // else if (wmsInPutAway.PutawayType == 2) // { // string sqlQueryRetreatRecord = $@"SELECT * FROM VW_WMS_IN_RETREAT_RECORD WHERE TRAY_CODE = '{wmsInPutAway.TrayCode}' AND PALLET_CODE = '{wmsInPutAway.PalletCode}' AND RETREAT_RECORD_STATUS = 0"; // List resultRetreatRecordList = new DataRepository(_dataContext).Query(sqlQueryRetreatRecord).ToList(); // WmsInRetreatRecordResult retRecordMd = resultRetreatRecordList.Find(x => x.MATERIEL_CODE == item.MATERIEL_CODE && x.BATCH_NO == item.BATCH_NO); // string sqlQueryWmsReceiptDtl = $@" // SELECT // * // FROM // VW_WMS_IN_RETREAT_DTL // WHERE // RETREAT_DTL_ID = '{retRecordMd.RETREAT_DTL_ID}' // "; // List resultRetreatDtlList = new DataRepository(_dataContext).Query(sqlQueryWmsReceiptDtl).ToList(); // if (resultRetreatDtlList[0].PUTAWAY_QTY == 0) // { // string sqlUpdateReceiptDtl = $@" // UPDATE WMS_IN_RETREAT_DTL // SET PUTAWAY_QTY = PUTAWAY_QTY + '{item.PUTAWAY_QTY}', // UPDATE_BY = '{wmsInPutAway.UpdateBy}', // UPDATE_TIME = GETDATE(), // DATA_VERSION = DATA_VERSION + 1 // WHERE // RETREAT_DTL_ID = '{retRecordMd.RETREAT_DTL_ID}'; // "; // sqlList.Add(sqlUpdateReceiptDtl); // } // else // { // string sqlUpdateReceiptDtl = $@" // IF NOT EXISTS ( // SELECT // 1 // FROM // WMS_IN_RETREAT_DTL // WHERE // RETREAT_QTY < PUTAWAY_QTY // AND RETREAT_DTL_ID = '{retRecordMd.RETREAT_DTL_ID}' // ) UPDATE WMS_IN_RETREAT_DTL // SET PUTAWAY_QTY = PUTAWAY_QTY + '{item.PUTAWAY_QTY}', // RETREAT_DTL_STATUS = 99, // UPDATE_BY = '{wmsInPutAway.UpdateBy}', // UPDATE_TIME = GETDATE(), // DATA_VERSION = DATA_VERSION + 1 // WHERE // RETREAT_DTL_ID = '{retRecordMd.RETREAT_DTL_ID}' ; // ELSE // UPDATE WMS_IN_RETREAT_DTL // SET PUTAWAY_QTY = PUTAWAY_QTY + '{item.PUTAWAY_QTY}', // UPDATE_BY = '{wmsInPutAway.UpdateBy}', // UPDATE_TIME = GETDATE(), // DATA_VERSION = DATA_VERSION + 1 // WHERE // RETREAT_DTL_ID = '{retRecordMd.RETREAT_DTL_ID}'; // "; // sqlList.Add(sqlUpdateReceiptDtl); // } // } // else if (wmsInPutAway.PutawayType == 6) // { // } // else // { // return FailMessageStatus($"上架单类型暂时不能手动上架。"); // } //} #endregion string status = string.Empty; if (wmsInPutAway.PutawayType == 1) { string sqlQueryReceiptRecord = $@"SELECT * FROM VW_WMS_IN_RECEIPT_RECORD WHERE TRAY_CODE = '{wmsInPutAway.TrayCode}' AND PALLET_CODE = '{wmsInPutAway.PalletCode}' AND RECEIPT_RECORD_STATUS = 0"; List resultReceiptRecordList = new DataRepository(_dataContext).Query(sqlQueryReceiptRecord).ToList(); List tmpReceiptIdLst = new List(); foreach (WmsInReceiptRecordResult recRecordMd in resultReceiptRecordList) { //WmsInReceiptRecordResult recRecordMd = resultReceiptRecordList.Find(x => x.MATERIEL_CODE == item.MATERIEL_CODE && x.BATCH_NO == item.BATCH_NO); int receiptId = tmpReceiptIdLst.FirstOrDefault(x => x == recRecordMd.RECEIPT_ID); if (receiptId <= 0) { tmpReceiptIdLst.Add(recRecordMd.RECEIPT_ID); } string sqlQueryWmsReceiptDtl = $@" SELECT * FROM VW_WMS_IN_RECEIPT_DTL WHERE RECEIPT_DTL_ID = '{recRecordMd.RECEIPT_DTL_ID}' "; List resultRetreatDtlList = new DataRepository(_dataContext).Query(sqlQueryWmsReceiptDtl).ToList(); if (resultRetreatDtlList[0].PutawayQty == 0) { string sqlUpdateReceiptDtl = $@" UPDATE WMS_IN_RECEIPT_DTL SET PUTAWAY_QTY = PUTAWAY_QTY + '{recRecordMd.RECEIPT_QTY}', UPDATE_BY = '{wmsInPutAway.UpdateBy}', UPDATE_TIME = GETDATE(), DATA_VERSION = DATA_VERSION + 1 WHERE RECEIPT_DTL_ID = '{recRecordMd.RECEIPT_DTL_ID}'; "; sqlList.Add(sqlUpdateReceiptDtl); string sqlUpdateReceiptDtl1 = $@" IF NOT EXISTS ( SELECT 1 FROM WMS_IN_RECEIPT_DTL WHERE RECEIPT_DTL_QTY < PUTAWAY_QTY AND RECEIPT_DTL_ID = '{recRecordMd.RECEIPT_DTL_ID}' ) UPDATE WMS_IN_RECEIPT_DTL SET RECEIPT_DTL_STATUS = 99 WHERE RECEIPT_DTL_ID = '{recRecordMd.RECEIPT_DTL_ID}'; "; sqlList.Add(sqlUpdateReceiptDtl1); } else { string sqlUpdateReceiptDtl = $@" IF NOT EXISTS ( SELECT 1 FROM WMS_IN_RECEIPT_DTL WHERE RECEIPT_DTL_QTY < PUTAWAY_QTY AND RECEIPT_DTL_ID = '{recRecordMd.RECEIPT_DTL_ID}' ) UPDATE WMS_IN_RECEIPT_DTL SET PUTAWAY_QTY = PUTAWAY_QTY + '{recRecordMd.RECEIPT_QTY}', RECEIPT_DTL_STATUS = 99, UPDATE_BY = '{wmsInPutAway.UpdateBy}', UPDATE_TIME = GETDATE(), DATA_VERSION = DATA_VERSION + 1 WHERE RECEIPT_DTL_ID = '{recRecordMd.RECEIPT_DTL_ID}' ; ELSE UPDATE WMS_IN_RECEIPT_DTL SET PUTAWAY_QTY = PUTAWAY_QTY + '{recRecordMd.RECEIPT_QTY}', UPDATE_BY = '{wmsInPutAway.UpdateBy}', UPDATE_TIME = GETDATE(), DATA_VERSION = DATA_VERSION + 1 WHERE RECEIPT_DTL_ID = '{recRecordMd.RECEIPT_DTL_ID}'; "; sqlList.Add(sqlUpdateReceiptDtl); } } status = "55"; foreach (int item in tmpReceiptIdLst) { string sqlUpdateReceipt = $@" IF NOT EXISTS ( SELECT 1 FROM VW_WMS_IN_RECEIPT_DTL WHERE RECEIPT_ID = '{item}' AND RECEIPT_DTL_STATUS < 99 ) UPDATE WMS_IN_RECEIPT SET RECEIPT_STATUS = 99, UPDATE_BY = '{wmsInPutAway.UpdateBy}', UPDATE_TIME = GETDATE(), DATA_VERSION = DATA_VERSION + 1 WHERE RECEIPT_ID = '{item}' "; sqlList.Add(sqlUpdateReceipt); } string sqlUpdateReceiptRecord = $@" UPDATE WMS_IN_RECEIPT_RECORD SET RECEIPT_RECORD_STATUS = 1, UPDATE_BY = '{wmsInPutAway.UpdateBy}', UPDATE_TIME = GETDATE(), BIN_CODE='{wmsInPutAway.EbinCode}', DATA_VERSION = DATA_VERSION + 1 WHERE TRAY_CODE = '{wmsInPutAway.TrayCode}' AND PALLET_CODE = '{wmsInPutAway.PalletCode}' AND RECEIPT_RECORD_STATUS = 0; ";// 收货组盘完成 sqlList.Add(sqlUpdateReceiptRecord); } else if (wmsInPutAway.PutawayType == 2) { string sqlQueryRetreatRecord = $@"SELECT * FROM VW_WMS_IN_RETREAT_RECORD WHERE TRAY_CODE = '{wmsInPutAway.TrayCode}' AND PALLET_CODE = '{wmsInPutAway.PalletCode}' AND RETREAT_RECORD_STATUS = 0"; List resultRetreatRecordList = new DataRepository(_dataContext).Query(sqlQueryRetreatRecord).ToList(); List tmpRetreatIdLst = new List(); foreach (WmsInRetreatRecordResult retRecordMd in resultRetreatRecordList) { //WmsInRetreatRecordResult retRecordMd = resultRetreatRecordList.Find(x => x.MATERIEL_CODE == item.MATERIEL_CODE && x.BATCH_NO == item.BATCH_NO); int receiptId = tmpRetreatIdLst.FirstOrDefault(x => x == retRecordMd.RETREAT_ID); if (receiptId <= 0) { tmpRetreatIdLst.Add(retRecordMd.RETREAT_ID); } string sqlQueryWmsReceiptDtl = $@" SELECT * FROM VW_WMS_IN_RETREAT_DTL WHERE RETREAT_DTL_ID = '{retRecordMd.RETREAT_DTL_ID}' "; List resultRetreatDtlList = new DataRepository(_dataContext).Query(sqlQueryWmsReceiptDtl).ToList(); if (resultRetreatDtlList[0].PUTAWAY_QTY == 0) { string sqlUpdateReceiptDtl = $@" UPDATE WMS_IN_RETREAT_DTL SET PUTAWAY_QTY = PUTAWAY_QTY + '{retRecordMd.RETREAT_QTY}', UPDATE_BY = '{wmsInPutAway.UpdateBy}', UPDATE_TIME = GETDATE(), DATA_VERSION = DATA_VERSION + 1 WHERE RETREAT_DTL_ID = '{retRecordMd.RETREAT_DTL_ID}'; "; sqlList.Add(sqlUpdateReceiptDtl); } else { string sqlUpdateReceiptDtl = $@" IF NOT EXISTS ( SELECT 1 FROM WMS_IN_RETREAT_DTL WHERE RETREAT_QTY < PUTAWAY_QTY AND RETREAT_DTL_ID = '{retRecordMd.RETREAT_DTL_ID}' ) UPDATE WMS_IN_RETREAT_DTL SET PUTAWAY_QTY = PUTAWAY_QTY + '{retRecordMd.RETREAT_QTY}', RETREAT_DTL_STATUS = 99, UPDATE_BY = '{wmsInPutAway.UpdateBy}', UPDATE_TIME = GETDATE(), DATA_VERSION = DATA_VERSION + 1 WHERE RETREAT_DTL_ID = '{retRecordMd.RETREAT_DTL_ID}' ; ELSE UPDATE WMS_IN_RETREAT_DTL SET PUTAWAY_QTY = PUTAWAY_QTY + '{retRecordMd.RETREAT_QTY}', UPDATE_BY = '{wmsInPutAway.UpdateBy}', UPDATE_TIME = GETDATE(), DATA_VERSION = DATA_VERSION + 1 WHERE RETREAT_DTL_ID = '{retRecordMd.RETREAT_DTL_ID}'; "; sqlList.Add(sqlUpdateReceiptDtl); } } status = "55"; foreach (int item in tmpRetreatIdLst) { string sqlUpdateReceipt = $@" IF NOT EXISTS ( SELECT 1 FROM VW_WMS_IN_RETREAT_DTL WHERE RETREAT_ID = '{item}' AND RETREAT_DTL_STATUS < 99 ) UPDATE WMS_IN_RETREAT SET RETREAT_STATUS = 99, UPDATE_BY = '{wmsInPutAway.UpdateBy}', UPDATE_TIME = GETDATE(), DATA_VERSION = DATA_VERSION + 1 WHERE RETREAT_ID = '{item}' "; sqlList.Add(sqlUpdateReceipt); } string sqlUpdateReceiptRecord = $@" UPDATE WMS_IN_RETREAT_RECORD SET RETREAT_RECORD_STATUS = 1, UPDATE_BY = '{wmsInPutAway.UpdateBy}', UPDATE_TIME = GETDATE(), DATA_VERSION = DATA_VERSION + 1 WHERE TRAY_CODE = '{wmsInPutAway.TrayCode}' AND PALLET_CODE = '{wmsInPutAway.PalletCode}' AND RETREAT_RECORD_STATUS = 0; "; sqlList.Add(sqlUpdateReceiptRecord); } else if (wmsInPutAway.PutawayType == 6) { status = "54"; } else { return FailMessageStatus($"上架单类型暂时不能手动上架。"); } string sqlUpdateWmsStkTray = $@" UPDATE WMS_STK_TRAY SET TRAY_STATUS = '{status}', UPDATE_BY = '{wmsInPutAway.UpdateBy}', UPDATE_TIME = GETDATE(), DATA_VERSION = DATA_VERSION + 1 WHERE TRAY_CODE = '{wmsInPutAway.TrayCode}'; "; string sqlUpdateWmsStkTrayDtl = $@" UPDATE WMS_STK_TRAY_DTL SET TRAY_DTL_STATUS = '{status}', UPDATE_BY = '{wmsInPutAway.UpdateBy}', UPDATE_TIME = GETDATE(), DATA_VERSION = DATA_VERSION + 1 WHERE TRAY_ID IN ( SELECT TRAY_ID FROM WMS_STK_TRAY WHERE TRAY_CODE = '{wmsInPutAway.TrayCode}' ); "; string sqlQueryWmsStkTray = $@"SELECT * FROM VW_WMS_STK_TRAY WHERE TRAY_CODE = '{wmsInPutAway.TrayCode}' AND TRAY_STATUS < 99"; List resultWmsStkTrayList = new DataRepository(_dataContext).Query(sqlQueryWmsStkTray).ToList(); string sqlAddWmsStkBalance = $@" INSERT INTO [WMS_STK_BALANCE] ( [BALANCE_ID], [AREA_CODE], [AREA_NAME], [WAREHOUSE_CODE], [WAREHOUSE_NAME], [REGION_CODE], [REGION_NAME], [BIN_CODE], [BIN_NAME], [TRAY_ID], [TRAY_CODE], [PALLET_CODE], [INWH_TIME], [BALANCE_STATUS], [DESCRIBE], [CREATE_BY], [CREATE_TIME], [UPDATE_BY], [UPDATE_TIME], [DATA_VERSION], [REMARKS1], [REMARKS2], [REMARKS3], [REMARKS4], [REMARKS5] ) VALUES ( '{Convert.ToInt32(new DataRepository(_dataContext).GetSequenceMsg("Balance_Id"))}', 'AreaTest1', '区域测试1', 'WarehouseTest1', '仓库测试1', '{resultBinList[0].REGION_CODE}', '{resultBinList[0].REGION_NAME}', '{resultBinList[0].BIN_CODE}', '{resultBinList[0].BIN_NAME}', '{resultWmsStkTrayList[0].TRAY_ID}', '{wmsInPutAway.TrayCode}', '{wmsInPutAway.PalletCode}', getdate(), '55', '{wmsInPutAwayInDataBase.Describe}', '{wmsInPutAway.CreateBy}', getdate(), '{wmsInPutAway.UpdateBy}', getdate(), 0, NULL, NULL, NULL, NULL, NULL ); "; sqlList.Add($@"update WMS_STK_BALANCE set BALANCE_STATUS = 99,UPDATE_BY = '{wmsInPutAway.UpdateBy}',UPDATE_TIME = getdate(),DATA_VERSION = DATA_VERSION + 1 where TRAY_CODE = '{wmsInPutAway.TrayCode}'"); sqlList.Add(sqlUpdateWmsStkTray); sqlList.Add(sqlUpdateWmsStkTrayDtl); if (wmsInPutAway.PutawayType == 6) { sqlList.Add($"update WMS_STK_BALANCE set BALANCE_STATUS = '{status}',UPDATE_BY = '{wmsInPutAway.UpdateBy}',UPDATE_TIME = getdate(),DATA_VERSION = DATA_VERSION + 1 where TRAY_CODE in (select TRAY_CODE from WMS_STK_INVENTORY_DTL where TRAY_CODE = '{wmsInPutAway.TrayCode}')"); } else { sqlList.Add(sqlAddWmsStkBalance); } sqlList.Add($@" UPDATE WMS_TSK_TASK SET CLOC_CODE = ELOC_CODE, UPDATE_BY = '{wmsInPutAway.UpdateBy}', UPDATE_TIME = GETDATE(), DATA_VERSION = DATA_VERSION + 1, TASK_STATUS = 99, TASK_MSG = '【{methodsEnum}】-- 更新WMS大任务为：【任务完成】状态' WHERE TRAY_CODE = '{wmsInPutAway.TrayCode}' AND TASK_STATUS < 99 "); #endregion int row = new DataRepository(_dataContext).ExecSqlListTran(sqlList); if (row > 0) { return SuccessMessageStatus($"【{methodsEnum}】-- 操作成功！", row); } else { return FailMessageStatus($"【{methodsEnum}】-- 操作失败！", row); } } catch (Exception ex) { return FailMessageStatus($"【{methodsEnum}】-- 操作发生异常，【{ex.Message}】"); } } #region 2021 0224 孙亚龙新增WCS上报托盘运行状态 public OperateResultInfo UploadPalletRunStatus(NoticeWmsPalletStatus parm) { try { int wmsTaskStatus; if (parm.palletCurrRunStatus == PalletTaskRunStatusEnum.托盘入库执行中) { wmsTaskStatus = 55; } else { wmsTaskStatus = 77; } string sqlQueryWmsTask = $@" SELECT * FROM VW_WMS_TSK_TASK WHERE TASK_NO = '{parm.TaskNo}' AND TASK_STATUS = '{wmsTaskStatus}' "; List resultList = new DataRepository(_dataContext).Query(sqlQueryWmsTask).ToList(); if (resultList == null || resultList.Count <= 0) { return FailMessageStatus($"WCS上报托盘运行状态的参数错误，无法获取WMS大任务信息！"); } string sqlQueryWmsInPutAway = $@" SELECT PUTAWAY_ID, PUTAWAY_NO, SOURCE_NO, PUTAWAY_TYPE, PUTAWAY_TYPE_NAME, TRAY_CODE, PALLET_CODE, SBIN_CODE, EBIN_CODE, PUTAWAY_PRIORITY, PUTAWAY_STATUS, PUTAWAY_STATUS_NAME, [DESCRIBE], CREATE_BY, CREATE_NAME, CREATE_TIME, UPDATE_BY, UPDATE_NAME, UPDATE_TIME FROM VW_WMS_IN_PUTAWAY WHERE TRAY_CODE = '{parm.TrayCode}' AND PUTAWAY_STATUS < 99 "; List resultPuatAwayList = new DataRepository(_dataContext).Query(sqlQueryWmsInPutAway).ToList(); if (resultPuatAwayList == null || resultPuatAwayList.Count <= 0) { return FailMessageStatus($"WCS上报托盘运行状态的参数错误，无法获取托盘上架单信息！"); } WmsTaskResult wmsTaskResultDataBase = resultList[0]; WmsInPutAwayResult wmsInPutAwayInDataBase = resultPuatAwayList[0]; if (parm.palletCurrRunStatus == PalletTaskRunStatusEnum.托盘入库执行中) { List sqlList = new List(); string sqlUpdatePutAway = $@" UPDATE WMS_IN_PUTAWAY SET PUTAWAY_STATUS = 55, SBIN_CODE = '{wmsTaskResultDataBase.SBIN_CODE}', EBIN_CODE = '{wmsTaskResultDataBase.EBIN_CODE}', UPDATE_BY = '{parm.OperateUserId}', UPDATE_TIME = GETDATE(), DATA_VERSION = DATA_VERSION + 1 WHERE PUTAWAY_ID = '{wmsInPutAwayInDataBase.PutawayId}' "; sqlList.Add(sqlUpdatePutAway); string sqlUpdatePutAwayDtl = $@" UPDATE WMS_IN_PUTAWAY_DTL SET PUTAWAY_DTL_STATUS = 55, UPDATE_BY = '{parm.OperateUserId}', UPDATE_TIME = GETDATE(), DATA_VERSION = DATA_VERSION + 1 WHERE PUTAWAY_ID = '{wmsInPutAwayInDataBase.PutawayId}' "; sqlList.Add(sqlUpdatePutAwayDtl); int row = new DataRepository(_dataContext).ExecSqlListTran(sqlList); if (row > 0) { return SuccessMessageStatus("WCS上报托盘运行状态成功！", row); } else { return FailMessageStatus("WCS上报托盘运行状态失败！", row); } } else { return ManualPutAwayPallet(wmsInPutAwayInDataBase, PutAwayMethodsEnum.WCS自动上架); } } catch (Exception ex) { return FailMessageStatus($"WCS上报托盘运行状态函数发生异常：【{ex.Message}】"); } } #endregion } }