Subversion Repositories Applications.annuaire

Rev

Rev 614 | Rev 619 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
610 delphine 1 
<?php
2 
// Encodage : UTF-8
3 
// +-------------------------------------------------------------------------------------------------------------------+
4 
/**
5 
* Migration des utilisateurs vers wordpress
6 
*
7 
* Description : classe permettant de migrer les profils de l'annuaire vers les profils wordpress
8 
* Utilisation : php cli.php migrationwp -a tous
614 delphine 9 
* /usr/local/bin/php -d memory_limit=4000M cli.php migrationwp -a tous
612 delphine 10 
* vérifier le nom de la base et le préfixe des tables définis dans $basewp
610 delphine 11 
*
12 
//Auteur original :
13 
* @author       Aurélien PERONNET <jpm@tela-botanica.org>
14 
* @copyright	Tela-Botanica 1999-2014
15 
* @licence		GPL v3 & CeCILL v2
16 
* @version		$Id$
17 
*/
18 
 
19 
class Migrationwp extends Script {
614 delphine 20 
	private $basewp = "wordpress.site_";
21 
	private $table = "site_";
618 delphine 22 
	private $doc_loc = array();
610 delphine 23 
 
24 
	public function executer() {
25 
		$this->bdd = new Bdd();
26 
		//$this->bdd->setAttribute(MYSQL_ATTR_USE_BUFFERED_QUERY, true);
27 
		// évite les erreurs 2006 "MySQL has gone away"
28 
		$this->bdd->executer("SET wait_timeout=300");
29 
 
30 
		$cmd = $this->getParametre('a');
31 
		$this->mode_verbeux = $this->getParametre('v');
32 
 
33 
		$retour = array();
34 
 
35 
		switch($cmd) {
36 
			case "tous":
37 
				$retour = $this->migrerUtilisateur();
38 
				$retour = $this->migrerUtilisateurMeta();
614 delphine 39 
				$retour = $this->migrerUtilisateurProfil();
610 delphine 40 
				$retour = $this->migrerUtilisateurActivite();
41 
				break;
42 
			case "utilisateur": //liste wordpress
43 
				$retour = $this->migrerUtilisateur();
44 
				break;
45 
			case "meta": //role
46 
				$retour = $this->migrerUtilisateurMeta();
47 
				break;
48 
			case "profil":
49 
				$retour = $this->migrerUtilisateurProfil();
50 
				break;
51 
			case "activite": // obligatoire pour affichage
52 
				$retour = $this->migrerUtilisateurActivite();
53 
				break;
618 delphine 54 
			case "actualiteTout" :
55 
				$retour = $this->migrerActualites();
56 
				$retour .= $this->migrerActualitesRubrique();
57 
				$retour .= $this->migrerActualitesCommentaires();
58 
				break;
614 delphine 59 
			case "actualite" :
618 delphine 60 
				$retour = $this->migrerActualites();
614 delphine 61 
				break;
62 
			case "actualiteRubrique" :
618 delphine 63 
				$retour = $this->migrerActualitesRubrique();
614 delphine 64 
				break;
65 
			case "actualiteComm" :
618 delphine 66 
				$retour = $this->migrerActualitesCommentaires();
614 delphine 67 
				break;
68 
			default: break;
610 delphine 69 
		}
70 
 
71 
		if($this->mode_verbeux) {
72 
			// echo pour que bash capte la sortie et stocke dans le log
73 
			//echo 'Identifiants des mails traites : '.implode(',', $retour)."--";
74 
		}
75 
	}
76 
 
77 
	private function migrerUtilisateur() {
614 delphine 78 
		$requete = "INSERT INTO ".$this->basewp."users
610 delphine 79 
				(`ID`, `user_login`, `user_pass`, `user_nicename`, `user_email`, `user_url`, `user_registered`, `user_status`, `display_name`)
614 delphine 80 
				SELECT `U_ID`, `U_MAIL`, `U_PASSWD`,concat(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(lower(concat(`U_SURNAME`,'-',`U_NAME`,'-')),' ',''),'\'',''),'é','e'),'è','e'),'ï','i'),'ü','u'),'ø',''),'œ','oe'),'ë','e'),'ç','c'),cast(`U_ID` as char)),
81 
				`U_MAIL` as mail,  '' as user_url, `U_DATE`, '0', concat(`U_SURNAME`,' ',`U_NAME`)  FROM tela_prod_v4.`annuaire_tela`";
610 delphine 82 
		$retour = $this->bdd->executer($requete);
83 
		echo 'Il y a '.count($retour).' utilisateurs migrés '."--";
84 
		return $retour;
85 
	}
86 
 
87 
 
614 delphine 88 
	//TODO encoder nick/first/last name pour '
610 delphine 89 
	private function migrerUtilisateurMeta() {
90 
		$retour = array();
614 delphine 91 
		$requete = "SELECT `U_ID`, `U_NAME`, `U_SURNAME` FROM `annuaire_tela`;";
610 delphine 92 
		$utilisateurs = $this->bdd->recupererTous($requete);
93 
		foreach ($utilisateurs as $utilisateur) {
614 delphine 94 
			// _access est pour définir les catégories d'articles que l'utilisateur pourra écrire
95 
			$requete_insert = "INSERT INTO ".$this->basewp."usermeta (`user_id`, `meta_key`, `meta_value`) VALUES
610 delphine 96 
					({$utilisateur['U_ID']}, 'last_activity', '2016-05-18 15:38:18'),
614 delphine 97 
					({$utilisateur['U_ID']}, 'first_name', {$this->bdd->proteger($utilisateur['U_SURNAME'])}),
98 
					({$utilisateur['U_ID']}, 'last_name', {$this->bdd->proteger($utilisateur['U_NAME'])}),
610 delphine 99 
					({$utilisateur['U_ID']}, 'description', ''),
100 
					({$utilisateur['U_ID']}, 'rich_editing', 'true'),
101 
					({$utilisateur['U_ID']}, 'comment_shortcuts', 'false'),
102 
					({$utilisateur['U_ID']}, 'admin_color', 'fresh'),
103 
					({$utilisateur['U_ID']}, 'use_ssl', '0'),
104 
					({$utilisateur['U_ID']}, 'show_admin_bar_front', 'true'),
614 delphine 105 
					({$utilisateur['U_ID']}, '".$this->basewp."capabilities', 'a:1:{s:11:\"contributor\";b:1;}'),
106 
					({$utilisateur['U_ID']}, '".$this->basewp."user_level', '1'),
610 delphine 107 
					({$utilisateur['U_ID']}, 'dismissed_wp_pointers', ''),
108 
					({$utilisateur['U_ID']}, 'wp_dashboard_quick_press_last_post_id', '63'),
109 
					({$utilisateur['U_ID']}, '_restrict_media', '1'),
614 delphine 110 
					({$utilisateur['U_ID']}, '_access', 'a:4:{i:0;s:1:\"2\";i:1;s:1:\"5\";i:2;s:1:\"6\";i:3;s:1:\"7\";}'),
111 
					({$utilisateur['U_ID']}, 'bp_xprofile_visibility_levels', 'a:12:{i:1;s:6:\"public\";i:60;s:6:\'public\';i:61;s:6:\'public\';i:49;s:6:\'public\';i:55;s:6:\'public\';i:48;s:6:\'public\';i:62;s:6:\'public\';i:63;s:6:\'public\';i:68;s:6:\'public\';i:76;s:6:\'public\';i:120;s:6:\'public\';i:81;s:6:\'public\';}');";
610 delphine 112 
			$retour[] = $this->bdd->executer($requete_insert);
113 
		}
114 
		// echo pour que bash capte la sortie et stocke dans le log
115 
		//echo 'Il y a '.count($utilisateurs).' utilisateurs '."--";
116 
		//print_r($utilisateurs);
117 
		return $retour;
118 
	}
119 
 
120 
	private function migrerUtilisateurActivite() {
121 
		$retour = array();
122 
		$requete = "SELECT `U_ID`, `U_NAME`, `U_SURNAME` FROM `annuaire_tela`;";
123 
		$utilisateurs = $this->bdd->recupererTous($requete);
124 
		foreach ($utilisateurs as $utilisateur) {
614 delphine 125 
			$requete_insert = "INSERT INTO ".$this->basewp."bp_activity
610 delphine 126 
					(`id`, `user_id`, `component`, `type`, `action`, `content`, `primary_link`, `item_id`, `secondary_item_id`, `date_recorded`, `hide_sitewide`, `mptt_left`, `mptt_right`, `is_spam`)
127 
					VALUES (NULL, {$utilisateur['U_ID']}, 'members', 'last_activity', '', '', '', '0', NULL, '2016-05-19 15:06:16', '0', '0', '0', '0');";
128 
			$retour[] = $this->bdd->executer($requete_insert);
129 
		}
130 
		// echo pour que bash capte la sortie et stocke dans le log
131 
		//echo 'Il y a '.count($utilisateurs).' utilisateurs '."--";
132 
		//print_r($utilisateurs);
133 
		return $retour;
134 
	}
135 
 
136 
	private function migrerUtilisateurProfil() {
614 delphine 137 
		$retour = array();
138 
		$requete = "SELECT `U_ID`, `U_NAME`, `U_SURNAME`, U_WEB, `U_CITY`, `U_COUNTRY`, pays, `U_NIV`,  `LABEL_NIV` FROM `annuaire_tela`
139 
				left join (select  `amo_nom` as pays,  `amo_abreviation` FROM `annu_meta_ontologie` WHERE  `amo_ce_parent` = 1074) liste_pays  on `amo_abreviation` = `U_COUNTRY`
140 
				LEFT JOIN `annuaire_LABEL_NIV` ON `ID_LABEL_NIV` = `U_NIV`;";
141 
		$utilisateurs = $this->bdd->recupererTous($requete);
142 
		$requete_supp = "SELECT *  FROM `annu_meta_valeurs` WHERE `amv_ce_colonne` in (2,137, 99, 125) and (amv_valeur != ''  and amv_valeur != 0)";
143 
		$infos_supp = $this->bdd->recupererTous($requete_supp);
144 
		$codes_langues = array("30842"=>"Anglais",
145 
				"30843"=>"Allemand",
146 
				"30844"=>"Italien",
147 
				"30845"=>"Espagnol",
148 
				"30846"=>"Arabe",
149 
				"30847"=>"Chinois",
150 
				"30848"=>"Russe");
151 
		foreach ($infos_supp as $infos) {
152 
			if ($infos['amv_ce_colonne'] == 2) {
153 
				//exemple a:3:{i:0;s:7:"Anglais";i:1;s:8:"Espagnol";i:2;s:7:"Italien";}
154 
				$langues = explode(";;", $infos['amv_valeur']);
155 
				$valeur = "a:".count($langues).':{';
156 
				foreach ($langues as $n=>$langue) {
157 
					$valeur .= 'i:'.$n.';s:'.strlen($codes_langues[$langue]).':"'.$codes_langues[$langue].'";';
158 
				}
159 
				$valeur .='}';
160 
				$supp[$infos['amv_cle_ligne']][$infos['amv_ce_colonne']] = $valeur;
161 
			} else {
162 
				$supp[$infos['amv_cle_ligne']][$infos['amv_ce_colonne']] = $infos['amv_valeur'];
163 
			}
164 
		}
165 
 
166 
		$correspondance_categories = array("99"=>"1",
167 
				"137"=>"2",
168 
				"125"=>"11",
169 
				"2"=>"13");
170 
		foreach ($utilisateurs as $utilisateur) {
171 
			$requete_insert = "INSERT INTO ".$this->basewp."bp_xprofile_data (`field_id`, `user_id`, `value`, `last_updated`) VALUES
172 
				('3', {$utilisateur['U_ID']}, {$this->bdd->proteger($utilisateur['pays'])}, '2016-05-19 15:06:16'),
173 
				('4', {$utilisateur['U_ID']}, {$this->bdd->proteger($utilisateur['U_CITY'])}, '2016-05-19 15:06:16'),
174 
				('9', {$utilisateur['U_ID']}, {$this->bdd->proteger($utilisateur['U_NAME'])}, '2016-05-19 15:06:16'),
175 
				('10', {$utilisateur['U_ID']}, {$this->bdd->proteger($utilisateur['U_SURNAME'])}, '2016-05-19 15:06:16'),
176 
				('12', {$utilisateur['U_ID']}, {$this->bdd->proteger($utilisateur['LABEL_NIV'])}, '2016-05-19 15:06:16'),
177 
				('21', {$utilisateur['U_ID']}, {$this->bdd->proteger($utilisateur['U_WEB'])}, '2016-05-19 15:06:16')";
178 
			if (isset($supp[$utilisateur['U_ID']])) {
179 
				foreach ($supp[$utilisateur['U_ID']] as $num=>$val){
180 
					$requete_insert .= ",({$correspondance_categories[$num]}, {$utilisateur['U_ID']}, {$this->bdd->proteger($val)}, '2016-05-19 15:06:16')";
181 
				}
182 
			}
183 
			$requete_insert .= ";";
184 
			$retour[] = $this->bdd->executer($requete_insert);
185 
		}
186 
		return $retour;
610 delphine 187 
	}
188 
 
618 delphine 189 
	private function migrerEvenements() {
190 
		$retour = array();
191 
		/*
192 
		 * */
193 
		$requete = "";
194 
		$articles = $this->bdd->recupererTous($requete);
195 
 
196 
 
197 
		$requete_insert = "INSERT INTO ".$this->basewp."posts VALUES ".implode($insert, ', ').";";
198 
		$retour[] = $this->bdd->executer($requete_insert);
199 
		echo 'Il y a '.count($retour).' actualités migrées '."--";
200 
		return $retour;
201 
	}
202 
 
203 
	private function migrerEvenementsRubrique() {
204 
		$requete = "INSERT INTO ".$this->basewp."term_relationships (`object_id`, `term_taxonomy_id`)
205 
			SELECT `bf_id_fiche` +10000, replace( replace( replace( replace( `bf_ce_nature` , '3', '39' ) , '4', '38' ) , '2', '37' ) , '1', '40' )
206 
				FROM `bazar_fiche`
207 
				WHERE year( `bf_date_debut_validite_fiche` ) =2016
208 
				UNION SELECT `bf_id_fiche` +10000, 36 AS cat_evnt
209 
				FROM `bazar_fiche`
210 
				WHERE year( `bf_date_debut_validite_fiche` ) =2016";
211 
		$retour = $this->bdd->executer($requete);
212 
		echo 'Il y a '.count($retour).' actualités migrées '."--";
213 
		return $retour;
214 
	}
215 
 
614 delphine 216 
	private function migrerActualites() {
217 
		$retour = array();
218 
		/*INSERT INTO `wp4_posts`
219 
		 SELECT spip_articles.`id_article` as ID, `id_auteur` as post_author, `date` as post_date, `date` as post_date_gmt,
220 
		 replace(replace(replace(replace(replace(replace(replace(replace(replace(convert( convert( texte USING latin1 ) USING utf8 ),'{{{{',''), '}}}}', '<!--more-->'), '{{{','<h2>'), '}}}', '</h2>'), '{{', '<strong>'), '}}', '</strong>'), '{', '<em>'), '}', '</em>'), '_ ', '') as post_content,
221 
		 `titre` as post_title,  "" as post_excerpt, replace(replace(replace(replace(replace(`statut`,'poubelle', 'trash'),'publie', 'publish'), 'prepa', 'private'), 'prop', 'pending'), 'refuse', 'trash') as post_status,  "open" as comment_status, "open" as ping_status, "" as post_password, spip_articles.`id_article` as post_name, "" as to_ping, "" as pinged, `date_modif` as post_modified,`date_modif` as post_modified_gmt, "" as post_content_filtered, "" as post_parent,
222 
		 concat("http://tela-botanica.net/wpsite/actu",spip_articles.`id_article`) as guid, "0" as menu_order, "post" as post_type, "" as post_mime_type, "" as comment_count FROM tela_prod_spip_actu.`spip_articles` left join tela_prod_spip_actu.spip_auteurs_articles on spip_auteurs_articles.`id_article` =  spip_articles.`id_article` WHERE id_rubrique in (22,54,70,30,19,51)
223 
		*/
224 
		$requete = "SELECT spip_articles.`id_article` as ID, `id_auteur` as post_author, `date` as post_date, `date` as post_date_gmt,
225 
			replace(replace(replace(replace(replace(replace(replace(replace(replace(convert( convert( texte USING latin1 ) USING utf8 ),'{{{{',''), '}}}}', '<!--more-->'), '{{{','<h2>'), '}}}', '</h2>'), '{{', '<strong>'), '}}', '</strong>'), '{', '<em>'), '}', '</em>'), '_ ', '') as post_content,
226 
			`titre` as post_title,  \"\" as post_excerpt, replace(replace(replace(replace(replace(`statut`,'poubelle', 'trash'),'publie', 'publish'), 'prepa', 'private'), 'prop', 'pending'), 'refuse', 'trash') as post_status,  \"open\" as comment_status, \"open\" as ping_status, \"\" as post_password, spip_articles.`id_article` as post_name, \"\" as to_ping, \"\" as pinged, `date_modif` as post_modified,`date_modif` as post_modified_gmt, \"\" as post_content_filtered, \"\" as post_parent,
227 
			concat(\"http://tela-botanica.net/wpsite/actu\",spip_articles.`id_article`) as guid, \"0\" as menu_order, \"post\" as post_type, \"\" as post_mime_type, \"\" as comment_count FROM tela_prod_spip_actu.`spip_articles` left join tela_prod_spip_actu.spip_auteurs_articles on spip_auteurs_articles.`id_article` =  spip_articles.`id_article` WHERE id_rubrique in (22,54,70,30,19,51)";
228 
		$articles = $this->bdd->recupererTous($requete);
618 delphine 229 
		$requete_doc = "SELECT d.`id_document`, `fichier`, `id_article` FROM tela_prod_spip_actu.`spip_documents` d  left join tela_prod_spip_actu.spip_documents_articles da on da.`id_document` = d.`id_document`";
614 delphine 230 
		$documents = $this->bdd->recupererTous($requete_doc);
231 
		foreach ($documents as $doc) {
618 delphine 232 
			$this->doc_loc[$doc['id_document']] = $doc['fichier'];
233 
		}$i=0;
614 delphine 234 
		foreach ($articles as $article) {
618 delphine 235 
 
236 
			$article['post_content'] = preg_replace("/\[([^\[]*)\-\>([^\[]*)\]/", '<a href="\2" target="_blank">\1</a>', $article['post_content']);
614 delphine 237 
			//$images = preg_grep("\<img([0-9]*)\|[a-z]*\>", $article['post_content']);
618 delphine 238 
			$article['post_content'] = preg_replace_callback("/\<img([0-9]*)\|[a-z]*\>/", array($this, transformerNumEnUrl), $article['post_content']);
239 
			$insert[] = "(".implode($this->bdd->proteger($article), ', ').")"; $i++;
240 
			if ($i >50) {
241 
				$requete_insert = "INSERT INTO ".$this->basewp."posts VALUES ".implode($insert, ', ').";";
242 
				$retour[] = $this->bdd->executer($requete_insert);
243 
				$insert = array(); $i = 0;
244 
			}
614 delphine 245 
		}
618 delphine 246 
		$requete_insert = "INSERT INTO ".$this->basewp."posts VALUES ".implode($insert, ', ').";";
614 delphine 247 
		$retour[] = $this->bdd->executer($requete_insert);
248 
		echo 'Il y a '.count($retour).' actualités migrées '."--";
249 
		return $retour;
250 
	}
251 
 
618 delphine 252 
	private function transformerNumEnUrl($matches) {
253 
		$remplace = "";
254 
		if (isset($matches[1])) {
255 
			$remplace = '<img src="http://www.tela-botanica.org/actu/'.$this->doc_loc[$matches[1]].'" \/\>';
256 
		}
257 
		return $remplace;
258 
	}
259 
 
614 delphine 260 
	private function migrerActualitesRubrique() {
618 delphine 261 
		$requete = "INSERT INTO ".$this->basewp."term_relationships (`object_id`, `term_taxonomy_id`)
614 delphine 262 
			SELECT `id_article`, replace(replace(replace(replace(replace(replace(`id_rubrique`, 22, 20), 54, 21), 30, 23), 19, 24), 51, 25), 70, 22)
618 delphine 263 
			FROM tela_prod_spip_actu.`spip_articles` WHERE id_rubrique in (22,54,70,30,19,51)";
614 delphine 264 
		$retour = $this->bdd->executer($requete);
265 
		echo 'Il y a '.count($retour).' actualités migrées '."--";
266 
		return $retour;
267 
	}
268 
 
269 
	private function migrerActualitesCommentaires() {
618 delphine 270 
		$requete = "INSERT INTO ".$this->basewp.'comments (`comment_ID`, `comment_post_ID`, `comment_author`, `comment_author_email`, `comment_author_url`, `comment_author_IP`, `comment_date`, `comment_date_gmt`, `comment_content`, `comment_karma`, `comment_approved`, `comment_agent`, `comment_type`, `comment_parent`, `user_id`)
614 delphine 271 
			SELECT `id_forum` , `id_article` , `auteur` , `email_auteur` , "" AS url, `ip` , `date_heure` , `date_heure` AS gmt, `texte` , "0" AS karma, replace(`statut`, "publie", "1") , "" AS agent, "" AS
272 
			TYPE , `id_parent` , `id_auteur`
273 
			FROM tela_prod_spip_actu.`spip_forum`
274 
			WHERE id_article in (SELECT `id_article` FROM tela_prod_spip_actu.`spip_articles` WHERE id_rubrique in (22,54,70,30,19,51))';
275 
 
276 
		$retour = $this->bdd->executer($requete);
277 
		echo 'Il y a '.count($retour).' actualités migrées '."--";
278 
		return $retour;
279 
	}
280 
 
281 
	private function migrerActualitesLogo() {
282 
		$requete = "INSERT INTO ".$this->basewp."term_relationships`(`object_id`, `term_taxonomy_id`)
283 
			SELECT `id_article`, replace(replace(replace(replace(replace(replace(`id_rubrique`, 22, 20), 54, 21), 30, 23), 19, 24), 51, 25), 70, 22)
284 
			FROM `spip_articles` WHERE id_rubrique in (22,54,70,30,19,51)";
285 
 
286 
		$retour = $this->bdd->executer($requete);
287 
		echo 'Il y a '.count($retour).' actualités migrées '."--";
288 
		return $retour;
289 
	}
290 
 
610 delphine 291 
}
614 delphine 292 
?>