o
    N+gi?1                 ]   @   sj  d dl mZ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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 d d	lmZ d d
lm Z m!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*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Z0ddddddZ1dZ2de2 d Z3de2 d Z4dd  Z5e)j6d!ee4d"d#d$ e)j6e2d% ee3d"d&d$ G d'd( d(eZ7e)j,d)e d*d+d, Z8G d-d. d.eZ9G d/d0 d0eZ:G d1d2 d2eZ;G d3d4 d4eZ<e)=e2d5 d6e<fd7d8Z>e)j,e2d9 ee? d:d;d< Z@e),e2d= d>e?fd?d@ZAe)=e2dA d6e;fdBdCZBe)=e2dD d6e;fdEdFZCe)=e2dG d6e7fdHdIZDG dJdK dKeZEe)=e2dL d6eEfdMdNZFe)=e2dO d6e9fdPdQZGdRe?fdSdTZHe)=e2dU edV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dWe?dXe
e? dYe
e? dZe
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? f&djdkZIe)=e2dl edV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dRe?dme
e? dne
e? doe
e? dpe
e? dqe
e? 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? f:ddZJe)=e2d edV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dRe?dme
e? dne
e? doe
e? dpe
e? dqe
e? 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? f:ddZKe)=e2d edVededededededfdRe?de
e? de
e? de
e? de
e? d\e
e? de
e? fddZLe)=e2d edV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dRe?de
e? de
e? de
e? de
e? de
e? dZe
e? d[e
e? de
e? d\e
e? d]e
e? de
e? de
e? de
e? die
e? de
e? de
e? de
e? de
e? de
e? de
e? de
e? de
e? de
e? f0ddZMe)=e2d edV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-dRe?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? 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لZNe)=e2d edVedededfdRe?de
e? dgee dhee? fdd݄ZOe)=e2d edV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dRe?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? f2ddZPe)=e2d edV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dRe?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ZQe)=e2d edVedVedfdRe?die?de
e? fddZRe)=e2d edV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dRe?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? die
e? dgee dhee? de
e? de
eS f(ddZTe)=e2d  edV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dRe?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? die
e? dgee dhee? de
e? de
eS f(ddZUe)=e2d edV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dRe?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? die
e? dgee dhee? de
e? f&ddZVG dd deZWe)j,e2d eeW d:dtd
e?fddZXG dd deZWe)j,e2d eeW d:dtd
e?fddZYe),e2d dd ZZd dl[Z[dd Z\de?dRe?de]fddZ^e),e2d dRe?fddZ_e),e2d dRe?fddZ`e),e2d  dRe?fd!d"Zae),e2d# dRe?fd$d%Zbe),e2d& dRe?fd'd(Zce),e2d) dRe?fd*d+Zde),e2d, dRe?fd-d.Zee)=e2d/ edVdWd0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dWe?dXe
e? dYe
e? dZe
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? die
e? dgee f$d1d2Zfe)=e2d3 edVfdRe?fd4d5Zgde?dRe?de]fd6dZ^e),e2d7 d8d9 Zhe),e2d: dRe?fd;d<Zie),e2d= dRe?fd>d?Zje),e2d@ dRe?fdAdBZke),e2dC dRe?fdDdEZle),e2dF dRe?fdGdHZme),e2dI dRe?fdJdKZne),e2dL dRe?fdMdNZoe),e2dO dRe?fdPdQZpe),e2dR dRe?fdSdTZqdRe?dUe?fdVdWZre),e2dX dRe?fdYdZZse),e2d[ dRe?fd\d]Zte),e2d^ dRe?fd_d`Zue),e2da dtd
e?fdbdcZve),e2dd dRe?fdedfZwe)=e2dg d6e:fdhdiZxe)=e2dj d6e:fdkdlZye)=e2dm edVfdRe?fdndoZze),e2dp edfdqe
e? fdrdsZ{dS (u      )FastAPIHTTPExceptionFile
UploadFileFormQuery)	BaseModel)OptionalDictListN)Image)Font	Alignment)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-testerDB_PASSWORDzPassWord12!!DB_NAMEsos_testi  )hostuserpassworddatabaseportz	/sos_testz	/home/air	/uploads/z/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_test/back/save_all_times.pyget_db_connection=   s   r-   z/reports)	directoryreports)namez/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,   r3   S      
 r3   /)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_indexX   s   $rQ   c                   @   s   e Zd ZU eed< dS )NextNoRequestprefixNr6   r+   r+   r+   r,   rR   c   s   
 rR   c                   @   r2   )AdminLoginRequestidr$   Nr6   r+   r+   r+   r,   rT   f   r<   rT   c                   @   r2   )SelectIncidentRequestambulance_code	case_codeNr6   r+   r+   r+   r,   rV   j   r<   rV   c                   @   s&   e Zd ZU eed< eed< eed< dS )CreateIncidentRequestabbreviationrX   timeNr6   r+   r+   r+   r,   rY   o   s   
 rY   z/api/create_incidentreqc              
      s  | j  }| j }| j }t|dkr| s tdddt|dkr*| s0tdddt }| }zzVt	
d}t|d}d	}|| | d
 }	| | | | }
d}||||
f | rqtdddd}|||
|f |  dd|
dW W |  |  S  ty } z|d }~w ty } z|  tjd| dd tdddd }~ww |  |  w )N     u:   事案番号は6桁の数字で入力してください。r@      u4   時刻は4桁の数字で入力してください。
Asia/Tokyoz%Y%m%dzESELECT COUNT(*) FROM incident_logs WHERE DATE(created_at) = CURDATE()r   zPSELECT case_code FROM incident_logs WHERE incident_number = %s OR case_code = %s  u'   既に存在する事件番号です。zFINSERT INTO incident_logs (case_code, incident_number) VALUES (%s, %s)Tu%   事件番号が作成されました.)successmessagefull_incident_idzError creating incident: exc_info  u3   事案作成中にエラーが発生しました。)rZ   striprX   r[   lenisdigitr   r-   cursorpytztimezoner   nowstrftimeexecutefetchonecommitclose	Exceptionrollbackloggingerror)r\   abbrrX   time_valcnxrk   jst	today_strcount_querytoday_countrd   check_queryinsert_queryheer+   r+   r,   create_incidentu   sR   






r   z/api/available_incidents)response_modelc               
      s   t  } | jdd}z9zd}|| dd | D }|W W |  |   S  tyC } ztjd| dd tdd	d
d }~ww |  |   w )NT
dictionaryzh
            SELECT case_code
            FROM incident_logs
            ORDER BY case_code ASC
        c                 S   s   g | ]}|d  qS )rX   r+   ).0rowr+   r+   r,   
<listcomp>   s    z+get_available_incidents.<locals>.<listcomp>z$Error fetching available incidents: re   rg   uK   利用可能な事案番号の取得中にエラーが発生しました。r@   )	r-   rk   rp   fetchallrs   rt   rv   rw   r   )rz   rk   	query_allall_incidentsr   r+   r+   r,   get_available_incidents   s.   



r   z /api/active_incident/{team_code}	team_codec              
      s   t  }|jdd}zSz3d}||| f | }|r0|d d ur0d|dW W |  |  S dddW W |  |  S  ty] } ztjd	|  d
| dd tdddd }~ww |  |  w )NTr   a  
            SELECT
                id,
                team_code,
                case_code,
                status,
                created_at,
                updated_at
            FROM ambulance_incidents
            WHERE team_code = %s
              AND status = 1
        rX   )rb   active_incidentFuH   現在、この部隊に割り当てられた事案はありません。rb   rc   z#Error fetching active incident for : re   rg   uN   アクティブな事案番号の取得中にエラーが発生しました。r@   )	r-   rk   rp   rq   rs   rt   rv   rw   r   )r   rz   rk   queryr   r   r+   r+   r,   get_active_incident   s>   



r   z/api/select_incidentc              
      s   t  }| }z\z*d}||| j| jf |jdkr!tddd|  dddW W |  |  S  tyE } z|	  |d }~w t
yd } z|	  tjd	| dd
 tdddd }~ww |  |  w )Nz
            UPDATE ambulance_incidents
            SET
                case_code = %s,
                status = 1,
                updated_at = CURRENT_TIMESTAMP
            WHERE team_code = %s
              AND status = 0
        r   ra   u]   既に他の事案を処理中か、指定された部隊コードが見つかりません。r@   Tu*   事案が正常に選択されました。r   zError selecting incident: re   rg   u3   事案選択中にエラーが発生しました。)r-   rk   rp   rX   rW   rowcountr   rr   rs   ru   rt   rv   rw   )r\   rz   rk   update_queryhttp_excr   r+   r+   r,   select_incident   sL   	



r   z/api/release_incidentc              
      s   t  }| }zIz(d}||| jf |jdkrtddd|  dddW W |  |  S  tyQ } z|	  t
jd	| dd
 tdddd }~ww |  |  w )Nz
            UPDATE ambulance_incidents
            SET
                status = 0,
                case_code = NULL,
                updated_at = CURRENT_TIMESTAMP
            WHERE team_code = %s
        r   r?   u?   解放する有効な事案が見つかりませんでした。r@   Tu*   事案が正常に解放されました。r   zError releasing incident: re   rg   u3   事案解除中にエラーが発生しました。)r-   rk   rp   rW   r   r   rr   rs   rt   ru   rv   rw   )r\   rz   rk   r   r   r+   r+   r,   release_incident-  s>   


r   z
/api/loginc              
   C   sl  | j  }| j }|r|stdddddS d }zzUtjjdi t}|jdd}d}|	|||f |
 }|rYd|d	 |d
 |d dW W |rW| rX|  |  S S S dddW W |rn| ro|  |  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 )Nr^   Fu9   隊コードと呼出名称を入力してください。r   rA   rF   Tr   zfSELECT team_code, team_name, abbreviation FROM ambulance_teams WHERE team_code = %s AND call_name = %sr   	team_namerZ   )rb   r   teamNamerZ   u<   隊コードまたは呼出名称が間違っています。zDatabase Error: rg   u3   データベースエラーが発生しました。r+   )r4   rh   r5   r   r(   r)   r*   	DB_CONFIGrk   rp   rq   is_connectedrs   Errorprint)r\   
input_codeinput_call_nameconnrk   r   r#   errr+   r+   r,   loginX  sZ   







r   c                   @   s   e Zd ZU eed< eed< dS )DateNumRequestdate_strnumN)r7   r8   r9   r:   r;   intr+   r+   r+   r,   r     r<   r   z/api/generate_date_numc           	   
   C   sH  | j  }| j}d }zzQtjjdi t}|jdd}|d }d}||||f |	  t
|d}td| d| d| d	|  d||d
W W |r[| r\|  |  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 )NTr      z:INSERT INTO `date_num` (`datetime`, `num`) VALUES (%s, %s)   zGenerated next_num: z (formatted: z) for date: z using provided_num: )rb   r   next_numzDB Error in generate_date_num: rg   FDB Errorr   r   r+   )r   rh   r   r(   r)   r*   r   rk   rp   rr   r:   zfillrv   infor   rs   r   rw   r   )	r\   target_dateprovided_numr   rk   r   r   formatted_numr   r+   r+   r,   generate_date_num  sB   
"


r   z/api/get_next_noc              
   C   s^  | j }d }zzbtjjdi t}|jdd}d}||| df | }d}t|}|D ]}|d }	|	|d  }
|
	 rHt
|
}||krH|}q.|d }dt|dd	W W |rg| rh|  |  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 )NTr   z'SELECT No FROM patient WHERE No LIKE %s%r   Nor   r   )rb   next_no
DB Error: rg   Fr   r   r   r+   )rS   r(   r)   r*   r   rk   rp   r   ri   rj   r   r:   r   r   rs   r   r   r   )r\   rS   r   rk   r   rowsmax_suffix_num
prefix_lenr   pidsuffix
current_nor   r   r+   r+   r,   get_next_no  sJ   


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: r`   %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))r[   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, '未入力'))
rp   rq   rv   r   rl   rm   r   rn   ro   weekday)	rk   r   r{   now_jstdate_time_strweekdaysday_of_weekplaceholder_tablestabler+   r+   r,   ensure_patient_record_exists  s   


r   z/api/save_patient.r   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@zg }|r"|dd |D  |r6|d| f | }g }|rn|d rn|d d}|D ],}| }|sJqAt	j
|}t	j
t|}t	j
|rmt	j
|}||||d d qAt	jtdd	 |D ]}|js}qw|dI d H  | I d H }t|}d
} d}!|D ]F}"|"d |krq|"d d u rt|"d d}#t|#  |"d< W d    n1 sw   Y  t| }$|"d |$krd} |"d }! nq| r|!|vr||! qwt  t	j
|jd  }%t	j
t|%}&t|&d}'|'| W d    n	1 sw   Y  t d|% }(||( ||&|(|t| d qw|r>d|nd })t d}*t!"|*}+|+#d},g d}-|-|+$  }.|rc| rc|nd}/|d| f | }0|0st d|   d}1| ||/|||)|||||	|
||||||,|.f}2||1|2 g d}3|3D ]}4|d|4 d| f q|d| |f |d| |,|.|/f n6t d |   d!}5||/|||||||	|
||||||)| f}6||5|6 |d"|/| f |r|d#|| f |d urd$}7||7|| f d%}8||8||/||| f |%  d&d'|  d(d)W W |&  |&  S  t'yQ }9 z|(  t j)d*|  d+|9 dd, t*d-d.t+|9 d/d }9~9ww |&  |&  w )0Nz+Attempting to save/update patient with No: c                 S      g | ]
}|  r|  qS r+   rh   r   rH   r+   r+   r,   r   '      z save_patient.<locals>.<listcomp>%SELECT img FROM patient WHERE No = %sr   ,rH   web_pathsizehashTexist_okF r   r   rH   rbr   r   wbr'   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
            UPDATE patient_info 
            SET patient_name1 = %s, patient_name2 = %s, gender = %s, phone_number = %s
            WHERE patient_no = %s
        rb   z$Patient data saved successfully (No=)statusrc   z!Error saving patient data for No r   re   rg   Database error: r@   ),rv   r   r-   rk   extendrp   rq   splitrh   rG   rH   basenamerI   
UPLOAD_DIRrL   getsizeappendmakedirsfilenameseekrN   ri   rM   hashlibmd5	hexdigestuuiduuid4splitextwritePREFIXrl   rm   r   rn   ro   r   rr   rs   rt   ru   rw   r   r:   ):r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   rz   rk   final_pathsr   candidate_filesdb_img_pathsr   r  file_sys_path	file_sizeimgrF   incoming_sizeis_duplicatematched_web_path	candidaterP   incoming_hashunique_filenamefull_save_pathbufferweb_urlcombined_img_stringr{   r   r   r   r   effective_name2record_existsr   insert_paramsr   r   r   update_paramssync_cr_querysync_pi_queryr   r+   r+   r,   save_patient  s   












r(  z/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 )Nr   r+   )rE  r+   r+   r,   to_db  s   z(initiate_record_from_time.<locals>.to_dbr   z8Attempted to create a record with duplicate patient_no: ra   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.rb   zITime-based record initiated with detailed times and placeholders created.r   z2Error in initiate_record_from_time for patient_no r   Tre   rg   )rv   r   r	   r:   r-   rk   rp   rq   warningr   rl   rm   r   rn   ro   r   rr   rs   rt   ru   rw   )0r   r)  r*  r+  r,  r-  r.  r/  r0  r1  r2  r3  r4  r5  r6  r7  r8  r9  r:  r;  r<  r=  r>  r?  r@  rA  rB  rC  rD  rG  rz   rk   r{   r   r   r   r   insert_patient_querypatient_valuesinsert_list_querylist_valuestime_insert_querytime_valuesr   r   insert_pi_queryr   r   r+   r+   r,   initiate_record_from_time  sp    







rP  z/api/save_all_timesc           )         s  t d|   t }| }zzd}|||||||||	|
||||||||||||||||||| f} |||  |d urT|d urTd}!||!||| f t d|   |rZ|rZdnd}"|rb|rbdnd}#|rj|rjdnd}$|rr|rrd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: r   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: rb   zTime data updated.r   z(Error updating time data for patient_no r   Tre   rg   r@   rv   r   r-   rk   rp   rr   rs   rt   ru   rw   r   r:   ))r   r)  r*  r+  r,  r-  r.  r/  r0  r1  r2  r3  r4  r5  r6  r7  r8  r9  r:  r;  r<  r=  r>  r?  r@  rA  rB  rC  rD  rz   rk   r   valuessync_contact_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_timesc  sT    

rZ  z/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
        rb   zCall received data updated.r   z1Error updating call_received data for patient_no r   Tre   rg   r@   rQ  )r   r[  r\  r]  r^  r   r_  placeholderrz   rk   r   rR  r   r+   r+   r,   save_call_received  s@   


rb  z/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: rb   zPatient info data updated.r   z0Error updating patient_info data for patient_no r   Tre   rg   r@   )rv   r   r-   rk   rh   rp   rr   rs   rt   ru   rw   r   r:   )r   rc  rd  re  rf  rg  r   r   rh  r   r   ri  rj  rk  r   rl  rm  rn  ro  rp  rq  rr  rs  rt  rz   rk   effective_patient_name2r   rR  update_list_name2_queryr   r+   r+   r,   save_patient_info  s<   



rw  z/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`  )rz  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
        rz  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  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: rb   zContact time data updated.r   z2Error updating contact_time_T data for patient_no r   Tre   rg   r@   rv   r   localsgetr-   rk   rp   rr   rs   rt   ru   rw   r   r:   )7r   rx  ry  rz  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  ra  
local_varsfields_to_checkfieldrz   rk   r   rR  sync_time_queryr   r+   r+   r,   save_contact_time?  s   0					





r  z/api/save_memotextc              
      s   t d|   t }| }zezJg }|r"|dd |D  |r6|d| f | }g }|rn|d rn|d d}	|	D ],}
|
 }
|
sJqAt	j
|
}t	j
t|}t	j
|rmt	j
|}|||
|d d qAt	jtdd	 |D ]}|js}qw|dI d H  | I d H }t|}d
}d}|D ]F}|d |krq|d d u rt|d d}t|  |d< W d    n1 sw   Y  t| }|d |krd}|d } nq|r||vr|| qwt  t	j
|jd  }t	j
t|}t|d}|| W d    n	1 sw   Y  t d| }|| ||||t| d qw|r>d|nd }d}||||| f |  dddW W |   |   S  t!yv } 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>z*SELECT img FROM memo WHERE patient_no = %sr   r   r   Tr   Fr   r   r   rH   r   r   r   r   r'   z9UPDATE memo SET text = %s, img = %s WHERE patient_no = %srb   z!Memo updated with multiple imagesr   rg   r@   )%rv   r   r-   rk   r  rp   rq   r  rh   rG   rH   r  rI   r  rL   r  r  r  r  r	  rN   ri   rM   r
  r  r  r  r  r  r  r  rr   rs   rt   ru   r   r:   )r   r  r   r   rz   rk   r  r   r  r  r   r  r  r  r  rF   r  r  r  r  rP   r  r  	full_pathr  r   r!  r   r   r+   r+   r,   	save_memo  s   




r  z/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`  )rz  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
        rz  r  r  r  r  r  r  rb   zBefore arrival data updated.r   z2Error updating before_arrival data for patient_no r   Tre   rg   r@   r  )"r   rx  ry  rz  r{  r|  r}  r~  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  ra  r  r  r  rz   rk   r   rR  r   r+   r+   r,   save_before_arrival#  sB   


r  z/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
        rb   zReport data updated.r   z*Error updating report data for patient_no r   Tre   rg   r@   rQ  )r   r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  rz   rk   r   rR  r   r+   r+   r,   save_reportq  s4   

r  z/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 = %srb   zPrimary triage data saved.r   rg   r   r@   )rv   r   r-   rk   r   rp   rr   rs   rt   ru   r   r:   )r   r   r  rz   rk   r   r   r+   r+   r,   save_primary_triage  s&   


r  z/api/save_secondary_triage_1practitioner_name1practitioner_name2treatment_timerespirationtreatment_details
save_statec           -           t  }| }z_g }|r|dd |D  |r,|d| f | }g }|rd|d rd|d d}|D ],}| }|s@q7tj	|}tj
t|}tj|rctj|}||||d d q7tjtdd |D ]}|jssqm|dI d H  | I d H }t|} d	}!d
}"|D ]F}#|#d | krq|#d d u rt|#d d}$t|$  |#d< W d    n1 sw   Y  t| }%|#d |%krd}!|#d }" nq|!r|"|vr||" qmt  tj|jd  }&tj
t|&}'t|'d}(|(| W d    n	1 sw   Y  t d|& })||) ||'|)| t| d qm|r4d
|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_1.<locals>.<listcomp>z8SELECT img FROM secondary_triage_1 WHERE patient_no = %sr   r   r   Tr   Fr   r   r   rH   r   r   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   rb    r-   rk   r  rp   rq   r  rh   rG   rH   r  rI   r  rL   r  r  r  r  r	  rN   ri   rM   r
  r  r  r  r  r  r  r  r   rr   rs   -r   r  r  r  r  r  r  r  r  r  rz  r{  r|  r}  r  r   r   r   r  r  rz   rk   r  r   r  r  r   r  r  r  r  rF   r  r  r  r  rP   r  u_namer  r  new_web_pathr!  r   valsr+   r+   r,   save_secondary_triage_1  s   



	


r  z/api/save_secondary_triage_2c           -         r  )Nc                 S   r   r+   r   r   r+   r+   r,   r   k  r   z+save_secondary_triage_2.<locals>.<listcomp>z8SELECT img FROM secondary_triage_2 WHERE patient_no = %sr   r   r   Tr   Fr   r   r   rH   r   r   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   rb   r  r  r+   r+   r,   save_secondary_triage_2M  s   



	


r  z/api/save_secondary_triage_3c           ,         s  t  }| }z^g }|r|dd |D  |r,|d| f | }g }|rd|d rd|d d}|D ],}| }|s@q7tj	|}tj
t|}tj|rctj|}||||d d q7tjtdd |D ]}|jssqm|dI d H  | I d H }t|}d	} d
}!|D ]F}"|"d |krq|"d d u rt|"d d}#t|#  |"d< W d    n1 sw   Y  t| }$|"d |$krd} |"d }! nq| r|!|vr||! qmt  tj|jd  }%tj
t|%}&t|&d}'|'| W d    n	1 sw   Y  t d|% }(||( ||&|(|t| d qm|r4d
|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>z8SELECT img FROM secondary_triage_3 WHERE patient_no = %sr   r   r   Tr   Fr   r   r   rH   r   r   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   rb   r  ),r   r  r  r  r  r  r  r  r  r  rz  r{  r|  r}  r  r   r   r   r  rz   rk   r  r   r  r  r   r  r  r  r  rF   r  r  r  r  rP   r  r  r  r  r  r!  r   r  r+   r+   r,   save_secondary_triage_3  s   



	



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r7   r8   r9   r:   r;   r  r	   r   r   r  r  r  r  r  r  r   r+   r+   r+   r,   r  Q     
 r  z/api/transport_patientsr   rZ   c              
         t  }|jdd}zUz3d}g }| rd}|d|  d d| d}|||r,t|nd	 | }|W W |  |  S  ty_ } ztj	d
| dd t
ddt| dd }~ww |  |  w )NTr   r   zAND p.No REGEXP %s^[0-9]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
          uV     -- [추가] 필터링 조건 삽입
        ORDER BY
            p.id DESC;
        r+   z+Error fetching patient list for transport: re   rg   r   r@   r-   rk   r  rp   tupler   rs   rt   rv   rw   r   r:   )rZ   rz   rk   extra_conditionparamsr   resultsr   r+   r+   r,   get_patient_list_for_transportb  s0   

r  c                   @   r  r  r  r+   r+   r+   r,   r    r  z/api/incidentsc              
      r  )NTr   r   WHERE l.patient_no REGEXP %sr  r  ao  
            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
            
            uc    -- [추가] WHERE 절 삽입
            
            ORDER BY
                l.id DESC;
        r+   z0Error fetching incident list from 'list' table: re   rg   r   r@   r  )rZ   rz   rk   where_clauser  r   r  r   r+   r+   r,   get_incident_list  s0   

r  z/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.Tr   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: re   rg   r   r@   N)rv   r   r-   rk   rp   r   rs   rt   rw   r   r:   )rz   rk   r   r  r   r+   r+   r,   get_transfer_status_list  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_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runr   stdoutrG   rH   r  r  rI   FileNotFoundErrorCalledProcessError
returncodestderrrt   )
excel_path
output_dircommandresultpdf_filenamer   r+   r+   r,   convert_excel_to_pdf  s4   	r  rS   rF  c                 C   s*   |  d| d}t jt|}t j|S )u   
    /home/air/sos_test/templates/ 폴더 안에
    prefix_patient_no.pdf (예: reportA_3.pdf) 가 존재하는지 확인
    _r  rG   rH   rI   TEMPLATE_DIRrL   )rS   r   r  rH   r+   r+   r,   report_exists   s   r   z/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!/api/generate_report/{patient_no}c           :         s	  t  }|jdd}zYz7d}||| f |  td|   tdt fdddD   td	t   sAtd
ddW n t	yY } ztddt| dd }~ww W |
  |
  n	|
  |
  w zftjtd}d|  d}tjt|}t|}d}	|	|jv r||	 ntd|	 d |jfdd}
 drz' d }t|trt|d}|j d< |j d< g d}||   d< W n t	y } ztd|  W Y d }~nd }~ww ddd d!d"d#}| D ]\}} |d ur|
| |  qd$d%d&d'}| D ]\}} |d ur*|
| |  qi d(d)d*d+d,d-d.d/d0d1d2d3d4d5d6d7d8d9d:d;d<d=d>d?d@dAdBdCdDdEdFdGdHdIdJdKdLdM}| D ]\}} |}|d urzt|}W n   Y |
|| qki dNdOdPdQdRdSdTdUdVdWdXdYdZd[d\d]d^d_d`dadbdcdddedfdgdhdidjdkdldmdndodpdqdrds}| D ]\}} |d ur|
| |  qg dt}|D ]$\}}} |} |}|d ur|d ur|
|| du|  qi dvdwdxdydzd{d|d}d~dddddddddddddddddddddddddi ddddddddddddddddddddddddddddddddddi ddddddddddÓddœddǓddɓdd˓dd͓ddϓddѓddӓddՓddדddٓddۓ}| D ]\}} |d ur|
| |  q dܡd ur dݡd ur d  du d  }|
d| i dddddddddddddddddddddddddddddddddd ddddddd}| D ]\}} |d ur?|
| |  q, dd urf d	d urf d  du d	  }|
d
|  d}|rÈd j}|stdddd _nt|j d|j!d|j"|j#dd _d}t$|| d } |%dd }!t&| |!}"|"dkr|"d j'd _(g d}#g }$tdt  tdt fdd|#D   |#D ]0}% |%r |% )dD ]}&tj*|&+ }'tjt|'}(tj,|(r|$-|( qqd})g d}*d}+d},t$|$}-t$|*}.|-|. d |. }/|/dkr@d}0nd}0t.|$D ]t\}1}&zO|1t$|* }2|1t$|* }3|)|3|+  }4|4d }5|*|2  |5 }6|,j'|4 _(|0j'|5 _(t/|&}7|7j0dkr|7j(|7j0 }8d |7_0d |8 |7_(1|7|6 W qG t	y } ztd!|& d"|  W Y d }~qGd }~ww d#_2|3| t4|t}9d$d%||9d&W S  t5y   td
d'| d t	y } ztdd(t| dd }~ww ()  NTr   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,
                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
        z[REPORT] patient_no=z[REPORT] image_fields_raw=c                       i | ]}|  |qS r+   r  )r   kdatar+   r,   
<dictcomp>      z#generate_report.<locals>.<dictcomp>r  st1_imgst2_imgst3_imgmemo_imgz[REPORT] UPLOAD_DIR=r?   /Report data not found for the given patient_no.r@   rg   r   z20251226_A.xlsxreportA_.xlsxu   消防機関用'C   ' 시트를 찾을 수 없어 첫 번째 시트를 사용합니다.c              
      ~   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: rt   rv   rw   typecellvaluer   sheetr+   r,   write_to_cell  s   z&generate_report.<locals>.write_to_cellr[  %Y-%m-%d
calc_monthcalc_dayr   calc_weekdayu   날짜 변환 오류: J5S5AC5AQ5CI5)r*  r+  r,  r\  r]  J11Z11J20)r^  
cr_addressr_  r)  A27r*  F27r+  J27r,  O27r-  R27r.  W27r/  Z27r0  AE27r1  AH27r2  AM27r3  AP27r4  AU27r5  AX27r6  BC27r7  BF27r8  BK27r9  BN27BS27BV27CA27)r:  r;  r<  rC  J31rD  BA31rc  J35rd  J38re  AR35rf  BD35rg  BL35pi_ageBV35	pi_genderCE35
pi_addressJ44pi_phone_numberJ48r  AP48report_severityBZ48ri  J52rj  AH52rk  AW52pi_urgency_levelBZ52A58BV56BV60)r  r  r  ))r  r  AE58)r  r  AO58)r  r  AY58)r  r  BI58.rl  P65rm  BM65rn  P69ro  BM69ct_contact_time_hA79ct_contact_time_mI79ct_consciousness_jcsU79ct_consciousness_eR83ct_consciousness_vW83ct_consciousness_mAB83ct_respiration_rateAE79ct_respiration_conditionAE83
ct_pulse_1AT79ct_pulse_rateAT81
ct_pulse_2AT83ct_temperature_textBI83ct_bp_right_1CF79ct_bp_right_2CM79ct_bp_left_1CF82ct_bp_left_2CM82ct_spo2_leftI85ct_spo2_rightW85ct_oxygen_useAH85ct_oxygen_flow_rateAT85ct_ecg_statusBL85ct_auscultationCG85ct_pupil_right_sizeJ90ct_pupil_right_reactionV90ct_gaze_deviationAO90ct_palpebral_conjunctivaBF90ct_pupil_left_sizeJ93ct_pupil_left_reactionV93ct_visual_impairmentAO93ct_nystagmusBF93ct_convulsionBW90ct_affected_area_conditionCG90ct_skin_conditionF96ct_paralysisAF96ct_paralysis_areaAU96ct_vomitBW96ct_vomit_countCC98ct_diarrheaCN96ct_first_aidI101ct_first_aid_otherAM101ct_transport_positionI107ct_adlP110rp  BN110rq  P114rr  BN114rs  P118rt  BN118ct_temperature_Lct_temperature_RBI79ba_contact_time_hA125ba_contact_time_mI125ba_consciousness_jcsU125ba_consciousness_eR129ba_consciousness_vW129ba_consciousness_mAB129ba_respiration_rateAE125ba_respiration_conditionAE129
ba_pulse_1AT125ba_pulse_rateAT127
ba_pulse_2AT129ba_temperature_textBI129ba_bp_right_1CF125ba_bp_right_2CM125ba_bp_left_1CF128ba_bp_left_2CM128ba_spo2_leftI131W131AH131AT131BL131CG131A136ba_spo2_rightba_oxygen_useba_oxygen_flow_rateba_ecg_statusba_auscultation	memo_textba_temperature_Lba_temperature_RBI125r  top	wrap_textvertical
horizontalr  text_rotationr  shrink_to_fitindent2   r   
      z[IMG] UPLOAD_DIR=z[IMG] raw_fields=c                    r  r+   r  )r   rP   r  r+   r,   r  	  r  r      r  TAOBHCBr   
      n   F   r         이미지 삽입 실패 (): A1:CV400rb   z3Report generated and converted to PDF successfully.r   rc   r  pdf_pathTemplate file not found at )File processing or PDF conversion error: )6r-   rk   rp   rq   rv   r   r:   r  r   rt   rs   rG   rH   rI   r  openpyxlload_workbook
sheetnamesrH  activer  
isinstancer   strptimer   r   r   rw   itemsr   	alignmentr   r   r  r  r  ri   countmaxrow_dimensionsheightr  r  rh   rL   r  	enumerater   width	add_image
print_areasaver  r  ):r   rz   rk   r   r   template_pathoutput_filenameexcel_output_pathworkbooktarget_sheet_namer(  cr_dater   PAGE1_HEADER_MAPkey	cell_addrPAGE1_LOCATION_MAPPAGE1_TIME_MAPrE  PAGE1_INFO_MAPdistance_pairsl_keyr_keyl_valr_valPAGE1_VITALS_MAPcombined_tempPAGE2_BEFORE_ARRIVAL_MAPcombined_temp_bar  currchars_per_linelines_by_lenlines_by_newline	est_linesimage_fields	all_pathsr  pfnamefpath	start_rowcolsrow_height_incrementspacer_heighttotal_imagesimages_per_rownum_image_rowsimage_heighticol_idx	row_group
spacer_row	image_rowtarget_cellimg_objratior  r+   )r  r'  r,   generate_report9  s
  P








			


	


	


  !!"##$%()*+,/ 
		


$
$




rZ  z/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: Tr   z7SELECT address FROM call_received WHERE patient_no = %srb   r   )r   r   r?   z+Address not found for the given patient_no.r@   z&Error fetching address for patient_no r   re   rg   rv   r   r-   rk   rp   rq   rs   r   rt   rw   r:   )r   rz   rk   r   r  r   r+   r+   r,   get_address
  s(   

r\  z#/api/generate_report_b/{patient_no}c           6         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 zd	}tj	
t|}d
|  d}tj	
t|}	t|}
d}||
jv rv|
|  ntd| d |
j  fdd}|drz'|d }t|trt|d}|j|d< |j|d< g d}||  |d< W n	 ty   Y nw dddddddd}| D ]\}}||d ur||||  qi dd d!d"d#d$d%d&d'd(d)d*d+d,d-d.d/d0d1d2d3d4d5d6d7d8d9d:d;d<d=d>d?d@dAdBdCdDdEdF}| D ]\}}||}|d ur>zt|}W n   Y ||| q i dGdHdIdJdKdLdMdNdOdPdQdRdSdTdUdVdWdXdYdZd[d\d]d^d_d`dadbdcdddedfdgdhdidjdkdl}| D ]\}}||d ur||||  qi dmdndodpdqdrdsdtdudvdwdxdydzd{d|d}d~ddddddddddddddddi ddddddddddddddddddddddddddddddddddi ddddddddddddddddddddēddƓddȓddʓdd̓ddΓddГddғ}| D ]\}}||d urJ||||  q7|dӡd url|dԡd url|d  d|d  }|d| i ddؓddړddܓddޓddddddddddddddddddddddddddddddddd}| D ]\}}||d ur||||  q|d d ur|dd ur|d   d|d  }|d| |d}|rB d j}|stddd d _nt|jd|jd|j|j d d _d}t!|| d }|"d	d }t#||}|dkrB|d
  j$d _%g d}g } |D ]0}!||!rz||! &dD ]}"tj	'|"( }#tj	
t)|#}$tj	*|$rx| +|$ q[qKd}%g d}&d}'d}(t!| })t!|&}*|)|* d |* }+|+dkrd},nd},t,| D ]t\}-}"zO|-t!|& }.|-t!|& }/|%|/|'  }0|0d }1|&|.  |1 }2|( j$|0 _%|, j$|1 _%t-|"}3|3j.dkr|3j%|3j. }4d|3_.d|4 |3_% /|3|2 W q ty  } zt0d|" d|  W Y d }~qd }~ww d _1|
2|	 t3|	t}5dd|	|5dW S  t4yK   tdd| d tyc } ztddt| dd }~ww (  NTr     
            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@   rg   r   z20251226_B.xlsxreportB_r  u   引継ぎ用r  r  c              
      r  )Nz2---!!! CRITICAL ERROR WRITING TO CELL (Report B): r  r  r  r   r   r!  r#  r&  r+   r,   r(  
  s   z(generate_report_b.<locals>.write_to_cellr[  r)  r*  r+  r   r,  r-  r.  r/  r0  r2  r3  r4  r*  r+  r,  r\  r^  r5  r_  r)  r6  r*  r7  r+  r8  r,  r9  r-  r:  r.  r;  r/  r<  r0  r=  r1  r>  r2  r?  r3  r@  r4  rA  r5  rB  r6  rC  r7  rD  r8  rE  r9  rF  rG  rH  rI  CD27CI27)r:  r;  r<  r=  r>  rC  rJ  rD  rK  rc  rL  rd  rM  re  rN  rf  rO  rg  rP  rQ  rR  rS  rT  pi_occupationCJ39rU  rV  rW  rX  r  rY  rZ  r[  ri  r\  rj  r]  rk  r^  ra  AE60rb  )r  r  r  rl  ri  rm  rj  rn  rk  ro  rl  rm  rn  ro  rp  rq  rr  rs  rt  ru  rv  rw  rx  ry  rz  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  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  rp  r  rq  r  rr  r  rs  r  rt  r  r  r  rh  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  r  r  r  r  r  r   r  r  r  r  r   r  r	  r   r  r  r  r  r   r  r  r  r  rb   z5Report B generated and converted to PDF successfully.r  r  r  5r-   rk   rp   rq   r   rt   r:   rs   rG   rH   rI   r  r  r  r  rv   rH  r  r  r  r   r  r   r   r   r   r   r!  r   r   r  r  r  ri   r"  r#  r$  r%  r  r  rh   r  rL   r  r&  r   r'  r(  rw   r)  r*  r  r  )6r   rz   rk   r   r  r   template_filenamer+  r,  r-  r.  r/  r(  r0  r   REPORT_B_PAGE1_MAPr2  r3  REPORT_B_TIME_MAPrE  REPORT_B_INFO_MAPREPORT_B_VITALS_MAPr=  REPORT_B_BEFORE_ARRIVAL_MAPr?  r  r@  rA  rB  rC  rD  rE  rF  r  rG  rH  rI  rJ  rK  rL  rM  rN  rO  rP  rQ  rR  rS  rT  rU  rV  rW  rX  rY  r  r+   r&  r,   generate_report_b#
  s  P








		


	
		
 !!""#$$%&)*+,-0 
		

$







rl  z#/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 zKd	}tj	
t|}d
|  d}tj	
t|}	t|}
d}||
jv rv|
|  ntd| d |
j  fdd}|drz'|d }t|trt|d}|j|d< |j|d< g d}||  |d< W n   Y dddddddd}| D ]\}}||d ur||||  qi dd d!d"d#d$d%d&d'd(d)d*d+d,d-d.d/d0d1d2d3d4d5d6d7d8d9d:d;d<d=d>d?d@dAdBdCdDdEdFdGdHdIdJ	}| D ]\}}||}|d ur=zt|}W n   Y ||| qi dKdLdMdNdOdPdQdRdSdTdUdVdWdXdYdZd[d\d]d^d_d`dadbdcdddedfdgdhdidjdkdldmdndodpdq}| D ]\}}||d ur||||  q|dr}|r dp j}|stddsdt dp _nt|jds|jd|j|j du dp _dv}t!|| dw }|"dxdw }t#||}|dwkr|dy  j$dz _%g d{}g }|D ]/}||r|| &d|D ]}tj	'|( }tj	
t)|} tj	*| r|+|  qqd}}!g d~}"d}#t,|D ]^\}$}z;|$t!|" }%|$t!|" }&|!|&|#  }'|"|%  |' }(t-|})|)j.dkr]|)j%|)j. }*d|)_.d|* |)_% /|)|( W q& ty } zt0d| d|  W Y d }~q&d }~ww d _1|
2|	 t3|	t}+dd|	|+dW S  t4y   tdd| d ty } ztddt| dd }~ww )NTr   r]  r?   r  r@   rg   r   z20251226_C.xlsxreportC_r  u   医療機関提出用r  r  c              
      sZ   z|d ur| | < W d S W d S  t y, } ztd|  d|  W Y d }~d S d }~ww )NzError Report C (r  )rt   rv   rw   r#  r&  r+   r,   r(  .  s   $z(generate_report_c.<locals>.write_to_cellr[  r)  r*  r+  r   r,  r-  r.  r/  r0  r2  r3  r4  r_  r)  r6  r*  r7  r+  r8  r,  r9  r-  r:  r.  r;  r/  r<  r0  r=  r1  r>  r2  r?  r3  r@  r4  rA  r5  rB  r6  rC  r7  rD  r8  rE  r9  rF  rG  rH  rI  r`  ra  CL27CR27CL29CR29)	r:  r;  r<  r=  r>  r?  r@  rA  rB  rC  rJ  rD  rK  rc  rL  rd  rM  re  rN  rf  rO  rg  rP  rQ  rR  rS  rT  rb  rc  rU  rV  rW  rX  ri  r\  rj  r]  rk  r^  r  rY  rZ  r[  ra  rd  rb  A65)r  r  r  r  r  r  r  r  r  r   r  r  A   r  r   G   r	     r   r  r  r  zA1:CU200rb   z5Report C generated and converted to PDF successfully.r  r  r  re  ),r   rz   rk   r   r  r   rf  r+  r,  r-  r.  r/  r(  r0  r   REPORT_C_PAGE1_MAPr2  r3  REPORT_C_TIME_MAPrE  REPORT_C_INFO_MAPr  r@  rA  rB  rC  rD  rE  rF  r  rG  rH  rI  rJ  rK  rL  rR  rS  rT  current_rowrW  rX  rY  r  r+   r&  r,   generate_report_c  s  P








		


	
 

	





rz  z&/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 )>NTr   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 )Nr  r  r  r  )RYGr  r   r  )r   mappingr+   r+   r,   get_triage_text  s   z-generate_triage_list.<locals>.get_triage_textr_   r   r  r   r  r  r  Dsecondary1_urgencyEsecondary2_urgencyFsecondary3_urgencyr   r   r  r   H
name_kanjiIr   Jr   Sr   r
  Wr  rb   )r   r  r  u#   PDF作成中にエラーが発生: re   rg   )r-   rk   rp   r   r   rG   rH   rI   r  r  r  r  r   r  r&  r%  fontr*  r  rs   rt   rv   rw   r:   )r   rz   rk   r   patientsr+  excel_filenamer-  r   wsfont_redfont_yellow
font_green
font_blackfont_mapfirstr  idxrG  r   triage_levelscolr   r  r$  r  r   r+   r+   r,   generate_triage_list  sv   

* 




r  z/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: Tr   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   re   rg   r[  )r   rz   rk   r   	time_datar   r   r+   r+   r,   	get_timesN  s.   		

r  z/api/update_patient_data)aliasc           )         s  t d|   t }| }zQz3|d| f | }|r%|d nd }|r.|dng }|r}tjt	dd |D ]A}|j
r|t  tj|j
d  }tjt	|}t|d}t|j| W d    n1 smw   Y  |t 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 yb }( z|!  t"ddt#|( d d }(~(ww |  |  w )!NzUPSERT patient data for No: r   r   r   Tr   r   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   rb   zPatient data saved (No=r   r   rg   r   r@   )$rv   r   r-   rk   rp   rq   r  rG   r  r  r  r  r  rH   r  rI   rM   shutilcopyfileobjfiler  r  rl   rm   r   rn   ro   r   rh   r  rr   rs   rt   ru   r   r:   ))r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   rz   rk   r   existing_img_strr  r  r  r  r  r!  r{   r   r   r   r   r"  r   r   r   
img_clauser   r  r   r+   r+   r,   update_patient_data  s|   


	
"	


r  z/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.rb   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   rg   r@   )r-   rk   rp   rq   rv   r   rs   rr   rt   ru   rw   r   r:   )r   rz   rk   r   r   r   r+   r+   r,   complete_incident}  s4   


r  c                 C   s&   t jt|  d| d}t j|S )u   
    /home/air/sos_test/templates/ 폴더 안에
    prefix_patient_no.pdf (예: reportA_X.pdf) 가 존재하는지 확인
    r  r  r  )rS   r   rH   r+   r+   r,   r     s   z/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 상태 포함)
    Tr   zSELECT No FROM patientr   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: re   rg   r   r@   )r-   rk   rp   r   r  r  r:   rv   rH  r   r  rt   rw   rs   r   )
rz   rk   r   patient_nosr  rG  r   r   
file_errorr   r+   r+   r,   get_report_status_list  sL   





r  z/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: Tr   z(SELECT * FROM time WHERE patient_no = %srb   r   r  zError fetching time data: re   rg   r@   rv   r   r-   rk   rp   rq   rs   rt   rw   r   r:   r   rz   rk   r   r   r   r+   r+   r,   get_time_data_full  ,   


r  z(/api/get_call_received_data/{patient_no}c              
      r  )Nz+Fetching FULL call_received data for edit: Tr   z1SELECT * FROM call_received WHERE patient_no = %srb   r  z#Error fetching call_received data: re   rg   r@   r  r  r+   r+   r,   get_call_received_data  r  r  z'/api/get_patient_info_data/{patient_no}c              
      r  )Nz*Fetching FULL patient_info data for edit: Tr   z0SELECT * FROM patient_info WHERE patient_no = %srb   r  z"Error fetching patient_info data: re   rg   r@   r  r  r+   r+   r,   get_patient_info_data  r  r  z)/api/get_contact_time_T_data/{patient_no}c              
      r  )Nz,Fetching FULL contact_time_T data for edit: Tr   z2SELECT * FROM contact_time_T WHERE patient_no = %srb   r  z$Error fetching contact_time_T data: re   rg   r@   r  r  r+   r+   r,   get_contact_time_T_data&  r  r  z/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: Tr   z(SELECT * FROM memo WHERE patient_no = %sr  )r  r   r  r   r   server_img_)rU   urlrb   r  zError fetching memo data: re   rg   r@   )rv   r   r-   rk   rp   rq   r  r  r&  rh   r  rs   rt   rw   r   r:   )
r   rz   rk   r   r   result_datapathsrR  rH   r   r+   r+   r,   get_memo_data?  s@   



r  z)/api/get_before_arrival_data/{patient_no}c              
      r  )Nz,Fetching FULL before_arrival data for edit: Tr   z2SELECT * FROM before_arrival WHERE patient_no = %srb   r  z$Error fetching before_arrival data: re   rg   r@   r  r  r+   r+   r,   get_before_arrival_data`  r  r  z!/api/get_report_data/{patient_no}c              
      r  )Nz$Fetching FULL report data for edit: Tr   z*SELECT * FROM report WHERE patient_no = %srb   r  zError fetching report data: re   rg   r@   r  r  r+   r+   r,   get_report_datax  r  r  z"/api/get_patient_data/{patient_no}c           	   
      s4  t d|   t }|jdd}z~z_d}||| f | }|rd|drU|d d}g }|D ]}d|v rH|t	 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: Tr   z#SELECT * FROM patient WHERE No = %sr  r   /home/r'   rb   r  zError fetching patient data: re   rg   r@   )rv   r   r-   rk   rp   rq   r  r  r  r  rG   rH   r  rI   rs   rt   rw   r   r:   )	r   rz   rk   r   r   	raw_pathsconverted_pathsrH   r   r+   r+   r,   get_patient_data  s<   



r  z)/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: Tr   z2SELECT * FROM primary_triage WHERE patient_no = %srb   r  zError fetching primary triage: rg   r@   r  r  r+   r+   r,   get_primary_triage_data  s$   

r  
table_namec           	         s  t d| d|   t }|jdd}zhd| d}||| f | }|rp|drb|d d}g }|D ]!}| }|sBq9d	|v rU|	t
 d
tj|  q9|	| q9d||d< d|dW |  |  S di dW |  |  S |  |  w )Nz	Fetching z for: Tr   zSELECT * FROM z WHERE patient_no = %sr  r   r  r'   rb   r  )rv   r   r-   rk   rp   rq   r  r  rh   r  r  rG   rH   r  rI   rs   )	r   r  rz   rk   r   r   r  r  rH   r+   r+   r,   get_secondary_triage_common  s6   





r  z-/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  z-/api/get_secondary_triage_2_data/{patient_no}c                    r  )Nr   r  r	  r+   r+   r,   get_secondary_triage_2_data  r  r  z-/api/get_secondary_triage_3_data/{patient_no}c                    r  )Nr   r  r	  r+   r+   r,   get_secondary_triage_3_data  r  r  z/api/patient_listc                    s  t  }|jdd}zzd}g }| rd}|d|  d nd}|d d	| d
}||t| | }g }|D ]G}|d |d |d |d g}	||d |d rX|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 |r|  |r|  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 )NTr   r   r  r  r  zWHERE l.patient_no LIKE %sr   u  
            SELECT 
                l.patient_no,
                
                -- 患者基本情報 (patient_info)
                pi.patient_name2 as name,
                pi.patient_name1 as kana,
                pi.age,
                pi.gender,
                pi.chief_complaint,
                
                -- トリアージ情報
                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
            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
            
            u     -- [중요] 위에서 만든 조건절이 여기에 들어갑니다
            
            ORDER BY l.date_time DESC
        triage_1triage_2triage_3triage_4r   r0   -kanar   r   rn  )r   r0   r  r   r   rn  r  rE   r   rw   rg   rF   rA   )r-   rk   r  rp   r  r   r   rs   r(   r)   r   rv   rw   r:   )rZ   r   rk   r  r  sqlr   r  r   triage_datar   r+   r+   r,   get_patient_list  sj   


 

r  z/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 )NTr   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.address as address1,
                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 as address2,
                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.ambulance_team_name,
                r.team_leader_name,
                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,
                m.img as img_5

            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
        rw   zPatient not foundr?   r  r  r   r  (r   first_aid_fullrE   r   rg   )r-   rk   rp   rq   r   rs   r(   r)   r   rv   rw   r:   )r   r   rk   r  r   first_aid_textr   r+   r+   r,   get_patient_detailP  sP    %

 

r  z/api/login/firec                    s   t  }|jdd}z;d}||| j| jf | }|r4tdd|d |d ddW |  |  S td	d
dddW |  |  S |  |  w )NTr   z[SELECT team_name, abbreviation FROM ambulance_teams WHERE team_code = %s AND call_name = %szLogin Successr   rZ   )rb   rc   r#   rZ   rE   FzLogin Failedr     r  )r-   rk   rp   rU   r$   rq   r   rs   r\   r   rk   r  r#   r+   r+   r,   
login_fire  s*   
	


r  z/api/login/medicalc                    s   t  }|jdd}z3d}||| j| jf | }|r,ddddW |  |  S tddd	d
dW |  |  S |  |  w )NTr   zASELECT * FROM medical_users WHERE login_id = %s AND password = %szMedical Login Successr   )rb   rc   rZ   r  Fu'   ID 또는 비밀번호가 틀립니다.r   r   )r-   rk   rp   rU   r$   rq   rs   r   r  r+   r+   r,   login_medical-  s&   	


r  z/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 = %srb   z3Incident completed (sick_and_wounded_list updated).r   rg   r@   )	r-   rk   rp   rr   rs   rt   ru   r   r:   )r   rz   rk   r   r   r+   r+   r,   complete_sick_woundedE  s"   

r  z/api/get_date_numr   c              
      s   t d|   t }|jdd}z`z>| r d}||| f nd}|| | }|rAd|d |d d	W W |  |  S dd d d
dW W |  |  S  tyr } zt jd| dd t	ddt
| dd }~ww |  |  w )Nz Fetching date_num. Target date: Tr   zZSELECT `datetime`, `num` FROM `date_num` WHERE `datetime` = %s ORDER BY `num` DESC LIMIT 1zDSELECT `datetime`, `num` FROM `date_num` ORDER BY `num` DESC LIMIT 1rb   r   r   )r   r   r   u*   該当するデータがありません。)r   r   r   rc   zError fetching date_num: re   rg   r   r@   r  )r   rz   rk   r   r   r   r+   r+   r,   get_date_numZ  s@   



r  )r   )|fastapir   r   r   r   r   r   pydanticr   typingr	   r
   r   mysql.connectorr(   rG   r  r  r
  r  openpyxl.drawing.imager   openpyxl.stylesr   r   fastapi.middleware.corsr   rl   r   fastapi.staticfilesr   fastapi.responsesr   r   PILPILImagerv   logging.handlersr   basicConfigINFOappadd_middlewareenvironr  r   r   r   r    r   r  r  r  r-   mountr3   rQ   rR   rT   rV   rY   postr   r:   r   r   r   r   r   r   r   r   r   r(  rP  rZ  rb  rw  r  r  r  r  r  r   r  r  r  r  r  r  r  r  r  boolr   r
  rZ  r\  rl  rz  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,   <module>   s    			

7
-3*-'-&	
 ]	
~	
e0	
D	
 !"#$%&'()*+,-vl	
M	
4	
 	
 	
 51$   Y     2c ?	
o"
3 ! \ D&