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