his/sql/bug_318_repair_historical_data.sql

-- ========================================================
-- Bug #318 历史数据修复脚本
-- 为已存在但未生成手术医嘱的手术申请单补齐数据
-- ========================================================

-- 1. 查询所有未生成手术医嘱的手术申请单
SELECT 
    rf.id as request_form_id,
    rf.prescription_no,
    rf.encounter_id,
    rf.patient_id,
    rf.requester_id,
    rf.create_time,
    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  -- 没有对应的手术医嘱
ORDER BY rf.create_time DESC;

-- ========================================================
-- 2. 修复脚本：为缺失的手术申请单生成手术医嘱
-- ========================================================

-- 创建临时表存储需要修复的数据
CREATE TEMPORARY TABLE IF NOT EXISTS temp_surgery_repair AS
SELECT 
    rf.id as request_form_id,
    rf.prescription_no,
    rf.encounter_id,
    rf.patient_id,
    rf.requester_id,
    rf.create_time,
    rf.desc_json,
    rf.org_id
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 need_repair_count FROM temp_surgery_repair;

-- ========================================================
-- 3. 插入手术医嘱（ServiceRequest）
-- ========================================================

INSERT INTO wor_service_request (
    id,
    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
)
SELECT 
    nextval('wor_service_request_id_seq'),  -- 使用序列生成ID
    LPAD(FLOOR(RANDOM() * 10000)::TEXT, 4, '0'),  -- 生成4位随机bus_no
    prescription_no,
    1,  -- status_enum: 1-待签发(DRAFT)
    1,  -- generate_source_enum: 1-医生处方
    2,  -- therapy_enum: 2-临时医嘱(TEMPORARY)
    1,  -- quantity: 1
    '次',  -- unit_code
    4,  -- category_enum: 4-手术
    patient_id,
    requester_id,
    encounter_id,
    create_time,
    org_id,
    CASE 
        WHEN desc_json IS NOT NULL AND desc_json != '' THEN desc_json
        ELSE NULL
    END,  -- content_json: 存储手术详情
    '0',  -- delete_flag
    create_time,
    requester_id
FROM temp_surgery_repair;

-- ========================================================
-- 4. 插入手术收费项目（ChargeItem）
-- ========================================================

-- 首先创建临时表关联新生成的ServiceRequest
CREATE TEMPORARY TABLE IF NOT EXISTS temp_new_service_request 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_surgery_repair t ON t.prescription_no = sr.prescription_no
WHERE sr.category_enum = 4
  AND sr.delete_flag = '0'
  AND sr.create_time > NOW() - INTERVAL '1 hour';  -- 只取最近1小时新增的

-- 插入手术费用收费项目
INSERT INTO adm_charge_item (
    id,
    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
)
SELECT 
    nextval('adm_charge_item_id_seq'),
    'CI' || bus_no,
    1,  -- status_enum: 1-草稿(DRAFT)
    1,  -- generate_source_enum: 1-医生处方
    patient_id,
    3,  -- context_enum: 3-诊疗
    encounter_id,
    requester_id,
    create_time,
    'wor_service_request',  -- service_table
    service_request_id,  -- service_id
    'wor_activity_definition',  -- product_table
    org_id,
    1,  -- quantity_value
    '次',  -- quantity_unit
    COALESCE(
        (desc_json::jsonb->>'surgeryFee')::numeric,
        0
    ),  -- unit_price: 从desc_json解析手术费用
    COALESCE(
        (desc_json::jsonb->>'surgeryFee')::numeric,
        0
    ),  -- total_price
    '0',
    create_time,
    requester_id
FROM temp_new_service_request;

-- 插入麻醉费用收费项目（如果有麻醉费用）
INSERT INTO adm_charge_item (
    id,
    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
)
SELECT 
    nextval('adm_charge_item_id_seq'),
    'CI' || bus_no || '_A',
    1,
    1,
    patient_id,
    3,
    encounter_id,
    requester_id,
    create_time,
    'wor_service_request',
    service_request_id,
    'wor_activity_definition',
    org_id,
    1,
    '次',
    (desc_json::jsonb->>'anesthesiaFee')::numeric,
    (desc_json::jsonb->>'anesthesiaFee')::numeric,
    '0',
    create_time,
    requester_id
FROM temp_new_service_request
WHERE desc_json::jsonb->>'anesthesiaFee' IS NOT NULL
  AND (desc_json::jsonb->>'anesthesiaFee')::numeric > 0;

-- ========================================================
-- 5. 验证修复结果
-- ========================================================

-- 查看修复后的手术医嘱数量
SELECT 
    '修复后的手术医嘱数量' as check_item,
    COUNT(*) as count
FROM wor_service_request sr
WHERE sr.category_enum = 4
  AND sr.delete_flag = '0'
  AND EXISTS (
      SELECT 1 FROM doc_request_form rf 
      WHERE rf.prescription_no = sr.prescription_no 
      AND rf.type_code = 'PROCEDURE'
  );

-- 查看修复后的收费项目数量
SELECT 
    '修复后的手术收费项目数量' as check_item,
    COUNT(*) as count
FROM adm_charge_item ci
WHERE ci.service_table = 'wor_service_request'
  AND ci.delete_flag = '0'
  AND EXISTS (
      SELECT 1 FROM wor_service_request sr 
      WHERE sr.id = ci.service_id 
      AND sr.category_enum = 4
  );

-- 清理临时表
DROP TABLE IF EXISTS temp_surgery_repair;
DROP TABLE IF EXISTS temp_new_service_request;

-- ========================================================
-- 完成！
-- ========================================================