aonestar.public

Parameters

Name	Type	Mode
booking_item	integer	IN
room_id	integer	IN (DEFAULT NULL)
guest_id	integer	IN (DEFAULT NULL)
data_only	boolean	IN (DEFAULT false)

Definition

DECLARE
    result_data json;
    _block_id int;
    rate json;
    is_early_checkin bool;
BEGIN
    _block_id := (SELECT id FROM room_block rb WHERE rb.booking_item = $1 AND rb.room_id = $2 AND NOT rb.checked_in LIMIT 1);
    is_early_checkin := (fn_system_date() < (SELECT arrival FROM booking_items WHERE item_id = $1));

    with BK as (
        SELECT b.id as booking_id, bi.item_id AS booking_item, _block_id AS block_id, b.book_type, b.status AS book_status,--, 'I' AS status
                b.stay_type, b.confirm_no,
                IIF(b.status IN ('R','C'), b.folio_id, NULL) folio_id,
                b.rsvtype_id, rst.name as reservation_type, b.book_by,
                COALESCE($3, iif(b.status IN ('I','O'), NULL, b.guest_id)) AS 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, bi.arrival, b.arrival_time, b.arrive_by, b.arrive_from,
                bi.departure, b.departure_time, b.depart_by, b.depart_to, (bi.departure-bi.arrival) AS nights,
                b.sales_id, ss.name as sales_name,
                b.market_id, mk.name as market_name,
                0 msg_count, 0 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,

                rm.id AS room_id, rm.room_number, 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, adult, child, infant, --1 AS qty,
                COALESCE(extra_adult, 0) AS extra_adult,
                COALESCE(extra_child, 0) AS extra_child,
                COALESCE(extra_infant,0) AS extra_infant,
                allot_qty, charge_to,
                b.status IN ('R','C') AS is_first_room,
                (SELECT COALESCE(json_agg(rates), '[]')
                 FROM (
                    SELECT 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, br.mealtype_id
                    FROM booking_rates br
                    WHERE br.booking_item = bi.item_id
                    ORDER BY 1
                ) AS rates) AS rate_schedule
        from booking_items bi LEFT JOIN booking b ON bi.booking_id = b.id
               -- LEFT JOIN room_block rb ON rb.booking_item = $1 AND rb.room_id = $2
                left join channel cn on cn.id = b.channel_id
                left join reservation_type rst on rst.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 rate_plan rp on rp.id = bi.rateplan_id
                left join meal_type mt on mt.id = bi.mealtype_id
                LEFT JOIN room rm ON rm.id = $2
                left join room_type rt on rt.id = COALESCE(rm.roomtype_id, bi.roomtype_id)
                left join bed_type bt on bt.id = COALESCE(rm.bedtype_id, bi.bedtype_id)
                LEFT JOIN v_rate_details rd ON rd.rateplan_id = bi.rateplan_id AND rd.roomtype_id = bi.roomtype_id
        where bi.item_id = $1

--    ), rates AS (
--        SELECT 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, br.mealtype_id
--        FROM booking_rates AS br,
--             LATERAL (SELECT rd.rate_extra_adult, rd.rate_extra_child, rd.rate_extra_infant
--                      FROM v_rate_details rd, bk
--                      WHERE rd.rateplan_id = bk.rateplan_id AND rd.roomtype_id = bk.roomtype_id
--                     ) AS rd
--        WHERE br.booking_item = $1
--        ORDER BY 1

    ), deposits AS (
        SELECT d.id, d.status, d.deposit_date, d.payment_id, dp.name AS payment_name, d.amount-COALESCE(d.clearing_amount,0) AS amount,
            d.remark, d.create_user, d.create_time, d.modify_user, d.modify_time, d.cancel_user, d.cancel_time
        --SELECT 'R' AS status, sum(d.amount-COALESCE(d.clearing_amount,0)) amount
        FROM deposit d LEFT JOIN department dp ON dp.id = d.payment_id, BK
        WHERE bk.is_first_room AND d.booking_id = bk.booking_id AND d.status IN ('R','H') AND d.amount-COALESCE(d.clearing_amount,0) <> 0

    ), requests AS (
        SELECT NULL::int AS 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, BK
        WHERE br.booking_id = bk.booking_id --AND (br.booking_item IS NULL OR br.booking_item = $1) --NOT br.inclusive AND
             -- AND NOT br.charge_to_booking
              AND (br.booking_item = bk.booking_item
                    OR (br.booking_item IS NULL AND (br.multiplier <> 1 OR is_first_room))
                  )

    ), jsons as (
        SELECT --BK.*,
            --(SELECT COALESCE(json_agg(rates), '[]') from rates) as rate_schedule,
            sp_get_guest_list_js(ARRAY[bk.guest_id]) as guests,
            (SELECT COALESCE(json_agg(requests), '[]') from requests) as all_requests,
            (SELECT COALESCE(json_agg(requests), '[]') from requests WHERE NOT inclusive) as requirements,
            (SELECT COALESCE(json_agg(deposits), '[]') from deposits) as deposits
            --'[]'::json AS deposits
           --(SELECT sp_get_checkin_summary_js($1, $2)) AS summary
        FROM BK
    ), summ AS (
        Select sp_get_checkin_summary_js(row_to_json(checkin_data)::jsonb) AS summary
        from  (SELECT BK.*, all_requests AS requirements, deposits FROM BK, jsons) AS checkin_data
    )
    SELECT
        CASE data_only
            WHEN FALSE THEN
                (SELECT row_to_json(dataset)
                 FROM (SELECT NULL AS register_id, BK.*, guests, requirements, deposits, '[]'::json AS logs, summary FROM BK, jsons, summ) AS dataset)
            WHEN TRUE THEN
                (SELECT row_to_json(dataset)
                 FROM (SELECT NULL AS register_id, BK.*, guests, requirements, deposits FROM BK, jsons) AS dataset)
        END
--    SELECT row_to_json(dataset)
--    FROM  (SELECT NULL AS register_id, BK.*, guests, requirements, deposits, '[]'::json AS logs, summary FROM BK, jsons, summ) AS dataset
    INTO result_data;

    PERFORM sp_sys_log('sp_get_checkin_details_js', result_data::text);

    RETURN result_data;
END