Subversion Repositories eFlore/Applications.cel

Rev

Rev 702 | Rev 1359 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
694 jpm 1 
<?php
2 
/**
696 jpm 3 
 * Service fournissant des statistiques de l'application CEL au format texte (JSON).
694 jpm 4 
 * Encodage en entrée : utf8
5 
 * Encodage en sortie : utf8
6 
 *
7 
 * Cas d'utilisation :
696 jpm 8 
 * /CelStatistiqueTxt/TypeDeStat : retourne les statistiques demandées
694 jpm 9 
 * Paramêtres :
696 jpm 10 
 * utilisateur=courriel : retourne les statistiques d'un utilisateur donné.
694 jpm 11 
 *
696 jpm 12 
 * @author		Jean-Pascal MILCENT <jpm@tela-botanica.org>
13 
 * @license		GPL v3 <http://www.gnu.org/licenses/gpl.txt>
14 
 * @license		CECILL v2 <http://www.cecill.info/licences/Licence_CeCILL_V2-en.txt>
15 
 * @version		$Id$
16 
 * @copyright	Copyright (c) 2011, Tela Botanica (accueil@tela-botanica.org)
694 jpm 17 
 */
18 
class CelStatistiqueTxt extends Cel {
696 jpm 19 
 
694 jpm 20 
	/**
21 
	 * Méthode appelée avec une requête de type GET.
22 
	 */
696 jpm 23 
	public function getElement($ressources) {
694 jpm 24 
		$graph = null;
25 
		$serveur = '';
26 
 
696 jpm 27 
		if (isset($ressources[0])) {
28 
			$this->analyserParametresUrl();
29 
			$stat_demande = array_shift($ressources);
694 jpm 30 
			$methode = 'get'.$stat_demande;
31 
			if (method_exists($this, $methode)) {
702 jpm 32 
				$this->ressources = $ressources;
696 jpm 33 
				$stats = $this->$methode($ressources);
694 jpm 34 
			} else {
35 
				$this->messages[] = "Ce type de statistiques '$stat_demande' n'est pas disponible.";
36 
			}
37 
		} else {
696 jpm 38 
			$this->messages[] = "La ressource du service CEL StatistiqueTxt doit indiquer le type de statistique. Ex. : .../CelStatistiqueTxt/Nombres";
694 jpm 39 
		}
40 
 
41 
		if (!is_null($stats)) {
42 
			$this->envoyerJson($stats);
43 
		}
44 
	}
45 
 
696 jpm 46 
	private function analyserParametresUrl() {
702 jpm 47 
		$this->parametres['utilisateur'] = isset($_GET['utilisateur']) ? $this->bdd->quote($this->verifierSecuriteParametreUrl($_GET['utilisateur'])) : null;
48 
		$this->parametres['num_taxon'] = isset($_GET['num_taxon']) ? $this->bdd->quote($this->verifierSecuriteParametreUrl($_GET['num_taxon'])) : null;
49 
		$this->parametres['taxon'] = isset($_GET['taxon']) ? $this->bdd->quote($this->verifierSecuriteParametreUrl($_GET['taxon'].'%')) : null;
707 jpm 50 
		$this->parametres['tag'] = isset($_GET['tag']) ? $this->verifierSecuriteParametreUrl($_GET['tag']) : null;
702 jpm 51 
		$this->parametres['start'] = isset($_GET['start']) ? $this->verifierSecuriteParametreUrl($_GET['start']) : null;
52 
		$this->parametres['limit'] = isset($_GET['limit']) ? $this->verifierSecuriteParametreUrl($_GET['limit']) : null;
696 jpm 53 
	}
54 
 
702 jpm 55 
	private function getListeUtilisateursNbrePhotos() {
707 jpm 56 
		$liste = array();
57 
 
702 jpm 58 
		$requete = $this->construireRequeteListeUtilisateurNbrePhoto();
707 jpm 59 
		if ($requete != null) {
60 
			$resultats = $this->executerRequete($requete);
61 
 
62 
			if ($resultats != false) {
63 
				foreach ($resultats as $resultat) {
64 
					$liste[$resultat['identifiant']] = $resultat['nbre'];
65 
				}
702 jpm 66 
			}
67 
		}
68 
		return $liste;
69 
	}
70 
 
71 
	private function construireRequeteListeUtilisateurNbrePhoto() {
72 
		$select = 	'SELECT identifiant, COUNT(DISTINCT ci_id_image) AS nbre ';
73 
		$from = 	'FROM cel_inventory '.
74 
					'	LEFT JOIN cel_obs_images ON (coi_ce_observation = ordre AND coi_ce_utilisateur = identifiant) '.
75 
					'	LEFT JOIN cel_images ON (coi_ce_image = ci_id_image) ';
76 
		$where = 	'WHERE transmission = 1 ';
77 
		$groupBy = 	'GROUP BY identifiant ';
78 
		$orderBy = 	'ORDER BY nbre DESC ';
79 
		$limitSql =	'LIMIT 0,150 ';
80 
 
707 jpm 81 
		$zero_images = false;
702 jpm 82 
		if (count($this->parametres) != 0) {
83 
			extract($this->parametres);
84 
 
85 
			$filtres = array();
86 
 
87 
			if (isset($utilisateur)) {
88 
				$filtres[] = "identifiant = $utilisateur ";
89 
			}
90 
			if (isset($num_taxon)) {
91 
				$filtres[] = "num_taxon = $num_taxon ";
92 
			}
93 
			if (isset($taxon)) {
94 
				$filtres[] = "nom_ret LIKE $taxon ";
95 
			}
707 jpm 96 
			if (isset($tag)) {
97 
				$limitation = $this->construireWhereTags();
98 
				if ($limitation != null) {
99 
					$filtres[] = $limitation;
100 
				} else {
101 
					$zero_images = true;
102 
				}
103 
			}
702 jpm 104 
			$where .= ((count($filtres) > 0) ? 'AND '.implode(' AND ', $filtres) : '');
105 
 
106 
			if (isset($start)) {
107 
				$limitSql = str_replace('0,', "$start,", $limitSql);
108 
			}
109 
			if (isset($limit)) {
110 
				$limitSql = str_replace('150', $limit, $limitSql);
111 
			}
112 
		}
707 jpm 113 
		if ($zero_images) {
114 
			$requete = null;
115 
		} else {
116 
			$requete = $select.$from.$where.$groupBy.$orderBy.$limitSql;
117 
			//echo $requete;
118 
		}
702 jpm 119 
		return $requete;
120 
	}
121 
 
707 jpm 122 
	private function construireWhereTags() {
123 
		$where = null;
124 
		if (isset($this->parametres['tag'])) {
125 
			$tag = $this->parametres['tag'];
126 
			$tag_encode = $this->bdd->quote($this->encoderMotCle(trim($tag)));
127 
 
128 
			// Construction de la requête
129 
			$requete = 	'SELECT cmc_id_mot_cle_utilisateur, cmc_id_proprietaire '.
130 
				'FROM cel_mots_cles_images '.
131 
				"WHERE cmc_id_mot_cle_general = $tag_encode ";
132 
			$elements = $this->executerRequete($requete);
133 
 
134 
			if ($elements != false && count($elements) > 0) {
135 
				// Pré-construction du where de la requête
136 
				$tpl_where = '(ci_meta_mots_cles LIKE "%%%s%%" AND ci_ce_utilisateur = %s )';
137 
				$requete_where = array();
138 
				foreach ($elements as $occurence) {
139 
					$requete_where[] = sprintf($tpl_where, $occurence['cmc_id_mot_cle_utilisateur'], $this->bdd->quote($occurence['cmc_id_proprietaire']));
140 
				}
141 
				$where = ' ('.implode(" \nOR ", $requete_where).') ';
142 
			} else {
143 
				$this->debug[] = "Aucune image ne correspond à ce mot clé.";
144 
			}
145 
		}
146 
		return $where;
147 
	}
148 
 
702 jpm 149 
	private function getListeTaxonsNbrePhotos() {
150 
		$requete = $this->construireRequeteListeTaxonNbrePhoto();
151 
		$resultats = $this->executerRequete($requete);
152 
		$liste = array();
153 
		if ($resultats != false) {
154 
			foreach ($resultats as $resultat) {
155 
				$liste[$resultat['nom_ret']] = $resultat['nbre'];
156 
			}
157 
		}
158 
		return $liste;
159 
	}
160 
 
161 
	private function construireRequeteListeTaxonNbrePhoto() {
162 
		$select = 	'SELECT nom_ret, COUNT(DISTINCT ci_id_image) AS nbre ';
163 
		$from = 	'FROM cel_inventory '.
164 
					'	LEFT JOIN cel_obs_images ON (coi_ce_observation = ordre AND coi_ce_utilisateur = identifiant) '.
165 
					'	LEFT JOIN cel_images ON (coi_ce_image = ci_id_image) ';
166 
		$where = 	'WHERE transmission = 1 '.
167 
					"	AND nom_ret != '' ";
168 
		$groupBy = 	'GROUP BY nom_ret ';
169 
		$orderBy = 	'ORDER BY nbre DESC ';
170 
		$limitSql =	'LIMIT 0,150 ';
171 
 
172 
 
173 
		if (count($this->parametres) != 0) {
174 
			extract($this->parametres);
175 
 
176 
			$filtres = array();
177 
 
178 
			if (isset($utilisateur)) {
179 
				$filtres[] = "identifiant = $utilisateur ";
180 
			}
181 
			if (isset($num_taxon)) {
182 
				$filtres[] = "num_taxon = $num_taxon ";
183 
			}
184 
			if (isset($taxon)) {
185 
				$filtres[] = "nom_ret LIKE $taxon ";
186 
			}
187 
			$where .= ((count($filtres) > 0) ? 'AND '.implode(' AND ', $filtres) : '');
188 
 
189 
			if (isset($start)) {
190 
				$limitSql = str_replace('0,', "$start,", $limitSql);
191 
			}
192 
			if (isset($limit)) {
193 
				$limitSql = str_replace('150', $limit, $limitSql);
194 
			}
195 
		}
196 
 
197 
		$requete = $select.$from.$where.$groupBy.$orderBy.$limitSql;
198 
		return $requete;
199 
	}
200 
 
201 
	private function getNombres() {
696 jpm 202 
 
203 
		$requete = $this->construireRequeteNbreObs();
204 
		$info['observations'] = (int) $this->executerRequete($requete, 'Column');
694 jpm 205 
 
696 jpm 206 
		$requete = $this->construireRequeteNbreObsPubliques();
207 
		$info['observationsPubliques'] = (int) $this->executerRequete($requete, 'Column');
208 
 
209 
		$requete = $this->construireRequeteNbreImg();
210 
		$info['images'] =(int) $this->executerRequete($requete, 'Column');
211 
 
212 
		$requete = $this->construireRequeteNbreImgLiees();
213 
		$info['imagesLiees'] =(int) $this->executerRequete($requete, 'Column');
214 
 
215 
		$requete = $this->construireRequeteNbreObsLiees();
216 
		$info['observationsLiees'] = (int) $this->executerRequete($requete, 'Column');
217 
 
697 jpm 218 
		$info['moyImagesParObs'] = ($info['observationsLiees'] > 0 ? round($info['imagesLiees']/$info['observationsLiees'], 2) : '');
696 jpm 219 
 
220 
		$requete = $this->construireRequeteNbreObsParCommune();
697 jpm 221 
		$info['communes'] = ($resultats = $this->executerRequete($requete)) ? count($resultats) : '' ;
222 
		$info['observationsParCommunesMin'] = 1000;
223 
		$info['observationsParCommunesMax'] = 0;
224 
		$info['observationsParCommunesTotal'] = 0;
225 
		foreach ($resultats as $resultat) {
226 
			if ($resultat['nbre'] < $info['observationsParCommunesMin']) {
227 
				$info['observationsParCommunesMin'] = $resultat['nbre'];
228 
			}
229 
			if ($resultat['nbre'] > $info['observationsParCommunesMax']) {
230 
				$info['observationsParCommunesMax'] = $resultat['nbre'];
231 
			}
232 
			$info['observationsParCommunesTotal'] += $resultat['nbre'];
233 
		}
702 jpm 234 
		$info['observationsParCommunesMoyenne'] = ($info['communes'] > 0 ) ? round($info['observationsParCommunesTotal'] / $info['communes'], 2) : 0;
696 jpm 235 
 
236 
		return $info;
237 
	}
238 
 
239 
	private function construireRequeteNbreObs() {
694 jpm 240 
		$requete = 	'SELECT COUNT(id) AS nbre '.
696 jpm 241 
					'FROM cel_inventory ';
242 
 
243 
		if (count($this->parametres) != 0) {
244 
			$filtres = array();
245 
			extract($this->parametres);
246 
 
247 
			if (isset($utilisateur)) {
248 
				$filtres[] = "identifiant = $utilisateur ";
249 
			}
250 
			if (isset($num_taxon)) {
251 
				$filtres[] = "num_taxon = $num_taxon ";
252 
			}
702 jpm 253 
			if (isset($taxon)) {
254 
				$filtres[] = "nom_ret LIKE $taxon ";
255 
			}
696 jpm 256 
 
257 
			$requete .= ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');
258 
		}
259 
 
260 
		return $requete;
261 
	}
262 
 
263 
	private function construireRequeteNbreObsPubliques() {
264 
		$requete = 	'SELECT COUNT(id) AS nbre '.
694 jpm 265 
					'FROM cel_inventory '.
696 jpm 266 
					"WHERE transmission = 1 ";
267 
 
268 
		if (count($this->parametres) != 0) {
269 
			$filtres = array();
270 
			extract($this->parametres);
271 
 
272 
			if (isset($utilisateur)) {
273 
				$filtres[] = "identifiant = $utilisateur ";
274 
			}
275 
			if (isset($num_taxon)) {
276 
				$filtres[] = "num_taxon = $num_taxon ";
277 
			}
702 jpm 278 
			if (isset($taxon)) {
279 
				$filtres[] = "nom_ret LIKE $taxon ";
280 
			}
696 jpm 281 
 
282 
			$requete .= ((count($filtres) > 0) ? 'AND '.implode(' AND ', $filtres) : '');
283 
		}
694 jpm 284 
 
696 jpm 285 
		return $requete;
286 
	}
287 
 
288 
	private function construireRequeteNbreObsParCommune() {
289 
		$requete = 	'SELECT COUNT(id) AS nbre '.
290 
					'FROM cel_inventory '.
291 
					"WHERE location != '000null' ".
292 
					"	AND id_location != '000null' ";
293 
		$groupBy = 'GROUP BY location, id_location';
294 
 
295 
		if (count($this->parametres) != 0) {
296 
			$filtres = array();
297 
			extract($this->parametres);
298 
 
299 
			if (isset($utilisateur)) {
300 
				$filtres[] = "identifiant = $utilisateur ";
301 
			}
302 
			if (isset($num_taxon)) {
303 
				$filtres[] = "num_taxon = $num_taxon ";
304 
			}
702 jpm 305 
			if (isset($taxon)) {
306 
				$filtres[] = "nom_ret LIKE $taxon ";
307 
			}
696 jpm 308 
 
309 
			$requete .= ((count($filtres) > 0) ? 'AND '.implode(' AND ', $filtres) : '');
310 
		}
311 
		$requete .= $groupBy;
694 jpm 312 
 
696 jpm 313 
		return $requete;
314 
	}
315 
 
316 
	private function construireRequeteNbreImg() {
317 
		$select = 	'SELECT COUNT(DISTINCT ci_id_image) AS nbre ';
318 
		$from = 'FROM cel_images ';
694 jpm 319 
 
696 jpm 320 
		if (count($this->parametres) != 0) {
321 
			$filtres = array();
322 
 
323 
			extract($this->parametres);
324 
 
325 
			if (isset($utilisateur)) {
326 
				$filtres[] = "ci_ce_utilisateur = $utilisateur ";
327 
			}
328 
			if (isset($num_taxon)) {
702 jpm 329 
 
696 jpm 330 
				$filtres[] = "num_taxon = $num_taxon ";
331 
			}
702 jpm 332 
			if (isset($taxon)) {
333 
				$filtres[] = "nom_ret LIKE $taxon ";
334 
			}
335 
			if (isset($num_taxon) || isset($taxon)) {
336 
				$from .= 'LEFT JOIN cel_obs_images ON (coi_ce_image = ci_id_image) '.
337 
						'LEFT JOIN cel_inventory ON (coi_ce_observation = ordre AND coi_ce_utilisateur = identifiant) ';
338 
			}
696 jpm 339 
 
340 
			$where = ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');
341 
		}
342 
 
343 
		$requete = $select.$from.$where;
344 
		return $requete;
694 jpm 345 
	}
696 jpm 346 
 
347 
	private function construireRequeteNbreImgLiees() {
348 
		$select = 'SELECT COUNT(DISTINCT ci_id_image) AS nbre ';
349 
		$from = 'FROM cel_obs_images '.
350 
				'	LEFT JOIN cel_images ON (coi_ce_image = ci_id_image) ';
351 
 
352 
		if (count($this->parametres) != 0) {
353 
			$filtres = array();
354 
 
355 
			extract($this->parametres);
356 
 
357 
			if (isset($utilisateur)) {
358 
				$filtres[] = "coi_ce_utilisateur = $utilisateur ";
359 
			}
360 
			if (isset($num_taxon)) {
361 
				$filtres[] = "num_taxon = $num_taxon ";
362 
			}
702 jpm 363 
			if (isset($taxon)) {
364 
				$filtres[] = "nom_ret LIKE $taxon ";
365 
			}
696 jpm 366 
 
702 jpm 367 
			if (isset($num_taxon) || isset($taxon)) {
368 
				$from .= 'LEFT JOIN cel_inventory ON (coi_ce_observation = ordre AND coi_ce_utilisateur = identifiant) ';
369 
			}
370 
 
696 jpm 371 
			$where = ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');
372 
		}
373 
 
374 
		$requete = $select.$from.$where;
375 
		return $requete;
376 
	}
377 
 
378 
	private function construireRequeteNbreObsLiees() {
379 
		$select = 'SELECT COUNT(DISTINCT coi_ce_observation) AS nbre ';
380 
		$from = 'FROM cel_obs_images '.
381 
				'	LEFT JOIN cel_inventory ON (coi_ce_observation = ordre AND coi_ce_utilisateur = identifiant) ';
382 
 
383 
		if (count($this->parametres) != 0) {
384 
			$filtres = array();
385 
 
386 
			extract($this->parametres);
387 
 
388 
			if (isset($utilisateur)) {
389 
				$filtres[] = "identifiant = $utilisateur ";
390 
			}
391 
			if (isset($num_taxon)) {
392 
				$filtres[] = "num_taxon = $num_taxon ";
393 
			}
702 jpm 394 
			if (isset($taxon)) {
395 
				$filtres[] = "nom_ret LIKE $taxon ";
396 
			}
696 jpm 397 
 
398 
			$where = ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');
399 
		}
400 
 
401 
		$requete = $select.$from.$where;
402 
 
403 
		return $requete;
404 
	}
694 jpm 405 
}
406 
?>