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

 <?php
-class MigrationMotsCles extends Cel {
-	const bdd_cel_migration = 'cel';
+class MigrationMotsCles extends Cel {
+	public static $bdd_cel_migration;
+	public static $bdd_utilisateurs;
+	const SEPARATEUR_MOT_CLE_TEXTE = ',';
-	const bdd_utilisateurs = 'cel_old';
+	const truncate = true; //Doit on vider les tables de destination ?
-	const dry_run = true;
+	const dry_run = false;
 	/**
 	 * Méthode appelée avec une requête de type GET.
 	 */
 	public function getElement($params) {
+		if(!isset($this->config['database_cel']['database_migration']) || $this->config['database_cel']['database_migration'] == '') {
+			echo 'Attention la variable de configuration database_migration dans la section database_cel, contenant la base de données d\'arrivée, doit être remplie '."\n";
+			exit;
+		}
+		if(!isset($this->config['database_ident']['database']) || $this->config['database_ident']['database'] == '') {
+			echo 'Attention la variable de configuration database dans la section database_ident, contenant la base de données utilisateurs, doit être remplie '."\n";
+			exit;
+		}
+		self::$bdd_cel_migration = $this->config['database_cel']['database_migration'];
+		self::$bdd_utilisateurs = $this->config['database_ident']['database'];
+		echo "--MIGRATION DES MOTS CLES --------------------------------------\n";
+		//1. TEMPORAIRE : vider les tables de destinations
+		if (self::truncate) {
+			echo "-------------------------------------------------------------------\n\n";
+			echo "  ETAPE 0. Vider les tables ... \n\n";
+			echo "-------------------------------------------------------------------\n\n";
+			$nouvellesTables = array('`cel_mots_cles_obs`','`cel_mots_cles_images`', '`cel_images_mots_cles`', '`cel_obs_mots_cles`');
+			foreach ($nouvellesTables as $nomTable) {
+				echo 'Vider la table '.$nomTable.'...';
+				$requeteTruncate = 'TRUNCATE TABLE '.self::$bdd_cel_migration.'.'.$nomTable;
+				$resultatTruncate = $this->executerRequete($requeteTruncate);
+				echo "ok \n";
+			}
+			echo "\n---------------------------------------------------------------- OK\n\n";
+		}
 		echo "-------------------------------------------------------------------\n\n";
 		echo "  ETAPE 1. Paramétrage ... \n\n";
-		echo "-------------------------------------------------------------------\n\n";
 		echo "-------------------------------------------------------------------\n\n";
+		$this->getUtilisateurs();
-		$this->getUtilisateurs();
+		echo "\n"."\n"."\n";
 		echo "-------------------------------------------------------------------\n\n";
 		echo "  ETAPE 2. Migration des mots clés ... \n\n";
-		echo "-------------------------------------------------------------------\n\n";
 		echo "-------------------------------------------------------------------\n\n";
+		$this->migrerTableMotsClesObs();
-		$this->migrerTableMotsClesObs();
+		echo "\n---------------------------------------------------------------- OK\n\n";
+		$this->migrerTableMotsClesImages();
+		echo "\n"."\n"."\n";
-		$this->migrerTableMotsClesImages();
+		echo "-------------------------------------------------------------------\n\n";
+		echo "  ETAPE 3. Migration des liaisons mots clés ... \n\n";
-		echo "\n"."\n".'|| Migration des tables de liaison des mots clés ||'."\n";
+		echo "-------------------------------------------------------------------\n\n";
 		$this->migrerLiaisonsMotsClesObs();
 		$this->migrerLiaisonsMotsClesImages();
+		echo "\n"."\n"."\n";
+		echo "-------------------------------------------------------------------\n\n";
+		echo "  ETAPE 4. Génération des index des mots clés ... \n\n";
+		echo "-------------------------------------------------------------------\n\n";
+		$this->genererIndexTexteMotsClesObs();
+		$this->genererIndexTexteMotsClesImages();
 		echo "\n"."\n"."\n";
+	}
 	public function executerRequeteSimple($requete) {
 	private function getUtilisateurs() {
 		echo "\n-------------------------------------------------------------------\n";
 		echo "--SELECTION DES UTILISATEURS---------------------------------------\n\n";
-		$requete_selection_utilisateurs = 'SELECT U_ID as id, U_MAIL as mail, U_NAME as nom, U_SURNAME as prenom, U_PASSWD as pass FROM '.self::bdd_utilisateurs.'.annuaire_tela';
+		$requete_selection_utilisateurs = 'SELECT U_ID as id, U_MAIL as mail, U_NAME as nom, U_SURNAME as prenom, U_PASSWD as pass FROM '.self::$bdd_utilisateurs.'.annuaire_tela';
 		$tableau_utilisateurs = $this->executerRequete($requete_selection_utilisateurs);
 		$mots_cles_obs = $this->executerRequete($requete_selection_mots_cles);
 		$mots_cles_tableau = array();
-		$requete_insertion_mots_cles = 'INSERT INTO '.self::bdd_cel_migration.'.cel_obs_mots_cles '.
+		$requete_insertion_mots_cles = 'INSERT INTO '.self::$bdd_cel_migration.'.cel_obs_mots_cles '.
-										'(id_observation, id_mot_cle_utilisateur) '.
+										'(id_observation, id_mot_cle_obs) '.
 										'VALUES ';
 		$sous_requete_insertion_valeurs = $sous_requete_insertion_mots_cles = $this->construireSousRequeteInsertionLiaisons($mots_cles_obs, 'mots_cles','id',';');
 		$requete_insertion_mots_cles = $requete_insertion_mots_cles.$sous_requete_insertion_valeurs;
 		$insertion = $this->executerRequeteSimple($requete_insertion_mots_cles);
 		if(!$insertion) {
 		$mots_cles_images = $this->executerRequete($requete_selection_mots_cles);
 		$mots_cles_tableau = array();
-		$requete_insertion_mots_cles = 'INSERT INTO '.self::bdd_cel_migration.'.cel_images_mots_cles '.
+		$requete_insertion_mots_cles = 'INSERT INTO '.self::$bdd_cel_migration.'.cel_images_mots_cles '.
-										'(id_image, id_mot_cle_utilisateur) '.
+										'(id_image, id_mot_cle_image) '.
 										'VALUES ';
 		$sous_requete_insertion_mots_cles = $this->construireSousRequeteInsertionLiaisons($mots_cles_images, 'ci_meta_mots_cles','ci_id_image',',');
 		$requete_insertion_mots_cles = $requete_insertion_mots_cles.$sous_requete_insertion_mots_cles;
 		$insertion = $this->executerRequeteSimple($requete_insertion_mots_cles);
 		if(!$insertion) {
 			echo 'la migration des mots cles images a échoué ! '."\n";
 		for($i = 0; $i < $nbMotsCles; $i += $pas) {
 			$requete_selection_arbres_mots_cles = 'SELECT * FROM cel_mots_cles_'.$image_ou_obs.' ORDER BY cmc_niveau '.
 													'LIMIT '.$i.','.$pas;
 			$arbres_mots_cles = $this->executerRequete($requete_selection_arbres_mots_cles);
+			$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_insertion_nouvel_arbre = 'INSERT INTO '.self::bdd_cel_migration.'.cel_mots_cles_'.$image_ou_obs.' '.
+			$requete_insertion_nouvel_arbre = 'INSERT INTO '.self::$bdd_cel_migration.'.cel_mots_cles_'.$image_ou_obs.' '.
-												'(id_mot_cle_utilisateur, ce_utilisateur, mot_cle, md5, bg, bd, niveau, ce_mot_cle_utilisateur_parent) '.
+												'('.$champ_id.', ce_utilisateur, mot_cle, md5, bg, bd, niveau, '.$champ_parent.') '.
 												'VALUES ';
 			$sous_requete_insertion_nouvel_arbre = '';
+				}
 				$sous_requete_insertion_nouvel_arbre = rtrim($sous_requete_insertion_nouvel_arbre,',');
 				$requete_insertion_nouvel_arbre = $requete_insertion_nouvel_arbre.$sous_requete_insertion_nouvel_arbre;
-				//echo var_dump($requete_insertion_nouvel_arbre);
+				$requete_insertion_nouvel_arbre = $requete_insertion_nouvel_arbre.$sous_requete_insertion_nouvel_arbre;
 				$migration = $this->executerRequeteSimple($requete_insertion_nouvel_arbre);
 				if(!$migration) {
 					echo  'la migration des mots cles '.$image_ou_obs.' a échoué ! '."\n";
 				} else {
 					$nbTotal ++;
 					$this->afficherAvancement('Migration des mots clés (par '.$pas.') :', $nbTotal);
+				}
 			} else {
-			} else {
+				echo "\n---------------------------------------------------------------- OK\n\n";
 				return;
+			}
 		foreach($tableau_mots_cles as $mot_cle) {
 			$mot_cle = str_replace($separateur.$separateur,'',$mot_cle);
 			$mot_cle = str_replace('null','',$mot_cle);
 			if($this->estUnIdentifiantMotCle($mot_cle)) {
 			if($this->estUnIdentifiantMotCle($mot_cle)) {
 				// certains mots clés mal formatés contiennent des virgules
 				if(strpos($mot_cle,',') !== false) {
 				} 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;
+				}
+			}
+		}
 		return $tableau_mots_cles_formates;
+	}
 	private function estUnIdentifiantMotCle($chaine) {
+		return trim($chaine) != '' && preg_match('/[0-9A-Z]+\.[0-9A-Z]+/i', $chaine) ;
+	}
+	private function genererIndexTexteMotsClesObs() {
+		$requete_selection_obs = 'SELECT DISTINCT id_observation, ce_utilisateur FROM '.self::$bdd_cel_migration.'.cel_obs '.
+			                              'WHERE id_observation IN (SELECT DISTINCT id_observation FROM '.self::$bdd_cel_migration.'.cel_mots_cles_obs) ';
+		$obs_a_mots_cles = $this->requeter($requete_selection_obs);
+		$nbTotal = 0;
+		foreach($obs_a_mots_cles as $obs) {
+			$mots_cles_texte = $this->obtenirMotsClesTexte($obs['id_observation'], $obs['ce_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');
+				$nbTotal ++;
+				$this->afficherAvancement('Génération des index mots clés obs (par 1) :', $nbTotal);
+			}
+		}
+		echo "\n-----------------------------------------------------------------OK\n";
+	}
+	private function genererIndexTexteMotsClesImages() {
+		$requete_selection_images = 'SELECT DISTINCT id_image, ce_utilisateur FROM '.self::$bdd_cel_migration.'.cel_images '.
+		                              'WHERE id_image IN (SELECT DISTINCT id_image FROM '.self::$bdd_cel_migration.'.cel_mots_cles_images) ';
+		$images_a_mots_cles = $this->requeter($requete_selection_images);
+		$nbTotal = 0;
+		foreach($images_a_mots_cles as $image) {
+			$mots_cles_texte = $this->obtenirMotsClesTexte($image['id_image'], $image['ce_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');
+				$nbTotal ++;
+				$this->afficherAvancement('Génération des index  mots clés images (par 1) :', $nbTotal);
+			}
+		}
+		echo "\n-----------------------------------------------------------------OK\n";
+	}
+	private function executerRequeteMiseAJourMotCleTexte($mots_cles_texte_chaine, $id_image_ou_obs, $identifiant_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($identifiant_utilisateur);
+		return $this->executer($requete);
+	}
+	private function obtenirMotsClesTexte($id_image_ou_obs, $identifiant_utilisateur, $mode_image_ou_obs) {
+		$champ_id = ($mode_image_ou_obs == 'obs') ? 'id_mot_cle_obs' : 'id_mot_cle_image';
+		$requete = 	'SELECT mot_cle '.
+						'FROM '.self::$bdd_cel_migration.'.cel_mots_cles_'.$mode_image_ou_obs.' '.
+						'WHERE '.$champ_id.' IN '.
+							'('.
+								'SELECT '.$champ_id.' '.
+								'FROM '.self::$bdd_cel_migration.'.cel_'.$mode_image_ou_obs.'_mots_cles '.
+								'WHERE '.(($mode_image_ou_obs == 'obs') ? 'id_observation' : 'id_image').' = '.$this->proteger($id_image_ou_obs).
+							')'.
+						' AND ce_utilisateur = '.$this->proteger($identifiant_utilisateur);
+		$resultats = $this->requeter($requete);
+		$mots_cles = array();
+		foreach($resultats as $resultat) {
+			$mots_cles[] = $resultat['mot_cle'];
+		}
-		return trim($chaine) != '' && preg_match('/[0-9A-Z]+\.[0-9A-Z]+/i', $chaine) ;
+		return $mots_cles;
+	}

Subversion Repositories eFlore/Applications.cel

(root)/trunk/jrest/services/MigrationMotsCles.php @ 1161 – Rev 1149 → 1161