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