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;

    }

}

?>