#!/usr/bin/env python """ Add loans, repayments, and expenses to the database """ import os import sys import django from datetime import datetime, timedelta from decimal import Decimal import random os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings') django.setup() from django.contrib.auth import get_user_model from users.models import Branch from loans.models import Loan, Repayment from expenses.models import Expense from django.utils import timezone from django.db import connection User = get_user_model() print("=" * 60) print("ADDING LOANS AND SAMPLE DATA") print("=" * 60) # Get data borrowers = list(User.objects.filter(role='borrower')) officers = list(User.objects.filter(role='loan_officer')) branches = list(Branch.objects.all()) if not borrowers: print("✗ No borrowers found! Run populate_sample_data.py first") sys.exit(1) if not officers: print("✗ No loan officers found!") sys.exit(1) # Get loan products with connection.cursor() as cursor: cursor.execute("SELECT id, name, min_amount, max_amount, interest_rate, processing_fee, min_duration, max_duration FROM loan_products WHERE is_active = 1") products = cursor.fetchall() if not products: print("✗ No loan products found!") sys.exit(1) print(f"\nFound:") print(f" - {len(borrowers)} borrowers") print(f" - {len(officers)} loan officers") print(f" - {len(products)} loan products") print(f" - {len(branches)} branches") # Create loans print("\n" + "=" * 60) print("Creating loans...") print("=" * 60) statuses = ['pending', 'approved', 'disbursed', 'active', 'completed', 'defaulted'] loans_created = 0 for i in range(min(80, len(borrowers))): borrower = borrowers[i] officer = random.choice(officers) product = random.choice(products) # Unpack product data product_id, product_name, min_amt, max_amt, interest_rate, processing_fee, min_dur, max_dur = product # Random loan amount amount = Decimal(random.randint(int(min_amt), int(max_amt))) duration_days = random.randint(min_dur, max_dur) # Calculate fees proc_fee = amount * Decimal(processing_fee) / 100 interest = amount * Decimal(interest_rate) / 100 total = amount + proc_fee + interest # Random date days_ago = random.randint(1, 180) app_date = timezone.now() - timedelta(days=days_ago) # Status based on age if days_ago < 7: status = random.choice(['pending', 'approved']) elif days_ago < 30: status = random.choice(['approved', 'disbursed', 'active']) elif days_ago < 90: status = random.choice(['active', 'completed']) else: status = random.choice(['completed', 'defaulted']) try: with connection.cursor() as cursor: cursor.execute(""" INSERT INTO loans ( borrower_id, loan_product_id, amount, processing_fee, interest_amount, total_amount, duration_days, status, loan_officer_id, application_date, approval_date, disbursement_date, due_date, created_at, updated_at ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW(), NOW()) """, [ str(borrower.id), product_id, amount, proc_fee, interest, total, duration_days, status, str(officer.id), app_date, app_date + timedelta(days=1) if status != 'pending' else None, app_date + timedelta(days=2) if status in ['disbursed', 'active', 'completed'] else None, app_date + timedelta(days=duration_days + 2) if status in ['active', 'completed', 'defaulted'] else None ]) loan_id = cursor.lastrowid # Add repayments for active/completed loans if status in ['active', 'completed', 'defaulted']: if status == 'completed': # Fully paid cursor.execute(""" INSERT INTO repayments ( loan_id, amount, payment_date, payment_method, transaction_code, received_by_id, created_at, updated_at ) VALUES (%s, %s, %s, %s, %s, %s, NOW(), NOW()) """, [ loan_id, total, app_date + timedelta(days=random.randint(1, duration_days)), 'mpesa', f'MPE{random.randint(100000, 999999)}', str(officer.id) ]) else: # Partial payments paid_amount = total * Decimal(random.uniform(0.2, 0.8)) num_payments = random.randint(1, 3) payment_amount = paid_amount / num_payments for j in range(num_payments): cursor.execute(""" INSERT INTO repayments ( loan_id, amount, payment_date, payment_method, transaction_code, received_by_id, created_at, updated_at ) VALUES (%s, %s, %s, %s, %s, %s, NOW(), NOW()) """, [ loan_id, payment_amount, app_date + timedelta(days=random.randint(1, duration_days)), random.choice(['mpesa', 'cash', 'bank']), f'TXN{random.randint(100000, 999999)}', str(officer.id) ]) loans_created += 1 if (i + 1) % 20 == 0: print(f" ✓ Created {i+1} loans...") except Exception as e: print(f" ✗ Error creating loan: {e}") print(f"\n✓ Total loans created: {loans_created}") # Create expenses print("\n" + "=" * 60) print("Creating expenses...") print("=" * 60) categories = ['operational', 'staff', 'marketing', 'utilities', 'office', 'transport', 'maintenance'] payment_methods = ['cash', 'mpesa', 'bank', 'cheque'] expenses_created = 0 for i in range(100): branch = random.choice(branches) staff = random.choice(officers) days_ago = random.randint(1, 180) expense_date = (timezone.now() - timedelta(days=days_ago)).date() expense = Expense.objects.create( branch=branch, staff=staff, title=f'{random.choice(categories).title()} Expense', description=f'Sample expense for {random.choice(categories)}', category=random.choice(categories), amount=Decimal(random.randint(500, 50000)), payment_method=random.choice(payment_methods), paid_to=f'Vendor {random.randint(1, 50)}', status=random.choice(['approved', 'approved', 'approved', 'pending']), expense_date=expense_date, reference_number=f'REF{random.randint(10000, 99999)}' ) expenses_created += 1 if (i + 1) % 25 == 0: print(f" ✓ Created {i+1} expenses...") print(f"\n✓ Total expenses created: {expenses_created}") # Final summary print("\n" + "=" * 60) print("FINAL SUMMARY") print("=" * 60) print(f"Branches: {Branch.objects.count()}") print(f"Users: {User.objects.count()}") print(f" - Borrowers: {User.objects.filter(role='borrower').count()}") print(f" - Loan Officers: {User.objects.filter(role='loan_officer').count()}") print(f" - Branch Managers: {User.objects.filter(role='branch_manager').count()}") with connection.cursor() as cursor: cursor.execute("SELECT COUNT(*) FROM loan_products") print(f"Loan Products: {cursor.fetchone()[0]}") cursor.execute("SELECT COUNT(*) FROM loans") total_loans = cursor.fetchone()[0] print(f"Loans: {total_loans}") cursor.execute("SELECT status, COUNT(*) FROM loans GROUP BY status") for status, count in cursor.fetchall(): print(f" - {status}: {count}") cursor.execute("SELECT COUNT(*) FROM repayments") print(f"Repayments: {cursor.fetchone()[0]}") print(f"Expenses: {Expense.objects.count()}") print("\n" + "=" * 60) print("✓ Sample data added successfully!") print("=" * 60) print("\nYou can now access the system at http://localhost:8000") print("Login with: admin / admin (or check your admin credentials)")