1<?php 2 3namespace dokuwiki\plugin\statistics; 4 5use dokuwiki\plugin\sqlite\SQLiteDB; 6use helper_plugin_statistics; 7 8/** 9 * This class defines a bunch of SQL queries to fetch various statistics from the database 10 */ 11class Query 12{ 13 protected helper_plugin_statistics $hlp; 14 protected SQLiteDB $db; 15 protected string $from; 16 protected string $to; 17 protected string $limit = ''; 18 19 /** 20 * @param helper_plugin_statistics $hlp 21 */ 22 public function __construct(helper_plugin_statistics $hlp) 23 { 24 $this->hlp = $hlp; 25 $this->db = $hlp->getDB(); 26 $today = date('Y-m-d'); 27 $this->setTimeFrame($today, $today); 28 $this->setPagination(0, 20); 29 } 30 31 /** 32 * Set the time frame for all queries 33 * 34 * @param string $from The start date as YYYY-MM-DD 35 * @param string $to The end date as YYYY-MM-DD 36 */ 37 public function setTimeFrame(string $from, string $to): void 38 { 39 try { 40 $from = new \DateTime($from); 41 $to = new \DateTime($to); 42 } catch (\Exception $e) { 43 $from = new \DateTime(); 44 $to = new \DateTime(); 45 } 46 $from->setTime(0, 0); 47 $to->setTime(23, 59, 59); 48 49 $this->from = $from->format('Y-m-d H:i:s'); 50 $this->to = $to->format('Y-m-d H:i:s'); 51 } 52 53 /** 54 * Set the pagination settings for some queries 55 * 56 * @param int $start The start offset 57 * @param int $limit The number of results. If one more is returned, there is another page 58 * @return void 59 */ 60 public function setPagination(int $start, int $limit) 61 { 62 // when a limit is set, one more is fetched to indicate when a next page exists 63 if ($limit) $limit += 1; 64 65 if ($limit) { 66 $this->limit = " LIMIT $start,$limit"; 67 } elseif ($start) { 68 $this->limit = " OFFSET $start"; 69 } 70 } 71 72 /** 73 * Return some aggregated statistics 74 */ 75 public function aggregate(): array 76 { 77 // init some values that might not be set 78 $data = [ 79 'referers' => 0, // total number of (external) referrers 80 'external' => 0, // external referrers 81 'search' => 0, // search engine referrers 82 'direct' => 0, // direct referrers 83 'internal' => 0, // internal referrers 84 'bouncerate' => 0, 85 'newvisitors' => 0, 86 ]; 87 88 // Count referrer types by joining with referers table 89 $sql = "SELECT 90 CASE 91 WHEN R.engine IS NOT NULL THEN 'search' 92 WHEN R.url = '' THEN 'direct' 93 WHEN R.url IS NOT NULL THEN 'external' 94 ELSE 'internal' 95 END as ref_type, 96 COUNT(*) as cnt 97 FROM pageviews as P 98 LEFT JOIN referers as R ON P.ref_id = R.id 99 LEFT JOIN sessions as S ON P.session = S.session 100 WHERE P.dt >= ? AND P.dt <= ? 101 AND S.ua_type = ? 102 GROUP BY ref_type"; 103 $result = $this->db->queryAll($sql, [$this->from, $this->to, 'browser']); 104 105 foreach ($result as $row) { 106 if ($row['ref_type'] == 'search') { 107 $data['search'] = $row['cnt']; 108 $data['referers'] += $row['cnt']; 109 } 110 if ($row['ref_type'] == 'direct') { 111 $data['direct'] = $row['cnt']; 112 $data['referers'] += $row['cnt']; 113 } 114 if ($row['ref_type'] == 'external') { 115 $data['external'] = $row['cnt']; 116 $data['referers'] += $row['cnt']; 117 } 118 if ($row['ref_type'] == 'internal') { 119 $data['internal'] = $row['cnt']; 120 } 121 } 122 123 // general user and session info 124 $sql = "SELECT COUNT(DISTINCT P.session) as sessions, 125 COUNT(P.session) as views, 126 COUNT(DISTINCT S.user) as users, 127 COUNT(DISTINCT S.uid) as visitors 128 FROM pageviews as P 129 LEFT JOIN sessions as S ON P.session = S.session 130 WHERE P.dt >= ? AND P.dt <= ? 131 AND S.ua_type = ?"; 132 $result = $this->db->queryRecord($sql, [$this->from, $this->to, 'browser']); 133 134 $data['users'] = $result['users']; 135 $data['sessions'] = $result['sessions']; 136 $data['pageviews'] = $result['views']; 137 $data['visitors'] = $result['visitors']; 138 139 // calculate bounce rate (sessions with only 1 page view) 140 if ($data['sessions']) { 141 $sql = "SELECT COUNT(*) as cnt 142 FROM ( 143 SELECT P.session, COUNT(*) as views 144 FROM pageviews as P 145 LEFT JOIN sessions as S ON P.session = S.session 146 WHERE P.dt >= ? AND P.dt <= ? 147 AND S.ua_type = ? 148 GROUP BY P.session 149 HAVING views = 1 150 )"; 151 $count = $this->db->queryValue($sql, [$this->from, $this->to, 'browser']); 152 $data['bouncerate'] = $count * 100 / $data['sessions']; 153 $data['newvisitors'] = $count * 100 / $data['sessions']; 154 } 155 156 // calculate avg. number of views per session 157 $sql = "SELECT AVG(views) as cnt 158 FROM ( 159 SELECT P.session, COUNT(*) as views 160 FROM pageviews as P 161 LEFT JOIN sessions as S ON P.session = S.session 162 WHERE P.dt >= ? AND P.dt <= ? 163 AND S.ua_type = ? 164 GROUP BY P.session 165 )"; 166 $data['avgpages'] = $this->db->queryValue($sql, [$this->from, $this->to, 'browser']); 167 168 // average time spent on the site 169 $sql = "SELECT AVG((julianday(end) - julianday(dt)) * 24 * 60) as time 170 FROM sessions as S 171 WHERE S.dt >= ? AND S.dt <= ? 172 AND S.dt != S.end 173 AND DATE(S.dt) = DATE(S.end) 174 AND S.ua_type = ?"; 175 $data['timespent'] = $this->db->queryValue($sql, [$this->from, $this->to, 'browser']); 176 177 // logins 178 $sql = "SELECT COUNT(*) as logins 179 FROM logins as A 180 WHERE A.dt >= ? AND A.dt <= ? 181 AND (type = ? OR type = ?)"; 182 $data['logins'] = $this->db->queryValue($sql, [$this->from, $this->to, 'l', 'p']); 183 184 // registrations 185 $sql = "SELECT COUNT(*) as registrations 186 FROM logins as A 187 WHERE A.dt >= ? AND A.dt <= ? 188 AND type = ?"; 189 $data['registrations'] = $this->db->queryValue($sql, [$this->from, $this->to, 'C']); 190 191 // current users (based on recent sessions) 192 $sql = "SELECT COUNT(DISTINCT uid) as current 193 FROM sessions 194 WHERE end >= datetime('now', '-10 minutes')"; 195 $data['current'] = $this->db->queryValue($sql); 196 197 return $data; 198 } 199 200 201 /** 202 * Return some trend data about visits and edits in the wiki 203 * 204 * @param bool $hours Use hour resolution rather than days 205 * @return array 206 */ 207 public function dashboardviews(bool $hours = false): array 208 { 209 if ($hours) { 210 $TIME = 'strftime(\'%H\', P.dt)'; 211 } else { 212 $TIME = 'DATE(P.dt)'; 213 } 214 215 $data = []; 216 217 // access trends 218 $sql = "SELECT $TIME as time, 219 COUNT(DISTINCT P.session) as sessions, 220 COUNT(P.session) as pageviews, 221 COUNT(DISTINCT S.uid) as visitors 222 FROM pageviews as P 223 LEFT JOIN sessions as S ON P.session = S.session 224 WHERE P.dt >= ? AND P.dt <= ? 225 AND S.ua_type = ? 226 GROUP BY $TIME 227 ORDER BY time"; 228 $result = $this->db->queryAll($sql, [$this->from, $this->to, 'browser']); 229 foreach ($result as $row) { 230 $data[$row['time']]['sessions'] = $row['sessions']; 231 $data[$row['time']]['pageviews'] = $row['pageviews']; 232 $data[$row['time']]['visitors'] = $row['visitors']; 233 } 234 return $data; 235 } 236 237 /** 238 * @param bool $hours Use hour resolution rather than days 239 * @return array 240 */ 241 public function dashboardwiki(bool $hours = false): array 242 { 243 if ($hours) { 244 $TIME = 'strftime(\'%H\', dt)'; 245 } else { 246 $TIME = 'DATE(dt)'; 247 } 248 249 $data = []; 250 251 // edit trends 252 foreach (['E', 'C', 'D'] as $type) { 253 $sql = "SELECT $TIME as time, 254 COUNT(*) as cnt 255 FROM edits as A 256 WHERE A.dt >= ? AND A.dt <= ? 257 AND type = ? 258 GROUP BY $TIME 259 ORDER BY time"; 260 $result = $this->db->queryAll($sql, [$this->from, $this->to, $type]); 261 foreach ($result as $row) { 262 $data[$row['time']][$type] = $row['cnt']; 263 } 264 } 265 ksort($data); 266 return $data; 267 } 268 269 /** 270 * @param string $info Which type of history to select (FIXME which ones are there?) 271 * @param string $interval Group data by this interval (days, weeks, months) 272 * @return array 273 */ 274 public function history(string $info, string $interval = 'day'): array 275 { 276 if ($interval == 'weeks') { 277 $TIME = 'strftime(\'%Y\', dt), strftime(\'%W\', dt)'; 278 } elseif ($interval == 'months') { 279 $TIME = 'strftime(\'%Y-%m\', dt)'; 280 } else { 281 $TIME = 'strftime(\'%d-%m\', dt)'; 282 } 283 284 $mod = 1; 285 if ($info == 'media_size' || $info == 'page_size') { 286 $mod = 1024 * 1024; 287 } 288 289 $sql = "SELECT $TIME as time, 290 AVG(value)/$mod as cnt 291 FROM history as A 292 WHERE A.dt >= ? AND A.dt <= ? 293 AND info = ? 294 GROUP BY $TIME 295 ORDER BY $TIME"; 296 return $this->db->queryAll($sql, [$this->from, $this->to, $info]); 297 } 298 299 /** 300 * @return array 301 */ 302 public function searchengines(): array 303 { 304 $sql = "SELECT COUNT(*) as cnt, R.engine 305 FROM pageviews as P, 306 referers as R 307 WHERE P.dt >= ? AND P.dt <= ? 308 AND P.ref_id = R.id 309 AND R.engine != '' 310 GROUP BY R.engine 311 ORDER BY cnt DESC, R.engine" . 312 $this->limit; 313 return $this->db->queryAll($sql, [$this->from, $this->to]); 314 } 315 316 /** 317 * @return array 318 */ 319 public function searchphrases(): array 320 { 321 $sql = "SELECT COUNT(*) as cnt, query, query as ilookup 322 FROM search 323 WHERE dt >= ? AND dt <= ? 324 GROUP BY query 325 ORDER BY cnt DESC, query" . 326 $this->limit; 327 return $this->db->queryAll($sql, [$this->from, $this->to]); 328 } 329 330 /** 331 * @return array 332 */ 333 public function searchwords(): array 334 { 335 $sql = "SELECT COUNT(*) as cnt, SW.word, SW.word as ilookup 336 FROM search as S, 337 searchwords as SW 338 WHERE S.dt >= ? AND S.dt <= ? 339 AND S.id = SW.sid 340 GROUP BY SW.word 341 ORDER BY cnt DESC, SW.word" . 342 $this->limit; 343 return $this->db->queryAll($sql, [$this->from, $this->to]); 344 } 345 346 /** 347 * @return array 348 */ 349 public function outlinks(): array 350 { 351 $sql = "SELECT COUNT(*) as cnt, link as url 352 FROM outlinks as A 353 WHERE A.dt >= ? AND A.dt <= ? 354 GROUP BY link 355 ORDER BY cnt DESC, link" . 356 $this->limit; 357 return $this->db->queryAll($sql, [$this->from, $this->to]); 358 } 359 360 /** 361 * @return array 362 */ 363 public function pages(): array 364 { 365 $sql = "SELECT COUNT(*) as cnt, P.page 366 FROM pageviews as P, 367 sessions as S 368 WHERE P.dt >= ? AND P.dt <= ? 369 AND P.session = S.session 370 AND S.ua_type = ? 371 GROUP BY P.page 372 ORDER BY cnt DESC, P.page" . 373 $this->limit; 374 return $this->db->queryAll($sql, [$this->from, $this->to, 'browser']); 375 } 376 377 /** 378 * @return array 379 */ 380 public function edits(): array 381 { 382 $sql = "SELECT COUNT(*) as cnt, page 383 FROM edits as A 384 WHERE A.dt >= ? AND A.dt <= ? 385 GROUP BY page 386 ORDER BY cnt DESC, page" . 387 $this->limit; 388 return $this->db->queryAll($sql, [$this->from, $this->to]); 389 } 390 391 /** 392 * @return array 393 */ 394 public function images(): array 395 { 396 $sql = "SELECT COUNT(*) as cnt, media, SUM(size) as filesize 397 FROM media as A 398 WHERE A.dt >= ? AND A.dt <= ? 399 AND mime1 = ? 400 GROUP BY media 401 ORDER BY cnt DESC, media" . 402 $this->limit; 403 return $this->db->queryAll($sql, [$this->from, $this->to, 'image']); 404 } 405 406 /** 407 * @return array 408 */ 409 public function imagessum(): array 410 { 411 $sql = "SELECT COUNT(*) as cnt, SUM(size) as filesize 412 FROM media as A 413 WHERE A.dt >= ? AND A.dt <= ? 414 AND mime1 = ?"; 415 return $this->db->queryAll($sql, [$this->from, $this->to, 'image']); 416 } 417 418 /** 419 * @return array 420 */ 421 public function downloads(): array 422 { 423 $sql = "SELECT COUNT(*) as cnt, media, SUM(size) as filesize 424 FROM media as A 425 WHERE A.dt >= ? AND A.dt <= ? 426 AND mime1 != ? 427 GROUP BY media 428 ORDER BY cnt DESC, media" . 429 $this->limit; 430 return $this->db->queryAll($sql, [$this->from, $this->to, 'image']); 431 } 432 433 /** 434 * @return array 435 */ 436 public function downloadssum(): array 437 { 438 $sql = "SELECT COUNT(*) as cnt, SUM(size) as filesize 439 FROM media as A 440 WHERE A.dt >= ? AND A.dt <= ? 441 AND mime1 != ?"; 442 return $this->db->queryAll($sql, [$this->from, $this->to, 'image']); 443 } 444 445 /** 446 * @return array 447 */ 448 public function referer(): array 449 { 450 $sql = "SELECT COUNT(*) as cnt, R.url 451 FROM pageviews as P 452 LEFT JOIN sessions as S ON P.session = S.session 453 LEFT JOIN referers as R ON P.ref_id = R.id 454 WHERE P.dt >= ? AND P.dt <= ? 455 AND S.ua_type = ? 456 AND R.url IS NOT NULL 457 AND R.url != '' 458 AND R.engine IS NULL 459 GROUP BY R.url 460 ORDER BY cnt DESC, R.url" . 461 $this->limit; 462 return $this->db->queryAll($sql, [$this->from, $this->to, 'browser']); 463 } 464 465 /** 466 * @return array 467 */ 468 public function newreferer(): array 469 { 470 $sql = "SELECT COUNT(*) as cnt, R.url 471 FROM pageviews as P 472 LEFT JOIN sessions as S ON P.session = S.session 473 LEFT JOIN referers as R ON P.ref_id = R.id 474 WHERE P.dt >= ? AND P.dt <= ? 475 AND S.ua_type = ? 476 AND R.url IS NOT NULL 477 AND R.url != '' 478 AND R.engine IS NULL 479 AND R.dt >= ? 480 GROUP BY R.url 481 ORDER BY cnt DESC, R.url" . 482 $this->limit; 483 return $this->db->queryAll($sql, [$this->from, $this->to, 'browser', $this->from]); 484 } 485 486 /** 487 * @return array 488 */ 489 public function countries(): array 490 { 491 $sql = "SELECT COUNT(DISTINCT P.session) as cnt, I.country 492 FROM pageviews as P, 493 iplocation as I 494 WHERE P.dt >= ? AND P.dt <= ? 495 AND P.ip = I.ip 496 AND I.country != '' 497 GROUP BY I.code 498 ORDER BY cnt DESC, I.country" . 499 $this->limit; 500 return $this->db->queryAll($sql, [$this->from, $this->to]); 501 } 502 503 /** 504 * @param bool $ext return extended information 505 * @return array 506 */ 507 public function browsers(bool $ext = false): array 508 { 509 if ($ext) { 510 $sel = 'S.ua_info as browser, S.ua_ver'; 511 $grp = 'S.ua_info, S.ua_ver'; 512 } else { 513 $sel = 'S.ua_info as browser'; 514 $grp = 'S.ua_info'; 515 } 516 517 $sql = "SELECT COUNT(DISTINCT S.session) as cnt, $sel 518 FROM sessions as S 519 WHERE S.dt >= ? AND S.dt <= ? 520 AND S.ua_type = ? 521 GROUP BY $grp 522 ORDER BY cnt DESC, S.ua_info" . 523 $this->limit; 524 return $this->db->queryAll($sql, [$this->from, $this->to, 'browser']); 525 } 526 527 /** 528 * @return array 529 */ 530 public function os(): array 531 { 532 $sql = "SELECT COUNT(DISTINCT S.session) as cnt, S.os 533 FROM sessions as S 534 WHERE S.dt >= ? AND S.dt <= ? 535 AND S.ua_type = ? 536 GROUP BY S.os 537 ORDER BY cnt DESC, S.os" . 538 $this->limit; 539 return $this->db->queryAll($sql, [$this->from, $this->to, 'browser']); 540 } 541 542 /** 543 * @return array 544 */ 545 public function topdomain(): array 546 { 547 $sql = "SELECT COUNT(*) as cnt, U.domain 548 FROM pageviews as P, 549 sessions as S, 550 users as U 551 WHERE P.dt >= ? AND P.dt <= ? 552 AND P.session = S.session 553 AND S.user = U.user 554 AND S.ua_type = ? 555 AND S.user IS NOT NULL 556 AND S.user != ? 557 GROUP BY U.domain 558 ORDER BY cnt DESC, U.domain" . 559 $this->limit; 560 return $this->db->queryAll($sql, [$this->from, $this->to, 'browser', '']); 561 } 562 563 /** 564 * @return array 565 */ 566 public function topuser(): array 567 { 568 $sql = "SELECT COUNT(*) as cnt, S.user 569 FROM pageviews as P, 570 sessions as S 571 WHERE P.dt >= ? AND P.dt <= ? 572 AND P.session = S.session 573 AND S.ua_type = ? 574 AND S.user != ? 575 GROUP BY S.user 576 ORDER BY cnt DESC, S.user" . 577 $this->limit; 578 return $this->db->queryAll($sql, [$this->from, $this->to, 'browser', '']); 579 } 580 581 /** 582 * @return array 583 */ 584 public function topeditor(): array 585 { 586 $sql = "SELECT COUNT(*) as cnt, user 587 FROM edits as E, 588 sessions as S 589 WHERE E.dt >= ? AND E.dt <= ? 590 AND E.session = S.session 591 AND S.user != ? 592 GROUP BY user 593 ORDER BY cnt DESC, user" . 594 $this->limit; 595 return $this->db->queryAll($sql, [$this->from, $this->to, '']); 596 } 597 598 /** 599 * @return array 600 */ 601 public function topgroup(): array 602 { 603 $sql = "SELECT COUNT(*) as cnt, G.`group` 604 FROM pageviews as P, 605 sessions as S, 606 groups as G 607 WHERE P.dt >= ? AND P.dt <= ? 608 AND P.session = S.session 609 AND S.user = G.user 610 AND S.ua_type = ? 611 GROUP BY G.`group` 612 ORDER BY cnt DESC, G.`group`" . 613 $this->limit; 614 return $this->db->queryAll($sql, [$this->from, $this->to, 'browser']); 615 } 616 617 /** 618 * @return array 619 */ 620 public function topgroupedit(): array 621 { 622 $sql = "SELECT COUNT(*) as cnt, G.`group` 623 FROM edits as E, 624 sessions as S, 625 groups as G 626 WHERE E.dt >= ? AND E.dt <= ? 627 AND E.session = S.session 628 AND S.user = G.user 629 GROUP BY G.`group` 630 ORDER BY cnt DESC, G.`group`" . 631 $this->limit; 632 return $this->db->queryAll($sql, [$this->from, $this->to]); 633 } 634 635 636 /** 637 * @return array 638 */ 639 public function resolution(): array 640 { 641 $sql = "SELECT COUNT(DISTINCT S.uid) as cnt, 642 ROUND(P.screen_x/100)*100 as res_x, 643 ROUND(P.screen_y/100)*100 as res_y, 644 CAST(ROUND(P.screen_x/100)*100 AS int) || 'x' || CAST(ROUND(P.screen_y/100)*100 AS int) as resolution 645 FROM pageviews as P, 646 sessions as S 647 WHERE P.dt >= ? AND P.dt <= ? 648 AND P.session = S.session 649 AND S.ua_type = ? 650 AND P.screen_x != ? 651 AND P.screen_y != ? 652 GROUP BY resolution 653 ORDER BY cnt DESC" . 654 $this->limit; 655 return $this->db->queryAll($sql, [$this->from, $this->to, 'browser', 0, 0]); 656 } 657 658 /** 659 * @return array 660 */ 661 public function viewport(): array 662 { 663 $sql = "SELECT COUNT(DISTINCT S.uid) as cnt, 664 ROUND(P.view_x/100)*100 as res_x, 665 ROUND(P.view_y/100)*100 as res_y, 666 CAST(ROUND(P.view_x/100)*100 AS int) || 'x' || CAST(ROUND(P.view_y/100)*100 AS int) as resolution 667 FROM pageviews as P, 668 sessions as S 669 WHERE P.dt >= ? AND P.dt <= ? 670 AND P.session = S.session 671 AND S.ua_type = ? 672 AND P.view_x != ? 673 AND P.view_y != ? 674 GROUP BY resolution 675 ORDER BY cnt DESC" . 676 $this->limit; 677 678 return $this->db->queryAll($sql, [$this->from, $this->to, 'browser', 0, 0]); 679 } 680 681 /** 682 * @return array 683 */ 684 public function seenusers(): array 685 { 686 $sql = "SELECT `user`, MAX(`dt`) as dt 687 FROM users 688 WHERE `user` IS NOT NULL 689 AND `user` != '' 690 GROUP BY `user` 691 ORDER BY `dt` DESC" . 692 $this->limit; 693 694 return $this->db->queryAll($sql); 695 } 696} 697