From 0dc8963ca4297583eda1612cb1817b517a2c076a Mon Sep 17 00:00:00 2001 From: Pascal Corpet Date: Thu, 10 Jun 2010 21:39:23 +0200 Subject: [PATCH] Xnet import into account : fixes empty names --- upgrade/account/30_xnet_groups.sql | 24 ++++++++++++++++++++++-- 1 file changed, 22 insertions(+), 2 deletions(-) diff --git a/upgrade/account/30_xnet_groups.sql b/upgrade/account/30_xnet_groups.sql index 8caaaf0..8f21ab6 100644 --- a/upgrade/account/30_xnet_groups.sql +++ b/upgrade/account/30_xnet_groups.sql @@ -1,3 +1,7 @@ +# Clean up group_members +UPDATE group_members SET prenom = NULL WHERE LENGTH(TRIM(prenom)) = 0; +UPDATE group_members SET nom = NULL WHERE LENGTH(TRIM(nom)) = 0; + # Create one account by email INSERT INTO accounts ( SELECT NULL AS uid, @@ -12,14 +16,30 @@ INSERT INTO accounts ( '' AS flags, comm AS comment, email, - CONCAT(prenom,' ',nom) AS full_name, - prenom AS display_name, + IF(prenom IS NULL, + REPLACE(LEFT(email, POSITION('@' IN email)-1),'.',' '), + CONCAT(prenom,' ',nom)) AS full_name, + IF(prenom IS NULL, + LEFT(email, POSITION('.' IN REPLACE(email,'@','.'))-1), + prenom) AS display_name, IF(sexe,'female','male') AS sex, 'html' AS email_format, 1 AS skin, NULL as last_version FROM group_members WHERE origine = 'ext' GROUP BY hruid); +# Make Upper case for first letters in fake full_names and display_names +UPDATE accounts AS a, group_members AS g + SET a.display_name = CONCAT(UPPER(LEFT(a.display_name,1)),LOWER(MID(a.display_name, 2))), + a.full_name = CONCAT(UPPER(LEFT(a.full_name,1)),LOWER(MID(a.full_name, 2))) + WHERE a.hruid = CONCAT(LOWER(REPLACE(g.email,'@','.')),'.ext') AND g.prenom IS NULL; +UPDATE accounts AS a, group_members AS g + SET a.full_name = CONCAT( + LEFT(a.full_name, POSITION(' ' IN a.full_name)), + UPPER(MID(a.full_name, POSITION(' ' IN a.full_name) + 1, 1)), + MID(a.full_name, POSITION(' ' IN a.full_name) + 2)) + WHERE a.hruid = CONCAT(LOWER(REPLACE(g.email,'@','.')),'.ext') AND POSITION(' ' IN a.full_name); + # Delete person that are several time in same asso DELETE g2 FROM group_members AS g1 -- 2.1.4