aonestar.public

Parameters

Name	Type	Mode
adate	date	IN (DEFAULT NULL)
recalculate	boolean	IN (DEFAULT false)
total_rooms	smallint	OUT
ooo_rooms	smallint	OUT
oos_rooms	smallint	OUT
ooi_rooms	smallint	OUT
occ_rooms	smallint	OUT
occ_adult	smallint	OUT
occ_child	smallint	OUT
occ_infant	smallint	OUT
hsu_rooms	smallint	OUT
hsu_adult	smallint	OUT
hsu_child	smallint	OUT
hsu_infant	smallint	OUT
comp_rooms	smallint	OUT
comp_adult	smallint	OUT
comp_child	smallint	OUT
comp_infant	smallint	OUT
individual_rooms	smallint	OUT
individual_adult	smallint	OUT
individual_child	smallint	OUT
individual_infant	smallint	OUT
individual_revenue	t_money	OUT
individual_revenue_total	t_money	OUT
individual_breakdown	jsonb	OUT
group_rooms	smallint	OUT
group_adult	smallint	OUT
group_child	smallint	OUT
group_infant	smallint	OUT
group_revenue	t_money	OUT
group_revenue_total	t_money	OUT
group_breakdown	jsonb	OUT
arrival_rooms	smallint	OUT
arrival_adult	smallint	OUT
arrival_child	smallint	OUT
arrival_infant	smallint	OUT
departure_rooms	smallint	OUT
departure_adult	smallint	OUT
departure_child	smallint	OUT
departure_infant	smallint	OUT
day_used_qty	smallint	OUT
day_used_adult	smallint	OUT
day_used_child	smallint	OUT
day_used_infant	smallint	OUT
day_used_revenue	t_money	OUT
day_used_revenue_total	t_money	OUT
booking_rooms	smallint	OUT
booking_adult	smallint	OUT
booking_child	smallint	OUT
booking_infant	smallint	OUT
walk_in_rooms	smallint	OUT
walk_in_adult	smallint	OUT
walk_in_child	smallint	OUT
walk_in_infant	smallint	OUT
early_checkout	smallint	OUT
extended_stay	smallint	OUT
canceled_rooms	smallint	OUT
canceled_adult	smallint	OUT
canceled_child	smallint	OUT
canceled_infant	smallint	OUT
no_show_rooms	smallint	OUT
no_show_adult	smallint	OUT
no_show_child	smallint	OUT
no_show_infant	smallint	OUT
no_show_rooms_partial	smallint	OUT
no_show_adult_partial	smallint	OUT
no_show_child_partial	smallint	OUT
no_show_infant_partial	smallint	OUT
tomorrow_expected_arrival	smallint	OUT
tomorrow_expected_departure	smallint	OUT
expected_arrival	smallint	OUT
expected_departure	smallint	OUT

Definition

DECLARE 
    system_date DATE;
BEGIN
    system_date := fn_system_date();
    adate = COALESCE(adate, system_date);
    IF (adate = system_date) OR recalculate THEN 
        --date_ref = adate;
        total_rooms = (SELECT count(id) FROM room);
    
        SELECT count(DISTINCT iif(oo.ooo_type = 'OOO', room_id, NULL)),
               count(DISTINCT iif(oo.ooo_type = 'OOS', room_id, NULL)),
               count(DISTINCT iif(oo.ooo_type = 'OOI', room_id, NULL))
        FROM out_of_order oo
        WHERE $1 BETWEEN oo.start_date AND oo.end_date-1
        INTO ooo_rooms, oos_rooms, ooi_rooms;
        
        --total_rooms := total_rooms - ooi_rooms;

        WITH REV AS (
            SELECT register_id, --room AS amount, 
                rate_amount as total_rev, room as room_rev,
                room, extrabed, abf, lunch, dinner, inclusive, other,
                abf_included, lunch_included, dinner_included, abf_extra, lunch_extra, dinner_extra
            FROM sp_rate_schedule_breakdown(post_date := $1, status := 'I')
        ), INH AS (
            SELECT COUNT(DISTINCT iif(NOT day_used, rg.room_id, NULL)) AS occ_rooms,
                COALESCE(sum(iif(NOT day_used, rg.adult, 0)), 0) AS occ_adult,  
                COALESCE(sum(iif(NOT day_used, rg.child, 0)), 0) AS occ_child,  
                COALESCE(sum(iif(NOT day_used, rg.infant, 0)), 0) AS occ_infant,    
--                COUNT(DISTINCT iif(house_used, rg.room_id, NULL)) AS hsu_rooms,
--                COALESCE(sum(iif(house_used, rg.adult, 0)), 0) AS hsu_adult,    
--                COALESCE(sum(iif(house_used, rg.child, 0)), 0) AS hsu_child,    
--                COALESCE(sum(iif(house_used, rg.infant, 0)), 0) AS hsu_infant,  
--                COUNT(DISTINCT iif(rr.comp AND NOT house_used, rg.room_id, NULL)) AS comp_rooms,
--                COALESCE(sum(iif(rr.comp AND NOT house_used, rg.adult, 0)), 0) AS comp_adult,   
--                COALESCE(sum(iif(rr.comp AND NOT house_used, rg.child, 0)), 0) AS comp_child,   
--                COALESCE(sum(iif(rr.comp AND NOT house_used, rg.infant, 0)), 0) AS comp_infant, 
                COUNT(DISTINCT iif(rp.plan_type = 6, rg.room_id, NULL)) AS hsu_rooms,
                COALESCE(sum(iif(rp.plan_type = 6, rg.adult, 0)), 0) AS hsu_adult,    
                COALESCE(sum(iif(rp.plan_type = 6, rg.child, 0)), 0) AS hsu_child,    
                COALESCE(sum(iif(rp.plan_type = 6, rg.infant, 0)), 0) AS hsu_infant,  
                COUNT(DISTINCT iif(rp.plan_type = 5 or rr.comp, rg.room_id, NULL)) AS comp_rooms,
                COALESCE(sum(iif(rp.plan_type = 5 or rr.comp, rg.adult, 0)), 0) AS comp_adult,   
                COALESCE(sum(iif(rp.plan_type = 5 or rr.comp, rg.child, 0)), 0) AS comp_child,   
                COALESCE(sum(iif(rp.plan_type = 5 or rr.comp, rg.infant, 0)), 0) AS comp_infant, 
                COUNT(DISTINCT iif(NOT rg.is_group, rg.room_id, NULL)) AS individual_rooms,
                COALESCE(sum(iif(NOT rg.is_group, rg.adult, 0)), 0) AS individual_adult,    
                COALESCE(sum(iif(NOT rg.is_group, rg.child, 0)), 0) AS individual_child,    
                COALESCE(sum(iif(NOT rg.is_group, rg.infant, 0)), 0) AS individual_infant,  
                COALESCE(sum(iif(NOT rg.is_group, rev.room_rev, 0)), 0) AS individual_revenue,    
                COALESCE(sum(iif(NOT rg.is_group, rev.total_rev, 0)), 0) AS individual_revenue_total,    
                COUNT(DISTINCT iif(rg.is_group, rg.room_id, NULL)) AS group_rooms,
                COALESCE(sum(iif(rg.is_group, rg.adult, 0)), 0) AS group_adult, 
                COALESCE(sum(iif(rg.is_group, rg.child, 0)), 0) AS group_child, 
                COALESCE(sum(iif(rg.is_group, rg.infant, 0)), 0) AS group_infant,   
                COALESCE(sum(iif(rg.is_group, rev.room_rev, 0)), 0) AS group_revenue, 
                COALESCE(sum(iif(rg.is_group, rev.total_rev, 0)), 0) AS group_revenue_total, 
--                COUNT(DISTINCT iif(rg.walk_in, rg.room_id, NULL)) AS walk_in_rooms,
--                COALESCE(sum(iif(rg.walk_in, rg.adult, 0)), 0) AS walk_in_adult,    
--                COALESCE(sum(iif(rg.walk_in, rg.child, 0)), 0) AS walk_in_child,    
--                COALESCE(sum(iif(rg.walk_in, rg.infant, 0)), 0) AS walk_in_infant,  
                COUNT(DISTINCT iif(rg.booking_id IS NOT NULL, rg.room_id, NULL)) AS booking_rooms,
                COALESCE(sum(iif(rg.booking_id IS NOT NULL, rg.adult, 0)), 0) AS booking_adult, 
                COALESCE(sum(iif(rg.booking_id IS NOT NULL, rg.child, 0)), 0) AS booking_child, 
                COALESCE(sum(iif(rg.booking_id IS NOT NULL, rg.infant, 0)), 0) AS booking_infant,
                jsonb_build_object(
                    'room',         sum(iif(rg.is_group, 0, rev.room)),  
                    'abf',          sum(iif(rg.is_group, 0, rev.abf_included)),
                    'lunch',        sum(iif(rg.is_group, 0, rev.lunch_included)),
                    'dinner',       sum(iif(rg.is_group, 0, rev.dinner_included)),
                    'inclusive',    sum(iif(rg.is_group, 0, rev.inclusive)),
                    'extrabed',     sum(iif(rg.is_group, 0, rev.extrabed)),
                    'abf_extra',    sum(iif(rg.is_group, 0, rev.abf_extra)),
                    'lunch_extra',  sum(iif(rg.is_group, 0, rev.lunch_extra)),
                    'dinner_extra', sum(iif(rg.is_group, 0, rev.dinner_extra)),
                    'other',        sum(iif(rg.is_group, 0, rev.other))
                ) as individual_breakdown,
                jsonb_build_object(
                    'room',         sum(iif(rg.is_group, rev.room, 0)),  
                    'abf',          sum(iif(rg.is_group, rev.abf_included, 0)),
                    'lunch',        sum(iif(rg.is_group, rev.lunch_included, 0)),
                    'dinner',       sum(iif(rg.is_group, rev.dinner_included, 0)),
                    'inclusive',    sum(iif(rg.is_group, rev.inclusive, 0)),
                    'extrabed',     sum(iif(rg.is_group, rev.extrabed, 0)),
                    'abf_extra',    sum(iif(rg.is_group, rev.abf_extra, 0)),
                    'lunch_extra',  sum(iif(rg.is_group, rev.lunch_extra, 0)),
                    'dinner_extra', sum(iif(rg.is_group, rev.dinner_extra, 0)),
                    'other',        sum(iif(rg.is_group, rev.other, 0))
                ) as group_breakdown        
            FROM registration rg 
                LEFT JOIN registration_rates rr ON rr.register_id = rg.id AND rr.charge_date = $1
                LEFT JOIN rate_plan rp on rp.id = rg.rateplan_id
                LEFT JOIN REV ON rev.register_id = rg.id
            WHERE ($1 >= system_date AND rg.status = 'I') 
                    OR ($1 < system_date AND $1 BETWEEN rg.arrival AND rg.departure-1 AND rg.status <> 'X')
                    --OR ($1 = rg.arrival AND rg.arrival = rg.departure)
        ), arr AS (
            SELECT  
                COUNT(DISTINCT rg.room_id) AS arrival_rooms,
                sum(rg.adult) AS arrival_adult, 
                sum(rg.child) AS arrival_child, 
                sum(rg.infant) AS arrival_infant,   
                COUNT(iif(rg.day_used, rg.id, NULL)) AS day_used_qty,
                sum(iif(rg.day_used, rg.adult, 0)) AS day_used_adult,   
                sum(iif(rg.day_used, rg.child, 0)) AS day_used_child,   
                sum(iif(rg.day_used, rg.infant, 0)) AS day_used_infant, 
                sum(iif(rg.day_used, rev.room_rev, 0)) AS day_used_revenue,
                sum(iif(rg.day_used, rev.total_rev, 0)) AS day_used_revenue_total,
                COUNT(DISTINCT iif(rg.walk_in, rg.room_id, NULL)) AS walk_in_rooms,
                COALESCE(sum(iif(rg.walk_in, rg.adult, 0)), 0) AS walk_in_adult,    
                COALESCE(sum(iif(rg.walk_in, rg.child, 0)), 0) AS walk_in_child,    
                COALESCE(sum(iif(rg.walk_in, rg.infant, 0)), 0) AS walk_in_infant  
            FROM registration rg LEFT JOIN REV ON rev.register_id = rg.id
            WHERE rg.arrival = $1 AND rg.status <> 'X'
        ), dep AS (
            SELECT count(DISTINCT rg.room_id) AS departure_rooms,
                count(DISTINCT iif(rg.status='I', rg.room_id, null)) AS expected_departure,
                sum(rg.adult) AS departure_adult,   
                sum(rg.child) AS departure_child,   
                sum(rg.infant) AS departure_infant,
                COUNT(DISTINCT iif(rg.early_checkout, rg.room_id, NULL)) AS early_checkout
            FROM registration rg 
            WHERE rg.departure = $1 AND rg.status <> 'X'
        ), ext AS (
            SELECT count(DISTINCT rg.room_id) AS extended_stay
            FROM registration_log lg LEFT JOIN registration rg ON rg.id = lg.register_id 
            WHERE lg.log_date = $1 AND lg.log_type = 'Extend stay'
        )
        SELECT inh.occ_rooms, inh.occ_adult, inh.occ_child, inh.occ_infant, inh.hsu_rooms, inh.hsu_adult, 
             inh.hsu_child, inh.hsu_infant, inh.comp_rooms, inh.comp_adult, inh.comp_child, inh.comp_infant, 
             inh.individual_rooms, inh.individual_adult, inh.individual_child, inh.individual_infant, 
             inh.individual_revenue, inh.individual_revenue_total, inh.individual_breakdown, inh.group_rooms, 
             inh.group_adult, inh.group_child, inh.group_infant, inh.group_revenue, inh.group_revenue_total, 
             inh.group_breakdown, COALESCE(arr.arrival_rooms,0), COALESCE(arr.arrival_adult,0), 
             COALESCE(arr.arrival_child,0), COALESCE(arr.arrival_infant,0), COALESCE(dep.departure_rooms,0), 
             COALESCE(dep.departure_adult,0), COALESCE(dep.departure_child,0), COALESCE(dep.departure_infant,0),
             COALESCE(arr.day_used_qty,0), COALESCE(arr.day_used_adult,0), COALESCE(arr.day_used_child,0), 
             COALESCE(arr.day_used_infant,0), COALESCE(arr.day_used_revenue,0), COALESCE(arr.day_used_revenue_total,0),
             inh.booking_rooms, inh.booking_adult, inh.booking_child, inh.booking_infant, arr.walk_in_rooms, 
             arr.walk_in_adult, arr.walk_in_child, arr.walk_in_infant, COALESCE(dep.early_checkout,0), 
             COALESCE(ext.extended_stay,0), dep.expected_departure
        FROM inh, arr, dep, ext
        INTO occ_rooms, occ_adult, occ_child, occ_infant, hsu_rooms, hsu_adult, hsu_child, hsu_infant, 
             comp_rooms, comp_adult, comp_child, comp_infant, individual_rooms, individual_adult, 
             individual_child, individual_infant, individual_revenue, individual_revenue_total, 
             individual_breakdown, group_rooms, group_adult, group_child, group_infant, group_revenue, 
             group_revenue_total, group_breakdown, arrival_rooms, arrival_adult, arrival_child, arrival_infant,
             departure_rooms, departure_adult, departure_child, departure_infant, day_used_qty, day_used_adult, 
             day_used_child, day_used_infant, day_used_revenue, day_used_revenue_total, booking_rooms, 
             booking_adult, booking_child, booking_infant, walk_in_rooms, walk_in_adult, walk_in_child, 
             walk_in_infant, early_checkout, extended_stay, expected_departure; 
            
        /* booking cancelation and no-show */
        SELECT COALESCE(sum(iif(bk.status = 'X', bi.qty, 0)), 0) AS canceled_rooms,
             COALESCE(sum(iif(bk.status = 'X', bi.qty * bi.adult, 0)), 0) AS canceled_adult,
             COALESCE(sum(iif(bk.status = 'X', bi.qty * bi.child, 0)), 0) AS canceled_child,
             COALESCE(sum(iif(bk.status = 'X', bi.qty * bi.infant, 0)), 0) AS canceled_infant,
             COALESCE(sum(iif(bk.status = 'N', bi.qty-bi.inhouse_qty, 0)), 0) AS no_show_rooms,
             COALESCE(sum(iif(bk.status = 'N', (bi.qty-bi.inhouse_qty) * bi.adult, 0)), 0) AS no_show_adult,
             COALESCE(sum(iif(bk.status = 'N', (bi.qty-bi.inhouse_qty) * bi.child, 0)), 0) AS no_show_child,
             COALESCE(sum(iif(bk.status = 'N', (bi.qty-bi.inhouse_qty) * bi.infant, 0)), 0) AS no_show_infant,
             COALESCE(sum(iif(bk.status in ('R','C'), bi.noshow_qty, 0)), 0) AS no_show_rooms_partial,
             COALESCE(sum(iif(bk.status in ('R','C'), bi.noshow_qty * bi.adult, 0)), 0) AS no_show_adult_partial,
             COALESCE(sum(iif(bk.status in ('R','C'), bi.noshow_qty * bi.child, 0)), 0) AS no_show_child_partial,
             COALESCE(sum(iif(bk.status in ('R','C'), bi.noshow_qty * bi.infant, 0)), 0) AS no_show_infant_partial
        FROM booking bk INNER JOIN booking_items bi ON bi.booking_id = bk.id 
        WHERE bi.arrival = $1
        INTO canceled_rooms, canceled_adult, canceled_child, canceled_infant, 
             no_show_rooms, no_show_adult, no_show_child, no_show_infant,
             no_show_rooms_partial, no_show_adult_partial, no_show_child_partial, no_show_infant_partial;
    
        /* expected arrival today */
        SELECT sum(bi.qty - bi.inhouse_qty) AS expected_arrival
        FROM booking bk INNER JOIN booking_items bi ON bi.booking_id = bk.id 
        WHERE bi.arrival = $1 and bk.status in ('R','C','I')
        INTO expected_arrival; 
    
        /* expected arrival tomorrow */
        SELECT sum(bi.qty - bi.inhouse_qty) AS tomorrow_expected_arrival
        FROM booking bk INNER JOIN booking_items bi ON bi.booking_id = bk.id 
        WHERE bi.arrival = $1+1 and bk.status in ('R','C','I')
        INTO tomorrow_expected_arrival; 
    
        /* expected departure tomorrow */
        SELECT COUNT(DISTINCT rg.room_id) AS tomorrow_expected_departure
        FROM registration rg 
        WHERE rg.departure = $1+1 AND rg.status <> 'X'
        INTO tomorrow_expected_departure;
    
        tomorrow_expected_arrival := COALESCE(tomorrow_expected_arrival, 0);
        tomorrow_expected_departure := COALESCE(tomorrow_expected_departure, 0);
        RETURN NEXT;
    ELSE
        --RETURN query 
        SELECT date_ref, total_rooms, ooo_rooms, oos_rooms, ooi_rooms, occ_rooms, occ_adult, occ_child, occ_infant, 
            hsu_rooms, hsu_adult, hsu_child, hsu_infant, comp_rooms, comp_adult, comp_child, comp_infant,  
            individual_rooms, individual_adult, individual_child, individual_infant, individual_revenue, 
            individual_revenue_total, individual_breakdown, group_rooms, group_adult, group_child, group_infant, 
            group_revenue, group_revenue_total, group_breakdown, arrival_rooms, arrival_adult, arrival_child,  
            arrival_infant, departure_rooms, departure_adult, departure_child, departure_infant, day_used_qty, 
            day_used_adult, day_used_child, day_used_infant, day_used_revenue, day_used_revenue_total, booking_rooms, 
            booking_adult, booking_child, booking_infant, walk_in_rooms, walk_in_adult, walk_in_child, walk_in_infant, 
            early_checkout, extended_stay, canceled_rooms, canceled_adult, canceled_child, canceled_infant, no_show_rooms, 
            no_show_adult, no_show_child, no_show_infant, tomorrow_expected_arrival, tomorrow_expected_departure,
            no_show_rooms_partial, no_show_adult_partial, no_show_child_partial, no_show_infant_partial
        FROM recapitulate rc
        WHERE rc.date_ref = $1
        INTO adate,
            total_rooms, ooo_rooms, oos_rooms, ooi_rooms, occ_rooms, occ_adult, occ_child, occ_infant, hsu_rooms, 
            hsu_adult, hsu_child, hsu_infant, comp_rooms, comp_adult, comp_child, comp_infant, individual_rooms, 
            individual_adult, individual_child, individual_infant, individual_revenue, individual_revenue_total, 
            individual_breakdown, group_rooms, group_adult, group_child, group_infant, group_revenue, group_revenue_total, 
            group_breakdown, arrival_rooms, arrival_adult, arrival_child, arrival_infant, departure_rooms, departure_adult, 
            departure_child, departure_infant, day_used_qty, day_used_adult, day_used_child, day_used_infant, 
            day_used_revenue, day_used_revenue_total, booking_rooms, booking_adult, booking_child, booking_infant, 
            walk_in_rooms, walk_in_adult, walk_in_child, walk_in_infant, early_checkout, extended_stay, canceled_rooms, 
            canceled_adult, canceled_child, canceled_infant, no_show_rooms, no_show_adult, no_show_child, no_show_infant, 
            tomorrow_expected_arrival, tomorrow_expected_departure, no_show_rooms_partial, no_show_adult_partial, 
            no_show_child_partial, no_show_infant_partial;      
        RETURN NEXT;
    END IF;
END

sp_get_recapitulate

Parameters

Definition