WebSVN – eFlore/Projets.eflore-projets – Diff – /trunk/services/modules/0.1/moissonnage/cartes/SourceDonnees.php


<?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_export_total 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;
	}
	
	public function recupererStationsVigieFlore() {
		$this->bdd->requeter("USE ".Config::get('bdd_nom'));
		$requete =
		"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 ".
		"FROM vigie_flore_tapir WHERE ".$this->construireWhereTaxonMoissonnage('vigie_flore')." ".
		"AND lieu_station_longitude BETWEEN ".$this->limitesCarte['ouest']." AND ".$this->limitesCarte['est']." ".
		"AND lieu_station_latitude BETWEEN ".$this->limitesCarte['sud']." AND ".$this->limitesCarte['nord']." ".
		" ORDER BY lat DESC, lng ASC, lieu_commune_code_insee, date"." -- " . __FILE__ . ":" . __LINE__." ". @$_SERVER['REQUEST_URI'];
		$stations = $this->bdd->recupererTous($requete);
		return $stations;
	}
	
	public function recupererStationsPhotoflora() {
		$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 photoflora_tapir WHERE ".$this->construireWhereTaxonMoissonnage('photoflora')." ".
		"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[] = (in_array($source, array("baznat", "ifn", "vigie_flore", "photoflora")) ) ? 
						"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) {
				if ($source == "sophy") {
					if (isset($sousTaxon['num_nom_retenu']) && $sousTaxon['num_nom'] == $sousTaxon['num_nom_retenu']) {
						$criteres[] =
						"nom_scientifique_complet LIKE ".$this->bdd->proteger($sousTaxon['nom_sci']) ;
					}
				} else {
					$criteres[] = (in_array($source, array("baznat", "ifn", "vigie_flore", "photoflora"))) ?
										"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);
	}
	
}
 
?>

Subversion Repositories eFlore/Projets.eflore-projets

(root)/trunk/services/modules/0.1/moissonnage/cartes/SourceDonnees.php – Rev 1295 → 1296

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