All salesman data lives in the trader schema on the same MariaDB server as
the Axion platform. No ETL or import — queries run directly against trader.
trader.customers366k customer records. ~3,819 active accounts have available credit at any time.
| Column | Type | Description |
|---|---|---|
DB_ACCOUNTCODE |
char(50) | Primary key (e.g. 215-208013) |
DB_NAME |
char | Customer name |
DB_MOBILE |
char | Mobile number |
DB_TELEPHONE1 |
char | Landline |
DB_EMAILADDRESS |
char | |
DB_STATUS_ID |
smallint | 1 = Active, other values = inactive/blocked/closed |
DB_BALANCE |
double | Outstanding balance (can be negative = credit balance) |
DB_CREDITLIMIT |
double | Approved credit limit |
DB_AGE0 |
double | Current (not yet due) |
DB_AGE30 |
double | 30 days overdue |
DB_AGE60 |
double | 60 days overdue |
DB_AGE90 |
double | 90 days overdue |
DB_AGE120 |
double | 120 days overdue |
DB_LASTPURCHASEDATE |
char | Last purchase date — stored as DD.MM.YYYY string |
DB_LASTPAYMENTDATE |
char | Last payment date — stored as DD.MM.YYYY string |
DB_PURCHASE_VALUE_MTD |
double | Purchase value month-to-date |
DB_PURCHASE_VALUE_YTD |
double | Purchase value year-to-date |
DB_STORE_ID |
smallint | Home store / branch |
DB_ACCOUNT_MANAGER |
smallint | FK → account_managers.AM_ID |
DB_DEFAULT_REP |
smallint | Sales rep ID (mostly 0 — not widely used) |
DB_LASTPURCHASEDATE and DB_LASTPAYMENTDATE are stored as DD.MM.YYYY strings.
Convert for date arithmetic:
STR_TO_DATE(DB_LASTPURCHASEDATE, '%d.%m.%Y')
DB_CREDITLIMIT - DB_BALANCE AS available_credit
A positive value means the customer can still spend. Negative DB_BALANCE means
they are in credit (overpaid).
WHERE DB_STATUS_ID = 1
AND DB_CREDITLIMIT > 0
AND DB_CREDITLIMIT > DB_BALANCE
| Table | Purpose |
|---|---|
trader.account_managers |
Account manager master (AM_ID, AM_NAME). Only 1 record ("HO CREDIT MANAGER"). |
trader.buyers |
Salesperson master (BY_ID, BY_NAME, BY_STORE_ID). BY_ID is unique per person across all stores. Join on CAST(BY_ID AS CHAR) = nc.sales_rep. |
trader.products |
Product master (PM_PRODUCTCODE, PM_DESCRIPTION, PM_STDSELLINGPRICE). |
trader.meta_product |
Web/PIMS product enrichment — images, web pricing, categories. |
trader.product_ledger |
Stock levels per store (PL_QTYONHAND, PL_QTYONCUSTOMERORDER). |
trader.avgsales |
Average product sales (AS_PRODUCTCODE, AS_SALES, AS_90DAYS). |
trader.promotion_header |
Promotion definitions (active flag, date range). |
trader.promotion_detail |
Per-product promotion pricing (PR_PRICE, META_INSTALMENT). |
The salesman is stored as a numeric string sales_rep in data_uninewcontracts. Join to buyers on ID only (store suffix in account code ≠ BY_STORE_ID):
SELECT b.BY_NAME
FROM trader.data_uninewcontracts nc
JOIN trader.buyers b ON CAST(b.BY_ID AS CHAR) = nc.sales_rep
WHERE nc.Accountcode = '{account}'
ORDER BY nc.Meta_InsertDate DESC
LIMIT 1
data_uninewcontracts is a one-time 2019 snapshot (all records have Meta_InsertDate = 2019-10-03).
It covers only ~178 of ~3,819 active credit accounts — UniCredit outsourced collections portfolio.
customer_payment_header (TH_TYPE=10 = HP invoices) contains 42k records covering ~3,166 active
credit accounts (83%), but only through 31.12.2022. No product names — invoice total only.
TH_USER_ID is the POS workstation user, not the salesman. Joins to buyers only for TH_USER_IDs 1–18
(SHERRY JOUBERT, RONEL SYFERT, and ~15 HO phone-in staff). Covers ~979 active credit accounts.
Post-2022 purchase detail (product names, salesman for recent purchases) is available in
trader.sales_transaction_header (live, updated to current). Join via TH_USER_ID + TH_STORE_ID
to trader.users (US_ID, US_USERNAME, US_NAME, US_STORE_ID).
Salesman strategy (COALESCE, best-effort):
data_uninewcontracts → buyers via sales_rep (covers ~178 accounts, has product-linked salesman)customer_payment_header → buyers via TH_USER_ID + TH_STORE_ID (covers ~979 accounts, through 2022)sales_transaction_header → users via TH_USER_ID + TH_STORE_ID (live, 2023–present)BENNICK LAKAY — top salesman by value in 2026 (R74,837 across 6 contracts).
| Field | Value |
|---|---|
US_ID |
52 |
US_USERNAME |
BENNICK |
US_NAME |
BENNICK LAKAY |
US_STORE_ID |
208 |
| Role | Branch Manager |
Set as default contact_id on the SALESWATCH conversation using the dynamic ContactIdSql
(automatically picks the top active salesman for the current year — no hardcoding needed):
UPDATE def_conversation
SET ContactIdSql = '
SELECT u.US_USERNAME
FROM trader.sales_transaction_header th
JOIN trader.users u
ON u.US_ID = th.TH_USER_ID
AND u.US_STORE_ID = th.TH_STORE_ID
WHERE th.META_TH_TRANSACTION_DATE >= DATE_FORMAT(NOW(), ''%Y-01-01'')
AND th.TH_TYPE = 1
AND th.TH_STATUS = 0
AND u.US_NAME NOT LIKE ''%LEFT%''
AND u.US_STATUS = 0
GROUP BY u.US_ID, u.US_USERNAME, u.US_NAME
ORDER BY SUM(th.TH_TRANSACTION_TOTAL) DESC
LIMIT 1
'
WHERE ConversationCode = 'SALESWATCH';
The static Userid field is a fallback if ContactIdSql is not set. Set it too for safety:
UPDATE def_conversation SET Userid = 'BENNICK' WHERE ConversationCode = 'SALESWATCH';
Note: RONEL SYFERT (previously top by data_uninewcontracts volume) is no longer at the company.
OptionOverrideSql)No per-salesman filtering — DB_DEFAULT_REP is 0 for ~99% of customers.
The contact list is a shared priority queue ordered by opportunity size.
Priority logic: largest available credit first, then longest since last purchase.
SELECT
CONCAT(c.DB_NAME,
' | Avail: R', FORMAT(c.DB_CREDITLIMIT - c.DB_BALANCE, 0),
' | Last: ', c.DB_LASTPURCHASEDATE) AS Option,
c.DB_ACCOUNTCODE AS OptionCallback,
'Y' AS OptionDisplay
FROM trader.customers c
WHERE c.DB_STATUS_ID = 1
AND c.DB_CREDITLIMIT > 0
AND c.DB_CREDITLIMIT > c.DB_BALANCE
AND c.DB_MOBILE != ''
ORDER BY (c.DB_CREDITLIMIT - c.DB_BALANCE) DESC,
STR_TO_DATE(c.DB_LASTPURCHASEDATE, '%d.%m.%Y') ASC
LIMIT $offset$, $pagelength$
Filter DB_MOBILE != '' — only show customers we can actually call.
HeaderReport query)Receives Param1 = DB_ACCOUNTCODE (selected from contact list):
SELECT
c.DB_NAME,
c.DB_MOBILE,
c.DB_TELEPHONE1,
c.DB_EMAILADDRESS,
c.DB_BALANCE,
c.DB_CREDITLIMIT,
ROUND(c.DB_CREDITLIMIT - c.DB_BALANCE, 2) AS AvailableCredit,
c.DB_AGE0,
c.DB_AGE30,
c.DB_AGE60,
c.DB_AGE90,
c.DB_LASTPURCHASEDATE,
c.DB_LASTPAYMENTDATE,
c.DB_PURCHASE_VALUE_MTD,
c.DB_PURCHASE_VALUE_YTD
FROM trader.customers c
WHERE c.DB_ACCOUNTCODE = '{param1}'
HeaderReport2 query)Source table: trader.customer_payment_header (TH_TYPE=10)
Covers ~3,166 of ~3,819 active credit accounts (83% coverage). Data runs from 2011 through 2022.
No product names available — only invoice date and total amount.
SELECT
h.TH_TRANSACTION_DATE AS Date,
CONCAT('R', FORMAT(h.TH_TRANSACTION_TOTAL, 0)) AS Amount
FROM trader.customer_payment_header h
WHERE h.TH_ACCOUNTCODE = '{param1}'
AND h.TH_TYPE = 10
ORDER BY STR_TO_DATE(h.TH_TRANSACTION_DATE, '%d.%m.%Y') DESC
LIMIT 3
TH_TRANSACTION_DATE format: DD.MM.YYYY — use STR_TO_DATE(..., '%d.%m.%Y') for ordering.
Report: saleswatch_client_contracts — returns 3 most recent HP invoices ordered by date DESC.
DB_STATUS_ID = 1 is the active status. All other values indicate some form ofDD.MM.YYYY strings — always use STR_TO_DATE() for comparisons.DB_BALANCE can be negative (customer is in credit). Available credit isDB_CREDITLIMIT - DB_BALANCE, not just DB_CREDITLIMIT - ABS(DB_BALANCE).trader. All salesmen share thetrader schema is a live POS/ERP system — data is current, not a nightly dump.