--[[
|
编码: GK-API-001
|
名称: 盘点计划同步
|
作者: Yuanfeng
|
日期: 2025-1-29
|
|
入口函数： SKU_Sync
|
来源项目: 国科项目
|
|
功能说明:
|
1. 接收来自上游系统的 XML 格式数据，并解析该数据, 创建SKU及SKU_UPC
|
更新：
|
2025/06/09 更新了udf01~udf04的属性变更
|
|
更改记录:
|
V2.0 HAN 20250402 代码规范
|
V2.1 Yuanfeng 优化返回结果处理
|
V2.2 优化错误处理和返回逻辑
|
V2.3 移除error字段，合并到message中
|
V2.4 新增SKU_GRID_PARM字段
|
V2.5 2025-07-01 SKU_GRID料箱格参数优化为根据cell_type 自动同步
|
V2.6 本次更新主要针对SKU料箱格参数（SKU_GridBox_Parm）同步逻辑进行优化，
|
解决了在不同cid_type变更场景下的料格处理问题，确保数据同步的准确性和完整性。
|
|
]] wms_base = require("wms_base")
|
xml = require("oi_base_xml")
|
m3 = require("oi_base_mobox")
|
|
-- 创建或更新SKU_UPC条码记录（重构版）
|
-- @param strLuaDEID: Lua数据交换ID
|
-- @param storer: 货主编码
|
-- @param item_code: SKU编码
|
-- @param upc_code: UPC条码
|
-- @return: 返回两个值 (错误码, 错误信息/成功信息)
|
-- 创建或更新SKU_UPC条码记录（优化版）
|
-- @param strLuaDEID: Lua数据交换ID
|
-- @param storer: 货主编码
|
-- @param item_code: SKU编码
|
-- @param upc_code: UPC条码
|
-- @return: 返回两个值 (错误码, 错误信息/成功信息)
|
local function create_or_update_sku_upc(strLuaDEID, storer, item_code, upc_code)
|
local nRet, strRetInfo
|
|
-- 1. 检查UPC是否有效
|
if not upc_code or upc_code == '' then
|
-- lua.debugex(strLuaDEID, "UPC为空", "货主:"..storer..", SKU:"..item_code)
|
return 0, "UPC为空，跳过处理" -- 修改为返回0表示跳过
|
end
|
|
-- 2. 检查该SKU的UPC记录是否已存在（三字段主键检查）
|
local strCondition = string.format("S_STORER = '%s' AND S_ITEM_CODE = '%s'", storer, item_code)
|
nRet, strRetInfo = mobox.existThisData(strLuaDEID, "SKU_UPC", strCondition)
|
if nRet ~= 0 then
|
local errMsg = "检查SKU_UPC记录失败: " .. tostring(strRetInfo)
|
return 1, errMsg
|
end
|
|
-- 3. 处理检查结果
|
if strRetInfo == 'yes' then
|
-- 记录已存在且完全相同，直接跳过
|
local updateAttr = string.format("S_UPC_CODE='%s'", upc_code);
|
local nRet, strRetInfo = mobox.updateDataAttrByCondition(strLuaDEID, "SKU_UPC", strCondition, updateAttr)
|
if (nRet ~= 0) then
|
return 1, "数据更新失败!" .. strRetInfo
|
else
|
lua.DebugEx(strLuaDEID, "数据更新成功....", strRetInfo)
|
return 0, "UPC 已更新" .. strRetInfo
|
end
|
|
-- 只作为更新操作
|
local sku_upc = m3.AllocObject(strLuaDEID, "SKU_UPC")
|
sku_upc.storer = storer
|
sku_upc.item_code = item_code
|
sku_upc.upc_code = upc_code
|
nRet, strRetInfo = m3.UpdateDataObj(strLuaDEID, sku_upc)
|
if nRet ~= 0 then
|
local errMsg = "更新SKU_UPC失败: " .. tostring(strRetInfo)
|
return 1, errMsg
|
end
|
return 0, "SKU_UPC已存在且相同，跳过处理"
|
end
|
|
-- 5. 创建新记录
|
local sku_upc = m3.AllocObject(strLuaDEID, "SKU_UPC")
|
sku_upc.storer = storer
|
sku_upc.item_code = item_code
|
sku_upc.upc_code = upc_code
|
|
nRet, strRetInfo = m3.CreateDataObj(strLuaDEID, sku_upc)
|
if nRet ~= 0 then
|
local errMsg = "创建SKU_UPC失败: " .. tostring(strRetInfo)
|
return 1, errMsg
|
end
|
return 0, "创建SKU_UPC成功"
|
end
|
|
-- 创建/更新SKU对应的货品料箱格参数
|
-- 不再依赖cell_type作为截止范围，而是检查所有maxCount~fMaxCount字段
|
-- @param strLuaDEID: Lua数据交换ID
|
-- @param skuModel: SKU模型对象
|
-- @param sku_input_data: 输入数据
|
-- @return: 返回错误码和错误信息
|
local function create_or_update_sku_gridbox(strLuaDEID, skuModel, sku_input_data)
|
local strRetInfo
|
|
-- 1. 获取默认容器类型编码
|
local nConstRet, CONST_CTD_CODE = wms_base.Get_sConst2("WMS_Default_CNTR_Type")
|
if nConstRet ~= 0 then
|
return 1, "获取默认容器类型定义编码失败: " .. CONST_CTD_CODE
|
end
|
-- 2. 定义料箱格类型与字段映射关系
|
local gridbox_types = {
|
A = {
|
field = "maxCount",
|
value = sku_input_data.maxCount
|
},
|
B = {
|
field = "bMaxCount",
|
value = sku_input_data.bMaxCount
|
},
|
C = {
|
field = "cMaxCount",
|
value = sku_input_data.cMaxCount
|
},
|
D = {
|
field = "dMaxCount",
|
value = sku_input_data.dMaxCount
|
},
|
E = {
|
field = "eMaxCount",
|
value = sku_input_data.eMaxCount
|
},
|
F = {
|
field = "fMaxCount",
|
value = sku_input_data.fMaxCount
|
}
|
}
|
-- 3. 处理所有料箱格类型
|
for cell_type, type_info in pairs(gridbox_types) do
|
local loading_limit = lua.Get_NumAttrValue(type_info.value) or 0
|
|
-- 只有当限制值大于0时才处理
|
if loading_limit > 0 then
|
-- 精确查询该类型的料箱格记录
|
local strCondition = string.format(
|
"S_ITEM_CODE = '%s' AND S_STORER = '%s' AND S_CTD_CODE='%s' AND S_CELL_TYPE='%s'", skuModel.item_code,
|
skuModel.storer, CONST_CTD_CODE, cell_type)
|
local nRet, existing_records = m3.GetDataObjByCondition(strLuaDEID, "SKU_GridBox_Parm", strCondition)
|
|
if nRet == 0 then
|
-- 记录存在，更新现有记录
|
local update_gridbox_obj = {{
|
id = existing_records.id,
|
attrs = {{
|
attr = "N_LOADING_LIMIT",
|
value = loading_limit
|
}}
|
}}
|
nRet, strRetInfo =
|
mobox.updateDataObj(strLuaDEID, "SKU_GridBox_Parm", lua.table2str(update_gridbox_obj))
|
if nRet ~= 0 then
|
return 1, string.format("更新%s类型料箱格属性失败: %s", cell_type, strRetInfo)
|
end
|
elseif nRet == 1 then
|
-- 记录不存在，创建新记录
|
local gridbox = m3.AllocObject(strLuaDEID, "SKU_GridBox_Parm")
|
gridbox.item_code = skuModel.item_code
|
gridbox.storer = skuModel.storer
|
gridbox.ctd_code = CONST_CTD_CODE
|
gridbox.loading_limit = loading_limit
|
gridbox.cell_type = cell_type
|
nRet, strRetInfo = m3.CreateDataObj(strLuaDEID, gridbox)
|
if nRet ~= 0 then
|
return 1, string.format("创建%s类型料箱格失败: %s", cell_type, strRetInfo)
|
end
|
else
|
-- 查询出错
|
return 1, string.format("查询%s类型料箱格记录失败: %s", cell_type,
|
existing_records or "未知错误")
|
end
|
end
|
end
|
|
return 0, "料箱格参数处理完成"
|
end
|
-- 创建统一返回结果（修改后版本，移除error字段）
|
function Create_result(flag, code, msg)
|
return {
|
flag = flag or "success",
|
code = code or "0",
|
message = msg or ""
|
}
|
end
|
|
-- 创建或更新GK_PROLINE数据(生产线数据)
|
local function create_or_update_proline(strLuaDEID, proline_no, proline_name)
|
-- 分配GK_PROLINE对象
|
local proline = m3.AllocObject(strLuaDEID, "GK_PROLINE")
|
proline.proline_no = proline_no
|
proline.proline_name = proline_name
|
-- 检查是否已存在
|
local strCondition = string.format("S_PROLINE_NO = '%s'", proline_no)
|
local nRet, id, strRetInfo = mobox.getDataObjAttrByKeyAttr(strLuaDEID, "GK_PROLINE", strCondition)
|
|
if nRet > 1 then
|
return 1, "检查GK_PROLINE是否存在时失败: " .. strRetInfo
|
end
|
|
if nRet == 1 then
|
-- 不存在，创建新记录
|
nRet, strRetInfo = m3.CreateDataObj(strLuaDEID, proline)
|
if nRet ~= 0 then
|
return 1, "创建GK_PROLINE失败: " .. strRetInfo
|
end
|
else
|
-- 已存在，更新记录
|
local update_proline_obj = {{
|
id = id,
|
attrs = {{
|
attr = "S_PROLINE_NAME",
|
value = proline_name
|
}}
|
}}
|
nRet, strRetInfo = mobox.updateDataObj(strLuaDEID, "GK_PROLINE", lua.table2str(update_proline_obj))
|
if nRet ~= 0 then
|
return 1, "更新GK_PROLINE失败: " .. strRetInfo
|
end
|
end
|
return 0, strRetInfo
|
end
|
|
-- 重构后的build_sku_grid_json函数
|
-- 现在完全基于数据库中的实际料格记录生成JSON
|
-- @param strLuaDEID: Lua数据交换ID
|
-- @param item_code: SKU编码
|
-- @param storer: 货主编码
|
-- @return: 返回两个值 (JSON字符串, 错误信息)
|
local function build_sku_grid_json(strLuaDEID, item_code, storer)
|
-- 1. 获取默认容器类型编码
|
local nConstRet, CONST_CTD_CODE = wms_base.Get_sConst2("WMS_Default_CNTR_Type")
|
if nConstRet ~= 0 then
|
return "", "获取默认容器类型定义编码失败: " .. CONST_CTD_CODE
|
end
|
|
-- 2. 查询该SKU所有现有的料箱格记录
|
local strCondition = string.format("S_ITEM_CODE = '%s' AND S_STORER = '%s' AND S_CTD_CODE='%s'", item_code, storer,
|
CONST_CTD_CODE)
|
local nRet, grid_records = m3.QueryDataObject(strLuaDEID, "SKU_GridBox_Parm", strCondition)
|
|
-- 3. 处理查询结果
|
if nRet ~= 0 then
|
return "", "查询SKU_GridBox_Parm记录失败: " .. (grid_records or "未知错误")
|
end
|
|
-- 4. 构建基础JSON结构
|
local grid_def = {{
|
ctd_code = CONST_CTD_CODE,
|
cell_def = {}
|
}}
|
|
-- 5. 处理查询到的记录
|
if grid_records and #grid_records > 0 then
|
for _, record in ipairs(grid_records) do
|
-- 将属性数组转换为对象
|
local record_obj = m3.KeyValueAttrsToObjAttr(record.attrs)
|
|
-- 提取需要的字段
|
local cell_type = record_obj.S_CELL_TYPE or ""
|
local loading_limit = lua.Get_NumAttrValue(record_obj.N_LOADING_LIMIT) or 0
|
local load_capacity = lua.Get_NumAttrValue(record_obj.F_LOAD_CAPACITY) or loading_limit -- 默认等于loading_limit
|
|
-- 只有当值有效时才添加到JSON
|
if cell_type ~= "" and loading_limit > 0 then
|
table.insert(grid_def[1].cell_def, {
|
cell_type = cell_type,
|
loading_limit = loading_limit,
|
load_capacity = load_capacity
|
})
|
end
|
end
|
end
|
|
-- 6. 转换为JSON字符串
|
local json_str = lua.table2str(grid_def)
|
-- -- lua.debugex(strLuaDEID, "生成的grid_json", json_str)
|
|
return json_str
|
end
|
-- 创建或更新SKU主数据
|
-- @param sku_input_data: 实际上就是报文中的：sku_data.COMMODITY_TB_ITEM 下面的字段内容
|
local function create_sku(strLuaDEID, sku_input_data)
|
local nRet, strRetInfo
|
-- 首先校验cidtype是否为空
|
if not sku_input_data.cidtype or sku_input_data.cidtype == '' then
|
return 1, "skuId为：" .. sku_input_data.skuId .. "的适配料格cidtype不允许为空！"
|
end
|
|
-- 获取系统常量
|
-- 获取默认容器类型编码
|
local nConstRet, CONST_CTD_CODE = wms_base.Get_sConst2("WMS_Default_CNTR_Type")
|
if nConstRet ~= 0 then
|
return 1, "获取默认容器类型定义编码失败: " .. CONST_CTD_CODE
|
end
|
if (sku_input_data.cidtype == nil or sku_input_data.cidtype == '') then
|
return 1, "skuId为：" .. sku_input_data.skuId .. "的适配料格cidtype不允许为空！"
|
end
|
|
-- 首先检查SKU是否已存在
|
local strCondition = string.format("S_ITEM_CODE = '%s' AND S_STORER = '%s'", sku_input_data.skuId,
|
sku_input_data.storerId)
|
|
local existSKUnRet, existingSKU = mobox.existThisData(strLuaDEID, "SKU", strCondition)
|
lua.DebugEx(strLuaDEID, "检查SKU是否存在返回结果", existingSKU);
|
|
if existSKUnRet ~= 0 then
|
return 1, "检查SKU是否存在时失败: " .. (existingSKU or "未知错误")
|
end
|
|
-- 分配SKU对象并设置属性
|
local sku = m3.AllocObject(strLuaDEID, "SKU")
|
sku.item_code = sku_input_data.skuId
|
sku.storer = sku_input_data.storerId
|
sku.item_name = sku_input_data.skuName
|
sku.note = sku_input_data.skuDec
|
sku.spec = sku_input_data.spec
|
sku.unit = sku_input_data.goodsUnit
|
sku.long = lua.Get_NumAttrValue(sku_input_data.length) or 0
|
sku.middle = lua.Get_NumAttrValue(sku_input_data.width) or 0
|
sku.short = lua.Get_NumAttrValue(sku_input_data.height) or 0
|
sku.count_method = "Limit"
|
sku.abc_type = sku_input_data.abcType or ""
|
sku.is_life_mgt = (sku_input_data.isBatchMgr == "1" and 'Y') or 'N'
|
sku.is_sn_mgt = (sku_input_data.isSnMgr == "1" and 'Y') or 'N'
|
sku.img_url = sku_input_data.imgUrl or ""
|
sku.cell_type = sku_input_data.cidtype
|
sku.item_type = sku_input_data.skuType
|
sku.loading_limit = lua.Get_NumAttrValue(sku_input_data.maxCount) or 0
|
sku.sku_grid_parm = '[{"ctd_code":"CTD-001","cell_def":{}}]'; -- 先给个默认值
|
|
-- 2025-07-21 新增主表字段
|
sku.udf01 = sku_input_data.productLine or ""
|
sku.udf02 = sku_input_data.registerNo or "" -- 注册证号
|
sku.udf03 = sku_input_data.companyName or "" -- 生产企业
|
sku.udf04 = sku_input_data.certCompanyName or "" -- 证件生产厂家
|
sku.udf05 = sku_input_data.consignCompanyWTName or "" -- 委托生产厂家
|
sku.udf06 = sku_input_data.sterilizationDate or "" -- 灭菌日期
|
sku.udf06 = sku_input_data.ex1 or "" -- 扩展字段1
|
sku.udf07 = sku_input_data.ex2 or "" -- 扩展字段2
|
sku.udf08 = sku_input_data.ex3 or "" -- 扩展字段3
|
-- 1. 先处理料箱格参数（确保数据库记录最新）
|
local nRet, strRetInfo = create_or_update_sku_gridbox(strLuaDEID, sku, sku_input_data)
|
if nRet ~= 0 then
|
return 1, "处理SKU_GridBox_Parm失败: " .. strRetInfo
|
end
|
|
-- 2. 基于数据库实际记录生成JSON（不再依赖传入的cidtype）
|
local sku_grid_parm, err = build_sku_grid_json(strLuaDEID, sku.item_code, sku.storer)
|
if err then
|
return 1, "生成SKU_GRID_PARM失败: " .. err
|
end
|
sku.sku_grid_parm = sku_grid_parm -- 直接更新SKU对象的属性
|
|
-- SKU不存在，创建新记录
|
if existingSKU == 'no' then
|
lua.DebugEx(strLuaDEID, "SKU不存在，创建新记录");
|
local createnRet, strRetInfo = m3.CreateDataObj(strLuaDEID, sku)
|
if createnRet ~= 0 then
|
return 1, "创建SKU失败: 信息：" .. strRetInfo
|
end
|
else
|
-- SKU已存在，更新记录
|
local update_sku_obj = {{
|
id = sku.id,
|
attrs = {{
|
attr = "S_CTD_CODE",
|
value = CONST_CTD_CODE
|
}, {
|
attr = "S_ITEM_NAME",
|
value = sku.item_name
|
}, {
|
attr = "S_SPEC",
|
value = sku.spec
|
}, {
|
attr = "S_ITEM_TYPE",
|
value = sku.item_type
|
}, {
|
attr = "S_UNIT",
|
value = sku.unit
|
}, {
|
attr = "S_SHORT_NAME",
|
value = sku.short_name
|
}, {
|
attr = "S_NOTE",
|
value = sku.note
|
}, {
|
attr = "S_ABCTYPE",
|
value = sku.abc_type
|
}, {
|
attr = "C_ISSNMGT",
|
value = sku.is_sn_mgt
|
}, {
|
attr = "C_ISLIFEMGT",
|
value = sku.is_life_mgt
|
}, {
|
attr = "S_IMG_URL",
|
value = sku.img_url
|
}, {
|
attr = "S_CELL_TYPE",
|
value = sku.cell_type
|
}, {
|
attr = "N_LOADING_LIMIT",
|
value = sku.loading_limit
|
}, {
|
attr = "F_LONG",
|
value = sku.long
|
}, {
|
attr = "F_MIDDLE",
|
value = sku.middle
|
}, {
|
attr = "F_SHORT",
|
value = sku.short
|
}, {
|
attr = "S_UDF01",
|
value = sku.udf01
|
}, {
|
attr = "S_UDF02",
|
value = sku.udf02
|
}, {
|
attr = "S_UDF03",
|
value = sku.udf03
|
}, {
|
attr = "S_UDF04",
|
value = sku.udf04
|
}, {
|
attr = "S_UDF05",
|
value = sku.udf05
|
}, {
|
attr = "S_UDF06",
|
value = sku.udf06
|
}, {
|
attr = "S_UDF07",
|
value = sku.udf07
|
}, {
|
attr = "S_UDF08",
|
value = sku.udf08
|
}, {
|
attr = "S_SKU_GRID_PARM",
|
value = sku.sku_grid_parm
|
}}
|
}}
|
nRet, strRetInfo = mobox.updateDataObj(strLuaDEID, "SKU", lua.table2str(update_sku_obj))
|
lua.DebugEx(strLuaDEID, "更新SKU对象结果", nRet .. " " .. strRetInfo);
|
if nRet ~= 0 then
|
return 1, "更新SKU属性失败: " .. strRetInfo
|
end
|
end
|
|
-- 处理产品线数据
|
if sku_input_data.productLine and sku_input_data.productLine ~= "" then
|
local proline_no = sku_input_data.productLine
|
local proline_name = string.match(proline_no, "KH%-(.+)") or proline_no
|
nRet, strRetInfo = create_or_update_proline(strLuaDEID, proline_no, proline_name)
|
if nRet ~= 0 then
|
return 1, "处理产品线数据失败: " .. strRetInfo
|
end
|
end
|
|
-- 处理SKU_UPC条码
|
-- 处理SKU_UPC条码（修改后版本）
|
-- 在create_sku函数中处理SKU_UPC条码的部分修改为：
|
local upc_code = sku_input_data.upc
|
if upc_code and upc_code ~= '' then
|
nRet, strRetInfo = create_or_update_sku_upc(strLuaDEID, sku.storer, sku.item_code, upc_code)
|
if nRet ~= 0 then
|
-- lua.debugex(strLuaDEID, "处理SKU_UPC失败", strRetInfo)
|
return 1, "处理SKU_UPC失败: " .. strRetInfo
|
end
|
-- lua.debugex(strLuaDEID, "处理SKU_UPC成功", upc_code)
|
end
|
-- 处理SKU_GridBox_Parm料箱格参数
|
local nRet, strRetInfo = create_or_update_sku_gridbox(strLuaDEID, sku, sku_input_data)
|
if nRet ~= 0 then
|
return 1, "处理SKU_GridBox_Parm失败: " .. strRetInfo
|
end
|
return 0
|
end
|
|
-- Main函数
|
function SKU_Sync(strLuaDEID)
|
m3.PrintLuaDEInfo(strLuaDEID)
|
-- 初始化最终结果
|
local FinalRes = Create_result()
|
|
-- 1. 获取xml数据包
|
local nRet, soap_xml = mobox.getCurEditDataPacket(strLuaDEID)
|
if nRet ~= 0 then
|
FinalRes = Create_result("failure", "201", "无法获取数据包: " .. soap_xml)
|
local xml_result = xml.json_to_xml(FinalRes, "response")
|
mobox.returnValue(strLuaDEID, 0, xml_result, 0)
|
lua.Stop(strLuaDEID, "获取数据包失败", FinalRes)
|
return
|
end
|
|
-- 2. 解析xml
|
local nRet, parsed_data = xml.parse(soap_xml)
|
if nRet ~= 0 then
|
FinalRes = Create_result("failure", "202", "xml格式非法")
|
local xml_result = xml.json_to_xml(FinalRes, "response")
|
mobox.returnValue(strLuaDEID, 0, xml_result, 0)
|
lua.Stop(strLuaDEID, "xml格式非法", FinalRes)
|
return
|
end
|
|
-- 3. 提取SKU数据
|
local sku_data = parsed_data.Envelope.Body.inCommodityReq.COMMODITY_Input.InputParameters.COMMODITY_TB
|
if not sku_data or not sku_data.COMMODITY_TB_ITEM then
|
FinalRes = Create_result("failure", "203", "xml数据格式错误，缺少COMMODITY_TB_ITEM")
|
local xml_result = xml.json_to_xml(FinalRes, "response")
|
mobox.returnValue(strLuaDEID, 0, xml_result, 0)
|
lua.Stop(strLuaDEID, "xml数据格式错误", FinalRes)
|
return
|
end
|
|
-- 4. 统一处理：确保sku_items是数组
|
local sku_items = sku_data.COMMODITY_TB_ITEM
|
if sku_items[1] == nil then
|
sku_items = {sku_items}
|
end
|
|
-- 5. 遍历所有SKU数据
|
for i = 1, #sku_items do
|
local sku_item = sku_items[i]
|
-- 创建SKU及其UPC
|
local nRet, err_msg = create_sku(strLuaDEID, sku_item)
|
if nRet ~= 0 then
|
wms_base.Warning(strLuaDEID, 1, 601, err_msg, "从GK-WMS系统同步SKU信息")
|
FinalRes = Create_result("failure", "204", "SKU同步失败: " .. err_msg)
|
local xml_result = xml.json_to_xml(FinalRes, "response")
|
mobox.returnValue(strLuaDEID, 0, xml_result, 0)
|
lua.Stop(strLuaDEID, "SKU同步失败: " .. sku_item.skuId, FinalRes)
|
return
|
end
|
end
|
-- 6. 返回成功
|
FinalRes = Create_result("success", "0", "SKU同步成功")
|
local xml_result = xml.json_to_xml(FinalRes, "response")
|
mobox.returnValue(strLuaDEID, 0, xml_result, 0)
|
end
|
