From b235d980e6d9b3e1e815da91465b2a6a32f46191 Mon Sep 17 00:00:00 2001 From: Guillaume Bandet Date: Sat, 21 Jun 2008 16:28:59 +0200 Subject: [PATCH] Modifies database structure for phones --- bin/cron/checkdb.php | 12 ++- bin/cron/phones.check.php | 105 ++++++++++++++++++++++++++ configs/platal.cron.in | 1 + include/profil.func.inc.php | 102 ++++++++++++++----------- include/user.func.inc.php | 107 ++++++++++++++++++--------- include/userset.inc.php | 1 - include/webservices/manageurs.server.inc.php | 27 ++++--- modules/geoloc.php | 46 ++++++++++++ modules/payment/money/paypal.inc.php | 4 +- modules/profile.php | 64 ---------------- modules/profile/addresses.inc.php | 6 +- modules/profile/general.inc.php | 6 +- modules/profile/jobs.inc.php | 14 ++-- modules/search/classes.inc.php | 6 +- modules/search/search.inc.php | 2 +- templates/admin/index.tpl | 4 +- upgrade/fusionax-0.0.1/03_telephone.sql | 65 ++-------------- upgrade/fusionax-0.0.1/phones.php | 59 +++++++++++---- 18 files changed, 378 insertions(+), 253 deletions(-) create mode 100755 bin/cron/phones.check.php diff --git a/bin/cron/checkdb.php b/bin/cron/checkdb.php index fcb56b5..5103817 100755 --- a/bin/cron/checkdb.php +++ b/bin/cron/checkdb.php @@ -81,7 +81,7 @@ check("SELECT u.user_id, nom, prenom, promo, OR (profile_medals_pub != 'private' AND profile_medals_pub != 'public')", "Utilisateur n'ayant pas de flag de publicite pour leurs donnees de profil"); check("select uid from adresses where pub != 'private' and pub !='ax' and pub != 'public'", "Utiliseur n'ayant pas de flag de publicite pour une adresse"); -check("select uid from telephone where pub != 'private' and pub != 'ax' and pub != 'public'", "Utiliseur n'ayant pas de flag de publicite pour un numero de telephone"); +check("select uid from profile_phones where pub != 'private' and pub != 'ax' and pub != 'public'", "Utiliseur n'ayant pas de flag de publicite pour un numero de téléphone"); check("select uid from profile_networking where pub != 'private' and pub != 'public'", "Utiliseur n'ayant pas de flag de publicité pour une adresse de networking"); /* validite de aliases */ @@ -118,6 +118,16 @@ check("select g.* from groupesx_ins as g left join groupesx_def as gd on g.gid=g /* validite de photo */ check("select p.* from photo as p left join auth_user_md5 as u on u.user_id=p.uid where u.prenom is null"); +/* validite des formats téléphoniques */ +check("SELECT DISTINCT g.phoneprf from geoloc_pays AS g + WHERE EXISTS (SELECT h.phoneprf + FROM geoloc_pays AS h + WHERE h.phoneprf = g.phoneprf AND h.phoneformat != (SELECT i.phoneformat + FROM geoloc_pays AS i + WHERE i.phoneprf = g.phoneprf + LIMIT 1))", + "Préfixes téléphoniques qui ont des formats de numéros de téléphones différents selon les pays"); + /* validite des champ pays et region */ check("SELECT a.uid, a.country FROM adresses AS a LEFT JOIN geoloc_pays AS gp ON a.country = gp.a2 WHERE gp.pays IS NULL","donne la liste des pays dans les profils qui n'ont pas d'entree correspondante dans geoloc_pays"); /* les régions ne sont valides que dans les adresses pros */ diff --git a/bin/cron/phones.check.php b/bin/cron/phones.check.php new file mode 100755 index 0000000..6f8e14d --- /dev/null +++ b/bin/cron/phones.check.php @@ -0,0 +1,105 @@ +#!/usr/bin/php5 +debug = 0; //do not store backtraces + + +function do_update_by_block($values) +{ + // Update display_tel by block + // Because there is no mysql update syntax for multiple updates in one query + // we use a multiple insert syntax which will fail because the key already exist + // and then update the display_tel + XDB::execute("INSERT INTO profile_phones (uid, link_type, link_id, tel_id ,tel_type, + search_tel, display_tel, pub, comment) + VALUES " . $values . " + ON DUPLICATE KEY UPDATE display_tel = VALUES(display_tel)"); +} + +$res = XDB::query("SELECT DISTINCT g.phoneprf FROM geoloc_pays AS g WHERE g.phoneprf IS NOT NULL"); +$prefixes = $res->fetchColumn(); +foreach ($prefixes as $i => $prefix) { + $res = XDB::query("SELECT g.phoneformat FROM geoloc_pays AS g + WHERE g.phoneprf = {?} AND g.phoneformat != '' LIMIT 1", + $prefix); + if ($res->numRows() > 0) { + $format = $res->fetchOneCell(); + //Build regexp for mysql query + $len = strlen($format); + $regexp = "^"; + $nbPar = 0; + for ($i = 0; $i < $len; $i++) { + $char = $format[$i]; + switch ($char) { + case 'p': + $regexp .= $prefix; + break; + case '#': + if ($nbPar == 0) { + $regexp .= '('; + $nbPar++; + } + $regexp .= '[0-9]('; + $nbPar++; + break; + default: + //Appends the char after escaping it if necessary + $escape = array('[', ']', '{', '}', '(', ')', '*', '+', '?', '.', '^', '$', '|', '\\'); + if (in_array($char, $escape)) { + $regexp .= '[' . $char . ']'; + } else { + $regexp .= $char; + } + } + } + //allows additionnal spaces and numbers + $regexp .= '[0-9 ]*'; + //closes parenthesis + for ($i = 0; $i < $nbPar; $i++) { + $regexp .= ')?'; + } + $regexp .= '$'; + $res = XDB::iterator("SELECT uid, link_type, link_id, tel_id, tel_type, search_tel, + display_tel, pub, comment + FROM profile_phones + WHERE search_tel LIKE {?} AND display_tel NOT REGEXP {?}", + $prefix . '%', $regexp); + if ($res->numRows() > 0) + { + //To speed up the update of phone numbers, theses updates are grouped by block of 1000 + $values = ''; + $i = 0; + while ($phone = $res->next()) { + $disp = format_display_number($phone['search_tel'], $error, array('format' => $format, 'phoneprf' => $prefix)); + if ($values != '') { + $values .= ",\n"; + } + $values .= "('" . addslashes($phone['uid']) . "', '" . addslashes($phone['link_type']) + . "', '" . addslashes($phone['link_id']) + . "', '" . addslashes($phone['tel_id']) . "', '" . addslashes($phone['tel_type']) + . "', '" . addslashes($phone['search_tel']) . "', '" . addslashes($disp) + . "', '" . addslashes($phone['pub']) . "', '" . addslashes($phone['comment']) . "')"; + $i++; + if ($i == 1000) { + do_update_by_block($values); + $values = ''; + $i = 0; + } + } + if ($values != '') { + do_update_by_block($values); + } + } + } +} + +// vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8: +?> diff --git a/configs/platal.cron.in b/configs/platal.cron.in index fafa58b..0994ace 100644 --- a/configs/platal.cron.in +++ b/configs/platal.cron.in @@ -7,6 +7,7 @@ WD=/home/web/prod/platal/bin/cron 0 21 * * 1-6 web cd $WD; ./checkdb.php | mail -e -s "verifications sur la BDD de plat/al @VERSION@" br@staff.m4x.org 0 21 * * 0 web cd $WD; ./checkdb.php -v | mail -e -s "verifications verbose sur la BDD de plat/al @VERSION@" br@staff.m4x.org 0 20 * * * web cd $WD; ./emails.check.php | mail -e -s "qualite de l'annuaire" br@staff.m4x.org +0 22 * * * web cd $WD; ./phones.check.php > /dev/null # inscription report 0 6 * * 1 web cd $WD; ./rapports_inscription.php diff --git a/include/profil.func.inc.php b/include/profil.func.inc.php index ac4bc39..5e084be 100644 --- a/include/profil.func.inc.php +++ b/include/profil.func.inc.php @@ -62,14 +62,12 @@ function diff_user_details(&$a, &$b, $view = 'private') { // compute $c = $a - $ switch ($val) { case 'adr' : if (!($c['adr'] = diff_user_addresses($a[$val], $bvar, $view))) unset($c['adr']); break; case 'adr_pro' : if (!($c['adr_pro'] = diff_user_pros($a[$val], $bvar, $view))) unset($c['adr_pro']); break; - case 'mobile' : if (same_tel($a[$val], $bvar)) unset($c['mobile']); break; + case 'tels' : if (!($c['tels'] = diff_user_tels($a[$val], $bvar, $view))) unset($c['tels']); break; } } } } - // don't modify mobile if you don't have the right - if (isset($b['mobile_pub']) && !has_user_right($b['mobile_pub'], $view) && isset($c['mobile'])) - unset($c['mobile']); + // don't modify freetext if you don't have the right if (isset($b['freetext_pub']) && !has_user_right($b['freetext_pub'], $view) && isset($c['freetext'])) unset($c['freetext']); if (!count($c)) @@ -124,49 +122,57 @@ function diff_user_tel(&$a, &$b) { return $c; } -function diff_user_address($a, $b) { - if (isset($b['pub']) && isset($a['pub']) && has_user_right($b['pub'], $a['pub'])) - $a['pub'] = $b['pub']; - if (isset($b['tels'])) { - $bvar = $b['tels']; - - $telids_b = array(); - foreach ($bvar as $i => $telb) $telids_b[$telb['telid']] = $i; +function diff_user_tels(&$a, &$b) +{ + $c = $a; + $telids_b = array(); + foreach ($b as $i => $telb) $telids_b[$telb['telid']] = $i; - if (isset($a['tels'])) - $avar = $a['tels']; - else - $avar = array(); - $ctels = $avar; - foreach ($avar as $j => $tela) { - if (isset($tela['telid'])) { - // if b has a tel with the same telid, compute diff - if (isset($telids_b[$tela['telid']])) { - if (!($ctels[$j] = diff_user_tel($tela, $varb[$telids_b[$tela['adrid']]]))) - unset($ctels[$j]); - unset($telids_b[$tela['telid']]); + foreach ($a as $j => $tela) { + if (isset($tela['telid'])) { + // if b has a tel with the same telid, compute diff + if (isset($telids_b[$tela['telid']])) { + if (!($c[$j] = diff_user_tel($tela, $b[$telids_b[$tela['adrid']]]))) { + unset($c[$j]); } - } else { - // try to find a match in b - foreach ($bvar as $i => $telb) { - if (same_tel($tela['tel'], $telb['tel'])) { - $tela['telid'] = $telb['telid']; - if (!($ctels[$j] = diff_user_tel($tela, $telb))) - unset($ctels[$j]); - unset($telids_b[$tela['telid']]); - break; + unset($telids_b[$tela['telid']]); + } + } else { + // try to find a match in b + foreach ($b as $i => $telb) { + if (same_tel($tela['tel'], $telb['tel'])) { + $tela['telid'] = $telb['telid']; + if (!($c[$j] = diff_user_tel($tela, $telb))) { + unset($c[$j]); } + unset($telids_b[$tela['telid']]); + break; } } } + } - foreach ($telids_b as $telidb => $i) - $ctels[] = array('telid' => $telidb, 'remove' => 1); + foreach ($telids_b as $telidb => $i) + $c[] = array('telid' => $telidb, 'remove' => 1); + return $c; +} + +function diff_user_address($a, $b) { + if (isset($b['pub']) && isset($a['pub']) && has_user_right($b['pub'], $a['pub'])) + $a['pub'] = $b['pub']; + if (isset($b['tels'])) { + if (isset($a['tels'])) { + $avar = $a['tels']; + } else { + $avar = array(); + } + $ctels = diff_user_tels($avar, $b['tels']); if (!count($ctels)) { $b['tels'] = $avar; - } else + } else { $a['tels'] = $ctels; + } } foreach ($a as $val => $avar) { @@ -227,13 +233,18 @@ function diff_user_pro($a, &$b, $view = 'private') { } if (isset($b['adr_pub']) && isset($a['adr_pub']) && has_user_right($b['adr_pub'], $a['adr_pub'])) $a['adr_pub'] = $b['adr_pub']; - if (isset($b['tel_pub']) && !has_user_right($b['tel_pub'], $view)) { - unset($a['tel']); - unset($a['fax']); - unset($a['mobile']); + if (isset($b['tels'])) { + if (isset($a['tels'])) + $avar = $a['tels']; + else + $avar = array(); + $ctels = diff_user_tels($avar, $b['tels']); + + if (!count($ctels)) { + $b['tels'] = $avar; + } else + $a['tels'] = $ctels; } - if (isset($b['tel_pub']) && isset($a['tel_pub']) && has_user_right($b['tel_pub'], $a['tel_pub'])) - $a['tel_pub'] = $b['tel_pub']; if (isset($b['email_pub']) && !has_user_right($b['email_pub'], $view)) unset($a['email']); if (isset($b['email_pub']) && isset($a['email_pub']) && has_user_right($b['email_pub'], $a['email_pub'])) @@ -304,9 +315,10 @@ function format_display_number($tel, &$error, $format = array('format'=>'','phon $ret = ''; $tel_length = strlen($tel); if((!isset($format['phoneprf'])) || ($format['phoneprf'] == '')) { - $res = XDB::query("SELECT phoneprf, format - FROM phone_formats - WHERE phoneprf = {?} OR phoneprf = {?} OR phoneprf = {?}", + $res = XDB::query("SELECT phoneprf, phoneformat AS format + FROM geoloc_pays + WHERE phoneprf = {?} OR phoneprf = {?} OR phoneprf = {?} + LIMIT 1", substr($tel, 0, 1), substr($tel, 0, 2), substr($tel, 0, 3)); if ($res->numRows() == 0) { $error = true; diff --git a/include/user.func.inc.php b/include/user.func.inc.php index 0fdd3ae..6ebfe8a 100644 --- a/include/user.func.inc.php +++ b/include/user.func.inc.php @@ -41,7 +41,7 @@ function user_clear_all_subs($user_id, $really_del=true) 'user_id' => array('requests', 'user_changes')); if ($really_del) { - array_push($tables_to_clear['uid'], 'emails', 'groupex.membres', 'contacts', 'adresses', 'telephone', + array_push($tables_to_clear['uid'], 'emails', 'groupex.membres', 'contacts', 'adresses', 'profile_phones', 'photo', 'perte_pass', 'langues_ins', 'forums.abos', 'forums.profils'); array_push($tables_to_clear['user_id'], 'newsletter_ins', 'auth_user_quick', 'binets_ins'); $tables_to_clear['id'] = array('aliases'); @@ -273,9 +273,9 @@ function get_user_details_pro($uid, $view = 'private') LEFT JOIN fonctions_def AS f ON(e.fonction = f.id) LEFT JOIN geoloc_pays AS gp ON (gp.a2 = e.country) LEFT JOIN geoloc_region AS gr ON (gr.a2 = e.country and gr.region = e.region) - LEFT JOIN telephone AS tt ON(tt.uid = e.uid AND tt.link_type = 'pro' AND tt.link_id = entrid AND tt.tel_id = 0) - LEFT JOIN telephone AS tf ON(tf.uid = e.uid AND tf.link_type = 'pro' AND tf.link_id = entrid AND tf.tel_id = 1) - LEFT JOIN telephone AS tm ON(tm.uid = e.uid AND tm.link_type = 'pro' AND tm.link_id = entrid AND tm.tel_id = 2) + LEFT JOIN profile_phones AS tt ON(tt.uid = e.uid AND tt.link_type = 'pro' AND tt.link_id = entrid AND tt.tel_id = 0) + LEFT JOIN profile_phones AS tf ON(tf.uid = e.uid AND tf.link_type = 'pro' AND tf.link_id = entrid AND tf.tel_id = 1) + LEFT JOIN profile_phones AS tm ON(tm.uid = e.uid AND tm.link_type = 'pro' AND tm.link_id = entrid AND tm.tel_id = 2) WHERE e.uid = {?} ORDER BY e.entrid"; $res = XDB::query($sql, $uid); @@ -366,8 +366,8 @@ function get_user_details_adr($uid, $view = 'private') { $adrid_index[$adr['adrid']] = $i; } - $sql = "SELECT link_id AS adrid, pub AS tel_pub, tel_type, display_tel AS tel, tel_id - FROM telephone AS t + $sql = "SELECT link_id AS adrid, pub AS tel_pub, tel_type, display_tel AS tel, tel_id AS telid + FROM profile_phones AS t WHERE uid = {?} AND link_type = 'address' ORDER BY link_id, tel_type DESC, tel_id"; $restel = XDB::iterator($sql, $uid); @@ -413,7 +413,7 @@ function &get_user_details($login, $from_uid = '', $view = 'private') LEFT JOIN photo AS p ON (p.uid = u.user_id) LEFT JOIN mentor AS m ON (m.uid = u.user_id) LEFT JOIN emails AS e ON (e.uid = u.user_id AND e.flags='active') - LEFT JOIN telephone AS t ON (t.uid = u.user_id AND link_type = 'user' AND link_id = 0 AND tel_id = 0) + LEFT JOIN profile_phones AS t ON (t.uid = u.user_id AND link_type = 'user' AND link_id = 0 AND tel_id = 0) WHERE a.alias = {?} GROUP BY u.user_id"; $res = XDB::query($reqsql, $from_uid, $login); @@ -527,7 +527,7 @@ function add_user_address($uid, $adrid, $adr) { if (isset($adr['tels']) && is_array($adr['tels'])) { $telid = 0; foreach ($adr['tels'] as $tel) if ($tel['tel']) { - add_user_tel($uid, $adrid, $telid, $tel); + add_user_tel($uid, 'address', $adrid, $telid, $tel); $telid ++; } } @@ -545,17 +545,17 @@ function update_user_address($uid, $adrid, $adr) { $adr['adr1'], $adr['adr2'], $adr['adr3'], $adr['postcode'], $adr['city'], $adr['pub'], $adrid, $uid); if (isset($adr['tels']) && is_array($adr['tels'])) { - $res = XDB::query("SELECT tel_id FROM telephone WHERE uid = {?} AND link_type = 'address' AND link_id = {?} ORDER BY tel_id", $uid, $adrid); + $res = XDB::query("SELECT tel_id FROM profile_phones WHERE uid = {?} AND link_type = 'address' AND link_id = {?} ORDER BY tel_id", $uid, $adrid); $telids = $res->fetchColumn(); foreach ($adr['tels'] as $tel) { if (isset($tel['telid']) && isset($tel['remove']) && $tel['remove']) { - remove_user_tel($uid, $adrid, $tel['telid']); + remove_user_tel($uid, 'address', $adrid, $tel['telid']); if (isset($telids[$tel['telid']])) unset($telids[$tel['telid']]); } else if (isset($tel['telid'])) { - update_user_tel($uid, $adrid, $tel['telid'], $tel); + update_user_tel($uid, 'address', $adrid, $tel['telid'], $tel); } else { for ($telid = 0; isset($telids[$telid]) && ($telids[$telid] == $telid); $telid++); - add_user_tel($uid, $adrid, $telid, $tel); + add_user_tel($uid, 'address', $adrid, $telid, $tel); } } } @@ -564,41 +564,41 @@ function update_user_address($uid, $adrid, $adr) { // {{{ function remove_user_address() function remove_user_address($uid, $adrid) { XDB::execute("DELETE FROM adresses WHERE adrid = {?} AND uid = {?}", $adrid, $uid); - XDB::execute("DELETE FROM telephone WHERE adrid = {?} AND uid = {?} AND link_type = 'address'", $adrid, $uid); + XDB::execute("DELETE FROM profile_phones WHERE link_id = {?} AND uid = {?} AND link_type = 'address'", $adrid, $uid); } // }}} // {{{ function add_user_tel() -function add_user_tel($uid, $adrid, $telid, $tel) { +function add_user_tel($uid, $link_type, $link_id, $telid, $tel) { require('profil.func.inc.php'); $fmt_phone = format_phone_number($tel['tel']); $disp_phone = format_display_number($fmt_phone, $error); - XDB::execute("INSERT INTO telephone (uid, link_type, link_id, tel_id, tel_type, search_tel, display_tel, pub) - VALUES ({?}, 'address', {?}, {?}, {?}, {?}, {?}, {?})", - $uid, $adrid, $telid, $tel['tel_type'], $fmt_phone, $disp_phone, $tel['tel_pub']); + XDB::execute("INSERT INTO profile_phones (uid, link_type, link_id, tel_id, tel_type, search_tel, display_tel, pub) + VALUES ({?}, {?}, {?}, {?}, {?}, {?}, {?}, {?})", + $uid, $link_type, $link_id, $telid, $tel['tel_type'], $fmt_phone, $disp_phone, $tel['tel_pub']); } // }}} // {{{ function update_user_tel() -function update_user_tel($uid, $adrid, $telid, $tel) { +function update_user_tel($uid, $link_type, $link_id, $telid, $tel) { require('profil.func.inc.php'); $fmt_phone = format_phone_number($tel['tel']); $disp_phone = format_display_number($fmt_phone, $error); - XDB::execute("UPDATE telephone SET search_tel = {?}, display_tel = {?}, tel_type = {?}, pub = {?} - WHERE link_type = 'address' AND tel_id = {?} AND link_id = {?} AND uid = {?}", + XDB::execute("UPDATE profile_phones SET search_tel = {?}, display_tel = {?}, tel_type = {?}, pub = {?} + WHERE link_type = {?} AND tel_id = {?} AND link_id = {?} AND uid = {?}", $fmt_phone, $disp_phone, $tel['tel_type'], $tel['tel_pub'], - $telid, $adrid, $uid); + $link_type, $telid, $link_id, $uid); } // }}} // {{{ function remove_user_tel() -function remove_user_tel($uid, $adrid, $telid) { - XDB::execute("DELETE FROM telephone WHERE tel_id = {?} AND link_id = {?} AND uid = {?} AND link_type = 'address'", - $telid, $adrid, $uid); +function remove_user_tel($uid, $link_type, $link_id, $telid) { + XDB::execute("DELETE FROM profile_phones WHERE tel_id = {?} AND link_id = {?} AND uid = {?} AND link_type = {?}", + $telid, $link_id, $uid, $link_type); } // }}} // {{{ function add_user_pro() function add_user_pro($uid, $entrid, $pro) { XDB::execute( "INSERT INTO entreprises (`uid`, `entrid`, `entreprise`, `poste`, `secteur`, `ss_secteur`, `fonction`, - `adr1`, `adr2`, `adr3`, `postcode`, `city`, `country`, `region`, `tel`, `fax`, `mobile`, `email`, `web`, `pub`, `adr_pub`, `tel_pub`, `email_pub`) + `adr1`, `adr2`, `adr3`, `postcode`, `city`, `country`, `region`, `email`, `web`, `pub`, `adr_pub`, `email_pub`) SELECT u.user_id, {?}, {?}, {?}, s.id, ss.id, f.id, {?}, {?}, {?}, {?}, {?}, gp.a2, gr.region, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?} FROM auth_user_md5 AS u @@ -610,10 +610,19 @@ function add_user_pro($uid, $entrid, $pro) { WHERE u.user_id = {?} LIMIT 1", $entrid, $pro['entreprise'], $pro['poste'], - $pro['adr1'], $pro['adr2'], $pro['adr3'], $pro['postcode'], $pro['city'], $pro['tel'], $pro['fax'], $pro['mobile'], $pro['email'], $pro['web'], $pro['pub'], $pro['adr_pub'], $pro['tel_pub'], $pro['email_pub'], + $pro['adr1'], $pro['adr2'], $pro['adr3'], $pro['postcode'], $pro['city'], $pro['email'], $pro['web'], $pro['pub'], $pro['adr_pub'], $pro['email_pub'], $pro['secteur'], $pro['sous_secteur'], $pro['fonction'], $pro['fonction'], $pro['countrytxt'], $pro['countrytxt'], $pro['region'], $uid); + if (isset($pro['tels']) && is_array($pro['tels'])) { + $telid = 0; + foreach ($pro['tels'] as $tel) { + if ($pro['tel']) { + add_user_tel($uid, 'pro', $entrid, $telid, $tel); + $telid ++; + } + } + } } // }}} // {{{ function update_user_pro() @@ -651,13 +660,6 @@ function update_user_pro($uid, $entrid, $pro) { $args_set[] = $pro['adr_pub']; } - if (isset($pro['tel'])) { - $set .= ", e.`tel` = {?}, e.`fax` = {?}, e.`mobile` = {?}, e.tel_pub = {?}"; - $args_set[] = $pro['tel']; - $args_set[] = $pro['fax']; - $args_set[] = $pro['mobile']; - $args_set[] = $pro['tel_pub']; - } if (isset($pro['email'])) { $set .= ", e.`email` = {?}, e.`email_pub` = {?}"; $args_set[] = $pro['email']; @@ -667,14 +669,32 @@ function update_user_pro($uid, $entrid, $pro) { $args_where = array($uid, $entrid); $args = array_merge(array($query), $args_join, $args_set, $args_where); call_user_func_array(array('XDB', 'execute'), $args); + + + if (isset($pro['tels']) && is_array($pro['tels'])) { + $res = XDB::query("SELECT tel_id FROM profile_phones WHERE uid = {?} AND link_type = 'pro' AND link_id = {?} ORDER BY tel_id", $uid, $entrid); + $telids = $res->fetchColumn(); + foreach ($pro['tels'] as $tel) { + if (isset($tel['telid']) && isset($tel['remove']) && $tel['remove']) { + remove_user_tel($uid, 'pro', $entrid, $tel['telid']); + if (isset($telids[$tel['telid']])) unset($telids[$tel['telid']]); + } else if (isset($tel['telid'])) { + update_user_tel($uid, 'pro', $entrid, $tel['telid'], $tel); + } else { + for ($telid = 0; isset($telids[$telid]) && ($telids[$telid] == $telid); $telid++); + add_user_tel($uid, 'pro', $entrid, $telid, $tel); + } + } + } } // }}} // {{{ function remove_user_pro() function remove_user_pro($uid, $entrid) { XDB::execute("DELETE FROM entreprises WHERE entrid = {?} AND uid = {?}", $entrid, $uid); + XDB::execute("DELETE FROM profile_phones WHERE link_id = {?} AND uid = {?} AND link_type = 'pro'", $entrid, $uid); } // }}} -// {{{ function set_user_details() +// {{{ function set_user_details_addresses() function set_user_details_addresses($uid, $adrs) { $res = XDB::query("SELECT adrid FROM adresses WHERE uid = {?} AND adrid >= 1 ORDER BY adrid", $uid); $adrids = $res->fetchColumn(); @@ -719,9 +739,6 @@ function set_user_details($uid, $details) { if (isset($details['nom_usage'])) { XDB::execute("UPDATE auth_user_md5 SET nom_usage = {?} WHERE user_id = {?}", strtoupper($details['nom_usage']), $uid); } - if (isset($details['mobile'])) { - XDB::execute("UPDATE auth_user_quick SET profile_mobile = {?} WHERE user_id = {?}", $details['mobile'], $uid); - } if (isset($details['nationalite'])) { XDB::execute( "UPDATE auth_user_md5 AS u @@ -752,6 +769,22 @@ function set_user_details($uid, $details) { $uid, $groupex); } } + if (isset($details['tels']) && is_array($details['tels'])) { + $res = XDB::query("SELECT tel_id FROM profile_phones WHERE uid = {?} AND link_type = 'user' ORDER BY tel_id", $uid); + $telids = $res->fetchColumn(); + foreach ($details['tels'] as $tel) { + if (isset($tel['telid']) && isset($tel['remove']) && $tel['remove']) { + remove_user_tel($uid, 'user', 0, $tel['telid']); + if (isset($telids[$tel['telid']])) unset($telids[$tel['telid']]); + } else if (isset($tel['telid'])) { + update_user_tel($uid, 'user', 0, $tel['telid'], $tel); + } else { + for ($telid = 0; isset($telids[$telid]) && ($telids[$telid] == $telid); $telid++); + add_user_tel($uid, 'user', 0, $telid, $tel); + } + } + } + // applis // medals } diff --git a/include/userset.inc.php b/include/userset.inc.php index 3829090..cdaf28d 100644 --- a/include/userset.inc.php +++ b/include/userset.inc.php @@ -386,7 +386,6 @@ class GadgetView implements PlView { return "u.user_id AS id, u.*, a.alias AS forlife," . - (S::logged() ? "q.profile_mobile AS mobile, " : "IF(q.profile_mobile_pub = 'public', q.profile_mobile, NULL) as mobile, ") . "u.perms != 'pending' AS inscrit, u.perms != 'pending' AS wasinscrit, u.deces != 0 AS dcd, u.deces, diff --git a/include/webservices/manageurs.server.inc.php b/include/webservices/manageurs.server.inc.php index bed509a..89b1b59 100644 --- a/include/webservices/manageurs.server.inc.php +++ b/include/webservices/manageurs.server.inc.php @@ -27,15 +27,16 @@ function get_annuaire_infos($method, $params) { //on ne recupere pas les adresses inutilement if(!isset($params[2])){ $res = XDB::iterRow( - "SELECT q.profile_mobile AS cell, a.naissance AS age - FROM auth_user_md5 AS a - INNER JOIN auth_user_quick AS q USING (user_id) - WHERE a.matricule = {?}", $params[1]); + "SELECT ph.display_tel AS cell, a.naissance AS age + FROM auth_user_md5 AS a + INNER JOIN auth_user_quick AS q USING (user_id) + LEFT JOIN profile_phones AS ph ON (ph.uid = a.user_id AND link_type='user' AND tel_type = 'mobile') + WHERE a.matricule = {?} LIMIT 1", $params[1]); $array = $res->next(); } else{ $res = XDB::iterRow( - "SELECT q.profile_mobile AS cell, a.naissance AS age, + "SELECT a.naissance AS age, adr.adr1, adr.adr2, adr.adr3, adr.postcode, adr.city, adr.country, adr.uid, adr.adrid @@ -48,10 +49,18 @@ function get_annuaire_infos($method, $params) { FIND_IN_SET('res-secondaire', adr.statut), NOT FIND_IN_SET('courrier', adr.statut)", $params[1]); //traitement des adresses si necessaire - if(list($cell, $age, $adr['adr1'], $adr['adr2'], $adr['adr3'], $adr['cp'], $adr['ville'], + if(list($age, $adr['adr1'], $adr['adr2'], $adr['adr3'], $adr['cp'], $adr['ville'], $adr['pays'], $uid, $adr['adrid']) = $res->next()) { - $array['cell'] = $cell; + $sql = XDB::query("SELECT display_tel + FROM profile_phones + WHERE uid ={?} AND link_type = 'user' AND tel_type = 'mobile' + LIMIT 1", $uid); + if ($sql->numRows()>0) { + $array['cell'] = $sql->fetchOneCell(); + } else { + $array['cell'] =''; + } $array['age'] = $age; $array['adresse'][] = $adr; @@ -61,7 +70,7 @@ function get_annuaire_infos($method, $params) { if ($adresse != 1) { //on ne veut pas la premiere adresse $i = 2; - while(list($cell, $age, $adr['adr1'], $adr['adr2'], $adr['adr3'], $adr['cp'], $adr['ville'], + while(list($age, $adr['adr1'], $adr['adr2'], $adr['adr3'], $adr['cp'], $adr['ville'], $adr['pays'], , $adr['adrid']) = $res->next()) { if($adresse == $i){//si on veut cette adresse en particulier @@ -82,7 +91,7 @@ function get_annuaire_infos($method, $params) { // on rajoute les numéros de tels $restel = XDB::iterator( "SELECT t.display_tel AS tel, t.tel_type, t.link_id as adrid - FROM telephone AS t + FROM profile_phones AS t INNER JOIN adresses AS a ON (t.link_id = a.adrid AND t.uid = a.uid) WHERE t.uid = {?} AND t.link_type = 'address' AND NOT FIND_IN_SET('pro', a.statut)", $uid); while ($tel = $restel->next()) $array['adresse'][$adrid_index[$tel['adrid']]]['tels'][] = $tel; diff --git a/modules/geoloc.php b/modules/geoloc.php index 895c5f4..4180fc2 100644 --- a/modules/geoloc.php +++ b/modules/geoloc.php @@ -27,6 +27,7 @@ class GeolocModule extends PLModule 'geoloc' => $this->make_hook('default', AUTH_COOKIE), 'admin/geoloc' => $this->make_hook('admin', AUTH_MDP, 'admin'), 'admin/geoloc/dynamap' => $this->make_hook('admin_dynamap', AUTH_MDP, 'admin'), + 'admin/geoloc/country' => $this->make_hook('admin_country', AUTH_MDP, 'admin') ); } @@ -107,6 +108,51 @@ class GeolocModule extends PLModule $page->assign("no_coordinates", $noCoordinates); } + function handler_admin_country(&$page, $action = 'list', $id = null) + { + $page->assign('xorg_title', 'Polytechnique.org - Administration - Pays'); + $page->assign('title', 'Gestion des pays'); + $table_editor = new PLTableEditor('admin/geoloc/country', 'geoloc_pays', 'a2', true); + $table_editor->describe('a2', 'alpha-2', true); + $table_editor->describe('a3', 'alpha-3', false); + $table_editor->describe('n3', 'ISO numeric', false); + $table_editor->describe('num', 'num', false); + $table_editor->describe('worldrgn', 'Continent', false); + $table_editor->describe('subd', 'Subdivisions territoriales', false); + $table_editor->describe('post', 'post', false); + $table_editor->describe('pays', 'Nom (fr)', true); + $table_editor->describe('country', 'Nom (en)', true); + $table_editor->describe('phoneprf', 'Préfixe téléphonique', true); + $table_editor->describe('phoneformat', 'Format du téléphone (ex: (+p) ### ## ## ##)', false); + $table_editor->describe('capital', 'Capitale', true); + $table_editor->describe('nat', 'Nationalité', true); + $table_editor->describe('display', 'Format des adresses', false); + + if ($action == 'update') { + if (Post::has('a2') && (Post::v('a2') == $id) && Post::has('phoneprf') && (Post::v('phoneprf') != '')) { + if (Post::has('phoneformat')) { + $new_format = Post::v('phoneformat'); + } else { + $new_format = ''; + } + $res = XDB::query("SELECT phoneformat + FROM geoloc_pays + WHERE phoneprf = {?} + LIMIT 1", + Post::v('phoneprf')); + $old_format = $res->fetchOneCell(); + if ($new_format != $old_format) { + require_once("profil.func.inc.php"); + XDB::execute("UPDATE geoloc_pays + SET phoneformat = {?} + WHERE phoneprf = {?}", + $new_format, Post::v('phoneprf')); + } + } + } + $table_editor->apply($page, $action, $id); + } + } // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8: diff --git a/modules/payment/money/paypal.inc.php b/modules/payment/money/paypal.inc.php index 465ead1..8b4060e 100644 --- a/modules/payment/money/paypal.inc.php +++ b/modules/payment/money/paypal.inc.php @@ -83,8 +83,8 @@ class PayPal IF(t1.display_tel != '', t1.display_tel, t2.display_tel) AS night_phone_b FROM auth_user_quick AS q LEFT JOIN adresses AS a ON (q.user_id = a.uid AND FIND_IN_SET('active', a.statut)) - LEFT JOIN telephone AS t1 ON (t1.uid = a.uid AND t1.link_type = 'address' AND t1.link_id = a.adrid) - LEFT JOIN telephone AS t2 ON (t2.uid = a.uid AND t2.link_type = 'user' AND t2.link_id = 0) + LEFT JOIN profile_phones AS t1 ON (t1.uid = a.uid AND t1.link_type = 'address' AND t1.link_id = a.adrid) + LEFT JOIN profile_phones AS t2 ON (t2.uid = a.uid AND t2.link_type = 'user' AND t2.link_id = 0) WHERE q.user_id = {?} LIMIT 1", S::v('uid')); $this->infos['client'] = array_map('replace_accent', array_merge($info_client, $res->fetchOneAssoc())); diff --git a/modules/profile.php b/modules/profile.php index ba1a4f2..e3d86cd 100644 --- a/modules/profile.php +++ b/modules/profile.php @@ -64,7 +64,6 @@ class ProfileModule extends PLModule 'admin/trombino' => $this->make_hook('admin_trombino', AUTH_MDP, 'admin'), 'admin/ss_secteurs' => $this->make_hook('admin_ss_secteurs', AUTH_MDP, 'admin'), 'admin/fonctions' => $this->make_hook('admin_fonctions', AUTH_MDP, 'admin'), - 'admin/phones_format_display' => $this->make_hook('admin_phones_format_display', AUTH_MDP, 'admin'), ); } @@ -830,69 +829,6 @@ class ProfileModule extends PLModule $table_editor->describe('link', 'lien web', true); $table_editor->apply($page, $action, $id); } - function handler_admin_phones_format_display(&$page, $action = 'list', $id = null) { - $page->assign('xorg_title', 'Polytechnique.org - Administration - Format d\'affichage des numéros de téléphone '); - $page->assign('title', 'Gestion des formats d\'affichage des numéros de téléphone'); - $table_editor = new PLTableEditor('admin/phones_format_display', 'phone_formats', 'phoneprf',true); - $table_editor->describe('phoneprf', 'Préfixe International', true); - $table_editor->describe('format', 'format d\'Affichage (ex: (+p) ### ## ## ##)', true); - if ($action == 'update') { - if ((Post::has('phoneprf')) && (Post::v('phoneprf') == $id)) { - $res = XDB::query("SELECT format - FROM phone_formats - WHERE phoneprf = {?}", - $id); - $old_val = $res->fetchOneCell(); - if (Post::has('format')) { - $new_val = Post::v('format'); - } else { - $new_val = ''; - } - if ($new_val != $old_val){ - require_once("profil.func.inc.php"); - XDB::execute("UPDATE phone_formats - SET format = {?} - WHERE phoneprf = {?}", - $new_val, $id); - $prefixe = $id . '%'; - $res = XDB::iterator("SELECT uid, link_type, link_id, tel_id, tel_type, search_tel, pub - FROM telephone - WHERE search_tel LIKE {?}", - $prefixe); - $req = ''; - $i = 0; - while($phonenumber = $res->next()) { - if ($req != '') { - $req .= ",\n"; - } - $req .= "('" . addslashes($phonenumber['uid']) . "', '" . addslashes($phonenumber['link_type']) . "', '" . addslashes($phonenumber['link_id']) - . "', '" . addslashes($phonenumber['tel_id']) . "', '" . addslashes($phonenumber['tel_type']) - . "', '" . addslashes($phonenumber['search_tel']) . "', '" - . addslashes(format_display_number($phonenumber['search_tel'], &$erreur, array('format' => $new_val, 'phoneprf' => $id))) - . "', '" . addslashes($phonenumber['pub']) . "')"; - $i++; - if( $i == 1000) { - XDB::execute("INSERT INTO telephone(uid, link_type, link_id, tel_id ,tel_type, - search_tel, display_tel, pub) - VALUES " . $req . " - ON DUPLICATE KEY UPDATE display_tel = VALUES(display_tel)"); - $req = ''; - $i = 0; - } - } - if ($req != '') { - XDB::execute("INSERT INTO telephone(uid, link_type, link_id, tel_id ,tel_type, - search_tel, display_tel, pub) - VALUES " . $req . " - ON DUPLICATE KEY UPDATE display_tel = VALUES(display_tel)"); - } - } - } - $action = 'list'; - $id = null; - } - $table_editor->apply($page, $action, $id); - } function handler_admin_medals(&$page, $action = 'list', $id = null) { $page->assign('xorg_title','Polytechnique.org - Administration - Distinctions'); $page->assign('title', 'Gestion des Distinctions'); diff --git a/modules/profile/addresses.inc.php b/modules/profile/addresses.inc.php index 7458d3e..6dfd2bb 100644 --- a/modules/profile/addresses.inc.php +++ b/modules/profile/addresses.inc.php @@ -111,7 +111,7 @@ class ProfileAddress extends ProfileGeoloc private function saveTel($adrid, $telid, array &$tel) { - XDB::execute("INSERT INTO telephone (uid, link_type, link_id, tel_id, tel_type, + XDB::execute("INSERT INTO profile_phones (uid, link_type, link_id, tel_id, tel_type, search_tel, display_tel, pub) VALUES ({?}, 'address', {?}, {?}, {?}, {?}, {?}, {?})", @@ -163,7 +163,7 @@ class ProfileAddress extends ProfileGeoloc XDB::execute("DELETE FROM adresses WHERE uid = {?}", S::i('uid')); - XDB::execute("DELETE FROM telephone + XDB::execute("DELETE FROM profile_phones WHERE uid = {?} AND link_type = 'address'", S::i('uid')); foreach ($value as $adrid=>&$address) { @@ -209,7 +209,7 @@ class ProfileAddresses extends ProfilePage } $res = XDB::iterator("SELECT link_id AS adrid, tel_type AS type, pub, display_tel AS tel - FROM telephone + FROM profile_phones WHERE uid = {?} AND link_type = 'address' ORDER BY link_id", S::i('uid')); diff --git a/modules/profile/general.inc.php b/modules/profile/general.inc.php index 1351848..3c45445 100644 --- a/modules/profile/general.inc.php +++ b/modules/profile/general.inc.php @@ -274,7 +274,7 @@ class ProfileGeneral extends ProfilePage FROM auth_user_md5 AS u INNER JOIN auth_user_quick AS q ON(u.user_id = q.user_id) INNER JOIN profile_names_display AS n ON(n.user_id = u.user_id) - LEFT JOIN telephone AS t ON(u.user_id = t.uid AND link_type = 'user') + LEFT JOIN profile_phones AS t ON(u.user_id = t.uid AND link_type = 'user') LEFT JOIN profile_directory AS d ON(d.uid = u.user_id) LEFT JOIN applis_ins AS a1 ON(a1.uid = u.user_id and a1.ordre = 0) LEFT JOIN applis_ins AS a2 ON(a2.uid = u.user_id and a2.ordre = 1) @@ -343,11 +343,11 @@ class ProfileGeneral extends ProfilePage if ($this->changed['mobile'] || $this->changed['mobile_pub']) { require_once('profil.func.inc.php'); $fmt_phone = format_phone_number($this->values['mobile']); - XDB::execute("DELETE FROM telephone + XDB::execute("DELETE FROM profile_phones WHERE uid = {?} AND link_type = 'user'", S::v('uid')); if ($fmt_phone != '') { - XDB::execute("INSERT INTO telephone (uid, link_type, link_id, tel_id, tel_type, search_tel, display_tel, pub) + XDB::execute("INSERT INTO profile_phones (uid, link_type, link_id, tel_id, tel_type, search_tel, display_tel, pub) VALUES ({?}, 'user', '0', '0', 'mobile', {?}, {?}, {?})", S::v('uid'), $fmt_phone, $this->values['mobile'], $this->values['mobile_pub']); } diff --git a/modules/profile/jobs.inc.php b/modules/profile/jobs.inc.php index 051b6e8..ef5abff 100644 --- a/modules/profile/jobs.inc.php +++ b/modules/profile/jobs.inc.php @@ -105,7 +105,7 @@ class ProfileJob extends ProfileGeoloc XDB::execute("DELETE FROM entreprises WHERE uid = {?}", S::i('uid')); - XDB::execute("DELETE FROM telephone + XDB::execute("DELETE FROM profile_phones WHERE uid = {?} AND link_type = 'pro'", S::i('uid')); $i = 0; @@ -136,21 +136,21 @@ class ProfileJob extends ProfileGeoloc $job['adr']['checked'] ? 'geoloc' : '', $job['adr']['precise_lat'], $job['adr']['precise_lon']); if ($job['tel'] != '') { - XDB::execute("INSERT INTO telephone (uid, link_type, link_id, tel_id, + XDB::execute("INSERT INTO profile_phones (uid, link_type, link_id, tel_id, tel_type, search_tel, display_tel, pub) VALUES ({?}, 'pro', {?}, 0, 'fixed', {?}, {?}, {?})", S::i('uid'), $i, format_phone_number($job['tel']), $job['tel'], $job['tel_pub']); } if ($job['fax'] != '') { - XDB::execute("INSERT INTO telephone (uid, link_type, link_id, tel_id, + XDB::execute("INSERT INTO profile_phones (uid, link_type, link_id, tel_id, tel_type, search_tel, display_tel, pub) VALUES ({?}, 'pro', {?}, 1, 'fax', {?}, {?}, {?})", S::i('uid'), $i, format_phone_number($job['fax']), $job['fax'], $job['tel_pub']); } if ($job['mobile'] != '') { - XDB::execute("INSERT INTO telephone (uid, link_type, link_id, tel_id, + XDB::execute("INSERT INTO profile_phones (uid, link_type, link_id, tel_id, tel_type, search_tel, display_tel, pub) VALUES ({?}, 'pro', {?}, 2, 'mobile', {?}, {?}, {?})", @@ -195,9 +195,9 @@ class ProfileJobs extends ProfilePage tf.display_tel AS fax, tm.display_tel AS mobile FROM entreprises AS e LEFT JOIN geoloc_pays AS gp ON(gp.a2 = e.country) - LEFT JOIN telephone AS tt ON(tt.uid = e.uid AND tt.link_type = 'pro' AND tt.link_id = entrid AND tt.tel_id = 0) - LEFT JOIN telephone AS tf ON(tf.uid = e.uid AND tf.link_type = 'pro' AND tf.link_id = entrid AND tf.tel_id = 1) - LEFT JOIN telephone AS tm ON(tm.uid = e.uid AND tm.link_type = 'pro' AND tm.link_id = entrid AND tm.tel_id = 2) + LEFT JOIN profile_phones AS tt ON(tt.uid = e.uid AND tt.link_type = 'pro' AND tt.link_id = entrid AND tt.tel_id = 0) + LEFT JOIN profile_phones AS tf ON(tf.uid = e.uid AND tf.link_type = 'pro' AND tf.link_id = entrid AND tf.tel_id = 1) + LEFT JOIN profile_phones AS tm ON(tm.uid = e.uid AND tm.link_type = 'pro' AND tm.link_id = entrid AND tm.tel_id = 2) WHERE e.uid = {?} AND entreprise != '' ORDER BY entrid", S::i('uid')); $this->values['jobs'] = array(); diff --git a/modules/search/classes.inc.php b/modules/search/classes.inc.php index 607d1b5..af91c2a 100644 --- a/modules/search/classes.inc.php +++ b/modules/search/classes.inc.php @@ -40,7 +40,6 @@ require_once("xorg.misc.inc.php"); // hide private information if not logged if (S::logged()) $globals->search->result_fields .=' - q.profile_mobile AS mobile, q.profile_freetext AS freetext, adr.city, gp.pays AS countrytxt, gr.name AS region, e.entreprise, @@ -48,7 +47,6 @@ if (S::logged()) nwe.name AS networking_name,'; else $globals->search->result_fields .=" - IF(q.profile_mobile_pub='public', q.profile_mobile, '') AS mobile, IF(q.profile_freetext_pub='public', q.profile_freetext, '') AS freetext, IF(adr.pub='public', adr.city, '') AS city, IF(adr.pub='public', gp.pays, '') AS countrytxt, @@ -354,9 +352,9 @@ class QuickSearch extends SField } if (!empty($this->phone)) { if (!S::logged()) { - $join .= "INNER JOIN telephone AS t ON (t.uid = u.user_id AND t.pub = 'public')"; + $join .= "INNER JOIN profile_phones AS t ON (t.uid = u.user_id AND t.pub = 'public')"; } else { - $join .= "INNER JOIN telephone AS t ON (t.uid = u.user_id)"; + $join .= "INNER JOIN profile_phones AS t ON (t.uid = u.user_id)"; } } return $join; diff --git a/modules/search/search.inc.php b/modules/search/search.inc.php index 7db59a7..c8e62fe 100644 --- a/modules/search/search.inc.php +++ b/modules/search/search.inc.php @@ -79,7 +79,7 @@ function advancedSearchFromInput() } else { $nwTypeField = new IndexSField('networking_type', array('nwe.network_type'), 'profile_networking_enum', 'nwe', 'nwe.network_type = nw.network_type'); } - $nwPhoneField = new PhoneSField('phone_number', array('t.search_tel'), 'telephone', 't', 't.uid=u.user_id'); + $nwPhoneField = new PhoneSField('phone_number', array('t.search_tel'), 'profile_phones', 't', 't.uid=u.user_id'); return array( $nameField, $firstnameField, $nicknameField, $promo1Field, $promo2Field, $womanField, $subscriberField, $aliveField, diff --git a/templates/admin/index.tpl b/templates/admin/index.tpl index 36dbf56..7ce8ca8 100644 --- a/templates/admin/index.tpl +++ b/templates/admin/index.tpl @@ -108,8 +108,6 @@ Fonctions   |   Networking -   |   - Téléphone @@ -150,6 +148,8 @@ Synchro   |   Dynamap +   |   + Pays diff --git a/upgrade/fusionax-0.0.1/03_telephone.sql b/upgrade/fusionax-0.0.1/03_telephone.sql index 33fe966..aa27a27 100644 --- a/upgrade/fusionax-0.0.1/03_telephone.sql +++ b/upgrade/fusionax-0.0.1/03_telephone.sql @@ -1,4 +1,4 @@ -CREATE TABLE IF NOT EXISTS `telephone` ( +CREATE TABLE IF NOT EXISTS `profile_phones` ( `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', @@ -11,63 +11,10 @@ CREATE TABLE IF NOT EXISTS `telephone` ( 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', ''); +-- Adds a temporary column to convert phone prefixes from varchar to int +ALTER TABLE `geoloc_pays` ADD COLUMN `tmp_phoneprf` smallint unsigned NULL; + +-- Adds phone format column +ALTER TABLE `geoloc_pays` ADD COLUMN `phoneformat` varchar(25) NOT NULL AFTER `nat`; diff --git a/upgrade/fusionax-0.0.1/phones.php b/upgrade/fusionax-0.0.1/phones.php index aae2eb9..6eea87c 100755 --- a/upgrade/fusionax-0.0.1/phones.php +++ b/upgrade/fusionax-0.0.1/phones.php @@ -10,6 +10,39 @@ require_once 'profil.func.inc.php'; $globals->debug = 0; //do not store backtraces + +// Convert phone prefixes from varchar to int +$prefixes = XDB::iterRow("SELECT a2, phoneprf FROM geoloc_pays WHERE phoneprf IS NOT 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_pays SET tmp_phoneprf = {?} WHERE a2 = {?}", $pref, $id); + } +} + +// geoloc_pays post operations +// Drops old prfix column +XDB::execute("ALTER TABLE geoloc_pays DROP COLUMN phoneprf"); +// Renames temporary column +XDB::execute("ALTER TABLE geoloc_pays CHANGE COLUMN tmp_phoneprf phoneprf smallint unsigned NULL AFTER nat"); +// Adds an index on phoneprf column +XDB::execute("ALTER TABLE geoloc_pays ADD INDEX (phoneprf)"); +// Adds French phone prefix +XDB::execute("UPDATE geoloc_pays SET phoneprf = '33' WHERE a2 = 'FR'"); +// Adds some phone formats +XDB::execute("UPDATE geoloc_pays SET phoneformat = '0# ## ## ## ##' WHERE phoneprf = '33'"); //France +XDB::execute("UPDATE geoloc_pays SET phoneformat = '(+p) ### ### ####' WHERE phoneprf = '1'"); //USA and NANP countries + + + +//Phone number import + $warnings = 0; // Import from auth_user_quick @@ -17,10 +50,9 @@ 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 != '') - { + 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) + if (!XDB::execute("INSERT INTO profile_phones (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++; @@ -33,29 +65,26 @@ while (list($uid, $pub, $phone) = $phones->next()) { 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) + $request = "INSERT INTO profile_phones (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 != '') - { + 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 != '') - { + 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 != '') - { + 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"; @@ -80,7 +109,7 @@ while ($row = $phones->next()) { 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) + if (!XDB::execute("INSERT INTO profile_phones (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'] @@ -149,7 +178,7 @@ function guess_phone_type($str_type, $phone) } } - if ((strpos($str_type, 'mob') !== false) || (strpos($str_type, 'cell') !== false) || (strpos($str_type, 'port') !== false)) { + 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 { @@ -157,14 +186,14 @@ function guess_phone_type($str_type, $phone) } } if (strpos($str_type, 'fax') !== false) { - if(substr($phone, 3) == '336') { + 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') { + 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'; -- 2.1.4