Subversion Repositories eFlore/Applications.del

<?php

// declare(encoding='UTF-8');

/**

 * Classe contenant des méthodes permettant de construire les requêtes SQL complexe concernant les images et obs.

 * Rempli un tableau des clauses "join", "where", "group by" et "oder by" nécessaire à la *recherche* des ids des

 * observations/images correspondantes aux filtres passés dans l'url du web service de recherche

 * (ListeImages et ListeObservations).

 *

 * Attention, cela signifie que toutes les tables ne sont pas *forcément* jointées, par exemple si aucune

 * contrainte ne le nécessite.

 * La requête construite ici est utile pour récupérer la liste des ids d'observations/images qui match.

 * Pour la récupération effective de "toutes" les données nécessaire au retour du web service en json, c'est une autre

 * requête directement dans le web service qui s'en charge. Cette technique en deux étapes est la plus rapide !

 *

 * Note: toujours rajouter les préfixes de table (di, do ou du), en fonction de ce que défini

 * les JOIN qui sont utilisés :

 * - le préfixe de del_image est "di"

 * - le préfixe de del_observation est "do"

 * - le préfixe de del_utilisateur est "du"

 *

 * @category  DEL

 * @package   Services

 * @package   Bibliotheque

 * @version   0.1

 * @author    Mathias CHOUET <mathias@tela-botanica.org>

 * @author    Jean-Pascal MILCENT <jpm@tela-botanica.org>

 * @author    Aurelien PERONNET <aurelien@tela-botanica.org>

 * @license   GPL v3 <http://www.gnu.org/licenses/gpl.txt>

 * @license   CECILL v2 <http://www.cecill.info/licences/Licence_CeCILL_V2-en.txt>

 * @copyright 1999-2014 Tela Botanica <accueil@tela-botanica.org>

 */

class Sql {

	const APPLI_IMG = 'IMG';

	const APPLI_OBS = 'OBS';

	private $conteneur;

	private $bdd;

	private $parametres = array();

	private $appli;

	private $requete = array(

		'join' => array(),

		'where' => array(),

		'groupby' => array(),

		'orderby' => array());

	private $champsPrenom = array('prenom_utilisateur');

	private $champsNom = array('nom_utilisateur');

	private $champsSousRequeteObs = array('masque.standard','masque.genre', 'masque.famille', 'masque.ns', 'masque.commune', 'masque.milieu', 'masque.pays');

	public function __construct(Conteneur $conteneur) {

		$this->conteneur = $conteneur;

		$this->bdd = $this->conteneur->getBdd();

	}

	public function setParametres(Array $parametres) {

		$this->parametres = $parametres;

	}

	public function setAppli($appliType) {

		if ($appliType == 'IMG' || $appliType == 'OBS') {

			$this->appli = $appliType;

		} else {

			throw new Exception("Les types d'appli disponible sont : IMG (pour PictoFlora) et OBS (pour IdentiPlante)");

		}

	}

	private function getPrefixe() {

		return $this->appli === 'IMG' ? 'di' : 'do';

	}

	private function etreAppliImg() {

		return $this->appli === 'IMG';

	}

	private function etreAppliObs() {

		return $this->appli === 'OBS';

	}

	public function getRequeteSql() {

		return $this->requete;

	}

	private function addJoin($join) {

		if (!isset($this->requete['join'][$join])) {

			$this->requete['join'][$join] = $join;

		}

	}

	public function getJoin() {

		return ($this->requete['join'] ? implode(' ', array_unique($this->requete['join'])).' ' : '');

	}

	private function addJoinDis($join) {

		$this->requete['join']['dis'] = $join;

	}

	private function addWhere($idParam, $where) {

		if (isset($this->parametres['_parametres_condition_or_'])

				&& in_array($idParam, $this->parametres['_parametres_condition_or_'])) {

			if ($this->etreAppliImg() && in_array($idParam, $this->champsSousRequeteObs)) {

				$this->requete['where']['OR_SOUS_REQUETE'][] = $where;

			} else {

				$this->requete['where']['OR'][] = $where;

			}

		} else {

			$this->requete['where']['AND'][] = $where;

		}

	}

	public function getWhere() {

		// Sous-requete spéciale pour éviter de rechercher dans la table obs jointe à img depuis Pictoflora...

		//print_r($this->requete);

		if (isset($this->requete['where']['OR_SOUS_REQUETE']) && count($this->requete['where']['OR_SOUS_REQUETE']) > 0) {

			$clauseWhereSousRequete = implode(' OR ', $this->requete['where']['OR_SOUS_REQUETE']);

			$sousRequete = 'di.ce_observation IN '.

				"(SELECT id_observation FROM del_observation AS do WHERE $clauseWhereSousRequete ) ";

			$this->requete['where']['OR'][] = "( $sousRequete )";

			unset($this->requete['join'][$this->getSqlJointureObs()]);

		}

		if (isset($this->requete['where']['OR']) && count($this->requete['where']['OR']) > 0) {

			$this->requete['where']['AND'][] = '('.implode(' OR ', $this->requete['where']['OR']).')';

		}

		$where = ' TRUE ';

		if (isset($this->requete['where']['AND']) && count($this->requete['where']['AND']) > 0) {

			$where = implode(' AND ', $this->requete['where']['AND']).' ';

		}

		return $where;

	}

	private function addGroupBy($groupBy) {

		$this->requete['groupby'][] = $groupBy;

	}

	public function getGroupBy() {

		$groupby = '';

		if (isset($this->requete['groupby']) && count($this->requete['groupby']) > 0) {

			$groupby = 'GROUP BY '.implode(', ', array_unique($this->requete['groupby'])).' ';

		}

		return $groupby;

	}

	private function addOrderBy($orderby) {

		$this->requete['orderby'][] = $orderby;

	}

	public function getOrderBy() {

		$orderby = '';

		if (isset($this->requete['orderby']) && count($this->requete['orderby']) > 0) {

			$orderby = 'ORDER BY '.implode(', ', array_unique($this->requete['orderby'])).' ';

		}

		return $orderby;

	}

	public function getLimit() {

		return 'LIMIT '.$this->parametres['navigation.depart'].','.$this->parametres['navigation.limite'].' ';

	}

	/**

	 * @param $p les paramètres (notamment de masque) passés par l'URL et déjà traités/filtrés (sauf quotes)

	 * @param $req le tableau, passé par référence représentant les composants de la requête à bâtir

	 */

	public function ajouterContraintes() {

		$this->ajouterContrainteStandard();

		$this->ajouterContrainteAuteur();

		$this->ajouterContrainteDate();

		$this->ajouterContraintePays();

		$this->ajouterContrainteDepartement();

		$this->ajouterContrainteIdZoneGeo();

		$this->ajouterContrainteGenre();

		$this->ajouterContrainteFamille();

		$this->ajouterContrainteNs();

		$this->ajouterContrainteNn();

		$this->ajouterContrainteReferentiel();

		$this->ajouterContrainteCommune();

		$this->ajouterContraintePnInscrits();

	}

	private function ajouterContrainteStandard() {

		if (isset($this->parametres['masque.standard'])) {

			$donnees_standard = $this->parametres['masque.standard'];

			$this->addWhere('masque.standard', "do.donnees_standard = $donnees_standard");

			$this->ajouterJoinObsSiNecessaire();

		}

	}

	private function ajouterContrainteAuteur() {

		if (isset($this->parametres['masque.auteur'])) {

			$auteur = $this->parametres['masque.auteur'];

			// id du poster de l'obs

			$prefixe = $this->getPrefixe();

			if (is_numeric($auteur)) {

				$this->ajouterContrainteAuteurId();

			} elseif(preg_match('/@[a-z0-9-]+(?:\.[a-z0-9-]+)*\.[a-z]{2,}$/i', $auteur)) {

				$this->ajouterContrainteAuteurEmail();

			} else {

				$this->ajouterContrainteAuteurIntitule();

			}

		}

	}

	private function ajouterContrainteAuteurId() {

		$id = $this->parametres['masque.auteur'];

		$prefixe = $this->getPrefixe();

		$sqlTpl = "($prefixe.ce_utilisateur = %1\$d)";

		$whereAuteur = sprintf($sqlTpl, $id);

		$this->addWhere('masque.auteur', $whereAuteur);

	}

	private function ajouterContrainteAuteurEmail() {

		$email = $this->parametres['masque.auteur'];

		$prefixe = $this->getPrefixe();

		$sqlTpl = "($prefixe.courriel_utilisateur LIKE %1\$s )";

		$emailP = $this->bdd->proteger("$email%");

		$whereAuteur = sprintf($sqlTpl, $emailP);

		$this->addWhere('masque.auteur', $whereAuteur);

	}

	/**

	 * Retourne une clause where du style:

	 * CONCAT(IF(du.prenom IS NULL, "", du.prenom), [...] vdi.i_nomutilisateur) REGEXP 'xxx'

	 */

	private function ajouterContrainteAuteurIntitule() {

		$auteurExplode = explode(' ', $this->parametres['masque.auteur']);

		$nbreMots = count($auteurExplode);

		if ($nbreMots == 1) {

			$this->ajouterContrainteAuteurPrenomOuNom();

		} else if ($nbreMots == 2) {

			$this->ajouterContrainteAuteurPrenomEtNom();

		}

	}

	private function ajouterContrainteAuteurPrenomOuNom() {

		$prenomOuNom = $this->parametres['masque.auteur'];

		$sqlTpl = 'CONCAT(%s,%s) LIKE %s';

		$prefixe = $this->getPrefixe();

		$champsPrenomSql = self::ajouterIfNullPourConcat($this->champsPrenom, $prefixe);

		$champsNomSql = self::ajouterIfNullPourConcat($this->champsNom, $prefixe);

		$auteurMotif = $this->bdd->proteger("%$prenomOuNom%");

		$auteurWhere = sprintf($sqlTpl, $champsPrenomSql, $champsNomSql, $auteurMotif);

		$this->addWhere('masque.auteur', $auteurWhere);

	}

	private function ajouterContrainteAuteurPrenomEtNom() {

		list($prenom, $nom) = explode(' ', $this->parametres['masque.auteur']);

		$sqlTpl = '(CONCAT(%1$s,%2$s) LIKE %3$s AND CONCAT(%1$s,%2$s) LIKE %4$s)';

		$prefixe = $this->getPrefixe();

		$champsPrenomSql = self::ajouterIfNullPourConcat($this->champsPrenom, $prefixe);

		$champsNomSql = self::ajouterIfNullPourConcat($this->champsNom, $prefixe);

		$prenomMotif = $this->bdd->proteger("%$prenom%");

		$nomMotif = $this->bdd->proteger("%$nom%");

		$auteurWhere = sprintf($sqlTpl, $champsPrenomSql, $champsNomSql, $prenomMotif, $nomMotif);

		$this->addWhere('masque.auteur', $auteurWhere);

	}

	/**

	 * Lorsque l'on concatène des champs, un seul NULL prend le dessus.

	 * Il faut donc utiliser la syntaxe IFNULL(%s, "").

	 * Cette fonction effectue aussi l'implode() "final".

	 */

	private static function ajouterIfNullPourConcat($champs, $prefixe) {

		$champsProteges = array();

		foreach ($champs as $champ) {

			if (strstr($champ, '.') === false) {

				$champ = "$prefixe.$champ";

			}

			$champsProteges[] = "IFNULL($champ, '')";

		}

		return implode(',', $champsProteges);

	}

	private function ajouterContrainteDate() {

		if (isset($this->parametres['masque.date'])) {

			$date = $this->parametres['masque.date'];

			if (preg_match('/^\d{4}$/', $date) && $date < 2030 && $date > 1600) {

				$sqlTpl = "YEAR(do.date_observation) = %d";

				$dateWhere = sprintf($sqlTpl, $date);

				$this->addWhere('masque.date', $dateWhere);

			} else {

				$sqlTpl = "do.date_observation = %s";

				$dateP = $this->bdd->proteger($date);

				$dateWhere = sprintf($sqlTpl, $dateP);

				$this->addWhere('masque.date', $dateWhere);

			}

			$this->ajouterJoinObsSiNecessaire();

		}

	}

	private function ajouterContrainteDepartement() {

		if (isset($this->parametres['masque.departement'])) {

			$dept = $this->parametres['masque.departement'];

			$deptMotif = $this->bdd->proteger("$dept");

			$this->addWhere('masque.departement', "do.ce_zone_geo like $deptMotif");

			$this->ajouterJoinObsSiNecessaire();

		}

	}

	private function ajouterContraintePays() {

		if (isset($this->parametres['masque.pays'])) {

			// Attention le standard contient parfois FX pour la france métropolitaine

			// Dans ce cas particulier on cherche donc FR et FX

			$this->parametres['masque.pays'] = strtoupper($this->parametres['masque.pays']);

			if(strpos($this->parametres['masque.pays'], 'FR') !== false) {

				$this->parametres['masque.pays'] = str_replace('FR', 'FR,FX', $this->parametres['masque.pays']);

			}

			$pays = explode(',', $this->parametres['masque.pays']);

			$pays = implode(',', $this->bdd->proteger($pays));

			$this->addWhere('masque.pays', "do.pays IN ($pays)");

			$this->ajouterJoinObsSiNecessaire();

		}

	}

	private function ajouterContrainteIdZoneGeo() {

		if (isset($this->parametres['masque.id_zone_geo'])) {

			$idZgMotif = $this->bdd->proteger($this->parametres['masque.id_zone_geo']);

			$this->addWhere('masque.id_zone_geo', "do.ce_zone_geo = $idZgMotif");

			$this->ajouterJoinObsSiNecessaire();

		}

	}

	private function ajouterContrainteGenre() {

		if (isset($this->parametres['masque.genre'])) {

			$genre = $this->parametres['masque.genre'];

			$genreMotif = $this->bdd->proteger("$genre%");

			$this->addWhere('masque.genre', "do.nom_sel LIKE $genreMotif");

			$this->ajouterJoinObsSiNecessaire();

		}

	}

	private function ajouterContrainteFamille() {

		if (isset($this->parametres['masque.famille'])) {

			$familleMotif = $this->bdd->proteger($this->parametres['masque.famille']);

			$this->addWhere('masque.famille', "do.famille = $familleMotif");

			$this->ajouterJoinObsSiNecessaire();

		}

	}

	private function ajouterContrainteNs() {

		if (isset($this->parametres['masque.ns'])) {

			$ns = $this->parametres['masque.ns'];

			$nsMotif = $this->bdd->proteger("$ns%");

			$this->addWhere('masque.ns', "do.nom_sel LIKE $nsMotif");

			$this->ajouterJoinObsSiNecessaire();

		}

	}

	private function ajouterContrainteNn() {

		if (isset($this->parametres['masque.nn'])) {

			$sqlTpl = '(do.nom_sel_nn = %1$d OR do.nom_ret_nn = %1$d)';

			$nnWhere = sprintf($sqlTpl, $this->parametres['masque.nn']);

			$this->addWhere('masque.nn', $nnWhere);

			$this->ajouterJoinObsSiNecessaire();

		}

	}

	private function ajouterContrainteReferentiel() {

		if (isset($this->parametres['masque.referentiel'])) {

			$ref = $this->parametres['masque.referentiel'];

			$refMotif = $this->bdd->proteger("$ref");

			$this->addWhere('masque.referentiel', "do.nom_referentiel = $refMotif");

			$this->ajouterJoinObsSiNecessaire();

		}

	}

	private function ajouterContrainteCommune() {

		if (isset($this->parametres['masque.commune'])) {

			$commune = $this->parametres['masque.commune'];

			$communeMotif = $this->bdd->proteger("$commune%");

			$this->addWhere('masque.commune', "do.zone_geo LIKE $communeMotif");

			$this->ajouterJoinObsSiNecessaire();

		}

	}

	/**

	 * Si masque.pninscritsseulement vaut true, les observations ayant un tag

	 * "plantnet" mais dont l'auteur n'est pas inscrit à TB seront éliminées

	 * (décision FlorisTic 2016-09)

	 */

	protected function ajouterContraintePnInscrits() {

		if (isset($this->parametres['masque.pninscritsseulement'])) {

			// avec la classe ParametresFiltrage, on ne passe là que si le masque vaut 1

			$motifMotClePlantnet = "'plantnet'";

			$this->addWhere('masque.pninscritsseulement', "((do.input_source != $motifMotClePlantnet) OR

			    (do.input_source = $motifMotClePlantnet AND do.ce_utilisateur != 0))");

			$this->ajouterJoinObsSiNecessaire();

		}

	}

	private function ajouterJoinObsSiNecessaire() {

			if ($this->etreAppliImg()) {

				$this->addJoin($this->getSqlJointureObs());

			}

	}

	private function getSqlJointureObs() {

		$typeJointure = !empty($this->parametres['masque']) ? 'LEFT' : 'INNER';

		return $typeJointure.' JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ';

	}

	// la constrainte de nostre bon roy

	public function ajouterConstrainteAppliObs() {

		$this->ajouterContrainteTagCel();

		$this->ajouterContrainteType();

		// TODO : ATTENTION -> vu que l'on utilise une vue basée sur les images, nous devons grouper par obs

		$this->addGroupBy('do.id_observation');

	}

	private function ajouterContrainteType() {

		// Les contraintes régissant les onglets sont issus de la réunion dont le compte rendu

		// disponible ici : http://tela-botanica.net/intranet/wakka.php?wiki=Octobre2014

		// Ce lien est à modifier pour pointer vers toute nouvelle réunion modifiant ce fonctionnement

		if (isset($this->parametres['masque.type'])) {

			if (array_key_exists('adeterminer', $this->parametres['masque.type'])) {

				// A DETERMINER : toutes les observations qui ont le tag "aDeterminer"

				// *ou* qui n'ont pas de nom d'espèce

				// *ou* qui ont la "certitude" à ("aDeterminer" *ou* "douteux")

				$this->addWhere('masque.type', '('.

					'do.certitude = "à déterminer" '.

					'OR do.certitude = "douteux" '.

					'OR do.mots_cles_texte LIKE "%aDeterminer%" '.

					'OR do.nom_sel_nn IS NULL '.

					'OR do.nom_sel_nn = 0 '.// il ne DEVRAIT pas y avoir d'entrées à 0, mais il y en a quand-même !!

					')');

			}

			if (array_key_exists('validees', $this->parametres['masque.type'])) {

				// VALIDEES : toutes les observations ayant un commentaire doté de proposition_retenue = 1

				// ou bien possédant une proposition initiale avec un nom valide ayant totalisé un score d'au moins 4

				// (ce qui correspond à au moins deux votes positifs dans la plupart des cas, dont un identifié)

				$sous_requete_score_prop_votees = $this->getSousRequeteSommeVotesPropositions();

				$this->addJoin('INNER JOIN del_commentaire AS dc '.

					'ON ( '.

						'do.id_observation = dc.ce_observation '.

						'AND ( '.

							'dc.proposition_retenue = 1 OR '.

							'( '.

								'dc.proposition_initiale = 1 '.

								'AND dc.nom_sel_nn != 0 '.

								'AND dc.nom_sel_nn IS NOT NULL '.

								' AND dc.id_commentaire IN ('.$sous_requete_score_prop_votees.' >= 4) '.

							') '.

						') '.

					')'

				);

			}

			if(array_key_exists('aconfirmer', $this->parametres['masque.type'])) {

				// A CONFIRMER : toutes les observations moins les validées et à confirmer

				// i.e. : des observations avec un nom valide, qui ne sont pas à déterminer

				// (ni certitude "aDeterminer" ou "douteuse", ni mot clé),

				// ne possédant pas de proposition officiellement retenue

				// et ayant une proposition initiale totalisant un score de moins de 4

				$sous_requete_score_prop_votees = $this->getSousRequeteSommeVotesPropositions();

				$this->addWhere('masque.type',

						'('.

							'do.id_observation IN ('.

								'SELECT dc.ce_observation FROM del_commentaire dc WHERE dc.proposition_retenue = 0'.

								' AND ( '.

									'dc.proposition_initiale = 1 '.

									'AND dc.nom_sel_nn != 0 '.

									'AND dc.nom_sel_nn IS NOT NULL '.

									'AND dc.id_commentaire IN ('.$sous_requete_score_prop_votees.' < 4) '.

								') '.

							') AND do.id_observation NOT IN ('.

								'SELECT dc.ce_observation FROM del_commentaire dc '.

								'WHERE '.

								' dc.proposition_retenue = 1'.

							') '.

							'AND do.certitude != "douteux" AND do.certitude != "aDeterminer" '.

							'AND do.mots_cles_texte NOT LIKE "%aDeterminer%" '.

							'AND do.nom_sel_nn != 0 '.

							'AND do.nom_sel_nn IS NOT NULL'.

						') '

					);

			}

			$this->ajouterJoinObsSiNecessaire();

		}

	}

	private function getSousRequeteSommeVotesPropositions() {

		// ATTENTION : un vote identifié compte 3 votes anonymes (dans les deux sens)

		return  'SELECT ce_proposition '.

				'FROM del_commentaire_vote dcv '.

				'GROUP BY ce_proposition HAVING '.

				'SUM(CASE '.

				'	WHEN valeur = 1 AND dcv.ce_utilisateur REGEXP \'^-?[0-9]+$\' != 0 THEN 3 '.

				'	WHEN valeur = 0 AND dcv.ce_utilisateur REGEXP \'^-?[0-9]+$\' != 0 THEN -3 '.

				'	WHEN valeur = 1 AND dcv.ce_utilisateur REGEXP \'^-?[0-9]+$\' = 0 THEN 1 '.

				'	WHEN valeur = 0 AND dcv.ce_utilisateur REGEXP \'^-?[0-9]+$\' = 0 THEN -1 '.

				'END '.

			') ';

	}

	public function ajouterConstrainteAppliImg() {

		$this->ajouterContrainteMilieu();

		$this->ajouterContrainteTri();

		$this->ajouterContrainteTagCel();

		$this->ajouterContrainteTagDel();

		$this->ajouterContraintePnInscrits();

	}

	private function ajouterContrainteMilieu() {

		if (isset($this->parametres['masque.milieu'])) {

			$milieu = $this->parametres['masque.milieu'];

			$milieuMotif = $this->bdd->proteger("%$milieu%");

			$this->addWhere('masque.milieu', "do.milieu LIKE $milieuMotif");

			$this->ajouterJoinObsSiNecessaire();

		}

	}

	private function ajouterContrainteTri() {

		if (isset($this->parametres['tri'])) {

			$tri = $this->parametres['tri'];

			if (isset($this->parametres['protocole'])  && ($tri == 'moyenne-arithmetique' || $tri == 'points')) {

				// $this->parametres['protocole'] *est* défini (cf Outils::filtrerUrlsParams...())

				$sqlTpl = 'LEFT JOIN del_image_stat AS dis ON di.id_image = dis.ce_image AND dis.ce_protocole = %d';

				$triSql = sprintf($sqlTpl, $this->parametres['protocole']);

				$this->addJoinDis($triSql);

			}

			if (isset($this->parametres['ordre']) && $tri == 'tags') {

				$typeJointure = ($this->parametres['ordre'] == 'desc') ? 'INNER' : 'LEFT';

				$this->addJoin("$typeJointure JOIN del_image_stat AS dis ON di.id_image = dis.ce_image");

			}

		}

	}

	private function ajouterContrainteTagCel() {

		if (isset($this->parametres['masque.tag_cel'])) {

			if (isset($this->parametres['masque.tag_cel']['AND'])) {

				$tags = $this->parametres['masque.tag_cel']['AND'];

				$clausesWhere = array();

				foreach ($tags as $tag) {

					$tagMotif = $this->bdd->proteger("%$tag%");

					if ($this->etreAppliImg()) {

						$sousRequete = 'SELECT id_observation '.

							'FROM del_observation '.

							"WHERE mots_cles_texte LIKE $tagMotif ";

						$sql = " (di.mots_cles_texte LIKE $tagMotif OR di.id_image IN ($sousRequete) ) ";

					} else {

						// WARNING : la sous-requête est la meilleure solution trouvée pour contrer le fonctionnement

						// étrange de l'optimiseur de MYSQL 5.6 (à retester avec Mysql 5.7 et suivant).

						$sousRequete = 'SELECT DISTINCT ce_observation '.

							'FROM del_image '.

							"WHERE mots_cles_texte LIKE $tagMotif ".

							'AND ce_observation IS NOT NULL';

						$sql = " (do.mots_cles_texte LIKE $tagMotif OR do.id_observation IN ($sousRequete)) ";

					}

					$clausesWhere[] = $sql;

				}

				$whereTags = implode(' AND ', $clausesWhere);

				$this->addWhere('masque.tag_cel', "($whereTags)");

			} else if (isset($this->parametres['masque.tag_cel']['OR'])) {

				$tags = $this->parametres['masque.tag_cel']['OR'];

				$tagMotif = $this->bdd->proteger(implode('|', $tags));

				$sqlTpl = "CONCAT(IFNULL(do.mots_cles_texte,''),IFNULL(di.mots_cles_texte,'')) REGEXP %s";

				$tagSql = sprintf($sqlTpl, $tagMotif);

				$this->addWhere('masque.tag_cel', $tagSql);

				if ($this->etreAppliObs()) {

					$this->addJoin('LEFT JOIN del_image AS di ON (di.ce_observation = do.id_observation) ');

				}

			}

			$this->ajouterJoinObsSiNecessaire();

		}

	}

	/**

	 * Plusieurs solutions sont disponibles dans les anciennes versions (voir DelTk et l'historique SVN de ce fichier).

	 */

	private function ajouterContrainteTagDel() {

		if (isset($this->parametres['masque.tag_del'])) {

			$nbTags = $this->getNombreDeTags();

			if($nbTags > 1) {

				// sous-requêtes car le GROUP BY avec GROUP_CONCAT est *trop* lent

				if (isset($this->parametres['masque.tag_del']['AND'])) {

					foreach ($this->parametres['masque.tag_del']['AND'] as $mc) {

						$sousRequete = 'SELECT ce_image '.

							'FROM del_image_tag '.

							'WHERE actif = 1 '.

							"AND tag_normalise LIKE '%$mc%' ";

						$this->addWhere('masque.tag_del', "di.id_image IN ($sousRequete)");

					}

				} else if (isset($this->parametres['masque.tag_del']['OR'])) {

					$tagsMotif = "'(" . implode('|', $this->parametres['masque.tag_del']['OR']) . ")'";

					$sousRequete = 'SELECT ce_image '.

						'FROM del_image_tag '.

						'WHERE actif = 1 '.

						"AND tag_normalise REGEXP $tagsMotif ";

					$this->addWhere('masque.tag_del', "di.id_image IN ($sousRequete)");

				}

			} else {

				// Si un seul tag est demandé il se trouve dans le OR dans le cas de la recherche

				// spécifique et dans le AND dans le cas de la recherche générale

				// WTF?

				$tag = "";

				if(isset($this->parametres['masque.tag_del']['OR'][0])) {

					$tag = $this->parametres['masque.tag_del']['OR'][0];

				} else if(isset($this->parametres['masque.tag_del']['AND'][0])) {

					$tag = $this->parametres['masque.tag_del']['AND'][0];

				}

				$sousRequete = 'SELECT ce_image '.

						'FROM del_image_tag '.

						'WHERE actif = 1 '.

						'AND tag_normalise LIKE '.$this->bdd->proteger($tag.'%');

				$this->addWhere('masque.tag_del', "di.id_image IN ($sousRequete)");

			}

		}

	}

	private function getNombreDeTags() {

		$somme = 0;

		if (isset($this->parametres['masque.tag_del']['AND'])) {

			$somme = count($this->parametres['masque.tag_del']['AND']);

		} else {

			$somme = count($this->parametres['masque.tag_del']['OR']);

		}

		return $somme;

	}

	/**

	 * Partie spécifique à PictoFlora:

	 * Attention : si le critère de tri n'est pas suffisant, les résultats affichés peuvent varier à chaque appel

	 * de la même page de résultat de PictoFlora.

	 */

	public function definirOrdreSqlAppliImg() {

		$ordre = $this->parametres['ordre'];

		$tri = isset($this->parametres['tri']) ? $this->parametres['tri'] : '';

		switch ($tri) {

			case 'moyenne-arithmetique' :

				$this->addOrderBy("dis.moyenne $ordre, dis.nb_votes $ordre, id_image $ordre");

				break;

			case 'points' :

				$this->addOrderBy("dis.nb_points $ordre, dis.moyenne $ordre, dis.nb_votes $ordre, id_image $ordre");

				break;

			case 'tags' :

				$this->addOrderBy("dis.nb_tags $ordre, id_image $ordre");

				break;

			case 'date_observation' :

				$this->addOrderBy("date_observation $ordre, ce_observation $ordre");

				break;

			case 'date_transmission' :

			default:

				$this->addOrderBy("di.date_transmission $ordre, ce_observation $ordre");

		}

	}

	public function definirOrdreSqlAppliObs() {

		$ordre = $this->parametres['ordre'];

		// parmi self::$tri_possible

		$tri = isset($this->parametres['tri']) ? $this->parametres['tri'] : '';

			switch ($tri) {

			case 'date_observation' :

				$this->addOrderBy("date_observation $ordre, id_observation $ordre");

				break;

			case 'nb_commentaires' :

				$sql_nb_comms = '(SELECT COUNT(id_commentaire) FROM del_commentaire AS dc WHERE ce_observation = id_observation)';

				$this->addOrderBy("$sql_nb_comms $ordre, id_observation $ordre");

				break;

			case 'date_transmission' :

			default:

				$this->addOrderBy("do.date_transmission $ordre, id_observation $ordre");

		}

	}

	public function getAliasDesChamps($champsEtAlias, $select = null, $prefix = null) {

		$arr = ($select) ? array_intersect_key($champsEtAlias, array_flip($select)) :  $champsEtAlias;

		$keys = array_keys($arr);

		if ($prefix) {

		    array_walk($keys, function(&$val, $k, $prefix) { $val = sprintf("%s.`%s`", $prefix, $val);}, $prefix);

		} else {

		    array_walk($keys, function(&$val, $k) { $val = sprintf("`%s`", $val);});

		}

		return implode(', ', array_map(function($v, $k) {return sprintf("%s AS `%s`", $k, $v);}, $arr, $keys));

	}

	public function getVotesDesImages($idsImages, $protocole = null) {

		if (!$idsImages) return;

		$mappingVotes = $this->conteneur->getParametreTableau('votes.mapping');

		$mappingProtocoles = $this->conteneur->getParametreTableau('protocoles.mapping');

 		$selectVotes = array('id_vote', 'ce_image', 'ce_protocole', 'ce_utilisateur', 'valeur', 'date');

		$selectProtocole = array('id_protocole', 'intitule', 'descriptif', 'tag');

		$voteChamps = $this->getAliasDesChamps($mappingVotes, $selectVotes, 'v'); // "v": cf alias dans la requête

		$protoChamps = $this->getAliasDesChamps($mappingProtocoles, $selectProtocole, 'p');

		$idImgsConcat = implode(',', $idsImages);

		$requete = "SELECT $voteChamps, $protoChamps ".

			'FROM del_image_vote AS v '.

			'	INNER JOIN del_image_protocole AS p ON (v.ce_protocole = p.id_protocole) '.

			"WHERE v.ce_image IN ($idImgsConcat) ".

			($protocole ? "	AND v.ce_protocole = $protocole " : '').

			"ORDER BY FIELD(v.ce_image, $idImgsConcat) ".

			'-- '.__FILE__.':'.__LINE__;

		return $this->bdd->recupererTous($requete);

	}

	/**

	 * Ajoute les informations sur le protocole et les votes aux images.

	 *

	 * ATTENTION : Subtilité, nous passons ici le tableau d'images indexé par id_image qui est bien

	 * plus pratique pour associer les vote à un tableau, puisque nous ne connaissons pas les id d'observation.

	 * Mais magiquement (par référence), cela va remplir notre tableau indexé par couple d'id (id_image, id_observation)

	 * cf ListeImages::reformateImagesDoubleIndex() à qui revient la tâche de créer ces deux versions

	 * simultanément lorsque c'est encore possible.

	 */

	// TODO : supprimer cette "subtilité" source d'erreurs

	public function ajouterInfosVotesProtocoles($votes, &$images) {

		if (!$votes) return;

		$mappingVotes = $this->conteneur->getParametreTableau('votes.mapping');

		$mappingProtocoles = $this->conteneur->getParametreTableau('protocoles.mapping');

		// pour chaque vote

		foreach ($votes as $vote) {

			$imgId = $vote['image.id'];

			$protoId = $vote['protocole.id'];

			if (!array_key_exists('protocoles_votes', $images[$imgId]) ||

					!array_key_exists($protoId, $images[$imgId]['protocoles_votes'])) {

				// extrait les champs spécifique au protocole (le LEFT JOIN de chargerVotesImage les ramène en doublons

				$protocole = array_intersect_key($vote, array_flip($mappingProtocoles));

				$images[$imgId]['protocoles_votes'][$protoId] = $protocole;

			}

			$chpsVotes = array('id_vote', 'ce_image', 'ce_utilisateur', 'valeur', 'date');

			$voteSelection = array_intersect_key($mappingVotes, array_flip($chpsVotes));

			$vote = array_intersect_key($vote, array_flip($voteSelection));

			$images[$imgId]['protocoles_votes'][$protoId]['votes'][$vote['vote.id']] = $vote;

		}

	}

	public function getTotalLignesTrouvees() {

		$resultat = $this->bdd->recuperer('SELECT FOUND_ROWS() AS nbre -- '.__FILE__.':'.__LINE__);

		return intval($resultat['nbre']);

	}

	public function getRequeteIdObsMonactiviteTout($id_utilisateur, $limite = "") {

		/*

		Une action c'est :

		- Quelqu'un commente mon observation

		- Quelqu'un fait une proposition sur mon observation

		- Quelqu'un vote pour ma proposition

		- Quelqu'un commente ma proposition ou mon commentaire

		- Quelqu'un vote pour une proposition sur mon observation

		- Quelqu'un commente une proposition ou un commentaire sur mon observation

		*/

		// on selectionne aussi la combinaison des champs de date afin que la liste soit triée correctement

		// pas d'autre meilleure solution mais attention ce comportement dépend entièrement de mysql

		$requete = "SELECT SQL_CALC_FOUND_ROWS DISTINCT id_observation, ".$this->getCombinaisonChampsDateMax()." as date_max FROM del_observation do ".

				$this->getJointureMonActivite($id_utilisateur).

				$this->getConditionMonActivite($id_utilisateur).

				"ORDER BY date_max DESC ".

				$limite;

		return $requete;

	}

	public function getRequeteNbEvenementsDepuisDate($id_utilisateur, $date) {

		$requete = "SELECT COUNT(DISTINCT id_observation) as nb_evenements FROM del_observation do ".

				$this->getJointureMonActivite($id_utilisateur).

				$this->getConditionMonActivite($id_utilisateur).

				"AND ".$this->getCombinaisonChampsDateMax()." > '".$date."' ".

				"ORDER BY ".$this->getCombinaisonChampsDateMax()." DESC ";

		return $requete;

	}

	public function getEvenementsObs($idsObsConcat, $id_utilisateur) {

		$sous_champ_date_max = $this->getCombinaisonChampsDateMax()." as date_max";

		$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, ".

							"do.date_observation as date_obs, dcp.date_validation as date_validation, dcpr.date as date_com_reponse, ".

							"dcvp.nom_sel as nom_sel_com_parent";

		$sous_champs_utilisateurs = "dc.ce_utilisateur as utilisateur_commentaire, dcp.ce_utilisateur as utilisateur_commentaire_valide, ".

									"dcv.ce_utilisateur as utilisateur_vote_commentaire, do.ce_utilisateur as utilisateur_observation, ".

									"dcp.ce_validateur as utilisateur_validation, dcvp.ce_utilisateur as utilisateur_commentaire_vote, ".

									"dcpr.ce_utilisateur as utilisateur_commentaire_reponse";

		$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, ".

							 "dcvp.nom_sel as proposition_commentaire_nom_sel_votee, dcpr.texte as proposition_commentaire_texte_commente";

		$requete = "SELECT DISTINCT id_observation, ".$sous_champs_utilisateurs.", ".$sous_champ_date_max.", ".$sous_champ_date.", ".$sous_champs_infos." ".

				"FROM del_observation do ".

				$this->getJointureMonActivite($id_utilisateur).

				$this->getConditionMonActivite($id_utilisateur).

				"AND id_observation IN ($idsObsConcat) ORDER BY date_max DESC";

		$evenements = $this->bdd->recupererTous($requete);

		return $evenements;

	}

	public function getJointureMonActivite($id_utilisateur) {

		return 	// quelqu'un commente mon observation ou fait une proposition

		"LEFT JOIN del_commentaire dc ON do.id_observation = dc.ce_observation ".

		"	AND do.ce_utilisateur = ".$id_utilisateur." ".

		"	AND dc.ce_utilisateur != ".$id_utilisateur." ".

		// quelqu'un valide ma proposition (et ce n'est pas moi qui l'ai validée)

		"LEFT JOIN del_commentaire dcp ON do.id_observation = dcp.ce_observation ".

		"	AND dcp.nom_sel IS NOT NULL AND dcp.ce_validateur != ".$id_utilisateur." ".

		"	AND dcp.ce_validateur != 0 ".

		"	AND dcp.date_validation IS NOT NULL ".

		"	AND dcp.ce_utilisateur = ".$id_utilisateur." ".

		// quelqu'un vote pour ma proposition ou sur une proposition sur une de mes observations

		"LEFT JOIN del_commentaire dcvp ON do.id_observation = dcvp.ce_observation ".

		"LEFT JOIN del_commentaire_vote dcv ON dcv.ce_proposition = dcvp.id_commentaire ".

		"AND (dcvp.ce_utilisateur = $id_utilisateur OR do.ce_utilisateur = $id_utilisateur) ".

		"AND dcv.ce_utilisateur != $id_utilisateur ".

		"AND dcv.ce_utilisateur != dcvp.ce_utilisateur ".

		// Quelqu'un répond à l'un de mes commentaires ou commente une de mes propositions

		"LEFT JOIN del_commentaire dcpr ON do.id_observation = dcpr.ce_observation ".

		"AND dcpr.ce_commentaire_parent = dcvp.id_commentaire AND dcvp.ce_utilisateur = $id_utilisateur ";

	}

	public function getConditionMonActivite($id_utilisateur, $type = "autres") {

		//TODO: gérer les cas suivants :

		// demander les activités des autres sur mes obs ou propositions (c'est dejà le cas)

		// demander mes activités

		// demander toutes les activités (combinaisons des deux cas ci dessus)

		return 	// Vérification que l'évènement me concerne (de près ou ou de loin)

		"WHERE (do.ce_utilisateur = $id_utilisateur OR dc.ce_utilisateur = $id_utilisateur ".

		"OR dcp.ce_utilisateur = $id_utilisateur OR dcv.ce_utilisateur = $id_utilisateur ".

		"OR dcvp.ce_utilisateur = $id_utilisateur) AND ".

		// mais qu'il y a au moins eu une action de la part d'une autre personne

		"(dc.ce_utilisateur IS NOT NULL OR dcp.ce_utilisateur IS NOT NULL OR dcv.ce_utilisateur IS NOT NULL) ";

	}

	private function getCombinaisonChampsDateMax() {

		return "GREATEST(IFNULL(dc.date,0), IFNULL(dcv.date,0), IFNULL(do.date_observation,0), IFNULL(dcp.date_validation,0), IFNULL(dcpr.date,0))";

	}

}