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)
auto_block_room	boolean	IN (DEFAULT fn_sys_param('AUTO_BLOCK_ROOM')
result_code	text	OUT
result_msg	text	OUT
msg_code	text	OUT
out_booking_id	integer	OUT

Definition

DECLARE 
    guests int[] = '{}';
    new_id int;
    temp_items int[] = '{}'; 
    new_items int[] = '{}';
    item record;
    v_state   TEXT;
    v_detail  TEXT;
    v_hint    TEXT;
    v_context TEXT;
    error TEXT;
    v_mealtype_id int;
    v_rate_id int;
    v_channel_origin_id int;
    v_channel_folpat_id int;
    v_channel_sales_id int;
    v_channel_crlimit t_money;
    v_channel_paycond char(3);
    AUTO_CONFIRM boolean = fn_sys_param('AUTO_CONFIRM_BOOKING', FALSE);
   -- AUTO_BLOCK_ROOM bool = fn_sys_param('AUTO_BLOCK_ROOM', FALSE);
begin
    
    --perform sp_log_debug('create_booking', booking_data::text, true);
    CALL sp_check_endday_process();

    result_code = '0'; result_msg = 'Success'; msg_code = '00';


    IF jsonb_array_length(booking_data->'guests') = 0 THEN
        msg_code = '01';    
    ELSIF jsonb_array_length(booking_data->'details') = 0 THEN
        msg_code = '02';
    END IF;
    
    IF msg_code = '00' THEN
    
        -- Save guest info and more guests
        SELECT array_agg(sp_save_guest_js(guest_list, preserve_old_values=>false))
        FROM jsonb_array_elements(booking_data->'guests') as guest_list
        INTO guests;

        select cn.origin_id, cn.folpat_id, cn.sales_id, cn.credit_limit, cn.payment_condition
        from channel cn
        where cn.id = (booking_data->>'channel_id')::int
        into v_channel_origin_id, v_channel_folpat_id, v_channel_sales_id, v_channel_crlimit, v_channel_paycond;    

        INSERT INTO booking (
            status, rsvtype_id, book_type, 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, create_user, folio_pattern_id, promo_code, payment_method, credit_limit, 
            note, channel_booking_id, book_date) 
        -- confirm_no, folio_id, credit_limit, confirm_remark, confirm_user, confirm_time)
        SELECT 'R', 
            COALESCE(rsvtype_id, fn_default('RESERVATION_TYPE')::int), 
            COALESCE(book_type,'I'), 
            COALESCE(stay_type,'D'), 
            book_by, 
            IIF(COALESCE(guest_id,-1)=-1, guests[1], guest_id), 
            group_id, party_id, member_code, channel_id, voucher_no, 
            coalesce(origin_id, v_channel_origin_id, fn_default('ORIGIN')::int), 
            arrival, 
            COALESCE(arrival_time, fn_default('CHECKIN_TIME', '12:00'::time)),              
            arrive_by, arrive_from, 
            departure, 
            COALESCE(departure_time, fn_default('CHECKOUT_TIME', '12:00'::time)), 
            depart_by, depart_to, 
            coalesce(sales_id, v_channel_sales_id),  
            market_id, 
            COALESCE(user_name, fn_current_user(), create_user),        
            COALESCE((payment->>'folio_pattern_id')::int, v_channel_folpat_id, fn_default('FOLIO_PATTERN', null::int)),
            payment->>'promo_code',
            COALESCE(payment->>'payment_method', v_channel_paycond, fn_default('PAYMENT_METHOD')),
            COALESCE((payment->>'credit_limit')::t_money, v_channel_crlimit, fn_default('CREDIT_LIMIT', null::t_money)), 
            note, channel_booking_id, book_date
        FROM jsonb_to_record(booking_data) 
        AS B(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, create_user text, note text, channel_booking_id text, book_date date)
        RETURNING id INTO out_booking_id;
    
        PERFORM sp_log_booking(out_booking_id, NULL, 'New Booking', 'New booking', user_name);
                   
        INSERT INTO booking_guests (booking_id, guest_id)
        SELECT out_booking_id, g_id 
        FROM unnest(guests) g_id
        WHERE g_id <> guests[1] --book_guest_id
        ON CONFLICT DO NOTHING;
        
        FOR item IN (
            SELECT item_id AS temp_id, roomtype_id, bedtype_id, rateplan_id, mealtype_id, rate_id, arrival, 
                departure, qty, adult, child, infant, allot_qty, coalesce(charge_to,'B') as charge_to, 
                extra_adult, extra_child, extra_infant, no_amend, no_cancel, no_refund, requirements, rate_schedule
            FROM jsonb_to_recordset(booking_data->'details') 
            AS (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)
        )
        LOOP 
            v_rate_id := coalesce(item.rate_id, (select rate_id from rate_details d 
                                                 where d.rateplan_id = item.rateplan_id and d.roomtype_id = item.roomtype_id));
            v_mealtype_id := coalesce(item.mealtype_id, (select mealtype_id from rate_plan where id = item.rateplan_id));
            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 (out_booking_id, item.roomtype_id, item.bedtype_id, item.rateplan_id, v_mealtype_id, v_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 new_id;
        
            PERFORM sp_save_guest_requests(item.requirements, out_booking_id, new_id, gen_inclusive => TRUE, rateplan_id => item.rateplan_id);
        
            PERFORM sp_save_rate_schedule(out_booking_id, new_id, v_rate_id, item.arrival, item.departure, item.rate_schedule); 
                     
            temp_items := temp_items || item.temp_id;
            new_items  := new_items  || new_id;
        END LOOP;
    
        INSERT INTO deposit (booking_id, status, deposit_date, amount, payment_id, remark, create_user) 
        SELECT out_booking_id, 'R', deposit_date, amount, payment_id, remark, create_user
        FROM jsonb_to_recordset(booking_data->'deposits') 
        AS (deposit_date date, amount t_money, payment_id int, remark text, create_user text);
    
        PERFORM sp_save_guest_requests(booking_data->'requirements', out_booking_id, gen_inclusive => FALSE);        
        
        IF auto_confirm OR confirm THEN 
            SELECT c.result_code, c.result_msg, c.msg_code 
            FROM sp_confirm_booking(out_booking_id, confirm_remark, user_name) AS c
            INTO result_code, result_msg, msg_code; 
        END IF;
        -- select system_params from property_info
        IF AUTO_BLOCK_ROOM THEN
            PERFORM sp_auto_block_rooms(out_booking_id, block_user := user_name);
        END IF;

        PERFORM sp_update_booking_summary(out_booking_id);
    
--            DELETE FROM drafte 
--            WHERE draft_type = 1 AND user_id = coalesce(user_name, fn_current_user()) AND ref_id IS NULL; 
    END IF;

    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_log_error('Create booking failed('||msg_code||')', 
                '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('Create booking success',
                'booking_id : '||out_booking_id::text
                ||coalesce(E'\n'||'voucher_no : '||(booking_data->>'voucher_no'), '')
                ||coalesce(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_create_booking', booking_data);  
END