83 lines
4.4 KiB
SQL
83 lines
4.4 KiB
SQL
-- 修复：注册「创建企业」审批通过后，申请人登录提示「该账号未绑定任何企业」。
|
||
--
|
||
-- 两个叠在一起的 bug：
|
||
-- ① finishApply 没把新建租户 tid 回写到申请单 → yz_auth_tenant_apply.tid 一直是 0，
|
||
-- 事后无法按 tid 追溯/修数据（代码已修复）。
|
||
-- ② 绑定时调用 SyncTenantUser 按「手机/邮箱/账号」归并键匹配身份，账号注册的身份
|
||
-- 没有手机号、邮箱，企业内账号又是拼出来的 user{id}，匹配不到 → 新建了一个身份
|
||
-- （account=user{id}）并绑定到新租户，申请人自己的身份反而没有绑定记录
|
||
-- （代码已改为按 identity_id 直接写绑定）。
|
||
-- 本脚本修正【已审批的历史数据】，下列语句均可直接执行，无需替换占位符。
|
||
|
||
-- ============ ① 回填申请单的 tid（按企业名称匹配新建的租户） ============
|
||
UPDATE yz_auth_tenant_apply a
|
||
JOIN yz_system_tenant t
|
||
ON t.tenant_name = a.tenant_name AND t.delete_time IS NULL
|
||
SET a.tid = t.id, a.update_time = NOW()
|
||
WHERE a.type = 2 AND a.status = 1 AND a.delete_time IS NULL
|
||
AND (a.tid = 0 OR a.tid IS NULL);
|
||
|
||
-- ============ ② 排查：申请 / 身份 / 绑定 / 租户对照 ============
|
||
SELECT a.id AS apply_id, a.status AS apply_status, a.tid, a.tenant_name,
|
||
a.identity_id AS apply_identity, ai.account AS apply_account, ai.mobile AS apply_mobile,
|
||
tu.id AS bind_id, tu.identity_id AS bind_identity, bi.account AS bind_account,
|
||
tu.status AS bind_status, tu.delete_time AS bind_deleted, tu.group_id,
|
||
t.status AS tenant_status
|
||
FROM yz_auth_tenant_apply a
|
||
LEFT JOIN yz_auth_identity ai ON ai.id = a.identity_id
|
||
LEFT JOIN yz_auth_tenant_user tu ON tu.tid = a.tid
|
||
LEFT JOIN yz_auth_identity bi ON bi.id = tu.identity_id
|
||
LEFT JOIN yz_system_tenant t ON t.id = a.tid
|
||
WHERE a.delete_time IS NULL
|
||
ORDER BY a.id DESC
|
||
LIMIT 20;
|
||
-- 关注：bind_identity 是否 = apply_identity；bind_status=1；bind_deleted=NULL；tenant_status=1
|
||
|
||
-- ============ ③ 修正绑错人的行（改指到申请人，并复位状态） ============
|
||
UPDATE yz_auth_tenant_user tu
|
||
JOIN yz_auth_tenant_apply a
|
||
ON a.tid = tu.tid AND a.type = 2 AND a.status = 1 AND a.delete_time IS NULL
|
||
SET tu.identity_id = a.identity_id, tu.status = 1, tu.is_default = 1,
|
||
tu.delete_time = NULL, tu.update_time = NOW()
|
||
WHERE tu.identity_id <> a.identity_id;
|
||
|
||
-- ============ ④ 修正「绑对人但状态异常」的行（status≠1 或已软删） ============
|
||
UPDATE yz_auth_tenant_user tu
|
||
JOIN yz_auth_tenant_apply a
|
||
ON a.tid = tu.tid AND a.type = 2 AND a.status = 1 AND a.delete_time IS NULL
|
||
SET tu.status = 1, tu.is_default = 1, tu.delete_time = NULL, tu.update_time = NOW()
|
||
WHERE tu.identity_id = a.identity_id
|
||
AND (tu.status <> 1 OR tu.delete_time IS NOT NULL);
|
||
|
||
-- ============ ⑤ 兜底：申请人压根没有绑定行时补一条 ============
|
||
INSERT INTO yz_auth_tenant_user
|
||
(tid, identity_id, account, name, phone, email, group_id, org_id, is_default, status, create_time)
|
||
SELECT a.tid, a.identity_id,
|
||
COALESCE(NULLIF(i.mobile, ''), NULLIF(i.account, ''), CONCAT('user', a.identity_id)),
|
||
COALESCE(i.nickname, ''), i.mobile, i.email,
|
||
0, 0, 1, 1, NOW()
|
||
FROM yz_auth_tenant_apply a
|
||
JOIN yz_auth_identity i ON i.id = a.identity_id
|
||
WHERE a.type = 2 AND a.status = 1 AND a.tid > 0 AND a.delete_time IS NULL
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM yz_auth_tenant_user tu
|
||
WHERE tu.tid = a.tid AND tu.identity_id = a.identity_id
|
||
);
|
||
|
||
-- ============ ⑥ 核对：申请人名下应能看到企业 ============
|
||
SELECT tu.identity_id, tu.tid, tu.status AS bind_status, tu.is_default, tu.group_id,
|
||
t.tenant_name, t.status AS tenant_status
|
||
FROM yz_auth_tenant_user tu
|
||
JOIN yz_system_tenant t ON t.id = tu.tid
|
||
JOIN yz_auth_tenant_apply a
|
||
ON a.tid = tu.tid AND a.type = 2 AND a.status = 1 AND a.identity_id = tu.identity_id;
|
||
|
||
-- ============ ⑦ 可选：清理审批时误建的身份（确认无任何绑定后再执行） ============
|
||
-- SELECT id, account, mobile, last_login_at FROM yz_auth_identity
|
||
-- WHERE account LIKE 'user%' AND last_login_at IS NULL
|
||
-- AND NOT EXISTS (SELECT 1 FROM yz_auth_tenant_user tu WHERE tu.identity_id = yz_auth_identity.id);
|
||
-- DELETE i FROM yz_auth_identity i
|
||
-- WHERE i.account LIKE 'user%' AND i.last_login_at IS NULL
|
||
-- AND NOT EXISTS (SELECT 1 FROM yz_auth_tenant_user tu WHERE tu.identity_id = i.id)
|
||
-- AND NOT EXISTS (SELECT 1 FROM yz_auth_identity_third th WHERE th.identity_id = i.id);
|