Subversion Repositories eFlore/Applications.cel

Rev

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

Rev 1121 Rev 1122
Line 139... Line 139...
139 
	}
 139 
	}
Line 140... Line 140...
140 
 
 140 
 
Line 141... Line 141...
141 
	public function getTout($params) {
 141 
	public function getTout($params) {
142 
 
 142 
 
143 
		$emplacements = null;
 143 
		$emplacements = null;
- 
 
 144 
		$coordonnees_nulles = "coord_x = '000null' OR coord_x = '' ".
144 
		$requete =  'SELECT DISTINCT location AS nom, '.
 145 
								" OR coord_y = '000null' OR coord_y = ''";
- 
 
 146 
		$coordonnees_non_nulles = "coord_x != '000null' AND coord_x != '' ".
- 
 
 147 
								" AND coord_y != '000null' AND coord_y != ''";
- 
 
 148 
		$coordonnees_non_nulles_pas_sensible = "(".$coordonnees_non_nulles." AND mots_cles_texte NOT LIKE '%sensible%')";
- 
 
 149
145 
							"IF ((coord_x != '000null' AND coord_x != '' ".
 150 
		$requete =  'SELECT DISTINCT *, location AS nom, '.
146 
								" AND coord_y != '000null' AND coord_y != ''), ".
 151 
							"IF (".$coordonnees_non_nulles_pas_sensible.", ".
147 
								"'stations', ".
 152 
								"'stations', ".
148 
							"'communes' ".
 153 
							"'communes' ".
149 
							") as type_emplacement, ".
 - 
 
150 
							"IF ((coord_x != '000null' AND coord_x != '' ".
 154 
							") as type_emplacement, ".
151 
								" AND coord_y != '000null' AND coord_y != ''), ".
 155 
							"IF (".$coordonnees_non_nulles_pas_sensible.", ".
152 
								"coord_x, ".
 156 
								"coord_x, ".
153 
								"wgs84_latitude ".
 157 
								"wgs84_latitude ".
154 
							") as lat, ".
 - 
 
155 
							"IF ((coord_x != '000null' AND coord_x != '' ".
 158 
							") as lat, ".
156 
								" AND coord_y != '000null' AND coord_y != ''), ".
 159 
							"IF (".$coordonnees_non_nulles_pas_sensible.", ".
157 
								"coord_y, ".
 160 
								"coord_y, ".
158 
								"wgs84_longitude ".
 161 
								"wgs84_longitude ".
159 
							") as lng, ".
 - 
 
160 
							"IF ((coord_x != '000null' AND coord_x != '' ".
 162 
							") as lng, ".
161 
								" AND coord_y != '000null' AND coord_y != ''), ".
 163 
							"IF (".$coordonnees_non_nulles_pas_sensible.", ".
162 
								'CONCAT("'.self::MARQUEUR_STATION.':",coord_x,"|",coord_y), '.
 164 
								'CONCAT("'.self::MARQUEUR_STATION.':",coord_x,"|",coord_y), '.
163 
								'CONCAT("'.self::MARQUEUR_COMMUNE.':",wgs84_latitude,"|",wgs84_longitude) '.
 165 
								'CONCAT("'.self::MARQUEUR_COMMUNE.':",wgs84_latitude,"|",wgs84_longitude) '.
164 
							") as id ".
 166 
							") as id ".
Line 170... Line 172...
170 
						"(".($this->construireWhereRectangleStationOR() != null ? $this->construireWhereRectangleStationOR().' AND ' : '').
 172 
						"(".($this->construireWhereRectangleStationOR() != null ? $this->construireWhereRectangleStationOR().' AND ' : '').
171 
							" (mots_cles_texte IS NULL OR mots_cles_texte NOT LIKE '%sensible%') ".
 173 
							" (mots_cles_texte IS NULL OR mots_cles_texte NOT LIKE '%sensible%') ".
172 
						")".
 174 
						")".
173 
						" OR ".
 175 
						" OR ".
174 
						" ( ".
 176 
						" ( ".
175 
							"(".($this->construireWhereRectangleCommuneOR() != null ? $this->construireWhereRectangleCommuneOR().' Or ' : '').
 177 
							"(".($this->construireWhereRectangleCommuneOR() != null ? $this->construireWhereRectangleCommuneOR().' AND ' : '').
176 
							" mots_cles_texte LIKE '%sensible%' ".
 178 
								"(".$coordonnees_nulles.")".
177 
							")".
 179 
							")".
178 
						" ) ".
 180 
						" ) ".
179 
					") ".
 181 
					") ".
180 
					$this->construireWhereDept().
 182 
					$this->construireWhereDept().
181 
                    $this->construireWhereCommune().
 183 
                    $this->construireWhereCommune().
Line 183... Line 185...
183 
					$this->construireWhereNumTaxon().
 185 
					$this->construireWhereNumTaxon().
184 
					$this->construireWhereNomTaxon().
 186 
					$this->construireWhereNomTaxon().
185 
					$this->construireWhereDate().
 187 
					$this->construireWhereDate().
186 
					$this->construireWhereCommentaire().
 188 
					$this->construireWhereCommentaire().
187 
					$this->construireWhereProjet().
 189 
					$this->construireWhereProjet().
188 
					$this->construireWhereTag();
 190 
					$this->construireWhereTag().' LIMIT 0,20000';
189 
 
 191
190 
		$resultats_emplacements = $this->executerRequete($requete);
 192 
		$resultats_emplacements = $this->executerRequete($requete);
191 
		$emplacements = $this->traiterEmplacements($resultats_emplacements, $this->compterObservations($params));
 193 
		$emplacements = $this->traiterEmplacements($resultats_emplacements, $this->compterObservations($params));
192 
 
 - 
 
193 
		return $emplacements;
 194 
		return $emplacements;
194 
	}
 195 
	}
- 
 
 196
- 
 
 197 
	private function afficherRequeteFormatee($requete) {
- 
 
 198 
		$requete = str_replace(')',')<br />',$requete);
- 
 
 199 
		$requete = str_replace('(','	<br />	(',$requete);
- 
 
 200 
		echo '<pre>'.$requete.'</pre>';
- 
 
 201 
		exit;
- 
 
 202 
	}
Line 195... Line 203...
195 
 
 203 
 
Line 196... Line 204...
196 
	private $nb_obs = 0;
 204 
	private $nb_obs = 0;
197 
 
 205 
 
Line 241... Line 249...
241 
			$marqueurs['points'] = CartoGroupage::creerGroupesQuadtree($emplacements, $ne['lat'], $ne['lng'], $sw['lat'], $sw['lng'], $zoom);
 249 
			$marqueurs['points'] = CartoGroupage::creerGroupesQuadtree($emplacements, $ne['lat'], $ne['lng'], $sw['lat'], $sw['lng'], $zoom);
Line 242... Line 250...
242 
 
 250 
 
243 
			// laisser la classe cartoGroupage compter les élements simplifie le comptage
 251 
			// laisser la classe cartoGroupage compter les élements simplifie le comptage
244 
			// et permet de ne pas reparser le tableau pour compter les différents éléments
 252 
			// et permet de ne pas reparser le tableau pour compter les différents éléments
245 
			$nb_elements = CartoGroupage::getNbElements();
 253 
			$nb_elements = CartoGroupage::getNbElements();
- 
 
 254
246 
 
 255 
			$marqueurs['stats']['coordmax'] = CartoGroupage::getBornes();
247 
			$marqueurs['stats']['stations'] = $nb_elements['stations'];
 256 
			$marqueurs['stats']['stations'] = $nb_elements['stations'];
248 
			$marqueurs['stats']['communes'] = $nb_elements['communes'];
 257 
			$marqueurs['stats']['communes'] = $nb_elements['communes'];
249 
			$marqueurs['stats']['observations'] = (int)$nb_total_observation;
 258 
			$marqueurs['stats']['observations'] = (int)$nb_total_observation;
250 
		} else {
 259 
		} else {
Line 382... Line 391...
382 
						'	sector, x_utm, y_utm, insee_code, date_transmission '.
 391 
						'	sector, x_utm, y_utm, insee_code, date_transmission '.
383 
						'FROM cel_inventory AS i '.
 392 
						'FROM cel_inventory AS i '.
384 
						'	LEFT JOIN locations AS l '.
 393 
						'	LEFT JOIN locations AS l '.
385 
						"		ON (l.name = i.location AND l.code = i.id_location) ".
 394 
						"		ON (l.name = i.location AND l.code = i.id_location) ".
386 
						"WHERE transmission = '1' ".
 395 
						"WHERE transmission = '1' ".
387 
						$this->construireWhereCoordonnees().
 396 
						((!$this->etreNull($this->parametres['commune'])) ? $this->construireWhereCommuneSansCoordonneesAvecSensibles() : $this->construireWhereCoordonneesSansSensibles()).
388 
						$this->construireWhereUtilisateur().
 397 
						$this->construireWhereUtilisateur().
389 
						$this->construireWhereNumTaxon().
 398 
						$this->construireWhereNumTaxon().
390 
						$this->construireWhereNomTaxon().
 399 
						$this->construireWhereNomTaxon().
391 
						$this->construireWhereDate().
 400 
						$this->construireWhereDate().
392 
						$this->construireWhereCommentaire().
 401 
						$this->construireWhereCommentaire().
Line 486... Line 495...
486 
	}
 495 
	}
Line 487... Line 496...
487 
 
 496 
 
488 
	private function ajouterImagesAuxObs($observations) {
 497 
	private function ajouterImagesAuxObs($observations) {
489 
		$images = $this->chargerImages(array_keys($observations['observations']));
 498 
		$images = $this->chargerImages(array_keys($observations['observations']));
- 
 
 499 
		foreach ($observations['observations'] as $id => $infos) {
490 
		foreach ($observations['observations'] as $id => $infos) {
 500 
			if(isset($images[$id])) {
491 
			$infos['images'] = $images[$id];
 501 
				$infos['images'] = $images[$id];
- 
 
 502 
				$observations['observations'][$id] = $infos;
492 
			$observations['observations'][$id] = $infos;
 503 
			}
493 
		}
 504 
		}
494 
		return $observations;
 505 
		return $observations;
Line 495... Line 506...
495 
	}
 506 
	}
Line 584... Line 595...
584 
				$sql = " AND (coord_x = $lat AND coord_y = $lng) ";
 595 
				$sql = " AND (coord_x = $lat AND coord_y = $lng) ";
585 
			}
 596 
			}
586 
		}
 597 
		}
587 
		return $sql;
 598 
		return $sql;
588 
	}
 599 
	}
- 
 
 600
- 
 
 601 
	private function construireWhereCoordonneesSansSensibles() {
- 
 
 602 
		$sql = '(';
- 
 
 603 
		// Récupération des coordonnées depuis l'id station
- 
 
 604 
		extract($this->decomposerParametreStation());
- 
 
 605 
		if (isset($type)) {
- 
 
 606 
			if ($type == self::MARQUEUR_COMMUNE) {
- 
 
 607 
				$lat = $this->proteger($lat);
- 
 
 608 
				$lng = $this->proteger($lng);
- 
 
 609 
				$sql = " AND (ROUND(wgs84_latitude,5) = ROUND($lat,5) AND ROUND(wgs84_longitude,5) = ROUND($lng,5)) ";
- 
 
 610 
			}  else if ($type == self::MARQUEUR_STATION) {
- 
 
 611 
				$lat = $this->proteger($lat);
- 
 
 612 
				$lng = $this->proteger($lng);
- 
 
 613 
				$sql = " AND (coord_x = $lat AND coord_y = $lng) ";
- 
 
 614 
			}
- 
 
 615 
		}
- 
 
 616 
		$sql .= ' AND mots_cles_texte NOT LIKE %sensible%';
- 
 
 617 
		return $sql;
- 
 
 618 
	}
Line 589... Line 619...
589 
 
 619 
 
590 
	private function construireWhereCommentaire() {
 620 
	private function construireWhereCommentaire() {
591 
		$sql = '';
 621 
		$sql = '';
592 
		list($type, $commentaire) = $this->decomposerParametreCommentaire();
 622 
		list($type, $commentaire) = $this->decomposerParametreCommentaire();
Line 918... Line 948...
918 
			$sql = " AND location LIKE $commune ";
 948 
			$sql = " AND location LIKE $commune ";
919 
		}
 949 
		}
920 
		return $sql;
 950 
		return $sql;
921 
	}
 951 
	}
Line -... Line 952...
- 
 
 952 
 
- 
 
 953 
	private function construireWhereCommuneSansCoordonneesAvecSensibles() {
- 
 
 954 
		$sql = '';
- 
 
 955 
		// Récupération des coordonnées depuis l'id station
- 
 
 956 
		extract($this->parametres);
- 
 
 957 
		if (!$this->etreNull($commune)) {
- 
 
 958 
			$commune = $this->proteger($commune);
- 
 
 959 
			$sql = " AND location LIKE $commune ";
- 
 
 960 
			$sql .= " AND (
- 
 
 961 
			           		(
- 
 
 962 
			           			(coord_x = '000null' OR coord_x = '') ".
- 
 
 963 
								" AND (coord_y = '000null' OR coord_y = '')".
- 
 
 964 
							')'.
- 
 
 965 
							' OR mots_cles_texte LIKE "%sensible%"'.
- 
 
 966 
						') ';
- 
 
 967
- 
 
 968 
		}
- 
 
 969 
		return $sql;
Line 922... Line 970...
922 
 
 970 
	}
923 
 
 971 
 
924 
	private function construireWhereUtilisateur() {
 972 
	private function construireWhereUtilisateur() {
925 
		$sql = '';
 973 
		$sql = '';