o
     i                 ]   @   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Zd dl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ddZ ejddZ!ejddZ"ejddZ#dZ$dZ%dd Z&ej'de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ededfde)de	e) de	e) d e	e) d!e	e) d"e	e) d#e	e) d$e	e) d%e	e) d&e	e) d'e	e) d(e	e) d)e	e) d*e	e) d+e	e) d,e	e) d-e	e) d.e	e f$d/d0Z*e(d1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d2e)d3e	e) d4e	e) d5e	e) d6e	e) d7e	e) d8e	e) d9e	e) d:e	e) d;e	e) d<e	e) d=e	e) d>e	e) d?e	e) d@e	e) dAe	e) dBe	e) dCe	e) dDe	e) dEe	e) dFe	e) dGe	e) dHe	e) dIe	e) dJe	e) dKe	e) dLe	e) dMe	e) dNe	e) f:dOdPZ+e(dQ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d2e)d3e	e) d4e	e) d5e	e) d6e	e) d7e	e) d8e	e) d9e	e) d:e	e) d;e	e) d<e	e) d=e	e) d>e	e) d?e	e) d@e	e) dAe	e) dBe	e) dCe	e) dDe	e) dEe	e) dFe	e) dGe	e) dHe	e) dIe	e) dJe	e) dKe	e) dLe	e) dMe	e) dNe	e) f:dRdSZ,e(dTedededededededfd2e)dUe	e) dVe	e) dWe	e) dXe	e) d"e	e) dYe	e) fdZd[Z-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d2e)d]e	e) d^e	e) d_e	e) d`e	e) dae	e) d e	e) d!e	e) dbe	e) d"e	e) d#e	e) dce	e) dde	e) dee	e) d-e	e) dfe	e) dge	e) dhe	e) die	e) dje	e) dke	e) dle	e) dme	e) dne	e) f0dodpZ.e(dq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-d2e)dre	e) dse	e) dte	e) due	e) dve	e) dwe	e) dxe	e) dye	e) dze	e) d{e	e) d|e	e) d}e	e) d~e	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) fZddZ/e(dededeg fd2e)de	e) dee fddZ0e(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d2e)dre	e) dse	e) dte	e) due	e) dve	e) dwe	e) dxe	e) dye	e) dze	e) d{e	e) d|e	e) d}e	e) d~e	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) f2ddZ1e(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d2e)de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) f ddZ2e(dde3fddZ4e(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d2e)de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) dze	e) dte	e) due	e) dve	e) dwe	e) de	e) d-e	e) de	e f"ddƄZ5e(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d2e)de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) dze	e) dte	e) due	e) dve	e) dwe	e) de	e) d-e	e) de	e f"ddɄZ6e(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d2e)de	e) de	e) de	e) de	e) de	e) de	e) de	e) de	e) dze	e) dte	e) due	e) dve	e) dwe	e) de	e) d-e	e) de	e f"dd̄Z7G dd΄ deZ8ejdee8 dЍdd҄ Z9G dd΄ deZ8ejdee8 dЍddք Z:d dl;Z;dd؄ Z<de)d2e)de=fdd܄Z>edݡd2e)fdd߄Z?edd2e)fddZ@edd2e)fddZAedd2e)fddZBedd2e)fddZCedd2e)fddZDedd2e)fddZEdS )    )FastAPIHTTPExceptionFile
UploadFileForm)	BaseModel)OptionalDictListN)Image)datetime)StaticFiles)RotatingFileHandlerz)%(asctime)s - %(levelname)s - %(message)s)levelformatDB_HOST	localhostDB_USERzsos-userDB_PASSWORDzPassWord12!!DB_NAMEsosz/home/air/sos/uploads/z/home/air/sos/templates/c                   C   s   t jjttttdS )N)hostuserpassworddatabase)mysql	connectorconnectr   r   r   r    r   r   $/home/air/sos/back/save_all_times.pyget_db_connection#   s   r    z/reports)	directoryreports)namez/sos/api/save_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urgency_levelimgc           &         s  t d|   t }| }z+z|d| f | r,t d|   tdddd }|rqtj	t
dd t  tj|jd	  }tjt
|}t|d
}t|j| W d    n1 sdw   Y  t d|  d}td}t|}|d}g d}||  }|r| r|nd}| ||||||||||	|
|||||||f}||| t d|   g d}|D ]} |d|  d| f qd}!||!| |f t d|  d d}"| |||f}#||"|# t d|   |  dddW W |  |  S  ty }$ z|$d }$~$w ty< }% z|  t j d|  d|% dd td t!|%dd }%~%ww |  |  w )!Nz$Attempting to save patient with No: $SELECT No FROM patient WHERE No = %sz1Attempted to create a patient with duplicate No:   Z   この番号は既に使用されています。別の番号を入力してください。status_codedetailTexist_ok   wbzSaved patient image to a\  
            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)
        
Asia/Tokyo%Y-%m-%d %H:%M:%Su   月u   火u   水u   木u   金u   土u   日	   未入力zInserted new patient with No: )
timecall_receivedprimary_triagesecondary_triage_1secondary_triage_2secondary_triage_3contact_time_Tmemobefore_arrivalreportINSERT INTO  (patient_no) VALUES (%s)zDINSERT INTO patient_info (patient_no, urgency_level) VALUES (%s, %s)z!Created placeholder rows for No: zA in all related tables, including urgency_level for patient_info.zTINSERT INTO list (patient_no, date_time, day_of_week, name2) VALUES (%s, %s, %s, %s).Added summary to 'list' table for patient_no: successz,Patient data saved and placeholders created.statusmessagezError in save_patient for No : exc_info  )"logginginfor    cursorexecutefetchonewarningr   osmakedirs
UPLOAD_DIRuuiduuid4pathsplitextfilenamejoinopenshutilcopyfileobjfilepytztimezoner   nowstrftimeweekdaystripcommitclose	Exceptionrollbackerrorstr)&r$   r%   r&   r'   r(   r)   r*   r+   r,   r-   r.   r/   r0   r1   r2   r3   r4   r5   cnxr[   img_pathunique_filenamebufferinsert_patient_queryjstnow_jstdate_time_strweekdaysday_of_weekeffective_name2patient_valuesplaceholder_tablestableinsert_pi_queryinsert_list_querylist_valueshttp_excer   r   r   save_patient3   st   



	

r   z"/sos/api/initiate_record_from_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_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_dbr6   z8Attempted to create a record with duplicate patient_no: r7   r8   r9   r@   rA   rB   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)rP   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: )	rE   rF   rG   rH   rI   rJ   rK   rL   rM   rN   rO   zFINSERT INTO patient_info (patient_no, urgency_level) VALUES (%s, NULL)z)Created placeholder rows for patient_no: z in remaining related tables.rQ   zITime-based record initiated with detailed times and placeholders created.rR   z2Error in initiate_record_from_time for patient_no rU   TrV   rX   )rY   rZ   r   rw   r    r[   r\   r]   r^   r   rl   rm   r   rn   ro   rp   rr   rs   rt   ru   rv   )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   rx   r[   r}   r~   r   r   r   r|   r   r   r   time_insert_querytime_valuesr   r   r   r   r   r   r   r   initiate_record_from_time   sp    







r   z/sos/api/save_all_timesc           "         s   t d|   t }| }z`z:d}|||||||||	|
||||||||||||||||||| f} |||  |  dddW W |  |  S  typ }! 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
        rQ   zTime data updated.rR   z(Error updating time data for patient_no rU   TrV   rX   r9   rY   rZ   r    r[   r\   rr   rs   rt   ru   rv   r   rw   )"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   rx   r[   queryvaluesr   r   r   r   save_all_times  s8    


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
        rQ   zCall received data updated.rR   z1Error updating call_received data for patient_no rU   TrV   rX   r9   r   )r   r   r   r   r   r)   r   placeholderrx   r[   r   r   r   r   r   r   save_call_receivedY  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: rC   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
        z0UPDATE list SET name2 = %s WHERE patient_no = %sz+Updated 'list' table name2 for patient_no: rQ   zPatient info data updated.rR   z0Error updating patient_info data for patient_no rU   TrV   rX   r9   )rY   rZ   r    r[   rq   r\   rr   rs   rt   ru   rv   r   rw   )r   r   r   r   r   r   r'   r(   r   r)   r*   r   r   r   r4   r   r   r   r   r   r   r   r   r   rx   r[   effective_patient_name2r   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-           6   
      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 |1  dddW W |2  |1  S  t	y }5 z|1
  t jd|  d|5 dd  td!t|5d"d }5~5ww |2  |1  w )#Nz-Updating contact_time_T data for patient_no: r   )r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   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   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   rQ   zContact time data updated.rR   z2Error updating contact_time_T data for patient_no rU   TrV   rX   r9   rY   rZ   localsgetr    r[   r\   rr   rs   rt   ru   rv   r   rw   )6r   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   r   r   r   r   r   r   r   
local_varsfields_to_checkfieldrx   r[   r   r   r   r   r   r   save_contact_time  s   0					





r  z/sos/api/save_memotextimagesc              
      sN  t d|   t }| }zzgd }|r[|d }tjtdd t  tj	
|jd  }tj	t|}t|d}t|j| W d    n1 sNw   Y  t d|  d}	||	||| f |  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 memo data for patient_no: r   Tr<   r>   r?   zSaved memo image to z9UPDATE memo SET text = %s, img = %s WHERE patient_no = %srQ   zMemo data updated.rR   z(Error updating memo data for patient_no rU   rV   rX   r9   )rY   rZ   r    r[   r_   r`   ra   rb   rc   rd   re   rf   rg   rh   ri   rj   rk   r\   rr   rs   rt   ru   rv   r   rw   )r   r  r  rx   r[   ry   r5   rz   r{   r   r   r   r   r   	save_memo;  s:   

r  z/sos/api/save_before_arrivalc           "         s:  t d|   d}t }g d}|D ]}|||kr!d ||< qt }| }zjzDd}|||d |||||d |	|d |d |||||||||||d	 |d
 |d | f} |||  |  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   r   r   r   r   r   r   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   r   r   r   r   r   r   rQ   zBefore arrival data updated.rR   z2Error updating before_arrival data for patient_no rU   TrV   rX   r9   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  rx   r[   r   r   r   r   r   r   save_before_arrival_  sB   


r  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
        rQ   zReport data updated.rR   z*Error updating report data for patient_no rU   TrV   rX   r9   r   )r   r  r	  r
  r  r  r  r  r  r  r  r  r  r  r  r  rx   r[   r   r   r   r   r   r   save_report  s4   

r  z/sos/api/save_primary_triagedatac              
      s   |  d}|  d}|stdddtd|  t }| }zCzd}||||f |  dd	d
W W |  |  S  t	ye } z|
  tjd| d| dd tdt|dd }~ww |  |  w )Nr   r4   i  zpatient_no is required.r9   z(Updating primary_triage for patient_no: zBUPDATE primary_triage SET urgency_level = %s WHERE patient_no = %srQ   zPrimary triage data updated.rR   z-Error updating primary_triage for patient_no rU   TrV   rX   )r   r   rY   rZ   r    r[   r\   rr   rs   rt   ru   rv   rw   )r  r   r4   rx   r[   r   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
img_uploadc                    f  t d|   d }|rNtjtdd t  tj|j	d  }tj
t|}t|d}t|j| W d    n1 sAw   Y  t d|  t }| }zTz.d}|||||||||	|
||||||| f}||| |  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 secondary_triage_1 for patient_no: Tr<   r>   r?   z"Saved secondary_triage_1 image to 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
            WHERE patient_no = %s
        rQ   z Secondary triage 1 data updated.rR   z1Error updating secondary_triage_1 for patient_no rU   rV   rX   r9   rY   rZ   r_   r`   ra   rb   rc   rd   re   rf   rg   rh   ri   rj   rk   r    r[   r\   rr   rs   rt   ru   rv   r   rw   r   r  r  r  r  r   r   r   r   r   r   r   r   r   r  r4   r  ry   rz   r{   rx   r[   r   r   r   r   r   r   save_secondary_triage_1  B   	


r#  z /sos/api/save_secondary_triage_2c                    r   )Nz,Updating secondary_triage_2 for patient_no: Tr<   r>   r?   z"Saved secondary_triage_2 image to 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
            WHERE patient_no = %s
        rQ   z Secondary triage 2 data updated.rR   z1Error updating secondary_triage_2 for patient_no rU   rV   rX   r9   r!  r"  r   r   r   save_secondary_triage_25  r$  r%  z /sos/api/save_secondary_triage_3c                    r   )Nz,Updating secondary_triage_3 for patient_no: Tr<   r>   r?   z"Saved secondary_triage_3 image to 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
            WHERE patient_no = %s
        rQ   z Secondary triage 3 data updated.rR   z1Error updating secondary_triage_3 for patient_no rU   rV   rX   r9   r!  r"  r   r   r   save_secondary_triage_3p  r$  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S 
Incident
patient_NoN	date_timer   patient_nameprimary_urgencysecondary_1_urgencysecondary_2_urgencysecondary_3_urgency__name__
__module____qualname__rw   __annotations__r+  r   r   r   r,  r-  r.  r/  r0  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 )	NT
dictionarya  
        SELECT
            p.No as patient_No,
            p.date_time,
            p.day_of_week,
            p.name2 as patient_name,
            pt.urgency_level as primary_urgency,
            st1.urgency_level as secondary_1_urgency,
            st2.urgency_level as secondary_2_urgency,
            st3.urgency_level as secondary_3_urgency
        FROM
            patient p
        LEFT JOIN primary_triage pt ON p.No = pt.patient_no
        LEFT JOIN secondary_triage_1 st1 ON p.No = st1.patient_no
        LEFT JOIN secondary_triage_2 st2 ON p.No = st2.patient_no
        LEFT JOIN secondary_triage_3 st3 ON p.No = st3.patient_no
        WHERE p.name2 IS NOT NULL
        ORDER BY
            p.id DESC;
        z+Error fetching patient list for transport: rV   rX   Database error: r9   
r    r[   r\   fetchallrs   rt   rY   rv   r   rw   rx   r[   r   resultsr   r   r   r   get_patient_list_for_transport  s"   


r@  c                   @   r'  r(  r1  r   r   r   r   r)    r6  z/sos/api/incidentsc               
      r8  )	NTr9  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
                                    FROM
                                        list l
                                    LEFT JOIN primary_triage pt ON l.patient_no = pt.patient_no
                                    LEFT JOIN secondary_triage_1 st1 ON l.patient_no = st1.patient_no
                                    LEFT JOIN secondary_triage_2 st2 ON l.patient_no = st2.patient_no
                                    LEFT JOIN secondary_triage_3 st3 ON l.patient_no = st3.patient_no
                                    ORDER BY
                                        l.id DESC;        z0Error fetching incident list from 'list' table: rV   rX   r;  r9   r<  r>  r   r   r   get_incident_list  s"   


rA  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_outputr  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runprintstdoutr_   rd   re   basenamerg   FileNotFoundErrorCalledProcessError
returncodestderrrt   )
excel_path
output_dircommandresultpdf_filenamer   r   r   r   convert_excel_to_pdf  s4   	rU  prefixr   c                 C   s*   |  d| d}t jt|}t j|S )uz   
    /home/air/sos/templates/ 폴더 안에
    prefix_patient_no.pdf (예: reportA_3.pdf) 가 존재하는지 확인
    _rF  )r_   rd   rg   TEMPLATE_DIRexists)rV  r   rf   rd   r   r   r   report_exists+  s   rZ  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)rZ  )r   r   r   r   get_report_status6  s   rc  z%/sos/api/generate_report/{patient_no}c              
      st  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 zCt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   dҡra d }tj	|rYzt|}|jdkr.|jd|j  |_d|_|dա W n+ tyX } zt !d| d|  d| W Y d }~nd }~ww dd|   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 )NTr9    
            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
          /Report data not found for the given patient_no.r9   rX   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: )    ERROR: rt   rY   rv   typecellvaluer   sheetr   r   write_to_cell  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K3rB   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AT16r   K20r   K21r   AO21r   AO35r   K36AO36K37AO37)r   r   r   
pi_addressF14r   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)r
  report_severityr  r  r  	memo_textA45memo_imgr      A47zCould not embed image z in Excel: zImage not found at: c                    sD     | d ur  |d ur | |   d |   d S d S d S )Nr  r   )l_fieldr_fieldrr  )r  rv  r   r   combine_and_write1  s    z*generate_report.<locals>.combine_and_writer  r  U18r  r  Z18r  r  AE18r  r  AJ18rQ   z3Report generated and converted to PDF successfully.rS   rT   rP  pdf_pathTemplate file not found at )File processing or PDF conversion error: )%r    r[   r\   r]   r   rt   rw   rs   r_   rd   rg   rX  openpyxlload_workbookactive	enumerateint
ValueError	TypeErrorr   r   strptimer+   r,   rp   itemsrq   rY  r   widthheight	add_imagerY   r^   saverU  rL  )r   rx   r[   r   r   template_pathoutput_filenameexcel_output_pathworkbooktime_fields
time_cellsir  rs  date_objr   	day_indexpatient_info_maprr  contact_time_mapbefore_arrival_map
report_mapry   r5   rI  rO  r   )r  ru  rv  r   generate_reportD  s  P



			


 	 

rl  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: Tr9  z7SELECT address FROM call_received WHERE patient_no = %srQ   r)   )rS   r)   re  z+Address not found for the given patient_no.r9   z&Error fetching address for patient_no rU   rV   rX   rY   rZ   r    r[   r\   r]   rs   r   rt   rv   rw   )r   rx   r[   r   rS  r   r   r   r   get_addressG  s(   

rn  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 )NTr9  rd  re  rf  r9   rX   r;  z20251112_B.xlsxreportB_rh  c              
      ri  )Nz2---!!! CRITICAL ERROR WRITING TO CELL (Report B): rj  rk  rl  rm  rn  ro  rq  rt  r   r   rv       z(generate_report_b.<locals>.write_to_cellr   r   rw  :r   r   B8r   r   rx  r   r   ry  r   r   J8r   r   M8r   r   Q8r   r   T8r   r   W8r   r   AB8r   r   AF8C10r   R10r   r   r  B2D2rB   G2L2r   D3r  B6r   r   B11r   N11r  r  r  AG11r   B12r   N12r   N13r  B14r  B15r   B16r   I16r   r  r   E21r   V21r   E22r   V22r   Z37F38Z38F39Z39)r   r   r   r   r  F25r  H25r  M25r  AD25r  AG25r  A26r  C26r  H26r  N26r  r  r  AD26r  AG26r  r  r  F27r  G27r  M27r  C28r  G28r  M28r  Q28r  Y28r  AG28r  D29r  G29r  M29r  U29r  AC29r  AG29r  D30r  G30r  O30r  V30r  B32r  N32AB32AD32AH32U33C34r  C36F37)r  r   r  r  r  r  r  r   r  r	  U25r  r  F42r  H42r  r  r$  AD42r&  AG42r  A43r  C43r  H43r  N43r"  U43r(  AD43r*  AG43r  E44r  F44r  G44r   M44r,  C45G45M45Q45Y45AG45)r3  r5  r4  r6  r7  r8  r9  r  L15AD15AA17r=  H18r
  r@  r	  r  r  rQ   z5Report B generated and converted to PDF successfully.rN  rP  rQ  r    r[   r\   r]   r   rt   rw   rs   r_   rd   rg   rX  rR  rS  rT  r   r   rY  r+   r,   rp   rW  rX  rZ  r^  rU  rL  )r   rx   r[   r   r  r   r_  r`  ra  rb  rv  rf  r   rg  pi_mapr  rr  ct_mapba_maprk  rO  r   rt  r   generate_report_bZ  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 )_NTr9  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
        re  rf  r9   rX   r;  z20251112_C.xlsxreportC_rh  c              
      ri  )Nz2---!!! CRITICAL ERROR WRITING TO CELL (Report C): rj  rk  rl  rm  rn  ro  rq  rt  r   r   rv    rp  z(generate_report_c.<locals>.write_to_cellr   r   rw  rq  r   r   rr  r   r   rx  r   r   ry  r   r   rs  r   r   rt  r   r   ru  r   r   rv  r   r   rw  r   r   rx  r   r   ry  r   r   r  r   r   AH9rz  r   r{  r   r   r  r|  r}  rB   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   r  r  r  r=  r  r  rQ   z5Report C generated and converted to PDF successfully.rN  rP  rQ  r  )r   rx   r[   r   r  r   r_  r`  ra  rb  rv  rf  r   rg  r  r  rr  rk  rO  r   rt  r   generate_report_c2  s   P


88888<<<<<<<<


r  z*/sos/api/generate_triage_list/{patient_no}c              
      s  t  }|jdd}z\z;d}|| | }|s!tdddtjtd}tj	|s4tdddd	|  d
}tjt|}t
|}|j}	|d }
|
dpSd|	d< |
dp\d|	d< |
dped|	d< |
dpnd|	d< |
dpwd|	d< dd }t|D ]\}}d| }|d |	d| < |d |	d| < ||d|	d| < ||d |	d!| < ||d"|	d#| < ||d$|	d%| < |d& pd|	d'| < |d( pd|	d)| < |d*pd|	d+| < |d,pd|	d-| < |d.pd|	d/| < |d0pd|	d1| < |dpd|	d2| < |dp(d|	d3| < q|| t|t}d4||d5W W |  |  S  tyg } ztjd6| dd7 td8t|dd }~ww |  |  w )9NTr9  a  
            SELECT
                p.No,
                p.age,
                p.gender,
                p.name2 AS name_kanji,
                p.address,
                pi.chief_complaint,
                p.receiving_hospital_name,
                p.transport_agency_name,
                t.depart_scene_h,
                t.depart_scene_m,
                p.month,
                p.day,
                p.am_pm,
                p.time_h,
                p.time_m,
                pt.urgency_level AS primary_urgency,
                st1.urgency_level AS secondary1_urgency,
                st2.urgency_level AS secondary2_urgency,
                st3.urgency_level AS secondary3_urgency
            FROM patient p
            LEFT JOIN patient_info pi ON p.No = pi.patient_no
            LEFT JOIN primary_triage pt ON p.No = pt.patient_no
            LEFT JOIN secondary_triage_1 st1 ON p.No = st1.patient_no
            LEFT JOIN secondary_triage_2 st2 ON p.No = st2.patient_no
            LEFT JOIN secondary_triage_3 st3 ON p.No = st3.patient_no
            LEFT JOIN time t ON p.No = t.patient_no
            WHERE p.No IS NOT NULL
            ORDER BY CAST(p.No AS UNSIGNED) ASC
        re  u'   登録された傷病者がいませんr9   z20251112_toriazi.xlsxuH   テンプレート ファイルがありません: 20251112_toriazi.xlsxreport_triage_rh  r   r+   r   I1r,   K1r-   M1r.   N1r/   P1c                 S   s   ddddd}| | dS )Nu   赤u   黄u   緑u   黒)RYGr`  r   rF  )r   mappingr   r   r   get_triage_text'  s   z-generate_triage_list.<locals>.get_triage_text   r>   r_  r$   r`  r-  ra  secondary1_urgencyDsecondary2_urgencyEsecondary3_urgencyFr'   r  r(   H
name_kanjiIr)   Jr3   Sr2   TWr  rQ   )rS   rP  rO  u)   トリアージリスト作成エラー: rV   rX   )r    r[   r\   r=  r   r_   rd   rg   rX  rY  rR  rS  rT  r   rU  r^  rU  rs   rt   rY   rv   rw   )r   rx   r[   r   patientsr_  excel_filenamera  r?   wsfirstr  idxprowrO  r   r   r   r   generate_triage_list  sl   






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: Tr9  z
            SELECT 
                patient_contact_h, 
                patient_contact_m, 
                arrival_hospital_h, 
                arrival_hospital_m 
            FROM time 
            WHERE patient_no = %s
        re  z-Time data not found for the given patient_no.r9   z(Error fetching time data for patient_no rU   rV   rX   rm  )r   rx   r[   r   	time_datar   r   r   r   r   	get_timesa  s.   		

r  )Ffastapir   r   r   r   r   pydanticr   typingr   r	   r
   mysql.connectorr   r_   rb   ri   rR  openpyxl.drawing.imager   rl   r   fastapi.staticfilesr   rY   logging.handlersr   basicConfigINFOappenvironr   r   r   r   r   ra   rX  r    mountpostrw   r   r   r   r   r   r  r  r  r  dictr  r#  r%  r&  r)  r@  rA  rG  rU  boolrZ  rc  rl  rn  r  r  r  r  r   r   r   r   <module>   sl   		
c	
|	
D0	
D	
 !"#$%&'()*+,-k#	
M	
4	
:	
:	
:
%

#   X 5y