1<?php 2 3// click stats listing users 4require_once dirname(__FILE__).'/accesscheck.php'; 5 6if (isset($_GET['msgid'])) { 7 $msgid = sprintf('%d', $_GET['msgid']); 8} else { 9 $msgid = 0; 10} 11if (isset($_GET['fwdid'])) { 12 $fwdid = sprintf('%d', $_GET['fwdid']); 13} else { 14 $fwdid = 0; 15} 16if (isset($_GET['userid'])) { 17 $userid = sprintf('%d', $_GET['userid']); 18} else { 19 $userid = 0; 20} 21if (isset($_GET['start'])) { 22 $start = sprintf('%d', $_GET['start']); 23} else { 24 $start = 0; 25} 26 27if (!$msgid && !$fwdid && !$userid) { 28 echo $GLOBALS['I18N']->get('Invalid Request'); 29 30 return; 31} 32 33$access = accessLevel('userclicks'); 34switch ($access) { 35 case 'owner': 36 case 'all': 37 $subselect = ''; 38 break; 39 case 'none': 40 default: 41 print $GLOBALS['I18N']->get('You do not have access to this page'); 42 43 return; 44 break; 45} 46 47$download = !empty($_GET['dl']); 48$downloadContent = ''; 49 50if ($download) { 51 ob_end_clean(); 52 header('Content-type: text/csv'); 53 header('Content-disposition: attachment; filename="phpList click statistics.csv"'); 54 ob_start(); 55} 56 57//$limit = ' limit 100'; 58 59$ls = new WebblerListing($GLOBALS['I18N']->get('Click statistics')); 60 61if ($fwdid) { 62 $urldata = Sql_Fetch_Array_Query(sprintf('select url from %s where id = %d', 63 $GLOBALS['tables']['linktrack_forward'], $fwdid)); 64} 65if ($msgid) { 66 // $messagedata = Sql_Fetch_Array_query("SELECT * FROM {$tables['message']} where id = $msgid $subselect"); 67 $messagedata = loadMessageData($msgid); 68} 69if ($userid) { 70 $userdata = Sql_Fetch_Array_query("SELECT * FROM {$tables['user']} where id = $userid $subselect"); 71} 72 73if ($fwdid && $msgid) { 74 echo '<h3>'.$GLOBALS['I18N']->get('Subscriber clicks for a URL in a campaign'); 75 echo ' '.strtolower(PageLink2('uclicks&id='.$fwdid, $urldata['url'])); 76 echo '</h3>'; 77 $downloadContent = s('Subscribers who clicked on URL "%s" in the campaign with subject "%s", sent %s', 78 $urldata['url'], $messagedata['subject'], $messagedata['sent']).PHP_EOL; 79 echo '<table class="userclicksDetails">'; 80 if ($messagedata['subject'] != $messagedata['campaigntitle']) { 81 echo '<tr><td>'.s('Title').'<td><td>'.$messagedata['campaigntitle'].'</td></tr>'; 82 } 83 echo '<tr><td>'.s('Subject').'<td><td>'.PageLink2('mclicks&id='.$msgid, $messagedata['subject']).'</td></tr> 84 <tr><td>' .$GLOBALS['I18N']->get('Entered').'<td><td>'.$messagedata['entered'].'</td></tr> 85 <tr><td>' .$GLOBALS['I18N']->get('Sent').'<td><td>'.$messagedata['sent'].'</td></tr> 86 </table>'; 87 echo '<div class="fright">'.PageLinkButton('userclicks&fwdid='.$fwdid.'&msgid='.$msgid.'&dl=1', 88 s('Download subscribers')).'</div>'; 89 $query = sprintf('select htmlclicked, textclicked, user.email,user.id as userid,firstclick,latestclick,clicked 90 from %s as uml_click, %s as user where uml_click.userid = user.id 91 and uml_click.forwardid = %d and uml_click.messageid = %d 92 and uml_click.clicked', $GLOBALS['tables']['linktrack_uml_click'], $GLOBALS['tables']['user'], $fwdid, $msgid); 93} elseif ($userid && $msgid) { 94 echo '<h3>'.$GLOBALS['I18N']->get('Subscriber clicks on a campaign').'</h3>'; 95 echo s('Subscriber').' '.PageLink2('user&id='.$userid, $userdata['email']); 96 echo '</h3>'; 97 echo '<table class="userclickDetails">'; 98 if ($messagedata['subject'] != $messagedata['campaigntitle']) { 99 echo '<tr><td>'.s('Title').'<td><td>'.$messagedata['campaigntitle'].'</td></tr>'; 100 } 101 echo ' 102 <tr><td>' .$GLOBALS['I18N']->get('Subject').'<td><td>'.PageLink2('mclicks&id='.$msgid, 103 $messagedata['subject']).'</td></tr> 104 <tr><td>' .$GLOBALS['I18N']->get('Entered').'<td><td>'.$messagedata['entered'].'</td></tr> 105 <tr><td>' .$GLOBALS['I18N']->get('Sent').'<td><td>'.$messagedata['sent'].'</td></tr> 106 </table>'; 107 $query = sprintf('select htmlclicked, textclicked,user.email,user.id as userid,firstclick,latestclick, 108 clicked,messageid,forwardid,url from %s as uml_click, %s as user, %s as forward where uml_click.userid = user.id 109 and uml_click.userid = %d and uml_click.messageid = %d and forward.id = uml_click.forwardid', 110 $GLOBALS['tables']['linktrack_uml_click'], $GLOBALS['tables']['user'], $GLOBALS['tables']['linktrack_forward'], 111 $userid, $msgid); 112} elseif ($fwdid) { 113 echo '<h3>'.$GLOBALS['I18N']->get('Subscribers who clicked a URL').' <b>'.$urldata['url'].'</b></h3>'; 114 $downloadContent = s('Subscribers who clicked on the URL "%s" across all campaigns', $urldata['url']).PHP_EOL; 115 echo '<div class="fright">'.PageLinkButton('userclicks&fwdid='.$fwdid.'&dl=1', 116 s('Download subscribers')).'</div>'; 117 $query = sprintf(' 118 SELECT user.email, 119 user.id AS userid, 120 MIN(firstclick) AS firstclick, 121 MAX(latestclick) AS latestclick, 122 SUM(clicked) AS clicked 123 FROM %s AS uml_click 124 JOIN %s AS user ON uml_click.userid = user.id 125 WHERE uml_click.forwardid = %d 126 GROUP BY uml_click.userid 127 ', 128 $GLOBALS['tables']['linktrack_uml_click'], 129 $GLOBALS['tables']['user'], 130 $fwdid 131 ); 132} elseif ($msgid) { 133 echo '<h3>'.$GLOBALS['I18N']->get('Subscribers who clicked a campaign').'</h3>'; 134 echo '<table class="userclickDetails">'; 135 if ($messagedata['subject'] != $messagedata['campaigntitle']) { 136 echo '<tr><td>'.s('Title').'<td><td>'.$messagedata['campaigntitle'].'</td></tr>'; 137 } 138 echo ' 139 <tr><td>' .$GLOBALS['I18N']->get('Subject').'<td><td>'.$messagedata['subject'].'</td></tr> 140 <tr><td>' .$GLOBALS['I18N']->get('Entered').'<td><td>'.$messagedata['entered'].'</td></tr> 141 <tr><td>' .$GLOBALS['I18N']->get('Sent').'<td><td>'.$messagedata['sent'].'</td></tr> 142 </table>'; 143 $downloadContent = s('Subscribers who clicked on campaign with subject "%s", sent %s', $messagedata['subject'], 144 $messagedata['sent']).PHP_EOL; 145 echo '<div class="fright">'.PageLinkButton('userclicks&msgid='.$msgid.'&dl=1', 146 s('Download subscribers')).'</div>'; 147 $query = sprintf(' 148 SELECT DISTINCT user.email, 149 user.id AS userid, 150 MIN(firstclick) AS firstclick, 151 MAX(latestclick) AS latestclick, 152 SUM(clicked) AS clicked 153 FROM %s AS uml_click 154 JOIN %s AS user ON uml_click.userid = user.id 155 WHERE uml_click.messageid = %d 156 GROUP BY uml_click.userid 157 ', 158 $GLOBALS['tables']['linktrack_uml_click'], 159 $GLOBALS['tables']['user'], 160 $msgid 161 ); 162} elseif ($userid) { 163 echo '<div class="jumbotron">'.$GLOBALS['I18N']->get('All clicks by').' <b>'.PageLink2('user&id='.$userid, $userdata['email']).'</b></div>'; 164 165 $query = ' 166 SELECT 167 SUM(htmlclicked) AS htmlclicked, 168 SUM(textclicked) AS textclicked, 169 user.email, 170 user.id AS userid, 171 MIN(firstclick) AS firstclick, 172 MAX(latestclick) AS latestclick, 173 SUM(clicked) AS clicked, 174 GROUP_CONCAT( 175 messageid 176 ORDER BY 177 messageid SEPARATOR \' \') AS messageid, 178 forwardid, 179 url 180 FROM 181 '.$GLOBALS['tables']['linktrack_uml_click'].' AS uml_click 182 JOIN 183 '.$GLOBALS['tables']['user'].' AS user ON uml_click.userid = user.id 184 JOIN 185 '.$GLOBALS['tables']['linktrack_forward'].' AS forward ON forward.id = uml_click.forwardid 186 WHERE 187 uml_click.userid = '.sprintf('%d', $userid).' 188 GROUP BY 189 forwardid 190 ORDER BY 191 clicked DESC, 192 url 193 '; 194} 195 196//ob_end_flush(); 197//flush(); 198 199$req = Sql_Query($query); 200$total = Sql_Num_Rows($req); 201if ($total > 100 && !$download) { 202 echo simplePaging('userclicks&msgid='.$msgid.'&fwdid='.$fwdid.'&userid='.$userid, $start, $total, 100, 203 s('Subscribers')); 204 205 $limit = ' limit '.$start.', 100'; 206 $req = Sql_Query($query.' '.$limit); 207} 208 209$summary = array(); 210$summary['totalclicks'] = 0; 211while ($row = Sql_Fetch_Array($req)) { 212 // print $row['email'] . "<br/>"; 213 if ($download) { 214 $downloadContent .= $row['email'].PHP_EOL; 215 } else { 216 if (!$userid) { 217 $element = shortenTextDisplay($row['email']); 218 $ls->addElement($element, PageUrl2('user&id='.$row['userid'])); 219 $ls->setClass($element, 'row1'); 220 } else { 221 // $link = substr($row['url'],0,50); 222 // $element = PageLink2($link,$link,PageUrl2('uclicks&id='.$row['forwardid']),"",true,$row['url']); 223 $element = shortenTextDisplay($row['url']); 224 $ls->addElement($element, PageUrl2('uclicks&id='.$row['forwardid'])); 225 $ls->setClass($element, 'row1'); 226 $messageLinks = preg_replace_callback( 227 '/\d+/', 228 function ($matches) { 229 return PageLink2("mclicks&id={$matches[0]}", $matches[0]); 230 }, 231 $row['messageid'] 232 ); 233 $ls->addColumn($element, $GLOBALS['I18N']->get('message'), $messageLinks); 234 } 235 // $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)); 236 // $total = Sql_Verbose_Query(sprintf('select count(*) as total from %s where messageid = %d and url = "%s"', 237 // $GLOBALS['tables']['linktrack'],$id,$row['url'])); 238 // $totalsent = Sql_Fetch_Array_Query(sprintf('select count(*) as total from %s where url = "%s"', 239 // $GLOBALS['tables']['linktrack'],$urldata['url'])); 240 $ls_userid = ''; 241 242 if (!empty($row['userid'])) { 243 $userStatus = Sql_Fetch_Assoc_Query(sprintf('select blacklisted,confirmed from %s where id = %d', 244 $GLOBALS['tables']['user'], $row['userid'])); 245 $ls->addColumn($element, s('Status'), 246 $userStatus['confirmed'] && empty($userStatus['blacklisted']) ? $GLOBALS['img_tick'] : $GLOBALS['img_cross']); 247 } 248 $ls->addColumn($element, $GLOBALS['I18N']->get('firstclick'), formatDateTime($row['firstclick'], 1)); 249 $ls->addColumn($element, $GLOBALS['I18N']->get('latestclick'), formatDateTime($row['latestclick'], 1)); 250 $ls->addColumn($element, $GLOBALS['I18N']->get('clicks'), $row['clicked']); 251 if (!$userid) { //Display 252 $ls_userid = '<span class="viewusers"><a class="button" href="' . PageUrl2('userclicks&userid=' . $row['userid']) . '" title="' . s('view user') . '"></a></span>'; 253 $ls->addColumn($element, s('View clicks'), $ls_userid); 254 } 255 if (!empty($row['htmlclicked']) && !empty($row['textclicked'])) { 256 $ls->addRow($element, 257 '<div class="content listingsmall fright gray">'.$GLOBALS['I18N']->get('HTML').': '.$row['htmlclicked'].'</div>'. 258 '<div class="content listingsmall fright gray">'.$GLOBALS['I18N']->get('text').': '.$row['textclicked'].'</div>', 259 ''); 260 } 261 // $ls->addColumn($element,$GLOBALS['I18N']->get('sent'),$total['total']); 262 // $perc = sprintf('%0.2f',($row['numclicks'] / $totalsent['total'] * 100)); 263 // $ls->addColumn($element,$GLOBALS['I18N']->get('clickrate'),$perc.'%'); 264 $summary['totalclicks'] += $row['clicked']; 265 } 266} 267 268//# adding a total doesn't make sense if we're not listing everything, it'll only do the total of the page 269//$ls->addElement($GLOBALS['I18N']->get('total')); 270//$ls->setClass($GLOBALS['I18N']->get('total'),'rowtotal'); 271//$ls->addColumn($GLOBALS['I18N']->get('total'),$GLOBALS['I18N']->get('clicks'),$summary['totalclicks']); 272if (!$download) { 273 echo $ls->display(); 274} else { 275 ob_end_clean(); 276 echo $downloadContent; 277 exit; 278} 279