65 lines
2.0 KiB
Java
65 lines
2.0 KiB
Java
|
-- 修复：胶料分类字典（XSLMES_RUBBER）数据库有数据但页面不展示
|
|||
|
|
-- 场景：开启多租户后，sys_category 的 tenant_id 与当前登录租户不一致/为空
|
|||
|
|
SET NAMES utf8mb4;
|
|||
|
|
|
|||
|
|
-- 1) 目标租户：默认取 admin 用户租户；若为空则回退到 0
|
|||
|
|
SET @target_tenant_id = (
|
|||
|
|
SELECT COALESCE(tenant_id, 0)
|
|||
|
|
FROM sys_user
|
|||
|
|
WHERE username = 'admin'
|
|||
|
|
ORDER BY create_time ASC
|
|||
|
|
LIMIT 1
|
|||
|
|
);
|
|||
|
|
SET @target_tenant_id = IFNULL(@target_tenant_id, 0);
|
|||
|
|
|
|||
|
|
-- 2) 定位根分类编码
|
|||
|
|
SET @rubber_code = 'XSLMES_RUBBER';
|
|||
|
|
|
|||
|
|
-- 若根节点不存在则补一个最小根节点（避免前端 pcode 查询直接失败）
|
|||
|
|
INSERT INTO sys_category (id, pid, name, code, has_child, tenant_id, create_by, create_time, update_by, update_time)
|
|||
|
|
SELECT '1994000000000000001', '0', 'MES胶料分类', @rubber_code, '1', @target_tenant_id, 'admin', NOW(), 'admin', NOW()
|
|||
|
|
FROM dual
|
|||
|
|
WHERE NOT EXISTS (
|
|||
|
|
SELECT 1 FROM sys_category WHERE code = @rubber_code
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
SET @rubber_root_id = (
|
|||
|
|
SELECT id
|
|||
|
|
FROM sys_category
|
|||
|
|
WHERE code = @rubber_code
|
|||
|
|
ORDER BY create_time ASC
|
|||
|
|
LIMIT 1
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- 3) 若根节点存在，统一修复租户与父子标记
|
|||
|
|
UPDATE sys_category
|
|||
|
|
SET tenant_id = @target_tenant_id
|
|||
|
|
WHERE id = @rubber_root_id;
|
|||
|
|
|
|||
|
|
UPDATE sys_category
|
|||
|
|
SET tenant_id = @target_tenant_id
|
|||
|
|
WHERE pid = @rubber_root_id;
|
|||
|
|
|
|||
|
|
-- 根节点是否有子节点，按真实数据回写
|
|||
|
|
UPDATE sys_category
|
|||
|
|
SET has_child = CASE
|
|||
|
|
WHEN EXISTS (SELECT 1 FROM (SELECT id FROM sys_category WHERE pid = @rubber_root_id LIMIT 1) t) THEN '1'
|
|||
|
|
ELSE '0'
|
|||
|
|
END
|
|||
|
|
WHERE id = @rubber_root_id;
|
|||
|
|
|
|||
|
|
-- 子节点统一标记为无子（当前这批分类通常为叶子）
|
|||
|
|
UPDATE sys_category
|
|||
|
|
SET has_child = '0'
|
|||
|
|
WHERE pid = @rubber_root_id;
|
|||
|
|
|
|||
|
|
-- 4) 结果检查（执行后看返回）
|
|||
|
|
SELECT 'ROOT' AS level_tag, id, pid, code, name, tenant_id, has_child
|
|||
|
|
FROM sys_category
|
|||
|
|
WHERE id = @rubber_root_id
|
|||
|
|
UNION ALL
|
|||
|
|
SELECT 'CHILD' AS level_tag, id, pid, code, name, tenant_id, has_child
|
|||
|
|
FROM sys_category
|
|||
|
|
WHERE pid = @rubber_root_id
|
|||
|
|
ORDER BY level_tag, code;
|