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 countries(): array 514 { 515 $sql = "SELECT COUNT(DISTINCT P.session) as cnt, I.country 516 FROM pageviews as P, 517 iplocation as I 518 WHERE DATETIME(P.dt, ?) >= ? AND DATETIME(P.dt, ?) <= ? 519 AND P.ip = I.ip 520 AND I.country != '' 521 GROUP BY I.code 522 ORDER BY cnt DESC, I.country" . 523 $this->limit; 524 return $this->db->queryAll($sql, [$this->tz, $this->from, $this->tz, $this->to]); 525 } 526 527 /** 528 * @param bool $ext return extended information 529 * @return array 530 */ 531 public function browsers(bool $ext = false): array 532 { 533 if ($ext) { 534 $sel = 'S.ua_info as browser, S.ua_ver'; 535 $grp = 'S.ua_info, S.ua_ver'; 536 } else { 537 $sel = 'S.ua_info as browser'; 538 $grp = 'S.ua_info'; 539 } 540 541 $sql = "SELECT COUNT(DISTINCT S.session) as cnt, $sel 542 FROM sessions as S 543 WHERE DATETIME(S.dt, ?) >= ? AND DATETIME(S.dt, ?) <= ? 544 AND S.ua_type = 'browser' 545 GROUP BY $grp 546 ORDER BY cnt DESC, S.ua_info" . 547 $this->limit; 548 return $this->db->queryAll($sql, [$this->tz, $this->from, $this->tz, $this->to]); 549 } 550 551 /** 552 * @return array 553 */ 554 public function os(): array 555 { 556 $sql = "SELECT COUNT(DISTINCT S.session) as cnt, S.os 557 FROM sessions as S 558 WHERE DATETIME(S.dt, ?) >= ? AND DATETIME(S.dt, ?) <= ? 559 AND S.ua_type = 'browser' 560 GROUP BY S.os 561 ORDER BY cnt DESC, S.os" . 562 $this->limit; 563 return $this->db->queryAll($sql, [$this->tz, $this->from, $this->tz, $this->to]); 564 } 565 566 /** 567 * @return array 568 */ 569 public function topdomain(): array 570 { 571 $sql = "SELECT COUNT(*) as cnt, U.domain 572 FROM pageviews as P, 573 sessions as S, 574 users as U 575 WHERE DATETIME(P.dt, ?) >= ? AND DATETIME(P.dt, ?) <= ? 576 AND P.session = S.session 577 AND S.user = U.user 578 AND S.ua_type = 'browser' 579 AND S.user IS NOT NULL 580 GROUP BY U.domain 581 ORDER BY cnt DESC, U.domain" . 582 $this->limit; 583 return $this->db->queryAll($sql, [$this->tz, $this->from, $this->tz, $this->to]); 584 } 585 586 /** 587 * @return array 588 */ 589 public function topuser(): array 590 { 591 $sql = "SELECT COUNT(*) as cnt, S.user 592 FROM pageviews as P, 593 sessions as S 594 WHERE DATETIME(P.dt, ?) >= ? AND DATETIME(P.dt, ?) <= ? 595 AND P.session = S.session 596 AND S.ua_type = 'browser' 597 AND S.user IS NOT NULL 598 GROUP BY S.user 599 ORDER BY cnt DESC, S.user" . 600 $this->limit; 601 return $this->db->queryAll($sql, [$this->tz, $this->from, $this->tz, $this->to]); 602 } 603 604 /** 605 * @return array 606 */ 607 public function topeditor(): array 608 { 609 $sql = "SELECT COUNT(*) as cnt, user 610 FROM edits as E, 611 sessions as S 612 WHERE DATETIME(E.dt, ?) >= ? AND DATETIME(E.dt, ?) <= ? 613 AND E.session = S.session 614 AND S.user IS NOT NULL 615 GROUP BY user 616 ORDER BY cnt DESC, user" . 617 $this->limit; 618 return $this->db->queryAll($sql, [$this->tz, $this->from, $this->tz, $this->to]); 619 } 620 621 /** 622 * @return array 623 */ 624 public function topgroup(): array 625 { 626 $sql = "SELECT COUNT(*) as cnt, G.`group` 627 FROM pageviews as P, 628 sessions as S, 629 groups as G 630 WHERE DATETIME(P.dt, ?) >= ? AND DATETIME(P.dt, ?) <= ? 631 AND P.session = S.session 632 AND S.user = G.user 633 AND S.ua_type = 'browser' 634 GROUP BY G.`group` 635 ORDER BY cnt DESC, G.`group`" . 636 $this->limit; 637 return $this->db->queryAll($sql, [$this->tz, $this->from, $this->tz, $this->to]); 638 } 639 640 /** 641 * @return array 642 */ 643 public function topgroupedit(): array 644 { 645 $sql = "SELECT COUNT(*) as cnt, G.`group` 646 FROM edits as E, 647 sessions as S, 648 groups as G 649 WHERE DATETIME(E.dt, ?) >= ? AND DATETIME(E.dt, ?) <= ? 650 AND E.session = S.session 651 AND S.user = G.user 652 GROUP BY G.`group` 653 ORDER BY cnt DESC, G.`group`" . 654 $this->limit; 655 return $this->db->queryAll($sql, [$this->tz, $this->from, $this->tz, $this->to]); 656 } 657 658 659 /** 660 * @return array 661 */ 662 public function resolution(): array 663 { 664 $sql = "SELECT COUNT(DISTINCT S.uid) as cnt, 665 ROUND(P.screen_x/100)*100 as res_x, 666 ROUND(P.screen_y/100)*100 as res_y, 667 CAST(ROUND(P.screen_x/100)*100 AS int) 668 || 'x' || 669 CAST(ROUND(P.screen_y/100)*100 AS int) as resolution 670 FROM pageviews as P, 671 sessions as S 672 WHERE DATETIME(P.dt, ?) >= ? AND DATETIME(P.dt, ?) <= ? 673 AND P.session = S.session 674 AND S.ua_type = 'browser' 675 AND P.screen_x != 0 676 AND P.screen_y != 0 677 GROUP BY resolution 678 ORDER BY cnt DESC" . 679 $this->limit; 680 return $this->db->queryAll($sql, [$this->tz, $this->from, $this->tz, $this->to]); 681 } 682 683 /** 684 * @return array 685 */ 686 public function viewport(): array 687 { 688 $sql = "SELECT COUNT(DISTINCT S.uid) as cnt, 689 ROUND(P.view_x/100)*100 as res_x, 690 ROUND(P.view_y/100)*100 as res_y, 691 CAST(ROUND(P.view_x/100)*100 AS int) 692 || 'x' || 693 CAST(ROUND(P.view_y/100)*100 AS int) as resolution 694 FROM pageviews as P, 695 sessions as S 696 WHERE DATETIME(P.dt, ?) >= ? AND DATETIME(P.dt, ?) <= ? 697 AND P.session = S.session 698 AND S.ua_type = 'browser' 699 AND P.view_x != 0 700 AND P.view_y != 0 701 GROUP BY resolution 702 ORDER BY cnt DESC" . 703 $this->limit; 704 705 return $this->db->queryAll($sql, [$this->tz, $this->from, $this->tz, $this->to]); 706 } 707 708 /** 709 * @return array 710 */ 711 public function seenusers(): array 712 { 713 $sql = "SELECT `user`, MAX(`dt`) as dt 714 FROM users 715 WHERE `user` IS NOT NULL 716 AND `user` != '' 717 GROUP BY `user` 718 ORDER BY `dt` DESC" . 719 $this->limit; 720 721 return $this->db->queryAll($sql); 722 } 723} 724