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