WebSVN – eFlore/Applications.moissonnage – Diff – /trunk/services/modules/0.1/sources/MoissonnageFormateur.php

  *   - recupererObservations : va rechercher dans la base de donnees les donnees sur des observations
  *     a partir des coordonnees longitude et latitude d'une station (+ des parametres additionnels)
+ *
  * Les donnees seront renvoyees au format JSON
+ *
- * @package framework-0.3
+ * @package framework-0.4
  * @author Alexandre GALIBERT <alexandre.galibert@tela-botanica.org>
  * @license GPL v3 <http://www.gnu.org/licenses/gpl.txt>
  * @license CECILL v2 <http://www.cecill.info/licences/Licence_CeCILL_V2-en.txt>
  * @version $Id$
  * @copyright 2013 Tela Botanica (accueil@tela-botanica.org)
+ *
  */
-class SophyFormateur extends Formateur {
+class MoissonnageFormateur extends Formateur {
-	protected function construireRequeteStations() {
+	final protected function construireRequeteStations() {
 		$requete =
 			"SELECT COUNT(guid) AS observations, lieu_station_nom AS nom, lieu_station_latitude AS latitude, ".
-			"lieu_station_longitude AS longitude, 'STATION' AS type_site, lieu_commune_code_insee AS code_insee ".
+			"lieu_station_longitude AS longitude, 'STATION' AS type_site, lieu_commune_code_insee AS code_insee, ".
-			"FROM sophy_tapir WHERE 1 ".
+			"'{$this->nomSource}' AS source FROM {$this->nomSource}_tapir WHERE 1 ".
 			$this->construireWhereDepartement().' '.
 			$this->construireWhereAuteur().' '.
 			$this->construireWhereDate().' '.
 			$this->construireWhereReferentiel().' '.
 			$this->construireWhereTaxon().' '.
 			$this->construireWhereCoordonneesBbox().' '.
 			"GROUP BY lieu_station_longitude, lieu_station_latitude";
 		return $requete;
+	}
-	protected function construireRequeteObservations() {
+	final protected function construireRequeteObservations() {
 		$requete =
 			"SELECT observation_id AS id_obs, nom_scientifique_complet AS nomSci, ".
-			"observation_date AS date, lieu_station_nom AS lieu, observateur_nom_complet AS observateur ".
+			"observation_date AS date, lieu_station_nom AS lieu, observateur_nom_complet AS observateur, ".
-			"FROM sophy_tapir WHERE 1 ".
+			"'{$this->nomSource}' AS projet FROM {$this->nomSource}_tapir WHERE 1 ".
 			$this->construireWhereAuteur().' '.
 			$this->construireWhereReferentiel().' '.
 			$this->construireWhereDate().' '.
 			$this->construireWhereTaxon().' '.
+			$this->construireWhereCoordonneesPoint().' '.
+			"ORDER BY nom_scientifique_complet, date, observateur";
+		return $requete;
+	}
+	final protected function construireRequeteWfs() {
+		$requete =
+		"SELECT nom_scientifique_complet AS taxon, lieu_station_nom AS nom, lieu_station_latitude AS latitude, ".
+		"lieu_station_longitude AS longitude, 'STATION' AS type_site, lieu_commune_code_insee AS code_insee, ".
+		"'{$this->nomSource}' AS source FROM {$this->nomSource}_tapir WHERE 1 ".
+		$this->construireWhereCoordonneesBbox().' '.
-			$this->construireWhereCoordonneesPoint().' '.
+		$this->construireWhereNomScientifique().' '.
-			"ORDER BY nom_scientifique_complet, date, observateur";
+		"ORDER BY lieu_station_longitude, lieu_station_latitude";
 		return $requete;
+	}
-	protected function construireWhereTaxon() {
+	private function construireWhereTaxon() {
-		$sql = '';
-		if (isset($this->criteresRecherche->taxon)) {
+		$sql = '';
-			$taxons = $this->criteresRecherche->taxon;
+		if (isset($this->criteresRecherche->taxon)) {
-			$criteres = array();
+			$taxons = $this->criteresRecherche->taxon;
-			foreach ($taxons as $taxon) {
+			$criteres = array();
-				$nomRang = $this->getNomRang($taxon);
+			foreach ($taxons as $taxon) {
 				$criteres[] = "nom_scientifique_complet LIKE ".$this->getBdd()->proteger($taxon['nom']."%");
-				if ($nomRang == 'espece') {
+				if ($taxon['rang'] >= Config::get('rang.espece')) {
-					$criteres = array_merge($criteres, $this->concatenerTaxonsSousEspeces($taxon));
+					$criteres = array_merge($criteres, $this->concatenerSynonymesEtSousEspeces($taxon));
-				} elseif ($nomRang == 'genre') {
+				} elseif ($taxon['rang'] == Config::get('rang.famille')) {
-					$criteres = array_merge($criteres, $this->concatenerTaxonsFamilles($taxon));
+					$criteres = array_merge($criteres, $this->concatenerTaxonsGenres($taxon));
+				}
 			}
 			$sql = "AND (".implode(' OR ',array_unique($criteres)).")";
 		}
 		return $sql;
+	}
-	protected function concatenerTaxonsSousEspeces($taxon) {
+	private function concatenerSynonymesEtSousEspeces($taxon) {
 		$referentiel = new Referentiel($this->criteresRecherche->referentiel, $taxon);
-		$sousTaxons = $referentiel->recupererTaxonsSousEspeces();
+		$sousTaxons = $referentiel->recupererSynonymesEtSousEspeces();
 		$criteres = array();
 		foreach ($sousTaxons as $sousTaxon) {
 			$criteres[] = "nom_scientifique_complet LIKE ".$this->getBdd()->proteger($sousTaxon['nom']."%");
+		}
 		return $criteres;
+	}
-	protected function concatenerTaxonsFamilles($taxon) {
+	private function concatenerTaxonsGenres($taxon) {
 		$referentiel = new Referentiel($this->criteresRecherche->referentiel, $taxon);
-		$sousTaxons = $referentiel->recupererTaxonsFamilles();
-		$criteres = array();
+		$sousTaxons = $referentiel->recupererGenres();
-		foreach ($sousTaxons as $sousTaxon) {
+		$criteres = array();
+		foreach ($sousTaxons as $sousTaxon) {
+			$criteres[] = "nom_scientifique_complet LIKE ".$this->getBdd()->proteger($sousTaxon['nom']."%");
+		}
+		return $criteres;
+	}
-			$criteres[] = "nom_scientifique_complet LIKE ".$this->getBdd()->proteger($sousTaxon['nom']."%");
+	private function construireWhereReferentiel() {
 		$sql = '';
-		return $criteres;
+		if (isset($this->criteresRecherche->referentiel)) {
 			$referentielSource = Config::get('referentiel_source');
 			if (strstr($this->criteresRecherche->referentiel, $referentielSource) === false) {
-	// TODO : completer le corps des methodes construire where pour referentiel et departement
+				$sql = "AND 0";
 	protected function construireWhereReferentiel() {
 		$sql = '';
 			$sql = "AND ($valeurs)";
+		}
 		return $sql;
+	}
-	protected function construireWhereAuteur() {
+	private function construireWhereAuteur() {
 		$sql = '';
 		if (isset($this->criteresRecherche->auteur)) {
 			$auteur = $this->getBdd()->proteger($this->criteresRecherche->auteur);
 			$sql = "AND observateur_nom_complet = $auteur";
+		}
 		return $sql;
 	}
-	protected function construireWhereDate() {
+	private function construireWhereDate() {
 		$sql = '';
 		$dateDebut = isset($this->criteresRecherche->dateDebut) ? $this->criteresRecherche->dateDebut : null;
 		$dateFin   = isset($this->criteresRecherche->dateFin) ? $this->criteresRecherche->dateFin : null;
 			$sql = "AND ($condition)";
+		}
 		return $sql;
+	}
+	private function construireWhereCoordonneesBbox() {
+		$sql = '';
-	protected function construireWhereCoordonneesBbox() {
+		if (isset($this->criteresRecherche->bbox)) {
+			$bboxRecherche = $this->criteresRecherche->bbox;
+			$conditions = array();
-		$bbox = $this->criteresRecherche->bbox;
+			foreach ($bboxRecherche as $bbox) {
-		$sql = "AND lieu_station_longitude BETWEEN ".$bbox['ouest']." AND ".$bbox['est']." ".
+				$conditions[] = "(lieu_station_longitude BETWEEN ".$bbox['ouest']." AND ".$bbox['est']." ".
+					"AND lieu_station_latitude BETWEEN ".$bbox['sud']." AND ".$bbox['nord'].")";
+			}
+			$sql = 'AND ('.implode(' OR ', $conditions).')';
+		}
+		return $sql;
+	}
+	private function construireWhereNomScientifique() {
+		$sql = '';
+		if (isset($this->criteresRecherche->filtre)) {
+			$filtre = $this->criteresRecherche->filtre;
+			$valeur = "'{$filtre['valeur']}".($filtre['operateur'] == 'LIKE' ? "%" : "")."'";
+			switch ($filtre['champ']) {
+				case "taxon" : $sql = "AND nom_scientifique_complet {$filtre['operateur']} {$valeur}"; break;
+			}
 			"AND lieu_station_latitude BETWEEN ".$bbox['sud']." AND ".$bbox['nord'];
 		return $sql;
 	}
+	private function construireWhereCoordonneesPoint() {
+		$sql = '';
+		$conditions = array();
+		foreach ($this->criteresRecherche->stations as $station) {
+			if ($station[0] == $this->nomSource) {
-	protected function construireWhereCoordonneesPoint() {
+				$longitude = str_replace(",", ".", strval($station[2]));
+				$latitude  = str_replace(",", ".", strval($station[3]));
+				$conditions[] = "(lieu_station_latitude={$longitude} AND lieu_station_longitude={$latitude})";
+			}
+		}
+		if (count($conditions) > 0) {
-		$sql = "AND lieu_station_latitude=".$this->criteresRecherche->latitude." ".
+			$sql = "AND (".implode(" OR ", $conditions).")";
 			"AND lieu_station_longitude=".$this->criteresRecherche->longitude;
 		return $sql;
 	}
-	protected function obtenirNomsStationsSurPoint() {
+	final protected function obtenirNomsStationsSurPoint() {
-		$requete = "SELECT DISTINCTROW lieu_station_nom FROM sophy_tapir WHERE 1 ".
+		$requete = "SELECT DISTINCTROW lieu_station_nom FROM {$this->nomSource}_tapir WHERE 1 ".
 			$this->construireWhereTaxon().' '.
 			$this->construireWhereCoordonneesPoint();
 		$stations = $this->getBdd()->recupererTous($requete);
 		$nomsStations = array();
 		foreach ($stations as $station) {
 			$nomsStations[] = $station['lieu_station_nom'];
 		}
 		return implode(', ', $nomsStations);
+	}
-	protected function obtenirNombreStationsDansBbox() {
+	final protected function obtenirNombreStationsDansBbox() {
-		$bbox = $this->criteresRecherche->bbox;
-		$zoom = $this->criteresRecherche->zoom;
+		$bbox = $this->criteresRecherche->bbox;
-		$requete =
+		$zoom = $this->criteresRecherche->zoom;
-			"SELECT zoom, Sum(nombre_sites) AS total_points FROM mailles_sophy ".
+		$requete =
-			"WHERE zoom=".$zoom." AND limite_sud<=".$bbox['nord']." AND limite_nord>=".$bbox['sud']." ".
+			"SELECT zoom, Sum(nombre_sites) AS total_points FROM mailles_{$this->nomSource} ".
-			"AND limite_ouest<=".$bbox['est']." AND limite_est>=".$bbox['ouest']." GROUP BY zoom";
+			"WHERE zoom=".$zoom." ".$this->construireWhereMaillesBbox()." GROUP BY zoom";
 		$resultat = $this->getBdd()->recuperer($requete);
 		return $resultat['total_points'];
+	}
-	protected function recupererMaillesDansBbox() {
+	final protected function recupererMaillesDansBbox() {
 		$bbox = $this->criteresRecherche->bbox;
-		$zoom = $this->criteresRecherche->zoom;
-		$requete =
-			"SELECT limite_sud AS latitudeSud, limite_ouest AS longitudeOuest, limite_est AS longitudeEst, ".
+		$zoom = $this->criteresRecherche->zoom;
-			"limite_nord AS latitudeNord, nombre_sites, nombre_observations FROM mailles_sophy ".
+		$requete =
-			"WHERE zoom=".$zoom." AND limite_sud<=".$bbox['nord']." ".
+			"SELECT limite_sud AS sud, limite_ouest AS ouest, limite_est AS est, limite_nord AS nord, ".
-			"AND limite_nord>=".$bbox['sud']." AND limite_ouest<=". $bbox['est']." ".
+			"nombre_sites AS stations, nombre_observations AS observations FROM mailles_{$this->nomSource} ".
-			"AND limite_est>=".$bbox['ouest'];
+			"WHERE zoom=".$zoom." ".$this->construireWhereMaillesBbox();
-		$mailles = $this->getBdd()->recupererTous($requete, Bdd::MODE_OBJET);
+		$mailles = $this->getBdd()->recupererTous($requete);
 		// placer les totaux des nombres de stations dans des mailles vides
+		$maillage = new Maillage($this->criteresRecherche->bbox, $zoom, $this->nomSource);
+		$maillage->genererMaillesVides();
+		$maillage->ajouterMailles($mailles);
+		return $maillage->formaterSortie();
+	}
+	private function construireWhereMaillesBbox() {
+		$bboxRecherche = $this->criteresRecherche->bbox;
+		$conditions = array();
+		$sql = '';
+		foreach ($bboxRecherche as $bbox) {
+			$conditions[] = "(limite_sud<=".$bbox['nord']." AND limite_nord>=".$bbox['sud']." ".
+				"AND limite_ouest<=". $bbox['est']." AND limite_est>=".$bbox['ouest'].")";
+		}
-		$maillage = new Maillage($this->criteresRecherche->bbox, $zoom, $this->nomSource);
+		if (count($conditions) > 0) {
-		$maillage->genererMaillesVides();
+			$sql = 'AND ('.implode(' OR ', $conditions).')';
 		$maillage->ajouterMailles($mailles);

Subversion Repositories eFlore/Applications.moissonnage

(root)/trunk/services/modules/0.1/sources/MoissonnageFormateur.php – Rev 31 → 34