
    ghhC                         d dl Zd dlmZmZ d dlmZmZmZ d dl	m
Z
 d dlZd dlmZmZ d dlZ G d d      Zedk(  r. e       Zd	Zd
ZdZ	 ej-                  eee      Z ede        yy# e$ rZ ede         ed       Y dZ[ydZ[ww xY w)    N)load_workbookWorkbook)PatternFillFont	Alignment)dataframe_to_rows)datetimedatec                   >    e Zd Zd Zd Zd Zd Zd Zd Zd Z	d
d	Z
y)SrikaraSettlementComparisonc                     t        dd      | _        t        dd      | _        t	        dd      | _        d	d
dddddd| _        y )N1274bdsolid)fgColor	fill_typeFFFFFFT)colorboldcenter)
horizontalverticalSRIKARA MAINSRIKARA BRANCH 1SRIKARA BRANCH 2SRIKARA BRANCH 3SRIKARA BRANCH 4SRIKARA BRANCH 5SRIKARA BRANCH 6)r   r   r   r   r   r   r   )r   header_fillr   header_fontr   	alignmentlocation_mapping)selfs    $SrikaraSettlementComparisonReport.py__init__z$SrikaraSettlementComparison.__init__
   sO    &x7KhT:"hJ + 2 2 2 2 2 2!
    c                 (   	 t        j                  |      }|j                  g d      j                  dd|j                  v rdndd      j                         }|S # t        $ r,}t        d|        t        j                         cY d}~S d}~ww xY w)	z;Load HIMS collection report and group by payment group name)Payment Group NameDateLocationsumTransaction Countcountsize)Amountr,   z&Error loading HIMS collection report: N)	pd
read_excelgroupbyaggcolumnsreset_index	Exceptionprint	DataFrame)r#   	file_pathhims_dfhims_summaryes        r$   load_hims_collection_reportz7SrikaraSettlementComparison.load_hims_collection_report   s    	"mmI.G #??+UVZZ0Cw0VW\b\  {} 
   	":1#>?<<>!	"s   AA 	B%!BBBc                 n   	 t        j                  |      }d|j                  vrBg d}|D cg c]  }||j                  v s| }}|r||   j                  d      |d<   nd|d<   |d   j	                  d      |d   j	                  d      z
  |d   j	                  d      z
  |d   j	                  d      z
  |d	<   |j                  d
dg      j                  dddddd      j                         }d|d<   |S c c}w # t        $ r,}t        d|        t        j                         cY d}~S d}~ww xY w)z6Load Axis bank settlement report for Card transactionsGST)IGSTSGSTCGST   )axisr   Gross AmountMDREMI
Net Amountr)   r*   r+   )rE   rH   rF   r?   rG   CardPayment Typez+Error loading Axis bank settlement report: N)r0   r1   r4   r+   fillnar2   r3   r5   r6   r7   r8   )r#   r9   axis_dfgst_colscolexisting_gst_colsaxis_summaryr<   s           r$   load_axis_bank_settlementz5SrikaraSettlementComparison.load_axis_bank_settlement,   sZ   $	"mmI.G
 GOO+34<$WSw@VS$W!$W$%,->%?%C%C%C%KGEN%&GEN &-^%<%C%CA%F#*5>#8#8#;&<#*5>#8#8#;&< $+5>#8#8#;&<GL! #??FJ+?@DD %#F  {}  ,2L(/ %X2  	"?sCD<<>!	"s5   +C? C:C:B4C? :C? ?	D4!D/)D4/D4c                    	 t        j                  |      }|d   j                  d      |d   j                  d      z
  |d   j                  d      z
  |d<   |j                  ddg      j	                  ddddd	      j                         }d
|d<   |S # t        $ r,}t        d|        t        j                         cY d}~S d}~ww xY w)z8Load Paytm settlement report for Online/UPI transactionsr/   r   
Commissionr?   rH   r)   r*   r+   )r/   rH   rS   r?   OnlinerJ   z'Error loading Paytm settlement report: N)	r0   r1   rK   r2   r3   r5   r6   r7   r8   )r#   r9   paytm_dfpaytm_summaryr<   s        r$   load_paytm_settlementz1SrikaraSettlementComparison.load_paytm_settlementT   s    	"}}Y/H
 '/x&8&?&?&B$,\$:$A$A!$D'E$,UO$:$:1$='>H\"
 %,,fj-ABFF##	H 
 {}  -5M.)   	";A3?@<<>!	"s   BB 	C	!C>C	C	c                     	 t        j                  g d      }d|d<   |S # t        $ r,}t        d|        t        j                         cY d}~S d}~ww xY w)zLLoad PhonePe settlement report for Others/PhonePe transactions (placeholder))r)   r*   r/   rH   rS   r?   )r4   zOthers/PhonePerJ   z)Error loading PhonePe settlement report: N)r0   r8   r6   r7   )r#   r9   
phonepe_dfr<   s       r$   load_phonepe_settlementz3SrikaraSettlementComparison.load_phonepe_settlementp   sR    	".opJ)9J~& 	"=aSAB<<>!	"s   ! 	A!AAAc                 d   t        j                  |||gd      }g }| j                  j                         D ]b  }|d   j	                         D ]H  }	||d   |k(  |d   |	k(  z  |d   dk(  z     }
||d   |k(  |d   |	k(  z  |d   dk(  z     }||d   |k(  |d   |	k(  z     }||d   |k(  |d   |	k(  z     }i d|	d|d|
j
                  s|
d	   j                         nd
d|j
                  s|d	   j                         nd
d|
j
                  s|
d	   j                         nd
|j
                  s|d	   j                         nd
z   d|j
                  s|d   j                         nd
d|j
                  s|d   j                         nd
d|j
                  s|d   j                         nd
d|j
                  s|d   j                         nd
d|j
                  s|d   j                         nd
d|j
                  s|d	   j                         nd
d|j
                  s|d   j                         nd
d|j
                  s|d   j                         nd
d|j
                  s|d   j                         nd
d|
j
                  s|
d	   j                         nd
|j
                  s|d   j                         nd
z
  d|j
                  s|d	   j                         nd
|j
                  s|d   j                         nd
z
  d|
j
                  s|
d	   j                         nd
|j
                  s|d	   j                         nd
z   |j
                  s|d   j                         nd
|j
                  s|d   j                         nd
z   z
  }|j                  |       K e t        j                  |      S )z#Create settlement comparison reportT)ignore_indexr)   r*   r(   rI   rT   HIMS_Card_Amountr/   r   HIMS_Online_AmountHIMS_Total_AmountAxis_Gross_AmountrE   Axis_Net_AmountrH   Axis_MDRrF   Axis_GSTr?   Axis_EMIrG   Paytm_AmountPaytm_Net_AmountPaytm_CommissionrS   	Paytm_GSTCard_VarianceOnline_VarianceTotal_Variance)	r0   concatr"   keysuniqueemptyr+   appendr8   )r#   	hims_data	axis_data
paytm_dataphonepe_datareport_datesettlement_combinedcomparison_datalocationdate_val	hims_cardhims_onlineaxis_settlementpaytm_settlementcomparison_rows                  r$   #create_settlement_comparison_reportz?SrikaraSettlementComparison.create_settlement_comparison_report|   sm    !iiJ(M\`a  --224 5	7H%f-446 47%z*h6v&(2434>@	 (z*h6v&(2434@B #,z*h6v&(24#
 $.
+x7'835$ "H"" '	((;(?(?(A^_" )[M^M^+h*?*C*C*Ede	"
 (9??)H*=*A*A*C`aKVK\K\H(=(A(A(Cbc*e" (VeVkVk)H)L)L)Nqr" &RaRgRg|'D'H'H'Jmn" ODYDY 6 : : <_`" ODYDY 6 : : <_`" ODYDY 6 : : <_`" #L\LbLb$4X$>$B$B$Dhi" 'TdTjTj(8(F(J(J(Lpq" 'TdTjTj(8(F(J(J(Lpq"  FVF\F\!1%!8!<!<!>bc"  $yi&9&=&=&?\]O^OdOdOL$A$E$E$Gjk&m!"$ &;K\K\H(=(A(A(CbcRbRhRh&6|&D&H&H&Jno(q%"( %	((;(?(?(A^_ITIZIZk(&;&?&?&A`a(cQ`QfQfol&C&G&G&IlmRbRhRh&6|&D&H&H&Jno&q'r)"4  &&~6i475	7n ||O,,r&   c                 	   t               }| j                  j                         D ]#  }||d   |k(     }|j                  r|j	                  |      }g d}t        |d      D ]L  \  }}	|j                  d||	      }
| j                  |
_        | j                  |
_
        | j                  |
_        N t        |j                         d      D ]  \  }\  }}t        |d      D ]  \  }}	|	j                  dd      j                  d	d	      j                  d
d
      j                  dd      }|j                  |d      }t        |t         t"        f      r|dkD  r|j                  ||d|d       |j                  |||         t%        |      dz   }|j                  |dd       t'        d      |j                  |d      _
        t)        dt%        |      dz         D ]c  }|j                  d|      j*                  }d| d| |dz
   d}|j                  |||       t'        d      |j                  ||      _
        e & |j	                  d      }|j-                  d      j/                  dddddddddddddddd      j1                         }g d}t        |d      D ]L  \  }}	|j                  d||	      }
| j                  |
_        | j                  |
_
        | j                  |
_        N t        |j                         d      D ]  \  }\  }}t        |d      D ]  \  }}	|	j                  dd      j                  d	d	      j                  d
d
      j                  dd      }|j                  |d      }t        |t         t"        f      r|dkD  r|j                  ||d|d       |j                  |||         t%        |      dz   }|j                  |dd       t'        d      |j                  |d      _
        t)        dt%        |      dz         D ]c  }|j                  d|      j*                  }d| d| |dz
   d}|j                  |||       t'        d      |j                  ||      _
        e d|j2                  v r|j5                  |d          |j7                  |       t9        d|        y)z*Create Excel report with proper formattingr*   )title)r)   r*   HIMS Card AmountHIMS Online AmountHIMS Total AmountAxis Gross AmountAxis Net AmountAxis MDRAxis GSTAxis EMIPaytm AmountPaytm Net AmountPaytm Commission	Paytm GSTCard VarianceOnline VarianceTotal VariancerC   )rowcolumnvalue    _HIMS_Axis_Paytm_r   u   ₹z,.2f   TOTALT)r   )r   r   z=SUM(z2:)zConsolidated Reportr)   r+   )r]   r^   r_   r`   ra   rb   rc   rd   re   rf   rg   rh   ri   rj   rk   )r)   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   Sheetz'Settlement comparison report saved to: N)r   r"   rm   ro   create_sheet	enumeratecellr   fillr    fontr!   iterrowsreplaceget
isinstanceintfloatlenr   rangecolumn_letterr2   r3   r5   
sheetnamesremovesaver7   )r#   comparison_dfoutput_filewbrx   location_datawsheaderscol_numheaderr   row_numr   row_datacol_keyr   
totals_row
col_lettertotal_formulaconsolidated_wsconsolidated_dataconsolidated_headerss                         r$   create_excel_reportz/SrikaraSettlementComparison.create_excel_report   s    Z --224 -	OH)-
*Cx*OPM"" x0BG $-Wa#8 0ww1WFwC ,,	 ,,	!%	0 +4M4J4J4La*P J&!X'0!'< JOGV$nnS#6>>wPXXY`bijrrs{  ~F  GG$LL!4E!%#u67Q;GGStCUVGG5IJJ ]+a/JGG
1GG<59t_BGG
1G-2 !CL1$45 OWW7W;II
"'
|2j\*Q,q QJwmL?CJw7<	OS-	O` //0E/F *11&9== %"'!&!&$! % %"$#?
   ;=! 	& 
  ))=qA 	,OGV"''AgV'LD((DI((DI!^^DN		, '00A0J0J0La&P 	S"G]a#,-A1#E S ..c2::7GLTTU\^efnnow  zB  C Wa0ec5\2w{#((WWcRWX\Q]L^(_#((WWE(RS	S *+a/
AWE>BoA6; Q$8 9A => 	XG(--!G-DRRJ#J<r*jl^1MM  Z} UHLRVO  Z @E		X bmm#IIbk" 	7}EFr&   Nc                    |'dt        j                         j                  d       d}t        d       | j	                  |      }| j                  |      }| j                  |      }|r| j                  |      nt        j                         }	|j                  rt        d       yt        d       | j                  ||||	t        j                         j                               }
| j                  |
|       t        d       |S )	z6Main function to generate settlement comparison reportNSrikara_Settlement_Comparison_z%Y%m%d_%H%M%Sz.xlsxzLoading data files...z?Warning: HIMS collection report is empty or could not be loadedz(Creating settlement comparison report...z4Settlement comparison report generated successfully!)r	   nowstrftimer7   r=   rQ   rW   rZ   r0   r8   ro   r   r
   r   )r#   	hims_file	axis_file
paytm_filephonepe_filer   rq   rr   rs   rt   r   s              r$   generate_settlement_reportz6SrikaraSettlementComparison.generate_settlement_report9  s     :8<<>;R;RSb;c:ddijK%& 44Y?	229=	//
;
EQt33LAWYWcWcWe??ST89 @@y*lHLLN<O<O<Q

 	  <DEr&   )NN)__name__
__module____qualname__r%   r=   rQ   rW   rZ   r   r   r    r&   r$   r   r   	   s0    
 "$&"P"8
"A-FxGtr&   r   __main__zSrikaraintegratedHIS.xlsxzMomentsPaySrikara_Bank_new.xlsxzSrikaramomentpay06-06.csv)r   r   r   zReport generated: zError generating report: z@Please ensure all input files exist and have the correct format.)pandasr0   openpyxlr   r   openpyxl.stylesr   r   r   openpyxl.utils.dataframer   numpynpr	   r
   osr   r   settlement_comparisonr   r   r   r   r   r7   r6   r<   r   r&   r$   <module>r      s     , 8 8 6  # 	M M`
 z79 ,I1I,J	R+FF! G 

 	";-01! "  R)!-.PQQRs   
A+ +B0BB