aonestar.public

Parameters

Name	Type	Mode
update_data	jsonb	IN

Definition

DECLARE
    _start_date date;
    _end_date date;
    _days json;
    _adate date;
    _rate t_money;
    _stop_sell bool;
    _cta bool;
    _ctd bool;
    _min_stay smallint;
    _max_stay smallint;
    _rate_ids int[];
    _row_count int;
    _update_count int = 0;
    _currency text = NULL;
    _sqlstate text;
    _detail text;
    _hint text;
    _context text;
    _msg_text text;
    log_text text = '';
    log_line text;
    item record;
    _prev_rate_log_action text;
    _prev_rate_log_group text;
    _rate_log_group uuid := uuidv7();
BEGIN
    _prev_rate_log_action := current_setting('rate_log.action_type', true);
    _prev_rate_log_group := current_setting('rate_log.log_group', true);
    PERFORM set_config('rate_log.action_type', 'B', true);
    PERFORM set_config('rate_log.log_group', _rate_log_group::text, true);

_days := (update_data->'dates'->'days')::json;
    _start_date := (update_data->'dates'->>'start')::date;
    _end_date := (update_data->'dates'->>'end')::date;
    _rate := (update_data->'values'->>'room_rate')::t_money;
    _stop_sell := (update_data->'values'->>'stop_sell')::bool;
    _cta := (update_data->'values'->>'cta')::bool;
    _ctd := (update_data->'values'->>'ctd')::bool;
    _min_stay := (update_data->'values'->>'min_stay')::smallint;
    _max_stay := (update_data->'values'->>'max_stay')::smallint;
    _rate_ids := ARRAY(SELECT rate_id FROM jsonb_array_elements_text(update_data->'rates') AS R(rate_id));
    _currency := update_data->>'currency';

RAISE NOTICE 'dates: % - %, days: %, rate_ids: %', _start_date, _end_date, _days, _rate_ids;
    RAISE NOTICE 'room_rate: %, stop_sell: %, cta: %, ctd: %, min_stay: %, max_stay: %', _rate, _stop_sell, _cta, _ctd, _min_stay, _max_stay;

FOR item IN (
        SELECT rt.code AS room_code, rp.code AS rate_code, coalesce(rp.min_rate, rt.min_rate) AS min_rate
        FROM rate_details rd
            LEFT JOIN rate_plan rp ON rp.id = rd.rateplan_id
            LEFT JOIN room_type rt ON rt.id = rd.roomtype_id
        WHERE rd.rate_id = ANY(_rate_ids)
    ) LOOP
        IF _rate < item.min_rate THEN
            PERFORM sp_raise_error('10801',
                'Room rate lower than the minimum rate are not allowed (%s - %s, Amount = %s, Minimum = %s)',
                item.room_code, item.rate_code, _rate::text, item.min_rate::text
            );
        END IF;
    END LOOP;

FOR _adate IN
        SELECT adate
        FROM fn_date_range(_start_date, _end_date) AS adate
        WHERE fn_match_dows(adate, _days)
    LOOP
        INSERT INTO rate_daily AS rd (rate_id, rate_date, currency_code, rate_amount, stop_sell, cta, ctd, min_stay, max_stay)
        SELECT rd.rate_id, _adate, _currency, IIF(rp.plan_type = 1, _rate, NULL), _stop_sell, _cta, _ctd, _min_stay, _max_stay
        FROM rate_details rd LEFT JOIN rate_plan rp ON rp.id = rd.rateplan_id
        WHERE rd.rate_id = ANY(_rate_ids)
        ON CONFLICT(rate_id, rate_date)
        DO UPDATE
           SET rate_amount = COALESCE(excluded.rate_amount, rd.rate_amount),
               stop_sell = COALESCE(excluded.stop_sell, rd.stop_sell),
               cta = COALESCE(excluded.cta, rd.cta),
               ctd = COALESCE(excluded.ctd, rd.ctd),
               min_stay = NULLIF(COALESCE(excluded.min_stay, rd.min_stay), 0),
               max_stay = NULLIF(COALESCE(excluded.max_stay, rd.max_stay), 0);

GET DIAGNOSTICS _row_count = ROW_COUNT;
        _update_count := _update_count + _row_count;
    END LOOP;

IF _update_count > 0 THEN
        log_text := format('%s%s%s%s%s%s',
                             ', Rate = ' || _rate,
                             ', Close Sell = ' || _stop_sell,
                             ', CTA = ' || _cta,
                             ', CTD = ' || _ctd,
                             ', Min Stay = ' || _min_stay,
                             ', Max Stay = ' || _max_stay);
        log_text := 'Update: ' || substring(log_text FROM 3) || E'\n';
        log_text := log_text || format('Dates : %s to %s', _start_date, _end_date) || E'\n';

SELECT 'Days  : ' || string_agg(dow, ', ') || E'\n'
        FROM json_each_text(_days) AS A(dow, active)
        WHERE active::bool = TRUE
        HAVING count(dow) <> 7
        INTO log_line;

log_text := log_text || coalesce(log_line, '');

WITH rates AS (
            SELECT rp.order_seq, rp.name || E' [' || string_agg(rt.code, ', ' ORDER BY rt.order_seq, rt.code) || ']' AS aline
            FROM rate_details rd
                LEFT JOIN rate_plan rp ON rp.id = rd.rateplan_id
                LEFT JOIN room_type rt ON rt.id = rd.roomtype_id
            WHERE rd.rate_id = ANY(_rate_ids)
            GROUP BY rp.order_seq, rp.name
        )
        SELECT string_agg('  * ' || aline, E'\n' ORDER BY order_seq) FROM rates
        INTO log_line;

log_text := log_text || E'Rates : \n' || coalesce(log_line, '');

PERFORM sp_user_log('inventory', 'Bulk Update', trim(log_text));
    END IF;

PERFORM set_config('rate_log.action_type', COALESCE(_prev_rate_log_action, ''), true);
    PERFORM set_config('rate_log.log_group', COALESCE(_prev_rate_log_group, ''), true);

RETURN fn_result_success('99900', '%s records updated', _update_count::text);
EXCEPTION
    WHEN OTHERS THEN
    PERFORM set_config('rate_log.action_type', COALESCE(_prev_rate_log_action, ''), true);
    PERFORM set_config('rate_log.log_group', COALESCE(_prev_rate_log_group, ''), true);

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_bulk_update_inventory', update_data);
END