aonestar.public

Parameters

Name	Type	Mode
limit_items	integer	IN (DEFAULT 999999)

Definition

DECLARE bk record;
    gen_count int = 0;
    _total_items int = 0;
    _row_count int;
    _item_count int = 0;
    _night_count int = 0;
    _insert_count int = 0;
BEGIN 
    FOR bk in (
        SELECT bi.* --, rd.rate_extra_adult, rd.rate_extra_child, rd.rate_extra_infant, rd.room_charge_dept, rd.rate_amount  
        FROM booking_items bi 
            LEFT JOIN booking b ON b.id = bi.booking_id
            --LEFT JOIN rate_details rd ON rd.roomtype_id = bi.roomtype_id AND rd.rateplan_id = bi.rateplan_id 
        WHERE b.status IN ('R','C','W','I') AND NOT exists(SELECT * FROM booking_rates WHERE booking_item = bi.item_id)
            --AND bi.rate_id IS NOT null
        LIMIT limit_items
    ) LOOP 
        _total_items := _total_items +1;
        INSERT INTO booking_rates (booking_id, booking_item, charge_date, room_rate, extrabed_rate, child_rate, infant_rate, room_charge_dept)
        --VALUES (booking_id, item_id, rate_date, rate_amount, rate_extra_adult, rate_extra_child, rate_extra_infant, room_charge_dept );
        SELECT bk.booking_id, bk.item_id, --arrival, departure, --min(rate_date), max(rate_date)+1, sum(iif(rate_amount IS NULL, 1, 0)) null_rates
            rs.rate_date, rs.rate_amount, rs.extra_adult, rs.extra_child, rs.extra_infant, rs.room_charge_dept 
        FROM sp_get_rate_schedule(bk.roomtype_id, bk.rateplan_id, bk.arrival, bk.departure) AS rs;
    
        GET DIAGNOSTICS _row_count = ROW_COUNT;
        _item_count := _item_count + IIF(_row_count > 0, 1, 0);
        _night_count := _night_count + (bk.departure-bk.arrival);
        _insert_count := _insert_count + _row_count;
    
        RAISE NOTICE '%> %/%, rate_id: %, (% nights), % rows inserted', _total_items, bk.booking_id, bk.item_id, bk.rate_id, bk.departure-bk.arrival, _row_count;
        
        gen_count := gen_count +1;
    
        IF gen_count >= 100 THEN 
            COMMIT;
            RAISE NOTICE '** Commit % records **', gen_count;
            gen_count := 0;
        END IF;
    END LOOP; 

    IF gen_count > 0 THEN 
       COMMIT;
    END IF;

    RAISE NOTICE '%/% items, % nights, % rows inserted', _item_count, _total_items, _night_count, _insert_count;
END

sp_gen_booking_rates

Parameters

Definition