1<?php
2
3verifyCsrfGetToken();
4if (!empty($_SESSION['LoadDelay'])) {
5    sleep($_SESSION['LoadDelay']);
6}
7// domain stats
8$status = '';
9
10// Fetch all subscribers' data
11$totalreq = Sql_Fetch_Row_Query(sprintf(
12    'select
13    count(*)
14from
15    %s', $GLOBALS['tables']['user']));
16
17$total = $totalreq[0];
18
19// Check if download flag is set (for downloading CSV)
20$download = !empty($_GET['dl']);
21
22// if download requested
23if ($download) {
24    ob_end_clean();
25    header('Content-type: text/csv');
26    header('Content-disposition:  attachment; filename="phpList Domain statistics.csv"');
27    ob_start();
28}
29
30// Count the number of confirmed/unconfirmed/blacklisted users per domain and return them in descending order
31$req = Sql_Query(sprintf(
32    'select
33    lcase( substring_index( email,"@",-1 ) ) as domain,
34    sum(if(confirmed = 1 && blacklisted = 0, 1, 0)) as domain_confirmed,
35    sum(if(confirmed = 0 && blacklisted = 0, 1, 0)) as domain_unconfirmed,
36    sum(if(blacklisted = 1, 1, 0)) AS domain_blacklisted,
37    count(email) as domain_total
38from
39    %s
40group by
41    domain
42having
43    domain_total > 5
44order by
45    domain_total desc
46limit
47    50', $GLOBALS['tables']['user']));
48
49$ls = new WebblerListing(s('Top 50 domains with more than 5 subscribers'));
50$ls->setElementHeading('Domain');
51$columnFormat = '<strong>%s</strong> (%s%%)';
52
53if (Sql_Num_Rows($req) > 0) {
54    while ($row = Sql_Fetch_Array($req)) {
55        $ls->addElement($row['domain']);
56
57        // Calculate the number of confirmed subs on this domain as a percentage of all subscribers
58        $perc = round($row['domain_confirmed'] / $total * 100);
59        $ls->addColumn(
60            $row['domain'],
61            s('confirmed'),
62            sprintf($columnFormat, number_format($row['domain_confirmed']), $perc)
63        );
64
65        // Calculate the number of unconfirmed subs on this domain as a percentage of all subscribers
66        $percentUnconfirmed = round($row['domain_unconfirmed'] / $total * 100);
67        $ls->addColumn(
68            $row['domain'],
69            s('unconfirmed'),
70            sprintf($columnFormat, number_format($row['domain_unconfirmed']), $percentUnconfirmed)
71        );
72
73        // Calculate the number of blacklisted subs on this domain as a percentage of all subscribers
74        $percentBlacklisted = round($row['domain_blacklisted'] / $total * 100);
75        $ls->addColumn(
76            $row['domain'],
77            s('blacklisted'),
78            sprintf($columnFormat, number_format($row['domain_blacklisted']), $percentBlacklisted)
79        );
80
81        // Calculate the number subs on this domain as a percentage of all subscribers
82        $percentTotal = round($row['domain_total'] / $total * 100);
83        $ls->addColumn(
84            $row['domain'],
85            s('total'),
86            sprintf($columnFormat, number_format($row['domain_total']), $percentTotal)
87        );
88    }
89
90    // Print download button
91    $status .= '<div class="actions pull-right">'.PageLinkButton('page=pageaction&action=domainstats&dl=true',
92            s('Download as CSV file')).'</div><div class="clearfix"></div>';
93} else {
94    // Print missing data notice
95    $status .= '<h3>'.s('Once you have some more subscribers, this page will list statistics on the domains of your subscribers. It will list domains that have 5 or more subscribers.').'</h3>';
96}
97
98// If download was requested, send CSV
99if ($download) {
100    ob_end_clean();
101    echo $ls->tabDelimited();
102    exit;
103}
104
105$status .= $ls->display();
106
107$status .= '<br /><br />';
108
109// Fetch top 25 domains ordered by total unconfirmed descending
110$query = Sql_Query(sprintf(
111    'select
112    lcase(substring_index(email,"@",-1)) as domain
113    , count(email) as domain_total
114    , sum(if(confirmed = 1 && blacklisted = 0, 1, 0)) as domain_confirmed
115    , sum(if(confirmed = 0 && blacklisted = 0, 1, 0)) as domain_unconfirmed
116    , sum(if(blacklisted = 1, 1, 0)) AS domain_blacklisted
117from
118    %s
119group by
120    domain
121having
122    domain_unconfirmed > 0
123order by
124    domain_unconfirmed DESC
125    , domain_total DESC
126limit
127    25', $GLOBALS['tables']['user']));
128
129// Only print table if results are found
130if (Sql_Num_Rows($query) > 0) {
131    $ls = new WebblerListing(s('Domains with most unconfirmed subscribers'));
132    $ls->setElementHeading('Domain');
133
134    // Loop through each domain result
135    while ($row = Sql_Fetch_Assoc($query)) {
136        $ls->addElement($row['domain']);
137
138        // Calculate the number of confirmed subs on this domain as a percentage of all subscribers
139        $percentConfirmed = round($row['domain_confirmed'] / $total * 100);
140        $ls->addColumn(
141            $row['domain'],
142            s('confirmed'),
143            sprintf($columnFormat, number_format($row['domain_confirmed']), $percentConfirmed)
144        );
145
146        // Calculate the number of unconfirmed subs on this domain as a percentage of all subscribers
147        $percentUnconfirmed = round($row['domain_unconfirmed'] / $total * 100);
148        $ls->addColumn(
149            $row['domain'],
150            s('unconfirmed'),
151            sprintf($columnFormat, number_format($row['domain_unconfirmed']), $percentUnconfirmed)
152        );
153        // Calculate the number of blacklisted subs on this domain as a percentage of all subscribers
154        $percentBlacklisted = round($row['domain_blacklisted'] / $total * 100);
155        $ls->addColumn(
156            $row['domain'],
157            s('blacklisted'),
158            sprintf($columnFormat, number_format($row['domain_blacklisted']), $percentBlacklisted)
159        );
160
161        // Calculate the number subs on this domain as a percentage of all subscribers
162        $percentTotal = round($row['domain_total'] / $total * 100);
163        // Show the total subscribers using this domain
164        $ls->addColumn(
165            $row['domain'],
166            s('total'),
167            sprintf($columnFormat, number_format($row['domain_total']), $percentTotal)
168        );
169    }
170
171    // Print table
172    $status .= $ls->display();
173    $status .= '<br /><br />';
174}
175
176$req = Sql_Query(sprintf(
177    'select
178    lcase(substring_index(email,"@",1)) as preat
179    , count(email) as num
180from
181    %s
182where
183    confirmed
184group by
185    preat
186order by
187    num desc, preat asc
188limit
189    25', $GLOBALS['tables']['user']));
190
191$ls = new WebblerListing(s('Top 25 local-parts of email addresses'));
192$ls->setElementHeading('Local-part');
193
194while ($row = Sql_Fetch_Array($req)) {
195    if ($row['num'] > 0) {
196        $ls->addElement($row['preat']);
197        $percentTotal = round($row['num'] / $total * 100);
198        $ls->addColumn(
199            $row['preat'],
200            s('total'),
201            sprintf($columnFormat, number_format($row['num']), $percentTotal)
202        );
203    }
204}
205$status .= $ls->display();
206$status .= '<br /><br />';
207
208$ls = new WebblerListing(s('Top 25 domains with the highest number of bounces'));
209$ls->setElementHeading('Domain');
210$req = Sql_Query(sprintf('
211SELECT COUNT(lcase(substring_index(u.email, "@", -1))) num,
212       lcase(substring_index(u.email, "@", -1)) domain
213FROM %s AS u
214RIGHT JOIN %s AS b ON u.id = b.user
215GROUP BY domain
216ORDER BY num DESC
217LIMIT 25;
218', $GLOBALS['tables']['user'],
219    $GLOBALS['tables']['user_message_bounce']));
220
221while ($row = Sql_Fetch_Array($req)) {
222    $ls->addElement($row['domain'],  PageURL2("domainbounces&amp;domain=".$row['domain'])."&amp;bounces=".$row['num']);
223    $ls->addColumn(
224        $row['domain'],
225        s('Bounces'),
226        sprintf( number_format($row['num']),'')
227    );
228}
229$status .= $ls->display();