7i"oddlZddlZddlmZddlZddlmZddlmZm Z ddl m Z m Z m Z mZmZddlmZGdd Zy) N) load_workbook)Session)DictAny)Module10RequestModule10Response MetricValueCapExMetricValueOverview)DatabaseServiceceZdZddefdZdedefdZdedefdZ de defd Z d edefd Z d edede fd ZdedefdZdedefdZdedee efdefdZdefdZdedefdZy)Module10ServiceNdbcFd|_||_|rt||_yy)Nzod:\after train\report\retail-simulation-api\excel_project\data\Simulation Test file - clean_complete_model.xlsx) excel_pathrr db_service)selfrs G/var/www/html/retail-simulation-api/app/api/module10_metrics/service.py__init__zModule10Service.__init__ s'M -b1DO requestreturncK |j|}|j||}tdgd||S#t$r2}t dt ||j |cYd}~Sd}~wwxYww)z>z?[ ##L1 ,@CCLQLr input_namecd i}|dk(rt|j|j|j|d<t|jr|jdznd|jr|jdznd|jr|jdznd|d<|S|d k(rlt|j|j|j|d <t|j d z|j d z|j d z|d<|S|dk(r1t|j|j|j|d<|S|dk(rit|j|j|j|d<t|jdz|jdz|jdz|d<|S|dk(rit|j|j|j|d<t|jdz|jdz|jdz|d<|S|dk(rit|j|j|j|d<t|jdz|jdz|jdz|d<|S|dk(rit|j|j|j|d <t|jd!z|jd!z|jd!z|d"<|S|d#k(r1t|j|j|j|d$<|S|d%k(r1t|j|j|j|d&<|S|d'k(r1t|j|j|j|d(<|S|d)k(r1t|j|j|j|d*<|S|d+k(r1t|j|j|j|d,<|S|d-k(r1t|j|j|j|d.<|S|d/k(rQt |j r |j nd01|d2<|j rt |j dz1|d3<|S|d4k(rit|j|j|j|d5<t|jdz|jd6z|jd7z|d8<|S|d9k(rit|j|j|j|d:<t|jdz|jdz|jdz|d;<|S|dz|d?<|S|j d@dAjdBz}t|j|j|j||<|S)CzCDerive business metrics from specific input field based on UI namesinvestment_and_capital_expensesY1_Y2_increaseY3_Y4_increaseY5_Y6_increasez$Investment & Capital Expenses Growthg333333?Ng?gffffff?z Capital ROIpre_operating_expenseszPre-Operating Expenses Impactg?g333333?g?zStartup Cost Efficiencyrental_advance_depositzRental Advance (Deposit) ImpactrentzRent Cost Impactg?gffffff?g333333?zOccupancy Cost Ratio inventoryzInventory Growthg?zInventory Turnover Ratiobills_per_monthzBills per Month Growthg?zCustomer Traffic Growthaverage_price_per_itemzAverage Price per Item Growthg?zRevenue per Transaction other_incomezOther Income Growth utilitieszUtilities Cost Impactcorporate_employee_salaryz Corporate Employee Salary Impactconcept_employee_salaryzConcept Employee Salary Impactstore_level_employee_salaryz"Store Level Employee Salary Impactlogistics_and_transportationz!Logistics and Transportation Costcap_ex_refurbishY7_Y8_increasezCap-Ex Refurbish ImpactzAsset Refurbishment ROI&online_investment_and_capital_expensesz$Online Investment & Capital Expensesg?g333333?zDigital Platform ROInumber_of_transactionszNumber of Transactions GrowthzOnline Customer Acquisitionaverage_transaction_valuez Average Transaction Value Growthg?zOnline Revenue per Customerr/ z Impact)r r0Y3_Y4Y5_Y6r Y7_Y8replacetitle)rr?r=metrics metric_names rr7z*Module10Service._derive_metrics_from_input?s : :>I.44.44.44?G: ; &1>M>S>S44s:Y]>M>S>S44s:Y]>M>S>S44s:Y]&GM "RG3 37B.44.44.448G3 4 2= / 5 55; / 5 55; / 5 55;2G- .zo3 39D.44.44.44:G5 6la6 !*5.44.44.44+G& ' /:.44s:.44s:.44s:/G* +TI; &*5.44.44.44+G& ' 3>.44s:.44s:.44s:3G. /|q, ,0;.44.44.441G, - 2=.44s:.44s:.44s:2G- .dY3 37B.44.44.448G3 4 2=.44s:.44s:.44s:2G- .LA> )-8.44.44.44.G) *~s; &/:.44.44.440G+ ,pe6 6:E.44.44.44;G6 7bW4 48C.44.44.449G4 5TI8 8612b[C C>I.44.44.44?G: ; /:.44s:.44s:.44s:/G* +NC3 37B.44.44.448G3 4 6A.44s:.44s:.44s:6G1 26+6 6:E.44.44.44;G6 7 6A.44s:.44s:.44s:6G1 2%,,S#6<<>JK#..44.44.44$GK rr:c"dtdddiS)zCCalculate default business metrics when no specific inputs providedzBusiness Performance$@ @@rB)r )rr:s rr9z3Module10Service._calculate_default_business_metricss! #K#""%  rr+c g}|jD] \}}t|dr9t|ds-|j}|s.|dkDs4|j|d|dLt |dd}t |dd}t |dd} |r|d kDr|j|d |d n|r|dkr|j|d |d |r|r||kDr|j|d n |r|r||dzkr|j|d| s| dkDs|j|d| dt fdt D} tt||t| tt|dddddS)zGGenerate dynamic overview based on actual inputs and calculated metricsrUrCz(: Significant refurbishment investment (z % in Y7-Y8)rrDrEz: Strong early growth (z % in Y1-Y2)z: Initial decline (z: Accelerating growth trendg?z: Growth slowing in mid-term z: Sustained long-term growth (z%)c3\K|]#}|jdst|d%yw)r/Nr)r2r3).0r;rs r z=Module10Service._generate_dynamic_overview..s3il * 5 5c :wwPZ?[?gls),z(Dynamic calculation based on user inputsz-Initial period with user-defined growth ratesz.Mid-term period with projected business impactz+Long-term period with sustained performance)inputs_providedmetrics_calculated analysis_type Y1_Y2_period Y3_Y4_period Y5_Y6_periodtotal_metrics_analyzed key_insightsperformance_summary) r5r6rUappendr3sumr1r r4r)) rr+rrtr` metric_valuey7_y8y1_y2y3_y4y5_y6 input_counts ` rr&z*Module10Service._generate_dynamic_overviews *6););)= %K|%56w|Ue?f$33UQY '';-7_`e_ffq(rs .>B .>B .>BURZ '';-7NugU`(abuqy '';-7J5'Q\(]^Uuu} '';-7R(STu)< '';-7S(TUURZ '';-7UV[U\\^(_`1*>6ic'lii #&|#4%#&{#3&)#l*;&B BB resultrc  |jjj}d|vr<|d}|jd|dd|dd|jd|dd d d |vr>|d }|d }|d z}|jd|dd|jd|dd dd|vrP|d}|d} |jd| ddd |vr,|d d d z}|dz| z dz} |jd| ddd|vr+|d} | d| dz} | d z} |jd | dd!|jj}d |vrd|vr|d }|d}|j dij dd"d"kDr+|d d z|ddz }t |d#z|d$z|d%z&|d'<|d d z|dz }t |d(z|d z|d)z&|d*<|jjj}d+|d,<d-|d.<d/|d0<t|j|j|tt|||12}|S#t$r#}td3t||cYd4}~Sd4}~wwxYw)5z7Enhance Module 10 results with actual database insights brand_datazBrand Analysis: brand_namez with store_formatsz store formatszTotal Store Area: total_store_size,z sq ft planned sales_datatotal_monthly_sales uRevenue Projection: ₹z,.0fz annual sales targetzCustomer Traffic: rJz bills per monthinvestment_datatotal_capital_investmentuCapital Investment: ₹z total investment plannedg333333?dzProjected ROI: z.1fz% annual return on investmentoperational_data monthly_rentmonthly_utilitiesuOperating Expenses: ₹z annual operational costsrg?g{Gz?gQ?rBzSales per Sq FtrgrhzAsset Turnover Ratioz1Dynamic calculation from actual simulation inputs data_sourcezAll 10 modules data integratedmodules_integratedz/Database-driven with business logic enhancementcalculation_methodrrr z(Error enhancing with database insights: N)r$rtcopyrvr#getr rur r!r"r r4r'r(r))rrrenhanced_insightsrr monthly_sales annual_salesrtotal_investmentroi_percentager monthly_opex annual_opexenhanced_metricssales_per_sqftasset_turnoverenhanced_summaryenhanced_resultr,s r_enhance_with_database_insightsz/Module10Service._enhance_with_database_insightsKsrT  & < < A A C y(&|4 !((+;J|\]^=__m)noy(&|4 *+@ A ,r1 !((+B[\]=^^n)op!I-"+,=">#23M#N !((+BCSTXBYYr)st 9,#,\#:;P#QTV#VL&2T&9=M%MPS%SN%,,~c>RRo-pq"Y.#,-?#@ /?BRSfBgg *R/ !((+B;tBTTm)no &4499; y(->)-K&|4 "+,=">==r2667I1MPQQ%/0E%F%KiXdNefxNy%yN:E'5';'5'<'5'<;$%67",,A!BR!G/ZtJu!u;F#1B#6#1B#6#1B#6< !78 &BBGGI .a ] +5U 1 25f 1 2/}}!'!8!8/!+./?+@!2(8 O# "  ':,%E.Z(N,T#N %    rc4|dk(rgtdddtdddtd d d td ddtd d dtd tddd d}d|ddddg}n=tdddtdddtdddtd d}d|ddg}|jj}|j D]\}}||vs |||<t dgd|t t||t|dddd !S)"z8Get dynamic response with actual data for specific groupg.@g(@rbrBrcrdg@g@g4@g9@g2@rTg>@g6@)rrrrrrrzGroup z* (Test Store A): Strong growth projectionsz)Investment expenses show 15% Y1-Y2 growthz0Online transactions projected 30% initial growthz,Cap-Ex refurbishment planned at 8% for Y7-Y8g@)rrrrz: Standard growth projectionsz3Moderate investment and operational growth expectedrrz,Dynamic calculation from group-specific datazGroup-based performance metricsz#Database-driven with business logic)rrrnrrrr ) r r rr#r5r r r4r))rrdynamic_metricsinsights all_metricsr`rxs rrz/Module10Service._get_dynamic_response_for_groups q=1<#'#'#'2 +6#&#&#&+ $#&#&#& )#'#'#' $/#'#'#'$ %5#&%+6#'#'#'+;"OH "LM;B> H2=#'#&#&2 $#&#&#& )#'#'#& %5#&%!O* "?@EH //1@@ )4):):)< %K/1/; ,*= @A*'*?';% #H #Q%F*O %    r)N)__name__ __module__ __qualname__rrrr r-dictr%r)r7r9r r&r*intrrrrrrrrrr s272 88CS8&/d6tStdtl   . t. o. Zb. `  o  BR  BB@PB V6FVSWX[]`X`SaVfvVpr &6r h] ] @P] rr)pandaspdopenpyxlrossqlalchemy.ormrtypingrrschemarr r r r rr rrrrrs," "^^'j  j  r