aonestar.public

Parameters

Name	Type	Mode
register_id	integer	IN
user_name	text	IN (DEFAULT NULL)
checkin_data	jsonb	IN (DEFAULT NULL)
result_code	text	OUT
result_msg	text	OUT
msg_code	text	OUT

Definition

DECLARE 
    guests int[] = '{}';
    item record;
    _guest_id int;
    --_rate_id int;
    old_departure date;
    new_departure date;
    error     TEXT;
    v_state   TEXT;
    v_detail  TEXT;
    v_hint    TEXT;
    v_context TEXT;
    rec record;
    old_rateplan int;
    new_rateplan int;
    old_rate_id int;
    new_rate_id int;
    old_roomtype int;
    new_roomtype int;
    old_room_id int;
    new_room_id int;
    ooo_status TEXT;
    rate_changed bool = FALSE;
    log_id bigint;
    old_total_amount t_money;    
BEGIN
    result_code = '0'; result_msg = 'Success'; msg_code = '00';
  
    -- Validate checkin
        -- check booking status
        -- check stay period
        -- check number of rooms remains in booking
        -- check room is ready to check in

    IF (checkin_data->>'departure')::date < fn_system_date() THEN
        PERFORM sp_raise_error('20101' , 'Invalid departure date');
    END IF;

    CALL sp_check_endday_process();
    CALL sp_check_record_lock(user_name, register_id := $1);

    log_id := sp_log_inhouse($1, 'Update registration', null, user_name);

    -- Start check-in process
    guests = sp_save_guests_js(checkin_data->'guests', preserve_old_values=>false);

    user_name := coalesce(user_name, fn_current_user(), checkin_data->>'modify_user');
    _guest_id := (checkin_data->>'guest_id')::int;  
    new_rateplan := (checkin_data->>'rateplan_id')::int;  
    new_rate_id  := (checkin_data->>'rate_id')::int; 
    new_departure:= (checkin_data->>'departure')::date; 
    new_room_id  := (checkin_data->>'room_id')::int; 
    new_roomtype := (SELECT roomtype_id FROM room WHERE id = (checkin_data->>'room_id')::int); 
    --is_room_changed := (_block_room_id is not null) and (_block_room_id <> i_room_id);

    SELECT rg.rateplan_id, rg.rate_id, rg.departure, rm.roomtype_id, rg.room_id
    FROM registration rg LEFT JOIN room rm ON rm.id = rg.room_id
    WHERE rg.id = $1
    INTO old_rateplan, old_rate_id, old_departure, old_roomtype, old_room_id;

    IF (old_room_id <> new_room_id) OR (old_departure <> new_departure) THEN
        CALL sp_check_room_activity(new_room_id, 
            (checkin_data->>'arrival')::date, 
            new_departure, 
            register_id := $1, 
            check_room_status := (old_room_id <> new_room_id)
        );
    END IF;
    
    INSERT INTO register_guests (register_id, guest_id)
    SELECT $1, guest_id 
    FROM unnest(guests) guest_id
    WHERE guest_id <> _guest_id
    ON CONFLICT DO NOTHING;

    DELETE FROM register_guests rg
    WHERE (rg.register_id = $1) AND (rg.guest_id = _guest_id OR rg.guest_id <> ALL(guests));

    --PERFORM sp_log_debug('sp_update_guest_inhouse', format('room_type: %L > %L, rate_plan: %L > %L, rate_id: %L > %L', 
    --                                                        old_roomtype, new_roomtype, old_rateplan, new_rateplan, old_rate_id,  new_rate_id));
    rate_changed := (old_departure <> new_departure OR old_roomtype <> new_roomtype OR old_rateplan <> new_rateplan OR old_rate_id IS DISTINCT FROM new_rate_id);
    IF rate_changed THEN  
        SELECT rd.rate_id
        FROM rate_details rd
        WHERE (rd.rateplan_id = new_rateplan) AND (rd.roomtype_id = new_roomtype)
        INTO new_rate_id;  
    END IF;
  
   -- new_rate_id  := coalesce(new_rate_id, _rate_id);

    UPDATE registration reg
    SET room_id = r.room_id, 
        guest_id = COALESCE(r.guest_id, guests[1]), 
        departure = r.departure, 
        departure_time = r.departure_time, 
        adult = r.adult, 
        child = r.child, 
        infant = r.infant, 
        extra_adult = (checkin_data->'summary'->>'extra_adult')::int2, --r.extra_adult, 
        extra_child = (checkin_data->'summary'->>'extra_child')::int2, --r.extra_child, 
        extra_infant = (checkin_data->'summary'->>'extra_infant')::int2, --r.extra_infant, 
        member_code = r.member_code, 
        channel_id = r.channel_id, 
        origin_id = r.origin_id, 
        arrive_by = r.arrive_by, 
        arrive_from = r.arrive_from, 
        depart_by = r.depart_by, 
        depart_to = r.depart_to, 
        voucher_no = r.voucher_no,  
        sales_id = r.sales_id, 
        market_id = r.market_id, 
        rsvtype_id = r.rsvtype_id, 
        stay_type = r.stay_type, 
        note = r.note, 
        rateplan_id = r.rateplan_id, 
        charge_to_booking = r.charge_to_booking, 
        promotion_id = r.promotion_id, 
        rate_id = new_rate_id,
        folio_pattern_id = COALESCE(payment->>'folio_pattern_id', fn_default('FOLIO_PATTERN'))::int,
        payment_method = COALESCE(payment->>'payment_method', fn_default('PAYMENT_METHOD')),
        credit_limit = COALESCE(payment->>'credit_limit', fn_default('CREDIT_LIMIT'))::t_money,
        promo_code = payment->>'promo_code',
        modify_user = $2, 
        modify_time = current_timestamp,
        --mealtype_id = COALESCE(r.mealtype_id, (select mealtype_id from rate_plan where id = new_rateplan)) 
        mealtype_id = (select mealtype_id from rate_plan rp where rp.id = r.rateplan_id)
    FROM jsonb_to_record(checkin_data) 
    AS R(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, charge_to_booking bool, promotion_id int,
         stay_type char(1), note text, rateplan_id int, mealtype_id int, rate_id int, payment jsonb, rsvtype_id int)
    WHERE reg.id = $1; 

    old_total_amount := (select sum(room_rate) from registration_rates r where r.register_id = $1);

    PERFORM sp_save_registration_rates($1, new_rate_id, (checkin_data->>'arrival')::date, new_departure, checkin_data->'rate_schedule');
                                      
    PERFORM sp_save_guest_requests(request_data  => checkin_data->'requirements', 
                                   register_id   => $1, 
                                   gen_inclusive => (old_rateplan <> new_rateplan), 
                                   rateplan_id   => new_rateplan);

--        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);
    
    FOR item IN (
        SELECT * FROM jsonb_to_recordset(checkin_data->'deposits')
        AS (id int, status char(1), deposit_date date, amount t_money, payment_id int, remark text, create_user text))
    LOOP
        IF (item.id IS NULL) OR (item.id < 1) THEN
            INSERT INTO deposit (register_id, status, deposit_date, amount, payment_id, remark, create_user) 
            VALUES ($1, item.status, item.deposit_date, item.amount, item.payment_id, item.remark, user_name);
        ELSE
            UPDATE deposit d
            SET status = item.status,
                deposit_date = item.deposit_date,
                amount = item.amount,
                payment_id = item.payment_id,
                remark = item.remark,   
                cancel_user = iif(status <> item.status AND item.status = 'X', user_name, null),
                cancel_time = iif(status <> item.status AND item.status = 'X', current_timestamp, null),
                modify_user = iif(deposit_date <> item.deposit_date 
                                  OR amount <> item.amount 
                                  OR payment_id <> item.payment_id 
                                  OR remark IS DISTINCT FROM item.remark, 
                                  user_name, modify_user),  
                modify_time = iif(deposit_date <> item.deposit_date 
                                  OR amount <> item.amount 
                                  OR payment_id <> item.payment_id 
                                  OR remark IS DISTINCT FROM item.remark, 
                                  current_timestamp, modify_time)               
            WHERE d.id = item.id;
        END IF;
    END LOOP;
   
    -- Departure changed
    IF old_departure < (checkin_data->>'departure')::date THEN 
        PERFORM sp_log_inhouse($1, 'Extend stay', format('%L -> %L', old_departure, new_departure), user_name);
    ELSEIF old_departure > (checkin_data->>'departure')::date THEN 
        PERFORM sp_log_inhouse($1, 'Change departure', format('%L -> %L', old_departure, new_departure), user_name);
    END IF;

    -- Rate changed
    IF old_rateplan <> new_rateplan THEN        
        PERFORM sp_log_inhouse($1, 'Change rate', 
                               format('%L -> %L',
                                      (SELECT name FROM rate_plan WHERE id = old_rateplan), 
                                      (SELECT name FROM rate_plan WHERE id = new_rateplan)), 
                               user_name, old_rateplan, new_rateplan);
    END IF;
    
    -- Room changed
    IF old_room_id <> new_room_id THEN 
        IF NOT is_room_occupied(old_room_id, $1) THEN 
            ooo_status := (SELECT ooo_type FROM out_of_order oo WHERE oo.room_id = old_room_id AND oo.start_date = fn_system_date() LIMIT 1);
            UPDATE room_status 
            SET status = COALESCE(ooo_status, 'VAC'), 
                hk_status = fn_sys_param('ROOM_STATUS', 'CHECKOUT_STATUS', 'DI')  
            WHERE room_id = old_room_id;
        
        END IF;   
            
        UPDATE room_status SET status = 'OCC' WHERE room_id = new_room_id;
    
        PERFORM sp_log_inhouse($1, 'Room change', 
                       format('Room change from %s to %s, Reason: %s', 
                              fn_room_number(old_room_id), 
                              fn_room_number(new_room_id), 
                              checkin_data->>'room_change_reason'),
                       user_name, old_room_id, new_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 ($1, 
                checkin_data->>'upgrade_type',
                old_total_amount,
                (select sum(room_rate) from registration_rates r where r.register_id = $1)
               );

        case checkin_data->>'upgrade_type' 
            when 'upgrade' then PERFORM sp_log_inhouse($1, 'Upgrade', 'Free upgrade', user_name);
            when 'upsell' then PERFORM sp_log_inhouse($1, 'Upsell', 'Upselling', user_name);
            else
        end case;
    end if;

    PERFORM sp_update_registration_summary($1);

    -- Delete log if empty    
    DELETE FROM registration_log lg
    WHERE lg.id = log_id AND lg.log_text IS NULL;

    IF char_length(msg_code) = 2 THEN
        result_code = iif(msg_code = '00', '0', '4');
        msg_code    = '201'||msg_code;
        result_msg  = CASE msg_code 
            WHEN '20100' THEN 'Success'
           -- WHEN '10001' THEN sys_msg(msg_code, 'Cannot create booking, no guest information.')
           -- WHEN '10002' THEN sys_msg(msg_code, 'Cannot create booking, no room details.')
            ELSE result_msg
        END; 
    END IF;

    PERFORM sp_unlock_register_record_lock($1, user_name);

    PERFORM sp_log_debug('sp_update_guest_inhouse', checkin_data::text);     

EXCEPTION 
    WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
        v_state   = RETURNED_SQLSTATE,
        result_msg= MESSAGE_TEXT,
        v_detail  = PG_EXCEPTION_DETAIL,
        v_hint    = PG_EXCEPTION_HINT,
        v_context = PG_EXCEPTION_CONTEXT;
    CALL sp_handle_error(result_code, result_msg, msg_code, v_state, result_msg, v_detail, v_hint, v_context, 'sp_update_guest_inhouse', checkin_data);  
END