upgrade/account/40_xnet_groups.sql

   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: