<?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']) ? 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;
		$this->parametres['taxon'] = isset($_GET['taxon']) ? Cel::db()->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 = Cel::db()->executerRequete($requete);		
			if ($resultats != false) {
				foreach ($resultats as $resultat) {
					$liste[$resultat['courriel_utilisateur']] = $resultat['nbre'];
				}
			}
		}
		return $liste;
	}
	
	private function construireRequeteListeUtilisateurNbrePhoto() {
		$select = 	'SELECT co.courriel_utilisateur, COUNT(DISTINCT ci.id_image) AS nbre ';
		$from = 	'FROM cel_obs co '.
					'	LEFT JOIN cel_obs_images coi ON (coi.id_observation = co.id_observation) '.
					'	LEFT JOIN cel_images ci ON (coi.id_image = ci.id_image) ';
		$where = 	'WHERE transmission = 1 ';
		$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 ";
			}
			if (isset($num_taxon)) {
				$filtres[] = "nt = $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 = Cel::db()->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 = Cel::db()->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'], Cel::db()->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 = Cel::db()->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_obs co '.
					'	LEFT JOIN cel_obs_images coi ON (coi.id_observation = co.id_observation) '.
					'	LEFT JOIN cel_images ci ON (coi.id_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[] = "co.courriel_utilisateur = $utilisateur ";
			}
			if (isset($num_taxon)) {
				$filtres[] = "nt = $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) Cel::db()->executerRequete($requete, 'Column');
		
		$requete = $this->construireRequeteNbreObsPubliques();		
		$info['observationsPubliques'] = (int) Cel::db()->executerRequete($requete, 'Column');
		
		$requete = $this->construireRequeteNbreImg();
		$info['images'] =(int) Cel::db()->executerRequete($requete, 'Column');
		
		$requete = $this->construireRequeteNbreImgLiees();
		$info['imagesLiees'] =(int) Cel::db()->executerRequete($requete, 'Column');
		
		$requete = $this->construireRequeteNbreObsLiees();
		$info['observationsLiees'] = (int) Cel::db()->executerRequete($requete, 'Column');
		
		$info['moyImagesParObs'] = ($info['observationsLiees'] > 0 ? round($info['imagesLiees']/$info['observationsLiees'], 2) : '');
		
		$requete = $this->construireRequeteNbreObsParCommune();
		$info['communes'] = ($resultats = Cel::db()->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_observation) AS nbre '.
					'FROM cel_obs ';

		if (count($this->parametres) != 0) {
			$filtres = array();
			extract($this->parametres);
			
			if (isset($utilisateur)) {
				$filtres[] = "courriel_utilisateur = $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_observation) AS nbre '.
					'FROM cel_obs '.
					"WHERE transmission = 1 ";

		if (count($this->parametres) != 0) {
			$filtres = array();
			extract($this->parametres);
			
			if (isset($utilisateur)) {
				$filtres[] = "courriel_utilisateur = $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_observation) AS nbre '.
					'FROM cel_obs '.
					"WHERE zone_geo IS NOT NULL ".
					"	AND ce_zone_geo IS NOT NULL ";
		$groupBy = 'GROUP BY zone_geo, ce_zone_geo';

		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)) {
				$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)) {
				$filtres[] = "nom_ret LIKE $taxon ";
			}
			if (isset($num_taxon) || isset($taxon)) {
				$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;

		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 ";
			}
			if (isset($num_taxon)) {
				$filtres[] = "nt = $num_taxon ";
			}
			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 ";
			}
			if (isset($num_taxon)) {
				$filtres[] = "nt = $num_taxon ";
			}
			if (isset($taxon)) {
				$filtres[] = "nom_ret LIKE $taxon ";
			}
			
			$where = ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');
		}
		
		$requete = $select.$from.$where;
		
		return $requete;
	}
}
?>