WebSVN – eFlore/Applications.del – Diff – /branches/v1.8-fluor/services/bibliotheque/Sql.php

 		if (isset($this->requete['where']['OR_SOUS_REQUETE']) && count($this->requete['where']['OR_SOUS_REQUETE']) > 0) {
 			$clauseWhereSousRequete = implode(' OR ', $this->requete['where']['OR_SOUS_REQUETE']);
 			$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) {
 			$this->requete['where']['AND'][] = '('.implode(' OR ', $this->requete['where']['OR']).')';
 			$this->ajouterJoinObsSiNecessaire();
+		}
 	}
 	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
 			$this->parametres['masque.pays'] = strtoupper($this->parametres['masque.pays']);
 			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();
+		}
+	}
+		}
+	}
 	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();
 				// A CONFIRMER : toutes les observations moins les validées et à confirmer
 				// i.e. : des observations avec un nom valide, qui ne sont pas à déterminer
 				// (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();
 				$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 do.id_observation NOT IN ('.
 								'SELECT dc.ce_observation FROM del_commentaire dc '.
 								'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.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 '.
 			') ';
+	}
 		if (isset($this->parametres['masque.milieu'])) {
 			$milieu = $this->parametres['masque.milieu'];
 			$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();
+		}
+	}
 				if ($this->etreAppliObs()) {
 					$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();
+		}
+	}
 						'WHERE actif = 1 '.
 						'GROUP BY ce_image '.
 						"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
+				// 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];
+					$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 '.
 						'AND tag_normalise LIKE '.$this->bdd->proteger($tag.'%');
+			}
 	private function getNombreDeTags() {
 		$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']);
+		}
 		return $somme;
+	}
 		$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");
 				break;
 			case 'date_transmission' :
 			default:
 				$this->addOrderBy("do.date_transmission $ordre, id_observation $ordre");
+		}

Subversion Repositories eFlore/Applications.del

(root)/branches/v1.8-fluor/services/bibliotheque/Sql.php – Rev 2032 → 2034