authen.public

Parameters

Name	Type	Mode
prop_id	integer	IN
batch_id	integer	IN (DEFAULT NULL)
only_active	boolean	IN (DEFAULT false)

Definition

WITH batch AS (
        SELECT id, batch_name, output_type, output_target, enabled,
            (SELECT json_agg(items)
             FROM (SELECT r.id AS report_id, r.report_name, i.item_id, i.report_alias, i.option_values, 
                        COALESCE(i.enabled, false) as enabled  
                   FROM reports r LEFT JOIN batch_report_items i ON r.id = i.report_id AND i.batch_id = b.id 
                   WHERE category = 'financial' AND (r.report_type = 'report') AND (r.report_filename IS NOT NULL)
                  ) AS items
            ) AS financial,
            (SELECT json_agg(items)
             FROM (SELECT r.id AS report_id, r.report_name, i.item_id, i.report_alias, i.option_values, 
                        COALESCE(i.enabled, false) as enabled 
                   FROM reports r LEFT JOIN batch_report_items i ON r.id = i.report_id AND i.batch_id = b.id 
                   WHERE category = 'reservation' AND (r.report_type = 'report') AND (r.report_filename IS NOT NULL)
                  ) AS items
            ) AS reservation,
            (SELECT json_agg(items)
             FROM (SELECT r.id AS report_id, r.report_name, i.item_id, i.report_alias, i.option_values, 
                        COALESCE(i.enabled, false) as enabled  
                   FROM reports r LEFT JOIN batch_report_items i ON r.id = i.report_id AND i.batch_id = b.id 
                   WHERE category = 'registration' AND (r.report_type = 'report') AND (r.report_filename IS NOT NULL)
                  ) AS items
            ) AS registration,
            (SELECT json_agg(items)
             FROM (SELECT r.id AS report_id, r.report_name, i.item_id, i.report_alias, i.option_values, 
                        COALESCE(i.enabled, false) as enabled  
                   FROM reports r LEFT JOIN batch_report_items i ON r.id = i.report_id AND i.batch_id = b.id 
                   WHERE category = 'marketing_analysis' AND (r.report_filename IS NOT NULL)
                  ) AS items
            ) AS marketing
        FROM batch_report b
        WHERE (prop_id = $1) AND ($2 IS NULL OR b.id = $2) AND (b.enabled OR NOT only_active) 
    )
    SELECT json_agg(batch) FROM batch

sp_get_batch_report_js

Parameters

Definition