Subversion Repositories eFlore/Applications.cel

Rev

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

Rev 1043 Rev 1092
Line 20... Line 20...
20 
 * @license CECILL v2 <http://www.cecill.info/licences/Licence_CeCILL_V2-en.txt>
 20 
 * @license CECILL v2 <http://www.cecill.info/licences/Licence_CeCILL_V2-en.txt>
21 
 * @version $Id$
 21 
 * @version $Id$
22 
 * @copyright © 2010, Jean-Pascal MILCENT
 22 
 * @copyright © 2010, Jean-Pascal MILCENT
23 
 */
 23 
 */
24 
// TODO : supprimer le TRIM quand les obs seront reliées correctements aux localisations (sur le code INSEE par exemple)
 24 
// TODO : supprimer le TRIM quand les obs seront reliées correctements aux localisations (sur le code INSEE par exemple)
25 
class CelWidgetMap extends Cel {
 25 
class CelWidgetMapPoint extends Cel {
26 
	const MARQUEUR_GROUPE = 'GROUPE';
 26 
	const MARQUEUR_GROUPE = 'GROUPE';
27 
	const MARQUEUR_COMMUNE = 'COMMUNE';
 27 
	const MARQUEUR_COMMUNE = 'COMMUNE';
28 
	const MARQUEUR_STATION = 'STATION';
 28 
	const MARQUEUR_STATION = 'STATION';
29 
	/**
 29 
	/**
30 
	 * Méthode appelée avec une requête de type GET.
 30 
	 * Méthode appelée avec une requête de type GET.
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
- 
 
 149 
		$coordonnees_non_nulles_pas_sensible = "(".$coordonnees_non_nulles." AND mots_cles_texte NOT LIKE '%sensible%')";
- 
 
 150
145 
							"IF ((coord_x != '000null' AND coord_x != '' ".
 151 
		$requete =  'SELECT DISTINCT *, location AS nom, '.
146 
								" AND coord_y != '000null' AND coord_y != ''), ".
 152 
							"IF (".$coordonnees_non_nulles_pas_sensible.", ".
147 
								"'stations', ".
 153 
								"'stations', ".
148 
							"'communes' ".
 154 
							"'communes' ".
149 
							") as type_emplacement, ".
 - 
 
150 
							"IF ((coord_x != '000null' AND coord_x != '' ".
 155 
							") as type_emplacement, ".
151 
								" AND coord_y != '000null' AND coord_y != ''), ".
 156 
							"IF (".$coordonnees_non_nulles_pas_sensible.", ".
152 
								"coord_x, ".
 157 
								"coord_x, ".
153 
								"wgs84_latitude ".
 158 
								"wgs84_latitude ".
154 
							") as lat, ".
 - 
 
155 
							"IF ((coord_x != '000null' AND coord_x != '' ".
 159 
							") as lat, ".
156 
								" AND coord_y != '000null' AND coord_y != ''), ".
 160 
							"IF (".$coordonnees_non_nulles_pas_sensible.", ".
157 
								"coord_y, ".
 161 
								"coord_y, ".
158 
								"wgs84_longitude ".
 162 
								"wgs84_longitude ".
159 
							") as lng, ".
 - 
 
160 
							"IF ((coord_x != '000null' AND coord_x != '' ".
 163 
							") as lng, ".
161 
								" AND coord_y != '000null' AND coord_y != ''), ".
 164 
							"IF (".$coordonnees_non_nulles_pas_sensible.", ".
162 
								'CONCAT("'.self::MARQUEUR_STATION.':",coord_x,"|",coord_y), '.
 165 
								'CONCAT("'.self::MARQUEUR_STATION.':",coord_x,"|",coord_y), '.
163 
								'CONCAT("'.self::MARQUEUR_COMMUNE.':",wgs84_latitude,"|",wgs84_longitude) '.
 166 
								'CONCAT("'.self::MARQUEUR_COMMUNE.':",wgs84_latitude,"|",wgs84_longitude) '.
164 
							") as id ".
 167 
							") as id ".
Line 170... Line 173...
170 
						"(".($this->construireWhereRectangleStationOR() != null ? $this->construireWhereRectangleStationOR().' AND ' : '').
 173 
						"(".($this->construireWhereRectangleStationOR() != null ? $this->construireWhereRectangleStationOR().' AND ' : '').
171 
							" (mots_cles_texte IS NULL OR mots_cles_texte NOT LIKE '%sensible%') ".
 174 
							" (mots_cles_texte IS NULL OR mots_cles_texte NOT LIKE '%sensible%') ".
172 
						")".
 175 
						")".
173 
						" OR ".
 176 
						" OR ".
174 
						" ( ".
 177 
						" ( ".
175 
							"(".($this->construireWhereRectangleCommuneOR() != null ? $this->construireWhereRectangleCommuneOR().' Or ' : '').
 178 
							"(".($this->construireWhereRectangleCommuneOR() != null ? $this->construireWhereRectangleCommuneOR().' AND ' : '').
176 
							" mots_cles_texte LIKE '%sensible%' ".
 179 
								"(".$coordonnees_nulles.")".
177 
							")".
 180 
							")".
178 
						" ) ".
 181 
						" ) ".
179 
					") ".
 182 
					") ".
180 
					$this->construireWhereDept().
 183 
					$this->construireWhereDept().
181 
                    $this->construireWhereCommune().
 184 
                    $this->construireWhereCommune().
Line 184... Line 187...
184 
					$this->construireWhereNomTaxon().
 187 
					$this->construireWhereNomTaxon().
185 
					$this->construireWhereDate().
 188 
					$this->construireWhereDate().
186 
					$this->construireWhereCommentaire().
 189 
					$this->construireWhereCommentaire().
187 
					$this->construireWhereProjet().
 190 
					$this->construireWhereProjet().
188 
					$this->construireWhereTag();
 191 
					$this->construireWhereTag();
189 
 
 192
- 
 
 193 
		$i = 0;
- 
 
 194 
		$limite = 50000;
- 
 
 195 
		$resultats = array();
- 
 
 196 
		do {
190 
		$resultats_emplacements = $this->executerRequete($requete);
 197 
			$resultats_emplacements = $this->executerRequete($requete.' LIMIT '.$i.','.$limite);
- 
 
 198 
			$resultats = array_merge($resultats,$resultats_emplacements);
- 
 
 199 
			$i += $limite;
- 
 
 200 
		} while(count($resultats_emplacements) > 0);
- 
 
 201 
		//echo $requete;
- 
 
 202 
		echo '<pre>'.print_r($resultats,true).'</pre>';
191 
		$emplacements = $this->traiterEmplacements($resultats_emplacements, $this->compterObservations($params));
 203 
		//$emplacements = $this->traiterEmplacements($resultats_emplacements, $this->compterObservations($params));
192 
 
 204 
 
193 
		return $emplacements;
 205 
		//return $emplacements;
- 
 
 206 
	}
- 
 
 207
- 
 
 208 
	private function afficherRequeteFormatee($requete) {
- 
 
 209 
		$requete = str_replace(')',')<br />',$requete);
- 
 
 210 
		$requete = str_replace('(','	<br />	(',$requete);
- 
 
 211 
		echo '<pre>'.$requete.'</pre>';
- 
 
 212 
		exit;
194 
	}
 213 
	}
Line 195... Line 214...
195 
 
 214 
 
Line 196... Line 215...
196 
	private $nb_obs = 0;
 215 
	private $nb_obs = 0;
Line 382... Line 401...
382 
						'	sector, x_utm, y_utm, insee_code, date_transmission '.
 401 
						'	sector, x_utm, y_utm, insee_code, date_transmission '.
383 
						'FROM cel_inventory AS i '.
 402 
						'FROM cel_inventory AS i '.
384 
						'	LEFT JOIN locations AS l '.
 403 
						'	LEFT JOIN locations AS l '.
385 
						"		ON (l.name = i.location AND l.code = i.id_location) ".
 404 
						"		ON (l.name = i.location AND l.code = i.id_location) ".
386 
						"WHERE transmission = '1' ".
 405 
						"WHERE transmission = '1' ".
387 
						((!$this->etreNull($this->parametres['commune'])) ? $this->construireWhereCommuneSansCoordonnees() : $this->construireWhereCoordonnees()).
 406 
						((!$this->etreNull($this->parametres['commune'])) ? $this->construireWhereCommuneSansCoordonneesAvecSensibles() : $this->construireWhereCoordonneesSansSensibles()).
388 
						$this->construireWhereUtilisateur().
 407 
						$this->construireWhereUtilisateur().
389 
						$this->construireWhereNumTaxon().
 408 
						$this->construireWhereNumTaxon().
390 
						$this->construireWhereNomTaxon().
 409 
						$this->construireWhereNomTaxon().
391 
						$this->construireWhereDate().
 410 
						$this->construireWhereDate().
392 
						$this->construireWhereCommentaire().
 411 
						$this->construireWhereCommentaire().
Line 586... Line 605...
586 
				$sql = " AND (coord_x = $lat AND coord_y = $lng) ";
 605 
				$sql = " AND (coord_x = $lat AND coord_y = $lng) ";
587 
			}
 606 
			}
588 
		}
 607 
		}
589 
		return $sql;
 608 
		return $sql;
590 
	}
 609 
	}
- 
 
 610
- 
 
 611 
	private function construireWhereCoordonneesSansSensibles() {
- 
 
 612 
		$sql = '(';
- 
 
 613 
		// Récupération des coordonnées depuis l'id station
- 
 
 614 
		extract($this->decomposerParametreStation());
- 
 
 615 
		if (isset($type)) {
- 
 
 616 
			if ($type == self::MARQUEUR_COMMUNE) {
- 
 
 617 
				$lat = $this->proteger($lat);
- 
 
 618 
				$lng = $this->proteger($lng);
- 
 
 619 
				$sql = " AND (ROUND(wgs84_latitude,5) = ROUND($lat,5) AND ROUND(wgs84_longitude,5) = ROUND($lng,5)) ";
- 
 
 620 
			}  else if ($type == self::MARQUEUR_STATION) {
- 
 
 621 
				$lat = $this->proteger($lat);
- 
 
 622 
				$lng = $this->proteger($lng);
- 
 
 623 
				$sql = " AND (coord_x = $lat AND coord_y = $lng) ";
- 
 
 624 
			}
- 
 
 625 
		}
- 
 
 626 
		$sql .= ' AND mots_cles_texte NOT LIKE %sensible%';
- 
 
 627 
		return $sql;
- 
 
 628 
	}
Line 591... Line 629...
591 
 
 629 
 
592 
	private function construireWhereCommentaire() {
 630 
	private function construireWhereCommentaire() {
593 
		$sql = '';
 631 
		$sql = '';
594 
		list($type, $commentaire) = $this->decomposerParametreCommentaire();
 632 
		list($type, $commentaire) = $this->decomposerParametreCommentaire();
Line 920... Line 958...
920 
			$sql = " AND location LIKE $commune ";
 958 
			$sql = " AND location LIKE $commune ";
921 
		}
 959 
		}
922 
		return $sql;
 960 
		return $sql;
923 
	}
 961 
	}
Line 924... Line 962...
924 
 
 962 
 
925 
	private function construireWhereCommuneSansCoordonnees() {
 963 
	private function construireWhereCommuneSansCoordonneesAvecSensibles() {
926 
		$sql = '';
 964 
		$sql = '';
927 
		// Récupération des coordonnées depuis l'id station
 965 
		// Récupération des coordonnées depuis l'id station
928 
		extract($this->parametres);
 966 
		extract($this->parametres);
929 
		if (!$this->etreNull($commune)) {
 967 
		if (!$this->etreNull($commune)) {
930 
			$commune = $this->proteger($commune);
 968 
			$commune = $this->proteger($commune);
- 
 
 969 
			$sql = " AND location LIKE $commune ";
- 
 
 970 
			$sql .= " AND (
931 
			$sql = " AND location LIKE $commune ";
 971 
			           		(
932 
			$sql .= " AND ((coord_x = '000null' OR coord_x = '') ".
 972 
			           			(coord_x = '000null' OR coord_x = '') ".
- 
 
 973 
								" AND (coord_y = '000null' OR coord_y = '')".
- 
 
 974 
							')'.
- 
 
 975 
							' OR mots_cles_texte LIKE "%sensible%"'.
- 
 
 976 
						') ';
933 
								" AND (coord_y = '000null' OR coord_y = '')) ";
 977
934 
		}
 978 
		}
935 
		return $sql;
 979 
		return $sql;
Line 936... Line 980...
936 
	}
 980 
	}