aonestar.public

Parameters

Name	Type	Mode
rateplan_data	jsonb	IN
effect_child_rates	boolean	IN (DEFAULT NULL)
user_name	text	IN (DEFAULT NULL)

Definition

DECLARE 
    plan record;
    dtl record;
    log_type TEXT;
    _rateplan_id int;
    _link_adjust_amt t_money;
    _link_adjust_percent t_percent;
    _days bool[];
    _active bool;
    _rateplan_name TEXT;
    _sqlstate TEXT;
    _detail   TEXT;
    _hint     TEXT;
    _context  TEXT;
    _msg_text TEXT;
    children_plans int[];
   
   
BEGIN
    PERFORM sp_log_debug('sp_save_rate_plan', jsonb_pretty(rateplan_data)::text);

    IF (rateplan_data ? 'id' AND rateplan_data ? 'enabled' AND NOT rateplan_data ? 'name') THEN 
        _rateplan_id  := (rateplan_data->>'id')::int;
        _active       := (rateplan_data->>'enabled')::bool;
        _rateplan_name:= (SELECT name FROM rate_plan WHERE id = _rateplan_id);
    
        UPDATE rate_plan SET enabled = _active WHERE id = _rateplan_id;
         
        PERFORM sp_user_log('settings', 'Set rate plan '||IIF(_active, 'active', 'inactive'), _rateplan_name, user_name, _rateplan_id);
        RETURN fn_result_success(json_build_object('rateplan_id', _rateplan_id));
    END IF;

    SELECT * INTO plan 
    FROM jsonb_to_record(rateplan_data)
    AS (id int, code text, name text, plan_type_id int, description text, link_plan_id int,
        link_adjust_type int, link_adjust_amt numeric, link_adjust_percent numeric, link_adjust_op text,
        book_start_date date, book_end_date date, stay_start_date date, stay_end_date date,
        origin_id int, market_id int, room_charge_dept int, min_stay int, max_stay int, min_adv_book int, max_adv_book int,
        rate_extrabed numeric, rate_child numeric, rate_infant numeric, comp boolean, comp_abf boolean, 
        no_amend boolean, no_cancel boolean, no_refund boolean, enabled boolean, order_seq int,
        days t_days, inclusions int[], details jsonb, flag_effect_child bool,
        mealtype_id int, rategroup_id int, min_rate t_money, rate_type char(1));
    
    effect_child_rates := COALESCE(effect_child_rates, plan.flag_effect_child, FALSE);   
    _rateplan_id := plan.id;
    
    _days := ARRAY[COALESCE((plan.days).sun,TRUE), COALESCE((plan.days).mon,TRUE), COALESCE((plan.days).tue,TRUE), 
                   COALESCE((plan.days).wed,TRUE), COALESCE((plan.days).thu,TRUE), COALESCE((plan.days).fri,TRUE), COALESCE((plan.days).sat,TRUE)];
    IF _days = '{false,false,false,false,false,false,false}' THEN 
        _days := '{true,true,true,true,true,true,true}';
    END IF; 

    _link_adjust_amt     := IIF(plan.link_adjust_op='-', -plan.link_adjust_amt,     plan.link_adjust_amt);
    _link_adjust_percent := IIF(plan.link_adjust_op='-', -plan.link_adjust_percent, plan.link_adjust_percent);

    /* Save rate plan */
    IF COALESCE(_rateplan_id, -1) < 0 THEN 
        INSERT INTO rate_plan (code, "name", plan_type, description, link_plan_id, link_adjust_type, link_adjust_percent, link_adjust_amt, 
            book_start_date, book_end_date, stay_start_date, stay_end_date, 
            min_stay, max_stay, min_adv_book, max_adv_book, rate_extrabed, rate_child, rate_infant, days, 
            --day_sun, day_mon, day_tue, day_wed, day_thu, day_fri, day_sat, 
            origin_id, market_id, enabled, order_seq, no_amend, no_cancel, no_refund, room_charge_dept, comp, comp_abf,
            mealtype_id, rategroup_id, min_rate, rate_type) 
        VALUES(plan.code, plan.name, plan.plan_type_id, plan.description, plan.link_plan_id, plan.link_adjust_type, _link_adjust_percent, 
            _link_adjust_amt, plan.book_start_date, plan.book_end_date, plan.stay_start_date, plan.stay_end_date, 
            plan.min_stay, plan.max_stay, plan.min_adv_book, plan.max_adv_book, plan.rate_extrabed, plan.rate_child, plan.rate_infant, _days, 
            --(plan.days).sun, (plan.days).mon, (plan.days).tue, (plan.days).wed, (plan.days).thu, (plan.days).fri, (plan.days).sat,
            plan.origin_id, plan.market_id, plan.enabled, plan.order_seq, plan.no_amend, plan.no_cancel, plan.no_refund, plan.room_charge_dept, 
            COALESCE(plan.comp,FALSE), COALESCE(plan.comp_abf,FALSE), plan.mealtype_id, plan.rategroup_id, plan.min_rate, plan.rate_type)
        RETURNING id INTO _rateplan_id;
        log_type := 'Add rate plan';
    ELSE 
        UPDATE rate_plan
        SET code = plan.code, 
            "name" = plan.name, 
            plan_type = plan.plan_type_id, 
            description = plan.description, 
            link_plan_id = plan.link_plan_id, 
            link_adjust_type = plan.link_adjust_type, 
            link_adjust_percent = _link_adjust_percent, 
            link_adjust_amt = _link_adjust_amt, 
            book_start_date = plan.book_start_date, 
            book_end_date = plan.book_end_date, 
            stay_start_date = plan.stay_start_date, 
            stay_end_date = plan.stay_end_date, 
            min_stay = plan.min_stay, 
            max_stay = plan.max_stay, 
            min_adv_book = plan.min_adv_book, 
            max_adv_book = plan.max_adv_book, 
            rate_extrabed = plan.rate_extrabed, 
            rate_child = plan.rate_child, 
            rate_infant = plan.rate_infant, 
            days = _days, 
            origin_id = plan.origin_id, 
            market_id = plan.market_id, 
            enabled = plan.enabled, 
            order_seq = plan.order_seq, 
            no_amend = plan.no_amend, 
            no_cancel = plan.no_cancel, 
            no_refund = plan.no_refund, 
            room_charge_dept = plan.room_charge_dept, 
            comp = COALESCE(plan.comp), 
            comp_abf = COALESCE(plan.comp_abf),
            mealtype_id = plan.mealtype_id, 
            rategroup_id = plan.rategroup_id, 
            min_rate = plan.min_rate, 
            rate_type = plan.rate_type 
        WHERE id = plan.id;
        log_type := 'Update rate plan';
    END IF;

    /* Save inclusions */
    IF plan.inclusions IS NOT NULL THEN 
        DELETE FROM rate_inclusion WHERE rateplan_id = _rateplan_id;
        INSERT INTO rate_inclusion(rateplan_id, inclusion_id)
        SELECT _rateplan_id, unnest(plan.inclusions);
    END IF;

    /* Save rate details */ 
    FOR dtl IN 
        SELECT new_dtl.*, 
               case plan.plan_type_id
                 when 1 then new_dtl.rate_amount
                 when 5 then 0.0
                 when 6 then 0.0
                 else NULL
               end::t_money AS _rate_amount,
               (new_dtl.active <> COALESCE(old_dtl.active, FALSE)) AND (NOT new_dtl.active OR effect_child_rates) AS should_update_child,
               rt.include_adult as def_include_adult,
               rt.include_child as def_include_child,
               rt.include_infant as def_include_infant 
        FROM jsonb_to_recordset(plan.details) 
        AS new_dtl(rate_id int,
               roomtype_id int,
               description text,
               rate_amount t_money,
               rate_extrabed t_money,
               rate_child t_money,
               rate_infant t_money,
               include_adult int,
               include_child int,
               include_infant int,
--               max_adult int,
--               max_child int,
--               max_infant int,
--               max_pax int,
               active bool)
        LEFT JOIN rate_details AS old_dtl ON old_dtl.rateplan_id = _rateplan_id AND old_dtl.roomtype_id = new_dtl.roomtype_id  
        LEFT JOIN room_type rt on rt.id = new_dtl.roomtype_id 
    LOOP 
        INSERT INTO rate_details(rateplan_id, roomtype_id, description, rate_amount, rate_extrabed, rate_child, rate_infant, 
                include_adult, include_child, include_infant, active) --, max_adult, max_child, max_infant, max_pax
        VALUES (_rateplan_id, dtl.roomtype_id, dtl.description, IIF(plan.link_plan_id IS NULL, dtl._rate_amount, NULL),               
                dtl.rate_extrabed, dtl.rate_child, dtl.rate_infant, 
                COALESCE(dtl.include_adult, dtl.def_include_adult, 2), 
                COALESCE(dtl.include_child, dtl.def_include_child, 0), 
                COALESCE(dtl.include_infant, dtl.def_include_infant, 0), 
                dtl.active)  --dtl.max_adult, dtl.max_child, dtl.max_infant, dtl.max_pax,
        ON CONFLICT ON CONSTRAINT rate_details_unq
        DO UPDATE 
           SET description = excluded.description,
               rate_amount = excluded.rate_amount,
               rate_extrabed = excluded.rate_extrabed,
               rate_child = excluded.rate_child,
               rate_infant = excluded.rate_infant,
               include_adult = excluded.include_adult,
               include_child = excluded.include_child,
               include_infant = excluded.include_infant,
--               max_adult = excluded.max_adult,
--               max_child = excluded.max_child,
--               max_infant = excluded.max_infant,
--               max_pax = excluded.max_pax,
               active = excluded.active; 
               
        /* Set active on children plans, if "effect_child_rates" is TRUE or change to inactive */
        IF dtl.should_update_child THEN 
            children_plans := fn_get_rateplan_children(_rateplan_id);
            INSERT INTO rate_details(rateplan_id, roomtype_id, active)
            SELECT UNNEST(children_plans), dtl.roomtype_id, dtl.active
            ON CONFLICT ON CONSTRAINT rate_details_unq
            DO UPDATE SET active = excluded.active; 
        END IF; 
    END LOOP;

    PERFORM sp_user_log('settings', log_type, plan.name, user_name, _rateplan_id);

    RETURN fn_result_success(json_build_object('rateplan_id', _rateplan_id));

EXCEPTION 
    WHEN unique_violation then
        
        PERFORM sp_log_error('sp_save_rate_plan', 'dup code');    
        RETURN fn_result_error('99', format($$Code '%s' already exist/already in use$$, rateplan_data->>'code'));    
     --   RAISE EXCEPTION 'Duplicate code: %', p_code
     --       USING ERRCODE = '23505';


    WHEN OTHERS then
    
    
    
    GET STACKED DIAGNOSTICS
        _msg_text = MESSAGE_TEXT,
        _sqlstate = RETURNED_SQLSTATE,
        _detail   = PG_EXCEPTION_DETAIL,
        _hint     = PG_EXCEPTION_HINT,
        _context  = PG_EXCEPTION_CONTEXT; 
       
        
       
    RETURN fn_handle_error(_sqlstate, _msg_text, _detail, _hint, _context, 'sp_save_rate_plan', rateplan_data);
END