X-Git-Url: http://git.polytechnique.org/?a=blobdiff_plain;f=upgrade%2Fnewdirectory-0.0.1%2Faddresses.php;h=01193d82249c4e706e43074b7bea2f3f9ac59396;hb=c67b874fd8d442bfded35295a819e7ce11a2d6dc;hp=0b659e6d93cde001048c2261dfff4234a5fcdcc2;hpb=1dc71da137a384e771723b8a893539a7f5cc9fab;p=platal.git diff --git a/upgrade/newdirectory-0.0.1/addresses.php b/upgrade/newdirectory-0.0.1/addresses.php index 0b659e6..01193d8 100755 --- a/upgrade/newdirectory-0.0.1/addresses.php +++ b/upgrade/newdirectory-0.0.1/addresses.php @@ -5,18 +5,19 @@ require_once 'geocoding.inc.php'; $globals->debug = 0; // Do not store backtraces. -$res = XDB::query('SELECT MIN(user_id), MAX(user_id) - FROM auth_user_md5'); +$res = XDB::query('SELECT MIN(pid), MAX(pid) + FROM profiles'); $pids = $res->fetchOneRow(); $minPid = $pids[0]; $maxPid = $pids[1]; -echo "This will take a few minutes.\n". +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, @@ -28,106 +29,78 @@ for ($pid = $minPid; $pid < $maxPid + 1; ++$pid) { FIND_IN_SET('active', a.statut) AS current, FIND_IN_SET('pro', a.statut) AS pro, a.glat AS precise_lat, a.glng AS precise_lon - FROM adresses AS a - INNER JOIN geoloc_pays AS gp ON(gp.a2 = a.country) - WHERE uid = {?} - ORDER BY adrid", + FROM #x4dat#.adresses AS a + INNER JOIN #x4dat#.geoloc_pays AS gp ON (gp.a2 = a.country) + INNER JOIN account_profiles AS ap ON (a.uid = ap.uid AND FIND_IN_SET('owner', ap.perms)) + WHERE ap.pid = {?} + ORDER BY a.adrid", $pid); while ($address = $res->next()) { $text = get_address_text($address); - XDB::iterator('UPDATE profile_addresses - SET text = {?} - WHERE pid = {?} AND type = {?} AND id = {?}', - $text, $pid, $address['pro'] ? 'job' : 'home', $address['id']); + XDB::execute('UPDATE profile_addresses + SET text = {?} + WHERE pid = {?} AND type = {?} AND id = {?}', + $text, $pid, $address['pro'] ? 'job' : 'home', $address['id']); } -} - -echo "Filling the 'text' filles is over. Geocoding will start now and will take a few days\n"; -// Tries to geocode all the addresses. -for ($pid = $minPid; $pid < $maxPid + 1; ++$pid) { - $res = XDB::iterator('SELECT * - FROM profile_addresses - WHERE pid = {?}', - $pid); + // Then deals with job addresses (located in #x4dat#.entreprises). + $res = XDB::iterator("SELECT e.entrid AS id, j.id AS jobid, 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)) + INNER JOIN profile_job_enum AS j ON (e.entreprise = j.name) + WHERE ap.pid = {?} + ORDER BY e.entrid", + $pid); while ($address = $res->next()) { - $updateTime = $address['updateTime']; - $gmapsGeocoder = new GMapsGeocoder(); - $address = $gmapsGeocoder->getGeocodedAddress($address); - - if (!isset($address['geoloc'])) { - // TODO: use address and phone classes to update profile_job_enum and profile_phones once they are done. - - XDB::execute('DELETE FROM profile_addresses - WHERE pid = {?} AND id = {?} AND type = {?}', - $address['pid'], $address['id'], $address['type']); - - Geocoder::getAreaId($address, 'administrativeArea'); - Geocoder::getAreaId($address, 'subAdministrativeArea'); - Geocoder::getAreaId($address, 'locality'); - XDB::execute('INSERT INTO profile_addresses (pid, type, id, flags, accuracy, - text, postalText, postalCode, localityId, - subAdministrativeAreaId, administrativeAreaId, - countryId, latitude, longitude, updateTime, pub, comment, - north, south, east, west) - VALUES ({?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, - {?}, {?}, FROM_UNIXTIME({?}), {?}, {?}, {?}, {?}, {?}, {?})', - $address['pid'], $address['type'], $address['id'], $flags, $address['accuracy'], - $address['text'], $address['postalText'], $address['postalCode'], $address['localityId'], - $address['subAdministrativeAreaId'], $address['administrativeAreaId'], - $address['countryId'], $address['latitude'], $address['longitude'], - $updateTime, $address['pub'], $address['comment'], - $address['north'], $address['south'], $address['east'], $address['west']); - } else { - XDB::execute('UPDATE profile_addresses - SET postalText = {?} - WHERE pid = {?} AND id = {?} AND type = {?}', - $address['postalText'], $address['pid'], $address['id'], $address['type']); - } - - sleep(60); // So we don't get blacklisted by Google. + $text = get_address_text($address); + XDB::execute('UPDATE profile_addresses + SET text = {?} + WHERE pid = {?} AND type = {?} AND id = {?} AND jobid = {?}', + $text, $pid, 'job', $address['id'], $address['jobid']); } } -echo "Geocoding is over.\n"; - function get_address_text($adr) { - $t = ""; - if (isset($adr['adr1']) && $adr['adr1']) $t.= $adr['adr1']; - if (isset($adr['adr2']) && $adr['adr2']) $t.= "\n".$adr['adr2']; - if (isset($adr['adr3']) && $adr['adr3']) $t.= "\n".$adr['adr3']; - $l = ""; + $t = ''; + if (isset($adr['adr1']) && $adr['adr1']) $t .= $adr['adr1']; + if (isset($adr['adr2']) && $adr['adr2']) $t .= "\n".$adr['adr2']; + if (isset($adr['adr3']) && $adr['adr3']) $t .= "\n".$adr['adr3']; + $l = ''; if (isset($adr['display']) && $adr['display']) { $keys = explode(' ', $adr['display']); foreach ($keys as $key) { if (isset($adr[$key])) { - $l .= " ".$adr[$key]; + $l .= ' ' . $adr[$key]; } else { - $l .= " ".$key; + $l .= ' ' . $key; } } if ($l) substr($l, 1); } elseif ($adr['country'] == 'US' || $adr['country'] == 'CA' || $adr['country'] == 'GB') { - if ($adr['city']) $l .= $adr['city'].",\n"; - if ($adr['region']) $l .= $adr['region']." "; + if ($adr['city']) $l .= $adr['city'] . ",\n"; + if ($adr['region']) $l .= $adr['region'] . ' '; if ($adr['postcode']) $l .= $adr['postcode']; } else { - if (isset($adr['postcode']) && $adr['postcode']) $l .= $adr['postcode']." "; + if (isset($adr['postcode']) && $adr['postcode']) $l .= $adr['postcode'] . ' '; if (isset($adr['city']) && $adr['city']) $l .= $adr['city']; } - if ($l) $t .= "\n".trim($l); + if ($l) $t .= "\n" . trim($l); if ($adr['country'] != '00' && (!$adr['countrytxt'] || $adr['countrytxt'] == strtoupper($adr['countrytxt']))) { - $res = XDB::query('SELECT pays - FROM geoloc_pays - WHERE a2 = {?}', + $res = XDB::query('SELECT countryFR + FROM geoloc_countries + WHERE iso_3166_1_a2 = {?}', $adr['country']); $adr['countrytxt'] = $res->fetchOneCell(); } if (isset($adr['countrytxt']) && $adr['countrytxt']) { - $t .= "\n".$adr['countrytxt']; + $t .= "\n" . $adr['countrytxt']; } return trim($t); }