#!/usr/bin/env python """gl_fix5.py - Fix missing loan disbursements + verify final state""" import sys, os, datetime, pathlib from decimal import Decimal PROJECT_ROOT = os.path.dirname(os.path.abspath(__file__)) sys.path.insert(0, PROJECT_ROOT) os.environ['DB_NAME']='xygbfpsg_loans'; os.environ['DB_USER']='xygbfpsg_graz' os.environ['DB_PASSWORD']='j.ez-xy6##y.rllB'; os.environ['DB_HOST']='localhost' os.environ['DB_PORT']='3306'; os.environ['DJANGO_SETTINGS_MODULE']='branch_system.settings' try: import dotenv as _d; _d.load_dotenv = lambda *a,**kw: None except ImportError: pass import django; django.setup() from django.conf import settings as _s _s.DATABASES['default'].update({'NAME':'xygbfpsg_loans','USER':'xygbfpsg_graz', 'PASSWORD':'j.ez-xy6##y.rllB','HOST':'localhost','PORT':'3306'}) from django import db as _db; _db.connections.close_all() def p(m): print(str(m), flush=True) def sep(): print("-"*60, flush=True) ZERO = Decimal('0.00') sep(); p(" GL Fix 5 - Missing Disbursements"); p(" "+datetime.datetime.now().strftime('%H:%M:%S')); sep() from accounting.models import Account, GeneralLedger, JournalEntry, JournalEntryLine, AccountBalance, FiscalPeriod from loans.models import Loan, Repayment from django.db import transaction as TX from django.db.models import Sum, Min from django.utils import timezone as tz User = __import__('django.contrib.auth', fromlist=['get_user_model']).get_user_model() su = User.objects.filter(is_superuser=True).order_by('date_joined').first() now = tz.now() today = datetime.date.today() ZERO = Decimal('0.00') cash_acc = Account.objects.get(code='1010') lp_acc = Account.objects.get(code='1020') eq_acc = Account.objects.get(code='3010') # ── STEP 1: Find loans with no DISB- entry in GL ───────────────────────── p("Step 1 - Finding loans missing from GL...") existing_refs = set(JournalEntry.objects.values_list('reference_number', flat=True)) missing_disb = [] for loan in Loan.objects.filter(is_deleted=False, disbursement_date__isnull=False): ref = 'DISB-' + loan.loan_number if ref not in existing_refs and loan.principal_amount and loan.principal_amount > ZERO: tx = loan.disbursement_date.date() if hasattr(loan.disbursement_date,'date') else loan.disbursement_date missing_disb.append((ref, tx, loan, loan.principal_amount)) p(" Missing disbursement entries: " + str(len(missing_disb))) total_missing = sum(s[3] for s in missing_disb) p(" Total missing amount: KES " + str(total_missing)) if missing_disb: # Ensure fiscal periods exist for all dates import calendar as cal all_dates = {s[1] for s in missing_disb} years = {d.year for d in all_dates} for year in years: for month in range(1,13): ms = datetime.date(year,month,1) me = datetime.date(year,month,cal.monthrange(year,month)[1]) FiscalPeriod.objects.get_or_create( name=str(year)+"-"+str(month).zfill(2), defaults={'period_type':'monthly','start_date':ms,'end_date':me,'status':'open'}) # Reopen closed periods covering these dates for d in all_dates: for fp in FiscalPeriod.objects.filter(start_date__lte=d,end_date__gte=d,status='closed'): fp.status='open'; fp.closed_at=None fp.save(update_fields=['status','closed_at']) p(" Reopened period: "+fp.name) BATCH = 200 total_posted = 0 for i in range(0, len(missing_disb), BATCH): batch = missing_disb[i:i+BATCH] with TX.atomic(): JournalEntry.objects.bulk_create([ JournalEntry(reference_number=ref, transaction_date=tx, description='Loan disbursement - '+loan.loan_number, status='posted', loan=loan, created_by=su, posted_by=su, posted_at=now) for ref,tx,loan,amt in batch], batch_size=BATCH) refs = [s[0] for s in batch] je_map = {je.reference_number: je.id for je in JournalEntry.objects.filter(reference_number__in=refs) .only('id','reference_number')} lines = [] for ref,tx,loan,amt in batch: jid = je_map[ref] lines.append(JournalEntryLine(journal_entry_id=jid, account=lp_acc, description='Dr Loan Portfolio', debit_amount=amt, credit_amount=ZERO, line_number=1)) lines.append(JournalEntryLine(journal_entry_id=jid, account=cash_acc, description='Cr Cash', debit_amount=ZERO, credit_amount=amt, line_number=2)) JournalEntryLine.objects.bulk_create(lines, batch_size=500) jids = list(je_map.values()) line_map = {(ln.journal_entry_id, ln.line_number): ln.id for ln in JournalEntryLine.objects.filter(journal_entry_id__in=jids) .only('id','journal_entry_id','line_number')} gl_rows = [] for ref,tx,loan,amt in batch: jid = je_map[ref] gl_rows.append(GeneralLedger(account=lp_acc, journal_entry_id=jid, journal_entry_line_id=line_map.get((jid,1)), transaction_date=tx, description='Dr Loan Portfolio', reference_number=ref, debit_amount=amt, credit_amount=ZERO, balance=ZERO, branch=None, posted_at=now, posted_by=su)) gl_rows.append(GeneralLedger(account=cash_acc, journal_entry_id=jid, journal_entry_line_id=line_map.get((jid,2)), transaction_date=tx, description='Cr Cash', reference_number=ref, debit_amount=ZERO, credit_amount=amt, balance=ZERO, branch=None, posted_at=now, posted_by=su)) GeneralLedger.objects.bulk_create(gl_rows, batch_size=500) total_posted += len(batch) p(" Batch "+str(i//BATCH+1)+": posted "+str(len(batch))+" disbursements") p(" Total posted: "+str(total_posted)+" disbursements, KES "+str(total_missing)) # Also fix opening equity for the new disbursements # Recalculate shortfall total_out2 = Loan.objects.filter(is_deleted=False).aggregate(t=Sum('principal_amount'))['t'] or ZERO total_in2 = Repayment.objects.aggregate(t=Sum('amount'))['t'] or ZERO shortfall2 = total_out2 - total_in2 ref_eq = 'OPEN-EQUITY-001' old_eq_je = JournalEntry.objects.filter(reference_number=ref_eq).first() if old_eq_je: old_eq_line = JournalEntryLine.objects.filter(journal_entry=old_eq_je, account=cash_acc).first() old_eq_amt = old_eq_line.debit_amount if old_eq_line else ZERO if old_eq_amt != shortfall2: p(" Equity injection needs update: old="+str(old_eq_amt)+" new="+str(shortfall2)) # Delete old and repost ids = [old_eq_je.id] GeneralLedger.objects.filter(journal_entry_id__in=ids).delete() JournalEntryLine.objects.filter(journal_entry_id__in=ids).delete() old_eq_je.delete() earliest = Loan.objects.filter(is_deleted=False,disbursement_date__isnull=False ).aggregate(e=Min('disbursement_date'))['e'] eq_date = (earliest.date() if hasattr(earliest,'date') else earliest) - datetime.timedelta(days=1) with TX.atomic(): je_eq = JournalEntry.objects.create(reference_number=ref_eq, transaction_date=eq_date, description='Opening capital injection', status='posted', created_by=su, posted_by=su, posted_at=now) ln1 = JournalEntryLine.objects.create(journal_entry=je_eq, account=cash_acc, description='Dr Cash', debit_amount=shortfall2, credit_amount=ZERO, line_number=1) ln2 = JournalEntryLine.objects.create(journal_entry=je_eq, account=eq_acc, description='Cr Share Capital', debit_amount=ZERO, credit_amount=shortfall2, line_number=2) GeneralLedger.objects.create(account=cash_acc, journal_entry=je_eq, journal_entry_line=ln1, transaction_date=eq_date, description='Dr Cash', reference_number=ref_eq, debit_amount=shortfall2, credit_amount=ZERO, balance=ZERO, branch=None, posted_at=now, posted_by=su) GeneralLedger.objects.create(account=eq_acc, journal_entry=je_eq, journal_entry_line=ln2, transaction_date=eq_date, description='Cr Share Capital', reference_number=ref_eq, debit_amount=ZERO, credit_amount=shortfall2, balance=ZERO, branch=None, posted_at=now, posted_by=su) p(" Updated equity injection to KES "+str(shortfall2)) p("Step 1 done.") # ── STEP 2: Recalculate ALL running balances ────────────────────────────── p("Step 2 - Recalculating all GL running balances...") fixed = 0 for acc in Account.objects.filter(ledger_entries__isnull=False).distinct(): entries = list(GeneralLedger.objects.filter(account=acc) .order_by('transaction_date','posted_at','id')) if not entries: continue running = ZERO; to_upd = [] 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; to_upd.append(gl) if to_upd: GeneralLedger.objects.bulk_update(to_upd, ['balance'], batch_size=500) fixed += len(to_upd) p(" Fixed "+str(fixed)+" rows.") p("Step 2 done.") # ── STEP 3: Clear cache + restart ──────────────────────────────────────── try: from django.core.cache import cache; cache.clear(); p("Cache cleared.") except Exception: pass try: n,_=AccountBalance.objects.all().delete(); p("Deleted "+str(n)+" AccountBalance rows.") except Exception: pass wsgi = pathlib.Path(PROJECT_ROOT)/'passenger_wsgi.py' if wsgi.exists(): wsgi.touch(); p("Touched passenger_wsgi.py") # ── FINAL SUMMARY ──────────────────────────────────────────────────────── sep(); p("FINAL STATE "+datetime.datetime.now().strftime('%H:%M:%S')); sep() from accounting.services.accounting_service import AccountingService svc = AccountingService() def _sum(t): tot=ZERO for a in Account.objects.filter(account_type=t,is_active=True): try: tot+=svc.calculate_account_balance(a,today,None) except Exception: pass return tot ta=_sum('asset'); tl=_sum('liability'); te=_sum('equity') ti=_sum('income'); tx2=_sum('expense') net=ti-tx2 p("Total Assets: KES "+"{:>14,.2f}".format(ta)) p("Total Liabilities: KES "+"{:>14,.2f}".format(tl)) p("Total Equity: KES "+"{:>14,.2f}".format(te)) p("Total Income: KES "+"{:>14,.2f}".format(ti)) p("Total Expenses: KES "+"{:>14,.2f}".format(tx2)) p("Net Income: KES "+"{:>14,.2f}".format(net)) p("Assets = L+E+Net: "+("YES" if abs(ta-tl-te-net)12,.2f}".format(bal)) except Exception: pass # Actual data check p("") total_principal=Loan.objects.filter(is_deleted=False).aggregate(t=Sum('principal_amount'))['t'] or ZERO total_repaid=Repayment.objects.aggregate(t=Sum('amount'))['t'] or ZERO p("Loans disbursed: KES "+"{:>14,.2f}".format(total_principal)) p("Repayments made: KES "+"{:>14,.2f}".format(total_repaid)) p("Outstanding loans: KES "+"{:>14,.2f}".format(total_principal-total_repaid)) sep() p("DONE"); sep()