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.
242 lines
15 KiB
242 lines
15 KiB
-- 卷宗管理模块：表结构 + 菜单树（6 个子菜单）
|
|
-- PostgreSQL / Kingbase
|
|
|
|
CREATE TABLE IF NOT EXISTS t_dossier (
|
|
dossier_id BIGSERIAL PRIMARY KEY,
|
|
case_no VARCHAR(64) NOT NULL,
|
|
case_name VARCHAR(200) NOT NULL,
|
|
dossier_no VARCHAR(64) NOT NULL,
|
|
source_case_status VARCHAR(32) NOT NULL,
|
|
archive_status VARCHAR(32) NOT NULL DEFAULT 'not_archived',
|
|
dossier_status VARCHAR(32) NOT NULL DEFAULT 'temp_custody',
|
|
owner_officer VARCHAR(64),
|
|
owner_user_id BIGINT,
|
|
owner_dept VARCHAR(100),
|
|
scope_dept VARCHAR(100),
|
|
alarm_id BIGINT,
|
|
create_user_id BIGINT,
|
|
create_time TIMESTAMP DEFAULT NOW(),
|
|
update_time TIMESTAMP DEFAULT NOW(),
|
|
deleted_flag BOOLEAN DEFAULT FALSE
|
|
);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS uk_t_dossier_no ON t_dossier(dossier_no) WHERE deleted_flag = FALSE;
|
|
CREATE INDEX IF NOT EXISTS idx_t_dossier_case_no ON t_dossier(case_no);
|
|
|
|
CREATE TABLE IF NOT EXISTS t_dossier_volume (
|
|
volume_id BIGSERIAL PRIMARY KEY,
|
|
dossier_id BIGINT NOT NULL,
|
|
volume_name VARCHAR(200) NOT NULL,
|
|
volume_count INTEGER NOT NULL DEFAULT 1,
|
|
page_count INTEGER NOT NULL DEFAULT 0,
|
|
sort_no INTEGER DEFAULT 0,
|
|
create_time TIMESTAMP DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_t_dossier_volume_did ON t_dossier_volume(dossier_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS t_dossier_transfer (
|
|
transfer_id BIGSERIAL PRIMARY KEY,
|
|
dossier_id BIGINT NOT NULL,
|
|
file_key VARCHAR(255),
|
|
file_name VARCHAR(255),
|
|
operator_name VARCHAR(64),
|
|
operator_id BIGINT,
|
|
transfer_time TIMESTAMP,
|
|
create_time TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS t_dossier_borrow (
|
|
borrow_id BIGSERIAL PRIMARY KEY,
|
|
dossier_id BIGINT NOT NULL,
|
|
borrow_type VARCHAR(32) NOT NULL,
|
|
borrow_matter VARCHAR(32) NOT NULL,
|
|
borrow_time TIMESTAMP,
|
|
planned_return_time TIMESTAMP,
|
|
actual_return_time TIMESTAMP,
|
|
external_file_key VARCHAR(255),
|
|
external_file_name VARCHAR(255),
|
|
reason TEXT,
|
|
status VARCHAR(32) NOT NULL,
|
|
captain_audit_result VARCHAR(32),
|
|
captain_audit_opinion TEXT,
|
|
captain_auditor_name VARCHAR(64),
|
|
captain_audit_time TIMESTAMP,
|
|
confirmer_name VARCHAR(64),
|
|
confirm_time TIMESTAMP,
|
|
return_audit_result VARCHAR(32),
|
|
return_audit_opinion TEXT,
|
|
return_auditor_name VARCHAR(64),
|
|
return_audit_time TIMESTAMP,
|
|
applicant_name VARCHAR(64),
|
|
applicant_user_id BIGINT,
|
|
create_time TIMESTAMP DEFAULT NOW(),
|
|
update_time TIMESTAMP DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_t_dossier_borrow_did ON t_dossier_borrow(dossier_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS t_dossier_copy_request (
|
|
request_id BIGSERIAL PRIMARY KEY,
|
|
dossier_id BIGINT NOT NULL,
|
|
copy_reason TEXT NOT NULL,
|
|
copy_content TEXT NOT NULL,
|
|
status VARCHAR(32) NOT NULL,
|
|
audit_level INTEGER DEFAULT 1,
|
|
captain_result VARCHAR(32),
|
|
captain_opinion TEXT,
|
|
captain_auditor VARCHAR(64),
|
|
captain_audit_time TIMESTAMP,
|
|
supervision_result VARCHAR(32),
|
|
supervision_opinion TEXT,
|
|
supervision_auditor VARCHAR(64),
|
|
supervision_audit_time TIMESTAMP,
|
|
leader_result VARCHAR(32),
|
|
leader_opinion TEXT,
|
|
leader_auditor VARCHAR(64),
|
|
leader_audit_time TIMESTAMP,
|
|
applicant_name VARCHAR(64),
|
|
applicant_user_id BIGINT,
|
|
create_time TIMESTAMP DEFAULT NOW(),
|
|
update_time TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS t_dossier_operation_log (
|
|
log_id BIGSERIAL PRIMARY KEY,
|
|
dossier_id BIGINT NOT NULL,
|
|
operation_type VARCHAR(64) NOT NULL,
|
|
operation_title VARCHAR(100) NOT NULL,
|
|
operator_name VARCHAR(64),
|
|
operator_id BIGINT,
|
|
remark TEXT,
|
|
related_no VARCHAR(64),
|
|
create_time TIMESTAMP DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_t_dossier_op_log_did ON t_dossier_operation_log(dossier_id);
|
|
|
|
-- 卷宗管理改为一级目录 + 6 个子菜单
|
|
UPDATE t_menu
|
|
SET menu_type = 1, path = '/dossier', component = NULL, update_time = NOW()
|
|
WHERE menu_name = '卷宗管理' AND parent_id = 0 AND deleted_flag = 0 AND menu_type = 2;
|
|
|
|
INSERT INTO t_menu (menu_name, menu_type, parent_id, sort, path, component,
|
|
frame_flag, cache_flag, visible_flag, disabled_flag,
|
|
perms_type, icon, deleted_flag, create_user_id, create_time, update_time)
|
|
SELECT '登记归档', 2, m.menu_id, 1, '/dossier/register', '/business/dossier/dossier-register-list.vue',
|
|
false, false, true, false, 1, 'FolderAddOutlined', false, 1, NOW(), NOW()
|
|
FROM t_menu m WHERE m.menu_name = '卷宗管理' AND m.menu_type = 1 AND m.parent_id = 0 AND m.deleted_flag = 0
|
|
AND NOT EXISTS (SELECT 1 FROM t_menu x WHERE x.path = '/dossier/register' AND x.deleted_flag = 0);
|
|
|
|
INSERT INTO t_menu (menu_name, menu_type, parent_id, sort, path, component,
|
|
frame_flag, cache_flag, visible_flag, disabled_flag,
|
|
perms_type, icon, deleted_flag, create_user_id, create_time, update_time)
|
|
SELECT '借阅本人卷宗', 2, m.menu_id, 2, '/dossier/borrow-self', '/business/dossier/dossier-borrow-self-list.vue',
|
|
false, false, true, false, 1, 'UserOutlined', false, 1, NOW(), NOW()
|
|
FROM t_menu m WHERE m.menu_name = '卷宗管理' AND m.menu_type = 1 AND m.deleted_flag = 0
|
|
AND NOT EXISTS (SELECT 1 FROM t_menu x WHERE x.path = '/dossier/borrow-self' AND x.deleted_flag = 0);
|
|
|
|
INSERT INTO t_menu (menu_name, menu_type, parent_id, sort, path, component,
|
|
frame_flag, cache_flag, visible_flag, disabled_flag,
|
|
perms_type, icon, deleted_flag, create_user_id, create_time, update_time)
|
|
SELECT '借阅他人卷宗', 2, m.menu_id, 3, '/dossier/borrow-other', '/business/dossier/dossier-borrow-other-list.vue',
|
|
false, false, true, false, 1, 'TeamOutlined', false, 1, NOW(), NOW()
|
|
FROM t_menu m WHERE m.menu_name = '卷宗管理' AND m.menu_type = 1 AND m.deleted_flag = 0
|
|
AND NOT EXISTS (SELECT 1 FROM t_menu x WHERE x.path = '/dossier/borrow-other' AND x.deleted_flag = 0);
|
|
|
|
INSERT INTO t_menu (menu_name, menu_type, parent_id, sort, path, component,
|
|
frame_flag, cache_flag, visible_flag, disabled_flag,
|
|
perms_type, icon, deleted_flag, create_user_id, create_time, update_time)
|
|
SELECT '支队借阅', 2, m.menu_id, 4, '/dossier/borrow-detachment', '/business/dossier/dossier-borrow-detachment-list.vue',
|
|
false, false, true, false, 1, 'BankOutlined', false, 1, NOW(), NOW()
|
|
FROM t_menu m WHERE m.menu_name = '卷宗管理' AND m.menu_type = 1 AND m.deleted_flag = 0
|
|
AND NOT EXISTS (SELECT 1 FROM t_menu x WHERE x.path = '/dossier/borrow-detachment' AND x.deleted_flag = 0);
|
|
|
|
INSERT INTO t_menu (menu_name, menu_type, parent_id, sort, path, component,
|
|
frame_flag, cache_flag, visible_flag, disabled_flag,
|
|
perms_type, icon, deleted_flag, create_user_id, create_time, update_time)
|
|
SELECT '借阅归还', 2, m.menu_id, 5, '/dossier/borrow-return', '/business/dossier/dossier-borrow-return-list.vue',
|
|
false, false, true, false, 1, 'RollbackOutlined', false, 1, NOW(), NOW()
|
|
FROM t_menu m WHERE m.menu_name = '卷宗管理' AND m.menu_type = 1 AND m.deleted_flag = 0
|
|
AND NOT EXISTS (SELECT 1 FROM t_menu x WHERE x.path = '/dossier/borrow-return' AND x.deleted_flag = 0);
|
|
|
|
INSERT INTO t_menu (menu_name, menu_type, parent_id, sort, path, component,
|
|
frame_flag, cache_flag, visible_flag, disabled_flag,
|
|
perms_type, icon, deleted_flag, create_user_id, create_time, update_time)
|
|
SELECT '呈请复印', 2, m.menu_id, 6, '/dossier/copy-request', '/business/dossier/dossier-copy-request-list.vue',
|
|
false, false, true, false, 1, 'CopyOutlined', false, 1, NOW(), NOW()
|
|
FROM t_menu m WHERE m.menu_name = '卷宗管理' AND m.menu_type = 1 AND m.deleted_flag = 0
|
|
AND NOT EXISTS (SELECT 1 FROM t_menu x WHERE x.path = '/dossier/copy-request' AND x.deleted_flag = 0);
|
|
|
|
-- 功能点（登记归档为例，其余页面复用 dossier:* 权限族）
|
|
INSERT INTO t_menu (menu_name, menu_type, parent_id, frame_flag, cache_flag, visible_flag, disabled_flag,
|
|
perms_type, api_perms, web_perms, context_menu_id, deleted_flag, create_user_id, create_time, update_time)
|
|
SELECT '查询', 3, m.menu_id, false, false, true, false, 1, 'dossier:register:query', 'dossier:register:query', m.menu_id, false, 1, NOW(), NOW()
|
|
FROM t_menu m WHERE m.path = '/dossier/register' AND m.deleted_flag = 0
|
|
AND NOT EXISTS (SELECT 1 FROM t_menu x WHERE x.web_perms = 'dossier:register:query' AND x.deleted_flag = 0);
|
|
|
|
INSERT INTO t_menu (menu_name, menu_type, parent_id, frame_flag, cache_flag, visible_flag, disabled_flag,
|
|
perms_type, api_perms, web_perms, context_menu_id, deleted_flag, create_user_id, create_time, update_time)
|
|
SELECT '登记归档', 3, m.menu_id, false, false, true, false, 1, 'dossier:register:submit', 'dossier:register:submit', m.menu_id, false, 1, NOW(), NOW()
|
|
FROM t_menu m WHERE m.path = '/dossier/register' AND m.deleted_flag = 0
|
|
AND NOT EXISTS (SELECT 1 FROM t_menu x WHERE x.web_perms = 'dossier:register:submit' AND x.deleted_flag = 0);
|
|
|
|
INSERT INTO t_menu (menu_name, menu_type, parent_id, frame_flag, cache_flag, visible_flag, disabled_flag,
|
|
perms_type, api_perms, web_perms, context_menu_id, deleted_flag, create_user_id, create_time, update_time)
|
|
SELECT '移交市局', 3, m.menu_id, false, false, true, false, 1, 'dossier:transfer:submit', 'dossier:transfer:submit', m.menu_id, false, 1, NOW(), NOW()
|
|
FROM t_menu m WHERE m.path = '/dossier/register' AND m.deleted_flag = 0
|
|
AND NOT EXISTS (SELECT 1 FROM t_menu x WHERE x.web_perms = 'dossier:transfer:submit' AND x.deleted_flag = 0);
|
|
|
|
INSERT INTO t_menu (menu_name, menu_type, parent_id, frame_flag, cache_flag, visible_flag, disabled_flag,
|
|
perms_type, api_perms, web_perms, context_menu_id, deleted_flag, create_user_id, create_time, update_time)
|
|
SELECT '同步办案数据', 3, m.menu_id, false, false, true, false, 1, 'dossier:sync', 'dossier:sync', m.menu_id, false, 1, NOW(), NOW()
|
|
FROM t_menu m WHERE m.path = '/dossier/register' AND m.deleted_flag = 0
|
|
AND NOT EXISTS (SELECT 1 FROM t_menu x WHERE x.web_perms = 'dossier:sync' AND x.deleted_flag = 0);
|
|
|
|
-- 借阅/审核/复印通用权限
|
|
INSERT INTO t_menu (menu_name, menu_type, parent_id, frame_flag, cache_flag, visible_flag, disabled_flag,
|
|
perms_type, api_perms, web_perms, context_menu_id, deleted_flag, create_user_id, create_time, update_time)
|
|
SELECT '借阅申请', 3, m.menu_id, false, false, true, false, 1, 'dossier:borrow:apply', 'dossier:borrow:apply', m.menu_id, false, 1, NOW(), NOW()
|
|
FROM t_menu m WHERE m.path = '/dossier/borrow-self' AND m.deleted_flag = 0
|
|
AND NOT EXISTS (SELECT 1 FROM t_menu x WHERE x.web_perms = 'dossier:borrow:apply' AND x.deleted_flag = 0);
|
|
|
|
INSERT INTO t_menu (menu_name, menu_type, parent_id, frame_flag, cache_flag, visible_flag, disabled_flag,
|
|
perms_type, api_perms, web_perms, context_menu_id, deleted_flag, create_user_id, create_time, update_time)
|
|
SELECT '借阅审核', 3, m.menu_id, false, false, true, false, 1, 'dossier:borrow:audit', 'dossier:borrow:audit', m.menu_id, false, 1, NOW(), NOW()
|
|
FROM t_menu m WHERE m.path = '/dossier/borrow-other' AND m.deleted_flag = 0
|
|
AND NOT EXISTS (SELECT 1 FROM t_menu x WHERE x.web_perms = 'dossier:borrow:audit' AND x.deleted_flag = 0);
|
|
|
|
INSERT INTO t_menu (menu_name, menu_type, parent_id, frame_flag, cache_flag, visible_flag, disabled_flag,
|
|
perms_type, api_perms, web_perms, context_menu_id, deleted_flag, create_user_id, create_time, update_time)
|
|
SELECT '管理员确认', 3, m.menu_id, false, false, true, false, 1, 'dossier:borrow:confirm', 'dossier:borrow:confirm', m.menu_id, false, 1, NOW(), NOW()
|
|
FROM t_menu m WHERE m.path = '/dossier/borrow-self' AND m.deleted_flag = 0
|
|
AND NOT EXISTS (SELECT 1 FROM t_menu x WHERE x.web_perms = 'dossier:borrow:confirm' AND x.deleted_flag = 0);
|
|
|
|
INSERT INTO t_menu (menu_name, menu_type, parent_id, frame_flag, cache_flag, visible_flag, disabled_flag,
|
|
perms_type, api_perms, web_perms, context_menu_id, deleted_flag, create_user_id, create_time, update_time)
|
|
SELECT '归还审核', 3, m.menu_id, false, false, true, false, 1, 'dossier:return:audit', 'dossier:return:audit', m.menu_id, false, 1, NOW(), NOW()
|
|
FROM t_menu m WHERE m.path = '/dossier/borrow-return' AND m.deleted_flag = 0
|
|
AND NOT EXISTS (SELECT 1 FROM t_menu x WHERE x.web_perms = 'dossier:return:audit' AND x.deleted_flag = 0);
|
|
|
|
INSERT INTO t_menu (menu_name, menu_type, parent_id, frame_flag, cache_flag, visible_flag, disabled_flag,
|
|
perms_type, api_perms, web_perms, context_menu_id, deleted_flag, create_user_id, create_time, update_time)
|
|
SELECT '呈请复印', 3, m.menu_id, false, false, true, false, 1, 'dossier:copy:apply', 'dossier:copy:apply', m.menu_id, false, 1, NOW(), NOW()
|
|
FROM t_menu m WHERE m.path = '/dossier/copy-request' AND m.deleted_flag = 0
|
|
AND NOT EXISTS (SELECT 1 FROM t_menu x WHERE x.web_perms = 'dossier:copy:apply' AND x.deleted_flag = 0);
|
|
|
|
INSERT INTO t_menu (menu_name, menu_type, parent_id, frame_flag, cache_flag, visible_flag, disabled_flag,
|
|
perms_type, api_perms, web_perms, context_menu_id, deleted_flag, create_user_id, create_time, update_time)
|
|
SELECT '复印审核', 3, m.menu_id, false, false, true, false, 1, 'dossier:copy:audit', 'dossier:copy:audit', m.menu_id, false, 1, NOW(), NOW()
|
|
FROM t_menu m WHERE m.path = '/dossier/copy-request' AND m.deleted_flag = 0
|
|
AND NOT EXISTS (SELECT 1 FROM t_menu x WHERE x.web_perms = 'dossier:copy:audit' AND x.deleted_flag = 0);
|
|
|
|
INSERT INTO t_menu (menu_name, menu_type, parent_id, frame_flag, cache_flag, visible_flag, disabled_flag,
|
|
perms_type, api_perms, web_perms, context_menu_id, deleted_flag, create_user_id, create_time, update_time)
|
|
SELECT '卷宗详情', 3, m.menu_id, false, false, true, false, 1, 'dossier:detail', 'dossier:detail', m.menu_id, false, 1, NOW(), NOW()
|
|
FROM t_menu m WHERE m.path = '/dossier/register' AND m.deleted_flag = 0
|
|
AND NOT EXISTS (SELECT 1 FROM t_menu x WHERE x.web_perms = 'dossier:detail' AND x.deleted_flag = 0);
|
|
|
|
-- 绑定经侦管理员
|
|
INSERT INTO t_role_menu (role_id, menu_id)
|
|
SELECT r.role_id, m.menu_id
|
|
FROM t_role r, t_menu m
|
|
WHERE r.role_code = 'JZ_ADMIN'
|
|
AND (m.path LIKE '/dossier/%' OR (m.menu_name = '卷宗管理' AND m.menu_type = 1))
|
|
AND m.deleted_flag = 0
|
|
AND NOT EXISTS (SELECT 1 FROM t_role_menu rm WHERE rm.role_id = r.role_id AND rm.menu_id = m.menu_id);
|
|
|