import pandas as pd from openpyxl import load_workbook, Workbook from openpyxl.styles import PatternFill, Font, Alignment import numpy as np from datetime import datetime import sys import os def load_and_process_hims_data(file_path): """Load HIMS collection report and process by payment group""" try: print(f"Loading HIMS collection report: {file_path}") hims_df = pd.read_excel(file_path) # Process the data to group by payment method # Map payment modes to payment groups payment_group_mapping = { 'Credit Card': 'Card', 'Debit Card': 'Card', 'UPI': 'Online', 'eWallet': 'Online', 'Net Banking': 'Online', 'Paytm': 'Online', 'PhonePe': 'Online' } # Create payment group name column hims_df['Payment Group Name'] = hims_df['Payment Mode Name'].map(payment_group_mapping) hims_df['Payment Group Name'] = hims_df['Payment Group Name'].fillna('Others') # Group by payment group name, date, and location hims_summary = hims_df.groupby(['Payment Group Name', 'Bill Dt', 'Unit/Location']).agg({ 'Amount': 'sum', 'Payment Mode Name': 'count' # Count of transactions }).reset_index() hims_summary = hims_summary.rename(columns={'Payment Mode Name': 'Transaction_Count'}) print(f"HIMS data processed: {len(hims_summary)} records") print(f"Payment Groups found: {hims_summary['Payment Group Name'].unique()}") return hims_summary except Exception as e: print(f"Error loading HIMS data: {e}") return pd.DataFrame() def load_and_process_axis_settlement(file_path): """Load and process Axis bank settlement data for Card transactions""" try: print(f"Loading Axis bank settlement report: {file_path}") axis_df = pd.read_excel(file_path) # Handle different column names that might exist column_mapping = { 'GROSS_AMT': 'Gross_Amount', 'NET_AMT': 'Net_Amount', 'MDR': 'MDR', 'GST_AMT': 'GST', 'EMI_AMT': 'EMI', 'PROCESS_DATE': 'Settlement_Date' } # Rename columns if they exist for old_col, new_col in column_mapping.items(): if old_col in axis_df.columns: axis_df = axis_df.rename(columns={old_col: new_col}) # Handle GST calculation if separate columns exist gst_columns = ['IGST', 'SGST', 'CGST'] if any(col in axis_df.columns for col in gst_columns): axis_df['GST'] = axis_df[gst_columns].sum(axis=1, skipna=True) # Fill missing values axis_df['Gross_Amount'] = axis_df['Gross_Amount'].fillna(0) axis_df['MDR'] = axis_df['MDR'].fillna(0) axis_df['GST'] = axis_df['GST'].fillna(0) axis_df['EMI'] = axis_df['EMI'].fillna(0) # Calculate net amount if not present if 'Net_Amount' not in axis_df.columns: axis_df['Net_Amount'] = axis_df['Gross_Amount'] - axis_df['MDR'] - axis_df['GST'] - axis_df['EMI'] # Group by settlement date axis_summary = axis_df.groupby('Settlement_Date').agg({ 'Gross_Amount': 'sum', 'Net_Amount': 'sum', 'MDR': 'sum', 'GST': 'sum', 'EMI': 'sum' }).reset_index() axis_summary['Payment_Type'] = 'Card' print(f"Axis settlement data processed: {len(axis_summary)} records") return axis_summary except Exception as e: print(f"Error loading Axis settlement data: {e}") return pd.DataFrame() def load_and_process_paytm_settlement(file_path): """Load and process Paytm settlement data for Online/UPI transactions""" try: print(f"Loading Paytm settlement report: {file_path}") # Try to read as CSV first, then Excel try: paytm_df = pd.read_csv(file_path) except: paytm_df = pd.read_excel(file_path) # Handle different column names column_mapping = { 'total_amount': 'Amount', 'commission': 'Commission', 'gst': 'GST', 'date_added': 'Settlement_Date' } # Rename columns if they exist for old_col, new_col in column_mapping.items(): if old_col in paytm_df.columns: paytm_df = paytm_df.rename(columns={old_col: new_col}) # Fill missing values paytm_df['Amount'] = paytm_df['Amount'].fillna(0) paytm_df['Commission'] = paytm_df['Commission'].fillna(0) paytm_df['GST'] = paytm_df['GST'].fillna(0) # Calculate net amount paytm_df['Net_Amount'] = paytm_df['Amount'] - paytm_df['Commission'] - paytm_df['GST'] # Group by settlement date paytm_summary = paytm_df.groupby('Settlement_Date').agg({ 'Amount': 'sum', 'Net_Amount': 'sum', 'Commission': 'sum', 'GST': 'sum' }).reset_index() paytm_summary['Payment_Type'] = 'Online' print(f"Paytm settlement data processed: {len(paytm_summary)} records") return paytm_summary except Exception as e: print(f"Error loading Paytm settlement data: {e}") return pd.DataFrame() def create_settlement_comparison_report(hims_data, axis_data, paytm_data): """Create the main settlement comparison report""" print("Creating settlement comparison report...") # Get unique dates from all sources all_dates = set() if not hims_data.empty: all_dates.update(hims_data['Bill Dt'].unique()) if not axis_data.empty: all_dates.update(axis_data['Settlement_Date'].unique()) if not paytm_data.empty: all_dates.update(paytm_data['Settlement_Date'].unique()) comparison_data = [] for date_val in all_dates: # HIMS data for this date hims_card = hims_data[ (hims_data['Bill Dt'] == date_val) & (hims_data['Payment Group Name'] == 'Card') ] hims_online = hims_data[ (hims_data['Bill Dt'] == date_val) & (hims_data['Payment Group Name'] == 'Online') ] # Settlement data for this date axis_settlement = axis_data[axis_data['Settlement_Date'] == date_val] paytm_settlement = paytm_data[paytm_data['Settlement_Date'] == date_val] # Calculate totals hims_card_amount = hims_card['Amount'].sum() if not hims_card.empty else 0 hims_online_amount = hims_online['Amount'].sum() if not hims_online.empty else 0 hims_total_amount = hims_card_amount + hims_online_amount axis_gross = axis_settlement['Gross_Amount'].sum() if not axis_settlement.empty else 0 axis_net = axis_settlement['Net_Amount'].sum() if not axis_settlement.empty else 0 axis_mdr = axis_settlement['MDR'].sum() if not axis_settlement.empty else 0 axis_gst = axis_settlement['GST'].sum() if not axis_settlement.empty else 0 axis_emi = axis_settlement['EMI'].sum() if not axis_settlement.empty else 0 paytm_amount = paytm_settlement['Amount'].sum() if not paytm_settlement.empty else 0 paytm_net = paytm_settlement['Net_Amount'].sum() if not paytm_settlement.empty else 0 paytm_commission = paytm_settlement['Commission'].sum() if not paytm_settlement.empty else 0 paytm_gst = paytm_settlement['GST'].sum() if not paytm_settlement.empty else 0 # Calculate variances card_variance = hims_card_amount - axis_net online_variance = hims_online_amount - paytm_net total_variance = hims_total_amount - (axis_net + paytm_net) comparison_row = { 'Date': date_val, 'HIMS_Card_Amount': hims_card_amount, 'HIMS_Online_Amount': hims_online_amount, 'HIMS_Total_Amount': hims_total_amount, 'Axis_Gross_Amount': axis_gross, 'Axis_Net_Amount': axis_net, 'Axis_MDR': axis_mdr, 'Axis_GST': axis_gst, 'Axis_EMI': axis_emi, 'Paytm_Amount': paytm_amount, 'Paytm_Net_Amount': paytm_net, 'Paytm_Commission': paytm_commission, 'Paytm_GST': paytm_gst, 'Card_Variance': card_variance, 'Online_Variance': online_variance, 'Total_Variance': total_variance } comparison_data.append(comparison_row) return pd.DataFrame(comparison_data) def create_excel_report(comparison_df, output_file): """Create formatted Excel report""" print(f"Creating Excel report: {output_file}") # Create workbook wb = Workbook() ws = wb.active ws.title = "Settlement Comparison" # Define headers headers = [ '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' ] # Format headers header_fill = PatternFill(fgColor='1274bd', fill_type='solid') header_font = Font(color='FFFFFF', bold=True) for col_num, header in enumerate(headers, 1): cell = ws.cell(row=1, column=col_num, value=header) cell.fill = header_fill cell.font = header_font cell.alignment = Alignment(horizontal='center', vertical='center') # Write data for row_num, (_, row_data) in enumerate(comparison_df.iterrows(), 2): for col_num, header in enumerate(headers, 1): col_key = header.replace(' ', '_').upper() if col_key in row_data: value = row_data[col_key] else: # Handle different column name mappings alt_key = header.replace(' ', '_') value = row_data.get(alt_key, 0) if isinstance(value, (int, float)) and col_num > 1: # Format currency columns ws.cell(row=row_num, column=col_num, value=f"₹{value:,.2f}") else: ws.cell(row=row_num, column=col_num, value=value) # Add totals row totals_row = len(comparison_df) + 3 ws.cell(row=totals_row, column=1, value="TOTAL") ws.cell(row=totals_row, column=1).font = Font(bold=True) # Calculate totals for numeric columns for col_num in range(2, len(headers) + 1): col_letter = ws.cell(row=1, column=col_num).column_letter total_formula = f"=SUM({col_letter}2:{col_letter}{totals_row-1})" ws.cell(row=totals_row, column=col_num, value=total_formula) ws.cell(row=totals_row, column=col_num).font = Font(bold=True) # Auto-adjust column widths for column in ws.columns: max_length = 0 column_letter = column[0].column_letter for cell in column: try: if len(str(cell.value)) > max_length: max_length = len(str(cell.value)) except: pass adjusted_width = min(max_length + 2, 20) ws.column_dimensions[column_letter].width = adjusted_width # Save workbook wb.save(output_file) print(f"Settlement comparison report saved: {output_file}") def main(): """Main function to generate settlement comparison report""" print("=== SRIKARA SETTLEMENT COMPARISON REPORT ===") print("This script creates a settlement comparison report comparing:") print("1. HIMS collection report (by payment group)") print("2. Axis bank settlement report (for Card transactions)") print("3. Paytm settlement report (for Online/UPI transactions)") print() # File paths - update these with actual file paths hims_file = "SrikaraintegratedHIS.xlsx" axis_file = "MomentsPaySrikara_Bank_new.xlsx" paytm_file = "Srikaramomentpay06-06.csv" # Check if files exist files_exist = True for file_path in [hims_file, axis_file, paytm_file]: if not os.path.exists(file_path): print(f"Warning: File not found: {file_path}") files_exist = False if not files_exist: print("Some required files are missing. Please ensure all files exist:") print(f"1. HIMS collection report: {hims_file}") print(f"2. Axis bank settlement: {axis_file}") print(f"3. Paytm settlement: {paytm_file}") return try: # Load and process data hims_data = load_and_process_hims_data(hims_file) axis_data = load_and_process_axis_settlement(axis_file) paytm_data = load_and_process_paytm_settlement(paytm_file) if hims_data.empty: print("Error: HIMS data is empty. Please check the file and column names.") return # Create comparison report comparison_df = create_settlement_comparison_report(hims_data, axis_data, paytm_data) if comparison_df.empty: print("Error: No comparison data generated. Please check the data files.") return # Generate output filename output_file = f"Srikara_Settlement_Comparison_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx" # Create Excel report create_excel_report(comparison_df, output_file) print() print("=== REPORT SUMMARY ===") print(f"Total dates processed: {len(comparison_df)}") print(f"Total HIMS amount: ₹{comparison_df['HIMS_Total_Amount'].sum():,.2f}") print(f"Total settlement amount: ₹{(comparison_df['Axis_Net_Amount'] + comparison_df['Paytm_Net_Amount']).sum():,.2f}") print(f"Total variance: ₹{comparison_df['Total_Variance'].sum():,.2f}") print() print(f"Report saved as: {output_file}") print("Settlement comparison report generated successfully!") except Exception as e: print(f"Error generating report: {e}") import traceback traceback.print_exc() if __name__ == "__main__": main()