Subversion Repositories eFlore/Projets.eflore-projets

<?php

class SourceDonnees {

	private $bdd = null;

	private $limitesCarte = '';

	private $taxon = array();

	private $nomRang = '';

	private $taxons = array();

	private $genres = array();

	private $utilisateurs = null;

	public function __construct($limitesCarte, $taxon) {

		$this->limitesCarte = $limitesCarte;

		foreach ($this->limitesCarte as $bord => $valeur) {

			$this->limitesCarte[$bord] = str_replace(",", ".", round($valeur, 6));

		}

		$this->bdd = new Bdd();

		$this->taxon = $taxon;

		$this->nomRang = $this->obtenirNomRang();

		if ($this->nomRang == 'espece' || $this->nomRang == 'sous_espece') {

			$this->taxons = $this->recupererSynonymesEtSousEspeces();

		} elseif ($this->nomRang == 'famille') {

			$this->genres = $this->recupererGenres();

		}

		$this->utilisateurs = new Utilisateurs();

	}

	private function obtenirNomRang() {

		$nomsRangs = array('famille', 'genre', 'espece', 'sous_espece');

		$rangs = explode(',', Config::get('rangs'));

		for ($index = 0; $index < count($nomsRangs) && $rangs[$index] != $this->taxon['rang']; $index ++);

		$position = $index == count($nomsRangs) ? count($nomsRangs)-1 : $index;

		return $nomsRangs[$position];

	}

	protected function recupererSynonymesEtSousEspeces() {

		$requete =

		"SELECT num_nom, nom_sci, nom_complet, num_taxonomique FROM ".Config::get('bdd_table_referentiel').

		" WHERE hierarchie LIKE '%-{$this->taxon['num_nom']}-%' ".

		"OR num_nom_retenu = {$this->taxon['num_nom_retenu']}";

		return $this->bdd->recupererTous($requete);

	}

	protected function recupererGenres() {

		$this->bdd->requeter("USE ".Config::get('bdd_nom'));

		$requete =

		"SELECT num_nom, nom_sci, num_taxonomique FROM ".Config::get('bdd_table_referentiel').

		" WHERE rang=220 AND num_tax_sup={$this->taxon['num_nom']}";

		return $this->bdd->recupererTous($requete);

	}

	public function recupererStationsFloradata() {

		$this->bdd->requeter("USE ".Config::get('bdd_nom_floradata'));

		$requete =

		"SELECT DISTINCTROW zone_geo AS commune, Date(date_observation) AS date, Floor(wgs84_latitude*10)/10 AS lat, ".

		"Floor(wgs84_longitude*10)/10 AS lng, courriel_utilisateur AS auteur ".

		"FROM cel_obs LEFT JOIN cel_zones_geo cz ON ce_zone_geo=id_zone_geo ".

		"WHERE ".$this->construireWhereTaxonFloradata()." AND transmission=1 AND nom_referentiel = '".Config::get('referentielsDispo')."' AND ".

		"wgs84_longitude BETWEEN ".$this->limitesCarte['ouest']." AND ".$this->limitesCarte['est']." ".

		"AND wgs84_latitude BETWEEN ".$this->limitesCarte['sud']." AND ".$this->limitesCarte['nord']." ".

		"AND date_observation<>'0000-00-00 00-00-00' ORDER BY lat DESC, lng ASC, commune, date";

		$stations = $this->bdd->recupererTous($requete);

		$this->extraireIdentitesAuteurs($stations);

		foreach($stations as &$station) {

			$station['auteur'] = $this->utilisateurs->getIntitule($station['auteur']);

		}

		return $stations;

	}

	private function extraireIdentitesAuteurs($stations) {

		$courriels = array();

		foreach ($stations as &$station) {

			$courriels[] = $station['auteur'];

		}

		$this->utilisateurs->setCourriels($courriels);

		$this->utilisateurs->chargerIdentites();

	}

	private function construireWhereTaxonFloradata() {

		$criteres = array();

		$nomRang = $this->obtenirNomRang($this->taxon);

		if ($this->nomRang == 'famille') {

			$criteres[] = "famille=".$this->bdd->proteger($this->taxon['nom_sci']);

		} elseif ($this->nomRang == 'genre') {

			$criteres[] = "nom_sel LIKE ".$this->bdd->proteger($this->taxon['nom_sci'].'%');

		} else {

			$taxons = array($this->taxon['num_nom']);

			foreach ($this->taxons as $sousTaxon) {

				$taxons[] = $sousTaxon['num_nom'];

			}

			$criteres[] = "nom_sel_nn IN (".implode(',', array_unique($taxons))	.")";

		}

		return "(".implode(' OR ',array_unique($criteres)).")";

	}

	public function recupererStationsMoissonnage($source) {

		$this->bdd->requeter("USE ".Config::get('bdd_nom'));

		$requete =

		"SELECT DISTINCTROW lieu_commune_code_insee, observation_date AS date, observateur_nom_complet AS auteur ".

		"FROM {$source}_tapir WHERE ".$this->construireWhereTaxonMoissonnage($source)." ".

		"AND lieu_station_longitude BETWEEN ".$this->limitesCarte['ouest']." AND ".$this->limitesCarte['est']." ".

		"AND lieu_station_latitude BETWEEN ".$this->limitesCarte['sud']." AND ".$this->limitesCarte['nord']." ".

		"AND Length(lieu_commune_code_insee)=5 ORDER BY lieu_commune_code_insee, date"." -- " . __FILE__ . ":" . __LINE__." ". @$_SERVER['REQUEST_URI'];

		$stations = $this->bdd->recupererTous($requete);

		$this->rechercherInfosCommune($stations);

		return $stations;

	}

	private function construireWhereTaxonMoissonnage($source) {

		$nomRang = $this->obtenirNomRang();

		$criteres = array();

		$nom = ($source == "baznat") ? implode('%',explode(' ',$this->taxon['nom_complet'] )) : $this->taxon['nom_sci'] ;

		$criteres[] = "nom_scientifique_complet LIKE ".$this->bdd->proteger($nom);

		if ($this->nomRang == 'espece' || $this->nomRang == 'sous_espece') {

			foreach ($this->taxons as $sousTaxon) {

				$nom = ($source == "baznat") ? implode('%',explode(' ',$sousTaxon['nom_complet'] )) : $sousTaxon['nom_sci'];

				$criteres[] = "nom_scientifique_complet LIKE ".$this->bdd->proteger($nom);

			}

		} elseif ($this->nomRang == 'famille') {

			foreach ($this->genres as $genre) {

				$criteres[] = "nom_scientifique_complet LIKE ".$this->bdd->proteger($genre['nom_sci']."%");

			}

		}

		return "(".implode(' OR ',array_unique($criteres)).")";

	}

	private function rechercherInfosCommune(& $stations) {

		$codesInsee = array();

		foreach ($stations as $station) {

			$codeInsee = $station['lieu_commune_code_insee'];

			if (substr($codeInsee, 0, 2) == '20') {

				$codeInsee  = '2A'.substr($codeInsee, 2);

				$codeInsee2 = '2B'.substr($codeInsee, 2);

			}

			if (!in_array($codeInsee, $codesInsee)) {

				if (substr($codeInsee, 0, 2) == '20') {

					$codesInsee[] = "'$codeInsee2'";

				}

				$codesInsee[] = "'$codeInsee'";

			}

		}

		$nomTableCommunes = Config::get('bdd_table_communes');

		$communes = array();

		if(count($codesInsee) > 0) {

			$requete =

			"SELECT insee, nom AS commune, Floor(latitude_degre*10)/10 AS lat, Floor(longitude_degre*10)/10 AS lng ".

			"FROM $nomTableCommunes WHERE insee IN (".implode(',', array_unique($codesInsee)).") ORDER BY insee";

			$communes = $this->bdd->recupererTous($requete);

		}

		$indexStation = 0;

		foreach ($communes as $commune) {

			$codeInsee = $commune['insee'];

			if (substr($codeInsee, 0, 2) == '2A' || substr($codeInsee, 0, 2) == '2B') {

				$codeInsee = '20'.substr($codeInsee, 2);

			}

			while ($stations[$indexStation]['lieu_commune_code_insee'] < $codeInsee) {

				$indexStation ++;

			}

			if ($stations[$indexStation]['lieu_commune_code_insee'] == $codeInsee) {

				$stations[$indexStation]['lat'] = $commune['lat'];

				$stations[$indexStation]['lng'] = $commune['lng'];

				$stations[$indexStation]['commune'] = $commune['commune'];

			}

		}

		$lat = array();

		$lng = array();

		foreach ($stations as $index => $station) {

			if (!isset($station['lat'])) {

				$station['lat'] = -100;

				$station['lng'] = -100;

			}

			$lat[$index] = $station['lat'];

			$lng[$index] = $station['lng'];

		}

		array_multisort($lat, SORT_DESC, $lng, SORT_ASC, $stations);

	}

}

?>