aonestar.public

Parameters

Name	Type	Mode
start_date	date	IN
end_date	date	IN
date	date	OUT
arr_ind	integer	OUT
dep_ind	integer	OUT
inh_ind	integer	OUT
arr_grp	integer	OUT
dep_grp	integer	OUT
inh_grp	integer	OUT
arr_total	integer	OUT
dep_total	integer	OUT
inh_total	integer	OUT
adult	integer	OUT
child	integer	OUT
infant	integer	OUT
pax	text	OUT
occ_actual	t_percent	OUT
occ_overall	t_percent	OUT
revenue	t_money	OUT
adr	t_money	OUT
rev_par	t_money	OUT
total_rooms	integer	OUT
ooi_rooms	integer	OUT
ooo_rooms	integer	OUT

Definition

DECLARE total_rooms int;
BEGIN 
    total_rooms := (SELECT count(*) FROM room);

    RETURN query
    WITH dates AS (
       SELECT fn_date_range($1, $2) adate
    ), bk AS (
        SELECT d.adate, COALESCE(A.IND_ARR, 0) AS IND_ARR, COALESCE(A.IND_DEP, 0) AS IND_DEP, 
             COALESCE(A.GRP_ARR, 0) AS GRP_ARR, COALESCE(A.GRP_DEP, 0) AS GRP_DEP,
             COALESCE(A.IND_INH, 0) AS IND_INH, COALESCE(A.GRP_INH, 0) AS GRP_INH, 
             COALESCE(A.IND_INH, 0) + COALESCE(A.GRP_INH, 0) AS TOTAL_INH, 
             COALESCE(A.adult, 0) AS adult, COALESCE(A.child, 0) AS child, COALESCE(A.infant, 0) AS infant
        FROM dates AS d, 
            LATERAL (
                SELECT sum(iif(b.book_type = 'I' AND d.adate = bi.arrival,   bi.qty -bi.inhouse_qty, 0)) AS IND_ARR,
                       sum(iif(b.book_type = 'I' AND d.adate = bi.departure, bi.qty -bi.inhouse_qty, 0)) AS IND_DEP,
                       sum(iif(b.book_type = 'G' AND d.adate = bi.arrival,   bi.qty -bi.inhouse_qty, 0)) AS GRP_ARR,
                       sum(iif(b.book_type = 'G' AND d.adate = bi.departure, bi.qty -bi.inhouse_qty, 0)) AS GRP_DEP,
                       sum(iif(b.book_type = 'I' AND d.adate <> bi.departure,bi.qty -bi.inhouse_qty, 0)) AS IND_INH,
                       sum(iif(b.book_type = 'G' AND d.adate <> bi.departure,bi.qty -bi.inhouse_qty, 0)) AS GRP_INH,
                       sum(iif(d.adate <> bi.departure, (bi.qty -bi.inhouse_qty) * bi.adult, 0)) AS adult,
                       sum(iif(d.adate <> bi.departure, (bi.qty -bi.inhouse_qty) * bi.child, 0)) AS child,
                       sum(iif(d.adate <> bi.departure, (bi.qty -bi.inhouse_qty) * bi.infant, 0)) AS infant
                FROM booking_items bi LEFT JOIN booking b ON b.id = bi.booking_id 
                WHERE b.status IN ('R','C','I') AND (bi.qty > bi.inhouse_qty) and (d.adate between bi.arrival AND bi.departure)
            ) AS A
    ), rg AS (
        SELECT d.adate, COALESCE(A.IND_ARR, 0) AS IND_ARR, COALESCE(A.IND_DEP, 0) AS IND_DEP, 
             COALESCE(A.GRP_ARR, 0) AS GRP_ARR, COALESCE(A.GRP_DEP, 0) AS GRP_DEP,
             COALESCE(A.IND_INH, 0) AS IND_INH, COALESCE(A.GRP_INH, 0) AS GRP_INH, 
             COALESCE(A.IND_INH, 0) + COALESCE(A.GRP_INH, 0) AS TOTAL_INH, 
             COALESCE(A.adult, 0) AS adult, COALESCE(A.child, 0) AS child, COALESCE(A.infant, 0) AS infant
        FROM dates AS d, 
            LATERAL (
                SELECT count(DISTINCT iif(d.adate = arrival   AND rg.group_id IS NULL, room_id, NULL)) AS IND_ARR,
                       count(DISTINCT iif(d.adate = departure AND rg.group_id IS NULL, room_id, NULL)) AS IND_DEP,
                       count(DISTINCT iif(d.adate = arrival   AND rg.group_id IS NOT NULL, room_id, NULL)) AS GRP_ARR,
                       count(DISTINCT iif(d.adate = departure AND rg.group_id IS NOT NULL, room_id, NULL)) AS GRP_DEP,
                       count(DISTINCT iif(d.adate <> rg.departure AND rg.group_id IS NULL    , rg.room_id, NULL)) as IND_INH, 
                       count(DISTINCT iif(d.adate <> rg.departure AND rg.group_id IS NOT NULL, rg.room_id, NULL)) as GRP_INH,
                       sum(iif(d.adate <> rg.departure, rg.adult,  0)) as adult,
                       sum(iif(d.adate <> rg.departure, rg.child,  0)) as child,
                       sum(iif(d.adate <> rg.departure, rg.infant, 0)) as infant  
                FROM registration rg 
                WHERE rg.status = 'I' AND rg.room_id IS NOT NULL and d.adate between rg.arrival AND rg.departure
            ) AS A
    ), oo AS (
        SELECT d.adate, COALESCE(A.OOO,0) AS OOO, COALESCE(A.OOS,0) AS OOS , COALESCE(A.OOI,0) AS OOI  
        FROM dates AS d, 
            LATERAL (   
                SELECT --rm.roomtype_id, 
                       count(DISTINCT iif(ooo_type = 'OOO', o.room_id, NULL)) as OOO,
                       count(DISTINCT iif(ooo_type = 'OOS', o.room_id, NULL)) as OOS,
                       count(DISTINCT iif(ooo_type = 'OOI', o.room_id, NULL)) as OOI
                FROM out_of_order o LEFT JOIN room rm ON rm.id = o.room_id
                WHERE d.adate between o.start_date AND o.end_date -1
                --GROUP BY 1
            ) AS A
    ), bk_rev AS (
        SELECT br.charge_date AS adate, sum(room_rate * (bi.qty-bi.inhouse_qty)) AS REV
        FROM booking_rates br 
            LEFT JOIN booking_items bi ON bi.item_id = br.booking_item 
            LEFT JOIN booking b ON b.id = br.booking_id 
        WHERE b.status IN ('R','C','I') AND (bi.qty > bi.inhouse_qty) AND (br.charge_date BETWEEN $1 AND $2)      
        GROUP BY 1
    ), rg_rev AS (
        SELECT rr.charge_date AS adate, sum(room_rate) AS REV
        FROM registration_rates rr 
            LEFT JOIN registration rg ON rg.id = rr.register_id 
        WHERE rg.status = 'I' AND (rr.charge_date BETWEEN $1 AND $2)             
        GROUP BY 1
    )--, dataset AS (
        SELECT A.adate, --max(TTL)-sum(OOI) TTL, 
               sum(A.IND_ARR)::int, sum(A.IND_DEP)::int, sum(A.IND_INH)::int, 
               sum(A.GRP_ARR)::int, sum(A.GRP_DEP)::int, sum(A.GRP_INH)::int, 
               sum(A.IND_ARR)::int + sum(A.GRP_ARR)::int, 
               sum(A.IND_DEP)::int + sum(A.GRP_DEP)::int, 
               sum(A.TOTAL_INH)::int, 
               sum(A.adult)::int AS adult, sum(A.child)::int AS child, sum(A.infant)::int AS infant,
               fn_pax(sum(A.adult)::int, sum(A.child)::int, sum(A.infant)::int) AS pax, 
               (sum(A.TOTAL_INH) * 100.00 / NULLIF(total_rooms-sum(A.OOI)-sum(A.OOO), 0))::t_percent AS occ_actual,
               (sum(A.TOTAL_INH) * 100.00 / NULLIF(total_rooms-sum(A.OOI), 0))::t_percent AS occ_overall,
               sum(A.REV)::t_money AS revenue,
               (sum(A.REV) / NULLIF(sum(TOTAL_INH),0))::t_money AS adr,
               (sum(A.REV) / NULLIF(total_rooms -sum(A.OOI),0))::t_money AS rev_par,
               total_rooms, sum(OOI)::int, sum(OOO)::int
        FROM (
            SELECT adate, bk.IND_ARR, bk.IND_DEP, bk.IND_INH, bk.GRP_ARR, bk.GRP_DEP, bk.GRP_INH, bk.TOTAL_INH, bk.ADULT, bk.CHILD, bk.INFANT, 0 OOO, 0 OOS, 0 OOI, 0.0 REV
            FROM bk 
            UNION ALL
            SELECT adate, rg.IND_ARR, rg.IND_DEP, rg.IND_INH, rg.GRP_ARR, rg.GRP_DEP, rg.GRP_INH, rg.TOTAL_INH, rg.ADULT, rg.CHILD, rg.INFANT, 0, 0, 0, 0.0 FROM rg
            UNION ALL
            SELECT adate, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, OOO, OOS, OOI, 0.0 FROM oo
            UNION ALL
            SELECT adate, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, REV FROM bk_rev
            UNION ALL
            SELECT adate, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, REV FROM rg_rev
        ) A
        GROUP BY 1
        ORDER BY 1;
END

sp_occupancy_forecast

Parameters

Definition