Spaces:
Sleeping
Sleeping
File size: 11,540 Bytes
ae77cb1 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 | # 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 |