aonestar.public

Parameters

Name	Type	Mode
update_data	jsonb	IN

Definition

DECLARE 
    _days jsonb; 
    _adate date;  
    _rate t_money;
    _stop_sell bool;
    _cta bool;
    _ctd bool;
    _min_stay SMALLINT;
    _max_stay SMALLINT;
    _roomtype_id int;
    _room_stop_sell bool;
    _rate_ids int[];
    _update_count int = 0;
    _row_count int;
    currency TEXT = NULL;
    _sqlstate TEXT;
    _detail   TEXT;
    _hint     TEXT;
    _context  TEXT;
    _msg_text TEXT;
BEGIN
    /* update_data:
    {
        "update_values": {
            "rate": 1250.50,    // optional
            "stop_sell": true,  // optional
            "cta": true,        // optional
            "ctd": true,        // optional
            "min_stay": 2,      // optional
            "max_stay": 4       // optional
        },
        "date_ranges": [
            {
                "start": "2021-01-01", 
                "end": "2021-03-31"
            }, 
            ...
        ],
        "days": {"sun": true, "mon": true, "tue": true, "wed": true, "thu": true, "fri": true, "sat": true},
        "targets": [
            {
                "roomtype_id": 4,
                "rates": [rate_id, rate_id, ...]
            },
            ...       
        ]
    }   */
    _days := update_data->'days';
    _rate := (update_data->'update_values'->>'rate')::t_money; 
    _stop_sell := (update_data->'update_values'->>'stop_sell')::bool; 
    _cta := (update_data->'update_values'->>'cta')::bool; 
    _ctd := (update_data->'update_values'->>'ctd')::bool; 
    _min_stay := (update_data->'update_values'->>'min_stay')::smallint; 
    _max_stay := (update_data->'update_values'->>'max_stay')::smallint; 
    _room_stop_sell := _stop_sell;
 
    FOR _adate IN 
        WITH dr AS (
            SELECT fn_date_range(d."start", d."end") AS adate
            FROM jsonb_to_recordset(update_data->'date_ranges') AS D("start" date, "end" date) 
        ), dates AS (SELECT adate, to_char(adate,'dy') AS dow FROM dr)
        SELECT adate FROM dates
        WHERE _days IS NULL or (_days->>dow)::bool -- Check day of weeks    
    LOOP 
        RAISE NOTICE '%', _adate; 
        FOR _roomtype_id, _rate_ids IN 
            SELECT * FROM jsonb_to_recordset(update_data->'targets') AS R(roomtype_id int, rates int[])    
        LOOP 
            RAISE NOTICE 'target: %, %', _roomtype_id, _rate_ids; 
        
            -- Update stop_sell in room restriction when rate ids not specified
            IF (_rate_ids IS NULL AND _room_stop_sell IS NOT NULL) THEN 
                INSERT INTO room_restriction(roomtype_id, effect_date, stop_sell)
                VALUES (_roomtype_id, _adate, _room_stop_sell)
                ON CONFLICT(roomtype_id, effect_date) 
                DO UPDATE SET stop_sell = excluded.stop_sell;
            
                -- reset stop_sell in child rates    
                WITH dtl AS (
                    SELECT rd.rate_id
                    FROM rate_details rd LEFT JOIN rate_plan rp ON rp.id = rd.rateplan_id 
                    WHERE rd.roomtype_id = _roomtype_id AND rp.enabled
                )
                UPDATE rate_daily rd
                SET stop_sell = NULL
                FROM dtl 
                WHERE rd.rate_id = dtl.rate_id AND rd.rate_date =_adate;
            
                GET DIAGNOSTICS _row_count = ROW_COUNT;
                _update_count = _update_count + _row_count;
                _stop_sell := NULL;
            END IF;
    
            
            -- Update other values in daily rates
            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.roomtype_id = _roomtype_id AND rp.enabled
                AND (_rate_ids IS NULL OR 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; --target;
    END LOOP;  --date;

    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_bulk_update_inventory', update_data);
END

sp_bulk_update_inventory2

Parameters

Definition