"""Seed the accounting module from NGO_Accounting_With_Ledger.xlsx. Imports the source tabs: Chart of Accounts, Funds & Grants, Transactions, and Journal Entries (grouped by Journal No. into JournalEntry + JournalLine). Derived tabs (GL, Trial Balance, Budget vs Actual, Dashboard, Journal Control) are computed on read and are NOT imported. Usage: python manage.py seed_accounting [--file path/to.xlsx] [--clear] """ from pathlib import Path from django.core.management.base import BaseCommand, CommandError from django.db import transaction from apps.accounting.models import ( Account, Fund, Transaction, JournalEntry, JournalLine, ) from apps.companies.models import Organization DEFAULT_XLSX = Path(__file__).resolve().parents[5] / "NGO_Accounting_With_Ledger.xlsx" def _d(v): """Coerce a cell to a date or None.""" return v.date() if hasattr(v, "date") else v def _num(v): return v if v is not None else 0 def _rows_after(ws, first_header): """Yield data rows after the row whose first cell == first_header.""" rows = list(ws.iter_rows(values_only=True)) start = None for i, r in enumerate(rows): vals = [c for c in r if c not in (None, "")] if vals and vals[0] == first_header: start = i + 1 break if start is None: return for r in rows[start:]: if all(c in (None, "") for c in r): continue yield r class Command(BaseCommand): help = "Seed accounting models from the NGO accounting xlsx workbook." def add_arguments(self, parser): parser.add_argument("--file", default=str(DEFAULT_XLSX)) parser.add_argument("--clear", action="store_true", help="Delete existing accounting data first.") parser.add_argument("--org", help="Organization slug to seed. Defaults to the first organization.") def handle(self, *args, **opts): import openpyxl org = ( Organization.objects.filter(slug=opts["org"]).first() if opts["org"] else Organization.objects.first() ) if org is None: raise CommandError("No organization found. Create one first.") path = Path(opts["file"]) if not path.exists(): self.stderr.write(self.style.ERROR(f"Workbook not found: {path}")) return wb = openpyxl.load_workbook(path, data_only=True) with transaction.atomic(): if opts["clear"]: JournalLine.objects.filter(journal__organization=org).delete() JournalEntry.objects.filter(organization=org).delete() Transaction.objects.filter(organization=org).delete() Fund.objects.filter(organization=org).delete() Account.objects.filter(organization=org).delete() self.stdout.write(f"Cleared existing accounting data for {org.name}.") accounts = self._seed_accounts(wb, org) funds = self._seed_funds(wb, org) self._seed_transactions(wb, accounts, funds, org) self._seed_journals(wb, accounts, funds, org) self.stdout.write(self.style.SUCCESS( f"Done for {org.name}. Accounts={Account.objects.filter(organization=org).count()} " f"Funds={Fund.objects.filter(organization=org).count()} " f"Transactions={Transaction.objects.filter(organization=org).count()} " f"Journals={JournalEntry.objects.filter(organization=org).count()} " f"Lines={JournalLine.objects.filter(journal__organization=org).count()}" )) def _seed_accounts(self, wb, org): ws = wb["Chart of Accounts"] by_code = {} for code, name, type_, normal, notes in (r[:5] for r in _rows_after(ws, "Account Code")): if code is None: continue name_l = (name or "").lower() is_cash = any(w in name_l for w in ("cash", "bank", "kas")) # Equipment/fixed assets are investing; everything else operating. cf = "investing" if any(w in name_l for w in ("equipment", "asset", "aset tetap")) else "operating" acc, _ = Account.objects.update_or_create( organization=org, code=str(code), defaults={"name": name or "", "type": type_ or "Asset", "normal_balance": normal or "Debit", "notes": notes or "", "is_cash": is_cash, "cash_flow_category": cf}, ) by_code[str(code)] = acc self.stdout.write(f"Accounts: {len(by_code)}") return by_code def _seed_funds(self, wb, org): ws = wb["Funds & Grants"] by_id = {} for r in _rows_after(ws, "Fund ID"): fid, donor, name, ftype, project, start, end, award, received = r[:9] if fid is None: continue fund, _ = Fund.objects.update_or_create( organization=org, fund_id=str(fid), defaults={ "donor_name": donor or "", "name": name or "", "fund_type": ftype or "Restricted", "project_code": project or "", "start_date": _d(start), "end_date": _d(end), "award_amount": _num(award), "received_amount": _num(received), }, ) by_id[str(fid)] = fund self.stdout.write(f"Funds: {len(by_id)}") return by_id def _seed_transactions(self, wb, accounts, funds, org): ws = wb["Transactions"] n = 0 for r in _rows_after(ws, "Transaction ID"): (tid, date, desc, ttype, fund_id, donor, project, activity, bline, acode, counterparty, currency, orig, rate, debit, credit, net, status) = r[:18] if tid is None: continue Transaction.objects.update_or_create( organization=org, transaction_id=str(tid), defaults={ "date": _d(date), "description": desc or "", "transaction_type": ttype or "Expense", "fund": funds.get(str(fund_id)) if fund_id else None, "donor_name": donor or "", "project_code": project or "", "activity": activity or "", "budget_line": bline or "", "account": accounts.get(str(acode)) if acode else None, "counterparty": counterparty or "", "currency": currency or "IDR", "original_amount": _num(orig), "exchange_rate": _num(rate) or 1, "debit_idr": _num(debit), "credit_idr": _num(credit), "net_expense_idr": _num(net), "status": status or "Draft", }, ) n += 1 self.stdout.write(f"Transactions: {n}") def _seed_journals(self, wb, accounts, funds, org): ws = wb["Journal Entries"] entries = {} # journal_no -> JournalEntry nlines = 0 for r in _rows_after(ws, "Journal No."): (jno, date, ref, desc, acode, aname, debit, credit, fund_id, donor, project, activity, bline, status) = r[:14] if jno is None: continue jno = str(jno) entry = entries.get(jno) if entry is None: entry, _ = JournalEntry.objects.update_or_create( organization=org, journal_no=jno, defaults={ "date": _d(date), "reference": ref or "", "description": desc or "", "fund": funds.get(str(fund_id)) if fund_id else None, "donor_name": donor or "", "project_code": project or "", "activity": activity or "", "budget_line": bline or "", "status": status or "Draft", }, ) entry.lines.all().delete() entries[jno] = entry acc = accounts.get(str(acode)) if acode else None if acc is None: continue JournalLine.objects.create( journal=entry, account=acc, description=desc or "", debit_idr=_num(debit), credit_idr=_num(credit), ) nlines += 1 self.stdout.write(f"Journals: {len(entries)} Lines: {nlines}")