160 lines
3.8 KiB
SQL
160 lines
3.8 KiB
SQL
-- Bug #318 历史数据修复 - 步骤2：执行修复
|
||
-- ⚠️ 请在执行前备份数据库！
|
||
|
||
-- =====================================================
|
||
-- 步骤 2.1: 创建临时表存储需要修复的手术申请单
|
||
-- =====================================================
|
||
|
||
CREATE TEMPORARY TABLE temp_repair_list AS
|
||
SELECT
|
||
rf.id as request_form_id,
|
||
rf.prescription_no,
|
||
rf.encounter_id,
|
||
rf.patient_id,
|
||
rf.requester_id,
|
||
rf.create_time,
|
||
rf.org_id,
|
||
rf.desc_json
|
||
FROM doc_request_form rf
|
||
LEFT JOIN wor_service_request sr ON sr.prescription_no = rf.prescription_no
|
||
AND sr.delete_flag = '0'
|
||
WHERE rf.type_code = 'PROCEDURE'
|
||
AND rf.delete_flag = '0'
|
||
AND sr.id IS NULL;
|
||
|
||
-- 查看需要修复的记录数
|
||
SELECT COUNT(*) as 需要修复的记录数 FROM temp_repair_list;
|
||
|
||
-- =====================================================
|
||
-- 步骤 2.2: 生成手术医嘱（ServiceRequest）
|
||
-- =====================================================
|
||
|
||
INSERT INTO wor_service_request (
|
||
bus_no,
|
||
prescription_no,
|
||
status_enum,
|
||
generate_source_enum,
|
||
therapy_enum,
|
||
quantity,
|
||
unit_code,
|
||
category_enum,
|
||
patient_id,
|
||
requester_id,
|
||
encounter_id,
|
||
authored_time,
|
||
org_id,
|
||
content_json,
|
||
delete_flag,
|
||
create_time,
|
||
create_by,
|
||
tenant_id
|
||
)
|
||
SELECT
|
||
LPAD(FLOOR(RANDOM() * 10000)::TEXT, 4, '0'),
|
||
prescription_no,
|
||
1, -- 待签发
|
||
1, -- 医生处方
|
||
2, -- 临时医嘱
|
||
1,
|
||
'次',
|
||
4, -- 手术
|
||
patient_id,
|
||
requester_id,
|
||
encounter_id,
|
||
create_time,
|
||
org_id,
|
||
desc_json,
|
||
'0',
|
||
create_time,
|
||
requester_id,
|
||
1 -- tenant_id
|
||
FROM temp_repair_list;
|
||
|
||
-- 查看生成的手术医嘱数量
|
||
SELECT '生成的手术医嘱数量' as 检查项, COUNT(*) as 数量
|
||
FROM wor_service_request
|
||
WHERE prescription_no IN (SELECT prescription_no FROM temp_repair_list);
|
||
|
||
-- =====================================================
|
||
-- 步骤 2.3: 生成手术收费项目（ChargeItem）
|
||
-- =====================================================
|
||
|
||
-- 获取刚生成的ServiceRequest
|
||
CREATE TEMPORARY TABLE temp_new_requests AS
|
||
SELECT
|
||
sr.id as service_request_id,
|
||
sr.prescription_no,
|
||
sr.patient_id,
|
||
sr.encounter_id,
|
||
sr.requester_id,
|
||
sr.create_time,
|
||
sr.org_id,
|
||
sr.bus_no,
|
||
t.desc_json
|
||
FROM wor_service_request sr
|
||
JOIN temp_repair_list t ON t.prescription_no = sr.prescription_no
|
||
WHERE sr.category_enum = 4
|
||
AND sr.delete_flag = '0';
|
||
|
||
-- 插入手术费用
|
||
INSERT INTO adm_charge_item (
|
||
bus_no,
|
||
status_enum,
|
||
generate_source_enum,
|
||
patient_id,
|
||
context_enum,
|
||
encounter_id,
|
||
enterer_id,
|
||
entered_date,
|
||
service_table,
|
||
service_id,
|
||
product_table,
|
||
requesting_org_id,
|
||
quantity_value,
|
||
quantity_unit,
|
||
unit_price,
|
||
total_price,
|
||
delete_flag,
|
||
create_time,
|
||
create_by,
|
||
tenant_id
|
||
)
|
||
SELECT
|
||
'CI' || bus_no,
|
||
1, -- 草稿
|
||
1, -- 医生处方
|
||
patient_id,
|
||
3, -- 诊疗
|
||
encounter_id,
|
||
requester_id,
|
||
create_time,
|
||
'wor_service_request',
|
||
service_request_id,
|
||
'wor_activity_definition',
|
||
org_id,
|
||
1,
|
||
'次',
|
||
COALESCE((desc_json::jsonb->>'surgeryFee')::numeric, 0),
|
||
COALESCE((desc_json::jsonb->>'surgeryFee')::numeric, 0),
|
||
'0',
|
||
create_time,
|
||
requester_id,
|
||
1
|
||
FROM temp_new_requests;
|
||
|
||
-- 查看生成的收费项目数量
|
||
SELECT '生成的手术收费项目数量' as 检查项, COUNT(*) as 数量
|
||
FROM adm_charge_item
|
||
WHERE service_id IN (SELECT service_request_id FROM temp_new_requests);
|
||
|
||
-- =====================================================
|
||
-- 步骤 2.4: 清理临时表
|
||
-- =====================================================
|
||
|
||
DROP TABLE IF EXISTS temp_repair_list;
|
||
DROP TABLE IF EXISTS temp_new_requests;
|
||
|
||
-- =====================================================
|
||
-- 完成！
|
||
-- =====================================================
|