WebSVN – eFlore/Applications.del – //trunk/services/modules/0.1/Statistiques.php

<?php
/**
* Description :
* Statistiques par année sur l'utilisation de Identiplante / Pictoflora
*
* Documentation: http://www.tela-botanica.org/wikini/DevInformatiques/wakka.php?wiki=AppliDelStats
* 
* @author Mathias
* @license GPL v3 <http://www.gnu.org/licenses/gpl.txt>
* @license CECILL v2 <http://www.cecill.info/licences/Licence_CeCILL_V2-en.txt>
*/
class Statistiques extends RestService {

        protected $bdd;

        public function __construct() {
                $this->bdd = $this->getBdd();
        }

        // GET
        public function consulter($ressources, $parametres) {
                $resultat = '';
                $reponseHttp = new ReponseHttp();

                // méthode à appeler
                $methode = "tout";
                if (isset($ressources[0])) {
                        $methode = $ressources[0];
                }
                // année à considérer - par défaut pas de filtrage
                $annee = null;
                if (isset($parametres['annee'])) {
                        $annee = $parametres['annee'];
                }
                //echo "Année: $annee\nMéthode: $methode\n";

                // appel
                if (method_exists($this, $methode)) {
                        try {
                                $retour = call_user_func(array($this, $methode), $annee);
                                $reponseHttp->setResultatService($retour);
                        } catch (ServiceFoiraxException $e) {
                                // @TODO renvoyer 500
                                $reponseHttp->ajouterErreur($e);
                        }
                } else {
                        // @TODO renvoyer 500
                        $reponseHttp->ajouterErreur(new Exception("Erreur: la méthode \"$methode\" n'existe pas<br/>" . $this->utilisation()));
                }

                // merci au revoir
                $reponseHttp->emettreLesEntetes();
                $corps = $reponseHttp->getCorps();

                return $corps;
        }

        // Renvoie la liste des méthodes disponibles
        private function utilisation() {
                return "<br/>Méthodes disponibles:<ul>".
                        "<li>tout</li>".
                        "<li>moyenneObsSansNomParMois</li>".
                        "<li>moyenneObsIdentifieesParMois</li>".
                        "<li>pourcentageObsIdentifieesEnFinDAnnee</li>".
                        "<li>pourcentageObsIdentifieesEnFinDAnneePlusPlus</li>".
                        "<li>moyenneActionsParJour</li>".
                        "<li>personnesEnvoyantUnePropositionParMois</li>".
                "</ul>";
        }

        // retourne toutes les stats pour l'année spécifiée
        protected function tout($annee=null) {
                $moyenneObsSansNomParMois = $this->moyenneObsSansNomParMois($annee);
                $moyenneObsIdentifieesParMois = $this->moyenneObsIdentifieesParMois($annee);
                $pourcentageObsIdentifieesEnFinDAnnee = $this->pourcentageObsIdentifieesEnFinDAnnee($annee);
                $pourcentageObsIdentifieesEnFinDAnneePlusPlus = $this->pourcentageObsIdentifieesEnFinDAnneePlusPlus($annee);
                $moyenneActionsParJour = $this->moyenneActionsParJour($annee);
                $personnesEnvoyantUnePropositionParMois = $this->personnesEnvoyantUnePropositionParMois($annee);

                return array(
                        'annee' => $annee ? intval($annee) : null,
                        'moyenneObsSansNomParMois' => $moyenneObsSansNomParMois,
                        'moyenneObsIdentifieesParMois' => $moyenneObsIdentifieesParMois,
                        'pourcentageObsIdentifieesEnFinDAnnee' => $pourcentageObsIdentifieesEnFinDAnnee,
                        'pourcentageObsIdentifieesEnFinDAnneePlusPlus' => $pourcentageObsIdentifieesEnFinDAnneePlusPlus['pourcentage'],
                        'moyenneActionsParJour' => $moyenneActionsParJour,
                        'personnesEnvoyantUnePropositionParMois' => $personnesEnvoyantUnePropositionParMois['nombre']
                );
        }

        // proxy pour le widget de stats
        protected function observations($annee) {
                return $this->tout($annee);
        }

        // Retourne le nombre moyen d'observations non identifiées envoyées par mois, pour l'année $annee
        protected function moyenneObsSansNomParMois($annee) {
                $req = "SELECT AVG(parMois.compte) as moyenne FROM".
                        " (SELECT count(*) as compte, CONCAT(YEAR(date_transmission),'-',MONTH(date_transmission)) as anneemois".
                        " FROM del_observation".
                        " WHERE ";
                if ($annee !== null) {
                        $req .= "YEAR(date_transmission) = '" . $annee . "' AND ";
                }
                                // Obs marqués comme "aDeterminer" ou ayant un faible niveau de certitude
                $req .= " (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".
                                        // Obs ayant une proposition initiale "vide" (sans no_sel_nn)
                                        " (SELECT DISTINCT ce_observation".
                                        " FROM del_commentaire".
                                        " WHERE proposition_initiale = 1".
                                        " AND (nom_sel_nn IS NULL OR nom_sel_nn = '')".
                                ")".
                        ")".
                        " GROUP BY anneemois".
                        " ORDER BY anneemois DESC) as parMois;";

                $res = $this->bdd->recupererTous($req);
                return intval($res[0]['moyenne']);
        }

        // 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)
        protected function moyenneObsIdentifieesParMois($annee) {
                $req = "SELECT AVG(valideesparmois) as moyenne FROM (".
                        " SELECT count(*) as valideesparmois, CONCAT(YEAR(maxdate), '-', MONTH(maxdate)) as anneemois".
                        " FROM (".
                                // Compte et date du dernier vote des propositions marquées comme "retenues"
                                " SELECT count(*), MAX(dcv.date) as maxdate".
                                " FROM del_commentaire dc".
                                " LEFT JOIN del_commentaire_vote dcv ON dcv.ce_proposition = dc.id_commentaire".
                                " WHERE proposition_retenue = 1".
                                " GROUP BY dc.id_commentaire";

                        if ($annee !== null) {
                                // Le dernier vote doit être dans l'année considérée (@ACHTUNG bancal)
                                $req .= " HAVING MAX(YEAR(dcv.date)) = '" . $annee . "'";
                        }
                        $req .= ") as temp".
                " GROUP BY anneemois) as temp2;";

                $res = $this->bdd->recupererTous($req);
                return intval($res[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)
        protected function pourcentageObsIdentifieesEnFinDAnneePlusPlus($annee) {
                // Obs ayant atteint un consensus cette année
                $req1 = "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 dc".
                                " LEFT JOIN del_observation do ON do.id_observation = dc.ce_observation".
                                " LEFT JOIN del_commentaire_vote dcv ON dc.id_commentaire = dcv.ce_proposition".
                                " AND dcv.valeur = 1".
                                " AND dc.proposition_retenue = 0".
                                " GROUP BY dc.id_commentaire";
                if ($annee != null) {
                        $req1 .= " HAVING MAX(YEAR(dcv.date)) = '" . $annee . "'";
                }
                $req1 .= " ) as temp GROUP BY id_observation".
                ") as temp2;";
                $obsEnConsensus = $this->bdd->recupererTous($req1);
                $oc = intval($obsEnConsensus[0]['nombre']);

                // Obs ayant une "proposition retenue" cette année
                $req2 = "SELECT count(*) as nombre FROM (".
                        " SELECT count(DISTINCT id_observation), MAX(dcv.date) as maxdate".
                        " FROM del_commentaire dc".
                        " LEFT JOIN del_commentaire_vote dcv ON dcv.ce_proposition = dc.id_commentaire".
                        " LEFT JOIN del_observation do ON do.id_observation = dc.ce_observation".
                        " WHERE proposition_retenue = 1";
                if ($annee != null) {
                        $req2 .= " AND YEAR(do.date_transmission) = '" . $annee . "'";
                }
                $req2 .= " GROUP BY dc.id_commentaire";
                if ($annee != null) {
                        $req2 .= " HAVING MAX(YEAR(dcv.date)) = '" . $annee . "'";
                }
                $req2 .= ") as temp;";
                $nbObsValidees = $this->bdd->recupererTous($req2);
                $ov = intval($nbObsValidees[0]['nombre']);

                // Nombre d'obs sans nom soumises cette année
                $req3 = "SELECT count(*) as nombre".
                        " FROM del_observation".
                        " WHERE ";
                if ($annee != null) {
                        $req3 .= "YEAR(date_transmission) = '" . $annee . "' AND ";
                }
                $req3 .= "(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 = '')".
                        " ))";
                $nbObsSansNom = $this->bdd->recupererTous($req3);
                $osn = intval($nbObsSansNom[0]['nombre']);

                return array(
                        'observationsEnConsensus' => $oc,
                        'observationsValidees' => $ov,
                        'observationsSansNom' => $osn,
                        'pourcentage' => ($osn == 0 ? 0 : (($oc + $ov) / $osn) * 100)
                );
        }

        protected function pourcentageObsIdentifieesEnFinDAnnee($annee) {
                $req = "SELECT (".
                        " SELECT count(*) FROM (".
                                " SELECT count(DISTINCT id_observation), MAX(dcv.date) as maxdate".
                                " FROM del_commentaire dc".
                                " LEFT JOIN del_commentaire_vote dcv ON dcv.ce_proposition = dc.id_commentaire".
                                " LEFT JOIN del_observation do ON do.id_observation = dc.ce_observation".
                                " WHERE proposition_retenue = 1";
                if ($annee !== null) {
                        $req .= " AND YEAR(do.date_transmission) = '" . $annee . "'";
                }
                $req .= " GROUP BY dc.id_commentaire";
                if ($annee !== null) {
                        $req .= " HAVING MAX(YEAR(dcv.date)) = '" . $annee . "'";
                }
                $req .= ") as temp)".
                        " /".
                        " (SELECT count(*)".
                        " FROM del_observation".
                        " WHERE ";
                if ($annee !== null) {
                        $req .= "YEAR(date_transmission) = '" . $annee . "' AND ";
                }
                $req .= " (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 = '')".
                                " )".
                        " )".
                " ) * 100 as pourcentage;";

                $res = $this->bdd->recupererTous($req);
                return floatval($res[0]['pourcentage']);
        }

        // Retourne la moyenne sur l'année du nombre d'actions (commentaire ou vote) par jour
        protected function moyenneActionsParJour($annee) {
                // nombre d'actions / nombre de jours
                $req = "SELECT".
                        " (SELECT".
                        // nombre de commentaires sur l'année
                        " (SELECT count(*) FROM del_commentaire" . ($annee != null ? " WHERE YEAR(date) = '" . $annee . "')" : ")").
                        " +".
                        // nombre de commentaires sur l'année
                        " (SELECT count(*) FROM del_commentaire_vote" . ($annee != null ? " WHERE YEAR(date) = '" . $annee . "')" : ")").
                        " )".
                        // nombre de jours écoulés dans l'année
                        " / (";
                if ($annee == null) {
                        $req .= "SELECT 365 * (year(now()) - min(year(date)) + 1) FROM del_commentaire_vote WHERE YEAR(date) != 0";
                } else {
                        $req .= "SELECT IF( YEAR(CURDATE()) = '" . $annee . "', DAYOFYEAR(CURDATE()), 365)";
                }
                $req .= ") as moyenne;";

                $res = $this->bdd->recupererTous($req);
                return intval($res[0]['moyenne']);
        }

        // Retourne le nombre et la liste des personnes ayant sur l'année une moyenne de participation par mois >= 1
        protected function personnesEnvoyantUnePropositionParMois($annee) {
                // Faire la moyenne par utilisateur et par mois
                $req = "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";
                if ($annee != null) {
                        $req .= " AND YEAR(date) = '" . $annee . "'";
                }
                $req .= " 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 ";
                if ($annee != null) {
                        $req .= "YEAR(date) = '" . $annee . "' AND ";
                }
                $req .= " 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;";
        
                $res = $this->bdd->recupererTous($req);
                $cpt = count($res);
                $retour = array(
                        'nombre' => intval($cpt),
                        'donnees' => $res
                );

                return $retour;
        }
}
?>
Subversion Repositories eFlore/Applications.del

(root)//trunk/services/modules/0.1/Statistiques.php – Rev 1792
