WebSVN – eFlore/Applications.cel – Diff – /trunk/jrest/services/CelWidgetMap.php

 	 * Les stations de la carte par défaut
 	 */
 	public function getStations($params) {
 		$json = null;
 		$requete =  'SELECT utm_secteur, utm_x, utm_y, wgs84_latitude AS latitude, wgs84_longitude AS longitude '.
-                    'FROM cel_obs AS co '.
+			'FROM cel_obs AS co '.
-                    '   LEFT JOIN cel_zones_geo AS l '.
+			'	LEFT JOIN cel_zones_geo AS l '.
-                    '       ON (l.id_zone_geo = co.ce_zone_geo) '.
+			'		ON (l.id_zone_geo = co.ce_zone_geo) '.
-                    "WHERE transmission = '1' ".
+			"WHERE transmission = '1' ".
 					$this->construireWhereDept().
-                    $this->construireWhereCommune().
+					$this->construireWhereCommune().
 					$this->construireWherePhotosSeulement().
-                    $this->construireWhereUtilisateur().
+					$this->construireWhereUtilisateur().
 					$this->construireWhereNumTaxon().
 					$this->construireWhereNomTaxon().
 					$this->construireWhereDate().
 					$this->construireWhereCommentaire().
 					$this->construireWhereProjet().
 	public function getObservations($params) {
 		$resultats = array();
 		$total = 0;
 		if (!$this->etreNull($this->parametres['station'])) {
 			$requete = 	'SELECT SQL_CALC_FOUND_ROWS id_observation, ce_utilisateur, courriel_utilisateur, '.
-			            '   nom_sel, nom_ret, nom_sel_nn, nom_ret_nn, nt, famille, '.
+				'	nom_sel, nom_ret, nom_sel_nn, nom_ret_nn, nt, famille, '.
-						'	lieudit, zone_geo, date_observation, milieu, commentaire, '.
+				'	lieudit, zone_geo, date_observation, milieu, commentaire, '.
-						'	utm_secteur, utm_x, utm_y, id_zone_geo, date_transmission, nom_referentiel '.
+				'	utm_secteur, utm_x, utm_y, id_zone_geo, date_transmission, nom_referentiel '.
-						'FROM cel_obs AS co '.
+				'FROM cel_obs AS co '.
-						'	LEFT JOIN cel_zones_geo AS l '.
+				'	LEFT JOIN cel_zones_geo AS l '.
-						"		ON (l.id_zone_geo = co.ce_zone_geo) ".
+				"		ON (l.id_zone_geo = co.ce_zone_geo) ".
-						"WHERE transmission = '1' ".
+				"WHERE transmission = '1' ".
-						$this->construireWhereCoordonnees().
+				$this->construireWhereCoordonnees().
-						$this->construireWherePhotosSeulement().
+				$this->construireWherePhotosSeulement().
-						$this->construireWhereUtilisateur().
+				$this->construireWhereUtilisateur().
-						$this->construireWhereNumTaxon().
+				$this->construireWhereNumTaxon().
-						$this->construireWhereNomTaxon().
+				$this->construireWhereNomTaxon().
-						$this->construireWhereDate().
+				$this->construireWhereDate().
-						$this->construireWhereCommentaire().
+				$this->construireWhereCommentaire().
-						$this->construireWhereProjet().
+				$this->construireWhereProjet().
-						$this->construireWhereTag().
+				$this->construireWhereTag().
-	                    'ORDER BY nom_sel ASC '.
+				'ORDER BY nom_sel ASC '.
-	                    "LIMIT {$this->start},{$this->limit} ";
+				"LIMIT {$this->start},{$this->limit} ";
 			//die($requete);
 			$resultats = Cel::db()->requeter($requete, self::SQL_RETOUR_COMPLET, self::SQL_MODE_OBJET);
 			$requete = 'SELECT FOUND_ROWS()';
 		if (!$this->etreNull($commentaire)) {
 			$commentaire = Cel::db()->proteger('%'.$commentaire.'%');
 			switch ($type) {
 				case '*' :
 					$sql = $this->obtenirConditionPourCommentaires($commentaire);
-					$sql = " AND (commentaire LIKE $commentaire OR ($sql)) ";
+					$sql = " AND (ci.commentaire LIKE $commentaire OR ($sql)) ";
 					break;
 				case 'observation' :
 					$sql = " AND co.commentaire LIKE $commentaire ";
 					break;
 				case 'photo' :
 	 * Récupération des identifiant d'utilisateur et des ordres des observations correspondant à une date.
 	 * Retour sous forme de tableau : array[identifiant] = array(ordre, ordre...);
 	 */
 	private function obtenirObsLieesImg($type, $param) {
 		// Construction de la requête
-		$requete = 	'SELECT DISTINCT id_observation AS id_obs, ce_utilisateur AS utilisateur '.
+		$requete = 	'SELECT DISTINCT id_observation AS id_obs, co.ce_utilisateur AS utilisateur '.
-			'FROM  cel_images ci'.
+			'FROM cel_images ci'.
-			'	LEFT JOIN cel_obs_images coi '.
-			'		ON (coi.id_image = ci.id_image) '.
-			'	LEFT JOIN cel_obs AS co '.
+			'	INNER JOIN cel_obs AS co '.
-			'		ON coi.id_observation = co.id_observation '.
+			'		ON ci.ce_observation = co.id_observation '.
 			'	LEFT JOIN locations AS l '.
 			'		ON (l.id_zone_geo = co.ce_zone_geo) '.
-			"WHERE  transmission = '1' ".
+			"WHERE co.transmission = '1' ".
 			($type == 'date.photo' ? " AND (date_prise_de_vue LIKE ".str_replace('-', ':', $param).") " : '').
-			($type == 'date.ajout' ? " AND date_creation LIKE $param " : '').
+			($type == 'date.ajout' ? " AND ci.date_creation LIKE $param " : '').
-			($type == 'date.liaison' ? " AND date_liaison LIKE $param " : '').
+			($type == 'date.liaison' ? " AND ci.date_liaison LIKE $param " : '').
 			// TODO: recherche sur le xml
 			//($type == 'commentaire.meta' ? " AND ci.commentaire LIKE $param " : '').
 			($type == 'commentaire.utilisateur' ? "	AND ci.commentaire LIKE $param " : '').
 			($type == 'commentaire.*' ? " AND (ci.commentaire LIKE $param) " : '').
 			$this->construireWhereCoordonnees().
+	}
 	private function construireWherePhotosSeulement() {
 		$sql = '';
 		if (isset($this->parametres['photos']) && $this->parametres['photos'] == 1) {
-			$sql = 'AND co.id_observation IN (SELECT DISTINCT id_observation FROM cel_obs_images) ';
+			$sql = 'AND co.id_observation IN (SELECT DISTINCT ce_observation FROM cel_images) ';
+		}
 		return $sql;
 	private function getSqlWhereObsAvecImagesTaguees($tag) {
 		$sql = null;
 		if (isset($tag) && !$this->etreNull($tag)) {
 			$tag_sql = $this->getSqlWhereMotsCles($tag);
 			// Construction de la requête
-			$requete = 	'SELECT DISTINCT coi.id_observation AS id_obs,  ci.ce_utilisateur AS utilisateur '.
+			$requete = 	'SELECT DISTINCT co.id_observation AS id_obs, ci.ce_utilisateur AS utilisateur '.
 						'FROM cel_images ci'.
-						'	LEFT JOIN cel_obs_images coi'.
-						'		ON (ci.id_image = coi.id_image) '.
-						'	LEFT JOIN cel_obs AS co '.
+						'	INNER JOIN cel_obs AS co '.
-						'		ON (coi.id_observation = co.id_observation) '.
+						'		ON (ci.ce_observation = co.id_observation) '.
 						'	LEFT JOIN cel_zones_geo AS l '.
 						"		ON (l.id_zone_geo = co.ce_zone_geo) ".
-						"WHERE transmission = '1' ".
+						"WHERE co.transmission = '1' ".
 						$this->construireWhereCoordonnees().
 						$this->construireWhereUtilisateur().
 						$this->construireWhereNumTaxon().
 						$this->construireWhereNomTaxon().
 						$this->construireWhereProjet().
 		$where_mots_cles_images = implode(' '.$mots_cles_encodes['type'].' ', $where_mots_cles_images);
 		return $where_mots_cles_images;
+	}
-	private function decomposerParametreTag($tags) {
 	private function decomposerParametreTag($tags) {
 		$mots_cles = array('type' => null, 'motsCles' => null, 'motsClesEncodesProteges' => null);
 		if (preg_match('/.+OU.+/', $tags)) {
 			$mots_cles['type'] = 'OR';
 			$mots_cles['motsCles'] = explode('OU', $tags);

Subversion Repositories eFlore/Applications.cel

(root)/trunk/jrest/services/CelWidgetMap.php – Rev 2143 → 2446