using DapperORMCore.Context.DataContext; using DapperORMCore.Model.CoreModel; using DapperORMCore.Repository.IRepositorys; using Microsoft.Extensions.Configuration; 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.Instock; using NXWMS.Model.Common; 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(IWmsInRetreatService), InjectType.Scope)] public class WmsInRetreatService : ServiceBase, IWmsInRetreatService { #region 全局变量、构造注入 /// /// 系统操作仓储中转 /// private IDataRepositoryContext _dataContext; /// /// SQL节点仓储 /// private ISQLNodeRepository _iSQLNodeRepository; /// /// 配置 /// private IConfiguration _configuration; /// /// 构造注入 /// /// /// /// public WmsInRetreatService(IDataRepositoryContext dataRepositoryContext, IConfiguration configuration, ISQLNodeRepository iSQLNodeRepository) { this._dataContext = dataRepositoryContext; this._configuration = configuration; this._iSQLNodeRepository = iSQLNodeRepository; } #endregion /// /// 分页查询退料单主表数据 /// /// 退料单查询条件实体类对象 /// public OperateResultInfo> GetWmsInRetreatListForPage(WmsInRetreatSearchMd retreatSearchMd) { try { #region SQL语句生成 StringBuilder sqlCondition = new StringBuilder(); if (!string.IsNullOrEmpty(retreatSearchMd.RetreatNoMsg)) { sqlCondition.Append($" AND RETREAT_NO = '{retreatSearchMd.RetreatNoMsg}'"); } if (!string.IsNullOrEmpty(retreatSearchMd.SourceNoMsg)) { sqlCondition.Append($" AND SOUCE_NO = '{retreatSearchMd.SourceNoMsg}'"); } if (!string.IsNullOrEmpty(retreatSearchMd.RetreaterMsg)) { sqlCondition.Append($" AND RETREATER = '{retreatSearchMd.RetreaterMsg}'"); } if (!string.IsNullOrEmpty(retreatSearchMd.RetreatType)) { sqlCondition.Append($" AND RETREAT_TYPE = '{retreatSearchMd.RetreatType}'"); } if (!string.IsNullOrEmpty(retreatSearchMd.RetreatStatus)) { sqlCondition.Append($" AND RETREAT_STATUS = '{retreatSearchMd.RetreatStatus}'"); } if (!string.IsNullOrEmpty(retreatSearchMd.BatchNoMsg)) { sqlCondition.Append($" AND RETREAT_ID IN (SELECT RETREAT_ID FROM VW_WMS_IN_RETREAT_DTL WHERE BATCH_NO = '{retreatSearchMd.BatchNoMsg}')"); } if (!string.IsNullOrEmpty(retreatSearchMd.MaterielMsg)) { sqlCondition.Append($" AND RETREAT_ID IN (SELECT RETREAT_ID FROM VW_WMS_IN_RETREAT_DTL WHERE MATERIEL_CODE LIKE '%{retreatSearchMd.MaterielMsg}%' OR MATERIEL_NAME LIKE '%{retreatSearchMd.MaterielMsg}%' OR MATERIEL_BARCODE LIKE '%{retreatSearchMd.MaterielMsg}%')"); } if (!string.IsNullOrEmpty(retreatSearchMd.SupplierMsg)) { sqlCondition.Append($" AND RETREAT_ID IN (SELECT RETREAT_ID FROM VW_WMS_IN_RETREAT_DTL WHERE SUPPLIER_CODE LIKE '%{retreatSearchMd.SupplierMsg}%' OR SUPPLIER_NAME LIKE '%{retreatSearchMd.SupplierMsg}%')"); } if (!string.IsNullOrEmpty(retreatSearchMd.StartCreateTime)) { sqlCondition.Append($" AND CREATE_TIME >= '{retreatSearchMd.StartCreateTime}'"); } if (!string.IsNullOrEmpty(retreatSearchMd.EndCreateTime)) { sqlCondition.Append($" AND CREATE_TIME <= '{retreatSearchMd.EndCreateTime}'"); } if (!string.IsNullOrEmpty(retreatSearchMd.StartProductTime)) { sqlCondition.Append($" AND RETREAT_ID IN (SELECT RETREAT_ID FROM VW_WMS_IN_RETREAT_DTL WHERE PRODUCT_DATE >= '{retreatSearchMd.StartProductTime}')"); } if (!string.IsNullOrEmpty(retreatSearchMd.EndProductTime)) { sqlCondition.Append($" AND RETREAT_ID IN (SELECT RETREAT_ID FROM VW_WMS_IN_RETREAT_DTL WHERE PRODUCT_DATE <= '{retreatSearchMd.EndProductTime}')"); } if (!string.IsNullOrEmpty(retreatSearchMd.StartExpTime)) { sqlCondition.Append($" AND RETREAT_ID IN (SELECT RETREAT_ID FROM VW_WMS_IN_RETREAT_DTL WHERE EXP_DATE >= '{retreatSearchMd.StartExpTime}')"); } if (!string.IsNullOrEmpty(retreatSearchMd.EndExpTime)) { sqlCondition.Append($" AND RETREAT_ID IN (SELECT RETREAT_ID FROM VW_WMS_IN_RETREAT_DTL WHERE EXP_DATE <= '{retreatSearchMd.EndExpTime}')"); } StringBuilder sqlCountRetreatData = new StringBuilder($@"SELECT COUNT(1) FROM VW_WMS_IN_RETREAT WHERE 1=1"); sqlCountRetreatData.Append(sqlCondition.ToString()); int pageStartIndex = (retreatSearchMd.PageNum - 1) * retreatSearchMd.EveryPageQty; int pageEndIndex = retreatSearchMd.PageNum * retreatSearchMd.EveryPageQty; StringBuilder sqlQueryRetreatData = new StringBuilder($@" SELECT RETREAT_ID, RETREAT_NO, SOUCE_NO, RETREAT_TYPE, RETREAT_TYPE_NAME, RETREAT_TIME, RETREATER, RETREAT_STATUS, RETREAT_STATUS_NAME, [DESCRIBE], CREATE_BY, CREATE_NAME, CREATE_TIME, UPDATE_BY, UPDATE_NAME, UPDATE_TIME FROM VW_WMS_IN_RETREAT WHERE 1=1 {sqlCondition} ORDER BY UPDATE_TIME DESC,CREATE_TIME DESC OFFSET {pageStartIndex} ROWS FETCH NEXT {retreatSearchMd.EveryPageQty} ROWS ONLY "); #endregion int dataCount = Convert.ToInt32(new DataRepository(_dataContext).ExecuteScalar(sqlCountRetreatData.ToString())); List resultList = new DataRepository(_dataContext).Query(sqlQueryRetreatData.ToString()).ToList(); OperateResultInfo> retDataMsg = SuccessStatus(resultList); retDataMsg.DataCount = dataCount; return retDataMsg; } catch (Exception ex) { return FailMessageStatus>($"查询退料单数据发生异常,【{ex.Message}】", null); } } /// /// 根据退料单主键Id查询退料单明细数据 /// /// 退料单对象 /// public OperateResultInfo GetWmsInRetreatDtlListForID(WmsInRetreatResult wmsInRetreat) { try { #region SQL语句生成 string sqlQueryWmsInRetreat = $@" SELECT RETREAT_ID, RETREAT_NO, SOUCE_NO, RETREAT_TYPE, RETREAT_TYPE_NAME, RETREAT_TIME, RETREATER, RETREAT_STATUS, RETREAT_STATUS_NAME, [DESCRIBE], CREATE_BY, CREATE_NAME, CREATE_TIME, UPDATE_BY, UPDATE_NAME, UPDATE_TIME FROM VW_WMS_IN_RETREAT WHERE RETREAT_ID = '{wmsInRetreat.RETREAT_ID}' "; string strWhere = string.Empty; if (wmsInRetreat.REMARKS1 == "查询已删除明细数据") { strWhere = "1=1"; } else if (wmsInRetreat.REMARKS1 == "查询未组盘完成的退料单明细") { strWhere = "RETREAT_DTL_STATUS < 99 AND RETREAT_TRAY_QTY < RETREAT_QTY"; } else { strWhere = "RETREAT_DTL_STATUS < 111"; } string sqlQueryWmsRetreatDtl = $@" SELECT * FROM VW_WMS_IN_RETREAT_DTL WHERE RETREAT_ID = '{wmsInRetreat.RETREAT_ID}' AND {strWhere} ORDER BY RETREAT_DTL_STATUS,RETREAT_DTL_ID "; #endregion List resultList = new DataRepository(_dataContext).Query(sqlQueryWmsInRetreat).ToList(); List resultDtlList = new DataRepository(_dataContext).Query(sqlQueryWmsRetreatDtl).ToList(); wmsInRetreat = resultList[0]; wmsInRetreat.WmsInRetreatDtlList = resultDtlList; OperateResultInfo retDataMsg = SuccessStatus(wmsInRetreat); return retDataMsg; } catch (Exception ex) { return FailMessageStatus($"查询退料单明细数据发生异常,【{ex.Message}】", null); } } /// /// 新增退料单数据 /// /// 退料单主表对象 /// public OperateResultInfo AddWmsInRetreatData(WmsInRetreatResult wmsInRetreat) { try { #region SQL语句生成 List sqlList = new List(); wmsInRetreat.RETREAT_ID = Convert.ToInt32(new DataRepository(_dataContext).GetSequenceMsg("Retreat_Id")); wmsInRetreat.RETREAT_NO = new DataRepository(_dataContext).GetSequenceMsg("Retreat_No"); wmsInRetreat.RETREAT_STATUS = 0; StringBuilder sqlAddWmsRetreat = new StringBuilder(); string retreatTime = DateTime.Compare(wmsInRetreat.RETREAT_TIME, new DateTime()) == 0 ? "NULL" : $"'{wmsInRetreat.RETREAT_TIME}'"; sqlAddWmsRetreat.Append($@" INSERT INTO [WMS_IN_RETREAT] ( [RETREAT_ID], [RETREAT_NO], [RETREATER], [RETREAT_TYPE], [RETREAT_TIME], [SOUCE_NO], [RETREAT_STATUS], [DESCRIBE], [CREATE_BY], [CREATE_TIME], [UPDATE_BY], [UPDATE_TIME], [DATA_VERSION], [REMARKS1], [REMARKS2], [REMARKS3], [REMARKS4], [REMARKS5] ) VALUES ( '{wmsInRetreat.RETREAT_ID}', '{wmsInRetreat.RETREAT_NO}', '{wmsInRetreat.RETREATER}', '{wmsInRetreat.RETREAT_TYPE}', {retreatTime}, '{wmsInRetreat.SOUCE_NO}', '{wmsInRetreat.RETREAT_STATUS}', '{wmsInRetreat.DESCRIBE}', '{wmsInRetreat.CREATE_BY}', getdate(), '{wmsInRetreat.UPDATE_BY}', getdate(), 0, NULL, NULL, NULL, NULL, NULL ); "); sqlList.Add(sqlAddWmsRetreat.ToString()); foreach (WmsInRetreatDtlResult item in wmsInRetreat.WmsInRetreatDtlList) { StringBuilder sqlAddWmsInRetreatDtl = new StringBuilder(); item.RETREAT_DTL_ID = Convert.ToInt32(new DataRepository(_dataContext).GetSequenceMsg("Retreat_Dtl_Id")); item.RETREAT_ID = wmsInRetreat.RETREAT_ID; item.RETREAT_DTL_STATUS = 0; sqlAddWmsInRetreatDtl.Append($@" INSERT INTO [WMS_IN_RETREAT_DTL] ( [RETREAT_DTL_ID], [RETREAT_ID], [MATERIEL_CODE], [MATERIEL_NAME], [MATERIEL_BARCODE], [MATERIEL_SPEC], [BATCH_NO], [PACKAGE_CODE], [UNIT_CODE], [RETREAT_QTY], [RETREAT_TRAY_QTY], [PUTAWAY_QTY], [SUPPLIER_CODE], [SUPPLIER_NAME], [PRODUCT_DATE], [EXP_DATE], [RETREAT_DTL_STATUS], [INSPECTION_RESULT], [ITEM_STATUS], [DESCRIBE], [CREATE_BY], [CREATE_TIME], [UPDATE_BY], [UPDATE_TIME], [DATA_VERSION], [REMARKS1], [REMARKS2], [REMARKS3], [REMARKS4], [REMARKS5] ) VALUES ( '{item.RETREAT_DTL_ID}', '{item.RETREAT_ID}', '{item.MATERIEL_CODE}', '{item.MATERIEL_NAME}', '{item.MATERIEL_BARCODE}', '{item.MATERIEL_SPEC}', '{item.BATCH_NO}', '{item.PACKAGE_CODE}', '{item.UNIT_CODE}', '{item.RETREAT_QTY}', '{item.RETREAT_TRAY_QTY}', '{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(sqlAddWmsInRetreatDtl.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 EditWmsInRetreatData(WmsInRetreatResult wmsInRetreat) { try { #region SQL语句生成 List sqlList = new List(); string retreatTime = DateTime.Compare(wmsInRetreat.RETREAT_TIME, new DateTime()) == 0 ? "NULL" : $"'{wmsInRetreat.RETREAT_TIME}'"; string sqlUpdateWmsInRetreat = $@" UPDATE [WMS_IN_RETREAT] SET [RETREAT_NO] = '{wmsInRetreat.RETREAT_NO}', [RETREATER] = '{wmsInRetreat.RETREATER}', [RETREAT_TYPE] = '{wmsInRetreat.RETREAT_TYPE}', [RETREAT_TIME] = {retreatTime}, [SOUCE_NO] = '{wmsInRetreat.SOUCE_NO}', [RETREAT_STATUS] = '{wmsInRetreat.RETREAT_STATUS}', [DESCRIBE] = '{wmsInRetreat.DESCRIBE}', [UPDATE_BY] = '{wmsInRetreat.UPDATE_BY}', [UPDATE_TIME] = GETDATE(), [DATA_VERSION] = [DATA_VERSION] + 1 WHERE [RETREAT_ID] = '{wmsInRetreat.RETREAT_ID}'; "; sqlList.Add(sqlUpdateWmsInRetreat); foreach (WmsInRetreatDtlResult item in wmsInRetreat.WmsInRetreatDtlList) { if (item.REMARKS1 == "更新" || item.REMARKS1 == "删除") { string sqlUpdateWmsInRetreatDtl = $@" UPDATE [WMS_IN_RETREAT_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}', [RETREAT_QTY] = '{item.RETREAT_QTY}', [RETREAT_TRAY_QTY] = '{item.RETREAT_TRAY_QTY}', [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}', [RETREAT_DTL_STATUS] = '{item.RETREAT_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 [RETREAT_DTL_ID] = '{item.RETREAT_DTL_ID}' AND [RETREAT_ID] = '{wmsInRetreat.RETREAT_ID}'; "; sqlList.Add(sqlUpdateWmsInRetreatDtl); } if (item.REMARKS1 == "添加") { StringBuilder sqlAddWmsInRetreatDtl = new StringBuilder(); item.RETREAT_DTL_ID = Convert.ToInt32(new DataRepository(_dataContext).GetSequenceMsg("Retreat_Dtl_Id")); item.RETREAT_ID = wmsInRetreat.RETREAT_ID; item.RETREAT_DTL_STATUS = 0; sqlAddWmsInRetreatDtl.Append($@" INSERT INTO [WMS_IN_RETREAT_DTL] ( [RETREAT_DTL_ID], [RETREAT_ID], [MATERIEL_CODE], [MATERIEL_NAME], [MATERIEL_BARCODE], [MATERIEL_SPEC], [BATCH_NO], [PACKAGE_CODE], [UNIT_CODE], [RETREAT_QTY], [RETREAT_TRAY_QTY], [PUTAWAY_QTY], [SUPPLIER_CODE], [SUPPLIER_NAME], [PRODUCT_DATE], [EXP_DATE], [RETREAT_DTL_STATUS], [INSPECTION_RESULT], [ITEM_STATUS], [DESCRIBE], [CREATE_BY], [CREATE_TIME], [UPDATE_BY], [UPDATE_TIME], [DATA_VERSION], [REMARKS1], [REMARKS2], [REMARKS3], [REMARKS4], [REMARKS5] ) VALUES ( '{item.RETREAT_DTL_ID}', '{item.RETREAT_ID}', '{item.MATERIEL_CODE}', '{item.MATERIEL_NAME}', '{item.MATERIEL_BARCODE}', '{item.MATERIEL_SPEC}', '{item.BATCH_NO}', '{item.PACKAGE_CODE}', '{item.UNIT_CODE}', '{item.RETREAT_QTY}', '{item.RETREAT_TRAY_QTY}', '{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(sqlAddWmsInRetreatDtl.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 DeleteWmsInRetreatData(WmsInRetreatResult wmsInRetreat) { try { #region SQL语句生成 string[] retreatIdList = wmsInRetreat.RETREAT_NO.Split(','); List sqlList = new List(); foreach (string item in retreatIdList) { string sqlDeleteWmsRetreat = $@" UPDATE WMS_IN_RETREAT SET RETREAT_STATUS = '111', UPDATE_BY = '{wmsInRetreat.UPDATE_BY}', UPDATE_TIME = GETDATE(), DATA_VERSION = DATA_VERSION + 1 WHERE RETREAT_ID = '{item}'; "; string sqlDeleteWmsInRetreatDtl = $@" UPDATE WMS_IN_RETREAT_DTL SET RETREAT_DTL_STATUS = '111', UPDATE_BY = '{wmsInRetreat.UPDATE_BY}', UPDATE_TIME = GETDATE(), DATA_VERSION = DATA_VERSION + 1 WHERE RETREAT_ID = '{item}'; "; sqlList.Add(sqlDeleteWmsRetreat); sqlList.Add(sqlDeleteWmsInRetreatDtl); /* 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> GetWmsInRetreatRecordListForPage(WmsInRetreatRecordSearchMd retreatRecordSearchMd) { try { #region SQL语句生成 StringBuilder sqlCondition = new StringBuilder(); if (!string.IsNullOrEmpty(retreatRecordSearchMd.RetreatNoMsg)) { sqlCondition.Append($" AND RETREAT_NO = '{retreatRecordSearchMd.RetreatNoMsg}'"); } if (!string.IsNullOrEmpty(retreatRecordSearchMd.SourceNoMsg)) { sqlCondition.Append($" AND SOURCE_NO = '{retreatRecordSearchMd.SourceNoMsg}'"); } if (!string.IsNullOrEmpty(retreatRecordSearchMd.RegionNoMsg)) { sqlCondition.Append($" AND REGION_CODE = '{retreatRecordSearchMd.RegionNoMsg}'"); } if (!string.IsNullOrEmpty(retreatRecordSearchMd.BinNoMsg)) { sqlCondition.Append($" AND (BIN_CODE like '%{retreatRecordSearchMd.BinNoMsg}%' OR BIN_NAME like '%{retreatRecordSearchMd.BinNoMsg}%')"); } if (!string.IsNullOrEmpty(retreatRecordSearchMd.PalletNoMsg)) { sqlCondition.Append($" AND PALLET_CODE = '{retreatRecordSearchMd.PalletNoMsg}'"); } if (!string.IsNullOrEmpty(retreatRecordSearchMd.RetreaterMsg)) { sqlCondition.Append($" AND RETREATER = '{retreatRecordSearchMd.RetreaterMsg}'"); } if (!string.IsNullOrEmpty(retreatRecordSearchMd.MaterielMsg)) { sqlCondition.Append($" AND (MATERIEL_CODE like '%{retreatRecordSearchMd.MaterielMsg}%' OR MATERIEL_NAME = '%{retreatRecordSearchMd.MaterielMsg}%')"); } if (!string.IsNullOrEmpty(retreatRecordSearchMd.MaterielTypeMsg)) { sqlCondition.Append($" AND (MATERIEL_TYPE_CODE = '%{retreatRecordSearchMd.MaterielTypeMsg}%' OR MATERIEL_TYPE_NAME = '%{retreatRecordSearchMd.MaterielTypeMsg}%')"); } if (!string.IsNullOrEmpty(retreatRecordSearchMd.SupplierMsg)) { sqlCondition.Append($" AND SUPPLIER_CODE = '{retreatRecordSearchMd.SupplierMsg}'"); } if (!string.IsNullOrEmpty(retreatRecordSearchMd.BatchNoMsg)) { sqlCondition.Append($" AND BATCH_NO = '{retreatRecordSearchMd.SupplierMsg}'"); } if (!string.IsNullOrEmpty(retreatRecordSearchMd.ItemStatusMsg)) { sqlCondition.Append($" AND ITEM_STATUS = '{retreatRecordSearchMd.ItemStatusMsg}'"); } if (!string.IsNullOrEmpty(retreatRecordSearchMd.InspectionResultMsg)) { sqlCondition.Append($" AND INSPECTION_RESULT = '{retreatRecordSearchMd.InspectionResultMsg}'"); } if (!string.IsNullOrEmpty(retreatRecordSearchMd.RetreatRecordStatus)) { sqlCondition.Append($" AND RETREAT_RECORD_STATUS = '{retreatRecordSearchMd.RetreatRecordStatus}'"); } if (!string.IsNullOrEmpty(retreatRecordSearchMd.StartRetreatTime)) { sqlCondition.Append($" AND RETREAT_TIME >= '{retreatRecordSearchMd.StartRetreatTime}'"); } if (!string.IsNullOrEmpty(retreatRecordSearchMd.EndRetreatTime)) { sqlCondition.Append($" AND RETREAT_TIME <= '{retreatRecordSearchMd.EndRetreatTime}'"); } if (!string.IsNullOrEmpty(retreatRecordSearchMd.StartProductTime)) { sqlCondition.Append($" AND PRODUCT_DATE >= '{retreatRecordSearchMd.StartProductTime}'"); } if (!string.IsNullOrEmpty(retreatRecordSearchMd.EndProductTime)) { sqlCondition.Append($" AND PRODUCT_DATE <= '{retreatRecordSearchMd.EndProductTime}'"); } if (!string.IsNullOrEmpty(retreatRecordSearchMd.StartExpTime)) { sqlCondition.Append($" AND EXP_DATE >= '{retreatRecordSearchMd.StartExpTime}'"); } if (!string.IsNullOrEmpty(retreatRecordSearchMd.EndExpTime)) { sqlCondition.Append($" AND EXP_DATE <= '{retreatRecordSearchMd.EndExpTime}'"); } StringBuilder sqlCountRetreatData = new StringBuilder($@"SELECT COUNT(1) FROM VW_WMS_IN_RETREAT_RECORD WHERE 1=1"); sqlCountRetreatData.Append(sqlCondition.ToString()); int pageStartIndex = (retreatRecordSearchMd.PageNum - 1) * retreatRecordSearchMd.EveryPageQty; int pageEndIndex = retreatRecordSearchMd.PageNum * retreatRecordSearchMd.EveryPageQty; StringBuilder sqlQueryRetreatData = new StringBuilder($@" SELECT RETREAT_RECORD_ID, NEWID, RETREAT_ID, RETREAT_NO, RETREAT_DTL_ID, SOURCE_NO, REGION_CODE, REGION_NAME, BIN_CODE, BIN_NAME, TRAY_CODE, PALLET_CODE, RETREAT_TIME, RETREATER, MATERIEL_TYPE_CODE, MATERIEL_TYPE_NAME, MATERIEL_CODE, MATERIEL_NAME, MATERIEL_BARCODE, MATERIEL_SPEC, SUPPLIER_CODE, SUPPLIER_NAME, BATCH_NO, PACKAGE_CODE, UNIT_CODE, RETREAT_QTY, RETREAT_REASON, PRODUCT_DATE, EXP_DATE, RETREAT_RECORD_STATUS, RETREAT_RECORD_STATUS_NAME, INSPECTION_RESULT, INSPECTION_RESULT_NAME, ITEM_STATUS, ITEM_STATUS_NAME, [DESCRIBE], CREATE_BY, CREATE_NAME, CREATE_TIME, UPDATE_BY, UPDATE_NAME, UPDATE_TIME, DATA_VERSION, REMARKS1, REMARKS2, REMARKS3, REMARKS4, REMARKS5 FROM VW_WMS_IN_RETREAT_RECORD WHERE 1=1 {sqlCondition} ORDER BY UPDATE_TIME DESC,CREATE_TIME DESC OFFSET {pageStartIndex} ROWS FETCH NEXT {retreatRecordSearchMd.EveryPageQty} ROWS ONLY "); #endregion int dataCount = Convert.ToInt32(new DataRepository(_dataContext).ExecuteScalar(sqlCountRetreatData.ToString())); List resultList = new DataRepository(_dataContext).Query(sqlQueryRetreatData.ToString()).ToList(); OperateResultInfo> retDataMsg = SuccessStatus(resultList); retDataMsg.DataCount = dataCount; return retDataMsg; } catch (Exception ex) { return FailMessageStatus>($"查询退料记录数据发生异常,【{ex.Message}】", null); } } /// /// 添加退料组盘数据 /// /// 组盘信息实体类对象 /// public OperateResultInfo AddWmsInRetreatTrayData(WmsStkTrayResult wmsStkTrayResult) { try { if (wmsStkTrayResult.WmsStkTrayDtlList.Count > 0) { #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.REGION_CODE = 'SHDJ_Region' "; List resultList = new DataRepository(_dataContext).Query(sqlQueryRetreatRegionBinMsg).ToList(); BinResult binMd = resultList[0]; wmsStkTrayResult.TRAY_ID = Convert.ToInt32(new DataRepository(_dataContext).GetSequenceMsg("Tray_Id")); wmsStkTrayResult.TRAY_CODE = new DataRepository(_dataContext).GetSequenceMsg("Tray_Code"); string sqlAddWmsStkTray = $@" INSERT INTO [WMS_STK_TRAY] ( [TRAY_ID], [TRAY_CODE], [PALLET_CODE], [WEIGHT], [HEIGHT], [TRAYS_TYPE], [TRAY_LOADED_TYPE], [TRAY_STATUS], [DESCRIBE], [CREATE_BY], [CREATE_TIME], [UPDATE_BY], [UPDATE_TIME], [DATA_VERSION], [REMARKS1], [REMARKS2], [REMARKS3], [REMARKS4], [REMARKS5] ) VALUES ( '{wmsStkTrayResult.TRAY_ID}', '{wmsStkTrayResult.TRAY_CODE}', '{wmsStkTrayResult.PALLET_CODE}', '{wmsStkTrayResult.WEIGHT}', '{wmsStkTrayResult.HEIGHT}', '{wmsStkTrayResult.TRAYS_TYPE}', '{wmsStkTrayResult.TRAY_LOADED_TYPE}', '{wmsStkTrayResult.TRAY_STATUS}', '{wmsStkTrayResult.DESCRIBE}', '{wmsStkTrayResult.CREATE_BY}', getdate(), '{wmsStkTrayResult.UPDATE_BY}', getdate(), 0, NULL, NULL, NULL, NULL, NULL ); "; sqlList.Add(sqlAddWmsStkTray); int putawayId = Convert.ToInt32(new DataRepository(_dataContext).GetSequenceMsg("InPutaway_Id")); string puawayNo = new DataRepository(_dataContext).GetSequenceMsg("InPutaway_No"); string sqlAddWmsInPutAway = $@" INSERT INTO [WMS_IN_PUTAWAY] ( [PUTAWAY_ID], [PUTAWAY_NO], [SOURCE_NO], [PUTAWAY_TYPE], [TRAY_CODE], [PALLET_CODE], [SBIN_CODE], [PUTAWAY_PRIORITY], [PUTAWAY_STATUS], [DESCRIBE], [CREATE_BY], [CREATE_TIME], [UPDATE_BY], [UPDATE_TIME], [DATA_VERSION], [REMARKS1], [REMARKS2], [REMARKS3], [REMARKS4], [REMARKS5] ) VALUES ( '{putawayId}', '{puawayNo}', '{wmsStkTrayResult.WmsStkTrayDtlList[0].WmsInRetreatRecord.RETREAT_NO}', '{2}', '{wmsStkTrayResult.TRAY_CODE}', '{wmsStkTrayResult.PALLET_CODE}', '{binMd.BIN_CODE}', '{100}', '{0}', '{wmsStkTrayResult.DESCRIBE}', '{wmsStkTrayResult.CREATE_BY}', getdate(), '{wmsStkTrayResult.UPDATE_BY}', getdate(), 0, NULL, NULL, NULL, NULL, NULL ); "; sqlList.Add(sqlAddWmsInPutAway); foreach (WmsStkTrayDtlResult item in wmsStkTrayResult.WmsStkTrayDtlList) { WmsStkTrayDtlExtResult trayDtlExtMd = item.WmsStkTrayDtlExtMd; WmsInRetreatRecordResult retreatRecordMd = item.WmsInRetreatRecord; item.TRAY_DTL_ID = Convert.ToInt32(new DataRepository(_dataContext).GetSequenceMsg("Tray_Dtl_Id")); trayDtlExtMd.TRAY_DTL_EXT_ID = Convert.ToInt32(new DataRepository(_dataContext).GetSequenceMsg("Tray_Dtl_Ext_Id")); trayDtlExtMd.TRAY_DTL_ID = item.TRAY_DTL_ID; trayDtlExtMd.TRAY_ID = wmsStkTrayResult.TRAY_ID; retreatRecordMd.RETREAT_RECORD_ID = Convert.ToInt32(new DataRepository(_dataContext).GetSequenceMsg("Retreat_Record_Id")); int putawayDtlId = Convert.ToInt32(new DataRepository(_dataContext).GetSequenceMsg("InPutaway_Dtl_Id")); retreatRecordMd.TRAY_CODE = wmsStkTrayResult.TRAY_CODE; retreatRecordMd.REGION_CODE = binMd.REGION_CODE; retreatRecordMd.REGION_NAME = binMd.REGION_NAME; retreatRecordMd.BIN_CODE = binMd.BIN_CODE; retreatRecordMd.BIN_NAME = binMd.BIN_NAME; string sqlAddWmsStkTrayDtl = $@" INSERT INTO [WMS_STK_TRAY_DTL] ( [TRAY_DTL_ID], [TRAY_ID], [MATERIEL_ID], [MATERIEL_CODE], [MATERIEL_NAME], [MATERIEL_BARCODE], [QTY], [TRAY_DTL_STATUS], [DESCRIBE], [CREATE_BY], [CREATE_TIME], [UPDATE_BY], [UPDATE_TIME], [DATA_VERSION], [REMARKS1], [REMARKS2], [REMARKS3], [REMARKS4], [REMARKS5] ) VALUES ( '{item.TRAY_DTL_ID}', '{item.TRAY_ID}', '{item.MATERIEL_ID}', '{item.MATERIEL_CODE}', '{item.MATERIEL_NAME}', '{item.MATERIEL_BARCODE}', '{item.QTY}', '{0}', '{item.DESCRIBE}', '{item.CREATE_BY}', getdate(), '{item.UPDATE_BY}', getdate(), 0, NULL, NULL, NULL, NULL, NULL ); "; string sqlAddWmsStkTrayDtlExt = $@" INSERT INTO [WMS_STK_TRAY_DTL_EXT] ( [TRAY_DTL_EXT_ID], [TRAY_DTL_ID], [MATERIEL_SPEC], [PACKAGE_CODE], [UNIT_CODE], [BATCH_NO], [SUPPLIER_CODE], [SUPPLIER_NAME], [PRODUCT_DATE], [EXP_DATE], [INSPECTION_RESULT], [LOCK_FLAG], [MIN_PKG_QTY], [ECTEND_TIME_LEN], [SUPPLIER_BATCH], [ITEM_STATUS], [DESCRIBE], [CREATE_BY], [CREATE_TIME], [UPDATE_BY], [UPDATE_TIME], [DATA_VERSION], [REMARKS1], [REMARKS2], [REMARKS3], [REMARKS4], [REMARKS5] ) VALUES ( '{trayDtlExtMd.TRAY_DTL_EXT_ID}', '{trayDtlExtMd.TRAY_DTL_ID}', '{trayDtlExtMd.MATERIEL_SPEC}', '{trayDtlExtMd.PACKAGE_CODE}', '{trayDtlExtMd.UNIT_CODE}', '{trayDtlExtMd.BATCH_NO}', '{trayDtlExtMd.SUPPLIER_CODE}', '{trayDtlExtMd.SUPPLIER_NAME}', '{trayDtlExtMd.PRODUCT_DATE}', '{trayDtlExtMd.EXP_DATE}', '{trayDtlExtMd.INSPECTION_RESULT}', '{0}', '{trayDtlExtMd.MIN_PKG_QTY}', '{trayDtlExtMd.ECTEND_TIME_LEN}', '{trayDtlExtMd.SUPPLIER_BATCH}', '{trayDtlExtMd.ITEM_STATUS}', '{trayDtlExtMd.DESCRIBE}', '{trayDtlExtMd.CREATE_BY}', getdate(), '{trayDtlExtMd.UPDATE_BY}', getdate(), 0, NULL, NULL, NULL, NULL, NULL ); "; string sqlAddWmsInRetreatRecord = $@" INSERT INTO [WMS_IN_RETREAT_RECORD] ( [RETREAT_RECORD_ID], [RETREAT_ID], [RETREAT_NO], [RETREAT_DTL_ID], [SOURCE_NO], [REGION_CODE], [REGION_NAME], [BIN_CODE], [BIN_NAME], [TRAY_CODE], [PALLET_CODE], [RETREAT_TIME], [RETREATER], [MATERIEL_CODE], [MATERIEL_NAME], [MATERIEL_BARCODE], [MATERIEL_SPEC], [SUPPLIER_CODE], [SUPPLIER_NAME], [BATCH_NO], [PACKAGE_CODE], [UNIT_CODE], [RETREAT_QTY], [PRODUCT_DATE], [EXP_DATE], [RETREAT_RECORD_STATUS], [INSPECTION_RESULT], [ITEM_STATUS], [DESCRIBE], [CREATE_BY], [CREATE_TIME], [UPDATE_BY], [UPDATE_TIME], [DATA_VERSION], [REMARKS1], [REMARKS2], [REMARKS3], [REMARKS4], [REMARKS5] ) VALUES ( '{retreatRecordMd.RETREAT_RECORD_ID}', '{retreatRecordMd.RETREAT_ID}', '{retreatRecordMd.RETREAT_NO}', '{retreatRecordMd.RETREAT_DTL_ID}', '{retreatRecordMd.SOURCE_NO}', '{retreatRecordMd.REGION_CODE}', '{retreatRecordMd.REGION_NAME}', '{retreatRecordMd.BIN_CODE}', '{retreatRecordMd.BIN_NAME}', '{retreatRecordMd.TRAY_CODE}', '{retreatRecordMd.PALLET_CODE}', '{retreatRecordMd.RETREAT_TIME}', '{retreatRecordMd.RETREATER}', '{retreatRecordMd.MATERIEL_CODE}', '{retreatRecordMd.MATERIEL_NAME}', '{retreatRecordMd.MATERIEL_BARCODE}', '{retreatRecordMd.MATERIEL_SPEC}', '{retreatRecordMd.SUPPLIER_CODE}', '{retreatRecordMd.SUPPLIER_NAME}', '{retreatRecordMd.BATCH_NO}', '{retreatRecordMd.PACKAGE_CODE}', '{retreatRecordMd.UNIT_CODE}', '{retreatRecordMd.RETREAT_QTY}', '{retreatRecordMd.PRODUCT_DATE}', '{retreatRecordMd.EXP_DATE}', '{retreatRecordMd.RETREAT_RECORD_STATUS}', '{retreatRecordMd.INSPECTION_RESULT}', '{retreatRecordMd.ITEM_STATUS}', '{retreatRecordMd.DESCRIBE}', '{retreatRecordMd.CREATE_BY}', getdate(), '{retreatRecordMd.UPDATE_BY}', getdate(), 0, NULL, NULL, NULL, NULL, NULL ); "; string sqlAddWmsInPutAwayDtl = $@" 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], [INSPECTION_RESULT], [PUTAWAY_DTL_STATUS], [ITEM_STATUS], [DESCRIBE], [CREATE_BY], [CREATE_TIME], [UPDATE_BY], [UPDATE_TIME], [DATA_VERSION], [REMARKS1], [REMARKS2], [REMARKS3], [REMARKS4], [REMARKS5] ) VALUES ( '{putawayDtlId}', '{putawayId}', '{item.MATERIEL_CODE}', '{item.MATERIEL_NAME}', '{item.MATERIEL_BARCODE}', '{trayDtlExtMd.MATERIEL_SPEC}', '{trayDtlExtMd.BATCH_NO}', '{trayDtlExtMd.PACKAGE_CODE}', '{trayDtlExtMd.UNIT_CODE}', '{item.QTY}', '{trayDtlExtMd.SUPPLIER_CODE}', '{trayDtlExtMd.SUPPLIER_NAME}', '{trayDtlExtMd.PRODUCT_DATE}', '{trayDtlExtMd.EXP_DATE}', '{trayDtlExtMd.INSPECTION_RESULT}', '{0}', '{trayDtlExtMd.ITEM_STATUS}', '{item.DESCRIBE}', '{trayDtlExtMd.CREATE_BY}', getdate(), '{trayDtlExtMd.UPDATE_BY}', getdate(), 0, NULL, NULL, NULL, NULL, NULL ); "; sqlList.Add(sqlAddWmsStkTrayDtl); sqlList.Add(sqlAddWmsStkTrayDtlExt); sqlList.Add(sqlAddWmsInRetreatRecord); sqlList.Add(sqlAddWmsInPutAwayDtl); string sqlUpdateWmsInRetreatDtl = $@" UPDATE WMS_IN_RETREAT_DTL SET RETREAT_TRAY_QTY = RETREAT_TRAY_QTY + '{item.QTY}', RETREAT_DTL_STATUS = '{55}', UPDATE_BY = '{wmsStkTrayResult.UPDATE_BY}', UPDATE_TIME = GETDATE(), DATA_VERSION = DATA_VERSION + 1 WHERE RETREAT_DTL_ID = '{retreatRecordMd.RETREAT_DTL_ID}' AND RETREAT_ID = '{retreatRecordMd.RETREAT_ID}' "; sqlList.Add(sqlUpdateWmsInRetreatDtl); } string sqlUpdateWmsInRetreat = $@" IF EXISTS ( SELECT 1 FROM WMS_IN_RETREAT_DTL WHERE RETREAT_DTL_STATUS = 55 AND RETREAT_ID = '{wmsStkTrayResult.WmsStkTrayDtlList[0].WmsInRetreatRecord.RETREAT_ID}' ) UPDATE WMS_IN_RETREAT SET RETREAT_STATUS = 55, UPDATE_BY = '{wmsStkTrayResult.UPDATE_BY}', UPDATE_TIME = GETDATE(), DATA_VERSION = DATA_VERSION + 1 WHERE RETREAT_ID = '{wmsStkTrayResult.WmsStkTrayDtlList[0].WmsInRetreatRecord.RETREAT_ID}' ; "; sqlList.Add(sqlUpdateWmsInRetreat); #endregion int row = new DataRepository(_dataContext).ExecSqlListTran(sqlList); if (row > 0) { return SuccessMessageStatus("新增退料组盘数据成功!", row); } else { return FailMessageStatus("新增退料组盘数据失败!", row); } } else { return FailMessageStatus($"传入数据不存在组盘明细数据!"); } } catch (Exception ex) { return FailMessageStatus($"添加退料组盘数据发生异常,【{ex.Message}】"); } } } }