/trunk/services/modules/0.1/commun/Commun.php |
---|
12,27 → 12,25 |
public function consulter($ressources, $parametres) { |
$this->recupererRessourcesEtParametres($ressources, $parametres); |
$retour = null; |
try { |
if (!$this->verifierExistenceSourcesDonnees()) { |
$message = "Source de donnees indisponible"; |
throw new Exception($message, RestServeur::HTTP_CODE_RESSOURCE_INTROUVABLE); |
} else { |
$this->chargerNomSource(); |
$this->verifierParametres(); |
$this->chargerNomService(); |
$objetTraitement = new $this->nomService($this->parametresRecherche); |
$methode = $this->genererNomMethodeAExecuter(); |
$retour = $objetTraitement->$methode(); |
if ($this->ressources[0] == 'mailles') { |
$retour = $this->recupererMaillage(); |
} else { |
$this->chargerNomSource(); |
$this->chargerNomService(); |
$retour = $this->executerServicePourSource(); |
} |
} |
} catch (Exception $erreur) { |
$retour = $erreur; |
} |
return $retour; |
} |
41,15 → 39,6 |
$this->parametres = $parametres; |
} |
private function chargerNomService() { |
$this->nomService = ucfirst($this->parametres['source']) . 'Formateur'; |
Projets::chargerConfigurationSource($this->parametres['source']); |
} |
private function genererNomMethodeAExecuter() { |
return 'recuperer' . ucfirst($this->ressources[0]); |
} |
private function verifierExistenceSourcesDonnees() { |
$sourcesDisponibles = explode(',', Config::get('sources_dispo')); |
$estDisponible = false; |
64,14 → 53,6 |
return $estDisponible; |
} |
private function chargerNomSource() { |
if (isset($this->parametres['source'])) { |
$this->nomSource = $this->parametres['source']; |
} else { |
$this->nomSource = Config::get('source_defaut'); |
} |
} |
private function verifierParametres() { |
$this->verificateur = new VerificateurParametres($this->parametres); |
$this->verificateur->verifierParametres(); |
86,7 → 67,36 |
$this->parametresRecherche = $this->verificateur->renvoyerResultatVerification(); |
} |
private function chargerNomSource() { |
if (isset($this->parametres['source'])) { |
$this->nomSource = $this->parametres['source']; |
} else { |
$this->nomSource = Config::get('source_defaut'); |
} |
} |
private function chargerNomService() { |
$this->nomService = ucfirst($this->parametres['source']) . 'Formateur'; |
Projets::chargerConfigurationSource($this->parametres['source']); |
} |
private function recupererMaillage() { |
$maillage = new Maillage($this->parametresRecherche->bbox, $this->parametresRecherche->zoom); |
$maillage->genererMaillesVides(); |
$formateurJSON = new FormateurJson(); |
return $formateurJSON->formaterMaillesVides($maillage->formaterSortie(true)); |
} |
private function executerServicePourSource() { |
$objetTraitement = new $this->nomService($this->parametresRecherche); |
$methode = $this->genererNomMethodeAExecuter(); |
return $objetTraitement->$methode(); |
} |
private function genererNomMethodeAExecuter() { |
return 'recuperer' . ucfirst($this->ressources[0]); |
} |
} |
?> |
/trunk/services/modules/0.1/sources/FloradataFormateur.php |
---|
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)"; |
} |
/trunk/services/modules/0.1/sources/BaznatFormateur.php |
---|
New file |
0,0 → 1,210 |
<?php |
/** |
* |
* Classe en charge de recuperer les donnees d'observation ou liees a leur localisation |
* Le jeu de donnees a interroger est partage en commun avec l'application EFlore |
* |
* On passera en parametre lors de la creation d'une instance un objet contenant la liste des criteres |
* qui vont restreindre le nombre de resultats a renvoyer |
* |
* Les deux operations suivantes peuvent etre utilisees dans les services : |
* - recupererStations : va rechercher dans la base de donnees tous les points d'observations |
* correspondant aux criteres de recherche demandes. La precision des lieux d'observation est |
* soit un point precis, soit ramenee au niveau de la commune dans laquelle l'observation a ete faite |
* En fonction du niveau de zoom et du nombre de resultats trouves, la presentation se fera |
* soit par les points localisant les stations pour des niveaux de zoom eleves ou pour un nombre |
* de stations inferieur a un seuil, ou par des mailles de 64*64 pixels dans le cas contraire |
* |
* - 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 |
* @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 BaznatFormateur extends Formateur { |
protected function construireRequeteStations() { |
$requete = |
"SELECT COUNT(guid) AS observations, commune AS nom, code_insee_commune AS code_insee, ". |
"latitude, longitude, 'STATION' AS type_site FROM baznat_tapir WHERE 1 ". |
$this->construireWhereDepartement().' '. |
$this->construireWhereAuteur().' '. |
$this->construireWhereDate().' '. |
$this->construireWhereReferentiel().' '. |
$this->construireWhereTaxon().' '. |
$this->construireWhereCoordonneesBbox().' '. |
"GROUP BY longitude, latitude"; |
return $requete; |
} |
protected function construireRequeteObservations() { |
$requete = |
"SELECT code_observation AS id_obs, nom_scientifique AS nomSci, date_observation AS date, ". |
"commune AS lieu, observateur FROM baznat_tapir WHERE 1 ". |
$this->construireWhereAuteur().' '. |
$this->construireWhereReferentiel().' '. |
$this->construireWhereDate().' '. |
$this->construireWhereTaxon().' '. |
$this->construireWhereCoordonneesPoint().' '. |
"ORDER BY nom_scientifique, date, observateur"; |
return $requete; |
} |
protected function construireWhereTaxon() { |
$sql = ''; |
if (isset($this->criteresRecherche->taxon)) { |
$taxons = $this->criteresRecherche->taxon; |
$criteres = array(); |
foreach ($taxons as $taxon) { |
$nomRang = $this->getNomRang($taxon); |
$criteres[] = "nom_scientifique LIKE ".$this->getBdd()->proteger($taxon['nom']."%"); |
if ($nomRang == 'espece') { |
$criteres = array_merge($criteres, $this->concatenerTaxonsSousEspeces($taxon)); |
} elseif ($nomRang == 'genre') { |
$criteres = array_merge($criteres, $this->concatenerTaxonsFamilles($taxon)); |
} |
} |
$sql = "AND (".implode(' OR ',array_unique($criteres)).")"; |
} |
return $sql; |
} |
protected function concatenerTaxonsSousEspeces($taxon) { |
$referentiel = new Referentiel($this->criteresRecherche->referentiel, $taxon); |
$sousTaxons = $referentiel->recupererTaxonsSousEspeces(); |
$criteres = array(); |
foreach ($sousTaxons as $sousTaxon) { |
$criteres[] = "nom_scientifique 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_scientifique LIKE ".$this->getBdd()->proteger($sousTaxon['nom']."%"); |
} |
return $criteres; |
} |
// TODO : completer le corps des methodes construire where pour referentiel et departement |
protected function construireWhereReferentiel() { |
$sql = ''; |
return $sql; |
} |
protected function construireWhereDepartement() { |
$sql = ''; |
if (isset($this->criteresRecherche->departement)) { |
$valeurs_a_proteger = $this->criteresRecherche->departement; |
foreach ($valeurs_a_proteger as $valeur) { |
$aProteger = $this->getBdd()->proteger($valeur . '%'); |
$valeurs_protegees[] = "code_insee_commune LIKE {$aProteger}"; |
} |
$valeurs = implode(' OR ', $valeurs_protegees); |
$sql = "AND ($valeurs)"; |
} |
return $sql; |
} |
protected function construireWhereAuteur() { |
$sql = ''; |
if (isset($this->criteresRecherche->auteur)) { |
$aProteger = $this->getBdd()->proteger('%'.$this->criteresRecherche->auteur.'%'); |
$sql = "AND observateur LIKE {$aProteger}"; |
} |
return $sql; |
} |
protected function construireWhereDate() { |
$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)) { |
$dateDebut = !is_null($dateDebut) ? substr($dateDebut, 0, 4) : null; |
$dateFin = !is_null($dateFin) ? substr($dateFin, 0, 4) : date('Y'); |
$condition = ''; |
if ($dateDebut == $dateFin) { |
$condition = "date_observation=".$dateDebut; |
} elseif (is_null($dateFin)) { |
$condition = "date_observation>=".$dateDebut; |
} elseif (is_null($dateDebut)) { |
$condition = "date_observation<=".$dateFin; |
} else { |
$condition = "date_observation BETWEEN ".$dateDebut." AND ".$dateFin; |
} |
$sql = "AND ($condition)"; |
} |
return $sql; |
} |
protected function construireWhereCoordonneesBbox() { |
$bbox = $this->criteresRecherche->bbox; |
$sql = |
"AND longitude BETWEEN ".$bbox['ouest']." AND ".$bbox['est']." ". |
"AND latitude BETWEEN ".$bbox['sud']." AND ".$bbox['nord']; |
return $sql; |
} |
protected function construireWhereCoordonneesPoint() { |
$sql = |
"AND latitude=".$this->criteresRecherche->latitude." ". |
"AND longitude=".$this->criteresRecherche->longitude; |
return $sql; |
} |
protected function obtenirNomsStationsSurPoint() { |
$requete = |
"SELECT DISTINCTROW commune FROM baznat_tapir WHERE 1 ". |
$this->construireWhereTaxon().' '. |
$this->construireWhereCoordonneesPoint(); |
$stations = $this->getBdd()->recupererTous($requete); |
$nomsStations = array(); |
foreach ($stations as $station) { |
$nomsStations[] = $station['commune']; |
} |
return implode(', ', $nomsStations); |
} |
protected function obtenirNombreStationsDansBbox() { |
$bbox = $this->criteresRecherche->bbox; |
$zoom = $this->criteresRecherche->zoom; |
$requete = |
"SELECT zoom, Sum(nombre_sites) AS total_points FROM mailles_baznat ". |
"WHERE zoom=".$zoom." AND limite_sud<=".$bbox['nord']." AND limite_nord>=".$bbox['sud']." ". |
"AND limite_ouest<=".$bbox['est']." AND limite_est>=".$bbox['ouest']." GROUP BY zoom"; |
$resultat = $this->getBdd()->recuperer($requete); |
return $resultat['total_points']; |
} |
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, ". |
"limite_nord AS latitudeNord, nombre_sites, nombre_observations FROM mailles_baznat ". |
"WHERE zoom=".$zoom." AND limite_sud<=".$bbox['nord']." ". |
"AND limite_nord>=".$bbox['sud']." AND limite_ouest<=". $bbox['est']." ". |
"AND limite_est>=".$bbox['ouest']; |
$mailles = $this->getBdd()->recupererTous($requete, Bdd::MODE_OBJET); |
$maillage = new Maillage($this->criteresRecherche->bbox, $zoom, $this->nomSource); |
$maillage->genererMaillesVides(); |
$maillage->ajouterMailles($mailles); |
return $maillage->formaterSortie(); |
} |
} |
?> |
/trunk/services/modules/0.1/sources/Formateur.php |
---|
New file |
0,0 → 1,149 |
<?php |
abstract class Formateur { |
protected $criteresRecherche; |
protected $bdd; |
protected $nomSource = ''; |
public function __construct($criteresRecherche) { |
$this->criteresRecherche = $criteresRecherche; |
$this->nomSource = Config::get('nom_source'); |
} |
public function recupererStations() { |
$stations = null; |
if ($this->estTraitementRecuperationAutorise()) { |
$stations = $this->traiterRecupererStations(); |
} |
return $stations; |
} |
public function recupererObservations() { |
$stations = null; |
if ($this->estTraitementRecuperationAutorise()) { |
$stations = $this->traiterRecupererObservations(); |
} |
return $stations; |
} |
protected function estTraitementRecuperationAutorise() { |
return (!( |
isset($this->criteresRecherche->nbJours) || |
(isset($this->criteresRecherche->referentiel) && |
$this->criteresRecherche->referentiel != Config::get('referentiel_source')) |
)); |
} |
protected function traiterRecupererStations() { |
$stations = array(); |
$nombreStations = $this->obtenirNombreStationsDansBbox(); |
$seuilMaillage = Config::get('seuil_maillage'); |
$nombreParametresNonSpatiaux = $this->calculerNombreCriteresNonSpatiaux(); |
if ($nombreStations >= $seuilMaillage && $nombreParametresNonSpatiaux == 0) { |
// pas besoin de rechercher les stations correspondant a ces criteres |
// recuperer les mailles se trouvant dans l'espace de recherche demande |
$stations = $this->recupererMaillesDansBbox(); |
} else { |
$requeteSql = $this->construireRequeteStations(); |
$stations = $this->getBdd()->recupererTous($requeteSql); |
$zoom = $this->criteresRecherche->zoom; |
$zoomMaxMaillage = Config::get('zoom_maximal_maillage'); |
if ($zoom <= $zoomMaxMaillage && count($stations) >= $seuilMaillage) { |
$maillage = new Maillage($this->criteresRecherche->bbox, $zoom, $this->nomSource); |
$maillage->genererMaillesVides(); |
$maillage->ajouterPoints($stations); |
$stations = $maillage->formaterSortie(); |
} |
} |
$formateurJSON = new FormateurJson($this->nomSource); |
$donneesFormatees = $formateurJSON->formaterStations($stations); |
return $donneesFormatees; |
} |
protected function traiterRecupererObservations() { |
$requeteSql = $this->construireRequeteObservations(); |
$observations = $this->getBdd()->recupererTous($requeteSql); |
$this->recupererNumeroNomenclaturauxTaxons($observations); |
$nomStation = $this->obtenirNomsStationsSurPoint(); |
$formateurJSON = new FormateurJson($this->nomSource); |
$donneesFormatees = $formateurJSON->formaterObservations($observations, $nomStation); |
return $donneesFormatees; |
} |
protected function calculerNombreCriteresNonSpatiaux() { |
$nombreParametresNonSpatiaux = 0; |
$criteresAIgnorer = array('zoom', 'bbox', 'longitude', 'latitude', 'referentiel'); |
foreach ($this->criteresRecherche as $nomCritere => $valeur) { |
if (!in_array($nomCritere, $criteresAIgnorer)) { |
echo $nomCritere.chr(13); |
$nombreParametresNonSpatiaux ++; |
} |
} |
return $nombreParametresNonSpatiaux; |
} |
protected function getBdd() { |
if (!isset($this->bdd)) { |
$this->bdd = new Bdd(); |
} |
$this->bdd->requeter("USE ".Config::get('bdd_nom')); |
return $this->bdd; |
} |
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 recupererNumeroNomenclaturauxTaxons(& $observations) { |
for ($index = 0; $index < count($observations); $index ++) { |
$taxons = isset($this->criteresRecherche->taxon) ? $this->criteresRecherche->taxon : null; |
if (!is_null($taxons)) { |
foreach ($taxons as $taxon) { |
if ($observations[$index]['nomSci'] == 0) { |
continue; |
} |
if (isset($this->criteresRecherche->taxon)) { |
$taxon = $this->rechercherTaxonDansReferentiel($observations[$index]['nomSci'], |
$this->criteresRecherche->referentiel); |
} else { |
$taxon = $this->obtenirNumeroTaxon($observations[$index]['nomSci']); |
} |
if (!is_null($taxon)) { |
$observations[$index]['nn'] = $taxon['nn']; |
$observations[$index]['num_referentiel'] = $taxon['referentiel']; |
} else { |
$observations[$index]['nn'] = ''; |
} |
} |
} |
} |
} |
protected function rechercherTaxonDansReferentiel($nomScientifique, $nomReferentiel) { |
$referentiel = new Referentiel($nomReferentiel); |
$taxon = $referentiel->obtenirNumeroNomenclatural($nomScientifique); |
return $taxon; |
} |
protected function obtenirNumeroTaxon($nomScientifique) { |
$taxonTrouve = null; |
$listeReferentiels = Referentiel::recupererListeReferentielsDisponibles(); |
foreach ($listeReferentiels as $nomReferentiel) { |
$taxon = $this->rechercherTaxonDansReferentiel($nomScientifique, $nomReferentiel); |
if (!is_null($taxon)) { |
$taxonTrouve = $taxon; |
break; |
} |
} |
return $taxonTrouve; |
} |
} |
?> |
/trunk/services/modules/0.1/sources/SophyFormateur.php |
---|
30,96 → 30,25 |
* |
*/ |
class SophyFormateur { |
class SophyFormateur extends Formateur { |
private $criteresRecherche; |
private $bdd; |
public function __construct($criteresRecherche) { |
$this->criteresRecherche = $criteresRecherche; |
} |
public function recupererStations() { |
$stations = array(); |
$nombreStations = $this->obtenirNombreStationsDansBbox(); |
$seuilMaillage = Config::get('seuil_maillage'); |
$nombreParametresNonSpatiaux = $this->calculerNombreCriteresNonSpatiaux(); |
if ($nombreStations >= $seuilMaillage && $nombreParametresNonSpatiaux == 0) { |
// pas besoin de rechercher les stations correspondant a ces criteres |
// recuperer les mailles se trouvant dans l'espace de recherche demande |
$stations = $this->recupererMaillesDansBbox(); |
} else { |
$requeteSql = $this->construireRequeteStations(); |
$stations = $this->getBdd()->recupererTous($requeteSql); |
$zoom = $this->criteresRecherche->zoom; |
$zoomMaxMaillage = Config::get('zoom_maximal_maillage'); |
if ($zoom <= $zoomMaxMaillage && count($stations) >= $seuilMaillage) { |
// generer un maillage a partir des stations recuperees dans la base de donnees |
$maillage = new Maillage($this->criteresRecherche->bbox, $zoom, 'sophy'); |
$maillage->genererMaillesVides(); |
$maillage->ajouterPoints($stations); |
$stations = $maillage->formaterSortie(); |
} |
} |
// mettre en forme les informations au format JSON |
$formateurJSON = new FormateurJson('sophy'); |
$donneesFormatees = $formateurJSON->formaterStations($stations); |
return $donneesFormatees; |
} |
public function recupererObservations() { |
$requeteSql = $this->construireRequeteObservations(); |
$observations = $this->getBdd()->recupererTous($requeteSql); |
$this->recupererNumeroNomenclaturauxTaxons($observations); |
$nomStation = $this->obtenirNomsStationsSurPoint(); |
$formateurJSON = new FormateurJson('sophy'); |
$donneesFormatees = $formateurJSON->formaterObservations($observations, $nomStation); |
return $donneesFormatees; |
} |
private function calculerNombreCriteresNonSpatiaux() { |
$nombreParametresNonSpatiaux = 0; |
$criteresSpatiaux = array('zoom', 'bbox', 'longitude', 'latitude'); |
foreach ($this->criteresRecherche as $nomCritere => $valeur) { |
if (!in_array($nomCritere, $criteresSpatiaux)) { |
$nombreParametresNonSpatiaux ++; |
} |
} |
return $nombreParametresNonSpatiaux; |
} |
// ------------------------------------------------------------------------ // |
// Fonctions de construction des criteres de recherches pour la requete SQL // |
// ------------------------------------------------------------------------ // |
private function getBdd() { |
if (!isset($this->bdd)) { |
$this->bdd = new Bdd(); |
} |
$this->bdd->requeter("USE ".Config::get('bdd_nom')); |
return $this->bdd; |
} |
private function construireRequeteStations() { |
protected function construireRequeteStations() { |
$requete = |
"SELECT DISTINCT lieu_station_nom AS nom, lieu_station_latitude AS latitude, ". |
"lieu_station_longitude AS longitude, 'STATION' AS type_site ". |
"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 ". |
"FROM sophy_tapir WHERE 1 ". |
$this->construireWhereDepartement().' '. |
$this->construireWhereAuteur().' '. |
$this->construireWhereDate().' '. |
$this->construireWhereReferentiel().' '. |
$this->construireWhereTaxon().' '. |
$this->construireWhereCoordonneesBbox().' '. |
"GROUP BY lieu_station_latitude, lieu_station_longitude"; |
"GROUP BY lieu_station_longitude, lieu_station_latitude"; |
return $requete; |
} |
private function construireRequeteObservations() { |
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 ". |
126,6 → 55,7 |
"FROM sophy_tapir WHERE 1 ". |
$this->construireWhereAuteur().' '. |
$this->construireWhereReferentiel().' '. |
$this->construireWhereDate().' '. |
$this->construireWhereTaxon().' '. |
$this->construireWhereCoordonneesPoint().' '. |
"ORDER BY nom_scientifique_complet, date, observateur"; |
132,34 → 62,66 |
return $requete; |
} |
private function construireWhereTaxon() { |
protected function construireWhereTaxon() { |
$sql = ''; |
if (isset($this->criteresRecherche->taxon)) { |
$taxon = $this->criteresRecherche->taxon; |
$criteres = "nom_scientifique_complet 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_scientifique_complet LIKE ".$this->getBdd()->proteger($taxon['nom']."%"); |
$taxons = $this->criteresRecherche->taxon; |
$criteres = array(); |
foreach ($taxons as $taxon) { |
$nomRang = $this->getNomRang($taxon); |
$criteres[] = "nom_scientifique_complet LIKE ".$this->getBdd()->proteger($taxon['nom']."%"); |
if ($nomRang == 'espece') { |
$criteres = array_merge($criteres, $this->concatenerTaxonsSousEspeces($taxon)); |
} elseif ($nomRang == 'genre') { |
$criteres = array_merge($criteres, $this->concatenerTaxonsFamilles($taxon)); |
} |
} |
$sql = "AND ($criteres)"; |
$sql = "AND (".implode(' OR ',array_unique($criteres)).")"; |
} |
return $sql; |
} |
protected function concatenerTaxonsSousEspeces($taxon) { |
$referentiel = new Referentiel($this->criteresRecherche->referentiel, $taxon); |
$sousTaxons = $referentiel->recupererTaxonsSousEspeces(); |
$criteres = array(); |
foreach ($sousTaxons as $sousTaxon) { |
$criteres[] = "nom_scientifique_complet 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_scientifique_complet LIKE ".$this->getBdd()->proteger($sousTaxon['nom']."%"); |
} |
return $criteres; |
} |
// TODO : completer le corps des methodes construire where pour referentiel et departement |
private function construireWhereReferentiel() { |
protected function construireWhereReferentiel() { |
$sql = ''; |
return $sql; |
} |
private function construireWhereDepartement() { |
protected function construireWhereDepartement() { |
$sql = ''; |
if (isset($this->criteresRecherche->departement)) { |
$valeurs_a_proteger = $this->criteresRecherche->departement; |
foreach ($valeurs_a_proteger as $valeur) { |
$aProteger = $this->getBdd()->proteger($valeur . '%'); |
$valeurs_protegees[] = "lieu_commune_code_insee LIKE {$aProteger}"; |
} |
$valeurs = implode(' OR ', $valeurs_protegees); |
$sql = "AND ($valeurs)"; |
} |
return $sql; |
} |
private function construireWhereAuteur() { |
protected function construireWhereAuteur() { |
$sql = ''; |
if (isset($this->criteresRecherche->auteur)) { |
$auteur = $this->getBdd()->proteger($this->criteresRecherche->auteur); |
167,8 → 129,30 |
} |
return $sql; |
} |
protected function construireWhereDate() { |
$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)) { |
$dateDebut = !is_null($dateDebut) ? substr($dateDebut, 0, 4) : null; |
$dateFin = !is_null($dateFin) ? substr($dateFin, 0, 4) : date('Y'); |
$condition = ''; |
if ($dateDebut == $dateFin) { |
$condition = "observation_date=".$dateDebut; |
} elseif (is_null($dateFin)) { |
$condition = "observation_date>=".$dateDebut; |
} elseif (is_null($dateDebut)) { |
$condition = "observation_date<=".$dateFin; |
} else { |
$condition = "observation_date BETWEEN ".$dateDebut." AND ".$dateFin; |
} |
$sql = "AND ($condition)"; |
} |
return $sql; |
} |
private function construireWhereCoordonneesBbox() { |
protected function construireWhereCoordonneesBbox() { |
$bbox = $this->criteresRecherche->bbox; |
$sql = "AND lieu_station_longitude BETWEEN ".$bbox['ouest']." AND ".$bbox['est']." ". |
"AND lieu_station_latitude BETWEEN ".$bbox['sud']." AND ".$bbox['nord']; |
175,13 → 159,13 |
return $sql; |
} |
private function construireWhereCoordonneesPoint() { |
protected function construireWhereCoordonneesPoint() { |
$sql = "AND lieu_station_latitude=".$this->criteresRecherche->latitude." ". |
"AND lieu_station_longitude=".$this->criteresRecherche->longitude; |
return $sql; |
} |
private function obtenirNomsStationsSurPoint() { |
protected function obtenirNomsStationsSurPoint() { |
$requete = "SELECT DISTINCTROW lieu_station_nom FROM sophy_tapir WHERE 1 ". |
$this->construireWhereTaxon().' '. |
$this->construireWhereCoordonneesPoint(); |
193,7 → 177,7 |
return implode(', ', $nomsStations); |
} |
private function obtenirNombreStationsDansBbox() { |
protected function obtenirNombreStationsDansBbox() { |
$bbox = $this->criteresRecherche->bbox; |
$zoom = $this->criteresRecherche->zoom; |
$requete = |
204,60 → 188,23 |
return $resultat['total_points']; |
} |
private function recupererMaillesDansBbox() { |
protected function recupererMaillesDansBbox() { |
$bbox = $this->criteresRecherche->bbox; |
$zoom = $this->criteresRecherche->zoom; |
$requete = |
"SELECT zoom, position_latitude, position_longitude, limite_sud AS sud, limite_ouest AS ouest, ". |
"limite_nord AS nord, limite_est AS est, nombre_sites AS points, 'MAILLE' AS type_site ". |
"FROM mailles_sophy WHERE zoom=".$zoom." AND limite_sud<=".$bbox['nord']." ". |
"SELECT limite_sud AS latitudeSud, limite_ouest AS longitudeOuest, limite_est AS longitudeEst, ". |
"limite_nord AS latitudeNord, nombre_sites, nombre_observations FROM mailles_sophy ". |
"WHERE zoom=".$zoom." AND limite_sud<=".$bbox['nord']." ". |
"AND limite_nord>=".$bbox['sud']." AND limite_ouest<=". $bbox['est']." ". |
"AND limite_est>=".$bbox['ouest']; |
return $this->getBdd()->recupererTous($requete); |
$mailles = $this->getBdd()->recupererTous($requete, Bdd::MODE_OBJET); |
// 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 recupererNumeroNomenclaturauxTaxons(& $observations) { |
for ($index = 0; $index < count($observations); $index ++) { |
$taxon = isset($this->criteresRecherche->taxon) ? $this->criteresRecherche->taxon : null; |
if (is_null($taxon) && strlen(trim($observations[$index]['nomSci'])) > 0) { |
if (isset($this->criteresRecherche->taxon)) { |
$taxon = $this->rechercherTaxonDansReferentiel($observations[$index]['nomSci'], |
$this->criteresRecherche->referentiel); |
} else { |
$taxon = $this->obtenirNumeroTaxon($observations[$index]['nomSci']); |
} |
} |
if (!is_null($taxon)) { |
$observations[$index]['nn'] = $taxon['nn']; |
$observations[$index]['num_referentiel'] = $taxon['referentiel']; |
} else { |
$observations[$index]['nn'] = ''; |
} |
} |
} |
private function rechercherTaxonDansReferentiel($nomScientifique, $nomReferentiel) { |
$referentiel = new Referentiel($nomReferentiel); |
$taxon = $referentiel->obtenirNumeroNomenclatural($nomScientifique); |
return $taxon; |
} |
private function obtenirNumeroTaxon($nomScientifique) { |
$taxonTrouve = null; |
$listeReferentiels = Referentiel::recupererListeReferentielsDisponibles(); |
foreach ($listeReferentiels as $nomReferentiel) { |
$taxon = $this->rechercherTaxonDansReferentiel($nomScientifique, $nomReferentiel); |
if (!is_null($taxon)) { |
$taxonTrouve = $taxon; |
break; |
} |
} |
return $taxonTrouve; |
} |
} |
?> |