Subversion Repositories eFlore/Applications.moissonnage

Rev

Rev 26 | Rev 43 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 26 Rev 31
Line 41... Line 41...
41 
	public function __construct($criteresRecherche) {
 41 
	public function __construct($criteresRecherche) {
42 
		$this->criteresRecherche = $criteresRecherche;
 42 
		$this->criteresRecherche = $criteresRecherche;
43 
	}
 43 
	}
Line 44... Line 44...
44
44
45 
	public function recupererStations() {
 - 
 
46 
		// recuperer les informations au niveau de la base de donnees
 45 
	public function recupererStations() {
47 
		$requeteSql = $this->construireRequeteStations();
 46 
		$requeteSql = $this->construireRequeteStations();
48 
		$stations = $this->getBdd()->recupererTous($requeteSql);
 - 
 
49
47 
		$stations = $this->getBdd()->recupererTous($requeteSql);
50 
		$zoomMaxMaillage = Config::get('zoom_maximal_maillage');
 48 
		$zoomMaxMaillage = Config::get('zoom_maximal_maillage');
51 
		$seuilMaillage   = Config::get('seuil_maillage');
 49 
		$seuilMaillage   = Config::get('seuil_maillage');
52 
		$zoom = $this->criteresRecherche->zoom;
 50 
		$zoom = $this->criteresRecherche->zoom;
53 
		$bbox = $this->criteresRecherche->bbox;
 51 
		$bbox = $this->criteresRecherche->bbox;
54 
		// TODO: gérer une notion d'échelle plutot que de zoom (pour les clients SIG)
 52 
		// TODO: gérer une notion d'échelle plutot que de zoom (pour les clients SIG)
55 
		if (count($stations) > $seuilMaillage && intval($zoom)<= $zoomMaxMaillage) {
 - 
 
56 
			// partitionnement des donnees en mailles
 53 
		if (count($stations) > $seuilMaillage && intval($zoom)<= $zoomMaxMaillage) {
57 
			$maillage = new Maillage($bbox, $zoom);
 54 
			$maillage = new Maillage($bbox, $zoom);
58 
			$maillage->genererMaillesVides();
 55 
			$maillage->genererMaillesVides();
59 
			$maillage->ajouterPoints($stations);
 56 
			$maillage->ajouterPoints($stations);
60 
			$stations = $maillage->formaterSortie();
 57 
			$stations = $maillage->formaterSortie();
61 
		}
 - 
 
62
- 
 
63 
		// mettre en forme les informations au format JSON
 58 
		}
64 
		$formateurJSON = new FormateurJson('floradata');
 59 
		$formateurJSON = new FormateurJson('floradata');
65 
		$donneesFormatees = $formateurJSON->formaterStations($stations);
 60 
		$donneesFormatees = $formateurJSON->formaterStations($stations);
66 
		return $donneesFormatees;
 61 
		return $donneesFormatees;
Line 67... Line 62...
67 
	}
 62 
	}
68
- 
 
69 
	public function recupererObservations() {
 63
70 
		// recuperer les informations sur les stations repondant a ces parametres
 64 
	public function recupererObservations() {
71 
		$requeteSql = $this->construireRequeteObservations();
 65 
		$requeteSql = $this->construireRequeteObservations();
72 
		$observations = $this->getBdd()->recupererTous($requeteSql);
 - 
 
73 
		$nomSite = $this->obtenirNomStation();
 - 
 
74
66 
		$observations = $this->getBdd()->recupererTous($requeteSql);
75 
		// mettre en forme les informations au format JSON
 67 
		$nomSite = $this->obtenirNomStation();
76 
		$formateurJSON = new FormateurJson('floradata');
 68 
		$formateurJSON = new FormateurJson('floradata');
77 
		$donneesFormatees = $formateurJSON->formaterObservations($observations, $nomSite);
 69 
		$donneesFormatees = $formateurJSON->formaterObservations($observations, $nomSite);
Line 91... Line 83...
91 
		$this->bdd->requeter("USE ".Config::get('bdd_nom'));
 83 
		$this->bdd->requeter("USE ".Config::get('bdd_nom'));
92 
		return $this->bdd;
 84 
		return $this->bdd;
93 
	}
 85 
	}
Line 94... Line 86...
94
86
- 
 
 87 
	private function construireRequeteStations() {
95 
	private function construireRequeteStations() {
 88 
		$bbox = $this->criteresRecherche->bbox;
96 
		$selectTypeSite =
 89 
		$selectTypeSite =
97 
			"IF(".
 90 
			"IF(".
98 
				"(longitude IS NULL OR latitude IS NULL) ".
 91 
				"(longitude IS NULL OR latitude IS NULL) ".
99 
				"OR (longitude=0 AND latitude=0) ".
 92 
				"OR (longitude=0 AND latitude=0) ".
- 
 
 93 
				"OR (mots_cles_texte LIKE '%sensible%') ".
- 
 
 94 
				"OR NOT (".
- 
 
 95 
					"longitude BETWEEN ".$bbox['ouest']." AND ".$bbox['est']." ".
- 
 
 96 
					"AND latitude BETWEEN ".$bbox['sud']." AND ".$bbox['nord'].
100 
				"OR (longitude=999.99999 AND latitude=999.99999)".
 97 
				")".
101 
				", 'COMMUNE', 'STATION'".
 98 
				", 'COMMUNE', 'STATION'".
102 
			")";
 99 
			")";
103 
		$requete =
100 
		$requete =
104 
			'SELECT ce_zone_geo, zone_geo, station, longitude, latitude, nom AS "nom_commune",'.
 101 
			"SELECT COUNT(id_observation) AS observations, ce_zone_geo, zone_geo, station, ".
105 
			'wgs84_longitude AS "lng_commune", wgs84_latitude AS "lat_commune", '.
 102 
			"longitude, latitude, nom AS nom_commune,wgs84_longitude AS lng_commune, ".
106 
			$selectTypeSite.' AS "type_site" '.
 103 
			"wgs84_latitude AS lat_commune, ".$selectTypeSite." AS type_site ".
107 
			'FROM cel_obs  LEFT JOIN cel_zones_geo cz ON ce_zone_geo=id_zone_geo '.
 104 
			"FROM cel_obs  LEFT JOIN cel_zones_geo cz ON ce_zone_geo=id_zone_geo ".
108 
			'WHERE transmission=1 '.
 105 
			"WHERE transmission=1 ".
109 
			$this->construireWhereDepartement().' '.
 106 
			$this->construireWhereDepartement().' '.
- 
 
 107 
			$this->construireWhereAuteur().' '.
110 
			$this->construireWhereAuteur().' '.
 108 
			$this->construireWhereDate().' '.
111 
			$this->construireWhereReferentiel().' '.
 109 
			$this->construireWhereReferentiel().' '.
112 
			$this->construireWhereTaxon().' '.
 110 
			$this->construireWhereTaxon().' '.
113 
			$this->construireWhereCoordonneesBbox().' '.
 111 
			$this->construireWhereCoordonneesBbox().' '.
114 
			"GROUP BY longitude, latitude, wgs84_longitude, wgs84_latitude";
 112 
			"GROUP BY longitude, latitude, wgs84_longitude, wgs84_latitude";
115 
		return $requete;
 113 
		return $requete;
Line 116... Line 114...
116 
	}
 114 
	}
117
115
118 
	private function construireRequeteObservations() {
 116 
	private function construireRequeteObservations() {
119 
		$requete =
 117 
		$requete =
120 
			"SELECT id_observation AS id_obs, nom_ret_nn AS nn, nom_ret AS nomSci, ".
 118 
			"SELECT id_observation AS id_obs, nom_ret_nn AS nn, nom_ret AS nomSci, ".
121 
			"Date(date_transmission)  AS date, milieu AS lieu, nom_referentiel, ".
 119 
			"Date(date_observation) AS date, milieu AS lieu, nom_referentiel, ".
122 
			"Concat(prenom_utilisateur, ' ', nom_utilisateur) AS observateur, ce_utilisateur AS observateurId ".
 120 
			"Concat(prenom_utilisateur, ' ', nom_utilisateur) AS observateur, ce_utilisateur AS observateurId ".
123 
			"FROM cel_obs WHERE transmission=1 ".
 121 
			"FROM cel_obs WHERE transmission=1 ".
- 
 
 122 
			$this->construireWhereAuteur().' '.
124 
			$this->construireWhereAuteur().' '.
 123 
			$this->construireWhereReferentiel().' '.
125 
			$this->construireWhereReferentiel().' '.
 124 
			$this->construireWhereDate().' '.
126 
			$this->construireWhereTaxon().' '.
 125 
			$this->construireWhereTaxon().' '.
127 
			$this->construireWhereCoordonneesPoint().' '.
 126 
			$this->construireWhereCoordonneesPoint().' '.
128 
			"ORDER BY nom_ret, date, observateur";
 127 
			"ORDER BY nom_ret, date, observateur";
Line 129... Line 128...
129 
		return $requete;
 128 
		return $requete;
130 
	}
 129 
	}
131
130
132 
	private function construireWhereTaxon() {
 131 
	protected function construireWhereTaxon() {
133 
		$sql = '';
 132 
		$sql = '';
134 
		if (isset($this->criteresRecherche->taxon)) {
 - 
 
135 
			$taxon = $this->criteresRecherche->taxon;
 133 
		if (isset($this->criteresRecherche->taxon)) {
136 
			$criteres = "nom_ret LIKE ".$this->getBdd()->proteger($taxon['nom']."%");
 134 
			$taxons = $this->criteresRecherche->taxon;
137
135 
			$criteres = array();
- 
 
 136 
			foreach ($taxons as $taxon) {
- 
 
 137 
				$nomRang = $this->getNomRang($taxon);
138 
			$referentiel = new Referentiel($this->criteresRecherche->referentiel, $this->criteresRecherche->taxon);
 138 
				if ($nomRang == 'genre') {
- 
 
 139 
					$criteres[] = "famille=".$this->getBdd()->proteger($taxon['nom']);
- 
 
 140 
				} else {
- 
 
 141 
					$criteres[] = "nom_ret LIKE ".$this->getBdd()->proteger($taxon['nom']."%");
- 
 
 142 
					if ($nomRang == 'espece') {
139 
			$sousTaxons = $referentiel->recupererSousTaxons();
 143 
						$criteres = array_merge($criteres, $this->concatenerTaxonsSousEspeces($taxon));
140 
			foreach ($sousTaxons as $sousTaxon) {
 144 
					}
141 
				$criteres .= " OR nom_ret LIKE ".$this->getBdd()->proteger($taxon['nom']."%");
 145 
				}
142 
			}
 146 
			}
143 
			$sql = "AND ($criteres)";
 147 
			$sql = "AND (".implode(' OR ',array_unique($criteres)).")";
Line -... Line 148...
- 
 
 148 
		}
- 
 
 149 
		return $sql;
- 
 
 150 
	}
- 
 
 151
- 
 
 152 
	protected function getNomRang($taxon) {
- 
 
 153 
		$nomsRangs = array('famille', 'genre', 'espece', 'sous_espece');
- 
 
 154 
		for ($index = 0; $index < count($nomsRangs)
- 
 
 155 
		&& Config::get("rang.".$nomsRangs[$index]) != $taxon['rang']; $index ++);
- 
 
 156 
		$position = $index == count($nomsRangs) ? count($nomsRangs)-1 : $index;
- 
 
 157 
		return $nomsRangs[$position];
- 
 
 158 
	}
- 
 
 159
- 
 
 160 
	protected function concatenerTaxonsSousEspeces($taxon) {
- 
 
 161 
		$referentiel = new Referentiel($this->criteresRecherche->referentiel, $taxon);
- 
 
 162 
		$sousTaxons = $referentiel->recupererTaxonsSousEspeces();
- 
 
 163 
		$criteres = array();
- 
 
 164 
		foreach ($sousTaxons as $sousTaxon) {
- 
 
 165 
			$criteres[] = "nom_ret LIKE ".$this->getBdd()->proteger($sousTaxon['nom']."%");
- 
 
 166 
		}
- 
 
 167 
		return $criteres;
- 
 
 168 
	}
- 
 
 169
- 
 
 170 
	protected function concatenerTaxonsFamilles($taxon) {
- 
 
 171 
		$referentiel = new Referentiel($this->criteresRecherche->referentiel, $taxon);
- 
 
 172 
		$sousTaxons = $referentiel->recupererTaxonsFamilles();
- 
 
 173 
		$criteres = array();
- 
 
 174 
		foreach ($sousTaxons as $sousTaxon) {
- 
 
 175 
			$criteres[] = "nom_ret LIKE ".$this->getBdd()->proteger($sousTaxon['nom']."%");
144 
		}
 176 
		}
145 
		return $sql;
 177 
		return $criteres;
146 
	}
 178 
	}
147
179
148 
	private function construireWhereReferentiel() {
 180 
	private function construireWhereReferentiel() {
Line 174... Line 206...
174 
			$sql = "AND courriel_utilisateur = $utilisateur";
 206 
			$sql = "AND courriel_utilisateur = $utilisateur";
175 
		}
 207 
		}
176 
		return $sql;
 208 
		return $sql;
177 
	}
 209 
	}
Line -... Line 210...
- 
 
 210
- 
 
 211 
	private function construireWhereDate() {
- 
 
 212 
		$sql = '';
- 
 
 213 
		if (isset($this->criteresRecherche->nbJours)) {
- 
 
 214 
			$nbJours = $this->criteresRecherche->nbJours;
- 
 
 215 
			$sql = "AND (Datediff(Curdate(), date_creation)<={$nbJours})";
- 
 
 216 
		} else {
- 
 
 217 
			$sql = $this->construireWhereDateDebutEtFin();
- 
 
 218 
		}
- 
 
 219 
		return $sql;
- 
 
 220 
	}
- 
 
 221
- 
 
 222 
	private function construireWhereDateDebutEtFin() {
- 
 
 223 
		$sql = '';
- 
 
 224 
		$dateDebut = isset($this->criteresRecherche->dateDebut) ? $this->criteresRecherche->dateDebut : null;
- 
 
 225 
		$dateFin   = isset($this->criteresRecherche->dateFin) ? $this->criteresRecherche->dateFin : null;
- 
 
 226 
		if (!is_null($dateDebut) || !is_null($dateFin)) {
- 
 
 227 
			$dateFin = !is_null($dateFin) ? $dateFin : date('Y-m-d');
- 
 
 228 
			$condition = '';
- 
 
 229 
			if ($dateDebut == $dateFin) {
- 
 
 230 
				$condition = "Date(date_observation)=".$this->getBdd()->proteger($dateDebut);
- 
 
 231 
			} elseif (is_null($dateFin)) {
- 
 
 232 
				$condition = "Date(date_observation)>=".$this->getBdd()->proteger($dateDebut);
- 
 
 233 
			} elseif (is_null($dateDebut)) {
- 
 
 234 
				$condition = "Date(date_observation)<=".$this->getBdd()->proteger($dateFin);
- 
 
 235 
			} else {
- 
 
 236 
				$condition = "Date(date_observation) BETWEEN ".$this->getBdd()->proteger($dateDebut)." ".
- 
 
 237 
						"AND ".$this->getBdd()->proteger($dateFin);
- 
 
 238 
			}
- 
 
 239 
			$sql = "AND ($condition)";
- 
 
 240 
		}
- 
 
 241 
		return $sql;
- 
 
 242 
	}
178
243
179 
	private function construireWhereCoordonneesBbox() {
 244 
	private function construireWhereCoordonneesBbox() {
180 
		$bbox = $this->criteresRecherche->bbox;
 245 
		$bbox = $this->criteresRecherche->bbox;
181 
		$sql =
 246 
		$sql =
182 
			"AND (".
 247 
			"AND (".
183 
				"(".
 248 
				"(".
184 
					"longitude BETWEEN ".$bbox['ouest']." AND ".$bbox['est']." ".
 249 
					"latitude BETWEEN ".$bbox['sud']." AND ".$bbox['nord']." ".
185 
					"AND latitude BETWEEN ".$bbox['sud']." AND ".$bbox['nord']." ".
 - 
 
186 
					"AND (wgs84_longitude IS NULL OR wgs84_latitude IS NULL)".
 250 
					"AND longitude BETWEEN ".$bbox['ouest']." AND ".$bbox['est'].
- 
 
 251 
				") OR (".
- 
 
 252 
					"((longitude IS NULL OR latitude IS NULL) OR (longitude=0 AND latitude=0) ".
187 
				") OR (".
 253 
					"OR (longitude>180 AND latitude>90)) AND ".
188 
					"wgs84_longitude BETWEEN ".$bbox['ouest']." AND ". $bbox['est']." ".
 254 
					"wgs84_longitude BETWEEN ".$bbox['ouest']." AND ". $bbox['est']." ".
189 
					"AND wgs84_latitude BETWEEN ".$bbox['sud']." AND ".$bbox['nord'].
 255 
					"AND wgs84_latitude BETWEEN ".$bbox['sud']." AND ".$bbox['nord'].
190 
				")".
 256 
				")".
191 
			")";
 257 
			")";
192 
		return $sql;
 258 
		return $sql;
Line 193... Line 259...
193 
	}
 259 
	}
194
260
- 
 
 261 
	private function construireWhereCoordonneesPoint() {
195 
	private function construireWhereCoordonneesPoint() {
 262 
		$longitude = $this->criteresRecherche->longitude;
- 
 
 263 
		$latitude  = $this->criteresRecherche->latitude;
196 
		$commune = 	$this->obtenirCoordonneesCommune();
 264 
		$condition = "(longitude=".$longitude." AND latitude=".$latitude." ".
197 
		$condition = "(longitude=".$this->criteresRecherche->longitude." ".
 265 
			"AND (mots_cles_texte IS NULL OR mots_cles_texte NOT LIKE '%sensible%'))";
- 
 
 266 
		if ($this->criteresRecherche->typeSite == 'commune') {
- 
 
 267 
			$commune = $this->obtenirCoordonneesCommune();
198 
			"AND latitude=".$this->criteresRecherche->latitude.")";
 268 
			$condition .=
- 
 
 269 
				" OR (".
199 
		if (!is_null($commune)) {
 270 
					"((longitude IS NULL OR latitude IS NULL) OR (longitude=0 AND latitude=0) ".
- 
 
 271 
					"OR (longitude>180 AND latitude>90)) ".
200 
			$condition .= " OR (longitude IS NULL AND latitude IS NULL AND ce_zone_geo=".
 272 
					"AND ce_zone_geo=".$this->getBdd()->proteger($commune['id_zone_geo']).
201 
				$this->getBdd()->proteger($commune['id_zone_geo']).")";
 273 
				")";
202 
		}
 274 
		}
Line 203... Line 275...
203 
		return "AND ($condition)";
 275 
		return "AND ($condition)";