Spaces:
Sleeping
Sleeping
Download app.py from MDBG-user/Doc_ShopPro_Portal: direct link, hf CLI and curl.
- Browser
- Download file 68.5 kB
-
https://huggingface.co/spaces/MDBG-user/Doc_ShopPro_Portal/resolve/main/app.py
- Command line
-
hf download hf://spaces/MDBG-user/Doc_ShopPro_Portal/app.py
-
curl -L -o app.py https://huggingface.co/spaces/MDBG-user/Doc_ShopPro_Portal/resolve/main/app.py
68.5 kB
import os
import tempfile
import uuid
from datetime import datetime
from typing import Optional, List, Dict, Any
import pandas as pd
import gradio as gr
from src.storage import (
load_all_transactions, save_all_transactions,
load_payment_notifications, save_payment_notifications,
get_payment_notification_summary, get_pn_details
)
from src.matching_algorithms import (
find_exact_matches, confirm_match, get_reconciliation_summary,
manual_match_invoice_to_payment, get_reconciliation_analytics
)
# ---------------------------------------------------------------------
# Data Synchronization Helper
# ---------------------------------------------------------------------
def _sync_data_after_matching():
"""
Synchronize data across all storage after matching operations.
This ensures all tabs show consistent, updated information.
"""
try:
# Load current data
all_transactions = load_all_transactions()
payment_notifications = load_payment_notifications()
# Update invoice statuses based on payment applications
for _, payment in payment_notifications.iterrows():
if payment['applied_status'] == 'Applied' and payment.get('applied_to_invoice'):
# Find the invoice this payment was applied to
invoice_mask = all_transactions['record_id'] == payment['applied_to_invoice']
if invoice_mask.any():
all_transactions.loc[invoice_mask, 'ar_status'] = 'Paid'
all_transactions.loc[invoice_mask, 'amount_received'] = payment['payment_amount']
# Save synchronized data
save_all_transactions(all_transactions)
save_payment_notifications(payment_notifications)
return True
except Exception as e:
print(f"Error syncing data: {e}")
return False
# ---------------------------------------------------------------------
# Data Normalization Helpers
# ---------------------------------------------------------------------
def _read_uploaded_file(file_obj) -> Optional[pd.DataFrame]:
"""Read uploaded file (Excel or CSV) with robust CSV handling."""
if file_obj is None:
return None
path = file_obj.name
_, ext = os.path.splitext(path.lower())
try:
if ext in [".xlsx", ".xls"]:
return pd.read_excel(path)
elif ext == ".csv":
# First, try to detect the delimiter
with open(path, 'r', encoding='utf-8') as f:
first_line = f.readline()
# Check if semicolon delimiter (your format)
if ';' in first_line:
print("Detected semicolon-delimited CSV")
df = pd.read_csv(path, delimiter=';', encoding='utf-8')
else:
df = pd.read_csv(path, encoding='utf-8')
return df
else:
raise ValueError(f"Unsupported file type: {ext}")
except Exception as e:
raise ValueError(f"Error reading file: {str(e)}")
def _normalize_invoice_data(df: pd.DataFrame) -> pd.DataFrame:
"""Normalize uploaded invoice data to canonical schema."""
if df is None or df.empty:
return pd.DataFrame()
df = df.copy()
# Generate system fields
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"
# Standardize core fields - handle your sample format
if "document_number" not in df.columns:
for cand in ["Num", "Invoice", "Invoice #", "Bill number", "Bill Number", "Document Number"]:
if cand in df.columns:
df["document_number"] = df[cand].astype(str)
break
else:
df["document_number"] = ""
if "invoice_date" not in df.columns:
for cand in ["Date", "Invoice Date", "Bill Date", "Document Date"]:
if cand in df.columns:
# Handle MM/DD/YYYY format
try:
df["invoice_date"] = pd.to_datetime(df[cand], format='%m/%d/%Y', errors='coerce')
if df["invoice_date"].isna().any():
df["invoice_date"] = pd.to_datetime(df[cand], errors='coerce')
except:
df["invoice_date"] = pd.to_datetime(df[cand], errors='coerce')
break
else:
df["invoice_date"] = pd.NaT
if "total_billed" not in df.columns:
for cand in ["Total billed", "Amount", "Invoice Amount", "Total", "Amount Due"]:
if cand in df.columns:
# Handle negative amounts and dollar signs
amount_series = df[cand].astype(str)
amount_series = amount_series.str.replace('$', '', regex=False)
amount_series = amount_series.str.replace(',', '', regex=False)
df["total_billed"] = pd.to_numeric(amount_series, errors="coerce").fillna(0.0)
break
else:
df["total_billed"] = 0.0
# Handle transaction types from your sample
if "Transaction type" in df.columns:
df["transaction_type"] = df["Transaction type"].str.lower().replace({
'credit memo': 'credit_memo',
'invoice': 'invoice'
})
# Optional fields
if "vendor_name" not in df.columns:
df["vendor_name"] = "Vendor"
if "customer_name" not in df.columns:
df["customer_name"] = "Customer"
return df
def _normalize_payment_data(df: pd.DataFrame) -> pd.DataFrame:
"""Normalize uploaded payment data to canonical schema."""
if df is None or df.empty:
return pd.DataFrame()
df = df.copy()
# Generate system fields
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 "applied_status" not in df.columns:
df["applied_status"] = "Unapplied"
if "applied_to_invoice" not in df.columns:
df["applied_to_invoice"] = ""
if "payment_notification_id" not in df.columns:
# Generate unique PN ID for this upload
base_id = f"PN-{datetime.utcnow().strftime('%Y%m%d-%H%M%S')}"
df["payment_notification_id"] = [f"{base_id}-{i}" for i in range(len(df))]
# Standardize core fields - handle your specific CSV format
if "document_number" not in df.columns:
for cand in ["Bill number", "Bill Number", "Num", "Invoice", "Invoice #", "Reference"]:
if cand in df.columns:
df["document_number"] = df[cand].astype(str)
break
else:
df["document_number"] = ""
if "invoice_date" not in df.columns:
for cand in ["Date", "Invoice Date", "Payment Date", "Document Date"]:
if cand in df.columns:
# Handle MM/DD/YYYY format specifically
try:
df["invoice_date"] = pd.to_datetime(df[cand], format='%m/%d/%Y', errors='coerce')
# If that fails, try without format
if df["invoice_date"].isna().any():
df["invoice_date"] = pd.to_datetime(df[cand], errors='coerce')
except:
df["invoice_date"] = pd.to_datetime(df[cand], errors='coerce')
break
else:
df["invoice_date"] = pd.NaT
if "payment_amount" not in df.columns:
# Handle your specific CSV format with dollar signs and parentheses for negatives
for cand in ["Payment amount", "Payment Amount", "Amount", "Total", "Amount Paid"]:
if cand in df.columns:
# Convert to string and handle negative amounts in parentheses
payment_series = df[cand].astype(str)
# Remove dollar signs and commas
payment_series = payment_series.str.replace('$', '', regex=False)
payment_series = payment_series.str.replace(',', '', regex=False)
# Handle negative amounts in parentheses: $(123.45) -> -123.45
def convert_negative(value):
if isinstance(value, str) and value.startswith('(') and value.endswith(')'):
return '-' + value[1:-1]
return value
payment_series = payment_series.apply(convert_negative)
# Convert to numeric
df["payment_amount"] = pd.to_numeric(payment_series, errors="coerce").fillna(0.0)
break
else:
df["payment_amount"] = 0.0
# Optional fields
if "vendor_name" not in df.columns:
df["vendor_name"] = "Vendor" # Default vendor name
return df
# ---------------------------------------------------------------------
# Upload Handlers
# ---------------------------------------------------------------------
def handle_invoice_upload(open_invoices_file):
"""Process Open Invoices upload and add to All Transactions List."""
if open_invoices_file is None:
return "Please select a file to upload", pd.DataFrame()
try:
# Read and normalize the uploaded file
df_raw = _read_uploaded_file(open_invoices_file)
df_normalized = _normalize_invoice_data(df_raw)
# Load existing transactions and append new ones
existing_df = load_all_transactions()
updated_df = pd.concat([existing_df, df_normalized], ignore_index=True)
# Save to persistent storage
save_all_transactions(updated_df)
message = f"β
Successfully uploaded {len(df_normalized)} invoices to All Transactions List"
preview = df_normalized[['document_number', 'invoice_date', 'total_billed', 'ar_status']].head(10)
return message, preview
except Exception as e:
return f"β Error uploading file: {str(e)}", pd.DataFrame()
def handle_payment_upload_with_reference(
payment_notification_file,
reference_number: str,
check_number: str,
reference_date: str,
total_payment_amount: float
):
"""Process Payment Notification upload with reference details and validation."""
if payment_notification_file is None:
return "Please select a file to upload", pd.DataFrame()
try:
# Read and normalize the uploaded file
df_raw = _read_uploaded_file(payment_notification_file)
df_normalized = _normalize_payment_data(df_raw)
# Validate total amount if provided
if total_payment_amount and total_payment_amount > 0:
uploaded_total = df_normalized['payment_amount'].sum()
if abs(uploaded_total - total_payment_amount) > 0.01:
return f"β Total amount validation failed! Uploaded: ${uploaded_total:.2f}, Entered: ${total_payment_amount:.2f}", pd.DataFrame()
# Load existing payment notifications and append new ones
existing_df = load_payment_notifications()
# Add reference details to each row
if reference_number:
# Generate unique PN ID with reference number
base_id = f"{reference_number}-{datetime.utcnow().strftime('%Y%m%d-%H%M%S')}"
df_normalized['payment_notification_id'] = [f"{base_id}-{i}" for i in range(len(df_normalized))]
df_normalized['reference_number'] = reference_number
else:
base_id = f"PN-{datetime.utcnow().strftime('%Y%m%d-%H%M%S')}"
df_normalized['payment_notification_id'] = [f"{base_id}-{i}" for i in range(len(df_normalized))]
df_normalized['reference_number'] = base_id
df_normalized['check_number'] = check_number
df_normalized['reference_date'] = pd.to_datetime(reference_date) if reference_date else datetime.utcnow()
df_normalized['entered_total_amount'] = total_payment_amount if total_payment_amount else df_normalized['payment_amount'].sum()
updated_df = pd.concat([existing_df, df_normalized], ignore_index=True)
# Save to persistent storage
save_payment_notifications(updated_df)
message = f"β
Successfully uploaded {len(df_normalized)} payment notifications"
if reference_number:
message += f" with reference: {reference_number}"
preview = df_normalized[['document_number', 'invoice_date', 'payment_amount', 'applied_status']].head(10)
return message, preview
except Exception as e:
return f"β Error uploading file: {str(e)}", pd.DataFrame()
# ---------------------------------------------------------------------
# Enhanced Reconciliation Dashboard with Selection System
# ---------------------------------------------------------------------
def refresh_reconciliation_dashboard():
"""Refresh the reconciliation dashboard with current matches."""
try:
matches, unmatched_invoices, unapplied_payments = find_exact_matches()
summary = get_reconciliation_summary()
# Prepare matches for display with selection columns
matches_df = pd.DataFrame(matches) if matches else pd.DataFrame()
if not matches_df.empty:
matches_df['select'] = False # Add checkbox column
# Ensure required columns exist
if 'invoice_amount' not in matches_df.columns and 'amount' in matches_df.columns:
matches_df['invoice_amount'] = matches_df['amount']
if 'payment_amount' not in matches_df.columns and 'amount' in matches_df.columns:
matches_df['payment_amount'] = matches_df['amount']
# Prepare unmatched invoices with selection columns
unmatched_invoices_df = pd.DataFrame(unmatched_invoices) if unmatched_invoices else pd.DataFrame()
if not unmatched_invoices_df.empty:
unmatched_invoices_df['select'] = False
# Prepare unapplied payments with selection columns
unapplied_payments_df = pd.DataFrame(unapplied_payments) if unapplied_payments else pd.DataFrame()
if not unapplied_payments_df.empty:
unapplied_payments_df['select'] = False
# Create summary message
summary_msg = f"""
### π Reconciliation Summary
- **Exact Matches Found**: {len(matches)}
- **Unmatched Invoices**: {len(unmatched_invoices)}
- **Unapplied Payments**: {len(unapplied_payments)}
**Invoice Statistics:**
- Total: {summary['invoices']['total']}
- Paid: {summary['invoices']['paid']}
- Unpaid: {summary['invoices']['unpaid']}
**Payment Statistics:**
- Total: {summary['payments']['total']}
- Applied: {summary['payments']['applied']}
- Unapplied: {summary['payments']['unapplied']}
"""
return summary_msg, matches_df, unmatched_invoices_df, unapplied_payments_df
except Exception as e:
error_msg = f"β Error loading reconciliation data: {str(e)}"
return error_msg, pd.DataFrame(), pd.DataFrame(), pd.DataFrame()
def handle_confirm_auto_match(matches_df: pd.DataFrame):
"""Handle confirming ALL selected auto matches."""
if matches_df is None or matches_df.empty:
return "β No matches available to confirm", *refresh_reconciliation_dashboard()[1:]
try:
# Get selected matches
selected_matches = matches_df[matches_df['select'] == True]
if selected_matches.empty:
return "β Please select at least one match to confirm", *refresh_reconciliation_dashboard()[1:]
success_count = 0
error_messages = []
for _, match in selected_matches.iterrows():
invoice_id = match.get('invoice_record_id')
payment_id = match.get('payment_record_id')
if invoice_id and payment_id:
result = confirm_match(invoice_id, payment_id)
if result['success']:
success_count += 1
else:
error_messages.append(result['message'])
# Synchronize data across all storage
_sync_data_after_matching()
if success_count > 0:
message = f"β
Successfully confirmed {success_count} matches"
if error_messages:
message += f"\n\nErrors: {', '.join(error_messages)}"
message += "\n\n**Data synchronized across all tabs!**"
else:
message = f"β Failed to confirm any matches: {', '.join(error_messages)}"
return message, *refresh_reconciliation_dashboard()[1:]
except Exception as e:
return f"β Error confirming matches: {str(e)}", *refresh_reconciliation_dashboard()[1:]
def handle_manual_pair_match(selected_invoice_id: str, selected_payment_id: str):
"""Handle manual pairing of specific invoice and payment."""
if not selected_invoice_id or not selected_payment_id:
return "β Please select both an invoice and a payment to pair", *refresh_reconciliation_dashboard()[1:]
print(f"Manual pairing - Invoice: {selected_invoice_id}, Payment: {selected_payment_id}")
result = manual_match_invoice_to_payment(selected_invoice_id, selected_payment_id)
if result['success']:
# Synchronize data across all storage
_sync_data_after_matching()
success_msg = f"β
{result['message']}\n\n**Data synchronized across all tabs!**"
return success_msg, *refresh_reconciliation_dashboard()[1:]
else:
return f"β {result['message']}", *refresh_reconciliation_dashboard()[1:]
def handle_select_all_auto_matches(matches_df: pd.DataFrame):
"""Select all auto matches for bulk confirmation."""
if matches_df is None or matches_df.empty:
return matches_df, "No matches available to select"
matches_df['select'] = True
return matches_df, f"β
Selected all {len(matches_df)} auto matches"
def handle_clear_all_selections(matches_df: pd.DataFrame, unmatched_invoices_df: pd.DataFrame, unapplied_payments_df: pd.DataFrame):
"""Clear all selections across all tables."""
if matches_df is not None and not matches_df.empty and 'select' in matches_df.columns:
matches_df['select'] = False
if unmatched_invoices_df is not None and not unmatched_invoices_df.empty and 'select' in unmatched_invoices_df.columns:
unmatched_invoices_df['select'] = False
if unapplied_payments_df is not None and not unapplied_payments_df.empty and 'select' in unapplied_payments_df.columns:
unapplied_payments_df['select'] = False
return matches_df, unmatched_invoices_df, unapplied_payments_df, "β
Cleared all selections"
def get_suggested_matches():
"""Get suggested matches based on 2 out of 3 field matches."""
try:
_, unmatched_invoices, unapplied_payments = find_exact_matches()
suggestions = []
for _, invoice in unmatched_invoices.iterrows():
for _, payment in unapplied_payments.iterrows():
match_score = 0
# Amount match (with tolerance for floating point)
if abs(invoice.get('total_billed', 0) - payment.get('payment_amount', 0)) < 0.01:
match_score += 1
# Document number match (fuzzy)
inv_doc = str(invoice.get('document_number', '')).lower().strip()
pay_doc = str(payment.get('document_number', '')).lower().strip()
if inv_doc and pay_doc and (inv_doc in pay_doc or pay_doc in inv_doc):
match_score += 1
# Date match (within 7 days)
inv_date = invoice.get('invoice_date')
pay_date = payment.get('invoice_date')
if pd.notna(inv_date) and pd.notna(pay_date):
days_diff = abs((inv_date - pay_date).days)
if days_diff <= 7:
match_score += 1
# If at least 2 out of 3 fields match
if match_score >= 2:
suggestions.append({
'invoice_record_id': invoice['record_id'],
'invoice_document': invoice.get('document_number', ''),
'invoice_amount': invoice.get('total_billed', 0),
'invoice_date': invoice.get('invoice_date'),
'payment_record_id': payment['record_id'],
'payment_document': payment.get('document_number', ''),
'payment_amount': payment.get('payment_amount', 0),
'payment_date': payment.get('invoice_date'),
'match_score': match_score,
'select': False
})
return pd.DataFrame(suggestions) if suggestions else pd.DataFrame()
except Exception as e:
print(f"Error getting suggested matches: {e}")
return pd.DataFrame()
def handle_confirm_suggested_matches(suggested_matches_df: pd.DataFrame):
"""Handle confirming selected suggested matches."""
if suggested_matches_df is None or suggested_matches_df.empty:
return "β No suggested matches available to confirm", *refresh_reconciliation_dashboard()[1:]
try:
# Get selected suggested matches
selected_matches = suggested_matches_df[suggested_matches_df['select'] == True]
if selected_matches.empty:
return "β Please select at least one suggested match to confirm", *refresh_reconciliation_dashboard()[1:]
success_count = 0
error_messages = []
for _, match in selected_matches.iterrows():
invoice_id = match.get('invoice_record_id')
payment_id = match.get('payment_record_id')
if invoice_id and payment_id:
result = manual_match_invoice_to_payment(invoice_id, payment_id)
if result['success']:
success_count += 1
else:
error_messages.append(result['message'])
# Synchronize data across all storage
_sync_data_after_matching()
if success_count > 0:
message = f"β
Successfully confirmed {success_count} suggested matches"
if error_messages:
message += f"\n\nErrors: {', '.join(error_messages)}"
message += "\n\n**Data synchronized across all tabs!**"
else:
message = f"β Failed to confirm any suggested matches: {', '.join(error_messages)}"
return message, *refresh_reconciliation_dashboard()[1:]
except Exception as e:
return f"β Error confirming suggested matches: {str(e)}", *refresh_reconciliation_dashboard()[1:]
# ---------------------------------------------------------------------
# Enhanced Selection Handlers for Manual Pairing
# ---------------------------------------------------------------------
def handle_unmatched_invoice_selection(evt: gr.SelectData, invoices_df: pd.DataFrame):
"""Handle selection from unmatched invoices table with better reliability."""
if evt is None or invoices_df is None or invoices_df.empty:
return "", "**Selected Invoice:** None"
try:
# Get the selected row index
row_index = evt.index[0] if isinstance(evt.index, (list, tuple)) else evt.index
selected_row = invoices_df.iloc[row_index]
record_id = selected_row.get('record_id', '')
document_num = selected_row.get('document_number', 'N/A')
amount = selected_row.get('total_billed', 0)
vendor = selected_row.get('vendor_name', '')
display = f"**Selected Invoice:** {document_num} (${amount:.2f}) - {vendor}"
print(f"Manual invoice selected: {record_id} - {document_num}")
return record_id, display
except Exception as e:
print(f"Error in invoice selection: {e}")
return "", "**Selected Invoice:** None"
def handle_unapplied_payment_selection(evt: gr.SelectData, payments_df: pd.DataFrame):
"""Handle selection from unapplied payments table with better reliability."""
if evt is None or payments_df is None or payments_df.empty:
return "", "**Selected Payment:** None"
try:
# Get the selected row index
row_index = evt.index[0] if isinstance(evt.index, (list, tuple)) else evt.index
selected_row = payments_df.iloc[row_index]
record_id = selected_row.get('record_id', '')
document_num = selected_row.get('document_number', 'N/A')
amount = selected_row.get('payment_amount', 0)
vendor = selected_row.get('vendor_name', '')
display = f"**Selected Payment:** {document_num} (${amount:.2f}) - {vendor}"
print(f"Manual payment selected: {record_id} - {document_num}")
return record_id, display
except Exception as e:
print(f"Error in payment selection: {e}")
return "", "**Selected Payment:** None"
# ---------------------------------------------------------------------
# Enhanced All Transactions List with Filtering
# ---------------------------------------------------------------------
def refresh_all_transactions():
"""Refresh the All Transactions List view with highlighting."""
try:
df = load_all_transactions()
if df.empty:
return pd.DataFrame(), "No transactions found. Upload some invoices to get started."
# Select display columns
display_columns = ['record_id', 'document_number', 'invoice_date', 'total_billed',
'ar_status', 'amount_received', 'vendor_name', 'transaction_type']
available_columns = [col for col in display_columns if col in df.columns]
display_df = df[available_columns].copy()
# Format datetime columns for display
for col in display_df.columns:
if pd.api.types.is_datetime64_any_dtype(display_df[col]):
display_df[col] = display_df[col].dt.strftime('%Y-%m-%d')
# Calculate summary with updated data
paid_count = len(df[df['ar_status'] == 'Paid'])
unpaid_count = len(df[df['ar_status'] == 'Unpaid'])
total_amount = df['total_billed'].sum()
amount_received = df['amount_received'].sum()
summary = f"""
**All Transactions Summary**
- Total Records: {len(df)}
- Paid: {paid_count}
- Unpaid: {unpaid_count}
- Total Amount: ${total_amount:,.2f}
- Amount Received: ${amount_received:,.2f}
- Outstanding: ${(total_amount - amount_received):,.2f}
"""
return display_df, summary
except Exception as e:
return pd.DataFrame(), f"β Error loading transactions: {str(e)}"
def filter_all_transactions(
date_range_start: str,
date_range_end: str,
document_number_filter: str,
ar_status_filter: str,
transaction_type_filter: str,
vendor_filter: str,
sort_column: str,
sort_direction: str
):
"""Apply filters and sorting to All Transactions List."""
try:
df = load_all_transactions()
if df.empty:
return pd.DataFrame(), "No transactions found."
# Apply date range filter
if date_range_start and date_range_end:
try:
start_date = pd.to_datetime(date_range_start)
end_date = pd.to_datetime(date_range_end)
df = df[(df['invoice_date'] >= start_date) & (df['invoice_date'] <= end_date)]
except:
pass
# Apply document number filter
if document_number_filter:
df = df[df['document_number'].astype(str).str.contains(document_number_filter, case=False, na=False)]
# Apply AR status filter
if ar_status_filter != "All":
df = df[df['ar_status'] == ar_status_filter]
# Apply transaction type filter
if transaction_type_filter != "All":
df = df[df['transaction_type'] == transaction_type_filter]
# Apply vendor filter
if vendor_filter:
df = df[df['vendor_name'].astype(str).str.contains(vendor_filter, case=False, na=False)]
# Apply sorting
if sort_column and sort_column in df.columns:
df = df.sort_values(sort_column, ascending=(sort_direction == "Ascending"))
# Select display columns
display_columns = ['record_id', 'document_number', 'invoice_date', 'total_billed',
'ar_status', 'amount_received', 'vendor_name', 'transaction_type']
available_columns = [col for col in display_columns if col in df.columns]
display_df = df[available_columns].copy()
# Format datetime columns for display
for col in display_df.columns:
if pd.api.types.is_datetime64_any_dtype(display_df[col]):
display_df[col] = display_df[col].dt.strftime('%Y-%m-%d')
# Calculate summary with filtered data
paid_count = len(df[df['ar_status'] == 'Paid'])
unpaid_count = len(df[df['ar_status'] == 'Unpaid'])
total_amount = df['total_billed'].sum()
amount_received = df['amount_received'].sum()
summary = f"""
**Filtered Transactions Summary**
- Filtered Records: {len(df)}
- Paid: {paid_count}
- Unpaid: {unpaid_count}
- Total Amount: ${total_amount:,.2f}
- Amount Received: ${amount_received:,.2f}
- Outstanding: ${(total_amount - amount_received):,.2f}
"""
return display_df, summary
except Exception as e:
return pd.DataFrame(), f"β Error filtering transactions: {str(e)}"
def clear_all_filters():
"""Clear all filter inputs."""
return "", "", "", "All", "All", "", "invoice_date", "Descending"
# ---------------------------------------------------------------------
# Enhanced Vendor Transaction List with Filtering
# ---------------------------------------------------------------------
def refresh_vendor_transactions():
"""Refresh Vendor Transaction List (Applied/Unapplied view)."""
try:
payments_df = load_payment_notifications()
if payments_df.empty:
return pd.DataFrame(), "No payment transactions found."
# Select display columns
display_columns = ['payment_notification_id', 'document_number', 'invoice_date',
'payment_amount', 'applied_status', 'applied_to_invoice',
'vendor_name', 'upload_timestamp']
available_columns = [col for col in display_columns if col in payments_df.columns]
display_df = payments_df[available_columns].copy()
# Format datetime columns
for col in display_df.columns:
if pd.api.types.is_datetime64_any_dtype(display_df[col]):
display_df[col] = display_df[col].dt.strftime('%Y-%m-%d')
# Summary with updated data
applied_count = len(display_df[display_df['applied_status'] == 'Applied'])
unapplied_count = len(display_df[display_df['applied_status'] == 'Unapplied'])
total_amount = display_df['payment_amount'].sum()
applied_amount = display_df[display_df['applied_status'] == 'Applied']['payment_amount'].sum()
unapplied_amount = display_df[display_df['applied_status'] == 'Unapplied']['payment_amount'].sum()
summary = f"""
**Vendor Transactions Summary**
- Total Payment Lines: {len(display_df)}
- Applied: {applied_count} (${applied_amount:,.2f})
- Unapplied: {unapplied_count} (${unapplied_amount:,.2f})
- Total Payment Amount: ${total_amount:,.2f}
- **Reconciliation Rate: {(applied_count/len(display_df)*100 if len(display_df) > 0 else 0):.1f}%**
"""
return display_df, summary
except Exception as e:
return pd.DataFrame(), f"β Error loading vendor transactions: {str(e)}"
def filter_vendor_transactions(
pn_id_filter: str,
document_number_filter: str,
applied_status_filter: str,
vendor_filter: str,
min_amount: float,
max_amount: float,
sort_column: str,
sort_direction: str
):
"""Apply filters and sorting to Vendor Transaction List."""
try:
payments_df = load_payment_notifications()
if payments_df.empty:
return pd.DataFrame(), "No payment transactions found."
# Apply Payment Notification ID filter
if pn_id_filter:
payments_df = payments_df[payments_df['payment_notification_id'].astype(str).str.contains(pn_id_filter, case=False, na=False)]
# Apply document number filter
if document_number_filter:
payments_df = payments_df[payments_df['document_number'].astype(str).str.contains(document_number_filter, case=False, na=False)]
# Apply applied status filter
if applied_status_filter != "All":
payments_df = payments_df[payments_df['applied_status'] == applied_status_filter]
# Apply vendor filter
if vendor_filter:
payments_df = payments_df[payments_df['vendor_name'].astype(str).str.contains(vendor_filter, case=False, na=False)]
# Apply amount range filter
if min_amount is not None:
payments_df = payments_df[payments_df['payment_amount'] >= min_amount]
if max_amount is not None:
payments_df = payments_df[payments_df['payment_amount'] <= max_amount]
# Apply sorting
if sort_column and sort_column in payments_df.columns:
payments_df = payments_df.sort_values(sort_column, ascending=(sort_direction == "Ascending"))
# Select display columns
display_columns = ['payment_notification_id', 'document_number', 'invoice_date',
'payment_amount', 'applied_status', 'applied_to_invoice',
'vendor_name', 'upload_timestamp']
available_columns = [col for col in display_columns if col in payments_df.columns]
display_df = payments_df[available_columns].copy()
# Format datetime columns
for col in display_df.columns:
if pd.api.types.is_datetime64_any_dtype(display_df[col]):
display_df[col] = display_df[col].dt.strftime('%Y-%m-%d')
# Summary with filtered data
applied_count = len(display_df[display_df['applied_status'] == 'Applied'])
unapplied_count = len(display_df[display_df['applied_status'] == 'Unapplied'])
total_amount = display_df['payment_amount'].sum()
applied_amount = display_df[display_df['applied_status'] == 'Applied']['payment_amount'].sum()
unapplied_amount = display_df[display_df['applied_status'] == 'Unapplied']['payment_amount'].sum()
summary = f"""
**Filtered Vendor Transactions Summary**
- Filtered Payment Lines: {len(display_df)}
- Applied: {applied_count} (${applied_amount:,.2f})
- Unapplied: {unapplied_count} (${unapplied_amount:,.2f})
- Total Payment Amount: ${total_amount:,.2f}
- **Reconciliation Rate: {(applied_count/len(display_df)*100 if len(display_df) > 0 else 0):.1f}%**
"""
return display_df, summary
except Exception as e:
return pd.DataFrame(), f"β Error filtering vendor transactions: {str(e)}"
def clear_vendor_filters():
"""Clear all vendor transaction filters."""
return "", "", "All", "", None, None, "upload_timestamp", "Descending"
# ---------------------------------------------------------------------
# Enhanced Payment Notification Summary with Reference System
# ---------------------------------------------------------------------
def refresh_pn_summary_with_reference():
"""Refresh Payment Notification summary with reference details."""
try:
payments_df = load_payment_notifications()
if payments_df.empty:
return pd.DataFrame(), "No payment notifications found.", pd.DataFrame()
# Group by reference number to create summary
if 'reference_number' in payments_df.columns:
summary = payments_df.groupby('reference_number').agg({
'check_number': 'first',
'reference_date': 'first',
'entered_total_amount': 'first',
'record_id': 'count',
'payment_amount': 'sum',
'applied_status': lambda x: (x == 'Applied').sum()
}).reset_index()
summary.columns = [
'reference_number', 'check_number', 'reference_date',
'entered_total_amount', 'total_count', 'actual_total_amount', 'applied_count'
]
# Use entered amount if available, otherwise use actual sum
summary['total_value'] = summary.apply(
lambda row: row['entered_total_amount'] if row['entered_total_amount'] > 0 else row['actual_total_amount'],
axis=1
)
summary['unapplied_count'] = summary['total_count'] - summary['applied_count']
# Format for display
display_df = summary[['reference_number', 'check_number', 'reference_date', 'total_value', 'total_count', 'applied_count', 'unapplied_count']].copy()
else:
# Fallback to original summary if no reference data
display_df = get_payment_notification_summary()
# Format for display
if 'reference_date' in display_df.columns:
display_df['reference_date'] = display_df['reference_date'].dt.strftime('%Y-%m-%d')
if 'upload_timestamp' in display_df.columns:
display_df['upload_timestamp'] = display_df['upload_timestamp'].dt.strftime('%Y-%m-%d %H:%M')
return display_df, "Select a Payment Notification to view details", pd.DataFrame()
except Exception as e:
return pd.DataFrame(), f"β Error loading payment notifications: {str(e)}", pd.DataFrame()
def handle_pn_selection(evt: gr.SelectData, pn_summary_df: pd.DataFrame):
"""Handle Payment Notification selection for drill-down."""
if evt is None or pn_summary_df is None or pn_summary_df.empty:
return "Please select a Payment Notification", pd.DataFrame()
try:
selected_row = pn_summary_df.iloc[evt.index]
pn_id = selected_row['reference_number'] if 'reference_number' in selected_row else selected_row['payment_notification_id']
# Get details for selected PN
details_df = get_pn_details(pn_id)
if details_df.empty:
return f"Details for {pn_id}", pd.DataFrame()
# Format for display
display_columns = ['record_id', 'document_number', 'invoice_date', 'payment_amount',
'applied_status', 'applied_to_invoice', 'vendor_name']
available_columns = [col for col in display_columns if col in details_df.columns]
display_details = details_df[available_columns].copy()
# Format datetime
for col in display_details.columns:
if pd.api.types.is_datetime64_any_dtype(display_details[col]):
display_details[col] = display_details[col].dt.strftime('%Y-%m-%d')
# Calculate updated summary
applied_count = len(details_df[details_df['applied_status'] == 'Applied'])
unapplied_count = len(details_df[details_df['applied_status'] == 'Unapplied'])
total_amount = details_df['payment_amount'].sum()
applied_amount = details_df[details_df['applied_status'] == 'Applied']['payment_amount'].sum()
message = f"**Payment Notification: {pn_id}**\n"
message += f"- Total Transactions: {len(details_df)}\n"
message += f"- Applied: {applied_count} (${applied_amount:,.2f})\n"
message += f"- Unapplied: {unapplied_count} (${(total_amount - applied_amount):,.2f})\n"
message += f"- Total Amount: ${total_amount:,.2f}"
return message, display_details
except Exception as e:
return f"β Error loading details: {str(e)}", pd.DataFrame()
# ---------------------------------------------------------------------
# Analytics & Export Dashboard
# ---------------------------------------------------------------------
def refresh_analytics_dashboard():
"""Refresh the analytics dashboard with comprehensive metrics."""
try:
analytics = get_reconciliation_analytics()
# Create comprehensive analytics summary
summary = f"""
## π Comprehensive Reconciliation Analytics
### Invoice Analytics
- **Total Invoices**: {analytics['invoices']['total']}
- **Paid Invoices**: {analytics['invoices']['paid']} (${analytics['invoices']['paid_amount']:,.2f})
- **Unpaid Invoices**: {analytics['invoices']['unpaid']} (${analytics['invoices']['unpaid_amount']:,.2f})
- **Total Invoice Amount**: ${analytics['invoices']['total_amount']:,.2f}
### Payment Analytics
- **Total Payments**: {analytics['payments']['total']}
- **Applied Payments**: {analytics['payments']['applied']} (${analytics['payments']['applied_amount']:,.2f})
- **Unapplied Payments**: {analytics['payments']['unapplied']} (${analytics['payments']['unapplied_amount']:,.2f})
- **Total Payment Amount**: ${analytics['payments']['total_amount']:,.2f}
### Performance Metrics
- **Overall Reconciliation Rate**: {analytics['reconciliation_rate']:.1f}%
- **Payment Application Efficiency**: {(analytics['payments']['applied_amount']/analytics['payments']['total_amount']*100) if analytics['payments']['total_amount'] > 0 else 0:.1f}%
- **Outstanding Balance**: ${analytics['invoices']['unpaid_amount'] - analytics['payments']['unapplied_amount']:,.2f}
"""
# Prepare data for export
invoices_df = load_all_transactions()
payments_df = load_payment_notifications()
# Combined data for export
combined_data = {
'invoices': invoices_df,
'payments': payments_df,
'analytics': analytics
}
return summary, combined_data
except Exception as e:
return f"β Error loading analytics: {str(e)}", {}
def export_filtered_data(export_type: str, combined_data: Dict):
"""Export filtered data based on selection."""
try:
if not combined_data or 'invoices' not in combined_data:
raise ValueError("No data available for export")
invoices_df = combined_data['invoices']
payments_df = combined_data['payments']
if export_type == "all_unapplied":
data = payments_df[payments_df['applied_status'] == 'Unapplied']
filename = "all_unapplied_payments"
elif export_type == "all_applied":
data = payments_df[payments_df['applied_status'] == 'Applied']
filename = "all_applied_payments"
elif export_type == "all_unpaid":
data = invoices_df[invoices_df['ar_status'] == 'Unpaid']
filename = "all_unpaid_invoices"
elif export_type == "all_paid":
data = invoices_df[invoices_df['ar_status'] == 'Paid']
filename = "all_paid_invoices"
elif export_type == "complete_dataset":
# Combine invoices and payments
inv = invoices_df.copy()
inv['record_type'] = 'invoice'
pay = payments_df.copy()
pay['record_type'] = 'payment'
data = pd.concat([inv, pay], ignore_index=True)
filename = "complete_reconciliation_dataset"
else:
raise ValueError(f"Unknown export type: {export_type}")
if data.empty:
raise ValueError(f"No data found for {export_type}")
# Create temporary file
suffix = ".xlsx"
tmp_fd, tmp_path = tempfile.mkstemp(suffix=suffix)
os.close(tmp_fd)
if suffix == ".xlsx":
data.to_excel(tmp_path, index=False)
else:
data.to_csv(tmp_path, index=False)
return tmp_path, f"β
Successfully exported {len(data)} records as {filename}"
except Exception as e:
return None, f"β Export failed: {str(e)}"
# ---------------------------------------------------------------------
# Main Application with Enhanced Interface
# ---------------------------------------------------------------------
with gr.Blocks(theme=gr.themes.Soft(), title="AR Reconciliation Portal") as demo:
gr.Markdown("# π’ Doc Shop Pro - AR PORTAL")
gr.Markdown("**Single Source of Truth for Vendor-Customer Transactions**")
with gr.Tabs():
# -------------------- TAB 1: Upload Area --------------------
with gr.Tab("π Upload Area"):
gr.Markdown("### Upload Open Invoices & Payment Notifications")
gr.Markdown("Upload files to add them to the respective storage areas.")
with gr.Row():
# Open Invoices Upload
with gr.Column():
gr.Markdown("#### π Open Invoices Upload")
gr.Markdown("Upload invoice files to add them to **All Transactions List**")
open_invoices_file = gr.File(
label="Upload Open Invoices File",
file_types=[".xlsx", ".xls", ".csv"],
type="filepath"
)
upload_invoices_btn = gr.Button("π Upload to All Transactions", variant="primary")
invoices_upload_status = gr.Markdown("### Upload Status")
invoices_preview = gr.Dataframe(
label="Invoices Preview (First 10 rows)",
interactive=False,
type="pandas"
)
# Payment Notifications Upload
with gr.Column():
gr.Markdown("#### π³ Payment Notifications Upload")
gr.Markdown("Upload payment files to add them to **Payment Notifications** storage")
with gr.Row():
reference_number = gr.Textbox(
label="Reference Number",
placeholder="e.g., CHK-12345, WIRE-67890"
)
check_number = gr.Textbox(
label="Check Number",
placeholder="Optional check number"
)
with gr.Row():
reference_date = gr.Textbox(
label="Reference Date (YYYY-MM-DD)",
placeholder="2024-01-15"
)
total_payment_amount = gr.Number(
label="Total Payment Amount",
placeholder="Enter total amount for validation"
)
payment_notification_file = gr.File(
label="Upload Payment Notification File",
file_types=[".xlsx", ".xls", ".csv"],
type="filepath"
)
upload_payments_btn = gr.Button("π Upload Payment Notifications", variant="primary")
payments_upload_status = gr.Markdown("### Upload Status")
payments_preview = gr.Dataframe(
label="Payments Preview (First 10 rows)",
interactive=False,
type="pandas"
)
# Button actions
upload_invoices_btn.click(
fn=handle_invoice_upload,
inputs=[open_invoices_file],
outputs=[invoices_upload_status, invoices_preview]
)
upload_payments_btn.click(
fn=handle_payment_upload_with_reference,
inputs=[payment_notification_file, reference_number, check_number, reference_date, total_payment_amount],
outputs=[payments_upload_status, payments_preview]
)
# -------------------- TAB 2: Enhanced Reconciliation Dashboard --------------------
with gr.Tab("π Reconciliation Dashboard"):
gr.Markdown("### Match Payment Notifications vs Open Invoices")
gr.Markdown("**Automatic 3-field matching + Manual matching for exceptions**")
with gr.Row():
refresh_btn = gr.Button("π Refresh All", variant="secondary")
clear_selections_btn = gr.Button("ποΈ Clear All Selections", variant="secondary")
get_suggestions_btn = gr.Button("π‘ Get Suggested Matches", variant="secondary")
with gr.Row():
reconciliation_summary = gr.Markdown("### Reconciliation Summary - Click Refresh All to load data")
# Manual Pairing Section
with gr.Row():
with gr.Column(scale=1):
gr.Markdown("#### π§ Manual Pairing")
gr.Markdown("Select one invoice and one payment to manually pair them")
with gr.Row():
selected_invoice_display = gr.Markdown("**Selected Invoice:** None")
selected_payment_display = gr.Markdown("**Selected Payment:** None")
manual_pair_btn = gr.Button("π€ Pair Selected Invoice & Payment", variant="primary")
# Auto Matches Section
with gr.Row():
with gr.Column():
gr.Markdown("#### β
Auto-Matched Items")
gr.Markdown("**Check the boxes to select matches, then confirm them**")
with gr.Row():
select_all_auto_btn = gr.Button("π Select All Auto Matches", variant="secondary")
confirm_auto_match_btn = gr.Button("β
Confirm Selected Auto Matches", variant="primary")
matches_table = gr.Dataframe(
label="Auto-Matched Invoices & Payments (Check to select)",
interactive=True,
type="pandas",
wrap=True
)
# Unmatched Items Section
with gr.Row():
with gr.Column():
gr.Markdown("#### β Unmatched Invoices")
gr.Markdown("**Click to select for manual pairing**")
unmatched_invoices_table = gr.Dataframe(
interactive=True,
type="pandas",
wrap=True
)
with gr.Column():
gr.Markdown("#### π° Unapplied Payments")
gr.Markdown("**Click to select for manual pairing**")
unapplied_payments_table = gr.Dataframe(
interactive=True,
type="pandas",
wrap=True
)
# Suggested Matches Section
with gr.Row():
with gr.Column():
gr.Markdown("#### π‘ Suggested Matches")
gr.Markdown("**Potential matches based on 2 out of 3 fields**")
with gr.Row():
confirm_suggested_btn = gr.Button("β
Confirm Selected Suggested Matches", variant="primary")
suggested_matches_table = gr.Dataframe(
label="Suggested Matches (Check to select)",
interactive=True,
type="pandas",
wrap=True
)
# Store selected matches for manual pairing
manual_invoice_id = gr.State() # For manual pairing
manual_payment_id = gr.State() # For manual pairing
# Event handlers for manual pairing
unmatched_invoices_table.select(
fn=handle_unmatched_invoice_selection,
inputs=[unmatched_invoices_table],
outputs=[manual_invoice_id, selected_invoice_display]
)
unapplied_payments_table.select(
fn=handle_unapplied_payment_selection,
inputs=[unapplied_payments_table],
outputs=[manual_payment_id, selected_payment_display]
)
# Button actions
refresh_btn.click(
fn=refresh_reconciliation_dashboard,
outputs=[reconciliation_summary, matches_table, unmatched_invoices_table, unapplied_payments_table]
)
clear_selections_btn.click(
fn=handle_clear_all_selections,
inputs=[matches_table, unmatched_invoices_table, unapplied_payments_table],
outputs=[matches_table, unmatched_invoices_table, unapplied_payments_table, reconciliation_summary]
)
select_all_auto_btn.click(
fn=handle_select_all_auto_matches,
inputs=[matches_table],
outputs=[matches_table, reconciliation_summary]
)
confirm_auto_match_btn.click(
fn=handle_confirm_auto_match,
inputs=[matches_table],
outputs=[reconciliation_summary, matches_table, unmatched_invoices_table, unapplied_payments_table]
)
manual_pair_btn.click(
fn=handle_manual_pair_match,
inputs=[manual_invoice_id, manual_payment_id],
outputs=[reconciliation_summary, matches_table, unmatched_invoices_table, unapplied_payments_table]
)
get_suggestions_btn.click(
fn=get_suggested_matches,
outputs=[suggested_matches_table]
)
confirm_suggested_btn.click(
fn=handle_confirm_suggested_matches,
inputs=[suggested_matches_table],
outputs=[reconciliation_summary, matches_table, unmatched_invoices_table, unapplied_payments_table]
)
# -------------------- TAB 3: All Transactions List with Filtering --------------------
with gr.Tab("π All Transactions List"):
gr.Markdown("### Central Database - All Transaction Records")
gr.Markdown("Master ledger containing all invoices, credit memos, and financial documents.")
with gr.Row():
with gr.Column(scale=2):
gr.Markdown("#### π Filter Options")
with gr.Row():
date_range_start = gr.Textbox(
label="Start Date (YYYY-MM-DD)",
placeholder="2024-01-01"
)
date_range_end = gr.Textbox(
label="End Date (YYYY-MM-DD)",
placeholder="2024-12-31"
)
with gr.Row():
document_number_filter = gr.Textbox(
label="Document Number Search",
placeholder="Search by document number..."
)
vendor_filter = gr.Textbox(
label="Vendor Search",
placeholder="Search by vendor name..."
)
with gr.Row():
ar_status_filter = gr.Dropdown(
choices=["All", "Paid", "Unpaid"],
label="AR Status Filter",
value="All"
)
transaction_type_filter = gr.Dropdown(
choices=["All", "invoice", "credit_memo"],
label="Transaction Type Filter",
value="All"
)
with gr.Row():
sort_column = gr.Dropdown(
choices=["document_number", "invoice_date", "total_billed", "vendor_name"],
label="Sort By",
value="invoice_date"
)
sort_direction = gr.Dropdown(
choices=["Ascending", "Descending"],
label="Sort Direction",
value="Descending"
)
with gr.Row():
apply_filters_btn = gr.Button("π Apply Filters", variant="primary")
refresh_transactions_btn = gr.Button("π Refresh All", variant="secondary")
clear_filters_btn = gr.Button("ποΈ Clear Filters", variant="secondary")
with gr.Row():
transactions_summary = gr.Markdown("### Transactions Summary")
all_transactions_table = gr.Dataframe(
label="All Transactions",
interactive=False,
type="pandas"
)
# Connect filter button
apply_filters_btn.click(
fn=filter_all_transactions,
inputs=[
date_range_start, date_range_end, document_number_filter,
ar_status_filter, transaction_type_filter, vendor_filter,
sort_column, sort_direction
],
outputs=[all_transactions_table, transactions_summary]
)
# Keep refresh button for full refresh
refresh_transactions_btn.click(
fn=refresh_all_transactions,
outputs=[all_transactions_table, transactions_summary]
)
# Clear filters button
clear_filters_btn.click(
fn=clear_all_filters,
outputs=[
date_range_start, date_range_end, document_number_filter,
ar_status_filter, transaction_type_filter, vendor_filter,
sort_column, sort_direction
]
).then(
fn=refresh_all_transactions,
outputs=[all_transactions_table, transactions_summary]
)
# -------------------- TAB 4: Enhanced Payment Notification Summary --------------------
with gr.Tab("π³ Payment Notification Summary"):
gr.Markdown("### Payment Notification Overview & Drill-Down")
gr.Markdown("Grouped by Payment Notification ID with transaction-level details.")
refresh_pn_btn = gr.Button("π Refresh Summary", variant="secondary")
with gr.Row():
with gr.Column():
gr.Markdown("#### Payment Notification Groups")
pn_summary_table = gr.Dataframe(
label="Payment Notification Summary",
interactive=True,
type="pandas"
)
with gr.Column():
pn_detail_summary = gr.Markdown("### Select a Payment Notification to view details")
pn_details_table = gr.Dataframe(
label="Payment Notification Details",
interactive=False,
type="pandas"
)
pn_summary_table.select(
fn=handle_pn_selection,
inputs=[pn_summary_table],
outputs=[pn_detail_summary, pn_details_table]
)
refresh_pn_btn.click(
fn=refresh_pn_summary_with_reference,
outputs=[pn_summary_table, pn_detail_summary, pn_details_table]
)
# -------------------- TAB 5: Vendor Transaction List with Filtering --------------------
with gr.Tab("π§Ύ Vendor Transaction List"):
gr.Markdown("### Applied & Unapplied Payment Transactions")
gr.Markdown("Complete view of all payment lines from vendors with real-time status tracking.")
with gr.Row():
with gr.Column(scale=2):
gr.Markdown("#### π Filter Options")
with gr.Row():
pn_id_filter = gr.Textbox(
label="Payment Notification ID",
placeholder="Search by PN ID..."
)
document_number_filter_vendor = gr.Textbox(
label="Document Number Search",
placeholder="Search by document number..."
)
with gr.Row():
applied_status_filter = gr.Dropdown(
choices=["All", "Applied", "Unapplied"],
label="Applied Status Filter",
value="All"
)
vendor_filter_vendor = gr.Textbox(
label="Vendor Search",
placeholder="Search by vendor name..."
)
with gr.Row():
min_amount = gr.Number(
label="Min Amount",
placeholder="Minimum payment amount"
)
max_amount = gr.Number(
label="Max Amount",
placeholder="Maximum payment amount"
)
with gr.Row():
sort_column_vendor = gr.Dropdown(
choices=["payment_notification_id", "document_number", "invoice_date", "payment_amount", "vendor_name", "upload_timestamp"],
label="Sort By",
value="upload_timestamp"
)
sort_direction_vendor = gr.Dropdown(
choices=["Ascending", "Descending"],
label="Sort Direction",
value="Descending"
)
with gr.Row():
apply_vendor_filters_btn = gr.Button("π Apply Filters", variant="primary")
refresh_vendor_btn = gr.Button("π Refresh All", variant="secondary")
clear_vendor_filters_btn = gr.Button("ποΈ Clear Filters", variant="secondary")
with gr.Row():
vendor_summary = gr.Markdown("### Vendor Transactions Summary")
vendor_transactions_table = gr.Dataframe(
label="Vendor Payment Transactions",
interactive=False,
type="pandas"
)
# Connect filter button
apply_vendor_filters_btn.click(
fn=filter_vendor_transactions,
inputs=[
pn_id_filter, document_number_filter_vendor, applied_status_filter,
vendor_filter_vendor, min_amount, max_amount, sort_column_vendor, sort_direction_vendor
],
outputs=[vendor_transactions_table, vendor_summary]
)
# Keep refresh button for full refresh
refresh_vendor_btn.click(
fn=refresh_vendor_transactions,
outputs=[vendor_transactions_table, vendor_summary]
)
# Clear filters button
clear_vendor_filters_btn.click(
fn=clear_vendor_filters,
outputs=[
pn_id_filter, document_number_filter_vendor, applied_status_filter,
vendor_filter_vendor, min_amount, max_amount, sort_column_vendor, sort_direction_vendor
]
).then(
fn=refresh_vendor_transactions,
outputs=[vendor_transactions_table, vendor_summary]
)
# -------------------- TAB 6: Analytics & Export Dashboard --------------------
with gr.Tab("π Analytics & Export"):
gr.Markdown("### Comprehensive Analytics & Data Export")
gr.Markdown("Advanced reporting and filtered data exports for business intelligence.")
refresh_analytics_btn = gr.Button("π Refresh Analytics", variant="secondary")
with gr.Row():
analytics_summary = gr.Markdown("### Analytics Dashboard")
with gr.Row():
gr.Markdown("#### π€ Export Filtered Data")
export_type = gr.Radio(
choices=[
("All Unapplied Payments", "all_unapplied"),
("All Applied Payments", "all_applied"),
("All Unpaid Invoices", "all_unpaid"),
("All Paid Invoices", "all_paid"),
("Complete Dataset", "complete_dataset")
],
label="Select Data to Export",
value="all_unapplied"
)
export_btn = gr.Button("πΎ Generate Export", variant="primary")
with gr.Row():
export_status = gr.Markdown("### Export Status")
export_file = gr.File(label="Download Export File")
# Store analytics data for export
analytics_data = gr.State()
refresh_analytics_btn.click(
fn=refresh_analytics_dashboard,
outputs=[analytics_summary, analytics_data]
)
export_btn.click(
fn=export_filtered_data,
inputs=[export_type, analytics_data],
outputs=[export_file, export_status]
)
if __name__ == "__main__":
demo.launch(
server_name="0.0.0.0",
server_port=7860,
share=False,
show_error=True
)
import os
import tempfile
import uuid
from datetime import datetime
from typing import Optional, List, Dict, Any
import pandas as pd
import gradio as gr
from src.storage import (
load_all_transactions, save_all_transactions,
load_payment_notifications, save_payment_notifications,
get_payment_notification_summary, get_pn_details
)
from src.matching_algorithms import (
find_exact_matches, confirm_match, get_reconciliation_summary,
manual_match_invoice_to_payment, get_reconciliation_analytics
)
# ---------------------------------------------------------------------
# Data Synchronization Helper
# ---------------------------------------------------------------------
def _sync_data_after_matching():
"""
Synchronize data across all storage after matching operations.
This ensures all tabs show consistent, updated information.
"""
try:
# Load current data
all_transactions = load_all_transactions()
payment_notifications = load_payment_notifications()
# Update invoice statuses based on payment applications
for _, payment in payment_notifications.iterrows():
if payment['applied_status'] == 'Applied' and payment.get('applied_to_invoice'):
# Find the invoice this payment was applied to
invoice_mask = all_transactions['record_id'] == payment['applied_to_invoice']
if invoice_mask.any():
all_transactions.loc[invoice_mask, 'ar_status'] = 'Paid'
all_transactions.loc[invoice_mask, 'amount_received'] = payment['payment_amount']
# Save synchronized data
save_all_transactions(all_transactions)
save_payment_notifications(payment_notifications)
return True
except Exception as e:
print(f"Error syncing data: {e}")
return False
# ---------------------------------------------------------------------
# Data Normalization Helpers
# ---------------------------------------------------------------------
def _read_uploaded_file(file_obj) -> Optional[pd.DataFrame]:
"""Read uploaded file (Excel or CSV) with robust CSV handling."""
if file_obj is None:
return None
path = file_obj.name
_, ext = os.path.splitext(path.lower())
try:
if ext in [".xlsx", ".xls"]:
return pd.read_excel(path)
elif ext == ".csv":
# First, try to detect the delimiter
with open(path, 'r', encoding='utf-8') as f:
first_line = f.readline()
# Check if semicolon delimiter (your format)
if ';' in first_line:
print("Detected semicolon-delimited CSV")
df = pd.read_csv(path, delimiter=';', encoding='utf-8')
else:
df = pd.read_csv(path, encoding='utf-8')
return df
else:
raise ValueError(f"Unsupported file type: {ext}")
except Exception as e:
raise ValueError(f"Error reading file: {str(e)}")
def _normalize_invoice_data(df: pd.DataFrame) -> pd.DataFrame:
"""Normalize uploaded invoice data to canonical schema."""
if df is None or df.empty:
return pd.DataFrame()
df = df.copy()
# Generate system fields
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"