o h'@sddlmZmZmZmZmZddlmZddlZddl Z ddl Z e e Zede ZdZejddgded d Zejd dgded d ZejddgdeddZdS)) Blueprint send_filejsonify current_apprequest) jwt_requiredNexcelz rolecall.xlsxz/api/download-excelGET)methodsc s6ztjd}tj|t}td|td|tdtj|tj|s@t ddtd||dd fWSzt j |d d gd }fd d|D}fdd|D}|svt ddt j |t j ddfWWS|}ddg}|D]}||j vrt j||ddjd||<q|d}t|} tdtd| dt|d|rtd|t dt| |d WWSty} z#td!td"| t d#d$td%t| d&d'fWYd(} ~ WSd(} ~ wwty} ztd)| t d*t| d&d'fWYd(} ~ Sd(} ~ ww)+zBConvert Excel file to JSON with headers as keys and rows as values UPLOAD_FOLDERzLooking for Excel file at: zUploads folder: z File exists: File not foundz Excel file "z" not found at path: )errormessageuploads_folderopenpyxlengine)zPayroll Numberz Employee NamezLicense Numberz License Code Start DateExpiry/Update DateStatecsg|] }|jvr|qScolumns.0coldfr$/var/www/html/routes/excel_routes.py 6z"download_excel..csg|] }|jvr|qSrrrrrrr 7r!zNo required columns foundzINone of the required columns found in the Excel file. Available columns: )r rrequired_columnsavailable_columnsrrcoerce)errorsz%Y-%m-%drecordszSuccessfully read Excel file: z, z rows, z columnszMissing columns in Excel file: T)successfilename total_rowsdatazError reading Excel file : zExcel reading failedzCould not read Excel file "": r rNzExcel processing error: zExcel processing failed)rconfigospathjoinEXCEL_FILENAMEloggerinfoexistsrpd read_excellistr to_datetimedtstrftimeto_dictlenwarning Exceptionr str) r file_pathr"r#missing_columns filtered_df date_columnsrr+r*errrdownload_excels     " rHz/api/file-data/c Csz1tjtjtjt}tj|d}td|d|tj|s5t dd|ddfWStj||}tj| tj|sSt dd dd fWStj|sgt d d |d ddfWStj |s{t dd|ddd fWStd|tj |d }zu|dvrtj|dd}n|dkrt|}nt dd|ddd fWWS|d}t|}d|vr|ddnd}zt|td|Wnty} ztd|d | WYd!} ~ nd!} ~ wwt d"||||d"d#WWSty2} z#td$|d%| t d&d'|d(t| dd)fWYd!} ~ WSd!} ~ wwtyX} ztd*| t d+t| dd)fWYd!} ~ Sd!} ~ ww),z=Get file data as JSON with headers as keys and rows as valuesresultszLooking for file 'z' in results folder: Results folder not foundResults folder not found at: r.rzInvalid file pathz'File path is outside the results folderr$r zFile "z" not found in results folderz Invalid file"z" is not a filez Found file: )z.xlsxz.xlsrrz.csvzUnsupported file typez File type "zC" is not supported. Only .xlsx, .xls, and .csv files are supported.r'_rUNKNOWNz)Successfully deleted file after reading: zCould not delete file z after reading: NT)r(r) scraper_typer*r+ file_deletedzError reading file r,zFile reading failedzCould not read file "r-r/zGet file data error: zGet file data failed)r1r2dirnameabspath__file__r3r5r6r7r startswithisfilesplitextlowerr8r9read_csvr>r?splitremoverAr@r rB) r) project_rootresults_folderrCfile_extrr+r*rP delete_errorrGrrr get_file_dataks         "  r`z/api/list-resultsc CsHz~tjtjtjt}tj|d}td|tj|s2t dd|gddfWSg}t |D]%}tj||}tj |r^| ds^t |}|||j|j|dq9|jd d d d td t|dt d t|||dWSty}ztd|t dt|ddfWYd}~Sd}~ww)z8List all files in the results folder without downloadingrIzLooking for results folder at: rJrK)r rfilesr.)r)sizemodifiedr2cSs|dS)Nrdr)xrrrszlist_results..T)keyreversezFound z files in results folder)r(countrar]zList results error: zList results failedr.r/N)r1r2rRrSrTr3r5r6r7rlistdirrVrUstatappendst_sizest_mtimesortr?rAr rB)r\r] result_filesfilerC file_statrGrrr list_resultssT   rs)flaskrrrrrflask_jwt_extendedrr1loggingpandasr8 getLogger__name__r5excel_bpr4routerHr`rsrrrrs"    X b