o &_hy@sdZddlZddlZddlmZddlmZddlmZm Z m Z ddl m Z ddl Z ddl mZmZddlZddlZddlmZdd lmZdd lmZdd lmZdd lmZdd lmZddlm Z m!Z!m"Z"ddl#m$Z$ddl%Z&eGdddZ'eGdddZ(eGdddZ)GdddZ*GdddZ+GdddZ,GdddZ-GdddZ.Gd d!d!Z/d"d#Z0Gd$d%d%Z1d&d'Z2e3d(kre2dSdS))zu NT License Checker ================================================================================================ N)Path) dataclass)ListDictOptional)datetime)ThreadPoolExecutor as_completed webdriverOptionsBy)Keys WebDriverWaitexpected_conditions)TimeoutExceptionWebDriverExceptionNoSuchElementException)fuzzc@sreZdZUdZdZeed<dZeed<dZ eed<dZ eed <d Z e ed <d Z eed <dZeed<dZeed<dS) SimpleConfigzSimple configuration-https://licensingnt.nt.gov.au/PublicRegister/ website_urlUname_similarity_requiredmax_retry_attemptsrequest_timeout_secondsg333333?delay_between_requestsparallel_browsersF test_mode test_record_limitN)__name__ __module__ __qualname____doc__rstr__annotations__rintrr!r"floatr$r%boolr'r1r1/var/www/html/scrapers/ntc.pyr#s        rc@sLeZdZUdZeed<eed<eed<dZeed<ddZd e fd d Z d S) EmployeeRecordz)Holds one employee's information from CSVpayroll_number employee_namelicense_numberrcsv_row_numbercCs4t|j|_t|j|_t|j|_dS)z)Remove extra spaces and clean up the dataN)r,r4stripr5r6selfr1r1r2 clean_dataJszEmployeeRecord.clean_datareturncCst|jo|jS)z9Check if this record has the minimum required information)r0r6r5r9r1r1r2has_required_dataPz EmployeeRecord.has_required_dataN) r(r)r*r+r,r-r7r.r;r0r=r1r1r1r2r3Bs  r3c@seZdZUdZdZeed<dZeed<dZeed<dZ eed<dZ eed <d Z e ed <d Z eed <dZeed<defddZdS) SearchResultz(Holds the result of searching NT website Not Found found_name license_typelicense_status No License name_matches error_messagerhow_many_retriessearch_time_secondsF was_cachedr<cCs|jdko|j S)z-Did we successfully find license information?r@)rArGr9r1r1r2 is_successfulaszSearchResult.is_successfulN)r(r)r*r+rAr,r-rBrCrErGrHr.rJr/rKr0rLr1r1r1r2r?Us         r?c@sNeZdZdZdefddZddZdejfdd Z d d Z d d Z ddZ dS)OptimizedBrowserManagerz6Manages multiple Chrome browsers for reliable scrapingconfigcCs*||_t|_g|_d|_t|_dS)NF) rNqueueQueue browser_pool all_browserssetup_complete threadingLocklock)r:rNr1r1r2__init__ms  z OptimizedBrowserManager.__init__cCs|jrdS|jI|jr WddStd|jjdt|jjD]}|}|j||j |td|ddq%d|_tdWddS1sTwYdS) z.Create Chrome browsers for parallel processingNz Setting up z Chrome browsers...z Browser z readyTz+All browsers ready for parallel processing!) rSrVprintrNr$range_create_chrome_browserrRappendrQput)r:ibrowserr1r1r2setup_browsersts   "z&OptimizedBrowserManager.setup_browsersr<cCst}|d|d|d|d|d|d|d|d|d |d |d |d |d |d|d|dtj|dS)z/Create one Chrome browser with optimal settings --headless --no-sandbox--disable-dev-shm-usagez --disable-gpuz--disable-extensionsz--disable-loggingz--disable-web-securityz--window-size=1280,720z%--disable-background-timer-throttlingz --disable-renderer-backgroundingz(--disable-backgrounding-occluded-windowsz!--disable-ipc-flooding-protectionz--disable-features=TranslateUIz--disable-default-appsz--no-first-runz--memory-pressure-offoptions)r add_argumentr Chrome)r:chrome_optionsr1r1r2r[s$                 z.OptimizedBrowserManager._create_chrome_browsercCs.|js||j}t||jj}||fS)z)Get a browser from the pool (thread-safe))rSr`rQgetrrNr!)r:r_ wait_helperr1r1r2 get_browsers  z#OptimizedBrowserManager.get_browsercCs|j|dS)z Return browser to pool when doneN)rQr])r:r_r1r1r2return_browserr>z&OptimizedBrowserManager.return_browsercCsNd}|jD]}z|Wq|d7}Yq|dkr%td|ddSdS)z$Close all browsers when program endsrrXz WARNING: z" browsers failed to close properlyN)rRquitrY)r:failed_cleanupsr_r1r1r2cleanup_all_browserss   z,OptimizedBrowserManager.cleanup_all_browsersN) r(r)r*r+rrWr`r rgr[rkrlror1r1r1r2rMjs rMc@seZdZdZdedefddZdedefdd Zdedefd d Z d ed edefddZ de de fddZ de de fddZdejdedede fddZdS)SeleniumLicenseSearcherz.Searches NT government website using Selenium rNbrowser_managercCs&||_||_i|_t|_i|_dSN)rNrq search_cacherTrU cache_lock name_cache)r:rNrqr1r1r2rWs   z SeleniumLicenseSearcher.__init__namer<cCs~|sdS||jvr|j|S||}|}t|dkr2|ddd|dd}n|}||j|<|S)zEConvert name to standard format for comparison with enhanced cleaningrFr,  N)ru_clean_name_for_matchingr8uppersplitlenjoin)r:rv cleaned_nameparts normalizedr1r1r2normalize_employee_names    "  z/SeleniumLicenseSearcher.normalize_employee_namecCs|sdSt|}d|}|}g}|D] }t|dkr*t|dkr*q|dr6t|dkr6q||qg}|D]}d|vrQ|d}||q@||q@dd|D}|red|S|S) z>Clean name by removing common variations and formatting issuesrFryrX.r#-cSsg|]}|r|qSr1)r8).0wordr1r1r2 szDSeleniumLicenseSearcher._clean_name_for_matching..) r,r8r~r|r{r}endswithr\extend)r:rvcleanedwordsfiltered_wordsrprocessed_words hyphen_partsr1r1r2rzs(      z0SeleniumLicenseSearcher._clean_name_for_matchingcsv_name website_namec Cs|dkrdS|r |s dS||}||}||}||}||kr&dS||kr0dSt||t||t||t||g}t|}||j j kr^dSd|ddS)z.z" ERROR: Missing required columns: rxz Your CSV file MUST have these exact column names: - Payroll Number - Employee Name - License Number Current columns in your file: z rrrrr4r5r6r7z!TEST MODE: Processing only first  recordsz2ERROR: No valid employee records found in CSV filerrXzNOTE: Removed z duplicate license numberszERROR reading CSV file: N)pdread_csvr,rr8replace ValueErrorr~listiterrowsr3r;r=r\r%r}r'rYsetr6addr) rrNrequired_columnsmissing_columns employeesindexrowrunique_employees seen_licensesduplicates_removedrr1rr2load_employee_datasX       z#SimpleCSVHandler.load_employee_dataN) r(r)r*r+ staticmethodr,rrr3rr1r1r1r2r rc@steZdZdZdefddZddZdedefd d Z dd e d ede fddZ de de fddZ de de fddZdS)SimpleProgressTrackerz1Shows nice progress bar and processing statisticstotal_employeescCs<||_d|_d|_d|_d|_t|_d|_t |_ dS)Nr) r completed successfulfailedcachedrr last_updaterTrUrV)r:rr1r1r2rWs zSimpleProgressTracker.__init__c Csdtddtd|jdtdtddddd dd ddd d dd tddS)zShow the processing header U=====================================================================================z NT LICENSE CHECKER - PROCESSING z EMPLOYEESProgress<15ryEmployee<25LicenseStatus<12SpeedzU-------------------------------------------------------------------------------------N)rYrr9r1r1r2 show_headers  ,z!SimpleProgressTracker.show_headerrrc Csl|j-|jd7_|jr|jd7_n|r"|jd7_n|jd7_Wdn1s3wYt}||jdkrEdS||_|j|j d}| |}t ||j d}|j|d}t |jdkrt|jddd n|j}t |jd kr|jdd d n|j} |jrd } n |rd } nd} td|d|dd| dd| dd|dd ddddS)zUpdate progress displayrXNrdg{Gz?<z..r CachedSuccessFailed ryrrr.0fz/minrFT)endflush)rVrrKrrLrrrrr_make_progress_barrrr}r5r6rY) r:rr current_timepercent progress_barelapsedspeed display_namedisplay_licensestatusr1r1r2update_progresssN  . z%SimpleProgressTracker.update_progressrwidthr<cCs8t||d}d|d||}d|d|ddS)zCreate ASCII progress barrXr[z] z5.1f%r.)r:rr filledbarr1r1r2r4sz(SimpleProgressTracker._make_progress_bar elapsed_timeexcel_file_pathcCstddtdtdtd|jtd|jtd|jtd|jtd|jt|jd d d d td ||td|j|dd dtd|tddS)zShow final processing summaryz rzPROCESSING COMPLETED!zTotal employees processed: zSuccessful searches: zFailed searches: zCached results: zSuccess rate: rXrrrz Total time: zProcessing speed: rz employees/minutezExcel report saved: N)rYrrrrr _format_time)r:rrr1r1r2show_final_summary:s $z(SimpleProgressTracker.show_final_summarysecondscCsp|dkr |ddS|dkr"t|d}t|d}|d|dSt|d}t|dd}|d|dS) z"Convert seconds to readable formatrrz secondsizm szh mr)r:rminutessecshoursr1r1r2rIs    z"SimpleProgressTracker._format_timeN)r )r(r)r*r+r.rWrr3r?r r/r,rrrr1r1r1r2rs ,rc@s.eZdZdZedeededefddZdS)SimpleExcelGeneratorz'Creates Excel reports with color codingresultsoriginal_file_pathr<c Csz1t|}td}|j|jd|d}g}|D]J}|dd}|dd}|dd} |dd} |d kp_|d kp_|d kp_|d p_|d kp_|d kp_| d kp_| d kp_| d kp_| d k} | rg||q|sd d dt |ddddd} | g}|j|jd|d}t |} t j |dd}t |dksdt |dddvrdnd}| j|d|d|j}|j|}|dd d!dd"d#dd$}t| jD]5\}}|d|||tt t || js| |t j nd}tt|d%d&d'}||||q|dd(|ddt | t | jd|ddWd)n 1s)wYt |WStyI}z td*t |d)}~ww)+zDCreate Excel report - EXCEPTIONS ONLY (problems that need attention)z %Y%m%d_%H%M%S _Exceptions_z.xlsx Name MatchrFNT Register Name License Typerr@rDErrorrzN/Az"ALL RECORDS PROCESSED SUCCESSFULLYrz No exceptionsz All foundz All validrrz Rolecall Namer#r"r$r_NT_ALL_SUCCESS_ xlsxwriter)enginerXz ALL RECORDSrr ExceptionszProcessing SummaryF)r sheet_nameTz#8C1E31whitecentervcenter)boldbg_color font_colorborderalignvalign text_wrapr# 2r NzERROR creating Excel report: )rrnowstrftimeparentstemri startswithr\r}r DataFrame ExcelWriterr,to_excelbooksheets add_format enumeraterwriteremptyastypemin set_columnset_row autofilter freeze_panesrrY)rr  original_path timestamp excel_pathexception_resultsr name_matchnt_register_namerBr  is_exceptionsummary_resultrwriterr+workbook worksheet header_colorcol_numheader max_length col_widthrr1r1r2create_excel_report^s                ,z(SimpleExcelGenerator.create_excel_reportN) r(r)r*r+rrrr,r\r1r1r1r2r[rrc@steZdZdZddZddZddZdefd d Zd e de fd d Z de e de efddZdedefddZdS)SeleniumNTLicenseCheckerz'Main application class - using reliablecCst|_t|j|_d|_dSrr)rrNrMrqprogress_trackerr9r1r1r2rWs  z!SeleniumNTLicenseChecker.__init__c Cs4zz?||}t||j}|t|s%tdWW|j dS| |}t ||}t |jj}|j||Wn;tyMtdYn7ty{}z#tdt|tdtdtdtdtd WYd}~nd}~wwW|j dSW|j dSW|j dS|j w) z0Main function - this is where everything happenszProcess cancelled by userNz* Process stopped by user (Ctrl+C pressed)z ERROR: z TROUBLESHOOTING TIPS:zj1. Make sure your CSV file has the exact column names: 'Payroll Number', 'Employee Name', 'License Number'z)2. Check that Chrome browser is installedz)3. Make sure you have internet connectionz<4. Try running as administrator if you get permission errors) _show_welcome_get_csv_file_from_userrrrN_ask_user_to_continuer}rYrqro_process_all_employeesrr\rr^rrKeyboardInterruptrr,)r: csv_file_pathrrrNrrr1r1r2runs>     zSeleniumNTLicenseChecker.runcCsdS)zShow welcome messageNr1r9r1r1r2r_sz&SeleniumNTLicenseChecker._show_welcomer<cCsttjdkrtjdd}td|ntdtdtdtdtdtd d}|s7szCSeleniumNTLicenseChecker._process_all_employees..r&zERROR processing z: r%)rGN)rr}r^rprNrqrr$r rr4r6r5rArErBrCr\r rrr"rrYr,r?) r:r all_resultsfuture_to_employeefuturer search_result result_dictr error_resulterror_search_resultr1rsr2rb)sT     99z/SeleniumNTLicenseChecker._process_all_employeesrcCsg}|jr |d|jdkr|d|jdd|jdkr(|d|j|jr5|d|jn|d|rAd |Sd S) z-Create informative notes for the Excel reportz!Cached result (duplicate license)rz Search time: z.2frz Retries: zError: zSelenium scrapingz | zProcessed successfully)rKr\rJrHrGr~)r:rnotesr1r1r2_create_notes_for_resultos    z1SeleniumNTLicenseChecker._create_notes_for_resultN)r(r)r*r+rWrer_r,r`r.r0rarr3rrbr?rr1r1r1r2r]s(Fr]c Cs8zkddlm}ddlm}ddlm}ddlm}ddlm }|}| d| d| d |j |d }||d }z*| d | ||jd f}|r[tdW|WdStdW|WdS|wty} z$tdt| tdtdtdtdtdWYd} ~ dSd} ~ ww)Nrr r rrrrarbrcrdr rrz Selenium connection test PASSED!Tz8License input field not found - website may have changedFz!Selenium connection test failed: z Common fixes:z 1. Install Google Chrome browserz"2. Update Chrome to latest version3. Run as administratorz4. Check internet connection)seleniumr !selenium.webdriver.chrome.optionsr selenium.webdriver.common.byrselenium.webdriver.support.uirselenium.webdriver.supportrrfrgrirrrrYrmrr,) r r rrrrhdriverwaitrrr1r1r2test_selenium_connectionsD           rc@seZdZdZddZddZdedefdd Zd e e de e fd d Z d e e de fddZ de e de e fddZddZdS)NTLicenseCheckerAPIz>API class for NT License Checker that can be called from FlaskcCst|_d|_d|_dSrr)rrNrqrur9r1r1r2rWs zNTLicenseCheckerAPI.__init__cCs0|jdus |jjst|j|_|jdSdS)z/Ensure browser manager is initialized and readyN)rqrSrMrNr`r9r1r1r2_ensure_browser_managers z+NTLicenseCheckerAPI._ensure_browser_managerrdr<c Csbz|t||j}t|j|j|_||}|WSty0}z tdt |d}~ww)z;Process CSV file and return results as JSON-compatible listzFailed to process CSV file: N) rrrrNrprqru_process_employees_for_apirr,)r:rdrrrr1r1r2process_csv_files z$NTLicenseCheckerAPI.process_csv_filercCsg}|D]Z}z-|j|}|j|j|j|j|j|j|j|j |j |j |j d }| |t|jjWqty^}z|j|j|jddddddt||j d }| |WYd}~qd}~ww|S)z4Process employees and return JSON-compatible results) r4r6r5rQrPrBrCrKrJrGr7r%FrIN)rurr4r6r5rArErBrCrKrJrGr7r\rrrNr"rr,)r:rrrrzr{rr|r1r1r2rsF   z.NTLicenseCheckerAPI._process_employees_for_apircCszt|}tdd|D}tdd|D}tdd|D}tdd|D}||||||dkr9||ddSddS) zCalculate processing statisticscss,|]}|ddkr|dsdVqdSrQr@rGrXNrirrr1r1r2 *z;NTLicenseCheckerAPI.get_processing_stats..css,|]}|ddks|drdVqdSrrrr1r1r2rrcss |] }|ddrdVqdS)rKFrXNrrr1r1r2rscss|] }|drdVqdS)rGrXNrrr1r1r2rsrr) total_recordssuccessful_searchesfailed_searchescached_resultserrors success_rate)r}sum)r:rtotalrrrrr1r1r2get_processing_statssz(NTLicenseCheckerAPI.get_processing_stats records_datac CszJ|g}t|D]/\}}tt|ddt|ddt|dd|dd}|||q t|j |j |_ | |}|WSt y_}z t dt|d}~ww) zBProcess list of records and return results as list of dictionariesrrFrrrXrzFailed to process records: N)rrCr3r,rir8r;r\rprNrqrurr)r:rrr^ record_datarrrr1r1r2process_records%s&  z#NTLicenseCheckerAPI.process_recordscCs$|jr|jd|_d|_dSdS)zsR          XeMhmG2,