aonestar.public

Parameters

Name	Type	Mode
booking_id	integer	IN

Definition

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
    ), items AS (
        SELECT  bi.*,
             (SELECT COALESCE(jsonb_agg(block),'[]')
              FROM (
                SELECT rb.id AS block_id, 
                    rb.status block_status, 
                    rb.status 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, 
                    rb.register_id,
                    g.full_name AS guest_name,
                    (rb.status = 'B' AND rs.status = 'VAC') AS can_checkin,
                    rb.status = 'I' AS can_join
                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 
                    LEFT JOIN registration rg ON rg.id = rb.register_id
                    LEFT JOIN guest g ON g.id = rg.guest_id 
                WHERE rb.booking_item = bi.item_id
                UNION ALL 
                SELECT NULL,'R','R',NULL,NULL,NULL,NULL,
                       bi.roomtype_id, bi.roomtype_code, bi.roomtype_name, bi.bedtype_id, bi.bedtype_code, bi.bedtype_name, NULL, NULL, TRUE, FALSE  
                FROM generate_series(1, bi.qty-bi.block_qty-bi.inhouse_qty)         
              ) block            
             ) as rooms
        FROM BI
    ), inh_guests AS (
        SELECT guest_id FROM registration rg WHERE rg.booking_id = $1
    ), bk_guests AS ( 
        SELECT guest_id FROM booking WHERE id = $1 
        union all
        SELECT guest_id FROM booking_guests WHERE booking_id = $1
    ), guests AS (
        SELECT guest_id, g.full_name AS name, g.sex, g.birthdate, age(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(items),'[]') FROM items) AS booking_items,
            (SELECT COALESCE(jsonb_agg(guests),'[]') FROM guests) AS guests
        FROM BI
    )
    SELECT row_to_json(dataset) FROM dataset

sp_get_booking_rooms_and_guests_js2

Parameters

Definition