1<?php 2// print '<p>'.s('Select Message to view').'</p>'; 3verifyCsrfGetToken(); 4 5if (isset($_GET['id'])) { 6 $id = sprintf('%d', $_GET['id']); 7} else { 8 $id = 0; 9} 10 11$start = 0; 12$limit = ' limit 10'; 13if (isset($_GET['start'])) { 14 $start = sprintf('%d', $_GET['start']); 15 $limit = ' limit '.$start.', 10'; 16} 17 18$status = ''; 19$access = accessLevel('statsoverview'); 20$ownership = ''; 21$subselect = ''; 22$paging = ''; 23 24//print "Access Level: $access"; 25switch ($access) { 26 case 'owner': 27 $ownership = sprintf(' and owner = %d ', $_SESSION['logindetails']['id']); 28 if ($id) { 29 $allow = Sql_Fetch_Row_query(sprintf('select owner from %s where id = %d %s', $GLOBALS['tables']['message'], 30 $id, $ownership)); 31 if ($allow[0] != $_SESSION['logindetails']['id']) { 32 $status .= s('You do not have access to this page'); 33 34 return; 35 } 36 } 37 $addcomparison = 1; 38 break; 39 case 'all': 40 break; 41 case 'none': 42 default: 43 $ownership = ' and msg.id = 0'; 44 $status .= s('You do not have access to this page'); 45 46 return; 47 break; 48} 49 50$download = !empty($_GET['dl']); 51if ($download) { 52 ob_end_clean(); 53// header("Content-type: text/plain"); 54 header('Content-type: text/csv'); 55 if (!$id) { 56 header('Content-disposition: attachment; filename="phpList Campaign statistics.csv"'); 57 } 58 ob_start(); 59} 60 61if (empty($start)) { 62 $status .= '<div class="actions pull-right">'.PageLinkButton('pageaction&action=statsoverview&dl=true', 63 s('Download as CSV file')).'</div><div class="clearfix"></div>'; 64} 65if (!empty($_SESSION['LoadDelay'])) { 66 sleep($_SESSION['LoadDelay']); 67} 68$timerange = ' and msg.entered > date_sub(now(),interval 12 month)'; 69$timerange = ''; 70 71$query = sprintf('select msg.owner,msg.id as messageid, subject, sent, bouncecount as bounced 72 from %s msg where msg.status = "sent" %s %s %s 73 group by msg.id order by msg.sent desc', 74 $GLOBALS['tables']['message'], $subselect, $timerange, $ownership); 75$req = Sql_Query($query); 76$total = Sql_Num_Rows($req); 77if ($total > 10) { 78 //print Paging(PageUrl2('statsoverview'),$start,$total,10); 79 $paging = simplePaging('statsoverview', $start, $total, 10); 80 // Increase the record limit for exported files 81 if ($download) { 82 $limit = ' limit 1000'; 83 } 84 85 $query .= $limit; 86 $req = Sql_Query($query); 87} 88 89if (!Sql_Affected_Rows()) { 90 $status .= '<p class="information">'.s('There are currently no campaigns to view').'</p>'; 91} 92 93$ls = new WebblerListing(''); 94$ls->usePanel($paging); 95while ($row = Sql_Fetch_Array($req)) { 96 // $element = '<!--'.$row['messageid'].'-->'.shortenTextDisplay($row['subject'],30); 97 $messagedata = loadMessageData($row['messageid']); 98 99 if ($messagedata['subject'] != $messagedata['campaigntitle']) { 100 $element = '<!--'.$row['messageid'].'-->' 101 .stripslashes(shortenTextDisplay($messagedata['campaigntitle'], 30)).'<br/><strong>'.stripslashes(shortenTextDisplay($messagedata['subject'], 30)).'</strong>'; 102 } else { 103 $element = '<!--'.$row['messageid'].'-->' 104 .stripslashes(shortenTextDisplay($messagedata['subject'], 30)); 105 } 106 107 $fwded = Sql_Fetch_Row_Query(sprintf('select count(id) from %s where message = %d', 108 $GLOBALS['tables']['user_message_forward'], $row['messageid'])); 109 $views = Sql_Fetch_Row_Query(sprintf('select count(viewed) from %s where messageid = %d 110 and status = "sent"', 111 $GLOBALS['tables']['usermessage'], $row['messageid'])); 112 $totls = Sql_Fetch_Row_Query(sprintf('select count(status) from %s where messageid = %d 113 and status = "sent"', 114 $GLOBALS['tables']['usermessage'], $row['messageid'])); 115 116 $totalclicked = Sql_Fetch_Row_Query(sprintf('select count(distinct userid) from %s where messageid = %d', 117 $GLOBALS['tables']['linktrack_uml_click'], $row['messageid'])); 118 119 $totalclicks = Sql_Fetch_Row_Query(sprintf('select count( userid) from %s where messageid = %d', 120 $GLOBALS['tables']['linktrack_uml_click'], $row['messageid'])); 121 122 $percentBouncedFormatted = $percentViewedFormatted = $percentClickedFormatted = ''; 123 if ($row['bounced'] > 0 && $totls[0] > 0) { 124 $percentBouncedFormatted = ' ('.sprintf('%0.2f', ($row['bounced'] / $totls[0] * 100)).' %)'; 125 } 126 if ($views[0] > 0 && $totls[0] > 0) { 127 $percentViewedFormatted = ' ('.sprintf('%0.2f', ($views[0] / ($totls[0] - $row['bounced']) * 100)).' %)'; 128 } 129 if ($totalclicked[0] > 0 && $totls[0] > 0) { 130 $percentClickedFormatted = ' ('.sprintf('%0.2f', ($totalclicked[0] / ($totls[0] - $row['bounced']) * 100)).' %)'; 131 } 132 133 $ls->setElementHeading(s('Campaign')); 134 $ls->addElement($element, 135 PageURL2('statsoverview&id='.$row['messageid'])); //,PageURL2('message&id='.$row['messageid'])); 136 $ls->setClass($element, 'row1'); 137 // $ls->addColumn($element,s('owner'),$row['owner']); 138 $ls->addColumn($element, s('Date sent'), formatDate($row['sent'], true)); 139 $ls->addColumn($element, s('sent'), number_format((int)$totls[0])); 140 $ls->addColumn($element, s('bncs').Help("bounces"), number_format((int)$row['bounced']).$percentBouncedFormatted); 141 $ls->addColumn($element, s('fwds').Help("forwards"), number_format((int)$fwded[0])); 142 $ls->addColumn($element, s('Unique views').Help("uniqueviews"), number_format((int)$views[0]).$percentViewedFormatted, 143 $views[0] ? PageURL2('mviews&id='.$row['messageid']) : ''); 144 $ls->addColumn($element, s('Total Clicks').Help("totalclicks"), number_format((int)$totalclicks[0]), 145 $totalclicks[0] ? PageURL2('mclicks&id='.$row['messageid']) : ''); 146 147 $ls->addColumn($element, s('Unique Clicks').Help("uniqueclicks"), number_format((int)$totalclicked[0]).$percentClickedFormatted, 148 $totalclicked[0] ? PageURL2('mclicks&id='.$row['messageid']) : ''); 149} 150//# needs reviewing 151if (false && $addcomparison) { 152 $total = Sql_Fetch_Array_Query(sprintf('select count(entered) as total from %s um where um.status = "sent"', 153 $GLOBALS['tables']['usermessage'])); 154 $viewed = Sql_Fetch_Array_Query(sprintf('select count(viewed) as viewed from %s um where um.status = "sent"', 155 $GLOBALS['tables']['usermessage'])); 156 $overall = s('Comparison to other admins'); 157 $ls->addElement($overall); 158 $ls->addColumn($overall, s('views'), $viewed['viewed']); 159 $perc = sprintf('%0.2f', ($viewed['viewed'] / $total['total'] * 100)); 160 $ls->addColumn($overall, s('rate'), $perc.' %'); 161} 162if ($download) { 163 ob_end_clean(); 164 $status .= $ls->tabDelimited(); 165} 166 167$status .= $ls->display(); 168