| 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 | $pub = ($pub == '' ? 'private' : $pub); |
| 65 | $fmt_phone = format_phone_number($phone); |
| 66 | if ($fmt_phone != '') { |
| 67 | $display = format_display_number($fmt_phone, $error); |
| 68 | if (!XDB::execute('INSERT INTO profile_phones (pid, link_type, link_id, tel_id, tel_type, search_tel, display_tel, pub) |
| 69 | VALUES ({?}, \'user\', 0, 0, \'mobile\', {?}, {?}, {?})', $pid, $fmt_phone, $display, $pub)) { |
| 70 | echo "WARNING: insert of profile mobile phone number failed for profile $pid.\n"; |
| 71 | ++$warnings; |
| 72 | } |
| 73 | } |
| 74 | } |
| 75 | |
| 76 | |
| 77 | // Import from entreprises |
| 78 | echo "\nImporting professional phone numbers from entreprises...\n"; |
| 79 | $phones = XDB::iterator('SELECT ap.pid, e.entrid, e.tel, e.fax, e.mobile, e.tel_pub |
| 80 | FROM #x4dat#.entreprises AS e |
| 81 | INNER JOIN account_profiles AS ap ON (e.uid = ap.uid AND FIND_IN_SET(\'owner\', ap.perms)) |
| 82 | ORDER BY ap.pid'); |
| 83 | while ($row = $phones->next()) { |
| 84 | $row['tel_pub'] = ($row['tel_pub'] == '' ? 'private' : $row['tel_pub']); |
| 85 | $request = 'INSERT INTO profile_phones (pid, link_type, link_id, tel_id, tel_type, search_tel, display_tel, pub) |
| 86 | VALUES ({?}, \'pro\', {?}, {?}, {?}, {?}, {?}, {?})'; |
| 87 | $fmt_fixed = format_phone_number($row['tel']); |
| 88 | $fmt_mobile = format_phone_number($row['mobile']); |
| 89 | $fmt_fax = format_phone_number($row['fax']); |
| 90 | if ($fmt_fixed != '') { |
| 91 | $disp_fixed = format_display_number($fmt_fixed, $error); |
| 92 | if (!XDB::execute($request, $row['pid'], $row['entrid'], 0, 'fixed', $fmt_fixed, $disp_fixed, $row['tel_pub'])) { |
| 93 | echo 'WARNING: insert of professional fixed phone number failed for profile ' . $row['pid'] . ' and entreprise ' . $row['entrid'] . ".\n"; |
| 94 | ++$warnings; |
| 95 | } |
| 96 | } |
| 97 | if ($fmt_mobile != '') { |
| 98 | $disp_mobile = format_display_number($fmt_mobile, $error); |
| 99 | if (!XDB::execute($request, $row['pid'], $row['entrid'], 1, 'mobile', $fmt_mobile, $disp_mobile, $row['tel_pub'])) { |
| 100 | echo 'WARNING: insert of professional mobile number failed for profile ' . $row['pid'] . ' and entreprise ' . $row['entrid'] . ".\n"; |
| 101 | $warnings++; |
| 102 | } |
| 103 | } |
| 104 | if ($fmt_fax != '') { |
| 105 | $disp_fax = format_display_number($fmt_fax, $error); |
| 106 | if (!XDB::execute($request, $row['pid'], $row['entrid'], 2, 'fax', $fmt_fax, $disp_fax, $row['tel_pub'])) { |
| 107 | echo 'WARNING: insert of professional fax number failed for profile ' . $row['pid'] . ' and entreprise ' . $row['entrid'] . ".\n"; |
| 108 | $warnings++; |
| 109 | } |
| 110 | } |
| 111 | } |
| 112 | |
| 113 | |
| 114 | //import from tels |
| 115 | echo "\nImporting personnal phone numbers from tels...\n"; |
| 116 | $phones = XDB::iterator('SELECT ap.pid, t.adrid, t.telid, t.tel_type, t.tel_pub, t.tel |
| 117 | FROM #x4dat#.tels AS t |
| 118 | INNER JOIN account_profiles AS ap ON (t.uid = ap.uid AND FIND_IN_SET(\'owner\', ap.perms))'); |
| 119 | $conversions = array(); |
| 120 | $other_count = 0; |
| 121 | while ($row = $phones->next()) { |
| 122 | $row['tel_pub'] = ($row['tel_pub'] == '' ? 'private' : $row['tel_pub']); |
| 123 | $fmt_phone = format_phone_number($row['tel']); |
| 124 | if ($fmt_phone != '') { |
| 125 | $display = format_display_number($fmt_phone, $error); |
| 126 | $guess_type = guess_phone_type($row['tel_type'], $fmt_phone); |
| 127 | |
| 128 | switch ($guess_type) { |
| 129 | case 'fixed': |
| 130 | case 'fax': |
| 131 | case 'mobile': |
| 132 | if (!XDB::execute('INSERT INTO profile_phones (pid, link_type, link_id, tel_id, tel_type, search_tel, display_tel, pub) |
| 133 | VALUES ({?}, \'address\', {?}, {?}, {?}, {?}, {?}, {?})', |
| 134 | $row['pid'], $row['adrid'], $row['telid'], $guess_type, $fmt_phone, $display, $row['tel_pub'])) { |
| 135 | echo 'WARNING: insert of address phone number failed for profile ' . $row['pid'] . ', address ' . $row['adrid'] |
| 136 | . ' and telephone id ' . $row['telid'] . ".\n"; |
| 137 | ++$warnings; |
| 138 | } else { |
| 139 | if ($row['tel_type'] == 'Autre') { |
| 140 | ++$other_count; |
| 141 | } else if (!isset($conversions[$row['tel_type']])) { |
| 142 | $conversions[$row['tel_type']] = $guess_type; |
| 143 | } |
| 144 | } |
| 145 | break; |
| 146 | case 'conflict': |
| 147 | echo 'WARNING: conflict for profile ' . $row['pid'] . ', address ' . $row['adrid'] |
| 148 | . ' and telephone id ' . $row['telid'] . ': type = "' . $row['tel_type'] |
| 149 | . '", number = "' .$fmt_phone . "\"\n"; |
| 150 | ++$warnings; |
| 151 | break; |
| 152 | case 'unknown': |
| 153 | default: |
| 154 | echo 'WARNING: unknown phone type (' . $row['tel_type'] . ') for profile ' . $row['pid'] . ', address ' . $row['adrid'] |
| 155 | . ' and telephone id ' . $row['telid'] . "\n"; |
| 156 | ++$warnings; |
| 157 | } |
| 158 | } |
| 159 | } |
| 160 | |
| 161 | echo "\nSummary of automatic phone type conversion\n"; |
| 162 | foreach ($conversions as $old => $new) { |
| 163 | echo "* $old => $new\n"; |
| 164 | } |
| 165 | echo "There was also $other_count conversions from old type 'Autre' to a new one determined by the phone number.\n"; |
| 166 | |
| 167 | |
| 168 | |
| 169 | //end of import |
| 170 | if ($warnings) { |
| 171 | echo "\n----------------------------------------------------------------------\n" |
| 172 | . " There is $warnings phone numbers that couldn't be imported.\n" |
| 173 | . " They need to be manually inserted.\n"; |
| 174 | } |
| 175 | |
| 176 | // auxilliary functions |
| 177 | |
| 178 | function guess_phone_type($str_type, $phone) |
| 179 | { |
| 180 | $str_type = strtolower(trim($str_type)); |
| 181 | |
| 182 | // special case for phone type 'autre', guessing by phone number |
| 183 | if ($str_type == 'autre') { |
| 184 | if (substr($phone, 3) == '336') { |
| 185 | return 'mobile'; |
| 186 | } else { |
| 187 | return 'fixed'; |
| 188 | } |
| 189 | } |
| 190 | |
| 191 | if ((strpos($str_type, 'mob') !== false) || (strpos($str_type, 'cell') !== false) |
| 192 | || (strpos($str_type, 'port') !== false) || (strpos($str_type, 'ptb') !== false)) { |
| 193 | if (substr($phone, 3) == '336' || substr($phone, 2) != '33') { |
| 194 | return 'mobile'; //for France check if number is a mobile one |
| 195 | } else { |
| 196 | return 'conflict'; |
| 197 | } |
| 198 | } |
| 199 | if (strpos($str_type, 'fax') !== false) { |
| 200 | if (substr($phone, 3) == '336') { |
| 201 | return 'conflict'; |
| 202 | } else { |
| 203 | return 'fax'; |
| 204 | } |
| 205 | } |
| 206 | if ((strpos($str_type, 'fixe') !== false) || (strpos($str_type, 'tél') !== false) |
| 207 | || (strpos($str_type, 'tel') !== false) || (strpos($str_type, 'free') !== false)) { |
| 208 | if (substr($phone, 3) == '336') { |
| 209 | return 'conflict'; |
| 210 | } else { |
| 211 | return 'fixed'; |
| 212 | } |
| 213 | } |
| 214 | |
| 215 | return 'unknown'; |
| 216 | } |
| 217 | |
| 218 | /* vim:set et sw=4 sts=4 ts=4: */ |
| 219 | ?> |