#!/usr/bin/env python3 """ FJCCV Ledger Helper Analyze the ledger and help with common tasks. """ import argparse import sys from pathlib import Path from datetime import datetime import openpyxl def get_ledger_path(): """Get the path to the FJCCV ledger""" # Primary location: workspace root workspace = Path.home() / ".openclaw/agents/main/workspace" primary_ledger = workspace / "FJCCV_Ledger.xlsx" if primary_ledger.exists(): return primary_ledger # Fallback: skill directory script_dir = Path(__file__).parent skill_dir = script_dir.parent return skill_dir / "FJCCV_Ledger.xlsx" def get_next_receipt_number(ledger_path: Path, donor_name: str, year: int) -> str: """ Determine the next receipt number for a donor. Returns format: LDDDDDD-YYYY """ # Load donations sheet for the year wb = openpyxl.load_workbook(ledger_path, data_only=True) sheet_name = f"Donations {year}" if sheet_name not in wb.sheetnames: print(f"Warning: Sheet '{sheet_name}' not found. Available sheets: {wb.sheetnames}") return None ws = wb[sheet_name] # Parse donor name to get last name name_parts = donor_name.strip().split() last_name = name_parts[-1] if name_parts else donor_name last_initial = last_name[0].upper() # Find all existing donations from this donor donor_donations = [] donors_with_same_initial = set() for row in ws.iter_rows(min_row=2, values_only=True): date_val = row[0] donor_val = row[1] receipt_num = row[5] # Skip empty rows and totals if not date_val or not donor_val: continue donor_last_name = donor_val.strip().split()[-1] if donor_val else "" # Track donors with same initial if donor_last_name and donor_last_name[0].upper() == last_initial: donors_with_same_initial.add(donor_val.strip()) # Track this specific donor's donations if donor_val.strip().lower() == donor_name.strip().lower(): donor_donations.append((date_val, receipt_num)) # Determine donor ID (sequence among donors with same initial) sorted_donors = sorted(donors_with_same_initial) try: donor_index = sorted_donors.index(donor_name.strip()) donor_id = donor_index + 1 except ValueError: # New donor with this initial donor_id = len(sorted_donors) + 1 # Determine donation count for this donor donation_count = len(donor_donations) + 1 # Format receipt number: LDDDDDD-YYYY receipt_number = f"{last_initial}{donor_id:03d}{donation_count:03d}-{year}" return receipt_number, donor_id, donation_count, len(donors_with_same_initial) def list_donors(ledger_path: Path, year: int = None): """List all donors with their donation counts""" wb = openpyxl.load_workbook(ledger_path, data_only=True) # Get all donation sheets donation_sheets = [s for s in wb.sheetnames if s.startswith("Donations ")] if year: donation_sheets = [f"Donations {year}"] donor_stats = {} for sheet_name in donation_sheets: if sheet_name not in wb.sheetnames: continue ws = wb[sheet_name] sheet_year = sheet_name.split()[-1] for row in ws.iter_rows(min_row=2, values_only=True): date_val = row[0] donor_val = row[1] amount_val = row[2] receipt_num = row[5] if not date_val or not donor_val: continue donor_key = donor_val.strip() if donor_key not in donor_stats: donor_stats[donor_key] = { 'total_donations': 0, 'total_amount': 0, 'last_receipt': None, 'years': set() } donor_stats[donor_key]['total_donations'] += 1 donor_stats[donor_key]['total_amount'] += float(amount_val) if amount_val else 0 donor_stats[donor_key]['last_receipt'] = receipt_num donor_stats[donor_key]['years'].add(sheet_year) print("=== FJCCV Donor Summary ===\n") for donor in sorted(donor_stats.keys()): stats = donor_stats[donor] years_str = ", ".join(sorted(stats['years'])) print(f"{donor}:") print(f" Donations: {stats['total_donations']}") print(f" Total Amount: ${stats['total_amount']:,.2f}") print(f" Last Receipt: {stats['last_receipt']}") print(f" Active Years: {years_str}") print() def show_recent_donations(ledger_path: Path, limit: int = 10): """Show most recent donations across all years""" wb = openpyxl.load_workbook(ledger_path, data_only=True) donation_sheets = [s for s in wb.sheetnames if s.startswith("Donations ")] all_donations = [] for sheet_name in donation_sheets: ws = wb[sheet_name] for row in ws.iter_rows(min_row=2, values_only=True): date_val = row[0] donor_val = row[1] amount_val = row[2] receipt_num = row[5] if not date_val or not donor_val: continue all_donations.append({ 'date': date_val, 'donor': donor_val, 'amount': amount_val, 'receipt': receipt_num }) # Sort by date descending all_donations.sort(key=lambda x: x['date'], reverse=True) print(f"=== Last {limit} Donations ===\n") for donation in all_donations[:limit]: date_str = donation['date'].strftime('%Y-%m-%d') if hasattr(donation['date'], 'strftime') else str(donation['date']) print(f"{date_str} | {donation['donor']:20s} | ${donation['amount']:>8,.2f} | {donation['receipt']}") def main(): parser = argparse.ArgumentParser(description='FJCCV Ledger Helper') parser.add_argument('action', choices=['next-receipt', 'list-donors', 'recent'], help='Action to perform') parser.add_argument('--donor', help='Donor name (for next-receipt)') parser.add_argument('--year', type=int, default=datetime.now().year, help='Year (default: current year)') parser.add_argument('--limit', type=int, default=10, help='Limit for recent donations (default: 10)') args = parser.parse_args() ledger_path = get_ledger_path() if not ledger_path.exists(): print(f"Error: Ledger not found at {ledger_path}") return 1 if args.action == 'next-receipt': if not args.donor: print("Error: --donor required for next-receipt action") return 1 result = get_next_receipt_number(ledger_path, args.donor, args.year) if result: receipt_num, donor_id, donation_count, total_with_initial = result print(f"Next receipt number for {args.donor} in {args.year}:") print(f" {receipt_num}") print(f"\nBreakdown:") print(f" Donor ID: {donor_id} (among {total_with_initial} donors with same initial)") print(f" Donation count: {donation_count}") elif args.action == 'list-donors': list_donors(ledger_path, args.year if args.year != datetime.now().year else None) elif args.action == 'recent': show_recent_donations(ledger_path, args.limit) return 0 if __name__ == '__main__': sys.exit(main())