Subversion Repositories eFlore/Applications.del

Rev

Rev 1968 | 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
 
1871 jpm 47
	private $champsPrenom = array('du.prenom', 'prenom_utilisateur');
1922 jpm 48
	private $champsNom = array('du.nom', 'nom_utilisateur');
49
	private $champsSousRequeteObs = array('masque.genre', 'masque.famille', 'masque.ns', 'masque.commune', 'masque.milieu');
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() {
73
		return $this->appli === 'IMG' ? true : false;
74
	}
75
 
76
	private function etreAppliObs() {
77
		return $this->appli === 'OBS' ? true : false;
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 '.
1980 aurelien 116
				"(SELECT id_observation FROM del_observation AS do WHERE $clauseWhereSousRequete ) ";
1922 jpm 117
			$this->requete['where']['OR'][] = "( $sousRequete )";
118
			unset($this->requete['join']['LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ']);
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
	/**
1881 jpm 161
 
1840 jpm 162
	 *
163
	 * @param $p les paramètres (notamment de masque) passés par l'URL et déjà traités/filtrés (sauf quotes)
164
	 * @param $req le tableau, passé par référence représentant les composants de la requête à bâtir
165
	 */
166
	public function ajouterContraintes() {
167
		$this->ajouterContrainteAuteur();
168
		$this->ajouterContrainteDate();
169
		$this->ajouterContrainteDepartement();
170
		$this->ajouterContrainteIdZoneGeo();
171
		$this->ajouterContrainteGenre();
172
		$this->ajouterContrainteFamille();
173
		$this->ajouterContrainteNs();
174
		$this->ajouterContrainteNn();
175
		$this->ajouterContrainteReferentiel();
176
		$this->ajouterContrainteCommune();
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();
184
			$this->addJoin("LEFT JOIN del_utilisateur AS du ON (du.id_utilisateur = $prefixe.ce_utilisateur) ");
1840 jpm 185
 
186
			if (is_numeric($auteur)) {
187
				$this->ajouterContrainteAuteurId();
1871 jpm 188
			} elseif(preg_match('/@[a-z0-9-]+(?:\.[a-z0-9-]+)*\.[a-z]{2,}$/i', $auteur)) {
1840 jpm 189
				$this->ajouterContrainteAuteurEmail();
190
			} else {
191
				$this->ajouterContrainteAuteurIntitule();
192
			}
193
		}
194
	}
195
 
196
	private function ajouterContrainteAuteurId() {
197
		$id = $this->parametres['masque.auteur'];
1871 jpm 198
		$prefixe = $this->getPrefixe();
199
		$sqlTpl = "(du.id_utilisateur = %1\$d OR $prefixe.ce_utilisateur = %1\$d)";
1840 jpm 200
		$whereAuteur = sprintf($sqlTpl, $id);
201
		$this->addWhere('masque.auteur', $whereAuteur);
202
	}
203
 
204
	private function ajouterContrainteAuteurEmail() {
205
		$email = $this->parametres['masque.auteur'];
1871 jpm 206
		$prefixe = $this->getPrefixe();
207
		$sqlTpl = "(du.courriel LIKE %1\$s OR $prefixe.courriel_utilisateur LIKE %1\$s )";
1840 jpm 208
		$emailP = $this->bdd->proteger("$email%");
209
		$whereAuteur = sprintf($sqlTpl, $emailP);
210
		$this->addWhere('masque.auteur', $whereAuteur);
211
	}
212
 
213
	/**
214
	 * Retourne une clause where du style:
215
	 * CONCAT(IF(du.prenom IS NULL, "", du.prenom), [...] vdi.i_nomutilisateur) REGEXP 'xxx'
216
	 */
217
	private function ajouterContrainteAuteurIntitule() {
218
		$auteurExplode = explode(' ', $this->parametres['masque.auteur']);
219
		$nbreMots = count($auteurExplode);
220
 
221
		if ($nbreMots == 1) {
222
			$this->ajouterContrainteAuteurPrenomOuNom();
223
		} else if ($nbreMots == 2) {
224
			$this->ajouterContrainteAuteurPrenomEtNom();
225
		}
226
	}
227
 
228
	private function ajouterContrainteAuteurPrenomOuNom() {
229
		$prenomOuNom = $this->parametres['masque.auteur'];
230
 
231
		$sqlTpl = 'CONCAT(%s,%s) LIKE %s';
1871 jpm 232
		$prefixe = $this->getPrefixe();
233
		$champsPrenomSql = self::ajouterIfNullPourConcat($this->champsPrenom, $prefixe);
234
		$champsNomSql = self::ajouterIfNullPourConcat($this->champsNom, $prefixe);
1840 jpm 235
		$auteurMotif = $this->bdd->proteger("%$prenomOuNom%");
236
 
237
		$auteurWhere = sprintf($sqlTpl, $champsPrenomSql, $champsNomSql, $auteurMotif);
238
		$this->addWhere('masque.auteur', $auteurWhere);
239
	}
240
 
241
	private function ajouterContrainteAuteurPrenomEtNom() {
242
		list($prenom, $nom) = explode(' ', $this->parametres['masque.auteur']);
243
 
244
		$sqlTpl = '(CONCAT(%1$s,%2$s) LIKE %3$s AND CONCAT(%1$s,%2$s) LIKE %4$s)';
1871 jpm 245
		$prefixe = $this->getPrefixe();
246
		$champsPrenomSql = self::ajouterIfNullPourConcat($this->champsPrenom, $prefixe);
247
		$champsNomSql = self::ajouterIfNullPourConcat($this->champsNom, $prefixe);
1840 jpm 248
		$prenomMotif = $this->bdd->proteger("%$prenom%");
249
		$nomMotif = $this->bdd->proteger("%$nom%");
250
 
251
		$auteurWhere = sprintf($sqlTpl, $champsPrenomSql, $champsNomSql, $prenomMotif, $nomMotif);
252
		$this->addWhere('masque.auteur', $auteurWhere);
253
	}
254
 
1881 jpm 255
	/**
256
	 * Lorsque l'on concatène des champs, un seul NULL prend le dessus.
1840 jpm 257
	 * Il faut donc utiliser la syntaxe IFNULL(%s, "").
1881 jpm 258
	 * Cette fonction effectue aussi l'implode() "final".
1840 jpm 259
	 */
1871 jpm 260
	private static function ajouterIfNullPourConcat($champs, $prefixe) {
1840 jpm 261
		$champsProteges = array();
262
		foreach ($champs as $champ) {
1871 jpm 263
			if (strstr($champ, '.') === false) {
264
				$champ = "$prefixe.$champ";
265
			}
1840 jpm 266
			$champsProteges[] = "IFNULL($champ, '')";
267
		}
268
		return implode(',', $champsProteges);
269
	}
270
 
271
	private function ajouterContrainteDate() {
272
		if (isset($this->parametres['masque.date'])) {
273
			$date = $this->parametres['masque.date'];
1871 jpm 274
			if (preg_match('/^\d{4}$/', $date) && $date < 2030 && $date > 1600) {
275
				$sqlTpl = "YEAR(do.date_observation) = %d";
1840 jpm 276
				$dateWhere = sprintf($sqlTpl, $date);
277
				$this->addWhere('masque.date', $dateWhere);
278
			} else {
1871 jpm 279
				$sqlTpl = "do.date_observation = %s";
280
				$dateP = $this->bdd->proteger($date);
1840 jpm 281
				$dateWhere = sprintf($sqlTpl, $dateP);
282
				$this->addWhere('masque.date', $dateWhere);
283
			}
1871 jpm 284
 
285
			if ($this->etreAppliImg()) {
286
				$this->addJoin('LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ');
287
			}
1840 jpm 288
		}
289
	}
290
 
291
	private function ajouterContrainteDepartement() {
292
		if (isset($this->parametres['masque.departement'])) {
293
			$dept = $this->parametres['masque.departement'];
294
			$deptMotif = $this->bdd->proteger("INSEE-C:$dept");
1871 jpm 295
			$this->addWhere('masque.departement', "do.ce_zone_geo LIKE $deptMotif");
296
 
297
			if ($this->etreAppliImg()) {
298
				$this->addJoin('LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ');
299
			}
1840 jpm 300
		}
301
	}
302
 
303
	private function ajouterContrainteIdZoneGeo() {
304
		if (isset($this->parametres['masque.id_zone_geo'])) {
305
			$idZgMotif = $this->bdd->proteger($this->parametres['masque.id_zone_geo']);
1871 jpm 306
			$this->addWhere('masque.id_zone_geo', "do.ce_zone_geo = $idZgMotif");
307
 
308
			if ($this->etreAppliImg()) {
309
				$this->addJoin('LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ');
310
			}
1840 jpm 311
		}
312
	}
313
 
314
	private function ajouterContrainteGenre() {
315
		if (isset($this->parametres['masque.genre'])) {
316
			$genre = $this->parametres['masque.genre'];
317
			$genreMotif = $this->bdd->proteger("%$genre% %");
1871 jpm 318
			$this->addWhere('masque.genre', "do.nom_sel LIKE $genreMotif");
319
 
320
			if ($this->etreAppliImg()) {
321
				$this->addJoin('LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ');
322
			}
1840 jpm 323
		}
324
	}
325
 
326
	private function ajouterContrainteFamille() {
327
		if (isset($this->parametres['masque.famille'])) {
328
			$familleMotif = $this->bdd->proteger($this->parametres['masque.famille']);
1871 jpm 329
			$this->addWhere('masque.famille', "do.famille = $familleMotif");
330
 
331
			if ($this->etreAppliImg()) {
332
				$this->addJoin('LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ');
333
			}
1840 jpm 334
		}
335
	}
336
 
337
	private function ajouterContrainteNs() {
338
		if (isset($this->parametres['masque.ns'])) {
339
			$ns = $this->parametres['masque.ns'];
340
			$nsMotif = $this->bdd->proteger("$ns%");
1871 jpm 341
			$this->addWhere('masque.ns', "do.nom_sel LIKE $nsMotif");
342
 
343
			if ($this->etreAppliImg()) {
344
				$this->addJoin('LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ');
345
			}
1840 jpm 346
		}
347
	}
348
 
349
	private function ajouterContrainteNn() {
350
		if (isset($this->parametres['masque.nn'])) {
1871 jpm 351
			$sqlTpl = '(do.nom_sel_nn = %1$d OR do.nom_ret_nn = %1$d)';
1840 jpm 352
			$nnWhere = sprintf($sqlTpl, $this->parametres['masque.nn']);
353
			$this->addWhere('masque.nn', $nnWhere);
1871 jpm 354
 
355
			if ($this->etreAppliImg()) {
356
				$this->addJoin('LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ');
357
			}
1840 jpm 358
		}
359
	}
360
 
361
	private function ajouterContrainteReferentiel() {
362
		if (isset($this->parametres['masque.referentiel'])) {
363
			$ref = $this->parametres['masque.referentiel'];
364
			$refMotif = $this->bdd->proteger("$ref%");
1871 jpm 365
			$this->addWhere('masque.referentiel', "do.nom_referentiel LIKE $refMotif");
366
 
367
			if ($this->etreAppliImg()) {
368
				$this->addJoin('LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ');
369
			}
1840 jpm 370
		}
371
	}
372
 
373
	private function ajouterContrainteCommune() {
374
		if (isset($this->parametres['masque.commune'])) {
375
			$commune = $this->parametres['masque.commune'];
376
			$communeMotif = $this->bdd->proteger("$commune%");
1871 jpm 377
			$this->addWhere('masque.commune', "do.zone_geo LIKE $communeMotif");
378
 
379
			if ($this->etreAppliImg()) {
380
				$this->addJoin('LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ');
381
			}
1840 jpm 382
		}
383
	}
384
 
1845 jpm 385
	public function ajouterConstrainteAppliObs() {
386
		$this->ajouterContrainteTagCel();
387
		$this->ajouterContrainteType();
388
		// TODO : ATTENTION -> vue que l'on utilise une vue basée sur les images, nous devons grouper par obs
1871 jpm 389
		$this->addGroupBy('do.id_observation');
1845 jpm 390
	}
391
 
392
	private function ajouterContrainteType() {
1933 aurelien 393
		// Les contraintes régissant les onglets sont issus de la réunion dont le compte rendu
394
		// disponible ici : http://tela-botanica.net/intranet/wakka.php?wiki=Octobre2014
395
		// Ce lien est à modifier pour pointer vers toute nouvelle réunion modifiant ce fonctionnement
396
 
1871 jpm 397
		if (isset($this->parametres['masque.type'])) {
398
			if (array_key_exists('adeterminer', $this->parametres['masque.type'])) {
1933 aurelien 399
				// A DETERMINER : toutes les observations qui ont le tag "aDeterminer"
400
				// *ou* qui n'ont pas de nom d'espèce
1871 jpm 401
				// *ou* qui ont la "certitude" à ("aDeterminer" *ou* "douteux")
402
				$this->addWhere('masque.type', '('.
403
					'do.certitude = "aDeterminer" '.
404
					'OR do.certitude = "douteux" '.
405
					'OR do.mots_cles_texte LIKE "%aDeterminer%" '.
406
					'OR do.nom_sel_nn IS NULL '.
407
					'OR do.nom_sel_nn = 0 '.// il ne DEVRAIT pas y avoir d'entrées à 0, mais il y en a quand-même !!
408
					')');
409
			}
1933 aurelien 410
 
1871 jpm 411
			if (array_key_exists('validees', $this->parametres['masque.type'])) {
1933 aurelien 412
				// VALIDEES : toutes les observations ayant un commentaire doté de proposition_retenue = 1
413
				// ou bien possédant une proposition initiale avec un nom valide ayant totalisé un score d'au moins 4
414
				// (ce qui correspond à au moins deux votes positifs dans la plupart des cas, dont un identifié)
415
				$sous_requete_score_prop_votees = $this->getSousRequeteSommeVotesPropositions();
1871 jpm 416
				$this->addJoin('INNER JOIN del_commentaire AS dc '.
1933 aurelien 417
					'ON ( '.
418
						'do.id_observation = dc.ce_observation '.
419
						'AND ( '.
420
							'dc.proposition_retenue = 1 OR '.
421
							'( '.
422
								'dc.proposition_initiale = 1 '.
423
								'AND dc.nom_sel_nn != 0 '.
424
								'AND dc.nom_sel_nn IS NOT NULL '.
425
								' AND dc.id_commentaire IN ('.$sous_requete_score_prop_votees.' >= 4) '.
426
							') '.
427
						') '.
428
					')'
429
				);
1871 jpm 430
			}
1933 aurelien 431
 
432
			if(array_key_exists('aconfirmer', $this->parametres['masque.type'])) {
433
				// A CONFIRMER : toutes les observations moins les validées et à confirmer
434
				// i.e. : des observations avec un nom valide, qui ne sont pas à déterminer
435
				// (ni certitude "aDeterminer" ou "douteuse", ni mot clé),
436
				// ne possédant pas de proposition officiellement retenue
437
				// et ayant une proposition initiale totalisant un score de moins de 4
1968 aurelien 438
				$sous_requete_score_prop_votees = $this->getSousRequeteSommeVotesPropositions();
1933 aurelien 439
				$this->addWhere('masque.type',
440
						'('.
441
							'do.id_observation IN ('.
442
								'SELECT dc.ce_observation FROM del_commentaire dc WHERE dc.proposition_retenue = 0'.
1968 aurelien 443
								' AND ( '.
444
									'dc.proposition_initiale = 1 '.
445
									'AND dc.nom_sel_nn != 0 '.
446
									'AND dc.nom_sel_nn IS NOT NULL '.
447
									'AND dc.id_commentaire IN ('.$sous_requete_score_prop_votees.' < 4) '.
1933 aurelien 448
								') '.
449
							') AND do.id_observation NOT IN ('.
450
								'SELECT dc.ce_observation FROM del_commentaire dc '.
451
								'WHERE '.
452
								' dc.proposition_retenue = 1'.
453
							') '.
1968 aurelien 454
							'AND do.certitude != "douteux" AND do.certitude != "aDeterminer" '.
455
							'AND do.mots_cles_texte NOT LIKE "%aDeterminer%" '.
456
							'AND do.nom_sel_nn != 0 '.
1933 aurelien 457
							'AND do.nom_sel_nn IS NOT NULL'.
458
						') '
459
					);
460
			}
1845 jpm 461
 
1871 jpm 462
			if ($this->etreAppliImg()) {
463
				$this->addJoin('LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ');
464
			}
1845 jpm 465
		}
466
	}
1933 aurelien 467
 
468
	private function getSousRequeteSommeVotesPropositions() {
469
		// ATTENTION : un vote identifié compte 3 votes anonymes (dans les deux sens)
1968 aurelien 470
		return  'SELECT ce_proposition '.
471
				'FROM del_commentaire_vote dcv '.
472
				'GROUP BY ce_proposition HAVING '.
473
				'SUM(CASE '.
474
				'	WHEN valeur = 1 AND dcv.ce_utilisateur REGEXP \'^-?[0-9]+$\' != 0 THEN 3 '.
475
				'	WHEN valeur = 0 AND dcv.ce_utilisateur REGEXP \'^-?[0-9]+$\' != 0 THEN -3 '.
476
				'	WHEN valeur = 1 AND dcv.ce_utilisateur REGEXP \'^-?[0-9]+$\' = 0 THEN 1 '.
477
				'	WHEN valeur = 0 AND dcv.ce_utilisateur REGEXP \'^-?[0-9]+$\' = 0 THEN -1 '.
478
				'END '.
1933 aurelien 479
			') ';
480
	}
1845 jpm 481
 
1840 jpm 482
	public function ajouterConstrainteAppliImg() {
483
		$this->ajouterContrainteMilieu();
484
		$this->ajouterContrainteTri();
485
		$this->ajouterContrainteTagCel();
486
		$this->ajouterContrainteTagDel();
487
	}
488
 
489
	private function ajouterContrainteMilieu() {
490
		if (isset($this->parametres['masque.milieu'])) {
491
			$milieu = $this->parametres['masque.milieu'];
492
			$milieuMotif = $this->bdd->proteger("%$milieu%");
1871 jpm 493
			$this->addWhere('masque.milieu', "do.milieu LIKE $milieuMotif");
494
 
495
			if ($this->etreAppliImg()) {
496
				$this->addJoin('LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ');
497
			}
1840 jpm 498
		}
499
	}
500
 
501
	private function ajouterContrainteTri() {
502
		if (isset($this->parametres['tri'])) {
503
			$tri = $this->parametres['tri'];
504
 
1863 jpm 505
			if (isset($this->parametres['protocole'])  && ($tri == 'moyenne-arithmetique' || $tri == 'points')) {
1840 jpm 506
				// $this->parametres['protocole'] *est* défini (cf Outils::filtrerUrlsParams...())
1871 jpm 507
				$sqlTpl = 'LEFT JOIN del_image_stat AS dis ON di.id_image = dis.ce_image AND dis.ce_protocole = %d';
1840 jpm 508
				$triSql = sprintf($sqlTpl, $this->parametres['protocole']);
509
				$this->addJoinDis($triSql);
510
			}
511
 
512
			if (isset($this->parametres['ordre']) && $tri == 'tags') {
513
				$typeJointure = ($this->parametres['ordre'] == 'desc') ? 'INNER' : 'LEFT';
1871 jpm 514
				$this->addJoin("$typeJointure JOIN del_image_stat AS dis ON di.id_image = dis.ce_image");
1840 jpm 515
			}
516
		}
517
	}
518
 
519
	private function ajouterContrainteTagCel() {
1845 jpm 520
		if (isset($this->parametres['masque.tag_cel'])) {
1840 jpm 521
			if (isset($this->parametres['masque.tag_cel']['AND'])) {
522
				$tags = $this->parametres['masque.tag_cel']['AND'];
523
				$clausesWhere = array();
524
				foreach ($tags as $tag) {
525
					$tagMotif = $this->bdd->proteger("%$tag%");
1922 jpm 526
					if ($this->etreAppliImg()) {
1980 aurelien 527
						$sousRequete = 'SELECT id_observation '.
1922 jpm 528
							'FROM del_observation '.
529
							"WHERE mots_cles_texte LIKE $tagMotif ";
530
						$sql = " (di.mots_cles_texte LIKE $tagMotif OR di.id_image IN ($sousRequete) ) ";
531
					} else {
532
						// WARNING : la sous-requête est la meilleure solution trouvée pour contrer le fonctionnement
533
						// étrange de l'optimiseur de MYSQL 5.6 (à retester avec Mysql 5.7 et suivant).
534
						$sousRequete = 'SELECT DISTINCT ce_observation '.
535
							'FROM del_image '.
536
							"WHERE mots_cles_texte LIKE $tagMotif ".
537
							'AND ce_observation IS NOT NULL';
538
						$sql = " (do.mots_cles_texte LIKE $tagMotif OR do.id_observation IN ($sousRequete)) ";
539
					}
540
					$clausesWhere[] = $sql;
1840 jpm 541
				}
542
				$whereTags = implode(' AND ', $clausesWhere);
543
				$this->addWhere('masque.tag_cel', "($whereTags)");
544
			} else if (isset($this->parametres['masque.tag_cel']['OR'])) {
545
				$tags = $this->parametres['masque.tag_cel']['OR'];
1922 jpm 546
				$tagMotif = $this->bdd->proteger(implode('|', $tags));
1871 jpm 547
				$sqlTpl = "CONCAT(IFNULL(do.mots_cles_texte,''),IFNULL(di.mots_cles_texte,'')) REGEXP %s";
1840 jpm 548
				$tagSql = sprintf($sqlTpl, $tagMotif);
1922 jpm 549
 
1840 jpm 550
				$this->addWhere('masque.tag_cel', $tagSql);
1922 jpm 551
 
552
				if ($this->etreAppliObs()) {
553
					$this->addJoin('LEFT JOIN del_image AS di ON (di.ce_observation = do.id_observation) ');
554
				}
1840 jpm 555
			}
1871 jpm 556
			if ($this->etreAppliImg()) {
557
				$this->addJoin('LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ');
558
			}
1840 jpm 559
		}
560
	}
561
 
562
	/**
1881 jpm 563
	 * Plusieurs solutions sont disponibles dans les anciennes versions (voir DelTk et l'historique SVN de ce fichier).
1840 jpm 564
	 */
565
	private function ajouterContrainteTagDel() {
566
		if (isset($this->parametres['masque.tag_del'])) {
1922 jpm 567
			$tagsMotif = $this->construireTagsMotif();
568
			if (is_null($tagsMotif) === false) {
569
				$sousRequete = 'SELECT ce_image '.
1845 jpm 570
					'FROM del_image_tag '.
571
					'WHERE actif = 1 '.
572
					'GROUP BY ce_image '.
1922 jpm 573
					"HAVING GROUP_CONCAT(DISTINCT tag_normalise ORDER BY tag_normalise) REGEXP $tagsMotif ";
1840 jpm 574
 
1922 jpm 575
				$this->addWhere('masque.tag_del', "di.id_image IN ($sousRequete)");
1840 jpm 576
			}
577
		}
578
	}
579
 
1922 jpm 580
	private function construireTagsMotif() {
581
		$tagsMotif = null;
582
		if (isset($this->parametres['masque.tag_del']['AND'])) {
583
			$tags = $this->parametres['masque.tag_del']['AND'];
584
			// ATTENTION -> optimisation: en cas de "AND" on sort() l'input et le GROUP_CONCAT()
585
			// donc nous utilisons des ".*" plutôt que de multiples conditions et "|"
586
			sort($tags);
587
			$tagsMotif = $this->bdd->proteger(implode('.*', $tags));
588
		} else if (isset($this->parametres['masque.tag_del']['OR'])) {
589
			$tags = $this->parametres['masque.tag_del']['OR'];
590
			$tagsMotif = $this->bdd->proteger(implode('|', $tags));
1840 jpm 591
		}
1922 jpm 592
		return $tagsMotif;
1840 jpm 593
	}
594
 
595
	/**
596
	 * Partie spécifique à PictoFlora:
1863 jpm 597
	 * Attention : si le critère de tri n'est pas suffisant, les résultats affichés peuvent varier à chaque appel
598
	 * de la même page de résultat de PictoFlora.
1840 jpm 599
	 */
600
	public function definirOrdreSqlAppliImg() {
601
		$ordre = $this->parametres['ordre'];
1968 aurelien 602
 
603
		$tri = isset($this->parametres['tri']) ? $this->parametres['tri'] : '';
604
		switch ($tri) {
1863 jpm 605
			case 'moyenne-arithmetique' :
1888 jpm 606
				$this->addOrderBy("dis.moyenne $ordre, dis.nb_votes $ordre, id_image $ordre");
1840 jpm 607
				break;
608
			case 'points' :
1888 jpm 609
				$this->addOrderBy("dis.nb_points $ordre, dis.moyenne $ordre, dis.nb_votes $ordre, id_image $ordre");
1840 jpm 610
				break;
611
			case 'tags' :
1888 jpm 612
				$this->addOrderBy("dis.nb_tags $ordre, id_image $ordre");
1840 jpm 613
				break;
614
			case 'date_observation' :
1871 jpm 615
				$this->addOrderBy("date_observation $ordre, ce_observation $ordre");
1840 jpm 616
				break;
1863 jpm 617
			case 'date_transmission' :
1840 jpm 618
			default:
1871 jpm 619
				$this->addOrderBy("di.date_transmission $ordre, ce_observation $ordre");
1840 jpm 620
		}
621
	}
622
 
1845 jpm 623
	public function definirOrdreSqlAppliObs() {
624
		$ordre = $this->parametres['ordre'];
625
 
626
		// parmi self::$tri_possible
1968 aurelien 627
		$tri = isset($this->parametres['tri']) ? $this->parametres['tri'] : '';
628
			switch ($tri) {
1845 jpm 629
			case 'date_observation' :
630
				$this->addOrderBy("date_observation $ordre, id_observation $ordre");
1933 aurelien 631
				break;
1968 aurelien 632
			case 'nb_commentaires' :
633
				$sql_nb_comms = '(SELECT COUNT(id_commentaire) FROM del_commentaire AS dc WHERE ce_observation = id_observation)';
634
				$this->addOrderBy("$sql_nb_comms $ordre, id_observation $ordre");
1845 jpm 635
				break;
1933 aurelien 636
			case 'date_transmission' :
1845 jpm 637
			default:
1871 jpm 638
				$this->addOrderBy("do.date_transmission $ordre, id_observation $ordre");
1845 jpm 639
		}
640
	}
641
 
1840 jpm 642
	public function getAliasDesChamps($champsEtAlias, $select = null, $prefix = null) {
643
		$arr = ($select) ? array_intersect_key($champsEtAlias, array_flip($select)) :  $champsEtAlias;
644
		$keys = array_keys($arr);
645
 
646
		if ($prefix) {
647
			array_walk($keys, create_function('&$val, $k, $prefix', '$val = sprintf("%s.`%s`", $prefix, $val);'), $prefix);
648
		} else {
649
			array_walk($keys, create_function('&$val, $k', '$val = sprintf("`%s`", $val);'));
650
		}
651
 
652
		return implode(', ', array_map(create_function('$v, $k', 'return sprintf("%s AS `%s`", $k, $v);'), $arr, $keys));
653
	}
654
 
655
	public function getVotesDesImages($idsImages, $protocole = null) {
656
		if (!$idsImages) return;
657
 
658
		$mappingVotes = $this->conteneur->getParametreTableau('votes.mapping');
659
		$mappingProtocoles = $this->conteneur->getParametreTableau('protocoles.mapping');
660
 		$selectVotes = array('id_vote', 'ce_image', 'ce_protocole', 'ce_utilisateur', 'valeur', 'date');
661
		$selectProtocole = array('id_protocole', 'intitule', 'descriptif', 'tag');
662
		$voteChamps = $this->getAliasDesChamps($mappingVotes, $selectVotes, 'v'); // "v": cf alias dans la requête
663
		$protoChamps = $this->getAliasDesChamps($mappingProtocoles, $selectProtocole, 'p');
664
		$idImgsConcat = implode(',', $idsImages);
665
 
666
		$requete = "SELECT $voteChamps, $protoChamps ".
667
			'FROM del_image_vote AS v '.
668
			'	INNER JOIN del_image_protocole AS p ON (v.ce_protocole = p.id_protocole) '.
669
			"WHERE v.ce_image IN ($idImgsConcat) ".
670
			($protocole ? "	AND v.ce_protocole = $protocole " : '').
671
			"ORDER BY FIELD(v.ce_image, $idImgsConcat) ".
672
			'-- '.__FILE__.':'.__LINE__;
673
		return $this->bdd->recupererTous($requete);
674
	}
675
 
676
	/**
677
	 * Ajoute les informations sur le protocole et les votes aux images.
678
	 *
679
	 * ATTENTION : Subtilité, nous passons ici le tableau d'images indexé par id_image qui est bien
680
	 * plus pratique pour associer les vote à un tableau, puisque nous ne connaissons pas les id d'observation.
681
	 * Mais magiquement (par référence), cela va remplir notre tableau indexé par couple d'id (id_image, id_observation)
682
	 * cf ListeImages::reformateImagesDoubleIndex() à qui revient la tâche de créer ces deux versions
683
	 * simultanément lorsque c'est encore possible.
684
	 */
1881 jpm 685
	// TODO : supprimer cette "subtilité" source d'erreurs
1840 jpm 686
	public function ajouterInfosVotesProtocoles($votes, &$images) {
687
		if (!$votes) return;
1845 jpm 688
 
1840 jpm 689
		$mappingVotes = $this->conteneur->getParametreTableau('votes.mapping');
690
		$mappingProtocoles = $this->conteneur->getParametreTableau('protocoles.mapping');
691
 
692
		// pour chaque vote
693
		foreach ($votes as $vote) {
694
			$imgId = $vote['image.id'];
695
			$protoId = $vote['protocole.id'];
696
 
697
			if (!array_key_exists('protocoles_votes', $images[$imgId]) ||
1863 jpm 698
					!array_key_exists($protoId, $images[$imgId]['protocoles_votes'])) {
1840 jpm 699
				// extrait les champs spécifique au protocole (le LEFT JOIN de chargerVotesImage les ramène en doublons
700
				$protocole = array_intersect_key($vote, array_flip($mappingProtocoles));
701
				$images[$imgId]['protocoles_votes'][$protoId] = $protocole;
702
			}
703
 
704
			$chpsVotes = array('id_vote', 'ce_image', 'ce_utilisateur', 'valeur', 'date');
705
			$voteSelection = array_intersect_key($mappingVotes, array_flip($chpsVotes));
706
			$vote = array_intersect_key($vote, array_flip($voteSelection));
707
			$images[$imgId]['protocoles_votes'][$protoId]['votes'][$vote['vote.id']] = $vote;
708
		}
709
	}
1845 jpm 710
 
711
	public function getTotalLignesTrouvees() {
1888 jpm 712
		$resultat = $this->bdd->recuperer('SELECT FOUND_ROWS() AS nbre -- '.__FILE__.':'.__LINE__);
1845 jpm 713
		return intval($resultat['nbre']);
714
	}
1840 jpm 715
}