1<?php 2 3// click stats per url 4require_once dirname(__FILE__).'/accesscheck.php'; 5 6if (isset($_GET['id'])) { 7 $id = sprintf('%d', $_GET['id']); 8} else { 9 $id = 0; 10} 11 12$some = 0; 13$access = accessLevel('uclicks'); 14switch ($access) { 15 case 'owner': 16 $select_tables = $GLOBALS['tables']['linktrack_ml'].' as ml, '.$GLOBALS['tables']['message'].' as message, '.$GLOBALS['tables']['linktrack_forward'].' as forward '; 17 $owner_and = ' and message.id = ml.messageid and message.owner = '.$_SESSION['logindetails']['id']; 18 break; 19 case 'all': 20 $select_tables = $GLOBALS['tables']['linktrack_ml'].' as ml, '.$GLOBALS['tables']['linktrack_forward'].' as forward '; 21 $owner_and = ''; 22 break; 23 break; 24 case 'none': 25 default: 26 print s('You do not have access to this page'); 27 28 return; 29 break; 30} 31 32$download = !empty($_GET['dl']); 33if ($download) { 34 ob_end_clean(); 35// header("Content-type: text/plain"); 36 header('Content-type: text/csv'); 37 if (!$id) { 38 header('Content-disposition: attachment; filename="phpList URL click statistics.csv"'); 39 } 40 ob_start(); 41} 42 43if (!$id) { 44 $req = Sql_Query(sprintf(' 45 SELECT forward.id, 46 url, 47 SUM(clicked) AS numclicks, 48 MAX(latestclick) AS lastclicked, 49 COUNT(messageid) AS msgs 50 FROM %s 51 WHERE clicked %s AND forward.id = ml.forwardid AND latestclick > DATE_SUB(NOW(),INTERVAL 12 MONTH) 52 GROUP BY forward.id 53 ORDER BY lastclicked DESC 54 LIMIT 50', 55 $select_tables, $owner_and)); 56 $ls = new WebblerListing(s('Available URLs')); 57 while ($row = Sql_Fetch_Array($req)) { 58 $some = 1; 59 if (!$download) { 60 $element = shortenTextDisplay($row['url'], 30); 61 } else { 62 $element = $row['url']; 63 } 64 $ls->addElement($element, PageURL2('uclicks&id='.$row['id'])); 65 $ls->addColumn($element, s('msgs'), $row['msgs']); 66 $ls->addColumn($element, s('last clicked'), formatDateTime($row['lastclicked'], 1)); 67 $ls->addColumn($element, s('clicks'), $row['numclicks']); 68 } 69 if ($download) { 70 ob_end_clean(); 71 echo $ls->tabDelimited(); 72 } 73 if ($some) { 74 echo '<p>'.s('Select URL to view').'</p>'; 75 echo '<div class="actions pull-right">'.PageLinkButton('uclicks&dl=true', 76 s('Download as CSV file')).'</div><div class="clearfix"></div>'; 77 echo $ls->display(); 78 } else { 79 echo '<p class="information">'.s('There are currently no statistics available').'</p>'; 80 } 81 82 return; 83} 84 85echo '<div class="actions">'.PageLinkButton('uclicks&dl=true&id='.$id, 86 s('Download as CSV file')).'</div>'; 87 88$ls = new WebblerListing(s('URL Click Statistics')); 89 90$urldata = Sql_Fetch_Array_Query(sprintf('select url from %s where id = %d', 91 $GLOBALS['tables']['linktrack_forward'], $id)); 92echo '<h3>'.s('Click details for a URL').': <b><a href="'.htmlspecialchars( $urldata['url'] ).'" target="_blank"><span aria-hidden="true" class="glyphicon glyphicon-new-window"></span> '.htmlspecialchars( $urldata['url'] ).'</a></b></h3><br/>'; 93echo PageLinkButton('userclicks&fwdid='.$id, s('View subscribers')); 94if ($download) { 95 header('Content-disposition: attachment; filename="phpList URL click statistics for '.$urldata['url'].'.csv"'); 96} 97$req = Sql_Query(sprintf('select messageid,firstclick,latestclick,total,clicked 98 from %s where forwardid = %d and firstclick is not null order by firstclick desc', 99 $GLOBALS['tables']['linktrack_ml'], $id)); 100$summary = array(); 101$summary['totalsent'] = 0; 102$summary['totalclicks'] = 0; 103$summary['uniqueclicks'] = 0; 104 105while ($row = Sql_Fetch_Array($req)) { 106 $messagedata = loadMessageData($row['messageid']); 107 if (!$download) { 108 if ($messagedata['subject'] != $messagedata['campaigntitle']) { 109 $element = '<!--'.$row['messageid'].'-->'.stripslashes($messagedata['campaigntitle']).'<br/><strong>'.shortenTextDisplay($messagedata['subject'], 110 30).'</strong>'; 111 } else { 112 $element = '<!--'.$row['messageid'].'-->'.shortenTextDisplay($messagedata['subject'], 30); 113 } 114 } else { 115 $element = $messagedata['subject']; 116 } 117// $element = s('msg').' '.$row['messageid'].': '.substr($msgsubj[0],0,25). '...'; 118// $element = sprintf('<a href="%s" target="_blank" class="url" title="%s">%s</a>',$row['url'],$row['url'],substr(str_replace('http://','',$row['url']),0,50)); 119// $total = Sql_Verbose_Query(sprintf('select count(*) as total from %s where messageid = %d and url = "%s"', 120// $GLOBALS['tables']['linktrack'],$id,$row['url'])); 121 122 // if (CLICKTRACK_SHOWDETAIL) { 123 $uniqueclicks = Sql_Fetch_Array_Query(sprintf('select count(distinct userid) as users from %s 124 where messageid = %d and forwardid = %d', 125 $GLOBALS['tables']['linktrack_uml_click'], $row['messageid'], $id)); 126// } 127 128 $ls->addElement($element, PageUrl2('mclicks&id='.$row['messageid'])); 129 $ls->setClass($element, 'row1'); 130 $ls->addColumn($element, s('firstclick'), formatDateTime($row['firstclick'], 1)); 131 $ls->addColumn($element, s('latestclick'), formatDateTime($row['latestclick'], 1)); 132 $ls->addRow($element, 133 '<div class="listingsmall gray">'.s('sent').': '.$row['total'].'</div>', ''); 134// $ls->addColumn($element,s('clicks'),$row['clicked'].'<span class="viewusers"><a class="button" href="'.PageUrl2('userclicks&msgid='.$row['messageid'].'&fwdid='.$id.'" title="'.s('view users').'"></a></span>')); 135// $perc = sprintf('%0.2f',($row['clicked'] / $row['total'] * 100)); 136// $ls->addColumn($element,s('clickrate'),$perc.'%'); 137 $summary['totalsent'] += $row['total']; 138// if (CLICKTRACK_SHOWDETAIL) { 139 $ls->addColumn($element, s('clicks'), 140 number_format($uniqueclicks['users']).'<span class="viewusers"><a class="button" href="'.PageUrl2('userclicks&msgid='.$row['messageid'].'&fwdid='.$id).'" title="'.s('view subscribers who clicked').'"></a></span>'); 141 $perc = sprintf('%0.2f', ($uniqueclicks['users'] / $row['total'] * 100)); 142 $ls->addColumn($element, s('click rate'), $perc.'%'); 143 $summary['uniqueclicks'] += $uniqueclicks['users']; 144// } 145 $summary['totalclicks'] += $row['clicked']; 146} 147$ls->addElement(s('total')); 148$ls->setClass(s('total'), 'rowtotal'); 149//$ls->addColumn(s('total'),s('clicks'),$summary['totalclicks']); 150//$perc = sprintf('%0.2f',($summary['totalclicks'] / $summary['totalsent'] * 100)); 151//$ls->addColumn(s('total'),s('clickrate'),$perc.'%'); 152//if (CLICKTRACK_SHOWDETAIL) { 153$ls->addColumn(s('total'), s('clicks'), $summary['uniqueclicks']); 154$perc = sprintf('%0.2f', ($summary['uniqueclicks'] / $summary['totalsent'] * 100)); 155$ls->addColumn(s('total'), s('click rate'), $perc.'%'); 156//} 157echo $ls->display(); 158if ($download) { 159 ob_end_clean(); 160 echo $ls->tabDelimited(); 161} 162