FEgroup/reconciliation
0
1import gradio as gr2import PyPDF23import pandas as pd4import re5import io6import os7import tempfile # Import the tempfile module8 9def extract_bank_statement_data(pdf_file):10 """11 Extracts bank statement data (Opening Balance, Closing Balance, Total Debit, Total Credit)12 for specified accounts from a PDF file.13 14 Args:15 pdf_file: A Gradio File object representing the uploaded PDF.16 17 Returns:18 A string representing the path to the generated Excel file,19 or None if an error occurs.20 """21 extracted_data = []22 23 # List of account names to search for in the PDF.24 # These names are provided by the user and are used to identify individual statements.25 account_names = [26 "THE SMART SCHOOL NAZIMABAD CAMPUS-I",27 "THE SMART SCHOOL (NAZIMABAD CAMPUS-II-B)",28 "THE SMART SCHOOL NAZIMABAD CAMPUS-IV",29 "THE SMART SCHOOL KARIMABAD CAMPUS",30 "THE SMART SCHOOL F.B. AREA CAMPUS II",31 "THE SMART SCHOOL FB AREA CAMPUS III",32 "UNITED CHARTER SCHOOLS",33 "STAR LINKS INTERNATIONAL SCHOOL",34 "THE SMART SCHOOL NAZIMABAD CAMPUS II",35 "MUHAMMAD FAROOQ EDHI",36 "ERUM MUHAMMAD FAROOQ EDHI",37 "THE LEARNING CIRCLE",38 "THE MAP SCHOOL"39 ]40 41 # Initialize output_filepath to None, in case of early exit or error42 output_filepath = None43 44 try:45 # Read the PDF content using PyPDF2.46 # pdf_file.name provides the path to the temporary uploaded file.47 pdf_reader = PyPDF2.PdfReader(pdf_file.name)48 full_text = ""49 # Iterate through each page and extract its text content.50 for page_num in range(len(pdf_reader.pages)):51 full_text += pdf_reader.pages[page_num].extract_text() + "\n"52 53 print("--- Full text extracted from PDF ---")54 print(full_text[:1000]) # Print first 1000 characters for debugging55 print("------------------------------------")56 57 # Define a regular expression to capture numbers, allowing for commas and decimal places.58 # This pattern matches numbers like "1,234,567.89" or "123.45" or "123".59 number_regex = r"(\d{1,3}(?:,\d{3})*(?:\.\d{2})?)"60 61 # Helper function to clean and convert numbers to integers62 def clean_and_convert_to_int(value):63 if isinstance(value, str):64 # Remove commas and convert to float, then to int65 try:66 return int(float(value.replace(",", "")))67 except ValueError:68 return 0 # Return 0 if conversion fails69 return 0 # Return 0 for non-string or invalid values70 71 # Iterate through each predefined account name to find its details in the extracted text.72 for i, account_name in enumerate(account_names):73 account_info = {74 "Account Name": account_name,75 "Opening Balance": 0, # Default to 076 "Closing Balance": 0, # Default to 077 "Total Debit": 0, # Default to 078 "Total Credit": 0 # Default to 079 }80 81 # Escape special characters in the account name for safe use in regex.82 escaped_account_name = re.escape(account_name)83 84 # Find the first occurrence of the current account name in the full text.85 current_account_start_match = re.search(escaped_account_name, full_text)86 87 # If the exact account name isn't found, try a more flexible match88 # by removing parentheses, as names in PDF might vary slightly.89 if not current_account_start_match:90 flexible_account_name = account_name.replace("(", "").replace(")", "").strip()91 escaped_flexible_account_name = re.escape(flexible_account_name)92 current_account_start_match = re.search(escaped_flexible_account_name, full_text)93 94 # If the account name is still not found, add it to the results with 0 and continue.95 if not current_account_start_match:96 print(f"Account '{account_name}' not found in PDF.")97 extracted_data.append(account_info)98 continue99 100 current_account_start_index = current_account_start_match.start()101 102 # Determine the end of the current account's statement block.103 # This is typically the start of the next *different* account name, or the end of the document.104 next_account_start_index = len(full_text) # Default to end of document105 106 # Search for the earliest start of any *other* account name after the current account's start.107 # This helps to define the boundary of the current account's statement.108 # We search only for subsequent account names in the list to define the section.109 for j in range(i + 1, len(account_names)):110 other_name = account_names[j]111 escaped_other_name = re.escape(other_name)112 113 # Search for the other account name *after* the current account's starting position.114 # The search starts from `current_account_start_index + len(current_account_start_match.group(0))`115 # to avoid matching the current account name itself again.116 other_name_match = re.search(escaped_other_name, full_text[current_account_start_index + len(current_account_start_match.group(0)):])117 118 if other_name_match:119 # Calculate the absolute index of the found 'other_name'.120 absolute_other_name_start = current_account_start_index + len(current_account_start_match.group(0)) + other_name_match.start()121 # Update `next_account_start_index` if a closer 'other_name' is found.122 if absolute_other_name_start < next_account_start_index:123 next_account_start_index = absolute_other_name_start124 break # Found the next account, no need to check further125 126 # Extract the relevant section of text for the current account.127 account_section_text = full_text[current_account_start_index:next_account_start_index]128 print(f"\n--- Section for '{account_name}' ---")129 print(account_section_text[:500]) # Print first 500 chars of section for debugging130 print("------------------------------------")131 132 # Extract Opening Balance: Look for "Opening Balance" followed by a number.133 opening_balance_match = re.search(r"Opening Balance\s*" + number_regex, account_section_text)134 if opening_balance_match:135 account_info["Opening Balance"] = clean_and_convert_to_int(opening_balance_match.group(1))136 print(f" Opening Balance found: {account_info['Opening Balance']}")137 138 # Initialize closing_balance_value139 closing_balance_value = 0140 141 # Find the start of the "Totals:" section.142 totals_start_match = re.search(r"Totals:", account_section_text, re.IGNORECASE)143 section_before_totals = account_section_text144 if totals_start_match:145 section_before_totals = account_section_text[:totals_start_match.start()]146 147 # Strategy 1: Look for "Closing Balance" followed by a number (flexible whitespace)148 # This covers cases like "Closing Balance\n3,696,065.87"149 direct_closing_balance_match = re.search(r"Closing Balance\s*([\s\S]*?)(" + number_regex + r")", section_before_totals)150 if direct_closing_balance_match:151 closing_balance_value = clean_and_convert_to_int(direct_closing_balance_match.group(direct_closing_balance_match.lastindex))152 print(f" Closing Balance found (direct after keyword): {closing_balance_value}")153 else:154 # Strategy 2: Look for a number immediately preceding "Closing Balance"155 # This covers cases like "3,696,065.87 Closing Balance"156 preceding_closing_balance_match = re.search(r"(" + number_regex + r")\s*Closing Balance", section_before_totals)157 if preceding_closing_balance_match:158 closing_balance_value = clean_and_convert_to_int(preceding_closing_balance_match.group(1))159 print(f" Closing Balance found (preceding keyword): {closing_balance_value}")160 else:161 # Strategy 3: Find the last number in the section before "Totals:"162 # This is the most general fallback, as per user's latest hint.163 all_numbers_before_totals = re.findall(number_regex, section_before_totals)164 if all_numbers_before_totals:165 closing_balance_value = clean_and_convert_to_int(all_numbers_before_totals[-1])166 print(f" Closing Balance found (last number before Totals:): {closing_balance_value}")167 else:168 print(" No closing balance found even with advanced fallbacks.")169 170 account_info["Closing Balance"] = closing_balance_value171 172 173 # Extract Total Debit and Total Credit from the "Totals:" line.174 # This regex captures the content after "Totals:" up to the newline.175 # It's made more flexible to handle potential variations in spacing or missing values.176 totals_line_match = re.search(r"Totals:\s*([\d.,\s]+)", account_section_text, re.IGNORECASE)177 if totals_line_match:178 totals_line_content = totals_line_match.group(1)179 print(f" Totals line content: '{totals_line_content}'")180 # Find all numbers within the captured "Totals:" line content.181 numbers_in_totals_line = re.findall(number_regex, totals_line_content)182 183 if len(numbers_in_totals_line) >= 2:184 # If two or more numbers are found, assume the first is Debit, the second is Credit.185 account_info["Total Debit"] = clean_and_convert_to_int(numbers_in_totals_line[0])186 account_info["Total Credit"] = clean_and_convert_to_int(numbers_in_totals_line[1])187 print(f" Total Debit: {account_info['Total Debit']}, Total Credit: {account_info['Total Credit']}")188 elif len(numbers_in_totals_line) == 1:189 # If only one number is found, it's typically the Debit, and Credit is 0.190 account_info["Total Debit"] = clean_and_convert_to_int(numbers_in_totals_line[0])191 account_info["Total Credit"] = 0 # Default to 0 if only debit is present192 print(f" Only one total found. Total Debit: {account_info['Total Debit']}, Total Credit set to 0")193 else:194 print(" No numbers found in Totals line.")195 else:196 print(" 'Totals:' line not found in section.")197 198 # Add the extracted information for the current account to the list.199 extracted_data.append(account_info)200 201 # Create a Pandas DataFrame from the extracted data.202 df = pd.DataFrame(extracted_data)203 204 # Create a temporary file to save the Excel data205 # tempfile.NamedTemporaryFile creates a file that is automatically deleted when closed206 with tempfile.NamedTemporaryFile(delete=False, suffix=".xlsx") as tmp_file:207 output_filepath = tmp_file.name208 with pd.ExcelWriter(output_filepath, engine='openpyxl') as writer:209 df.to_excel(writer, index=False, sheet_name='Bank Statements')210 211 print(f"Excel file saved to: {output_filepath}")212 return output_filepath213 214 except Exception as e:215 # Handle any errors during PDF processing.216 print(f"Error processing PDF: {e}")217 # Create a DataFrame with an error message218 error_df = pd.DataFrame([{"Account Name": "Processing Error", "Opening Balance": str(e), "Closing Balance": 0, "Total Debit": 0, "Total Credit": 0}])219 220 # Save the error DataFrame to a temporary Excel file221 with tempfile.NamedTemporaryFile(delete=False, suffix=".xlsx") as tmp_file:222 output_filepath = tmp_file.name223 with pd.ExcelWriter(output_filepath, engine='openpyxl') as writer:224 error_df.to_excel(writer, index=False, sheet_name='Error Report')225 226 print(f"Error report saved to: {output_filepath}")227 return output_filepath # Return the path to the error report file228 229# Define the Gradio interface for the application.230iface = gr.Interface(231 fn=extract_bank_statement_data, # The function to be called when the user interacts with the UI.232 inputs=gr.File(label="Upload Merged Bank Statement PDF"), # Input component: a file upload button.233 outputs=gr.File(label="Download Extracted Data (Excel)"), # Output component: a file download link.234 title="Bank Statement Data Extractor", # Title displayed on the Gradio app.235 description="Upload a merged PDF of multiple bank statements to extract Opening Balance, Closing Balance, Total Debit, and Total Credit for each account into an Excel file." # Description for the app.236)237 238# Launch the Gradio interface if the script is run directly.239if __name__ == "__main__":240 iface.launch()241