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;
    log_lines TEXT;
    item record;
BEGIN
    /* update_data:
    {   "values": {               // require at least 1 value from below
            "room_rate": 1250.50, // optional
            "stop_sell": true,    // optional
            "cta": true,          // optional
            "ctd": true,          // optional
            "min_stay": 2,        // optional
            "max_stay": 4         // optional
        },
        "dates": {  
            "start": "2021-01-01", //require
            "end": "2021-03-31"    //require
            "days": {"sun": true, "mon": true, "tue": true, "wed": true, "thu": true, "fri": true, "sat": true} // optional
        },
        "rates": [rate_id, rate_id, ...] //require
    }   */
    _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)
       -- GROUP BY rp.order_seq, rp.name
    ) 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;


     
    /*
    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   
                    );
    */

    FOR _adate IN 
        SELECT adate
        FROM fn_date_range(_start_date, _end_date) AS adate
        WHERE fn_match_dows(adate, _days)
    LOOP  
       -- RAISE NOTICE '%', _adate;
        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) --rp.enabled AND 
        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; 

    /* Save log */
    IF _update_count > 0 THEN 
        log_text := format('%s%s%s%s%s%s', --'  %s: %s%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;

    RETURN fn_result_success('99900', '%s records updated', _update_count::text);
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