Commit | Line | Data |
---|---|---|
de15699b GB |
1 | #!/usr/bin/php5 |
2 | <?php | |
3 | require_once 'connect.db.inc.php'; | |
de15699b GB |
4 | require_once 'profil.func.inc.php'; |
5 | ||
6 | $globals->debug = 0; //do not store backtraces | |
7 | ||
b235d980 GB |
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 | ||
de15699b GB |
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); | |
b235d980 | 47 | if ($fmt_phone != '') { |
de15699b | 48 | $display = format_display_number($fmt_phone, $error); |
b235d980 | 49 | if (!XDB::execute("INSERT INTO profile_phones (uid, link_type, link_id, tel_id, tel_type, search_tel, display_tel, pub) |
de15699b GB |
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()) { | |
b235d980 | 62 | $request = "INSERT INTO profile_phones (uid, link_type, link_id, tel_id, tel_type, search_tel, display_tel, pub) |
de15699b GB |
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']); | |
b235d980 | 67 | if ($fmt_fixed != '') { |
de15699b GB |
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 | } | |
b235d980 | 74 | if ($fmt_mobile != '') { |
de15699b GB |
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 | } | |
b235d980 | 81 | if ($fmt_fax != '') { |
de15699b GB |
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': | |
b235d980 | 106 | if (!XDB::execute("INSERT INTO profile_phones (uid, link_type, link_id, tel_id, tel_type, search_tel, display_tel, pub) |
de15699b GB |
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 | ||
b235d980 | 175 | if ((strpos($str_type, 'mob') !== false) || (strpos($str_type, 'cell') !== false) || (strpos($str_type, 'port') !== false)) || (strpos($str_type, 'ptb') !== false) { |
de15699b GB |
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) { | |
b235d980 | 183 | if (substr($phone, 3) == '336') { |
de15699b GB |
184 | return 'conflict'; |
185 | } else { | |
186 | return 'fax'; | |
187 | } | |
188 | } | |
b235d980 GB |
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') { | |
de15699b GB |
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 | ?> |