
     phIz                     0   d dl Zd dlmZmZ d dlmZmZ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d dlZ G d d      Zedk(  r. e       Zd	Zd
ZdZ	 ej3                  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BorderSide)dataframe_to_rows)datetimedatec                   P    e Zd Zd Zd 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      | _        t        dd      | _        t        dd	      | _        t        d
      | _        t        dd      | _	        t        t        d      t        d      t        d      t        d            | _        dddddddd| _        y )ND5E8D4solidfgColor	fill_typeDAE8FCF8CECCE1D5E7T
   boldsize	   )r   center)
horizontalverticalthin)style)leftrighttopbottom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   hims_collection_fillsettlement_fillnet_amount_fillcharges_fillr   header_font	data_fontr   	alignmentr   r	   borderlocation_mapping)selfs    P/home/runner/work/ReconOld/ReconOld/Srikara/SrikaraSettlementComparisonReport.py__init__z$SrikaraSettlementComparison.__init__   s    $/G$T!*8wO*8wO'GLT31"hJF#V$6"f%	
 + 2 2 2 2 2 2!
    c                    	 t        j                  |      }ddddd}|d   j                  |      |d<   |d   j                  d      |d<   |j	                  ddd	
      }t        j
                  |d         |d<   |j                  ddg      j                  ddi      j                         }|S # t        $ r,}t        d|        t        j                         cY d}~S d}~ww xY w)z9Load HIMS collection report and process for report formatCardsOnline)zCredit CardUPIzNet BankingWalletzPayment Mode NamePayment_CategoryOthers/PhonePeDateLocation)zBill DtzUnit/LocationcolumnsAmountsumz&Error loading HIMS collection report: N)pd
read_excelmapfillnarenameto_datetimegroupbyaggreset_index	Exceptionprint	DataFrame)r6   	file_pathhims_dfpayment_mode_mappinghims_summaryes         r7   load_hims_collection_reportz7SrikaraSettlementComparison.load_hims_collection_report'   s   "	"mmI.G  ''"	$  +22E*F*J*JK_*`G&'*12D*E*L*LM]*^G&' nn!!+.n G !nnWV_=GFO #??F4F+GHLL%N {}    	":1#>?<<>!	"s   B-B0 0	C%9!C C% C%c           	         	 t        j                  |      }|j                  ddddddd      }t        j                  |d         |d<   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<   |d   j                  d      |d   z
  |d<   |j                  d      j                  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 transactionsrA   Gross_AmountMDRGSTEMI
Net_Amount)PROCESS_DATE	GROSS_AMTr[   GST_AMTEMI_AMTNET_AMTrC   )IGSTSGSTCGST   )axisr   Charges_DeductedNet_Amount_CalculatedrF   rZ   rj   ri   r;   r?   z+Error loading Axis bank settlement report: N)rG   rH   rK   rL   rD   rF   rJ   rM   rN   rO   rP   rQ   rR   )r6   rS   axis_dfgst_colscolexisting_gst_colsaxis_summaryrW   s           r7   load_axis_bank_settlementz5SrikaraSettlementComparison.load_axis_bank_settlementM   s   /	"mmI.G nn &+  '.n G !nnWV_=GFO GOO+34<$WSw@VS$W!$W$%,->%?%C%C%C%KGEN%&GEN ,35>+@+@+C)0)>)>q)A,B)0)>)>q)A,BG&'
 180G0N0Nq0Q.56H.I1JG+, #??6266 %).$)8  {}	  07L+,1 %X4  	"?sCD<<>!	"s7   AD7 !D25D29B8D7 2D7 7	E, !E'!E,'E,c                 V   	 |j                  d      rt        j                  |      }nt        j                  |      }|j	                  dddddd      }t        j
                  |d         |d<   |d   j                  d	      |d   j                  d	      z   |d
<   |d   j                  d	      |d
   z
  |d<   |j                  d      j                  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 transactionsz.csvrA   rZ   
Commissionr\   r^   )
date_addedtotal_amount
commissiongst
net_amountrC   r   ri   rj   rF   rk   r<   r?   z'Error loading Paytm settlement report: N)endswithrG   read_csvrH   rK   rL   rJ   rM   rN   rO   rP   rQ   rR   )r6   rS   paytm_dfpaytm_summaryrW   s        r7   load_paytm_settlementz1SrikaraSettlementComparison.load_paytm_settlement   sI   (	"!!&);;y1==3  $ .**0 H  "~~hv.>?HV -5\,B,I,I!,L*25/*@*@*C-DH'( 2:.1I1P1PQR1S/78J/K2LH,- %,,V488 %).$):  {}	  19M,-   	";A3?@<<>!	"s   C0C3 3	D(<!D#D(#D(c                     	 t        j                  g d      }|S # t        $ r,}t        d|        t        j                         cY d}~S d}~ww xY w)z>Load PhonePe settlement report for Others/PhonePe transactionsrA   r?   rZ   rj   ri   rC   z)Error loading PhonePe settlement report: N)rG   rR   rP   rQ   )r6   rS   phonepe_summaryrW   s       r7   load_phonepe_settlementz3SrikaraSettlementComparison.load_phonepe_settlement   sN    	" ll  4M  NO"" 	"=aSAB<<>!	"s    	A!AAAc                    g }|j                   s|j                  |g d          |j                   s|j                  |g d          |j                   s|j                  |g d          |rt        j                  |d      }nt        j                  g d      }t               }|j                   s"|j                  |d   j                                |j                   s"|j                  |d   j                                g }t        |      D ]  }	|j                   s||d   |	k(     nt        j                         }
|j                   s||d   |	k(     nt        j                         }|
j                   s|
|
d   dk(     d   j                         nd	}|
j                   s|
|
d   d
k(     d   j                         nd	}|
j                   s|
|
d   dk(     d   j                         nd	}|j                   s||d   dk(     d   j                         nd	}|j                   s||d   d
k(     d   j                         nd	}|j                   s||d   dk(     d   j                         nd	}|j                   s||d   dk(     d   j                         nd	}|j                   s||d   d
k(     d   j                         nd	}|j                   s||d   dk(     d   j                         nd	}|j                   s||d   dk(     d   j                         nd	}|j                   s||d   d
k(     d   j                         nd	}|j                   s||d   dk(     d   j                         nd	}||z
  }||z
  }||z
  }|j                  i d|	d|d|d|d|d|d|d|d|d|d|d|d|d|d|d|        t        j                  |      S )z@Prepare consolidated data grouped by date with proper categoriesr   T)ignore_indexrC   rA   r?   r;   rE   r   r@   r<   rZ   rj   ri   
HIMS_CardsHIMS_Others_PhonePeHIMS_OnlineSettlement_CardsSettlement_Others_PhonePeSettlement_Online
Diff_CardsDiff_Others_PhonePeDiff_Online	Net_CardsNet_Others_PhonePe
Net_OnlineCharges_CardsCharges_Others_PhonePeCharges_Online)
emptyappendrG   concatrR   setupdateuniquesortedrF   )r6   	hims_data	axis_data
paytm_dataphonepe_datasettlement_datasettlement_combined	all_datesconsolidated_datadate_val	hims_datesettlement_date
hims_cardshims_othershims_onlinesettlement_cards_grosssettlement_others_grosssettlement_online_gross	net_cards
net_others
net_onlinecharges_cardscharges_otherscharges_online
diff_cardsdiff_othersdiff_onlines                              r7   prepare_consolidated_dataz5SrikaraSettlementComparison.prepare_consolidated_data   sk     ""9  .G  $H  I "":  /H  $I  J !!""<  1J  $K  L "$))O$"O"$,,  8Q  #R E	Yv.5578"((08??AB y) 5	HHQ	)F"3x"?@]_]i]i]kI cvb{b{12Ef2MQY2YZ  BD  BN  BN  BPO endsds9-?#@G#KLXVZZ\yzJnwn}n})I.@$AEU$UVW_`ddf  DEKfofufu)I.@$AX$MNxX\\^{|K CR  CX  CX__EW5X\c5c%des%t%x%x%z  ^_" M\  Mb  MbooFX6Y]m6m&no}&~  'C  'C  'E  hi# ET  EZ  EZooFX6Y]e6e&fgu&v&z&z&|  `a# N  T  T8J(Kw(VWXopttv  Z[I IX  I^  I^9K)LP`)`abyz~~  A  deJ AP  AV  AV9K)LPX)XYZqrvvx  \]J ~M  ~S  ~SOO<N,OSZ,Z[\nossu  YZM HW  H]  H]__=O-PTd-defxy}}  cdN @O  @U  @U__=O-PT\-\]^pquuw  [\N $&<<J%(??K%(??K$$ && j& &{	&
 {& #$:& ,-D& $%<& j& &{& {& Y& %j&  j!&$  %&& ).'&( !.)& A5	n ||-..r9   c                    |j                   rt        d       y|d   j                         }t        |d      r|j	                  d      }nd}t               }|j                  }d|_        d| d	}|j                  d
       ||d<   t        dd      |d   _
        t        d      |d   _        d| }|j                  d       ||d<   t        dd      |d   _
        t        d      |d   _        |j                  d       d|d<   t        d      |d   _
        | j                  ||       d}	|j                  d|	 d|	        d|d|	 <   t        d      |d|	    _
        | j                  |||	dz          |j                  d      }
| j!                  |
|       t#        dd      D ]  }d }t%        d!|z         }|j'                  ||"      D ]a  }|D ]Z  }	 t        |d#      rK|j(                  ?t+        t-        |j(                              |kD  rt+        t-        |j(                              }\ c t        |d$z   d%      }||j.                  |   _         |j3                  |       t        d&|        y#  Y xY w)'z6Create Excel report with format matching sample reportzNo data to create reportNrA   strftime%B %YzPayment ReconzSettlement ComparisonzPayment Recon z - Unit NamezA1:R1A1T   r   r   r   zCollection on zA2:R2A2r   zA4:R4zTable-1A4)r   Az:RzTable-2rg   Summary)title   r   @   min_colmax_colvalue      z'Settlement comparison report saved to: )r   rQ   minhasattrr   r   activer   merge_cellsr   fontr   r3   _create_table1_monthly_totals_create_table2_datewisecreate_sheet_create_summary_sheetrangechr	iter_rowsr   lenstrcolumn_dimensionswidthsave)r6   consolidated_dfoutput_file
first_date
month_namewbwsr   subtitletable2_start_row
summary_wscol_idx
max_lengthcolumn_letterrowcelladjusted_widths                    r7   create_excel_reportz/SrikaraSettlementComparison.create_excel_report  s      ,- %V,002
:z*#,,W5J(J ZYY* !L9
w4$R04&(;4 $J<0
w4$R04&(;4 	w4$4 	**2? 
+,B/?.@AB%.Q !"*.D/Q !"' 	$$R:JQ:NO __9_5
"":? Q| 	GGJW-M||GW|E  D"41djj6L"3tzz?3j@-0TZZ-A
	 !a4N8FB  /5	G 	7}EFs   AI;;I?	c                    |d   j                         |d   j                         |d   j                         |d   j                         |d   j                         |d   j                         |d   j                         |d   j                         |d	   j                         |d
   j                         |d   j                         |d   j                         |d   j                         |d   j                         |d   j                         d}g d}t        |d      D ]  \  }}|j                  d||       | j                  |j                  d|      _        | j
                  |j                  d|      _        | j                  |j                  d|      _         t        dd      D ]$  }| j                  |j                  d|      _	        & t        dd      D ]$  }| j                  |j                  d|      _	        & t        dd      D ]$  }| j                  |j                  d|      _	        & t        dd      D ]$  }| j                  |j                  d|      _	        & g d}t        |d      D ]  \  }}|j                  d||       | j                  |j                  d|      _        | j
                  |j                  d|      _        | j                  |j                  d|      _         t        dd      |j                  dd      _	        t        dd      D ]$  }| j                  |j                  d|      _	        & t        dd      D ]$  }| j                  |j                  d|      _	        & t        dd       D ]$  }| j                  |j                  d|      _	        & t        d d!      D ]$  }| j                  |j                  d|      _	        & |d   |d   z   |d   z   }|d   |d   z   |d   z   }	||	z
  }
d"||d   |d   |d   |	|d   |d   |d   |
|d
   |d   |d   |d   |d   |d   g}t        |d      D ]  \  }}|j                  d#||       |dkD  r.t        |t        t         f      rd$|j                  d#|      _        | j$                  |j                  d#|      _        | j
                  |j                  d#|      _        | j                  |j                  d#|      _         t        dd      |j                  d#d      _	        t        dd      D ]$  }| j                  |j                  d#|      _	        & t        dd      D ]$  }| j                  |j                  d#|      _	        & t        dd       D ]$  }| j                  |j                  d#|      _	        & t        d d!      D ]$  }| j                  |j                  d#|      _	        & y%)&z"Create Table 1 with monthly totalsr   r   r   r   r   r   r   r   r   r   r   r   r   r   r   )r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   )z	Unit NameHIMS Collection total r   Bank/Provider settlement totalr   r   r   "Net amount after Charges deductionr   r   Charges deductedr   r   rg      r   columnr   r   r   r   r   r      )ViewsapHIMS Collection Totalr;   Others/Phone Per<   Settlement Totalr;   r   r<   Diffr;   r   r<   r;   r   r<      FFFFFFr   r   r         Total   #,##0.00N)rF   	enumerater   r1   r   r3   r4   r   r-   fillr.   r/   r0   r   
isinstanceintfloatnumber_formatr2   )r6   r   dftotalsmain_headersrn   headersub_headers
hims_totalsettlement_total
diff_totaldata_rowr   s                r7   r   z9SrikaraSettlementComparison._create_table1_monthly_totals[  s$   
 \*..0#%&;#<#@#@#Bm,002 "#5 6 : : <)+,G)H)L)L)N!#$7!8!<!<!>\*..0#%&;#<#@#@#Bm,002K,,."$%9":">">"@\*..00446&()A&B&F&F&H !12668
&a$\15 	<KCGG#VG4.2.>.>BGG#G&+37>>BGG#G&004BGG#G&-		< A; 	HC.2.G.GBGG#G&+	H A; 	CC.2.B.BBGG#G&+	C B< 	CC.2.B.BBGG#G&+	C R= 	@C.2.?.?BGG#G&+	@b %[!4 	<KCGG#VG4.2.>.>BGG#G&+37>>BGG#G&004BGG#G&-		< )4HPW(XAa %A; 	HC.2.G.GBGG#G&+	HB< 	CC.2.B.BBGG#G&+	CR= 	CC.2.B.BBGG#G&+	CR= 	@C.2.?.?BGG#G&+	@ L)F3H,IIFS`Laa
!"45?Z8[[^dex^yy"22
Z)=vF[?\^der^s#V,>%?HcAdfl  nA  gB  DN+&/C(Df\FZ/*F3K,LfUeNfh
 $Ha0 	<JCGG#UG3Qw:ec5\:;EAc*8.2nnBGG#G&+37>>BGG#G&004BGG#G&-	< )4HPW(XAa %A; 	HC.2.G.GBGG#G&+	HB< 	CC.2.B.BBGG#G&+	CR= 	CC.2.B.BBGG#G&+	CR= 	@C.2.?.?BGG#G&+	@r9   c                    |}t        |d   j                         d      r"|d   j                         j                  d      nd}d| d}|j                  d| d|        ||d| <   t	        d	d
      |d|    _        t        d      |d|    _        |dz  }g d}t        |d      D ]  \  }}	|j                  |||	       | j                  |j                  ||      _        | j                  |j                  ||      _        | j                  |j                  ||      _         t        dd      |j                  |d      _        t        dd      D ]$  }| j                  |j                  ||      _        & t        dd      D ]$  }| j                   |j                  ||      _        & t        dd      D ]$  }| j"                  |j                  ||      _        & t        dd      D ]$  }| j$                  |j                  ||      _        & |dz  }g d}
t        |
d      D ]  \  }}	|j                  |||	       | j                  |j                  ||      _        | j                  |j                  ||      _        | j                  |j                  ||      _         t        dd      |j                  |d      _        t        dd      D ]$  }| j                  |j                  ||      _        & t        dd
      D ]$  }| j                   |j                  ||      _        & t        d
d      D ]$  }| j"                  |j                  ||      _        & t        dd      D ]$  }| j$                  |j                  ||      _        & |dz  }|j'                         D ]F  \  }}|d   }t        |d      r|j                  d      }nt)        |      }|d   |d   z   |d    z   }|d!   |d"   z   |d#   z   }||z
  }|||d   |d   |d    ||d!   |d"   |d#   ||d$   |d%   |d&   |d'   |d(   |d)   g}t        |d      D ]  \  }}|j                  |||       |dkD  r.t+        |t,        t.        f      rd*|j                  ||      _        | j2                  |j                  ||      _        | j                  |j                  ||      _        | j                  |j                  ||      _         t        dd      |j                  |d      _        t        dd      D ]$  }| j                  |j                  ||      _        & t        dd
      D ]$  }| j                   |j                  ||      _        & t        d
d      D ]$  }| j"                  |j                  ||      _        & t        dd      D ]$  }| j$                  |j                  ||      _        & |dz  }I y+),z'Create Table 2 with date-wise breakdownrA   r   r   z	June 2025zSrik z1 (Collection in HIMS vs Bank/Provider Settlement)r   z:PTr   r   r   r   rg   )rA   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   )rA   r   r;   r   r<   r   r;   r   r<   r   r;   r   r<   r;   r   r<   r   r   %d-%m-%Yr   r   r   r   r   r   r   r   r   r   r   r   r   N)r   r   r   r   r   r   r   r3   r   r   r1   r4   r   r  r   r-   r.   r/   r0   iterrowsr   r  r  r  r  r2   )r6   r   r  	start_rowcurrent_rowr   r   r  rn   r	  r
  _r   r   date_strr  r  r  r  r   s                       r7   r   z3SrikaraSettlementComparison._create_table2_datewise  sB      <C2f:>>CSU_;`RZ^^%..w7fq
:,&WX
;-r+78 (Q{m%)t"%=Q{m"*3x*HQ{m'qa$\15 	FKCGGCvG>8<8H8HBGGCG05=A^^BGGCG0::>++BGGCG07		F 3>hZa2bK*/A; 	RC8<8Q8QBGGCG05	RA; 	MC8<8L8LBGGCG05	MB< 	MC8<8L8LBGGCG05	MR= 	JC8<8I8IBGGCG05	Jqb %[!4 	FKCGGCvG>8<8H8HBGGCG05=A^^BGGCG0::>++BGGCG07		F 3>hZa2bK*/A; 	RC8<8Q8QBGGCG05	RB< 	MC8<8L8LBGGCG05	MR= 	MC8<8L8LBGGCG05	MR= 	JC8<8I8IBGGCG05	Jq kkm "	FAs6{Hx,#,,Z8x=\*S1F-GG#mJ\\J"#56=X9YY\_`s\tt#&66J *c,.?EZA[]`an]o'-?)@#FaBbdgh{d|  I;'-A)BCDU?+S1I-JCP`LacH
 (!4 J
UK5A7z%#u>ISBGGCG8F<@NNK49AEK4>>BkkK4;J 7B(^e6fBGGAG.3Q{ V<@<U<UK49VQ| Q<@<P<PK49QR} Q<@<P<PK49QR} N<@<M<MK49N1KE"	r9   c                    |j                  d       d|d<   t        dd      |d   _        t        d      |d   _        d	|d
<   d|d<   dD ]S  }| j
                  ||   _        | j                  ||   _        | j                  ||   _        t        dd      ||   _        U d}|j                         D ]V  \  }}|d	   }t        |d      r|j                  d      }nt        |      }|d   |d   z   |d   z   }	|j                  |d|       | j                  |j                  |d      _        | j                  |j                  |d      _        | j                  |j                  |d      _        |j                  |d|	       | j                  |j                  |d      _        | j                  |j                  |d      _        | j                  |j                  |d      _        d|j                  |d      _        |dz  }Y dD ]  }
d}|j!                  t#        |
      dz
  t#        |
      dz
        D ]a  }|D ]Z  }	 t        |d       rK|j$                  ?t'        t        |j$                              |kD  rt'        t        |j$                              }\ c t)        |dz   d"      }||j*                  |
   _         y!#  Y xY w)#z6Create Summary sheet with date-wise transaction totalszA1:B1zDate-wise Transaction Summaryr   T   r   r   r   rA   A3zTotal Transaction ValueB3)r  r  E6E6E6r   r      r   r  r   r   r   rg   r   r   r   r   )r   Br   r   r   r   N   )r   r   r   r   r3   r1   r4   r   r  r  r   r   r   r   r2   r  r   ordr   r   r   r   r   )r6   r   r  rn   r  r  r   r   r  total_value
col_letterr   r   r   s                 r7   r   z1SrikaraSettlementComparison._create_summary_sheet  s    	w24$R04&(;4 4,4   	LC++BsGL $BsG![[BsGN&x7KBsGL		L kkm 	FAs6{Hx,#,,Z8x= l+c2G.HH3}K]]K GGAXG>6:nnBGGAG.3;?>>BGGAG.88<BGGAG.5 GGA[GA6:nnBGGAG.3;?>>BGGAG.88<BGGAG.5?IBGGAG.<1K/	4 % 	DJJ||C
OB,>JXZHZ|[  D"41djj6L"3tzz?3j@-0TZZ-A
	 !a4N5CB  ,2	Ds   	AK

K	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                  ||||	      }
|
j                  rt        d       y| 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...zNo data to generate reportz4Settlement comparison report generated successfully!)r   nowr   rQ   rX   rq   r}   r   rG   rR   r   r   r   )r6   	hims_file	axis_file
paytm_filephonepe_filer   r   r   r   r   r   s              r7   generate_settlement_reportz6SrikaraSettlementComparison.generate_settlement_reportX  s     :8<<>;R;RSb;c:ddijK%& 44Y?	229=	//
;
EQt33LAWYWcWcWe??ST89 88y*l
   ./ 	  +>DEr9   )NN)__name__
__module____qualname__r8   rX   rq   r}   r   r   r   r   r   r   r(   r9   r7   r   r   
   sH    
8$"L1"f*"X	"Y/vGGR`@D\|;Dz!r9   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.)pandasrG   openpyxlr   r   openpyxl.stylesr   r   r   r   r	   openpyxl.utils.dataframer
   numpynpr   r   oscalendarr   r)  settlement_comparisonr$  r%  r&  r(  r   rQ   rP   rW   r,  r9   r7   <module>r7     s     , F F 6  # 	 o	 o	d z79 ,I1I,J	R+FF! G 

 	";-01! "  R)!-.PQQRs   A3 3B8BB