protected function execute()
{
$query = <<<QUERY
SELECT
t.id as "Template ID",
t.usercode as "Template Code",
u.count as "Active users using template",
countall as "All users using template"
FROM welcometemplate t
LEFT JOIN
(
SELECT
welcome_template,
COUNT(*) as count
FROM user
WHERE
(status = "User" OR status = "Admin")
AND welcome_template IS NOT NULL
GROUP BY welcome_template
) u ON u.welcome_template = t.id
LEFT JOIN
(
SELECT
welcome_template as allid,
COUNT(*) as countall
FROM user
WHERE welcome_template IS NOT NULL
GROUP BY welcome_template
) u2 ON u2.allid = t.id;
QUERY;
global $baseurl;
$qb = new QueryBrowser();
$r = $qb->executeQueryToTable($query);
return $r;
}
protected function execute()
{
$query = <<<SQL
SELECT
log_closed.objectid AS Request,
user.username AS User,
log_closed.user AS UserID,
TIMEDIFF(log_closed.timestamp, log_reserved.timestamp) AS 'Time Taken',
closes.mail_desc AS 'Close Type',
log_closed.timestamp AS 'Date'
FROM log log_closed
INNER JOIN log log_reserved ON log_closed.objectid = log_reserved.objectid
\tAND log_closed.objecttype = log_reserved.objecttype
INNER JOIN closes ON closes.`closes` = log_closed.action
LEFT JOIN user ON log_closed.user = user.id
WHERE log_closed.action LIKE 'Closed%'
AND log_reserved.action = 'Reserved'
AND TIMEDIFF(log_closed.timestamp, log_reserved.timestamp) < '00:00:30'
AND log_closed.user = log_reserved.user
AND TIMEDIFF(log_closed.timestamp, log_reserved.timestamp) > '00:00:00'
AND DATE(log_closed.timestamp) > DATE(NOW()-INTERVAL 3 MONTH)
ORDER BY TIMEDIFF(log_closed.timestamp, log_reserved.timestamp) ASC
;
SQL;
$qb = new QueryBrowser();
$qb->tableCallbackFunction = "statsFastClosesRowCallback";
$qb->overrideTableTitles = array("Request", "User", "Time Taken", "Close Type", "Date");
$qb->rowFetchMode = PDO::FETCH_NUM;
$r = $qb->executeQueryToTable($query);
return $r;
}
private function getUserList()
{
$query = "select username, status, checkuser from user where identified = 1 order by username;";
$qb = new QueryBrowser();
$qb->rowFetchMode = PDO::FETCH_NUM;
$qb->overrideTableTitles = array("User name", "Access level", "Checkuser?");
$r = $qb->executeQueryToTable($query);
return $r;
}
protected function execute()
{
global $baseurl;
$query = <<<sql
SELECT
CONCAT("<a href=\\"", "{$baseurl}", "/acc.php?action=zoom&id=", p.id, "\\">", p.id, "</a>") AS "#",
p.name AS "Requested Name",
p.status AS "Status",
u.username AS "Reserved by"
FROM request p
INNER JOIN user u ON u.id = p.reserved
WHERE reserved != 0;
sql;
$qb = new QueryBrowser();
return $qb->executeQueryToTable($query);
}
protected function execute()
{
global $baseurl;
$query = <<<sql
SELECT
CONCAT('<a href="', '{$baseurl}', '/acc.php?action=zoom&id=', p.id, '">', p.id, '</a>') AS '#',
p.name AS 'Requested Name',
p.status AS 'Status',
u.username AS 'Reserved by'
FROM request p
INNER JOIN user u ON u.id = p.reserved
WHERE reserved != 0;
sql;
$qb = new QueryBrowser();
return $qb->executeQueryToTable($query);
}
protected function execute()
{
$query = <<<sql
SELECT '0' AS 'Version', 'Active' AS 'Type', COUNT(*) AS 'Count' FROM user WHERE password NOT LIKE ':%' AND (status = 'User' OR status = 'Admin')
UNION
SELECT '0', 'Inactive', COUNT(*) FROM user WHERE password NOT LIKE ':%' AND NOT (status = 'User' OR status = 'Admin')
UNION
SELECT SUBSTRING(password FROM 2 FOR 1), 'Active', COUNT(*) FROM user WHERE password LIKE ':%' AND (status = 'User' OR status = 'Admin') GROUP BY SUBSTRING(password FROM 2 FOR 1)
UNION
SELECT SUBSTRING(password FROM 2 FOR 1), 'Inactive', COUNT(*) FROM user WHERE password LIKE ':%' AND NOT (status = 'User' OR status = 'Admin') GROUP BY SUBSTRING(password FROM 2 FOR 1)
ORDER BY `Version` ASC, 'Type' ASC
;
sql;
$qb = new QueryBrowser();
$qb->rowFetchMode = PDO::FETCH_NUM;
$r = $qb->executeQueryToTable($query);
return $r;
}
protected function execute()
{
$query = <<<sql
SELECT "0" AS "Version", "Active" AS "Type", COUNT(*) AS "Count" FROM user WHERE password NOT LIKE ":%" AND (status = "User" OR status = "Admin")
UNION
SELECT "0", "Inactive", COUNT(*) FROM user WHERE password NOT LIKE ":%" AND NOT (status = "User" OR status = "Admin")
UNION
SELECT SUBSTRING(password FROM 2 FOR 1), "Active", COUNT(*) FROM user WHERE password LIKE ":%" AND (status = "User" OR status = "Admin") GROUP BY SUBSTRING(password FROM 2 FOR 1)
UNION
SELECT SUBSTRING(password FROM 2 FOR 1), "Inactive", COUNT(*) FROM user WHERE password LIKE ":%" AND NOT (status = "User" OR status = "Admin") GROUP BY SUBSTRING(password FROM 2 FOR 1)
ORDER BY `Version` ASC, "Type" ASC
;
sql;
global $baseurl;
$qb = new QueryBrowser();
$qb->rowFetchMode = PDO::FETCH_NUM;
$r = $qb->executeQueryToTable($query);
return $r;
}
protected function execute()
{
$query = <<<QUERY
SELECT
Closed.log_pend AS Request,
Closed.log_user AS User,
u.id AS UserID,
TIMEDIFF(Closed.log_time, Reserved.log_time) AS "Time Taken",
mail_desc AS "Close Type",
Closed.log_time AS "Date"
FROM acc_log Closed
INNER JOIN acc_log Reserved
ON Closed.log_pend = Reserved.log_pend
INNER JOIN closes c
ON c.`closes` = Closed.log_action
LEFT JOIN user u
ON Closed.log_user = u.username
WHERE
Closed.log_action LIKE "Closed%"
AND
Reserved.log_action = "Reserved"
AND
TIMEDIFF(Closed.log_time, Reserved.log_time) < "00:00:30"
AND
Closed.log_user = Reserved.log_user
AND
TIMEDIFF(Closed.log_time, Reserved.log_time) > "00:00:00"
AND
DATE(Closed.log_time) > DATE(NOW()-INTERVAL 3 MONTH)
ORDER BY
TIMEDIFF(Closed.log_time, Reserved.log_time) ASC
;
QUERY;
global $baseurl;
$qb = new QueryBrowser();
$qb->tableCallbackFunction = "statsFastClosesRowCallback";
$qb->overrideTableTitles = array("Request", "User", "Time Taken", "Close Type", "Date");
$qb->rowFetchMode = PDO::FETCH_NUM;
$r = $qb->executeQueryToTable($query);
return $r;
}
private function createClosuresGraph($queries)
{
$qb = new QueryBrowser();
$imagehashes = array();
foreach ($queries as $q) {
$DataSet = new pData();
$qResult = $qb->executeQueryToArray($q['query']);
if (sizeof($qResult) > 0) {
foreach ($qResult as $row) {
$DataSet->AddPoint($row['y'], $q['series'], $row['x']);
}
$DataSet->AddAllSeries();
$DataSet->SetAbsciseLabelSerie();
$chartname = $this->createPathFromHash(md5(serialize($DataSet)));
$imagehashes[] = array($chartname, $q['series']);
if (!file_exists($chartname)) {
$Test = new pChart(700, 280);
$Test->setFontProperties("graph/Fonts/tahoma.ttf", 8);
$Test->setGraphArea(50, 30, 680, 200);
$Test->drawFilledRoundedRectangle(7, 7, 693, 273, 5, 240, 240, 240);
$Test->drawRoundedRectangle(5, 5, 695, 275, 5, 230, 230, 230);
$Test->drawGraphArea(255, 255, 255, true);
$Test->drawScale($DataSet->GetData(), $DataSet->GetDataDescription(), SCALE_NORMAL, 150, 150, 150, true, 45, 2);
$Test->drawGrid(4, true, 230, 230, 230, 50);
// Draw the 0 line
$Test->setFontProperties("graph/Fonts/tahoma.ttf", 6);
$Test->drawTreshold(0, 143, 55, 72, true, true);
// Draw the cubic curve graph
$Test->drawFilledCubicCurve($DataSet->GetData(), $DataSet->GetDataDescription(), 0.1, 50);
// Finish the graph
$Test->setFontProperties("graph/Fonts/tahoma.ttf", 10);
$Test->drawTitle(50, 22, $q['series'], 50, 50, 50, 585);
$Test->Render("render/" . $chartname);
}
}
}
return $imagehashes;
}
protected function execute()
{
global $smarty;
$qb = new QueryBrowser();
$qb->numberedList = true;
$qb->numberedListTitle = "Position";
$qb->tableCallbackFunction = "statsTopCreatorsRowCallback";
$qb->overrideTableTitles = array("# Created", "Username");
// Retrieve all-time stats
$top5aout = $qb->executeQueryToTable(<<<SQL
SELECT
\t/* StatsTopCreators::execute()/top5aout */
COUNT(*),
log.user user_id,
user.username log_user,
user.status user_level
FROM log
LEFT JOIN emailtemplate ON concat('Closed ', emailtemplate.id) = log.action
INNER JOIN user ON user.id = log.user
WHERE emailtemplate.oncreated = '1'
OR log.action = 'Closed custom-y'
GROUP BY log.user, user.username, user.status
ORDER BY COUNT(*) DESC;
SQL
);
// Retrieve all-time stats for active users only
$top5activeout = $qb->executeQueryToTable(<<<SQL
SELECT
\t/* StatsTopCreators::execute()/top5activeout */
COUNT(*),
log.user user_id,
user.username log_user,
user.status user_level
FROM log
LEFT JOIN emailtemplate ON concat('Closed ', emailtemplate.id) = log.action
INNER JOIN user ON user.id = log.user
WHERE
\t(emailtemplate.oncreated = 1 OR log.action = 'Closed custom-y')
AND user.status != 'Suspended'
GROUP BY user.username, user.id
ORDER BY COUNT(*) DESC;
SQL
);
// Retrieve today's stats (so far)
$now = date("Y-m-d", mktime(0, 0, 0, date("m"), date("d")));
$top5out = $qb->executeQueryToTable(<<<SQL
SELECT
\t/* StatsTopCreators::execute()/top5out */
COUNT(*),
log.user user_id,
user.username log_user,
user.status user_level
FROM log
INNER JOIN user ON user.id = log.user
LEFT JOIN emailtemplate ON CONCAT('Closed ', emailtemplate.id) = log.action
WHERE (emailtemplate.oncreated = '1' OR log.action = 'Closed custom-y')
AND log.timestamp LIKE '{$now}%'
GROUP BY log.user, user.username
ORDER BY COUNT(*) DESC;
SQL
);
// Retrieve Yesterday's stats
$yesterday = date("Y-m-d", mktime(0, 0, 0, date("m"), date("d") - 1));
$top5yout = $qb->executeQueryToTable(<<<SQL
SELECT
\t/* StatsTopCreators::execute()/top5yout */
COUNT(*),
log.user user_id,
user.username log_user,
user.status user_level
FROM log
INNER JOIN user ON user.id = log.user
LEFT JOIN emailtemplate ON CONCAT('Closed ', emailtemplate.id) = log.action
WHERE (emailtemplate.oncreated = '1' OR log.action = 'Closed custom-y')
AND log.timestamp LIKE '{$yesterday}%'
GROUP BY log.user, user.username
ORDER BY COUNT(*) DESC;
SQL
);
// Retrieve last 7 days
$lastweek = date("Y-m-d", mktime(0, 0, 0, date("m"), date("d") - 7));
$top5wout = $qb->executeQueryToTable(<<<SQL
SELECT
\t/* StatsTopCreators::execute()/top5wout */
COUNT(*),
log.user user_id,
user.username log_user,
user.status user_level
FROM log
INNER JOIN user ON user.id = log.user
LEFT JOIN emailtemplate ON CONCAT('Closed ', emailtemplate.id) = log.action
WHERE (emailtemplate.oncreated = '1' OR log.action = 'Closed custom-y')
AND log.timestamp > '{$lastweek}%'
GROUP BY log.user, user.username
ORDER BY COUNT(*) DESC;
SQL
);
// Retrieve last month's stats
$lastmonth = date("Y-m-d", mktime(0, 0, 0, date("m"), date("d") - 28));
$top5mout = $qb->executeQueryToTable(<<<SQL
SELECT
\t/* StatsTopCreators::execute()/top5mout */
COUNT(*),
log.user user_id,
user.username log_user,
user.status user_level
FROM log
INNER JOIN user ON user.id = log.user
LEFT JOIN emailtemplate ON CONCAT('Closed ', emailtemplate.id) = log.action
WHERE (emailtemplate.oncreated = '1' OR log.action = 'Closed custom-y')
AND log.timestamp > '{$lastmonth}%'
GROUP BY log.user, user.username
ORDER BY COUNT(*) DESC;
SQL
);
// Put it all together
$smarty->assign("top5aout", $top5aout);
$smarty->assign("top5activeout", $top5activeout);
$smarty->assign("top5out", $top5out);
$smarty->assign("top5yout", $top5yout);
$smarty->assign("top5wout", $top5wout);
$smarty->assign("top5mout", $top5mout);
return $smarty->fetch("statistics/topcreators.tpl");
}
protected function execute()
{
global $smarty;
$qb = new QueryBrowser();
$qb->numberedList = true;
$qb->numberedListTitle = "Postition";
$qb->tableCallbackFunction = "statsTopCreatorsRowCallback";
$qb->overrideTableTitles = array("# Created", "Username");
// Retrieve all-time stats
$top5aout = $qb->executeQueryToTable(<<<SQL
SELECT
COUNT(*),
u.`id` user_id,
`log_user`,
u.`status` user_level
FROM `acc_log` l
INNER JOIN `user` u ON u.`username` = l.`log_user`
LEFT JOIN `emailtemplate` e
ON concat('Closed ', e.`id`) = l.`log_action`
WHERE (e.`oncreated` = "1" OR `log_action` = "Closed custom-y")
GROUP BY `log_user`, u.`id`
ORDER BY COUNT(*) DESC;
SQL
);
// Retrieve all-time stats for active users only
$top5activeout = $qb->executeQueryToTable(<<<SQL
SELECT
COUNT(*),
u.`id` user_id,
`log_user`,
u.`status` user_level
FROM `acc_log` l
INNER JOIN `user` u
ON u.`username` = l.`log_user`
LEFT JOIN `emailtemplate` e
ON concat('Closed ', e.`id`) = l.`log_action`
WHERE (e.`oncreated` = "1" OR `log_action` = "Closed custom-y")
AND u.`status` != "Suspended"
GROUP BY `log_user`, u.`id`
ORDER BY COUNT(*) DESC;
SQL
);
// Retrieve today's stats (so far)
$now = date("Y-m-d", mktime(0, 0, 0, date("m"), date("d")));
$top5out = $qb->executeQueryToTable(<<<SQL
SELECT
COUNT(*),
u.`id` user_id,
`log_user`,
u.`status` user_level
FROM `acc_log` l
INNER JOIN `user` u
ON u.`username` = l.`log_user`
LEFT JOIN `emailtemplate` e
ON concat('Closed ', e.`id`) = l.`log_action`
WHERE (e.`oncreated` = "1" OR `log_action` = "Closed custom-y")
AND `log_time` LIKE "{$now}%"
GROUP BY `log_user`, u.`id`
ORDER BY COUNT(*) DESC;
SQL
);
// Retrieve Yesterday's stats
$yesterday = date("Y-m-d", mktime(0, 0, 0, date("m"), date("d") - 1));
$top5yout = $qb->executeQueryToTable(<<<SQL
SELECT
COUNT(*),
u.`id` user_id,
`log_user`,
u.`status` user_level
FROM `acc_log` l
INNER JOIN `user` u
ON u.`username` = l.`log_user`
LEFT JOIN `emailtemplate` e
ON concat('Closed ', e.`id`) = l.`log_action`
WHERE (e.`oncreated` = "1" OR `log_action` = "Closed custom-y")
AND `log_time` LIKE "{$yesterday}%"
GROUP BY `log_user`, u.`id`
ORDER BY COUNT(*) DESC;
SQL
);
// Retrieve last 7 days
$lastweek = date("Y-m-d", mktime(0, 0, 0, date("m"), date("d") - 7));
$top5wout = $qb->executeQueryToTable(<<<SQL
SELECT
COUNT(*),
u.`id` user_id,
`log_user`,
u.`status` user_level
FROM `acc_log` l
INNER JOIN `user` u
ON u.`username` = l.`log_user`
LEFT JOIN `emailtemplate` e
ON concat('Closed ', e.`id`) = l.`log_action`
WHERE (e.`oncreated` = "1" OR `log_action` = "Closed custom-y")
AND `log_time` > "{$lastweek}%"
GROUP BY `log_user`, u.`id`
ORDER BY COUNT(*) DESC;
SQL
);
// Retrieve last month's stats
$lastmonth = date("Y-m-d", mktime(0, 0, 0, date("m"), date("d") - 28));
$top5mout = $qb->executeQueryToTable(<<<SQL
SELECT
COUNT(*),
u.`id` user_id,
`log_user`,
u.`status` user_level
FROM `acc_log` l
INNER JOIN `user` u
ON u.`username` = l.`log_user`
LEFT JOIN `emailtemplate` e
ON concat('Closed ', e.`id`) = l.`log_action`
WHERE (e.`oncreated` = "1" OR `log_action` = "Closed custom-y")
AND `log_time` > "{$lastmonth}%"
GROUP BY `log_user`, u.`id`
ORDER BY COUNT(*) DESC;
SQL
);
// Put it all together
$smarty->assign("top5aout", $top5aout);
$smarty->assign("top5activeout", $top5activeout);
$smarty->assign("top5out", $top5out);
$smarty->assign("top5yout", $top5yout);
$smarty->assign("top5wout", $top5wout);
$smarty->assign("top5mout", $top5mout);
return $smarty->fetch("statistics/topcreators.tpl");
}
