bin/cron/phones.check.php

   1 #!/usr/bin/php5
   2 <?php
   3
   4 require './connect.db.inc.php';
   5
   6 $globals->debug = 0; // do not store backtraces
   7 // TODO: Improve this using Phone::iterate.
   8
   9 function do_update_by_block($values)
  10 {
  11     // Update display_tel by block
  12     // Because there is no mysql update syntax for multiple updates in one query
  13     // we use a multiple insert syntax which will fail because the key already exist
  14     // and then update the display_tel
  15     XDB::execute("INSERT INTO  profile_phones (pid, link_type, link_id, tel_id ,tel_type,
  16                                           search_tel, display_tel, pub, comment)
  17                        VALUES  " . $values . "
  18       ON DUPLICATE KEY UPDATE  display_tel = VALUES(display_tel)");
  19 }
  20
  21 $res = XDB::query("SELECT DISTINCT  phonePrefix
  22                               FROM  geoloc_countries
  23                              WHERE  phonePrefix IS NOT NULL");
  24 $prefixes = $res->fetchColumn();
  25 foreach ($prefixes as $i => $prefix) {
  26     $res = XDB::query("SELECT  phoneFormat
  27                          FROM  geoloc_countries
  28                         WHERE  phonePrefix = {?} AND phoneFormat != '' LIMIT 1",
  29                       $prefix);
  30     if ($res->numRows() > 0) {
  31         $format = $res->fetchOneCell();
  32         //Build regexp for mysql query
  33         $len = strlen($format);
  34         $regexp = "^";
  35         $nbPar = 0;
  36         for ($i = 0; $i < $len; $i++) {
  37             $char = $format[$i];
  38             switch ($char) {
  39             case 'p':
  40                 $regexp .= $prefix;
  41                 break;
  42             case '#':
  43                 if ($nbPar == 0) {
  44                     $regexp .= '(';
  45                     $nbPar++;
  46                 }
  47                 $regexp .= '[0-9](';
  48                 $nbPar++;
  49                 break;
  50             default:
  51                 //Appends the char after escaping it if necessary
  52                 $escape = array('[', ']', '{', '}', '(', ')', '*', '+', '?', '.', '^', '$', '|', '\\');
  53                 if (in_array($char, $escape)) {
  54                     $regexp .= '[' . $char . ']';
  55                 } else {
  56                     $regexp .= $char;
  57                 }
  58             }
  59         }
  60         //allows additionnal spaces and numbers
  61         $regexp .= '[0-9 ]*';
  62         //closes parenthesis
  63         for ($i = 0; $i < $nbPar; $i++) {
  64             $regexp .= ')?';
  65         }
  66         $regexp .= '$';
  67         $res = XDB::iterator("SELECT pid, link_type, link_id, tel_id, tel_type, search_tel,
  68                                      display_tel, pub, comment
  69                                 FROM profile_phones
  70                                WHERE search_tel LIKE {?} AND display_tel NOT REGEXP {?}",
  71                              $prefix . '%', $regexp);
  72         if ($res->numRows() > 0)
  73         {
  74             //To speed up the update of phone numbers, theses updates are grouped by block of 1000
  75             $values = '';
  76             $i = 0;
  77             while ($phone = $res->next()) {
  78                 $phone = new Phone(array('display' => $phone['display_tel']));
  79                 $phone->format(array('format' => $format, 'phoneprf' => $prefix));
  80                 if ($values != '') {
  81                     $values .= ",\n";
  82                 }
  83                 $values .= "('"   . addslashes($phone['pid']) . "', '" . addslashes($phone['link_type'])
  84                     . "', '" . addslashes($phone['link_id'])
  85                     . "', '" . addslashes($phone['tel_id']) . "', '" . addslashes($phone['tel_type'])
  86                     . "', '" . addslashes($phone['search_tel']) . "', '" . addslashes($phone->display)
  87                     . "', '" . addslashes($phone['pub']) . "', '" . addslashes($phone['comment']) . "')";
  88                 $i++;
  89                 if ($i == 1000) {
  90                     do_update_by_block($values);
  91                     $values = '';
  92                     $i = 0;
  93                 }
  94             }
  95             if ($values != '') {
  96                 do_update_by_block($values);
  97             }
  98         }
  99     }
 100 }
 101
 102 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker fenc=utf-8:
 103 ?>