<?php
class StatisticsQuery {
private $hlp;
public function __construct(helper_plugin_statistics $hlp) {
$this->hlp = $hlp;
}
/**
* Return some aggregated statistics
*/
public function aggregate($tlimit) {
$data = array();
$sql = "SELECT ref_type, COUNT(*) as cnt
FROM " . $this->hlp->prefix . "access as A
WHERE $tlimit
AND ua_type = 'browser'
GROUP BY ref_type";
$result = $this->hlp->runSQL($sql);
if(is_array($result)) 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'] == '') $data['direct'] = $row['cnt'];
}
// general user and session info
$sql = "SELECT COUNT(DISTINCT session) as sessions,
COUNT(session) as views,
COUNT(DISTINCT user) as users,
COUNT(DISTINCT uid) as visitors
FROM " . $this->hlp->prefix . "access as A
WHERE $tlimit
AND ua_type = 'browser'";
$result = $this->hlp->runSQL($sql);
$data['users'] = max($result[0]['users'] - 1, 0); // subtract empty user
$data['sessions'] = $result[0]['sessions'];
$data['pageviews'] = $result[0]['views'];
$data['visitors'] = $result[0]['visitors'];
// calculate bounce rate
if($data['sessions']) {
$sql = "SELECT COUNT(*) as cnt
FROM " . $this->hlp->prefix . "session as A
WHERE $tlimit
AND views = 1";
$result = $this->hlp->runSQL($sql);
$data['bouncerate'] = $result[0]['cnt'] * 100 / $data['sessions'];
// new visitors
$result = "SELECT COUNT(*) as cnt
FROM " . $this->hlp->prefix . "session as A
WHERE $tlimit
AND NOT EXISTS (
SELECT *
FROM stats_session B
WHERE A.session <> B.session
AND B.uid = B.uid
)";
$result = $this->hlp->runSQL($sql);
$data['newvisitors'] = $result[0]['cnt'] * 100 / $data['sessions'];
}
// calculate avg. number of views per session
$sql = "SELECT AVG(views) as cnt
FROM " . $this->hlp->prefix . "session as A
WHERE $tlimit";
$result = $this->hlp->runSQL($sql);
$data['avgpages'] = $result[0]['cnt'];
/* not used currently
$sql = "SELECT COUNT(id) as robots
FROM ".$this->hlp->prefix."access as A
WHERE $tlimit
AND ua_type = 'robot'";
$result = $this->hlp->runSQL($sql);
$data['robots'] = $result[0]['robots'];
*/
// average time spent on the site
$sql = "SELECT AVG(end - dt)/60 as time
FROM " . $this->hlp->prefix . "session as A
WHERE $tlimit
AND dt != end
AND DATE(dt) = DATE(end)";
$result = $this->hlp->runSQL($sql);
$data['timespent'] = $result[0]['time'];
// logins
$sql = "SELECT COUNT(*) as logins
FROM " . $this->hlp->prefix . "logins as A
WHERE $tlimit
AND (type = 'l' OR type = 'p')";
$result = $this->hlp->runSQL($sql);
$data['logins'] = $result[0]['logins'];
// registrations
$sql = "SELECT COUNT(*) as registrations
FROM " . $this->hlp->prefix . "logins as A
WHERE $tlimit
AND type = 'C'";
$result = $this->hlp->runSQL($sql);
$data['registrations'] = $result[0]['registrations'];
// current users
$sql = "SELECT COUNT(*) as current
FROM ". $this->hlp->prefix . "lastseen
WHERE `dt` >= NOW() - INTERVAL 10 MINUTE";
$result = $this->hlp->runSQL($sql);
$data['current'] = $result[0]['current'];
return $data;
}
/**
* standard statistics follow, only accesses made by browsers are counted
* for general stats like browser or OS only visitors not pageviews are counted
*/
/**
* Return some trend data about visits and edits in the wiki
*/
public function dashboardviews($tlimit, $hours = false) {
if($hours) {
$TIME = 'HOUR(dt)';
} else {
$TIME = 'DATE(dt)';
}
$data = array();
// access trends
$sql = "SELECT $TIME as time,
COUNT(DISTINCT session) as sessions,
COUNT(session) as pageviews,
COUNT(DISTINCT uid) as visitors
FROM " . $this->hlp->prefix . "access as A
WHERE $tlimit
AND ua_type = 'browser'
GROUP BY $TIME
ORDER BY time";
$result = $this->hlp->runSQL($sql);
foreach($result as $row) {
$data[$row['time']]['sessions'] = $row['sessions'];
$data[$row['time']]['pageviews'] = $row['pageviews'];
$data[$row['time']]['visitors'] = $row['visitors'];
}
return $data;
}
public function dashboardwiki($tlimit, $hours = false) {
if($hours) {
$TIME = 'HOUR(dt)';
} else {
$TIME = 'DATE(dt)';
}
$data = array();
// edit trends
foreach(array('E', 'C', 'D') as $type) {
$sql = "SELECT $TIME as time,
COUNT(*) as cnt
FROM " . $this->hlp->prefix . "edits as A
WHERE $tlimit
AND type = '$type'
GROUP BY $TIME
ORDER BY time";
$result = $this->hlp->runSQL($sql);
foreach($result as $row) {
$data[$row['time']][$type] = $row['cnt'];
}
}
ksort($data);
return $data;
}
public function history($tlimit, $info, $interval = false) {
if($interval == 'weeks') {
$TIME = 'EXTRACT(YEAR FROM dt), EXTRACT(WEEK FROM dt)';
} elseif ($interval == 'months') {
$TIME = 'EXTRACT(YEAR_MONTH FROM dt)';
} else {
$TIME = 'dt';
}
$mod = 1;
if($info == 'media_size' || $info == 'page_size') {
$mod = 1024*1024;
}
$sql = "SELECT $TIME as time,
AVG(`value`)/$mod as cnt
FROM " . $this->hlp->prefix . "history as A
WHERE $tlimit
AND info = '$info'
GROUP BY $TIME
ORDER BY $TIME";
return $this->hlp->runSQL($sql);
}
public function searchengines($tlimit, $start = 0, $limit = 20) {
$sql = "SELECT COUNT(*) as cnt, engine as eflag, engine
FROM " . $this->hlp->prefix . "search as A
WHERE $tlimit
GROUP BY engine
ORDER BY cnt DESC, engine" .
$this->mklimit($start, $limit);
return $this->hlp->runSQL($sql);
}
public function searchphrases($extern, $tlimit, $start = 0, $limit = 20) {
if($extern) {
$WHERE = "engine != 'dokuwiki'";
$I = '';
} else {
$WHERE = "engine = 'dokuwiki'";
$I = 'i';
}
$sql = "SELECT COUNT(*) as cnt, query, query as ${I}lookup
FROM " . $this->hlp->prefix . "search as A
WHERE $tlimit
AND $WHERE
GROUP BY query
ORDER BY cnt DESC, query" .
$this->mklimit($start, $limit);
return $this->hlp->runSQL($sql);
}
public function searchwords($extern, $tlimit, $start = 0, $limit = 20) {
if($extern) {
$WHERE = "engine != 'dokuwiki'";
$I = '';
} else {
$WHERE = "engine = 'dokuwiki'";
$I = 'i';
}
$sql = "SELECT COUNT(*) as cnt, word, word as ${I}lookup
FROM " . $this->hlp->prefix . "search as A,
" . $this->hlp->prefix . "searchwords as B
WHERE $tlimit
AND A.id = B.sid
AND $WHERE
GROUP BY word
ORDER BY cnt DESC, word" .
$this->mklimit($start, $limit);
return $this->hlp->runSQL($sql);
}
public function outlinks($tlimit, $start = 0, $limit = 20) {
$sql = "SELECT COUNT(*) as cnt, link as url
FROM " . $this->hlp->prefix . "outlinks as A
WHERE $tlimit
GROUP BY link
ORDER BY cnt DESC, link" .
$this->mklimit($start, $limit);
return $this->hlp->runSQL($sql);
}
public function pages($tlimit, $start = 0, $limit = 20) {
$sql = "SELECT COUNT(*) as cnt, page
FROM " . $this->hlp->prefix . "access as A
WHERE $tlimit
AND ua_type = 'browser'
GROUP BY page
ORDER BY cnt DESC, page" .
$this->mklimit($start, $limit);
return $this->hlp->runSQL($sql);
}
public function edits($tlimit, $start = 0, $limit = 20) {
$sql = "SELECT COUNT(*) as cnt, page
FROM " . $this->hlp->prefix . "edits as A
WHERE $tlimit
GROUP BY page
ORDER BY cnt DESC, page" .
$this->mklimit($start, $limit);
return $this->hlp->runSQL($sql);
}
public function images($tlimit, $start = 0, $limit = 20) {
$sql = "SELECT COUNT(*) as cnt, media, SUM(size) as filesize
FROM " . $this->hlp->prefix . "media as A
WHERE $tlimit
AND mime1 = 'image'
GROUP BY media
ORDER BY cnt DESC, media" .
$this->mklimit($start, $limit);
return $this->hlp->runSQL($sql);
}
public function imagessum($tlimit) {
$sql = "SELECT COUNT(*) as cnt, SUM(size) as filesize
FROM " . $this->hlp->prefix . "media as A
WHERE $tlimit
AND mime1 = 'image'";
return $this->hlp->runSQL($sql);
}
public function downloads($tlimit, $start = 0, $limit = 20) {
$sql = "SELECT COUNT(*) as cnt, media, SUM(size) as filesize
FROM " . $this->hlp->prefix . "media as A
WHERE $tlimit
AND mime1 != 'image'
GROUP BY media
ORDER BY cnt DESC, media" .
$this->mklimit($start, $limit);
return $this->hlp->runSQL($sql);
}
public function downloadssum($tlimit) {
$sql = "SELECT COUNT(*) as cnt, SUM(size) as filesize
FROM " . $this->hlp->prefix . "media as A
WHERE $tlimit
AND mime1 != 'image'";
return $this->hlp->runSQL($sql);
}
public function referer($tlimit, $start = 0, $limit = 20) {
$sql = "SELECT COUNT(*) as cnt, ref as url
FROM " . $this->hlp->prefix . "access as A
WHERE $tlimit
AND ua_type = 'browser'
AND ref_type = 'external'
GROUP BY ref_md5
ORDER BY cnt DESC, url" .
$this->mklimit($start, $limit);
return $this->hlp->runSQL($sql);
}
public function newreferer($tlimit, $start = 0, $limit = 20) {
$sql = "SELECT COUNT(*) as cnt, ref as url
FROM " . $this->hlp->prefix . "access as B,
" . $this->hlp->prefix . "refseen as A
WHERE $tlimit
AND ua_type = 'browser'
AND ref_type = 'external'
AND A.ref_md5 = B.ref_md5
GROUP BY A.ref_md5
ORDER BY cnt DESC, url" .
$this->mklimit($start, $limit);
return $this->hlp->runSQL($sql);
}
public function countries($tlimit, $start = 0, $limit = 20) {
$sql = "SELECT COUNT(DISTINCT session) as cnt, B.code AS cflag, B.country
FROM " . $this->hlp->prefix . "access as A,
" . $this->hlp->prefix . "iplocation as B
WHERE $tlimit
AND A.ip = B.ip
GROUP BY B.code
ORDER BY cnt DESC, B.country" .
$this->mklimit($start, $limit);
return $this->hlp->runSQL($sql);
}
public function browsers($tlimit, $start = 0, $limit = 20, $ext = true) {
if($ext) {
$sel = 'ua_info as bflag, ua_info as browser, ua_ver';
$grp = 'ua_info, ua_ver';
} else {
$grp = 'ua_info';
$sel = 'ua_info';
}
$sql = "SELECT COUNT(DISTINCT session) as cnt, $sel
FROM " . $this->hlp->prefix . "access as A
WHERE $tlimit
AND ua_type = 'browser'
GROUP BY $grp
ORDER BY cnt DESC, ua_info" .
$this->mklimit($start, $limit);
return $this->hlp->runSQL($sql);
}
public function os($tlimit, $start = 0, $limit = 20) {
$sql = "SELECT COUNT(DISTINCT session) as cnt, os as osflag, os
FROM " . $this->hlp->prefix . "access as A
WHERE $tlimit
AND ua_type = 'browser'
GROUP BY os
ORDER BY cnt DESC, os" .
$this->mklimit($start, $limit);
return $this->hlp->runSQL($sql);
}
public function topuser($tlimit, $start = 0, $limit = 20) {
$sql = "SELECT COUNT(*) as cnt, user
FROM " . $this->hlp->prefix . "access as A
WHERE $tlimit
AND ua_type = 'browser'
AND user != ''
GROUP BY user
ORDER BY cnt DESC, user" .
$this->mklimit($start, $limit);
return $this->hlp->runSQL($sql);
}
public function topeditor($tlimit, $start = 0, $limit = 20) {
$sql = "SELECT COUNT(*) as cnt, user
FROM " . $this->hlp->prefix . "edits as A
WHERE $tlimit
AND user != ''
GROUP BY user
ORDER BY cnt DESC, user" .
$this->mklimit($start, $limit);
return $this->hlp->runSQL($sql);
}
public function topgroup($tlimit, $start = 0, $limit = 20) {
$sql = "SELECT COUNT(*) as cnt, `group`
FROM " . $this->hlp->prefix . "groups as A
WHERE $tlimit
AND `type` = 'view'
GROUP BY `group`
ORDER BY cnt DESC, `group`" .
$this->mklimit($start, $limit);
return $this->hlp->runSQL($sql);
}
public function topgroupedit($tlimit, $start = 0, $limit = 20) {
$sql = "SELECT COUNT(*) as cnt, `group`
FROM " . $this->hlp->prefix . "groups as A
WHERE $tlimit
AND `type` = 'edit'
GROUP BY `group`
ORDER BY cnt DESC, `group`" .
$this->mklimit($start, $limit);
return $this->hlp->runSQL($sql);
}
public function resolution($tlimit, $start = 0, $limit = 20) {
$sql = "SELECT COUNT(DISTINCT uid) as cnt,
ROUND(screen_x/100)*100 as res_x,
ROUND(screen_y/100)*100 as res_y,
CONCAT(ROUND(screen_x/100)*100,'x',ROUND(screen_y/100)*100) as resolution
FROM " . $this->hlp->prefix . "access as A
WHERE $tlimit
AND ua_type = 'browser'
AND screen_x != 0
AND screen_y != 0
GROUP BY resolution
ORDER BY cnt DESC" .
$this->mklimit($start, $limit);
return $this->hlp->runSQL($sql);
}
public function viewport($tlimit, $start = 0, $limit = 20) {
$sql = "SELECT COUNT(DISTINCT uid) as cnt,
ROUND(view_x/100)*100 as res_x,
ROUND(view_y/100)*100 as res_y,
CONCAT(ROUND(view_x/100)*100,'x',ROUND(view_y/100)*100) as resolution
FROM " . $this->hlp->prefix . "access as A
WHERE $tlimit
AND ua_type = 'browser'
AND view_x != 0
AND view_y != 0
GROUP BY resolution
ORDER BY cnt DESC" .
$this->mklimit($start, $limit);
return $this->hlp->runSQL($sql);
}
public function seenusers($tlimit, $start = 0, $limit = 20) {
$sql = "SELECT `user`, `dt`
FROM " . $this->hlp->prefix . "lastseen as A
ORDER BY `dt` DESC" .
$this->mklimit($start, $limit);
return $this->hlp->runSQL($sql);
}
/**
* Builds a limit clause
*/
public function mklimit($start, $limit) {
$start = (int) $start;
$limit = (int) $limit;
if($limit) {
$limit += 1;
return " LIMIT $start,$limit";
} elseif($start) {
return " OFFSET $start";
}
return '';
}
/**
* Create a time limit for use in SQL
*/
public function mktlimit(&$from, &$to) {
// fixme add better sanity checking here:
$from = preg_replace('/[^\d\-]+/', '', $from);
$to = preg_replace('/[^\d\-]+/', '', $to);
if(!$from) $from = date('Y-m-d');
if(!$to) $to = date('Y-m-d');
return "A.dt >= '$from 00:00:00' AND A.dt <= '$to 23:59:59'";
}
}