'c.ci_meta_mots_cles', 'auteur' => 'c.ci_ce_utilisateur', 'commune' => 'b.location', 'dept' => 'b.id_location', 'taxon' => 'b.nom_ret', 'famille' => 'b.famille'); function InventoryImageListPublic($config) { parent::__construct($config); $this->start = 0; $this->limit = 100; $this->distinct = true; } function getRessource() { } function getElement($uid) { $criteres = array() ; if($uid[0] == '*') { $criteres = array(); } else { if(!$this->estUneRechercheGenerale()) { $criteres = $this->traiterCriteres($_GET); if(empty($criteres)) { header("content-type: text/html"); $images_json = json_encode(array()); print $images_json; exit() ; } } } if(isset($_GET['start'])) { $this->start = $_GET['start']; } if(isset($_GET['limit'])) { $this->limit = $_GET['limit']; } // Construction de la requête $requete_debut = 'SELECT * '; $requete_nb = 'SELECT COUNT(*) as nb_res '; $jointure = 'FROM cel_obs_images a '. 'INNER JOIN cel_inventory b '. 'ON a.coi_ce_observation = b.ordre AND a.coi_ce_utilisateur = b.identifiant '. 'INNER JOIN cel_images c '. 'ON a.coi_ce_image = c.ci_id_image AND a.coi_ce_utilisateur = c.ci_ce_utilisateur '. 'WHERE b.transmission = 1 AND b.identifiant = c.ci_ce_utilisateur AND '; $requete_debut .= $jointure; $requete_nb .= $jointure; if($this->estUneRechercheGenerale()) { $chaine_requete = $_GET['recherche']; $requete = $this->creerSousRequeteRechercheGenerale($chaine_requete) ; } else { $criteres = $this->traiterCriteres($_GET) ; $requete = $this->creerSousRequeteRechercheParCriteres($criteres); } $requete_debut .= $requete; $requete_nb .= $requete; $requete_debut = rtrim($requete_debut,'AND '); $requete_nb = rtrim($requete_nb,'AND '); $requete_debut .= ' ORDER BY c.ci_meta_date_ajout DESC '; $requete_debut .= "LIMIT $this->start,$this->limit "; $res = $this->executerRequete($requete_debut); $res_nb =& $this->executerRequete($requete_nb); $images = array(); $total = 0; foreach ($res as $row) { $images[]= $row; $nb_res = $row['nb_res']; } foreach ($res_nb as $row) { $total= $row['nb_res']; } $resultat = array('total' => $total,'images' => $images); $images_json = json_encode($resultat) ; $images_json = str_replace('\u0000','',$images_json); header("content-type: text/html") ; print $images_json ; exit() ; } private function creerSousRequeteRechercheParCriteres($criteres) { $requete = ''; foreach($criteres as $pair) { $nom_valeur = explode("=",$pair) ; if(sizeof($nom_valeur) != 0) { if($nom_valeur[0] == "ci_limite") { $this->limite = $this->proteger($nom_valeur[1]) ; } elseif($nom_valeur[0] == "c.ci_numero_page") { $this->start = $this->limite*$this->proteger($nom_valeur[1]) ; } elseif($nom_valeur[0] == "c.ci_ce_utilisateur") { $requete .= 'c.ci_ce_utilisateur LIKE '.$this->proteger($nom_valeur[1].'%') ; $requete .= ' AND '; } elseif($nom_valeur[0] == "c.ci_meta_mots_cles") { $requete .= $this->creerSousRequeteMotsCles($nom_valeur[1]); $requete .= ' AND '; } elseif($nom_valeur[0] == "c.ci_meta_comment") { $mots_comment_liste = explode(" " , $nom_valeur[1]) ; foreach($mots_comment_liste as $mot_comment) { $mot_comment = trim($mot_comment) ; $requete .= $nom_valeur[0].' LIKE '.$this->proteger($mot_comment.'%'); $requete .= ' AND ' ; } } elseif($nom_valeur[0] == "c.ci_meta_date") { $requete .= 'DATE_FORMAT( '.$nom_valeur[0].', \'%Y-%m-%d\' ) = '.$this->proteger($nom_valeur[1]) ; $requete .= ' AND ' ; } elseif($nom_valeur[0] == "c.ci_id_tampon") { $ids_tampon = rtrim($nom_valeur[1], ',') ; $requete .= 'c.ci_id_image IN ( '.$this->proteger($ids_tampon).')' ; } elseif($nom_valeur[0] == "b.nom_ret") { if($nom_valeur[1] == "indetermine") { $nom_valeur[1] = 'null'; } $requete .= ' ('; $requete .= $nom_valeur[0].' LIKE '.$this->proteger($nom_valeur[1].'%') ; $requete .= ' OR ' ; $requete .= 'b.nom_sel LIKE '.$this->proteger($nom_valeur[1].'%') ; $requete .= ') AND ' ; } else { $requete .= $nom_valeur[0].' = '.$this->proteger($nom_valeur[1]) ; $requete .= ' AND ' ; } } } $requete = rtrim($requete,' AND ') ; return $requete; } private function creerSousRequeteRechercheGenerale($chaine_requete) { if(trim($chaine_requete) == '') { return ''; } $chaine_requete = strtolower($chaine_requete); $chaine_requete = str_replace(' ','_',$chaine_requete); $requete = ' ('; $requete .= 'b.nom_ret LIKE '.$this->proteger($chaine_requete.'%') ; $requete .= ' OR ' ; $requete .= 'b.nom_sel LIKE '.$this->proteger($chaine_requete.'%') ; $requete .= ' OR ' ; $requete .= 'b.location LIKE '.$this->proteger($chaine_requete.'%') ; $requete .= ' OR ' ; $requete .= 'b.id_location LIKE '.$this->proteger($chaine_requete.'%') ; $requete .= ' OR ' ; $requete .= 'b.famille LIKE '.$this->proteger($chaine_requete.'%') ; $requete .= ' OR ' ; $requete_mots_cles .= $this->creerSousRequeteMotsCles($chaine_requete); if(trim($requete_mots_cles) != '') { $requete .= $requete_mots_cles; $requete .= ' OR ' ; } $requete .= 'c.ci_ce_utilisateur LIKE '.$this->proteger($chaine_requete.'%') ; $requete .= ') '; return $requete; } private function estUneRechercheGenerale() { return isset($_GET['recherche']); } private function traiterCriteres($tableau_criteres) { $tableau_criteres_pour_bdd = array(); foreach($tableau_criteres as $nom_critere => $valeur_critere) { if(isset($this->criteres[$nom_critere])) { $tableau_criteres_pour_bdd[] = $this->criteres[$nom_critere].'='.$valeur_critere; } } return $tableau_criteres_pour_bdd; } private function creerSousRequeteMotsCles($mot_cle) { $requete = ''; if (preg_match('/.*OU.*/', $mot_cle)) { $requete = $this->creerSousRequeteMotsClesOu($mot_cle); } else if (preg_match('/.*ET.*/', $mot_cle)) { $requete = $this->creerSousRequeteMotsClesEt($mot_cle); } else { // Construction de la requête $prerequete = "SELECT cmc_id_mot_cle_utilisateur, cmc_id_proprietaire FROM cel_mots_cles_images WHERE $where ". 'cmc_id_mot_cle_general = '.$this->proteger($this->encoderMotCle($mot_cle)); $elements = $this->executerRequete($prerequete); if ($elements != false && count($elements) > 0) { foreach ($elements as $occurence) { $requete .= '(ci_meta_mots_cles LIKE "%'.$occurence['cmc_id_mot_cle_utilisateur'].'%" AND ci_ce_utilisateur = '. $this->proteger($occurence['cmc_id_proprietaire']).' ) OR '; } } } $requete = rtrim($requete,' OR '); return $requete; } private function creerSousRequeteMotsClesOu($mot_cle) { $requete = ''; $tab_mots_cles = explode('OU', $mot_cle); $where = ''; foreach ($tab_mots_cles as $mot) { $where .= 'cmc_id_mot_cle_general = '.$this->proteger($this->encoderMotCle($mot)).' OR '; } $where = rtrim($where,' OR '); // Construction de la requête $prerequete = "SELECT cmc_id_mot_cle_utilisateur, cmc_id_proprietaire FROM cel_mots_cles_images WHERE $where "; $elements = $this->executerRequete($prerequete);//print_r($elements); if ($elements != false && count($elements) > 0) { foreach ($elements as $occurence) { $requete .= '(ci_meta_mots_cles LIKE "%'.$occurence['cmc_id_mot_cle_utilisateur'].'%" AND ci_ce_utilisateur = '. $this->proteger($occurence['cmc_id_proprietaire']).' ) OR '; } } return $requete; } private function creerSousRequeteMotsClesEt($mot_cle) { $requete = ''; $where = ''; $champs = 'a.cmc_id_proprietaire , '; $table = ''; $i = "a"; $j = "a"; $tab_mots_cles = explode("ET", $mot_cle); foreach ($tab_mots_cles as $mot) { $champs .= "$i.cmc_id_mot_cle_utilisateur as $i , "; $table .= "cel_mots_cles_images $i , "; $where .= "$i.cmc_id_mot_cle_general = ".$this->proteger($this->encoderMotCle($mot)).' AND '; if ($i !== "a") { $where .= " $i.cmc_id_proprietaire = ".$j.".cmc_id_proprietaire AND "; $j++; } $i++; } $where = rtrim($where,' AND '); $champs = rtrim($champs,' , '); $table = rtrim($table,' , '); // Construction de la requête $prerequete = "SELECT $champs FROM $table WHERE $where "; $elements = $this->executerRequete($prerequete);//print_r($elements); if ($elements != false && count($elements) > 0) { foreach ($elements as $occurence) { $requete = ' ('; for ($j = 'a'; $j < $i; $j++) { $requete .= 'ci_meta_mots_cles like "%'.$occurence[$j].'%" AND '; } $requete .= ' ci_ce_utilisateur = '.$this->proteger($occurence['cmc_id_proprietaire']).' ) OR '; } } return $requete; } } ?>