Subversion Repositories eFlore/Projets.eflore-projets

Rev

Rev 860 | Rev 886 | Go to most recent revision | Only display areas with differences | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

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