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&domain=".$row['domain'])."&bounces=".$row['num']); 223 $ls->addColumn( 224 $row['domain'], 225 s('Bounces'), 226 sprintf( number_format($row['num']),'') 227 ); 228} 229$status .= $ls->display();