aonestar.public

Parameters

Name	Type	Mode
booking_id	integer	IN
include_logs	boolean	IN (DEFAULT true)
include_requests	boolean	IN (DEFAULT true)
include_summary	boolean	IN (DEFAULT true)
include_deposit	boolean	IN (DEFAULT true)
i_source	text	IN (DEFAULT 'active')

Definition

DECLARE
    json_result json;
    _use_active boolean;
    v_booking_id integer;
BEGIN
    v_booking_id := booking_id;
    IF i_source = 'any' THEN
        _use_active := EXISTS (SELECT 1 FROM booking WHERE id = v_booking_id);
    ELSE
        _use_active := (i_source <> 'archive');
    END IF;

WITH
    -- Abstract source: booking
    src_booking AS (
        SELECT id, status, book_type, booking_name, stay_type, confirm_no, folio_id, rsvtype_id,
               book_by, guest_id, group_id, member_code, channel_id, voucher_no, origin_id,
               arrival, arrival_time, arrive_by, arrive_from, departure, departure_time, depart_by, depart_to,
               sales_id, market_id, create_user, create_time, modify_user, modify_time,
               confirm_user, confirm_time, confirm_remark, cancel_user, cancel_time,
               msg_count, flag_count, note, payment_method, folio_pattern_id, credit_limit, promo_code,
               false AS is_archived
        FROM booking WHERE id = v_booking_id AND _use_active
        UNION ALL
        SELECT id, status, book_type, booking_name, stay_type, confirm_no, folio_id, rsvtype_id,
               book_by, guest_id, group_id, member_code, channel_id, voucher_no, origin_id,
               arrival, arrival_time, arrive_by, arrive_from, departure, departure_time, depart_by, depart_to,
               sales_id, market_id, create_user, create_time, modify_user, modify_time,
               confirm_user, confirm_time, confirm_remark, cancel_user, cancel_time,
               msg_count, flag_count, note, payment_method, folio_pattern_id, credit_limit, promo_code,
               true AS is_archived
        FROM backup.booking WHERE id = v_booking_id AND NOT _use_active
    ),
    -- Abstract source: booking_items
    src_booking_items AS (
        SELECT bi.item_id, bi.booking_id AS bk_id, bi.roomtype_id, bi.bedtype_id, bi.rateplan_id, bi.mealtype_id, bi.rate_id,
               bi.arrival, bi.departure, bi.qty, bi.noshow_qty, bi.inhouse_qty, bi.adult, bi.child, bi.infant, bi.extra_adult,
               bi.extra_child, bi.extra_infant, bi.allot_qty, bi.block_qty, bi.charge_to, bi.no_amend, bi.no_cancel, bi.no_refund, bi.room_numbers
        FROM booking_items bi WHERE bi.booking_id = v_booking_id AND _use_active
        UNION ALL
        SELECT bi.item_id, bi.booking_id AS bk_id, bi.roomtype_id, bi.bedtype_id, bi.rateplan_id, bi.mealtype_id, bi.rate_id,
               bi.arrival, bi.departure, bi.qty, bi.noshow_qty, bi.inhouse_qty, bi.adult, bi.child, bi.infant, bi.extra_adult,
               bi.extra_child, bi.extra_infant, bi.allot_qty, bi.block_qty, bi.charge_to, bi.no_amend, bi.no_cancel, bi.no_refund, bi.room_numbers
        FROM backup.booking_items bi WHERE bi.booking_id = v_booking_id AND NOT _use_active
    ),
    -- Abstract source: booking_rates
    src_booking_rates AS (
        SELECT br.booking_item, br.charge_date, br.room_rate, br.extrabed_rate, br.child_rate, br.infant_rate,
               br.comp, br.comp_abf, br.rateplan_id, br.mealtype_id
        FROM booking_rates br WHERE br.booking_id = v_booking_id AND _use_active
        UNION ALL
        SELECT br.booking_item, br.charge_date, br.room_rate, br.extrabed_rate, br.child_rate, br.infant_rate,
               br.comp, br.comp_abf, br.rateplan_id, br.mealtype_id
        FROM backup.booking_rates br WHERE br.booking_id = v_booking_id AND NOT _use_active
    ),
    -- Abstract source: booking_guests
    src_booking_guests AS (
        SELECT bg.guest_id FROM booking_guests bg WHERE bg.booking_id = v_booking_id AND _use_active
        UNION ALL
        SELECT bg.guest_id FROM backup.booking_guests bg WHERE bg.booking_id = v_booking_id AND NOT _use_active
    ),
    -- Abstract source: guest_request
    src_guest_request AS (
        SELECT gr.booking_item, gr.id, gr.inclusion_id, gr.multiplier, gr.frequency, gr.charge_dept, gr.charge_rate,
               gr.qty, gr.start_date, gr.end_date, gr.request_time, gr.remark, gr.inclusive
        FROM guest_request gr WHERE gr.booking_id = v_booking_id AND _use_active
        UNION ALL
        SELECT gr.booking_item, gr.id, gr.inclusion_id, gr.multiplier, gr.frequency, gr.charge_dept, gr.charge_rate,
               gr.qty, gr.start_date, gr.end_date, gr.request_time, gr.remark, gr.inclusive
        FROM backup.guest_request gr WHERE gr.booking_id = v_booking_id AND NOT _use_active
    ),
    -- Abstract source: deposit
    src_deposit AS (
        SELECT d.id, d.status, d.deposit_date, d.payment_id, 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 WHERE d.booking_id = v_booking_id AND _use_active
        UNION ALL
        SELECT d.id, d.status, d.deposit_date, d.payment_id, d.amount, d.remark,
               d.create_user, d.create_time, d.modify_user, d.modify_time, d.cancel_user, d.cancel_time
        FROM backup.deposit d WHERE d.booking_id = v_booking_id AND NOT _use_active
    ),
    -- Abstract source: booking_log
    src_booking_log AS (
        SELECT bl.id, bl.log_user, bl.log_type, bl.log_text, bl.log_date, bl.log_time
        FROM booking_log bl WHERE bl.booking_id = v_booking_id AND include_logs AND _use_active
        UNION ALL
        SELECT bl.id, bl.log_user, bl.log_type, bl.log_text, bl.log_date, bl.log_time
        FROM backup.booking_log bl WHERE bl.booking_id = v_booking_id AND include_logs AND NOT _use_active
    ),
    -- Main booking info
    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,
            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,
            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,
            b.is_archived
        FROM src_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
    ),
    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 src_guest_request br LEFT JOIN inclusion inc ON inc.id = br.inclusion_id
        WHERE INCLUDE_REQUESTS
    ),
    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 AND INCLUDE_REQUESTS
        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
        FROM all_requests br
        WHERE (booking_item IS NOT NULL) AND NOT inclusive AND INCLUDE_REQUESTS
        GROUP BY br.booking_item
    ),
    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, bi.arrival, bi.departure, (bi.departure-bi.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,
            bi.extra_child, bi.extra_infant, bi.allot_qty, bi.block_qty, bi.inhouse_qty, bi.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), '[]')
             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 src_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 src_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
        ORDER BY bi.item_id
    ),
    guests AS (
        SELECT guest_id FROM bk
        UNION
        SELECT guest_id FROM src_booking_guests
    ),
    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 src_deposit d LEFT JOIN department dp ON dp.id = d.payment_id
        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 src_booking_log
        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 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 CASE WHEN INCLUDE_LOGS
              THEN (SELECT row_to_json(dataset)
                    FROM (SELECT BK.*, details, guests, requirements, deposits, logs, summary FROM bk, objects, summ) AS dataset)
              ELSE (SELECT row_to_json(dataset)
                    FROM (SELECT BK.*, details, guests, requirements, deposits, summary FROM bk, objects, summ) AS dataset)
           END
    INTO json_result;

RETURN json_result;
END

sp_get_booking_js

Parameters

Definition