9bc08cd4003d5523c899d8710096d7f5aa58c33e
3 require_once 'connect.db.inc.php';
4 require_once 'profil.func.inc.php';
6 $globals->debug
= 0; //do not store backtraces
8 // Convert phone prefixes from varchar to int
9 $prefixes = XDB
::iterRow('SELECT iso_3166_1_a2, phonePrefix
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') {
19 if ($pref[0] == '7') {
22 if ($pref != '' && strlen($pref) < 4) {
23 XDB
::execute('UPDATE geoloc_countries
24 SET tmpPhonePrefix = {?}
25 WHERE iso_3166_1_a2 = {?}', $pref, $id);
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
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";
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))
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";
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";
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";
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();
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);
125 switch ($guess_type) {
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";
136 if ($row['tel_type'] == 'Autre') {
138 } else if (!isset($conversions[$row['tel_type']])) {
139 $conversions[$row['tel_type']] = $guess_type;
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";
151 echo 'WARNING: unknown phone type (' . $row['tel_type'] . ') for profile ' . $row['pid'] . ', address ' . $row['adrid']
152 . ' and telephone id ' . $row['telid'] . "\n";
158 echo "\nSummary of automatic phone type conversion\n";
159 foreach ($conversions as $old => $new) {
160 echo "* $old => $new\n";
162 echo "There was also $other_count conversions from old type 'Autre' to a new one determined by the phone number.\n";
168 echo "\n----------------------------------------------------------------------\n"
169 . " There is $warnings phone numbers that couldn't be imported.\n"
170 . " They need to be manually inserted.\n";
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";
183 // auxilliary functions
185 function guess_phone_type($str_type, $phone)
187 $str_type = strtolower(trim($str_type));
189 // special case for phone type 'autre', guessing by phone number
190 if ($str_type == 'autre') {
191 if (substr($phone, 3) == '336') {
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
206 if (strpos($str_type, 'fax') !== false
) {
207 if (substr($phone, 3) == '336') {
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') {
225 /* vim:set et sw=4 sts=4 ts=4: */