Subversion Repositories eFlore/Applications.del

Rev

Rev 2120 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
1840 jpm 1
<?php
2
// declare(encoding='UTF-8');
3
/**
4
 * Classe contenant des méthodes permettant de construire les requêtes SQL complexe concernant les images et obs.
1881 jpm 5
 * Rempli un tableau des clauses "join", "where", "group by" et "oder by" nécessaire à la *recherche* des ids des
6
 * observations/images correspondantes aux filtres passés dans l'url du web service de recherche
7
 * (ListeImages et ListeObservations).
1840 jpm 8
 *
1881 jpm 9
 * Attention, cela signifie que toutes les tables ne sont pas *forcément* jointées, par exemple si aucune
10
 * contrainte ne le nécessite.
11
 * La requête construite ici est utile pour récupérer la liste des ids d'observations/images qui match.
12
 * Pour la récupération effective de "toutes" les données nécessaire au retour du web service en json, c'est une autre
13
 * requête directement dans le web service qui s'en charge. Cette technique en deux étapes est la plus rapide !
14
 *
15
 * Note: toujours rajouter les préfixes de table (di, do ou du), en fonction de ce que défini
16
 * les JOIN qui sont utilisés :
17
 * - le préfix de del_image est "di"
18
 * - le préfix de del_observation est "do"
19
 * - le préfix de del_utilisateur est "du"
20
 *
1840 jpm 21
 * @category  DEL
22
 * @package   Services
23
 * @package   Bibliotheque
24
 * @version   0.1
25
 * @author    Mathias CHOUET <mathias@tela-botanica.org>
26
 * @author    Jean-Pascal MILCENT <jpm@tela-botanica.org>
27
 * @author    Aurelien PERONNET <aurelien@tela-botanica.org>
28
 * @license   GPL v3 <http://www.gnu.org/licenses/gpl.txt>
29
 * @license   CECILL v2 <http://www.cecill.info/licences/Licence_CeCILL_V2-en.txt>
30
 * @copyright 1999-2014 Tela Botanica <accueil@tela-botanica.org>
31
 */
32
class Sql {
33
 
1871 jpm 34
	const APPLI_IMG = 'IMG';
35
	const APPLI_OBS = 'OBS';
36
 
1840 jpm 37
	private $conteneur;
38
	private $bdd;
39
	private $parametres = array();
1871 jpm 40
	private $appli;
1840 jpm 41
	private $requete = array(
42
		'join' => array(),
43
		'where' => array(),
44
		'groupby' => array(),
45
		'orderby' => array());
46
 
2004 mathias 47
	private $champsPrenom = array('prenom_utilisateur');
48
	private $champsNom = array('nom_utilisateur');
1985 aurelien 49
	private $champsSousRequeteObs = array('masque.genre', 'masque.famille', 'masque.ns', 'masque.commune', 'masque.milieu', 'masque.pays');
1840 jpm 50
 
51
	public function __construct(Conteneur $conteneur) {
52
		$this->conteneur = $conteneur;
53
		$this->bdd = $this->conteneur->getBdd();
54
	}
55
 
56
	public function setParametres(Array $parametres) {
57
		$this->parametres = $parametres;
58
	}
59
 
1871 jpm 60
	public function setAppli($appliType) {
61
		if ($appliType == 'IMG' || $appliType == 'OBS') {
62
			$this->appli = $appliType;
63
		} else {
64
			throw new Exception("Les types d'appli disponible sont : IMG (pour PictoFlora) et OBS (pour IdentiPlante)");
65
		}
66
	}
67
 
68
	private function getPrefixe() {
69
		return $this->appli === 'IMG' ? 'di' : 'do';
70
	}
71
 
72
	private function etreAppliImg() {
2138 mathias 73
		return $this->appli === 'IMG';
1871 jpm 74
	}
75
 
76
	private function etreAppliObs() {
2138 mathias 77
		return $this->appli === 'OBS';
1871 jpm 78
	}
79
 
1840 jpm 80
	public function getRequeteSql() {
81
		return $this->requete;
82
	}
83
 
84
	private function addJoin($join) {
1871 jpm 85
		if (!isset($this->requete['join'][$join])) {
1922 jpm 86
			$this->requete['join'][$join] = $join;
1871 jpm 87
		}
1840 jpm 88
	}
89
 
90
	public function getJoin() {
91
		return ($this->requete['join'] ? implode(' ', array_unique($this->requete['join'])).' ' : '');
92
	}
93
 
94
	private function addJoinDis($join) {
95
		$this->requete['join']['dis'] = $join;
96
	}
97
 
98
	private function addWhere($idParam, $where) {
99
		if (isset($this->parametres['_parametres_condition_or_'])
100
				&& in_array($idParam, $this->parametres['_parametres_condition_or_'])) {
1922 jpm 101
			if ($this->etreAppliImg() && in_array($idParam, $this->champsSousRequeteObs)) {
102
				$this->requete['where']['OR_SOUS_REQUETE'][] = $where;
103
			} else {
104
				$this->requete['where']['OR'][] = $where;
105
			}
1840 jpm 106
		} else {
107
			$this->requete['where']['AND'][] = $where;
108
		}
109
	}
1922 jpm 110
 
1840 jpm 111
	public function getWhere() {
1922 jpm 112
		// Sous-requete spéciale pour éviter de rechercher dans la table obs jointe à img depuis Pictoflora...
113
		if (isset($this->requete['where']['OR_SOUS_REQUETE']) && count($this->requete['where']['OR_SOUS_REQUETE']) > 0) {
114
			$clauseWhereSousRequete = implode(' OR ', $this->requete['where']['OR_SOUS_REQUETE']);
115
			$sousRequete = 'di.ce_observation IN '.
1981 aurelien 116
				"(SELECT id_observation FROM del_observation AS do WHERE $clauseWhereSousRequete ) ";
1922 jpm 117
			$this->requete['where']['OR'][] = "( $sousRequete )";
2033 aurelien 118
			unset($this->requete['join'][$this->getSqlJointureObs()]);
1922 jpm 119
		}
120
 
1840 jpm 121
		if (isset($this->requete['where']['OR']) && count($this->requete['where']['OR']) > 0) {
122
			$this->requete['where']['AND'][] = '('.implode(' OR ', $this->requete['where']['OR']).')';
123
		}
124
 
125
		$where = ' TRUE ';
126
		if (isset($this->requete['where']['AND']) && count($this->requete['where']['AND']) > 0) {
127
			$where = implode(' AND ', $this->requete['where']['AND']).' ';
128
		}
129
		return $where;
130
	}
131
 
132
	private function addGroupBy($groupBy) {
133
		$this->requete['groupby'][] = $groupBy;
134
	}
135
 
136
	public function getGroupBy() {
137
		$groupby = '';
138
		if (isset($this->requete['groupby']) && count($this->requete['groupby']) > 0) {
139
			$groupby = 'GROUP BY '.implode(', ', array_unique($this->requete['groupby'])).' ';
140
		}
141
		return $groupby;
142
	}
143
 
144
	private function addOrderBy($orderby) {
145
		$this->requete['orderby'][] = $orderby;
146
	}
147
 
148
	public function getOrderBy() {
149
		$orderby = '';
150
		if (isset($this->requete['orderby']) && count($this->requete['orderby']) > 0) {
151
			$orderby = 'ORDER BY '.implode(', ', array_unique($this->requete['orderby'])).' ';
152
		}
153
		return $orderby;
154
	}
155
 
156
	public function getLimit() {
157
		return 'LIMIT '.$this->parametres['navigation.depart'].','.$this->parametres['navigation.limite'].' ';
158
	}
159
 
160
	/**
161
	 * @param $p les paramètres (notamment de masque) passés par l'URL et déjà traités/filtrés (sauf quotes)
162
	 * @param $req le tableau, passé par référence représentant les composants de la requête à bâtir
163
	 */
164
	public function ajouterContraintes() {
165
		$this->ajouterContrainteAuteur();
166
		$this->ajouterContrainteDate();
1985 aurelien 167
		$this->ajouterContraintePays();
1840 jpm 168
		$this->ajouterContrainteDepartement();
169
		$this->ajouterContrainteIdZoneGeo();
170
		$this->ajouterContrainteGenre();
171
		$this->ajouterContrainteFamille();
172
		$this->ajouterContrainteNs();
173
		$this->ajouterContrainteNn();
174
		$this->ajouterContrainteReferentiel();
175
		$this->ajouterContrainteCommune();
2138 mathias 176
		$this->ajouterContraintePnInscrits();
1840 jpm 177
	}
178
 
179
	private function ajouterContrainteAuteur() {
180
		if (isset($this->parametres['masque.auteur'])) {
181
			$auteur = $this->parametres['masque.auteur'];
182
			// id du poster de l'obs
1871 jpm 183
			$prefixe = $this->getPrefixe();
1840 jpm 184
 
185
			if (is_numeric($auteur)) {
186
				$this->ajouterContrainteAuteurId();
1871 jpm 187
			} elseif(preg_match('/@[a-z0-9-]+(?:\.[a-z0-9-]+)*\.[a-z]{2,}$/i', $auteur)) {
1840 jpm 188
				$this->ajouterContrainteAuteurEmail();
189
			} else {
190
				$this->ajouterContrainteAuteurIntitule();
191
			}
192
		}
193
	}
194
 
195
	private function ajouterContrainteAuteurId() {
196
		$id = $this->parametres['masque.auteur'];
1871 jpm 197
		$prefixe = $this->getPrefixe();
2048 mathias 198
		$sqlTpl = "($prefixe.ce_utilisateur = %1\$d)";
1840 jpm 199
		$whereAuteur = sprintf($sqlTpl, $id);
200
		$this->addWhere('masque.auteur', $whereAuteur);
201
	}
202
 
203
	private function ajouterContrainteAuteurEmail() {
204
		$email = $this->parametres['masque.auteur'];
1871 jpm 205
		$prefixe = $this->getPrefixe();
2048 mathias 206
		$sqlTpl = "($prefixe.courriel_utilisateur LIKE %1\$s )";
1840 jpm 207
		$emailP = $this->bdd->proteger("$email%");
208
		$whereAuteur = sprintf($sqlTpl, $emailP);
209
		$this->addWhere('masque.auteur', $whereAuteur);
210
	}
211
 
212
	/**
213
	 * Retourne une clause where du style:
214
	 * CONCAT(IF(du.prenom IS NULL, "", du.prenom), [...] vdi.i_nomutilisateur) REGEXP 'xxx'
215
	 */
216
	private function ajouterContrainteAuteurIntitule() {
217
		$auteurExplode = explode(' ', $this->parametres['masque.auteur']);
218
		$nbreMots = count($auteurExplode);
219
 
220
		if ($nbreMots == 1) {
221
			$this->ajouterContrainteAuteurPrenomOuNom();
222
		} else if ($nbreMots == 2) {
223
			$this->ajouterContrainteAuteurPrenomEtNom();
224
		}
225
	}
226
 
227
	private function ajouterContrainteAuteurPrenomOuNom() {
228
		$prenomOuNom = $this->parametres['masque.auteur'];
229
 
230
		$sqlTpl = 'CONCAT(%s,%s) LIKE %s';
1871 jpm 231
		$prefixe = $this->getPrefixe();
232
		$champsPrenomSql = self::ajouterIfNullPourConcat($this->champsPrenom, $prefixe);
233
		$champsNomSql = self::ajouterIfNullPourConcat($this->champsNom, $prefixe);
1840 jpm 234
		$auteurMotif = $this->bdd->proteger("%$prenomOuNom%");
235
 
236
		$auteurWhere = sprintf($sqlTpl, $champsPrenomSql, $champsNomSql, $auteurMotif);
237
		$this->addWhere('masque.auteur', $auteurWhere);
238
	}
239
 
240
	private function ajouterContrainteAuteurPrenomEtNom() {
241
		list($prenom, $nom) = explode(' ', $this->parametres['masque.auteur']);
242
 
243
		$sqlTpl = '(CONCAT(%1$s,%2$s) LIKE %3$s AND CONCAT(%1$s,%2$s) LIKE %4$s)';
1871 jpm 244
		$prefixe = $this->getPrefixe();
245
		$champsPrenomSql = self::ajouterIfNullPourConcat($this->champsPrenom, $prefixe);
246
		$champsNomSql = self::ajouterIfNullPourConcat($this->champsNom, $prefixe);
1840 jpm 247
		$prenomMotif = $this->bdd->proteger("%$prenom%");
248
		$nomMotif = $this->bdd->proteger("%$nom%");
249
 
250
		$auteurWhere = sprintf($sqlTpl, $champsPrenomSql, $champsNomSql, $prenomMotif, $nomMotif);
251
		$this->addWhere('masque.auteur', $auteurWhere);
252
	}
253
 
1881 jpm 254
	/**
255
	 * Lorsque l'on concatène des champs, un seul NULL prend le dessus.
1840 jpm 256
	 * Il faut donc utiliser la syntaxe IFNULL(%s, "").
1881 jpm 257
	 * Cette fonction effectue aussi l'implode() "final".
1840 jpm 258
	 */
1871 jpm 259
	private static function ajouterIfNullPourConcat($champs, $prefixe) {
1840 jpm 260
		$champsProteges = array();
261
		foreach ($champs as $champ) {
1871 jpm 262
			if (strstr($champ, '.') === false) {
263
				$champ = "$prefixe.$champ";
264
			}
1840 jpm 265
			$champsProteges[] = "IFNULL($champ, '')";
266
		}
267
		return implode(',', $champsProteges);
268
	}
269
 
270
	private function ajouterContrainteDate() {
271
		if (isset($this->parametres['masque.date'])) {
272
			$date = $this->parametres['masque.date'];
1871 jpm 273
			if (preg_match('/^\d{4}$/', $date) && $date < 2030 && $date > 1600) {
274
				$sqlTpl = "YEAR(do.date_observation) = %d";
1840 jpm 275
				$dateWhere = sprintf($sqlTpl, $date);
276
				$this->addWhere('masque.date', $dateWhere);
277
			} else {
1871 jpm 278
				$sqlTpl = "do.date_observation = %s";
279
				$dateP = $this->bdd->proteger($date);
1840 jpm 280
				$dateWhere = sprintf($sqlTpl, $dateP);
281
				$this->addWhere('masque.date', $dateWhere);
282
			}
1871 jpm 283
 
2031 aurelien 284
			$this->ajouterJoinObsSiNecessaire();
1840 jpm 285
		}
286
	}
287
 
288
	private function ajouterContrainteDepartement() {
289
		if (isset($this->parametres['masque.departement'])) {
290
			$dept = $this->parametres['masque.departement'];
291
			$deptMotif = $this->bdd->proteger("INSEE-C:$dept");
1871 jpm 292
			$this->addWhere('masque.departement', "do.ce_zone_geo LIKE $deptMotif");
293
 
2031 aurelien 294
			$this->ajouterJoinObsSiNecessaire();
1840 jpm 295
		}
296
	}
1985 aurelien 297
 
2048 mathias 298
	private function ajouterContraintePays() {
1985 aurelien 299
		if (isset($this->parametres['masque.pays'])) {
300
			// Attention le standard contient parfois FX pour la france métropolitaine
301
			// Dans ce cas particulier on cherche donc FR et FX
302
			$this->parametres['masque.pays'] = strtoupper($this->parametres['masque.pays']);
303
			if(strpos($this->parametres['masque.pays'], 'FR') !== false) {
304
				$this->parametres['masque.pays'] = str_replace('FR', 'FR,FX', $this->parametres['masque.pays']);
305
			}
2048 mathias 306
			$pays = explode(',', $this->parametres['masque.pays']);
307
			$pays = implode(',', $this->bdd->proteger($pays));
308
			$this->addWhere('masque.pays', "do.pays IN ($pays)");
309
 
310
			$this->ajouterJoinObsSiNecessaire();
311
		}
1985 aurelien 312
	}
1840 jpm 313
 
314
	private function ajouterContrainteIdZoneGeo() {
315
		if (isset($this->parametres['masque.id_zone_geo'])) {
316
			$idZgMotif = $this->bdd->proteger($this->parametres['masque.id_zone_geo']);
1871 jpm 317
			$this->addWhere('masque.id_zone_geo', "do.ce_zone_geo = $idZgMotif");
2031 aurelien 318
 
319
			$this->ajouterJoinObsSiNecessaire();
1840 jpm 320
		}
321
	}
322
 
323
	private function ajouterContrainteGenre() {
324
		if (isset($this->parametres['masque.genre'])) {
325
			$genre = $this->parametres['masque.genre'];
2004 mathias 326
			$genreMotif = $this->bdd->proteger("$genre%");
1871 jpm 327
			$this->addWhere('masque.genre', "do.nom_sel LIKE $genreMotif");
328
 
2031 aurelien 329
			$this->ajouterJoinObsSiNecessaire();
1840 jpm 330
		}
331
	}
332
 
333
	private function ajouterContrainteFamille() {
334
		if (isset($this->parametres['masque.famille'])) {
335
			$familleMotif = $this->bdd->proteger($this->parametres['masque.famille']);
1871 jpm 336
			$this->addWhere('masque.famille', "do.famille = $familleMotif");
337
 
2031 aurelien 338
			$this->ajouterJoinObsSiNecessaire();
1840 jpm 339
		}
340
	}
341
 
342
	private function ajouterContrainteNs() {
343
		if (isset($this->parametres['masque.ns'])) {
344
			$ns = $this->parametres['masque.ns'];
345
			$nsMotif = $this->bdd->proteger("$ns%");
1871 jpm 346
			$this->addWhere('masque.ns', "do.nom_sel LIKE $nsMotif");
2031 aurelien 347
 
348
			$this->ajouterJoinObsSiNecessaire();
1840 jpm 349
		}
350
	}
351
 
352
	private function ajouterContrainteNn() {
353
		if (isset($this->parametres['masque.nn'])) {
1871 jpm 354
			$sqlTpl = '(do.nom_sel_nn = %1$d OR do.nom_ret_nn = %1$d)';
1840 jpm 355
			$nnWhere = sprintf($sqlTpl, $this->parametres['masque.nn']);
356
			$this->addWhere('masque.nn', $nnWhere);
1871 jpm 357
 
2031 aurelien 358
			$this->ajouterJoinObsSiNecessaire();
1840 jpm 359
		}
360
	}
361
 
362
	private function ajouterContrainteReferentiel() {
363
		if (isset($this->parametres['masque.referentiel'])) {
364
			$ref = $this->parametres['masque.referentiel'];
365
			$refMotif = $this->bdd->proteger("$ref%");
1871 jpm 366
			$this->addWhere('masque.referentiel', "do.nom_referentiel LIKE $refMotif");
367
 
2031 aurelien 368
			$this->ajouterJoinObsSiNecessaire();
1840 jpm 369
		}
370
	}
371
 
372
	private function ajouterContrainteCommune() {
373
		if (isset($this->parametres['masque.commune'])) {
374
			$commune = $this->parametres['masque.commune'];
375
			$communeMotif = $this->bdd->proteger("$commune%");
1871 jpm 376
			$this->addWhere('masque.commune', "do.zone_geo LIKE $communeMotif");
377
 
2031 aurelien 378
			$this->ajouterJoinObsSiNecessaire();
379
		}
380
	}
2138 mathias 381
 
382
	/**
383
	 * Si masque.pninscritsseulement vaut true, les observations ayant un tag
384
	 * "plantnet" mais dont l'auteur n'est pas inscrit à TB seront éliminées
385
	 * (décision FlorisTic 2016-09)
386
	 */
387
	protected function ajouterContraintePnInscrits() {
388
		if (isset($this->parametres['masque.pninscritsseulement'])) {
389
			// avec la classe ParametresFiltrage, on ne passe là que si le masque vaut 1
390
			$motifMotClePlantnet = "'%plantnet%'";
391
			$this->addWhere('masque.pninscritsseulement', "((do.mots_cles_texte NOT LIKE $motifMotClePlantnet OR do.mots_cles_texte IS NULL) OR do.ce_utilisateur != 0)");
392
 
393
			$this->ajouterJoinObsSiNecessaire();
394
		}
395
	}
2031 aurelien 396
 
397
	private function ajouterJoinObsSiNecessaire() {
1871 jpm 398
			if ($this->etreAppliImg()) {
2033 aurelien 399
				$this->addJoin($this->getSqlJointureObs());
1871 jpm 400
			}
1840 jpm 401
	}
2033 aurelien 402
 
2035 aurelien 403
	private function getSqlJointureObs() {
404
		$typeJointure = !empty($this->parametres['masque']) ? 'LEFT' : 'INNER';
405
		return $typeJointure.' JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ';
2033 aurelien 406
	}
1840 jpm 407
 
2138 mathias 408
	// la constrainte de nostre bon roy
1845 jpm 409
	public function ajouterConstrainteAppliObs() {
410
		$this->ajouterContrainteTagCel();
411
		$this->ajouterContrainteType();
2138 mathias 412
		// TODO : ATTENTION -> vu que l'on utilise une vue basée sur les images, nous devons grouper par obs
1871 jpm 413
		$this->addGroupBy('do.id_observation');
1845 jpm 414
	}
415
 
416
	private function ajouterContrainteType() {
1933 aurelien 417
		// Les contraintes régissant les onglets sont issus de la réunion dont le compte rendu
418
		// disponible ici : http://tela-botanica.net/intranet/wakka.php?wiki=Octobre2014
419
		// Ce lien est à modifier pour pointer vers toute nouvelle réunion modifiant ce fonctionnement
420
 
1871 jpm 421
		if (isset($this->parametres['masque.type'])) {
422
			if (array_key_exists('adeterminer', $this->parametres['masque.type'])) {
1933 aurelien 423
				// A DETERMINER : toutes les observations qui ont le tag "aDeterminer"
424
				// *ou* qui n'ont pas de nom d'espèce
1871 jpm 425
				// *ou* qui ont la "certitude" à ("aDeterminer" *ou* "douteux")
426
				$this->addWhere('masque.type', '('.
427
					'do.certitude = "aDeterminer" '.
428
					'OR do.certitude = "douteux" '.
429
					'OR do.mots_cles_texte LIKE "%aDeterminer%" '.
430
					'OR do.nom_sel_nn IS NULL '.
431
					'OR do.nom_sel_nn = 0 '.// il ne DEVRAIT pas y avoir d'entrées à 0, mais il y en a quand-même !!
432
					')');
433
			}
1933 aurelien 434
 
1871 jpm 435
			if (array_key_exists('validees', $this->parametres['masque.type'])) {
1933 aurelien 436
				// VALIDEES : toutes les observations ayant un commentaire doté de proposition_retenue = 1
437
				// ou bien possédant une proposition initiale avec un nom valide ayant totalisé un score d'au moins 4
438
				// (ce qui correspond à au moins deux votes positifs dans la plupart des cas, dont un identifié)
439
				$sous_requete_score_prop_votees = $this->getSousRequeteSommeVotesPropositions();
1871 jpm 440
				$this->addJoin('INNER JOIN del_commentaire AS dc '.
1933 aurelien 441
					'ON ( '.
442
						'do.id_observation = dc.ce_observation '.
443
						'AND ( '.
444
							'dc.proposition_retenue = 1 OR '.
445
							'( '.
446
								'dc.proposition_initiale = 1 '.
447
								'AND dc.nom_sel_nn != 0 '.
448
								'AND dc.nom_sel_nn IS NOT NULL '.
449
								' AND dc.id_commentaire IN ('.$sous_requete_score_prop_votees.' >= 4) '.
450
							') '.
451
						') '.
452
					')'
453
				);
1871 jpm 454
			}
1933 aurelien 455
 
456
			if(array_key_exists('aconfirmer', $this->parametres['masque.type'])) {
457
				// A CONFIRMER : toutes les observations moins les validées et à confirmer
458
				// i.e. : des observations avec un nom valide, qui ne sont pas à déterminer
459
				// (ni certitude "aDeterminer" ou "douteuse", ni mot clé),
460
				// ne possédant pas de proposition officiellement retenue
461
				// et ayant une proposition initiale totalisant un score de moins de 4
2048 mathias 462
				$sous_requete_score_prop_votees = $this->getSousRequeteSommeVotesPropositions();
1933 aurelien 463
				$this->addWhere('masque.type',
464
						'('.
465
							'do.id_observation IN ('.
466
								'SELECT dc.ce_observation FROM del_commentaire dc WHERE dc.proposition_retenue = 0'.
2048 mathias 467
								' AND ( '.
468
									'dc.proposition_initiale = 1 '.
469
									'AND dc.nom_sel_nn != 0 '.
470
									'AND dc.nom_sel_nn IS NOT NULL '.
471
									'AND dc.id_commentaire IN ('.$sous_requete_score_prop_votees.' < 4) '.
1933 aurelien 472
								') '.
473
							') AND do.id_observation NOT IN ('.
474
								'SELECT dc.ce_observation FROM del_commentaire dc '.
475
								'WHERE '.
476
								' dc.proposition_retenue = 1'.
477
							') '.
2048 mathias 478
							'AND do.certitude != "douteux" AND do.certitude != "aDeterminer" '.
479
							'AND do.mots_cles_texte NOT LIKE "%aDeterminer%" '.
480
							'AND do.nom_sel_nn != 0 '.
1933 aurelien 481
							'AND do.nom_sel_nn IS NOT NULL'.
482
						') '
483
					);
484
			}
1845 jpm 485
 
2033 aurelien 486
			$this->ajouterJoinObsSiNecessaire();
1845 jpm 487
		}
488
	}
1933 aurelien 489
 
490
	private function getSousRequeteSommeVotesPropositions() {
491
		// ATTENTION : un vote identifié compte 3 votes anonymes (dans les deux sens)
2048 mathias 492
		return  'SELECT ce_proposition '.
493
				'FROM del_commentaire_vote dcv '.
494
				'GROUP BY ce_proposition HAVING '.
495
				'SUM(CASE '.
496
				'	WHEN valeur = 1 AND dcv.ce_utilisateur REGEXP \'^-?[0-9]+$\' != 0 THEN 3 '.
497
				'	WHEN valeur = 0 AND dcv.ce_utilisateur REGEXP \'^-?[0-9]+$\' != 0 THEN -3 '.
498
				'	WHEN valeur = 1 AND dcv.ce_utilisateur REGEXP \'^-?[0-9]+$\' = 0 THEN 1 '.
499
				'	WHEN valeur = 0 AND dcv.ce_utilisateur REGEXP \'^-?[0-9]+$\' = 0 THEN -1 '.
500
				'END '.
1933 aurelien 501
			') ';
502
	}
1845 jpm 503
 
1840 jpm 504
	public function ajouterConstrainteAppliImg() {
505
		$this->ajouterContrainteMilieu();
506
		$this->ajouterContrainteTri();
507
		$this->ajouterContrainteTagCel();
508
		$this->ajouterContrainteTagDel();
2138 mathias 509
		$this->ajouterContraintePnInscrits();
1840 jpm 510
	}
511
 
512
	private function ajouterContrainteMilieu() {
513
		if (isset($this->parametres['masque.milieu'])) {
514
			$milieu = $this->parametres['masque.milieu'];
515
			$milieuMotif = $this->bdd->proteger("%$milieu%");
1871 jpm 516
			$this->addWhere('masque.milieu', "do.milieu LIKE $milieuMotif");
517
 
2033 aurelien 518
			$this->ajouterJoinObsSiNecessaire();
1840 jpm 519
		}
520
	}
521
 
522
	private function ajouterContrainteTri() {
523
		if (isset($this->parametres['tri'])) {
524
			$tri = $this->parametres['tri'];
525
 
1863 jpm 526
			if (isset($this->parametres['protocole'])  && ($tri == 'moyenne-arithmetique' || $tri == 'points')) {
1840 jpm 527
				// $this->parametres['protocole'] *est* défini (cf Outils::filtrerUrlsParams...())
1871 jpm 528
				$sqlTpl = 'LEFT JOIN del_image_stat AS dis ON di.id_image = dis.ce_image AND dis.ce_protocole = %d';
1840 jpm 529
				$triSql = sprintf($sqlTpl, $this->parametres['protocole']);
530
				$this->addJoinDis($triSql);
531
			}
532
 
533
			if (isset($this->parametres['ordre']) && $tri == 'tags') {
534
				$typeJointure = ($this->parametres['ordre'] == 'desc') ? 'INNER' : 'LEFT';
1871 jpm 535
				$this->addJoin("$typeJointure JOIN del_image_stat AS dis ON di.id_image = dis.ce_image");
1840 jpm 536
			}
537
		}
538
	}
539
 
540
	private function ajouterContrainteTagCel() {
1845 jpm 541
		if (isset($this->parametres['masque.tag_cel'])) {
1840 jpm 542
			if (isset($this->parametres['masque.tag_cel']['AND'])) {
543
				$tags = $this->parametres['masque.tag_cel']['AND'];
544
				$clausesWhere = array();
545
				foreach ($tags as $tag) {
546
					$tagMotif = $this->bdd->proteger("%$tag%");
1922 jpm 547
					if ($this->etreAppliImg()) {
1981 aurelien 548
						$sousRequete = 'SELECT id_observation '.
1922 jpm 549
							'FROM del_observation '.
550
							"WHERE mots_cles_texte LIKE $tagMotif ";
551
						$sql = " (di.mots_cles_texte LIKE $tagMotif OR di.id_image IN ($sousRequete) ) ";
552
					} else {
553
						// WARNING : la sous-requête est la meilleure solution trouvée pour contrer le fonctionnement
554
						// étrange de l'optimiseur de MYSQL 5.6 (à retester avec Mysql 5.7 et suivant).
555
						$sousRequete = 'SELECT DISTINCT ce_observation '.
556
							'FROM del_image '.
557
							"WHERE mots_cles_texte LIKE $tagMotif ".
558
							'AND ce_observation IS NOT NULL';
559
						$sql = " (do.mots_cles_texte LIKE $tagMotif OR do.id_observation IN ($sousRequete)) ";
560
					}
561
					$clausesWhere[] = $sql;
1840 jpm 562
				}
563
				$whereTags = implode(' AND ', $clausesWhere);
564
				$this->addWhere('masque.tag_cel', "($whereTags)");
565
			} else if (isset($this->parametres['masque.tag_cel']['OR'])) {
566
				$tags = $this->parametres['masque.tag_cel']['OR'];
1922 jpm 567
				$tagMotif = $this->bdd->proteger(implode('|', $tags));
1871 jpm 568
				$sqlTpl = "CONCAT(IFNULL(do.mots_cles_texte,''),IFNULL(di.mots_cles_texte,'')) REGEXP %s";
1840 jpm 569
				$tagSql = sprintf($sqlTpl, $tagMotif);
1922 jpm 570
 
1840 jpm 571
				$this->addWhere('masque.tag_cel', $tagSql);
1922 jpm 572
 
573
				if ($this->etreAppliObs()) {
574
					$this->addJoin('LEFT JOIN del_image AS di ON (di.ce_observation = do.id_observation) ');
575
				}
1840 jpm 576
			}
2033 aurelien 577
			$this->ajouterJoinObsSiNecessaire();
1840 jpm 578
		}
579
	}
580
 
581
	/**
1881 jpm 582
	 * Plusieurs solutions sont disponibles dans les anciennes versions (voir DelTk et l'historique SVN de ce fichier).
1840 jpm 583
	 */
584
	private function ajouterContrainteTagDel() {
585
		if (isset($this->parametres['masque.tag_del'])) {
2029 aurelien 586
			$nbTags = $this->getNombreDeTags();
587
			if($nbTags > 1) {
2120 mathias 588
				// sous-requêtes car le GROUP BY avec GROUP_CONCAT est *trop* lent
589
				if (isset($this->parametres['masque.tag_del']['AND'])) {
590
					foreach ($this->parametres['masque.tag_del']['AND'] as $mc) {
591
						$sousRequete = 'SELECT ce_image '.
592
							'FROM del_image_tag '.
593
							'WHERE actif = 1 '.
594
							"AND tag_normalise LIKE '%$mc%' ";
595
						$this->addWhere('masque.tag_del', "di.id_image IN ($sousRequete)");
596
					}
597
				} else if (isset($this->parametres['masque.tag_del']['OR'])) {
598
					$tagsMotif = "'(" . implode('|', $this->parametres['masque.tag_del']['OR']) . ")'";
2029 aurelien 599
					$sousRequete = 'SELECT ce_image '.
600
						'FROM del_image_tag '.
601
						'WHERE actif = 1 '.
2120 mathias 602
						"AND tag_normalise REGEXP $tagsMotif ";
603
 
604
					$this->addWhere('masque.tag_del', "di.id_image IN ($sousRequete)");
2029 aurelien 605
				}
606
			} else {
2033 aurelien 607
				// Si un seul tag est demandé il se trouve dans le OR dans le cas de la recherche
608
				// spécifique et dans le AND dans le cas de la recherche générale
609
				// WTF?
610
				$tag = "";
611
				if(isset($this->parametres['masque.tag_del']['OR'][0])) {
612
					$tag = $this->parametres['masque.tag_del']['OR'][0];
613
				} else if(isset($this->parametres['masque.tag_del']['AND'][0])) {
614
					$tag = $this->parametres['masque.tag_del']['AND'][0];
615
				}
616
 
1922 jpm 617
				$sousRequete = 'SELECT ce_image '.
2029 aurelien 618
						'FROM del_image_tag '.
619
						'WHERE actif = 1 '.
620
						'AND tag_normalise LIKE '.$this->bdd->proteger($tag.'%');
2120 mathias 621
 
622
				$this->addWhere('masque.tag_del', "di.id_image IN ($sousRequete)");
1840 jpm 623
			}
2029 aurelien 624
 
1840 jpm 625
		}
626
	}
2029 aurelien 627
 
628
	private function getNombreDeTags() {
629
		$somme = 0;
630
		if (isset($this->parametres['masque.tag_del']['AND'])) {
631
			$somme = count($this->parametres['masque.tag_del']['AND']);
2048 mathias 632
		} else {
633
			$somme = count($this->parametres['masque.tag_del']['OR']);
2029 aurelien 634
		}
635
		return $somme;
636
	}
1840 jpm 637
 
638
	/**
639
	 * Partie spécifique à PictoFlora:
1863 jpm 640
	 * Attention : si le critère de tri n'est pas suffisant, les résultats affichés peuvent varier à chaque appel
641
	 * de la même page de résultat de PictoFlora.
1840 jpm 642
	 */
643
	public function definirOrdreSqlAppliImg() {
644
		$ordre = $this->parametres['ordre'];
1968 aurelien 645
 
646
		$tri = isset($this->parametres['tri']) ? $this->parametres['tri'] : '';
647
		switch ($tri) {
1863 jpm 648
			case 'moyenne-arithmetique' :
1888 jpm 649
				$this->addOrderBy("dis.moyenne $ordre, dis.nb_votes $ordre, id_image $ordre");
1840 jpm 650
				break;
651
			case 'points' :
1888 jpm 652
				$this->addOrderBy("dis.nb_points $ordre, dis.moyenne $ordre, dis.nb_votes $ordre, id_image $ordre");
1840 jpm 653
				break;
654
			case 'tags' :
1888 jpm 655
				$this->addOrderBy("dis.nb_tags $ordre, id_image $ordre");
1840 jpm 656
				break;
657
			case 'date_observation' :
1871 jpm 658
				$this->addOrderBy("date_observation $ordre, ce_observation $ordre");
1840 jpm 659
				break;
1863 jpm 660
			case 'date_transmission' :
1840 jpm 661
			default:
1871 jpm 662
				$this->addOrderBy("di.date_transmission $ordre, ce_observation $ordre");
1840 jpm 663
		}
664
	}
665
 
1845 jpm 666
	public function definirOrdreSqlAppliObs() {
667
		$ordre = $this->parametres['ordre'];
668
 
669
		// parmi self::$tri_possible
1968 aurelien 670
		$tri = isset($this->parametres['tri']) ? $this->parametres['tri'] : '';
671
			switch ($tri) {
1845 jpm 672
			case 'date_observation' :
673
				$this->addOrderBy("date_observation $ordre, id_observation $ordre");
1933 aurelien 674
				break;
2048 mathias 675
			case 'nb_commentaires' :
676
				$sql_nb_comms = '(SELECT COUNT(id_commentaire) FROM del_commentaire AS dc WHERE ce_observation = id_observation)';
677
				$this->addOrderBy("$sql_nb_comms $ordre, id_observation $ordre");
1845 jpm 678
				break;
1933 aurelien 679
			case 'date_transmission' :
1845 jpm 680
			default:
1871 jpm 681
				$this->addOrderBy("do.date_transmission $ordre, id_observation $ordre");
1845 jpm 682
		}
683
	}
684
 
1840 jpm 685
	public function getAliasDesChamps($champsEtAlias, $select = null, $prefix = null) {
686
		$arr = ($select) ? array_intersect_key($champsEtAlias, array_flip($select)) :  $champsEtAlias;
687
		$keys = array_keys($arr);
688
 
689
		if ($prefix) {
690
			array_walk($keys, create_function('&$val, $k, $prefix', '$val = sprintf("%s.`%s`", $prefix, $val);'), $prefix);
691
		} else {
692
			array_walk($keys, create_function('&$val, $k', '$val = sprintf("`%s`", $val);'));
693
		}
694
 
695
		return implode(', ', array_map(create_function('$v, $k', 'return sprintf("%s AS `%s`", $k, $v);'), $arr, $keys));
696
	}
697
 
698
	public function getVotesDesImages($idsImages, $protocole = null) {
699
		if (!$idsImages) return;
700
 
701
		$mappingVotes = $this->conteneur->getParametreTableau('votes.mapping');
702
		$mappingProtocoles = $this->conteneur->getParametreTableau('protocoles.mapping');
703
 		$selectVotes = array('id_vote', 'ce_image', 'ce_protocole', 'ce_utilisateur', 'valeur', 'date');
704
		$selectProtocole = array('id_protocole', 'intitule', 'descriptif', 'tag');
705
		$voteChamps = $this->getAliasDesChamps($mappingVotes, $selectVotes, 'v'); // "v": cf alias dans la requête
706
		$protoChamps = $this->getAliasDesChamps($mappingProtocoles, $selectProtocole, 'p');
707
		$idImgsConcat = implode(',', $idsImages);
708
 
709
		$requete = "SELECT $voteChamps, $protoChamps ".
710
			'FROM del_image_vote AS v '.
711
			'	INNER JOIN del_image_protocole AS p ON (v.ce_protocole = p.id_protocole) '.
712
			"WHERE v.ce_image IN ($idImgsConcat) ".
713
			($protocole ? "	AND v.ce_protocole = $protocole " : '').
714
			"ORDER BY FIELD(v.ce_image, $idImgsConcat) ".
715
			'-- '.__FILE__.':'.__LINE__;
716
		return $this->bdd->recupererTous($requete);
717
	}
718
 
719
	/**
720
	 * Ajoute les informations sur le protocole et les votes aux images.
721
	 *
722
	 * ATTENTION : Subtilité, nous passons ici le tableau d'images indexé par id_image qui est bien
723
	 * plus pratique pour associer les vote à un tableau, puisque nous ne connaissons pas les id d'observation.
724
	 * Mais magiquement (par référence), cela va remplir notre tableau indexé par couple d'id (id_image, id_observation)
725
	 * cf ListeImages::reformateImagesDoubleIndex() à qui revient la tâche de créer ces deux versions
726
	 * simultanément lorsque c'est encore possible.
727
	 */
1881 jpm 728
	// TODO : supprimer cette "subtilité" source d'erreurs
1840 jpm 729
	public function ajouterInfosVotesProtocoles($votes, &$images) {
730
		if (!$votes) return;
1845 jpm 731
 
1840 jpm 732
		$mappingVotes = $this->conteneur->getParametreTableau('votes.mapping');
733
		$mappingProtocoles = $this->conteneur->getParametreTableau('protocoles.mapping');
734
 
735
		// pour chaque vote
736
		foreach ($votes as $vote) {
737
			$imgId = $vote['image.id'];
738
			$protoId = $vote['protocole.id'];
739
 
740
			if (!array_key_exists('protocoles_votes', $images[$imgId]) ||
1863 jpm 741
					!array_key_exists($protoId, $images[$imgId]['protocoles_votes'])) {
1840 jpm 742
				// extrait les champs spécifique au protocole (le LEFT JOIN de chargerVotesImage les ramène en doublons
743
				$protocole = array_intersect_key($vote, array_flip($mappingProtocoles));
744
				$images[$imgId]['protocoles_votes'][$protoId] = $protocole;
745
			}
746
 
747
			$chpsVotes = array('id_vote', 'ce_image', 'ce_utilisateur', 'valeur', 'date');
748
			$voteSelection = array_intersect_key($mappingVotes, array_flip($chpsVotes));
749
			$vote = array_intersect_key($vote, array_flip($voteSelection));
750
			$images[$imgId]['protocoles_votes'][$protoId]['votes'][$vote['vote.id']] = $vote;
751
		}
752
	}
1845 jpm 753
 
754
	public function getTotalLignesTrouvees() {
1888 jpm 755
		$resultat = $this->bdd->recuperer('SELECT FOUND_ROWS() AS nbre -- '.__FILE__.':'.__LINE__);
1845 jpm 756
		return intval($resultat['nbre']);
757
	}
2120 mathias 758
 
759
	public function getRequeteIdObsMonactiviteTout($id_utilisateur, $limite = "") {
760
		/*
761
		Une action c'est :
762
		- Quelqu'un commente mon observation
763
		- Quelqu'un fait une proposition sur mon observation
764
 
765
		- Quelqu'un vote pour ma proposition
766
		- Quelqu'un commente ma proposition ou mon commentaire
767
 
768
		- Quelqu'un vote pour une proposition sur mon observation
769
		- Quelqu'un commente une proposition ou un commentaire sur mon observation
2072 aurelien 770
		*/
771
 
772
		// on selectionne aussi la combinaison des champs de date afin que la liste soit triée correctement
2120 mathias 773
		// pas d'autre meilleure solution mais attention ce comportement dépend entièrement de mysql
774
		$requete = "SELECT SQL_CALC_FOUND_ROWS DISTINCT id_observation, ".$this->getCombinaisonChampsDateMax()." as date_max FROM del_observation do ".
775
				$this->getJointureMonActivite($id_utilisateur).
776
				$this->getConditionMonActivite($id_utilisateur).
777
				"ORDER BY date_max DESC ".
2049 aurelien 778
				$limite;
2120 mathias 779
 
780
		return $requete;
2049 aurelien 781
	}
782
 
783
	public function getRequeteNbEvenementsDepuisDate($id_utilisateur, $date) {
2120 mathias 784
		$requete = "SELECT COUNT(DISTINCT id_observation) as nb_evenements FROM del_observation do ".
785
				$this->getJointureMonActivite($id_utilisateur).
2049 aurelien 786
				$this->getConditionMonActivite($id_utilisateur).
2120 mathias 787
				"AND ".$this->getCombinaisonChampsDateMax()." > '".$date."' ".
788
				"ORDER BY ".$this->getCombinaisonChampsDateMax()." DESC ";
789
 
2049 aurelien 790
		return $requete;
791
	}
792
 
793
	public function getEvenementsObs($idsObsConcat, $id_utilisateur) {
2120 mathias 794
		$sous_champ_date_max = $this->getCombinaisonChampsDateMax()." as date_max";
2063 aurelien 795
		$sous_champ_date = "dc.date as date_com, dc.nom_sel as nom_sel_com, dcpr.ce_commentaire_parent as parent_com, dcv.date as date_vote, ".
796
							"do.date_observation as date_obs, dcp.date_validation as date_validation, dcpr.date as date_com_reponse, ".
2120 mathias 797
							"dcvp.nom_sel as nom_sel_com_parent";
2056 aurelien 798
		$sous_champs_utilisateurs = "dc.ce_utilisateur as utilisateur_commentaire, dcp.ce_utilisateur as utilisateur_commentaire_valide, ".
799
									"dcv.ce_utilisateur as utilisateur_vote_commentaire, do.ce_utilisateur as utilisateur_observation, ".
2063 aurelien 800
									"dcp.ce_validateur as utilisateur_validation, dcvp.ce_utilisateur as utilisateur_commentaire_vote, ".
2120 mathias 801
									"dcpr.ce_utilisateur as utilisateur_commentaire_reponse";
2067 aurelien 802
 
803
		$sous_champs_infos = "dc.nom_sel as proposition_commentaire_nom_sel, dc.texte as proposition_commentaire_texte, dcp.nom_sel as proposition_validee_nom_sel, ".
2120 mathias 804
							 "dcvp.nom_sel as proposition_commentaire_nom_sel_votee, dcpr.texte as proposition_commentaire_texte_commente";
2067 aurelien 805
 
2120 mathias 806
		$requete = "SELECT DISTINCT id_observation, ".$sous_champs_utilisateurs.", ".$sous_champ_date_max.", ".$sous_champ_date.", ".$sous_champs_infos." ".
807
				"FROM del_observation do ".
808
				$this->getJointureMonActivite($id_utilisateur).
809
				$this->getConditionMonActivite($id_utilisateur).
810
				"AND id_observation IN ($idsObsConcat) ORDER BY date_max DESC";
811
 
2049 aurelien 812
		$evenements = $this->bdd->recupererTous($requete);
813
		return $evenements;
814
	}
815
 
2120 mathias 816
	public function getJointureMonActivite($id_utilisateur) {
817
		return 	// quelqu'un commente mon observation ou fait une proposition
818
		"LEFT JOIN del_commentaire dc ON do.id_observation = dc.ce_observation ".
819
		"	AND do.ce_utilisateur = ".$id_utilisateur." ".
820
		"	AND dc.ce_utilisateur != ".$id_utilisateur." ".
821
		// quelqu'un valide ma proposition (et ce n'est pas moi qui l'ai validée)
822
		"LEFT JOIN del_commentaire dcp ON do.id_observation = dcp.ce_observation ".
823
		"	AND dcp.nom_sel IS NOT NULL AND dcp.ce_validateur != ".$id_utilisateur." ".
824
		"	AND dcp.ce_validateur != 0 ".
825
		"	AND dcp.date_validation IS NOT NULL ".
826
		"	AND dcp.ce_utilisateur = ".$id_utilisateur." ".
827
		// quelqu'un vote pour ma proposition ou sur une proposition sur une de mes observations
828
		"LEFT JOIN del_commentaire dcvp ON do.id_observation = dcvp.ce_observation ".
829
		"LEFT JOIN del_commentaire_vote dcv ON dcv.ce_proposition = dcvp.id_commentaire ".
2056 aurelien 830
		"AND (dcvp.ce_utilisateur = $id_utilisateur OR do.ce_utilisateur = $id_utilisateur) ".
2120 mathias 831
		"AND dcv.ce_utilisateur != $id_utilisateur ".
2063 aurelien 832
		"AND dcv.ce_utilisateur != dcvp.ce_utilisateur ".
2059 aurelien 833
		// Quelqu'un répond à l'un de mes commentaires ou commente une de mes propositions
2066 aurelien 834
		"LEFT JOIN del_commentaire dcpr ON do.id_observation = dcpr.ce_observation ".
2120 mathias 835
		"AND dcpr.ce_commentaire_parent = dcvp.id_commentaire AND dcvp.ce_utilisateur = $id_utilisateur ";
836
	}
837
 
838
	public function getConditionMonActivite($id_utilisateur, $type = "autres") {
839
		//TODO: gérer les cas suivants :
840
		// demander les activités des autres sur mes obs ou propositions (c'est dejà le cas)
841
		// demander mes activités
842
		// demander toutes les activités (combinaisons des deux cas ci dessus)
843
		return 	// Vérification que l'évènement me concerne (de près ou ou de loin)
844
		"WHERE (do.ce_utilisateur = $id_utilisateur OR dc.ce_utilisateur = $id_utilisateur ".
2056 aurelien 845
		"OR dcp.ce_utilisateur = $id_utilisateur OR dcv.ce_utilisateur = $id_utilisateur ".
2120 mathias 846
		"OR dcvp.ce_utilisateur = $id_utilisateur) AND ".
847
		// mais qu'il y a au moins eu une action de la part d'une autre personne
848
		"(dc.ce_utilisateur IS NOT NULL OR dcp.ce_utilisateur IS NOT NULL OR dcv.ce_utilisateur IS NOT NULL) ";
2049 aurelien 849
	}
2120 mathias 850
 
851
	private function getCombinaisonChampsDateMax() {
852
		return "GREATEST(IFNULL(dc.date,0), IFNULL(dcv.date,0), IFNULL(do.date_observation,0), IFNULL(dcp.date_validation,0), IFNULL(dcpr.date,0))";
2049 aurelien 853
	}
1840 jpm 854
}