115,7 → 115,7 |
$sousRequete = 'di.ce_observation IN '. |
"(SELECT id_observation FROM del_observation AS do WHERE $clauseWhereSousRequete ) "; |
$this->requete['where']['OR'][] = "( $sousRequete )"; |
unset($this->requete['join']['LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ']); |
unset($this->requete['join'][$this->getSqlJointureObs()]); |
} |
|
if (isset($this->requete['where']['OR']) && count($this->requete['where']['OR']) > 0) { |
296,7 → 296,7 |
} |
} |
|
private function ajouterContraintePays() { |
private function ajouterContraintePays() { |
if (isset($this->parametres['masque.pays'])) { |
// Attention le standard contient parfois FX pour la france métropolitaine |
// Dans ce cas particulier on cherche donc FR et FX |
304,12 → 304,12 |
if(strpos($this->parametres['masque.pays'], 'FR') !== false) { |
$this->parametres['masque.pays'] = str_replace('FR', 'FR,FX', $this->parametres['masque.pays']); |
} |
$pays = explode(',', $this->parametres['masque.pays']); |
$pays = implode(',', $this->bdd->proteger($pays)); |
$this->addWhere('masque.pays', "do.pays IN ($pays)"); |
|
$this->ajouterJoinObsSiNecessaire(); |
} |
$pays = explode(',', $this->parametres['masque.pays']); |
$pays = implode(',', $this->bdd->proteger($pays)); |
$this->addWhere('masque.pays', "do.pays IN ($pays)"); |
|
$this->ajouterJoinObsSiNecessaire(); |
} |
} |
|
private function ajouterContrainteIdZoneGeo() { |
382,9 → 382,13 |
|
private function ajouterJoinObsSiNecessaire() { |
if ($this->etreAppliImg()) { |
$this->addJoin('INNER JOIN del_observation AS do ON (di.ce_observation = do.id_observation) '); |
$this->addJoin($this->getSqlJointureObs()); |
} |
} |
|
private function getSqlJointureObs() { |
return 'INNER JOIN del_observation AS do ON (di.ce_observation = do.id_observation) '; |
} |
|
public function ajouterConstrainteAppliObs() { |
$this->ajouterContrainteTagCel(); |
439,16 → 443,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 '. |
455,31 → 459,29 |
'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'. |
') ' |
); |
} |
|
if ($this->etreAppliImg()) { |
$this->addJoin('LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) '); |
} |
$this->ajouterJoinObsSiNecessaire(); |
} |
} |
|
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 '. |
') '; |
} |
|
496,9 → 498,7 |
$milieuMotif = $this->bdd->proteger("%$milieu%"); |
$this->addWhere('masque.milieu', "do.milieu LIKE $milieuMotif"); |
|
if ($this->etreAppliImg()) { |
$this->addJoin('LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) '); |
} |
$this->ajouterJoinObsSiNecessaire(); |
} |
} |
|
557,9 → 557,7 |
$this->addJoin('LEFT JOIN del_image AS di ON (di.ce_observation = do.id_observation) '); |
} |
} |
if ($this->etreAppliImg()) { |
$this->addJoin('LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) '); |
} |
$this->ajouterJoinObsSiNecessaire(); |
} |
} |
|
579,8 → 577,16 |
"HAVING GROUP_CONCAT(DISTINCT tag_normalise ORDER BY tag_normalise) REGEXP $tagsMotif "; |
} |
} else { |
// Si un seul tag est demandé il se trouve dans le OR |
$tag = $this->parametres['masque.tag_del']['OR'][0]; |
// Si un seul tag est demandé il se trouve dans le OR dans le cas de la recherche |
// spécifique et dans le AND dans le cas de la recherche générale |
// WTF? |
$tag = ""; |
if(isset($this->parametres['masque.tag_del']['OR'][0])) { |
$tag = $this->parametres['masque.tag_del']['OR'][0]; |
} else if(isset($this->parametres['masque.tag_del']['AND'][0])) { |
$tag = $this->parametres['masque.tag_del']['AND'][0]; |
} |
|
$sousRequete = 'SELECT ce_image '. |
'FROM del_image_tag '. |
'WHERE actif = 1 '. |
595,8 → 601,8 |
$somme = 0; |
if (isset($this->parametres['masque.tag_del']['AND'])) { |
$somme = count($this->parametres['masque.tag_del']['AND']); |
} else { |
$somme = count($this->parametres['masque.tag_del']['OR']); |
} else { |
$somme = count($this->parametres['masque.tag_del']['OR']); |
} |
return $somme; |
} |
653,9 → 659,9 |
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: |