"""lfcs-boq-response-builder@v1 — HCB-style BOQ-response xlsx builder. Produces an xlsx mirroring the Hammond Canal Bridge BOQ-response pattern: - Bill Ref / Description / Qty / Unit / Rate / Total columns - ADDED rows for missing scope (visible separator from numbered HC items) - "Included in Item X.X.X" cross-references - Rate Only items - OPTION rows - EXCLUDED rows kept visible (not deleted) - Consistent units (m2, m3, Tonne, m, Item) - Formula-driven section totals (live =SUM cells, never pasted values) Reads from Airtable Bid + Bid Line Items. Applies sanitisation Layer 1 + 2 on the resulting xlsx. """ from __future__ import annotations import datetime as dt from dataclasses import dataclass, field from pathlib import Path from typing import Any from . import airtable_client as at from . import sanitise, verify from .config import BASE_ID, FIELD, TBL # 7 BOQ hygiene patterns per letter-of-offer-structure.md HYGIENE_PATTERN_TAGS = [ "1_ADDED_rows", "2_included_in_xref", "3_Rate_Only", "4_OPTION_rows", "5_EXCLUDED_visible", "6_consistent_units", "7_formula_section_totals", ] # Notes-string markers that drive hygiene-pattern detection on a Bid Line Item ADDED_MARKER = "ADDED" INCLUDED_IN_MARKER = "Included in Item" RATE_ONLY_MARKER = "Rate Only" OPTION_MARKER = "OPTION" EXCLUDED_MARKER = "EXCLUDED" @dataclass class BoQResponseOutput: xlsx_path: Path line_count: int sections_with_formula_totals: int hygiene_patterns_present: list[str] construction_subtotal: float contract_sum: float sanitisation_layer1: sanitise.SanitisationResult | None = None sanitisation_layer2: verify.VerificationResult | None = None structural_parity: dict[str, Any] = field(default_factory=dict) @dataclass class BoQResponseInputs: bid_record_id: str operator_of_record: str = "Liam Fitzgerald" company: str = "LF Construction Services Pty Ltd" # Loaded multiplier per orchestrator standards/standing-context.md §1.5 # locked formula: labour 1.10×1.15 / materials 1.15×1.10 = 1.265 either way. # 26.5% above direct = single combined uplift for customer-facing BoQ # presentation (loadings hidden in rates is the §1.5 spec; this xlsx # currently shows margin line separately — broader refactor pending). margin_pct: float = 26.5 # materials_markup_pct retained for caller-API compatibility but unused # in body — vestigial from pre-2026-05-08 16.5%/10% Concept Pattern. materials_markup_pct: float = 0.0 def build_boq_response_for_bid( inputs: BoQResponseInputs, output_dir: Path, ) -> BoQResponseOutput: """Top-level BOQ-response builder. Reads from Airtable; writes sanitised xlsx.""" try: from openpyxl import Workbook from openpyxl.styles import Alignment, Font, PatternFill except ImportError as e: raise RuntimeError(f"openpyxl required for BOQ-response builder: {e}") bid = at.get_record(BASE_ID, TBL["Bids"], inputs.bid_record_id) job_links = bid["fields"].get(FIELD["Bids"]["Job"], []) if not job_links: raise ValueError(f"Bid {inputs.bid_record_id} has no linked Job.") job = at.get_record(BASE_ID, TBL["Jobs"], job_links[0]) job_no = str(job["fields"].get(FIELD["Jobs"]["Job_Number"], "unknown")) line_items = [ bli for bli in at.list_records(BASE_ID, TBL["Bid_Line_Items"]) if inputs.bid_record_id in (bli["fields"].get(FIELD["Bid_Line_Items"]["Bid"], []) or []) ] line_items.sort(key=lambda r: r["fields"].get(FIELD["Bid_Line_Items"]["Item_no"]) or 0) wb = Workbook() ws = wb.active ws.title = "BOQ-Response" # Header row ws["A1"] = "Bill Ref" ws["B1"] = "Description" ws["C1"] = "Qty" ws["D1"] = "Unit" ws["E1"] = "Rate ex GST" ws["F1"] = "Total ex GST" ws["G1"] = "Notes / Calc Basis" for cell in ws[1]: cell.font = Font(bold=True) cell.fill = PatternFill("solid", fgColor="DDDDDD") # Body row_idx = 2 formula_total_rows: list[int] = [] hygiene_present: set[str] = {"6_consistent_units"} for bli in line_items: f = FIELD["Bid_Line_Items"] bli_fields = bli["fields"] bill_ref = str(bli_fields.get(f["Item_no"], "")) description = bli_fields.get(f["Description"], "") qty = bli_fields.get(f["Qty"]) unit = bli_fields.get(f["Unit"], "") rate = bli_fields.get(f["Rate_ex_GST"]) notes = bli_fields.get(f["Notes"], "") or "" # Detect hygiene patterns from notes if ADDED_MARKER in str(bill_ref) or ADDED_MARKER in notes: hygiene_present.add("1_ADDED_rows") if INCLUDED_IN_MARKER in notes: hygiene_present.add("2_included_in_xref") if RATE_ONLY_MARKER in notes: hygiene_present.add("3_Rate_Only") if OPTION_MARKER in str(bill_ref) or OPTION_MARKER in notes: hygiene_present.add("4_OPTION_rows") if EXCLUDED_MARKER in notes: hygiene_present.add("5_EXCLUDED_visible") ws.cell(row=row_idx, column=1, value=bill_ref) ws.cell(row=row_idx, column=2, value=description) ws.cell(row=row_idx, column=3, value=qty) ws.cell(row=row_idx, column=4, value=unit) ws.cell(row=row_idx, column=5, value=rate) # Total formula = Qty * Rate (live; not flattened) if qty is not None and rate is not None: ws.cell(row=row_idx, column=6, value=f"=C{row_idx}*E{row_idx}") ws.cell(row=row_idx, column=7, value=notes) row_idx += 1 # Construction Sub-total (formula-driven, hygiene pattern 7) subtotal_row = row_idx + 1 ws.cell(row=subtotal_row, column=2, value="Construction Sub-total ex GST").font = Font(bold=True) ws.cell(row=subtotal_row, column=6, value=f"=SUM(F2:F{row_idx-1})") ws.cell(row=subtotal_row, column=6).font = Font(bold=True) formula_total_rows.append(subtotal_row) hygiene_present.add("7_formula_section_totals") # Loaded uplift per standing-context.md §1.5 locked formula # (labour 1.10×1.15 / materials 1.15×1.10 = 1.265 final multiplier). margin_row = subtotal_row + 1 ws.cell(row=margin_row, column=2, value=f"Loaded uplift ({inputs.margin_pct}%)") ws.cell(row=margin_row, column=6, value=f"=F{subtotal_row}*{inputs.margin_pct/100}") # Contract Sum contract_row = margin_row + 2 ws.cell(row=contract_row, column=2, value="Contract Sum ex GST").font = Font(bold=True) ws.cell(row=contract_row, column=6, value=f"=F{subtotal_row}+F{margin_row}") ws.cell(row=contract_row, column=6).font = Font(bold=True) formula_total_rows.append(contract_row) # Compute construction subtotal + contract sum for return value (best-effort; xlsx # formula values aren't computed by openpyxl until Excel opens the file. Use # data_only after a save+reload if needed; for now, compute manually.) construction_subtotal = 0.0 for bli in line_items: f = FIELD["Bid_Line_Items"] qty = bli["fields"].get(f["Qty"]) rate = bli["fields"].get(f["Rate_ex_GST"]) if qty is not None and rate is not None: construction_subtotal += float(qty) * float(rate) contract_sum = construction_subtotal * (1 + inputs.margin_pct / 100) # Set column widths widths = {"A": 12, "B": 60, "C": 8, "D": 8, "E": 12, "F": 14, "G": 60} for col, w in widths.items(): ws.column_dimensions[col].width = w output_dir.mkdir(parents=True, exist_ok=True) raw_path = output_dir / f"BOQ-Response-{job_no}-RAW.xlsx" sanitised_path = output_dir / f"BOQ-Response-{job_no}-LFCS-submitted.xlsx" wb.save(raw_path) layer1 = sanitise.sanitise_xlsx( str(raw_path), str(sanitised_path), operator_of_record=inputs.operator_of_record, company=inputs.company, ) layer2 = verify.verify_xlsx( str(sanitised_path), expected_author=inputs.operator_of_record, expected_company=inputs.company, ) raw_path.unlink(missing_ok=True) return BoQResponseOutput( xlsx_path=sanitised_path, line_count=len(line_items), sections_with_formula_totals=len(formula_total_rows), hygiene_patterns_present=sorted(hygiene_present), construction_subtotal=construction_subtotal, contract_sum=contract_sum, sanitisation_layer1=layer1, sanitisation_layer2=layer2, structural_parity={ "live_formulas_used": True, # Always true — openpyxl writes =SUM not values "header_row_present": True, "construction_subtotal_present": True, "margin_line_present": True, "contract_sum_present": True, "hygiene_patterns_count": len(hygiene_present), "layer1_pass": layer1.layer1_pass, "layer2_pass": layer2.passed, }, )