#!/usr/bin/env python """Close remaining net income to Retained Earnings and rebalance SFP.""" 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) ZERO = Decimal('0.00') from accounting.models import Account, GeneralLedger, JournalEntry, JournalEntryLine, AccountBalance from django.db import transaction as TX from django.utils import timezone as tz from django.contrib.auth import get_user_model User = get_user_model() su = User.objects.filter(is_superuser=True).order_by('date_joined').first() now = tz.now() today = datetime.date.today() # --- Calculate true net income from current GL balances --- total_income = ZERO total_expense = ZERO income_lines = [] # (account, balance) for income accounts with balance > 0 for acc in Account.objects.filter(account_type='income', is_active=True): last = GeneralLedger.objects.filter(account=acc).order_by('-transaction_date','-id').first() if last and last.balance > ZERO: total_income += last.balance income_lines.append((acc, last.balance)) for acc in Account.objects.filter(account_type='expense', is_active=True): last = GeneralLedger.objects.filter(account=acc).order_by('-transaction_date','-id').first() if last and last.balance > ZERO: total_expense += last.balance net_income = total_income - total_expense p("Total income (from GL balances): " + str(total_income)) p("Total expenses: " + str(total_expense)) p("Net income to close: " + str(net_income)) # --- Check existing closing entries already posted --- closed_so_far = ZERO for je in JournalEntry.objects.filter(reference_number__startswith='CLOSE-NET-INCOME-'): for ln in JournalEntryLine.objects.filter(journal_entry=je, account__account_type='equity'): closed_so_far += ln.credit_amount - ln.debit_amount p("Already closed to RE: " + str(closed_so_far)) remaining = net_income - closed_so_far p("Remaining to close: " + str(remaining)) if abs(remaining) < Decimal('0.01'): p("Nothing to do - already balanced.") else: re_acc = Account.objects.get(code='3020') ref = 'CLOSE-NET-INCOME-' + str(today.year) + '-ADJ' # Delete any previous ADJ entry to repost cleanly old = JournalEntry.objects.filter(reference_number=ref) if old.exists(): ids = list(old.values_list('id', flat=True)) GeneralLedger.objects.filter(journal_entry_id__in=ids).delete() JournalEntryLine.objects.filter(journal_entry_id__in=ids).delete() old.delete() p("Deleted previous ADJ closing entry.") with TX.atomic(): je_close = JournalEntry.objects.create( reference_number=ref, transaction_date=today, description='Income close to RE - adjustment', status='posted', created_by=su, posted_by=su, posted_at=now) line_num = 1 for acc, bal in income_lines: # Only close the portion not yet closed # Simple: close full balance of each income account # (previous close already debited 42,051 from 401002) already = ZERO for old_je in JournalEntry.objects.filter(reference_number__startswith='CLOSE-NET-INCOME-').exclude(reference_number=ref): old_ln = JournalEntryLine.objects.filter(journal_entry=old_je, account=acc).first() if old_ln: already += old_ln.debit_amount to_close = bal - already if to_close <= ZERO: continue JournalEntryLine.objects.create( journal_entry=je_close, account=acc, description='Close income to RE', debit_amount=to_close, credit_amount=ZERO, line_number=line_num) line_num += 1 # Credit RE with net remaining JournalEntryLine.objects.create( journal_entry=je_close, account=re_acc, description='Net income to retained earnings', debit_amount=ZERO, credit_amount=remaining, line_number=line_num) # GL rows for each line for ln in JournalEntryLine.objects.filter(journal_entry=je_close): GeneralLedger.objects.create( account=ln.account, journal_entry=je_close, journal_entry_line=ln, transaction_date=today, description=ln.description, reference_number=ref, debit_amount=ln.debit_amount, credit_amount=ln.credit_amount, balance=ZERO, branch=None, posted_at=now, posted_by=su) p("Posted closing entry ref=" + ref + " KES " + str(remaining)) # Recalculate balances for affected accounts affected = set([a for a,b in income_lines] + [re_acc]) for acc in affected: entries = list(GeneralLedger.objects.filter(account=acc) .order_by('transaction_date','posted_at','id')) 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) p("Fixed " + str(len(to_upd)) + " rows for " + acc.code) # Clear cache 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 # Touch restart wsgi = pathlib.Path(PROJECT_ROOT) / 'passenger_wsgi.py' if wsgi.exists(): wsgi.touch(); p("Touched passenger_wsgi.py") # Final check from accounting.services.accounting_service import AccountingService svc = AccountingService() def _s2(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=_s2('asset'); tl=_s2('liability'); te=_s2('equity') p("") p("Total Assets: KES " + "{:>14,.2f}".format(ta)) p("Total Liabilities: KES " + "{:>14,.2f}".format(tl)) p("Total Equity: KES " + "{:>14,.2f}".format(te)) diff = ta-tl-te p("SFP Balanced: " + ("YES" if abs(diff)