MEGA INFRA
301d0fff44
Reads our Candy PDF (deterministically, no OCR or model needed) and populates the consultant's own workbook using their formulas, column layout and PAYMENT column. Nothing is ever silently overwritten: every disagreement is reported, can be settled inside the app, and regenerated. - extract Candy text PDF -> tree (x-position column detection) - template layout detect, sequence align, write, totals, overrides - validate qty x rate and children-vs-subtotal reconciliation - profiles per-consultant layout profiles, stored on a volume Shipped with a Dockerfile + docker-compose for Coolify.
362 lines
14 KiB
Python
362 lines
14 KiB
Python
"""
|
|
Populate the standard BOQ Excel template (and a flat CSV) from an extracted
|
|
BOQ tree.
|
|
|
|
Two output shapes, because they serve different jobs:
|
|
|
|
populate_template() writes a *tender-ready* workbook. Line AMOUNT cells hold
|
|
live formulas (=Qty*Rate) and group totals are live
|
|
SUMs, so when the estimator types a rate the money
|
|
re-totals itself. This is the file that goes out.
|
|
|
|
to_csv() writes a flat, one-row-per-line audit CSV. This is what
|
|
gets diffed against a competing tender or pushed into
|
|
their estimating system.
|
|
"""
|
|
|
|
from __future__ import annotations
|
|
|
|
import csv
|
|
from typing import Any
|
|
|
|
from openpyxl import Workbook
|
|
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
|
|
from openpyxl.utils import get_column_letter
|
|
|
|
# --------------------------------------------------------------------------
|
|
# presentation
|
|
# --------------------------------------------------------------------------
|
|
NAVY = "1F3864"
|
|
GREY = "F2F2F2"
|
|
BAND = "DDEBF7"
|
|
AMBER = "FFF2CC"
|
|
|
|
HDR_FONT = Font(bold=True, color="FFFFFF", size=10)
|
|
HDR_FILL = PatternFill("solid", fgColor=NAVY)
|
|
SEC_FONT = Font(bold=True, size=11, color=NAVY)
|
|
GRP_FONT = Font(bold=True, size=10)
|
|
BODY_FONT = Font(size=10)
|
|
TOT_FONT = Font(bold=True, size=10)
|
|
TOT_FILL = PatternFill("solid", fgColor=BAND)
|
|
FLAG_FILL = PatternFill("solid", fgColor=AMBER)
|
|
|
|
THIN = Side(style="thin", color="BFBFBF")
|
|
BOX = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
|
|
|
|
MONEY = '#,##0.00'
|
|
QTY_FMT = '#,##0.###'
|
|
|
|
# a unit a human would recognise; used to sanity-check extraction
|
|
KNOWN_UNITS = {
|
|
"m", "m2", "m²", "m3", "m³", "km", "km2", "mm", "cm",
|
|
"no", "no.", "sum", "sum.", "month", "week", "day", "hour",
|
|
"pc", "pc.sum", "ps.sum", "p.sum", "prov sum", "item", "each",
|
|
"t", "kg", "l", "litre", "litres", "%", "?", "ha", "m³ ", "m² ",
|
|
}
|
|
|
|
|
|
def _is_known_unit(u: str) -> bool:
|
|
return (u or "").strip().lower().rstrip(".") in {
|
|
x.strip().lower().rstrip(".") for x in KNOWN_UNITS
|
|
}
|
|
|
|
|
|
# --------------------------------------------------------------------------
|
|
# flatten
|
|
# --------------------------------------------------------------------------
|
|
def flatten(tree: dict) -> list[dict]:
|
|
"""One flat record per row, with its full ancestry for the CSV."""
|
|
rows: list[dict] = []
|
|
|
|
def walk(node: dict, path: list[str], depth: int) -> None:
|
|
kind = node.get("kind")
|
|
if kind == "root":
|
|
for c in node.get("children", []):
|
|
walk(c, path, depth)
|
|
return
|
|
|
|
num = node.get("number", "")
|
|
label = (node.get("title") or node.get("desc") or "").strip()
|
|
|
|
if kind == "line":
|
|
if node.get("qty") is None and node.get("rate") is None:
|
|
return
|
|
rows.append({
|
|
"item": num,
|
|
"description": label,
|
|
"unit": (node.get("unit") or "").strip(),
|
|
"qty": node.get("qty"),
|
|
"rate": node.get("rate"),
|
|
"amount": node.get("amount"),
|
|
"rate_only": bool(node.get("rate_only")),
|
|
"unit_recognised": _is_known_unit(node.get("unit") or ""),
|
|
"level": depth,
|
|
"path": " > ".join(path + [f"{num} {label}".strip()]),
|
|
"page": node.get("page", 0),
|
|
})
|
|
else:
|
|
p = path + [f"{num} {label}".strip()]
|
|
for c in node.get("children", []):
|
|
walk(c, p, depth + 1)
|
|
|
|
walk(tree, [], 1)
|
|
return rows
|
|
|
|
|
|
# --------------------------------------------------------------------------
|
|
# tender workbook
|
|
# --------------------------------------------------------------------------
|
|
HEADERS = ["ITEM", "DESCRIPTION", "UNIT", "QTY", "RATE", "AMOUNT"]
|
|
|
|
|
|
def populate_template(tree: dict, out_path: str, *,
|
|
title: str = "BILL OF QUANTITIES",
|
|
contract: str = "",
|
|
vat_pct: float = 15.0) -> dict:
|
|
"""Write a tender-ready workbook with live formulas.
|
|
|
|
AMOUNT is a formula, not a value, so the estimator can overwrite a rate and
|
|
watch every total in the workbook re-compute. Printed subtotals from the
|
|
source are written alongside as a static "as-printed" figure so any
|
|
divergence stays visible instead of being silently normalised away.
|
|
"""
|
|
wb = Workbook()
|
|
ws = wb.active
|
|
ws.title = "BOQ"
|
|
|
|
r = 1
|
|
ws.cell(r, 1, title).font = Font(bold=True, size=14, color=NAVY)
|
|
r += 1
|
|
if contract:
|
|
ws.cell(r, 1, contract).font = Font(size=10, italic=True)
|
|
r += 1
|
|
r += 1
|
|
|
|
header_row = r
|
|
for i, h in enumerate(HEADERS, start=1):
|
|
c = ws.cell(header_row, i, h)
|
|
c.font, c.fill, c.border = HDR_FONT, HDR_FILL, BOX
|
|
c.alignment = Alignment(horizontal="center", vertical="center")
|
|
r += 1
|
|
|
|
stats = {"lines": 0, "groups": 0, "rate_only": 0, "unknown_units": 0}
|
|
subtotal_cells: list[tuple[str, int, str, int]] = [] # label,row,kind,depth
|
|
group_rows: list[tuple[str, str, float, int]] = [] # label, number, printed, row
|
|
|
|
def emit_line(node: dict) -> None:
|
|
nonlocal r
|
|
num = node.get("number", "")
|
|
desc = (node.get("desc") or "").strip()
|
|
unit = (node.get("unit") or "").strip()
|
|
qty, rate = node.get("qty"), node.get("rate")
|
|
amt = node.get("amount")
|
|
rate_only = bool(node.get("rate_only"))
|
|
|
|
ws.cell(r, 1, num).font = BODY_FONT
|
|
ws.cell(r, 2, desc).font = BODY_FONT
|
|
ws.cell(r, 3, unit).font = BODY_FONT
|
|
ws.cell(r, 4, qty).number_format = QTY_FMT
|
|
if not rate_only and rate is not None:
|
|
ws.cell(r, 5, rate).number_format = MONEY
|
|
# live formula - the whole point of the deliverable
|
|
ws.cell(r, 6, f"=IF(D{r}=\"\",\"\",D{r}*E{r})").number_format = MONEY
|
|
elif rate_only:
|
|
ws.cell(r, 5, rate).number_format = MONEY
|
|
ws.cell(r, 6, "Rate only")
|
|
elif amt is not None:
|
|
ws.cell(r, 6, amt).number_format = MONEY
|
|
|
|
for col in range(1, 7):
|
|
ws.cell(r, col).border = BOX
|
|
if not _is_known_unit(unit):
|
|
ws.cell(r, 3).fill = FLAG_FILL
|
|
stats["unknown_units"] += 1
|
|
if rate_only:
|
|
stats["rate_only"] += 1
|
|
stats["lines"] += 1
|
|
r += 1
|
|
|
|
def emit_bare(desc: str) -> None:
|
|
"""A description with no price (placeholder or INCL note)."""
|
|
nonlocal r
|
|
if not desc:
|
|
return
|
|
ws.cell(r, 2, desc).font = Font(size=10, color="595959")
|
|
for col in range(1, 7):
|
|
ws.cell(r, col).border = BOX
|
|
r += 1
|
|
|
|
def emit_line(node: dict) -> str:
|
|
"""Write one priced line; return its AMOUNT cell reference."""
|
|
nonlocal r
|
|
num = node.get("number", "")
|
|
desc = (node.get("desc") or "").strip()
|
|
unit = (node.get("unit") or "").strip()
|
|
qty, rate = node.get("qty"), node.get("rate")
|
|
amt = node.get("amount")
|
|
rate_only = bool(node.get("rate_only"))
|
|
|
|
ws.cell(r, 1, num).font = BODY_FONT
|
|
ws.cell(r, 2, desc).font = BODY_FONT
|
|
ws.cell(r, 3, unit).font = BODY_FONT
|
|
if qty is not None:
|
|
ws.cell(r, 4, qty).number_format = QTY_FMT
|
|
if not rate_only and rate is not None:
|
|
ws.cell(r, 5, rate).number_format = MONEY
|
|
# live formula - the whole point of the deliverable
|
|
ws.cell(r, 6, f'=IF(D{r}="","",D{r}*E{r})').number_format = MONEY
|
|
elif rate_only:
|
|
if rate is not None:
|
|
ws.cell(r, 5, rate).number_format = MONEY
|
|
ws.cell(r, 6, "Rate only").font = Font(size=9, italic=True,
|
|
color="808080")
|
|
elif amt is not None:
|
|
ws.cell(r, 6, amt).number_format = MONEY
|
|
|
|
for col in range(1, 7):
|
|
ws.cell(r, col).border = BOX
|
|
if not _is_known_unit(unit):
|
|
ws.cell(r, 3).fill = FLAG_FILL
|
|
stats["unknown_units"] += 1
|
|
if rate_only:
|
|
stats["rate_only"] += 1
|
|
stats["lines"] += 1
|
|
r += 1
|
|
return f"F{r - 1}"
|
|
|
|
def depth_of(node: dict) -> int:
|
|
n = node.get("number") or ""
|
|
return n.count(".") + 1 if n else 1
|
|
|
|
def walk(node: dict) -> list[str]:
|
|
"""Emit a node depth-first; return its AMOUNT cell reference(s)."""
|
|
nonlocal r
|
|
kind = node.get("kind")
|
|
num = node.get("number", "")
|
|
title = (node.get("title") or "").strip()
|
|
printed = node.get("subtotal")
|
|
|
|
if kind not in ("section", "schedule", "group", "item"):
|
|
cells: list[str] = []
|
|
for c in node.get("children", []):
|
|
cells.extend(walk(c))
|
|
return cells
|
|
|
|
hdr = r
|
|
c = ws.cell(hdr, 1, f"{num} {title}".strip())
|
|
c.font = SEC_FONT if kind in ("section", "schedule") else GRP_FONT
|
|
if kind in ("section", "schedule"):
|
|
for col in range(1, 7):
|
|
ws.cell(hdr, col).fill = PatternFill("solid", fgColor=GREY)
|
|
r += 1
|
|
|
|
child_cells: list[str] = []
|
|
for ch in node.get("children", []):
|
|
ck = ch.get("kind")
|
|
if ck == "line":
|
|
if ch.get("qty") is None and ch.get("rate") is None:
|
|
emit_bare((ch.get("desc") or "").strip())
|
|
continue
|
|
child_cells.append(emit_line(ch))
|
|
else:
|
|
child_cells.extend(walk(ch))
|
|
|
|
if child_cells:
|
|
# SUM(), not a "+" chain: "Rate only" cells hold text, and Excel
|
|
# raises #VALUE! when text appears in an addition chain. SUM()
|
|
# ignores text arguments, so rate-only lines drop out cleanly.
|
|
ws.cell(hdr, 6, f"=SUM({','.join(child_cells)})").number_format = MONEY
|
|
elif printed is not None:
|
|
ws.cell(hdr, 6, printed).number_format = MONEY
|
|
if printed is not None:
|
|
# the figure the source actually printed, kept beside the live
|
|
# formula so a divergence stays visible instead of being hidden
|
|
ws.cell(hdr, 7, printed).number_format = MONEY
|
|
ws.cell(hdr, 7).font = Font(size=9, color="808080")
|
|
for col in range(1, 8):
|
|
ws.cell(hdr, col).border = BOX
|
|
ws.cell(hdr, 6).font = TOT_FONT
|
|
stats["groups"] += 1
|
|
label = f"{num} {title}".strip()
|
|
group_rows.append((label, num,
|
|
float(printed) if printed is not None else None, hdr))
|
|
subtotal_cells.append((label, hdr, kind, depth_of(node)))
|
|
return [f"F{hdr}"]
|
|
|
|
for child in tree.get("children", []):
|
|
walk(child)
|
|
|
|
# ---- combined summary ---------------------------------------------
|
|
r += 1
|
|
ws.cell(r, 1, "COMBINED SUMMARY").font = Font(bold=True, size=12, color=NAVY)
|
|
r += 1
|
|
for i, h in enumerate(["", "SCHEDULE / SECTION", "AMOUNT", "AS PRINTED"],
|
|
start=1):
|
|
c = ws.cell(r, i, h)
|
|
c.font, c.fill, c.border = HDR_FONT, HDR_FILL, BOX
|
|
r += 1
|
|
# every schedule and every section heads the summary; the numbered groups
|
|
# inside them are listed indented beneath, exactly like a Candy summary
|
|
# Only schedules and depth-1 groups/sections roll into SUB TOTAL. The
|
|
# groups nested inside them are listed indented for reference but must NOT
|
|
# be summed again, or every level of the tree is counted several times.
|
|
counted: list[int] = []
|
|
first_sum = r
|
|
for label, row, kind, d in subtotal_cells:
|
|
# Counted: schedules, and depth-1 groups that sit at the root
|
|
# ("1 SCHEDULE A : GENERAL"). Sections are depth-1 too but they live
|
|
# INSIDE a schedule, which is already counted - including them would
|
|
# add the whole tree to itself.
|
|
top = (kind == "schedule") or (d <= 1 and kind != "section")
|
|
if not top and not (kind in ("group", "section") and d <= 2):
|
|
continue
|
|
ws.cell(r, 2, f"{'' if top else ' '}{label}")
|
|
ws.cell(r, 3, f"=F{row}").number_format = MONEY
|
|
ws.cell(r, 2).font = GRP_FONT if top else Font(size=9, color="595959")
|
|
if top:
|
|
counted.append(r)
|
|
r += 1
|
|
last_sum = r - 1
|
|
ws.cell(r, 2, "SUB TOTAL").font = TOT_FONT
|
|
ws.cell(r, 3, "=SUM(" + ",".join(f"C{x}" for x in counted) + ")").number_format = MONEY
|
|
ws.cell(r, 3).font = TOT_FONT
|
|
ws.cell(r, 3).fill = TOT_FILL
|
|
sub_row = r
|
|
r += 1
|
|
ws.cell(r, 2, f"ADD {vat_pct:g} % VAT").font = TOT_FONT
|
|
ws.cell(r, 3, f"=C{sub_row}*{vat_pct / 100}").number_format = MONEY
|
|
r += 1
|
|
ws.cell(r, 2, "TOTAL CARRIED FORWARD TO FORM OF OFFER").font = TOT_FONT
|
|
ws.cell(r, 3, f"=C{sub_row}+C{r - 1}").number_format = MONEY
|
|
ws.cell(r, 3).font = Font(bold=True, size=11)
|
|
ws.cell(r, 3).fill = TOT_FILL
|
|
r += 2
|
|
|
|
ws.cell(r, 1, "Column G holds the subtotal AS PRINTED in the source PDF, for "
|
|
"comparison against the live formula in column F.").font = \
|
|
Font(size=9, italic=True, color="808080")
|
|
|
|
widths = {"A": 10, "B": 66, "C": 10, "D": 12, "E": 14, "F": 16, "G": 16}
|
|
for col, w in widths.items():
|
|
ws.column_dimensions[col].width = w
|
|
ws.freeze_panes = ws.cell(header_row + 1, 1)
|
|
|
|
wb.save(out_path)
|
|
return stats
|
|
|
|
|
|
# --------------------------------------------------------------------------
|
|
# flat CSV
|
|
# --------------------------------------------------------------------------
|
|
CSV_COLS = ["item", "description", "unit", "qty", "rate", "amount",
|
|
"rate_only", "unit_recognised", "level", "page", "path"]
|
|
|
|
|
|
def to_csv(tree: dict, out_path: str) -> int:
|
|
rows = flatten(tree)
|
|
with open(out_path, "w", newline="", encoding="utf-8-sig") as fh:
|
|
w = csv.DictWriter(fh, fieldnames=CSV_COLS, extrasaction="ignore")
|
|
w.writeheader()
|
|
for row in rows:
|
|
w.writerow(row)
|
|
return len(rows)
|