You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
306 lines
15 KiB
306 lines
15 KiB
4 days ago
|
-- 业务演示角色 + 六位警号账号（密码 123456，Argon2id 与系统一致）
|
||
|
|
-- 市局：案管员 / 财物保管员 / 接报案接待员
|
||
|
|
-- 义安分局：大队长 / 分局领导 / 办案民警 / 大队案管员 / 大队财物保管员 / 大队接报案接待员
|
||
|
|
|
||
|
|
-- 已知 123456 哈希（与 t_employee.admin 相同算法）
|
||
|
|
-- $argon2id$v=19$m=16384,t=2,p=1$e/hqRAZYCYHydMS3SPo7yA$5hdCxLG7q+Jtf6KLJHVg/yb0I8LZrPuKUF66jLq+Drc
|
||
|
|
|
||
|
|
INSERT INTO t_role (role_name, role_code, remark, create_time, update_time)
|
||
|
|
SELECT v.role_name, v.role_code, v.remark, NOW(), NOW()
|
||
|
|
FROM (VALUES
|
||
|
|
('市局案管员', 'city_case_manager', '市局案件监督、卷宗登记归档、借阅确认'),
|
||
|
|
('涉案财物保管员', 'city_property_keeper', '市局涉案财物入库保管、归还/出库确认'),
|
||
|
|
('接报案中心接待员', 'city_alarm_receptionist', '市局接报案中心登记、材料、发起作废'),
|
||
|
|
('义安区分局经侦大队大队长', 'yian_brigade_captain', '区县作废审核、财物调用审核、卷宗借阅/复印审核'),
|
||
|
|
('分局领导', 'bureau_leader', '分局业务总览、审议审批、领办包保、复印领导审'),
|
||
|
|
('大队办案民警', 'brigade_officer', '本大队办案：案件查询、财物录入/调用、本人卷宗借阅'),
|
||
|
|
('大队案管员', 'brigade_case_manager', '大队案件监督、卷宗登记、移交市局'),
|
||
|
|
('大队涉案财物保管员', 'brigade_property_keeper', '大队涉案财物入库保管、归还/出库确认'),
|
||
|
|
('大队接报案中心接待员', 'brigade_alarm_receptionist', '区县接报案分中心登记、材料、发起作废')
|
||
|
|
) AS v(role_name, role_code, remark)
|
||
|
|
WHERE NOT EXISTS (SELECT 1 FROM t_role x WHERE x.role_code = v.role_code);
|
||
|
|
|
||
|
|
-- 授权规则：dir=一级目录名；path=页面；children=页面及其功能点
|
||
|
|
CREATE TEMP TABLE IF NOT EXISTS tmp_role_menu_rule (
|
||
|
|
role_code VARCHAR(64) NOT NULL,
|
||
|
|
kind VARCHAR(16) NOT NULL,
|
||
|
|
val VARCHAR(256) NOT NULL
|
||
|
|
);
|
||
|
|
TRUNCATE tmp_role_menu_rule;
|
||
|
|
|
||
|
|
INSERT INTO tmp_role_menu_rule (role_code, kind, val) VALUES
|
||
|
|
-- 市局案管员
|
||
|
|
('city_case_manager', 'dir', '案件管理'),
|
||
|
|
('city_case_manager', 'dir', '案件监督'),
|
||
|
|
('city_case_manager', 'dir', '风险预警'),
|
||
|
|
('city_case_manager', 'dir', '卷宗管理'),
|
||
|
|
('city_case_manager', 'dir', '数据大屏'),
|
||
|
|
('city_case_manager', 'path_prefix', '/case-management'),
|
||
|
|
('city_case_manager', 'path_prefix', '/dossier'),
|
||
|
|
('city_case_manager', 'path', '/bigscreen/data-bigscreen'),
|
||
|
|
('city_case_manager', 'web_prefix', 'negative-list:'),
|
||
|
|
('city_case_manager', 'web_prefix', 'weekly-schedule:'),
|
||
|
|
('city_case_manager', 'web_prefix', 'risk-warning:'),
|
||
|
|
('city_case_manager', 'web_prefix', 'dossier:'),
|
||
|
|
-- 市局财物保管员
|
||
|
|
('city_property_keeper', 'dir', '涉案财物'),
|
||
|
|
('city_property_keeper', 'path_prefix', '/property'),
|
||
|
|
('city_property_keeper', 'web_prefix', 'property:management:'),
|
||
|
|
('city_property_keeper', 'web_prefix', 'property:unregistered:'),
|
||
|
|
('city_property_keeper', 'web_prefix', 'property:omission:'),
|
||
|
|
('city_property_keeper', 'web_prefix', 'property:return:'),
|
||
|
|
('city_property_keeper', 'web_prefix', 'property:checkout:'),
|
||
|
|
('city_property_keeper', 'web_prefix', 'property:transfer:query'),
|
||
|
|
('city_property_keeper', 'web_prefix', 'property:transfer:detail'),
|
||
|
|
-- 市局接报案接待员
|
||
|
|
('city_alarm_receptionist', 'dir', '接报案中心'),
|
||
|
|
('city_alarm_receptionist', 'path', '/business/alarm/alarm-reception-list'),
|
||
|
|
('city_alarm_receptionist', 'children', '/business/alarm/alarm-reception-list'),
|
||
|
|
('city_alarm_receptionist', 'path', '/business/alarm/material-view'),
|
||
|
|
('city_alarm_receptionist', 'children', '/business/alarm/material-view'),
|
||
|
|
('city_alarm_receptionist', 'path', '/business/alarm/center-monitor'),
|
||
|
|
('city_alarm_receptionist', 'children', '/business/alarm/center-monitor'),
|
||
|
|
('city_alarm_receptionist', 'path', '/business/alarm/no-accept-review-list'),
|
||
|
|
('city_alarm_receptionist', 'children', '/business/alarm/no-accept-review-list'),
|
||
|
|
('city_alarm_receptionist', 'web_prefix', 'alarm:void:apply'),
|
||
|
|
-- 义安大队长
|
||
|
|
('yian_brigade_captain', 'dir', '接报案中心'),
|
||
|
|
('yian_brigade_captain', 'dir', '案件管理'),
|
||
|
|
('yian_brigade_captain', 'dir', '案件监督'),
|
||
|
|
('yian_brigade_captain', 'dir', '风险预警'),
|
||
|
|
('yian_brigade_captain', 'dir', '涉案财物'),
|
||
|
|
('yian_brigade_captain', 'dir', '卷宗管理'),
|
||
|
|
('yian_brigade_captain', 'dir', '审议流程'),
|
||
|
|
('yian_brigade_captain', 'dir', '数据大屏'),
|
||
|
|
('yian_brigade_captain', 'path', '/business/alarm/alarm-reception-list'),
|
||
|
|
('yian_brigade_captain', 'children', '/business/alarm/alarm-reception-list'),
|
||
|
|
('yian_brigade_captain', 'path', '/business/alarm/void-audit-list'),
|
||
|
|
('yian_brigade_captain', 'children', '/business/alarm/void-audit-list'),
|
||
|
|
('yian_brigade_captain', 'path_prefix', '/case-management'),
|
||
|
|
('yian_brigade_captain', 'path_prefix', '/dossier'),
|
||
|
|
('yian_brigade_captain', 'path', '/property/transfer'),
|
||
|
|
('yian_brigade_captain', 'children', '/property/transfer'),
|
||
|
|
('yian_brigade_captain', 'path', '/property/management'),
|
||
|
|
('yian_brigade_captain', 'children', '/property/management'),
|
||
|
|
('yian_brigade_captain', 'path', '/bigscreen/data-bigscreen'),
|
||
|
|
('yian_brigade_captain', 'web_prefix', 'alarm:void:'),
|
||
|
|
('yian_brigade_captain', 'web_prefix', 'property:transfer:'),
|
||
|
|
('yian_brigade_captain', 'web_prefix', 'dossier:borrow:audit'),
|
||
|
|
('yian_brigade_captain', 'web_prefix', 'dossier:copy:audit'),
|
||
|
|
('yian_brigade_captain', 'web_prefix', 'weekly-schedule:'),
|
||
|
|
-- 分局领导
|
||
|
|
('bureau_leader', 'dir', '接报案中心'),
|
||
|
|
('bureau_leader', 'dir', '案件管理'),
|
||
|
|
('bureau_leader', 'dir', '案件监督'),
|
||
|
|
('bureau_leader', 'dir', '风险预警'),
|
||
|
|
('bureau_leader', 'dir', '涉案资金'),
|
||
|
|
('bureau_leader', 'dir', '涉案财物'),
|
||
|
|
('bureau_leader', 'dir', '卷宗管理'),
|
||
|
|
('bureau_leader', 'dir', '审议流程'),
|
||
|
|
('bureau_leader', 'dir', '数据大屏'),
|
||
|
|
('bureau_leader', 'path', '/business/alarm/alarm-reception-list'),
|
||
|
|
('bureau_leader', 'children', '/business/alarm/alarm-reception-list'),
|
||
|
|
('bureau_leader', 'path_prefix', '/case-management'),
|
||
|
|
('bureau_leader', 'path_prefix', '/fund'),
|
||
|
|
('bureau_leader', 'path', '/property/management'),
|
||
|
|
('bureau_leader', 'children', '/property/management'),
|
||
|
|
('bureau_leader', 'path_prefix', '/dossier'),
|
||
|
|
('bureau_leader', 'path', '/bigscreen/data-bigscreen'),
|
||
|
|
('bureau_leader', 'web_prefix', 'review:general:'),
|
||
|
|
('bureau_leader', 'web_prefix', 'leader-record:'),
|
||
|
|
('bureau_leader', 'web_prefix', 'dossier:copy:audit'),
|
||
|
|
('bureau_leader', 'web_prefix', 'risk-warning:query'),
|
||
|
|
('bureau_leader', 'web_prefix', 'risk-warning:view'),
|
||
|
|
-- 大队办案民警
|
||
|
|
('brigade_officer', 'dir', '案件管理'),
|
||
|
|
('brigade_officer', 'dir', '案件监督'),
|
||
|
|
('brigade_officer', 'dir', '风险预警'),
|
||
|
|
('brigade_officer', 'dir', '涉案财物'),
|
||
|
|
('brigade_officer', 'dir', '卷宗管理'),
|
||
|
|
('brigade_officer', 'path', '/case-management/supervision/weekly-schedule'),
|
||
|
|
('brigade_officer', 'children', '/case-management/supervision/weekly-schedule'),
|
||
|
|
('brigade_officer', 'path', '/case-management/risk-warning'),
|
||
|
|
('brigade_officer', 'path_prefix', '/case-management/risk-warning'),
|
||
|
|
('brigade_officer', 'path', '/property/management'),
|
||
|
|
('brigade_officer', 'children', '/property/management'),
|
||
|
|
('brigade_officer', 'path', '/property/transfer'),
|
||
|
|
('brigade_officer', 'children', '/property/transfer'),
|
||
|
|
('brigade_officer', 'path', '/dossier/register'),
|
||
|
|
('brigade_officer', 'children', '/dossier/register'),
|
||
|
|
('brigade_officer', 'path', '/dossier/borrow-self'),
|
||
|
|
('brigade_officer', 'children', '/dossier/borrow-self'),
|
||
|
|
('brigade_officer', 'path', '/dossier/copy-request'),
|
||
|
|
('brigade_officer', 'children', '/dossier/copy-request'),
|
||
|
|
('brigade_officer', 'web_prefix', 'weekly-schedule:confirm'),
|
||
|
|
('brigade_officer', 'web_prefix', 'weekly-schedule:query'),
|
||
|
|
('brigade_officer', 'web_prefix', 'weekly-schedule:view'),
|
||
|
|
('brigade_officer', 'web_prefix', 'risk-warning:query'),
|
||
|
|
('brigade_officer', 'web_prefix', 'risk-warning:view'),
|
||
|
|
('brigade_officer', 'web_prefix', 'property:management:query'),
|
||
|
|
('brigade_officer', 'web_prefix', 'property:management:add'),
|
||
|
|
('brigade_officer', 'web_prefix', 'property:management:detail'),
|
||
|
|
('brigade_officer', 'web_prefix', 'property:management:invoke'),
|
||
|
|
('brigade_officer', 'web_prefix', 'dossier:register:query'),
|
||
|
|
('brigade_officer', 'web_prefix', 'dossier:detail'),
|
||
|
|
('brigade_officer', 'web_prefix', 'dossier:borrow:apply'),
|
||
|
|
('brigade_officer', 'web_prefix', 'dossier:copy:apply'),
|
||
|
|
-- 大队案管员
|
||
|
|
('brigade_case_manager', 'dir', '案件管理'),
|
||
|
|
('brigade_case_manager', 'dir', '案件监督'),
|
||
|
|
('brigade_case_manager', 'dir', '风险预警'),
|
||
|
|
('brigade_case_manager', 'dir', '卷宗管理'),
|
||
|
|
('brigade_case_manager', 'path_prefix', '/case-management/supervision'),
|
||
|
|
('brigade_case_manager', 'path_prefix', '/case-management/risk-warning'),
|
||
|
|
('brigade_case_manager', 'path_prefix', '/dossier'),
|
||
|
|
('brigade_case_manager', 'web_prefix', 'negative-list:'),
|
||
|
|
('brigade_case_manager', 'web_prefix', 'weekly-schedule:'),
|
||
|
|
('brigade_case_manager', 'web_prefix', 'risk-warning:'),
|
||
|
|
('brigade_case_manager', 'web_prefix', 'dossier:'),
|
||
|
|
-- 大队财物保管员
|
||
|
|
('brigade_property_keeper', 'dir', '涉案财物'),
|
||
|
|
('brigade_property_keeper', 'path_prefix', '/property'),
|
||
|
|
('brigade_property_keeper', 'web_prefix', 'property:management:'),
|
||
|
|
('brigade_property_keeper', 'web_prefix', 'property:unregistered:'),
|
||
|
|
('brigade_property_keeper', 'web_prefix', 'property:omission:'),
|
||
|
|
('brigade_property_keeper', 'web_prefix', 'property:return:'),
|
||
|
|
('brigade_property_keeper', 'web_prefix', 'property:checkout:'),
|
||
|
|
('brigade_property_keeper', 'web_prefix', 'property:transfer:query'),
|
||
|
|
('brigade_property_keeper', 'web_prefix', 'property:transfer:detail'),
|
||
|
|
-- 大队接报案接待员
|
||
|
|
('brigade_alarm_receptionist', 'dir', '接报案中心'),
|
||
|
|
('brigade_alarm_receptionist', 'path', '/business/alarm/alarm-reception-list'),
|
||
|
|
('brigade_alarm_receptionist', 'children', '/business/alarm/alarm-reception-list'),
|
||
|
|
('brigade_alarm_receptionist', 'path', '/business/alarm/material-view'),
|
||
|
|
('brigade_alarm_receptionist', 'children', '/business/alarm/material-view'),
|
||
|
|
('brigade_alarm_receptionist', 'path', '/business/alarm/center-monitor'),
|
||
|
|
('brigade_alarm_receptionist', 'children', '/business/alarm/center-monitor'),
|
||
|
|
('brigade_alarm_receptionist', 'path', '/business/alarm/no-accept-review-list'),
|
||
|
|
('brigade_alarm_receptionist', 'children', '/business/alarm/no-accept-review-list'),
|
||
|
|
('brigade_alarm_receptionist', 'web_prefix', 'alarm:void:apply');
|
||
|
|
|
||
|
|
INSERT INTO t_role_menu (role_id, menu_id, create_time, update_time)
|
||
|
|
SELECT DISTINCT r.role_id, m.menu_id, NOW(), NOW()
|
||
|
|
FROM tmp_role_menu_rule g
|
||
|
|
JOIN t_role r ON r.role_code = g.role_code
|
||
|
|
JOIN t_menu m ON m.deleted_flag = 0
|
||
|
|
WHERE (
|
||
|
|
(g.kind = 'dir' AND m.menu_type = 1 AND m.menu_name = g.val)
|
||
|
|
OR (g.kind = 'path' AND m.path = g.val)
|
||
|
|
OR (g.kind = 'path_prefix' AND (m.path = g.val OR m.path LIKE CONCAT(g.val, '/%')))
|
||
|
|
OR (g.kind = 'web_prefix' AND m.web_perms LIKE CONCAT(g.val, '%'))
|
||
|
|
OR (g.kind = 'children' AND (
|
||
|
|
m.path = g.val
|
||
|
|
OR m.parent_id IN (SELECT menu_id FROM t_menu p WHERE p.path = g.val AND p.deleted_flag = 0)
|
||
|
|
))
|
||
|
|
)
|
||
|
|
AND NOT EXISTS (
|
||
|
|
SELECT 1 FROM t_role_menu x WHERE x.role_id = r.role_id AND x.menu_id = m.menu_id
|
||
|
|
);
|
||
|
|
|
||
|
|
-- 办案民警去掉财物调用审核、卷宗借阅他人审核等越权点
|
||
|
|
DELETE FROM t_role_menu rm
|
||
|
|
USING t_role r, t_menu m
|
||
|
|
WHERE rm.role_id = r.role_id
|
||
|
|
AND rm.menu_id = m.menu_id
|
||
|
|
AND r.role_code = 'brigade_officer'
|
||
|
|
AND m.web_perms IN (
|
||
|
|
'property:transfer:audit',
|
||
|
|
'dossier:borrow:audit',
|
||
|
|
'dossier:borrow:confirm',
|
||
|
|
'dossier:return:audit',
|
||
|
|
'dossier:copy:audit',
|
||
|
|
'dossier:transfer:submit',
|
||
|
|
'dossier:register:submit',
|
||
|
|
'dossier:sync',
|
||
|
|
'weekly-schedule:add',
|
||
|
|
'weekly-schedule:push',
|
||
|
|
'weekly-schedule:print',
|
||
|
|
'property:management:checkout'
|
||
|
|
);
|
||
|
|
|
||
|
|
-- 分局领导不作出库/入库操作与作废审批
|
||
|
|
DELETE FROM t_role_menu rm
|
||
|
|
USING t_role r, t_menu m
|
||
|
|
WHERE rm.role_id = r.role_id
|
||
|
|
AND rm.menu_id = m.menu_id
|
||
|
|
AND r.role_code = 'bureau_leader'
|
||
|
|
AND (
|
||
|
|
m.web_perms IN (
|
||
|
|
'property:management:add',
|
||
|
|
'property:management:checkout',
|
||
|
|
'property:management:invoke',
|
||
|
|
'property:transfer:audit',
|
||
|
|
'alarm:void:apply',
|
||
|
|
'alarm:void:audit',
|
||
|
|
'alarm:add',
|
||
|
|
'alarm:update'
|
||
|
|
)
|
||
|
|
OR m.path IN ('/business/alarm/void-audit-list', '/property/unregistered', '/property/omission')
|
||
|
|
OR m.web_perms IN ('weekly-schedule:add', 'weekly-schedule:push')
|
||
|
|
);
|
||
|
|
|
||
|
|
-- 财物保管员不审调用
|
||
|
|
DELETE FROM t_role_menu rm
|
||
|
|
USING t_role r, t_menu m
|
||
|
|
WHERE rm.role_id = r.role_id
|
||
|
|
AND rm.menu_id = m.menu_id
|
||
|
|
AND r.role_code IN ('city_property_keeper', 'brigade_property_keeper')
|
||
|
|
AND m.web_perms = 'property:transfer:audit';
|
||
|
|
|
||
|
|
INSERT INTO t_employee (
|
||
|
|
login_name, login_pwd, actual_name, gender, phone, department_id, email,
|
||
|
|
disabled_flag, deleted_flag, administrator_flag, remark, create_time, update_time
|
||
|
|
)
|
||
|
|
SELECT v.login_name,
|
||
|
|
'$argon2id$v=19$m=16384,t=2,p=1$e/hqRAZYCYHydMS3SPo7yA$5hdCxLG7q+Jtf6KLJHVg/yb0I8LZrPuKUF66jLq+Drc',
|
||
|
|
v.actual_name, v.gender, v.phone,
|
||
|
|
COALESCE((SELECT department_id FROM t_department d WHERE d.department_id = v.dept_id), 1),
|
||
|
|
v.login_name || '@tl-police.cn',
|
||
|
|
0, 0, 0, v.remark, NOW(), NOW()
|
||
|
|
FROM (VALUES
|
||
|
|
('340701', '陈立', 1, '13834070101', 118, '市局案管员'),
|
||
|
|
('340702', '刘仓', 1, '13834070201', 118, '涉案财物保管员'),
|
||
|
|
('340703', '王敏', 0, '13834070301', 123, '接报案中心接待员'),
|
||
|
|
('340711', '赵建军', 1, '13834071101', 3017, '义安区分局经侦大队大队长'),
|
||
|
|
('340712', '黄志强', 1, '13834071201', 301, '分局领导'),
|
||
|
|
('340713', '张伟', 1, '13834071301', 3017, '大队办案民警'),
|
||
|
|
('340714', '李芳', 0, '13834071401', 3017, '大队案管员'),
|
||
|
|
('340715', '周海', 1, '13834071501', 3017, '大队涉案财物保管员'),
|
||
|
|
('340716', '吴娟', 0, '13834071601', 3017, '大队接报案中心接待员')
|
||
|
|
) AS v(login_name, actual_name, gender, phone, dept_id, remark)
|
||
|
|
WHERE NOT EXISTS (
|
||
|
|
SELECT 1 FROM t_employee e WHERE e.login_name = v.login_name AND e.deleted_flag IN (0, FALSE)
|
||
|
|
);
|
||
|
|
|
||
|
|
INSERT INTO t_role_employee (role_id, employee_id, create_time, update_time)
|
||
|
|
SELECT r.role_id, e.employee_id, NOW(), NOW()
|
||
|
|
FROM (VALUES
|
||
|
|
('340701', 'city_case_manager'),
|
||
|
|
('340702', 'city_property_keeper'),
|
||
|
|
('340703', 'city_alarm_receptionist'),
|
||
|
|
('340703', 'alarm_city_receiver'),
|
||
|
|
('340711', 'yian_brigade_captain'),
|
||
|
|
('340711', 'alarm_district_captain'),
|
||
|
|
('340712', 'bureau_leader'),
|
||
|
|
('340713', 'brigade_officer'),
|
||
|
|
('340714', 'brigade_case_manager'),
|
||
|
|
('340715', 'brigade_property_keeper'),
|
||
|
|
('340716', 'brigade_alarm_receptionist'),
|
||
|
|
('340716', 'alarm_district_receiver')
|
||
|
|
) AS v(login_name, role_code)
|
||
|
|
JOIN t_employee e ON e.login_name = v.login_name AND e.deleted_flag IN (0, FALSE)
|
||
|
|
JOIN t_role r ON r.role_code = v.role_code
|
||
|
|
WHERE NOT EXISTS (
|
||
|
|
SELECT 1 FROM t_role_employee x WHERE x.role_id = r.role_id AND x.employee_id = e.employee_id
|
||
|
|
);
|
||
|
|
|
||
|
|
DO $$
|
||
|
|
BEGIN
|
||
|
|
IF EXISTS (SELECT 1 FROM pg_class WHERE relkind = 'S' AND relname = 't_employee_employee_id_seq') THEN
|
||
|
|
PERFORM setval('t_employee_employee_id_seq', (SELECT COALESCE(MAX(employee_id), 1) FROM t_employee));
|
||
|
|
END IF;
|
||
|
|
IF EXISTS (SELECT 1 FROM pg_class WHERE relkind = 'S' AND relname = 't_role_role_id_seq') THEN
|
||
|
|
PERFORM setval('t_role_role_id_seq', (SELECT COALESCE(MAX(role_id), 1) FROM t_role));
|
||
|
|
END IF;
|
||
|
|
END $$;
|