upgrade/fusionax-0.0.1/phones.php

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