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