89,234 → 89,172 |
return (array) json_decode($json); |
} |
|
// retourne toutes les stats pour l'année spécifiée |
private function getTout() { |
$obsIdentifieesFinAnneePlus = $this->getPourcentageObsIdentifieesFinAnneePlus(); |
$participants = $this->getParticipants(); |
|
/** |
* 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( |
'annee' => $this->annee, |
'moyenneObsSansNomParMois' => $this->getMoyenneObsSansNomParMois(), |
'moyenneObsIdentifieesParMois' => $this->getMoyenneObsIdentifieesParMois(), |
'pourcentageObsIdentifieesEnFinDAnnee' => $this->getPourcentageObsIdentifieesFinAnnee(), |
'pourcentageObsIdentifieesEnFinDAnneePlusPlus' => $obsIdentifieesFinAnneePlus['pourcentage'], |
'moyenneActionsParJour' => $this->getMoyenneActionsParJour(), |
'personnesEnvoyantUnePropositionParMois' => $participants['nombre'] |
'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), |
); |
} |
|
// proxy pour le widget de stats |
private function getObservations() { |
return $this->getTout(); |
/** |
* 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])); |
} |
|
// Retourne le nombre moyen d'observations non identifiées envoyées par mois, pour l'année $annee |
private 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 '; |
/** |
* 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])); |
} |
|
$requete = "SELECT AVG(parMois.compte) AS moyenne FROM ($sqlTableTmp) AS parMois ". |
' -- '.__FILE__.' : '.__LINE__; |
/** |
* 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($resultat[0]['moyenne']); |
return intval(array_pop($resultat[0])); |
} |
|
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; |
/** |
* 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"; |
} |
|
// 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__; |
|
$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($resultat[0]['moyenne']); |
return intval(array_pop($resultat[0])); |
} |
|
// 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)) |
); |
/** |
* 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"; |
} |
|
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__; |
$requete .= " (certitude IN ('aDeterminer','douteux')))"; |
if ($mpm) { |
$requete = $this->encapsulerMPM($requete, 'date_transmission'); |
} |
$resultat = $this->bdd->recupererTous($requete); |
return floatval($resultat[0]['pourcentage']); |
return intval(array_pop($resultat[0])); |
} |
|
// Retourne la moyenne sur l'année du nombre d'actions (commentaire ou vote) par jour |
private 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 "; |
/** |
* 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) { |
$sqlNbreJours = "SELECT IF(YEAR(CURDATE()) = '{$this->annee}', DAYOFYEAR(CURDATE()), 365) "; |
$requete .= ' YEAR(date_transmission) = ' . $this->annee . " AND"; |
} |
|
// nombre d'actions / nombre de jours |
$requete = "SELECT ((($sqlNbreCommentaires) + ($sqlNbreVotes)) / ($sqlNbreJours)) AS moyenne ". |
' -- '.__FILE__.' : '.__LINE__; |
|
$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($resultat[0]['moyenne']); |
return intval(array_pop($resultat[0])); |
} |
|
// Retourne le nombre et la liste des personnes ayant sur l'année une moyenne de participation par mois >= 1 |
private 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__; |
|
/** |
* 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); |
$cpt = count($resultat); |
$retour = array( |
'nombre' => intval($cpt), |
'donnees' => $resultat |
); |
return $retour; |
return intval(array_pop($resultat[0])); |
} |
|
/** |
343,14 → 281,14 |
'nbPropositionsToutesObsMPM' => $this->getNbPropositionsToutesObs(true), |
'nbPropositionsRetenuesToutesObs' => $this->getNbPropositionsRetenuesToutesObs(), |
'nbPropositionsRetenuesToutesObsMPM' => $this->getNbPropositionsRetenuesToutesObs(true), |
'nbPropositionsConsensusToutesObs' => null, //$this->getNbPropositionsConsensusToutesObs(), |
'nbPropositionsConsensusToutesObsMPM' => null, //$this->getNbPropositionsConsensusToutesObs(true), |
'nbPropositionsConsensusToutesObs' => $this->getNbPropositionsConsensusToutesObs(), |
'nbPropositionsConsensusToutesObsMPM' => $this->getNbPropositionsConsensusToutesObs(true), |
'nbPropositionsObsAnnee' => $this->getNbPropositionsObsAnnee(), |
'nbPropositionsObsAnneeMPM' => $this->getNbPropositionsObsAnnee(true), |
'nbPropositionsRetenuesObsAnnee' => $this->getNbPropositionsRetenuesObsAnnee(), |
'nbPropositionsRetenuesObsAnneeMPM' => $this->getNbPropositionsRetenuesObsAnnee(true), |
'nbPropositionsConsensusObsAnnee' => null, //$this->getNbPropositionsConsensusObsAnnee(), |
'nbPropositionsConsensusObsAnneeMPM' => null //$this->getNbPropositionsConsensusObsAnnee(true), |
'nbPropositionsConsensusObsAnnee' => $this->getNbPropositionsConsensusObsAnnee(), |
'nbPropositionsConsensusObsAnneeMPM' => $this->getNbPropositionsConsensusObsAnnee(true), |
); |
} |
|
414,12 → 352,44 |
} |
|
/** |
* @TODO Nombre de proposition en consensus faites sur toutes les observations |
* Nombre de proposition en consensus faites sur toutes les observations |
*/ |
private function getNbPropositionsConsensusToutesObs($mpm=false) { |
$requete = ""; |
$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($resultat[0][0]); |
return intval(array_pop($resultat[0])); |
} |
|
/** |
456,12 → 426,31 |
} |
|
/** |
* @TODO Nombre de proposition en consensus faites sur les observations d'une année |
* Nombre de proposition en consensus faites sur les observations d'une année |
*/ |
private function getNbPropositionsConsensusObsAnnee($mpm=false) { |
$requete = ""; |
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($resultat[0][0]); |
return intval(array_pop($resultat[0])); |
} |
|
/** |
500,8 → 489,10 |
* "MPM" = moyenne par mois |
* - Nombre total d'utilisateurs |
* - Nombre d'utilisateurs ayant fait une proposition |
* - Nombre d'utilisateurs ayant fait un vote |
* - 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( |
510,10 → 501,12 |
'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() |
); |
} |
|
590,6 → 583,42 |
} |
|
/** |
* 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() { |
777,10 → 806,223 |
* afin de calculer la moyenne par mois |
* @param string $requete count() qui doit renvoyer une colonne 'nb_total' |
*/ |
protected function encapsulerMPM($requete) { |
protected function encapsulerMPM($requete, $colonne="date") { |
$requeteEncapsulee = "SELECT AVG(nb_total) as moyenne FROM (" |
. $requete |
. " GROUP BY CONCAT(year(date),month(date)) ) AS nombre"; |
. " 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 $annee |
private 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 jour |
private 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 >= 1 |
private 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 -------------- */ |
} |