upgrade/newdirectory-0.0.1/phones.php

   1 #!/usr/bin/php5
   2 <?php
   3 require_once 'connect.db.inc.php';
   4 require_once 'profil.func.inc.php';
   5
   6 $globals->debug = 0; //do not store backtraces
   7
   8 // Convert phone prefixes from varchar to int
   9 $prefixes = XDB::iterRow('SELECT  iso_3166_1_a2, phonePrefix
  10                             FROM  geoloc_countries
  11                            WHERE  phonePrefix IS NOT NULL');
  12 XDB::execute('ALTER TABLE  geoloc_countries
  13                ADD COLUMN  tmpPhonePrefix SMALLINT UNSIGNED NULL');
  14 while (list($id, $pref) = $prefixes->next()) {
  15     $pref = preg_replace('/[^0-9]/', '', $pref);
  16     if ($pref[0] == '1') {
  17         $pref = '1';
  18     }
  19     if ($pref[0] == '7') {
  20         $pref = '7';
  21     }
  22     if ($pref != '' && strlen($pref) < 4) {
  23         XDB::execute('UPDATE  geoloc_countries
  24                          SET  tmpPhonePrefix = {?}
  25                        WHERE  iso_3166_1_a2 = {?}', $pref, $id);
  26     }
  27 }
  28
  29 // geoloc_pays post operations
  30 // Drops old prefix column
  31 XDB::execute('ALTER TABLE  geoloc_countries
  32               DROP COLUMN  phonePrefix');
  33 // Renames temporary column
  34 XDB::execute('ALTER TABLE  geoloc_countries
  35             CHANGE COLUMN  tmpPhonePrefix phonePrefix SMALLINT UNSIGNED NULL AFTER nationality');
  36 // Adds an index on phonePrefix column
  37 XDB::execute('ALTER TABLE  geoloc_countries
  38                 ADD INDEX  (phonePrefix)');
  39 // Adds French phone prefix
  40 XDB::execute('UPDATE  geoloc_countries
  41                  SET  phonePrefix = \'33\'
  42                WHERE  iso_3166_1_a2 = \'FR\'');
  43 // Adds some phone formats
  44 XDB::execute('UPDATE  geoloc_countries
  45                  SET  phoneFormat = \'0# ## ## ## ##\'
  46                WHERE  phonePrefix = \'33\'');    //France
  47 XDB::execute('UPDATE  geoloc_countries
  48                  SET  phoneFormat = \'(+p) ### ### ####\'
  49                WHERE  phonePrefix = \'1\'');  //USA and NANP countries
  50
  51
  52
  53 //Phone number import
  54
  55 $warnings = 0;
  56
  57 // Import from auth_user_quick
  58 echo "\nImporting mobile phone numbers from auth_user_quick...\n";
  59 $phones = XDB::iterRow('SELECT  ap.pid, q.profile_mobile_pub, q.profile_mobile
  60                           FROM  #x4dat#.auth_user_quick AS q
  61                     INNER JOIN  account_profiles        AS ap ON (q.user_id = ap.uid AND FIND_IN_SET(\'owner\', ap.perms))
  62                          WHERE  q.profile_mobile <> \'\'');
  63 while (list($pid, $pub, $phone) = $phones->next()) {
  64     $fmt_phone = format_phone_number($phone);
  65     if ($fmt_phone != '') {
  66         $display = format_display_number($fmt_phone, $error);
  67         if (!XDB::execute('INSERT INTO  profile_phones (pid, link_type, link_id, tel_id, tel_type, search_tel, display_tel, pub)
  68                                 VALUES  ({?}, \'user\', 0, 0, \'mobile\', {?}, {?}, {?})', $pid, $fmt_phone, $display, $pub)) {
  69             echo "WARNING: insert of profile mobile phone number failed for profile $pid.\n";
  70             ++$warnings;
  71         }
  72     }
  73 }
  74
  75
  76 // Import from entreprises
  77 echo "\nImporting professional phone numbers from entreprises...\n";
  78 $phones = XDB::iterator('SELECT  ap.pid, e.entrid, e.tel, e.fax, e.mobile, e.tel_pub
  79                            FROM  #x4dat#.entreprises AS e
  80                      INNER JOIN  account_profiles    AS ap ON (e.uid = ap.uid AND FIND_IN_SET(\'owner\', ap.perms))
  81                        ORDER BY  ap.pid');
  82 while ($row = $phones->next()) {
  83     $request = 'INSERT INTO  profile_phones (pid, link_type, link_id, tel_id, tel_type, search_tel, display_tel, pub)
  84                      VALUES  ({?}, \'pro\', {?}, {?}, {?}, {?}, {?}, {?})';
  85     $fmt_fixed   = format_phone_number($row['tel']);
  86     $fmt_mobile  = format_phone_number($row['mobile']);
  87     $fmt_fax     = format_phone_number($row['fax']);
  88     if ($fmt_fixed != '') {
  89         $disp_fixed  = format_display_number($fmt_fixed, $error);
  90         if (!XDB::execute($request, $row['pid'], $row['entrid'], 0, 'fixed', $fmt_fixed, $disp_fixed, $row['tel_pub'])) {
  91             echo 'WARNING: insert of professional fixed phone number failed for profile ' . $row['pid'] . ' and entreprise ' . $row['entrid'] . ".\n";
  92             ++$warnings;
  93         }
  94     }
  95     if ($fmt_mobile != '') {
  96         $disp_mobile = format_display_number($fmt_mobile, $error);
  97         if (!XDB::execute($request, $row['pid'], $row['entrid'], 1, 'mobile', $fmt_mobile, $disp_mobile, $row['tel_pub'])) {
  98             echo 'WARNING: insert of professional mobile number failed for profile ' . $row['pid'] . ' and entreprise ' . $row['entrid'] . ".\n";
  99             $warnings++;
 100         }
 101     }
 102     if ($fmt_fax != '') {
 103         $disp_fax    = format_display_number($fmt_fax, $error);
 104         if (!XDB::execute($request, $row['pid'], $row['entrid'], 2, 'fax', $fmt_fax, $disp_fax, $row['tel_pub'])) {
 105             echo 'WARNING: insert of professional fax number failed for profile ' . $row['pid'] . ' and entreprise ' . $row['entrid'] . ".\n";
 106             $warnings++;
 107         }
 108     }
 109 }
 110
 111
 112 //import from tels
 113 echo "\nImporting personnal phone numbers from tels...\n";
 114 $phones = XDB::iterator('SELECT  ap.pid, t.adrid, t.telid, t.tel_type, t.tel_pub, t.tel
 115                            FROM  #x4dat#.tels     AS t
 116                      INNER JOIN  account_profiles AS ap ON (t.uid = ap.uid AND FIND_IN_SET(\'owner\', ap.perms))');
 117 $conversions = array();
 118 $other_count = 0;
 119 while ($row = $phones->next()) {
 120     $fmt_phone  = format_phone_number($row['tel']);
 121     if ($fmt_phone != '') {
 122         $display    = format_display_number($fmt_phone, $error);
 123         $guess_type = guess_phone_type($row['tel_type'], $fmt_phone);
 124
 125         switch ($guess_type) {
 126         case 'fixed':
 127         case 'fax':
 128         case 'mobile':
 129             if (!XDB::execute('INSERT  INTO profile_phones (pid, link_type, link_id, tel_id, tel_type, search_tel, display_tel, pub)
 130                                      VALUES ({?}, \'address\', {?}, {?}, {?}, {?}, {?}, {?})',
 131                               $row['pid'], $row['adrid'], $row['telid'], $guess_type, $fmt_phone, $display, $row['tel_pub'])) {
 132                 echo  'WARNING: insert of address phone number failed for profile ' . $row['pid'] . ', address ' . $row['adrid']
 133                     . ' and telephone id ' . $row['telid'] . ".\n";
 134                 ++$warnings;
 135             } else {
 136                 if ($row['tel_type'] == 'Autre') {
 137                     ++$other_count;
 138                 } else if (!isset($conversions[$row['tel_type']])) {
 139                     $conversions[$row['tel_type']] = $guess_type;
 140                 }
 141             }
 142             break;
 143         case 'conflict':
 144             echo  'WARNING: conflict for profile ' . $row['pid'] . ', address ' . $row['adrid']
 145                 . ' and telephone id ' . $row['telid'] . ': type = "' . $row['tel_type']
 146                 . '", number = "' .$fmt_phone . "\"\n";
 147             ++$warnings;
 148             break;
 149         case 'unknown':
 150         default:
 151             echo  'WARNING: unknown phone type (' . $row['tel_type'] . ') for profile ' . $row['pid'] . ', address ' . $row['adrid']
 152                 . ' and telephone id ' . $row['telid'] . "\n";
 153             ++$warnings;
 154         }
 155     }
 156 }
 157
 158 echo "\nSummary of automatic phone type conversion\n";
 159 foreach ($conversions as $old => $new) {
 160     echo "* $old => $new\n";
 161 }
 162 echo "There was also $other_count conversions from old type 'Autre' to a new one determined by the phone number.\n";
 163
 164
 165
 166 //end of import
 167 if ($warnings) {
 168     echo  "\n----------------------------------------------------------------------\n"
 169         . " There is $warnings phone numbers that couldn't be imported.\n"
 170         . " They need to be manually inserted.\n";
 171 }
 172 echo  "\nAfter solving any import problem and checking automatic conversions,\n"
 173     . "you can drop useless columns and tables by these requests:\n"
 174     . "DROP TABLE IF EXISTS tels;\n"
 175     . "ALTER TABLE auth_user_quick DROP COLUMN profile_mobile;\n"
 176     . "ALTER TABLE auth_user_quick DROP COLUMN profile_mobile_pub;\n"
 177     . "ALTER TABLE entreprises DROP COLUMN tel;\n"
 178     . "ALTER TABLE entreprises DROP COLUMN fax;\n"
 179     . "ALTER TABLE entreprises DROP COLUMN mobile;\n"
 180     . "ALTER TABLE entreprises DROP COLUMN tel_pub;\n";
 181
 182
 183 // auxilliary functions
 184
 185 function guess_phone_type($str_type, $phone)
 186 {
 187     $str_type = strtolower(trim($str_type));
 188
 189     // special case for phone type 'autre', guessing by phone number
 190     if ($str_type == 'autre') {
 191         if (substr($phone, 3) == '336') {
 192             return 'mobile';
 193         } else {
 194             return 'fixed';
 195         }
 196     }
 197
 198     if ((strpos($str_type, 'mob') !== false) || (strpos($str_type, 'cell') !== false)
 199         || (strpos($str_type, 'port') !== false) || (strpos($str_type, 'ptb') !== false)) {
 200         if (substr($phone, 3) == '336' || substr($phone, 2) != '33') {
 201             return 'mobile';      //for France check if number is a mobile one
 202         } else {
 203             return 'conflict';
 204         }
 205     }
 206     if (strpos($str_type, 'fax') !== false) {
 207         if (substr($phone, 3) == '336') {
 208             return 'conflict';
 209         } else {
 210             return 'fax';
 211         }
 212     }
 213     if ((strpos($str_type, 'fixe') !== false) || (strpos($str_type, 'tél') !== false)
 214         || (strpos($str_type, 'tel') !== false) || (strpos($str_type, 'free') !== false)) {
 215         if (substr($phone, 3) == '336') {
 216             return 'conflict';
 217         } else {
 218             return 'fixed';
 219         }
 220     }
 221
 222     return 'unknown';
 223 }
 224
 225 /* vim:set et sw=4 sts=4 ts=4: */
 226 ?>