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 = 'browser' 102 GROUP BY ref_type"; 103 $result = $this->db->queryAll($sql, [$this->from, $this->to]); 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 = 'browser'"; 132 $result = $this->db->queryRecord($sql, [$this->from, $this->to]); 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 = 'browser' 148 GROUP BY P.session 149 HAVING views = 1 150 )"; 151 $count = $this->db->queryValue($sql, [$this->from, $this->to]); 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 = 'browser' 164 GROUP BY P.session 165 )"; 166 $data['avgpages'] = $this->db->queryValue($sql, [$this->from, $this->to]); 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 = 'browser'"; 175 $data['timespent'] = $this->db->queryValue($sql, [$this->from, $this->to]); 176 177 // logins 178 $sql = "SELECT COUNT(*) as logins 179 FROM logins as A 180 WHERE A.dt >= ? AND A.dt <= ? 181 AND (type = 'l' OR type = 'p')"; 182 $data['logins'] = $this->db->queryValue($sql, [$this->from, $this->to]); 183 184 // registrations 185 $sql = "SELECT COUNT(*) as registrations 186 FROM logins as A 187 WHERE A.dt >= ? AND A.dt <= ? 188 AND type = 'C'"; 189 $data['registrations'] = $this->db->queryValue($sql, [$this->from, $this->to]); 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 = 'browser' 226 GROUP BY $TIME 227 ORDER BY time"; 228 $result = $this->db->queryAll($sql, [$this->from, $this->to]); 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 = '$type' 258 GROUP BY $TIME 259 ORDER BY time"; 260 $result = $this->db->queryAll($sql, [$this->from, $this->to]); 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 = 'browser' 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]); 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 = 'image' 400 GROUP BY media 401 ORDER BY cnt DESC, media" . 402 $this->limit; 403 return $this->db->queryAll($sql, [$this->from, $this->to]); 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 = 'image'"; 415 return $this->db->queryAll($sql, [$this->from, $this->to]); 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 != 'image' 427 GROUP BY media 428 ORDER BY cnt DESC, media" . 429 $this->limit; 430 return $this->db->queryAll($sql, [$this->from, $this->to]); 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 != 'image'"; 442 return $this->db->queryAll($sql, [$this->from, $this->to]); 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 = 'browser' 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]); 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 = 'browser' 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, $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 = 'browser' 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]); 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 = 'browser' 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]); 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 = 'browser' 555 AND S.user IS NOT NULL 556 GROUP BY U.domain 557 ORDER BY cnt DESC, U.domain" . 558 $this->limit; 559 return $this->db->queryAll($sql, [$this->from, $this->to]); 560 } 561 562 /** 563 * @return array 564 */ 565 public function topuser(): array 566 { 567 $sql = "SELECT COUNT(*) as cnt, S.user 568 FROM pageviews as P, 569 sessions as S 570 WHERE P.dt >= ? AND P.dt <= ? 571 AND P.session = S.session 572 AND S.ua_type = 'browser' 573 AND S.user IS NOT NULL 574 GROUP BY S.user 575 ORDER BY cnt DESC, S.user" . 576 $this->limit; 577 return $this->db->queryAll($sql, [$this->from, $this->to]); 578 } 579 580 /** 581 * @return array 582 */ 583 public function topeditor(): array 584 { 585 $sql = "SELECT COUNT(*) as cnt, user 586 FROM edits as E, 587 sessions as S 588 WHERE E.dt >= ? AND E.dt <= ? 589 AND E.session = S.session 590 AND S.user IS NOT NULL 591 GROUP BY user 592 ORDER BY cnt DESC, user" . 593 $this->limit; 594 return $this->db->queryAll($sql, [$this->from, $this->to]); 595 } 596 597 /** 598 * @return array 599 */ 600 public function topgroup(): array 601 { 602 $sql = "SELECT COUNT(*) as cnt, G.`group` 603 FROM pageviews as P, 604 sessions as S, 605 groups as G 606 WHERE P.dt >= ? AND P.dt <= ? 607 AND P.session = S.session 608 AND S.user = G.user 609 AND S.ua_type = 'browser' 610 GROUP BY G.`group` 611 ORDER BY cnt DESC, G.`group`" . 612 $this->limit; 613 return $this->db->queryAll($sql, [$this->from, $this->to]); 614 } 615 616 /** 617 * @return array 618 */ 619 public function topgroupedit(): array 620 { 621 $sql = "SELECT COUNT(*) as cnt, G.`group` 622 FROM edits as E, 623 sessions as S, 624 groups as G 625 WHERE E.dt >= ? AND E.dt <= ? 626 AND E.session = S.session 627 AND S.user = G.user 628 GROUP BY G.`group` 629 ORDER BY cnt DESC, G.`group`" . 630 $this->limit; 631 return $this->db->queryAll($sql, [$this->from, $this->to]); 632 } 633 634 635 /** 636 * @return array 637 */ 638 public function resolution(): array 639 { 640 $sql = "SELECT COUNT(DISTINCT S.uid) as cnt, 641 ROUND(P.screen_x/100)*100 as res_x, 642 ROUND(P.screen_y/100)*100 as res_y, 643 CAST(ROUND(P.screen_x/100)*100 AS int) 644 || 'x' || 645 CAST(ROUND(P.screen_y/100)*100 AS int) as resolution 646 FROM pageviews as P, 647 sessions as S 648 WHERE P.dt >= ? AND P.dt <= ? 649 AND P.session = S.session 650 AND S.ua_type = 'browser' 651 AND P.screen_x != 0 652 AND P.screen_y != 0 653 GROUP BY resolution 654 ORDER BY cnt DESC" . 655 $this->limit; 656 return $this->db->queryAll($sql, [$this->from, $this->to]); 657 } 658 659 /** 660 * @return array 661 */ 662 public function viewport(): array 663 { 664 $sql = "SELECT COUNT(DISTINCT S.uid) as cnt, 665 ROUND(P.view_x/100)*100 as res_x, 666 ROUND(P.view_y/100)*100 as res_y, 667 CAST(ROUND(P.view_x/100)*100 AS int) 668 || 'x' || 669 CAST(ROUND(P.view_y/100)*100 AS int) as resolution 670 FROM pageviews as P, 671 sessions as S 672 WHERE P.dt >= ? AND P.dt <= ? 673 AND P.session = S.session 674 AND S.ua_type = 'browser' 675 AND P.view_x != 0 676 AND P.view_y != 0 677 GROUP BY resolution 678 ORDER BY cnt DESC" . 679 $this->limit; 680 681 return $this->db->queryAll($sql, [$this->from, $this->to]); 682 } 683 684 /** 685 * @return array 686 */ 687 public function seenusers(): array 688 { 689 $sql = "SELECT `user`, MAX(`dt`) as dt 690 FROM users 691 WHERE `user` IS NOT NULL 692 AND `user` != '' 693 GROUP BY `user` 694 ORDER BY `dt` DESC" . 695 $this->limit; 696 697 return $this->db->queryAll($sql); 698 } 699} 700