From e44d8688495f9dce9d860460bdad24887a0caecd Mon Sep 17 00:00:00 2001 From: =?utf8?q?St=C3=A9phane=20Jacob?= Date: Sun, 8 Jan 2012 17:14:44 +0100 Subject: [PATCH] Adds french postal code retrieval for postal addresses dump. MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Signed-off-by: Stéphane Jacob --- classes/address.php | 12 ++++++++---- include/userset.inc.php | 20 ++++++-------------- upgrade/1.1.5/04_addresses.sql | 3 +++ 3 files changed, 17 insertions(+), 18 deletions(-) create mode 100644 upgrade/1.1.5/04_addresses.sql diff --git a/classes/address.php b/classes/address.php index 9783018..e5f677c 100644 --- a/classes/address.php +++ b/classes/address.php @@ -298,6 +298,7 @@ class Address // Geocoding fields. public $text = ''; public $postalText = ''; + public $postal_code_fr = null; public $types = ''; public $formatted_address = ''; public $components = array(); @@ -492,6 +493,7 @@ class Address --$count; } + $postal_code = null; // All the lines must have less than 38 characters but street and // locality lines whose limit is 32 characters. foreach ($arrayText as $lineNumber => $line) { @@ -499,7 +501,8 @@ class Address $formattedLine = $this->getStreetNumberFR($line) . ' '; $limit = 32; } elseif ($this->isLocalityFR($line)) { - $formattedLine = $this->getPostalCodeFR($line) . ' '; + $postal_code = $this->getPostalCodeFR($line); + $formattedLine = $postal_code . ' '; $limit = 32; } else { $formattedLine = ''; @@ -536,6 +539,7 @@ class Address $arrayText[$lineNumber] = trim($formattedLine); } + $this->postal_code_fr = $postal_code; return implode("\n", $arrayText); } @@ -693,12 +697,12 @@ class Address XDB::execute('INSERT IGNORE INTO profile_addresses (pid, jobid, groupid, type, id, flags, text, postalText, pub, comment, types, formatted_address, location_type, partial_match, latitude, longitude, southwest_latitude, southwest_longitude, northeast_latitude, northeast_longitude, - geocoding_date, geocoding_calls) + geocoding_date, geocoding_calls, postal_code_fr) VALUES ({?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, - {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, NOW(), {?})', + {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, NOW(), {?}, {?})', $this->pid, $this->jobid, $this->groupid, $this->type, $this->id, $this->flags, $this->text, $this->postalText, $this->pub, $this->comment, $this->types, $this->formatted_address, $this->location_type, $this->partial_match, $this->latitude, $this->longitude, - $this->southwest_latitude, $this->southwest_longitude, $this->northeast_latitude, $this->northeast_longitude, $this->geocoding_calls); + $this->southwest_latitude, $this->southwest_longitude, $this->northeast_latitude, $this->northeast_longitude, $this->geocoding_calls, $this->postal_code_fr); if ($this->componentsIds) { foreach (explode(',', $this->componentsIds) as $component_id) { diff --git a/include/userset.inc.php b/include/userset.inc.php index 0b3b978..9d8d7df 100644 --- a/include/userset.inc.php +++ b/include/userset.inc.php @@ -433,20 +433,12 @@ class AddressesView implements PlView $res = XDB::query("SELECT pd.promo, p.title, IF (pn.firstname_ordinary = '', UPPER(pn.firstname_main), UPPER(pn.firstname_ordinary)) AS firstname, IF (pn.lastname_ordinary = '', UPPER(pn.lastname_main), UPPER(pn.lastname_ordinary)) AS lastname, - UPPER(pje.name), pa.postalText, pa.long_name AS postal_code, p.email_directory - FROM (SELECT pa.pid, pa.postalText, pa.jobid, pa.groupid, pa.type, pa.id, pace.long_name - FROM profile_addresses AS pa - LEFT JOIN profile_addresses_components AS pac ON (pa.pid = pac.pid - AND pa.jobid = pac.jobid - AND pa.groupid = pac.groupid - AND pa.type = pac.type - AND pa.id = pac.id) - LEFT JOIN profile_addresses_components_enum AS pace ON (pac.component_id = pace.id - AND FIND_IN_SET('postal_code', pace.types)) - WHERE pa.pub IN ('public', 'ax') AND FIND_IN_SET('mail', pa.flags) AND pa.pid IN {?} - ORDER BY pa.pid, NOT FIND_IN_SET('current', pa.flags), - FIND_IN_SET('secondary', pa.flags), pa.type = 'job', - pace.long_name IS NULL) AS pa + UPPER(pje.name), pa.postalText, pa.postal_code_fr AS postal_code, p.email_directory + FROM (SELECT pid, postalText, jobid, groupid, type, id, postal_code_fr + FROM profile_addresses + WHERE pub IN ('public', 'ax') AND FIND_IN_SET('mail', flags) AND pid IN {?} + ORDER BY pid, NOT FIND_IN_SET('current', flags), + FIND_IN_SET('secondary', flags), type = 'job') AS pa INNER JOIN profiles AS p ON (pa.pid = p.pid) INNER JOIN profile_display AS pd ON (pd.pid = pa.pid) INNER JOIN profile_public_names AS pn ON (pn.pid = pa.pid) diff --git a/upgrade/1.1.5/04_addresses.sql b/upgrade/1.1.5/04_addresses.sql new file mode 100644 index 0000000..c4afed5 --- /dev/null +++ b/upgrade/1.1.5/04_addresses.sql @@ -0,0 +1,3 @@ +ALTER TABLE profile_addresses ADD COLUMN postal_code_fr CHAR(5) DEFAULT NULL; + +-- vim:set syntax=mysql: -- 2.1.4