Skip to content

dleerdefi/claude-code-construction / skills/submittal-log-generator

submittal-log-generator

Verified

Extract all submittal requirements from specification sections and generate a comprehensive submittal register in Excel. Triggers: 'submittal log', 'extract submittals', 'submittal register'. Requires /spec-splitter output.

FileKindSize
SKILL.mdskill md26.7 KB
agents/openai.yamlconfig279 B
references/submittal_log_schema.jsonconfig5.3 KB
scripts/export_submittal_log.pyscript12.9 KB
scripts/shared.pyscript554 B

scripts/export_submittal_log.py

View on GitHub
#!/usr/bin/env python3
"""Submittal Log Excel Export — RIGID script.

Claude Code calls this with structured JSON data. This script does not
make any judgment calls — it formats the data identically every time.

Input:  JSON file with submittal_items[], qa_sections[], and project_info{}
Output: Formatted .xlsx workbook with three sheets:
  - Submittal Log: all items with confidence color-coding
  - Summary: pivot-style counts by section, type, and confidence
  - Extraction QA: per-section extraction quality metrics

Usage:
    construction-python export_submittal_log.py --data input.json --output log.xlsx
"""

import argparse
import json
import sys
from datetime import datetime
from pathlib import Path

from shared import safe_output_path

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter


# ── Color Palette ──────────────────────────────────────────────────

COLORS = {
    "header_bg":     "2C3E50",  # dark slate
    "header_fg":     "FFFFFF",
    "row_even":      "F8F9FA",
    "row_odd":       "FFFFFF",
    "high":          "D4EDDA",  # green
    "medium":        "FFF3CD",  # yellow
    "low":           "FFE0B2",  # orange
    "flagged":       "F8D7DA",  # red
    "high_font":     "155724",
    "medium_font":   "856404",
    "low_font":      "E65100",
    "flagged_font":  "721C24",
    "summary_header": "34495E",
    "summary_accent": "3498DB",
    "border":        "DEE2E6",
}

THIN_BORDER = Border(
    left=Side(style="thin", color=COLORS["border"]),
    right=Side(style="thin", color=COLORS["border"]),
    top=Side(style="thin", color=COLORS["border"]),
    bottom=Side(style="thin", color=COLORS["border"]),
)

HEADER_FONT = Font(name="Arial", bold=True, color=COLORS["header_fg"], size=10)
HEADER_FILL = PatternFill("solid", fgColor=COLORS["header_bg"])
HEADER_ALIGN = Alignment(horizontal="center", vertical="center", wrap_text=True)

BODY_FONT = Font(name="Arial", size=10)
WRAP_ALIGN = Alignment(vertical="top", wrap_text=True)
CENTER_ALIGN = Alignment(horizontal="center", vertical="top")

CONFIDENCE_STYLES = {
    "HIGH":    (COLORS["high"],    COLORS["high_font"]),
    "MEDIUM":  (COLORS["medium"],  COLORS["medium_font"]),
    "LOW":     (COLORS["low"],     COLORS["low_font"]),
    "FLAGGED": (COLORS["flagged"], COLORS["flagged_font"]),
}


# ── Schema ─────────────────────────────────────────────────────────

LOG_COLUMNS = [
    ("Spec Section",          14),
    ("Spec Title",            28),
    ("Submittal No.",         16),
    ("Submittal Type",        18),
    ("Submittal Description", 60),
    ("Article Reference",     14),
    ("Action/Informational",  14),
    ("Confidence",            12),
    ("Flag Reason",           36),
    ("Extraction Method",     14),
    ("Notes",                 36),
]

QA_COLUMNS = [
    ("Spec Section",          14),
    ("Spec Title",            28),
    ("Extraction Method",     16),
    ("Quality Rating",        14),
    ("Failure Modes",         36),
    ("Repair Attempted",      14),
    ("Items Extracted",       14),
    ("Flagged Items",         14),
]


# ── Helpers ────────────────────────────────────────────────────────

def apply_header_row(ws, columns):
    for col_idx, (name, width) in enumerate(columns, 1):
        cell = ws.cell(row=1, column=col_idx, value=name)
        cell.font = HEADER_FONT
        cell.fill = HEADER_FILL
        cell.alignment = HEADER_ALIGN
        cell.border = THIN_BORDER
        ws.column_dimensions[get_column_letter(col_idx)].width = width
    ws.freeze_panes = "A2"
    ws.auto_filter.ref = f"A1:{get_column_letter(len(columns))}1"


def style_body_cell(cell, row_idx, center=False):
    cell.font = BODY_FONT
    cell.border = THIN_BORDER
    cell.alignment = CENTER_ALIGN if center else WRAP_ALIGN
    bg = COLORS["row_even"] if row_idx % 2 == 0 else COLORS["row_odd"]
    cell.fill = PatternFill("solid", fgColor=bg)


def style_confidence_cell(cell, value):
    if value in CONFIDENCE_STYLES:
        bg, fg = CONFIDENCE_STYLES[value]
        cell.fill = PatternFill("solid", fgColor=bg)
        cell.font = Font(name="Arial", size=10, bold=True, color=fg)
        cell.alignment = CENTER_ALIGN


# ── Sheet 1: Submittal Log ────────────────────────────────────────

def write_submittal_log(wb, items):
    ws = wb.active
    ws.title = "Submittal Log"
    ws.sheet_properties.tabColor = "2C3E50"
    apply_header_row(ws, LOG_COLUMNS)

    field_order = [
        "spec_section", "spec_title", "submittal_no", "submittal_type",
        "description", "article_ref", "action_informational",
        "confidence", "flag_reason", "extraction_method", "notes"
    ]
    center_cols = {1, 3, 6, 7, 8, 10}  # 1-indexed columns to center

    for row_idx, item in enumerate(items, 2):
        for col_idx, field in enumerate(field_order, 1):
            value = item.get(field, "")
            cell = ws.cell(row=row_idx, column=col_idx, value=value)
            style_body_cell(cell, row_idx, center=(col_idx in center_cols))

            if field == "confidence":
                style_confidence_cell(cell, str(value).upper())

    ws.print_title_rows = "1:1"
    return ws


# ── Sheet 2: Summary ──────────────────────────────────────────────

def write_summary(wb, items, project_info):
    ws = wb.create_sheet("Summary")
    ws.sheet_properties.tabColor = "3498DB"

    title_font = Font(name="Arial", bold=True, size=14, color=COLORS["summary_header"])
    subtitle_font = Font(name="Arial", bold=True, size=11, color=COLORS["summary_accent"])
    label_font = Font(name="Arial", size=10, bold=True)
    value_font = Font(name="Arial", size=10)

    ws.column_dimensions["A"].width = 4
    ws.column_dimensions["B"].width = 30
    ws.column_dimensions["C"].width = 14
    ws.column_dimensions["D"].width = 4
    ws.column_dimensions["E"].width = 24
    ws.column_dimensions["F"].width = 14

    row = 1
    ws.cell(row=row, column=2, value="Submittal Log Summary").font = title_font
    row += 1
    proj_name = project_info.get("project_name", "")
    if proj_name:
        ws.cell(row=row, column=2, value=proj_name).font = Font(
            name="Arial", size=11, color="666666"
        )
        row += 1
    ws.cell(
        row=row, column=2,
        value=f"Generated: {datetime.now().strftime('%Y-%m-%d %H:%M')}"
    ).font = Font(name="Arial", size=9, color="999999")
    row += 2

    # ── Overview stats
    total = len(items)
    action = sum(1 for i in items if i.get("action_informational", "").lower() == "action")
    info = sum(1 for i in items if i.get("action_informational", "").lower() == "informational")
    flagged = sum(
        1 for i in items if str(i.get("confidence", "")).upper() in ("LOW", "FLAGGED")
    )
    sections = len(set(i.get("spec_section", "") for i in items))

    ws.cell(row=row, column=2, value="Overview").font = subtitle_font
    row += 1
    for label, val in [
        ("Total Submittal Items", total),
        ("Action Submittals", action),
        ("Informational Submittals", info),
        ("Items Needing Review (Low/Flagged)", flagged),
        ("Spec Sections with Submittals", sections),
    ]:
        ws.cell(row=row, column=2, value=label).font = label_font
        ws.cell(row=row, column=3, value=val).font = value_font
        ws.cell(row=row, column=3).alignment = CENTER_ALIGN
        row += 1

    row += 1

    # ── By Confidence
    ws.cell(row=row, column=2, value="By Confidence Level").font = subtitle_font
    row += 1
    for level in ["HIGH", "MEDIUM", "LOW", "FLAGGED"]:
        count = sum(1 for i in items if str(i.get("confidence", "")).upper() == level)
        ws.cell(row=row, column=2, value=level).font = label_font
        c = ws.cell(row=row, column=3, value=count)
        c.font = value_font
        c.alignment = CENTER_ALIGN
        if level in CONFIDENCE_STYLES:
            bg, fg = CONFIDENCE_STYLES[level]
            ws.cell(row=row, column=2).fill = PatternFill("solid", fgColor=bg)
            c.fill = PatternFill("solid", fgColor=bg)
        row += 1

    row += 1

    # ── By Submittal Type (two-column layout with By Section)
    ws.cell(row=row, column=2, value="By Submittal Type").font = subtitle_font
    ws.cell(row=row, column=5, value="By Spec Section").font = subtitle_font
    type_start = row + 1
    section_start = row + 1

    type_counts = {}
    for i in items:
        t = i.get("submittal_type", "Unknown")
        type_counts[t] = type_counts.get(t, 0) + 1
    for t in sorted(type_counts, key=type_counts.get, reverse=True):
        ws.cell(row=type_start, column=2, value=t).font = value_font
        c = ws.cell(row=type_start, column=3, value=type_counts[t])
        c.font = value_font
        c.alignment = CENTER_ALIGN
        type_start += 1

    section_counts = {}
    for i in items:
        key = f"{i.get('spec_section', '')} - {i.get('spec_title', '')}"
        section_counts[key] = section_counts.get(key, 0) + 1
    for s in sorted(section_counts):
        ws.cell(row=section_start, column=5, value=s).font = value_font
        c = ws.cell(row=section_start, column=6, value=section_counts[s])
        c.font = value_font
        c.alignment = CENTER_ALIGN
        section_start += 1

    ws.print_title_rows = "1:1"
    return ws


# ── Sheet 3: Extraction QA ────────────────────────────────────────

def write_qa_sheet(wb, qa_sections):
    ws = wb.create_sheet("Extraction QA")
    ws.sheet_properties.tabColor = "E67E22"
    apply_header_row(ws, QA_COLUMNS)

    field_order = [
        "spec_section", "spec_title", "extraction_method",
        "quality_rating", "failure_modes", "repair_attempted",
        "items_extracted", "flagged_items"
    ]
    center_cols = {1, 3, 4, 6, 7, 8}

    for row_idx, section in enumerate(qa_sections, 2):
        for col_idx, field in enumerate(field_order, 1):
            value = section.get(field, "")
            cell = ws.cell(row=row_idx, column=col_idx, value=value)
            style_body_cell(cell, row_idx, center=(col_idx in center_cols))

            if field == "quality_rating":
                rating = str(value).upper()
                if rating == "GOOD":
                    cell.fill = PatternFill("solid", fgColor=COLORS["high"])
                    cell.font = Font(name="Arial", size=10, bold=True, color=COLORS["high_font"])
                elif rating == "DEGRADED":
                    cell.fill = PatternFill("solid", fgColor=COLORS["medium"])
                    cell.font = Font(name="Arial", size=10, bold=True, color=COLORS["medium_font"])
                elif rating == "POOR":
                    cell.fill = PatternFill("solid", fgColor=COLORS["flagged"])
                    cell.font = Font(name="Arial", size=10, bold=True, color=COLORS["flagged_font"])

    ws.print_title_rows = "1:1"
    return ws


# ── Main ───────────────────────────────────────────────────────────

def export(input_path, output_path):
    with open(input_path, "r") as f:
        data = json.load(f)

    items = data.get("submittal_items", [])
    qa_sections = data.get("qa_sections", [])
    project_info = data.get("project_info", {})

    if not items:
        print("WARNING: No submittal items found in input data.")

    wb = Workbook()
    write_submittal_log(wb, items)
    write_summary(wb, items, project_info)
    write_qa_sheet(wb, qa_sections)

    out = safe_output_path(output_path)
    wb.save(str(out))
    print(f"OK: {out}")
    print(f"  Submittal items: {len(items)}")
    print(f"  QA sections:     {len(qa_sections)}")
    print(f"  Confidence: "
          f"HIGH={sum(1 for i in items if str(i.get('confidence', '')).upper() == 'HIGH')} "
          f"MED={sum(1 for i in items if str(i.get('confidence', '')).upper() == 'MEDIUM')} "
          f"LOW={sum(1 for i in items if str(i.get('confidence', '')).upper() == 'LOW')} "
          f"FLAG={sum(1 for i in items if str(i.get('confidence', '')).upper() == 'FLAGGED')}")


if __name__ == "__main__":
    parser = argparse.ArgumentParser(description="Export submittal log JSON to formatted Excel")
    parser.add_argument("--data", required=True, help="Input JSON file")
    parser.add_argument("--output", "-o", default="Submittal_Log.xlsx", help="Output Excel file")
    args = parser.parse_args()

    if not Path(args.data).exists():
        print(f"ERROR: Input file not found: {args.data}")
        sys.exit(1)

    export(args.data, args.output)
canvas-designanthropics/skillsCreate beautiful visual art in .png and .pdf documents using design philosophy. You should use this skill when the user asks to create a poster, piece of art, design, or other static piece. Create original visual designs, never copying existing artists' work to avoid copyright violations.Stars: 197kUpdated Claude CodeCodexVerifiedInstall
docxanthropics/skillsUse this skill whenever the user wants to create, read, edit, or manipulate Word documents (.docx files) or Word templates (.dotx files). Triggers include: any mention of 'Word doc', 'word document', '.docx', '.dotx', or requests to produce professional documents with formatting like tables of contents, headings, page numbers, or letterheads. Also use when extracting or reorganizing content from .docx or .dotx files, inserting or replacing images in documents, performing find-and-replace in Word files, working with tracked changes or comments, or converting content into a polished Word document. If the user asks for a 'report', 'memo', 'letter', 'template', or similar deliverable as a Word or .docx file, use this skill. Do NOT use for PDFs, spreadsheets, Google Docs, or general coding tasks unrelated to document generation.Stars: 197kUpdated Claude CodeCodexVerifiedInstall
pdfanthropics/skillsUse this skill whenever the user wants to do anything with PDF files. This includes reading or extracting text/tables from PDFs, combining or merging multiple PDFs into one, splitting PDFs apart, rotating pages, adding watermarks, creating new PDFs, filling PDF forms, encrypting/decrypting PDFs, extracting images, and OCR on scanned PDFs to make them searchable. If the user mentions a .pdf file or asks to produce one, use this skill.Stars: 197kUpdated Claude CodeCodexVerifiedInstall
pptxanthropics/skillsUse this skill any time a .pptx or .potx file is involved in any way — as input, output, or both. This includes: creating slide decks, pitch decks, or presentations; reading, parsing, or extracting text from any .pptx or .potx file (even if the extracted content will be used elsewhere, like in an email or summary); editing, modifying, or updating existing presentations; combining or splitting slide files; working with templates (.potx), layouts, speaker notes, or comments. Trigger whenever the user mentions "deck," "slides," "presentation," or references a .pptx or .potx filename, regardless of what they plan to do with the content afterward. If a .pptx or .potx file needs to be opened, created, or touched, use this skill.Stars: 197kUpdated Claude CodeCodexVerifiedInstall