/**
 * @author tpasquet
 * @date 07/12/21
 * @version 1.0.0
 * @project SIEau
 * @table chroniques_pluvio
 */

# --- !Ups
create or replace function getallpluviomeasures(p_stationids integer[], p_filtreid character varying, p_codegestionnaire integer, p_loginmaj character varying, p_job_execution_id integer) returns double precision
    language plpgsql
as
$$
DECLARE
my_cursor_measures 				      REFCURSOR;;
  my_cursor_stations              REFCURSOR;;
  measure 							          RECORD;;
  v_requete 						          VARCHAR (2000);;
  v_requete_count				          VARCHAR (2000);;
  d_count 							          double precision;;
  d_count_ch						          double precision;;
  d_count_chb 						        double precision;;
  d_count_chbs 						        double precision;;
  d_count_chbc 						        double precision;;
  d_count_repere					        double precision;;
  d_count_intervenant				      double precision;;
  d_count_modif                   double precision;;
  v_identifiant 						          VARCHAR (20);;
  d_j 								            double precision;;
  i_codepluviometre                integer;;
  v_requete_filtre    				    VARCHAR (2000);;
  v_rupture                       VARCHAR (100);;
  v_dateEtHeure                   VARCHAR(100);;
  d_id_job                        double precision;;
  b_integration                   boolean;;

BEGIN

  v_requete := '';;
  d_count := 0;;
  d_count_ch := 0;;
  d_count_chb := 0;;
  d_count_chbs := 0;;
  d_count_chbc := 0;;
  d_count_repere := 0;;
  d_count_intervenant := 0;;
  d_count_modif := 0;;
  d_id_job := 0;;
  b_integration := true;;

  /* Table de logs */
DROP TABLE IF EXISTS ebonneau_logs;;
CREATE TABLE ebonneau_logs (id double precision, commentaire text, valeur text, execution_timestamp timestamp, station varchar(255), job_execution_id integer);;

/*RAISE NOTICE 'Initialisation du traitement;;*/
INSERT INTO ebonneau_logs VALUES(d_id_job, 'Initialisation du traitement', null, now(), null, p_job_execution_id);;
d_id_job := d_id_job +1;;

  /* Liste des dates à exclures */
DROP TABLE IF EXISTS date_a_exclure;;
CREATE TABLE date_a_exclure(date varchar);;
insert into date_a_exclure VALUES ('26/03/2000 02:00:00'),
                                  ('25/03/2001 02:00:00'),
                                  ('31/03/2002 02:00:00'),
                                  ('30/03/2003 02:00:00'),
                                  ('28/03/2004 02:00:00'),
                                  ('27/03/2005 02:00:00'),
                                  ('26/03/2006 02:00:00'),
                                  ('25/03/2007 02:00:00'),
                                  ('30/03/2008 02:00:00'),
                                  ('29/03/2009 02:00:00'),
                                  ('28/03/2010 02:00:00'),
                                  ('27/03/2011 02:00:00'),
                                  ('25/03/2012 02:00:00'),
                                  ('31/03/2013 02:00:00'),
                                  ('30/03/2014 02:00:00'),
                                  ('29/03/2015 02:00:00'),
                                  ('27/03/2016 02:00:00'),
                                  ('26/03/2017 02:00:00'),
                                  ('25/03/2018 02:00:00'),
                                  ('31/03/2019 02:00:00'),
                                  ('29/03/2020 02:00:00');;

IF p_stationIds is not null THEN
    OPEN my_cursor_stations FOR SELECT codepluviometre FROM pluviometres WHERE codepluviometre = ANY(p_stationIds);;
ELSE
    IF p_filtreId is not null THEN
SELECT selectionfiltre INTO v_requete_filtre FROM utilisateurs_listes
WHERE module = 'SIP'
  AND codeliste = p_filtreId;;
OPEN my_cursor_stations FOR EXECUTE v_requete_filtre;;
ELSE
      OPEN my_cursor_stations FOR SELECT codepluviometre FROM pluviometres /*where codepluviometre = 1*/;;
END IF;;
END IF;;

  LOOP
FETCH my_cursor_stations INTO i_codepluviometre;;
    EXIT WHEN NOT FOUND;;

    /* Récupération du identifiant de la station */
SELECT identifiant INTO v_identifiant FROM pluviometres
WHERE codepluviometre = i_codepluviometre;;

RAISE NOTICE 'Traitement de la station %', v_identifiant;;
INSERT INTO ebonneau_logs VALUES(d_id_job, 'Traitement de la station', null, now(), v_identifiant, p_job_execution_id);;

d_id_job := d_id_job +1;;

    IF v_identifiant <> '' THEN

      RAISE NOTICE 'Suppression des données existantes dans les tables de destination';;
INSERT INTO ebonneau_logs VALUES(d_id_job, 'Suppression des données existantes dans les tables de destination', null, now(), v_identifiant, p_job_execution_id);;
d_id_job := d_id_job +1;;

DELETE FROM chroniques_pluvio WHERE codepluviometre=i_codepluviometre;;
DELETE FROM chroniques_pluvio_suppr WHERE codepluviometre=i_codepluviometre;;

RAISE NOTICE 'ETAPE 1: Intégration des données brutes';;
INSERT INTO ebonneau_logs VALUES(d_id_job, 'ETAPE 1: Intégration des données brutes', null, now(), v_identifiant, p_job_execution_id);;
d_id_job := d_id_job +1;;

      v_requete := 'SELECT datecreation, heure, cast(pluie as double precision) as pluie, cast(pluieefficace as double precision) as pluieefficace, ';;
      v_requete := v_requete || ' cast(etp as double precision) as etp, cast(valeur1 as double precision) as valeur1, cast(valeur2 as double precision) as valeur2, cast(valeur3 as double precision) as valeur3,';;
      v_requete := v_requete || ' continuite, modeobtention, cast(precision as double precision) as precision, validite';;
      v_requete := v_requete || ' from pluvio_' || i_codepluviometre || ' ORDER BY datecreation ASC, heure ASC';;

      d_j := 0;;
      v_rupture := '0';;

BEGIN
EXECUTE v_requete;;
b_integration := true;;
EXCEPTION
        WHEN undefined_table THEN
          RAISE NOTICE '[UNDEFINED TABLE] Error on STEP 1 during table select : %', v_identifiant;;
INSERT INTO ebonneau_logs VALUES(d_id_job, '[UNDEFINED TABLE] Error on STEP 1 on getting data', v_requete, now(), v_identifiant, p_job_execution_id);;
d_id_job := d_id_job +1;;
          b_integration := false;;
WHEN OTHERS THEN
          RAISE NOTICE '[OTHERS] Error % on STEP 1 during table select : %', SQLERRM, v_identifiant;;
INSERT INTO ebonneau_logs VALUES(d_id_job, '[OTHERS] Error '|| SQLERRM ||' on STEP 1 on getting data', v_requete, now(), v_identifiant, p_job_execution_id);;
d_id_job := d_id_job +1;;
          b_integration := false;;
end;;

      IF b_integration = true THEN
        OPEN my_cursor_measures FOR EXECUTE v_requete;;
        LOOP
FETCH my_cursor_measures into measure;;
          EXIT WHEN NOT FOUND;;

          IF measure.pluie is not null THEN
            --PLUIE
            v_requete := 'insert into chroniques_pluvio values ';;
            v_requete := v_requete || '(';;
            v_requete := v_requete || i_codepluviometre || ', ';;
            --RAISE NOTICE 'CB i_codepluviometre : %', i_codepluviometre;;
            v_requete := v_requete || '1, ';; /* pluie */
            IF measure.datecreation is null OR measure.heure is null THEN
              v_requete := v_requete || 'null, ';;
ELSE
              v_dateEtHeure := to_char(measure.datecreation, 'DD/MM/YYYY') ||' '||to_char(measure.heure, 'HH24:MI:SS');;
END IF;;
            v_requete := v_requete || '''' || v_dateEtHeure || ''', ';;
            --RAISE NOTICE 'CB datecreation : %', v_dateEtHeure;;
            v_requete := v_requete || measure.pluie || ', ';;
            v_requete := v_requete || 'null, ';; /* valeurmesurecorrigée */
            IF measure.modeobtention = 'A' THEN
              v_requete := v_requete || '1, ';;  /* mode mesure */
            ELSEIF measure.modeobtention = 'M' THEN
              v_requete := v_requete || '2, ';;  /* mode mesure */
ELSE
              v_requete := v_requete || '0, ';;  /* mode mesure */
END IF;;
            --RAISE NOTICE 'CB measure.modeobtention : %', measure.modeobtention;;
            IF measure.validite = '' THEN
              v_requete := v_requete || '1, ';; /* statutMesure brute */
ELSE
              v_requete := v_requete || measure.validite || ', ';; /* statutMesure */
END IF;;
            --RAISE NOTICE 'CB measure.validite : %', measure.validite;;
            v_requete := v_requete || '4, ';; /* qualificationMesure non qualifiée */
            v_requete := v_requete || 'null, ';;   /* natureMesure non renseignée */
            IF d_j = 0 THEN
              v_requete := v_requete || '1, ';;  /* point initial */
              v_rupture := measure.continuite || '';;
            ELSEIF v_rupture = '1' THEN
              v_requete := v_requete || '1, ';;  /* point initial */
ELSE
              v_requete := v_requete || '2, ';;  /* point continu */
END IF;;
            --RAISE NOTICE 'CB d_j : %', d_j;;
            v_rupture := measure.continuite || '';;
            v_requete := v_requete || 'null, ';;  /* codeReponsableValidation */
            v_requete := v_requete || 'null, ';;  /* codeGestionnaire */
            v_requete := v_requete || 'null, ';;  /* codeProducteur */
            v_requete := v_requete || 'null, ';;  /* jobExecutionId */
            v_requete := v_requete || 'null, ';;  /* dateCreation */
            v_requete := v_requete || 'null, ';;  /* dateMaj */
            v_requete := v_requete || 'null ';;  /* loginMaj */
            v_requete := v_requete || ')';;

BEGIN
              RAISE NOTICE 'REQUETE : %', v_requete;;
              IF v_requete is not null THEN
                EXECUTE v_requete;;
                d_j := d_j + 1;;
ELSE
                INSERT INTO ebonneau_logs VALUES(d_id_job, '[NO REQUEST] Error on STEP 1 on pluie : ' || measure, v_requete, now(), v_identifiant, p_job_execution_id);;
                d_id_job := d_id_job +1;;
END IF;;
EXCEPTION
              WHEN undefined_table THEN
                RAISE NOTICE '[UNDEFINED TABLE] Error on STEP 1 during table insert : %', v_identifiant;;
INSERT INTO ebonneau_logs VALUES(d_id_job, '[UNDEFINED TABLE] Error on STEP 1 during table insert pluie', v_requete, now(), v_identifiant, p_job_execution_id);;
d_id_job := d_id_job +1;;
WHEN OTHERS THEN
                RAISE NOTICE '[OTHERS] Error % on STEP 1 during table insert : %', SQLERRM, v_identifiant;;
INSERT INTO ebonneau_logs VALUES(d_id_job, '[OTHERS] Error '|| SQLERRM ||' on STEP 1 during table insert pluie', v_requete, now(), v_identifiant, p_job_execution_id);;
d_id_job := d_id_job +1;;
END;;
END IF;;

          IF measure.pluieefficace is not null THEN
            -- PLUIE EFFICACE
            v_requete := 'insert into chroniques_pluvio values ';;
            v_requete := v_requete || '(';;
            v_requete := v_requete || i_codepluviometre || ', ';;
            --RAISE NOTICE 'CB i_codepluviometre : %', i_codepluviometre;;
            v_requete := v_requete || '2, ';; /* pluieefficace */
            IF measure.datecreation is null OR measure.heure is null THEN
              v_requete := v_requete || 'null, ';;
ELSE
              v_dateEtHeure := to_char(measure.datecreation, 'DD/MM/YYYY') ||' '||to_char(measure.heure, 'HH24:MI:SS');;
END IF;;
            v_requete := v_requete || '''' || v_dateEtHeure || ''', ';;
            /* RAISE NOTICE 'CB datecreation : %', v_dateEtHeure;; */
            v_requete := v_requete || measure.pluieefficace || ', ';;
            v_requete := v_requete || 'null, ';; /* valeurmesurecorrigée */
            IF measure.modeobtention = 'A' THEN
              v_requete := v_requete || '1, ';;  /* mode mesure */
            ELSEIF measure.modeobtention = 'M' THEN
              v_requete := v_requete || '2, ';;  /* mode mesure */
ELSE
              v_requete := v_requete || '0, ';;  /* mode mesure */
END IF;;
            /* RAISE NOTICE 'CB measure.modeobtention : %', measure.modeobtention;; */
            IF measure.validite = '' THEN
              v_requete := v_requete || '1, ';; /* statutMesure brute */
ELSE
              v_requete := v_requete || measure.validite || ', ';; /* statutMesure */
END IF;;
            /* RAISE NOTICE 'CB measure.validite : %', measure.validite;; */
            v_requete := v_requete || '4, ';; /* qualificationMesure non qualifiée */
            /* RAISE NOTICE 'CB measure.qualification : %', measure.qualification;; */
            v_requete := v_requete || 'null, ';;   /* natureMesure non renseignée */
            /* RAISE NOTICE 'CB measure.naturemesure : %', measure.naturemesure;; */
            IF d_j = 0 THEN
              v_requete := v_requete || '1, ';;  /* point initial */
              v_rupture := measure.continuite || '';;
            ELSEIF v_rupture = '1' THEN
              v_requete := v_requete || '1, ';;  /* point initial */
ELSE
              v_requete := v_requete || '2, ';;  /* point continu */
END IF;;
            /* RAISE NOTICE 'CB d_j : %', d_j;; */
            v_rupture := measure.continuite || '';;
            /* RAISE NOTICE 'CB measure.continuite : %', measure.continuite;; */
            v_requete := v_requete || 'null, ';;  /* codeReponsableValidation */
            v_requete := v_requete || 'null, ';;  /* codeGestionnaire */
            v_requete := v_requete || 'null, ';;  /* codeProducteur */
            v_requete := v_requete || 'null, ';;  /* jobExecutionId */
            v_requete := v_requete || 'null, ';;  /* dateCreation */
            v_requete := v_requete || 'null, ';;  /* dateMaj */
            v_requete := v_requete || 'null ';;  /* loginMaj */
            v_requete := v_requete || ')';;
BEGIN
              IF v_requete is not null THEN
                EXECUTE v_requete;;
                d_j := d_j + 1;;
ELSE
                INSERT INTO ebonneau_logs VALUES(d_id_job, '[NO REQUEST] Error on STEP 1 on measure pluie efficace : ' || measure, v_requete, now(), v_identifiant, p_job_execution_id);;
                d_id_job := d_id_job +1;;
END IF;;
EXCEPTION
              WHEN undefined_table THEN
                /*RAISE NOTICE '[UNDEFINED TABLE] Error on STEP 1 during table insert : %', v_identifiant;;*/
                INSERT INTO ebonneau_logs VALUES(d_id_job, '[UNDEFINED TABLE] Error on STEP 1 during table insert pluie efficace ', v_requete, now(), v_identifiant, p_job_execution_id);;
                d_id_job := d_id_job +1;;
WHEN OTHERS THEN
                /*RAISE NOTICE '[OTHERS] Error % on STEP 1 during table insert : %', SQLERRM, v_identifiant;;*/
                INSERT INTO ebonneau_logs VALUES(d_id_job, '[OTHERS] Error '|| SQLERRM ||' on STEP 1 during table insert pluie efficace', v_requete, now(), v_identifiant, p_job_execution_id);;
                d_id_job := d_id_job +1;;
END;;
END IF;;

          IF measure.etp is not null THEN
            -- ETP
            v_requete := 'insert into chroniques_pluvio values ';;
            v_requete := v_requete || '(';;
            v_requete := v_requete || i_codepluviometre || ', ';;
            /* RAISE NOTICE 'CB i_codepluviometre : %', i_codepluviometre;; */
            v_requete := v_requete || '3, ';; /* etp */
            IF measure.datecreation is null OR measure.heure is null THEN
              v_requete := v_requete || 'null, ';;
ELSE
              v_dateEtHeure := to_char(measure.datecreation, 'DD/MM/YYYY') ||' '||to_char(measure.heure, 'HH24:MI:SS');;
END IF;;
            v_requete := v_requete || '''' || v_dateEtHeure || ''', ';;
            /* RAISE NOTICE 'CB datecreation : %', v_dateEtHeure;; */
            v_requete := v_requete || measure.etp || ', ';;
            v_requete := v_requete || 'null, ';; /* valeurmesurecorrigée */
            IF measure.modeobtention = 'A' THEN
              v_requete := v_requete || '1, ';;  /* mode mesure */
            ELSEIF measure.modeobtention = 'M' THEN
              v_requete := v_requete || '2, ';;  /* mode mesure */
ELSE
              v_requete := v_requete || '0, ';;  /* mode mesure */
END IF;;
            /* RAISE NOTICE 'CB measure.modeobtention : %', measure.modeobtention;; */
            IF measure.validite = '' THEN
              v_requete := v_requete || '1, ';; /* statutMesure brute */
ELSE
              v_requete := v_requete || measure.validite || ', ';; /* statutMesure */
END IF;;
            /* RAISE NOTICE 'CB measure.validite : %', measure.validite;; */
            v_requete := v_requete || '4, ';; /* qualificationMesure non qualifiée */
            /* RAISE NOTICE 'CB measure.qualification : %', measure.qualification;; */
            v_requete := v_requete || 'null, ';;   /* natureMesure non renseignée */
            /* RAISE NOTICE 'CB measure.naturemesure : %', measure.naturemesure;; */
            IF d_j = 0 THEN
              v_requete := v_requete || '1, ';;  /* point initial */
              v_rupture := measure.continuite || '';;
            ELSEIF v_rupture = '1' THEN
              v_requete := v_requete || '1, ';;  /* point initial */
ELSE
              v_requete := v_requete || '2, ';;  /* point continu */
END IF;;
            /* RAISE NOTICE 'CB d_j : %', d_j;; */
            v_rupture := measure.continuite || '';;
            /* RAISE NOTICE 'CB measure.continuite : %', measure.continuite;; */
            v_requete := v_requete || 'null, ';;  /* codeReponsableValidation */
            v_requete := v_requete || 'null, ';;  /* codeGestionnaire */
            v_requete := v_requete || 'null, ';;  /* codeProducteur */
            v_requete := v_requete || 'null, ';;  /* jobExecutionId */
            v_requete := v_requete || 'null, ';;  /* dateCreation */
            v_requete := v_requete || 'null, ';;  /* dateMaj */
            v_requete := v_requete || 'null ';;  /* loginMaj */
            v_requete := v_requete || ')';;
BEGIN
              IF v_requete is not null THEN
                EXECUTE v_requete;;
                d_j := d_j + 1;;
ELSE
                INSERT INTO ebonneau_logs VALUES(d_id_job, '[NO REQUEST] Error on STEP 1 on measure etp : ' || measure, v_requete, now(), v_identifiant, p_job_execution_id);;
                d_id_job := d_id_job +1;;
END IF;;
EXCEPTION
              WHEN undefined_table THEN
                /*RAISE NOTICE '[UNDEFINED TABLE] Error on STEP 1 during table insert : %', v_identifiant;;*/
                INSERT INTO ebonneau_logs VALUES(d_id_job, '[UNDEFINED TABLE] Error on STEP 1 during table insert etp ', v_requete, now(), v_identifiant, p_job_execution_id);;
                d_id_job := d_id_job +1;;
WHEN OTHERS THEN
                /*RAISE NOTICE '[OTHERS] Error % on STEP 1 during table insert : %', SQLERRM, v_identifiant;;*/
                INSERT INTO ebonneau_logs VALUES(d_id_job, '[OTHERS] Error '|| SQLERRM ||' on STEP 1 during table insert etp', v_requete, now(), v_identifiant, p_job_execution_id);;
                d_id_job := d_id_job +1;;
END;;
END IF;;

          IF measure.valeur1 is not null THEN
            -- VALEUR1
            v_requete := 'insert into chroniques_pluvio values ';;
            v_requete := v_requete || '(';;
            v_requete := v_requete || i_codepluviometre || ', ';;
            /* RAISE NOTICE 'CB i_codepluviometre : %', i_codepluviometre;; */
            v_requete := v_requete || '4, ';; /* valeur1 */
            IF measure.datecreation is null OR measure.heure is null THEN
              v_requete := v_requete || 'null, ';;
ELSE
              v_dateEtHeure := to_char(measure.datecreation, 'DD/MM/YYYY') ||' '||to_char(measure.heure, 'HH24:MI:SS');;
END IF;;
            v_requete := v_requete || '''' || v_dateEtHeure || ''', ';;
            /* RAISE NOTICE 'CB datecreation : %', v_dateEtHeure;; */
            v_requete := v_requete || measure.valeur1 || ', ';;
            v_requete := v_requete || 'null, ';; /* valeurmesurecorrigée */
            IF measure.modeobtention = 'A' THEN
              v_requete := v_requete || '1, ';;  /* mode mesure */
            ELSEIF measure.modeobtention = 'M' THEN
              v_requete := v_requete || '2, ';;  /* mode mesure */
ELSE
              v_requete := v_requete || '0, ';;  /* mode mesure */
END IF;;
            /* RAISE NOTICE 'CB measure.modeobtention : %', measure.modeobtention;; */
            IF measure.validite = '' THEN
              v_requete := v_requete || '1, ';; /* statutMesure brute */
ELSE
              v_requete := v_requete || measure.validite || ', ';; /* statutMesure */
END IF;;
            /* RAISE NOTICE 'CB measure.validite : %', measure.validite;; */
            v_requete := v_requete || '4, ';; /* qualificationMesure non qualifiée */
            /* RAISE NOTICE 'CB measure.qualification : %', measure.qualification;; */
            v_requete := v_requete || 'null, ';;   /* natureMesure non renseignée */
            /* RAISE NOTICE 'CB measure.naturemesure : %', measure.naturemesure;; */
            IF d_j = 0 THEN
              v_requete := v_requete || '1, ';;  /* point initial */
              v_rupture := measure.continuite || '';;
            ELSEIF v_rupture = '1' THEN
              v_requete := v_requete || '1, ';;  /* point initial */
ELSE
              v_requete := v_requete || '2, ';;  /* point continu */
END IF;;
            /* RAISE NOTICE 'CB d_j : %', d_j;; */
            v_rupture := measure.continuite || '';;
            /* RAISE NOTICE 'CB measure.continuite : %', measure.continuite;; */
            v_requete := v_requete || 'null, ';;  /* codeReponsableValidation */
            v_requete := v_requete || 'null, ';;  /* codeGestionnaire */
            v_requete := v_requete || 'null, ';;  /* codeProducteur */
            v_requete := v_requete || 'null, ';;  /* jobExecutionId */
            v_requete := v_requete || 'null, ';;  /* dateCreation */
            v_requete := v_requete || 'null, ';;  /* dateMaj */
            v_requete := v_requete || 'null ';;  /* loginMaj */
            v_requete := v_requete || ')';;
BEGIN
              IF v_requete is not null THEN
                EXECUTE v_requete;;
                d_j := d_j + 1;;
ELSE
                INSERT INTO ebonneau_logs VALUES(d_id_job, '[NO REQUEST] Error on STEP 1 on measure valeur1 : ' || measure, v_requete, now(), v_identifiant, p_job_execution_id);;
                d_id_job := d_id_job +1;;
END IF;;
EXCEPTION
              WHEN undefined_table THEN
                /*RAISE NOTICE '[UNDEFINED TABLE] Error on STEP 1 during table insert : %', v_identifiant;;*/
                INSERT INTO ebonneau_logs VALUES(d_id_job, '[UNDEFINED TABLE] Error on STEP 1 during table insert valeur1 ', v_requete, now(), v_identifiant, p_job_execution_id);;
                d_id_job := d_id_job +1;;
WHEN OTHERS THEN
                /*RAISE NOTICE '[OTHERS] Error % on STEP 1 during table insert : %', SQLERRM, v_identifiant;;*/
                INSERT INTO ebonneau_logs VALUES(d_id_job, '[OTHERS] Error '|| SQLERRM ||' on STEP 1 during table insert valeur1', v_requete, now(), v_identifiant, p_job_execution_id);;
                d_id_job := d_id_job +1;;
END;;
END IF;;

          IF measure.valeur2 is not null THEN
            -- VALEUR2
            v_requete := 'insert into chroniques_pluvio values ';;
            v_requete := v_requete || '(';;
            v_requete := v_requete || i_codepluviometre || ', ';;
            /* RAISE NOTICE 'CB i_codepluviometre : %', i_codepluviometre;; */
            v_requete := v_requete || '5, ';; /* etp */
            IF measure.datecreation is null OR measure.heure is null THEN
              v_requete := v_requete || 'null, ';;
ELSE
              v_dateEtHeure := to_char(measure.datecreation, 'DD/MM/YYYY') ||' '||to_char(measure.heure, 'HH24:MI:SS');;
END IF;;
            v_requete := v_requete || '''' || v_dateEtHeure || ''', ';;
            /* RAISE NOTICE 'CB datecreation : %', v_dateEtHeure;; */
            v_requete := v_requete || measure.valeur2 || ', ';;
            v_requete := v_requete || 'null, ';; /* valeurmesurecorrigée */
            IF measure.modeobtention = 'A' THEN
              v_requete := v_requete || '1, ';;  /* mode mesure */
            ELSEIF measure.modeobtention = 'M' THEN
              v_requete := v_requete || '2, ';;  /* mode mesure */
ELSE
              v_requete := v_requete || '0, ';;  /* mode mesure */
END IF;;
            /* RAISE NOTICE 'CB measure.modeobtention : %', measure.modeobtention;; */
            IF measure.validite = '' THEN
              v_requete := v_requete || '1, ';; /* statutMesure brute */
ELSE
              v_requete := v_requete || measure.validite || ', ';; /* statutMesure */
END IF;;
            /* RAISE NOTICE 'CB measure.validite : %', measure.validite;; */
            v_requete := v_requete || '4, ';; /* qualificationMesure non qualifiée */
            /* RAISE NOTICE 'CB measure.qualification : %', measure.qualification;; */
            v_requete := v_requete || 'null, ';;   /* natureMesure non renseignée */
            /* RAISE NOTICE 'CB measure.naturemesure : %', measure.naturemesure;; */
            IF d_j = 0 THEN
              v_requete := v_requete || '1, ';;  /* point initial */
              v_rupture := measure.continuite || '';;
            ELSEIF v_rupture = '1' THEN
              v_requete := v_requete || '1, ';;  /* point initial */
ELSE
              v_requete := v_requete || '2, ';;  /* point continu */
END IF;;
            /* RAISE NOTICE 'CB d_j : %', d_j;; */
            v_rupture := measure.continuite || '';;
            /* RAISE NOTICE 'CB measure.continuite : %', measure.continuite;; */
            v_requete := v_requete || 'null, ';;  /* codeReponsableValidation */
            v_requete := v_requete || 'null, ';;  /* codeGestionnaire */
            v_requete := v_requete || 'null, ';;  /* codeProducteur */
            v_requete := v_requete || 'null, ';;  /* jobExecutionId */
            v_requete := v_requete || 'null, ';;  /* dateCreation */
            v_requete := v_requete || 'null, ';;  /* dateMaj */
            v_requete := v_requete || 'null ';;  /* loginMaj */
            v_requete := v_requete || ')';;
BEGIN
              IF v_requete is not null THEN
                EXECUTE v_requete;;
                d_j := d_j + 1;;
ELSE
                INSERT INTO ebonneau_logs VALUES(d_id_job, '[NO REQUEST] Error on STEP 1 on measure valeur2 : ' || measure, v_requete, now(), v_identifiant, p_job_execution_id);;
                d_id_job := d_id_job +1;;
END IF;;
EXCEPTION
              WHEN undefined_table THEN
                /*RAISE NOTICE '[UNDEFINED TABLE] Error on STEP 1 during table insert : %', v_identifiant;;*/
                INSERT INTO ebonneau_logs VALUES(d_id_job, '[UNDEFINED TABLE] Error on STEP 1 during table insert valeur2 ', v_requete, now(), v_identifiant, p_job_execution_id);;
                d_id_job := d_id_job +1;;
WHEN OTHERS THEN
                /*RAISE NOTICE '[OTHERS] Error % on STEP 1 during table insert : %', SQLERRM, v_identifiant;;*/
                INSERT INTO ebonneau_logs VALUES(d_id_job, '[OTHERS] Error '|| SQLERRM ||' on STEP 1 during table insert valeur2', v_requete, now(), v_identifiant, p_job_execution_id);;
                d_id_job := d_id_job +1;;
END;;
END IF;;

          IF measure.valeur3 is not null THEN
            -- VALEUR3
            v_requete := 'insert into chroniques_pluvio values ';;
            v_requete := v_requete || '(';;
            v_requete := v_requete || i_codepluviometre || ', ';;
            /* RAISE NOTICE 'CB i_codepluviometre : %', i_codepluviometre;; */
            v_requete := v_requete || '6, ';; /* valeur3 */
            IF measure.datecreation is null OR measure.heure is null THEN
              v_requete := v_requete || 'null, ';;
ELSE
              v_dateEtHeure := to_char(measure.datecreation, 'DD/MM/YYYY') ||' '||to_char(measure.heure, 'HH24:MI:SS');;
END IF;;
            v_requete := v_requete || '''' || v_dateEtHeure || ''', ';;
            /* RAISE NOTICE 'CB datecreation : %', v_dateEtHeure;; */
            v_requete := v_requete || measure.etp || ', ';;
            v_requete := v_requete || 'null, ';; /* valeurmesurecorrigée */
            IF measure.modeobtention = 'A' THEN
              v_requete := v_requete || '1, ';;  /* mode mesure */
            ELSEIF measure.modeobtention = 'M' THEN
              v_requete := v_requete || '2, ';;  /* mode mesure */
ELSE
              v_requete := v_requete || '0, ';;  /* mode mesure */
END IF;;
            /* RAISE NOTICE 'CB measure.modeobtention : %', measure.modeobtention;; */
            IF measure.validite = '' THEN
              v_requete := v_requete || '1, ';; /* statutMesure brute */
ELSE
              v_requete := v_requete || measure.validite || ', ';; /* statutMesure */
END IF;;
            /* RAISE NOTICE 'CB measure.validite : %', measure.validite;; */
            v_requete := v_requete || '4, ';; /* qualificationMesure non qualifiée */
            /* RAISE NOTICE 'CB measure.qualification : %', measure.qualification;; */
            v_requete := v_requete || 'null, ';;   /* natureMesure non renseignée */
            /* RAISE NOTICE 'CB measure.naturemesure : %', measure.naturemesure;; */
            IF d_j = 0 THEN
              v_requete := v_requete || '1, ';;  /* point initial */
              v_rupture := measure.continuite || '';;
            ELSEIF v_rupture = '1' THEN
              v_requete := v_requete || '1, ';;  /* point initial */
ELSE
              v_requete := v_requete || '2, ';;  /* point continu */
END IF;;
            /* RAISE NOTICE 'CB d_j : %', d_j;; */
            v_rupture := measure.continuite || '';;
            /* RAISE NOTICE 'CB measure.continuite : %', measure.continuite;; */
            v_requete := v_requete || 'null, ';;  /* codeReponsableValidation */
            v_requete := v_requete || 'null, ';;  /* codeGestionnaire */
            v_requete := v_requete || 'null, ';;  /* codeProducteur */
            v_requete := v_requete || 'null, ';;  /* jobExecutionId */
            v_requete := v_requete || 'null, ';;  /* dateCreation */
            v_requete := v_requete || 'null, ';;  /* dateMaj */
            v_requete := v_requete || 'null ';;  /* loginMaj */
            v_requete := v_requete || ')';;
BEGIN
              IF v_requete is not null THEN
                EXECUTE v_requete;;
                d_j := d_j + 1;;
ELSE
                INSERT INTO ebonneau_logs VALUES(d_id_job, '[NO REQUEST] Error on STEP 1 on measure valeur3 : ' || measure, v_requete, now(), v_identifiant, p_job_execution_id);;
                d_id_job := d_id_job +1;;
END IF;;
EXCEPTION
              WHEN undefined_table THEN
                /*RAISE NOTICE '[UNDEFINED TABLE] Error on STEP 1 during table insert : %', v_identifiant;;*/
                INSERT INTO ebonneau_logs VALUES(d_id_job, '[UNDEFINED TABLE] Error on STEP 1 during table insert valeur3 ', v_requete, now(), v_identifiant, p_job_execution_id);;
                d_id_job := d_id_job +1;;
WHEN OTHERS THEN
                /*RAISE NOTICE '[OTHERS] Error % on STEP 1 during table insert : %', SQLERRM, v_identifiant;;*/
                INSERT INTO ebonneau_logs VALUES(d_id_job, '[OTHERS] Error '|| SQLERRM ||' on STEP 1 during table insert valeur3', v_requete, now(), v_identifiant, p_job_execution_id);;
                d_id_job := d_id_job +1;;
END;;
END IF;;

END LOOP;;
        d_count_ch := d_j;;
        /*RAISE NOTICE 'Nombre de données dans chronique : %', d_count_ch;;*/
INSERT INTO ebonneau_logs VALUES(d_id_job, 'Nombre de données dans chronique : '|| d_count_ch, null, now(), v_identifiant, p_job_execution_id);;
d_id_job := d_id_job +1;;
CLOSE my_cursor_measures;;
END IF;;

      /*RAISE NOTICE 'ETAPE 2 : Mise à jour des intervenants';;*/
INSERT INTO ebonneau_logs VALUES(d_id_job, 'ETAPE 2 : Mise à jour des intervenants', null, now(), v_identifiant, p_job_execution_id);;
d_id_job := d_id_job +1;;

UPDATE chroniques_pluvio
set coderesponsablevalidation = p_codegestionnaire,
    codegestionnaire = p_codegestionnaire,
    codeproducteur = p_codegestionnaire,
    loginmaj = p_loginmaj,
    datemaj= now()
where codepluviometre = i_codepluviometre;;

GET DIAGNOSTICS d_count_intervenant := ROW_COUNT ;;
RAISE NOTICE 'Nombre des intervenants mis à jour dans chroniques_pluvio : %', d_count_intervenant;;
INSERT INTO ebonneau_logs VALUES(d_id_job, 'Nombre des intervenants mis à jour dans chroniques_pluvio : ' ||d_count_intervenant, null, now(), v_identifiant, p_job_execution_id);;
d_id_job := d_id_job +1;;
      d_count_intervenant := 0;;

      RAISE NOTICE 'ETAPE 3 : Journalisation de la bascule';;
INSERT INTO ebonneau_logs VALUES(d_id_job, 'ETAPE 3 : Journalisation de la bascule', null, now(), v_identifiant, p_job_execution_id);;
d_id_job := d_id_job +1;;

INSERT INTO journal VALUES (now(), 0, 'Migration chroniques pluvio', p_loginmaj, null, null, 'SIP', v_identifiant);;
RAISE NOTICE 'Station traitée : %', v_identifiant;;
INSERT INTO ebonneau_logs VALUES(d_id_job, 'Station traitée : ', null, now(), v_identifiant, p_job_execution_id);;
d_id_job := d_id_job +1;;

      d_count := d_count + 1;;

END IF;;

END LOOP;;
CLOSE my_cursor_stations;;
/* RAISE NOTICE 'Nombre de stations traitées  : %', d_count;; */
INSERT INTO ebonneau_logs VALUES(d_id_job, 'Nombre de stations traitées  : '||d_count, null, now(), v_identifiant, p_job_execution_id);;
RETURN d_count;;
END;;
$$


# --- !Downs
