Subversion Repositories eFlore/Applications.del

Rev

Rev 1914 | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 1914 Rev 1921
Line 43... Line 43...
43
		'where' => array(),
43
		'where' => array(),
44
		'groupby' => array(),
44
		'groupby' => array(),
45
		'orderby' => array());
45
		'orderby' => array());
Line 46... Line 46...
46
 
46
 
47
	private $champsPrenom = array('du.prenom', 'prenom_utilisateur');
47
	private $champsPrenom = array('du.prenom', 'prenom_utilisateur');
48
	private $champsNom = array('du.nom',  'nom_utilisateur');
-
 
-
 
48
	private $champsNom = array('du.nom', 'nom_utilisateur');
Line 49... Line 49...
49
 
49
	private $champsSousRequeteObs = array('masque.genre', 'masque.famille', 'masque.ns', 'masque.commune', 'masque.milieu');
50
 
50
 
51
	public function __construct(Conteneur $conteneur) {
51
	public function __construct(Conteneur $conteneur) {
52
		$this->conteneur = $conteneur;
52
		$this->conteneur = $conteneur;
Line 81... Line 81...
81
		return $this->requete;
81
		return $this->requete;
82
	}
82
	}
Line 83... Line 83...
83
 
83
 
84
	private function addJoin($join) {
84
	private function addJoin($join) {
85
		if (!isset($this->requete['join'][$join])) {
85
		if (!isset($this->requete['join'][$join])) {
86
			$this->requete['join'][] = $join;
86
			$this->requete['join'][$join] = $join;
87
		}
87
		}
Line 88... Line 88...
88
	}
88
	}
89
 
89
 
Line 96... Line 96...
96
	}
96
	}
Line 97... Line 97...
97
 
97
 
98
	private function addWhere($idParam, $where) {
98
	private function addWhere($idParam, $where) {
99
		if (isset($this->parametres['_parametres_condition_or_'])
99
		if (isset($this->parametres['_parametres_condition_or_'])
-
 
100
				&& in_array($idParam, $this->parametres['_parametres_condition_or_'])) {
-
 
101
			if ($this->etreAppliImg() && in_array($idParam, $this->champsSousRequeteObs)) {
-
 
102
				$this->requete['where']['OR_SOUS_REQUETE'][] = $where;
100
				&& in_array($idParam, $this->parametres['_parametres_condition_or_'])) {
103
			} else {
-
 
104
				$this->requete['where']['OR'][] = $where;
101
			$this->requete['where']['OR'][] = $where;
105
			}
102
		} else {
106
		} else {
103
			$this->requete['where']['AND'][] = $where;
107
			$this->requete['where']['AND'][] = $where;
104
		}
108
		}
-
 
109
	}
105
	}
110
 
-
 
111
	public function getWhere() {
-
 
112
		// Sous-requete spéciale pour éviter de rechercher dans la table obs jointe à img depuis Pictoflora...
-
 
113
		if (isset($this->requete['where']['OR_SOUS_REQUETE']) && count($this->requete['where']['OR_SOUS_REQUETE']) > 0) {
-
 
114
			$clauseWhereSousRequete = implode(' OR ', $this->requete['where']['OR_SOUS_REQUETE']);
-
 
115
			$sousRequete = 'di.ce_observation IN '.
-
 
116
				"(SELECT DISTINCT id_observation FROM del_observation AS do WHERE $clauseWhereSousRequete ) ";
-
 
117
			$this->requete['where']['OR'][] = "( $sousRequete )";
-
 
118
			unset($this->requete['join']['LEFT JOIN del_observation AS do ON (di.ce_observation = do.id_observation) ']);
-
 
119
		}
106
	public function getWhere() {
120
 
107
		if (isset($this->requete['where']['OR']) && count($this->requete['where']['OR']) > 0) {
121
		if (isset($this->requete['where']['OR']) && count($this->requete['where']['OR']) > 0) {
108
			$this->requete['where']['AND'][] = '('.implode(' OR ', $this->requete['where']['OR']).')';
122
			$this->requete['where']['AND'][] = '('.implode(' OR ', $this->requete['where']['OR']).')';
Line 109... Line 123...
109
		}
123
		}
Line 449... Line 463...
449
				$tags = $this->parametres['masque.tag_cel']['AND'];
463
				$tags = $this->parametres['masque.tag_cel']['AND'];
450
				$clausesWhere = array();
464
				$clausesWhere = array();
451
				foreach ($tags as $tag) {
465
				foreach ($tags as $tag) {
452
					$tagMotif = $this->bdd->proteger("%$tag%");
466
					$tagMotif = $this->bdd->proteger("%$tag%");
453
					if ($this->etreAppliImg()) {
467
					if ($this->etreAppliImg()) {
-
 
468
						$sousRequete = 'SELECT DISTINCT id_observation '.
-
 
469
							'FROM del_observation '.
-
 
470
							"WHERE mots_cles_texte LIKE $tagMotif ";
454
						$sql = " (do.mots_cles_texte LIKE $tagMotif OR di.mots_cles_texte LIKE $tagMotif) ";
471
						$sql = " (di.mots_cles_texte LIKE $tagMotif OR di.id_image IN ($sousRequete) ) ";
455
					} else {
472
					} else {
456
						// WARNING : la sous-requête est la meilleure solution trouvée pour contrer le fonctionnement
473
						// WARNING : la sous-requête est la meilleure solution trouvée pour contrer le fonctionnement
457
						// étrange de l'optimiseur de MYSQL 5.6 (à retester avec Mysql 5.7 et suivant).
474
						// étrange de l'optimiseur de MYSQL 5.6 (à retester avec Mysql 5.7 et suivant).
458
						$sousRequete = 'SELECT DISTINCT ce_observation '.
475
						$sousRequete = 'SELECT DISTINCT ce_observation '.
459
							'FROM del_image '.
476
							'FROM del_image '.
Line 465... Line 482...
465
				}
482
				}
466
				$whereTags = implode(' AND ', $clausesWhere);
483
				$whereTags = implode(' AND ', $clausesWhere);
467
				$this->addWhere('masque.tag_cel', "($whereTags)");
484
				$this->addWhere('masque.tag_cel', "($whereTags)");
468
			} else if (isset($this->parametres['masque.tag_cel']['OR'])) {
485
			} else if (isset($this->parametres['masque.tag_cel']['OR'])) {
469
				$tags = $this->parametres['masque.tag_cel']['OR'];
486
				$tags = $this->parametres['masque.tag_cel']['OR'];
470
				$sqlTpl = "CONCAT(IFNULL(do.mots_cles_texte,''),IFNULL(di.mots_cles_texte,'')) REGEXP %s";
-
 
471
				$tagMotif = $this->bdd->proteger(implode('|', $tags));
487
				$tagMotif = $this->bdd->proteger(implode('|', $tags));
-
 
488
				$sqlTpl = "CONCAT(IFNULL(do.mots_cles_texte,''),IFNULL(di.mots_cles_texte,'')) REGEXP %s";
472
				$tagSql = sprintf($sqlTpl, $tagMotif);
489
				$tagSql = sprintf($sqlTpl, $tagMotif);
-
 
490
 
473
				$this->addWhere('masque.tag_cel', $tagSql);
491
				$this->addWhere('masque.tag_cel', $tagSql);
-
 
492
 
474
				if ($this->etreAppliObs()) {
493
				if ($this->etreAppliObs()) {
475
					$this->addJoin('LEFT JOIN del_image AS di ON (di.ce_observation = do.id_observation) ');
494
					$this->addJoin('LEFT JOIN del_image AS di ON (di.ce_observation = do.id_observation) ');
476
				}
495
				}
477
			}
496
			}
478
			if ($this->etreAppliImg()) {
497
			if ($this->etreAppliImg()) {
Line 484... Line 503...
484
	/**
503
	/**
485
	 * Plusieurs solutions sont disponibles dans les anciennes versions (voir DelTk et l'historique SVN de ce fichier).
504
	 * Plusieurs solutions sont disponibles dans les anciennes versions (voir DelTk et l'historique SVN de ce fichier).
486
	 */
505
	 */
487
	private function ajouterContrainteTagDel() {
506
	private function ajouterContrainteTagDel() {
488
		if (isset($this->parametres['masque.tag_del'])) {
507
		if (isset($this->parametres['masque.tag_del'])) {
489
			if (isset($this->parametres['masque.tag_del']['AND'])) {
-
 
490
				$tags = $this->parametres['masque.tag_del']['AND'];
-
 
491
				// optimisation: en cas de "AND" on sort() l'input et le GROUP_CONCAT()
-
 
492
				// donc nous utilisons des ".*" plutôt que de multiples conditions et "|"
-
 
493
				sort($tags);
-
 
494
				$tagsMotif = $this->bdd->proteger(implode('.*', $tags));
508
			$tagsMotif = $this->construireTagsMotif();
495
				$requete = 'SELECT ce_image '.
-
 
496
					'FROM del_image_tag '.
-
 
497
					'WHERE actif = 1 '.
-
 
498
					'GROUP BY ce_image '.
-
 
499
					"HAVING GROUP_CONCAT(tag_normalise ORDER BY tag_normalise) REGEXP $tagsMotif ".
-
 
500
					' -- '.__FILE__.' : '.__LINE__;
-
 
501
				$sql = $this->recupererSqlContrainteTag($requete);
-
 
502
				$this->addWhere('masque.tag_del', $sql);
509
			if (is_null($tagsMotif) === false) {
503
 
-
 
504
			} else if (isset($this->parametres['masque.tag_del']['OR'])) {
-
 
505
				$tags = $this->parametres['masque.tag_del']['OR'];
-
 
506
				$tagsMotif = $this->bdd->proteger(implode('|', $tags));
-
 
507
				$requete = 'SELECT ce_image '.
510
				$sousRequete = 'SELECT ce_image '.
508
					'FROM del_image_tag '.
511
					'FROM del_image_tag '.
509
					'WHERE actif = 1 '.
512
					'WHERE actif = 1 '.
510
					'GROUP BY ce_image '.
513
					'GROUP BY ce_image '.
511
					"HAVING GROUP_CONCAT(tag_normalise) REGEXP $tagsMotif ".
514
					"HAVING GROUP_CONCAT(DISTINCT tag_normalise ORDER BY tag_normalise) REGEXP $tagsMotif ";
512
					' -- '.__FILE__.' : '.__LINE__;
-
 
Line 513... Line -...
513
 
-
 
514
				$sql = $this->recupererSqlContrainteTag($requete);
515
 
515
				$this->addWhere('masque.tag_del', $sql);
516
				$this->addWhere('masque.tag_del', "di.id_image IN ($sousRequete)");
516
			}
517
			}
517
		}
518
		}
Line 518... Line 519...
518
	}
519
	}
519
 
-
 
520
	private function recupererSqlContrainteTag($requete) {
520
 
521
		$resultats = $this->bdd->recupererTous($requete);
521
	private function construireTagsMotif() {
522
		$ids = array();
522
		$tagsMotif = null;
523
		foreach ($resultats as $resultat) {
-
 
524
			$ids[] = $resultat['ce_image'];
-
 
-
 
523
		if (isset($this->parametres['masque.tag_del']['AND'])) {
-
 
524
			$tags = $this->parametres['masque.tag_del']['AND'];
525
		}
525
			// ATTENTION -> optimisation: en cas de "AND" on sort() l'input et le GROUP_CONCAT()
526
 
526
			// donc nous utilisons des ".*" plutôt que de multiples conditions et "|"
527
		if (!empty($ids)) {
527
			sort($tags);
528
			$clauseIn = implode(',', $ids);
528
			$tagsMotif = $this->bdd->proteger(implode('.*', $tags));
-
 
529
		} else if (isset($this->parametres['masque.tag_del']['OR'])) {
529
		} else {
530
			$tags = $this->parametres['masque.tag_del']['OR'];
530
			$clauseIn = 'SELECT ce_image FROM del_image_tag WHERE false';
531
			$tagsMotif = $this->bdd->proteger(implode('|', $tags));
531
		}
532
		}
Line 532... Line 533...
532
		return "di.id_image IN ($clauseIn)";
533
		return $tagsMotif;
533
	}
534
	}
534
 
535