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 from openpyxl.styles import Font # [추가] 셀 스타일 from fastapi.middleware.cors import CORSMiddleware import pytz from datetime import datetime from fastapi.staticfiles import StaticFiles #[추가]: pdf로 이동 from fastapi.responses import HTMLResponse, JSONResponse from pydantic import BaseModel import logging from logging.handlers import RotatingFileHandler # Basic logging configuration logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s') app = FastAPI() # CORS 설정 app.add_middleware( CORSMiddleware, allow_origins=["*"], allow_credentials=True, allow_methods=["*"], allow_headers=["*"], ) # --- 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') DB_CONFIG = { 'host': 'localhost', 'user': 'sos-user', 'password': 'PassWord12!!', 'database': 'sos', 'port': 3306 } # --- 경로 정보 --- 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" ) # [추가] 업로드 폴더를 웹 주소(/sos/uploads)로 연결 app.mount("/sos/uploads", StaticFiles(directory=UPLOAD_DIR), name="uploads") # Pydantic 모델: 로그인 요청 데이터 구조 정의 class LoginRequest(BaseModel): code: str callName: str # 2. HTML 파일 서빙 (루트 경로 접근 시 index.html 제공) @app.get("/", response_class=HTMLResponse) async def serve_index(): # index.html 파일 경로 확인 및 반환 (경로에 맞게 수정 필요) index_path = os.path.join(os.path.dirname(__file__), "index.html") if not os.path.exists(index_path): raise HTTPException(status_code=404, detail="index.html not found") with open(index_path, 'r', encoding='utf-8') as f: return HTMLResponse(content=f.read()) # [추가] 다음 번호 요청 데이터 모델 (환자 번호 자동 생성용) class NextNoRequest(BaseModel): prefix: str class AdminLoginRequest(BaseModel): id: str password: str # 3. 로그인 인증 API 엔드포인트 @app.post("/login") def login(req: LoginRequest): input_code = req.code.strip() input_call_name = req.callName.strip() if not input_code or not input_call_name: return JSONResponse(status_code=400, content={ "success": False, "message": "隊コードと呼出名称を入力してください。" }) conn = None try: conn = mysql.connector.connect(**DB_CONFIG) cursor = conn.cursor(dictionary=True) # [수정] abbreviation 컬럼도 같이 가져옴 query = "SELECT team_name, abbreviation FROM ambulance_teams WHERE team_code = %s AND call_name = %s" cursor.execute(query, (input_code, input_call_name)) user = cursor.fetchone() if user: # [수정] teamName과 함께 abbreviation도 보냄 return { "success": True, "teamName": user['team_name'], "abbreviation": user['abbreviation'] } else: return {"success": False, "message": "隊コードまたは呼出名称が間違っています。"} except mysql.connector.Error as err: print(f"Database Error: {err}") return JSONResponse(status_code=500, content={ "success": False, "message": "データベースエラーが発生しました。" }) finally: if conn and conn.is_connected(): cursor.close() conn.close() # [추가] 환자 번호 자동 생성 API @app.post("/get_next_no") def get_next_no(req: NextNoRequest): prefix = req.prefix # 예: "202512110431" conn = None try: conn = mysql.connector.connect(**DB_CONFIG) cursor = conn.cursor(dictionary=True) # patient 테이블에서 해당 시간대(prefix)로 시작하는 모든 No를 조회 # 예: '2025-1211-0431-%' 패턴 검색 # [수정] '-' 제거 query = "SELECT No FROM patient WHERE No LIKE %s" cursor.execute(query, (f"{prefix}%", )) rows = cursor.fetchall() max_no = 0 prefix_len = len(prefix) for row in rows: # "2025-1211-0431-2" 형식에서 맨 뒤 숫자("2") 추출 pid = row['No'] if len(pid) > prefix_len: suffix = pid[prefix_len:] if suffix.isdigit(): no = int(suffix) if no > max_no: max_no = no # 가장 큰 번호 + 1 (데이터가 없으면 0+1=1) next_no = max_no + 1 return {"success": True, "next_no": next_no} except mysql.connector.Error as err: print(f"DB Error: {err}") return JSONResponse(status_code=500, content={"success": False, "message": "DB Error"}) finally: if conn and conn.is_connected(): cursor.close() conn.close() # [추가] 공통 헬퍼 함수: 환자 레코드가 없으면 초기화(INSERT) # 토리야지 부분 함수 def ensure_patient_record_exists(cursor, patient_no: str): """ DB에 해당 patient_no가 존재하는지 확인하고, 없다면 patient 테이블 및 모든 관련 테이블에 초기 행(placeholder)을 생성합니다. """ cursor.execute("SELECT No FROM patient WHERE No = %s", (patient_no,)) if cursor.fetchone(): return # 이미 존재하면 패스 # 존재하지 않으면 초기 레코드 생성 (INSERT) logging.info(f"Auto-initiating record for missing patient_no: {patient_no}") 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()] # 1. patient 테이블 (기본값) cursor.execute("INSERT INTO patient (No, date_time, day_of_week) VALUES (%s, %s, %s)", (patient_no, date_time_str, day_of_week)) # 2. 관련 테이블 Placeholder 생성 # [수정] 추가된 테이블(transfer_state) placeholder_tables = [ "time", "call_received", "primary_triage", "secondary_triage_1", "secondary_triage_2", "secondary_triage_3", "contact_time_T", "memo", "before_arrival", "report", "transfer_state", "sick_and_wounded_list" ] for table in placeholder_tables: cursor.execute(f"INSERT INTO {table} (patient_no) VALUES (%s)", (patient_no,)) # 3. patient_info & list 테이블 cursor.execute("INSERT INTO patient_info (patient_no, urgency_level) VALUES (%s, NULL)", (patient_no,)) cursor.execute("INSERT INTO list (patient_no, date_time, day_of_week, name2) VALUES (%s, %s, %s, '未入力')", (patient_no, date_time_str, day_of_week)) # --- 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), images: List[UploadFile] = File(None), # [수정] 여러 장 수신 existing_images: List[str] = Form(None), # [추가] 기존 이미지 경로 유지용 urgency_level: Optional[str] = Form(None) # img: Optional[UploadFile] = File(None) # 'img'라는 키로 한 장만 받음 ): logging.info(f"Attempting to save/update patient with No: {No}") cnx = get_db_connection() cursor = cnx.cursor() try: # --- 1. 이미지 경로 리스트 생성 --- final_paths = [] # 삭제되지 않고 유지된 기존 이미지 경로 추가 if existing_images: # existing_images는 리스트 형태로 들어옴 final_paths.extend([path.strip() for path in existing_images if path.strip()]) # 새로 업로드된 이미지 파일 저장 및 경로 추가 if images: os.makedirs(UPLOAD_DIR, exist_ok=True) for img in images: if img.filename: unique_filename = f"{uuid.uuid4()}{os.path.splitext(img.filename)[1]}" full_save_path = os.path.join(UPLOAD_DIR, unique_filename) with open(full_save_path, "wb") as buffer: shutil.copyfileobj(img.file, buffer) # 웹에서 접근 가능한 상대 경로 추가 web_url = f"/sos/uploads/{unique_filename}" final_paths.append(web_url) # 모든 경로를 쉼표로 연결하여 문자열 생성 combined_img_string = ",".join(final_paths) if final_paths else None # 2. 날짜/요일 정보 생성 (공통) 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 "未入力" # 3. 레코드 존재 여부 확인 (핵심: Time 페이지와 같은 방식 적용) cursor.execute("SELECT No FROM patient WHERE No = %s", (No,)) record_exists = cursor.fetchone() if not record_exists: # === [CASE A: INSERT] 데이터가 없으면 새로 생성 (기존 로직) === logging.info(f"Insert NEW patient record: {No}") insert_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 ) """ insert_params = ( No, name1, effective_name2, age, gender, combined_img_string, 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_query, insert_params) # 관련 테이블 초기화 (Placeholder 생성) # [수정] 추가된 테이블(transfer_state) placeholder_tables = [ "time", "call_received", "primary_triage", "secondary_triage_1", "secondary_triage_2", "secondary_triage_3", "contact_time_T", "memo", "before_arrival", "report", "transfer_state" ] for table in placeholder_tables: cursor.execute(f"INSERT INTO {table} (patient_no) VALUES (%s)", (No,)) # patient_info 생성 cursor.execute("INSERT INTO patient_info (patient_no, urgency_level) VALUES (%s, %s)", (No, urgency_level)) # list 테이블 추가 cursor.execute( "INSERT INTO list (patient_no, date_time, day_of_week, name2) VALUES (%s, %s, %s, %s)", (No, date_time_str, day_of_week, effective_name2) ) else: logging.info(f"Update EXISTING patient record: {No}") update_query = """ UPDATE patient SET name1=%s, name2=%s, age=%s, gender=%s, address=%s, phone_number=%s, month=%s, day=%s, am_pm=%s, time_h=%s, time_m=%s, triage_officer_name1=%s, triage_officer_name2=%s, transport_agency_name=%s, receiving_hospital_name=%s, img=%s WHERE No = %s """ update_params = ( name1, effective_name2, age, gender, address, phone_number, month, day, am_pm, time_h, time_m, triage_officer_name1, triage_officer_name2, transport_agency_name, receiving_hospital_name, combined_img_string, No ) cursor.execute(update_query, update_params) # list 테이블 이름 동기화 cursor.execute("UPDATE list SET name2 = %s WHERE patient_no = %s", (effective_name2, No)) # patient_info 긴급도(urgency_level) 업데이트 if urgency_level: cursor.execute("UPDATE patient_info SET urgency_level = %s WHERE patient_no = %s", (urgency_level, No)) # [추가] 트리아지 데이터 연동 (patient 저장 시 call_received, patient_info 업데이트) # 1. 주소 연동 (patient.address -> call_received.address) # 주소값이 있을 때만 업데이트합니다. if address is not None: sync_cr_query = "UPDATE call_received SET address = %s WHERE patient_no = %s" cursor.execute(sync_cr_query, (address, No)) logging.info(f"Synced address to call_received for No: {No}") # 2. 환자 정보 연동 (patient -> patient_info) # 이름(1,2), 성별, 전화번호를 patient_info 테이블에 업데이트합니다. sync_pi_query = """ UPDATE patient_info SET patient_name1 = %s, patient_name2 = %s, gender = %s, phone_number = %s WHERE patient_no = %s """ # effective_name2는 위에서 처리된 이름(없으면 '未入力')입니다. cursor.execute(sync_pi_query, (name1, effective_name2, gender, phone_number, No)) logging.info(f"Synced name, gender, phone to patient_info for No: {No}") cnx.commit() return {"status": "success", "message": f"Patient data saved successfully (No={No})"} except Exception as e: cnx.rollback() logging.error(f"Error saving patient data for No {No}: {e}", exc_info=True) raise HTTPException(status_code=500, detail=f"Database error: {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 테이블 제외) # [수정] 추가된 테이블(transfer_state) placeholder_tables = [ "call_received", "primary_triage", "secondary_triage_1", "secondary_triage_2", "secondary_triage_3", "contact_time_T", "memo", "before_arrival", "report", "transfer_state", "sick_and_wounded_list" ] 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) # [추가] 接触 (Contact) -> contact_time_T 테이블 동기화 # Time 페이지의 '접촉' 시간이 입력되면 contact_time_T의 '접촉시' 시간도 업데이트 if patient_contact_h is not None and patient_contact_m is not None: sync_contact_query = """ UPDATE contact_time_T SET contact_time_h = %s, contact_time_m = %s WHERE patient_no = %s """ cursor.execute(sync_contact_query, (patient_contact_h, patient_contact_m, patient_no)) logging.info(f"Synced time.patient_contact -> contact_time_T for No: {patient_no}") # [추가] 病院到着 (Arrival Hospital) -> before_arrival 테이블 동기화 # Time 페이지의 '병원도착' 시간이 입력되면 before_arrival의 '접촉시(병원도착전)' 시간도 업데이트 if arrival_hospital_h is not None and arrival_hospital_m is not None: sync_before_query = """ UPDATE before_arrival SET contact_time_h = %s, contact_time_m = %s WHERE patient_no = %s """ cursor.execute(sync_before_query, (arrival_hospital_h, arrival_hospital_m, patient_no)) logging.info(f"Synced time.arrival_hospital -> before_arrival for No: {patient_no}") # [추가] transfer_state 테이블 업데이트 로직 # 시/분 두 필드가 모두 None이 아니거나 빈 문자열이 아닌 경우에만 1로 설정합니다. patient_loaded_status = 1 if patient_loaded_h and patient_loaded_m else 0 depart_scene_status = 1 if depart_scene_h and depart_scene_m else 0 arrival_hospital_status = 1 if arrival_hospital_h and arrival_hospital_m else 0 handover_to_doctor_status = 1 if handover_to_doctor_h and handover_to_doctor_m else 0 transfer_update_query = """ UPDATE transfer_state SET patient_loaded_time=%s, depart_scene_time=%s, arrival_hospital_time=%s, handover_to_doctor_time=%s WHERE patient_no = %s """ transfer_update_value = ( patient_loaded_status, depart_scene_status, arrival_hospital_status, handover_to_doctor_status, patient_no ) cursor.execute(transfer_update_query, transfer_update_value) logging.info(f"Updated transfer_state (Checked _h and _m) for patient_no: {patient_no}") 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) # [추가] contact_time_T (접촉시) -> time (접촉) 동기화 if contact_time_h is not None and contact_time_m is not None: sync_time_query = """ UPDATE time SET patient_contact_h = %s, patient_contact_m = %s WHERE patient_no = %s """ cursor.execute(sync_time_query, (contact_time_h, contact_time_m, patient_no)) logging.info(f"Synced contact_time_T -> time.patient_contact for No: {patient_no}") 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([]), # 신규 파일들 existing_images: List[str] = Form([]) # 유지할 기존 경로들 ): logging.info(f"Updating memo data for patient_no: {patient_no}") cnx = get_db_connection() cursor = cnx.cursor() try: final_paths = [] # 1. 유지할 기존 이미지 경로 추가 (추가된 로직) if existing_images: final_paths.extend([path.strip() for path in existing_images if path.strip()]) # 2. 신규 이미지 저장 (기존 로직 유지 및 개선) if images: os.makedirs(UPLOAD_DIR, exist_ok=True) for img in images: if img.filename: unique_filename = f"{uuid.uuid4()}{os.path.splitext(img.filename)[1]}" full_path = os.path.join(UPLOAD_DIR, unique_filename) with open(full_path, "wb") as buffer: shutil.copyfileobj(img.file, buffer) final_paths.append(f"/sos/uploads/{unique_filename}") combined_img_string = ",".join(final_paths) if final_paths else None # 3. DB 업데이트 query = "UPDATE memo SET text = %s, img = %s WHERE patient_no = %s" cursor.execute(query, (text, combined_img_string, patient_no)) cnx.commit() return {"status": "success", "message": "Memo updated with multiple images"} except Exception as e: cnx.rollback() 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) # [추가] before_arrival (병원도착전) -> time (병원도착) 동기화 if contact_time_h is not None and contact_time_m is not None: sync_time_query = """ UPDATE time SET arrival_hospital_h = %s, arrival_hospital_m = %s WHERE patient_no = %s """ cursor.execute(sync_time_query, (contact_time_h, contact_time_m, patient_no)) # [추가] time 테이블의 '병원도착'이 업데이트 되었으므로 transfer_state도 업데이트 (필요시) transfer_sync_query = """ UPDATE transfer_state SET arrival_hospital_time = 1 WHERE patient_no = %s """ cursor.execute(transfer_sync_query, (patient_no,)) logging.info(f"Synced before_arrival -> time.arrival_hospital for No: {patient_no}") 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( patient_no: str = Form(...), urgency_level: str = Form(...), checklist_data: Optional[str] = Form(None) ): logging.info(f"UPSERT primary_triage for patient_no: {patient_no}") cnx = get_db_connection() cursor = cnx.cursor() try: # ✅ [UPSERT] 레코드 없으면 생성 ensure_patient_record_exists(cursor, patient_no) query = "UPDATE primary_triage SET urgency_level = %s, checklist_data = %s WHERE patient_no = %s" cursor.execute(query, (urgency_level, checklist_data, patient_no)) cnx.commit() return {"status": "success", "message": "Primary triage data saved."} except Exception as e: cnx.rollback() raise HTTPException(status_code=500, detail=f"DB Error: {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), images: List[UploadFile] = File(None), # [수정] 여러 장 수신 existing_images: List[str] = Form(None), # [추가] 기존 이미지 유지용 checklist_data: Optional[str] = Form(None), save_state: Optional[int] = Form(None) ): cnx = get_db_connection() cursor = cnx.cursor() try: # --- 이미지 경로 처리 (기존 로직과 동일하게 통합) --- final_paths = [] if existing_images: final_paths.extend([path.strip() for path in existing_images if path.strip()]) if images: os.makedirs(UPLOAD_DIR, exist_ok=True) for img in images: if img.filename: u_name = f"{uuid.uuid4()}{os.path.splitext(img.filename)[1]}" full_path = os.path.join(UPLOAD_DIR, u_name) with open(full_path, "wb") as buffer: shutil.copyfileobj(img.file, buffer) final_paths.append(f"/sos/uploads/{u_name}") combined_img_string = ",".join(final_paths) if final_paths else None ensure_patient_record_exists(cursor, patient_no) 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, checklist_data=%s, save_state=COALESCE(%s, save_state) WHERE patient_no = %s """ vals = ( 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, combined_img_string, checklist_data, save_state, patient_no ) cursor.execute(query, vals) cnx.commit() return {"status": "success"} 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), images: List[UploadFile] = File(None), # [수정] 여러 장 수신 existing_images: List[str] = Form(None), # [추가] 기존 이미지 유지용 checklist_data: Optional[str] = Form(None), save_state: Optional[int] = Form(None) ): cnx = get_db_connection() cursor = cnx.cursor() try: # --- 이미지 경로 처리 (기존 로직과 동일하게 통합) --- final_paths = [] if existing_images: final_paths.extend([path.strip() for path in existing_images if path.strip()]) if images: os.makedirs(UPLOAD_DIR, exist_ok=True) for img in images: if img.filename: u_name = f"{uuid.uuid4()}{os.path.splitext(img.filename)[1]}" full_path = os.path.join(UPLOAD_DIR, u_name) with open(full_path, "wb") as buffer: shutil.copyfileobj(img.file, buffer) final_paths.append(f"/sos/uploads/{u_name}") combined_img_string = ",".join(final_paths) if final_paths else None ensure_patient_record_exists(cursor, patient_no) 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, checklist_data=%s, save_state=COALESCE(%s, save_state) WHERE patient_no = %s """ vals = ( 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, combined_img_string, checklist_data, save_state, patient_no ) cursor.execute(query, vals) cnx.commit() return {"status": "success"} 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), images: List[UploadFile] = File(None), # [수정] 여러 장 수신 existing_images: List[str] = Form(None), # [추가] 기존 이미지 유지용 checklist_data: Optional[str] = Form(None) ): cnx = get_db_connection() cursor = cnx.cursor() try: # --- 이미지 경로 처리 (기존 로직과 동일하게 통합) --- final_paths = [] if existing_images: final_paths.extend([path.strip() for path in existing_images if path.strip()]) if images: os.makedirs(UPLOAD_DIR, exist_ok=True) for img in images: if img.filename: u_name = f"{uuid.uuid4()}{os.path.splitext(img.filename)[1]}" full_path = os.path.join(UPLOAD_DIR, u_name) with open(full_path, "wb") as buffer: shutil.copyfileobj(img.file, buffer) final_paths.append(f"/sos/uploads/{u_name}") combined_img_string = ",".join(final_paths) if final_paths else None ensure_patient_record_exists(cursor, patient_no) 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, checklist_data=%s WHERE patient_no = %s """ vals = ( 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, combined_img_string, checklist_data, patient_no ) cursor.execute(query, vals) cnx.commit() return {"status": "success"} 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 complete_list: Optional[int] = 0 # --- トリアージ一覧를 위한 환자 목록 API --- # --- 트리아지 목록 조회 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: # [핵심 수정] # sick_and_wounded_list 테이블을 LEFT JOIN 하고, # WHERE 절에서 'swl.id IS NULL' 조건을 걸어 # "상병자 리스트에 아직 등록되지 않은 환자"만 가져옵니다. 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, l.complete_list FROM patient p LEFT JOIN list l ON p.No = l.patient_no LEFT JOIN sick_and_wounded_list swl ON p.No = swl.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 WHERE p.name2 IS NOT NULL AND swl.id IS 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 complete_list: Optional[int] = 0 @app.get("/sos/api/incidents", response_model=List[Incident]) async def get_incident_list(): cnx = get_db_connection() cursor = cnx.cursor(dictionary=True) try: # [핵심 수정 사항] # 1. sick_and_wounded_list를 'INNER JOIN'으로 변경 # -> 상병자 리스트(sick_and_wounded_list)에 데이터가 없으면(트리아지만 작성 시) 조회되지 않음. # 2. 트리아지 테이블(pt, st1~3)은 다시 포함시켜서 긴급도 정보가 뜨도록 함. 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, swl.complete_list FROM list l INNER JOIN sick_and_wounded_list swl ON l.patient_no = swl.patient_no 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() # --- [추가] menu.html: 트리아지 목록 이송 상태 조회 API --- @app.get("/sos/api/transfer_status_list") async def get_transfer_status_list(): """ 모든 환자에 대한 이송 상태 (transfer_state) 정보를 반환합니다. (車内収容, 現場出発, 病院到着, 医師引継 상태) """ logging.info("Fetching all transfer status data.") cnx = get_db_connection() cursor = cnx.cursor(dictionary=True) try: # transfer_state 테이블에서 필요한 4가지 상태 컬럼과 patient_no를 조회 query = """ SELECT patient_no, patient_loaded_time, depart_scene_time, arrival_hospital_time, handover_to_doctor_time FROM transfer_state ORDER BY patient_no DESC; """ cursor.execute(query) results = cursor.fetchall() if not results: logging.info("No transfer status records found.") return [] return results except Exception as e: logging.error(f"Error fetching transfer status list: {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']) # --- 모든 이미지를 8개씩 끊어서 여러 행에 오른쪽 나열 --- image_fields = ['img', 'st1_img', 'st2_img', 'st3_img', 'memo_img'] all_paths = [] for f in image_fields: if data.get(f): for p in data[f].split(','): fname = os.path.basename(p.strip()) fpath = os.path.join(UPLOAD_DIR, fname) if os.path.exists(fpath): all_paths.append(fpath) start_row = 47 # 시작 행 번호 cols = ['A', 'H', 'O', 'V', 'AC', 'AJ', 'AQ', 'AX'] # 사용할 열들 row_height_increment = 6 # 한 줄(8장)이 끝난 후 아래로 내려갈 행 간격 for i, p in enumerate(all_paths): col_idx = i % len(cols) # 0~7 사이의 인덱스 (열 결정) row_group = i // len(cols) # 8장마다 1씩 증가 (행 그룹 결정) # 현재 이미지가 들어갈 행 번호 계산 current_row = start_row + (row_group * row_height_increment) target_cell = f'{cols[col_idx]}{current_row}' try: img_obj = Image(p) ratio = img_obj.height / img_obj.width img_obj.width = 180 # 가로 크기 고정 img_obj.height = 180 * ratio sheet.add_image(img_obj, target_cell) except Exception as e: logging.error(f"이미지 삽입 실패 ({p}): {e}") continue 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: query = """ SELECT p.No, p.age, p.gender, p.name2 AS name_kanji, p.address, p.receiving_hospital_name, p.transport_agency_name, 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 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.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="登録された傷病者がいません") template_path = os.path.join(TEMPLATE_DIR, "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 # 색상 및 글꼴 설정 (가독성을 위해 진한 노란색 사용) font_red = Font(color="FF0000", bold=True) # 赤 font_yellow = Font(color="FFC000", bold=True) # 黄 (진한 귤색 계열) font_green = Font(color="00B050", bold=True) # 緑 font_black = Font(color="000000", bold=True) # 黒 font_map = {'赤': font_red, '黄': font_yellow, '緑': font_green, '黒': font_black} # 상단 공통 정보 입력 first = patients[0] ws['I1'], ws['K1'], ws['M1'], ws['N1'], ws['P1'] = \ first.get('month'), first.get('day'), first.get('am_pm'), first.get('time_h'), first.get('time_m') def get_triage_text(level): mapping = {'R': '赤', 'Y': '黄', 'G': '緑', 'B': '黒'} return mapping.get(level, '') # 리스트 데이터 입력 및 색상 적용 for idx, p in enumerate(patients): row = 4 + idx ws[f'A{row}'] = idx + 1 ws[f'B{row}'] = p['No'] # C, D, E, F열 (각 트리아지 단계별 색상 지정) triage_levels = [ ('C', p.get('primary_urgency')), ('D', p.get('secondary1_urgency')), ('E', p.get('secondary2_urgency')), ('F', p.get('secondary3_urgency')) ] for col, level in triage_levels: text = get_triage_text(level) cell = ws[f'{col}{row}'] cell.value = text if text in font_map: cell.font = font_map[text] # 텍스트에 맞는 색상 적용 # 기타 정보 입력 ws[f'G{row}'] = p['age'] or '' ws[f'H{row}'] = p['gender'] or '' ws[f'I{row}'] = p.get('name_kanji') or '' ws[f'J{row}'] = p.get('address') or '' ws[f'S{row}'] = p.get('receiving_hospital_name') or '' ws[f'T{row}'] = p.get('transport_agency_name') or '' ws[f'W{row}'] = p.get('time_h') or '' ws[f'Y{row}'] = p.get('time_m') or '' wb.save(excel_output_path) # PDF 변환 실행 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"PDF作成中にエラーが発生: {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() # [추가] triage 페이지 자동 저장용 엔드포인트 (UPSERT 적용) @app.post("/sos/api/update_patient_data") async def update_patient_data( No: str = Form(..., alias="No"), # JS에서 'No'로 보냄 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), images: List[UploadFile] = File(None) # [수정] 여러 장 수신 ): logging.info(f"UPSERT patient data for No: {No}") cnx = get_db_connection() cursor = cnx.cursor() try: # 기존 이미지 목록을 유지하면서 새 이미지를 추가하기 위해 현재 DB 값 조회 cursor.execute("SELECT img FROM patient WHERE No = %s", (No,)) row = cursor.fetchone() existing_img_str = row[0] if row else None final_paths = existing_img_str.split(",") if existing_img_str else [] # 새 이미지 파일들 저장 if images: os.makedirs(UPLOAD_DIR, exist_ok=True) for img in images: if img.filename: unique_filename = f"{uuid.uuid4()}{os.path.splitext(img.filename)[1]}" full_path = os.path.join(UPLOAD_DIR, unique_filename) with open(full_path, "wb") as buffer: shutil.copyfileobj(img.file, buffer) final_paths.append(f"/sos/uploads/{unique_filename}") combined_img_string = ",".join(final_paths) if final_paths else None # 날짜/요일 계산 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 "未入力" # === UPSERT 로직 === cursor.execute("SELECT No FROM patient WHERE No = %s", (No,)) if not cursor.fetchone(): # [INSERT] 레코드가 없으면 새로 생성 insert_query = """ INSERT INTO patient ( No, name1, name2, age, gender, 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, img ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) """ cursor.execute(insert_query, ( No, name1, effective_name2, age, gender, 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, combined_img_string )) # 관련 테이블 초기화 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,)) cursor.execute("INSERT INTO patient_info (patient_no, urgency_level) VALUES (%s, %s)", (No, urgency_level)) cursor.execute("INSERT INTO list (patient_no, date_time, day_of_week, name2) VALUES (%s, %s, %s, %s)", (No, date_time_str, day_of_week, effective_name2)) else: # [UPDATE] 레코드가 있으면 수정 img_clause = ", img = %s" if combined_img_string else "" update_query = f""" UPDATE patient SET name1=%s, name2=%s, age=%s, gender=%s, address=%s, phone_number=%s, month=%s, day=%s, am_pm=%s, time_h=%s, time_m=%s, triage_officer_name1=%s, triage_officer_name2=%s, transport_agency_name=%s, receiving_hospital_name=%s {img_clause} WHERE No = %s """ params = [name1, effective_name2, age, gender, address, phone_number, month, day, am_pm, time_h, time_m, triage_officer_name1, triage_officer_name2, transport_agency_name, receiving_hospital_name] if combined_img_string: params.append(combined_img_string) params.append(No) cursor.execute(update_query, tuple(params)) # list 테이블 이름 동기화 cursor.execute("UPDATE list SET name2 = %s WHERE patient_no = %s", (effective_name2, No)) cnx.commit() return {"status": "success", "message": f"Patient data saved (No={No})"} except Exception as e: cnx.rollback() raise HTTPException(status_code=500, detail=f"DB Error: {str(e)}") finally: cursor.close() cnx.close() # [수정] 사안 완료 버튼 API (기능 변경: 트리아지 리스트 -> 상병자 리스트로 이동) @app.post("/sos/api/complete_incident") async def complete_incident(patient_no: str = Form(...)): cnx = get_db_connection() cursor = cnx.cursor() try: # 1. 이미 상병자 리스트(sick_and_wounded_list)에 있는지 확인 check_query = "SELECT id FROM sick_and_wounded_list WHERE patient_no = %s" cursor.execute(check_query, (patient_no,)) if cursor.fetchone(): # 이미 이동된 상태라면 메시지만 반환 (혹은 중복 처리 방지) logging.info(f"Patient {patient_no} is already in sick_and_wounded_list.") return {"status": "success", "message": "Already moved to Sick and Wounded List."} # 2. 없다면 INSERT (이 동작 하나로 리스트 이동 효과가 발생함) insert_query = "INSERT INTO sick_and_wounded_list (patient_no) VALUES (%s)" cursor.execute(insert_query, (patient_no,)) logging.info(f"Moved patient {patient_no} from Triage List to Sick List (Manual Button)") # 기존의 'UPDATE list SET complete_list = 1' (블러 처리) 코드는 삭제 # 이제 버튼을 누르면 블러가 되는 게 아니라 리스트에서 사라지고 다른 리스트로 이동 cnx.commit() return {"status": "success", "message": "Incident moved to Sick and Wounded List."} except Exception as e: cnx.rollback() logging.error(f"Error completing incident for {patient_no}: {e}") raise HTTPException(status_code=500, detail=str(e)) finally: cursor.close() cnx.close() # [추가] 공통 헬퍼 함수: 보고서 파일 존재 여부 확인 def report_exists(prefix: str, patient_no: str) -> bool: """ /home/air/sos/templates/ 폴더 안에 prefix_patient_no.pdf (예: reportA_X.pdf) 가 존재하는지 확인 """ # TEMPLATE_DIR은 save_all_times.py 파일 상단에 정의되어 있음 path = os.path.join(TEMPLATE_DIR, f"{prefix}_{patient_no}.pdf") return os.path.exists(path) # [추가] 작성 현황 @app.get("/sos/api/report_status_list") async def get_report_status_list(): """ 모든 환자에 대해 4가지 필수 보고서의 작성 현황을 반환합니다. (patient_no, A, B, C, Triage의 bool 상태 포함) """ cnx = get_db_connection() cursor = cnx.cursor(dictionary=True) try: # 1. 모든 활성 patient_no를 patient 테이블에서 조회 query = "SELECT No FROM patient" cursor.execute(query) patient_nos = cursor.fetchall() results = [] # 2. 각 환자 번호별로 4가지 파일 존재 여부 확인 for p in patient_nos: patient_no = p.get('No') # [수정]: patient_no가 유효한 문자열인지 확인 if not patient_no or not isinstance(patient_no, str): logging.warning("Skipping non-string/None patient_no found in DB.") continue try: # [수정]: 파일 존재 여부 확인 중 try-except로 오류를 방어 status = { "patient_no": patient_no, "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), } results.append(status) except Exception as file_error: # 파일 확인 중 오류 발생 시, 해당 환자는 보고서 미완료(False)로 처리하고 계속 진행 logging.error(f"File check failed for patient_no: {patient_no}. Error: {file_error}") results.append({ "patient_no": patient_no, "A": False, "B": False, "C": False, "triage": False }) return results except Exception as e: # DB 쿼리 자체 실패 등 전체적인 오류만 500으로 던짐 logging.error(f"Error fetching report status list: {e}", exc_info=True) raise HTTPException(status_code=500, detail=f"Database error: {str(e)}") finally: cursor.close() cnx.close() # [추가] time 테이블 데이터 조회 @app.get("/sos/api/get_time_data/{patient_no}") async def get_time_data_full(patient_no: str): logging.info(f"Fetching FULL time data for edit: {patient_no}") cnx = get_db_connection() cursor = cnx.cursor(dictionary=True) try: # [핵심 수정] 특정 컬럼이 아니라 '*'를 써서 모든 데이터를 다 가져옵니다. query = "SELECT * FROM time WHERE patient_no = %s" cursor.execute(query, (patient_no,)) row = cursor.fetchone() if row: return {"status": "success", "data": row} else: return {"status": "success", "data": {}} except Exception as e: logging.error(f"Error fetching time data: {e}", exc_info=True) raise HTTPException(status_code=500, detail=str(e)) finally: cursor.close() cnx.close() # [추가] call_received 데이터 조회 @app.get("/sos/api/get_call_received_data/{patient_no}") async def get_call_received_data(patient_no: str): logging.info(f"Fetching FULL call_received data for edit: {patient_no}") cnx = get_db_connection() cursor = cnx.cursor(dictionary=True) try: # call_received 테이블의 모든 컬럼 조회 query = "SELECT * FROM call_received WHERE patient_no = %s" cursor.execute(query, (patient_no,)) row = cursor.fetchone() if row: return {"status": "success", "data": row} else: return {"status": "success", "data": {}} except Exception as e: logging.error(f"Error fetching call_received data: {e}", exc_info=True) raise HTTPException(status_code=500, detail=str(e)) finally: cursor.close() cnx.close() # [추가] patient_info 데이터 조회 @app.get("/sos/api/get_patient_info_data/{patient_no}") async def get_patient_info_data(patient_no: str): logging.info(f"Fetching FULL patient_info data for edit: {patient_no}") cnx = get_db_connection() cursor = cnx.cursor(dictionary=True) try: # patient_info 테이블의 모든 컬럼 조회 query = "SELECT * FROM patient_info WHERE patient_no = %s" cursor.execute(query, (patient_no,)) row = cursor.fetchone() if row: return {"status": "success", "data": row} else: return {"status": "success", "data": {}} except Exception as e: logging.error(f"Error fetching patient_info data: {e}", exc_info=True) raise HTTPException(status_code=500, detail=str(e)) finally: cursor.close() cnx.close() # [추가] contact_time_T 데이터 조회 @app.get("/sos/api/get_contact_time_T_data/{patient_no}") async def get_contact_time_T_data(patient_no: str): logging.info(f"Fetching FULL contact_time_T data for edit: {patient_no}") cnx = get_db_connection() cursor = cnx.cursor(dictionary=True) try: # contact_time_T 테이블의 모든 컬럼 조회 query = "SELECT * FROM contact_time_T WHERE patient_no = %s" cursor.execute(query, (patient_no,)) row = cursor.fetchone() if row: return {"status": "success", "data": row} else: return {"status": "success", "data": {}} except Exception as e: logging.error(f"Error fetching contact_time_T data: {e}", exc_info=True) raise HTTPException(status_code=500, detail=str(e)) finally: cursor.close() cnx.close() # [추가] memo 데이터 조회 # [수정] 이미지 경로 변환 로직 추가 @app.get("/sos/api/get_memo_data/{patient_no}") async def get_memo_data(patient_no: str): logging.info(f"Fetching FULL memo data for edit: {patient_no}") cnx = get_db_connection() cursor = cnx.cursor(dictionary=True) try: # memo 테이블의 모든 컬럼 조회 query = "SELECT * FROM memo WHERE patient_no = %s" cursor.execute(query, (patient_no,)) row = cursor.fetchone() if row: result_data = {"text": row["text"], "images": []} if row.get("img"): # 쉼표로 구분된 경로들을 리스트로 변환 paths = row["img"].split(",") for i, path in enumerate(paths): if path.strip(): result_data["images"].append({ "id": f"server_img_{i}", "url": path.strip() }) return {"status": "success", "data": result_data} return {"status": "success", "data": {}} except Exception as e: logging.error(f"Error fetching memo data: {e}", exc_info=True) raise HTTPException(status_code=500, detail=str(e)) finally: cursor.close() cnx.close() # [추가] before_arrival 데이터 조회 @app.get("/sos/api/get_before_arrival_data/{patient_no}") async def get_before_arrival_data(patient_no: str): logging.info(f"Fetching FULL before_arrival data for edit: {patient_no}") cnx = get_db_connection() cursor = cnx.cursor(dictionary=True) try: # before_arrival 테이블의 모든 컬럼 조회 query = "SELECT * FROM before_arrival WHERE patient_no = %s" cursor.execute(query, (patient_no,)) row = cursor.fetchone() if row: return {"status": "success", "data": row} else: return {"status": "success", "data": {}} except Exception as e: logging.error(f"Error fetching before_arrival data: {e}", exc_info=True) raise HTTPException(status_code=500, detail=str(e)) finally: cursor.close() cnx.close() # [추가] report 데이터 조회 @app.get("/sos/api/get_report_data/{patient_no}") async def get_report_data(patient_no: str): logging.info(f"Fetching FULL report data for edit: {patient_no}") cnx = get_db_connection() cursor = cnx.cursor(dictionary=True) try: # report 테이블의 모든 컬럼 조회 query = "SELECT * FROM report WHERE patient_no = %s" cursor.execute(query, (patient_no,)) row = cursor.fetchone() if row: return {"status": "success", "data": row} else: return {"status": "success", "data": {}} except Exception as e: logging.error(f"Error fetching report data: {e}", exc_info=True) raise HTTPException(status_code=500, detail=str(e)) finally: cursor.close() cnx.close() # [수정] 환자 기본 정보 (patient 데이터 조회) @app.get("/sos/api/get_patient_data/{patient_no}") async def get_patient_data(patient_no: str): logging.info(f"Fetching patient data for: {patient_no}") cnx = get_db_connection() cursor = cnx.cursor(dictionary=True) try: # patient 테이블만 조회 query = "SELECT * FROM patient WHERE No = %s" cursor.execute(query, (patient_no,)) row = cursor.fetchone() if row: # 이미지 경로 변환 (절대경로 -> 웹 URL) if row.get("img"): raw_paths = row["img"].split(",") converted_paths = [] for path in raw_paths: if "/home/" in path: # 절대경로인 경우만 변환 converted_paths.append(f"/sos/uploads/{os.path.basename(path)}") else: converted_paths.append(path) row["img"] = ",".join(converted_paths) return {"status": "success", "data": row} else: return {"status": "success", "data": {}} except Exception as e: logging.error(f"Error fetching patient data: {e}", exc_info=True) raise HTTPException(status_code=500, detail=str(e)) finally: cursor.close() cnx.close() # 2. 1차 트리아지 조회 (primary_triage.html 용) @app.get("/sos/api/get_primary_triage_data/{patient_no}") async def get_primary_triage_data(patient_no: str): logging.info(f"Fetching primary triage data for: {patient_no}") cnx = get_db_connection() cursor = cnx.cursor(dictionary=True) try: query = "SELECT * FROM primary_triage WHERE patient_no = %s" cursor.execute(query, (patient_no,)) row = cursor.fetchone() return {"status": "success", "data": row if row else {}} except Exception as e: logging.error(f"Error fetching primary triage: {e}") raise HTTPException(status_code=500, detail=str(e)) finally: cursor.close() cnx.close() # 3. 2차 트리아지 (1, 2, 3) 공통 함수 생성 헬퍼 async def get_secondary_triage_common(patient_no: str, table_name: str): logging.info(f"Fetching {table_name} for: {patient_no}") cnx = get_db_connection() cursor = cnx.cursor(dictionary=True) try: query = f"SELECT * FROM {table_name} WHERE patient_no = %s" cursor.execute(query, (patient_no,)) row = cursor.fetchone() if row: # [핵심 수정] 이미지 경로가 쉼표로 연결된 문자열인 경우를 처리 if row.get("img"): raw_paths = row["img"].split(",") converted_paths = [] for path in raw_paths: path = path.strip() if not path: continue # 절대 경로인 경우 웹 경로로 변환, 이미 웹 경로면 유지 if "/home/" in path: converted_paths.append(f"/sos/uploads/{os.path.basename(path)}") else: converted_paths.append(path) # 다시 쉼표로 합쳐서 전달 row["img"] = ",".join(converted_paths) return {"status": "success", "data": row} return {"status": "success", "data": {}} finally: cursor.close() cnx.close() # 3-1. 2차 트리아지 ① (secondary_triage_1.html) @app.get("/sos/api/get_secondary_triage_1_data/{patient_no}") async def get_secondary_triage_1_data(patient_no: str): return await get_secondary_triage_common(patient_no, "secondary_triage_1") # 3-2. 2차 트리아지 ② (secondary_triage_2.html) @app.get("/sos/api/get_secondary_triage_2_data/{patient_no}") async def get_secondary_triage_2_data(patient_no: str): return await get_secondary_triage_common(patient_no, "secondary_triage_2") # 3-3. 2차 트리아지 ③ (secondary_triage_3.html) @app.get("/sos/api/get_secondary_triage_3_data/{patient_no}") async def get_secondary_triage_3_data(patient_no: str): return await get_secondary_triage_common(patient_no, "secondary_triage_3") @app.get("/sos/api/patient_list") async def get_patient_list(): conn = get_db_connection() cursor = conn.cursor(dictionary=True) try: # 1. SQLクエリ（変更なし） # listテーブルを基準に必要な情報を一度に取得します。 sql = """ SELECT l.patient_no, -- 患者基本情報 (patient_info) pi.patient_name2 as name, pi.patient_name1 as kana, pi.age, pi.gender, pi.chief_complaint, -- トリアージ情報（DBにはG, R, Y, Bで保存されています） pt.urgency_level as triage_1, st1.urgency_level as triage_2, st2.urgency_level as triage_3, st3.urgency_level as triage_4 FROM list l -- データがなくてもlistは表示する必要があるため、LEFT JOINを使用 LEFT JOIN patient_info pi ON l.patient_no = pi.patient_no 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.date_time DESC """ cursor.execute(sql) rows = cursor.fetchall() results = [] for row in rows: # 2. データ加工 # DBの値がすでにG, R, Y, Bなので、そのままリストに格納します。 # データがない場合(NULL)、PythonではNoneになり、JSONではnullに変換されます。 triage_data = [ row['triage_1'], row['triage_2'], row['triage_3'], row['triage_4'] ] results.append({ "patient_no": row['patient_no'], # テキストデータがない場合は "-" または空文字で処理 "name": row['name'] if row['name'] else "-", "kana": row['kana'] if row['kana'] else "-", "age": row['age'] if row['age'] else "", "gender": row['gender'] if row['gender'] else "", "chief_complaint": row['chief_complaint'] if row['chief_complaint'] else "", # トリアージ配列 (例: ['R', 'G', None, None]) "triage": triage_data }) return JSONResponse(content=results) except mysql.connector.Error as err: logging.error(f"DB Error: {err}") return JSONResponse(content={"error": str(err)}, status_code=500) finally: if cursor: cursor.close() if conn: conn.close() @app.get("/sos/api/patient_detail") async def get_patient_detail(patient_no: str): conn = get_db_connection() cursor = conn.cursor(dictionary=True) try: sql = """ SELECT -- 1. 基本ヘッダー情報 p.No as patient_no, pi.patient_name2 as name, pi.patient_name1 as kana, pi.age, pi.gender, pt.urgency_level as triage_1, st1.urgency_level as triage_2, st2.urgency_level as triage_3, st3.urgency_level as triage_4, -- 2. ピンクエリア cr.call_received_date, cr.call_method, cr.monthly_number, cr.request_location, cr.incident_type, CONCAT(t.call_received_h, ':', t.call_received_m) as time_call_received, CONCAT(t.dispatch_h, ':', t.dispatch_m) as time_dispatch, CONCAT(t.arrival_on_scene_h, ':', t.arrival_on_scene_m) as time_arrival_scene, CONCAT(t.patient_contact_h, ':', t.patient_contact_m) as time_contact, CONCAT(t.transport_start_h, ':', t.transport_start_m) as time_transport_start, CONCAT(t.patient_loaded_h, ':', t.patient_loaded_m) as time_loaded, CONCAT(t.depart_scene_h, ':', t.depart_scene_m) as time_depart, CONCAT(t.arrival_hospital_h, ':', t.arrival_hospital_m) as time_arrival_hospital, CONCAT(t.handover_to_doctor_h, ':', t.handover_to_doctor_m) as time_handover, CONCAT(t.return_from_site_h, ':', t.return_from_site_m) as time_return_site, CONCAT(t.return_to_station_h, ':', t.return_to_station_m) as time_return_station, CONCAT(t.transfer1_h, ':', t.transfer1_m) as time_transfer1, CONCAT(t.transfer2_h, ':', t.transfer2_m) as time_transfer2, t.dispatch_location, t.doctor_car_detail, -- 3. 黄色エリア CONCAT(pi.birth_year, '/', pi.birth_month, '/', pi.birth_day) as dob, pi.occupation, pi.address, pi.phone_number as tel, pi.companion_name, pi.relation, pi.contact_info, pi.urgency_level as pi_urgency, 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, -- 4. 青エリア r.diagnosis_name, r.severity, r.hospital_selection_reason, CONCAT(r.distance_station_to_scene_L, '.', r.distance_station_to_scene_R, ' km') as dist_station_scene, CONCAT(r.distance_scene_to_hospital_L, '.', r.distance_scene_to_hospital_R, ' km') as dist_scene_hospital, CONCAT(r.distance_hospital_to_station_L, '.', r.distance_hospital_to_station_R, ' km') as dist_hospital_station, CONCAT(r.distance_station_roundtrip_L, '.', r.distance_station_roundtrip_R, ' km') as dist_roundtrip, r.first_doctor_name, r.related_organization, -- 5. 肌色エリア (contact_time_T) CONCAT(ct.contact_time_h, ':', ct.contact_time_m) as ct_time, ct.consciousness_jcs as ct_jcs, ct.consciousness_e as ct_e, ct.consciousness_v as ct_v, ct.consciousness_m as ct_m, CONCAT(ct.respiration_rate, ' 回/分') as ct_respiration, ct.respiration_condition as ct_respiration_cond, CONCAT(ct.pulse_rate, ' 回/分') as ct_pulse, ct.pulse_1 as ct_pulse_1, ct.pulse_2 as ct_pulse_2, CONCAT(ct.temperature_L, '.', ct.temperature_R, ' ℃') as ct_temp, ct.temperature_text as ct_temp_text, CONCAT(ct.bp_right_1, '/', ct.bp_right_2, ' mmHg') as ct_bp_right, CONCAT(ct.bp_left_1, '/', ct.bp_left_2, ' mmHg') as ct_bp_left, CONCAT(ct.spo2_left, ' % -> ', ct.spo2_right, ' %') as ct_spo2, CONCAT(ct.oxygen_flow_rate, ' L/分') as ct_oxygen, ct.oxygen_use as ct_oxygen_use, ct.ecg_status as ct_ecg, ct.auscultation as ct_auscultation, CONCAT(ct.pupil_right_size, ' mm / ', ct.pupil_right_reaction) as ct_pupil_right, CONCAT(ct.pupil_left_size, ' mm / ', ct.pupil_left_reaction) as ct_pupil_left, ct.gaze_deviation, ct.visual_impairment, ct.palpebral_conjunctiva, ct.nystagmus, ct.convulsion, ct.affected_area_condition, ct.skin_condition, ct.paralysis, ct.paralysis_area, ct.vomit, CONCAT(ct.vomit_count, ' 回') as ct_vomit_count, ct.diarrhea, ct.first_aid, ct.first_aid_other, ct.transport_position, ct.adl, -- 6. 緑エリア (before_arrival) CONCAT(ba.contact_time_h, ':', ba.contact_time_m) as ba_time, ba.consciousness_jcs as ba_jcs, ba.consciousness_e as ba_e, ba.consciousness_v as ba_v, ba.consciousness_m as ba_m, CONCAT(ba.respiration_rate, ' 回/分') as ba_respiration, ba.respiration_condition as ba_respiration_cond, CONCAT(ba.pulse_rate, ' 回/分') as ba_pulse, ba.pulse_1 as ba_pulse_1, ba.pulse_2 as ba_pulse_2, CONCAT(ba.temperature_L, '.', ba.temperature_R, ' ℃') as ba_temp, ba.temperature_text as ba_temp_text, CONCAT(ba.bp_right_1, '/', ba.bp_right_2, ' mmHg') as ba_bp_right, CONCAT(ba.bp_left_1, '/', ba.bp_left_2, ' mmHg') as ba_bp_left, CONCAT(ba.spo2_left, ' % -> ', ba.spo2_right, ' %') as ba_spo2, CONCAT(ba.oxygen_flow_rate, ' L/分') as ba_oxygen, ba.oxygen_use as ba_oxygen_use, ba.ecg_status as ba_ecg, ba.auscultation as ba_auscultation, -- メモ m.text as memo_text, -- 7. 画像パス p.img as img_1, st1.img as img_2, st2.img as img_3, st3.img as img_4 FROM list l LEFT JOIN patient p ON l.patient_no = p.No LEFT JOIN patient_info pi ON l.patient_no = pi.patient_no LEFT JOIN call_received cr ON l.patient_no = cr.patient_no LEFT JOIN time t ON l.patient_no = t.patient_no LEFT JOIN report r ON l.patient_no = r.patient_no LEFT JOIN contact_time_T ct ON l.patient_no = ct.patient_no LEFT JOIN before_arrival ba ON l.patient_no = ba.patient_no LEFT JOIN memo m ON l.patient_no = m.patient_no 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 WHERE l.patient_no = %s """ cursor.execute(sql, (patient_no,)) row = cursor.fetchone() if not row: return JSONResponse(content={"error": "Patient not found"}, status_code=404) # 応急処置の括弧処理 first_aid_text = row['first_aid'] if row['first_aid'] else "" if row['first_aid_other']: first_aid_text += f"({row['first_aid_other']})" row['first_aid_full'] = first_aid_text return JSONResponse(content=row) except mysql.connector.Error as err: logging.error(f"DB Error: {err}") return JSONResponse(content={"error": str(err)}, status_code=500) finally: if cursor: cursor.close() if conn: conn.close() @app.post("/sos/api/login/fire") async def login_fire(req: AdminLoginRequest): conn = get_db_connection() cursor = conn.cursor(dictionary=True) try: sql = "SELECT * FROM ambulance_teams WHERE team_code = %s AND call_name = %s" cursor.execute(sql, (req.id, req.password)) user = cursor.fetchone() if user: return JSONResponse(content={"message": "Login Success", "user": user['team_name']}) else: return JSONResponse(content={"message": "Login Failed"}, status_code=401) finally: cursor.close() conn.close() @app.post("/sos/api/login/medical") async def login_medical(req: AdminLoginRequest): conn = get_db_connection() cursor = conn.cursor(dictionary=True) try: sql = "SELECT * FROM medical_users WHERE login_id = %s AND password = %s" cursor.execute(sql, (req.id, req.password)) user = cursor.fetchone() if user: return JSONResponse(content={"message": "Login Success"}) else: return JSONResponse(content={"message": "Login Failed"}, status_code=401) finally: cursor.close() conn.close() # [추가] 사안 완료 처리 API (sick_and_wounded_list 테이블 업데이트용) @app.post("/sos/api/complete_sick_wounded") async def complete_sick_wounded(patient_no: str = Form(...)): cnx = get_db_connection() cursor = cnx.cursor() try: # [수정됨] list 테이블이 아니라 sick_and_wounded_list 테이블을 업데이트합니다. query = "UPDATE sick_and_wounded_list SET complete_list = 1 WHERE patient_no = %s" cursor.execute(query, (patient_no,)) cnx.commit() return {"status": "success", "message": "Incident completed (sick_and_wounded_list updated)."} except Exception as e: cnx.rollback() raise HTTPException(status_code=500, detail=str(e)) finally: cursor.close() cnx.close()