
    ˿i$                         d dl Z d dlZd dlZd dlZd dlZd dlmZ d dlm	Z	 d dl
mZ dZe j                  j                  ed      Zdedefd	Zd
edefdZ G d d      Zy)    N)HTTPException)VesselService)CompanyServiceuploaded_filesOffline dashboard.xlsxvaluereturnc                    t        | t              st        |       } t        j                  d|       } | j	                  dd      } | j	                  dd      } | j	                  dd      } | j	                  dd      } | j	                  dd      } | j	                  d	d      } | j	                  d
d      } t        j                  dd|       } | j                         S )zBRemove Excel hidden characters (NBSP, odd unicode, double spaces).NFKC     u    u    u   ﻿ 	
\s+)
isinstancestrunicodedata	normalizereplaceresubstrip)r   s    4/var/www/html/beaconod-api/app/api/course/service.py
clean_textr      s    %%JE%%fe4h,h,h,h+dC(dC(dC(vsE*{{}    namec                 P    t        |       } t        j                  dd|       } | d d S )Nz[\\/*?\[\]:]_   )r   r   r   )r   s    r   clean_sheet_namer"      s*    dD66/3-D9r   c                       e Zd ZdefdZdedefdZddededej                  fdZ
d	edefd
Zdedededz  fdZd	efdZy)CourseServicevessel_servicec                 0    || _         t               | _        y N)r%   r   company_service)selfr%   s     r   __init__zCourseService.__init__"   s    ,-/r   
company_idr	   c                     t         j                  j                  t        d| d      }t         j                  j	                  |      r|S t         j                  j                  t        d      S )zP
        Return dynamic offline file per company, fallback to template.
        company_z.xlsxr   )ospathjoin
UPLOAD_DIRexists)r)   r+   r/   s      r   get_company_excelzCourseService.get_company_excel&   sL     ww||J(:,e(DE77>>$Kww||J(@AAr   	file_pathtarget_headerc                    t         j                  j                  |      st        dd|       t	        j
                  |d       }d }|j                         D ]@  \  }}|j                  D cg c]  }|t        |      j                           }}||v s>|} n |t        dd| d      t        |t              st        dd      t	        j
                  ||      }	|	j                  j                  t              j                  j                         j                  j                  d	d
      j                  j                  dd
      |	_        |	S c c}w )N  zExcel file not found: status_codedetail)header  z(Could not detect header row containing ''z+Detected header row index is not an integerr   r   r   )r.   r/   r2   r   pd
read_exceliterrowsvaluesr   r   r   intcolumnsastyper   )
r)   r4   r5   rawheader_row_idxidxrowvrA   dfs
             r   load_excel_auto_headerz$CourseService.load_excel_auto_header/   s*   ww~~i(C:PQZP[8\]]mmId3HC.1jjJjAMc!fllnjFJ&!$	 '
 !C:bcpbqqr8stt.#.C8eff]]9^<ZZ&&s+//557;;CCD#NRRZZ[_ade
	 Ks   -E5E	vessel_idc                 v   t        dd      D ]  }	 | j                  j                  |      }|D ]h  }t        |j                        t        |      k(  s%t        |j                        |j                  t        |j                        |j                  dc c S   t        dd| d      # t        $ r Y w xY w)z}
        Find vessel by vessel_id across all companies.
        Returns companyId, vesselType, vesselId, vesselName.
              )	companyId
vesselTypevesselId
vesselNamer7   zVessel z
 not foundr8   )ranger(   get_vessels_by_companyr   VesselIDrB   	CompanyID
VesselType
VesselName	Exceptionr   )r)   rL   cidvesselsrI   s        r   get_vessel_details_by_vessel_idz-CourseService.get_vessel_details_by_vessel_idD   s    
 C=C..EEcJ A1::#i.8),Q[[)9*+,,(+AJJ*+,,	   ! ! gi[
4STT  s   AB,AB,B,,	B87B8workbook_sheetsdesired_sheet_nameNc                 
   t        |      j                         }t        |      j                         }|D ]L  }t        |      j                         }t        |      j                         }||k(  s||k(  s||k(  s||k(  sJ|c S  y r'   )r   lowerr"   )r)   r^   r_   desired_cleandesired_clean2ss_cleans_clean2s           r   _find_matching_sheet_namez'CourseService._find_matching_sheet_nameX   s    "#56<<>)*<=CCE A m))+G'*002H=(M)>)N* ! r   c                 	   d}d}d|i}	 t        j                  | d|d      }|j                          |j                         }t        |t              r%t        |j                  d
      t              r|d
   }n t        |t              r|}nt        dd	      |D 	ci c]&  }	|	j                  d      s|	j                  d      |	( }
}	| j                  |      }|d   }|d   }| j                  j                  |      }|j                  dg       }t        |t              r|nt        |      g}|D cg c]  }t        |       }}| j                  |      }	 t!        j"                  |d d       }|st        dd| 	      | j'                  t        |j)                               |      }|*t        dd| dt        |j)                                	      ||   }d }|j+                         D ]J  \  }}|j-                         D cg c]  }t        t        |             }}t/        d |D              sH|} n |t        dd| d	      |j0                  |   j-                         }|D cg c]  }t        t        |             }}|j0                  |dz   d  j3                         }||_        |j4                  j7                         j9                  d       |_        |j;                  t!        j<                  |      d       }|D cg c]  }||j4                  vs| } }| r+t        dd|  d|j4                  j-                          	      g }!g }"|j+                         D ]  \  }#}|j                  d      }$|$st?        ||      D %&cg c]  \  }%}&|j                  |&      dk(  r|% }'}%}&|
j                  |$      }(|'r|(r|!jA                  |(j                  d       |(j                  d!      |(j                  d      |(j                  d"      |$|j                  d#      |j                  d$      |j                  d%      |'d&	       |"jA                  |$        d'd(|||tC        |!      tC        |"      |!|"d)	S # t         j                  $ r}t        dd| 	      d }~ww xY wc c}	w c c}w # t$        $ r}t        dd| d| 	      d }~ww xY wc c}w c c}w c c}w c c}&}%w )*Nz.https://lms.marinerskills.com/lmsmarinerskillsz$6598E51A-4C41-416C-821F-A50EBBBE4E99Keyz/FullCourseList
   )jsontimeouti  zLMS API error: r8   courseszUnexpected LMS response formatCourserP   rQ   roles)
sheet_namer;   r<   zError reading Excel file: z - zExcel file is empty: z(No matching sheet found for vesselType 'z'. Sheets: c              3   B   K   | ]  }|j                         d k(    yw)zcourse nameN)ra   ).0rI   s     r   	<genexpr>z6CourseService.sync_full_course_list.<locals>.<genexpr>   s     >v!1779-vs   z4Could not find header row ('Course Name') in sheet 'z'.rN   c                 L    t        j                  dd|       j                         S )Nr   r   )r   r   r   )xs    r   <lambda>z5CourseService.sync_full_course_list.<locals>.<lambda>   s    BFF63PQ<R<X<X<Zr   zMissing role columns: z. Sheet columns: Course NameXProgramBatchBatchCourseCodeSerieszFunctional AreaCategory)	lms_program	lms_batch
lms_coursebatch_course_codecms_course_nameseriesfunctional_areacategory_namero   1zCourses mapped successfully)	statusmessagerP   rQ   dynamic_rolestotal_matchedtotal_unmatchedmatched_coursesunmatched_courses)"requestspostraise_for_statusrk   RequestExceptionr   r   dictgetlistr]   r%   get_roles_by_vesselr   r   r3   r>   r?   rZ   rg   keysr@   tolistanyiloccopyrC   	to_seriesapplywherenotnullzipappendlen))r)   rL   LMS_BASE_URLAPI_KEYpayloadresplms_rawelms_coursesc
lms_lookupvessel_detailr+   vessel_typevessel_role_infodynamic_roles_rawr   rclean_dynamic_rolesoffline_filewbactual_sheet_namedf_rawheader_indexrG   rH   rI   rA   header_valueshclean_headersrJ   missing_rolesmatched	unmatchedr    cms_nameorigclean_rassigned_roleslms_recs)                                            r   sync_full_course_listz#CourseService.sync_full_course_listh   s    H8'"	O==L>!AY[\D!!#iikG
 gt$GKK	4JD)Q!),K&!KC8XYY2=Q+QxaeeHoq(+
Q <<YG";/
#L1  ..BB9M,00"=-78I4-P)WZ[lWmVn6CDmz!}mD --j9	k|TJB C:OP\~8^__ !::4	?KX$A+kZ^_a_f_f_hZiYjk 
 %& )HC25**,?,QjQ(,F?>v>>"	 * MN_M``bc  L188:5BC]CF+]C [[)*+002"
 ZZ))+112Z[
XXbjjnd+ %8O$7q1BJJ;N$7O/>OPRPZPZPaPaPcOde 
 	kkmFAsww}-H +.m=P*Q*Qw777#s* *Q  
 !nnX.G'#*;;y#9!(W!5")++h"7)05F)G'/!ggh/'*ww/@'A%(WWZ%8+
  
   *3 $8 4#%* \"9~&!*

 
	
c (( 	OC/!8MNN	O R E  	kC:TUaTbbefgeh8ijj	k& @ D P s_   ;R  R+5R+5R0R5 	S2SS#"S# S( R(R##R(5	S>SS)rw   )__name__
__module____qualname__r   r*   rB   r   r3   r>   	DataFramerK   r   r]   r   rg   r    r   r   r$   r$   !   s    0} 0BC BC B C \^\h\h *U U U( SV [^ae[e  F
s F
r   r$   )r.   r   r   pandasr>   r   fastapir   app.api.vessel.servicer   app.api.company.servicer   r1   r/   r0   OFFLINE_DASHBOARD_FILEr   r   r"   r$   r   r   r   <module>r      sj    	 	    ! 0 2
j2JK c c 3 3 
M
 M
r   