#!/usr/bin/env python """ Haven Grazuri - GL Fix (bulk version - fast) Run: python deploy_gl_fix.py [--dry-run] """ import sys, os, datetime, pathlib, calendar, uuid 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(msg): print(msg, flush=True) def ok(msg): print("[OK] " + str(msg), flush=True) def inf(msg):print("[..] " + str(msg), flush=True) def sep(): print("-"*55, flush=True) DRY = '--dry-run' in sys.argv ZERO = Decimal('0.00') sep() p(" Haven Grazuri GL Fix (bulk) " + datetime.datetime.now().strftime('%H:%M:%S')) if DRY: p(" DRY RUN - nothing will be written") sep() # ── imports ─────────────────────────────────────────────────────────────── from django.db import transaction as TX from django.db.models import Min, Sum from django.utils import timezone as tz from accounting.models import (Account, GeneralLedger, JournalEntry, JournalEntryLine, FiscalPeriod, AccountBalance) from loans.models import Loan, Repayment from django.contrib.auth import get_user_model User = get_user_model() # ── 1. Ensure accounts ──────────────────────────────────────────────────── inf("Step 1 - Ensuring GL accounts...") su = User.objects.filter(is_superuser=True).order_by('date_joined').first() REQUIRED = [ ('1010','Cash and Bank Balances','asset','current_asset'), ('1020','Loan Portfolio - Performing','asset','loan_portfolio'), ('4010','Interest Income - Loans','income','interest_income'), ('401002','Fee Income','income','fee_income'), ('3010','Share Capital','equity',None), ('3020','Retained Earnings','equity',None), ] for code,name,atype,subtype in REQUIRED: d = {'name':name,'account_type':atype,'description':name, 'is_active':True,'is_system_account':True,'created_by':su} if subtype: d['subtype'] = subtype acc,c = Account.objects.get_or_create(code=code, defaults=d) p(" " + ("[+]" if c else "[ ]") + " " + code + " " + acc.name) accts = {code: Account.objects.get(code=code) for code in ['1010','1020','4010','401002','3010']} ok("Step 1 done.") # ── 2. Fiscal periods ───────────────────────────────────────────────────── inf("Step 2 - Ensuring fiscal periods...") all_dates = set() for d in Loan.objects.filter(disbursement_date__isnull=False).values_list('disbursement_date',flat=True): all_dates.add(d.date() if hasattr(d,'date') else d) for d in Repayment.objects.values_list('payment_date',flat=True): if d: all_dates.add(d.date() if hasattr(d,'date') else d) today = datetime.date.today() years = sorted({d.year for d in all_dates} | {today.year, today.year+1}) created = 0 for year in years: for month in range(1,13): ms = datetime.date(year,month,1) me = datetime.date(year,month,calendar.monthrange(year,month)[1]) _,c = FiscalPeriod.objects.get_or_create( name=str(year)+"-"+str(month).zfill(2), defaults={'period_type':'monthly','start_date':ms,'end_date':me,'status':'open'}) if c: created += 1 reopened = 0 for fp in FiscalPeriod.objects.filter(status='closed'): for tx in all_dates: if fp.start_date <= tx <= fp.end_date: fp.status='open'; fp.closed_at=None fp.save(update_fields=['status','closed_at']) reopened+=1; p(" [REOPENED] "+fp.name); break p(" Created: "+str(created)+" Reopened: "+str(reopened)) ok("Step 2 done.") # ── 3. Bulk backfill GL ─────────────────────────────────────────────────── inf("Step 3 - Bulk backfilling GL...") # Find already-posted references so we skip them existing_refs = set(JournalEntry.objects.values_list('reference_number', flat=True)) now = tz.now() # Build all JournalEntry + JournalEntryLine objects in memory, then bulk insert je_list = [] # JournalEntry instances (unsaved) line_list = [] # JournalEntryLine instances (unsaved, je set after bulk_create) # Each entry is: (ref, tx_date, description, loan_or_none, debit_acct, credit_acct, amount) entry_specs = [] # --- Disbursements --- loans = Loan.objects.filter( status__in=['active','paid','defaulted','rolled_over','written_off'], is_deleted=False, disbursement_date__isnull=False) p(" Loans: " + str(loans.count())) for loan in loans: ref = 'DISB-' + loan.loan_number if ref in existing_refs: continue tx = loan.disbursement_date.date() if hasattr(loan.disbursement_date,'date') else loan.disbursement_date entry_specs.append((ref, tx, 'Disbursement '+loan.loan_number, loan, accts['1020'], accts['1010'], loan.principal_amount)) # --- Repayments --- reps = Repayment.objects.select_related('loan').order_by('payment_date') p(" Repayments: " + str(reps.count())) for r in reps: ref = 'REP-' + str(r.receipt_number or r.id) if ref in existing_refs: continue tx = r.payment_date.date() if hasattr(r.payment_date,'date') else r.payment_date entry_specs.append((ref, tx, 'Repayment '+r.loan.loan_number, r.loan, accts['1010'], accts['1020'], r.amount)) # --- Opening equity --- total_out = Loan.objects.filter(is_deleted=False).aggregate(t=Sum('principal_amount'))['t'] or ZERO total_in = Repayment.objects.aggregate(t=Sum('amount'))['t'] or ZERO shortfall = total_out - total_in ref_eq = 'OPEN-EQUITY-001' if shortfall > 0 and ref_eq not in existing_refs: 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) if earliest else datetime.date(today.year,1,1) entry_specs.append((ref_eq, eq_date, 'Opening capital injection', None, accts['1010'], accts['3010'], shortfall)) p(" Entries to post: " + str(len(entry_specs))) if DRY: for ref,tx,desc,_,da,ca,amt in entry_specs: p(" DRY " + ref + " KES " + str(amt)) ok("Step 3 done (dry run).") elif entry_specs: with TX.atomic(): # A: bulk insert JournalEntry — MySQL bulk_create does NOT return PKs je_objects = [] for ref,tx,desc,loan,da,ca,amt in entry_specs: kw = dict(reference_number=ref, transaction_date=tx, description=desc, status='posted', created_by=su, posted_by=su, posted_at=now) if loan is not None: kw['loan'] = loan je_objects.append(JournalEntry(**kw)) JournalEntry.objects.bulk_create(je_objects, batch_size=500) p(" Inserted " + str(len(je_objects)) + " JournalEntry rows.") # B: re-fetch to get real PKs refs = [s[0] for s in entry_specs] je_map = {je.reference_number: je.id for je in JournalEntry.objects.filter(reference_number__in=refs).only('id','reference_number')} p(" Fetched " + str(len(je_map)) + " JournalEntry PKs.") # C: bulk insert JournalEntryLine using raw je_id (bypasses unsaved-object check) line_objects = [] for ref,tx,desc,loan,debit_acct,credit_acct,amt in entry_specs: je_id = je_map[ref] line_objects.append(JournalEntryLine( journal_entry_id=je_id, account=debit_acct, description='Dr '+debit_acct.name, debit_amount=amt, credit_amount=ZERO, line_number=1)) line_objects.append(JournalEntryLine( journal_entry_id=je_id, account=credit_acct, description='Cr '+credit_acct.name, debit_amount=ZERO, credit_amount=amt, line_number=2)) JournalEntryLine.objects.bulk_create(line_objects, batch_size=1000) p(" Inserted " + str(len(line_objects)) + " JournalEntryLine rows.") # D: re-fetch line PKs, keyed by (je_id, line_number) je_ids = list(je_map.values()) line_map = {} for ln in JournalEntryLine.objects.filter(journal_entry_id__in=je_ids).only('id','journal_entry_id','line_number'): line_map[(ln.journal_entry_id, ln.line_number)] = ln.id # E: bulk insert GeneralLedger using raw FK ids gl_objects = [] for ref,tx,desc,loan,debit_acct,credit_acct,amt in entry_specs: je_id = je_map[ref] dr_id = line_map.get((je_id, 1)) cr_id = line_map.get((je_id, 2)) gl_objects.append(GeneralLedger( account=debit_acct, journal_entry_id=je_id, journal_entry_line_id=dr_id, transaction_date=tx, description='Dr '+debit_acct.name, reference_number=ref, debit_amount=amt, credit_amount=ZERO, balance=ZERO, branch=None, posted_at=now, posted_by=su)) gl_objects.append(GeneralLedger( account=credit_acct, journal_entry_id=je_id, journal_entry_line_id=cr_id, transaction_date=tx, description='Cr '+credit_acct.name, reference_number=ref, debit_amount=ZERO, credit_amount=amt, balance=ZERO, branch=None, posted_at=now, posted_by=su)) GeneralLedger.objects.bulk_create(gl_objects, batch_size=1000) p(" Inserted " + str(len(gl_objects)) + " GeneralLedger rows.") ok("Step 3 done.") else: p(" Nothing new to post.") ok("Step 3 done.") # ── 4. Fix GL running balances (all 5 accounts in one pass each) ────────── inf("Step 4 - Fixing GL running balances...") if not DRY: fixed_total = 0 for code, acc in accts.items(): entries = list(GeneralLedger.objects.filter(account=acc) .order_by('transaction_date','posted_at','id')) if not entries: continue running = ZERO; to_update = [] 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_update.append(gl) if to_update: GeneralLedger.objects.bulk_update(to_update, ['balance'], batch_size=500) p(" Fixed " + str(len(to_update)) + " balance row(s) for " + code) fixed_total += len(to_update) p(" Total fixed: " + str(fixed_total)) ok("Step 4 done.") # ── 5. Clear cache ──────────────────────────────────────────────────────── inf("Step 5 - Clearing cache...") try: from django.core.cache import cache; cache.clear(); p(" Cache cleared.") except Exception as e: p(" Cache warn: "+str(e)) try: n,_ = AccountBalance.objects.all().delete(); p(" Deleted "+str(n)+" AccountBalance row(s).") except Exception: pass ok("Step 5 done.") # ── 6. Restart ──────────────────────────────────────────────────────────── inf("Step 6 - Restarting app...") wsgi = pathlib.Path(PROJECT_ROOT)/'passenger_wsgi.py' if wsgi.exists(): wsgi.touch(); p(" Touched passenger_wsgi.py") else: tmp = pathlib.Path(PROJECT_ROOT)/'tmp'; tmp.mkdir(exist_ok=True) (tmp/'restart.txt').touch(); p(" Touched tmp/restart.txt") ok("Step 6 done.") # ── Summary ─────────────────────────────────────────────────────────────── sep() p("SUMMARY " + datetime.datetime.now().strftime('%H:%M:%S')) sep() from accounting.services.accounting_service import AccountingService as AS svc = AS() p("GL entries: " + str(GeneralLedger.objects.count())) p("Posted JEs: " + str(JournalEntry.objects.filter(status='posted').count())) for code,label in [('1010','Cash'),('1020','Loan Portfolio'),('3010','Share Capital')]: try: acc = Account.objects.get(code=code) bal = svc.calculate_account_balance(acc, today, None) p(" "+code+" "+label.ljust(18)+" KES "+"{:,.2f}".format(bal)) except Exception as exc: p(" "+code+" error: "+str(exc)) 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') p("Total Assets: KES "+"{:,.2f}".format(ta)) p("Total Liabilities: KES "+"{:,.2f}".format(tl)) p("Total Equity: KES "+"{:,.2f}".format(te)) p("SFP Balanced: "+("YES" if abs(ta-tl-te)