84 lines
3.3 KiB
SQL
84 lines
3.3 KiB
SQL
-- 海外费用菜单定向修复，MySQL 8.0+，目标只能 baiyue_iam。
|
|
-- 将“海外费用管理”改为“海外费用明细”，并挂到“财务管理”下。
|
|
-- 权限码、路由和角色授权保持不变；可重复执行。
|
|
USE baiyue_iam;
|
|
SET NAMES utf8mb4;
|
|
|
|
DROP PROCEDURE IF EXISTS repair_overseas_fee_menu_parent;
|
|
DELIMITER $$
|
|
CREATE PROCEDURE repair_overseas_fee_menu_parent()
|
|
BEGIN
|
|
DECLARE v_menu_changed INT DEFAULT 0;
|
|
DECLARE EXIT HANDLER FOR SQLEXCEPTION
|
|
BEGIN
|
|
ROLLBACK;
|
|
RESIGNAL;
|
|
END;
|
|
|
|
IF (SELECT COUNT(*) FROM iam_permission
|
|
WHERE id=5105000 AND tenant_id=0 AND app_code='PLATFORM'
|
|
AND permission_type='MENU' AND permission_code='platform:finance'
|
|
AND status='ACTIVE' AND deleted=0)<>1 THEN
|
|
SIGNAL SQLSTATE '45000'
|
|
SET MESSAGE_TEXT='Finance parent menu platform:finance is missing or inactive';
|
|
END IF;
|
|
|
|
IF EXISTS (SELECT 1 FROM iam_permission
|
|
WHERE (id=5117200 AND (permission_code<>'platform:overseas-fee:list'
|
|
OR tenant_id<>0 OR app_code<>'PLATFORM' OR permission_type<>'MENU'))
|
|
OR (permission_code='platform:overseas-fee:list'
|
|
AND (id<>5117200 OR tenant_id<>0 OR app_code<>'PLATFORM'
|
|
OR permission_type<>'MENU'))) THEN
|
|
SIGNAL SQLSTATE '45000'
|
|
SET MESSAGE_TEXT='Overseas fee menu permission identity conflict';
|
|
END IF;
|
|
|
|
IF (SELECT COUNT(*) FROM iam_permission
|
|
WHERE id=5117200 AND tenant_id=0 AND app_code='PLATFORM'
|
|
AND permission_type='MENU' AND permission_code='platform:overseas-fee:list')<>1 THEN
|
|
SIGNAL SQLSTATE '45000'
|
|
SET MESSAGE_TEXT='Overseas fee menu platform:overseas-fee:list is missing';
|
|
END IF;
|
|
|
|
START TRANSACTION;
|
|
UPDATE iam_permission
|
|
SET parent_id=5105000,
|
|
permission_name='海外费用明细',
|
|
route_path='/overseas-business/fees',
|
|
http_method='GET',
|
|
sort_no=60,
|
|
status='ACTIVE',
|
|
deleted=0,
|
|
updated_at=CURRENT_TIMESTAMP(3)
|
|
WHERE id=5117200 AND tenant_id=0 AND app_code='PLATFORM'
|
|
AND permission_type='MENU' AND permission_code='platform:overseas-fee:list'
|
|
AND (parent_id<>5105000 OR permission_name<>'海外费用明细'
|
|
OR route_path<>'/overseas-business/fees' OR http_method<>'GET'
|
|
OR sort_no<>60 OR status<>'ACTIVE' OR deleted<>0);
|
|
SET v_menu_changed=ROW_COUNT();
|
|
|
|
-- 菜单实际变化时递增已获该菜单权限用户的安全版本，刷新旧授权快照。
|
|
IF v_menu_changed>0 THEN
|
|
INSERT INTO iam_security_subject_version(auth_user_id,permission_version)
|
|
SELECT DISTINCT u.auth_user_id,1
|
|
FROM iam_user u
|
|
JOIN iam_membership m ON m.user_id=u.id AND m.tenant_id=0
|
|
JOIN iam_membership_role mr ON mr.membership_id=m.id AND mr.tenant_id=0
|
|
JOIN iam_role r ON r.id=mr.role_id AND r.tenant_id=0
|
|
JOIN iam_role_permission rp ON rp.role_id=r.id
|
|
AND rp.permission_id=5117200 AND rp.tenant_id=0
|
|
WHERE u.auth_user_id IS NOT NULL AND u.status='ACTIVE' AND u.deleted=0
|
|
AND m.status='ACTIVE' AND m.deleted=0
|
|
AND r.app_code='PLATFORM' AND r.status='ACTIVE' AND r.deleted=0
|
|
ON DUPLICATE KEY UPDATE permission_version=permission_version+1;
|
|
END IF;
|
|
|
|
COMMIT;
|
|
END$$
|
|
DELIMITER ;
|
|
CALL repair_overseas_fee_menu_parent();
|
|
DROP PROCEDURE repair_overseas_fee_menu_parent;
|
|
|
|
SELECT id,parent_id,permission_code,permission_name,route_path,sort_no
|
|
FROM iam_permission WHERE id IN (5105000,5117200) ORDER BY id;
|