PayTrace_Merchant / src /file_processors.py
jonels1988's picture
Update src/file_processors.py
c2e9e46 verified
Raw History Blame Contribute Delete
5.87 kB
import pandas as pd
import numpy as np
from datetime import datetime
import re
from typing import Tuple, Optional, Dict, Any
import logging
logger = logging.getLogger(__name__)
def process_uploaded_files(open_invoices_file: str, payment_notification_file: str) -> Tuple[pd.DataFrame, pd.DataFrame]:
"""Process uploaded files and convert to canonical format"""
open_invoices_df = pd.DataFrame()
payment_notifications_df = pd.DataFrame()
if open_invoices_file:
open_invoices_df = process_open_invoices(open_invoices_file)
if payment_notification_file:
payment_notifications_df = process_payment_notification(payment_notification_file)
return open_invoices_df, payment_notifications_df
def process_open_invoices(file_path: str) -> pd.DataFrame:
"""Process Open Invoices file to canonical format"""
try:
# Read file
if file_path.endswith('.xlsx'):
df = pd.read_excel(file_path)
else:
df = pd.read_csv(file_path)
# Drop empty rows
df = df.dropna(how='all')
# Create canonical format
processed_data = []
for idx, row in df.iterrows():
# Handle potential missing columns
row_dict = row.to_dict()
record = {
'id': f"INV_{idx:06d}",
'document_type': str(row_dict.get('Transaction type', 'Invoice')),
'document_number': str(row_dict.get('Num', f"INV_{idx}")),
'reference_number': None,
'po_number': str(row_dict.get('P.O. Number', '')),
'invoice_date': parse_date(row_dict.get('Date')),
'due_date': parse_date(row_dict.get('Due date')),
'total_billed': parse_currency(row_dict.get('Amount', 0)),
'amount_outstanding': parse_currency(row_dict.get('Open balance', 0)),
'discount': 0.0,
'credit_advances_applied': 0.0,
'payment_amount': 0.0,
'ar_status': str(row_dict.get('A/R paid', 'Unpaid')),
'open_balance': parse_currency(row_dict.get('Open balance', 0))
}
processed_data.append(record)
return pd.DataFrame(processed_data)
except Exception as e:
logger.error(f"Error processing open invoices: {str(e)}")
# Return empty dataframe with expected structure
return pd.DataFrame(columns=[
'id', 'document_type', 'document_number', 'reference_number', 'po_number',
'invoice_date', 'due_date', 'total_billed', 'amount_outstanding',
'discount', 'credit_advances_applied', 'payment_amount', 'ar_status', 'open_balance'
])
def process_payment_notification(file_path: str) -> pd.DataFrame:
"""Process Payment Notification file to canonical format"""
try:
# Read file
if file_path.endswith('.xlsx'):
df = pd.read_excel(file_path)
else:
df = pd.read_csv(file_path)
# Drop empty rows
df = df.dropna(how='all')
# Create canonical format
processed_data = []
for idx, row in df.iterrows():
# Handle potential missing columns
row_dict = row.to_dict()
record = {
'id': f"PAY_{idx:06d}",
'document_type': 'Payment',
'document_number': str(row_dict.get('Bill number', f"PAY_{idx}")),
'reference_number': str(row_dict.get('Reference', '')),
'po_number': None,
'invoice_date': parse_date(row_dict.get('Date')),
'due_date': None,
'total_billed': parse_currency(row_dict.get('Total billed', 0)),
'amount_outstanding': parse_currency(row_dict.get('Amount outstanding', 0)),
'discount': parse_currency(row_dict.get('Discount', 0)),
'credit_advances_applied': parse_currency(row_dict.get('Credit/advances applied', 0)),
'payment_amount': parse_currency(row_dict.get('Payment amount', 0)),
'ar_status': 'Paid',
'open_balance': parse_currency(row_dict.get('Amount outstanding', 0))
}
processed_data.append(record)
return pd.DataFrame(processed_data)
except Exception as e:
logger.error(f"Error processing payment notification: {str(e)}")
# Return empty dataframe with expected structure
return pd.DataFrame(columns=[
'id', 'document_type', 'document_number', 'reference_number', 'po_number',
'invoice_date', 'due_date', 'total_billed', 'amount_outstanding',
'discount', 'credit_advances_applied', 'payment_amount', 'ar_status', 'open_balance'
])
def parse_date(date_value) -> Optional[datetime]:
"""Parse various date formats to datetime"""
if pd.isna(date_value) or date_value == '' or date_value is None:
return datetime.now() # Default to current date
try:
parsed = pd.to_datetime(date_value, errors='coerce')
if pd.isna(parsed):
return datetime.now()
return parsed
except:
return datetime.now()
def parse_currency(currency_value) -> float:
"""Parse currency values to float"""
if pd.isna(currency_value) or currency_value is None:
return 0.0
try:
if isinstance(currency_value, (int, float)):
return float(currency_value)
# Remove currency symbols and commas
cleaned = str(currency_value).replace('$', '').replace(',', '').replace(' ', '').strip()
if cleaned == '':
return 0.0
return float(cleaned)
except:
return 0.0