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 | 8 | // Convert phone prefixes from varchar to int |
b0145685 SJ |
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'); | |
b235d980 GB |
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) { | |
b0145685 SJ |
23 | XDB::execute('UPDATE geoloc_countries |
24 | SET tmpPhonePrefix = {?} | |
25 | WHERE iso_3166_1_a2 = {?}', $pref, $id); | |
b235d980 GB |
26 | } |
27 | } | |
28 | ||
29 | // geoloc_pays post operations | |
b0145685 SJ |
30 | // Drops old prefix column |
31 | XDB::execute('ALTER TABLE geoloc_countries | |
32 | DROP COLUMN phonePrefix'); | |
b235d980 | 33 | // Renames temporary column |
b0145685 SJ |
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)'); | |
b235d980 | 39 | // Adds French phone prefix |
b0145685 SJ |
40 | XDB::execute('UPDATE geoloc_countries |
41 | SET phonePrefix = \'33\' | |
42 | WHERE iso_3166_1_a2 = \'FR\''); | |
b235d980 | 43 | // Adds some phone formats |
b0145685 SJ |
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 | |
b235d980 GB |
50 | |
51 | ||
52 | ||
53 | //Phone number import | |
54 | ||
de15699b GB |
55 | $warnings = 0; |
56 | ||
57 | // Import from auth_user_quick | |
58 | echo "\nImporting mobile phone numbers from auth_user_quick...\n"; | |
b0145685 SJ |
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()) { | |
d106734b | 64 | $pub = ($pub == '' ? 'private' : $pub); |
de15699b | 65 | $fmt_phone = format_phone_number($phone); |
b235d980 | 66 | if ($fmt_phone != '') { |
b0145685 SJ |
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; | |
de15699b GB |
72 | } |
73 | } | |
74 | } | |
75 | ||
76 | ||
77 | // Import from entreprises | |
78 | echo "\nImporting professional phone numbers from entreprises...\n"; | |
b0145685 SJ |
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'); | |
de15699b | 83 | while ($row = $phones->next()) { |
d106734b | 84 | $row['tel_pub'] = ($row['tel_pub'] == '' ? 'private' : $row['tel_pub']); |
b0145685 SJ |
85 | $request = 'INSERT INTO profile_phones (pid, link_type, link_id, tel_id, tel_type, search_tel, display_tel, pub) |
86 | VALUES ({?}, \'pro\', {?}, {?}, {?}, {?}, {?}, {?})'; | |
de15699b GB |
87 | $fmt_fixed = format_phone_number($row['tel']); |
88 | $fmt_mobile = format_phone_number($row['mobile']); | |
89 | $fmt_fax = format_phone_number($row['fax']); | |
b235d980 | 90 | if ($fmt_fixed != '') { |
de15699b | 91 | $disp_fixed = format_display_number($fmt_fixed, $error); |
b0145685 SJ |
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; | |
de15699b GB |
95 | } |
96 | } | |
b235d980 | 97 | if ($fmt_mobile != '') { |
de15699b | 98 | $disp_mobile = format_display_number($fmt_mobile, $error); |
b0145685 SJ |
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"; | |
de15699b GB |
101 | $warnings++; |
102 | } | |
103 | } | |
b235d980 | 104 | if ($fmt_fax != '') { |
de15699b | 105 | $disp_fax = format_display_number($fmt_fax, $error); |
b0145685 SJ |
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"; | |
de15699b GB |
108 | $warnings++; |
109 | } | |
110 | } | |
111 | } | |
112 | ||
113 | ||
114 | //import from tels | |
115 | echo "\nImporting personnal phone numbers from tels...\n"; | |
b0145685 SJ |
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))'); | |
de15699b | 119 | $conversions = array(); |
b0145685 | 120 | $other_count = 0; |
de15699b | 121 | while ($row = $phones->next()) { |
d106734b | 122 | $row['tel_pub'] = ($row['tel_pub'] == '' ? 'private' : $row['tel_pub']); |
de15699b GB |
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': | |
b0145685 SJ |
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'] | |
de15699b | 136 | . ' and telephone id ' . $row['telid'] . ".\n"; |
b0145685 | 137 | ++$warnings; |
de15699b GB |
138 | } else { |
139 | if ($row['tel_type'] == 'Autre') { | |
b0145685 | 140 | ++$other_count; |
de15699b GB |
141 | } else if (!isset($conversions[$row['tel_type']])) { |
142 | $conversions[$row['tel_type']] = $guess_type; | |
143 | } | |
144 | } | |
145 | break; | |
146 | case 'conflict': | |
b0145685 | 147 | echo 'WARNING: conflict for profile ' . $row['pid'] . ', address ' . $row['adrid'] |
de15699b GB |
148 | . ' and telephone id ' . $row['telid'] . ': type = "' . $row['tel_type'] |
149 | . '", number = "' .$fmt_phone . "\"\n"; | |
b0145685 | 150 | ++$warnings; |
de15699b GB |
151 | break; |
152 | case 'unknown': | |
153 | default: | |
b0145685 | 154 | echo 'WARNING: unknown phone type (' . $row['tel_type'] . ') for profile ' . $row['pid'] . ', address ' . $row['adrid'] |
de15699b | 155 | . ' and telephone id ' . $row['telid'] . "\n"; |
b0145685 | 156 | ++$warnings; |
de15699b GB |
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 | } | |
b0145685 | 165 | echo "There was also $other_count conversions from old type 'Autre' to a new one determined by the phone number.\n"; |
de15699b GB |
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 | } | |
de15699b GB |
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 | ||
b0145685 SJ |
191 | if ((strpos($str_type, 'mob') !== false) || (strpos($str_type, 'cell') !== false) |
192 | || (strpos($str_type, 'port') !== false) || (strpos($str_type, 'ptb') !== false)) { | |
de15699b GB |
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) { | |
b235d980 | 200 | if (substr($phone, 3) == '336') { |
de15699b GB |
201 | return 'conflict'; |
202 | } else { | |
203 | return 'fax'; | |
204 | } | |
205 | } | |
b0145685 SJ |
206 | if ((strpos($str_type, 'fixe') !== false) || (strpos($str_type, 'tél') !== false) |
207 | || (strpos($str_type, 'tel') !== false) || (strpos($str_type, 'free') !== false)) { | |
b235d980 | 208 | if (substr($phone, 3) == '336') { |
de15699b GB |
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 | ?> |