aonestar.public

Parameters

Name	Type	Mode
chart_ids	text[]	IN
params	json	IN
chart_id	text	IN
params	json	IN (DEFAULT NULL)

Definition

DECLARE 
    period_start date;
    period_end date;
    compare_start date;
    compare_end date;
    chart_data jsonb;
    --results jsonb;
    query TEXT;
    is_object bool;
BEGIN 
    period_end   := COALESCE((params->>'end_date')::date, fn_system_date());
    period_start := COALESCE((params->>'start_date')::date, period_end-30);
    compare_end   := period_start - 1;
    compare_start := compare_end - (period_end - period_start);
    --RAISE NOTICE 'period: % - %, compare: % - %', period_start, period_end, compare_start, compare_end;
 
    SELECT c.query, FALSE --c.chart_template IS NOT NULL 
    FROM chart c 
    WHERE c.id = $1 
    INTO query, is_object;
 
    CASE chart_id 
        WHEN 'room-statistic' THEN  
            EXECUTE query 
            INTO chart_data 
            USING period_start, period_end, compare_start, compare_end;
            RETURN jsonb_build_object('chart_id', chart_id, 'chart_data', chart_data);
        WHEN 'performance-compare' THEN 
            query := format(query,  
                            --period_start, period_end, 
                            params->>'table_name', 
                            params->>'key_column',  
                            params->>'sort_column', 
                            params->>'limit');
            --RAISE NOTICE '%', query;
            EXECUTE query
            INTO chart_data
            USING period_start, period_end;
            RETURN jsonb_build_object('chart_id', chart_id, 'chart_data', chart_data);
        WHEN 'dashboard' THEN  
            RETURN array_to_json(ARRAY[sp_get_chart_data('room-statistic', $2),
                         sp_get_chart_data('performance-geo', $2),
                         --sp_get_chart_data('performance-compare', $2),
                         sp_get_chart_data('recent-activity')]);
        ELSE 
            EXECUTE query
            INTO chart_data
            USING period_start, period_end, compare_start, compare_end;
            --RETURN jsonb_build_object('chart_id', chart_id, 'chart_object', chart_data);
            RETURN jsonb_build_object('chart_id', chart_id, IIF(is_object, 'chart_object', 'chart_data'), chart_data);
    END CASE;
END