aonestar.public

Parameters

Name	Type	Mode
register_id	integer	IN
rate_id	integer	IN
arrival	date	IN
departure	date	IN
rate_schedule	jsonb	IN
result_code	text	OUT
result_msg	text	OUT
msg_code	text	OUT

Definition

DECLARE 
    start_date date;
    rate record;
    _booking_item int;
    _rate_extra_adult t_money = 0.0; 
    _rate_extra_child t_money = 0.0;  
    _rate_extra_infant t_money = 0.0; 
    _room_charge_dept int; 
    _row_count int;
    _rateplan_id int;
    _rate_name TEXT;
    _extra_adult int;    
    _extra_child int;     
    _extra_infant int;    
BEGIN 
    --start_date = (SELECT COALESCE(min(charge_date), arrival) FROM registration_rates rr WHERE rr.register_id = $1 AND posted = false);
    result_code := '0';

    start_date = arrival;

    DELETE FROM registration_rates rg 
    WHERE rg.register_id = $1 AND rg.charge_date >= start_date AND rg.posted = false; 

    SELECT rg.booking_item, coalesce($2, rd.rate_id), rg.rateplan_id, rg.extra_adult, rg.extra_child, rg.extra_infant
    FROM registration rg 
        LEFT JOIN room rm ON rm.id = rg.room_id 
        LEFT JOIN rate_details rd ON rd.roomtype_id = rm.roomtype_id AND rd.rateplan_id = rg.rateplan_id 
    WHERE rg.id = $1    
    INTO _booking_item, rate_id, _rateplan_id, _extra_adult, _extra_child, _extra_infant;

    --rate_id := COALESCE(rate_id, (SELECT rate_id FROM rate_details rd WHERE rd.roomtype_id = _roomtype_id ))
    --_booking_item = (SELECT booking_item FROM registration WHERE id = $1);

    --perform sp_log_debug('sp_save_registration_rates', coalesce(rate_schedule::text, 'NULL'));
    
    IF (rate_schedule IS NOT NULL AND jsonb_array_length(rate_schedule) > 0) THEN   
        --PERFORM sp_log_debug('sp_save_registration_rates', '1, rate_id: '||$2::text);
        SELECT COALESCE(rd.rate_extra_adult,  fn_sys_param('ROOM_RATE', 'EXTRA_ADULT',  0.0)::t_money), 
               COALESCE(rd.rate_extra_child,  fn_sys_param('ROOM_RATE', 'EXTRA_CHILD',  0.0)::t_money), 
               COALESCE(rd.rate_extra_infant, fn_sys_param('ROOM_RATE', 'EXTRA_INFANT', 0.0)::t_money),
               rd.room_charge_dept
        FROM v_rate_details rd
        WHERE rd.rate_id = $2
        INTO _rate_extra_adult, _rate_extra_child, _rate_extra_infant, _room_charge_dept;
    
        INSERT INTO registration_rates 
         (register_id, charge_date, room_rate, extrabed_rate, child_rate,  
          infant_rate, comp, room_charge_dept, rateplan_id, mealtype_id)
        SELECT $1, rs."date", rs.amount, 
            COALESCE(rs.extra_adult_rate, _rate_extra_adult), 
            COALESCE(rs.extra_child_rate, _rate_extra_child), 
            COALESCE(rs.extra_infant_rate, _rate_extra_infant), 
            COALESCE(rs.comp, FALSE), 
            _room_charge_dept, 
            rateplan_id, 
            mealtype_id              
        FROM jsonb_to_recordset(rate_schedule) 
             AS RS("date" date, amount t_money, extra_adult_rate t_money, extra_child_rate t_money, extra_infant_rate t_money, comp bool, rateplan_id int, mealtype_id int)
        ON CONFLICT DO NOTHING;    
    
    ELSIF (_booking_item IS NOT NULL) THEN 
        --PERFORM sp_log_debug('sp_save_registration_rates', '2, booking_item: '||_booking_item::text);
        INSERT INTO registration_rates (register_id, charge_date, room_rate, extrabed_rate, child_rate, 
                infant_rate, comp, room_charge_dept, rateplan_id, mealtype_id)
        SELECT $1, br.charge_date, br.room_rate, br.extrabed_rate, br.child_rate, br.infant_rate, br.comp, 
                br.room_charge_dept, br.rateplan_id, br.mealtype_id
        FROM booking_rates br 
        WHERE br.booking_item = _booking_item
        ORDER BY 1
        ON CONFLICT DO NOTHING; 
    ELSE
    
        FOR rate IN 
            SELECT rs.rate_date, rs.rate_amount, rs.extra_adult, rs.extra_child, rs.extra_infant, rs.comp, rs.comp_abf, rs.room_charge_dept              
            FROM sp_get_rate_schedule($2, start_date, departure, false) as rs
        LOOP 
            IF rate.rate_amount IS NOT NULL THEN 
                INSERT INTO registration_rates (register_id, charge_date, room_rate, extrabed_rate, child_rate, infant_rate, comp, comp_abf, room_charge_dept)
                VALUES ($1, rate.rate_date, rate.rate_amount, rate.extra_adult, rate.extra_child, rate.extra_infant, rate.comp, rate.comp_abf, rate.room_charge_dept)
                ON CONFLICT DO NOTHING;    
            ELSE 
                _rate_name := (SELECT name FROM rate_plan WHERE id = _rateplan_id);
                PERFORM sp_raise_error('10106', 'Rate "%s" is not available during this period', _rate_name);
            END IF;
        END LOOP;
    END IF;
   
    GET DIAGNOSTICS _row_count = ROW_COUNT;


    result_msg = _row_count::TEXT; 
END