Commit | Line | Data |
---|---|---|
de15699b GB |
1 | #!/usr/bin/php5 |
2 | <?php | |
3 | require_once 'connect.db.inc.php'; | |
4 | ||
5 | //next two are required to include 'profil.func.inc.php' | |
6 | require_once 'xorg.inc.php'; | |
7 | $page = new XorgPage(null); | |
8 | ||
9 | require_once 'profil.func.inc.php'; | |
10 | ||
11 | $globals->debug = 0; //do not store backtraces | |
12 | ||
b235d980 GB |
13 | |
14 | // Convert phone prefixes from varchar to int | |
15 | $prefixes = XDB::iterRow("SELECT a2, phoneprf FROM geoloc_pays WHERE phoneprf IS NOT NULL"); | |
16 | while (list($id, $pref) = $prefixes->next()) { | |
17 | $pref = preg_replace('/[^0-9]/', '', $pref); | |
18 | if ($pref[0] == '1') { | |
19 | $pref = '1'; | |
20 | } | |
21 | if ($pref[0] == '7') { | |
22 | $pref = '7'; | |
23 | } | |
24 | if ($pref != '' && strlen($pref) < 4) { | |
25 | XDB::execute("UPDATE geoloc_pays SET tmp_phoneprf = {?} WHERE a2 = {?}", $pref, $id); | |
26 | } | |
27 | } | |
28 | ||
29 | // geoloc_pays post operations | |
30 | // Drops old prfix column | |
31 | XDB::execute("ALTER TABLE geoloc_pays DROP COLUMN phoneprf"); | |
32 | // Renames temporary column | |
33 | XDB::execute("ALTER TABLE geoloc_pays CHANGE COLUMN tmp_phoneprf phoneprf smallint unsigned NULL AFTER nat"); | |
34 | // Adds an index on phoneprf column | |
35 | XDB::execute("ALTER TABLE geoloc_pays ADD INDEX (phoneprf)"); | |
36 | // Adds French phone prefix | |
37 | XDB::execute("UPDATE geoloc_pays SET phoneprf = '33' WHERE a2 = 'FR'"); | |
38 | // Adds some phone formats | |
39 | XDB::execute("UPDATE geoloc_pays SET phoneformat = '0# ## ## ## ##' WHERE phoneprf = '33'"); //France | |
40 | XDB::execute("UPDATE geoloc_pays SET phoneformat = '(+p) ### ### ####' WHERE phoneprf = '1'"); //USA and NANP countries | |
41 | ||
42 | ||
43 | ||
44 | //Phone number import | |
45 | ||
de15699b GB |
46 | $warnings = 0; |
47 | ||
48 | // Import from auth_user_quick | |
49 | echo "\nImporting mobile phone numbers from auth_user_quick...\n"; | |
50 | $phones = XDB::iterRow("SELECT user_id, profile_mobile_pub, profile_mobile FROM auth_user_quick WHERE profile_mobile <> ''"); | |
51 | while (list($uid, $pub, $phone) = $phones->next()) { | |
52 | $fmt_phone = format_phone_number($phone); | |
b235d980 | 53 | if ($fmt_phone != '') { |
de15699b | 54 | $display = format_display_number($fmt_phone, $error); |
b235d980 | 55 | if (!XDB::execute("INSERT INTO profile_phones (uid, link_type, link_id, tel_id, tel_type, search_tel, display_tel, pub) |
de15699b GB |
56 | VALUES ({?}, 'user', 0, 0, 'mobile', {?}, {?}, {?})", $uid, $fmt_phone, $display, $pub)) { |
57 | echo "WARNING: insert of profile mobile phone number failed for user $uid.\n"; | |
58 | $warnings++; | |
59 | } | |
60 | } | |
61 | } | |
62 | ||
63 | ||
64 | // Import from entreprises | |
65 | echo "\nImporting professional phone numbers from entreprises...\n"; | |
66 | $phones = XDB::iterator("SELECT uid, entrid, tel, fax, mobile, tel_pub FROM entreprises ORDER BY uid"); | |
67 | while ($row = $phones->next()) { | |
b235d980 | 68 | $request = "INSERT INTO profile_phones (uid, link_type, link_id, tel_id, tel_type, search_tel, display_tel, pub) |
de15699b GB |
69 | VALUES ({?}, 'pro', {?}, {?}, {?}, {?}, {?}, {?})"; |
70 | $fmt_fixed = format_phone_number($row['tel']); | |
71 | $fmt_mobile = format_phone_number($row['mobile']); | |
72 | $fmt_fax = format_phone_number($row['fax']); | |
b235d980 | 73 | if ($fmt_fixed != '') { |
de15699b GB |
74 | $disp_fixed = format_display_number($fmt_fixed, $error); |
75 | if (!XDB::execute($request, $row['uid'], $row['entrid'], 0, 'fixed', $fmt_fixed, $disp_fixed, $row['tel_pub'])) { | |
76 | echo 'WARNING: insert of professional fixed phone number failed for user ' . $row['uid'] . ' and entreprise ' . $row['entrid'] . ".\n"; | |
77 | $warnings++; | |
78 | } | |
79 | } | |
b235d980 | 80 | if ($fmt_mobile != '') { |
de15699b GB |
81 | $disp_mobile = format_display_number($fmt_mobile, $error); |
82 | if (!XDB::execute($request, $row['uid'], $row['entrid'], 1, 'mobile', $fmt_mobile, $disp_mobile, $row['tel_pub'])) { | |
83 | echo 'WARNING: insert of professional mobile number failed for user ' . $row['uid'] . ' and entreprise ' . $row['entrid'] . ".\n"; | |
84 | $warnings++; | |
85 | } | |
86 | } | |
b235d980 | 87 | if ($fmt_fax != '') { |
de15699b GB |
88 | $disp_fax = format_display_number($fmt_fax, $error); |
89 | if (!XDB::execute($request, $row['uid'], $row['entrid'], 2, 'fax', $fmt_fax, $disp_fax, $row['tel_pub'])) { | |
90 | echo 'WARNING: insert of professional fax number failed for user ' . $row['uid'] . ' and entreprise ' . $row['entrid'] . ".\n"; | |
91 | $warnings++; | |
92 | } | |
93 | } | |
94 | } | |
95 | ||
96 | ||
97 | //import from tels | |
98 | echo "\nImporting personnal phone numbers from tels...\n"; | |
99 | $phones = XDB::iterator("SELECT uid, adrid, telid, tel_type, tel_pub, tel FROM tels"); | |
100 | $conversions = array(); | |
101 | $autre_count = 0; | |
102 | while ($row = $phones->next()) { | |
103 | $fmt_phone = format_phone_number($row['tel']); | |
104 | if ($fmt_phone != '') { | |
105 | $display = format_display_number($fmt_phone, $error); | |
106 | $guess_type = guess_phone_type($row['tel_type'], $fmt_phone); | |
107 | ||
108 | switch ($guess_type) { | |
109 | case 'fixed': | |
110 | case 'fax': | |
111 | case 'mobile': | |
b235d980 | 112 | if (!XDB::execute("INSERT INTO profile_phones (uid, link_type, link_id, tel_id, tel_type, search_tel, display_tel, pub) |
de15699b GB |
113 | VALUES ({?}, 'address', {?}, {?}, {?}, {?}, {?}, {?})", |
114 | $row['uid'], $row['adrid'], $row['telid'], $guess_type, $fmt_phone, $display, $row['tel_pub'])) { | |
115 | echo 'WARNING: insert of address phone number failed for user ' . $row['uid'] . ', address ' . $row['adrid'] | |
116 | . ' and telephone id ' . $row['telid'] . ".\n"; | |
117 | $warnings++; | |
118 | } else { | |
119 | if ($row['tel_type'] == 'Autre') { | |
120 | $autre_count++; | |
121 | } else if (!isset($conversions[$row['tel_type']])) { | |
122 | $conversions[$row['tel_type']] = $guess_type; | |
123 | } | |
124 | } | |
125 | break; | |
126 | case 'conflict': | |
127 | echo 'WARNING: conflict for user ' . $row['uid'] . ', address ' . $row['adrid'] | |
128 | . ' and telephone id ' . $row['telid'] . ': type = "' . $row['tel_type'] | |
129 | . '", number = "' .$fmt_phone . "\"\n"; | |
130 | $warnings++; | |
131 | break; | |
132 | case 'unknown': | |
133 | default: | |
134 | echo 'WARNING: unknown phone type (' . $row['tel_type'] . ') for user ' . $row['uid'] . ', address ' . $row['adrid'] | |
135 | . ' and telephone id ' . $row['telid'] . "\n"; | |
136 | $warnings++; | |
137 | } | |
138 | } | |
139 | } | |
140 | ||
141 | echo "\nSummary of automatic phone type conversion\n"; | |
142 | foreach ($conversions as $old => $new) { | |
143 | echo "* $old => $new\n"; | |
144 | } | |
145 | echo "There was also $autre_count conversions from old type 'Autre' to a new one determined by the phone number.\n"; | |
146 | ||
147 | ||
148 | ||
149 | //end of import | |
150 | if ($warnings) { | |
151 | echo "\n----------------------------------------------------------------------\n" | |
152 | . " There is $warnings phone numbers that couldn't be imported.\n" | |
153 | . " They need to be manually inserted.\n"; | |
154 | } | |
155 | echo "\nAfter solving any import problem and checking automatic conversions,\n" | |
156 | . "you can drop useless columns and tables by these requests:\n" | |
157 | . "DROP TABLE IF EXISTS `tels`;\n" | |
158 | . "ALTER TABLE `auth_user_quick` DROP COLUMN `profile_mobile`;\n" | |
159 | . "ALTER TABLE `auth_user_quick` DROP COLUMN `profile_mobile_pub`;\n" | |
160 | . "ALTER TABLE `entreprises` DROP COLUMN `tel`;\n" | |
161 | . "ALTER TABLE `entreprises` DROP COLUMN `fax`;\n" | |
162 | . "ALTER TABLE `entreprises` DROP COLUMN `mobile`;\n" | |
163 | . "ALTER TABLE `entreprises` DROP COLUMN `tel_pub`;\n"; | |
164 | ||
165 | ||
166 | // auxilliary functions | |
167 | ||
168 | function guess_phone_type($str_type, $phone) | |
169 | { | |
170 | $str_type = strtolower(trim($str_type)); | |
171 | ||
172 | // special case for phone type 'autre', guessing by phone number | |
173 | if ($str_type == 'autre') { | |
174 | if (substr($phone, 3) == '336') { | |
175 | return 'mobile'; | |
176 | } else { | |
177 | return 'fixed'; | |
178 | } | |
179 | } | |
180 | ||
b235d980 | 181 | if ((strpos($str_type, 'mob') !== false) || (strpos($str_type, 'cell') !== false) || (strpos($str_type, 'port') !== false)) || (strpos($str_type, 'ptb') !== false) { |
de15699b GB |
182 | if (substr($phone, 3) == '336' || substr($phone, 2) != '33') { |
183 | return 'mobile'; //for France check if number is a mobile one | |
184 | } else { | |
185 | return 'conflict'; | |
186 | } | |
187 | } | |
188 | if (strpos($str_type, 'fax') !== false) { | |
b235d980 | 189 | if (substr($phone, 3) == '336') { |
de15699b GB |
190 | return 'conflict'; |
191 | } else { | |
192 | return 'fax'; | |
193 | } | |
194 | } | |
b235d980 GB |
195 | if ((strpos($str_type, 'fixe') !== false) || (strpos($str_type, 'tél') !== false) || (strpos($str_type, 'tel') !== false) || (strpos($str_type, 'free') !== false)) { |
196 | if (substr($phone, 3) == '336') { | |
de15699b GB |
197 | return 'conflict'; |
198 | } else { | |
199 | return 'fixed'; | |
200 | } | |
201 | } | |
202 | ||
203 | return 'unknown'; | |
204 | } | |
205 | ||
206 | /* vim:set et sw=4 sts=4 ts=4: */ | |
207 | ?> |