Rev 2178 | 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;}/*** 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* - Nombre d'observations ayant une proposition retenue (parmi les observations de l'année)* - Nombre d'observations ayant une proposition retenue ou en consensus (parmi les observations de l'année)*/private function getObservations() {$nbObsTotal = $this->getNbObsTotal();$nbObsSansIdentOuTC = $this->getNbObsSansIdentOuTC();$nbObsSansIdent = $this->getNbObsSansIdent();$nbObsTC = $this->getNbObsTC();$nbObsTCRetenue = $this->getNbObsTCRetenue();$nbObsTCConsensus = $this->getNbObsTCConsensus();$nbObsTCConsensusNonValide = $this->getNbObsTCConsensusNonValide();$nbPropositionsRetenuesObsAnnee = $this->getNbPropositionsRetenuesObsAnnee();$nbPropositionsConsensusObsAnnee = $this->getNbPropositionsConsensusObsAnnee();return array('nbObsTotal' => $nbObsTotal,'nbObsTotalMPM' => $this->getNbObsTotal(true),'nbObsSansIdentOuTC' => $nbObsSansIdentOuTC,'nbObsSansIdentOuTCMPM' => $this->getNbObsSansIdentOuTC(true),'nbObsSansIdentOuTCPC' => $nbObsTotal == 0 ? 0 : round(($nbObsSansIdentOuTC / $nbObsTotal) * 100, 2),'nbObsSansIdent' => $nbObsSansIdent,'nbObsSansIdentMPM' => $this->getNbObsSansIdent(true),'nbObsSansIdentPC' => $nbObsTotal == 0 ? 0 : round(($nbObsSansIdent / $nbObsTotal) * 100, 2),'nbObsTC' => $nbObsTC,'nbObsTCMPM' => $this->getNbObsTC(true),'nbObsTCPC' => $nbObsTotal == 0 ? 0 : round(($nbObsTC / $nbObsTotal) * 100, 2),'nbObsTCRetenue' => $nbObsTCRetenue,'nbObsTCRetenueMPM' => $this->getNbObsTCRetenue(true),'nbObsTCRetenuePC' => $nbObsTotal == 0 ? 0 : round(($nbObsTCRetenue / $nbObsTotal) * 100, 2),'nbObsTCConsensus' => $nbObsTCConsensus,'nbObsTCConsensusMPM' => $this->getNbObsTCConsensus(true),'nbObsTCConsensusPC' => $nbObsTotal == 0 ? 0 : round(($nbObsTCConsensus / $nbObsTotal) * 100, 2),'nbObsTCConsensusNonValide' => $nbObsTCConsensusNonValide,'nbObsTCConsensusNonValideMPM' => $this->getNbObsTCConsensusNonValide(true),'nbObsTCConsensusNonValidePC' => $nbObsTotal == 0 ? 0 : round(($nbObsTCConsensusNonValide / $nbObsTotal) * 100, 2),'nbPropositionsRetenuesObsAnnee' => $nbPropositionsRetenuesObsAnnee,'nbPropositionsRetenuesObsAnneeMPM' => $this->getNbPropositionsRetenuesObsAnnee(true),'nbPropositionsRetenuesObsAnneePC' => $nbObsTotal == 0 ? 0 : round(($nbPropositionsRetenuesObsAnnee / $nbObsTotal) * 100, 2),'nbPropositionsConsensusObsAnnee' => $nbPropositionsConsensusObsAnnee,'nbPropositionsConsensusObsAnneeMPM' => $this->getNbPropositionsConsensusObsAnnee(true),'nbPropositionsConsensusObsAnneePC' => $nbObsTotal == 0 ? 0 : round(($nbPropositionsConsensusObsAnnee / $nbObsTotal) * 100, 2),);}/*** 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 nom_sel_nn IS NULL 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 = '' OR nom_sel_nn IS NULL)";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* - Nombre d'observations ayant une proposition retenue (parmi toutes les observations)* - Nombre d'observations ayant une proposition retenue ou en consensus (parmi toutes les observations)* - Nombre de propositions 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),);}/*** 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 propositions faites sur toutes les observations (hors initiales)*/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 d'observations ayant une proposition retenue (parmi toutes les observations)*/private function getNbPropositionsRetenuesToutesObs($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 .= " proposition_retenue = 1";if ($mpm) {$requete = $this->encapsulerMPM($requete);}$resultat = $this->bdd->recupererTous($requete);return intval(array_pop($resultat[0]));}/*** Nombre d'observations ayant une proposition retenue ou en consensus (parmi 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 d'observations ayant une proposition retenue (parmi les observations de l'année)*/private function getNbPropositionsObsAnnee($mpm=false) {if ($this->annee == null) {return null;}$requete = "SELECT COUNT(DISTINCT ce_observation) 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 d'observations ayant une proposition retenue ou en consensus (parmi les observations de l'année)*/private function getNbPropositionsRetenuesObsAnnee($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 .= "proposition_retenue = 1 AND ce_observation in (SELECT id_observation FROM del_observation ";if ($this->annee != null) {$requete .= "WHERE year(date_transmission) = " . $this->annee;}$requete .= ")";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) {$requete = "SELECT COUNT(DISTINCT ce_observation) AS nb_total FROM del_commentaire dc WHERE ce_observation in (". " SELECT id_observation FROM del_observation ";if ($this->annee != null) {$requete .= "WHERE year(date_transmission) = " . $this->annee ;}$requete .= ") 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";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);}$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, ce_utilisateur, 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);// Formatage de la liste avec les intitulés des utilisateurs$ids = array_column($resultat, 'ce_utilisateur');$ids = array_filter($ids, 'is_numeric'); // on oublie les ids de session et autres facéties$infosUtilisateurs = $this->recupererIntitulesUtilisateursParIds($ids, true);foreach ($resultat as &$util) {$ce = $util['ce_utilisateur'];$util['intitule'] = isset($infosUtilisateurs[$ce]['intitule']) ? $infosUtilisateurs[$ce]['intitule'] : null;}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, ce_utilisateur, COUNT(DISTINCT id_vote) AS nombre FROM del_commentaire_vote, del_utilisateur_infos 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);// Formatage de la liste avec les intitulés des utilisateurs$ids = array_column($resultat, 'ce_utilisateur');$ids = array_filter($ids, 'is_numeric'); // on oublie les ids de session et autres facéties$infosUtilisateurs = $this->recupererIntitulesUtilisateursParIds($ids, true);foreach ($resultat as &$util) {$ce = $util['ce_utilisateur'];$util['intitule'] = isset($infosUtilisateurs[$ce]['intitule']) ? $infosUtilisateurs[$ce]['intitule'] : null;}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);// Formatage de la liste avec les intitulés des utilisateurs$ids = array_column($resultat, 'ce_utilisateur');$ids = array_filter($ids, 'is_numeric'); // on oublie les ids de session et autres facéties$infosUtilisateurs = $this->recupererIntitulesUtilisateursParIds($ids, true);foreach ($resultat as &$util) {$ce = $util['ce_utilisateur'];$util['intitule'] = isset($infosUtilisateurs[$ce]['intitule']) ? $infosUtilisateurs[$ce]['intitule'] : null;}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);}/*** Liste des utilisateurs ayant ajouté le plus de mots-clés*/private function getListeMeilleursTagueurs() {$resultat = array();$requete = "SELECT count(*) as nombre, IF (ce_utilisateur REGEXP '^-?[0-9]+$' OR ce_utilisateur REGEXP '^.+@.+$', ce_utilisateur, null) as ce_util FROM del_image_tag";if ($this->annee != null) {$requete .= " WHERE year(date) = " . $this->annee;}$requete .= " GROUP BY ce_util ORDER BY nombre DESC LIMIT 20";$resultat = $this->bdd->recupererTous($requete);// Formatage de la liste avec les intitulés des utilisateurs$ids = array_column($resultat, 'ce_util');$ids = array_filter($ids, 'is_numeric'); // on oublie les ids de session et autres facéties$infosUtilisateurs = $this->recupererIntitulesUtilisateursParIds($ids, true);foreach ($resultat as &$util) {$ce = $util['ce_util'];if (isset($infosUtilisateurs[$ce]['intitule'])) {$util['intitule'] = $infosUtilisateurs[$ce]['intitule'];} else {$posa = strpos($ce, '@');if ($posa !== false) {$util['intitule'] = substr($ce, 0, $posa+1) . '...';} else {$util['intitule'] = null;}}}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;}/*** Prend en paramêtre un tableau d'identifiants utilisateurs et retourne après avoir interrogé un* webservice de l'annuaire un tableau avec en clé l'id et en valeur l'intitulé* @param array $ids un tableau d'ids*/protected function recupererIntitulesUtilisateursParIds(array $ids) {$service = "utilisateur/infos-par-id/" . implode(',', $ids);$url = $this->conteneur->getParametre('urlServiceBaseAnnuaire') . $service;//echo "URL: $url\n\n";$json = $this->conteneur->getRestClient()->consulter($url);$resultat = json_decode($json, true);return $resultat;}}