31,9 → 31,9 |
private $auteurs = array(); |
private $flux = array(); |
private $criteres = array( |
'utilisateur' => 'ce_utilisateur', |
'utilisateur' => 'courriel_utilisateur', |
'commune' => 'zone_geo', |
'dept' => 'id_zone_geo', |
'dept' => 'ce_zone_geo', |
'taxon' => 'nom_ret', |
'commentaire' => 'commentaire', |
'date' => 'date_observation', |
206,8 → 206,8 |
private function getServiceParDefaut() { |
// Construction de la requête |
$requete = (isset($this->distinct) ? 'SELECT DISTINCT' : 'SELECT').' * '. |
'FROM cel_inventory WHERE identifiant like "%@%" '. |
(($this->etreFluxAdmin()) ? '' : 'AND transmission = 1 '). |
'FROM cel_obs '. |
(($this->etreFluxAdmin()) ? '' : 'WHERE transmission = 1 '). |
'ORDER BY '.(isset($this->orderby) && (!is_null($this->orderby)) ? $this->orderby : 'date_modification DESC').' '. |
"LIMIT $this->start,$this->limit "; |
|
220,7 → 220,7 |
|
private function getServiceParMotsCles() { |
$infos=array(); |
$infos[0]['num_nom_sel'] = ''; |
$infos[0]['nom_sel_nn'] = ''; |
$infos[0]['date_modification'] = '2011-06-28'; |
$donnees = $this->construireDonneesCommunesAuFlux($infos); |
$donnees['items'][0]['guid'] = 0; |
238,7 → 238,7 |
|
private function getServiceParCommune() { |
$infos=array(); |
$infos[0]['num_nom_sel'] = ''; |
$infos[0]['nom_sel_nn'] = ''; |
$infos[0]['date_modification'] = '2011-06-28'; |
$donnees = $this->construireDonneesCommunesAuFlux($infos); |
$donnees['items'][0]['guid'] = 0; |
261,8 → 261,8 |
|
// Construction de la requête |
$requete = (isset($this->distinct) ? 'SELECT DISTINCT' : 'SELECT').' * '. |
'FROM cel_inventory '. |
'WHERE identifiant like "%@%" AND '.(($this->etreFluxAdmin()) ? '' : ' transmission = 1 AND '); |
'FROM cel_obs '. |
'WHERE 1 AND '.(($this->etreFluxAdmin()) ? '' : ' transmission = 1 AND '); |
|
if ($this->estUneRechercheGenerale()) { |
$chaine_requete = $_GET['recherche']; |
275,11 → 275,11 |
} |
$requete = rtrim($requete, 'AND '); |
$requete .= ' ORDER BY '.(isset($this->orderby) && (!is_null($this->orderby)) ? $this->orderby : |
'date_modification DESC, location ASC').' '. |
'date_modification DESC, zone_geo ASC').' '. |
"LIMIT $this->start,$this->limit "; |
|
$elements = $this->executerRequete($requete);//echo $requete; |
|
$elements = $this->executerRequete($requete); |
|
// Création du contenu |
if ($elements != false && count($elements) > 0) { |
$contenu = $this->executerService($elements); |
309,11 → 309,13 |
$nom_valeur[1] = $matches[3].'-'.$matches[2].'-'.$matches[1]; |
} |
$requete .= $nom_valeur[0].'='.$this->bdd->quote($nom_valeur[1]).' AND '; break; |
case "ce_zone_geo" : |
$requete .= ' ('.$nom_valeur[0].' LIKE "INSEE-C:'.$nom_valeur[1].'%") AND '; break; |
case "nom_ret" : |
if ($nom_valeur[1] == "indetermine") $nom_valeur[1] = 'null'; |
$requete .= ' ('.$nom_valeur[0].' LIKE "%'.$nom_valeur[1].'%" OR nom_sel LIKE "%'. |
$nom_valeur[1].'%") AND '; break; |
case "mots-cles" : $requete .= $this->creerSousRequeteMotsCles($nom_valeur[1]); break; |
case "mots-cles" : $requete .= $this->creerSousRequeteMotsCles($nom_valeur[1]).' AND '; break; |
default : $requete .= $nom_valeur[0].' = "'.$nom_valeur[1].'" AND '; break; |
} |
} |
323,88 → 325,25 |
} |
|
private function creerSousRequeteMotsCles($mot_cle) { |
$requete = 'mots_cles like "inexistant" OR'; |
$requete = ''; |
if (preg_match('/.*OU.*/', $mot_cle)) { |
$requete = $this->creerSousRequeteMotsClesOu($mot_cle); |
$mots_cles_tab = explode('OU',$mot_cle); |
foreach($mots_cles_tab as $mot_cle_item) { |
$requete .= '(mots_cles_texte LIKE '.$this->proteger('%'.$mot_cle_item.'%').') OR '; |
} |
$requete = '('.rtrim($requete,'OR ').') '; |
} else if (preg_match('/.*ET.*/', $mot_cle)) { |
$requete = $this->creerSousRequeteMotsClesEt($mot_cle); |
} else { |
// Construction de la requête |
$prerequete = 'SELECT * '. |
'FROM cel_mots_cles_obs '. |
'WHERE cmc_id_mot_cle_general = '.$this->bdd->quote($this->encoderMotCle($mot_cle)); |
$elements = $this->executerRequete($prerequete); |
if ($elements != false && count($elements) > 0) { |
$requete = ''; |
foreach ($elements as $occurence) { |
$requete .= '(mots_cles LIKE "%'.$occurence['cmc_id_mot_cle_utilisateur'].'%" AND identifiant = '. |
$this->bdd->quote($occurence['cmc_id_proprietaire']).' ) OR '; |
} |
$mots_cles_tab = explode('ET',$mot_cle); |
foreach($mots_cles_tab as $mot_cle_item) { |
$requete .= '(mots_cles_texte LIKE '.$this->proteger('%'.$mot_cle_item.'%').') AND '; |
} |
$requete = '('.rtrim($requete, 'AND ').') '; |
} else { |
$requete = "(mots_cles_texte LIKE ".$this->proteger('%'.$mot_cle.'%').') '; |
} |
$requete = rtrim($requete,' OR ').' AND '; |
return $requete; |
} |
|
private function creerSousRequeteMotsClesOu($mot_cle) { |
$requete = 'mots_cles like "inexistant" OR'; |
$tab_mots_cles = explode('OU', $mot_cle); |
$where = ''; |
foreach ($tab_mots_cles as $mot) { |
$where .= 'cmc_id_mot_cle_general = '.$this->bdd->quote($this->encoderMotCle(trim($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_obs WHERE $where "; |
$elements = $this->executerRequete($prerequete);//print_r($elements); |
if ($elements != false && count($elements) > 0) { |
$requete = ''; |
foreach ($elements as $occurence) { |
$requete .= '(mots_cles LIKE "%'.$occurence['cmc_id_mot_cle_utilisateur'].'%" AND identifiant = '. |
$this->bdd->quote($occurence['cmc_id_proprietaire']).' ) OR '; |
} |
} |
return $requete; |
} |
|
private function creerSousRequeteMotsClesEt($mot_cle) { |
|
$requete = 'mots_cles like "inexistant" OR'; |
$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_obs $i , "; |
$where .= "$i.cmc_id_mot_cle_general = ".$this->bdd->quote($this->encoderMotCle(trim($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) { |
$requete = ''; |
foreach ($elements as $occurence) { |
$requete = ' ('; |
for ($j = 'a'; $j < $i; $j++) { |
$requete .= 'mots_cles like "%'.$occurence[$j].'%" AND '; |
} |
$requete .= ' identifiant = '.$this->bdd->quote($occurence['cmc_id_proprietaire']).' ) OR '; |
} |
} |
return $requete; |
} |
|
private function traiterCriteresMultiples($tableau_criteres) { |
$tableau_criteres_pour_bdd = array(); |
|
426,11 → 365,15 |
' OR '. |
'nom_sel LIKE "'.$chaine_requete.'%"'. |
' OR '. |
'location LIKE "'.$chaine_requete.'%" '. |
'zone_geo LIKE "'.$chaine_requete.'%" '. |
' OR '. |
'id_location LIKE "'.$chaine_requete.'%" '. |
'ce_zone_geo LIKE "'.$chaine_requete.'%" '. |
' OR '. |
'identifiant LIKE "'.$chaine_requete.'%" '. |
'ce_zone_geo LIKE "INSEE-C:'.$chaine_requete.'%" '. |
' OR '. |
'courriel_utilisateur LIKE "'.$chaine_requete.'%" '. |
' OR '. |
'mots_cles_texte LIKE "'.$chaine_requete.'%" '. |
') '; |
} |
return $requete; |
446,7 → 389,7 |
// Prétraitement des données |
$donnees = $this->construireDonneesCommunesAuFlux($elements); |
foreach ($elements as $element) { |
$identifiants[$element['identifiant']] = $element['identifiant']; |
$identifiants[$element['ce_utilisateur']] = $element['ce_utilisateur']; |
} |
$this->auteurs = $this->creerAuteurs($identifiants); |
foreach ($elements as $element) { |
493,14 → 436,14 |
$item['categorie'] = $this->creerCategorie($item); |
$item['description'] = $this->creerDescription($this->protegerCaracteresHtmlDansChamps($observation), $item); |
$item['description_encodee'] = htmlspecialchars($this->creerDescription($observation, $item)); |
$item['modifier_par'] = $this->auteurs[$observation['identifiant']]; |
$item['modifier_par'] = $observation['id_observation']; |
return $item; |
} |
|
private function creerTitre($obs) { |
$nom_plante = $obs['nom_sel'].' [nn'.$obs['num_nom_sel'].']'; |
$lieu = $obs['location'].' ('.$obs['id_location'].')'; |
$utilisateur = $this->auteurs[$obs['identifiant']]; |
$nom_plante = $obs['nom_sel'].' [nn'.$obs['nom_sel_nn'].']'; |
$lieu = $obs['zone_geo'].' ('.$obs['ce_zone_geo'].')'; |
$utilisateur = $obs['prenom_utilisateur'].' '.$obs['nom_utilisateur']; |
$titre = "$nom_plante à $lieu par $utilisateur"; |
$titre = $this->nettoyerTexte($titre); |
return $titre; |
507,30 → 450,30 |
} |
|
private function creerGuidItem($element) { |
$guid = sprintf($this->config['settings']['guidObsTpl'], $element['id']); |
$guid = sprintf($this->config['settings']['guidObsTpl'], $element['id_observation']); |
return $guid; |
} |
|
private function creerLienItem($element) { |
$lien = null; |
if ($element['num_nom_sel'] != 0) { |
$lien = sprintf($this->config['settings']['efloreUrlTpl'], urlencode($element['num_nom_sel']), 'cel'); |
if ($element['nom_sel_nn'] != 0) { |
$lien = sprintf($this->config['settings']['efloreUrlTpl'], urlencode($element['nom_sel_nn']), 'cel'); |
} |
return $lien; |
} |
|
private function creerDescription($obs, $item) { |
$id_obs = $obs['id']; |
$id_obs = $obs['id_observation']; |
$famille = $obs['famille']; |
$nom_saisi = $obs['nom_sel']; |
$nom_retenu = $obs['nom_ret']; |
$auteur = $this->auteurs[$obs['identifiant']]; |
$auteur_mail = $obs['identifiant']; |
$mots_cles_obs = $this->decoderMotsClesObs($obs['identifiant'], $obs['mots_cles']); |
$lien_correction = sprintf($this->config['settings']['phpEditUrlTpl'], $obs['id']); |
$lieu = $obs['location'].' ('.$obs['id_location'].') > '.$obs['lieudit'].' > '.$obs['station']; |
$auteur = $obs['prenom_utilisateur'].' '.$obs['nom_utilisateur']; |
$auteur_mail = $obs['courriel_utilisateur']; |
$mots_cles_obs = $obs['mots_cles_texte']; |
$lien_correction = sprintf($this->config['settings']['phpEditUrlTpl'], $obs['id_observation']); |
$lieu = $obs['zone_geo'].' ('.$this->convertirCodeZoneGeoVersCodeInsee($obs['ce_zone_geo']).') > '.$obs['lieudit'].' > '.$obs['station']; |
$milieu = $obs['milieu']; |
$coordonnees = ($this->etreNull($obs['coord_x']) && $this->etreNull($obs['coord_y'])) ? '' : $obs['coord_x'].'/'.$obs['coord_y']; |
$coordonnees = ($this->etreNull($obs['latitude']) && $this->etreNull($obs['longitude'])) ? '' : $obs['latitude'].'/'.$obs['longitude']; |
$commentaire = $obs['commentaire']; |
$date_observation = $this->formaterDate($obs['date_observation'], '%A %d %B %Y'); |
$date_transmission = $this->formaterDate($obs['date_transmission']); |
548,7 → 491,7 |
'<li>'.'Milieu : '.$milieu.'</li>'. |
(($this->etreFluxAdmin()) ? '<li>Coordonnées (Lat/Long) : '.$coordonnees.'</li>' : ''). |
'<li>'.'Commentaire : '.$commentaire.'</li>'. |
'<li>'.'Mots-clés : '.implode(', ', $mots_cles_obs).'</li>'. |
'<li>'.'Mots-clés : '.$mots_cles_obs.'</li>'. |
(($this->etreFluxAdmin()) ? '<li>Transmis (= public) : '.$transmission.'</li>' : ''). |
'<li>Modifiée le : '.$date_modification.'</li>'. |
'<li>Créée le : '.$date_creation.'</li>'. |