import pandas as pd from openpyxl import load_workbook, Workbook from openpyxl.styles import PatternFill, Font, Alignment import os from datetime import datetime import glob class SrikaraConsolidatedReport: def __init__(self): self.header_fill = PatternFill(fgColor='1274bd', fill_type='solid') self.header_font = Font(color='FFFFFF', bold=True) self.alignment = Alignment(horizontal='center', vertical='center') # All 7 locations as mentioned in requirements self.locations = [ 'SRIKARA MAIN', 'SRIKARA BRANCH 1', 'SRIKARA BRANCH 2', 'SRIKARA BRANCH 3', 'SRIKARA BRANCH 4', 'SRIKARA BRANCH 5', 'SRIKARA BRANCH 6' ] def compile_all_locations_report(self, reports_folder, output_file=None): """Compile all 7 locations settlement reports into one consolidated sheet""" if output_file is None: output_file = f"Srikara_All_Locations_Consolidated_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx" print("Compiling all locations settlement reports...") # Find all settlement report files settlement_files = glob.glob(os.path.join(reports_folder, "*Settlement_Comparison*.xlsx")) if not settlement_files: print(f"No settlement comparison files found in {reports_folder}") return consolidated_data = [] # Process each settlement report file for file_path in settlement_files: try: # Load the consolidated sheet from each report workbook = load_workbook(file_path) if "Consolidated Report" in workbook.sheetnames: ws = workbook["Consolidated Report"] # Read data from sheet data = [] headers = [] # Get headers for row in ws.iter_rows(min_row=1, max_row=1, values_only=True): headers = list(row) break # Get data rows for row in ws.iter_rows(min_row=2, values_only=True): if row[0] and row[0] != "TOTAL": # Skip empty rows and total row data.append(list(row)) # Convert to DataFrame if data: df = pd.DataFrame(data, columns=headers) # Add source file info df['Source_File'] = os.path.basename(file_path) consolidated_data.append(df) workbook.close() except Exception as e: print(f"Error processing file {file_path}: {e}") continue if not consolidated_data: print("No data found in settlement reports") return # Combine all data all_data = pd.concat(consolidated_data, ignore_index=True) # Create consolidated workbook wb = Workbook() ws = wb.active ws.title = "All Locations Consolidated" # Write headers headers = list(all_data.columns) for col_num, header in enumerate(headers, 1): cell = ws.cell(row=1, column=col_num, value=header) cell.fill = self.header_fill cell.font = self.header_font cell.alignment = self.alignment # Write data for row_num, (_, row_data) in enumerate(all_data.iterrows(), 2): for col_num, header in enumerate(headers, 1): value = row_data[header] # Handle currency formatting if isinstance(value, str) and value.startswith('₹'): ws.cell(row=row_num, column=col_num, value=value) elif isinstance(value, (int, float)) and 'Amount' in header: ws.cell(row=row_num, column=col_num, value=f"₹{value:,.2f}") else: ws.cell(row=row_num, column=col_num, value=value) # Add summary sheet summary_ws = wb.create_sheet("Summary") # Create summary by location numeric_columns = [col for col in all_data.columns if 'Amount' in col or 'Variance' in col or 'MDR' in col or 'GST' in col or 'EMI' in col or 'Commission' in col] # Group by location if location column exists if 'Location' in all_data.columns: location_summary = all_data.groupby('Location')[numeric_columns].sum().reset_index() else: # Create a simple summary location_summary = pd.DataFrame([all_data[numeric_columns].sum()], columns=numeric_columns) location_summary.insert(0, 'Location', 'All Locations') # Write summary headers summary_headers = list(location_summary.columns) for col_num, header in enumerate(summary_headers, 1): cell = summary_ws.cell(row=1, column=col_num, value=header) cell.fill = self.header_fill cell.font = self.header_font cell.alignment = self.alignment # Write summary data for row_num, (_, row_data) in enumerate(location_summary.iterrows(), 2): for col_num, header in enumerate(summary_headers, 1): value = row_data[header] if isinstance(value, (int, float)) and col_num > 1: # Format currency columns summary_ws.cell(row=row_num, column=col_num, value=f"₹{value:,.2f}") else: summary_ws.cell(row=row_num, column=col_num, value=value) # Add grand total row grand_total_row = len(location_summary) + 3 summary_ws.cell(row=grand_total_row, column=1, value="GRAND TOTAL") summary_ws.cell(row=grand_total_row, column=1).font = Font(bold=True) # Calculate grand totals for col_num in range(2, len(summary_headers) + 1): col_letter = summary_ws.cell(row=1, column=col_num).column_letter total_formula = f"=SUM({col_letter}2:{col_letter}{grand_total_row-1})" summary_ws.cell(row=grand_total_row, column=col_num, value=total_formula) summary_ws.cell(row=grand_total_row, column=col_num).font = Font(bold=True) # Save consolidated report wb.save(output_file) print(f"All locations consolidated report saved to: {output_file}") return output_file def create_location_specific_template(self, location_name, template_file=None): """Create a template for location-specific settlement comparison""" if template_file is None: template_file = f"Srikara_{location_name.replace(' ', '_')}_Settlement_Template.xlsx" wb = Workbook() ws = wb.active ws.title = f"{location_name} Settlement" # Create template headers headers = [ 'Date', 'Payment Group Name', 'HIMS Collection Amount', 'HIMS Transaction Count', 'Bank Gross Amount', 'Bank Net Amount', 'Bank MDR', 'Bank GST', 'Bank EMI', 'Paytm Amount', 'Paytm Net Amount', 'Paytm Commission', 'Paytm GST', 'Settlement Variance', 'Remarks' ] # Write headers for col_num, header in enumerate(headers, 1): cell = ws.cell(row=1, column=col_num, value=header) cell.fill = self.header_fill cell.font = self.header_font cell.alignment = self.alignment # Add sample data rows sample_data = [ ['2024-01-01', 'Card', 50000, 100, 50000, 48500, 1200, 300, 0, 0, 0, 0, 0, 0, 'Sample data'], ['2024-01-01', 'Online', 30000, 150, 0, 0, 0, 0, 0, 30000, 29400, 500, 100, 0, 'Sample data'], ['2024-01-02', 'Card', 60000, 120, 60000, 58200, 1440, 360, 0, 0, 0, 0, 0, 0, 'Sample data'], ['2024-01-02', 'Online', 40000, 200, 0, 0, 0, 0, 0, 40000, 39200, 600, 200, 0, 'Sample data'] ] for row_num, row_data in enumerate(sample_data, 2): for col_num, value in enumerate(row_data, 1): if isinstance(value, (int, float)) and col_num > 2: # Format currency columns ws.cell(row=row_num, column=col_num, value=f"₹{value:,.2f}" if value > 0 else 0) else: ws.cell(row=row_num, column=col_num, value=value) # Add instructions sheet instructions_ws = wb.create_sheet("Instructions") instructions = [ "SRIKARA SETTLEMENT COMPARISON REPORT - INSTRUCTIONS", "", "This template is for location: " + location_name, "", "DATA SOURCES:", "1. HIMS Collection Report - Summary of transactions from billing system", "2. Bank Settlement Report - For Card transactions (Axis Bank)", "3. Paytm Settlement Report - For Online/UPI transactions", "", "COLUMN DESCRIPTIONS:", "- Date: Settlement date", "- Payment Group Name: Card, Online, or Others", "- HIMS Collection Amount: Amount from HIMS billing system", "- HIMS Transaction Count: Number of transactions", "- Bank Gross Amount: Total amount from bank settlement", "- Bank Net Amount: Amount after deducting MDR, GST, EMI", "- Bank MDR: Merchant Discount Rate charged by bank", "- Bank GST: GST on MDR (sum of IGST+SGST+CGST if separate)", "- Bank EMI: EMI processing charges if applicable", "- Paytm Amount: Gross amount from Paytm settlement", "- Paytm Net Amount: Amount after deducting commission and GST", "- Paytm Commission: Commission charged by Paytm", "- Paytm GST: GST on commission", "- Settlement Variance: Difference between HIMS and settlement amounts", "", "FORMULAS:", "Bank Net Amount = Bank Gross Amount - Bank MDR - Bank GST - Bank EMI", "Paytm Net Amount = Paytm Amount - Paytm Commission - Paytm GST", "Settlement Variance = HIMS Amount - Settlement Net Amount", "", "USAGE:", "1. Replace sample data with actual data from your reports", "2. Ensure date formats are consistent", "3. Use the SrikaraSettlementComparisonReport.py script to generate automated reports", "4. Use SrikaraConsolidatedReport.py to combine all location reports" ] for row_num, instruction in enumerate(instructions, 1): cell = instructions_ws.cell(row=row_num, column=1, value=instruction) if row_num == 1: # Title cell.font = Font(bold=True, size=14) elif instruction.startswith(("DATA SOURCES:", "COLUMN DESCRIPTIONS:", "FORMULAS:", "USAGE:")): cell.font = Font(bold=True) wb.save(template_file) print(f"Template created for {location_name}: {template_file}") return template_file # Example usage if __name__ == "__main__": consolidator = SrikaraConsolidatedReport() # Create templates for all 7 locations print("Creating templates for all 7 locations...") for location in consolidator.locations: consolidator.create_location_specific_template(location) # Example: Compile all location reports (uncomment when you have actual reports) # reports_folder = "." # Current directory # consolidator.compile_all_locations_report(reports_folder) print("Setup complete!") print("Templates created for all 7 Srikara locations") print("Use these templates to enter your settlement data") print("Then run the compilation script to generate consolidated reports")