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