""" Backfill General Ledger from Loan Transactions This command reads all existing Loan and Repayment records and posts the corresponding journal entries to the GL. Journal entry rules: Loan disbursement: Dr Loan Portfolio – Performing (1020) principal_amount Cr Cash and Bank Balances (1010) principal_amount Processing fee (if > 0): Dr Cash and Bank Balances (1010) processing_fee Cr Fee Income – Loans (401002) processing_fee Repayment received: Dr Cash and Bank Balances (1010) amount Cr Loan Portfolio – Performing (1020) amount Interest income (final repayment on a paid loan, if interest_amount > 0): Dr Cash and Bank Balances (1010) interest_amount Cr Interest Income – Loans (4010) interest_amount """ import datetime import logging from decimal import Decimal from django.contrib.auth import get_user_model from django.core.management.base import BaseCommand from django.db import transaction from django.db.models import Min, Sum from accounting.models import Account, GeneralLedger, JournalEntry, JournalEntryLine from accounting.services.accounting_service import AccountingService from loans.models import Loan, Repayment User = get_user_model() logger = logging.getLogger(__name__) # Account codes CODE_CASH = '1010' CODE_LOAN_PORTFOLIO = '1020' CODE_INTEREST_INCOME = '4010' CODE_FEE_INCOME = '401002' CODE_EQUITY = '3010' class Command(BaseCommand): help = 'Backfill the General Ledger from existing Loan and Repayment records' def add_arguments(self, parser): parser.add_argument( '--dry-run', action='store_true', help='Print what would be posted without actually posting anything', ) def handle(self, *args, **options): dry_run = options['dry_run'] if dry_run: self.stdout.write(self.style.WARNING('DRY RUN – nothing will be written.\n')) # ------------------------------------------------------------------ # # Load required accounts # ------------------------------------------------------------------ # accounts = {} missing = False for code in [CODE_CASH, CODE_LOAN_PORTFOLIO, CODE_INTEREST_INCOME, CODE_FEE_INCOME, CODE_EQUITY]: try: accounts[code] = Account.objects.get(code=code) except Account.DoesNotExist: self.stdout.write( self.style.ERROR(f'Account {code} not found – aborting.') ) missing = True if missing: return # ------------------------------------------------------------------ # # Get (or create) system user # ------------------------------------------------------------------ # system_user = User.objects.filter(is_superuser=True).order_by('date_joined').first() if system_user is None: self.stdout.write(self.style.ERROR('No superuser found. Create one first.')) return service = AccountingService() disb_count = 0 fee_count = 0 rep_count = 0 int_count = 0 equity_count = 0 # ------------------------------------------------------------------ # # 1. Loan disbursements # ------------------------------------------------------------------ # loans = Loan.objects.filter( status__in=['active', 'paid', 'defaulted', 'rolled_over', 'written_off'], is_deleted=False, disbursement_date__isnull=False, ).select_related('approved_by') self.stdout.write(f'Processing {loans.count()} loans...') for loan in loans: # --- disbursement entry --- ref_disb = f'DISB-{loan.loan_number}' if JournalEntry.objects.filter(reference_number=ref_disb).exists(): self.stdout.write(f' SKIP {ref_disb} (already posted)') else: tx_date = loan.disbursement_date.date() je = JournalEntry( reference_number=ref_disb, transaction_date=tx_date, description=f'Loan disbursement – {loan.loan_number}', status='draft', loan=loan, created_by=system_user, ) if not dry_run: try: with transaction.atomic(): je.save() JournalEntryLine.objects.create( journal_entry=je, account=accounts[CODE_LOAN_PORTFOLIO], description=f'Loan disbursement – {loan.loan_number}', debit_amount=loan.principal_amount, credit_amount=Decimal('0.00'), line_number=1, ) JournalEntryLine.objects.create( journal_entry=je, account=accounts[CODE_CASH], description=f'Loan disbursement – {loan.loan_number}', debit_amount=Decimal('0.00'), credit_amount=loan.principal_amount, line_number=2, ) service.post_journal_entry(je, system_user) disb_count += 1 self.stdout.write(f' POST {ref_disb} KES {loan.principal_amount}') except Exception as exc: self.stdout.write( self.style.WARNING(f' WARN {ref_disb}: {exc}') ) else: self.stdout.write( f' [dry-run] WOULD POST {ref_disb} KES {loan.principal_amount}' ) disb_count += 1 # --- processing fee entry --- if loan.processing_fee and loan.processing_fee > 0: ref_fee = f'FEE-{loan.loan_number}' if JournalEntry.objects.filter(reference_number=ref_fee).exists(): self.stdout.write(f' SKIP {ref_fee} (already posted)') else: tx_date = loan.disbursement_date.date() je_fee = JournalEntry( reference_number=ref_fee, transaction_date=tx_date, description=f'Processing fee – {loan.loan_number}', status='draft', loan=loan, created_by=system_user, ) if not dry_run: try: with transaction.atomic(): je_fee.save() JournalEntryLine.objects.create( journal_entry=je_fee, account=accounts[CODE_CASH], description=f'Processing fee – {loan.loan_number}', debit_amount=loan.processing_fee, credit_amount=Decimal('0.00'), line_number=1, ) JournalEntryLine.objects.create( journal_entry=je_fee, account=accounts[CODE_FEE_INCOME], description=f'Processing fee – {loan.loan_number}', debit_amount=Decimal('0.00'), credit_amount=loan.processing_fee, line_number=2, ) service.post_journal_entry(je_fee, system_user) fee_count += 1 self.stdout.write(f' POST {ref_fee} KES {loan.processing_fee}') except Exception as exc: self.stdout.write( self.style.WARNING(f' WARN {ref_fee}: {exc}') ) else: self.stdout.write( f' [dry-run] WOULD POST {ref_fee} KES {loan.processing_fee}' ) fee_count += 1 # ------------------------------------------------------------------ # # 2. Repayments # ------------------------------------------------------------------ # repayments = Repayment.objects.select_related('loan').order_by('payment_date') self.stdout.write(f'\nProcessing {repayments.count()} repayments...') for repayment in repayments: loan = repayment.loan ref_rep = f'REP-{repayment.receipt_number or repayment.id}' if JournalEntry.objects.filter(reference_number=ref_rep).exists(): self.stdout.write(f' SKIP {ref_rep} (already posted)') continue tx_date = repayment.payment_date.date() je_rep = JournalEntry( reference_number=ref_rep, transaction_date=tx_date, description=f'Repayment – {loan.loan_number}', status='draft', loan=loan, created_by=system_user, ) if not dry_run: try: with transaction.atomic(): je_rep.save() JournalEntryLine.objects.create( journal_entry=je_rep, account=accounts[CODE_CASH], description=f'Repayment received – {loan.loan_number}', debit_amount=repayment.amount, credit_amount=Decimal('0.00'), line_number=1, ) JournalEntryLine.objects.create( journal_entry=je_rep, account=accounts[CODE_LOAN_PORTFOLIO], description=f'Repayment received – {loan.loan_number}', debit_amount=Decimal('0.00'), credit_amount=repayment.amount, line_number=2, ) service.post_journal_entry(je_rep, system_user) rep_count += 1 self.stdout.write(f' POST {ref_rep} KES {repayment.amount}') except Exception as exc: self.stdout.write( self.style.WARNING(f' WARN {ref_rep}: {exc}') ) else: self.stdout.write( f' [dry-run] WOULD POST {ref_rep} KES {repayment.amount}' ) rep_count += 1 # --- interest income on final repayment --- if ( loan.status == 'paid' and loan.interest_amount and loan.interest_amount > 0 ): ref_int = f'INT-{loan.loan_number}' if JournalEntry.objects.filter(reference_number=ref_int).exists(): self.stdout.write(f' SKIP {ref_int} (already posted)') else: je_int = JournalEntry( reference_number=ref_int, transaction_date=tx_date, description=f'Interest income – {loan.loan_number}', status='draft', loan=loan, created_by=system_user, ) if not dry_run: try: with transaction.atomic(): je_int.save() JournalEntryLine.objects.create( journal_entry=je_int, account=accounts[CODE_CASH], description=f'Interest income – {loan.loan_number}', debit_amount=loan.interest_amount, credit_amount=Decimal('0.00'), line_number=1, ) JournalEntryLine.objects.create( journal_entry=je_int, account=accounts[CODE_INTEREST_INCOME], description=f'Interest income – {loan.loan_number}', debit_amount=Decimal('0.00'), credit_amount=loan.interest_amount, line_number=2, ) service.post_journal_entry(je_int, system_user) int_count += 1 self.stdout.write( f' POST {ref_int} KES {loan.interest_amount}' ) except Exception as exc: self.stdout.write( self.style.WARNING(f' WARN {ref_int}: {exc}') ) else: self.stdout.write( f' [dry-run] WOULD POST {ref_int} KES {loan.interest_amount}' ) int_count += 1 # ------------------------------------------------------------------ # # 3. Opening equity / capital entry # ------------------------------------------------------------------ # self.stdout.write('\nStep 3 – Opening equity entry...') total_disbursed = Loan.objects.filter( status__in=['active', 'paid', 'defaulted', 'rolled_over', 'written_off'], is_deleted=False, ).aggregate(total=Sum('principal_amount'))['total'] or Decimal('0.00') total_repaid = Repayment.objects.aggregate( total=Sum('amount') )['total'] or Decimal('0.00') cash_balance_now = total_repaid - total_disbursed self.stdout.write( f' total_disbursed={total_disbursed} ' f'total_repaid={total_repaid} ' f'cash_balance_now={cash_balance_now}' ) ref_equity = 'OPEN-EQUITY-001' if cash_balance_now >= 0: self.stdout.write( f' Cash balance is not negative ({cash_balance_now}); ' 'no opening equity entry needed.' ) elif JournalEntry.objects.filter(reference_number=ref_equity).exists(): self.stdout.write(f' SKIP {ref_equity} (already posted)') else: equity_amount = abs(cash_balance_now) # Determine date: earliest disbursement date minus 1 day earliest = Loan.objects.filter( is_deleted=False, disbursement_date__isnull=False, ).aggregate(earliest=Min('disbursement_date'))['earliest'] if earliest is not None: earliest_date = earliest.date() if hasattr(earliest, 'date') else earliest equity_date = earliest_date - datetime.timedelta(days=1) else: equity_date = datetime.date(datetime.date.today().year, 1, 1) je_eq = JournalEntry( reference_number=ref_equity, transaction_date=equity_date, description='Opening capital – equity injection to fund loan portfolio', status='draft', created_by=system_user, ) if not dry_run: try: with transaction.atomic(): je_eq.save() JournalEntryLine.objects.create( journal_entry=je_eq, account=accounts[CODE_CASH], description='Opening capital – equity injection to fund loan portfolio', debit_amount=equity_amount, credit_amount=Decimal('0.00'), line_number=1, ) JournalEntryLine.objects.create( journal_entry=je_eq, account=accounts[CODE_EQUITY], description='Opening capital – equity injection to fund loan portfolio', debit_amount=Decimal('0.00'), credit_amount=equity_amount, line_number=2, ) service.post_journal_entry(je_eq, system_user) equity_count += 1 self.stdout.write( f' POST {ref_equity} KES {equity_amount} date={equity_date}' ) except Exception as exc: self.stdout.write(self.style.WARNING(f' WARN {ref_equity}: {exc}')) else: self.stdout.write( f' [dry-run] WOULD POST {ref_equity} KES {equity_amount} date={equity_date}' ) equity_count += 1 # ------------------------------------------------------------------ # # 4. Recalculate running balances for all affected accounts # The posting order (equity entry posted last but dated first) can # leave running-balance fields out of order. Fix them all now. # ------------------------------------------------------------------ # self.stdout.write('\nStep 4 – Recalculating GL running balances...') if not dry_run: affected_codes = [CODE_CASH, CODE_LOAN_PORTFOLIO, CODE_EQUITY, CODE_INTEREST_INCOME, CODE_FEE_INCOME] fixed_total = 0 for code in affected_codes: if code not in accounts: continue acc = accounts[code] entries = list( GeneralLedger.objects.filter(account=acc) .order_by('transaction_date', 'posted_at', 'id') ) if not entries: continue running = Decimal('0.00') fixed = 0 for gl in entries: if acc.account_type in ('asset', 'expense'): running = running + gl.debit_amount - gl.credit_amount else: running = running + gl.credit_amount - gl.debit_amount if gl.balance != running: gl.balance = running gl.save(update_fields=['balance']) fixed += 1 if fixed: self.stdout.write(f' Fixed {fixed} balance row(s) for account {code}') fixed_total += fixed else: self.stdout.write(f' OK {code} – all balances correct') # Clear memory and DB cache so next balance reads are fresh from django.core.cache import cache as _cache _cache.clear() try: from accounting.models import AccountBalance AccountBalance.objects.all().delete() except Exception: pass self.stdout.write(f' Cache cleared. {fixed_total} GL row(s) corrected.') else: self.stdout.write(' [dry-run] Skipping balance recalculation.') # ------------------------------------------------------------------ # # Summary # ------------------------------------------------------------------ # self.stdout.write('\n' + '=' * 60) prefix = '[DRY RUN] Would have posted' if dry_run else 'Posted' self.stdout.write( self.style.SUCCESS( f'{prefix}: {disb_count} disbursements, ' f'{rep_count} repayments, ' f'{fee_count} fee entries, ' f'{int_count} interest entries, ' f'{equity_count} equity entries.' ) )