util/util-sqlpage/code/recent.sql

SELECT 'dynamic' AS component, sqlpage.run_sql('sqlpage/theme.sql') AS properties;
SET ":filter_config" = '[
  {"name": "expiry", "type": "hidden"},
  {"name": "value", "type": "hidden"},
  {"name": "type[]", "label": "Type",
   "type": "select", "dropdown": true,
   "multiple": true,
   "width": 3
  },
  {"name": "store[]", "create_new": false,
   "multiple": true,
   "width": 3
  },
  {"name": "expired", "label": "Show Expired",
   "type": "checkbox", "value": "true",
   "width": 2
  },
  {"name": "used", "label": "Show Used",
   "type": "checkbox", "value": "true",
   "width": 2
  }
]';
SET ":validate" = 'Apply';
SET ":action" = 'Apply';
SET ":method" = 'get';
SET ":autofill" = FALSE;
SELECT 'dynamic' AS component, sqlpage.run_sql('code/form.sql') AS properties;

SELECT 'list' AS component;
SELECT COALESCE(type||' ','') || COALESCE(store||' ', '') || COALESCE(expiry, created) AS title
, COALESCE(content->>'content'||' ', '') || COALESCE(content->>'type', '') AS description
, '/code?hash='||c.hash AS link
FROM code c
LEFT JOIN code_detail cd
ON c.hash = cd.hash
WHERE (
    cd.expiry IS NULL OR COALESCE($expired, 'false') = 'true' OR date(cd.expiry) >= date(datetime(CURRENT_TIMESTAMP, 'localtime'))
) AND (
    cd.used IS NULL OR COALESCE($used||'', 'false') = 'true' OR COALESCE(cd.used||'','') = 'false'
) AND (
    CASE COALESCE($type, '')
      WHEN '' THEN TRUE
      ELSE $type IS NULL OR cd.type IN (SELECT value FROM json_each($type))
    END
) AND (
    CASE COALESCE($store, '')
      WHEN '' THEN TRUE
      ELSE $store IS NULL OR cd.store IN (SELECT value FROM json_each($store))
    END
)
ORDER BY expiry, created DESC, type, c.hash NULLS FIRST
;