[platal.git] / upgrade / newdirectory-0.0.1 / phones.php

#!/usr/bin/php5
<?php
require_once 'connect.db.inc.php';
require_once 'profil.func.inc.php';

$globals->debug = 0; //do not store backtraces

// Convert phone prefixes from varchar to int
$prefixes = XDB::iterRow('SELECT  iso_3166_1_a2, phonePrefix
                            FROM  geoloc_countries
                           WHERE  phonePrefix IS NOT NULL');
XDB::execute('ALTER TABLE  geoloc_countries
               ADD COLUMN  tmpPhonePrefix SMALLINT UNSIGNED NULL');
while (list($id, $pref) = $prefixes->next()) {
    $pref = preg_replace('/[^0-9]/', '', $pref);
    if ($pref[0] == '1') {
        $pref = '1';
    }
    if ($pref[0] == '7') {
        $pref = '7';
    }
    if ($pref != '' && strlen($pref) < 4) {
        XDB::execute('UPDATE  geoloc_countries
                         SET  tmpPhonePrefix = {?}
                       WHERE  iso_3166_1_a2 = {?}', $pref, $id);
    }
}

// geoloc_pays post operations
// Drops old prefix column
XDB::execute('ALTER TABLE  geoloc_countries
              DROP COLUMN  phonePrefix');
// Renames temporary column
XDB::execute('ALTER TABLE  geoloc_countries
            CHANGE COLUMN  tmpPhonePrefix phonePrefix SMALLINT UNSIGNED NULL AFTER nationality');
// Adds an index on phonePrefix column
XDB::execute('ALTER TABLE  geoloc_countries
                ADD INDEX  (phonePrefix)');
// Adds French phone prefix
XDB::execute('UPDATE  geoloc_countries
                 SET  phonePrefix = \'33\'
               WHERE  iso_3166_1_a2 = \'FR\'');
// Adds some phone formats
XDB::execute('UPDATE  geoloc_countries
                 SET  phoneFormat = \'0# ## ## ## ##\'
               WHERE  phonePrefix = \'33\'');    //France
XDB::execute('UPDATE  geoloc_countries
                 SET  phoneFormat = \'(+p) ### ### ####\'
               WHERE  phonePrefix = \'1\'');  //USA and NANP countries


//Phone number import

$warnings = 0;

// Import from auth_user_quick
echo "\nImporting mobile phone numbers from auth_user_quick...\n";
$phones = XDB::iterRow('SELECT  ap.pid, q.profile_mobile_pub, q.profile_mobile
                          FROM  #x4dat#.auth_user_quick AS q
                    INNER JOIN  account_profiles        AS ap ON (q.user_id = ap.uid AND FIND_IN_SET(\'owner\', ap.perms))
                         WHERE  q.profile_mobile <> \'\'');
while (list($pid, $pub, $phone) = $phones->next()) {
    $pub = ($pub == '' ? 'private' : $pub);
    $fmt_phone = format_phone_number($phone);
    if ($fmt_phone != '') {
        $display = format_display_number($fmt_phone, $error);
        if (!XDB::execute('INSERT INTO  profile_phones (pid, link_type, link_id, tel_id, tel_type, search_tel, display_tel, pub)
                                VALUES  ({?}, \'user\', 0, 0, \'mobile\', {?}, {?}, {?})', $pid, $fmt_phone, $display, $pub)) {
            echo "WARNING: insert of profile mobile phone number failed for profile $pid.\n";
            ++$warnings;
        }
    }
}


// Import from entreprises
echo "\nImporting professional phone numbers from entreprises...\n";
$phones = XDB::iterator('SELECT  ap.pid, e.entrid, e.tel, e.fax, e.mobile, e.tel_pub
                           FROM  #x4dat#.entreprises AS e
                     INNER JOIN  account_profiles    AS ap ON (e.uid = ap.uid AND FIND_IN_SET(\'owner\', ap.perms))
                       ORDER BY  ap.pid');
while ($row = $phones->next()) {
    $row['tel_pub'] = ($row['tel_pub'] == '' ? 'private' : $row['tel_pub']);
    $request = 'INSERT INTO  profile_phones (pid, 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['pid'], $row['entrid'], 0, 'fixed', $fmt_fixed, $disp_fixed, $row['tel_pub'])) {
            echo 'WARNING: insert of professional fixed phone number failed for profile ' . $row['pid'] . ' and entreprise ' . $row['entrid'] . ".\n";
            ++$warnings;
        }
    }
    if ($fmt_mobile != '') {
        $disp_mobile = format_display_number($fmt_mobile, $error);
        if (!XDB::execute($request, $row['pid'], $row['entrid'], 1, 'mobile', $fmt_mobile, $disp_mobile, $row['tel_pub'])) {
            echo 'WARNING: insert of professional mobile number failed for profile ' . $row['pid'] . ' and entreprise ' . $row['entrid'] . ".\n";
            $warnings++;
        }
    }
    if ($fmt_fax != '') {
        $disp_fax    = format_display_number($fmt_fax, $error);
        if (!XDB::execute($request, $row['pid'], $row['entrid'], 2, 'fax', $fmt_fax, $disp_fax, $row['tel_pub'])) {
            echo 'WARNING: insert of professional fax number failed for profile ' . $row['pid'] . ' and entreprise ' . $row['entrid'] . ".\n";
            $warnings++;
        }
    }
}


//import from tels
echo "\nImporting personnal phone numbers from tels...\n";
$phones = XDB::iterator('SELECT  ap.pid, t.adrid, t.telid, t.tel_type, t.tel_pub, t.tel
                           FROM  #x4dat#.tels     AS t
                     INNER JOIN  account_profiles AS ap ON (t.uid = ap.uid AND FIND_IN_SET(\'owner\', ap.perms))');
$conversions = array();
$other_count = 0;
while ($row = $phones->next()) {
    $row['tel_pub'] = ($row['tel_pub'] == '' ? 'private' : $row['tel_pub']);
    $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 profile_phones (pid, link_type, link_id, tel_id, tel_type, search_tel, display_tel, pub)
                                     VALUES ({?}, \'address\', {?}, {?}, {?}, {?}, {?}, {?})',
                              $row['pid'], $row['adrid'], $row['telid'], $guess_type, $fmt_phone, $display, $row['tel_pub'])) {
                echo  'WARNING: insert of address phone number failed for profile ' . $row['pid'] . ', address ' . $row['adrid']
                    . ' and telephone id ' . $row['telid'] . ".\n";
                ++$warnings;
            } else {
                if ($row['tel_type'] == 'Autre') {
                    ++$other_count;
                } else if (!isset($conversions[$row['tel_type']])) {
                    $conversions[$row['tel_type']] = $guess_type;
                }
            }
            break;
        case 'conflict':
            echo  'WARNING: conflict for profile ' . $row['pid'] . ', 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 profile ' . $row['pid'] . ', 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 $other_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";
}

// 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) || (strpos($str_type, 'ptb') !== 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) || (strpos($str_type, 'free') !== false)) {
        if (substr($phone, 3) == '336') {
            return 'conflict';
        } else {
            return 'fixed';
        }
    }

    return 'unknown';
}

/* vim:set et sw=4 sts=4 ts=4: */
?>
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)
b0145685 SJ	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	}
de15699b GB	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'])) {
b0145685 SJ	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'])) {
b0145685 SJ	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 {
de15699b GB	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']
de15699b GB	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)
b0145685 SJ	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)
b0145685 SJ	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	?>