authen.public

Parameters

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

Definition

WITH batch as (
     	SELECT id, batch_name, batch_type, output_type, output_target, enabled	
     	FROM batch_report b
        WHERE ($1 <> -1) and (b.id = $1)
        union all 
        select -1, null, null, null, null, true where $1 = -1       
     ), items AS (
        SELECT r.category, r.id AS report_id, coalesce(i.report_alias, r.report_name) as report_name, i.item_id, --i.option_values, 
        	r."options"  is not null as has_options,	
            COALESCE(i.enabled, false) as enabled,
            COALESCE(i.duplicated, FALSE) AS duplicated  
        FROM reports r 
            LEFT JOIN batch_report_items i ON r.id = i.report_id AND i.batch_id = $1 
        WHERE (r.report_filename IS NOT NULL) AND (COALESCE(i.enabled, false) = true OR NOT only_active)
        --ORDER BY r.report_name, i.item_id
     ), batch_data AS (
        SELECT id, batch_name, batch_type, output_type, output_target, enabled,
        
            (SELECT COALESCE(json_agg(items), '[]')
             FROM (SELECT row_number() over(order by report_name, duplicated, item_id) as seq, 
             			report_id, report_name, item_id, has_options, enabled, duplicated 
                   FROM items WHERE category = 'financial' 
                   ORDER BY report_name, duplicated, item_id) AS items             
            ) AS financial_reports,
        
            (SELECT COALESCE(json_agg(items), '[]')
             FROM (SELECT row_number() over(order by report_name, duplicated, item_id) as seq, 
             			report_id, report_name, item_id,  has_options, enabled, duplicated  
                   FROM items WHERE category = 'reservation' 
                   ORDER BY report_name, duplicated, item_id) AS items             
            ) AS reservation_reports,
        
            (SELECT COALESCE(json_agg(items), '[]')
             FROM (SELECT row_number() over(order by report_name, duplicated, item_id) as seq, 
             			report_id, report_name, item_id, has_options, enabled, duplicated  
                   FROM items WHERE category = 'registration' 
                   ORDER BY report_name, duplicated, item_id) AS items             
            ) AS registration_reports,
        
            (SELECT COALESCE(json_agg(items), '[]')
             FROM (SELECT row_number() over(order by report_name, duplicated, item_id) as seq, 
             			report_id, report_name, item_id, has_options, enabled, duplicated  
                   FROM items WHERE category = 'marketing_analysis' 
                   ORDER BY report_name, duplicated, item_id) AS items             
            ) AS marketing_reports
            
        FROM batch b
    )
    SELECT row_to_json(batch_data) FROM batch_data

sp_get_batch_report_details_js

Parameters

Definition