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