API Reference

Auto-IT One

The unified REST API for Auto-IT South Africa — structured access to customer accounts, invoices, credit notes, payments, statements and more. Read endpoints across the board, with a focused set of write operations for CRM data.

Protocol HTTPS Format JSON Auth Header Keys Access Read + Write Version 2.8

Introduction

Auto-IT One gives integrated systems structured access to financial account data maintained within Auto-IT. Read endpoints use GET and return JSON. As of v2.1 a small set of PATCH/POST write endpoints can update customer contact details and notes.

Each endpoint is a standalone PHP file served from the API root — e.g. /customers.php, /invoices.php. There is no routing layer.

Invoice line items are included. Invoice responses embed parts, labour, misc, vehicle, accessory and trade-in detail per module (I Parts · V Vehicle · W Workshop).

Write scope is intentionally narrow. Writes cover CRM/contact data only. Financial postings (invoices, payments, allocations) and inventory quantities are not writable here — they must go through Auto-IT so the ledger, aging and stock stay consistent.

Authentication

Every request must include two custom HTTP headers. Both are validated before any query executes. A missing or incorrect header returns 401 or 403 immediately.

AUTHKEYyour-auth-keyIdentifies the calling application

APIKEYyour-api-key-secretCredential paired to the AUTHKEY

WRITEKEYyour-write-keyRequired only for write endpoints — second factor for mutations

Try it: Enter your keys once in any endpoint's modal — they are saved for the rest of your session.

Example — cURL

bash

curl -X GET "https://your-server/api/customers.php?acc_no=1001" \
     -H "AUTHKEY: your-auth-key" \
     -H "APIKEY: your-api-key-secret"

Conventions

Topic	Detail
Base URL	`https://your-server.com/api/` — replace with your hosting path
Method	All endpoints are `GET` — read-only
Encoding	UTF-8. All responses carry `Content-Type: application/json`
Dates	Pass as `YYYY-MM-DD` in query parameters
Pagination	`?page=` (default 1) and `?limit=`. Leave `limit` blank to return all rows; pass a number for that many (an unusable value falls back to 20). Responses include a `meta` block
Amounts	Numeric, 2 decimal places. Balances calculated live from the allocations ledger
Tran types	`IN` Invoice · `CR` Credit Note · `RC` Receipt · `DS` Discount
Debug mode	Append `?debug=1` to return generated SQL without executing it

Standard list response

json

{ "meta": { "total": 1284, "page": 1, "limit": 20, "total_pages": 65 }, "data": [ /* records */ ] }

Error Codes

Code	Meaning	Common cause
200	OK	Request succeeded
400	Bad Request	Missing required parameter
401	Unauthorized	AUTHKEY or APIKEY header absent
403	Forbidden	Key pair does not match
404	Not Found	Record does not exist
405	Method Not Allowed	Non-GET request sent
500	Server Error	Database error — check the `message` field

Executive

Executive Summary

A single top-management snapshot assembled from the platform's tables: debtors and creditors balances with aging, working capital, vehicle / wholegoods stock by type with retail value, and parts and workshop activity. Bound the activity window with date_from / date_to.

GET executive_summary.php ?date_from= &date_to=

Covers what's computable today. The full Financial Position (P&L, balance sheet, ratios) comes from the general ledger and needs the GL tables; gross profit / margin everywhere needs the cost columns (vehicle / parts / labour). Those are added as soon as those schemas are shared.

Key	Type	Required	Description
date_from / date_to	date	opt	Activity window for parts / workshop

json

{ "debtors": { "total": 12840300.00,
    "aging": { "current": 8100200.00, "days_30": 2510000.00 } },
  "creditors": { "total": 9650100.00 },
  "working_capital": 3190200.00,
  "vehicle_stock": { "by_type": [{ "type": "N", "units": 128, "retail_value": 78200000.00 }] },
  "parts_sales": { "sale_value": 4210880.00 },
  "workshop": { "repair_orders": 3421, "labour_value": 2980400.00 } }

Customer Anatomy

A 360° view of one debtor — profile and contact, the live AR position (balance, aging, credit limit and terms), what they spend with the dealership by department, and their most recent transactions and invoices.

GET customer_anatomy.php ?acc_no={n} &date_from= &date_to=

Spend is drawn from the Invoice ledger by department (module). Omit the date window for lifetime spend.

Key	Type	Required	Description
acc_no	integer	req	Debtor account number
date_from / date_to	date	opt	Spend window (omit for lifetime)

json

{ "profile": { "ACC_NO": 1001, "company_name": "Acme Parts" },
  "ar_position": { "balance": 48250.00, "credit_limit": 100000.00,
    "aging": { "current": 30000.00, "days_30": 18250.00 } },
  "spend": { "totals": { "invoices": 214, "total": 1875400.00 },
    "by_module": [{ "module": "W", "total": 920100.00 }] } }

Admin · Accounts Receivable

Debtors

The debtor (accounts-receivable) master — account and contact detail joined across the master debtor, customer settings, and contact tables. Includes billing and postal address, VAT/ABN, credit terms, trading details, and live aging. Served by customers.php.

GET customers.php ?search= &acc_no= &status= …

Paginated customer list. Use ?search= for partial match across company name, surname, first name, and email. Pass ?acc_no= to retrieve a single account with full detail and live aging.

Key	Type	Required	Description
search	string	opt	Partial match on company name, surname, name, email
acc_no	integer	opt	Exact account — returns single record with aging breakdown
status	string	opt	Credit status code — e.g. `O` = Open
trade_type	string	opt	Filter by trade type code
territory	string	opt	Filter by territory code
page	integer	opt	Default: 1
limit	integer	opt	blank = all rows; number = that many (else 20)

json

{ "meta": { "total": 842, "page": 1, "limit": 20 },
  "data": [{ "ACC_NO": 1001, "company_name": "Acme Parts",
    "abn": "4320000001", "CREDIT_LIMIT": 50000.00,
    "Credit_Status": "O", "city": "Johannesburg" }] }

Debtor Transactions

Reads ArTrans (the live AR ledger). Each row carries its allocation position — allocated_to, allocated_by, and a net outstanding (positive = still owed, negative = unapplied credit). Pass ?include_archive=1 to also return historical rows from ArTrans_Old.

GET debtor_transactions.php ?acc_no= &tran_type= &include_archive= …

Filter by acc_no for a single debtor. Response includes page totals; archive rows (if requested) come back in their own shape.

Key	Type	Required	Description
acc_no	integer	opt	Debtor account — recommended
tran_type	string	opt	`IN` · `CR` · `RC` · `DS`
ref	string	opt	Exact document reference
receipt_no	string	opt	Exact receipt number
date_from / date_to	date	opt	TRANS_DATE range
search	string	opt	ref, description, receipt_no
include_archive	integer	opt	`1` appends ArTrans_Old (capped 200)
page / limit	integer	opt	limit blank = all rows; number = that many (else 20)

json

{ "meta": { "total": 214 },
  "page_totals": { "value": 48250.00, "outstanding": 12300.00 },
  "data": [{ "REF": "INV00123", "TRAN_TYPE": "IN",
    "VALUE": 2255.00, "allocated_to": 2000.00,
    "outstanding": 255.00, "TRANS_DATE": "2025-04-15 09:22:11" }] }

Debtor Statements

Generates a complete account statement for a given period. The response is self-contained — everything needed to render or print a statement in one call.

GET statement.php ?date_from={date}&date_to={date} &acc_no=

Returns opening balance, transactions with running balance, closing balance, summary by type, and aging. Pass ?acc_no= for a single statement, or omit it to return paginated statements for all accounts under statements[].

Multi-account mode defaults to 10 per page (max 50) — each statement runs several queries so keep limit low.

Key	Type	Required	Description
date_from	date	req	Period start — `YYYY-MM-DD`
date_to	date	req	Period end — `YYYY-MM-DD`
acc_no	integer	opt	Single account — omit to return all accounts
status	string	opt	Filter by credit status in multi-account mode — e.g. `O`
currency	string	opt	Restrict to a specific foreign currency
page / limit	integer	opt	Multi-account only · Defaults: 1 / 10, max limit: 50

json

{ "customer": { "acc_no": 1001, "company_name": "Acme Parts", "abn": "4320000001" },
  "opening_balance": 8500.00,
  "transactions": [{ "TRAN_TYPE": "IN", "VALUE": 2255.00, "running_balance": 10755.00 }],
  "closing_balance": 5755.00,
  "aging": { "current_age": 2255.00, "days_30": 1800.00, "total_outstanding": 5755.25 } }

Admin · Accounts Payable

Creditors

Reads from APMASTER (the creditor master) joined to contact via CONTACT_CODE. A single creditor returns the full record plus a live open balance, aging, and recent transactions — all derived from ApTrans.

GET creditors.php ?acc_no= &search= &status= …

Paginated creditor list with open balance. ?acc_no= returns a single creditor with aging and recent transactions.

Key	Type	Required	Description
acc_no	integer	opt	Single creditor + balance, aging, recent txns
search	string	opt	company, surname, name, email, VAT/ABN
status	string	opt	`CREDIT_STATUS` code
trade_term	string	opt	`TRADE_TERM` code
branch	string	opt	Branch code
foreign	integer	opt	`1` = foreign entities only
page / limit	integer	opt	limit blank = all rows; number = that many (else 20)

json

{ "meta": { "total": 318 },
  "data": [{ "ACC_NO": 5001, "company_name": "Bosch SA",
    "TRADE_TERM": "30", "CREDIT_STATUS": "O",
    "YTD_PURCH_VAL": 842500.00, "open_balance": 128400.00 }] }

Creditor Transactions

Reads ApTrans (the live AP ledger). Each row carries outstanding = GROSS_AMOUNT − PART_PAID_GROSS. Pass ?include_archive=1 to also return historical rows from OldTrans under an archive block.

GET creditor_transactions.php ?acc_no= &include_archive= …

Filter by acc_no for a single creditor. Response includes page totals; archive rows (if requested) come back in their own shape.

Key	Type	Required	Description
acc_no	integer	opt	Creditor account — recommended
doc_type	string	opt	`ApTrans.DOC_TYPE`
status	string	opt	`ApTrans.STATUS`
doc_no	string	opt	Exact document number
order_number	string	opt	Exact PO number
date_from / date_to	date	opt	trans_date range
search	string	opt	doc_no, description, payment_ref
include_archive	integer	opt	`1` appends OldTrans (capped 200)
rec_type	string	opt	Filter OldTrans.REC_TYPE (archive only)
page / limit	integer	opt	limit blank = all rows; number = that many (else 20)

json

{ "meta": { "total": 96 },
  "page_totals": { "gross": 128400.00, "outstanding": 128400.00 },
  "data": [{ "DOC_NO": "PINV4471", "DOC_TYPE": "IN",
    "GROSS_AMOUNT": 14200.00, "outstanding": 14200.00,
    "DUE_DATE": "2025-06-30 00:00:00" }] }

Creditor Statement

A full AP statement for one creditor over a period: opening balance, period transactions with a running balance, closing balance, summary and aging.

GET creditor_statement.php ?acc_no={n}&date_from={date}&date_to={date}

All three parameters are required. Each transaction contributes its unpaid gross to the running balance.

Key	Type	Required	Description
acc_no	integer	req	Creditor account number
date_from	date	req	Period start — `YYYY-MM-DD`
date_to	date	req	Period end — `YYYY-MM-DD`

json

{ "creditor": { "acc_no": 5001, "company_name": "Bosch SA" },
  "opening_balance": 114200.00,
  "transactions": [{ "DOC_NO": "PINV4471", "outstanding": 14200.00, "running_balance": 128400.00 }],
  "closing_balance": 128400.00,
  "aging": { "current": 80000.00, "days_31_60": 48400.00, "total_outstanding": 128400.00 } }

Admin · System

Branches

Reads branch_name and tags each branch with the tier-1 departments it actually operates — wholegoods (VhStock), parts (InTrans) and workshop (WKINVREG) — so a client can show only the branches relevant to the active tab. Presence is history-wide, so the flags don't flicker as a date filter changes. Drives the global Branch filter.

GET branches.php

No parameters. Each department flag is a correlated EXISTS probe into the branch column, so it short-circuits on the first match rather than scanning for distinct values.

Key	Type	Required	Description
—	—	opt	No query parameters. `?debug=1` returns the SQL.

json

{ "count": 7,
  "data": [{ "branch": "01", "name": "Head Office",
    "has_wholegoods": 1, "has_parts": 1, "has_workshop": 1 }] }

Accounting Periods

Reads syscalendar — the financial calendar. Each row is one accounting period: year_end (financial year), period_start (first day) and period_me (last day = period_start + no_of_day − 1). By default only periods on or before the current month are returned — the periods you can post into.

GET accounting_periods.php ?all= &year= …

Periods up to the current month by default. ?all=1 includes future periods; ?year= bounds to one financial year.

Key	Type	Required	Description
all	integer	opt	`1` = include future periods
year	integer	opt	Financial year, e.g. `2026`

json

{ "filters": { "all": 0, "year": null }, "count": 9,
  "data": [{ "year_end": 2026, "period_start": "2025-03-01",
    "period_me": "2025-03-31" }] }

Franchises

Reads codtyp where TYPE = 'FC' — the franchise code list — translating a franchise code (the FRANCHISE column on InMaster / InTrans) to its display name, long description and chip colours. This is the lookup behind the franchise_name now returned by the parts endpoints.

GET franchises.php ?code= …

All franchise codes, ordered by sequence. ?code= returns a single franchise.

Key	Type	Required	Description
code	string	opt	Exact franchise code

json

{ "count": 6,
  "data": [{ "franchise": "TY", "name": "Toyota",
    "long_description": "Toyota South Africa", "color_bg": "#EB0A1E",
    "sequence_no": 1, "read_only": "N" }] }

Invoices & Credit Notes

Reads from the Invoice table. Invoices and credit notes are linked to a debtor via bill_to_acc = acc_no. Type codes: I = Invoice · C = Credit Note. Module codes: I = Parts · V = Vehicle · W = Workshop.

GET invoices.php ?document_no= &type= &acc_no= &module_type= …

Paginated list of invoices and credit notes from the Invoice table. Pass ?document_no= to fetch a single record with full detail, outstanding balance, and allocation history.

Key	Type	Required	Description
document_no	string	opt	Single invoice / credit note — returns full detail + allocation history
type	string	opt	`invoice` → `invo_type = I` · `credit` → `invo_type = C`
acc_no	integer	opt	Debtor account number (`bill_to_acc`)
module_type	string	opt	`I` = Parts · `V` = Vehicle · `W` = Workshop
salesman	string	opt	Partial match on salesman code
currency	string	opt	3-letter ISO currency code
date_from	date	opt	`YYYY-MM-DD`
date_to	date	opt	`YYYY-MM-DD`
cancelled	integer	opt	`0` exclude cancelled · `1` only cancelled
search	string	opt	Partial match on company name, surname, doc no, order no
page / limit	integer	opt	limit blank = all rows; number = that many (else 20)

json

{ "meta": { "total": 3201 },
  "data": [{ "document_no": "INV00123", "invo_type": "I", "module_type": "W",
    "bill_to_acc": 1001, "company_name": "Acme Parts",
    "parts_sale_val": 1200.00, "labour_sale_val": 850.00,
    "gst": 205.00, "invoice_total": 2255.00,
    "invo_datetime": "2025-04-15 09:22:11", "cancel_date": null }] }

Allocated Payments

Reads from ArTrans_Alloc — the table that records payments once they have been matched against invoices. Each row represents an allocation: a payment applied to a specific invoice, with the amount matched and the dates involved.

GET payments.php ?acc_no= &date_from= &date_to= &invoice_ref=

Returns all allocated payment records from ArTrans_Alloc, joined to ArTrans for payment detail and Invoice for invoice detail. Filter by acc_no to get all payments for a specific debtor.

Key	Type	Required	Description
acc_no	integer	opt	Debtor account number — recommended for performance
invoice_ref	string	opt	Exact invoice document number — returns all payments against that invoice
date_from	date	opt	Allocation date from — `YYYY-MM-DD`
date_to	date	opt	Allocation date to — `YYYY-MM-DD`
page / limit	integer	opt	limit blank = all rows; number = that many (else 20)

json

{ "meta": { "total": 142 },
  "data": [{
    "invoice_rec_no":       45201,
    "payment_rec_no":       46100,
    "amount_allocated":     2255.00,
    "allocation_date":      "2025-05-02 00:00:00",
    "invoice_ref":          "INV00123",
    "payment_ref":          "EFT-20250502",
    "payment_date":         "2025-05-02 00:00:00",
    "payment_amount":       -5000.00,
    "acc_no":               1001,
    "document_no":          "INV00123",
    "invo_type":            "I",
    "module_type":          "W",
    "company_name":         "Acme Parts"
  }] }

Top X Customers

Returns the top 20 debtors ranked by total invoice sales value (excl. and incl. tax). Only invoices (invo_type = I) are included — credit notes are excluded. Optionally filter by date range or module type.

GET top_customers.php ?date_from= &date_to= &module_type=

Aggregates invoice sales by debtor account, joins customer details, and returns the top 20 sorted by sales_incl_tax descending. Includes invoice count, sales excluding tax, total tax, and sales including tax per customer.

Key	Type	Required	Description
top	integer	opt	Number of customers to return, max 100 · Default: 20
date_from	date	opt	Invoice date range start — `YYYY-MM-DD`
date_to	date	opt	Invoice date range end — `YYYY-MM-DD`
module_type	string	opt	`I` = Parts · `V` = Vehicle · `W` = Workshop

json

{ "filters": { "date_from": "2025-01-01", "date_to": "2025-12-31", "module_type": null },
  "count": 20,
  "data": [{
    "acc_no":          1001,
    "company_name":    "Acme Parts Pty Ltd",
    "email_address":   "accounts@acme.co.za",
    "vat_no":          "4630198010",
    "credit_limit":    50000.00,
    "invoice_count":   84,
    "sales_excl_tax":  284500.00,
    "total_tax":       42675.00,
    "sales_incl_tax":  327175.00
  }] }

Vehicle Stock

Reads from VhStock — the new, used and demo units. Pass ?stock_no= for a single unit including the accessories allocated to it (VhStockAccess, keyed on stock number) and its most recent appraisal.

GET vehicles.php ?stock_no= &make= &model= &status= …

Paginated list of units in stock (no sales invoice, not swapped out). ?stock_no= returns a single unit with accessories and appraisal. Sold units are in vehicle_sales.php.

Key	Type	Required	Description
stock_no	integer	opt	Single unit + accessories + appraisal
make	string	opt	Exact make
model	string	opt	Partial model match
status	string	opt	Stock status code
fuel_type	string	opt	Exact fuel type
year_from / year_to	integer	opt	Model-year range
reg / vin	string	opt	Registration / VIN match
search	string	opt	make, model, vin, reg, stock_no
page / limit	integer	opt	limit blank = all rows; number = that many (else 20)

json

{ "meta": { "total": 312 },
  "data": [{ "stock_no": 12345, "make": "Toyota", "model": "Hilux",
    "vin_no": "AHT…", "stock_status": "A", "sale_price_incl": 689900.00 }] }

Vehicle Accessories

Reads VhAccess — the master catalogue of every accessory available for sale, with cost/sell pricing, GST and tax flags, factory/supplier references and effectivity dates. Accessories actually allocated to a unit are returned per stock number by vehicles.php?stock_no=.

GET vehicle_accessories.php ?search= &make= &type= &active= …

Paginated accessory catalogue. Note that Make = 'ALL' is the catch-all make.

Key	Type	Required	Description
search	string	opt	code, description, alternate description
code	string	opt	Exact accessory code
make	string	opt	Make (`ALL` = catch-all)
type	string	opt	`VhAccess.TYPE`
category	string	opt	`codtyp_category`
branch	string	opt	Branch code
display	integer	opt	`1` = DisplayIndicator 'Y' only
active	integer	opt	`1` = effective today only
page / limit	integer	opt	limit blank = all rows; number = that many (else 20)

json

{ "meta": { "total": 1240 },
  "data": [{ "code": "TOWBAR-HX", "description": "Tow Bar Kit",
    "make": "Toyota", "sales_price": 4500.00,
    "sales_price_inc_gst": 5175.00, "display_indicator": "Y" }] }

Vehicle Sales

Reads VhStock and returns only units that have been sold — a sales invoice is stamped on them. The complement of Vehicle Stock; the two sets don't overlap. For a sold unit's full detail (accessories, appraisal) use vehicles.php?stock_no=.

GET vehicle_sales.php ?date_from= &date_to= &sales_invoice= &make= …

Paginated list of sold units, newest sale first. Each page carries page_totals (units and sale value). Gross profit awaits the VhStock cost column.

Key	Type	Required	Description
date_from / date_to	string	opt	Sale-date range (SALESDATE), YYYY-MM-DD
sales_invoice	string	opt	Exact sales invoice number
make	string	opt	Exact make
model	string	opt	Partial model match
reg / vin	string	opt	Registration / VIN match
year_from / year_to	integer	opt	Model-year range
search	string	opt	make, model, vin, reg, stock_no
page / limit	integer	opt	limit blank = all rows; number = that many (else 20)

json

{ "meta": { "total": 1820 },
  "page_totals": { "units": 20, "sales_value": 7430000.00 },
  "data": [{ "stock_no": 500124, "make": "Toro", "model": "WM1110",
    "sales_invoice": "35923", "sales_date": "2026-05-18 14:54:17",
    "sales_value": 35000.00 }] }

Repair Orders

Reads from WKROFILE with the serviced vehicle from WKVEHFL. A single RO returns the same line-item breakdown as a workshop invoice — parts (InTrans + InMaster), labour (hours × value), consumables / misc, and a combined ro_totals summary.

GET workshop.php ?ro_number= &reg= &search= …

Paginated repair-order list. ?ro_number= returns a single RO with vehicle, parts, labour and consumable/misc lines plus combined totals.

The repair-order header is returned as-is (SELECT rf.*) so every real WKROFILE column comes through. Filtering is limited to ro_number / reg until the WKROFILE schema is confirmed. Parts are read from InTrans by REF_NO = RO number.

Key	Type	Required	Description
ro_number	integer	opt	Single RO + vehicle + parts + labour + misc
reg	string	opt	Exact registration
search	string	opt	reg, RO number
page / limit	integer	opt	limit blank = all rows; number = that many (else 20)

json

{ "data": { "RO_NUMBER": 10234, "reg": "CA123456",
    "vehicle": { "make": "Ford", "model": "Ranger" },
    "parts_items": { "parts_items_total": { "qty": 6, "sale_value": 2240.00 } },
    "labour_items": { "labour_items_total": { "hrs": 2.5, "labour_sale": 1875.00 } },
    "misc_items": { "misc_items_total": { "value": 120.00 } },
    "ro_totals": { "parts_sale": 2240.00, "labour_sale": 1875.00, "total_sale": 4235.00 } } }

Work In Progress

The live WIP board — every open repair order (at least one uninvoiced WKOTHSUB line, header not invoiced) with its financial rollup across labour (WKMECHWK), sublet (PURCHASE_CONTROL), other (WKRODESC) and parts (INSALPAR + INMASTER valuation). Each RO is classified as WIP, Quote, No Show or Future Booking, and carries the equipment, sales advisor, last technician and age. The response pairs a ready-to-plot summary (counts by category, value totals, age bands) with the paginated data rows.

GET workshop_wip.php ?branch= &category= &job_type= &job_code= &mechanic_code= …

Open repair orders with a live value rollup and derived category. The summary block feeds the WIP widgets (value tile, category donut, age-band bar); the data array is the WIP list, filterable and paginated. Every filter is optional; category is validated against the known set, and comma lists are accepted for branch / branch_exclude / franchise / category. The core is job-line grain (branch·ro·job_code·job_type); group_by=ro (default) rolls it up to one row per RO for the widgets, group_by=job returns the raw lines. job_code / job_type slice the lines before the rollup (RO value = matching lines only); ?job_facets=1 returns their distinct value lists for the picker. mechanic_code works differently on purpose: technician lives one grain below the RO (on the labour lines, and an RO usually has several), so it is an EXISTS that selects whole ROs — "the ROs this technician has labour on" — and leaves their value alone. Parts, sublet and RO charges cannot be attributed to a technician, so slicing the value would hand back a partial total that read exactly like a real one.

The financial rollup, category rules and parts-cost valuation are workshop business logic and are preserved verbatim. branch here is the RO branch. The expensive core query runs once per request; the summary and pagination are computed over the returned set.

Key	Type	Required	Description
branch	string	opt	Comma list of RO branches to include
branch_include	string	opt	CSV of RO branches to include (Pulse-standard alias of `branch`)
branch_exclude	string	opt	Comma list of RO branches to exclude
franchise	string	opt	Comma list of franchise codes to include
franchise_include	string	opt	CSV of franchise codes to include (Pulse-standard alias of `franchise`)
category	string	opt	`WIP` · `Quote` · `No Show` · `Future Booking` (comma list allowed)
service_type	string	opt	`Field` or `Shop` (partial match)
group_by	string	opt	`ro` (default) = one row per open RO (the widget rollup, with `summary`) · `job` = raw job-line detail (per `branch·ro·job_code·job_type`, no `summary`)
job_code / job_code_include	string	opt	Friendly job-code bucket(s): `Sublet` · `Repair` · `Service` · `<raw code>` (comma list). Filters the job LINES before the rollup, so the RO's value is the slice of matching lines only.
job_type / job_type_include	string	opt	Friendly job-type(s): `Retail` · `Fleet` · `Internal` · `Warranty` · `Policy` · `Sundry` · `Excess` · `Project Billing` (comma list). Same line-slice semantics as `job_code`.
job_facets	flag	opt	`1` ⇒ returns `{job_codes:[…], job_types:[…]}` distinct value lists (branch-scoped) for the Filters-modal pickers, and nothing else.
ro_number	integer	opt	A single repair order
mechanic_code / mechanic_code_include	string	opt	Comma list of WKMECHWK mechanic codes — keeps only ROs one of those technicians has a labour line on. Technician is not an RO column (it is one grain down, and an RO usually has several), so this is an `EXISTS` over the RO's labour lines and the value is not sliced: a matching RO still shows its whole total, not that technician's share of it. An RO with no labour line therefore drops out entirely, and that is a large population — measured 2026-08-11, only 1 671 of 4 092 open ROs carried any labour line, including none of the 602 Quotes — so any technician filter empties the Quote category. Roster for the picker comes from `workshop_wip_labour.php?tech_facets=1`.
flag	string	opt	`Y` = future booking that already has value; `N` = rest
min_total_sales	number	opt	Only ROs with `total_sales` ≥ this (value-at-risk)
min_age	integer	opt	Only ROs at least N days old (by `datetime_in`)
search	string	opt	reg, RO number or equipment
page / limit	integer	opt	limit blank = all rows; number = that many (else 20)

json

{ "meta": { "total": 128, "page": 1, "limit": "all", "total_pages": 1 },
  "summary": {
    "ro_count": 128,
    "by_category": { "WIP": 90, "Quote": 20, "No Show": 8, "Future Booking": 10 },
    "totals": { "lab_sales": 184200, "sub_sales": 42100, "other_sales": 9800,
      "part_sales": 211500, "total_sales": 447600, "total_cost": 310400, "gross_profit": 137200 },
    "age_bands": { "0_7": 61, "8_30": 40, "31_60": 15, "61_90": 7, "90_plus": 5 } },
  "data": [{ "branch": "DBN", "branch_name": "DURBAN", "ro_no": 10234,
    "category": "WIP", "service_type": "Shop", "franchise": "TY", "franchise_name": "Toyota",
    "equipment": "FORD RANGER", "reg_no": "CA123456", "ro_status": "In Workshop",
    "sales_advisor": "Jane Doe", "last_tech": "John Smith",
    "total_sales": 4235, "total_cost": 2980, "age_days_datetime_in": 3, "flag": "N" }] }

WIP Labour Detail

The labour-grain drill-down behind Work In Progress — one row per WKMECHWK labour line on an open RO (same WIP gate: uninvoiced WKOTHSUB job + header not invoiced). Each line carries the mechanic (contact), booked vs worked hours, job-code estimate (WkCodeFl), sell/cost values and derived sell_rate / cost_rate / eff_perc. The response pairs a summary (by-mechanic rollup + weighted totals) for the technician-productivity widgets with the paginated data lines.

GET workshop_wip_labour.php ?ro_number= &mechanic_code= &tech_facets=1 &work_location= &ro_status= &sales_advisor= &job_code= …

Per-line labour detail for open ROs. Pass ?ro_number= for a single-RO drill-down, or filter across the whole WIP by branch / franchise / mechanic / work location / job code / job type. The summary.by_mechanic rollup feeds the technician-efficiency widgets; efficiency_pct is booked ÷ worked hours, and the rates are weighted (Σvalue ÷ Σhours). category is the SAME 4-way value as workshop_wip.php (WIP / Quote / No Show / Future Booking), and job_code / job_type are the same friendly labels — so a tab-wide Job Code / Job Type filter matches on both endpoints. Every line also carries the RO header's ro_status and sales_advisor: the RO number is the key both endpoints already share, and WKROFILE is joined here anyway for the open-RO gate, so two lookup joins off it put the board's own Status and Sales Advisor on every labour line — filterable here, and value-identical to workshop_wip.php because the expressions are copied verbatim. This endpoint also owns the technician roster (?tech_facets=1): it is the only WIP feed that carries a technician at all, so every other WIP card's Technician picker is fed from here, and the same pick means "this technician's lines" here but "the ROs those lines sit on" everywhere else.

Rate / efficiency divisions are guarded (zero-hours line → 0, never a divide-by-zero). A labour line matched to more than one open WKOTHSUB sub-line can appear more than once — verify against your WKOTHSUB grain if exact de-duplication matters.

Key	Type	Required	Description
ro_number	integer	opt	A single repair order (drill-down)
branch	string	opt	Comma list of RO branches to include
branch_include	string	opt	Alias of `branch` (Pulse multi-select CSV)
branch_exclude	string	opt	Comma list of RO branches to exclude
franchise	string	opt	Comma list of franchise codes to include
franchise_include	string	opt	Alias of `franchise` (Pulse multi-select CSV)
ro_status	string	opt	RO progress status(es) — comma list of the `CODTYP 'WC'` descriptions the WIP board shows in its Status column. Carried onto every labour line via the RO join, so a value picked on a WIP-RO card matches here verbatim. Matched on the trimmed value: this DB is not blank-padded, and at least one live description carries a real trailing space, so `Claim Warranty` is selectable as `Claim Warranty`.
ro_status_include	string	opt	Alias of `ro_status` (Pulse multi-select CSV)
sales_advisor	string	opt	Sales advisor(s) — comma list of `Name Surname`, exactly as the board renders them
sales_advisor_include	string	opt	Alias of `sales_advisor` (Pulse multi-select CSV)
mechanic_code	string	opt	One or more technician / mechanic codes (comma list). This endpoint is line-grain, so it matches its own column — "this technician's lines". The other WIP feeds have no technician column and use an `EXISTS` over the RO's labour lines instead, i.e. "the ROs they have labour on". Matched on the trimmed column, same reason as `ro_status`.
mechanic_code_include	string	opt	Alias of `mechanic_code` (Pulse multi-select CSV)
tech_facets	flag	opt	`1` ⇒ returns `{technicians:[{mechanic_code, mech_name}]}` and nothing else — the roster for the tab-wide Technician picker, which every other WIP card is fed from because this is the only endpoint that knows it. Derived from the same core query as the detail (not a raw WKMECHWK scan), so it can never offer a technician whose only work sits on ROs this feed excludes. Honours every filter except `mechanic_code`, so the picker never narrows itself.
work_location	string	opt	`Shop` or `Field` (accepts `S` / `F`)
job_code	string	opt	Friendly job-code bucket(s): `Sublet` / `Repair` / `Service` / raw code (comma list)
job_code_include	string	opt	Alias of `job_code` (Pulse multi-select CSV)
job_type	string	opt	Friendly job-type(s): `Retail` / `Fleet` / `Internal` / `Warranty` / `Policy` / `Sundry` / `Excess` / `Project Billing`
job_type_include	string	opt	Alias of `job_type` (Pulse multi-select CSV)
search	string	opt	mechanic name, RO number or job code
page / limit	integer	opt	limit blank = all rows; number = that many (else 20)

json

{ "meta": { "total": 342, "page": 1, "limit": "all", "total_pages": 1 },
  "summary": {
    "line_count": 342, "mechanic_count": 11,
    "totals": { "hours_work": 1284.5, "invoice_hrs": 1102.0,
      "sell_val": 742100.00, "cost_val": 388600.00, "gross_profit": 353500.00,
      "efficiency_pct": 85.79, "sell_rate": 673.41, "cost_rate": 302.53 },
    "by_mechanic": [{ "mechanic_code": "1042", "mech_name": "John Smith",
      "lines": 48, "hours_work": 168.0, "invoice_hrs": 152.5,
      "sell_val": 102400.00, "cost_val": 50800.00, "gross_profit": 51600.00,
      "efficiency_pct": 90.77, "sell_rate": 671.48, "cost_rate": 302.38 }] },
  "data": [{ "branch": "DBN", "franchise": "TY", "ro_number": 10234, "category": "WIP",
    "job_code": "Service", "job_type": "Retail", "work_cat": "1", "mechanic_code": "1042",
    "work_location": "Shop", "mech_name": "John Smith", "seq": 1,
    "hours_work": 3.5, "job_code_hrs": 3.0, "invoice_hrs": 3.0,
    "sell_val": 2010.00, "cost_val": 1058.75,
    "sell_rate": 670.00, "cost_rate": 302.50, "eff_perc": 85.71 }] }

WIP Sublet Detail

The sublet-grain drill-down behind Work In Progress — one row per PURCHASE_CONTROL sublet line on an open RO (same WIP gate: uninvoiced WKOTHSUB job + header not invoiced). Each line carries the vendor (APMASTER_CONTACT), purchase order number/description & status, vendor-invoice reference, sublet sell/cost value and derived gross_profit / margin_perc. Zero-value lines are excluded. The response pairs a summary (by-vendor rollup + totals) for the sublet widgets with the paginated data lines.

GET workshop_wip_sublet.php ?ro_number= &vendor_no= &service_type= &ro_status= &sales_advisor= &mechanic_code= &job_code= …

Per-line sublet detail for open ROs. Pass ?ro_number= for a single-RO drill-down, or filter across the whole WIP by branch / franchise / vendor / service type / PO status / job code / job type. The summary.by_vendor rollup feeds the sublet widgets; margin_perc is gross profit ÷ sell value, and comma lists are accepted for branch / franchise / vendor_no / category. category is the SAME 4-way value as workshop_wip.php (WIP / Quote / No Show / Future Booking), and job_code / job_type are the same friendly labels — so a tab-wide Job Code / Job Type filter matches on both endpoints. (job_type replaces the old raw ro_type column.) Every line also carries the RO header's ro_status and sales_advisor, resolved through the RO number the two endpoints share (see WIP Labour Detail). Note sales_advisor is the RO advisor and is not po_creater, who is whoever raised the sublet purchase order — different people, different source. mechanic_code is the tab-wide Technician filter: a sublet line has no technician of its own, so it resolves by EXISTS over the RO's labour lines — "the sublet sitting on the ROs this technician is working".

The margin division is guarded (a cost-only line where sell = 0 → 0, never a divide-by-zero — the source query divided by sell directly). branch here is the RO branch. Lines with both sell and cost of 0 are excluded, matching the source.

Key	Type	Required	Description
ro_number	integer	opt	A single repair order (drill-down)
branch	string	opt	Comma list of RO branches to include
branch_include	string	opt	Alias of `branch` (Pulse multi-select CSV)
branch_exclude	string	opt	Comma list of RO branches to exclude
franchise	string	opt	Comma list of franchise codes to include
franchise_include	string	opt	Alias of `franchise` (Pulse multi-select CSV)
ro_status	string	opt	RO progress status(es) — comma list of the `CODTYP 'WC'` descriptions the WIP board shows in its Status column. Carried onto every sublet line via the RO join, so a value picked on a WIP-RO card matches here verbatim. Matched on the trimmed value: this DB is not blank-padded, and at least one live description carries a real trailing space, so `Claim Warranty` is selectable as `Claim Warranty`.
ro_status_include	string	opt	Alias of `ro_status` (Pulse multi-select CSV)
sales_advisor	string	opt	Sales advisor(s) — comma list of `Name Surname`. This is the RO advisor, not `po_creater`, who is whoever raised the sublet purchase order.
sales_advisor_include	string	opt	Alias of `sales_advisor` (Pulse multi-select CSV)
mechanic_code / mechanic_code_include	string	opt	Comma list of WKMECHWK mechanic codes — keeps only the sublet sitting on ROs one of those technicians has a labour line on. A sublet line has no technician of its own (the work is off-site, done by the vendor), so this is an `EXISTS` over the RO's labour lines; line values are not sliced. Same shared definition the RO board uses, so one pick means the same thing on both.
vendor_no	string	opt	One or more creditor / vendor account numbers (comma list)
service_type	string	opt	`Shop` or `Field` (accepts `S` / `F`)
purchase_status	string	opt	`Open` or `Closed` (the sublet PO status)
category	string	opt	`WIP` · `Quote` · `No Show` · `Future Booking` (comma list)
job_code	string	opt	Friendly job-code bucket(s): `Sublet` / `Repair` / `Service` / raw code (comma list)
job_code_include	string	opt	Alias of `job_code` (Pulse multi-select CSV)
job_type	string	opt	Friendly job-type(s): `Retail` / `Fleet` / `Internal` / `Warranty` / `Policy` / `Sundry` / `Excess` / `Project Billing`
job_type_include	string	opt	Alias of `job_type` (Pulse multi-select CSV)
search	string	opt	vendor name, RO number, purchase no/desc or job code
page / limit	integer	opt	limit blank = all rows; number = that many (else 20)

json

{ "meta": { "total": 86, "page": 1, "limit": "all", "total_pages": 1 },
  "summary": {
    "line_count": 86, "vendor_count": 14,
    "totals": { "sublet_val": 142800.00, "sublet_cost": 101450.00,
      "gross_profit": 41350.00, "margin_pct": 28.96 },
    "by_vendor": [{ "vendor_no": "A0012", "vendor_name": "ACME EXHAUST CO",
      "lines": 12, "sublet_val": 31200.00, "sublet_cost": 22100.00,
      "gross_profit": 9100.00, "margin_pct": 29.17 }] },
  "data": [{ "branch": "DBN", "franchise": "TY", "ro_number": 10234,
    "category": "WIP", "job_code": "Sublet", "job_type": "Retail", "service_type": "Shop",
    "purchase_no": 50021, "purchase_desc": "EXHAUST REPAIR", "purchase_status": "Open",
    "vendor_no": "A0012", "vendor_name": "ACME EXHAUST CO",
    "vend_invoice_no": "INV-88231", "vend_invoice_date": "2026-07-14",
    "sublet_val": 2600.00, "sublet_cost": 1840.00,
    "gross_profit": 760.00, "margin_perc": 29.23 }] }

WIP Parts on Backorder

The parts-requirement drill-down behind Work In Progress — one row per INSALPAR sales-order part line linked to an open RO (wkrofile.part_order_no = insalpar.file_no, RO not invoiced). Each line splits the committed qty (comm_part_qty) into what is already on a purchase order (po_qty, capped at the committed qty) and what still has to be ordered (to_order_qty), each valued at the part's derived unit_cost (moving-average cost, else replacement / stock-order price). Carries InMaster description & bin, the salesperson (CONTACT) and the same Job Age columns as the labour / sublet lines. Three response shapes: ?group=1 returns a compact branch × franchise rollup aggregated in SQL (the Pulse widget's main view — a few hundred cells, not the 28k-line board); ?risk_rollup=1 returns the Workshop Risk tab's three backorder exceptions, also aggregated in SQL; without either you get the per-line data + a by-branch summary, which is only ever requested drill-scoped to a single branch+franchise. The core query drives from WkRoFile (ROs that carry a parts order) and seeks INSALPAR by file_no, so it never scans all of INSALPAR. Never pull the whole board: measured live it is 28 459 rows / 35.6 MB / 13.6 s, and ?limit does not bound the query — pagination slices after the fetch, so a capped page costs the same DMS work.

GET workshop_wip_parts_backorder.php ?group=1 &risk_rollup=1 &risk_lines= &branch= &job_age= &part_no= &ro_status= &sales_advisor= …

?group=1 → the compact branch × franchise rollup (drives the widget's rollup levels + KPIs). ?risk_rollup=1 → the Workshop Risk tab feed: the three backorder exceptions as counts + values + contributing-group tables for both drill directions, the two fences and their populations, and a worst-10 context list per fenced rule — all aggregated off the same #bo temp, with ?risk_lines=aging|over|novendor serving its drill leaf from the detail mode. ?parts_like=<q> → distinct part_no/part_desc matching a prefix (feeds the Part filter's type-to-search). ?part_franchises=1 → distinct part_franchise (+ name) for the Part-Franchise picker. ?cat_counts=1 → distinct RO count per category ({counts,total}) for the Options scope badges. ?category= filters any mode to WIP/Quote/No Show/Future Booking (also emitted on the rollup). ?order_state= filters to on_order/to_order/over — per line, the outstanding requirement (order_qty − supplied_qty) vs the raw po_qty; also a rollup grouping dimension carrying over_value. ?no_vendor=1 isolates at-risk lines — no default vendor (insalpar.PO_DEFAULT_VENDOR null) and still short of a PO — also a rollup dimension. Otherwise → per-line detail (drill-scoped). Filters (branch/franchise/part_franchise/ro_type/part_no/job_age/mechanic_code) apply server-side to every mode — mechanic_code being the tab-wide Technician filter, which a part line cannot carry and so resolves by EXISTS over the RO's labour lines ("which of this technician's jobs are stuck waiting for parts"); to_order_qty = committed − on-PO and to_order_value = that qty × unit_cost. The rollup also carries a per-cell risk_rule (aging/over/novendor/'') and echoes the two Tukey thresholds in meta.risk_thresholds, so the Pulse widget lights Risk flames straight off the rollup (no second line-level fetch). The fences are computed here in PHP — a byte-for-byte port of the Workshop Risk tab's tukeyFence (floors 7d / R2000) over the header scope (risk_branch/risk_franchise) — so a flame always maps to a real Risk exception. The core is materialised once into a #bo temp table shared by the rollup + both fence scans; the drill leaf re-uses the echoed thresholds (age_thr/over_thr) to flag its own lines.

The unit_cost division is guarded (a zero on-hand line falls back to the replacement / stock-order price, never a divide-by-zero). po_qty is capped at the committed qty so an over-ordered PO can never drive to_order_qty negative. branch is the RO branch. Two franchises per line: franchise = wkrofile.FRANCHISE (the job / RO franchise, what the rollup groups by and the Franchise filter acts on); part_franchise = i.FRANCHISE (the part's franchise, named via the Parts codtyp TYPE='FC' lookup).

Key	Type	Required	Description
ro_number	string	opt	Repair order number(s) — comma list (a single value behaves as before); `ro_number_include` alias
ro_status	string	opt	RO progress status(es) — comma list of the `CODTYP 'WC'` descriptions the WIP board shows; `ro_status_include` alias. Matched on the trimmed value, so `Claim Warranty` is selectable as `Claim Warranty`.
sales_advisor	string	opt	RO sales advisor(s) — comma list of `Name Surname`; `sales_advisor_include` alias. This is the RO advisor, not the `salesperson` column (who raised the parts sales order).
branch	string	opt	Comma list of RO branches to include
branch_include	string	opt	Alias of `branch` (Pulse multi-select CSV)
branch_exclude	string	opt	Comma list of RO branches to exclude
franchise	string	opt	Comma list of job / RO franchise codes (`wkrofile.FRANCHISE`) to include
franchise_include	string	opt	Alias of `franchise` (Pulse multi-select CSV)
mechanic_code / mechanic_code_include	string	opt	Comma list of WKMECHWK mechanic codes — keeps only backorders on ROs one of those technicians has a labour line on ("which of this technician's jobs are stuck waiting for parts"). A part line has no technician, so this is an `EXISTS` over the RO's labour lines; line values are not sliced. Honoured by every facet mode too — it is never a field a facet feeds.
part_franchise	string	opt	Comma list of part franchise codes (`i.FRANCHISE`) to include; `part_franchise_include` alias
ro_type	string	opt	One or more RO types (`ret_war_pol`; comma list; `ro_type_include` alias)
part_no	string	opt	Exact part numbers to include (comma list; `part_no_include` alias)
job_age	string	opt	Job-age buckets: `0_7` · `8_30` · `31_90` · `90_plus` (comma list)
job_code	string	opt	Friendly job-code bucket(s): `Sublet` / `Repair` / `Service` / raw code (comma list; same labels as workshop_wip.php; `job_code_include` alias)
job_type	string	opt	Friendly job-type(s): `Retail` / `Fleet` / `Internal` / `Warranty` / `Policy` / `Sundry` / `Excess` / `Project Billing` (`job_type_include` alias)
group	integer	opt	`1` = branch × franchise rollup instead of per-line data
parts_like	string	opt	Prefix → distinct `part_no`/`part_desc` (Part picker search)
part_franchises	integer	opt	`1` = distinct `part_franchise` (+ `part_franchise_name`) for the Part-Franchise picker
ro_facets	integer	opt	`1` = distinct `ro_numbers` / `ro_statuses` / `sales_advisors` for the three RO pickers, in one round trip. Honours every filter except those three, so no picker narrows itself or its siblings. Uncapped by design — a silent `TOP n` would read as "that RO has no backorders".
category	string	opt	RO category filter: `WIP` · `Quote` · `No Show` · `Future Booking` (comma list; derived identically to `workshop_wip.php` / `workshop_wip_labour.php` / `workshop_wip_sublet.php` — No Show/Future Booking = the RO's other + parts sales = 0). Also emitted on the rollup rows.
cat_counts	integer	opt	`1` = distinct RO count per category `{counts:{…}, total}` for the Options scope badges. Honours every filter except `category` (facets across categories); RO identity = `branch`+`ro_number` so a part spanning franchises counts once.
order_state	string	opt	On/to/over-order state filter: `on_order` · `to_order` · `over` (comma list). Per line, `req_qty` (`order_qty − supplied_qty`) vs the raw `po_qty`. Also emitted on the rollup as a grouping dimension (with `over_value`).
no_vendor	integer	opt	Orderability filter: `1` = at-risk lines — no default vendor (`insalpar.PO_DEFAULT_VENDOR` null/blank) and still short of a PO (can't be DMS-auto-ordered); `0` = the rest. Fully-ordered no-vendor lines carry no risk so aren't flagged. Also a rollup grouping dimension.
risk_branch	string	opt	Rollup only. The header branch scope (F9-independent) the Risk-flame Tukey fences are computed over — kept separate from `branch_include` (which may be narrowed by the widget's own filters) so the fence population matches the Workshop Risk tab. Falls back to `branch_include`/`branch`.
risk_franchise	string	opt	Rollup only. The header job-franchise scope for the fence population (see `risk_branch`). Falls back to `franchise_include`/`franchise`.
risk_rollup	integer	opt	`1` = the Workshop Risk tab feed: the three backorder exceptions (`aging` · `over` · `novendor`) as `count` + `value` + contributing-group tables for both drill directions (`groups_branch` / `groups_franchise`), plus `meta.risk_thresholds`, `meta.populations` and a worst-10 `fallback` list per fenced rule. Aggregated in SQL off the same `#bo` temp as the rollup, so membership matches the widget flames exactly. Replaces the tab's old whole-board `?limit=0` pull (28k rows / 35.6 MB / 13.6 s → KBs). The three rules are independent predicates, not the priority `risk_rule` CASE — a no-vendor line that is also an age outlier counts in both.
risk_lines	string	opt	Detail only. `aging` · `over` · `novendor` — restrict the per-line list to the lines flagged by ONE rule (the Risk tab's drill leaf). Uses the same predicate as `risk_rollup`, so a leaf always sums back to the total it was drilled from. Pass `age_thr`/`over_thr` with it; without them a fenced rule returns nothing (never everything). Unknown value → `400`.
age_thr	number	opt	Detail only. The aging Tukey threshold (in days) the rollup returned in `meta.risk_thresholds.aging`, echoed back so drill lines get the SAME `risk_rule` without re-materialising the fence population. Absent = no aging flags.
over_thr	number	opt	Detail only. The over-order Tukey threshold (rand value) from `meta.risk_thresholds.over`, echoed back for per-line flagging. Absent = no over-order flags.
fields	string	opt	Detail only. `export` = project the ~33 core columns down to the 16 the Pulse export workbook needs (its 13 columns + the 3 quantities `summary` sums), dropping `risk_rule`. The export is the one caller that legitimately needs every LINE (28k of them), so `w.` more than doubled its payload for columns the workbook never opens. Any other value = full `w.`.
search	string	opt	part no/desc, RO number, sales-order no or salesperson
page / limit	integer	opt	limit blank = all rows; number = that many (else 20)

json

// ?group=1 — branch × franchise rollup (the widget's main view)
{ "meta": { "mode": "rollup", "cells": 257,
    // Tukey thresholds (header scope) — echoed to the drill leaf as ?age_thr / ?over_thr
    "risk_thresholds": { "aging": 34, "over": 4200.00 } },
  "rollup": [{ "branch": "DBN", "franchise": "TY", "part_franchise": "TY", "part_franchise_name": "Toyota",
    "lines": 28, "ro_count": 12, "part_count": 24, "po_value": 9100.00, "to_order_value": 5010.00,
    // per-cell Risk-flame rule (folded to a branch/franchise flame client-side); '' = none
    "risk_rule": "aging" }] }

// ?risk_rollup=1 — the Workshop Risk tab feed (replaces the old whole-board ?limit=0 pull)
{ "meta": { "mode": "risk_rollup", "cells": 188,
    "risk_thresholds": { "aging": 34, "over": 4200.00 },
    // fence POPULATIONS (all committed lines in that state, pre-threshold) — the cards quote these
    "populations": { "unordered": 9142, "over": 2210 } },
  // one block per rule; the three are INDEPENDENT (a line can appear in aging AND novendor)
  "rules": { "aging": { "count": 412, "value": 1840233.55,
      // branch groups come back name = code (branch names live in gauge.branches, not the DMS)
      "groups_branch": [{ "name": "DBN", "sub": "DBN", "n": 88, "val": 402118.20, "pct": 21.8 }],
      "groups_franchise": [{ "name": "Toyota", "sub": "TY", "n": 140, "val": 655900.10, "pct": 35.6 }],
      "branches": ["DBN", "JHB"] },
    "over": { "count": 51, "value": 288400.00, "groups_branch": [], "groups_franchise": [], "branches": [] },
    "novendor": { "count": 96, "value": 140120.75, "groups_branch": [], "groups_franchise": [], "branches": [] } },
  // worst 10 lines per fenced rule — shown as context when that rule has NO outlier
  "fallback": { "aging": [{ "branch": "DBN", "part_no": "90915-YZZD2", "ro_number": 344843,
      "sales_order_days": 211, "to_order_value": 1820.40, "over_value": 0, "no_vendor": 1 }],
    "over": [] } }

// ?risk_lines=aging&age_thr=34&branch_include=DBN&limit=0 — that group's flagged lines (the Risk leaf)
{ "meta": { "total": 88 }, "data": [{ "part_no": "90915-YZZD2", "risk_rule": "aging", "…": "…" }] }

// per-line detail (no group; the widget requests this drill-scoped to one branch+franchise)
{ "meta": { "total": 64, "page": 1, "limit": "all", "total_pages": 1 },
  "summary": {
    "line_count": 64, "ro_count": 37, "part_count": 58,
    "totals": { "order_qty": 214.0, "supplied_qty": 150.0, "comm_part_qty": 64.0,
      "po_qty": 41.0, "to_order_qty": 23.0, "po_value": 18420.00, "to_order_value": 10360.00 },
    "by_branch": [{ "branch": "DBN", "lines": 28, "comm_part_qty": 31.0,
      "po_qty": 20.0, "to_order_qty": 11.0, "po_value": 9100.00, "to_order_value": 5010.00 }] },
  "data": [{ "branch": "DBN", "ro_number": 10234, "franchise": "TY",
    "part_franchise": "TY", "part_franchise_name": "Toyota",
    "part_no": "90915-YZZD4", "part_desc": "OIL FILTER", "ro_type": "R", "bin_location": "A-12-3",
    "sales_order_no": 55021, "sales_order_date": "2026-07-10", "sales_order_days": 17,
    "salesperson": "Jane Doe", "order_qty": 4, "supplied_qty": 1, "comm_part_qty": 3,
    "po_qty": 2, "to_order_qty": 1, "unit_cost": 112.40,
    "po_value": 224.80, "to_order_value": 112.40,
    "datetime_in": "2026-07-10 08:15", "age_days_datetime_in": 17 }] }

Workshop Sales

The invoiced counterpart to Work In Progress — one row per time bucket × job code × job type, off WKINVREG (the invoice one-liner, work_date = invoiced date) with the sale elements split by job line from the ZA_WKOTHSUB snapshot and technician hours from ZA_WKMECHWK. Rolls up by day, week-within-month, month or year. Same filter vocabulary as the WIP endpoints, so one Workshop filter bar drives both.

GET workshop_sales.php ?date_from= &date_to= &months= &bucket= &group_by= &branch= &job_code= …

Server-side aggregate — the payload is bounded by the bucket count, never by the invoice count. bucket chooses the time grain (day · week · month · year, where week is the week WITHIN the month, 1–5) and group_by the dimension carried alongside it (job = job_code × job_type, the default; or branch / franchise / advisor / none). Every row carries labour_sales / parts_sales / other_sales / sublet_sales + total_sales, and — for the labour element — invoice_hrs, hours_work, labour_cost and labour_gp. Gross profit is COMPLETE: labour_cost from ZA_WKMECHWK clocking, parts_cost and sublet_cost from INVOICE (parts_cost_val / sublet_cost_val, document+RO grain, split across job lines by each element own sale), and other_cost a hard 0 since Other has no cost of sales. So labour_gp / parts_gp / sublet_gp / other_gp and — for the first time — total_gp. Plus the three document counts ro_count / invoice_count / credit_note_count. job_code / job_type are the SAME friendly labels as workshop_wip.php, so a tab-wide Job Code / Job Type filter matches on both.

Totals always tie back to WKINVREG. Where the ZA_WKOTHSUB snapshot does not fully account for a document (ROs predating the mirror, or a partial snapshot), the remainder is emitted as a synthetic job_code: "Unallocated" row with allocated: false, and reported in summary.reconciliation — so a snapshot gap is visible rather than a quiet shortfall. This residual is suppressed when a job_code / job_type filter is active (you asked for a slice, so the rest of the document is not "unallocated"); summary.reconciliation.ties_to_wkinvreg says which mode you are in.

The three document counts have two rules, and both bite. ro_count / invoice_count / credit_note_count come off WKINVREG — the same authority as the money — at document grain.
1. They are null at line grain. A row only carries them when the row IS a document grouping: group_by=none · branch · franchise · advisor. For group_by=job (the default) they are null, because a bucket fans out to dozens of job_code × job_type rows and stamping the bucket's 159 ROs onto 40 of them gives a column that looks right and totals to 6 360. meta.counts tells you which case you are in.
2. ro_count does not sum across buckets. It is a DISTINCT count — an RO invoiced Monday and credited Thursday is distinct in both buckets, so adding buckets counts it twice. invoice_count and credit_note_count do sum. For a window figure read summary.totals, which runs its own un-grouped query for exactly this reason.
Related: doc_count has always counted documents (invoices + credits), not repair orders — on 2026-08-03 one dealer shows 168 documents against 159 distinct ROs. Anything reading doc_count as "repair orders" has been wrong by that margin.

A document_no or ro_number lookup searches all history. Both are self-restricting and more selective than any date range, so passing either drops the default date window rather than silently clipping the result to the current year — meta.date_from/date_to come back null with meta.date_window: "unbounded …". An explicit date_from/date_to still wins, which is how you scope a prefix search like ?document_no=WI001* to one year. Both columns are indexed, so unbounded here is a seek, not a scan.

Credit notes are included by default, so the figure is net; ?credits=0 excludes them. Values are read as stored (native sign, no forced negation) — the same rule invoices.php follows. Because that sign convention is a DMS behaviour rather than a schema guarantee, summary.by_credit_flag always breaks out I vs C so it is readable at a glance. A credit's job lines are filed under the original invoice's document_no — wkinvreg carries two rows with two numbers, the za_* mirrors carry one number for both — so the credit is linked to its lines through invoice.orig_invoice_no, keyed (document_no, module_type='W', invo_type). Where that is null it falls back to the nearest inv_rev_date match. Because the two sides are two different document numbers, a plain ?document_no= search shows one of them and a total that reads as if the work was never reversed — pass ?include_related=1 to get the whole family back, netted, however many months apart they were raised.

Key	Type	Required	Description
date_from	date	opt	`YYYY-MM-DD` on `work_date` (the invoiced date), inclusive. Default: 1 January of the current year — unless `document_no` / `ro_number` is passed, which drops the default window
date_to	date	opt	`YYYY-MM-DD`, inclusive of the whole day. Default: today (same exception)
bucket	string	opt	`day` · `week` · `month` · `year` (default `month`). `week` = week within the month, 1–5. `year` is the FINANCIAL year, not the calendar one (changed 2026-08-15) — it joins SYSCALENDAR, the same per-dealer financial calendar `accounting_periods.php` reads, so a dealer running Apr–Mar buckets Apr–Mar. `period_key` is the bare FY number and `period_label` reads `FY2027`. NB the cost of a call tracks the window, not the bucket: a multi-year window is a multi-year query (~9 s per financial year measured), so `bucket=year` over several years is slow regardless of how few rows it returns
group_by	string	opt	`job` (default: job_code × job_type) · `branch` · `franchise` · `advisor` · `none`
branch	string	opt	Comma list of invoice branches to include
branch_include	string	opt	Alias of `branch` (Pulse multi-select CSV)
branch_exclude	string	opt	Comma list of branches to exclude
franchise	string	opt	Comma list of franchise codes to include
franchise_include	string	opt	Alias of `franchise` (Pulse multi-select CSV)
job_code	string	opt	Friendly job-code bucket(s): `Sublet` / `Repair` / `Service` / raw code (comma list). Suppresses the Unallocated row
job_code_include	string	opt	Alias of `job_code` (Pulse multi-select CSV)
job_type	string	opt	Friendly job-type(s): `Retail` / `Fleet` / `Internal` / `Warranty` / `Policy` / `Sundry` / `Excess` / `Project Billing`
job_type_include	string	opt	Alias of `job_type` (Pulse multi-select CSV)
trade_type	string	opt	Comma list of `WKINVREG.trade_type` codes
sales_advisor	string	opt	Comma list of advisor codes (`WKINVREG.sales_advisor`)
ro_number	integer	opt	A single repair order — the lookup form, which drops the default date window (see `document_no`). To filter inside a period use `ro_number_include` instead; the two are not interchangeable, and a comma list passed here is silently ignored (it fails `is_numeric`), returning the whole window while looking filtered
ro_number_include	string	opt	Repair orders to keep, inside the window — comma list, exact values only. This is the filter form: it never touches the date range. No `*` here on purpose — the wildcard belongs to `filter_search`, which is what fills the picker, and what comes back from it are exact picks. Keeps the clause an integer `IN` on a numeric column rather than a cast-and-`LIKE`
document_no	string	opt	Invoice / credit-note number. Comma list; a trailing `` on a term makes that term a prefix search (`WI001`). Applied at document level, so the buckets returned are that document's alone. Drops the default date window — searches all history. Pass `date_from`/`date_to` explicitly and you get the intersection instead, which is how it also works as an in-period filter
filter_search	string	opt	Prefix search for the Filters panel's two high-cardinality pickers. One of `document_no` or `ro_number` (whitelisted — anything else is a `400`), paired with `q`. Returns `{data:[{<field>: value}, …]}`, `TOP 100`, keyed by the field name so the shared filters engine consumes the rows directly. Reads WKINVREG at document grain — no job-line join, no clocking leg, no cost legs
q	string	opt	The prefix for `filter_search`. 2 characters minimum (matching the shared engine's own floor); shorter returns an empty `data` rather than every document in the window
filter_facets	integer	opt	`1` ⇒ the three low-cardinality document-grain pickers in one round trip, and nothing else: `{branches:[code,…], franchises:[code,…], sales_advisors:[{value:code, label:name},…]}`. Branch and franchise are bare codes — the client already holds both name maps (`branchName`, `franchises.php`). `sales_advisor` cannot be: `WKINVREG.sales_advisor` holds a contact code (live values look like `9025295`) and `?sales_advisor=` matches that code, so the picker submits codes and displays names, resolved off ADMINISTRATOR.CONTACT exactly as `workshop_wip.php` does. The code stands in as its own label when the join misses — that advisor still has invoices. Like `filter_search`, this inherits the call's window and branch scope, so a branch-limited user is only ever offered values they can also retrieve
include_related	integer	opt	With `document_no`: also return the rest of that document's family — the original invoice and every credit raised against it, resolved via `invoice.orig_invoice_no`, across months. Ignored without `document_no`; `ro_number` never needs it
credits	integer	opt	`0` excludes credit notes; default `1` (net of credits)
counts	integer	opt	`0` skips the two document-count aggregates — `ro_count` / `invoice_count` / `credit_note_count` return `null` everywhere. Default `1`. Also how you measure what they cost: run `?debug=1` with and without
hours	integer	opt	`0` skips the ZA_WKMECHWK clocking leg — `invoice_hrs` / `hours_work` and now `labour_cost` return `0`, because cost rides the same aggregate — and `labour_gp` returns `null`, deliberately not a number: 0 cost would report a 100% margin on a call that merely declined to measure it. The flag gates the table, not just hours. `other_cost` / `other_gp` are unaffected — Other has no cost leg to skip, so nothing there goes unmeasured. Default `1`. That table is the largest in the query, so a money-only caller should not pay to aggregate it
job_top	integer	opt	Keep the N highest-value job codes; roll the rest into one `Other` row per period and job type. Ranked by absolute `total_sales`, deliberately not by name — a dealer can have ~480 distinct codes of which most are machine ids or model-specific (`8R_1500Hrs`), and that vocabulary differs per customer, so a name map would be config nobody maintains. `Unallocated` is exempt. Opt-in
min_total_sales	number	opt	Only rows with `total_sales` ≥ this
job_facets	integer	opt	`1` → `{job_codes:[…], job_types:[…]}` for the Filters modal, scoped to the same window, and nothing else. Short-circuits before the main aggregate and skips both the hours and the element-cost legs, since it selects two label columns and every aggregate column would be discarded. That makes it the cheap path for filling a picker — a 31-day window measured 930–2329 ms while it was still paying for the cost legs (2026-08-14/15), against tens of ms without them.
page / limit	integer	opt	limit blank = all rows; number = that many (else 20)
debug	integer	opt	Exactly `1` appends a `_debug` block: `wall_ms`, `query_ms_total` and per-query `ms` / `rows` / `bytes` / generated SQL. Comparing `wall_ms` against `query_ms_total` separates database time from PHP time

json

{ "meta": { "total": 54, "page": 1, "limit": "all", "total_pages": 1,
    "bucket": "month", "group_by": "job",
    "date_from": "2026-01-01", "date_to": "2026-08-07", "credits": "included" },
  "summary": {
    "row_count": 54, "document_count": 3184,
    "totals": { "labour_sales": 4820150.00, "parts_sales": 3110480.00,
      "other_sales": 204300.00, "sublet_sales": 388920.00,
      "total_sales": 8523850.00, "invoice_hrs": 28714.5000, "hours_work": 30188.2500,
      // window-level, from their own un-grouped query — ro_count does NOT sum across buckets
      "ro_count": 3021, "invoice_count": 3102, "credit_note_count": 82 },
    "by_period": [{ "period_key": "2026-01", "period_label": "Jan 2026",
      "total_sales": 1042300.00, "invoice_hrs": 3512.7500 }],
    "by_credit_flag": {
      "I": { "doc_count": 3102, "invoice_value": 8801420.00 },
      "C": { "doc_count": 82, "invoice_value": -277570.00 } },
    "reconciliation": { "wkinvreg_total": 8523850.00,
      "allocated_total": 8498120.00, "unallocated_total": 25730.00,
      "ties_to_wkinvreg": true } },
  "data": [{ "period_key": "2026-01", "period_label": "Jan 2026",
    "year": 2026, "month": 1, "day": null, "week_of_month": null,
    "job_code": "Service", "job_type": "Retail",
    "labour_sales": 312400.00, "parts_sales": 198220.00,
    "other_sales": 12100.00, "sublet_sales": 21450.00,
    "total_sales": 544170.00,
    "invoice_hrs": 1842.5000, "hours_work": 1930.7500,
    // labour_cost = ZA_WKMECHWK.cost_val; labour_gp = labour_sales - labour_cost. Both null/0 under ?hours=0
    "labour_cost": 96420.00, "labour_gp": 215980.00,
    // Other has NO cost of sales, so other_cost is always 0 and other_gp always == other_sales. NOT gated by ?hours=0
    "other_cost": 0.00, "other_gp": 12100.00,
    // sublet_cost = PURCHASE_CONTROL.order_value; sublet_gp = sublet_sales - sublet_cost. Both null/0 under ?sublet=0
        "parts_cost": 158420.00, "parts_gp": 39800.00,
    "sublet_cost": 18230.00, "sublet_gp": 3220.00, "total_gp": 271100.00,
    "doc_count": 418,
    // null here because group_by=job is LINE grain — see the note above
    "ro_count": null, "invoice_count": null, "credit_note_count": null,
    "allocated": true }] }

Parts Inventory

Reads from InMaster (Inventory Master). A single part (exact part_no) includes its last 20 movements from InTrans. The list uses the same filter vocabulary as the ageing endpoints and returns branch / franchise buckets that seed and cross-filter the pickers; ?group_by=part_no switches to per-part rows for the Part pickers. For grouped rollups, see Parts Inventory Analysis and Parts Sales Analysis.

GET parts.php ?part_no= &group_by= &branch_include= …

Modes. Single part: an exact part_no (no *, no group_by) returns the part master + recent movement. Detail (group_by=part_no): per-part rows exposing part_no, part_description (+ im.* compliance fields) for the Part pickers. Stocktake ageing (group_by=stocktake_age): a compact summary — one row per branch × franchise × age bucket (months since last stocktake: -1 never counted, 0 future, 1..24, 25 = 25+), each with n, n_onhand, val, val_onhand; returns as_at + branch/franchise + stocktake_age_summary (no raw data). Margin band (group_by=margin_band): a margin histogram, one flat row per branch × franchise × occupied band. Band code is -2 unpriced (list_price≤0), -1 loss (margin<0), else floor(gross_margin/5)*5 → 0,5,…,95,100 (margin clamped to 100). Per row: n, val (Σon_hand_val), sell (Σlist_price×on_hand_qty), cost (Σon_hand_val) — sell/cost are 0 for the unpriced band. Population is on-hand-only; turns the multi-MB raw margin pull into a <100 KB payload. Aggregate (default list): also returns top-level branch and franchise arrays over the filtered scope to seed & cross-filter the Branch/Franchise pickers. A trailing * on part_no/part_desc is a prefix search (routes to the list).

The part master is returned as-is (SELECT im.*) so every real InMaster column comes through. The low_stock / in_stock filters return once the InMaster stock columns are confirmed.

Key	Type	Required	Description
part_no	string	opt	Exact = single part + movement; trailing `*` = prefix search (list)
part_desc	string	opt	Exact, or trailing `*` for prefix search
group_by	string	opt	`part_no` (per-part rows) · `stocktake_age` (stocktake-ageing summary) · `margin_band` (margin histogram)
branch / franchise	string	opt	Exact match
branch_include	string	opt	CSV of branch codes to include
franchise_include	string	opt	CSV of franchise codes to include
part_no_include	string	opt	CSV of part numbers to include (each may end `*`)
part_desc_include	string	opt	CSV of description tokens (contains-match)
stocktake_before	date	opt	Non-compliant drill: parts last counted before `YYYY-MM-DD` or never counted
in_stock	integer	opt	`1` = only parts with on_hand_qty ≠ 0. Default = all. Applies to every mode
margin_max	number	opt	Leaf drill: on-hand priced parts with gross margin % below this (thin + loss)
include_unpriced	integer	opt	With `margin_max`, also return on-hand parts with list_price ≤ 0
bin	string	opt	Partial bin-location match
search	string	opt	part number or description
page / limit	integer	opt	limit blank = all rows; number = that many (else 20)

json — aggregate (list)

{ "meta": { "total": 8421 },
  "branch": ["01", "04"],
  "franchise": [{ "franchise": "TY", "franchise_name": "Toyota" }],
  "data": [{ "PART_NO": "90915-YZZE1", "PART_DESC": "Oil Filter",
    "BRANCH": "01", "FRANCHISE": "TY", "bin_location": "A12-4" }] }

Detail — ?group_by=part_no

json — detail

{ "meta": { "total": 3 }, "group_by": "part_no",
  "data": [{ "part_no": "90915-YZZE1", "part_description": "Oil Filter",
    "branch": "01", "franchise": "TY", "franchise_name": "Toyota" }] }

Stocktake ageing — ?group_by=stocktake_age

json — stocktake_age

{ "as_at": "2026-07-01",
  "branch": ["01", "61"],
  "franchise": [{ "franchise": "RT", "franchise_name": "Revet" }],
  "stocktake_age_summary": [{ "branch": "61", "franchise": "RT", "franchise_name": "Revet",
    "age_bucket": -1, "n": 12, "n_onhand": 3, "val": 45000.00, "val_onhand": 12000.00 },
    { "branch": "61", "franchise": "RT", "franchise_name": "Revet",
    "age_bucket": 9, "n": 40, "n_onhand": 8, "val": 88000.00, "val_onhand": 21000.00 }] }

Margin band — ?group_by=margin_band

json — margin_band

{ "as_at": "2026-07-06",
  "branch": ["61"], "franchise": [{ "franchise": "JD", "franchise_name": "John Deere" }],
  "margin_band_summary": [
    { "branch": "61", "franchise": "JD", "franchise_name": "John Deere",
      "margin_band": -2, "n": 88, "val": 9100.00, "sell": 0.00, "cost": 0.00 },
    { "branch": "61", "franchise": "JD", "franchise_name": "John Deere",
      "margin_band": -1, "n": 42, "val": 18400.00, "sell": 15900.00, "cost": 18400.00 },
    { "branch": "61", "franchise": "JD", "franchise_name": "John Deere",
      "margin_band": 15, "n": 42, "val": 183402.55, "sell": 240100.00, "cost": 183402.55 }
    /* … one row per (branch × franchise × occupied band); bands 0,5,…,95,100 */
  ] }

Parts Inventory Analysis

Groups InMaster (Inventory Master) by any column you pass — franchise (default), branch, prod_group, etc. Always returns part counts, distinct parts and back-order quantity per group. Pass ?sum= to total any numeric InMaster columns you have (e.g. on-hand qty or stock value).

GET parts_inventory_analysis.php ?group_by= &sum= …

The grouping column is validated as a plain identifier. An unknown column returns a clear error rather than affecting other data.

Key	Type	Required	Description
group_by	string	opt	Grouping column (default `FRANCHISE`) — e.g. `branch`, `prod_group`
sum	string	opt	Comma-separated numeric columns to SUM per group
branch / franchise	string	opt	Restrict to one branch / franchise
limit	integer	opt	Groups returned — blank = all, number = that many (else 20)

json

{ "group_by": "FRANCHISE",
  "groups": [{ "FRANCHISE": "TOY", "parts": 8420,
    "distinct_parts": 8011, "back_order_qty": 96.00, "branches": 3 }],
  "totals": { "groups": 6, "parts": 48210, "back_order_qty": 412.00 } }

Parts Stock Ageing Summary

On-hand value from InMaster (parts with ON_HAND_QTY <> 0), cross-tabulated by two age dimensions: DLP age (months since last purchased) and DLS age (months since last invoiced / sold). Bands: 0-1, 2-12, 13-24, 25-36, 37-48, 48+; DLS also has No Sale for parts never sold. Each cell carries on_hand_val and on_hand_ex_wip (value excluding WIP).

GET parts_stock_ageing.php ?wip= &branch_exclude= …

Summary cross-tab of on-hand value by DLP × DLS age band, plus a grand total. Each cell carries on_hand_val and on_hand_ex_wip (on-hand value less WIP). Cells are returned in chronological band order. The response also lists the distinct branch codes and franchise (code + name) in scope — handy for populating filter dropdowns; both reflect the active filters.

Key	Type	Required	Description
wip	integer	opt	`1` = only parts with WIP_QTY <> 0
franchise	string	opt	Restrict to one franchise code
franchise_include	string	opt	Comma list of franchise codes to include
part_no	string	opt	Restrict to one part — exact, or a trailing `` for prefix search (e.g. `90915`)
part_no_include	string	opt	Comma list of part numbers to include — each may end in `*` for prefix
part_desc_include	string	opt	Comma list of description terms — matches descriptions containing any term
branch_include	string	opt	Comma list of branch codes to include
branch_exclude	string	opt	Comma list of branch codes to exclude

json

{ "total_on_hand_val": 4128500.00, "total_on_hand_ex_wip": 4061250.00,
  "bands": ["0-1", "2-12", "13-24", "25-36", "37-48", "48+", "No Sale"],
  "branch": ["01", "02", "07"],
  "franchise": [{ "franchise": "TY", "franchise_name": "Toyota" },
    { "franchise": "NS", "franchise_name": "Nissan" }],
  "data": [{ "dlp_age": "0-1", "dls_age": "0-1", "on_hand_val": 512300.00, "on_hand_ex_wip": 509100.00 },
    { "dlp_age": "48+", "dls_age": "No Sale", "on_hand_val": 88010.00, "on_hand_ex_wip": 88010.00 }] }

Parts Stock Ageing Detail

The drill-down behind Parts Stock Ageing Summary: the individual on-hand parts, with per-line cost detail (unit_cost = on_hand_val ÷ on_hand_qty, wip_val = unit_cost × wip_qty, on_hand_ex_wip = on_hand_val − wip_val) plus the franchise name. dlp_age/dls_age are optional filters — pass both to pin one cell of the cross-tab, one to slice a single age dimension, or neither to list all parts. Add ?group_by=branch|franchise|part_no to switch to a roll-up: one aggregated row per group instead of per part, each carrying three age breakdowns of its own parts — by_dlp (across DLP bands), by_dls (across DLS bands) and by_cell (the full DLP×DLS crossed grid).

GET parts_stock_ageing_detail.php ?risk_parts=1 &group_by= &dlp_age= &part_no= …

Four modes. Risk-parts mode (?risk_parts=1, takes precedence): the Pulse Parts Risk feed's part-grain facts only — the dead-value Tukey fence, the flagged part list, the two counts and the worst 25 (see the param table). A few KB, replacing a 96 MB / 48 s group_by=part_no pull. Risk-lines mode (?risk_lines=): the SKU lines behind one Risk metric — three fields per part instead of the whole band grid (that drill was 73.2 MB / 36.3 s for one franchise). Part mode (default): the individual on-hand parts, with cell_totals. dlp_age/dls_age are optional here — pass both to pin one cell, one to slice an age dimension, or neither to list all parts (narrow with the franchise/branch/part filters). Group mode (?group_by=): one aggregated row per branch, franchise or part_no, each with by_dlp, by_dls and by_cell (the full DLP×DLS grid) breakdowns of its own parts, plus grand totals — bands optional. group_by=part_no rows also include part_description, and group_by=franchise rows include franchise_name. The three breakdowns are slices of the same parts (each sums to the group total; the grid's cells sum along each axis to by_dlp / by_dls). An invalid band or group_by returns 400 with the valid list.

Key	Type	Required	Description
risk_parts	integer	opt	`1` = Parts Risk feed mode (takes precedence over `group_by`). Returns only the part-grain facts that feed lights the Risk tab: `meta.dead_threshold` (the upper Tukey fence over each part's dead value — the `No Sale` DLS band's on-hand value, floor R50 000, computed in PHP as a byte-for-byte port of the client's `tukeyFence`), `meta.part_count` (every part group — the "across N parts" population), `meta.dead_part_count`, `flagged_parts[]` (the part numbers at/above the fence = the drill flame index) and `top_dead[]` (the worst 25 with description + value). Replaces an unpaginated `?group_by=part_no` call that returned 96 MB in 48.3 s — the largest call in the platform — of which the client used exactly those four facts.
risk_lines	string	opt	`dead` · `obsolete` · `fresh` · `wip_aged` — the SKU lines behind one Risk metric (also takes precedence over `group_by`): `part_no`, `part_description` and that metric's `val`, non-zero only, worst-first. What the Risk tab's "Dead-stock lines" / "Obsolete lines" drill renders. Metric definitions are shared with `risk_parts`, so a drill agrees with the fence above it. Previously served by `?group_by=part_no` scoped to the drilled franchise — John Deere alone was 73.2 MB / 36.3 s to read one number per part. Unknown value → `400`.
group_by	string	opt	`branch` · `franchise` · `part_no` — switches to the roll-up (group) mode. Never call `part_no` unpaginated or unfiltered: every row carries the full DLP×DLS grid plus both margins, so the payload scales at roughly 2.4 KB per part. Use `?risk_parts=1` for the Risk feed, or narrow with `branch_include`/`franchise_include`/`page`.
dlp_age	string	opt	0-1 · 2-12 · 13-24 · 25-36 · 37-48 · 48+ filter — both pin a cell, one slices
dls_age	string	opt	…same, plus `No Sale` filter — both pin a cell, one slices
franchise	string	opt	Restrict to one franchise code
franchise_include	string	opt	Comma list of franchise codes to include
part_no	string	opt	Restrict to one part — exact, or a trailing `` for prefix search (e.g. `90915`)
part_desc	string	opt	Restrict by description — exact, or a trailing `` for prefix search (e.g. `BRAKE`)
part_no_include	string	opt	Comma list of part numbers to include — each may end in `*` for prefix
part_desc_include	string	opt	Comma list of description terms — matches descriptions containing any term
branch_include	string	opt	Comma list of branch codes to include
branch_exclude	string	opt	Comma list of branch codes to exclude
page / limit	integer	opt	limit blank = all rows; number = that many (else 20)

json — part mode

{ "meta": { "total": 214 }, "mode": "part",
  "cell": { "dlp_age": "2-12", "dls_age": "No Sale" },
  "cell_totals": { "parts": 214, "on_hand_val": 186400.00, "on_hand_ex_wip": 183010.00 },
  "data": [{ "part_no": "90915-YZZE1", "franchise": "TY", "franchise_name": "Toyota",
    "unit_cost": 43.20, "on_hand_qty": 12, "on_hand_val": 518.40, "wip_val": 0.00 }] }

Group mode — ?group_by=branch

json — group mode

{ "meta": { "total": 6 }, "mode": "group", "group_by": "branch",
  "cell": { "dlp_age": null, "dls_age": null },
  "totals": { "groups": 6, "parts": 5840, "on_hand_val": 2841900.00, "on_hand_ex_wip": 2790110.00 },
  "data": [{ "branch": "01", "parts": 1820, "on_hand_qty": 9410.00,
    "on_hand_val": 980450.00, "wip_val": 12300.00, "on_hand_ex_wip": 968150.00,
    "by_dlp": [{ "age": "0-1", "parts": 240, "on_hand_val": 120300.00 },
              { "age": "2-12", "parts": 910, "on_hand_val": 540150.00 },
              { "age": "48+", "parts": 670, "on_hand_val": 320000.00 }],
    "by_dls": [{ "age": "2-12", "parts": 1100, "on_hand_val": 610200.00 },
              { "age": "No Sale", "parts": 720, "on_hand_val": 370250.00 }],
    "by_cell": [{ "dlp_age": "0-1", "dls_age": "2-12", "parts": 180, "on_hand_val": 90200.00 },
               { "dlp_age": "2-12", "dls_age": "No Sale", "parts": 410, "on_hand_val": 220050.00 },
               { "dlp_age": "48+", "dls_age": "No Sale", "parts": 310, "on_hand_val": 150000.00 }] }] }

Parts Sales

Lists InTrans (Inventory Sales) — the parts sales / movement ledger. Each row is a sale line with quantity, sale value, tax and the list price it sold against. For grouped totals, see Parts Sales Analysis.

GET parts_sales.php ?part_no= &branch= &date_from= …

Paginated sales lines with page totals (qty, sale value, tax).

Key	Type	Required	Description
part_no	string	opt	Exact part number
branch / franchise	string	opt	Exact match
salesman	string	opt	Salesman code
sale_class / trade_type	string	opt	Exact match
acc_no	integer	opt	Bill-to account
ref_no	string	opt	Document / order reference
date_from / date_to	date	opt	`Trans_Datetime` range
search	string	opt	part no, description, ref, salesman
page / limit	integer	opt	limit blank = all rows; number = that many (else 20)

json

{ "meta": { "total": 28940 },
  "page_totals": { "qty": 312.00, "sale_value": 48250.00 },
  "data": [{ "PART_NO": "90915-YZZE1", "qty": 2,
    "sale_value": 258.00, "branch": "01", "franchise": "TOY" }] }

Parts Sales Analysis

Groups InTrans (Inventory Sales) by any column you pass — franchise (default), branch, PART_NO (top sellers), Salesman, SALE_CLASS, etc. Each group carries lines, qty, sale value, tax, list value and gross discount; ordered by sale value.

GET parts_sales_analysis.php ?group_by= &date_from= &limit= …

Discount is list value (LIST_PRICE × QTY) less sale value — a gross discount, not margin. group_by=PART_NO&limit=50 gives the top 50 selling parts.

Key	Type	Required	Description
group_by	string	opt	Grouping column (default `FRANCHISE`) — e.g. `branch`, `PART_NO`, `Salesman`
date_from / date_to	date	opt	`Trans_Datetime` range
branch / franchise	string	opt	Exact match
trade_type / sale_class	string	opt	Exact match
salesman	string	opt	Salesman code
limit	integer	opt	Groups returned — blank = all, number = that many (else 20)

json

{ "group_by": "FRANCHISE",
  "groups": [{ "FRANCHISE": "TOY", "lines": 4821, "qty": 15230.00,
    "sale_value": 1875400.00, "discount_value": 210600.00, "discount_pct": 10.10 }],
  "totals": { "groups": 6, "sale_value": 4210880.00 } }

Parts Analytics

A combined inventory + sales overview across InMaster (stock catalogue) and InTrans (sales movement). Every response carries an inventory summary (parts, distinct parts, back-order qty, branch/franchise counts), a sales summary (lines, qty, sale value, tax, list value, gross discount) and a groups breakdown for the chosen dimension. Discount is list value (LIST_PRICE × QTY) less sale value — a gross discount, not margin; stock valuation and true margin are omitted until the InMaster cost columns are confirmed.

GET parts_analytics.php ?group_by= &date_from= &branch= …

group_by chooses the sales breakdown dimension: franchise (default), branch, salesman, sale_class or trade_type give one group per code; part gives top parts by sale value (bounded by limit); month gives a monthly trend. When grouping by franchise or branch, each group also carries its catalogue counts.

Key	Type	Required	Description
group_by	string	opt	Sales dimension (default `franchise`) — `branch`, `salesman`, `part`, `month`, `sale_class`, `trade_type`
date_from / date_to	date	opt	`Trans_Datetime` range (sales side)
branch / franchise	string	opt	Restrict both tables to one branch / franchise
trade_type / sale_class	string	opt	Exact match (sales side)
limit	integer	opt	Rows for `group_by=part` — blank = all, number = that many (else 20)

json

{ "group_by": "franchise",
  "inventory": { "parts": 16816, "distinct_parts": 15920, "back_order_qty": 418.00, "branches": 7, "franchises": 12 },
  "sales": { "lines": 28450, "qty": 92300.00, "sale_value": 4210880.00,
    "sale_tax": 631632.00, "list_value": 4684300.00, "discount_value": 473420.00, "discount_pct": 10.11 },
  "groups": [{ "franchise": "TOY", "sales": { "lines": 4821, "sale_value": 1875400.00 },
    "parts": 5210, "distinct_parts": 4980, "back_order_qty": 120.00 }] }

Parts Sales Orders

Customer parts sales orders — the orders customers place to buy parts from the dealership. Reads the order header (insalord, one row per order) joined to the order line detail (insalpar, one row per part) on file_no. The list returns headers with per-order line counts, quantity roll-ups and a derived status; ?file_no= returns one order with its header and every line.

GET parts_sales_orders.php ?file_no= &customer_no= &open= &part_no= …

Paginated order list, newest first. Each header carries line_count, total_order_qty, total_supplied_qty, total_outstanding_qty, an order_total (line value + freight) and a status of open / part_supplied / supplied. Pass ?file_no= for one order with header + lines; add &lines_only=1 for just the lines.

Key	Type	Required	Description
file_no	integer	opt	Single order — returns header + all lines + totals
lines_only	integer	opt	`1` = with `file_no`, return only the line detail
branch	string	opt	Originating branch code
customer_no	string	opt	Customer account / number
salesman	string	opt	Salesman code (partial match)
cust_ord_no	string	opt	Customer's own order reference (partial)
type	string	opt	Order TYPE code
pay_method	string	opt	Payment method
part_no	string	opt	Only orders containing this part
open	integer	opt	`1` = orders with parts still to supply
fully_supplied	integer	opt	`1` = orders with nothing left to supply
date_from / date_to	date	opt	`ord_date` range — `YYYY-MM-DD`
search	string	opt	cust_ord_no, customer_no, salesman, special_inst
page / limit	integer	opt	limit blank = all rows; number = that many (else 20)

json

{ "meta": { "total": 1248 },
  "data": [{ "file_no": 120345, "branch": "JHB01", "cust_ord_no": "PO-7788",
    "customer_no": "1001", "salesman": "DV", "ord_date": "2026-06-20 10:14:00",
    "line_count": 3, "total_order_qty": 9.00, "total_supplied_qty": 4.00,
    "total_outstanding_qty": 5.00, "order_total": 1289.45, "status": "part_supplied" }] }

Single order — ?file_no=120345

json

{ "header": { "file_no": 120345, "branch": "JHB01", "customer_no": "1001",
    "cust_ord_no": "PO-7788", "freight": 120.00, "pay_method": "ACCOUNT" },
  "totals": { "order_qty": 9.00, "supplied_qty": 4.00, "line_value": 1169.45,
    "freight": 120.00, "order_total": 1289.45 },
  "lines": [{ "line_no": 1, "part_no": "90915-YZZE1", "order_qty": 4.00,
    "supplied_qty": 4.00, "unit_price": 129.95, "line_value": 519.80,
    "outstanding_qty": 0.00, "branch": "JHB01", "franchise": "TY" }] }

Parts Audit

Parts-related entries from the application audit log (uaudit). Each row is one logged action — who (username, win_username, win_pcname), when (ad_timestamp), what (ad_type), the record touched (identity1), and before/after values (old_value / new_value). ad_type is whitelisted to parts events only — currently insalpar_delete (a parts sales-order line deletion); the response lists the recognised types under ad_types.

GET parts_audit.php ?ad_type= &username= &date_from= …

Paginated parts audit log, newest first. Only rows whose ad_type is whitelisted are returned. Pass ?adid= for a single record. ?ad_type= accepts only a whitelisted value (else 400 with the allowed list).

Key	Type	Required	Description
adid	integer	opt	Single audit record by id
ad_type	string	opt	One whitelisted type — currently `insalpar_delete`
username	string	opt	Auto-IT user who performed the action (partial)
session_id	integer	opt	Exact session id
connection_id	string	opt	Exact connection id
identity1	string	opt	Record identity the entry refers to (partial)
search	string	opt	username, int_window, identity1, old/new value, other
date_from / date_to	date	opt	`ad_timestamp` range — `YYYY-MM-DD`
page / limit	integer	opt	limit blank = all rows; number = that many (else 20)

json

{ "meta": { "total": 37 },
  "ad_types": ["insalpar_delete"],
  "data": [{ "adid": 90412, "ad_timestamp": "2026-06-22 14:08:31", "ad_type": "insalpar_delete",
    "username": "DVANROOYEN", "session_id": 2241, "win_pcname": "PARTS-PC-03",
    "identity1": "120345/2", "old_value": "part 90915-YZZE1 qty 4", "new_value": null }] }

Parts Stock Adjustment

Parts stock adjustments from InTrans and the InTrans_Old archive — rows where TYPE = 'A' with non-zero qty, classified In / Out by the sign of quantity. Stocktake entries are included by default; pass exclude_stocktakes=1 to drop them. Joined to InMaster for the description and contact for the adjusting salesman. Returns a summary (in/out counts, qty and cost) plus the detail lines.

GET stock_adjustments.php ?date_from= &branch= &direction= …

The live and archive tables are unioned by default; pass live_only=1 for InTrans alone. Cost is cost_val; resulting_qty / resulting_value are the on-hand position after each adjustment.

Key	Type	Required	Description
date_from / date_to	date	opt	`trans_datetime` range
branch / franchise	string	opt	Exact match
part_no	string	opt	Part number — exact, or a trailing `` for prefix search (e.g. `90915`)
direction	string	opt	`in` (qty > 0) or `out` (qty < 0)
salesman	string	opt	Salesman / contact code
search	string	opt	part number or description
exclude_stocktakes	integer	opt	`1` = drop rows whose description contains STOCKTAKE
live_only	integer	opt	`1` = skip the InTrans_Old archive
page / limit	integer	opt	limit blank = all rows; number = that many (else 20)

json

{ "meta": { "total": 1840 },
  "summary": { "in_count": 1120, "out_count": 720,
    "net_qty": 240.00, "net_cost": 18420.50 },
  "data": [{ "part_no": "90915-YZZE1", "part_description": "Oil Filter",
    "description": "STOCKTAKE VARIANCE", "qty": -2,
    "cost_value": 86.40, "adjustment_type": "Stock Adjustments Out",
    "salesman_name": "Jan Smit", "resulting_qty": 40 }] }

part_description is the part's master description (InMaster); description is the transaction's own description from the adjustment row.

Parts Stock Adjustment Analysis

Aggregates the same adjustment set as Parts Stock Adjustment (InTrans + InTrans_Old, TYPE='A', non-zero qty) into groups by branch, franchise or part_no. Each group carries in/out line counts, in/out and net qty, and in/out and net cost. group_by=franchise adds franchise_name; group_by=part_no adds part_description. Pass ?include_buckets=1 to also get the distinct branch codes and franchise (code + name) that have adjustments in scope — for filter dropdowns. Buckets are off by default (each scans the full union), so omit the parameter for best speed.

GET stock_adjustments_analysis.php ?group_by= &date_from= &branch= …

Grouped adjustment totals. group_by is restricted to branch, franchise or part_no (default franchise). All the Parts Stock Adjustment filters apply.

Key	Type	Required	Description
group_by	string	opt	`branch` · `franchise` · `part_no` (default franchise)
date_from / date_to	string	opt	trans_datetime range, YYYY-MM-DD
branch / franchise / part_no	string	opt	Restrict the set (part_no exact, or trailing `*` for prefix)
franchise_include	string	opt	Comma list of franchise codes to include
part_no_include	string	opt	Comma list of part numbers to include — each may end in `*` for prefix
part_desc_include	string	opt	Comma list of description terms — matches descriptions containing any term
branch_include	string	opt	Comma list of branch codes to include
branch_exclude	string	opt	Comma list of branch codes to exclude
direction	string	opt	`in` (qty > 0) · `out` (qty < 0)
exclude_stocktakes	integer	opt	`1` = drop STOCKTAKE rows
live_only	integer	opt	`1` = skip InTrans_Old archive
include_buckets	integer	opt	`1` = also return distinct `branch`/`franchise` in scope (off by default — slower)
limit	integer	opt	Groups returned — blank = all, number = that many (else 20)

json — with ?include_buckets=1

{ "group_by": "franchise",
  // branch[] and franchise[] only present when ?include_buckets=1
  "branch": ["01", "04"],
  "franchise": [{ "franchise": "TY", "franchise_name": "Toyota" },
    { "franchise": "BO", "franchise_name": "Bosch" }],
  "totals": { "groups": 6, "lines": 1840, "net_qty": -312.00, "net_cost": -48230.10 },
  "groups": [{ "franchise": "TY", "franchise_name": "Toyota", "lines": 920,
    "in_count": 540, "out_count": 380, "net_qty": -120.00, "net_cost": -19440.00 }] }

Analytics Snapshot

One call returning a management snapshot: period sales by module, AR aging totals, headline counts, vehicle stock by status, top customers and recent invoices.

GET dashboard.php ?date_from= &date_to=

Defaults to the current month if no dates are supplied. All figures derive from tables the platform already reads.

Key	Type	Required	Description
date_from	date	opt	Period start · default: 1st of month
date_to	date	opt	Period end · default: today

json

{ "period": { "date_from": "2026-06-01", "date_to": "2026-06-19" },
  "sales_by_module": [{ "module_type": "V", "sales_incl_tax": 2840500.00 }],
  "receivables_aging": { "total_outstanding": 1284300.00 },
  "counts": { "total_customers": 842, "invoices_in_period": 317 } }

Ensure Indexes

Admin maintenance endpoint. Holds a curated registry of the indexes the read endpoints benefit from, checks each against the live SQL Anywhere 17 catalog by column set (a vendor index under another name is respected, never duplicated), and creates only the ones not already covered. DDL is isolated here so read endpoints stay SELECT-only.

Dry-run is the default — nothing is created unless ?apply=1 is passed. Requires a WRITEKEY header in addition to the read keys. CREATE INDEX takes schema locks, so run it off the request path, once per dealer.

POST ensure_indexes.php ?apply=1 (default: dry-run)

Reports which curated indexes are missing (dry-run), or creates them with ?apply=1. Each registry entry is validated, checked against the catalog, and handled independently — one failure is reported without aborting the rest. Index names are namespaced AITONE_ so what the endpoint added is trackable.

Key	Type	Required	Description
apply	integer	opt	`1` = create missing indexes. Omitted = dry-run (report only)
WRITEKEY	header	req	Write authorization header (plus the usual AUTHKEY / APIKEY)

Always dry-run first and review the indexes[] report — entries with unconfirmed column names surface as error rather than being created.

json — dry-run

{ "status": "ok", "applied": false,
  "summary": { "registry_entries": 10, "created": 0, "already_present": 7, "errors": 0 },
  "indexes": [{ "index": "AITONE_INMASTER_BR_FR_ONHAND", "table": "InMaster",
    "status": "would_create", "ddl": "CREATE INDEX AITONE_INMASTER_BR_FR_ONHAND ON InMaster (branch, franchise, on_hand_qty, on_hand_val, list_price)" },
    { "index": "AITONE_INMASTER_BR_FR_PART", "status": "exists", "covered_by": "inmaster_pkey" }] }

Write Operations

Mutating endpoints. Each requires the standard read keys plus a WRITEKEY header, accepts a JSON body, binds every value as a prepared-statement parameter, and supports ?dry_run=1 to preview without committing.

Writes are limited to CRM/contact data. The financial ledger and inventory quantities are not writable here by design.

PATCH customer_contact.php { acc_no, …fields }

Updates whitelisted contact fields for the account. Supply acc_no plus any subset of editable fields. Anything not whitelisted is ignored.

Field	Type	Required	Description
acc_no	integer	req	Debtor account to update
email_address	string	opt	Validated as an email
bus_phone / mob_phone / fax_no	string	opt	Phone fields
street, city, state, pcode, country	string	opt	Billing address
postal_*	string	opt	Postal address fields
title, name, surname, company_name	string	opt	Identity fields

Add ?dry_run=1 to return the exact SQL + bound params without writing.

json

{ "status": "ok", "acc_no": 1001,
  "fields_updated": ["email_address", "city"],
  "rows_affected": 1 }

POST customer_note.php { acc_no, note, mode }

Appends a timestamped note (default) or replaces the note entirely.

Field	Type	Required	Description
acc_no	integer	req	Debtor account
note	string	req	Note text
mode	string	opt	`append` (default) or `replace`

json

{ "status": "ok", "acc_no": 1001, "mode": "append",
  "rows_affected": 1 }

Auto-IT One · Auto-IT South Africa · API v3.7 · Read + Write