Subversion Repositories eFlore/Applications.del

Compare Revisions

Ignore whitespace Rev 2018 → Rev 2019

/trunk/services/modules/0.1/statistiques/StatistiquesParAnnee.php
127,7 → 127,7
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'))";
$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');
}
143,7 → 143,7
if ($this->annee != null) {
$requete .= ' YEAR(date_transmission) = ' . $this->annee . " AND";
}
$requete .= " nom_sel_nn = ''";
$requete .= " nom_sel_nn = '' OR nom_sel_nn IS NULL";
if ($mpm) {
$requete = $this->encapsulerMPM($requete, 'date_transmission');
}
309,11 → 309,11
* Nombre de proposition faites sur toutes les observations
*/
private function getNbPropositionsToutesObs($mpm=false) {
$requete = "SELECT COUNT(DISTINCT `id_commentaire`) AS nb_total FROM `del_commentaire` WHERE";
$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";
$requete .= " (nom_sel_nn IS NOT NULL OR nom_sel_nn != '') AND proposition_initiale = 0";
if ($mpm) {
$requete = $this->encapsulerMPM($requete);
}
385,9 → 385,9
if ($this->annee == null) {
return null;
}
$requete = "SELECT COUNT(DISTINCT `id_commentaire`) 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 . ")";
$requete = "SELECT COUNT(DISTINCT id_commentaire) 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);
}
402,8 → 402,8
if ($this->annee == null) {
return null;
}
$requete = "SELECT COUNT(DISTINCT `id_commentaire`) AS nb_total FROM `del_commentaire` WHERE YEAR(date) = " . $this->annee . " AND "
. "`proposition_retenue` = 1 AND ce_observation in (SELECT `id_observation` FROM `del_observation` WHERE year(date_transmission) = " . $this->annee . ")";
$requete = "SELECT COUNT(DISTINCT id_commentaire) AS nb_total FROM del_commentaire WHERE YEAR(date) = " . $this->annee . " AND "
. "proposition_retenue = 1 AND ce_observation in (SELECT id_observation FROM del_observation WHERE year(date_transmission) = " . $this->annee . ")";
if ($mpm) {
$requete = $this->encapsulerMPM($requete);
}
419,7 → 419,7
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 ("
. " 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"
443,7 → 443,7
* Nombre de votes
*/
private function getNbVotes($mpm=false) {
$requete = "SELECT COUNT(DISTINCT `id_vote`) AS nb_total FROM del_commentaire_vote";
$requete = "SELECT COUNT(DISTINCT id_vote) AS nb_total FROM del_commentaire_vote";
if ($this->annee != null) {
$requete .= ' WHERE YEAR(date) = ' . $this->annee;
}
458,7 → 458,7
* 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` = '')";
$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;
}
512,7 → 512,7
* 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` != ''";
$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;
}
527,7 → 527,7
* 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` = '')";
$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;
}
610,11 → 610,11
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";
. " 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 .= " 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`)"
$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);
 
638,11 → 638,11
*/
private function getListeMeilleursVoteurs() {
$liste = array();
$requete = "SELECT * FROM (SELECT courriel, ce_utilisateur, COUNT(DISTINCT `id_vote`) AS nombre FROM del_commentaire_vote, del_utilisateur where";
$requete = "SELECT * FROM (SELECT courriel, ce_utilisateur, COUNT(DISTINCT id_vote) AS nombre FROM del_commentaire_vote, del_utilisateur where";
if ($this->annee != null) {
$requete .= " year(`date`) = " . $this->annee . " AND";
$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";
$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
668,11 → 668,11
. " 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 .= " 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 .= " 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 ?