aonestar.public

Parameters

Name	Type	Mode
booking_data	jsonb	IN
user_name	text	IN (DEFAULT NULL)
confirm	boolean	IN (DEFAULT false)
confirm_remark	text	IN (DEFAULT NULL)
result_code	text	OUT
result_msg	text	OUT
msg_code	text	OUT

Definition

DECLARE 
    guests int[] = '{}';
    updated_items int[] = '{}';
    item record;
    _booking_id int;
    _mealtype_id int;
    booking_item int;
    book_status char(1);
    _rate_id int;
    error     TEXT;
    v_state   TEXT;
    v_detail  TEXT;
    v_hint    TEXT;
    v_context TEXT;
    old_guest_id int;
    new_guest_id int;
    preserve_guest_data bool;
    rate_changed bool;
begin
    result_code := '0'; result_msg := 'Success'; msg_code := '00';   
    _booking_id := (booking_data->>'booking_id')::int;
    user_name = coalesce(user_name, fn_current_user());

    --perform sp_log_debug('amend_booking', booking_data::text, true);

    CALL sp_check_endday_process();
    CALL sp_check_record_lock(user_name, booking_id := _booking_id);

    SELECT status, guest_id FROM booking b WHERE id = _booking_id
    INTO book_status, old_guest_id;
    new_guest_id := (booking_data->>'guest_id')::int;

    preserve_guest_data := (user_name = 'NBIS' or new_guest_id IS NOT NULL) 
                            AND (new_guest_id IS DISTINCT FROM old_guest_id);

    PERFORM sp_log_booking(_booking_id, NULL, 'Amend Booking', NULL, user_name);

    WITH bk AS (
        SELECT  
            COALESCE(rsvtype_id, fn_default('RESERVATION_TYPE')::int) AS rsvtype_id, 
            COALESCE(book_type,'I') AS book_type, 
            COALESCE(stay_type,'D') AS stay_type, 
            book_by, guest_id, group_id, party_id, member_code, channel_id, voucher_no, origin_id, 
            arrival, arrival_time, arrive_by, arrive_from, departure, departure_time, depart_by, 
            depart_to, sales_id, market_id, payment, note, channel_booking_id, book_date
        FROM jsonb_to_record(booking_data) 
        AS (rsvtype_id int, book_type char(1), stay_type char(1), book_by text, guest_id int, group_id int, party_id int, 
           member_code text, channel_id int, voucher_no text, origin_id int, arrival date, arrival_time time, arrive_by text, 
           arrive_from text, departure date, departure_time time, depart_by text, depart_to text, payment jsonb,
           sales_id int, market_id int, note TEXT, channel_booking_id text, book_date date)
    )
    UPDATE booking b
    SET rsvtype_id = coalesce(bk.rsvtype_id, b.rsvtype_id),
        book_type = coalesce(bk.book_type, b.book_type),
        stay_type = coalesce(bk.stay_type, b.stay_type),
        book_by = bk.book_by,
        guest_id = coalesce(bk.guest_id, b.guest_id),
        --group_id = bk.group_id,
        party_id = coalesce(bk.party_id, b.party_id),
        member_code = bk.member_code,
        channel_id = coalesce(bk.channel_id, b.channel_id),
        voucher_no = bk.voucher_no,
        origin_id = coalesce(bk.origin_id, b.origin_id),
        arrival = bk.arrival,
        --arrival_time = bk.arrival_time,
        arrival_time = COALESCE(bk.arrival_time, b.arrival_time, fn_default('CHECKIN_TIME', '12:00'::time)),  
        arrive_by = bk.arrive_by,
        arrive_from = bk.arrive_from,
        departure = bk.departure,
        --departure_time = bk.departure_time,
        departure_time = COALESCE(bk.departure_time, b.departure_time, fn_default('CHECKOUT_TIME', '12:00'::time)),  
        depart_by = bk.depart_by,
        depart_to = bk.depart_to,
        sales_id = bk.sales_id,
        market_id = bk.market_id,
        note = coalesce(bk.note, b.note),
        folio_pattern_id = coalesce((bk.payment->>'folio_pattern_id')::int, b.folio_pattern_id),
        promo_code = bk.payment->>'promo_code',
        payment_method = bk.payment->>'payment_method',
        credit_limit = coalesce((bk.payment->>'credit_limit')::t_money, b.credit_limit),
        modify_user = user_name,
        modify_time = current_timestamp,  
        channel_booking_id = coalesce(bk.channel_booking_id, b.channel_booking_id), 
        book_date = coalesce(bk.book_date, b.book_date) 
    FROM bk    
    WHERE b.id = _booking_id;

    if coalesce((booking_data->'guests'->1->>'guest_id')::int,-1) < 1  then
        booking_data := jsonb_set(booking_data, '{guests,0,id}'::text[], to_jsonb(old_guest_id));
        raise notice '%', jsonb_pretty(booking_data);
        --guest_list || jsonb_build_object('guest_id', old_guest_id)
    end if;

    -- Save guest info and more guests
    SELECT array_agg(sp_save_guest_js(guest_list, preserve_guest_data))
    FROM jsonb_array_elements(booking_data->'guests') as guest_list
    INTO guests;

    INSERT INTO booking_guests (booking_id, guest_id)
    SELECT _booking_id, g_id 
    FROM unnest(guests) g_id
    WHERE g_id <> new_guest_id --guests[1]
    ON CONFLICT DO NOTHING;
    
    DELETE FROM booking_guests bg  --guests[1]
    WHERE booking_id = _booking_id AND (bg.guest_id = new_guest_id OR bg.guest_id <> ALL(guests));
 
-- Save booking details and rate
    RAISE NOTICE '--- start details loop ---';
    FOR item IN (
        SELECT it.*, 
            (bi.rateplan_id IS DISTINCT FROM it.rateplan_id) AS rateplan_changed,
            (bi.item_id IS NULL or bi.item_id < 1
             OR bi.arrival <> it.arrival 
             OR bi.departure <> it.departure 
             OR bi.roomtype_id <> it.roomtype_id 
             OR bi.rateplan_id <> it.rateplan_id 
             OR bi.rate_id <> it.rate_id
             ) AS rate_schedule_changed
        FROM jsonb_to_recordset(booking_data->'details') 
        AS it(item_id int4,
            roomtype_id int4,
            bedtype_id int4,
            rateplan_id int4,
            mealtype_id int4,
            rate_id int4,
            arrival date,
            departure date,
            qty int2,
            adult int2,
            child int2,
            infant int2,
            allot_qty int2,
            charge_to char(1),
            extra_adult int2,
            extra_child int2,
            extra_infant int2,
            no_amend bool,
            no_cancel bool,
            no_refund bool,
            requirements jsonb,
            rate_schedule jsonb
        ) LEFT JOIN booking_items bi ON bi.item_id = it.item_id
    )
    LOOP 
        
      --  raise notice 'meal_type_id : %' , item.mealtype_id;
        raise notice 'meal_type_id : %', item.mealtype_id ;
        
        
        _rate_id := (SELECT rd.rate_id FROM rate_details rd WHERE rd.rateplan_id = item.rateplan_id AND rd.roomtype_id = item.roomtype_id); 
        _mealtype_id := coalesce(item.mealtype_id, (select mealtype_id from rate_plan where id = item.rateplan_id));
   
        IF (item.item_id IS NULL OR item.item_id < 1) THEN 
            INSERT INTO booking_items(booking_id, roomtype_id, bedtype_id, rateplan_id, mealtype_id, rate_id, arrival, 
                departure, qty, adult, child, infant, allot_qty, charge_to, extra_adult, 
                extra_child, extra_infant, no_amend, no_cancel, no_refund)
            VALUES (_booking_id, item.roomtype_id, item.bedtype_id, item.rateplan_id, _mealtype_id, _rate_id, item.arrival, 
                item.departure, item.qty, item.adult, item.child, item.infant, item.allot_qty, item.charge_to, COALESCE(item.extra_adult,0), 
                COALESCE(item.extra_child,0), COALESCE(item.extra_infant,0), item.no_amend, item.no_cancel, item.no_refund)
            RETURNING item_id INTO booking_item;
        ELSE 
            UPDATE booking_items bi
            SET roomtype_id = item.roomtype_id,
                bedtype_id = item.bedtype_id,
                rateplan_id = item.rateplan_id,
                rate_id = _rate_id,
                arrival = item.arrival,
                departure = item.departure,
                qty = item.qty,
                adult = item.adult,
                child = item.child,
                infant = item.infant,
                allot_qty = item.allot_qty,
                charge_to = item.charge_to,
                extra_adult = COALESCE(item.extra_adult,0),
                extra_child = COALESCE(item.extra_child,0),
                extra_infant = COALESCE(item.extra_infant,0),
                no_amend = item.no_amend,
                no_cancel = item.no_cancel,
                no_refund = item.no_refund,
                mealtype_id = coalesce(item.mealtype_id , _mealtype_id)
            WHERE bi.item_id = item.item_id;
            booking_item = item.item_id; 
        END IF;
    
        -- Sync booking stay period with detail periods
        WITH bk AS (
            SELECT min(arrival) AS arrival, max(departure) AS departure
            FROM booking_items 
            WHERE booking_id = _booking_id
        )
        UPDATE booking b
        SET arrival = bk.arrival, departure = bk.departure
        FROM bk 
        WHERE b.id = _booking_id AND (b.arrival <> bk.arrival or b.departure <> bk.departure);
    
        PERFORM sp_save_rate_schedule(_booking_id, booking_item, _rate_id, item.arrival, item.departure, item.rate_schedule);
--                                       CASE WHEN item.rate_schedule_changed THEN NULL 
--                                            ELSE item.rate_schedule
--                                       END); 
    
        PERFORM sp_save_guest_requests(item.requirements, 
                                       booking_id    => _booking_id, 
                                       booking_item  => booking_item, 
                                       gen_inclusive => item.rateplan_changed, 
                                       rateplan_id   => item.rateplan_id);
           
       
        --temp_items = temp_items || item.temp_id;
        updated_items  = updated_items || booking_item;
    END LOOP;
   
   RAISE NOTICE '--- end details loop ---';
   

    DELETE FROM booking_items bi
    WHERE (booking_id = _booking_id) AND (bi.item_id <> ALL(updated_items));

    UPDATE folio f
    set close_date = (booking_data->>'departure')::date
    WHERE f.booking_id =_booking_id and f.folio_type = 'B' and f.close_date <> (booking_data->>'departure')::date;

    FOR item IN (
        SELECT * FROM jsonb_to_recordset(booking_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 (booking_id, status, deposit_date, amount, payment_id, remark, create_user) 
            VALUES (_booking_id, 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;

    IF (result_code <> '4') THEN
        PERFORM sp_save_guest_requests(booking_data->'requirements', _booking_id, gen_inclusive => FALSE);  

        IF book_status = 'R' AND confirm THEN 
            SELECT c.result_code, c.result_msg, c.msg_code 
            FROM sp_confirm_booking(_booking_id, confirm_remark, user_name) AS c
            INTO result_code, result_msg, msg_code;
        END IF;

        PERFORM sp_update_booking_summary(_booking_id);
    
        --PERFORM sp_log_booking(_booking_id, NULL, 'Amend', 'Amend booking', user_name);
    END IF;
       -- RAISE NOTICE 'Finish msg_code=%', msg_code;
    PERFORM sp_finish_log();

    IF char_length(msg_code) = 2 THEN
        result_code = iif(msg_code = '00', '0', '4');
        msg_code    = '100'||msg_code;
        result_msg  = CASE msg_code 
            WHEN '10000' 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;

    IF result_code = '0' THEN 
       PERFORM sp_unlock_booking_record_lock(_booking_id, user_name);
    ELSE 
       PERFORM sp_log_warning('Amend booking('||result_code||'-'||msg_code||')', 
                          iif(msg_code = '10099', error||E'\n', '')||booking_data::text); 
    END IF;

    
    IF result_code <> '0' THEN 
        PERFORM sp_log_error('Modify booking failed('||msg_code||')', 
                'booking_id : '||_booking_id::text||E'\n'||
                'voucher_no : '||(booking_data->>'voucher_no')||E'\n'||
                'channel_booking_id : '||(booking_data->>'channel_booking_id'),
                error_data := booking_data
        );             
    ELSIF user_name = 'NBIS' THEN 
        PERFORM sp_log_debug('Modify booking success',
                'booking_id : '||_booking_id::text||E'\n'||
                'voucher_no : '||(booking_data->>'voucher_no')||E'\n'||
                'channel_booking_id : '||(booking_data->>'channel_booking_id'),
                --||E'\n'||coalesce('data     : '||E'\n'||jsonb_pretty(booking_data), ''),
                notify_admin := true 
        ); 
    END IF;

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_amend_booking', booking_data);
END