| 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 | |
| 5 | # Create one account by email |
| 6 | INSERT INTO accounts ( |
| 7 | SELECT NULL AS uid, |
| 8 | CONCAT(LOWER(REPLACE(email,'@','.')),'.ext') AS hruid, |
| 9 | IF(origine = 'ext','xnet','virtual') AS type, |
| 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, |
| 17 | NULL AS comment, |
| 18 | email, |
| 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, |
| 25 | IF(sexe,'female','male') AS sex, |
| 26 | 'html' AS email_format, |
| 27 | 1 AS skin, |
| 28 | NULL as last_version |
| 29 | FROM group_members WHERE origine != 'x' GROUP BY hruid); |
| 30 | |
| 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)) |
| 41 | WHERE a.hruid = CONCAT(LOWER(REPLACE(g.email,'@','.')),'.ext') AND g.prenom IS NULL AND POSITION(' ' IN a.full_name); |
| 42 | |
| 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) |
| 47 | WHERE g1.origine != 'x' AND g2.origine != 'x'; |
| 48 | |
| 49 | # Update uids |
| 50 | UPDATE group_members AS g, accounts AS a |
| 51 | SET g.uid = a.uid |
| 52 | WHERE g.origine != 'x' AND |
| 53 | a.hruid = CONCAT(LOWER(REPLACE(g.email,'@','.')),'.ext'); |
| 54 | |
| 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 | |
| 66 | # vim:set syntax=mysql: |