WebSVN – eFlore/Applications.del – Diff – /trunk/services/bibliotheque/Sql.php


<?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éfix de del_image est "di"
 * - le préfix de del_observation est "do"
 * - le préfix 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.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' ? true : false;
	}
 
	private function etreAppliObs() {
		return $this->appli === 'OBS' ? true : false;
	}
 
	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...
		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']['LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ']);
		}
 
		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->ajouterContrainteAuteur();
		$this->ajouterContrainteDate();
		$this->ajouterContraintePays();
		$this->ajouterContrainteDepartement();
		$this->ajouterContrainteIdZoneGeo();
		$this->ajouterContrainteGenre();
		$this->ajouterContrainteFamille();
		$this->ajouterContrainteNs();
		$this->ajouterContrainteNn();
		$this->ajouterContrainteReferentiel();
		$this->ajouterContrainteCommune();
	}
 
	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 = "(du.id_utilisateur = %1\$d OR $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 = "(du.courriel LIKE %1\$s OR $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);
			}
 
			if ($this->etreAppliImg()) {
				$this->addJoin('LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ');
			}
		}
	}
 
	private function ajouterContrainteDepartement() {
		if (isset($this->parametres['masque.departement'])) {
			$dept = $this->parametres['masque.departement'];
			$deptMotif = $this->bdd->proteger("INSEE-C:$dept");
			$this->addWhere('masque.departement', "do.ce_zone_geo LIKE $deptMotif");
 
			if ($this->etreAppliImg()) {
				$this->addJoin('LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ');
			}
		}
	}
	
	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)");
	
			if ($this->etreAppliImg()) {
				$this->addJoin('LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ');
			}
		}
	}
 
	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");
			if ($this->etreAppliImg()) {
				$this->addJoin('LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ');
			}
		}
	}
 
	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");
 
			if ($this->etreAppliImg()) {
				$this->addJoin('LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ');
			}
		}
	}
 
	private function ajouterContrainteFamille() {
		if (isset($this->parametres['masque.famille'])) {
			$familleMotif = $this->bdd->proteger($this->parametres['masque.famille']);
			$this->addWhere('masque.famille', "do.famille = $familleMotif");
 
			if ($this->etreAppliImg()) {
				$this->addJoin('LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ');
			}
		}
	}
 
	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");
 
			if ($this->etreAppliImg()) {
				$this->addJoin('LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ');
			}
		}
	}
 
	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);
 
			if ($this->etreAppliImg()) {
				$this->addJoin('LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ');
			}
		}
	}
 
	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 LIKE $refMotif");
 
			if ($this->etreAppliImg()) {
				$this->addJoin('LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ');
			}
		}
	}
 
	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");
 
			if ($this->etreAppliImg()) {
				$this->addJoin('LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ');
			}
		}
	}
 
	public function ajouterConstrainteAppliObs() {
		$this->ajouterContrainteTagCel();
		$this->ajouterContrainteType();
		// TODO : ATTENTION -> vue 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 = "aDeterminer" '.
					'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'.
						') '	
					);
			}	
 
			if ($this->etreAppliImg()) {
				$this->addJoin('LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ');
			}
		}
	}
	
	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();
	}
 
	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");
 
			if ($this->etreAppliImg()) {
				$this->addJoin('LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ');
			}
		}
	}
 
	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) ');
				}
			}
			if ($this->etreAppliImg()) {
				$this->addJoin('LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ');
			}
		}
	}
 
	/**
	 * 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) {
				$tagsMotif = $this->construireTagsMotif();
				if (is_null($tagsMotif) === false) {
					$sousRequete = 'SELECT ce_image '.
						'FROM del_image_tag '.
						'WHERE actif = 1 '.
						'GROUP BY ce_image '.
						"HAVING GROUP_CONCAT(DISTINCT tag_normalise ORDER BY tag_normalise) REGEXP $tagsMotif ";
				}
			} else {
				// Si un seul tag est demandé il se trouve dans le OR
				$tag = $this->parametres['masque.tag_del']['OR'][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;
	}
 
	private function construireTagsMotif() {
		$tagsMotif = null;
		if (isset($this->parametres['masque.tag_del']['AND'])) {
			$tags = $this->parametres['masque.tag_del']['AND'];
			// ATTENTION -> optimisation: en cas de "AND" on sort() l'input et le GROUP_CONCAT()
			// donc nous utilisons des ".*" plutôt que de multiples conditions et "|"
			sort($tags);
			$tagsMotif = $this->bdd->proteger(implode('.*', $tags));
		} else if (isset($this->parametres['masque.tag_del']['OR'])) {
			$tags = $this->parametres['masque.tag_del']['OR'];
			$tagsMotif = $this->bdd->proteger(implode('|', $tags));
		}
		return $tagsMotif;
	}
 
	/**
	 * 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, create_function('&$val, $k, $prefix', '$val = sprintf("%s.`%s`", $prefix, $val);'), $prefix);
		} else {
			array_walk($keys, create_function('&$val, $k', '$val = sprintf("`%s`", $val);'));
		}
 
		return implode(', ', array_map(create_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']);
	}
}

Subversion Repositories eFlore/Applications.del

(root)/trunk/services/bibliotheque/Sql.php – Rev 2004 → 2029

Rev 2004		Rev 2029
1	`<?php`	1	`<?php`
2	`// declare(encoding='UTF-8');`	2	`// declare(encoding='UTF-8');`
3	`/**`	3	`/**`
4	`* Classe contenant des méthodes permettant de construire les requêtes SQL complexe concernant les images et obs.`	4	`* Classe contenant des méthodes permettant de construire les requêtes SQL complexe concernant les images et obs.`
5	`* Rempli un tableau des clauses "join", "where", "group by" et "oder by" nécessaire à la recherche des ids des`	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`	6	`* observations/images correspondantes aux filtres passés dans l'url du web service de recherche`
7	`* (ListeImages et ListeObservations).`	7	`* (ListeImages et ListeObservations).`
8	`*`	8	`*`
9	`* Attention, cela signifie que toutes les tables ne sont pas forcément jointées, par exemple si aucune`	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.`	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.`	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`	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 !`	13	`* requête directement dans le web service qui s'en charge. Cette technique en deux étapes est la plus rapide !`
14	`*`	14	`*`
15	`* Note: toujours rajouter les préfixes de table (di, do ou du), en fonction de ce que défini`	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 :`	16	`* les JOIN qui sont utilisés :`
17	`* - le préfix de del_image est "di"`	17	`* - le préfix de del_image est "di"`
18	`* - le préfix de del_observation est "do"`	18	`* - le préfix de del_observation est "do"`
19	`* - le préfix de del_utilisateur est "du"`	19	`* - le préfix de del_utilisateur est "du"`
20	`*`	20	`*`
21	`* @category DEL`	21	`* @category DEL`
22	`* @package Services`	22	`* @package Services`
23	`* @package Bibliotheque`	23	`* @package Bibliotheque`
24	`* @version 0.1`	24	`* @version 0.1`
25	`* @author Mathias CHOUET <mathias@tela-botanica.org>`	25	`* @author Mathias CHOUET <mathias@tela-botanica.org>`
26	`* @author Jean-Pascal MILCENT <jpm@tela-botanica.org>`	26	`* @author Jean-Pascal MILCENT <jpm@tela-botanica.org>`
27	`* @author Aurelien PERONNET <aurelien@tela-botanica.org>`	27	`* @author Aurelien PERONNET <aurelien@tela-botanica.org>`
28	`* @license GPL v3 <http://www.gnu.org/licenses/gpl.txt>`	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>`	29	`* @license CECILL v2 <http://www.cecill.info/licences/Licence_CeCILL_V2-en.txt>`
30	`* @copyright 1999-2014 Tela Botanica <accueil@tela-botanica.org>`	30	`* @copyright 1999-2014 Tela Botanica <accueil@tela-botanica.org>`
31	`*/`	31	`*/`
32	`class Sql {`	32	`class Sql {`
33		33
34	`const APPLI_IMG = 'IMG';`	34	`const APPLI_IMG = 'IMG';`
35	`const APPLI_OBS = 'OBS';`	35	`const APPLI_OBS = 'OBS';`
36		36
37	`private $conteneur;`	37	`private $conteneur;`
38	`private $bdd;`	38	`private $bdd;`
39	`private $parametres = array();`	39	`private $parametres = array();`
40	`private $appli;`	40	`private $appli;`