aonestar.public

Parameters

Name	Type	Mode
period1	text	IN
period2	text	IN (DEFAULT NULL)
period3	text	IN (DEFAULT NULL)
period4	text	IN (DEFAULT NULL)
date_ref	date	IN (DEFAULT NULL)
segment	text	IN (DEFAULT NULL)
segment_group	text	IN (DEFAULT NULL)
sort_period	integer	IN (DEFAULT NULL)
sort_column	text	IN (DEFAULT NULL)
limit_rows	integer	IN (DEFAULT NULL)
filter_text	text	IN (DEFAULT NULL)
revenue_calc_type	integer	IN (DEFAULT fn_revenue_calc_type())
seq	integer	OUT
group_id	integer	OUT
group_name	text	OUT
id	integer	OUT
name	text	OUT
rooms1	integer	OUT
revenue1	t_money	OUT
adult1	integer	OUT
child1	integer	OUT
infant1	integer	OUT
arr_rooms1	integer	OUT
arr_adult1	integer	OUT
arr_child1	integer	OUT
arr_infant1	integer	OUT
adr1	t_money	OUT
rooms2	integer	OUT
revenue2	t_money	OUT
adult2	integer	OUT
child2	integer	OUT
infant2	integer	OUT
arr_rooms2	integer	OUT
arr_adult2	integer	OUT
arr_child2	integer	OUT
arr_infant2	integer	OUT
adr2	t_money	OUT
rooms3	integer	OUT
revenue3	t_money	OUT
adult3	integer	OUT
child3	integer	OUT
infant3	integer	OUT
arr_rooms3	integer	OUT
arr_adult3	integer	OUT
arr_child3	integer	OUT
arr_infant3	integer	OUT
adr3	t_money	OUT
rooms4	integer	OUT
revenue4	t_money	OUT
adult4	integer	OUT
child4	integer	OUT
infant4	integer	OUT
arr_rooms4	integer	OUT
arr_adult4	integer	OUT
arr_child4	integer	OUT
arr_infant4	integer	OUT
adr4	t_money	OUT

Definition

DECLARE 
    STMT text = $$
    WITH summ AS (
        SELECT
            COALESCE(p1.group_id, p2.group_id, p3.group_id) AS group_id, 
            COALESCE(p1.group_name, p2.group_name, p3.group_name) AS group_name, 
            COALESCE(p1.id, p2.id, p3.id) AS id, 
            COALESCE(p1.name, p2.name, p3.name) AS name, 
            COALESCE(p1.group_id, p2.group_id, p3.group_id) = 999999 AS is_other_group,
            COALESCE(p1.id, p2.id, p3.id) = 999999 AS is_other_item,
            sum(p1.rooms) rooms1, sum(p1.revenue) revenue1, sum(p1.adult) adult1, sum(p1.child) child1, sum(p1.infant) infant1, sum(p1.adr) adr1, sum(p1.arr_rooms) arr_rooms1, sum(p1.arr_adult) arr_adult1, sum(p1.arr_child) arr_child1, sum(p1.arr_infant) arr_infant1,
            sum(p2.rooms) rooms2, sum(p2.revenue) revenue2, sum(p2.adult) adult2, sum(p2.child) child2, sum(p2.infant) infant2, sum(p2.adr) adr2, sum(p2.arr_rooms) arr_rooms2, sum(p2.arr_adult) arr_adult2, sum(p2.arr_child) arr_child2, sum(p2.arr_infant) arr_infant2,
            sum(p3.rooms) rooms3, sum(p3.revenue) revenue3, sum(p3.adult) adult3, sum(p3.child) child3, sum(p3.infant) infant3, sum(p3.adr) adr3, sum(p3.arr_rooms) arr_rooms3, sum(p3.arr_adult) arr_adult3, sum(p3.arr_child) arr_child3, sum(p3.arr_infant) arr_infant3,
            sum(p4.rooms) rooms4, sum(p4.revenue) revenue4, sum(p4.adult) adult4, sum(p4.child) child4, sum(p4.infant) infant4, sum(p4.adr) adr4, sum(p4.arr_rooms) arr_rooms4, sum(p4.arr_adult) arr_adult4, sum(p4.arr_child) arr_child4, sum(p4.arr_infant) arr_infant4
        FROM sp_productivity($1, $5, 
                             segment := $6, 
                             segment_group := $7, 
                             filter_text := $8, 
                             limit_rows := 99999,
                             revenue_calc_type := $9) AS P1  

             FULL OUTER JOIN sp_productivity($2, $5, 
                                             segment := $6, 
                                             segment_group := $7, 
                                             filter_text := $8, 
                                             limit_rows := 99999,
                                             revenue_calc_type := $9) AS P2 
             ON P1.id = P2.id AND P1.group_id = P2.group_id    
             
             FULL OUTER JOIN sp_productivity($3, $5, 
                                             segment := $6, 
                                             segment_group := $7, 
                                             filter_text := $8, 
                                             limit_rows := 99999,
                                             revenue_calc_type := $9) AS P3 
             ON COALESCE(P1.id,P2.id) = P3.id AND COALESCE(P1.group_id, P2.group_id) = P3.group_id

             FULL OUTER JOIN sp_productivity($4, $5, 
                                             segment := $6, 
                                             segment_group := $7, 
                                             filter_text := $8, 
                                             limit_rows := 99999,
                                             revenue_calc_type := $9) AS P4 
            ON COALESCE(P1.id,P2.id,P3.id) = P4.id AND COALESCE(P1.group_id, P2.group_id, P3.group_id) = P4.group_id
        GROUP BY 1,2,3,4,5,6
    ), items AS (   
        SELECT ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY is_other_item, %s NULLS LAST, name) AS seq, * 
        FROM summ
    )
     SELECT                          
        CASE WHEN is_other_item OR seq > %L THEN 999 ELSE seq END::int AS seq,
        group_id, group_name,
        CASE WHEN is_other_item OR seq > %L THEN 999999 ELSE id END AS id,
        CASE WHEN is_other_item OR seq > %L THEN 'Others/Unknown' ELSE name END AS name,
        COALESCE(sum(rooms1),0)::int AS p1_rooms, 
        COALESCE(sum(revenue1),0)::t_money AS p1_revenue, 
        COALESCE(sum(adult1),0)::int AS p1_adult, 
        COALESCE(sum(child1),0)::int AS p1_child, 
        COALESCE(sum(infant1),0)::int AS p1_infant, 
        COALESCE(sum(arr_rooms1),0)::int AS p1_arr_rooms, 
        COALESCE(sum(arr_adult1),0)::int AS p1_arr_adult, 
        COALESCE(sum(arr_child1),0)::int AS p1_arr_child, 
        COALESCE(sum(arr_infant1),0)::int AS p1_arr_infant,
        (sum(revenue1) / nullif(sum(rooms1),0))::t_money AS p1_adr,
        COALESCE(sum(rooms2),0)::int AS p2_rooms, 
        COALESCE(sum(revenue2),0)::t_money AS p2_revenue, 
        COALESCE(sum(adult2),0)::int AS p2_adult, 
        COALESCE(sum(child2),0)::int AS p2_child, 
        COALESCE(sum(infant2),0)::int AS p2_infant, 
        COALESCE(sum(arr_rooms2),0)::int AS p2_arr_rooms, 
        COALESCE(sum(arr_adult2),0)::int AS p2_arr_adult, 
        COALESCE(sum(arr_child2),0)::int AS p2_arr_child, 
        COALESCE(sum(arr_infant2),0)::int AS p2_arr_infant,
        (sum(revenue2) / nullif(sum(rooms2),0))::t_money AS p2_adr,
        COALESCE(sum(rooms3),0)::int AS p3_rooms, 
        COALESCE(sum(revenue3),0)::t_money AS p3_revenue, 
        COALESCE(sum(adult3),0)::int AS p3_adult, 
        COALESCE(sum(child3),0)::int AS p3_child, 
        COALESCE(sum(infant3),0)::int AS p3_infant, 
        COALESCE(sum(arr_rooms3),0)::int AS p3_arr_rooms, 
        COALESCE(sum(arr_adult3),0)::int AS p3_arr_adult, 
        COALESCE(sum(arr_child3),0)::int AS p3_arr_child, 
        COALESCE(sum(arr_infant3),0)::int AS p3_arr_infant,
        (sum(revenue3) / nullif(sum(rooms3),0))::t_money AS p3_adr,
        COALESCE(sum(rooms4),0)::int AS p4_rooms, 
        COALESCE(sum(revenue4),0)::t_money AS p4_revenue, 
        COALESCE(sum(adult4),0)::int AS p4_adult, 
        COALESCE(sum(child4),0)::int AS p4_child, 
        COALESCE(sum(infant4),0)::int AS p4_infant, 
        COALESCE(sum(arr_rooms4),0)::int AS p4_arr_rooms, 
        COALESCE(sum(arr_adult4),0)::int AS p4_arr_adult, 
        COALESCE(sum(arr_child4),0)::int AS p4_arr_child, 
        COALESCE(sum(arr_infant4),0)::int AS p4_arr_infant,
        (sum(revenue4) / nullif(sum(rooms4),0))::t_money AS p4_adr
    FROM items
    GROUP BY is_other_group,1,2,3,4,5
    ORDER BY is_other_group, group_name ,1,5;
    $$;
BEGIN 
    limit_rows  := coalesce(limit_rows, IIF(segment IN ('date','month','year'), 999999, 5));
    sort_column := CASE
        WHEN sort_column IS NULL THEN IIF(segment IN ('date','month','year'), 'id', 'rooms'||COALESCE(sort_period::TEXT,'1')||' DESC')
        WHEN lower(sort_column) IN ('id','name','group_id','group_name') THEN sort_column
        ELSE sort_column||COALESCE(sort_period::TEXT,'')||' DESC'
    END; 

    RAISE NOTICE 'sort: %, limit: %', sort_column, limit_rows;

    stmt := format (stmt, sort_column, limit_rows, limit_rows, limit_rows);
    RAISE NOTICE '%', STMT;

    RETURN QUERY
    EXECUTE stmt USING period1, period2, period3, period4, date_ref, segment, segment_group, filter_text, revenue_calc_type;

END

sp_productivity_compare

Parameters

Definition