| 435,16 → 435,16 |
|---|
| // (ni certitude "aDeterminer" ou "douteuse", ni mot clé), |
| // ne possédant pas de proposition officiellement retenue |
| // et ayant une proposition initiale totalisant un score de moins de 4 |
| $sous_requete_score_prop_votees = $this->getSousRequeteSommeVotesPropositions(); |
| $sous_requete_score_prop_votees = $this->getSousRequeteSommeVotesPropositions(); |
| $this->addWhere('masque.type', |
| '('. |
| 'do.id_observation IN ('. |
| 'SELECT dc.ce_observation FROM del_commentaire dc WHERE dc.proposition_retenue = 0'. |
| ' AND ( '. |
| 'dc.proposition_initiale = 1 '. |
| 'AND dc.nom_sel_nn != 0 '. |
| 'AND dc.nom_sel_nn IS NOT NULL '. |
| 'AND dc.id_commentaire IN ('.$sous_requete_score_prop_votees.' < 4) '. |
| ' AND ( '. |
| 'dc.proposition_initiale = 1 '. |
| 'AND dc.nom_sel_nn != 0 '. |
| 'AND dc.nom_sel_nn IS NOT NULL '. |
| 'AND dc.id_commentaire IN ('.$sous_requete_score_prop_votees.' < 4) '. |
| ') '. |
| ') AND do.id_observation NOT IN ('. |
| 'SELECT dc.ce_observation FROM del_commentaire dc '. |
| 451,9 → 451,9 |
|---|
| 'WHERE '. |
| ' dc.proposition_retenue = 1'. |
| ') '. |
| 'AND do.certitude != "douteux" AND do.certitude != "aDeterminer" '. |
| 'AND do.mots_cles_texte NOT LIKE "%aDeterminer%" '. |
| 'AND do.nom_sel_nn != 0 '. |
| 'AND do.certitude != "douteux" AND do.certitude != "aDeterminer" '. |
| 'AND do.mots_cles_texte NOT LIKE "%aDeterminer%" '. |
| 'AND do.nom_sel_nn != 0 '. |
| 'AND do.nom_sel_nn IS NOT NULL'. |
| ') ' |
| ); |
| 467,15 → 467,15 |
|---|
| |
| private function getSousRequeteSommeVotesPropositions() { |
| // ATTENTION : un vote identifié compte 3 votes anonymes (dans les deux sens) |
| return 'SELECT ce_proposition '. |
| 'FROM del_commentaire_vote dcv '. |
| '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 '. |
| return 'SELECT ce_proposition '. |
| 'FROM del_commentaire_vote dcv '. |
| '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 '. |
| ') '; |
| } |
| |
| 599,8 → 599,9 |
|---|
| */ |
| public function definirOrdreSqlAppliImg() { |
| $ordre = $this->parametres['ordre']; |
| |
| switch ($this->parametres['tri']) { |
| |
| $tri = isset($this->parametres['tri']) ? $this->parametres['tri'] : ''; |
| switch ($tri) { |
| case 'moyenne-arithmetique' : |
| $this->addOrderBy("dis.moyenne $ordre, dis.nb_votes $ordre, id_image $ordre"); |
| break; |
| 623,13 → 624,14 |
|---|
| $ordre = $this->parametres['ordre']; |
| |
| // parmi self::$tri_possible |
| switch ($this->parametres['tri']) { |
| $tri = isset($this->parametres['tri']) ? $this->parametres['tri'] : ''; |
| switch ($tri) { |
| case 'date_observation' : |
| $this->addOrderBy("date_observation $ordre, id_observation $ordre"); |
| break; |
| case 'nb_commentaires' : |
| $sql_nb_comms = '(SELECT COUNT(id_commentaire) FROM del_commentaire AS dc WHERE ce_observation = id_observation)'; |
| $this->addOrderBy("$sql_nb_comms $ordre, id_observation $ordre"); |
| case 'nb_commentaires' : |
| $sql_nb_comms = '(SELECT COUNT(id_commentaire) FROM del_commentaire AS dc WHERE ce_observation = id_observation)'; |
| $this->addOrderBy("$sql_nb_comms $ordre, id_observation $ordre"); |
| break; |
| case 'date_transmission' : |
| default: |