xref: /plugin/statistics/admin.php (revision c73e16f189f03bb9b4fcdad5da8027d9806f7bba) 
1<?php
2/**
3 * statistics plugin
4 *
5 * @license    GPL 2 (http://www.gnu.org/licenses/gpl.html)
6 * @author     Andreas Gohr <gohr@cosmocode.de>
7 */
8
9// must be run within Dokuwiki
10if(!defined('DOKU_INC')) die();
11
12if(!defined('DOKU_PLUGIN')) define('DOKU_PLUGIN',DOKU_INC.'lib/plugins/');
13require_once(DOKU_PLUGIN.'admin.php');
14
15/**
16 * All DokuWiki plugins to extend the admin function
17 * need to inherit from this class
18 */
19class admin_plugin_statistics extends DokuWiki_Admin_Plugin {
20    var $dblink = null;
21    var $opt    = '';
22    var $from   = '';
23    var $to     = '';
24    var $start  = '';
25    var $tlimit = '';
26
27    /**
28     * return some info
29     */
30    function getInfo(){
31        return confToHash(dirname(__FILE__).'/info.txt');
32    }
33
34    /**
35     * Access for managers allowed
36     */
37    function forAdminOnly(){
38        return false;
39    }
40
41    /**
42     * return sort order for position in admin menu
43     */
44    function getMenuSort() {
45        return 150;
46    }
47
48    /**
49     * handle user request
50     */
51    function handle() {
52        $this->opt = preg_replace('/[^a-z]+/','',$_REQUEST['opt']);
53
54        $this->start = (int) $_REQUEST['s'];
55
56        // fixme add better sanity checking here:
57        $this->from = preg_replace('/[^\d\-]+/','',$_REQUEST['f']);
58        $this->to = preg_replace('/[^\d\-]+/','',$_REQUEST['t']);
59        if(!$this->from) $this->from = date('Y-m-d');
60        if(!$this->to) $this->to     = date('Y-m-d');
61
62        //setup limit clause
63        if($this->from != $this->to){
64            $this->tlimit = "DATE(A.dt) >= DATE('".$this->from."') AND DATE(A.dt) <= DATE('".$this->to."')";
65        }else{
66            $this->tlimit = "DATE(A.dt) = DATE('".$this->from."')";
67        }
68    }
69
70    /**
71     * fixme build statistics here
72     */
73    function html() {
74        $this->html_toc();
75        echo '<h1>Access Statistics</h1>';
76        $this->html_timeselect();
77
78        switch($this->opt){
79            case 'country':
80                $this->html_country();
81                break;
82            case 'page':
83                $this->html_page();
84                break;
85            case 'browser':
86                $this->html_browser();
87                break;
88            case 'os':
89                $this->html_os();
90                break;
91            case 'referer':
92                $this->html_referer();
93                break;
94            case 'newreferer':
95                $this->html_newreferer();
96                break;
97            case 'resolution':
98                $this->html_resolution();
99                break;
100            default:
101                $this->html_dashboard();
102        }
103    }
104
105    function html_toc(){
106        echo '<div class="toc">';
107        echo '<div class="tocheader toctoggle" id="toc__header">';
108        echo 'Detailed Statistics';
109        echo '</div>';
110        echo '<div id="toc__inside">';
111        echo '<ul class="toc">';
112
113        echo '<li><div class="li">';
114        echo '<a href="?do=admin&amp;page=statistics&amp;opt=&amp;f='.$this->from.'&amp;t='.$this->to.'">Dashboard</a>';
115        echo '</div></li>';
116
117        echo '<li><div class="li">';
118        echo '<a href="?do=admin&amp;page=statistics&amp;opt=page&amp;f='.$this->from.'&amp;t='.$this->to.'">Pages</a>';
119        echo '</div></li>';
120
121        echo '<li><div class="li">';
122        echo '<a href="?do=admin&amp;page=statistics&amp;opt=referer&amp;f='.$this->from.'&amp;t='.$this->to.'">Incoming Links</a>';
123        echo '</div></li>';
124
125        echo '<li><div class="li">';
126        echo '<a href="?do=admin&amp;page=statistics&amp;opt=newreferer&amp;f='.$this->from.'&amp;t='.$this->to.'">New Incoming Links</a>';
127        echo '</div></li>';
128
129        echo '<li><div class="li">';
130        echo '<a href="?do=admin&amp;page=statistics&amp;opt=browser&amp;f='.$this->from.'&amp;t='.$this->to.'">Browsers</a>';
131        echo '</div></li>';
132
133        echo '<li><div class="li">';
134        echo '<a href="?do=admin&amp;page=statistics&amp;opt=os&amp;f='.$this->from.'&amp;t='.$this->to.'">Operating Systems</a>';
135        echo '</div></li>';
136
137        echo '<li><div class="li">';
138        echo '<a href="?do=admin&amp;page=statistics&amp;opt=country&amp;f='.$this->from.'&amp;t='.$this->to.'">Countries</a>';
139        echo '</div></li>';
140
141        echo '<li><div class="li">';
142        echo '<a href="?do=admin&amp;page=statistics&amp;opt=resolution&amp;f='.$this->from.'&amp;t='.$this->to.'">Resolution</a>';
143        echo '</div></li>';
144
145        echo '</ul>';
146        echo '</div>';
147        echo '</div>';
148    }
149
150    function html_pager($limit,$next){
151        echo '<div class="plg_stats_pager">';
152
153        if($this->start > 0){
154            $go = max($this->start - $limit, 0);
155            echo '<a href="?do=admin&amp;page=statistics&amp;opt='.$this->opt.'&amp;f='.$this->from.'&amp;t='.$this->to.'&amp;s='.$go.'" class="prev">previous page</a>';
156        }
157
158        if($next){
159            $go = $this->start + $limit;
160            echo '<a href="?do=admin&amp;page=statistics&amp;opt='.$this->opt.'&amp;f='.$this->from.'&amp;t='.$this->to.'&amp;s='.$go.'" class="next">next page</a>';
161        }
162        echo '</div>';
163    }
164
165    /**
166     * Print the time selection menu
167     */
168    function html_timeselect(){
169        $now   = date('Y-m-d');
170        $yday  = date('Y-m-d',time()-(60*60*24));
171        $week  = date('Y-m-d',time()-(60*60*24*7));
172        $month = date('Y-m-d',time()-(60*60*24*30));
173
174        echo '<div class="plg_stats_timeselect">';
175        echo '<span>Select the timeframe:</span>';
176        echo '<ul>';
177
178        echo '<li>';
179        echo '<a href="?do=admin&amp;page=statistics&amp;opt='.$this->opt.'&amp;f='.$now.'&amp;t='.$now.'">';
180        echo 'today';
181        echo '</a>';
182        echo '</li>';
183
184        echo '<li>';
185        echo '<a href="?do=admin&amp;page=statistics&amp;opt='.$this->opt.'&amp;f='.$yday.'&amp;t='.$yday.'">';
186        echo 'yesterday';
187        echo '</a>';
188        echo '</li>';
189
190        echo '<li>';
191        echo '<a href="?do=admin&amp;page=statistics&amp;opt='.$this->opt.'&amp;f='.$week.'&amp;t='.$now.'">';
192        echo 'last 7 days';
193        echo '</a>';
194        echo '</li>';
195
196        echo '<li>';
197        echo '<a href="?do=admin&amp;page=statistics&amp;opt='.$this->opt.'&amp;f='.$month.'&amp;t='.$now.'">';
198        echo 'last 30 days';
199        echo '</a>';
200        echo '</li>';
201
202        echo '</ul>';
203
204
205        echo '<form action="" method="get">';
206        echo '<input type="hidden" name="do" value="admin" />';
207        echo '<input type="hidden" name="page" value="statistics" />';
208        echo '<input type="hidden" name="opt" value="'.$this->opt.'" />';
209        echo '<input type="text" name="f" value="'.$this->from.'" class="edit" />';
210        echo '<input type="text" name="t" value="'.$this->to.'" class="edit" />';
211        echo '<input type="submit" value="go" class="button" />';
212        echo '</form>';
213
214        echo '</div>';
215    }
216
217
218    /**
219     * Print an introductionary screen
220     */
221    function html_dashboard(){
222        echo '<p>This page gives you a quick overview on what is happening in your Wiki. For detailed lists
223              choose a topic from the list.</p>';
224
225
226        echo '<div class="plg_stats_dashboard">';
227
228        // general info
229        echo '<div class="plg_stats_top">';
230        $result = $this->sql_aggregate($this->tlimit);
231        echo '<ul>';
232        echo '<li><span>'.$result['pageviews'].'</span> page views</li>';
233        echo '<li><span>'.$result['sessions'].'</span> visitors (sessions)</li>';
234        echo '<li><span>'.$result['users'].'</span> logged in users</li>';
235
236        echo '</ul>';
237        echo '<img src="'.DOKU_BASE.'lib/plugins/statistics/img.php?img=trend&amp;f='.$this->from.'&amp;t='.$this->to.'" />';
238        echo '</div>';
239
240
241        // top pages today
242        echo '<div>';
243        echo '<h2>Most popular pages</h2>';
244        $result = $this->sql_pages($this->tlimit,$this->start,15);
245        $this->html_resulttable($result);
246        echo '<a href="?do=admin&amp;page=statistics&amp;opt=page&amp;f='.$this->from.'&amp;t='.$this->to.'" class="more">more</a>';
247        echo '</div>';
248
249        // top referer today
250        echo '<div>';
251        echo '<h2>Newest incoming links</h2>';
252        $result = $this->sql_newreferer($this->tlimit,$this->start,15);
253        $this->html_resulttable($result);
254        echo '<a href="?do=admin&amp;page=statistics&amp;opt=newreferer&amp;f='.$this->from.'&amp;t='.$this->to.'" class="more">more</a>';
255        echo '</div>';
256
257        // top countries today
258        echo '<div>';
259        echo '<h2>Visitor\'s top countries</h2>';
260        echo '<img src="'.DOKU_BASE.'lib/plugins/statistics/img.php?img=country&amp;f='.$this->from.'&amp;t='.$this->to.'" />';
261        echo '<a href="?do=admin&amp;page=statistics&amp;opt=country&amp;f='.$this->from.'&amp;t='.$this->to.'" class="more">more</a>';
262        echo '</div>';
263
264        echo '</div>';
265    }
266
267    function html_country(){
268        echo '<div class="plg_stats_full">';
269        echo '<h2>Visitor\'s Countries</h2>';
270        echo '<img src="'.DOKU_BASE.'lib/plugins/statistics/img.php?img=country&amp;f='.$this->from.'&amp;t='.$this->to.'" />';
271        $result = $this->sql_countries($this->tlimit,$this->start,150);
272        $this->html_resulttable($result,'',150);
273        echo '</div>';
274    }
275
276    function html_page(){
277        echo '<div class="plg_stats_full">';
278        echo '<h2>Popular Pages</h2>';
279        $result = $this->sql_pages($this->tlimit,$this->start,150);
280        $this->html_resulttable($result,'',150);
281        echo '</div>';
282    }
283
284    function html_browser(){
285        echo '<div class="plg_stats_full">';
286        echo '<h2>Browser Shootout</h2>';
287        echo '<img src="'.DOKU_BASE.'lib/plugins/statistics/img.php?img=browser&amp;f='.$this->from.'&amp;t='.$this->to.'" />';
288        $result = $this->sql_browsers($this->tlimit,$this->start,150,true);
289        $this->html_resulttable($result,'',150);
290        echo '</div>';
291    }
292
293    function html_os(){
294        echo '<div class="plg_stats_full">';
295        echo '<h2>Operating Systems</h2>';
296        $result = $this->sql_os($this->tlimit,$this->start,150,true);
297        $this->html_resulttable($result,'',150);
298        echo '</div>';
299    }
300
301    function html_referer(){
302        echo '<div class="plg_stats_full">';
303        echo '<h2>Incoming Links</h2>';
304        $result = $this->sql_aggregate($this->tlimit);
305
306        $all    = $result['search']+$result['external']+$result['direct'];
307
308        if($all){
309            printf("<p>Of all %d external visits, %d (%.1f%%) were bookmarked (direct) accesses,
310                    %d (%.1f%%) came from search engines and %d (%.1f%%) were referred through
311                    links from other pages.</p>",$all,$result['direct'],(100*$result['direct']/$all),
312                    $result['search'],(100*$result['search']/$all),$result['external'],
313                    (100*$result['external']/$all));
314        }
315
316        $result = $this->sql_referer($this->tlimit,$this->start,150);
317        $this->html_resulttable($result,'',150);
318        echo '</div>';
319    }
320
321    function html_newreferer(){
322        echo '<div class="plg_stats_full">';
323        echo '<h2>New Incoming Links</h2>';
324        echo '<p>The following incoming links where first logged in the selected time frame,
325              and have never been seen before.</p>';
326
327        $result = $this->sql_newreferer($this->tlimit,$this->start,150);
328        $this->html_resulttable($result,'',150);
329        echo '</div>';
330    }
331
332    function html_resolution(){
333        echo '<div class="plg_stats_full">';
334        echo '<h2>Resolution</h2>';
335        $result = $this->sql_resolution($this->tlimit,$this->start,150);
336        $this->html_resulttable($result,'',150);
337
338        echo '<p>While the data above gives you some info about the resolution your visitors use, it does not tell you
339              much about about the real size of their browser windows. The graphic below shows the size distribution of
340              the view port (document area) of your visitor\'s browsers. Please note that this data can not be logged
341              in all browsers. Because users may resize their browser window while browsing your site the statistics may
342              be flawed. Take it with a grain of salt.</p>';
343
344        echo '<img src="'.DOKU_BASE.'lib/plugins/statistics/img.php?img=view&amp;f='.$this->from.'&amp;t='.$this->to.'" />';
345        echo '</div>';
346    }
347
348
349    /**
350     * Display a result in a HTML table
351     */
352    function html_resulttable($result,$header='',$pager=0){
353        echo '<table>';
354        if(is_array($header)){
355            echo '<tr>';
356            foreach($header as $h){
357                echo '<th>'.hsc($h).'</th>';
358            }
359            echo '</tr>';
360        }
361
362        $count = 0;
363        foreach($result as $row){
364            echo '<tr>';
365            foreach($row as $k => $v){
366                echo '<td class="plg_stats_X'.$k.'">';
367                if($k == 'page'){
368                    echo '<a href="'.wl($v).'" class="wikilink1">';
369                    echo hsc($v);
370                    echo '</a>';
371                }elseif($k == 'url'){
372                    $url = hsc($v);
373                    if(strlen($url) > 45){
374                        $url = substr($url,0,30).' &hellip; '.substr($url,-15);
375                    }
376                    echo '<a href="'.$v.'" class="urlextern">';
377                    echo $url;
378                    echo '</a>';
379                }elseif($k == 'browser'){
380                    include_once(dirname(__FILE__).'/inc/browsers.php');
381                    echo $BrowsersHashIDLib[$v];
382                }elseif($k == 'bflag'){
383                    include_once(dirname(__FILE__).'/inc/browsers.php');
384                    echo '<img src="'.DOKU_BASE.'lib/plugins/statistics/ico/browser/'.$BrowsersHashIcon[$v].'.png" alt="'.hsc($v).'" />';
385                }elseif($k == 'os'){
386                    if(empty($v)){
387                        echo 'unknown';
388                    }else{
389                        include_once(dirname(__FILE__).'/inc/operating_systems.php');
390                        echo $OSHashLib[$v];
391                    }
392                }elseif($k == 'osflag'){
393                    echo '<img src="'.DOKU_BASE.'lib/plugins/statistics/ico/os/'.hsc($v).'.png" alt="'.hsc($v).'" />';
394                }elseif($k == 'cflag'){
395                    echo '<img src="'.DOKU_BASE.'lib/plugins/statistics/ico/flags/'.hsc($v).'.png" alt="'.hsc($v).'" width="18" height="12" />';
396                }elseif($k == 'html'){
397                    echo $v;
398                }else{
399                    echo hsc($v);
400                }
401                echo '</td>';
402            }
403            echo '</tr>';
404
405            if($pager && ($count == $pager)) break;
406            $count++;
407        }
408        echo '</table>';
409
410        if($pager) $this->html_pager($pager,count($result) > $pager);
411    }
412
413    /**
414     * Create an image
415     */
416    function img_build($img){
417        include(dirname(__FILE__).'/inc/AGC.class.php');
418
419        switch($img){
420            case 'country':
421                // build top countries + other
422                $result = $this->sql_countries($this->tlimit,$this->start,0);
423                $data = array();
424                $top = 0;
425                foreach($result as $row){
426                    if($top < 7){
427                        $data[$row['country']] = $row['cnt'];
428                    }else{
429                        $data['other'] += $row['cnt'];
430                    }
431                    $top++;
432                }
433                $pie = new AGC(300, 200);
434                $pie->setProp("showkey",true);
435                $pie->setProp("showval",false);
436                $pie->setProp("showgrid",false);
437                $pie->setProp("type","pie");
438                $pie->setProp("keyinfo",1);
439                $pie->setProp("keysize",8);
440                $pie->setProp("keywidspc",-50);
441                $pie->setProp("key",array_keys($data));
442                $pie->addBulkPoints(array_values($data));
443                @$pie->graph();
444                $pie->showGraph();
445                break;
446            case 'browser':
447                // build top browsers + other
448                include_once(dirname(__FILE__).'/inc/browsers.php');
449
450                $result = $this->sql_browsers($this->tlimit,$this->start,0,false);
451                $data = array();
452                $top = 0;
453                foreach($result as $row){
454                    if($top < 5){
455                        $data[strip_tags($BrowsersHashIDLib[$row['ua_info']])] = $row['cnt'];
456                    }else{
457                        $data['other'] += $row['cnt'];
458                    }
459                    $top++;
460                }
461                $pie = new AGC(300, 200);
462                $pie->setProp("showkey",true);
463                $pie->setProp("showval",false);
464                $pie->setProp("showgrid",false);
465                $pie->setProp("type","pie");
466                $pie->setProp("keyinfo",1);
467                $pie->setProp("keysize",8);
468                $pie->setProp("keywidspc",-50);
469                $pie->setProp("key",array_keys($data));
470                $pie->addBulkPoints(array_values($data));
471                @$pie->graph();
472                $pie->showGraph();
473                break;
474            case 'view':
475
476                $graph = new AGC(400, 200);
477                $graph->setColor('color',0,'blue');
478                $graph->setColor('color',1,'red');
479                $graph->setProp("showkey",true);
480                $graph->setProp("key",'view port width',0);
481                $graph->setProp("key",'view port height',1);
482
483                $result = $this->sql_viewport($this->tlimit,0,0,true);
484                foreach($result as $row){
485                    $graph->addPoint($row['cnt'],$row['res_x'],0);
486                }
487
488                $result = $this->sql_viewport($this->tlimit,0,0,false);
489                foreach($result as $row){
490                    $graph->addPoint($row['cnt'],$row['res_y'],1);
491                }
492
493                @$graph->graph();
494                $graph->showGraph();
495
496                break;
497            case 'trend':
498                $hours  = ($this->from == $this->to);
499                $result = $this->sql_trend($this->tlimit,$hours);
500                $data1   = array();
501                $data2   = array();
502
503                $graph = new AGC(400, 150);
504                $graph->setProp("type","bar");
505                $graph->setProp("showgrid",false);
506                $graph->setProp("barwidth",.8);
507
508                $graph->setColor('color',0,'blue');
509                $graph->setColor('color',1,'red');
510
511                if($hours){
512                    //preset $hours
513                    for($i=0;$i<24;$i++){
514                        $data1[$i] = 0;
515                        $data2[$i] = 0;
516                        $graph->setProp("scale",array(' 0h','   4h','   8h','    12h','    16h','    20h','    24h'));
517                    }
518                }else{
519                    $graph->setProp("scale",array(next(array_keys($data1)),$this->to));
520                }
521
522                foreach($result as $row){
523                    $data1[$row['time']] = $row['pageviews'];
524                    $data2[$row['time']] = $row['sessions'];
525                }
526
527                foreach($data1 as $key => $val){
528                    $graph->addPoint($val,$key,0);
529                }
530                foreach($data2 as $key => $val){
531                    $graph->addPoint($val,$key,1);
532                }
533
534                @$graph->graph();
535                $graph->showGraph();
536
537            default:
538                $this->sendGIF();
539        }
540    }
541
542
543    /**
544     * Return some aggregated statistics
545     */
546    function sql_aggregate($tlimit){
547        $data = array();
548
549        $sql = "SELECT ref_type, COUNT(*) as cnt
550                  FROM ".$this->getConf('db_prefix')."access as A
551                 WHERE $tlimit
552                   AND ua_type = 'browser'
553              GROUP BY ref_type";
554        $result = $this->runSQL($sql);
555
556        foreach($result as $row){
557            if($row['ref_type'] == 'search')   $data['search']   = $row['cnt'];
558            if($row['ref_type'] == 'external') $data['external'] = $row['cnt'];
559            if($row['ref_type'] == 'internal') $data['internal'] = $row['cnt'];
560            if($row['ref_type'] == '')         $data['direct']   = $row['cnt'];
561        }
562
563        $sql = "SELECT COUNT(DISTINCT session) as sessions,
564                       COUNT(session) as views,
565                       COUNT(DISTINCT user) as users
566                  FROM ".$this->getConf('db_prefix')."access as A
567                 WHERE $tlimit
568                   AND ua_type = 'browser'";
569        $result = $this->runSQL($sql);
570
571        $data['users']     = max($result[0]['users'] - 1,0); // subtract empty user
572        $data['sessions']  = $result[0]['sessions'];
573        $data['pageviews'] = $result[0]['views'];
574
575        $sql = "SELECT COUNT(id) as robots
576                  FROM ".$this->getConf('db_prefix')."access as A
577                 WHERE $tlimit
578                   AND ua_type = 'robot'";
579        $result = $this->runSQL($sql);
580        $data['robots'] = $result[0]['robots'];
581
582        return $data;
583    }
584
585    /**
586     * standard statistics follow, only accesses made by browsers are counted
587     * for general stats like browser or OS only visitors not pageviews are counted
588     */
589    function sql_trend($tlimit,$hours=false){
590        if($hours){
591            $sql = "SELECT HOUR(dt) as time,
592                           COUNT(DISTINCT session) as sessions,
593                           COUNT(session) as pageviews
594                      FROM ".$this->getConf('db_prefix')."access as A
595                     WHERE $tlimit
596                       AND ua_type = 'browser'
597                  GROUP BY HOUR(dt)
598                  ORDER BY time";
599        }else{
600            $sql = "SELECT DATE(dt) as time,
601                           COUNT(DISTINCT session) as sessions,
602                           COUNT(session) as pageviews
603                      FROM ".$this->getConf('db_prefix')."access as A
604                     WHERE $tlimit
605                       AND ua_type = 'browser'
606                  GROUP BY DATE(dt)
607                  ORDER BY time";
608        }
609        return $this->runSQL($sql);
610    }
611
612    function sql_pages($tlimit,$start=0,$limit=20){
613        $sql = "SELECT COUNT(*) as cnt, page
614                  FROM ".$this->getConf('db_prefix')."access as A
615                 WHERE $tlimit
616                   AND ua_type = 'browser'
617              GROUP BY page
618              ORDER BY cnt DESC, page".
619              $this->sql_limit($start,$limit);
620        return $this->runSQL($sql);
621    }
622
623    function sql_referer($tlimit,$start=0,$limit=20){
624        $sql = "SELECT COUNT(*) as cnt, ref as url
625                  FROM ".$this->getConf('db_prefix')."access as A
626                 WHERE $tlimit
627                   AND ua_type = 'browser'
628                   AND ref_type = 'external'
629              GROUP BY ref_md5
630              ORDER BY cnt DESC, url".
631              $this->sql_limit($start,$limit);
632        return $this->runSQL($sql);
633    }
634
635    function sql_newreferer($tlimit,$start=0,$limit=20){
636        $sql = "SELECT COUNT(*) as cnt, ref as url
637                  FROM ".$this->getConf('db_prefix')."access as A
638                 WHERE ua_type = 'browser'
639                   AND ref_type = 'external'
640              GROUP BY ref_md5
641                HAVING DATE(MIN(dt)) >= DATE('".$this->from."')
642                   AND DATE(MIN(dt)) <= DATE('".$this->to."')
643              ORDER BY cnt DESC, url".
644              $this->sql_limit($start,$limit);
645        return $this->runSQL($sql);
646    }
647
648    function sql_countries($tlimit,$start=0,$limit=20){
649        $sql = "SELECT COUNT(DISTINCT session) as cnt, B.code AS cflag, B.country
650                  FROM ".$this->getConf('db_prefix')."access as A,
651                       ".$this->getConf('db_prefix')."iplocation as B
652                 WHERE $tlimit
653                   AND A.ip = B.ip
654              GROUP BY B.country
655              ORDER BY cnt DESC, B.country".
656              $this->sql_limit($start,$limit);
657        return $this->runSQL($sql);
658    }
659
660    function sql_browsers($tlimit,$start=0,$limit=20,$ext=true){
661        if($ext){
662            $sel = 'ua_info as bflag, ua_info as browser, ua_ver';
663            $grp = 'ua_info, ua_ver';
664        }else{
665            $grp = 'ua_info';
666            $sel = 'ua_info';
667        }
668
669        $sql = "SELECT COUNT(DISTINCT session) as cnt, $sel
670                  FROM ".$this->getConf('db_prefix')."access as A
671                 WHERE $tlimit
672                   AND ua_type = 'browser'
673              GROUP BY $grp
674              ORDER BY cnt DESC, ua_info".
675              $this->sql_limit($start,$limit);
676        return $this->runSQL($sql);
677    }
678
679    function sql_os($tlimit,$start=0,$limit=20){
680        $sql = "SELECT COUNT(DISTINCT session) as cnt, os as osflag, os
681                  FROM ".$this->getConf('db_prefix')."access as A
682                 WHERE $tlimit
683                   AND ua_type = 'browser'
684              GROUP BY os
685              ORDER BY cnt DESC, os".
686              $this->sql_limit($start,$limit);
687        return $this->runSQL($sql);
688    }
689
690    function sql_resolution($tlimit,$start=0,$limit=20){
691        $sql = "SELECT COUNT(DISTINCT session) as cnt, CONCAT(screen_x,'x',screen_y) as res
692                  FROM ".$this->getConf('db_prefix')."access as A
693                 WHERE $tlimit
694                   AND ua_type  = 'browser'
695                   AND screen_x != 0
696              GROUP BY screen_x, screen_y
697              ORDER BY cnt DESC, screen_x".
698              $this->sql_limit($start,$limit);
699        return $this->runSQL($sql);
700    }
701
702    function sql_viewport($tlimit,$start=0,$limit=20,$x=true){
703        if($x){
704            $col = 'view_x';
705            $res = 'res_x';
706        }else{
707            $col = 'view_y';
708            $res = 'res_y';
709        }
710
711        $sql = "SELECT COUNT(*) as cnt,
712                       ROUND($col/10)*10 as $res
713                  FROM ".$this->getConf('db_prefix')."access as A
714                 WHERE $tlimit
715                   AND ua_type  = 'browser'
716                   AND $col != 0
717              GROUP BY $res
718              ORDER BY cnt DESC, $res".
719              $this->sql_limit($start,$limit);
720        return $this->runSQL($sql);
721    }
722
723
724    /**
725     * Builds a limit clause
726     */
727    function sql_limit($start,$limit){
728        $start = (int) $start;
729        $limit = (int) $limit;
730        if($limit){
731            $limit += 1;
732            return " LIMIT $start,$limit";
733        }elseif($start){
734            return " OFFSET $start";
735        }
736        return '';
737    }
738
739    /**
740     * Return a link to the DB, opening the connection if needed
741     */
742    function dbLink(){
743        // connect to DB if needed
744        if(!$this->dblink){
745            $this->dblink = mysql_connect($this->getConf('db_server'),
746                                          $this->getConf('db_user'),
747                                          $this->getConf('db_password'));
748            if(!$this->dblink){
749                msg('DB Error: connection failed',-1);
750                return null;
751            }
752            // set utf-8
753            if(!mysql_db_query($this->getConf('db_database'),'set names utf8',$this->dblink)){
754                msg('DB Error: could not set UTF-8 ('.mysql_error($this->dblink).')',-1);
755                return null;
756            }
757        }
758        return $this->dblink;
759    }
760
761    /**
762     * Simple function to run a DB query
763     */
764    function runSQL($sql_string) {
765        $link = $this->dbLink();
766
767        $result = mysql_db_query($this->conf['db_database'],$sql_string,$link);
768        if(!$result){
769            msg('DB Error: '.mysql_error($link).' '.hsc($sql_string),-1);
770            return null;
771        }
772
773        $resultarray = array();
774
775        //mysql_db_query returns 1 on a insert statement -> no need to ask for results
776        if ($result != 1) {
777            for($i=0; $i< mysql_num_rows($result); $i++) {
778                $temparray = mysql_fetch_assoc($result);
779                $resultarray[]=$temparray;
780            }
781            mysql_free_result($result);
782        }
783
784        if (mysql_insert_id($link)) {
785            $resultarray = mysql_insert_id($link); //give back ID on insert
786        }
787
788        return $resultarray;
789    }
790
791    /**
792     * Returns a short name for a User Agent and sets type, version and os info
793     */
794    function ua_info($ua,&$type,&$ver,&$os){
795        $ua = strtr($ua,' +','__');
796        $ua = strtolower($ua);
797
798        // common browsers
799        $regvermsie     = '/msie([+_ ]|)([\d\.]*)/i';
800        $regvernetscape = '/netscape.?\/([\d\.]*)/i';
801        $regverfirefox  = '/firefox\/([\d\.]*)/i';
802        $regversvn      = '/svn\/([\d\.]*)/i';
803        $regvermozilla  = '/mozilla(\/|)([\d\.]*)/i';
804        $regnotie       = '/webtv|omniweb|opera/i';
805        $regnotnetscape = '/gecko|compatible|opera|galeon|safari/i';
806
807        $name = '';
808        # IE ?
809        if(preg_match($regvermsie,$ua,$m) && !preg_match($regnotie,$ua)){
810            $type = 'browser';
811            $ver  = $m[2];
812            $name = 'msie';
813        }
814        # Firefox ?
815        elseif (preg_match($regverfirefox,$ua,$m)){
816            $type = 'browser';
817            $ver  = $m[1];
818            $name = 'firefox';
819        }
820        # Subversion ?
821        elseif (preg_match($regversvn,$ua,$m)){
822            $type = 'rcs';
823            $ver  = $m[1];
824            $name = 'svn';
825        }
826        # Netscape 6.x, 7.x ... ?
827        elseif (preg_match($regvernetscape,$ua,$m)){
828            $type = 'browser';
829            $ver  = $m[1];
830            $name = 'netscape';
831        }
832        # Netscape 3.x, 4.x ... ?
833        elseif(preg_match($regvermozilla,$ua,$m) && !preg_match($regnotnetscape,$ua)){
834            $type = 'browser';
835            $ver  = $m[2];
836            $name = 'netscape';
837        }else{
838            include(dirname(__FILE__).'/inc/browsers.php');
839            foreach($BrowsersSearchIDOrder as $regex){
840                if(preg_match('/'.$regex.'/',$ua)){
841                    // it's a browser!
842                    $type = 'browser';
843                    $name = strtolower($regex);
844                    break;
845                }
846            }
847        }
848
849        // check versions for Safari and Opera
850        if($name == 'safari'){
851            if(preg_match('/safari\/([\d\.]*)/i',$ua,$match)){
852                $ver = $BrowsersSafariBuildToVersionHash[$match[1]];
853            }
854        }elseif($name == 'opera'){
855            if(preg_match('/opera[\/ ]([\d\.]*)/i',$ua,$match)){
856                $ver = $match[1];
857            }
858        }
859
860
861        // check OS for browsers
862        if($type == 'browser'){
863            include(dirname(__FILE__).'/inc/operating_systems.php');
864            foreach($OSSearchIDOrder as $regex){
865                if(preg_match('/'.$regex.'/',$ua)){
866                    $os = $OSHashID[$regex];
867                    break;
868                }
869            }
870
871        }
872
873        // are we done now?
874        if($name) return $name;
875
876        include(dirname(__FILE__).'/inc/robots.php');
877        foreach($RobotsSearchIDOrder as $regex){
878            if(preg_match('/'.$regex.'/',$ua)){
879                    // it's a robot!
880                    $type = 'robot';
881                    return strtolower($regex);
882            }
883        }
884
885        // dunno
886        return '';
887    }
888
889    /**
890     *
891     * @fixme: put search engine queries in seperate table here
892     */
893    function log_search($referer,&$type){
894        $referer = strtr($referer,' +','__');
895        $referer = strtolower($referer);
896
897        include(dirname(__FILE__).'/inc/search_engines.php');
898
899        foreach($SearchEnginesSearchIDOrder as $regex){
900            if(preg_match('/'.$regex.'/',$referer)){
901                if(!$NotSearchEnginesKeys[$regex] ||
902                   !preg_match('/'.$NotSearchEnginesKeys[$regex].'/',$referer)){
903                    // it's a search engine!
904                    $type = 'search';
905                    break;
906                }
907            }
908        }
909        if($type != 'search') return; // we're done here
910
911        #fixme now do the keyword magic!
912    }
913
914    /**
915     * Resolve IP to country/city
916     */
917    function log_ip($ip){
918        // check if IP already known and up-to-date
919        $sql = "SELECT ip
920                  FROM ".$this->getConf('db_prefix')."iplocation
921                 WHERE ip ='".addslashes($ip)."'
922                   AND lastupd > DATE_SUB(CURDATE(),INTERVAL 30 DAY)";
923        $result = $this->runSQL($sql);
924        if($result[0]['ip']) return;
925
926        $http = new DokuHTTPClient();
927        $http->timeout = 10;
928        $data = $http->get('http://api.hostip.info/get_html.php?ip='.$ip);
929
930        if(preg_match('/^Country: (.*?) \((.*?)\)\nCity: (.*?)$/s',$data,$match)){
931            $country = addslashes(trim($match[1]));
932            $code    = addslashes(strtolower(trim($match[2])));
933            $city    = addslashes(trim($match[3]));
934            $host    = addslashes(gethostbyaddr($ip));
935            $ip      = addslashes($ip);
936
937            $sql = "REPLACE INTO ".$this->getConf('db_prefix')."iplocation
938                        SET ip = '$ip',
939                            country = '$country',
940                            code    = '$code',
941                            city    = '$city',
942                            host    = '$host'";
943            $this->runSQL($sql);
944        }
945    }
946
947    /**
948     * log a page access
949     *
950     * called from log.php
951     */
952    function log_access(){
953        if(!$_REQUEST['p']) return;
954
955        # FIXME check referer against blacklist and drop logging for bad boys
956
957        // handle referer
958        $referer = trim($_REQUEST['r']);
959        if($referer){
960            $ref     = addslashes($referer);
961            $ref_md5 = ($ref) ? md5($referer) : '';
962            if(strpos($referer,DOKU_URL) === 0){
963                $ref_type = 'internal';
964            }else{
965                $ref_type = 'external';
966                $this->log_search($referer,$ref_type);
967            }
968        }else{
969            $ref      = '';
970            $ref_md5  = '';
971            $ref_type = '';
972        }
973
974        // handle user agent
975        $agent   = trim($_SERVER['HTTP_USER_AGENT']);
976
977        $ua      = addslashes($agent);
978        $ua_type = '';
979        $ua_ver  = '';
980        $os      = '';
981        $ua_info = addslashes($this->ua_info($agent,$ua_type,$ua_ver,$os));
982
983        $page    = addslashes($_REQUEST['p']);
984        $ip      = addslashes($_SERVER['REMOTE_ADDR']);
985        $sx      = (int) $_REQUEST['sx'];
986        $sy      = (int) $_REQUEST['sy'];
987        $vx      = (int) $_REQUEST['vx'];
988        $vy      = (int) $_REQUEST['vy'];
989        $js      = (int) $_REQUEST['js'];
990        $user    = addslashes($_SERVER['REMOTE_USER']);
991        $session = addslashes(session_id());
992
993        $sql  = "INSERT DELAYED INTO ".$this->getConf('db_prefix')."access
994                    SET dt       = NOW(),
995                        page     = '$page',
996                        ip       = '$ip',
997                        ua       = '$ua',
998                        ua_info  = '$ua_info',
999                        ua_type  = '$ua_type',
1000                        ua_ver   = '$ua_ver',
1001                        os       = '$os',
1002                        ref      = '$ref',
1003                        ref_md5  = '$ref_md5',
1004                        ref_type = '$ref_type',
1005                        screen_x = '$sx',
1006                        screen_y = '$sy',
1007                        view_x   = '$vx',
1008                        view_y   = '$vy',
1009                        js       = '$js',
1010                        user     = '$user',
1011                        session  = '$session'";
1012        $ok = $this->runSQL($sql);
1013        if(is_null($ok)){
1014            global $MSG;
1015            print_r($MSG);
1016        }
1017
1018        // resolve the IP
1019        $this->log_ip($_SERVER['REMOTE_ADDR']);
1020    }
1021
1022    /**
1023     * Just send a 1x1 pixel blank gif to the browser
1024     *
1025     * @called from log.php
1026     *
1027     * @author Andreas Gohr <andi@splitbrain.org>
1028     * @author Harry Fuecks <fuecks@gmail.com>
1029     */
1030    function sendGIF(){
1031        $img = base64_decode('R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAEALAAAAAABAAEAAAIBTAA7');
1032        header('Content-Type: image/gif');
1033        header('Content-Length: '.strlen($img));
1034        header('Connection: Close');
1035        print $img;
1036        flush();
1037        // Browser should drop connection after this
1038        // Thinks it's got the whole image
1039    }
1040
1041}
1042