generated from youfool-project/youfool-prj-springboot3-template
120 lines
5.4 KiB
SQL
120 lines
5.4 KiB
SQL
-- ============================================================================
|
||
-- OARMS - CW-4 线索转办管理 初始数据
|
||
-- Database: DM8 (达梦)
|
||
-- Schema: OARMS
|
||
-- Version: V10.0.1
|
||
-- Date: 2026-05-27
|
||
-- Description: 基于 CW-MC-001 线索的转办记录 + 操作日志
|
||
-- Note:
|
||
-- 1. CW-MC-001 当前线索状态为 1(待转办)，创建一条已转办记录
|
||
-- 2. 同时将线索状态更新为 2(已转办)
|
||
-- 3. 转办目标使用行政区划中的区域编码
|
||
-- ============================================================================
|
||
|
||
-- 1. CW-MC-001(白云万达虚假宣传) → 转办到白云区市场监督管理局
|
||
INSERT INTO OARMS.CW_CLUE_TRANSFER_RECORD (
|
||
ID, CLUE_ID, TRANSFER_TARGET_DISTRICT, TRANSFER_TARGET_DEPARTMENT,
|
||
TRANSFER_TARGET_PERSON, TRANSFER_DESCRIPTION, TRANSFER_PERSON,
|
||
TRANSFERRED_AT, TRANSFER_STATUS, EXTERNAL_CLUE_ID,
|
||
CREATE_BY, CREATE_TIME, CREATE_NAME, UPDATE_BY, UPDATE_TIME, UPDATE_NAME
|
||
) VALUES (
|
||
'CW-CTR-001', 'CW-MC-001', '440111', '白云区市场监督管理局',
|
||
'张志明', '该线索涉及白云万达广场LED大屏虚假宣传"包治百病"，建议转白云区市监局处理',
|
||
'系统管理员',
|
||
TO_TIMESTAMP('2026-05-25 10:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'transferred', 'EXT-CLUE-20260525-001',
|
||
'system', TO_TIMESTAMP('2026-05-25 10:00:00', 'YYYY-MM-DD HH24:MI:SS'), '系统',
|
||
'system', TO_TIMESTAMP('2026-05-25 10:00:00', 'YYYY-MM-DD HH24:MI:SS'), '系统'
|
||
);
|
||
|
||
-- 2. CW-MC-001 → 转办记录2（天河区，已办结状态）
|
||
INSERT INTO OARMS.CW_CLUE_TRANSFER_RECORD (
|
||
ID, CLUE_ID, TRANSFER_TARGET_DISTRICT, TRANSFER_TARGET_DEPARTMENT,
|
||
TRANSFER_TARGET_PERSON, TRANSFER_DESCRIPTION, TRANSFER_PERSON,
|
||
TRANSFERRED_AT, TRANSFER_STATUS, EXTERNAL_CLUE_ID,
|
||
DISPOSAL_RESULT, DISPOSAL_COMPLETED_AT,
|
||
CREATE_BY, CREATE_TIME, CREATE_NAME, UPDATE_BY, UPDATE_TIME, UPDATE_NAME
|
||
) VALUES (
|
||
'CW-CTR-002', 'CW-MC-001', '440106', '天河区市场监督管理局',
|
||
'李伟强', '同时转天河区市监局协助调查广告主信息',
|
||
'系统管理员',
|
||
TO_TIMESTAMP('2026-05-26 09:30:00', 'YYYY-MM-DD HH24:MI:SS'), 'completed', 'EXT-CLUE-20260526-002',
|
||
'已联系广告主，责令拆除违规广告内容，罚款5000元',
|
||
TO_TIMESTAMP('2026-05-26 16:00:00', 'YYYY-MM-DD HH24:MI:SS'),
|
||
'system', TO_TIMESTAMP('2026-05-26 09:30:00', 'YYYY-MM-DD HH24:MI:SS'), '系统',
|
||
'system', TO_TIMESTAMP('2026-05-26 16:00:00', 'YYYY-MM-DD HH24:MI:SS'), '系统'
|
||
);
|
||
|
||
-- ===================== 转办操作日志 =====================
|
||
|
||
-- CW-CTR-001: 提交 + 推送成功
|
||
INSERT INTO OARMS.CW_TRANSFER_OPERATION_LOG (
|
||
ID, CLUE_TRANSFER_RECORD_ID, OPERATION_TYPE, OPERATION_DETAIL,
|
||
FROM_STATUS, TO_STATUS, OPERATOR, OPERATED_AT
|
||
) VALUES (
|
||
'CW-TOL-001', 'CW-CTR-001', 'submit',
|
||
'提交线索转办，目标：白云区市场监督管理局',
|
||
NULL, 'transferred', '系统管理员',
|
||
TO_TIMESTAMP('2026-05-25 10:00:00', 'YYYY-MM-DD HH24:MI:SS')
|
||
);
|
||
|
||
INSERT INTO OARMS.CW_TRANSFER_OPERATION_LOG (
|
||
ID, CLUE_TRANSFER_RECORD_ID, OPERATION_TYPE, OPERATION_DETAIL,
|
||
FROM_STATUS, TO_STATUS, OPERATOR, OPERATED_AT
|
||
) VALUES (
|
||
'CW-TOL-002', 'CW-CTR-001', 'push_success',
|
||
'线索推送成功，外部线索ID: EXT-CLUE-20260525-001',
|
||
'transferred', 'transferred', '系统管理员',
|
||
TO_TIMESTAMP('2026-05-25 10:00:05', 'YYYY-MM-DD HH24:MI:SS')
|
||
);
|
||
|
||
-- CW-CTR-002: 提交 + 推送成功 + 状态同步 + 结果反馈
|
||
INSERT INTO OARMS.CW_TRANSFER_OPERATION_LOG (
|
||
ID, CLUE_TRANSFER_RECORD_ID, OPERATION_TYPE, OPERATION_DETAIL,
|
||
FROM_STATUS, TO_STATUS, OPERATOR, OPERATED_AT
|
||
) VALUES (
|
||
'CW-TOL-003', 'CW-CTR-002', 'submit',
|
||
'提交线索转办，目标：天河区市场监督管理局',
|
||
NULL, 'transferred', '系统管理员',
|
||
TO_TIMESTAMP('2026-05-26 09:30:00', 'YYYY-MM-DD HH24:MI:SS')
|
||
);
|
||
|
||
INSERT INTO OARMS.CW_TRANSFER_OPERATION_LOG (
|
||
ID, CLUE_TRANSFER_RECORD_ID, OPERATION_TYPE, OPERATION_DETAIL,
|
||
FROM_STATUS, TO_STATUS, OPERATOR, OPERATED_AT
|
||
) VALUES (
|
||
'CW-TOL-004', 'CW-CTR-002', 'push_success',
|
||
'线索推送成功，外部线索ID: EXT-CLUE-20260526-002',
|
||
'transferred', 'transferred', '系统管理员',
|
||
TO_TIMESTAMP('2026-05-26 09:30:05', 'YYYY-MM-DD HH24:MI:SS')
|
||
);
|
||
|
||
INSERT INTO OARMS.CW_TRANSFER_OPERATION_LOG (
|
||
ID, CLUE_TRANSFER_RECORD_ID, OPERATION_TYPE, OPERATION_DETAIL,
|
||
FROM_STATUS, TO_STATUS, OPERATOR, OPERATED_AT
|
||
) VALUES (
|
||
'CW-TOL-005', 'CW-CTR-002', 'status_update',
|
||
'状态从 transferred 变更为 processing',
|
||
'transferred', 'processing', '外部系统回调',
|
||
TO_TIMESTAMP('2026-05-26 14:00:00', 'YYYY-MM-DD HH24:MI:SS')
|
||
);
|
||
|
||
INSERT INTO OARMS.CW_TRANSFER_OPERATION_LOG (
|
||
ID, CLUE_TRANSFER_RECORD_ID, OPERATION_TYPE, OPERATION_DETAIL,
|
||
FROM_STATUS, TO_STATUS, OPERATOR, OPERATED_AT
|
||
) VALUES (
|
||
'CW-TOL-006', 'CW-CTR-002', 'status_update',
|
||
'状态从 processing 变更为 completed',
|
||
'processing', 'completed', '外部系统回调',
|
||
TO_TIMESTAMP('2026-05-26 16:00:00', 'YYYY-MM-DD HH24:MI:SS')
|
||
);
|
||
|
||
-- ===================== 更新线索状态 =====================
|
||
-- CW-MC-001 从待转办(1)更新为已转办(2)
|
||
UPDATE OARMS.CW_MONITORING_CLUE
|
||
SET CLUE_STATUS = 2,
|
||
TRANSFERRED_AT = TO_TIMESTAMP('2026-05-25 10:00:00', 'YYYY-MM-DD HH24:MI:SS'),
|
||
UPDATE_BY = 'system',
|
||
UPDATE_TIME = TO_TIMESTAMP('2026-05-26 16:00:00', 'YYYY-MM-DD HH24:MI:SS'),
|
||
UPDATE_NAME = '系统'
|
||
WHERE ID = 'CW-MC-001';
|