aonestar.public

Parameters

Name	Type	Mode
start_date	date	IN (DEFAULT NULL)
end_date	date	IN (DEFAULT NULL)
adate	date	OUT
roomtype_id	integer	OUT
ttl	integer	OUT
rsv	integer	OUT
cfm	integer	OUT
ttt	integer	OUT
inh	integer	OUT
hsu	integer	OUT
ooo	integer	OUT
oos	integer	OUT
ooi	integer	OUT
arr	integer	OUT
dep	integer	OUT
occ	integer	OUT
rev	t_money	OUT
avl	integer	OUT

Definition

DECLARE 
    result_json jsonb;
    V_SYS_DATE date;
BEGIN
    V_SYS_DATE := fn_system_date();
    START_DATE := COALESCE($1, fn_system_date());
    END_DATE   := COALESCE($2, START_DATE + 13);

    RETURN QUERY
    WITH dates AS (
       SELECT fn_date_range(START_DATE, END_DATE) adate
    ), RSV AS (
        SELECT d.adate, a.* 
        FROM dates AS d, 
            LATERAL (
                WITH BK as (
                    SELECT bi.roomtype_id, b.status, bi.arrival, bi.departure, bi.qty, bi.inhouse_qty, bi.block_qty, br.room_rate,
                        (d.adate < bi.departure) and (b.status = 'R' or (b.status = 'I' and b.confirm_no is null)) as IS_RSV,
                        (d.adate < bi.departure) and (b.status = 'C' or (b.status = 'I' and b.confirm_no is not null)) as IS_CFM,
                        (d.adate < bi.departure and b.status = 'W') as IS_TTT,
                        (b.status <> 'W' AND d.adate = bi.arrival) as IS_ARR,
                        (b.status <> 'W' AND d.adate = bi.departure) as IS_DEP,  
                        (bi.qty - bi.inhouse_qty - bi.block_qty) as book_qty    
                    FROM booking_items bi 
                        LEFT JOIN booking b ON b.id = bi.booking_id 
                        LEFT JOIN booking_rates br ON br.booking_item = bi.item_id AND br.charge_date = d.adate  
                    WHERE b.status IN ('R','C','W','I') and d.adate between bi.arrival AND bi.departure
                )
                SELECT bk.roomtype_id, 
                       sum(iif(IS_RSV, bk.book_qty, 0)) AS RSV, 
                       sum(iif(IS_CFM, bk.book_qty, 0)) AS CFM, 
                       sum(iif(IS_TTT, bk.book_qty, 0)) AS TTT,
                       sum(iif(IS_ARR, bk.book_qty, 0)) AS ARR,
                       sum(iif(IS_DEP, bk.book_qty, 0)) AS DEP,
                       sum(IIF(bk.status <> 'W', bk.room_rate * bk.book_qty, 0)) REV
                FROM BK
                GROUP BY 1
            ) AS a
    ), BLK AS (
        SELECT d.adate, b.* 
        FROM dates AS d, 
            LATERAL (
                SELECT rm.roomtype_id, 
                    count(DISTINCT IIF(b.status = 'R' AND d.adate < rb.departure, rb.room_id, NULL)) AS RSV,  
                    count(DISTINCT IIF(b.status in ('I','C') AND d.adate < rb.departure, rb.room_id, NULL)) AS CFM,
                    count(DISTINCT IIF(rb.arrival   = d.adate, rb.room_id, NULL)) AS ARR,
                    count(DISTINCT IIF(rb.departure = d.adate, rb.room_id, NULL)) AS DEP,
                    sum(room_rate) AS REV
                FROM room_block rb 
                    LEFT JOIN booking b ON b.id = rb.booking_id 
                    LEFT JOIN room rm ON rm.id = rb.room_id 
                    LEFT JOIN booking_rates br ON br.booking_item = rb.booking_item AND br.charge_date = d.adate  
                WHERE b.status IN ('R','C','I') AND rb.status = 'B' and d.adate between rb.arrival AND rb.departure
                GROUP BY 1
            ) AS b
    ), INH AS (
        SELECT d.adate, a.* 
        FROM dates AS d, 
            LATERAL (
                SELECT rm.roomtype_id, 
                       count(DISTINCT iif(NOT rg.house_used AND d.adate <> rg.departure, rg.room_id, NULL)) as INH, 
                       count(DISTINCT iif(rg.house_used     AND d.adate <> rg.departure, rg.room_id, NULL)) AS HSU,
                       count(DISTINCT iif(d.adate = arrival,   room_id, NULL)) AS ARR,
                       count(DISTINCT iif(d.adate = departure, room_id, NULL)) AS DEP,
                       sum(room_rate) AS REV
                FROM registration rg 
                    LEFT JOIN room rm ON rm.id = rg.room_id
                    LEFT JOIN registration_rates rr ON rr.register_id = rg.id AND rr.charge_date = d.adate 
                WHERE (rg.status = 'I' or (rg.status = 'O' and rg.departure = d.adate)) 
                      AND rg.room_id IS NOT NULL 
                      AND d.adate between rg.arrival AND rg.departure
                GROUP BY 1
            ) AS a
    ), OO AS (
        SELECT d.adate, a.* 
        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
    )--, dataset AS (
    SELECT x.adate, x.roomtype_id, 
        max(x.TTL)::int - sum(x.OOI)::int as TTL, 
        sum(x.RSV)::int as RSV, 
        sum(x.CFM)::int as CFM, 
        sum(x.TTT)::int as TTT, 
        sum(x.INH)::int as INH, sum(x.HSU)::int as HSU, sum(x.OOO)::int as OOO, 
        sum(x.OOS)::int as OOS, sum(x.OOI)::int as OOI, sum(x.ARR)::int as ARR, 
        sum(x.DEP)::int as DEP, sum(x.INH + x.HSU + x.RSV + x.CFM)::int as OCC,
        sum(x.REV)::t_money as REV,
        max(x.TTL)::int-sum(x.OOI+x.OOO+x.RSV+x.CFM+x.INH+x.HSU)::int as AVL
    FROM (
        SELECT a.adate, a.roomtype_id, 0 TTL, a.RSV, a.CFM, a.TTT, a.ARR, a.DEP, 0 INH, 0 HSU, 0 OOO, 0 OOS, 0 OOI, a.REV FROM RSV A
        UNION ALL
        SELECT a.adate, a.roomtype_id, 0 TTL, a.RSV, a.CFM, 0 TTT, a.ARR, a.DEP, 0 INH, 0 HSU, 0 OOO, 0 OOS, 0 OOI, a.REV FROM BLK A
        UNION ALL
        SELECT a.adate, a.roomtype_id, 0 TTL, 0 RSV, 0 CFM, 0 TTT, a.ARR, a.DEP, a.INH, a.HSU, 0 OOO, 0 OOS, 0 OOI, a.REV FROM INH A
        UNION ALL
        SELECT a.adate, a.roomtype_id, 0 TTL, 0 RSV, 0 CFM, 0 TTT, 0 ARR, 0 DEP, 0 INH, 0 HSU, a.OOO, a.OOS, a.OOI, 0 REV FROM OO A
        UNION ALL
        SELECT d.adate, r.id, COALESCE(total_rooms,0), 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 FROM room_type r, dates as d
    ) X
    GROUP BY 1,2
    ORDER BY 1,2;
END

sp_room_avail_by_roomtype

Parameters

Definition