aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_parse_report_param
Parameters
Name
Type
Mode
param
jsonb
IN
is_batch_report
boolean
IN (DEFAULT false)
param_value
jsonb
IN (DEFAULT NULL)
Definition
declare json_items json = '[]'; param_type text; json_property jsonb = '{}'; itemsLabels jsonb = '[]'; itemsValues jsonb = '[]'; default_value jsonb; date_items json = $$[ {"label": "Today", "value": "TODAY"}, {"label": "Tomorrow", "value": "TOMORROW"}, {"label": "Yesterday", "value": "YESTERDAY"}, {"label": "Next 7 days", "value": "NEXT_7_DAYS"}, {"label": "Next 30 days", "value": "NEXT_30_DAYS"}, {"label": "Last 7 days", "value": "LAST_7_DAYS"}, {"label": "Last 30 days", "value": "LAST_30_DAYS"}, {"label": "Start of month", "value": "START_OF_MONTH"}, {"label": "End of month", "value": "END_OF_MONTH"}, {"label": "Start of year", "value": "START_OF_YEAR"}, {"label": "End of year", "value": "END_OF_YEAR"} ]$$; range_items json = $$[ {"label": "Today", "value": "TODAY"}, {"label": "Tomorrow", "value": "TOMORROW"}, {"label": "Yesterday", "value": "YESTERDAY"}, {"label": "This month", "value": "THIS_MONTH"}, {"label": "Last month", "value": "LAST_MONTH"}, {"label": "Next month", "value": "NEXT_MONTH"}, {"label": "Last 7 days", "value": "LAST_7_DAYS"}, {"label": "Next 7 days", "value": "NEXT_7_DAYS"}, {"label": "Last 30 days", "value": "LAST_30_DAYS"}, {"label": "Next 30 days", "value": "NEXT_30_DAYS"}, {"label": "This year", "value": "THIS_YEAR"}, {"label": "Last year", "value": "LAST_YEAR"}, {"label": "Next year", "value": "NEXT_YEAR"}, {"label": "Month-To-Date", "value": "MTD"}, {"label": "Year-To-Date", "value": "YTD"}, {"label": "Last Year's Month-To-Date", "value": "LMTD"}, {"label": "Last Year's Year-To-Date", "value": "LYTD"} ]$$; day_items_batch json = jsonb_build_array(date_items->0, date_items->1, date_items->2, date_items->7, date_items->8, date_items->9, date_items->10); month_items_batch json = jsonb_build_array(range_items->3, range_items->4, range_items->5); year_items_batch json = jsonb_build_array(range_items->10, range_items->11, range_items->12); day_items json = $$[ {"label":"1","value":1},{"label":"2","value":2},{"label":"3","value":3},{"label":"4","value":4},{"label":"5","value":5}, {"label":"6","value":6},{"label":"7","value":7},{"label":"8","value":8},{"label":"9","value":9},{"label":"10","value":10}, {"label":"11","value":11},{"label":"12","value":12},{"label":"13","value":13},{"label":"14","value":14},{"label":"15","value":15}, {"label":"16","value":16},{"label":"17","value":17},{"label":"18","value":18},{"label":"19","value":19},{"label":"20","value":20}, {"label":"21","value":21},{"label":"22","value":22},{"label":"23","value":23},{"label":"24","value":24},{"label":"25","value":25}, {"label":"26","value":26},{"label":"27","value":27},{"label":"28","value":28},{"label":"29","value":29},{"label":"30","value":30}, {"label":"31","value":31}]$$; month_items json = $$[ {"label":"January","value":1},{"label":"February","value":2},{"label":"March","value":3},{"label":"April","value":4}, {"label":"May","value":5},{"label":"June","value":6},{"label":"July","value":7},{"label":"August","value":8}, {"label":"September","value":9},{"label":"October","value":10},{"label":"November","value":11},{"label":"December","value":12}]$$; begin json_property := param->'property'; default_value := coalesce(param_value, param->'defaultValue'); param_type := lower(param->>'type'); /* Prepare param items and convert special types to simple DropdownList */ case when param_type in ('dropdownlist','radiogroup') then itemsLabels := json_property->'itemLabels'; itemsValues := json_property->'itemValues'; WITH items AS ( SELECT jsonb_array_elements_text(itemsLabels) AS "label", jsonb_array_elements(itemsValues) AS "value" ) SELECT json_agg(items) FROM items INTO json_items; when param_type = 'dropdowndb' then json_items := fn_generate_dropdown_items(json_property->>'sql', json_property->>'itemValueField', json_property->>'itemLabelField'); param_type := 'dropdownlist'; when is_batch_report and param_type in ('date','daterange','day','month','year') then json_items := case param_type when 'date' then date_items when 'daterange' then range_items when 'day' then day_items_batch when 'month' then month_items_batch when 'year' then year_items_batch end; param_type := 'dropdownlist'; when not is_batch_report and param_type in ('day','month','year') then json_items := case param_type when 'day' then day_items when 'month' then month_items when 'year' then ( select json_agg(years order by"value" desc) from (select y::text, y from fn_generate_years(5,4) as y) as years("label", "value") ) end; param_type := 'dropdownlist'; else end case; RETURN CASE param_type WHEN 'dropdownlist' THEN /** * type `DropdownsList` * - passValueLabel = use in Frontend / false = pass itemsValue only * - passValueLabel use DropdownList and DropdownDB only */ jsonb_build_object( 'type', 'DropdownList', 'passValueLabel', lower(param->>'type') = 'dropdowndb', /* <-- In case of DropdownDB, return label to Jasper Report too */ 'name', param->>'name', 'label', param->>'label', 'groupArea', param->>'groupArea', 'groupLayout', param->>'groupLayout', 'defaultValue', default_value, 'mode', coalesce(json_property->>'mode', 'single'), 'items', json_items ) WHEN 'radiogroup' THEN jsonb_build_object( 'type', param->>'type', 'name', param->>'name', 'label', param->>'label', 'groupArea', param->>'groupArea', 'groupLayout', param->>'groupLayout', 'defaultValue', default_value, --'mode', json_property->>'mode', 'items', json_items ) WHEN 'daterange' then /** * type `DateRange` * defaultValue boolean * Don't return Key `property` */ jsonb_build_object( 'type', param->>'type', 'name', concat(json_property->>'startDateName', '|', json_property->>'endDateName'), 'label', param->>'label', 'groupArea', param->>'groupArea', 'groupLayout', param->>'groupLayout', 'startDateName', json_property->>'startDateName', 'endDateName', json_property->>'endDateName', 'defaultStartDate', json_property->>'defaultStartDate', 'defaultEndDate', json_property->>'defaultEndDate' ) else jsonb_build_object( 'type', param->>'type', 'name', param->>'name', 'label', param->>'label', 'groupArea', param->>'groupArea', 'groupLayout', param->>'groupLayout', 'defaultValue', default_value ) END; --RETURN out_param; --EXCEPTION -- WHEN OTHERS THEN -- GET STACKED DIAGNOSTICS -- _state = RETURNED_SQLSTATE, -- _result_msg = MESSAGE_TEXT, -- _detail = PG_EXCEPTION_DETAIL, -- _hint = PG_EXCEPTION_HINT, -- _context = PG_EXCEPTION_CONTEXT; -- RETURN fn_handle_error(_state, _result_msg, _detail, _hint, _context, 'sp_get_reports_option_js', -- jsonb_build_object( -- 'report_id', report_id, -- 'sql_text', sql_text, -- 'parameter', parameters -- ) -- ); END;