
    G	/iOT                     <   d dl mZ d dlmZ d dlmZ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$m%Z% d dl&m'Z' d dl(m)Z) d dl*m+Z+m,Z,m-Z-m.Z.m/Z/m0Z0m1Z1m2Z2 d dl3m4Z4  G d d      Z5y)    )HTTPException)Session)Environment
BaseLoaderN)datetime)BytesIO)A4)colors)getSampleStyleSheetParagraphStyle)SimpleDocTemplate	ParagraphSpacerTable
TableStyleImage)TblCompetitorCategoryAnalyses)TblPreSelectedCategory)TblGrossMarginContribution)%TblMarginandSalesContributionAnalysis)
TblSummarySummaryBase)TblGroup)TblStoreFormat)CompetitorCategoryAnalysisPreSelectedCategoryGrossMarginContribution"MarginAndSalesContributionAnalysisSummaryStoreFormatCategoryRoleManagementModule4ReportPreview)JWTPayloadSchemac                   l    e 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de
fdZy)Module4ReportServicedbtokenc                      || _         || _        y N)r&   r'   )selfr&   r'   s      F/var/www/html/retail-simulation-api/app/api/module_4_report/service.py__init__zModule4ReportService.__init__#   s    
    group_idreturnc                 t	  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                         }t        ||rt        di |j                  n	t               |rt        di |j                  n	t               |r#|D 	cg c]  }	t!        di |	j                   c}	n
t!               gt#               g|r#|D 
cg c]  }
t%        di |
j                   c}
n
t%               g|rt'        di |j                  n	t'               |rt)        di |j                  n	t)               t*        j,                  j/                         j1                         	      S | j                  j                  t2              j	                  |      j                         }|rt        |t        |      t        |      t!        |      gt#        |      gt%        |      gt'        |      t)        |      t*        j,                  j/                         j1                         	      S t        dd      c c}	w c c}
w w)	Ni  zgroup_id is required)status_codedetail)r.   )	r.   competitor_analysispre_selected_categoriesstore_formatscategory_role_managementgross_margin_contributionsmargin_sales_analysissummarylast_updatedi  zGroup ID not found in database )r   r&   queryr   	filter_byfirstr   r   r   r   r   allr"   r   __dict__r   r    r!   r   r   r   pd	Timestampnowto_pydatetimer   )r*   r.   module_data_existsr3   pre_selectedr5   gross_marginsmargin_salesr9   sfgmgroup_existss               r+   _fetch_module4_dataz(Module4ReportService._fetch_module4_data'   s    C8NOO GGMM78BBHBU[[] KGGMM01;;X;NTTVKGGMM45???RXXZK GGMM?@JJT\J]cceK GGMM*%///BHHJ 	 "&''--0M"N"X"Xbj"X"k"q"q"s77==)?@JJT\J]cceL GGMM.9CCXCVZZ\M GGMM*DEOOYaObffhM77==)NOYYckYlrrtLggmmJ/9989LRRTG'!bu$>$^ATA]A]$^  |V  |XXd(;(Tl>S>S(Tj}jVcMRMb{9R[[9MRjujwix*@*B)Co|]j+k]jWY,C,Rbkk,R]j+k  D[  D]  C^eq&H&a<K`K`&a  xZ  x\7>3'"2"23GI\\--/==?
 
  77==2<<h<OUUWL+%(BH(U,?,R#.#A"B.Dh.W-X0GQY0Z/[*LV^*_#X6!#!1!1!3!A!A!C
 
 $<\]]5 S+ks   LR8R.$R8R3ER8c                 @   K   | j                  |       d {   S 7 wr)   )rL   )r*   r.   s     r+   get_report_previewz'Module4ReportService.get_report_preview]   s     --h7777s   datac                 t   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|             ddgd|j                  j                  xs dgdt        |j                  j                  xs d      gd|j                  j                  xs dgd|j                  j                  xs dgd |j                  j                   xs dgd!|j                  j"                  xs dgd"|j                  j$                  xs dgd#|j                  j&                  xs dgg	}	t)        |	      }
|
j+                  t-        d$d%d&d't.        j0                  fd(d)d*d+d,d-d.d/d0g
             |j                  |
       |j                  t        dd             |j                  t        d1|             ddgd2t        |j2                  j4                  xs d      gd3t        |j2                  j6                  xs d      gd4t        |j2                  j8                  xs d      gg}t)        |d5d6g7      }|j+                  t-        d$d%d&d't.        j0                  fd(d)d*d8d9g             |j                  |       |j                  t        dd             |j                  t        d:|             g d;}|g}|j:                  D ]@  }|j                  |j<                  xs d|j>                  xs d|j@                  xs dg       B t)        |      }|j+                  t-        d$d%d&d't.        j0                  fd(d)d*d8d9g             |j                  |       |j                  t        dd             |d<   }d|_!        d	|_"        |j                  t        d=|d                |j                  t        dd	             t        d>|      t        d?|      t        d@|      t        dA|      g}|g}|jF                  D ]  }t        |jH                  xs d|      t        |jJ                  xs d|      t        t        |jL                  xs d      |      t        t        |jN                  xs d      |      g}|j                  |        g dB}t)        |dC      }|j+                  t-        d$d%d&d't.        j0                  fd(dDdEdFd8d9dGdHd*d+g             |j                  |       |j                  t        dd             |d<   }dI|_!        dJ|_"        |j                  t        dK|d                |j                  t        dd	             t        dL|      t        dM|      t        dN|      g}|g}|jP                  D ]r  }t        t        |jR                  xs d      |      t        t        |jT                  xs d      |      t        |jV                  xs d|      g}|j                  |       t g dO}t)        |dC      }|j+                  t-        d$d%d&d't.        j0                  fdPd%d&dt.        j0                  fd(dQdEdRd8d9dGdHd*d+g             |j                  |       |j                  t        dd             |d<   }d|_!        d	|_"        |j                  t        dS|d                |j                  t        dd	             t        d|      t        d|      gt        dT|      t        |jX                  jZ                  xs d|      gt        dU|      t        |jX                  j\                  xs d|      gt        dV|      t        |jX                  j^                  xs d|      gg}t)        |dWdXg7      }|j+                  t-        d$d%d&d't.        j0                  fdPd%d&dt.        j0                  fd(dDdEdFdYdZdGdHd*d+g             |j                  |       |j                  t        dd             |d<   }d|_!        d	|_"        |j                  t        d[|d                |j                  t        dd	             t        d\|      t        d]|      gt        d^|      t        t        |j`                  jb                  xs d_      |      gt        d`|      t        t        |j`                  jd                  xs d_      |      gt        da|      t        t        |j`                  jf                  xs d_      |      gt        db|      t        t        |j`                  jh                  xs d_      |      gt        dc|      t        t        |j`                  jj                  xs d_      |      gt        dd|      t        t        |j`                  jl                  xs d_      |      gt        de|      t        t        |j`                  jn                  xs d_      |      gt        df|      t        t        |j`                  jp                  xs d_      |      gt        dg|      t        t        |j`                  jr                  xs d_      |      gt        dh|      t        t        |j`                  jt                  xs d_      |      gt        di|      t        t        |j`                  jv                  xs d_      |      gt        dj|      t        t        |j`                  jx                  xs d_      |      gt        dk|      t        t        |j`                  jz                  xs d_      |      gt        dl|      t        t        |j`                  j|                  xs d_      |      gt        dm|      t        t        |j`                  j~                  xs d_      |      gt        dn|      t        t        |j`                  j                  xs d_      |      gt        do|      t        t        |j`                  j                  xs d_      |      gt        dp|      t        t        |j`                  j                  xs d_      |      gg}t)        |dqdrg7      }|j+                  t-        d$d%d&d't.        j0                  fdPd%d&dt.        j0                  fd(dDdEdFdYdZdsdtd*d+g             |j                  |       |j                  t        dd             |j                  t        dd	             |j                  t        du|j                  j                  dv       |d<                |j                  |       |j                  d       t        j                         j                  dw      }dx|j                   dy| dz} d{|  }!t        j                  t        j                  j                  |!      d|}       t        |!d~      5 }"|"j                  |j                                ddd       t        j                  j                  d|       S #  Y =xY w# 1 sw Y   1xY w)z5Generate PDF report from Module4 data in table format   )pagesize
leftMarginrightMargin	topMarginbottomMarginzTS Logo.png   K   )widthheightLEFT   
   TitleHeading1      Helvetica-Bold)parentfontSize
spaceAfter	alignmentfontNamez$Module 4: Category Management Report   SectionHeading2      )rc   rd   re   rg   zCompetitor AnalysisFieldValuezReference CompetitorzN/AzNumber of Categories Stockedr   zKey Categories StockedzStocking ObservationszCompetitor Others RemarkzResearch MethodologyzShopping BehaviorzConsumer Others RemarkGRIDr   r   rr   g      ?)FONTNAMErp   rr   r   rb   )FONTSIZErp   rq   rl   )VALIGNrp   rq   TOP)ALIGNrp   rq   r[   )WORDWRAPrp   rq   T)LEFTPADDINGrp   rq      )RIGHTPADDINGrp   rq   r{   )
TOPPADDINGrp   rq   r{   )BOTTOMPADDINGrp   rq   r{   zPre Selected Categoriesz	Low ValuezMedium Valuez
High Valued   ic  )	colWidths)rz   rp   rq      )r|   rp   rq   r   zStore Formats)z
Store NamezStore Format TypeMerchandiseNormalzCategory Role ManagementzCategory NamezCategory RolezSales<br/>ContributionzMargin<br/>Contribution)   r   x   r   )
repeatRows)ru   rp   rt   rl   )rs   r   r\   rq   	Helvetica)ru   r   rq   rl   )r}   rp   rq      )r~   rp   rq   r      	   zGross Margin ContributionszContribution<br/>to Total Salesz Contribution<br/>to Gross Margin	Rationale)r   r   i@  BOX)ru   rp   rt   r   )ru   r   rq   r   zMargin Sales Analysisz(Which category contributes more to salesz0Which category contributes more to gross marginsz$Which category represents your store   i^  )rz   rp   rq      )r|   rp   rq   r   r   z<b>Field</b>z<b>Value</b>zStore SubmissionsnullzStore SummarisezLocation SubmissionszLocation SummarisezNetwork SubmissionszNetwork SummarisezCategory SubmissionszCategory SummarisezPromotions SubmissionszPromotions SummarisezMarketing SubmissionszMarketing SummarisezService SubmissionszService SummarisezBackend SubmissionszBackend SummarisezMetrics SubmissionszMetrics Summarise   iT  )r}   rp   rq   r   )r~   rp   rq   r   zLast Updated: z%Y-%m-%d %H:%M:%Sz%Y%m%d_%H%M%Smodule4_complete_report_group__z.pdfzuploaded_files/T)exist_okwbNuploaded_files)Rr   r   r	   r   r   hAlignappendr   r   r   r3   reference_competitorstrnumber_of_categories_stockedkey_categories_stockedstocking_observationscompetitor_others_remarkresearch_methodologyshopping_behaviorconsumer_others_remarkr   setStyler   r
   blackr4   	low_valuemedium_value
high_valuer5   
store_namestore_format_typemerchandiserd   leadingr6   category_namecategory_rolesales_contributionmargin_contributionr7   contribution_to_total_salescontribution_to_gross_margin	rationaler8   5which_category_do_you_think_contributes_more_to_sales=which_category_do_you_think_contributes_more_to_gross_margins1which_category_do_you_think_represents_your_storer9   store_submissionsstore_summariselocation_submissionslocation_summarisenetwork_submissionsnetwork_summarisecategory_submissionscategory_summarisepromotions_submissionspromotions_summarisemarketing_submissionsmarketing_summariseservice_submissionsservice_summarisebackend_submissionsbackend_summarisemetrics_submissionsmetrics_summariser:   strftimebuildseekr   rC   r.   osmakedirspathdirnameopenwritegetvaluejoin)#r*   rO   bufferdocstylesstorylogotitle_stylesection_style	comp_data
comp_tablepre_data	pre_tablestore_headers
store_datastorestore_tablenormal_styleheader_cells
table_rowsrolerowcolumn_widths
role_tableheadersrJ   margin_tableanalysis_dataanalysis_tablesummary_datasummary_table	timestampfilenamefilepathfs#                                      r+   _generate_pdf_reportz)Module4ReportService._generate_pdf_report`   s   PR^`oqr$&	c"=D DKLLLL2'
 %WVJ5GRTacop  |L  MYE{STVAr]# 'y
9KVXef  rB  C 	Y4mDEg#T%=%=%R%R%[V[\+S1I1I1f1f1kjk-lm%t'?'?'V'V'_Z_`$d&>&>&T&T&]X]^')A)A)Z)Z)c^cd#T%=%=%R%R%[V[\ $":":"L"L"UPUV%t'?'?'V'V'_Z_`

	 9%
JVXsFLL9;-//001/2(
  	 	Z VAr]# 	Y8-HIg#d::DDIJKS!=!=!J!J!OaPQ3t;;FFK!LM	
 (sCj9	:VXsFLL9;-/01'
  	 	YVAr]# 	Y>?J#_
''E  )E''05!!*U  ( J'ZVXsFLL9;-/01)
  	 	[!VAr]# h' !!Y96*;MNOVAr]# o|4o|4.=/>	
 #^
11D$,,5|D$,,5|D#d55:;\J#d66;!<lK	C c" 2 -:!4
JVXsFLL9;,7-01/2//(
  	 	Z VAr]# h' ! Y;VJ=OPQVAr]# 7F8,Gk<0
 Y
11B#b<<ABLQ#b==BC\R",,/%>C
 c" 2 (ZA6jVXsFLL9FHa6;,7-01/2//*
  	 	\"VAr]# h' !!Y6z8JKLVAr]# w-y,/OPA<PR[\`\v\v  ]m  ]m  ]v  qv  xD  SE  FI<XZcdhd~d~  e}  e}  eF  AF  HT  [U  V=|LiX\XrXr  Ye  Ye  Yn  in  p|  O}  ~	
 }c
C
VXsFLL9FHa6;,7-01/2//,
 ! 	 	^$VAr]# h' !!Yy&*<=>VAr]# ~|4iP\6]^*L99SIgIgIqkqEr  uA  <B  C(,73t||GcGcGmgmCnp|9}~-|<iDLLLmLmLwqwHx  {G  ?H  I+\:Ic$,,JiJiJsmsFt  wC  =D  E,l;Ys4<<KkKkKuouGv  yE  >F  G*L99SIgIgIqkqEr  uA  <B  C-|<iDLLLmLmLwqwHx  {G  ?H  I+\:Ic$,,JiJiJsmsFt  wC  =D  E/>	#dllNqNqN{u{J|  K  AL  M-|<iDLLLmLmLwqwHx  {G  ?H  I.=yT\\MoMoMysyIz  }I  @J  K,l;Ys4<<KkKkKuouGv  yE  >F  G,l;Ys4<<KkKkKuouGv  yE  >F  G*L99SIgIgIqkqEr  uA  <B  C,l;Ys4<<KkKkKuouGv  yE  >F  G*L99SIgIgIqkqEr  uA  <B  C,l;Ys4<<KkKkKuouGv  yE  >F  G*L99SIgIgIqkqEr  uA  <B  C'
. lsCjA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7e	\ "!s   Az&  z.&z+.z7c                    K   | j                  |       d{   }| j                  |      }| j                  ||       |S 7 )w)z3Generate PDF and save to database, return file pathN)rL   r   _save_file_path_to_db)r*   r.   report_data	file_paths       r+   generate_and_save_pdfz*Module4ReportService.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_m4z6DEBUG: 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)r.   group_id_modr   z+DEBUG: Created new record with summary_id: z$ERROR saving file path to database: r   N)printr&   r<   r   filterr   r>   r   commitrefreshadd
summary_id	Exceptionrollback	traceback	print_exc)r*   r.   r   r   existing_summarynew_summaryer  s           r+   r   z*Module4ReportService._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	N)__name__
__module____qualname__r   r#   r,   intr"   rL   rN   r   r   r   r   r;   r-   r+   r%   r%   "   s    7 +; 4^# 4^:N 4^l8 89M 8_8)= _8# _8B	C C "c "c "r-   r%   )6fastapir   sqlalchemy.ormr   jinja2r   r   pandasrA   r   r   ior   reportlab.lib.pagesizesr	   reportlab.libr
   reportlab.lib.stylesr   r   reportlab.platypusr   r   r   r   r   r   ,app.models.main.competitor_category_analysesr   %app.models.main.pre_selected_caregoryr   *app.models.main.gross_margin_contributionsr   6app.models.main.margin_and_sales_contribution_analysisr   app.models.main.summaryr   r   app.models.main.groupr   app.models.main.store_formater   app.api.module_4_report.schemar   r   r   r   r   r    r!   r"   app.dependency.authanticationr#   r%   r;   r-   r+   <module>r     s^    ! " *  	   &   D ] ] V H Q h ; * 8	 	 	 ;B" B"r-   