From de15699b3c13f351553da4897be34a89002f8811 Mon Sep 17 00:00:00 2001 From: Guillaume Bandet Date: Mon, 16 Jun 2008 16:01:21 +0200 Subject: [PATCH] Creates tables for telephone numbers and imports old ones Adds format functions for phone numbers (search and display) --- include/profil.func.inc.php | 57 ++++++++++ upgrade/fusionax-0.0.1/03_telephone.sql | 73 ++++++++++++ upgrade/fusionax-0.0.1/connect.db.inc.php | 28 +++++ upgrade/fusionax-0.0.1/phones.php | 178 ++++++++++++++++++++++++++++++ upgrade/fusionax-0.0.1/update.sh | 6 + 5 files changed, 342 insertions(+) create mode 100644 upgrade/fusionax-0.0.1/03_telephone.sql create mode 100644 upgrade/fusionax-0.0.1/connect.db.inc.php create mode 100755 upgrade/fusionax-0.0.1/phones.php diff --git a/include/profil.func.inc.php b/include/profil.func.inc.php index 40fa9c6..c8334b1 100644 --- a/include/profil.func.inc.php +++ b/include/profil.func.inc.php @@ -282,5 +282,62 @@ function diff_user_pros(&$a, &$b, $view = 'private') { return $c; } +function format_phone_number($tel) +{ + $tel = trim($tel); + if (substr($tel, 0, 3) === '(0)') { + $tel = '33' . $tel; + } + $tel = preg_replace('/\(0\)/', '', $tel); + $tel = preg_replace('/[^0-9]/', '', $tel); + if (substr($tel, 0, 2) === '00') { + $tel = substr($tel, 2); + } else if(substr($tel, 0, 1) === '0') { + $tel = '33' . substr($tel, 1); + } + return $tel; +} + +function format_display_number($tel, &$error) +{ + $error = false; + $ret = ''; + $tel_length = strlen($tel); + $res = XDB::query("SELECT phoneprf, format + FROM phone_formats + WHERE phoneprf = {?} OR phoneprf = {?} OR phoneprf = {?}", + substr($tel, 0, 1), substr($tel, 0, 2), substr($tel, 0, 3)); + if ($res->numRows() == 0) { + $error = true; + return '*+' . $tel; + } + $format = $res->fetchOneAssoc(); + if ($format['format'] == '') { + $format['format'] = '+p'; + } + $j = 0; + $i = strlen($format['phoneprf']); + $length_format = strlen($format['format']); + while (($i < $tel_length) && ($j < $length_format)){ + if ($format['format'][$j] == '#'){ + $ret .= $tel[$i]; + $i++; + } else if ($format['format'][$j] == 'p') { + $ret .= $format['phoneprf']; + } else { + $ret .= $format['format'][$j]; + } + $j++; + } + for (; $i < $tel_length - 1; $i += 2) { + $ret .= ' ' . substr($tel, $i, 2); + } + //appends last alone number to the last block + if ($i < $tel_length) { + $ret .= substr($tel, $i); + } + return $ret; +} + // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8: ?> diff --git a/upgrade/fusionax-0.0.1/03_telephone.sql b/upgrade/fusionax-0.0.1/03_telephone.sql new file mode 100644 index 0000000..33fe966 --- /dev/null +++ b/upgrade/fusionax-0.0.1/03_telephone.sql @@ -0,0 +1,73 @@ +CREATE TABLE IF NOT EXISTS `telephone` ( + `uid` smallint unsigned NOT NULL, + `link_type` enum('address', 'pro', 'user') NOT NULL DEFAULT 'user' COMMENT 'type of parent element', + `link_id` tinyint unsigned NOT NULL COMMENT 'id of his parent element', + `tel_id` tinyint unsigned NOT NULL COMMENT 'index of this number for the couple (user, parent element)', + `tel_type` enum('fixed', 'mobile', 'fax') NOT NULL DEFAULT 'fixed', + `search_tel` varchar(25) NOT NULL COMMENT 'search number in an international format with only digits and the initial +', + `display_tel` varchar(30) NOT NULL COMMENT 'display number', + `pub` enum('private', 'ax', 'public') NOT NULL DEFAULT 'private', + PRIMARY KEY(`uid`, `link_type`, `link_id`, `tel_id`), + INDEX (`search_tel`) +); + +CREATE TABLE IF NOT EXISTS `phone_formats` ( + `phoneprf` varchar(4) NOT NULL, + `format` varchar(25) NOT NULL, + PRIMARY KEY(`phoneprf`) +); + +INSERT INTO `phone_formats` (`phoneprf`, `format`) + VALUES ('1', '(+p) ### ### ####'), ('7', ''), ('20', ''), ('27', ''), + ('30', ''), ('31', ''), ('32', ''), ('33', '0# ## ## ## ##'), + ('34', ''), ('36', ''), ('39', ''), ('40', ''), + ('41', ''), ('43', ''), ('44', ''), ('45', ''), + ('46', ''), ('47', ''), ('48', ''), ('49', ''), + ('51', ''), ('52', ''), ('53', ''), ('54', ''), + ('55', ''), ('56', ''), ('57', ''), ('58', ''), + ('60', ''), ('61', ''), ('62', ''), ('63', ''), + ('64', ''), ('65', ''), ('66', ''), ('81', ''), + ('82', ''), ('84', ''), ('86', ''), ('90', ''), + ('91', ''), ('92', ''), ('93', ''), ('94', ''), + ('95', ''), ('98', ''), ('212', ''), ('213', ''), + ('216', ''), ('218', ''), ('220', ''), ('221', ''), + ('222', ''), ('223', ''), ('224', ''), ('225', ''), + ('226', ''), ('227', ''), ('228', ''), ('229', ''), + ('230', ''), ('231', ''), ('232', ''), ('233', ''), + ('234', ''), ('235', ''), ('236', ''), ('237', ''), + ('238', ''), ('239', ''), ('240', ''), ('241', ''), + ('242', ''), ('243', ''), ('244', ''), ('245', ''), + ('246', ''), ('247', ''), ('248', ''), ('249', ''), + ('250', ''), ('251', ''), ('252', ''), ('253', ''), + ('254', ''), ('255', ''), ('256', ''), ('257', ''), + ('258', ''), ('260', ''), ('261', ''), ('262', ''), + ('263', ''), ('264', ''), ('265', ''), ('266', ''), + ('267', ''), ('268', ''), ('269', ''), ('290', ''), + ('291', ''), ('297', ''), ('298', ''), ('299', ''), + ('350', ''), ('351', ''), ('352', ''), ('353', ''), + ('354', ''), ('355', ''), ('356', ''), ('357', ''), + ('358', ''), ('359', ''), ('370', ''), ('371', ''), + ('372', ''), ('373', ''), ('374', ''), ('375', ''), + ('376', ''), ('377', ''), ('378', ''), ('379', ''), + ('380', ''), ('381', ''), ('382', ''), ('385', ''), + ('386', ''), ('387', ''), ('389', ''), ('420', ''), + ('421', ''), ('423', ''), ('500', ''), ('501', ''), + ('502', ''), ('503', ''), ('504', ''), ('505', ''), + ('506', ''), ('507', ''), ('508', ''), ('509', ''), + ('590', ''), ('591', ''), ('592', ''), ('593', ''), + ('594', ''), ('595', ''), ('596', ''), ('597', ''), + ('598', ''), ('599', ''), ('670', ''), ('672', ''), + ('673', ''), ('674', ''), ('675', ''), ('676', ''), + ('677', ''), ('678', ''), ('679', ''), ('680', ''), + ('681', ''), ('682', ''), ('683', ''), ('684', ''), + ('685', ''), ('686', ''), ('687', ''), ('688', ''), + ('689', ''), ('690', ''), ('691', ''), ('692', ''), + ('850', ''), ('852', ''), ('853', ''), ('855', ''), + ('856', ''), ('880', ''), ('886', ''), ('960', ''), + ('961', ''), ('962', ''), ('963', ''), ('964', ''), + ('965', ''), ('966', ''), ('967', ''), ('968', ''), + ('970', ''), ('971', ''), ('972', ''), ('973', ''), + ('974', ''), ('975', ''), ('976', ''), ('977', ''), + ('992', ''), ('993', ''), ('994', ''), ('995', ''), + ('996', ''), ('998', ''); + diff --git a/upgrade/fusionax-0.0.1/connect.db.inc.php b/upgrade/fusionax-0.0.1/connect.db.inc.php new file mode 100644 index 0000000..2e66895 --- /dev/null +++ b/upgrade/fusionax-0.0.1/connect.db.inc.php @@ -0,0 +1,28 @@ + diff --git a/upgrade/fusionax-0.0.1/phones.php b/upgrade/fusionax-0.0.1/phones.php new file mode 100755 index 0000000..aae2eb9 --- /dev/null +++ b/upgrade/fusionax-0.0.1/phones.php @@ -0,0 +1,178 @@ +#!/usr/bin/php5 +debug = 0; //do not store backtraces + +$warnings = 0; + +// Import from auth_user_quick +echo "\nImporting mobile phone numbers from auth_user_quick...\n"; +$phones = XDB::iterRow("SELECT user_id, profile_mobile_pub, profile_mobile FROM auth_user_quick WHERE profile_mobile <> ''"); +while (list($uid, $pub, $phone) = $phones->next()) { + $fmt_phone = format_phone_number($phone); + if($fmt_phone != '') + { + $display = format_display_number($fmt_phone, $error); + if (!XDB::execute("INSERT INTO telephone (uid, link_type, link_id, tel_id, tel_type, search_tel, display_tel, pub) + VALUES ({?}, 'user', 0, 0, 'mobile', {?}, {?}, {?})", $uid, $fmt_phone, $display, $pub)) { + echo "WARNING: insert of profile mobile phone number failed for user $uid.\n"; + $warnings++; + } + } +} + + +// Import from entreprises +echo "\nImporting professional phone numbers from entreprises...\n"; +$phones = XDB::iterator("SELECT uid, entrid, tel, fax, mobile, tel_pub FROM entreprises ORDER BY uid"); +while ($row = $phones->next()) { + $request = "INSERT INTO telephone (uid, link_type, link_id, tel_id, tel_type, search_tel, display_tel, pub) + VALUES ({?}, 'pro', {?}, {?}, {?}, {?}, {?}, {?})"; + $fmt_fixed = format_phone_number($row['tel']); + $fmt_mobile = format_phone_number($row['mobile']); + $fmt_fax = format_phone_number($row['fax']); + if ($fmt_fixed != '') + { + $disp_fixed = format_display_number($fmt_fixed, $error); + if (!XDB::execute($request, $row['uid'], $row['entrid'], 0, 'fixed', $fmt_fixed, $disp_fixed, $row['tel_pub'])) { + echo 'WARNING: insert of professional fixed phone number failed for user ' . $row['uid'] . ' and entreprise ' . $row['entrid'] . ".\n"; + $warnings++; + } + } + if ($fmt_mobile != '') + { + $disp_mobile = format_display_number($fmt_mobile, $error); + if (!XDB::execute($request, $row['uid'], $row['entrid'], 1, 'mobile', $fmt_mobile, $disp_mobile, $row['tel_pub'])) { + echo 'WARNING: insert of professional mobile number failed for user ' . $row['uid'] . ' and entreprise ' . $row['entrid'] . ".\n"; + $warnings++; + } + } + if ($fmt_fax != '') + { + $disp_fax = format_display_number($fmt_fax, $error); + if (!XDB::execute($request, $row['uid'], $row['entrid'], 2, 'fax', $fmt_fax, $disp_fax, $row['tel_pub'])) { + echo 'WARNING: insert of professional fax number failed for user ' . $row['uid'] . ' and entreprise ' . $row['entrid'] . ".\n"; + $warnings++; + } + } +} + + +//import from tels +echo "\nImporting personnal phone numbers from tels...\n"; +$phones = XDB::iterator("SELECT uid, adrid, telid, tel_type, tel_pub, tel FROM tels"); +$conversions = array(); +$autre_count = 0; +while ($row = $phones->next()) { + $fmt_phone = format_phone_number($row['tel']); + if ($fmt_phone != '') { + $display = format_display_number($fmt_phone, $error); + $guess_type = guess_phone_type($row['tel_type'], $fmt_phone); + + switch ($guess_type) { + case 'fixed': + case 'fax': + case 'mobile': + if (!XDB::execute("INSERT INTO telephone (uid, link_type, link_id, tel_id, tel_type, search_tel, display_tel, pub) + VALUES ({?}, 'address', {?}, {?}, {?}, {?}, {?}, {?})", + $row['uid'], $row['adrid'], $row['telid'], $guess_type, $fmt_phone, $display, $row['tel_pub'])) { + echo 'WARNING: insert of address phone number failed for user ' . $row['uid'] . ', address ' . $row['adrid'] + . ' and telephone id ' . $row['telid'] . ".\n"; + $warnings++; + } else { + if ($row['tel_type'] == 'Autre') { + $autre_count++; + } else if (!isset($conversions[$row['tel_type']])) { + $conversions[$row['tel_type']] = $guess_type; + } + } + break; + case 'conflict': + echo 'WARNING: conflict for user ' . $row['uid'] . ', address ' . $row['adrid'] + . ' and telephone id ' . $row['telid'] . ': type = "' . $row['tel_type'] + . '", number = "' .$fmt_phone . "\"\n"; + $warnings++; + break; + case 'unknown': + default: + echo 'WARNING: unknown phone type (' . $row['tel_type'] . ') for user ' . $row['uid'] . ', address ' . $row['adrid'] + . ' and telephone id ' . $row['telid'] . "\n"; + $warnings++; + } + } +} + +echo "\nSummary of automatic phone type conversion\n"; +foreach ($conversions as $old => $new) { + echo "* $old => $new\n"; +} +echo "There was also $autre_count conversions from old type 'Autre' to a new one determined by the phone number.\n"; + + + +//end of import +if ($warnings) { + echo "\n----------------------------------------------------------------------\n" + . " There is $warnings phone numbers that couldn't be imported.\n" + . " They need to be manually inserted.\n"; +} +echo "\nAfter solving any import problem and checking automatic conversions,\n" + . "you can drop useless columns and tables by these requests:\n" + . "DROP TABLE IF EXISTS `tels`;\n" + . "ALTER TABLE `auth_user_quick` DROP COLUMN `profile_mobile`;\n" + . "ALTER TABLE `auth_user_quick` DROP COLUMN `profile_mobile_pub`;\n" + . "ALTER TABLE `entreprises` DROP COLUMN `tel`;\n" + . "ALTER TABLE `entreprises` DROP COLUMN `fax`;\n" + . "ALTER TABLE `entreprises` DROP COLUMN `mobile`;\n" + . "ALTER TABLE `entreprises` DROP COLUMN `tel_pub`;\n"; + + +// auxilliary functions + +function guess_phone_type($str_type, $phone) +{ + $str_type = strtolower(trim($str_type)); + + // special case for phone type 'autre', guessing by phone number + if ($str_type == 'autre') { + if (substr($phone, 3) == '336') { + return 'mobile'; + } else { + return 'fixed'; + } + } + + if ((strpos($str_type, 'mob') !== false) || (strpos($str_type, 'cell') !== false) || (strpos($str_type, 'port') !== false)) { + if (substr($phone, 3) == '336' || substr($phone, 2) != '33') { + return 'mobile'; //for France check if number is a mobile one + } else { + return 'conflict'; + } + } + if (strpos($str_type, 'fax') !== false) { + if(substr($phone, 3) == '336') { + return 'conflict'; + } else { + return 'fax'; + } + } + if ((strpos($str_type, 'fixe') !== false) || (strpos($str_type, 'tél') !== false) || (strpos($str_type, 'tel') !== false)) { + if(substr($phone, 3) == '336') { + return 'conflict'; + } else { + return 'fixed'; + } + } + + return 'unknown'; +} + +/* vim:set et sw=4 sts=4 ts=4: */ +?> diff --git a/upgrade/fusionax-0.0.1/update.sh b/upgrade/fusionax-0.0.1/update.sh index de82fc0..cbdc91e 100755 --- a/upgrade/fusionax-0.0.1/update.sh +++ b/upgrade/fusionax-0.0.1/update.sh @@ -17,6 +17,12 @@ done ########################################################### +echo "Importing phone numbers" + +./phones.php + +########################################################### + echo "we will now upgrade the search table (this may be a long operation) please hit ^D to continue -- 2.1.4