home-launcher/util-sqlpage/code/form-fuel.sql

--TODO: show barcode at top
--SELECT 'dynamic' AS component, sqlpage.run_sql('sqlpage/QR.sql') AS properties;
SET filter_options = (
  SELECT json_agg(json_build_object('name', k, 'options', o))
  FROM (
    SELECT o.k, json_agg(
      json_build_object('label', v, 'value', v, 'selected', c == 1)
      ORDER BY v)
    FROM (
      SELECT 'store' AS k, DISTINCT store AS v, count(DISTINCT store) AS c FROM code_detail
      UNION
      SELECT 'value' AS k, DISTINCT value AS v, count(DISTINCT value) AS c FROM code_detail 
    ) AS o
    WHERE v IS NOT NULL
    GROUP BY o.k
  ) q(k, o)
);
SET :expiry = (SELECT expiry FROM code_detail WHERE hash = $hash);
SET :value = (SELECT value FROM code_detail WHERE hash = $hash);
SET :store = (SELECT store FROM code_detail WHERE hash = $hash);
SET :used = (SELECT used FROM code_detail WHERE hash = $hash);

SELECT 'form' AS component
, '/code.sql' AS action
, 'Update' AS validate
, $tabler_color AS validate_color
, 'post' AS method
;
SELECT j.name
--, fo.j#>>'{options}' AS label
, j.label
, COALESCE(c.type, j.type) AS type
, j.dropdown AS dropdown
, j.multiple
, COALESCE(c.width, j.width) AS width
, o.j#>'{options}' AS options
, COALESCE(j.value, v.value) AS value
, CASE j.type
    WHEN 'checkbox' THEN v.value::bool
    ELSE NULL
  END AS checked
, j.formaction
FROM json_populate_recordset(null::sqlpage_filter_type, sqlpage.read_file_as_text('code/json/filters.json')::json) j
LEFT JOIN json_populate_recordset(null::sqlpage_filter_type, $filter_config::json) c
USING (name)
LEFT JOIN json_array_elements($filter_options::json) o(j)
ON (o.j#>>'{name}') = j.name
LEFT JOIN json_each_text(sqlpage.variables()::json) v(key, value)
ON v.key = j.name
;
INSERT INTO code_detal(hash, type, value, expiry, used)
VALUES ($hash, $type, $value, $expiry, $used)
ON CONFLICT DO
UPDATE SET
  type = excluded.type,
  value = excluded.value,
  expiry = excluded.expiry,
  used = excluded.used
;