o
    v4Ei}                 ]   @   s  d dl mZmZmZmZmZ d dlmZ d dlm	Z	m
Z
mZ d dlZd dlZd dlZd dlZd dlZd dlmZ d dlmZ d dlmZ d dlZd dlmZ d d	lmZ d d
lmZmZ d dlmZ d dlZd dl m!Z! ej"ej#dd e Z$e$j%edgddgdgd ej&'ddZ(ej&'ddZ)ej&'ddZ*ej&'ddZ+ddddddZ,dZ-dZ.dd Z/e$j0dee.d d!d" e$j0d#ee-d d$d" G d%d& d&eZ1e$j'd'ed(d)d* Z2G d+d, d,eZ3G d-d. d.eZ4e$5d/d0e1fd1d2Z6e$5d3d0e3fd4d5Z7d6e8fd7d8Z9e$5d9ed:ededededededededededededededededededfd;e8d<e	e8 d=e	e8 d>e	e8 d?e	e8 d@e	e8 dAe	e8 dBe	e8 dCe	e8 dDe	e8 dEe	e8 dFe	e8 dGe	e8 dHe	e8 dIe	e8 dJe	e8 dKee dLee8 dMe	e8 f&dNdOZ:e$5dPed:ededededededededededededededededededededededededededededfd6e8dQe	e8 dRe	e8 dSe	e8 dTe	e8 dUe	e8 dVe	e8 dWe	e8 dXe	e8 dYe	e8 dZe	e8 d[e	e8 d\e	e8 d]e	e8 d^e	e8 d_e	e8 d`e	e8 dae	e8 dbe	e8 dce	e8 dde	e8 dee	e8 dfe	e8 dge	e8 dhe	e8 die	e8 dje	e8 dke	e8 dle	e8 f:dmdnZ;e$5doed:ededededededededededededededededededededededededededededfd6e8dQe	e8 dRe	e8 dSe	e8 dTe	e8 dUe	e8 dVe	e8 dWe	e8 dXe	e8 dYe	e8 dZe	e8 d[e	e8 d\e	e8 d]e	e8 d^e	e8 d_e	e8 d`e	e8 dae	e8 dbe	e8 dce	e8 dde	e8 dee	e8 dfe	e8 dge	e8 dhe	e8 die	e8 dje	e8 dke	e8 dle	e8 f:dpdqZ<e$5dred:ededededededfd6e8dse	e8 dte	e8 due	e8 dve	e8 d@e	e8 dwe	e8 fdxdyZ=e$5dzed:edededededededededededededededededededededededfd6e8d{e	e8 d|e	e8 d}e	e8 d~e	e8 de	e8 d>e	e8 d?e	e8 de	e8 d@e	e8 dAe	e8 de	e8 de	e8 de	e8 dMe	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 f0ddZ>e$5ded:ededededededededededededededededededededededededededededededededededededededededededededf-d6e8de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 fZddZ?e$5ded:edeg eg fd6e8de	e8 dKee dLee8 fddZ@e$5d¡ed:ededededededededededededededededededededededededfd6e8de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 f2ddĄZAe$5dšed:edededededededededededededededfd6e8de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 f ddքZBe$5dסed:ed:edfd6e8dMe8de	e8 fddڄZCe$5dۡed:edededededededededededededededededededfd6e8de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 dMe	e8 dKee dLee8 de	e8 de	eD f(ddZEe$5ded:edededededededededededededededededededfd6e8de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 dMe	e8 dKee dLee8 de	e8 de	eD f(ddZFe$5ded:ededededededededededededededededededfd6e8de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 de	e8 dMe	e8 dKee dLee8 de	e8 f&ddZGG dd deZHe$j'deeH ddd ZIG dd deZHe$j'deeH ddd ZJe$'ddd ZKd dlLZLdd ZMde8d6e8deNfddZOe$'dd6e8fddZPe$'d d6e8fddZQe$'dd6e8fddZRe$'dd6e8fddZSe$'d	d6e8fd
dZTe$'dd6e8fddZUe$'dd6e8fddZVe$5ded:d;dedededededededededededededededededfd;e8d<e	e8 d=e	e8 d>e	e8 d?e	e8 d@e	e8 dAe	e8 dBe	e8 dCe	e8 dDe	e8 dEe	e8 dFe	e8 dGe	e8 dHe	e8 dIe	e8 dJe	e8 dMe	e8 dKee f$ddZWe$5ded:fd6e8fddZXde8d6e8deNfddZOe$'ddd ZYe$'dd6e8fddZZe$'d d6e8fd!d"Z[e$'d#d6e8fd$d%Z\e$'d&d6e8fd'd(Z]e$'d)d6e8fd*d+Z^e$'d,d6e8fd-d.Z_e$'d/d6e8fd0d1Z`e$'d2d6e8fd3d4Zae$'d5d6e8fd6d7Zbd6e8d8e8fd9d:Zce$'d;d6e8fd<d=Zde$'d>d6e8fd?d@Zee$'dAd6e8fdBdCZfe$'dDdEdF Zge$'dGd6e8fdHdIZhe$5dJd0e4fdKdLZie$5dMd0e4fdNdOZje$5dPed:fd6e8fdQdRZkdS (S      )FastAPIHTTPExceptionFile
UploadFileForm)	BaseModel)OptionalDictListN)Image)Font)CORSMiddleware)datetime)StaticFiles)HTMLResponseJSONResponse)RotatingFileHandlerz)%(asctime)s - %(levelname)s - %(message)s)levelformat*T)allow_originsallow_credentialsallow_methodsallow_headersDB_HOST	localhostDB_USERzsos-userDB_PASSWORDzPassWord12!!DB_NAMEsosi  )hostuserpassworddatabaseportz/home/air/sos/uploads/z/home/air/sos/templates/c                   C   s   t jjttttdS )N)r    r!   r"   r#   )mysql	connectorconnectr   r   r   r    r(   r(   $/home/air/sos/back/save_all_times.pyget_db_connection8   s   r*   z/reports)	directoryreports)namez/sos/uploadsuploadsc                   @      e Zd ZU eed< eed< dS )LoginRequestcodecallNameN__name__
__module____qualname__str__annotations__r(   r(   r(   r)   r0   K      
 r0   /)response_classc                     sp   t jt jtd} t j| stdddt| ddd}t|	 dW  d    S 1 s1w   Y  d S )	Nz
index.html  zindex.html not foundstatus_codedetailrzutf-8)encodingcontent)
ospathjoindirname__file__existsr   openr   read)
index_pathfr(   r(   r)   serve_indexP   s   $rN   c                   @   s   e Zd ZU eed< dS )NextNoRequestprefixNr3   r(   r(   r(   r)   rO   [   s   
 rO   c                   @   r/   )AdminLoginRequestidr"   Nr3   r(   r(   r(   r)   rQ   ^   r9   rQ   z/loginreqc              
   C   sf  | j  }| j }|r|stdddddS d }zzRtjjdi t}|jdd}d}|	|||f |
 }|rVd|d	 |d
 dW W |rT| rU|  |  S S S dddW W |rk| rl|  |  S S S  tjjy } z'td|  tdddddW  Y d }~W |r| r|  |  S S S d }~ww |r| r|  |  w w w )Ni  Fu9   隊コードと呼出名称を入力してください。successmessager>   rC   T
dictionaryz[SELECT team_name, abbreviation FROM ambulance_teams WHERE team_code = %s AND call_name = %s	team_nameabbreviation)rU   teamNamer[   u<   隊コードまたは呼出名称が間違っています。zDatabase Error:   u3   データベースエラーが発生しました。r(   )r1   stripr2   r   r%   r&   r'   	DB_CONFIGcursorexecutefetchoneis_connectedcloseErrorprint)rS   
input_codeinput_call_nameconnr`   queryr!   errr(   r(   r)   loginc   sX   







rl   z/get_next_noc              
   C   s`  | j }d }zzctjjdi t}|jdd}d}||| df | }d}t|}|D ] }|d }	t|	|krN|	|d  }
|
	 rNt
|
}||krN|}q.|d }d|dW W |rh| ri|  |  S S S  tjjy } z'td	|  td
ddddW  Y d }~W |r| r|  |  S S S d }~ww |r| r|  |  w w w )NTrX   z'SELECT No FROM patient WHERE No LIKE %s%r   No   )rU   next_no
DB Error: r]   FzDB ErrorrT   rW   r(   )rP   r%   r&   r'   r_   r`   ra   fetchalllenisdigitintrc   rd   re   rf   r   )rS   rP   ri   r`   rj   rowsmax_no
prefix_lenrowpidsuffixnorp   rk   r(   r(   r)   get_next_no   sL   


r}   
patient_noc           	      C   s   |  d|f |  rdS td|  td}t|}|d}g d}||	  }|  d|||f g d}|D ]}|  d	| d
|f q=|  d|f |  d|||f dS )u   
    DB에 해당 patient_no가 존재하는지 확인하고,
    없다면 patient 테이블 및 모든 관련 테이블에 초기 행(placeholder)을 생성합니다.
    $SELECT No FROM patient WHERE No = %sNz/Auto-initiating record for missing patient_no: 
Asia/Tokyo%Y-%m-%d %H:%M:%Su   月u   火u   水u   木u   金u   土u   日zDINSERT INTO patient (No, date_time, day_of_week) VALUES (%s, %s, %s))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INSERT INTO  (patient_no) VALUES (%s)FINSERT INTO patient_info (patient_no, urgency_level) VALUES (%s, NULL)u]   INSERT INTO list (patient_no, date_time, day_of_week, name2) VALUES (%s, %s, %s, '未入力'))
ra   rb   logginginfopytztimezoner   nowstrftimeweekday)	r`   r~   jstnow_jstdate_time_strweekdaysday_of_weekplaceholder_tablestabler(   r(   r)   ensure_patient_record_exists   s   


r   z/sos/api/save_patient.rn   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imagesexisting_imagesurgency_levelc           ,         s>  t d|   t }| }zzYg }|r"|dd |D  |rotjtdd |D ]A}|jrnt	
  tj|jd  }tjt|}t|d}t|j| W d    n1 s_w   Y  d| }|| q-|rvd	|nd }td
}t|}|d}g d}||  } |r| r|nd}!|d| f | }"|"st d|   d}#| ||!||||||||	|
||||||| f}$||#|$ g d}%|%D ]}&|d|& d| f q|d| |f |d| || |!f n6t d|   d}'||!|||||||	|
||||||| f}(||'|( |d|!| f |r*|d|| f |d urAd})||)|| f t d|   d}*||*||!||| f t d|   |  dd|  d d!W W |  |  S  ty }+ z|  t j d"|  d#|+ dd$ t!d%d&t"|+ d'd }+~+ww |  |  w )(Nz+Attempting to save/update patient with No: c                 S      g | ]
}|  r|  qS r(   r^   .0rE   r(   r(   r)   
<listcomp>      z save_patient.<locals>.<listcomp>Texist_okro   wb/sos/uploads/,r   r   r   	   未入力r   zInsert NEW patient record: aD  
                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
                )
            )r   r   r   r   r   r   r   r   r   r   r   r   r   DINSERT INTO patient_info (patient_no, urgency_level) VALUES (%s, %s)TINSERT INTO list (patient_no, date_time, day_of_week, name2) VALUES (%s, %s, %s, %s)z Update EXISTING patient record: a  
                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
            0UPDATE list SET name2 = %s WHERE patient_no = %sz@UPDATE patient_info SET urgency_level = %s WHERE patient_no = %sz;UPDATE call_received SET address = %s WHERE patient_no = %sz(Synced address to call_received for No: z
            UPDATE patient_info 
            SET patient_name1 = %s, patient_name2 = %s, gender = %s, phone_number = %s
            WHERE patient_no = %s
        z3Synced name, gender, phone to patient_info for No: rU   z$Patient data saved successfully (No=)statusrV   z!Error saving patient data for No : exc_infor]   Database error: r=   )#r   r   r*   r`   extendrD   makedirs
UPLOAD_DIRfilenameuuiduuid4rE   splitextrF   rJ   shutilcopyfileobjfileappendr   r   r   r   r   r   r^   ra   rb   commitrd   	Exceptionrollbackerrorr   r7   ),rn   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   cnxr`   final_pathsimgunique_filenamefull_save_pathbufferweb_urlcombined_img_stringr   r   r   r   r   effective_name2record_existsinsert_queryinsert_paramsr   r   update_queryupdate_paramssync_cr_querysync_pi_queryer(   r(   r)   save_patient   s   











r   z"/sos/api/initiate_record_from_time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_detailc           0         s  t d|   dtt dtt fdd}t }| }z1z|d| f | r9t d|   t	dd	d
t
d} t| }!|!d}"g d}#|#|!  }$d}%| |"|$f}&||%|& t d|   d}'| |"|$f}(||'|( t d|   d})| ||||||||||||||||||	||
||||||||||||||||||||||||||||||||||||f}*||)|* t d|   g d}+|+D ]},|d|, d| f qd}-||-| f t d|  d |  dddW W |  |  S  t	y* }. z|.d }.~.w tyO }/ z|  t jd|  d|/ dd  t	d!t|/d
d }/~/ww |  |  w )"Nz9Attempting to initiate time-based record for patient_no: valreturnc                 S   s   | dkrd S | S )N r(   )r  r(   r(   r)   to_db  s   z(initiate_record_from_time.<locals>.to_dbr   z8Attempted to create a record with duplicate patient_no: i  uZ   この番号は既に使用されています。別の番号を入力してください。r=   r   r   r   zs
            INSERT INTO patient (No, date_time, day_of_week, name2)
            VALUES (%s, %s, %s, NULL)
        z-Inserted new minimal patient record with No: zVINSERT INTO list (patient_no, date_time, day_of_week, name2) VALUES (%s, %s, %s, NULL)z.Added summary to 'list' table for patient_no: a]  
            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
            )
        z.Inserted detailed time record for patient_no: )r   r   r   r   r   r   r   r   r   r   r   r   r   r   z)Created placeholder rows for patient_no: z in remaining related tables.rU   zITime-based record initiated with detailed times and placeholders created.r   z2Error in initiate_record_from_time for patient_no r   Tr   r]   )r   r   r   r7   r*   r`   ra   rb   warningr   r   r   r   r   r   r   r   rd   r   r   r   )0r~   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r  r  r  r  r  r	  r   r`   r   r   r   r   r   insert_patient_querypatient_valuesinsert_list_querylist_valuestime_insert_querytime_valuesr   r   insert_pi_queryhttp_excr   r(   r(   r)   initiate_record_from_time  sp    







r  z/sos/api/save_all_timesc           *         s  t d|   t }| }zzd}|||||||||	|
||||||||||||||||||| f} |||  |d urT|d urTd}!||!||| f t d|   |d uro|d urod}"||"||| f t d|   |ru|rudnd}#|r}|r}dnd}$|r|rdnd}%|r|rdnd}&d	}'|#|$|%|&| f}(||'|( t d
|   |  dddW W |  |  S  ty }) z|  t j	d|  d|) dd t
dt|)dd })~)ww |  |  w )Nz#Updating time data for patient_no: a  
            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
        z
                UPDATE contact_time_T 
                SET contact_time_h = %s, contact_time_m = %s 
                WHERE patient_no = %s
             z6Synced time.patient_contact -> contact_time_T for No: z
                UPDATE before_arrival 
                SET contact_time_h = %s, contact_time_m = %s 
                WHERE patient_no = %s
             z7Synced time.arrival_hospital -> before_arrival for No: ro   r   z
            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
        z;Updated transfer_state (Checked _h and _m) for patient_no: rU   zTime data updated.r   z(Error updating time data for patient_no r   Tr   r]   r=   r   r   r*   r`   ra   r   rd   r   r   r   r   r7   )*r~   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r  r  r  r  r  r   r`   rj   valuessync_contact_querysync_before_querypatient_loaded_statusdepart_scene_statusarrival_hospital_statushandover_to_doctor_statustransfer_update_querytransfer_update_valuer   r(   r(   r)   save_all_times  s\    

r  z/sos/api/save_call_receivedcall_received_datecall_methodmonthly_numberrequest_locationincident_typec              
      s   t d|   d}||krd }|dkrd }||krd }||kr#d }t }| }	zJz$d}
||||||| f}|	|
| |  dddW W |	  |  S  tyt } z|  t j	d|  d	| d
d t
dt|dd }~ww |	  |  w )Nz,Updating call_received data for patient_no: <   クリックすると選択メニューが表示されますr  z
            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
        rU   zCall received data updated.r   z1Error updating call_received data for patient_no r   Tr   r]   r=   r  )r~   r  r   r!  r"  r   r#  placeholderr   r`   rj   r  r   r(   r(   r)   save_call_received  s@   


r&  z/sos/api/save_patient_infopatient_name1patient_name2
birth_yearbirth_month	birth_day
occupationcompanion_namerelationcontact_info
onset_timecontact_conditionchief_complaintsymptom_severity	allergiesmedication_historymedical_historylast_meal_timeprimary_medical_institutionc                    s"  t d|   t }| }zwzQ|r| r|nd}d}|||||||||	|
|||||||||||||| f}||| d}|||| f t d|   |  dddW W |  |  S  ty } z|	  t j
d	|  d
| dd tdt|dd }~ww |  |  w )Nz+Updating patient_info data for patient_no: r   a#  
            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
        r   z+Updated 'list' table name2 for patient_no: rU   zPatient info data updated.r   z0Error updating patient_info data for patient_no r   Tr   r]   r=   )r   r   r*   r`   r^   ra   r   rd   r   r   r   r   r7   )r~   r'  r(  r)  r*  r+  r   r   r,  r   r   r-  r.  r/  r   r0  r1  r2  r3  r4  r5  r6  r7  r8  r   r`   effective_patient_name2rj   r  update_list_name2_queryr   r(   r(   r)   save_patient_info  s<   



r;  z/sos/api/save_contact_timecontact_time_hcontact_time_mconsciousness_jcsconsciousness_econsciousness_vconsciousness_mrespiration_raterespiration_condition
pulse_ratepulse_1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
oxygen_use
ecg_statusauscultationpupil_right_sizepupil_right_reactionpupil_left_sizepupil_left_reactiongaze_deviationpalpebral_conjunctivavisual_impairment	nystagmus
convulsionaffected_area_conditionskin_condition	paralysisparalysis_areavomitvomit_countdiarrhea	first_aidfirst_aid_othertransport_positionadlc-           7   
      s.  t d|   d}-t }.g d}/|/D ]}0|.|0|-kr!d |.|0< qt }1|1 }2zzd}3g |||.d |||||.d |	|.d |.d |||||||||||.d	 |.d
 |.d ||.d ||.d |.d |.d |.d |.d |.d |"|.d |.d |%|.d |'|.d |.d |*|.d |.d | R }4|2|3|4 |d ur|d urd}5|2|5||| f t d|   |1  dddW W |2  |1  S  t	y }6 z|1
  t jd|  d |6 d!d" td#t|6d$d }6~6ww |2  |1  w )%Nz-Updating contact_time_T data for patient_no: r$  )r>  rC  rE  rF  rQ  rR  rS  rU  rW  rX  rY  rZ  r[  r\  r^  r_  ra  rc  rd  rf  rg  a  
            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
        r>  rC  rE  rF  rQ  rR  rS  rU  rW  rX  rY  rZ  r[  r\  r^  r_  ra  rc  rd  rf  rg  z
                UPDATE time 
                SET patient_contact_h = %s, patient_contact_m = %s 
                WHERE patient_no = %s
            z6Synced contact_time_T -> time.patient_contact for No: rU   zContact time data updated.r   z2Error updating contact_time_T data for patient_no r   Tr   r]   r=   r   r   localsgetr*   r`   ra   r   rd   r   r   r   r   r7   )7r~   r<  r=  r>  r?  r@  rA  rB  rC  rD  rE  rF  rG  rH  rI  rJ  rK  rL  rM  rN  rO  rP  rQ  rR  rS  rT  rU  rV  rW  rX  rY  rZ  r[  r\  r]  r^  r_  r`  ra  rb  rc  rd  re  rf  rg  r%  
local_varsfields_to_checkfieldr   r`   rj   r  sync_time_queryr   r(   r(   r)   save_contact_time  s   0					





ro  z/sos/api/save_memotextc              
      sf  t d|   t }| }zzg }|r |dd |D  |rktjtdd |D ]?}|jrjt	
  tj|jd  }tjt|}	t|	d}
t|j|
 W d    n1 s]w   Y  |d|  q+|rrd	|nd }d
}||||| f |  dddW W |  |  S  ty } z|  tdt|dd }~ww |  |  w )Nz#Updating memo data for patient_no: c                 S   r   r(   r   r   r(   r(   r)   r   {  r   zsave_memo.<locals>.<listcomp>Tr   ro   r   r   r   z9UPDATE memo SET text = %s, img = %s WHERE patient_no = %srU   z!Memo updated with multiple imagesr   r]   r=   )r   r   r*   r`   r   rD   r   r   r   r   r   rE   r   rF   rJ   r   r   r   r   ra   r   rd   r   r   r   r7   )r~   rp  r   r   r   r`   r   r   r   	full_pathr   r   rj   r   r(   r(   r)   	save_memol  sB   

rr  z/sos/api/save_before_arrivalc           $         s  t d|   d}t }g d}|D ]}|||kr!d ||< qt }| }zzhd}|||d |||||d |	|d |d |||||||||||d	 |d
 |d | f} |||  |d ur|d urd}!||!||| f d}"||"| f t d|   |  dddW W |  |  S  t	y }# z|
  t jd|  d|# dd tdt|#dd }#~#ww |  |  w )Nz-Updating before_arrival data for patient_no: r$  )r>  rC  rE  rF  rQ  rR  rS  a:  
            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
        r>  rC  rE  rF  rQ  rR  rS  z
                UPDATE time 
                SET arrival_hospital_h = %s, arrival_hospital_m = %s 
                WHERE patient_no = %s
            z
                UPDATE transfer_state 
                SET arrival_hospital_time = 1 
                WHERE patient_no = %s
            z7Synced before_arrival -> time.arrival_hospital for No: rU   zBefore arrival data updated.r   z2Error updating before_arrival data for patient_no r   Tr   r]   r=   rh  )$r~   r<  r=  r>  r?  r@  rA  rB  rC  rD  rE  rF  rG  rH  rI  rJ  rK  rL  rM  rN  rO  rP  rQ  rR  rS  r%  rk  rl  rm  r   r`   rj   r  rn  transfer_sync_queryr   r(   r(   r)   save_before_arrival  sN   


rt  z/sos/api/save_report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_organizationc                    s   t d|   t }| }zSz-d}|||||||||	|
|||||| f}||| |  dddW W |  |  S  tyc } z|  t j	d|  d| dd	 t
d
t|dd }~ww |  |  w )Nz%Updating report data for patient_no: a4  
            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
        rU   zReport data updated.r   z*Error updating report data for patient_no r   Tr   r]   r=   r  )r~   ru  rv  rw  rx  ry  rz  r{  r|  r}  r~  r  r  r  r  r  r   r`   rj   r  r   r(   r(   r)   save_report  s4   

r  z/sos/api/save_primary_triagechecklist_datac              
      s   t d|   t }| }z?z#t||  d}||||| f |  dddW W |  |  S  tyO } z|	  t
ddt| dd }~ww |  |  w )	Nz&UPSERT primary_triage for patient_no: zWUPDATE primary_triage SET urgency_level = %s, checklist_data = %s WHERE patient_no = %srU   zPrimary triage data saved.r   r]   rq   r=   )r   r   r*   r`   r   ra   r   rd   r   r   r   r7   )r~   r   r  r   r`   rj   r   r(   r(   r)   save_primary_triage/  s&   


r  z /sos/api/save_secondary_triage_1practitioner_name1practitioner_name2treatment_timerespirationtreatment_details
save_statec                    N  t  }| }zg }|r|dd |D  |rbtjtdd |D ]?}|jrat  tj	
|jd  }tj	t|}t|d}t|j| W d    n1 sTw   Y  |d|  q"|rid|nd }t||  d	}|||||||||	|
||||||||| f}||| |  d
diW |  |  S |  |  w )Nc                 S   r   r(   r   r   r(   r(   r)   r   e  r   z+save_secondary_triage_1.<locals>.<listcomp>Tr   ro   r   r   r   a  
            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
        r   rU   r*   r`   r   rD   r   r   r   r   r   rE   r   rF   rJ   r   r   r   r   r   ra   r   rd   r~   r  r  r  r  rJ  rK  rL  rM  rD  r>  r?  r@  rA  r  r   r   r   r  r  r   r`   r   r   u_namerq  r   r   rj   valsr(   r(   r)   save_secondary_triage_1H  B   
	


r  z /sos/api/save_secondary_triage_2c                    r  )Nc                 S   r   r(   r   r   r(   r(   r)   r     r   z+save_secondary_triage_2.<locals>.<listcomp>Tr   ro   r   r   r   a  
            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
        r   rU   r  r  r(   r(   r)   save_secondary_triage_2  r  r  z /sos/api/save_secondary_triage_3c                    sL  t  }| }zg }|r|dd |D  |rbtjtdd |D ]?}|jrat  tj	
|jd  }tj	t|}t|d}t|j| W d    n1 sTw   Y  |d|  q"|rid|nd }t||  d	}|||||||||	|
|||||||| f}||| |  d
diW |  |  S |  |  w )Nc                 S   r   r(   r   r   r(   r(   r)   r     r   z+save_secondary_triage_3.<locals>.<listcomp>Tr   ro   r   r   r   a  
            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
        r   rU   r  )r~   r  r  r  r  rJ  rK  rL  rM  rD  r>  r?  r@  rA  r  r   r   r   r  r   r`   r   r   r  rq  r   r   rj   r  r(   r(   r)   save_secondary_triage_3  sB   
	



r  c                   @      e Zd ZU eed< dZee ed< dZee ed< dZ	ee ed< dZ
ee ed< dZee ed< dZee ed< dZee ed	< d
Zee ed< dS Incident
patient_NoN	date_timer   patient_nameprimary_urgencysecondary_1_urgencysecondary_2_urgencysecondary_3_urgencyr   complete_listr4   r5   r6   r7   r8   r  r   r   r   r  r  r  r  r  r  ru   r(   r(   r(   r)   r       
 r  z/sos/api/transport_patients)response_modelc               
         t  } | jdd}z9zd}|| | }|W W |  |   S  tyC } ztjd| dd tddt	| dd }~ww |  |   w )	NTrX   a  
        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;
        z+Error fetching patient list for transport: r   r]   r   r=   
r*   r`   ra   rr   rd   r   r   r   r   r7   r   r`   rj   resultsr   r(   r(   r)   get_patient_list_for_transport  s"   


r  c                   @   r  r  r  r(   r(   r(   r)   r  A  r  z/sos/api/incidentsc               
      r  )	NTrX   a  
            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;
        z0Error fetching incident list from 'list' table: r   r]   r   r=   r  r  r(   r(   r)   get_incident_listL  s"   


r  z/sos/api/transfer_status_listc               
      s   t d t } | jdd}zLz*d}|| | }|s/t d g W W |  |   S |W W |  |   S  ty[ } zt jd| dd t	dd	t
| d
d}~ww |  |   w )u   
    모든 환자에 대한 이송 상태 (transfer_state) 정보를 반환합니다.
    (車内収容, 現場出発, 病院到着, 医師引継 상태)
    z"Fetching all transfer status data.TrX   a  
            SELECT
                patient_no,
                patient_loaded_time,
                depart_scene_time,
                arrival_hospital_time,
                handover_to_doctor_time
            FROM transfer_state
            ORDER BY patient_no DESC;
        z!No transfer status records found.z%Error fetching transfer status list: r   r]   r   r=   N)r   r   r*   r`   ra   rr   rd   r   r   r   r7   r  r(   r(   r)   get_transfer_status_listv  s.   






r  c              
   C   s   z.ddddd|| g}t j|dddd}td|j tjtj| d	 d
 }tj||W S  t	y:   td   t j
yU } ztd|j  td|j  d}~w tyh } ztd|   d}~ww )uM   
    LibreOffice를 사용하여 Excel 파일을 PDF로 변환합니다.
    libreofficez
--headlessz--convert-topdfz--outdirT)capture_outputrp  checkz"LibreOffice PDF conversion stdout:r   .pdfzZERROR: 'libreoffice' command not found. Is LibreOffice installed and in the system's PATH?z3ERROR: LibreOffice conversion failed. Return code: zStderr:Nz4An unexpected error occurred during PDF conversion: )
subprocessrunrf   stdoutrD   rE   r   basenamerF   FileNotFoundErrorCalledProcessError
returncodestderrr   )
excel_path
output_dircommandresultpdf_filenamer   r(   r(   r)   convert_excel_to_pdf  s4   	r  rP   r  c                 C   s*   |  d| d}t jt|}t j|S )uz   
    /home/air/sos/templates/ 폴더 안에
    prefix_patient_no.pdf (예: reportA_3.pdf) 가 존재하는지 확인
    _r  rD   rE   rF   TEMPLATE_DIRrI   )rP   r~   r   rE   r(   r(   r)   report_exists  s   r  z#/sos/api/report_status/{patient_no}c                    s(   t d| t d| t d| t d| dS )u   
    특정 patient_no 에 대해 A/B/C/triage 보고서 PDF 존재 여부를 반환
    예: { "A": true, "B": false, "C": true, "triage": false }
    reportAreportBreportCreport_triage)ABCtriage)r  r~   r(   r(   r)   get_report_status  s   r  z%/sos/api/generate_report/{patient_no}c           '         s  t  }|jdd}z9zd}||| f |   s!tdddW n ty9 } ztddt| dd }~ww W |  |  n	|  |  w ztj	
td	}d
|  d}tj	
t|}t|}|jfddg d}	g d}
t|	D ]3\}}| v r | d ur | }d|v sd|v rzt|}W n ttfy   Y nw |
| | q} drz%t d d}d|j d|j g d}| }d||  W n ttfy   Y nw  drd d   drd d   drd d   drd d   d r&d! d   i d"d#d$d%d&d'd(d)d*d+d,d-d.d/d0d1d2d3d4d5d6d7d8d9d:d;d<d=d>d?d@dAdBdCdDdEdFdG}| D ]\}} |d urx| |  qe dHrdI dH   dJrdK dJ  i dLdMdNdOdPdQdRdSdTdUdVdWdXdYdZd[d\d]d^d_d`dadbdcdddedfdgdhdidjdkdldmi dndodpdqdrdsdtdudvdwdxdydzd{d|d}d~dddddddddddddddddddddddd}| D ]\}} |d ur| |  q dd ur/d d   drD d rDd d   dd urb dd urbd d  d d   i ddddddddddddddddddddddddddddddddddddddddƜ}| D ]\}} |d ur| |  q dǡd urֈ dȡd urֈdɈ d  d d   ddddddϜ}| D ]\}} |d ur| |  q dСd urdш d  g dҢ}g }|D ]/} |r< | dӡD ]}tj	| }tj	
t|}tj	|r:|| qqd}g dբ}d}t|D ]X\}}|t | }|t | } || |  }!||  |! }"zt!|}#|#j"|#j# }$d|#_#d|$ |#_"$|#|" W qJ ty } zt%&d| d|  W Y d }~qJd }~ww  fddۄ}%|%dddރ |%ddd |%ddd |%ddd |'| t(|t}&dd||&dW S  t)y   tdd| d ty } ztddt| dd }~ww )NTrX     
            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
        r<   /Report data not found for the given patient_no.r=   r]   r   z20251112_A.xlsxreportA_.xlsxc              
      ~   z|d u rd  | < W d S | | < W d S  t y> } z td|  d td| dt| d td|   d }~ww )Nz'---!!! CRITICAL ERROR WRITING TO CELL:  !!!---    VALUE:  (Type: r       ERROR: r   r   r   typecellvaluer   sheetr(   r)   write_to_cellA  s   z&generate_report.<locals>.write_to_cell)r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r  r  )A8D8F8I8K8N8P8S8U8X8Z8AC8AE8AH8AJ8AM8AO8AR8AT8AW8H10AJ10_h_mr  %Y-%m-%dF3K3r   Q3r!  AT3r"  F4r#  F6r   Z3
cr_addressP4r'  F11r(  F12r)  Z11r*  AE11r+  AJ11pi_ageAO11	pi_genderAT11pi_phone_numberF15r-  F16r.  P16r/  Z16pi_urgency_levelAT16r0  K20r2  K21r3  AO21r4  AO35r5  K36AO36K37AO37)r6  r7  r8  
pi_addressF14r1  AO20ct_contact_time_hC24ct_contact_time_mF24ct_consciousness_jcsP24ct_consciousness_eM26ct_consciousness_vP26ct_consciousness_mS26ct_respiration_rateU24ct_respiration_conditionU26ct_pulse_rateAE25
ct_pulse_1AE24
ct_pulse_2AE26ct_temperature_textAO26ct_bp_right_1BB24ct_bp_right_2BE24ct_bp_left_1BB25ct_bp_left_2BE25ct_spo2_leftE27ct_spo2_rightK27ct_oxygen_useS27ct_oxygen_flow_rateY27ct_ecg_statusAH27ct_auscultationAW27ct_pupil_right_sizeF29ct_pupil_right_reactionK29ct_pupil_left_sizeF30ct_pupil_left_reactionK30ct_gaze_deviationQ29ct_palpebral_conjunctivaZ29ct_visual_impairmentQ30ct_nystagmusZ30ct_convulsionAH29ct_affected_area_conditionAW29ct_skin_conditionD31ct_paralysisS31X31AH31AP31AW31K34K35)ct_paralysis_areact_vomitct_vomit_countct_diarrheact_transport_positionct_adlct_first_aidK33ct_first_aid_otherW33ct_temperature_Lct_temperature_RAO24.ba_contact_time_hC40ba_contact_time_mF40ba_consciousness_jcsP40ba_consciousness_eM42ba_consciousness_vP42ba_consciousness_mS42ba_respiration_rateU40ba_respiration_conditionU42ba_pulse_rateAE41
ba_pulse_1AE40
ba_pulse_2AE42ba_temperature_textAO42ba_bp_right_1BB40ba_bp_right_2BE40ba_bp_left_1BB41ba_bp_left_2BE41ba_spo2_leftE43K43Y43S43AH43AW43)ba_spo2_rightba_oxygen_flow_rateba_oxygen_useba_ecg_statusba_auscultationba_temperature_Lba_temperature_RAO40Z15AT15A18AT17AT18)rw  report_severityry  r  r  	memo_textA45)r   st1_imgst2_imgst3_imgmemo_imgr   /   )r  HOVACAJAQAX      u   이미지 삽입 실패 (z): c                    sD     | d ur  |d ur | |   d |   d S d S d S )Nrq  rj  )l_fieldr_fieldr  )datar  r(   r)   combine_and_write  s    z*generate_report.<locals>.combine_and_writerz  r{  U18r|  r}  Z18r~  r  AE18r  r  AJ18rU   z3Report generated and converted to PDF successfully.r   rV   r  pdf_pathTemplate file not found at )File processing or PDF conversion error: )*r*   r`   ra   rb   r   r   r7   rd   rD   rE   rF   r  openpyxlload_workbookactive	enumerateru   
ValueError	TypeErrorrj  r   strptimer   r   r   itemsr^   splitr  r   rI   r   rs   r   heightwidth	add_imager   r   saver  r  )'r~   r   r`   rj   r   template_pathoutput_filenameexcel_output_pathworkbooktime_fields
time_cellsirm  r  date_objr   	day_indexpatient_info_mapr  contact_time_mapbefore_arrival_map
report_mapimage_fields	all_pathsrM   pfnamefpath	start_rowcolsrow_height_incrementcol_idx	row_groupcurrent_rowtarget_cellimg_objratior  r  r(   )r  r  r  r)   generate_report  s  P



			


 	 



r  z/sos/api/address/{patient_no}c              
      s   t d|   t }|jdd}zHz&d}||| f | }|r4d|d dW W |  |  S tdd	d
 tyZ } zt j	d|  d| dd tdt
|d
d }~ww |  |  w )Nz!Fetching address for patient_no: TrX   z7SELECT address FROM call_received WHERE patient_no = %srU   r   )r   r   r<   z+Address not found for the given patient_no.r=   z&Error fetching address for patient_no r   r   r]   r   r   r*   r`   ra   rb   rd   r   r   r   r7   )r~   r   r`   rj   r  r   r(   r(   r)   get_address  s(   

r  z'/sos/api/generate_report_b/{patient_no}c              
      s  t  }|jdd}z9zd}||| f | }|s!tdddW n ty9 } ztddt| dd }~ww W |  |  n	|  |  w zNtj	
td	}d
|  d}tj	
t|}t|}	|	j  fdd}
|dd ur|dd ur|
d|d  d|d   |dd ur|dd ur|
d|d  d|d   |dd ur|dd ur|
d|d  d|d   |dd ur|dd ur|
d|d  d|d   |dd ur|dd ur|
d|d  d|d   |dd ur|dd ur|
d |d  d|d   |d!d ur9|d"d ur9|
d#|d!  d|d"   |d$d urW|d%d urW|
d&|d$  d|d%   |d'd uru|d(d uru|
d)|d'  d|d(   |d*d ur|d+d ur|
d,|d*  d|d+   |d-d ur|d.d ur|
d/|d-  d|d.   |
d0|d1 |
d2|d3 |d4rz%t|d4 d5}|
d6|j |
d7|j g d8}| }|
d9||  W n ttfy   Y nw |
d:|d; |
d<|d= |
d>|d? i d@dAdBdCdDdEdFdGdHdIdJdKdLdMdNdOdPdQdRdSdTdUdVdWdXdYdZd[d\d]d^d_d`dadbdcdddedf}| D ]\}}|
||| qQi dgdhdidjdkdldmdndodpdqdrdsdtdudvdwdxdydzd{d|d}d~ddddddddddi ddddddddddddddddddddddddddddddddddddddddddd}| D ]\}}|
||| q|dd ur|dd ur|
d|d  d|d   i ddddddddddddÓddœddǓddɓdd˓dd͓ddϓddѓddӓddՓddדddٓddddddߜ}| D ]\}}|
||| qE|dd urq|dd urq|
d|d  d|d   dddddd}| D ]\}}|
||| q}|	| t|t}dd||dW S  ty   tdd| d ty } ztddt| dd }~ww )NTrX   r  r<   r  r=   r]   r   z20251112_B.xlsxreportB_r  c              
      r  )Nz2---!!! CRITICAL ERROR WRITING TO CELL (Report B): r  r  r  r   r  r  r  r  r(   r)   r  e     z(generate_report_b.<locals>.write_to_cellr   r   r  :r   r   B8r   r   r  r   r   r  r   r   J8r   r   M8r   r   Q8r   r   T8r   r   W8r   r   AB8r   r   AF8C10r  R10r  r  r  B2D2r   G2L2r   D3r  B6r#  r'  B11r)  N11r  r  r  AG11r(  B12r*  N12r+  N13r  B14r  B15r-  B16r.  I16r/  r  r0  E21r1  V21r2  E22r3  V22r4  Z37F38Z38F39Z39)r5  r6  r7  r8  r  F25r&  H25r,  M25r2  AD25r4  AG25r  A26r  C26r(  H26r*  N26r0  r)  r6  AD26r8  AG26r   r;  r"  F27r$  G27r.  M27r:  C28r<  G28r>  M28r@  Q28rB  Y28rD  AG28rF  D29rH  G29rN  M29rP  U29rV  AC29rX  AG29rJ  D30rL  G30rR  O30rT  V30rZ  B32r\  N32AB32AD32AH32U33C34rb  C36F37)re  rf  rg  rd  rj  rl  rh  rg  rn  ro  U25rq  rv  F42r~  H42r  ry  r  AD42r  AG42rr  A43rt  C43r  H43r  N43r  U43r  AD43r  AG43rx  E44rz  F44r|  G44r  M44r  C45G45M45Q45Y45AG45)r  r  r  r  r  r  r  r  L15AD15AA17r  H18rw  r  rv  ry  ru  rU   z5Report B generated and converted to PDF successfully.r  r  r  r*   r`   ra   rb   r   r   r7   rd   rD   rE   rF   r  r  r  r  rj  r   r  r   r   r   r  r  r  r  r  r  )r~   r   r`   rj   r  r   r  r  r  r  r  r  r   r  pi_maprm  r  ct_mapba_mapr  r  r(   r  r)   generate_report_b  s  P


88888<<<<<<			


 	 

r[  z'/sos/api/generate_report_c/{patient_no}c                    s  t  }|jdd}z9zd}||| f | }|s!tdddW n ty9 } ztddt| dd }~ww W |  |  n	|  |  w zLtj	
td	}d
|  d}tj	
t|}t|}	|	j  fdd}
|dd ur|dd ur|
d|d  d|d   |dd ur|dd ur|
d|d  d|d   |dd ur|dd ur|
d|d  d|d   |dd ur|dd ur|
d|d  d|d   |dd ur|dd ur|
d|d  d|d   |dd ur|dd ur|
d |d  d|d   |d!d ur9|d"d ur9|
d#|d!  d|d"   |d$d urW|d%d urW|
d&|d$  d|d%   |d'd uru|d(d uru|
d)|d'  d|d(   |d*d ur|d+d ur|
d,|d*  d|d+   |d-d ur|d.d ur|
d/|d-  d|d.   |d0d ur|d1d ur|
d2|d0  d|d1   |d3d ur|d4d ur|
d5|d3  d|d4   |
d6|d7 |
d8|d9 |d:r5z%t|d: d;}|
d<|j |
d=|j g d>}| }|
d?||  W n ttfy4   Y nw |
d@|dA |
dB|dC |
dD|dE dFdGdHdIdJdKdLdMdNdOdPdQdRdS}| D ]\}}|
||| qadTdUdVdWdXdY}| D ]\}}|
||| q{|	| t|t}dZd[||d\W S  ty   tdd]| d ty } ztdd^t| dd }~ww )_NTrX   a  
            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
        r<   r  r=   r]   r   z20251112_C.xlsxreportC_r  c              
      r  )Nz2---!!! CRITICAL ERROR WRITING TO CELL (Report C): r  r  r  r   r  r  r  r  r(   r)   r  =  r  z(generate_report_c.<locals>.write_to_cellr   r   r  r  r   r   r  r   r   r  r   r   r  r   r   r  r   r   r  r   r   r  r   r   r  r   r   r  r   r   r  r   r   r  r   r  r  r  r  AH9r  r  r  r  r  r  r  r  r   r  r  r   r   r  r  r#  r  r  Z12r  AG13r  r  r  r  r	  r
  r  r  )r'  r)  r  r  pi_occupationr(  r*  r+  r  r  r-  r.  r/  rR  rS  rT  r  rU  rV  rU   z5Report C generated and converted to PDF successfully.r  r  r  rW  )r~   r   r`   rj   r  r   r  r  r  r  r  r  r   r  rX  rm  r  r  r  r(   r  r)   generate_report_c  s   P


88888<<<<<<<<


ra  z*/sos/api/generate_triage_list/{patient_no}c              
      s  t  }|jdd}zqzPd}|| | }|s!tdddtjtd}d|  d	}tjt|}t	
|}|j}	td
dd}
tddd}tddd}tddd}|
|||d}|d }|d|d|d|d|df\|	d< |	d< |	d< |	d< |	d< dd }t|D ]\}}d| }|d |	d| < |d  |	d!| < d"|d#fd$|d%fd&|d'fd(|d)fg}|D ]\}}||}|	| |  }||_||v r|| |_q|d* pd+|	d,| < |d- pd+|	d.| < |d/pd+|	d0| < |d1p	d+|	d2| < |d3pd+|	d4| < |d5p#d+|	d6| < |dp0d+|	d7| < |dp=d+|	d8| < q|| t|t}d9||d:W W |  |  S  ty| } ztjd;| dd< td=t|dd }~ww |  |  w )>NTrX   aE  
            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
        r<   u'   登録された傷病者がいませんr=   z20251112_toriazi.xlsxreport_triage_r  FF0000)colorboldFFC00000B050000000)   赤   黄   緑   黒r   r   r   r   r   r   I1K1M1N1P1c                 S   s   ddddd}| | dS )Nri  rj  rk  rl  )RYGr  r  r  )r   mappingr(   r(   r)   get_triage_text  s   z-generate_triage_list.<locals>.get_triage_text   ro   r  rn   r  r  r  Dsecondary1_urgencyEsecondary2_urgencyFsecondary3_urgencyr   r  rt  r   r  
name_kanjiIr   Jr   Sr   TWrs  rU   )r   r  r  u#   PDF作成中にエラーが発生: r   r]   )r*   r`   ra   rr   r   rD   rE   rF   r  r  r  r  r   rj  r  r  fontr  r  rd   r   r   r   r7   )r~   r   r`   rj   patientsr  excel_filenamer  r   wsfont_redfont_yellow
font_green
font_blackfont_mapfirstrv  idxr  ry   triage_levelscolr   rp  r  r  r   r(   r(   r)   generate_triage_list  sv   

* 




r  z/sos/api/get_times/{patient_no}c              
      s   t d|   t }|jdd}zPz!d}||| f | }|r/|W W |  |  S tddd tyB } z|d }~w tyb } zt j	d|  d	| dd
 tdt
|dd }~ww |  |  w )Nz,Fetching specific time data for patient_no: TrX   z
            SELECT 
                patient_contact_h, 
                patient_contact_m, 
                arrival_hospital_h, 
                arrival_hospital_m 
            FROM time 
            WHERE patient_no = %s
        r<   z-Time data not found for the given patient_no.r=   z(Error fetching time data for patient_no r   r   r]   r  )r~   r   r`   rj   	time_datar  r   r(   r(   r)   	get_times  s.   		

r  z/sos/api/update_patient_data)aliasc           )         s  t d|   t }| }zOz1|d| f | }|r%|d nd }|r.|dng }|r{tjt	dd |D ]?}|j
rzt  tj|j
d  }tjt	|}t|d}t|j| W d    n1 smw   Y  |d	|  q;|rd|nd }td
}t|}|d}g d}||  } |r| r|nd}!|d| f | sd}"||"| ||!|||||||	|
||||||| |f g d}#|#D ]}$|d|$ d| f q|d| |f |d| || |!f n:|rdnd}%d|% d}&||!|||||||	|
|||||g}'|r|'| |'|  ||&t|' |d|!| f |  dd|  ddW W |  |  S  ty` }( z|   t!ddt"|( d d }(~(ww |  |  w )!NzUPSERT patient data for No: z%SELECT img FROM patient WHERE No = %sr   r   Tr   ro   r   r   r   r   r   r   r   a  
                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)
            )
r   r   r   r   r   r   r   r   r   r   r   r   r   r   z
, img = %sr  af  
                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
                    z+
                WHERE No = %s
            r   rU   zPatient data saved (No=r   r   r]   rq   r=   )#r   r   r*   r`   ra   rb   r  rD   r   r   r   r   r   rE   r   rF   rJ   r   r   r   r   r   r   r   r   r   r   r^   tupler   rd   r   r   r   r7   ))rn   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r`   ry   existing_img_strr   r   r   rq  r   r   r   r   r   r   r   r   r   r   r   
img_clauser   paramsr   r(   r(   r)   update_patient_data	  s|   


	
"	


r  z/sos/api/complete_incidentc              
      s   t  }| }znzJd}||| f | r/td|  d dddW W |  |  S d}||| f td|  d	 |  dd
dW W |  |  S  tyv } z|	  t
d|  d|  tdt|dd }~ww |  |  w )Nz:SELECT id FROM sick_and_wounded_list WHERE patient_no = %szPatient z% is already in sick_and_wounded_list.rU   z'Already moved to Sick and Wounded List.r   z:INSERT INTO sick_and_wounded_list (patient_no) VALUES (%s)zMoved patient z. from Triage List to Sick List (Manual Button)z(Incident moved to Sick and Wounded List.zError completing incident for r   r]   r=   )r*   r`   ra   rb   r   r   rd   r   r   r   r   r   r7   )r~   r   r`   check_queryr   r   r(   r(   r)   complete_incident
  s4   


r  c                 C   s&   t jt|  d| d}t j|S )uz   
    /home/air/sos/templates/ 폴더 안에
    prefix_patient_no.pdf (예: reportA_X.pdf) 가 존재하는지 확인
    r  r  r  )rP   r~   rE   r(   r(   r)   r  <
  s   z/sos/api/report_status_listc            
         sR  t  } | jdd}zzsd}|| | }g }|D ]W}|d}|r)t|ts/td qz|t	d|t	d|t	d|t	d	|d
}|
| W q tyr } ztd| d|  |
|ddddd
 W Y d}~qd}~ww |W W |  |   S  ty }	 ztjd|	 dd tddt|	 dd}	~	ww |  |   w )u   
    모든 환자에 대해 4가지 필수 보고서의 작성 현황을 반환합니다.
    (patient_no, A, B, C, Triage의 bool 상태 포함)
    TrX   zSELECT No FROM patientrn   z0Skipping non-string/None patient_no found in DB.r  r  r  r  )r~   r  r  r  r  z"File check failed for patient_no: z	. Error: FNz#Error fetching report status list: r   r]   r   r=   )r*   r`   ra   rr   rj  
isinstancer7   r   r
  r  r   r   r   rd   r   )
r   r`   rj   patient_nosr  r  r~   r   
file_errorr   r(   r(   r)   get_report_status_listF
  sL   





r  z#/sos/api/get_time_data/{patient_no}c              
         t d|   t }|jdd}zLz-d}||| f | }|r2d|dW W |  |  S di dW W |  |  S  ty^ } zt jd| dd t	d	t
|d
d }~ww |  |  w )Nz"Fetching FULL time data for edit: TrX   z(SELECT * FROM time WHERE patient_no = %srU   r   r  zError fetching time data: r   r]   r=   r   r   r*   r`   ra   rb   rd   r   r   r   r7   r~   r   r`   rj   ry   r   r(   r(   r)   get_time_data_fullz
  ,   


r  z,/sos/api/get_call_received_data/{patient_no}c              
      r  )Nz+Fetching FULL call_received data for edit: TrX   z1SELECT * FROM call_received WHERE patient_no = %srU   r  z#Error fetching call_received data: r   r]   r=   r  r  r(   r(   r)   get_call_received_data
  r  r  z+/sos/api/get_patient_info_data/{patient_no}c              
      r  )Nz*Fetching FULL patient_info data for edit: TrX   z0SELECT * FROM patient_info WHERE patient_no = %srU   r  z"Error fetching patient_info data: r   r]   r=   r  r  r(   r(   r)   get_patient_info_data
  r  r  z-/sos/api/get_contact_time_T_data/{patient_no}c              
      r  )Nz,Fetching FULL contact_time_T data for edit: TrX   z2SELECT * FROM contact_time_T WHERE patient_no = %srU   r  z$Error fetching contact_time_T data: r   r]   r=   r  r  r(   r(   r)   get_contact_time_T_data
  r  r  z#/sos/api/get_memo_data/{patient_no}c           
   
      s.  t d|   t }|jdd}z{z\d}||| f | }|ra|d g d}|drR|d d}t|D ]\}}|	 rQ|d	 
d
| |	 d q:d|dW W |  |  S di dW W |  |  S  ty }	 zt jd|	 dd tdt|	dd }	~	ww |  |  w )Nz"Fetching FULL memo data for edit: TrX   z(SELECT * FROM memo WHERE patient_no = %srp  )rp  r   r   r   r   server_img_)rR   urlrU   r  zError fetching memo data: r   r]   r=   )r   r   r*   r`   ra   rb   rj  r  r  r^   r   rd   r   r   r   r7   )
r~   r   r`   rj   ry   result_datapathsr  rE   r   r(   r(   r)   get_memo_data
  s@   



r  z-/sos/api/get_before_arrival_data/{patient_no}c              
      r  )Nz,Fetching FULL before_arrival data for edit: TrX   z2SELECT * FROM before_arrival WHERE patient_no = %srU   r  z$Error fetching before_arrival data: r   r]   r=   r  r  r(   r(   r)   get_before_arrival_data
  r  r  z%/sos/api/get_report_data/{patient_no}c              
      r  )Nz$Fetching FULL report data for edit: TrX   z*SELECT * FROM report WHERE patient_no = %srU   r  zError fetching report data: r   r]   r=   r  r  r(   r(   r)   get_report_data  r  r  z&/sos/api/get_patient_data/{patient_no}c           	   
      s0  t d|   t }|jdd}z|z]d}||| f | }|rb|drS|d d}g }|D ]}d|v rF|dt	j
|  q3|| q3d||d< d	|d
W W |  |  S d	i d
W W |  |  S  ty } zt jd| dd tdt|dd }~ww |  |  w )NzFetching patient data for: TrX   z#SELECT * FROM patient WHERE No = %sr   r   /home/r   rU   r  zError fetching patient data: r   r]   r=   )r   r   r*   r`   ra   rb   rj  r  r   rD   rE   r  rF   rd   r   r   r   r7   )	r~   r   r`   rj   ry   	raw_pathsconverted_pathsrE   r   r(   r(   r)   get_patient_data,  s<   



r  z-/sos/api/get_primary_triage_data/{patient_no}c              
      s   t d|   t }|jdd}z=z d}||| f | }d|r&|ni dW W |  |  S  tyO } zt d|  t	dt
|d	d }~ww |  |  w )
Nz"Fetching primary triage data for: TrX   z2SELECT * FROM primary_triage WHERE patient_no = %srU   r  zError fetching primary triage: r]   r=   r  r  r(   r(   r)   get_primary_triage_dataN  s$   

r  
table_namec           	         s
  t d| d|   t }|jdd}zfd| d}||| f | }|rn|dr`|d d}g }|D ]}| }|sBq9d	|v rS|	d
t
j|  q9|	| q9d||d< d|dW |  |  S di dW |  |  S |  |  w )Nz	Fetching z for: TrX   zSELECT * FROM z WHERE patient_no = %sr   r   r  r   rU   r  )r   r   r*   r`   ra   rb   rj  r  r^   r   rD   rE   r  rF   rd   )	r~   r  r   r`   rj   ry   r  r  rE   r(   r(   r)   get_secondary_triage_common`  s6   





r  z1/sos/api/get_secondary_triage_1_data/{patient_no}c                       t | dI d H S )Nr   r  r  r(   r(   r)   get_secondary_triage_1_data     r  z1/sos/api/get_secondary_triage_2_data/{patient_no}c                    r  )Nr   r  r  r(   r(   r)   get_secondary_triage_2_data  r  r  z1/sos/api/get_secondary_triage_3_data/{patient_no}c                    r  )Nr   r  r  r(   r(   r)   get_secondary_triage_3_data  r  r  z/sos/api/patient_listc                     sr  t  } | jdd}zzkd}|| | }g }|D ]G}|d |d |d |d g}||d |d	 r8|d	 nd
|d rA|d nd
|d rJ|d nd|d rS|d nd|d r\|d nd|d qt|dW W |ro|  | rv|   S S  tjj	y } z&t
d|  tdt|iddW  Y d }~W |r|  | r|   S S d }~ww |r|  | r|   w w )NTrX   u  
            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
        triage_1triage_2triage_3triage_4r~   r-   -kanar   r  r   r2  )r~   r-   r  r   r   r2  r  rB   rq   r   r]   rC   r>   )r*   r`   ra   rr   r   r   rd   r%   r&   re   r   r   r7   )ri   r`   sqlrv   r  ry   triage_datark   r(   r(   r)   get_patient_list  sX   


 

r  z/sos/api/patient_detailc              
      sH  t  }|jdd}zzVd}||| f | }|s2tddiddW W |r*|  |r1|  S S |d r:|d nd	}|d
 rJ|d|d
  d7 }||d< t|dW W |rZ|  |ra|  S S  tjjy } z&t	
d|  tdt|iddW  Y d }~W |r|  |r|  S S d }~ww |r|  |r|  w w )NTrX   u$  
            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
        r   zPatient not foundr<   r  rd  r  re  (r   first_aid_fullrB   rq   r]   )r*   r`   ra   rb   r   rd   r%   r&   re   r   r   r7   )r~   ri   r`   r  ry   first_aid_textrk   r(   r(   r)   get_patient_detail  sP    !

 

r  z/sos/api/login/firec                    s   t  }|jdd}z6d}||| j| jf | }|r0td|d ddW |  |  S tdd	id
dW |  |  S |  |  w )NTrX   zESELECT * FROM ambulance_teams WHERE team_code = %s AND call_name = %sLogin SuccessrZ   )rV   r!   rB   rV   Login Failed  r  r*   r`   ra   rR   r"   rb   r   rd   rS   ri   r`   r  r!   r(   r(   r)   
login_fire  s    


r  z/sos/api/login/medicalc                    s   t  }|jdd}z3d}||| j| jf | }|r-tddidW |  |  S tddidd	W |  |  S |  |  w )
NTrX   zASELECT * FROM medical_users WHERE login_id = %s AND password = %srV   r  rB   r  r  r  r  r  r(   r(   r)   login_medical  s    


r  z/sos/api/complete_sick_woundedc              
      s   t  }| }z5zd}||| f |  dddW W |  |  S  ty= } z|  tdt|dd }~ww |  |  w )NzHUPDATE sick_and_wounded_list SET complete_list = 1 WHERE patient_no = %srU   z3Incident completed (sick_and_wounded_list updated).r   r]   r=   )	r*   r`   ra   r   rd   r   r   r   r7   )r~   r   r`   rj   r   r(   r(   r)   complete_sick_wounded  s"   

r  )lfastapir   r   r   r   r   pydanticr   typingr   r	   r
   mysql.connectorr%   rD   r   r   r  openpyxl.drawing.imager   openpyxl.stylesr   fastapi.middleware.corsr   r   r   fastapi.staticfilesr   fastapi.responsesr   r   r   logging.handlersr   basicConfigINFOappadd_middlewareenvironrj  r   r   r   r   r_   r   r  r*   mountr0   rN   rO   rQ   postrl   r}   r7   r   r   r  r  r&  r;  ro  rr  rt  r  r  ru   r  r  r  r  r  r  r  r  r  boolr  r  r  r  r[  ra  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r(   r(   r(   r)   <module>   s   			

,.&	
 *	
~	
p0	
D	
 !"#$%&'()*+,-v+	
a	
4	
?	
?	
>
,
)
$
  

 
X 
5
c 
?	

o"


3




 


!
 



J 
@

 