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;
5 #
Create one account
by email
8 CONCAT(LOWER(REPLACE(email
,'@','.')),'.ext') AS hruid
,
9 IF(origine
= 'ext','xnet','virtual') AS type,
14 NULL as weak_password
,
15 NULL as registration_date
,
20 REPLACE(LEFT(email
, POSITION('@' IN email
)-1),'.',' '),
21 CONCAT(prenom
,' ',nom
)) AS full_name
,
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
,
29 FROM group_members
WHERE origine
!= 'x' GROUP BY hruid
);
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
);
43 #
Delete person that
are several
time in same asso
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';
50 UPDATE group_members
AS g
, accounts
AS a
52 WHERE g.origine
!= 'x' AND
53 a.hruid
= CONCAT(LOWER(REPLACE(g.email
,'@','.')),'.ext');
55 #
Enable virtual
accounts (for groups
) with no rights
56 INSERT IGNORE INTO account_types
VALUES('virtual', '');
58 #
Drop now unused
columns
59 ALTER TABLE group_members
66 # vim
:set syntax
=mysql
: