import pandas as pd import numpy as np from datetime import datetime import re from typing import Tuple, Optional, Dict, Any import logging logger = logging.getLogger(__name__) def process_uploaded_files(open_invoices_file: str, payment_notification_file: str) -> Tuple[pd.DataFrame, pd.DataFrame]: """Process uploaded files and convert to canonical format""" open_invoices_df = pd.DataFrame() payment_notifications_df = pd.DataFrame() if open_invoices_file: open_invoices_df = process_open_invoices(open_invoices_file) if payment_notification_file: payment_notifications_df = process_payment_notification(payment_notification_file) return open_invoices_df, payment_notifications_df def process_open_invoices(file_path: str) -> pd.DataFrame: """Process Open Invoices file to canonical format""" try: # Read file if file_path.endswith('.xlsx'): df = pd.read_excel(file_path) else: df = pd.read_csv(file_path) # Drop empty rows df = df.dropna(how='all') # Create canonical format processed_data = [] for idx, row in df.iterrows(): # Handle potential missing columns row_dict = row.to_dict() record = { 'id': f"INV_{idx:06d}", 'document_type': str(row_dict.get('Transaction type', 'Invoice')), 'document_number': str(row_dict.get('Num', f"INV_{idx}")), 'reference_number': None, 'po_number': str(row_dict.get('P.O. Number', '')), 'invoice_date': parse_date(row_dict.get('Date')), 'due_date': parse_date(row_dict.get('Due date')), 'total_billed': parse_currency(row_dict.get('Amount', 0)), 'amount_outstanding': parse_currency(row_dict.get('Open balance', 0)), 'discount': 0.0, 'credit_advances_applied': 0.0, 'payment_amount': 0.0, 'ar_status': str(row_dict.get('A/R paid', 'Unpaid')), 'open_balance': parse_currency(row_dict.get('Open balance', 0)) } processed_data.append(record) return pd.DataFrame(processed_data) except Exception as e: logger.error(f"Error processing open invoices: {str(e)}") # Return empty dataframe with expected structure return pd.DataFrame(columns=[ 'id', 'document_type', 'document_number', 'reference_number', 'po_number', 'invoice_date', 'due_date', 'total_billed', 'amount_outstanding', 'discount', 'credit_advances_applied', 'payment_amount', 'ar_status', 'open_balance' ]) def process_payment_notification(file_path: str) -> pd.DataFrame: """Process Payment Notification file to canonical format""" try: # Read file if file_path.endswith('.xlsx'): df = pd.read_excel(file_path) else: df = pd.read_csv(file_path) # Drop empty rows df = df.dropna(how='all') # Create canonical format processed_data = [] for idx, row in df.iterrows(): # Handle potential missing columns row_dict = row.to_dict() record = { 'id': f"PAY_{idx:06d}", 'document_type': 'Payment', 'document_number': str(row_dict.get('Bill number', f"PAY_{idx}")), 'reference_number': str(row_dict.get('Reference', '')), 'po_number': None, 'invoice_date': parse_date(row_dict.get('Date')), 'due_date': None, 'total_billed': parse_currency(row_dict.get('Total billed', 0)), 'amount_outstanding': parse_currency(row_dict.get('Amount outstanding', 0)), 'discount': parse_currency(row_dict.get('Discount', 0)), 'credit_advances_applied': parse_currency(row_dict.get('Credit/advances applied', 0)), 'payment_amount': parse_currency(row_dict.get('Payment amount', 0)), 'ar_status': 'Paid', 'open_balance': parse_currency(row_dict.get('Amount outstanding', 0)) } processed_data.append(record) return pd.DataFrame(processed_data) except Exception as e: logger.error(f"Error processing payment notification: {str(e)}") # Return empty dataframe with expected structure return pd.DataFrame(columns=[ 'id', 'document_type', 'document_number', 'reference_number', 'po_number', 'invoice_date', 'due_date', 'total_billed', 'amount_outstanding', 'discount', 'credit_advances_applied', 'payment_amount', 'ar_status', 'open_balance' ]) def parse_date(date_value) -> Optional[datetime]: """Parse various date formats to datetime""" if pd.isna(date_value) or date_value == '' or date_value is None: return datetime.now() # Default to current date try: parsed = pd.to_datetime(date_value, errors='coerce') if pd.isna(parsed): return datetime.now() return parsed except: return datetime.now() def parse_currency(currency_value) -> float: """Parse currency values to float""" if pd.isna(currency_value) or currency_value is None: return 0.0 try: if isinstance(currency_value, (int, float)): return float(currency_value) # Remove currency symbols and commas cleaned = str(currency_value).replace('$', '').replace(',', '').replace(' ', '').strip() if cleaned == '': return 0.0 return float(cleaned) except: return 0.0