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