--
-- Set character set the client will use to send SQL statements to the server
--

--
-- Set default database
--
USE appros;

DELIMITER $$

--
-- Create procedure `ps_Rafraichir_prm_derniere_mesure`
--
CREATE
DEFINER = 'user_odbc'@'%'
PROCEDURE ps_Rafraichir_prm_derniere_mesure ()
BEGIN


  INSERT INTO tbl_gpt_societe_site_prm_derniere_mesures (idPRM, datetime_R63, QUALIF_R63, TYPE_R63)
    SELECT
      *
    FROM (SELECT
        idPrm,
        MAX(d) AS Max_Horodatage_R63,
        etapeMetier,
        MAX(grandeurMetier) AS Type_R63
      FROM r63
      GROUP BY idPrm,
               etapeMetier) AS R63max
  ON DUPLICATE KEY UPDATE
  datetime_R63 = R63max.Max_Horodatage_R63, QUALIF_R63 = R63max.etapeMetier, TYPE_R63 = R63max.Type_R63;

  INSERT INTO tbl_gpt_societe_site_prm_derniere_mesures (idPRM, datetime_R67, QUALIF_R67, TYPE_R67)
    SELECT
      *
    FROM (SELECT
        idPrm,
        MAX(dateFIn) AS Max_Horodatage_R67,
        MAX(etapeMetier) AS etapeMetier,
        MAX(grandeurMetier) AS Type_R67
      FROM r67
      GROUP BY idPrm) AS R67max
  ON DUPLICATE KEY UPDATE
  datetime_R67 = R67max.Max_Horodatage_R67, QUALIF_R67 = R67max.etapeMetier, TYPE_R67 = R67max.Type_R67;

  -- Obligé de rafraichir l'ID technique de base de données

  UPDATE tbl_gpt_societe_site_prm_derniere_mesures mesures, (SELECT
      id,
      idPrm,
      Soutirage,
      Injection,
      Ref_5,
      flag_desactif,
      date_desactif,
      site.site_NOM
    FROM tbl_gpt_societe_site_prm prm
      INNER JOIN tbl_gpt_societe_site site
        ON prm.ID_SITE = site.ID_SITE) AS prm_et_site
  SET mesures.ID_DB_PRM = prm_et_site.id,
      mesures.prm_SOUTIRAGE = prm_et_site.Soutirage,
      mesures.prm_INJECTION = prm_et_site.Injection,
      mesures.prm_STATUT = prm_et_site.Ref_5,
      mesures.Flag_Desactive = prm_et_site.flag_desactif,
      mesures.Date_Desactive = prm_et_site.date_desactif,
      mesures.Nom_SITE = prm_et_site.site_NOM
  WHERE mesures.idPRM = prm_et_site.idPrm;
END
$$

DELIMITER ;