aonestar.public

Parameters

Name	Type	Mode
update_data	jsonb	IN
register_id	integer	IN
dates	date[]	IN (DEFAULT NULL)
room_rate	t_money	IN (DEFAULT NULL)
extra_adult	t_money	IN (DEFAULT NULL)
extra_child	t_money	IN (DEFAULT NULL)
extra_infant	t_money	IN (DEFAULT NULL)
mealtype_id	integer	IN (DEFAULT NULL)
rateplan_id	integer	IN (DEFAULT NULL)
booking_id	integer	IN
booking_item	integer	IN (DEFAULT NULL)
dates	date[]	IN (DEFAULT NULL)
room_rate	t_money	IN (DEFAULT NULL)
extra_adult	t_money	IN (DEFAULT NULL)
extra_child	t_money	IN (DEFAULT NULL)
extra_infant	t_money	IN (DEFAULT NULL)
mealtype_id	integer	IN (DEFAULT NULL)
rateplan_id	integer	IN (DEFAULT NULL)

Definition

DECLARE 
    item record;
    _sqlstate TEXT;
    _detail   TEXT;
    _hint     TEXT;
    _context  TEXT;
    _msg_text TEXT;
BEGIN     
    /*  if "booking_item_id" is null, all booking items in specified booking will be updated 
     *  if "dates" is null, all dates in stay period will be updated 
     *  Column room_rate, extra_adult, extra_child, extra_infant, comp, comp_abf will be skipped if their values are nulls    
     */

IF booking_id IS NOT NULL THEN 
        FOR item IN 
            -- Query old rates by date
            WITH bi AS (
                SELECT bi.item_id, charge_date 
                FROM booking_items AS bi, 
                    LATERAL fn_date_range(arrival, IIF(bi.arrival=bi.departure, bi.departure, bi.departure-1)) AS charge_date --) AS b 
                WHERE (bi.booking_id = $1) AND ($2 IS NULL OR bi.item_id = $2)
            )
            SELECT bi.item_id, bi.charge_date, br.room_rate, br.extrabed_rate, br.child_rate, br.infant_rate, br.mealtype_id, br.rateplan_id  
            FROM bi LEFT JOIN booking_rates br ON br.booking_item = bi.item_id AND bi.charge_date = br.charge_date
            WHERE $3 IS NULL OR bi.charge_date = ANY($3)
        LOOP 
            INSERT INTO booking_rates AS br (booking_id, booking_item, charge_date, start_date, end_date, room_rate, extrabed_rate, child_rate, infant_rate, mealtype_id, rateplan_id) 
            VALUES($1, item.item_id, item.charge_date, item.charge_date, item.charge_date, 
                   COALESCE($4, item.room_rate), 
				   COALESCE($5, item.extrabed_rate), 
				   COALESCE($6, item.child_rate), 
                   COALESCE($7, item.infant_rate), 
				   iif($8 is distinct from -1, $8, item.mealtype_id), --COALESCE($8, item.mealtype_id), 
 				   COALESCE($9, item.rateplan_id))
            ON CONFLICT ON CONSTRAINT pk_booking_rates
            DO UPDATE
               SET --charge_date   = excluded.charge_date,
                   --end_date      = excluded.end_date,
                   room_rate     = excluded.room_rate,
                   extrabed_rate = excluded.extrabed_rate,
                   child_rate    = excluded.child_rate,
                   infant_rate   = excluded.infant_rate,
                   mealtype_id   = excluded.mealtype_id,
                   rateplan_id   = excluded.rateplan_id;
        END LOOP;
    END IF;
    RETURN fn_result_success();
EXCEPTION  
    WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
        _sqlstate = RETURNED_SQLSTATE,
        _msg_text = MESSAGE_TEXT,
        _detail   = PG_EXCEPTION_DETAIL,
        _hint     = PG_EXCEPTION_HINT,
        _context  = PG_EXCEPTION_CONTEXT;    
    RETURN fn_handle_error(_sqlstate, _msg_text, _detail, _hint, _context, 'sp_update_room_charge_schedule', 
                jsonb_build_object(
                    'booking_id', booking_id,
                    'booking_item', booking_item,
                    'dates', dates,
                    'room_rate', room_rate,
                    'extra_adult', extra_adult,
                    'extra_child', extra_child,
                    'extra_infant', extra_infant,
                    'comp', comp,
                    'comp_abf', comp_abf)
           );
END