1,21 → 1,29 |
<?php |
// ATTENTION ! Classe compatible uniquement avec nouveau format de bdd du cel // |
/** |
* PHP Version 5 |
* |
* @category PHP |
* @package jrest |
* @author Aurélien Peronnet <aurelien@tela-botania.org> |
* @copyright 2010 Tela-Botanica |
* @license http://www.cecill.info/licences/Licence_CeCILL_V2-fr.txt Licence CECILL |
* @version SVN: <svn_id> |
* @link /doc/jrest/ |
*/ |
|
/** |
* in : utf8 |
* out : utf8 |
* |
* Service recherche d'images publique a partir de divers critères |
* |
*/ |
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'); |
const start_defaut = 0; |
const limit_defaut = 100; |
|
function InventoryImageListPublic($config) { |
|
parent::__construct($config); |
|
$this->start = 0; |
$this->limit = 100; |
$this->distinct = true; |
} |
|
function getRessource() { |
|
} |
22,292 → 30,37 |
|
function getElement($uid) |
{ |
$criteres = array() ; |
$criteres = array('transmission' => '1'); |
|
if($uid[0] == '*') { |
$criteres = array(); |
} else { |
|
if(!$this->estUneRechercheGenerale()) { |
|
$criteres = $this->traiterCriteres($_GET); |
|
if(empty($criteres)) { |
header("content-type: text/html"); |
if($uid[0] != '*' && empty($_GET)) { |
header("content-type: application/json"); |
$images_json = json_encode(array()); |
print $images_json; |
exit() ; |
} |
} |
} |
|
if(isset($_GET['start'])) { |
$this->start = $_GET['start']; |
} |
$this->start = isset($_GET['start']) ? $_GET['start'] : self::start_defaut; |
$this->limit = isset($_GET['limit']) ? $_GET['limit'] : self::limit_defaut; |
|
if(isset($_GET['limit'])) { |
$this->limit = $_GET['limit']; |
} |
$criteres['mots_cles'] = isset($_GET['tag']) ? $_GET['tag'] : null; |
$criteres['ce_utilisateur'] = isset($_GET['auteur']) ? $_GET['auteur'] : null; |
$criteres['zone_geo'] = isset($_GET['commune']) ? $_GET['commune'] : null; |
$criteres['taxon'] = isset($_GET['taxon']) ? $_GET['taxon'] : null; |
$criteres['ce_zone_geo'] = isset($_GET['dept']) ? $_GET['dept'] : null; |
|
$chercheur_images = new RechercheImage($this->config); |
|
// Construction de la requête |
$requete_debut = 'SELECT * '; |
$total = $chercheur_images->compterImages($criteres['ce_utilisateur'], $criteres); |
$images = $chercheur_images->rechercherImages($criteres['ce_utilisateur'], $criteres, $this->start, $this->limit); |
|
$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") ; |
header("content-type: application/json") ; |
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_meta_mots_cles") |
{ |
$requete .= $this->creerSousRequeteMotsCles($nom_valeur[1]); |
} |
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 .= $this->creerSousRequeteMotsCles($chaine_requete); |
$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 ').' AND '; |
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; |
} |
} |
?> |