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". |