-- =============================================
|
-- 修复：部门/岗位的 tenant_id 错误（0 应为 1）
|
--
|
-- 现象：
|
-- 1) 员工管理列表「部门」列为空（部分或全部）
|
-- 2) 部门管理页面看不到大部分部门（只剩「总公司」）
|
-- 3) 新增员工报「部门不存在 / 岗位不存在」，无法保存
|
-- 4) 员工列表按部门筛选查不到数据
|
--
|
-- 根因：
|
-- 导入部门/岗位时未写 tenant_id，落成 0；而系统按登录租户（通常为 1）查询，
|
-- 导致这些部门/岗位在业务上"不存在"。
|
-- （hrm_employee 表无 tenant_id 列，所以员工本身能看到，只是部门名解析不到）
|
--
|
-- 执行前请确认目标租户 id（单租户系统一般为 1）：
|
-- SELECT id, name FROM system_tenant;
|
-- =============================================
|
SET NAMES utf8mb4;
|
|
-- 目标租户（按实际情况修改）
|
SET @T := 1;
|
|
-- ---------- 1. 执行前检查 ----------
|
SELECT '执行前：部门租户分布' AS step, tenant_id, COUNT(*) AS cnt
|
FROM system_dept WHERE deleted = b'0' GROUP BY tenant_id;
|
|
SELECT '执行前：岗位租户分布' AS step, tenant_id, COUNT(*) AS cnt
|
FROM system_post WHERE deleted = b'0' GROUP BY tenant_id;
|
|
-- 重名检查：必须都为 0，否则 UPDATE 会违反唯一索引
|
SELECT '部门重名数(须为0)' AS step, COUNT(*) AS cnt
|
FROM system_dept d0
|
JOIN system_dept d1 ON d1.tenant_id = @T AND d1.name = d0.name AND d1.deleted = b'0'
|
WHERE d0.tenant_id = 0 AND d0.deleted = b'0';
|
|
SELECT '岗位重名数(须为0)' AS step, COUNT(*) AS cnt
|
FROM system_post p0
|
JOIN system_post p1 ON p1.tenant_id = @T AND p1.name = p0.name AND p1.deleted = b'0'
|
WHERE p0.tenant_id = 0 AND p0.deleted = b'0';
|
|
-- ---------- 2. 备份 ----------
|
CREATE TABLE IF NOT EXISTS bak_system_dept_tenant AS SELECT * FROM system_dept;
|
CREATE TABLE IF NOT EXISTS bak_system_post_tenant AS SELECT * FROM system_post;
|
|
-- ---------- 3. 修复 ----------
|
UPDATE system_dept SET tenant_id = @T WHERE tenant_id = 0;
|
UPDATE system_post SET tenant_id = @T WHERE tenant_id = 0;
|
|
-- ---------- 4. 执行后核对 ----------
|
SELECT '执行后：部门租户分布' AS step, tenant_id, COUNT(*) AS cnt
|
FROM system_dept WHERE deleted = b'0' GROUP BY tenant_id;
|
|
SELECT '执行后：岗位租户分布' AS step, tenant_id, COUNT(*) AS cnt
|
FROM system_post WHERE deleted = b'0' GROUP BY tenant_id;
|
|
-- 员工部门悬空数（应为 0，表示所有员工的部门都能解析到）
|
SELECT '员工部门悬空数(应为0)' AS step, COUNT(*) AS cnt
|
FROM hrm_employee e
|
LEFT JOIN system_dept d ON d.id = e.dept_id AND d.tenant_id = @T AND d.deleted = b'0'
|
WHERE e.deleted = b'0' AND d.id IS NULL;
|
|
SELECT '修复完成，请让用户重新登录后刷新页面' AS result;
|
