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         );
  36     }
  37
  38     function handler_marketing(&$page)
  39     {
  40         global $globals;
  41
  42         $page->changeTpl('marketing/index.tpl');
  43
  44         $page->assign('xorg_title','Polytechnique.org - Marketing');
  45
  46         // Quelques statistiques
  47
  48         $res   = $globals->xdb->query(
  49                   "SELECT COUNT(*) AS vivants,
  50                           COUNT(NULLIF(perms='admin' OR perms='user', 0)) AS inscrits,
  51                           100*COUNT(NULLIF(perms='admin' OR perms='user', 0))/COUNT(*) AS ins_rate,
  52                           COUNT(NULLIF(promo >= 1972, 0)) AS vivants72,
  53                           COUNT(NULLIF(promo >= 1972 AND (perms='admin' OR perms='user'), 0)) AS inscrits72,
  54                           100 * COUNT(NULLIF(promo >= 1972 AND (perms='admin' OR perms='user'), 0)) /
  55                               COUNT(NULLIF(promo >= 1972, 0)) AS ins72_rate,
  56                           COUNT(NULLIF(FIND_IN_SET('femme', flags), 0)) AS vivantes,
  57                           COUNT(NULLIF(FIND_IN_SET('femme', flags) AND (perms='admin' OR perms='user'), 0)) AS inscrites,
  58                           100 * COUNT(NULLIF(FIND_IN_SET('femme', flags) AND (perms='admin' OR perms='user'), 0)) /
  59                               COUNT(NULLIF(FIND_IN_SET('femme', flags), 0)) AS inse_rate
  60                      FROM auth_user_md5
  61                     WHERE deces = 0");
  62         $stats = $res->fetchOneAssoc();
  63         $page->assign('stats', $stats);
  64
  65         $res   = $globals->xdb->query("SELECT count(*) FROM auth_user_md5 WHERE date_ins > ".
  66                                       date('Ymd000000', strtotime('1 week ago')));
  67         $page->assign('nbInsSem', $res->fetchOneCell());
  68
  69         $res = $globals->xdb->query("SELECT count(*) FROM register_pending WHERE hash != 'INSCRIT'");
  70         $page->assign('nbInsEnCours', $res->fetchOneCell());
  71
  72         $res = $globals->xdb->query("SELECT count(*) FROM register_marketing");
  73         $page->assign('nbInsMarket', $res->fetchOneCell());
  74
  75         $res = $globals->xdb->query("SELECT count(*) FROM register_mstats
  76                                       WHERE TO_DAYS(NOW()) - TO_DAYS(success) <= 7");
  77         $page->assign('nbInsMarkOK', $res->fetchOneCell());
  78
  79         return PL_OK;
  80     }
  81
  82     function handler_private(&$page, $uid = null,
  83                              $action = null, $value = null)
  84     {
  85         global $globals;
  86
  87         $page->changeTpl('marketing/private.tpl');
  88
  89         if (is_null($uid)) {
  90             return PL_NOT_FOUND;
  91         }
  92
  93         $page->assign('path', 'marketing/private/'.$uid);
  94
  95         $res = $globals->xdb->query("SELECT nom, prenom, promo, matricule
  96                                        FROM auth_user_md5
  97                                       WHERE user_id={?} AND perms='pending'", $uid);
  98
  99         if (list($nom, $prenom, $promo, $matricule) = $res->fetchOneRow()) {
 100             require_once('register.inc.php');
 101             $matricule_X = get_X_mat($matricule);
 102             $page->assign('nom', $nom);
 103             $page->assign('prenom', $prenom);
 104             $page->assign('promo', $promo);
 105             $page->assign('matricule', $matricule);
 106             $page->assign('matricule_X',$matricule_X);
 107         } else {
 108             $page->kill('uid invalide');
 109         }
 110
 111         if ($action == 'del') {
 112             $globals->xdb->execute('DELETE FROM register_marketing WHERE uid={?} AND email={?}',
 113                                    $uid, $value);
 114         }
 115
 116         if ($action == 'rel') {
 117             require_once('marketing.inc.php');
 118             list($to, $title, $text) = mark_text_mail($uid, $value);
 119             $from = mark_from_mail($uid, $value);
 120             $page->assign('rel_from_user', $from);
 121             $page->assign('rel_from_staff',
 122                           "\"Equipe Polytechnique.org\" <register@polytechnique.org>");
 123             $page->assign('rel_to', $to);
 124             $page->assign('rel_title', $title);
 125             $page->assign('rel_text', $text);
 126             $page->assign('rel_email', $value);
 127         }
 128
 129         if ($action == 'relforce') {
 130             require_once('marketing.inc.php');
 131             mark_send_mail($uid, $value, Env::get('from'),
 132                            Env::get('to'), Env::get('title'),
 133                            Env::get('message'));
 134             $page->trig("Mail envoyé");
 135         }
 136
 137         if ($action == 'insrel') {
 138             require_once('marketing.inc.php');
 139             if (relance($uid)) {
 140                 $page->trig('relance faite');
 141             }
 142         }
 143
 144         if ($action == 'add' && Post::has('email') && Post::has('type')) {
 145             $globals->xdb->execute(
 146                 "INSERT INTO register_marketing
 147                          SET uid = {?}, sender = {?}, email = {?},
 148                              date = NOW(), type = {?}",
 149                 $uid, Session::get('uid'), Post::get('email'), Post::get('type'));
 150         }
 151
 152         $res = $globals->xdb->iterator(
 153                 "SELECT  r.*, a.alias
 154                    FROM  register_marketing AS r
 155              INNER JOIN  aliases            AS a ON (r.sender=a.id AND a.type = 'a_vie')
 156                   WHERE  uid={?}
 157                ORDER BY  date", $uid);
 158         $page->assign('addr', $res);
 159
 160         $res = $globals->xdb->query("SELECT date, relance FROM register_pending
 161                                       WHERE uid = {?}", $uid);
 162         if (list($pending, $relance) = $res->fetchOneCell()) {
 163             $page->assign('pending', $pending);
 164             $page->assign('relance', $relance);
 165         }
 166
 167         return PL_OK;
 168     }
 169
 170     function handler_promo(&$page, $promo = null)
 171     {
 172         global $globals;
 173
 174         $page->changeTpl('marketing/promo.tpl');
 175
 176         if (is_null($promo)) {
 177             $promo = Session::getInt('promo');
 178         }
 179         $page->assign('promo', $promo);
 180
 181         $sql = "SELECT  u.user_id, u.nom, u.prenom, u.last_known_email, u.matricule_ax,
 182                         IF(MAX(m.last)>p.relance, MAX(m.last), p.relance) AS dern_rel, p.email
 183                   FROM  auth_user_md5      AS u
 184              LEFT JOIN  register_pending   AS p ON p.uid = u.user_id
 185              LEFT JOIN  register_marketing AS m ON m.uid = u.user_id
 186                  WHERE  u.promo = {?} AND u.deces = 0 AND u.perms='pending'
 187               GROUP BY  u.user_id
 188               ORDER BY  nom, prenom";
 189         $page->assign('nonins', $globals->xdb->iterator($sql, $promo));
 190
 191         return PL_OK;
 192     }
 193
 194     function handler_public(&$page, $uid = null)
 195     {
 196         global $globals;
 197
 198         $page->changeTpl('marketing/public.tpl');
 199
 200         if (is_null($uid)) {
 201             return PL_NOT_FOUND;
 202         }
 203
 204         $res = $globals->xdb->query("SELECT nom, prenom, promo FROM auth_user_md5
 205                                       WHERE user_id={?} AND perms='pending'", $uid);
 206
 207         if (list($nom, $prenom, $promo) = $res->fetchOneRow()) {
 208             $page->assign('prenom', $prenom);
 209             $page->assign('nom', $nom);
 210             $page->assign('promo', $promo);
 211
 212             if (Env::has('valide')) {
 213                 require_once('xorg.misc.inc.php');
 214
 215                 $email = trim(Env::get('mail'));
 216                 $res   = $globals->xdb->query('SELECT COUNT(*) FROM register_marketing
 217                                                 WHERE uid={?} AND email={?}', $uid, $email);
 218
 219                 if (!isvalid_email_redirection($email)) {
 220                     $page->trig("Email invalide !");
 221                 } elseif ($res->fetchOneCell()) {
 222                     $page->assign('already', true);
 223                 } else {
 224                     $page->assign('ok', true);
 225                     $globals->xdb->execute(
 226                             "INSERT INTO  register_marketing (uid,sender,email,date,last,nb,type,hash)
 227                                   VALUES  ({?}, {?}, {?}, NOW(), 0, 0, {?}, '')",
 228                             $uid, Session::getInt('uid'), $email, Env::get('origine'));
 229                     require_once('validations.inc.php');
 230                     $req = new MarkReq(Session::getInt('uid'), $uid, $email,
 231                                        Env::get('origine')=='user');
 232                     $req->submit();
 233                 }
 234             }
 235         }
 236
 237         return PL_OK;
 238     }
 239
 240     function handler_week(&$page)
 241     {
 242         global $globals;
 243
 244         $page->changeTpl('marketing/this_week.tpl');
 245
 246         $sort = Get::get('sort') == 'promo' ? 'promo' : 'date_ins';
 247
 248         $sql = "SELECT  a.alias AS forlife, u.date_ins, u.promo, u.nom, u.prenom
 249                   FROM  auth_user_md5  AS u
 250             INNER JOIN  aliases        AS a ON (u.user_id = a.id AND a.type='a_vie')
 251                  WHERE  u.date_ins > ".date("Ymd000000", strtotime ('1 week ago'))."
 252               ORDER BY  u.$sort DESC";
 253         $page->assign('ins', $globals->xdb->iterator($sql));
 254
 255         return PL_OK;
 256     }
 257
 258     function handler_volontaire(&$page)
 259     {
 260         global $globals;
 261
 262         $page->changeTpl('marketing/volontaire.tpl');
 263
 264         $res = $globals->xdb->query(
 265                 "SELECT
 266                DISTINCT  a.promo
 267                    FROM  register_marketing AS m
 268              INNER JOIN  auth_user_md5      AS a  ON a.user_id = m.uid
 269                ORDER BY  a.promo");
 270         $page->assign('promos', $res->fetchColumn());
 271
 272
 273         if (Env::has('promo')) {
 274             $sql = "SELECT  a.nom, a.prenom, a.user_id,
 275                             m.email, sa.alias AS forlife
 276                       FROM  register_marketing AS m
 277                 INNER JOIN  auth_user_md5      AS a  ON a.user_id = m.uid AND a.promo = {?}
 278                 INNER JOIN  aliases            AS sa ON (m.sender = sa.id AND sa.type='a_vie')
 279                   ORDER BY  a.nom";
 280             $page->assign('addr', $globals->xdb->iterator($sql, Env::get('promo')));
 281         }
 282
 283         return PL_OK;
 284     }
 285
 286     function handler_relance(&$page)
 287     {
 288         global $globals;
 289
 290         $page->changeTpl('marketing/relance.tpl');
 291
 292         if (Post::has('relancer')) {
 293             $res   = $globals->xdb->query("SELECT COUNT(*) FROM auth_user_md5 WHERE deces=0");
 294             $nbdix = $res->fetchOneCell();
 295
 296             $sent  = Array();
 297             foreach (array_keys($_POST['relance']) as $uid) {
 298                 if ($tmp = relance($uid, $nbdix)) {
 299                     $sent[] = $tmp.' a été relancé';
 300                 }
 301             }
 302             $page->assign('sent', $sent);
 303         }
 304
 305         $sql = "SELECT  r.date, r.relance, r.uid, u.promo, u.nom, u.prenom
 306                   FROM  register_pending AS r
 307             INNER JOIN  auth_user_md5    AS u ON r. uid = u.user_id
 308                  WHERE  hash!='INSCRIT'
 309               ORDER BY  date DESC";
 310         $page->assign('relance', $globals->xdb->iterator($sql));
 311
 312         return PL_OK;
 313     }
 314 }
 315
 316 ?>