aonestar.public

Parameters

Name	Type	Mode
i_booking_id	integer	IN
i_mode	text	IN (DEFAULT 'one')
i_booking_item	integer	IN (DEFAULT NULL)

Definition

DECLARE
    v_group_name   text;
    v_room         RECORD;
    v_room_ordinal int := 0;
    v_n            int;
    v_occupied     int[];
    v_slot         int;
    v_last         text;
    v_guest_id     int;
    v_generated    int := 0;
BEGIN
    -- Group name: group_info.name -> booking.booking_name -> 'Guest'
    SELECT COALESCE(NULLIF(gi.name, ''), NULLIF(b.booking_name, ''), 'Guest')
    INTO v_group_name
    FROM booking b
    LEFT JOIN group_info gi ON gi.id = b.group_id
    WHERE b.id = i_booking_id;

IF NOT FOUND THEN
        RETURN 0;
    END IF;

FOR v_room IN
        SELECT item_id, adult, child, infant
        FROM booking_items
        WHERE booking_id = i_booking_id
          AND (namelist_group IS NOT NULL OR namelist_add = TRUE)
          AND (i_booking_item IS NULL OR item_id = i_booking_item)
        ORDER BY booking_seq, item_id
    LOOP
        v_room_ordinal := v_room_ordinal + 1;

v_n := CASE i_mode
                   WHEN 'one'   THEN 1
                   WHEN 'adult' THEN GREATEST(v_room.adult, 0)
                   WHEN 'all'   THEN GREATEST(v_room.adult + v_room.child + v_room.infant, 0)
                   ELSE 0
               END;

IF v_n < 1 THEN
            CONTINUE;
        END IF;

SELECT COALESCE(array_agg(room_seq), '{}')
        INTO v_occupied
        FROM booking_guests
        WHERE booking_id = i_booking_id AND booking_item = v_room.item_id;

FOR v_slot IN 1..v_n LOOP
            IF v_slot = ANY(v_occupied) THEN
                CONTINUE;   -- slot already has a guest -> skip
            END IF;

v_last := lpad(v_room_ordinal::text, 3, '0')
                      || CASE WHEN v_n > 1 THEN '-' || v_slot::text ELSE '' END;

v_guest_id := sp_save_guest(
                i_guest_id          => NULL,
                i_last_name         => v_last,
                i_first_name        => v_group_name,
                i_check_guest_exist => false
            );

INSERT INTO booking_guests(booking_id, guest_id, booking_item, room_seq)
            VALUES (i_booking_id, v_guest_id, v_room.item_id, v_slot);

UPDATE guest SET is_dummy = true WHERE id = v_guest_id;

v_generated := v_generated + 1;
        END LOOP;
    END LOOP;

RETURN v_generated;
END

sp_gen_namelist_guests

Parameters

Definition