1<?php 2 3verifyCsrfGetToken(); 4 5if (isset($_GET['id'])) { 6 $id = sprintf('%d', $_GET['id']); 7} else { 8 $id = 0; 9} 10$some = 0; 11$status = ''; 12 13$access = accessLevel('mclicks'); 14switch ($access) { 15 case 'owner': 16 $subselect = ' and owner = '.$_SESSION['logindetails']['id']; 17 if ($id) { 18 $allow = Sql_Fetch_Row_query(sprintf('select owner from %s where id = %d %s', $GLOBALS['tables']['message'], 19 $id, $subselect)); 20 if ($allow[0] != $_SESSION['logindetails']['id']) { 21 echo $GLOBALS['I18N']->get('You do not have access to this page'); 22 23 return; 24 } 25 } 26 break; 27 case 'all': 28 $subselect = ''; 29 break; 30 case 'none': 31 default: 32 $subselect = ' where id = 0'; 33 echo $GLOBALS['I18N']->get('You do not have access to this page'); 34 35 return; 36 break; 37} 38 39$download = !empty($_GET['dl']); 40if ($download) { 41 ob_end_clean(); 42// header("Content-type: text/plain"); 43 header('Content-type: text/csv'); 44 header('Content-disposition: attachment; filename="phpList Campaign click statistics.csv"'); 45 ob_start(); 46} 47/* $req = Sql_Query(sprintf('select distinct messageid, subject, sum(clicked) as totalclicks, count(distinct userid) as users, count(distinct linkid) as linkcount from %s as linktrack, %s as message 48where clicked and linktrack.messageid = message.id %s group by messageid order by entered desc limit 50', 49$GLOBALS['tables']['linktrack'],$GLOBALS['tables']['message'],$subselect));*/ 50$req = Sql_Query(sprintf('select distinct messageid, subject, 51 sum(total) as total, count(forwardid) as linkcount,sum(clicked) as totalclicks, 52 sum(htmlclicked) as htmlclicked,sum(textclicked) as textclicked from %s as linktrack_ml, %s as message 53 where clicked and linktrack_ml.messageid = message.id %s group by messageid order by entered desc limit 50', 54 $GLOBALS['tables']['linktrack_ml'], $GLOBALS['tables']['message'], $subselect)); 55if (!Sql_Affected_Rows()) { 56 $status .= '<p class="information">'.s('There are currently no messages to view').'</p>'; 57} 58$ls = new WebblerListing(s('Clicked campaigns')); 59$ls->setElementHeading(s('Campaign')); 60while ($row = Sql_Fetch_Array($req)) { 61 // Flag indicating that results were returned 62 $some = 1; 63 $messagedata = loadMessageData($row['messageid']); 64 $totalusers = Sql_Fetch_Row_Query(sprintf('select count(userid) from %s where messageid = %d and status = "sent"', 65 $GLOBALS['tables']['usermessage'], $row['messageid'])); 66 $totalclicked = Sql_Fetch_Row_Query(sprintf('select count(distinct userid) from %s where messageid = %d', 67 $GLOBALS['tables']['linktrack_uml_click'], $row['messageid'])); 68 if ($totalusers[0] > 0) { 69 $clickrate = sprintf('%0.2f', ($totalclicked[0] / $totalusers[0] * 100)); 70 } else { 71 $clickrate = s('N/A'); 72 } 73 if (!$download) { 74 if ($messagedata['subject'] != $messagedata['campaigntitle']) { 75 $element = '<!--'.$row['messageid'].'-->'.stripslashes($messagedata['campaigntitle']).'<br/><strong>'.shortenTextDisplay($messagedata['subject'], 76 30).'</strong>'; 77 } else { 78 $element = '<!--'.$row['messageid'].'-->'.shortenTextDisplay($messagedata['subject'], 30); 79 } 80 } else { 81 $element = $messagedata['subject']; 82 } 83 84 $ls->addElement($element, PageURL2('mclicks&id='.$row['messageid'])); 85 $ls->setClass($element, 'row1'); 86 $ls->addColumn($element, ucfirst(s('links')), $row['linkcount']); 87// $ls->addColumn($element,$GLOBALS['I18N']->get('sent'),$totalusers[0]); 88 $ls->addColumn($element, ucfirst(s('subscribers')), number_format($totalclicked[0])); 89 $ls->addColumn($element, ucfirst(s('clickrate')), $clickrate); 90 91 $ls->addColumn($element, ucfirst(s('clicks')), PageLink2('userclicks&msgid='.$row['messageid'], number_format($row['totalclicks']))); 92// $ls->addColumn($element,$GLOBALS['I18N']->get('total'),$row['total']); 93// $ls->addColumn($element,$GLOBALS['I18N']->get('users'),$row['users']); 94 $ls->addRow($element, '', 95 '<div class="content listingsmall fright gray">'.s('html').': '.number_format($row['htmlclicked']).'</div><div class="content listingsmall fright gray">'.s('text').': '.number_format($row['textclicked']).'</div>'); 96 97 /* this one is the percentage of total links versus clicks. I guess that's too detailed for most people. 98 * besides it'll be low 99 $perc = sprintf('%0.2f',($row['totalclicks'] / $row['total'] * 100)); 100 $ls->addColumn($element,$GLOBALS['I18N']->get('rate'),$perc.' %'); 101 */ 102} 103 104// If records / links exist 105if ($some) { 106 // if data export requested 107 if ($download) { 108 ob_end_clean(); 109 $status .= $ls->tabDelimited(); 110 } 111 112 // Add CSV download button 113 $status .= '<div class="action">'; 114 $status .= '<p class="pull-right">'.PageLinkButton('pageaction&action=mclicks&dl=true', s('Download as CSV file')).'</p>'; 115 $status .= '</div><div class="clearfix"></div>'; 116 $status .= $ls->display(); 117} 118