59 lines
2.2 KiB
Markdown
59 lines
2.2 KiB
Markdown
# Stock Dashboard - MySQL Tables
|
|
|
|
Exported from Omni Accounts Firebird database (`STARGASHO.FDB`).
|
|
|
|
## Tables
|
|
|
|
### products.sql — `STK_STOCKITEM`
|
|
Product master table. One row per product/stock item.
|
|
Key columns: `STOCKCODE`, `DESCRIPTION`, `CATEGORY`, `PRODUCTGROUP`, `UOM`, `COSTPRICE`, `SELLINGPRICE1`, `ISACTVE`
|
|
|
|
### categories.sql — `STK_STOCKCATEGORY`
|
|
Lookup table for product categories. Join `STK_STOCKITEM.CATEGORY` → `STCKCTGRYCDE`.
|
|
Key columns: `STCKCTGRYCDE`, `STCKCTGRYDESC`
|
|
|
|
### suppliers.sql — `PL_SUPPLIERACCOUNT`
|
|
Supplier master table. One row per supplier account.
|
|
Key columns: `SUPLCDE`, `SUPLNME`, address fields, contact fields
|
|
|
|
### supplier_invoices.sql — `PL_BILL`
|
|
Supplier invoice headers. One row per invoice received from a supplier.
|
|
Key columns: `REFNO`, `ACCNO` (→ SUPLCDE), `DOCDTETME`, `SUPLNME`
|
|
|
|
### supplier_invoice_lines.sql — `PL_BILLTRAN`
|
|
Supplier invoice line items. One row per product line on a supplier invoice.
|
|
Key columns: `REFNO` (→ PL_BILL), `STOCKCODE` (→ STK_STOCKITEM), `QTYTOINVOICE`, `COSTPRICE`
|
|
|
|
### customers.sql — `SL_CUSTOMERACCOUNT`
|
|
Customer/debtor master table. One row per customer account.
|
|
Key columns: `DBTRCDE`, `DBTRNME`, `REGION`, `DBTRCTGRY`, `CRLMT`
|
|
|
|
### customer_invoices.sql — `SL_SALESINVOICE`
|
|
Customer invoice headers. One row per invoice issued to a customer.
|
|
Key columns: `REFNO`, `ACCNO` (→ DBTRCDE), `DOCDTETME`, `DBTRNME`
|
|
|
|
### customer_invoice_lines.sql — `SL_SALESINVOICETRAN`
|
|
Customer invoice line items. One row per product line on a customer invoice.
|
|
Key columns: `REFNO` (→ SL_SALESINVOICE), `STOCKCODE` (→ STK_STOCKITEM), `QTYTOINVOICE`, `SELLINGPRICE`, `COSTPRICE`
|
|
|
|
## Joining the Tables
|
|
|
|
**Supplier purchases per product:**
|
|
```sql
|
|
SELECT p.DESCRIPTION, s.SUPLNME, il.QTYTOINVOICE, il.COSTPRICE, i.DOCDTETME
|
|
FROM supplier_invoice_lines il
|
|
JOIN supplier_invoices i ON il.REFNO = i.REFNO
|
|
JOIN suppliers s ON i.ACCNO = s.SUPLCDE
|
|
JOIN products p ON il.STOCKCODE = p.STOCKCODE;
|
|
```
|
|
|
|
**Customer sales per product:**
|
|
```sql
|
|
SELECT p.DESCRIPTION, c.DBTRNME, il.QTYTOINVOICE, il.SELLINGPRICE, i.DOCDTETME
|
|
FROM customer_invoice_lines il
|
|
JOIN customer_invoices i ON il.REFNO = i.REFNO
|
|
JOIN customers c ON i.ACCNO = c.DBTRCDE
|
|
JOIN products p ON il.STOCKCODE = p.STOCKCODE;
|
|
```
|
|
|
|
|