Metadata-Version: 2.5
Name: visualkit
Version: 0.4.2
Summary: Read-only access to the Infor VISUAL Manufacturing 10 ERP database (SQL Server)
Project-URL: Source, https://gt.pescoinc.biz/spencerm/VisualKit
Author-email: Spencer McGuire <spencerm@pescoinc.biz>
License: Proprietary - PESCO internal use
Classifier: Private :: Do Not Upload
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3
Classifier: Typing :: Typed
Requires-Python: >=3.11
Requires-Dist: pydantic-settings>=2.3
Requires-Dist: pydantic>=2.7
Requires-Dist: pyodbc>=5.1
Provides-Extra: dev
Requires-Dist: build>=1.2; extra == 'dev'
Requires-Dist: pytest>=8; extra == 'dev'
Requires-Dist: twine>=6; extra == 'dev'
Provides-Extra: keyring
Requires-Dist: keyring>=25; extra == 'keyring'
Provides-Extra: parquet
Requires-Dist: pyarrow>=17; extra == 'parquet'
Description-Content-Type: text/markdown

# VisualKit

Read-only Python access to PESCO's Infor VISUAL Manufacturing 10 ERP database (SQL Server). See [PLAN.md](PLAN.md) for the design and roadmap, and [CHANGELOG.md](CHANGELOG.md) for releases.

**Using VisualKit in your own app or scheduled job? Start with [docs/INSTALL.md](docs/INSTALL.md).** This README is for working on VisualKit itself.

VisualKit never changes anything in VISUAL. Only `SELECT` statements get through, and it connects to DEV unless production is chosen explicitly.

## Install (development)

You need Python 3.11+ and Microsoft ODBC Driver 18 for SQL Server (64-bit). On Linux, see [docs/INSTALL.md](docs/INSTALL.md#linux) for the driver.

```bash
python -m venv .venv
.venv\Scripts\python -m pip install -e ".[dev,keyring]"
.venv\Scripts\visualkit doctor          # checks settings, driver, encryption, auth, connectivity and that it can't write
```

On Linux, use `.venv/bin/` instead of `.venv\Scripts\`, and leave out `keyring`.

## Configure

Copy `.env.example` to `.env`. The defaults point at DEV (`VSQL\DEV` / `PESCOINCDEV`). Don't put the password in `.env`.

- **Windows, before `visualkit_ro` exists:** leave `VISUAL_USER` unset. VisualKit uses your Windows login (integrated auth). Keep to DEV and small queries.
- **With `visualkit_ro`:** set `VISUAL_USER=visualkit_ro`, then either
  - store the password once in Windows Credential Manager: `.venv\Scripts\keyring set visualkit visualkit_ro`, or
  - set `VISUAL_PASSWORD`, or `VISUAL_PASSWORD_FILE` pointing at a file that holds only the password.

Production (`vsql` / `PESCOINC`) is only used with `VISUAL_PROFILE=prod`, or with `VISUAL_SERVER=vsql` and `VISUAL_DATABASE=PESCOINC` both set. All settings are listed in [docs/INSTALL.md](docs/INSTALL.md#settings).

## Use

```python
from visualkit import Visual

with Visual.from_env() as v:
    print(v.server_info())                     # SQL Server version, database, login, app name, VISUAL DBVERSION
    for site in v.sites():
        print(site)

    rows = v.query("SELECT ID, DESCRIPTION FROM PART WHERE ID LIKE ?", ["L073%"], max_rows=100)
    count = v.scalar("SELECT COUNT(*) FROM PART")
    for row in v.stream("SELECT ID FROM PART"):  # fetched in batches of VISUAL_BATCH_SIZE
        ...
```

- `query()` stops at `VISUAL_MAX_ROWS` rows (10,000) unless `max_rows=` says otherwise. Use `stream()` for big reads.
- Every query has a timeout (`VISUAL_TIMEOUT`, 60 s).
- Anything that isn't a single `SELECT` is refused before it reaches the server.
- ID columns come back as text. Keep them that way.

### Domains

Eight typed, read-only repositories hang off `Visual`. They return frozen pydantic models. The full reference is in [docs/INSTALL.md](docs/INSTALL.md#api-reference).

```python
from datetime import date
from visualkit import Visual, WorkOrderKey

with Visual.from_env() as v:
    # Parts
    part = v.parts.get("L073-76-2")
    parts = v.parts.get_many(["L073-76-2", "L073-76-2GA"])         # {id: Part}
    planning = v.parts.site("L073-76-2GA", "PESCO")                  # PART_SITE: price, primary location

    # Customer orders
    order, customer = v.orders.with_customer("51049")
    lines = v.orders.lines("51049")
    open_orders = v.orders.list(open_only=True, limit=50)            # a Page

    # Work orders
    wo = v.work_orders.get(WorkOrderKey.parse("L073-75-1/53/0/0"))
    ops = v.work_orders.operations(wo.key)                           # op.barcode() for Packet Station
    feed = v.work_orders.released("PESCO", date(2026, 10, 1), date(2026, 10, 31))

    # Inventory
    stock = v.inventory.on_hand("L073-76-2GA", site_id="PESCO")      # nonzero locations

    # Shipments
    packlists = v.shipments.for_order("51049")
    shipped = v.shipments.for_order_line("51049", 1)                 # [] means not shipped yet

    # Labor: tickets, demonstrated capacity, resources, departments, employees (IDs only)
    tickets = v.labor.tickets_for_work_order("L707001")              # LaborTicket list
    week = v.labor.tickets(date_between=(date(2026, 10, 8), date(2026, 10, 14)), type="R")
    hours = v.labor.hours_by_resource("PESCO", date(2026, 10, 8), date(2026, 10, 14))
    resources = v.labor.resources(site_id="PESCO", obsolete=False)  # STATUS 'O' is obsolete

    # Purchasing: POs, lines, deliveries, receipts, vendors (no vendor names)
    po = v.purchasing.get("268122")
    open_lines = v.purchasing.lines_list(open_only=True, part_id_prefix="L489-")
    pegged = v.purchasing.lines_for_work_order("L707001")            # outside service and material
    receipts = v.purchasing.receipts_for_po("268122")

    # Links: work order to customer order, and PO lines to work orders
    orders = v.links.orders_for_work_orders([WorkOrderKey("L707001")])
    po_links = v.links.for_purchase_order("268122", line_no=1)
```

`v.links` also answers "which order is this job for" through DEMAND_SUPPLY_LINK; see the reference for what it covers.

### Ad-hoc SQL

For a question no repository answers, `visualkit.sql` runs one SELECT you wrote yourself, through the same guard. Values go in as `:name` parameters, never formatted into the SQL. There is no `site_id`: filter on `SITE_ID` in the query.

```python
from visualkit import Visual, run_sql, stream_sql
from visualkit.sql import load_sql_file

with Visual.from_env() as v:
    res = run_sql(v, "SELECT TOP 10 ID, STATUS FROM PURCHASE_ORDER WHERE VENDOR_ID = :vendor "
                     "ORDER BY ID DESC", {"vendor": "HALSER"})
    print(res.columns, len(res), res.elapsed, res.sha256)

    sql = load_sql_file("open_po_lines.sql")                       # one SELECT, WITH ... allowed
    with stream_sql(v, sql, {"site": "PESCO"}) as rows:              # no row cap; run it off-hours
        for row in rows:
            ...
```

`SET`, `DECLARE`, `@variables`, temp tables, `EXEC` and a second statement are refused. `visualkit.sql.explain_rejection(sql)` says which construct is blocked and how to rewrite it. The porting notes for Insights' scripts are in [docs/INSTALL.md](docs/INSTALL.md#ad-hoc-sql).

List calls return a `Page`: `page.items`, `page.has_more`, `page.next_offset`.

### CLI

```bash
visualkit doctor | info | sites
visualkit parts get L073-76-2 L073-76-2GA          # also: list, search, site
visualkit orders get 51049                         # also: list, lines
visualkit wo get L073-75-1/53/0/0                  # or a bare base ID: lot 1, split 0, sub 0
visualkit wo released --since 2026-10-01 --until 2026-11-14 --limit 5
visualkit wo barcode "*%2L073-75-1$53$0$0$10%*" --fetch
visualkit inventory onhand L073-76-2GA             # also: list, warehouses, locations
visualkit ship get 77881                           # also: list, for-order, for-line
visualkit labor hours --since 2026-10-08 --by resource --limit 5
visualkit labor wo L707001                         # also: tickets, resources, departments, employees
visualkit po list --open --limit 3                 # also: get, lines, wo, receipts
visualkit po wo L707001                            # PO lines pegged to a work order
visualkit vendor get HALSER                        # IDs and terms only; never the vendor name
visualkit sql "SELECT TOP 3 ID FROM PART ORDER BY ID" --json
visualkit sql --file q.sql -p site=PESCO -p since:date=2026-10-01 --explain
visualkit extract list                             # also: run, status (see Extract below)
```

Every data subcommand prints a table, or JSON with `--json`. Most take `--site`. List subcommands take `--limit` and `--offset`; when there are more rows, the next `--offset` goes to stderr. `--width` sets where long text is cut (40 by default). Dates are `YYYY-MM-DD` or ISO. Run `visualkit <domain> <action> --help` for the filters.

Exit codes: 0 fine, 1 not found or a VISUAL error, 2 bad settings or input. Production needs `--allow-production`.

### Packet Station example

[examples/packet_station_feed.py](examples/packet_station_feed.py) replaces the two Power BI feeds Packet Station reads today. It writes one JSON object with `meta`, `rows` (Job Release Information: one row per operation, with `wo, lot, split, sub, seq, res, dwg, rev, ps, desc, qty, tgt, rel, want, st`, plus `barcode`, `part`, `site` and `order`), `materials` (Job Pull: one row per material line, with `wo, lot, split, sub, seq, piece, part, qty, issued, whs, loc, st, istat, dwg, desc, comm, cons`), `orders_by_base` and `orders_by_project`.

Customer orders come from DEMAND_SUPPLY_LINK (`v.links`), not from part-number prefixes. `orders_by_base` holds the orders a base is pegged to directly; only whole-number top jobs (L489001) and field-service jobs are. `orders_by_project` holds the orders of each project's top jobs, found by PESCO's "project plus three digits" naming (L489 -> L489001); that is a naming convention, not a VISUAL relationship. Each row's `order` is the base's own order, else the project's first open order, else its newest order, else null, with `via` saying which. Order entries carry the order ID, status, customer ID and name, customer PO and dates; no amounts.

```bash
.venv\Scripts\python examples\packet_station_feed.py --since 2026-09-30 --out feed.json
```

- `--since` is required. `--until` is optional; without it there is no upper bound, like the Power BI report.
- `--status` picks the work order statuses for rows (default `F,R,U,C`). `--materials-status` picks the bases and legs for materials (default `F,R`).
- `--no-materials` leaves materials out. `--no-orders` leaves out `orders_by_base`, `orders_by_project` and `order`. `--full` adds the raw `work_orders`, `operations`, `requirements`, `parts` and `orders` sections; `orders` lists the directly linked orders with their bases and lines.
- PARTSPECS (`ps`) comes from OPERATION_BINARY; the text of material lines with no part comes from REQUIREMENT_BINARY.
- Numbers are JSON numbers, dates `YYYY-MM-DD`, IDs strings. No cost, price or pay field is written.

On DEV, since 2026-09-30 with no upper bound: 1,533 work orders, 5,962 rows and 16,956 material lines in about 7.5 seconds. Compared with the Power BI export of 2026-10-14, every matched field agrees except where DEV changed after the export (see PLAN.md section 8b).

## Extract

`visualkit.extract` pulls whole tables to CSV or Parquet files for Insights and other bulk readers. There are 40 ready-made jobs, three of them restricted. PART is incremental on MODIFY_DATE. LABOR_TICKET and INVENTORY_TRANS are windowed on TRANSACTION_DATE, with 730 and 1,096 days of history. Every other job is a snapshot. The full guide, with the output layout and how to read it, is in [docs/INSTALL.md](docs/INSTALL.md#extraction).

```bash
visualkit extract list                                     # jobs: mode, modified column, column count; and the presets
visualkit extract run insights --out D:\extracts\visual    # a preset, job names, or "all"
visualkit extract run part labor_ticket --out D:\extracts\visual --since 2025-01-01
visualkit extract run work_order --out x --dry-run         # print the SQL and parameters; no connection
visualkit extract status --out D:\extracts\visual          # checkpoints, last complete run, last 10 runs
```

- `run` prints one line per job (rows, bytes, seconds, mode, checkpoint). A failed job is reported and the next one runs; the exit code is then 1.
- `--full` restarts incremental jobs from their start and replaces their part files. `--since` sets that start for incremental jobs. `--site` filters the jobs that have a site column. `--format parquet` needs `pip install "visualkit[parquet]"`.
- Production needs `--allow-production` and runs only between 18:00 and 06:00 unless `--force`.
- Presets (`visualkit.extract.presets.PRESETS`): `insights` (every ordinary job: masters, then documents, then the two windowed tables), `packet_station`, `masters`, `restricted` (every restricted job) and `restricted_costs` (the two cost jobs).
- **Restricted jobs** `work_order_cost` and `part_site_cost` read labor and burden cost totals per work order and per part, and `employee_name` reads employee ID, first and last name, for VisualMCP. They are only in `restricted` and `restricted_costs` (costs only), never in `all`, and run only with `--allow-restricted` (`run_job(..., allow_restricted=True)`). `extract list` marks them RESTRICTED.

```python
from pathlib import Path
from visualkit import Visual
from visualkit.extract import JOBS, run_job

with Visual.from_env() as v:
    summary = run_job(v, JOBS["part"], Path("extracts"))
    print(summary.rows, summary.output, summary.checkpoint_after)
```

### Insights snapshot example

[examples/insights_snapshot.py](examples/insights_snapshot.py) writes Insights' dated layout on top of `run_job`: `ROOT/<DEV|PROD>/<yyyy-MM-dd_HHmm>.partial/` while running, renamed without `.partial` only when every job completed, with a `manifest.json` (who, when, parameters, and per job rows, bytes, seconds and the query's SHA-256). Each snapshot is a new folder, so incremental jobs run with `full=True` and the windowed tables re-read their whole window every time, as `run_extract.ps1` does.

```bash
.venv\Scripts\python examples\insights_snapshot.py --out D:\visual-insights --site PESCO
```

`--history-days N` sets the labor and inventory window (default 35); `--full` makes it 730 days. On DEV the whole `insights` preset took 139 s for 3.16 million rows (349 MB of CSV); the largest read was REQUIREMENT, 984,356 rows in 50 s.

## Test

```bash
.venv\Scripts\python -m pytest
```

Tests use a fake cursor and need no database. Integration tests run against DEV only, when asked:

```powershell
$env:VISUALKIT_INTEGRATION = "1"; .venv\Scripts\python -m pytest
```

## Release

1. Bump `__version__` in `src/visualkit/__init__.py` and add a `CHANGELOG.md` entry.
2. Run the tests, then build:
   ```bash
   .venv\Scripts\python -m pytest
   .venv\Scripts\python -m build
   ```
3. If any runtime dependency version changed, update `requirements/runtime-lock.txt`. The first section lists the versions the tests ran with. The second is the `keyring` extra, mirrored for Windows only: on Linux the password comes from `VISUAL_PASSWORD` or `VISUAL_PASSWORD_FILE`, so keyring's Linux deps are not mirrored. The third is the `parquet` extra (`pyarrow` only; it has no dependencies). The fourth has the extras the `keyring` extra needs on Python 3.11 only; for those, pick releases that are at least 14 days old.
4. Build the wheelhouse, so installs work from PESCO's registry alone. `pyodbc` and `pydantic_core` have a separate wheel per platform and Python version, so this step matters. The dependency list is short, so plain `pip download` does it; copy the pins from `requirements/runtime-lock.txt` into the four lists. pyarrow publishes `manylinux_2_28` Linux wheels only, so it is downloaded for that platform (PowerShell):
   ```powershell
   $runtime = 'annotated-types==0.8.0','pydantic-settings==2.16.0','pydantic==2.14.1','pydantic_core==2.50.1','pyodbc==5.3.0','python-dotenv==1.2.4','typing-inspection==0.4.4','typing_extensions==4.16.0'
   $keyring = 'jaraco.classes==3.4.0','jaraco.context==6.1.2','jaraco.functools==4.6.0','keyring==25.7.0','more-itertools==11.2.1','pywin32-ctypes==0.2.3'
   $py311   = 'backports.tarfile==1.2.0','importlib-metadata==9.0.1','zipp==4.1.0'
   $parquet = 'pyarrow==26.0.0'
   foreach ($plat in 'win_amd64','manylinux2014_x86_64') {
     foreach ($py in '3.11','3.12','3.13','3.14') {
       $pkgs = $runtime
       if ($plat -eq 'win_amd64') { $pkgs += $keyring; if ($py -eq '3.11') { $pkgs += $py311 } }
       foreach ($p in $pkgs) {
         .venv\Scripts\python -m pip download $p --no-deps --only-binary=:all: --platform $plat --implementation cp --python-version $py -d wheelhouse -q
         if ($LASTEXITCODE) { Write-Warning "no wheel: $p $plat cp$py" }
       }
       $pp = if ($plat -eq 'win_amd64') { $plat } else { 'manylinux_2_28_x86_64' }
       .venv\Scripts\python -m pip download $parquet --no-deps --only-binary=:all: --platform $pp --implementation cp --python-version $py -d wheelhouse -q
       if ($LASTEXITCODE) { Write-Warning "no wheel: $parquet $pp cp$py" }
     }
   }
   ```
   Then check that the whole tree resolves from `dist` and `wheelhouse` alone, for Linux and in a throwaway venv on Windows:
   ```powershell
   'visualkit[parquet]==0.3.0' | Set-Content $env:TEMP\vk-req.txt
   foreach ($py in '3.11','3.14') {
     .venv\Scripts\python -m pip download -r $env:TEMP\vk-req.txt --no-index --find-links dist --find-links wheelhouse --only-binary=:all: --platform manylinux2014_x86_64 --platform manylinux_2_28_x86_64 --implementation cp --python-version $py -d $env:TEMP\vk-linux$py
   }
   python -m venv $env:TEMP\vk-venv
   & $env:TEMP\vk-venv\Scripts\python -m pip install "visualkit[keyring,parquet]==0.3.0" --no-index --find-links dist --find-links wheelhouse
   & $env:TEMP\vk-venv\Scripts\visualkit --version
   ```
5. Upload to the Gitea package registry. This needs a Gitea access token with `write:package` scope; paste it when twine asks for the password:
   ```bash
   .venv\Scripts\python -m twine upload --repository-url https://gt.pescoinc.biz/api/packages/spencerm/pypi -u spencerm dist\*
   .venv\Scripts\python scripts\missing_wheels.py
   .venv\Scripts\python -m twine upload --repository-url https://gt.pescoinc.biz/api/packages/spencerm/pypi -u spencerm wheelhouse\upload\*
   ```
   twine 7 refuses `--skip-existing` for registries other than PyPI, and Gitea rejects a re-upload of a file it already has. `scripts\missing_wheels.py` reads the registry's simple index and copies only the wheels it lacks to `wheelhouse\upload\`, so the second upload never hits a duplicate.
   Once both are uploaded, the registry install line in [docs/INSTALL.md](docs/INSTALL.md) works:
   ```bash
   pip install "visualkit==0.3.0" --index-url https://gt.pescoinc.biz/api/packages/spencerm/pypi/simple/
   ```
6. Tag the release: `git tag v0.3.0` and `git push --tags`.

Source: `https://gt.pescoinc.biz/spencerm/VisualKit`. Proprietary, PESCO internal.
