CREATE OR REPLACE PROCEDURE MONITORAGGIO.p_kriging_data(end_dt_par           in date,
                                           minuti_camp_par      number,
                                           ore_periodo_camp_par in number) AS
BEGIN
  delete tmp_kriging_data;
  insert into tmp_kriging_data
    with sens as
     (select s.id_sensore
        from sensore s
       where s.id_tipo_sensore = 301),
         --and s.cd_das in (select cd_das
         --                   from sensore_sorgente_das
         --                  where cd_flusso = 'ARL-PL')),
    conf_val as
     (select end_dt_par           end_dt,
             minuti_camp_par      minuti_camp,
             ore_periodo_camp_par ore_periodo_camp
        from dual),
    conf_dt as
     (select TRUNC(c.end_dt, 'hh24') +
             FLOOR(TO_CHAR(c.end_dt, 'mi') / minuti_camp) * minuti_camp / 1440 round_up_end_dt,
             minuti_camp,
             ore_periodo_camp,
             ore_periodo_camp * 60 / minuti_camp num_val
        from conf_val c),
    conf as
     (select round_up_end_dt end_dt,
             c.round_up_end_dt - c.ore_periodo_camp / 24 - 2 start_dt,
             minuti_camp,
             ore_periodo_camp,
             num_val
        from conf_dt c),
    dt_range as
     (select end_dt - numtodsinterval(level - 1, 'MINUTE') * minuti_camp dt
        from conf
      connect by level <= num_val),
    mis as
     (select m.id_sensore, m.dt_rilevamento, m.valore
        from sens s, misura m, conf c
       where m.id_sensore = s.id_sensore
         and m.dt_rilevamento >= c.start_dt
         and m.dt_rilevamento <= c.end_dt
      union
      select m.id_sensore, m.dt_rilevamento, m.valore
        from sens s, mis_pioggia m, conf c
       where m.id_sensore = s.id_sensore
         and m.dt_rilevamento >= c.start_dt
         and m.dt_rilevamento <= c.end_dt),
    mis_cumul as
     (select m.id_sensore,
             m.dt_rilevamento,
             m.valore,
             r.dt,
             case
               when r.dt is not null then
                sum(m.valore)
                over(PARTITION BY m.id_sensore ORDER BY m.dt_rilevamento
                     RANGE INTERVAL '1' HOUR PRECEDING)
             end cumulata_1_ora,
             case
               when r.dt is not null then
                sum(m.valore)
                over(PARTITION BY m.id_sensore ORDER BY m.dt_rilevamento
                     RANGE INTERVAL '3' HOUR PRECEDING)
             end cumulata_3_ora,
             case
               when r.dt is not null then
                sum(m.valore)
                over(PARTITION BY m.id_sensore ORDER BY m.dt_rilevamento
                     RANGE INTERVAL '6' HOUR PRECEDING)
             end cumulata_6_ora,
             case
               when r.dt is not null then
                sum(m.valore)
                over(PARTITION BY m.id_sensore ORDER BY m.dt_rilevamento
                     RANGE INTERVAL '12' HOUR PRECEDING)
             end cumulata_12_ora,
             case
               when r.dt is not null then
                sum(m.valore)
                over(PARTITION BY m.id_sensore ORDER BY m.dt_rilevamento
                     RANGE INTERVAL '24' HOUR PRECEDING)
             end cumulata_24_ora,
             case
               when r.dt is not null then
                sum(m.valore)
                over(PARTITION BY m.id_sensore ORDER BY m.dt_rilevamento
                     RANGE INTERVAL '48' HOUR PRECEDING)
             end cumulata_48_ora
        from mis m
        left join dt_range r
          on m.dt_rilevamento = r.dt),
    mis_camp as
     (select m.id_sensore,
             m.dt_rilevamento,
             m.cumulata_1_ora,
             m.cumulata_3_ora,
             m.cumulata_6_ora,
             m.cumulata_12_ora,
             m.cumulata_24_ora,
             m.cumulata_48_ora
        from mis_cumul m
       where m.dt is not null)
    select s.id_sensore,
           z.id_stazione,
           z.utm_est,
           z.utm_nord,
           l.dt_rilevamento,
           nvl(l.cumulata_1_ora, -99999) cumulata_1_ora,
           nvl(l.cumulata_3_ora, -99999) cumulata_3_ora,
           nvl(l.cumulata_6_ora, -99999) cumulata_6_ora,
           nvl(l.cumulata_12_ora, -99999) cumulata_12_ora,
           nvl(l.cumulata_24_ora, -99999) cumulata_24_ora,
           nvl(l.cumulata_48_ora, -99999) cumulata_48_ora,
           u.simbolo um
      from sensore s
      join sens r
        on s.id_sensore = r.id_sensore
      join stazione z
        on s.id_stazione = z.id_stazione
      join tipo_sensore t
        on s.id_tipo_sensore = t.id_tipo_sensore
      join tipo_sensore t
        on s.id_tipo_sensore = t.id_tipo_sensore
      join def_unita_misura u
        on t.id_unita_misura_vis = u.id_unita_misura
      left join mis_camp l
        on l.id_sensore = s.id_sensore
     where z.utm_est is not null
       and z.utm_nord is not null;
  --and z.id_stato_stazione = 1
END;