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