16,7 → 16,7 |
* @copyright Copyright (c) 2011, Tela Botanica (accueil@tela-botanica.org) |
*/ |
class CelStatistiqueTxt extends Cel { |
|
|
/** |
* Méthode appelée avec une requête de type GET. |
*/ |
42,7 → 42,7 |
$this->envoyerJson($stats); |
} |
} |
|
|
private function analyserParametresUrl() { |
$this->parametres['utilisateur'] = isset($_GET['utilisateur']) ? Cel::db()->quote($this->verifierSecuriteParametreUrl($_GET['utilisateur'])) : null; |
$this->parametres['num_taxon'] = isset($_GET['num_taxon']) ? Cel::db()->quote($this->verifierSecuriteParametreUrl($_GET['num_taxon'])) : null; |
51,13 → 51,13 |
$this->parametres['start'] = isset($_GET['start']) ? $this->verifierSecuriteParametreUrl($_GET['start']) : null; |
$this->parametres['limit'] = isset($_GET['limit']) ? $this->verifierSecuriteParametreUrl($_GET['limit']) : null; |
} |
|
|
private function getListeUtilisateursNbrePhotos() { |
$liste = array(); |
|
|
$requete = $this->construireRequeteListeUtilisateurNbrePhoto(); |
if ($requete != null) { |
$resultats = Cel::db()->executerRequete($requete); |
$resultats = Cel::db()->requeter($requete); |
if ($resultats != false) { |
foreach ($resultats as $resultat) { |
$liste[$resultat['courriel_utilisateur']] = $resultat['nbre']; |
66,7 → 66,7 |
} |
return $liste; |
} |
|
|
private function construireRequeteListeUtilisateurNbrePhoto() { |
$select = 'SELECT co.courriel_utilisateur, COUNT(DISTINCT ci.id_image) AS nbre '; |
$from = 'FROM cel_obs co '. |
76,13 → 76,13 |
$groupBy = 'GROUP BY co.courriel_utilisateur '; |
$orderBy = 'ORDER BY nbre DESC '; |
$limitSql = 'LIMIT 0,150 '; |
|
|
$zero_images = false; |
if (count($this->parametres) != 0) { |
extract($this->parametres); |
|
|
$filtres = array(); |
|
|
if (isset($utilisateur)) { |
$filtres[] = "co.courriel_utilisateur = $utilisateur "; |
} |
101,7 → 101,7 |
} |
} |
$where .= ((count($filtres) > 0) ? 'AND '.implode(' AND ', $filtres) : ''); |
|
|
if (isset($start)) { |
$limitSql = str_replace('0,', "$start,", $limitSql); |
} |
117,7 → 117,7 |
} |
return $requete; |
} |
|
|
private function construireWhereTags() { |
$where = null; |
if (isset($this->parametres['tag'])) { |
128,8 → 128,8 |
$requete = 'SELECT cmc_id_mot_cle_utilisateur, cmc_id_proprietaire '. |
'FROM cel_mots_cles_images '. |
"WHERE cmc_id_mot_cle_general = $tag_encode "; |
$elements = Cel::db()->executerRequete($requete); |
|
$elements = Cel::db()->requeter($requete); |
|
if ($elements != false && count($elements) > 0) { |
// Pré-construction du where de la requête |
$tpl_where = '(ci_meta_mots_cles LIKE "%%%s%%" AND ci_ce_utilisateur = %s )'; |
144,11 → 144,11 |
} |
return $where; |
} |
|
|
private function getListeTaxonsNbrePhotos() { |
$requete = $this->construireRequeteListeTaxonNbrePhoto(); |
|
$resultats = Cel::db()->executerRequete($requete); |
$resultats = Cel::db()->requeter($requete); |
$liste = array(); |
if ($resultats != false) { |
foreach ($resultats as $resultat) { |
157,7 → 157,7 |
} |
return $liste; |
} |
|
|
private function construireRequeteListeTaxonNbrePhoto() { |
$select = 'SELECT nom_ret, COUNT(DISTINCT ci.id_image) AS nbre '; |
$from = 'FROM cel_obs co '. |
168,13 → 168,13 |
$groupBy = 'GROUP BY nom_ret '; |
$orderBy = 'ORDER BY nbre DESC '; |
$limitSql = 'LIMIT 0,150 '; |
|
|
|
|
if (count($this->parametres) != 0) { |
extract($this->parametres); |
|
|
$filtres = array(); |
|
|
if (isset($utilisateur)) { |
$filtres[] = "co.courriel_utilisateur = $utilisateur "; |
} |
185,7 → 185,7 |
$filtres[] = "nom_ret LIKE $taxon "; |
} |
$where .= ((count($filtres) > 0) ? 'AND '.implode(' AND ', $filtres) : ''); |
|
|
if (isset($start)) { |
$limitSql = str_replace('0,', "$start,", $limitSql); |
} |
193,33 → 193,33 |
$limitSql = str_replace('150', $limit, $limitSql); |
} |
} |
|
|
$requete = $select.$from.$where.$groupBy.$orderBy.$limitSql; |
|
return $requete; |
} |
|
|
private function getNombres() { |
|
$requete = $this->construireRequeteNbreObs(); |
$info['observations'] = (int) Cel::db()->executerRequete($requete, 'Column'); |
|
$requete = $this->construireRequeteNbreObsPubliques(); |
$info['observationsPubliques'] = (int) Cel::db()->executerRequete($requete, 'Column'); |
|
$requete = $this->construireRequeteNbreObs(); |
$info['observations'] = (int) Cel::db()->requeterValeurUnique($requete); |
|
$requete = $this->construireRequeteNbreObsPubliques(); |
$info['observationsPubliques'] = (int) Cel::db()->requeterValeurUnique($requete); |
|
$requete = $this->construireRequeteNbreImg(); |
$info['images'] =(int) Cel::db()->executerRequete($requete, 'Column'); |
|
$info['images'] =(int) Cel::db()->requeterValeurUnique($requete); |
|
$requete = $this->construireRequeteNbreImgLiees(); |
$info['imagesLiees'] =(int) Cel::db()->executerRequete($requete, 'Column'); |
|
$info['imagesLiees'] =(int) Cel::db()->requeterValeurUnique($requete); |
|
$requete = $this->construireRequeteNbreObsLiees(); |
$info['observationsLiees'] = (int) Cel::db()->executerRequete($requete, 'Column'); |
|
$info['observationsLiees'] = (int) Cel::db()->requeterValeurUnique($requete); |
|
$info['moyImagesParObs'] = ($info['observationsLiees'] > 0 ? round($info['imagesLiees']/$info['observationsLiees'], 2) : ''); |
|
|
$requete = $this->construireRequeteNbreObsParCommune(); |
$info['communes'] = ($resultats = Cel::db()->executerRequete($requete)) ? count($resultats) : '' ; |
$info['communes'] = ($resultats = Cel::db()->requeter($requete)) ? count($resultats) : '' ; |
$info['observationsParCommunesMin'] = 1000; |
$info['observationsParCommunesMax'] = 0; |
$info['observationsParCommunesTotal'] = 0; |
233,10 → 233,10 |
$info['observationsParCommunesTotal'] += $resultat['nbre']; |
} |
$info['observationsParCommunesMoyenne'] = ($info['communes'] > 0 ) ? round($info['observationsParCommunesTotal'] / $info['communes'], 2) : 0; |
|
return $info; |
|
return $info; |
} |
|
|
private function construireRequeteNbreObs() { |
$requete = 'SELECT COUNT(id_observation) AS nbre '. |
'FROM cel_obs '; |
244,7 → 244,7 |
if (count($this->parametres) != 0) { |
$filtres = array(); |
extract($this->parametres); |
|
|
if (isset($utilisateur)) { |
$filtres[] = "courriel_utilisateur = $utilisateur "; |
} |
254,13 → 254,13 |
if (isset($taxon)) { |
$filtres[] = "nom_ret LIKE $taxon "; |
} |
|
|
$requete .= ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : ''); |
} |
|
return $requete; |
} |
|
|
private function construireRequeteNbreObsPubliques() { |
$requete = 'SELECT COUNT(id_observation) AS nbre '. |
'FROM cel_obs '. |
269,7 → 269,7 |
if (count($this->parametres) != 0) { |
$filtres = array(); |
extract($this->parametres); |
|
|
if (isset($utilisateur)) { |
$filtres[] = "courriel_utilisateur = $utilisateur "; |
} |
279,13 → 279,13 |
if (isset($taxon)) { |
$filtres[] = "nom_ret LIKE $taxon "; |
} |
|
|
$requete .= ((count($filtres) > 0) ? 'AND '.implode(' AND ', $filtres) : ''); |
} |
|
|
return $requete; |
} |
|
|
private function construireRequeteNbreObsParCommune() { |
$requete = 'SELECT COUNT(id_observation) AS nbre '. |
'FROM cel_obs '. |
296,7 → 296,7 |
if (count($this->parametres) != 0) { |
$filtres = array(); |
extract($this->parametres); |
|
|
if (isset($utilisateur)) { |
$filtres[] = "courriel_utilisateur = $utilisateur "; |
} |
306,28 → 306,28 |
if (isset($taxon)) { |
$filtres[] = "nom_ret LIKE $taxon "; |
} |
|
|
$requete .= ((count($filtres) > 0) ? 'AND '.implode(' AND ', $filtres) : ''); |
} |
$requete .= $groupBy; |
|
|
return $requete; |
} |
|
|
private function construireRequeteNbreImg() { |
$select = 'SELECT COUNT(DISTINCT ci.id_image) AS nbre '; |
$from = 'FROM cel_images ci '; |
|
|
if (count($this->parametres) != 0) { |
$filtres = array(); |
|
|
extract($this->parametres); |
|
|
if (isset($utilisateur)) { |
$filtres[] = "courriel_utilisateur = $utilisateur "; |
} |
if (isset($num_taxon)) { |
|
|
$filtres[] = "nt = $num_taxon "; |
} |
if (isset($taxon)) { |
337,7 → 337,7 |
$from .= 'LEFT JOIN cel_obs_images coi ON (coi.id_image = ci.id_image) '. |
'LEFT JOIN cel_obs co ON (coi.id_observation = co.id_observation) '; |
} |
|
|
$where = ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : ''); |
} |
$requete = $select.$from.$where; |
344,17 → 344,17 |
|
return $requete; |
} |
|
|
private function construireRequeteNbreImgLiees() { |
$select = 'SELECT COUNT(DISTINCT ci.id_image) AS nbre '; |
$from = 'FROM cel_obs_images coi '. |
' LEFT JOIN cel_images ci ON (coi.id_image = ci.id_image) '; |
|
|
if (count($this->parametres) != 0) { |
$filtres = array(); |
|
|
extract($this->parametres); |
|
|
if (isset($utilisateur)) { |
$filtres[] = "ci.courriel_utilisateur = $utilisateur "; |
} |
364,28 → 364,28 |
if (isset($taxon)) { |
$filtres[] = "nom_ret LIKE $taxon "; |
} |
|
|
if (isset($num_taxon) || isset($taxon)) { |
$from .= 'LEFT JOIN cel_obs ON (coi.id_observation = co.id_observation) '; |
} |
|
|
$where = ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : ''); |
} |
|
|
$requete = $select.$from.$where; |
return $requete; |
} |
|
|
private function construireRequeteNbreObsLiees() { |
$select = 'SELECT COUNT(DISTINCT coi.id_observation) AS nbre '; |
$from = 'FROM cel_obs_images coi '. |
' LEFT JOIN cel_obs co ON (coi.id_observation = co.id_observation) '; |
|
|
if (count($this->parametres) != 0) { |
$filtres = array(); |
|
|
extract($this->parametres); |
|
|
if (isset($utilisateur)) { |
$filtres[] = "courriel_utilisateur = $utilisateur "; |
} |
395,12 → 395,12 |
if (isset($taxon)) { |
$filtres[] = "nom_ret LIKE $taxon "; |
} |
|
|
$where = ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : ''); |
} |
|
|
$requete = $select.$from.$where; |
|
|
return $requete; |
} |
} |