from fastapi import FastAPI, HTTPException, File, UploadFile, Form from pydantic import BaseModel from typing import Optional, Dict, List import mysql.connector import os import uuid import shutil import openpyxl from openpyxl.drawing.image import Image import pytz from datetime import datetime from fastapi.staticfiles import StaticFiles #[추가]: pdf로 이동 import logging from logging.handlers import RotatingFileHandler # Basic logging configuration logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s') app = FastAPI() # --- DB 접속 정보 --- DB_HOST = os.environ.get('DB_HOST', 'localhost') DB_USER = os.environ.get('DB_USER', 'sos-user') DB_PASSWORD = os.environ.get('DB_PASSWORD', 'PassWord12!!') DB_NAME = os.environ.get('DB_NAME', 'sos') # --- 경로 정보 --- UPLOAD_DIR = "/home/air/sos/uploads/" TEMPLATE_DIR = "/home/air/sos/templates/" # --- Pydantic 모델들은 더 이상 각 API에서 직접 사용되지 않음 --- # --- API들은 이제 Form 데이터로 직접 값을 받음 --- def get_db_connection(): return mysql.connector.connect( host=DB_HOST, user=DB_USER, password=DB_PASSWORD, database=DB_NAME ) # [추가]: PDF 정적 경로 마운트 app.mount( "/reports", StaticFiles(directory=TEMPLATE_DIR), name="reports" ) # --- two_page.html: 환자 정보 저장 (마스터 생성 함수) --- @app.post("/sos/api/save_patient") async def save_patient( No: str = Form(...), name1: Optional[str] = Form(None), name2: Optional[str] = Form(None), age: Optional[str] = Form(None), gender: Optional[str] = Form(None), address: Optional[str] = Form(None), phone_number: Optional[str] = Form(None), month: Optional[str] = Form(None), day: Optional[str] = Form(None), am_pm: Optional[str] = Form(None), time_h: Optional[str] = Form(None), time_m: Optional[str] = Form(None), triage_officer_name1: Optional[str] = Form(None), triage_officer_name2: Optional[str] = Form(None), transport_agency_name: Optional[str] = Form(None), receiving_hospital_name: Optional[str] = Form(None), urgency_level: Optional[str] = Form(None), # [추가] 긴급도 필드 img: Optional[UploadFile] = File(None) ): logging.info(f"Attempting to save patient with No: {No}") cnx = get_db_connection() cursor = cnx.cursor() try: # 1. 중복 No 확인 cursor.execute("SELECT No FROM patient WHERE No = %s", (No,)) if cursor.fetchone(): logging.warning(f"Attempted to create a patient with duplicate No: {No}") raise HTTPException(status_code=409, detail="この番号は既に使用されています。別の番号を入力してください。") # 2. 이미지 파일 처리 img_path = None if img: os.makedirs(UPLOAD_DIR, exist_ok=True) unique_filename = f"{uuid.uuid4()}{os.path.splitext(img.filename)[1]}" img_path = os.path.join(UPLOAD_DIR, unique_filename) with open(img_path, "wb") as buffer: shutil.copyfileobj(img.file, buffer) logging.info(f"Saved patient image to {img_path}") # 3. patient 테이블에 INSERT insert_patient_query = """ INSERT INTO patient (No, name1, name2, age, gender, img, address, phone_number, month, day, am_pm, time_h, time_m, triage_officer_name1, triage_officer_name2, transport_agency_name, receiving_hospital_name, date_time, day_of_week) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) """ jst = pytz.timezone('Asia/Tokyo') now_jst = datetime.now(jst) date_time_str = now_jst.strftime('%Y-%m-%d %H:%M:%S') weekdays = ["月", "火", "水", "木", "金", "土", "日"] day_of_week = weekdays[now_jst.weekday()] effective_name2 = name2 if name2 and name2.strip() else "未入力" patient_values = ( No, name1, effective_name2, age, gender, img_path, address, phone_number, month, day, am_pm, time_h, time_m, triage_officer_name1, triage_officer_name2, transport_agency_name, receiving_hospital_name, date_time_str, day_of_week ) cursor.execute(insert_patient_query, patient_values) logging.info(f"Inserted new patient with No: {No}") # 4. 다른 모든 관련 테이블에 placeholder 행 생성 # patient_info 테이블은 urgency_level을 함께 저장 placeholder_tables = [ "time", "call_received", "primary_triage", "secondary_triage_1", "secondary_triage_2", "secondary_triage_3", "contact_time_T", "memo", "before_arrival", "report" ] for table in placeholder_tables: cursor.execute(f"INSERT INTO {table} (patient_no) VALUES (%s)", (No,)) # patient_info 테이블에 urgency_level과 함께 INSERT insert_pi_query = "INSERT INTO patient_info (patient_no, urgency_level) VALUES (%s, %s)" cursor.execute(insert_pi_query, (No, urgency_level)) logging.info(f"Created placeholder rows for No: {No} in all related tables, including urgency_level for patient_info.") # 5. list 테이블에도 요약 정보 추가 insert_list_query = "INSERT INTO list (patient_no, date_time, day_of_week, name2) VALUES (%s, %s, %s, %s)" list_values = (No, date_time_str, day_of_week, effective_name2) cursor.execute(insert_list_query, list_values) logging.info(f"Added summary to 'list' table for patient_no: {No}") cnx.commit() return {"status": "success", "message": "Patient data saved and placeholders created."} except HTTPException as http_exc: # HTTP 예외는 그대로 다시 발생시킴 raise http_exc except Exception as e: cnx.rollback() logging.error(f"Error in save_patient for No {No}: {e}", exc_info=True) raise HTTPException(status_code=500, detail=str(e)) finally: cursor.close() cnx.close() # --- time_no.html: 시간 기반 레코드 생성 (마스터 생성 함수) --- @app.post("/sos/api/initiate_record_from_time") async def initiate_record_from_time( patient_no: str = Form(...), call_received_h: Optional[str] = Form(None), call_received_m: Optional[str] = Form(None), dispatch_h: Optional[str] = Form(None), dispatch_m: Optional[str] = Form(None), arrival_on_scene_h: Optional[str] = Form(None), arrival_on_scene_m: Optional[str] = Form(None), patient_contact_h: Optional[str] = Form(None), patient_contact_m: Optional[str] = Form(None), transport_start_h: Optional[str] = Form(None), transport_start_m: Optional[str] = Form(None), patient_loaded_h: Optional[str] = Form(None), patient_loaded_m: Optional[str] = Form(None), depart_scene_h: Optional[str] = Form(None), depart_scene_m: Optional[str] = Form(None), arrival_hospital_h: Optional[str] = Form(None), arrival_hospital_m: Optional[str] = Form(None), handover_to_doctor_h: Optional[str] = Form(None), handover_to_doctor_m: Optional[str] = Form(None), return_from_site_h: Optional[str] = Form(None), return_from_site_m: Optional[str] = Form(None), return_to_station_h: Optional[str] = Form(None), return_to_station_m: Optional[str] = Form(None), transfer1_h: Optional[str] = Form(None), transfer1_m: Optional[str] = Form(None), transfer2_h: Optional[str] = Form(None), transfer2_m: Optional[str] = Form(None), dispatch_location: Optional[str] = Form(None), doctor_car_detail: Optional[str] = Form(None) ): logging.info(f"Attempting to initiate time-based record for patient_no: {patient_no}") def to_db(val: Optional[str]) -> Optional[str]: return None if val == '' else val cnx = get_db_connection() cursor = cnx.cursor() try: # 1. 중복 patient_no 확인 (patient 테이블 기준) cursor.execute("SELECT No FROM patient WHERE No = %s", (patient_no,)) if cursor.fetchone(): logging.warning(f"Attempted to create a record with duplicate patient_no: {patient_no}") raise HTTPException(status_code=409, detail="この番号は既に使用されています。別の番号を入力してください。") # 현재 시간 및 요일 생성 jst = pytz.timezone('Asia/Tokyo') now_jst = datetime.now(jst) date_time_str = now_jst.strftime('%Y-%m-%d %H:%M:%S') weekdays = ["月", "火", "水", "木", "金", "土", "日"] day_of_week = weekdays[now_jst.weekday()] # 2. patient 테이블에 최소 정보 INSERT (name2는 NULL) insert_patient_query = """ INSERT INTO patient (No, date_time, day_of_week, name2) VALUES (%s, %s, %s, NULL) """ patient_values = (patient_no, date_time_str, day_of_week) cursor.execute(insert_patient_query, patient_values) logging.info(f"Inserted new minimal patient record with No: {patient_no}") # 3. list 테이블에도 요약 정보 추가 (name2는 NULL) insert_list_query = "INSERT INTO list (patient_no, date_time, day_of_week, name2) VALUES (%s, %s, %s, NULL)" list_values = (patient_no, date_time_str, day_of_week) cursor.execute(insert_list_query, list_values) logging.info(f"Added summary to 'list' table for patient_no: {patient_no}") # 4. time 테이블에 상세 시간 정보 INSERT time_insert_query = """ INSERT INTO time ( patient_no, call_received_h, call_received_m, dispatch_h, dispatch_m, arrival_on_scene_h, arrival_on_scene_m, patient_contact_h, patient_contact_m, transport_start_h, transport_start_m, patient_loaded_h, patient_loaded_m, depart_scene_h, depart_scene_m, arrival_hospital_h, arrival_hospital_m, handover_to_doctor_h, handover_to_doctor_m, return_from_site_h, return_from_site_m, return_to_station_h, return_to_station_m, transfer1_h, transfer1_m, transfer2_h, transfer2_m, dispatch_location, doctor_car_detail ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s ) """ time_values = ( patient_no, to_db(call_received_h), to_db(call_received_m), to_db(dispatch_h), to_db(dispatch_m), to_db(arrival_on_scene_h), to_db(arrival_on_scene_m), to_db(patient_contact_h), to_db(patient_contact_m), to_db(transport_start_h), to_db(transport_start_m), to_db(patient_loaded_h), to_db(patient_loaded_m), to_db(depart_scene_h), to_db(depart_scene_m), to_db(arrival_hospital_h), to_db(arrival_hospital_m), to_db(handover_to_doctor_h), to_db(handover_to_doctor_m), to_db(return_from_site_h), to_db(return_from_site_m), to_db(return_to_station_h), to_db(return_to_station_m), to_db(transfer1_h), to_db(transfer1_m), to_db(transfer2_h), to_db(transfer2_m), to_db(dispatch_location), to_db(doctor_car_detail) ) cursor.execute(time_insert_query, time_values) logging.info(f"Inserted detailed time record for patient_no: {patient_no}") # 5. 다른 관련 테이블에 placeholder 행 생성 (time 테이블 제외) placeholder_tables = [ "call_received", "primary_triage", "secondary_triage_1", "secondary_triage_2", "secondary_triage_3", "contact_time_T", "memo", "before_arrival", "report" ] for table in placeholder_tables: cursor.execute(f"INSERT INTO {table} (patient_no) VALUES (%s)", (patient_no,)) # patient_info 테이블에 urgency_level을 NULL로 INSERT insert_pi_query = "INSERT INTO patient_info (patient_no, urgency_level) VALUES (%s, NULL)" cursor.execute(insert_pi_query, (patient_no,)) logging.info(f"Created placeholder rows for patient_no: {patient_no} in remaining related tables.") cnx.commit() return {"status": "success", "message": "Time-based record initiated with detailed times and placeholders created."} except HTTPException as http_exc: raise http_exc except Exception as e: cnx.rollback() logging.error(f"Error in initiate_record_from_time for patient_no {patient_no}: {e}", exc_info=True) raise HTTPException(status_code=500, detail=str(e)) finally: cursor.close() cnx.close() # --- time.html: 시간 정보 저장 (UPDATE) --- @app.post("/sos/api/save_all_times") async def save_all_times( patient_no: str = Form(...), call_received_h: Optional[str] = Form(None), call_received_m: Optional[str] = Form(None), dispatch_h: Optional[str] = Form(None), dispatch_m: Optional[str] = Form(None), arrival_on_scene_h: Optional[str] = Form(None), arrival_on_scene_m: Optional[str] = Form(None), patient_contact_h: Optional[str] = Form(None), patient_contact_m: Optional[str] = Form(None), transport_start_h: Optional[str] = Form(None), transport_start_m: Optional[str] = Form(None), patient_loaded_h: Optional[str] = Form(None), patient_loaded_m: Optional[str] = Form(None), depart_scene_h: Optional[str] = Form(None), depart_scene_m: Optional[str] = Form(None), arrival_hospital_h: Optional[str] = Form(None), arrival_hospital_m: Optional[str] = Form(None), handover_to_doctor_h: Optional[str] = Form(None), handover_to_doctor_m: Optional[str] = Form(None), return_from_site_h: Optional[str] = Form(None), return_from_site_m: Optional[str] = Form(None), return_to_station_h: Optional[str] = Form(None), return_to_station_m: Optional[str] = Form(None), transfer1_h: Optional[str] = Form(None), transfer1_m: Optional[str] = Form(None), transfer2_h: Optional[str] = Form(None), transfer2_m: Optional[str] = Form(None), dispatch_location: Optional[str] = Form(None), doctor_car_detail: Optional[str] = Form(None) ): logging.info(f"Updating time data for patient_no: {patient_no}") cnx = get_db_connection() cursor = cnx.cursor() try: query = """ UPDATE time SET call_received_h=%s, call_received_m=%s, dispatch_h=%s, dispatch_m=%s, arrival_on_scene_h=%s, arrival_on_scene_m=%s, patient_contact_h=%s, patient_contact_m=%s, transport_start_h=%s, transport_start_m=%s, patient_loaded_h=%s, patient_loaded_m=%s, depart_scene_h=%s, depart_scene_m=%s, arrival_hospital_h=%s, arrival_hospital_m=%s, handover_to_doctor_h=%s, handover_to_doctor_m=%s, return_from_site_h=%s, return_from_site_m=%s, return_to_station_h=%s, return_to_station_m=%s, transfer1_h=%s, transfer1_m=%s, transfer2_h=%s, transfer2_m=%s, dispatch_location=%s, doctor_car_detail=%s WHERE patient_no = %s """ values = ( call_received_h, call_received_m, dispatch_h, dispatch_m, arrival_on_scene_h, arrival_on_scene_m, patient_contact_h, patient_contact_m, transport_start_h, transport_start_m, patient_loaded_h, patient_loaded_m, depart_scene_h, depart_scene_m, arrival_hospital_h, arrival_hospital_m, handover_to_doctor_h, handover_to_doctor_m, return_from_site_h, return_from_site_m, return_to_station_h, return_to_station_m, transfer1_h, transfer1_m, transfer2_h, transfer2_m, dispatch_location, doctor_car_detail, patient_no ) cursor.execute(query, values) cnx.commit() return {"status": "success", "message": "Time data updated."} except Exception as e: cnx.rollback() logging.error(f"Error updating time data for patient_no {patient_no}: {e}", exc_info=True) raise HTTPException(status_code=500, detail=str(e)) finally: cursor.close() cnx.close() # --- call_received.html: 입전 정보 저장 (UPDATE) --- @app.post("/sos/api/save_call_received") async def save_call_received( patient_no: str = Form(...), call_received_date: Optional[str] = Form(None), call_method: Optional[str] = Form(None), monthly_number: Optional[str] = Form(None), request_location: Optional[str] = Form(None), address: Optional[str] = Form(None), incident_type: Optional[str] = Form(None) ): logging.info(f"Updating call_received data for patient_no: {patient_no}") # --- Placeholder 및 빈 문자열 처리 --- placeholder = "クリックすると選択メニューが表示されます" if incident_type == placeholder: incident_type = None if call_received_date == '': call_received_date = None if call_method == placeholder: call_method = None if request_location == placeholder: request_location = None cnx = get_db_connection() cursor = cnx.cursor() try: query = """ UPDATE call_received SET call_received_date=%s, call_method=%s, monthly_number=%s, request_location=%s, address=%s, incident_type=%s WHERE patient_no = %s """ values = ( call_received_date, call_method, monthly_number, request_location, address, incident_type, patient_no ) cursor.execute(query, values) cnx.commit() return {"status": "success", "message": "Call received data updated."} except Exception as e: cnx.rollback() logging.error(f"Error updating call_received data for patient_no {patient_no}: {e}", exc_info=True) raise HTTPException(status_code=500, detail=str(e)) finally: cursor.close() cnx.close() # --- patient_info.html: 환자 정보 저장 (UPDATE) --- @app.post("/sos/api/save_patient_info") async def save_patient_info( patient_no: str = Form(...), patient_name1: Optional[str] = Form(None), patient_name2: Optional[str] = Form(None), birth_year: Optional[str] = Form(None), birth_month: Optional[str] = Form(None), birth_day: Optional[str] = Form(None), age: Optional[str] = Form(None), gender: Optional[str] = Form(None), occupation: Optional[str] = Form(None), address: Optional[str] = Form(None), phone_number: Optional[str] = Form(None), companion_name: Optional[str] = Form(None), relation: Optional[str] = Form(None), contact_info: Optional[str] = Form(None), urgency_level: Optional[str] = Form(None), onset_time: Optional[str] = Form(None), contact_condition: Optional[str] = Form(None), chief_complaint: Optional[str] = Form(None), symptom_severity: Optional[str] = Form(None), allergies: Optional[str] = Form(None), medication_history: Optional[str] = Form(None), medical_history: Optional[str] = Form(None), last_meal_time: Optional[str] = Form(None), primary_medical_institution: Optional[str] = Form(None) ): logging.info(f"Updating patient_info data for patient_no: {patient_no}") cnx = get_db_connection() cursor = cnx.cursor() try: effective_patient_name2 = patient_name2 if patient_name2 and patient_name2.strip() else "未入力" query = """ UPDATE patient_info SET patient_name1=%s, patient_name2=%s, birth_year=%s, birth_month=%s, birth_day=%s, age=%s, gender=%s, occupation=%s, address=%s, phone_number=%s, companion_name=%s, relation=%s, contact_info=%s, urgency_level=%s, onset_time=%s, contact_condition=%s, chief_complaint=%s, symptom_severity=%s, allergies=%s, medication_history=%s, medical_history=%s, last_meal_time=%s, primary_medical_institution=%s WHERE patient_no = %s """ values = ( patient_name1, effective_patient_name2, birth_year, birth_month, birth_day, age, gender, occupation, address, phone_number, companion_name, relation, contact_info, urgency_level, onset_time, contact_condition, chief_complaint, symptom_severity, allergies, medication_history, medical_history, last_meal_time, primary_medical_institution, patient_no ) cursor.execute(query, values) # list 테이블의 name2 컬럼도 업데이트 update_list_name2_query = "UPDATE list SET name2 = %s WHERE patient_no = %s" cursor.execute(update_list_name2_query, (effective_patient_name2, patient_no)) logging.info(f"Updated 'list' table name2 for patient_no: {patient_no}") cnx.commit() return {"status": "success", "message": "Patient info data updated."} except Exception as e: cnx.rollback() logging.error(f"Error updating patient_info data for patient_no {patient_no}: {e}", exc_info=True) raise HTTPException(status_code=500, detail=str(e)) finally: cursor.close() cnx.close() # --- contact_time_T.html: 접촉 시 정보 저장 (UPDATE) --- @app.post("/sos/api/save_contact_time") async def save_contact_time( patient_no: str = Form(...), contact_time_h: Optional[str] = Form(None), contact_time_m: Optional[str] = Form(None), consciousness_jcs: Optional[str] = Form(None), consciousness_e: Optional[str] = Form(None), consciousness_v: Optional[str] = Form(None), consciousness_m: Optional[str] = Form(None), respiration_rate: Optional[str] = Form(None), respiration_condition: Optional[str] = Form(None), pulse_rate: Optional[str] = Form(None), pulse_1: Optional[str] = Form(None), pulse_2: Optional[str] = Form(None), temperature_L: Optional[str] = Form(None), temperature_R: Optional[str] = Form(None), temperature_text: Optional[str] = Form(None), bp_right_1: Optional[str] = Form(None), bp_right_2: Optional[str] = Form(None), bp_left_1: Optional[str] = Form(None), bp_left_2: Optional[str] = Form(None), spo2_left: Optional[str] = Form(None), spo2_right: Optional[str] = Form(None), oxygen_flow_rate: Optional[str] = Form(None), oxygen_use: Optional[str] = Form(None), ecg_status: Optional[str] = Form(None), auscultation: Optional[str] = Form(None), pupil_right_size: Optional[str] = Form(None), pupil_right_reaction: Optional[str] = Form(None), pupil_left_size: Optional[str] = Form(None), pupil_left_reaction: Optional[str] = Form(None), gaze_deviation: Optional[str] = Form(None), palpebral_conjunctiva: Optional[str] = Form(None), visual_impairment: Optional[str] = Form(None), nystagmus: Optional[str] = Form(None), convulsion: Optional[str] = Form(None), affected_area_condition: Optional[str] = Form(None), skin_condition: Optional[str] = Form(None), paralysis: Optional[str] = Form(None), paralysis_area: Optional[str] = Form(None), vomit: Optional[str] = Form(None), vomit_count: Optional[str] = Form(None), diarrhea: Optional[str] = Form(None), first_aid: Optional[str] = Form(None), first_aid_other: Optional[str] = Form(None), transport_position: Optional[str] = Form(None), adl: Optional[str] = Form(None) ): logging.info(f"Updating contact_time_T data for patient_no: {patient_no}") # --- Placeholder 값 처리 --- placeholder = "クリックすると選択メニューが表示されます" local_vars = locals() fields_to_check = [ 'consciousness_jcs', 'respiration_condition', 'pulse_1', 'pulse_2', 'oxygen_use', 'ecg_status', 'auscultation', 'pupil_right_reaction', 'pupil_left_reaction', 'gaze_deviation', 'palpebral_conjunctiva', 'visual_impairment', 'nystagmus', 'convulsion', 'skin_condition', 'paralysis', 'vomit', 'diarrhea', 'first_aid', 'transport_position', 'adl' ] for field in fields_to_check: if local_vars.get(field) == placeholder: local_vars[field] = None cnx = get_db_connection() cursor = cnx.cursor() try: query = """ UPDATE contact_time_T SET contact_time_h=%s, contact_time_m=%s, consciousness_jcs=%s, consciousness_e=%s, consciousness_v=%s, consciousness_m=%s, respiration_rate=%s, respiration_condition=%s, pulse_rate=%s, pulse_1=%s, pulse_2=%s, temperature_L=%s, temperature_R=%s, temperature_text=%s, bp_right_1=%s, bp_right_2=%s, bp_left_1=%s, bp_left_2=%s, spo2_left=%s, spo2_right=%s, oxygen_flow_rate=%s, oxygen_use=%s, ecg_status=%s, auscultation=%s, pupil_right_size=%s, pupil_right_reaction=%s, pupil_left_size=%s, pupil_left_reaction=%s, gaze_deviation=%s, palpebral_conjunctiva=%s, visual_impairment=%s, nystagmus=%s, convulsion=%s, affected_area_condition=%s, skin_condition=%s, paralysis=%s, paralysis_area=%s, vomit=%s, vomit_count=%s, diarrhea=%s, first_aid=%s, first_aid_other=%s, transport_position=%s, adl=%s WHERE patient_no = %s """ values = ( contact_time_h, contact_time_m, local_vars['consciousness_jcs'], consciousness_e, consciousness_v, consciousness_m, respiration_rate, local_vars['respiration_condition'], pulse_rate, local_vars['pulse_1'], local_vars['pulse_2'], temperature_L, temperature_R, temperature_text, bp_right_1, bp_right_2, bp_left_1, bp_left_2, spo2_left, spo2_right, oxygen_flow_rate, local_vars['oxygen_use'], local_vars['ecg_status'], local_vars['auscultation'], pupil_right_size, local_vars['pupil_right_reaction'], pupil_left_size, local_vars['pupil_left_reaction'], local_vars['gaze_deviation'], local_vars['palpebral_conjunctiva'], local_vars['visual_impairment'], local_vars['nystagmus'], local_vars['convulsion'], affected_area_condition, local_vars['skin_condition'], local_vars['paralysis'], paralysis_area, local_vars['vomit'], vomit_count, local_vars['diarrhea'], local_vars['first_aid'], first_aid_other, local_vars['transport_position'], local_vars['adl'], patient_no ) cursor.execute(query, values) cnx.commit() return {"status": "success", "message": "Contact time data updated."} except Exception as e: cnx.rollback() logging.error(f"Error updating contact_time_T data for patient_no {patient_no}: {e}", exc_info=True) raise HTTPException(status_code=500, detail=str(e)) finally: cursor.close() cnx.close() # --- memo.html: 메모 저장 (UPDATE) --- @app.post("/sos/api/save_memo") async def save_memo( patient_no: str = Form(...), text: Optional[str] = Form(None), images: List[UploadFile] = File([]) ): logging.info(f"Updating memo data for patient_no: {patient_no}") cnx = get_db_connection() cursor = cnx.cursor() try: # 1. 이미지 파일 처리 (첫 번째 이미지만 저장) img_path = None if images: img = images[0] # 첫 번째 이미지만 사용 os.makedirs(UPLOAD_DIR, exist_ok=True) unique_filename = f"{uuid.uuid4()}{os.path.splitext(img.filename)[1]}" img_path = os.path.join(UPLOAD_DIR, unique_filename) with open(img_path, "wb") as buffer: shutil.copyfileobj(img.file, buffer) logging.info(f"Saved memo image to {img_path}") # 2. memo 테이블 업데이트 (올바른 'img' 컬럼 사용) query = "UPDATE memo SET text = %s, img = %s WHERE patient_no = %s" cursor.execute(query, (text, img_path, patient_no)) cnx.commit() return {"status": "success", "message": "Memo data updated."} except Exception as e: cnx.rollback() logging.error(f"Error updating memo data for patient_no {patient_no}: {e}", exc_info=True) raise HTTPException(status_code=500, detail=str(e)) finally: cursor.close() cnx.close() # --- before_arrival.html: 도착 전 정보 저장 (UPDATE) --- @app.post("/sos/api/save_before_arrival") async def save_before_arrival( patient_no: str = Form(...), contact_time_h: Optional[str] = Form(None), contact_time_m: Optional[str] = Form(None), consciousness_jcs: Optional[str] = Form(None), consciousness_e: Optional[str] = Form(None), consciousness_v: Optional[str] = Form(None), consciousness_m: Optional[str] = Form(None), respiration_rate: Optional[str] = Form(None), respiration_condition: Optional[str] = Form(None), pulse_rate: Optional[str] = Form(None), pulse_1: Optional[str] = Form(None), pulse_2: Optional[str] = Form(None), temperature_L: Optional[str] = Form(None), temperature_R: Optional[str] = Form(None), temperature_text: Optional[str] = Form(None), bp_right_1: Optional[str] = Form(None), bp_right_2: Optional[str] = Form(None), bp_left_1: Optional[str] = Form(None), bp_left_2: Optional[str] = Form(None), spo2_left: Optional[str] = Form(None), spo2_right: Optional[str] = Form(None), oxygen_flow_rate: Optional[str] = Form(None), oxygen_use: Optional[str] = Form(None), ecg_status: Optional[str] = Form(None), auscultation: Optional[str] = Form(None) ): logging.info(f"Updating before_arrival data for patient_no: {patient_no}") # --- Placeholder 값 처리 --- placeholder = "クリックすると選択メニューが表示されます" local_vars = locals() fields_to_check = [ 'consciousness_jcs', 'respiration_condition', 'pulse_1', 'pulse_2', 'oxygen_use', 'ecg_status', 'auscultation' ] for field in fields_to_check: if local_vars.get(field) == placeholder: local_vars[field] = None cnx = get_db_connection() cursor = cnx.cursor() try: query = """ UPDATE before_arrival SET contact_time_h=%s, contact_time_m=%s, consciousness_jcs=%s, consciousness_e=%s, consciousness_v=%s, consciousness_m=%s, respiration_rate=%s, respiration_condition=%s, pulse_rate=%s, pulse_1=%s, pulse_2=%s, temperature_L=%s, temperature_R=%s, temperature_text=%s, bp_right_1=%s, bp_right_2=%s, bp_left_1=%s, bp_left_2=%s, spo2_left=%s, spo2_right=%s, oxygen_flow_rate=%s, oxygen_use=%s, ecg_status=%s, auscultation=%s WHERE patient_no = %s """ values = ( contact_time_h, contact_time_m, local_vars['consciousness_jcs'], consciousness_e, consciousness_v, consciousness_m, respiration_rate, local_vars['respiration_condition'], pulse_rate, local_vars['pulse_1'], local_vars['pulse_2'], temperature_L, temperature_R, temperature_text, bp_right_1, bp_right_2, bp_left_1, bp_left_2, spo2_left, spo2_right, oxygen_flow_rate, local_vars['oxygen_use'], local_vars['ecg_status'], local_vars['auscultation'], patient_no ) cursor.execute(query, values) cnx.commit() return {"status": "success", "message": "Before arrival data updated."} except Exception as e: cnx.rollback() logging.error(f"Error updating before_arrival data for patient_no {patient_no}: {e}", exc_info=True) raise HTTPException(status_code=500, detail=str(e)) finally: cursor.close() cnx.close() # --- report.html: 보고서 저장 (UPDATE) --- @app.post("/sos/api/save_report") async def save_report( patient_no: str = Form(...), ambulance_team_name: Optional[str] = Form(None), team_leader_name: Optional[str] = Form(None), diagnosis_name: Optional[str] = Form(None), severity: Optional[str] = Form(None), hospital_selection_reason: Optional[str] = Form(None), distance_station_to_scene_L: Optional[str] = Form(None), distance_station_to_scene_R: Optional[str] = Form(None), distance_scene_to_hospital_L: Optional[str] = Form(None), distance_scene_to_hospital_R: Optional[str] = Form(None), distance_hospital_to_station_L: Optional[str] = Form(None), distance_hospital_to_station_R: Optional[str] = Form(None), distance_station_roundtrip_L: Optional[str] = Form(None), distance_station_roundtrip_R: Optional[str] = Form(None), first_doctor_name: Optional[str] = Form(None), related_organization: Optional[str] = Form(None) ): logging.info(f"Updating report data for patient_no: {patient_no}") cnx = get_db_connection() cursor = cnx.cursor() try: query = """ UPDATE report SET ambulance_team_name=%s, team_leader_name=%s, diagnosis_name=%s, severity=%s, hospital_selection_reason=%s, distance_station_to_scene_L=%s, distance_station_to_scene_R=%s, distance_scene_to_hospital_L=%s, distance_scene_to_hospital_R=%s, distance_hospital_to_station_L=%s, distance_hospital_to_station_R=%s, distance_station_roundtrip_L=%s, distance_station_roundtrip_R=%s, first_doctor_name=%s, related_organization=%s WHERE patient_no = %s """ values = ( ambulance_team_name, team_leader_name, diagnosis_name, severity, hospital_selection_reason, distance_station_to_scene_L, distance_station_to_scene_R, distance_scene_to_hospital_L, distance_scene_to_hospital_R, distance_hospital_to_station_L, distance_hospital_to_station_R, distance_station_roundtrip_L, distance_station_roundtrip_R, first_doctor_name, related_organization, patient_no ) cursor.execute(query, values) cnx.commit() return {"status": "success", "message": "Report data updated."} except Exception as e: cnx.rollback() logging.error(f"Error updating report data for patient_no {patient_no}: {e}", exc_info=True) raise HTTPException(status_code=500, detail=str(e)) finally: cursor.close() cnx.close() # --- three_page.html: 1차 트리아지 정보 저장 (UPDATE) --- @app.post("/sos/api/save_primary_triage") async def save_primary_triage(data: dict): patient_no = data.get("patient_no") urgency_level = data.get("urgency_level") if not patient_no: raise HTTPException(status_code=400, detail="patient_no is required.") logging.info(f"Updating primary_triage for patient_no: {patient_no}") cnx = get_db_connection() cursor = cnx.cursor() try: query = "UPDATE primary_triage SET urgency_level = %s WHERE patient_no = %s" cursor.execute(query, (urgency_level, patient_no)) cnx.commit() return {"status": "success", "message": "Primary triage data updated."} except Exception as e: cnx.rollback() logging.error(f"Error updating primary_triage for patient_no {patient_no}: {e}", exc_info=True) raise HTTPException(status_code=500, detail=str(e)) finally: cursor.close() cnx.close() # --- four_page.html: 2차 트리아지① 정보 저장 (UPDATE) --- @app.post("/sos/api/save_secondary_triage_1") async def save_secondary_triage_1( patient_no: str = Form(...), practitioner_name1: Optional[str] = Form(None), practitioner_name2: Optional[str] = Form(None), treatment_time: Optional[str] = Form(None), respiration: Optional[str] = Form(None), bp_right_1: Optional[str] = Form(None), bp_right_2: Optional[str] = Form(None), bp_left_1: Optional[str] = Form(None), bp_left_2: Optional[str] = Form(None), pulse_rate: Optional[str] = Form(None), consciousness_jcs: Optional[str] = Form(None), consciousness_e: Optional[str] = Form(None), consciousness_v: Optional[str] = Form(None), consciousness_m: Optional[str] = Form(None), treatment_details: Optional[str] = Form(None), urgency_level: Optional[str] = Form(None), img_upload: Optional[UploadFile] = File(None) ): logging.info(f"Updating secondary_triage_1 for patient_no: {patient_no}") img_path = None if img_upload: os.makedirs(UPLOAD_DIR, exist_ok=True) unique_filename = f"{uuid.uuid4()}{os.path.splitext(img_upload.filename)[1]}" img_path = os.path.join(UPLOAD_DIR, unique_filename) with open(img_path, "wb") as buffer: shutil.copyfileobj(img_upload.file, buffer) logging.info(f"Saved secondary_triage_1 image to {img_path}") cnx = get_db_connection() cursor = cnx.cursor() try: query = """ UPDATE secondary_triage_1 SET practitioner_name1=%s, practitioner_name2=%s, treatment_time=%s, respiration=%s, bp_right_1=%s, bp_right_2=%s, bp_left_1=%s, bp_left_2=%s, pulse_rate=%s, consciousness_jcs=%s, consciousness_e=%s, consciousness_v=%s, consciousness_m=%s, treatment_details=%s, urgency_level=%s, img=%s WHERE patient_no = %s """ values = ( practitioner_name1, practitioner_name2, treatment_time, respiration, bp_right_1, bp_right_2, bp_left_1, bp_left_2, pulse_rate, consciousness_jcs, consciousness_e, consciousness_v, consciousness_m, treatment_details, urgency_level, img_path, patient_no ) cursor.execute(query, values) cnx.commit() return {"status": "success", "message": "Secondary triage 1 data updated."} except Exception as e: cnx.rollback() logging.error(f"Error updating secondary_triage_1 for patient_no {patient_no}: {e}", exc_info=True) raise HTTPException(status_code=500, detail=str(e)) finally: cursor.close() cnx.close() # --- five_page.html: 2차 트리아지② 정보 저장 (UPDATE) --- @app.post("/sos/api/save_secondary_triage_2") async def save_secondary_triage_2( patient_no: str = Form(...), practitioner_name1: Optional[str] = Form(None), practitioner_name2: Optional[str] = Form(None), treatment_time: Optional[str] = Form(None), respiration: Optional[str] = Form(None), bp_right_1: Optional[str] = Form(None), bp_right_2: Optional[str] = Form(None), bp_left_1: Optional[str] = Form(None), bp_left_2: Optional[str] = Form(None), pulse_rate: Optional[str] = Form(None), consciousness_jcs: Optional[str] = Form(None), consciousness_e: Optional[str] = Form(None), consciousness_v: Optional[str] = Form(None), consciousness_m: Optional[str] = Form(None), treatment_details: Optional[str] = Form(None), urgency_level: Optional[str] = Form(None), img_upload: Optional[UploadFile] = File(None) ): logging.info(f"Updating secondary_triage_2 for patient_no: {patient_no}") img_path = None if img_upload: os.makedirs(UPLOAD_DIR, exist_ok=True) unique_filename = f"{uuid.uuid4()}{os.path.splitext(img_upload.filename)[1]}" img_path = os.path.join(UPLOAD_DIR, unique_filename) with open(img_path, "wb") as buffer: shutil.copyfileobj(img_upload.file, buffer) logging.info(f"Saved secondary_triage_2 image to {img_path}") cnx = get_db_connection() cursor = cnx.cursor() try: query = """ UPDATE secondary_triage_2 SET practitioner_name1=%s, practitioner_name2=%s, treatment_time=%s, respiration=%s, bp_right_1=%s, bp_right_2=%s, bp_left_1=%s, bp_left_2=%s, pulse_rate=%s, consciousness_jcs=%s, consciousness_e=%s, consciousness_v=%s, consciousness_m=%s, treatment_details=%s, urgency_level=%s, img=%s WHERE patient_no = %s """ values = ( practitioner_name1, practitioner_name2, treatment_time, respiration, bp_right_1, bp_right_2, bp_left_1, bp_left_2, pulse_rate, consciousness_jcs, consciousness_e, consciousness_v, consciousness_m, treatment_details, urgency_level, img_path, patient_no ) cursor.execute(query, values) cnx.commit() return {"status": "success", "message": "Secondary triage 2 data updated."} except Exception as e: cnx.rollback() logging.error(f"Error updating secondary_triage_2 for patient_no {patient_no}: {e}", exc_info=True) raise HTTPException(status_code=500, detail=str(e)) finally: cursor.close() cnx.close() # --- six_page.html: 2차 트리아지③ 정보 저장 (UPDATE) --- @app.post("/sos/api/save_secondary_triage_3") async def save_secondary_triage_3( patient_no: str = Form(...), practitioner_name1: Optional[str] = Form(None), practitioner_name2: Optional[str] = Form(None), treatment_time: Optional[str] = Form(None), respiration: Optional[str] = Form(None), bp_right_1: Optional[str] = Form(None), bp_right_2: Optional[str] = Form(None), bp_left_1: Optional[str] = Form(None), bp_left_2: Optional[str] = Form(None), pulse_rate: Optional[str] = Form(None), consciousness_jcs: Optional[str] = Form(None), consciousness_e: Optional[str] = Form(None), consciousness_v: Optional[str] = Form(None), consciousness_m: Optional[str] = Form(None), treatment_details: Optional[str] = Form(None), urgency_level: Optional[str] = Form(None), img_upload: Optional[UploadFile] = File(None) ): logging.info(f"Updating secondary_triage_3 for patient_no: {patient_no}") img_path = None if img_upload: os.makedirs(UPLOAD_DIR, exist_ok=True) unique_filename = f"{uuid.uuid4()}{os.path.splitext(img_upload.filename)[1]}" img_path = os.path.join(UPLOAD_DIR, unique_filename) with open(img_path, "wb") as buffer: shutil.copyfileobj(img_upload.file, buffer) logging.info(f"Saved secondary_triage_3 image to {img_path}") cnx = get_db_connection() cursor = cnx.cursor() try: query = """ UPDATE secondary_triage_3 SET practitioner_name1=%s, practitioner_name2=%s, treatment_time=%s, respiration=%s, bp_right_1=%s, bp_right_2=%s, bp_left_1=%s, bp_left_2=%s, pulse_rate=%s, consciousness_jcs=%s, consciousness_e=%s, consciousness_v=%s, consciousness_m=%s, treatment_details=%s, urgency_level=%s, img=%s WHERE patient_no = %s """ values = ( practitioner_name1, practitioner_name2, treatment_time, respiration, bp_right_1, bp_right_2, bp_left_1, bp_left_2, pulse_rate, consciousness_jcs, consciousness_e, consciousness_v, consciousness_m, treatment_details, urgency_level, img_path, patient_no ) cursor.execute(query, values) cnx.commit() return {"status": "success", "message": "Secondary triage 3 data updated."} except Exception as e: cnx.rollback() logging.error(f"Error updating secondary_triage_3 for patient_no {patient_no}: {e}", exc_info=True) raise HTTPException(status_code=500, detail=str(e)) finally: cursor.close() cnx.close() # --- Pydantic 모델 정의 --- class Incident(BaseModel): patient_No: str date_time: Optional[datetime] = None day_of_week: Optional[str] = None patient_name: Optional[str] = None primary_urgency: Optional[str] = None secondary_1_urgency: Optional[str] = None secondary_2_urgency: Optional[str] = None secondary_3_urgency: Optional[str] = None # --- 傷病者搬送通知書를 위한 환자 목록 API --- @app.get("/sos/api/transport_patients", response_model=List[Incident]) async def get_patient_list_for_transport(): cnx = get_db_connection() cursor = cnx.cursor(dictionary=True) try: # patient 테이블을 기준으로 모든 트리아지 테이블을 LEFT JOIN query = """ SELECT p.No as patient_No, p.date_time, p.day_of_week, p.name2 as patient_name, pt.urgency_level as primary_urgency, st1.urgency_level as secondary_1_urgency, st2.urgency_level as secondary_2_urgency, st3.urgency_level as secondary_3_urgency FROM patient p LEFT JOIN primary_triage pt ON p.No = pt.patient_no LEFT JOIN secondary_triage_1 st1 ON p.No = st1.patient_no LEFT JOIN secondary_triage_2 st2 ON p.No = st2.patient_no LEFT JOIN secondary_triage_3 st3 ON p.No = st3.patient_no WHERE p.name2 IS NOT NULL ORDER BY p.id DESC; """ cursor.execute(query) results = cursor.fetchall() return results except Exception as e: logging.error(f"Error fetching patient list for transport: {e}", exc_info=True) raise HTTPException(status_code=500, detail=f"Database error: {str(e)}") finally: cursor.close() cnx.close() # --- 목록 불러오기 API (수정됨) --- class Incident(BaseModel): patient_No: str date_time: Optional[datetime] = None day_of_week: Optional[str] = None patient_name: Optional[str] = None primary_urgency: Optional[str] = None secondary_1_urgency: Optional[str] = None secondary_2_urgency: Optional[str] = None secondary_3_urgency: Optional[str] = None @app.get("/sos/api/incidents", response_model=List[Incident]) async def get_incident_list(): cnx = get_db_connection() cursor = cnx.cursor(dictionary=True) try: # 'list' 테이블에서 데이터를 조회합니다. query = """ SELECT l.patient_no AS patient_No, l.date_time, l.day_of_week, l.name2 AS patient_name, pt.urgency_level AS primary_urgency, st1.urgency_level AS secondary_1_urgency, st2.urgency_level AS secondary_2_urgency, st3.urgency_level AS secondary_3_urgency FROM list l LEFT JOIN primary_triage pt ON l.patient_no = pt.patient_no LEFT JOIN secondary_triage_1 st1 ON l.patient_no = st1.patient_no LEFT JOIN secondary_triage_2 st2 ON l.patient_no = st2.patient_no LEFT JOIN secondary_triage_3 st3 ON l.patient_no = st3.patient_no ORDER BY l.id DESC; """ cursor.execute(query) results = cursor.fetchall() return results except Exception as e: logging.error(f"Error fetching incident list from 'list' table: {e}", exc_info=True) raise HTTPException(status_code=500, detail=f"Database error: {str(e)}") finally: cursor.close() cnx.close() # --- 보고서 생성 API --- import subprocess def convert_excel_to_pdf(excel_path, output_dir): """ LibreOffice를 사용하여 Excel 파일을 PDF로 변환합니다. """ try: command = [ "libreoffice", "--headless", "--convert-to", "pdf", "--outdir", output_dir, excel_path ] result = subprocess.run(command, capture_output=True, text=True, check=True) print("LibreOffice PDF conversion stdout:", result.stdout) pdf_filename = os.path.splitext(os.path.basename(excel_path))[0] + ".pdf" return os.path.join(output_dir, pdf_filename) except FileNotFoundError: print("ERROR: 'libreoffice' command not found. Is LibreOffice installed and in the system's PATH?") raise except subprocess.CalledProcessError as e: print(f"ERROR: LibreOffice conversion failed. Return code: {e.returncode}") print("Stderr:", e.stderr) raise except Exception as e: print(f"An unexpected error occurred during PDF conversion: {e}") raise # ---[추가]: 보고서 PDF 존재 여부 확인용 헬퍼 --- def report_exists(prefix: str, patient_no: str) -> bool: """ /home/air/sos/templates/ 폴더 안에 prefix_patient_no.pdf (예: reportA_3.pdf) 가 존재하는지 확인 """ filename = f"{prefix}_{patient_no}.pdf" path = os.path.join(TEMPLATE_DIR, filename) return os.path.exists(path) # ---[추가]: 보고서 존재 여부 API --- @app.get("/sos/api/report_status/{patient_no}") async def get_report_status(patient_no: str): """ 특정 patient_no 에 대해 A/B/C/triage 보고서 PDF 존재 여부를 반환 예: { "A": true, "B": false, "C": true, "triage": false } """ return { "A": report_exists("reportA", patient_no), "B": report_exists("reportB", patient_no), "C": report_exists("reportC", patient_no), "triage": report_exists("report_triage", patient_no), } @app.get("/sos/api/generate_report/{patient_no}") async def generate_report(patient_no: str): cnx = get_db_connection() cursor = cnx.cursor(dictionary=True) try: # 1. 현재 DB 스키마에 맞게 모든 테이블을 JOIN하여 데이터 조회 query = """ SELECT p.*, t.call_received_h, t.call_received_m, t.dispatch_h, t.dispatch_m, t.arrival_on_scene_h, t.arrival_on_scene_m, t.patient_contact_h, t.patient_contact_m, t.transport_start_h, t.transport_start_m, t.patient_loaded_h, t.patient_loaded_m, t.depart_scene_h, t.depart_scene_m, t.arrival_hospital_h, t.arrival_hospital_m, t.handover_to_doctor_h, t.handover_to_doctor_m, t.return_from_site_h, t.return_from_site_m, t.return_to_station_h, t.return_to_station_m, t.transfer1_h, t.transfer1_m, t.transfer2_h, t.transfer2_m, t.dispatch_location, t.doctor_car_detail, cr.call_received_date, cr.call_method, cr.monthly_number, cr.request_location, cr.address as cr_address, cr.incident_type, pi.patient_name1, pi.patient_name2, pi.birth_year, pi.birth_month, pi.birth_day, pi.age as pi_age, pi.gender as pi_gender, pi.occupation, pi.address as pi_address, pi.phone_number as pi_phone_number, pi.companion_name, pi.relation, pi.contact_info, pi.urgency_level as pi_urgency_level, pi.onset_time, pi.contact_condition, pi.chief_complaint, pi.symptom_severity, pi.allergies, pi.medication_history, pi.medical_history, pi.last_meal_time, pi.primary_medical_institution, pt.urgency_level as primary_triage_urgency_level, st1.practitioner_name1 as st1_practitioner_name1, st1.practitioner_name2 as st1_practitioner_name2, st1.treatment_time as st1_treatment_time, st1.respiration as st1_respiration, st1.bp_right_1 as st1_bp_right_1, st1.bp_right_2 as st1_bp_right_2, st1.bp_left_1 as st1_bp_left_1, st1.bp_left_2 as st1_bp_left_2, st1.pulse_rate as st1_pulse_rate, st1.consciousness_jcs as st1_consciousness_jcs, st1.consciousness_e as st1_consciousness_e, st1.consciousness_v as st1_consciousness_v, st1.consciousness_m as st1_consciousness_m, st1.treatment_details as st1_treatment_details, st1.urgency_level as st1_urgency_level, st1.img as st1_img, st2.practitioner_name1 as st2_practitioner_name1, st2.practitioner_name2 as st2_practitioner_name2, st2.treatment_time as st2_treatment_time, st2.respiration as st2_respiration, st2.bp_right_1 as st2_bp_right_1, st2.bp_right_2 as st2_bp_right_2, st2.bp_left_1 as st2_bp_left_1, st2.bp_left_2 as st2_bp_left_2, st2.pulse_rate as st2_pulse_rate, st2.consciousness_jcs as st2_consciousness_jcs, st2.consciousness_e as st2_consciousness_e, st2.consciousness_v as st2_consciousness_v, st2.consciousness_m as st2_consciousness_m, st2.treatment_details as st2_treatment_details, st2.urgency_level as st2_urgency_level, st2.img as st2_img, st3.practitioner_name1 as st3_practitioner_name1, st3.practitioner_name2 as st3_practitioner_name2, st3.treatment_time as st3_treatment_time, st3.respiration as st3_respiration, st3.bp_right_1 as st3_bp_right_1, st3.bp_right_2 as st3_bp_right_2, st3.bp_left_1 as st3_bp_left_1, st3.bp_left_2 as st3_bp_left_2, st3.pulse_rate as st3_pulse_rate, st3.consciousness_jcs as st3_consciousness_jcs, st3.consciousness_e as st3_consciousness_e, st3.consciousness_v as st3_consciousness_v, st3.consciousness_m as st3_consciousness_m, st3.treatment_details as st3_treatment_details, st3.urgency_level as st3_urgency_level, st3.img as st3_img, ct.contact_time_h as ct_contact_time_h, ct.contact_time_m as ct_contact_time_m, ct.consciousness_jcs as ct_consciousness_jcs, ct.consciousness_e as ct_consciousness_e, ct.consciousness_v as ct_consciousness_v, ct.consciousness_m as ct_consciousness_m, ct.respiration_rate as ct_respiration_rate, ct.respiration_condition as ct_respiration_condition, ct.pulse_rate as ct_pulse_rate, ct.pulse_1 as ct_pulse_1, ct.pulse_2 as ct_pulse_2, ct.temperature_L as ct_temperature_L, ct.temperature_R as ct_temperature_R, ct.temperature_text as ct_temperature_text, ct.bp_right_1 as ct_bp_right_1, ct.bp_right_2 as ct_bp_right_2, ct.bp_left_1 as ct_bp_left_1, ct.bp_left_2 as ct_bp_left_2, ct.spo2_left as ct_spo2_left, ct.spo2_right as ct_spo2_right, ct.oxygen_flow_rate as ct_oxygen_flow_rate, ct.oxygen_use as ct_oxygen_use, ct.ecg_status as ct_ecg_status, ct.auscultation as ct_auscultation, ct.pupil_right_size as ct_pupil_right_size, ct.pupil_right_reaction as ct_pupil_right_reaction, ct.pupil_left_size as ct_pupil_left_size, ct.pupil_left_reaction as ct_pupil_left_reaction, ct.gaze_deviation as ct_gaze_deviation, ct.palpebral_conjunctiva as ct_palpebral_conjunctiva, ct.visual_impairment as ct_visual_impairment, ct.nystagmus as ct_nystagmus, ct.convulsion as ct_convulsion, ct.affected_area_condition as ct_affected_area_condition, ct.skin_condition as ct_skin_condition, ct.paralysis as ct_paralysis, ct.paralysis_area as ct_paralysis_area, ct.vomit as ct_vomit, ct.vomit_count as ct_vomit_count, ct.diarrhea as ct_diarrhea, ct.first_aid as ct_first_aid, ct.first_aid_other as ct_first_aid_other, ct.transport_position as ct_transport_position, ct.adl as ct_adl, m.text as memo_text, m.img as memo_img, ba.contact_time_h as ba_contact_time_h, ba.contact_time_m as ba_contact_time_m, ba.consciousness_jcs as ba_consciousness_jcs, ba.consciousness_e as ba_consciousness_e, ba.consciousness_v as ba_consciousness_v, ba.consciousness_m as ba_consciousness_m, ba.respiration_rate as ba_respiration_rate, ba.respiration_condition as ba_respiration_condition, ba.pulse_rate as ba_pulse_rate, ba.pulse_1 as ba_pulse_1, ba.pulse_2 as ba_pulse_2, ba.temperature_L as ba_temperature_L, ba.temperature_R as ba_temperature_R, ba.temperature_text as ba_temperature_text, ba.bp_right_1 as ba_bp_right_1, ba.bp_right_2 as ba_bp_right_2, ba.bp_left_1 as ba_bp_left_1, ba.bp_left_2 as ba_bp_left_2, ba.spo2_left as ba_spo2_left, ba.spo2_right as ba_spo2_right, ba.oxygen_flow_rate as ba_oxygen_flow_rate, ba.oxygen_use as ba_oxygen_use, ba.ecg_status as ba_ecg_status, ba.auscultation as ba_auscultation, r.ambulance_team_name, r.team_leader_name, r.diagnosis_name, r.severity as report_severity, r.hospital_selection_reason, r.distance_station_to_scene_L, r.distance_station_to_scene_R, r.distance_scene_to_hospital_L, r.distance_scene_to_hospital_R, r.distance_hospital_to_station_L, r.distance_hospital_to_station_R, r.distance_station_roundtrip_L, r.distance_station_roundtrip_R, r.first_doctor_name, r.related_organization FROM patient p LEFT JOIN time t ON p.No = t.patient_no LEFT JOIN call_received cr ON p.No = cr.patient_no LEFT JOIN patient_info pi ON p.No = pi.patient_no LEFT JOIN primary_triage pt ON p.No = pt.patient_no LEFT JOIN secondary_triage_1 st1 ON p.No = st1.patient_no LEFT JOIN secondary_triage_2 st2 ON p.No = st2.patient_no LEFT JOIN secondary_triage_3 st3 ON p.No = st3.patient_no LEFT JOIN contact_time_T ct ON p.No = ct.patient_no LEFT JOIN memo m ON p.No = m.patient_no LEFT JOIN before_arrival ba ON p.No = ba.patient_no LEFT JOIN report r ON p.No = r.patient_no WHERE p.No = %s """ cursor.execute(query, (patient_no,)) data = cursor.fetchone() if not data: raise HTTPException(status_code=404, detail="Report data not found for the given patient_no.") except Exception as e: raise HTTPException(status_code=500, detail=f"Database error: {str(e)}") finally: cursor.close() cnx.close() # --- 2. 엑셀 파일에 데이터 채우기 (백업 파일 로직 기반) --- try: template_path = os.path.join(TEMPLATE_DIR, "20251112_A.xlsx") output_filename = f"reportA_{patient_no}.xlsx" excel_output_path = os.path.join(TEMPLATE_DIR, output_filename) workbook = openpyxl.load_workbook(template_path) sheet = workbook.active # --- 디버깅용 헬퍼 함수 --- def write_to_cell(cell, value): try: # None 값을 빈 문자열로 처리하지 않도록 명시적으로 확인 if value is None: # sheet[cell] = None 또는 sheet[cell] = "" 둘 다 가능 # openpyxl에서는 None으로 설정하는 것이 더 명확할 수 있음 sheet[cell] = None else: sheet[cell] = value except Exception as e: logging.error(f"---!!! CRITICAL ERROR WRITING TO CELL: {cell} !!!---") logging.error(f" VALUE: {value} (Type: {type(value)})") logging.error(f" ERROR: {e}") raise # 2.1. 1페이지(time) 데이터 채우기 time_fields = ['call_received_h', 'call_received_m', 'dispatch_h', 'dispatch_m', 'arrival_on_scene_h', 'arrival_on_scene_m', 'patient_contact_h', 'patient_contact_m', 'transport_start_h', 'transport_start_m', 'patient_loaded_h', 'patient_loaded_m', 'depart_scene_h', 'depart_scene_m', 'arrival_hospital_h', 'arrival_hospital_m', 'handover_to_doctor_h', 'handover_to_doctor_m', 'return_from_site_h', 'return_from_site_m', 'dispatch_location', 'doctor_car_detail'] time_cells = ['A8', 'D8', 'F8', 'I8', 'K8', 'N8', 'P8', 'S8', 'U8', 'X8', 'Z8', 'AC8', 'AE8', 'AH8', 'AJ8', 'AM8', 'AO8', 'AR8', 'AT8', 'AW8', 'H10', 'AJ10'] for i, field in enumerate(time_fields): if field in data and data[field] is not None: value = data[field] if '_h' in field or '_m' in field: try: value = int(value) except (ValueError, TypeError): pass write_to_cell(time_cells[i], value) # 2.2. 2페이지(call_received) 데이터 채우기 if data.get('call_received_date'): try: date_obj = datetime.strptime(data['call_received_date'], '%Y-%m-%d') write_to_cell('F3', date_obj.month) write_to_cell('K3', date_obj.day) weekdays = ["月", "火", "水", "木", "金", "土", "日"] day_index = date_obj.weekday() write_to_cell('Q3', weekdays[day_index]) except (ValueError, TypeError): pass if data.get('monthly_number'): write_to_cell('AT3', data['monthly_number']) if data.get('request_location'): write_to_cell('F4', data['request_location']) if data.get('incident_type'): write_to_cell('F6', data['incident_type']) if data.get('call_method'): write_to_cell('Z3', data['call_method']) if data.get('cr_address'): write_to_cell('P4', data['cr_address']) # 2.3. 3페이지(patient_info) 데이터 채우기 patient_info_map = { 'patient_name1': 'F11', 'patient_name2': 'F12', 'birth_year': 'Z11', 'birth_month': 'AE11', 'birth_day': 'AJ11', 'pi_age': 'AO11', 'pi_gender': 'AT11', 'pi_phone_number': 'F15', 'companion_name': 'F16', 'relation': 'P16', 'contact_info': 'Z16', 'pi_urgency_level': 'AT16', 'onset_time': 'K20', 'chief_complaint': 'K21', 'symptom_severity': 'AO21', 'allergies': 'AO35', 'medication_history': 'K36', 'medical_history': 'AO36', 'last_meal_time': 'K37', 'primary_medical_institution': 'AO37' } for field, cell in patient_info_map.items(): if data.get(field) is not None: write_to_cell(cell, data[field]) if data.get('pi_address'): write_to_cell('F14', data['pi_address']) if data.get('contact_condition'): write_to_cell('AO20', data['contact_condition']) # 2.4. 4페이지(contact_time) 데이터 채우기 contact_time_map = { 'ct_contact_time_h': 'C24', 'ct_contact_time_m': 'F24', 'ct_consciousness_jcs': 'P24', 'ct_consciousness_e': 'M26', 'ct_consciousness_v': 'P26', 'ct_consciousness_m': 'S26', 'ct_respiration_rate': 'U24', 'ct_respiration_condition': 'U26', 'ct_pulse_rate': 'AE25', 'ct_pulse_1': 'AE24', 'ct_pulse_2': 'AE26', 'ct_temperature_text': 'AO26', 'ct_bp_right_1': 'BB24', 'ct_bp_right_2': 'BE24', 'ct_bp_left_1': 'BB25', 'ct_bp_left_2': 'BE25', 'ct_spo2_left': 'E27', 'ct_spo2_right': 'K27', 'ct_oxygen_use': 'S27', 'ct_oxygen_flow_rate': 'Y27', 'ct_ecg_status': 'AH27', 'ct_auscultation': 'AW27', 'ct_pupil_right_size': 'F29', 'ct_pupil_right_reaction': 'K29', 'ct_pupil_left_size': 'F30', 'ct_pupil_left_reaction': 'K30', 'ct_gaze_deviation': 'Q29', 'ct_palpebral_conjunctiva': 'Z29', 'ct_visual_impairment': 'Q30', 'ct_nystagmus': 'Z30', 'ct_convulsion': 'AH29', 'ct_affected_area_condition': 'AW29', 'ct_skin_condition': 'D31', 'ct_paralysis': 'S31', 'ct_paralysis_area': 'X31', 'ct_vomit': 'AH31', 'ct_vomit_count': 'AP31', 'ct_diarrhea': 'AW31', 'ct_transport_position': 'K34', 'ct_adl': 'K35' } for field, cell in contact_time_map.items(): if data.get(field) is not None: write_to_cell(cell, data[field]) if data.get('ct_first_aid') is not None: write_to_cell('K33', data['ct_first_aid']) if data.get('ct_first_aid_other') and data.get('ct_first_aid_other').strip(): write_to_cell('W33', data['ct_first_aid_other']) if data.get('ct_temperature_L') is not None and data.get('ct_temperature_R') is not None: write_to_cell('AO24', f"{data['ct_temperature_L']}.{data['ct_temperature_R']}") # 2.5. 6페이지(before_arrival) 데이터 채우기 before_arrival_map = { 'ba_contact_time_h': 'C40', 'ba_contact_time_m': 'F40', 'ba_consciousness_jcs': 'P40', 'ba_consciousness_e': 'M42', 'ba_consciousness_v': 'P42', 'ba_consciousness_m': 'S42', 'ba_respiration_rate': 'U40', 'ba_respiration_condition': 'U42', 'ba_pulse_rate': 'AE41', 'ba_pulse_1': 'AE40', 'ba_pulse_2': 'AE42', 'ba_temperature_text': 'AO42', 'ba_bp_right_1': 'BB40', 'ba_bp_right_2': 'BE40', 'ba_bp_left_1': 'BB41', 'ba_bp_left_2': 'BE41', 'ba_spo2_left': 'E43', 'ba_spo2_right': 'K43', 'ba_oxygen_flow_rate': 'Y43', 'ba_oxygen_use': 'S43', 'ba_ecg_status': 'AH43', 'ba_auscultation': 'AW43' } for field, cell in before_arrival_map.items(): if data.get(field) is not None: write_to_cell(cell, data[field]) if data.get('ba_temperature_L') is not None and data.get('ba_temperature_R') is not None: write_to_cell('AO40', f"{data['ba_temperature_L']}.{data['ba_temperature_R']}") # 2.6. 7페이지(report) 데이터 채우기 report_map = { 'diagnosis_name': 'Z15', 'report_severity': 'AT15', 'hospital_selection_reason': 'A18', 'first_doctor_name': 'AT17', 'related_organization': 'AT18' } for field, cell in report_map.items(): if data.get(field) is not None: write_to_cell(cell, data[field]) # 2.7. memo 테이블 데이터 채우기 if data.get('memo_text') is not None: write_to_cell('A45', data['memo_text']) # memo_img를 실제 이미지로 삽입 if data.get('memo_img'): img_path = data['memo_img'] if os.path.exists(img_path): try: img = Image(img_path) # 이미지 크기를 너비 150으로 고정하고, 비율에 맞게 높이 조절 if img.width > 0: img.height = img.height * (150 / img.width) img.width = 150 sheet.add_image(img, 'A47') except Exception as e: logging.warning(f"Could not embed image {img_path} in Excel: {e}") # 실패 시 이미지 경로라도 기록 write_to_cell('A47', img_path) else: # 경로에 파일이 없을 경우 write_to_cell('A47', f"Image not found at: {img_path}") def combine_and_write(l_field, r_field, cell): if data.get(l_field) is not None and data.get(r_field) is not None: write_to_cell(cell, f"{data[l_field]}.{data[r_field]}") combine_and_write('distance_station_to_scene_L', 'distance_station_to_scene_R', 'U18') combine_and_write('distance_scene_to_hospital_L', 'distance_scene_to_hospital_R', 'Z18') combine_and_write('distance_hospital_to_station_L', 'distance_hospital_to_station_R', 'AE18') combine_and_write('distance_station_roundtrip_L', 'distance_station_roundtrip_R', 'AJ18') workbook.save(excel_output_path) # --- 3. PDF로 변환 --- pdf_path = convert_excel_to_pdf(excel_output_path, TEMPLATE_DIR) return {"status": "success", "message": "Report generated and converted to PDF successfully.", "excel_path": excel_output_path, "pdf_path": pdf_path} except FileNotFoundError: raise HTTPException(status_code=404, detail=f"Template file not found at {template_path}") except Exception as e: # The detailed error is now logged by the write_to_cell helper raise HTTPException(status_code=500, detail=f"File processing or PDF conversion error: {str(e)}") # --- 주소 불러오기 API --- @app.get("/sos/api/address/{patient_no}") async def get_address(patient_no: str): logging.info(f"Fetching address for patient_no: {patient_no}") cnx = get_db_connection() cursor = cnx.cursor(dictionary=True) try: query = "SELECT address FROM call_received WHERE patient_no = %s" cursor.execute(query, (patient_no,)) result = cursor.fetchone() if result: return {"status": "success", "address": result["address"]} raise HTTPException(status_code=404, detail="Address not found for the given patient_no.") except Exception as e: logging.error(f"Error fetching address for patient_no {patient_no}: {e}", exc_info=True) raise HTTPException(status_code=500, detail=str(e)) finally: cursor.close() cnx.close() @app.get("/sos/api/generate_report_b/{patient_no}") async def generate_report_b(patient_no: str): cnx = get_db_connection() cursor = cnx.cursor(dictionary=True) try: # 1. DB 데이터 조회 (기존과 동일) query = """ SELECT p.*, t.call_received_h, t.call_received_m, t.dispatch_h, t.dispatch_m, t.arrival_on_scene_h, t.arrival_on_scene_m, t.patient_contact_h, t.patient_contact_m, t.transport_start_h, t.transport_start_m, t.patient_loaded_h, t.patient_loaded_m, t.depart_scene_h, t.depart_scene_m, t.arrival_hospital_h, t.arrival_hospital_m, t.handover_to_doctor_h, t.handover_to_doctor_m, t.return_from_site_h, t.return_from_site_m, t.return_to_station_h, t.return_to_station_m, t.transfer1_h, t.transfer1_m, t.transfer2_h, t.transfer2_m, t.dispatch_location, t.doctor_car_detail, cr.call_received_date, cr.call_method, cr.monthly_number, cr.request_location, cr.address as cr_address, cr.incident_type, pi.patient_name1, pi.patient_name2, pi.birth_year, pi.birth_month, pi.birth_day, pi.age as pi_age, pi.gender as pi_gender, pi.occupation, pi.address as pi_address, pi.phone_number as pi_phone_number, pi.companion_name, pi.relation, pi.contact_info, pi.urgency_level as pi_urgency_level, pi.onset_time, pi.contact_condition, pi.chief_complaint, pi.symptom_severity, pi.allergies, pi.medication_history, pi.medical_history, pi.last_meal_time, pi.primary_medical_institution, pt.urgency_level as primary_triage_urgency_level, st1.practitioner_name1 as st1_practitioner_name1, st1.practitioner_name2 as st1_practitioner_name2, st1.treatment_time as st1_treatment_time, st1.respiration as st1_respiration, st1.bp_right_1 as st1_bp_right_1, st1.bp_right_2 as st1_bp_right_2, st1.bp_left_1 as st1_bp_left_1, st1.bp_left_2 as st1_bp_left_2, st1.pulse_rate as st1_pulse_rate, st1.consciousness_jcs as st1_consciousness_jcs, st1.consciousness_e as st1_consciousness_e, st1.consciousness_v as st1_consciousness_v, st1.consciousness_m as st1_consciousness_m, st1.treatment_details as st1_treatment_details, st1.urgency_level as st1_urgency_level, st1.img as st1_img, st2.practitioner_name1 as st2_practitioner_name1, st2.practitioner_name2 as st2_practitioner_name2, st2.treatment_time as st2_treatment_time, st2.respiration as st2_respiration, st2.bp_right_1 as st2_bp_right_1, st2.bp_right_2 as st2_bp_right_2, st2.bp_left_1 as st2_bp_left_1, st2.bp_left_2 as st2_bp_left_2, st2.pulse_rate as st2_pulse_rate, st2.consciousness_jcs as st2_consciousness_jcs, st2.consciousness_e as st2_consciousness_e, st2.consciousness_v as st2_consciousness_v, st2.consciousness_m as st2_consciousness_m, st2.treatment_details as st2_treatment_details, st2.urgency_level as st2_urgency_level, st2.img as st2_img, st3.practitioner_name1 as st3_practitioner_name1, st3.practitioner_name2 as st3_practitioner_name2, st3.treatment_time as st3_treatment_time, st3.respiration as st3_respiration, st3.bp_right_1 as st3_bp_right_1, st3.bp_right_2 as st3_bp_right_2, st3.bp_left_1 as st3_bp_left_1, st3.bp_left_2 as st3_bp_left_2, st3.pulse_rate as st3_pulse_rate, st3.consciousness_jcs as st3_consciousness_jcs, st3.consciousness_e as st3_consciousness_e, st3.consciousness_v as st3_consciousness_v, st3.consciousness_m as st3_consciousness_m, st3.treatment_details as st3_treatment_details, st3.urgency_level as st3_urgency_level, st3.img as st3_img, ct.contact_time_h as ct_contact_time_h, ct.contact_time_m as ct_contact_time_m, ct.consciousness_jcs as ct_consciousness_jcs, ct.consciousness_e as ct_consciousness_e, ct.consciousness_v as ct_consciousness_v, ct.consciousness_m as ct_consciousness_m, ct.respiration_rate as ct_respiration_rate, ct.respiration_condition as ct_respiration_condition, ct.pulse_rate as ct_pulse_rate, ct.pulse_1 as ct_pulse_1, ct.pulse_2 as ct_pulse_2, ct.temperature_L as ct_temperature_L, ct.temperature_R as ct_temperature_R, ct.temperature_text as ct_temperature_text, ct.bp_right_1 as ct_bp_right_1, ct.bp_right_2 as ct_bp_right_2, ct.bp_left_1 as ct_bp_left_1, ct.bp_left_2 as ct_bp_left_2, ct.spo2_left as ct_spo2_left, ct.spo2_right as ct_spo2_right, ct.oxygen_flow_rate as ct_oxygen_flow_rate, ct.oxygen_use as ct_oxygen_use, ct.ecg_status as ct_ecg_status, ct.auscultation as ct_auscultation, ct.pupil_right_size as ct_pupil_right_size, ct.pupil_right_reaction as ct_pupil_right_reaction, ct.pupil_left_size as ct_pupil_left_size, ct.pupil_left_reaction as ct_pupil_left_reaction, ct.gaze_deviation as ct_gaze_deviation, ct.palpebral_conjunctiva as ct_palpebral_conjunctiva, ct.visual_impairment as ct_visual_impairment, ct.nystagmus as ct_nystagmus, ct.convulsion as ct_convulsion, ct.affected_area_condition as ct_affected_area_condition, ct.skin_condition as ct_skin_condition, ct.paralysis as ct_paralysis, ct.paralysis_area as ct_paralysis_area, ct.vomit as ct_vomit, ct.vomit_count as ct_vomit_count, ct.diarrhea as ct_diarrhea, ct.first_aid as ct_first_aid, ct.first_aid_other as ct_first_aid_other, ct.transport_position as ct_transport_position, ct.adl as ct_adl, m.text as memo_text, m.img as memo_img, ba.contact_time_h as ba_contact_time_h, ba.contact_time_m as ba_contact_time_m, ba.consciousness_jcs as ba_consciousness_jcs, ba.consciousness_e as ba_consciousness_e, ba.consciousness_v as ba_consciousness_v, ba.consciousness_m as ba_consciousness_m, ba.respiration_rate as ba_respiration_rate, ba.respiration_condition as ba_respiration_condition, ba.pulse_rate as ba_pulse_rate, ba.pulse_1 as ba_pulse_1, ba.pulse_2 as ba_pulse_2, ba.temperature_L as ba_temperature_L, ba.temperature_R as ba_temperature_R, ba.temperature_text as ba_temperature_text, ba.bp_right_1 as ba_bp_right_1, ba.bp_right_2 as ba_bp_right_2, ba.bp_left_1 as ba_bp_left_1, ba.bp_left_2 as ba_bp_left_2, ba.spo2_left as ba_spo2_left, ba.spo2_right as ba_spo2_right, ba.oxygen_flow_rate as ba_oxygen_flow_rate, ba.oxygen_use as ba_oxygen_use, ba.ecg_status as ba_ecg_status, ba.auscultation as ba_auscultation, r.ambulance_team_name, r.team_leader_name, r.diagnosis_name, r.severity as report_severity, r.hospital_selection_reason, r.distance_station_to_scene_L, r.distance_station_to_scene_R, r.distance_scene_to_hospital_L, r.distance_scene_to_hospital_R, r.distance_hospital_to_station_L, r.distance_hospital_to_station_R, r.distance_station_roundtrip_L, r.distance_station_roundtrip_R, r.first_doctor_name, r.related_organization FROM patient p LEFT JOIN time t ON p.No = t.patient_no LEFT JOIN call_received cr ON p.No = cr.patient_no LEFT JOIN patient_info pi ON p.No = pi.patient_no LEFT JOIN primary_triage pt ON p.No = pt.patient_no LEFT JOIN secondary_triage_1 st1 ON p.No = st1.patient_no LEFT JOIN secondary_triage_2 st2 ON p.No = st2.patient_no LEFT JOIN secondary_triage_3 st3 ON p.No = st3.patient_no LEFT JOIN contact_time_T ct ON p.No = ct.patient_no LEFT JOIN memo m ON p.No = m.patient_no LEFT JOIN before_arrival ba ON p.No = ba.patient_no LEFT JOIN report r ON p.No = r.patient_no WHERE p.No = %s """ cursor.execute(query, (patient_no,)) data = cursor.fetchone() if not data: raise HTTPException(status_code=404, detail="Report data not found for the given patient_no.") except Exception as e: raise HTTPException(status_code=500, detail=f"Database error: {str(e)}") finally: cursor.close() cnx.close() # --- 2. 엑셀 파일에 데이터 채우기 --- try: template_path = os.path.join(TEMPLATE_DIR, "20251112_B.xlsx") output_filename = f"reportB_{patient_no}.xlsx" excel_output_path = os.path.join(TEMPLATE_DIR, output_filename) workbook = openpyxl.load_workbook(template_path) sheet = workbook.active # --- 디버깅용 헬퍼 함수 --- def write_to_cell(cell, value): try: if value is None: sheet[cell] = None else: sheet[cell] = value except Exception as e: logging.error(f"---!!! CRITICAL ERROR WRITING TO CELL (Report B): {cell} !!!---") logging.error(f" VALUE: {value} (Type: {type(value)})") logging.error(f" ERROR: {e}") raise # --- time 테이블 --- if data.get('call_received_h') is not None and data.get('call_received_m') is not None: write_to_cell('A8', f"{data['call_received_h']}:{data['call_received_m']}") if data.get('dispatch_h') is not None and data.get('dispatch_m') is not None: write_to_cell('B8', f"{data['dispatch_h']}:{data['dispatch_m']}") if data.get('arrival_on_scene_h') is not None and data.get('arrival_on_scene_m') is not None: write_to_cell('D8', f"{data['arrival_on_scene_h']}:{data['arrival_on_scene_m']}") if data.get('patient_contact_h') is not None and data.get('patient_contact_m') is not None: write_to_cell('F8', f"{data['patient_contact_h']}:{data['patient_contact_m']}") if data.get('transport_start_h') is not None and data.get('transport_start_m') is not None: write_to_cell('J8', f"{data['transport_start_h']}:{data['transport_start_m']}") if data.get('patient_loaded_h') is not None and data.get('patient_loaded_m') is not None: write_to_cell('M8', f"{data['patient_loaded_h']}:{data['patient_loaded_m']}") if data.get('depart_scene_h') is not None and data.get('depart_scene_m') is not None: write_to_cell('Q8', f"{data['depart_scene_h']}:{data['depart_scene_m']}") if data.get('arrival_hospital_h') is not None and data.get('arrival_hospital_m') is not None: write_to_cell('T8', f"{data['arrival_hospital_h']}:{data['arrival_hospital_m']}") if data.get('handover_to_doctor_h') is not None and data.get('handover_to_doctor_m') is not None: write_to_cell('W8', f"{data['handover_to_doctor_h']}:{data['handover_to_doctor_m']}") if data.get('return_from_site_h') is not None and data.get('return_from_site_m') is not None: write_to_cell('AB8', f"{data['return_from_site_h']}:{data['return_from_site_m']}") if data.get('return_to_station_h') is not None and data.get('return_to_station_m') is not None: write_to_cell('AF8', f"{data['return_to_station_h']}:{data['return_to_station_m']}") write_to_cell('C10', data.get('dispatch_location')) write_to_cell('R10', data.get('doctor_car_detail')) # --- call_received 테이블 --- if data.get('call_received_date'): try: date_obj = datetime.strptime(data['call_received_date'], '%Y-%m-%d') write_to_cell('B2', date_obj.month) write_to_cell('D2', date_obj.day) weekdays = ["月", "火", "水", "木", "金", "土", "日"] day_index = date_obj.weekday() write_to_cell('G2', weekdays[day_index]) except (ValueError, TypeError): pass write_to_cell('L2', data.get('call_method')) write_to_cell('D3', data.get('cr_address')) write_to_cell('B6', data.get('incident_type')) # --- patient_info 테이블 --- pi_map = { 'patient_name1': 'B11', 'birth_year': 'N11', 'pi_age': 'Z11', 'pi_gender': 'AG11', 'patient_name2': 'B12', 'birth_month': 'N12', 'birth_day': 'N13', 'pi_address': 'B14', 'pi_phone_number': 'B15', 'companion_name': 'B16', 'relation': 'I16', 'contact_info': 'P16', 'onset_time': 'E21', 'contact_condition': 'V21', 'chief_complaint': 'E22', 'symptom_severity': 'V22', 'allergies': 'Z37', 'medication_history': 'F38', 'medical_history': 'Z38', 'last_meal_time': 'F39', 'primary_medical_institution': 'Z39' } for field, cell in pi_map.items(): write_to_cell(cell, data.get(field)) # --- contact_time_T 테이블 --- ct_map = { 'ct_consciousness_jcs': 'F25', 'ct_respiration_rate': 'H25', 'ct_pulse_1': 'M25', 'ct_bp_right_1': 'AD25', 'ct_bp_right_2': 'AG25', 'ct_contact_time_h': 'A26', 'ct_contact_time_m': 'C26', 'ct_respiration_condition': 'H26', 'ct_pulse_rate': 'N26', 'ct_temperature_text': 'U26', 'ct_bp_left_1': 'AD26', 'ct_bp_left_2': 'AG26', 'ct_consciousness_e': 'E27', 'ct_consciousness_v': 'F27', 'ct_consciousness_m': 'G27', 'ct_pulse_2': 'M27', 'ct_spo2_left': 'C28', 'ct_spo2_right': 'G28', 'ct_oxygen_use': 'M28', 'ct_oxygen_flow_rate': 'Q28', 'ct_ecg_status': 'Y28', 'ct_auscultation': 'AG28', 'ct_pupil_right_size': 'D29', 'ct_pupil_right_reaction': 'G29', 'ct_gaze_deviation': 'M29', 'ct_palpebral_conjunctiva': 'U29', 'ct_convulsion': 'AC29', 'ct_affected_area_condition': 'AG29', 'ct_pupil_left_size': 'D30', 'ct_pupil_left_reaction': 'G30', 'ct_visual_impairment': 'O30', 'ct_nystagmus': 'V30', 'ct_skin_condition': 'B32', 'ct_paralysis': 'N32', 'ct_vomit': 'AB32', 'ct_vomit_count': 'AD32', 'ct_diarrhea': 'AH32', 'ct_paralysis_area': 'U33', 'ct_first_aid': 'C34', 'ct_first_aid_other': 'K34', 'ct_transport_position': 'C36', 'adl': 'F37' } for field, cell in ct_map.items(): write_to_cell(cell, data.get(field)) if data.get('ct_temperature_L') is not None and data.get('ct_temperature_R') is not None: write_to_cell('U25', f"{data['ct_temperature_L']}.{data['ct_temperature_R']}") # --- before_arrival 테이블 --- ba_map = { 'ba_consciousness_jcs': 'F42', 'ba_respiration_rate': 'H42', 'ba_pulse_1': 'M42', 'ba_bp_right_1': 'AD42', 'ba_bp_right_2': 'AG42', 'ba_contact_time_h': 'A43', 'ba_contact_time_m': 'C43', 'ba_respiration_condition': 'H43', 'ba_pulse_rate': 'N43', 'ba_temperature_text': 'U43', 'ba_bp_left_1': 'AD43', 'ba_bp_left_2': 'AG43', 'ba_consciousness_e': 'E44', 'ba_consciousness_v': 'F44', 'ba_consciousness_m': 'G44', 'ba_pulse_2': 'M44', 'ba_spo2_left': 'C45', 'ba_spo2_right': 'G45', 'ba_oxygen_use': 'M45', 'ba_oxygen_flow_rate': 'Q45', 'ba_ecg_status': 'Y45', 'ba_auscultation': 'AG45' } for field, cell in ba_map.items(): write_to_cell(cell, data.get(field)) if data.get('ba_temperature_L') is not None and data.get('ba_temperature_R') is not None: write_to_cell('U42', f"{data['ba_temperature_L']}.{data['ba_temperature_R']}") # --- report 테이블 --- report_map = { 'diagnosis_name': 'L15', 'report_severity': 'AD15', 'team_leader_name': 'AA17', 'hospital_selection_reason': 'A18', 'ambulance_team_name': 'H18' } for field, cell in report_map.items(): write_to_cell(cell, data.get(field)) workbook.save(excel_output_path) pdf_path = convert_excel_to_pdf(excel_output_path, TEMPLATE_DIR) return {"status": "success", "message": "Report B generated and converted to PDF successfully.", "excel_path": excel_output_path, "pdf_path": pdf_path} except FileNotFoundError: raise HTTPException(status_code=404, detail=f"Template file not found at {template_path}") except Exception as e: raise HTTPException(status_code=500, detail=f"File processing or PDF conversion error: {str(e)}") # ============= [추가] report_c ============= @app.get("/sos/api/generate_report_c/{patient_no}") async def generate_report_c(patient_no: str): cnx = get_db_connection() cursor = cnx.cursor(dictionary=True) try: # 1. DB 데이터 조회 (기존과 동일) query = """ SELECT p.*, t.call_received_h, t.call_received_m, t.dispatch_h, t.dispatch_m, t.arrival_on_scene_h, t.arrival_on_scene_m, t.patient_contact_h, t.patient_contact_m, t.transport_start_h, t.transport_start_m, t.patient_loaded_h, t.patient_loaded_m, t.depart_scene_h, t.depart_scene_m, t.arrival_hospital_h, t.arrival_hospital_m, t.handover_to_doctor_h, t.handover_to_doctor_m, t.return_from_site_h, t.return_from_site_m, t.return_to_station_h, t.return_to_station_m, t.transfer1_h, t.transfer1_m, t.transfer2_h, t.transfer2_m, t.dispatch_location, t.doctor_car_detail, cr.call_received_date, cr.call_method, cr.monthly_number, cr.request_location, cr.address as cr_address, cr.incident_type, pi.patient_name1, pi.patient_name2, pi.birth_year, pi.birth_month, pi.birth_day, pi.age as pi_age, pi.gender as pi_gender, pi.occupation as pi_occupation, pi.address as pi_address, pi.phone_number as pi_phone_number, pi.companion_name, pi.relation, pi.contact_info, pi.urgency_level as pi_urgency_level, pi.onset_time, pi.contact_condition, pi.chief_complaint, pi.symptom_severity, pi.allergies, pi.medication_history, pi.medical_history, pi.last_meal_time, pi.primary_medical_institution, pt.urgency_level as primary_triage_urgency_level, st1.practitioner_name1 as st1_practitioner_name1, st1.practitioner_name2 as st1_practitioner_name2, st1.treatment_time as st1_treatment_time, st1.respiration as st1_respiration, st1.bp_right_1 as st1_bp_right_1, st1.bp_right_2 as st1_bp_right_2, st1.bp_left_1 as st1_bp_left_1, st1.bp_left_2 as st1_bp_left_2, st1.pulse_rate as st1_pulse_rate, st1.consciousness_jcs as st1_consciousness_jcs, st1.consciousness_e as st1_consciousness_e, st1.consciousness_v as st1_consciousness_v, st1.consciousness_m as st1_consciousness_m, st1.treatment_details as st1_treatment_details, st1.urgency_level as st1_urgency_level, st1.img as st1_img, st2.practitioner_name1 as st2_practitioner_name1, st2.practitioner_name2 as st2_practitioner_name2, st2.treatment_time as st2_treatment_time, st2.respiration as st2_respiration, st2.bp_right_1 as st2_bp_right_1, st2.bp_right_2 as st2_bp_right_2, st2.bp_left_1 as st2_bp_left_1, st2.bp_left_2 as st2_bp_left_2, st2.pulse_rate as st2_pulse_rate, st2.consciousness_jcs as st2_consciousness_jcs, st2.consciousness_e as st2_consciousness_e, st2.consciousness_v as st2_consciousness_v, st2.consciousness_m as st2_consciousness_m, st2.treatment_details as st2_treatment_details, st2.urgency_level as st2_urgency_level, st2.img as st2_img, st3.practitioner_name1 as st3_practitioner_name1, st3.practitioner_name2 as st3_practitioner_name2, st3.treatment_time as st3_treatment_time, st3.respiration as st3_respiration, st3.bp_right_1 as st3_bp_right_1, st3.bp_right_2 as st3_bp_right_2, st3.bp_left_1 as st3_bp_left_1, st3.bp_left_2 as st3_bp_left_2, st3.pulse_rate as st3_pulse_rate, st3.consciousness_jcs as st3_consciousness_jcs, st3.consciousness_e as st3_consciousness_e, st3.consciousness_v as st3_consciousness_v, st3.consciousness_m as st3_consciousness_m, st3.treatment_details as st3_treatment_details, st3.urgency_level as st3_urgency_level, st3.img as st3_img, ct.contact_time_h as ct_contact_time_h, ct.contact_time_m as ct_contact_time_m, ct.consciousness_jcs as ct_consciousness_jcs, ct.consciousness_e as ct_consciousness_e, ct.consciousness_v as ct_consciousness_v, ct.consciousness_m as ct_consciousness_m, ct.respiration_rate as ct_respiration_rate, ct.respiration_condition as ct_respiration_condition, ct.pulse_rate as ct_pulse_rate, ct.pulse_1 as ct_pulse_1, ct.pulse_2 as ct_pulse_2, ct.temperature_L as ct_temperature_L, ct.temperature_R as ct_temperature_R, ct.temperature_text as ct_temperature_text, ct.bp_right_1 as ct_bp_right_1, ct.bp_right_2 as ct_bp_right_2, ct.bp_left_1 as ct_bp_left_1, ct.bp_left_2 as ct_bp_left_2, ct.spo2_left as ct_spo2_left, ct.spo2_right as ct_spo2_right, ct.oxygen_flow_rate as ct_oxygen_flow_rate, ct.oxygen_use as ct_oxygen_use, ct.ecg_status as ct_ecg_status, ct.auscultation as ct_auscultation, ct.pupil_right_size as ct_pupil_right_size, ct.pupil_right_reaction as ct_pupil_right_reaction, ct.pupil_left_size as ct_pupil_left_size, ct.pupil_left_reaction as ct_pupil_left_reaction, ct.gaze_deviation as ct_gaze_deviation, ct.palpebral_conjunctiva as ct_palpebral_conjunctiva, ct.visual_impairment as ct_visual_impairment, ct.nystagmus as ct_nystagmus, ct.convulsion as ct_convulsion, ct.affected_area_condition as ct_affected_area_condition, ct.skin_condition as ct_skin_condition, ct.paralysis as ct_paralysis, ct.paralysis_area as ct_paralysis_area, ct.vomit as ct_vomit, ct.vomit_count as ct_vomit_count, ct.diarrhea as ct_diarrhea, ct.first_aid as ct_first_aid, ct.first_aid_other as ct_first_aid_other, ct.transport_position as ct_transport_position, ct.adl as ct_adl, m.text as memo_text, m.img as memo_img, ba.contact_time_h as ba_contact_time_h, ba.contact_time_m as ba_contact_time_m, ba.consciousness_jcs as ba_consciousness_jcs, ba.consciousness_e as ba_consciousness_e, ba.consciousness_v as ba_consciousness_v, ba.consciousness_m as ba_consciousness_m, ba.respiration_rate as ba_respiration_rate, ba.respiration_condition as ba_respiration_condition, ba.pulse_rate as ba_pulse_rate, ba.pulse_1 as ba_pulse_1, ba.pulse_2 as ba_pulse_2, ba.temperature_L as ba_temperature_L, ba.temperature_R as ba_temperature_R, ba.temperature_text as ba_temperature_text, ba.bp_right_1 as ba_bp_right_1, ba.bp_right_2 as ba_bp_right_2, ba.bp_left_1 as ba_bp_left_1, ba.bp_left_2 as ba_bp_left_2, ba.spo2_left as ba_spo2_left, ba.spo2_right as ba_spo2_right, ba.oxygen_flow_rate as ba_oxygen_flow_rate, ba.oxygen_use as ba_oxygen_use, ba.ecg_status as ba_ecg_status, ba.auscultation as ba_auscultation, r.ambulance_team_name, r.team_leader_name, r.diagnosis_name, r.severity as report_severity, r.hospital_selection_reason, r.distance_station_to_scene_L, r.distance_station_to_scene_R, r.distance_scene_to_hospital_L, r.distance_scene_to_hospital_R, r.distance_hospital_to_station_L, r.distance_hospital_to_station_R, r.distance_station_roundtrip_L, r.distance_station_roundtrip_R, r.first_doctor_name, r.related_organization FROM patient p LEFT JOIN time t ON p.No = t.patient_no LEFT JOIN call_received cr ON p.No = cr.patient_no LEFT JOIN patient_info pi ON p.No = pi.patient_no LEFT JOIN primary_triage pt ON p.No = pt.patient_no LEFT JOIN secondary_triage_1 st1 ON p.No = st1.patient_no LEFT JOIN secondary_triage_2 st2 ON p.No = st2.patient_no LEFT JOIN secondary_triage_3 st3 ON p.No = st3.patient_no LEFT JOIN contact_time_T ct ON p.No = ct.patient_no LEFT JOIN memo m ON p.No = m.patient_no LEFT JOIN before_arrival ba ON p.No = ba.patient_no LEFT JOIN report r ON p.No = r.patient_no WHERE p.No = %s """ cursor.execute(query, (patient_no,)) data = cursor.fetchone() if not data: raise HTTPException(status_code=404, detail="Report data not found for the given patient_no.") except Exception as e: raise HTTPException(status_code=500, detail=f"Database error: {str(e)}") finally: cursor.close() cnx.close() # --- 2. 엑셀 파일에 데이터 채우기 --- try: template_path = os.path.join(TEMPLATE_DIR, "20251112_C.xlsx") output_filename = f"reportC_{patient_no}.xlsx" excel_output_path = os.path.join(TEMPLATE_DIR, output_filename) workbook = openpyxl.load_workbook(template_path) sheet = workbook.active # --- 디버깅용 헬퍼 함수 --- def write_to_cell(cell, value): try: if value is None: sheet[cell] = None else: sheet[cell] = value except Exception as e: logging.error(f"---!!! CRITICAL ERROR WRITING TO CELL (Report C): {cell} !!!---") logging.error(f" VALUE: {value} (Type: {type(value)})") logging.error(f" ERROR: {e}") raise # --- time 테이블 --- if data.get('call_received_h') is not None and data.get('call_received_m') is not None: write_to_cell('A8', f"{data['call_received_h']}:{data['call_received_m']}") if data.get('dispatch_h') is not None and data.get('dispatch_m') is not None: write_to_cell('B8', f"{data['dispatch_h']}:{data['dispatch_m']}") if data.get('arrival_on_scene_h') is not None and data.get('arrival_on_scene_m') is not None: write_to_cell('D8', f"{data['arrival_on_scene_h']}:{data['arrival_on_scene_m']}") if data.get('patient_contact_h') is not None and data.get('patient_contact_m') is not None: write_to_cell('F8', f"{data['patient_contact_h']}:{data['patient_contact_m']}") if data.get('transport_start_h') is not None and data.get('transport_start_m') is not None: write_to_cell('J8', f"{data['transport_start_h']}:{data['transport_start_m']}") if data.get('patient_loaded_h') is not None and data.get('patient_loaded_m') is not None: write_to_cell('M8', f"{data['patient_loaded_h']}:{data['patient_loaded_m']}") if data.get('depart_scene_h') is not None and data.get('depart_scene_m') is not None: write_to_cell('Q8', f"{data['depart_scene_h']}:{data['depart_scene_m']}") if data.get('arrival_hospital_h') is not None and data.get('arrival_hospital_m') is not None: write_to_cell('T8', f"{data['arrival_hospital_h']}:{data['arrival_hospital_m']}") if data.get('handover_to_doctor_h') is not None and data.get('handover_to_doctor_m') is not None: write_to_cell('W8', f"{data['handover_to_doctor_h']}:{data['handover_to_doctor_m']}") if data.get('return_from_site_h') is not None and data.get('return_from_site_m') is not None: write_to_cell('AB8', f"{data['return_from_site_h']}:{data['return_from_site_m']}") if data.get('return_to_station_h') is not None and data.get('return_to_station_m') is not None: write_to_cell('AF8', f"{data['return_to_station_h']}:{data['return_to_station_m']}") if data.get('transfer1_h') is not None and data.get('transfer1_m') is not None: write_to_cell('AH8', f"{data['transfer1_h']}:{data['transfer1_m']}") if data.get('transfer2_h') is not None and data.get('transfer2_m') is not None: write_to_cell('AH9', f"{data['transfer2_h']}:{data['transfer2_m']}") write_to_cell('C10', data.get('dispatch_location')) write_to_cell('R10', data.get('doctor_car_detail')) # --- call_received 테이블 --- if data.get('call_received_date'): try: date_obj = datetime.strptime(data['call_received_date'], '%Y-%m-%d') write_to_cell('B2', date_obj.month) write_to_cell('D2', date_obj.day) weekdays = ["月", "火", "水", "木", "金", "土", "日"] day_index = date_obj.weekday() write_to_cell('G2', weekdays[day_index]) except (ValueError, TypeError): pass write_to_cell('L2', data.get('call_method')) write_to_cell('D3', data.get('cr_address')) write_to_cell('B6', data.get('incident_type')) # --- patient_info 테이블 --- pi_map = { 'patient_name1': 'B11', 'birth_year': 'N11', 'pi_age': 'Z12', 'pi_gender': 'AE11', 'pi_occupation': 'AG13', 'patient_name2': 'B12', 'birth_month': 'N12', 'birth_day': 'N13', 'pi_address': 'B14', 'pi_phone_number': 'B15', 'companion_name': 'B16', 'relation': 'I16', 'contact_info': 'P16' } for field, cell in pi_map.items(): write_to_cell(cell, data.get(field)) # --- report 테이블 --- report_map = { 'diagnosis_name': 'L15', 'report_severity': 'AD15', 'team_leader_name': 'AA17', 'hospital_selection_reason': 'A18', 'ambulance_team_name': 'H18' } for field, cell in report_map.items(): write_to_cell(cell, data.get(field)) workbook.save(excel_output_path) # --- pdf 만들기 --- pdf_path = convert_excel_to_pdf(excel_output_path, TEMPLATE_DIR) return {"status": "success", "message": "Report C generated and converted to PDF successfully.", "excel_path": excel_output_path, "pdf_path": pdf_path} except FileNotFoundError: raise HTTPException(status_code=404, detail=f"Template file not found at {template_path}") except Exception as e: raise HTTPException(status_code=500, detail=f"File processing or PDF conversion error: {str(e)}") # ======================== [추가] toriage pdf ======================== @app.get("/sos/api/generate_triage_list/{patient_no}") async def generate_triage_list(patient_no: str): cnx = get_db_connection() cursor = cnx.cursor(dictionary=True) try: # 1. 각 토리아지 단계별 urgency_level 별도로 가져오기 query = """ SELECT p.No, p.age, p.gender, p.name2 AS name_kanji, p.address, pi.chief_complaint, p.receiving_hospital_name, p.transport_agency_name, t.depart_scene_h, t.depart_scene_m, p.month, p.day, p.am_pm, p.time_h, p.time_m, pt.urgency_level AS primary_urgency, st1.urgency_level AS secondary1_urgency, st2.urgency_level AS secondary2_urgency, st3.urgency_level AS secondary3_urgency FROM patient p LEFT JOIN patient_info pi ON p.No = pi.patient_no LEFT JOIN primary_triage pt ON p.No = pt.patient_no LEFT JOIN secondary_triage_1 st1 ON p.No = st1.patient_no LEFT JOIN secondary_triage_2 st2 ON p.No = st2.patient_no LEFT JOIN secondary_triage_3 st3 ON p.No = st3.patient_no LEFT JOIN time t ON p.No = t.patient_no WHERE p.No IS NOT NULL ORDER BY CAST(p.No AS UNSIGNED) ASC """ cursor.execute(query) patients = cursor.fetchall() if not patients: raise HTTPException(status_code=404, detail="登録された傷病者がいません") # 2. 템플릿 로드(20251112_toriazi.xlsx) template_path = os.path.join(TEMPLATE_DIR, "20251112_toriazi.xlsx") if not os.path.exists(template_path): raise HTTPException(status_code=404, detail="テンプレート ファイルがありません: 20251112_toriazi.xlsx") excel_filename = f"report_triage_{patient_no}.xlsx" excel_output_path = os.path.join(TEMPLATE_DIR, excel_filename) wb = openpyxl.load_workbook(template_path) ws = wb.active # 3. 상단 【覚知】(トリアージ実施時刻) - 첫 번째 환자 데이터 사용 first = patients[0] ws['I1'] = first.get('month') or '' # monthButton → I1 ws['K1'] = first.get('day') or '' # dayButton → K1 ws['M1'] = first.get('am_pm') or '' # ampm-dropDown → M1 ws['N1'] = first.get('time_h') or '' # time_h_text 상단 ws['P1'] = first.get('time_m') or '' # time_m_text 상단 # 4. 리스트 본문 (row 4부터) def get_triage_text(level): mapping = {'R': '赤', 'Y': '黄', 'G': '緑', 'B': '黒'} return mapping.get(level, '') for idx, p in enumerate(patients): row = 4 + idx # A열: 순번 ws[f'A{row}'] = idx + 1 # B열: No (noText) ws[f'B{row}'] = p['No'] # C~F열: 각 토리아지 단계별 색상 텍스트 (赤/黄/緑/黒) ws[f'C{row}'] = get_triage_text(p.get('primary_urgency')) ws[f'D{row}'] = get_triage_text(p.get('secondary1_urgency')) ws[f'E{row}'] = get_triage_text(p.get('secondary2_urgency')) ws[f'F{row}'] = get_triage_text(p.get('secondary3_urgency')) # G~I열 ws[f'G{row}'] = p['age'] or '' ws[f'H{row}'] = p['gender'] or '' ws[f'I{row}'] = p.get('name_kanji') or '' # J열 주소 ws[f'J{row}'] = p.get('address') or '' # S열 이송처 ws[f'S{row}'] = p.get('receiving_hospital_name') or '' # T열 구급대 ws[f'T{row}'] = p.get('transport_agency_name') or '' # W열 토리아지 시 ws[f'W{row}'] = p.get('time_h') or '' # Y열 토리아지 분 ws[f'Y{row}'] = p.get('time_m') or '' # 5. 저장 + PDF 변환 wb.save(excel_output_path) pdf_path = convert_excel_to_pdf(excel_output_path, TEMPLATE_DIR) return { "status": "success", "excel_path": excel_output_path, "pdf_path": pdf_path } except Exception as e: logging.error(f"トリアージリスト作成エラー: {e}", exc_info=True) raise HTTPException(status_code=500, detail=str(e)) finally: cursor.close() cnx.close() # ================================================================================ # --- [추가] 시간 정보 불러오기 API --- @app.get("/sos/api/get_times/{patient_no}") async def get_times(patient_no: str): logging.info(f"Fetching specific time data for patient_no: {patient_no}") cnx = get_db_connection() cursor = cnx.cursor(dictionary=True) try: # 요구사항에 명시된 특정 시간 필드만 선택합니다. query = """ SELECT patient_contact_h, patient_contact_m, arrival_hospital_h, arrival_hospital_m FROM time WHERE patient_no = %s """ cursor.execute(query, (patient_no,)) time_data = cursor.fetchone() if time_data: return time_data # 데이터가 없는 경우 404 에러를 발생시킵니다. raise HTTPException(status_code=404, detail="Time data not found for the given patient_no.") except HTTPException as http_exc: raise http_exc except Exception as e: logging.error(f"Error fetching time data for patient_no {patient_no}: {e}", exc_info=True) raise HTTPException(status_code=500, detail=str(e)) finally: cursor.close() cnx.close() # @app.get("/sos/api/generate_triage_list") # async def generate_triage_list(patient_no: str): # cnx = get_db_connection() # cursor = cnx.cursor(dictionary=True) # # 시작 로그 # logger.info("▼ generate_triage_list START patient_no=%s", patient_no) # try: # # 1. 각 토리아지 단계별 urgency_level 별도로 가져오기 # query = """ # SELECT # p.No, # p.age, # p.gender, # p.name2 AS name_kanji, # p.address, # pi.chief_complaint, # p.receiving_hospital_name, # p.transport_agency_name, # t.depart_scene_h, # t.depart_scene_m, # p.month, # p.day, # p.am_pm, # p.time_h, # p.time_m, # pt.urgency_level AS primary_urgency, # st1.urgency_level AS secondary1_urgency, # st2.urgency_level AS secondary2_urgency, # st3.urgency_level AS secondary3_urgency # FROM patient p # LEFT JOIN patient_info pi ON p.No = pi.patient_no # LEFT JOIN primary_triage pt ON p.No = pt.patient_no # LEFT JOIN secondary_triage_1 st1 ON p.No = st1.patient_no # LEFT JOIN secondary_triage_2 st2 ON p.No = st2.patient_no # LEFT JOIN secondary_triage_3 st3 ON p.No = st3.patient_no # LEFT JOIN time t ON p.No = t.patient_no # WHERE p.No IS NOT NULL # ORDER BY CAST(p.No AS UNSIGNED) ASC # """ # logger.info("SQL 実行開始 (triage_list)") # cursor.execute(query) # patients = cursor.fetchall() # logger.info("SQL 実行完了: 患者件数=%d", len(patients)) # if not patients: # logger.warning("患者データが 0 件です") # raise HTTPException(status_code=404, detail="登録された傷病者がいません") # # 2. 템플릿 로드(20251112_toriazi.xlsx) # template_path = os.path.join(TEMPLATE_DIR, "20251112_toriazi.xlsx") # logger.info("テンプレートパス確認: %s", template_path) # if not os.path.exists(template_path): # logger.error("テンプレートファイルが存在しません: %s", template_path) # raise HTTPException(status_code=404, detail="テンプレート ファイルがありません: 20251112_toriazi.xlsx") # excel_filename = f"report_toriage_{patient_no}.xlsx" # excel_output_path = os.path.join(TEMPLATE_DIR, excel_filename) # logger.info("Excel 出力パス: %s", excel_output_path) # wb = openpyxl.load_workbook(template_path) # ws = wb.active # # 3. 상단 【覚知】(トリアージ実施時刻) - 첫 번째 환자 데이터 사용 # first = patients[0] # logger.info("先頭患者No=%s month=%s day=%s am_pm=%s time=%s:%s", # first.get('No'), # first.get('month'), # first.get('day'), # first.get('am_pm'), # first.get('time_h'), # first.get('time_m')) # ws['I1'] = first.get('month') or '' # monthButton → I1 # ws['K1'] = first.get('day') or '' # dayButton → K1 # ws['M1'] = first.get('am_pm') or '' # ampm-dropDown → M1 # ws['N1'] = first.get('time_h') or '' # time_h_text 상단 # ws['P1'] = first.get('time_m') or '' # time_m_text 상단 # # 4. 리스트 본문 (row 4부터) # def get_triage_text(level): # mapping = {'R': '赤', 'Y': '黄', 'G': '緑', 'B': '黒'} # return mapping.get(level, '') # for idx, p in enumerate(patients): # row = 4 + idx # # A열: 순번 # ws[f'A{row}'] = idx + 1 # # B열: No (noText) # ws[f'B{row}'] = p['No'] # # C~F열: 각 토リア지 단계별 색상 텍스트 (赤/黄/緑/黒) # ws[f'C{row}'] = get_triage_text(p.get('primary_urgency')) # ws[f'D{row}'] = get_triage_text(p.get('secondary1_urgency')) # ws[f'E{row}'] = get_triage_text(p.get('secondary2_urgency')) # ws[f'F{row}'] = get_triage_text(p.get('secondary3_urgency')) # # G~I열 # ws[f'G{row}'] = p['age'] or '' # ws[f'H{row}'] = p['gender'] or '' # ws[f'I{row}'] = p.get('name_kanji') or '' # # J열 주소 # ws[f'J{row}'] = p.get('address') or '' # # S열 이송처 # ws[f'S{row}'] = p.get('receiving_hospital_name') or '' # # T열 구급대 # ws[f'T{row}'] = p.get('transport_agency_name') or '' # # W열 토리아지 시 # ws[f'W{row}'] = p.get('time_h') or '' # # Y열 토리아지 분 # ws[f'Y{row}'] = p.get('time_m') or '' # logger.info("Excel への書き込み完了, 保存開始") # # 5. 저장 + PDF 변환 # wb.save(excel_output_path) # logger.info("Excel 保存完了: %s", excel_output_path) # pdf_path = convert_excel_to_pdf(excel_output_path, TEMPLATE_DIR) # logger.info("PDF 変換完了: %s", pdf_path) # logger.info("▲ generate_triage_list END 正常終了 patient_no=%s", patient_no) # return { # "status": "success", # "excel_path": excel_output_path, # "pdf_path": pdf_path # } # except HTTPException as he: # # FastAPI에서 의도적으로 raise 한 에러 # logger.exception("HTTPException in generate_triage_list patient_no=%s detail=%s", # patient_no, he.detail) # raise # except Exception as e: # # 예기치 못한 에러 (스택 트레이스 전체 기록) # logger.exception("Unexpected error in generate_triage_list patient_no=%s", patient_no) # # 프론트에서 보기 좋게 타입까지 내려줌 # raise HTTPException( # status_code=500, # detail={ # "error": str(e), # "type": e.__class__.__name__ # } # ) # finally: # cursor.close() # cnx.close()