aonestar.public

Parameters

Name	Type	Mode
booking_id	integer	IN

Definition

DECLARE 
    js_result json;
    sellable text[];
    sys_date date;
BEGIN 
    sellable := ARRAY(SELECT json_array_elements_text(fn_sys_param('ROOM_STATUS','SELLABLE', '[]'::json)));
    sys_date := fn_system_date();
    WITH BI AS (
        SELECT item_id, qty, block_qty, inhouse_qty, 
             bi.roomtype_id, rt.code AS roomtype_code, rt."name" AS roomtype_name, 
             bi.bedtype_id, bt.code AS bedtype_code, bt.name AS bedtype_name, bi.arrival, bi.departure,
             format('%s%s @%s (%s)', rt.name, coalesce('/'||bt.code, ''), rp.name, fn_period_to_str(bi.arrival, bi.departure)) AS description
        FROM booking_items bi 
            LEFT JOIN room_type rt ON rt.id = bi.roomtype_id 
            LEFT JOIN bed_type bt ON bt.id = bi.bedtype_id 
            LEFT JOIN rate_plan rp ON rp.id = bi.rateplan_id 
        WHERE bi.booking_id = $1 and bi.arrival = sys_date
    ), inh AS (
        SELECT '1-'||rg.id::text AS "key", rg.booking_item, 'I' AS item_status, 
            rg.room_id, rm.room_number, rs.status room_status, rs.hk_status, 
            rm.roomtype_id AS roomtype_id, rt.code AS roomtype_code, rt."name" AS roomtype_name,
            rm.bedtype_id, bt.code AS bedtype_code, bt.name AS bedtype_name, 
            rg.id AS register_id, NULL::int AS block_id, 
            FALSE AS can_checkin, rg.status = 'I' AS can_join, 
            g.full_name
                ||coalesce(' | '||(select string_agg(g.full_name, ' | ')
                            from register_guests mg inner join guest g on g.id = mg.guest_id
                            where mg.register_id = rg.id
                  ), '') 
            AS guest_name, 
            FALSE AS room_is_ready
        FROM registration rg 
            LEFT JOIN room rm ON rm.id = rg.room_id 
            LEFT JOIN guest g ON g.id = rg.guest_id 
            LEFT JOIN room_status rs ON rs.room_id = rm.id
            LEFT JOIN room_type rt ON rt.id = rm.roomtype_id 
            LEFT JOIN bed_type bt ON bt.id = rm.bedtype_id 
        WHERE 
                rg.status <> 'X' 
             
                AND 
                rg.booking_id = $1
    ), block AS (
        SELECT '2-'||rb.id::text AS "key", rb.booking_item, 'B' AS item_status, 
            rb.room_id, rm.room_number, rs.status room_status, rs.hk_status, 
            rm.roomtype_id AS roomtype_id, rt.code AS roomtype_code, rt."name" AS roomtype_name,
            rm.bedtype_id, bt.code AS bedtype_code, bt.name AS bedtype_name, 
            NULL::int, rb.id AS block_id, 
            TRUE AS can_checkin, FALSE AS can_join, NULL, 
            (rs.status = 'VAC' AND rs.hk_status = ANY(sellable)) AS room_is_ready 
        FROM room_block rb 
            LEFT JOIN room rm ON rm.id = rb.room_id 
            LEFT JOIN room_status rs ON rs.room_id = rm.id
            LEFT JOIN room_type rt ON rt.id = rm.roomtype_id 
            LEFT JOIN bed_type bt ON bt.id = rm.bedtype_id
        WHERE rb.booking_id = $1 AND NOT rb.checked_in and rb.arrival = sys_date 
    ), unblock AS (
        SELECT '3-'||bi.item_id::TEXT||'-'||lpad(seq::TEXT,3,'0') AS "key", bi.item_id, 'B', NULL::int, NULL, NULL, NULL, 
            NULL::int, NULL, NULL, --bi.roomtype_id, roomtype_code, roomtype_name,
            NULL::int, NULL, NULL, --bi.bedtype_id, bedtype_code, bedtype_name,
            NULL::int, NULL::int, TRUE AS can_checkin, FALSE AS can_join, NULL, FALSE AS room_is_ready  
        FROM BI, LATERAL generate_series(1, bi.qty-bi.block_qty-bi.inhouse_qty) AS seq         
    ), items AS (
        SELECT * FROM inh
        UNION ALL 
        SELECT * FROM block
        UNION ALL 
        SELECT * FROM unblock
    ), bk_items AS (
        SELECT bi.*,
            (SELECT json_agg(B)
             FROM (SELECT "key", item_status, item_status AS block_status, room_id, room_number, room_status, 
                        hk_status, roomtype_id, roomtype_code, roomtype_name, bedtype_id, bedtype_code,  
                        bedtype_name, register_id, block_id, guest_name, can_checkin, can_join, room_is_ready                        
                   FROM items
                   WHERE items.booking_item = bi.item_id) AS B 
            ) AS rooms    
        FROM BI
    ), inh_guests AS (
        SELECT guest_id FROM registration rg WHERE rg.booking_id = $1 and rg.status <> 'X' 
        union
        SELECT mg.guest_id FROM register_guests mg inner join registration rg on rg.id = mg.register_id 
        WHERE rg.booking_id = $1 and rg.status <> 'X' 
    ), bk_guests AS ( 
        SELECT guest_id FROM booking WHERE id = $1 
        union all
        SELECT guest_id FROM booking_guests bg 
        WHERE bg.booking_id = $1 and NOT bg.registered --AND bg.guest_id NOT IN (SELECT guest_id FROM inh_guests)
    ), guests AS (
        SELECT guest_id, g.full_name AS name, g.sex, g.birthdate, fn_age_str(g.birthdate) AS "age", n.name as nationality, v.name vip_level 
        from bk_guests bg LEFT join guest g ON g.id = bg.guest_id
             left join nationality n on n.id = g.nation_id 
             left join "language" l on l.id = g.lang_id 
             left join vip v on v.id = g.vip_id 
        WHERE bg.guest_id NOT IN (SELECT guest_id FROM inh_guests)
    ), dataset AS (
        SELECT sum(bi.qty) AS booking_qty, 
            sum(bi.inhouse_qty) AS inhouse_qty, 
            sum(bi.qty) - sum(bi.inhouse_qty) AS checkin_available,
            (SELECT COALESCE(jsonb_agg(bk_items),'[]') FROM bk_items) AS booking_items,
            (SELECT COALESCE(jsonb_agg(guests),'[]') FROM guests) AS guests
        FROM BI
    )
    SELECT row_to_json(dataset) FROM dataset
    INTO js_result;

    RETURN js_result;
END

sp_get_booking_rooms_and_guests_js

Parameters

Definition