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