Subversion Repositories eFlore/Projets.eflore-projets

Rev

Rev 997 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
727 alex 1 
<?php
2 
 
747 alex 3 
class SourceDonnees {
727 alex 4 
 
747 alex 5 
	private $bdd = null;
6 
	private $limitesCarte = '';
7 
	private $taxon = array();
727 alex 8 
 
747 alex 9 
	private $nomRang = '';
10 
	private $taxons = array();
11 
	private $genres = array();
878 aurelien 12 
	private $utilisateurs = null;
727 alex 13 
 
747 alex 14 
 
15 
	public function __construct($limitesCarte, $taxon) {
727 alex 16 
		$this->limitesCarte = $limitesCarte;
17 
		foreach ($this->limitesCarte as $bord => $valeur) {
18 
			$this->limitesCarte[$bord] = str_replace(",", ".", round($valeur, 6));
19 
		}
20 
		$this->bdd = new Bdd();
21 
		$this->taxon = $taxon;
747 alex 22 
		$this->nomRang = $this->obtenirNomRang();
23 
		if ($this->nomRang == 'espece' || $this->nomRang == 'sous_espece') {
24 
			$this->taxons = $this->recupererSynonymesEtSousEspeces();
25 
		} elseif ($this->nomRang == 'famille') {
26 
			$this->genres = $this->recupererGenres();
27 
		}
878 aurelien 28 
		$this->utilisateurs = new Utilisateurs();
727 alex 29 
	}
30 
 
747 alex 31 
	private function obtenirNomRang() {
727 alex 32 
		$nomsRangs = array('famille', 'genre', 'espece', 'sous_espece');
33 
		$rangs = explode(',', Config::get('rangs'));
34 
		for ($index = 0; $index < count($nomsRangs) && $rangs[$index] != $this->taxon['rang']; $index ++);
35 
		$position = $index == count($nomsRangs) ? count($nomsRangs)-1 : $index;
36 
		return $nomsRangs[$position];
37 
	}
38 
 
39 
	protected function recupererSynonymesEtSousEspeces() {
40 
		$requete =
971 delphine 41 
		"SELECT num_nom, nom_sci, nom_complet, num_taxonomique FROM ".Config::get('bdd_table_referentiel').
42 
		" WHERE hierarchie LIKE '%-{$this->taxon['num_nom']}-%' ".
43 
		"OR num_nom_retenu = {$this->taxon['num_nom_retenu']}";
727 alex 44 
		return $this->bdd->recupererTous($requete);
45 
	}
46 
 
47 
	protected function recupererGenres() {
48 
		$this->bdd->requeter("USE ".Config::get('bdd_nom'));
49 
		$requete =
971 delphine 50 
		"SELECT num_nom, nom_sci, num_taxonomique FROM ".Config::get('bdd_table_referentiel').
51 
		" WHERE rang=220 AND num_tax_sup={$this->taxon['num_nom']}";
727 alex 52 
		return $this->bdd->recupererTous($requete);
53 
	}
54 
 
747 alex 55 
	public function recupererStationsFloradata() {
56 
		$this->bdd->requeter("USE ".Config::get('bdd_nom_floradata'));
57 
		$requete =
58 
		"SELECT DISTINCTROW zone_geo AS commune, Date(date_observation) AS date, Floor(wgs84_latitude*10)/10 AS lat, ".
59 
		"Floor(wgs84_longitude*10)/10 AS lng, courriel_utilisateur AS auteur ".
60 
		"FROM cel_obs LEFT JOIN cel_zones_geo cz ON ce_zone_geo=id_zone_geo ".
971 delphine 61 
		"WHERE ".$this->construireWhereTaxonFloradata()." AND transmission=1 AND nom_referentiel = '".Config::get('referentielsDispo')."' AND ".
747 alex 62 
		"wgs84_longitude BETWEEN ".$this->limitesCarte['ouest']." AND ".$this->limitesCarte['est']." ".
63 
		"AND wgs84_latitude BETWEEN ".$this->limitesCarte['sud']." AND ".$this->limitesCarte['nord']." ".
64 
		"AND date_observation<>'0000-00-00 00-00-00' ORDER BY lat DESC, lng ASC, commune, date";
878 aurelien 65 
		$stations = $this->bdd->recupererTous($requete);
66 
 
67 
		$this->extraireIdentitesAuteurs($stations);
68 
		foreach($stations as &$station) {
69 
			$station['auteur'] = $this->utilisateurs->getIntitule($station['auteur']);
70 
		}
71 
		return $stations;
747 alex 72 
	}
73 
 
878 aurelien 74 
	private function extraireIdentitesAuteurs($stations) {
75 
		$courriels = array();
76 
		foreach ($stations as &$station) {
77 
			$courriels[] = $station['auteur'];
78 
		}
79 
		$this->utilisateurs->setCourriels($courriels);
80 
		$this->utilisateurs->chargerIdentites();
81 
	}
82 
 
747 alex 83 
	private function construireWhereTaxonFloradata() {
84 
		$criteres = array();
85 
		$nomRang = $this->obtenirNomRang($this->taxon);
86 
		if ($this->nomRang == 'famille') {
87 
			$criteres[] = "famille=".$this->bdd->proteger($this->taxon['nom_sci']);
88 
		} elseif ($this->nomRang == 'genre') {
89 
			$criteres[] = "nom_sel LIKE ".$this->bdd->proteger($this->taxon['nom_sci'].'%');
90 
		} else {
971 delphine 91 
			$taxons = array($this->taxon['num_nom']);
747 alex 92 
			foreach ($this->taxons as $sousTaxon) {
971 delphine 93 
				$taxons[] = $sousTaxon['num_nom'];
747 alex 94 
			}
971 delphine 95 
			$criteres[] = "nom_sel_nn IN (".implode(',', array_unique($taxons))	.")";
747 alex 96 
		}
97 
		return "(".implode(' OR ',array_unique($criteres)).")";
98 
	}
99 
 
100 
	public function recupererStationsMoissonnage($source) {
101 
		$this->bdd->requeter("USE ".Config::get('bdd_nom'));
102 
		$requete =
103 
		"SELECT DISTINCTROW lieu_commune_code_insee, observation_date AS date, observateur_nom_complet AS auteur ".
970 delphine 104 
		"FROM {$source}_tapir WHERE ".$this->construireWhereTaxonMoissonnage($source)." ".
747 alex 105 
		"AND lieu_station_longitude BETWEEN ".$this->limitesCarte['ouest']." AND ".$this->limitesCarte['est']." ".
106 
		"AND lieu_station_latitude BETWEEN ".$this->limitesCarte['sud']." AND ".$this->limitesCarte['nord']." ".
949 raphael 107 
		"AND Length(lieu_commune_code_insee)=5 ORDER BY lieu_commune_code_insee, date"." -- " . __FILE__ . ":" . __LINE__." ". @$_SERVER['REQUEST_URI'];
747 alex 108 
		$stations = $this->bdd->recupererTous($requete);
109 
		$this->rechercherInfosCommune($stations);
110 
		return $stations;
111 
	}
112 
 
970 delphine 113 
	private function construireWhereTaxonMoissonnage($source) {
747 alex 114 
		$nomRang = $this->obtenirNomRang();
115 
		$criteres = array();
1002 mathias 116 
		$criteres[] = ($source == "baznat" || $source == "ifn") ?
117 
						"num_nom = ".$this->taxon['num_nom'] :
118 
						"nom_scientifique_complet LIKE ".$this->bdd->proteger($this->taxon['nom_sci']) ;
747 alex 119 
		if ($this->nomRang == 'espece' || $this->nomRang == 'sous_espece') {
120 
			foreach ($this->taxons as $sousTaxon) {
1002 mathias 121 
				$criteres[] = ($source == "baznat" || $source == "ifn") ?
122 
										"num_nom = ".$sousTaxon['num_nom'] :
123 
										"nom_scientifique_complet LIKE ".$this->bdd->proteger($sousTaxon['nom_sci']) ;
747 alex 124 
			}
125 
		} elseif ($this->nomRang == 'famille') {
126 
			foreach ($this->genres as $genre) {
127 
				$criteres[] = "nom_scientifique_complet LIKE ".$this->bdd->proteger($genre['nom_sci']."%");
128 
			}
129 
		}
130 
		return "(".implode(' OR ',array_unique($criteres)).")";
131 
	}
132 
 
133 
	private function rechercherInfosCommune(& $stations) {
134 
		$codesInsee = array();
135 
		foreach ($stations as $station) {
136 
			$codeInsee = $station['lieu_commune_code_insee'];
137 
			if (substr($codeInsee, 0, 2) == '20') {
138 
				$codeInsee  = '2A'.substr($codeInsee, 2);
139 
				$codeInsee2 = '2B'.substr($codeInsee, 2);
140 
			}
141 
			if (!in_array($codeInsee, $codesInsee)) {
142 
				if (substr($codeInsee, 0, 2) == '20') {
143 
					$codesInsee[] = "'$codeInsee2'";
144 
				}
145 
				$codesInsee[] = "'$codeInsee'";
146 
			}
147 
		}
751 alex 148 
		$nomTableCommunes = Config::get('bdd_table_communes');
860 aurelien 149 
		$communes = array();
150 
		if(count($codesInsee) > 0) {
151 
			$requete =
152 
			"SELECT insee, nom AS commune, Floor(latitude_degre*10)/10 AS lat, Floor(longitude_degre*10)/10 AS lng ".
153 
			"FROM $nomTableCommunes WHERE insee IN (".implode(',', array_unique($codesInsee)).") ORDER BY insee";
154 
			$communes = $this->bdd->recupererTous($requete);
155 
		}
747 alex 156 
		$indexStation = 0;
157 
		foreach ($communes as $commune) {
158 
			$codeInsee = $commune['insee'];
159 
			if (substr($codeInsee, 0, 2) == '2A' || substr($codeInsee, 0, 2) == '2B') {
160 
				$codeInsee = '20'.substr($codeInsee, 2);
161 
			}
162 
			while ($stations[$indexStation]['lieu_commune_code_insee'] < $codeInsee) {
163 
				$indexStation ++;
164 
			}
165 
			if ($stations[$indexStation]['lieu_commune_code_insee'] == $codeInsee) {
166 
				$stations[$indexStation]['lat'] = $commune['lat'];
167 
				$stations[$indexStation]['lng'] = $commune['lng'];
168 
				$stations[$indexStation]['commune'] = $commune['commune'];
169 
			}
170 
		}
171 
 
172 
		$lat = array();
173 
		$lng = array();
174 
		foreach ($stations as $index => $station) {
175 
			if (!isset($station['lat'])) {
176 
				$station['lat'] = -100;
177 
				$station['lng'] = -100;
178 
			}
179 
			$lat[$index] = $station['lat'];
180 
			$lng[$index] = $station['lng'];
181 
		}
182 
		array_multisort($lat, SORT_DESC, $lng, SORT_ASC, $stations);
183 
	}
184 
 
727 alex 185 
}
186 
 
187 
?>