gh/ddlZddlmZmZddlmZmZmZddl Z ddl m Z ddl Z GddZ e dk(rRe Zedej D]Zej%eed ed ed ed yy) N) load_workbookWorkbook) PatternFillFont Alignment)datetimec"eZdZdZddZddZy)SrikaraConsolidatedReportctdd|_tdd|_t dd|_gd |_y) N1274bdsolid)fgColor fill_typeFFFFFFT)colorboldcenter) horizontalvertical)z SRIKARA MAINzSRIKARA BRANCH 1zSRIKARA BRANCH 2zSRIKARA BRANCH 3zSRIKARA BRANCH 4zSRIKARA BRANCH 5zSRIKARA BRANCH 6)r header_fillr header_fontr alignment locations)selfs SrikaraConsolidatedReport.py__init__z"SrikaraConsolidatedReport.__init__ s7&x7KhT:"hJ Nc 0 |'dtjjdd}tdt jt j j|d}|std|yg}|D]} t|}d|jvr|d}g}g} |jd d d D]} t| } n|jd d D]+} | ds | ddk7s|jt| -|rJtj|| } t j j|| d<|j| |j!|s tdytj$|d } t'}|j(}d|_t| j,} t/| d D]L\}}|j1d ||}|j2|_|j6|_|j:|_Nt/| j=d D]\}\}}t/| d D]\}}||}t?|t@r&|jCdr|j1|||At?|tDtFfrd|vr|j1||d|dt|j1||||jId}| j,Dcgc]}d|vsd|vsd|vs d|vs d|vsd |vs|}}d!| j,vr1| jKd!|jMjO}n 99RWW\\.B`%ab <^.>u.EGGuGEU|4V9KGGU4L?QGRGGuGE F F__Y/ +3*:*:k3h#oQ[_bQbfkorfrv{@CwCGLPSGSWcgjWj3kk )) )'// ;OLPPR^^`  "||Xo-F-J-J-L,MWfg   # #Az? C/778(!< ,OGV??q?GD((DI((DI!^^DN  ,'00@0I0I0KQ&O N "G]a#,_a#@ N (ec5\2w{OOUSWLGYOZOOuOM  N N./!3OA]K>Bo OA6;QO 4q 89 XG#Qw?MMJ#JO=PPQRM OO}O UHLRVJOOO @ E  X   <[MJKk .ykA3?@ Rks2A"U+$U+-A8U+3VV+ V4V  Vc |d|jddd}t}|j}|d|_gd}t |dD]L\}}|j d||}|j |_|j|_ |j|_ Ngd gd gd gd g} t | d D]h\} } t | dD]T\}} t| ttfr%|d kDr |j | || dkDrd| dndA|j | || Vj|jd} gddd|zdddddddddddddd d!d"d#d$d%d&d'dd(d)d*d+dd,d-d.d/d0}t |dD]T\} }| j | d|}| dk(rtd1d23|_ 2|j!d4sDtd15|_ V|j#|t%d6|d7||S)8z=Create a template for location-specific settlement comparisonSrikara_ rqz_Settlement_Template.xlsxz Settlement)DatezPayment Group NamezHIMS Collection AmountzHIMS Transaction CountzBank Gross AmountzBank Net AmountzBank MDRzBank GSTzBank EMIz Paytm AmountzPaytm Net AmountzPaytm Commissionz Paytm GSTzSettlement VarianceRemarksr r*) 2024-01-01CardPdritii,rrrrrr Sample data)rOnline0urrrrrririrrr) 2024-01-02r`xriXiihrrrrrrr)rr@rrrrrri iXrrrr$rr.r0 Instructionsz3SRIKARA SETTLEMENT COMPARISON REPORT - INSTRUCTIONSzThis template is for location: DATA SOURCES:zG1. HIMS Collection Report - Summary of transactions from billing systemz=2. Bank Settlement Report - For Card transactions (Axis Bank)z83. Paytm Settlement Report - For Online/UPI transactionsCOLUMN DESCRIPTIONS:z- Date: Settlement datez-- Payment Group Name: Card, Online, or Othersz9- HIMS Collection Amount: Amount from HIMS billing systemz0- HIMS Transaction Count: Number of transactionsz6- Bank Gross Amount: Total amount from bank settlementz7- Bank Net Amount: Amount after deducting MDR, GST, EMIz2- Bank MDR: Merchant Discount Rate charged by bankz:- Bank GST: GST on MDR (sum of IGST+SGST+CGST if separate)z0- Bank EMI: EMI processing charges if applicablez2- Paytm Amount: Gross amount from Paytm settlementz=- Paytm Net Amount: Amount after deducting commission and GSTz/- Paytm Commission: Commission charged by Paytmz- Paytm GST: GST on commissionzE- Settlement Variance: Difference between HIMS and settlement amounts FORMULAS:zDBank Net Amount = Bank Gross Amount - Bank MDR - Bank GST - Bank EMIz>Paytm Net Amount = Paytm Amount - Paytm Commission - Paytm GSTz9Settlement Variance = HIMS Amount - Settlement Net AmountUSAGE:z91. Replace sample data with actual data from your reportsz%2. Ensure date formats are consistentzT3. Use the SrikaraSettlementComparisonReport.py script to generate automated reportszC4. Use SrikaraConsolidatedReport.py to combine all location reportsT)rsize)rrrrr9zTemplate created for r()replacerrLrMrNrOrrPrrQrrSrVrWrXrrUr`r=)r location_name template_filermrgrirnrorO sample_datarprrr-instructions_ws instructions instructions r!create_location_specific_templatez;SrikaraConsolidatedReport.create_location_specific_templates\  &}'<'-$ . -/$ 0 T1$ 2 3$ 4 5$ 6 S7$ 8 M9$ : H;$ < =$ > ?$ @ HA$ B 4C$ D cE$ F RG$ L%.lA$> , G["''GA['QD!| d4 ''(hi dO  ,   %m_B}oFGr)N)__name__ __module__ __qualname__rr{rrrr r s FPZrr __main__z)Creating templates for all 7 locations...zSetup complete!z-Templates created for all 7 Srikara locationsz1Use these templates to enter your settlement dataz@Then run the compilation script to generate consolidated reports)pandasrFopenpyxlrropenpyxl.stylesrrrr?rr>r r consolidatorr=rlocationrrrrrs,88  ssl z,.L 56 **A66x@A  9: => LMr