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