
    ˿i6                         d dl Z d dlmZmZmZmZ d dlZd dlm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Zd dlmZ d dlmZ d dlmZ  G d	 d
      Zy)    N)ListDictUnioncast)HTTPException)
UploadFile)FileResponseStreamingResponse)BytesIO)
UPLOAD_DIR)CompanyServicec            	           e Zd Zd Zdedeeeeee   f   f   fdZ	dedeeeeee   f   f   fdZ
defdZdedefd	Zdefd
ZdedefdZdedefdZy)SeafarerServicec                 >    d| _         d| _        t               | _        y )Nz>https://cms.marinerskills.com/api/get-seafarer-based-on-vesselFslKvipEQ3hT2PfdZla00hp)api_urlsecurity_keyr   company_service)selfs    6/var/www/html/beaconod-api/app/api/seafarer/service.py__init__zSeafarerService.__init__   s    W5-/    	vessel_idreturnc                    	 t        |      | j                  d}t        j                  | j                  |d      }|j                          |j                         }t        |t              r|}nFt        |t              r+d|v rt        |d   t              r|d   }nt        d|       t        d      |D cg c]%  }|j                  d      s|j                  d      ' }}|st        dd	
      t        t        |            }||dS c c}w # t        j                  $ r}	t        dd|	 
      d}	~	wt         $ r}	t        dt        |	      
      d}	~	ww xY w)z
        Calls the third-party APIs to retrieve seafarers for a vessel,
        then extracts all distinct Role values.
        )VesselIDSecurityKey
   )jsontimeoutdataz Unexpected API response format: zInvalid API responseRole  z No roles found for this vesselIdstatus_codedetail)vesselIdrolesi  zExternal API error: N  )strr   requestspostr   raise_for_statusr   
isinstancelistdict
ValueErrorgetr   sortedsetRequestException	Exception)
r   r   payloadresponser!   	seafarerssr(   unique_roleses
             r   get_roles_by_vesselz#SeafarerService.get_roles_by_vessel   s@   
	@#&y>$BSBSTG}}T\\LH%%'==?D$% 	D$'T>jft&D $VI$'Gv%NOO !788 -6GIqvQUU6]IEG#<^__!#e*-L )LAA H (( 	TC:Nqc8RSS 	@CA??	@s<   B7D 9DD#)D D E%D55EEE
company_idc                    	 t        |      | j                  d}t               }|j                  |      }|st	        dd      |D cg c];  }t        |dt        |dd            r!t        t        |dt        |dd                  = }}t               }|D ]y  }	 | j                  |      }	|	j                  d	g       }
t        |
t              r)|j                  |
D cg c]  }t        |       c}       nt        d
| dt        |
              { |st	        dd      |t        |      dS c c}w c c}w # t        $ r}t        d| d|        Y d}~d}~ww xY w# t        $ r}t	        dt        |            d}~ww xY w)z|
        Calls third-party APIs to retrieve seafarers for a vessel,
        then extracts all distinct Role values.
        )	CompanyIDr   r#   z!No vessels found for this companyr$   r   vesselidNr   r(   zInvalid roles type for vessel z: z Error fetching roles for vessel zNo roles found for any vessels)	companyIdr(   r)   )r*   r   r   get_vessels_by_companyr   getattrintr4   r=   r2   r.   r/   updateprinttyper6   )r   r>   r7   servicecompany_datav
vessel_ids	all_rolesr   	role_datar(   rr<   s                r   get_roles_by_companyz$SeafarerService.get_roles_by_company7   s   
'	@$'
ODDUDUVG$&G"99*EL#<_`` &%A1j'!Z*FG GAz71j!+DEF%   #&%I'	O $ 8 8 CI%MM'26E "%.!((%)@%Q#a&%)@A  >ykDQVK=YZ ( #<\]] ",d9oFF7 *A
 ! O<YKr!MNNO  	@CA??	@sh   AE  A D/E  AD9D4
."D9E  /E  4D99	EEE  EE   	F)E??Fc                    | j                  |      }t        t        t           |d         }g d}||z   }ddddd|D ci c]  }||dv rdnd	 c}d
dddd|D ci c]  }|d c}g}t	        j
                  ||      }t               }	t	        j                  |	d      5 }
|j                  |
dd       ddd       |	j                  d       dd| di}t        |	d|      S c c}w c c}w # 1 sw Y   :xY w)zZ
        Generates an Excel file with static + dynamic role columns for a vessel.
        r(   zCourse NameSeriesCategoryzFunctional AreaMSSV-2001-Coping with Stressvideo-seriesVideo Series for SIRE 2.0SIRE Ch 3: Crew ManagementzOffice - Senior ManagersOtherX "MSSV-2002-Managing Fatigue onboardcolumnsopenpyxlengineFCoursesindex
sheet_nameNr   Content-Dispositionzattachment; filename=vessel__courses.xlsxAapplication/vnd.openxmlformats-officedocument.spreadsheetml.sheet
media_typeheaders)r=   r   r   r*   pd	DataFramer   ExcelWriterto_excelseekr
   )r   r   rN   r(   static_columnsall_columnsroler!   dfoutputwriterrl   s               r   generate_excel_oldz"SeafarerService.generate_excel_olde   sB   
 ,,Y7	T#Y	' 23Q$u,  >(7#?	
 chhbgZ^4(M MSUUbgh  D(7#?	
 *//49/
" \\$4^^F:6&KKe	KB 7 	A "%A)M#Z
 !Z
 	
/ i 0 76s   C'
C,#C11C:namec                 <    dd l } |j                  dd|      }|d d S )Nr   z[\\/*?\[\]]_   )resub)r   ry   r}   s      r   clean_sheet_namez SeafarerService.clean_sheet_name   s%    rvvnc40CRyr   c                 Z   t         j                  j                  t        d| d      }t         j                  j	                  |      rt        |dd| d      S | j                  j                  t        |            }|D cg c]  }|j                   }}| j                  j                  |      }|j                  }g d}||z   }	ddd	d
d|D 
ci c]  }
|
|
dv rdnd c}
ddd	d
d|D 
ci c]  }
|
d c}
g}t               }t        j                  |d      5 }|D ]>  }| j                  |      }t        j                   ||	      }|j#                  |d|       @ 	 d d d        |j%                  d       dd| di}t'        |d|      S c c}w c c}
w c c}
w # 1 sw Y   ?xY w)Ncompany_.xlsxri   rh   )rk   filenamerR   rU   rV   rW   rX   rY   r[   r\   r]   r`   ra   r^   Frd   r   rg   zattachment; filename=company_rj   )ospathjoinr   existsr	   r   	get_rolesr*   RoleNameget_vessel_typesvessel_typesr   rm   ro   r   rn   rp   rq   r
   )r   r>   uploaded_file_pathr(   rO   rM   vessel_responser   rr   rs   rt   template_datarv   rw   vessel_typecleaned_nameru   rl   s                     r   generate_excelzSeafarerService.generate_excel   s     WW\\*E6RS77>>,-"^#J<}=  $$..s:?)./AQZZ	/ ..??
K&33Q$y0  >(7#?	
 enndm\`4)N!N#TVVdmn  D(7#?	
 *33493
" ^^F:6&+#44[A\\-EF%LI  , 7 	A "%B:,m#\
 !Z
 	
Y 0 o 4 76s   FF.
FAF!!F*filec                   K   |j                   r)|j                   j                         j                  d      st        dd      | j	                  |      }t        t        t           |d         }g d}||z   }t        j                  t        d       t        j                  j                  t        d	      }	 t        |d
      5 }t        j                  |j                   |       ddd       	 t%        j&                  |d      }
|
j-                  d      }
|
j.                  d   |
_        |
j3                  |
j4                  d         }|j0                  j7                  t              j                  j9                  d      j                  j;                  dd      j                  j;                  dd      |_        |D cg c]  }||j0                  vs| }}|j0                  D cg c]	  }||vs| }}|s|rJt        j                  j)                  |      rt        j*                  |       dd|r|nd d|r|nd d| dS t        j                  j                  t        d      }t        j:                  ||       |j=                  t%        j>                  |      d      }dd|||tA        |      d S # 1 sw Y   xY w# t"        $ r}	t        dd|	       d}	~	ww xY w# t"        $ rI}	t        j                  j)                  |      rt        j*                  |       t        dd|	       d}	~	ww xY wc c}w c c}w w)!z
        Upload Excel file and validate static + dynamic columns.
        Upload will be rejected if columns do not match exactly.
        File will not be saved if validation fails.
        r   z.xls  ,Only Excel files (.xlsx, .xls) are supportedr$   r(   rR   Texist_oktemp_upload.xlsxwbNr)   Error saving temporary file: )headerError reading Excel file: allhowr   r\   
 -1NExcel upload failed. Columns do not match expected template. Missing columns: None, Extra columns: . Expected columns: statusmessagezOffline dashboard.xlsx1!Excel file validated successfully)r   r   r'   r(   saved_file_path
total_rows)!r   lowerendswithr   r=   r   r   r*   r   makedirsr   r   r   openshutilcopyfileobjr   r6   rm   
read_excelr   removedropnailocr_   dropre   astypestripreplacewherenotnulllen)r   r   r   rN   dynamic_rolesrr   required_columns	temp_pathbufferr<   df_rawru   colmissingextra
final_paths                   r   upload_courses_by_vesselz(SeafarerService.upload_courses_by_vessel   s     }}DMM$7$7$9$B$BCT$UC8fgg,,Y7	T#Y	'(:;Q)M9
J.GGLL-?@		]i&&""499f5 '
	Z]]9T:F 5)Q[[a)JJc"SrSs#Ss#	 	
 #3L"23c6K3"2L "

J
c9I.I
J eww~~i(		)$(3:'G H&/4e&%A B))9(:<  WW\\*.FG



9j)XXbjjnd+ :!")b'
 	
] '& 	]C:WXYWZ8[\\	]
  	Zww~~i(		)$C:TUVTW8XYY		Z  MJs   B6M9K !J6&K /K$ B9M?L9L9M'	L>1L>5CM6K ;K 	K!KK!!M$	L6-AL11L66Mc                   K   |j                   r)|j                   j                         j                  d      st        dd      | j                  j                  |      }|D cg c]  }|j                   }}g d}||z   }t        j                  t        d       t        j                  j                  t        d      }	 t        |d	      5 }	t        j                  |j                  |	       d d d        	 t#        j$                  |dd       }|j)                  d      }|j*                  d   |_        |j/                  |j0                  d         }|j,                  j3                  t4              j4                  j7                         j4                  j9                  dd      j4                  j9                  dd      |_        |D cg c]  }||j,                  vs| }}|j,                  D cg c]	  }||vs| }}|s|r+t        j&                  |       dd|xs d d|xs d d| dS t        j                  j                  t        d| d      }	 t        j                  j;                  |      rt        j&                  |       t        j<                  ||       dd|||t?        |      dS c c}w # 1 sw Y   xY w# t         $ r}
t        d
d|
       d }
~
ww xY w# t         $ r*}
t        j&                  |       t        dd|
       d }
~
ww xY wc c}w c c}w # t         $ r}
t        d
d      d }
~
ww xY ww) Nr   r   r   r$   rR   Tr   r   r   r)   r   r   )rf   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   zICannot overwrite existing file. Please close the Excel file if it's open.r   r   )r   r   rB   r(   r   r   ) r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r6   rm   r   r   r   r   r_   r   re   r   r*   r   r   r   mover   )r   r>   r   rN   rO   r   rr   r   r   r   r<   r   ru   r   r   r   r   s                    r   upload_courses_by_companyz)SeafarerService.upload_courses_by_company%  s    }}DMM$7$7$9$B$BCT$UC8fgg ((22:>	-67YY7 R)M9 	J.GGLL-?@		]i&&""499f5 '	Z]]94HF 5)Q[[a) JJc"SSs#Ss#	 	
 #3L"23c6K3"2L "

J
c9I.I
JeIIi ((/(96': ;&&+ov%6 7))9(:<  WW\\*E.JK
	ww~~j)		*% 	Iz* :#")b'
 	
C 8 '& 	]C:WXYWZ8[\\	]  	ZIIi C:TUVTW8XYY	Z  MJ&  	b 	s   A"M$J47A
MK !J9/K 8K' B8MLL M0	L":L">AM4L' -M9K>K 	K$KK$$M'	L0%LLM'	M0L==MMN)__name__
__module____qualname__r   rE   r   r*   r   r   r=   rP   rx   r   r   r   r   r    r   r   r   r      s    0
!@S !@T#uS$s)^?T:T5U !@F,@s ,@tCsDQTI~AV<V7W ,@\,
C ,
\S S >
 >
@I
 I
: I
XO
# O
Z O
r   r   )r}   typingr   r   r   r   unicodedatafastapir   r+   r   r   r   fastapi.responsesr	   r
   pandasrm   ior   app.api.course.servicer   app.api.company.servicer   r   r   r   r   <module>r      s;    	 * *  !  	   =   - 2f
 f
r   