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, Post::get('from'), Post::get('to'),
 132                            Post::get('title'), Post::get('message'));
 133             $page->trig("Mail envoyé");
 134         }
 135
 136         if ($action == 'insrel') {
 137             require_once 'marketing.inc.php';
 138             if (relance($uid)) {
 139                 $page->trig('relance faite');
 140             }
 141         }
 142
 143         if ($action == 'add' && Post::has('email') && Post::has('type')) {
 144             $globals->xdb->execute(
 145                 "INSERT INTO register_marketing
 146                          SET uid = {?}, sender = {?}, email = {?},
 147                              date = NOW(), type = {?}",
 148                 $uid, Session::get('uid'), Post::get('email'), Post::get('type'));
 149         }
 150
 151         $res = $globals->xdb->iterator(
 152                 "SELECT  r.*, a.alias
 153                    FROM  register_marketing AS r
 154              INNER JOIN  aliases            AS a ON (r.sender=a.id AND a.type = 'a_vie')
 155                   WHERE  uid={?}
 156                ORDER BY  date", $uid);
 157         $page->assign('addr', $res);
 158
 159         $res = $globals->xdb->query("SELECT date, relance FROM register_pending
 160                                       WHERE uid = {?}", $uid);
 161         if (list($pending, $relance) = $res->fetchOneCell()) {
 162             $page->assign('pending', $pending);
 163             $page->assign('relance', $relance);
 164         }
 165
 166         return PL_OK;
 167     }
 168
 169     function handler_promo(&$page, $promo = null)
 170     {
 171         global $globals;
 172
 173         $page->changeTpl('marketing/promo.tpl');
 174
 175         if (is_null($promo)) {
 176             $promo = Session::getInt('promo');
 177         }
 178         $page->assign('promo', $promo);
 179
 180         $sql = "SELECT  u.user_id, u.nom, u.prenom, u.last_known_email, u.matricule_ax,
 181                         IF(MAX(m.last)>p.relance, MAX(m.last), p.relance) AS dern_rel, p.email
 182                   FROM  auth_user_md5      AS u
 183              LEFT JOIN  register_pending   AS p ON p.uid = u.user_id
 184              LEFT JOIN  register_marketing AS m ON m.uid = u.user_id
 185                  WHERE  u.promo = {?} AND u.deces = 0 AND u.perms='pending'
 186               GROUP BY  u.user_id
 187               ORDER BY  nom, prenom";
 188         $page->assign('nonins', $globals->xdb->iterator($sql, $promo));
 189
 190         return PL_OK;
 191     }
 192
 193     function handler_public(&$page, $uid = null)
 194     {
 195         global $globals;
 196
 197         $page->changeTpl('marketing/public.tpl');
 198
 199         if (is_null($uid)) {
 200             return PL_NOT_FOUND;
 201         }
 202
 203         $res = $globals->xdb->query("SELECT nom, prenom, promo FROM auth_user_md5
 204                                       WHERE user_id={?} AND perms='pending'", $uid);
 205
 206         if (list($nom, $prenom, $promo) = $res->fetchOneRow()) {
 207             $page->assign('prenom', $prenom);
 208             $page->assign('nom', $nom);
 209             $page->assign('promo', $promo);
 210
 211             if (Post::has('valide')) {
 212                 require_once('xorg.misc.inc.php');
 213
 214                 $email = trim(Post::get('mail'));
 215                 $res   = $globals->xdb->query('SELECT COUNT(*) FROM register_marketing
 216                                                 WHERE uid={?} AND email={?}', $uid, $email);
 217
 218                 if (!isvalid_email_redirection($email)) {
 219                     $page->trig("Email invalide !");
 220                 } elseif ($res->fetchOneCell()) {
 221                     $page->assign('already', true);
 222                 } else {
 223                     $page->assign('ok', true);
 224                     $globals->xdb->execute(
 225                             "INSERT INTO  register_marketing (uid,sender,email,date,last,nb,type,hash)
 226                                   VALUES  ({?}, {?}, {?}, NOW(), 0, 0, {?}, '')",
 227                             $uid, Session::getInt('uid'), $email, Post::get('origine'));
 228                     require_once('validations.inc.php');
 229                     $req = new MarkReq(Session::getInt('uid'), $uid, $email,
 230                                        Post::get('origine')=='user');
 231                     $req->submit();
 232                 }
 233             }
 234         }
 235
 236         return PL_OK;
 237     }
 238
 239     function handler_week(&$page, $sorting = 'per_promo')
 240     {
 241         global $globals;
 242
 243         $page->changeTpl('marketing/this_week.tpl');
 244
 245         $sort = $sorting == 'per_promo' ? 'promo' : 'date_ins';
 246
 247         $sql = "SELECT  a.alias AS forlife, u.date_ins, u.promo, u.nom, u.prenom
 248                   FROM  auth_user_md5  AS u
 249             INNER JOIN  aliases        AS a ON (u.user_id = a.id AND a.type='a_vie')
 250                  WHERE  u.date_ins > ".date("Ymd000000", strtotime ('1 week ago'))."
 251               ORDER BY  u.$sort DESC";
 252         $page->assign('ins', $globals->xdb->iterator($sql));
 253
 254         return PL_OK;
 255     }
 256
 257     function handler_volontaire(&$page, $promo = null)
 258     {
 259         global $globals;
 260
 261         $page->changeTpl('marketing/volontaire.tpl');
 262
 263         $res = $globals->xdb->query(
 264                 "SELECT
 265                DISTINCT  a.promo
 266                    FROM  register_marketing AS m
 267              INNER JOIN  auth_user_md5      AS a  ON a.user_id = m.uid
 268                ORDER BY  a.promo");
 269         $page->assign('promos', $res->fetchColumn());
 270
 271
 272         if (!is_null($promo)) {
 273             $sql = "SELECT  a.nom, a.prenom, a.user_id,
 274                             m.email, sa.alias AS forlife
 275                       FROM  register_marketing AS m
 276                 INNER JOIN  auth_user_md5      AS a  ON a.user_id = m.uid AND a.promo = {?}
 277                 INNER JOIN  aliases            AS sa ON (m.sender = sa.id AND sa.type='a_vie')
 278                   ORDER BY  a.nom";
 279             $page->assign('addr', $globals->xdb->iterator($sql, $promo));
 280         }
 281
 282         return PL_OK;
 283     }
 284
 285     function handler_relance(&$page)
 286     {
 287         global $globals;
 288
 289         $page->changeTpl('marketing/relance.tpl');
 290
 291         if (Post::has('relancer')) {
 292             require_once 'marketing.inc.php';
 293
 294             $res   = $globals->xdb->query("SELECT COUNT(*) FROM auth_user_md5 WHERE deces=0");
 295             $nbdix = $res->fetchOneCell();
 296
 297             $sent  = Array();
 298             foreach (array_keys($_POST['relance']) as $uid) {
 299                 if ($tmp = relance($uid, $nbdix)) {
 300                     $sent[] = $tmp.' a été relancé';
 301                 }
 302             }
 303             $page->assign('sent', $sent);
 304         }
 305
 306         $sql = "SELECT  r.date, r.relance, r.uid, u.promo, u.nom, u.prenom
 307                   FROM  register_pending AS r
 308             INNER JOIN  auth_user_md5    AS u ON r. uid = u.user_id
 309                  WHERE  hash!='INSCRIT'
 310               ORDER BY  date DESC";
 311         $page->assign('relance', $globals->xdb->iterator($sql));
 312
 313         return PL_OK;
 314     }
 315 }
 316
 317 ?>