Spaces:
Sleeping
Sleeping
Download src/storage.py from jonels1988/PayTrace_Merchant: direct link, hf CLI and curl.
- Browser
- Download file 11.5 kB
-
https://huggingface.co/spaces/jonels1988/PayTrace_Merchant/resolve/main/src/storage.py
- Command line
-
hf download hf://spaces/jonels1988/PayTrace_Merchant/src/storage.py
-
curl -L -o storage.py https://huggingface.co/spaces/jonels1988/PayTrace_Merchant/resolve/main/src/storage.py
11.5 kB
| # src/storage.py | |
| """ | |
| Enhanced disk-based storage for the AR reconciliation system. | |
| Supports manual matching, audit trails, and comprehensive analytics. | |
| """ | |
| from pathlib import Path | |
| from typing import Tuple, List, Dict, Any | |
| from datetime import datetime | |
| import uuid | |
| import pandas as pd | |
| DATA_DIR = Path("data_store") | |
| DATA_DIR.mkdir(exist_ok=True) | |
| # Master tables | |
| ALL_TRANSACTIONS_PATH = DATA_DIR / "all_transactions.parquet" | |
| PAYMENT_NOTIFICATIONS_PATH = DATA_DIR / "payment_notifications.parquet" | |
| MATCHES_PATH = DATA_DIR / "matches.parquet" | |
| def load_all_transactions() -> pd.DataFrame: | |
| """Load All Transactions List - central database tab.""" | |
| if ALL_TRANSACTIONS_PATH.exists(): | |
| df = pd.read_parquet(ALL_TRANSACTIONS_PATH) | |
| return _ensure_all_transactions_schema(df) | |
| return _create_empty_all_transactions() | |
| def load_payment_notifications() -> pd.DataFrame: | |
| """Load Payment Notifications for reconciliation.""" | |
| if PAYMENT_NOTIFICATIONS_PATH.exists(): | |
| df = pd.read_parquet(PAYMENT_NOTIFICATIONS_PATH) | |
| return _ensure_payment_notifications_schema(df) | |
| return _create_empty_payment_notifications() | |
| def load_matches() -> pd.DataFrame: | |
| """Load reconciliation match history.""" | |
| if MATCHES_PATH.exists(): | |
| return pd.read_parquet(MATCHES_PATH) | |
| return _create_empty_matches_df() | |
| def save_all_transactions(df: pd.DataFrame) -> None: | |
| """Save to All Transactions List.""" | |
| df = _ensure_all_transactions_schema(df) | |
| df.to_parquet(ALL_TRANSACTIONS_PATH, index=False) | |
| def save_payment_notifications(df: pd.DataFrame) -> None: | |
| """Save Payment Notifications.""" | |
| df = _ensure_payment_notifications_schema(df) | |
| df.to_parquet(PAYMENT_NOTIFICATIONS_PATH, index=False) | |
| def save_matches(df: pd.DataFrame) -> None: | |
| """Save reconciliation matches.""" | |
| df = _ensure_matches_schema(df) | |
| df.to_parquet(MATCHES_PATH, index=False) | |
| def load_all() -> Tuple[pd.DataFrame, pd.DataFrame]: | |
| """Return (invoices_df, payments_df).""" | |
| return load_all_transactions(), load_payment_notifications() | |
| def save_all(invoices_df: pd.DataFrame, payments_df: pd.DataFrame) -> None: | |
| """Save both invoices and payments.""" | |
| save_all_transactions(invoices_df) | |
| save_payment_notifications(payments_df) | |
| def record_match(invoice_record_id: str, payment_record_id: str, matched_by: str = "auto") -> None: | |
| """Record a successful reconciliation match.""" | |
| matches_df = load_matches() | |
| # Check if this match already exists | |
| existing_match = matches_df[ | |
| (matches_df['invoice_record_id'] == invoice_record_id) & | |
| (matches_df['payment_record_id'] == payment_record_id) | |
| ] | |
| if not existing_match.empty: | |
| return # Match already recorded | |
| new_match = pd.DataFrame([{ | |
| 'match_id': str(uuid.uuid4()), | |
| 'invoice_record_id': invoice_record_id, | |
| 'payment_record_id': payment_record_id, | |
| 'matched_at': datetime.utcnow(), | |
| 'matched_by': matched_by | |
| }]) | |
| matches_df = pd.concat([matches_df, new_match], ignore_index=True) | |
| save_matches(matches_df) | |
| def record_manual_match(invoice_record_id: str, payment_record_id: str) -> None: | |
| """Record a manual reconciliation match.""" | |
| record_match(invoice_record_id, payment_record_id, "manual") | |
| # --------------------------------------------------------------------- | |
| # Schema Management | |
| # --------------------------------------------------------------------- | |
| def _create_empty_all_transactions() -> pd.DataFrame: | |
| """Empty All Transactions List with exact specified columns.""" | |
| return pd.DataFrame({ | |
| 'record_id': pd.Series([], dtype='str'), | |
| 'upload_timestamp': pd.Series([], dtype='datetime64[ns]'), | |
| 'document_number': pd.Series([], dtype='str'), | |
| 'invoice_date': pd.Series([], dtype='datetime64[ns]'), | |
| 'total_billed': pd.Series([], dtype='float64'), | |
| 'vendor_name': pd.Series([], dtype='str'), | |
| 'customer_name': pd.Series([], dtype='str'), | |
| # Added columns per specification | |
| 'ar_status': pd.Series([], dtype='str'), # 'Paid' / 'Unpaid' | |
| 'amount_received': pd.Series([], dtype='float64'), | |
| 'transaction_type': pd.Series([], dtype='str') # 'invoice', 'credit_memo', etc. | |
| }) | |
| def _create_empty_payment_notifications() -> pd.DataFrame: | |
| """Empty Payment Notifications with exact specified columns.""" | |
| return pd.DataFrame({ | |
| 'record_id': pd.Series([], dtype='str'), | |
| 'upload_timestamp': pd.Series([], dtype='datetime64[ns]'), | |
| 'payment_notification_id': pd.Series([], dtype='str'), # For grouping | |
| 'document_number': pd.Series([], dtype='str'), | |
| 'invoice_date': pd.Series([], dtype='datetime64[ns]'), | |
| 'payment_amount': pd.Series([], dtype='float64'), | |
| 'vendor_name': pd.Series([], dtype='str'), | |
| # Status columns | |
| 'applied_status': pd.Series([], dtype='str'), # 'Applied' / 'Unapplied' | |
| 'applied_to_invoice': pd.Series([], dtype='str') # Links to invoice record_id | |
| }) | |
| def _create_empty_matches_df() -> pd.DataFrame: | |
| """Create empty matches DataFrame for audit trail.""" | |
| return pd.DataFrame({ | |
| 'match_id': pd.Series([], dtype='str'), | |
| 'invoice_record_id': pd.Series([], dtype='str'), | |
| 'payment_record_id': pd.Series([], dtype='str'), | |
| 'matched_at': pd.Series([], dtype='datetime64[ns]'), | |
| 'matched_by': pd.Series([], dtype='str') # 'auto' or 'manual' | |
| }) | |
| def _ensure_all_transactions_schema(df: pd.DataFrame) -> pd.DataFrame: | |
| """Ensure All Transactions List has exact specified columns.""" | |
| if df.empty: | |
| return _create_empty_all_transactions() | |
| df = df.copy() | |
| # Required columns per specification | |
| if 'record_id' not in df.columns: | |
| df['record_id'] = [str(uuid.uuid4()) for _ in range(len(df))] | |
| if 'upload_timestamp' not in df.columns: | |
| df['upload_timestamp'] = datetime.utcnow() | |
| if 'ar_status' not in df.columns: | |
| df['ar_status'] = 'Unpaid' | |
| if 'amount_received' not in df.columns: | |
| df['amount_received'] = 0.0 | |
| if 'transaction_type' not in df.columns: | |
| df['transaction_type'] = 'invoice' # Default | |
| return df | |
| def _ensure_payment_notifications_schema(df: pd.DataFrame) -> pd.DataFrame: | |
| """Ensure Payment Notifications have exact specified columns.""" | |
| if df.empty: | |
| return _create_empty_payment_notifications() | |
| df = df.copy() | |
| # Required columns per specification | |
| if 'record_id' not in df.columns: | |
| df['record_id'] = [str(uuid.uuid4()) for _ in range(len(df))] | |
| if 'upload_timestamp' not in df.columns: | |
| df['upload_timestamp'] = datetime.utcnow() | |
| if 'payment_notification_id' not in df.columns: | |
| # Generate group IDs for Tab 4 summary | |
| df['payment_notification_id'] = df['upload_timestamp'].dt.strftime('PN-%Y%m%d-') + df.index.astype(str) | |
| if 'applied_status' not in df.columns: | |
| df['applied_status'] = 'Unapplied' | |
| if 'applied_to_invoice' not in df.columns: | |
| df['applied_to_invoice'] = '' | |
| return df | |
| def _ensure_matches_schema(df: pd.DataFrame) -> pd.DataFrame: | |
| """Ensure matches DataFrame has correct schema.""" | |
| expected_columns = ['match_id', 'invoice_record_id', 'payment_record_id', 'matched_at', 'matched_by'] | |
| for col in expected_columns: | |
| if col not in df.columns: | |
| if col == 'match_id': | |
| df[col] = [str(uuid.uuid4()) for _ in range(len(df))] | |
| elif col == 'matched_at': | |
| df[col] = datetime.utcnow() | |
| elif col == 'matched_by': | |
| df[col] = 'auto' | |
| else: | |
| df[col] = None | |
| return df | |
| # --------------------------------------------------------------------- | |
| # Utility functions for the enhanced system | |
| # --------------------------------------------------------------------- | |
| def get_payment_notification_summary() -> pd.DataFrame: | |
| """Generate summary for Tab 4 - grouped by payment_notification_id.""" | |
| payments_df = load_payment_notifications() | |
| if payments_df.empty: | |
| return _create_empty_pn_summary() | |
| summary = payments_df.groupby('payment_notification_id').agg({ | |
| 'upload_timestamp': 'min', | |
| 'record_id': 'count', | |
| 'payment_amount': 'sum', | |
| 'applied_status': lambda x: (x == 'Applied').sum() | |
| }).reset_index() | |
| summary.columns = ['payment_notification_id', 'upload_timestamp', 'total_count', 'total_value', 'applied_count'] | |
| summary['unapplied_count'] = summary['total_count'] - summary['applied_count'] | |
| return summary | |
| def get_pn_details(payment_notification_id: str) -> pd.DataFrame: | |
| """Get details for a specific Payment Notification (drill-down).""" | |
| payments_df = load_payment_notifications() | |
| return payments_df[payments_df['payment_notification_id'] == payment_notification_id] | |
| def get_reconciliation_audit_trail() -> pd.DataFrame: | |
| """Get complete audit trail of all matches.""" | |
| matches_df = load_matches() | |
| invoices_df = load_all_transactions() | |
| payments_df = load_payment_notifications() | |
| if matches_df.empty: | |
| return pd.DataFrame() | |
| # Enrich match data with invoice and payment details | |
| audit_trail = matches_df.merge( | |
| invoices_df[['record_id', 'document_number', 'total_billed', 'vendor_name']], | |
| left_on='invoice_record_id', | |
| right_on='record_id', | |
| how='left', | |
| suffixes=('', '_invoice') | |
| ).merge( | |
| payments_df[['record_id', 'document_number', 'payment_amount', 'vendor_name']], | |
| left_on='payment_record_id', | |
| right_on='record_id', | |
| how='left', | |
| suffixes=('_invoice', '_payment') | |
| ) | |
| # Select and rename columns for clarity | |
| audit_trail = audit_trail[[ | |
| 'matched_at', 'matched_by', | |
| 'document_number_invoice', 'total_billed', 'vendor_name_invoice', | |
| 'document_number_payment', 'payment_amount', 'vendor_name_payment' | |
| ]] | |
| audit_trail.columns = [ | |
| 'matched_at', 'matched_by', | |
| 'invoice_number', 'invoice_amount', 'invoice_vendor', | |
| 'payment_number', 'payment_amount', 'payment_vendor' | |
| ] | |
| return audit_trail | |
| def _create_empty_pn_summary() -> pd.DataFrame: | |
| """Empty Payment Notification Summary.""" | |
| return pd.DataFrame({ | |
| 'payment_notification_id': pd.Series([], dtype='str'), | |
| 'upload_timestamp': pd.Series([], dtype='datetime64[ns]'), | |
| 'total_count': pd.Series([], dtype='int64'), | |
| 'total_value': pd.Series([], dtype='float64'), | |
| 'applied_count': pd.Series([], dtype='int64'), | |
| 'unapplied_count': pd.Series([], dtype='int64') | |
| }) | |
| def get_vendor_summary() -> pd.DataFrame: | |
| """Get vendor-level summary for analytics.""" | |
| payments_df = load_payment_notifications() | |
| if payments_df.empty: | |
| return pd.DataFrame() | |
| vendor_summary = payments_df.groupby('vendor_name').agg({ | |
| 'payment_amount': ['count', 'sum'], | |
| 'applied_status': lambda x: (x == 'Applied').sum() | |
| }).reset_index() | |
| vendor_summary.columns = ['vendor_name', 'payment_count', 'total_payment_amount', 'applied_count'] | |
| vendor_summary['unapplied_count'] = vendor_summary['payment_count'] - vendor_summary['applied_count'] | |
| vendor_summary['reconciliation_rate'] = (vendor_summary['applied_count'] / vendor_summary['payment_count'] * 100).round(1) | |
| return vendor_summary |