DocShopProE-Cloud / src /storage.py
jonels1988's picture
Update src/storage.py
34cdccf verified
Raw History Blame Contribute Delete
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