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&amp;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&amp;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&amp;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&amp;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&amp;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&amp;id='.$row['userid']));
219            $ls->setClass($element, 'row1');
220        } else {
221            //    $link = substr($row['url'],0,50);
222            //    $element = PageLink2($link,$link,PageUrl2('uclicks&amp;id='.$row['forwardid']),"",true,$row['url']);
223            $element = shortenTextDisplay($row['url']);
224            $ls->addElement($element, PageUrl2('uclicks&amp;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&amp;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