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, | |
9 | 'xnet' 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 | comm AS comment, | |
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 | |
29 | FROM group_members WHERE origine = 'ext' GROUP BY hruid); | |
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)) | |
41 | WHERE a.hruid = CONCAT(LOWER(REPLACE(g.email,'@','.')),'.ext') AND POSITION(' ' IN a.full_name); | |
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) | |
47 | WHERE g1.origine = 'ext' AND g2.origine = 'ext'; | |
48 | ||
49 | # Update uids | |
50 | UPDATE group_members AS g, accounts AS a | |
51 | SET g.uid = a.uid | |
52 | WHERE g.origine = 'ext' AND | |
53 | a.hruid = CONCAT(LOWER(REPLACE(g.email,'@','.')),'.ext'); | |
54 | ||
55 | # vim:set syntax=mysql: |