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&amp;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&amp;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&amp;msgid='.$row['messageid'].'&amp;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&amp;msgid='.$row['messageid'].'&amp;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