from django.utils import timezone from organisations.models import Organisation from django.db.models import Sum from datetime import datetime from dateutil.relativedelta import relativedelta import pytz import math from .models import * from ledgers.ledgers_helper import generate_reference_no from .models import ( ExternalLoanPaymentTransaction, ExternalLoanMainTransactions, ExternalLoanPayments, ExternalLoanRepaymentSchedule ) from ledgers.models import SystemTransactions, OrganisationSubAccount from django.db import transaction from .models import ExternalLoanDisbursement, ExternalLoanApplication from ledgers.models import SystemTransactions, OrganisationSubAccount, BankAccounts from organisations.models import Organisation, OrganisationBranch from django.utils import timezone from datetime import datetime, timedelta from dateutil.relativedelta import relativedelta from django.utils import timezone from .models import ExternalLoanDisbursement def int_for_term(period_type, interest_rate): """Convert annual interest rate to period-specific rate""" if period_type == 'w': return (interest_rate/12)/4 elif period_type == 'd': return (interest_rate/12)/30 elif period_type == 'y': return interest_rate elif period_type == 'bw': return (interest_rate/12)/2 elif period_type == 'q': return (interest_rate/4) else: return (interest_rate/12) def freq_for_term(period_type, frequency): """Calculate frequency multiplier for different period types""" if period_type == 'w': return frequency * 7 elif period_type == 'd': return frequency * 1 elif period_type == 'y': return frequency * 365 elif period_type == 'q': return frequency * 90 elif period_type == 'bw': return frequency * 14 else: return frequency * 30 def round_off_amount(request, amount): """Round off amount based on organization settings""" return round(amount, 2) def refine_amount(request, amount, is_refine_only=False): """Refine amount for cleaner schedules""" refine = round_off_amount(request, amount) supplement = 0 if amount > 100: refine = int(amount/100) * 100 supplement = amount - refine elif amount < 100 and amount > 50: refine = int(amount/50) * 50 supplement = amount - refine else: refine = round_off_amount(request, amount) supplement = 0 return (refine, supplement) if is_refine_only == False else refine def external_loan_payment(request, loan_id=None, loan_data=None): """Calculate external loan payment totals""" if loan_id: loan_details = ExternalLoanApplication.objects.get(pk=loan_id) period_type = loan_details.period_type loan_period = loan_details.loan_period loan_amount = loan_details.loan_amount interest_rate = loan_details.int_rate int_method = loan_details.int_method frequency = 1 # Default frequency for external loans principal = loan_amount elif loan_data: period_type = loan_data.get('period_type') loan_period = int(loan_data.get('loan_period')) loan_amount = float(loan_data.get('loan_amount')) interest_rate = float(loan_data.get('int_rate')) int_method = loan_data.get('int_method') frequency = 1 principal = loan_amount else: return (0, 0, 0) total_interest = 0 total_payment = 0 if int_method == 'declining': the_term_rate = int_for_term(period_type, interest_rate) termly_interest_rate = the_term_rate / 100 term_payment = loan_amount / loan_period for i in range(0, loan_period): interest = loan_amount * termly_interest_rate * frequency total_interest += interest loan_amount -= term_payment total_payment = principal + total_interest elif int_method == 'flat': the_term_rate = int_for_term(period_type, interest_rate) termly_interest_rate = the_term_rate / 100 total_interest = loan_amount * termly_interest_rate * loan_period * frequency total_payment = principal + total_interest else: # amortization termly_rate = int_for_term(period_type, interest_rate) termly_interest_rate = termly_rate / 100 r = termly_interest_rate * frequency termly_payment = loan_amount * termly_interest_rate * ((1 + termly_interest_rate) ** loan_period) / (((1 + termly_interest_rate) ** loan_period) - 1) for i in range(loan_period): interest = loan_amount * r total_interest += interest principal_payment = termly_payment - interest loan_amount -= principal_payment total_payment = principal + total_interest return (round_off_amount(request, total_interest), round_off_amount(request, principal), round_off_amount(request, total_payment)) def generate_external_loan_number(organisation_id): """Generate external loan number similar to normal loans""" try: organisation = Organisation.objects.get(pk=organisation_id) current_year = timezone.now().year # Get the last external loan application for this organization last_loan = ExternalLoanApplication.objects.filter( applicant_organisation_id=organisation_id, date_added__year=current_year ).order_by('-id').first() if last_loan and last_loan.loan_number: # Extract the sequence number from the last loan number try: parts = last_loan.loan_number.split('-') if len(parts) >= 4: sequence = int(parts[-1]) + 1 else: sequence = 1 except (ValueError, IndexError): sequence = 1 else: sequence = 1 # Format: EXT-YYYY-ORG_ID-SEQUENCE loan_number = f"EXT-{current_year}-{organisation_id}-{sequence:04d}" return loan_number except Organisation.DoesNotExist: # Fallback loan number return f"EXT-{timezone.now().year}-{organisation_id}-0001" def calculate_external_loan_interest(principal, rate, period, method='flat'): """Calculate external loan interest""" if method == 'flat': return (principal * rate * period) / 100 elif method == 'declining': # Declining balance calculation monthly_rate = rate / 12 / 100 if monthly_rate == 0: return 0 return principal * monthly_rate * period else: return (principal * rate * period) / 100 def generate_external_loan_schedules_detailed(request, loan_id=None, schedule_type='normal', loan_data=None): """Generate detailed external loan repayment schedules with proper calculations""" schedules = [] try: if loan_data: # Generate from loan data loan_amount = float(loan_data.get('loan_amount', 0)) interest_rate = float(loan_data.get('int_rate', 0)) loan_period = int(loan_data.get('loan_period', 0)) period_type = loan_data.get('period_type', 'm') int_method = loan_data.get('int_method', 'flat') start_date = timezone.now().date() else: # Generate from existing loan loan = ExternalLoanApplication.objects.get(pk=loan_id) loan_amount = float(loan.loan_amount) interest_rate = float(loan.int_rate) loan_period = int(loan.loan_period) period_type = loan.period_type int_method = loan.int_method # Get disbursement date if available disbursement = ExternalLoanDisbursement.objects.filter( external_loan_application=loan, approval_status='approved' ).first() start_date = disbursement.disbursement_date.date() if disbursement else timezone.now().date() if loan_period > 0 and loan_amount > 0: # Calculate payment frequency if period_type == 'd': delta = timedelta(days=1) elif period_type == 'w': delta = timedelta(weeks=1) elif period_type == 'bw': delta = timedelta(weeks=2) elif period_type == 'q': delta = relativedelta(months=3) elif period_type == 'y': delta = relativedelta(years=1) else: # default to monthly delta = relativedelta(months=1) # Calculate payments based on interest method if int_method == 'flat': # Flat interest method total_interest = (loan_amount * interest_rate * loan_period) / 100 principal_per_period = loan_amount / loan_period interest_per_period = total_interest / loan_period current_date = start_date remaining_balance = loan_amount for i in range(loan_period): ending_balance = remaining_balance - principal_per_period schedule = { 'payment_number': i + 1, 'principal_expected': round(principal_per_period, 2), 'interest_expected': round(interest_per_period, 2), 'total_payment': round(principal_per_period + interest_per_period, 2), 'starting_balance': round(remaining_balance, 2), 'ending_balance': round(max(0, ending_balance), 2), 'expected_date': current_date, 'principal_paid': 0.0, 'interest_paid': 0.0, 'penalty_paid': 0.0, 'penalty_amount': 0.0, 'is_fully_paid': False, 'is_overdue': False, 'days_overdue': 0, 'remaining_principal': round(principal_per_period, 2), 'remaining_interest': round(interest_per_period, 2), 'remaining_penalty': 0.0, 'total_remaining': round(principal_per_period + interest_per_period, 2), 'payment_progress_percentage': 0.0 } schedules.append(schedule) remaining_balance = ending_balance current_date = current_date + delta elif int_method == 'declining': # Declining balance method if period_type == 'm': period_rate = interest_rate / 12 / 100 elif period_type == 'y': period_rate = interest_rate / 100 elif period_type == 'q': period_rate = interest_rate / 4 / 100 elif period_type == 'w': period_rate = interest_rate / 52 / 100 elif period_type == 'bw': period_rate = interest_rate / 26 / 100 else: period_rate = interest_rate / 12 / 100 principal_per_period = loan_amount / loan_period current_date = start_date remaining_balance = loan_amount for i in range(loan_period): interest_payment = remaining_balance * period_rate ending_balance = remaining_balance - principal_per_period schedule = { 'payment_number': i + 1, 'principal_expected': round(principal_per_period, 2), 'interest_expected': round(interest_payment, 2), 'total_payment': round(principal_per_period + interest_payment, 2), 'starting_balance': round(remaining_balance, 2), 'ending_balance': round(max(0, ending_balance), 2), 'expected_date': current_date, 'principal_paid': 0.0, 'interest_paid': 0.0, 'penalty_paid': 0.0, 'penalty_amount': 0.0, 'is_fully_paid': False, 'is_overdue': False, 'days_overdue': 0, 'remaining_principal': round(principal_per_period, 2), 'remaining_interest': round(interest_payment, 2), 'remaining_penalty': 0.0, 'total_remaining': round(principal_per_period + interest_payment, 2), 'payment_progress_percentage': 0.0 } schedules.append(schedule) remaining_balance = ending_balance current_date = current_date + delta else: # amortization # Amortization method if period_type == 'm': period_rate = interest_rate / 12 / 100 elif period_type == 'y': period_rate = interest_rate / 100 elif period_type == 'q': period_rate = interest_rate / 4 / 100 elif period_type == 'w': period_rate = interest_rate / 52 / 100 elif period_type == 'bw': period_rate = interest_rate / 26 / 100 else: period_rate = interest_rate / 12 / 100 if period_rate > 0: # Calculate fixed payment amount payment_amount = loan_amount * (period_rate * (1 + period_rate) ** loan_period) / ((1 + period_rate) ** loan_period - 1) else: payment_amount = loan_amount / loan_period current_date = start_date remaining_balance = loan_amount for i in range(loan_period): interest_payment = remaining_balance * period_rate principal_payment = payment_amount - interest_payment ending_balance = remaining_balance - principal_payment schedule = { 'payment_number': i + 1, 'principal_expected': round(principal_payment, 2), 'interest_expected': round(interest_payment, 2), 'total_payment': round(payment_amount, 2), 'starting_balance': round(remaining_balance, 2), 'ending_balance': round(max(0, ending_balance), 2), 'expected_date': current_date, 'principal_paid': 0.0, 'interest_paid': 0.0, 'penalty_paid': 0.0, 'penalty_amount': 0.0, 'is_fully_paid': False, 'is_overdue': False, 'days_overdue': 0, 'remaining_principal': round(principal_payment, 2), 'remaining_interest': round(interest_payment, 2), 'remaining_penalty': 0.0, 'total_remaining': round(payment_amount, 2), 'payment_progress_percentage': 0.0 } schedules.append(schedule) remaining_balance = ending_balance current_date = current_date + delta except Exception as e: print(f"Error generating external loan schedules: {e}") return schedules def save_external_loan_schedules(loan_id, schedules, request_user): """Save external loan schedules to database with proper field mapping""" from .models import ExternalLoanRepaymentSchedule try: loan = ExternalLoanApplication.objects.get(pk=loan_id) # Soft delete existing schedules before regenerating ExternalLoanRepaymentSchedule.objects.filter( external_loan_application=loan, status='active' ).update(deleted=True, deleted_at=timezone.now(), deleted_by=request_user, status='deleted') # Create new schedules with all fields for schedule in schedules: ExternalLoanRepaymentSchedule.objects.create( external_loan_application=loan, principal_expected=schedule['principal_expected'], interest_expected=schedule['interest_expected'], total_payment=schedule['total_payment'], expected_date=schedule['expected_date'], ending_balance=schedule['ending_balance'], starting_balance=schedule['starting_balance'], payment_number=schedule['payment_number'], principal_paid=schedule.get('principal_paid', 0.0), interest_paid=schedule.get('interest_paid', 0.0), penalty_paid=schedule.get('penalty_paid', 0.0), penalty_amount=schedule.get('penalty_amount', 0.0), is_fully_paid=schedule.get('is_fully_paid', False), is_overdue=schedule.get('is_overdue', False), days_overdue=schedule.get('days_overdue', 0), added_by=request_user ) return True except Exception as e: print(f"Error saving schedules: {e}") return False def get_external_loan_balance_detailed(loan_id): """Get detailed external loan balance with breakdown""" try: loan = ExternalLoanApplication.objects.get(pk=loan_id) # Get disbursed amount disbursement = ExternalLoanDisbursement.objects.filter( external_loan_application=loan, approval_status='approved' ).first() principal_disbursed = disbursement.disbursed_amount if disbursement else 0 # Get payments payments = ExternalLoanPayments.objects.filter( external_loan_application=loan, payment_status='normal' ).aggregate( total_principal_paid=Sum('princ_paid'), total_interest_paid=Sum('int_paid'), total_penalty_paid=Sum('penalty_paid') ) principal_paid = payments['total_principal_paid'] or 0 interest_paid = payments['total_interest_paid'] or 0 penalty_paid = payments['total_penalty_paid'] or 0 # Get penalties total_penalties = ExternalLoanPenalty.objects.filter( external_loan_application=loan ).aggregate(total=Sum('amount'))['total'] or 0 # Get waivers penalty_waived = ExternalLoanPenaltyWaivered.objects.filter( external_loan_application=loan ).aggregate(total=Sum('amount'))['total'] or 0 interest_waived = ExternalLoanInterestWaivered.objects.filter( external_loan_application=loan ).aggregate(total=Sum('amount'))['total'] or 0 # Calculate expected interest (simplified) expected_interest = calculate_external_loan_interest( principal_disbursed, loan.int_rate, loan.loan_period, loan.int_method ) # Calculate balances principal_balance = principal_disbursed - principal_paid interest_balance = expected_interest - interest_paid - interest_waived penalty_balance = total_penalties - penalty_paid - penalty_waived return { 'principal_balance': max(0, principal_balance), 'interest_balance': max(0, interest_balance), 'penalty_balance': max(0, penalty_balance), 'total_balance': max(0, principal_balance + interest_balance + penalty_balance), 'principal_paid': principal_paid, 'interest_paid': interest_paid, 'penalty_paid': penalty_paid, 'penalty_waived': penalty_waived, 'interest_waived': interest_waived, 'principal_disbursed': principal_disbursed, 'expected_interest': expected_interest, 'total_penalties': total_penalties } except ExternalLoanApplication.DoesNotExist: return { 'principal_balance': 0, 'interest_balance': 0, 'penalty_balance': 0, 'total_balance': 0, 'principal_paid': 0, 'interest_paid': 0, 'penalty_paid': 0, 'penalty_waived': 0, 'interest_waived': 0, 'principal_disbursed': 0, 'expected_interest': 0, 'total_penalties': 0 } def get_external_loan_balance(loan_id): """Get external loan balance - legacy function for compatibility""" balance = get_external_loan_balance_detailed(loan_id) return { 'principal_balance': balance['principal_balance'], 'interest_balance': balance['interest_balance'], 'penalty_balance': balance['penalty_balance'], 'total_balance': balance['total_balance'] } def process_external_loan_payment_detailed(payment_details, request): """Process detailed external loan payment with schedule breakdown and ledger entries for both organizations""" try: with transaction.atomic(): loan_id = payment_details['loan_id'] loan = ExternalLoanApplication.objects.get(pk=loan_id) borrower_org = loan.applicant_organisation lender_org = loan.loan_provider_organisation # Get disbursement to find the original bank account used disbursement = ExternalLoanDisbursement.objects.filter( external_loan_application=loan, approval_status='approved' ).first() if not disbursement or not disbursement.system_transaction: raise Exception("Disbursement transaction not found") # Get the bank account that was used for disbursement (money should return here) lender_bank_account = disbursement.system_transaction.credit_chart # Get the loan asset account (lender side) loan_asset_account = OrganisationSubAccount.objects.get( account_code=f"11101{loan.id}", account_organisation=lender_org ) # Get the loan liability account (borrower side) loan_liability_account = OrganisationSubAccount.objects.get( account_code=f"21101{loan.id}", account_organisation=borrower_org ) # Get borrower's bank account borrower_bank = BankAccounts.objects.filter( chart__account_organisation=borrower_org, status='active' ).first() if not borrower_bank: raise Exception("Borrower bank account not found") borrower_bank_chart = borrower_bank.chart # Parse payment date from django.utils.dateparse import parse_date payment_date = payment_details.get('date_added') if isinstance(payment_date, str): payment_date = parse_date(payment_date) or timezone.now().date() # Generate reference numbers lender_ref = generate_reference_no('assets', lender_org.id, 'ext-loan-pay') borrower_ref = generate_reference_no('liabilities', borrower_org.id, 'ext-loan-pay') # Create payment transaction payment_transaction = ExternalLoanPaymentTransaction.objects.create( amount=payment_details['amount_paid'], external_loan_application=loan, added_by=request.user, payment_date=payment_date, transaction_type='manual' ) total_amount = payment_details.get('amount_paid', 0) # LENDER SIDE: Money coming in, loan asset reducing lender_transaction = SystemTransactions.objects.create( amount=total_amount, heading=f'External Loan Payment Received - {loan.loan_number}', debit_chart=lender_bank_account, # Money coming into lender's bank credit_chart=loan_asset_account, # Reducing loan asset reference_no=lender_ref, branch=lender_org.organisation_branches.first(), voucher_no=payment_details.get('voucher_no', ''), payment_method='bank', transaction_type='normal', coment=f'Loan payment from {borrower_org.name}', depositor_name=borrower_org.name, record_date=payment_date, added_by=request.user ) # BORROWER SIDE: Money leaving, liability reducing borrower_transaction = SystemTransactions.objects.create( amount=total_amount, heading=f'External Loan Payment Made - {loan.loan_number}', debit_chart=loan_liability_account, # Reducing liability credit_chart=borrower_bank_chart, # Money leaving borrower's bank reference_no=borrower_ref, branch=borrower_org.organisation_branches.first(), voucher_no=payment_details.get('voucher_no', ''), payment_method='bank', transaction_type='normal', coment=f'Loan payment to {lender_org.name}', depositor_name=lender_org.name, record_date=payment_date, added_by=request.user ) # Create main transaction record main_transaction = ExternalLoanMainTransactions.objects.create( heading=f'External Loan Payment - {loan.loan_number}', amount=total_amount, payment_method='bank', external_loan_application=loan, transaction_type='ExternalLoanPayment', added_by=request.user, payment_date=payment_date, system_transaction=lender_transaction ) # Allocate payment to schedules remaining_amount = total_amount schedules = ExternalLoanRepaymentSchedule.objects.filter( external_loan_application=loan, status='active', is_fully_paid=False ).order_by('payment_number') for schedule in schedules: if remaining_amount <= 0: break # Calculate what's due for this schedule penalty_due = schedule.penalty_amount - schedule.penalty_paid interest_due = schedule.interest_expected - schedule.interest_paid principal_due = schedule.principal_expected - schedule.principal_paid # Allocate payment: penalty first, then interest, then principal penalty_payment = min(remaining_amount, penalty_due) remaining_amount -= penalty_payment interest_payment = min(remaining_amount, interest_due) remaining_amount -= interest_payment principal_payment = min(remaining_amount, principal_due) remaining_amount -= principal_payment # Update schedule schedule.penalty_paid += penalty_payment schedule.interest_paid += interest_payment schedule.principal_paid += principal_payment # Check if fully paid if (schedule.penalty_paid >= schedule.penalty_amount and schedule.interest_paid >= schedule.interest_expected and schedule.principal_paid >= schedule.principal_expected): schedule.is_fully_paid = True schedule.save() # Create payment record for this schedule if penalty_payment > 0 or interest_payment > 0 or principal_payment > 0: ExternalLoanPayments.objects.create( external_loan_application=loan, external_loan_repayment_schedule=schedule, external_loan_main_transaction=main_transaction, external_loan_payment_transaction=payment_transaction, princ_paid=principal_payment, int_paid=interest_payment, penalty_paid=penalty_payment, payment_date=payment_date, added_by=request.user ) return payment_transaction except Exception as e: print(f"Error processing external loan payment: {e}") import traceback traceback.print_exc() return None def _get_or_create_chart(account_code, account_organisation, defaults): """get_or_create wrapper that avoids savepoint issues by using filter+create.""" obj = OrganisationSubAccount.objects.filter( account_code=account_code, account_organisation=account_organisation ).first() if obj: return obj return OrganisationSubAccount.objects.create( account_code=account_code, account_organisation=account_organisation, **defaults ) def _resolve_borrower_accounts(loan_application, request_user): """Resolve or create the borrower-side ledger accounts needed for disbursement. Run this OUTSIDE any atomic block to avoid savepoint rollback issues.""" borrower_org = loan_application.applicant_organisation lender_org = loan_application.loan_provider_organisation borrower_bank_details = loan_application.bank_account_details or {} # Borrower destination (bank) account destination_account = None if borrower_bank_details.get('account_number') and borrower_bank_details.get('bank_name'): existing = BankAccounts.objects.filter( account_number=borrower_bank_details['account_number'], bank_name=borrower_bank_details['bank_name'], chart__account_organisation=borrower_org ).first() if existing: destination_account = existing.chart else: bank_chart = _get_or_create_chart( account_code=f"BANK_{borrower_org.id}_{borrower_bank_details['account_number'][-4:]}", account_organisation=borrower_org, defaults={ 'account_name': f"Bank - {borrower_bank_details['bank_name']}", 'account_line': 'assets', 'description': f"Bank account at {borrower_bank_details['bank_name']}", 'account_type': 'system', 'added_by': request_user.id, } ) BankAccounts.objects.get_or_create( account_number=borrower_bank_details['account_number'], bank_name=borrower_bank_details['bank_name'], chart__account_organisation=borrower_org, defaults={ 'account_name': borrower_bank_details.get('account_name', f"{borrower_org.name} Account"), 'account_alias': f"{borrower_org.name} - {borrower_bank_details['bank_name']}", 'chart': bank_chart, 'branch': borrower_org.organisation_branches.first(), 'added_by': request_user.id, } ) destination_account = bank_chart if not destination_account: existing_bank = BankAccounts.objects.filter( chart__account_organisation=borrower_org, status='active' ).first() destination_account = existing_bank.chart if existing_bank else _get_or_create_chart( account_code=f"DEF_BANK_{borrower_org.id}", account_organisation=borrower_org, defaults={ 'account_name': f"Default Bank Account - {borrower_org.name}", 'account_line': 'assets', 'description': 'Default bank account for loan receipts', 'account_type': 'system', 'added_by': request_user.id, } ) # Borrower liability parent (External Loans Payable) current_liabilities_chart = OrganisationSubAccount.objects.filter( account_organisation=borrower_org, account_line='liabilities', account_code='sys-21' ).first() or OrganisationSubAccount.objects.filter( account_organisation=borrower_org, account_line='liabilities', parent_id__isnull=True ).first() external_loans_liability_chart = _get_or_create_chart( account_code="sys-2110", account_organisation=borrower_org, defaults={ 'account_name': 'External Loans Payable', 'account_line': 'liabilities', 'description': 'External loans payable to other organizations', 'account_type': 'system', 'parent_id': current_liabilities_chart, 'added_by': request_user.id, } ) # Per-loan liability account loan_liability_account = _get_or_create_chart( account_code=f"21101{loan_application.id}", account_organisation=borrower_org, defaults={ 'account_name': f"Loan from {lender_org.name} - {loan_application.loan_number}", 'account_line': 'liabilities', 'description': f'Loan liability for {loan_application.loan_number}', 'account_type': 'system', 'parent_id': external_loans_liability_chart, 'added_by': request_user.id, } ) # Ensure borrower branch exists borrower_branch = borrower_org.organisation_branches.first() if not borrower_branch: borrower_branch = OrganisationBranch.objects.create( name=f"{borrower_org.name} - Main Branch", branch_organisation=borrower_org, added_by=request_user.id, status='active' ) return destination_account, loan_liability_account, borrower_branch def process_external_loan_disbursement_with_ledger(request, disbursement_data): """Process external loan disbursement with proper ledger entries""" try: loan_application = ExternalLoanApplication.objects.get(pk=disbursement_data['loan_application_id']) source_account = OrganisationSubAccount.objects.get(pk=disbursement_data['source_account_id']) borrower_org = loan_application.applicant_organisation lender_org = loan_application.loan_provider_organisation # --- Resolve all chart accounts OUTSIDE atomic to avoid savepoint rollbacks --- # Lender asset accounts current_assets_chart = OrganisationSubAccount.objects.filter( account_organisation=lender_org, account_line='assets', account_code='sys-11' ).first() or OrganisationSubAccount.objects.filter( account_organisation=lender_org, account_line='assets', parent_id__isnull=True ).first() external_loan_chart = _get_or_create_chart( account_code="sys-1110", account_organisation=lender_org, defaults={ 'account_name': 'External Loans', 'account_line': 'assets', 'description': 'External loans provided to other organizations', 'account_type': 'system', 'parent_id': current_assets_chart, 'added_by': request.user.id, } ) loan_asset_account = _get_or_create_chart( account_code=f"11101{loan_application.id}", account_organisation=lender_org, defaults={ 'account_name': f"Loan to {borrower_org.name} - {loan_application.loan_number}", 'account_line': 'assets', 'description': f'Loan asset for {loan_application.loan_number}', 'account_type': 'system', 'parent_id': external_loan_chart, 'added_by': request.user.id, } ) # Borrower accounts destination_account, loan_liability_account, borrower_branch = _resolve_borrower_accounts( loan_application, request.user ) print(f"[DISBURSE] borrower_org={borrower_org.id} ({borrower_org.name})") print(f"[DISBURSE] destination_account={destination_account.id} ({destination_account.account_name}) org={destination_account.account_organisation_id}") print(f"[DISBURSE] loan_liability_account={loan_liability_account.id} ({loan_liability_account.account_name}) org={loan_liability_account.account_organisation_id}") print(f"[DISBURSE] borrower_branch={borrower_branch.id} ({borrower_branch.name}) org={borrower_branch.branch_organisation_id}") # Parse disbursement date disbursement_date = disbursement_data.get('disbursement_date') if disbursement_date: from django.utils.dateparse import parse_date if isinstance(disbursement_date, str): disbursement_date = parse_date(disbursement_date) or timezone.now().date() else: disbursement_date = timezone.now().date() lender_ref_no = generate_reference_no('assets', lender_org.id, 'ext-loan-disb') borrower_ref_no = generate_reference_no('liabilities', borrower_org.id, 'ext-loan-recv') # --- Atomic block: only the actual transaction/disbursement record writes --- with transaction.atomic(): lender_transaction = SystemTransactions.objects.create( amount=disbursement_data['disbursed_amount'], heading=f'External Loan Disbursement - {loan_application.loan_number}', credit_chart=source_account, debit_chart=loan_asset_account, reference_no=lender_ref_no, branch=source_account.account_organisation.organisation_branches.first(), voucher_no=disbursement_data.get('voucher_no', ''), payment_method=disbursement_data['disbursement_method'], transaction_type='normal', coment=f'Loan disbursement to {borrower_org.name}', depositor_name=borrower_org.name, record_date=disbursement_date, added_by=request.user ) borrower_transaction = SystemTransactions.objects.create( amount=disbursement_data['disbursed_amount'], heading=f'External Loan Received - {loan_application.loan_number}', debit_chart=destination_account, credit_chart=loan_liability_account, reference_no=borrower_ref_no, branch=borrower_branch, voucher_no=disbursement_data.get('voucher_no', ''), payment_method=disbursement_data['disbursement_method'], transaction_type='normal', coment=f'Loan received from {lender_org.name}', depositor_name=lender_org.name, record_date=disbursement_date, added_by=request.user ) print(f"[DISBURSE] borrower_transaction created id={borrower_transaction.id} branch={borrower_transaction.branch_id}") disbursement = ExternalLoanDisbursement.objects.create( external_loan_application=loan_application, disbursed_amount=disbursement_data['disbursed_amount'], disbursement_method=disbursement_data['disbursement_method'], payment_reference=disbursement_data.get('payment_reference', ''), disbursement_notes=disbursement_data.get('disbursement_notes', ''), disbursement_date=timezone.now(), disbursed_by=request.user, approval_status='approved', approved_by=request.user, date_approved=timezone.now(), system_transaction=lender_transaction ) except Exception as e: import traceback traceback.print_exc() return { 'success': False, 'error': str(e), 'message': 'Error processing external loan disbursement' } # Status update and schedule generation outside atomic to avoid savepoint issues ExternalLoanApplication.objects.filter(pk=loan_application.id).update(status='disbursed') schedules = generate_external_loan_schedules_detailed(request, loan_application.id) if schedules: save_external_loan_schedules(loan_application.id, schedules, request.user) return { 'success': True, 'disbursement': disbursement, 'lender_transaction': lender_transaction, 'borrower_transaction': borrower_transaction, 'system_transaction': lender_transaction, 'message': 'External loan disbursed successfully with proper ledger entries for both organizations' } def process_external_loan_payment(payment_details, request): """Process external loan payment""" from .models import ExternalLoanMainTransactions try: loan_id = payment_details['loan_id'] loan = ExternalLoanApplication.objects.get(pk=loan_id) # Create payment transaction payment = ExternalLoanMainTransactions.objects.create( heading=f'External Loan Payment for {loan.applicant_organisation.name}', amount=payment_details['amount_paid'], payment_method=payment_details['payment_method'], external_loan_application=loan, transaction_type='ExternalLoanPayment', added_by=request.user, payment_date=payment_details['date_added'] ) return payment except Exception as e: return None def process_external_loan_recovery(recovery_details, request): """Process external loan recovery""" from .models import ExternalLoanRecovery, ExternalLoanMainTransactions try: loan_id = recovery_details['loan_id'] loan = ExternalLoanApplication.objects.get(pk=loan_id) # Create recovery transaction main_transaction = ExternalLoanMainTransactions.objects.create( heading=f'External Loan Recovery for {loan.applicant_organisation.name}', amount=recovery_details['recovered_ammount'], payment_method=recovery_details['payment_method'], external_loan_application=loan, transaction_type='ExternalLoanRecovery', added_by=request.user, payment_date=recovery_details['recovery_date'] ) # Create recovery record recovery = ExternalLoanRecovery.objects.create( external_loan_application=loan, recovery_added_by=request.user, external_loan_main_transaction=main_transaction, recovered_amount=recovery_details['recovered_ammount'], recovery_date=recovery_details['recovery_date'] ) return recovery except Exception as e: return None def generate_external_loan_schedules(request, loan_id, schedule_type='normal', loan_data=None): """Generate external loan repayment schedules - legacy function for compatibility""" return generate_external_loan_schedules_detailed(request, loan_id, schedule_type, loan_data) def external_loan_schedules_with_payments(loan_id): """Get external loan schedules with payment information""" try: loan = ExternalLoanApplication.objects.get(pk=loan_id) # Get schedules from database if loan is disbursed if loan.status in ['disbursed', 'cleared_off', 'written_off']: schedules = ExternalLoanRepaymentSchedule.objects.filter( external_loan_application=loan, status='active' ).order_by('payment_number') schedule_data = [] for schedule in schedules: # Get payments for this schedule payments = ExternalLoanPayments.objects.filter( external_loan_application=loan, external_loan_repayment_schedule=schedule, payment_status='normal' ).aggregate( total_principal_paid=Sum('princ_paid'), total_interest_paid=Sum('int_paid'), total_penalty_paid=Sum('penalty_paid') ) principal_paid = payments['total_principal_paid'] or 0 interest_paid = payments['total_interest_paid'] or 0 penalty_paid = payments['total_penalty_paid'] or 0 schedule_data.append({ 'id': schedule.id, 'payment_number': schedule.payment_number, 'principal_expected': schedule.principal_expected, 'interest_expected': schedule.interest_expected, 'total_payment': schedule.total_payment, 'starting_balance': schedule.starting_balance, 'ending_balance': schedule.ending_balance, 'expected_date': schedule.expected_date, 'payments': { 'total_princ_paid': principal_paid, 'total_int_paid': interest_paid, 'total_penalty_paid': penalty_paid, 'balance': (schedule.principal_expected + schedule.interest_expected) - (principal_paid + interest_paid) }, 'is_paid_off': 'true' if (schedule.principal_expected + schedule.interest_expected) <= (principal_paid + interest_paid) else 'false' }) else: # Generate schedules for preview schedules = generate_external_loan_schedules_detailed(None, loan_id) schedule_data = schedules # Get loan balance balance = get_external_loan_balance_detailed(loan_id) return { 'loan_schedules': schedule_data, 'schedule_due': { 'principal_expected': balance['principal_balance'], 'interest_expected': balance['interest_balance'], 'penalty_expected': balance['penalty_balance'], 'total_payment': balance['total_balance'] }, 'next_schedule': {}, 'reschedule': {} } except Exception as e: return { 'loan_schedules': [], 'schedule_due': {}, 'next_schedule': {}, 'reschedule': {} } def _reverse_disbursement_ledger(loan, request_user): """Create reversal SystemTransactions for both lender and borrower sides.""" try: disbursement = ExternalLoanDisbursement.objects.filter( external_loan_application=loan, deleted=False ).first() if not disbursement or not disbursement.system_transaction: print(f"[REVERSAL] No disbursement or system_transaction found for loan {loan.id}") return original = disbursement.system_transaction # lender transaction lender_org = loan.loan_provider_organisation borrower_org = loan.applicant_organisation now = timezone.now().date() # Reverse lender transaction: swap debit/credit lender_ref = generate_reference_no('assets', lender_org.id, 'ext-loan-rev') SystemTransactions.objects.create( amount=original.amount, heading=f'Reversal - External Loan Disbursement - {loan.loan_number}', debit_chart=original.credit_chart, credit_chart=original.debit_chart, reference_no=lender_ref, branch=original.branch, payment_method=original.payment_method, transaction_type='normal', coment=f'Reversal of loan disbursement to {borrower_org.name}', depositor_name=borrower_org.name, record_date=now, added_by=request_user ) print(f"[REVERSAL] Lender reversal created for loan {loan.id}") # Reverse borrower transaction: find by loan liability account loan_liability_account = OrganisationSubAccount.objects.filter( account_code=f"21101{loan.id}", account_organisation=borrower_org ).first() if loan_liability_account: borrower_tx = SystemTransactions.objects.filter( credit_chart=loan_liability_account ).order_by('-id').first() if borrower_tx: borrower_ref = generate_reference_no('liabilities', borrower_org.id, 'ext-loan-rev') SystemTransactions.objects.create( amount=borrower_tx.amount, heading=f'Reversal - External Loan Received - {loan.loan_number}', debit_chart=borrower_tx.credit_chart, credit_chart=borrower_tx.debit_chart, reference_no=borrower_ref, branch=borrower_tx.branch, payment_method=borrower_tx.payment_method, transaction_type='normal', coment=f'Reversal of loan received from {lender_org.name}', depositor_name=lender_org.name, record_date=now, added_by=request_user ) print(f"[REVERSAL] Borrower reversal created for loan {loan.id}") else: print(f"[REVERSAL] No borrower transaction found for liability account {loan_liability_account.id}") else: print(f"[REVERSAL] No liability account found for loan {loan.id}") except Exception: import traceback traceback.print_exc() def delete_external_loan_by_id(loan_id, delete_type='soft', reason=None, user_id=None, branch=None, request_user=None): """Delete external loan application""" from django.utils import timezone from users.audit_log_helper import add_system_audit_trail now = timezone.now() df = dict(deleted=True, deleted_at=now) if user_id: df['deleted_by_id'] = user_id try: loan = ExternalLoanApplication.objects.get(pk=loan_id) if not branch: branch = getattr(loan.applicant_organisation, 'organisation_branches', None) branch = branch.first() if branch else None if user_id and branch: try: from users.models import User user = User.objects.filter(pk=user_id).first() if user: add_system_audit_trail( 'transaction_management', 'delete_external_loan', f'Deleted External Loan {loan.loan_number} for {loan.applicant_organisation.name}', reason or '', {}, {}, user, branch ) except Exception: pass if delete_type == 'permanent': if loan.status == 'disbursed': _reverse_disbursement_ledger(loan, request_user) ExternalLoanPayments.objects.filter(external_loan_application=loan).update(**df) ExternalLoanPaymentTransaction.objects.filter(external_loan_application=loan).update(**df) ExternalLoanPenalty.objects.filter(external_loan_application=loan).update(**df) ExternalLoanPenaltyWaivered.objects.filter(external_loan_application=loan).update(**df) ExternalLoanInterestWaivered.objects.filter(external_loan_application=loan).update(**df) ExternalLoanRepaymentSchedule.objects.filter(external_loan_application=loan).update(**df) ExternalRescheduledLoans.objects.filter(external_loan_application=loan).update(**df) ExternalLoanTopUp.objects.filter(external_loan_application=loan).update(**df) ExternalLoanApproval.objects.filter(external_loan_application=loan).update(**df) ExternalLoanDisbursement.objects.filter(external_loan_application=loan).update(**df) ExternalLoanWriteOff.objects.filter(external_loan_application=loan).update(**df) ExternalLoanTransfer.objects.filter(external_loan_application=loan).update(**df) ExternalLoanGuarantors.objects.filter(external_loan_application=loan).update(**df) ExternalLoanApplicationSecurity.objects.filter(external_loan_application=loan).update(**df) ExternalLoanIncomeSource.objects.filter(external_loan_application=loan).update(**df) ExternalLoanMainTransactions.objects.filter(external_loan_application=loan).update(**df) ExternalLoanRecovery.objects.filter(external_loan_application=loan).update(**df) loan.deleted = True loan.is_deleted = True loan.deleted_at = now if user_id: loan.deleted_by_id = user_id loan.save() else: if loan.status == 'disbursed': _reverse_disbursement_ledger(loan, request_user) loan.is_deleted = True loan.reason_for_delete = reason loan.save() return True except Exception as e: return False