jonels1988's picture
Upload app.py
6dcadb8 verified
Raw History Blame Contribute Delete
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"