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,13 → 43,35 |
$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; |
} |
} |
|
public function getJoin() { |
return ($this->requete['join'] ? implode(' ', array_unique($this->requete['join'])).' ' : ''); |
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,86 → 258,122 |
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) '); |
} |
} |
} |
|
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', "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) '); |
} |
} |
} |
|
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) '); |
} |
} |
} |
|
private function ajouterContrainteGenre() { |
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) '); |
} |
} |
} |
|
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) '); |
} |
} |
} |
|
private function ajouterContrainteNs() { |
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) '); |
} |
} |
} |
|
private function ajouterContrainteReferentiel() { |
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) '); |
} |
} |
} |
|
private function ajouterContrainteCommune() { |
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) '); |
} |
} |
} |
|
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('vdi.id_observation'); |
$this->addGroupBy('do.id_observation'); |
} |
|
/** |
313,21 → 380,22 |
* @param $req: la représentation de la requête MySQL complète, à amender. |
*/ |
private function ajouterContrainteType() { |
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', '('. |
'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 !! |
'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 (vdi.id_observation = dc.ce_observation AND dc.proposition_retenue = 1) '); |
'ON (do.id_observation = dc.ce_observation AND dc.proposition_retenue = 1) '); |
} |
|
if (array_key_exists('endiscussion', $this->parametres['masque.type'])) { |
335,7 → 403,12 |
$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) '); |
} |
} |
} |
|
/** |
* in $p: un tableau de paramètres, dont: |
366,9 → 439,13 |
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) '); |
} |
} |
} |
|
/** Pour le tri par AVG() des votes nous avons toujours un protocole donné, |
* celui-ci indique sur quels votes porte l'AVG. |
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,13 → 502,19 |
$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) '); |
} |
} |
} |
|
/** |
* Plusieurs solutions sont disponibles dans les anciennes versions (voir DelTk). |
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"); |
} |
} |
|