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