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