xref: /plugin/statistics/admin.php (revision 2998b1f6cbd57e9dd6b6614b5d932281bcf317c4) 
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        global $conf;
810        $bc = new DokuBrowscap($conf['cachedir']);
811        $ua = $bc->getBrowser($agent);
812
813        $type = 'browser';
814        if($ua->Crawler) $type = 'robot';
815        if($ua->isSyndicationReader) $type = 'feedreader';
816
817        $version = $ua->Version;
818        $os      = $ua->Platform;
819        return $ua->Browser;
820    }
821
822    /**
823     * Log search queries
824     */
825    function log_search($referer,&$type){
826        $referer = strtolower($referer);
827        $ref     = strtr($referer,' +','__');
828
829        include(dirname(__FILE__).'/inc/search_engines.php');
830
831        foreach($SearchEnginesSearchIDOrder as $regex){
832            if(preg_match('/'.$regex.'/',$ref)){
833                if(!$NotSearchEnginesKeys[$regex] ||
834                   !preg_match('/'.$NotSearchEnginesKeys[$regex].'/',$ref)){
835                    // it's a search engine!
836                    $type = 'search';
837                    break;
838                }
839            }
840        }
841        if($type != 'search') return; // we're done here
842
843        // extract query
844        $engine = $SearchEnginesHashID[$regex];
845        $param = $SearchEnginesKnownUrl[$engine];
846        if($param && preg_match('/'.$param.'(.*?)[&$]/',$referer,$match)){
847            $query = array_pop($match);
848        }elseif(preg_match('/'.$WordsToExtractSearchUrl.'(.*?)[&$]/',$referer,$match)){
849            $query = array_pop($match);
850        }
851        if(!$query) return; // we failed
852
853        // clean the query
854        $query = preg_replace('/^(cache|related):[^\+]+/','',$query);  // non-search queries
855        $query = preg_replace('/%0[ad]/',' ',$query);                  // LF CR
856        $query = preg_replace('/%2[02789abc]/',' ',$query);            // space " ' ( ) * + ,
857        $query = preg_replace('/%3a/',' ',$query);                     // :
858        $query = strtr($query,'+\'()"*,:','        ');                 // badly encoded
859        $query = preg_replace('/ +/',' ',$query);                      // ws compact
860        $query = trim($query);
861        $query = urldecode($query);
862        if(!utf8_check($query)) $query = utf8_encode($query);          // assume latin1 if not utf8
863        $query = utf8_strtolower($query);
864
865        // log it!
866        $page  = addslashes($_REQUEST['p']);
867        $query = addslashes($query);
868        $sql  = "INSERT INTO ".$this->getConf('db_prefix')."search
869                    SET dt       = NOW(),
870                        page     = '$page',
871                        query    = '$query',
872                        engine   = '$engine'";
873        $id = $this->runSQL($sql);
874        if(is_null($id)){
875            global $MSG;
876            print_r($MSG);
877            return;
878        }
879
880        // log single keywords
881        $words = explode(' ',utf8_stripspecials($query,' ','\._\-:\*'));
882        foreach($words as $word){
883            if(!$word) continue;
884            $word = addslashes($word);
885            $sql = "INSERT DELAYED INTO ".$this->getConf('db_prefix')."searchwords
886                       SET sid  = $id,
887                           word = '$word'";
888            $ok = $this->runSQL($sql);
889            if(is_null($ok)){
890                global $MSG;
891                print_r($MSG);
892            }
893        }
894    }
895
896    /**
897     * Resolve IP to country/city
898     */
899    function log_ip($ip){
900        // check if IP already known and up-to-date
901        $sql = "SELECT ip
902                  FROM ".$this->getConf('db_prefix')."iplocation
903                 WHERE ip ='".addslashes($ip)."'
904                   AND lastupd > DATE_SUB(CURDATE(),INTERVAL 30 DAY)";
905        $result = $this->runSQL($sql);
906        if($result[0]['ip']) return;
907
908        $http = new DokuHTTPClient();
909        $http->timeout = 10;
910        $data = $http->get('http://api.hostip.info/get_html.php?ip='.$ip);
911
912        if(preg_match('/^Country: (.*?) \((.*?)\)\nCity: (.*?)$/s',$data,$match)){
913            $country = addslashes(trim($match[1]));
914            $code    = addslashes(strtolower(trim($match[2])));
915            $city    = addslashes(trim($match[3]));
916            $host    = addslashes(gethostbyaddr($ip));
917            $ip      = addslashes($ip);
918
919            $sql = "REPLACE INTO ".$this->getConf('db_prefix')."iplocation
920                        SET ip = '$ip',
921                            country = '$country',
922                            code    = '$code',
923                            city    = '$city',
924                            host    = '$host'";
925            $this->runSQL($sql);
926        }
927    }
928
929    /**
930     * log a click on an external link
931     *
932     * called from log.php
933     */
934    function log_outgoing(){
935        if(!$_REQUEST['ol']) return;
936
937        $link_md5 = md5($link);
938        $link     = addslashes($_REQUEST['ol']);
939        $session  = addslashes(session_id());
940        $page     = addslashes($_REQUEST['p']);
941
942        $sql  = "INSERT DELAYED INTO ".$this->getConf('db_prefix')."outlinks
943                    SET dt       = NOW(),
944                        session  = '$session',
945                        page     = '$page',
946                        link_md5 = '$link_md5',
947                        link     = '$link'";
948        $ok = $this->runSQL($sql);
949        if(is_null($ok)){
950            global $MSG;
951            print_r($MSG);
952        }
953    }
954
955    /**
956     * log a page access
957     *
958     * called from log.php
959     */
960    function log_access(){
961        if(!$_REQUEST['p']) return;
962
963        # FIXME check referer against blacklist and drop logging for bad boys
964
965        // handle referer
966        $referer = trim($_REQUEST['r']);
967        if($referer){
968            $ref     = addslashes($referer);
969            $ref_md5 = ($ref) ? md5($referer) : '';
970            if(strpos($referer,DOKU_URL) === 0){
971                $ref_type = 'internal';
972            }else{
973                $ref_type = 'external';
974                $this->log_search($referer,$ref_type);
975            }
976        }else{
977            $ref      = '';
978            $ref_md5  = '';
979            $ref_type = '';
980        }
981
982        // handle user agent
983        $agent   = trim($_SERVER['HTTP_USER_AGENT']);
984
985        $ua      = addslashes($agent);
986        $ua_type = '';
987        $ua_ver  = '';
988        $os      = '';
989        $ua_info = addslashes($this->ua_info($agent,$ua_type,$ua_ver,$os));
990
991        $page    = addslashes($_REQUEST['p']);
992        $ip      = addslashes($_SERVER['REMOTE_ADDR']);
993        $sx      = (int) $_REQUEST['sx'];
994        $sy      = (int) $_REQUEST['sy'];
995        $vx      = (int) $_REQUEST['vx'];
996        $vy      = (int) $_REQUEST['vy'];
997        $js      = (int) $_REQUEST['js'];
998        $uid     = addslashes($_REQUEST['uid']);
999        $user    = addslashes($_SERVER['REMOTE_USER']);
1000        $session = addslashes(session_id());
1001        if(!$uid) $uid = $session;
1002
1003        $sql  = "INSERT DELAYED INTO ".$this->getConf('db_prefix')."access
1004                    SET dt       = NOW(),
1005                        page     = '$page',
1006                        ip       = '$ip',
1007                        ua       = '$ua',
1008                        ua_info  = '$ua_info',
1009                        ua_type  = '$ua_type',
1010                        ua_ver   = '$ua_ver',
1011                        os       = '$os',
1012                        ref      = '$ref',
1013                        ref_md5  = '$ref_md5',
1014                        ref_type = '$ref_type',
1015                        screen_x = '$sx',
1016                        screen_y = '$sy',
1017                        view_x   = '$vx',
1018                        view_y   = '$vy',
1019                        js       = '$js',
1020                        user     = '$user',
1021                        session  = '$session',
1022                        uid      = '$uid'";
1023        $ok = $this->runSQL($sql);
1024        if(is_null($ok)){
1025            global $MSG;
1026            print_r($MSG);
1027        }
1028
1029        $sql = "INSERT DELAYED IGNORE INTO ".$this->getConf('db_prefix')."refseen
1030                   SET ref_md5  = '$ref_md5',
1031                       dt       = NOW()";
1032        $ok = $this->runSQL($sql);
1033        if(is_null($ok)){
1034            global $MSG;
1035            print_r($MSG);
1036        }
1037
1038        // resolve the IP
1039        $this->log_ip($_SERVER['REMOTE_ADDR']);
1040    }
1041
1042    /**
1043     * Just send a 1x1 pixel blank gif to the browser
1044     *
1045     * @called from log.php
1046     *
1047     * @author Andreas Gohr <andi@splitbrain.org>
1048     * @author Harry Fuecks <fuecks@gmail.com>
1049     */
1050    function sendGIF(){
1051        $img = base64_decode('R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAEALAAAAAABAAEAAAIBTAA7');
1052        header('Content-Type: image/gif');
1053        header('Content-Length: '.strlen($img));
1054        header('Connection: Close');
1055        print $img;
1056        flush();
1057        // Browser should drop connection after this
1058        // Thinks it's got the whole image
1059    }
1060
1061}
1062