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