aonestar.public

Parameters

Name	Type	Mode
row_type	text	IN
key_name	text	IN
adate	date	IN (DEFAULT NULL)
roomtype_id	integer	IN (DEFAULT NULL)

Definition

DECLARE 
    json_result jsonb;
    _status bpchar[];
    _house_used bool;
BEGIN
    key_name = upper(replace(key_name, 'room_', ''));

    CASE 
        WHEN (key_name in ('RSV','CFM','TTT')) THEN
            WITH BI as (
                SELECT bi.booking_id, bi.item_id, b.guest_id, b.channel_id, bi.roomtype_id, b.status, bi.arrival, bi.departure, 
                    bi.qty, bi.inhouse_qty, bi.block_qty, (bi.qty - bi.inhouse_qty) as book_qty--,                    
                FROM booking_items bi 
                    LEFT JOIN booking b ON b.id = bi.booking_id 
                WHERE b.status IN ('R','C','W','I') 
                    AND $3 BETWEEN bi.arrival AND bi.departure-1 
                    AND ((row_type = 'actual' 
                            AND CASE key_name  
                                    WHEN 'RSV' THEN (adate < bi.departure) and (b.status = 'R' or (b.status = 'I' and b.confirm_no is null))
                                    WHEN 'CFM' THEN (adate < bi.departure) and (b.status = 'C' or (b.status = 'I' and b.confirm_no is not null))
                                    WHEN 'TTT' THEN (adate < bi.departure and b.status = 'W')
                                END
                            )
                        OR (row_type = 'room' 
                            AND b.status IN ('R','C','I') 
                            AND ($4 is null or bi.roomtype_id = $4)))
                     AND (bi.qty - bi.inhouse_qty) > 0
    
            ), dataset as (
                SELECT ROW_NUMBER () OVER () AS "key", 
                    bi.booking_id, rt.code AS room_type, 
                    g.full_name AS "name", cn."name" AS channel, bi.arrival, bi.departure,
                    book_qty AS qty,
                    fn_get_block_rooms(bi.booking_id, bi.item_id) room_number
                FROM BI
                    LEFT JOIN room_type rt ON rt.id = bi.roomtype_id 
                    LEFT JOIN guest g ON g.id = bi.guest_id 
                    LEFT JOIN channel cn ON cn.id = bi.channel_id 
            )
            SELECT jsonb_agg(dataset) FROM dataset
            INTO json_result;
        
        WHEN (key_name in ('INH','HSU')) THEN 
            SELECT jsonb_agg(dataset) 
            FROM (
                SELECT ROW_NUMBER () OVER () AS "key", rg.id AS register_id, rm.room_number, rt.code AS room_type, g.full_name AS "name", cn."name" AS channel,  rg.arrival, rg.departure
                FROM registration rg 
                    LEFT JOIN room rm ON rm.id = rg.room_id
                    LEFT JOIN room_type rt ON rt.id = rm.roomtype_id 
                    LEFT JOIN guest g ON g.id = rg.guest_id 
                    LEFT JOIN channel cn ON cn.id = rg.channel_id 
                WHERE rg.status = 'I' 
                    AND ((row_type = 'actual' AND rg.house_used = (key_name='HSU')) OR (row_type = 'room' AND rm.roomtype_id = $4)) 
                    AND rg.room_id IS NOT NULL and $3 between rg.arrival AND rg.departure-1
            ) AS dataset
            INTO json_result;
        
        WHEN (key_name in ('OOO','OOI','OOS')) THEN 
            SELECT jsonb_agg(dataset) 
            FROM (
                SELECT ROW_NUMBER () OVER () AS "key", rm.room_number, rt.code AS room_type, rs."name" AS reason, oo.start_date, oo.end_date, oo.remark 
                FROM out_of_order oo 
                    LEFT JOIN room rm ON rm.id = oo.room_id
                    LEFT JOIN room_type rt ON rt.id = rm.roomtype_id 
                    LEFT JOIN ooo_reason rs ON rs.id = oo.reason_id 
                WHERE oo.ooo_type = key_name and $3 between oo.start_date AND oo.end_date -1
                    AND ($4 IS NULL OR rm.roomtype_id = $4)
            ) AS dataset
            INTO json_result;
        
        WHEN (row_type = 'forecast' AND key_name in ('ARR','DEP','OCC')) THEN 
            WITH rg as (
                SELECT rg.id,
                       iif(rg.status='O','C/O','INH') AS status, 
                       rm.room_number, 
                       rt.code AS room_type, 
                       g.full_name AS "name",
                       cn."name" AS channel,
                       rg.arrival,
                       rg.departure,
                       1 AS qty
                FROM registration rg 
                    LEFT JOIN room rm ON rm.id = rg.room_id
                    LEFT JOIN room_type rt ON rt.id = rm.roomtype_id 
                    LEFT JOIN guest g ON g.id = rg.guest_id 
                    LEFT JOIN channel cn ON cn.id = rg.channel_id 
                WHERE (rg.room_id is not null) 
                      AND ((key_name = 'ARR' AND rg.status in ('I','O') AND rg.arrival = $3) 
                        OR (key_name = 'DEP' AND rg.status in ('I','O') AND rg.departure = $3)
                        OR (key_name = 'OCC' AND rg.status = 'I' AND $3 between rg.arrival AND rg.departure-1)) 
            ), bk AS (
                SELECT bi.booking_id, 'RSV' AS status, fn_get_block_rooms(bi.booking_id, bi.item_id) AS rooms, rt.code AS room_type, 
                    g.full_name AS "name", cn."name" AS channel, b.arrival, b.departure, (bi.qty - bi.inhouse_qty) AS qty
                FROM booking_items bi 
                    LEFT JOIN booking b ON b.id = bi.booking_id 
                    LEFT JOIN room_type rt ON rt.id = bi.roomtype_id 
                    LEFT JOIN guest g ON g.id = b.guest_id 
                    LEFT JOIN channel cn ON cn.id = b.channel_id 
                WHERE b.status IN ('R','C','I') AND (bi.qty - bi.inhouse_qty) > 0
                    and ((key_name = 'ARR' AND bi.arrival = $3) 
                        OR (key_name = 'DEP' AND bi.departure = $3)
                        OR (key_name = 'OCC' AND $3 between bi.arrival AND bi.departure-1)) 
            ), dataset AS (
                SELECT ROW_NUMBER () OVER () AS "key", a.*
                FROM (
                    SELECT * FROM rg 
                    UNION ALL
                    SELECT * FROM bk
                ) AS A
            )
            SELECT jsonb_agg(dataset) FROM dataset INTO json_result;
        
        WHEN (key_name = 'REV') THEN 
            WITH A as (
                SELECT rr.register_id AS id, iif(rg.status='O','C/O','INH') AS status, rm.room_number, rt.code AS room_type, g.full_name AS "name", 
                    cn."name" AS channel, rg.arrival, rg.departure, rr.room_rate AS revenue
                FROM registration_rates AS rr 
                    INNER JOIN registration rg ON rr.register_id = rg.id
                    LEFT JOIN room rm ON rm.id = rg.room_id
                    LEFT JOIN room_type rt ON rt.id = rm.roomtype_id 
                    LEFT JOIN guest g ON g.id = rg.guest_id 
                    LEFT JOIN channel cn ON cn.id = rg.channel_id 
                WHERE (rg.status = 'I') AND ($4 IS NULL OR rm.roomtype_id = $4) AND (rr.charge_date = $3) 
                UNION ALL 
                SELECT bi.booking_id AS id, 'RSV', fn_get_block_rooms(bi.booking_id, bi.item_id), rt.code, g.full_name, cn."name", 
                    bi.arrival, bi.departure, (br.room_rate * (bi.qty-bi.inhouse_qty)) AS revenue
                FROM booking_rates AS br 
                    INNER JOIN booking_items bi ON bi.item_id = br.booking_item 
                    LEFT JOIN booking b ON b.id = br.booking_id 
                    LEFT JOIN room_type rt ON rt.id = bi.roomtype_id 
                    LEFT JOIN guest g ON g.id = b.guest_id 
                    LEFT JOIN channel cn ON cn.id = b.channel_id 
                WHERE ($4 IS NULL OR bi.roomtype_id = $4) AND (b.status IN ('R','C')) and ($3 BETWEEN br.start_date AND br.end_date) 
            ), dataset as (
                SELECT ROW_NUMBER () OVER () AS "key", A.* FROM A
            )
            SELECT jsonb_agg(dataset) FROM dataset INTO json_result;
        
        ELSE json_result = '[]';
    END CASE;
    
    RETURN COALESCE(json_result, '[]');
END;

sp_get_room_avail_detail_js

Parameters

Definition