aonestar.public

Parameters

Name	Type	Mode
p_booking_count	integer	IN (DEFAULT 0)
p_inhouse_count	integer	IN (DEFAULT 0)
p_block_room	boolean	IN (DEFAULT false)
p_roomtype_id	int4[]	IN (DEFAULT NULL)

Definition

DECLARE
    _ch_id       int;
    _rp_id       int;
    _sysdate     date;
    _i           int;
    _room_id     int;
    _booking_id  int;
    _result_code text;
    _result_msg  text;
    _msg_code    text;
    _ci_result   json;
    _booking_ok  int := 0;
    _inhouse_ok  int := 0;
    _arrival     date;
    _departure   date;
    _nights      int;
    _offset      int;
    _rt_ids      int[];
    _rt_count    int;
    _curr_rt_id  int;
    _curr_rp_id  int;
    v_state      TEXT; v_message TEXT; v_detail TEXT; v_hint TEXT; v_context TEXT;
BEGIN
    _sysdate := fn_system_date();

SELECT id INTO _ch_id FROM channel WHERE origin_id IS NOT NULL ORDER BY id LIMIT 1;
    SELECT id INTO _rp_id FROM rate_plan ORDER BY id LIMIT 1;

-- room types ที่ใช้: ถ้าระบุ p_roomtype_id ใช้เฉพาะ type นั้น มิฉะนั้น cycle ทุก type
    -- กรองเฉพาะ type ที่มี rate_amount (plan_type<>9) เพื่อให้ sp_inventory_rates ทำงานได้
    -- กรองเฉพาะ room type ที่มี rate plan ใช้งานได้ (prefer non-open-rate; ถ้าไม่มีใช้ open rate)
    SELECT array_agg(rt.id ORDER BY rt.id) INTO _rt_ids
    FROM room_type rt
    WHERE (p_roomtype_id IS NULL OR rt.id = ANY(p_roomtype_id))
      AND EXISTS (
        SELECT 1 FROM rate_details rd
        JOIN rate_plan rp ON rp.id = rd.rateplan_id
        WHERE rd.roomtype_id = rt.id
          AND rd.active = true
          AND rp.enabled = true
          AND (rp.end_date IS NULL OR rp.end_date >= _sysdate)
          AND (rd.rate_amount IS NOT NULL OR rp.plan_type = 9)
    );

_rt_count := coalesce(array_length(_rt_ids, 1), 0);

IF _ch_id IS NULL OR _rp_id IS NULL OR _rt_count = 0 THEN
        RETURN fn_result_error('99', 'Missing reference data: channel (with origin_id), room_type with rates, or rate_plan not found');
    END IF;

-- ── Bookings ──────────────────────────────────────────────
    -- arrival: กระจายใน 1-30 วัน
    -- nights: หมุนเวียน 3 pattern — short 1-2, medium 3-5, long 7-14
    -- room type: วนผ่านทุก room type ที่มี rate
    FOR _i IN 1..GREATEST(p_booking_count, 0) LOOP
        BEGIN
            _offset     := 1 + floor(random() * 30)::int;
            _nights     := CASE _i % 3
                               WHEN 0 THEN 1 + floor(random() * 2)::int   -- short: 1-2 คืน
                               WHEN 1 THEN 3 + floor(random() * 3)::int   -- medium: 3-5 คืน
                               ELSE        7 + floor(random() * 8)::int   -- long: 7-14 คืน
                           END;
            _arrival    := _sysdate + _offset;
            _departure  := _arrival + _nights;
            _curr_rt_id := _rt_ids[((_i - 1) % _rt_count) + 1];

-- prefer non-open-rate plan; fall back to open rate if none
            SELECT rd.rateplan_id INTO _curr_rp_id
            FROM rate_details rd
            JOIN rate_plan rp ON rp.id = rd.rateplan_id
            WHERE rd.roomtype_id = _curr_rt_id
              AND rd.active = true
              AND rp.enabled = true
              AND (rp.end_date IS NULL OR rp.end_date >= _sysdate)
              AND (rd.rate_amount IS NOT NULL OR rp.plan_type = 9)
            ORDER BY (rp.plan_type = 9) ASC, rd.rateplan_id ASC
            LIMIT 1;
            _curr_rp_id := coalesce(_curr_rp_id, _rp_id);

SELECT result_code, result_msg, msg_code, out_booking_id
            INTO _result_code, _result_msg, _msg_code, _booking_id
            FROM sp_create_booking(
                jsonb_build_object(
                    'arrival',    _arrival,
                    'departure',  _departure,
                    'channel_id', _ch_id,
                    'guests', jsonb_build_array(
                        jsonb_build_object(
                            'first_name', 'Test' || lpad(_i::text, 4, '0'),
                            'last_name',  'Guest'
                        )
                    ),
                    'details', jsonb_build_array(
                        jsonb_build_object(
                            'item_id',     1,
                            'roomtype_id', _curr_rt_id,
                            'rateplan_id', _curr_rp_id,
                            'arrival',     _arrival,
                            'departure',   _departure,
                            'qty',         1,
                            'adult',       2,
                            'child',       0,
                            'infant',      0
                        )
                    )
                ),
                'TEST',
                false,        -- confirm
                null,         -- confirm_remark
                p_block_room  -- auto_block_room
            );
            IF _result_code = '0' THEN
                _booking_ok := _booking_ok + 1;
            ELSE
                RAISE WARNING 'sp_gen_test_data: booking % failed code=% msg=%', _i, _result_code, _result_msg;
            END IF;
        EXCEPTION WHEN OTHERS THEN
            RAISE WARNING 'sp_gen_test_data: booking % error: %', _i, SQLERRM;
        END;
    END LOOP;

-- ── In-house (walk-in) ────────────────────────────────────
    _i := 0;
    FOR _room_id IN (
        SELECT r.id
        FROM room r
        WHERE NOT EXISTS (
            SELECT 1 FROM registration rg
            WHERE rg.room_id = r.id AND rg.status = 'I'
        )
        ORDER BY r.id
        LIMIT GREATEST(p_inhouse_count, 0)
    ) LOOP
        _i := _i + 1;
        BEGIN
            _ci_result := sp_check_in(
                NULL,      -- i_booking_item (NULL = walk-in)
                _room_id,
                0,         -- i_guest_id (< 1 → null; guests come from checkin_data)
                'TEST',
                jsonb_build_object(
                    'departure',   _sysdate + (CASE _i % 3
                                       WHEN 0 THEN 1 + floor(random() * 2)::int   -- short 1-2
                                       WHEN 1 THEN 3 + floor(random() * 3)::int   -- medium 3-5
                                       ELSE        7 + floor(random() * 8)::int   -- long 7-14
                                   END),
                    'rateplan_id', _rp_id,
                    'adult',       2,
                    'child',       0,
                    'infant',      0,
                    'guests', jsonb_build_array(
                        jsonb_build_object(
                            'first_name', 'Inhouse' || lpad(_i::text, 4, '0'),
                            'last_name',  'Test'
                        )
                    )
                ),
                true       -- walk_in
            );
            IF _ci_result->>'result_code' = '0' THEN
                _inhouse_ok := _inhouse_ok + 1;
            ELSE
                RAISE WARNING 'sp_gen_test_data: inhouse room % failed code=% msg=%',
                    _room_id, _ci_result->>'result_code', _ci_result->>'result_msg';
            END IF;
        EXCEPTION WHEN OTHERS THEN
            RAISE WARNING 'sp_gen_test_data: inhouse room % error: %', _room_id, SQLERRM;
        END;
    END LOOP;

RETURN fn_result_success(jsonb_build_object(
        'booking_count', _booking_ok,
        'inhouse_count', _inhouse_ok
    ));

EXCEPTION WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
        v_state   = RETURNED_SQLSTATE, v_message = MESSAGE_TEXT,
        v_detail  = PG_EXCEPTION_DETAIL, v_hint = PG_EXCEPTION_HINT, v_context = PG_EXCEPTION_CONTEXT;
    RETURN fn_handle_error(v_state, v_message, v_detail, v_hint, v_context, 'sp_gen_test_data',
        jsonb_build_object(
            'p_booking_count', p_booking_count,
            'p_inhouse_count', p_inhouse_count,
            'p_block_room',    p_block_room,
            'p_roomtype_id',   p_roomtype_id
        ));
END