1<?php
2
3class StatisticsQuery {
4    private $hlp;
5
6    public function __construct(helper_plugin_statistics $hlp) {
7        $this->hlp = $hlp;
8    }
9
10    /**
11     * Return some aggregated statistics
12     */
13    public function aggregate($tlimit) {
14        $data = array();
15
16        $sql    = "SELECT ref_type, COUNT(*) as cnt
17                  FROM " . $this->hlp->prefix . "access as A
18                 WHERE $tlimit
19                   AND ua_type = 'browser'
20              GROUP BY ref_type";
21        $result = $this->hlp->runSQL($sql);
22
23        if(is_array($result)) foreach($result as $row) {
24            if($row['ref_type'] == 'search') $data['search'] = $row['cnt'];
25            if($row['ref_type'] == 'external') $data['external'] = $row['cnt'];
26            if($row['ref_type'] == 'internal') $data['internal'] = $row['cnt'];
27            if($row['ref_type'] == '') $data['direct'] = $row['cnt'];
28        }
29
30        // general user and session info
31        $sql    = "SELECT COUNT(DISTINCT session) as sessions,
32                       COUNT(session) as views,
33                       COUNT(DISTINCT user) as users,
34                       COUNT(DISTINCT uid) as visitors
35                  FROM " . $this->hlp->prefix . "access as A
36                 WHERE $tlimit
37                   AND ua_type = 'browser'";
38        $result = $this->hlp->runSQL($sql);
39
40        $data['users']     = max($result[0]['users'] - 1, 0); // subtract empty user
41        $data['sessions']  = $result[0]['sessions'];
42        $data['pageviews'] = $result[0]['views'];
43        $data['visitors']  = $result[0]['visitors'];
44
45        // calculate bounce rate
46        if($data['sessions']) {
47            $sql                = "SELECT COUNT(*) as cnt
48                      FROM " . $this->hlp->prefix . "session as A
49                     WHERE $tlimit
50                       AND views = 1";
51            $result             = $this->hlp->runSQL($sql);
52            $data['bouncerate'] = $result[0]['cnt'] * 100 / $data['sessions'];
53
54            // new visitors
55            $result              = "SELECT COUNT(*) as cnt
56                         FROM " . $this->hlp->prefix . "session as A
57                        WHERE $tlimit
58                          AND NOT EXISTS (
59                                SELECT *
60                                  FROM stats_session B
61                                 WHERE A.session <> B.session
62                                   AND B.uid = B.uid
63                              )";
64            $result              = $this->hlp->runSQL($sql);
65            $data['newvisitors'] = $result[0]['cnt'] * 100 / $data['sessions'];
66        }
67
68        // calculate avg. number of views per session
69        $sql              = "SELECT AVG(views) as cnt
70                  FROM " . $this->hlp->prefix . "session as A
71                     WHERE $tlimit";
72        $result           = $this->hlp->runSQL($sql);
73        $data['avgpages'] = $result[0]['cnt'];
74
75        /* not used currently
76                $sql = "SELECT COUNT(id) as robots
77                          FROM ".$this->hlp->prefix."access as A
78                         WHERE $tlimit
79                           AND ua_type = 'robot'";
80                $result = $this->hlp->runSQL($sql);
81                $data['robots'] = $result[0]['robots'];
82        */
83
84        // average time spent on the site
85        $sql               = "SELECT AVG(end - dt)/60 as time
86                  FROM " . $this->hlp->prefix . "session as A
87                 WHERE $tlimit
88                   AND dt != end
89                   AND DATE(dt) = DATE(end)";
90        $result            = $this->hlp->runSQL($sql);
91        $data['timespent'] = $result[0]['time'];
92
93        // logins
94        $sql            = "SELECT COUNT(*) as logins
95                  FROM " . $this->hlp->prefix . "logins as A
96                 WHERE $tlimit
97                   AND (type = 'l' OR type = 'p')";
98        $result         = $this->hlp->runSQL($sql);
99        $data['logins'] = $result[0]['logins'];
100
101        // registrations
102        $sql                   = "SELECT COUNT(*) as registrations
103                  FROM " . $this->hlp->prefix . "logins as A
104                 WHERE $tlimit
105                   AND type = 'C'";
106        $result                = $this->hlp->runSQL($sql);
107        $data['registrations'] = $result[0]['registrations'];
108
109        // current users
110        $sql = "SELECT COUNT(*) as current
111                  FROM ". $this->hlp->prefix . "lastseen
112                 WHERE `dt` >= NOW() - INTERVAL 10 MINUTE";
113        $result                = $this->hlp->runSQL($sql);
114        $data['current'] = $result[0]['current'];
115
116        return $data;
117    }
118
119    /**
120     * standard statistics follow, only accesses made by browsers are counted
121     * for general stats like browser or OS only visitors not pageviews are counted
122     */
123
124    /**
125     * Return some trend data about visits and edits in the wiki
126     */
127    public function dashboardviews($tlimit, $hours = false) {
128        if($hours) {
129            $TIME = 'HOUR(dt)';
130        } else {
131            $TIME = 'DATE(dt)';
132        }
133
134        $data = array();
135
136        // access trends
137        $sql    = "SELECT $TIME as time,
138                       COUNT(DISTINCT session) as sessions,
139                       COUNT(session) as pageviews,
140                       COUNT(DISTINCT uid) as visitors
141                  FROM " . $this->hlp->prefix . "access as A
142                 WHERE $tlimit
143                   AND ua_type = 'browser'
144              GROUP BY $TIME
145              ORDER BY time";
146        $result = $this->hlp->runSQL($sql);
147        foreach($result as $row) {
148            $data[$row['time']]['sessions']  = $row['sessions'];
149            $data[$row['time']]['pageviews'] = $row['pageviews'];
150            $data[$row['time']]['visitors']  = $row['visitors'];
151        }
152        return $data;
153    }
154
155    public function dashboardwiki($tlimit, $hours = false) {
156        if($hours) {
157            $TIME = 'HOUR(dt)';
158        } else {
159            $TIME = 'DATE(dt)';
160        }
161
162        $data = array();
163
164        // edit trends
165        foreach(array('E', 'C', 'D') as $type) {
166            $sql    = "SELECT $TIME as time,
167                           COUNT(*) as cnt
168                      FROM " . $this->hlp->prefix . "edits as A
169                     WHERE $tlimit
170                       AND type = '$type'
171                  GROUP BY $TIME
172                  ORDER BY time";
173            $result = $this->hlp->runSQL($sql);
174            foreach($result as $row) {
175                $data[$row['time']][$type] = $row['cnt'];
176            }
177        }
178        ksort($data);
179        return $data;
180    }
181
182    public function history($tlimit, $info, $interval = false) {
183        if($interval == 'weeks') {
184            $TIME = 'EXTRACT(YEAR FROM dt), EXTRACT(WEEK FROM dt)';
185        } elseif ($interval == 'months') {
186            $TIME = 'EXTRACT(YEAR_MONTH FROM dt)';
187        } else {
188            $TIME = 'dt';
189        }
190
191        $mod = 1;
192        if($info == 'media_size' || $info == 'page_size') {
193            $mod = 1024*1024;
194        }
195
196        $sql = "SELECT $TIME as time,
197                       AVG(`value`)/$mod as cnt
198                  FROM " . $this->hlp->prefix . "history as A
199                 WHERE $tlimit
200                   AND info = '$info'
201                  GROUP BY $TIME
202                  ORDER BY $TIME";
203        return $this->hlp->runSQL($sql);
204    }
205
206    public function searchengines($tlimit, $start = 0, $limit = 20) {
207        $sql = "SELECT COUNT(*) as cnt, engine as eflag, engine
208                  FROM " . $this->hlp->prefix . "search as A
209                 WHERE $tlimit
210              GROUP BY engine
211              ORDER BY cnt DESC, engine" .
212            $this->mklimit($start, $limit);
213        return $this->hlp->runSQL($sql);
214    }
215
216    public function searchphrases($extern, $tlimit, $start = 0, $limit = 20) {
217        if($extern) {
218            $WHERE = "engine != 'dokuwiki'";
219            $I     = '';
220        } else {
221            $WHERE = "engine = 'dokuwiki'";
222            $I     = 'i';
223        }
224        $sql = "SELECT COUNT(*) as cnt, query, query as ${I}lookup
225                  FROM " . $this->hlp->prefix . "search as A
226                 WHERE $tlimit
227                   AND $WHERE
228              GROUP BY query
229              ORDER BY cnt DESC, query" .
230            $this->mklimit($start, $limit);
231        return $this->hlp->runSQL($sql);
232    }
233
234    public function searchwords($extern, $tlimit, $start = 0, $limit = 20) {
235        if($extern) {
236            $WHERE = "engine != 'dokuwiki'";
237            $I     = '';
238        } else {
239            $WHERE = "engine = 'dokuwiki'";
240            $I     = 'i';
241        }
242        $sql = "SELECT COUNT(*) as cnt, word, word as ${I}lookup
243                  FROM " . $this->hlp->prefix . "search as A,
244                       " . $this->hlp->prefix . "searchwords as B
245                 WHERE $tlimit
246                   AND A.id = B.sid
247                   AND $WHERE
248              GROUP BY word
249              ORDER BY cnt DESC, word" .
250            $this->mklimit($start, $limit);
251        return $this->hlp->runSQL($sql);
252    }
253
254    public function outlinks($tlimit, $start = 0, $limit = 20) {
255        $sql = "SELECT COUNT(*) as cnt, link as url
256                  FROM " . $this->hlp->prefix . "outlinks as A
257                 WHERE $tlimit
258              GROUP BY link
259              ORDER BY cnt DESC, link" .
260            $this->mklimit($start, $limit);
261        return $this->hlp->runSQL($sql);
262    }
263
264    public function pages($tlimit, $start = 0, $limit = 20) {
265        $sql = "SELECT COUNT(*) as cnt, page
266                  FROM " . $this->hlp->prefix . "access as A
267                 WHERE $tlimit
268                   AND ua_type = 'browser'
269              GROUP BY page
270              ORDER BY cnt DESC, page" .
271            $this->mklimit($start, $limit);
272        return $this->hlp->runSQL($sql);
273    }
274
275    public function edits($tlimit, $start = 0, $limit = 20) {
276        $sql = "SELECT COUNT(*) as cnt, page
277                  FROM " . $this->hlp->prefix . "edits as A
278                 WHERE $tlimit
279              GROUP BY page
280              ORDER BY cnt DESC, page" .
281            $this->mklimit($start, $limit);
282        return $this->hlp->runSQL($sql);
283    }
284
285    public function images($tlimit, $start = 0, $limit = 20) {
286        $sql = "SELECT COUNT(*) as cnt, media, SUM(size) as filesize
287                  FROM " . $this->hlp->prefix . "media as A
288                 WHERE $tlimit
289                   AND mime1 = 'image'
290              GROUP BY media
291              ORDER BY cnt DESC, media" .
292            $this->mklimit($start, $limit);
293        return $this->hlp->runSQL($sql);
294    }
295
296    public function imagessum($tlimit) {
297        $sql = "SELECT COUNT(*) as cnt, SUM(size) as filesize
298                  FROM " . $this->hlp->prefix . "media as A
299                 WHERE $tlimit
300                   AND mime1 = 'image'";
301        return $this->hlp->runSQL($sql);
302    }
303
304    public function downloads($tlimit, $start = 0, $limit = 20) {
305        $sql = "SELECT COUNT(*) as cnt, media, SUM(size) as filesize
306                  FROM " . $this->hlp->prefix . "media as A
307                 WHERE $tlimit
308                   AND mime1 != 'image'
309              GROUP BY media
310              ORDER BY cnt DESC, media" .
311            $this->mklimit($start, $limit);
312        return $this->hlp->runSQL($sql);
313    }
314
315    public function downloadssum($tlimit) {
316        $sql = "SELECT COUNT(*) as cnt, SUM(size) as filesize
317                  FROM " . $this->hlp->prefix . "media as A
318                 WHERE $tlimit
319                   AND mime1 != 'image'";
320        return $this->hlp->runSQL($sql);
321    }
322
323    public function referer($tlimit, $start = 0, $limit = 20) {
324        $sql = "SELECT COUNT(*) as cnt, ref as url
325                  FROM " . $this->hlp->prefix . "access as A
326                 WHERE $tlimit
327                   AND ua_type = 'browser'
328                   AND ref_type = 'external'
329              GROUP BY ref_md5
330              ORDER BY cnt DESC, url" .
331            $this->mklimit($start, $limit);
332        return $this->hlp->runSQL($sql);
333    }
334
335    public function newreferer($tlimit, $start = 0, $limit = 20) {
336        $sql = "SELECT COUNT(*) as cnt, ref as url
337                  FROM " . $this->hlp->prefix . "access as B,
338                       " . $this->hlp->prefix . "refseen as A
339                 WHERE $tlimit
340                   AND ua_type = 'browser'
341                   AND ref_type = 'external'
342                   AND A.ref_md5 = B.ref_md5
343              GROUP BY A.ref_md5
344              ORDER BY cnt DESC, url" .
345            $this->mklimit($start, $limit);
346        return $this->hlp->runSQL($sql);
347    }
348
349    public function countries($tlimit, $start = 0, $limit = 20) {
350        $sql = "SELECT COUNT(DISTINCT session) as cnt, B.code AS cflag, B.country
351                  FROM " . $this->hlp->prefix . "access as A,
352                       " . $this->hlp->prefix . "iplocation as B
353                 WHERE $tlimit
354                   AND A.ip = B.ip
355              GROUP BY B.code
356              ORDER BY cnt DESC, B.country" .
357            $this->mklimit($start, $limit);
358        return $this->hlp->runSQL($sql);
359    }
360
361    public function browsers($tlimit, $start = 0, $limit = 20, $ext = true) {
362        if($ext) {
363            $sel = 'ua_info as bflag, ua_info as browser, ua_ver';
364            $grp = 'ua_info, ua_ver';
365        } else {
366            $grp = 'ua_info';
367            $sel = 'ua_info';
368        }
369
370        $sql = "SELECT COUNT(DISTINCT session) as cnt, $sel
371                  FROM " . $this->hlp->prefix . "access as A
372                 WHERE $tlimit
373                   AND ua_type = 'browser'
374              GROUP BY $grp
375              ORDER BY cnt DESC, ua_info" .
376            $this->mklimit($start, $limit);
377        return $this->hlp->runSQL($sql);
378    }
379
380    public function os($tlimit, $start = 0, $limit = 20) {
381        $sql = "SELECT COUNT(DISTINCT session) as cnt, os as osflag, os
382                  FROM " . $this->hlp->prefix . "access as A
383                 WHERE $tlimit
384                   AND ua_type = 'browser'
385              GROUP BY os
386              ORDER BY cnt DESC, os" .
387            $this->mklimit($start, $limit);
388        return $this->hlp->runSQL($sql);
389    }
390
391    public function topuser($tlimit, $start = 0, $limit = 20) {
392        $sql = "SELECT COUNT(*) as cnt, user
393                  FROM " . $this->hlp->prefix . "access as A
394                 WHERE $tlimit
395                   AND ua_type = 'browser'
396                   AND user != ''
397              GROUP BY user
398              ORDER BY cnt DESC, user" .
399            $this->mklimit($start, $limit);
400        return $this->hlp->runSQL($sql);
401    }
402
403    public function topeditor($tlimit, $start = 0, $limit = 20) {
404        $sql = "SELECT COUNT(*) as cnt, user
405                  FROM " . $this->hlp->prefix . "edits as A
406                 WHERE $tlimit
407                   AND user != ''
408              GROUP BY user
409              ORDER BY cnt DESC, user" .
410            $this->mklimit($start, $limit);
411        return $this->hlp->runSQL($sql);
412    }
413
414    public function topgroup($tlimit, $start = 0, $limit = 20) {
415        $sql = "SELECT COUNT(*) as cnt, `group`
416                  FROM " . $this->hlp->prefix . "groups as A
417                 WHERE $tlimit
418                   AND `type` = 'view'
419              GROUP BY `group`
420              ORDER BY cnt DESC, `group`" .
421            $this->mklimit($start, $limit);
422        return $this->hlp->runSQL($sql);
423    }
424
425    public function topgroupedit($tlimit, $start = 0, $limit = 20) {
426        $sql = "SELECT COUNT(*) as cnt, `group`
427                  FROM " . $this->hlp->prefix . "groups as A
428                 WHERE $tlimit
429                   AND `type` = 'edit'
430              GROUP BY `group`
431              ORDER BY cnt DESC, `group`" .
432            $this->mklimit($start, $limit);
433        return $this->hlp->runSQL($sql);
434    }
435
436
437    public function resolution($tlimit, $start = 0, $limit = 20) {
438        $sql = "SELECT COUNT(DISTINCT uid) as cnt,
439                       ROUND(screen_x/100)*100 as res_x,
440                       ROUND(screen_y/100)*100 as res_y,
441                       CONCAT(ROUND(screen_x/100)*100,'x',ROUND(screen_y/100)*100) as resolution
442                  FROM " . $this->hlp->prefix . "access as A
443                 WHERE $tlimit
444                   AND ua_type  = 'browser'
445                   AND screen_x != 0
446                   AND screen_y != 0
447              GROUP BY resolution
448              ORDER BY cnt DESC" .
449            $this->mklimit($start, $limit);
450        return $this->hlp->runSQL($sql);
451    }
452
453    public function viewport($tlimit, $start = 0, $limit = 20) {
454        $sql = "SELECT COUNT(DISTINCT uid) as cnt,
455                       ROUND(view_x/100)*100 as res_x,
456                       ROUND(view_y/100)*100 as res_y,
457                       CONCAT(ROUND(view_x/100)*100,'x',ROUND(view_y/100)*100) as resolution
458                  FROM " . $this->hlp->prefix . "access as A
459                 WHERE $tlimit
460                   AND ua_type  = 'browser'
461                   AND view_x != 0
462                   AND view_y != 0
463              GROUP BY resolution
464              ORDER BY cnt DESC" .
465            $this->mklimit($start, $limit);
466
467        return $this->hlp->runSQL($sql);
468    }
469
470    public function seenusers($tlimit, $start = 0, $limit = 20) {
471        $sql = "SELECT `user`, `dt`
472                  FROM " . $this->hlp->prefix . "lastseen as A
473              ORDER BY `dt` DESC" .
474            $this->mklimit($start, $limit);
475
476        return $this->hlp->runSQL($sql);
477    }
478
479
480    /**
481     * Builds a limit clause
482     */
483    public function mklimit($start, $limit) {
484        $start = (int) $start;
485        $limit = (int) $limit;
486        if($limit) {
487            $limit += 1;
488            return " LIMIT $start,$limit";
489        } elseif($start) {
490            return " OFFSET $start";
491        }
492        return '';
493    }
494
495    /**
496     * Create a time limit for use in SQL
497     */
498    public function mktlimit(&$from, &$to) {
499        // fixme add better sanity checking here:
500        $from = preg_replace('/[^\d\-]+/', '', $from);
501        $to   = preg_replace('/[^\d\-]+/', '', $to);
502        if(!$from) $from = date('Y-m-d');
503        if(!$to) $to = date('Y-m-d');
504
505        return "A.dt >= '$from 00:00:00' AND A.dt <= '$to 23:59:59'";
506    }
507}
508