Subversion Repositories eFlore/Applications.del

Rev

Rev 2018 | Rev 2020 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 2018 Rev 2019
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);