aonestar.public

Parameters

Name	Type	Mode
i_booking_item	integer	IN
i_room_id	integer	IN
i_guest_ids	int4[]	IN

Definition

DECLARE
    v_booking_id int;
    v_missing    jsonb := '[]'::jsonb;
    v_max        int;
    v_used       int;
    v_g          int;
    v_register_id int;
BEGIN
    SELECT booking_id INTO v_booking_id FROM booking_items WHERE item_id = i_booking_item;

-- ต่อ guest: ต้องมีเอกสาร (passport/id) + signature (guest_signature ผูก booking)
    FOREACH v_g IN ARRAY COALESCE(i_guest_ids, '{}'::int[]) LOOP
        IF NOT EXISTS (SELECT 1 FROM guest_document d
                       WHERE d.guest_id = v_g AND d.doc_type IN (1,2) AND COALESCE(d.doc_number,'') <> '') THEN
            v_missing := v_missing || jsonb_build_object('guest_id', v_g, 'reason', '20210',
                            'message', sys_msg('20210','Guest %s has no ID document.', NULL, v_g::text));
        END IF;
        IF NOT EXISTS (SELECT 1 FROM guest_signature gs
                       WHERE gs.booking_id = v_booking_id AND gs.guest_id = v_g) THEN
            v_missing := v_missing || jsonb_build_object('guest_id', v_g, 'reason', '20211',
                            'message', sys_msg('20211','Guest %s has no signature.', NULL, v_g::text));
        END IF;
    END LOOP;

-- capacity: จำนวนแขก (ที่ส่งมา + ที่ check-in ห้องนี้แล้ว) ต้องไม่เกิน adult+child+infant
    SELECT rg.id INTO v_register_id
    FROM registration rg
    WHERE rg.status = 'I' AND rg.booking_item = i_booking_item AND rg.room_id = i_room_id
    LIMIT 1;

IF v_register_id IS NOT NULL THEN
        SELECT (rg.adult + rg.child + rg.infant),
               1 + (SELECT count(*) FROM register_guests WHERE register_id = v_register_id)
          INTO v_max, v_used
        FROM registration rg WHERE rg.id = v_register_id;
        -- แขกใหม่ที่ยังไม่อยู่ใน register_guests/primary
        v_used := v_used + (SELECT count(*) FROM unnest(i_guest_ids) g
                            WHERE g NOT IN (SELECT guest_id FROM register_guests WHERE register_id = v_register_id)
                              AND g <> (SELECT guest_id FROM registration WHERE id = v_register_id));
    ELSE
        SELECT (bi.adult + bi.child + bi.infant) INTO v_max FROM booking_items bi WHERE bi.item_id = i_booking_item;
        v_used := COALESCE(array_length(i_guest_ids, 1), 0);
    END IF;

RETURN jsonb_build_object(
        'ok', (jsonb_array_length(v_missing) = 0 AND v_used <= COALESCE(v_max, v_used)),
        'missing', v_missing,
        'capacity', jsonb_build_object('used', v_used, 'max', v_max,
                     'exceeded', v_used > COALESCE(v_max, v_used)));
END

fn_selfcheckin_validate_guests

Parameters

Definition