1<?php
2// print '<p>'.s('Select Message to view').'</p>';
3verifyCsrfGetToken();
4
5if (isset($_GET['id'])) {
6    $id = sprintf('%d', $_GET['id']);
7} else {
8    $id = 0;
9}
10
11$start = 0;
12$limit = ' limit 10';
13if (isset($_GET['start'])) {
14    $start = sprintf('%d', $_GET['start']);
15    $limit = ' limit '.$start.', 10';
16}
17
18$status = '';
19$access = accessLevel('statsoverview');
20$ownership = '';
21$subselect = '';
22$paging = '';
23
24//print "Access Level: $access";
25switch ($access) {
26    case 'owner':
27        $ownership = sprintf(' and owner = %d ', $_SESSION['logindetails']['id']);
28        if ($id) {
29            $allow = Sql_Fetch_Row_query(sprintf('select owner from %s where id = %d %s', $GLOBALS['tables']['message'],
30                $id, $ownership));
31            if ($allow[0] != $_SESSION['logindetails']['id']) {
32                $status .= s('You do not have access to this page');
33
34                return;
35            }
36        }
37        $addcomparison = 1;
38        break;
39    case 'all':
40        break;
41    case 'none':
42    default:
43        $ownership = ' and msg.id = 0';
44        $status .= s('You do not have access to this page');
45
46        return;
47        break;
48}
49
50$download = !empty($_GET['dl']);
51if ($download) {
52    ob_end_clean();
53//  header("Content-type: text/plain");
54    header('Content-type: text/csv');
55    if (!$id) {
56        header('Content-disposition:  attachment; filename="phpList Campaign statistics.csv"');
57    }
58    ob_start();
59}
60
61if (empty($start)) {
62    $status .= '<div class="actions pull-right">'.PageLinkButton('pageaction&action=statsoverview&dl=true',
63            s('Download as CSV file')).'</div><div class="clearfix"></div>';
64}
65if (!empty($_SESSION['LoadDelay'])) {
66    sleep($_SESSION['LoadDelay']);
67}
68$timerange = ' and msg.entered > date_sub(now(),interval 12 month)';
69$timerange = '';
70
71$query = sprintf('select msg.owner,msg.id as messageid, subject, sent, bouncecount as bounced
72    from %s msg where msg.status = "sent" %s %s %s
73    group by msg.id order by msg.sent desc',
74    $GLOBALS['tables']['message'], $subselect, $timerange, $ownership);
75$req = Sql_Query($query);
76$total = Sql_Num_Rows($req);
77if ($total > 10) {
78    //print Paging(PageUrl2('statsoverview'),$start,$total,10);
79    $paging = simplePaging('statsoverview', $start, $total, 10);
80    // Increase the record limit for exported files
81    if ($download) {
82        $limit = ' limit 1000';
83    }
84
85    $query .= $limit;
86    $req = Sql_Query($query);
87}
88
89if (!Sql_Affected_Rows()) {
90    $status .= '<p class="information">'.s('There are currently no campaigns to view').'</p>';
91}
92
93$ls = new WebblerListing('');
94$ls->usePanel($paging);
95while ($row = Sql_Fetch_Array($req)) {
96    //  $element = '<!--'.$row['messageid'].'-->'.shortenTextDisplay($row['subject'],30);
97    $messagedata = loadMessageData($row['messageid']);
98
99    if ($messagedata['subject'] != $messagedata['campaigntitle']) {
100        $element = '<!--'.$row['messageid'].'-->'
101            .stripslashes(shortenTextDisplay($messagedata['campaigntitle'], 30)).'<br/><strong>'.stripslashes(shortenTextDisplay($messagedata['subject'], 30)).'</strong>';
102    } else {
103        $element = '<!--'.$row['messageid'].'-->'
104            .stripslashes(shortenTextDisplay($messagedata['subject'], 30));
105    }
106
107    $fwded = Sql_Fetch_Row_Query(sprintf('select count(id) from %s where message = %d',
108    $GLOBALS['tables']['user_message_forward'], $row['messageid']));
109    $views = Sql_Fetch_Row_Query(sprintf('select count(viewed) from %s where messageid = %d
110           and status = "sent"',
111    $GLOBALS['tables']['usermessage'], $row['messageid']));
112    $totls = Sql_Fetch_Row_Query(sprintf('select count(status) from %s where messageid = %d
113           and status = "sent"',
114    $GLOBALS['tables']['usermessage'], $row['messageid']));
115
116    $totalclicked = Sql_Fetch_Row_Query(sprintf('select count(distinct userid) from %s where messageid = %d',
117    $GLOBALS['tables']['linktrack_uml_click'], $row['messageid']));
118
119    $totalclicks = Sql_Fetch_Row_Query(sprintf('select count( userid) from %s where messageid = %d',
120        $GLOBALS['tables']['linktrack_uml_click'], $row['messageid']));
121
122    $percentBouncedFormatted = $percentViewedFormatted = $percentClickedFormatted = '';
123    if ($row['bounced'] > 0 && $totls[0] > 0) {
124        $percentBouncedFormatted = ' ('.sprintf('%0.2f', ($row['bounced'] / $totls[0] * 100)).' %)';
125    }
126    if ($views[0] > 0 && $totls[0] > 0) {
127        $percentViewedFormatted = ' ('.sprintf('%0.2f', ($views[0] / ($totls[0] - $row['bounced']) * 100)).' %)';
128    }
129    if ($totalclicked[0] > 0 && $totls[0] > 0) {
130        $percentClickedFormatted = ' ('.sprintf('%0.2f', ($totalclicked[0] / ($totls[0] - $row['bounced']) * 100)).' %)';
131    }
132
133    $ls->setElementHeading(s('Campaign'));
134    $ls->addElement($element,
135        PageURL2('statsoverview&amp;id='.$row['messageid'])); //,PageURL2('message&amp;id='.$row['messageid']));
136    $ls->setClass($element, 'row1');
137    //   $ls->addColumn($element,s('owner'),$row['owner']);
138    $ls->addColumn($element, s('Date sent'), formatDate($row['sent'], true));
139    $ls->addColumn($element, s('sent'), number_format((int)$totls[0]));
140    $ls->addColumn($element, s('bncs').Help("bounces"), number_format((int)$row['bounced']).$percentBouncedFormatted);
141    $ls->addColumn($element, s('fwds').Help("forwards"), number_format((int)$fwded[0]));
142    $ls->addColumn($element, s('Unique views').Help("uniqueviews"), number_format((int)$views[0]).$percentViewedFormatted,
143    $views[0] ? PageURL2('mviews&amp;id='.$row['messageid']) : '');
144    $ls->addColumn($element, s('Total Clicks').Help("totalclicks"), number_format((int)$totalclicks[0]),
145        $totalclicks[0] ? PageURL2('mclicks&id='.$row['messageid']) : '');
146
147    $ls->addColumn($element, s('Unique Clicks').Help("uniqueclicks"), number_format((int)$totalclicked[0]).$percentClickedFormatted,
148        $totalclicked[0] ? PageURL2('mclicks&id='.$row['messageid']) : '');
149}
150//# needs reviewing
151if (false && $addcomparison) {
152    $total = Sql_Fetch_Array_Query(sprintf('select count(entered) as total from %s um where um.status = "sent"',
153        $GLOBALS['tables']['usermessage']));
154    $viewed = Sql_Fetch_Array_Query(sprintf('select count(viewed) as viewed from %s um where um.status = "sent"',
155        $GLOBALS['tables']['usermessage']));
156    $overall = s('Comparison to other admins');
157    $ls->addElement($overall);
158    $ls->addColumn($overall, s('views'), $viewed['viewed']);
159    $perc = sprintf('%0.2f', ($viewed['viewed'] / $total['total'] * 100));
160    $ls->addColumn($overall, s('rate'), $perc.' %');
161}
162if ($download) {
163    ob_end_clean();
164    $status .= $ls->tabDelimited();
165}
166
167$status .= $ls->display();
168