31 lines
2.3 KiB
SQL
31 lines
2.3 KiB
SQL
-- 客户照片确认功能增量，MySQL 8.0.16+，可重复执行。
|
|
-- 完整执行，遇错停止；须连接同时承载这两个业务库的指定实例。
|
|
-- 仅新增确认时间和权限，不确认/回填任何历史业务单据。
|
|
USE `baiyue_lastleg`;
|
|
-- 客户确认废弃完结时间，可重复执行；仅结构变更。
|
|
SET @ddl = IF(NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ll_cargo_discard_application' AND column_name='customer_confirmed_at'), 'ALTER TABLE ll_cargo_discard_application ADD COLUMN customer_confirmed_at DATETIME(3) NULL COMMENT ''客户确认废弃完结时间''', 'SELECT 1');
|
|
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
|
|
USE `baiyue_iam`;
|
|
-- 仅 baiyue_iam，新增客户废弃确认按钮权限并授权默认客户管理员。
|
|
-- 自定义客户角色由管理员按需分配；不自动扩大其权限。
|
|
-- 可重复执行；授权和持久化安全版本在同一事务提交。
|
|
START TRANSACTION;
|
|
INSERT INTO iam_permission(id,tenant_id,app_code,permission_type,parent_id,permission_code,permission_name,route_path,http_method,sort_no,status,built_in,deleted)
|
|
VALUES (5306183,0,'MERCHANT','BUTTON',5306180,'merchant:overseas-warehouse:discard:confirm','确认废弃完成',NULL,'POST',30,'ACTIVE',1,0)
|
|
ON DUPLICATE KEY UPDATE permission_name=VALUES(permission_name),status='ACTIVE',deleted=0;
|
|
INSERT IGNORE INTO iam_role_permission(tenant_id,role_id,permission_id,created_at)
|
|
SELECT 0,r.id,p.id,NOW(3) FROM iam_role r JOIN iam_permission p
|
|
ON p.tenant_id=0 AND p.app_code='MERCHANT' AND p.permission_code='merchant:overseas-warehouse:discard:confirm'
|
|
WHERE r.tenant_id=0 AND r.app_code='MERCHANT' AND r.role_code='MERCHANT_OWNER' AND r.status='ACTIVE' AND r.deleted=0;
|
|
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
|
|
JOIN iam_membership_role mr ON mr.membership_id=m.id AND mr.tenant_id=m.tenant_id
|
|
JOIN iam_role r ON r.id=mr.role_id
|
|
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.tenant_id=0 AND r.app_code='MERCHANT'
|
|
AND r.role_code='MERCHANT_OWNER' AND r.status='ACTIVE' AND r.deleted=0
|
|
ON DUPLICATE KEY UPDATE permission_version=permission_version+1;
|
|
COMMIT;
|