[platal.git] / modules / marketing.php

<?php
/***************************************************************************
 *  Copyright (C) 2003-2008 Polytechnique.org                              *
 *  http://opensource.polytechnique.org/                                   *
 *                                                                         *
 *  This program is free software; you can redistribute it and/or modify   *
 *  it under the terms of the GNU General Public License as published by   *
 *  the Free Software Foundation; either version 2 of the License, or      *
 *  (at your option) any later version.                                    *
 *                                                                         *
 *  This program is distributed in the hope that it will be useful,        *
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of         *
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the          *
 *  GNU General Public License for more details.                           *
 *                                                                         *
 *  You should have received a copy of the GNU General Public License      *
 *  along with this program; if not, write to the Free Software            *
 *  Foundation, Inc.,                                                      *
 *  59 Temple Place, Suite 330, Boston, MA  02111-1307  USA                *
 ***************************************************************************/

class MarketingModule extends PLModule
{
    function handlers()
    {
        return array(
            'marketing'            => $this->make_hook('marketing',  AUTH_MDP, 'admin'),
            'marketing/promo'      => $this->make_hook('promo',      AUTH_MDP, 'admin'),
            'marketing/relance'    => $this->make_hook('relance',    AUTH_MDP, 'admin'),
            'marketing/this_week'  => $this->make_hook('week',       AUTH_MDP, 'admin'),
            'marketing/volontaire' => $this->make_hook('volontaire', AUTH_MDP, 'admin'),

            'marketing/private'    => $this->make_hook('private',    AUTH_MDP, 'admin'),
            'marketing/public'     => $this->make_hook('public',     AUTH_COOKIE),
            'marketing/broken'     => $this->make_hook('broken',     AUTH_COOKIE),
        );
    }

    function handler_marketing(&$page)
    {
        $page->changeTpl('marketing/index.tpl');

        $page->assign('xorg_title','Polytechnique.org - Marketing');

        // Quelques statistiques

        $res   = XDB::query(
                  "SELECT COUNT(*) AS vivants,
                          COUNT(NULLIF(perms='admin' OR perms='user', 0)) AS inscrits,
                          100*COUNT(NULLIF(perms='admin' OR perms='user', 0))/COUNT(*) AS ins_rate,
                          COUNT(NULLIF(promo >= 1972, 0)) AS vivants72,
                          COUNT(NULLIF(promo >= 1972 AND (perms='admin' OR perms='user'), 0)) AS inscrits72,
                          100 * COUNT(NULLIF(promo >= 1972 AND (perms='admin' OR perms='user'), 0)) /
                              COUNT(NULLIF(promo >= 1972, 0)) AS ins72_rate,
                          COUNT(NULLIF(FIND_IN_SET('femme', flags), 0)) AS vivantes,
                          COUNT(NULLIF(FIND_IN_SET('femme', flags) AND (perms='admin' OR perms='user'), 0)) AS inscrites,
                          100 * COUNT(NULLIF(FIND_IN_SET('femme', flags) AND (perms='admin' OR perms='user'), 0)) /
                              COUNT(NULLIF(FIND_IN_SET('femme', flags), 0)) AS inse_rate
                     FROM auth_user_md5
                    WHERE deces = 0");
        $stats = $res->fetchOneAssoc();
        $page->assign('stats', $stats);

        $res   = XDB::query("SELECT count(*) FROM auth_user_md5 WHERE date_ins > ".
                                      date('Ymd000000', strtotime('1 week ago')));
        $page->assign('nbInsSem', $res->fetchOneCell());

        $res = XDB::query("SELECT count(*) FROM register_pending WHERE hash != 'INSCRIT'");
        $page->assign('nbInsEnCours', $res->fetchOneCell());

        $res = XDB::query("SELECT count(*) FROM register_marketing");
        $page->assign('nbInsMarket', $res->fetchOneCell());

        $res = XDB::query("SELECT count(*) FROM register_mstats
                                      WHERE TO_DAYS(NOW()) - TO_DAYS(success) <= 7");
        $page->assign('nbInsMarkOK', $res->fetchOneCell());
    }

    function handler_private(&$page, $uid = null,
                             $action = null, $value = null)
    {
        $page->changeTpl('marketing/private.tpl');

        if (is_null($uid)) {
            return PL_NOT_FOUND;
        }

        $page->assign('path', 'marketing/private/'.$uid);

        $res = XDB::query("SELECT  nom, prenom, promo, matricule
                             FROM  auth_user_md5
                            WHERE  user_id={?} AND perms='pending'", $uid);

        if (list($nom, $prenom, $promo, $matricule) = $res->fetchOneRow()) {
            require_once('user.func.inc.php');
            $matricule_X = get_X_mat($matricule);
            $page->assign('nom', $nom);
            $page->assign('prenom', $prenom);
            $page->assign('promo', $promo);
            $page->assign('matricule', $matricule);
            $page->assign('matricule_X',$matricule_X);
        } else {
            $page->kill('uid invalide');
        }

        if ($action == 'del') {
            Marketing::clear($uid, $value);
        }

        if ($action == 'rel') {
            $market = Marketing::get($uid, $value);
            if ($market == null) {
                $page->trig("Aucun marketing n'a été effectué vers $value");
            } else {
                $to    = $market->user['to'];
                $title = $market->getTitle();
                $text  = $market->getText();
                $from  = $market->sender_mail;
                $page->assign('rel_from_user', $from);
                $page->assign('rel_from_staff',
                              '"Equipe Polytechnique.org" <register@' . $globals->mail->domain . '>');
                $page->assign('rel_to', $to);
                $page->assign('rel_title', $title);
                $page->assign('rel_text', $text);
                $page->assign('rel_email', $value);
            }
        }

        if ($action == 'relforce') {
            $market = Marketing::get($uid, Post::v('to'));
            if (is_null($market)) {
                $market = new Marketing($uid, Post::v('to'), 'default', null, 'staff');
            }
            $market->send(Post::v('title'), Post::v('message'));
            $page->trig("Mail envoyé");
        }

        if ($action == 'insrel') {
            if (Marketing::relance($uid)) {
                $page->trig('relance faite');
            }
        }

        if ($action == 'add' && Post::has('email') && Post::has('type')) {
            $market = new Marketing($uid, Post::v('email'), 'default', null, Post::v('type'), S::v('uid'));
            $market->add(false);
        }

        $res = XDB::iterator(
                "SELECT  r.*, a.alias
                   FROM  register_marketing AS r
             INNER JOIN  aliases            AS a ON (r.sender=a.id AND a.type = 'a_vie')
                  WHERE  uid={?}
               ORDER BY  date", $uid);
        $page->assign('addr', $res);

        $res = XDB::query("SELECT date, relance FROM register_pending
                            WHERE uid = {?}", $uid);
        if (list($pending, $relance) = $res->fetchOneRow()) {
            $page->assign('pending', $pending);
            $page->assign('relance', $relance);
        }
    }

    function handler_broken(&$page, $uid = null)
    {
        require_once('user.func.inc.php');
        $page->changeTpl('marketing/broken.tpl');

        if (is_null($uid)) {
            return PL_NOT_FOUND;
        }
        $forlife = get_user_forlife($uid);
        if (!$forlife) {
            return PL_NOT_FOUND;
        } elseif ($forlife == S::v('forlife')) {
            pl_redirect('emails/redirect');
        }

        $res = Xdb::query("SELECT  u.nom, u.prenom, u.promo, FIND_IN_SET('femme', u.flags) AS sexe,
                                   u.deces = '0000-00-00' AS alive, a.alias AS forlife, b.alias AS bestalias,
                                   IF(e.email IS NOT NULL, e.email, IF(FIND_IN_SET('googleapps', u.mail_storage), 'googleapps', NULL)) AS email, e.last
                             FROM  auth_user_md5 AS u
                       INNER JOIN  aliases       AS a ON (a.id = u.user_id AND a.type = 'a_vie')
                       INNER JOIN  aliases       AS b ON (b.id = u.user_id AND FIND_IN_SET('bestalias', b.flags))
                        LEFT JOIN  emails        AS e ON (e.flags = 'active' AND e.uid = u.user_id)
                            WHERE  a.alias = {?}
                         ORDER BY  e.panne_level, e.last", $forlife);
        if (!$res->numRows()) {
            return PL_NOT_FOUND;
        }
        $user = $res->fetchOneAssoc();
        $page->assign('user', $user);

        $email = null;
        if (Post::has('mail')) {
            require_once 'emails.inc.php';
            $email = valide_email(Post::v('mail'));
        }
        if (Post::has('valide') && isvalid_email_redirection($email)) {
            // security stuff
            check_email($email, "Proposition d'une adresse surveillee pour " . $user['forlife'] . " par " . S::v('forlife'));
            $res = XDB::query("SELECT  state
                                 FROM  emails   AS e
                           INNER JOIN  aliases  AS a ON (a.id = e.uid)
                                WHERE  e.email = {?} AND a.alias = {?}", $email, $user['forlife']);
            $state = $res->numRows() ? $res->fetchOneCell() : null;
            if ($state == 'panne') {
                $page->trig("L'adresse que tu as fournie est l'adresse actuelle de {$user['prenom']} et est en panne.");
            } elseif ($state == 'active') {
                $page->trig("L'adresse que tu as fournie est l'adresse actuelle de {$user['prenom']}");
            } elseif ($user['email'] && !trim(Post::v('comment'))) {
                $page->trig("Il faut que tu ajoutes un commentaire à ta proposition pour justifier le "
                           ."besoin de changer la redirection de " . $user['prenom']);
            } else {
                require_once 'validations.inc.php';
                $valid = new BrokenReq(S::i('uid'), $user, $email, trim(Post::v('comment')));
                $valid->submit();
                $page->assign('sent', true);
            }
        } elseif ($email) {
            $page->trig("L'adresse proposée n'est pas une adresse acceptable pour une redirection");
        }
    }

    function handler_promo(&$page, $promo = null)
    {
        $page->changeTpl('marketing/promo.tpl');

        if (is_null($promo)) {
            $promo = S::v('promo');
        }
        $page->assign('promo', $promo);

        $sql = "SELECT  u.user_id, u.nom, u.prenom, u.last_known_email, u.matricule_ax,
                        IF(MAX(m.last) > p.relance, MAX(m.last), p.relance) AS dern_rel, p.email
                  FROM  auth_user_md5      AS u
             LEFT JOIN  register_pending   AS p ON p.uid = u.user_id
             LEFT JOIN  register_marketing AS m ON m.uid = u.user_id
                 WHERE  u.promo = {?} AND u.deces = 0 AND u.perms='pending'
              GROUP BY  u.user_id
              ORDER BY  nom, prenom";
        $page->assign('nonins', XDB::iterator($sql, $promo));
    }

    function handler_public(&$page, $uid = null)
    {
        $page->changeTpl('marketing/public.tpl');

        if (is_null($uid)) {
            return PL_NOT_FOUND;
        }

        $res = XDB::query("SELECT nom, prenom, promo FROM auth_user_md5
                                      WHERE user_id={?} AND perms='pending'", $uid);

        if (list($nom, $prenom, $promo) = $res->fetchOneRow()) {
            $page->assign('prenom', $prenom);
            $page->assign('nom', $nom);
            $page->assign('promo', $promo);

            if (Post::has('valide')) {
                require_once('xorg.misc.inc.php');
                $email = trim(Post::v('mail'));

                if (!isvalid_email_redirection($email)) {
                    $page->trig("Email invalide !");
                } else {
                    // On cherche les marketings précédents sur cette adresse
                    // email, en se restreignant au dernier mois

                    if (Marketing::get($uid, $email, true)) {
                        $page->assign('already', true);
                    } else {
                        $page->assign('ok', true);
                        check_email($email, "Une adresse surveillée est proposée au marketing par " . S::v('forlife'));
                        $market = new Marketing($uid, $email, 'default', null, Post::v('origine'), S::v('uid'));
                        $market->add();
                    }
                }
            }
        }
    }

    function handler_week(&$page, $sorting = 'per_promo')
    {
        $page->changeTpl('marketing/this_week.tpl');

        $sort = $sorting == 'per_promo' ? 'promo' : 'date_ins';

        $sql = "SELECT  a.alias AS forlife, u.date_ins, u.promo, u.nom, u.prenom
                  FROM  auth_user_md5  AS u
            INNER JOIN  aliases        AS a ON (u.user_id = a.id AND a.type='a_vie')
                 WHERE  u.date_ins > ".date("Ymd000000", strtotime ('1 week ago'))."
              ORDER BY  u.$sort DESC";
        $page->assign('ins', XDB::iterator($sql));
    }

    function handler_volontaire(&$page, $promo = null)
    {
        $page->changeTpl('marketing/volontaire.tpl');

        $res = XDB::query(
                "SELECT
               DISTINCT  a.promo
                   FROM  register_marketing AS m
             INNER JOIN  auth_user_md5      AS a  ON a.user_id = m.uid
               ORDER BY  a.promo");
        $page->assign('promos', $res->fetchColumn());


        if (!is_null($promo)) {
            $sql = "SELECT  a.nom, a.prenom, a.user_id,
                            m.email, sa.alias AS forlife
                      FROM  register_marketing AS m
                INNER JOIN  auth_user_md5      AS a  ON a.user_id = m.uid AND a.promo = {?}
                INNER JOIN  aliases            AS sa ON (m.sender = sa.id AND sa.type='a_vie')
                  ORDER BY  a.nom";
            $page->assign('addr', XDB::iterator($sql, $promo));
        }
    }

    function handler_relance(&$page)
    {
        $page->changeTpl('marketing/relance.tpl');

        if (Post::has('relancer')) {
            $res   = XDB::query("SELECT COUNT(*) FROM auth_user_md5 WHERE deces=0");
            $nbdix = $res->fetchOneCell();

            $sent  = Array();
            foreach (array_keys($_POST['relance']) as $uid) {
                if ($tmp = Marketing::relance($uid, $nbdix)) {
                    $sent[] = $tmp.' a été relancé';
                }
            }
            $page->assign('sent', $sent);
        }

        $sql = "SELECT  r.date, r.relance, r.uid, u.promo, u.nom, u.prenom
                  FROM  register_pending AS r
            INNER JOIN  auth_user_md5    AS u ON r. uid = u.user_id
                 WHERE  hash!='INSCRIT'
              ORDER BY  date DESC";
        $page->assign('relance', XDB::iterator($sql));
    }
}

// vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
?>
Commit	Line	Data
10b057be	1	<?php
10b057be	2	/***************************************************************************
179afa7f	3	* Copyright (C) 2003-2008 Polytechnique.org *
10b057be	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),
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	100COUNT(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());
10b057be	70
08cce2ff	71	$res = XDB::query("SELECT count(*) FROM register_marketing");
10b057be	72	$page->assign('nbInsMarket', $res->fetchOneCell());
10b057be	73
08cce2ff	74	$res = XDB::query("SELECT count(*) FROM register_mstats
10b057be	75	WHERE TO_DAYS(NOW()) - TO_DAYS(success) <= 7");
10b057be	76	$page->assign('nbInsMarkOK', $res->fetchOneCell());
10b057be	77	}
10b057be	78
926edcd1	79	function handler_private(&$page, $uid = null,
926edcd1	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);
926edcd1	89
e654517d	90	$res = XDB::query("SELECT nom, prenom, promo, matricule
	91	FROM auth_user_md5
	92	WHERE user_id={?} AND perms='pending'", $uid);
10b057be	93
10b057be	94	if (list($nom, $prenom, $promo, $matricule) = $res->fetchOneRow()) {
433336f3	95	require_once('user.func.inc.php');
10b057be	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') {
e654517d	107	Marketing::clear($uid, $value);
10b057be	108	}
10b057be	109
926edcd1	110	if ($action == 'rel') {
e654517d	111	$market = Marketing::get($uid, $value);
	112	if ($market == null) {
	113	$page->trig("Aucun marketing n'a été effectué vers $value");
	114	} else {
	115	$to = $market->user['to'];
	116	$title = $market->getTitle();
	117	$text = $market->getText();
	118	$from = $market->sender_mail;
	119	$page->assign('rel_from_user', $from);
	120	$page->assign('rel_from_staff',
1d55fe45	121	'"Equipe Polytechnique.org" <register@' . $globals->mail->domain . '>');
e654517d	122	$page->assign('rel_to', $to);
	123	$page->assign('rel_title', $title);
	124	$page->assign('rel_text', $text);
	125	$page->assign('rel_email', $value);
	126	}
10b057be	127	}
10b057be	128
926edcd1	129	if ($action == 'relforce') {
e654517d	130	$market = Marketing::get($uid, Post::v('to'));
	131	if (is_null($market)) {
	132	$market = new Marketing($uid, Post::v('to'), 'default', null, 'staff');
	133	}
	134	$market->send(Post::v('title'), Post::v('message'));
a7de4ef7	135	$page->trig("Mail envoyé");
10b057be	136	}
10b057be	137
926edcd1	138	if ($action == 'insrel') {
e654517d	139	if (Marketing::relance($uid)) {
10b057be	140	$page->trig('relance faite');
	141	}
	142	}
	143
926edcd1	144	if ($action == 'add' && Post::has('email') && Post::has('type')) {
e654517d	145	$market = new Marketing($uid, Post::v('email'), 'default', null, Post::v('type'), S::v('uid'));
e654517d	146	$market->add(false);
10b057be	147	}
10b057be	148
08cce2ff	149	$res = XDB::iterator(
10b057be	150	"SELECT r.*, a.alias
	151	FROM register_marketing AS r
	152	INNER JOIN aliases AS a ON (r.sender=a.id AND a.type = 'a_vie')
	153	WHERE uid={?}
	154	ORDER BY date", $uid);
	155	$page->assign('addr', $res);
	156
08cce2ff	157	$res = XDB::query("SELECT date, relance FROM register_pending
e654517d	158	WHERE uid = {?}", $uid);
54f962cd	159	if (list($pending, $relance) = $res->fetchOneRow()) {
0025a0c5	160	$page->assign('pending', $pending);
0025a0c5	161	$page->assign('relance', $relance);
10b057be	162	}
10b057be	163	}
10b057be	164
d216d84a	165	function handler_broken(&$page, $uid = null)
d216d84a	166	{
c441b5ed	167	require_once('user.func.inc.php');
d216d84a	168	$page->changeTpl('marketing/broken.tpl');
	169
	170	if (is_null($uid)) {
c441b5ed	171	return PL_NOT_FOUND;
	172	}
	173	$forlife = get_user_forlife($uid);
	174	if (!$forlife) {
	175	return PL_NOT_FOUND;
	176	} elseif ($forlife == S::v('forlife')) {
	177	pl_redirect('emails/redirect');
d216d84a	178	}
d216d84a	179
0d693e2f	180	$res = Xdb::query("SELECT u.nom, u.prenom, u.promo, FIND_IN_SET('femme', u.flags) AS sexe,
ea2569eb	181	u.deces = '0000-00-00' AS alive, a.alias AS forlife, b.alias AS bestalias,
0e5ec860	182	IF(e.email IS NOT NULL, e.email, IF(FIND_IN_SET('googleapps', u.mail_storage), 'googleapps', NULL)) AS email, e.last
d216d84a	183	FROM auth_user_md5 AS u
0d693e2f	184	INNER JOIN aliases AS a ON (a.id = u.user_id AND a.type = 'a_vie')
	185	INNER JOIN aliases AS b ON (b.id = u.user_id AND FIND_IN_SET('bestalias', b.flags))
	186	LEFT JOIN emails AS e ON (e.flags = 'active' AND e.uid = u.user_id)
	187	WHERE a.alias = {?}
	188	ORDER BY e.panne_level, e.last", $forlife);
d216d84a	189	if (!$res->numRows()) {
c441b5ed	190	return PL_NOT_FOUND;
d216d84a	191	}
	192	$user = $res->fetchOneAssoc();
	193	$page->assign('user', $user);
	194
0d693e2f	195	$email = null;
	196	if (Post::has('mail')) {
	197	require_once 'emails.inc.php';
	198	$email = valide_email(Post::v('mail'));
	199	}
	200	if (Post::has('valide') && isvalid_email_redirection($email)) {
	201	// security stuff
	202	check_email($email, "Proposition d'une adresse surveillee pour " . $user['forlife'] . " par " . S::v('forlife'));
0381e170	203	$res = XDB::query("SELECT state
	204	FROM emails AS e
	205	INNER JOIN aliases AS a ON (a.id = e.uid)
	206	WHERE e.email = {?} AND a.alias = {?}", $email, $user['forlife']);
	207	$state = $res->numRows() ? $res->fetchOneCell() : null;
	208	if ($state == 'panne') {
	209	$page->trig("L'adresse que tu as fournie est l'adresse actuelle de {$user['prenom']} et est en panne.");
	210	} elseif ($state == 'active') {
	211	$page->trig("L'adresse que tu as fournie est l'adresse actuelle de {$user['prenom']}");
	212	} elseif ($user['email'] && !trim(Post::v('comment'))) {
a7de4ef7	213	$page->trig("Il faut que tu ajoutes un commentaire à ta proposition pour justifier le "
0d693e2f	214	."besoin de changer la redirection de " . $user['prenom']);
	215	} else {
	216	require_once 'validations.inc.php';
	217	$valid = new BrokenReq(S::i('uid'), $user, $email, trim(Post::v('comment')));
	218	$valid->submit();
	219	$page->assign('sent', true);
	220	}
	221	} elseif ($email) {
a7de4ef7	222	$page->trig("L'adresse proposée n'est pas une adresse acceptable pour une redirection");
d216d84a	223	}
	224	}
	225
10b057be	226	function handler_promo(&$page, $promo = null)
10b057be	227	{
10b057be	228	$page->changeTpl('marketing/promo.tpl');
10b057be	229
0025a0c5	230	if (is_null($promo)) {
cab08090	231	$promo = S::v('promo');
10b057be	232	}
	233	$page->assign('promo', $promo);
	234
	235	$sql = "SELECT u.user_id, u.nom, u.prenom, u.last_known_email, u.matricule_ax,
e654517d	236	IF(MAX(m.last) > p.relance, MAX(m.last), p.relance) AS dern_rel, p.email
10b057be	237	FROM auth_user_md5 AS u
	238	LEFT JOIN register_pending AS p ON p.uid = u.user_id
	239	LEFT JOIN register_marketing AS m ON m.uid = u.user_id
	240	WHERE u.promo = {?} AND u.deces = 0 AND u.perms='pending'
	241	GROUP BY u.user_id
	242	ORDER BY nom, prenom";
08cce2ff	243	$page->assign('nonins', XDB::iterator($sql, $promo));
10b057be	244	}
	245
	246	function handler_public(&$page, $uid = null)
	247	{
10b057be	248	$page->changeTpl('marketing/public.tpl');
	249
	250	if (is_null($uid)) {
	251	return PL_NOT_FOUND;
	252	}
	253
08cce2ff	254	$res = XDB::query("SELECT nom, prenom, promo FROM auth_user_md5
10b057be	255	WHERE user_id={?} AND perms='pending'", $uid);
	256
	257	if (list($nom, $prenom, $promo) = $res->fetchOneRow()) {
	258	$page->assign('prenom', $prenom);
	259	$page->assign('nom', $nom);
	260	$page->assign('promo', $promo);
	261
7c8a4874	262	if (Post::has('valide')) {
10b057be	263	require_once('xorg.misc.inc.php');
5e2307dc	264	$email = trim(Post::v('mail'));
8ded5b5e	265
10b057be	266	if (!isvalid_email_redirection($email)) {
10b057be	267	$page->trig("Email invalide !");
10b057be	268	} else {
8ded5b5e	269	// On cherche les marketings précédents sur cette adresse
8ded5b5e	270	// email, en se restreignant au dernier mois
eaf30d86	271
8ded5b5e	272	if (Marketing::get($uid, $email, true)) {
	273	$page->assign('already', true);
	274	} else {
	275	$page->assign('ok', true);
	276	check_email($email, "Une adresse surveillée est proposée au marketing par " . S::v('forlife'));
	277	$market = new Marketing($uid, $email, 'default', null, Post::v('origine'), S::v('uid'));
	278	$market->add();
	279	}
10b057be	280	}
	281	}
	282	}
10b057be	283	}
10b057be	284
7c8a4874	285	function handler_week(&$page, $sorting = 'per_promo')
10b057be	286	{
10b057be	287	$page->changeTpl('marketing/this_week.tpl');
10b057be	288
7c8a4874	289	$sort = $sorting == 'per_promo' ? 'promo' : 'date_ins';
10b057be	290
	291	$sql = "SELECT a.alias AS forlife, u.date_ins, u.promo, u.nom, u.prenom
	292	FROM auth_user_md5 AS u
	293	INNER JOIN aliases AS a ON (u.user_id = a.id AND a.type='a_vie')
	294	WHERE u.date_ins > ".date("Ymd000000", strtotime ('1 week ago'))."
	295	ORDER BY u.$sort DESC";
08cce2ff	296	$page->assign('ins', XDB::iterator($sql));
10b057be	297	}
10b057be	298
7c8a4874	299	function handler_volontaire(&$page, $promo = null)
10b057be	300	{
10b057be	301	$page->changeTpl('marketing/volontaire.tpl');
10b057be	302
08cce2ff	303	$res = XDB::query(
10b057be	304	"SELECT
	305	DISTINCT a.promo
	306	FROM register_marketing AS m
	307	INNER JOIN auth_user_md5 AS a ON a.user_id = m.uid
	308	ORDER BY a.promo");
	309	$page->assign('promos', $res->fetchColumn());
	310
	311
7c8a4874	312	if (!is_null($promo)) {
10b057be	313	$sql = "SELECT a.nom, a.prenom, a.user_id,
	314	m.email, sa.alias AS forlife
	315	FROM register_marketing AS m
	316	INNER JOIN auth_user_md5 AS a ON a.user_id = m.uid AND a.promo = {?}
	317	INNER JOIN aliases AS sa ON (m.sender = sa.id AND sa.type='a_vie')
	318	ORDER BY a.nom";
08cce2ff	319	$page->assign('addr', XDB::iterator($sql, $promo));
10b057be	320	}
10b057be	321	}
	322
	323	function handler_relance(&$page)
	324	{
10b057be	325	$page->changeTpl('marketing/relance.tpl');
	326
	327	if (Post::has('relancer')) {
08cce2ff	328	$res = XDB::query("SELECT COUNT(*) FROM auth_user_md5 WHERE deces=0");
10b057be	329	$nbdix = $res->fetchOneCell();
	330
	331	$sent = Array();
	332	foreach (array_keys($_POST['relance']) as $uid) {
e654517d	333	if ($tmp = Marketing::relance($uid, $nbdix)) {
a7de4ef7	334	$sent[] = $tmp.' a été relancé';
10b057be	335	}
	336	}
	337	$page->assign('sent', $sent);
	338	}
	339
	340	$sql = "SELECT r.date, r.relance, r.uid, u.promo, u.nom, u.prenom
	341	FROM register_pending AS r
	342	INNER JOIN auth_user_md5 AS u ON r. uid = u.user_id
	343	WHERE hash!='INSCRIT'
	344	ORDER BY date DESC";
08cce2ff	345	$page->assign('relance', XDB::iterator($sql));
10b057be	346	}
	347	}
	348
a7de4ef7	349	// vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
10b057be	350	?>