authen
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_get_reports_list_js
Parameters
Name
Type
Mode
i_prop_code
citext
IN
i_prefix_url_pms_file
text
IN (DEFAULT NULL)
user_name
citext
IN (DEFAULT NULL)
Definition
declare json_result json; prefix_url text; storage_url text; _prop_id int; _user_id int; begin prefix_url := fn_get_config(null, 'PREFIX_URL_PMS_FILE'); --'http://file.fromas.local'; -- storage_url := fn_get_config(null, 'FILE_STORAGE_URL'); _prop_id := (SELECT id FROM property WHERE code = i_prop_code); _user_id := (SELECT u.id FROM user_property up LEFT JOIN users u ON u.id = up.user_id WHERE up.prop_id = _prop_id AND u.user_name = $3); RAISE NOTICE 'prop_id = %, user_id = %', _prop_id, _user_id; WITH categories AS ( SELECT * FROM (VALUES(1, 'financial', 'Financial'), (2, 'reservation', 'Reservation'), (3, 'registration', 'Registration'), (4, 'marketing_analysis', 'Marketing'), (5, 'masterfile', 'Master File') ) AS C(seq, category, category_name) ), report_list AS ( SELECT r.id, r.category, r.path_url, COALESCE(pr.report_name, r.report_name) AS report_name, COALESCE(pr.report_filename, r.report_filename) as report_filename, COALESCE(pr."options", r."options") IS NOT NULL as has_options, (pr.report_name is not null) as override_name, (pr.report_filename is not null) as override_template, (pr."options" is not null) as override_options FROM reports r LEFT JOIN property_reports pr on pr.report_id= r.id AND pr.prop_id = _prop_id LEFT JOIN user_permission pm ON pm.user_id = _user_id AND pm.prop_id = _prop_id AND pm.code = r.permission_code WHERE (r.report_type = 'report') AND (r.report_filename IS NOT NULL) --AND (pr.prop_id = _prop_id) AND (_user_id IS NULL OR pm.code IS NOT NULL) ), report_data AS ( SELECT r.id, r.category, r.report_name, json_build_object( 'id', r.id, 'name', r.report_name, 'filename', r.report_filename, 'path_url', prefix_url || r.path_url, 'template', storage_url ||'report_src/'|| r.report_filename ||case when (r.report_filename like '%.frx') then '' else '.jrxml' end, 'options', has_options, 'override_name', override_name, 'override_template', override_template, 'override_options', override_options ) AS report FROM report_list r ), report_groups AS ( SELECT c.category, c.category_name, c.seq AS "sequence", --array_agg(report ORDER BY report_name) AS reports (SELECT COALESCE(json_agg(report ORDER BY report_name), '[]') FROM report_data rd WHERE rd.category = c.category) AS reports FROM categories c -- LEFT JOIN report_data r ON r.category = c.category --GROUP BY 1,2,3 ORDER BY "sequence" ) SELECT COALESCE(JSON_AGG(report_groups), '[]') FROM report_groups INTO json_result; RETURN json_result; END;