Subversion Repositories eFlore/Applications.cel

Compare Revisions

Ignore whitespace Rev 1198 → Rev 1199

/trunk/jrest/scripts/MigrationMotsCles.php
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);
}
}