Rev 2072 | Rev 2156 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed
<?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'][$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->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 = "($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("INSEE-C:$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 LIKE $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();}}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) ';}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 fonctionnementif (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'.') ');}$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();}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* lentif (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, 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'erreurspublic function ajouterInfosVotesProtocoles($votes, &$images) {if (!$votes) return;$mappingVotes = $this->conteneur->getParametreTableau('votes.mapping');$mappingProtocoles = $this->conteneur->getParametreTableau('protocoles.mapping');// pour chaque voteforeach ($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))";}}