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

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

DELIMITER $$

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

  -- ---- GESTION C68 ----------------
  INSERT INTO tbl_sge_request (ID_DB_FK_PRM, nom_PRM, Type_Requete, SENS, Date_Request_Appros, ID_FK_MANDAT)
    SELECT
      *
    FROM (SELECT
        prm.ID AS PRM_ID,
        prm.Id_PRM_GRD,
        'C68',
        'SOUTIRAGE',
        NOW(),
        NULL
      FROM tbl_gpt_societe_site_prm prm

      WHERE prm.Soutirage = 1
      AND (prm.flag_desactif IS NULL
      OR prm.flag_desactif = 0)
      AND (prm.date_desactif IS NULL
      OR prm.date_desactif > NOW())
      AND prm.Autorise_requete_SGE) AS result;

  -- OU utilisation de la date de fin du mandat
  INSERT INTO tbl_sge_request (ID_DB_FK_PRM, nom_PRM, Type_Requete, SENS, Date_Request_Appros, ID_FK_MANDAT)
    SELECT
      *
    FROM (SELECT
        prm.ID AS PRM_ID,
        prm.Id_PRM_GRD,
        'C68',
        'INJECTION',
        NOW(),
        NULL
      FROM tbl_gpt_societe_site_prm prm

      WHERE prm.Injection = 1
      AND (prm.flag_desactif IS NULL
      OR prm.flag_desactif = 0)
      AND (prm.date_desactif IS NULL
      OR prm.date_desactif > NOW())
      AND prm.Autorise_requete_SGE = TRUE) AS result;


  -- ---- GESTION R67 ----------------

  INSERT INTO tbl_sge_request (ID_DB_FK_PRM, nom_PRM, Type_Requete, SENS, Date_Request_Appros, Date_Request_Debut, ID_FK_MANDAT)
    SELECT
      *
    FROM (SELECT
        prm.ID AS PRM_ID,
        prm.Id_PRM_GRD,
        'R67',
        'SOUTIRAGE',
        NOW(),
        mesures.datetime_R67,
        NULL
      FROM tbl_gpt_societe_site_prm prm
        INNER JOIN tbl_gpt_societe_site_prm_derniere_mesures mesures
          -- Migrer le IdPRM varcher vers ID technique en base -> INT !
          ON (prm.id = mesures.ID_DB_PRM
          OR prm.idPrm = mesures.idPRM)

      WHERE prm.Soutirage = 1
      AND (prm.flag_desactif IS NULL
      OR prm.flag_desactif = 0)
      AND (prm.date_desactif IS NULL
      OR prm.date_desactif > NOW())
      AND prm.Autorise_requete_SGE = TRUE) AS result;

  INSERT INTO tbl_sge_request (ID_DB_FK_PRM, nom_PRM, Type_Requete, SENS, Date_Request_Appros, Date_Request_Debut, ID_FK_MANDAT)
    SELECT
      *
    FROM (SELECT
        prm.ID AS PRM_ID,
        prm.Id_PRM_GRD,
        'R67',
        'INJECTION',
        NOW(),
        mesures.datetime_R67,
        NULL
      FROM tbl_gpt_societe_site_prm prm
        INNER JOIN tbl_gpt_societe_site_prm_derniere_mesures mesures
          -- Migrer le IdPRM varcher vers ID technique en base -> INT !
          ON (prm.id = mesures.ID_DB_PRM
          OR prm.idPrm = mesures.idPRM)

      WHERE prm.Injection = 1
      AND (prm.flag_desactif IS NULL
      OR prm.flag_desactif = 0)
      AND (prm.date_desactif IS NULL
      OR prm.date_desactif > NOW())
      AND prm.Autorise_requete_SGE = TRUE) AS result;

  -- ---- GESTION R63 ----------------


  INSERT INTO tbl_sge_request (ID_DB_FK_PRM, nom_PRM, Type_Requete, SENS, Date_Request_Appros, Date_Request_Debut, ID_FK_MANDAT)
    SELECT
      *
    FROM (SELECT
        prm.ID AS PRM_ID,
        prm.Id_PRM_GRD,
        'R63',
        'SOUTIRAGE',
        NOW(),
        mesures.datetime_R63,
        NULL
      FROM tbl_gpt_societe_site_prm prm
        INNER JOIN tbl_gpt_societe_site_prm_derniere_mesures mesures
          -- Migrer le IdPRM varcher vers ID technique en base -> INT !
          ON (prm.id = mesures.ID_DB_PRM
          OR prm.idPrm = mesures.idPRM)

      WHERE prm.Soutirage = 1
      AND (prm.flag_desactif IS NULL
      OR prm.flag_desactif = 0)
      AND (prm.date_desactif IS NULL
      OR prm.date_desactif > NOW())
      AND prm.Autorise_requete_SGE = TRUE) AS result;

  INSERT INTO tbl_sge_request (ID_DB_FK_PRM, nom_PRM, Type_Requete, SENS, Date_Request_Appros, Date_Request_Debut, ID_FK_MANDAT)
    SELECT
      *
    FROM (SELECT
        prm.ID AS PRM_ID,
        prm.Id_PRM_GRD,
        'R63',
        'INJECTION',
        NOW(),
        mesures.datetime_R63,
        NULL
      FROM tbl_gpt_societe_site_prm prm
        INNER JOIN tbl_gpt_societe_site_prm_derniere_mesures mesures
          -- Migrer le IdPRM varcher vers ID technique en base -> INT !
          ON (prm.id = mesures.ID_DB_PRM
          OR prm.idPrm = mesures.idPRM)

      WHERE prm.Injection = 1
      AND (prm.flag_desactif IS NULL
      OR prm.flag_desactif = 0)
      AND (prm.date_desactif IS NULL
      OR prm.date_desactif > NOW())
      AND prm.Autorise_requete_SGE = TRUE) AS result;
END
$$

DELIMITER ;