xref: /plugin/statistics/Query.php (revision 10dcb86f38cab1d9cfa19911e1d59c086626d7c8)

<?php

namespace dokuwiki\plugin\statistics;

use dokuwiki\plugin\sqlite\SQLiteDB;
use helper_plugin_statistics;

/**
 * This class defines a bunch of SQL queries to fetch various statistics from the database
 */
class Query
{
    protected helper_plugin_statistics $hlp;
    protected SQLiteDB $db;
    protected string $from;
    protected string $to;
    protected string $limit = '';

    /**
     * @param helper_plugin_statistics $hlp
     */
    public function __construct(helper_plugin_statistics $hlp)
    {
        $this->hlp = $hlp;
        $this->db = $hlp->getDB();
        $today = date('Y-m-d');
        $this->setTimeFrame($today, $today);
        $this->setPagination(0, 20);
    }

    /**
     * Set the time frame for all queries
     *
     * @param string $from The start date as YYYY-MM-DD
     * @param string $to The end date as YYYY-MM-DD
     */
    public function setTimeFrame(string $from, string $to): void
    {
        try {
            $from = new \DateTime($from);
            $to = new \DateTime($to);
        } catch (\Exception $e) {
            $from = new \DateTime();
            $to = new \DateTime();
        }
        $from->setTime(0, 0);
        $to->setTime(23, 59, 59);

        $this->from = $from->format('Y-m-d H:i:s');
        $this->to = $to->format('Y-m-d H:i:s');
    }

    /**
     * Set the pagination settings for some queries
     *
     * @param int $start The start offset
     * @param int $limit The number of results. If one more is returned, there is another page
     * @return void
     */
    public function setPagination(int $start, int $limit)
    {
        // when a limit is set, one more is fetched to indicate when a next page exists
        if ($limit) $limit += 1;

        if ($limit) {
            $this->limit = " LIMIT $start,$limit";
        } elseif ($start) {
            $this->limit = " OFFSET $start";
        }
    }

    /**
     * Return some aggregated statistics
     */
    public function aggregate(): array
    {
        $data = [];

        // Count referrer types by joining with referers table
        $sql = "SELECT
                    CASE
                        WHEN R.engine IS NOT NULL THEN 'search'
                        WHEN R.url IS NOT NULL AND R.url != '' THEN 'external'
                        ELSE 'direct'
                    END as ref_type,
                    COUNT(*) as cnt
                  FROM pageviews as P
                  LEFT JOIN referers as R ON P.ref_id = R.id
                  LEFT JOIN sessions as S ON P.session = S.session
                 WHERE P.dt >= ? AND P.dt <= ?
                   AND S.ua_type = ?
              GROUP BY ref_type";
        $result = $this->db->queryAll($sql, [$this->from, $this->to, 'browser']);

        foreach ($result as $row) {
            if ($row['ref_type'] == 'search') $data['search'] = $row['cnt'];
            if ($row['ref_type'] == 'external') $data['external'] = $row['cnt'];
            if ($row['ref_type'] == 'internal') $data['internal'] = $row['cnt'];
            if ($row['ref_type'] == 'direct') $data['direct'] = $row['cnt'];
        }

        // general user and session info
        $sql = "SELECT COUNT(DISTINCT P.session) as sessions,
                       COUNT(P.session) as views,
                       COUNT(DISTINCT S.user) as users,
                       COUNT(DISTINCT S.uid) as visitors
                  FROM pageviews as P
                  LEFT JOIN sessions as S ON P.session = S.session
                 WHERE P.dt >= ? AND P.dt <= ?
                   AND S.ua_type = ?";
        $result = $this->db->queryRecord($sql, [$this->from, $this->to, 'browser']);

        $data['users'] = $result['users'];
        $data['sessions'] = $result['sessions'];
        $data['pageviews'] = $result['views'];
        $data['visitors'] = $result['visitors'];

        // calculate bounce rate (sessions with only 1 page view)
        if ($data['sessions']) {
            $sql = "SELECT COUNT(*) as cnt
                      FROM (
                          SELECT P.session, COUNT(*) as views
                            FROM pageviews as P
                            LEFT JOIN sessions as S ON P.session = S.session
                           WHERE P.dt >= ? AND P.dt <= ?
                             AND S.ua_type = ?
                        GROUP BY P.session
                          HAVING views = 1
                      )";
            $count = $this->db->queryValue($sql, [$this->from, $this->to, 'browser']);
            $data['bouncerate'] = $count * 100 / $data['sessions'];
            $data['newvisitors'] = $count * 100 / $data['sessions'];
        }

        // calculate avg. number of views per session
        $sql = "SELECT AVG(views) as cnt
                  FROM (
                      SELECT P.session, COUNT(*) as views
                        FROM pageviews as P
                        LEFT JOIN sessions as S ON P.session = S.session
                       WHERE P.dt >= ? AND P.dt <= ?
                         AND S.ua_type = ?
                    GROUP BY P.session
                  )";
        $data['avgpages'] = $this->db->queryValue($sql, [$this->from, $this->to, 'browser']);

        // average time spent on the site
        $sql = "SELECT AVG((julianday(end) - julianday(dt)) * 24 * 60) as time
                  FROM sessions as S
                 WHERE S.dt >= ? AND S.dt <= ?
                   AND S.dt != S.end
                   AND DATE(S.dt) = DATE(S.end)
                   AND S.ua_type = ?";
        $data['timespent'] = $this->db->queryValue($sql, [$this->from, $this->to, 'browser']);

        // logins
        $sql = "SELECT COUNT(*) as logins
                  FROM logins as A
                 WHERE A.dt >= ? AND A.dt <= ?
                   AND (type = ? OR type = ?)";
        $data['logins'] = $this->db->queryValue($sql, [$this->from, $this->to, 'l', 'p']);

        // registrations
        $sql = "SELECT COUNT(*) as registrations
                  FROM logins as A
                 WHERE A.dt >= ? AND A.dt <= ?
                   AND type = ?";
        $data['registrations'] = $this->db->queryValue($sql, [$this->from, $this->to, 'C']);

        // current users (based on recent sessions)
        $sql = "SELECT COUNT(DISTINCT uid) as current
                  FROM sessions
                 WHERE end >= datetime('now', '-10 minutes')";
        $data['current'] = $this->db->queryValue($sql);

        return $data;
    }


    /**
     * Return some trend data about visits and edits in the wiki
     *
     * @param bool $hours Use hour resolution rather than days
     * @return array
     */
    public function dashboardviews(bool $hours = false): array
    {
        if ($hours) {
            $TIME = 'strftime(\'%H\', P.dt)';
        } else {
            $TIME = 'DATE(P.dt)';
        }

        $data = [];

        // access trends
        $sql = "SELECT $TIME as time,
                       COUNT(DISTINCT P.session) as sessions,
                       COUNT(P.session) as pageviews,
                       COUNT(DISTINCT S.uid) as visitors
                  FROM pageviews as P
                  LEFT JOIN sessions as S ON P.session = S.session
                 WHERE P.dt >= ? AND P.dt <= ?
                   AND S.ua_type = ?
              GROUP BY $TIME
              ORDER BY time";
        $result = $this->db->queryAll($sql, [$this->from, $this->to, 'browser']);
        foreach ($result as $row) {
            $data[$row['time']]['sessions'] = $row['sessions'];
            $data[$row['time']]['pageviews'] = $row['pageviews'];
            $data[$row['time']]['visitors'] = $row['visitors'];
        }
        return $data;
    }

    /**
     * @param bool $hours Use hour resolution rather than days
     * @return array
     */
    public function dashboardwiki(bool $hours = false): array
    {
        if ($hours) {
            $TIME = 'strftime(\'%H\', dt)';
        } else {
            $TIME = 'DATE(dt)';
        }

        $data = [];

        // edit trends
        foreach (['E', 'C', 'D'] as $type) {
            $sql = "SELECT $TIME as time,
                           COUNT(*) as cnt
                      FROM edits as A
                     WHERE A.dt >= ? AND A.dt <= ?
                       AND type = ?
                  GROUP BY $TIME
                  ORDER BY time";
            $result = $this->db->queryAll($sql, [$this->from, $this->to, $type]);
            foreach ($result as $row) {
                $data[$row['time']][$type] = $row['cnt'];
            }
        }
        ksort($data);
        return $data;
    }

    /**
     * @param string $info Which type of history to select (FIXME which ones are there?)
     * @param string $interval Group data by this interval (days, weeks, months)
     * @return array
     */
    public function history(string $info, string $interval = 'day'): array
    {
        if ($interval == 'weeks') {
            $TIME = 'strftime(\'%Y\', dt), strftime(\'%W\', dt)';
        } elseif ($interval == 'months') {
            $TIME = 'strftime(\'%Y-%m\', dt)';
        } else {
            $TIME = 'strftime(\'%d-%m\', dt)';
        }

        $mod = 1;
        if ($info == 'media_size' || $info == 'page_size') {
            $mod = 1024 * 1024;
        }

        $sql = "SELECT $TIME as time,
                       AVG(value)/$mod as cnt
                  FROM history as A
                 WHERE A.dt >= ? AND A.dt <= ?
                   AND info = ?
                  GROUP BY $TIME
                  ORDER BY $TIME";
        return $this->db->queryAll($sql, [$this->from, $this->to, $info]);
    }

    /**
     * @return array
     */
    public function searchengines(): array
    {
        $sql = "SELECT COUNT(*) as cnt, R.engine
                  FROM pageviews as P
                  LEFT JOIN referers as R ON P.ref_id = R.id
                 WHERE P.dt >= ? AND P.dt <= ?
                   AND R.engine IS NOT NULL
                   AND R.engine != ''
              GROUP BY R.engine
              ORDER BY cnt DESC, R.engine" .
            $this->limit;
        return $this->db->queryAll($sql, [$this->from, $this->to]);
    }

    /**
     * @param bool $extern Limit results to external search engine (true) or dokuwiki (false)
     * @return array
     */
    public function searchphrases(bool $extern = false): array
    {
        if ($extern) {
            $WHERE = "S.query != '' AND (R.engine IS NULL OR R.engine != ?)";
            $engineParam = 'dokuwiki';
            $I = '';
        } else {
            $WHERE = "S.query != '' AND R.engine = ?";
            $engineParam = 'dokuwiki';
            $I = 'i';
        }
        $sql = "SELECT COUNT(*) as cnt, S.query, S.query as ${I}lookup
                  FROM search as S
                  LEFT JOIN referers as R ON S.query = R.url
                 WHERE S.dt >= ? AND S.dt <= ?
                   AND $WHERE
              GROUP BY S.query
              ORDER BY cnt DESC, S.query" .
            $this->limit;
        return $this->db->queryAll($sql, [$this->from, $this->to, $engineParam]);
    }

    /**
     * @param bool $extern Limit results to external search engine (true) or dokuwiki (false)
     * @return array
     */
    public function searchwords(bool $extern = false): array
    {
        if ($extern) {
            $WHERE = "R.engine IS NULL OR R.engine != ?";
            $engineParam = 'dokuwiki';
            $I = '';
        } else {
            $WHERE = "R.engine = ?";
            $engineParam = 'dokuwiki';
            $I = 'i';
        }
        $sql = "SELECT COUNT(*) as cnt, SW.word, SW.word as ${I}lookup
                  FROM search as S
                  LEFT JOIN searchwords as SW ON S.id = SW.sid
                  LEFT JOIN referers as R ON S.query = R.url
                 WHERE S.dt >= ? AND S.dt <= ?
                   AND SW.word IS NOT NULL
                   AND $WHERE
              GROUP BY SW.word
              ORDER BY cnt DESC, SW.word" .
            $this->limit;
        return $this->db->queryAll($sql, [$this->from, $this->to, $engineParam]);
    }

    /**
     * @return array
     */
    public function outlinks(): array
    {
        $sql = "SELECT COUNT(*) as cnt, link as url
                  FROM outlinks as A
                 WHERE A.dt >= ? AND A.dt <= ?
              GROUP BY link
              ORDER BY cnt DESC, link" .
            $this->limit;
        return $this->db->queryAll($sql, [$this->from, $this->to]);
    }

    /**
     * @return array
     */
    public function pages(): array
    {
        $sql = "SELECT COUNT(*) as cnt, P.page
                  FROM pageviews as P
                  LEFT JOIN sessions as S ON P.session = S.session
                 WHERE P.dt >= ? AND P.dt <= ?
                   AND S.ua_type = ?
              GROUP BY P.page
              ORDER BY cnt DESC, P.page" .
            $this->limit;
        return $this->db->queryAll($sql, [$this->from, $this->to, 'browser']);
    }

    /**
     * @return array
     */
    public function edits(): array
    {
        $sql = "SELECT COUNT(*) as cnt, page
                  FROM edits as A
                 WHERE A.dt >= ? AND A.dt <= ?
              GROUP BY page
              ORDER BY cnt DESC, page" .
            $this->limit;
        return $this->db->queryAll($sql, [$this->from, $this->to]);
    }

    /**
     * @return array
     */
    public function images(): array
    {
        $sql = "SELECT COUNT(*) as cnt, media, SUM(size) as filesize
                  FROM media as A
                 WHERE A.dt >= ? AND A.dt <= ?
                   AND mime1 = ?
              GROUP BY media
              ORDER BY cnt DESC, media" .
            $this->limit;
        return $this->db->queryAll($sql, [$this->from, $this->to, 'image']);
    }

    /**
     * @return array
     */
    public function imagessum(): array
    {
        $sql = "SELECT COUNT(*) as cnt, SUM(size) as filesize
                  FROM media as A
                 WHERE A.dt >= ? AND A.dt <= ?
                   AND mime1 = ?";
        return $this->db->queryAll($sql, [$this->from, $this->to, 'image']);
    }

    /**
     * @return array
     */
    public function downloads(): array
    {
        $sql = "SELECT COUNT(*) as cnt, media, SUM(size) as filesize
                  FROM media as A
                 WHERE A.dt >= ? AND A.dt <= ?
                   AND mime1 != ?
              GROUP BY media
              ORDER BY cnt DESC, media" .
            $this->limit;
        return $this->db->queryAll($sql, [$this->from, $this->to, 'image']);
    }

    /**
     * @return array
     */
    public function downloadssum(): array
    {
        $sql = "SELECT COUNT(*) as cnt, SUM(size) as filesize
                  FROM media as A
                 WHERE A.dt >= ? AND A.dt <= ?
                   AND mime1 != ?";
        return $this->db->queryAll($sql, [$this->from, $this->to, 'image']);
    }

    /**
     * @return array
     */
    public function referer(): array
    {
        $sql = "SELECT COUNT(*) as cnt, R.url
                  FROM pageviews as P
                  LEFT JOIN sessions as S ON P.session = S.session
                  LEFT JOIN referers as R ON P.ref_id = R.id
                 WHERE P.dt >= ? AND P.dt <= ?
                   AND S.ua_type = ?
                   AND R.url IS NOT NULL
                   AND R.url != ''
                   AND R.engine IS NULL
              GROUP BY R.url
              ORDER BY cnt DESC, R.url" .
            $this->limit;
        return $this->db->queryAll($sql, [$this->from, $this->to, 'browser']);
    }

    /**
     * @return array
     */
    public function newreferer(): array
    {
        $sql = "SELECT COUNT(*) as cnt, R.url
                  FROM pageviews as P
                  LEFT JOIN sessions as S ON P.session = S.session
                  LEFT JOIN referers as R ON P.ref_id = R.id
                 WHERE P.dt >= ? AND P.dt <= ?
                   AND S.ua_type = ?
                   AND R.url IS NOT NULL
                   AND R.url != ''
                   AND R.engine IS NULL
                   AND R.dt >= ?
              GROUP BY R.url
              ORDER BY cnt DESC, R.url" .
            $this->limit;
        return $this->db->queryAll($sql, [$this->from, $this->to, 'browser', $this->from]);
    }

    /**
     * @return array
     */
    public function countries(): array
    {
        $sql = "SELECT COUNT(DISTINCT P.session) as cnt, I.country
                  FROM pageviews as P
                  LEFT JOIN iplocation as I ON P.ip = I.ip
                 WHERE P.dt >= ? AND P.dt <= ?
                   AND I.country IS NOT NULL
                   AND I.country != ''
              GROUP BY I.code
              ORDER BY cnt DESC, I.country" .
            $this->limit;
        return $this->db->queryAll($sql, [$this->from, $this->to]);
    }

    /**
     * @param bool $ext return extended information
     * @return array
     */
    public function browsers(bool $ext = true): array
    {
        if ($ext) {
            $sel = 'S.ua_info as browser, S.ua_ver';
            $grp = 'S.ua_info, S.ua_ver';
        } else {
            $grp = 'S.ua_info';
            $sel = 'S.ua_info';
        }

        $sql = "SELECT COUNT(DISTINCT S.session) as cnt, $sel
                  FROM sessions as S
                 WHERE S.dt >= ? AND S.dt <= ?
                   AND S.ua_type = ?
              GROUP BY $grp
              ORDER BY cnt DESC, S.ua_info" .
            $this->limit;
        return $this->db->queryAll($sql, [$this->from, $this->to, 'browser']);
    }

    /**
     * @return array
     */
    public function os(): array
    {
        $sql = "SELECT COUNT(DISTINCT S.session) as cnt, S.os
                  FROM sessions as S
                 WHERE S.dt >= ? AND S.dt <= ?
                   AND S.ua_type = ?
              GROUP BY S.os
              ORDER BY cnt DESC, S.os" .
            $this->limit;
        return $this->db->queryAll($sql, [$this->from, $this->to, 'browser']);
    }

    /**
     * @return array
     */
    public function topuser(): array
    {
        $sql = "SELECT COUNT(*) as cnt, S.user
                  FROM pageviews as P
                  LEFT JOIN sessions as S ON P.session = S.session
                 WHERE P.dt >= ? AND P.dt <= ?
                   AND S.ua_type = ?
                   AND S.user IS NOT NULL
                   AND S.user != ?
              GROUP BY S.user
              ORDER BY cnt DESC, S.user" .
            $this->limit;
        return $this->db->queryAll($sql, [$this->from, $this->to, 'browser', '']);
    }

    /**
     * @return array
     */
    public function topeditor(): array
    {
        $sql = "SELECT COUNT(*) as cnt, user
                  FROM edits as E
             LEFT JOIN sessions as S ON E.session = S.session
                 WHERE E.dt >= ? AND E.dt <= ?
                   AND S.user != ?
              GROUP BY user
              ORDER BY cnt DESC, user" .
            $this->limit;
        return $this->db->queryAll($sql, [$this->from, $this->to, '']);
    }

    /**
     * @return array
     */
    public function topgroup(): array
    {
        $sql = "SELECT COUNT(*) as cnt, G.`group`
                  FROM pageviews as P
                  LEFT JOIN sessions as S ON P.session = S.session
                  LEFT JOIN groups as G ON S.user = G.user
                 WHERE P.dt >= ? AND P.dt <= ?
                   AND S.ua_type = ?
                   AND G.`group` IS NOT NULL
              GROUP BY G.`group`
              ORDER BY cnt DESC, G.`group`" .
            $this->limit;
        return $this->db->queryAll($sql, [$this->from, $this->to, 'browser']);
    }

    /**
     * @return array
     */
    public function topgroupedit(): array
    {
        $sql = "SELECT COUNT(*) as cnt, G.`group`
                  FROM edits as E
                  LEFT JOIN sessions as S ON E.session = S.session
                  LEFT JOIN groups as G ON S.user = G.user
                 WHERE E.dt >= ? AND E.dt <= ?
                   AND G.`group` IS NOT NULL
              GROUP BY G.`group`
              ORDER BY cnt DESC, G.`group`" .
            $this->limit;
        return $this->db->queryAll($sql, [$this->from, $this->to]);
    }


    /**
     * @return array
     */
    public function resolution(): array
    {
        $sql = "SELECT COUNT(DISTINCT S.uid) as cnt,
                       ROUND(P.screen_x/100)*100 as res_x,
                       ROUND(P.screen_y/100)*100 as res_y,
                       CAST(ROUND(P.screen_x/100)*100 AS int) || 'x' || CAST(ROUND(P.screen_y/100)*100 AS int) as resolution
                  FROM pageviews as P
                  LEFT JOIN sessions as S ON P.session = S.session
                 WHERE P.dt >= ? AND P.dt <= ?
                   AND S.ua_type = ?
                   AND P.screen_x != ?
                   AND P.screen_y != ?
              GROUP BY resolution
              ORDER BY cnt DESC" .
            $this->limit;
        return $this->db->queryAll($sql, [$this->from, $this->to, 'browser', 0, 0]);
    }

    /**
     * @return array
     */
    public function viewport(): array
    {
        $sql = "SELECT COUNT(DISTINCT S.uid) as cnt,
                       ROUND(P.view_x/100)*100 as res_x,
                       ROUND(P.view_y/100)*100 as res_y,
                       CAST(ROUND(P.view_x/100)*100 AS int) || 'x' || CAST(ROUND(P.view_y/100)*100 AS int) as resolution
                  FROM pageviews as P
                  LEFT JOIN sessions as S ON P.session = S.session
                 WHERE P.dt >= ? AND P.dt <= ?
                   AND S.ua_type = ?
                   AND P.view_x != ?
                   AND P.view_y != ?
              GROUP BY resolution
              ORDER BY cnt DESC" .
            $this->limit;

        return $this->db->queryAll($sql, [$this->from, $this->to, 'browser', 0, 0]);
    }

    /**
     * @return array
     */
    public function seenusers(): array
    {
        $sql = "SELECT `user`, MAX(`dt`) as dt
                  FROM users
                 WHERE `user` IS NOT NULL
                   AND `user` != ''
              GROUP BY `user`
              ORDER BY `dt` DESC" .
            $this->limit;

        return $this->db->queryAll($sql);
    }
}