
    xohm                     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                   J    e 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	
      }|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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 #??F4F+GHLL%N {}    	":1#>?<<>!	"s   BB 	C
!C?C
C
c           	         	 t        j                  |      }|j                  ddddd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_AMTrZ   GST_AMTEMI_AMTNET_AMTrC   )IGSTSGSTCGST   )axisr   Charges_DeductedNet_Amount_CalculatedrF   rY   ri   rh   r;   r?   z+Error loading Axis bank settlement report: N)rG   rH   rK   rD   rF   rJ   rL   rM   rN   rO   rP   rQ   )r6   rR   axis_dfgst_colscolexisting_gst_colsaxis_summaryrV   s           r7   load_axis_bank_settlementz5SrikaraSettlementComparison.load_axis_bank_settlementJ   s   ,	"mmI.G nn &+  '.n G 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D DDB8D D 	E%!EEEc                     	 |j                  d      rt        j                  |      }nt        j                  |      }|j	                  dddd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   rY   
Commissionr[   r]   )
date_addedtotal_amount
commissiongst
net_amountrC   r   rh   ri   rF   rj   r<   r?   z'Error loading Paytm settlement report: N)endswithrG   read_csvrH   rK   rJ   rL   rM   rN   rO   rP   rQ   )r6   rR   paytm_dfpaytm_summaryrV   s        r7   load_paytm_settlementz1SrikaraSettlementComparison.load_paytm_settlementz   s/   %	"!!&);;y1==3  $ .**0 H -5\,B,I,I!,L*25/*@*@*C-DH'( 2:.1I1P1PQR1S/78J/K2LH,- %,,V488 %).$):  {}	  19M,-   	";A3?@<<>!	"s   CC 	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?   rY   ri   rh   rC   z)Error loading PhonePe settlement report: N)rG   rQ   rO   rP   )r6   rR   phonepe_summaryrV   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<   rY   ri   rh   
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   concatrQ   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          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*                  |   _         |j/                  |       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-2rf      r   @   )min_colmax_colvalue      z'Settlement comparison report saved to: )r   rP   minhasattrr   r   activetitlemerge_cellsr   fontr   r3   _create_table1_monthly_totals_create_table2_datewise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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 Q| 	GGJW-M||GW|E  D"41djj6L"3tzz?3j@-0TZZ-A
	 !a4N8FB  /5	G 	7}EFs   7A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   rf      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_headersrm   headersub_headers
hims_totalsettlement_total
diff_totaldata_rowr   s                r7   r   z9SrikaraSettlementComparison._create_table1_monthly_totalsN  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   rf   )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   z%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  rm   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   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   rP   rW   rp   r|   r   rG   rQ   r   r   r   )r6   	hims_file	axis_file
paytm_filephonepe_filer   r   r   r   r   r   s              r7   generate_settlement_reportz6SrikaraSettlementComparison.generate_settlement_report  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   rW   rp   r|   r   r   r   r   r   r   r9   r7   r   r   
   sA    
8!"F."`'"R	"Y/vCGJ`@D\|!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   rP   rO   rV   r  r9   r7   <module>r%     s     , F F 6  # 	 e eP z79 ,I1I,J	R+FF! G 

 	";-01! "  R)!-.PQQRs   A3 3B8BB