modules/marketing.php

   1 <?php
   2 /***************************************************************************
   3  *  Copyright (C) 2003-2006 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->assign('xorg_title','Polytechnique.org - 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, $uid = null,
  80                              $action = null, $value = null)
  81     {
  82         $page->changeTpl('marketing/private.tpl');
  83
  84         if (is_null($uid)) {
  85             return PL_NOT_FOUND;
  86         }
  87
  88         $page->assign('path', 'marketing/private/'.$uid);
  89
  90         $res = XDB::query("SELECT nom, prenom, promo, matricule
  91                                        FROM auth_user_md5
  92                                       WHERE user_id={?} AND perms='pending'", $uid);
  93
  94         if (list($nom, $prenom, $promo, $matricule) = $res->fetchOneRow()) {
  95             require_once('register.inc.php');
  96             $matricule_X = get_X_mat($matricule);
  97             $page->assign('nom', $nom);
  98             $page->assign('prenom', $prenom);
  99             $page->assign('promo', $promo);
 100             $page->assign('matricule', $matricule);
 101             $page->assign('matricule_X',$matricule_X);
 102         } else {
 103             $page->kill('uid invalide');
 104         }
 105
 106         if ($action == 'del') {
 107             XDB::execute('DELETE FROM register_marketing WHERE uid={?} AND email={?}',
 108                                    $uid, $value);
 109         }
 110
 111         if ($action == 'rel') {
 112             require_once('marketing.inc.php');
 113             list($to, $title, $text) = mark_text_mail($uid, $value);
 114             $from = mark_from_mail($uid, $value);
 115             $page->assign('rel_from_user', $from);
 116             $page->assign('rel_from_staff',
 117                           "\"Equipe Polytechnique.org\" <register@polytechnique.org>");
 118             $page->assign('rel_to', $to);
 119             $page->assign('rel_title', $title);
 120             $page->assign('rel_text', $text);
 121             $page->assign('rel_email', $value);
 122         }
 123
 124         if ($action == 'relforce') {
 125             require_once('marketing.inc.php');
 126             mark_send_mail($uid, $value, Post::v('from'), Post::v('to'),
 127                            Post::v('title'), Post::v('message'));
 128             $page->trig("Mail envoyé");
 129         }
 130
 131         if ($action == 'insrel') {
 132             require_once 'marketing.inc.php';
 133             if (relance($uid)) {
 134                 $page->trig('relance faite');
 135             }
 136         }
 137
 138         if ($action == 'add' && Post::has('email') && Post::has('type')) {
 139             XDB::execute(
 140                 "INSERT INTO register_marketing
 141                          SET uid = {?}, sender = {?}, email = {?},
 142                              date = NOW(), type = {?}",
 143                 $uid, S::v('uid'), Post::v('email'), Post::v('type'));
 144         }
 145
 146         $res = XDB::iterator(
 147                 "SELECT  r.*, a.alias
 148                    FROM  register_marketing AS r
 149              INNER JOIN  aliases            AS a ON (r.sender=a.id AND a.type = 'a_vie')
 150                   WHERE  uid={?}
 151                ORDER BY  date", $uid);
 152         $page->assign('addr', $res);
 153
 154         $res = XDB::query("SELECT date, relance FROM register_pending
 155                                       WHERE uid = {?}", $uid);
 156         if (list($pending, $relance) = $res->fetchOneRow()) {
 157             $page->assign('pending', $pending);
 158             $page->assign('relance', $relance);
 159         }
 160     }
 161
 162     function handler_broken(&$page, $uid = null)
 163     {
 164         require_once('user.func.inc.php');
 165         $page->changeTpl('marketing/broken.tpl');
 166
 167         if (is_null($uid)) {
 168             return PL_NOT_FOUND;
 169         }
 170         $forlife = get_user_forlife($uid);
 171         if (!$forlife) {
 172             return PL_NOT_FOUND;
 173         } elseif ($forlife == S::v('forlife')) {
 174             pl_redirect('emails/redirect');
 175         }
 176
 177         $res = Xdb::query("SELECT  u.nom, u.prenom, u.promo, a.alias AS forlife
 178                              FROM  auth_user_md5 AS u
 179                        INNER JOIN  aliases       AS a ON a.id = u.user_id
 180                             WHERE  a.alias = {?}", $forlife);
 181         if (!$res->numRows()) {
 182             return PL_NOT_FOUND;
 183         }
 184         $user = $res->fetchOneAssoc();
 185         $page->assign('user', $user);
 186
 187         $email = trim(Post::v('mail'));
 188         if (Post::has('valide') && strlen($email) > 0) {
 189             require_once('diogenes/diogenes.hermes.inc.php');
 190             $mailer = new HermesMailer();
 191             $mailer->setFrom(S::v('bestalias') . '@polytechnique.org');
 192             $mailer->addTo('resetpass@polytechnique.org');
 193             $mailer->setSubject("Proposition d'adresse mail pour " . $user['forlife']);
 194
 195             $message = S::v('nom') . ' ' . S::v('prenom') . ' (X' . S::v('promo') . ') '
 196                      . 'propose l\'adresse suivante pour un camarade qui n\'a plus de '
 197                      . 'redirections actives :' . "\n\n"
 198                      . '* ' . $user['forlife'] . ' => ' . $email . "\n";
 199             $mailer->setTxtBody(wordwrap($message, 78));
 200             $mailer->send();
 201             $page->assign('sent', true);
 202         }
 203     }
 204
 205     function handler_promo(&$page, $promo = null)
 206     {
 207         $page->changeTpl('marketing/promo.tpl');
 208
 209         if (is_null($promo)) {
 210             $promo = S::v('promo');
 211         }
 212         $page->assign('promo', $promo);
 213
 214         $sql = "SELECT  u.user_id, u.nom, u.prenom, u.last_known_email, u.matricule_ax,
 215                         IF(MAX(m.last)>p.relance, MAX(m.last), p.relance) AS dern_rel, p.email
 216                   FROM  auth_user_md5      AS u
 217              LEFT JOIN  register_pending   AS p ON p.uid = u.user_id
 218              LEFT JOIN  register_marketing AS m ON m.uid = u.user_id
 219                  WHERE  u.promo = {?} AND u.deces = 0 AND u.perms='pending'
 220               GROUP BY  u.user_id
 221               ORDER BY  nom, prenom";
 222         $page->assign('nonins', XDB::iterator($sql, $promo));
 223     }
 224
 225     function handler_public(&$page, $uid = null)
 226     {
 227         $page->changeTpl('marketing/public.tpl');
 228
 229         if (is_null($uid)) {
 230             return PL_NOT_FOUND;
 231         }
 232
 233         $res = XDB::query("SELECT nom, prenom, promo FROM auth_user_md5
 234                                       WHERE user_id={?} AND perms='pending'", $uid);
 235
 236         if (list($nom, $prenom, $promo) = $res->fetchOneRow()) {
 237             $page->assign('prenom', $prenom);
 238             $page->assign('nom', $nom);
 239             $page->assign('promo', $promo);
 240
 241             if (Post::has('valide')) {
 242                 require_once('xorg.misc.inc.php');
 243
 244                 $email = trim(Post::v('mail'));
 245                 $res   = XDB::query('SELECT COUNT(*) FROM register_marketing
 246                                                 WHERE uid={?} AND email={?}', $uid, $email);
 247
 248                 if (!isvalid_email_redirection($email)) {
 249                     $page->trig("Email invalide !");
 250                 } elseif ($res->fetchOneCell()) {
 251                     $page->assign('already', true);
 252                 } else {
 253                     $page->assign('ok', true);
 254                     XDB::execute(
 255                             "INSERT INTO  register_marketing (uid,sender,email,date,last,nb,type,hash)
 256                                   VALUES  ({?}, {?}, {?}, NOW(), 0, 0, {?}, '')",
 257                             $uid, S::v('uid'), $email, Post::v('origine'));
 258                     require_once('validations.inc.php');
 259                     $req = new MarkReq(S::v('uid'), $uid, $email,
 260                                        Post::v('origine')=='user');
 261                     $req->submit();
 262                 }
 263             }
 264         }
 265     }
 266
 267     function handler_week(&$page, $sorting = 'per_promo')
 268     {
 269         $page->changeTpl('marketing/this_week.tpl');
 270
 271         $sort = $sorting == 'per_promo' ? 'promo' : 'date_ins';
 272
 273         $sql = "SELECT  a.alias AS forlife, u.date_ins, u.promo, u.nom, u.prenom
 274                   FROM  auth_user_md5  AS u
 275             INNER JOIN  aliases        AS a ON (u.user_id = a.id AND a.type='a_vie')
 276                  WHERE  u.date_ins > ".date("Ymd000000", strtotime ('1 week ago'))."
 277               ORDER BY  u.$sort DESC";
 278         $page->assign('ins', XDB::iterator($sql));
 279     }
 280
 281     function handler_volontaire(&$page, $promo = null)
 282     {
 283         $page->changeTpl('marketing/volontaire.tpl');
 284
 285         $res = XDB::query(
 286                 "SELECT
 287                DISTINCT  a.promo
 288                    FROM  register_marketing AS m
 289              INNER JOIN  auth_user_md5      AS a  ON a.user_id = m.uid
 290                ORDER BY  a.promo");
 291         $page->assign('promos', $res->fetchColumn());
 292
 293
 294         if (!is_null($promo)) {
 295             $sql = "SELECT  a.nom, a.prenom, a.user_id,
 296                             m.email, sa.alias AS forlife
 297                       FROM  register_marketing AS m
 298                 INNER JOIN  auth_user_md5      AS a  ON a.user_id = m.uid AND a.promo = {?}
 299                 INNER JOIN  aliases            AS sa ON (m.sender = sa.id AND sa.type='a_vie')
 300                   ORDER BY  a.nom";
 301             $page->assign('addr', XDB::iterator($sql, $promo));
 302         }
 303     }
 304
 305     function handler_relance(&$page)
 306     {
 307         $page->changeTpl('marketing/relance.tpl');
 308
 309         if (Post::has('relancer')) {
 310             require_once 'marketing.inc.php';
 311
 312             $res   = XDB::query("SELECT COUNT(*) FROM auth_user_md5 WHERE deces=0");
 313             $nbdix = $res->fetchOneCell();
 314
 315             $sent  = Array();
 316             foreach (array_keys($_POST['relance']) as $uid) {
 317                 if ($tmp = relance($uid, $nbdix)) {
 318                     $sent[] = $tmp.' a été relancé';
 319                 }
 320             }
 321             $page->assign('sent', $sent);
 322         }
 323
 324         $sql = "SELECT  r.date, r.relance, r.uid, u.promo, u.nom, u.prenom
 325                   FROM  register_pending AS r
 326             INNER JOIN  auth_user_md5    AS u ON r. uid = u.user_id
 327                  WHERE  hash!='INSCRIT'
 328               ORDER BY  date DESC";
 329         $page->assign('relance', XDB::iterator($sql));
 330     }
 331 }
 332
 333 ?>