From a9fd3272be2fa0a2513e0ef22323edab77604ca3 Mon Sep 17 00:00:00 2001 From: =?utf8?q?St=C3=A9phane=20Jacob?= Date: Tue, 22 Feb 2011 17:19:09 +0100 Subject: [PATCH] Adapts broken addresses processing 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/emails.inc.php | 39 ++++++++++ include/validations/broken.inc.php | 12 ++- modules/email.php | 151 +++++++++++++------------------------ templates/emails/broken.mail.tpl | 4 +- templates/emails/broken.tpl | 10 +-- 5 files changed, 105 insertions(+), 111 deletions(-) diff --git a/include/emails.inc.php b/include/emails.inc.php index 6e079f0..4561329 100644 --- a/include/emails.inc.php +++ b/include/emails.inc.php @@ -24,6 +24,45 @@ define('ERROR_INACTIVE_REDIRECTION', 2); define('ERROR_INVALID_EMAIL', 3); define('ERROR_LOOP_EMAIL', 4); +// function mark_broken_email() {{{1 +function mark_broken_email($email, $admin = false) +{ + $email = valide_email($email); + if (empty($email) || $email == '@') { + return; + } + + $user = XDB::fetchOneAssoc('SELECT r1.uid, r1.broken_level != 0 AS broken, a.hruid, COUNT(r2.uid) AS nb_mails, a.full_name, s.email AS alias + FROM email_redirect_account AS r1 + INNER JOIN accounts AS a ON (a.uid = r1.uid) + INNER JOIN email_source_account AS s ON (a.uid = s.uid AND s.flags = \'bestalias\') + LEFT JOIN email_redirect_account AS r2 ON (a.uid = r2.uid AND r1.redirect != r2.redirect AND + r2.broken_level = 0 AND r2.flags = \'active\' AND + (r2.type = \'smtp\' OR r2.type = \'googleapps\')) + WHERE r1.redirect = {?} + GROUP BY r1.uid', $email); + + if ($user) { + // Mark address as broken. + if (!$user['broken']) { + XDB::execute('UPDATE email_redirect_account + SET broken_date = NOW(), last = NOW(), broken_level = 1 + WHERE redirect = {?}', $email); + } elseif ($admin) { + XDB::execute('UPDATE email_redirect_account + SET last = CURDATE(), broken_level = IF(broken_level > 1, 3, 2) + WHERE redirect = {?} AND DATE_ADD(last, INTERVAL 14 DAY) < CURDATE()', + $email); + } else { + XDB::execute('UPDATE email_redirect_account + SET broken_level = 1 + WHERE redirect = {?} AND broken_level = 0', $email); + } + } + + return $user; +} + // function fix_bestalias() {{{1 // Checks for an existing 'bestalias' among the the current user's aliases, and // eventually selects a new bestalias when required. diff --git a/include/validations/broken.inc.php b/include/validations/broken.inc.php index 8e793f9..70d02bd 100644 --- a/include/validations/broken.inc.php +++ b/include/validations/broken.inc.php @@ -88,9 +88,10 @@ class BrokenReq extends Validate global $globals; $email = $this->m_user->bestEmail(); - XDB::execute("UPDATE emails - SET flags = 'active', panne_level = 2 - WHERE uid = {?} AND email = {?}", $this->m_user->id(), $this->m_email); + XDB::execute('UPDATE email_redirect_account + SET flags = \'active\', broken_level = 2 + WHERE uid = {?} AND redirect = {?}', + $this->m_user->id(), $this->m_email); if (XDB::affectedRows() > 0) { $this->m_reactive = true; $mailer = new PlMailer(); @@ -113,7 +114,10 @@ class BrokenReq extends Validate $subject = "Ton adresse $email semble ne plus fonctionner"; $reason = "Nous avons été informés que ton adresse $email ne fonctionne plus correctement par un camarade"; } else { - $res = XDB::iterRow("SELECT email FROM emails WHERE uid = {?} AND flags = 'panne'", $this->m_user->id()); + $res = XDB::iterRow('SELECT redirect + FROM email_redirect_account + WHERE uid = {?} AND flags = \'broken\'', + $this->m_user->id()); $redirect = array(); while (list($red) = $res->next()) { list(, $redirect[]) = explode('@', $red); diff --git a/modules/email.php b/modules/email.php index b0bb942..2d6d8c1 100644 --- a/modules/email.php +++ b/modules/email.php @@ -621,11 +621,12 @@ class EmailModule extends PLModule if ($warn == 'warn' && $email) { S::assert_xsrf_token(); + // Usual verifications. $email = valide_email($email); - // vérifications d'usage - $uid = XDB::fetchOneCell("SELECT uid - FROM emails - WHERE email = {?}", $email); + $uid = XDB::fetchOneCell('SELECT uid + FROM email_redirect_account + WHERE redirect = {?}', $email); + if ($uid) { $dest = User::getWithUID($uid); @@ -638,38 +639,17 @@ class EmailModule extends PLModule } elseif (Post::has('email')) { S::assert_xsrf_token(); - $email = valide_email(Post::v('email')); + $email = Post::t('email'); + list(, $domain) = explode('@', $email); + $domain = strtolower($domain); - list(,$fqdn) = explode('@', $email); - $fqdn = strtolower($fqdn); - if ($fqdn == 'polytechnique.org' || $fqdn == 'melix.org' || $fqdn == 'm4x.org' || $fqdn == 'melix.net') { + if ($domain == $globals->mail->alias_dom || $domain == $globals->mail->alias_dom2 + || $domain == $globals->mail->domain || $domain == $globals->mail->domain2) { $page->assign('neuneu', true); } else { - $page->assign('email',$email); - $x = XDB::fetchOneAssoc("SELECT e1.uid, e1.panne != 0 AS panne, a.hruid, - (COUNT(e2.uid) + IF(FIND_IN_SET('googleapps', eo.storage), 1, 0)) AS nb_mails - FROM emails AS e1 - INNER JOIN email_options AS eo ON (eo.uid = e1.uid) - INNER JOIN accounts AS a ON (e1.uid = a.uid) - LEFT JOIN emails AS e2 ON (e1.uid = e2.uid - AND FIND_IN_SET('active', e2.flags) - AND e1.email != e2.email) - WHERE e1.email = {?} - GROUP BY e1.uid", $email); - if ($x) { - // on écrit dans la base que l'adresse est cassée - if (!$x['panne']) { - XDB::execute("UPDATE emails - SET panne=NOW(), last=NOW(), panne_level = 1 - WHERE email = {?}", $email); - } else { - XDB::execute("UPDATE emails - SET panne_level = 1 - WHERE email = {?} AND panne_level = 0", $email); - } - $x['user'] = User::getWithUID($x['uid']); - $page->assign_by_ref('x', $x); - } + $user = mark_broken_email($email); + $page->assign('user', $user); + $page->assign('email', $email); } } } @@ -807,13 +787,13 @@ class EmailModule extends PLModule if (empty($email) || $email == '@') { $invalid_emails[] = trim($orig_email) . ': invalid email'; } elseif (!in_array($email, $valid_emails)) { - $res = XDB::query('SELECT COUNT(*) - FROM emails - WHERE email = {?}', $email); - if ($res->fetchOneCell() > 0) { + $nb = XDB::fetchOneCell('SELECT COUNT(*) + FROM email_redirect_account + WHERE redirect = {?}', $email); + if ($nb > 0) { $valid_emails[] = $email; } else { - $invalid_emails[] = "$orig_email: no such redirection"; + $invalid_emails[] = $orig_email . ': no such redirection'; } } } @@ -834,62 +814,33 @@ class EmailModule extends PLModule $broken_user_list = array(); $broken_list = explode("\n", $list); sort($broken_list); - foreach ($broken_list as $orig_email) { - $email = valide_email(trim($orig_email)); - if (empty($email) || $email == '@') { - continue; - } - - $sel = XDB::query( - "SELECT e1.uid, e1.panne != 0 AS panne, count(e2.uid) AS nb_mails, - acc.full_name, a.alias - FROM emails AS e1 - LEFT JOIN emails AS e2 ON (e1.uid = e2.uid AND FIND_IN_SET('active', e2.flags) - AND e1.email != e2.email) - INNER JOIN accounts AS acc ON (e1.uid = acc.uid) - INNER JOIN aliases AS a ON (acc.uid = a.uid AND FIND_IN_SET('bestalias', a.flags)) - WHERE e1.email = {?} - GROUP BY e1.uid", $email); - - if ($x = $sel->fetchOneAssoc()) { - if (!$x['panne']) { - XDB::execute('UPDATE emails - SET panne=NOW(), last=NOW(), panne_level = 1 - WHERE email = {?}', - $email); - } else { - XDB::execute('UPDATE emails - SET last = CURDATE(), panne_level = panne_level + 1 - WHERE email = {?} - AND DATE_ADD(last, INTERVAL 14 DAY) < CURDATE()', - $email); - } - if (!empty($x['nb_mails'])) { + foreach ($broken_list as $email) { + if ($user = mark_broken_email($email, true)) { + if ($user['nb_mails'] > 0) { $mail = new PlMailer('emails/broken.mail.tpl'); - $mail->addTo("\"{$x['full_name']}\" <{$x['alias']}@" - . Platal::globals()->mail->domain . '>'); - $mail->assign('x', $x); + $mail->addTo('"' . $user['full_name'] . '" <' . $user['alias'] . '@' . Platal::globals()->mail->domain . '>'); + $mail->assign('user', $user); $mail->assign('email', $email); $mail->send(); } - if (!isset($broken_user_list[$x['alias']])) { - $broken_user_list[$x['alias']] = array($email); + if (!isset($broken_user_list[$user['alias']])) { + $broken_user_list[$user['alias']] = array($email); } else { - $broken_user_list[$x['alias']][] = $email; + $broken_user_list[$user['alias']][] = $email; } } } - XDB::execute("UPDATE emails - SET panne_level = panne_level - 1 - WHERE flags = 'active' AND panne_level > 1 - AND DATE_ADD(last, INTERVAL 1 MONTH) < CURDATE()"); - XDB::execute("UPDATE emails - SET panne_level = 0 - WHERE flags = 'active' AND panne_level = 1 - AND DATE_ADD(last, INTERVAL 1 YEAR) < CURDATE()"); + XDB::execute('UPDATE email_redirect_account + SET broken_level = broken_level - 1 + WHERE flags = \'active\' AND broken_level > 1 + AND DATE_ADD(last, INTERVAL 1 MONTH) < CURDATE()'); + XDB::execute('UPDATE email_redirect_account + SET broken_level = 0 + WHERE flags = \'active\' AND broken_level = 1 + AND DATE_ADD(last, INTERVAL 1 YEAR) < CURDATE()'); // Output the list of users with recently broken addresses, // along with the count of valid redirections. @@ -900,23 +851,23 @@ class EmailModule extends PLModule fputcsv($csv, array('nom', 'promo', 'alias', 'bounce', 'nbmails', 'url', 'corps', 'job', 'networking'), ';'); foreach ($broken_user_list as $alias => $mails) { $sel = Xdb::query( - "SELECT acc.uid, count(DISTINCT(e.email)) AS nb_mails, - IFNULL(pd.public_name, acc.full_name) AS fullname, - IFNULL(pd.promo, 0) AS promo, IFNULL(pce.name, 'Aucun') AS corps, - IFNULL(pje.name, 'Aucun') AS job, GROUP_CONCAT(pn.address SEPARATOR ', ') AS networking - FROM aliases AS a - INNER JOIN accounts AS acc ON (a.uid = acc.uid) - LEFT JOIN emails AS e ON (e.uid = acc.uid - AND FIND_IN_SET('active', e.flags) AND e.panne = 0) - LEFT JOIN account_profiles AS ap ON (acc.uid = ap.uid AND FIND_IN_SET('owner', ap.perms)) - LEFT JOIN profile_display AS pd ON (pd.pid = ap.pid) - LEFT JOIN profile_corps AS pc ON (pc.pid = ap.pid) - LEFT JOIN profile_corps_enum AS pce ON (pc.current_corpsid = pce.id) - LEFT JOIN profile_job AS pj ON (pj.pid = ap.pid) - LEFT JOIN profile_job_enum AS pje ON (pj.jobid = pje.id) - LEFT JOIN profile_networking AS pn ON (pn.pid = ap.pid) - WHERE a.alias = {?} - GROUP BY acc.uid", $alias); + 'SELECT a.uid, count(DISTINCT(r.redirect)) AS nb_mails, + IFNULL(pd.public_name, a.full_name) AS fullname, + IFNULL(pd.promo, 0) AS promo, IFNULL(pce.name, \'Aucun\') AS corps, + IFNULL(pje.name, \'Aucun\') AS job, GROUP_CONCAT(pn.address SEPARATOR \', \') AS networking + FROM email_source_account AS s + INNER JOIN accounts AS a ON (s.uid = a.uid) + LEFT JOIN email_redirect_account AS r ON (a.uid = r.uid AND r.broken_level = 0 AND r.flags = \'active\' AND + (r.type = \'smtp\' OR r.type = \'googleapps\')) + LEFT JOIN account_profiles AS ap ON (a.uid = ap.uid AND FIND_IN_SET(\'owner\', ap.perms)) + LEFT JOIN profile_display AS pd ON (pd.pid = ap.pid) + LEFT JOIN profile_corps AS pc ON (pc.pid = ap.pid) + LEFT JOIN profile_corps_enum AS pce ON (pc.current_corpsid = pce.id) + LEFT JOIN profile_job AS pj ON (pj.pid = ap.pid) + LEFT JOIN profile_job_enum AS pje ON (pj.jobid = pje.id) + LEFT JOIN profile_networking AS pn ON (pn.pid = ap.pid) + WHERE s.email = {?} + GROUP BY a.uid', $alias); if ($x = $sel->fetchOneAssoc()) { if ($x['nb_mails'] == 0) { diff --git a/templates/emails/broken.mail.tpl b/templates/emails/broken.mail.tpl index 13e80f4..8d6b932 100644 --- a/templates/emails/broken.mail.tpl +++ b/templates/emails/broken.mail.tpl @@ -30,7 +30,7 @@ Bonjour ! Nous t'écrivons car lors de l'envoi de la lettre d'information mensuelle de Polytechnique.org à ton adresse polytechnicienne : - {$x.alias}@{$globals->mail->domain}, + {$user.alias}@{$globals->mail->domain}, l'adresse {$email}, sur laquelle tu rediriges tes emails, ne fonctionnait pas. @@ -51,7 +51,7 @@ d'accéder au site, rends toi sur la page https://www.polytechnique.org/recovery elle te permettra de créer un nouveau mot de passe après avoir rentré ton -login ({$x.alias}) et ta date de naissance !"; +login ({$user.alias}) et ta date de naissance !"; {/if} {* vim:set et sw=2 sts=2 sws=2 enc=utf-8: *} diff --git a/templates/emails/broken.tpl b/templates/emails/broken.tpl index 84e808f..ea5a292 100644 --- a/templates/emails/broken.tpl +++ b/templates/emails/broken.tpl @@ -32,10 +32,10 @@ ce qui est invalide. En effet, il faut nous donner l'adresse qui se cache derrière l'adresse polytechnicienne de ton correspondant si tu veux que nous puissions te répondre.

-{elseif $x && $x.nb_mails} +{elseif t($user) && $user.nb_mails}

Patte cassée

- Ton correspondant a à l'heure actuelle {$x.nb_mails} adresse(s) email(s) de redirection active(s) + Ton correspondant a à l'heure actuelle {$user.nb_mails} adresse(s) email(s) de redirection active(s) en dehors de celle que tu nous as communiquée. Cela ne veut pas forcément dire qu'il les avait déjà activées lorsque tu as envoyé ton email, mais c'est fort probable.

@@ -44,10 +44,10 @@ correspondant si tu veux que nous puissions te répondre. Si tu veux que nous lui envoyions un email automatique de ta part pour le prévenir, clique sur ce lien.

-{elseif $x} +{elseif $user}

Patte cassée

- Désolé, mais ton correspondant, {$x.user->fullName()}, + Désolé, mais ton correspondant, {$user.full_name}, n'a actuellement aucune adresse email de redirection active autre que celle que tu viens de rentrer. Nous t'invitons à prendre contact avec lui autrement que par email, @@ -55,7 +55,7 @@ correspondant si tu veux que nous puissions te répondre.

Si tu connais une autre adresse email où le contacter, nous pouvons l'inviter à mettre à jour sa redirection - Polytechnique.org. Pour ceci il suffit que tu remplisses ce formulaire. + Polytechnique.org. Pour ceci il suffit que tu remplisses ce formulaire.

{elseif $email}

-- 2.1.4