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/3)\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 non formated phones. (2/3)\n";
 177 $it = XDB::rawIterator("SELECT  search_tel AS search, display_tel AS display, comment, link_id,
 178                                 tel_type AS type, link_type, tel_id AS id, pid, pub
 179                           FROM  profile_phones
 180                          WHERE  search_tel = '' OR search_tel IS NULL
 181                       ORDER BY  pid, link_id, tel_id");
 182 $total = $it->total();
 183 $i = 0;
 184 $j = 0;
 185 while ($item = $it->next()) {
 186     $phone = new Phone($item);
 187     $phone->delete();
 188     $phone->save();
 189
 190     ++$i;
 191     ++$j;
 192     if ($j == 100) {
 193         $j = 0;
 194         printf("\r%u / %u",  $i, $total);
 195     }
 196 }
 197 printf("\r%u / %u",  $i, $total);
 198 print "\nFormating done.\n\n";
 199
 200 print "Deletes duplicated phones. (3/3)\n";
 201 $pids = XDB::rawFetchColumn("SELECT  DISTINCT(pid)
 202                                FROM  profile_phones AS p1
 203                               WHERE  link_type = 'user' AND EXISTS (SELECT  *
 204                                                                       FROM  profile_phones AS p2
 205                                                                      WHERE  p2.link_type = 'user' AND p2.pid = p1.pid AND p2.tel_id != p1.tel_id)
 206                            ORDER BY  pid");
 207 $total = count($pids);
 208 $done = 0;
 209 $aux = 0;
 210 $deleted = 0;
 211 $phones = array();
 212 $duplicates = array();
 213 foreach ($pids as $pid) {
 214     $count = 0;
 215     $it = Phone::iterate(array($pid), array(Phone::LINK_PROFILE), array(0), Visibility::get(Visibility::VIEW_PRIVATE));
 216     while ($item = $it->next()) {
 217         $phones[] = $item;
 218         ++$count;
 219     }
 220     for ($i = 0; $i < $count; ++$i) {
 221         for ($j = $i + 1; $j < $count; ++$j) {
 222             if ($phones[$i]->search == $phones[$j]->search) {
 223                 $duplicates[$j] = true;
 224                 if (Visibility::isLessRestrictive($phones[$i]->pub, $phones[$j]->pub)) {
 225                     $phones[$i]->pub = $phones[$j]->pub;
 226                 }
 227             }
 228         }
 229     }
 230     if (count($duplicates)) {
 231         foreach ($duplicates as $key => $bool) {
 232             unset($phones[$key]);
 233         }
 234     }
 235     if (count($phones) != $count) {
 236         $deleted += ($count - count($phones));
 237         Phone::deletePhones($pid, 'user');
 238         $id = 0;
 239         foreach ($phones as $phone) {
 240             $phone->setId($id);
 241             $phone->save();
 242             ++$id;
 243         }
 244         XDB::execute('UPDATE IGNORE  profile_merge_issues
 245                                 SET  issues = REPLACE(issues, \'phone\', \'\')
 246                               WHERE  pid = {?}', $pid);
 247     }
 248     unset($duplicates);
 249     unset($phones);
 250
 251     ++$done;
 252     ++$aux;
 253     if ($aux == 10) {
 254         $aux = 0;
 255         printf("\r%u / %u",  $done, $total);
 256     }
 257 }
 258 printf("\r%u / %u",  $done, $total);
 259 print "\n$deleted phones deleted.\n\n";
 260
 261 print "That's all folks!\n";
 262
 263 /* vim:set et sw=4 sts=4 ts=4: */
 264 ?>