/**
 * @author ebo
 * @date 25/04/2022
 * @version 1.0.0
 * @project SIEau
 * @table chroniques_brutes
 */
# --- !Ups
update chroniques_brutes set cotechronique = 2,	loginmaj = 'MAJ_COTE', datemaj = '2022-04-23' where typeid = -1 and valeurmesure != valeurngf and cotechronique = 1;

CREATE OR REPLACE FUNCTION public.getallpiezomeasures(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 $function$
DECLARE
  my_cursor_measures 				      REFCURSOR;;
  my_cursor_measures_brutes 		  REFCURSOR;;
  my_cursor_measures_datas 			  REFCURSOR;;
  my_cursor_measures_datas_comp		REFCURSOR;;
  my_cursor_measures_prel         REFCURSOR;;
  my_cursor_stations              REFCURSOR;;
  my_cursor_reperes               REFCURSOR;;
  measure 							          RECORD;;
  measure_brute						        RECORD;;
  measure_data						        RECORD;;
  measure_data_comp					      RECORD;;
  measure_prel                    RECORD;;
  repere                          RECORD;;
  v_requete 						          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;;
  d_altitude 						          double precision;;
  d_profondeur 						        double precision;;
  v_codebss 						          VARCHAR (20);;
  d_ngf 							            double precision;;
  d_j 								            double precision;;
  d_repere							          double precision;;
  t_dates_changement_heure 			  VARCHAR[21];;
  i_codePiezometre                integer;;
  v_requete_filtre    				    VARCHAR (2000);;
  v_rupture                       VARCHAR (100);;
  d_systemealti                   double precision;;
  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 codepiezometre FROM piezometres WHERE codepiezometre = ANY(p_stationIds);;
  ELSE
    IF p_filtreId is not null THEN
      SELECT selectionfiltre INTO v_requete_filtre FROM utilisateurs_listes
      WHERE module = 'SIES'
        AND codeliste = p_filtreId;;
      OPEN my_cursor_stations FOR EXECUTE v_requete_filtre;;
    ELSE
      OPEN my_cursor_stations FOR SELECT codepiezometre FROM piezometres;;
    END IF;;
  END IF;;

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

    /* Récupération du codebss de la station */
    SELECT codebss INTO v_codebss FROM piezometres
    WHERE codepiezometre = i_codePiezometre;;

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

    d_id_job := d_id_job +1;;

    IF v_codebss <> '' 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_codebss, p_job_execution_id);;
      d_id_job := d_id_job +1;;

      DELETE FROM chroniques_brutes WHERE codepiezometre=i_codePiezometre;;
      DELETE FROM chroniques_brutes_suppr WHERE codepiezometre=i_codePiezometre;;
      DELETE FROM chroniques WHERE codepiezometre=i_codePiezometre;;

      /*RAISE NOTICE 'Récupération du Z du dernier repère pour calculer les NGF par défaut';;*/
      INSERT INTO ebonneau_logs VALUES(d_id_job, 'Récupération du Z du dernier repère pour calculer les NGF par défaut', null, now(), v_codebss, p_job_execution_id);;
      d_id_job := d_id_job +1;;

      d_altitude := 0;;
      SELECT altitude INTO d_altitude FROM piezometres
      WHERE codepiezometre = i_codePiezometre;;

      /*RAISE NOTICE 'ETAPE 1: Intégration des MAX journaliers';;*/
      INSERT INTO ebonneau_logs VALUES(d_id_job, 'ETAPE 1: Intégration des MAX journaliers', null, now(), v_codebss, p_job_execution_id);;
      d_id_job := d_id_job +1;;

      v_requete := 'SELECT datecreation, heure, cast(valeur as double precision) as valeur, ';;
      v_requete := v_requete || ' continuite, modeobtention, cast(precision as double precision) as precision, validite, qualification, naturemesure';;
      v_requete := v_requete || ' from pz_' || v_codebss || ' 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 insert : %', v_codebss;;*/
          INSERT INTO ebonneau_logs VALUES(d_id_job, '[UNDEFINED TABLE] Error on STEP 1 on getting data', v_requete, now(), v_codebss, 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 insert : %', SQLERRM, v_codebss;;*/
          INSERT INTO ebonneau_logs VALUES(d_id_job, '[OTHERS] Error '|| SQLERRM ||' on STEP 1 on getting data', v_requete, now(), v_codebss, 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;;

          d_profondeur := measure.valeur * -1;;
          d_ngf := (d_altitude - d_profondeur);;

          v_requete := 'insert into chroniques_brutes values ';;
          v_requete := v_requete || '(';;
          v_requete := v_requete || i_codePiezometre || ', ';;
          /* RAISE NOTICE 'CB i_codePiezometre : %', i_codePiezometre;; */
          v_requete := v_requete || '-1, ';; /* brute */
          IF measure.datecreation is null OR measure.heure is null THEN
            v_requete := v_requete || 'null, ';;
          ELSE
            v_dateEtHeure := 'to_date(''' || to_char(measure.datecreation, 'DD/MM/YYYY') ||' '||to_char(measure.heure, 'HH24:MI:SS') || ''',''DD/MM/YYYY HH24:MI:SS'')';;
          END IF;;
          v_requete := v_requete || v_dateEtHeure || ', ';;
          /* RAISE NOTICE 'CB datecreation : %', v_dateEtHeure;; */
          IF d_profondeur is null THEN
            v_requete := v_requete || 'null, ';;
          ELSE
            v_requete := v_requete || d_profondeur || ', ';;
          END IF;;
          /* RAISE NOTICE 'CB d_profondeur : %', d_profondeur;; */
          IF d_ngf is null THEN
            v_requete := v_requete || 'null, ';;
          ELSE
            v_requete := v_requete || d_ngf || ', ';;
          END IF;;
          /* RAISE NOTICE 'CB d_ngf : %', d_ngf;; */
          v_requete := v_requete || '2, ';; /* mode de mesure 1 = NGF, 2 = profondeur */
          v_requete := v_requete || 'null, ';; /* valeurcorrigée */
          v_requete := v_requete || 'null, ';; /* ngf corrigée */
          IF measure.modeobtention = 'A' THEN
            v_requete := v_requete || '1, ';;  /* mode obtention */
          ELSEIF measure.modeobtention = 'M' THEN
            v_requete := v_requete || '2, ';;  /* mode obtention */
          ELSE
            v_requete := v_requete || '0, ';;  /* mode obtention */
          END IF;;
          /* RAISE NOTICE 'CB measure.modeobtention : %', measure.modeobtention;; */
          IF measure.qualification is null OR 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;; */
          IF measure.qualification is null OR measure.qualification = '' THEN
            v_requete := v_requete || '4, ';; /* qualificationMesure non qualifiée */
          ELSE
            v_requete := v_requete || measure.qualification || ', ';; /* qualificationMesure */
          END IF;;
          /* RAISE NOTICE 'CB measure.qualification : %', measure.qualification;; */
          IF measure.naturemesure is null OR measure.naturemesure = '' THEN
            v_requete := v_requete || 'null, ';;   /* natureMesure non renseignée */
          ELSE
            v_requete := v_requete || '''' || measure.naturemesure || ''', ';; /* natureMesure */
          END IF;;
          /* 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, ';;  /* referenceAltimetrique */
          v_requete := v_requete || 'null, ';;  /* repere */
          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 : ' || measure, v_requete, now(), v_codebss, 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_codebss;;*/
              INSERT INTO ebonneau_logs VALUES(d_id_job, '[UNDEFINED TABLE] Error on STEP 1 during table insert', v_requete, now(), v_codebss, 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_codebss;;*/
              INSERT INTO ebonneau_logs VALUES(d_id_job, '[OTHERS] Error '|| SQLERRM ||' on STEP 1 during table insert', v_requete, now(), v_codebss, p_job_execution_id);;
              d_id_job := d_id_job +1;;
          END;;

        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_codebss, p_job_execution_id);;
        d_id_job := d_id_job +1;;
        CLOSE my_cursor_measures;;
      END IF;;

      /*RAISE NOTICE 'ETAPE 2 : Bascule des données brutes en excluant les valeurs déjà intégrées avec les MAX';;*/
      INSERT INTO ebonneau_logs VALUES(d_id_job, 'ETAPE 2 : Bascule des données brutes en excluant les valeurs déjà intégrées avec les MAX', null, now(), v_codebss, p_job_execution_id);;
      d_id_job := d_id_job +1;;

      v_requete := 'SELECT datecreation, heure, cast(valeur as double precision) as valeur, ';;
      v_requete := v_requete || ' continuite, modeobtention, cast(precision as double precision) as precision, validite, qualification';;
      v_requete := v_requete || ' FROM pz_' || v_codebss || '_brute where (datecreation, heure) not in ';;
      v_requete := v_requete || '(select datecreation, heure from pz_' || v_codebss || ')';;
      v_requete := v_requete || ' AND (to_char(datecreation, ''DD/MM/YYYY'') || '' '' || to_char(heure, ''HH24:MI:SS'')) in (select date from date_a_exclure)';;
      v_requete := v_requete || ' ORDER BY datecreation ASC, heure ASC';;

      /*RAISE NOTICE 'ETAPE 2 : Requete % ', v_requete;;*/
      v_rupture := '0';;
      d_j := 0;;

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

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

          /* Calcul des valeurs pour la table de destination */
          d_profondeur := measure_brute.valeur * -1;;
          d_ngf := (d_altitude - d_profondeur);;

          v_requete := 'insert into chroniques_brutes values ';;
          v_requete := v_requete || '(';;
          v_requete := v_requete || i_codePiezometre || ', ';;
          v_requete := v_requete || '-1, ';; /* brute */
          IF measure_brute.datecreation is null OR measure_brute.heure is null THEN
            v_requete := v_requete || 'null, ';;
          ELSE
            v_dateEtHeure := 'to_date(''' || to_char(measure_brute.datecreation, 'DD/MM/YYYY') ||' '||to_char(measure_brute.heure, 'HH24:MI:SS') || ''',''DD/MM/YYYY HH24:MI:SS'')';;
          END IF;;
          v_requete := v_requete || v_dateEtHeure || ', ';;
          IF d_profondeur is null THEN
            v_requete := v_requete || 'null, ';;
          ELSE
            v_requete := v_requete || d_profondeur || ', ';;
          END IF;;
          IF d_ngf is null THEN
            v_requete := v_requete || 'null, ';;
          ELSE
            v_requete := v_requete || d_ngf || ', ';;
          END IF;;
          v_requete := v_requete || '2, ';; /* mode de mesure 1 = NGF, 2 = profondeur */
          v_requete := v_requete || 'null, ';; /* valeurcorrigée */
          v_requete := v_requete || 'null, ';; /* ngf corrigée */
          v_requete := v_requete || 'null, ';; /* mode mesure */
          v_requete := v_requete || 'null, ';; /* statutMesure brute */
          v_requete := v_requete || 'null, ';; /* 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_brute.continuite;;
          ELSE
            IF v_rupture = '1' THEN
              v_requete := v_requete || '1, ';; /* point initial */
            ELSE
              v_requete := v_requete || '2, ';; /* point continu */
            END IF;;
            v_rupture := measure_brute.continuite;;
          END IF;;
          v_requete := v_requete || 'null, ';; /* referenceAltimetrique */
          v_requete := v_requete || 'null, ';; /* repere */
          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 2 on measure : ' || measure_brute, v_requete, now(), v_codebss, 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 2 during table insert : %', v_codebss;;*/
              INSERT INTO ebonneau_logs VALUES(d_id_job, '[UNDEFINED TABLE] Error on STEP 2 during table insert', v_requete, now(), v_codebss, p_job_execution_id);;
              d_id_job := d_id_job +1;;
            WHEN OTHERS THEN
              /*RAISE NOTICE '[OTHERS] Error % on STEP 2 during table insert : %', SQLERRM, v_codebss;;*/
              INSERT INTO ebonneau_logs VALUES(d_id_job, '[OTHERS] Error '|| SQLERRM ||' on STEP 2 during table insert', v_requete, now(), v_codebss, p_job_execution_id);;
              d_id_job := d_id_job +1;;
          END;;

        END LOOP;;
        d_count_chb := d_j;;

        /*RAISE NOTICE 'Nombre de données dans chroniques_brutes : %', d_count_chb;;*/
        INSERT INTO ebonneau_logs VALUES(d_id_job, 'Nombre de données dans chroniques_brutes : ' ||d_count_chb, null, now(), v_codebss, p_job_execution_id);;
        d_id_job := d_id_job +1;;
        CLOSE my_cursor_measures_brutes;;
      END IF;;

      /*RAISE NOTICE 'ETAPE 3 : Gestion des données modifiées et supprimées';;*/
      INSERT INTO ebonneau_logs VALUES(d_id_job, 'ETAPE 3 : Gestion des données modifiées et supprimées', null, now(), v_codebss, p_job_execution_id);;
      d_id_job := d_id_job +1;;

      v_requete := 'SELECT datecreation, heure, cast(valeur as double precision) as valeur, continuite, modeobtention, ';;
      v_requete := v_requete || ' cast(precision as double precision) as precision, validite, qualification, type, suppression,';;
      v_requete := v_requete || ' naturemesure FROM pz_' || v_codebss || '_data WHERE type=''3'' ORDER BY datecreation ASC, heure ASC';;
      v_rupture := '0';;
      d_j := 0;;

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

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

          IF measure_data.datecreation is null OR measure_data.heure is null THEN
            v_dateEtHeure := null;;
          ELSE
            v_dateEtHeure := 'to_date(''' || to_char(measure_data.datecreation, 'DD/MM/YYYY') ||' '||to_char(measure_data.heure, 'HH24:MI:SS') || ''',''DD/MM/YYYY HH24:MI:SS'')';;
          END IF;;

          IF measure_data.suppression <> '' THEN

            /* Si la valeur a été supprimée, on la supprime de la table des brutes pour la basculer dans la table des brutes supprimées */
            /* Calcul des valeurs pour la table de destination */
            d_profondeur := measure_data.valeur * -1;;
            d_ngf := (d_altitude - d_profondeur);;

            v_requete := 'DELETE FROM chroniques_brutes
            WHERE codePiezometre = ' || i_codePiezometre || '
              AND dateMesure =  cast(' || v_dateEtHeure || ' as timestamp)';;
            EXECUTE v_requete;;

            v_requete := 'insert into chroniques_brutes_suppr values ';;
            v_requete := v_requete || '(';;
            v_requete := v_requete || i_codePiezometre || ', ';;
            /*RAISE NOTICE 'i_codePiezometre : %', i_codePiezometre;;*/
            v_requete := v_requete || '-1, ';; /* brute */
            IF v_dateEtHeure is null THEN
              v_requete := v_requete || 'null, ';;
            ELSE
              v_requete := v_requete || v_dateEtHeure || ', ';;
            END IF;;
            IF d_profondeur is null THEN
              v_requete := v_requete || 'null, ';;
            ELSE
              v_requete := v_requete || d_profondeur || ', ';;
            END IF;;
            /*RAISE NOTICE 'Profondeur : %', d_profondeur;;*/
            IF d_ngf is null THEN
              v_requete := v_requete || 'null, ';;
            ELSE
              v_requete := v_requete || d_ngf || ', ';;
            END IF;;
            /*RAISE NOTICE 'NGF : %', d_ngf;;*/
            v_requete := v_requete || '2, ';; /* mode de mesure 1 = NGF, 2 = profondeur */
            v_requete := v_requete || 'null, ';; /* valeurcorrigée */
            v_requete := v_requete || 'null, ';; /* ngf corrigée */
            v_requete := v_requete || 'null, ';; /* mode mesure */
            v_requete := v_requete || 'null, ';; /* statutMesure brute */
            v_requete := v_requete || 'null, ';; /* 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_data.continuite;;
            ELSE
              IF v_rupture = '1' THEN
                v_requete := v_requete || '1, ';; /* point initial */
              ELSE
                v_requete := v_requete || '2, ';; /* point continu */
              END IF;;
              v_rupture := measure_data.continuite;;
            END IF;;
            /*RAISE NOTICE 'v_rupture : %', measure_data.continuite;;*/
            v_requete := v_requete || 'null, ';; /* referenceAltimetrique */
            v_requete := v_requete || 'null, ';; /* repere */
            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 3 on measure : ' || measure_data, v_requete, now(), v_codebss, 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 3 during table insert : %', v_codebss;;*/
                INSERT INTO ebonneau_logs VALUES(d_id_job, '[UNDEFINED TABLE] Error on STEP 3 during table insert', v_requete, now(), v_codebss, p_job_execution_id);;
                d_id_job := d_id_job +1;;
              WHEN OTHERS THEN
                /*RAISE NOTICE '[OTHERS] Error % on STEP 3 during table insert : %', SQLERRM, v_codebss;;*/
                INSERT INTO ebonneau_logs VALUES(d_id_job, '[OTHERS] Error '|| SQLERRM ||' on STEP 3 during table insert', v_requete, now(), v_codebss, p_job_execution_id);;
                d_id_job := d_id_job +1;;
            END;;

          ELSE
            d_profondeur := measure_data.valeur * -1;;
            d_ngf := (d_altitude - d_profondeur);;

            /* Si la valeur a été modifiée, on met à jour les colonnes de modifications */
            IF v_dateEtHeure is not null THEN
              v_requete := 'UPDATE chroniques_brutes
              SET valeurmesurecorrigee = ' || d_profondeur || ',
                  valeurngfcorrigee = ' || d_ngf || '
              WHERE codePiezometre = ' || i_codePiezometre || '
                AND dateMesure =  cast(' || v_dateEtHeure || ' as timestamp)';;
              EXECUTE v_requete;;
            END IF;;

          END IF;;

          d_j := d_j + 1;;

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

      /*RAISE NOTICE 'ETAPE 4: Gestion des données complémentaires';;*/
      INSERT INTO ebonneau_logs VALUES(d_id_job, 'ETAPE 4: Gestion des données complémentaires', null, now(), v_codebss, p_job_execution_id);;
      d_id_job := d_id_job +1;;

      v_requete := 'SELECT datecreation, heure, cast(valeur as double precision) as valeur, continuite, modeobtention, ';;
      v_requete := v_requete || ' cast(precision as double precision) as precision, validite, qualification, type, suppression, naturemesure';;
      v_requete := v_requete || ' FROM pz_' || v_codebss || '_data WHERE type>''3'' or type = ''-2'' ORDER BY datecreation ASC, heure ASC';;
      v_rupture := '0';;
      d_j := 0;;

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

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

          d_profondeur := measure_data_comp.valeur;;

          v_requete := 'insert into chroniques_brutes values ';;
          v_requete := v_requete || '(';;
          v_requete := v_requete || i_codePiezometre || ', ';;
          v_requete := v_requete || measure_data_comp.type || ', ';; /* type données */

          IF measure_data_comp.datecreation is null OR measure_data_comp.heure is null THEN
            v_dateEtHeure := null;;
            v_requete := v_requete || 'null, ';;
          ELSE
            v_dateEtHeure := 'to_date(''' || to_char(measure_data_comp.datecreation, 'DD/MM/YYYY') ||' '||to_char(measure_data_comp.heure, 'HH24:MI:SS') || ''',''DD/MM/YYYY HH24:MI:SS'')';;
            v_requete := v_requete || v_dateEtHeure || ', ';;
          END IF;;
          IF d_profondeur is null THEN
            v_requete := v_requete || 'null, ';;
          ELSE
            v_requete := v_requete || d_profondeur || ', ';;
          END IF;;
          v_requete := v_requete || 'null, ';; /* ngf */
          v_requete := v_requete || 'null, ';; /* cotechronique */
          v_requete := v_requete || 'null, ';; /* valeur corrigée */
          v_requete := v_requete || 'null, ';; /* valeur ngf corrigée */
          v_requete := v_requete || 'null, ';; /* mode mesure */
          v_requete := v_requete || 'null, ';; /* statut mesure */
          v_requete := v_requete || 'null, ';; /* qualification mesure */
          v_requete := v_requete || 'null, ';; /* nature mesure */
          IF d_j = 0 THEN
            v_requete := v_requete || '1, ';;  /* point initial */
          ELSE
            v_requete := v_requete || '2, ';; /* point continu */
          END IF;;
          v_requete := v_requete || 'null, ';; /* referenceAltimetrique */
          v_requete := v_requete || 'null, ';; /* repere */
          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 4 on measure : ' || measure_data_comp, v_requete, now(), v_codebss, 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 4 during table insert : %', v_codebss;;*/
              INSERT INTO ebonneau_logs VALUES(d_id_job, '[UNDEFINED TABLE] Error on STEP 4 during table insert', v_requete, now(), v_codebss, p_job_execution_id);;
              d_id_job := d_id_job +1;;
            WHEN OTHERS THEN
              /*RAISE NOTICE '[OTHERS] Error % on STEP 4 during table insert : %', SQLERRM , v_codebss;;*/
              INSERT INTO ebonneau_logs VALUES(d_id_job, '[OTHERS] Error '|| SQLERRM ||' on STEP 4 during table insert', v_requete, now(), v_codebss, p_job_execution_id);;
              d_id_job := d_id_job +1;;
          END;;

        END LOOP;;
        d_count_chbc := d_j;;
        /*RAISE NOTICE 'Nombre de données complémentaires dans chroniques : %', d_count_chbc;;*/
        INSERT INTO ebonneau_logs VALUES(d_id_job, 'Nombre de données complémentaires dans chroniques', null, now(), v_codebss, p_job_execution_id);;
        d_id_job := d_id_job +1;;
        CLOSE my_cursor_measures_datas_comp;;
      END IF;;

      /*RAISE NOTICE 'ETAPE 5: Gestion des données prélévements';;*/
      INSERT INTO ebonneau_logs VALUES(d_id_job, 'ETAPE 5: Gestion des prélévements', null, now(), v_codebss, p_job_execution_id);;
      d_id_job := d_id_job +1;;

      v_requete := 'SELECT dateprelevement, heureprelevement, cast(valeur as double precision) as valeur';;
      v_requete := v_requete || ' FROM piezometre_prelevement_' || v_codebss || ' ORDER BY dateprelevement ASC, heureprelevement ASC';;
      d_j := 0;;

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

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

          d_profondeur := measure_prel.valeur;;

          v_requete := 'insert into chroniques_brutes values ';;
          v_requete := v_requete || '(';;
          v_requete := v_requete || i_codePiezometre || ', ';;
          v_requete := v_requete || '-2' || ', ';; /* type données */

          IF measure_prel.dateprelevement is null OR measure_prel.heureprelevement is null THEN
            v_dateEtHeure := null;;
            v_requete := v_requete || 'null, ';;
          ELSE
            v_dateEtHeure := 'to_date(''' || to_char(measure_prel.datecreation, 'DD/MM/YYYY') ||' '||to_char(measure_prel.heure, 'HH24:MI:SS') || ''',''DD/MM/YYYY HH24:MI:SS'')';;
            v_requete := v_requete  || v_dateEtHeure || ', ';;
          END IF;;
          IF d_profondeur is null THEN
            v_requete := v_requete || 'null, ';;
          ELSE
            v_requete := v_requete || d_profondeur || ', ';;
          END IF;;
          v_requete := v_requete || 'null, ';; /* ngf */
          v_requete := v_requete || 'null, ';; /* cotechronique */
          v_requete := v_requete || 'null, ';; /* valeur corrigée */
          v_requete := v_requete || 'null, ';; /* valeur ngf corrigée */
          v_requete := v_requete || 'null, ';; /* mode mesure */
          v_requete := v_requete || 'null, ';; /* statut mesure */
          v_requete := v_requete || 'null, ';; /* qualification mesure */
          v_requete := v_requete || 'null, ';; /* nature mesure */
          IF d_j = 0 THEN
            v_requete := v_requete || '1, ';;  /* point initial */
          ELSE
            v_requete := v_requete || '2, ';; /* point continu */
          END IF;;
          v_requete := v_requete || 'null, ';; /* referenceAltimetrique */
          v_requete := v_requete || 'null, ';; /* repere */
          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 5 on measure : ' || measure_prel, v_requete, now(), v_codebss, 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 4 during table insert : %', v_codebss;;*/
              INSERT INTO ebonneau_logs VALUES(d_id_job, '[UNDEFINED TABLE] Error on STEP 5 during table insert', v_requete, now(), v_codebss, p_job_execution_id);;
              d_id_job := d_id_job +1;;
            WHEN OTHERS THEN
              /*RAISE NOTICE '[OTHERS] Error % on STEP 4 during table insert : %', SQLERRM , v_codebss;;*/
              INSERT INTO ebonneau_logs VALUES(d_id_job, '[OTHERS] Error '|| SQLERRM ||' on STEP 5 during table insert', v_requete, now(), v_codebss, p_job_execution_id);;
              d_id_job := d_id_job +1;;
          END;;

        END LOOP;;
        d_count_chbc := d_j;;
        /*RAISE NOTICE 'Nombre de données prélévements dans chroniques : %', d_count_chbc;;*/
        INSERT INTO ebonneau_logs VALUES(d_id_job, 'Nombre de données prélévements dans chroniques', null, now(), v_codebss, p_job_execution_id);;
        d_id_job := d_id_job +1;;
        CLOSE my_cursor_measures_prel;;
      END IF;;

      /*RAISE NOTICE 'ETAPE 6 : Mise à jour des repères et des systèmes altimétriques';;*/
      INSERT INTO ebonneau_logs VALUES(d_id_job, 'ETAPE 6 : Mise à jour des repères et des systèmes altimétriques ', null, now(), v_codebss, p_job_execution_id);;
      d_id_job := d_id_job +1;;

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

      OPEN my_cursor_reperes FOR SELECT dateDebut, hauteur, naturesystemealti, datesystemealti FROM reperes
                                 WHERE codePiezometre = i_codePiezometre ORDER BY dateDebut ASC;;
      LOOP
        FETCH my_cursor_reperes into repere;;
        EXIT WHEN NOT FOUND;;

        d_repere = repere.hauteur;;

        UPDATE chroniques_brutes
        SET repere = d_repere
        WHERE codePiezometre = i_codePiezometre
          AND dateMesure >= repere.dateDebut;;

        GET DIAGNOSTICS d_count_repere := ROW_COUNT ;;
        /*RAISE NOTICE 'Nombre de repères mis à jour dans chroniques_brutes : %', d_count_repere;;*/
        INSERT INTO ebonneau_logs VALUES(d_id_job, 'Nombre de repères mis à jour dans chroniques_brutes : ' ||d_count_repere, null, now(), v_codebss, p_job_execution_id);;
        d_id_job := d_id_job +1;;
        d_count_repere := 0;;

        UPDATE chroniques_brutes_suppr
        SET repere = d_repere
        WHERE codePiezometre = i_codePiezometre
          AND dateMesure >= repere.dateDebut;;

        GET DIAGNOSTICS d_count_repere := ROW_COUNT ;;
        /*RAISE NOTICE 'Nombre de repères mis à jour dans chroniques_brutes_suppr : %', d_count_repere;;*/
        INSERT INTO ebonneau_logs VALUES(d_id_job, 'Nombre de repères mis à jour dans chroniques_brutes_suppr : ' ||d_count_repere, null, now(), v_codebss, p_job_execution_id);;
        d_id_job := d_id_job +1;;
        d_count_repere := 0;;

        IF repere.naturesystemealti is not null AND repere.datesystemealti is not null THEN

          SELECT altitude INTO d_altitude FROM SYSTEMES_ALTIMETRIQUES
          WHERE codePiezometre = i_codePiezometre
            AND codenature = repere.naturesystemealti
            AND datedebut = repere.datesystemealti;;

          IF d_altitude is not null THEN

            d_systemealti := d_altitude;;

            UPDATE chroniques_brutes
            SET referencealtimetrique = d_systemealti
            WHERE codePiezometre = i_codePiezometre
              AND dateMesure >= repere.dateDebut;;

            GET DIAGNOSTICS d_count_repere := ROW_COUNT ;;
            /*RAISE NOTICE 'Nombre de référence altimétriques à jour dans chroniques_brutes : %', d_count_repere;;*/
            INSERT INTO ebonneau_logs VALUES(d_id_job, 'Nombre de référence altimétriques à jour dans chroniques_brutes : ' ||d_count_repere, null, now(), v_codebss, p_job_execution_id);;
            d_id_job := d_id_job +1;;
            d_count_repere := 0;;

            UPDATE chroniques_brutes_suppr
            SET referencealtimetrique = d_systemealti
            WHERE codePiezometre = i_codePiezometre
              AND dateMesure >= repere.dateDebut;;

            GET DIAGNOSTICS d_count_repere := ROW_COUNT ;;
            /*RAISE NOTICE 'Nombre de référence altimétriques à jour dans chroniques_brutes_suppr : %', d_count_repere;;*/
            INSERT INTO ebonneau_logs VALUES(d_id_job, 'Nombre de référence altimétriques à jour dans chroniques_brutes_suppr : ' ||d_count_repere, null, now(), v_codebss, p_job_execution_id);;
            d_id_job := d_id_job +1;;
            d_count_repere := 0;;

          END IF;;

        END IF;;
        d_j := d_j + 1;;
      END LOOP;;
      d_count_repere := d_j;;
      /*RAISE NOTICE 'Nombre de repères traités : %', d_count_repere;;*/
      INSERT INTO ebonneau_logs VALUES(d_id_job, 'Nombre de repères traités : ' ||d_count_repere, null, now(), v_codebss, p_job_execution_id);;
      d_id_job := d_id_job +1;;
      CLOSE my_cursor_reperes;;

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

      UPDATE chroniques_brutes
      set coderesponsablevalidation = p_codegestionnaire,
          codegestionnaire = p_codegestionnaire,
          codeproducteur = p_codegestionnaire,
          loginmaj = p_loginmaj,
          datemaj= now()
      where codepiezometre = i_codePiezometre;;

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

      UPDATE chroniques_brutes_suppr
      set coderesponsablevalidation = p_codegestionnaire,
          codegestionnaire = p_codegestionnaire,
          codeproducteur = p_codegestionnaire,
          loginmaj = p_loginmaj,
          datemaj = now()
      where codepiezometre = i_codePiezometre;;

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

      UPDATE chroniques
      set coderesponsablevalidation = p_codegestionnaire,
          codegestionnaire = p_codegestionnaire,
          codeproducteur = p_codegestionnaire,
          loginmaj = p_loginmaj,
          datemaj = now()
      where codepiezometre = i_codePiezometre;;

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

      /*RAISE NOTICE 'ETAPE 7 : Journalisation de la bascule';;*/
      INSERT INTO ebonneau_logs VALUES(d_id_job, 'ETAPE 8 : Journalisation de la bascule', null, now(), v_codebss, p_job_execution_id);;
      d_id_job := d_id_job +1;;

      INSERT INTO journal VALUES (now(), 0, 'Migration chroniques', p_loginmaj, null, null, 'SIES', v_codebss);;
      /* RAISE NOTICE 'Station traitée : %', v_codebss;;*/
      INSERT INTO ebonneau_logs VALUES(d_id_job, 'Station traitée : ', null, now(), v_codebss, 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_codebss, p_job_execution_id);;
  RETURN d_count;;
END;;
$function$
;;


# --- !Downs

