authen.public

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;

sp_get_reports_list_js

Parameters

Definition