""" Diagnose and fix the Mwamba loan LOAN-000131 calculation issue. The issue is that the total_amount stored in the database doesn't match the sum of principal_amount + interest_amount + processing_fee. """ import os import django from decimal import Decimal os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings') django.setup() from loans.models import Loan, LoanApplication from django.db.models import Sum def diagnose_and_fix_loan_131(): """Diagnose and fix LOAN-000131""" print("=" * 80) print("DIAGNOSING MWAMBA LOAN LOAN-000131") print("=" * 80) try: loan = Loan.objects.get(loan_number='LOAN-000131') except Loan.DoesNotExist: print("\n✗ Loan LOAN-000131 not found!") return print(f"\nLoan Details:") print(f" Loan Number: {loan.loan_number}") print(f" Borrower: {loan.borrower.get_full_name()}") print(f" Product: {loan.application.loan_product.name}") print(f" Status: {loan.status}") print(f" Duration: {loan.duration_days} days") print(f"\n" + "-" * 80) print("CURRENT DATABASE VALUES") print("-" * 80) print(f" Principal Amount: KES {loan.principal_amount:,.2f}") print(f" Interest Amount: KES {loan.interest_amount:,.2f}") print(f" Processing Fee: KES {loan.processing_fee:,.2f}") print(f" Total Amount (stored): KES {loan.total_amount:,.2f}") # Calculate what it should be correct_total = loan.principal_amount + loan.interest_amount + loan.processing_fee print(f"\n" + "-" * 80) print("CORRECT CALCULATION") print("-" * 80) print(f" Principal: KES {loan.principal_amount:,.2f}") print(f" + Interest: KES {loan.interest_amount:,.2f}") print(f" + Processing Fee: KES {loan.processing_fee:,.2f}") print(f" = Total: KES {correct_total:,.2f}") # Check if there's a mismatch if abs(loan.total_amount - correct_total) > Decimal('0.01'): print(f"\n⚠️ MISMATCH DETECTED!") print(f" Stored Total: KES {loan.total_amount:,.2f}") print(f" Correct Total: KES {correct_total:,.2f}") print(f" Difference: KES {correct_total - loan.total_amount:,.2f}") else: print(f"\n✓ Total amount is correct!") # Check repayments print(f"\n" + "-" * 80) print("REPAYMENT ANALYSIS") print("-" * 80) repayments = loan.repayments.all().order_by('payment_date') total_paid = repayments.aggregate(total=Sum('amount'))['total'] or Decimal('0.00') print(f" Number of Repayments: {repayments.count()}") print(f" Total Amount Paid: KES {total_paid:,.2f}") for repayment in repayments: print(f" - {repayment.payment_date.strftime('%Y-%m-%d')}: KES {repayment.amount:,.2f} ({repayment.payment_method})") # Calculate outstanding with correct total correct_outstanding = correct_total - total_paid stored_outstanding = loan.total_amount - total_paid print(f"\n" + "-" * 80) print("OUTSTANDING BALANCE") print("-" * 80) print(f" With Stored Total: KES {stored_outstanding:,.2f}") print(f" With Correct Total: KES {correct_outstanding:,.2f}") print(f" Difference: KES {correct_outstanding - stored_outstanding:,.2f}") # Check if we need to fix if abs(loan.total_amount - correct_total) > Decimal('0.01'): print(f"\n" + "=" * 80) print("FIX REQUIRED") print("=" * 80) response = input(f"\nDo you want to fix the total_amount from KES {loan.total_amount:,.2f} to KES {correct_total:,.2f}? (yes/no): ") if response.lower() in ['yes', 'y']: # Update the loan old_total = loan.total_amount loan.total_amount = correct_total loan.save() print(f"\n✓ Fixed!") print(f" Old Total: KES {old_total:,.2f}") print(f" New Total: KES {correct_total:,.2f}") print(f" New Outstanding: KES {correct_outstanding:,.2f}") # Also check and fix the application if needed application = loan.application if abs(application.total_amount - correct_total) > Decimal('0.01'): print(f"\n Also fixing application total_amount...") application.total_amount = correct_total application.save() print(f" ✓ Application fixed!") else: print(f"\n Fix cancelled.") else: print(f"\n✓ No fix needed - total_amount is correct!") print(f"\n" + "=" * 80) print("DIAGNOSIS COMPLETE") print("=" * 80) def check_all_mwamba_loans(): """Check all Mwamba loans for similar issues""" print("\n" + "=" * 80) print("CHECKING ALL MWAMBA LOANS") print("=" * 80) from loans.models import LoanProduct try: mwamba = LoanProduct.objects.get(product_type='mwamba') except LoanProduct.DoesNotExist: print("\n✗ Mwamba product not found!") return # Get all Mwamba loans mwamba_loans = Loan.objects.filter( application__loan_product=mwamba ).select_related('borrower', 'application') print(f"\nFound {mwamba_loans.count()} Mwamba loans") issues_found = [] for loan in mwamba_loans: correct_total = loan.principal_amount + loan.interest_amount + loan.processing_fee if abs(loan.total_amount - correct_total) > Decimal('0.01'): issues_found.append({ 'loan': loan, 'stored_total': loan.total_amount, 'correct_total': correct_total, 'difference': correct_total - loan.total_amount }) if issues_found: print(f"\n⚠️ Found {len(issues_found)} loans with incorrect total_amount:") print(f"\n{'Loan Number':<15} {'Borrower':<25} {'Stored Total':<15} {'Correct Total':<15} {'Difference':<15}") print("-" * 90) for issue in issues_found: loan = issue['loan'] print(f"{loan.loan_number:<15} {loan.borrower.get_full_name():<25} " f"KES {issue['stored_total']:>10,.2f} KES {issue['correct_total']:>10,.2f} " f"KES {issue['difference']:>10,.2f}") response = input(f"\nDo you want to fix all {len(issues_found)} loans? (yes/no): ") if response.lower() in ['yes', 'y']: fixed_count = 0 for issue in issues_found: loan = issue['loan'] loan.total_amount = issue['correct_total'] loan.save() # Also fix application application = loan.application if abs(application.total_amount - issue['correct_total']) > Decimal('0.01'): application.total_amount = issue['correct_total'] application.save() fixed_count += 1 print(f"\n✓ Fixed {fixed_count} loans!") else: print(f"\n Fix cancelled.") else: print(f"\n✓ All Mwamba loans have correct total_amount!") if __name__ == '__main__': try: # First diagnose the specific loan diagnose_and_fix_loan_131() # Then check all Mwamba loans check_all_mwamba_loans() except Exception as e: print(f"\n✗ Error: {str(e)}") import traceback traceback.print_exc()