# src/matching_algorithms.py """ Enhanced matching algorithms for AR reconciliation system. Supports exact 3-field matching, manual matching, and comprehensive analytics. """ import pandas as pd from typing import Tuple, List, Dict, Any from datetime import datetime from src.storage import ( load_all_transactions, save_all_transactions, load_payment_notifications, save_payment_notifications, record_match, record_manual_match, get_vendor_summary, get_reconciliation_audit_trail ) def find_exact_matches() -> Tuple[List[Dict], List[Dict], List[Dict]]: """ Find exact matches between Open Invoices and Payment Notifications. Returns: matches: List of matched pairs with details unmatched_invoices: Invoices with no match unapplied_payments: Payments with no match """ invoices_df = load_all_transactions() payments_df = load_payment_notifications() # Filter for unpaid invoices and unapplied payments open_invoices = invoices_df[ (invoices_df['ar_status'] == 'Unpaid') & (invoices_df['transaction_type'].isin(['invoice', 'credit_memo'])) ] unapplied_payments = payments_df[payments_df['applied_status'] == 'Unapplied'] matches = [] matched_invoice_ids = set() matched_payment_ids = set() # Exact 3-field matching: document_number + invoice_date + amount for _, invoice in open_invoices.iterrows(): for _, payment in unapplied_payments.iterrows(): if (_is_exact_match(invoice, payment) and invoice['record_id'] not in matched_invoice_ids and payment['record_id'] not in matched_payment_ids): matches.append({ 'invoice_record_id': invoice['record_id'], 'payment_record_id': payment['record_id'], 'document_number': invoice['document_number'], 'invoice_date': invoice['invoice_date'], 'amount': invoice['total_billed'], 'vendor_name': invoice.get('vendor_name', ''), 'customer_name': invoice.get('customer_name', ''), 'invoice_upload_timestamp': invoice['upload_timestamp'], 'payment_upload_timestamp': payment['upload_timestamp'], 'match_type': 'auto' }) matched_invoice_ids.add(invoice['record_id']) matched_payment_ids.add(payment['record_id']) break # Find unmatched items unmatched_invoices = [ row.to_dict() for _, row in open_invoices.iterrows() if row['record_id'] not in matched_invoice_ids ] unapplied_payments_list = [ row.to_dict() for _, row in unapplied_payments.iterrows() if row['record_id'] not in matched_payment_ids ] return matches, unmatched_invoices, unapplied_payments_list def _is_exact_match(invoice: pd.Series, payment: pd.Series) -> bool: """ Check exact 3-field match: - document_number (case-insensitive, stripped) - invoice_date (exact date match) - amount (exact amount, tolerance 0.01 for floating point) """ # Document number match (case-insensitive, strip whitespace) doc_match = (str(invoice['document_number']).strip().lower() == str(payment['document_number']).strip().lower()) # Date match (compare date parts only, ignore time) if pd.notna(invoice['invoice_date']) and pd.notna(payment['invoice_date']): try: invoice_date = invoice['invoice_date'].date() if hasattr(invoice['invoice_date'], 'date') else invoice['invoice_date'] payment_date = payment['invoice_date'].date() if hasattr(payment['invoice_date'], 'date') else payment['invoice_date'] date_match = (invoice_date == payment_date) except: date_match = False else: date_match = False # Amount match (with small tolerance for floating point) try: invoice_amount = float(invoice['total_billed']) payment_amount = float(payment['payment_amount']) amount_match = abs(invoice_amount - payment_amount) < 0.01 except: amount_match = False return doc_match and date_match and amount_match def confirm_match(invoice_record_id: str, payment_record_id: str) -> Dict[str, Any]: """ Confirm a match - update AR Status to Paid and mark payment as Applied. Exact implementation per specification. Returns: Dictionary with success status and message """ try: invoices_df = load_all_transactions() payments_df = load_payment_notifications() # Find the records invoice_mask = invoices_df['record_id'] == invoice_record_id payment_mask = payments_df['record_id'] == payment_record_id if not invoice_mask.any(): return {'success': False, 'message': f'Invoice record {invoice_record_id} not found'} if not payment_mask.any(): return {'success': False, 'message': f'Payment record {payment_record_id} not found'} # Get payment amount and document details payment_amount = payments_df.loc[payment_mask, 'payment_amount'].iloc[0] document_number = invoices_df.loc[invoice_mask, 'document_number'].iloc[0] # Update invoice in All Transactions List per specification invoices_df.loc[invoice_mask, 'ar_status'] = 'Paid' invoices_df.loc[invoice_mask, 'amount_received'] = payment_amount # Update payment notification per specification payments_df.loc[payment_mask, 'applied_status'] = 'Applied' payments_df.loc[payment_mask, 'applied_to_invoice'] = invoice_record_id # Save changes save_all_transactions(invoices_df) save_payment_notifications(payments_df) # Record the match record_match(invoice_record_id, payment_record_id) return { 'success': True, 'message': f'✅ Successfully matched invoice {document_number} with payment ${payment_amount:.2f}' } except Exception as e: return {'success': False, 'message': f'Error confirming match: {str(e)}'} def manual_match_invoice_to_payment(invoice_record_id: str, payment_record_id: str) -> Dict[str, Any]: """ Manually match an invoice to a payment (admin override). Bypasses the exact 3-field matching rules. Returns: Dictionary with success status and message """ try: invoices_df = load_all_transactions() payments_df = load_payment_notifications() # Find the records invoice_mask = invoices_df['record_id'] == invoice_record_id payment_mask = payments_df['record_id'] == payment_record_id if not invoice_mask.any(): return {'success': False, 'message': f'Invoice record {invoice_record_id} not found'} if not payment_mask.any(): return {'success': False, 'message': f'Payment record {payment_record_id} not found'} # Get payment amount and document details payment_amount = payments_df.loc[payment_mask, 'payment_amount'].iloc[0] invoice_doc = invoices_df.loc[invoice_mask, 'document_number'].iloc[0] payment_doc = payments_df.loc[payment_mask, 'document_number'].iloc[0] invoice_amount = invoices_df.loc[invoice_mask, 'total_billed'].iloc[0] # Update invoice in All Transactions List invoices_df.loc[invoice_mask, 'ar_status'] = 'Paid' invoices_df.loc[invoice_mask, 'amount_received'] = payment_amount # Update payment notification payments_df.loc[payment_mask, 'applied_status'] = 'Applied' payments_df.loc[payment_mask, 'applied_to_invoice'] = invoice_record_id # Save changes save_all_transactions(invoices_df) save_payment_notifications(payments_df) # Record the manual match record_manual_match(invoice_record_id, payment_record_id) amount_diff = abs(invoice_amount - payment_amount) diff_note = f" (Amount difference: ${amount_diff:.2f})" if amount_diff >= 0.01 else "" return { 'success': True, 'message': f'✅ Manual match successful: Invoice {invoice_doc} → Payment {payment_doc} (${payment_amount:.2f}){diff_note}' } except Exception as e: return {'success': False, 'message': f'Error in manual match: {str(e)}'} def get_reconciliation_summary() -> Dict[str, Any]: """ Get summary statistics for reconciliation dashboard. """ invoices_df = load_all_transactions() payments_df = load_payment_notifications() # Invoice statistics total_invoices = len(invoices_df[invoices_df['transaction_type'].isin(['invoice', 'credit_memo'])]) paid_invoices = len(invoices_df[invoices_df['ar_status'] == 'Paid']) unpaid_invoices = len(invoices_df[invoices_df['ar_status'] == 'Unpaid']) # Payment statistics total_payments = len(payments_df) applied_payments = len(payments_df[payments_df['applied_status'] == 'Applied']) unapplied_payments = len(payments_df[payments_df['applied_status'] == 'Unapplied']) # Amount statistics total_invoice_amount = invoices_df['total_billed'].sum() total_payment_amount = payments_df['payment_amount'].sum() unapplied_payment_amount = payments_df[payments_df['applied_status'] == 'Unapplied']['payment_amount'].sum() return { 'invoices': { 'total': total_invoices, 'paid': paid_invoices, 'unpaid': unpaid_invoices, 'total_amount': total_invoice_amount }, 'payments': { 'total': total_payments, 'applied': applied_payments, 'unapplied': unapplied_payments, 'total_amount': total_payment_amount, 'unapplied_amount': unapplied_payment_amount } } def get_reconciliation_analytics() -> Dict[str, Any]: """ Enhanced analytics for dashboard with comprehensive metrics. """ invoices_df = load_all_transactions() payments_df = load_payment_notifications() # Invoice statistics invoice_records = invoices_df[invoices_df['transaction_type'].isin(['invoice', 'credit_memo'])] total_invoices = len(invoice_records) paid_invoices = len(invoice_records[invoice_records['ar_status'] == 'Paid']) unpaid_invoices = len(invoice_records[invoice_records['ar_status'] == 'Unpaid']) unpaid_invoices_amount = invoice_records[invoice_records['ar_status'] == 'Unpaid']['total_billed'].sum() paid_invoices_amount = invoice_records[invoice_records['ar_status'] == 'Paid']['total_billed'].sum() total_invoice_amount = invoice_records['total_billed'].sum() # Payment statistics total_payments = len(payments_df) applied_payments = len(payments_df[payments_df['applied_status'] == 'Applied']) unapplied_payments = len(payments_df[payments_df['applied_status'] == 'Unapplied']) total_payment_amount = payments_df['payment_amount'].sum() applied_payment_amount = payments_df[payments_df['applied_status'] == 'Applied']['payment_amount'].sum() unapplied_payment_amount = payments_df[payments_df['applied_status'] == 'Unapplied']['payment_amount'].sum() # Vendor summary vendor_summary = get_vendor_summary() # Match analytics audit_trail = get_reconciliation_audit_trail() auto_matches = len(audit_trail[audit_trail['matched_by'] == 'auto']) if not audit_trail.empty else 0 manual_matches = len(audit_trail[audit_trail['matched_by'] == 'manual']) if not audit_trail.empty else 0 # Performance metrics reconciliation_rate = (applied_payments / total_payments * 100) if total_payments > 0 else 0 payment_efficiency = (applied_payment_amount / total_payment_amount * 100) if total_payment_amount > 0 else 0 outstanding_balance = unpaid_invoices_amount - unapplied_payment_amount return { 'invoices': { 'total': total_invoices, 'paid': paid_invoices, 'unpaid': unpaid_invoices, 'paid_amount': paid_invoices_amount, 'unpaid_amount': unpaid_invoices_amount, 'total_amount': total_invoice_amount }, 'payments': { 'total': total_payments, 'applied': applied_payments, 'unapplied': unapplied_payments, 'total_amount': total_payment_amount, 'applied_amount': applied_payment_amount, 'unapplied_amount': unapplied_payment_amount }, 'vendor_summary': vendor_summary.to_dict('records') if not vendor_summary.empty else [], 'match_analytics': { 'auto_matches': auto_matches, 'manual_matches': manual_matches, 'total_matches': auto_matches + manual_matches }, 'reconciliation_rate': round(reconciliation_rate, 1), 'payment_efficiency': round(payment_efficiency, 1), 'outstanding_balance': outstanding_balance } def find_potential_matches(invoice_record_id: str) -> List[Dict]: """ Find potential payment matches for a specific invoice. Useful for manual reconciliation assistance. """ invoices_df = load_all_transactions() payments_df = load_payment_notifications() # Get the target invoice invoice = invoices_df[invoices_df['record_id'] == invoice_record_id] if invoice.empty: return [] invoice = invoice.iloc[0] # Find unapplied payments that match on any field unapplied_payments = payments_df[payments_df['applied_status'] == 'Unapplied'] potential_matches = [] for _, payment in unapplied_payments.iterrows(): score = 0 reasons = [] # Document number match if str(invoice['document_number']).strip().lower() == str(payment['document_number']).strip().lower(): score += 3 reasons.append('Document number matches exactly') elif str(invoice['document_number']).strip().lower() in str(payment['document_number']).strip().lower(): score += 1 reasons.append('Document number partial match') # Date match if (pd.notna(invoice['invoice_date']) and pd.notna(payment['invoice_date'])): try: invoice_date = invoice['invoice_date'].date() if hasattr(invoice['invoice_date'], 'date') else invoice['invoice_date'] payment_date = payment['invoice_date'].date() if hasattr(payment['invoice_date'], 'date') else payment['invoice_date'] if invoice_date == payment_date: score += 2 reasons.append('Date matches exactly') elif abs((invoice_date - payment_date).days) <= 7: score += 1 reasons.append('Date within 7 days') except: pass # Amount match try: invoice_amount = float(invoice['total_billed']) payment_amount = float(payment['payment_amount']) if abs(invoice_amount - payment_amount) < 0.01: score += 3 reasons.append('Amount matches exactly') elif abs(invoice_amount - payment_amount) / invoice_amount < 0.1: # Within 10% score += 1 reasons.append('Amount within 10%') except: pass if score > 0: # At least one field matches potential_matches.append({ 'payment_record_id': payment['record_id'], 'document_number': payment['document_number'], 'invoice_date': payment['invoice_date'], 'payment_amount': payment['payment_amount'], 'vendor_name': payment.get('vendor_name', ''), 'score': score, 'reasons': reasons, 'is_exact_match': score >= 8 # High threshold for exact match }) # Sort by score (descending) and then by amount difference potential_matches.sort(key=lambda x: (-x['score'], abs(float(invoice['total_billed']) - x['payment_amount']))) return potential_matches def get_unreconciled_items() -> Dict[str, Any]: """ Get all unreconciled items for manual matching interface. """ _, unmatched_invoices, unapplied_payments = find_exact_matches() return { 'unmatched_invoices': unmatched_invoices, 'unapplied_payments': unapplied_payments }