WebSVN – eFlore/Applications.cel – Diff – /trunk/jrest/scripts/MigrationMotsCles.php

 		self::$bdd_cel_migration = $this->config['database_cel']['database_migration'];
 		self::$bdd_utilisateurs = $this->config['database_ident']['database'];
 		echo "--MIGRATION DES MOTS CLES --------------------------------------\n";
 		if (self::truncate) {
-			echo "-------------------------------------------------------------------\n\n";
+			echo "-------------------------------------------------------------------\n";
 			echo "  ETAPE 0. Vider les tables ... \n\n";
-			echo "-------------------------------------------------------------------\n\n";
+			echo "-------------------------------------------------------------------\n";
 			$this->viderTables();
-			echo "\n---------------------------------------------------------------- OK\n\n";
+			echo "---------------------------------------------------------------- OK\n";
 		}
-		echo "-------------------------------------------------------------------\n\n";
+		echo "-------------------------------------------------------------------\n";
-		echo "  ETAPE 1. Paramétrage ... \n\n";
+		echo "  ETAPE 1. Paramétrage ... \n";
-		echo "-------------------------------------------------------------------\n\n";
+		echo "-------------------------------------------------------------------\n";
 		$this->getUtilisateurs();
 		echo "\n"."\n"."\n";
-		echo "-------------------------------------------------------------------\n\n";
+		echo "-------------------------------------------------------------------\n";
-		echo "  ETAPE 2. Migration des mots clés ... \n\n";
+		echo "  ETAPE 2. Migration des mots clés ... \n";
-		echo "-------------------------------------------------------------------\n\n";
+		echo "-------------------------------------------------------------------\n";
 		$this->migrerTableMotsClesObs();
-		echo "\n---------------------------------------------------------------- OK\n\n";
+		echo "\n---------------------------------------------------------------- OK\n";
 		$this->migrerTableMotsClesImages();
 		echo "\n"."\n"."\n";
-		echo "-------------------------------------------------------------------\n\n";
+		echo "-------------------------------------------------------------------\n";
-		echo "  ETAPE 3. Migration des liaisons mots clés ... \n\n";
+		echo "  ETAPE 3. Migration des liaisons mots clés ... \n";
-		echo "-------------------------------------------------------------------\n\n";
+		echo "-------------------------------------------------------------------\n";
 		$this->migrerLiaisonsMotsClesObs();
 		$this->migrerLiaisonsMotsClesImages();
 		echo "\n"."\n"."\n";
-		echo "-------------------------------------------------------------------\n\n";
+		echo "-------------------------------------------------------------------\n";
-		echo "  ETAPE 4. Génération des index des mots clés ... \n\n";
+		echo "  ETAPE 4. Génération des index des mots clés ... \n";
-		echo "-------------------------------------------------------------------\n\n";
+		echo "-------------------------------------------------------------------\n";
 		$this->genererIndexTexteMotsClesObs();
 			if (count($arbres_mots_cles) > 0) {
 				$champ_parent = ($image_ou_obs == "obs") ? 'ce_mot_cle_obs_parent' : 'ce_mot_cle_image_parent';
 				$champ_id = ($image_ou_obs == "obs") ? 'id_mot_cle_obs' : 'id_mot_cle_image';
 				$requete = 'INSERT INTO '.self::$bdd_cel_migration.".cel_mots_cles_$image_ou_obs ".
-						"($champ_id, id_utilisateur, mot_cle, md5, bg, bd, niveau, $champ_parent) ".
+					"($champ_id, id_utilisateur, mot_cle, md5, bg, bd, niveau, $champ_parent) ".
-						'VALUES ';
+					'VALUES ';
 				$sous_requete = array();
 				foreach ($arbres_mots_cles as $arbre_mot_cle) {
+	}
 	private function migrerLiaisonsMotsClesObs() {
 		$requete = 'SELECT mots_cles AS mots_cles, id AS id, identifiant AS id_utilisateur '.
 				'FROM cel_inventory '.
+				'WHERE mots_cles != "" '.
+				'	AND mots_cles != "NULL" '.
-				'WHERE mots_cles != "" '.
+				'	AND mots_cles != "null" '.
 				'	AND mots_cles IS NOT NULL '.
 				'ORDER BY identifiant ';
 		$mots_cles_obs = $this->executerRequete($requete);
+	}
 	private function migrerLiaisonsMotsClesImages() {
 		$requete = 'SELECT ci_meta_mots_cles AS mots_cles, ci_id_image AS id, ci_ce_utilisateur AS id_utilisateur '.
 				'FROM cel_images '.
+				'WHERE ci_meta_mots_cles != "" '.
+				'	AND ci_meta_mots_cles != "NULL" '.
-				'WHERE ci_meta_mots_cles != "" '.
+				'	AND ci_meta_mots_cles != "null" '.
 				'	AND ci_meta_mots_cles IS NOT NULL '.
 				'ORDER BY ci_ce_utilisateur';
 		$mots_cles_images = $this->executerRequete($requete);
 		$sous_requete_chaine = implode(',', $sous_requete);
 		return $sous_requete_chaine;
+	}
+	private function parserMotsCles($mot_cles, $separateur = ',') {
-	private function parserMotsCles($mot_cles, $separateur = ',') {
+		$mot_cles = trim($mot_cles, $separateur);
 		$tableau_mots_cles = explode($separateur, $mot_cles);
 		$tableau_mots_cles_formates = array();
 		foreach ($tableau_mots_cles as $mot_cle) {
+			$mot_cle = str_replace($separateur.$separateur, '', $mot_cle);
-			$mot_cle = str_replace($separateur.$separateur, '', $mot_cle);
+			$mot_cle = str_replace('null', '', $mot_cle);
-			$mot_cle = str_replace('null', '', $mot_cle);
+			$mot_cle = trim($mot_cle);
 			if ($this->estUnIdentifiantMotCle($mot_cle)) {
 				} else {
 					// on met le mot clé dans sa propre case afin d'éviter
 					// facilement les doublons provoqués par de mauvais formatages
 					$tableau_mots_cles_formates[$mot_cle] = $mot_cle;
+				}
+			} else if ($mot_cle != '') {
+				echo "N'est pas un mot clé : $mot_cle\n";
+			}
+		}
 		return $tableau_mots_cles_formates;
+	}
 	private function estUnIdentifiantMotCle($chaine) {
-		return trim($chaine) != '' && preg_match('/^(?:[0-9]+\.[0-9]+|[-_a-z0-9]+(?:[.][-_a-z0-9]+)*)$/i', $chaine);
+		return preg_match('/^(?:[-][0-9]+[.][0-9]+|[-_a-z0-9]+(?:[.][-_a-z0-9]+)*)$/i', $chaine);
+	}
 	private function genererIndexTexteMotsClesObs() {
-		$requete_selection_obs = 'SELECT DISTINCT id_observation, ce_utilisateur '.
-			'FROM '.self::$bdd_cel_migration.'.cel_obs '.
+		$requete = 'SELECT DISTINCT id_observation, id_utilisateur '.
-			'WHERE id_observation IN (SELECT DISTINCT id_observation FROM '.self::$bdd_cel_migration.'.cel_mots_cles_obs) ';
+			'FROM '.self::$bdd_cel_migration.'.cel_obs_mots_cles ';
-		$obs_a_mots_cles = $this->requeter($requete_selection_obs);
+		$obs_a_mots_cles = $this->requeter($requete);
 		foreach ($obs_a_mots_cles as $obs) {
-			$mots_cles_texte = $this->obtenirMotsClesTexte($obs['id_observation'], $obs['ce_utilisateur'], 'obs');
+			$mots_cles_texte = $this->obtenirMotsClesTexte($obs['id_observation'], $obs['id_utilisateur'], 'obs');
 			if (is_array($mots_cles_texte) && count($mots_cles_texte) > 0) {
 				$mots_cles_texte_chaine = implode(',', $mots_cles_texte);
-				$mise_a_jour_index = $this->executerRequeteMiseAJourMotCleTexte($mots_cles_texte_chaine,$obs['id_observation'], $obs['ce_utilisateur'], 'obs');
+				$mise_a_jour_index = $this->executerRequeteMiseAJourMotCleTexte($mots_cles_texte_chaine, $obs['id_observation'], $obs['id_utilisateur'], 'obs');
 				$this->afficherAvancement('Génération des index mots clés obs (par 1)');
+			}
+		}
 		echo "\n-----------------------------------------------------------------OK\n";
 	}
-	private function genererIndexTexteMotsClesImages() {
-		$requete_selection_images = 'SELECT DISTINCT id_image, ce_utilisateur '.
+	private function genererIndexTexteMotsClesImages() {
-			'FROM '.self::$bdd_cel_migration.'.cel_images '.
+		$requete = 'SELECT DISTINCT id_image, id_utilisateur '.
-			'WHERE id_image IN (SELECT DISTINCT id_image FROM '.self::$bdd_cel_migration.'.cel_mots_cles_images) ';
+			'FROM '.self::$bdd_cel_migration.'.cel_images_mots_cles ';
-		$images_a_mots_cles = $this->requeter($requete_selection_images);
+		$images_a_mots_cles = $this->requeter($requete);
 		foreach ($images_a_mots_cles as $image) {
-			$mots_cles_texte = $this->obtenirMotsClesTexte($image['id_image'], $image['ce_utilisateur'], 'images');
+			$mots_cles_texte = $this->obtenirMotsClesTexte($image['id_image'], $image['id_utilisateur'], 'images');
 			$mots_cles_texte_chaine = '';
 			if (is_array($mots_cles_texte) && count($mots_cles_texte) > 0) {
 				$mots_cles_texte_chaine = implode(',', $mots_cles_texte);
-				$mise_a_jour_index = $this->executerRequeteMiseAJourMotCleTexte($mots_cles_texte_chaine,$image['id_image'], $image['ce_utilisateur'], 'images');
+				$mise_a_jour_index = $this->executerRequeteMiseAJourMotCleTexte($mots_cles_texte_chaine, $image['id_image'], $image['id_utilisateur'], 'images');
 				$this->afficherAvancement('Génération des index mots clés images (par 1)');
 			}
 		}
 		echo "\n-----------------------------------------------------------------OK\n";
 	}
-	private function executerRequeteMiseAJourMotCleTexte($mots_cles_texte_chaine, $id_image_ou_obs, $id_utilisateur, $mode_image_ou_obs) {
-		$requete = 	'UPDATE '.self::$bdd_cel_migration.'.'.(($mode_image_ou_obs == 'obs') ? 'cel_obs' : 'cel_images').' '.
-			'SET mots_cles_texte = '.$this->proteger($mots_cles_texte_chaine).' '.
-			'WHERE '.(($mode_image_ou_obs == 'obs') ? 'id_observation' : 'id_image').' = '.$this->proteger($id_image_ou_obs).' '.
-			'	AND ce_utilisateur = '.$this->proteger($id_utilisateur);
+	private function obtenirMotsClesTexte($id_image_ou_obs, $id_utilisateur, $mode) {
-		return $this->executer($requete);
+		$bdd = self::$bdd_cel_migration;
-	}
+		$champ_id_mot_cle = ($mode == 'obs') ? 'id_mot_cle_obs' : 'id_mot_cle_image';
+		$champ_id_obs_ou_img = ($mode == 'obs') ? 'id_observation' : 'id_image';
-	private function obtenirMotsClesTexte($id_image_ou_obs, $id_utilisateur, $mode_image_ou_obs) {
-		$champ_id = ($mode_image_ou_obs == 'obs') ? 'id_mot_cle_obs' : 'id_mot_cle_image';
+		$id_image_ou_obs = $this->proteger($id_image_ou_obs);
-		$requete = 'SELECT mot_cle '.
+		$id_utilisateur = $this->proteger($id_utilisateur);
-			'FROM '.self::$bdd_cel_migration.'.cel_mots_cles_'.$mode_image_ou_obs.' '.
-			"WHERE $champ_id IN ".
-				'('.
+		$requete = 'SELECT mot_cle '.
-					"SELECT $champ_id ".
+				"FROM $bdd.cel_mots_cles_$mode AS a ".
-					'FROM '.self::$bdd_cel_migration.'.cel_'.$mode_image_ou_obs.'_mots_cles '.
+				"	INNER JOIN $bdd.cel_{$mode}_mots_cles AS b ".
-					'WHERE '.(($mode_image_ou_obs == 'obs') ? 'id_observation' : 'id_image').' = '.$this->proteger($id_image_ou_obs).
+				"	ON (a.$champ_id_mot_cle = b.$champ_id_mot_cle AND a.id_utilisateur = b.id_utilisateur) ".
-				')'.
+				"WHERE b.$champ_id_obs_ou_img = $id_image_ou_obs ".
-			' AND id_utilisateur = '.$this->proteger($id_utilisateur);
+				"	AND a.id_utilisateur = $id_utilisateur ";
 		$resultats = $this->requeter($requete);
+		$mots_cles = array();
+		foreach ($resultats as $resultat) {
+			$mots_cles[] = $resultat['mot_cle'];
+		}
+		return $mots_cles;
+	}
+	private function executerRequeteMiseAJourMotCleTexte($mots_cles_texte_chaine, $id_image_ou_obs, $id_utilisateur, $mode_image_ou_obs) {
+		$bdd = self::$bdd_cel_migration;
+		$table = ($mode_image_ou_obs == 'obs') ? 'cel_obs' : 'cel_images';
+		$chp_id_obs_ou_img = ($mode_image_ou_obs == 'obs') ? 'id_observation' : 'id_image';
+		$id_image_ou_obs = $this->proteger($id_image_ou_obs);
+		$mots_cles_texte = $this->proteger($mots_cles_texte_chaine);
+		$ce_utilisateur = $this->proteger($id_utilisateur);
-		$mots_cles = array();
+		$requete = 	"UPDATE $bdd.$table ".

Subversion Repositories eFlore/Applications.cel

(root)/trunk/jrest/scripts/MigrationMotsCles.php @ 1199 – Rev 1198 → 1199