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.
136 lines
8.2 KiB
136 lines
8.2 KiB
1 month ago
|
-- 借阅本人卷宗：已登记归档（入库）测试数据
|
||
|
|
-- 绑定现有账号 admin / huke / zhuoda，覆盖可借阅、待确认、借阅中、借阅记录四个 Tab
|
||
|
|
|
||
|
|
-- ========== 1. 已登记归档 · 可借阅 ==========
|
||
|
|
INSERT INTO t_dossier (
|
||
|
|
case_no, case_name, dossier_no, source_case_status, archive_status, dossier_status,
|
||
|
|
owner_officer, owner_user_id, owner_dept, scope_dept, create_user_id, create_time, update_time, deleted_flag
|
||
|
|
)
|
||
|
|
SELECT v.case_no, v.case_name, v.dossier_no, v.source_case_status, 'archived', 'registered_archived',
|
||
|
|
e.actual_name, e.employee_id, '经济犯罪侦查支队', '经济犯罪侦查支队', e.employee_id, NOW(), NOW(), FALSE
|
||
|
|
FROM (VALUES
|
||
|
|
('admin', 'AJ20260907001', '铜陵市某商贸公司涉嫌合同诈骗案', 'JZ20260907001', 'transfer_prosecution'),
|
||
|
|
('admin', 'AJ20260907002', '张某涉嫌非法吸收公众存款案', 'JZ20260907002', 'no_filing'),
|
||
|
|
('admin', 'AJ20260907003', '李某涉嫌职务侵占案', 'JZ20260907003', 'withdrawn'),
|
||
|
|
('huke', 'AJ20260907011', '王某涉嫌虚开增值税专用发票案', 'JZ20260907011', 'transfer_prosecution'),
|
||
|
|
('huke', 'AJ20260907012', '孙某涉嫌洗钱案', 'JZ20260907012', 'no_filing'),
|
||
|
|
('zhuoda','AJ20260907021', '赵某涉嫌集资诈骗案', 'JZ20260907021', 'withdrawn')
|
||
|
|
) AS v(login_name, case_no, case_name, dossier_no, source_case_status)
|
||
|
|
JOIN t_employee e ON e.login_name = v.login_name AND e.deleted_flag = 0
|
||
|
|
WHERE NOT EXISTS (
|
||
|
|
SELECT 1 FROM t_dossier d WHERE d.dossier_no = v.dossier_no AND d.deleted_flag = FALSE
|
||
|
|
);
|
||
|
|
|
||
|
|
-- ========== 2. 待管理员确认 / 借阅中 / 已归还·已驳回（借阅记录） ==========
|
||
|
|
INSERT INTO t_dossier (
|
||
|
|
case_no, case_name, dossier_no, source_case_status, archive_status, dossier_status,
|
||
|
|
owner_officer, owner_user_id, owner_dept, scope_dept, create_user_id, create_time, update_time, deleted_flag
|
||
|
|
)
|
||
|
|
SELECT v.case_no, v.case_name, v.dossier_no, v.source_case_status, 'archived', v.dossier_status,
|
||
|
|
e.actual_name, e.employee_id, '经济犯罪侦查支队', '经济犯罪侦查支队', e.employee_id, NOW(), NOW(), FALSE
|
||
|
|
FROM (VALUES
|
||
|
|
('admin', 'AJ20260907004', '陈某涉嫌挪用资金案', 'JZ20260907004', 'no_filing', 'pending_confirm'),
|
||
|
|
('admin', 'AJ20260907005', '刘某涉嫌非法经营案', 'JZ20260907005', 'transfer_prosecution','borrowing'),
|
||
|
|
('admin', 'AJ20260907006', '周某涉嫌合同诈骗案', 'JZ20260907006', 'withdrawn', 'registered_archived'),
|
||
|
|
('admin', 'AJ20260907007', '吴某涉嫌骗取贷款案', 'JZ20260907007', 'no_filing', 'registered_archived')
|
||
|
|
) AS v(login_name, case_no, case_name, dossier_no, source_case_status, dossier_status)
|
||
|
|
JOIN t_employee e ON e.login_name = v.login_name AND e.deleted_flag = 0
|
||
|
|
WHERE NOT EXISTS (
|
||
|
|
SELECT 1 FROM t_dossier d WHERE d.dossier_no = v.dossier_no AND d.deleted_flag = FALSE
|
||
|
|
);
|
||
|
|
|
||
|
|
-- ========== 3. 卷宗分册 ==========
|
||
|
|
INSERT INTO t_dossier_volume (dossier_id, volume_name, volume_count, page_count, sort_no, create_time)
|
||
|
|
SELECT d.dossier_id, v.volume_name, v.volume_count, v.page_count, v.sort_no, NOW()
|
||
|
|
FROM t_dossier d
|
||
|
|
JOIN (VALUES
|
||
|
|
('JZ20260907001', '诉讼文书卷', 1, 86, 1),
|
||
|
|
('JZ20260907001', '证据材料卷', 2, 214, 2),
|
||
|
|
('JZ20260907001', '侦查工作卷', 1, 132, 3),
|
||
|
|
('JZ20260907002', '诉讼文书卷', 1, 64, 1),
|
||
|
|
('JZ20260907002', '证据材料卷', 1, 158, 2),
|
||
|
|
('JZ20260907003', '诉讼文书卷', 1, 72, 1),
|
||
|
|
('JZ20260907003', '侦查工作卷', 1, 96, 2),
|
||
|
|
('JZ20260907004', '诉讼文书卷', 1, 58, 1),
|
||
|
|
('JZ20260907004', '证据材料卷', 1, 120, 2),
|
||
|
|
('JZ20260907005', '诉讼文书卷', 1, 91, 1),
|
||
|
|
('JZ20260907005', '证据材料卷', 2, 186, 2),
|
||
|
|
('JZ20260907005', '侦查工作卷', 1, 104, 3),
|
||
|
|
('JZ20260907006', '诉讼文书卷', 1, 67, 1),
|
||
|
|
('JZ20260907006', '证据材料卷', 1, 143, 2),
|
||
|
|
('JZ20260907007', '诉讼文书卷', 1, 55, 1),
|
||
|
|
('JZ20260907011', '诉讼文书卷', 1, 78, 1),
|
||
|
|
('JZ20260907011', '证据材料卷', 1, 165, 2),
|
||
|
|
('JZ20260907012', '诉讼文书卷', 1, 62, 1),
|
||
|
|
('JZ20260907012', '侦查工作卷', 1, 88, 2),
|
||
|
|
('JZ20260907021', '诉讼文书卷', 1, 74, 1),
|
||
|
|
('JZ20260907021', '证据材料卷', 1, 152, 2),
|
||
|
|
('JZ20260907021', '侦查工作卷', 1, 98, 3)
|
||
|
|
) AS v(dossier_no, volume_name, volume_count, page_count, sort_no) ON d.dossier_no = v.dossier_no AND d.deleted_flag = FALSE
|
||
|
|
WHERE NOT EXISTS (
|
||
|
|
SELECT 1 FROM t_dossier_volume dv WHERE dv.dossier_id = d.dossier_id
|
||
|
|
);
|
||
|
|
|
||
|
|
-- ========== 4. 登记归档操作留痕 ==========
|
||
|
|
INSERT INTO t_dossier_operation_log (dossier_id, operation_type, operation_title, operator_name, operator_id, remark, related_no, create_time)
|
||
|
|
SELECT d.dossier_id, 'register', '登记归档', d.owner_officer, d.owner_user_id,
|
||
|
|
'测试数据：共登记 ' || COALESCE(v.cnt, 1) || ' 个分册', d.dossier_no, NOW() - INTERVAL '3 day'
|
||
|
|
FROM t_dossier d
|
||
|
|
LEFT JOIN (
|
||
|
|
SELECT dossier_id, COUNT(*) AS cnt FROM t_dossier_volume GROUP BY dossier_id
|
||
|
|
) v ON v.dossier_id = d.dossier_id
|
||
|
|
WHERE d.dossier_no LIKE 'JZ20260907%'
|
||
|
|
AND d.deleted_flag = FALSE
|
||
|
|
AND NOT EXISTS (
|
||
|
|
SELECT 1 FROM t_dossier_operation_log l
|
||
|
|
WHERE l.dossier_id = d.dossier_id AND l.operation_type = 'register'
|
||
|
|
);
|
||
|
|
|
||
|
|
-- ========== 5. 本人借阅单（待确认 / 借阅中 / 历史） ==========
|
||
|
|
INSERT INTO t_dossier_borrow (
|
||
|
|
dossier_id, borrow_type, borrow_matter, borrow_time, planned_return_time, actual_return_time,
|
||
|
|
reason, status, confirmer_name, confirm_time,
|
||
|
|
return_audit_result, return_audit_opinion, return_auditor_name, return_audit_time,
|
||
|
|
applicant_name, applicant_user_id, create_time, update_time
|
||
|
|
)
|
||
|
|
SELECT d.dossier_id, 'self', v.borrow_matter, v.borrow_time, v.planned_return_time, v.actual_return_time,
|
||
|
|
v.reason, v.status, v.confirmer_name, v.confirm_time,
|
||
|
|
v.return_audit_result, v.return_audit_opinion, v.return_auditor_name, v.return_audit_time,
|
||
|
|
d.owner_officer, d.owner_user_id, v.borrow_time, NOW()
|
||
|
|
FROM t_dossier d
|
||
|
|
JOIN (VALUES
|
||
|
|
('JZ20260907004', 'case_review', TIMESTAMP '2026-09-05 09:30:00', TIMESTAMP '2026-09-12 18:00:00', NULL,
|
||
|
|
'补充阅卷核对起诉意见书中的涉案金额认定', 'pending_confirm', NULL, NULL, NULL, NULL, NULL, NULL),
|
||
|
|
('JZ20260907005', 'supplement', TIMESTAMP '2026-09-03 14:20:00', TIMESTAMP '2026-09-10 18:00:00', NULL,
|
||
|
|
'补充侦查需查阅原始银行流水及会计凭证', 'borrowing', '管理员', TIMESTAMP '2026-09-03 16:00:00', NULL, NULL, NULL, NULL),
|
||
|
|
('JZ20260907006', 'case_review', TIMESTAMP '2026-08-28 10:00:00', TIMESTAMP '2026-09-04 18:00:00', TIMESTAMP '2026-09-04 17:30:00',
|
||
|
|
'案件复盘查阅卷宗材料', 'returned', '管理员', TIMESTAMP '2026-08-28 11:00:00',
|
||
|
|
'agree', '卷宗完整，同意归还入库', '管理员', TIMESTAMP '2026-09-04 18:10:00'),
|
||
|
|
('JZ20260907007', 'external', TIMESTAMP '2026-08-20 15:00:00', TIMESTAMP '2026-08-27 18:00:00', NULL,
|
||
|
|
'对外提供法律文书复印件（已取消）', 'rejected', NULL, NULL, NULL, NULL, NULL, NULL)
|
||
|
|
) AS v(dossier_no, borrow_matter, borrow_time, planned_return_time, actual_return_time,
|
||
|
|
reason, status, confirmer_name, confirm_time,
|
||
|
|
return_audit_result, return_audit_opinion, return_auditor_name, return_audit_time)
|
||
|
|
ON d.dossier_no = v.dossier_no AND d.deleted_flag = FALSE
|
||
|
|
WHERE NOT EXISTS (
|
||
|
|
SELECT 1 FROM t_dossier_borrow b WHERE b.dossier_id = d.dossier_id AND b.borrow_type = 'self'
|
||
|
|
);
|
||
|
|
|
||
|
|
-- ========== 6. 借阅相关操作留痕 ==========
|
||
|
|
INSERT INTO t_dossier_operation_log (dossier_id, operation_type, operation_title, operator_name, operator_id, remark, related_no, create_time)
|
||
|
|
SELECT b.dossier_id, 'borrow_apply', '借阅本人卷宗', b.applicant_name, b.applicant_user_id,
|
||
|
|
'事由：' || CASE b.borrow_matter
|
||
|
|
WHEN 'case_review' THEN '案件复核'
|
||
|
|
WHEN 'supplement' THEN '补充侦查'
|
||
|
|
WHEN 'external' THEN '对外提供'
|
||
|
|
ELSE b.borrow_matter END || '；' || LEFT(b.reason, 80),
|
||
|
|
d.dossier_no, b.borrow_time
|
||
|
|
FROM t_dossier_borrow b
|
||
|
|
JOIN t_dossier d ON d.dossier_id = b.dossier_id
|
||
|
|
WHERE d.dossier_no LIKE 'JZ20260907%'
|
||
|
|
AND b.borrow_type = 'self'
|
||
|
|
AND NOT EXISTS (
|
||
|
|
SELECT 1 FROM t_dossier_operation_log l
|
||
|
|
WHERE l.dossier_id = b.dossier_id AND l.operation_type = 'borrow_apply'
|
||
|
|
);
|