Line 125... |
Line 125... |
125 |
private function getNbObsSansIdentOuTC($mpm=false) {
|
125 |
private function getNbObsSansIdentOuTC($mpm=false) {
|
126 |
$requete = "SELECT COUNT(id_observation) AS nb_total FROM del_observation WHERE";
|
126 |
$requete = "SELECT COUNT(id_observation) AS nb_total FROM del_observation WHERE";
|
127 |
if ($this->annee != null) {
|
127 |
if ($this->annee != null) {
|
128 |
$requete .= ' YEAR(date_transmission) = ' . $this->annee . " AND";
|
128 |
$requete .= ' YEAR(date_transmission) = ' . $this->annee . " AND";
|
129 |
}
|
129 |
}
|
130 |
$requete .= " (`mots_cles_texte` LIKE '%determiner%' OR `nom_sel_nn` = '' OR certitude IN ('aDeterminer','douteux'))";
|
130 |
$requete .= " (mots_cles_texte LIKE '%determiner%' OR nom_sel_nn = '' OR nom_sel_nn IS NULL OR certitude IN ('aDeterminer','douteux'))";
|
131 |
if ($mpm) {
|
131 |
if ($mpm) {
|
132 |
$requete = $this->encapsulerMPM($requete, 'date_transmission');
|
132 |
$requete = $this->encapsulerMPM($requete, 'date_transmission');
|
133 |
}
|
133 |
}
|
134 |
$resultat = $this->bdd->recupererTous($requete);
|
134 |
$resultat = $this->bdd->recupererTous($requete);
|
135 |
return intval(array_pop($resultat[0]));
|
135 |
return intval(array_pop($resultat[0]));
|
Line 141... |
Line 141... |
141 |
private function getNbObsSansIdent($mpm=false) {
|
141 |
private function getNbObsSansIdent($mpm=false) {
|
142 |
$requete = "SELECT COUNT(id_observation) AS nb_total FROM del_observation WHERE";
|
142 |
$requete = "SELECT COUNT(id_observation) AS nb_total FROM del_observation WHERE";
|
143 |
if ($this->annee != null) {
|
143 |
if ($this->annee != null) {
|
144 |
$requete .= ' YEAR(date_transmission) = ' . $this->annee . " AND";
|
144 |
$requete .= ' YEAR(date_transmission) = ' . $this->annee . " AND";
|
145 |
}
|
145 |
}
|
146 |
$requete .= " nom_sel_nn = ''";
|
146 |
$requete .= " nom_sel_nn = '' OR nom_sel_nn IS NULL";
|
147 |
if ($mpm) {
|
147 |
if ($mpm) {
|
148 |
$requete = $this->encapsulerMPM($requete, 'date_transmission');
|
148 |
$requete = $this->encapsulerMPM($requete, 'date_transmission');
|
149 |
}
|
149 |
}
|
150 |
$resultat = $this->bdd->recupererTous($requete);
|
150 |
$resultat = $this->bdd->recupererTous($requete);
|
151 |
return intval(array_pop($resultat[0]));
|
151 |
return intval(array_pop($resultat[0]));
|
Line 307... |
Line 307... |
307 |
|
307 |
|
308 |
/**
|
308 |
/**
|
309 |
* Nombre de proposition faites sur toutes les observations
|
309 |
* Nombre de proposition faites sur toutes les observations
|
310 |
*/
|
310 |
*/
|
311 |
private function getNbPropositionsToutesObs($mpm=false) {
|
311 |
private function getNbPropositionsToutesObs($mpm=false) {
|
312 |
$requete = "SELECT COUNT(DISTINCT `id_commentaire`) AS nb_total FROM `del_commentaire` WHERE";
|
312 |
$requete = "SELECT COUNT(DISTINCT id_commentaire) AS nb_total FROM del_commentaire WHERE";
|
313 |
if ($this->annee != null) {
|
313 |
if ($this->annee != null) {
|
314 |
$requete .= ' YEAR(date) = ' . $this->annee . " AND";
|
314 |
$requete .= ' YEAR(date) = ' . $this->annee . " AND";
|
315 |
}
|
315 |
}
|
316 |
$requete .= " (`nom_sel_nn` IS NOT NULL OR `nom_sel_nn` != '') AND `proposition_initiale` = 0";
|
316 |
$requete .= " (nom_sel_nn IS NOT NULL OR nom_sel_nn != '') AND proposition_initiale = 0";
|
317 |
if ($mpm) {
|
317 |
if ($mpm) {
|
318 |
$requete = $this->encapsulerMPM($requete);
|
318 |
$requete = $this->encapsulerMPM($requete);
|
319 |
}
|
319 |
}
|
320 |
$resultat = $this->bdd->recupererTous($requete);
|
320 |
$resultat = $this->bdd->recupererTous($requete);
|
Line 383... |
Line 383... |
383 |
*/
|
383 |
*/
|
384 |
private function getNbPropositionsObsAnnee($mpm=false) {
|
384 |
private function getNbPropositionsObsAnnee($mpm=false) {
|
385 |
if ($this->annee == null) {
|
385 |
if ($this->annee == null) {
|
386 |
return null;
|
386 |
return null;
|
387 |
}
|
387 |
}
|
388 |
$requete = "SELECT COUNT(DISTINCT `id_commentaire`) AS nb_total FROM `del_commentaire` WHERE YEAR(date) = " . $this->annee . " AND "
|
388 |
$requete = "SELECT COUNT(DISTINCT id_commentaire) AS nb_total FROM del_commentaire WHERE YEAR(date) = " . $this->annee . " AND "
|
389 |
. "(`nom_sel_nn` IS NOT NULL OR `nom_sel_nn` != '') AND `proposition_initiale` = 0 AND ce_observation in"
|
389 |
. "(nom_sel_nn IS NOT NULL OR nom_sel_nn != '') AND proposition_initiale = 0 AND ce_observation in"
|
390 |
. " (SELECT `id_observation` FROM `del_observation` WHERE year(date_transmission) = " . $this->annee . ")";
|
390 |
. " (SELECT id_observation FROM del_observation WHERE year(date_transmission) = " . $this->annee . ")";
|
391 |
if ($mpm) {
|
391 |
if ($mpm) {
|
392 |
$requete = $this->encapsulerMPM($requete);
|
392 |
$requete = $this->encapsulerMPM($requete);
|
393 |
}
|
393 |
}
|
394 |
$resultat = $this->bdd->recupererTous($requete);
|
394 |
$resultat = $this->bdd->recupererTous($requete);
|
395 |
return intval(array_pop($resultat[0]));
|
395 |
return intval(array_pop($resultat[0]));
|
Line 400... |
Line 400... |
400 |
*/
|
400 |
*/
|
401 |
private function getNbPropositionsRetenuesObsAnnee($mpm=false) {
|
401 |
private function getNbPropositionsRetenuesObsAnnee($mpm=false) {
|
402 |
if ($this->annee == null) {
|
402 |
if ($this->annee == null) {
|
403 |
return null;
|
403 |
return null;
|
404 |
}
|
404 |
}
|
405 |
$requete = "SELECT COUNT(DISTINCT `id_commentaire`) AS nb_total FROM `del_commentaire` WHERE YEAR(date) = " . $this->annee . " AND "
|
405 |
$requete = "SELECT COUNT(DISTINCT id_commentaire) AS nb_total FROM del_commentaire WHERE YEAR(date) = " . $this->annee . " AND "
|
406 |
. "`proposition_retenue` = 1 AND ce_observation in (SELECT `id_observation` FROM `del_observation` WHERE year(date_transmission) = " . $this->annee . ")";
|
406 |
. "proposition_retenue = 1 AND ce_observation in (SELECT id_observation FROM del_observation WHERE year(date_transmission) = " . $this->annee . ")";
|
407 |
if ($mpm) {
|
407 |
if ($mpm) {
|
408 |
$requete = $this->encapsulerMPM($requete);
|
408 |
$requete = $this->encapsulerMPM($requete);
|
409 |
}
|
409 |
}
|
410 |
$resultat = $this->bdd->recupererTous($requete);
|
410 |
$resultat = $this->bdd->recupererTous($requete);
|
411 |
return intval(array_pop($resultat[0]));
|
411 |
return intval(array_pop($resultat[0]));
|
Line 417... |
Line 417... |
417 |
private function getNbPropositionsConsensusObsAnnee($mpm=false) {
|
417 |
private function getNbPropositionsConsensusObsAnnee($mpm=false) {
|
418 |
if ($this->annee == null) {
|
418 |
if ($this->annee == null) {
|
419 |
return null;
|
419 |
return null;
|
420 |
}
|
420 |
}
|
421 |
$requete = "SELECT COUNT(DISTINCT ce_observation) AS nb_total FROM del_commentaire dc WHERE ce_observation in ("
|
421 |
$requete = "SELECT COUNT(DISTINCT ce_observation) AS nb_total FROM del_commentaire dc WHERE ce_observation in ("
|
422 |
. " SELECT `id_observation` FROM `del_observation` WHERE year(date_transmission) = " . $this->annee . ") AND ("
|
422 |
. " SELECT id_observation FROM del_observation WHERE year(date_transmission) = " . $this->annee . ") AND ("
|
423 |
. " dc.proposition_retenue = 1 OR (dc.proposition_initiale = 1"
|
423 |
. " dc.proposition_retenue = 1 OR (dc.proposition_initiale = 1"
|
424 |
. " AND dc.nom_sel_nn != 0 AND dc.nom_sel_nn IS NOT NULL AND dc.id_commentaire IN"
|
424 |
. " AND dc.nom_sel_nn != 0 AND dc.nom_sel_nn IS NOT NULL AND dc.id_commentaire IN"
|
425 |
. " (SELECT ce_proposition FROM del_commentaire_vote dcv"
|
425 |
. " (SELECT ce_proposition FROM del_commentaire_vote dcv"
|
426 |
. " WHERE year(date) <= " . $this->annee
|
426 |
. " WHERE year(date) <= " . $this->annee
|
427 |
. " GROUP BY ce_proposition"
|
427 |
. " GROUP BY ce_proposition"
|
Line 441... |
Line 441... |
441 |
|
441 |
|
442 |
/**
|
442 |
/**
|
443 |
* Nombre de votes
|
443 |
* Nombre de votes
|
444 |
*/
|
444 |
*/
|
445 |
private function getNbVotes($mpm=false) {
|
445 |
private function getNbVotes($mpm=false) {
|
446 |
$requete = "SELECT COUNT(DISTINCT `id_vote`) AS nb_total FROM del_commentaire_vote";
|
446 |
$requete = "SELECT COUNT(DISTINCT id_vote) AS nb_total FROM del_commentaire_vote";
|
447 |
if ($this->annee != null) {
|
447 |
if ($this->annee != null) {
|
448 |
$requete .= ' WHERE YEAR(date) = ' . $this->annee;
|
448 |
$requete .= ' WHERE YEAR(date) = ' . $this->annee;
|
449 |
}
|
449 |
}
|
450 |
if ($mpm) {
|
450 |
if ($mpm) {
|
Line 456... |
Line 456... |
456 |
|
456 |
|
457 |
/**
|
457 |
/**
|
458 |
* Nombre de commentaires
|
458 |
* Nombre de commentaires
|
459 |
*/
|
459 |
*/
|
460 |
private function getNbCommentaires($mpm=false) {
|
460 |
private function getNbCommentaires($mpm=false) {
|
461 |
$requete = "SELECT COUNT(DISTINCT `id_commentaire`) AS nb_total FROM del_commentaire WHERE `ce_proposition` != '' AND (`nom_sel` IS NULL OR `nom_sel` = '')";
|
461 |
$requete = "SELECT COUNT(DISTINCT id_commentaire) AS nb_total FROM del_commentaire WHERE ce_proposition != '' AND (nom_sel IS NULL OR nom_sel = '')";
|
462 |
if ($this->annee != null) {
|
462 |
if ($this->annee != null) {
|
463 |
$requete .= ' AND YEAR(date) = ' . $this->annee;
|
463 |
$requete .= ' AND YEAR(date) = ' . $this->annee;
|
464 |
}
|
464 |
}
|
465 |
if ($mpm) {
|
465 |
if ($mpm) {
|
Line 510... |
Line 510... |
510 |
|
510 |
|
511 |
/**
|
511 |
/**
|
512 |
* Nombre d'utilisateurs d'Identiplante ayant fait au moins une proposition
|
512 |
* Nombre d'utilisateurs d'Identiplante ayant fait au moins une proposition
|
513 |
*/
|
513 |
*/
|
514 |
private function getNbUtilisateursAFProposition($mpm=false) {
|
514 |
private function getNbUtilisateursAFProposition($mpm=false) {
|
515 |
$requete = "SELECT COUNT(DISTINCT `utilisateur_courriel`) AS nb_total FROM del_commentaire WHERE `ce_proposition` = '' AND `nom_sel` IS NOT NULL AND `nom_sel` != ''";
|
515 |
$requete = "SELECT COUNT(DISTINCT utilisateur_courriel) AS nb_total FROM del_commentaire WHERE ce_proposition = '' AND nom_sel IS NOT NULL AND nom_sel != ''";
|
516 |
if ($this->annee != null) {
|
516 |
if ($this->annee != null) {
|
517 |
$requete .= ' AND YEAR(date) = ' . $this->annee;
|
517 |
$requete .= ' AND YEAR(date) = ' . $this->annee;
|
518 |
}
|
518 |
}
|
519 |
if ($mpm) {
|
519 |
if ($mpm) {
|
Line 525... |
Line 525... |
525 |
|
525 |
|
526 |
/**
|
526 |
/**
|
527 |
* Nombre d'utilisateurs d'Identiplante ayant fait au moins un commentaire
|
527 |
* Nombre d'utilisateurs d'Identiplante ayant fait au moins un commentaire
|
528 |
*/
|
528 |
*/
|
529 |
private function getNbUtilisateursAFCommentaire($mpm=false) {
|
529 |
private function getNbUtilisateursAFCommentaire($mpm=false) {
|
530 |
$requete = "SELECT COUNT(DISTINCT `utilisateur_courriel`) AS nb_total FROM del_commentaire WHERE `ce_proposition` != '' AND (`nom_sel` IS NULL OR `nom_sel` = '')";
|
530 |
$requete = "SELECT COUNT(DISTINCT utilisateur_courriel) AS nb_total FROM del_commentaire WHERE ce_proposition != '' AND (nom_sel IS NULL OR nom_sel = '')";
|
531 |
if ($this->annee != null) {
|
531 |
if ($this->annee != null) {
|
532 |
$requete .= ' AND YEAR(date) = ' . $this->annee;
|
532 |
$requete .= ' AND YEAR(date) = ' . $this->annee;
|
533 |
}
|
533 |
}
|
534 |
if ($mpm) {
|
534 |
if ($mpm) {
|
Line 608... |
Line 608... |
608 |
* Liste des utilisateurs dont les propositions ont été votées positivement
|
608 |
* Liste des utilisateurs dont les propositions ont été votées positivement
|
609 |
*/
|
609 |
*/
|
610 |
private function getListeMeilleursProposeurs() {
|
610 |
private function getListeMeilleursProposeurs() {
|
611 |
$liste = array();
|
611 |
$liste = array();
|
612 |
$requete = "SELECT * FROM (SELECT utilisateur_courriel, ce_utilisateur, count(prop) as nb_prop"
|
612 |
$requete = "SELECT * FROM (SELECT utilisateur_courriel, ce_utilisateur, count(prop) as nb_prop"
|
613 |
. " FROM (SELECT `ce_proposition` as prop, COUNT(DISTINCT `id_vote`) AS nb_vote FROM del_commentaire_vote where";
|
613 |
. " FROM (SELECT ce_proposition as prop, COUNT(DISTINCT id_vote) AS nb_vote FROM del_commentaire_vote where";
|
614 |
if ($this->annee != null) {
|
614 |
if ($this->annee != null) {
|
615 |
$requete .= " year(`date`) = " . $this->annee . " AND";
|
615 |
$requete .= " year(date) = " . $this->annee . " AND";
|
616 |
}
|
616 |
}
|
617 |
$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`)"
|
617 |
$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)"
|
618 |
. " AS utlisateurs WHERE nb_prop > 10 ORDER BY nb_prop DESC LIMIT 20";
|
618 |
. " AS utlisateurs WHERE nb_prop > 10 ORDER BY nb_prop DESC LIMIT 20";
|
619 |
$resultat = $this->bdd->recupererTous($requete);
|
619 |
$resultat = $this->bdd->recupererTous($requete);
|
Line 620... |
Line 620... |
620 |
|
620 |
|
621 |
// Formatage de la liste avec les intitulés des utilisateurs
|
621 |
// Formatage de la liste avec les intitulés des utilisateurs
|
Line 636... |
Line 636... |
636 |
* Liste des utilisateurs ayant fait le plus de votes positifs
|
636 |
* Liste des utilisateurs ayant fait le plus de votes positifs
|
637 |
* @TODO et le plus de votes sur des propositions retenues (ou ayant atteint un consensus)
|
637 |
* @TODO et le plus de votes sur des propositions retenues (ou ayant atteint un consensus)
|
638 |
*/
|
638 |
*/
|
639 |
private function getListeMeilleursVoteurs() {
|
639 |
private function getListeMeilleursVoteurs() {
|
640 |
$liste = array();
|
640 |
$liste = array();
|
641 |
$requete = "SELECT * FROM (SELECT courriel, ce_utilisateur, COUNT(DISTINCT `id_vote`) AS nombre FROM del_commentaire_vote, del_utilisateur where";
|
641 |
$requete = "SELECT * FROM (SELECT courriel, ce_utilisateur, COUNT(DISTINCT id_vote) AS nombre FROM del_commentaire_vote, del_utilisateur where";
|
642 |
if ($this->annee != null) {
|
642 |
if ($this->annee != null) {
|
643 |
$requete .= " year(`date`) = " . $this->annee . " AND";
|
643 |
$requete .= " year(date) = " . $this->annee . " AND";
|
644 |
}
|
644 |
}
|
645 |
$requete .= " ce_utilisateur = id_utilisateur AND valeur = 1 GROUP BY `ce_utilisateur`) AS utilisateurs WHERE nombre > 100 ORDER BY nombre DESC LIMIT 20";
|
645 |
$requete .= " ce_utilisateur = id_utilisateur AND valeur = 1 GROUP BY ce_utilisateur) AS utilisateurs WHERE nombre > 100 ORDER BY nombre DESC LIMIT 20";
|
646 |
$resultat = $this->bdd->recupererTous($requete);
|
646 |
$resultat = $this->bdd->recupererTous($requete);
|
Line 647... |
Line 647... |
647 |
|
647 |
|
648 |
// Formatage de la liste avec les intitulés des utilisateurs
|
648 |
// Formatage de la liste avec les intitulés des utilisateurs
|
649 |
$ids = array_column($resultat, 'ce_utilisateur');
|
649 |
$ids = array_column($resultat, 'ce_utilisateur');
|
Line 666... |
Line 666... |
666 |
$liste = array();
|
666 |
$liste = array();
|
667 |
$requete = "SELECT cal.nbmois, SUM(somme) / cal.nbmois as moyenne, ce_utilisateur, utilisateur_courriel FROM (SELECT count(*) as somme,"
|
667 |
$requete = "SELECT cal.nbmois, SUM(somme) / cal.nbmois as moyenne, ce_utilisateur, utilisateur_courriel FROM (SELECT count(*) as somme,"
|
668 |
. " CONCAT(YEAR(date),'-',MONTH(date)) as anneemois, ce_utilisateur, utilisateur_courriel, id_commentaire FROM del_commentaire"
|
668 |
. " CONCAT(YEAR(date),'-',MONTH(date)) as anneemois, ce_utilisateur, utilisateur_courriel, id_commentaire FROM del_commentaire"
|
669 |
. " WHERE ce_proposition = '' AND nom_sel_nn != '' AND nom_sel_nn IS NOT NULL";
|
669 |
. " WHERE ce_proposition = '' AND nom_sel_nn != '' AND nom_sel_nn IS NOT NULL";
|
670 |
if ($this->annee != null) {
|
670 |
if ($this->annee != null) {
|
671 |
$requete .= " AND year(`date`) = " . $this->annee;
|
671 |
$requete .= " AND year(date) = " . $this->annee;
|
672 |
}
|
672 |
}
|
673 |
$requete .= " GROUP BY anneemois, ce_utilisateur, utilisateur_courriel) as ppm, (SELECT count(distinct CONCAT(YEAR(date),'-',MONTH(date))) as nbmois FROM del_commentaire WHERE";
|
673 |
$requete .= " GROUP BY anneemois, ce_utilisateur, utilisateur_courriel) as ppm, (SELECT count(distinct CONCAT(YEAR(date),'-',MONTH(date))) as nbmois FROM del_commentaire WHERE";
|
674 |
if ($this->annee != null) {
|
674 |
if ($this->annee != null) {
|
675 |
$requete .= " year(`date`) = " . $this->annee . " AND";
|
675 |
$requete .= " year(date) = " . $this->annee . " AND";
|
676 |
}
|
676 |
}
|
677 |
$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"
|
677 |
$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"
|
678 |
. " ORDER BY moyenne DESC"; // @ TODO limite ?
|
678 |
. " ORDER BY moyenne DESC"; // @ TODO limite ?
|
679 |
$resultat = $this->bdd->recupererTous($requete);
|
679 |
$resultat = $this->bdd->recupererTous($requete);
|