Table editor fills the new entry form with the default values of the fields
[platal.git] / modules / marketing.php
CommitLineData
10b057be 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
22class 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),
d216d84a 35 'marketing/broken' => $this->make_hook('broken', AUTH_COOKIE),
10b057be 36 );
37 }
38
39 function handler_marketing(&$page)
40 {
10b057be 41 $page->changeTpl('marketing/index.tpl');
42
43 $page->assign('xorg_title','Polytechnique.org - Marketing');
44
45 // Quelques statistiques
46
08cce2ff 47 $res = XDB::query(
10b057be 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
08cce2ff 64 $res = XDB::query("SELECT count(*) FROM auth_user_md5 WHERE date_ins > ".
10b057be 65 date('Ymd000000', strtotime('1 week ago')));
66 $page->assign('nbInsSem', $res->fetchOneCell());
67
08cce2ff 68 $res = XDB::query("SELECT count(*) FROM register_pending WHERE hash != 'INSCRIT'");
10b057be 69 $page->assign('nbInsEnCours', $res->fetchOneCell());
70
08cce2ff 71 $res = XDB::query("SELECT count(*) FROM register_marketing");
10b057be 72 $page->assign('nbInsMarket', $res->fetchOneCell());
73
08cce2ff 74 $res = XDB::query("SELECT count(*) FROM register_mstats
10b057be 75 WHERE TO_DAYS(NOW()) - TO_DAYS(success) <= 7");
76 $page->assign('nbInsMarkOK', $res->fetchOneCell());
10b057be 77 }
78
926edcd1 79 function handler_private(&$page, $uid = null,
80 $action = null, $value = null)
10b057be 81 {
10b057be 82 $page->changeTpl('marketing/private.tpl');
83
84 if (is_null($uid)) {
85 return PL_NOT_FOUND;
86 }
87
926edcd1 88 $page->assign('path', 'marketing/private/'.$uid);
89
08cce2ff 90 $res = XDB::query("SELECT nom, prenom, promo, matricule
10b057be 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);
0025a0c5 97 $page->assign('nom', $nom);
98 $page->assign('prenom', $prenom);
99 $page->assign('promo', $promo);
100 $page->assign('matricule', $matricule);
10b057be 101 $page->assign('matricule_X',$matricule_X);
102 } else {
103 $page->kill('uid invalide');
104 }
105
926edcd1 106 if ($action == 'del') {
08cce2ff 107 XDB::execute('DELETE FROM register_marketing WHERE uid={?} AND email={?}',
926edcd1 108 $uid, $value);
10b057be 109 }
110
926edcd1 111 if ($action == 'rel') {
10b057be 112 require_once('marketing.inc.php');
926edcd1 113 list($to, $title, $text) = mark_text_mail($uid, $value);
114 $from = mark_from_mail($uid, $value);
10b057be 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);
926edcd1 121 $page->assign('rel_email', $value);
10b057be 122 }
123
926edcd1 124 if ($action == 'relforce') {
10b057be 125 require_once('marketing.inc.php');
5e2307dc 126 mark_send_mail($uid, $value, Post::v('from'), Post::v('to'),
127 Post::v('title'), Post::v('message'));
089a5801 128 $page->trig("Mail envoyé");
10b057be 129 }
130
926edcd1 131 if ($action == 'insrel') {
fcf2e33e 132 require_once 'marketing.inc.php';
10b057be 133 if (relance($uid)) {
134 $page->trig('relance faite');
135 }
136 }
137
926edcd1 138 if ($action == 'add' && Post::has('email') && Post::has('type')) {
08cce2ff 139 XDB::execute(
10b057be 140 "INSERT INTO register_marketing
141 SET uid = {?}, sender = {?}, email = {?},
142 date = NOW(), type = {?}",
5e2307dc 143 $uid, S::v('uid'), Post::v('email'), Post::v('type'));
10b057be 144 }
145
08cce2ff 146 $res = XDB::iterator(
10b057be 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
08cce2ff 154 $res = XDB::query("SELECT date, relance FROM register_pending
10b057be 155 WHERE uid = {?}", $uid);
54f962cd 156 if (list($pending, $relance) = $res->fetchOneRow()) {
0025a0c5 157 $page->assign('pending', $pending);
158 $page->assign('relance', $relance);
10b057be 159 }
10b057be 160 }
161
d216d84a 162 function handler_broken(&$page, $uid = null)
163 {
c441b5ed 164 require_once('user.func.inc.php');
d216d84a 165 $page->changeTpl('marketing/broken.tpl');
166
167 if (is_null($uid)) {
c441b5ed 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');
d216d84a 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
c441b5ed 180 WHERE a.alias = {?}", $forlife);
d216d84a 181 if (!$res->numRows()) {
c441b5ed 182 return PL_NOT_FOUND;
d216d84a 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
10b057be 205 function handler_promo(&$page, $promo = null)
206 {
10b057be 207 $page->changeTpl('marketing/promo.tpl');
208
0025a0c5 209 if (is_null($promo)) {
cab08090 210 $promo = S::v('promo');
10b057be 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";
08cce2ff 222 $page->assign('nonins', XDB::iterator($sql, $promo));
10b057be 223 }
224
225 function handler_public(&$page, $uid = null)
226 {
10b057be 227 $page->changeTpl('marketing/public.tpl');
228
229 if (is_null($uid)) {
230 return PL_NOT_FOUND;
231 }
232
08cce2ff 233 $res = XDB::query("SELECT nom, prenom, promo FROM auth_user_md5
10b057be 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
7c8a4874 241 if (Post::has('valide')) {
10b057be 242 require_once('xorg.misc.inc.php');
243
5e2307dc 244 $email = trim(Post::v('mail'));
08cce2ff 245 $res = XDB::query('SELECT COUNT(*) FROM register_marketing
10b057be 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);
08cce2ff 254 XDB::execute(
10b057be 255 "INSERT INTO register_marketing (uid,sender,email,date,last,nb,type,hash)
256 VALUES ({?}, {?}, {?}, NOW(), 0, 0, {?}, '')",
5e2307dc 257 $uid, S::v('uid'), $email, Post::v('origine'));
10b057be 258 require_once('validations.inc.php');
cab08090 259 $req = new MarkReq(S::v('uid'), $uid, $email,
5e2307dc 260 Post::v('origine')=='user');
10b057be 261 $req->submit();
262 }
263 }
264 }
10b057be 265 }
266
7c8a4874 267 function handler_week(&$page, $sorting = 'per_promo')
10b057be 268 {
10b057be 269 $page->changeTpl('marketing/this_week.tpl');
270
7c8a4874 271 $sort = $sorting == 'per_promo' ? 'promo' : 'date_ins';
10b057be 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";
08cce2ff 278 $page->assign('ins', XDB::iterator($sql));
10b057be 279 }
280
7c8a4874 281 function handler_volontaire(&$page, $promo = null)
10b057be 282 {
10b057be 283 $page->changeTpl('marketing/volontaire.tpl');
284
08cce2ff 285 $res = XDB::query(
10b057be 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
7c8a4874 294 if (!is_null($promo)) {
10b057be 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";
08cce2ff 301 $page->assign('addr', XDB::iterator($sql, $promo));
10b057be 302 }
10b057be 303 }
304
305 function handler_relance(&$page)
306 {
10b057be 307 $page->changeTpl('marketing/relance.tpl');
308
309 if (Post::has('relancer')) {
fcf2e33e 310 require_once 'marketing.inc.php';
311
08cce2ff 312 $res = XDB::query("SELECT COUNT(*) FROM auth_user_md5 WHERE deces=0");
10b057be 313 $nbdix = $res->fetchOneCell();
314
315 $sent = Array();
316 foreach (array_keys($_POST['relance']) as $uid) {
317 if ($tmp = relance($uid, $nbdix)) {
089a5801 318 $sent[] = $tmp.' a été relancé';
10b057be 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";
08cce2ff 329 $page->assign('relance', XDB::iterator($sql));
10b057be 330 }
331}
332
333?>