| New file |
|---|
| 0,0 → 1,842 |
|---|
| <?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 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'. |
| ') ' |
| ); |
| } |
| |
| $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) { |
| $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 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; |
| } |
| |
| 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']); |
| } |
| |
| 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))"; |
| } |
| } |
| Property changes: |
|---|
| Added: svnkit:entry:sha1-checksum |
|---|
| +d671d5d650c4560e1a9b099d98db1e0c78f00002 |
|---|
| \ No newline at end of property |
|---|