Subversion Repositories eFlore/Applications.del

Compare Revisions

Ignore whitespace Rev 1787 → Rev 1788

/trunk/services/modules/0.1/Statistiques.php
27,8 → 27,8
if (isset($ressources[0])) {
$methode = $ressources[0];
}
// année à considérer
$annee = date('Y');
// année à considérer - par défaut pas de filtrage
$annee = null;
if (isset($parametres['annee'])) {
$annee = $parametres['annee'];
}
69,7 → 69,7
}
 
// retourne toutes les stats pour l'année spécifiée
protected function tout($annee) {
protected function tout($annee=null) {
$moyenneObsSansNomParMois = $this->moyenneObsSansNomParMois($annee);
$moyenneObsIdentifieesParMois = $this->moyenneObsIdentifieesParMois($annee);
$pourcentageObsIdentifieesEnFinDAnnee = $this->pourcentageObsIdentifieesEnFinDAnnee($annee);
78,7 → 78,7
$personnesEnvoyantUnePropositionParMois = $this->personnesEnvoyantUnePropositionParMois($annee);
 
return array(
'annee' => intval($annee),
'annee' => $annee ? intval($annee) : null,
'moyenneObsSansNomParMois' => $moyenneObsSansNomParMois,
'moyenneObsIdentifieesParMois' => $moyenneObsIdentifieesParMois,
'pourcentageObsIdentifieesEnFinDAnnee' => $pourcentageObsIdentifieesEnFinDAnnee,
88,15 → 88,22
);
}
 
// 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 YEAR(date_transmission) = '" . $annee . "'".
" AND".
" WHERE ";
if ($annee !== null) {
$req .= "YEAR(date_transmission) = '" . $annee . "' AND ";
}
// Obs marqués comme "aDeterminer" ou ayant un faible niveau de certitude
" (mots_cles_texte LIKE '%aDeterminer%'".
$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)
128,10 → 135,14
" 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".
" GROUP BY dc.id_commentaire";
 
if ($annee !== null) {
// Le dernier vote doit être dans l'année considérée (@ACHTUNG bancal)
" HAVING MAX(YEAR(dcv.date)) = '" . $annee . "') as temp".
" GROUP BY anneemois) as temp2;";
$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']);
151,9 → 162,11
" 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".
" HAVING MAX(YEAR(dcv.date)) = '" . $annee . "'".
" ) as temp GROUP BY id_observation".
" 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']);
164,11 → 177,15
" 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".
" AND YEAR(do.date_transmission) = '" . $annee . "'".
" GROUP BY dc.id_commentaire".
" HAVING MAX(YEAR(dcv.date)) = '" . $annee . "')".
" as temp;";
" 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']);
 
175,8 → 192,11
// Nombre d'obs sans nom soumises cette année
$req3 = "SELECT count(*) as nombre".
" FROM del_observation".
" WHERE YEAR(date_transmission) = '" . $annee . "'".
" AND (mots_cles_texte LIKE '%aDeterminer%'".
" 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".
205,16 → 225,23
" 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".
" AND YEAR(do.date_transmission) = '" . $annee . "'".
" GROUP BY dc.id_commentaire".
" HAVING MAX(YEAR(dcv.date)) = '" . $annee . "')".
" as temp)".
" 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 YEAR(date_transmission) = '" . $annee . "'".
" AND (mots_cles_texte LIKE '%aDeterminer%'".
" 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".
238,14 → 265,19
$req = "SELECT".
" (SELECT".
// nombre de commentaires sur l'année
" (SELECT count(*) FROM del_commentaire WHERE YEAR(date) = '" . $annee . "')".
" (SELECT count(*) FROM del_commentaire" . ($annee != null ? " WHERE YEAR(date) = '" . $annee . "')" : ")").
" +".
// nombre de commentaires sur l'année
" (SELECT count(*) FROM del_commentaire_vote WHERE YEAR(date) = '" . $annee . "')".
" (SELECT count(*) FROM del_commentaire_vote" . ($annee != null ? " WHERE YEAR(date) = '" . $annee . "')" : ")").
" )".
// nombre de jours écoulés dans l'année
" / (SELECT IF( YEAR(CURDATE()) = '" . $annee . "', DAYOFYEAR(CURDATE()), 365)".
" ) as moyenne;";
" / (";
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']);
260,15 → 292,20
" FROM del_commentaire".
" WHERE ce_proposition = ''".
" AND nom_sel_nn != ''".
" AND nom_sel_nn IS NOT NULL".
" AND YEAR(date) = '" . $annee . "'".
" GROUP BY anneemois, ce_utilisateur, utilisateur_courriel) as ppm,".
" 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 YEAR(date) = '" . $annee . "'".
" AND ce_proposition = ''".
" 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".