create or replace FUNCTION calc_topn1_proc(p_last_run TIMESTAMP,
   p_last_stat TIMESTAMP)
RETURNS VOID
LANGUAGE plpgsql
VOLATILE
AS $BODY$
 
-----------------------------------------------------------------------------
-- Procedure	: calc_topn1_proc
-- Description	: This procedure implements background processing of daily
--                integral values for improvement of Top N Ranking feature.
--                Should be run from rule_topn1_proc.
-- Parameters   : p_last_run date - last successful run
--                p_last_stat date - last income sample_time
-----------------------------------------------------------------------------

  

 -- Step 1.
-- Gather daily stats data, calculate integrals and store them into topn_past_day table.
-- Select only configured for Top N processing stats and only fresh data.
-- The special structure and order of the data is to calculate integals
-- using only SQL engine avoiding cursors or other row-by-row processing
-- to provide maximum performance

-- IF last_run is null, exit the procedure immediately.
   DECLARE
   v_time_level  vpx_topn_state.time_level%type DEFAULT 1;
   v_partitions  VARCHAR(1000) DEFAULT ' ';
   v_sql  VARCHAR(2000);
   v_partition  VPX_SAMPLE_TIME1.partition_index%type;
   v_cnt  INTEGER;

   PI_CUR cursor FOR select distinct partition_index
   from VPX_SAMPLE_TIME1
   where sample_time > p_last_run and sample_time <= p_last_stat;
BEGIN
   select count(1) into v_cnt
   from vpx_job_log
   where job_id = v_time_level;

   if v_cnt = 0 then
      return;
   end if;

   v_cnt := 0;
   open PI_CUR;
   loop
      fetch PI_CUR into v_partition;
      exit when NOT FOUND;
      v_partitions := v_partitions || case when v_cnt > 0 then ' union all '
                                           else ' '
                                       end
      || 'select counter_id, time_id, stat_val from VPX_HIST_STAT1_' || CAST(v_partition AS TEXT);
      v_cnt := v_cnt+1;
   end loop;
   close PI_CUR;

-- check whether we have any partitions at all
-- if not stop
   if v_cnt = 0 then
      return;
   end if;

   v_sql := 'INSERT INTO vpx_topn_past_day(counter_id, sample_time, integral_value)
 (SELECT s.counter_id, t.sample_time,
         round(COALESCE(lag(s.stat_val) OVER (PARTITION BY s.counter_id ORDER BY t.time_id)
         + s.stat_val, 0) * sample_interval / 60 / 2, 0)
    FROM (' || v_partitions || ') s
          JOIN VPX_SAMPLE_TIME1 t ON t.time_id = s.time_id
          JOIN vpx_stat_counter c ON c.counter_id = s.counter_id
          JOIN vpx_topn_statistics tos ON tos.stat_id = c.stat_id
   WHERE t.sample_time >= $1 and t.sample_time <=$2)';

   EXECUTE v_sql using p_last_run, p_last_stat;

   GET DIAGNOSTICS v_cnt = ROW_COUNT;
   IF v_cnt < 1 THEN
      RETURN;
   END IF;

   v_cnt := 0;
 -- check for rowcount, if found UPDATE else INSERT row into vpx_topn_state
   select 1 into v_cnt
   from vpx_topn_state
   where time_level = v_time_level;

   IF v_cnt = 1 THEN
      UPDATE vpx_topn_state
      SET sample_time = p_last_stat
      WHERE TIME_LEVEL = v_time_level;
   ELSE
      INSERT INTO vpx_topn_state(TIME_LEVEL,SAMPLE_TIME)
      VALUES(v_time_level,p_last_stat);
   END IF;

END;
$BODY$
