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-tester') DB_PASSWORD = os.environ.get('DB_PASSWORD', 'PassWord12!!') DB_NAME = os.environ.get('DB_NAME', 'sos_pdf') DB_CONFIG = { 'host': 'localhost', 'user': 'sos-tester', 'password': 'PassWord12!!', 'database': 'sos_pdf', 'port': 3306 } # 폴더명 설정 PREFIX = "/sos-pdf" # --- 경로 정보 --- UPLOAD_DIR = "/home/air" + PREFIX + "/uploads/" TEMPLATE_DIR = "/home/air"+ PREFIX + "/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-pdf/uploads)로 연결 app.mount(PREFIX + "/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(PREFIX + "/api/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(PREFIX + "/api/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(PREFIX + "/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"{PREFIX}/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(PREFIX + "/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(PREFIX + "/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(PREFIX + "/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(PREFIX + "/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(PREFIX + "/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(PREFIX + "/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"{PREFIX}/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(PREFIX + "/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(PREFIX + "/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(PREFIX + "/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(PREFIX + "/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"{PREFIX}/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(PREFIX + "/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"{PREFIX}/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(PREFIX + "/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"{PREFIX}/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(PREFIX + "/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(PREFIX + "/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(PREFIX + "/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-test/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(PREFIX + "/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(PREFIX + "/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() logging.info(f"[REPORT] patient_no={patient_no}") logging.info(f"[REPORT] image_fields_raw=" + str({k: data.get(k) for k in ['img','st1_img','st2_img','st3_img','memo_img']})) logging.info(f"[REPORT] UPLOAD_DIR={UPLOAD_DIR}") 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, "20251226_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) # [수정 1] 시트 이름으로 정확하게 가져오기 target_sheet_name = "消防機関用" if target_sheet_name in workbook.sheetnames: sheet = workbook[target_sheet_name] else: # 만약 이름이 다르면 첫 번째 시트를 가져오되 경고 로그 남기기 logging.warning(f"'{target_sheet_name}' 시트를 찾을 수 없어 첫 번째 시트를 사용합니다.") 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 # --- [1페이지] 상단: 입전 정보 채우기 --- # 1. 날짜 데이터 가공 (DB의 날짜를 월/일/요일로 쪼개서 준비) if data.get('call_received_date'): try: cr_date = data['call_received_date'] if isinstance(cr_date, str): cr_date = datetime.strptime(cr_date, '%Y-%m-%d') # 가공된 데이터를 data 딕셔너리에 추가 data['calc_month'] = cr_date.month data['calc_day'] = cr_date.day weekdays = ["月", "火", "水", "木", "金", "土", "日"] data['calc_weekday'] = weekdays[cr_date.weekday()] except Exception as e: logging.error(f"날짜 변환 오류: {e}") # 2. 매핑 정의 (데이터 키 : 엑셀 좌표) # [수정 2] 정확한 좌표 (L5, S5...) 적용 PAGE1_HEADER_MAP = { 'calc_month': 'J5', # 월 'calc_day': 'S5', # 일 'calc_weekday': 'AC5', # 요일 'call_method': 'AQ5', # 입전방법 'monthly_number': 'CI5' # 월간번호 } # 3. 데이터 쓰기 (write_to_cell 활용) for key, cell_addr in PAGE1_HEADER_MAP.items(): # data에 해당 키가 있고 값이 None이 아닐 때만 실행 if data.get(key) is not None: write_to_cell(cell_addr, data[key]) # --- [1페이지] 중간: 요청 장소 및 사고 종별 채우기 --- # 사용자가 요청한 매핑: PAGE1_LOCATION_MAP = { 'request_location': 'J11', # 요청 장소 'cr_address': 'Z11', # 주소 (DB 컬럼 alias 주의: address -> cr_address) 'incident_type': 'J20' # 사고 종별 } # 데이터 쓰기 for key, cell_addr in PAGE1_LOCATION_MAP.items(): if data.get(key) is not None: write_to_cell(cell_addr, data[key]) # --- [1페이지] 하단: 시간 정보 (Time) 채우기 --- # 입전 -> 지령 -> 현장도착 -> 접촉 -> 반출개시 -> 차내수용 -> 현장출발 -> 병원도착 -> 의사인계 -> 귀서 PAGE1_TIME_MAP = { 'call_received_h': 'A27', 'call_received_m': 'F27', # 입전 (Call Received) 'dispatch_h': 'J27', 'dispatch_m': 'O27', # 지령 (Dispatch) 'arrival_on_scene_h': 'R27', 'arrival_on_scene_m': 'W27', # 현장도착 (Arrival on Scene) 'patient_contact_h': 'Z27', 'patient_contact_m': 'AE27', # 접촉 (Patient Contact) 'transport_start_h': 'AH27', 'transport_start_m': 'AM27', # 반출개시 (Transport Start) 'patient_loaded_h': 'AP27', 'patient_loaded_m': 'AU27', # 차내수용 (Patient Loaded) 'depart_scene_h': 'AX27', 'depart_scene_m': 'BC27', # 현장출발 (Depart Scene) 'arrival_hospital_h': 'BF27', 'arrival_hospital_m': 'BK27', # 병원도착 (Arrival Hospital) 'handover_to_doctor_h': 'BN27', 'handover_to_doctor_m': 'BS27', # 의사인계 (Handover to Doctor) 'return_from_site_h': 'BV27', 'return_from_site_m': 'CA27' # 귀서 (Return from Site) } # 데이터 쓰기 for key, cell_addr in PAGE1_TIME_MAP.items(): val = data.get(key) if val is not None: # (옵션) 숫자인 경우 정수로 변환하여 "09" 대신 "9"로 깔끔하게 찍히도록 처리 try: val = int(val) except: pass write_to_cell(cell_addr, val) # --- [1페이지] 중하단: 환자 정보, 신고 내용, 거리 정보 --- # 1. 일반 필드 매핑 # ※ 주의: SQL 쿼리의 Alias(별칭)와 일치해야 데이터가 들어갑니다. PAGE1_INFO_MAP = { 'dispatch_location': 'J31', # 수령장소 'doctor_car_detail': 'BA31', # DC상세 'patient_name1': 'J35', # 환자명1 'patient_name2': 'J38', # 환자명2 'birth_year': 'AR35', # 생년 'birth_month': 'BD35', # 생월 'birth_day': 'BL35', # 생일 'pi_age': 'BV35', # 나이 (SQL: pi.age as pi_age) 'pi_gender': 'CE35', # 성별 (SQL: pi.gender as pi_gender) 'pi_address': 'J44', # 주소 (SQL: pi.address as pi_address) 'pi_phone_number': 'J48', # TEL (SQL: pi.phone_number as pi_phone_number) 'diagnosis_name': 'AP48', # 상병명 'report_severity': 'BZ48', # 정도 (SQL: r.severity as report_severity) 'companion_name': 'J52', # 동승자 'relation': 'AH52', # 관계 'contact_info': 'AW52', # 연락처 'pi_urgency_level': 'BZ52', # 긴급도 (SQL: pi.urgency_level as pi_urgency_level) 'hospital_selection_reason': 'A58', # 선정이유 'first_doctor_name': 'BV56', # 초진의사 'related_organization': 'BV60' # 관계기관 } for key, cell_addr in PAGE1_INFO_MAP.items(): if data.get(key) is not None: write_to_cell(cell_addr, data[key]) # 2. 거리 정보 결합 (정수부 + "." + 소수부) # 리스트 형식: (정수부DB키, 소수부DB키, 엑셀좌표) distance_pairs = [ ('distance_station_to_scene_L', 'distance_station_to_scene_R', 'AE58'), ('distance_scene_to_hospital_L', 'distance_scene_to_hospital_R', 'AO58'), ('distance_hospital_to_station_L', 'distance_hospital_to_station_R', 'AY58'), ('distance_station_roundtrip_L', 'distance_station_roundtrip_R', 'BI58') ] for l_key, r_key, cell_addr in distance_pairs: l_val = data.get(l_key) r_val = data.get(r_key) # 둘 다 값이 있을 때만 "숫자.숫자" 형태로 결합 if l_val is not None and r_val is not None: write_to_cell(cell_addr, f"{l_val}.{r_val}") # --- [1페이지] 하단 ~ 2페이지: 상세 증상, 바이탈 사인, 병력 --- # 1. 일반 필드 매핑 # ※ 주의: contact_time_T 테이블 컬럼은 SQL에서 'ct_' 접두어가 붙어있습니다. PAGE1_VITALS_MAP = { # --- Patient Info (증상) --- 'onset_time': 'P65', # 발증시기 'contact_condition': 'BM65', # 접촉시상황 'chief_complaint': 'P69', # 주소 'symptom_severity': 'BM69', # 성상/강도 # --- Contact Time (바이탈 사인 & 관찰) --- 'ct_contact_time_h': 'A79', 'ct_contact_time_m': 'I79', # 접촉시각 'ct_consciousness_jcs': 'U79', # JCS 'ct_consciousness_e': 'R83', 'ct_consciousness_v': 'W83', 'ct_consciousness_m': 'AB83', # GCS 'ct_respiration_rate': 'AE79', 'ct_respiration_condition': 'AE83', # 호흡 'ct_pulse_1': 'AT79', 'ct_pulse_rate': 'AT81', 'ct_pulse_2': 'AT83', # 맥박 # (체온은 별도 처리) 'ct_temperature_text': 'BI83', # 체온(텍스트) 'ct_bp_right_1': 'CF79', 'ct_bp_right_2': 'CM79', # 혈압 우 'ct_bp_left_1': 'CF82', 'ct_bp_left_2': 'CM82', # 혈압 좌 'ct_spo2_left': 'I85', 'ct_spo2_right': 'W85', # SPO2 'ct_oxygen_use': 'AH85', 'ct_oxygen_flow_rate': 'AT85', # 산소 'ct_ecg_status': 'BL85', # 심전도 'ct_auscultation': 'CG85', # 청진 # --- 눈 (Pupil) --- 'ct_pupil_right_size': 'J90', 'ct_pupil_right_reaction': 'V90', 'ct_gaze_deviation': 'AO90', 'ct_palpebral_conjunctiva': 'BF90', 'ct_pupil_left_size': 'J93', 'ct_pupil_left_reaction': 'V93', 'ct_visual_impairment': 'AO93', 'ct_nystagmus': 'BF93', # --- 기타 관찰 --- 'ct_convulsion': 'BW90', # 경련 'ct_affected_area_condition': 'CG90', # 부위성상 'ct_skin_condition': 'F96', # 피부 'ct_paralysis': 'AF96', 'ct_paralysis_area': 'AU96', # 마비 'ct_vomit': 'BW96', 'ct_vomit_count': 'CC98', # 구토 'ct_diarrhea': 'CN96', # 설사 'ct_first_aid': 'I101', 'ct_first_aid_other': 'AM101', # 응급처치 'ct_transport_position': 'I107', # 반송체위 'ct_adl': 'P110', # ADL # --- Patient Info (병력 등 하단 정보) --- 'allergies': 'BN110', # 알레르기 'medication_history': 'P114', # 약력 'medical_history': 'BN114', # 기왕력 'last_meal_time': 'P118', # 최종식사 'primary_medical_institution': 'BN118' # 단골병원 } for key, cell_addr in PAGE1_VITALS_MAP.items(): if data.get(key) is not None: write_to_cell(cell_addr, data[key]) # 2. 체온 정보 결합 (정수부 + "." + 소수부) -> BI79 # SQL Alias: ct_temperature_L, ct_temperature_R if data.get('ct_temperature_L') is not None and data.get('ct_temperature_R') is not None: combined_temp = f"{data['ct_temperature_L']}.{data['ct_temperature_R']}" write_to_cell('BI79', combined_temp) # --- [2페이지] 하단: 병원 도착 전 상태 및 메모 --- # 1. 일반 필드 매핑 # ※ 주의: before_arrival 테이블은 'ba_', memo 테이블은 'memo_' 접두어 사용 PAGE2_BEFORE_ARRIVAL_MAP = { # --- Before Arrival (병원 도착 전) --- 'ba_contact_time_h': 'A125', 'ba_contact_time_m': 'I125', # 시각 'ba_consciousness_jcs': 'U125', # JCS 'ba_consciousness_e': 'R129', 'ba_consciousness_v': 'W129', 'ba_consciousness_m': 'AB129', # GCS 'ba_respiration_rate': 'AE125', 'ba_respiration_condition': 'AE129', # 호흡 'ba_pulse_1': 'AT125', 'ba_pulse_rate': 'AT127', 'ba_pulse_2': 'AT129', # 맥박 'ba_temperature_text': 'BI129', # 체온(텍스트) 'ba_bp_right_1': 'CF125', 'ba_bp_right_2': 'CM125', # 혈압 우 'ba_bp_left_1': 'CF128', 'ba_bp_left_2': 'CM128', # 혈압 좌 'ba_spo2_left': 'I131', 'ba_spo2_right': 'W131', # SPO2 'ba_oxygen_use': 'AH131', 'ba_oxygen_flow_rate': 'AT131', # 산소 'ba_ecg_status': 'BL131', # 심전도 'ba_auscultation': 'CG131', # 청진 # --- Memo (메모) --- 'memo_text': 'A136' # 구급대원 메모 } for key, cell_addr in PAGE2_BEFORE_ARRIVAL_MAP.items(): if data.get(key) is not None: write_to_cell(cell_addr, data[key]) # 2. 체온 정보 결합 (병원 도착 전) -> BI125 if data.get('ba_temperature_L') is not None and data.get('ba_temperature_R') is not None: combined_temp_ba = f"{data['ba_temperature_L']}.{data['ba_temperature_R']}" write_to_cell('BI125', combined_temp_ba) # --- [마지막] 이미지 삽입 (A139 행부터 시작) --- # 1. 이미지 경로 수집 # patient 테이블의 img는 'img', 나머지는 SQL Alias에 따라 'st1_img' 등으로 조회 image_fields = ['img', 'st1_img', 'st2_img', 'st3_img', 'memo_img'] all_paths = [] logging.info(f"[IMG] UPLOAD_DIR={UPLOAD_DIR}") logging.info(f"[IMG] raw_fields=" + str({f: data.get(f) for f in image_fields})) for field in image_fields: if data.get(field): # 콤마(,)로 구분된 여러 이미지가 있을 경우 분리 for p in data[field].split(','): # 파일명 추출 및 전체 경로 결합 fname = os.path.basename(p.strip()) fpath = os.path.join(UPLOAD_DIR, fname) # 실제 파일이 존재할 때만 리스트에 추가 if os.path.exists(fpath): all_paths.append(fpath) # 2. 엑셀에 이미지 배치 (8개씩 한 줄) start_row = 139 # 사용자가 지정한 시작 행 (A139) # 이미지가 들어갈 열 위치 (기존 코드와 동일하게 간격 배치) cols = ['B', 'T', 'AO', 'BH', 'CB'] # [수정 1] 한 그룹당 2줄씩 씁니다. (윗줄은 여백, 아랫줄은 이미지) row_height_increment = 2 # [수정 2] 행 높이 설정 spacer_height = 10 # 여백 줄 높이 (살짝 내리는 정도) # --- [추가 로직] 전체 이미지 개수에 따라 높이 결정 --- total_images = len(all_paths) images_per_row = len(cols) # 한 줄에 5개 # 필요한 이미지 행 수 계산 (올림 나눗셈과 동일한 로직) # 예: 6장 -> 2줄, 11장 -> 3줄 num_image_rows = (total_images + images_per_row - 1) // images_per_row # 조건 적용: 3줄 이상이면 110, 2줄 이하면 85 if num_image_rows >= 3: image_height = 110 else: image_height = 70 for i, p in enumerate(all_paths): try: col_idx = i % len(cols) row_group = i // len(cols) # --- 좌표 계산 로직 변경 --- # 1. 현재 그룹의 시작 행 (여백 행) spacer_row = start_row + (row_group * row_height_increment) # 2. 이미지가 실제로 들어갈 행 (여백 행 바로 다음 줄) image_row = spacer_row + 1 target_cell = f'{cols[col_idx]}{image_row}' # 이미지는 아랫줄에 넣음 # --- 행 높이 설정 (매번 덮어써도 상관없음) --- # 윗줄(여백)은 얇게 sheet.row_dimensions[spacer_row].height = spacer_height # 아랫줄(이미지)은 넉넉하게 sheet.row_dimensions[image_row].height = image_height # 이미지 로드 및 리사이징 img_obj = Image(p) if img_obj.width > 0: ratio = img_obj.height / img_obj.width img_obj.width = 130 img_obj.height = 130 * ratio sheet.add_image(img_obj, target_cell) except Exception as e: logging.error(f"이미지 삽입 실패 ({p}): {e}") continue sheet.print_area = "A1:CV400" 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(PREFIX + "/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(PREFIX + "/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_filename = "20251226_B.xlsx" template_path = os.path.join(TEMPLATE_DIR, template_filename) output_filename = f"reportB_{patient_no}.xlsx" excel_output_path = os.path.join(TEMPLATE_DIR, output_filename) workbook = openpyxl.load_workbook(template_path) # 시트 이름 지정 (인계용) target_sheet_name = "引継ぎ用" if target_sheet_name in workbook.sheetnames: sheet = workbook[target_sheet_name] else: logging.warning(f"'{target_sheet_name}' 시트를 찾을 수 없어 첫 번째 시트를 사용합니다.") 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 # --- [1페이지] 날짜 데이터 가공 --- if data.get('call_received_date'): try: cr_date = data['call_received_date'] if isinstance(cr_date, str): cr_date = datetime.strptime(cr_date, '%Y-%m-%d') data['calc_month'] = cr_date.month data['calc_day'] = cr_date.day weekdays = ["月", "火", "水", "木", "金", "土", "日"] data['calc_weekday'] = weekdays[cr_date.weekday()] except Exception: pass # --- [1페이지] 매핑 정의 (B 리포트용) --- REPORT_B_PAGE1_MAP = { 'calc_month': 'J5', # 월 'calc_day': 'S5', # 일 'calc_weekday': 'AC5', # 요일(한자) 'call_method': 'AQ5', # 입전방법 'request_location': 'J11', # 요청장소 'cr_address': 'Z11', # 주소 (SQL Alias 주의: address -> cr_address) 'incident_type': 'J20' # 사고종별 } # 데이터 쓰기 for key, cell_addr in REPORT_B_PAGE1_MAP.items(): if data.get(key) is not None: write_to_cell(cell_addr, data[key]) # --- [1페이지] 하단: 시간 정보 (Time) 채우기 --- REPORT_B_TIME_MAP = { 'call_received_h': 'A27', 'call_received_m': 'F27', # 입전 (Call Received) 'dispatch_h': 'J27', 'dispatch_m': 'O27', # 지령 (Dispatch) 'arrival_on_scene_h': 'R27', 'arrival_on_scene_m': 'W27', # 현장도착 (Arrival on Scene) 'patient_contact_h': 'Z27', 'patient_contact_m': 'AE27', # 접촉 (Patient Contact) 'transport_start_h': 'AH27', 'transport_start_m': 'AM27', # 반출개시 (Transport Start) 'patient_loaded_h': 'AP27', 'patient_loaded_m': 'AU27', # 차내수용 (Patient Loaded) 'depart_scene_h': 'AX27', 'depart_scene_m': 'BC27', # 현장출발 (Depart Scene) 'arrival_hospital_h': 'BF27', 'arrival_hospital_m': 'BK27', # 병원도착 (Arrival Hospital) 'handover_to_doctor_h': 'BN27', 'handover_to_doctor_m': 'BS27', # 의사인계 (Handover to Doctor) 'return_from_site_h': 'BV27', 'return_from_site_m': 'CA27', # 현장철수 (Return from Site - 귀서) 'return_to_station_h': 'CD27', 'return_to_station_m': 'CI27' # 귀소 (Return to Station) } for key, cell_addr in REPORT_B_TIME_MAP.items(): val = data.get(key) if val is not None: # (옵션) 09 -> 9 처럼 숫자로 변환하여 깔끔하게 입력 try: val = int(val) except: pass write_to_cell(cell_addr, val) # --- [1페이지] 중하단: 환자 정보 및 구급대 정보 --- REPORT_B_INFO_MAP = { # --- 환자 기본 정보 (AW52까지는 기존 동일) --- 'dispatch_location': 'J31', # 수령장소 'doctor_car_detail': 'BA31', # DC상세 'patient_name1': 'J35', # 환자명1 'patient_name2': 'J38', # 환자명2 'birth_year': 'AR35', # 생년 'birth_month': 'BD35', # 생월 'birth_day': 'BL35', # 생일 'pi_age': 'BV35', # 나이 'pi_gender': 'CE35', # 성별 'pi_address': 'J44', # 주소 'pi_phone_number': 'J48', # TEL 'diagnosis_name': 'AP48', # 상병명 'report_severity': 'BZ48', # 정도 'companion_name': 'J52', # 동승자 'relation': 'AH52', # 관계 'contact_info': 'AW52', # 연락처 'hospital_selection_reason': 'A58', # 의료기관 선정이유 'ambulance_team_name': 'AE60', # 구급대명 'team_leader_name': 'BV56' # 대장명 } for key, cell_addr in REPORT_B_INFO_MAP.items(): if data.get(key) is not None: write_to_cell(cell_addr, data[key]) # --- [1페이지] 하단 ~ 2페이지: 상세 증상, 바이탈 사인, 병력 --- REPORT_B_VITALS_MAP = { # --- Patient Info (증상) --- 'onset_time': 'P65', # 발증시기 'contact_condition': 'BM65', # 접촉시상황 'chief_complaint': 'P69', # 주소 'symptom_severity': 'BM69', # 성상/강도 # --- Contact Time (바이탈 사인 & 관찰) --- # ※ 주의: SQL Alias에 따라 'ct_' 접두어 사용 'ct_contact_time_h': 'A79', 'ct_contact_time_m': 'I79', # 시각 'ct_consciousness_jcs': 'U79', # JCS 'ct_consciousness_e': 'R83', 'ct_consciousness_v': 'W83', 'ct_consciousness_m': 'AB83', # GCS 'ct_respiration_rate': 'AE79', 'ct_respiration_condition': 'AE83', # 호흡 'ct_pulse_1': 'AT79', 'ct_pulse_rate': 'AT81', 'ct_pulse_2': 'AT83', # 맥박 # (체온 BI79는 아래에서 별도 처리) 'ct_temperature_text': 'BI83', # 체온(텍스트) 'ct_bp_right_1': 'CF79', 'ct_bp_right_2': 'CM79', # 혈압 우 'ct_bp_left_1': 'CF82', 'ct_bp_left_2': 'CM82', # 혈압 좌 'ct_spo2_left': 'I85', 'ct_spo2_right': 'W85', # SPO2 'ct_oxygen_use': 'AH85', 'ct_oxygen_flow_rate': 'AT85', # 산소 'ct_ecg_status': 'BL85', # 심전도 'ct_auscultation': 'CG85', # 청진 # --- 눈 (Pupil) --- 'ct_pupil_right_size': 'J90', 'ct_pupil_right_reaction': 'V90', 'ct_gaze_deviation': 'AO90', 'ct_palpebral_conjunctiva': 'BF90', 'ct_pupil_left_size': 'J93', 'ct_pupil_left_reaction': 'V93', 'ct_visual_impairment': 'AO93', 'ct_nystagmus': 'BF93', # --- 기타 관찰 --- 'ct_convulsion': 'BW90', # 경련 'ct_affected_area_condition': 'CG90', # 부위성상 'ct_skin_condition': 'F96', # 피부 'ct_paralysis': 'AF96', 'ct_paralysis_area': 'AU96', # 마비 'ct_vomit': 'BW96', 'ct_vomit_count': 'CC98', # 구토 'ct_diarrhea': 'CN96', # 설사 'ct_first_aid': 'I101', 'ct_first_aid_other': 'AM101', # 응급처치 'ct_transport_position': 'I107', # 반송체위 'ct_adl': 'P110', # ADL # --- Patient Info (병력 등 하단 정보) --- 'allergies': 'BN110', # 알레르기 'medication_history': 'P114', # 약력 'medical_history': 'BN114', # 기왕력 'last_meal_time': 'P118', # 최종식사 'primary_medical_institution': 'BN118' # 단골병원 } for key, cell_addr in REPORT_B_VITALS_MAP.items(): if data.get(key) is not None: write_to_cell(cell_addr, data[key]) # 2. 체온 정보 결합 (정수부 + "." + 소수부) -> BI79 if data.get('ct_temperature_L') is not None and data.get('ct_temperature_R') is not None: combined_temp = f"{data['ct_temperature_L']}.{data['ct_temperature_R']}" write_to_cell('BI79', combined_temp) # --- [2페이지] 하단: 병원 도착 전 상태 및 메모 --- REPORT_B_BEFORE_ARRIVAL_MAP = { # --- Before Arrival (병원 도착 전) --- # ※ SQL Alias: ba_ 접두어 사용 'ba_contact_time_h': 'A125', 'ba_contact_time_m': 'I125', # 시각 'ba_consciousness_jcs': 'U125', # JCS 'ba_consciousness_e': 'R129', 'ba_consciousness_v': 'W129', 'ba_consciousness_m': 'AB129', # GCS 'ba_respiration_rate': 'AE125', 'ba_respiration_condition': 'AE129', # 호흡 'ba_pulse_1': 'AT125', 'ba_pulse_rate': 'AT127', 'ba_pulse_2': 'AT129', # 맥박 'ba_temperature_text': 'BI129', # 체온(텍스트) 'ba_bp_right_1': 'CF125', 'ba_bp_right_2': 'CM125', # 혈압 우 'ba_bp_left_1': 'CF128', 'ba_bp_left_2': 'CM128', # 혈압 좌 'ba_spo2_left': 'I131', 'ba_spo2_right': 'W131', # SPO2 'ba_oxygen_use': 'AH131', 'ba_oxygen_flow_rate': 'AT131', # 산소 'ba_ecg_status': 'BL131', # 심전도 'ba_auscultation': 'CG131', # 청진 # --- Memo (메모) --- 'memo_text': 'A136' # 구급대원 메모 } for key, cell_addr in REPORT_B_BEFORE_ARRIVAL_MAP.items(): if data.get(key) is not None: write_to_cell(cell_addr, data[key]) # 2. 체온 정보 결합 (병원 도착 전) -> BI125 if data.get('ba_temperature_L') is not None and data.get('ba_temperature_R') is not None: combined_temp_ba = f"{data['ba_temperature_L']}.{data['ba_temperature_R']}" write_to_cell('BI125', combined_temp_ba) # 1. 이미지 경로 수집 # DB에서 조회한 컬럼명(Alias) 기준: img(환자), st1_img, st2_img, st3_img, memo_img image_fields = ['img', 'st1_img', 'st2_img', 'st3_img', 'memo_img'] all_paths = [] for field in image_fields: if data.get(field): # 콤마(,)로 구분된 여러 이미지가 있을 경우 분리 for p in data[field].split(','): fname = os.path.basename(p.strip()) fpath = os.path.join(UPLOAD_DIR, fname) if os.path.exists(fpath): all_paths.append(fpath) # 2. 엑셀에 이미지 배치 (8개씩 한 줄) start_row = 139 # 사용자가 지정한 시작 행 (A139) # 이미지가 들어갈 열 위치 (기존 코드와 동일하게 간격 배치) cols = ['B', 'T', 'AO', 'BH', 'CB'] # [수정 1] 한 그룹당 2줄씩 씁니다. (윗줄은 여백, 아랫줄은 이미지) row_height_increment = 2 # [수정 2] 행 높이 설정 spacer_height = 10 # 여백 줄 높이 (살짝 내리는 정도) # --- [추가 로직] 전체 이미지 개수에 따라 높이 결정 --- total_images = len(all_paths) images_per_row = len(cols) # 한 줄에 5개 # 필요한 이미지 행 수 계산 (올림 나눗셈과 동일한 로직) # 예: 6장 -> 2줄, 11장 -> 3줄 num_image_rows = (total_images + images_per_row - 1) // images_per_row # 조건 적용: 3줄 이상이면 110, 2줄 이하면 85 if num_image_rows >= 3: image_height = 110 else: image_height = 70 for i, p in enumerate(all_paths): try: col_idx = i % len(cols) row_group = i // len(cols) # --- 좌표 계산 로직 변경 --- # 1. 현재 그룹의 시작 행 (여백 행) spacer_row = start_row + (row_group * row_height_increment) # 2. 이미지가 실제로 들어갈 행 (여백 행 바로 다음 줄) image_row = spacer_row + 1 target_cell = f'{cols[col_idx]}{image_row}' # 이미지는 아랫줄에 넣음 # --- 행 높이 설정 (매번 덮어써도 상관없음) --- # 윗줄(여백)은 얇게 sheet.row_dimensions[spacer_row].height = spacer_height # 아랫줄(이미지)은 넉넉하게 sheet.row_dimensions[image_row].height = image_height # 이미지 로드 및 리사이징 img_obj = Image(p) if img_obj.width > 0: ratio = img_obj.height / img_obj.width img_obj.width = 130 img_obj.height = 130 * ratio sheet.add_image(img_obj, target_cell) except Exception as e: logging.error(f"이미지 삽입 실패 ({p}): {e}") continue sheet.print_area = "A1:CV400" 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(PREFIX + "/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. 엑셀 파일에 데이터 채우기 (Report C) --- try: # [설정 1] 파일명: 20251226_C.xlsx template_filename = "20251226_C.xlsx" template_path = os.path.join(TEMPLATE_DIR, template_filename) output_filename = f"reportC_{patient_no}.xlsx" excel_output_path = os.path.join(TEMPLATE_DIR, output_filename) workbook = openpyxl.load_workbook(template_path) # [설정 2] 시트명: 医療機関提出用 target_sheet_name = "医療機関提出用" if target_sheet_name in workbook.sheetnames: sheet = workbook[target_sheet_name] else: logging.warning(f"'{target_sheet_name}' 시트를 찾을 수 없어 첫 번째 시트를 사용합니다.") sheet = workbook.active # 헬퍼 함수 def write_to_cell(cell, value): try: if value is not None: sheet[cell] = value except Exception as e: logging.error(f"Error Report C ({cell}): {e}") # ========================================== # [여기부터 알려주시는 데이터를 매핑합니다] # ========================================== # 1. 날짜 등 공통 가공 (필요 시 작성) if data.get('call_received_date'): try: cr_date = data['call_received_date'] if isinstance(cr_date, str): cr_date = datetime.strptime(cr_date, '%Y-%m-%d') # 날짜 데이터 준비 data['calc_month'] = cr_date.month data['calc_day'] = cr_date.day weekdays = ["月", "火", "水", "木", "金", "土", "日"] data['calc_weekday'] = weekdays[cr_date.weekday()] except: pass # --- [1단계] 기본 정보 매핑 (Report C) --- REPORT_C_PAGE1_MAP = { 'calc_month': 'J5', # 월 'calc_day': 'S5', # 일 'calc_weekday': 'AC5', # 요일(한자) 'call_method': 'AQ5', # 입전방법 'request_location': 'J11', # 요청장소 'cr_address': 'Z11', # 주소 (SQL: cr.address as cr_address) 'incident_type': 'J20' # 사고종별 } # 데이터 쓰기 for key, cell_addr in REPORT_C_PAGE1_MAP.items(): if data.get(key) is not None: write_to_cell(cell_addr, data[key]) # --- [1페이지] 하단: 시간 정보 (Time) 채우기 --- REPORT_C_TIME_MAP = { 'call_received_h': 'A27', 'call_received_m': 'F27', # 입전 (Call Received) 'dispatch_h': 'J27', 'dispatch_m': 'O27', # 지령 (Dispatch) 'arrival_on_scene_h': 'R27', 'arrival_on_scene_m': 'W27', # 현장도착 (Arrival on Scene) 'patient_contact_h': 'Z27', 'patient_contact_m': 'AE27', # 접촉 (Patient Contact) 'transport_start_h': 'AH27', 'transport_start_m': 'AM27', # 반출개시 (Transport Start) 'patient_loaded_h': 'AP27', 'patient_loaded_m': 'AU27', # 차내수용 (Patient Loaded) 'depart_scene_h': 'AX27', 'depart_scene_m': 'BC27', # 현장출발 (Depart Scene) 'arrival_hospital_h': 'BF27', 'arrival_hospital_m': 'BK27', # 병원도착 (Arrival Hospital) 'handover_to_doctor_h': 'BN27', 'handover_to_doctor_m': 'BS27', # 의사인계 (Handover to Doctor) 'return_from_site_h': 'BV27', 'return_from_site_m': 'CA27', # 현장철수 (Return from Site) 'return_to_station_h': 'CD27', 'return_to_station_m': 'CI27', # 귀소 (Return to Station) # [Report C 추가 항목] 전원 (Transfer) 'transfer1_h': 'CL27', 'transfer1_m': 'CR27', 'transfer2_h': 'CL29', 'transfer2_m': 'CR29' } for key, cell_addr in REPORT_C_TIME_MAP.items(): val = data.get(key) if val is not None: # 숫자로 변환하여 깔끔하게 입력 (예: "09" -> 9) try: val = int(val) except: pass write_to_cell(cell_addr, val) # --- [1페이지] 중단: 환자 정보, 신고 내용, 메모 --- REPORT_C_INFO_MAP = { # Time 테이블 'dispatch_location': 'J31', # 수령장소 'doctor_car_detail': 'BA31', # DC상세 # Patient Info 테이블 'patient_name1': 'J35', # 환자명1 'patient_name2': 'J38', # 환자명2 'birth_year': 'AR35', # 생년 'birth_month': 'BD35', # 생월 'birth_day': 'BL35', # 생일 'pi_age': 'BV35', # 나이 (SQL alias: pi_age) 'pi_gender': 'CE35', # 성별 (SQL alias: pi_gender) 'pi_occupation': 'CJ39', # 직업 (pi.occupation) 'pi_address': 'J44', # 주소 (SQL alias: pi_address) 'pi_phone_number': 'J48', # TEL (SQL alias: pi_phone_number) 'companion_name': 'J52', # 동승자 'relation': 'AH52', # 관계 'contact_info': 'AW52', # 연락처 # Report 테이블 'diagnosis_name': 'AP48', # 상병명 'report_severity': 'BZ48', # 정도 (SQL alias: report_severity) 'hospital_selection_reason': 'A58', # 선정이유 'ambulance_team_name': 'AE60', # 구급대명 'team_leader_name': 'BV56', # 대장명 # Memo 테이블 'memo_text': 'A65' # 메모 (SQL alias: memo_text) } for key, cell_addr in REPORT_C_INFO_MAP.items(): if data.get(key) is not None: write_to_cell(cell_addr, data[key]) # 1. 이미지 경로 수집 # DB 컬럼: img(환자), st1_img, st2_img, st3_img, memo_img image_fields = ['img', 'st1_img', 'st2_img', 'st3_img', 'memo_img'] all_paths = [] for field in image_fields: if data.get(field): # 콤마(,)로 구분된 여러 이미지가 있을 경우 분리 for p in data[field].split(','): fname = os.path.basename(p.strip()) fpath = os.path.join(UPLOAD_DIR, fname) if os.path.exists(fpath): all_paths.append(fpath) # 2. 엑셀에 이미지 배치 (8개씩 한 줄) start_row = 71 # [설정] 시작 행 (A71) cols = ['B', 'T', 'AO', 'BH', 'CB'] # 이미지가 들어갈 열 row_height_increment = 15 # 다음 줄 간격 (행 높이) for i, p in enumerate(all_paths): try: col_idx = i % len(cols) # 열 인덱스 (0~7) row_group = i // len(cols) # 행 그룹 (0, 1, 2...) current_row = start_row + (row_group * row_height_increment) target_cell = f'{cols[col_idx]}{current_row}' # 이미지 로드 및 리사이징 img_obj = Image(p) # 가로 240px 기준 비율 조정 if img_obj.width > 0: ratio = img_obj.height / img_obj.width img_obj.width = 130 img_obj.height = 130 * ratio sheet.add_image(img_obj, target_cell) except Exception as e: logging.error(f"이미지 삽입 실패 ({p}): {e}") continue sheet.print_area = "A1:CU200" 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(PREFIX + "/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(PREFIX + "/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-test/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(PREFIX + "/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"{PREFIX}/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(PREFIX + "/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-test/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(PREFIX + "/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(PREFIX + "/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(PREFIX + "/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(PREFIX + "/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(PREFIX + "/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(PREFIX + "/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(PREFIX + "/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(PREFIX + "/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(PREFIX + "/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"{PREFIX}/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(PREFIX + "/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"{PREFIX}/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(PREFIX + "/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(PREFIX + "/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(PREFIX + "/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(PREFIX + "/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(PREFIX + "/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(PREFIX + "/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(PREFIX + "/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(PREFIX + "/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()