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