#!/usr/bin/env python3 """Build print-friendly Motor Line container manifest from SQLite DB. DB is the single source of truth. Jinja2 template renders the HTML. Usage: python3 build_manifest.py # rebuild from DB python3 build_manifest.py --load-items # one-time: load container items from xlsx into DB """ import os import sys import base64 import sqlite3 from collections import OrderedDict from datetime import datetime import jinja2 BASE = os.path.dirname(os.path.abspath(__file__)) DB_PATH = os.path.join(BASE, "tmed2_install.db") TEMPLATE = os.path.join(BASE, "templates", "manifest.html") OUT = os.path.join(BASE, "container_manifest.html") LOGO = "/home/borbolla/clawd/assets/branding/logo.png" PKG = os.path.join(BASE, "package_lists") # Upcoming schedule (update as containers arrive) UPCOMING = [ ("Mon May 26", "5 Flat Racks (DK2-01, DK2-02, HWIA-37, HWIA-38, HWIA-43)"), ("Tue May 27", "5 Full containers (HWIA)"), ] def ensure_items_table(conn): """Create container_items table if it doesn't exist.""" conn.execute(""" CREATE TABLE IF NOT EXISTS container_items ( id INTEGER PRIMARY KEY, container_id INTEGER NOT NULL REFERENCES containers(id), facility_name TEXT NOT NULL, net_kg REAL DEFAULT 0, gross_kg REAL DEFAULT 0, length_mm REAL DEFAULT 0, width_mm REAL DEFAULT 0, height_mm REAL DEFAULT 0 ) """) conn.execute("CREATE INDEX IF NOT EXISTS idx_citems_ct ON container_items(container_id)") def load_items_from_xlsx(conn): """One-time: parse xlsx packing details and load into container_items table.""" import openpyxl ensure_items_table(conn) conn.execute("DELETE FROM container_items") PREFIXES = { "DKTIND-2604-01.xlsx": "DK", "DKTIND-2604-02.xlsx": "DK2", "HWIA-EM260408-01.xlsx": "HWIA", "HWIA-EM260408-02.xlsx": "HWIA", "HWIA-EM260413-03.xlsx": "HWIA", "HWIA-EM260413-04.xlsx": "HWIA", } # Build container_num → id lookup ct_lookup = {} for row in conn.execute("SELECT id, supplier_prefix, container_num FROM containers"): ct_lookup[(row[1], row[2])] = row[0] total = 0 for fname in sorted(os.listdir(PKG)): if not fname.endswith(".xlsx"): continue prefix = PREFIXES.get(fname) if not prefix: continue wb = openpyxl.load_workbook(os.path.join(PKG, fname), data_only=True) ws = wb["PACKING DETAIL"] # Auto-detect weight columns net_col = gross_col = None for col in range(1, ws.max_column + 1): v = str(ws.cell(2, col).value or "").lower() if "net" in v and "weight" in v: net_col = col if "gross" in v and "weight" in v: gross_col = col # L, W, H columns l_col = w_col = h_col = None if gross_col: for col in range(gross_col + 1, min(gross_col + 5, ws.max_column + 1)): hdr = str(ws.cell(2, col).value or "") + str(ws.cell(3, col).value or "") if not l_col and ("L" in hdr or "Gross Size" in hdr): l_col = col elif l_col and not w_col: w_col = col elif w_col and not h_col: h_col = col current_seq = None for r in range(4, ws.max_row + 1): seq_val = ws.cell(r, 1).value if seq_val and str(seq_val).strip() and str(seq_val).strip() != "END": current_seq = str(seq_val).strip() if str(ws.cell(r, 1).value or "").strip() == "END": break facility = str(ws.cell(r, 4).value or "").strip() if not facility: continue net = gross = 0 try: net = float(ws.cell(r, net_col).value or 0) if net_col else 0 except (ValueError, TypeError): pass try: gross = float(ws.cell(r, gross_col).value or 0) if gross_col else 0 except (ValueError, TypeError): pass if net == 0 and gross == 0: continue l_v = w_v = h_v = 0 try: l_v = float(ws.cell(r, l_col).value or 0) if l_col else 0 except (ValueError, TypeError): pass try: w_v = float(ws.cell(r, w_col).value or 0) if w_col else 0 except (ValueError, TypeError): pass try: h_v = float(ws.cell(r, h_col).value or 0) if h_col else 0 except (ValueError, TypeError): pass ct_id = ct_lookup.get((prefix, current_seq)) if not ct_id: continue conn.execute(""" INSERT INTO container_items (container_id, facility_name, net_kg, gross_kg, length_mm, width_mm, height_mm) VALUES (?, ?, ?, ?, ?, ?, ?) """, (ct_id, facility.replace("\n", " / ")[:60], net, gross, l_v, w_v, h_v)) total += 1 wb.close() conn.commit() print(f"Loaded {total} container items into DB") def build_manifest(): """Build manifest HTML from DB using Jinja2 template.""" conn = sqlite3.connect(DB_PATH) conn.row_factory = sqlite3.Row ensure_items_table(conn) # Check if items are loaded item_count = conn.execute("SELECT count(*) FROM container_items").fetchone()[0] if item_count == 0: print("No container items in DB. Run with --load-items first.") sys.exit(1) # Load all containers with their items containers = [] for ct in conn.execute(""" SELECT id, supplier_prefix, container_num, container_type, vessel, sailing_date, est_arrival, forklift_class, unload_day, unloaded, unload_date, real_container_no, seal_no FROM containers ORDER BY est_arrival, supplier_prefix, CAST(container_num AS INTEGER) """): items = [] for item in conn.execute(""" SELECT facility_name, net_kg, gross_kg, length_mm, width_mm, height_mm FROM container_items WHERE container_id = ? ORDER BY id """, (ct["id"],)): items.append(dict(item)) if not items: continue ct_dict = dict(ct) label = f"{ct['supplier_prefix']}-{int(ct['container_num']):02d}" if ct["container_num"].isdigit() else f"{ct['supplier_prefix']}-{ct['container_num']}" gross_t = sum(i["gross_kg"] for i in items) / 1000 is_fr = "FR" in (ct["container_type"] or "") # Compute proportional widths for item boxes total_l = sum(i["length_mm"] for i in items) n = len(items) for item in items: if total_l and item["length_mm"]: item["pct"] = max((item["length_mm"] / total_l) * 100, 4) else: item["pct"] = max(100 / n, 4) item["pct"] = round(item["pct"], 1) l, w, h = item["length_mm"], item["width_mm"], item["height_mm"] item["dims_str"] = f"{l/1000:.1f}x{w/1000:.1f}x{h/1000:.1f}m" if l else "" wt = item["gross_kg"] item["wt_str"] = f"{wt/1000:.1f}T" if wt >= 1000 else f"{wt:.0f}kg" item["name"] = item["facility_name"] containers.append({ "label": label, "real_no": ct["real_container_no"] or "", "container_type": ct["container_type"] or "40HC", "vessel": ct["vessel"] or "", "sailing": ct["sailing_date"] or "", "arrival": ct["est_arrival"] or "", "is_fr": is_fr, "gross_t": gross_t, "crates": items, "unloaded": bool(ct["unloaded"]), "unload_date": ct["unload_date"] or "", }) conn.close() # Group by arrival groups = OrderedDict() for ct in containers: groups.setdefault(ct["arrival"], []).append(ct) # Stats stats = { "total": len(containers), "items": sum(len(ct["crates"]) for ct in containers), "gross_t": round(sum(ct["gross_t"] for ct in containers)), "fr": sum(1 for ct in containers if ct["is_fr"]), "received": sum(1 for ct in containers if ct["unloaded"]), "updated": datetime.now().strftime("%Y-%m-%d %H:%M"), } # Logo with open(LOGO, "rb") as f: logo_b64 = base64.b64encode(f.read()).decode() # Render env = jinja2.Environment( loader=jinja2.FileSystemLoader(os.path.dirname(TEMPLATE)), autoescape=False, ) template = env.get_template(os.path.basename(TEMPLATE)) html = template.render( stats=stats, groups=list(groups.items()), upcoming=UPCOMING, logo_b64=logo_b64, ) with open(OUT, "w") as f: f.write(html) print(f"Written: {OUT}") print(f"{stats['total']} containers, {stats['items']} items, {stats['gross_t']}T, {stats['fr']} FR, {stats['received']} received") if __name__ == "__main__": if "--load-items" in sys.argv: conn = sqlite3.connect(DB_PATH) load_items_from_xlsx(conn) conn.close() print("Items loaded. Now run without --load-items to build manifest.") else: build_manifest()