upgrade/newdirectory-0.0.1/addresses.php

   1 #!/usr/bin/php5
   2 <?php
   3 require_once 'connect.db.inc.php';
   4 require_once 'geocoding.inc.php';
   5
   6 $globals->debug = 0; // Do not store backtraces.
   7
   8 $res = XDB::query('SELECT  MIN(pid), MAX(pid)
   9                      FROM  profiles');
  10
  11 $pids = $res->fetchOneRow();
  12
  13 $minPid = $pids[0];
  14 $maxPid = $pids[1];
  15
  16 echo "This will take a few minutes.\n";
  17
  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))
  35                             WHERE  ap.pid = {?}
  36                          ORDER BY  a.adrid",
  37                            $pid);
  38
  39     while ($address = $res->next()) {
  40         $text = get_address_text($address);
  41         XDB::execute('UPDATE  profile_addresses
  42                          SET  text = {?}
  43                        WHERE  pid = {?} AND type = {?} AND id = {?}',
  44                      $text, $pid, $address['pro'] ? 'job' : 'home', $address['id']);
  45     }
  46
  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)
  56                             WHERE  ap.pid = {?}
  57                          ORDER BY  e.entrid",
  58                            $pid);
  59
  60     while ($address = $res->next()) {
  61         $text = get_address_text($address);
  62         XDB::execute('UPDATE  profile_addresses
  63                          SET  text = {?}
  64                        WHERE  pid = {?} AND type = {?} AND id = {?} AND jobid = {?}',
  65                      $text, $pid, 'job', $address['id'], $address['jobid']);
  66     }
  67 }
  68
  69 function get_address_text($adr)
  70 {
  71     $t = '';
  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'];
  75     $l = '';
  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];
  81             } else {
  82                 $l .= ' ' . $key;
  83             }
  84         }
  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'];
  90     } else {
  91         if (isset($adr['postcode']) && $adr['postcode']) $l .= $adr['postcode'] . ' ';
  92         if (isset($adr['city']) && $adr['city']) $l .= $adr['city'];
  93     }
  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
  97                              FROM  geoloc_countries
  98                             WHERE  iso_3166_1_a2 = {?}',
  99                           $adr['country']);
 100         $adr['countrytxt'] = $res->fetchOneCell();
 101     }
 102     if (isset($adr['countrytxt']) && $adr['countrytxt']) {
 103         $t .= "\n" . $adr['countrytxt'];
 104     }
 105     return trim($t);
 106 }
 107
 108 /* vim:set et sw=4 sts=4 ts=4: */
 109 ?>