From ce0b2c6f3fc4e7c524df32f56f1bc8e8d573bb1a Mon Sep 17 00:00:00 2001 From: Florent Bruneau Date: Sun, 21 Feb 2010 22:18:52 +0100 Subject: [PATCH] Fix many uid fields in profile tables created by newdirectory. Signed-off-by: Florent Bruneau --- bin/cron/checkdb.php | 6 ++-- bin/cron/phones.check.php | 6 ++-- classes/profile.php | 14 +++++----- classes/userfilter.php | 14 +++++----- include/directory.enums.inc.php | 10 +++---- include/emails.combobox.inc.php | 39 ++++++++++++-------------- include/validations/entreprises.inc.php | 6 ++-- include/validations/orange.inc.php | 4 +-- include/webservices/manageurs.server.inc.php | 22 +++++++++++++-- modules/admin.php | 8 +++--- modules/payment/money/paypal.inc.php | 4 +-- modules/profile.php | 10 +++---- modules/profile/addresses.inc.php | 4 +-- modules/profile/general.inc.php | 41 ++++++++++++---------------- modules/profile/jobs.inc.php | 28 +++++++++---------- modules/profile/mentor.inc.php | 18 ++++++------ modules/profile/page.inc.php | 6 ++-- modules/search/classes.inc.php | 16 +++++------ modules/search/search.inc.php | 10 +++---- modules/stats.php | 8 +++--- upgrade/account/01_profiles.sql | 3 ++ upgrade/newdirectory-0.0.1/02_networking.sql | 10 +++---- upgrade/newdirectory-0.0.1/03_emails.sql | 6 ---- upgrade/newdirectory-0.0.1/04_telephone.sql | 6 ++-- upgrade/newdirectory-0.0.1/06_education.sql | 8 +++--- upgrade/newdirectory-0.0.1/07_corps.sql | 10 +++---- upgrade/newdirectory-0.0.1/09_education.sql | 2 +- upgrade/newdirectory-0.0.1/10_promotion.sql | 4 +-- upgrade/newdirectory-0.0.1/11_jobs.sql | 4 +-- upgrade/newdirectory-0.0.1/13_mentoring.sql | 20 +++++++------- 30 files changed, 175 insertions(+), 172 deletions(-) diff --git a/bin/cron/checkdb.php b/bin/cron/checkdb.php index d210de9..b3bc249 100755 --- a/bin/cron/checkdb.php +++ b/bin/cron/checkdb.php @@ -84,8 +84,8 @@ check("SELECT pid FROM profile_addresses WHERE pub != 'private' AND pub !='ax' AND pub != 'public'", "Utiliseur n'ayant pas de flag de publicité pour une adresse."); -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"); +check("select pid 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 pid from profile_networking where pub != 'private' and pub != 'public'", "Utiliseur n'ayant pas de flag de publicité pour une adresse de networking"); /* validite des hruid */ check("SELECT user_id, nom, prenom, promo FROM auth_user_md5 WHERE hruid IS NULL OR hruid = ''", @@ -98,7 +98,7 @@ check("SELECT a.* WHERE (a.type='alias' OR a.type='a_vie') AND u.prenom is null"); /* validite de profile_education */ -check("select a.* from profile_education as a left join auth_user_md5 as u on u.user_id=a.uid where u.prenom is null"); +check("select a.* from profile_education as a left join auth_user_md5 as u on u.user_id=a.pid where u.prenom is null"); check("select a.* from profile_education as a left join profile_education_enum as ad on ad.id=a.eduid where ad.name is null"); /* validite de binet_users */ diff --git a/bin/cron/phones.check.php b/bin/cron/phones.check.php index 62fa4e5..b661e5e 100755 --- a/bin/cron/phones.check.php +++ b/bin/cron/phones.check.php @@ -13,7 +13,7 @@ function do_update_by_block($values) // 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, + XDB::execute("INSERT INTO profile_phones (pid, 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)"); @@ -65,7 +65,7 @@ foreach ($prefixes as $i => $prefix) { $regexp .= ')?'; } $regexp .= '$'; - $res = XDB::iterator("SELECT uid, link_type, link_id, tel_id, tel_type, search_tel, + $res = XDB::iterator("SELECT pid, 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 {?}", @@ -80,7 +80,7 @@ foreach ($prefixes as $i => $prefix) { if ($values != '') { $values .= ",\n"; } - $values .= "('" . addslashes($phone['uid']) . "', '" . addslashes($phone['link_type']) + $values .= "('" . addslashes($phone['pid']) . "', '" . addslashes($phone['link_type']) . "', '" . addslashes($phone['link_id']) . "', '" . addslashes($phone['tel_id']) . "', '" . addslashes($phone['tel_type']) . "', '" . addslashes($phone['search_tel']) . "', '" . addslashes($disp) diff --git a/classes/profile.php b/classes/profile.php index a61d73a..a1ddca2 100644 --- a/classes/profile.php +++ b/classes/profile.php @@ -286,8 +286,8 @@ class Profile LEFT JOIN geoloc_administrativeareas AS ga ON (ga.id = pa.administrativeAreaId) LEFT JOIN geoloc_administrativeareas AS gas ON (gas.id = pa.subAdministrativeAreaId) LEFT JOIN geoloc_countries AS gc ON (gc.iso_3166_1_a2 = pa.countryId) - LEFT JOIN profile_phones AS ppfix ON (ppfix.link_type = \'address\' AND ppfix.uid = pa.pid AND ppfix.link_id = pa.id AND ppfix.tel_type = \'fixed\') - LEFT JOIN profile_phones AS ppfax ON (ppfax.link_type = \'address\' AND ppfax.uid = pa.pid AND ppfax.link_id = pa.id AND ppfax.tel_type = \'fax\') + LEFT JOIN profile_phones AS ppfix ON (ppfix.link_type = \'address\' AND ppfix.pid = pa.pid AND ppfix.link_id = pa.id AND ppfix.tel_type = \'fixed\') + LEFT JOIN profile_phones AS ppfax ON (ppfax.link_type = \'address\' AND ppfax.pid = pa.pid AND ppfax.link_id = pa.id AND ppfax.tel_type = \'fax\') WHERE ' . $where . ' ORDER BY pa.id ' . $limit); @@ -308,7 +308,7 @@ class Profile */ public function getEducations($flags, $limit = null) { - $where = XDB::format('pe.uid = {?}', $this->id()); + $where = XDB::format('pe.pid = {?}', $this->id()); if ($flags & self::EDUCATION_MAIN) { $where .= ' AND FIND_IN_SET(\'primary\', pe.flags)'; } else if ($flags & self::EDUCATION_EXTRA) { @@ -344,7 +344,7 @@ class Profile public function getNetworking($flags, $limit = null) { - $where = XDB::format('pn.uid = {?}', $this->id()); + $where = XDB::format('pn.pid = {?}', $this->id()); if ($flags & self::NETWORKING_WEB) { $where .= ' AND pn.network_type = 0'; // XXX hardcoded reference to web site index } @@ -378,7 +378,7 @@ class Profile public function getJobs($flags, $limit = null) { - $where = XDB::format('pj.uid = {?}', $this->id()); + $where = XDB::format('pj.pid = {?}', $this->id()); $cond = 'TRUE'; if ($this->visibility) { $where .= ' AND pj.pub IN ' . XDB::formatArray($this->visibility); @@ -447,7 +447,7 @@ class Profile ap.uid AS owner_id FROM profiles AS p INNER JOIN profile_display AS pd ON (pd.pid = p.pid) - INNER JOIN profile_education AS pe ON (pe.uid = p.pid AND FIND_IN_SET(\'primary\', pe.flags)) + INNER JOIN profile_education AS pe ON (pe.pid = p.pid AND FIND_IN_SET(\'primary\', pe.flags)) INNER JOIN profile_name AS pn_f ON (pn_f.pid = p.pid AND pn_f.typeid = ' . self::getNameTypeId('firstname', true) . ') INNER JOIN profile_name AS pn_l ON (pn_l.pid = p.pid @@ -458,7 +458,7 @@ class Profile AND pn_ul.typeid = ' . self::getNameTypeId('lastname_ordinary', true) . ') LEFT JOIN profile_name AS pn_n ON (pn_n.pid = p.pid AND pn_n.typeid = ' . self::getNameTypeId('nickname', true) . ') - LEFT JOIN profile_phones AS pp ON (pp.uid = p.pid AND pp.link_type = \'user\' AND tel_type = \'mobile\') + LEFT JOIN profile_phones AS pp ON (pp.pid = p.pid AND pp.link_type = \'user\' AND tel_type = \'mobile\') LEFT JOIN profile_photos AS ph ON (ph.pid = p.pid) LEFT JOIN account_profiles AS ap ON (ap.pid = p.pid AND FIND_IN_SET(\'owner\', ap.perms)) WHERE p.pid IN ' . XDB::formatArray($pids) . ' diff --git a/classes/userfilter.php b/classes/userfilter.php index efb03c6..24d4c82 100644 --- a/classes/userfilter.php +++ b/classes/userfilter.php @@ -1984,11 +1984,11 @@ class UserFilter extends PlFilter } foreach ($this->pepe as $grade => $sub) { if ($this->isGrade($grade)) { - $joins['pe' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_education', '$ME.eduid = pee.id AND $ME.uid = $PID'); + $joins['pe' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_education', '$ME.eduid = pee.id AND $ME.pid = $PID'); $joins['pede' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_INNER, 'profile_education_degree_enum', '$ME.id = pe' . $sub . '.degreeid AND $ME.abbreviation LIKE ' . XDB::format('{?}', $grade)); } else { - $joins['pe' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_education', '$ME.uid = $PID'); + $joins['pe' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_education', '$ME.pid = $PID'); $joins['pee' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_INNER, 'profile_education_enum', '$ME.id = pe' . $sub . '.eduid'); $joins['pede' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_INNER, 'profile_education_degree_enum', '$ME.id = pe' . $sub . '.degreeid'); } @@ -2203,7 +2203,7 @@ class UserFilter extends PlFilter { $joins = array(); if ($this->pc) { - $joins['pc'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_corps', '$ME.uid = $UID'); + $joins['pc'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_corps', '$ME.pid = $PID'); } if ($this->pcr) { $joins['pcr'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_corps_rank_enum', '$ME.id = pc.rankid'); @@ -2278,7 +2278,7 @@ class UserFilter extends PlFilter { $joins = array(); if ($this->with_pj) { - $joins['pj'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job', '$ME.uid = $UID'); + $joins['pj'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job', '$ME.pid = $PID'); } if ($this->with_pje) { $joins['pje'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job_enum', '$ME.id = pj.jobid'); @@ -2313,7 +2313,7 @@ class UserFilter extends PlFilter { $joins = array(); if ($this->with_pnw) { - $joins['pnw'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_networking', '$ME.uid = $UID'); + $joins['pnw'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_networking', '$ME.pid = $PID'); } return $joins; } @@ -2334,7 +2334,7 @@ class UserFilter extends PlFilter { $joins = array(); if ($this->with_ptel) { - $joins['ptel'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_phones', '$ME.uid = $UID'); + $joins['ptel'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_phones', '$ME.pid = $PID'); } return $joins; } @@ -2389,7 +2389,7 @@ class UserFilter extends PlFilter { $joins = array(); foreach ($this->pms as $sub => $tab) { - $joins[$sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, $tab, '$ME.uid = $UID'); + $joins[$sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, $tab, '$ME.pid = $PID'); } return $joins; } diff --git a/include/directory.enums.inc.php b/include/directory.enums.inc.php index a5b7736..d44a111 100644 --- a/include/directory.enums.inc.php +++ b/include/directory.enums.inc.php @@ -461,7 +461,7 @@ class DE_EducationSchools extends DirEnumeration protected $from = 'profile_education_enum'; protected $ac_join = 'INNER JOIN profile_education ON (profile_education.eduid = profile_education_enum.id)'; - protected $ac_unique = 'profile_education.uid'; + protected $ac_unique = 'profile_education.pid'; } // }}} @@ -486,7 +486,7 @@ class DE_EducationFields extends DirEnumeration protected $from = 'profile_education_field_enum'; protected $ac_join = 'INNER JOIN profile_education ON (profile_education.fieldid = profile_education_field_enum.id)'; - protected $ac_unique = 'profile_education.uid'; + protected $ac_unique = 'profile_education.pid'; } // }}} @@ -553,7 +553,7 @@ class DE_Companies extends DirEnumeration protected $from = 'profile_job_enum'; protected $ac_join = 'INNER JOIN profile_job ON (profile_job.jobid = profile_job_enum.id)'; - protected $ac_unique = 'profile_job.uid'; + protected $ac_unique = 'profile_job.pid'; } // }}} @@ -564,7 +564,7 @@ class DE_Sectors extends DirEnumeration protected $from = 'profile_job_sector_enum'; protected $ac_join = 'INNER JOIN profile_job ON (profile_job_sector_enum.id = profile_job.sectorid)'; - protected $ac_unique = 'profile_job.uid'; + protected $ac_unique = 'profile_job.pid'; } // }}} @@ -590,7 +590,7 @@ class DE_Networking extends DirEnumeration protected $ac_join = 'INNER JOIN profile_networking ON (profile_networking.network_type = profile_networking_enum.network_type'; - protected $ac_unique = 'profile_networking.uid'; + protected $ac_unique = 'profile_networking.pid'; } // }}} ?> diff --git a/include/emails.combobox.inc.php b/include/emails.combobox.inc.php index 2813bea..3d9a09f 100644 --- a/include/emails.combobox.inc.php +++ b/include/emails.combobox.inc.php @@ -30,11 +30,7 @@ function fill_email_combobox(PlPage& $page, $user = null, $profile = null) $email_type = "directory"; if ($profile) { - $res = XDB::query( - "SELECT email_directory - FROM profile_directory - WHERE uid = {?}", $profile->id()); - $email_directory = $res->fetchOneCell(); + $email_directory = $profile->email_directory; if ($email_directory) { $page->assign('email_directory', $email_directory); list($alias, $domain) = explode('@', $email_directory); @@ -43,6 +39,22 @@ function fill_email_combobox(PlPage& $page, $user = null, $profile = null) $email_type = NULL; $alias = $domain = ''; } + + $res = XDB::query( + "SELECT email + FROM profile_job + WHERE pid = {?}", $profile->id()); + $res = $res->fetchAllAssoc(); + $pro = array(); + foreach ($res as $res_it) { + if ($res_it['email'] != '') { + $pro[] = $res_it['email']; + if ($email_directory == $res_it['email']) { + $email_type = "pro"; + } + } + } + $page->assign('list_email_pro', $pro); } if ($user) { @@ -81,24 +93,7 @@ function fill_email_combobox(PlPage& $page, $user = null, $profile = null) } } $page->assign('list_email_redir', $redir); - - $res = XDB::query( - "SELECT email - FROM profile_job - WHERE uid = {?}", $user->id()); - $res = $res->fetchAllAssoc(); - $pro = array(); - foreach ($res as $res_it) { - if ($res_it['email'] != '') { - $pro[] = $res_it['email']; - if ($email_directory == $res_it['email']) { - $email_type = "pro"; - } - } - } - $page->assign('list_email_pro', $pro); $page->assign('email_type', $email_type); - } else { $page->assign('list_email_X', array()); $page->assign('list_email_redir', array()); diff --git a/include/validations/entreprises.inc.php b/include/validations/entreprises.inc.php index 5a7279f..6becf7a 100644 --- a/include/validations/entreprises.inc.php +++ b/include/validations/entreprises.inc.php @@ -164,7 +164,7 @@ class EntrReq extends Validate $jobid = XDB::insertId(); $display_tel = format_display_number($this->tel, $error_tel); $display_fax = format_display_number($this->fax, $error_fax); - XDB::execute("INSERT INTO profile_phones (uid, link_type, link_id, tel_id, tel_type, + XDB::execute("INSERT INTO profile_phones (pid, link_type, link_id, tel_id, tel_type, search_tel, display_tel, pub) VALUES ({?}, 'hq', 0, 0, 'fixed', {?}, {?}, 'public'), ({?}, 'hq', 0, 1, 'fax', {?}, {?}, 'public')", @@ -195,8 +195,8 @@ class EntrReq extends Validate } return XDB::execute('UPDATE profile_job SET jobid = {?} - WHERE uid = {?} AND id = {?}', - $jobid, $this->user->id(), $this->id); + WHERE pid = {?} AND id = {?}', + $jobid, $this->user->profile()->id(), $this->id); } // }}} diff --git a/include/validations/orange.inc.php b/include/validations/orange.inc.php index 76ea94f..3c7d3fd 100644 --- a/include/validations/orange.inc.php +++ b/include/validations/orange.inc.php @@ -44,7 +44,7 @@ class OrangeReq extends Validate $this->newGradYear = $_newGradYear; $res = XDB::query("SELECT entry_year FROM profile_education - WHERE uid = {?} AND FIND_IN_SET('primary', flags)", $this->user->id()); + WHERE pid = {?} AND FIND_IN_SET('primary', flags)", $this->user->profile()->id()); $this->entryYear = $res->fetchOneCell(); $this->oldGradYear = $this->entryYear + 3; } @@ -86,7 +86,7 @@ class OrangeReq extends Validate { XDB::execute("UPDATE profile_education SET grad_year = {?} - WHERE uid = {?} AND FIND_IN_SET('primary', flags)", $this->newGradYear, $this->user->id()); + WHERE pid = {?} AND FIND_IN_SET('primary', flags)", $this->newGradYear, $this->user->profile()->id()); return true; } diff --git a/include/webservices/manageurs.server.inc.php b/include/webservices/manageurs.server.inc.php index 562f402..c561dc4 100644 --- a/include/webservices/manageurs.server.inc.php +++ b/include/webservices/manageurs.server.inc.php @@ -35,7 +35,7 @@ function get_annuaire_infos($method, $params) { "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') + LEFT JOIN profile_phones AS ph ON (ph.pid = a.user_id AND link_type='user' AND tel_type = 'mobile') WHERE a.matricule = {?} LIMIT 1", $params[1]); $array = $res->next(); } else { @@ -58,7 +58,7 @@ function get_annuaire_infos($method, $params) { explode("\n", Geocoder::getFirstLines($text, $adr['cp'], 3)); $sql = XDB::query("SELECT display_tel FROM profile_phones - WHERE uid = {?} AND link_type = 'user' AND tel_type = 'mobile' + WHERE pid = {?} AND link_type = 'user' AND tel_type = 'mobile' LIMIT 1", $uid); if ($sql->numRows() > 0) { $array['cell'] = $sql->fetchOneCell(); @@ -100,7 +100,23 @@ function get_annuaire_infos($method, $params) { "SELECT t.display_tel AS tel, t.tel_type, t.link_id as adrid FROM profile_phones AS t INNER JOIN profile_addresses AS a ON (t.link_id = a.id AND t.uid = a.pid) - WHERE t.uid = {?} AND t.link_type = 'address' + WHERE t.u + $res = XDB::query( + "SELECT email + FROM profile_job + WHERE pid = {?}", $user->id()); + $res = $res->fetchAllAssoc(); + $pro = array(); + foreach ($res as $res_it) { + if ($res_it['email'] != '') { + $pro[] = $res_it['email']; + if ($email_directory == $res_it['email']) { + $email_type = "pro"; + } + } + } + $page->assign('list_email_pro', $pro); +id = {?} 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/admin.php b/modules/admin.php index 89a81d3..068638e 100644 --- a/modules/admin.php +++ b/modules/admin.php @@ -1312,7 +1312,7 @@ class AdminModule extends PLModule $selectedJob = Env::has('selectedJob'); XDB::execute("DELETE FROM profile_phones - WHERE uid = {?} AND link_type = 'hq'", + WHERE pid = {?} AND link_type = 'hq'", $id); XDB::execute("DELETE FROM profile_addresses WHERE jobid = {?} AND type = 'hq'", @@ -1348,7 +1348,7 @@ class AdminModule extends PLModule Env::t('name'), Env::t('acronym'), Env::t('url'), Env::t('email'), Env::t('NAF_code'), Env::i('AX_code'), Env::i('holdingId'), $id); - XDB::execute("INSERT INTO profile_phones (uid, link_type, link_id, tel_id, tel_type, + XDB::execute("INSERT INTO profile_phones (pid, link_type, link_id, tel_id, tel_type, search_tel, display_tel, pub) VALUES ({?}, 'hq', 0, 0, 'fixed', {?}, {?}, 'public'), ({?}, 'hq', 0, 1, 'fax', {?}, {?}, 'public')", @@ -1379,8 +1379,8 @@ class AdminModule extends PLModule t.display_tel AS tel, f.display_tel AS fax, a.text AS address FROM profile_job_enum AS e LEFT JOIN profile_job_enum AS h ON (e.holdingid = h.id) - LEFT JOIN profile_phones AS t ON (t.uid = e.id AND link_type = 'hq' AND tel_id = 0) - LEFT JOIN profile_phones AS f ON (f.uid = e.id AND link_type = 'hq' AND tel_id = 1) + LEFT JOIN profile_phones AS t ON (t.pid = e.id AND link_type = 'hq' AND tel_id = 0) + LEFT JOIN profile_phones AS f ON (f.pid = e.id AND link_type = 'hq' AND tel_id = 1) LEFT JOIN profile_addresses AS a ON (a.jobid = e.id AND a.type = 'hq') WHERE e.id = {?}", $id); diff --git a/modules/payment/money/paypal.inc.php b/modules/payment/money/paypal.inc.php index fc0d931..45ad720 100644 --- a/modules/payment/money/paypal.inc.php +++ b/modules/payment/money/paypal.inc.php @@ -75,9 +75,9 @@ class PayPal IF(t1.display_tel != '', t1.display_tel, t2.display_tel) AS night_phone_b FROM auth_user_quick AS q LEFT JOIN profile_addresses AS a ON (q.user_id = a.pid AND FIND_IN_SET('current', a.flags)) - LEFT JOIN profile_phones AS t1 ON (t1.uid = a.uid AND t1.link_type = 'address' + LEFT JOIN profile_phones AS t1 ON (t1.pid = a.pid 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' + LEFT JOIN profile_phones AS t2 ON (t2.pid = a.pid AND t2.link_type = 'user' AND t2.link_id = 0) LEFT JOIN geoloc_localities AS l ON (l.id = a.localityId) WHERE q.user_id = {?} diff --git a/modules/profile.php b/modules/profile.php index 69dc2bf..55f4099 100644 --- a/modules/profile.php +++ b/modules/profile.php @@ -568,7 +568,7 @@ class ProfileModule extends PLModule } elseif ($promo_sortie == $promo + 3) { XDB::execute('UPDATE profile_education SET grad_year = {?} - WHERE uid = {?} AND FIND_IN_SET(\'primary\', flags)', + WHERE pid = {?} AND FIND_IN_SET(\'primary\', flags)', $promo_sortie, $profile->id()); $page->trigSuccess('Ton statut "orange" a été supprimé.'); $page->assign('promo_sortie_old', $promo_sortie); @@ -602,7 +602,7 @@ class ProfileModule extends PLModule //expertise $res = XDB::query('SELECT expertise FROM profile_mentor - WHERE uid = {?}', $user->id()); + WHERE pid = {?}', $user->id()); $page->assign('expertise', $res->fetchOneCell()); // Sectors @@ -612,7 +612,7 @@ class ProfileModule extends PLModule FROM profile_mentor_sector AS m LEFT JOIN profile_job_sector_enum AS s ON(m.sectorid = s.id) LEFT JOIN profile_job_subsector_enum AS ss ON(m.sectorid = ss.sectorid AND m.subsectorid = ss.id) - WHERE uid = {?}", $user->id()); + WHERE pid = {?}", $user->id()); while (list($sector, $subSector) = $res->next()) { $sectors[] = $sector; $subSectors[] = $subSector; @@ -625,7 +625,7 @@ class ProfileModule extends PLModule "SELECT gc.countryFR FROM profile_mentor_country AS m LEFT JOIN geoloc_countries AS gc ON (m.country = gc.iso_3166_1_a2) - WHERE uid = {?}", $user->id()); + WHERE pid = {?}", $user->id()); $page->assign('pays', $res->fetchColumn()); $page->addJsLink('close_on_esc.js'); @@ -688,7 +688,7 @@ class ProfileModule extends PLModule $it = XDB::iterator("SELECT gc.iso_3166_1_a2 AS id, gc.countryFR AS field FROM geoloc_countries AS gc INNER JOIN profile_mentor_country AS mp ON (mp.country = gc.iso_3166_1_a2) - INNER JOIN profile_mentor_sector AS ms ON (ms.uid = mp.uid) + INNER JOIN profile_mentor_sector AS ms ON (ms.pid = mp.pid) WHERE ms.sectorid = {?} " . $where . " GROUP BY iso_3166_1_a2 ORDER BY countryFR", $sect, $ssect); diff --git a/modules/profile/addresses.inc.php b/modules/profile/addresses.inc.php index ea460fc..2bc00b2 100644 --- a/modules/profile/addresses.inc.php +++ b/modules/profile/addresses.inc.php @@ -119,7 +119,7 @@ class ProfileAddress extends ProfileGeocoding WHERE pid = {?} AND type = 'home'", $page->pid()); XDB::execute("DELETE FROM profile_phones - WHERE uid = {?} AND link_type = 'address'", + WHERE pid = {?} AND link_type = 'address'", $page->pid()); foreach ($value as $addrid => &$address) { $this->saveAddress($page->pid(), $addrid, $address, 'home'); @@ -163,7 +163,7 @@ class ProfileAddresses extends ProfilePage $res = XDB::iterator("SELECT link_id AS addrid, tel_type AS type, pub, display_tel AS tel, comment FROM profile_phones - WHERE uid = {?} AND link_type = 'address' + WHERE pid = {?} AND link_type = 'address' ORDER BY link_id", $this->pid()); $i = 0; diff --git a/modules/profile/general.inc.php b/modules/profile/general.inc.php index 5e70adc..cac45ec 100644 --- a/modules/profile/general.inc.php +++ b/modules/profile/general.inc.php @@ -215,7 +215,7 @@ class ProfileEdu implements ProfileSetting $value = array(); $value = XDB::fetchAllAssoc("SELECT eduid, degreeid, fieldid, grad_year, program FROM profile_education - WHERE uid = {?} AND !FIND_IN_SET('primary', flags) + WHERE pid = {?} AND !FIND_IN_SET('primary', flags) ORDER BY id", $page->pid()); } else { @@ -240,12 +240,12 @@ class ProfileEdu implements ProfileSetting public function save(ProfilePage &$page, $field, $value) { XDB::execute("DELETE FROM profile_education - WHERE uid = {?} AND !FIND_IN_SET('primary', flags)", + WHERE pid = {?} AND !FIND_IN_SET('primary', flags)", $page->pid()); foreach ($value as $eduid=>&$edu) { if ($edu['eduid'] != '') { XDB::execute("INSERT INTO profile_education - SET id = {?}, uid = {?}, eduid = {?}, degreeid = {?}, + SET id = {?}, pid = {?}, eduid = {?}, degreeid = {?}, fieldid = {?}, grad_year = {?}, program = {?}", $eduid, $page->pid(), $edu['eduid'], $edu['degreeid'], $edu['fieldid'], $edu['grad_year'], $edu['program']); @@ -300,7 +300,7 @@ class ProfileNetworking implements ProfileSetting $value = XDB::fetchAllAssoc("SELECT n.address, n.network_type AS type, n.pub, m.name FROM profile_networking AS n INNER JOIN profile_networking_enum AS m ON (n.network_type = m.network_type) - WHERE n.uid = {?}", + WHERE n.pid = {?}", $page->pid()); } if (!is_array($value)) { @@ -338,14 +338,14 @@ class ProfileNetworking implements ProfileSetting public function save(ProfilePage &$page, $field, $value) { XDB::execute("DELETE FROM profile_networking - WHERE uid = {?}", + WHERE pid = {?}", $page->pid()); if (!count($value)) { return; } $insert = array(); foreach ($value as $id=>$network) { - XDB::execute("INSERT INTO profile_networking (uid, nwid, network_type, address, pub) + XDB::execute("INSERT INTO profile_networking (pid, nwid, network_type, address, pub) VALUES ({?}, {?}, {?}, {?}, {?})", $page->pid(), $id, $network['type'], $network['address'], $network['pub']); } @@ -391,13 +391,12 @@ class ProfileGeneral extends ProfilePage $res = XDB::query("SELECT p.promo, e.entry_year AS entry_year, e.grad_year AS grad_year, pr.nationality1, pr.nationality2, pr.nationality3, pr.birthdate, t.display_tel as mobile, t.pub as mobile_pub, - d.email_directory as email_directory, + pr.email_directory as email_directory, pr.freetext, pr.freetext_pub, pr.ax_id AS matricule_ax, p.yourself FROM profiles AS pr INNER JOIN profile_display AS p ON (p.pid = pr.pid) - INNER JOIN profile_education AS e ON (e.uid = pr.pid AND FIND_IN_SET('primary', e.flags)) - LEFT JOIN profile_phones AS t ON (t.uid = pr.pid AND link_type = 'user') - LEFT JOIN profile_directory AS d ON (d.uid = pr.pid) + INNER JOIN profile_education AS e ON (e.pid = pr.pid AND FIND_IN_SET('primary', e.flags)) + LEFT JOIN profile_phones AS t ON (t.pid = pr.pid AND link_type = 'user') WHERE pr.pid = {?}", $this->pid()); $this->values = $res->fetchOneAssoc(); if ($this->owner) { @@ -431,7 +430,8 @@ class ProfileGeneral extends ProfilePage protected function _saveData() { if ($this->changed['nationality1'] || $this->changed['nationality2'] || $this->changed['nationality3'] - || $this->changed['birthdate'] || $this->changed['freetext'] || $this->changed['freetext_pub']) { + || $this->changed['birthdate'] || $this->changed['freetext'] || $this->changed['freetext_pub'] + || $this->changed['email_directory']) { if ($this->values['nationality3'] == "") { $this->values['nationality3'] = NULL; } @@ -444,24 +444,19 @@ class ProfileGeneral extends ProfilePage $this->values['nationality2'] = $this->values['nationality3']; $this->values['nationality3'] = NULL; } + $new_email = ($this->values['email_directory'] == "new@example.org") ? + $this->values['email_directory_new'] : $this->values['email_directory']; + if ($new_email == "") { + $new_email = NULL; + } XDB::execute("UPDATE profiles SET nationality1 = {?}, nationality2 = {?}, nationality3 = {?}, birthdate = {?}, - freetext = {?}, freetext_pub = {?} + freetext = {?}, freetext_pub = {?}, email_directory = {?} WHERE pid = {?}", $this->values['nationality1'], $this->values['nationality2'], $this->values['nationality3'], preg_replace('@(\d{2})/(\d{2})/(\d{4})@', '\3-\2-\1', $this->values['birthdate']), - $this->values['freetext'], $this->values['freetext_pub'], $this->pid()); - } - if ($this->changed['email_directory']) { - $new_email = ($this->values['email_directory'] == "new@example.org") ? - $this->values['email_directory_new'] : $this->values['email_directory']; - if ($new_email == "") { - $new_email = NULL; - } - XDB::execute("REPLACE INTO profile_directory (uid, email_directory) - VALUES ({?}, {?})", - $this->pid(), $new_email); + $this->values['freetext'], $this->values['freetext_pub'], $new_email, $this->pid()); } if ($this->changed['photo_pub']) { XDB::execute("UPDATE profile_photos diff --git a/modules/profile/jobs.inc.php b/modules/profile/jobs.inc.php index d628cc8..5927666 100644 --- a/modules/profile/jobs.inc.php +++ b/modules/profile/jobs.inc.php @@ -221,27 +221,27 @@ class ProfileJob extends ProfileGeocoding require_once('validations.inc.php'); XDB::execute("DELETE FROM profile_job - WHERE uid = {?}", - S::i('uid')); + WHERE pid = {?}", + $this->pid()); XDB::execute("DELETE FROM profile_addresses WHERE pid = {?} AND type = 'job'", - S::i('uid')); + $this->pid()); XDB::execute("DELETE FROM profile_phones - WHERE uid = {?} AND link_type = 'pro'", - S::i('uid')); + WHERE pid = {?} AND link_type = 'pro'", + $this->pid()); foreach ($value as $id=>&$job) { if (isset($job['name']) && $job['name']) { if (isset($job['jobid']) && $job['jobid']) { - XDB::execute("INSERT INTO profile_job (uid, id, description, sectorid, subsectorid, + XDB::execute("INSERT INTO profile_job (pid, id, description, sectorid, subsectorid, subsubsectorid, email, url, pub, email_pub, jobid) VALUES ({?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?})", - S::i('uid'), $id, $job['description'], $job['sector'], $job['subSector'], + $this->pid(), $id, $job['description'], $job['sector'], $job['subSector'], $job['subSubSector'], $job['w_email'], $job['w_url'], $job['pub'], $job['w_email_pub'], $job['jobid']); } else { - XDB::execute("INSERT INTO profile_job (uid, id, description, sectorid, subsectorid, + XDB::execute("INSERT INTO profile_job (pid, id, description, sectorid, subsectorid, subsubsectorid, email, url, pub, email_pub) VALUES ({?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?})", - S::i('uid'), $id, $job['description'], $job['sector'], $job['subSector'], + $this->pid(), $id, $job['description'], $job['sector'], $job['subSector'], $job['subSubSector'], $job['w_email'], $job['w_url'], $job['pub'], $job['w_email_pub']); } $address = new ProfileAddress(); @@ -279,7 +279,7 @@ class ProfileJobs extends ProfilePage $res = XDB::query("SELECT original_corpsid AS original, current_corpsid AS current, rankid AS rank, corps_pub AS pub FROM profile_corps - WHERE uid = {?}", + WHERE pid = {?}", $this->pid()); $this->values['corps'] = $res->fetchOneAssoc(); @@ -298,10 +298,10 @@ class ProfileJobs extends ProfilePage FROM profile_job AS j LEFT JOIN profile_job_enum AS je ON (j.jobid = je.id) LEFT JOIN profile_job_subsubsector_enum AS s ON (s.id = j.subsubsectorid) - LEFT JOIN profile_addresses AS aw ON (aw.pid = j.uid AND aw.type = 'job' + LEFT JOIN profile_addresses AS aw ON (aw.pid = j.pid AND aw.type = 'job' AND aw.id = j.id) LEFT JOIN profile_addresses AS ah ON (ah.jobid = j.jobid AND ah.type = 'hq') - WHERE j.uid = {?} + WHERE j.pid = {?} ORDER BY j.id", $this->pid()); $this->values['jobs'] = array(); @@ -376,7 +376,7 @@ class ProfileJobs extends ProfilePage $res = XDB::iterator("SELECT link_id AS jobid, tel_type AS type, pub, display_tel AS tel, comment FROM profile_phones - WHERE uid = {?} AND link_type = 'pro' + WHERE pid = {?} AND link_type = 'pro' ORDER BY link_id", S::i('uid')); $i = 0; @@ -463,7 +463,7 @@ class ProfileJobs extends ProfilePage XDB::execute("UPDATE profile_corps SET original_corpsid = {?}, current_corpsid = {?}, rankid = {?}, corps_pub = {?} - WHERE uid = {?}", + WHERE pid = {?}", $this->values['corps']['original'], $this->values['corps']['current'], $this->values['corps']['rank'], $this->values['corps']['pub'], $this->pid()); } diff --git a/modules/profile/mentor.inc.php b/modules/profile/mentor.inc.php index d61867b..d026ed0 100644 --- a/modules/profile/mentor.inc.php +++ b/modules/profile/mentor.inc.php @@ -30,7 +30,7 @@ class ProfileSectors implements ProfileSetting FROM profile_mentor_sector AS m INNER JOIN profile_job_sector_enum AS s ON (m.sectorid = s.id) INNER JOIN profile_job_subsector_enum AS ss ON (s.id = ss.sectorid AND m.subsectorid = ss.id) - WHERE m.uid = {?}", + WHERE m.pid = {?}", $page->pid()); while (list($s, $ss, $ssname) = $res->next()) { if (!isset($value[$s])) { @@ -56,14 +56,14 @@ class ProfileSectors implements ProfileSetting { XDB::execute("DELETE FROM profile_mentor_sector - WHERE uid = {?}", + WHERE pid = {?}", $page->pid()); if (!count($value)) { return; } foreach ($value as $id => $sect) { foreach ($sect as $sid => $name) { - XDB::execute("INSERT INTO profile_mentor_sector (uid, sectorid, subsectorid) + XDB::execute("INSERT INTO profile_mentor_sector (pid, sectorid, subsectorid) VALUES ({?}, {?}, {?})", $page->pid(), $id, $sid); } @@ -81,7 +81,7 @@ class ProfileCountry implements ProfileSetting $res = XDB::iterRow("SELECT m.country, gc.countryFR FROM profile_mentor_country AS m INNER JOIN geoloc_countries AS gc ON (m.country = gc.iso_3166_1_a2) - WHERE m.uid = {?}", + WHERE m.pid = {?}", $page->pid()); while (list($id, $name) = $res->next()) { $value[$id] = $name; @@ -99,10 +99,10 @@ class ProfileCountry implements ProfileSetting public function save(ProfilePage &$page, $field, $value) { XDB::execute("DELETE FROM profile_mentor_country - WHERE uid = {?}", + WHERE pid = {?}", $page->pid()); foreach ($value as $id=>&$name) { - XDB::execute("INSERT INTO profile_mentor_country (uid, country) + XDB::execute("INSERT INTO profile_mentor_country (pid, country) VALUES ({?}, {?})", $page->pid(), $id); } @@ -126,7 +126,7 @@ class ProfileMentor extends ProfilePage { $res = XDB::query("SELECT expertise FROM profile_mentor - WHERE uid = {?}", + WHERE pid = {?}", $this->pid()); $this->values['expertise'] = $res->fetchOneCell(); } @@ -137,11 +137,11 @@ class ProfileMentor extends ProfilePage $expertise = trim($this->values['expertise']); if (empty($expertise)) { XDB::execute("DELETE FROM profile_mentor - WHERE uid = {?}", + WHERE pid = {?}", $this->pid()); $this->values['expertise'] = null; } else { - XDB::execute("REPLACE INTO profile_mentor (uid, expertise) + XDB::execute("REPLACE INTO profile_mentor (pid, expertise) VALUES ({?}, {?})", $this->pid(), $expertise); $this->values['expertise'] = $expertise; diff --git a/modules/profile/page.inc.php b/modules/profile/page.inc.php index 468c8cf..de655ec 100644 --- a/modules/profile/page.inc.php +++ b/modules/profile/page.inc.php @@ -137,7 +137,7 @@ class ProfilePhones implements ProfileSetting $value = array(); $res = XDB::iterator('SELECT display_tel AS tel, tel_type AS type, pub, comment FROM profile_phones - WHERE uid = {?} AND link_type = {?} + WHERE pid = {?} AND link_type = {?} ORDER BY tel_id', $page->pid(), $this->link_type); if ($res->numRows() > 0) { @@ -179,7 +179,7 @@ class ProfilePhones implements ProfileSetting private function saveTel($pid, $telid, array &$phone) { if ($phone['tel'] != '') { - XDB::execute("INSERT INTO profile_phones (uid, link_type, link_id, tel_id, tel_type, + XDB::execute("INSERT INTO profile_phones (pid, link_type, link_id, tel_id, tel_type, search_tel, display_tel, pub, comment) VALUES ({?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?})", @@ -191,7 +191,7 @@ class ProfilePhones implements ProfileSetting public function save(ProfilePage &$page, $field, $value) { XDB::execute("DELETE FROM profile_phones - WHERE uid = {?} AND link_type = {?} AND link_id = {?}", + WHERE pid = {?} AND link_type = {?} AND link_id = {?}", $page->pid(), $this->link_type, $this->link_id); $this->saveTels($page->pid(), $field, $value); } diff --git a/modules/search/classes.inc.php b/modules/search/classes.inc.php index d232f11..c23d1c7 100644 --- a/modules/search/classes.inc.php +++ b/modules/search/classes.inc.php @@ -60,23 +60,23 @@ else IF(nw.pub='public', nw.address, '') AS networking_address, IF(nw.pub='public', nwe.name, '') AS networking_name,"; @$globals->search->result_where_statement = " - LEFT JOIN profile_education AS edu0 ON (u.user_id = edu0.uid AND edu0.id = 0) + LEFT JOIN profile_education AS edu0 ON (u.user_id = edu0.pid AND edu0.id = 0) LEFT JOIN profile_education_enum AS ede0 ON (ede0.id = edu0.eduid) LEFT JOIN profile_education_degree_enum AS edd0 ON (edd0.id = edu0.degreeid) LEFT JOIN profile_education_field_enum AS f0 ON (f0.id = edu0.fieldid) - LEFT JOIN profile_education AS edu1 ON (u.user_id = edu1.uid AND edu1.id = 1) + LEFT JOIN profile_education AS edu1 ON (u.user_id = edu1.pid AND edu1.id = 1) LEFT JOIN profile_education_enum AS ede1 ON (ede1.id = edu1.eduid) LEFT JOIN profile_education_degree_enum AS edd1 ON (edd1.id = edu1.degreeid) LEFT JOIN profile_education_field_enum AS f1 ON (f1.id = edu1.fieldid) - LEFT JOIN profile_education AS edu2 ON (u.user_id = edu2.uid AND edu2.id = 2) + LEFT JOIN profile_education AS edu2 ON (u.user_id = edu2.pid AND edu2.id = 2) LEFT JOIN profile_education_enum AS ede2 ON (ede2.id = edu2.eduid) LEFT JOIN profile_education_degree_enum AS edd2 ON (edd2.id = edu2.degreeid) LEFT JOIN profile_education_field_enum AS f2 ON (f2.id = edu2.fieldid) - LEFT JOIN profile_education AS edu3 ON (u.user_id = edu3.uid AND edu3.id = 3) + LEFT JOIN profile_education AS edu3 ON (u.user_id = edu3.pid AND edu3.id = 3) LEFT JOIN profile_education_enum AS ede3 ON (ede3.id = edu3.eduid) LEFT JOIN profile_education_degree_enum AS edd3 ON (edd3.id = edu3.degreeid) LEFT JOIN profile_education_field_enum AS f3 ON (f3.id = edu3.fieldid) - LEFT JOIN profile_job AS e ON (e.uid = u.user_id) + LEFT JOIN profile_job AS e ON (e.pid = u.user_id) LEFT JOIN profile_job_enum AS ee ON (e.jobid = ee.id) LEFT JOIN profile_job_sector_enum AS es ON (es.id = e.sectorid) LEFT JOIN fonctions_def AS ef ON (e.fonction = ef.id) @@ -89,7 +89,7 @@ else LEFT JOIN geoloc_administrativeareas AS gr ON (adr.countryId = gr.country AND adr.administrativeAreaId = gr.id) LEFT JOIN emails AS em ON (em.uid = u.user_id AND em.flags = 'active') - LEFT JOIN profile_networking AS nw ON (nw.uid = u.user_id) + LEFT JOIN profile_networking AS nw ON (nw.pid = u.user_id) LEFT JOIN profile_networking_enum AS nwe ON (nwe.network_type = nw.network_type)"; // }}} @@ -383,9 +383,9 @@ class QuickSearch extends SField } if (!empty($this->phone)) { if (!S::logged()) { - $join .= "INNER JOIN profile_phones AS t ON (t.uid = u.user_id AND t.pub = 'public')"; + $join .= "INNER JOIN profile_phones AS t ON (t.pid = u.user_id AND t.pub = 'public')"; } else { - $join .= "INNER JOIN profile_phones AS t ON (t.uid = u.user_id)"; + $join .= "INNER JOIN profile_phones AS t ON (t.pid = u.user_id)"; } } return $join; diff --git a/modules/search/search.inc.php b/modules/search/search.inc.php index 1cc5058..1d73a17 100644 --- a/modules/search/search.inc.php +++ b/modules/search/search.inc.php @@ -51,10 +51,10 @@ function advancedSearchFromInput() $regionField = new RefSField('region',array('ar.administrativeAreaId'), 'profile_addresses', 'ar', getAddressJoin('ar')); $entrepriseField = new RefSField('entreprise', array('je.name'), '', '',''); - $posteField = new RefSField('poste', array('ep.description'), 'profile_job', 'ep', 'u.user_id = ep.uid', false); + $posteField = new RefSField('poste', array('ep.description'), 'profile_job', 'ep', 'u.user_id = ep.pid', false); $fonctionField = new RefSField('fonction', array('en.fonction_fr'), 'fonctions_def', 'en', - 'u.user_id = profile_job.uid AND fonctions_def.id = profile_job.functionid'); - $secteurField = new RefSField('secteur', array('fm.sectorid'), 'profile_job', 'fm', 'u.user_id = fm.uid'); + 'u.user_id = profile_job.pid AND fonctions_def.id = profile_job.functionid'); + $secteurField = new RefSField('secteur', array('fm.sectorid'), 'profile_job', 'fm', 'u.user_id = fm.pid'); $cvField = new RefSField('cv', array('u.cv'), '', '', '', false); $natField = new RefSField('nationalite', array('u.nationalite', 'u.nationalite2', 'u.nationalite3'), '', '', ''); @@ -68,7 +68,7 @@ function advancedSearchFromInput() $freeField = new RefSField('free', array('q.profile_freetext'), '', '', '', false); - $nwAddressField = new RefSField('networking_address', array('nw.address'), 'profile_networking', 'nw', 'nw.uid=u.user_id', false); + $nwAddressField = new RefSField('networking_address', array('nw.address'), 'profile_networking', 'nw', 'nw.pid=u.user_id', false); if (Env::v('networking_address') == '') { $nwTypeField = new IndexSField('networking_type', array('nwe.network_type'), array('profile_networking', 'profile_networking_enum'), array('nw', 'nwe'), array('nw.uid = u.user_id', 'nwe.network_type = nw.network_type')); @@ -76,7 +76,7 @@ function advancedSearchFromInput() $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'), 'profile_phones', 't', 't.uid = u.user_id'); + $nwPhoneField = new PhoneSField('phone_number', array('t.search_tel'), 'profile_phones', 't', 't.pid = u.user_id'); return array( $nameField, $promo1Field, $promo2Field, $womanField, $subscriberField, $aliveField, diff --git a/modules/stats.php b/modules/stats.php index 12152a0..24863ae 100644 --- a/modules/stats.php +++ b/modules/stats.php @@ -132,7 +132,7 @@ EOF2; FROM accounts AS a INNER JOIN account_profiles AS ap ON (ap.uid = a.uid AND FIND_IN_SET('owner', ap.perms)) INNER JOIN profiles AS p ON (p.pid = ap.pid) - INNER JOIN profile_education AS pe ON (pe.uid = ap.pid AND FIND_IN_SET('primary', pe.flags)) + INNER JOIN profile_education AS pe ON (pe.pid = ap.pid AND FIND_IN_SET('primary', pe.flags)) WHERE pe.entry_year >= {?} AND p.deathdate IS NULL GROUP BY promo", $depart); @@ -181,7 +181,7 @@ EOF2; $res = XDB::query("SELECT MIN(TO_DAYS(a.registration_date) - TO_DAYS(NOW())) FROM accounts AS a INNER JOIN account_profiles AS ap ON (ap.uid = a.uid AND FIND_IN_SET('owner', ap.perms)) - INNER JOIN profile_education AS pe ON (pe.uid = ap.pid AND FIND_IN_SET('primary', pe.flags)) + INNER JOIN profile_education AS pe ON (pe.pid = ap.pid AND FIND_IN_SET('primary', pe.flags)) WHERE pe.entry_year = {?} AND a.state = 'active'", (int)$promo); $jours = -$res->fetchOneCell(); @@ -192,7 +192,7 @@ EOF2; COUNT(a.uid) AS nb FROM accounts AS a INNER JOIN account_profiles AS ap ON (ap.uid = a.uid AND FIND_IN_SET('owner', ap.perms)) - INNER JOIN profile_education AS pe ON (pe.uid = ap.pid AND FIND_IN_SET('primary', pe.flags)) + INNER JOIN profile_education AS pe ON (pe.pid = ap.pid AND FIND_IN_SET('primary', pe.flags)) WHERE pe.entry_year = {?} AND a.state = 'active' GROUP BY jour", (int)$jours, 1 + (int)$jours, (int)$promo); @@ -253,7 +253,7 @@ EOF2; $res = XDB::iterRow('SELECT pe.entry_year AS promo, COUNT(*) FROM accounts AS a INNER JOIN account_profiles AS ap ON (ap.uid = a.uid AND FIND_IN_SET(\'owner\', ap.perms)) - INNER JOIN profile_education AS pe ON (pe.uid = ap.pid AND FIND_IN_SET(\'primary\', pe.flags)) + INNER JOIN profile_education AS pe ON (pe.pid = ap.pid AND FIND_IN_SET(\'primary\', pe.flags)) WHERE pe.entry_year >= 1900 AND a.state = \'active\' GROUP BY promo ORDER BY promo'); diff --git a/upgrade/account/01_profiles.sql b/upgrade/account/01_profiles.sql index cbf5125..040e3ee 100644 --- a/upgrade/account/01_profiles.sql +++ b/upgrade/account/01_profiles.sql @@ -28,6 +28,9 @@ create table profiles ( nationality2 char(2) default null, nationality3 char(2) default null, + # Directory informations + email_directory varchar(255) default null, + # Last modification date (for notifications) last_change date not null, diff --git a/upgrade/newdirectory-0.0.1/02_networking.sql b/upgrade/newdirectory-0.0.1/02_networking.sql index f18e05d..3aaa122 100644 --- a/upgrade/newdirectory-0.0.1/02_networking.sql +++ b/upgrade/newdirectory-0.0.1/02_networking.sql @@ -11,21 +11,21 @@ CREATE TABLE `profile_networking_enum` ( ) ENGINE=InnoDB, CHARSET=utf8, COMMENT='types of networking addresses'; -CREATE TABLE IF NOT EXISTS `profile_networking` ( - `uid` int NOT NULL COMMENT 'user id', +CREATE TABLE `profile_networking` ( + `pid` int NOT NULL COMMENT 'profile id', `nwid` tinyint unsigned NOT NULL COMMENT 'number of the address for the user', `network_type` tinyint unsigned NOT NULL, `address` varchar(255) NOT NULL, `pub` enum('private','public') NOT NULL DEFAULT 'private', - PRIMARY KEY (`uid`, `nwid`), - INDEX uid (uid) + PRIMARY KEY (`pid`, `nwid`), + INDEX uid (pid) ) ENGINE=InnoDB, CHARSET=utf8, COMMENT='networking addresses'; -- Insert a first address type for old URLs INSERT INTO `profile_networking_enum` (`network_type`, `name`, `icon`, `filter`) VALUES (0, 'Page web', 'web.gif', 'web'); -INSERT INTO `profile_networking` (`uid`, `nwid`, `network_type`, `address`, `pub`) +INSERT INTO `profile_networking` (`pid`, `nwid`, `network_type`, `address`, `pub`) SELECT `user_id`, 0, 0, `profile_web`, `profile_web_pub` FROM #x4dat#.`auth_user_quick` WHERE `profile_web` <> ""; diff --git a/upgrade/newdirectory-0.0.1/03_emails.sql b/upgrade/newdirectory-0.0.1/03_emails.sql index dc7eea3..7033d30 100644 --- a/upgrade/newdirectory-0.0.1/03_emails.sql +++ b/upgrade/newdirectory-0.0.1/03_emails.sql @@ -1,7 +1 @@ -CREATE TABLE IF NOT EXISTS profile_directory ( - uid INT NOT NULL, - email_directory VARCHAR(255) DEFAULT NULL, - PRIMARY KEY (uid) -) ENGINE=InnoDB, CHARSET=utf8; - ALTER TABLE register_marketing MODIFY COLUMN type ENUM('user', 'staff', 'ax'); diff --git a/upgrade/newdirectory-0.0.1/04_telephone.sql b/upgrade/newdirectory-0.0.1/04_telephone.sql index 93846f0..bc66f78 100644 --- a/upgrade/newdirectory-0.0.1/04_telephone.sql +++ b/upgrade/newdirectory-0.0.1/04_telephone.sql @@ -1,7 +1,7 @@ DROP TABLE IF EXISTS profile_phones; CREATE TABLE `profile_phones` ( - `uid` smallint unsigned NOT NULL, + `pid` 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)', @@ -10,9 +10,9 @@ CREATE TABLE `profile_phones` ( `display_tel` varchar(30) NOT NULL COMMENT 'display number', `pub` enum('private', 'ax', 'public') NOT NULL DEFAULT 'private', `comment` varchar(80) NOT NULL, - PRIMARY KEY(`uid`, `link_type`, `link_id`, `tel_id`), + PRIMARY KEY(`pid`, `link_type`, `link_id`, `tel_id`), INDEX (`search_tel`), - INDEX uid (uid) + INDEX pid (pid) ) ENGINE=InnoDB, CHARSET=utf8; diff --git a/upgrade/newdirectory-0.0.1/06_education.sql b/upgrade/newdirectory-0.0.1/06_education.sql index 631941c..dc55947 100644 --- a/upgrade/newdirectory-0.0.1/06_education.sql +++ b/upgrade/newdirectory-0.0.1/06_education.sql @@ -38,7 +38,7 @@ CREATE TABLE profile_education_enum ( CREATE TABLE profile_education ( id TINYINT(2) UNSIGNED NOT NULL DEFAULT 0, - uid INT(11) NOT NULL DEFAULT 0, + pid INT(11) NOT NULL DEFAULT 0, eduid INT(4) NOT NULL DEFAULT 0, degreeid INT(4) NOT NULL DEFAULT 0, fieldid INT(2) NOT NULL DEFAULT 0, @@ -46,8 +46,8 @@ CREATE TABLE profile_education ( grad_year INT(4) DEFAULT NULL, program VARCHAR(255) DEFAULT NULL, flags SET('primary') DEFAULT '' NOT NULL, - PRIMARY KEY(id, uid), - INDEX uid (uid) + PRIMARY KEY(id, pid), + INDEX pid (pid) ) ENGINE=InnoDB, CHARSET=utf8; INSERT INTO profile_education_field_enum (field) @@ -71,7 +71,7 @@ INSERT INTO profile_education_enum (id, name, url) SELECT id, text, url FROM #x4dat#.applis_def; -INSERT INTO profile_education (id, uid, eduid, degreeid) +INSERT INTO profile_education (id, pid, eduid, degreeid) SELECT a.ordre, a.uid, a.aid, d.id FROM #x4dat#.applis_ins AS a INNER JOIN profile_education_degree_enum AS d ON (a.type = d.degree); diff --git a/upgrade/newdirectory-0.0.1/07_corps.sql b/upgrade/newdirectory-0.0.1/07_corps.sql index 49ee5e7..e423e6e 100644 --- a/upgrade/newdirectory-0.0.1/07_corps.sql +++ b/upgrade/newdirectory-0.0.1/07_corps.sql @@ -2,17 +2,17 @@ DROP TABLE IF EXISTS profile_corps; DROP TABLE IF EXISTS profile_corps_enum; DROP TABLE IF EXISTS profile_corps_rank_enum; -CREATE TABLE IF NOT EXISTS profile_corps ( - uid INT(11) NOT NULL, +CREATE TABLE profile_corps ( + pid INT(11) NOT NULL, original_corpsid INT(4) UNSIGNED NOT NULL DEFAULT 0, current_corpsid INT(4) UNSIGNED NOT NULL DEFAULT 0, rankid INT(4) UNSIGNED NOT NULL DEFAULT 0, corps_pub ENUM('private', 'ax', 'public') NOT NULL DEFAULT 'private', - PRIMARY KEY(uid) + PRIMARY KEY(pid) ) ENGINE=InnoDB, CHARSET=utf8; -CREATE TABLE IF NOT EXISTS profile_corps_enum ( +CREATE TABLE profile_corps_enum ( id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL DEFAULT '', abbreviation CHAR(5) NOT NULL DEFAULT '', @@ -23,7 +23,7 @@ CREATE TABLE IF NOT EXISTS profile_corps_enum ( ) ENGINE=InnoDB, CHARSET=utf8; -CREATE TABLE IF NOT EXISTS profile_corps_rank_enum ( +CREATE TABLE profile_corps_rank_enum ( id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL DEFAULT '', abbreviation CHAR(5) NOT NULL DEFAULT '', diff --git a/upgrade/newdirectory-0.0.1/09_education.sql b/upgrade/newdirectory-0.0.1/09_education.sql index ffb9063..95340fb 100644 --- a/upgrade/newdirectory-0.0.1/09_education.sql +++ b/upgrade/newdirectory-0.0.1/09_education.sql @@ -609,7 +609,7 @@ INSERT INTO profile_education_enum (name, url, country, abbreviation) -- Médecine is not a university but an educational field -REPLACE INTO profile_education (uid, id, fieldid, eduid, degreeid) +REPLACE INTO profile_education (pid, id, fieldid, eduid, degreeid) SELECT e.uid, e.id, f.id, 0, d.id FROM profile_education AS e INNER JOIN profile_education_enum AS l ON (l.id = e.eduid) diff --git a/upgrade/newdirectory-0.0.1/10_promotion.sql b/upgrade/newdirectory-0.0.1/10_promotion.sql index 5cd2066..7dec54d 100644 --- a/upgrade/newdirectory-0.0.1/10_promotion.sql +++ b/upgrade/newdirectory-0.0.1/10_promotion.sql @@ -1,11 +1,11 @@ -INSERT INTO profile_education (id, uid, grad_year, entry_year, eduid, degreeid, flags) +INSERT INTO profile_education (id, pid, grad_year, entry_year, eduid, degreeid, flags) SELECT 100, u.user_id, u.promo_sortie, u.promo, e.id, d.id, 'primary' FROM #x4dat#.auth_user_md5 AS u LEFT JOIN profile_education_enum AS e ON (e.abbreviation = "X") LEFT JOIN profile_education_degree_enum AS d ON (d.degree = "Ingénieur"); UPDATE profile_display AS d -INNER JOIN profile_education AS e ON (d.pid = e.uid) +INNER JOIN profile_education AS e ON (d.pid = e.pid) SET d.promo = CONCAT("X", entry_year) WHERE FIND_IN_SET('primary', e.flags); diff --git a/upgrade/newdirectory-0.0.1/11_jobs.sql b/upgrade/newdirectory-0.0.1/11_jobs.sql index cd359d9..f5ca0e3 100644 --- a/upgrade/newdirectory-0.0.1/11_jobs.sql +++ b/upgrade/newdirectory-0.0.1/11_jobs.sql @@ -17,7 +17,7 @@ DROP TABLE IF EXISTS profile_job; CREATE TABLE profile_job ( id TINYINT(1) UNSIGNED NOT NULL, - uid INT(11) NOT NULL DEFAULT 0, + pid INT(11) NOT NULL DEFAULT 0, jobid INT(6) UNSIGNED NOT NULL DEFAULT 0, sectorid TINYINT(2) UNSIGNED NOT NULL DEFAULT 0, subsectorid TINYINT(3) UNSIGNED NOT NULL DEFAULT 0, @@ -35,7 +35,7 @@ INSERT IGNORE INTO profile_job_enum (name, url) SELECT entreprise, web FROM #x4dat#.entreprises; -INSERT INTO profile_job (id, uid, jobid, email, pub, email_pub, description) +INSERT INTO profile_job (id, pid, jobid, email, pub, email_pub, description) SELECT e.entrid, e.uid, j.id, e.email, e.pub, e.email_pub, CONCAT_WS(', ', IF(e.poste = '', NULL, e.poste), IF(e.fonction = 0, NULL, f.fonction_fr), IF(e.ss_secteur IS NULL , IF(e.secteur IS NULL, NULL, s.label), ss.label)) diff --git a/upgrade/newdirectory-0.0.1/13_mentoring.sql b/upgrade/newdirectory-0.0.1/13_mentoring.sql index 4aa967c..5f63c61 100644 --- a/upgrade/newdirectory-0.0.1/13_mentoring.sql +++ b/upgrade/newdirectory-0.0.1/13_mentoring.sql @@ -3,32 +3,32 @@ DROP TABLE IF EXISTS profile_mentor_country; DROP TABLE IF EXISTS profile_mentor_sector; CREATE TABLE profile_mentor ( - uid INT(11) NOT NULL DEFAULT 0, + pid INT(11) NOT NULL DEFAULT 0, expertise TEXT NOT NULL, - PRIMARY KEY (uid) + PRIMARY KEY (pid) ) ENGINE=InnoDB, CHARSET=utf8; -INSERT INTO profile_mentor (uid, expertise) +INSERT INTO profile_mentor (pid, expertise) SELECT uid, expertise FROM #x4dat#.mentor; CREATE TABLE profile_mentor_country ( - uid INT(11) NOT NULL DEFAULT 0, + pid INT(11) NOT NULL DEFAULT 0, country CHAR(2) NOT NULL DEFAULT "FR", - PRIMARY KEY (uid, country), - INDEX uid (uid) + PRIMARY KEY (pid, country), + INDEX pid (pid) ) ENGINE=InnoDB, CHARSET=utf8; -INSERT INTO profile_mentor_country (uid, country) +INSERT INTO profile_mentor_country (pid, country) SELECT uid, pid FROM #x4dat#.mentor_pays; CREATE TABLE profile_mentor_sector ( - uid INT(11) NOT NULL DEFAULT 0, + pid INT(11) NOT NULL DEFAULT 0, sectorid TINYINT(2) UNSIGNED NOT NULL, subsectorid TINYINT(3) UNSIGNED NOT NULL, - PRIMARY KEY (uid, sectorid, subsectorid), - INDEX uid (uid) + PRIMARY KEY (pid, sectorid, subsectorid), + INDEX pid (pid) ) ENGINE=InnoDB, CHARSET=utf8; -- vim:set syntax=mysql: -- 2.1.4