Subversion Repositories eFlore/Projets.eflore-projets

Rev

Rev 1205 | Rev 1296 | Go to most recent revision | 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 ".
1295 delphine 60
		"FROM cel_export_total 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
 
1192 delphine 100
 
101
 
747 alex 102
	public function recupererStationsMoissonnage($source) {
103
		$this->bdd->requeter("USE ".Config::get('bdd_nom'));
104
		$requete =
105
		"SELECT DISTINCTROW lieu_commune_code_insee, observation_date AS date, observateur_nom_complet AS auteur ".
970 delphine 106
		"FROM {$source}_tapir WHERE ".$this->construireWhereTaxonMoissonnage($source)." ".
747 alex 107
		"AND lieu_station_longitude BETWEEN ".$this->limitesCarte['ouest']." AND ".$this->limitesCarte['est']." ".
108
		"AND lieu_station_latitude BETWEEN ".$this->limitesCarte['sud']." AND ".$this->limitesCarte['nord']." ".
949 raphael 109
		"AND Length(lieu_commune_code_insee)=5 ORDER BY lieu_commune_code_insee, date"." -- " . __FILE__ . ":" . __LINE__." ". @$_SERVER['REQUEST_URI'];
1200 delphine 110
		$stations = $this->bdd->recupererTous($requete);
747 alex 111
		$this->rechercherInfosCommune($stations);
112
		return $stations;
113
	}
114
 
1192 delphine 115
	public function recupererStationsVigieFlore() {
116
		$this->bdd->requeter("USE ".Config::get('bdd_nom'));
117
		$requete =
118
		"SELECT DISTINCTROW lieu_commune_code_insee AS commune, Floor(lieu_station_longitude*10)/10 as lng, Floor(lieu_station_latitude*10)/10 as lat, observation_date AS date, observateur_nom_complet AS auteur ".
1193 delphine 119
		"FROM vigie_flore_tapir WHERE ".$this->construireWhereTaxonMoissonnage('vigie_flore')." ".
1192 delphine 120
		"AND lieu_station_longitude BETWEEN ".$this->limitesCarte['ouest']." AND ".$this->limitesCarte['est']." ".
121
		"AND lieu_station_latitude BETWEEN ".$this->limitesCarte['sud']." AND ".$this->limitesCarte['nord']." ".
122
		" ORDER BY lat DESC, lng ASC, lieu_commune_code_insee, date"." -- " . __FILE__ . ":" . __LINE__." ". @$_SERVER['REQUEST_URI'];
123
		$stations = $this->bdd->recupererTous($requete);
124
		return $stations;
125
	}
126
 
1204 delphine 127
	public function recupererStationsPhotoflora() {
128
		$this->bdd->requeter("USE ".Config::get('bdd_nom'));
129
		$requete =
130
		"SELECT DISTINCTROW lieu_commune_code_insee, observation_date AS date, observateur_nom_complet AS auteur ".
131
		"FROM photoflora_tapir WHERE ".$this->construireWhereTaxonMoissonnage('photoflora')." ".
132
		"AND Length(lieu_commune_code_insee)=5 ORDER BY lieu_commune_code_insee, date"." -- " . __FILE__ . ":" . __LINE__." ". @$_SERVER['REQUEST_URI'];
133
		$stations = $this->bdd->recupererTous($requete);
134
		$this->rechercherInfosCommune($stations);
135
		return $stations;
136
	}
137
 
138
 
970 delphine 139
	private function construireWhereTaxonMoissonnage($source) {
747 alex 140
		$nomRang = $this->obtenirNomRang();
141
		$criteres = array();
1204 delphine 142
		$criteres[] = (in_array($source, array("baznat", "ifn", "vigie_flore", "photoflora")) ) ?
998 delphine 143
						"num_nom = ".$this->taxon['num_nom'] :
144
						"nom_scientifique_complet LIKE ".$this->bdd->proteger($this->taxon['nom_sci']) ;
747 alex 145
		if ($this->nomRang == 'espece' || $this->nomRang == 'sous_espece') {
146
			foreach ($this->taxons as $sousTaxon) {
1200 delphine 147
				if ($source == "sophy") {
1205 delphine 148
					if (isset($sousTaxon['num_nom_retenu']) && $sousTaxon['num_nom'] == $sousTaxon['num_nom_retenu']) {
1200 delphine 149
						$criteres[] =
150
						"nom_scientifique_complet LIKE ".$this->bdd->proteger($sousTaxon['nom_sci']) ;
151
					}
152
				} else {
1204 delphine 153
					$criteres[] = (in_array($source, array("baznat", "ifn", "vigie_flore", "photoflora"))) ?
998 delphine 154
										"num_nom = ".$sousTaxon['num_nom'] :
155
										"nom_scientifique_complet LIKE ".$this->bdd->proteger($sousTaxon['nom_sci']) ;
1200 delphine 156
				}
747 alex 157
			}
158
		} elseif ($this->nomRang == 'famille') {
159
			foreach ($this->genres as $genre) {
160
				$criteres[] = "nom_scientifique_complet LIKE ".$this->bdd->proteger($genre['nom_sci']."%");
161
			}
162
		}
163
		return "(".implode(' OR ',array_unique($criteres)).")";
1200 delphine 164
 
747 alex 165
	}
166
 
167
	private function rechercherInfosCommune(& $stations) {
168
		$codesInsee = array();
169
		foreach ($stations as $station) {
170
			$codeInsee = $station['lieu_commune_code_insee'];
171
			if (substr($codeInsee, 0, 2) == '20') {
172
				$codeInsee  = '2A'.substr($codeInsee, 2);
173
				$codeInsee2 = '2B'.substr($codeInsee, 2);
174
			}
175
			if (!in_array($codeInsee, $codesInsee)) {
176
				if (substr($codeInsee, 0, 2) == '20') {
177
					$codesInsee[] = "'$codeInsee2'";
178
				}
179
				$codesInsee[] = "'$codeInsee'";
180
			}
181
		}
751 alex 182
		$nomTableCommunes = Config::get('bdd_table_communes');
860 aurelien 183
		$communes = array();
184
		if(count($codesInsee) > 0) {
185
			$requete =
186
			"SELECT insee, nom AS commune, Floor(latitude_degre*10)/10 AS lat, Floor(longitude_degre*10)/10 AS lng ".
187
			"FROM $nomTableCommunes WHERE insee IN (".implode(',', array_unique($codesInsee)).") ORDER BY insee";
188
			$communes = $this->bdd->recupererTous($requete);
189
		}
747 alex 190
		$indexStation = 0;
191
		foreach ($communes as $commune) {
192
			$codeInsee = $commune['insee'];
193
			if (substr($codeInsee, 0, 2) == '2A' || substr($codeInsee, 0, 2) == '2B') {
194
				$codeInsee = '20'.substr($codeInsee, 2);
195
			}
196
			while ($stations[$indexStation]['lieu_commune_code_insee'] < $codeInsee) {
197
				$indexStation ++;
198
			}
199
			if ($stations[$indexStation]['lieu_commune_code_insee'] == $codeInsee) {
200
				$stations[$indexStation]['lat'] = $commune['lat'];
201
				$stations[$indexStation]['lng'] = $commune['lng'];
202
				$stations[$indexStation]['commune'] = $commune['commune'];
203
			}
204
		}
205
 
206
		$lat = array();
207
		$lng = array();
208
		foreach ($stations as $index => $station) {
209
			if (!isset($station['lat'])) {
210
				$station['lat'] = -100;
211
				$station['lng'] = -100;
212
			}
213
			$lat[$index] = $station['lat'];
214
			$lng[$index] = $station['lng'];
215
		}
216
		array_multisort($lat, SORT_DESC, $lng, SORT_ASC, $stations);
217
	}
218
 
727 alex 219
}
220
 
221
?>