ali27x/TendersREF

1from __future__ import annotations2 3from dataclasses import dataclass4from pathlib import Path5from typing import Any6import re7 8import openpyxl9 10 11@dataclass(frozen=True)12class MarketDomain:13    id: str14    en: str15    ar: str16 17 18@dataclass(frozen=True)19class MarketSector:20    id: str21    domain_id: str22    en: str23    ar: str24    includes: str = ""25    excludes: str = ""26    notes: str = ""27 28 29@dataclass(frozen=True)30class Nature:31    id: str32    en: str33    ar: str34    scope_required: bool35 36 37@dataclass(frozen=True)38class Scope:39    id: str40    en: str41    ar: str42    applies_to: str43    definition: str44 45 46@dataclass(frozen=True)47class MappingRule:48    term: str49    maps_to: str50    reason: str51    example: str52 53 54@dataclass(frozen=True)55class TcsSpec:56    version: str57    domains: list[MarketDomain]58    sectors: list[MarketSector]59    natures: list[Nature]60    scopes: list[Scope]61    mappings: list[MappingRule]62 63 64def _cell_str(value: Any) -> str:65    return "" if value is None else str(value).strip()66 67 68def _sheet_rows(ws):69    for row in ws.iter_rows(values_only=True):70        yield [_cell_str(v) for v in row]71 72 73def load_spec(xlsx_path: str | Path) -> TcsSpec:74    wb = openpyxl.load_workbook(xlsx_path, data_only=True)75 76    dom_ws = wb["02 Market Domains"]77    sec_ws = wb["03 Market Sectors"]78    nat_ws = wb["04 Nature"]79    scope_ws = wb["05 Scopes"]80    map_ws = wb["07 Special Mappings"]81    overview = wb["01 Overview"]82 83    version = "v1.0"84    for row in _sheet_rows(overview):85        for cell in row:86            if isinstance(cell, str):87                m = re.search(r"v\d+\.\d+", cell)88                if m:89                    version = m.group(0)90                    break91 92    domains: list[MarketDomain] = []93    for row in dom_ws.iter_rows(min_row=4, values_only=True):94        if not row[0]:95            continue96        domains.append(MarketDomain(str(row[0]), _cell_str(row[1]), _cell_str(row[2])))97 98    sectors: list[MarketSector] = []99    for row in sec_ws.iter_rows(min_row=4, values_only=True):100        if not row[0]:101            continue102        sectors.append(103            MarketSector(104                str(row[0]),105                _cell_str(row[1]),106                _cell_str(row[2]),107                _cell_str(row[3]),108                _cell_str(row[6]),109                _cell_str(row[7]),110                _cell_str(row[9]),111            )112        )113 114    natures: list[Nature] = []115    for row in nat_ws.iter_rows(min_row=4, values_only=True):116        if not row[0]:117            continue118        natures.append(Nature(str(row[0]), _cell_str(row[1]), _cell_str(row[2]), _cell_str(row[4]).lower() == "yes"))119 120    scopes: list[Scope] = []121    for row in scope_ws.iter_rows(min_row=4, values_only=True):122        if not row[0]:123            continue124        scopes.append(Scope(str(row[0]), _cell_str(row[1]), _cell_str(row[2]), _cell_str(row[3]), _cell_str(row[4])))125 126    mappings: list[MappingRule] = []127    for row in map_ws.iter_rows(min_row=4, values_only=True):128        if not row[0]:129            continue130        mappings.append(MappingRule(_cell_str(row[0]), _cell_str(row[1]), _cell_str(row[2]), _cell_str(row[3])))131 132    return TcsSpec(version=version, domains=domains, sectors=sectors, natures=natures, scopes=scopes, mappings=mappings)133 134