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