Subversion Repositories eFlore/Applications.cel

<?php
class InventoryImageListPublic extends Cel {
                
        private $criteres = array('tag' => '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;
    } 
}
?>