modules/marketing.php

   1 <?php
   2 /***************************************************************************
   3  *  Copyright (C) 2003-2010 Polytechnique.org                              *
   4  *  http://opensource.polytechnique.org/                                   *
   5  *                                                                         *
   6  *  This program is free software; you can redistribute it and/or modify   *
   7  *  it under the terms of the GNU General Public License as published by   *
   8  *  the Free Software Foundation; either version 2 of the License, or      *
   9  *  (at your option) any later version.                                    *
  10  *                                                                         *
  11  *  This program is distributed in the hope that it will be useful,        *
  12  *  but WITHOUT ANY WARRANTY; without even the implied warranty of         *
  13  *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the          *
  14  *  GNU General Public License for more details.                           *
  15  *                                                                         *
  16  *  You should have received a copy of the GNU General Public License      *
  17  *  along with this program; if not, write to the Free Software            *
  18  *  Foundation, Inc.,                                                      *
  19  *  59 Temple Place, Suite 330, Boston, MA  02111-1307  USA                *
  20  ***************************************************************************/
  21
  22 class MarketingModule extends PLModule
  23 {
  24     function handlers()
  25     {
  26         return array(
  27             'marketing'            => $this->make_hook('marketing',  AUTH_MDP, 'admin'),
  28             'marketing/promo'      => $this->make_hook('promo',      AUTH_MDP, 'admin'),
  29             'marketing/relance'    => $this->make_hook('relance',    AUTH_MDP, 'admin'),
  30             'marketing/this_week'  => $this->make_hook('week',       AUTH_MDP, 'admin'),
  31             'marketing/volontaire' => $this->make_hook('volontaire', AUTH_MDP, 'admin'),
  32
  33             'marketing/private'    => $this->make_hook('private',    AUTH_MDP, 'admin'),
  34             'marketing/public'     => $this->make_hook('public',     AUTH_COOKIE),
  35             'marketing/broken'     => $this->make_hook('broken',     AUTH_COOKIE),
  36         );
  37     }
  38
  39     function handler_marketing(&$page)
  40     {
  41         $page->changeTpl('marketing/index.tpl');
  42
  43         $page->setTitle('Marketing');
  44
  45         // Some statistics
  46         $res = XDB::query(
  47                 "SELECT  COUNT(*) AS vivants,
  48                          COUNT(NULLIF(perms='admin' OR perms='user', 0)) AS inscrits,
  49                          100*COUNT(NULLIF(perms='admin' OR perms='user', 0))/COUNT(*) AS ins_rate,
  50                          COUNT(NULLIF(promo >= 1972, 0)) AS vivants72,
  51                          COUNT(NULLIF(promo >= 1972 AND (perms='admin' OR perms='user'), 0)) AS inscrits72,
  52                          100 * COUNT(NULLIF(promo >= 1972 AND (perms='admin' OR perms='user'), 0)) /
  53                              COUNT(NULLIF(promo >= 1972, 0)) AS ins72_rate,
  54                          COUNT(NULLIF(FIND_IN_SET('femme', flags), 0)) AS vivantes,
  55                          COUNT(NULLIF(FIND_IN_SET('femme', flags) AND (perms='admin' OR perms='user'), 0)) AS inscrites,
  56                          100 * COUNT(NULLIF(FIND_IN_SET('femme', flags) AND (perms='admin' OR perms='user'), 0)) /
  57                              COUNT(NULLIF(FIND_IN_SET('femme', flags), 0)) AS inse_rate
  58                    FROM  auth_user_md5
  59                   WHERE  deces = 0");
  60         $stats = $res->fetchOneAssoc();
  61         $page->assign('stats', $stats);
  62
  63         $res = XDB::query('SELECT  COUNT(*)
  64                              FROM  auth_user_md5
  65                             WHERE  date_ins > ' . date('Ymd000000', strtotime('1 week ago')));
  66         $page->assign('nbInsSem', $res->fetchOneCell());
  67
  68         $res = XDB::query("SELECT  COUNT(*)
  69                              FROM  register_pending
  70                             WHERE  hash != 'INSCRIT'");
  71         $page->assign('nbInsEnCours', $res->fetchOneCell());
  72
  73         $res = XDB::query('SELECT  COUNT(*) AS count
  74                              FROM  register_marketing
  75                          GROUP BY  sender = 0');
  76         $nbInsMarketNo = $res->fetchAllAssoc();
  77         $res = XDB::query('SELECT  COUNT(*)
  78                              FROM  register_marketing
  79                             WHERE  TO_DAYS(NOW()) - TO_DAYS(last) <= 7');
  80         $page->assign('nbInsMarketNoPerso', $nbInsMarketNo[0]['count']);
  81         $page->assign('nbInsMarketNoXorg', $nbInsMarketNo[1]['count']);
  82         $page->assign('nbInsMarketNoWeek', $res->fetchOneCell());
  83
  84         $res = XDB::query('SELECT  COUNT(*) AS count
  85                              FROM  register_mstats
  86                          GROUP BY  sender = 0');
  87         $nbInsMarketOk = $res->fetchAllAssoc();
  88         $res = XDB::query('SELECT  COUNT(*)
  89                              FROM  register_mstats
  90                             WHERE  TO_DAYS(NOW()) - TO_DAYS(success) <= 7');
  91         $page->assign('nbInsMarketOkPerso', $nbInsMarketOk[0]['count']);
  92         $page->assign('nbInsMarketOkXorg', $nbInsMarketOk[1]['count']);
  93         $page->assign('nbInsMarketOkWeek', $res->fetchOneCell());
  94     }
  95
  96     function handler_private(&$page, $hruid = null,
  97                              $action = null, $value = null)
  98     {
  99         global $globals;
 100         $page->changeTpl('marketing/private.tpl');
 101
 102         $user = User::getSilent($hruid);
 103         if (!$user) {
 104             return PL_NOT_FOUND;
 105         }
 106
 107         // Retrieves marketed user details.
 108         $res = XDB::query(
 109             "SELECT  matricule
 110                FROM  auth_user_md5
 111               WHERE  user_id = {?} AND perms = 'pending'", $user->id());
 112         if (!($matricule = $res->fetchOneCell())) {
 113             $page->kill("Cet utilisateur est déjà inscrit au site.");
 114         }
 115
 116         require_once('user.func.inc.php');
 117         $matricule = $res->fetchOneCell();
 118         $matricule_X = get_X_mat($matricule);
 119
 120         $page->assign('full_name', $user->fullName());
 121         $page->assign('promo', $user->promo());
 122         $page->assign('matricule', $matricule);
 123         $page->assign('matricule_X',$matricule_X);
 124
 125         // Applies in-parameter action to the user.
 126         if ($action == 'del') {
 127             S::assert_xsrf_token();
 128             Marketing::clear($user->id(), $value);
 129         }
 130
 131         if ($action == 'rel') {
 132             $market = Marketing::get($user->id(), $value);
 133             if ($market == null) {
 134                 $page->trigWarning("Aucun marketing n'a été effectué vers $value");
 135             } else {
 136                 $to    = $market->user['to'];
 137                 $title = $market->getTitle();
 138                 $text  = $market->getText();
 139                 $from  = $market->sender_mail;
 140                 $page->assign('rel_from_user', $from);
 141                 $page->assign('rel_from_staff',
 142                               '"Equipe Polytechnique.org" <register@' . $globals->mail->domain . '>');
 143                 $page->assign('rel_to', $to);
 144                 $page->assign('rel_title', $title);
 145                 $page->assign('rel_text', $text);
 146                 $page->assign('rel_email', $value);
 147             }
 148         }
 149
 150         if ($action == 'relforce') {
 151             S::assert_xsrf_token();
 152
 153             $market = Marketing::get($user->id(), Post::v('to'));
 154             if (is_null($market)) {
 155                 $market = new Marketing($user->id(), Post::v('to'), 'default', null, 'staff');
 156             }
 157             $market->send(Post::v('title'), Post::v('message'));
 158             $page->trigSuccess("Email envoyé");
 159         }
 160
 161         if ($action == 'insrel') {
 162             S::assert_xsrf_token();
 163             if (Marketing::relance($user->id())) {
 164                 $page->trigSuccess('relance faite');
 165             }
 166         }
 167
 168         if ($action == 'add' && Post::has('email') && Post::has('type')) {
 169             $market = new Marketing($user->id(), Post::v('email'), 'default', null, Post::v('type'), S::v('uid'));
 170             $market->add(false);
 171         }
 172
 173         // Retrieves and display the existing marketing attempts.
 174         $res = XDB::iterator(
 175                 "SELECT  r.*, a.alias
 176                    FROM  register_marketing AS r
 177               LEFT JOIN  aliases            AS a ON (r.sender=a.id AND a.type = 'a_vie')
 178                   WHERE  uid={?}
 179                ORDER BY  date", $user->id());
 180         $page->assign('addr', $res);
 181
 182         $res = XDB::query("SELECT date, relance FROM register_pending
 183                             WHERE uid = {?}", $user->id());
 184         if (list($pending, $relance) = $res->fetchOneRow()) {
 185             $page->assign('pending', $pending);
 186             $page->assign('relance', $relance);
 187         }
 188
 189         $page->assign('path', 'marketing/private/' . $user->login());
 190     }
 191
 192     function handler_broken(&$page, $uid = null)
 193     {
 194         $page->changeTpl('marketing/broken.tpl');
 195
 196         if (is_null($uid)) {
 197             return PL_NOT_FOUND;
 198         }
 199
 200         $user = User::get($uid);
 201         if (!$user) {
 202             return PL_NOT_FOUND;
 203         } elseif ($user->login() == S::user()->login()) {
 204             pl_redirect('emails/redirect');
 205         }
 206
 207         $res = XDB::query(
 208                 "SELECT  u.deces = '0000-00-00' AS alive, e.last,
 209                          IF(e.email IS NOT NULL, e.email, IF(FIND_IN_SET('googleapps', u.mail_storage), 'googleapps', NULL)) AS email
 210                    FROM  auth_user_md5 AS u
 211               LEFT JOIN  emails        AS e ON (e.flags = 'active' AND e.uid = u.user_id)
 212                   WHERE  u.user_id = {?}
 213                ORDER BY  e.panne_level, e.last", $user->id());
 214         if (!$res->numRows()) {
 215             return PL_NOT_FOUND;
 216         }
 217         $user->addProperties($res->fetchOneAssoc());
 218         $page->assign('user', $user);
 219
 220         $email = null;
 221         require_once 'emails.inc.php';
 222         if (Post::has('mail')) {
 223             $email = valide_email(Post::v('mail'));
 224         }
 225         if (Post::has('valide') && isvalid_email_redirection($email)) {
 226             S::assert_xsrf_token();
 227
 228             // security stuff
 229             check_email($email, "Proposition d'une adresse surveillee pour " . $user->login() . " par " . S::user()->login());
 230             $res = XDB::query("SELECT  flags
 231                                  FROM  emails
 232                                 WHERE  email = {?} AND uid = {?}", $email, $user->id());
 233             $state = $res->numRows() ? $res->fetchOneCell() : null;
 234             if ($state == 'panne') {
 235                 $page->trigWarning("L'adresse que tu as fournie est l'adresse actuelle de {$user->fullName()} et est en panne.");
 236             } elseif ($state == 'active') {
 237                 $page->trigWarning("L'adresse que tu as fournie est l'adresse actuelle de {$user->fullName()}");
 238             } elseif ($user->email && !trim(Post::v('comment'))) {
 239                 $page->trigError("Il faut que tu ajoutes un commentaire à ta proposition pour justifier le "
 240                                . "besoin de changer la redirection de {$user->fullName()}.");
 241             } else {
 242                 require_once 'validations.inc.php';
 243                 $valid = new BrokenReq(S::user(), $user, $email, trim(Post::v('comment')));
 244                 $valid->submit();
 245                 $page->assign('sent', true);
 246             }
 247         } elseif ($email) {
 248             $page->trigError("L'adresse proposée n'est pas une adresse acceptable pour une redirection");
 249         }
 250     }
 251
 252     function handler_promo(&$page, $promo = null)
 253     {
 254         $page->changeTpl('marketing/promo.tpl');
 255
 256         if (is_null($promo)) {
 257             $promo = S::v('promo');
 258         }
 259         $page->assign('promo', $promo);
 260
 261         $sql = "SELECT  u.user_id, u.hruid, u.nom, u.prenom, u.last_known_email,
 262                         IF(MAX(m.last) > p.relance, MAX(m.last), p.relance) AS dern_rel, p.email
 263                   FROM  auth_user_md5      AS u
 264              LEFT JOIN  register_pending   AS p ON p.uid = u.user_id
 265              LEFT JOIN  register_marketing AS m ON m.uid = u.user_id
 266                  WHERE  u.promo = {?} AND u.deces = 0 AND u.perms='pending'
 267               GROUP BY  u.user_id
 268               ORDER BY  nom, prenom";
 269         $page->assign('nonins', XDB::iterator($sql, $promo));
 270     }
 271
 272     function handler_public(&$page, $hruid = null)
 273     {
 274         $page->changeTpl('marketing/public.tpl');
 275
 276         // Retrieves the user info, and checks the user is not yet registered.
 277         $user = User::getSilent($hruid);
 278         if (!$user) {
 279             return PL_NOT_FOUND;
 280         }
 281
 282         $res = XDB::query(
 283             "SELECT  COUNT(*)
 284                FROM  auth_user_md5
 285               WHERE  user_id = {?} AND perms = 'pending'", $user->id());
 286         if (!$res->fetchOneCell()) {
 287             $page->kill("Cet utilisateur est déjà inscrit au site.");
 288         }
 289
 290         // Displays the page, and handles the eventual user actions.
 291         $page->assign('full_name', $user->fullName());
 292         $page->assign('promo', $user->promo());
 293
 294         if (Post::has('valide')) {
 295             S::assert_xsrf_token();
 296             $email = trim(Post::v('mail'));
 297
 298             require_once 'emails.inc.php';
 299             if (!isvalid_email_redirection($email)) {
 300                 $page->trigError('Email invalide&nbsp;!');
 301             } else {
 302                 // On cherche les marketings précédents sur cette adresse
 303                 // email, en se restreignant au dernier mois
 304
 305                 if (Marketing::get($user->id(), $email, true)) {
 306                     $page->assign('already', true);
 307                 } else {
 308                     $page->assign('ok', true);
 309                     check_email($email, "Une adresse surveillée est proposée au marketing par " . S::user()->login());
 310                     $market = new Marketing($user->id(), $email, 'default', null, Post::v('origine'), S::v('uid'),
 311                                             Post::v('origine') == 'user' ? Post::v('personal_notes') : null);
 312                     $market->add();
 313                 }
 314             }
 315         } else {
 316             global $globals;
 317             require_once 'marketing.inc.php';
 318
 319             $sender = User::getSilent(S::v('uid'));
 320             $market = new AnnuaireMarketing(null, true);
 321             $text = $market->getText(array(
 322                 'sexe'           => $user->isFemale(),
 323                 'forlife_email'  => $user->login() . '@' . $globals->mail->domain,
 324                 'forlife_email2' => $user->login() . '@' . $globals->mail->domain2
 325             ));
 326             $text = str_replace('%%hash%%', '', $text);
 327             $text = str_replace('%%personal_notes%%', '<em id="personal_notes_display"></em>', $text);
 328             $text = str_replace('%%sender%%',
 329                                 "<span id=\"sender\">" . $sender->fullName() . '</span>', $text);
 330             $page->assign('text', nl2br($text));
 331             // TODO (JAC): define a unique Xorg signature for all the emails we send.
 332             $page->assign('xorg_signature', "L'équipe de Polytechnique.org,<br />Le portail des élèves & anciens élèves de l'École polytechnique");
 333             $page->assign('perso_signature', $sender->fullName());
 334         }
 335     }
 336
 337     function handler_week(&$page, $sorting = 'per_promo')
 338     {
 339         $page->changeTpl('marketing/this_week.tpl');
 340
 341         $sort = $sorting == 'per_promo' ? 'promo' : 'date_ins';
 342
 343         $sql = "SELECT  a.alias AS forlife, u.date_ins, u.promo, u.nom, u.prenom
 344                   FROM  auth_user_md5  AS u
 345             INNER JOIN  aliases        AS a ON (u.user_id = a.id AND a.type='a_vie')
 346                  WHERE  u.date_ins > ".date("Ymd000000", strtotime ('1 week ago'))."
 347               ORDER BY  u.$sort DESC";
 348         $page->assign('ins', XDB::iterator($sql));
 349     }
 350
 351     function handler_volontaire(&$page, $promo = null)
 352     {
 353         $page->changeTpl('marketing/volontaire.tpl');
 354
 355         $res = XDB::query(
 356                 "SELECT
 357                DISTINCT  a.promo
 358                    FROM  register_marketing AS m
 359              INNER JOIN  auth_user_md5      AS a  ON a.user_id = m.uid
 360                ORDER BY  a.promo");
 361         $page->assign('promos', $res->fetchColumn());
 362
 363
 364         if (!is_null($promo)) {
 365             $sql = "SELECT  a.nom, a.prenom, a.user_id,
 366                             m.email, sa.alias AS forlife
 367                       FROM  register_marketing AS m
 368                 INNER JOIN  auth_user_md5      AS a  ON a.user_id = m.uid AND a.promo = {?}
 369                 INNER JOIN  aliases            AS sa ON (m.sender = sa.id AND sa.type='a_vie')
 370                   ORDER BY  a.nom";
 371             $page->assign('addr', XDB::iterator($sql, $promo));
 372         }
 373     }
 374
 375     function handler_relance(&$page)
 376     {
 377         $page->changeTpl('marketing/relance.tpl');
 378
 379         if (Post::has('relancer')) {
 380             $res   = XDB::query("SELECT COUNT(*) FROM auth_user_md5 WHERE deces=0");
 381             $nbdix = $res->fetchOneCell();
 382
 383             $sent  = Array();
 384             foreach (array_keys($_POST['relance']) as $uid) {
 385                 if ($tmp = Marketing::relance($uid, $nbdix)) {
 386                     $sent[] = $tmp . ' a été relancé.';
 387                 }
 388             }
 389             $page->assign('sent', $sent);
 390         }
 391
 392         $sql = "SELECT  r.date, r.relance, r.uid, u.promo, u.nom, u.prenom
 393                   FROM  register_pending AS r
 394             INNER JOIN  auth_user_md5    AS u ON r. uid = u.user_id
 395                  WHERE  hash!='INSCRIT'
 396               ORDER BY  date DESC";
 397         $page->assign('relance', XDB::iterator($sql));
 398     }
 399 }
 400
 401 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
 402 ?>