13b56fd1b0
LibreOffice Calc extension for Silo PLM integration. Uses shared silo-client package (submodule) for API communication. Changes from monorepo version: - SiloClient class removed from client.py, replaced with CalcSiloSettings adapter + factory function wrapping silo_client.SiloClient - silo_calc_component.py adds silo-client to sys.path - Makefile build-oxt copies silo_client into .oxt for self-contained packaging - All other modules unchanged
179 lines
5.3 KiB
Python
179 lines
5.3 KiB
Python
"""BOM and Items sheet column layouts, constants, and detection helpers.
|
|
|
|
This module defines the column structure that matches the engineer's working
|
|
BOM format. Hidden property columns and sync-tracking columns are appended
|
|
to the right.
|
|
"""
|
|
|
|
from typing import Dict, List, Optional, Tuple
|
|
|
|
# ---------------------------------------------------------------------------
|
|
# Column indices -- BOM sheet
|
|
# ---------------------------------------------------------------------------
|
|
|
|
# Visible core columns (always shown)
|
|
BOM_VISIBLE_HEADERS: List[str] = [
|
|
"Item", # A - assembly label / section header
|
|
"Level", # B - depth in expanded BOM
|
|
"Source", # C - sourcing_type (M/P)
|
|
"PN", # D - part_number
|
|
"Description", # E - item description
|
|
"Seller Description", # F - metadata.seller_description
|
|
"Unit Cost", # G - standard_cost / metadata.unit_cost
|
|
"QTY", # H - quantity on relationship
|
|
"Ext Cost", # I - formula =G*H
|
|
"Sourcing Link", # J - sourcing_link
|
|
"Schema", # K - schema name
|
|
]
|
|
|
|
# Hidden property columns (collapsed group, available when needed)
|
|
BOM_PROPERTY_HEADERS: List[str] = [
|
|
"Manufacturer", # L
|
|
"Manufacturer PN", # M
|
|
"Supplier", # N
|
|
"Supplier PN", # O
|
|
"Lead Time (days)", # P
|
|
"Min Order Qty", # Q
|
|
"Lifecycle Status", # R
|
|
"RoHS Compliant", # S
|
|
"Country of Origin", # T
|
|
"Material", # U
|
|
"Finish", # V
|
|
"Notes", # W
|
|
"Long Description", # X
|
|
]
|
|
|
|
# Hidden sync columns (never shown to user)
|
|
BOM_SYNC_HEADERS: List[str] = [
|
|
"_silo_row_hash", # Y - SHA256 of row data at pull time
|
|
"_silo_row_status", # Z - synced/modified/new/error
|
|
"_silo_updated_at", # AA - server timestamp
|
|
"_silo_parent_pn", # AB - parent assembly PN for this BOM entry
|
|
]
|
|
|
|
# All headers in order
|
|
BOM_ALL_HEADERS: List[str] = (
|
|
BOM_VISIBLE_HEADERS + BOM_PROPERTY_HEADERS + BOM_SYNC_HEADERS
|
|
)
|
|
|
|
# Index constants for quick access
|
|
COL_ITEM = 0
|
|
COL_LEVEL = 1
|
|
COL_SOURCE = 2
|
|
COL_PN = 3
|
|
COL_DESCRIPTION = 4
|
|
COL_SELLER_DESC = 5
|
|
COL_UNIT_COST = 6
|
|
COL_QTY = 7
|
|
COL_EXT_COST = 8
|
|
COL_SOURCING_LINK = 9
|
|
COL_SCHEMA = 10
|
|
|
|
# Property column range
|
|
COL_PROP_START = len(BOM_VISIBLE_HEADERS) # 11
|
|
COL_PROP_END = COL_PROP_START + len(BOM_PROPERTY_HEADERS) # 24
|
|
|
|
# Sync column range
|
|
COL_SYNC_START = COL_PROP_END # 24
|
|
COL_ROW_HASH = COL_SYNC_START # 24
|
|
COL_ROW_STATUS = COL_SYNC_START + 1 # 25
|
|
COL_UPDATED_AT = COL_SYNC_START + 2 # 26
|
|
COL_PARENT_PN = COL_SYNC_START + 3 # 27
|
|
|
|
# Total column count
|
|
BOM_TOTAL_COLS = len(BOM_ALL_HEADERS)
|
|
|
|
# ---------------------------------------------------------------------------
|
|
# Items sheet columns (flat list of all items for a project)
|
|
# ---------------------------------------------------------------------------
|
|
|
|
ITEMS_HEADERS: List[str] = [
|
|
"PN",
|
|
"Description",
|
|
"Type",
|
|
"Source",
|
|
"Schema",
|
|
"Standard Cost",
|
|
"Sourcing Link",
|
|
"Long Description",
|
|
"Manufacturer",
|
|
"Manufacturer PN",
|
|
"Supplier",
|
|
"Supplier PN",
|
|
"Lead Time (days)",
|
|
"Min Order Qty",
|
|
"Lifecycle Status",
|
|
"RoHS Compliant",
|
|
"Country of Origin",
|
|
"Material",
|
|
"Finish",
|
|
"Notes",
|
|
"Created",
|
|
"Updated",
|
|
]
|
|
|
|
# ---------------------------------------------------------------------------
|
|
# Property key mapping (header name -> DB field path)
|
|
# ---------------------------------------------------------------------------
|
|
|
|
PROPERTY_KEY_MAP: Dict[str, str] = {
|
|
"Manufacturer": "manufacturer",
|
|
"Manufacturer PN": "manufacturer_pn",
|
|
"Supplier": "supplier",
|
|
"Supplier PN": "supplier_pn",
|
|
"Lead Time (days)": "lead_time_days",
|
|
"Min Order Qty": "minimum_order_qty",
|
|
"Lifecycle Status": "lifecycle_status",
|
|
"RoHS Compliant": "rohs_compliant",
|
|
"Country of Origin": "country_of_origin",
|
|
"Material": "material",
|
|
"Finish": "finish",
|
|
"Notes": "notes",
|
|
}
|
|
|
|
# Reverse map
|
|
DB_FIELD_TO_HEADER: Dict[str, str] = {v: k for k, v in PROPERTY_KEY_MAP.items()}
|
|
|
|
# ---------------------------------------------------------------------------
|
|
# Row status colours (RGB tuples, 0-255)
|
|
# ---------------------------------------------------------------------------
|
|
|
|
STATUS_COLORS: Dict[str, Tuple[int, int, int]] = {
|
|
"synced": (198, 239, 206), # light green #C6EFCE
|
|
"modified": (255, 235, 156), # light yellow #FFEB9C
|
|
"new": (189, 215, 238), # light blue #BDD7EE
|
|
"error": (255, 199, 206), # light red #FFC7CE
|
|
"conflict": (244, 176, 132), # orange #F4B084
|
|
}
|
|
|
|
# ---------------------------------------------------------------------------
|
|
# Sheet type detection
|
|
# ---------------------------------------------------------------------------
|
|
|
|
|
|
def detect_sheet_type(headers: List[str]) -> Optional[str]:
|
|
"""Detect sheet type from the first row of headers.
|
|
|
|
Returns ``"bom"``, ``"items"``, or ``None`` if unrecognised.
|
|
"""
|
|
if not headers:
|
|
return None
|
|
# Normalise for comparison
|
|
norm = [h.strip().lower() for h in headers]
|
|
if "item" in norm and "level" in norm and "qty" in norm:
|
|
return "bom"
|
|
if "pn" in norm and "type" in norm:
|
|
return "items"
|
|
return None
|
|
|
|
|
|
def col_letter(index: int) -> str:
|
|
"""Convert 0-based column index to spreadsheet letter (A, B, ..., AA, AB)."""
|
|
result = ""
|
|
while True:
|
|
result = chr(65 + index % 26) + result
|
|
index = index // 26 - 1
|
|
if index < 0:
|
|
break
|
|
return result
|