grocery-manager/sqlpage/transactions.sql

SELECT
  'dynamic' as component,
  sqlpage.run_sql('cookie.sql') AS properties
;

SELECT
  'dynamic' as component,
  sqlpage.run_sql('nav.sql') AS properties
;

SELECT
  'table' AS component,
  TRUE AS sort,
  TRUE AS search,
  TRUE AS striped_rows,
  TRUE AS small,
  'Price' AS align_right,
  'Quantity' AS align_right,
  'Unit' AS align_right,
  'Notice' AS markdown
;

DROP TABLE IF EXISTS sqlpage_txn;
CREATE TEMPORARY TABLE IF NOT EXISTS sqlpage_txn AS (
SELECT
  to_char(
    (ts AT TIME ZONE 'UTC') AT TIME ZONE 'Pacific/Auckland', --COALESCE('$ParameterTZ', 'Pacific/Auckland'),
    'Mon DD HH24:MI:SS'
  ) AS "Time",
  t.description AS Description,
  s.code AS "Store",
  p.name AS "Product",
  c.name AS "Category",
  g.name AS "Group",
  CASE WHEN t.organic THEN 'yes' ELSE 'no' END AS "Organic",
  to_char(t.price, 'FM999999999.00') AS "Price",
  --to_char(quantity, '9.'||repeat('9',3-1)||'EEEE') AS Quantity,
  to_char(t.price, 'FM999999999.000') AS "Quantity",
  u.name AS "Unit"
FROM transactions t
JOIN products p ON (product_id = p.id)
JOIN categories c ON (category_id = c.id)
JOIN groups g ON (group_id = g.id)
JOIN stores s ON (store_id = s.id)
JOIN units u ON (t.unit_id = u.id)
JOIN sqlpage_options op ON op.type = 'product' AND p.name = op.name
JOIN sqlpage_options oc ON oc.type = 'category' AND c.name = oc.name
JOIN sqlpage_options og ON og.type = 'group' AND g.name = og.name
WHERE
  COALESCE(op.selected, oc.selected, og.selected, TRUE)
AND
  op.session = sqlpage.cookie('session')
AND
  oc.session = sqlpage.cookie('session')
AND
  og.session = sqlpage.cookie('session')
LIMIT 100);
SELECT * FROM sqlpage_txn;

SELECT 
  '# No Data' AS "Notice"
WHERE NOT EXISTS(SELECT * FROM sqlpage_txn LIMIT 1);