Subversion Repositories eFlore/Applications.cel

Compare Revisions

Regard whitespace Rev 3742 → Rev 3743

/trunk/jrest/services/CelWidgetSaisie.php
43,7 → 43,7
} else if (array_key_exists('projet', $requeteDonnees)) {
$this->debug[] = 'Projet : '.$requeteDonnees['projet'];
if ($requeteDonnees['projet'] != "base") {
$this->projet = $requeteDonnees['projet'];
$this->projet = $requeteDonnees['projet'] ?? null;
$this->projetId = $requeteDonnees['id_projet'] ?? null;
}
 
125,6 → 125,8
'coordinates' => [ (float)$obs['longitude'], (float)$obs['latitude'] ]
]); // CONCAT('{\"type\":\"Point\",\"coordinates\":[', longitude, ',', latitude,']}'),
$obsAAjouter['geodatum'] = 'WGS84';
$obsAAjouter['published_location'] = ($this->tagsObs != null && in_array('sensible', $this->tagsObs) == true) ?
"localité" : "précise";
 
$obsAAjouter['phenology'] = isset($obs['phenologie']) ? $obs['phenologie'] : null;
$obsAAjouter['coef'] = isset($obs['abondance']) ? $obs['abondance'] : null;
137,7 → 139,7
$obsAAjouter['image_nom'] = $obs['image_nom'] ?? null;
$obsAAjouter['image_b64'] = $obs['image_b64'] ?? null;
 
$obsAAjouter['certainty'] = '';
$obsAAjouter['certainty'] = null;
if (isset($obs['certitude'])) {
switch ($obs['certitude']) {
case 'certaine':
151,9 → 153,11
break;
}
}
$obsAAjouter['certainty'] = ($this->projet == "aDeterminer") ? 'à déterminer' : $obsAAjouter['certainty'];
$obsAAjouter['input_source'] = 'widget';
$obsAAjouter['project_id'] = $this->projetId;
$obsAAjouter['project'] = $this->projet;
// racommodage en attendant mieux
$obsAAjouter['project'] = ($this->projetId == null) ? null : ($this->projetId == 53) ? "missions-flore" : $this->projet ;
if ($this->isFromPlantNet) {
$obsAAjouter['input_source'] = 'PlantNet';
$obsAAjouter['plantnet_id'] = $obs['obs_id'];
663,6 → 667,7
 
private function traiterNomFichierImage($fichierNom) {
$fichierNom = preg_replace('/[.](jpeg|jpg)$/i', '.jpg', strtolower(trim($fichierNom)));
return $fichierNom;
}
 
749,7 → 754,7
$requete = $this->construireRequeteInsertionImage($infosImage);
$resultat = Cel::db()->executer($requete);
if ($resultat !== false) {
$idImage = $this->traiterEtRecupererIdImage($nomFichierImage);
$idImage = $this->traiterEtRecupererIdImage($nomFichierImage, $id_obs);
if ($idImage !== false) {
$infosImage['url'] = 'https://api.tela-botanica.org/img:'.str_pad($idImage, 9, '0', STR_PAD_LEFT).'O';
 
799,14 → 804,13
return $ok;
}
 
private function traiterEtRecupererIdImage($original_name) {
$id_utilisateur = Cel::db()->proteger($this->userId);
$email_utilisateur = Cel::db()->proteger($this->userEmail);
private function traiterEtRecupererIdImage($original_name, $id_obs) {
$original_name = Cel::db()->proteger($original_name);
$id_obs = Cel::db()->proteger($id_obs);
 
$requete = 'SELECT id '.
'FROM photo '.
"WHERE (user_id = $id_utilisateur OR user_email = $email_utilisateur)".
"WHERE occurrence_id = ".$id_obs.
" AND original_name = $original_name ";
$resultat = Cel::db()->requeter($requete);
$id_image = $resultat[0]['id'] ?? false;
/trunk/jrest/services/CelSyndicationImage.php
41,8 → 41,7
'motcle' => 'tag',
'projet' => 'projet',
'referentiel' => 'referentiel',
'groupe_zones_geo' => 'groupe_zones_geo',
'standard' => 'donnees_standard'
'groupe_zones_geo' => 'groupe_zones_geo'
);
 
/**
342,7 → 341,7
$requete = (isset($this->distinct) ? 'SELECT DISTINCT' : 'SELECT').' co.*, '.
' ci.id_image, co.ce_utilisateur, nom_original, ci.date_creation, ci.mots_cles_texte AS mots_cles_texte_images, '.
' co.mots_cles_texte AS mots_cles_texte_images_obs, "" as commentaire, "" as note_qualite, nom_referentiel '.
'FROM cel_export_total AS co LEFT JOIN cel_images_export AS ci ON (co.id_observation = ci.ce_observation) '.
'FROM cel_export_total AS co JOIN cel_images_export AS ci ON (co.id_observation = ci.ce_observation) '.
'WHERE co.transmission = 1 '.
' AND co.ce_utilisateur = ci.ce_utilisateur '.
'ORDER BY '.(isset($this->orderby) && (!is_null($this->orderby)) ? $this->orderby : 'ci.date_creation DESC').' '.
383,7 → 382,7
' co.mots_cles_texte AS mots_cles_texte_obs, ci.commentaire AS commentaire_img, note_qualite, nom_referentiel, '.
' co.commentaire AS commentaire_obs '.
'FROM cel_images_export AS ci '.
' LEFT JOIN cel_export_total AS co '.
' JOIN cel_export AS co '.
' ON (ci.ce_observation = co.id_observation) '.
(($this->etreFluxAdmin()) ? '' : 'WHERE co.transmission = 1 ').
'ORDER BY '.(isset($this->orderby) && (!is_null($this->orderby)) ? $this->orderby : 'ci.date_creation DESC').' '.
438,7 → 437,7
$requete = 'SELECT *, b.mots_cles_texte AS mots_cles_texte_obs, c.mots_cles_texte AS mots_cles_texte_images, '.
' b.commentaire AS commentaire_obs, "" AS commentaire_img, nom_referentiel '.
'FROM cel_images_export c '.
' left JOIN cel_export AS b '.
' JOIN cel_export AS b '.
' ON (c.ce_observation = b.id_observation) ';
$requete .= ' ORDER BY '.(isset($this->orderby) && (!is_null($this->orderby)) ? $this->orderby : 'c.date_creation DESC').' '.
"LIMIT $this->start,$this->limit ";
451,17 → 450,22
$requete = 'SELECT *, b.mots_cles_texte as mots_cles_texte_obs, c.mots_cles_texte as mots_cles_texte_images, '.
' b.commentaire as commentaire_obs, "" as commentaire_img, nom_referentiel '.
'FROM cel_images_export AS c '.
' INNER JOIN cel_export_total AS b '.
' JOIN cel_export_total AS b '.
' ON (c.ce_observation = b.id_observation) '.
'WHERE donnees_standard = 1'.
(($this->etreFluxAdmin()) ? '' : ' AND b.transmission = 1 ').
'WHERE '.
(($this->etreFluxAdmin()) ? '' : ' b.transmission = 1 ').
' AND ';
 
if ($this->estUneRechercheGenerale()) {
$chaine_requete = $_GET['recherche'];
$requete .= $this->creerSousRequeteRechercheGenerale($chaine_requete);
$requete .= "donnees_standard = 1 AND ".$this->creerSousRequeteRechercheGenerale($chaine_requete);
} else {
$criteres = $this->traiterCriteresMultiples($_GET) ;
if (!isset($_GET["standard"])) {
$requete .= "donnees_standard = 1 AND ";
} elseif ($_GET["standard"] = 1) {
$requete .= "donnees_standard = 1 AND ";
}
if (!empty($criteres)) {
$requete .= $this->creerSousRequeteRechercheParCriteres($criteres);
}
/trunk/jrest/services/CelWidgetManager.php
136,7 → 136,7
$projet = (isset($_GET['projet'])) ? $_GET['projet'] : "";
$langue = (isset($_GET['langue'])) ? $_GET['langue'] : "fr";
$liste = $champs->consulterProjetChampsEtendus($projet, $langue);
$liste = $this->traiterCorrespondanceChampsEtendusBDInterface($liste);
$liste = $this->traiterCorrespondanceChampsEtendusBDInterface($liste, $projet);
$this->envoyerJson($liste);
}
213,25 → 213,25
}
return $champsrequete;
}
protected function traiterCorrespondanceChampsEtendusBDInterface(Array $champsrequete) {
protected function traiterCorrespondanceChampsEtendusBDInterface(Array $champsrequete, $projet = "null") {
$champssupp = array(); $i = 0;
foreach ($champsrequete as $champ) {
$champssupp[$champ['project']]['projet'] = $champ['project'];
$champssupp[$champ['project']]['langue'] = $champ['language_iso_code'];
$champssupp[$projet]['projet'] = $champ['project'];
$champssupp[$projet]['langue'] = $champ['language_iso_code'];
// pour la table extended fiels champs interface => champs bd
foreach ($this->correspondance_champs_etendus as $ci => $ce) {
if (isset($champ[$ce]) && $champ[$ce] != "") {
$champssupp[$champ['project']]['champs-supp'][$i][$ci] = $champ[$ce];
$champssupp[$projet]['champs-supp'][$i][$ci] = $champ[$ce];
} else {
$champssupp[$champ['project']]['champs-supp'][$i][$ci]= "";
$champssupp[$projet]['champs-supp'][$i][$ci]= "";
}
}
// pour la table extended fiels trad champs interface => champs bd
foreach ($this->correspondance_champs_etendus_trad as $cet => $cr) {
if (isset($champ[$cr]) && $champ[$cr] != "") {
$champssupp[$champ['project']]['champs-supp'][$i][$cet]= $champ[$cr];
$champssupp[$projet]['champs-supp'][$i][$cet]= $champ[$cr];
} else {
$champssupp[$champ['project']]['champs-supp'][$i][$cet]="";
$champssupp[$projet]['champs-supp'][$i][$cet]="";
}
}$i++;
}
/trunk/jrest/services/CelWidgetMapPoint.php
35,7 → 35,8
const MARQUEUR_GROUPE = 'GROUPE';
const MARQUEUR_COMMUNE = 'COMMUNE';
const MARQUEUR_STATION = 'STATION';
private $standard = "AND donnees_standard = 1";
private $standard = "1";
private $table_export = "cel_export";
/**
* Méthode appelée avec une requête de type GET.
48,7 → 49,9
extract($this->parametres);
//Chronometre::chrono("Avant groupage");
if (isset($this->parametres['standard']) && $this->parametres['standard'] == 0) {
$this->standard = "";
$this->standard = "0";
$this->table_export = "cel_export_total";
}
 
$action = array_shift($ressources);
106,22 → 109,20
public function getTout($params) {
 
$emplacements = null;
$concatenation_id = "CONCAT(IFNULL(latitude,''),IFNULL(longitude,''), IFNULL(wgs84_latitude,''),IFNULL(wgs84_longitude,'')) ";
 
$requete = 'SELECT ce_zone_geo, zone_geo, station, '.
"mots_cles_texte, ".
$concatenation_id = "CONCAT(IFNULL(latitude,''),IFNULL(longitude,'')) ";
$transmission = ( $this->standard == 0) ? "transmission = '1' AND " : "";
$requete = 'SELECT distinct ce_zone_geo, zone_geo, NULL as station, '.
"NULL as mots_cles_texte, ".
"latitude, ".
"wgs84_latitude, ".
"NULL as wgs84_latitude, ".
"longitude, ".
"wgs84_longitude, ".
"NULL as wgs84_longitude, ".
$concatenation_id." as id_coord ".
'FROM cel_export_total AS co '.
' LEFT JOIN cel_zones_geo AS zg '.
' ON (zg.nom = co.zone_geo AND zg.id_zone_geo = co.ce_zone_geo) '.
"WHERE transmission = '1' ".
$this->standard.
" AND (".
$this->construireWhereRectangleStationOR()." OR ".
'FROM '.$this->table_export.' AS co '.
"WHERE ".$transmission.
" (".
$this->construireWhereRectangleStationOR()." ".
$this->construireWhereRectangleCommuneOR().") ".
$this->construireWhereDept().
$this->construireWhereCommune().
137,8 → 138,7
$this->construireWhereTag().
$this->construireWhereNombreDeJours().
$this->construireWhereAnnee().
$this->construireWhereGroupeZoneGeo().
' GROUP BY id_coord';
$this->construireWhereGroupeZoneGeo();
 
$resultats_emplacements = Cel::db()->requeter($requete);
$emplacements = $this->traiterEmplacements($resultats_emplacements, $this->compterObservations($params));
155,13 → 155,11
private $nb_obs = 0;
 
private function compterObservations($params) {
$transmission = ( $this->standard == 0) ? "transmission = '1' AND " : "";
$requete = 'SELECT COUNT(*) as nb '.
'FROM cel_export_total AS co '.
' LEFT JOIN cel_zones_geo AS zg '.
' ON (zg.nom = co.zone_geo AND zg.id_zone_geo = co.ce_zone_geo) '.
"WHERE transmission = '1' ".
$this->standard.
" AND (".
'FROM '.$this->table_export.' AS co '.
"WHERE ".$transmission.
" (".
$this->construireWhereRectangleStationOR()." OR ".
$this->construireWhereRectangleCommuneOR().") ".
$this->construireWhereDept().
344,16 → 342,15
public function getObservations($params) {
$resultats = array();
$total = 0;
$transmission = ( $this->standard == 0) ? "transmission = '1' " : " 1 ";
if (isset($this->parametres['station']) && !$this->etreNull($this->parametres['station'])) {
$requete = 'SELECT SQL_CALC_FOUND_ROWS id_observation, ce_utilisateur, courriel_utilisateur, pseudo_utilisateur as nom_utilisateur, "" as prenom_utilisateur, '.
' nom_sel, nom_ret, nom_sel_nn, nom_ret_nn, "" as nt, famille, '.
' lieudit, zone_geo, date_observation, milieu, commentaire, '.
' utm_secteur, utm_x, utm_y, code, date_transmission, nom_referentiel '.
'FROM cel_export_total AS co '.
' LEFT JOIN cel_zones_geo AS zg '.
" ON (zg.nom = co.zone_geo AND zg.id_zone_geo = co.ce_zone_geo) ".
"WHERE transmission = '1' ".
$this->standard.
' null as utm_secteur, null as utm_x, null as utm_y, ce_zone_geo as code, date_transmission, nom_referentiel '.
'FROM '.$this->table_export.' AS co '.
"WHERE ".$transmission.
(($this->communeEstDemandee()) ? $this->construireWhereCommuneSansCoordonneesAvecSensibles() : $this->construireWhereCoordonneesSansSensibles()).
$this->construireWhereDept().
$this->construireWherePays().
496,14 → 493,11
*/
public function getTaxons($params) {
$json = null;
 
$transmission = ( $this->standard == 0) ? "transmission = '1' AND " : "";
$requete = 'SELECT SQL_CALC_FOUND_ROWS DISTINCT nom_ret, nom_ret_nn, nt, famille '.
'FROM cel_export_total AS co '.
' LEFT JOIN cel_zones_geo AS zg '.
' ON (zg.nom = co.zone_geo AND zg.id_zone_geo = co.ce_zone_geo) '.
"WHERE transmission = '1' ".
$this->standard.
" AND nom_ret != '' ".
'FROM '.$this->table_export.' AS co '.
"WHERE ".$transmission.
" nom_ret != '' ".
$this->construireWhereDept().
$this->construireWhereCommune().
$this->construireWherePays().
559,7 → 553,7
if ($type == self::MARQUEUR_COMMUNE) {
$lat = Cel::db()->proteger($lat.'%');
$lng = Cel::db()->proteger($lng.'%');
$sql = " AND wgs84_latitude LIKE $lat AND wgs84_longitude LIKE $lng ";
//$sql = " AND wgs84_latitude LIKE $lat AND wgs84_longitude LIKE $lng ";
} else if ($type == self::MARQUEUR_STATION) {
$lat = Cel::db()->proteger($lat.'%');
$lng = Cel::db()->proteger($lng.'%');
577,7 → 571,7
if ($type == self::MARQUEUR_COMMUNE) {
$lat = Cel::db()->proteger($lat);
$lng = Cel::db()->proteger($lng);
$sql = " AND wgs84_latitude LIKE $lat AND wgs84_longitude LIKE $lng ";
//$sql = " AND wgs84_latitude LIKE $lat AND wgs84_longitude LIKE $lng ";
} else if ($type == self::MARQUEUR_STATION) {
$lat = Cel::db()->proteger($lat.'%');
$lng = Cel::db()->proteger($lng.'%');
647,8 → 641,8
$sql = '';
extract($this->parametres);
if (isset($referentiel) && !$this->etreNull($referentiel)) {
$referentiel = Cel::db()->proteger($referentiel.'%');
$sql = ' AND co.nom_referentiel LIKE '.$referentiel.' ';
$referentiel = Cel::db()->proteger($referentiel);
$sql = ' AND co.nom_referentiel = '.$referentiel.' ';
}
return $sql;
}
758,13 → 752,13
* Retour sous forme de tableau : array[identifiant] = array(ordre, ordre...);
*/
private function obtenirObsLieesImg($type, $param) {
$transmission = ( $this->standard == 0) ? "transmission = '1' AND " : "";
// Construction de la requête
$requete = 'SELECT DISTINCT co.id_obs, co.ce_utilisateur AS utilisateur '.
'FROM cel_export_total AS co '.
'FROM '.$this->table_export.' AS co '.
' LEFT JOIN cel_zones_geo AS zg '.
' ON (zg.nom = co.zone_geo AND zg.id_zone_geo = co.ce_zone_geo) '.
"WHERE co.transmission = '1' and co.images != null".
$this->standard.
' ON (zg.nom = co.zone_geo AND zg.code = co.ce_zone_geo) '.
"WHERE ".$transmission." co.images != null".
($type == 'date.creation' ? " AND co.date_creation LIKE $param " : '').
($type == 'commentaire.obs' ? " AND co.commentaire LIKE $param " : '').
($type == 'commentaire.*' ? " AND co.commentaire LIKE $param " : '').
779,7 → 773,7
$this->construireWhereProjet().
$this->construireWhereTag().
$this->construireWhereGroupeZoneGeo().
'ORDER BY utilisateur ASC, ordre ASC';
'ORDER BY utilisateur ASC';
//$this->debug[] = $requete;
//die($requete);
$resultats = Cel::db()->requeter($requete);
789,7 → 783,7
$observations = array();
foreach ($resultats as $occurence) {
$utilisateur = $occurence['utilisateur'];
$ordre = $occurence['ordre'];
$ordre = $occurence['id_obs'];
if (!array_key_exists($utilisateur, $observations)) {
$observations[$utilisateur] = array();
}
879,9 → 873,9
$latMax = $ne['lat'];
$lngMax = $ne['lng'];
 
$sql = "AND wgs84_longitude != 0 AND wgs84_latitude != 0 ".
/*$sql = "AND wgs84_longitude != 0 AND wgs84_latitude != 0 ".
" AND wgs84_latitude BETWEEN $latMin AND $latMax ".
" AND wgs84_longitude BETWEEN $lngMin AND $lngMax ";
" AND wgs84_longitude BETWEEN $lngMin AND $lngMax ";*/
}
return $sql;
}
898,9 → 892,9
$latMax = $ne['lat'];
$lngMax = $ne['lng'];
 
$sql = "( wgs84_longitude != 0 AND wgs84_latitude != 0 ".
/*$sql = "( wgs84_longitude != 0 AND wgs84_latitude != 0 ".
" AND wgs84_latitude BETWEEN $latMin AND $latMax ".
" AND wgs84_longitude BETWEEN $lngMin AND $lngMax )";
" AND wgs84_longitude BETWEEN $lngMin AND $lngMax )";*/
 
/*$sql = " MBRWithin(point_commune, GeomFromText('POLYGON((".$latMin.' '.$lngMin.','.
$latMax.' '.$lngMin.','.
979,7 → 973,7
private function construireWherePhotosSeulement() {
$sql = '';
if (isset($this->parametres['photos']) && $this->parametres['photos'] == 1) {
$sql = 'AND co.id_observation IN (SELECT DISTINCT ce_observation FROM cel_images_export) ';
$sql = ' AND co.images is not null ';
}
return $sql;
}
1021,8 → 1015,8
$num_nom_retenu = Cel::db()->proteger($num_nom_ret);
if(!empty($sous_taxons)) {
$sql_in_sous_tax = implode(',', $sous_taxons);
$sql = " AND (nom_sel_nn IN (".$sql_in_sous_tax.") OR ".
"nom_ret_nn IN (".$sql_in_sous_tax.") ".
$sql = " AND (nom_sel_nn IN (".$num_nom_retenu.', '.$sql_in_sous_tax.") OR ".
"nom_ret_nn IN (".$num_nom_retenu.', '.$sql_in_sous_tax.") ".
") ";
}
else {
1143,17 → 1137,17
*/
private function getSqlWhereObsAvecImagesTaguees($tag) {
$sql = null;
$transmission = ( $this->standard == 0) ? "co.transmission = '1' " : " 1 ";
if (isset($tag) && !$this->etreNull($tag)) {
$tag_sql = $this->getSqlWhereMotsCles($tag);
// Construction de la requête
$requete = 'SELECT DISTINCT co.id_observation AS id_obs, co.ce_utilisateur AS utilisateur '.
'FROM cel_images_export ci'.
' INNER JOIN cel_export_total AS co '.
' INNER JOIN '.$this->table_export.' AS co '.
' ON (ci.ce_observation = co.id_observation) '.
' LEFT JOIN cel_zones_geo AS zg '.
" ON (zg.nom = co.zone_geo AND zg.id_zone_geo = co.ce_zone_geo) ".
"WHERE co.transmission = '1' ".
$this->standard.
" ON (zg.nom = co.zone_geo AND zg.code = co.ce_zone_geo) ".
"WHERE ".$transmission.
$this->construireWhereCoordonnees().
$this->construireWhereUtilisateur().
$this->construireWhereNumTaxon().
1161,7 → 1155,7
$this->construireWhereReferentiel().
$this->construireWhereProjet().
(!$this->etreNull($tag_sql) ? "AND ($tag_sql) " : '').
'ORDER BY utilisateur ASC, ci.ordre ASC';
'ORDER BY utilisateur ASC';
//$this->debug[] = $requete;
//die($requete);
$elements_tag = Cel::db()->requeter($requete);
/trunk/jrest/services/CelSyndicationObservation.php
284,7 → 284,7
}
$requete .= $nom_valeur[0].'='.Cel::db()->quote($nom_valeur[1]).' AND '; break;
case "ce_zone_geo" :
$requete .= ' ('.$nom_valeur[0].' LIKE "INSEE-C:'.$nom_valeur[1].'%") AND '; break;
$requete .= ' ('.$nom_valeur[0].' LIKE "'.$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 "%'.
379,7 → 379,7
' OR '.
'ce_zone_geo LIKE "'.$chaine_requete.'%" '.
' OR '.
'ce_zone_geo LIKE "INSEE-C:'.$chaine_requete.'%" '.
'ce_zone_geo LIKE "'.$chaine_requete.'%" '.
' OR '.
'courriel_utilisateur LIKE "'.$chaine_requete.'%" '.
' OR '.