New file |
0,0 → 1,187 |
<?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(); |
$criteres[] = ($source == "baznat" || $source == "ifn") ? |
"num_nom = ".$this->taxon['num_nom'] : |
"nom_scientifique_complet LIKE ".$this->bdd->proteger($this->taxon['nom_sci']) ; |
if ($this->nomRang == 'espece' || $this->nomRang == 'sous_espece') { |
foreach ($this->taxons as $sousTaxon) { |
$criteres[] = ($source == "baznat" || $source == "ifn") ? |
"num_nom = ".$sousTaxon['num_nom'] : |
"nom_scientifique_complet LIKE ".$this->bdd->proteger($sousTaxon['nom_sci']) ; |
} |
} 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); |
} |
|
} |
|
?> |