
    )gh8                         d dl Zd dlmZmZ d dlmZmZmZ d dl	Z
d dlmZ d dlZd dlZd Zd Zd Zd Zd	 Zd
 Zedk(  r e        yy)    N)load_workbookWorkbook)PatternFillFont	Alignment)datetimec                 *   	 t        d|         t        j                  |       }dddddddd}|d   j                  |      |d<   |d   j	                  d      |d<   |j                  g d      j                  d	d
d      j                         }|j                  ddi      }t        dt        |       d       t        d|d   j                                 |S # t        $ r,}t        d|        t        j                         cY d}~S d}~ww xY w)z8Load HIMS collection report and process by payment groupz Loading HIMS collection report: CardOnline)zCredit Cardz
Debit CardUPIeWalletzNet BankingPaytmPhonePePayment Mode NamePayment Group NameOthers)r   Bill DtzUnit/Locationsumcount)Amountr   Transaction_CountcolumnszHIMS data processed:  recordszPayment Groups found: zError loading HIMS data: N)printpd
read_excelmapfillnagroupbyaggreset_indexrenamelenunique	Exception	DataFrame)	file_pathhims_dfpayment_group_mappinghims_summaryes        SrikaraSettlementMain.pyload_and_process_hims_datar.   	   s9   #0<=--	*
 " #!
 )00C(D(H(HI^(_$%(/0D(E(L(LX(V$% 'YZ^^!(`
  ;= 	
 $**4GI\3]*^%c,&7%8AB&|4H'I'P'P'R&STU )!-.||~s   CC 	D&!DDDc                 T   	 t        d|         t        j                  |       ddddddd}|j                         D ](  \  }}|j                  v sj                  ||i	      * g d
}t        fd|D              r|   j                  dd      d<   d   j                  d      d<   d   j                  d      d<   d   j                  d      d<   d   j                  d      d<   dj                  vrd   d   z
  d   z
  d   z
  d<   j                  d      j                  dddddd      j                         }d|d<   t        dt        |       d       |S # t        $ r,}t        d|        t        j                         cY d}~S d}~ww xY w)z@Load and process Axis bank settlement data for Card transactionsz%Loading Axis bank settlement report: Gross_Amount
Net_AmountMDRGSTEMISettlement_Date)	GROSS_AMTNET_AMTr2   GST_AMTEMI_AMTPROCESS_DATEr   )IGSTSGSTCGSTc              3   :   K   | ]  }|j                   v   y w)Nr   ).0colaxis_dfs     r-   	<genexpr>z3load_and_process_axis_settlement.<locals>.<genexpr>G   s     =#sgoo%=s      T)axisskipnar   r   )r0   r1   r2   r3   r4   r
   Payment_Typez Axis settlement data processed: r   z$Error loading Axis settlement data: N)r   r   r   itemsr   r#   anyr   r   r    r!   r"   r$   r&   r'   )r(   column_mappingold_colnew_colgst_columnsaxis_summaryr,   rA   s          @r-    load_and_process_axis_settlementrN   0   s   25i[AB--	* (#-
 !/ 4 4 6 	EGW'//)!..'71C.D	E
 /===$[1551T5JGEN #*."9"@"@"C ..q1 ..q1 ..q1 w.$+N$;gen$LwW\~$]`ghm`n$nGL! '89==!?
  ;= 	 (.^$0\1B0C8LM 4QC89||~s%   AE2 DE2 2	F';!F"F'"F'c                    	 t        d|         	 t        j                  |       }ddddd}|j	                         D ](  \  }}||j
                  v s|j                  ||i      }* |d   j                  d      |d<   |d   j                  d      |d<   |d   j                  d      |d<   |d   |d   z
  |d   z
  |d	<   |j                  d      j                  d
d
d
d
d      j                         }d|d<   t        dt        |       d       |S #  t        j                  |       }Y xY w# t        $ r,}t        d|        t        j                         cY d}~S d}~ww xY w)zBLoad and process Paytm settlement data for Online/UPI transactionsz!Loading Paytm settlement report: r   
Commissionr3   r5   )total_amount
commissiongst
date_addedr   r   r1   r   )r   r1   rP   r3   r   rF   z!Paytm settlement data processed: r   z%Error loading Paytm settlement data: N)r   r   read_csvr   rG   r   r#   r   r    r!   r"   r$   r&   r'   )r(   paytm_dfrI   rJ   rK   paytm_summaryr,   s          r-   !load_and_process_paytm_settlementrX   f   s   -1)=>	0{{9-H %&+	
 !/ 4 4 6 	GGW(***#??GW3E?F	G
 &h/66q9!),!7!>!>q!A"5/003 "*(!3h|6L!LxX]!^ !(():;??	A
 
 ;= 	 )1n%1#m2D1EXNOG	0}}Y/HH  5aS9:||~s:   D0 D +D0 C D0 D-*D0 0	E%9!E E% E%c                    t        d       t               }| j                  s"|j                  | d   j	                                |j                  s"|j                  |d   j	                                |j                  s"|j                  |d   j	                                g }|D ]  }| | d   |k(  | d   dk(  z     }| | d   |k(  | d   dk(  z     }||d   |k(     }||d   |k(     }	|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}|j                  s|d   j                         nd}|j                  s|d   j                         nd}|j                  s|d   j                         nd}|	j                  s|	d   j                         nd}|	j                  s|	d
   j                         nd}|	j                  s|	d   j                         nd}|	j                  s|	d   j                         nd}|
|z
  }||z
  }|||z   z
  }i d|d|
d|d|d|d|d|d|d|d|d|d|d|d|d|d|}|j                  |       	 t        j                  |      S )z,Create the main settlement comparison reportz(Creating settlement comparison report...r   r5   r   r
   r   r   r   r0   r1   r2   r3   r4   rP   Date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_Variance)	r   setemptyupdater%   r   appendr   r'   )	hims_data	axis_data
paytm_data	all_datescomparison_datadate_val	hims_cardhims_onlineaxis_settlementpaytm_settlementhims_card_amounthims_online_amounthims_total_amount
axis_grossaxis_netaxis_mdraxis_gstaxis_emipaytm_amount	paytm_netpaytm_commission	paytm_gstcard_varianceonline_variancetotal_variancecomparison_rows                             r-   #create_settlement_comparison_reportr      s    

45 I??9-4467??#45<<>?$56==?@O 8/y!X-+,68
	
  y!X-+,8:
 $I.?$@H$LM%j1B&Cx&OP =FOO9X.224QR@K@Q@Q[2668WX,/AABQBWBW_^488:]^
>M>S>S?<0446YZ7F7L7L?5)--/RS7F7L7L?5)--/RS7F7L7L?5)--/RS?O?U?U'1557[\@P@V@V$\2668\]	GWG]G]+L9==?cd9I9O9O$U+//1UV	 )83,y8*h.BC
H
 0
 !"4
  !2	

  
 x
 
 
 
 L
 	
  0
 
 ]
 
  n!
& 	~.q8/t <<((    c           	         t        d|        t               }|j                  }d|_        g d}t	        dd      }t        dd	      }t        |d
      D ]9  \  }}|j                  d
||      }	||	_        ||	_	        t        dd      |	_        ; t        | j                         d      D ]  \  }
\  }}t        |d
      D ]  \  }}|j                  dd      j                         }||v r||   }n$|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 ]}  }d}|d   j*                  }|D ]B  }		 t'        t/        |	j0                              |kD  rt'        t/        |	j0                              }D t3        |dz   d      }||j4                  |   _         |j9                  |       t        d|        y#  Y xY w)zCreate formatted Excel reportzCreating Excel report: zSettlement Comparison)rZ   zHIMS Card AmountzHIMS Online AmountzHIMS Total AmountzAxis Gross AmountzAxis Net AmountzAxis MDRzAxis GSTzAxis EMIzPaytm AmountzPaytm Net AmountzPaytm Commissionz	Paytm GSTzCard VariancezOnline VariancezTotal Variance1274bdsolid)fgColor	fill_typeFFFFFFT)colorboldrC   )rowcolumnvaluecenter)
horizontalvertical    _r   u   ₹,.2f   TOTAL)r   )r   r   z=SUM(z2:)   z$Settlement comparison report saved: N)r   r   activetitler   r   	enumeratecellfillfontr   	alignmentiterrowsreplaceupperget
isinstanceintfloatr$   rangecolumn_letterr   strr   mincolumn_dimensionswidthsave)comparison_dfoutput_filewbwsheadersheader_fillheader_fontcol_numheaderr   row_numr   row_datacol_keyr   alt_key
totals_row
col_lettertotal_formular   
max_lengthr   adjusted_widths                          r-   create_excel_reportr      s    
#K=
12 
B	B&BHG h'BKXD1K$Wa0 Kww1WFw;		"hJ	K #,M,B,B,Da"H B!X(!4 	BOGVnnS#.446G(" ) !..c2 Wa0%#u.7Q;GGSt;MNGG5A	BB  ]#a'JGG
1GG4-1t_BGG
1G%* CL1,- GWW7W3AA

|2j\*Q,qI
JwmD7;Jw/4	G ** 
C
q	// 	Ds4::'*4!$S_!5J	 Z!^R04B]+1
C GGK	0
>?s   	?KKc                  x   t        d       t        d       t        d       t        d       t        d       t                d} d}d}d	}| ||fD ]2  }t        j                  j                  |      r#t        d
|        d}4 |s6t        d       t        d|         t        d|        t        d|        y	 t	        |       }t        |      }t        |      }|j                  rt        d       yt        |||      }|j                  rt        d       ydt        j                         j                  d       d}	t        ||	       t                t        d       t        dt        |              t        d|d   j                         d       t        d|d   |d   z   j                         d       t        d|d   j                         d       t                t        d |	        t        d!       y# t        $ r,}
t        d"|
        d#dl}|j#                          Y d}
~
yd}
~
ww xY w)$z6Main function to generate settlement comparison reportz,=== SRIKARA SETTLEMENT COMPARISON REPORT ===z=This script creates a settlement comparison report comparing:z,1. HIMS collection report (by payment group)z62. Axis bank settlement report (for Card transactions)z83. Paytm settlement report (for Online/UPI transactions)zSrikaraintegratedHIS.xlsxzMomentsPaySrikara_Bank_new.xlsxzSrikaramomentpay06-06.csvTzWarning: File not found: Fz?Some required files are missing. Please ensure all files exist:z1. HIMS collection report: z2. Axis bank settlement: z3. Paytm settlement: NzBError: HIMS data is empty. Please check the file and column names.zAError: No comparison data generated. Please check the data files.Srikara_Settlement_Comparison_z%Y%m%d_%H%M%Sz.xlsxz=== REPORT SUMMARY ===zTotal dates processed: u   Total HIMS amount: ₹r]   r   u   Total settlement amount: ₹r_   rd   u   Total variance: ₹ri   zReport saved as: z4Settlement comparison report generated successfully!zError generating report: r   )r   ospathexistsr.   rN   rX   rk   r   r   nowstrftimer   r$   r   r&   	traceback	print_exc)	hims_file	axis_file
paytm_filefiles_existr(   rn   ro   rp   r   r   r,   r   s               r-   mainr   ,  sC    

89	
IJ	
89	
BC	
DE	G ,I1I,J KJ7  	ww~~i(-i[9:K 
 OP+I;78))56%j\23$.y9	4Y?	6zB
??VW <IyR\]UV 7x||~7N7N7_6``ef 	M;7&''M(:';<=&}5H'I'M'M'OPT&UVW,m<M.NQ^_qQr.r-w-w-yz~,  A  	B#M2B$C$G$G$I$#OPQ!+/0DE )!-.s%   =8H 6$H C(H 	H9"H44H9__main__)pandasr   openpyxlr   r   openpyxl.stylesr   r   r   numpynpr   sysr   r.   rN   rX   r   r   r   __name__ r   r-   <module>r      sY     , 8 8   
 	%N4l/bJ)XG@RAF zF r   