modules/marketing.php

   1 <?php
   2 /***************************************************************************
   3  *  Copyright (C) 2003-2009 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         // Quelques statistiques
  46
  47         $res   = XDB::query(
  48                   "SELECT COUNT(*) AS vivants,
  49                           COUNT(NULLIF(perms='admin' OR perms='user', 0)) AS inscrits,
  50                           100*COUNT(NULLIF(perms='admin' OR perms='user', 0))/COUNT(*) AS ins_rate,
  51                           COUNT(NULLIF(promo >= 1972, 0)) AS vivants72,
  52                           COUNT(NULLIF(promo >= 1972 AND (perms='admin' OR perms='user'), 0)) AS inscrits72,
  53                           100 * COUNT(NULLIF(promo >= 1972 AND (perms='admin' OR perms='user'), 0)) /
  54                               COUNT(NULLIF(promo >= 1972, 0)) AS ins72_rate,
  55                           COUNT(NULLIF(FIND_IN_SET('femme', flags), 0)) AS vivantes,
  56                           COUNT(NULLIF(FIND_IN_SET('femme', flags) AND (perms='admin' OR perms='user'), 0)) AS inscrites,
  57                           100 * COUNT(NULLIF(FIND_IN_SET('femme', flags) AND (perms='admin' OR perms='user'), 0)) /
  58                               COUNT(NULLIF(FIND_IN_SET('femme', flags), 0)) AS inse_rate
  59                      FROM auth_user_md5
  60                     WHERE deces = 0");
  61         $stats = $res->fetchOneAssoc();
  62         $page->assign('stats', $stats);
  63
  64         $res   = XDB::query("SELECT count(*) FROM auth_user_md5 WHERE date_ins > ".
  65                                       date('Ymd000000', strtotime('1 week ago')));
  66         $page->assign('nbInsSem', $res->fetchOneCell());
  67
  68         $res = XDB::query("SELECT count(*) FROM register_pending WHERE hash != 'INSCRIT'");
  69         $page->assign('nbInsEnCours', $res->fetchOneCell());
  70
  71         $res = XDB::query("SELECT count(*) FROM register_marketing");
  72         $page->assign('nbInsMarket', $res->fetchOneCell());
  73
  74         $res = XDB::query("SELECT count(*) FROM register_mstats
  75                                       WHERE TO_DAYS(NOW()) - TO_DAYS(success) <= 7");
  76         $page->assign('nbInsMarkOK', $res->fetchOneCell());
  77     }
  78
  79     function handler_private(&$page, $hruid = null,
  80                              $action = null, $value = null)
  81     {
  82         global $globals;
  83         $page->changeTpl('marketing/private.tpl');
  84
  85         $user = User::getSilent($hruid);
  86         if (!$user) {
  87             return PL_NOT_FOUND;
  88         }
  89
  90         // Retrieves marketed user details.
  91         $res = XDB::query(
  92             "SELECT  matricule
  93                FROM  auth_user_md5
  94               WHERE  user_id = {?} AND perms = 'pending'", $user->id());
  95         if (!($matricule = $res->fetchOneCell())) {
  96             $page->kill("Cet utilisateur est déjà inscrit au site.");
  97         }
  98
  99         require_once('user.func.inc.php');
 100         $matricule = $res->fetchOneCell();
 101         $matricule_X = get_X_mat($matricule);
 102
 103         $page->assign('full_name', $user->fullName());
 104         $page->assign('promo', $user->promo());
 105         $page->assign('matricule', $matricule);
 106         $page->assign('matricule_X',$matricule_X);
 107
 108         // Applies in-parameter action to the user.
 109         if ($action == 'del') {
 110             S::assert_xsrf_token();
 111             Marketing::clear($user->id(), $value);
 112         }
 113
 114         if ($action == 'rel') {
 115             $market = Marketing::get($user->id(), $value);
 116             if ($market == null) {
 117                 $page->trigWarning("Aucun marketing n'a été effectué vers $value");
 118             } else {
 119                 $to    = $market->user['to'];
 120                 $title = $market->getTitle();
 121                 $text  = $market->getText();
 122                 $from  = $market->sender_mail;
 123                 $page->assign('rel_from_user', $from);
 124                 $page->assign('rel_from_staff',
 125                               '"Equipe Polytechnique.org" <register@' . $globals->mail->domain . '>');
 126                 $page->assign('rel_to', $to);
 127                 $page->assign('rel_title', $title);
 128                 $page->assign('rel_text', $text);
 129                 $page->assign('rel_email', $value);
 130             }
 131         }
 132
 133         if ($action == 'relforce') {
 134             S::assert_xsrf_token();
 135
 136             $market = Marketing::get($user->id(), Post::v('to'));
 137             if (is_null($market)) {
 138                 $market = new Marketing($user->id(), Post::v('to'), 'default', null, 'staff');
 139             }
 140             $market->send(Post::v('title'), Post::v('message'));
 141             $page->trigSuccess("Email envoyé");
 142         }
 143
 144         if ($action == 'insrel') {
 145             S::assert_xsrf_token();
 146             if (Marketing::relance($user->id())) {
 147                 $page->trigSuccess('relance faite');
 148             }
 149         }
 150
 151         if ($action == 'add' && Post::has('email') && Post::has('type')) {
 152             $market = new Marketing($user->id(), Post::v('email'), 'default', null, Post::v('type'), S::v('uid'));
 153             $market->add(false);
 154         }
 155
 156         // Retrieves and display the existing marketing attempts.
 157         $res = XDB::iterator(
 158                 "SELECT  r.*, a.alias
 159                    FROM  register_marketing AS r
 160               LEFT JOIN  aliases            AS a ON (r.sender=a.id AND a.type = 'a_vie')
 161                   WHERE  uid={?}
 162                ORDER BY  date", $user->id());
 163         $page->assign('addr', $res);
 164
 165         $res = XDB::query("SELECT date, relance FROM register_pending
 166                             WHERE uid = {?}", $user->id());
 167         if (list($pending, $relance) = $res->fetchOneRow()) {
 168             $page->assign('pending', $pending);
 169             $page->assign('relance', $relance);
 170         }
 171
 172         $page->assign('path', 'marketing/private/' . $user->login());
 173     }
 174
 175     function handler_broken(&$page, $uid = null)
 176     {
 177         $page->changeTpl('marketing/broken.tpl');
 178
 179         if (is_null($uid)) {
 180             return PL_NOT_FOUND;
 181         }
 182
 183         $user = User::get($uid);
 184         if (!$user) {
 185             return PL_NOT_FOUND;
 186         } elseif ($user->login() == S::user()->login()) {
 187             pl_redirect('emails/redirect');
 188         }
 189
 190         $res = XDB::query(
 191                 "SELECT  u.deces = '0000-00-00' AS alive, e.last,
 192                          IF(e.email IS NOT NULL, e.email, IF(FIND_IN_SET('googleapps', u.mail_storage), 'googleapps', NULL)) AS email
 193                    FROM  auth_user_md5 AS u
 194               LEFT JOIN  emails        AS e ON (e.flags = 'active' AND e.uid = u.user_id)
 195                   WHERE  u.user_id = {?}
 196                ORDER BY  e.panne_level, e.last", $user->id());
 197         if (!$res->numRows()) {
 198             return PL_NOT_FOUND;
 199         }
 200         $user->addProperties($res->fetchOneAssoc());
 201         $page->assign('user', $user);
 202
 203         $email = null;
 204         require_once 'emails.inc.php';
 205         if (Post::has('mail')) {
 206             $email = valide_email(Post::v('mail'));
 207         }
 208         if (Post::has('valide') && isvalid_email_redirection($email)) {
 209             S::assert_xsrf_token();
 210
 211             // security stuff
 212             check_email($email, "Proposition d'une adresse surveillee pour " . $user->login() . " par " . S::user()->login());
 213             $res = XDB::query("SELECT  flags
 214                                  FROM  emails
 215                                 WHERE  email = {?} AND uid = {?}", $email, $user->id());
 216             $state = $res->numRows() ? $res->fetchOneCell() : null;
 217             if ($state == 'panne') {
 218                 $page->trigWarning("L'adresse que tu as fournie est l'adresse actuelle de {$user->fullName()} et est en panne.");
 219             } elseif ($state == 'active') {
 220                 $page->trigWarning("L'adresse que tu as fournie est l'adresse actuelle de {$user->fullName()}");
 221             } elseif ($user->email && !trim(Post::v('comment'))) {
 222                 $page->trigError("Il faut que tu ajoutes un commentaire à ta proposition pour justifier le "
 223                                . "besoin de changer la redirection de {$user->fullName()}.");
 224             } else {
 225                 require_once 'validations.inc.php';
 226                 $valid = new BrokenReq(S::user(), $user, $email, trim(Post::v('comment')));
 227                 $valid->submit();
 228                 $page->assign('sent', true);
 229             }
 230         } elseif ($email) {
 231             $page->trigError("L'adresse proposée n'est pas une adresse acceptable pour une redirection");
 232         }
 233     }
 234
 235     function handler_promo(&$page, $promo = null)
 236     {
 237         $page->changeTpl('marketing/promo.tpl');
 238
 239         if (is_null($promo)) {
 240             $promo = S::v('promo');
 241         }
 242         $page->assign('promo', $promo);
 243
 244         $sql = "SELECT  u.user_id, u.nom, u.prenom, u.last_known_email, u.matricule_ax,
 245                         IF(MAX(m.last) > p.relance, MAX(m.last), p.relance) AS dern_rel, p.email
 246                   FROM  auth_user_md5      AS u
 247              LEFT JOIN  register_pending   AS p ON p.uid = u.user_id
 248              LEFT JOIN  register_marketing AS m ON m.uid = u.user_id
 249                  WHERE  u.promo = {?} AND u.deces = 0 AND u.perms='pending'
 250               GROUP BY  u.user_id
 251               ORDER BY  nom, prenom";
 252         $page->assign('nonins', XDB::iterator($sql, $promo));
 253     }
 254
 255     function handler_public(&$page, $hruid = null)
 256     {
 257         $page->changeTpl('marketing/public.tpl');
 258
 259         // Retrieves the user info, and checks the user is not yet registered.
 260         $user = User::getSilent($hruid);
 261         if (!$user) {
 262             return PL_NOT_FOUND;
 263         }
 264
 265         $res = XDB::query(
 266             "SELECT  COUNT(*)
 267                FROM  auth_user_md5
 268               WHERE  user_id = {?} AND perms = 'pending'", $user->id());
 269         if (!$res->fetchOneCell()) {
 270             $page->kill("Cet utilisateur est déjà inscrit au site.");
 271         }
 272
 273         // Displays the page, and handles the eventual user actions.
 274         $page->assign('full_name', $user->fullName());
 275         $page->assign('promo', $user->promo());
 276
 277         if (Post::has('valide')) {
 278             S::assert_xsrf_token();
 279             $email = trim(Post::v('mail'));
 280
 281             require_once 'emails.inc.php';
 282             if (!isvalid_email_redirection($email)) {
 283                 $page->trigError("Email invalide !");
 284             } else {
 285                 // On cherche les marketings précédents sur cette adresse
 286                 // email, en se restreignant au dernier mois
 287
 288                 if (Marketing::get($user->id(), $email, true)) {
 289                     $page->assign('already', true);
 290                 } else {
 291                     $page->assign('ok', true);
 292                     check_email($email, "Une adresse surveillée est proposée au marketing par " . S::user()->login());
 293                     $market = new Marketing($user->id(), $email, 'default', null, Post::v('origine'), S::v('uid'));
 294                     $market->add();
 295                 }
 296             }
 297         }
 298     }
 299
 300     function handler_week(&$page, $sorting = 'per_promo')
 301     {
 302         $page->changeTpl('marketing/this_week.tpl');
 303
 304         $sort = $sorting == 'per_promo' ? 'promo' : 'date_ins';
 305
 306         $sql = "SELECT  a.alias AS forlife, u.date_ins, u.promo, u.nom, u.prenom
 307                   FROM  auth_user_md5  AS u
 308             INNER JOIN  aliases        AS a ON (u.user_id = a.id AND a.type='a_vie')
 309                  WHERE  u.date_ins > ".date("Ymd000000", strtotime ('1 week ago'))."
 310               ORDER BY  u.$sort DESC";
 311         $page->assign('ins', XDB::iterator($sql));
 312     }
 313
 314     function handler_volontaire(&$page, $promo = null)
 315     {
 316         $page->changeTpl('marketing/volontaire.tpl');
 317
 318         $res = XDB::query(
 319                 "SELECT
 320                DISTINCT  a.promo
 321                    FROM  register_marketing AS m
 322              INNER JOIN  auth_user_md5      AS a  ON a.user_id = m.uid
 323                ORDER BY  a.promo");
 324         $page->assign('promos', $res->fetchColumn());
 325
 326
 327         if (!is_null($promo)) {
 328             $sql = "SELECT  a.nom, a.prenom, a.user_id,
 329                             m.email, sa.alias AS forlife
 330                       FROM  register_marketing AS m
 331                 INNER JOIN  auth_user_md5      AS a  ON a.user_id = m.uid AND a.promo = {?}
 332                 INNER JOIN  aliases            AS sa ON (m.sender = sa.id AND sa.type='a_vie')
 333                   ORDER BY  a.nom";
 334             $page->assign('addr', XDB::iterator($sql, $promo));
 335         }
 336     }
 337
 338     function handler_relance(&$page)
 339     {
 340         $page->changeTpl('marketing/relance.tpl');
 341
 342         if (Post::has('relancer')) {
 343             $res   = XDB::query("SELECT COUNT(*) FROM auth_user_md5 WHERE deces=0");
 344             $nbdix = $res->fetchOneCell();
 345
 346             $sent  = Array();
 347             foreach (array_keys($_POST['relance']) as $uid) {
 348                 if ($tmp = Marketing::relance($uid, $nbdix)) {
 349                     $sent[] = $tmp . ' a été relancé.';
 350                 }
 351             }
 352             $page->assign('sent', $sent);
 353         }
 354
 355         $sql = "SELECT  r.date, r.relance, r.uid, u.promo, u.nom, u.prenom
 356                   FROM  register_pending AS r
 357             INNER JOIN  auth_user_md5    AS u ON r. uid = u.user_id
 358                  WHERE  hash!='INSCRIT'
 359               ORDER BY  date DESC";
 360         $page->assign('relance', XDB::iterator($sql));
 361     }
 362 }
 363
 364 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
 365 ?>