""" Production Loan Calculation Investigation Script This script will investigate all calculations for a specific loan showing 56,000 total Run this on production and send me the output """ import os import sys # Set production database credentials os.environ['DB_NAME'] = 'xygbfpsg_graz' os.environ['DB_USER'] = 'xygbfpsg_graz' os.environ['DB_PASSWORD'] = ',qdN3O_!}oC67(]W' os.environ['DB_HOST'] = 'localhost' os.environ['DB_PORT'] = '3306' import django os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings') django.setup() from loans.models import Loan, LoanApplication, LoanProduct from decimal import Decimal from django.contrib.auth import get_user_model User = get_user_model() print(f"\n{'='*80}") print(f"PRODUCTION LOAN CALCULATION INVESTIGATION") print(f"{'='*80}\n") try: # Search for the loan with 56,000 total print("Searching for loan with total_amount = 56,000...") loans_56k = Loan.objects.filter(total_amount=56000).select_related( 'borrower', 'application', 'application__loan_product' ) if loans_56k.exists(): for loan in loans_56k: print(f"\n{'='*80}") print(f"FOUND LOAN: {loan.loan_number}") print(f"{'='*80}\n") print(f"BASIC INFO:") print(f" Loan Number: {loan.loan_number}") print(f" Borrower: {loan.borrower.get_full_name()}") print(f" Phone: {loan.borrower.phone_number}") print(f" Status: {loan.status}") print(f" Created: {loan.created_at}") print(f" Disbursement Date: {loan.disbursement_date}") print(f" Due Date: {loan.due_date}") print(f"\nLOAN PRODUCT:") print(f" Name: {loan.application.loan_product.name}") print(f" Type: {loan.application.loan_product.product_type}") print(f" Interest Rate (Model): {loan.application.loan_product.interest_rate}%") print(f" Processing Fee (Model): {loan.application.loan_product.processing_fee}%") print(f" Interest Rate (Settings): {loan.application.loan_product.get_interest_rate()}%") print(f" Processing Fee (Settings): {loan.application.loan_product.get_processing_fee()}%") print(f"\nSTORED VALUES IN DATABASE:") print(f" Principal Amount: KES {loan.principal_amount:>15,.2f}") print(f" Interest Amount: KES {loan.interest_amount:>15,.2f}") print(f" Processing Fee: KES {loan.processing_fee:>15,.2f}") print(f" Total Amount: KES {loan.total_amount:>15,.2f}") print(f" Duration: {loan.duration_days:>15} days") # Calculate months months = loan.duration_days / 30 print(f" Duration (months): {months:>15.2f} months") print(f"\nCALCULATION BREAKDOWN:") print(f" Step 1: Calculate Interest") print(f" Formula: Principal × Interest Rate × Months") # Method 1: Using stored interest rate interest_calc_1 = loan.principal_amount * (loan.application.loan_product.interest_rate / Decimal('100')) * Decimal(str(months)) print(f" Using Model Rate: {loan.principal_amount:,.2f} × {loan.application.loan_product.interest_rate}% × {months:.2f}") print(f" = KES {interest_calc_1:,.2f}") # Method 2: Using system settings interest_calc_2 = loan.principal_amount * (Decimal(str(loan.application.loan_product.get_interest_rate())) / Decimal('100')) * Decimal(str(months)) print(f" Using Settings Rate: {loan.principal_amount:,.2f} × {loan.application.loan_product.get_interest_rate()}% × {months:.2f}") print(f" = KES {interest_calc_2:,.2f}") # Method 3: Using product method interest_calc_3 = loan.application.loan_product.calculate_interest(loan.principal_amount, months) print(f" Using Product Method: KES {interest_calc_3:,.2f}") # Method 4: Using display method interest_calc_4 = loan.get_display_interest_amount() print(f" Using Display Method: KES {interest_calc_4:,.2f}") print(f"\n Step 2: Calculate Processing Fee") print(f" Formula: Principal × Processing Fee Rate") # Check if it's Boost Plus (monthly processing fee) is_boost_plus = loan.application.loan_product.product_type == 'boost_plus' if is_boost_plus: print(f" NOTE: This is Boost Plus - processing fee is charged MONTHLY") # Method 1: Using stored rate processing_fee_calc_1 = loan.principal_amount * (loan.application.loan_product.processing_fee / Decimal('100')) if is_boost_plus: processing_fee_calc_1 *= Decimal(str(months)) print(f" Using Model Rate: {loan.principal_amount:,.2f} × {loan.application.loan_product.processing_fee}%") if is_boost_plus: print(f" × {months:.2f} months (Boost Plus)") print(f" = KES {processing_fee_calc_1:,.2f}") # Method 2: Using system settings processing_fee_calc_2 = loan.principal_amount * (Decimal(str(loan.application.loan_product.get_processing_fee())) / Decimal('100')) if is_boost_plus: processing_fee_calc_2 *= Decimal(str(months)) print(f" Using Settings Rate: {loan.principal_amount:,.2f} × {loan.application.loan_product.get_processing_fee()}%") if is_boost_plus: print(f" × {months:.2f} months (Boost Plus)") print(f" = KES {processing_fee_calc_2:,.2f}") # Method 3: Using product method processing_fee_calc_3 = loan.application.loan_product.calculate_processing_fee(loan.principal_amount, months) print(f" Using Product Method: KES {processing_fee_calc_3:,.2f}") # Method 4: Using display method processing_fee_calc_4 = loan.get_display_processing_fee_amount() print(f" Using Display Method: KES {processing_fee_calc_4:,.2f}") print(f"\n Step 3: Calculate Total") print(f" Formula: Principal + Interest + Processing Fee") # Calculate all possible totals total_calc_1 = loan.principal_amount + interest_calc_1 + processing_fee_calc_1 total_calc_2 = loan.principal_amount + interest_calc_2 + processing_fee_calc_2 total_calc_3 = loan.principal_amount + interest_calc_3 + processing_fee_calc_3 total_calc_4 = loan.principal_amount + interest_calc_4 + processing_fee_calc_4 print(f" Using Model Rates: {loan.principal_amount:,.2f} + {interest_calc_1:,.2f} + {processing_fee_calc_1:,.2f}") print(f" = KES {total_calc_1:,.2f}") print(f" Using Settings Rates: {loan.principal_amount:,.2f} + {interest_calc_2:,.2f} + {processing_fee_calc_2:,.2f}") print(f" = KES {total_calc_2:,.2f}") print(f" Using Product Methods: {loan.principal_amount:,.2f} + {interest_calc_3:,.2f} + {processing_fee_calc_3:,.2f}") print(f" = KES {total_calc_3:,.2f}") print(f" Using Display Methods: {loan.principal_amount:,.2f} + {interest_calc_4:,.2f} + {processing_fee_calc_4:,.2f}") print(f" = KES {total_calc_4:,.2f}") print(f"\nCOMPARISON:") print(f" Database Total: KES {loan.total_amount:>15,.2f}") print(f" Correct Total (Method 1): KES {total_calc_1:>15,.2f} Diff: {total_calc_1 - loan.total_amount:>10,.2f}") print(f" Correct Total (Method 2): KES {total_calc_2:>15,.2f} Diff: {total_calc_2 - loan.total_amount:>10,.2f}") print(f" Correct Total (Method 3): KES {total_calc_3:>15,.2f} Diff: {total_calc_3 - loan.total_amount:>10,.2f}") print(f" Correct Total (Method 4): KES {total_calc_4:>15,.2f} Diff: {total_calc_4 - loan.total_amount:>10,.2f}") # Identify the issue print(f"\nISSUE ANALYSIS:") # Check if interest is wrong if abs(loan.interest_amount - interest_calc_3) > Decimal('0.01'): print(f" ❌ Interest amount is INCORRECT") print(f" Stored: KES {loan.interest_amount:,.2f}") print(f" Should be: KES {interest_calc_3:,.2f}") print(f" Difference: KES {interest_calc_3 - loan.interest_amount:,.2f}") else: print(f" ✅ Interest amount is correct") # Check if processing fee is wrong if abs(loan.processing_fee - processing_fee_calc_3) > Decimal('0.01'): print(f" ❌ Processing fee is INCORRECT") print(f" Stored: KES {loan.processing_fee:,.2f}") print(f" Should be: KES {processing_fee_calc_3:,.2f}") print(f" Difference: KES {processing_fee_calc_3 - loan.processing_fee:,.2f}") else: print(f" ✅ Processing fee is correct") # Check if total is wrong correct_total = loan.principal_amount + loan.interest_amount + loan.processing_fee if abs(loan.total_amount - correct_total) > Decimal('0.01'): print(f" ❌ Total amount calculation is INCORRECT") print(f" Stored: KES {loan.total_amount:,.2f}") print(f" Should be (using stored values): KES {correct_total:,.2f}") print(f" Difference: KES {correct_total - loan.total_amount:,.2f}") print(f" This means: total_amount ≠ principal + interest + processing_fee") else: print(f" ✅ Total amount calculation is correct (principal + interest + processing_fee)") # Check application values print(f"\nLOAN APPLICATION VALUES:") print(f" Application Number: {loan.application.application_number}") print(f" Requested Amount: KES {loan.application.requested_amount:,.2f}") print(f" Requested Duration: {loan.application.requested_duration} days") print(f" Interest Amount: KES {loan.application.interest_amount:,.2f}") print(f" Processing Fee: KES {loan.application.processing_fee_amount:,.2f}") print(f" Total Amount: KES {loan.application.total_amount:,.2f}") # Check if application values match loan values if loan.application.requested_amount != loan.principal_amount: print(f" ⚠️ Application amount ≠ Loan principal") if loan.application.interest_amount != loan.interest_amount: print(f" ⚠️ Application interest ≠ Loan interest") if loan.application.processing_fee_amount != loan.processing_fee: print(f" ⚠️ Application processing fee ≠ Loan processing fee") if loan.application.total_amount != loan.total_amount: print(f" ⚠️ Application total ≠ Loan total") print(f"\n{'='*80}\n") else: print("\n❌ No loan found with total_amount = 56,000") print("\nSearching for loans with principal = 50,000...") loans_50k = Loan.objects.filter(principal_amount=50000).select_related( 'borrower', 'application', 'application__loan_product' ).order_by('-created_at')[:10] if loans_50k.exists(): print(f"\nFound {loans_50k.count()} recent loan(s) with principal = 50,000:\n") for loan in loans_50k: correct_total = loan.principal_amount + loan.interest_amount + loan.processing_fee print(f" {loan.loan_number} | {loan.borrower.get_full_name()}") print(f" Product: {loan.application.loan_product.name}") print(f" Duration: {loan.duration_days} days") print(f" Principal: {loan.principal_amount:,.2f}") print(f" Interest: {loan.interest_amount:,.2f}") print(f" Processing Fee: {loan.processing_fee:,.2f}") print(f" Total (DB): {loan.total_amount:,.2f}") print(f" Total (Calc): {correct_total:,.2f}") if abs(loan.total_amount - correct_total) > Decimal('0.01'): print(f" ❌ MISMATCH: {correct_total - loan.total_amount:,.2f}") else: print(f" ✅ Correct") print() else: print("\n❌ No loans found with principal = 50,000") print("\nSearching for ALL recent loans...") all_loans = Loan.objects.all().select_related( 'borrower', 'application', 'application__loan_product' ).order_by('-created_at')[:10] print(f"\nFound {all_loans.count()} recent loan(s):\n") for loan in all_loans: correct_total = loan.principal_amount + loan.interest_amount + loan.processing_fee print(f" {loan.loan_number} | {loan.borrower.get_full_name()}") print(f" Product: {loan.application.loan_product.name}") print(f" Duration: {loan.duration_days} days") print(f" Principal: {loan.principal_amount:,.2f}") print(f" Total (DB): {loan.total_amount:,.2f}") print(f" Total (Calc): {correct_total:,.2f}") if abs(loan.total_amount - correct_total) > Decimal('0.01'): print(f" ❌ MISMATCH: {correct_total - loan.total_amount:,.2f}") print() except Exception as e: print(f"\n❌ ERROR: {str(e)}") import traceback traceback.print_exc() sys.exit(1) print(f"\n{'='*80}") print(f"INVESTIGATION COMPLETE") print(f"{'='*80}\n")