aonestar.public

Parameters

Name	Type	Mode
booking_id	integer	IN

Definition

with BK as (
        select b.id as booking_id, b.status, b.book_type, b.booking_name, 
                b.stay_type, b.confirm_no, b.folio_id, b.rsvtype_id, rt.name as reservation_type, b.book_by, 
                b.guest_id, b.group_id, grp.name as group_name, b.member_code, b.channel_id, cn.name as channel_name, 
                b.voucher_no, b.origin_id, org.name as origin_name, b.arrival, b.arrival_time, b.arrive_by, b.arrive_from, 
                b.departure, b.departure_time, b.depart_by, b.depart_to,                            
                b.sales_id, ss.name as sales_name, 
                b.market_id, mk.name as market_name, 
                b.create_user, b.create_time, 
                b.modify_user, b.modify_time, 
                b.confirm_user, b.confirm_time, b.confirm_remark,
                b.cancel_user, b.cancel_time, 
                --rg.checkin_user, rg.checkin_time, 
                iif(b.status IN ('I','O'), coalesce(b.modify_user, b.confirm_user, b.create_user), NULL) AS checkin_user,
                ((b.arrival + b.arrival_time) AT TIME ZONE 'Asia/Bangkok')::timestamptz AS checkin_time,
                --rg.checkout_user, rg.checkout_time,
                iif(b.status = 'O', coalesce(b.modify_user, b.confirm_user, b.create_user), NULL) AS checkout_user,
                (b.departure + b.departure_time)::timestamptz AS checkout_time,
                b.msg_count, b.flag_count, b.note,
                json_build_object('payment_method', b.payment_method, 
                    'folio_pattern_id', b.folio_pattern_id,
                    'folio_pattern_name', fp.name,
                    'credit_limit', b.credit_limit,
                    'promo_code', b.promo_code
                ) AS payment
        from booking b left join channel cn on cn.id = b.channel_id
                left join reservation_type rt on rt.id = b.rsvtype_id
                left join group_info grp on grp.id = b.group_id
                left join origin org on org.id = b.origin_id
                left join folio_pattern fp on fp.id = b.folio_pattern_id
                left join sales_staff ss on ss.id = b.sales_id
                left join market mk on mk.id = b.market_id
                --LEFT JOIN registration rg ON rg.booking_id = b.id
        where b.id = $1 
             
    ), all_requests AS (
        SELECT br.booking_item, br.id, br.inclusion_id, inc."name", br.multiplier AS "charge_type", br.frequency,  
            br.charge_dept, br.charge_rate, br.qty, br.start_date, br.end_date, br.request_time, br.remark, br.inclusive
        FROM guest_request br LEFT JOIN inclusion inc ON inc.id = br.inclusion_id 
        WHERE br.booking_id = $1 --AND booking_item IS NULL 
             
    ), requirements AS (
        SELECT id, inclusion_id, "name", charge_type, frequency, charge_dept, charge_rate, qty, start_date, end_date, request_time, remark, inclusive
        FROM all_requests
        WHERE booking_item IS NULL AND NOT inclusive 
        ORDER BY 1
                                
    ), dtl_requests AS (
        SELECT br.booking_item, string_agg("name", ', ') request_list, 
           json_agg((id, inclusion_id, "name", charge_type, frequency, charge_dept, 
                     charge_rate, qty, start_date, end_date, request_time, remark, inclusive
                     )::t_requirement
           ) AS requirements
--            json_agg((SELECT X FROM
--                       (SELECT id, inclusion_id, "name", charge_type, frequency, charge_dept, charge_rate, qty, start_date, end_date, request_time, remark, inclusive
--                       ) AS X
--            )) AS requirements
        FROM all_requests br --LEFT JOIN inclusion inc ON inc.id = br.inclusion_id 
        WHERE booking_item IS NOT NULL AND NOT inclusive
        GROUP BY br.booking_item   
        --WHERE recalc_inclusive AND (inc.multiplier <> 1 OR bi.seq = 1)
        
    ), details as ( 
        select bi.item_id, bi.roomtype_id, rt.code AS roomtype_code, rt."name" as roomtype_name, 
            bi.bedtype_id, bt."name" as bedtype_name, 
            bi.rateplan_id, rp."name" as rateplan_name, 
            bi.mealtype_id, mt."name" as mealtype_name, 
            bi.rate_id, arrival, departure, (departure-arrival) AS nights, 
            bi.qty, bi.qty + bi.noshow_qty as book_qty, bi.inhouse_qty, bi.noshow_qty, 
            bi.adult, bi.child, bi.infant, bi.extra_adult, 
            extra_child, extra_infant, allot_qty, block_qty, inhouse_qty, charge_to, bi.no_amend, bi.no_cancel, bi.no_refund,  
            coalesce(string_to_array(bi.room_numbers, ', '), '{}') as room_numbers,
            req.request_list, COALESCE(req.requirements, '[]') AS requirements,
            (SELECT COALESCE(json_agg(rates), '[]') --COALESCE(array_agg(json_build_object('date', adate, 'amount', room_rate)), '{}')
             FROM (SELECT br.charge_date AS "date", br.room_rate AS amount, 
                         coalesce(br.extrabed_rate, rd.rate_extra_adult) AS extra_adult_rate, 
                         coalesce(br.child_rate, rd.rate_extra_child) AS extra_child_rate, 
                         coalesce(br.infant_rate, rd.rate_extra_infant) AS extra_infant_rate, 
                         br.comp, br.comp_abf, 
                         br.rateplan_id, rp."name" as rateplan_name, 
                         br.mealtype_id, mt."name" as mealtype_name 
                   FROM booking_rates br 
                        left join rate_plan rp on rp.id = br.rateplan_id 
                        left join meal_type mt on mt.id = br.mealtype_id 
                   WHERE br.booking_item = bi.item_id 
                   ORDER BY 1
                  ) AS rates
            ) AS rate_schedule
        from booking_items bi 
            left join rate_plan rp on rp.id = bi.rateplan_id 
            left join meal_type mt on mt.id = bi.mealtype_id 
            left join room_type rt on rt.id = bi.roomtype_id 
            left join bed_type bt on bt.id = bi.bedtype_id 
            LEFT JOIN dtl_requests req ON req.booking_item = bi.item_id 
            LEFT JOIN v_rate_details rd ON rd.rateplan_id = bi.rateplan_id AND rd.roomtype_id = bi.roomtype_id 
        where bi.booking_id = $1
        order by bi.item_id

    ), guests as (
        SELECT guest_id FROM bk 
        union
        SELECT guest_id FROM booking_guests WHERE booking_id = $1
        
    ), deposits AS (
        SELECT d.id, d.status, d.deposit_date, d.payment_id, dp.name AS payment_name, d.amount,
            d.remark, d.create_user, d.create_time, d.modify_user, d.modify_time, d.cancel_user, d.cancel_time 
        FROM deposit d LEFT JOIN department dp ON dp.id = d.payment_id
        WHERE d.booking_id = $1
        ORDER BY d.deposit_date, d.id
        
    ), bk_log AS (      
        SELECT id, log_user AS user_name, log_type, COALESCE(log_text,log_type) AS log_text, log_date, log_time
        FROM booking_log WHERE booking_id = $1
        ORDER BY 1
        
    ), objects AS ( 
        SELECT 
            (SELECT json_agg(details) from details) as details,
            (SELECT sp_get_guest_list_js(array_agg(guest_id)) FROM guests) as guests, 
            (SELECT COALESCE(json_agg(all_requests), '[]') from all_requests 
             WHERE booking_item IS NULL OR inclusive) as bk_requests,
            (SELECT COALESCE(json_agg(requirements), '[]') from requirements) as requirements,
            (SELECT COALESCE(json_agg(deposits), '[]') from deposits) as deposits,
            --(SELECT sp_get_booking_summary_js($1)) AS summary,
            (SELECT COALESCE(json_agg(bk_log), '[]') from bk_log) AS logs
    ), summ AS (
        Select sp_get_booking_summary_js(row_to_json(booking_data)::jsonb, recalc_inclusive => FALSE) AS summary 
        from  (SELECT BK.*, details, guests, bk_requests AS requirements, deposits, logs FROM bk, objects) AS booking_data
    )   
    Select row_to_json(dataset)
    from  (SELECT BK.*, details, guests, requirements, deposits, logs, summary FROM bk, objects, summ) AS dataset

sp_get_booking_js

Parameters

Definition