Subversion Repositories eFlore/Applications.cel

Compare Revisions

Regard whitespace Rev 3742 → Rev 3743

/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);