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&amp;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