aonestar.public

Parameters

Name	Type	Mode
inventory_data	jsonb	IN
currency	character varying	IN (DEFAULT NULL)

Definition

DECLARE
    inv record;
    st record;
    plan record;
    rate record;
    log_line text = '';
    log_text text = '';
    stop_sell_on text = '';
    stop_sell_off text = '';
    min_rate t_money;
    _sqlstate text;
    _detail text;
    _hint text;
    _context text;
    _msg_text text;
    _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', 'D', true);
    PERFORM set_config('rate_log.log_group', _rate_log_group::text, true);

FOR inv IN
        SELECT R.*, rt.name AS roomtype_name, rt.min_rate AS room_min_rate, rt.code
        FROM jsonb_to_recordset(inventory_data->'inventory')
             AS R(roomtype_id int, available jsonb, sell_status jsonb, rate_plans jsonb)
             LEFT JOIN room_type rt ON rt.id = r.roomtype_id
        ORDER BY rt.order_seq
    LOOP
        log_text := log_text || inv.roomtype_name || E' :\n';
        log_line := '';

FOR st IN
            SELECT *
            FROM jsonb_to_recordset(inv.sell_status)
            AS ("date" date, stop_sell bool, min_avail int2, max_avail int2)
            WHERE stop_sell IS NOT NULL
            ORDER BY "date"
        LOOP
            INSERT INTO room_restriction(roomtype_id, effect_date, stop_sell, min_avail, max_avail)
            VALUES (inv.roomtype_id, st."date", st.stop_sell, st.min_avail, st.max_avail)
            ON CONFLICT(roomtype_id, effect_date)
            DO UPDATE
            SET stop_sell = excluded.stop_sell,
                min_avail = excluded.min_avail,
                max_avail = excluded.max_avail;

IF st.stop_sell THEN
                 stop_sell_on := stop_sell_on || ', ' || st."date";
            ELSE
                 stop_sell_off := stop_sell_off || ', ' || st."date";
            END IF;
        END LOOP;

IF stop_sell_on <> '' THEN
             log_text := log_text || '  * Close Sell [ON]  : ' || substring(stop_sell_on FROM 3) || E'\n';
        END IF;

IF stop_sell_off <> '' THEN
             log_text := log_text || '  * Close Sell [OFF] : ' || substring(stop_sell_off FROM 3) || E'\n';
        END IF;

FOR plan IN
           SELECT P.*, rp.name AS rateplan_name, rp.min_rate AS rate_min_rate, rp.code
           FROM jsonb_to_recordset(inv.rate_plans) AS P(rate_id int, rateplan_id int, editable bool, rates jsonb)
                LEFT JOIN rate_plan rp ON rp.id = p.rateplan_id
           ORDER BY rp.order_seq, rp.name
        LOOP
            log_text := log_text || '  ' || plan.rateplan_name || E'\n';

FOR rate IN
                SELECT r."date", $2, r.amount,
                    (restrictions->>'stop_sell')::bool AS stop_sell,
                    (restrictions->>'cta')::bool AS cta,
                    (restrictions->>'ctd')::bool AS ctd,
                    (restrictions->>'min_stay')::int AS min_stay,
                    (restrictions->>'max_stay')::int AS max_stay
                FROM jsonb_to_recordset(plan.rates) AS R("date" date, amount t_money, restrictions jsonb)
                WHERE NOT (r.amount IS NULL
                            AND restrictions->>'stop_sell' IS NULL
                            AND restrictions->>'cta' IS NULL
                            AND restrictions->>'ctd' IS NULL
                            AND restrictions->>'min_stay' IS NULL
                            AND restrictions->>'max_stay' IS NULL)
                ORDER BY 1
            LOOP
                min_rate := coalesce(plan.rate_min_rate, inv.room_min_rate);
                IF rate.amount < min_rate THEN
                    PERFORM sp_raise_error('10801',
                        'Room rate lower than the minimum rate are not allowed (%s - %s, Amount = %s, Minimum = %s)',
                         inv.code, plan.code, rate.amount::text, min_rate::text
                    );
                END IF;

INSERT INTO rate_daily AS rd (rate_id, rate_date, currency_code, rate_amount, stop_sell, cta, ctd, min_stay, max_stay)
                VALUES (plan.rate_id, rate."date", $2, rate.amount, rate.stop_sell, rate.cta, rate.ctd, rate.min_stay, rate.max_stay)
                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);

log_text := log_text || format('    * %s%s%s%s%s%s%s',
                                 rate."date",
                                 ', Rate = ' || rate.amount,
                                 ', Close Sell = ' || rate.stop_sell,
                                 ', CTA = ' || rate.cta,
                                 ', CTD = ' || rate.ctd,
                                 ', Min Stay = ' || rate.min_stay,
                                 ', Max Stay = ' || rate.max_stay) || E'\n';
            END LOOP;
        END LOOP;

log_text := log_text || E'\n';
    END LOOP;

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);

PERFORM sp_user_log('inventory', 'Inventory Update', trim(log_text));
    RETURN 0;

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
        _msg_text = MESSAGE_TEXT,
        _sqlstate = RETURNED_SQLSTATE,
        _detail = PG_EXCEPTION_DETAIL,
        _hint = PG_EXCEPTION_HINT,
        _context = PG_EXCEPTION_CONTEXT;
    PERFORM fn_handle_error(_sqlstate, _msg_text, _detail, _hint, _context, 'sp_save_inventory', inventory_data);
    RETURN 4;
END