Rev 2013 | Rev 2017 | 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) {$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*/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);}/*** @TODO Statistiques sur les images de Pictoflora* @REFLEXION aucune idée de ce qu'on met dedans*/private function getImages() {return array('annee' => $this->annee);}/*** @TODO Statistiques sur les mots-clés de Pictoflora* @REFLEXION aucune idée de ce qu'on met dedans*/private function getTags() {return array('annee' => $this->annee);}/*** @TODO Statistiques sur les utilisateurs de Pictoflora* - Nombre total d'utilisateurs* - Nombre d'utilisateurs réguliers* - Nombre d'utilisateurs prenant part au taggage ?* - Nombre de participants actifs pour le mois, la semaine, l'année*/private function getUtilisateursPf() {$nbUtilisateursActifs = $this->getNbUtilisateursPfActifs();return array('annee' => $this->annee,'nbUtilisateursTotal' => $this->getNbUtilisateursPfTotal(),'nbUtilisateursReguliers' => $this->getNbUtilisateursPfReguliers(),'nbUtilisateursTaggant' => $this->getNbUtilisateursPfTaggant(),'nbUtilisateursActifsAnnee' => $nbUtilisateursActifs['annee'],'nbUtilisateursActifsMois' => $nbUtilisateursActifs['mois'],'nbUtilisateursActifsSemaine' => $nbUtilisateursActifs['semaine']);}/*** @TODO Nombre total d'utilisateurs de Pictoflora* @REFLEXION est-ce le même nombre que les utilisateurs d'Identiplante ?*/private function getNbUtilisateursPfTotal() {$requete = "";$resultat = $this->bdd->recupererTous($requete);return intval($resultat[0][0]);}/*** @TODO Nombre d'utilisateurs réguliers de Pictoflora* @REFLEXION quelle différence avec getNbUtilisateursPfActifs() ?*/private function getNbUtilisateursPfReguliers() {$requete = "";$resultat = $this->bdd->recupererTous($requete);return intval($resultat[0][0]);}/*** @TODO Nombre d'utilisateurs de Pictoflora prenant part aux votes*/private function getNbUtilisateursPfTaggant() {$requete = "";$resultat = $this->bdd->recupererTous($requete);return intval($resultat[0][0]);}/*** @TODO Nombre de participants à Pictoflora actifs (pour le mois, la semaine, l'année)* @REFLEXION retourner un array ? Si on demande une année spécifique, comment calculer* l'activité du mois et de la semaine (on met la case à null ?)*/private function getNbUtilisateursPfActifs() {$requete = "";$resultat = $this->bdd->recupererTous($requete);return intval($resultat[0][0]);}/*** @TODO Liste des mots-clés les plus fréquents*/private function getListeMeilleursTags() {$liste = array();$requete = "";$resultat = $this->bdd->recupererTous($requete);// Formater liste// ...return array('annee' => $this->annee,'listeMeilleursTags' => $liste);}/*** @TODO Liste des utilisateurs ayant ajouté le plus de mots-clés*/private function getListeMeilleursTagueurs() {$liste = array();$requete = "";$resultat = $this->bdd->recupererTous($requete);// Formater liste (récupérer les pseudos des gens dans l'annuaire etc. => voir service CEL stats)// => faut-il le faire ici ou dans le widget ? :-/ plutôt ici non ?// ...return array('annee' => $this->annee,'listeMeilleursTagueurs' => $liste);}/*** 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;}/* ---------------- OBSOLETE -------------// Retourne le nombre moyen d'observations non identifiées envoyées par mois, pour l'année $anneeprivate function getMoyenneObsSansNomParMois() {$sqlTableTmp = "SELECT COUNT(*) AS compte, "." CONCAT(YEAR(date_transmission),'-',MONTH(date_transmission)) AS anneemois "."FROM del_observation "."WHERE (mots_cles_texte LIKE '%aDeterminer%' "."OR certitude = 'aDeterminer' "."OR certitude = 'douteux' ".// Obs n'ayant pas de nom_sel_nn (détermination non choisie parmi le référentiel)"OR nom_sel_nn IS NULL "."OR nom_sel_nn = 0 "."OR id_observation IN ({$this->getSqlObsSansNom()}) ".') '.(($this->annee !== null) ? "AND YEAR(date_transmission) = '{$this->annee}' " : '').'GROUP BY anneemois '.'ORDER BY anneemois DESC ';$requete = "SELECT AVG(parMois.compte) AS moyenne FROM ($sqlTableTmp) AS parMois ".' -- '.__FILE__.' : '.__LINE__;$resultat = $this->bdd->recupererTous($requete);return intval($resultat[0]['moyenne']);}private function getSqlObsSansNom() {$sqlObsSansNom = "SELECT DISTINCT ce_observation "."FROM del_commentaire "."WHERE proposition_initiale = 1 "."AND (nom_sel_nn IS NULL OR nom_sel_nn = '') ";return $sqlObsSansNom;}// Retourne la moyenne par mois sur l'année en cours, des propositions marquées comme "retenues"// dont le dernier vote est dans l'année considérée (comptées en groupant par mois du dernier vote)private function getMoyenneObsIdentifieesParMois() {// Compte et date du dernier vote des propositions marquées comme "retenues"$sqlTableTmp1 = "SELECT COUNT(*), MAX(dcv.date) AS maxdate "."FROM del_commentaire AS dc "." LEFT JOIN del_commentaire_vote dcv ON dcv.ce_proposition = dc.id_commentaire "." WHERE proposition_retenue = 1 "." GROUP BY dc.id_commentaire ".(($this->annee !== null) ? "HAVING MAX(YEAR(dcv.date)) = '{$this->annee}' " : '');$sqlTableTmp2 = 'SELECT COUNT(*) AS valideesparmois, '." CONCAT(YEAR(maxdate), '-', MONTH(maxdate)) AS anneemois "."FROM ($sqlTableTmp1) AS temp "."GROUP BY anneemois ";$requete = "SELECT AVG(valideesparmois) AS moyenne FROM ($sqlTableTmp2) AS temp2 ".' -- '.__FILE__.' : '.__LINE__;$resultat = $this->bdd->recupererTous($requete);return intval($resultat[0]['moyenne']);}// Version améliorée mais non optimale (prend en compte les consensus non validés)// @TODO on devrait croiser les IDS pour ne pas prendre en compte les obs validées ou en// consensus, mais qui datent des années précédentes// @ACHTUNG mache pas, dépasse les 100% (voir Wiki)private function getPourcentageObsIdentifieesFinAnneePlus() {// Obs ayant atteint un consensus cette année$requete = "SELECT COUNT(*) AS nombre "."FROM (SELECT id_observation, id_commentaire, id_vote, nbvotes "."FROM (SELECT do.id_observation, dc.id_commentaire, dcv.id_vote, COUNT(dcv.id_vote) AS nbvotes "."FROM del_commentaire AS dc "." LEFT JOIN del_observation AS do ON (do.id_observation = dc.ce_observation) "." LEFT JOIN del_commentaire_vote AS dcv ON (dc.id_commentaire = dcv.ce_proposition) "."AND dcv.valeur = 1 "."AND dc.proposition_retenue = 0 "."GROUP BY dc.id_commentaire ".(($this->annee != null) ? " HAVING MAX(YEAR(dcv.date)) = '{$this->annee}' " : '')." ) AS temp "."GROUP BY id_observation ".") AS temp2 ".' -- '.__FILE__.' : '.__LINE__;$obsEnConsensus = $this->bdd->recupererTous($requete);$oc = intval($obsEnConsensus[0]['nombre']);// Obs ayant une "proposition retenue" cette année$requete = "SELECT COUNT(*) AS nombre "."FROM (SELECT COUNT(DISTINCT id_observation), MAX(dcv.date) AS maxdate "."FROM del_commentaire AS dc "." LEFT JOIN del_commentaire_vote AS dcv ON (dcv.ce_proposition = dc.id_commentaire) "." LEFT JOIN del_observation AS do ON (do.id_observation = dc.ce_observation) "."WHERE proposition_retenue = 1 ".(($this->annee != null) ? "AND YEAR(do.date_transmission) = '{$this->annee}' " : '')."GROUP BY dc.id_commentaire ".(($this->annee != null) ? "HAVING MAX(YEAR(dcv.date)) = '{$this->annee}' " : '').") as temp ".' -- '.__FILE__.' : '.__LINE__;$nbObsValidees = $this->bdd->recupererTous($requete);$ov = intval($nbObsValidees[0]['nombre']);// Nombre d'obs sans nom soumises cette année$requete = "SELECT COUNT(*) AS nombre "."FROM del_observation "."WHERE (mots_cles_texte LIKE '%aDeterminer%' "."OR certitude = 'aDeterminer' "."OR certitude = 'douteux' "."OR nom_sel_nn IS NULL "."OR nom_sel_nn = 0 "."OR id_observation IN ({$this->getSqlObsSansNom()})".') '.(($this->annee != null) ? "AND YEAR(date_transmission) = '{$this->annee}' " : '').' -- '.__FILE__.' : '.__LINE__;$nbObsSansNom = $this->bdd->recupererTous($requete);$osn = intval($nbObsSansNom[0]['nombre']);return array('observationsEnConsensus' => $oc,'observationsValidees' => $ov,'observationsSansNom' => $osn,'pourcentage' => ($osn == 0 ? 0 : round(((($oc + $ov) / $osn) * 100), 2)));}private function getPourcentageObsIdentifieesFinAnnee() {$requete = "SELECT ( "."SELECT COUNT(*) FROM ( "."SELECT COUNT(DISTINCT id_observation), MAX(dcv.date) AS maxdate "."FROM del_commentaire AS dc "." LEFT JOIN del_commentaire_vote AS dcv ON (dcv.ce_proposition = dc.id_commentaire) "." LEFT JOIN del_observation AS do ON (do.id_observation = dc.ce_observation) "."WHERE proposition_retenue = 1 ".(($this->annee != null) ? "AND YEAR(do.date_transmission) = '{$this->annee}' " : '')."GROUP BY dc.id_commentaire ".(($this->annee != null) ? "HAVING MAX(YEAR(dcv.date)) = '{$this->annee}' " : '').") AS temp)"." / "."(SELECT COUNT(*) "."FROM del_observation "."WHERE (mots_cles_texte LIKE '%aDeterminer%' "."OR certitude = 'aDeterminer' "."OR certitude = 'douteux' "."OR nom_sel_nn IS NULL "."OR nom_sel_nn = 0 "."OR id_observation IN ( "."SELECT DISTINCT ce_observation "."FROM del_commentaire "."WHERE proposition_initiale = 1 "."AND (nom_sel_nn IS NULL OR nom_sel_nn = '') ".") ".") ".(($this->annee != null) ? "AND YEAR(date_transmission) = '{$this->annee}' " : '').") * 100 AS pourcentage ".' -- '.__FILE__.' : '.__LINE__;$resultat = $this->bdd->recupererTous($requete);return floatval($resultat[0]['pourcentage']);}// Retourne la moyenne sur l'année du nombre d'actions (commentaire ou vote) par jourprivate function getMoyenneActionsParJour() {// nombre de commentaires sur l'année$sqlNbreCommentaires = 'SELECT COUNT(*) FROM del_commentaire '.($this->annee != null ? "WHERE YEAR(date) = '{$this->annee}' " : '');// nombre de votes sur l'année$sqlNbreVotes = 'SELECT COUNT(*) FROM del_commentaire_vote '.($this->annee != null ? "WHERE YEAR(date) = '{$this->annee}' " : '');// nombre de jours écoulés dans l'année*$sqlNbreJours = "SELECT 365 * (YEAR(now()) - MIN(YEAR(date)) + 1) FROM del_commentaire_vote WHERE YEAR(date) != 0 ";if ($this->annee != null) {$sqlNbreJours = "SELECT IF(YEAR(CURDATE()) = '{$this->annee}', DAYOFYEAR(CURDATE()), 365) ";}// nombre d'actions / nombre de jours$requete = "SELECT ((($sqlNbreCommentaires) + ($sqlNbreVotes)) / ($sqlNbreJours)) AS moyenne ".' -- '.__FILE__.' : '.__LINE__;$resultat = $this->bdd->recupererTous($requete);return intval($resultat[0]['moyenne']);}// Retourne le nombre et la liste des personnes ayant sur l'année une moyenne de participation par mois >= 1private function getParticipants() {// Faire la moyenne par utilisateur et par mois$requete = "SELECT cal.nbmois, SUM(somme) / cal.nbmois as moyenne, ce_utilisateur, utilisateur_courriel "."FROM ".// Compter le nombre de participations pour chaque utilisateur à chaque mois de cette année"(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 ".(($this->annee != null) ? " AND YEAR(date) = '{$this->annee}' " : '')."GROUP BY anneemois, ce_utilisateur, utilisateur_courriel) AS ppm, ".// Trouver le nombre de mois différents lors desquels les utilisateurs ont participé, cette année// Pour l'année en cours par ex, retournera 2 si on est en février (voire un au début du mois)."(SELECT COUNT(distinct CONCAT(YEAR(date),'-',MONTH(date))) AS nbmois "."FROM del_commentaire "."WHERE ce_proposition = '' ".(($this->annee != null) ? "AND YEAR(date) = '{$this->annee}' " : '')."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 ".' -- '.__FILE__.' : '.__LINE__;$resultat = $this->bdd->recupererTous($requete);$cpt = count($resultat);$retour = array('nombre' => intval($cpt),'donnees' => $resultat);return $retour;}--------------- FIN OBSOLETE -------------- */}