Line 7... |
Line 7... |
7 |
*
|
7 |
*
|
8 |
* Cas d'utilisation :
|
8 |
* Cas d'utilisation :
|
9 |
* /CelWidgetMap/Carte/Utilisateur : carte des observations publiques d'un utilisateur.
|
9 |
* /CelWidgetMap/Carte/Utilisateur : carte des observations publiques d'un utilisateur.
|
10 |
* /CelWidgetMap/Carte/Utilisateur/Projet : carte des observations publiques d'un utilisateur pour un projet.
|
10 |
* /CelWidgetMap/Carte/Utilisateur/Projet : carte des observations publiques d'un utilisateur pour un projet.
|
11 |
* /CelWidgetMap/Carte/Utilisateur/Projet/dept : carte des observations publiques d'un utilisateur pour un projet sur un département.
|
11 |
* /CelWidgetMap/Carte/Utilisateur/Projet/dept : carte des observations publiques d'un utilisateur pour un projet sur un département.
|
12 |
* /CelWidgetMap/Carte/Utilisateur/Projet/dept/num_taxon : carte des observations publiques d'un utilisateur pour un projet sur un département pour un taxon.
|
12 |
* /CelWidgetMap/Carte/Utilisateur/Projet/dept/nt : carte des observations publiques d'un utilisateur pour un projet sur un département pour un taxon.
|
13 |
*
|
13 |
*
|
14 |
* Carte = Type de carte. Valeurs possible : defaut,
|
14 |
* Carte = Type de carte. Valeurs possible : defaut,
|
15 |
* Utilisateur = identifiant (= courriel) de l'utilisateur ou * pour tous les utilisateurs.
|
15 |
* Utilisateur = identifiant (= courriel) de l'utilisateur ou * pour tous les utilisateurs.
|
16 |
* Projet = mot-clé du projet
|
16 |
* Projet = mot-clé du projet
|
17 |
*
|
17 |
*
|
Line 93... |
Line 93... |
93 |
* Les stations de la carte par défaut
|
93 |
* Les stations de la carte par défaut
|
94 |
*/
|
94 |
*/
|
95 |
public function getStations($params) {
|
95 |
public function getStations($params) {
|
96 |
$stations = null;
|
96 |
$stations = null;
|
97 |
$requete = 'SELECT wgs84_latitude AS lat, wgs84_longitude AS lng, location AS nom '.
|
97 |
$requete = 'SELECT wgs84_latitude AS lat, wgs84_longitude AS lng, location AS nom '.
|
98 |
'FROM cel_inventory AS i '.
|
98 |
'FROM cel_obs AS co '.
|
99 |
' LEFT JOIN locations AS l '.
|
99 |
' LEFT JOIN cel_zones_geo AS l '.
|
100 |
' ON (l.name = i.location AND l.code = i.id_location) '.
|
100 |
' ON (l.nom = co.zone_geo AND l.id_zone_geo = co.ce_zone_geo) '.
|
101 |
"WHERE transmission = '1' ".
|
101 |
"WHERE transmission = '1' ".
|
102 |
" AND (( ".
|
102 |
" AND (( ".
|
103 |
" (coord_x = '' OR coord_x IS NULL OR coord_x = '000null') ".
|
103 |
" (latitude = '' OR latitude IS NULL OR latitude = '000null') ".
|
104 |
" AND (coord_y = '' OR coord_y IS NULL OR coord_y = '000null') ".
|
104 |
" AND (longitude = '' OR longitude IS NULL OR longitude = '000null') ".
|
105 |
" ) OR mots_cles_texte LIKE '%sensible%') ".
|
105 |
" ) OR mots_cles_texte LIKE '%sensible%') ".
|
106 |
$this->construireWhereRectangleCommune().
|
106 |
$this->construireWhereRectangleCommune().
|
107 |
$this->construireWhereDept().
|
107 |
$this->construireWhereDept().
|
108 |
$this->construireWhereCommune().
|
108 |
$this->construireWhereCommune().
|
109 |
$this->construireWhereUtilisateur().
|
109 |
$this->construireWhereUtilisateur().
|
Line 114... |
Line 114... |
114 |
$this->construireWhereProjet().
|
114 |
$this->construireWhereProjet().
|
115 |
$this->construireWhereTag();
|
115 |
$this->construireWhereTag();
|
Line 116... |
Line 116... |
116 |
|
116 |
|
Line 117... |
Line 117... |
117 |
$resultats_communes = $this->requeter($requete);
|
117 |
$resultats_communes = $this->requeter($requete);
|
118 |
|
118 |
|
119 |
$requete = 'SELECT coord_x AS lat, coord_y AS lng, location AS nom '.
|
119 |
$requete = 'SELECT latitude AS lat, longitude AS lng, location AS nom '.
|
120 |
'FROM cel_inventory AS i '.
|
120 |
'FROM cel_obs AS co '.
|
121 |
' LEFT JOIN locations AS l '.
|
121 |
' LEFT JOIN cel_zones_geo AS l '.
|
122 |
' ON (l.name = i.location AND l.code = i.id_location) '.
|
122 |
' ON (l.nom = co.zone_geo AND l.id_zone_geo = co.ce_zone_geo) '.
|
123 |
"WHERE transmission = '1' ".
|
123 |
"WHERE transmission = '1' ".
|
124 |
" AND coord_x != '' AND coord_x IS NOT NULL AND coord_x != '000null' ".
|
124 |
" AND latitude != '' AND latitude IS NOT NULL AND latitude != '000null' ".
|
125 |
" AND coord_y != '' AND coord_y IS NOT NULL AND coord_y != '000null' ".
|
125 |
" AND longitude != '' AND longitude IS NOT NULL AND longitude != '000null' ".
|
126 |
" AND (mots_cles_texte IS NULL OR mots_cles_texte NOT LIKE '%sensible%') ".
|
126 |
" AND (mots_cles_texte IS NULL OR mots_cles_texte NOT LIKE '%sensible%') ".
|
127 |
$this->construireWhereRectangleStation().
|
127 |
$this->construireWhereRectangleStation().
|
128 |
$this->construireWhereDept().
|
128 |
$this->construireWhereDept().
|
Line 142... |
Line 142... |
142 |
}
|
142 |
}
|
Line 143... |
Line 143... |
143 |
|
143 |
|
Line 144... |
Line 144... |
144 |
public function getTout($params) {
|
144 |
public function getTout($params) {
|
145 |
|
145 |
|
Line 146... |
Line 146... |
146 |
$emplacements = null;
|
146 |
$emplacements = null;
|
147 |
$concatenation_id = 'CONCAT(coord_x,coord_y, wgs84_latitude,wgs84_longitude) ';
|
147 |
$concatenation_id = 'CONCAT(latitude,longitude, wgs84_latitude,wgs84_longitude) ';
|
148 |
|
148 |
|
149 |
$requete = 'SELECT DISTINCT *, location AS nom, '.
|
149 |
$requete = 'SELECT DISTINCT *, zone_geo AS nom, '.
|
150 |
"mots_cles_texte, ".
|
150 |
"mots_cles_texte, ".
|
151 |
"coord_x, ".
|
151 |
"latitude, ".
|
152 |
"wgs84_latitude, ".
|
152 |
"wgs84_latitude, ".
|
153 |
"coord_y, ".
|
153 |
"longitude, ".
|
154 |
"wgs84_longitude, ".
|
154 |
"wgs84_longitude, ".
|
155 |
$concatenation_id." as id_coord ".
|
155 |
$concatenation_id." as id_coord ".
|
156 |
'FROM cel_inventory AS i '.
|
156 |
'FROM cel_obs AS co '.
|
157 |
' LEFT JOIN locations AS l '.
|
157 |
' LEFT JOIN cel_zones_geo AS l '.
|
158 |
' ON (l.name = i.location AND l.code = i.id_location) '.
|
158 |
' ON (l.nom = co.zone_geo AND l.id_zone_geo = co.ce_zone_geo) '.
|
159 |
"WHERE transmission = '1' ".
|
159 |
"WHERE transmission = '1' ".
|
160 |
" AND (".
|
160 |
" AND (".
|
Line 167... |
Line 167... |
167 |
$this->construireWhereNomTaxon().
|
167 |
$this->construireWhereNomTaxon().
|
168 |
$this->construireWhereDate().
|
168 |
$this->construireWhereDate().
|
169 |
$this->construireWhereCommentaire().
|
169 |
$this->construireWhereCommentaire().
|
170 |
$this->construireWhereProjet().
|
170 |
$this->construireWhereProjet().
|
171 |
$this->construireWhereTag().' GROUP BY id_coord';
|
171 |
$this->construireWhereTag().' GROUP BY id_coord';
|
- |
|
172 |
//echo $requete;exit;
|
172 |
$resultats_emplacements = $this->executerRequete($requete);
|
173 |
$resultats_emplacements = $this->executerRequete($requete);
|
173 |
$emplacements = $this->traiterEmplacements($resultats_emplacements, $this->compterObservations($params));
|
174 |
$emplacements = $this->traiterEmplacements($resultats_emplacements, $this->compterObservations($params));
|
174 |
return $emplacements;
|
175 |
return $emplacements;
|
175 |
}
|
176 |
}
|
Line 183... |
Line 184... |
183 |
|
184 |
|
Line 184... |
Line 185... |
184 |
private $nb_obs = 0;
|
185 |
private $nb_obs = 0;
|
185 |
|
186 |
|
186 |
private function compterObservations($params) {
|
187 |
private function compterObservations($params) {
|
187 |
$requete = 'SELECT COUNT(*) as nb '.
|
188 |
$requete = 'SELECT COUNT(*) as nb '.
|
188 |
'FROM cel_inventory AS i '.
|
189 |
'FROM cel_obs AS co '.
|
189 |
' LEFT JOIN locations AS l '.
|
190 |
' LEFT JOIN cel_zones_geo AS l '.
|
190 |
' ON (l.name = i.location AND l.code = i.id_location) '.
|
191 |
' ON (l.nom = co.zone_geo AND l.id_zone_geo = co.ce_zone_geo) '.
|
191 |
"WHERE transmission = '1' ".
|
192 |
"WHERE transmission = '1' ".
|
192 |
" AND (".
|
193 |
" AND (".
|
193 |
$this->construireWhereRectangleStationOR()." OR ".
|
194 |
$this->construireWhereRectangleStationOR()." OR ".
|
Line 199... |
Line 200... |
199 |
$this->construireWhereNomTaxon().
|
200 |
$this->construireWhereNomTaxon().
|
200 |
$this->construireWhereDate().
|
201 |
$this->construireWhereDate().
|
201 |
$this->construireWhereCommentaire().
|
202 |
$this->construireWhereCommentaire().
|
202 |
$this->construireWhereProjet().
|
203 |
$this->construireWhereProjet().
|
203 |
$this->construireWhereTag();
|
204 |
$this->construireWhereTag();
|
204 |
|
- |
|
- |
|
205 |
//echo $requete;exit;
|
205 |
$resultats_nb_obs = $this->executerRequete($requete);
|
206 |
$resultats_nb_obs = $this->executerRequete($requete);
|
206 |
return $resultats_nb_obs[0]['nb'];
|
207 |
return $resultats_nb_obs[0]['nb'];
|
207 |
}
|
208 |
}
|
Line 208... |
Line 209... |
208 |
|
209 |
|
Line 270... |
Line 271... |
270 |
if ($stations !== false) {
|
271 |
if ($stations !== false) {
|
271 |
foreach ($stations as $station) {
|
272 |
foreach ($stations as $station) {
|
272 |
if (is_numeric($station['lat']) && is_numeric($station['lng'])) {
|
273 |
if (is_numeric($station['lat']) && is_numeric($station['lng'])) {
|
273 |
extract($station);
|
274 |
extract($station);
|
274 |
$id = self::MARQUEUR_STATION.':'.$lat.'|'.$lng;
|
275 |
$id = self::MARQUEUR_STATION.':'.$lat.'|'.$lng;
|
275 |
//$this->definirLatLngMaxMin($marqueurs, $lat, $lng);
|
- |
|
276 |
$lata = round($lat, 5);
|
276 |
$lata = round($lat, 5);
|
277 |
$lnga = round($lng, 5);
|
277 |
$lnga = round($lng, 5);
|
278 |
$nom = $this->etreNull($nom) ? $lata.','.$lnga : $nom;
|
278 |
$nom = $this->etreNull($nom) ? $lata.','.$lnga : $nom;
|
Line 279... |
Line 279... |
279 |
|
279 |
|
Line 364... |
Line 364... |
364 |
*/
|
364 |
*/
|
365 |
public function getObservations($params) {
|
365 |
public function getObservations($params) {
|
366 |
$resultats = array();
|
366 |
$resultats = array();
|
367 |
$total = 0;
|
367 |
$total = 0;
|
368 |
if (isset($this->parametres['station']) && !$this->etreNull($this->parametres['station'])) {
|
368 |
if (isset($this->parametres['station']) && !$this->etreNull($this->parametres['station'])) {
|
369 |
$requete = 'SELECT SQL_CALC_FOUND_ROWS id, identifiant, nom_sel, nom_ret, num_nom_sel, num_nom_ret, num_taxon, famille, '.
|
369 |
$requete = 'SELECT SQL_CALC_FOUND_ROWS id_observation, ce_utilisateur, courriel_utilisateur, nom_utilisateur, prenom_utilisateur, '.
|
- |
|
370 |
' nom_sel, nom_ret, nom_sel_nn, nom_ret_nn, nt, famille, '.
|
370 |
' lieudit, location, date_observation, milieu, commentaire, '.
|
371 |
' lieudit, zone_geo, date_observation, milieu, commentaire, '.
|
371 |
' sector, x_utm, y_utm, insee_code, date_transmission '.
|
372 |
' utm_secteur, utm_x, utm_y, code, date_transmission '.
|
372 |
'FROM cel_inventory AS i '.
|
373 |
'FROM cel_obs AS co '.
|
373 |
' LEFT JOIN locations AS l '.
|
374 |
' LEFT JOIN cel_zones_geo AS l '.
|
374 |
" ON (l.name = i.location AND l.code = i.id_location) ".
|
375 |
" ON (l.nom = co.zone_geo AND l.id_zone_geo = co.ce_zone_geo) ".
|
375 |
"WHERE transmission = '1' ".
|
376 |
"WHERE transmission = '1' ".
|
376 |
(($this->communeEstDemandee()) ? $this->construireWhereCommuneSansCoordonneesAvecSensibles() : $this->construireWhereCoordonneesSansSensibles()).
|
377 |
(($this->communeEstDemandee()) ? $this->construireWhereCommuneSansCoordonneesAvecSensibles() : $this->construireWhereCoordonneesSansSensibles()).
|
377 |
$this->construireWhereDept().
|
378 |
$this->construireWhereDept().
|
378 |
$this->construireWhereUtilisateur().
|
379 |
$this->construireWhereUtilisateur().
|
379 |
$this->construireWhereNumTaxon().
|
380 |
$this->construireWhereNumTaxon().
|
Line 382... |
Line 383... |
382 |
$this->construireWhereCommentaire().
|
383 |
$this->construireWhereCommentaire().
|
383 |
$this->construireWhereProjet().
|
384 |
$this->construireWhereProjet().
|
384 |
$this->construireWhereTag().
|
385 |
$this->construireWhereTag().
|
385 |
'ORDER BY nom_sel ASC '.
|
386 |
'ORDER BY nom_sel ASC '.
|
386 |
"LIMIT {$this->start},{$this->limit} ";
|
387 |
"LIMIT {$this->start},{$this->limit} ";
|
387 |
|
- |
|
- |
|
388 |
//echo $requete;exit;
|
388 |
$resultats = $this->requeter($requete, self::SQL_RETOUR_COMPLET, self::SQL_MODE_OBJET);
|
389 |
$resultats = $this->requeter($requete, self::SQL_RETOUR_COMPLET, self::SQL_MODE_OBJET);
|
389 |
$requete = 'SELECT FOUND_ROWS()';
|
390 |
$requete = 'SELECT FOUND_ROWS()';
|
390 |
$total = (int) $this->requeter($requete, self::SQL_RETOUR_COLONNE);
|
391 |
$total = (int) $this->requeter($requete, self::SQL_RETOUR_COLONNE);
|
391 |
}
|
392 |
}
|
Line 402... |
Line 403... |
402 |
private function traiterObservations($donnees, $total) {
|
403 |
private function traiterObservations($donnees, $total) {
|
403 |
$observations = array('commune' => '', 'observations' => array(), 'observateurs' => array());
|
404 |
$observations = array('commune' => '', 'observations' => array(), 'observateurs' => array());
|
404 |
$observations['total'] = (isset($total)) ? $total : 0;
|
405 |
$observations['total'] = (isset($total)) ? $total : 0;
|
405 |
if (is_array($donnees) && count($donnees) > 0) {
|
406 |
if (is_array($donnees) && count($donnees) > 0) {
|
406 |
foreach ($donnees as $donnee) {
|
407 |
foreach ($donnees as $donnee) {
|
- |
|
408 |
//echo '<pre>'.print_r($donnee,true).'</pre>';exit;
|
407 |
$observation = array();
|
409 |
$observation = array();
|
408 |
$observation['idObs'] = $donnee->id;
|
410 |
$observation['idObs'] = $donnee->id_observation;
|
409 |
$observation['nn'] = $this->etreNull($donnee->num_nom_sel) ? null : $donnee->num_nom_sel;
|
411 |
$observation['nn'] = $this->etreNull($donnee->nom_sel_nn) ? null : $donnee->nom_sel_nn;
|
410 |
$observation['nomSci'] = $this->nettoyerTexte($donnee->nom_sel);
|
412 |
$observation['nomSci'] = $this->nettoyerTexte($donnee->nom_sel);
|
411 |
$observation['date'] = $this->formaterDate($donnee->date_observation, '%d/%m/%Y');
|
413 |
$observation['date'] = $this->formaterDate($donnee->date_observation, '%d/%m/%Y');
|
412 |
$observation['datePubli'] = $this->formaterDate($donnee->date_transmission);
|
414 |
$observation['datePubli'] = $this->formaterDate($donnee->date_transmission);
|
413 |
$observation['lieu'] = $this->traiterLieu($donnee);
|
415 |
$observation['lieu'] = $this->traiterLieu($donnee);
|
- |
|
416 |
$observation['observateur'] = $donnee->courriel_utilisateur;
|
414 |
$observation['observateur'] = $donnee->identifiant;
|
417 |
$observation['observateurId'] = $donnee->ce_utilisateur;
|
415 |
$observation['urlEflore'] = $this->getUrlEflore($donnee->num_nom_sel);
|
418 |
$observation['urlEflore'] = $this->getUrlEflore($donnee->nom_sel_nn);
|
Line 416... |
Line 419... |
416 |
|
419 |
|
417 |
if (isset($donnee->location)) {
|
420 |
if (isset($donnee->zone_geo)) {
|
418 |
$observations['commune'] = $this->nettoyerTexte($donnee->location);
|
421 |
$observations['commune'] = $this->nettoyerTexte($donnee->zone_geo);
|
419 |
}
|
422 |
}
|
Line 420... |
Line 423... |
420 |
$observations['observations'][$donnee->id] = $observation;
|
423 |
$observations['observations'][$donnee->id_observation] = $observation;
|
421 |
|
424 |
|
422 |
if (! array_key_exists($donnee->identifiant, $observations['observateurs'])) {
|
425 |
if (! array_key_exists($donnee->ce_utilisateur, $observations['observateurs'])) {
|
423 |
$observations['observateurs'][$donnee->identifiant] = $donnee->identifiant;
|
426 |
$observations['observateurs'][$donnee->courriel_utilisateur] = $donnee->courriel_utilisateur;
|
424 |
}
|
427 |
}
|
425 |
}
|
428 |
}
|
426 |
}
|
429 |
}
|
Line 489... |
Line 492... |
489 |
private function ajouterAuteursAuxObs($observations) {
|
492 |
private function ajouterAuteursAuxObs($observations) {
|
490 |
$observateurs = $this->recupererUtilisateursIdentite(array_keys($observations['observateurs']));
|
493 |
$observateurs = $this->recupererUtilisateursIdentite(array_keys($observations['observateurs']));
|
491 |
unset($observations['observateurs']);
|
494 |
unset($observations['observateurs']);
|
492 |
foreach ($observations['observations'] as $id => $infos) {
|
495 |
foreach ($observations['observations'] as $id => $infos) {
|
493 |
$courriel = $infos['observateur'];
|
496 |
$courriel = $infos['observateur'];
|
- |
|
497 |
if(isset($observateurs[$courriel])) {
|
494 |
$infos['observateur'] = $observateurs[$courriel]['intitule'];
|
498 |
$infos['observateur'] = $observateurs[$courriel]['intitule'];
|
495 |
$infos['observateurId'] = $observateurs[$courriel]['id'];
|
499 |
$infos['observateurId'] = $observateurs[$courriel]['id'];
|
- |
|
500 |
}
|
496 |
$observations['observations'][$id] = $infos;
|
501 |
$observations['observations'][$id] = $infos;
|
497 |
}
|
502 |
}
|
498 |
return $observations;
|
503 |
return $observations;
|
499 |
}
|
504 |
}
|
Line 512... |
Line 517... |
512 |
* Liste des taxons présents sur la carte
|
517 |
* Liste des taxons présents sur la carte
|
513 |
*/
|
518 |
*/
|
514 |
public function getTaxons($params) {
|
519 |
public function getTaxons($params) {
|
515 |
$json = null;
|
520 |
$json = null;
|
Line 516... |
Line 521... |
516 |
|
521 |
|
517 |
$requete = 'SELECT SQL_CALC_FOUND_ROWS DISTINCT nom_ret, num_nom_ret, num_taxon, famille '.
|
522 |
$requete = 'SELECT SQL_CALC_FOUND_ROWS DISTINCT nom_ret, nom_ret_nn, nt, famille '.
|
518 |
'FROM cel_inventory AS i '.
|
523 |
'FROM cel_obs AS co '.
|
519 |
' LEFT JOIN locations AS l '.
|
524 |
' LEFT JOIN cel_zones_geo AS l '.
|
520 |
' ON (l.name = i.location AND l.code = i.id_location) '.
|
525 |
' ON (l.nom = co.zone_geo AND l.id_zone_geo = co.ce_zone_geo) '.
|
521 |
"WHERE transmission = '1' ".
|
526 |
"WHERE transmission = '1' ".
|
522 |
" AND nom_ret != '' ".
|
527 |
" AND nom_ret != '' ".
|
523 |
$this->construireWhereDept().
|
528 |
$this->construireWhereDept().
|
524 |
$this->construireWhereCommune().
|
529 |
$this->construireWhereCommune().
|
Line 531... |
Line 536... |
531 |
$this->construireWhereTag().
|
536 |
$this->construireWhereTag().
|
532 |
'ORDER BY nom_ret ASC '.
|
537 |
'ORDER BY nom_ret ASC '.
|
533 |
"LIMIT {$this->start},{$this->limit} ";
|
538 |
"LIMIT {$this->start},{$this->limit} ";
|
534 |
//$this->debug[] = $requete;
|
539 |
//$this->debug[] = $requete;
|
535 |
$resultats = $this->requeter($requete, self::SQL_RETOUR_COMPLET, self::SQL_MODE_OBJET);
|
540 |
$resultats = $this->requeter($requete, self::SQL_RETOUR_COMPLET, self::SQL_MODE_OBJET);
|
536 |
|
- |
|
- |
|
541 |
//echo $requete;exit;
|
537 |
$requete = 'SELECT FOUND_ROWS()';
|
542 |
$requete = 'SELECT FOUND_ROWS()';
|
538 |
$taxons['total'] = (int) $this->requeter($requete, self::SQL_RETOUR_COLONNE);
|
543 |
$taxons['total'] = (int) $this->requeter($requete, self::SQL_RETOUR_COLONNE);
|
Line 539... |
Line 544... |
539 |
|
544 |
|
540 |
// Post-traitement
|
545 |
// Post-traitement
|
Line 545... |
Line 550... |
545 |
|
550 |
|
546 |
private function traiterTaxons($donnees) {
|
551 |
private function traiterTaxons($donnees) {
|
547 |
$taxons = array();
|
552 |
$taxons = array();
|
548 |
if (is_array($donnees) && count($donnees) > 0) {
|
553 |
if (is_array($donnees) && count($donnees) > 0) {
|
549 |
foreach ($donnees as $donnee) {
|
554 |
foreach ($donnees as $donnee) {
|
550 |
if (!isset($taxons[$donnee->num_taxon]) && ! $this->etreNull($donnee->nom_ret)) {
|
555 |
if (!isset($taxons[$donnee->nt]) && ! $this->etreNull($donnee->nom_ret)) {
|
551 |
$taxon = array();
|
556 |
$taxon = array();
|
552 |
$taxon['nn'] = $donnee->num_nom_ret;
|
557 |
$taxon['nn'] = $donnee->nom_ret_nn;
|
553 |
$taxon['nt'] = $donnee->num_taxon;
|
558 |
$taxon['nt'] = $donnee->nt;
|
554 |
$taxon['nom'] = $this->nettoyerTexte($donnee->nom_ret);
|
559 |
$taxon['nom'] = $this->nettoyerTexte($donnee->nom_ret);
|
555 |
$taxon['famille'] = $this->nettoyerTexte($donnee->famille);
|
560 |
$taxon['famille'] = $this->nettoyerTexte($donnee->famille);
|
556 |
$taxons[$donnee->num_taxon] = $taxon;
|
561 |
$taxons[$donnee->nt] = $taxon;
|
557 |
}
|
562 |
}
|
558 |
}
|
563 |
}
|
559 |
}
|
564 |
}
|
560 |
$taxons = array_values($taxons);
|
565 |
$taxons = array_values($taxons);
|
Line 569... |
Line 574... |
569 |
if ($type == self::MARQUEUR_COMMUNE) {
|
574 |
if ($type == self::MARQUEUR_COMMUNE) {
|
570 |
$lat = $this->proteger($lat);
|
575 |
$lat = $this->proteger($lat);
|
571 |
$lng = $this->proteger($lng);
|
576 |
$lng = $this->proteger($lng);
|
572 |
$sql = " AND (ROUND(wgs84_latitude,5) = ROUND($lat,5) AND ROUND(wgs84_longitude,5) = ROUND($lng,5)) ";
|
577 |
$sql = " AND (ROUND(wgs84_latitude,5) = ROUND($lat,5) AND ROUND(wgs84_longitude,5) = ROUND($lng,5)) ";
|
573 |
} else if ($type == self::MARQUEUR_STATION) {
|
578 |
} else if ($type == self::MARQUEUR_STATION) {
|
574 |
$lat = $this->proteger($lat);
|
579 |
$lat = $this->proteger($lat.'%');
|
575 |
$lng = $this->proteger($lng);
|
580 |
$lng = $this->proteger($lng.'%');
|
576 |
$sql = " AND (coord_x = $lat AND coord_y = $lng) ";
|
581 |
$sql = " AND (latitude LIKE $lat AND longitude LIKE $lng) ";
|
577 |
}
|
582 |
}
|
578 |
}
|
583 |
}
|
579 |
return $sql;
|
584 |
return $sql;
|
580 |
}
|
585 |
}
|
Line 587... |
Line 592... |
587 |
if ($type == self::MARQUEUR_COMMUNE) {
|
592 |
if ($type == self::MARQUEUR_COMMUNE) {
|
588 |
$lat = $this->proteger($lat);
|
593 |
$lat = $this->proteger($lat);
|
589 |
$lng = $this->proteger($lng);
|
594 |
$lng = $this->proteger($lng);
|
590 |
$sql = " AND (ROUND(wgs84_latitude,5) = ROUND($lat,5) AND ROUND(wgs84_longitude,5) = ROUND($lng,5)) ";
|
595 |
$sql = " AND (ROUND(wgs84_latitude,5) = ROUND($lat,5) AND ROUND(wgs84_longitude,5) = ROUND($lng,5)) ";
|
591 |
} else if ($type == self::MARQUEUR_STATION) {
|
596 |
} else if ($type == self::MARQUEUR_STATION) {
|
592 |
$lat = $this->proteger($lat);
|
597 |
$lat = $this->proteger($lat.'%');
|
593 |
$lng = $this->proteger($lng);
|
598 |
$lng = $this->proteger($lng.'%');
|
594 |
$sql = " AND (coord_x = $lat AND coord_y = $lng) ";
|
599 |
$sql = " AND (latitude LIKE $lat AND longitude LIKE $lng) ";
|
595 |
}
|
600 |
}
|
596 |
}
|
601 |
}
|
597 |
$sql .= ' AND (mots_cles_texte IS NULL OR mots_cles_texte NOT LIKE "%sensible%" ) ';
|
602 |
$sql .= ' AND (mots_cles_texte IS NULL OR mots_cles_texte NOT LIKE "%sensible%" ) ';
|
598 |
return $sql;
|
603 |
return $sql;
|
599 |
}
|
604 |
}
|
Line 757... |
Line 762... |
757 |
* Récupération des identifiant d'utilisateur et des ordres des observations correspondant à une date.
|
762 |
* Récupération des identifiant d'utilisateur et des ordres des observations correspondant à une date.
|
758 |
* Retour sous forme de tableau : array[identifiant] = array(ordre, ordre...);
|
763 |
* Retour sous forme de tableau : array[identifiant] = array(ordre, ordre...);
|
759 |
*/
|
764 |
*/
|
760 |
private function obtenirObsLieesImg($type, $param) {
|
765 |
private function obtenirObsLieesImg($type, $param) {
|
761 |
// Construction de la requête
|
766 |
// Construction de la requête
|
762 |
$requete = 'SELECT DISTINCT coi_ce_observation AS ordre, coi_ce_utilisateur AS utilisateur '.
|
767 |
$requete = 'SELECT DISTINCT co.id_obs, ci.ce_utilisateur AS utilisateur '.
|
763 |
'FROM cel_images '.
|
768 |
'FROM cel_images '.
|
764 |
' LEFT JOIN cel_obs_images '.
|
769 |
' LEFT JOIN cel_obs_images coi '.
|
765 |
' ON (ci_id_image = coi_ce_image) '.
|
770 |
' ON (ci.id_image = coi.id_image) '.
|
766 |
' LEFT JOIN cel_inventory AS i '.
|
771 |
' LEFT JOIN cel_obs AS co '.
|
767 |
' ON (coi_ce_utilisateur = i.identifiant AND coi_ce_observation = i.ordre) '.
|
772 |
' ON (coi.id_observation = co.id_observation) '.
|
768 |
' LEFT JOIN locations AS l '.
|
773 |
' LEFT JOIN cel_zones_geo AS l '.
|
769 |
' ON (l.name = i.location AND l.code = i.id_location) '.
|
774 |
' ON (l.nom = co.zone_geo AND l.id_zone_geo = co.ce_zone_geo) '.
|
770 |
"WHERE transmission = '1' ".
|
775 |
"WHERE transmission = '1' ".
|
771 |
($type == 'date.photo' ? " AND (ci_meta_date_time LIKE ".str_replace('-', ':', $param)." OR ci_meta_date LIKE $param) " : '').
|
776 |
($type == 'date.photo' ? " AND (ci_meta_date_time LIKE ".str_replace('-', ':', $param)." OR ci_meta_date LIKE $param) " : '').
|
772 |
($type == 'date.ajout' ? " AND ci_meta_date_ajout LIKE $param " : '').
|
777 |
($type == 'date.ajout' ? " AND ci_meta_date_ajout LIKE $param " : '').
|
773 |
($type == 'date.liaison' ? " AND coi_date_liaison LIKE $param " : '').
|
778 |
($type == 'date.liaison' ? " AND coi_date_liaison LIKE $param " : '').
|
774 |
($type == 'commentaire.meta' ? " AND ci_meta_comment LIKE $param " : '').
|
779 |
($type == 'commentaire.meta' ? " AND ci_meta_comment LIKE $param " : '').
|
Line 833... |
Line 838... |
833 |
$lngMin = $sw['lng'];
|
838 |
$lngMin = $sw['lng'];
|
Line 834... |
Line 839... |
834 |
|
839 |
|
835 |
$latMax = $ne['lat'];
|
840 |
$latMax = $ne['lat'];
|
Line 836... |
Line 841... |
836 |
$lngMax = $ne['lng'];
|
841 |
$lngMax = $ne['lng'];
|
837 |
|
842 |
|
838 |
// ATTENTION : coord_x correspond bien à la LATITUDE!
|
843 |
// ATTENTION : latitude correspond bien à la LATITUDE!
|
839 |
$sql = " AND (coord_x != 0 AND coord_y != 0) ".
|
844 |
$sql = " AND (latitude != 0 AND longitude != 0) ".
|
840 |
" AND coord_x > $latMin ".
|
845 |
" AND latitude > $latMin ".
|
841 |
" AND coord_x < $latMax ".
|
846 |
" AND latitude < $latMax ".
|
842 |
" AND coord_y > $lngMin ".
|
847 |
" AND longitude > $lngMin ".
|
843 |
" AND coord_y < $lngMax ";
|
848 |
" AND longitude < $lngMax ";
|
844 |
}
|
849 |
}
|
Line 845... |
Line 850... |
845 |
return $sql;
|
850 |
return $sql;
|
Line 855... |
Line 860... |
855 |
$lngMin = $sw['lng'];
|
860 |
$lngMin = $sw['lng'];
|
Line 856... |
Line 861... |
856 |
|
861 |
|
857 |
$latMax = $ne['lat'];
|
862 |
$latMax = $ne['lat'];
|
Line 858... |
Line -... |
858 |
$lngMax = $ne['lng'];
|
- |
|
859 |
|
863 |
$lngMax = $ne['lng'];
|
860 |
// ATTENTION : coord_x correspond bien à la LATITUDE!
|
864 |
|
861 |
$sql = "( (coord_x != 0 AND coord_y != 0) ".
|
865 |
$sql = "( (latitude != 0 AND longitude != 0) ".
|
862 |
" AND coord_x > $latMin ".
|
866 |
" AND latitude > $latMin ".
|
863 |
" AND coord_x < $latMax ".
|
867 |
" AND latitude < $latMax ".
|
864 |
" AND coord_y > $lngMin ".
|
868 |
" AND longitude > $lngMin ".
|
865 |
" AND coord_y < $lngMax )";
|
869 |
" AND longitude < $lngMax )";
|
866 |
}
|
870 |
}
|
Line 867... |
Line 871... |
867 |
return $sql;
|
871 |
return $sql;
|
Line 911... |
Line 915... |
911 |
|
915 |
|
912 |
private function construireWhereDept() {
|
916 |
private function construireWhereDept() {
|
913 |
$sql = '';
|
917 |
$sql = '';
|
914 |
// Récupération des coordonnées depuis l'id station
|
918 |
// Récupération des coordonnées depuis l'id station
|
915 |
extract($this->parametres);
|
919 |
extract($this->parametres);
|
916 |
if (isset($this->parametres['dept']) && !$this->etreNull($dept)) {
|
920 |
if (isset($dept) && !$this->etreNull($dept)) {
|
- |
|
921 |
$valeurs_a_proteger = explode(',',trim($dept));
|
- |
|
922 |
foreach ($valeurs_a_proteger as $valeur) {
|
- |
|
923 |
$valeurs_protegees[] = '(ce_zone_geo LIKE '.$this->bdd->quote('INSEE-C:'.$valeur.'%').') ';
|
- |
|
924 |
}
|
917 |
$dept = $this->traiterValeursMultiples($dept);
|
925 |
$valeurs = implode(' OR ', $valeurs_protegees);
|
918 |
$sql = " AND code IN ($dept) ";
|
926 |
$sql = " AND ($valeurs) ";
|
919 |
}
|
927 |
}
|
920 |
return $sql;
|
928 |
return $sql;
|
Line 921... |
Line 929... |
921 |
}
|
929 |
}
|
922 |
|
930 |
|
923 |
private function construireWhereCommune() {
|
931 |
private function construireWhereCommune() {
|
924 |
$sql = '';
|
932 |
$sql = '';
|
925 |
// Récupération des coordonnées depuis l'id station
|
933 |
// Récupération des coordonnées depuis l'id station
|
926 |
extract($this->parametres);
|
934 |
extract($this->parametres);
|
927 |
if (isset($this->parametres['commune']) && !$this->etreNull($commune)) {
|
935 |
if (isset($this->parametres['commune']) && !$this->etreNull($commune)) {
|
928 |
$commune = $this->proteger($commune);
|
936 |
$commune = $this->proteger($commune);
|
929 |
$sql = " AND location LIKE $commune ";
|
937 |
$sql = " AND zone_geo LIKE $commune ";
|
930 |
}
|
938 |
}
|
Line 931... |
Line 939... |
931 |
return $sql;
|
939 |
return $sql;
|
932 |
}
|
940 |
}
|
933 |
|
941 |
|
934 |
private function construireWhereCommuneSansCoordonneesAvecSensibles() {
|
942 |
private function construireWhereCommuneSansCoordonneesAvecSensibles() {
|
935 |
$sql = '';
|
943 |
$sql = '';
|
936 |
// Récupération des coordonnées depuis l'id station
|
944 |
// Récupération des coordonnées depuis l'id station
|
937 |
extract($this->parametres);
|
945 |
extract($this->parametres);
|
938 |
if (isset($this->parametres['commune']) && !$this->etreNull($commune)) {
|
946 |
if (isset($this->parametres['commune']) && !$this->etreNull($commune)) {
|
939 |
$commune = $this->proteger($commune);
|
947 |
$commune = $this->proteger($commune);
|
940 |
$sql = " AND location LIKE $commune ";
|
948 |
$sql = " AND zone_geo LIKE $commune ";
|
941 |
$sql .= " AND (
|
949 |
$sql .= " AND (
|
942 |
(
|
950 |
(
|
943 |
(coord_x = '000null' OR coord_x = '') ".
|
951 |
(latitude = '000null' OR latitude = '' OR latitude IS NULL) ".
|
944 |
" AND (coord_y = '000null' OR coord_y = '')".
|
952 |
" AND (longitude = '000null' OR longitude = '' OR longitude IS NULL)".
|
Line 945... |
Line 953... |
945 |
')'.
|
953 |
')'.
|
946 |
' OR mots_cles_texte LIKE "%sensible%"'.
|
954 |
' OR mots_cles_texte LIKE "%sensible%"'.
|
947 |
') ';
|
955 |
') ';
|
Line 948... |
Line 956... |
948 |
|
956 |
|
949 |
}
|
957 |
}
|
950 |
return $sql;
|
958 |
return $sql;
|
- |
|
959 |
}
|
951 |
}
|
960 |
|
952 |
|
961 |
private function construireWhereUtilisateur() {
|
953 |
private function construireWhereUtilisateur() {
|
962 |
$sql = '';
|
954 |
$sql = '';
|
963 |
// TODO tester si l'on recoit un id, un mail ou bien un nom ou prenom
|
955 |
// Récupération des coordonnées depuis l'id station
|
964 |
// pour en faire une fonction polyvalente
|
956 |
extract($this->parametres);
|
965 |
extract($this->parametres);
|
957 |
if (isset($this->parametres['utilisateur']) && !$this->etreNull($utilisateur)) {
|
966 |
if (isset($this->parametres['utilisateur']) && !$this->etreNull($utilisateur)) {
|
Line 958... |
Line 967... |
958 |
$utilisateur = $this->proteger($utilisateur);
|
967 |
$utilisateur = $this->proteger($utilisateur);
|
959 |
$sql = " AND identifiant = $utilisateur ";
|
968 |
$sql = " AND co.courriel_utilisateur = $utilisateur ";
|
960 |
}
|
969 |
}
|
961 |
return $sql;
|
970 |
return $sql;
|
962 |
}
|
971 |
}
|
963 |
|
972 |
|
964 |
private function construireWhereNumTaxon() {
|
973 |
private function construireWhereNumTaxon() {
|
965 |
$sql = '';
|
974 |
$sql = '';
|
966 |
// Récupération des coordonnées depuis l'id station
|
975 |
// Récupération des coordonnées depuis l'id station
|
967 |
extract($this->parametres);
|
976 |
extract($this->parametres);
|
Line 968... |
Line 977... |
968 |
if (isset($this->parametres['num_taxon']) && !$this->etreNull($num_taxon)) {
|
977 |
if (isset($this->parametres['nt']) && !$this->etreNull($num_taxon)) {
|
969 |
$num_taxon = $this->proteger($num_taxon);
|
978 |
$num_taxon = $this->proteger($num_taxon);
|
970 |
$sql = " AND num_taxon = $num_taxon ";
|
979 |
$sql = " AND nt = $num_taxon ";
|
971 |
}
|
980 |
}
|
972 |
return $sql;
|
- |
|
973 |
}
|
981 |
return $sql;
|
974 |
|
982 |
}
|
975 |
private function construireWhereProjet() {
|
983 |
|
976 |
$sql = '';
|
984 |
private function construireWhereProjet() {
|
977 |
// Récupération des coordonnées depuis l'id station
|
- |
|
978 |
extract($this->parametres);
|
985 |
$sql = '';
|
979 |
if(isset($this->parametres['projet']) ) {
|
986 |
// Récupération des coordonnées depuis l'id station
|
Line 980... |
Line 987... |
980 |
$projet_sql = $this->getSqlWhereProjet($projet);
|
987 |
extract($this->parametres);
|
981 |
if (!$this->etreNull($projet_sql)) {
|
988 |
$projet_sql = isset($projet) ? $this->getSqlWhereProjet($projet) : null;
|
982 |
$sql = " AND ($projet_sql) ";
|
989 |
if (!$this->etreNull($projet_sql)) {
|
983 |
}
|
990 |
$sql = " AND ($projet_sql) ";
|
984 |
}
|
991 |
}
|
985 |
return $sql;
|
992 |
return $sql;
|
986 |
}
|
993 |
}
|
987 |
|
- |
|
988 |
/**
|
- |
|
989 |
* Traitement de $projet pour construction du filtre dans la requête
|
- |
|
990 |
*/
|
- |
|
991 |
private function getSqlWhereProjet($projet) {
|
- |
|
992 |
$sql = null;
|
- |
|
993 |
if (! $this->etreNull($projet)) {
|
- |
|
994 |
$mot_cle_encode = $this->bdd->quote($this->encoderMotCle($projet));
|
- |
|
995 |
|
- |
|
996 |
// Construction de la requête
|
- |
|
997 |
$requete = 'SELECT * '.
|
- |
|
998 |
'FROM cel_mots_cles_obs '.
|
- |
|
999 |
"WHERE cmc_id_mot_cle_general = $mot_cle_encode ";
|
- |
|
1000 |
$elements_projet = $this->executerRequete($requete);
|
- |
|
1001 |
|
- |
|
1002 |
$requete_projet = array();
|
- |
|
1003 |
if ($elements_projet != false && count($elements_projet) > 0) {
|
- |
|
1004 |
// Pré-construction du where de la requête
|
- |
|
1005 |
$tpl_where = '(mots_cles LIKE "%%%s%%" AND identifiant = %s )';
|
- |
|
1006 |
|
- |
|
1007 |
foreach ($elements_projet as $occurence) {
|
- |
|
1008 |
$requete_projet[] = sprintf($tpl_where, $occurence['cmc_id_mot_cle_utilisateur'], $this->bdd->quote($occurence['cmc_id_proprietaire']));
|
994 |
|
1009 |
}
|
995 |
/**
|
1010 |
} else {
|
996 |
* Traitement de $projet pour construction du filtre dans la requête
|
Line 1011... |
Line 997... |
1011 |
$this->messages[] = "Aucune observation ne correspond à ce mot clé.";
|
997 |
*/
|
1012 |
}
|
998 |
private function getSqlWhereProjet($projet) {
|
1013 |
if (count($requete_projet) > 0) {
|
- |
|
1014 |
$sql = implode(" \nOR ", $requete_projet);
|
999 |
$sql = null;
|
1015 |
}
|
- |
|
1016 |
}
|
1000 |
if (isset($projet) && !$this->etreNull($projet)) {
|
1017 |
return $sql;
|
1001 |
$sql = 'co.mots_cles_texte LIKE '.$this->proteger($projet);
|
1018 |
}
|
1002 |
}
|
1019 |
|
1003 |
return $sql;
|
1020 |
private function construireWhereTag() {
|
- |
|
1021 |
$sql = '';
|
1004 |
}
|
1022 |
// Récupération des coordonnées depuis l'id station
|
1005 |
|
Line 1023... |
Line 1006... |
1023 |
extract($this->parametres);
|
1006 |
private function construireWhereTag() {
|
1024 |
if(isset($this->parametres['tag']) ) {
|
1007 |
$sql = '';
|
1025 |
$tag_sql = $this->getSqlWhereObsAvecImagesTaguees($tag);
|
1008 |
extract($this->parametres);
|
1026 |
if (!$this->etreNull($tag_sql)) {
|
1009 |
$tag_sql = isset($tag) ? $this->getSqlWhereObsAvecImagesTaguees($tag) : null;
|
1027 |
$sql = " AND ($tag_sql) ";
|
1010 |
if (!$this->etreNull($tag_sql)) {
|
1028 |
}
|
1011 |
$sql = " AND ($tag_sql) ";
|
1029 |
}
|
1012 |
}
|
1030 |
return $sql;
|
1013 |
return $sql;
|
1031 |
}
|
1014 |
}
|
1032 |
|
1015 |
|
1033 |
/**
|
1016 |
/**
|
1034 |
* Traitement de $tag pour construction du filtre dans la requête
|
1017 |
* Traitement de $tag pour construction du filtre dans la requête
|
1035 |
*/
|
1018 |
*/
|
1036 |
private function getSqlWhereObsAvecImagesTaguees($tag) {
|
1019 |
private function getSqlWhereObsAvecImagesTaguees($tag) {
|
1037 |
$sql = null;
|
1020 |
$sql = null;
|
1038 |
if (! $this->etreNull($tag)) {
|
1021 |
if (isset($tag) && !$this->etreNull($tag)) {
|
1039 |
$tag_sql = $this->getSqlWhereMotsCles($tag);
|
1022 |
$tag_sql = $this->getSqlWhereMotsCles($tag);
|
1040 |
// Construction de la requête
|
1023 |
// Construction de la requête
|
1041 |
$requete = 'SELECT DISTINCT coi_ce_observation AS ordre, coi_ce_utilisateur AS utilisateur '.
|
1024 |
$requete = 'SELECT DISTINCT coi.id_observation AS id_obs, ci.ce_utilisateur AS utilisateur '.
|
1042 |
'FROM cel_images '.
|
1025 |
'FROM cel_images ci'.
|
1043 |
' LEFT JOIN cel_obs_images '.
|
1026 |
' LEFT JOIN cel_obs_images coi'.
|
1044 |
' ON (ci_id_image = coi_ce_image) '.
|
1027 |
' ON (ci.id_image = coi.id_image) '.
|
1045 |
' LEFT JOIN cel_inventory AS i '.
|
1028 |
' LEFT JOIN cel_obs AS co '.
|
1046 |
' ON (coi_ce_utilisateur = i.identifiant AND coi_ce_observation = i.ordre) '.
|
1029 |
' ON (coi.id_observation = co.id_observation) '.
|
1047 |
' LEFT JOIN locations AS l '.
|
1030 |
' LEFT JOIN cel_zones_geo AS l '.
|
1048 |
" ON (l.name = i.location AND l.code = i.id_location) ".
|
1031 |
" ON (l.nom = co.zone_geo AND l.id_zone_geo = co.ce_zone_geo) ".
|
1049 |
"WHERE transmission = '1' ".
|
1032 |
"WHERE transmission = '1' ".
|
Line 1050... |
Line 1033... |
1050 |
$this->construireWhereCoordonnees().
|
1033 |
$this->construireWhereCoordonnees().
|
1051 |
$this->construireWhereUtilisateur().
|
1034 |
$this->construireWhereUtilisateur().
|
Line 1052... |
Line 1035... |
1052 |
$this->construireWhereNumTaxon().
|
1035 |
$this->construireWhereNumTaxon().
|
1053 |
$this->construireWhereNomTaxon().
|
1036 |
$this->construireWhereNomTaxon().
|
1054 |
$this->construireWhereProjet().
|
1037 |
$this->construireWhereProjet().
|
1055 |
(!$this->etreNull($tag_sql) ? "AND ($tag_sql) " : '').
|
1038 |
(!$this->etreNull($tag_sql) ? "AND ($tag_sql) " : '').
|
1056 |
'ORDER BY utilisateur ASC, ordre ASC';
|
1039 |
'ORDER BY utilisateur ASC, ci.ordre ASC';
|
1057 |
//$this->debug[] = $requete;
|
1040 |
//$this->debug[] = $requete;
|
1058 |
//die($requete);
|
1041 |
//die($requete);
|
1059 |
$elements_tag = $this->executerRequete($requete);
|
1042 |
$elements_tag = $this->executerRequete($requete);
|
1060 |
|
1043 |
|
1061 |
$requete_tag = array();
|
1044 |
$requete_tag = array();
|
1062 |
if ($elements_tag != false && count($elements_tag) > 0) {
|
1045 |
if ($elements_tag != false && count($elements_tag) > 0) {
|
Line 1063... |
Line 1046... |
1063 |
|
1046 |
|
1064 |
$filtres = array();
|
1047 |
$filtres = array();
|
1065 |
foreach ($elements_tag as $occurence) {
|
1048 |
foreach ($elements_tag as $occurence) {
|
1066 |
$utilisateur = $occurence['utilisateur'];
|
1049 |
$utilisateur = $occurence['utilisateur'];
|
1067 |
$ordre = $occurence['ordre'];
|
1050 |
$id_obs = $occurence['id_obs'];
|
Line 1068... |
Line 1051... |
1068 |
if (!array_key_exists($utilisateur, $filtres)) {
|
1051 |
if (!array_key_exists($utilisateur, $filtres)) {
|
1069 |
$filtres[$utilisateur] = array();
|
1052 |
$filtres[$utilisateur] = array();
|
1070 |
}
|
1053 |
}
|
Line 1093... |
Line 1076... |
1093 |
* Traitement de $tag pour construction du filtre dans la requête
|
1076 |
* Traitement de $tag pour construction du filtre dans la requête
|
1094 |
*/
|
1077 |
*/
|
1095 |
private function getSqlWhereMotsCles($tag) {
|
1078 |
private function getSqlWhereMotsCles($tag) {
|
1096 |
$sql = null;
|
1079 |
$sql = null;
|
1097 |
$mots_cles = $this->decomposerParametreTag($tag);
|
1080 |
$mots_cles = $this->decomposerParametreTag($tag);
|
1098 |
|
- |
|
1099 |
// Construction de la requête
|
- |
|
1100 |
$requete = 'SELECT cmc_id_proprietaire AS utilisateur, cmc_id_mot_cle_general AS mot_cle_general, '.
|
- |
|
1101 |
' cmc_id_mot_cle_utilisateur AS mot_cle_utilisateur '.
|
- |
|
1102 |
'FROM cel_mots_cles_images '.
|
- |
|
1103 |
'WHERE '.$this->getSqlWhereMotsClesImages($mots_cles['motsClesEncodesProteges']);
|
- |
|
1104 |
$elements_projet = $this->executerRequete($requete);
|
1081 |
$requete_projet = $this->getSqlWhereMotsClesImages($mots_cles);
|
1105 |
//$this->debug[] = $requete;
|
- |
|
1106 |
|
- |
|
1107 |
$requete_projet = array();
|
- |
|
1108 |
if ($elements_projet != false && count($elements_projet) > 0) {
|
- |
|
1109 |
// Pré-construction du where de la requête
|
- |
|
1110 |
if ($mots_cles['type'] == 'OR') {
|
- |
|
1111 |
$tpl_where = '(ci_meta_mots_cles LIKE "%%%s,%%" AND ci_ce_utilisateur = %s )';
|
- |
|
1112 |
foreach ($elements_projet as $occurence) {
|
- |
|
1113 |
$requete_projet[] = sprintf($tpl_where, $occurence['mot_cle_utilisateur'], $this->bdd->quote($occurence['utilisateur']));
|
- |
|
1114 |
}
|
- |
|
1115 |
} else {
|
- |
|
1116 |
$tpl_where_mc = "ci_meta_mots_cles LIKE '%%%s,%%'";
|
- |
|
1117 |
$tpl_where = '((%s) AND ci_ce_utilisateur = %s)';
|
- |
|
1118 |
$utilisateur_mc = array();
|
- |
|
1119 |
foreach ($elements_projet as $occurence) {
|
- |
|
1120 |
if (!isset($utilisateur_mc[$occurence['utilisateur']][$occurence['mot_cle_general']])) {
|
- |
|
1121 |
$utilisateur_mc[$occurence['utilisateur']][$occurence['mot_cle_general']] = $occurence['mot_cle_utilisateur'];
|
- |
|
1122 |
}
|
- |
|
1123 |
}
|
- |
|
1124 |
|
- |
|
1125 |
foreach ($utilisateur_mc as $utilisateur => $mots_cles_utilisateur) {
|
- |
|
1126 |
if (count($mots_cles_utilisateur) == count($mots_cles['motsCles'])) {
|
- |
|
1127 |
$where_mots_cles_utilisateur = array();
|
- |
|
1128 |
foreach ($mots_cles_utilisateur as $mot_cle) {
|
- |
|
1129 |
$where_mots_cles_utilisateur[] = sprintf($tpl_where_mc, $mot_cle);
|
- |
|
1130 |
}
|
- |
|
1131 |
$where_mots_cles_utilisateur = implode(' AND ', $where_mots_cles_utilisateur);
|
- |
|
1132 |
$utilisateur = $this->bdd->quote($utilisateur);
|
- |
|
1133 |
$requete_projet[] = sprintf($tpl_where, $where_mots_cles_utilisateur, $utilisateur);
|
- |
|
1134 |
}
|
- |
|
1135 |
}
|
- |
|
1136 |
}
|
- |
|
1137 |
} else {
|
- |
|
1138 |
$this->messages[] = "Aucune observation ne correspond à ce mot clé.";
|
- |
|
1139 |
}
|
- |
|
1140 |
|
- |
|
1141 |
$sql = implode(" \nOR ", $requete_projet);
|
1082 |
$sql = $requete_projet;
|
1142 |
//$this->debug[] = $sql;
|
1083 |
//$this->debug[] = $sql;
|
1143 |
return $sql;
|
1084 |
return $sql;
|
1144 |
}
|
1085 |
}
|
Line 1145... |
Line 1086... |
1145 |
|
1086 |
|
1146 |
/**
|
1087 |
/**
|
1147 |
* Traitement de $tag pour construction du filtre dans la requête
|
1088 |
* Traitement de $tag pour construction du filtre dans la requête
|
1148 |
*/
|
1089 |
*/
|
1149 |
private function getSqlWhereMotsClesImages($mots_cles_encodes) {
|
1090 |
private function getSqlWhereMotsClesImages($mots_cles_encodes) {
|
1150 |
$where_mots_cles_images = array();
|
1091 |
$where_mots_cles_images = array();
|
1151 |
foreach ($mots_cles_encodes as $mot_cle_encode) {
|
1092 |
foreach ($mots_cles_encodes['motsClesEncodesProteges'] as $mot_cle_encode) {
|
1152 |
$where_mots_cles_images[] = "cmc_id_mot_cle_general = $mot_cle_encode";
|
1093 |
$where_mots_cles_images[] = "ci.mots_cles_texte LIKE $mot_cle_encode";
|
1153 |
}
|
1094 |
}
|
1154 |
$where_mots_cles_images = implode(' OR ', $where_mots_cles_images);
|
1095 |
$where_mots_cles_images = implode(' '.$mots_cles_encodes['type'].' ', $where_mots_cles_images);
|
1155 |
return $where_mots_cles_images;
|
1096 |
return $where_mots_cles_images;
|
Line 1156... |
Line 1097... |
1156 |
}
|
1097 |
}
|
- |
|
1098 |
|
1157 |
|
1099 |
private function decomposerParametreTag($tags) {
|
1158 |
private function decomposerParametreTag($tags) {
|
1100 |
|
1159 |
$mots_cles = array('type' => null, 'motsCles' => null, 'motsClesEncodesProteges' => null);
|
1101 |
$mots_cles = array('type' => null, 'motsCles' => null, 'motsClesEncodesProteges' => null);
|
1160 |
if (preg_match('/.+OU.+/', $tags)) {
|
1102 |
if (preg_match('/.+OU.+/', $tags)) {
|
1161 |
$mots_cles['type'] = 'OR';
|
1103 |
$mots_cles['type'] = 'OR';
|
Line 1166... |
Line 1108... |
1166 |
} else {
|
1108 |
} else {
|
1167 |
$mots_cles['motsCles'][] = $tags;
|
1109 |
$mots_cles['motsCles'][] = $tags;
|
1168 |
}
|
1110 |
}
|
Line 1169... |
Line 1111... |
1169 |
|
1111 |
|
1170 |
foreach ($mots_cles['motsCles'] as $mot) {
|
1112 |
foreach ($mots_cles['motsCles'] as $mot) {
|
1171 |
$mots_cles['motsClesEncodesProteges'][] = $this->bdd->quote($this->encoderMotCle($mot));
|
1113 |
$mots_cles['motsClesEncodesProteges'][] = $this->bdd->quote('%'.$mot.'%');
|
1172 |
}
|
1114 |
}
|
1173 |
$this->debug[] = $mots_cles;
|
1115 |
$this->debug[] = $mots_cles;
|
1174 |
return $mots_cles;
|
1116 |
return $mots_cles;
|