Spaces:
Running
Running
Download src/matching_algorithms.py from jonels1988/DocShopProE-Cloud: direct link, hf CLI and curl.
- Browser
- Download file 16.9 kB
-
https://huggingface.co/spaces/jonels1988/DocShopProE-Cloud/resolve/main/src/matching_algorithms.py
- Command line
-
hf download hf://spaces/jonels1988/DocShopProE-Cloud/src/matching_algorithms.py
-
curl -L -o matching_algorithms.py https://huggingface.co/spaces/jonels1988/DocShopProE-Cloud/resolve/main/src/matching_algorithms.py
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 | |
| } |