Subversion Repositories eFlore/Applications.cel

Rev

Rev 2026 | Rev 2032 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 2026 Rev 2027
Line 14... Line 14...
14 
	private $config;
 14 
	private $config;
15 
	private $mode;
 15 
	private $mode;
Line 16... Line 16...
16
16
17 
	private $table_liaison;
 17 
	private $table_liaison;
- 
 
 18 
	private $table_mots_cles;
Line 18... Line 19...
18 
	private $table_mots_cles;
 19 
	private $champ_id_element_lie;
19
20
20 
	public function GestionMotsClesChemin($config, $mode = 'obs') {
 21 
	public function GestionMotsClesChemin($config, $mode = 'obs') {
- 
 
 22 
		$this->config = $config;
- 
 
 23 
		//TODO: switch suivant mode
- 
 
 24 
		$this->mode = $mode;
21 
		$this->config = $config;
 25
22 
		//TODO: switch suivant mode
 26 
		if($mode == 'obs') {
- 
 
 27 
			$this->table_liaison = 'cel_obs_tags_path_liaison';
- 
 
 28 
			$this->table_mots_cles = 'cel_obs_tags_path';
- 
 
 29 
			$this->champ_id_element_lie = 'id_obs';
- 
 
 30 
		} else {
- 
 
 31 
			$this->table_liaison = 'cel_images_tags_path_liaison';
- 
 
 32 
			$this->table_mots_cles = 'cel_images_tags_path';
23 
		$this->table_liaison = 'cel_obs_tags_path_liaison';
 33 
			$this->champ_id_element_lie = 'id_image';
Line 24... Line 34...
24 
		$this->table_mots_cles = 'cel_obs_tags_path';
 34 
		}
25 
	}
 35 
	}
26
36
Line 32... Line 42...
32 
		$vidage = Cel::db()->executer($requete.' -- '.__FILE__.':'.__LINE__);
 42 
		$vidage = Cel::db()->executer($requete.' -- '.__FILE__.':'.__LINE__);
Line 33... Line 43...
33
43
34 
		return $vidage;
 44 
		return $vidage;
Line 35... Line 45...
35 
	}
 45 
	}
- 
 
 46
- 
 
 47 
	public function construireRequeteOr($mots_cles) {
- 
 
 48 
		return $this->construireRequete($mots_cles, 'OR');
- 
 
 49 
	}
- 
 
 50
- 
 
 51 
	public function construireRequeteCheminOr($mots_cles) {
- 
 
 52 
		return $this->construireRequeteChemin($mots_cles, 'OR');
- 
 
 53 
	}
- 
 
 54
- 
 
 55 
	public function construireRequeteAnd($mots_cles) {
- 
 
 56 
		return $this->construireRequete($mots_cles, 'AND');
- 
 
 57 
	}
- 
 
 58
- 
 
 59 
	public function construireRequeteCheminAnd($mots_cles) {
- 
 
 60 
		return $this->construireRequeteChemin($mots_cles, 'AND');
- 
 
 61 
	}
- 
 
 62
- 
 
 63 
	public function construireRequete($mots_cles, $operateur = 'AND') {
- 
 
 64 
		$requete = "SELECT * FROM cel_images_tags_path_liaison ";
- 
 
 65 
		$criteres = array();
- 
 
 66 
		$premier = array_pop($mots_cles);
- 
 
 67 
		$requete .= 'WHERE (tag = '.Cel::db()->proteger($premier).' ';
- 
 
 68
- 
 
 69 
		foreach ($mots_cles as $mot_cle) {
- 
 
 70 
			$requete .= " ".$operateur." id_image IN (SELECT id_image ".
- 
 
 71 
										"FROM cel_images_tags_path_liaison ".
- 
 
 72 
										"WHERE tag = ".Cel::db()->proteger($mot_cle)." ".
- 
 
 73 
										") ";
- 
 
 74 
		}
- 
 
 75 
		$requete .= ")";
- 
 
 76 
		$images = Cel::db()->requeter($requete);
36
77 
		return $images;
- 
 
 78 
	}
- 
 
 79
37 
	public function convertirNestedSetVersPathEnum() {
 80 
	private function construireRequeteChemin($mots_cles, $operateur = 'AND') {
- 
 
 81 
		$requete = "SELECT * FROM cel_images_tags_path_liaison ";
- 
 
 82 
		$criteres = array();
- 
 
 83 
		$premier = array_pop($mots_cles);
- 
 
 84 
		$requete .= 'WHERE (chemin LIKE '.Cel::db()->proteger('%/'.$premier.'/%').' ';
- 
 
 85
- 
 
 86 
		foreach ($mots_cles as $mot_cle) {
- 
 
 87 
			$requete .= " ".$operateur." id_image IN (SELECT id_image ".
- 
 
 88 
												"FROM cel_images_tags_path_liaison ".
- 
 
 89 
												"WHERE chemin LIKE ".Cel::db()->proteger('%/'.$mot_cle.'/%')." ".
- 
 
 90 
												") ";
- 
 
 91 
		}
- 
 
 92 
		$requete .= ")";
- 
 
 93 
		$images = Cel::db()->requeter($requete);
- 
 
 94 
		return $images;
- 
 
 95 
	}
Line -... Line 96...
- 
 
 96
- 
 
 97 
	private function obtenirMotsClesOriginaux($mode) {
38
98
- 
 
 99 
		if($mode == 'obs') {
- 
 
 100 
			$requete_arbre = "SELECT id_mot_cle_obs as id_mot_cle, ce_mot_cle_obs_parent as parent, id_utilisateur, mot_cle ".
- 
 
 101 
							"FROM cel_mots_cles_obs_srv ORDER BY id_utilisateur, niveau";
- 
 
 102 
		} else {
- 
 
 103 
			$requete_arbre = "SELECT id_mot_cle_image as id_mot_cle, ce_mot_cle_image_parent as parent, id_utilisateur, mot_cle ".
39 
		$time_start = microtime();
 104 
							"FROM cel_mots_cles_images_srv ORDER BY id_utilisateur, niveau";
Line 40... Line -...
40
- 
 
41 
		$requete_arbre = "SELECT * FROM cel_mots_cles_obs_srv ORDER BY id_utilisateur, niveau";
 - 
 
42 
		$mots_cles = Cel::db()->requeter($requete_arbre);
 - 
 
43
- 
 
44 
		$requete_liaisons = "SELECT comc.id_observation, comc.id_mot_cle_obs, o.ce_utilisateur as id_utilisateur ".
 - 
 
45 
									"FROM cel_obs_mots_cles comc ".
 - 
 
46 
									"INNER JOIN cel_obs o ON o.id_observation = comc.id_observation ";
 105 
		}
47
106
Line -... Line 107...
- 
 
 107 
		$mots_cles = Cel::db()->requeter($requete_arbre);
- 
 
 108
48 
		$liaisons = Cel::db()->requeter($requete_liaisons);
 109 
		$mots_cles_hierarchiques = array();
49
110 
		$correspondances_id_mots_cles_obs = array();
- 
 
 111
50 
		$mots_cles_hierarchiques = array();
 112 
		$rien = 0;
- 
 
 113
51 
		$correspondances_id_mots_cles_obs = array();
 114 
		foreach($mots_cles as &$mot) {
52
115
- 
 
 116 
			if(!empty($mot['mot_cle'])) {
- 
 
 117 
				$chemin = '/';
- 
 
 118 
				$cle_parent = $mot['parent'].'-'.$mot['id_utilisateur'];
- 
 
 119 
				if(isset($mots_cles_hierarchiques[$cle_parent])) {
- 
 
 120 
					$chemin = $mots_cles_hierarchiques[$cle_parent]['chemin'];
- 
 
 121 
				}
- 
 
 122 
				$chemin .= self::simplifier($mot['mot_cle']).'/';
- 
 
 123 
				$chemin = str_replace("//", "/", $chemin);
- 
 
 124
- 
 
 125 
				$cle = $mot['id_mot_cle'].'-'.$mot['id_utilisateur'];
- 
 
 126
53 
		foreach($mots_cles as &$mot) {
 127 
				$mots_cles_hierarchiques[$cle] = array(
54 
 
 - 
 
55 
			$chemin = '/';
 - 
 
56 
			if(isset($mots_cles_hierarchiques[$mot['ce_mot_cle_obs_parent']])) {
 - 
 
57 
				$chemin = $mots_cles_hierarchiques[$mot['ce_mot_cle_obs_parent']]['chemin'];
 - 
 
58 
			}
 - 
 
59 
			$chemin .= self::simplifier($mot['mot_cle']).'/';
 - 
 
60 
			$chemin = str_replace("//", "", $chemin);
 - 
 
61
- 
 
62 
			$cle = $mot['id_mot_cle_obs'].'-'.$mot['id_utilisateur'];
 - 
 
63
- 
 
64 
			$mots_cles_hierarchiques[$cle] = array(
 128 
								'id_utilisateur' => $mot['id_utilisateur'],
Line 65... Line 129...
65 
					'id_utilisateur' => $mot['id_utilisateur'],
 129 
								'chemin' => $chemin,
- 
 
 130 
								'tag' => $mot['mot_cle']
- 
 
 131 
				);
- 
 
 132 
			}
66 
					'chemin' => $chemin,
 133 
		}
- 
 
 134
- 
 
 135 
		return $mots_cles_hierarchiques;
- 
 
 136 
	}
- 
 
 137
- 
 
 138 
	private function obtenirLiaisonsMotsClesOriginaux($mode) {
- 
 
 139 
		if($mode == 'obs') {
- 
 
 140 
			$requete_liaisons = "SELECT comc.id_observation as id_element_lie, comc.id_mot_cle_obs as id_mot_cle, id_utilisateur as id_utilisateur ".
- 
 
 141 
														"FROM cel_obs_mots_cles_srv comc ";
Line -... Line 142...
- 
 
 142 
														//"INNER JOIN cel_obs o ON o.id_observation = comc.id_observation ";
- 
 
 143 
		} else {
- 
 
 144 
			$requete_liaisons = "SELECT comc.id_image as id_element_lie, comc.id_mot_cle_image as id_mot_cle, id_utilisateur as id_utilisateur ".
- 
 
 145 
																	"FROM cel_images_mots_cles_srv comc ";
- 
 
 146 
																	//"INNER JOIN cel_images o ON o.id_image = comc.id_image ";
- 
 
 147 
		}
- 
 
 148
- 
 
 149 
		$liaisons = Cel::db()->requeter($requete_liaisons);
- 
 
 150 
		return $liaisons;
- 
 
 151 
	}
- 
 
 152
- 
 
 153 
	public function convertirNestedSetVersPathEnum() {
- 
 
 154
- 
 
 155 
		$mots_cles_hierarchiques = $this->obtenirMotsClesOriginaux($this->mode);
- 
 
 156 
		$liaisons = $this->obtenirLiaisonsMotsClesOriginaux($this->mode);
- 
 
 157
- 
 
 158 
		/*foreach($mots_cles_hierarchiques as $mot) {
67 
					'tag' => $mot['mot_cle']
 159 
			echo $mot['chemin'].'<br />';
68 
			);
 160 
		}*/
69 
		}
 161 
		//echo '<pre>'.print_r($liaisons,true).'</pre>';
70
162 
		//exit;
Line 86... Line 178...
86
178
87 
			$requete_insertion_mots_cles = rtrim($requete_insertion_mots_cles, ", ");
 179 
			$requete_insertion_mots_cles = rtrim($requete_insertion_mots_cles, ", ");
88 
			cel::db()->executer($requete_insertion_mots_cles);
 180 
			cel::db()->executer($requete_insertion_mots_cles);
Line -... Line 181...
- 
 
 181 
		}
89 
		}
 182
90
183 
		$gne = 0;
91 
		$liaisons_slice = array_chunk($liaisons, 800, true);
 184 
		$liaisons_slice = array_chunk($liaisons, 800, true);
92 
		foreach($liaisons_slice as &$liaison_tranche) {
 185 
		foreach($liaisons_slice as &$liaison_tranche) {
93 
			$requete_insertion_liaisons = "INSERT INTO ".$this->table_liaison." (id_obs, chemin, id_utilisateur) ".
 186 
			$requete_insertion_liaisons = "INSERT INTO ".$this->table_liaison." (".$this->champ_id_element_lie.", chemin, tag, id_utilisateur) ".
Line 94... Line 187...
94 
									      " VALUES ";
 187 
									      " VALUES ";
95 
			$lier = false;
 188 
			$lier = false;
96
189
97 
			foreach($liaison_tranche as &$liaison_obs_mot_cle) {
 190 
			foreach($liaison_tranche as &$liaison_obs_mot_cle) {
98 
				$cle = $liaison_obs_mot_cle['id_mot_cle_obs'].'-'.$liaison_obs_mot_cle['id_utilisateur'];
 191 
				$cle = $liaison_obs_mot_cle['id_mot_cle'].'-'.$liaison_obs_mot_cle['id_utilisateur'];
99 
				if(isset($mots_cles_hierarchiques[$cle])) {
 192 
				if(isset($mots_cles_hierarchiques[$cle])) {
- 
 
 193 
					$requete_insertion_liaisons .= "(".
100 
					$requete_insertion_liaisons .= "(".
 194 
						cel::db()->proteger($liaison_obs_mot_cle['id_element_lie']).", ".
101 
						cel::db()->proteger($liaison_obs_mot_cle['id_observation']).", ".
 195 
						cel::db()->proteger($mots_cles_hierarchiques[$cle]['chemin']).", ".
102 
						cel::db()->proteger($mots_cles_hierarchiques[$cle]['chemin']).", ".
 196 
						cel::db()->proteger($mots_cles_hierarchiques[$cle]['tag']).", ".
103 
						cel::db()->proteger($liaison_obs_mot_cle['id_utilisateur'])." ".
 197 
						cel::db()->proteger($liaison_obs_mot_cle['id_utilisateur'])." ".
104 
					"), ";
 198 
					"), ";
Line 110... Line 204...
110 
				$requete_insertion_liaisons = rtrim($requete_insertion_liaisons, ", ");
 204 
				$requete_insertion_liaisons = rtrim($requete_insertion_liaisons, ", ");
111 
				cel::db()->executer($requete_insertion_liaisons);
 205 
				cel::db()->executer($requete_insertion_liaisons);
112 
			}
 206 
			}
113 
		}
 207 
		}
Line -... Line 208...
- 
 
 208
114
209 
		$time = microtime() - $time_start;
115 
		echo "La génération d'un tableau de ".count($mots_cles_hierarchiques).' éléments a pris '.$time.' secondes';
 210 
		echo "La génération d'un tableau de ".count($mots_cles_hierarchiques).' éléments et de '.count($liaisons).' liaisons a pris '.$time.' secondes';
116 
		exit;
 211 
		exit;
Line 117... Line 212...
117 
	}
 212 
	}
Line 167... Line 262...
167 
	public function lierParId($id_mot_cle, $id_observation, $id_utilisateur) {
 262 
	public function lierParId($id_mot_cle, $id_observation, $id_utilisateur) {
Line 168... Line 263...
168
263
169 
		$sous_requete_chemin = "(SELECT chemin FROM ".$this->table_mots_cles." ".
 264 
		$sous_requete_chemin = "(SELECT chemin FROM ".$this->table_mots_cles." ".
Line 170... Line 265...
170 
				   		"WHERE id_tag = ".Cel::db()->proteger($id_mot_cle).") ";
 265 
				   		"WHERE id_tag = ".Cel::db()->proteger($id_mot_cle).") ";
171
266
172 
		$requete = "INSERT INTO ".$this->table_liaison." (id_obs, chemin, id_utilisateur) ".
 267 
		$requete = "INSERT INTO ".$this->table_liaison." (".$this->champ_id_element_lie.", chemin, id_utilisateur) ".
173 
					"VALUES (".
 268 
					"VALUES (".
174 
				   		Cel::db()->proteger($id_observation).", ".
 269 
				   		Cel::db()->proteger($id_observation).", ".
175 
				   		$sous_requete_chemin.",".
 270 
				   		$sous_requete_chemin.",".
176 
				   		Cel::db()->proteger($id_utilisateur)." ".
 271 
				   		Cel::db()->proteger($id_utilisateur)." ".
Line 177... Line 272...
177 
				   	") ".
 272 
				   	") ".
Line 178... Line 273...
178 
					"ON DUPLICATE KEY UPDATE id_obs = id_obs; ";
 273 
					"ON DUPLICATE KEY UPDATE ".$this->champ_id_element_lie." = ".$this->champ_id_element_lie." ";
179
274
Line 188... Line 283...
188 
					"VALUES (".
 283 
					"VALUES (".
189 
						Cel::db()->proteger($id_observation).", ".
 284 
						Cel::db()->proteger($id_observation).", ".
190 
						Cel::db()->proteger($sous_requete_chemin).", ".
 285 
						Cel::db()->proteger($sous_requete_chemin).", ".
191 
						Cel::db()->proteger($id_utilisateur)." ".
 286 
						Cel::db()->proteger($id_utilisateur)." ".
192 
				   	") ".
 287 
				   	") ".
193 
					"ON DUPLICATE KEY UPDATE id_obs = id_obs; ";
 288 
					"ON DUPLICATE KEY UPDATE ".$this->champ_id_element_lie." = ".$this->champ_id_element_lie." ";
Line 194... Line 289...
194
289
Line 195... Line 290...
195 
		$liaison = Cel::db()->executer($requete.' -- '.__FILE__.':'.__LINE__);
 290 
		$liaison = Cel::db()->executer($requete.' -- '.__FILE__.':'.__LINE__);
196
291