Rev 2016 | Rev 2018 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed
<?php/*** Statistiques par année sur l'utilisation de Identiplante / Pictoflora** @see Documentation : <http://www.tela-botanica.org/wikini/DevInformatiques/wakka.php?wiki=AppliDelStats>** @category DEL* @package Services* @subpackage Statistiques* @version 0.1* @author Mathias CHOUET <mathias@tela-botanica.org>* @author Jean-Pascal MILCENT <jpm@tela-botanica.org>* @author Aurelien PERONNET <aurelien@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>* @copyright 1999-2014 Tela Botanica <accueil@tela-botanica.org>*/class StatistiquesParAnnee {private $conteneur;private $contexte;private $navigation;private $bdd;private $annee = null;private $type = 'tout';private $methode = '';public function __construct(Conteneur $conteneur = null) {$this->conteneur = $conteneur == null ? new Conteneur() : $conteneur;$this->contexte = $conteneur->getContexte();$this->navigation = $conteneur->getNavigation();$this->bdd = $this->conteneur->getBdd();}public function consulter() {$this->intitialiserParametresEtRessources();$this->verifierPreRequis();$resultat = new ResultatService();$resultat->corps = call_user_func(array($this, $this->methode));return $resultat;}private function intitialiserParametresEtRessources() {$this->type = $this->contexte->getRessource(2) != null ? $this->contexte->getRessource(2) : $this->type;$this->methode = $this->obtenirNomMethode($this->type);$this->annee =(int) $this->contexte->getQS('annee') != null ? intval($this->contexte->getQS('annee')) : null;}private function verifierPreRequis() {$erreurs = array();if ($this->annee != null && !is_int($this->annee)) {$erreurs[] = "Le paramètre 'annee' doit être un entier.";}if (method_exists($this, $this->obtenirNomMethode($this->type)) === false) {$erreurs[] = "Les stats de type '{$this->type}' n'existent pas.";}if (!empty($erreurs)) {$msg = "Erreur de configuration :\n".implode("\n", $erreurs)."\n\n".Statistiques::getDoc();throw new Exception($msg, RestServeur::HTTP_CODE_MAUVAISE_REQUETE);}}/*** Ouh la jolie méthode magique !!* @param unknown $mot* @return string*/private function obtenirNomMethode($mot) {$classeNom = 'get'.str_replace(' ', '', ucwords(strtolower(str_replace('-', ' ', $mot))));return $classeNom;}/*** Prend en paramêtre un tableau de courriels et retourne après avoir intérogé un service we de l'annuaire* une tableau avec en clé le courriel et en valeur, un tableau associatif :* - nom : le nom de l'utilisateur* - prenom : le prénom de l'utilisateur.* @TODO ne gère pas le pseudo, qui devrait être retourné en lieu et place des nom / prénom s'il est utilisé et renseigné* @param array $courriels un tableau de courriels pour lesquels il faut recherche le prénom et nom.*/protected function recupererUtilisateursNomPrenom(Array $courriels) {// Récupération des données au format Json$service = "utilisateur/prenom-nom-par-courriel/".implode(',', $courriels);$url = sprintf($this->config['chemins']['baseURLServicesAnnuaireTpl'], $service);$json = $this->getDao()->consulter($url);return (array) json_decode($json);}/*** Statistiques sur les observations* "MPM" = moyenne par mois* "TC" = tag à déterminer ou certitude incertaine* - Nombre d'observations au total* - Nombre d'obs sans identification (ou tag ou certitude)* - Nombre d'obs sans identification* - Nombre d'obs avec tag à déterminer ou certitude incertaine* - Nombre d'obs avec tag à déterminer ou certitude incertaine et retenue* - Nombre d'obs avec tag à déterminer ou certitude incertaine et avec consensus* - Nombre d'obs avec tag à déterminer ou certitude incertaine et avec consensus mais non validées*/private function getObservations() {return array('nbObsTotal' => $this->getNbObsTotal(),'nbObsTotalMPM' => $this->getNbObsTotal(true),'nbObsSansIdentOuTC' => $this->getNbObsSansIdentOuTC(),'nbObsSansIdentOuTCMPM' => $this->getNbObsSansIdentOuTC(true),'nbObsSansIdent' => $this->getNbObsSansIdent(),'nbObsSansIdentMPM' => $this->getNbObsSansIdent(true),'nbObsTC' => $this->getNbObsTC(),'nbObsTCMPM' => $this->getNbObsTC(true),'nbObsTCRetenue' => $this->getNbObsTCRetenue(),'nbObsTCRetenueMPM' => $this->getNbObsTCRetenue(true),'nbObsTCConsensus' => $this->getNbObsTCConsensus(),'nbObsTCConsensusMPM' => $this->getNbObsTCConsensus(true),'nbObsTCConsensusNonValide' => $this->getNbObsTCConsensusNonValide(),'nbObsTCConsensusNonValideMPM' => $this->getNbObsTCConsensusNonValide(true),);}/*** Nombre d'observations au total*/private function getNbObsTotal($mpm=false) {$requete = "SELECT COUNT(id_observation) AS nb_total FROM del_observation";if ($this->annee != null) {$requete .= ' WHERE YEAR(date_transmission) = ' . $this->annee;}if ($mpm) {$requete = $this->encapsulerMPM($requete, 'date_transmission');}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Nombre d'obs sans identification (ou tag ou certitude)*/private function getNbObsSansIdentOuTC($mpm=false) {$requete = "SELECT COUNT(id_observation) AS nb_total FROM del_observation WHERE";if ($this->annee != null) {$requete .= ' YEAR(date_transmission) = ' . $this->annee . " AND";}$requete .= " (`mots_cles_texte` LIKE '%determiner%' OR `nom_sel_nn` = '' OR certitude IN ('aDeterminer','douteux'))";if ($mpm) {$requete = $this->encapsulerMPM($requete, 'date_transmission');}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Nombre d'obs sans identification*/private function getNbObsSansIdent($mpm=false) {$requete = "SELECT COUNT(id_observation) AS nb_total FROM del_observation WHERE";if ($this->annee != null) {$requete .= ' YEAR(date_transmission) = ' . $this->annee . " AND";}$requete .= " nom_sel_nn = ''";if ($mpm) {$requete = $this->encapsulerMPM($requete, 'date_transmission');}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Nombre d'obs avec tag à déterminer ou certitude incertaine*/private function getNbObsTC($mpm=false) {$requete = "SELECT COUNT(id_observation) AS nb_total FROM del_observation WHERE";if ($this->annee != null) {$requete .= ' YEAR(date_transmission) = ' . $this->annee . " AND";}$requete .= " (mots_cles_texte LIKE '%determiner%' OR certitude IN ('aDeterminer','douteux'))";if ($mpm) {$requete = $this->encapsulerMPM($requete, 'date_transmission');}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Nombre d'obs avec tag à déterminer ou certitude incertaine et retenue*/private function getNbObsTCRetenue($mpm=false) {$requete = "SELECT COUNT(*) AS nb_total FROM del_commentaire WHERE proposition_retenue = 1 AND ce_observation IN (SELECT id_observation FROM del_observation WHERE";if ($this->annee != null) {$requete .= ' YEAR(date_transmission) = ' . $this->annee . " AND";}$requete .= " (certitude IN ('aDeterminer','douteux')))";if ($mpm) {// @TODO vérifier que grouper sur "date" est pertinent// date_transmission n'est pas dispo pour grouper ici :-/$requete = $this->encapsulerMPM($requete);}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Nombre d'obs avec tag à déterminer ou certitude incertaine et avec consensus*/private function getNbObsTCConsensus($mpm=false) {$requete = "SELECT COUNT(id_observation) AS nb_total FROM del_observation WHERE";if ($this->annee != null) {$requete .= ' YEAR(date_transmission) = ' . $this->annee . " AND";}$requete .= " (mots_cles_texte LIKE '%determiner%' OR certitude IN ('aDeterminer','douteux')) AND id_observation IN". " (SELECT ce_observation FROM del_commentaire WHERE proposition_initiale = 1". " AND nom_sel_nn != 0". " AND nom_sel_nn IS NOT NULL". " AND id_commentaire IN (SELECT ce_proposition FROM del_commentaire_vote dcv";if ($this->annee != null) {$requete .= ' WHERE YEAR(date) <= ' . $this->annee;}$requete .= " GROUP BY ce_proposition HAVING SUM(CASE". " WHEN valeur = 1 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' != 0 THEN 3". " WHEN valeur = 0 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' != 0 THEN -3". " WHEN valeur = 1 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' = 0 THEN 1". " WHEN valeur = 0 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' = 0 THEN -1". " END) >= 4))";if ($mpm) {$requete = $this->encapsulerMPM($requete, 'date_transmission');}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Nombre d'obs avec tag à déterminer ou certitude incertaine et avec consensus mais non validées*/private function getNbObsTCConsensusNonValide($mpm=false) {$requete = "SELECT COUNT(id_observation) AS nb_total FROM del_observation WHERE";if ($this->annee != null) {$requete .= ' YEAR(date_transmission) = ' . $this->annee . " AND";}$requete .= " (mots_cles_texte LIKE '%determiner%' OR certitude IN ('aDeterminer','douteux')) AND id_observation IN". " (SELECT ce_observation FROM del_commentaire WHERE proposition_initiale = 1 AND proposition_retenue = 0". " AND nom_sel_nn != 0". " AND nom_sel_nn IS NOT NULL". " AND id_commentaire IN (SELECT ce_proposition FROM del_commentaire_vote dcv";if ($this->annee != null) {$requete .= ' WHERE YEAR(date) <= ' . $this->annee;}$requete .= " GROUP BY ce_proposition HAVING SUM(CASE". " WHEN valeur = 1 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' != 0 THEN 3". " WHEN valeur = 0 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' != 0 THEN -3". " WHEN valeur = 1 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' = 0 THEN 1". " WHEN valeur = 0 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' = 0 THEN -1". " END) >= 4))";if ($mpm) {$requete = $this->encapsulerMPM($requete, 'date_transmission');}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Statistiques sur les propositions, les commentaires et les votes* "MPM" = moyenne par mois* - Nombre de votes* - Nombre de commentaires* - Nombre d'actions par jour* - Nombres de propositions sur toutes les obs* - Nombres de propositions retenues sur toutes les obs* - Nombres de propositions en consensus sur toutes les obs* - Nombres de propositions sur les obs d'une année* - Nombres de propositions retenues sur les obs d'une année* - Nombres de propositions en consensus sur les obs d'une année*/private function getPropositions() {return array('nbVotes' => $this->getNbVotes(),'nbVotesMPM' => $this->getNbVotes(true),'nbMoyenActionsParJour' => $this->getNbMoyenActionsParJour(),'nbCommentaires' => $this->getNbCommentaires(),'nbCommentairesMPM' => $this->getNbCommentaires(true),'nbPropositionsToutesObs' => $this->getNbPropositionsToutesObs(),'nbPropositionsToutesObsMPM' => $this->getNbPropositionsToutesObs(true),'nbPropositionsRetenuesToutesObs' => $this->getNbPropositionsRetenuesToutesObs(),'nbPropositionsRetenuesToutesObsMPM' => $this->getNbPropositionsRetenuesToutesObs(true),'nbPropositionsConsensusToutesObs' => $this->getNbPropositionsConsensusToutesObs(),'nbPropositionsConsensusToutesObsMPM' => $this->getNbPropositionsConsensusToutesObs(true),'nbPropositionsObsAnnee' => $this->getNbPropositionsObsAnnee(),'nbPropositionsObsAnneeMPM' => $this->getNbPropositionsObsAnnee(true),'nbPropositionsRetenuesObsAnnee' => $this->getNbPropositionsRetenuesObsAnnee(),'nbPropositionsRetenuesObsAnneeMPM' => $this->getNbPropositionsRetenuesObsAnnee(true),'nbPropositionsConsensusObsAnnee' => $this->getNbPropositionsConsensusObsAnnee(),'nbPropositionsConsensusObsAnneeMPM' => $this->getNbPropositionsConsensusObsAnnee(true),);}/*** Nombre moyen d'actions par jour (commentaire, proposition, vote)*/private function getNbMoyenActionsParJour() {$requete = "SELECT (SELECT (SELECT count(*) FROM del_commentaire";if ($this->annee != null) {$requete .= ' WHERE YEAR(date) = ' . $this->annee;}$requete .= ") + (SELECT count(*) FROM del_commentaire_vote";if ($this->annee != null) {$requete .= ' WHERE YEAR(date) = ' . $this->annee;}$requete .= ") + (SELECT count(*) FROM del_commentaire";if ($this->annee != null) {$requete .= ' WHERE YEAR(date_validation) = ' . $this->annee;}$requete .= ")) / (";if ($this->annee != null) {$requete .= "SELECT IF( YEAR(CURDATE()) = " . $this->annee . ", DAYOFYEAR(CURDATE()), 365) ";} else {$requete .= "365";}$requete .= ")";$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Nombre de proposition faites sur toutes les observations*/private function getNbPropositionsToutesObs($mpm=false) {$requete = "SELECT COUNT(DISTINCT `id_commentaire`) AS nb_total FROM `del_commentaire` WHERE";if ($this->annee != null) {$requete .= ' YEAR(date) = ' . $this->annee . " AND";}$requete .= " (`nom_sel_nn` IS NOT NULL OR `nom_sel_nn` != '') AND `proposition_initiale` = 0";if ($mpm) {$requete = $this->encapsulerMPM($requete);}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Nombre de proposition retenues faites sur toutes les observations*/private function getNbPropositionsRetenuesToutesObs($mpm=false) {$requete = "SELECT COUNT(DISTINCT id_commentaire) AS nb_total FROM del_commentaire WHERE";if ($this->annee != null) {$requete .= ' YEAR(date) = ' . $this->annee . " AND";}$requete .= " proposition_retenue = 1";if ($mpm) {$requete = $this->encapsulerMPM($requete);}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Nombre de proposition en consensus faites sur toutes les observations*/private function getNbPropositionsConsensusToutesObs($mpm=false) {$requete = "SELECT COUNT(DISTINCT ce_observation) AS nb_total FROM del_commentaire dc WHERE". " dc.proposition_retenue = 1". " OR (dc.proposition_initiale = 1". " AND dc.nom_sel_nn != 0". " AND dc.nom_sel_nn IS NOT NULL". " AND dc.id_commentaire IN". " (SELECT ce_proposition FROM del_commentaire_vote dcv WHERE";if ($this->annee != null) {$requete .= " year(date) < " . $this->annee . " AND";}$requete .= " ce_proposition NOT IN (SELECT ce_proposition FROM del_commentaire_vote dcv";if ($this->annee != null) {$requete .= " WHERE year(date) < " . ($this->annee - 1);}$requete .= " GROUP BY ce_proposition". " HAVING SUM(CASE". " WHEN valeur = 1 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' != 0 THEN 3". " WHEN valeur = 0 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' != 0 THEN -3". " WHEN valeur = 1 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' = 0 THEN 1". " WHEN valeur = 0 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' = 0 THEN -1". " END) >= 4)". " GROUP BY ce_proposition". " HAVING SUM(CASE". " WHEN valeur = 1 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' != 0 THEN 3". " WHEN valeur = 0 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' != 0 THEN -3". " WHEN valeur = 1 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' = 0 THEN 1". " WHEN valeur = 0 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' = 0 THEN -1". " END) >= 4". " )". " )";if ($mpm) {$requete = $this->encapsulerMPM($requete);}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Nombre de proposition faites sur les observations d'une année*/private function getNbPropositionsObsAnnee($mpm=false) {if ($this->annee == null) {return null;}$requete = "SELECT COUNT(DISTINCT `id_commentaire`) AS nb_total FROM `del_commentaire` WHERE YEAR(date) = " . $this->annee . " AND ". "(`nom_sel_nn` IS NOT NULL OR `nom_sel_nn` != '') AND `proposition_initiale` = 0 AND ce_observation in". " (SELECT `id_observation` FROM `del_observation` WHERE year(date_transmission) = " . $this->annee . ")";if ($mpm) {$requete = $this->encapsulerMPM($requete);}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Nombre de proposition retenues faites sur les observations d'une année*/private function getNbPropositionsRetenuesObsAnnee($mpm=false) {if ($this->annee == null) {return null;}$requete = "SELECT COUNT(DISTINCT `id_commentaire`) AS nb_total FROM `del_commentaire` WHERE YEAR(date) = " . $this->annee . " AND ". "`proposition_retenue` = 1 AND ce_observation in (SELECT `id_observation` FROM `del_observation` WHERE year(date_transmission) = " . $this->annee . ")";if ($mpm) {$requete = $this->encapsulerMPM($requete);}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Nombre de proposition en consensus faites sur les observations d'une année*/private function getNbPropositionsConsensusObsAnnee($mpm=false) {if ($this->annee == null) {return null;}$requete = "SELECT COUNT(DISTINCT ce_observation) AS nb_total FROM del_commentaire dc WHERE ce_observation in (". " SELECT `id_observation` FROM `del_observation` WHERE year(date_transmission) = " . $this->annee . ") AND (". " dc.proposition_retenue = 1 OR (dc.proposition_initiale = 1". " AND dc.nom_sel_nn != 0 AND dc.nom_sel_nn IS NOT NULL AND dc.id_commentaire IN". " (SELECT ce_proposition FROM del_commentaire_vote dcv". " WHERE year(date) <= " . $this->annee. " GROUP BY ce_proposition". " HAVING SUM(CASE". " WHEN valeur = 1 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' != 0 THEN 3". " WHEN valeur = 0 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' != 0 THEN -3". " WHEN valeur = 1 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' = 0 THEN 1". " WHEN valeur = 0 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' = 0 THEN -1". " END) >= 4)". "))";if ($mpm) {$requete = $this->encapsulerMPM($requete);}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Nombre de votes*/private function getNbVotes($mpm=false) {$requete = "SELECT COUNT(DISTINCT `id_vote`) AS nb_total FROM del_commentaire_vote";if ($this->annee != null) {$requete .= ' WHERE YEAR(date) = ' . $this->annee;}if ($mpm) {$requete = $this->encapsulerMPM($requete);}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Nombre de commentaires*/private function getNbCommentaires($mpm=false) {$requete = "SELECT COUNT(DISTINCT `id_commentaire`) AS nb_total FROM del_commentaire WHERE `ce_proposition` != '' AND (`nom_sel` IS NULL OR `nom_sel` = '')";if ($this->annee != null) {$requete .= ' AND YEAR(date) = ' . $this->annee;}if ($mpm) {$requete = $this->encapsulerMPM($requete);}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Statistiques sur les utilisateurs d'Identiplante* "AF" = ayant fait un(e)* "MPM" = moyenne par mois* - Nombre total d'utilisateurs* - Nombre d'utilisateurs ayant fait une proposition* - Nombre d'utilisateurs identifiés ayant fait un vote* - Nombre d'utilisateurs anonymes ayant fait un vote* - Nombre d'utilisateurs ayant fait un commentaire* - Nombre d'utilisateurs ayant fait une action*/private function getUtilisateursIp() {return array('nbUtilisateursTotal' => $this->getNbUtilisateursIpTotal(),'nbUtilisateursAFProposition' => $this->getNbUtilisateursAFProposition(),'nbUtilisateursAFCommentaire' => $this->getNbUtilisateursAFCommentaire(),'nbUtilisateursAFVote' => $this->getNbUtilisateursAFVote(),'nbUtilisateursAnonymesAFVote' => $this->getNbUtilisateursAnonymesAFVote(),'nbUtilisateursAFAction' => $this->getNbUtilisateursAFAction(),'nbUtilisateursAFPropositionMPM' => $this->getNbUtilisateursAFProposition(true),'nbUtilisateursAFCommentaireMPM' => $this->getNbUtilisateursAFCommentaire(true),'nbUtilisateursAFVoteMPM' => $this->getNbUtilisateursAFVote(true),'nbUtilisateursAnonymesAFVoteMPM' => $this->getNbUtilisateursAnonymesAFVote(true),'nbUtilisateursAFActionMPM' => $this->getNbUtilisateursAFActionMPM());}/*** Nombre total d'utilisateurs d'Identiplante*/private function getNbUtilisateursIpTotal() {$requete = "SELECT COUNT(DISTINCT ce_utilisateur) AS nb_total FROM del_commentaire";if ($this->annee != null) {$requete .= ' WHERE YEAR(date) = ' . $this->annee;}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Nombre d'utilisateurs d'Identiplante ayant fait au moins une proposition*/private function getNbUtilisateursAFProposition($mpm=false) {$requete = "SELECT COUNT(DISTINCT `utilisateur_courriel`) AS nb_total FROM del_commentaire WHERE `ce_proposition` = '' AND `nom_sel` IS NOT NULL AND `nom_sel` != ''";if ($this->annee != null) {$requete .= ' AND YEAR(date) = ' . $this->annee;}if ($mpm) {$requete = $this->encapsulerMPM($requete);}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Nombre d'utilisateurs d'Identiplante ayant fait au moins un commentaire*/private function getNbUtilisateursAFCommentaire($mpm=false) {$requete = "SELECT COUNT(DISTINCT `utilisateur_courriel`) AS nb_total FROM del_commentaire WHERE `ce_proposition` != '' AND (`nom_sel` IS NULL OR `nom_sel` = '')";if ($this->annee != null) {$requete .= ' AND YEAR(date) = ' . $this->annee;}if ($mpm) {$requete = $this->encapsulerMPM($requete);}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Nombre d'utilisateurs d'Identiplante identifiés prenant part aux votes*/private function getNbUtilisateursAFVote($mpm=false) {$requete = "SELECT COUNT(DISTINCT ce_utilisateur) AS nb_total FROM del_commentaire_vote WHERE ce_utilisateur REGEXP '^-?[0-9]+$'";if ($this->annee != null) {$requete .= ' AND YEAR(date) = ' . $this->annee;}if ($mpm) {$requete = $this->encapsulerMPM($requete);}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Nombre d'utilisateurs d'Identiplante anonymes prenant part aux votes*/private function getNbUtilisateursAnonymesAFVote($mpm=false) {$requete = "SELECT COUNT(DISTINCT ce_utilisateur) AS nb_total FROM del_commentaire_vote WHERE ce_utilisateur NOT REGEXP '^-?[0-9]+$'";if ($this->annee != null) {$requete .= ' AND YEAR(date) = ' . $this->annee;}if ($mpm) {$requete = $this->encapsulerMPM($requete);}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Nombre d'utilisateurs d'Identiplante ayant fait une action (commentaire, vote, proposition)*/private function getNbUtilisateursAFAction() {$requete = "SELECT COUNT(*) AS nb_total FROM (SELECT ce_utilisateur FROM del_commentaire_vote";if ($this->annee != null) {$requete .= ' WHERE YEAR(date) = ' . $this->annee;}$requete .= " UNION SELECT ce_utilisateur FROM del_commentaire";if ($this->annee != null) {$requete .= ' WHERE YEAR(date) = ' . $this->annee;}$requete .= " ) AS action";$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Moyenne par mois du nombre d'utilisateurs d'Identiplante ayant fait une action* (commentaire, vote, proposition) -> n'est pas encapsulable par encapsulerMPM()*/private function getNbUtilisateursAFActionMPM() {$requete = "SELECT avg(nb_total) FROM (SELECT count(*) as nb_total FROM". " (SELECT * FROM (SELECT ce_utilisateur, date FROM del_commentaire_vote";if ($this->annee != null) {$requete .= ' WHERE YEAR(date) = ' . $this->annee;}$requete .= " UNION SELECT ce_utilisateur, date FROM del_commentaire";if ($this->annee != null) {$requete .= ' WHERE YEAR(date) = ' . $this->annee;}$requete .= " ) AS action GROUP BY ce_utilisateur) AS utildate GROUP BY CONCAT(year(date),month(date))) as truc";$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Liste des utilisateurs dont les propositions ont été votées positivement*/private function getListeMeilleursProposeurs() {$liste = array();$requete = "SELECT * FROM (SELECT utilisateur_courriel, count(prop) as nb_prop". " FROM (SELECT `ce_proposition` as prop, COUNT(DISTINCT `id_vote`) AS nb_vote FROM del_commentaire_vote where";if ($this->annee != null) {$requete .= " year(`date`) = " . $this->annee . " AND";}$requete .= " valeur = 1 GROUP BY `ce_proposition`) AS vote, del_commentaire WHERE nb_vote > 3 AND prop = id_commentaire AND `ce_utilisateur` != 0 GROUP BY `ce_utilisateur`)". " AS utlisateurs WHERE nb_prop > 10 ORDER BY nb_prop DESC LIMIT 20";$resultat = $this->bdd->recupererTous($requete);// Formater liste (récupérer les pseudos des gens dans l'annuaire etc. => voir widget CEL stats)// => faut-il le faire ici ou dans le widget ? :-/ plutôt ici non ?// ...return array('liste' => $resultat);}/*** Liste des utilisateurs ayant fait le plus de votes positifs* @TODO et le plus de votes sur des propositions retenues (ou ayant atteint un consensus)*/private function getListeMeilleursVoteurs() {$liste = array();$requete = "SELECT * FROM (SELECT courriel, COUNT(DISTINCT `id_vote`) AS nombre FROM del_commentaire_vote, del_utilisateur where";if ($this->annee != null) {$requete .= " year(`date`) = " . $this->annee . " AND";}$requete .= " ce_utilisateur = id_utilisateur AND valeur = 1 GROUP BY `ce_utilisateur`) AS utilisateurs WHERE nombre > 100 ORDER BY nombre DESC LIMIT 20";$resultat = $this->bdd->recupererTous($requete);// Formater liste (récupérer les pseudos des gens dans l'annuaire etc. => voir widget CEL stats)// => faut-il le faire ici ou dans le widget ? :-/ plutôt ici non ?// ...return array('liste' => $resultat);}/*** Liste des utilisateurs ayant fait au moins une proposition par mois toute l'année*/private function getListeProposeursReguliers() {$liste = array();$requete = "SELECT cal.nbmois, SUM(somme) / cal.nbmois as moyenne, ce_utilisateur, utilisateur_courriel FROM (SELECT count(*) as somme,". " CONCAT(YEAR(date),'-',MONTH(date)) as anneemois, ce_utilisateur, utilisateur_courriel, id_commentaire FROM del_commentaire". " WHERE ce_proposition = '' AND nom_sel_nn != '' AND nom_sel_nn IS NOT NULL";if ($this->annee != null) {$requete .= " AND year(`date`) = " . $this->annee;}$requete .= " GROUP BY anneemois, ce_utilisateur, utilisateur_courriel) as ppm, (SELECT count(distinct CONCAT(YEAR(date),'-',MONTH(date))) as nbmois FROM del_commentaire WHERE";if ($this->annee != null) {$requete .= " year(`date`) = " . $this->annee . " AND";}$requete .= " ce_proposition = '' AND nom_sel_nn != '' AND nom_sel_nn IS NOT NULL) as cal GROUP BY ce_utilisateur, utilisateur_courriel HAVING SUM(somme) / cal.nbmois >= 1". " ORDER BY moyenne DESC"; // @ TODO limite ?$resultat = $this->bdd->recupererTous($requete);// Formater liste (récupérer les pseudos des gens dans l'annuaire etc. => voir widget CEL stats)// => faut-il le faire ici ou dans le widget ? :-/ plutôt ici non ?// ...return array('liste' => $resultat);}/*** Statistiques sur Sauvages de ma Rue (Sdmr)* "MPM" = moyenne par mois* - Nombre total d'observations sdmr* - Nombre d'observations Sauvages de PACA*/private function getSdmr() {return array('nbObsSdmrTotal' => $this->getNbObsSdmrTotal(),'nbObsSdmrTotalMPM' => $this->getNbObsSdmrTotal(true),'nbObsSdmrPACA' => $this->getNbObsSdmrPACA(),'nbObsSdmrPACAMPM' => $this->getNbObsSdmrPACA(true));}/*** Nombre total d'observations Sauvages de ma Rue*/private function getNbObsSdmrTotal($mpm=false) {$requete = "SELECT COUNT(DISTINCT ce_observation) as nb_total FROM del_commentaire WHERE";if ($this->annee != null) {$requete .= ' YEAR(date) <= ' . $this->annee . ' AND';}$requete .= " ce_observation in (SELECT id_observation FROM del_observation WHERE mots_cles_texte like '%sauvages%')";if ($mpm) {$requete = $this->encapsulerMPM($requete);}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Nombre total d'observations Sauvages de ma Rue en PACA*/private function getNbObsSdmrPACA($mpm=false) {$requete = "SELECT COUNT(DISTINCT ce_observation) as nb_total FROM del_commentaire WHERE";if ($this->annee != null) {$requete .= ' YEAR(date) <= ' . $this->annee . ' AND';}$requete .= " ce_observation in (SELECT id_observation FROM del_observation". " WHERE SUBSTR(ce_zone_geo, 9 , 2) IN (13,04,05,06,83,84) AND mots_cles_texte like '%sauvages%')";if ($mpm) {$requete = $this->encapsulerMPM($requete);}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Statistiques sur les images de Pictoflora* "MPM" = Moyenne par mois* - Nombre total d'images* - Nombre d'images ayant au moins un mot-clé* - Nombre d'images ayant au moins un vote* - Nombre d'images de l'année ayant au moins un mot-clé* - Nombre d'images de l'année ayant au moins un vote*/private function getImages() {return array('nbImagesTotal' => $this->getNbImagesTotal(),'nbImagesTotalMPM' => $this->getNbImagesTotal(true),'nbImagesAyantTag' => $this->getNbImagesAyantTag(),'nbImagesAyantTagMPM' => $this->getNbImagesAyantTag(true),'nbImagesAyantVote' => $this->getNbImagesAyantVote(),'nbImagesAyantVoteMPM' => $this->getNbImagesAyantVote(true),'nbImagesAnneeAyantTag' => $this->getNbImagesAnneeAyantTag(),'nbImagesAnneeAyantTagMPM' => $this->getNbImagesAnneeAyantTag(true),'nbImagesAnneeAyantVote' => $this->getNbImagesAnneeAyantVote(),'nbImagesAnneeAyantVoteMPM' => $this->getNbImagesAnneeAyantVote(true),);}/*** Nombre total d'images Pictoflora*/private function getNbImagesTotal($mpm=false) {$requete = "SELECT COUNT(*) AS nb_total FROM del_image";if ($this->annee != null) {$requete .= ' WHERE YEAR(date_transmission) = ' . $this->annee;}if ($mpm) {$requete = $this->encapsulerMPM($requete, 'date_transmission');}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Nombre total d'images Pictoflora ayant au moins un mot-clé*/private function getNbImagesAyantTag($mpm=false) {$requete = "SELECT COUNT(DISTINCT ce_image) AS nb_total FROM del_image_tag";if ($this->annee != null) {$requete .= ' WHERE YEAR(date) = ' . $this->annee;}if ($mpm) {$requete = $this->encapsulerMPM($requete);}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Nombre total d'images Pictoflora ayant au moins un vote*/private function getNbImagesAyantVote($mpm=false) {$requete = "SELECT COUNT(DISTINCT ce_image) AS nb_total FROM del_image_vote";if ($this->annee != null) {$requete .= ' WHERE YEAR(date) = ' . $this->annee;}if ($mpm) {$requete = $this->encapsulerMPM($requete);}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Nombre total d'images Pictoflora de l'année ayant au moins un mot-clé*/private function getNbImagesAnneeAyantTag($mpm=false) {if ($this->annee == null) {return null;}$requete = "SELECT COUNT(DISTINCT ce_image) AS nb_total FROM del_image_tag WHERE YEAR(date) = " . $this->annee. " AND ce_image IN (SELECT id_image FROM del_image WHERE year(date_transmission) = " . $this->annee . ")";if ($mpm) {$requete = $this->encapsulerMPM($requete);}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Nombre total d'images Pictoflora de l'année ayant au moins un vote*/private function getNbImagesAnneeAyantVote($mpm=false) {if ($this->annee == null) {return null;}$requete = "SELECT COUNT(DISTINCT ce_image) AS nb_total FROM del_image_vote WHERE YEAR(date) = " . $this->annee. " AND ce_image IN (SELECT id_image FROM del_image WHERE year(date_transmission) = " . $this->annee . ")";if ($mpm) {$requete = $this->encapsulerMPM($requete);}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Statistiques sur les votes et mots-clés (tags) de Pictoflora* "MPM" = moyenne par mois* - Nombre total de votes* - Nombre total de mots clés* - Nombre d'images ayant le mot-clé "defiPhoto"*/private function getTagsVotes() {return array('nbTagsTotal' => $this->getNbTagsTotal(),'nbTagsTotalMPM' => $this->getNbTagsTotal(true),'nbVotesTotal' => $this->getNbVotesTotal(),'nbVotesTotalMPM' => $this->getNbVotesTotal(true),// devra être paramétré par une liste de mots-clés//'nbImagesTagDP' => $this->getNbImagesTagDP(),//'nbImagesTagDPMPM' => $this->getNbImagesTagDP(true));}/*** Nombre total de mots clés Pictoflora*/private function getNbTagsTotal($mpm=false) {$requete = "SELECT COUNT(*) AS nb_total FROM del_image_tag";if ($this->annee != null) {$requete .= ' WHERE YEAR(date) = ' . $this->annee;}if ($mpm) {$requete = $this->encapsulerMPM($requete);}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Nombre total de votes Pictoflora*/private function getNbVotesTotal($mpm=false) {$requete = "SELECT COUNT(*) AS nb_total FROM del_image_vote";if ($this->annee != null) {$requete .= ' WHERE YEAR(date) = ' . $this->annee;}if ($mpm) {$requete = $this->encapsulerMPM($requete);}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Statistiques sur les utilisateurs de Pictoflora* "MPM" = Moyenne par mois* "AF" = Ayant fait* - Nombre d'utilisateurs ayant ajouté un mot-clé* - Nombre d'utilisateurs ayant voté* - Nombre d'utilisateurs ayant fait une action*/private function getUtilisateursPf() {return array('nbUtilisateursAFTag' => $this->getNbUtilisateursPfAFTag(),'nbUtilisateursAFTagMPM' => $this->getNbUtilisateursPfAFTag(true),'nbUtilisateursAFVote' => $this->getNbUtilisateursPfAFVote(),'nbUtilisateursAFVoteMPM' => $this->getNbUtilisateursPfAFVote(true),'nbUtilisateursAFAction' => $this->getNbUtilisateursPfAFAction(),'nbUtilisateursAFActionMPM' => $this->getNbUtilisateursPfAFActionMPM());}/*** Nombre d'utilisateurs ayant ajouté un mot-clé*/private function getNbUtilisateursPfAFTag($mpm=false) {$requete = "SELECT COUNT(DISTINCT ce_utilisateur) AS nb_total FROM del_image_tag";if ($this->annee != null) {$requete .= ' WHERE YEAR(date) = ' . $this->annee;}if ($mpm) {$requete = $this->encapsulerMPM($requete);}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Nombre d'utilisateurs ayant fait un vote*/private function getNbUtilisateursPfAFVote($mpm=false) {$requete = "SELECT COUNT(DISTINCT ce_utilisateur) AS nb_total FROM del_image_vote";if ($this->annee != null) {$requete .= ' WHERE YEAR(date) = ' . $this->annee;}if ($mpm) {$requete = $this->encapsulerMPM($requete);}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Nombre d'utilisateurs ayant fait une action*/private function getNbUtilisateursPfAFAction() {$requete = "SELECT COUNT(*) AS nb_total FROM (SELECT ce_utilisateur FROM del_image_tag";if ($this->annee != null) {$requete .= ' WHERE YEAR(date) = ' . $this->annee;}$requete .= " UNION SELECT ce_utilisateur FROM del_image_vote";if ($this->annee != null) {$requete .= ' WHERE YEAR(date) = ' . $this->annee;}$requete .= ") AS action";$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Moyenne par mois du nombre d'utilisateurs ayant fait une action* -> n'est pas encapsulable par encapsulerMPM()*/private function getNbUtilisateursPfAFActionMPM() {$requete = "SELECT avg(nb_total) FROM (SELECT COUNT(*) AS nb_total FROM (SELECT * FROM (SELECT ce_utilisateur, date FROM del_image_tag";if ($this->annee != null) {$requete .= ' WHERE YEAR(date) = ' . $this->annee;}$requete .= " UNION SELECT ce_utilisateur, date FROM del_image_vote";if ($this->annee != null) {$requete .= ' WHERE YEAR(date) = ' . $this->annee;}$requete .= ") AS action GROUP BY ce_utilisateur) AS utildate GROUP BY CONCAT(year(date),month(date))) as truc";$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Liste des mots-clés les plus fréquents*/private function getListeMeilleursTags() {$liste = array();$requete = "SELECT count(*) as occurrences, tag FROM del_image_tag";if ($this->annee != null) {$requete .= " WHERE YEAR(date) = " . $this->annee;}$requete .= " GROUP BY tag ORDER BY occurrences DESC LIMIT 20";$resultat = $this->bdd->recupererTous($requete);return array('liste' => $resultat);}/*** @TODO Liste des utilisateurs ayant ajouté le plus de mots-clés*/private function getListeMeilleursTagueurs() {$resultat = array();/*$requete = "SELECT * FROM (SELECT utilisateur_courriel, count(prop) as nb_prop". " FROM (SELECT `ce_proposition` as prop, COUNT(DISTINCT `id_vote`) AS nb_vote FROM del_commentaire_vote where";if ($this->annee != null) {$requete .= " year(`date`) = " . $this->annee . " AND";}$requete .= " valeur = 1 GROUP BY `ce_proposition`) AS vote, del_commentaire WHERE nb_vote > 3 AND prop = id_commentaire AND `ce_utilisateur` != 0 GROUP BY `ce_utilisateur`)". " AS utlisateurs WHERE nb_prop > 10 ORDER BY nb_prop DESC LIMIT 20";$resultat = $this->bdd->recupererTous($requete);// Formater liste (récupérer les pseudos des gens dans l'annuaire etc. => voir widget CEL stats)// => faut-il le faire ici ou dans le widget ? :-/ plutôt ici non ?// ...*/return array('liste' => $resultat);}/*** Encapsule une reqûete de comptage dans un autre morceau de requête* afin de calculer la moyenne par mois* @param string $requete count() qui doit renvoyer une colonne 'nb_total'*/protected function encapsulerMPM($requete, $colonne="date") {$requeteEncapsulee = "SELECT AVG(nb_total) as moyenne FROM (". $requete. " GROUP BY CONCAT(year($colonne),month($colonne)) ) AS nombre";return $requeteEncapsulee;}}