aonestar.public

Parameters

Name	Type	Mode
i_booking_item	integer	IN
i_room_id	integer	IN
i_guest_id	integer	IN
i_checkin_user	text	IN (DEFAULT NULL)
checkin_data	jsonb	IN (DEFAULT NULL)
walk_in	boolean	IN (DEFAULT false)

Definition

DECLARE
    guests int[] = '{}';
    updated_items int[] = '{}';
    item record;
    _booking_id int;
    _register_id int;
    _guest_id int;
    _rate_id int;
    _arrival date;
    _departure date;
    _folio_id int;
    _folio_pattern_id int;
    _room_id int;
    _room_number TEXT;
    _rateplan_id int;
    _mealtype_id int;
    _rate_name TEXT;
    _bk_folio_id int;
    _block_id int = null;
    _block_room_id int;
    is_walk_in boolean;
    is_join_room bool;
    is_room_changed boolean;
    is_house_used boolean;
    book_status char(1);
    log_text TEXT;
    _state   TEXT;
    _detail  TEXT;
    _hint    TEXT;
    _context TEXT;
    _result_code TEXT;
    _result_msg TEXT;
    _msg_code TEXT;
BEGIN
    is_walk_in := i_booking_item IS NULL;
    _block_id  := (checkin_data->>'block_id')::int;
    IF i_guest_id < 1 THEN
        i_guest_id := null;
    END IF;

    PERFORM sp_raise_error_if(i_room_id is null, 'Room number is missing');

    IF (i_guest_id is null) and (coalesce(jsonb_array_length(checkin_data->'guests'), 0) = 0) THEN
        PERFORM sp_raise_error('99998', 'Guest information is missing' );
    END IF;

    CALL sp_check_endday_process();

    IF NOT is_walk_in THEN

        SELECT bi.booking_id, b.status, b.folio_id, coalesce(_block_id, rb.id), rb.room_id
        FROM booking_items bi
            LEFT JOIN booking b ON b.id = bi.booking_id
            LEFT JOIN room_block rb ON rb.booking_item = i_booking_item AND rb.room_id = i_room_id AND NOT rb.checked_in
        WHERE bi.item_id = i_booking_item
        INTO _booking_id, book_status, _bk_folio_id, _block_id, _block_room_id;

        CALL sp_check_record_lock(i_checkin_user, booking_id := _booking_id, booking_item := i_booking_item, block_id := _block_id);

        IF checkin_data IS NULL THEN
            -- Retrieve checkin details if no check-in data supplied
            checkin_data := sp_get_checkin_details_js(i_booking_item, i_room_id, i_guest_id, data_only=>true);
        END IF;
    END IF;

    -- Validate checkin
        -- check booking status
        -- check stay period
        -- check number of rooms remains in booking
        -- check room is ready to check in
    _arrival     := fn_system_date();
    _departure   := (checkin_data->>'departure')::date;
    _rateplan_id := (checkin_data->>'rateplan_id')::int;
    i_room_id    := COALESCE(i_room_id, (checkin_data->>'room_id')::int);
    is_room_changed := (_block_room_id is not null) and (_block_room_id <> i_room_id);

    PERFORM sp_raise_error_if(_rateplan_id is null, 'Rate plan is missing');
    PERFORM sp_raise_error_if(i_room_id is null, 'Room number is missing');

    CALL sp_check_room_activity(i_room_id, _arrival, _departure, block_id := _block_id, check_room_status := true);

    -- Start check-in process
    guests         := sp_save_guests_js(checkin_data->'guests', preserve_old_values=>false);

    i_checkin_user := coalesce(i_checkin_user, checkin_data->>'checkin_user', fn_current_user());
    is_join_room   := EXISTS(SELECT * FROM registration rg WHERE rg.status = 'I' AND rg.room_id = i_room_id AND rg.booking_id IS NOT DISTINCT FROM _booking_id);

    i_guest_id := guests[1];

    select coalesce((checkin_data->>'mealtype_id')::int, rp.mealtype_id), rp.plan_type = 6
    from rate_plan rp
    where rp.id = _rateplan_id
    into _mealtype_id, is_house_used;

    --sp_calc_extra(bi.rateplan_id, bi.roomtype_id, bi.adult, bi.child, bi.infant) as ex

    INSERT INTO registration (booking_id, booking_item, room_id, guest_id, arrival, departure, departure_time,
        walk_in, adult, child, infant, extra_adult, extra_child, extra_infant, checkin_user, member_code,
        channel_id, origin_id, arrive_by, arrive_from, depart_by, depart_to, voucher_no,
        sales_id, market_id, stay_type, note, rateplan_id, mealtype_id, rate_id, charge_to_booking,
        folio_pattern_id, promo_code, payment_method, credit_limit, rsvtype_id, house_used)
    SELECT _booking_id, i_booking_item,
        COALESCE(i_room_id, room_id),
        i_guest_id, _arrival, departure, departure_time,
        is_walk_in, adult, child, infant,
        ex.extra_adult, --COALESCE(extra_adult,0),
        ex.extra_child, --COALESCE(extra_child,0),
        ex.extra_infant, --COALESCE(extra_infant,0),
        i_checkin_user, member_code, channel_id,
        COALESCE(origin_id, fn_default('ORIGIN')::int),
        arrive_by, arrive_from, depart_by, depart_to, voucher_no,
        sales_id, market_id,
        COALESCE(stay_type,'D'),
        note, rateplan_id, _mealtype_id,
        rate_id,
        COALESCE(charge_to,'B') = 'B',
        COALESCE(payment->>'folio_pattern_id', fn_default('FOLIO_PATTERN'))::int,
        payment->>'promo_code',
        COALESCE(payment->>'payment_method', fn_default('PAYMENT_METHOD')),
        COALESCE(payment->>'credit_limit', fn_default('CREDIT_LIMIT'))::t_money,
        COALESCE(rsvtype_id, fn_default('RESERVATION_TYPE', null::int)),
        coalesce(is_house_used, false)
    FROM jsonb_to_record(checkin_data)
    AS reg(room_id int, guest_id int, arrival date, departure date, departure_time time,
           adult int, child int, infant int, extra_adult int, extra_child int, extra_infant int, member_code TEXT,
           channel_id int, origin_id int, arrive_by text, arrive_from text , depart_by text, depart_to text,
           folio_pattern_id int, voucher_no text, promo_code text, sales_id int, market_id int, rsvtype_id int,
           stay_type char(1), note text, rateplan_id int, mealtype_id int, rate_id int, payment jsonb, charge_to char(1))
       LEFT JOIN room rm on rm.id = COALESCE(i_room_id, reg.room_id),
       LATERAL sp_calc_extra(reg.rateplan_id, rm.roomtype_id, reg.adult, reg.child, reg.infant) as ex
    RETURNING id, room_id, rate_id, arrival, departure, guest_id, folio_pattern_id, rateplan_id
    INTO _register_id, _room_id, _rate_id, _arrival, _departure, _guest_id, _folio_pattern_id, _rateplan_id;

    _rate_name   := (SELECT name FROM rate_plan WHERE id = _rateplan_id);
    _room_number := fn_room_number(_room_id);
    log_text     := format('%s: %s, %s to %s @ %s', IIF(is_walk_in, 'Walk in', 'Check in'), _room_number, _arrival, _departure, _rate_name);

    /* Save check in log */
    PERFORM sp_log_inhouse(_register_id, 'Check in', log_text, i_checkin_user);

    UPDATE room_status SET status = 'OCC' WHERE room_id = _room_id;

    IF book_status <> 'I' AND _bk_folio_id IS NOT NULL THEN
        -- Change booking folio to guest folio for first guest check-in
        UPDATE folio f
        SET folio_type = 'R', register_id = _register_id, booking_id = NULL
        WHERE f.id = _bk_folio_id;
        _folio_id = _bk_folio_id;

        UPDATE deposit d
        SET status = 'C', clearing_date = _arrival
        WHERE d.booking_id = _booking_id AND d.status IN ('R','H');
    ELSE
        -- Create new guest folio for other guest
        INSERT INTO folio(folio_type, booking_id, register_id, folio_pattern_id, open_user)
        VALUES('R', NULL, _register_id, _folio_pattern_id, i_checkin_user)
        RETURNING id INTO _folio_id;
    END IF;

    UPDATE registration SET folio_id = _folio_id WHERE id = _register_id;

    PERFORM sp_sys_log('sp_check_in', '_guest_id = '||_guest_id);

    -- Set registered flag in booking_guests
    IF _booking_id is not null THEN
        update booking_guests bg set registered = True
        where bg.booking_id = _booking_id and bg.guest_id = _guest_id and bg.registered = False;
    END IF;

    -- Add more guests
    INSERT INTO register_guests (register_id, guest_id)
    SELECT _register_id, guest_id
    FROM unnest(guests) guest_id
    WHERE guest_id <> _guest_id
    ON CONFLICT DO NOTHING;

    IF NOT is_walk_in THEN
        UPDATE booking_guests bg
        SET registered = TRUE, register_id = _register_id
        WHERE bg.booking_id = _booking_id AND bg.guest_id = ANY(guests);

        -- Update room_block's status
        IF _block_id IS NOT NULL THEN
            UPDATE room_block
            SET checked_in = TRUE, status = 'I', register_id = _register_id
            WHERE id = _block_id;
        END IF;

        -- Update booking's qty, block_qty if not a share room
        IF NOT is_join_room THEN
            UPDATE booking_items bi
            SET inhouse_qty = bi.inhouse_qty +1,
                block_qty = bi.block_qty - IIF(_block_id IS NULL, 0, 1)
            WHERE bi.item_id = i_booking_item AND bi.qty > bi.inhouse_qty;
        END IF;

        UPDATE booking b
        SET status = 'I'--,
            --folio_id = coalesce(b.folio_id, sp_create_booking_folio(b.id))
        WHERE id = _booking_id AND status IN ('R','C');

        PERFORM sp_save_registration_rates(_register_id, _rate_id, _arrival, _departure, null);

        /* Save room change log */
        if is_room_changed then
            PERFORM sp_log_inhouse(_register_id,
                       'Room change',
                       'Room change '||fn_room_number(_block_room_id)||' to '||_room_number||coalesce(', '||checkin_data->>'room_change_reason', ''),
                        i_checkin_user,
                       _block_room_id, _room_id
            );
        end if;

        /* Save room upgrade */
        if (checkin_data ? 'upgrade_type') and (checkin_data->>'upgrade_type' in ('upgrade','upsell','none')) then
            INSERT INTO room_upgrade (register_id, upgrade_type, old_amount, new_amount)
            VALUES (_register_id,
                    checkin_data->>'upgrade_type',
                    (select sum(room_rate) from booking_rates r where r.booking_item = $1),
                    (select sum(room_rate) from registration_rates r where r.register_id = _register_id)
                   );
            case checkin_data->>'upgrade_type'
                when 'upgrade' then PERFORM sp_log_inhouse(_register_id, 'Upgrade', 'Free upgrade', i_checkin_user);
                when 'upsell' then PERFORM sp_log_inhouse(_register_id, 'Upsell', 'Upselling', i_checkin_user);
                else
            end case;
        end if;
    ELSE
        PERFORM sp_save_registration_rates(_register_id, _rate_id, _arrival, _departure, checkin_data->'rate_schedule');
    END IF;

    /* Save room upgrade */
    if (checkin_data ? 'upgrade_type') and (checkin_data->>'upgrade_type' in ('upgrade','upsell','none')) then
        INSERT INTO public.room_upgrade (register_id, upgrade_type, old_amount, new_amount)
        VALUES (_register_id,
                checkin_data->>'upgrade_type',
                (select sum(room_rate) from booking_rates where booking_item = i_booking_item),
                (select sum(room_rate) from registration_rates where register_id = _register_id)
               );
    end if;


        --RAISE NOTICE 'requirements: %', jsonb_pretty((checkin_data->'requirements')::jsonb);
    PERFORM sp_save_guest_requests(request_data  => checkin_data->'requirements',
                                   register_id   => _register_id,
                                   gen_inclusive => is_walk_in,
                                   rateplan_id   => (checkin_data->>'rateplan_id')::int);

    IF is_walk_in THEN
--        INSERT INTO guest_request (register_id, inclusion_id, multiplier, frequency,
--            charge_dept, charge_rate, qty, remark, charge_to_booking, inclusive)
--        SELECT _register_id, inc.inclusion_id, inc.multiplier, inc.frequency,
--             inc.charge_dept, inc.charge_rate, inc.qty, 'Inclusive', COALESCE(checkin_data->>'charge_to','O') = 'B', TRUE
--        FROM sp_get_rate_inclusions((checkin_data->>'rateplan_id')::int) AS inc;
    ELSIF checkin_data->'requirements' IS NULL THEN
        RAISE NOTICE 'Get requirement from booking';
        INSERT INTO guest_request (register_id, inclusion_id, multiplier, frequency,
            charge_dept, charge_rate, qty, remark, charge_to_booking, inclusive)
        SELECT _register_id, inc.inclusion_id, inc.multiplier, inc.frequency,
             inc.charge_dept, inc.charge_rate, inc.qty, 'Inclusive', inc.charge_to_booking, TRUE
        FROM guest_request AS inc
        WHERE (inc.booking_id = _booking_id)
              AND NOT inc.charge_to_booking
              AND (inc.booking_item = i_booking_item
                    OR (inc.booking_item IS NULL AND (inc.multiplier <> 1 OR book_status IN ('R','C')))
                  );
    END IF;

--    INSERT INTO deposit (register_id, status, deposit_date, amount, payment_id, remark, create_user)
--    SELECT _register_id, 'R', deposit_date, amount, payment_id, remark, create_user
--    FROM jsonb_to_recordset(checkin_data->'deposits')
--    AS (deposit_date date, amount t_money, payment_id int, remark text, create_user text);

    PERFORM sp_update_registration_summary(_register_id);

    IF NOT is_walk_in THEN
        PERFORM sp_log_booking(_booking_id, i_booking_item, 'Check in', 'Check in, room '||_room_number, i_checkin_user);
        PERFORM sp_unlock_booking_record_lock(_booking_id, i_checkin_user);
    END IF;

   -- PERFORM sp_sys_log('sp_check_in', checkin_data::text);
    IF is_interface_active('MSG-CHECKIN') THEN
        PERFORM sp_send_message_to_guest(_guest_id,
            msg_template => 'guest-welcome',
            params => jsonb_build_object(
                'booking_id' , _booking_id,
                'register_id', _register_id,
                'room_number', _room_number,
                'arrival'    , _arrival,
                'departure'  , _departure
            )
        );
    END IF;

    RETURN fn_result_success(json_build_object('register_id', _register_id));
EXCEPTION
    WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
        _state   = RETURNED_SQLSTATE,
        _result_msg= MESSAGE_TEXT,
        _detail  = PG_EXCEPTION_DETAIL,
        _hint    = PG_EXCEPTION_HINT,
        _context = PG_EXCEPTION_CONTEXT;
    RETURN fn_handle_error(_state, _result_msg, _detail, _hint, _context, 'sp_check_in',
        jsonb_build_object(
            'booking_item', i_booking_item,
            'room_id'     , i_room_id,
            'guest_id'    , i_guest_id,
            'walk_in'     , walk_in,
            'checkin_user', i_checkin_user,
            'checkin_data', checkin_data
        )
    );
END