| 16,9 → 16,13 |
|---|
| */ |
| class Sql { |
| |
| const APPLI_IMG = 'IMG'; |
| const APPLI_OBS = 'OBS'; |
| |
| private $conteneur; |
| private $bdd; |
| private $parametres = array(); |
| private $appli; |
| private $requete = array( |
| 'select' => array(), |
| 'join' => array(), |
| 26,8 → 30,8 |
|---|
| 'groupby' => array(), |
| 'orderby' => array()); |
| |
| private $champsPrenom = array('du.prenom', 'vdi.prenom_utilisateur'); |
| private $champsNom = array('du.nom', 'vdi.nom_utilisateur'); |
| private $champsPrenom = array('du.prenom', 'prenom_utilisateur'); |
| private $champsNom = array('du.nom', 'nom_utilisateur'); |
| |
| |
| public function __construct(Conteneur $conteneur) { |
| 39,12 → 43,34 |
|---|
| $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) { |
| $this->requete['join'][] = $join; |
| if (!isset($this->requete['join'][$join])) { |
| $this->requete['join'][] = $join; |
| } |
| } |
| |
| public function getJoin() { |
| 139,14 → 165,12 |
|---|
| if (isset($this->parametres['masque.auteur'])) { |
| $auteur = $this->parametres['masque.auteur']; |
| // id du poster de l'obs |
| $this->addJoin('LEFT JOIN del_utilisateur AS du ON (du.id_utilisateur = vdi.ce_utilisateur) '); |
| // id du poster de l'image... NON, c'est le même que le posteur de l'obs |
| // Cette jointure de table est ignoré ci-dessous pour les recherches d'auteurs |
| // $req['join'][] = 'LEFT JOIN del_utilisateur AS dui ON dui.id_utilisateur = vdi.i_ce_utilisateur'; |
| $prefixe = $this->getPrefixe(); |
| $this->addJoin("LEFT JOIN del_utilisateur AS du ON (du.id_utilisateur = $prefixe.ce_utilisateur) "); |
| |
| if (is_numeric($auteur)) { |
| $this->ajouterContrainteAuteurId(); |
| } elseif(preg_match('/^.{5,}@[a-z0-9-.]{5,}$/i', $auteur)) { |
| } elseif(preg_match('/@[a-z0-9-]+(?:\.[a-z0-9-]+)*\.[a-z]{2,}$/i', $auteur)) { |
| $this->ajouterContrainteAuteurEmail(); |
| } else { |
| $this->ajouterContrainteAuteurIntitule(); |
| 156,7 → 180,8 |
|---|
| |
| private function ajouterContrainteAuteurId() { |
| $id = $this->parametres['masque.auteur']; |
| $sqlTpl = '(du.id_utilisateur = %1$d OR vdi.ce_utilisateur = %1$d)'; |
| $prefixe = $this->getPrefixe(); |
| $sqlTpl = "(du.id_utilisateur = %1\$d OR $prefixe.ce_utilisateur = %1\$d)"; |
| $whereAuteur = sprintf($sqlTpl, $id); |
| $this->addWhere('masque.auteur', $whereAuteur); |
| } |
| 163,7 → 188,8 |
|---|
| |
| private function ajouterContrainteAuteurEmail() { |
| $email = $this->parametres['masque.auteur']; |
| $sqlTpl = '(du.courriel LIKE %1$s OR vdi.courriel LIKE %1$s )'; |
| $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); |
| 190,8 → 216,9 |
|---|
| $prenomOuNom = $this->parametres['masque.auteur']; |
| |
| $sqlTpl = 'CONCAT(%s,%s) LIKE %s'; |
| $champsPrenomSql = self::ajouterIfNullPourConcat($this->champsPrenom); |
| $champsNomSql = self::ajouterIfNullPourConcat($this->champsNom); |
| $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); |
| 202,8 → 229,9 |
|---|
| 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)'; |
| $champsPrenomSql = self::ajouterIfNullPourConcat($this->champsPrenom); |
| $champsNomSql = self::ajouterIfNullPourConcat($this->champsNom); |
| $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%"); |
| |
| 216,9 → 244,12 |
|---|
| * Il faut donc utiliser la syntaxe IFNULL(%s, ""). |
| * (Cette fonction effectue aussi l'implode() "final" |
| */ |
| private static function ajouterIfNullPourConcat($champs) { |
| 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); |
| 227,16 → 258,20 |
|---|
| private function ajouterContrainteDate() { |
| if (isset($this->parametres['masque.date'])) { |
| $date = $this->parametres['masque.date']; |
| if (is_integer($date) && $date < 2030 && $date > 1600) { |
| $sqlTpl = "YEAR(vdi.date_observation) = %d"; |
| 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 = "DATE_FORMAT(vdi.date_observation, '%%Y-%%m-%%d') = %s"; |
| $dateP = $this->bdd->proteger(strftime('%Y-%m-%d', $date)); |
| $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) '); |
| } |
| } |
| } |
| |
| 244,7 → 279,11 |
|---|
| if (isset($this->parametres['masque.departement'])) { |
| $dept = $this->parametres['masque.departement']; |
| $deptMotif = $this->bdd->proteger("INSEE-C:$dept"); |
| $this->addWhere('masque.departement', "vdi.ce_zone_geo LIKE $deptMotif"); |
| $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) '); |
| } |
| } |
| } |
| |
| 251,7 → 290,11 |
|---|
| 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', "vdi.ce_zone_geo = $idZgMotif"); |
| $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) '); |
| } |
| } |
| } |
| |
| 259,7 → 302,11 |
|---|
| if (isset($this->parametres['masque.genre'])) { |
| $genre = $this->parametres['masque.genre']; |
| $genreMotif = $this->bdd->proteger("%$genre% %"); |
| $this->addWhere('masque.genre', "vdi.nom_sel LIKE $genreMotif"); |
| $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) '); |
| } |
| } |
| } |
| |
| 266,7 → 313,11 |
|---|
| private function ajouterContrainteFamille() { |
| if (isset($this->parametres['masque.famille'])) { |
| $familleMotif = $this->bdd->proteger($this->parametres['masque.famille']); |
| $this->addWhere('masque.famille', "vdi.famille = $familleMotif"); |
| $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) '); |
| } |
| } |
| } |
| |
| 274,15 → 325,23 |
|---|
| if (isset($this->parametres['masque.ns'])) { |
| $ns = $this->parametres['masque.ns']; |
| $nsMotif = $this->bdd->proteger("$ns%"); |
| $this->addWhere('masque.ns', "vdi.nom_sel LIKE $nsMotif"); |
| $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 = '(vdi.nom_sel_nn = %1$d OR vdi.nom_ret_nn = %1$d)'; |
| $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) '); |
| } |
| } |
| } |
| |
| 290,7 → 349,11 |
|---|
| if (isset($this->parametres['masque.referentiel'])) { |
| $ref = $this->parametres['masque.referentiel']; |
| $refMotif = $this->bdd->proteger("$ref%"); |
| $this->addWhere('masque.referentiel', "vdi.nom_referentiel LIKE $refMotif"); |
| $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) '); |
| } |
| } |
| } |
| |
| 298,7 → 361,11 |
|---|
| if (isset($this->parametres['masque.commune'])) { |
| $commune = $this->parametres['masque.commune']; |
| $communeMotif = $this->bdd->proteger("$commune%"); |
| $this->addWhere('masque.commune', "vdi.zone_geo LIKE $communeMotif"); |
| $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) '); |
| } |
| } |
| } |
| |
| 306,7 → 373,7 |
|---|
| $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('vdi.id_observation'); |
| $this->addGroupBy('do.id_observation'); |
| } |
| |
| /** |
| 313,27 → 380,33 |
|---|
| * @param $req: la représentation de la requête MySQL complète, à amender. |
| */ |
| private function ajouterContrainteType() { |
| if (array_key_exists('adeterminer', $this->parametres['masque.type'])) { |
| // Récupèration de toutes les observations qui on 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', '('. |
| 'vdi.certitude = "aDeterminer" '. |
| 'OR vdi.certitude = "douteux" '. |
| 'OR vdi.mots_cles_texte LIKE "%aDeterminer%" '. |
| 'OR vdi.nom_sel_nn IS NULL '. |
| 'OR vdi.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'])) { |
| // Récupèration de toutes les observations ayant un commentaire doté de proposition_retenue = 1 |
| $this->addJoin('INNER JOIN del_commentaire AS dc '. |
| 'ON (vdi.id_observation = dc.ce_observation AND dc.proposition_retenue = 1) '); |
| } |
| if (isset($this->parametres['masque.type'])) { |
| if (array_key_exists('adeterminer', $this->parametres['masque.type'])) { |
| // Récupèration de toutes les observations qui on 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'])) { |
| // Récupèration de toutes les observations ayant un commentaire doté de proposition_retenue = 1 |
| $this->addJoin('INNER JOIN del_commentaire AS dc '. |
| 'ON (do.id_observation = dc.ce_observation AND dc.proposition_retenue = 1) '); |
| } |
| |
| if (array_key_exists('endiscussion', $this->parametres['masque.type'])) { |
| $nbreCommentaire =(int) ($this->conteneur->getParametre('observations.nb_commentaires_discussion')); |
| $this->addWhere('masque.type', '(SELECT COUNT(id_commentaire) FROM del_commentaire AS dc '. |
| "WHERE ce_observation = id_observation) > $nbreCommentaire "); |
| if (array_key_exists('endiscussion', $this->parametres['masque.type'])) { |
| $nbreCommentaire =(int) ($this->conteneur->getParametre('observations.nb_commentaires_discussion')); |
| $this->addWhere('masque.type', '(SELECT COUNT(id_commentaire) FROM del_commentaire AS dc '. |
| "WHERE ce_observation = id_observation) > $nbreCommentaire "); |
| } |
| |
| if ($this->etreAppliImg()) { |
| $this->addJoin('LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) '); |
| } |
| } |
| } |
| |
| 366,7 → 439,11 |
|---|
| if (isset($this->parametres['masque.milieu'])) { |
| $milieu = $this->parametres['masque.milieu']; |
| $milieuMotif = $this->bdd->proteger("%$milieu%"); |
| $this->addWhere('masque.milieu', "vdi.milieu LIKE $milieuMotif"); |
| $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) '); |
| } |
| } |
| } |
| |
| 392,7 → 469,7 |
|---|
| |
| 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 dis ON vdi.id_image = dis.ce_image AND dis.ce_protocole = %d'; |
| $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); |
| } |
| 399,9 → 476,9 |
|---|
| |
| if (isset($this->parametres['ordre']) && $tri == 'tags') { |
| $typeJointure = ($this->parametres['ordre'] == 'desc') ? 'INNER' : 'LEFT'; |
| $this->addJoin("$typeJointure JOIN del_image_stat dis ON vdi.id_image = dis.ce_image"); |
| $this->addJoin("$typeJointure JOIN del_image_stat AS dis ON di.id_image = dis.ce_image"); |
| // nécessaire (dup ce_image dans del_image_stat) |
| $this->addGroupBy('vdi.id_observation'); |
| $this->addGroupBy('di.ce_observation'); |
| } |
| } |
| } |
| 418,7 → 495,7 |
|---|
| $clausesWhere = array(); |
| foreach ($tags as $tag) { |
| $tagMotif = $this->bdd->proteger("%$tag%"); |
| $sqlTpl = "CONCAT(IFNULL(vdi.mots_cles_texte,''),IFNULL(vdi.i_mots_cles_texte,'')) LIKE %s"; |
| $sqlTpl = "CONCAT(IFNULL(do.mots_cles_texte,''),IFNULL(di.mots_cles_texte,'')) LIKE %s"; |
| $clausesWhere[] = sprintf($sqlTpl, $tagMotif); |
| } |
| $whereTags = implode(' AND ', $clausesWhere); |
| 425,11 → 502,17 |
|---|
| $this->addWhere('masque.tag_cel', "($whereTags)"); |
| } else if (isset($this->parametres['masque.tag_cel']['OR'])) { |
| $tags = $this->parametres['masque.tag_cel']['OR']; |
| $sqlTpl = "CONCAT(IFNULL(vdi.mots_cles_texte,''),IFNULL(vdi.i_mots_cles_texte,'')) REGEXP %s"; |
| $sqlTpl = "CONCAT(IFNULL(do.mots_cles_texte,''),IFNULL(di.mots_cles_texte,'')) REGEXP %s"; |
| $tagMotif = $this->bdd->proteger(implode('|', $tags)); |
| $tagSql = sprintf($sqlTpl, $tagMotif); |
| $this->addWhere('masque.tag_cel', $tagSql); |
| } |
| if ($this->etreAppliImg()) { |
| $this->addJoin('LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) '); |
| } |
| if ($this->etreAppliObs()) { |
| $this->addJoin('LEFT JOIN del_image AS di ON (do.id_observation = di.ce_observation) '); |
| } |
| } |
| } |
| |
| 462,6 → 545,7 |
|---|
| 'GROUP BY ce_image '. |
| "HAVING GROUP_CONCAT(tag_normalise) REGEXP $tagsMotif ". |
| ' -- '.__FILE__.' : '.__LINE__; |
| |
| $sql = $this->recupererSqlContrainteTag($requete); |
| $this->addWhere('masque.tag_del', $sql); |
| } |
| 480,7 → 564,7 |
|---|
| } else { |
| $clauseIn = 'SELECT ce_image FROM del_image_tag WHERE false'; |
| } |
| return "vdi.id_image IN ($clauseIn)"; |
| return "di.id_image IN ($clauseIn)"; |
| } |
| |
| /** |
| 502,11 → 586,11 |
|---|
| $this->addOrderBy("dis.nb_tags $ordre, id_image DESC"); |
| break; |
| case 'date_observation' : |
| $this->addOrderBy("date_observation $ordre, id_observation $ordre"); |
| $this->addOrderBy("date_observation $ordre, ce_observation $ordre"); |
| break; |
| case 'date_transmission' : |
| default: |
| $this->addOrderBy("date_transmission $ordre, id_observation $ordre"); |
| $this->addOrderBy("di.date_transmission $ordre, ce_observation $ordre"); |
| } |
| } |
| |
| 519,7 → 603,7 |
|---|
| $this->addOrderBy("date_observation $ordre, id_observation $ordre"); |
| break; |
| default: |
| $this->addOrderBy("date_transmission $ordre, id_observation $ordre"); |
| $this->addOrderBy("do.date_transmission $ordre, id_observation $ordre"); |
| } |
| } |
| |