PayTrace_Merchant / src /matching_algorithms.py
jonels1988's picture
Update src/matching_algorithms.py
1a17d99 verified
Raw History Blame Contribute Delete
16.9 kB
# 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
}