[platal.git] / modules / marketing.php

<?php
/***************************************************************************
 *  Copyright (C) 2003-2009 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->setTitle('Marketing');

        // Some statistics
        $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(*) AS count
                             FROM  register_marketing
                         GROUP BY  sender = 0');
        $nbInsMarketNo = $res->fetchAllAssoc();
        $res = XDB::query('SELECT  COUNT(*)
                             FROM  register_marketing
                            WHERE  TO_DAYS(NOW()) - TO_DAYS(last) <= 7');
        $page->assign('nbInsMarketNoPerso', $nbInsMarketNo[0]['count']);
        $page->assign('nbInsMarketNoXorg', $nbInsMarketNo[1]['count']);
        $page->assign('nbInsMarketNoWeek', $res->fetchOneCell());

        $res = XDB::query('SELECT  COUNT(*) AS count
                             FROM  register_mstats
                         GROUP BY  sender = 0');
        $nbInsMarketOk = $res->fetchAllAssoc();
        $res = XDB::query('SELECT  COUNT(*)
                             FROM  register_mstats
                            WHERE  TO_DAYS(NOW()) - TO_DAYS(success) <= 7');
        $page->assign('nbInsMarketOkPerso', $nbInsMarketOk[0]['count']);
        $page->assign('nbInsMarketOkXorg', $nbInsMarketOk[1]['count']);
        $page->assign('nbInsMarketOkWeek', $res->fetchOneCell());
    }

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

        $user = User::getSilent($hruid);
        if (!$user) {
            return PL_NOT_FOUND;
        }

        // Retrieves marketed user details.
        $res = XDB::query(
            "SELECT  matricule
               FROM  auth_user_md5
              WHERE  user_id = {?} AND perms = 'pending'", $user->id());
        if (!($matricule = $res->fetchOneCell())) {
            $page->kill("Cet utilisateur est déjà inscrit au site.");
        }

        require_once('user.func.inc.php');
        $matricule = $res->fetchOneCell();
        $matricule_X = get_X_mat($matricule);

        $page->assign('full_name', $user->fullName());
        $page->assign('promo', $user->promo());
        $page->assign('matricule', $matricule);
        $page->assign('matricule_X',$matricule_X);

        // Applies in-parameter action to the user.
        if ($action == 'del') {
            S::assert_xsrf_token();
            Marketing::clear($user->id(), $value);
        }

        if ($action == 'rel') {
            $market = Marketing::get($user->id(), $value);
            if ($market == null) {
                $page->trigWarning("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') {
            S::assert_xsrf_token();

            $market = Marketing::get($user->id(), Post::v('to'));
            if (is_null($market)) {
                $market = new Marketing($user->id(), Post::v('to'), 'default', null, 'staff');
            }
            $market->send(Post::v('title'), Post::v('message'));
            $page->trigSuccess("Email envoyé");
        }

        if ($action == 'insrel') {
            S::assert_xsrf_token();
            if (Marketing::relance($user->id())) {
                $page->trigSuccess('relance faite');
            }
        }

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

        // Retrieves and display the existing marketing attempts.
        $res = XDB::iterator(
                "SELECT  r.*, a.alias
                   FROM  register_marketing AS r
              LEFT JOIN  aliases            AS a ON (r.sender=a.id AND a.type = 'a_vie')
                  WHERE  uid={?}
               ORDER BY  date", $user->id());
        $page->assign('addr', $res);

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

        $page->assign('path', 'marketing/private/' . $user->login());
    }

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

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

        $user = User::get($uid);
        if (!$user) {
            return PL_NOT_FOUND;
        } elseif ($user->login() == S::user()->login()) {
            pl_redirect('emails/redirect');
        }

        $res = XDB::query(
                "SELECT  u.deces = '0000-00-00' AS alive, e.last,
                         IF(e.email IS NOT NULL, e.email, IF(FIND_IN_SET('googleapps', u.mail_storage), 'googleapps', NULL)) AS email
                   FROM  auth_user_md5 AS u
              LEFT JOIN  emails        AS e ON (e.flags = 'active' AND e.uid = u.user_id)
                  WHERE  u.user_id = {?}
               ORDER BY  e.panne_level, e.last", $user->id());
        if (!$res->numRows()) {
            return PL_NOT_FOUND;
        }
        $user->addProperties($res->fetchOneAssoc());
        $page->assign('user', $user);

        $email = null;
        require_once 'emails.inc.php';
        if (Post::has('mail')) {
            $email = valide_email(Post::v('mail'));
        }
        if (Post::has('valide') && isvalid_email_redirection($email)) {
            S::assert_xsrf_token();

            // security stuff
            check_email($email, "Proposition d'une adresse surveillee pour " . $user->login() . " par " . S::user()->login());
            $res = XDB::query("SELECT  flags
                                 FROM  emails
                                WHERE  email = {?} AND uid = {?}", $email, $user->id());
            $state = $res->numRows() ? $res->fetchOneCell() : null;
            if ($state == 'panne') {
                $page->trigWarning("L'adresse que tu as fournie est l'adresse actuelle de {$user->fullName()} et est en panne.");
            } elseif ($state == 'active') {
                $page->trigWarning("L'adresse que tu as fournie est l'adresse actuelle de {$user->fullName()}");
            } elseif ($user->email && !trim(Post::v('comment'))) {
                $page->trigError("Il faut que tu ajoutes un commentaire à ta proposition pour justifier le "
                               . "besoin de changer la redirection de {$user->fullName()}.");
            } else {
                require_once 'validations.inc.php';
                $valid = new BrokenReq(S::user(), $user, $email, trim(Post::v('comment')));
                $valid->submit();
                $page->assign('sent', true);
            }
        } elseif ($email) {
            $page->trigError("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.hruid, u.nom, u.prenom, u.last_known_email,
                        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, $hruid = null)
    {
        $page->changeTpl('marketing/public.tpl');

        // Retrieves the user info, and checks the user is not yet registered.
        $user = User::getSilent($hruid);
        if (!$user) {
            return PL_NOT_FOUND;
        }

        $res = XDB::query(
            "SELECT  COUNT(*)
               FROM  auth_user_md5
              WHERE  user_id = {?} AND perms = 'pending'", $user->id());
        if (!$res->fetchOneCell()) {
            $page->kill("Cet utilisateur est déjà inscrit au site.");
        }

        // Displays the page, and handles the eventual user actions.
        $page->assign('full_name', $user->fullName());
        $page->assign('promo', $user->promo());

        if (Post::has('valide')) {
            S::assert_xsrf_token();
            $email = trim(Post::v('mail'));

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

                if (Marketing::get($user->id(), $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::user()->login());
                    $market = new Marketing($user->id(), $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	/***************************************************************************
8d84c630	3	* Copyright (C) 2003-2009 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');
10b057be	42
46f272fe	43	$page->setTitle('Marketing');
10b057be	44
fd33998d SJ	45	// Some statistics
	46	$res = XDB::query(
	47	"SELECT COUNT(*) AS vivants,
	48	COUNT(NULLIF(perms='admin' OR perms='user', 0)) AS inscrits,
	49	100COUNT(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");
10b057be	60	$stats = $res->fetchOneAssoc();
	61	$page->assign('stats', $stats);
	62
fd33998d SJ	63	$res = XDB::query('SELECT COUNT(*)
	64	FROM auth_user_md5
	65	WHERE date_ins > ' . date('Ymd000000', strtotime('1 week ago')));
10b057be	66	$page->assign('nbInsSem', $res->fetchOneCell());
10b057be	67
fd33998d SJ	68	$res = XDB::query("SELECT COUNT(*)
	69	FROM register_pending
	70	WHERE hash != 'INSCRIT'");
10b057be	71	$page->assign('nbInsEnCours', $res->fetchOneCell());
10b057be	72
fd33998d SJ	73	$res = XDB::query('SELECT COUNT(*) AS count
	74	FROM register_marketing
	75	GROUP BY sender = 0');
	76	$nbInsMarketNo = $res->fetchAllAssoc();
	77	$res = XDB::query('SELECT COUNT(*)
	78	FROM register_marketing
	79	WHERE TO_DAYS(NOW()) - TO_DAYS(last) <= 7');
	80	$page->assign('nbInsMarketNoPerso', $nbInsMarketNo[0]['count']);
	81	$page->assign('nbInsMarketNoXorg', $nbInsMarketNo[1]['count']);
	82	$page->assign('nbInsMarketNoWeek', $res->fetchOneCell());
10b057be	83
fd33998d SJ	84	$res = XDB::query('SELECT COUNT(*) AS count
	85	FROM register_mstats
	86	GROUP BY sender = 0');
	87	$nbInsMarketOk = $res->fetchAllAssoc();
	88	$res = XDB::query('SELECT COUNT(*)
	89	FROM register_mstats
	90	WHERE TO_DAYS(NOW()) - TO_DAYS(success) <= 7');
	91	$page->assign('nbInsMarketOkPerso', $nbInsMarketOk[0]['count']);
	92	$page->assign('nbInsMarketOkXorg', $nbInsMarketOk[1]['count']);
	93	$page->assign('nbInsMarketOkWeek', $res->fetchOneCell());
10b057be	94	}
10b057be	95
2455266e	96	function handler_private(&$page, $hruid = null,
926edcd1	97	$action = null, $value = null)
10b057be	98	{
115c90db	99	global $globals;
10b057be	100	$page->changeTpl('marketing/private.tpl');
10b057be	101
2455266e VZ	102	$user = User::getSilent($hruid);
2455266e VZ	103	if (!$user) {
10b057be	104	return PL_NOT_FOUND;
	105	}
	106
2455266e VZ	107	// Retrieves marketed user details.
	108	$res = XDB::query(
	109	"SELECT matricule
	110	FROM auth_user_md5
	111	WHERE user_id = {?} AND perms = 'pending'", $user->id());
	112	if (!($matricule = $res->fetchOneCell())) {
	113	$page->kill("Cet utilisateur est déjà inscrit au site.");
10b057be	114	}
10b057be	115
2455266e VZ	116	require_once('user.func.inc.php');
	117	$matricule = $res->fetchOneCell();
	118	$matricule_X = get_X_mat($matricule);
	119
	120	$page->assign('full_name', $user->fullName());
	121	$page->assign('promo', $user->promo());
	122	$page->assign('matricule', $matricule);
	123	$page->assign('matricule_X',$matricule_X);
	124
	125	// Applies in-parameter action to the user.
926edcd1	126	if ($action == 'del') {
ce8ec5a3	127	S::assert_xsrf_token();
2455266e	128	Marketing::clear($user->id(), $value);
10b057be	129	}
10b057be	130
926edcd1	131	if ($action == 'rel') {
2455266e	132	$market = Marketing::get($user->id(), $value);
e654517d	133	if ($market == null) {
a7d35093	134	$page->trigWarning("Aucun marketing n'a été effectué vers $value");
e654517d	135	} else {
	136	$to = $market->user['to'];
	137	$title = $market->getTitle();
	138	$text = $market->getText();
	139	$from = $market->sender_mail;
	140	$page->assign('rel_from_user', $from);
	141	$page->assign('rel_from_staff',
1d55fe45	142	'"Equipe Polytechnique.org" <register@' . $globals->mail->domain . '>');
e654517d	143	$page->assign('rel_to', $to);
	144	$page->assign('rel_title', $title);
	145	$page->assign('rel_text', $text);
	146	$page->assign('rel_email', $value);
	147	}
10b057be	148	}
10b057be	149
926edcd1	150	if ($action == 'relforce') {
ce8ec5a3 VZ	151	S::assert_xsrf_token();
ce8ec5a3 VZ	152
2455266e	153	$market = Marketing::get($user->id(), Post::v('to'));
e654517d	154	if (is_null($market)) {
2455266e	155	$market = new Marketing($user->id(), Post::v('to'), 'default', null, 'staff');
e654517d	156	}
e654517d	157	$market->send(Post::v('title'), Post::v('message'));
faefdbb7	158	$page->trigSuccess("Email envoyé");
10b057be	159	}
10b057be	160
926edcd1	161	if ($action == 'insrel') {
ce8ec5a3	162	S::assert_xsrf_token();
2455266e	163	if (Marketing::relance($user->id())) {
a7d35093	164	$page->trigSuccess('relance faite');
10b057be	165	}
	166	}
	167
926edcd1	168	if ($action == 'add' && Post::has('email') && Post::has('type')) {
2455266e	169	$market = new Marketing($user->id(), Post::v('email'), 'default', null, Post::v('type'), S::v('uid'));
e654517d	170	$market->add(false);
10b057be	171	}
10b057be	172
2455266e	173	// Retrieves and display the existing marketing attempts.
08cce2ff	174	$res = XDB::iterator(
10b057be	175	"SELECT r.*, a.alias
10b057be	176	FROM register_marketing AS r
fa61fc66	177	LEFT JOIN aliases AS a ON (r.sender=a.id AND a.type = 'a_vie')
10b057be	178	WHERE uid={?}
2455266e	179	ORDER BY date", $user->id());
10b057be	180	$page->assign('addr', $res);
10b057be	181
08cce2ff	182	$res = XDB::query("SELECT date, relance FROM register_pending
2455266e	183	WHERE uid = {?}", $user->id());
54f962cd	184	if (list($pending, $relance) = $res->fetchOneRow()) {
0025a0c5	185	$page->assign('pending', $pending);
0025a0c5	186	$page->assign('relance', $relance);
10b057be	187	}
2455266e VZ	188
2455266e VZ	189	$page->assign('path', 'marketing/private/' . $user->login());
10b057be	190	}
10b057be	191
d216d84a	192	function handler_broken(&$page, $uid = null)
	193	{
	194	$page->changeTpl('marketing/broken.tpl');
	195
	196	if (is_null($uid)) {
c441b5ed	197	return PL_NOT_FOUND;
c441b5ed	198	}
35211d07 VZ	199
	200	$user = User::get($uid);
	201	if (!$user) {
c441b5ed	202	return PL_NOT_FOUND;
35211d07	203	} elseif ($user->login() == S::user()->login()) {
c441b5ed	204	pl_redirect('emails/redirect');
d216d84a	205	}
d216d84a	206
5daf68f6 VZ	207	$res = XDB::query(
	208	"SELECT u.deces = '0000-00-00' AS alive, e.last,
	209	IF(e.email IS NOT NULL, e.email, IF(FIND_IN_SET('googleapps', u.mail_storage), 'googleapps', NULL)) AS email
	210	FROM auth_user_md5 AS u
	211	LEFT JOIN emails AS e ON (e.flags = 'active' AND e.uid = u.user_id)
	212	WHERE u.user_id = {?}
	213	ORDER BY e.panne_level, e.last", $user->id());
d216d84a	214	if (!$res->numRows()) {
c441b5ed	215	return PL_NOT_FOUND;
d216d84a	216	}
5daf68f6 VZ	217	$user->addProperties($res->fetchOneAssoc());
5daf68f6 VZ	218	$page->assign('user', $user);
d216d84a	219
0d693e2f	220	$email = null;
c6310567	221	require_once 'emails.inc.php';
0d693e2f	222	if (Post::has('mail')) {
0d693e2f	223	$email = valide_email(Post::v('mail'));
	224	}
	225	if (Post::has('valide') && isvalid_email_redirection($email)) {
ce8ec5a3 VZ	226	S::assert_xsrf_token();
ce8ec5a3 VZ	227
0d693e2f	228	// security stuff
35211d07 VZ	229	check_email($email, "Proposition d'une adresse surveillee pour " . $user->login() . " par " . S::user()->login());
	230	$res = XDB::query("SELECT flags
	231	FROM emails
	232	WHERE email = {?} AND uid = {?}", $email, $user->id());
0381e170	233	$state = $res->numRows() ? $res->fetchOneCell() : null;
0381e170	234	if ($state == 'panne') {
5daf68f6	235	$page->trigWarning("L'adresse que tu as fournie est l'adresse actuelle de {$user->fullName()} et est en panne.");
0381e170	236	} elseif ($state == 'active') {
5daf68f6 VZ	237	$page->trigWarning("L'adresse que tu as fournie est l'adresse actuelle de {$user->fullName()}");
5daf68f6 VZ	238	} elseif ($user->email && !trim(Post::v('comment'))) {
a7d35093	239	$page->trigError("Il faut que tu ajoutes un commentaire à ta proposition pour justifier le "
5daf68f6	240	. "besoin de changer la redirection de {$user->fullName()}.");
0d693e2f	241	} else {
0d693e2f	242	require_once 'validations.inc.php';
5daf68f6	243	$valid = new BrokenReq(S::user(), $user, $email, trim(Post::v('comment')));
0d693e2f	244	$valid->submit();
	245	$page->assign('sent', true);
	246	}
	247	} elseif ($email) {
a7d35093	248	$page->trigError("L'adresse proposée n'est pas une adresse acceptable pour une redirection");
d216d84a	249	}
	250	}
	251
10b057be	252	function handler_promo(&$page, $promo = null)
10b057be	253	{
10b057be	254	$page->changeTpl('marketing/promo.tpl');
10b057be	255
0025a0c5	256	if (is_null($promo)) {
cab08090	257	$promo = S::v('promo');
10b057be	258	}
	259	$page->assign('promo', $promo);
	260
a3ad3d44	261	$sql = "SELECT u.user_id, u.hruid, u.nom, u.prenom, u.last_known_email,
e654517d	262	IF(MAX(m.last) > p.relance, MAX(m.last), p.relance) AS dern_rel, p.email
10b057be	263	FROM auth_user_md5 AS u
	264	LEFT JOIN register_pending AS p ON p.uid = u.user_id
	265	LEFT JOIN register_marketing AS m ON m.uid = u.user_id
	266	WHERE u.promo = {?} AND u.deces = 0 AND u.perms='pending'
	267	GROUP BY u.user_id
	268	ORDER BY nom, prenom";
08cce2ff	269	$page->assign('nonins', XDB::iterator($sql, $promo));
10b057be	270	}
10b057be	271
2455266e	272	function handler_public(&$page, $hruid = null)
10b057be	273	{
10b057be	274	$page->changeTpl('marketing/public.tpl');
10b057be	275
2455266e VZ	276	// Retrieves the user info, and checks the user is not yet registered.
	277	$user = User::getSilent($hruid);
	278	if (!$user) {
10b057be	279	return PL_NOT_FOUND;
	280	}
	281
2455266e VZ	282	$res = XDB::query(
	283	"SELECT COUNT(*)
	284	FROM auth_user_md5
	285	WHERE user_id = {?} AND perms = 'pending'", $user->id());
	286	if (!$res->fetchOneCell()) {
	287	$page->kill("Cet utilisateur est déjà inscrit au site.");
	288	}
10b057be	289
2455266e VZ	290	// Displays the page, and handles the eventual user actions.
	291	$page->assign('full_name', $user->fullName());
	292	$page->assign('promo', $user->promo());
10b057be	293
2455266e VZ	294	if (Post::has('valide')) {
	295	S::assert_xsrf_token();
	296	$email = trim(Post::v('mail'));
	297
	298	require_once 'emails.inc.php';
	299	if (!isvalid_email_redirection($email)) {
	300	$page->trigError("Email invalide !");
	301	} else {
	302	// On cherche les marketings précédents sur cette adresse
	303	// email, en se restreignant au dernier mois
8ded5b5e	304
2455266e VZ	305	if (Marketing::get($user->id(), $email, true)) {
2455266e VZ	306	$page->assign('already', true);
10b057be	307	} else {
2455266e VZ	308	$page->assign('ok', true);
	309	check_email($email, "Une adresse surveillée est proposée au marketing par " . S::user()->login());
	310	$market = new Marketing($user->id(), $email, 'default', null, Post::v('origine'), S::v('uid'));
	311	$market->add();
10b057be	312	}
	313	}
	314	}
10b057be	315	}
10b057be	316
7c8a4874	317	function handler_week(&$page, $sorting = 'per_promo')
10b057be	318	{
10b057be	319	$page->changeTpl('marketing/this_week.tpl');
10b057be	320
7c8a4874	321	$sort = $sorting == 'per_promo' ? 'promo' : 'date_ins';
10b057be	322
	323	$sql = "SELECT a.alias AS forlife, u.date_ins, u.promo, u.nom, u.prenom
	324	FROM auth_user_md5 AS u
	325	INNER JOIN aliases AS a ON (u.user_id = a.id AND a.type='a_vie')
	326	WHERE u.date_ins > ".date("Ymd000000", strtotime ('1 week ago'))."
	327	ORDER BY u.$sort DESC";
08cce2ff	328	$page->assign('ins', XDB::iterator($sql));
10b057be	329	}
10b057be	330
7c8a4874	331	function handler_volontaire(&$page, $promo = null)
10b057be	332	{
10b057be	333	$page->changeTpl('marketing/volontaire.tpl');
10b057be	334
08cce2ff	335	$res = XDB::query(
10b057be	336	"SELECT
	337	DISTINCT a.promo
	338	FROM register_marketing AS m
	339	INNER JOIN auth_user_md5 AS a ON a.user_id = m.uid
	340	ORDER BY a.promo");
	341	$page->assign('promos', $res->fetchColumn());
	342
	343
7c8a4874	344	if (!is_null($promo)) {
10b057be	345	$sql = "SELECT a.nom, a.prenom, a.user_id,
	346	m.email, sa.alias AS forlife
	347	FROM register_marketing AS m
	348	INNER JOIN auth_user_md5 AS a ON a.user_id = m.uid AND a.promo = {?}
	349	INNER JOIN aliases AS sa ON (m.sender = sa.id AND sa.type='a_vie')
	350	ORDER BY a.nom";
08cce2ff	351	$page->assign('addr', XDB::iterator($sql, $promo));
10b057be	352	}
10b057be	353	}
	354
	355	function handler_relance(&$page)
	356	{
10b057be	357	$page->changeTpl('marketing/relance.tpl');
	358
	359	if (Post::has('relancer')) {
08cce2ff	360	$res = XDB::query("SELECT COUNT(*) FROM auth_user_md5 WHERE deces=0");
10b057be	361	$nbdix = $res->fetchOneCell();
	362
	363	$sent = Array();
	364	foreach (array_keys($_POST['relance']) as $uid) {
e654517d	365	if ($tmp = Marketing::relance($uid, $nbdix)) {
846c391e	366	$sent[] = $tmp . ' a été relancé.';
10b057be	367	}
	368	}
	369	$page->assign('sent', $sent);
	370	}
	371
	372	$sql = "SELECT r.date, r.relance, r.uid, u.promo, u.nom, u.prenom
	373	FROM register_pending AS r
	374	INNER JOIN auth_user_md5 AS u ON r. uid = u.user_id
	375	WHERE hash!='INSCRIT'
	376	ORDER BY date DESC";
08cce2ff	377	$page->assign('relance', XDB::iterator($sql));
10b057be	378	}
	379	}
	380
a7de4ef7	381	// vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
10b057be	382	?>