Subversion Repositories eFlore/Applications.del

Compare Revisions

Ignore whitespace Rev 2033 → Rev 2034

/branches/v1.8-fluor/services/bibliotheque/Sql.php
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: