277 lines
8.7 KiB
MySQL
277 lines
8.7 KiB
MySQL
|
begin;
|
||
|
|
|
||
|
|
set search_path to public;
|
||
|
|
|
||
|
|
do $$
|
||
|
|
declare
|
||
|
|
v_system_parent_id bigint;
|
||
|
|
v_menu_perm_id bigint;
|
||
|
|
v_view_perm_id bigint;
|
||
|
|
v_update_perm_id bigint;
|
||
|
|
v_test_perm_id bigint;
|
||
|
|
v_admin_user_id bigint;
|
||
|
|
v_admin_role_id bigint;
|
||
|
|
begin
|
||
|
|
perform setval('sys_permission_perm_id_seq', coalesce((select max(perm_id) from sys_permission), 0) + 1, false);
|
||
|
|
perform setval('sys_role_permission_id_seq', coalesce((select max(id) from sys_role_permission), 0) + 1, false);
|
||
|
|
|
||
|
|
select perm_id
|
||
|
|
into v_system_parent_id
|
||
|
|
from sys_permission
|
||
|
|
where code = 'system'
|
||
|
|
and coalesce(is_deleted, 0) = 0
|
||
|
|
order by perm_id
|
||
|
|
limit 1;
|
||
|
|
|
||
|
|
if v_system_parent_id is null then
|
||
|
|
insert into sys_permission (
|
||
|
|
parent_id, name, code, perm_type, level, path, component, icon,
|
||
|
|
sort_order, is_visible, status, description, meta, is_deleted, created_at, updated_at
|
||
|
|
) values (
|
||
|
|
null, '系统管理', 'system', 'directory', 1, null, null, 'SettingOutlined',
|
||
|
|
110, 1, 1, '系统管理目录', '{}'::jsonb, 0, now(), now()
|
||
|
|
)
|
||
|
|
returning perm_id into v_system_parent_id;
|
||
|
|
end if;
|
||
|
|
|
||
|
|
select perm_id
|
||
|
|
into v_menu_perm_id
|
||
|
|
from sys_permission
|
||
|
|
where code = 'menu:report-reminder-settings'
|
||
|
|
order by perm_id
|
||
|
|
limit 1;
|
||
|
|
|
||
|
|
if v_menu_perm_id is null then
|
||
|
|
insert into sys_permission (
|
||
|
|
parent_id, name, code, perm_type, level, path, component, icon,
|
||
|
|
sort_order, is_visible, status, description, meta, is_deleted, created_at, updated_at
|
||
|
|
) values (
|
||
|
|
v_system_parent_id, '日报提醒设置', 'menu:report-reminder-settings', 'menu', 2,
|
||
|
|
'/report-reminder-settings', null, 'BellOutlined', 6, 1, 1,
|
||
|
|
'企业微信日报提醒与提交通知设置页面', jsonb_build_object('tenantScoped', true), 0, now(), now()
|
||
|
|
)
|
||
|
|
returning perm_id into v_menu_perm_id;
|
||
|
|
else
|
||
|
|
update sys_permission
|
||
|
|
set parent_id = v_system_parent_id,
|
||
|
|
name = '日报提醒设置',
|
||
|
|
perm_type = 'menu',
|
||
|
|
level = 2,
|
||
|
|
path = '/report-reminder-settings',
|
||
|
|
component = null,
|
||
|
|
icon = 'BellOutlined',
|
||
|
|
sort_order = 6,
|
||
|
|
is_visible = 1,
|
||
|
|
status = 1,
|
||
|
|
description = '企业微信日报提醒与提交通知设置页面',
|
||
|
|
meta = jsonb_build_object('tenantScoped', true),
|
||
|
|
is_deleted = 0,
|
||
|
|
updated_at = now()
|
||
|
|
where perm_id = v_menu_perm_id;
|
||
|
|
end if;
|
||
|
|
|
||
|
|
select perm_id
|
||
|
|
into v_view_perm_id
|
||
|
|
from sys_permission
|
||
|
|
where code = 'report_reminder_config:view'
|
||
|
|
order by perm_id
|
||
|
|
limit 1;
|
||
|
|
|
||
|
|
if v_view_perm_id is null then
|
||
|
|
insert into sys_permission (
|
||
|
|
parent_id, name, code, perm_type, level, path, component, icon,
|
||
|
|
sort_order, is_visible, status, description, meta, is_deleted, created_at, updated_at
|
||
|
|
) values (
|
||
|
|
v_menu_perm_id, '查看日报提醒设置', 'report_reminder_config:view', 'button', 3,
|
||
|
|
null, null, null, 1, 1, 1, '查看日报提醒配置和企业微信应用参数', '{}'::jsonb, 0, now(), now()
|
||
|
|
)
|
||
|
|
returning perm_id into v_view_perm_id;
|
||
|
|
else
|
||
|
|
update sys_permission
|
||
|
|
set parent_id = v_menu_perm_id,
|
||
|
|
name = '查看日报提醒设置',
|
||
|
|
perm_type = 'button',
|
||
|
|
level = 3,
|
||
|
|
sort_order = 1,
|
||
|
|
is_visible = 1,
|
||
|
|
status = 1,
|
||
|
|
description = '查看日报提醒配置和企业微信应用参数',
|
||
|
|
meta = '{}'::jsonb,
|
||
|
|
is_deleted = 0,
|
||
|
|
updated_at = now()
|
||
|
|
where perm_id = v_view_perm_id;
|
||
|
|
end if;
|
||
|
|
|
||
|
|
select perm_id
|
||
|
|
into v_update_perm_id
|
||
|
|
from sys_permission
|
||
|
|
where code = 'report_reminder_config:update'
|
||
|
|
order by perm_id
|
||
|
|
limit 1;
|
||
|
|
|
||
|
|
if v_update_perm_id is null then
|
||
|
|
insert into sys_permission (
|
||
|
|
parent_id, name, code, perm_type, level, path, component, icon,
|
||
|
|
sort_order, is_visible, status, description, meta, is_deleted, created_at, updated_at
|
||
|
|
) values (
|
||
|
|
v_menu_perm_id, '修改日报提醒设置', 'report_reminder_config:update', 'button', 3,
|
||
|
|
null, null, null, 2, 1, 1, '保存日报提醒配置和企业微信应用参数', '{}'::jsonb, 0, now(), now()
|
||
|
|
)
|
||
|
|
returning perm_id into v_update_perm_id;
|
||
|
|
else
|
||
|
|
update sys_permission
|
||
|
|
set parent_id = v_menu_perm_id,
|
||
|
|
name = '修改日报提醒设置',
|
||
|
|
perm_type = 'button',
|
||
|
|
level = 3,
|
||
|
|
sort_order = 2,
|
||
|
|
is_visible = 1,
|
||
|
|
status = 1,
|
||
|
|
description = '保存日报提醒配置和企业微信应用参数',
|
||
|
|
meta = '{}'::jsonb,
|
||
|
|
is_deleted = 0,
|
||
|
|
updated_at = now()
|
||
|
|
where perm_id = v_update_perm_id;
|
||
|
|
end if;
|
||
|
|
|
||
|
|
select perm_id
|
||
|
|
into v_test_perm_id
|
||
|
|
from sys_permission
|
||
|
|
where code = 'report_reminder_config:test'
|
||
|
|
order by perm_id
|
||
|
|
limit 1;
|
||
|
|
|
||
|
|
if v_test_perm_id is null then
|
||
|
|
insert into sys_permission (
|
||
|
|
parent_id, name, code, perm_type, level, path, component, icon,
|
||
|
|
sort_order, is_visible, status, description, meta, is_deleted, created_at, updated_at
|
||
|
|
) values (
|
||
|
|
v_menu_perm_id, '测试发送日报提醒', 'report_reminder_config:test', 'button', 3,
|
||
|
|
null, null, null, 3, 1, 1, '触发企业微信测试发送', '{}'::jsonb, 0, now(), now()
|
||
|
|
)
|
||
|
|
returning perm_id into v_test_perm_id;
|
||
|
|
else
|
||
|
|
update sys_permission
|
||
|
|
set parent_id = v_menu_perm_id,
|
||
|
|
name = '测试发送日报提醒',
|
||
|
|
perm_type = 'button',
|
||
|
|
level = 3,
|
||
|
|
sort_order = 3,
|
||
|
|
is_visible = 1,
|
||
|
|
status = 1,
|
||
|
|
description = '触发企业微信测试发送',
|
||
|
|
meta = '{}'::jsonb,
|
||
|
|
is_deleted = 0,
|
||
|
|
updated_at = now()
|
||
|
|
where perm_id = v_test_perm_id;
|
||
|
|
end if;
|
||
|
|
|
||
|
|
select u.user_id
|
||
|
|
into v_admin_user_id
|
||
|
|
from sys_user u
|
||
|
|
where u.username = 'admin'
|
||
|
|
and coalesce(u.is_deleted, 0) = 0
|
||
|
|
order by u.user_id
|
||
|
|
limit 1;
|
||
|
|
|
||
|
|
if v_admin_user_id is not null and not exists (
|
||
|
|
select 1
|
||
|
|
from sys_user_role ur
|
||
|
|
where ur.user_id = v_admin_user_id
|
||
|
|
and coalesce(ur.is_deleted, 0) = 0
|
||
|
|
) then
|
||
|
|
select r.role_id
|
||
|
|
into v_admin_role_id
|
||
|
|
from sys_role r
|
||
|
|
where coalesce(r.is_deleted, 0) = 0
|
||
|
|
and (
|
||
|
|
r.role_code in ('TENANT_ADMIN', 'ADMIN', 'SYS_ADMIN', 'PLATFORM_ADMIN', 'SUPER_ADMIN')
|
||
|
|
or r.role_name ilike '%管理员%'
|
||
|
|
or r.role_name ilike '%admin%'
|
||
|
|
)
|
||
|
|
order by
|
||
|
|
case
|
||
|
|
when r.role_code = 'TENANT_ADMIN' then 1
|
||
|
|
when r.role_code = 'ADMIN' then 2
|
||
|
|
when r.role_code = 'SYS_ADMIN' then 3
|
||
|
|
when r.role_code = 'PLATFORM_ADMIN' then 4
|
||
|
|
when r.role_code = 'SUPER_ADMIN' then 5
|
||
|
|
when r.role_name ilike '%管理员%' then 6
|
||
|
|
when r.role_name ilike '%admin%' then 7
|
||
|
|
else 99
|
||
|
|
end,
|
||
|
|
coalesce(r.tenant_id, 0),
|
||
|
|
r.role_id
|
||
|
|
limit 1;
|
||
|
|
|
||
|
|
if v_admin_role_id is not null then
|
||
|
|
insert into sys_user_role (user_id, role_id, is_deleted, created_at, updated_at)
|
||
|
|
select v_admin_user_id, v_admin_role_id, 0, now(), now()
|
||
|
|
where not exists (
|
||
|
|
select 1
|
||
|
|
from sys_user_role ur
|
||
|
|
where ur.user_id = v_admin_user_id
|
||
|
|
and ur.role_id = v_admin_role_id
|
||
|
|
);
|
||
|
|
|
||
|
|
update sys_user_role
|
||
|
|
set is_deleted = 0,
|
||
|
|
updated_at = now()
|
||
|
|
where user_id = v_admin_user_id
|
||
|
|
and role_id = v_admin_role_id;
|
||
|
|
end if;
|
||
|
|
end if;
|
||
|
|
|
||
|
|
insert into sys_role_permission (role_id, perm_id, is_deleted, created_at, updated_at)
|
||
|
|
select role_source.role_id, perm_source.perm_id, 0, now(), now()
|
||
|
|
from (
|
||
|
|
select distinct r.role_id
|
||
|
|
from sys_role r
|
||
|
|
where coalesce(r.is_deleted, 0) = 0
|
||
|
|
and (
|
||
|
|
r.role_code in ('TENANT_ADMIN', 'ADMIN', 'SYS_ADMIN', 'PLATFORM_ADMIN', 'SUPER_ADMIN')
|
||
|
|
or r.role_name ilike '%管理员%'
|
||
|
|
or r.role_name ilike '%admin%'
|
||
|
|
)
|
||
|
|
union
|
||
|
|
select distinct ur.role_id
|
||
|
|
from sys_user_role ur
|
||
|
|
where v_admin_user_id is not null
|
||
|
|
and ur.user_id = v_admin_user_id
|
||
|
|
and coalesce(ur.is_deleted, 0) = 0
|
||
|
|
) role_source
|
||
|
|
cross join (
|
||
|
|
select unnest(array[v_menu_perm_id, v_view_perm_id, v_update_perm_id, v_test_perm_id]) as perm_id
|
||
|
|
) perm_source
|
||
|
|
where perm_source.perm_id is not null
|
||
|
|
and not exists (
|
||
|
|
select 1
|
||
|
|
from sys_role_permission rp
|
||
|
|
where rp.role_id = role_source.role_id
|
||
|
|
and rp.perm_id = perm_source.perm_id
|
||
|
|
);
|
||
|
|
|
||
|
|
update sys_role_permission
|
||
|
|
set is_deleted = 0,
|
||
|
|
updated_at = now()
|
||
|
|
where perm_id in (v_menu_perm_id, v_view_perm_id, v_update_perm_id, v_test_perm_id)
|
||
|
|
and role_id in (
|
||
|
|
select distinct r.role_id
|
||
|
|
from sys_role r
|
||
|
|
where coalesce(r.is_deleted, 0) = 0
|
||
|
|
and (
|
||
|
|
r.role_code in ('TENANT_ADMIN', 'ADMIN', 'SYS_ADMIN', 'PLATFORM_ADMIN', 'SUPER_ADMIN')
|
||
|
|
or r.role_name ilike '%管理员%'
|
||
|
|
or r.role_name ilike '%admin%'
|
||
|
|
)
|
||
|
|
union
|
||
|
|
select distinct ur.role_id
|
||
|
|
from sys_user_role ur
|
||
|
|
where v_admin_user_id is not null
|
||
|
|
and ur.user_id = v_admin_user_id
|
||
|
|
and coalesce(ur.is_deleted, 0) = 0
|
||
|
|
);
|
||
|
|
end $$;
|
||
|
|
|
||
|
|
commit;
|