upgrade/1.0.1/merge_issues.php

   1 #!/usr/bin/php5
   2 <?php
   3 // WARNING: this script takes a few minutes to be executed completly, thus run it into a screen.
   4
   5 require_once 'connect.db.inc.php';
   6 require_once '../../classes/phone.php';
   7 require_once '../../classes/address.php';
   8 require_once '../../classes/visibility.php';
   9
  10 $globals->debug = 0; // Do not store backtraces.
  11
  12 $abbreviations = array(
  13     'commandant'    => 'cdt',
  14     'docteur'       => 'dr',
  15     'haut'          => 'ht',
  16     'haute'         => 'ht',
  17     'hauts'         => 'ht',
  18     'hts'           => 'ht',
  19     'general'       => 'gen',
  20     'gal '          => 'gen ',
  21     'grand'         => 'gd',
  22     'grande'        => 'gd',
  23     'grands'        => 'gd',
  24     'gde '          => 'gd ',
  25     'gds '          => 'gd ',
  26     'lieutenant'    => 'lt',
  27     'marechal'      => 'mal',
  28     'notre dame'    => 'n d',
  29     'nouveau'       => 'nouv',
  30     'president'     => 'pdt',
  31     'saint'         => 'st',
  32     'sainte'        => 'st',
  33     'saintes'       => 'st',
  34     'saints'        => 'st',
  35     'ste '          => 'st ',
  36     'appartement'   => 'app',
  37     'apt'           => 'app',
  38     'appt'          => 'app',
  39     'appart'        => 'app',
  40     'arrondissement'=> 'arr',
  41     'batiment'      => 'bat',
  42     'escalier'      => 'esc',
  43     'etage'         => 'etg',
  44     'et '           => 'etg',
  45     'immeuble'      => 'imm',
  46     'lieu dit'      => 'ld',
  47     ' lt '          => ' lt ',
  48     'porte'         => 'pte',
  49     'quartier'      => 'quart',
  50     'residence'     => 'res',
  51     'resi'          => 'res',
  52     'villa'         => 'vla',
  53     'village'       => 'vlge',
  54     'vil '          => 'vlge ',
  55     'allee'         => 'all',
  56     'avenue'        => 'av',
  57     'boulevard'     => 'bd',
  58     'bld'           => 'bd',
  59     'chemin'        => 'ch',
  60     'chem '         => 'ch ',
  61     'che '          => 'ch ',
  62     'cours'         => 'crs',
  63     'domaine'       => 'dom',
  64     'doma '         => 'dom ',
  65     'faubourg'      => 'fg',
  66     'fbg'           => 'fg',
  67     'hameau'        => 'ham',
  68     'hame '         => 'ham ',
  69     'impasse'       => 'imp',
  70     'impa '         => 'imp ',
  71     'lotissement'   => 'lot',
  72     'montee'        => 'mte',
  73     'passage'       => 'pass',
  74     'place'         => 'pl',
  75     'promenade'     => 'pro ',
  76     'prom '         => 'pro ',
  77     'quai'          => 'qu',
  78     'rue'           => 'r',
  79     'route'         => 'rte',
  80     ' rde '         => ' rte ',
  81     ' rle '         => ' rte ',
  82     'sentier'       => 'sen',
  83     'sent '         => 'sen ',
  84     'square'        => 'sq',
  85     'mount'         => 'mt',
  86     'road'          => 'rd',
  87     'street'        => 'st',
  88     'str '          => 'str',
  89     'bis'           => 'b',
  90     'ter'           => 't'
  91 );
  92 $patterns = array();
  93 $replacements = array();
  94 foreach ($abbreviations as $key => $abbreviation) {
  95     $patterns[] = '/' . $key . '/';
  96     $replacements[] = $abbreviation;
  97 }
  98
  99 function check($address1, $address2)
 100 {
 101     return $address1['short'] == $address2['short'] || $address1['short'] == $address2['long']
 102         || $address1['long'] == $address2['short'] || $address1['long'] == $address2['long'];
 103 }
 104
 105 print "Deletes duplicated addresses. (1/4)\n";
 106 $pids = XDB::rawFetchColumn("SELECT  DISTINCT(pid)
 107                                FROM  profile_addresses AS a1
 108                               WHERE  type = 'home' AND EXISTS (SELECT  *
 109                                                                  FROM  profile_addresses AS a2
 110                                                                 WHERE  a2.type = 'home' AND a2.pid = a1.pid AND a2.id != a1.id)
 111                            ORDER BY  pid");
 112 $total = count($pids);
 113 $done = 0;
 114 $aux = 0;
 115 $deleted = 0;
 116 $addresses = array();
 117 $rawAddresses = array();
 118 $duplicates = array();
 119 foreach ($pids as $pid) {
 120     $count = 0;
 121     $it = Address::iterate(array($pid), array(Address::LINK_PROFILE), array(0), Visibility::get(Visibility::VIEW_PRIVATE));
 122     while ($item = $it->next()) {
 123         $addresses[$count] = $item;
 124         $rawAddress = preg_replace('/[^a-z0-9]/', ' ', mb_strtolower(replace_accent($item->text)));
 125         $rawAddresses[$count] = array(
 126             'long'  => preg_replace('/\s+/', '', $rawAddress),
 127             'short' => preg_replace('/\s+/', '', preg_replace($patterns, $replacements, $rawAddress)),
 128         );
 129         ++$count;
 130     }
 131     for ($i = 0; $i < $count; ++$i) {
 132         for ($j = $i + 1; $j < $count; ++$j) {
 133             if (check($rawAddresses[$i], $rawAddresses[$j])) {
 134                 $duplicates[$j] = true;
 135                 if (Visibility::isLessRestrictive($addresses[$i]->pub, $addresses[$j]->pub)) {
 136                     $addresses[$i]->pub = $addresses[$j]->pub;
 137                 }
 138                 if ($addresses[$j]->hasFlag('mail') && !$addresses[$i]->hasFlag('mail')) {
 139                     $addresses[$i]->addFlag('mail');
 140                 }
 141             }
 142         }
 143     }
 144     if (count($duplicates)) {
 145         foreach ($duplicates as $key => $bool) {
 146             unset($addresses[$key]);
 147         }
 148     }
 149     if (count($addresses) != $count) {
 150         $deleted += ($count - count($addresses));
 151         Address::deleteAddresses($pid, 'home');
 152         $id = 0;
 153         foreach ($addresses as $address) {
 154             $address->setId($id);
 155             $address->save();
 156             ++$id;
 157         }
 158         XDB::execute('UPDATE IGNORE  profile_merge_issues
 159                                 SET  issues = REPLACE(issues, \'address\', \'\')
 160                               WHERE  pid = {?}', $pid);
 161     }
 162     unset($rawAddresses);
 163     unset($addresses);
 164     unset($duplicates);
 165
 166     ++$done;
 167     ++$aux;
 168     if ($aux == 100) {
 169         $aux = 0;
 170         printf("\r%u / %u",  $done, $total);
 171     }
 172 }
 173 printf("\r%u / %u",  $done, $total);
 174 print "\n$deleted addresses deleted.\n\n";
 175
 176 print "Formats erroneous addresses phones. (3/4)\n";
 177 $it = Address::iterate(array(), array(), array(), Visibility::get(Visibility::VIEW_PRIVATE));
 178 $total = $it->total();
 179 $i = 0;
 180 $skipped = 0;
 181 $fixed = 0;
 182 printf("\r%u / %u",  $i, $total);
 183 while ($address = $it->next()) {
 184     $fix = preg_replace('/\s+r(\.|)\s+/i', ' rue ', $address->text);
 185     if ($fix != $address->text) {
 186         $address->changed = 0;
 187         $address->text = $fix;
 188         $address->format();
 189         if ($address->delete()) {
 190             $address->save(false);
 191             ++$fixed;
 192         } else {
 193             ++$skipped;
 194         }
 195     }
 196
 197     ++$i;
 198     printf("\r%u / %u",  $i, $total);
 199 }
 200 printf("\r%u / %u",  $i, $total);
 201 if ($skipped != 0) {
 202     printf("\n%u addresses skipped.\n", $skipped);
 203 }
 204 if ($fixed != 0) {
 205     printf("\n%u addresses fixed.\n", $fixed);
 206 }
 207 print "\nAddress formating done.\n\n";
 208
 209 print "Formats non formated phones. (3/4)\n";
 210 $it = XDB::rawIterator("SELECT  search_tel AS search, display_tel AS display, comment, link_id,
 211                                 tel_type AS type, link_type, tel_id AS id, pid, pub
 212                           FROM  profile_phones
 213                          WHERE  search_tel = '' OR search_tel IS NULL
 214                       ORDER BY  pid, link_id, tel_id");
 215 $total = $it->total();
 216 $i = 0;
 217 $j = 0;
 218 while ($item = $it->next()) {
 219     $phone = new Phone($item);
 220     $phone->delete();
 221     $phone->save();
 222
 223     ++$i;
 224     ++$j;
 225     if ($j == 100) {
 226         $j = 0;
 227         printf("\r%u / %u",  $i, $total);
 228     }
 229 }
 230 printf("\r%u / %u",  $i, $total);
 231 print "\nFormating done.\n\n";
 232
 233 print "Deletes duplicated phones. (4/4)\n";
 234 $pids = XDB::rawFetchColumn("SELECT  DISTINCT(pid)
 235                                FROM  profile_phones AS p1
 236                               WHERE  link_type = 'user' AND EXISTS (SELECT  *
 237                                                                       FROM  profile_phones AS p2
 238                                                                      WHERE  p2.link_type = 'user' AND p2.pid = p1.pid AND p2.tel_id != p1.tel_id)
 239                            ORDER BY  pid");
 240 $total = count($pids);
 241 $done = 0;
 242 $aux = 0;
 243 $deleted = 0;
 244 $phones = array();
 245 $duplicates = array();
 246 foreach ($pids as $pid) {
 247     $count = 0;
 248     $it = Phone::iterate(array($pid), array(Phone::LINK_PROFILE), array(0), Visibility::get(Visibility::VIEW_PRIVATE));
 249     while ($item = $it->next()) {
 250         $phones[] = $item;
 251         ++$count;
 252     }
 253     for ($i = 0; $i < $count; ++$i) {
 254         for ($j = $i + 1; $j < $count; ++$j) {
 255             if ($phones[$i]->search == $phones[$j]->search) {
 256                 $duplicates[$j] = true;
 257                 if (Visibility::isLessRestrictive($phones[$i]->pub, $phones[$j]->pub)) {
 258                     $phones[$i]->pub = $phones[$j]->pub;
 259                 }
 260             }
 261         }
 262     }
 263     if (count($duplicates)) {
 264         foreach ($duplicates as $key => $bool) {
 265             unset($phones[$key]);
 266         }
 267     }
 268     if (count($phones) != $count) {
 269         $deleted += ($count - count($phones));
 270         Phone::deletePhones($pid, 'user');
 271         $id = 0;
 272         foreach ($phones as $phone) {
 273             $phone->setId($id);
 274             $phone->save();
 275             ++$id;
 276         }
 277         XDB::execute('UPDATE IGNORE  profile_merge_issues
 278                                 SET  issues = REPLACE(issues, \'phone\', \'\')
 279                               WHERE  pid = {?}', $pid);
 280     }
 281     unset($duplicates);
 282     unset($phones);
 283
 284     ++$done;
 285     ++$aux;
 286     if ($aux == 10) {
 287         $aux = 0;
 288         printf("\r%u / %u",  $done, $total);
 289     }
 290 }
 291 printf("\r%u / %u",  $done, $total);
 292 print "\n$deleted phones deleted.\n\n";
 293
 294 print "That's all folks!\n";
 295
 296 /* vim:set et sw=4 sts=4 ts=4: */
 297 ?>