X-Git-Url: http://git.polytechnique.org/?a=blobdiff_plain;f=modules%2Femail.php;h=4080b62d447fa6e4e659c17149acd99ecb3bba77;hb=4d336f59bad0da6714bc69fd71aa94056bc455c1;hp=f4995094cbeda3492c342ab7eb9b5772f63c54c8;hpb=d0edeb70f254ed642b59c7c3636b7802266bcfe4;p=platal.git diff --git a/modules/email.php b/modules/email.php index f499509..4080b62 100644 --- a/modules/email.php +++ b/modules/email.php @@ -1,6 +1,6 @@ $this->make_hook('submit', AUTH_COOKIE), 'emails/test' => $this->make_hook('test', AUTH_COOKIE, 'user', NO_AUTH), + 'emails/rewrite/in' => $this->make_hook('rewrite_in', AUTH_PUBLIC), + 'emails/rewrite/out' => $this->make_hook('rewrite_out', AUTH_PUBLIC), + 'emails/imap/in' => $this->make_hook('imap_in', AUTH_PUBLIC), 'admin/emails/duplicated' => $this->make_hook('duplicated', AUTH_MDP, 'admin'), @@ -49,45 +52,59 @@ class EmailModule extends PLModule $page->changeTpl('emails/index.tpl'); $page->setTitle('Mes emails'); - $uid = S::v('uid'); + $user = S::user(); + // Apply the bestalias change request. if ($action == 'best' && $email) { if (!S::has_xsrf_token()) { return PL_FORBIDDEN; } - // bestalias is the first bit : 1 - // there will be maximum 8 bits in flags : 255 - XDB::execute("UPDATE aliases SET flags=flags & (255 - 1) WHERE id={?}", $uid); - XDB::execute("UPDATE aliases SET flags=flags | 1 WHERE id={?} AND alias={?}", - $uid, $email); + XDB::execute("UPDATE aliases + SET flags = TRIM(BOTH ',' FROM REPLACE(CONCAT(',', flags, ','), ',bestalias,', ',')) + WHERE id = {?}", $user->id()); + XDB::execute("UPDATE aliases + SET flags = CONCAT_WS(',', IF(flags = '', NULL, flags), 'bestalias') + WHERE id = {?} AND alias = {?}", $user->id(), $email); + + // As having a non-null bestalias value is critical in + // plat/al's code, we do an a posteriori check on the + // validity of the bestalias. + fix_bestalias($user); } - // on regarde si on a affaire à un homonyme + // Fetch and display aliases. $sql = "SELECT alias, (type='a_vie') AS a_vie, (alias REGEXP '\\\\.[0-9]{2}$') AS cent_ans, FIND_IN_SET('bestalias',flags) AS best, expire FROM aliases WHERE id = {?} AND type!='homonyme' ORDER BY LENGTH(alias)"; - $page->assign('aliases', XDB::iterator($sql, $uid)); + $page->assign('aliases', XDB::iterator($sql, $user->id())); - $homonyme = XDB::query("SELECT alias FROM aliases INNER JOIN homonymes ON (id = homonyme_id) WHERE user_id = {?} AND type = 'homonyme'", $uid); + // Check for homonyms. + $homonyme = XDB::query( + "SELECT alias + FROM aliases + INNER JOIN homonymes ON (id = homonyme_id) + WHERE user_id = {?} AND type = 'homonyme'", $user->id()); $page->assign('homonyme', $homonyme->fetchOneCell()); - // Affichage des redirections de l'utilisateur. - $redirect = new Redirect($uid); + // Display active redirections. + $redirect = new Redirect($user); $page->assign('mails', $redirect->active_emails()); - // on regarde si l'utilisateur a un alias et si oui on l'affiche ! - $forlife = S::v('forlife'); + // Display, when available, the @alias_dom email alias. $res = XDB::query( "SELECT alias FROM virtual AS v INNER JOIN virtual_redirect AS vr USING(vid) WHERE (redirect={?} OR redirect={?}) AND alias LIKE '%@{$globals->mail->alias_dom}'", - $forlife.'@'.$globals->mail->domain, $forlife.'@'.$globals->mail->domain2); + $user->forlifeEmail(), + // TODO: remove this über-ugly hack. The issue is that you need + // to remove all @m4x.org addresses in virtual_redirect first. + $user->login() . '@' . $globals->mail->domain2); $page->assign('melix', $res->fetchOneCell()); } @@ -100,32 +117,35 @@ class EmailModule extends PLModule $page->changeTpl('emails/alias.tpl'); $page->setTitle('Alias melix.net'); - $uid = S::v('uid'); - $forlife = S::v('forlife'); - - $page->assign('demande', AliasReq::get_request($uid)); + $user = S::user(); + $page->assign('demande', AliasReq::get_request($user->id())); + // Remove the email alias. if ($action == 'delete' && $value) { S::assert_xsrf_token(); - //Suppression d'un alias XDB::execute( - 'DELETE virtual, virtual_redirect - FROM virtual - INNER JOIN virtual_redirect USING (vid) - WHERE alias = {?} AND (redirect = {?} OR redirect = {?})', $value, - $forlife.'@'.$globals->mail->domain, $forlife.'@'.$globals->mail->domain2); + "DELETE virtual, virtual_redirect + FROM virtual + INNER JOIN virtual_redirect USING (vid) + WHERE alias = {?} AND (redirect = {?} OR redirect = {?})", + $value, $user->forlifeEmail(), + // TODO: remove this über-ugly hack. The issue is that you need + // to remove all @m4x.org addresses in virtual_redirect first. + $user->login() . '@' . $globals->mail->domain2); } - //Récupération des alias éventuellement existants + // Fetch existing @alias_dom aliases. $res = XDB::query( "SELECT alias, emails_alias_pub FROM auth_user_quick, virtual INNER JOIN virtual_redirect USING(vid) - WHERE ( redirect={?} OR redirect= {?} ) + WHERE (redirect = {?} OR redirect = {?}) AND alias LIKE '%@{$globals->mail->alias_dom}' AND user_id = {?}", - $forlife.'@'.$globals->mail->domain, - $forlife.'@'.$globals->mail->domain2, S::v('uid')); + $user->forlifeEmail(), + // TODO: remove this über-ugly hack. The issue is that you need + // to remove all @m4x.org addresses in virtual_redirect first. + $user->login() . '@' . $globals->mail->domain2, $user->id()); list($alias, $visibility) = $res->fetchOneRow(); $page->assign('actuel', $alias); @@ -171,7 +191,7 @@ class EmailModule extends PLModule } //Insertion de la demande dans la base, écrase les requêtes précédente - $myalias = new AliasReq($uid, $alias, $raison, $public); + $myalias = new AliasReq($user, $alias, $raison, $public); $myalias->submit(); $page->assign('success',$alias); return; @@ -183,10 +203,10 @@ class EmailModule extends PLModule if ($value == 'public') { XDB::execute("UPDATE auth_user_quick SET emails_alias_pub = 'public' - WHERE user_id = {?}", S::v('uid')); + WHERE user_id = {?}", $user->id()); } else { XDB::execute("UPDATE auth_user_quick SET emails_alias_pub = 'private' - WHERE user_id = {?}", S::v('uid')); + WHERE user_id = {?}", $user->id()); } $visibility = $value; @@ -203,21 +223,20 @@ class EmailModule extends PLModule $page->changeTpl('emails/redirect.tpl'); - $uid = S::v('uid'); - $forlife = S::v('forlife'); - - $page->assign('eleve', S::i('promo') >= date("Y") - 5); + $user = S::user(); + $page->assign_by_ref('user', $user); + $page->assign('eleve', $user->promo() >= date("Y") - 5); - $redirect = new Redirect(S::v('uid')); + $redirect = new Redirect($user); // FS#703 : $_GET is urldecoded twice, hence // + (the data) => %2B (in the url) => + (first decoding) => ' ' (second decoding) // Since there can be no spaces in emails, we can fix this with : $email = str_replace(' ', '+', $email); + // Apply email redirection change requests. if ($action == 'remove' && $email) { $retour = $redirect->delete_email($email); - $page->assign('retour', $retour); } if ($action == 'active' && $email) { @@ -239,39 +258,69 @@ class EmailModule extends PLModule $actifs = Env::v('emails_actifs', Array()); print_r(Env::v('emails_rewrite')); if (Env::v('emailop') == "ajouter" && Env::has('email')) { - $page->assign('retour', $redirect->add_email(Env::v('email'))); + $new_email = Env::v('email'); + if ($new_email == "new@example.org") { + $new_email = Env::v('email_new'); + } + $retour = $redirect->add_email($new_email); + if ($retour == ERROR_INVALID_EMAIL) { + $page->assign('email', $new_email); + } + $page->assign('retour', $retour); } elseif (empty($actifs)) { - $page->assign('retour', ERROR_INACTIVE_REDIRECTION); + $retour = ERROR_INACTIVE_REDIRECTION; } elseif (is_array($actifs)) { - $page->assign('retour', $redirect->modify_email($actifs, - Env::v('emails_rewrite',Array()))); + $retour = $redirect->modify_email($actifs, Env::v('emails_rewrite', Array())); } } + switch ($retour) { + case ERROR_INACTIVE_REDIRECTION: + $page->trigError('Tu ne peux pas avoir aucune adresse de redirection active, sinon ton adresse ' + . $user->forlifeEmail() . ' ne fonctionnerait plus.'); + break; + case ERROR_INVALID_EMAIL: + $page->trigError('Erreur: l\'email n\'est pas valide.'); + break; + case ERROR_LOOP_EMAIL: + $page->trigError('Erreur : ' . $user->forlifeEmail() + . ' ne doit pas être renvoyé vers lui-même, ni vers son équivalent en ' + . $globals->mail->domain2 . ' ni vers polytechnique.edu.'); + break; + } + + // Fetch the @alias_dom email alias, if any. $res = XDB::query( "SELECT alias FROM virtual INNER JOIN virtual_redirect USING(vid) WHERE (redirect={?} OR redirect={?}) AND alias LIKE '%@{$globals->mail->alias_dom}'", - $forlife.'@'.$globals->mail->domain, $forlife.'@'.$globals->mail->domain2); + $user->forlifeEmail(), + // TODO: remove this über-ugly hack. The issue is that you need + // to remove all @m4x.org addresses in virtual_redirect first. + $user->login() . '@' . $globals->mail->domain2); $melix = $res->fetchOneCell(); if ($melix) { list($melix) = explode('@', $melix); $page->assign('melix',$melix); } + // Fetch existing email aliases. $res = XDB::query( "SELECT alias,expire FROM aliases WHERE id={?} AND (type='a_vie' OR type='alias') - ORDER BY !FIND_IN_SET('usage',flags), LENGTH(alias)", $uid); - + ORDER BY !FIND_IN_SET('usage',flags), LENGTH(alias)", $user->id()); $page->assign('alias', $res->fetchAllAssoc()); - $page->assign('emails',$redirect->emails); + $page->assign('emails', $redirect->emails); + // Display GoogleApps acount information. require_once 'googleapps.inc.php'; - $page->assign('googleapps', GoogleAppsAccount::account_status($uid)); + $page->assign('googleapps', GoogleAppsAccount::account_status($user->id())); + + require_once 'emails.combobox.inc.php'; + fill_email_combobox($page); } function handler_antispam(&$page, $statut_filtre = null) @@ -282,11 +331,11 @@ class EmailModule extends PLModule $page->changeTpl('emails/antispam.tpl'); - $bogo = new Bogo(S::v('uid')); + $bogo = new Bogo(S::user()); if (isset($statut_filtre)) { $bogo->change($statut_filtre + 0); } - $page->assign('filtre',$bogo->level()); + $page->assign('filtre', $bogo->level()); } function handler_submit(&$page) @@ -298,7 +347,7 @@ class EmailModule extends PLModule if (Post::has('send_email')) { S::assert_xsrf_token(); - $upload = PlUpload::get($_FILES['mail'], S::v('forlife'), 'spam.submit', true); + $upload = PlUpload::get($_FILES['mail'], S::user()->login(), 'spam.submit', true); if (!$upload) { $page->trigError('Une erreur a été rencontrée lors du transfert du fichier'); return; @@ -309,13 +358,15 @@ class EmailModule extends PLModule $page->trigError('Le fichier ne contient pas un email complet'); return; } + $type = (Post::v('type') == 'spam' ? 'spam' : 'nonspam'); + global $globals; - $box = Post::v('type') . '@' . $globals->mail->domain; + $box = $type . '@' . $globals->mail->domain; $mailer = new PlMailer(); $mailer->addTo($box); - $mailer->setFrom('"' . S::v('prenom') . ' ' . S::v('nom') . '" mail->domain . '>'); - $mailer->setTxtBody(Post::v('type') . ' soumis par ' . S::v('forlife') . ' via le web'); - $mailer->addUploadAttachment($upload, Post::v('type') . '.mail'); + $mailer->setFrom('"' . S::user()->fullName() . '" mail->domain . '>'); + $mailer->setTxtBody($type . ' soumis par ' . S::user()->login() . ' via le web'); + $mailer->addUploadAttachment($upload, $type . '.mail'); $mailer->send(); $page->trigSuccess('Le message a été transmis à ' . $box); $upload->clear(); @@ -324,7 +375,6 @@ class EmailModule extends PLModule function handler_send(&$page) { - global $globals; $page->changeTpl('emails/send.tpl'); $page->addJsLink('ajax.js'); @@ -363,7 +413,7 @@ class EmailModule extends PLModule $error = false; foreach ($_FILES as &$file) { - if ($file['name'] && !PlUpload::get($file, S::v('forlife'), 'emails.send', false)) { + if ($file['name'] && !PlUpload::get($file, S::user()->login(), 'emails.send', false)) { $page->trigError(PlUpload::$lastError); $error = true; break; @@ -385,7 +435,7 @@ class EmailModule extends PLModule if (empty($to) && empty($cc) && empty($to2) && empty($bcc) && empty($cc2)) { $page->trigError("Indique au moins un destinataire."); - $page->assign('uploaded_f', PlUpload::listFilenames(S::v('forlife'), 'emails.send')); + $page->assign('uploaded_f', PlUpload::listFilenames(S::user()->login(), 'emails.send')); } else { $mymail = new PlMailer(); $mymail->setFrom($from); @@ -395,7 +445,7 @@ class EmailModule extends PLModule if (!empty($bcc)) { $mymail->addBcc($bcc); } if (!empty($to2)) { $mymail->addTo($to2); } if (!empty($cc2)) { $mymail->addCc($cc2); } - $files =& PlUpload::listFiles(S::v('forlife'), 'emails.send'); + $files =& PlUpload::listFiles(S::user()->login(), 'emails.send'); foreach ($files as $name=>&$upload) { $mymail->addUploadAttachment($upload, $name); } @@ -406,11 +456,11 @@ class EmailModule extends PLModule } if ($mymail->send()) { $page->trigSuccess("Ton email a bien été envoyé."); - $_REQUEST = array('bcc' => S::v('bestalias').'@'.$globals->mail->domain); - PlUpload::clear(S::v('forlife'), 'emails.send'); + $_REQUEST = array('bcc' => S::user()->bestEmail()); + PlUpload::clear(S::user()->login(), 'emails.send'); } else { $page->trigError("Erreur lors de l'envoi du courriel, réessaye."); - $page->assign('uploaded_f', PlUpload::listFilenames(S::v('forlife'), 'emails.send')); + $page->assign('uploaded_f', PlUpload::listFilenames(S::user()->login(), 'emails.send')); } } } @@ -419,8 +469,8 @@ class EmailModule extends PLModule FROM email_send_save WHERE uid = {?}", S::i('uid')); if ($res->numRows() == 0) { - PlUpload::clear(S::v('forlife'), 'emails.send'); - $_REQUEST['bcc'] = S::v('bestalias').'@'.$globals->mail->domain; + PlUpload::clear(S::user()->login(), 'emails.send'); + $_REQUEST['bcc'] = S::user()->bestEmail(); } else { $data = unserialize($res->fetchOneCell()); $_REQUEST = array_merge($_REQUEST, $data); @@ -436,63 +486,136 @@ class EmailModule extends PLModule ORDER BY u.nom, u.prenom", S::v('uid')); $page->assign('contacts', $res->fetchAllAssoc()); $page->assign('maxsize', ini_get('upload_max_filesize') . 'o'); + $page->assign('user', S::user()); } - function handler_test(&$page, $forlife = null) + function handler_test(&$page, $hruid = null) { - global $globals; require_once 'emails.inc.php'; if (!S::has_xsrf_token()) { return PL_FORBIDDEN; } - if (!S::has_perms() || !$forlife) { - $forlife = S::v('bestalias'); + + // Retrieves the User object for the test email recipient. + if (S::has_perms() && $hruid) { + $user = User::getSilent($hruid); + } else { + $user = S::user(); + } + if (!$user) { + return PL_NOT_FOUND; } - $res = XDB::query("SELECT FIND_IN_SET('femme', u.flags), prenom, user_id - FROM auth_user_md5 AS u - INNER JOIN aliases AS a ON (a.id = u.user_id) - WHERE a.alias = {?}", $forlife); - list($sexe, $prenom, $uid) = $res->fetchOneRow(); - $redirect = new Redirect($uid); + // Sends the test email. + $redirect = new Redirect($user); $mailer = new PlMailer('emails/test.mail.tpl'); - $mailer->assign('email', $forlife . '@' . $globals->mail->domain); + $mailer->assign('email', $user->bestEmail()); $mailer->assign('redirects', $redirect->active_emails()); - $mailer->assign('sexe', $sexe); - $mailer->assign('prenom', $prenom); - $mailer->send(); + $mailer->assign('display_name', $user->displayName()); + $mailer->assign('sexe', $user->isFemale()); + $mailer->send($user->isEmailFormatHtml()); exit; } + function handler_rewrite_in(&$page, $mail, $hash) + { + $page->changeTpl('emails/rewrite.tpl'); + $page->assign('option', 'in'); + if (empty($mail) || empty($hash)) { + return PL_NOT_FOUND; + } + $pos = strrpos($mail, '_'); + if ($pos === false) { + return PL_NOT_FOUND; + } + $mail{$pos} = '@'; + $res = XDB::query("SELECT COUNT(*) + FROM emails + WHERE email = {?} AND hash = {?}", + $mail, $hash); + $count = intval($res->fetchOneCell()); + if ($count > 0) { + XDB::query("UPDATE emails + SET allow_rewrite = true, hash = NULL + WHERE email = {?} AND hash = {?}", + $mail, $hash); + $page->trigSuccess("Réécriture activée pour l'adresse " . $mail); + return; + } + return PL_NOT_FOUND; + } + + function handler_rewrite_out(&$page, $mail, $hash) + { + $page->changeTpl('emails/rewrite.tpl'); + $page->assign('option', 'out'); + if (empty($mail) || empty($hash)) { + return PL_NOT_FOUND; + } + $pos = strrpos($mail, '_'); + if ($pos === false) { + return PL_NOT_FOUND; + } + $mail{$pos} = '@'; + $res = XDB::query("SELECT COUNT(*) + FROM emails + WHERE email = {?} AND hash = {?}", + $mail, $hash); + $count = intval($res->fetchOneCell()); + if ($count > 0) { + global $globals; + $res = XDB::query("SELECT e.email, e.rewrite, a.alias + FROM emails AS e + INNER JOIN aliases AS a ON (a.id = e.uid AND a.type = 'a_vie') + WHERE e.email = {?} AND e.hash = {?}", + $mail, $hash); + XDB::query("UPDATE emails + SET allow_rewrite = false, hash = NULL + WHERE email = {?} AND hash = {?}", + $mail, $hash); + list($mail, $rewrite, $forlife) = $res->fetchOneRow(); + $mail = new PlMailer(); + $mail->setFrom("webmaster@" . $globals->mail->domain); + $mail->addTo("support@" . $globals->mail->domain); + $mail->setSubject("Tentative de détournement de correspondance via le rewrite"); + $mail->setTxtBody("$forlife a tenté un rewrite de $mail vers $rewrite. Cette demande a été rejetée via le web"); + $mail->send(); + $page->trigWarning("Un mail d'alerte a été envoyé à l'équipe de " . $globals->core->sitename); + return; + } + return PL_NOT_FOUND; + } + function handler_imap_in(&$page, $hash = null, $login = null) { $page->changeTpl('emails/imap_register.tpl'); - $id = null; + $user = null; if (!empty($hash) || !empty($login)) { - $req = XDB::query("SELECT u.prenom, FIND_IN_SET('femme', u.flags) AS sexe, a.id - FROM aliases AS a - INNER JOIN newsletter_ins AS ni ON (a.id = ni.user_id) - INNER JOIN auth_user_md5 AS u ON (u.user_id = a.id) - WHERE a.alias = {?} AND ni.hash = {?}", $login, $hash); - list($prenom, $sexe, $id) = $req->fetchOneRow(); + $user = User::getSilent($login); + if ($user) { + $req = XDB::query("SELECT 1 FROM newsletter_ins WHERE user_id = {?} AND hash = {?}", $user->id(), $hash); + if ($req->numRows() == 0) { + $user = null; + } + } } require_once('emails.inc.php'); $page->assign('ok', false); - if (S::logged() && (is_null($id) || $id == S::i('uid'))) { - $storage = new EmailStorage(S::i('uid'), 'imap'); + if (S::logged() && (is_null($user) || $user->id() == S::i('uid'))) { + $storage = new EmailStorage(S::user(), 'imap'); $storage->activate(); $page->assign('ok', true); $page->assign('prenom', S::v('prenom')); $page->assign('sexe', S::v('femme')); - } else if (!S::logged() && $id) { - $storage = new EmailStorage($id, 'imap'); + } else if (!S::logged() && $user) { + $storage = new EmailStorage($user, 'imap'); $storage->activate(); $page->assign('ok', true); - $page->assign('prenom', $prenom); - $page->assign('sexe', $sexe); + $page->assign('prenom', $user->displayName()); + $page->assign('sexe', $user->isFemale()); } } @@ -511,19 +634,15 @@ class EmailModule extends PLModule $email = valide_email($email); // vérifications d'usage - $sel = XDB::query( - "SELECT e.uid, a.alias - FROM emails AS e - INNER JOIN aliases AS a ON (e.uid = a.id AND type!='homonyme' - AND FIND_IN_SET('bestalias',a.flags)) - WHERE e.email={?}", $email); - - if (list($uid, $dest) = $sel->fetchOneRow()) { + $sel = XDB::query("SELECT uid FROM emails WHERE email = {?}", $email); + if (($uid = $sel->fetchOneCell())) { + $dest = User::getSilent($uid); + // envoi du mail $message = "Bonjour ! Cet email a été généré automatiquement par le service de patte cassée de -Polytechnique.org car un autre utilisateur, ".S::v('prenom').' '.S::v('nom').", +Polytechnique.org car un autre utilisateur, " . S::user()->fullName() . ", nous a signalé qu'en t'envoyant un email, il avait reçu un message d'erreur indiquant que ton adresse de redirection $email ne fonctionnait plus ! @@ -541,7 +660,7 @@ L'équipe d'administration mail->domain . '>'; $mail = new PlMailer(); $mail->setFrom('"Polytechnique.org" mail->domain . '>'); - $mail->addTo("$dest@" . $globals->mail->domain); + $mail->addTo($dest->bestEmail()); $mail->setSubject("Une de tes adresse de redirection Polytechnique.org ne marche plus !!"); $mail->setTxtBody($message); $mail->send(); @@ -561,13 +680,12 @@ L'équipe d'administration mail->domain . '>'; $sel = XDB::query( "SELECT e1.uid, e1.panne != 0 AS panne, (count(e2.uid) + IF(FIND_IN_SET('googleapps', u.mail_storage), 1, 0)) AS nb_mails, - u.nom, u.prenom, u.promo, a.alias AS forlife + u.nom, u.prenom, u.promo, u.hruid 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 auth_user_md5 as u ON(e1.uid = u.user_id) - INNER JOIN aliases AS a ON (a.id = e1.uid AND a.type = 'a_vie') WHERE e1.email = {?} GROUP BY e1.uid", $email); if ($x = $sel->fetchOneAssoc()) { @@ -686,15 +804,13 @@ L'équipe d'administration mail->domain . '>'; { $page->changeTpl('emails/lost.tpl'); - $page->assign('lost_emails', XDB::iterator(' - SELECT u.user_id, a.alias + $page->assign('lost_emails', XDB::iterator(" + SELECT u.user_id, u.hruid FROM auth_user_md5 AS u - INNER JOIN aliases AS a ON (a.id = u.user_id AND a.type = "a_vie") - LEFT JOIN emails AS e ON (u.user_id=e.uid AND FIND_IN_SET("active",e.flags)) - WHERE e.uid IS NULL AND - FIND_IN_SET("googleapps", u.mail_storage) = 0 AND - u.deces = 0 - ORDER BY u.promo DESC, u.nom, u.prenom')); + LEFT JOIN emails AS e ON (u.user_id = e.uid AND FIND_IN_SET('active', e.flags)) + WHERE e.uid IS NULL AND FIND_IN_SET('googleapps', u.mail_storage) = 0 AND + u.deces = 0 AND u.perms IN ('user', 'admin', 'disabled') + ORDER BY u.promo DESC, u.nom, u.prenom")); } }