From b0653ad8f359685deb843d14f3103288997ec0c3 Mon Sep 17 00:00:00 2001
From: =?utf8?q?St=C3=A9phane=20Jacob?= <sj@m4x.org>
Date: Wed, 21 Sep 2011 12:42:06 +0200
Subject: [PATCH] Displays number of subscribers without any valid redirection.
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

Signed-off-by: Stéphane Jacob <sj@m4x.org>
---
 ChangeLog                      |  5 +++++
 include/newsletter.inc.php     | 14 ++++++++++++++
 templates/newsletter/index.tpl |  2 +-
 3 files changed, 20 insertions(+), 1 deletion(-)

diff --git a/ChangeLog b/ChangeLog
index e4e6b15..f782f94 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,6 +1,11 @@
 ================================================================================
 VERSION 1.1.4                                                         XX XX XXXX
 
+New:
+
+    * Newsletter:
+        - Displays number of subscribers without any valid redirection     -JAC
+
 Bug/Wish:
 
     * Carnet:
diff --git a/include/newsletter.inc.php b/include/newsletter.inc.php
index 2f6ccca..0b521b4 100644
--- a/include/newsletter.inc.php
+++ b/include/newsletter.inc.php
@@ -407,6 +407,20 @@ class NewsLetter
                                    WHERE  nlid = {?}', $this->id);
     }
 
+    /** Get the count of subscribers with non valid redirection.
+     */
+    public function lostSubscriberCount()
+    {
+        return XDB::fetchOneCell('SELECT  COUNT(DISTINCT(n.uid))
+                                    FROM  newsletter_ins         AS n
+                              INNER JOIN  accounts               AS a ON (n.uid = a.uid)
+                              INNER JOIN  account_types          AS t ON (t.type = a.type)
+                               LEFT JOIN  email_redirect_account AS r ON (r.uid = a.uid AND r.flags = \'active\' AND r.broken_level < 3
+                                                                          AND r.type != \'imap\' AND r.type != \'homonym\')
+                                   WHERE  n.nlid = {?} AND r.redirect IS NULL AND a.state = \'active\' AND FIND_IN_SET(\'mail\', t.perms)',
+                                 $this->id);
+    }
+
     /** Get the number of subscribers to the NL whose last received mailing was $last.
      * @p $last ID of the issue for which subscribers should be counted.
      * @return Number of subscribers
diff --git a/templates/newsletter/index.tpl b/templates/newsletter/index.tpl
index 6bcb7d5..79da294 100644
--- a/templates/newsletter/index.tpl
+++ b/templates/newsletter/index.tpl
@@ -74,7 +74,7 @@ Tu n'es actuellement pas inscrit à la {$nl->name}.
 </table>
 
 {if $nl->mayEdit()}
-<p>Il y a actuellement {$nl->subscriberCount()} inscrits aux envois.</p>
+<p>Il y a actuellement {$nl->subscriberCount()} inscrits aux envois, parmi lesquels {$nl->lostSubscriberCount()} n'ont aucune redirection active.</p>
 {/if}
 
 
-- 
2.1.4