aonestar.public

Parameters

Name	Type	Mode
i_register_id	integer	IN
i_user	text	IN (DEFAULT NULL)

Definition

DECLARE
    v_pin_on          boolean := is_interface_active('DOORLOCK_PIN');
    v_room_id         int;
    v_room_number     text;
    v_booking_id      int;
    v_valid_from      timestamp;
    v_valid_until     timestamp;
    v_actual_from     timestamp;
    v_actual_until    timestamp;
    v_payload         jsonb;
    v_cfg             jsonb;
    v_url             text;
    v_http_status     int;
    v_http_content    text;
    v_resp            jsonb;
    v_pin             text;
    v_keyboard_pwd_id jsonb;
    v_reason          text;
    v_state           text;
    v_message         text;
    v_detail          text;
    v_hint            text;
    v_context         text;
BEGIN
    IF NOT v_pin_on THEN
        RETURN NULL;
    END IF;

SELECT rg.room_id,
           fn_room_number(rg.room_id),
           rg.booking_id,
           (rg.arrival + COALESCE(b.arrival_time, fn_default('CHECKIN_TIME', '12:00'::time)))::timestamp,
           (rg.departure + COALESCE(rg.departure_time, fn_default('CHECKOUT_TIME', '12:00'::time)))::timestamp
      INTO v_room_id, v_room_number, v_booking_id, v_valid_from, v_valid_until
    FROM registration rg
    LEFT JOIN booking b ON b.id = rg.booking_id
    WHERE rg.id = i_register_id;

IF NOT FOUND THEN
        RAISE EXCEPTION 'Registration not found: %', i_register_id;
    END IF;
    IF NULLIF(v_room_number, '') IS NULL THEN
        RAISE EXCEPTION 'Room number is missing for registration %', i_register_id;
    END IF;

SELECT configs INTO v_cfg FROM interfaces WHERE code = 'DOORLOCK_PIN';
    v_url := NULLIF(BTRIM(v_cfg->>'api_url'), '');
    IF v_url IS NULL THEN
        RAISE EXCEPTION 'DOORLOCK_PIN api_url is not configured';
    END IF;

v_payload := jsonb_build_object(
        'hotel_code', fn_prop_code(),
        'roomno', v_room_number,
        'effect', to_char(v_valid_from, 'FMMM/FMDD/YYYY HH24:MI:SS'),
        'expire', to_char(v_valid_until, 'FMMM/FMDD/YYYY HH24:MI:SS')
    );

SELECT h.status, h.content
      INTO v_http_status, v_http_content
    FROM public.http_post(v_url::varchar, v_payload, '{}'::jsonb) h;

IF v_http_status IS NULL OR v_http_status NOT BETWEEN 200 AND 299 THEN
        RAISE EXCEPTION 'DoorLock API returned HTTP %: %',
            COALESCE(v_http_status::text, 'NULL'), COALESCE(v_http_content, 'empty response');
    END IF;

BEGIN
        v_resp := v_http_content::jsonb;
    EXCEPTION WHEN OTHERS THEN
        v_resp := jsonb_build_object('raw_response', v_http_content);
        RAISE EXCEPTION 'DoorLock API returned invalid JSON';
    END;

IF COALESCE(v_resp->>'result_code', '') <> '0' THEN
        RAISE EXCEPTION '%', COALESCE(NULLIF(v_resp->>'result_msg', ''),
                                      'DoorLock API returned result_code ' || COALESCE(v_resp->>'result_code', 'NULL'));
    END IF;

v_pin := NULLIF(v_resp #>> '{result_data,keyboardPwd}', '');
    v_keyboard_pwd_id := v_resp #> '{result_data,keyboardPwdId}';
    IF v_pin IS NULL THEN
        RAISE EXCEPTION 'DoorLock API success response has no keyboardPwd';
    END IF;

v_actual_from := COALESCE(NULLIF(v_resp->>'adjusted_effect', '')::timestamp, v_valid_from);
    v_actual_until := COALESCE(NULLIF(v_resp->>'adjusted_expire', '')::timestamp, v_valid_until);

INSERT INTO intf_doorlock_logs (
        register_id, room_id, booking_id, credential_type, pin_code,
        valid_from, valid_until, status, api_request, api_response, log_user
    ) VALUES (
        i_register_id, v_room_id, v_booking_id, 'PIN', v_pin,
        v_actual_from, v_actual_until, 'P', v_payload, v_resp, i_user
    );

RETURN jsonb_build_object(
        'pin_code', v_pin,
        'keyboard_pwd_id', v_keyboard_pwd_id,
        'valid_from', v_actual_from,
        'valid_until', v_actual_until
    );
EXCEPTION
    WHEN SQLSTATE '99999' THEN
        RAISE;
    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;
        v_reason := COALESCE(NULLIF(v_message, ''), 'Unknown DoorLock error');

PERFORM sp_raise_error(jsonb_build_object(
            'result_code', '4',
            'msg_code', '20216',
            'result_msg', sys_msg_format('20216', 'Unable to create door passcode: %s', v_reason),
            'result_data', jsonb_build_object(
                'source', 'DOORLOCK_PIN',
                'register_id', i_register_id,
                'room_id', v_room_id,
                'booking_id', v_booking_id,
                'valid_from', v_valid_from,
                'valid_until', v_valid_until,
                'api_request', v_payload,
                'api_response', v_resp,
                'upstream_error', jsonb_build_object(
                    'sql_state', v_state,
                    'message', v_message,
                    'detail', v_detail,
                    'hint', v_hint,
                    'context', v_context
                )
            )
        )::json);
        RETURN NULL;
END

sp_gen_door_credential

Parameters

Definition