From 976e28e9f77f8764010ac151592a39808f360a4e Mon Sep 17 00:00:00 2001
From: tuqin <tuqin@tuqin.cn>
Date: 星期四, 22 十月 2026 19:58:38 +0800
Subject: [PATCH] fix(sql): 新增部门/岗位 tenant_id 修复脚本（修复员工部门为空、部门管理缺失、新增员工报部门不存在）

---
 sql/mysql/fix_dept_post_tenant.sql |   62 +++++++++++++++++++++++++++++++
 1 files changed, 62 insertions(+), 0 deletions(-)

diff --git a/sql/mysql/fix_dept_post_tenant.sql b/sql/mysql/fix_dept_post_tenant.sql
new file mode 100644
index 0000000..02be082
--- /dev/null
+++ b/sql/mysql/fix_dept_post_tenant.sql
@@ -0,0 +1,62 @@
+-- =============================================
+-- 淇锛氶儴闂�/宀椾綅鐨� tenant_id 閿欒锛�0 搴斾负 1锛�
+--
+-- 鐜拌薄锛�
+--   1) 鍛樺伐绠＄悊鍒楄〃銆岄儴闂ㄣ�嶅垪涓虹┖锛堥儴鍒嗘垨鍏ㄩ儴锛�
+--   2) 閮ㄩ棬绠＄悊椤甸潰鐪嬩笉鍒板ぇ閮ㄥ垎閮ㄩ棬锛堝彧鍓┿�屾�诲叕鍙搞�嶏級
+--   3) 鏂板鍛樺伐鎶ャ�岄儴闂ㄤ笉瀛樺湪 / 宀椾綅涓嶅瓨鍦ㄣ�嶏紝鏃犳硶淇濆瓨
+--   4) 鍛樺伐鍒楄〃鎸夐儴闂ㄧ瓫閫夋煡涓嶅埌鏁版嵁
+--
+-- 鏍瑰洜锛�
+--   瀵煎叆閮ㄩ棬/宀椾綅鏃舵湭鍐� tenant_id锛岃惤鎴� 0锛涜�岀郴缁熸寜鐧诲綍绉熸埛锛堥�氬父涓� 1锛夋煡璇紝
+--   瀵艰嚧杩欎簺閮ㄩ棬/宀椾綅鍦ㄤ笟鍔′笂"涓嶅瓨鍦�"銆�
+--   锛坔rm_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;

--
Gitblit v1.9.3