118 lines
3.4 KiB
Python
118 lines
3.4 KiB
Python
#!/usr/bin/env python3
|
|
# -*- coding: utf-8 -*-
|
|
import psycopg2
|
|
import json
|
|
from datetime import datetime
|
|
|
|
DB_CONFIG = {
|
|
"host": "192.168.110.252",
|
|
"port": 15432,
|
|
"database": "postgresql",
|
|
"user": "postgresql",
|
|
"password": "Jchl1528",
|
|
"options": "-c search_path=hisdev",
|
|
}
|
|
|
|
|
|
def check_surgery_advice():
|
|
conn = None
|
|
try:
|
|
print("Connecting to database...")
|
|
conn = psycopg2.connect(**DB_CONFIG)
|
|
cursor = conn.cursor()
|
|
print("Connected!\n")
|
|
|
|
print("=" * 80)
|
|
print("Surgery Advice Diagnostic Report")
|
|
print("=" * 80)
|
|
print(f"Time: {datetime.now()}")
|
|
print()
|
|
|
|
# 1. Query recent surgery advice
|
|
print("1. Recent surgery advice records:")
|
|
print("-" * 80)
|
|
cursor.execute("""
|
|
SELECT
|
|
id,
|
|
category_enum,
|
|
activity_id,
|
|
content_json::jsonb->>'surgeryName' as surgery_name,
|
|
content_json::jsonb->>'surgeryCode' as surgery_code,
|
|
create_time
|
|
FROM wor_service_request
|
|
WHERE category_enum = 4
|
|
AND delete_flag = '0'
|
|
ORDER BY create_time DESC
|
|
LIMIT 5
|
|
""")
|
|
|
|
rows = cursor.fetchall()
|
|
if not rows:
|
|
print("No surgery advice found (category_enum = 4)")
|
|
else:
|
|
for row in rows:
|
|
print(f"\nID: {row[0]}")
|
|
print(f" category_enum: {row[1]}")
|
|
print(f" activity_id: {row[2]}")
|
|
print(f" surgeryName from JSON: {row[3]}")
|
|
print(f" surgeryCode from JSON: {row[4]}")
|
|
print(f" create_time: {row[5]}")
|
|
|
|
if not row[3]:
|
|
print(" WARNING: surgeryName is EMPTY!")
|
|
else:
|
|
print(" OK: surgeryName exists")
|
|
|
|
print("\n" + "=" * 80)
|
|
|
|
# 2. Test SQL COALESCE query
|
|
print("\n2. Testing SQL COALESCE query:")
|
|
print("-" * 80)
|
|
|
|
cursor.execute("""
|
|
SELECT
|
|
wsr.id,
|
|
COALESCE(wad.NAME, wsr.content_json::jsonb->>'surgeryName', wsr.content_json::jsonb->>'adviceName') AS advice_name,
|
|
wsr.content_json::jsonb->>'surgeryName' AS surgery_name_from_json,
|
|
wad.NAME AS activity_name,
|
|
wsr.activity_id
|
|
FROM wor_service_request wsr
|
|
LEFT JOIN wor_activity_definition wad ON wad.ID = wsr.activity_id AND wad.delete_flag = '0'
|
|
WHERE wsr.category_enum = 4
|
|
AND wsr.delete_flag = '0'
|
|
ORDER BY wsr.create_time DESC
|
|
LIMIT 5
|
|
""")
|
|
|
|
rows = cursor.fetchall()
|
|
for row in rows:
|
|
print(f"\nID: {row[0]}")
|
|
print(f" COALESCE advice_name: {row[1]}")
|
|
print(f" surgeryName from JSON: {row[2]}")
|
|
print(f" activity_definition name: {row[3]}")
|
|
print(f" activity_id: {row[4]}")
|
|
|
|
if not row[1]:
|
|
print(" WARNING: advice_name is EMPTY!")
|
|
else:
|
|
print(" OK: advice_name exists")
|
|
|
|
print("\n" + "=" * 80)
|
|
print("Diagnostic complete")
|
|
print("=" * 80)
|
|
|
|
cursor.close()
|
|
|
|
except Exception as e:
|
|
print(f"ERROR: {e}")
|
|
import traceback
|
|
|
|
traceback.print_exc()
|
|
finally:
|
|
if conn:
|
|
conn.close()
|
|
|
|
|
|
if __name__ == "__main__":
|
|
check_surgery_advice()
|