
    G	/i^                     h   d dl mZ d dlm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 dlmZ d dlmZmZ d d	lmZmZmZmZ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+m,Z, d dl-m.Z. d dl/m0Z0m1Z1m2Z2m3Z3m4Z4m5Z5m6Z6m7Z7m8Z8m9Z9m:Z: d dl;m<Z<  G d d      Z=y)    )HTTPException)SessionN)datetime)BytesIO)A4)colors)getSampleStyleSheetParagraphStyle)SimpleDocTemplate	ParagraphSpacerTable
TableStyleImage)inch)TblCustomerLocation)TblCatchmentPotential)TblCompetitorIntensity)TblCompetitorBenchmarking)TblLocationSpillageFactor)TblLocationDetails)TblSalesEstimate)TblRentExpenses)
TblSummarySummaryBase)TblGroup)CustomerSegmentEstimateCatchmentPotentialCompetitionIntensityCompetitorBenchmarkingLocationSpillageFactorLocationDetailsSalesEstimateRentExpensesSalesSummaryComparisonModule2SummaryModule2ReportPreview)JWTPayloadSchemac                   ^    e Zd Zd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y)Module2ReportServiceNdbtokenc                      || _         || _        y N)r+   r,   )selfr+   r,   s      ?/var/www/html/retail-simulation-api/app/api/module_2/service.py__init__zModule2ReportService.__init__)   s    
    group_idreturnc                 ~  K   |st        dd      | j                  j                  t              j	                  |      j                         xs | j                  j                  t              j	                  |      j                         xs | j                  j                  t              j	                  |      j                         xs| | j                  j                  t              j	                  |      j                         xs= | j                  j                  t              j	                  |      j                         }|r| j                  j                  t              j	                  |      j                         }| j                  j                  t              j	                  |      j                         }| j                  j                  t              j	                  |      j                         }| j                  j                  t              j	                  |      j                         }| j                  j                  t              j	                  |      j                         }| j                  j                  t              j	                  |      j                         }| j                  j                  t              j	                  |      j                         }	| j                  j                  t              j	                  |      j                         }
| j                  j                  t              j	                  |      j                         }t        ||D cg c]  }t!        di |j"                   c}xs t!               g|D cg c]  }t%        di |j"                   c}xs t%               g|rt'        di |j"                  n	t'               |D cg c]  }t)        di |j"                   c}xs t)               g|D cg c]  }t+        di |j"                   c}xs t+               g|D cg c]  }t-        di |j"                   c}xs t-               g|	D cg c]  }t/        di |j"                   c}xs t/               g|
D cg c]  }t1        di |j"                   c}xs t1               gt3               g|rt5        di |j"                  n	t5               t6        j8                  j;                               S | j                  j                  t<              j	                  |      j                         }|rt        |t!        |      gt%        |      gt'        |      t)        |      gt+        |      gt-        |      gt/        |      gt1        |      gt3        |      gt5        |      t6        j8                  j;                               S t        dd      c c}w c c}w c c}w c c}w c c}w c c}w c c}w w)	Ni  zgroup_id is required)status_codedetail)r3   )r3   customer_segment_estimatescatchment_potentialcompetition_intensitycompetitor_benchmarkinglocation_spillage_factorlocation_detailssales_estimatesrent_expensessales_summary_comparisonsummarylast_updatedi  zGroup ID not found in database )r   r+   queryr   	filter_byfirstr   r   r   r   allr   r   r   r   r'   r   __dict__r   r   r    r!   r"   r#   r$   r%   r&   pd	Timestampnowr   )r/   r3   module_data_existscustomer_recordscatchment_recordscompetition_recordbenchmarking_recordsspillage_recordslocation_recordssales_recordsrent_recordssummary_recordcbslrgroup_existss                     r0   _fetch_module2_dataz(Module2ReportService._fetch_module2_data-   s4    C8NOO GGMM-.88(8KQQS QGGMM/0::H:MSSUQGGMM01;;X;NTTVQ GGMM,-777JPPRQ GGMM*+55x5HNNP 	 #ww}}-@AKKU]K^bbd $.C D N NX` N a e e g!%/E!F!P!PZb!P!c!i!i!k#'77==1J#K#U#U_g#U#h#l#l#n #ww}}-FGQQ[cQdhhj#ww}}-?@JJT\J]aac GGMM*:;EExEX\\^M77==9CCXCVZZ\L!WW]]:6@@(@SYY[N'![k+l[kVW,C,Qajj,Q[k+l  ,L  rI  rK  qLO`$aO`!%7%E!**%EO`$a${fxfze{]o&:&Y=O=X=X&Y  vJ  vLWk(lWkRS)?)M!**)MWk(l  )K  rH  rJ  qKXh)iXhST*@*N1::*NXh)i  *H  oE  oG  nHIY!ZIYA/"?AJJ"?IY!Z!q_n_p^qFS Tm!<!<m T iYfYhXiCOP<a|9ajj9<PdUaUcTd*@*B)CESA)@)@AYgYi\\--/    77==2<<h<OUUWL+%0GQY0Z/[);X)N(O*>*Q-CX-V,W.Dh.W-X&5x&H%I%2H%E$F#/#B"C.Dh.W-X*H=!#!1!1!3   $<\]]E ,m$a(l)i!Z TPs[   M;X==X
X=,X3X=;X$X=*X)X=X.5X=X3$X=7X8E*X=datac                   8 t               }t        |t        dddd      }t               }g }	 t	        ddd      }d|_        |j                  |       |j                  t        dd	             t        d
|d   dddd      }|j                  t        d|             |j                  t        dd             t        d|d   dd	d      }|j                  t        d|             |j                  rt        d|d         t        d|d         t        d|d         t        d|d         t        d|d         t        d|d         t        d|d         t        d|d         t        d |d         g	}	|	g}
|j                  D ]  }|
j                  t        |j                  xs d!      t        |j                  xs d!      t        |j                  xs d!      t        |j                  xs d"      t        |j                   xs d"      t        |j"                  xs d"      t        |j$                  xs d!      t        |j&                  xs d!      t        |j(                  xs d!      g	        t+        |
      }|j-                  t/        d#d$d%d&d'd(d)t0        j2                  fd*d+g             |j                  |       |j                  t        dd,             |j                  t        d-|             |j4                  rg d.}|g}|j4                  D ]s  }|j                  t        |j                  xs d!      t        |j6                  xs d!      t        |j8                  xs d!      t        |j:                  xs d!      g       u t+        |      }|j-                  t/        d#d$d/d&d'd(d)t0        j2                  fd*d+g             |j                  |       |j                  t        dd,             |j                  t        d0|             g d1g}|j                  t        |j<                  j>                  xs d"      t        |j<                  j@                  xs d"      t        |j<                  jB                  xs d"      g       t+        |      }|j-                  t/        d#d$d2d&d'd(d)t0        j2                  fd*d+g             |j                  |       |j                  t        dd,             |jD                  r&|j                  t        d3|             g d4}|g}|jD                  D ]  }|j                  t        |jF                  xs d"      t        |jH                  xs d!      t        |jJ                  xs d!      t        |jL                  xs d!      t        |jN                  xs d"      g        t+        |      }|j-                  t/        d#d$d%d&d'd(d)t0        j2                  fd*d+g             |j                  |       |j                  t        dd,             |j                  t        d5|             |jP                  rd6d7g}|g}|jP                  D ]C  }|j                  t        |jR                  xs d"      t        |jB                  xs d"      g       E t+        |      }|j-                  t/        d#d$d/d&d'd(d)t0        j2                  fd*d+g             |j                  |       |j                  t        dd,             |d   8d88_*        d98_+        8fd:}|jX                  r)|j                  t        d;|             |j                  t        dd	             g d<}|D cg c]  }t        |8       }}|g}|jX                  D ]9  }  || jZ                        }!t        t        | j\                  xs d"      8      t        t        | j^                  xs d"      8      t        t        | j`                  xs d"      8      t        t        | jb                  xs d"      8      t        t        | jd                  xs d"      8      t        t        | jf                  xs d"      8      t        t        | jh                  xs d"      8      |!t        t        | jj                  xs d"      8      g	}"|j                  |"       < g d=}#t+        ||#d>      }$|$j-                  t/        d&d'd(d)t0        j2                  fd?d'd(dt0        j2                  fd$d@dAdBdCdDdEdFd*d#g             |j                  |$       |j                  t        dd,             |jl                  r|j                  t        dG|             g dH}%|%g}&|jl                  D ]s  }'|&j                  t        |'jn                  xs d"      t        |'jp                  xs d!      t        |'jr                  xs d"      t        |'jt                  xs d"      g       u t+        |&      }(|(j-                  t/        d#d$d2d&d'd(d)t0        j2                  fd*d+g             |j                  |(       |j                  t        dd,             |jv                  r>|j                  t        dI|             g dJ})|)g}*|jv                  D ]  }+|*j                  t        |+jx                  xs d!      t        |+jz                  xs d!      t        |+j|                  xs d!      t        |+j~                  xs d!      t        |+jj                  xs d"      t        |+j                  xs d"      g        t+        |*      },|,j-                  t/        d#d$d%d&d'd(d)t0        j2                  fd*d+g             |j                  |,       |j                  t        dd,             |j                  t        dK|             |j                  rg dL}-|-g}.|j                  D ][  }/|.j                  t        |/jj                  xs d"      t        |/j                  xs d!      t        |/j                  xs d!      g       ] t+        |.      }0|0j-                  t/        d#d$d/d&d'd(d)t0        j2                  fd*d+g             |j                  |0       |j                  t        dd,             |d   }1dM|1_*        d	|1_+        |j                  t        dN|             |j                  t        dd	             t        dO|1      t        dP|1      g}t        t        |j                  j                  xs d"      |1      t        t        |j                  j                  xs d"      |1      g}"||"g}2dQdQg}#t+        |2|#d>      }3|3j-                  t/        d&d'd(dRt0        j2                  fd?d'd(dt0        j2                  fd$dSdAdTdUdVdWdXd*d#g             |j                  |3       |j                  t        dd	             |j                  t        dd	             |j                  t        dY|j                  j                  dZ       |d                |j                  |       |j                  d!       t        j                         j                  d[      }4d\|j                   d]|4 d^}5d_|5 }6t        j                  t        j                  j                  |6      d`a       t        |6db      5 }7|7j                  |j                                dcdcdc       t        j                  j                  dd|5      S #  Y xY wc c}w # 1 sw Y   6xY w)ez%Generate PDF report from Module2 data2   )pagesize
leftMarginrightMargin	topMarginbottomMarginzTS Logo.png   K   )widthheightLEFT   
   CustomTitleHeading1      Helvetica-Bold)parentfontSize
spaceAfter	alignmentfontNamezModule 2: Location Analysis   SectionHeaderHeading2   )rq   rr   rs   ru   zCustomer Segment EstimateszISEC<br/>SegmentNormalzSample<br/>SizezAverage<br/>AgezIncome<br/>LevelszOccupation<br/>ModezEducation<br/>Level Modez$Shopping<br/>Frequency<br/>Per Monthz'Household<br/>Consumption<br/>Per Monthz-Total Household<br/>Consumption<br/>Per Monthr   N/A)ALIGNr   r   r   ri   )FONTNAMEr}   r   r   rp   )FONTSIZEr}   r~      GRIDr}   r~   g      ?)VALIGNr}   r~   TOP)WORDWRAPr}   r~   T   zCatchment Potential)zISEC SegmentzNumber of HouseholdszPercentage of SegmentzPotential Number of Households)r   r}   r~      zCompetition Intensity)zUpload ImagezTotal Square FootageAssumptions)r   r}   r~      zCompetitor Benchmarking)zReference CompetitorzBills Per MonthzItems Pre Billz	Avg PricezOther RemarkzLocation Spillage FactorzEstimated Spillage Factorr   r   	   c                     | r| i k(  rt        d      S dj                  d | j                         D              }t        |      S )Nr{   z<br/>c              3   2   K   | ]  \  }}d | d|   yw)z<b>z</b>: NrC   ).0kvs      r0   	<genexpr>zTModule2ReportService._generate_pdf_report.<locals>.format_details.<locals>.<genexpr>  s$     S>RdaQCvaS1>Rs   )r   joinitems)details_dicttxtnormal_styles     r0   format_detailszAModule2ReportService._generate_pdf_report.<locals>.format_details
  sD    <2#5 55,,Sl>P>P>RSSCS,//r2   zLocation Details)	zTrading<br/>RadiuszTrading<br/>RationalAdjacencieszAdjacencies<br/>RationalzLocation<br/>Characteristicsz Corner<br/>Property<br/>RationalRationalDetailszStore<br/>Format<br/>Type)	#   <   A   r   7   r   r   P   (   )	colWidths
repeatRowsBOX)r   r}   r   r   )r   r   rj   r~   	Helvetica)r   r   r~   r   )LEFTPADDINGr}   r~      )RIGHTPADDINGr}   r~   r   )
TOPPADDINGr}   r~      )BOTTOMPADDINGr}   r~   r   zSales Estimates)	ParameterValueRemarkStore Format TypezRent Expenses)z
Store SizezRental Advance AmountzRental Advance PeriodzRent Per Monthr   zRational For Rental ExpenseszSales Summary Comparison)r   zSales PotentialzSales Estimater   SummaryzPromotions<br/>SubmissionszPromotions<br/>Summarise   gffffff?)r   r}   r   r   )r   r   r~   r   )r   r}   r~      )r   r}   r~   r   )r   r}   r~   r   )r   r}   r~   r   zLast Updated: z%Y-%m-%d %H:%M:%Sz%Y%m%d_%H%M%Smodule2_complete_report_group__z.pdfzuploaded_files/T)exist_okwbNuploaded_files)Vr   r   r   r	   r   hAlignappendr   r
   r   r8   strISEC_Segmentsample_sizeaverage_ageincome_levelsoccupation_modeeducation_level_modeshopping_frequency_per_monthhousehold_consumption_per_month%total_household_consumption_per_monthr   setStyler   r   blackr9   number_of_householdspercentage_of_segmentpotential_number_of_householdsr:   upload_imagetotal_square_footageassumptionsr;   reference_competitorbills_per_monthitems_pre_bill	avg_priceother_remarkr<   estimated_spillage_factorrr   leadingr=   detailstrading_radiustrading_rationaladjacenciesadjacencies_rationallocation_characteristicscorner_property_rationalrationalstore_format_typer>   	parametervalueremarkstore_formate_typer?   
store_sizerental_advance_amountrental_advance_periodrent_per_monthrational_for_rental_expensesr@   sales_potentialsales_estimaterA   promotions_submissionspromotions_summariserB   strftimebuildseekr   rK   r3   osmakedirspathdirnameopenwritegetvaluer   )9r/   r]   bufferdocstylesstorylogotitle_stylesection_stylecustomer_headerscustomer_rowscustomercustomer_tablecatchment_headerscatchment_rows	catchmentcatchment_table	comp_data
comp_tablebench_headers
bench_rowsbenchbench_tablespillage_headersspillage_rowsspillagespillage_tabler   headersh
header_rowrowslocdetails_columnrowcolumn_widthslocation_tablesales_headers
sales_rowssalessales_tablerent_headers	rent_rowsrent
rent_tablesales_summary_headerssales_summary_rowsrA   sales_summary_tablesummary_normal_style
table_datasummary_table	timestampfilenamefilepathfr   s9                                                           @r0   _generate_pdf_reportz)Module2ReportService._generate_pdf_reportl   s   PR^`oqr$&	c"=D DKLLLL2'
 %*%%
 	Y<kJKVAr]# '*%%
 	Y;]KL** )*<fX>N OQZ[lntu}n~Q  BK  L]  _e  fn  _o  Bp  r{  |O  QW  X`  Qa  rb  dm  nC  EK  LT  EU  dV  Xa  b|  ~D  EM  ~N  XO  QZ  [A  CI  JR  CS  QT  V_  `I  KQ  RZ  K[  V\  ^g  hW  Y_  `h  Yi  ^j   k-.M ;;$$--23,,12,,12..7%8009E:55>?==BC@@EAFFFK!L
& 
 < #=1N##J3?13=340 %  LL(VAr]# 	Y4mDE## !D/0N!55	%%	..3!4	66;!<	77<1=	@@EAF	'  6 $N3O$$Z3?13=341 &  LL)VAr]# 	Y6FGLM	#d88EENOQTUYUoUo  VE  VE  VN  IN  RO  QT  UY  Uo  Uo  U{  U{  UD  D  QE  F  	G9%
J/;-VXsFLL9/0(
  	 	Z VAr]# ''LL#<mLMvM'J55!!22;e<--23,,12,1-**3e4#  6  
+K  3?13=34- "  LL%LL2' 	Y9=IJ(( ;]K-.M 99$$::CeD,,56&  :
 #=1N##J3?13=340 %  LL(VAr]# h' ! 	0   LL#5}EFLL2'G
 ?FFg)A|4gJF<D,,!/!<c#"4"4"=>Mc#"6"6"?%@,Oc#//":U;\Jc#":":"CeDlSc#">">"G%H,Wc#">">"G%H,Wc#,,"7%8,G"c#"7"7"@5A<P
 C  - AM"4=QON##J3=!V\\:?0;14536330 %  LL(LL2' LL#4mDEQM'J--!!051(q)-.009E:	#  .  
+K  3?13=34- "  LL%LL2' LL?MBC cL%I**  ,1-227a8227a8++0q1..7%899BUC"  + y)J
3?13=34, !  LL$LL2' 	Y9=IJ(($^!"7!888"))11:U;//415..3!4+  9 #((:";((3?13=345 *  LL,-VAr]#  &h/()%')$Yy-89VAr]# 24HI02FG
 c$,,==FGI]^c$,,;;DuEG[\

 s^
c
jMaPzVXsFLL9FHa6;,7-01/2//+
   	 	]#VAr]# 	VAr]#Y0A0A0J0JK^0_/`acijrcstu		%A LLN++O<	3DMM?!I;dS$XJ/
BGGOOH-=(D!QGGFOO%& " ww||,h77K
	~ GD "!s   A} 6} }}}"c                    K   | j                  |       d{   }| j                  |      }| j                  ||       |S 7 )w)z3Generate PDF and save to database, return file pathN)r\   r(  _save_file_path_to_db)r/   r3   report_data	file_paths       r0   generate_and_save_pdfz*Module2ReportService.generate_and_save_pdf  sD      44X>>--k:	""8Y7 ?s   AA*Ar,  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_m2z6DEBUG: 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_modr   z+DEBUG: Created new record with summary_id: z$ERROR saving file path to database: r   N)printr+   rD   r   filterr1  rF   r   commitrefreshadd
summary_id	Exceptionrollback	traceback	print_exc)r/   r3   r,  r1  existing_summarynew_summaryer:  s           r0   r*  z*Module2ReportService._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	r.   )__name__
__module____qualname__r   r(   r1   intr'   r\   r   r(  r-  r*  rC   r2   r0   r*   r*   (   sl    7 +; =^# =^:N =^~R8)= R8# R8h
C C "c "c "r2   r*   )>fastapir   sqlalchemy.ormr   pandasrI   r   r   ior   reportlab.lib.pagesizesr   reportlab.libr   reportlab.lib.stylesr	   r
   reportlab.platypusr   r   r   r   r   r   reportlab.lib.unitsr   !app.models.main.customer_locationr   #app.models.main.catchment_potentialr   $app.models.main.competitor_intensityr   'app.models.main.competitor_benchmarkingr   1app.models.main.location_spillage_factor_analysisr    app.models.main.location_detailsr   app.models.main.sales_estimater   app.models.main.rent_expensesr   app.models.main.summaryr   r   app.models.main.groupr   app.api.module_2.schemar   r   r   r    r!   r"   r#   r$   r%   r&   r'   app.dependency.authanticationr(   r*   rC   r2   r0   <module>rX     sl    ! "  	   &   D ] ] $ A E G M W ? ; 9 ; *    ;{" {"r2   