48,35 → 48,35 |
|
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 " ETAPE 1. Paramétrage ... \n\n"; |
echo "-------------------------------------------------------------------\n\n"; |
echo "-------------------------------------------------------------------\n"; |
echo " ETAPE 1. Paramétrage ... \n"; |
echo "-------------------------------------------------------------------\n"; |
$this->getUtilisateurs(); |
echo "\n"."\n"."\n"; |
echo "-------------------------------------------------------------------\n\n"; |
echo " ETAPE 2. Migration des mots clés ... \n\n"; |
echo "-------------------------------------------------------------------\n\n"; |
echo "-------------------------------------------------------------------\n"; |
echo " ETAPE 2. Migration des mots clés ... \n"; |
echo "-------------------------------------------------------------------\n"; |
$this->migrerTableMotsClesObs(); |
echo "\n---------------------------------------------------------------- OK\n\n"; |
echo "\n---------------------------------------------------------------- OK\n"; |
$this->migrerTableMotsClesImages(); |
echo "\n"."\n"."\n"; |
echo "-------------------------------------------------------------------\n\n"; |
echo " ETAPE 3. Migration des liaisons mots clés ... \n\n"; |
echo "-------------------------------------------------------------------\n\n"; |
echo "-------------------------------------------------------------------\n"; |
echo " ETAPE 3. Migration des liaisons mots clés ... \n"; |
echo "-------------------------------------------------------------------\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"; |
echo "-------------------------------------------------------------------\n"; |
echo " ETAPE 4. Génération des index des mots clés ... \n"; |
echo "-------------------------------------------------------------------\n"; |
$this->genererIndexTexteMotsClesObs(); |
$this->genererIndexTexteMotsClesImages(); |
echo "\n"."\n"."\n"; |
146,8 → 146,8 |
$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) ". |
'VALUES '; |
"($champ_id, id_utilisateur, mot_cle, md5, bg, bd, niveau, $champ_parent) ". |
'VALUES '; |
|
$sous_requete = array(); |
foreach ($arbres_mots_cles as $arbre_mot_cle) { |
236,7 → 236,9 |
private function migrerLiaisonsMotsClesObs() { |
$requete = 'SELECT mots_cles AS mots_cles, id AS id, identifiant AS id_utilisateur '. |
'FROM cel_inventory '. |
'WHERE mots_cles != "" '. |
'WHERE mots_cles != "" '. |
' AND mots_cles != "NULL" '. |
' AND mots_cles != "null" '. |
' AND mots_cles IS NOT NULL '. |
'ORDER BY identifiant '; |
$mots_cles_obs = $this->executerRequete($requete); |
257,7 → 259,9 |
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 != "" '. |
'WHERE ci_meta_mots_cles != "" '. |
' AND ci_meta_mots_cles != "NULL" '. |
' 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); |
292,6 → 296,7 |
} |
|
private function parserMotsCles($mot_cles, $separateur = ',') { |
$mot_cles = trim($mot_cles, $separateur); |
$tableau_mots_cles = explode($separateur, $mot_cles); |
|
$tableau_mots_cles_formates = array(); |
298,6 → 303,7 |
foreach ($tableau_mots_cles as $mot_cle) { |
$mot_cle = str_replace($separateur.$separateur, '', $mot_cle); |
$mot_cle = str_replace('null', '', $mot_cle); |
$mot_cle = trim($mot_cle); |
|
if ($this->estUnIdentifiantMotCle($mot_cle)) { |
// certains mots clés mal formatés contiennent des virgules |
314,6 → 320,8 |
// 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; |
320,20 → 328,19 |
} |
|
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 '. |
'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); |
$requete = 'SELECT DISTINCT id_observation, id_utilisateur '. |
'FROM '.self::$bdd_cel_migration.'.cel_obs_mots_cles '; |
$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)'); |
} |
} |
341,17 → 348,16 |
} |
|
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); |
$requete = 'SELECT DISTINCT id_image, id_utilisateur '. |
'FROM '.self::$bdd_cel_migration.'.cel_images_mots_cles '; |
$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)'); |
} |
} |
358,27 → 364,19 |
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) { |
$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'; |
$id_image_ou_obs = $this->proteger($id_image_ou_obs); |
$id_utilisateur = $this->proteger($id_utilisateur); |
|
return $this->executer($requete); |
} |
|
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'; |
|
$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 id_utilisateur = '.$this->proteger($id_utilisateur); |
$requete = 'SELECT mot_cle '. |
"FROM $bdd.cel_mots_cles_$mode AS a ". |
" INNER JOIN $bdd.cel_{$mode}_mots_cles AS b ". |
" 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 a.id_utilisateur = $id_utilisateur "; |
$resultats = $this->requeter($requete); |
|
$mots_cles = array(); |
388,4 → 386,20 |
|
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); |
|
$requete = "UPDATE $bdd.$table ". |
"SET mots_cles_texte = $mots_cles_texte ". |
"WHERE $chp_id_obs_ou_img = $id_image_ou_obs ". |
" AND ce_utilisateur = $id_utilisateur "; |
|
return $this->executer($requete); |
} |
} |