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"); }