Trader is a Firebird-based retail credit management system. Axion connects to it
via a MariaDB mirror (trader schema on the primary DB) that is populated by an
ETL process importing Trader's .FDB file.
| Table | Purpose |
|---|---|
trader.meta_statement_rolling |
One row per account — all client details, balances, address, branch, and account summary fields pre-joined |
trader.meta_statement_email |
Email dispatch list; Account + Email columns; joined to meta_statement for sending |
trader.meta_statement |
Master statement flag table; STATFLG = 'Y' marks accounts eligible for statement delivery |
trader.meta_template |
HTML email templates keyed by templatetype (e.g. 'SETTLEMENT') |
trader.meta_transactions_rolling |
Per-contract transaction history; axionfilename = 'update' rows are payment updates |
trader.meta_tractions_rolling |
Payment amount detail (note: typo in source — missing 's' in "transactions") |
trader.colour_grid |
7 colour categories: UNIFORMS, SHOES, FLAVOURS, LOUNGE SUITES, FURNITURE, APPLIANCES, BEDDING |
trader.colour_types |
110 master colour definitions (CL_GRID_ID → colour_grid, CL_CODE, CL_DESCRIPTION) |
trader.product_colour |
543 per-product colour assignments (PXC_PRODUCTCODE → PXC_COLOURCODE + PXC_COLOUR_DESCRIPTION) |
Three tables form the colour hierarchy:
colour_grid — categories (Furniture, Lounge Suites, Appliances, Bedding, etc.)colour_types — colour names per category, linked by CL_GRID_IDproduct_colour — which colours a specific product comes inUsed by ObjServiceFHShopify._load_known_colours() to build a filter
set that distinguishes colours from brands in product title suffixes
(e.g., "Legend Chest Combo - OAK" → OAK is a colour, not a brand).
SELECT CL_DESCRIPTION
FROM trader.colour_types
WHERE CL_GRID_ID = (
SELECT CG_ID FROM trader.colour_grid
WHERE CG_DESCRIPTION = 'FURNITURE'
)
The single source of truth for client information is trader.meta_statement_rolling,
filtered by ACCOUNT:
SELECT
Account, Contract,
Title, Inits, Surname,
Address_line_1, Address_line_2, Address_line_3, Address_line_4, Postal_code,
Cell_number,
balance, settlement, instalment, arrears, nowdue, totaldue,
points_earned, unredeemed_voucher_amounts,
statdate, duedate, prev_statdate,
branch, br_add, br_tel, br_email_address, company_website,
Easypay, Payment_nowdue, Payment_instalment
FROM `trader`.meta_statement_rolling
WHERE ACCOUNT = '{account_number}'
sql_read_object() maps each column to self._ColumnName automatically
(e.g. Surname → self._Surname).
The stage channel insert that triggers statement email delivery:
INSERT INTO stage_channel (CHANNEL, Email, Guid, Param1)
SELECT 'Fh_statement', M.Email, Uuid(), S.ACCOUNT
FROM trader.meta_statement_email AS M
LEFT JOIN trader.meta_statement AS S ON M.Account = S.Account
WHERE S.STATFLG = 'Y'
GROUP BY S.Account
Param1 is the account number — passed as Param1 to ObjReportFHpaymentsettlement.
Settlement value is NOT taken directly from meta_statement_rolling.settle.
It is recomputed at render time via self.ComputeSettle(Contract) using the
{fhsettlement:CONTRACT} process_text token.
For each contract under an account, the report queries meta_statement_rolling
again (by account) and meta_transactions_rolling (by contract) to get:
max(date) WHERE axionfilename = 'update'meta_tractions_rolling (typo table) by date + contractsum(amount) WHERE axionfilename = 'update'Client = f"<strong>{Title} {Inits} {Surname}</strong><br>"
# Address_line_1..4 appended if non-empty
Client += Postal_code + "<br>"
All fields are .title() cased except Inits which is .upper().
Statement barcode format: Code 128, value ] + account number (10 chars max).
Rendered via ObjBarcode.Barcode and served as a PNG from the Axion site.
meta_statement Table (Axion-writable)This is NOT a raw Trader mirror — it is the Axion-managed table that stores
pre-computed payment links and statement eligibility flags.
| Column | Description |
|---|---|
Account |
FK to meta_statement_rolling.Account |
AxionGuid |
Unique row identifier (UUID) |
Contract |
Contract number |
STATFLG |
'Y' = eligible for statement delivery |
StatementRun |
Period string (e.g. '2020-06') used to batch-filter payment link generation |
EasyPay |
Rendered EasyPay reference link (via {easypay:ACCOUNT} process_text token) |
NowDue |
Current amount due (sourced from Trader) |
STDINST |
Standard instalment amount (sourced from Trader) |
PAYMENT_NOWDUE |
Full payment URL for now-due amount (via {paymentlink:...} token) |
PAYMENT_INSTALMENT |
Full payment URL for instalment amount (via {paymentlink:...} token) |
PAYMENT_NOWDUE_GO |
Short redirect link for now-due payment (via {go:GUID:LINK} token) |
PAYMENT_INSTALMENT_GO |
Short redirect link for instalment payment (via {go:GUID:LINK} token) |
ObjServiceFHStatementfactory.service/package.fullhouse/ObjServiceFHStatement.py
ComputeSql() orchestrates the payment link population:
TRADERRDS (Trader RDS mirror)AddPaymentLinks() — populates EasyPay, Payment_Nowdue,Payment_Instalment for any meta_statement row where these are NULL,StatementRunAddPaymentShortcuts() — generates {go:GUID:LINK} short redirectsPAYMENT_NOWDUE_GO and PAYMENT_INSTALMENT_GO where NULLProcess text tokens used:
| Token | Output |
|---|---|
{easypay:ACCOUNT} |
EasyPay reference number/link for the account |
{paymentlink:GUID:CONTRACT:AMOUNT:statement} |
Full payment URL |
{go:GUID:LINK} |
Short redirect URL stored in Axion |
Note: StatementRun is currently hardcoded to "2020-06" in the source
— this field must be updated before running a new statement batch.
TRADERRDSPayment link generation connects via self.remote_connect("TRADERRDS").
The TRADERRDS entry in Def_RemoteConnections provides the host, credentials,
and connection details for the Trader RDS database.
| Report | Description |
|---|---|
ObjReportFHpaymentsettlement |
Settlement letter with client address, contract lines, barcode, banking details |
ObjReportFHpayment |
Standard payment/account report |
ObjReportFHpaymentcurrent |
Current payment view |
ObjReportFHpaymentemail |
Email-formatted payment report |
ObjReportFHpaymentmail |
Mail-formatted payment report |
ObjReportFHpaymentjson |
JSON export of payment data |
ObjReportFHclient |
Client detail report |
meta_statement_rolling is always a single current snapshot — there is no
historical run data in this table. StatementRun is blank; use STATDATE to
confirm the snapshot date (MAX(STATDATE) = 2026-02-08). No date filter is
needed when querying — all rows are the latest statement.
Total accounts: ~54,955 | Total contracts: ~75,271
"Most active" = most contracts on credit + paid recently + highest total balance.
SELECT
ACCOUNT,
SURNAME,
INITS,
CELL_NUMBER,
COUNT(CONTRACT) AS num_contracts,
SUM(CBAL) AS total_balance,
SUM(NOWDUE) AS total_nowdue,
SUM(STDINST) AS total_instalment,
MAX(LAST_PAY_DT) AS last_payment_date
FROM meta_statement_rolling
WHERE ACCOUNT IS NOT NULL AND ACCOUNT != ''
AND LAST_PAY_DT >= DATE_SUB(CURDATE(), INTERVAL 60 DAY)
GROUP BY ACCOUNT, SURNAME, INITS, CELL_NUMBER
ORDER BY num_contracts DESC, total_balance DESC
LIMIT {xxx}
LAST_PAY_DT >= 60 days excludes dormant accounts — adjust window as neededLAST_PAY_DT filter to include all accounts regardless ofCBAL is the current contract balance; NOWDUE is the amount due thisSTDINST is the standard monthly instalmentA customer overview page must include:
Typical client pattern observed: furniture, appliances, bedding — multiple
items per contract, extended warranties, repeat purchases every few months.
Use this client as the reference/test case when building and demoing the
customer overview report.
The sales_transaction_reps table is empty — do not use it.
Salesperson is found via sales_transaction_header.TH_USER_ID → users.
SELECT
th.TH_USER_ID,
u.US_NAME,
u.US_INITIALS,
u.US_USERNAME,
COUNT(DISTINCT th.TH_ID) AS contracts_sold
FROM sales_transaction_header th
JOIN users u
ON u.US_ID = th.TH_USER_ID
AND u.US_STORE_ID = th.TH_STORE_ID
WHERE th.TH_ACCOUNTCODE = '{account_number}'
GROUP BY th.TH_USER_ID, u.US_NAME, u.US_INITIALS, u.US_USERNAME
ORDER BY contracts_sold DESC
TH_ACCOUNTCODE = account number (e.g. 218-014161)TH_USER_ID links to users.US_ID — join also on US_STORE_ID = TH_STORE_IDcontracts_sold DESCusers columns: US_NAME, US_INITIALS, US_USERNAMEReference: C Goliath (218-014161) — primary salesperson Ancke Bothma
(user 42, 10 contracts); secondary Brigette Cloete (user 43, 6 contracts).
Lists a salesperson's clients ranked by contracts sold and total balance.
Useful for sales manager overview, performance review, and arrears flagging.
SELECT
th.TH_ACCOUNTCODE AS Account,
msr.SURNAME,
msr.INITS,
msr.CELL_NUMBER,
COUNT(DISTINCT th.TH_ID) AS contracts_sold,
SUM(msr.CBAL) AS total_balance,
SUM(msr.NOWDUE) AS total_nowdue,
MAX(msr.LAST_PAY_DT) AS last_payment_date
FROM sales_transaction_header th
JOIN hp_transaction_header hp
ON hp.HP_TYPE = th.TH_TYPE
AND hp.HP_STORE_ID = th.TH_STORE_ID
AND hp.HP_ID = th.TH_ID
JOIN meta_statement_rolling msr
ON msr.CONTRACT = hp.HP_USER_CONTRACT_ID
WHERE th.TH_USER_ID = {user_id}
AND th.TH_STORE_ID = {store_id}
GROUP BY th.TH_ACCOUNTCODE, msr.SURNAME, msr.INITS, msr.CELL_NUMBER
ORDER BY contracts_sold DESC, total_balance DESC
LIMIT {xxx}
Join chain: sales_transaction_header → hp_transaction_header
(on HP_TYPE / HP_STORE_ID / HP_ID) → meta_statement_rolling
(on CONTRACT = HP_USER_CONTRACT_ID)
Arrears flags to surface in the report:
total_nowdue close to or exceeding total_balance → severely in arrearslast_payment_date older than 60 days → no recent paymentReference data — Ancke Bothma (user 42, store 218):
Identifies a salesperson's best clients whose contracts are finishing in the
next 1–2 months and who are good payers — prime targets for a new sale offer.
Key logic:
MTHS_REM BETWEEN 1 AND 2 — finishing soon (at statement date)NOWDUE <= STDINST * 1.5 — not in arrears (normal instalment only)LAST_PAY_DT >= 60 days ago — active payerinstalment_freeing DESC then total_balance DESC — surfacesSELECT
finishing.Account,
finishing.SURNAME,
finishing.INITS,
finishing.CELL_NUMBER,
finishing.contracts_finishing,
finishing.instalment_freeing,
finishing.soonest_due,
acct.total_contracts,
acct.total_balance,
acct.total_instalment,
acct.last_payment_date
FROM (
SELECT
th.TH_ACCOUNTCODE AS Account,
msr.SURNAME,
msr.INITS,
msr.CELL_NUMBER,
COUNT(DISTINCT msr.CONTRACT) AS contracts_finishing,
SUM(msr.STDINST) AS instalment_freeing,
MIN(msr.DUEDATE) AS soonest_due
FROM sales_transaction_header th
JOIN hp_transaction_header hp
ON hp.HP_TYPE = th.TH_TYPE
AND hp.HP_STORE_ID = th.TH_STORE_ID
AND hp.HP_ID = th.TH_ID
JOIN meta_statement_rolling msr
ON msr.CONTRACT = hp.HP_USER_CONTRACT_ID
WHERE th.TH_USER_ID = {user_id}
AND th.TH_STORE_ID = {store_id}
AND msr.MTHS_REM BETWEEN 1 AND 2
AND msr.CBAL > 0
AND msr.NOWDUE <= msr.STDINST * 1.5
AND msr.LAST_PAY_DT >= DATE_SUB(CURDATE(), INTERVAL 60 DAY)
GROUP BY th.TH_ACCOUNTCODE, msr.SURNAME, msr.INITS, msr.CELL_NUMBER
) AS finishing
JOIN (
SELECT
th2.TH_ACCOUNTCODE,
COUNT(DISTINCT msr2.CONTRACT) AS total_contracts,
SUM(msr2.CBAL) AS total_balance,
SUM(msr2.STDINST) AS total_instalment,
MAX(msr2.LAST_PAY_DT) AS last_payment_date
FROM sales_transaction_header th2
JOIN hp_transaction_header hp2
ON hp2.HP_TYPE = th2.TH_TYPE
AND hp2.HP_STORE_ID = th2.TH_STORE_ID
AND hp2.HP_ID = th2.TH_ID
JOIN meta_statement_rolling msr2
ON msr2.CONTRACT = hp2.HP_USER_CONTRACT_ID
WHERE th2.TH_USER_ID = {user_id}
AND th2.TH_STORE_ID = {store_id}
GROUP BY th2.TH_ACCOUNTCODE
) AS acct ON acct.TH_ACCOUNTCODE = finishing.Account
ORDER BY instalment_freeing DESC, acct.total_balance DESC
Important: MTHS_REM = 0 with NOWDUE = CBAL means the full balance is
overdue — this is an arrears case, not a finishing contract. Always apply the
NOWDUE <= STDINST * 1.5 filter to exclude these.
Reference — Ancke Bothma top priority clients (Feb 2026 statement):
Full profile for a single salesperson. Combines book summary, health,
yearly performance, top products, and sales pipeline.
SELECT
COUNT(DISTINCT th.TH_ACCOUNTCODE) AS total_clients,
COUNT(DISTINCT th.TH_ID) AS total_contracts,
SUM(msr.CBAL) AS total_book_value,
SUM(msr.STDINST) AS total_monthly_instalment,
SUM(msr.NOWDUE) AS total_nowdue,
SUM(msr.ARREARS) AS total_arrears,
AVG(msr.MTHS_REM) AS avg_months_remaining
FROM sales_transaction_header th
JOIN hp_transaction_header hp
ON hp.HP_TYPE = th.TH_TYPE
AND hp.HP_STORE_ID = th.TH_STORE_ID
AND hp.HP_ID = th.TH_ID
JOIN meta_statement_rolling msr
ON msr.CONTRACT = hp.HP_USER_CONTRACT_ID
WHERE th.TH_USER_ID = {user_id}
AND th.TH_STORE_ID = {store_id}
AND msr.CBAL > 0
-- Clients in arrears
SELECT COUNT(DISTINCT th.TH_ACCOUNTCODE) AS clients_in_arrears,
SUM(msr.ARREARS) AS total_arrears_value
FROM sales_transaction_header th
JOIN hp_transaction_header hp
ON hp.HP_TYPE = th.TH_TYPE AND hp.HP_STORE_ID = th.TH_STORE_ID AND hp.HP_ID = th.TH_ID
JOIN meta_statement_rolling msr ON msr.CONTRACT = hp.HP_USER_CONTRACT_ID
WHERE th.TH_USER_ID = {user_id} AND th.TH_STORE_ID = {store_id}
AND msr.ARREARS > 0 AND msr.CBAL > 0;
-- Clients paying on time (no arrears, paid within 60 days)
SELECT COUNT(DISTINCT th.TH_ACCOUNTCODE) AS clients_paying_on_time
FROM sales_transaction_header th
JOIN hp_transaction_header hp
ON hp.HP_TYPE = th.TH_TYPE AND hp.HP_STORE_ID = th.TH_STORE_ID AND hp.HP_ID = th.TH_ID
JOIN meta_statement_rolling msr ON msr.CONTRACT = hp.HP_USER_CONTRACT_ID
WHERE th.TH_USER_ID = {user_id} AND th.TH_STORE_ID = {store_id}
AND msr.ARREARS = 0 AND msr.CBAL > 0
AND msr.LAST_PAY_DT >= DATE_SUB(CURDATE(), INTERVAL 60 DAY);
SELECT
YEAR(hp.META_HP_CONTRACT_DATE) AS year,
COUNT(DISTINCT th.TH_ID) AS contracts_sold,
COUNT(DISTINCT th.TH_ACCOUNTCODE) AS clients_served,
SUM(std.TD_RETAIL_PRICE * std.TD_QTY) AS total_sales_value
FROM sales_transaction_header th
JOIN hp_transaction_header hp
ON hp.HP_TYPE = th.TH_TYPE AND hp.HP_STORE_ID = th.TH_STORE_ID AND hp.HP_ID = th.TH_ID
JOIN sales_transaction_detail std
ON std.TD_TYPE = th.TH_TYPE AND std.TD_STORE_ID = th.TH_STORE_ID AND std.TD_DOCUMENT_ID = th.TH_ID
WHERE th.TH_USER_ID = {user_id} AND th.TH_STORE_ID = {store_id}
GROUP BY YEAR(hp.META_HP_CONTRACT_DATE)
ORDER BY year DESC
SELECT
std.TD_PRODUCTCODE,
std.TD_DESCRIPTION,
COUNT(*) AS times_sold,
SUM(std.TD_RETAIL_PRICE * std.TD_QTY) AS total_value
FROM sales_transaction_header th
JOIN sales_transaction_detail std
ON std.TD_TYPE = th.TH_TYPE AND std.TD_STORE_ID = th.TH_STORE_ID AND std.TD_DOCUMENT_ID = th.TH_ID
WHERE th.TH_USER_ID = {user_id} AND th.TH_STORE_ID = {store_id}
AND std.TD_DESCRIPTION NOT LIKE '%WARRANTY%'
AND std.TD_DESCRIPTION NOT LIKE '%EXTENDED%'
GROUP BY std.TD_PRODUCTCODE, std.TD_DESCRIPTION
ORDER BY times_sold DESC
LIMIT 10
SELECT
COUNT(DISTINCT th.TH_ACCOUNTCODE) AS finishing_clients,
SUM(msr.STDINST) AS instalment_freeing_up
FROM sales_transaction_header th
JOIN hp_transaction_header hp
ON hp.HP_TYPE = th.TH_TYPE AND hp.HP_STORE_ID = th.TH_STORE_ID AND hp.HP_ID = th.TH_ID
JOIN meta_statement_rolling msr ON msr.CONTRACT = hp.HP_USER_CONTRACT_ID
WHERE th.TH_USER_ID = {user_id} AND th.TH_STORE_ID = {store_id}
AND msr.MTHS_REM BETWEEN 1 AND 2
AND msr.CBAL > 0
AND msr.NOWDUE <= msr.STDINST * 1.5
AND msr.LAST_PAY_DT >= DATE_SUB(CURDATE(), INTERVAL 60 DAY)
Identifies clients who are almost done paying, have no arrears, are still
actively paying, and haven't made a new purchase in 6+ months. Ranked by
contracts ever (repeat buyer loyalty) then lifetime spend (value).
SELECT
th.TH_ACCOUNTCODE AS Account,
MAX(msr.SURNAME) AS Surname,
MAX(msr.INITS) AS Inits,
MAX(msr.CELL_NUMBER) AS Cell,
COUNT(DISTINCT th.TH_ID) AS contracts_ever,
SUM(msr.CBAL) AS remaining_balance,
SUM(msr.STDINST) AS monthly_instalment,
SUM(msr.ARREARS) AS arrears,
MAX(msr.LAST_PAY_DT) AS last_payment,
MAX(hp.META_HP_CONTRACT_DATE) AS last_purchase,
SUM(std.TD_RETAIL_PRICE * std.TD_QTY) AS lifetime_spend
FROM sales_transaction_header th
JOIN hp_transaction_header hp
ON hp.HP_TYPE = th.TH_TYPE
AND hp.HP_STORE_ID = th.TH_STORE_ID
AND hp.HP_ID = th.TH_ID
JOIN meta_statement_rolling msr
ON msr.CONTRACT = hp.HP_USER_CONTRACT_ID
JOIN sales_transaction_detail std
ON std.TD_TYPE = th.TH_TYPE
AND std.TD_STORE_ID = th.TH_STORE_ID
AND std.TD_DOCUMENT_ID = th.TH_ID
WHERE th.TH_USER_ID = {user_id}
AND th.TH_STORE_ID = {store_id}
GROUP BY th.TH_ACCOUNTCODE
HAVING remaining_balance < 3000
AND arrears = 0
AND last_payment >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
AND last_purchase < DATE_SUB(CURDATE(), INTERVAL 180 DAY)
ORDER BY contracts_ever DESC, lifetime_spend DESC
LIMIT {xxx}
HAVING thresholds (adjust as needed):
remaining_balance < 3000 — nearly paid uparrears = 0 — clean payer onlylast_payment >= 90 days — still actively payinglast_purchase < 180 days — hasn't bought recently (opportunity window)Priority call strategy:
remaining_balance < 800 first — effectively done nowmonthly_instalment — most capacity freeing upcontracts_ever > 1 are repeat buyers — highest conversion likelihoodReference — Ancke Bothma top prospects (Feb 2026):
Uses ObjPrompt.query_prompt(role, prompt) to generate a one-paragraph
assessment from the raw profile metrics.
Role (system prompt):
You are a retail credit sales analyst for FullHouse, a hire-purchase furniture
and appliance retailer. You write concise, factual one-paragraph assessments
of salesperson performance for a sales manager. Be direct. Highlight strengths,
flag risks, and end with the key opportunity. No bullet points. No headings.
Maximum 5 sentences.
Prompt (data feed):
Write a sales manager assessment for the following salesperson.
Name: {name}
Store: {store_id}
Total clients: {total_clients}
Active contracts: {total_contracts}
Total book value: R {total_book_value}
Monthly instalment collected: R {total_monthly_instalment}
Avg months remaining on contracts: {avg_months_remaining}
Clients paying on time: {clients_paying_on_time} ({pct_on_time}%)
Clients in arrears: {clients_in_arrears} ({pct_in_arrears}%)
Total arrears value: R {total_arrears}
Best sales year: {best_year} — {best_year_contracts} contracts, R {best_year_value}
Top product: {top_product} — {top_product_count} sold
Contracts finishing in next 2 months: {finishing_clients} clients
Instalment capacity freeing up: R {instalment_freeing}/month
Top prospect: {top_prospect_name} — R {top_prospect_balance} total book
Axion implementation:
from ObjAILlmOllama import ObjPrompt
ai = ObjPrompt(DB=self.DB, model="mistral")
summary = ai.query_prompt(
role=FULLHOUSE_SALESMAN_ROLE,
prompt=f"""
Write a sales manager assessment for the following salesperson.
Name: {name}
Total clients: {total_clients}
Book value: R {total_book_value:,.0f}
Monthly instalment: R {total_monthly_instalment:,.0f}
Paying on time: {clients_paying_on_time} ({pct_on_time:.0f}%)
In arrears: {clients_in_arrears} ({pct_in_arrears:.0f}%) — R {total_arrears:,.0f}
Best year: {best_year} — R {best_year_value:,.0f}
Top product: {top_product} ({top_product_count} sold)
Pipeline: {finishing_clients} clients finishing, R {instalment_freeing:,.0f}/month freeing up
""",
)
role keeps the AI focused and prevents hallucinationprompt feeds only numbers — the model writes the narrativemistral (default); swap for llama3 or gemma as neededTurns the prospect list into a motivating, actionable call briefing the
salesperson can read before picking up the phone.
Role:
You are a retail credit sales coach for FullHouse, a hire-purchase furniture
and appliance retailer. You write short, motivating call briefings for
salespeople. For each prospect, give one sentence on why to call them now
and one suggested opening line. Be conversational, not corporate. No headings.
Use a numbered list.
Prompt:
Write a priority call briefing for salesperson {name}.
These clients are nearly done paying and have no arrears. Call them today.
{for each prospect}
{rank}. {Inits} {Surname} | {Cell} | R {remaining_balance} left
— Paying R {monthly_instalment}/month | Last bought: {last_purchase}
— Lifetime spend with us: R {lifetime_spend} | Contracts: {contracts_ever}
{end for}
For each client: one sentence on why to call now, then suggest an opening
line {name} can use on the phone.
Axion implementation:
prospect_lines = []
for i, p in enumerate(prospects, 1):
prospect_lines.append(
f"{i}. {p['Inits']} {p['Surname']} | {p['Cell']} "
f"| R {p['remaining_balance']:,.0f} left\n"
f" — Paying R {p['monthly_instalment']:,.0f}/month "
f"| Last bought: {p['last_purchase']}\n"
f" — Lifetime spend: R {p['lifetime_spend']:,.0f} "
f"| Contracts: {p['contracts_ever']}"
)
ai = ObjPrompt(DB=self.DB, model="mistral")
briefing = ai.query_prompt(
role=FULLHOUSE_PROSPECT_ROLE,
prompt=f"Write a priority call briefing for {name}.\n"
f"These clients are nearly done paying and have no arrears. "
f"Call them today.\n\n"
+ "\n".join(prospect_lines)
+ "\n\nFor each client: one sentence on why to call now, "
"then suggest an opening line {name} can use on the phone.",
)
Expected output style:
- G Scholtz — Almost done with his last contract and has R 1,185/month
about to free up — perfect time for a new bedroom suite. Opening: "Hi
Mr Scholtz, I see you're nearly finished paying — I wanted to show you
something new before anyone else does."
Reference — Ancke Bothma (user 42, store 218), Feb 2026 statement:
| Metric | Value |
|---|---|
| Total clients | 1,419 |
| Active contracts | 1,721 |
| Total book value | R 14,250,174 |
| Monthly instalment | R 1,156,561/month |
| Avg months remaining | 12.5 |
| Paying on time | 564 clients (40%) |
| In arrears | 834 clients (59%) |
| Total arrears | R 3,817,861 (27% of book) |
| Best year | 2023 — 951 contracts, R 7,341,547 |
| Top product | Medi-Spine Crafton Bed Set — 221 sold, R 1,087,831 |
| Pipeline | 26 clients finishing, R 18,452/month freeing up |
Products a client bought are stored across two HP (Hire Purchase) tables.
Each credit purchase is one row in hp_transaction_header; the items on
that purchase are rows in sales_transaction_detail.
Join pattern:
SELECT
hp.HP_USER_CONTRACT_ID AS Contract,
hp.META_HP_CONTRACT_DATE AS PurchaseDate,
std.TD_PRODUCTCODE AS ProductCode,
std.TD_DESCRIPTION AS Description,
std.TD_QTY AS Qty,
std.TD_RETAIL_PRICE AS Price
FROM hp_transaction_header hp
JOIN sales_transaction_detail std
ON std.TD_TYPE = hp.HP_TYPE
AND std.TD_STORE_ID = hp.HP_STORE_ID
AND std.TD_DOCUMENT_ID = hp.HP_ID
WHERE hp.META_Account = '{account_number}'
ORDER BY hp.META_HP_CONTRACT_DATE DESC
LIMIT {xxx}
Key columns:
| Table | Column | Notes |
|---|---|---|
hp_transaction_header |
META_Account |
Account number (FK to meta_statement_rolling.ACCOUNT) |
hp_transaction_header |
HP_USER_CONTRACT_ID |
Contract reference (matches meta_statement_rolling.CONTRACT) |
hp_transaction_header |
META_HP_CONTRACT_DATE |
Purchase date (date type) |
hp_transaction_header |
HP_TYPE, HP_STORE_ID, HP_ID |
Composite PK — used to join to detail lines |
sales_transaction_detail |
TD_DESCRIPTION |
Product description (denormalized — no product join needed) |
sales_transaction_detail |
TD_PRODUCTCODE |
Product code (FK to products.PM_PRODUCTCODE) |
sales_transaction_detail |
TD_RETAIL_PRICE |
Selling price |
sales_transaction_detail |
TD_QTY |
Quantity |
products |
PM_DESCRIPTION |
Canonical product description (optional join for richer info) |
Notes:
HP_USER_CONTRACT_ID matches CONTRACT in meta_statement_rolling, soTD_DESCRIPTION is stored on the sale line, so the join to products issales_transaction_detail join keys are TD_TYPE / TD_STORE_ID / TD_DOCUMENT_ID (not TD_HP_* — those columns do not exist).Two active promotions valid 12 Feb 2026 – 17 Mar 2026, all on 24-month terms.
| Promotion | ID | Products |
|---|---|---|
| 202602 - MANAGERS LEAFLET | 313 | 74 |
| 202602 - PJN ONLINE DEALS MANAGERS CHOICE | 314 | 48 (subset of 313) |
Query to fetch active promotions and their products:
SELECT
ph.PH_NAME AS Promotion,
pd.PR_PRODUCTCODE AS Code,
p.PM_DESCRIPTION AS ProductName,
pd.PR_PRICE AS OriginalPrice,
pd.PR_NEWPRICE AS SpecialPrice,
pd.META_TERMS AS Terms,
pd.META_INSTALMENT AS Instalment
FROM promotion_header ph
JOIN promotion_detail pd ON pd.PR_ID = ph.PH_ID
JOIN products p ON p.PM_PRODUCTCODE = pd.PR_PRODUCTCODE
WHERE STR_TO_DATE(ph.PH_ENDDATE, '%d.%m.%Y') >= CURDATE()
AND STR_TO_DATE(ph.PH_STARTDATE, '%d.%m.%Y') <= CURDATE()
AND pd.PR_NEWPRICE > 0
ORDER BY ph.PH_ID, pd.PR_NEWPRICE DESC
Notes:
PH_ACTIVE = 1 is NOT a reliable active flag — all past promotions show active.STR_TO_DATE(PH_STARTDATE/PH_ENDDATE, '%d.%m.%Y') vs CURDATE().PR_PRICE may be cost/reference price; PR_NEWPRICE is the promotional selling price.PR_NEWPRICE > PR_PRICE — this is expected for bundle deals.META_TERMS = instalment term in months (typically 24).Generates a personalised WhatsApp/SMS message for a client based on their
purchase history and the current specials. Match specials to what the client
has bought before.
Role:
You are {salesperson_name}, a sales consultant at FullHouse, a hire-purchase
furniture and appliance retailer in South Africa. You are writing a short,
warm, personal WhatsApp message directly to one of your own clients.
Write in first person as the salesperson — not as the company. Be friendly
and genuine, like a message from someone who knows the client. Maximum 5
sentences. Address the client by surname. Sign off with your own name.
Prompt:
Write a personalised WhatsApp message from me to my client.
My name: {salesperson_name}
My cell: {salesperson_cell}
Client: {Inits} {Surname}
What this client has bought from me before:
{purchase_history — one line per contract, e.g. "Bed set (2024), Gas stove (2025)"}
Current specials I want to share (pick the most relevant 2-3 for this client):
{specials_list — one line per product, e.g. "Restonic Bedding Bundle — R 8,899 over 24 months"}
Write a WhatsApp message that:
- Comes from me personally, not from the company
- Greets the client by surname
- Mentions 2-3 specials most relevant to what they have bought before
- Feels like I thought of them specifically when I saw these deals
- Ends with my name and an invitation to call or WhatsApp me directly
Axion implementation:
# Build purchase history summary
history_lines = [
f"{std['TD_DESCRIPTION'].title()} ({hp['META_HP_CONTRACT_DATE'][:4]})"
for std, hp in purchase_history
]
# Build matched specials (pre-filter to client-relevant categories)
specials_lines = [
f"{s['ProductName'].title()} — R {s['SpecialPrice']:,.0f} "
f"over {s['Terms']} months"
for s in matched_specials
]
ai = ObjPrompt(DB=self.DB, model="mistral")
message = ai.query_prompt(
role=FULLHOUSE_SPECIALS_ROLE,
prompt=f"Write a personalised specials message for this client.\n\n"
f"Client: {inits} {surname}\n"
f"Salesperson: {salesperson_name}\n"
f"Salesperson cell: {salesperson_cell}\n\n"
f"What this client has bought from us before:\n"
+ "\n".join(history_lines)
+ "\n\nCurrent specials available:\n"
+ "\n".join(specials_lines)
+ "\n\nWrite a WhatsApp message that greets them by surname, "
"mentions 2-3 relevant specials, includes the monthly instalment "
f"if available, and ends with a call to action to contact "
f"{salesperson_name}.",
)
Example output for C Goliath (from Ancke Bothma):
Good day Mr Goliath! It's Ancke from FullHouse — I saw these specials
and immediately thought of you. The Restonic Bedding Bundle is going for
R 8,899 over 24 months and we also have a Totai 90cm Gas Stove with Oven
at R 12,999 — both right up your street. Give me a WhatsApp or call when
you're ready and I'll sort everything out for you. — Ancke
Tip: Pre-filter specials by matching product category keywords against
the client's purchase history before sending to the prompt. This keeps the
prompt short and the output focused.
Sends a WhatsApp broadcast to the salesperson's full client group,
organized by category. Not personalised — general announcement.
Role:
You are {salesperson_name}, a sales consultant at FullHouse, a hire-purchase
furniture and appliance retailer in South Africa. You are writing a WhatsApp
broadcast message to your client group to share this month's best specials.
Write in first person. Be warm and enthusiastic but brief. Organize specials
by category with a short heading per category. Use simple formatting suitable
for WhatsApp. Maximum 15 lines total. Sign off with your name.
Prompt:
Write a WhatsApp broadcast message from me to my clients sharing this month's
specials. Organize by category. Keep it short and punchy.
My name: {salesperson_name}
Valid until: {end_date}
All items on {terms}-month hire purchase.
Specials by category:
{category_block}
Write me a broadcast WhatsApp message — short, exciting, well organized by
category, formatted for WhatsApp. Sign off with my name and invite them to
reply for more info.
Sends a personal message to a specific client picking only the 2-3 most
relevant categories based on what they have bought before.
Role:
You are {salesperson_name}, a sales consultant at FullHouse. You are writing
a personal WhatsApp message to one of your clients. You know what they have
bought before and you are picking only the most relevant category specials
for them. Write in first person. Maximum 3 categories. Maximum 8 lines.
Sign off with your name.
Prompt:
Write a personal WhatsApp message from me to my client about this month's
specials. Only include the 2-3 categories most relevant to what they have
bought before.
My name: {salesperson_name}
Client: {Inits} {Surname}
Valid until: {end_date}
What this client has bought before (by category):
{e.g. "Bedding (2024, 2025), Couches (2023), Whites — Cooking (2024)"}
Available specials by category:
{category_block}
Pick only the categories that match what they have bought. Write a warm,
personal message. Sign off with my name and invite them to WhatsApp me back.
SQL — specials grouped by category:
SELECT
COALESCE(c1.CT_NAME, p.PM_CATEGORY_ID1) AS Category,
COALESCE(c2.CT_NAME, p.PM_CATEGORY_ID2) AS SubCategory,
p.PM_DESCRIPTION AS Product,
pd.PR_NEWPRICE AS SpecialPrice,
pd.META_TERMS AS Terms
FROM promotion_header ph
JOIN promotion_detail pd ON pd.PR_ID = ph.PH_ID
JOIN products p ON p.PM_PRODUCTCODE = pd.PR_PRODUCTCODE
LEFT JOIN category c1 ON c1.CT_CODE = p.PM_CATEGORY_ID1 AND c1.CT_TYPE = '0'
LEFT JOIN category c2 ON c2.CT_CODE = p.PM_CATEGORY_ID2 AND c2.CT_TYPE = '0'
WHERE STR_TO_DATE(ph.PH_ENDDATE, '%d.%m.%Y') >= CURDATE()
AND STR_TO_DATE(ph.PH_STARTDATE, '%d.%m.%Y') <= CURDATE()
AND pd.PR_NEWPRICE > 0
ORDER BY Category, SubCategory, SpecialPrice DESC
Python — build category block for prompt:
from collections import defaultdict
category_map = defaultdict(list)
for row in specials:
key = f"{row['Category']} — {row['SubCategory']}"
category_map[key].append(
f" • {row['Product'].title()} — R {row['SpecialPrice']:,.0f}"
)
category_block = ""
for cat, items in category_map.items():
category_block += f"\n{cat.upper()}\n" + "\n".join(items) + "\n"
Category codes resolved (CT_TYPE = '0'):
| Code | Category |
|---|---|
| CELL | Cellular |
| COUC | Couches and Chairs |
| ELEC | Electronics |
| FURN | Furniture |
| MISC | Miscellaneous |
| SLEE | Mattress Base Sets / Sleeper Couches |
| SM | Small Appliances |
| WH | Whites |
meta_sales_repsFormal sales targets are stored in meta_sales_reps. This table is separate
from the users table (which drives transaction attribution). They share the
same people but with different ID sequences; link by name.
SELECT
msr.SR_ID,
msr.SR_NAME,
msr.SR_STORE_ID,
msr.SalesTarget,
msr.CommissionTarget,
msr.Status
FROM meta_sales_reps msr
WHERE msr.SR_STORE_ID = {store_id}
AND (msr.Status IS NULL OR msr.Status NOT LIKE '%LEFT%')
ORDER BY msr.SalesTarget DESC
Store 218 targets (current):
| SR_ID | Name | SalesTarget/month | CommissionTarget |
|---|---|---|---|
| 14 | OT - ANCKE BOTHMA | R 300,000 | NULL |
| 15 | OT - BRIGETTE CLOETE | R 300,000 | NULL |
| 11 | SM - LAUNCE BURGER | R 300,000 | NULL |
| ... | (all reps same) | R 300,000 | NULL |
Note: The R 300,000/month target appears to be a legacy placeholder.
Both active reps are consistently 180–200%+ of target. The commission table
(salesrep_commission) has no data for store 218.
meta_sales_reps.SR_ID and users.US_ID are separate sequences. The
sales_transaction_header.TH_USER_ID joins to users.US_ID (not SR_ID).
To link targets to actuals, match by stripped name:
# Strip "OT - " / "SM - " prefix from meta_sales_reps.SR_NAME to match users.US_NAME
rep_name = msr_name.replace("OT - ", "").replace("SM - ", "").strip()
Or join via name in SQL:
JOIN users u ON TRIM(REPLACE(REPLACE(msr.SR_NAME, 'OT - ', ''), 'SM - ', '')) = u.US_NAME
AND u.US_STORE_ID = msr.SR_STORE_ID
SELECT
u.US_NAME AS Salesperson,
DATE_FORMAT(hp.META_HP_CONTRACT_DATE, '%Y-%m') AS Month,
COUNT(DISTINCT th.TH_ID) AS Contracts,
ROUND(SUM(std.TD_RETAIL_PRICE * std.TD_QTY), 0) AS ActualSales,
msr.SalesTarget AS Target,
ROUND(SUM(std.TD_RETAIL_PRICE * std.TD_QTY)
/ msr.SalesTarget * 100, 1) AS PctOfTarget
FROM sales_transaction_header th
JOIN hp_transaction_header hp
ON hp.HP_TYPE = th.TH_TYPE AND hp.HP_STORE_ID = th.TH_STORE_ID AND hp.HP_ID = th.TH_ID
JOIN sales_transaction_detail std
ON std.TD_TYPE = th.TH_TYPE AND std.TD_STORE_ID = th.TH_STORE_ID AND std.TD_DOCUMENT_ID = th.TH_ID
JOIN users u ON u.US_ID = th.TH_USER_ID AND u.US_STORE_ID = th.TH_STORE_ID
LEFT JOIN meta_sales_reps msr
ON TRIM(REPLACE(REPLACE(msr.SR_NAME, 'OT - ', ''), 'SM - ', '')) = u.US_NAME
AND msr.SR_STORE_ID = th.TH_STORE_ID
WHERE th.TH_STORE_ID = {store_id}
GROUP BY u.US_NAME, DATE_FORMAT(hp.META_HP_CONTRACT_DATE, '%Y-%m'), msr.SalesTarget
ORDER BY Month DESC, ActualSales DESC
| Salesperson | Contracts | Total Sales | Avg/Month | vs R300k Target |
|---|---|---|---|---|
| Ancke Bothma | 915 | R 7,368,865 | R 614,072 | 205% |
| Brigette Cloete | 832 | R 6,710,072 | R 559,173 | 186% |
2025 Monthly breakdown (Ancke / Brigette):
| Month | Ancke | Brigette | Store Total |
|---|---|---|---|
| Jan 2025 | R 555,984 | R 435,320 | R 1,003,501 |
| Feb 2025 | R 500,835 | R 421,482 | R 922,317 |
| Mar 2025 | R 751,219 | R 571,108 | R 1,322,327 |
| Apr 2025 | R 483,340 | R 589,528 | R 1,072,868 |
| May 2025 | R 601,611 | R 628,267 | R 1,229,878 |
| Jun 2025 | R 508,867 | R 469,673 | R 978,540 |
| Jul 2025 | R 436,158 | R 520,880 | R 957,038 |
| Aug 2025 | R 443,222 | R 592,619 | R 1,035,841 |
| Sep 2025 | R 613,383 | R 293,922 | R 907,305 |
| Oct 2025 | R 802,233 | R 557,609 | R 1,359,842 |
| Nov 2025 | R 682,032 | R 637,220 | R 1,319,252 |
| Dec 2025 | R 989,980 | R 992,444 | R 1,992,623 |
Observations:
| Rep | Floor (R300k min) | Realistic | Stretch (2025 best) |
|---|---|---|---|
| Ancke Bothma | R 300,000 | R 600,000 | R 990,000 (Dec 2025) |
| Brigette Cloete | R 300,000 | R 550,000 | R 992,000 (Dec 2025) |
factory.text/package.fullhouseAll modules expose ObjProcessText(DB, Page) with a Process(Param1..8) entry
point (the standard process_text token interface).
ObjTextFHInstalment — Hire-Purchase Instalment CalculatorThe core HP instalment engine. All other financial calculators inherit from it.
Parameters:
Param1 — base price (used if no product code)Param2 — product code (looks up trader.products.PM_STDSELLINGPRICE; promotion price overrides if active)Param3 — term override in months, or "extended" to add extended warrantyCalculation pipeline:
| Step | Formula / Source |
|---|---|
| Term | trader.product_notes where PN_NOTE_NAME LIKE '%term%'; default 24; hardcoded overrides for a few codes; prices < R400 → 1 month (cash) |
| Insurance type | trader.products.PM_USERFIELD1 → hp_insurance_types.HPI_ID; cells default to ID 2 |
| Deposit | Base × 10% (only when product code supplied, terms > 1) |
| Base (net) | SellingPrice − Deposit |
| Initiation fee | ((Base − 1000) × 11.5%) + R189.75; cap at min(17.25% × Base, R1207) |
| Principal | Base + InitiationFee |
| Admin fee | maf_terms tiered lookup by Principal |
| Insurance | CEIL(BaseInsure × HPI_Rate / 12) per month |
| Instalment | CEIL(-PMT(23.75%/12, Terms, Principal)) (numpy pmt) |
| Monthly total | Instalment + Insurance + AdminFee |
| Total payable | Monthly × Terms |
Result cached in Janee_Instalment table. Returns (MonthlyPayment, TotalPayment).
Insurance rates (hp_insurance_types):
| ID | Policy | Rate %/year |
|---|---|---|
| 1 | PLUS! POLICY | 22% |
| 2 | STANDARD POLICY | 19% |
| 3 | NO INSURANCE | 0% |
| 4 | THEFT & DAMAGE | 12% |
| 5 | CELLPHONE/TABLET | 40% |
| 6 | LAPTOP/CAR AUDIO/BICYCLE | 30% |
Interest & admin (hp_agreement_types, ID=1 — Standard):
| Field | Value |
|---|---|
| Interest rate | 23.75% p.a. |
| Admin fee | R39/month (overridden by maf_terms) |
| Init start | R189.75 |
| Init percent above break | 11.5% |
| Init break point | R1,000 |
| Init max | R1,207 |
Admin fee tiers (maf_terms):
| Principal ≤ | Monthly admin fee |
|---|---|
| R1,599 | R14 |
| R2,799 | R29 |
| R3,999 | R39 |
| R4,999 | R49 |
| R5,999 | R59 |
| R6,999 | R69 |
| > R6,999 | R69 |
Process text tokens:
{instalment:PRICE:PRODUCTCODE} → monthly instalment string{instalment:PRICE:PRODUCTCODE:extended} → adds extended warranty to base priceObjTextFHTotal — Total PayableInherits from ObjTextFHInstalment. Process() returns TotalPayment (Monthly × Terms)
instead of monthly instalment.
ObjTextFHSettlement — Settlement Amount CalculatorCalculates exact settlement figure for an existing HP contract.
Source data from trader.hp_transaction_header:
HP_TOTAL_VALUE, HP_INTEREST_AMOUNT, HP_DEPOSIT_AMOUNTHP_NUMBER_PAYMENTS (original term)HP_INTEREST_PERCENT (annual %)HP_FIRST_PAYMENT_DATE, HP_CONTRACT_BALANCE (current book balance)Settlement formula:
Principal = TotalValue - InterestAmount - Deposit
MonthsPaid = TIMESTAMPDIFF(MONTH, FirstPaymentDate, NOW()) + 2
TotalInterest = Σ ipmt(rate/12, n, NumPayments, Principal) for n=1..NumPayments
TodateInterest = Σ ipmt(rate/12, n, NumPayments, Principal) for n=1..MonthsPaid
Partial = TotalInterest - TodateInterest ← future interest still owed
Settlement = CurrentBalance + Partial
Param1 = contract_id → single contractParam1 = "complete" → batch updates all contracts where meta_statement_rolling.Settle = -1ObjReportFHpaymentsettlement via {fhsettlement:CONTRACT} tokenObjTextEasypay — EasyPay Reference GeneratorGenerates EasyPay bill payment reference numbers for client accounts.
Format: 9 + 2280 (prefix) + first 9 digits of account (dashes stripped) + Luhn check digit
# Example: account 218-014161
Number = "218014161" # 9 digits, dashes stripped
Easy = "9" + "2280" + Number + luhn_digit("2280218014161")
# → "922802180141619" (15 chars)
Batch mode: Check() scans trader.meta_easypay and meta_statement; generates
and inserts missing EasyPay numbers; updates meta_statement.Easypay.
Key tables:
trader.meta_easypay — (Contract, EasyPay) lookuptrader.meta_statement.Easypay — denormalized EasyPay per contractObjTextPaymentlink — PayFast Payment URL BuilderGenerates a PayFast payment URL for a specific contract and amount.
Parameters:
Param1 = payment GUIDParam2 = contract IDParam3 = amountParam4 = service code (e.g. "STATEMENT")Param5 = cell numberParam6 = item name (optional)Param7 = item description (optional)Reads PayFast credentials (merchant_id, merchant_key, Urltemplate, Submiturl)
from def_payment WHERE Active = 'Y' LIMIT 1, then substitutes all placeholders.
ObjTextFHDescription — Product Feature Description BuilderReads trader.meta_product.Feature1..15 and concatenates them as a pipe-delimited
Description string. Updates meta_product.Description in place.
ObjTextFHFeatures — Product Feature ImporterImports and normalises product features from trader.product_notes into trader.meta_product.
Source: product_notes WHERE PN_NOTE_NAME = 'Features'
Splitting: tries | then : then ,
Target: meta_product.Feature1..15 + Description (pipe-joined)
ObjTextFHImages — PIMS Image ManagerManages product images in the PIMS (product image system):
data.documents/pims/ for *_pims.jpg filesdata_documentfile and data_documentmeta_pimsThere are two image variants per product and two access domains:
| Variant | Public URL | Internal HTML |
|---|---|---|
| Icon (thumbnail) | https://fhgo.co.za/image/pims/{CODE}_pims_icon.jpeg |
/objects/local.documents/pims/{CODE}_pims_icon.jpeg |
| Full image | https://fhgo.co.za/image/pims/{CODE}_pims.jpg |
/objects/local.documents/pims/{CODE}_pims.jpg |
| Fallback (no image) | https://fhgo.co.za/image/pims/logo_pims_icon.jpeg |
/image/noimage.jpg |
{CODE} is the trader.products.PM_PRODUCTCODE (e.g. 15952).
Existence check — before building the URL, check data_documentmeta_pims:
SELECT COUNT(*) FROM data_documentmeta_pims WHERE Guid = '{product_code}'
Or via data_documentfile:
SELECT COUNT(*) FROM data_documentfile
WHERE Docname = 'Pims' AND CONTEXT = '{product_code}'
(data_documentfile is in the fullhouse.axion database, not trader.)
Alternate product code — if no image found, look up trader.meta_product_alternates
for a sibling product that shares a Guid, then use that product code's image.
Python helper (from ObjReportFHproductjson):
import os
from ObjConstants import LOCAL_DOCUMENTS
image_base_url = "https://fhgo.co.za"
base_dir = f"{site_base_dir}/objects/{LOCAL_DOCUMENTS}/pims/{product_code}_pims_icon.jpeg"
base_dir = base_dir.replace("objects/objects", "objects")
if os.path.isfile(base_dir):
icon_url = f"{image_base_url}/image/pims/{product_code}_pims_icon.jpeg"
full_url = f"{image_base_url}/image/pims/{product_code}_pims.jpg"
else:
icon_url = f"{image_base_url}/image/pims/logo_pims_icon.jpeg"
full_url = f"{image_base_url}/image/pims/logo_pims.jpg"
In-page HTML (internal Axion reports):
<img src="/objects/local.documents/pims/{CODE}_pims.jpg"
onerror="this.src='/image/noimage.jpg'"
class="img-thumbnail" style="max-width: 200px;">
13,911 products have registered PIMS images in fullhouse.axion.data_documentfile.
ObjTextFHDeletePimsImage — PIMS Image DeleterDeletes a specific product's PIMS image file:
/var/www/axion/objects/{LOCAL_DOCUMENTS}/pims/{Code}_pims.jpg
| Table | Used By | Purpose |
|---|---|---|
trader.hp_insurance_types |
Instalment | Insurance rates per policy type |
trader.hp_agreement_types |
Instalment | Interest rate, initiation formula |
trader.maf_terms |
Instalment | Tiered admin fees by principal |
trader.products |
Instalment | Selling price, category, HPI ID, term notes |
trader.product_notes |
Instalment, Features | Per-product term and feature text |
trader.hp_transaction_header |
Settlement | Contract financial terms |
trader.meta_product |
Description, Features, Images | Product metadata cache |
trader.meta_easypay |
EasyPay | EasyPay number lookup |
trader.meta_statement |
Settlement, EasyPay | Standard instalment, EasyPay |
Janee_Instalment |
Instalment | Instalment calculation cache |
def_payment |
PaymentLink | PayFast merchant credentials |
data_documentfile |
Images | Document file registry |
data_documentmeta_pims |
Images | PIMS image metadata |
| Service | Description |
|---|---|
ObjServiceFHStatement |
Populates payment links in meta_statement via TRADERRDS; run before statement dispatch |
ObjServiceFHShopify |
Shopify GraphQL Admin API integration — product CRUD, pricing, images, inventory sync |
FullHouse uses Shopify as their e-commerce frontend. Axion manages products, pricing,
promotions, images, and inventory via the Shopify GraphQL Admin API (v2026-01).
https://{shop_domain}/admin/api/2026-01/graphql.jsonremoteconnections.AccessToken WHERE RemoteType = 'SHOPIFY'X-Shopify-Access-Token: {token}7112607826093)gid://shopify/Product/7112607826093)Three databases are involved:
| Database | Purpose |
|---|---|
trader |
Source of truth for products, pricing, promotions, alternates |
core.axion |
Shopify sync state — local cache of products, variants, meta, images |
fullhouse.axion |
Staging table (prestage_shopify) for product push queue |
core.axion)| Table | Purpose |
|---|---|
data_shopify_products |
Product header cache (product_id, graphql_id, title, status) |
data_shopify_variants |
Variant cache (sku, price, compare_at_price, inventory_qty) |
data_shopify_variants_all |
Complete variant history (all variants, not just primary) |
data_shopify_meta |
Metafield cache (custom fields on products/variants) |
data_shopify_inventory |
Inventory sync state (last_sync, price ranges, promotion state) |
data_shopify_images |
Image hash cache (prevents duplicate uploads) |
data_shopify_locations |
Warehouse/location mapping for inventory activation |
data_shopify_price |
Price update audit trail |
data_shopify_dryrun |
Dry-run verification table for pricing/terms testing |
| Table | Purpose |
|---|---|
trader.products |
Master product data (price, status, warranty, category) |
trader.meta_product |
Product metadata (descriptions, features, dimensions, credit terms) |
trader.meta_product_alternates |
Product grouping (colour/size variants share a Guid) |
trader.product_colour |
Colour descriptions per product code |
trader.promotion_header |
Promotion definitions (name, start/end dates, store scope) |
trader.promotion_detail |
Promotion line items (product code, discount price, original price) |
trader.view_product_promotion |
Convenience view joining header+detail+credit terms |
trader.product_status |
Status lookup (1=Listed, 4=Range, 5/6/12=Discontinued, etc.) |
trader.product_ledger |
Stock levels per store/location |
trader.category |
Category hierarchy (3 levels, with optional META_NAME override) |
trader.meta_categorymap |
Category mapping from Trader categories to Shopify categories |
trader.meta_stock |
Stock metadata cache |
trader.product_brands |
Brand/vendor names |
Promotions in Trader use dd.mm.YYYY date format (not ISO). All date comparisons
must use STR_TO_DATE(field, '%d.%m.%Y').
SELECT
pdt.PR_PRODUCTCODE AS ProductCode,
COUNT(*) AS ActivePromos,
GROUP_CONCAT(phd.PH_ID) AS PromoIds
FROM trader.promotion_header phd
LEFT JOIN trader.promotion_detail pdt
ON phd.PH_ID = pdt.PR_ID
WHERE
pdt.PR_PRODUCTCODE = '{product_code}'
AND CURDATE() BETWEEN
STR_TO_DATE(phd.PH_STARTDATE, '%d.%m.%Y')
- INTERVAL 7 DAY
AND STR_TO_DATE(phd.PH_ENDDATE, '%d.%m.%Y')
- INTERVAL 1 DAY
Note: Tracks promotions from up to a week early, and until the day before
a promotion ends.
SELECT *
FROM (
SELECT
pdt.PR_PRODUCTCODE AS ProductCode,
COUNT(*) AS ActivePromos,
GROUP_CONCAT(phd.PH_ID) AS PromoIds,
GROUP_CONCAT(pdt.PR_PRICE) AS DiscountPrices
FROM trader.promotion_header phd
LEFT JOIN trader.promotion_detail pdt
ON phd.PH_ID = pdt.PR_ID
WHERE
CURDATE() BETWEEN
STR_TO_DATE(phd.PH_STARTDATE, '%d.%m.%Y')
- INTERVAL 7 DAY
AND STR_TO_DATE(phd.PH_ENDDATE, '%d.%m.%Y')
- INTERVAL 1 DAY
GROUP BY pdt.PR_PRODUCTCODE
) AS promo_items
WHERE ActivePromos > 1
Should return no rows in a clean state.
SELECT *
FROM (
SELECT
pdt.PR_PRODUCTCODE AS product_code,
pdt.PR_PRICE AS discount_price,
pdt.PR_NEWPRICE AS orig_price,
phd.PH_ID AS promo_id
FROM trader.promotion_header phd
LEFT JOIN trader.promotion_detail pdt
ON phd.PH_ID = pdt.PR_ID
WHERE
CURDATE() BETWEEN
STR_TO_DATE(phd.PH_STARTDATE, '%d.%m.%Y')
- INTERVAL 7 DAY
AND STR_TO_DATE(phd.PH_ENDDATE, '%d.%m.%Y')
- INTERVAL 1 DAY
) AS promo_data
LEFT JOIN (
SELECT
pdt.PR_PRODUCTCODE AS product_code,
AVG(pdt.PR_PRICE) AS avg_promo_price
FROM trader.promotion_header phd
LEFT JOIN trader.promotion_detail pdt
ON phd.PH_ID = pdt.PR_ID
WHERE
CURDATE() BETWEEN
STR_TO_DATE(phd.PH_STARTDATE, '%d.%m.%Y')
- INTERVAL 7 DAY
AND STR_TO_DATE(phd.PH_ENDDATE, '%d.%m.%Y')
- INTERVAL 1 DAY
GROUP BY pdt.PR_PRODUCTCODE
) AS means
ON promo_data.product_code = means.product_code
WHERE promo_data.discount_price != means.avg_promo_price
Used by _get_promotion_data_bulk() for efficient batch lookups:
SELECT
pdt.PR_PRODUCTCODE AS product_code,
phd.PH_NAME AS promotion_name,
pdt.PR_PRICE AS promotion_price,
pdt.PR_PRICE - pdt.PR_NEWPRICE AS promotion_discount_amount,
pdt.PR_NEWPRICE AS original_price,
mpr.Web_Instalment AS credit_installment,
mpr.Web_Deposit AS credit_deposit,
(mpr.Web_Totalprice - mpr.Web_Deposit)
/ mpr.Web_Instalment AS credit_months,
mpr.Web_Totalprice AS credit_price
FROM trader.promotion_header phd
LEFT JOIN trader.promotion_detail pdt
ON phd.PH_ID = pdt.PR_ID
LEFT JOIN trader.meta_product mpr
ON pdt.PR_PRODUCTCODE = mpr.Code
WHERE pdt.PR_PRODUCTCODE IN ({sku_list})
Products that come in multiple colours/sizes share a Guid in
trader.meta_product_alternates. One product per group is marked IsPrimary = 'Y'.
SELECT ProductCode, IsPrimary
FROM trader.meta_product_alternates
WHERE Guid = (
SELECT Guid
FROM trader.meta_product_alternates
WHERE ProductCode = '{product_code}'
)
-- Furniture alternates (colour variants)
SELECT COUNT(Option1)
FROM trader.meta_product_alternates
WHERE Productcode = '{product_code}'
AND Option1 IN ('furniture', 'lounge')
-- Bedding alternates (size variants)
SELECT COUNT(Option1)
FROM trader.meta_product_alternates
WHERE Productcode = '{product_code}'
AND Option1 = 'bedding'
SELECT DISTINCT Sku
FROM `core.axion`.data_shopify_variants AS S
WHERE
NOT Sku LIKE '%\\_%'
AND NOT Sku = ''
AND DATEDIFF(NOW(), LastUpdate) >= 1
AND NOT Sku IN ('CURRENT', 'COMPLETE')
ORDER BY LastUpdate
SELECT
Sku, id, product_id, admin_graphql_api_id,
Pims_Description, LastUpdate, Pims_Primary_Image
FROM `core.axion`.data_shopify_variants
WHERE
product_id = '{shopify_product_id}'
AND Sku = '{product_code}'
SELECT DISTINCT
PM_ProductCode AS code,
PM_Stdsellingprice AS total_price,
pm_description AS Description,
pb_name AS Brand,
COALESCE(C1.META_NAME, C1.CT_NAME) AS Category,
COALESCE(C2.META_NAME, C2.CT_NAME) AS SubCategory,
COALESCE(C3.META_NAME, C3.CT_NAME) AS ThirdCategory,
IF(ps_allowsale = 1, 'Y', 'N') AS AllowSale,
IF(ps_alloworder = 1, 'Y', 'N') AS AllowOrder
FROM trader.products AS products
LEFT JOIN trader.product_brands AS brands
ON products.pm_brand_id = brands.pb_id
LEFT JOIN trader.product_status AS status
ON products.pm_status_id = status.ps_id
LEFT JOIN trader.meta_stock AS meta_stock
ON meta_stock.meta_stock_id = products.pm_productcode
LEFT JOIN trader.category AS C1
ON products.PM_CATEGORY_ID1 = C1.CT_CODE
LEFT JOIN trader.category AS C2
ON products.PM_CATEGORY_ID2 = C2.CT_CODE
LEFT JOIN trader.category AS C3
ON products.PM_CATEGORY_ID3 = C3.CT_CODE
WHERE pm_ProductCode = '{product_code}'
LIMIT 1
SELECT
M1.TargetCat1, M1.TargetCat2, M1.TargetCat3,
PM_CATEGORY_ID1, PM_CATEGORY_ID2, PM_CATEGORY_ID3
FROM trader.products AS P
LEFT JOIN trader.meta_categorymap AS M1
ON P.PM_CATEGORY_ID1 = M1.Ct_code1
AND P.PM_CATEGORY_ID2 = M1.Ct_code2
AND P.PM_CATEGORY_ID3 = M1.Ct_code3
WHERE P.PM_PRODUCTCODE = '{product_code}'
Falls back to 2-level match if 3-level match returns no rows.
SELECT
(Weight_1 + Weight_2 + Weight_3
+ Weight_4 + Weight_5) AS Weight,
(Length_1 + Length_2 + Length_3
+ Length_4 + Length_5) AS Length,
(Height_1 + Height_2 + Height_3
+ Height_4 + Height_5) AS Height,
(Width_1 + Width_2 + Width_3
+ Width_4 + Width_5) AS Width,
Weight_1, Weight_2, Weight_3, Weight_4, Weight_5,
Length_1, Length_2, Length_3, Length_4, Length_5,
Height_1, Height_2, Height_3, Height_4, Height_5,
Width_1, Width_2, Width_3, Width_4, Width_5,
Quantity_1, Quantity_2, Quantity_3, Quantity_4, Quantity_5
FROM trader.meta_product
WHERE CODE = '{product_code}'
SELECT
PM_PRODUCTCODE,
PM_SIMILIARITEMCODE AS ExtendedWarranty,
PM_WEIGHTCODE AS WarrantyCode,
PM_SHELFLIFE AS GauranteePeriod,
PM_COSTBEFOREREBATE AS WarrantyPeriod
FROM trader.products
WHERE PM_Productcode = '{product_code}'
The meta_can_order_message field determines how a product appears on Shopify:
| Status | Stock | Result |
|---|---|---|
PM_IS_WEB=0 or Status 3,7,8,9,11 |
Any | Archived |
| Range (Status 4) | > 0 | In Stock |
| Range (Status 4) | <= 0 | In Stock |
| Listed (Status 1) | > 0 | In Stock |
| Listed (Status 1) | <= 0 | Supplier Order |
| Discontinued (Status 5,6,12) | > 0 | In Stock |
| Discontinued (Status 5,6,12) | <= 0 | Archived |
SELECT
CASE
WHEN Archived = 'Archived' THEN 'Archived'
WHEN RangeItem = 'Y' AND stock_level > 0 THEN 'In Stock'
WHEN RangeItem = 'Y' AND stock_level <= 0 THEN 'In Stock'
WHEN ListItem = 'Y' AND stock_level > 0 THEN 'In Stock'
WHEN ListItem = 'Y' AND stock_level <= 0
THEN 'Supplier Order'
WHEN Discontinued = 'Y' AND stock_level > 0
THEN 'In Stock'
WHEN Discontinued = 'Y' AND stock_level <= 0
THEN 'Archived'
ELSE 'Error'
END AS meta_can_order_message
FROM (
SELECT
p.PM_PRODUCTCODE,
IF(
p.PM_IS_WEB = 0
OR p.PM_Status_id IN (3, 7, 8, 9, 11),
'Archived', ''
) AS Archived,
IF(p.PM_STATUS_ID = 4, 'Y', 'N') AS RangeItem,
IF(p.PM_STATUS_ID = 1, 'Y', 'N') AS ListItem,
IF(
p.PM_Status_id IN (5, 6, 12), 'Y', 'N'
) AS Discontinued,
COALESCE(SUM(CASE
WHEN pl.PL_SUBTYPE1 = ''
AND pl.PL_SUBTYPE2 = ''
AND pl_store_id = '299'
AND pl_location_id = 1
THEN pl_qtyonhand
WHEN pl.PL_SUBTYPE1 = ''
AND pl.PL_SUBTYPE2 = ''
AND pl_store_id != '299'
AND pl_location_id = 0
THEN pl_qtyonhand
ELSE 0
END), 0) AS stock_level
FROM trader.products AS p
INNER JOIN trader.product_status AS ps
ON p.PM_STATUS_ID = ps.PS_ID
LEFT JOIN trader.product_ledger AS pl
ON pl.PL_PRODUCTCODE = p.PM_PRODUCTCODE
WHERE p.PM_PRODUCTCODE = '{product_code}'
GROUP BY p.PM_PRODUCTCODE
) AS Ex1
SELECT PM_PRODUCTCODE, meta_can_order_message
FROM (
SELECT
p.PM_PRODUCTCODE,
CASE
WHEN p.PM_IS_WEB = 0
OR p.PM_Status_id IN (3, 7, 8, 9, 11)
THEN 'Archived'
WHEN p.PM_STATUS_ID IN (4, 1)
AND stock.stock_level > 0
THEN 'In Stock'
WHEN p.PM_STATUS_ID = 4
AND stock.stock_level <= 0
THEN 'In Stock'
WHEN p.PM_STATUS_ID = 1
AND stock.stock_level <= 0
THEN 'Supplier Order'
WHEN p.PM_Status_id IN (5, 6, 12)
AND stock.stock_level > 0
THEN 'In Stock'
WHEN p.PM_Status_id IN (5, 6, 12)
AND stock.stock_level <= 0
THEN 'Archived'
ELSE 'Error'
END AS meta_can_order_message
FROM trader.products AS p
LEFT JOIN (
SELECT
pl.PL_PRODUCTCODE,
COALESCE(SUM(CASE
WHEN pl_store_id = '299'
AND pl_location_id = 1
THEN pl_qtyonhand
WHEN pl_store_id != '299'
AND pl_location_id = 0
THEN pl_qtyonhand
ELSE 0
END), 0) AS stock_level
FROM trader.product_ledger AS pl
WHERE pl.PL_SUBTYPE1 = ''
AND pl.PL_SUBTYPE2 = ''
GROUP BY pl.PL_PRODUCTCODE
) AS stock
ON stock.PL_PRODUCTCODE = p.PM_PRODUCTCODE
) AS tbl1
WHERE meta_can_order_message != 'Archived'
fullhouse.axion)Products queued for push to Shopify are staged in prestage_shopify:
CREATE TABLE fullhouse.axion.prestage_shopify (
ProductCode CHAR(255) PRIMARY KEY,
Status CHAR(50),
Guid INT,
TimeScheduled DATETIME,
TimeCompleted DATETIME,
Payload TEXT
)
299 = main warehouse. pl_location_id = 1 = primary locationpl_location_id = 0 = primary locationPL_SUBTYPE1 = '' AND PL_SUBTYPE2 = '' count(L * W * H) / 4000 (FullHouse carrier divisor)meta_can_order_message — controls display statusm_can_order_message — per-variant overrideAll 76 SQL queries are defined in ObjServiceFHShopify.yaml and loaded
via self.get_queries("name").format(...). Grouped by function:
Configuration:
get_cache_max_age, get_dry_run_mode, upsert_cache_max_age
Colour System:
get_colour_types, get_product_colours, get_alternate_colour_suffixes,
get_product_colour_list
Promotions:
get_active_promotions, get_multi_promo_products, get_promotion_data_single,
get_promotion_data_bulk, get_promotions_window, get_promotion_products_by_ids,
get_promotion_dates, get_product_set_promo
Product Information:
get_product_status, get_product_alternates, get_product_dimensions,
get_warranty_data, get_warranty_fields, get_extended_warranty_cost,
get_extended_warranty_code, get_extended_warranty_price, get_long_description,
get_product_feature, get_alternate_descriptions, get_alternate_guid,
get_primary_alternate, get_web_enabled_products, get_product_set_old,
get_product_set_active
Shopify Variant CRUD:
get_stale_variants, count_stale_variants, count_total_variants,
get_shopify_variants_by_sku, get_variant_product_id, insert_variant_stub,
insert_variant_all_stub, insert_variant_reject, update_variant_reject,
insert_variant_for_meta, update_variant_stockitem, delete_variant_by_id,
replace_variant_after_create
Shopify Product CRUD:
get_product_id_by_sku, get_sku_by_product_id, get_product_id_for_delete,
get_duplicate_products, delete_product_cleanup, delete_variants_all_by_product,
upsert_product_graphql, upsert_variant_graphql, check_existing_product,
update_web_status_flag, get_all_inventory_product_ids
Inventory & Cache:
upsert_inventory, get_inventory_cache, update_inventory_sync_time
Images:
get_cached_image_hash, save_image_hash, replace_image_record
Metafields:
replace_metafield, get_metafield_id, delete_meta_by_variant
Pricing:
upsert_price_record
Locations:
replace_location, get_variants_with_stockitem, check_location_exists,
replace_location_activation
Dry-Run:
get_dryrun_summary, get_dryrun_run_data, insert_dryrun_variant,
delete_dryrun_before, truncate_dryrun
Misc:
read_service_connection, normalize_promotion_dates
14 GraphQL operations defined as module-level constants:
| Constant | Operation | Purpose |
|---|---|---|
GRAPHQL_PRODUCT_CREATE |
productCreate |
Create product with variants |
GRAPHQL_PRODUCT_VARIANTS_BULK_CREATE |
productVariantsBulkCreate |
Add variants after create |
GRAPHQL_PRODUCT_UPDATE |
productUpdate |
Update existing product |
GRAPHQL_PRODUCT_DELETE |
productDelete |
Delete product permanently |
GRAPHQL_PRODUCT_QUERY |
product |
Fetch product + variants + metafields |
GRAPHQL_VARIANT_UPDATE |
productVariantUpdate |
Update variant price |
GRAPHQL_METAFIELD_SET |
metafieldsSet |
Batch set metafields |
GRAPHQL_IMAGE_CREATE |
productCreateMedia |
Batch upload images |
GRAPHQL_PRODUCT_MEDIA_QUERY |
productMedia |
Check existing images |
GRAPHQL_INVENTORY_ACTIVATE |
inventoryActivate |
Activate stock at location |
GRAPHQL_LOCATIONS_QUERY |
locations |
List warehouses |
GRAPHQL_PRODUCT_STATUS_QUERY |
productStatus |
Sync status back |
GRAPHQL_ORDERS_QUERY |
orders |
Retrieve orders |
GRAPHQL_PRODUCTS_QUERY |
products |
List all products (paginated) |