Commit | Line | Data |
---|---|---|
0dc8963c PC |
1 | # Clean up group_members |
2 | UPDATE group_members SET prenom = NULL WHERE LENGTH(TRIM(prenom)) = 0; | |
3 | UPDATE group_members SET nom = NULL WHERE LENGTH(TRIM(nom)) = 0; | |
4 | ||
1d4cd498 PC |
5 | # Create one account by email |
6 | INSERT INTO accounts ( | |
7 | SELECT NULL AS uid, | |
8 | CONCAT(LOWER(REPLACE(email,'@','.')),'.ext') AS hruid, | |
bb88d138 | 9 | IF(origine = 'ext','xnet','virtual') AS type, |
1d4cd498 PC |
10 | 0 as is_admin, |
11 | 'disabled' AS state, | |
12 | NULL as password, | |
13 | NULL as token, | |
14 | NULL as weak_password, | |
15 | NULL as registration_date, | |
16 | '' AS flags, | |
ab06182d | 17 | NULL AS comment, |
1d4cd498 | 18 | email, |
0dc8963c PC |
19 | IF(prenom IS NULL, |
20 | REPLACE(LEFT(email, POSITION('@' IN email)-1),'.',' '), | |
21 | CONCAT(prenom,' ',nom)) AS full_name, | |
22 | IF(prenom IS NULL, | |
23 | LEFT(email, POSITION('.' IN REPLACE(email,'@','.'))-1), | |
24 | prenom) AS display_name, | |
1d4cd498 PC |
25 | IF(sexe,'female','male') AS sex, |
26 | 'html' AS email_format, | |
27 | 1 AS skin, | |
28 | NULL as last_version | |
bb88d138 | 29 | FROM group_members WHERE origine != 'x' GROUP BY hruid); |
1d4cd498 | 30 | |
0dc8963c PC |
31 | # Make Upper case for first letters in fake full_names and display_names |
32 | UPDATE accounts AS a, group_members AS g | |
33 | SET a.display_name = CONCAT(UPPER(LEFT(a.display_name,1)),LOWER(MID(a.display_name, 2))), | |
34 | a.full_name = CONCAT(UPPER(LEFT(a.full_name,1)),LOWER(MID(a.full_name, 2))) | |
35 | WHERE a.hruid = CONCAT(LOWER(REPLACE(g.email,'@','.')),'.ext') AND g.prenom IS NULL; | |
36 | UPDATE accounts AS a, group_members AS g | |
37 | SET a.full_name = CONCAT( | |
38 | LEFT(a.full_name, POSITION(' ' IN a.full_name)), | |
39 | UPPER(MID(a.full_name, POSITION(' ' IN a.full_name) + 1, 1)), | |
40 | MID(a.full_name, POSITION(' ' IN a.full_name) + 2)) | |
bb88d138 | 41 | WHERE a.hruid = CONCAT(LOWER(REPLACE(g.email,'@','.')),'.ext') AND g.prenom IS NULL AND POSITION(' ' IN a.full_name); |
0dc8963c | 42 | |
1d4cd498 PC |
43 | # Delete person that are several time in same asso |
44 | DELETE g2 | |
45 | FROM group_members AS g1 | |
46 | INNER JOIN group_members AS g2 ON (g1.asso_id = g2.asso_id AND g1.email = g2.email AND g1.uid < g2.uid) | |
bb88d138 | 47 | WHERE g1.origine != 'x' AND g2.origine != 'x'; |
1d4cd498 PC |
48 | |
49 | # Update uids | |
50 | UPDATE group_members AS g, accounts AS a | |
51 | SET g.uid = a.uid | |
bb88d138 | 52 | WHERE g.origine != 'x' AND |
1d4cd498 PC |
53 | a.hruid = CONCAT(LOWER(REPLACE(g.email,'@','.')),'.ext'); |
54 | ||
bb88d138 PC |
55 | # Enable virtual accounts (for groups) with no rights |
56 | INSERT IGNORE INTO account_types VALUES('virtual', ''); | |
57 | ||
58 | # Drop now unused columns | |
59 | ALTER TABLE group_members | |
60 | DROP COLUMN sexe, | |
61 | DROP COLUMN prenom, | |
62 | DROP COLUMN nom, | |
63 | DROP COLUMN origine, | |
64 | DROP COLUMN email; | |
65 | ||
1d4cd498 | 66 | # vim:set syntax=mysql: |