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