Subversion Repositories eFlore/Applications.cel

Rev

Go to most recent revision | Details | 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
?>