X-Git-Url: http://git.polytechnique.org/?a=blobdiff_plain;f=upgrade%2Fnewdirectory-0.0.1%2F15_addresses.sql;h=8ce64b6025d6dd9228925f38d569af8188bdf6cb;hb=843a2191e4c76f0beb00c754f06974de76791238;hp=0294ea469a508da7ea3bfc5f1582be24811e019a;hpb=00ca0ad5370df5be4a0272364fb16a4385ffabfe;p=platal.git

diff --git a/upgrade/newdirectory-0.0.1/15_addresses.sql b/upgrade/newdirectory-0.0.1/15_addresses.sql
index 0294ea4..8ce64b6 100644
--- a/upgrade/newdirectory-0.0.1/15_addresses.sql
+++ b/upgrade/newdirectory-0.0.1/15_addresses.sql
@@ -34,16 +34,23 @@ CREATE TABLE profile_addresses (
   INDEX countryId (countryId)
 ) ENGINE=InnoDB, CHARSET=utf8;
 
-INSERT INTO  profile_addresses (pid, id, postalCode, updateTime, pub, comment, latitude, longitude, countryId,
-                                type, flags)
-     SELECT  uid, adrid, postcode, datemaj, pub, NULL, glat, glng, country,
+INSERT INTO  profile_addresses (pid, id, postalCode, updateTime, pub, comment, latitude, longitude,
+                                countryId, type, flags)
+     SELECT  uid, adrid, postcode, datemaj, pub, NULL, glat, glng, IF(country = '' OR country = '00', NULL, country),
              IF(FIND_IN_SET('pro', 'statut'), 'job', 'home'),
-             CONCAT(IF(FIND_IN_SET('res-secondaire', 'statut'), 'secondary,', ''),
-                    IF(FIND_IN_SET('courrier', 'statut'), 'mail,', ''),
-                    IF(FIND_IN_SET('active', 'statut'), 'current,', ''),
-                    IF(FIND_IN_SET('temporaire', 'statut'), 'temporary', ''))
+             CONCAT(IF(FIND_IN_SET('res-secondaire', statut), 'secondary,', ''),
+                    IF(FIND_IN_SET('courrier', statut), 'mail,', ''),
+                    IF(FIND_IN_SET('active', statut), 'current,', ''),
+                    IF(FIND_IN_SET('temporaire', statut), 'temporary', ''))
        FROM  #x4dat#.adresses;
 
+INSERT INTO  profile_addresses (pid, jobid, id, postalCode, pub, comment,
+                                latitude, longitude, countryId, type)
+     SELECT  e.uid, j.id, e.entrid, e.postcode, e.adr_pub, NULL, e.glat, e.glng,
+             IF(e.country = '' OR e.country = '00', NULL, e.country), 'job'
+       FROM  #x4dat#.entreprises AS e
+ INNER JOIN  profile_job_enum    AS j ON (e.entreprise = j.name);
+
 DROP TABLE IF EXISTS geoloc_countries;
 CREATE TABLE geoloc_countries (
   iso_3166_1_a2 CHAR(2) NOT NULL,
@@ -287,7 +294,7 @@ INSERT INTO  geoloc_countries (iso_3166_1_a2, iso_3166_1_a3, iso_3166_1_num, wor
                      WHEN "ER" THEN 232
                      ELSE n3 END,
              worldrgn, pays, country, capital, nat,
-             phoneprf, phoneformat, t.code
+             phoneprf, '', t.code
        FROM  #x4dat#.geoloc_pays AS g
  INNER JOIN  tmp_update_geoloc_pays AS t ON (t.a2 = g.a2);