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