aonestar.public

Parameters

Name	Type	Mode
i_booking_item	integer	IN
i_room_id	integer	IN
i_checkin_user	text	IN

Definition

DECLARE
    v_row          record;
    v_result       json;
    v_register_ids integer[] := '{}';
    v_errors       jsonb     := '[]';
    v_n            integer;
    is_first       boolean   := true;
BEGIN
    FOR v_row IN
        SELECT bg.guest_id
        FROM booking_guests bg
        WHERE bg.booking_item = i_booking_item
        ORDER BY bg.room_seq NULLS LAST, bg.guest_id
    LOOP
        v_result := sp_check_in(
            i_booking_item, i_room_id, v_row.guest_id,
            i_checkin_user, NULL, FALSE,
            NOT is_first   -- FALSE for primary, TRUE for followers
        );
        IF (v_result::jsonb->>'result_code') = '0' THEN
            v_register_ids := v_register_ids
                           || (v_result::jsonb->'result_data'->>'register_id')::int;
            is_first := false;
        ELSE
            v_errors := v_errors || jsonb_build_array(jsonb_build_object(
                'item_id',  i_booking_item,
                'guest_id', v_row.guest_id,
                'message',  v_result::jsonb->>'result_msg'));
        END IF;
    END LOOP;

v_n := array_length(v_register_ids, 1);

-- Split room_rate evenly across all registrations; remainder goes to last guest
    IF coalesce(v_n, 0) > 1 THEN
        WITH indexed AS (
            SELECT t.register_id, t.idx
            FROM unnest(v_register_ids) WITH ORDINALITY AS t(register_id, idx)
        )
        UPDATE registration_rates rr
        SET room_rate     = CASE
                WHEN ir.idx < v_n
                THEN FLOOR(br.room_rate::numeric / v_n)::public.t_money
                ELSE (br.room_rate::numeric
                      - FLOOR(br.room_rate::numeric / v_n) * (v_n - 1))::public.t_money
                END,
            extrabed_rate = CASE WHEN ir.idx = 1 THEN rr.extrabed_rate ELSE 0::public.t_money END,
            child_rate    = CASE WHEN ir.idx = 1 THEN rr.child_rate    ELSE 0::public.t_money END,
            infant_rate   = CASE WHEN ir.idx = 1 THEN rr.infant_rate   ELSE 0::public.t_money END
        FROM indexed ir
        JOIN booking_rates br
          ON br.booking_item = i_booking_item AND br.charge_date = rr.charge_date
        WHERE rr.register_id = ir.register_id
          AND NOT rr.posted;
    END IF;

RETURN jsonb_build_object(
        'checked_in',   coalesce(v_n, 0),
        'register_ids', to_jsonb(v_register_ids),
        'errors',       v_errors
    );
END

sp_checkin_split_folio

Parameters

Definition