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;
|