Subversion Repositories eFlore/Applications.del

Rev

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