From d989355f50a69412c756e6729c83cba913683e40 Mon Sep 17 00:00:00 2001 From: =?utf8?q?St=C3=A9phane=20Jacob?= Date: Thu, 24 Jun 2010 10:26:15 +0200 Subject: [PATCH] Imports job addresses (Closes #1149). MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Signed-off-by: Stéphane Jacob --- upgrade/newdirectory-0.0.1/15_addresses.sql | 6 ++++++ upgrade/newdirectory-0.0.1/addresses.php | 21 +++++++++++++++++++++ 2 files changed, 27 insertions(+) diff --git a/upgrade/newdirectory-0.0.1/15_addresses.sql b/upgrade/newdirectory-0.0.1/15_addresses.sql index 0d9ef47..84101de 100644 --- a/upgrade/newdirectory-0.0.1/15_addresses.sql +++ b/upgrade/newdirectory-0.0.1/15_addresses.sql @@ -44,6 +44,12 @@ INSERT INTO profile_addresses (pid, id, postalCode, updateTime, pub, comment, l 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 uid, entrid, 0, postcode, adr_pub, NULL, glat, glng, + IF(country = '' OR country = '00', NULL, country), 'job' + FROM #x4dat#.entreprises; + DROP TABLE IF EXISTS geoloc_countries; CREATE TABLE geoloc_countries ( iso_3166_1_a2 CHAR(2) NOT NULL, diff --git a/upgrade/newdirectory-0.0.1/addresses.php b/upgrade/newdirectory-0.0.1/addresses.php index 12c0497..209521f 100755 --- a/upgrade/newdirectory-0.0.1/addresses.php +++ b/upgrade/newdirectory-0.0.1/addresses.php @@ -17,6 +17,7 @@ echo "This will take a few minutes.\n"; // Fills the 'text' field in profile_addresses. for ($pid = $minPid; $pid < $maxPid + 1; ++$pid) { + // First deals with home addresses (located in #x4dat#.adresses). $res = XDB::iterator("SELECT a.adrid AS id, a.adr1, a.adr2, a.adr3, UNIX_TIMESTAMP(a.datemaj) AS datemaj, a.postcode, a.city, a.cityid, a.region, a.regiontxt, @@ -42,6 +43,26 @@ for ($pid = $minPid; $pid < $maxPid + 1; ++$pid) { WHERE pid = {?} AND type = {?} AND id = {?}', $text, $pid, $address['pro'] ? 'job' : 'home', $address['id']); } + + // Then deals with job addresses (located in #x4dat#.entreprises). + $res = XDB::iterator("SELECT e.entrid AS jobid, 0 AS id, e.adr1, e.adr2, e.adr3, + e.postcode, e.city, e.cityid, e.region, e.regiontxt, + e.adr_pub AS pub, e.country, gp.pays AS countrytxt, gp.display, + e.glat AS precise_lat, e.glng AS precise_lon + FROM #x4dat#.entreprises AS e + INNER JOIN #x4dat#.geoloc_pays AS gp ON (gp.a2 = e.country) + INNER JOIN account_profiles AS ap ON (e.uid = ap.uid AND FIND_IN_SET('owner', ap.perms)) + WHERE ap.pid = {?} + ORDER BY e.entrid", + $pid); + + while ($address = $res->next()) { + $text = get_address_text($address); + XDB::iterator('UPDATE profile_addresses + SET text = {?} + WHERE pid = {?} AND type = {?} AND id = {?} AND jobid = {?}', + $text, $pid, 'job', $address['id'], $address['jobid']); + } } function get_address_text($adr) -- 2.1.4