From 4371e993d51f10d655c4b453563c730b613b490e Mon Sep 17 00:00:00 2001 From: =?utf8?q?St=C3=A9phane=20Jacob?= Date: Fri, 25 Feb 2011 13:59:12 +0100 Subject: [PATCH] Adapts homonyms issues to new mail chain. MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Signed-off-by: Stéphane Jacob --- include/validations/homonymes.inc.php | 20 +++++++-- modules/admin.php | 77 ++++++++++++++++++++++----------- modules/admin/homonyms.inc.php | 31 ++----------- modules/register/register.inc.php | 32 ++++++++------ templates/admin/homonymes.tpl | 61 +++++++++++++++++++------- upgrade/1.1.0/01_new_mail_schema.sql | 2 +- upgrade/1.1.0/02_new_mail_insertion.sql | 4 +- 7 files changed, 138 insertions(+), 89 deletions(-) diff --git a/include/validations/homonymes.inc.php b/include/validations/homonymes.inc.php index f39209e..7f181ef 100644 --- a/include/validations/homonymes.inc.php +++ b/include/validations/homonymes.inc.php @@ -121,12 +121,24 @@ est ambigu pour des raisons d'homonymie et signalera ton email exact."; public function commit() { Platal::load('admin', 'homonyms.inc.php'); - switch_bestalias($this->user, $this->loginbis); if (!$this->warning) { - XDB::execute("UPDATE aliases SET type = 'homonyme', expire = NOW() WHERE alias = {?}", $this->loginbis); - XDB::execute('INSERT IGNORE INTO homonyms (homonyme_id, uid) + global $globals; + require_once 'emails.inc.php'; + + XDB::execute('DELETE e + FROM email_source_account AS e + INNER JOIN email_virtual_domains AS d ON (e.domain = d.id) + WHERE e.email = {?} AND d.name = {?}', + $this->loginbis, $globals->mail->domain); + XDB::execute('INSERT INTO email_source_other (hrmid, email, domain, type, expire) + SELECT CONCAT(\'h.\', {?}, \'.\', {?}), {?}, id, \'homonym\', NOW() + FROM email_virtual_domains + WHERE name = {?}', + $this->loginbis, $globals->mail->domain, $this->loginbis, $globals->mail->domain); + XDB::execute('INSERT IGNORE INTO homonyms_list (hrmid, uid) VALUES ({?}, {?})', - $this->user->id(), $this->user->id()); + 'h.' . $this->loginbis . '.' . $globals->mail->domain, $this->user->id()); + fix_bestalias($this->user); } return true; diff --git a/modules/admin.php b/modules/admin.php index ea0928b..c533a6c 100644 --- a/modules/admin.php +++ b/modules/admin.php @@ -938,13 +938,14 @@ class AdminModule extends PLModule function handler_homonyms($page, $op = 'list', $target = null) { + global $globals; $page->changeTpl('admin/homonymes.tpl'); $page->setTitle('Administration - Homonymes'); $this->load("homonyms.inc.php"); if ($target) { - $user = User::getSilent($target); - if (!$user || !($loginbis = select_if_homonyme($user))) { + $user = User::getSilentWithUID($target); + if (!$user || !($loginbis = select_if_homonym($user))) { $target = 0; } else { $page->assign('user', $user); @@ -955,15 +956,16 @@ class AdminModule extends PLModule $page->assign('op', $op); $page->assign('target', $target); - // on a un $target valide, on prepare les mails + // When we have a valid target, prepare emails. if ($target) { - // on examine l'op a effectuer + require_once 'emails.inc.php'; + // Examine what operation needs to be performed. switch ($op) { case 'mail': S::assert_xsrf_token(); send_warning_homonyme($user, $loginbis); - switch_bestalias($user, $loginbis); + fix_bestalias($user); $op = 'list'; $page->trigSuccess('Email envoyé à ' . $user->forlifeEmail() . '.'); break; @@ -971,12 +973,20 @@ class AdminModule extends PLModule case 'correct': S::assert_xsrf_token(); - switch_bestalias($user, $loginbis); - XDB::execute("UPDATE aliases - SET type = 'homonyme', expire=NOW() - WHERE alias = {?}", $loginbis); - XDB::execute('INSERT IGNORE INTO homonyms (homonyme_id, uid) - VALUES ({?}, {?})', $target, $target); + XDB::execute('DELETE e + FROM email_source_account AS e + INNER JOIN email_virtual_domains AS d ON (e.domain = d.id) + WHERE e.email = {?} AND d.name = {?}', + $loginbis, $globals->mail->domain); + XDB::execute('INSERT INTO email_source_other (hrmid, email, domain, type, expire) + SELECT CONCAT(\'h.\', {?}, \'.\', {?}), {?}, id, \'homonym\', NOW() + FROM email_virtual_domains + WHERE name = {?}', + $loginbis, $globals->mail->domain, $loginbis, $globals->mail->domain); + XDB::execute('INSERT IGNORE INTO homonyms_list (hrmid, uid) + VALUES ({?}, {?})', + 'h.' . $loginbis . '.' . $globals->mail->domain, $target); + fix_bestalias($user); send_robot_homonyme($user, $loginbis); $op = 'list'; $page->trigSuccess('Email envoyé à ' . $user->forlifeEmail() . ', alias supprimé.'); @@ -985,21 +995,36 @@ class AdminModule extends PLModule } if ($op == 'list') { - $res = XDB::iterator( - "SELECT a.alias AS homonyme, s.alias AS forlife, - IF(h.homonyme_id = s.uid, a.expire, NULL) AS expire, - IF(h.homonyme_id = s.uid, a.type, NULL) AS type, ac.uid - FROM aliases AS a - LEFT JOIN homonyms AS h ON (h.homonyme_id = a.uid) - INNER JOIN aliases AS s ON (s.uid = h.uid AND s.type = 'a_vie') - INNER JOIN accounts AS ac ON (ac.uid = a.uid) - WHERE a.type = 'homonyme' OR a.expire != '' - ORDER BY a.alias, forlife"); - $hnymes = Array(); - while ($tab = $res->next()) { - $hnymes[$tab['homonyme']][] = $tab; - } - $page->assign_by_ref('hnymes', $hnymes); + // Retrieves homonyms that are already been fixed. + $res = XDB::iterator('SELECT o.email AS homonym, f.email AS forlife, o.expire, f.uid + FROM email_source_other AS o + INNER JOIN homonyms_list AS h ON (o.hrmid = h.hrmid) + INNER JOIN email_source_account AS f ON (h.uid = f.uid AND f.type = \'forlife\') + INNER JOIN email_virtual_domains AS d ON (f.domain = d.id) + WHERE o.expire IS NOT NULL AND d.name = {?} + ORDER BY homonym, forlife', + $globals->mail->domain); + $homonyms = array(); + while ($item = $res->next()) { + $homonyms[$item['homonym']][] = $item; + } + $page->assign_by_ref('homonyms', $homonyms); + + // Retrieves homonyms that needs to be fixed. + $res = XDB::iterator('SELECT e.email AS homonym, f.email AS forlife, e.expire, e.uid, (e.expire < NOW()) AS urgent + FROM email_source_account AS e + INNER JOIN homonyms_list AS l ON (e.uid = l.uid) + INNER JOIN homonyms_list AS h ON (l.hrmid = h.hrmid) + INNER JOIN email_source_account AS f ON (h.uid = f.uid AND f.type = \'forlife\') + INNER JOIN email_virtual_domains AS d ON (f.domain = d.id) + WHERE e.expire IS NOT NULL AND d.name = {?} + ORDER BY homonym, forlife', + $globals->mail->domain); + $homonyms_to_fix = array(); + while ($item = $res->next()) { + $homonyms_to_fix[$item['homonym']][] = $item; + } + $page->assign_by_ref('homonyms_to_fix', $homonyms_to_fix); } } diff --git a/modules/admin/homonyms.inc.php b/modules/admin/homonyms.inc.php index 0d22b94..d96e735 100644 --- a/modules/admin/homonyms.inc.php +++ b/modules/admin/homonyms.inc.php @@ -19,10 +19,10 @@ * 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA * ***************************************************************************/ -function select_if_homonyme(PlUser $user) { - return XDB::fetchOneCell("SELECT a.alias - FROM aliases AS a - WHERE a.uid = {?} AND a.expire != ''", +function select_if_homonym(PlUser $user) { + return XDB::fetchOneCell('SELECT email + FROM email_source_account + WHERE uid = {?} AND expire IS NOT NULL', $user->id()); } @@ -50,28 +50,5 @@ function send_robot_homonyme(PlUser $user, $loginbis) { $mymail->sendTo($user); } -function switch_bestalias(PlUser $user, $loginbis) { - // check if loginbis was the bestalias - $bestailas = XDB::fetchOneCell("SELECT alias - FROM aliases - WHERE uid = {?} AND FIND_IN_SET('bestalias', flags)", - $user->id()); - if ($bestalias && $bestalias != $loginbis) { - return false; - } - - // select the shortest alias still alive - $newbest = XDB::fetchOneCell("SELECT alias - FROM aliases - WHERE uid = {?} AND alias != {?} AND expire IS NULL - ORDER BY LENGTH(alias) - LIMIT 1", $user->id(), $loginbis); - // change the bestalias flag - XDB::execute("UPDATE aliases - SET flags = (flags & (255 - 1)) | IF(alias = {?}, 1, 0) - WHERE uid = {?}", $newbest, $user->id()); - return $newbest; -} - // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8: ?> diff --git a/modules/register/register.inc.php b/modules/register/register.inc.php index c32d35a..bd6f860 100644 --- a/modules/register/register.inc.php +++ b/modules/register/register.inc.php @@ -152,22 +152,28 @@ function createAliases($subState) . "mail->domain}\">support@{$globals->mail->domain}."; } - $res = XDB::query('SELECT uid, type, expire - FROM aliases - WHERE alias = {?}', $emailXorg); - if ($res->numRows()) { + $count = XDB::numRows('SELECT * + FROM email_source_account AS s + INNER JOIN email_virtual_domains AS d ON (s.domain = d.id) + WHERE s.email = {?} AND d.name = {?}', + $emailXorg, $globals->mail->domain); + if ($count) { list($h_id, $h_type, $expire) = $res->fetchOneRow(); if ($h_type != 'homonyme' and empty($expire)) { - XDB::execute('UPDATE aliases - SET expire = ADDDATE(NOW(), INTERVAL 1 MONTH) - WHERE alias = {?}', $emailXorg); - XDB::execute('INSERT IGNORE INTO homonyms (homonyme_id, uid) + XDB::execute('UPDATE email_source_account AS e + INNER JOIN email_virtual_domains AS d ON (e.domain = d.id) + SET e.expire = ADDDATE(NOW(), INTERVAL 1 MONTH) + WHERE e.email = {?} AND d.name = {?}', + $emailXorg, $globals->mail->domain); + $hrmid = 'h.' . $emailXorg . '.' . $globals->mail->domain; + XDB::execute('INSERT IGNORE INTO homonyms_list (hrmid, uid) VALUES ({?}, {?}), ({?}, {?})', - $subState->i('uid'), $h_id, $h_id, $subState->i('uid')); - $res = XDB::query('SELECT alias - FROM aliases - WHERE uid = {?} AND expire IS NULL', $h_id); - $als = $res->fetchColumn(); + $hrmid, $h_id, $hrmid, $subState->i('uid')); + $als = XDB::fetchColumn('SELECT * + FROM email_source_account AS s + INNER JOIN email_virtual_domains AS d ON (s.domain = d.id) + WHERE s.uid = {?} AND d.name = {?} AND s.expire IS NULL', + $h_id, $globals->mail->domain); $mailer = new PlMailer('register/lostalias.mail.tpl'); $mailer->addTo($emailXorg . '@' . $globals->mail->domain); diff --git a/templates/admin/homonymes.tpl b/templates/admin/homonymes.tpl index 1567407..9f9fed5 100644 --- a/templates/admin/homonymes.tpl +++ b/templates/admin/homonymes.tpl @@ -24,40 +24,69 @@ {if $op eq 'list' || $op eq 'mail' || $op eq 'correct'} +{if $homonyms_to_fix|@count}

- Les utilisateurs signalés en rouge sont ceux qui conservent actuellement - l'alias prenom.nom et empêchent donc la mise en place du robot détrompeur. + Liste des homonymies à corriger, celles en rouge devraient déjà être traitées.

- + - {foreach from=$hnymes key=login item=row} + {foreach from=$homonyms_to_fix key=login item=row} - + + {foreach from=$row item=user} - + + - + + {/foreach} + {/foreach} +
alias concerné date de péremption de l'aliasopactions
+ + {if $row.0.urgent} + {$login} + {else} {$login} + {/if} + {$row.0.expire|date_format} + envoyer un email + corriger
   - {if $user.type eq 'alias'} - {$user.forlife} - {else} - {$user.forlife} - {/if} +   {$user.forlife} + fiche + edit {$user.expire|date_format}
+{/if} + +

+ Liste des homonymies déjà corrigées. +

+ + + + + + + + {foreach from=$homonyms key=login item=row} + + + + + + {foreach from=$row item=user} + + + {/foreach} diff --git a/upgrade/1.1.0/01_new_mail_schema.sql b/upgrade/1.1.0/01_new_mail_schema.sql index 1d599b1..46cbd97 100644 --- a/upgrade/1.1.0/01_new_mail_schema.sql +++ b/upgrade/1.1.0/01_new_mail_schema.sql @@ -35,7 +35,7 @@ CREATE TABLE email_source_other ( domain SMALLINT(3) UNSIGNED NOT NULL DEFAULT 1, hrmid VARCHAR(255) NOT NULL, type ENUM('homonym', 'ax', 'honeypot'), - expire DATE NOT NULL DEFAULT '0000-00-00', + expire DATE DEFAULT NULL, PRIMARY KEY (email, domain), KEY (domain), FOREIGN KEY (domain) REFERENCES email_virtual_domains (id) ON UPDATE CASCADE ON DELETE CASCADE diff --git a/upgrade/1.1.0/02_new_mail_insertion.sql b/upgrade/1.1.0/02_new_mail_insertion.sql index fa60db8..2229789 100644 --- a/upgrade/1.1.0/02_new_mail_insertion.sql +++ b/upgrade/1.1.0/02_new_mail_insertion.sql @@ -56,8 +56,8 @@ INSERT INTO email_source_account (uid, domain, email, type) WHERE v.type = 'user' AND v.alias LIKE '%@melix.net' AND a.uid IS NOT NULL; -- 3/ Feeds email_source_other -INSERT INTO email_source_other (hrmid, email, domain, type) - SELECT CONCAT(CONCAT('h.', alias), '.polytechnique.org'), alias, @p_domain_id, 'homonym' +INSERT INTO email_source_other (hrmid, email, domain, type, expire) + SELECT CONCAT(CONCAT('h.', alias), '.polytechnique.org'), alias, @p_domain_id, 'homonym', IF(expire IS NULL, '0000-00-00', expire) FROM aliases WHERE type = 'homonyme' GROUP BY alias; -- 2.1.4
alias concernéalias prémimé depuisactions
{$login}{if $row.0.expire eq '0000-00-00'}---{else}{$row.0.expire|date_format}{/if}
  {$user.forlife} fiche edit - {if $user.type eq 'alias'} - envoyer un email - corriger - {/if}