aonestar.public

Parameters

Name	Type	Mode
rateplan_id	integer	IN
roomtype_id	integer	IN
arrival	date	IN
departure	date	IN
group_same_rates	boolean	IN (DEFAULT false)
result_code	text	OUT
result_msg	text	OUT
msg_code	text	OUT
rate_id	integer	OUT
rate_schedule	jsonb	OUT
i_booking_id	integer	IN (DEFAULT NULL)
i_scope	text	IN (DEFAULT 'all')
i_item_id	integer	IN (DEFAULT NULL)
i_register_id	integer	IN (DEFAULT NULL)

Definition

SELECT (
        -- booking side (ห้องที่ยังไม่ check in)
        CASE WHEN i_scope IN ('booking','all') AND i_booking_id IS NOT NULL THEN (
            SELECT COALESCE(jsonb_agg(el || jsonb_build_object('source','booking') ORDER BY (el->>'id')::int), '[]'::jsonb)
            FROM jsonb_array_elements(
                    sp_get_room_charge_schedule(booking_id := i_booking_id, booking_item := i_item_id)::jsonb
                 ) el
            WHERE (el->>'id')::int IN (
                SELECT item_id FROM booking_items
                WHERE booking_id = i_booking_id
                  AND (i_item_id IS NULL OR item_id = i_item_id)
                  AND (qty - inhouse_qty) > 0
            )
        ) ELSE '[]'::jsonb END
        ||
        -- in-house side (ห้องที่ check in แล้ว: status I/O)
        CASE WHEN i_scope IN ('inhouse','all') THEN (
            SELECT COALESCE(jsonb_agg(el || jsonb_build_object('source','inhouse') ORDER BY rg.id), '[]'::jsonb)
            FROM registration rg
                CROSS JOIN LATERAL jsonb_array_elements(
                    sp_get_room_charge_schedule(register_id := rg.id)::jsonb
                ) el
            WHERE (i_register_id IS NOT NULL AND rg.id = i_register_id)
               OR (i_register_id IS NULL AND rg.booking_id = i_booking_id AND rg.status IN ('I','O'))
        ) ELSE '[]'::jsonb END
    )::json

sp_get_rate_schedule_js

Parameters

Definition