<?php
  ini_set('display_errors', 1);
  ini_set('display_startup_errors', 1);
  error_reporting(E_ERROR | E_WARNING | E_PARSE | E_NOTICE);

  $db = "(DESCRIPTION =
            (ADDRESS_LIST =
              (ADDRESS = (PROTOCOL = TCP)(HOST = P-BDD-16)(PORT = 1521))
            )
            (CONNECT_DATA =
              (SERVICE_NAME = SIGCG50)
            )
          )";

  // Connexion au service
  $conn = oci_connect('ELIGIBILITE_R', 'H6nmb0KCwR', $db);

  // Execute query
  function executeQuery($conn, $query) {
    $res = [];

    $stid = oci_parse($conn, $query);

    if (!$stid) {
      $e = oci_error($conn);
      $error['error'] = 'Hum!!! Ssomething went wrong! '.$e;
      return $error;
    }

    $r = oci_execute($stid);

    if (!$r) {
      $e = oci_error($stid);
      $error['error'] = 'Hum!!! Ssomething went wrong! '.$e;
      return $error;
    }

    while ($row = oci_fetch_array($stid, OCI_RETURN_NULLS+OCI_ASSOC)) {
        $res[] = $row;
    }

    return $res;
  }

  // Get parameters
  if($_POST["x"] && $_POST["y"]) {
    $x = floatval($_POST["x"]);
    $y = floatval($_POST["y"]);
  }
  else if($_GET["x"] && $_GET["y"]) {
    $x = floatval($_GET["x"]);
    $y = floatval($_GET["y"]);
  }
  else {
    $x = -1.109669152209201;
    $y = 49.102353729303125;
  }

  // Append X,Y to results
  $results['x'] = $x;
  $results['y'] = $y;

  // Get FTTH results
  $queryFTTH2 = "Select
                  FTTH.Id_Zapm_Partielle,
                  FTTH.PHASE_TRAV,
                  FTTH.DATE_DEB_TRAV_PREV,
                  FTTH.DATE_DEB_TRAV_REEL,
                  FTTH.DATE_OUV_BUL_PRERACCO,
                  FTTH.DATE_FERM_BUL_PRERACCO,
                  FTTH.DATE_ABONNEMENT_PREV,
                  FTTH.DATE_OUV_COMMERCIALISATION
                FROM
                  SIG_ANT.VFTTH_MN_ELIGIBILITE FTTH
                WHERE
                  SDO_CONTAINS(FTTH.GEOMETRY, Mdsys.Sdo_Geometry(2001,4326,Mdsys.Sdo_Point_Type(".$x.", ".$y.", Null), Null, Null)) = 'TRUE'";

  $results['ftth'] = executeQuery($conn, $queryFTTH2);

  // $queryFTTH = "Select FTTH.Id_Zapm_Partielle, FTTH.PHASE_TRAV, FTTH.DATE_OUV_SERVICE_REEL, FTTH.DATE_OUV_SERVICE_PREV, FTTH.DATE_FIN_TRAV_PREV, FTTH.DATE_DEB_ETUDES_PREV From SIG_ANT.FTTH_MN_ELIGIBILITE FTTH WHERE SDO_CONTAINS(FTTH.GEOMETRY, Mdsys.Sdo_Geometry(2001,4326,Mdsys.Sdo_Point_Type(".$x.", ".$y.", Null), Null, Null)) = 'TRUE'";

  // $results['ftth'] = executeQuery($conn, $queryFTTH);

  // Get ADSL results
  $queryADSL = "Select ADSL.ID_ADSL_MTHD , ADSL.CLASSEDEBI, ADSL.CLASSE_LEGENDE  From SIG_ANT.ADSL_MTHD ADSL WHERE SDO_CONTAINS(ADSL.GEOM, Mdsys.Sdo_Geometry(2001,4326,Mdsys.Sdo_Point_Type(".$x.", ".$y.", Null), Null, Null)) = 'TRUE'";
  $results['adsl'] = executeQuery($conn, $queryADSL);

  // Get MIMO results
  $queryMIMO = "Select A.ID_MIMO_ANTC_COUVERTURE_6KM, A.DATE_OUV_SERVICE_REEL, A.DATE_OUV_SERVICE_PREV From SIG_ANT.MIMO_ANTC_COUVERTURE_6KM A  WHERE SDO_CONTAINS(a.GEOM, Mdsys.Sdo_Geometry(2001,4326,Mdsys.Sdo_Point_Type(".$x.", ".$y.", Null), Null, Null)) = 'TRUE'";
  $results['mimo'] = executeQuery($conn, $queryMIMO);

  echo json_encode($results);

  /*
  les requetes pour FTTH et ADSL passent sur des vues
  un boolean dans la view dit si zone ouverte ou pas au preraco
  date d'abonnement previsionel au semestre (ald mois)

  1) regarder adresse.gouv.data
  2) adresse gouville 150 points avec même X, Y
  3) Bug IE11 5 rue des croix, saint come du mont => 50500 ca marche!
  */
?>