
    G	/iΉ                     d   d dl mZ d dlmZmZmZmZ d dlmZ d dl	m	Z	 d dl
Z
d dlZd dlZd dlmZ d dlmZmZmZmZmZ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  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/m0Z0 ddl1m2Z2m3Z3m4Z4m5Z5m6Z6 d dl7m8Z8 d dl9m:Z:  G d d      Z;y)    )Session)DictListAnyOptional)BytesIO)datetimeN)A4)SimpleDocTemplate	ParagraphSpacerTable
TableStyle	PageBreakImage)getSampleStyleSheetParagraphStyle)inch)colors)TblExpandedPerformance)
TblSummary)TblGroup)TblSalesEstimate)TblStoreFormat)TblNetworkPlanning)TblOperatingExpenses)
TblUtility)TblStoreLevelStaffNeeds   )Module10MetricsDataModule10ReportRequestModule10ReportResponseModule10ReportPreviewExpandedPerformanceMetrics)JWTPayloadSchema)SummaryCreatec                       e Zd ZddedefdZd ZdedefdZ	dede
fd	Zdedefd
ZdedefdZdedefdZdefdZdedefdZdedefdZdedefdZdedefdZy)Module10ReportServiceNdbtokenc                 ^    || _         || _        t               | _        | j	                          y N)r)   r*   r   styles_setup_custom_styles)selfr)   r*   s      G/var/www/html/retail-simulation-api/app/api/module_10_report/service.py__init__zModule10ReportService.__init__    s&    
)+!!#    c                     t        d| j                  d   dddd      | _        t        d| j                  d	   d
dd
d      | _        t        d| j                  d   dd      | _        y)z)Setup custom paragraph styles for reportsCustomTitleHeading1      r   Helvetica-Bold)parentfontSize
spaceAfter	alignmentfontNameSectionHeaderHeading2      )r9   r:   r;   spaceBeforer=   Normal	   	Helvetica)r9   r:   r=   N)r   r-   title_stylesection_stylenormal_styler/   s    r0   r.   z*Module10ReportService._setup_custom_styles&   sx    );;z*%
 ,;;z*%
 +;;x( 	
r2   group_idreturnc                 `  K   |st        d      | j                  j                  t              j	                  |      j                         }|st        d      | j                  |      }| j                  j                  t              j	                  |      j                         }|rd|D cg c]X  }t        |j                  xs d|j                  xs d|j                  xs d|j                  xs d|j                  xs d      Z c}nt        dddd|      g}| j                  j                  t              j	                  |      j                         }|r|j                   nd|r|j"                  ndd}t%        ||||t&        j(                  j+                               S c c}w w)	z"Fetch Module 10 data from databasezgroup_id is requiredrJ   zGroup ID not found in databaser   zN/A)expanded_idname_of_kpirational_for_selectionexpected_outputrJ   )metrics_submissionsmetrics_summarise)rJ   metrics_dataexpanded_performance_metricssummarylast_updated)
ValueErrorr)   queryr   	filter_byfirst_calculate_metrics_datar   allr$   rN   rO   rP   rQ   rJ   r   rR   rS   r#   pd	Timestampnow)	r/   rJ   group_existsrT   expanded_metricsmetricrU   rV   summary_datas	            r0   _fetch_module10_dataz*Module10ReportService._fetch_module10_dataA   s    344 ww}}X.88(8KQQS=>> 33H=  77==)?@JJT\J]aac  -(
 -& '"..3!"..7%'-'D'D'M & 6 6 ?%-A -(
 %?#(!%
 $ 	%" ''--
+55x5HNNPBI7#>#>u>E!:!:5

 %%)E ))+
 	
/(
s   B,F..AF)B#F.c           	      n   | j                   j                  t              j                  |      j	                         }| j                   j                  t
              j                  |      j	                         }| j                   j                  t              j                  |      j	                         }| j                   j                  t              j                  |      j	                         }t               }t        |xs
 |xs |xs |      }|sqt        dd      D ]R  }t        |d| d       t        |d| d       t        |d| d       t        |d| d       t        |d	| d
       T d
|_        d
|_        |S d|dz  z   }	|D 
cg c]  }
t        |
dd      dv s|
 }}
|D 
cg c]  }
t        |
dd      dv s|
 }}
|D 
cg c]  }
t        |
dd      dv s|
 }}
t        d |D              }t        d |D              }|rt        d |D              t!        |      z  nd
}t        d |D              xs d}|d
kD  r|d
kD  r|d
kD  r|r|r||z  |z  |z  dz  }	nE|r!t        d |D              dz  dz   }|	|z  dz  }	n"|r t        d |D              dz  dz   }|	|z  dz  }	g d}t#        |d      D ]^  \  }}t%        |	|z  d      }t        |d| |       t        |d| t%        |dz  d             t        |d| t%        |dz  d             ` d|d z  d!z  z   }g d"}t#        |d      D ]"  \  }}t        |d| t%        ||z  d             $ |r7|D cg c]  }t        |d#d      d$k(  s| }}|rt        d% |D              nd&}nd&}d'|d(z  dz  z   }t        dd      D ]C  }t        |d| d
      dz  }t%        ||z  d)z  |z  d
      }t        |d	| t'        |             E |rt!        |      n|dz  d*z   |_        |d*z  dz   |_        |S c c}
w c c}
w c c}
w c c}w )+z+Calculate metrics data from database tablesrM   r      sales_yg        gross_margin_yoperating_expenses_y	gmroi_a_ysales_per_sqft_a_yr   g      Y@   	parameter )BILLS_PER_MONTHzNumber of Bills per Month)ITEMS_PER_BILLzNumber of Items per Bill)	AVG_PRICEz"Average Price per item (in Rupees)c              3   >   K   | ]  }t        |d d      xs d  ywvaluer   Ngetattr.0ses     r0   	<genexpr>z@Module10ReportService._calculate_metrics_data.<locals>.<genexpr>   s$     #cKbRGB$;$@q$@Kb   c              3   >   K   | ]  }t        |d d      xs d  ywrt   rv   rx   s     r0   r{   z@Module10ReportService._calculate_metrics_data.<locals>.<genexpr>   s$     "aJ`B72w#:#?a#?J`r|   c              3   >   K   | ]  }t        |d d      xs d  ywrt   rv   rx   s     r0   r{   z@Module10ReportService._calculate_metrics_data.<locals>.<genexpr>   s$      ZHY"Wa!8!=A!=HYr|   c              3   >   K   | ]  }t        |d d      xs d  yw)total_store_count_y1r   Nrv   ry   nps     r0   r{   z@Module10ReportService._calculate_metrics_data.<locals>.<genexpr>   s%     _Q^272'=qAFQFQ^r|   i c              3   >   K   | ]  }t        |d d      xs d  yw)sales_estimate_idr   Nrv   rx   s     r0   r{   z@Module10ReportService._calculate_metrics_data.<locals>.<genexpr>   s%     !eUdr'".A1"E"J"JUdr|   
   g?c              3   >   K   | ]  }t        |d d      xs d  yw)network_plan_idr   Nrv   r   s     r0   r{   z@Module10ReportService._calculate_metrics_data.<locals>.<genexpr>   s%     !aS`R'".?"C"Hq"HS`r|   rA   g333333?)      ?gffffff?gQ?g)\(?gGz?gGz?g)\(@@gףp=
@g\(\@g\(\@g(\@g333333?       @   g?)r   g?g?g?g?r   g@g333333@r   gffffff@g      @g	@store_format_typezStore Format Ac              3   >   K   | ]  }t        |d d      xs d  yw)
store_sizer   Nrv   )ry   sfs     r0   r{   z@Module10ReportService._calculate_metrics_data.<locals>.<genexpr>   s"     Y2WRq9>Q>r|   i  r      im     )r)   rY   r   rZ   r]   r   r   r   r    boolrangesetattrtotal_sessionstotal_metricsrw   sumlen	enumerateroundint)r/   rJ   sales_estimatesstore_formatsnetwork_plansoperating_expensesmetricshas_module_datai
base_salesrz   bills_per_month_recordsitems_per_bill_recordsavg_price_recordstotal_bills_per_monthtotal_items_per_billavg_price_per_itemtotal_storesbase_multiplierplan_multipliergrowth_ratesratesales_value
base_gmroigmroi_growthgrowthr   store_a_formats
total_sqftsqft_multipliersales_per_sqfts                                  r0   r\   z-Module10ReportService._calculate_metrics_datao   s    ''--(89CCXCVZZ\n5???RVVX&89CCXCVZZ\!WW]]+?@JJT\J]aac &' f-f=fTfg1b\71#4>!!5s;#7s!;SA9QC#6#5aS!91= " &'G"$%G!N hm,
 1@  #U"72{\^C_  dT  DT2  #U/>  "R'"k[]B^  cQ  CQ"  "R*9  R/BWRVX=Y  ^Q  >QR/  R ##cKb#c c""aJ`"aa xIS ZHY ZZ]`ar]ss  OP _Q^__dcd !1$)=)AFX[\F\ap  vC/2FFI[[^jjnttJ!!eUd!eehjjmnnO#o5;J!!aS`!aadeehiiO#o5<J _ q1GAt
T 115KGwqc]K8G~aS15t9KQ3OPG3A37{T?QST9UV	 2 HqLC//
S"<3IAvGy_eJ4G.KL 4 ,9vMbWRI\^`=aeu=urMOv]lYYYrvJJ A 44q"A!'WQC=!<vEK"K*$<s$Bo#UWXYNG1!5s>7JK  :I_!5xZ\}`aNa!)A 2m #U "R RH ws0   P#*P#4P(	P(P-(P-(P2>P2c                    K   	 | j                  |       d{   }t               }t        |t        dddd      }g }	 t	        ddd      }d|_        |j                  |       |j                  t        d	d
             |j                  t        d| j                               |j                  t        d	d             |j                  t        d| j                               dt        |j                  j                        gdt        |j                  j                        gdt        |j                  j                        gd|j                  j                   gg}t#        |dt$        z  dt$        z  g      }|j'                  | j)                                |j                  |       |j                  t        d	d             |j                  t        d| j                               |j                  t        d	d             |j                  t        d| j*                               |j                  t        d	d             g d}	t        |j                  j,                        t        |j                  j.                        t        |j                  j0                        t        |j                  j2                        t        |j                  j4                        t        |j                  j6                        g}
t#        |	|
gdt$        z  gdz        }|j'                  | j)                                |j                  |       g d}t        |j                  j8                        t        |j                  j:                        t        |j                  j<                        t        |j                  j>                        t        |j                  j@                        t        |j                  jB                        g}t#        ||gdt$        z  gdz        }|j'                  | j)                                |j                  |       |j                  t        d	d             |j                  t        d| j*                               |j                  t        d	d             g d}t        |j                  jD                        t        |j                  jF                        t        |j                  jH                        t        |j                  jJ                        t        |j                  jL                        t        |j                  jN                        g}t#        ||gdt$        z  gdz        }|j'                  | j)                                |j                  |       g d}t        |j                  jP                        t        |j                  jR                        t        |j                  jT                        t        |j                  jV                        t        |j                  jX                        t        |j                  jZ                        g}t#        ||gdt$        z  gdz        }|j'                  | j)                                |j                  |       |j                  t        d	d             |j                  t        d| j*                               |j                  t        d	d             g d}t        |j                  j\                        t        |j                  j^                        t        |j                  j`                        t        |j                  jb                        t        |j                  jd                        t        |j                  jf                        g}t#        ||gdt$        z  gdz        }|j'                  | j)                                |j                  |       g d}t        |j                  jh                        t        |j                  jj                        t        |j                  jl                        t        |j                  jn                        t        |j                  jp                        t        |j                  jr                        g}t#        ||gdt$        z  gdz        }|j'                  | j)                                |j                  |       |j                  t        d	d             |j                  t        d| j*                               |j                  t        d	d             g d}t        |j                  jt                        t        |j                  jv                        t        |j                  jx                        t        |j                  jz                        t        |j                  j|                        t        |j                  j~                        g}t#        ||gdt$        z  gdz        }|j'                  | j)                                |j                  |       g d}t        |j                  j                        t        |j                  j                        t        |j                  j                        t        |j                  j                        t        |j                  j                        t        |j                  j                        g}t#        ||gdt$        z  gdz        } | j'                  | j)                                |j                  |        |j                  t        d	d             |j                  t        d | j*                               |j                  t        d	d             g d}!t        |j                  j                        t        |j                  j                        t        |j                  j                        t        |j                  j                        t        |j                  j                        t        |j                  j                        g}"t#        |!|"gdt$        z  gdz        }#|#j'                  | j)                                |j                  |#       g d}$t        |j                  j                        t        |j                  j                        t        |j                  j                        t        |j                  j                        t        |j                  j                        t        |j                  j                        g}%t#        |$|%gdt$        z  gdz        }&|&j'                  | j)                                |j                  |&       |j                  t        d	d             |j                  t        d!| j                               g d"g}'|j                  D ]4  }(|'j                  |(j                  |(j                  |(j                  g       6 t#        |'dt$        z  dt$        z  dt$        z  g      })|)j'                  | j)                                |j                  |)       |j                  t        d	d             |j                  t        d#| j                               d$t        |j                  d%         gd&t        |j                  d'         gg}*t#        |*dt$        z  d(t$        z  g      }+|+j'                  | j)                                |j                  |+       |j                  |       t        j                         j                  d)      },d*| d+|, d,}-d-|- }.t        j                  t        j                  j                  |.      d./       t        |.d0      5 }/|/j                  |j                                ddd       | j                  ||.       |.S 7 #  Y xY w# 1 sw Y   (xY w# t        $ r}0t        d1t        |0             d}0~0ww xY ww)2z@Generate PDF from JSON data and save to uploaded_files directoryN2   pagesize
leftMarginrightMargin	topMarginbottomMarginTS Logo.png   K   widthheightLEFTr   r   z(Module 10: Metrics & Performing Indexes r7   Summary StatisticsTotal SessionsTotal MetricsYears CoveredCurrency   	colWidths   zMetrics Data (All Years)   zSales (Rs in Lacs)r@   )Y1Y2Y3Y4Y5Y6g?   )Y7Y8Y9Y10Y11Y12zGross Margin (Rs in Lacs)zOperating Expenses (Rs in Lacs)zGMROI (Store Format A)Sales per Square FeetExpanded Performance Metrics)zKPI NameRationalExpected OutputSummaryzMetrics SubmissionsrR   zMetrics SummariserS   r   %Y%m%d_%H%M%Smodule10_complete_report_group__.pdfzuploaded_files/Texist_okwbzError generating preview PDF: )dre   r   r   r
   r   hAlignappendr   r   rF   rG   strrT   r   r   years_coveredcurrencyr   r   setStyle_get_metrics_table_stylerH   sales_y1sales_y2sales_y3sales_y4sales_y5sales_y6sales_y7sales_y8sales_y9	sales_y10	sales_y11	sales_y12gross_margin_y1gross_margin_y2gross_margin_y3gross_margin_y4gross_margin_y5gross_margin_y6gross_margin_y7gross_margin_y8gross_margin_y9gross_margin_y10gross_margin_y11gross_margin_y12operating_expenses_y1operating_expenses_y2operating_expenses_y3operating_expenses_y4operating_expenses_y5operating_expenses_y6operating_expenses_y7operating_expenses_y8operating_expenses_y9operating_expenses_y10operating_expenses_y11operating_expenses_y12
gmroi_a_y1
gmroi_a_y2
gmroi_a_y3
gmroi_a_y4
gmroi_a_y5
gmroi_a_y6
gmroi_a_y7
gmroi_a_y8
gmroi_a_y9gmroi_a_y10gmroi_a_y11gmroi_a_y12sales_per_sqft_a_y1sales_per_sqft_a_y2sales_per_sqft_a_y3sales_per_sqft_a_y4sales_per_sqft_a_y5sales_per_sqft_a_y6sales_per_sqft_a_y7sales_per_sqft_a_y8sales_per_sqft_a_y9sales_per_sqft_a_y10sales_per_sqft_a_y11sales_per_sqft_a_y12rU   rO   rP   rQ   rV   buildr	   r`   strftimeosmakedirspathdirnameopenwritegetvalue_save_file_path_to_db	Exception)1r/   rJ   report_databufferdocstorylogord   summary_tablesales_headerssales_values1sales_table1sales_headers2sales_values2sales_table2
gm_headers
gm_values1	gm_table1gm_headers2
gm_values2	gm_table2
oe_headers
oe_values1	oe_table1oe_headers2
oe_values2	oe_table2gmroi_headersgmroi_values1gmroi_table1gmroi_headers2gmroi_values2gmroi_table2spf_headersspf_values1
spf_table1spf_headers2spf_values2
spf_table2epm_datarc   	epm_tablesummary_infosummary_info_table	timestampfilenamefilepathfes1                                                    r0   generate_preview_report_pdfz1Module10ReportService.generate_preview_report_pdf   s2    ]	G $ 9 9( CCK YF#FRBTVbdsuvCE]#bA$T"VAr]+
 LL#MtO_O_`aLL2' LL#79K9KLM!3{'?'?'N'N#OP #k&>&>&L&L"MN #k&>&>&L&L"MN[55>>?	L ",1T61T6:JKM""4#@#@#BCLL'LL2' LL#=t?Q?QRS LL2'LL#79J9JKLLL2'@M !9!9!B!BCSIaIaIjIjEkmpq|  rJ  rJ  rS  rS  nT  VY  Ze  Zr  Zr  Z{  Z{  V|  ~A  BM  BZ  BZ  Bc  Bc  ~d  fi  ju  jB  jB  jK  jK  fL  MM -!?CPTH:XY>ZL!!$"?"?"ABLL&DN !9!9!B!BCSIaIaIjIjEkmpq|  rJ  rJ  rS  rS  nT  VY  Ze  Zr  Zr  Z|  Z|  V}  B  CN  C[  C[  Ce  Ce  f  hk  lw  lD  lD  lN  lN  hO  PM .-!@SQUXJYZN[L!!$"?"?"ABLL&LL2' LL#>@Q@QRSLL2'=Jk66FFG[MeMeMuMuIvx{  }H  }U  }U  }e  }e  yf  hk  lw  lD  lD  lT  lT  hU  WZ  [f  [s  [s  [C  [C  WD  FI  JU  Jb  Jb  Jr  Jr  Fs  tJz:63t8*q.QIt<<>?LL#AKk66FFG[MeMeMuMuIvx{  }H  }U  }U  }e  }e  yf  hk  lw  lD  lD  lU  lU  hV  X[  \g  \t  \t  \E  \E  XF  HK  LW  Ld  Ld  Lu  Lu  Hv  wJ{J7CH:PQ>RIt<<>?LL#LL2' LL#DdFWFWXYLL2'=Jk66LLMsS^SkSk  TB  TB  PC  EH  IT  Ia  Ia  Iw  Iw  Ex  z}  ~I  ~V  ~V  ~l  ~l  zm  or  s~  sK  sK  sa  sa  ob  dg  hs  h@  h@  hV  hV  dW  XJz:63t8*q.QIt<<>?LL#AKk66LLMsS^SkSk  TB  TB  PC  EH  IT  Ia  Ia  Iw  Iw  Ex  z}  ~I  ~V  ~V  ~m  ~m  zn  ps  t  tL  tL  tc  tc  pd  fi  ju  jB  jB  jY  jY  fZ  [J{J7CH:PQ>RIt<<>?LL#LL2' LL#;T=N=NOPLL2'@M !9!9!D!DEs;KcKcKnKnGoqt  vA  vN  vN  vY  vY  rZ  \_  `k  `x  `x  `C  `C  \D  FI  JU  Jb  Jb  Jm  Jm  Fn  ps  t  tL  tL  tW  tW  pX  YM -!?CPTH:XY>ZL!!$"?"?"ABLL&DN !9!9!D!DEs;KcKcKnKnGoqt  vA  vN  vN  vY  vY  rZ  \_  `k  `x  `x  `D  `D  \E  GJ  KV  Kc  Kc  Ko  Ko  Gp  ru  vA  vN  vN  vZ  vZ  r[  \M .-!@SQUXJYZN[L!!$"?"?"ABLL&LL2' LL#:D<M<MNOLL2'>K{77KKLcR]RjRjR~R~N  BE  FQ  F^  F^  Fr  Fr  Bs  ux  yD  yQ  yQ  ye  ye  uf  hk  lw  lD  lD  lX  lX  hY  [^  _j  _w  _w  _K  _K  [L  MK[9c$hZRS^TJ = = ?@LL$BL{77KKLcR]RjRjR~R~N  BE  FQ  F^  F^  Fr  Fr  Bs  ux  yD  yQ  yQ  yf  yf  ug  il  mx  mE  mE  mZ  mZ  i[  ]`  al  ay  ay  aN  aN  ]O  PKk:s4xjSTnUJ = = ?@LL$LL2' LL#A4CUCUVWCDH%BB!3!3V5R5RTZTjTj kl Ch1T61T61T62JKIt<<>?LL#LL2' LL9d.@.@AB&K,?,?@U,V(WX$c+*=*=>Q*R&STL "'|$$?O!P''(E(E(GHLL+,IIe !//@I8
!I;dSH(
3HKK1DA h%)* & &&x:Oq DH &%  	G<SVHEFF	Gs   AA5AA A@6%AA AA@9  }:AA : AA@AA @5AA5@6AA @9A@>@;AA AAA
AAA A	AA2AAA-A-AA2A2AA5c                   K   	 t        |d      }| j                  |      }d| dt        j                         j                  dd  d}t
        j                  j                  dd	|      }t        j                  t
        j                  j                  |      d
       t        |d      5 }|j                  |j                                ddd       | j                  j                  t              j!                  |      j#                         }|r-|j$                  r|xj$                  d| z  c_        nI||_        nAt'        ||      }t        di |j)                         }| j                  j+                  |       | j                  j-                          |S # 1 sw Y   xY w# t.        $ r6}	| j                  j1                          t/        dt3        |	             d}	~	ww xY ww)z$Generate PDF and save to file systemF)rJ   include_chartsmodule_10_report_r   NrA   r   uploadsreportsTr   r   rM   ;)rJ   uploaded_fileszError generating PDF:  )r!   generate_module10_reportuuiduuid4hexr.  r0  joinr/  r1  r2  r3  r4  r)   rY   r   rZ   r[   rk  r&   dictaddcommitr6  rollbackr   )
r/   rJ   requestr8  r`  	file_pathrb  rV   rd   rc  s
             r0   generate_and_save_pdfz+Module10ReportService.generate_and_save_pdfh  s    $	?+XeTG227;F +8*Adjjl6F6Fr6J5K4PHY	8DI KK	2TB i&!)* ' ggmmJ/9989LRRTG))**)o=*-6G*,%#,  %;|'8'8':;G$GGNN' '&*  	?GG4SVH=>>	?sB   G*B'F( + FCF( G*F%!F( (	G'11G""G''G*rv  c                 X   t               }t        |t        dddd      }g }| j                  |j                        }	 t        ddd      }d|_        |j                  |       |j                  t        dd	             |j                  t        d
| j                               |j                  t        dd             |j                  t        d| j                               dt        |j                        gdt        |j                        gdt        |j                        gd|j                   gg}t#        |dt$        z  dt$        z  g      }|j'                  t)        g d             |j                  |       |j                  t        dd             |j                  t        d| j                               |j                  t        dd             |j                  t        d| j*                               |j                  t        dd             g d}	t        |j,                        t        |j.                        t        |j0                        g}
t#        |	|
gdt$        z  gdz        }|j'                  | j3                                |j                  |       |j                  t        dd             |j                  t        d| j*                               |j                  t        dd             g d}t        |j4                        t        |j6                        t        |j8                        g}t#        ||gdt$        z  gdz        }|j'                  | j3                                |j                  |       |j                  t        dd             |j                  t        d| j*                               |j                  t        dd             g d}t        |j:                        t        |j<                        t        |j>                        g}t#        ||gdt$        z  gdz        }|j'                  | j3                                |j                  |       |j                  t        dd             |j                  t        d| j                               |j                  t        d| j*                               |j                  t        dd             g d}t        |j@                        t        |jB                        t        |jD                        g}t#        ||gdt$        z  gdz        }|j'                  | j3                                |j                  |       |j                  t        dd             |j                  tG                      |j                  t        d | j*                               |j                  t        dd             g d}t        |jH                        t        |jJ                        t        |jL                        g}t#        ||gdt$        z  gdz        }|j'                  | j3                                |j                  |       |jO                  |       |jQ                  d!       |S #  Y *xY w)"z,Generate Module 10 KPIs & Metrics PDF reportr   r   r   r   r   r   r   r   r   z1Module 10: Metrics and Performance Indexes Reportr7   r   r   r   r   r   r   r   )ALIGNr   r   r~  CENTERFONTNAMEr|  )r   r~  r8   FONTSIZEr|  r}  rD   GRIDr|  r}  r   blackVALIGNr|  r}  MIDDLEr   zGeneral Metrics (Overall)r   Salesr@   )r   r   r   g      ?r   zGross MarginzOperating Expensesz#Store-Wise Metrics (Store Format A)GMROIr   r   ))r   r   r
   r\   rJ   r   r   r   r   r   rF   rG   r   r   r   r   r   r   r   r   r   rH   r   r   r   r   r   r   r  r  r  r  r  r  r  r   r   r$  r+  r,  seek)r/   rv  r8  r9  r:  rT   r;  rd   r<  r=  sales_valuessales_tablerC  	gm_valuesgm_tablerI  	oe_valuesoe_tablerO  gmroi_valuesgmroi_tablerU  
spf_values	spf_tables                           r0   rm  z.Module10ReportService.generate_module10_report  sn    
  33G4D4DE	c"=D DKLLLL2'
 	YRTXTdTdefVAr]# 	Y3T5G5GHI s<#>#>?@c,"<"<=>c,"<"<=>../	
 lqvqv6FGz +
   	 	]#VAr]# 	Y:D<N<NOPVAr]#Yw(9(9:;VAr]#+L112C8M8M4NPST`TjTjPkl]L9c$hZRS^TT::<=[!VAr]# 	Y~t/@/@ABVAr]#(
556L<X<X8Y[^_k_|_|[}~	*i0SXJNK$779:XVAr]# 	Y3T5F5FGHVAr]#(
;;<c,BdBd>egjkw  lO  lO  hP  Q	*i0SXJNK$779:XVAr]# 	YDdFXFXYZYw(9(9:;VAr]#+L334c,:Q:Q6RTWXdXpXpTqr]L9c$hZRS^TT::<=[!VAr]# 	Y[!Y68I8IJKVAr]#),::;SAaAa=bdght  iJ  iJ  eK  L
;
3DzA~N	488:;Y		%A{	s   AZ$ $Z)c                     t        g d      S )z+Get standard table style for metrics tables)rz  r  r|  r~  r   r8   r  r   r   r}  rE   r  r  r  )r   rI   s    r0   r   z.Module10ReportService._get_metrics_table_style  s     
  	r2   datac                    t               }t        |t        dt        z  dt        z        }g }t	               }|d   }d|_        d|_        	 t        ddd	      }d
|_        |j                  |       |j                  t        dd             |j                  t        d|d                |j                  t        dd             |j                  t        d|d                |j                  t        dd             dt        |j                  j                        gdt        |j                  j                        gdt        |j                  j                         gd|j                  j"                  gg}t%        |ddg      }	|	j'                  t)        ddddt*        j,                  fddddg             |j                  |	       |j                  t        dd             |j                  t        d|d                |j                  t        dd             |j.                  rt        d |      t        d!|      t        d"|      g}
|
g}|j.                  D ]`  }t        |j0                  xs d#|      t        |j2                  xs d#|      t        |j4                  xs d#|      g}|j                  |       b t%        |d$      }|j'                  t)        ddddt*        j,                  fd%dddt*        j,                  fd&d'd(d)d*d+d,d-dd.d/g             |j                  |       n|j                  t        d0|             |j                  t        dd             |j                  t        d1|d                |j                  t        dd             |j6                  r}t        d2|      t        t        |j6                  j9                  d3      xs d#      |      gt        d4|      t        t        |j6                  j9                  d5      xs d#      |      gg}n0t        d2|      t        d#|      gt        d4|      t        d#|      gg}t%        |d6dg      }	|	j'                  t)        ddddt*        j,                  fd%dddt*        j,                  fdd*d+d,d-d.d7d/g
             |j                  |	       |j                  t        dd             |j                  t        d8|j:                  j=                  d9       |d                |j?                  |       |jA                  d:       tC        jD                         j=                  d;      }d<|jF                   d=| d>}	 tH        jJ                  jM                  tH        jJ                  jM                  tH        jJ                  jM                  tH        jJ                  jM                  tH        jJ                  jO                  tP                                      }|r|d?k(  rd@}tH        jJ                  jS                  |dA      }tH        jJ                  jS                  ||      }tI        jT                  |dBC       tW        |dD      5 }|jY                  |j[                                dEdEdE       tH        jJ                  jS                  dA|      S #  Y /xY w#  d@}Y xY w# 1 sw Y   :xY w)Fz(Generate PDF report and return file pathg      ?)r   r   r   rC   rA   r   r   r   r   r   r   r   z'<b>Module 10: KPIs & Metrics Report</b>Titler@   zMetrics Summaryr?   r   r   r   r      i,  r   r  r|  r}  r  )r  r|  r}  rA   rz  r  r   zName of KPIzRational for Selectionr   null)
repeatRowsBOXr  )r  r|  r  rA   r  )r  r  r}  rA   )LEFTPADDINGr|  r}  r   )RIGHTPADDINGr|  r}  r   )
TOPPADDINGr|  r}  r   )BOTTOMPADDINGr|  r}  r   )r{  r|  r}  r   )WORDWRAPr|  r}  Tz.No expanded performance metrics data availabler   z<b>Metrics Submissions</b>rR   z<b>Metrics Summarise</b>rS      )r  r|  r}  TOPzLast Updated: z%Y-%m-%d %H:%M:%Sr   r   module10_kpis_metrics_group_r   r   /z#/var/www/html/retail-simulation-apirk  Tr   r   N).r   r   r
   r   r   r:   leadingr   r   r   r   r   r   rT   r   r   r   r   r   r   r   r   r  rU   rO   rP   rQ   rV   getrW   r-  r,  r  r	   r`   rJ   r.  r0  r1  abspath__file__rq  r/  r2  r3  r4  )r/   r  r8  r9  r:  r-   rH   r;  rd   r<  perf_header	perf_rowsrc   row
perf_tabler_  r`  
script_dir
upload_dirra  rb  s                        r0   _generate_pdf_reportz*Module10ReportService._generate_pdf_report  s(   s4xVYZ^V^_$&h' !!	c"=D DKLLLL2'
 	YH&QX/Z[VAr]# 	Y0&2DEFVAr]# s4#4#4#C#CDEc$"3"3"A"ABCc$"3"3"A"ABC**334	
 lsCjAzVXsFLL9;-12+
   	 	]#VAr]# 	Y=vj?QRSVAr]#,,-62LA+\:K %I;;f00:FLIf;;Ev|Tf44>M
   % < yQ7J
3=!V\\:?0;14536634, !  LL$LL#SUabcVAr]# 	Yy&*<=>VAr]#<<7F	RUVZVbVbVfVfg|V}  WH  BH  SI  KW  IX  Y5|DiPSTXT`T`TdTdexTy  UD  ~D  QE  GS  GT  UL 7F	RXZfHgh5|DiPVXdFefL
 lsCjAzVXsFLL9FHa6-01/2//0+
   	 	]#VAr]# 	Y0A0A0J0JK^0_/`acijrcstu		%A LLN++O<	1$--)DQ	?Y[Y`Y`YhYhiqYrIs9t)uvJs!2B
 WW\\*.>?
77<<
H5
J.(D!QGGFOO%& " ww||,h77{	f	?>J
 "!s&   AZ7 ,B Z? / [7Z<?[[c                    K   | j                  |       d{   }| j                  |      }| j                  ||       |S 7 )w)z3Generate PDF and save to database, return file pathN)re   r  r5  )r/   rJ   r7  rw  s       r0   rx  z+Module10ReportService.generate_and_save_pdf  sE      55h??--k:	""8Y7 @s   AA*Arw  c                    	 d| d}t        d|        | j                  j                  t              j	                  t        j
                  |k(        j                         }|rSt        d       ||_        | j                  j                          | j                  j                  |       t        d       yt        d       t        |||      }| j                  j                  |       | j                  j                          | j                  j                  |       t        d|j                          y# t        $ rF}| j                  j                          t        d	|        d
dl}|j                          Y d}~yd}~ww xY w)z;Save or update file path in summary table with group_id_modg_m10z6DEBUG: 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)rJ   group_id_modrk  z+DEBUG: Created new record with summary_id: z$ERROR saving file path to database: r   N)printr)   rY   r   filterr  r[   rk  rt  refreshrs  
summary_idr6  ru  	traceback	print_exc)r/   rJ   rw  r  existing_summarynew_summaryrc  r  s           r0   r5  z+Module10ReportService._save_file_path_to_db  s:   	"xj-LJ<.YZ#ww}}Z8??
@W@W[g@ghnnpMO2; /  01:<IK(%!-#,
 K( ,CKDZDZC[\] 	"GG8<=!!		"s   B5D: 8BD: :	F	<FF	r8  c                     t        t        j                               }dt        j                         j                  d       d}t        ||t        |j                               t        j                         d      S )z#Create standardized report responsemodule_10_metrics_report_r   r   	completed)	report_id	file_name	file_sizegeneration_timestatus)	r   rn  ro  r	   r`   r-  r"   r   r4  )r/   r8  r  r  s       r0   create_report_responsez,Module10ReportService.create_report_response  sa    

%	/0G0G0X/YY]^	%&//+,$LLN
 	
r2   r,   )__name__
__module____qualname__r   r%   r1   r.   r   r#   re   r    r\   r   rd  rx  r!   r   rm  r   r   r  r5  r"   r  rl  r2   r0   r(   r(      s    $7 $+; $
6,
3 ,
;P ,
\V V8K Vp_G# _G# _GB&?C &?C &?Pu0E u' un	* 	M8)> M83 M8^C C "c "c "<
W 
9O 
r2   r(   )<sqlalchemy.ormr   typingr   r   r   r   ior   r	   rn  r.  pandasr^   reportlab.lib.pagesizesr
   reportlab.platypusr   r   r   r   r   r   r   reportlab.lib.stylesr   r   reportlab.lib.unitsr   reportlab.libr   ,app.models.main.expanded_performance_metricsr   app.models.main.summaryr   app.models.main.groupr   app.models.main.sales_estimater   app.models.main.store_formater   app.models.main.network_planr   "app.models.main.operating_expensesr   app.models.main.utilityr   'app.models.main.store_level_staff_needsr   schemar    r!   r"   r#   r$   app.dependency.authanticationr%   app.api.summary.schemar&   r(   rl  r2   r0   <module>r     sk    " , ,    	  & h h h D $   O . * ; 8 ; C . K J  J : 0s

 s

r2   