shanxia2.0/docs/sql/20261027_wechat_cert_notice_role.sql

-- MySQL 5.7：证件到期通知接收人角色（可重复执行）。
-- 仅建立角色及 Web 证件提醒列表菜单/祖先授权，不自动绑定任何用户。
-- 新角色为 data_scope=5 且无自定义明细：不扩大用户已有多角色数据范围。
-- 既有同名角色的状态、数据权限及自定义明细保持不变。
-- 使用临时过程遍历任意深度祖先；需要 CREATE ROUTINE 权限。
SET NAMES utf8mb4;

DELIMITER $$
DROP PROCEDURE IF EXISTS migrate_20261027_cert_notice_role$$
CREATE PROCEDURE migrate_20261027_cert_notice_role()
BEGIN
  DECLARE added_rows INT DEFAULT 1;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    DROP TEMPORARY TABLE IF EXISTS tmp_cert_notice_valid;
    DROP TEMPORARY TABLE IF EXISTS tmp_cert_notice_next;
    DROP TEMPORARY TABLE IF EXISTS tmp_cert_notice_frontier;
    DROP TEMPORARY TABLE IF EXISTS tmp_cert_notice_chain;
    RESIGNAL;
  END;

  DROP TEMPORARY TABLE IF EXISTS tmp_cert_notice_valid;
  DROP TEMPORARY TABLE IF EXISTS tmp_cert_notice_next;
  DROP TEMPORARY TABLE IF EXISTS tmp_cert_notice_frontier;
  DROP TEMPORARY TABLE IF EXISTS tmp_cert_notice_chain;
  CREATE TEMPORARY TABLE tmp_cert_notice_chain (
    tenant_no VARCHAR(64) NOT NULL,
    leaf_id VARCHAR(64) NOT NULL,
    menu_id VARCHAR(64) NOT NULL,
    parent_id VARCHAR(64) NOT NULL,
    path VARCHAR(200) NOT NULL,
    PRIMARY KEY (tenant_no, leaf_id, menu_id)
  ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  CREATE TEMPORARY TABLE tmp_cert_notice_frontier LIKE tmp_cert_notice_chain;
  CREATE TEMPORARY TABLE tmp_cert_notice_next LIKE tmp_cert_notice_chain;
  CREATE TEMPORARY TABLE tmp_cert_notice_valid (
    tenant_no VARCHAR(64) NOT NULL,
    leaf_id VARCHAR(64) NOT NULL,
    PRIMARY KEY (tenant_no, leaf_id)
  ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

  START TRANSACTION;
  INSERT INTO sys_role (
    id, role_name, data_scope, status, tenant_no, order_num, remark,
    create_by, create_dept_id, create_time, update_by, update_time, del_flag
  )
  SELECT UUID(), '证件到期通知接收人', '5', '1', t.tenant_no, 0,
    '接收每日证件到期汇总；数据权限由已有角色并集决定，请手动绑定接收用户',
    'system', '', NOW(), 'system', NOW(), 0
  FROM (SELECT DISTINCT tenant_no FROM sys_tenant WHERE del_flag=0) t
  WHERE NOT EXISTS (
    SELECT 1 FROM sys_role r WHERE r.tenant_no=t.tenant_no
      AND r.role_name='证件到期通知接收人' AND r.del_flag=0
  );

  INSERT INTO tmp_cert_notice_chain
  SELECT m.tenant_no, m.id, m.id, m.parent_id, m.path
  FROM sys_menu m
  WHERE m.del_flag=0 AND m.status='1' AND m.menu_type='C'
    AND m.path='document-reminder'
    AND m.component='pl/inventory/document-reminder'
    AND m.perms='pl:document:list'
    AND EXISTS (SELECT 1 FROM sys_tenant t WHERE t.tenant_no=m.tenant_no AND t.del_flag=0);
  INSERT INTO tmp_cert_notice_frontier SELECT * FROM tmp_cert_notice_chain;

  -- 始终同租户向上遍历，仅授予目录祖先，绝不加入按钮或修改权限。
  WHILE added_rows > 0 DO
    DELETE FROM tmp_cert_notice_next;
    INSERT INTO tmp_cert_notice_next
    SELECT DISTINCT f.tenant_no, f.leaf_id, p.id, p.parent_id, p.path
    FROM tmp_cert_notice_frontier f
    JOIN sys_menu p ON p.id=f.parent_id AND p.tenant_no=f.tenant_no
    LEFT JOIN tmp_cert_notice_chain seen
      ON seen.tenant_no=f.tenant_no AND seen.leaf_id=f.leaf_id AND seen.menu_id=p.id
    WHERE p.del_flag=0 AND p.status='1' AND p.menu_type='M'
      AND (p.perms IS NULL OR p.perms='' OR p.perms LIKE '%:list')
      AND seen.menu_id IS NULL;
    SET added_rows = ROW_COUNT();
    INSERT INTO tmp_cert_notice_chain SELECT * FROM tmp_cert_notice_next;
    DELETE FROM tmp_cert_notice_frontier;
    INSERT INTO tmp_cert_notice_frontier SELECT * FROM tmp_cert_notice_next;
  END WHILE;

  -- 只有完整抵达本租户 /web 虚拟根的链有效；小程序或跨租户/断裂链不授权。
  INSERT INTO tmp_cert_notice_valid
  SELECT DISTINCT tenant_no, leaf_id FROM tmp_cert_notice_chain
  WHERE parent_id='0' AND path='/web';

  INSERT INTO sys_role_menu (
    id, role_id, menu_id, tenant_no, order_num, remark,
    create_by, create_dept_id, create_time, update_by, update_time, del_flag
  )
  SELECT UUID(), assignments.role_id, assignments.menu_id, assignments.tenant_no, 0,
    '证件到期通知接收人：Web 证件提醒列表及祖先目录',
    'system', '', NOW(), 'system', NOW(), 0
  FROM (
    SELECT DISTINCT r.id AS role_id, c.menu_id, r.tenant_no
    FROM sys_role r
    JOIN tmp_cert_notice_chain c ON c.tenant_no=r.tenant_no
    JOIN tmp_cert_notice_valid v ON v.tenant_no=c.tenant_no AND v.leaf_id=c.leaf_id
    WHERE r.role_name='证件到期通知接收人' AND r.del_flag=0
  ) assignments
  WHERE NOT EXISTS (
    SELECT 1 FROM sys_role_menu rm
    WHERE rm.role_id=assignments.role_id AND rm.menu_id=assignments.menu_id
      AND rm.tenant_no=assignments.tenant_no AND rm.del_flag=0
  );
  COMMIT;

  DROP TEMPORARY TABLE tmp_cert_notice_valid;
  DROP TEMPORARY TABLE tmp_cert_notice_next;
  DROP TEMPORARY TABLE tmp_cert_notice_frontier;
  DROP TEMPORARY TABLE tmp_cert_notice_chain;
END$$
CALL migrate_20261027_cert_notice_role()$$
DROP PROCEDURE migrate_20261027_cert_notice_role$$
DELIMITER ;