aonestar.public

Parameters

Name	Type	Mode
booking_id	integer	IN

Definition

declare
    v_booking_data jsonb;
    v_details jsonb;
    v_template_params jsonb;
BEGIN
    v_booking_data := sp_get_booking_js($1, include_logs := false)::jsonb;

with details as (
        select b.roomtype_name as room_type, b.mealtype_name as meal_type,
            b.arrival, b.departure, b.qty, b.adult, b.child, b.infant,
            coalesce(fn_qty(b.adult, 'adult'), '')
            ||coalesce(', '||fn_qty(nullif(b.child, 0), 'child','children'),'')
            ||coalesce(', '||fn_qty(nullif(b.infant, 0), 'infant'),'') as pax,
            to_char(bi.total_charge, 'FM99,990.00') as total_charge
        from jsonb_to_recordset(v_booking_data->'details')
             as B(
                item_id int,
                arrival date,
                departure date,
                qty int,
                roomtype_name text,
                mealtype_name text,
                adult int,
                child int,
                infant int
             ) LEFT JOIN booking_items bi on bi.item_id = b.item_id
    )
    SELECT jsonb_agg(details) from details
    INTO v_details;

v_template_params := jsonb_build_object(
        'booking_id' , (v_booking_data->>'booking_id')::int,
        'voucher_no' , v_booking_data->>'voucher_no',
        'arrival'    , to_char((v_booking_data->>'arrival')::date, 'DD Mon YYYY'),
        'departure'  , to_char((v_booking_data->>'departure')::date, 'DD Mon YYYY'),
        'grand_total', to_char((v_booking_data->'summary'->>'total_charge')::t_money, 'FM99,990.00'),
        'deposit'    , to_char((v_booking_data->'summary'->>'deposit')::t_money, 'FM99,990.00'),
        'balance'    , to_char((v_booking_data->'summary'->>'balance')::t_money, 'FM99,990.00'),
        'cancel_policy' , 'No cancellation',
        'details'    , v_details
    );

RETURN sp_send_message_to_guest(
        (v_booking_data->>'guest_id')::int,
        msg_template   => 'booking-confirm',
        params         => v_template_params,
        skip_test_mode => true
    ) is not null;
END

sp_send_confirm_email

Parameters

Definition