WebSVN – eFlore/Applications.cel – Diff – /trunk/jrest/services/CelStatistiqueTxt.php


<?php
/**
 * Service fournissant des statistiques de l'application CEL au format texte (JSON).
 * Encodage en entrée : utf8
 * Encodage en sortie : utf8
 *
 * Cas d'utilisation :
 * /CelStatistiqueTxt/TypeDeStat : retourne les statistiques demandées
 * Paramêtres :
 * utilisateur=courriel : retourne les statistiques d'un utilisateur donné.
 *
 * @author		Jean-Pascal MILCENT <jpm@tela-botanica.org>
 * @license		GPL v3 <http://www.gnu.org/licenses/gpl.txt>
 * @license		CECILL v2 <http://www.cecill.info/licences/Licence_CeCILL_V2-en.txt>
 * @version		$Id$
 * @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.
	 */
	public function getElement($ressources) {
		$graph = null;
		$serveur = '';
 
		if (isset($ressources[0])) {
			$this->analyserParametresUrl();
			$stat_demande = array_shift($ressources);
			$methode = 'get'.$stat_demande;
			if (method_exists($this, $methode)) {
				$this->ressources = $ressources;
				$stats = $this->$methode($ressources);
			} else {
				$this->messages[] = "Ce type de statistiques '$stat_demande' n'est pas disponible.";
			}
		} else {
			$this->messages[] = "La ressource du service CEL StatistiqueTxt doit indiquer le type de statistique. Ex. : .../CelStatistiqueTxt/Nombres";
		}
 
		if (!is_null($stats)) {
			$this->envoyerJson($stats);
		}
	}
	
	private function analyserParametresUrl() {
		$this->parametres['utilisateur'] = isset($_GET['utilisateur']) ? $this->bdd->quote($this->verifierSecuriteParametreUrl($_GET['utilisateur'])) : null;
		$this->parametres['num_taxon'] = isset($_GET['num_taxon']) ? $this->bdd->quote($this->verifierSecuriteParametreUrl($_GET['num_taxon'])) : null;
		$this->parametres['taxon'] = isset($_GET['taxon']) ? $this->bdd->quote($this->verifierSecuriteParametreUrl($_GET['taxon'].'%')) : null;
		$this->parametres['tag'] = isset($_GET['tag']) ? $this->verifierSecuriteParametreUrl($_GET['tag']) : null;
		$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 = $this->executerRequete($requete);
			
			if ($resultats != false) {
				foreach ($resultats as $resultat) {
					$liste[$resultat['identifiant']] = $resultat['nbre'];
				}
			}
		}
		return $liste;
	}
	
	private function construireRequeteListeUtilisateurNbrePhoto() {
		$select = 	'SELECT identifiant, COUNT(DISTINCT ci_id_image) AS nbre ';
		$from = 	'FROM cel_inventory '.
					'	LEFT JOIN cel_obs_images ON (coi_ce_observation = ordre AND coi_ce_utilisateur = identifiant) '.
					'	LEFT JOIN cel_images ON (coi_ce_image = ci_id_image) ';
		$where = 	'WHERE transmission = 1 ';
		$groupBy = 	'GROUP BY identifiant ';
		$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[] = "identifiant = $utilisateur ";
			}
			if (isset($num_taxon)) {
				$filtres[] = "num_taxon = $num_taxon ";
			}
			if (isset($taxon)) {
				$filtres[] = "nom_ret LIKE $taxon ";
			}
			if (isset($tag)) {
				$limitation = $this->construireWhereTags();
				if ($limitation != null) {
					$filtres[] = $limitation;
				} else {
					$zero_images = true;
				}
			}
			$where .= ((count($filtres) > 0) ? 'AND '.implode(' AND ', $filtres) : '');
			
			if (isset($start)) {
				$limitSql = str_replace('0,', "$start,", $limitSql);
			}
			if (isset($limit)) {
				$limitSql = str_replace('150', $limit, $limitSql);
			}
		}
		if ($zero_images) {
			$requete = null;
		} else {
			$requete = $select.$from.$where.$groupBy.$orderBy.$limitSql;
			//echo $requete;
		}
		return $requete;
	}
	
	private function construireWhereTags() {
		$where = null;
		if (isset($this->parametres['tag'])) {
			$tag = $this->parametres['tag'];
			$tag_encode = $this->bdd->quote($this->encoderMotCle(trim($tag)));
 
			// Construction de la requête
			$requete = 	'SELECT cmc_id_mot_cle_utilisateur, cmc_id_proprietaire '.
				'FROM cel_mots_cles_images '.
				"WHERE cmc_id_mot_cle_general = $tag_encode ";
			$elements = $this->executerRequete($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 )';
				$requete_where = array();
				foreach ($elements as $occurence) {
					$requete_where[] = sprintf($tpl_where, $occurence['cmc_id_mot_cle_utilisateur'], $this->bdd->quote($occurence['cmc_id_proprietaire']));
				}
				$where = ' ('.implode(" \nOR ", $requete_where).') ';
			} else {
				$this->debug[] = "Aucune image ne correspond à ce mot clé.";
			}
		}
		return $where;
	}
	
	private function getListeTaxonsNbrePhotos() {
		$requete = $this->construireRequeteListeTaxonNbrePhoto();
		$resultats = $this->executerRequete($requete);
		$liste = array();
		if ($resultats != false) {
			foreach ($resultats as $resultat) {
				$liste[$resultat['nom_ret']] = $resultat['nbre'];
			}
		}
		return $liste;
	}
	
	private function construireRequeteListeTaxonNbrePhoto() {
		$select = 	'SELECT nom_ret, COUNT(DISTINCT ci_id_image) AS nbre ';
		$from = 	'FROM cel_inventory '.
					'	LEFT JOIN cel_obs_images ON (coi_ce_observation = ordre AND coi_ce_utilisateur = identifiant) '.
					'	LEFT JOIN cel_images ON (coi_ce_image = ci_id_image) ';
		$where = 	'WHERE transmission = 1 '.
					"	AND nom_ret != '' ";
		$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[] = "identifiant = $utilisateur ";
			}
			if (isset($num_taxon)) {
				$filtres[] = "num_taxon = $num_taxon ";
			}
			if (isset($taxon)) {
				$filtres[] = "nom_ret LIKE $taxon ";
			}
			$where .= ((count($filtres) > 0) ? 'AND '.implode(' AND ', $filtres) : '');
			
			if (isset($start)) {
				$limitSql = str_replace('0,', "$start,", $limitSql);
			}
			if (isset($limit)) {
				$limitSql = str_replace('150', $limit, $limitSql);
			}
		}
		
		$requete = $select.$from.$where.$groupBy.$orderBy.$limitSql;
		return $requete;
	}
	
	private function getNombres() {
 
		$requete = $this->construireRequeteNbreObs();		
		$info['observations'] = (int) $this->executerRequete($requete, 'Column');
		
		$requete = $this->construireRequeteNbreObsPubliques();		
		$info['observationsPubliques'] = (int) $this->executerRequete($requete, 'Column');
		
		$requete = $this->construireRequeteNbreImg();
		$info['images'] =(int) $this->executerRequete($requete, 'Column');
		
		$requete = $this->construireRequeteNbreImgLiees();
		$info['imagesLiees'] =(int) $this->executerRequete($requete, 'Column');
		
		$requete = $this->construireRequeteNbreObsLiees();
		$info['observationsLiees'] = (int) $this->executerRequete($requete, 'Column');
		
		$info['moyImagesParObs'] = ($info['observationsLiees'] > 0 ? round($info['imagesLiees']/$info['observationsLiees'], 2) : '');
		
		$requete = $this->construireRequeteNbreObsParCommune();
		$info['communes'] = ($resultats = $this->executerRequete($requete)) ? count($resultats) : '' ;
		$info['observationsParCommunesMin'] = 1000;
		$info['observationsParCommunesMax'] = 0;
		$info['observationsParCommunesTotal'] = 0;
		foreach ($resultats as $resultat) {
			if ($resultat['nbre'] < $info['observationsParCommunesMin']) {
				$info['observationsParCommunesMin'] = $resultat['nbre'];
			}
			if ($resultat['nbre'] > $info['observationsParCommunesMax']) {
				$info['observationsParCommunesMax'] = $resultat['nbre'];
			}
			$info['observationsParCommunesTotal'] += $resultat['nbre'];
		}
		$info['observationsParCommunesMoyenne'] = ($info['communes'] > 0 ) ? round($info['observationsParCommunesTotal'] / $info['communes'], 2) : 0;
		
		return $info;		
	}
	
	private function construireRequeteNbreObs() {
		$requete = 	'SELECT COUNT(id) AS nbre '.
					'FROM cel_inventory ';
 
		if (count($this->parametres) != 0) {
			$filtres = array();
			extract($this->parametres);
			
			if (isset($utilisateur)) {
				$filtres[] = "identifiant = $utilisateur ";
			}
			if (isset($num_taxon)) {
				$filtres[] = "num_taxon = $num_taxon ";
			}
			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) AS nbre '.
					'FROM cel_inventory '.
					"WHERE transmission = 1 ";
 
		if (count($this->parametres) != 0) {
			$filtres = array();
			extract($this->parametres);
			
			if (isset($utilisateur)) {
				$filtres[] = "identifiant = $utilisateur ";
			}
			if (isset($num_taxon)) {
				$filtres[] = "num_taxon = $num_taxon ";
			}
			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) AS nbre '.
					'FROM cel_inventory '.
					"WHERE location != '000null' ".
					"	AND id_location != '000null' ";
		$groupBy = 'GROUP BY location, id_location';
 
		if (count($this->parametres) != 0) {
			$filtres = array();
			extract($this->parametres);
			
			if (isset($utilisateur)) {
				$filtres[] = "identifiant = $utilisateur ";
			}
			if (isset($num_taxon)) {
				$filtres[] = "num_taxon = $num_taxon ";
			}
			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 ';
		
		if (count($this->parametres) != 0) {
			$filtres = array();
			
			extract($this->parametres);
			
			if (isset($utilisateur)) {
				$filtres[] = "ci_ce_utilisateur = $utilisateur ";
			}
			if (isset($num_taxon)) {
				 
				$filtres[] = "num_taxon = $num_taxon ";
			}
			if (isset($taxon)) {
				$filtres[] = "nom_ret LIKE $taxon ";
			}
			if (isset($num_taxon) || isset($taxon)) {
				$from .= 'LEFT JOIN cel_obs_images ON (coi_ce_image = ci_id_image) '.
						'LEFT JOIN cel_inventory ON (coi_ce_observation = ordre AND coi_ce_utilisateur = identifiant) ';
			}
			
			$where = ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');
		}
		
		$requete = $select.$from.$where;
		return $requete;
	}
	
	private function construireRequeteNbreImgLiees() {
		$select = 'SELECT COUNT(DISTINCT ci_id_image) AS nbre ';
		$from = 'FROM cel_obs_images '.
				'	LEFT JOIN cel_images ON (coi_ce_image = ci_id_image) ';
		
		if (count($this->parametres) != 0) {
			$filtres = array();
			
			extract($this->parametres);
			
			if (isset($utilisateur)) {
				$filtres[] = "coi_ce_utilisateur = $utilisateur ";
			}
			if (isset($num_taxon)) {
				$filtres[] = "num_taxon = $num_taxon ";
			}
			if (isset($taxon)) {
				$filtres[] = "nom_ret LIKE $taxon ";
			}
			
			if (isset($num_taxon) || isset($taxon)) {
				$from .= 'LEFT JOIN cel_inventory ON (coi_ce_observation = ordre AND coi_ce_utilisateur = identifiant) ';
			}
			
			$where = ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');
		}
		
		$requete = $select.$from.$where;
		return $requete;
	}
	
	private function construireRequeteNbreObsLiees() {
		$select = 'SELECT COUNT(DISTINCT coi_ce_observation) AS nbre ';
		$from = 'FROM cel_obs_images '.
				'	LEFT JOIN cel_inventory ON (coi_ce_observation = ordre AND coi_ce_utilisateur = identifiant) ';
		
		if (count($this->parametres) != 0) {
			$filtres = array();
			
			extract($this->parametres);
			
			if (isset($utilisateur)) {
				$filtres[] = "identifiant = $utilisateur ";
			}
			if (isset($num_taxon)) {
				$filtres[] = "num_taxon = $num_taxon ";
			}
			if (isset($taxon)) {
				$filtres[] = "nom_ret LIKE $taxon ";
			}
			
			$where = ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');
		}
		
		$requete = $select.$from.$where;
		
		return $requete;
	}
}
?>

Rev 702	Rev 707
1	`<?php`	1	`<?php`
2	`/**`	2	`/**`
3	`* Service fournissant des statistiques de l'application CEL au format texte (JSON).`	3	`* Service fournissant des statistiques de l'application CEL au format texte (JSON).`
4	`* Encodage en entrée : utf8`	4	`* Encodage en entrée : utf8`
5	`* Encodage en sortie : utf8`	5	`* Encodage en sortie : utf8`
6	`*`	6	`*`
7	`* Cas d'utilisation :`	7	`* Cas d'utilisation :`
8	`* /CelStatistiqueTxt/TypeDeStat : retourne les statistiques demandées`	8	`* /CelStatistiqueTxt/TypeDeStat : retourne les statistiques demandées`
9	`* Paramêtres :`	9	`* Paramêtres :`
10	`* utilisateur=courriel : retourne les statistiques d'un utilisateur donné.`	10	`* utilisateur=courriel : retourne les statistiques d'un utilisateur donné.`
11	`*`	11	`*`
12	`* @author Jean-Pascal MILCENT <jpm@tela-botanica.org>`	12	`* @author Jean-Pascal MILCENT <jpm@tela-botanica.org>`
13	`* @license GPL v3 <http://www.gnu.org/licenses/gpl.txt>`	13	`* @license GPL v3 <http://www.gnu.org/licenses/gpl.txt>`
14	`* @license CECILL v2 <http://www.cecill.info/licences/Licence_CeCILL_V2-en.txt>`	14	`* @license CECILL v2 <http://www.cecill.info/licences/Licence_CeCILL_V2-en.txt>`
15	`* @version $Id$`	15	`* @version $Id$`
16	`* @copyright Copyright (c) 2011, Tela Botanica (accueil@tela-botanica.org)`	16	`* @copyright Copyright (c) 2011, Tela Botanica (accueil@tela-botanica.org)`
17	`*/`	17	`*/`
18	`class CelStatistiqueTxt extends Cel {`	18	`class CelStatistiqueTxt extends Cel {`
19		19
20	`/**`	20	`/**`
21	`* Méthode appelée avec une requête de type GET.`	21	`* Méthode appelée avec une requête de type GET.`
22	`*/`	22	`*/`
23	`public function getElement($ressources) {`	23	`public function getElement($ressources) {`
24	`$graph = null;`	24	`$graph = null;`
25	`$serveur = '';`	25	`$serveur = '';`
26		26
27	`if (isset($ressources[0])) {`	27	`if (isset($ressources[0])) {`
28	`$this->analyserParametresUrl();`	28	`$this->analyserParametresUrl();`
29	`$stat_demande = array_shift($ressources);`	29	`$stat_demande = array_shift($ressources);`
30	`$methode = 'get'.$stat_demande;`	30	`$methode = 'get'.$stat_demande;`
31	`if (method_exists($this, $methode)) {`	31	`if (method_exists($this, $methode)) {`
32	`$this->ressources = $ressources;`	32	`$this->ressources = $ressources;`
33	`$stats = $this->$methode($ressources);`	33	`$stats = $this->$methode($ressources);`
34	`} else {`	34	`} else {`
35	`$this->messages[] = "Ce type de statistiques '$stat_demande' n'est pas disponible.";`	35	`$this->messages[] = "Ce type de statistiques '$stat_demande' n'est pas disponible.";`
36	`}`	36	`}`
37	`} else {`	37	`} else {`
38	`$this->messages[] = "La ressource du service CEL StatistiqueTxt doit indiquer le type de statistique. Ex. : .../CelStatistiqueTxt/Nombres";`	38	`$this->messages[] = "La ressource du service CEL StatistiqueTxt doit indiquer le type de statistique. Ex. : .../CelStatistiqueTxt/Nombres";`
39	`}`	39	`}`
40		40
41	`if (!is_null($stats)) {`	41	`if (!is_null($stats)) {`
42	`$this->envoyerJson($stats);`	42	`$this->envoyerJson($stats);`
43	`}`	43	`}`
44	`}`	44	`}`
45		45
46	`private function analyserParametresUrl() {`	46	`private function analyserParametresUrl() {`
47	`$this->parametres['utilisateur'] = isset($_GET['utilisateur']) ? $this->bdd->quote($this->verifierSecuriteParametreUrl($_GET['utilisateur'])) : null;`	47	`$this->parametres['utilisateur'] = isset($_GET['utilisateur']) ? $this->bdd->quote($this->verifierSecuriteParametreUrl($_GET['utilisateur'])) : null;`
48	`$this->parametres['num_taxon'] = isset($_GET['num_taxon']) ? $this->bdd->quote($this->verifierSecuriteParametreUrl($_GET['num_taxon'])) : null;`	48	`$this->parametres['num_taxon'] = isset($_GET['num_taxon']) ? $this->bdd->quote($this->verifierSecuriteParametreUrl($_GET['num_taxon'])) : null;`
49	`$this->parametres['taxon'] = isset($_GET['taxon']) ? $this->bdd->quote($this->verifierSecuriteParametreUrl($_GET['taxon'].'%')) : null;`	49	`$this->parametres['taxon'] = isset($_GET['taxon']) ? $this->bdd->quote($this->verifierSecuriteParametreUrl($_GET['taxon'].'%')) : null;`
-		50	`$this->parametres['tag'] = isset($_GET['tag']) ? $this->verifierSecuriteParametreUrl($_GET['tag']) : null;`
50	`$this->parametres['start'] = isset($_GET['start']) ? $this->verifierSecuriteParametreUrl($_GET['start']) : null;`	51	`$this->parametres['start'] = isset($_GET['start']) ? $this->verifierSecuriteParametreUrl($_GET['start']) : null;`
51	`$this->parametres['limit'] = isset($_GET['limit']) ? $this->verifierSecuriteParametreUrl($_GET['limit']) : null;`	52	`$this->parametres['limit'] = isset($_GET['limit']) ? $this->verifierSecuriteParametreUrl($_GET['limit']) : null;`
52	`}`	53	`}`
53		54
54	`private function getListeUtilisateursNbrePhotos() {`	55	`private function getListeUtilisateursNbrePhotos() {`
55	`$requete = $this->construireRequeteListeUtilisateurNbrePhoto();`	-
56	`$resultats = $this->executerRequete($requete);`	-
57	`$liste = array();`	56	`$liste = array();`
-		57
-		58	`$requete = $this->construireRequeteListeUtilisateurNbrePhoto();`
-		59	`if ($requete != null) {`
-		60	`$resultats = $this->executerRequete($requete);`
-		61
58	`if ($resultats != false) {`	62	`if ($resultats != false) {`
59	`foreach ($resultats as $resultat) {`	63	`foreach ($resultats as $resultat) {`
60	`$liste[$resultat['identifiant']] = $resultat['nbre'];`	64	`$liste[$resultat['identifiant']] = $resultat['nbre'];`
-		65	`}`
61	`}`	66	`}`
62	`}`	67	`}`
63	`return $liste;`	68	`return $liste;`
64	`}`	69	`}`
65		70
66	`private function construireRequeteListeUtilisateurNbrePhoto() {`	71	`private function construireRequeteListeUtilisateurNbrePhoto() {`
67	`$select = 'SELECT identifiant, COUNT(DISTINCT ci_id_image) AS nbre ';`	72	`$select = 'SELECT identifiant, COUNT(DISTINCT ci_id_image) AS nbre ';`
68	`$from = 'FROM cel_inventory '.`	73	`$from = 'FROM cel_inventory '.`
69	`' LEFT JOIN cel_obs_images ON (coi_ce_observation = ordre AND coi_ce_utilisateur = identifiant) '.`	74	`' LEFT JOIN cel_obs_images ON (coi_ce_observation = ordre AND coi_ce_utilisateur = identifiant) '.`
70	`' LEFT JOIN cel_images ON (coi_ce_image = ci_id_image) ';`	75	`' LEFT JOIN cel_images ON (coi_ce_image = ci_id_image) ';`
71	`$where = 'WHERE transmission = 1 ';`	76	`$where = 'WHERE transmission = 1 ';`
72	`$groupBy = 'GROUP BY identifiant ';`	77	`$groupBy = 'GROUP BY identifiant ';`
73	`$orderBy = 'ORDER BY nbre DESC ';`	78	`$orderBy = 'ORDER BY nbre DESC ';`
74	`$limitSql = 'LIMIT 0,150 ';`	79	`$limitSql = 'LIMIT 0,150 ';`
75		-
-		80
76		81	`$zero_images = false;`
77	`if (count($this->parametres) != 0) {`	82	`if (count($this->parametres) != 0) {`
78	`extract($this->parametres);`	83	`extract($this->parametres);`
79		84
80	`$filtres = array();`	85	`$filtres = array();`
81		86
82	`if (isset($utilisateur)) {`	87	`if (isset($utilisateur)) {`
83	`$filtres[] = "identifiant = $utilisateur ";`	88	`$filtres[] = "identifiant = $utilisateur ";`
84	`}`	89	`}`
85	`if (isset($num_taxon)) {`	90	`if (isset($num_taxon)) {`
86	`$filtres[] = "num_taxon = $num_taxon ";`	91	`$filtres[] = "num_taxon = $num_taxon ";`
87	`}`	92	`}`
88	`if (isset($taxon)) {`	93	`if (isset($taxon)) {`
89	`$filtres[] = "nom_ret LIKE $taxon ";`	94	`$filtres[] = "nom_ret LIKE $taxon ";`
90	`}`	95	`}`
-		96	`if (isset($tag)) {`
-		97	`$limitation = $this->construireWhereTags();`
-		98	`if ($limitation != null) {`
-		99	`$filtres[] = $limitation;`
-		100	`} else {`
-		101	`$zero_images = true;`
-		102	`}`
-		103	`}`
91	`$where .= ((count($filtres) > 0) ? 'AND '.implode(' AND ', $filtres) : '');`	104	`$where .= ((count($filtres) > 0) ? 'AND '.implode(' AND ', $filtres) : '');`
92		105
93	`if (isset($start)) {`	106	`if (isset($start)) {`
94	`$limitSql = str_replace('0,', "$start,", $limitSql);`	107	`$limitSql = str_replace('0,', "$start,", $limitSql);`
95	`}`	108	`}`
96	`if (isset($limit)) {`	109	`if (isset($limit)) {`
97	`$limitSql = str_replace('150', $limit, $limitSql);`	110	`$limitSql = str_replace('150', $limit, $limitSql);`
98	`}`	111	`}`
99	`}`	112	`}`
-		113	`if ($zero_images) {`
-		114	`$requete = null;`
100		115	`} else {`
101	`$requete = $select.$from.$where.$groupBy.$orderBy.$limitSql;`	116	`$requete = $select.$from.$where.$groupBy.$orderBy.$limitSql;`
-		117	`//echo $requete;`
102		118	`}`
103	`return $requete;`	119	`return $requete;`
104	`}`	120	`}`
-		121
-		122	`private function construireWhereTags() {`
-		123	`$where = null;`
-		124	`if (isset($this->parametres['tag'])) {`
-		125	`$tag = $this->parametres['tag'];`
-		126	`$tag_encode = $this->bdd->quote($this->encoderMotCle(trim($tag)));`
-		127
-		128	`// Construction de la requête`
-		129	`$requete = 'SELECT cmc_id_mot_cle_utilisateur, cmc_id_proprietaire '.`
-		130	`'FROM cel_mots_cles_images '.`
-		131	`"WHERE cmc_id_mot_cle_general = $tag_encode ";`
-		132	`$elements = $this->executerRequete($requete);`
-		133
-		134	`if ($elements != false && count($elements) > 0) {`
-		135	`// Pré-construction du where de la requête`
-		136	`$tpl_where = '(ci_meta_mots_cles LIKE "%%%s%%" AND ci_ce_utilisateur = %s )';`
-		137	`$requete_where = array();`
-		138	`foreach ($elements as $occurence) {`
-		139	`$requete_where[] = sprintf($tpl_where, $occurence['cmc_id_mot_cle_utilisateur'], $this->bdd->quote($occurence['cmc_id_proprietaire']));`
-		140	`}`
-		141	`$where = ' ('.implode(" \nOR ", $requete_where).') ';`
-		142	`} else {`
-		143	`$this->debug[] = "Aucune image ne correspond à ce mot clé.";`
-		144	`}`
-		145	`}`
-		146	`return $where;`
-		147	`}`
105		148
106	`private function getListeTaxonsNbrePhotos() {`	149	`private function getListeTaxonsNbrePhotos() {`
107	`$requete = $this->construireRequeteListeTaxonNbrePhoto();`	150	`$requete = $this->construireRequeteListeTaxonNbrePhoto();`
108	`$resultats = $this->executerRequete($requete);`	151	`$resultats = $this->executerRequete($requete);`
109	`$liste = array();`	152	`$liste = array();`
110	`if ($resultats != false) {`	153	`if ($resultats != false) {`
111	`foreach ($resultats as $resultat) {`	154	`foreach ($resultats as $resultat) {`
112	`$liste[$resultat['nom_ret']] = $resultat['nbre'];`	155	`$liste[$resultat['nom_ret']] = $resultat['nbre'];`
113	`}`	156	`}`
114	`}`	157	`}`
115	`return $liste;`	158	`return $liste;`
116	`}`	159	`}`
117		160
118	`private function construireRequeteListeTaxonNbrePhoto() {`	161	`private function construireRequeteListeTaxonNbrePhoto() {`
119	`$select = 'SELECT nom_ret, COUNT(DISTINCT ci_id_image) AS nbre ';`	162	`$select = 'SELECT nom_ret, COUNT(DISTINCT ci_id_image) AS nbre ';`
120	`$from = 'FROM cel_inventory '.`	163	`$from = 'FROM cel_inventory '.`
121	`' LEFT JOIN cel_obs_images ON (coi_ce_observation = ordre AND coi_ce_utilisateur = identifiant) '.`	164	`' LEFT JOIN cel_obs_images ON (coi_ce_observation = ordre AND coi_ce_utilisateur = identifiant) '.`
122	`' LEFT JOIN cel_images ON (coi_ce_image = ci_id_image) ';`	165	`' LEFT JOIN cel_images ON (coi_ce_image = ci_id_image) ';`
123	`$where = 'WHERE transmission = 1 '.`	166	`$where = 'WHERE transmission = 1 '.`
124	`" AND nom_ret != '' ";`	167	`" AND nom_ret != '' ";`
125	`$groupBy = 'GROUP BY nom_ret ';`	168	`$groupBy = 'GROUP BY nom_ret ';`
126	`$orderBy = 'ORDER BY nbre DESC ';`	169	`$orderBy = 'ORDER BY nbre DESC ';`
127	`$limitSql = 'LIMIT 0,150 ';`	170	`$limitSql = 'LIMIT 0,150 ';`
128		171
129		172
130	`if (count($this->parametres) != 0) {`	173	`if (count($this->parametres) != 0) {`
131	`extract($this->parametres);`	174	`extract($this->parametres);`
132		175
133	`$filtres = array();`	176	`$filtres = array();`
134		177
135	`if (isset($utilisateur)) {`	178	`if (isset($utilisateur)) {`
136	`$filtres[] = "identifiant = $utilisateur ";`	179	`$filtres[] = "identifiant = $utilisateur ";`
137	`}`	180	`}`
138	`if (isset($num_taxon)) {`	181	`if (isset($num_taxon)) {`
139	`$filtres[] = "num_taxon = $num_taxon ";`	182	`$filtres[] = "num_taxon = $num_taxon ";`
140	`}`	183	`}`
141	`if (isset($taxon)) {`	184	`if (isset($taxon)) {`
142	`$filtres[] = "nom_ret LIKE $taxon ";`	185	`$filtres[] = "nom_ret LIKE $taxon ";`
143	`}`	186	`}`
144	`$where .= ((count($filtres) > 0) ? 'AND '.implode(' AND ', $filtres) : '');`	187	`$where .= ((count($filtres) > 0) ? 'AND '.implode(' AND ', $filtres) : '');`
145		188
146	`if (isset($start)) {`	189	`if (isset($start)) {`
147	`$limitSql = str_replace('0,', "$start,", $limitSql);`	190	`$limitSql = str_replace('0,', "$start,", $limitSql);`
148	`}`	191	`}`
149	`if (isset($limit)) {`	192	`if (isset($limit)) {`
150	`$limitSql = str_replace('150', $limit, $limitSql);`	193	`$limitSql = str_replace('150', $limit, $limitSql);`
151	`}`	194	`}`
152	`}`	195	`}`
153		196
154	`$requete = $select.$from.$where.$groupBy.$orderBy.$limitSql;`	197	`$requete = $select.$from.$where.$groupBy.$orderBy.$limitSql;`
155	`return $requete;`	198	`return $requete;`
156	`}`	199	`}`
157		200
158	`private function getNombres() {`	201	`private function getNombres() {`
159		202
160	`$requete = $this->construireRequeteNbreObs();`	203	`$requete = $this->construireRequeteNbreObs();`
161	`$info['observations'] = (int) $this->executerRequete($requete, 'Column');`	204	`$info['observations'] = (int) $this->executerRequete($requete, 'Column');`
162		205
163	`$requete = $this->construireRequeteNbreObsPubliques();`	206	`$requete = $this->construireRequeteNbreObsPubliques();`
164	`$info['observationsPubliques'] = (int) $this->executerRequete($requete, 'Column');`	207	`$info['observationsPubliques'] = (int) $this->executerRequete($requete, 'Column');`
165		208
166	`$requete = $this->construireRequeteNbreImg();`	209	`$requete = $this->construireRequeteNbreImg();`
167	`$info['images'] =(int) $this->executerRequete($requete, 'Column');`	210	`$info['images'] =(int) $this->executerRequete($requete, 'Column');`
168		211
169	`$requete = $this->construireRequeteNbreImgLiees();`	212	`$requete = $this->construireRequeteNbreImgLiees();`
170	`$info['imagesLiees'] =(int) $this->executerRequete($requete, 'Column');`	213	`$info['imagesLiees'] =(int) $this->executerRequete($requete, 'Column');`
171		214
172	`$requete = $this->construireRequeteNbreObsLiees();`	215	`$requete = $this->construireRequeteNbreObsLiees();`
173	`$info['observationsLiees'] = (int) $this->executerRequete($requete, 'Column');`	216	`$info['observationsLiees'] = (int) $this->executerRequete($requete, 'Column');`
174		217
175	`$info['moyImagesParObs'] = ($info['observationsLiees'] > 0 ? round($info['imagesLiees']/$info['observationsLiees'], 2) : '');`	218	`$info['moyImagesParObs'] = ($info['observationsLiees'] > 0 ? round($info['imagesLiees']/$info['observationsLiees'], 2) : '');`
176		219
177	`$requete = $this->construireRequeteNbreObsParCommune();`	220	`$requete = $this->construireRequeteNbreObsParCommune();`
178	`$info['communes'] = ($resultats = $this->executerRequete($requete)) ? count($resultats) : '' ;`	221	`$info['communes'] = ($resultats = $this->executerRequete($requete)) ? count($resultats) : '' ;`
179	`$info['observationsParCommunesMin'] = 1000;`	222	`$info['observationsParCommunesMin'] = 1000;`
180	`$info['observationsParCommunesMax'] = 0;`	223	`$info['observationsParCommunesMax'] = 0;`
181	`$info['observationsParCommunesTotal'] = 0;`	224	`$info['observationsParCommunesTotal'] = 0;`
182	`foreach ($resultats as $resultat) {`	225	`foreach ($resultats as $resultat) {`
183	`if ($resultat['nbre'] < $info['observationsParCommunesMin']) {`	226	`if ($resultat['nbre'] < $info['observationsParCommunesMin']) {`
184	`$info['observationsParCommunesMin'] = $resultat['nbre'];`	227	`$info['observationsParCommunesMin'] = $resultat['nbre'];`
185	`}`	228	`}`
186	`if ($resultat['nbre'] > $info['observationsParCommunesMax']) {`	229	`if ($resultat['nbre'] > $info['observationsParCommunesMax']) {`
187	`$info['observationsParCommunesMax'] = $resultat['nbre'];`	230	`$info['observationsParCommunesMax'] = $resultat['nbre'];`
188	`}`	231	`}`
189	`$info['observationsParCommunesTotal'] += $resultat['nbre'];`	232	`$info['observationsParCommunesTotal'] += $resultat['nbre'];`
190	`}`	233	`}`
191	`$info['observationsParCommunesMoyenne'] = ($info['communes'] > 0 ) ? round($info['observationsParCommunesTotal'] / $info['communes'], 2) : 0;`	234	`$info['observationsParCommunesMoyenne'] = ($info['communes'] > 0 ) ? round($info['observationsParCommunesTotal'] / $info['communes'], 2) : 0;`
192		235
193	`return $info;`	236	`return $info;`
194	`}`	237	`}`
195		238
196	`private function construireRequeteNbreObs() {`	239	`private function construireRequeteNbreObs() {`
197	`$requete = 'SELECT COUNT(id) AS nbre '.`	240	`$requete = 'SELECT COUNT(id) AS nbre '.`
198	`'FROM cel_inventory ';`	241	`'FROM cel_inventory ';`
199		242
200	`if (count($this->parametres) != 0) {`	243	`if (count($this->parametres) != 0) {`
201	`$filtres = array();`	244	`$filtres = array();`
202	`extract($this->parametres);`	245	`extract($this->parametres);`
203		246
204	`if (isset($utilisateur)) {`	247	`if (isset($utilisateur)) {`
205	`$filtres[] = "identifiant = $utilisateur ";`	248	`$filtres[] = "identifiant = $utilisateur ";`
206	`}`	249	`}`
207	`if (isset($num_taxon)) {`	250	`if (isset($num_taxon)) {`
208	`$filtres[] = "num_taxon = $num_taxon ";`	251	`$filtres[] = "num_taxon = $num_taxon ";`
209	`}`	252	`}`
210	`if (isset($taxon)) {`	253	`if (isset($taxon)) {`
211	`$filtres[] = "nom_ret LIKE $taxon ";`	254	`$filtres[] = "nom_ret LIKE $taxon ";`
212	`}`	255	`}`
213		256
214	`$requete .= ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');`	257	`$requete .= ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');`
215	`}`	258	`}`
216		259
217	`return $requete;`	260	`return $requete;`
218	`}`	261	`}`
219		262
220	`private function construireRequeteNbreObsPubliques() {`	263	`private function construireRequeteNbreObsPubliques() {`
221	`$requete = 'SELECT COUNT(id) AS nbre '.`	264	`$requete = 'SELECT COUNT(id) AS nbre '.`
222	`'FROM cel_inventory '.`	265	`'FROM cel_inventory '.`
223	`"WHERE transmission = 1 ";`	266	`"WHERE transmission = 1 ";`
224		267
225	`if (count($this->parametres) != 0) {`	268	`if (count($this->parametres) != 0) {`
226	`$filtres = array();`	269	`$filtres = array();`
227	`extract($this->parametres);`	270	`extract($this->parametres);`
228		271
229	`if (isset($utilisateur)) {`	272	`if (isset($utilisateur)) {`
230	`$filtres[] = "identifiant = $utilisateur ";`	273	`$filtres[] = "identifiant = $utilisateur ";`
231	`}`	274	`}`
232	`if (isset($num_taxon)) {`	275	`if (isset($num_taxon)) {`
233	`$filtres[] = "num_taxon = $num_taxon ";`	276	`$filtres[] = "num_taxon = $num_taxon ";`
234	`}`	277	`}`
235	`if (isset($taxon)) {`	278	`if (isset($taxon)) {`
236	`$filtres[] = "nom_ret LIKE $taxon ";`	279	`$filtres[] = "nom_ret LIKE $taxon ";`
237	`}`	280	`}`
238		281
239	`$requete .= ((count($filtres) > 0) ? 'AND '.implode(' AND ', $filtres) : '');`	282	`$requete .= ((count($filtres) > 0) ? 'AND '.implode(' AND ', $filtres) : '');`
240	`}`	283	`}`
241		284
242	`return $requete;`	285	`return $requete;`
243	`}`	286	`}`
244		287
245	`private function construireRequeteNbreObsParCommune() {`	288	`private function construireRequeteNbreObsParCommune() {`
246	`$requete = 'SELECT COUNT(id) AS nbre '.`	289	`$requete = 'SELECT COUNT(id) AS nbre '.`
247	`'FROM cel_inventory '.`	290	`'FROM cel_inventory '.`
248	`"WHERE location != '000null' ".`	291	`"WHERE location != '000null' ".`
249	`" AND id_location != '000null' ";`	292	`" AND id_location != '000null' ";`
250	`$groupBy = 'GROUP BY location, id_location';`	293	`$groupBy = 'GROUP BY location, id_location';`
251		294
252	`if (count($this->parametres) != 0) {`	295	`if (count($this->parametres) != 0) {`
253	`$filtres = array();`	296	`$filtres = array();`
254	`extract($this->parametres);`	297	`extract($this->parametres);`
255		298
256	`if (isset($utilisateur)) {`	299	`if (isset($utilisateur)) {`
257	`$filtres[] = "identifiant = $utilisateur ";`	300	`$filtres[] = "identifiant = $utilisateur ";`
258	`}`	301	`}`
259	`if (isset($num_taxon)) {`	302	`if (isset($num_taxon)) {`
260	`$filtres[] = "num_taxon = $num_taxon ";`	303	`$filtres[] = "num_taxon = $num_taxon ";`
261	`}`	304	`}`
262	`if (isset($taxon)) {`	305	`if (isset($taxon)) {`
263	`$filtres[] = "nom_ret LIKE $taxon ";`	306	`$filtres[] = "nom_ret LIKE $taxon ";`
264	`}`	307	`}`
265		308
266	`$requete .= ((count($filtres) > 0) ? 'AND '.implode(' AND ', $filtres) : '');`	309	`$requete .= ((count($filtres) > 0) ? 'AND '.implode(' AND ', $filtres) : '');`
267	`}`	310	`}`
268	`$requete .= $groupBy;`	311	`$requete .= $groupBy;`
269		312
270	`return $requete;`	313	`return $requete;`
271	`}`	314	`}`
272		315
273	`private function construireRequeteNbreImg() {`	316	`private function construireRequeteNbreImg() {`
274	`$select = 'SELECT COUNT(DISTINCT ci_id_image) AS nbre ';`	317	`$select = 'SELECT COUNT(DISTINCT ci_id_image) AS nbre ';`
275	`$from = 'FROM cel_images ';`	318	`$from = 'FROM cel_images ';`
276		319
277	`if (count($this->parametres) != 0) {`	320	`if (count($this->parametres) != 0) {`
278	`$filtres = array();`	321	`$filtres = array();`
279		322
280	`extract($this->parametres);`	323	`extract($this->parametres);`
281		324
282	`if (isset($utilisateur)) {`	325	`if (isset($utilisateur)) {`
283	`$filtres[] = "ci_ce_utilisateur = $utilisateur ";`	326	`$filtres[] = "ci_ce_utilisateur = $utilisateur ";`
284	`}`	327	`}`
285	`if (isset($num_taxon)) {`	328	`if (isset($num_taxon)) {`
286		329
287	`$filtres[] = "num_taxon = $num_taxon ";`	330	`$filtres[] = "num_taxon = $num_taxon ";`
288	`}`	331	`}`
289	`if (isset($taxon)) {`	332	`if (isset($taxon)) {`
290	`$filtres[] = "nom_ret LIKE $taxon ";`	333	`$filtres[] = "nom_ret LIKE $taxon ";`
291	`}`	334	`}`
292	`if (isset($num_taxon) \|\| isset($taxon)) {`	335	`if (isset($num_taxon) \|\| isset($taxon)) {`
293	`$from .= 'LEFT JOIN cel_obs_images ON (coi_ce_image = ci_id_image) '.`	336	`$from .= 'LEFT JOIN cel_obs_images ON (coi_ce_image = ci_id_image) '.`
294	`'LEFT JOIN cel_inventory ON (coi_ce_observation = ordre AND coi_ce_utilisateur = identifiant) ';`	337	`'LEFT JOIN cel_inventory ON (coi_ce_observation = ordre AND coi_ce_utilisateur = identifiant) ';`
295	`}`	338	`}`
296		339
297	`$where = ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');`	340	`$where = ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');`
298	`}`	341	`}`
299		342
300	`$requete = $select.$from.$where;`	343	`$requete = $select.$from.$where;`
301	`return $requete;`	344	`return $requete;`
302	`}`	345	`}`
303		346
304	`private function construireRequeteNbreImgLiees() {`	347	`private function construireRequeteNbreImgLiees() {`
305	`$select = 'SELECT COUNT(DISTINCT ci_id_image) AS nbre ';`	348	`$select = 'SELECT COUNT(DISTINCT ci_id_image) AS nbre ';`
306	`$from = 'FROM cel_obs_images '.`	349	`$from = 'FROM cel_obs_images '.`
307	`' LEFT JOIN cel_images ON (coi_ce_image = ci_id_image) ';`	350	`' LEFT JOIN cel_images ON (coi_ce_image = ci_id_image) ';`
308		351
309	`if (count($this->parametres) != 0) {`	352	`if (count($this->parametres) != 0) {`
310	`$filtres = array();`	353	`$filtres = array();`
311		354
312	`extract($this->parametres);`	355	`extract($this->parametres);`
313		356
314	`if (isset($utilisateur)) {`	357	`if (isset($utilisateur)) {`
315	`$filtres[] = "coi_ce_utilisateur = $utilisateur ";`	358	`$filtres[] = "coi_ce_utilisateur = $utilisateur ";`
316	`}`	359	`}`
317	`if (isset($num_taxon)) {`	360	`if (isset($num_taxon)) {`
318	`$filtres[] = "num_taxon = $num_taxon ";`	361	`$filtres[] = "num_taxon = $num_taxon ";`
319	`}`	362	`}`
320	`if (isset($taxon)) {`	363	`if (isset($taxon)) {`
321	`$filtres[] = "nom_ret LIKE $taxon ";`	364	`$filtres[] = "nom_ret LIKE $taxon ";`
322	`}`	365	`}`
323		366
324	`if (isset($num_taxon) \|\| isset($taxon)) {`	367	`if (isset($num_taxon) \|\| isset($taxon)) {`
325	`$from .= 'LEFT JOIN cel_inventory ON (coi_ce_observation = ordre AND coi_ce_utilisateur = identifiant) ';`	368	`$from .= 'LEFT JOIN cel_inventory ON (coi_ce_observation = ordre AND coi_ce_utilisateur = identifiant) ';`
326	`}`	369	`}`
327		370
328	`$where = ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');`	371	`$where = ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');`
329	`}`	372	`}`
330		373
331	`$requete = $select.$from.$where;`	374	`$requete = $select.$from.$where;`
332	`return $requete;`	375	`return $requete;`
333	`}`	376	`}`
334		377
335	`private function construireRequeteNbreObsLiees() {`	378	`private function construireRequeteNbreObsLiees() {`
336	`$select = 'SELECT COUNT(DISTINCT coi_ce_observation) AS nbre ';`	379	`$select = 'SELECT COUNT(DISTINCT coi_ce_observation) AS nbre ';`
337	`$from = 'FROM cel_obs_images '.`	380	`$from = 'FROM cel_obs_images '.`
338	`' LEFT JOIN cel_inventory ON (coi_ce_observation = ordre AND coi_ce_utilisateur = identifiant) ';`	381	`' LEFT JOIN cel_inventory ON (coi_ce_observation = ordre AND coi_ce_utilisateur = identifiant) ';`
339		382
340	`if (count($this->parametres) != 0) {`	383	`if (count($this->parametres) != 0) {`
341	`$filtres = array();`	384	`$filtres = array();`
342		385
343	`extract($this->parametres);`	386	`extract($this->parametres);`
344		387
345	`if (isset($utilisateur)) {`	388	`if (isset($utilisateur)) {`
346	`$filtres[] = "identifiant = $utilisateur ";`	389	`$filtres[] = "identifiant = $utilisateur ";`
347	`}`	390	`}`
348	`if (isset($num_taxon)) {`	391	`if (isset($num_taxon)) {`
349	`$filtres[] = "num_taxon = $num_taxon ";`	392	`$filtres[] = "num_taxon = $num_taxon ";`
350	`}`	393	`}`
351	`if (isset($taxon)) {`	394	`if (isset($taxon)) {`
352	`$filtres[] = "nom_ret LIKE $taxon ";`	395	`$filtres[] = "nom_ret LIKE $taxon ";`
353	`}`	396	`}`
354		397
355	`$where = ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');`	398	`$where = ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');`
356	`}`	399	`}`
357		400
358	`$requete = $select.$from.$where;`	401	`$requete = $select.$from.$where;`
359		402
360	`return $requete;`	403	`return $requete;`
361	`}`	404	`}`
362	`}`	405	`}`
363	`?>`	406	`?>`

Subversion Repositories eFlore/Applications.cel

(root)/trunk/jrest/services/CelStatistiqueTxt.php @ 3616 – Rev 702 → 707