3 require_once 'connect.db.inc.php';
4 require_once 'geocoding.inc.php';
6 $globals->debug
= 0; // Do not store backtraces.
8 $res = XDB
::query('SELECT MIN(pid), MAX(pid)
11 $pids = $res->fetchOneRow();
16 echo "This will take a few minutes.\n";
18 // Fills the 'text' field in profile_addresses.
19 for ($pid = $minPid; $pid < $maxPid +
1; ++
$pid) {
20 // First deals with home addresses (located in #x4dat#.adresses).
21 $res = XDB
::iterator("SELECT a.adrid AS id, a.adr1, a.adr2, a.adr3,
22 UNIX_TIMESTAMP(a.datemaj) AS datemaj,
23 a.postcode, a.city, a.cityid, a.region, a.regiontxt,
24 a.pub, a.country, gp.pays AS countrytxt, gp.display,
25 FIND_IN_SET('coord-checked', a.statut) AS checked,
26 FIND_IN_SET('res-secondaire', a.statut) AS secondaire,
27 FIND_IN_SET('courrier', a.statut) AS mail,
28 FIND_IN_SET('temporaire', a.statut) AS temporary,
29 FIND_IN_SET('active', a.statut) AS current,
30 FIND_IN_SET('pro', a.statut) AS pro,
31 a.glat AS precise_lat, a.glng AS precise_lon
32 FROM #x4dat#.adresses AS a
33 INNER JOIN #x4dat#.geoloc_pays AS gp ON (gp.a2 = a.country)
34 INNER JOIN account_profiles AS ap ON (a.uid = ap.uid AND FIND_IN_SET('owner', ap.perms))
39 while ($address = $res->next()) {
40 $text = get_address_text($address);
41 XDB
::execute('UPDATE profile_addresses
43 WHERE pid = {?} AND type = {?} AND id = {?}',
44 $text, $pid, $address['pro'] ?
'job' : 'home', $address['id']);
47 // Then deals with job addresses (located in #x4dat#.entreprises).
48 $res = XDB
::iterator("SELECT e.entrid AS id, j.id AS jobid, e.adr1, e.adr2, e.adr3,
49 e.postcode, e.city, e.cityid, e.region, e.regiontxt,
50 e.adr_pub AS pub, e.country, gp.pays AS countrytxt, gp.display,
51 e.glat AS precise_lat, e.glng AS precise_lon
52 FROM #x4dat#.entreprises AS e
53 INNER JOIN #x4dat#.geoloc_pays AS gp ON (gp.a2 = e.country)
54 INNER JOIN account_profiles AS ap ON (e.uid = ap.uid AND FIND_IN_SET('owner', ap.perms))
55 INNER JOIN profile_job_enum AS j ON (e.entreprise = j.name)
60 while ($address = $res->next()) {
61 $text = get_address_text($address);
62 XDB
::execute('UPDATE profile_addresses
64 WHERE pid = {?} AND type = {?} AND id = {?} AND jobid = {?}',
65 $text, $pid, 'job', $address['id'], $address['jobid']);
69 function get_address_text($adr)
72 if (isset($adr['adr1']) && $adr['adr1']) $t .= $adr['adr1'];
73 if (isset($adr['adr2']) && $adr['adr2']) $t .= "\n".$adr['adr2'];
74 if (isset($adr['adr3']) && $adr['adr3']) $t .= "\n".$adr['adr3'];
76 if (isset($adr['display']) && $adr['display']) {
77 $keys = explode(' ', $adr['display']);
78 foreach ($keys as $key) {
79 if (isset($adr[$key])) {
80 $l .= ' ' . $adr[$key];
85 if ($l) substr($l, 1);
86 } elseif ($adr['country'] == 'US' ||
$adr['country'] == 'CA' ||
$adr['country'] == 'GB') {
87 if ($adr['city']) $l .= $adr['city'] . ",\n";
88 if ($adr['region']) $l .= $adr['region'] . ' ';
89 if ($adr['postcode']) $l .= $adr['postcode'];
91 if (isset($adr['postcode']) && $adr['postcode']) $l .= $adr['postcode'] . ' ';
92 if (isset($adr['city']) && $adr['city']) $l .= $adr['city'];
94 if ($l) $t .= "\n" . trim($l);
95 if ($adr['country'] != '00' && (!$adr['countrytxt'] ||
$adr['countrytxt'] == strtoupper($adr['countrytxt']))) {
96 $res = XDB
::query('SELECT countryFR
98 WHERE iso_3166_1_a2 = {?}',
100 $adr['countrytxt'] = $res->fetchOneCell();
102 if (isset($adr['countrytxt']) && $adr['countrytxt']) {
103 $t .= "\n" . $adr['countrytxt'];
108 /* vim:set et sw=4 sts=4 ts=4: */