G /iu|ddlmZddlmZddlZddlZddlmZddlm Z ddl m Z ddl m Z ddlmZmZdd lmZmZmZmZmZmZdd lmZdd lmZdd lmZdd lmZddl m!Z!ddl"m#Z#ddl$m%Z%ddl&m'Z'ddl(m)Z)ddl*m+Z+ddl,m-Z-ddl.m/Z/ddl0m1Z1m2Z2m3Z3m4Z4m5Z5m6Z6m7Z7m8Z8m9Z9m:Z:m;Z;ddlm?Z?GddZ@y)) HTTPException)SessionN)datetime)BytesIO)A4)colors)getSampleStyleSheetParagraphStyle)SimpleDocTemplate ParagraphSpacerTable TableStyleImage)inch)TblAdaptingSupplyChain)TblCategoryWishInventory)TblCashConversionCycle)TblShrinkageAreasReasons)TblComparativeAnalysis)TblOnlinePreSelectService)TblSales)TblDigitalAsset)TblRecurringOperating) TblSummary)TblGroup) SupplyChainAdaptationCategoryInventoryCashConversionCycle ShrinkageDataComparativeAnalysisOnlinePreSelectService OnlineSales DigitalAssetRecurringOperatingSummaryModule9ReportPreview)JWTPayloadSchema) SummaryCreatecleZdZdedefdZdedefdZdedefdZ dede fd Z dede fd Z ded e fd Z y )Module9ReportServicedbtokenc ||_||_y)N)r,r-)selfr,r-s F/var/www/html/retail-simulation-api/app/api/module_9_report/service.py__init__zModule9ReportService.__init__+s group_idreturnc@K|j|d{S7w)z Get Module 9 report preview dataN)_fetch_module9_data)r/r3s r0get_report_previewz'Module9ReportService.get_report_preview/s--h7777s cK|s tdd|jjtj |j }|s tddd}d}d }|jjt j |j}|jjtj |j}|jjtj |j }|jjtj |j } |jjtj |j} |jjtj |j} |jjtj |j } |jjtj |j} |jjtj |j}|jjt j#t j$|k(t j&j)dj }g}|D]}d }t+|d rW|j,rKt/|j,t0rd j3|j,}nt5|j,}|j7t9|j:xsd ||||j<||j>||j@|j$xsd g}|D]\}|j7tC|jDxsd ||jF||jH|j$xsd ^d}|r\tK|jLxsd ||jN||jP||jR|j$xsd }d}| r\tU| jVxsd || jX|| jZ|| j\| j$xsd }g}| D]m}|j7t_|j`xsd ||jb||jd||jf|j$xsd og}| D]\}|j7ti|jjxsd ||jl||jn|j$xsd ^d}| rtq| jrxsd || jt|| jv|| jx|| jz|| j||| j~|| j|| j| j$xsd  }g}| D]m}|j7t|jxsd ||j||j||j|j$xsd og}|D]m}|j7t|jxsd ||j||j||j|j$xsd od}|rKt|jxsd ||j&||j|j$xsd }t||xs t9g|xs tCg|||xs t_g|xs tig||xs tg|xs tg|tjj Sw)Nizgroup_id is required) status_codedetail)r3izGroup ID not found in databasec|dvr|SdS)N)NnullzN/Avs r0safe_strz:Module9ReportService._fetch_module9_data..safe_str<s!331 > >r2c0 t|xsdS#YyxYw)Nrg)floatr?s r0 safe_floatz.safe_float?s QV!}$ c0 t|xsdS#YyxYw)Nr)intr?s r0safe_intz:Module9ReportService._fetch_module9_data..safe_intEs 16{" rEr<what_are_some_categoriesz, r) adapting_idrIremarks_about_these_categoriesdo_you_think_you_needat_which_point_and_whyr3)category_wish_id inventory credit_periodr3)cash_idinventory_purchase_timingsales_to_cash_gapcash_flow_challengesr3) shrinkage_id shrinkageshrinkage_do_you_envisionshrinkage_in_your_storesr3)comparative_id attributesown_brand_physical_storeown_brand_online_storer3) online_idtype check_boxr3) sales_idaverage_traffic_expectedaverage_remarknumber_of_transaction_per_month number_remarkaverage_transaction_valuetransaction_remarkcogs cogs_remarkr3) digital_iditem estimate_costremarkr3) recurring_id expenses_typepercentage_of__salesremarksr3) summary_idbackend_submissionsbackend_summariser3) r3supply_chain_adaptationcategory_inventorycash_conversion_cycleshrinkage_datacomparative_analysisonline_pre_select_service online_sales digital_assetrecurring_operatingsummary last_updated)Srr,queryr filter_byfirstrallrrrrrrrrrfilterr3rrisnothasattrrI isinstancelistjoinstrappendrrJrKrLrMrrNrOrPrrQrRrSrTr rUrVrWrXr!rYrZr[r\r"r]r^r_r#r`rarbrcrdrerfrgrhr$rirjrkrlr%rmrnrorpr&rqrsr'pd Timestampnow)r/r3 group_existsrArDrHadapting_supplyrucash_conversionrVrxonline_pre_selectsalesr{r|r}supply_chain_adaptationsrjcategories_strcategory_inventories cash_convrwcomparative_analysesonline_pre_servicesrzdigital_assetsrecurring_operatings summary_datas r0r6z(Module9ReportService._fetch_module9_data3sC8NO Oww}}X.88(8KQQS C8XY Y ?   ''--(>?IIS[I\``b!WW]]+CDNNX`Naeeg''--(>?IIS[I\bbdGGMM":;EExEX^^` #ww}}-CDNNX`Naeeg GGMM*CDNNX`Naeeg h'1181DJJL o6@@(@SWWY "ggmm,ABLLV^L_cce''-- +22   8 +  * * 0 0 6  %'  $& #DNt78T=Z=Zd;;TB%)YYt/L/L%MN%()F)F%GN $ + +,A ,,1)1.)A/78[8[/\&.t/I/I&J'/0K0K'L+! - $$ "&D ' '(9!%!6!6!;!"4>>2&t'9'9:+! ) ' +'//41*2?3\3\*]"*?+L+L"M%-o.R.R%S(116Q I *&338q$Y%8%89*293V3V*W)1)2T2T)U"++0q N "(D ' '(;#227a#DOO4)1$2O2O)P'/0K0K'L+! ) )!%D  & &'=..-Adii("4>>2+! ( & &,1)1%2P2P)Q'(<(<=089^9^0_&u':':;*4U5T5T*U#+E,D,D#E +$U%6%67,1 L!D  ! !,??/adii((););< ,+! # " "'D ' '(:!..3!&t'9'9:%/0I0I%J .+! ) ( ""--2$,W-H-H$I"*7+D+D"E )).Q L$$<$YAVAX@Y3L8I8K7L"+)!5!P:M:O9P&9&W>T>V=W%(<\^,< 4 N9K9M8N ))+  s`<`>datac"t}t|tdtzdtz}g}t }|d}d|_d|_td|dddd} td d d }d |_ |j||jtdd|jtd|d|jtddtd|td|td|td|g} | g} |jD]y} t| jxsd|t| jxsd|t| j xsd|t| j"xsd|g} | j| {t%| d} | j't)ddddt*j,fddddt*j,fddddd d!d"d#d$d%d&g |j| |jtdd|jtd'|d(|jtddtd)|td*|gg}|j.D]W} |jtt1| j2xsd+|tt1| j4xsd+|gYt%|}|j't)ddddt*j,fdd,d-d$d d!d"d#d&g |j||jtdd|jtd.|d(|jtdd|j6rtd/|t|j6j8xsd|gtd0|t|j6j:xsd|gtd1|t|j6j<xsd|gg}nGtd/|td|gtd0|td|gtd1|td|gg}t%|}|j't)ddddt*j,fddddt*j,fd,d2d3d4d5d%d6g |j||jtdd|jtd7|d(|jtdd|j>rtd8|tt1|j>j@xsd+|gtd9|t|j>jBxsd|gtd:|t|j>jDxsd|gg}nGtd8|td|gtd9|td|gtd:|td|gg}t%|}|j't)ddddt*j,fddddt*j,fd,d2d3d4d5d%d6g |j||jtdd|jtd;|d(|jtddtd<|td=|td>|g}|g}|jFD]`} t| jHxsd|t| jJxsd|t| jLxsd|g} |j| bt%|d}|j't)ddddt*j,fddddt*j,fddddd?d@d4d5d$d%g |j||jtdd|jtdA|d(|jtddtdB|tdC|g}|g}|jND]G} t| jPxsd|t| jRxsd|g} |j| It%|d}|j't)ddddt*j,fddddt*j,fddddd?d@d4d5d$d%g |j||jtdd|jtdD|d(|jtdd|jTrtdE|tt1|jTjVxsd+|gtdF|t|jTjXxsd|gtdG|tt1|jTjZxsd+|gtdH|t|jTj\xsd|gtdI|tt1|jTj^xsd+|gtdJ|t|jTj`xsd|gtdK|tt1|jTjbxsd+|gtdL|t|jTjdxsd|gg}ntdE|td|gtdF|td|gtdG|td|gtdH|td|gtdI|td|gtdJ|td|gtdK|td|gtdL|td|gg}t%|}|j't)ddddt*j,fddddt*j,fd,d2d3d4d5d%d6g |j||jtdd|jtdM|d(|jtddtdN|tdO|tdP|g}|g}|jfD]i} t| jhxsd|tt1| jjxsd+|t| jlxsd|g} |j| kt%|d}|j't)ddddt*j,fddddt*j,fddddd?d@d4d5d$d%g |j||jtdd|jtdQ|d(|jtddtdR|tdS|tdT|g}|g} |jnD]i} t| jpxsd|tt1| jrxsd+|t| jtxsd|g} | j| kt%| d}!|!j't)ddddt*j,fddddt*j,fddddd?d@d4d5d$d%g |j|!|jtdd|jtdU|d(|jtdd|jvrstdV|tt1|jvjxxsd|gtdW|tt1|jvjzxsd|gg}"n0tdV|td|gtdW|td|gg}"t%|"}#|#j't)ddddt*j,fddddt*j,fd,d2d3d4d5d%d6g |j|#|jtdd|jtdX|j|jdY|d|j||jd+tjjdZ}$d[|jd\|$d]}%d^|%}&tjtjj|&d_`t|&da5}'|'j|jdbdbdbtjjdc|%S#YxYw#1swY1xYw)dz(Generate PDF report and return file pathg?)pagesize topMargin bottomMarginNormal FlexibleStyleCJK)parentfontSizeleadingwordWrapz TS Logo.pngK)widthheightLEFTz.Module 9: Backend & Supply Chain ReportTitle CategorieszRemarks About CategorieszDo You Think You NeedzAt Which Point And Whyr=) repeatRowsGRIDrrrBOX)FONTNAMErrrzHelvetica-Bold)FONTSIZErrr)rrrr Helvetica)rrrr) LEFTPADDINGrr) RIGHTPADDINGrrr) TOPPADDINGrrr) BOTTOMPADDINGrrr)VALIGNrrMIDDLE)ALIGNrrr)WORDWRAPrrTzCategory InventoryHeading2 Inventoryz Credit Periodr)rrrr)rrrCENTERzCash Conversion Cyclez Inventory Purchase TimingzSales To Cash GapzCash Flow Challenges)rrr)rrrr)rrr)rrrr)rrrTOPzShrinkage DatazShrinkagez Shrinkage Do You EnvisionzShrinkage In Your StoreszComparative Analysis AttributeszOwn Brand Physical StorezOwn Brand Online Store)rrrr)rrrrzOnline Pre Select ServiceTypez Check Boxz Online SaleszAverage Traffic ExpectedzAverage Remarkz&Number Of Transaction Per MonthzNumber Remarkz Average Transaction ValuezTransaction Remarkz COGSzCOGS Remarkz Digital AssetItemz Estimate CostRemarkzRecurring Operatingz Expenses TypezPercentage Of SalesRemarksr&zBackend SubmissionszBackend SummarisezLast Updated: z%Y-%m-%d %H:%M:%Sz %Y%m%d_%H%M%Smodule9_complete_report_group__z.pdfzuploaded_files/T)exist_okwbNuploaded_files)Mrr rrr rrr rhAlignrr r rtrIrKrLrMrsetStylerrblackrurrOrPrvrRrSrTrwrVrWrXrxrZr[r\ryr^r_rzrarbrcrdrerfrgrhr{rjrkrlr|rnrorpr}rrrsr~strftimebuildseekrrr3osmakedirspathdirnameopenwritegetvaluer)(r/rbufferdocstorystyles normal_styleflexible_stylelogo header_cells table_rowsrjrow supply_tablecat_data cat_table cash_data cash_table shrink_data shrink_table comp_header comp_rows comp_table online_header online_rows online_table sales_data sales_tabledigital_header digital_rows digital_tablerecurring_headerrecurring_rowsrecurring_tabler summary_table timestampfilenamefilepathfs( r0_generate_pdf_reportz)Module9ReportService._generate_pdf_reportss4xVYZ^V^_$&h' ! ! ( F8$4U   c"=D DK LL  LL2 '  YOQWX_Q`ab VAr]# lN 3 0. A -~ > . ?  #^ 00D$77A6>R$==GX$44>O$55?P C   c "1ZA6 j VXsFLL 9 FHa 6 ; , 7 - 0 1 / 2 2 / 0*    \" VAr]#  Y3VJ5GHI VAr]# k> 2 o~ 6  ++D OO#dnn12NC#d005A6G , (O : VXsFLL 9 ; - 1 2 0 1 / 2 0 '    Y VAr]#  Y6z8JKL VAr]#  % %=~NPYZ^ZtZt[O[O[YSY[iQjk5~F RVRlRlR~R~SICIKYIZ[8.I9UYUoUoVEVEVOIOQ_L`aI=~NPYZ`bpPqr5~F RXZhHij8.I9U[]kKlmI 9% J VXsFLL 9 FHa 6 - 0 1 / 2 / / (    Z  VAr]#  Y/ 1CDE VAr]#   -~> #dNaNaNkNkNpopJqtBACD=~NPYZ^ZmZm[H[H[RLRTbQcd &R`@ab=~NPYZ`bpPqrR$55?PC   S ! .93 J VXsFLL 9 FHa 6 ; , 7 - 0 1 / 2 2 / (    Z  VAr]#  Y:F: 2 %o 22D$))-v~>$..2FNCC   s # 3[Q7 j VXsFLL 9 FHa 6 ; , 7 - 0 1 / 2 2 / *    \" VAr]#  Y~vj/ABC VAr]#   BIdN_N_NmNmNwqwzHEIJ=~NPYZ]^b^o^o_J_J_ONO[PR`Qab6GSWSdSdSwSwTB|BDRJST=.99SIZIZI_I_IdcdEegu;vw/@)DL]L]LiLiLsmsvDCEF JBIfVdDef=~NPYZ`bpPqr6GSY[iIjk=.99V^;\]/@)FTbBcd JJ' Z VXsFLL 9 FHa 6 - 0 1 / 2 / / )    [! VAr]#  Yz0BCD VAr]# fn - o~ 6 h /  '' &&D$))-v~>#d005A6G$++/@C    $ 'lq9 z VXsFLL 9 FHa 6 ; , 7 - 0 1 / 2 2 / +   ]# VAr]#  Y4fZ6HIJ VAr]# o~ 6 +^ < i 0  ++,,D$,,6G#d77<1=~N$,,0&.AC  ! !# & - 1=   VXsFLL 9 FHa 6 ; , 7 - 0 1 / 2 2 / - "   _% VAr]#  Yy&*<=> VAr]# <<7H)TWX\XdXdXxXxYC}CUDFTKUV5~F RUVZVbVbVtVtV~x~RBPIQRL 7H)TZ\jJkl5~F RXZhHijL l+ z VXsFLL 9 FHa 6 - 0 1 / 2 / / +   ]# VAr]#  Y0A0A0J0JK^0_/`acijrcstu % ALLN++O< 3DMM?!I;dS$XJ/ BGGOOH-= (D !Q GGFOO% &"ww||,h77g   ^ " !s AAED AE'EAE$E'AE0cK|j|d{}|j|}|j|||S7)w)z3Generate PDF and save to database, return file pathN)r6r_save_file_path_to_db)r/r3 report_data file_paths r0generate_and_save_pdfz*Module9ReportService.generate_and_save_pdflsD 44X>> --k:  ""8Y7?sAA*Ar c d|d}td||jjtj tj |k(j }|rStd||_|jj|jj|tdy tdt|||}|jj||jj|jj|td|jy #t$rF}|jjtd |d d l}|jYd }~y d }~wwxYw) z;Save or update file path in summary table with group_id_modg_m9z6DEBUG: Looking for existing record with group_id_mod: z5DEBUG: Found existing record, updating uploaded_filesz"DEBUG: Updated record successfullyz1DEBUG: No existing record found, creating new one)r3 group_id_modrz+DEBUG: Created new record with summary_id: z$ERROR saving file path to database: rN)printr,rrrrrrcommitrefreshaddrq Exceptionrollback traceback print_exc)r/r3r rexisting_summary new_summaryers r0r z*Module9ReportService._save_file_path_to_dbss: "xj,L J<.Y Z#ww}}Z8?? @W@W[g@ghnnp MO2; /  01:<IK(%!-#,  K(  ,CKDZDZC[\] " GG    8< =     ! !  "sB5D:8BD:: F r2r0r+r+*s7+;889M8l #l :Nl \I8)=I8#I8V CC"c"c"r2r+)Afastapirsqlalchemy.ormrpandasrrriorreportlab.lib.pagesizesr reportlab.librreportlab.lib.stylesr r reportlab.platypusr r r rrrreportlab.lib.unitsr%app.models.main.adapting_supply_chainr'app.models.main.category_wish_inventoryr%app.models.main.cash_conversion_cycler'app.models.main.shrinkage_areas_reasonsr$app.models.main.comparative_analysisr1app.models.main.online_pre_selected_service_levelrapp.models.main.salesr)app.models.main.digital_asset_acquisitionr,app.models.main.recurring_operating_expensesrapp.models.main.summaryrapp.models.main.grouprapp.api.module_9_report.schemarrrr r!r"r#r$r%r&r'app.dependency.authanticationr(app.api.summary.schemar)r+r>r2r0r7sr!" & D]]$HLHLGW*EN.*    ;0e "e "r2