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