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