· 10 years ago · Aug 30, 2016, 07:34 AM
1SET character_set_client = utf8mb4;
2SET character_set_connection = utf8mb4;
3
4DELIMITER $$
5
6DROP FUNCTION IF EXISTS mvd_joomla.HTML_UnEncode$$
7CREATE DEFINER=`root`@`localhost` FUNCTION `HTML_UnEncode`(x VARCHAR(16383)) RETURNS varchar(16383) CHARSET utf8mb4
8BEGIN
9
10 DECLARE TextString VARCHAR(16383);
11
12 SET TextString = x;
13
14 IF INSTR(x, '[blockquote')
15 THEN SET TextString = REPLACE(REPLACE(TextString, '[blockquote','[quote'), '[/blockquote]','[/quote]');
16 END IF;
17 IF INSTR(x, '[link')
18 THEN SET TextString = REPLACE(REPLACE(TextString, '[link','[url'), '[/link', '[/url');
19 END IF;
20 IF INSTR(x, '[iframe')
21 THEN SET TextString = REPLACE(REPLACE(TextString, '[iframe','[video'), '[/iframe', '[/video');
22 END IF;
23 IF INSTR(x, '[img:')
24 THEN SET TextString = REPLACE(TextString, '[img:','[img ');
25 END IF;
26 IF INSTR(x, '{e_FILE}')
27 THEN SET TextString = REPLACE(TextString, '{e_FILE}','/e107_files/');
28 END IF;
29
30 IF INSTR(x, '\\'')
31 THEN SET TextString = REPLACE(TextString, '&\#092;\'','\'');
32 END IF;
33 IF INSTR(x, '$')
34 THEN SET TextString = REPLACE(TextString, '&\#036;','\$');
35 END IF;
36 IF INSTR(x, '%')
37 THEN SET TextString = REPLACE(TextString, '&\#037;','\%');
38 END IF;
39 IF INSTR(x, '&')
40 THEN SET TextString = REPLACE(TextString, '&\#038;','\&');
41 END IF;
42 IF INSTR(x, ''')
43 THEN SET TextString = REPLACE(TextString, '&\#039;','\'');
44 END IF;
45 #quotation mark
46 IF INSTR(x, '"')
47 THEN SET TextString = REPLACE(TextString, '"','"');
48 END IF;
49 #apostrophe
50 IF INSTR(x, ''')
51 THEN SET TextString = REPLACE(TextString, ''','"');
52 END IF;
53 #ampersand
54 IF INSTR(x, '&')
55 THEN SET TextString = REPLACE(TextString, '&','&');
56 END IF;
57 #less-than
58 IF INSTR(x, '<')
59 THEN SET TextString = REPLACE(TextString, '<','<');
60 END IF;
61 #greater-than
62 IF INSTR(x, '>')
63 THEN SET TextString = REPLACE(TextString, '>','>');
64 END IF;
65 #non-breaking space
66 IF INSTR(x, ' ')
67 THEN SET TextString = REPLACE(TextString, ' ',' ');
68 END IF;
69 #inverted exclamation mark
70 IF INSTR(x, '¡')
71 THEN SET TextString = REPLACE(TextString, '¡','¡');
72 END IF;
73 #cent
74 IF INSTR(x, '¢')
75 THEN SET TextString = REPLACE(TextString, '¢','¢');
76 END IF;
77 #pound
78 IF INSTR(x, '£')
79 THEN SET TextString = REPLACE(TextString, '£','£');
80 END IF;
81 #currency
82 IF INSTR(x, '¤')
83 THEN SET TextString = REPLACE(TextString, '¤','¤');
84 END IF;
85 #yen
86 IF INSTR(x, '¥')
87 THEN SET TextString = REPLACE(TextString, '¥','Â¥');
88 END IF;
89 #broken vertical bar
90 IF INSTR(x, '¦')
91 THEN SET TextString = REPLACE(TextString, '¦','¦');
92 END IF;
93 #section
94 IF INSTR(x, '§')
95 THEN SET TextString = REPLACE(TextString, '§','§');
96 END IF;
97 #spacing diaeresis
98 IF INSTR(x, '¨')
99 THEN SET TextString = REPLACE(TextString, '¨','¨');
100 END IF;
101 #copyright
102 IF INSTR(x, '©')
103 THEN SET TextString = REPLACE(TextString, '©','©');
104 END IF;
105 #feminine ordinal indicator
106 IF INSTR(x, 'ª')
107 THEN SET TextString = REPLACE(TextString, 'ª','ª');
108 END IF;
109 #angle quotation mark (left)
110 IF INSTR(x, '«')
111 THEN SET TextString = REPLACE(TextString, '«','«');
112 END IF;
113 #negation
114 IF INSTR(x, '¬')
115 THEN SET TextString = REPLACE(TextString, '¬','¬');
116 END IF;
117 #soft hyphen
118 IF INSTR(x, '­')
119 THEN SET TextString = REPLACE(TextString, '­','Â');
120 END IF;
121 #registered trademark
122 IF INSTR(x, '®')
123 THEN SET TextString = REPLACE(TextString, '®','®');
124 END IF;
125 #spacing macron
126 IF INSTR(x, '¯')
127 THEN SET TextString = REPLACE(TextString, '¯','¯');
128 END IF;
129 #degree
130 IF INSTR(x, '°')
131 THEN SET TextString = REPLACE(TextString, '°','°');
132 END IF;
133 #plus-or-minus
134 IF INSTR(x, '±')
135 THEN SET TextString = REPLACE(TextString, '±','±');
136 END IF;
137 #superscript 2
138 IF INSTR(x, '²')
139 THEN SET TextString = REPLACE(TextString, '²','²');
140 END IF;
141 #superscript 3
142 IF INSTR(x, '³')
143 THEN SET TextString = REPLACE(TextString, '³','³');
144 END IF;
145 #spacing acute
146 IF INSTR(x, '´')
147 THEN SET TextString = REPLACE(TextString, '´','´');
148 END IF;
149 #micro
150 IF INSTR(x, 'µ')
151 THEN SET TextString = REPLACE(TextString, 'µ','µ');
152 END IF;
153 #paragraph
154 IF INSTR(x, '¶')
155 THEN SET TextString = REPLACE(TextString, '¶','¶');
156 END IF;
157 #middle dot
158 IF INSTR(x, '·')
159 THEN SET TextString = REPLACE(TextString, '·','·');
160 END IF;
161 #spacing cedilla
162 IF INSTR(x, '¸')
163 THEN SET TextString = REPLACE(TextString, '¸','¸');
164 END IF;
165 #superscript 1
166 IF INSTR(x, '¹')
167 THEN SET TextString = REPLACE(TextString, '¹','¹');
168 END IF;
169 #masculine ordinal indicator
170 IF INSTR(x, 'º')
171 THEN SET TextString = REPLACE(TextString, 'º','º');
172 END IF;
173 #angle quotation mark (right)
174 IF INSTR(x, '»')
175 THEN SET TextString = REPLACE(TextString, '»','»');
176 END IF;
177 #fraction 1/4
178 IF INSTR(x, '¼')
179 THEN SET TextString = REPLACE(TextString, '¼','¼');
180 END IF;
181 #fraction 1/2
182 IF INSTR(x, '½')
183 THEN SET TextString = REPLACE(TextString, '½','½');
184 END IF;
185 #fraction 3/4
186 IF INSTR(x, '¾')
187 THEN SET TextString = REPLACE(TextString, '¾','¾');
188 END IF;
189 #inverted question mark
190 IF INSTR(x, '¿')
191 THEN SET TextString = REPLACE(TextString, '¿','¿');
192 END IF;
193 #multiplication
194 IF INSTR(x, '×')
195 THEN SET TextString = REPLACE(TextString, '×','×');
196 END IF;
197 #division
198 IF INSTR(x, '÷')
199 THEN SET TextString = REPLACE(TextString, '÷','÷');
200 END IF;
201 #capital a, grave accent
202 IF INSTR(x, 'À')
203 THEN SET TextString = REPLACE(TextString, 'À','À');
204 END IF;
205 #capital a, acute accent
206 IF INSTR(x, 'Á')
207 THEN SET TextString = REPLACE(TextString, 'Á','Ã');
208 END IF;
209 #capital a, circumflex accent
210 IF INSTR(x, 'Â')
211 THEN SET TextString = REPLACE(TextString, 'Â','Â');
212 END IF;
213 #capital a, tilde
214 IF INSTR(x, 'Ã')
215 THEN SET TextString = REPLACE(TextString, 'Ã','Ã');
216 END IF;
217 #capital a, umlaut mark
218 IF INSTR(x, 'Ä')
219 THEN SET TextString = REPLACE(TextString, 'Ä','Ä');
220 END IF;
221 #capital a, ring
222 IF INSTR(x, 'Å')
223 THEN SET TextString = REPLACE(TextString, 'Å','Ã…');
224 END IF;
225 #capital ae
226 IF INSTR(x, 'Æ')
227 THEN SET TextString = REPLACE(TextString, 'Æ','Æ');
228 END IF;
229 #capital c, cedilla
230 IF INSTR(x, 'Ç')
231 THEN SET TextString = REPLACE(TextString, 'Ç','Ç');
232 END IF;
233 #capital e, grave accent
234 IF INSTR(x, 'È')
235 THEN SET TextString = REPLACE(TextString, 'È','È');
236 END IF;
237 #capital e, acute accent
238 IF INSTR(x, 'É')
239 THEN SET TextString = REPLACE(TextString, 'É','É');
240 END IF;
241 #capital e, circumflex accent
242 IF INSTR(x, 'Ê')
243 THEN SET TextString = REPLACE(TextString, 'Ê','Ê');
244 END IF;
245 #capital e, umlaut mark
246 IF INSTR(x, 'Ë')
247 THEN SET TextString = REPLACE(TextString, 'Ë','Ë');
248 END IF;
249 #capital i, grave accent
250 IF INSTR(x, 'Ì')
251 THEN SET TextString = REPLACE(TextString, 'Ì','ÃŒ');
252 END IF;
253 #capital i, acute accent
254 IF INSTR(x, 'Í')
255 THEN SET TextString = REPLACE(TextString, 'Í','Ã');
256 END IF;
257 #capital i, circumflex accent
258 IF INSTR(x, 'Î')
259 THEN SET TextString = REPLACE(TextString, 'Î','ÃŽ');
260 END IF;
261 #capital i, umlaut mark
262 IF INSTR(x, 'Ï')
263 THEN SET TextString = REPLACE(TextString, 'Ï','Ã');
264 END IF;
265 #capital eth, Icelandic
266 IF INSTR(x, 'Ð')
267 THEN SET TextString = REPLACE(TextString, 'Ð','Ã');
268 END IF;
269 #capital n, tilde
270 IF INSTR(x, 'Ñ')
271 THEN SET TextString = REPLACE(TextString, 'Ñ','Ñ');
272 END IF;
273 #capital o, grave accent
274 IF INSTR(x, 'Ò')
275 THEN SET TextString = REPLACE(TextString, 'Ò','Ã’');
276 END IF;
277 #capital o, acute accent
278 IF INSTR(x, 'Ó')
279 THEN SET TextString = REPLACE(TextString, 'Ó','Ó');
280 END IF;
281 #capital o, circumflex accent
282 IF INSTR(x, 'Ô')
283 THEN SET TextString = REPLACE(TextString, 'Ô','Ô');
284 END IF;
285 #capital o, tilde
286 IF INSTR(x, 'Õ')
287 THEN SET TextString = REPLACE(TextString, 'Õ','Õ');
288 END IF;
289 #capital o, umlaut mark
290 IF INSTR(x, 'Ö')
291 THEN SET TextString = REPLACE(TextString, 'Ö','Ö');
292 END IF;
293 #capital o, slash
294 IF INSTR(x, 'Ø')
295 THEN SET TextString = REPLACE(TextString, 'Ø','Ø');
296 END IF;
297 #capital u, grave accent
298 IF INSTR(x, 'Ù')
299 THEN SET TextString = REPLACE(TextString, 'Ù','Ù');
300 END IF;
301 #capital u, acute accent
302 IF INSTR(x, 'Ú')
303 THEN SET TextString = REPLACE(TextString, 'Ú','Ú');
304 END IF;
305 #capital u, circumflex accent
306 IF INSTR(x, 'Û')
307 THEN SET TextString = REPLACE(TextString, 'Û','Û');
308 END IF;
309 #capital u, umlaut mark
310 IF INSTR(x, 'Ü')
311 THEN SET TextString = REPLACE(TextString, 'Ü','Ãœ');
312 END IF;
313 #capital y, acute accent
314 IF INSTR(x, 'Ý')
315 THEN SET TextString = REPLACE(TextString, 'Ý','Ã');
316 END IF;
317 #capital THORN, Icelandic
318 IF INSTR(x, 'Þ')
319 THEN SET TextString = REPLACE(TextString, 'Þ','Þ');
320 END IF;
321 #small sharp s, German
322 IF INSTR(x, 'ß')
323 THEN SET TextString = REPLACE(TextString, 'ß','ß');
324 END IF;
325 #small a, grave accent
326 IF INSTR(x, 'à')
327 THEN SET TextString = REPLACE(TextString, 'à','Ã ');
328 END IF;
329 #small a, acute accent
330 IF INSTR(x, 'á')
331 THEN SET TextString = REPLACE(TextString, 'á','á');
332 END IF;
333 #small a, circumflex accent
334 IF INSTR(x, 'â')
335 THEN SET TextString = REPLACE(TextString, 'â','â');
336 END IF;
337 #small a, tilde
338 IF INSTR(x, 'ã')
339 THEN SET TextString = REPLACE(TextString, 'ã','ã');
340 END IF;
341 #small a, umlaut mark
342 IF INSTR(x, 'ä')
343 THEN SET TextString = REPLACE(TextString, 'ä','ä');
344 END IF;
345 #small a, ring
346 IF INSTR(x, 'å')
347 THEN SET TextString = REPLACE(TextString, 'å','Ã¥');
348 END IF;
349 #small ae
350 IF INSTR(x, 'æ')
351 THEN SET TextString = REPLACE(TextString, 'æ','æ');
352 END IF;
353 #small c, cedilla
354 IF INSTR(x, 'ç')
355 THEN SET TextString = REPLACE(TextString, 'ç','ç');
356 END IF;
357 #small e, grave accent
358 IF INSTR(x, 'è')
359 THEN SET TextString = REPLACE(TextString, 'è','è');
360 END IF;
361 #small e, acute accent
362 IF INSTR(x, 'é')
363 THEN SET TextString = REPLACE(TextString, 'é','é');
364 END IF;
365 #small e, circumflex accent
366 IF INSTR(x, 'ê')
367 THEN SET TextString = REPLACE(TextString, 'ê','ê');
368 END IF;
369 #small e, umlaut mark
370 IF INSTR(x, 'ë')
371 THEN SET TextString = REPLACE(TextString, 'ë','ë');
372 END IF;
373 #small i, grave accent
374 IF INSTR(x, 'ì')
375 THEN SET TextString = REPLACE(TextString, 'ì','ì');
376 END IF;
377 #small i, acute accent
378 IF INSTR(x, 'í')
379 THEN SET TextString = REPLACE(TextString, 'í','Ã');
380 END IF;
381 #small i, circumflex accent
382 IF INSTR(x, 'î')
383 THEN SET TextString = REPLACE(TextString, 'î','î');
384 END IF;
385 #small i, umlaut mark
386 IF INSTR(x, 'ï')
387 THEN SET TextString = REPLACE(TextString, 'ï','ï');
388 END IF;
389 #small eth, Icelandic
390 IF INSTR(x, 'ð')
391 THEN SET TextString = REPLACE(TextString, 'ð','ð');
392 END IF;
393 #small n, tilde
394 IF INSTR(x, 'ñ')
395 THEN SET TextString = REPLACE(TextString, 'ñ','ñ');
396 END IF;
397 #small o, grave accent
398 IF INSTR(x, 'ò')
399 THEN SET TextString = REPLACE(TextString, 'ò','ò');
400 END IF;
401 #small o, acute accent
402 IF INSTR(x, 'ó')
403 THEN SET TextString = REPLACE(TextString, 'ó','ó');
404 END IF;
405 #small o, circumflex accent
406 IF INSTR(x, 'ô')
407 THEN SET TextString = REPLACE(TextString, 'ô','ô');
408 END IF;
409 #small o, tilde
410 IF INSTR(x, 'õ')
411 THEN SET TextString = REPLACE(TextString, 'õ','õ');
412 END IF;
413 #small o, umlaut mark
414 IF INSTR(x, 'ö')
415 THEN SET TextString = REPLACE(TextString, 'ö','ö');
416 END IF;
417 #small o, slash
418 IF INSTR(x, 'ø')
419 THEN SET TextString = REPLACE(TextString, 'ø','ø');
420 END IF;
421 #small u, grave accent
422 IF INSTR(x, 'ù')
423 THEN SET TextString = REPLACE(TextString, 'ù','ù');
424 END IF;
425 #small u, acute accent
426 IF INSTR(x, 'ú')
427 THEN SET TextString = REPLACE(TextString, 'ú','ú');
428 END IF;
429 #small u, circumflex accent
430 IF INSTR(x, 'û')
431 THEN SET TextString = REPLACE(TextString, 'û','û');
432 END IF;
433 #small u, umlaut mark
434 IF INSTR(x, 'ü')
435 THEN SET TextString = REPLACE(TextString, 'ü','ü');
436 END IF;
437 #small y, acute accent
438 IF INSTR(x, 'ý')
439 THEN SET TextString = REPLACE(TextString, 'ý','ý');
440 END IF;
441 #small thorn, Icelandic
442 IF INSTR(x, 'þ')
443 THEN SET TextString = REPLACE(TextString, 'þ','þ');
444 END IF;
445 #small y, umlaut mark
446 IF INSTR(x, 'ÿ')
447 THEN SET TextString = REPLACE(TextString, 'ÿ','ÿ');
448 END IF;
449
450 RETURN TextString;
451
452END$$
453
454DELIMITER ;
455
456
457-- Utilisateurs
458
459DELETE FROM mvd_joomla2.gltb5_usergroups;
460DELETE FROM mvd_joomla.gltb5_users;
461DELETE FROM mvd_joomla.gltb5_comprofiler;
462DELETE FROM mvd_joomla.gltb5_user_usergroup_map;
463DELETE FROM mvd_joomla.gltb5_kunena_categories;
464DELETE FROM mvd_joomla.gltb5_kunena_aliases;
465DELETE FROM mvd_joomla.gltb5_kunena_topics;
466DELETE FROM mvd_joomla.gltb5_kunena_messages;
467DELETE FROM mvd_joomla.gltb5_kunena_messages_text;
468
469-- ALTER TABLE mvd_joomla.gltb5_users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
470-- ALTER TABLE mvd_joomla.gltb5_kunena_categories CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
471-- ALTER TABLE mvd_joomla.gltb5_kunena_aliases CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
472-- ALTER TABLE mvd_joomla.gltb5_kunena_topics CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
473-- ALTER TABLE mvd_joomla.gltb5_kunena_messages CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
474-- ALTER TABLE mvd_joomla.gltb5_kunena_messages_text CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
475
476INSERT INTO mvd_joomla2.gltb5_usergroups
477 (id, parent_id, lft, rgt, title)
478VALUES
479 (1, 0, 0, 0, 'Public'),
480 (2, 1, 0, 0, 'Enregistré'),
481 (3, 2, 0, 0, 'Auteur'),
482 (4, 3, 0, 0, 'Rédacteur'),
483 (5, 4, 0, 0, 'Validateur'),
484 (6, 1, 0, 0, 'Gestionnaire'),
485 (7, 6, 0, 0, 'Administrateur'),
486 (8, 1, 0, 0, 'Super Utilisateur'),
487 (9, 1, 0, 0, 'Invité');
488
489INSERT INTO mvd_joomla2.gltb5_usergroups (
490 id,
491 parent_id,
492 lft,
493 rgt,
494 title
495) SELECT
496 -- Ajouter 10 pour la continuité
497 userclass_id+15 AS id,
498 2 AS parent_id,
499 0 AS lft,
500 0 AS rgt,
501 userclass_name AS title
502FROM mvd_e107.docksuserclass_classes;
503
504INSERT INTO mvd_joomla.gltb5_users (
505 id,
506 name,
507 username,
508 email,
509 password,
510 block,
511 sendEmail,
512 registerDate,
513 lastvisitDate,
514 activation,
515 requireReset,
516 params
517) SELECT
518 user_id AS id,
519 user_name AS name,
520 user_loginname AS username,
521 user_email AS email,
522 '\$2y\$10\$1OUxS6zW4wdRgs1xYPYcKuyuaABs91AIA.m32f.rCkQBwXXx.Vnhe' AS password,
523 user_ban AS block,
524 0 AS sendEmail,
525 FROM_UNIXTIME(user_join) AS registerDate,
526 FROM_UNIXTIME(user_lastvisit) AS lastVisitDate,
527 1 AS activation,
528 0 AS requireReset, /*The e107 password cannot be converted to joomla password.*/
529 '' AS params
530FROM mvd_e107.docksuser;
531
532DELIMITER $$
533DROP FUNCTION IF EXISTS mvd_joomla.insert_user_groups $$
534CREATE FUNCTION mvd_joomla.insert_user_groups(a_group_ids VARCHAR(255), a_user_id INTEGER) RETURNS INTEGER
535BEGIN
536 SET @group_ids = a_group_ids;
537
538 WHILE @group_ids != '' DO
539 SET @group_id = SUBSTRING_INDEX(@group_ids, ',', 1);
540 SET @group_ids = SUBSTRING(@group_ids, LENGTH(@group_id) + 2);
541 INSERT INTO mvd_joomla.gltb5_user_usergroup_map (group_id, user_id) VALUES (@group_id + 10, a_user_id);
542 END WHILE;
543
544 RETURN 1;
545END $$
546DELIMITER ;
547
548-- SELECT DISTINCT mvd_joomla.insert_user_groups(user_class, user_id) FROM mvd_e107.docksuser;
549
550INSERT INTO mvd_joomla.gltb5_user_usergroup_map (
551 group_id,
552 user_id
553) SELECT
554 2,
555 user_id
556FROM mvd_e107.docksuser;
557
558INSERT INTO mvd_joomla.gltb5_user_usergroup_map (
559 group_id,
560 user_id
561) SELECT
562 8,
563 user_id
564FROM mvd_e107.docksuser WHERE user_admin = 1 OR user_name IN ('Raoul', 'Jerome Blu');
565
566INSERT INTO mvd_joomla.gltb5_user_usergroup_map (
567 group_id,
568 user_id
569) SELECT
570 c.userclass_id+15 AS group_id,
571 user_id
572FROM mvd_e107.docksuser u INNER JOIN mvd_e107.docksuserclass_classes c
573ON u.user_class = c.userclass_id
574OR u.user_class LIKE CONCAT('%,', c.userclass_id, ',%')
575OR u.user_class LIKE CONCAT(c.userclass_id, ',%')
576OR u.user_class LIKE CONCAT('%,', c.userclass_id);
577
578
579INSERT INTO mvd_joomla.gltb5_comprofiler (
580 id,
581 user_id,
582 firstname,
583 middlename,
584 lastname,
585 hits,
586 message_last_sent,
587 message_number_sent,
588 avatar,
589 avatarapproved,
590 canvas,
591 canvasapproved,
592 approved,
593 confirmed,
594 lastupdatedate,
595 registeripaddr,
596 cbactivation,
597 banned,
598 banneddate,
599 unbanneddate,
600 bannedby,
601 unbannedby,
602 bannedreason,
603 acceptedterms,
604 cb_genre,
605 cb_mvd_groupes,
606 cb_anniversaire,
607 cb_nombredenfants,
608 cb_association,
609 cb_aproposdemoi
610) SELECT
611 u.user_id AS id,
612 u.user_id AS user_id,
613 ue.user_Prenom AS firstname,
614 NULL AS middlename,
615 ue.user_Nom AS lastname,
616 u.user_visits AS hits,
617 '0000-00-00' AS message_last_sent,
618 u.user_forums AS message_number_sent,
619 NULL AS avatar,
620 1 AS avatarapproved,
621 NULL AS canvas,
622 1 AS canvasapproved,
623 1 AS approved,
624 1 AS confirmed,
625 FROM_UNIXTIME(u.user_currentvisit) AS lastupdatedate,
626 u.user_ip AS registeripaddr,
627 '' AS cbactivation,
628 0 AS banned,
629 NULL AS banneddate,
630 NULL AS unbanneddate,
631 NULL AS bannedby,
632 NULL AS unbannedby,
633 NULL AS bannedreason,
634 1 AS acceptedterms,
635 '' AS cb_genre,
636 '' AS cb_mvd_groupes,
637 ue.user_birthday AS cb_anniversaire,
638 ue.user_enfants AS cb_nombredenfants,
639 0 AS cb_association,
640 u.user_signature AS cb_aproposdemoi
641FROM mvd_e107.docksuser u INNER JOIN mvd_e107.docksuser_extended ue ON u.user_id=ue.user_extended_id;
642
643INSERT INTO mvd_joomla.gltb5_kunena_categories
644(
645 id,
646 parent_id,
647 name,
648 alias,
649 icon,
650 icon_id,
651 locked,
652 accesstype,
653 access,
654 pub_access,
655 pub_recurse,
656 admin_access,
657 admin_recurse,
658 ordering,
659 published,
660 checked_out,
661 checked_out_time,
662 review,
663 allow_anonymous,
664 post_anonymous,
665 hits,
666 description,
667 headerdesc,
668 class_sfx,
669 allow_polls,
670 topic_ordering,
671 numTopics,
672 numPosts,
673 last_topic_id,
674 last_post_id,
675 last_post_time,
676 params
677) SELECT
678 forum_id AS id,
679 forum_parent AS parent_id,
680 mvd_joomla.HTML_UnEncode(forum_name) AS name,
681 forum_id AS alias,
682 'fa fa-list' as icon,
683 0 AS icon_id,
684 0 AS locked,
685 'joomla.level' AS accesstype,
686 1 AS access,
687 1 AS pub_access,
688 1 AS pub_recurse,
689 8 AS admin_access,
690 1 AS admin_recurse,
691 forum_order AS ordering,
692 1 AS published,
693 0 AS checked_out,
694 '0000-00-00 00:00:00' AS checked_out_time,
695 0 AS review,
696 0 AS allow_anonymous,
697 0 AS post_anonymous,
698 0 AS hits,
699 mvd_joomla.HTML_UnEncode(forum_description) as description,
700 '' AS headerdesc,
701 '' AS class_sfx,
702 1 AS allow_polls,
703 'lastpost' AS topic_ordering,
704 forum_threads AS numTopics,
705 forum_replies AS numPosts,
706 0 AS last_topic_id,
707 0 AS last_post_id,
708 0 AS last_post_time,
709 '{}' AS params
710 FROM mvd_e107.docksforum
711ORDER BY forum_id;
712
713
714INSERT INTO mvd_joomla.gltb5_kunena_aliases(alias, type, item, state)
715SELECT alias, 'catid', id, 0
716FROM mvd_joomla.gltb5_kunena_categories;
717
718
719INSERT INTO mvd_joomla.gltb5_kunena_topics
720(
721 id,
722 category_id,
723 subject,
724 icon_id,
725 locked,
726 hold,
727 ordering,
728 posts,
729 hits,
730 attachments,
731 poll_id,
732 moved_id,
733 first_post_id,
734 first_post_time,
735 first_post_userid,
736 first_post_message,
737 first_post_guest_name,
738 last_post_id,
739 last_post_time,
740 last_post_userid,
741 last_post_message,
742 last_post_guest_name,
743 params
744)
745SELECT
746 thread_id as id,
747 thread_forum_id as category_id,
748 mvd_joomla.HTML_UnEncode(thread_name) as subject,
749 0 AS icon_id,
750 0 AS locked,
751 0 AS hold,
752 0 AS ordering,
753 thread_total_replies + 1 AS posts,
754 thread_views AS hits,
755 0 AS attachments,
756 0 AS poll_id,
757 0 AS moved_id,
758 thread_id AS first_post_id,
759 thread_datestamp AS first_post_time,
760 SUBSTRING_INDEX(thread_user, '.', 1) AS first_post_userid,
761 mvd_joomla.HTML_UnEncode(thread_thread) AS first_post_message,
762 SUBSTRING_INDEX(thread_user, '.', -1) AS first_post_guest_name,
763 IFNULL((SELECT thread_id FROM mvd_e107.docksforum_t TMP WHERE TMP.thread_parent=T.thread_id ORDER BY thread_datestamp DESC LIMIT 1), thread_id) AS last_post_id,
764 thread_lastpost AS last_post_time,
765 SUBSTRING_INDEX(thread_lastuser, '.', 1) AS last_post_userid,
766 IFNULL((SELECT thread_thread FROM mvd_e107.docksforum_t TMP WHERE TMP.thread_parent=T.thread_id ORDER BY thread_datestamp DESC LIMIT 1), thread_thread) AS last_post_message,
767 SUBSTRING_INDEX(thread_lastuser, '.', -1) AS last_post_guest_name,
768 '' AS params
769FROM mvd_e107.docksforum_t as T
770WHERE thread_parent=0;
771
772
773
774INSERT INTO mvd_joomla.gltb5_kunena_messages
775(
776 id,
777 parent,
778 thread,
779 catid,
780 name,
781 userid,
782 email,
783 subject,
784 time,
785 ip,
786 topic_emoticon,
787 locked,
788 hold,
789 ordering,
790 hits,
791 moved
792)
793SELECT
794 thread_id AS id,
795 thread_parent AS parent,
796 IF(thread_parent > 0, thread_parent, thread_id) AS thread,
797 thread_forum_id AS catid,
798 SUBSTRING_INDEX(thread_user, '.', -1) AS name,
799 SUBSTRING_INDEX(thread_user, '.', 1) AS userid,
800 '' AS email,
801 mvd_joomla.HTML_UnEncode(thread_name) AS subject,
802 thread_datestamp AS time,
803 '' AS ip,
804 0 AS topic_emoticon,
805 0 AS locked,
806 0 AS hold,
807 0 AS ordering,
808 thread_views AS hits,
809 0 AS moved
810FROM mvd_e107.docksforum_t as T;
811
812
813INSERT INTO mvd_joomla.gltb5_kunena_messages_text
814(
815 mesid,
816 message
817)
818SELECT
819 thread_id AS mesid,
820 mvd_joomla.HTML_UnEncode(thread_thread) AS message
821FROM mvd_e107.docksforum_t as T;