Merge branch 'platal-0.10.1'

[platal.git] / modules / marketing.php

diff --git a/modules/marketing.php b/modules/marketing.php
index f6ac4fb..cb280ae 100644 (file)
--- a/modules/marketing.php
+++ b/modules/marketing.php
@@ -1,6 +1,6 @@
 <?php
 /***************************************************************************
- *  Copyright (C) 2003-2008 Polytechnique.org                              *
+ *  Copyright (C) 2003-2009 Polytechnique.org                              *
  *  http://opensource.polytechnique.org/                                   *
  *                                                                         *
  *  This program is free software; you can redistribute it and/or modify   *
@@ -42,38 +42,55 @@ class MarketingModule extends PLModule
 
         $page->setTitle('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");
+        // 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')));
+        $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'");
+        $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());
+        $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,
@@ -241,7 +258,7 @@ class MarketingModule extends PLModule
         }
         $page->assign('promo', $promo);
 
-        $sql = "SELECT  u.user_id, u.nom, u.prenom, u.last_known_email, u.matricule_ax,
+        $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
@@ -346,7 +363,7 @@ class MarketingModule extends PLModule
             $sent  = Array();
             foreach (array_keys($_POST['relance']) as $uid) {
                 if ($tmp = Marketing::relance($uid, $nbdix)) {
-                    $sent[] = $tmp.' a été relancé';
+                    $sent[] = $tmp . ' a été relancé.';
                 }
             }
             $page->assign('sent', $sent);