-- ============================================================ -- CRM 数据权限修复脚本(统一以「客户团队」为准) -- -- 背景(实测发现): -- 1. crm_customer.owner_user_id 与 crm_permission 的负责人行不一致(6 个客户) -- 2. 部分合同/商机/联系人没有自身的权限记录(历史数据直接入库) -- -- 说明: -- - 代码已兼容上述异常,本脚本不是必须执行,但执行后数据更规范 -- - 脚本幂等,可重复执行 -- - 执行前请备份 crm_permission、crm_customer -- - 不涉及表结构变更,无需停机 -- -- 枚举值: -- biz_type:2=客户 3=联系人 4=商机 5=合同 -- level :1=负责人 2=只读 3=读写 -- ============================================================ -- ============================================================ -- 一、客户负责人权限行对齐 crm_customer.owner_user_id -- ============================================================ -- 1.1 目标负责人还没有权限行:把旧负责人行改到目标负责人 DROP TEMPORARY TABLE IF EXISTS tmp_perm_customer; CREATE TEMPORARY TABLE tmp_perm_customer AS SELECT biz_id, user_id FROM crm_permission WHERE biz_type = 2 AND deleted = 0; ALTER TABLE tmp_perm_customer ADD INDEX idx_bu (biz_id, user_id); UPDATE crm_permission p JOIN crm_customer c ON c.id = p.biz_id LEFT JOIN tmp_perm_customer t ON t.biz_id = c.id AND t.user_id = c.owner_user_id SET p.user_id = c.owner_user_id, p.update_time = NOW() WHERE p.biz_type = 2 AND p.deleted = 0 AND c.deleted = 0 AND p.level = 1 AND p.user_id <> c.owner_user_id AND t.user_id IS NULL; -- 1.2 目标负责人已有权限行:升级为负责人 UPDATE crm_permission p JOIN crm_customer c ON c.id = p.biz_id SET p.level = 1, p.update_time = NOW() WHERE p.biz_type = 2 AND p.deleted = 0 AND c.deleted = 0 AND p.user_id = c.owner_user_id AND p.level <> 1; -- 1.3 删除重复的旧负责人行(目标负责人行已存在时) DROP TEMPORARY TABLE IF EXISTS tmp_perm_customer; CREATE TEMPORARY TABLE tmp_perm_customer AS SELECT id, biz_id, user_id, level FROM crm_permission WHERE biz_type = 2 AND deleted = 0; ALTER TABLE tmp_perm_customer ADD INDEX idx_bu (biz_id, user_id); DELETE p FROM crm_permission p JOIN crm_customer c ON c.id = p.biz_id JOIN tmp_perm_customer t ON t.biz_id = c.id AND t.user_id = c.owner_user_id AND t.level = 1 WHERE p.biz_type = 2 AND p.deleted = 0 AND c.deleted = 0 AND p.level = 1 AND p.user_id <> c.owner_user_id; DROP TEMPORARY TABLE IF EXISTS tmp_perm_customer; -- ============================================================ -- 二、补齐子数据自身的负责人权限行 -- ============================================================ -- 2.1 合同(biz_type = 5) INSERT INTO crm_permission (biz_type, biz_id, user_id, level, creator, create_time, updater, update_time, deleted) SELECT 5, ct.id, ct.owner_user_id, 1, '1', NOW(), '1', NOW(), 0 FROM crm_contract ct WHERE ct.deleted = 0 AND ct.owner_user_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM crm_permission p WHERE p.biz_type = 5 AND p.biz_id = ct.id AND p.user_id = ct.owner_user_id AND p.deleted = 0); UPDATE crm_permission p JOIN crm_contract ct ON ct.id = p.biz_id AND ct.deleted = 0 SET p.level = 1, p.update_time = NOW() WHERE p.biz_type = 5 AND p.deleted = 0 AND p.user_id = ct.owner_user_id AND p.level <> 1; -- 2.2 商机(biz_type = 4) INSERT INTO crm_permission (biz_type, biz_id, user_id, level, creator, create_time, updater, update_time, deleted) SELECT 4, b.id, b.owner_user_id, 1, '1', NOW(), '1', NOW(), 0 FROM crm_business b WHERE b.deleted = 0 AND b.owner_user_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM crm_permission p WHERE p.biz_type = 4 AND p.biz_id = b.id AND p.user_id = b.owner_user_id AND p.deleted = 0); UPDATE crm_permission p JOIN crm_business b ON b.id = p.biz_id AND b.deleted = 0 SET p.level = 1, p.update_time = NOW() WHERE p.biz_type = 4 AND p.deleted = 0 AND p.user_id = b.owner_user_id AND p.level <> 1; -- 2.3 联系人(biz_type = 3) INSERT INTO crm_permission (biz_type, biz_id, user_id, level, creator, create_time, updater, update_time, deleted) SELECT 3, ct.id, ct.owner_user_id, 1, '1', NOW(), '1', NOW(), 0 FROM crm_contact ct WHERE ct.deleted = 0 AND ct.owner_user_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM crm_permission p WHERE p.biz_type = 3 AND p.biz_id = ct.id AND p.user_id = ct.owner_user_id AND p.deleted = 0); UPDATE crm_permission p JOIN crm_contact ct ON ct.id = p.biz_id AND ct.deleted = 0 SET p.level = 1, p.update_time = NOW() WHERE p.biz_type = 3 AND p.deleted = 0 AND p.user_id = ct.owner_user_id AND p.level <> 1; -- 2.4 销售报价(biz_type = 9) INSERT INTO crm_permission (biz_type, biz_id, user_id, level, creator, create_time, updater, update_time, deleted) SELECT 9, q.id, q.owner_user_id, 1, '1', NOW(), '1', NOW(), 0 FROM crm_sale_quotation q WHERE q.deleted = 0 AND q.owner_user_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM crm_permission p WHERE p.biz_type = 9 AND p.biz_id = q.id AND p.user_id = q.owner_user_id AND p.deleted = 0); UPDATE crm_permission p JOIN crm_sale_quotation q ON q.id = p.biz_id AND q.deleted = 0 SET p.level = 1, p.update_time = NOW() WHERE p.biz_type = 9 AND p.deleted = 0 AND p.user_id = q.owner_user_id AND p.level <> 1; -- 2.5 回款(biz_type = 7) INSERT INTO crm_permission (biz_type, biz_id, user_id, level, creator, create_time, updater, update_time, deleted) SELECT 7, r.id, r.owner_user_id, 1, '1', NOW(), '1', NOW(), 0 FROM crm_receivable r WHERE r.deleted = 0 AND r.owner_user_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM crm_permission p WHERE p.biz_type = 7 AND p.biz_id = r.id AND p.user_id = r.owner_user_id AND p.deleted = 0); UPDATE crm_permission p JOIN crm_receivable r ON r.id = p.biz_id AND r.deleted = 0 SET p.level = 1, p.update_time = NOW() WHERE p.biz_type = 7 AND p.deleted = 0 AND p.user_id = r.owner_user_id AND p.level <> 1; -- 2.6 回款计划(biz_type = 8) INSERT INTO crm_permission (biz_type, biz_id, user_id, level, creator, create_time, updater, update_time, deleted) SELECT 8, rp.id, rp.owner_user_id, 1, '1', NOW(), '1', NOW(), 0 FROM crm_receivable_plan rp WHERE rp.deleted = 0 AND rp.owner_user_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM crm_permission p WHERE p.biz_type = 8 AND p.biz_id = rp.id AND p.user_id = rp.owner_user_id AND p.deleted = 0); UPDATE crm_permission p JOIN crm_receivable_plan rp ON rp.id = p.biz_id AND rp.deleted = 0 SET p.level = 1, p.update_time = NOW() WHERE p.biz_type = 8 AND p.deleted = 0 AND p.user_id = rp.owner_user_id AND p.level <> 1; -- 2.7 开票(biz_type = 10) INSERT INTO crm_permission (biz_type, biz_id, user_id, level, creator, create_time, updater, update_time, deleted) SELECT 10, i.id, i.owner_user_id, 1, '1', NOW(), '1', NOW(), 0 FROM crm_invoice i WHERE i.deleted = 0 AND i.owner_user_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM crm_permission p WHERE p.biz_type = 10 AND p.biz_id = i.id AND p.user_id = i.owner_user_id AND p.deleted = 0); UPDATE crm_permission p JOIN crm_invoice i ON i.id = p.biz_id AND i.deleted = 0 SET p.level = 1, p.update_time = NOW() WHERE p.biz_type = 10 AND p.deleted = 0 AND p.user_id = i.owner_user_id AND p.level <> 1; -- ============================================================ -- 三、校验(可选,执行后跑一下看结果) -- ============================================================ -- 3.1 客户负责人是否已一致(应为 0 行) -- SELECT c.id, c.name, c.owner_user_id, p.user_id AS perm_owner -- FROM crm_customer c -- LEFT JOIN crm_permission p -- ON p.biz_type = 2 AND p.biz_id = c.id AND p.level = 1 AND p.deleted = 0 -- WHERE c.deleted = 0 AND (p.user_id IS NULL OR p.user_id <> c.owner_user_id); -- 3.2 子数据是否都有负责人权限行(应为 0) -- SELECT '合同' AS biz, COUNT(*) AS missing FROM crm_contract ct -- WHERE ct.deleted = 0 AND NOT EXISTS ( -- SELECT 1 FROM crm_permission p WHERE p.biz_type = 5 AND p.biz_id = ct.id AND p.level = 1 AND p.deleted = 0) -- UNION ALL -- SELECT '商机', COUNT(*) FROM crm_business b -- WHERE b.deleted = 0 AND NOT EXISTS ( -- SELECT 1 FROM crm_permission p WHERE p.biz_type = 4 AND p.biz_id = b.id AND p.level = 1 AND p.deleted = 0) -- UNION ALL -- SELECT '联系人', COUNT(*) FROM crm_contact ct -- WHERE ct.deleted = 0 AND NOT EXISTS ( -- SELECT 1 FROM crm_permission p WHERE p.biz_type = 3 AND p.biz_id = ct.id AND p.level = 1 AND p.deleted = 0) -- UNION ALL -- SELECT '销售报价', COUNT(*) FROM crm_sale_quotation q -- WHERE q.deleted = 0 AND NOT EXISTS ( -- SELECT 1 FROM crm_permission p WHERE p.biz_type = 9 AND p.biz_id = q.id AND p.level = 1 AND p.deleted = 0) -- UNION ALL -- SELECT '回款', COUNT(*) FROM crm_receivable r -- WHERE r.deleted = 0 AND NOT EXISTS ( -- SELECT 1 FROM crm_permission p WHERE p.biz_type = 7 AND p.biz_id = r.id AND p.level = 1 AND p.deleted = 0) -- UNION ALL -- SELECT '回款计划', COUNT(*) FROM crm_receivable_plan rp -- WHERE rp.deleted = 0 AND NOT EXISTS ( -- SELECT 1 FROM crm_permission p WHERE p.biz_type = 8 AND p.biz_id = rp.id AND p.level = 1 AND p.deleted = 0) -- UNION ALL -- SELECT '开票', COUNT(*) FROM crm_invoice i -- WHERE i.deleted = 0 AND NOT EXISTS ( -- SELECT 1 FROM crm_permission p WHERE p.biz_type = 10 AND p.biz_id = i.id AND p.level = 1 AND p.deleted = 0);