43,10 → 43,8 |
} |
|
public function recupererStations() { |
// recuperer les informations au niveau de la base de donnees |
$requeteSql = $this->construireRequeteStations(); |
$stations = $this->getBdd()->recupererTous($requeteSql); |
|
$zoomMaxMaillage = Config::get('zoom_maximal_maillage'); |
$seuilMaillage = Config::get('seuil_maillage'); |
$zoom = $this->criteresRecherche->zoom; |
53,14 → 51,11 |
$bbox = $this->criteresRecherche->bbox; |
// TODO: gérer une notion d'échelle plutot que de zoom (pour les clients SIG) |
if (count($stations) > $seuilMaillage && intval($zoom)<= $zoomMaxMaillage) { |
// partitionnement des donnees en mailles |
$maillage = new Maillage($bbox, $zoom); |
$maillage->genererMaillesVides(); |
$maillage->ajouterPoints($stations); |
$stations = $maillage->formaterSortie(); |
} |
|
// mettre en forme les informations au format JSON |
$formateurJSON = new FormateurJson('floradata'); |
$donneesFormatees = $formateurJSON->formaterStations($stations); |
return $donneesFormatees; |
67,12 → 62,9 |
} |
|
public function recupererObservations() { |
// recuperer les informations sur les stations repondant a ces parametres |
$requeteSql = $this->construireRequeteObservations(); |
$observations = $this->getBdd()->recupererTous($requeteSql); |
$nomSite = $this->obtenirNomStation(); |
|
// mettre en forme les informations au format JSON |
$formateurJSON = new FormateurJson('floradata'); |
$donneesFormatees = $formateurJSON->formaterObservations($observations, $nomSite); |
return $donneesFormatees; |
93,21 → 85,27 |
} |
|
private function construireRequeteStations() { |
$bbox = $this->criteresRecherche->bbox; |
$selectTypeSite = |
"IF(". |
"(longitude IS NULL OR latitude IS NULL) ". |
"OR (longitude=0 AND latitude=0) ". |
"OR (longitude=999.99999 AND latitude=999.99999)". |
"OR (mots_cles_texte LIKE '%sensible%') ". |
"OR NOT (". |
"longitude BETWEEN ".$bbox['ouest']." AND ".$bbox['est']." ". |
"AND latitude BETWEEN ".$bbox['sud']." AND ".$bbox['nord']. |
")". |
", 'COMMUNE', 'STATION'". |
")"; |
$requete = |
'SELECT ce_zone_geo, zone_geo, station, longitude, latitude, nom AS "nom_commune",'. |
'wgs84_longitude AS "lng_commune", wgs84_latitude AS "lat_commune", '. |
$selectTypeSite.' AS "type_site" '. |
'FROM cel_obs LEFT JOIN cel_zones_geo cz ON ce_zone_geo=id_zone_geo '. |
'WHERE transmission=1 '. |
"SELECT COUNT(id_observation) AS observations, ce_zone_geo, zone_geo, station, ". |
"longitude, latitude, nom AS nom_commune,wgs84_longitude AS lng_commune, ". |
"wgs84_latitude AS lat_commune, ".$selectTypeSite." AS type_site ". |
"FROM cel_obs LEFT JOIN cel_zones_geo cz ON ce_zone_geo=id_zone_geo ". |
"WHERE transmission=1 ". |
$this->construireWhereDepartement().' '. |
$this->construireWhereAuteur().' '. |
$this->construireWhereDate().' '. |
$this->construireWhereReferentiel().' '. |
$this->construireWhereTaxon().' '. |
$this->construireWhereCoordonneesBbox().' '. |
118,11 → 116,12 |
private function construireRequeteObservations() { |
$requete = |
"SELECT id_observation AS id_obs, nom_ret_nn AS nn, nom_ret AS nomSci, ". |
"Date(date_transmission) AS date, milieu AS lieu, nom_referentiel, ". |
"Date(date_observation) AS date, milieu AS lieu, nom_referentiel, ". |
"Concat(prenom_utilisateur, ' ', nom_utilisateur) AS observateur, ce_utilisateur AS observateurId ". |
"FROM cel_obs WHERE transmission=1 ". |
$this->construireWhereAuteur().' '. |
$this->construireWhereReferentiel().' '. |
$this->construireWhereDate().' '. |
$this->construireWhereTaxon().' '. |
$this->construireWhereCoordonneesPoint().' '. |
"ORDER BY nom_ret, date, observateur"; |
129,22 → 128,55 |
return $requete; |
} |
|
private function construireWhereTaxon() { |
protected function construireWhereTaxon() { |
$sql = ''; |
if (isset($this->criteresRecherche->taxon)) { |
$taxon = $this->criteresRecherche->taxon; |
$criteres = "nom_ret LIKE ".$this->getBdd()->proteger($taxon['nom']."%"); |
|
$referentiel = new Referentiel($this->criteresRecherche->referentiel, $this->criteresRecherche->taxon); |
$sousTaxons = $referentiel->recupererSousTaxons(); |
foreach ($sousTaxons as $sousTaxon) { |
$criteres .= " OR nom_ret LIKE ".$this->getBdd()->proteger($taxon['nom']."%"); |
$taxons = $this->criteresRecherche->taxon; |
$criteres = array(); |
foreach ($taxons as $taxon) { |
$nomRang = $this->getNomRang($taxon); |
if ($nomRang == 'genre') { |
$criteres[] = "famille=".$this->getBdd()->proteger($taxon['nom']); |
} else { |
$criteres[] = "nom_ret LIKE ".$this->getBdd()->proteger($taxon['nom']."%"); |
if ($nomRang == 'espece') { |
$criteres = array_merge($criteres, $this->concatenerTaxonsSousEspeces($taxon)); |
} |
} |
} |
$sql = "AND ($criteres)"; |
$sql = "AND (".implode(' OR ',array_unique($criteres)).")"; |
} |
return $sql; |
} |
|
protected function getNomRang($taxon) { |
$nomsRangs = array('famille', 'genre', 'espece', 'sous_espece'); |
for ($index = 0; $index < count($nomsRangs) |
&& Config::get("rang.".$nomsRangs[$index]) != $taxon['rang']; $index ++); |
$position = $index == count($nomsRangs) ? count($nomsRangs)-1 : $index; |
return $nomsRangs[$position]; |
} |
|
protected function concatenerTaxonsSousEspeces($taxon) { |
$referentiel = new Referentiel($this->criteresRecherche->referentiel, $taxon); |
$sousTaxons = $referentiel->recupererTaxonsSousEspeces(); |
$criteres = array(); |
foreach ($sousTaxons as $sousTaxon) { |
$criteres[] = "nom_ret LIKE ".$this->getBdd()->proteger($sousTaxon['nom']."%"); |
} |
return $criteres; |
} |
|
protected function concatenerTaxonsFamilles($taxon) { |
$referentiel = new Referentiel($this->criteresRecherche->referentiel, $taxon); |
$sousTaxons = $referentiel->recupererTaxonsFamilles(); |
$criteres = array(); |
foreach ($sousTaxons as $sousTaxon) { |
$criteres[] = "nom_ret LIKE ".$this->getBdd()->proteger($sousTaxon['nom']."%"); |
} |
return $criteres; |
} |
|
private function construireWhereReferentiel() { |
$sql = ''; |
if (isset($this->criteresRecherche->referentiel) && !isset($this->criteresRecherche->taxon)) { |
176,15 → 208,49 |
return $sql; |
} |
|
private function construireWhereDate() { |
$sql = ''; |
if (isset($this->criteresRecherche->nbJours)) { |
$nbJours = $this->criteresRecherche->nbJours; |
$sql = "AND (Datediff(Curdate(), date_creation)<={$nbJours})"; |
} else { |
$sql = $this->construireWhereDateDebutEtFin(); |
} |
return $sql; |
} |
|
private function construireWhereDateDebutEtFin() { |
$sql = ''; |
$dateDebut = isset($this->criteresRecherche->dateDebut) ? $this->criteresRecherche->dateDebut : null; |
$dateFin = isset($this->criteresRecherche->dateFin) ? $this->criteresRecherche->dateFin : null; |
if (!is_null($dateDebut) || !is_null($dateFin)) { |
$dateFin = !is_null($dateFin) ? $dateFin : date('Y-m-d'); |
$condition = ''; |
if ($dateDebut == $dateFin) { |
$condition = "Date(date_observation)=".$this->getBdd()->proteger($dateDebut); |
} elseif (is_null($dateFin)) { |
$condition = "Date(date_observation)>=".$this->getBdd()->proteger($dateDebut); |
} elseif (is_null($dateDebut)) { |
$condition = "Date(date_observation)<=".$this->getBdd()->proteger($dateFin); |
} else { |
$condition = "Date(date_observation) BETWEEN ".$this->getBdd()->proteger($dateDebut)." ". |
"AND ".$this->getBdd()->proteger($dateFin); |
} |
$sql = "AND ($condition)"; |
} |
return $sql; |
} |
|
private function construireWhereCoordonneesBbox() { |
$bbox = $this->criteresRecherche->bbox; |
$sql = |
"AND (". |
"(". |
"longitude BETWEEN ".$bbox['ouest']." AND ".$bbox['est']." ". |
"AND latitude BETWEEN ".$bbox['sud']." AND ".$bbox['nord']." ". |
"AND (wgs84_longitude IS NULL OR wgs84_latitude IS NULL)". |
"latitude BETWEEN ".$bbox['sud']." AND ".$bbox['nord']." ". |
"AND longitude BETWEEN ".$bbox['ouest']." AND ".$bbox['est']. |
") OR (". |
"((longitude IS NULL OR latitude IS NULL) OR (longitude=0 AND latitude=0) ". |
"OR (longitude>180 AND latitude>90)) AND ". |
"wgs84_longitude BETWEEN ".$bbox['ouest']." AND ". $bbox['est']." ". |
"AND wgs84_latitude BETWEEN ".$bbox['sud']." AND ".$bbox['nord']. |
")". |
193,12 → 259,18 |
} |
|
private function construireWhereCoordonneesPoint() { |
$commune = $this->obtenirCoordonneesCommune(); |
$condition = "(longitude=".$this->criteresRecherche->longitude." ". |
"AND latitude=".$this->criteresRecherche->latitude.")"; |
if (!is_null($commune)) { |
$condition .= " OR (longitude IS NULL AND latitude IS NULL AND ce_zone_geo=". |
$this->getBdd()->proteger($commune['id_zone_geo']).")"; |
$longitude = $this->criteresRecherche->longitude; |
$latitude = $this->criteresRecherche->latitude; |
$condition = "(longitude=".$longitude." AND latitude=".$latitude." ". |
"AND (mots_cles_texte IS NULL OR mots_cles_texte NOT LIKE '%sensible%'))"; |
if ($this->criteresRecherche->typeSite == 'commune') { |
$commune = $this->obtenirCoordonneesCommune(); |
$condition .= |
" OR (". |
"((longitude IS NULL OR latitude IS NULL) OR (longitude=0 AND latitude=0) ". |
"OR (longitude>180 AND latitude>90)) ". |
"AND ce_zone_geo=".$this->getBdd()->proteger($commune['id_zone_geo']). |
")"; |
} |
return "AND ($condition)"; |
} |