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 ? |