-- ==========================================
|
-- 任务附件同步定时任务配置脚本
|
-- ==========================================
|
--
|
-- 功能说明:
|
-- 将已同步调度单的任务附件同步到旧系统ImageData表
|
--
|
-- 使用场景:
|
-- 1. 新系统上传了任务附件
|
-- 2. 任务的调度单已成功同步到旧系统
|
-- 3. 附件需要同步到旧系统以便旧系统查看
|
--
|
-- 同步条件:
|
-- 1. 任务类型为急救转运 (task_type = 'EMERGENCY_TRANSFER')
|
-- 2. 调度单已同步成功 (dispatch_sync_status = 2)
|
-- 3. 有调度单ID和服务单ID (legacy_dispatch_ord_id IS NOT NULL AND legacy_service_ord_id IS NOT NULL)
|
-- 4. 附件未同步 (synced_to_image_data = 0 OR synced_to_image_data IS NULL)
|
--
|
-- 执行频率建议:
|
-- - 生产环境: 每5分钟执行一次 (0 0/5 * * * ?)
|
-- - 测试环境: 每2分钟执行一次 (0 0/2 * * * ?)
|
--
|
-- 注意事项:
|
-- 1. 依赖调度单同步完成
|
-- 2. 会上传文件并生成缩略图
|
-- 3. 附件分类映射见TaskAttachmentSyncServiceImpl类
|
-- ==========================================
|
|
-- 插入定时任务配置
|
INSERT INTO `sys_job` (
|
`job_name`,
|
`job_group`,
|
`invoke_target`,
|
`cron_expression`,
|
`misfire_policy`,
|
`concurrent`,
|
`status`,
|
`create_by`,
|
`create_time`,
|
`update_by`,
|
`update_time`,
|
`remark`
|
) VALUES (
|
'任务附件同步',
|
'DEFAULT',
|
'legacySystemSyncTask.syncPendingAttachments()',
|
'0 0/5 * * * ?',
|
'2',
|
'1',
|
'0',
|
'admin',
|
NOW(),
|
'admin',
|
NOW(),
|
'将已同步调度单的任务附件同步到旧系统ImageData表，每5分钟执行一次。同步条件：调度单已同步、附件未同步、有服务单和调度单ID。'
|
);
|
|
-- ==========================================
|
-- 验证查询
|
-- ==========================================
|
|
-- 1. 查看定时任务是否添加成功
|
SELECT
|
job_id,
|
job_name,
|
job_group,
|
invoke_target,
|
cron_expression,
|
CASE status
|
WHEN '0' THEN '正常'
|
WHEN '1' THEN '暂停'
|
END AS job_status,
|
remark
|
FROM sys_job
|
WHERE job_name = '任务附件同步';
|
|
-- 2. 查看需要同步的附件数量
|
SELECT
|
COUNT(*) AS total_attachments,
|
SUM(CASE WHEN a.attachment_category = '1' THEN 1 ELSE 0 END) AS consent_form,
|
SUM(CASE WHEN a.attachment_category = '2' THEN 1 ELSE 0 END) AS patient_data,
|
SUM(CASE WHEN a.attachment_category = '3' THEN 1 ELSE 0 END) AS operation_record,
|
SUM(CASE WHEN a.attachment_category = '4' THEN 1 ELSE 0 END) AS before_departure,
|
SUM(CASE WHEN a.attachment_category = '5' THEN 1 ELSE 0 END) AS after_departure,
|
SUM(CASE WHEN a.attachment_category = '6' THEN 1 ELSE 0 END) AS seat_belt
|
FROM sys_task_attachment a
|
INNER JOIN sys_task t ON a.task_id = t.task_id
|
INNER JOIN sys_task_emergency e ON t.task_id = e.task_id
|
WHERE t.task_type = 'EMERGENCY_TRANSFER'
|
AND e.dispatch_sync_status = 2
|
AND e.legacy_dispatch_ord_id IS NOT NULL
|
AND e.legacy_service_ord_id IS NOT NULL
|
AND (a.synced_to_image_data = 0 OR a.synced_to_image_data IS NULL);
|
|
-- 3. 查看待同步附件详情（最近10条）
|
SELECT
|
a.attachment_id,
|
t.task_code,
|
a.file_name,
|
CASE a.attachment_category
|
WHEN '1' THEN '知情同意书'
|
WHEN '2' THEN '病人资料'
|
WHEN '3' THEN '操作记录'
|
WHEN '4' THEN '出车前'
|
WHEN '5' THEN '出车后'
|
WHEN '6' THEN '系安全带'
|
ELSE '未分类'
|
END AS category_name,
|
e.legacy_service_ord_id,
|
e.legacy_dispatch_ord_id,
|
a.upload_time,
|
a.synced_to_image_data,
|
a.sync_time
|
FROM sys_task_attachment a
|
INNER JOIN sys_task t ON a.task_id = t.task_id
|
INNER JOIN sys_task_emergency e ON t.task_id = e.task_id
|
WHERE t.task_type = 'EMERGENCY_TRANSFER'
|
AND e.dispatch_sync_status = 2
|
AND e.legacy_dispatch_ord_id IS NOT NULL
|
AND e.legacy_service_ord_id IS NOT NULL
|
AND (a.synced_to_image_data = 0 OR a.synced_to_image_data IS NULL)
|
ORDER BY a.upload_time DESC
|
LIMIT 10;
|
|
-- 4. 查看已同步附件统计
|
SELECT
|
COUNT(*) AS synced_attachments,
|
MIN(a.sync_time) AS first_sync_time,
|
MAX(a.sync_time) AS last_sync_time
|
FROM sys_task_attachment a
|
WHERE a.synced_to_image_data = 1
|
AND a.sync_time IS NOT NULL;
|
|
-- 5. 按任务统计待同步附件
|
SELECT
|
t.task_id,
|
t.task_code,
|
e.legacy_service_ord_id,
|
e.legacy_dispatch_ord_id,
|
COUNT(a.attachment_id) AS attachment_count,
|
GROUP_CONCAT(
|
CASE a.attachment_category
|
WHEN '1' THEN '知情同意书'
|
WHEN '2' THEN '病人资料'
|
WHEN '3' THEN '操作记录'
|
WHEN '4' THEN '出车前'
|
WHEN '5' THEN '出车后'
|
WHEN '6' THEN '系安全带'
|
END
|
SEPARATOR ', '
|
) AS categories
|
FROM sys_task t
|
INNER JOIN sys_task_emergency e ON t.task_id = e.task_id
|
INNER JOIN sys_task_attachment a ON t.task_id = a.task_id
|
WHERE t.task_type = 'EMERGENCY_TRANSFER'
|
AND e.dispatch_sync_status = 2
|
AND e.legacy_dispatch_ord_id IS NOT NULL
|
AND e.legacy_service_ord_id IS NOT NULL
|
AND (a.synced_to_image_data = 0 OR a.synced_to_image_data IS NULL)
|
GROUP BY t.task_id, t.task_code, e.legacy_service_ord_id, e.legacy_dispatch_ord_id
|
ORDER BY attachment_count DESC;
|
|
-- ==========================================
|
-- 附件分类映射规则参考
|
-- ==========================================
|
/*
|
附件分类 -> ImageData类型:
|
1-知情同意书 -> ImageType: 1
|
2-病人资料 -> ImageType: 2
|
3-操作记录 -> ImageType: 3
|
4-出车前 -> ImageType: 4
|
5-出车后 -> ImageType: 5
|
6-系安全带 -> ImageType: 6
|
|
详细说明见: com.ruoyi.system.service.impl.TaskAttachmentSyncServiceImpl.getImageTypeByCategory()
|
*/
|
|
-- ==========================================
|
-- 同步流程说明
|
-- ==========================================
|
/*
|
1. 查询待同步附件列表（符合条件的附件）
|
2. 按任务分组，获取服务单ID、调度单ID、创建人OA用户ID
|
3. 读取本地附件文件
|
4. 上传到文件服务器并生成缩略图
|
5. 将附件信息写入旧系统ImageData表
|
- DOrdIDDt: 调度单ID
|
- SOrdIDDt: 服务单ID
|
- ImageType: 附件分类对应的类型
|
- ImageUrl: 原图路径
|
- ImageUrls: 缩略图路径
|
- UpImageTime: 上传时间
|
- UpImageOAid: 上传者OA用户ID
|
6. 更新附件同步状态
|
- synced_to_image_data = 1
|
- sync_time = 当前时间
|
- image_data_id = ImageData表ID
|
*/
|
|
-- ==========================================
|
-- 手动触发测试
|
-- ==========================================
|
/*
|
-- 在定时任务管理页面手动执行一次任务，或者在代码中调用:
|
-- legacySystemSyncTask.syncPendingAttachments()
|
|
-- 查看同步日志（需要查看应用日志文件）
|
*/
|
|
-- ==========================================
|
-- 故障排查SQL
|
-- ==========================================
|
|
-- 查看同步失败的附件（如果有错误记录）
|
SELECT
|
a.attachment_id,
|
t.task_code,
|
a.file_name,
|
a.file_path,
|
a.upload_time,
|
a.synced_to_image_data,
|
a.sync_time
|
FROM sys_task_attachment a
|
INNER JOIN sys_task t ON a.task_id = t.task_id
|
WHERE a.synced_to_image_data = 0
|
AND a.upload_time < DATE_SUB(NOW(), INTERVAL 1 HOUR)
|
ORDER BY a.upload_time DESC;
|
|
-- 查看某个任务的所有附件同步状态
|
-- SELECT
|
-- a.attachment_id,
|
-- a.file_name,
|
-- a.attachment_category,
|
-- a.synced_to_image_data,
|
-- a.sync_time,
|
-- a.image_data_id
|
-- FROM sys_task_attachment a
|
-- WHERE a.task_id = ? -- 替换为具体的任务ID
|
-- ORDER BY a.upload_time DESC;
|
