aonestar.public

Parameters

Name	Type	Mode
booking_id	integer	IN
booking_item	integer	IN
rate_id	integer	IN
arrival	date	IN
departure	date	IN
rate_schedule	jsonb	IN
result_code	text	OUT
result_msg	text	OUT
msg_code	text	OUT

Definition

DECLARE
    rs record;
    rate_name TEXT;
BEGIN
    --PERFORM sp_log_debug('sp_save_rate_schedule',
    --      format(E'booking_id: %L, booking_item: %L, rate_id: %L, arrival: %L, departure: %L, \nschedule data:\n%L',
    --              booking_id, booking_item, rate_id, arrival, departure, rate_schedule));

   -- DELETE FROM booking_rates br WHERE br.booking_item = $2;

    IF (rate_schedule IS NULL) OR (jsonb_array_length(rate_schedule) = 0) THEN
        FOR rs IN
            SELECT r.rate_date, r.start_date, r.end_date, r.rate_amount, r.extra_adult, r.extra_child,
                r.extra_infant, r.room_charge_dept, r.comp, r.comp_abf, r.comp_lunch, r.comp_dinner
            FROM sp_get_rate_schedule($3, $4, $5, false) as r
        LOOP
            IF rs.rate_amount IS NOT NULL THEN
                INSERT INTO booking_rates AS br (booking_id, booking_item, charge_date, start_date, end_date, room_rate,
                    extrabed_rate, child_rate, infant_rate, room_charge_dept, comp, comp_abf, comp_lunch, comp_dinner)
                VALUES($1, $2, rs.rate_date, rs.start_date, rs.end_date, rs.rate_amount, rs.extra_adult, rs.extra_child,
                    rs.extra_infant, rs.room_charge_dept, rs.comp, rs.comp_abf, rs.comp_lunch, rs.comp_dinner)
                ON CONFLICT ON CONSTRAINT pk_booking_rates--(br.booking_item, br.charge_date)
                DO UPDATE
                   SET room_rate        = COALESCE(excluded.room_rate, br.room_rate),
                       extrabed_rate    = COALESCE(excluded.extrabed_rate, br.extrabed_rate),
                       child_rate       = COALESCE(excluded.child_rate, br.child_rate),
                       infant_rate      = COALESCE(excluded.infant_rate, br.infant_rate),
                       room_charge_dept = COALESCE(excluded.room_charge_dept, br.room_charge_dept),
                       comp             = COALESCE(excluded.comp, br.comp),
                       comp_abf         = COALESCE(excluded.comp_abf, br.comp_abf),
                       comp_lunch       = COALESCE(excluded.comp_lunch, br.comp_lunch),
                       comp_dinner      = COALESCE(excluded.comp_dinner, br.comp_dinner);
            ELSE
                rate_name := (SELECT rp.name FROM booking_items bi INNER JOIN rate_plan rp ON rp.id = bi.rateplan_id WHERE bi.item_id = $2);
                PERFORM sp_raise_error('10106', 'Rate "%s" is not available during this period', rate_name);
            END IF;
        END 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, room_charge_dept, comp, comp_abf, comp_lunch, comp_dinner)
--        SELECT $1, $2, rs.rate_date, rs.start_date, rs.end_date, rs.rate_amount, rs.extra_adult, rs.extra_child,
--            rs.extra_infant, rs.room_charge_dept, rs.comp, rs.comp_abf, rs.comp_lunch, rs.comp_dinner
--        FROM sp_get_rate_schedule($3, $4, $5, false) as rs
--        ON CONFLICT ON CONSTRAINT pk_booking_rates--(br.booking_item, br.charge_date)
--        DO UPDATE
--           SET room_rate        = COALESCE(excluded.room_rate, br.room_rate),
--               extrabed_rate    = COALESCE(excluded.extrabed_rate, br.extrabed_rate),
--               child_rate       = COALESCE(excluded.child_rate, br.child_rate),
--               infant_rate      = COALESCE(excluded.infant_rate, br.infant_rate),
--               room_charge_dept = COALESCE(excluded.room_charge_dept, br.room_charge_dept),
--               comp             = COALESCE(excluded.comp, br.comp),
--               comp_abf         = COALESCE(excluded.comp_abf, br.comp_abf),
--               comp_lunch       = COALESCE(excluded.comp_lunch, br.comp_lunch),
--               comp_dinner      = COALESCE(excluded.comp_dinner, br.comp_dinner);
    ELSE
        --PERFORM sp_log_debug('sp_save_rate_schedule', '2 [use schedule data]');
        WITH rates AS (
            SELECT *
            FROM jsonb_to_recordset(rate_schedule)
            AS S("date" date, amount t_money, extra_adult_rate t_money, extra_child_rate t_money,
                 extra_infant_rate t_money, room_charge_dept int, comp bool, comp_abf bool, comp_lunch bool,
                 comp_dinner bool, rateplan_id int, mealtype_id int)
        )
        INSERT INTO booking_rates AS br
            (booking_id, booking_item, charge_date, start_date, end_date, room_rate, extrabed_rate, child_rate,
             infant_rate, room_charge_dept, comp, comp_abf, comp_lunch, comp_dinner, rateplan_id, mealtype_id)
        SELECT $1, $2, s.rate_date, s.start_date, s.end_date,
            COALESCE(r.amount, s.rate_amount),
            COALESCE(r.extra_adult_rate, s.extra_adult),
            COALESCE(r.extra_child_rate, s.extra_child),
            COALESCE(r.extra_infant_rate, s.extra_infant),
            COALESCE(r.room_charge_dept, s.room_charge_dept),
            COALESCE(r.comp, s.comp),
            COALESCE(r.comp_abf, s.comp_abf),
            COALESCE(r.comp_lunch, s.comp_lunch),
            COALESCE(r.comp_dinner, s.comp_dinner),
            r.rateplan_id, r.mealtype_id
        FROM sp_get_rate_schedule($3, $4, $5, false) as s
            LEFT JOIN rates AS r ON s.rate_date = r."date"
        ON CONFLICT ON CONSTRAINT pk_booking_rates--(br.booking_item, br.charge_date)
        DO UPDATE
           SET room_rate        = excluded.room_rate,
               extrabed_rate    = excluded.extrabed_rate,
               child_rate       = excluded.child_rate,
               infant_rate      = excluded.infant_rate,
               room_charge_dept = excluded.room_charge_dept,
               comp             = excluded.comp,
               comp_abf         = excluded.comp_abf,
               comp_lunch       = excluded.comp_lunch,
               comp_dinner      = excluded.comp_dinner,
               rateplan_id      = excluded.rateplan_id,
               mealtype_id      = excluded.mealtype_id;
    END IF;

    DELETE FROM booking_rates br
    WHERE (br.booking_item = $2) AND (br.charge_date NOT BETWEEN arrival AND departure-1);
END