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()) { | |
de15699b | 64 | $fmt_phone = format_phone_number($phone); |
b235d980 | 65 | if ($fmt_phone != '') { |
b0145685 SJ |
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"; | |
70 | ++$warnings; | |
de15699b GB |
71 | } |
72 | } | |
73 | } | |
74 | ||
75 | ||
76 | // Import from entreprises | |
77 | echo "\nImporting professional phone numbers from entreprises...\n"; | |
b0145685 SJ |
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)) | |
81 | ORDER BY ap.pid'); | |
de15699b | 82 | while ($row = $phones->next()) { |
b0145685 SJ |
83 | $request = 'INSERT INTO profile_phones (pid, link_type, link_id, tel_id, tel_type, search_tel, display_tel, pub) |
84 | VALUES ({?}, \'pro\', {?}, {?}, {?}, {?}, {?}, {?})'; | |
de15699b GB |
85 | $fmt_fixed = format_phone_number($row['tel']); |
86 | $fmt_mobile = format_phone_number($row['mobile']); | |
87 | $fmt_fax = format_phone_number($row['fax']); | |
b235d980 | 88 | if ($fmt_fixed != '') { |
de15699b | 89 | $disp_fixed = format_display_number($fmt_fixed, $error); |
b0145685 SJ |
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"; | |
92 | ++$warnings; | |
de15699b GB |
93 | } |
94 | } | |
b235d980 | 95 | if ($fmt_mobile != '') { |
de15699b | 96 | $disp_mobile = format_display_number($fmt_mobile, $error); |
b0145685 SJ |
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"; | |
de15699b GB |
99 | $warnings++; |
100 | } | |
101 | } | |
b235d980 | 102 | if ($fmt_fax != '') { |
de15699b | 103 | $disp_fax = format_display_number($fmt_fax, $error); |
b0145685 SJ |
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"; | |
de15699b GB |
106 | $warnings++; |
107 | } | |
108 | } | |
109 | } | |
110 | ||
111 | ||
112 | //import from tels | |
113 | echo "\nImporting personnal phone numbers from tels...\n"; | |
b0145685 SJ |
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))'); | |
de15699b | 117 | $conversions = array(); |
b0145685 | 118 | $other_count = 0; |
de15699b GB |
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); | |
124 | ||
125 | switch ($guess_type) { | |
126 | case 'fixed': | |
127 | case 'fax': | |
128 | case 'mobile': | |
b0145685 SJ |
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'] | |
de15699b | 133 | . ' and telephone id ' . $row['telid'] . ".\n"; |
b0145685 | 134 | ++$warnings; |
de15699b GB |
135 | } else { |
136 | if ($row['tel_type'] == 'Autre') { | |
b0145685 | 137 | ++$other_count; |
de15699b GB |
138 | } else if (!isset($conversions[$row['tel_type']])) { |
139 | $conversions[$row['tel_type']] = $guess_type; | |
140 | } | |
141 | } | |
142 | break; | |
143 | case 'conflict': | |
b0145685 | 144 | echo 'WARNING: conflict for profile ' . $row['pid'] . ', address ' . $row['adrid'] |
de15699b GB |
145 | . ' and telephone id ' . $row['telid'] . ': type = "' . $row['tel_type'] |
146 | . '", number = "' .$fmt_phone . "\"\n"; | |
b0145685 | 147 | ++$warnings; |
de15699b GB |
148 | break; |
149 | case 'unknown': | |
150 | default: | |
b0145685 | 151 | echo 'WARNING: unknown phone type (' . $row['tel_type'] . ') for profile ' . $row['pid'] . ', address ' . $row['adrid'] |
de15699b | 152 | . ' and telephone id ' . $row['telid'] . "\n"; |
b0145685 | 153 | ++$warnings; |
de15699b GB |
154 | } |
155 | } | |
156 | } | |
157 | ||
158 | echo "\nSummary of automatic phone type conversion\n"; | |
159 | foreach ($conversions as $old => $new) { | |
160 | echo "* $old => $new\n"; | |
161 | } | |
b0145685 | 162 | echo "There was also $other_count conversions from old type 'Autre' to a new one determined by the phone number.\n"; |
de15699b GB |
163 | |
164 | ||
165 | ||
166 | //end of import | |
167 | if ($warnings) { | |
168 | echo "\n----------------------------------------------------------------------\n" | |
169 | . " There is $warnings phone numbers that couldn't be imported.\n" | |
170 | . " They need to be manually inserted.\n"; | |
171 | } | |
172 | echo "\nAfter solving any import problem and checking automatic conversions,\n" | |
173 | . "you can drop useless columns and tables by these requests:\n" | |
b0145685 SJ |
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"; | |
de15699b GB |
181 | |
182 | ||
183 | // auxilliary functions | |
184 | ||
185 | function guess_phone_type($str_type, $phone) | |
186 | { | |
187 | $str_type = strtolower(trim($str_type)); | |
188 | ||
189 | // special case for phone type 'autre', guessing by phone number | |
190 | if ($str_type == 'autre') { | |
191 | if (substr($phone, 3) == '336') { | |
192 | return 'mobile'; | |
193 | } else { | |
194 | return 'fixed'; | |
195 | } | |
196 | } | |
197 | ||
b0145685 SJ |
198 | if ((strpos($str_type, 'mob') !== false) || (strpos($str_type, 'cell') !== false) |
199 | || (strpos($str_type, 'port') !== false) || (strpos($str_type, 'ptb') !== false)) { | |
de15699b GB |
200 | if (substr($phone, 3) == '336' || substr($phone, 2) != '33') { |
201 | return 'mobile'; //for France check if number is a mobile one | |
202 | } else { | |
203 | return 'conflict'; | |
204 | } | |
205 | } | |
206 | if (strpos($str_type, 'fax') !== false) { | |
b235d980 | 207 | if (substr($phone, 3) == '336') { |
de15699b GB |
208 | return 'conflict'; |
209 | } else { | |
210 | return 'fax'; | |
211 | } | |
212 | } | |
b0145685 SJ |
213 | if ((strpos($str_type, 'fixe') !== false) || (strpos($str_type, 'tél') !== false) |
214 | || (strpos($str_type, 'tel') !== false) || (strpos($str_type, 'free') !== false)) { | |
b235d980 | 215 | if (substr($phone, 3) == '336') { |
de15699b GB |
216 | return 'conflict'; |
217 | } else { | |
218 | return 'fixed'; | |
219 | } | |
220 | } | |
221 | ||
222 | return 'unknown'; | |
223 | } | |
224 | ||
225 | /* vim:set et sw=4 sts=4 ts=4: */ | |
226 | ?> |