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