
    riG                        d dl mZ d dlmZ d dlmZ d dlmZ d dlm	Z	m
Z
 d dlmZmZ d dlmZ d dlmZ d d	lmZmZmZ d d
lmZ d dlmZ d dlmZ d dlmZ d dlmZ d dl m!Z! d dl"m#Z# d dl$m%Z%m&Z&m'Z'm(Z(m)Z)m*Z* d dl+m,Z,m-Z- d dl.m/Z/m0Z0 d dl1m2Z2 d dl3m4Z4 d dl5m6Z6 de7de7de	e7   de	e8   de7f
dZ9d&de7de7d e8e7   de8e8   d!e7ez  dz  f
d"Z:d&de7de7d e8e7   de8e8   d!e7ez  dz  f
d#Z; G d$ d%      Z<y)'    )FileResponse)HTTPException)Workbook)Path)ListOptional)Font	Alignment)get_column_letter)func)Crew_report_by_statusCrew_report_by_typecrew_report)TblVesselList)TblCompanyList)TblSeafarers)TblSeafarerRole)TblEnrollment)TblEnrolledSeafarerInfo)Session)SimpleDocTemplateTable
TableStyle	ParagraphImageSpacer)A4	landscape)getSampleStyleSheetParagraphStyle)	TA_CENTER)colors)inchtitlesubtitlecolumnsrows	file_namec                    t               }|j                  }d|_        |j                  dddt	        |             | |d<   t        dd      |d   _        t        d	      |d   _        |j                  d
dd
t	        |             ||d<   t        dd      |d   _        t        d	      |d   _        |j                  |       d}t        dt	        |      dz         D ]8  }|j                  ||      }	t        d      |	_        t        dd      |	_        : |D ]  }
|j                  |
        |j                  D ]A  }t        d |D              }|dz   |j                  t        |d   j                            _        C d|_        t'        d| d      }|j(                  j+                  d       |j-                  |       |S )NReport   )	start_rowstart_columnend_row
end_columnA1T   )boldsizecenter)
horizontal   A2      )rowcolumn)r2   )r5   verticalc              3   t   K   | ]0  }|j                   rt        t        |j                               nd  2 yw)r   N)valuelenstr).0cells     3/var/www/html/beacon-api/app/api/reports/service.py	<genexpr>z(generate_excel_report.<locals>.<genexpr>5   s)     SstS_-Bss   68   r   r   zreports/z.xlsxexist_ok)r   activer$   merge_cellsr?   r	   fontr
   	alignmentappendrangerB   r&   maxcolumn_dimensionsr   r;   widthfreeze_panesr   parentmkdirsave)r$   r%   r&   r'   r(   wbws
header_rowcolrB   r:   
max_lengthreport_paths                rC   generate_excel_reportr[      s   	B	BBHNNQQc'lNSBtHd,BtHM"h7BtHNNQQc'lNSBtHd,BtHM"h7BtH IIgJQGq()ww:cw2dO	"hJ *
 
		#  zzSsSS
GQTU~.s1v}}=>D  BO)E23Kd+GGK    Ncompany_nametotalheaders	logo_pathc                    t        d      }|j                  j                  dd       t        t              \  }}t        t        |      t        t              dddd      }||j                  z
  |j                  z
  }	g }
t               }|r?t        |      j                         r&t        t        |      dt        z  dt        z        }nt        d	d	      }t        d
dd	d      }t        ddd	      }t        | |      t        d	d      t        ||      g}t!        ||ggdt        z  |	dt        z  z
  g      }|j#                  t%        g d             |
j'                  |       |
j'                  t        d	d             |g|z   }|	dz  |	dz  |	dz  |	dz  |	dz  |	dz  |	dz  |	dz  g}t!        ||d	      }|j#                  t%        dddt)        j*                  d      fd d!d"d#d$d%d&d'd(d)d*d+t(        j,                  t)        j*                  d,      gfd-dd+d.t(        j.                  fd/d0g             |
j'                  |       |j1                  |
       |S )1Nreports/Crew_Report.pdfTparentsrG      pagesize
leftMarginrightMargin	topMarginbottomMargin?皙?rP   heightr+   Title   Helvetica-BoldnamefontSizerK   fontNameSubtitle   rt   ru   rK      皙?	colWidthsVALIGNr   r   r   MIDDLEALIGNr+   r   r   CENTER)BOTTOMPADDINGr   r   r1   {Gz?
ףp=
?{Gz?Q?g{Gz?Q?r}   
repeatRows
BACKGROUNDr   r   r   #cfe2ffFONTNAMEr   r   rr   r   r   r   r   r   r   r   r   r   r   r+   )r   r   r   r   )r6   r+   )r6   r   r   )r   )rz   r+   rz   r   r   r   )   r+   )r   r   r   )r   r+   r+   )   r   LEFTWORDWRAPr   r   ROWBACKGROUNDSr   r   #e7f0ffGRID      ?
TOPPADDINGr   r   rz   r   r   r   rz   r   rR   rS   r   r   r   r@   rh   ri   r   existsr   r#   r   r    r   r   setStyler   rL   r"   HexColorwhiteblackbuildr]   r^   r_   r'   r`   output_path
page_widthpage_heightdocusable_widthelementsstyleslogotitle_stylesubtitle_styletitle_blockheader_table
table_data
col_widthstables                       rC   generate_pdf_reportr   @   s   01KTD9'mJ
K2C .@LH "FT)_++-S^3:cDjIa| !	K $N 	,,q!%(K 
	:|cDj89L
 * &   OOL!OOF1bM"T!J 	tttttttt	
J *
qAE	NN:	vw	(BC7,- 	-,,,* 	' 
68	vy12	4 
35 	,.5  < OOEIIhr\   c                    t        d      }|j                  j                  dd       t        t              \  }}t        t        |      t        t              dddd      }||j                  z
  |j                  z
  }	g }
t               }|r?t        |      j                         r&t        t        |      dt        z  dt        z        }nt        d	d	      }t        d
dd	d      }t        ddd	      }t        | |      t        d	d      t        ||      g}t!        ||ggdt        z  |	dt        z  z
  g      }|j#                  t%        g d             |
j'                  |       |
j'                  t        d	d             |g|z   }|	dz  |	dz  |	dz  |	dz  |	dz  |	dz  |	dz  |	dz  |	dz  g	}t!        ||d	      }|j#                  t%        ddd t)        j*                  d!      fd"d#d$d%d&d'd(d)d*d+d,d-d.t(        j,                  t)        j*                  d/      gfd0dd.d1t(        j.                  fd2d3g             |
j'                  |       |j1                  |
       |S )4Nrb   Trc   re   rf   rl   rm   rn   r+   rp   rq   rr   rs   rw   rx   ry   rz   r{   r|   r~   r   r   gQ?r   r   g333333?r   g?r   r   r   r   r   r   r   r   r   r   )r   )rE   r+   )rE   r   r   r   )r   )   r+   )r   r   r   )r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   s                       rC   generate_pdf_report2r      s   01KTD9'mJ
K2C .@LH "FT)_++-S^3:cDjIa| !	K $N 	,,q!%(K 
	:|cDj89L
 * &   OOL!OOF1bM"T!J 	ttttttttt
J *
qAE	NN:	vw	(BC7,- 	-,,,,* 	' 
68\\6??956	8 
35 	,.9  @ OOEIIhr\   c                       e Zd ZefdefdZdefdZdefdZdefdZ	defdZ
defdZdefd	Zd
efdZd
efdZdefdZd
efdZd
efdZdefdZddedee   fdZd
efdZd
efdZd Zy)ReportServicedbc                      || _         || _        y N)r   token)selfr   r   s      rC   __init__zReportService.__init__  s    
r\   
company_idc                    K   | j                   j                  t              j                  t        j                  |k(        j                         }|S wr   r   queryr   filterr   all)r   r   get_all_datas      rC   get_all_vessal_by_companyz'ReportService.get_all_vessal_by_company$  s>     ww}}]3::=;S;SWa;abffh   AAc                 H  K   | j                   j                  t              j                  t        j                  |k(        j                         }| j                   j                  t              j                  t        j                  |k(        j                         }d|j                   }dt        |       }g d}t        |d      D cg c])  \  }}||j                  |j                  |j                  g+ }	}}t        ||||	d      }
t        |
dd	      S c c}}w w)
NCompany Name : zTotal Vessels : )NOVessel NamezVessel TypeIMOr+   startVessel_ReportAapplication/vnd.openxmlformats-officedocument.spreadsheetml.sheetzVessel_Report.xlsxpath
media_typefilename)r   r   r   r   r   r   r   firstr]   r?   	enumeratevessel_namevessel_typeimor[   r   )r   r   r   get_company_namer$   r%   r_   idxvesselr'   
excel_paths              rC   create_excel_reportz!ReportService.create_excel_report(  s    ww}}]3::=;S;SWa;abffh77==8??@Y@Y]g@ghnnp!"2"?"?!@A%c,&7%89= %\;
;KC 
f  &"4"4fjjA; 	 

 +5(GT?[
\+
 	

s   CD".D<&D"c                 <  K   | j                   j                  t              j                  t        j                  |k(        j                         }| j                   j                  t              j                  t        j                  |k(        j                         }t        d      }|j                  j                  d       t        t        |      t        dddd      }t               }g }t        ddd      }t!        d	|d
   t"        dd      }	t!        d|d   t"        d      }
t%        |t'        d|j(                   d|	      ggddg      }|j+                  t-        ddddddt.        j0                  fg             |j3                  |       |j3                  t5        dd             |j3                  t'        dt7        |       |
             |j3                  t5        dd              g d!g}t9        |d"      D ]8  \  }}|j3                  ||j:                  |j<                  |j>                  g       : t%        |g d#      }|j+                  t-        d$dd%t/        j@                  d&      fd'd(d)ddd*t.        jB                  fd+d,d-d.d/d0d1g             tE        dt7        |            D ]8  }|d2z  dk(  s|j+                  d$d|fd3|ft/        j@                  d4      fg       : |j3                  |       |jG                  |       tI        |d5d67      S w)8Nzreports/Vessel_Report.pdfTrF   re   )rg   ri   rh   rj   rk   !app/static/marinerskills_logo.png<   rn   
TitleStylerp   rq   rz   )rR   rK   ru   
spaceAfterSubtitleStyleNormalrx   )rR   rK   ru   z<b>z</b>P   i  r|   )r   r   r   r   r   BOXr   r   r   r+   r   z<b>Total Vessels:</b>    )SNOzVESSEL NAMEzVESSEL TYPEzIMO NOr   )2      r   x   r   r   r   r   r   r   g?r   )r   )r9   r+   )r9   r   r   r   )LEFTPADDINGr   r   rz   )RIGHTPADDINGr   r   rz   r   r   r6   r   z#e7f1ffapplication/pdfzVessel_Report.pdfr   r   )%r   r   r   r   r   r   r   r   r   rR   rS   r   r@   r   r   r   r    r!   r   r   r]   r   r   r"   r   rL   r   r?   r   r   r   r   r   redrM   r   r   )r   r   r   r   	file_pathr   r   r   r   r   r   r   r   ir   r   r:   s                    rC   generate_vessel_pdfz!ReportService.generate_vessel_pdf=  s    ww}}]3::=;S;SWa;abffh77==8??@Y@Y]g@ghnnp45	-	N
 %&82N$'?
 ((#	
 C 0 = =>dC[Q  3i
 	j0/FHa6*
  	 	%q!%.s</@.ABNS	
 	q"& <

 #<q9IAv""""

	  : )
 	z67FOOI,FG;0VXsFJJ700201/2#
  	$ C
O,CQw!|!As8b#Y	8RS   - 			(((
 	
s   J:L=ALc                    K   | j                   j                  t              j                  t        j                  |k(        j                         }|S wr   r   )r   r   r   s      rC   get_all_vessel_by_companyz'ReportService.get_all_vessel_by_company  s=     }-44]5M5MQ[5[\``br   c                 X  K   | j                   j                  t        j                  j	                  d      t        j                  t        j                        j	                  d            j                  t        t        j                  t        j                  k(        j                  t        j                        j                         }| j                   j                  t        j                  t        j                  t        j                  t        j                   t        j"                  t$        j&                  t$        j(                  t$        j*                  t,        j.                  |j0                  j                  
      j                  t$        t$        j&                  t        j&                  k(        j                  t,        t,        j2                  t        j2                  k(        j5                  ||j0                  j                  t        j                  k(        j7                  t$        j8                  |k(        }|j;                  t$        j&                        j=                         }|st?        dd      g }|D ]  }|jA                  |j                  dj                  t7        d |j                  |j                  |j                   g            |j.                  |j&                  |j(                  |j*                  |j"                  |j                  r|j                  ndd        |S w)	Nseafarer_idlast_accessed  z"No crew found for selected vesselsstatus_codedetail -)crew_id	crew_name	crew_role	vessel_idr   r   onboard_datelast_accessed_date)!r   r   r   r  labelr   rN   r   r  joinenrollment_idgroup_bysubqueryr   
first_namemiddle_name	last_namer  r   r  r   r   r   sf_role_namec
sf_role_id	outerjoinr   r   order_byr   r   rL   )r   r   last_accessed_subqueryr   resultsresponser:   s          rC   get_crew_by_datazReportService.get_crew_by_data  sM     GGMM))//>0>>?EEoV T''559T9TT Xm//0XZ 	 GGMM((''((&&))''))!!,,&((66 T-!8!8L<R<R!RST/?#=#=AXAX#XYY&&((448P8PP VM,,
:;' 	, ..##

#% 	 ;  COO?? XX4#..#//3==!QR !--!mm"ww # 0 0;>;L;Lc&7&7RU   s   L(L*c                 
  K   | j                   j                  t              j                  t        j                  |k(        j                         }g }|D ]*  }|j                  |j                  |j                  d       , |S w)N)r  r   )	r   r   r   r   r   r   rL   r  r   )r   r   r   vessel_id_and_namevess        rC   get_all_vessel_name_by_companyz,ReportService.get_all_vessel_name_by_company  sq     }-44]5M5MQ[5[\``bC%%!mm #'  
 "!s   BBrequestc                   K   | j                   j                  t              j                  t        j                  |j                  k(        j                         }| j                   j                  t        j                  j                  d      t        j                  t        j                        j                  d            j                  t        t        j                  t        j                  k(        j                  t        j                        j!                         }| j                   j                  t"        j                  t"        j$                  t"        j&                  t"        j(                  t"        j*                  t,        j.                  t,        j0                  t2        j4                  |j6                  j                  	      j                  t,        t,        j8                  t"        j8                  k(        j                  t2        t2        j:                  t"        j:                  k(        j=                  ||j6                  j                  t"        j                  k(        j                  t,        j                  |j                  k(        }t?        |j8                        dkD  r8|j                  t"        j8                  jA                  |j8                              }|jC                  t,        j8                        jE                         }g d}d|jF                   }dt?        |       }g }	tI        |d      D ]  \  }
}d	j                  t        d |j$                  |j&                  |j(                  g            }|	jK                  |
||j                  |j4                  |j                  r|j                  nd
|j.                  |j0                  |j*                  g        tM        ||||	d      }tO        |d|jF                   d      S w)Nr  r  r   r   	Crew NameCrew Id	Crew RoleLast Accessed Dater   r   Onboard Dater   Total Crew : r+   r   r  r  Crew_Reportr$   r%   r&   r'   r(   r   zCrew_Report_vessel.xlsxr   )(r   r   r   r   r   r   r   r  r  r   rN   r   r  r  r  r  r  r   r  r  r  r  r   r   r   r   r  r  r  r  r  r?   in_r   r   r]   r   rL   r[   r   )r   r)  companyr!  r   r"  r_   r$   r%   r'   r   r:   	full_namer   s                 rC   crew_report_excel_by_vesselz)ReportService.crew_report_excel_by_vessel  s    ''--/66~7P7PT[TfTf7fgmmo GGMM))//>0>>?EEoV T''559T9TT Xm//0XZ 	 GGMM((''((&&))))!!,,&((66
 T-!8!8L<R<R!RST/?#=#=AXAX#XYY&&((448P8PP VM,,0B0BBC% 	( w  !A%LL!7!7!;!;G<M<M!NOE..##

#% 	 z!'"6"6!78"3w<.1!'3HCtcnncoos}}MNI KK  %(%6%6!!C  	 	 4  +#

 Z,,--DE
 	
s   P
Pc                   K   | j                   j                  t              j                  t        j                  |j                  k(        j                         }| j                   j                  t        j                  j                  d      t        j                  t        j                        j                  d            j                  t        t        j                  t        j                  k(        j                  t        j                        j!                         }| j                   j                  t"        j                  t"        j$                  t"        j&                  t"        j(                  t"        j*                  t,        j.                  t,        j0                  t2        j4                  |j6                  j                  	      j                  t,        t,        j8                  t"        j8                  k(        j                  t2        t2        j:                  t"        j:                  k(        j=                  ||j6                  j                  t"        j                  k(        j                  t,        j                  |j                  k(        }t?        |j8                        dkD  r8|j                  t"        j8                  jA                  |j8                              }|jC                  t,        j8                        jE                         }g d}|jF                  }dt?        |       }g }	tI        |d      D ]  \  }
}dj                  t        d |j$                  |j&                  |j(                  g            }|	jK                  |
||j                  |j4                  |j                  r|j                  nd	|j.                  |j0                  |j*                  g        d
}tM        ||||	|      }tO        |d|jF                   d      S w)Nr  r  r   r+  r1  r+   r   r  r  r   r   zCrew_Report_vessel.pdfr   )(r   r   r   r   r   r   r   r  r  r   rN   r   r  r  r  r  r  r   r  r  r  r  r   r   r   r   r  r  r  r  r  r?   r4  r   r   r]   r   rL   r   r   )r   r)  r5  r!  r   r"  r_   r]   
total_crewr'   r   r:   r6  r`   pdf_files                  rC   crew_report_pdf_by_vesselz'ReportService.crew_report_pdf_by_vesselE  s    ''--/66~7P7PT[TfTf7fgmmoGGMM))//>0>>?EEoV T''559T9TT Xm//0XZ 	 GGMM((''((&&))))!!,,&((66
 T-!8!8L<R<R!RST/?#=#=AXAX#XYY&&((448P8PP VM,,0B0BBC% 	( w  !A%LL!7!7!;!;G<M<M!NOE..!8!89==?y++$S\N3
!'3HCtcnncoos}}MNI KK  %(%6%6!!C  	 	 4 8	&|JwtIV(,,--CD
 	
s   PP
c                 f  K   | j                   j                  t              j                  t        j                  |k(        j                         }|st        dd      | j                   j                  t        j                  j                  d      t        j                  t        j                        j                  d            j                  t        t        j                  t        j                  k(        j!                  t        j                        j#                         }| j                   j                  t$        j                  t$        j&                  t$        j(                  t$        j*                  t$        j,                  t.        j0                  t.        j2                  t.        j4                  t.        j6                  t8        j:                  |j<                  j                        j                  t.        t.        j0                  t$        j0                  k(        j                  t8        t8        j>                  t$        j>                  k(        jA                  ||j<                  j                  t$        j                  k(        j                  t.        j                  |k(        }|jC                  t.        j0                        jE                         }|st        dd      g }tG        |d      D ]  \  }}d	j                  t        d |j&                  |j(                  |j*                  g            }	|jI                  ||	|j                  |j:                  |j                  r|j                  nd
|j0                  |j2                  |j4                  |j6                  |j,                  d
        |S w)Nr  Company not foundr	  r  r  'No seafarers found for this vessel typer+   r   r  r  
nor  r  r  r  r  r   r   r   r  )%r   r   r   r   r   r   r   r   r  r  r   rN   r   r  r  r  r  r  r   r  r  r  r  r   r  r   r   r   r   r  r  r  r  r   r   r   rL   )
r   r   r5  r!  r   r"  datar   r:   r6  s
             rC   get_all_seafarer_by_vesseltypez,ReportService.get_all_seafarer_by_vesseltype  s    ''--/66~7P7PT^7^_eegC8KLL GGMM))//>0>>?EEoV T''559T9TT Xm//0XZ 	 GGMM((''((&&))''))))!!,,&((66 T-!8!8L<R<R!RST/?#=#=AXAX#XYY&&((448P8PP VM,,
:;) 	. ..!8!89==?@  !'3HCtcnncoos}}MNI KK&?? --;>;L;Lc&7&7RU!mm #"ww # 0 0  4$ s   N/N1c                 8	  K   | j                   j                  t              j                  t        j                  |j                  k(        j                         }|st        dd      | j                   j                  t        j                  j                  d      t        j                  t        j                        j                  d            j                  t        t        j                  t        j                  k(        j!                  t        j                        j#                         }| j                   j                  t$        j                  t$        j&                  t$        j(                  t$        j*                  t$        j,                  t.        j0                  t.        j2                  t.        j4                  t.        j6                  t8        j:                  |j<                  j                        j                  t.        t.        j0                  t$        j0                  k(        j                  t8        t8        j>                  t$        j>                  k(        jA                  ||j<                  j                  t$        j                  k(        j                  t.        j                  |j                  k(        }|j4                  r8|j                  t.        j4                  jC                  |j4                              }|jE                  t.        j0                        jG                         }|st        dd      g }tI        |d      D ]  \  }}d	j                  t        d |j&                  |j(                  |j*                  g            }	|jK                  ||	|j                  |j:                  |j                  r|j                  nd
|j0                  |j2                  |j4                  |j6                  |j,                  d
        d|jL                   }
dtO        |       }g d}g }|D ]3  }|jK                  |d   |d   |d   |d   |d   |d   |d   |d   g       5 tQ        |
||||jL                   d      }tS        |d|jL                   d      S w)Nr  r=  r	  r  r  r>  r+   r   r  r  r?  r   r1  Nor,  r-  r.  r/  r   r   r0  r@  r  r  r  r  r   r   r  vessel_type_Crew_Reportr3  r   zCrew_Report_vessel_type.xlsxr   )*r   r   r   r   r   r   r   r   r  r  r   rN   r   r  r  r  r  r  r   r  r  r  r  r   r  r   r   r   r   r  r  r  r  r4  r   r   r   rL   r]   r?   r[   r   )r   r)  r5  r!  r   r"  datasr   r:   r6  r$   r%   headerrA  r   s                  rC   %generate_seafarer_excel_by_vesseltypez3ReportService.generate_seafarer_excel_by_vesseltype  s    ''--/66~7P7PT[TfTf7fgmmoC8KLL GGMM))//>0>>?EEoV T''559T9TT Xm//0XZ 	 GGMM((''((&&))''))))!!,,&((66 T-!8!8L<R<R!RST/?#=#=AXAX#XYY&&((448P8PP VM,,0B0BBC) 	, LL!:!:!>!>w?R?R!STE ..!8!89==?@  !'3HCtcnncoos}}MNI LL&?? --;>;L;Lc&7&7RU!mm #"ww # 0 0  4& "'"6"6!78"3w<.1xCKKD	#k*3y>#k:J3OcKdehivewx{  }B  yC  DG  HV  DW  
 + --..EF

 Z,,--IJ
 	
s   RRc                 	  K   | j                   j                  t              j                  t        j                  |j                  k(        j                         }|st        dd      | j                   j                  t        j                  j                  d      t        j                  t        j                        j                  d            j                  t        t        j                  t        j                  k(        j!                  t        j                        j#                         }| j                   j                  t$        j                  t$        j&                  t$        j(                  t$        j*                  t$        j,                  t.        j0                  t.        j2                  t.        j4                  t.        j6                  t8        j:                  |j<                  j                        j                  t.        t.        j0                  t$        j0                  k(        j                  t8        t8        j>                  t$        j>                  k(        jA                  ||j<                  j                  t$        j                  k(        j                  t.        j                  |j                  k(        }|j4                  r8|j                  t.        j4                  jC                  |j4                              }|jE                  t.        j0                        jG                         }|st        dd      g }tI        |d      D ]  \  }}d	j                  t        d |j&                  |j(                  |j*                  g            }	|jK                  ||	|j                  |j:                  |j                  r|j                  nd
|j0                  |j2                  |j4                  |j6                  |j,                  d
        |jL                  }
dtO        |       }g d}g }|D ]3  }|jK                  |d   |d   |d   |d   |d   |d   |d   |d   g       5 d}tQ        |
||||      }tS        |d|jL                   d      S w)Nr  r=  r	  r  r  r>  r+   r   r  r  r?  r1  rD  r@  r  r  r  r  r   r   r  r   r   zCrew_Report_vessel_type.pdfr   )*r   r   r   r   r   r   r   r   r  r  r   rN   r   r  r  r  r  r  r   r  r  r  r  r   r  r   r   r   r   r  r  r  r  r4  r   r   r   rL   r]   r?   r   r   )r   r)  r5  r!  r   r"  rG  r   r:   r6  r]   r9  rH  rA  r`   r:  s                   rC   #generate_seafarer_pdf_by_vesseltypez1ReportService.generate_seafarer_pdf_by_vesseltype7  s    ''--/66~7P7PT[TfTf7fgmmoC8KLL GGMM))//>0>>?EEoV T''559T9TT Xm//0XZ 	 GGMM((''((&&))''))))!!,,&((66 T-!8!8L<R<R!RST/?#=#=AXAX#XYY&&((448P8PP VM,,0B0BBC) 	, LL!:!:!>!>w?R?R!STE ..!8!89==?@  !'3HCtcnncoos}}MNI LL&?? --;>;L;Lc&7&7RU!mm #"ww # 0 0  4$ ++$S\N3
xCKKD	#k*3y>#k:J3OcKdehivewx{  }B  yC  DG  HV  DW  
 8	&|Jvd9U(,,--HI
 	
s   R	Rc                 :  K   | j                   j                  t        j                        j	                  t        j
                  |k(        j                         j                         }t        |d      D cg c]  \  }}||j                  d c}}S c c}}w w)Nr+   r   )r  vessel_type_name)	r   r   r   r   r   r   distinctr   r   )r   r   vessel_typesr   vts        rC   get_all_vessel_type_by_companyz,ReportService.get_all_vessel_type_by_company  s     GGMM-334VM,,
:;XZSU	 	 %\:

 ;R "$&NN ;
 	
 
s   A6B8B
BNstatusc                   K   | j                   j                  t        j                  j	                  d      t        j                  t        j                        j	                  d            j                  t        t        j                  t        j                  k(        j                  t        j                        j                         }| j                   j                  t        j                  t        j                  t        j                  t        j                   t        j"                  t        j$                  t&        j(                  t&        j*                  t,        j.                  |j0                  j                  
      j                  t&        t&        j2                  t        j2                  k(        j                  t,        t,        j4                  t        j4                  k(        j7                  ||j0                  j                  t        j                  k(        j9                  t&        j:                  |k(        }|r1|dk(  rd}n|dk(  rd}|j9                  t        j$                  |k(        }|j=                  t&        j2                        j?                         }g }tA        |d      D ]  \  }}dj                  t9        d |j                  |j                  |j                   g            }	|jC                  ||	|j                  |j.                  |j$                  |j                  r|j                  nd	|j(                  |j*                  |j"                  d
	        |S w)Nr  r  r+   Activer6   Inactiver   r  r  )	r@  r  r  r  rR  r  r   r   r  )"r   r   r   r  r  r   rN   r   r  r  r  r  r  r   r  r  r  r  rR  r   r   r   r   r  r  r  r  r  r   r   r   r   r   rL   )
r   r   rR  r!  r   r"  r#  r   r:   r6  s
             rC   get_all_seafarer_by_statusz(ReportService.get_all_seafarer_by_status  st     GGMM))//>0>>?EEoV T''559T9TT Xm//0XZ 	 ''--$$##$$""%%%%(("$$22
 T-!8!8L<R<R!RST/?#=#=AXAX#XYY&&((448P8PP VM,,
:;% 	( {!1#LL!4!4!>?E..!8!89==?!'3HCs~~sPSP]P].^!_`IOO%??,,:=:K:KS%6%6QT #gg"//
 
 4 s   MMc                 V  K   |j                   }|j                  }| j                  j                  t              j                  t        j                   |k(        j                         }|st        dd      | j                  ||       d {   }d|j                   }dt        |       }g d}g }	|D ]7  }
|	j                  |
d   |
d   |
d	   |
d
   |
d   |
d   |
d   |
d   |
d   g	       9 t        ||||	|j                   d      }t        |d|j                   d      S 7 w)Nr  Company Not Foundr	  r   r1  	rE  r,  r-  r.  Statusr/  r   r   r0  r@  r  r  r  rR  r  r   r   r  _Seafarer_Status_Reportr   z_Seafarer_Status_Report.xlsxr   )r   rR  r   r   r   r   r   r   rV  r]   r?   rL   r[   r   )r   r)  r   rR  r5  r'   r$   r%   rH  rA  r:   r   s               rC   !generate_seafarer_excel_by_statusz/ReportService.generate_seafarer_excel_by_status  sp    ''
''--/66~7P7PT^7^_eegC8KLL44ZHH!'"6"6!78"3t9+. CCKKD	#k*3y>#k:J3x=Y\]qYrsv  xE  tF  GJ  KP  GQ  RU  Vd  Re  
 +5(FDWMaMaLbbyJz{
Z,,--IJ
 	
 Is   B
D)D'BD)c                 :  K   |j                   }|j                  }| j                  j                  t              j                  t        j                   |k(        j                         }|st        dd      | j                  ||       d {   }|j                  }dt        |       }g d}g }	|D ]7  }
|	j                  |
d   |
d   |
d   |
d	   |
d
   |
d   |
d   |
d   |
d   g	       9 d}t        ||||	|      }t        |d|j                   d      S 7 w)Nr  rX  r	  r1  rY  r@  r  r  r  rR  r  r   r   r  r   r   z_Seafarer_Status_Report.pdfr   )r   rR  r   r   r   r   r   r   rV  r]   r?   rL   r   r   )r   r)  r   rR  r5  r'   r]   r9  rH  rA  r:   r`   r:  s                rC   generate_seafarer_pdf_by_statusz-ReportService.generate_seafarer_pdf_by_status  s^    ''
''--/66~7P7PT^7^_eegC8KLL44ZHH++$SYK0
 CCKKD	#k*3y>#k:J3x=Y\]qYrsv  xE  tF  GJ  KP  GQ  RU  Vd  Re  
 8	'ZtIV(,,--HI
 	
! Is   B
DDBDc                 "   K   ddddddg}|S w)Nr+   rT  )	status_idstatus_resposnser6   rU   )r   rR  s     rC   seafarer_status_dropdownz&ReportService.seafarer_status_dropdown  s!     !"BSTkuCvws   r   )__name__
__module____qualname__dictr   r   intr   r   r  r  r$  r(  r   r7  r;  rB  r   rI  rK  rQ  r   rV  r   r\  r^  rc  rb  r\   rC   r   r     s    -1 G 
# 
C 
*j
S j
X3 = =~" "M
+ M
^D
 D
LFs FPb
DW b
HY
AT Y
v
 
 <3 <RU <|
@U 
6
>S 
:r\   r   r   )=fastapi.responsesr   fastapir   openpyxlr   pathlibr   typingr   r   openpyxl.stylesr	   r
   openpyxl.utilsr   
sqlalchemyr   app.api.reports.schemar   r   r   app.models.main.vessel_listr   app.models.main.company_listr   app.models.main.seafarerr   app.models.main.seafarer_roler   app.models.main.enrollmentr   $app.models.main.enrolledseafarerinfor   sqlalchemy.ormr   reportlab.platypusr   r   r   r   r   r   reportlab.lib.pagesizesr   r   reportlab.lib.stylesr   r    reportlab.lib.enumsr!   reportlab.libr"   reportlab.lib.unitsr#   r@   listr[   r   r   r   rb  r\   rC   <module>r     s   * !   ! + ,  Z Z 5 7 1 9 4 H " [ [ 0 D )   $% %s %DI %DQUJ %be %Nkc k kd3i kdSWj kdgjndnqudu kZns n# ntCy ntTXz nehkoeorvev nb| |r\   