public function testUtc2local()
{
$helper = \Helper\View::instance();
$time = 1420498500;
$result = $helper->utc2local($time);
$this->assertEquals(1420473300, $result);
}
public function index($f3)
{
$this->_requireLogin(0);
$db = $f3->get("db.instance");
// User stats
$result = $db->exec("SELECT AVG(a.num) num\n\t\t\t\tFROM (\n\t\t\t\t\tSELECT COUNT(*) AS num\n\t\t\t\t\tFROM issue i\n\t\t\t\t\tGROUP BY i.author_id\n\t\t\t\t\tHAVING num > 0\n\t\t\t\t) a");
$f3->set("user_avg_created", $result[0]['num']);
$result = $db->exec("SELECT AVG(a.num) num\n\t\t\t\tFROM (\n\t\t\t\t\tSELECT COUNT(*) num\n\t\t\t\t\tFROM issue_comment c\n\t\t\t\t\tGROUP BY c.user_id\n\t\t\t\t) a");
$f3->set("user_avg_comments", $result[0]['num']);
$result = $db->exec("SELECT AVG(a.num) num\n\t\t\t\tFROM (\n\t\t\t\t\tSELECT COUNT(*) num\n\t\t\t\t\tFROM issue_update u\n\t\t\t\t\tGROUP BY u.user_id\n\t\t\t\t) a");
$f3->set("user_avg_updates", $result[0]['num']);
$result = $db->exec("SELECT AVG(a.num) num\n\t\t\t\tFROM (\n\t\t\t\t\tSELECT SUM(new_value - old_value) num\n\t\t\t\t\tFROM issue_update_field f\n\t\t\t\t\tJOIN issue_update u ON u.id = f.issue_update_id\n\t\t\t\t\tWHERE f.field = 'hours_spent'\n\t\t\t\t\tGROUP BY u.user_id\n\t\t\t\t\tHAVING num > 0\n\t\t\t\t) a");
$f3->set("user_avg_hours_spent", $result[0]['num']);
$result = $db->exec("SELECT author_name name, COUNT(*) num\n\t\t\t\tFROM issue_detail\n\t\t\t\tGROUP BY author_id\n\t\t\t\tORDER BY num DESC\n\t\t\t\tLIMIT 25");
$f3->set("user_created", $result);
$result = $db->exec("SELECT owner_name name, COUNT(*) num\n\t\t\t\tFROM issue_detail\n\t\t\t\tWHERE status_closed = '1'\n\t\t\t\tGROUP BY owner_id\n\t\t\t\tORDER BY num DESC\n\t\t\t\tLIMIT 25");
$f3->set("user_closed", $result);
$result = $db->exec("SELECT u.name, COUNT(*) num\n\t\t\t\tFROM issue_update iu\n\t\t\t\tJOIN user u ON u.id = iu.user_id\n\t\t\t\tGROUP BY u.id\n\t\t\t\tORDER BY num DESC\n\t\t\t\tLIMIT 25");
$f3->set("user_updates", $result);
$result = $db->exec("SELECT u.name, SUM(iuf.new_value - iuf.old_value) num\n\t\t\t\tFROM issue_update iu\n\t\t\t\tJOIN issue_update_field iuf ON iu.id = iuf.issue_update_id\n\t\t\t\tJOIN `user` u ON u.id = iu.user_id\n\t\t\t\tWHERE iuf.field = 'hours_spent'\n\t\t\t\tGROUP BY u.id\n\t\t\t\tORDER BY num DESC\n\t\t\t\tLIMIT 25");
$f3->set("user_hours", $result);
// Issue stats
$result = $db->exec("SELECT SUM(hours_spent) num FROM issue");
$f3->set("issues_total_hours_spent", $result[0]['num']);
$result = $db->exec("SELECT AVG(hours_spent) num\n\t\t\t\tFROM issue\n\t\t\t\tWHERE hours_spent > 0\n\t\t\t\t\tAND hours_spent IS NOT NULL");
$f3->set("issues_avg_hours_spent", $result[0]['num']);
$result = $db->exec("SELECT COUNT(*) num FROM issue WHERE closed_date IS NOT NULL");
$f3->set("issues_total_closed", $result[0]['num']);
$result = $db->exec("SELECT i.id, i.name, COUNT(*) num\n\t\t\t\tFROM issue i\n\t\t\t\tJOIN issue_comment c ON c.issue_id = i.id\n\t\t\t\tGROUP BY i.id\n\t\t\t\tORDER BY num DESC\n\t\t\t\tLIMIT 25");
$f3->set("top_issue_comments", $result);
$result = $db->exec("SELECT i.id, i.name, COUNT(*) num\n\t\t\t\tFROM issue i\n\t\t\t\tJOIN issue_update u ON u.issue_id = i.id\n\t\t\t\tGROUP BY i.id\n\t\t\t\tORDER BY num DESC\n\t\t\t\tLIMIT 25");
$f3->set("top_issue_updates", $result);
$result = $db->exec("SELECT i.id, i.name, i.hours_spent num\n\t\t\t\tFROM issue i\n\t\t\t\tWHERE hours_spent > 0\n\t\t\t\t\tAND hours_spent IS NOT NULL\n\t\t\t\tORDER BY hours_spent DESC\n\t\t\t\tLIMIT 25");
$f3->set("top_issue_hours", $result);
// Render view
echo \Helper\View::instance()->render("stats/index.html");
}
/**
* Get user statistics
* @param int $time The lower limit on timestamps for stats collection
* @return array
*/
public function stats($time = 0)
{
\Helper\View::instance()->utc2local();
$offset = \Base::instance()->get("site.timeoffset");
if (!$time) {
$time = strtotime("-2 weeks", time() + $offset);
}
$result = array();
$result["spent"] = $this->db->exec("SELECT DATE(DATE_ADD(u.created_date, INTERVAL :offset SECOND)) AS `date`, SUM(f.new_value - f.old_value) AS `val`\n\t\t\tFROM issue_update u\n\t\t\tJOIN issue_update_field f ON u.id = f.issue_update_id AND f.field = 'hours_spent'\n\t\t\tWHERE u.user_id = :user AND u.created_date > :date\n\t\t\tGROUP BY DATE(DATE_ADD(u.created_date, INTERVAL :offset2 SECOND))", array(":user" => $this->id, ":offset" => $offset, ":offset2" => $offset, ":date" => date("Y-m-d H:i:s", $time)));
$result["closed"] = $this->db->exec("SELECT DATE(DATE_ADD(i.closed_date, INTERVAL :offset SECOND)) AS `date`, COUNT(*) AS `val`\n\t\t\tFROM issue i\n\t\t\tWHERE i.owner_id = :user AND i.closed_date > :date\n\t\t\tGROUP BY DATE(DATE_ADD(i.closed_date, INTERVAL :offset2 SECOND))", array(":user" => $this->id, ":offset" => $offset, ":offset2" => $offset, ":date" => date("Y-m-d H:i:s", $time)));
$result["created"] = $this->db->exec("SELECT DATE(DATE_ADD(i.created_date, INTERVAL :offset SECOND)) AS `date`, COUNT(*) AS `val`\n\t\t\tFROM issue i\n\t\t\tWHERE i.author_id = :user AND i.created_date > :date\n\t\t\tGROUP BY DATE(DATE_ADD(i.created_date, INTERVAL :offset2 SECOND))", array(":user" => $this->id, ":offset" => $offset, ":offset2" => $offset, ":date" => date("Y-m-d H:i:s", $time)));
$dates = $this->_createDateRangeArray(date("Y-m-d", $time), date("Y-m-d", time() + $offset));
$return = array("labels" => array(), "spent" => array(), "closed" => array(), "created" => array());
foreach ($result["spent"] as $r) {
$return["spent"][$r["date"]] = floatval($r["val"]);
}
foreach ($result["closed"] as $r) {
$return["closed"][$r["date"]] = intval($r["val"]);
}
foreach ($result["created"] as $r) {
$return["created"][$r["date"]] = intval($r["val"]);
}
foreach ($dates as $date) {
$return["labels"][$date] = date("D j", strtotime($date));
if (!isset($return["spent"][$date])) {
$return["spent"][$date] = 0;
}
if (!isset($return["closed"][$date])) {
$return["closed"][$date] = 0;
}
if (!isset($return["created"][$date])) {
$return["created"][$date] = 0;
}
}
foreach ($return as &$r) {
ksort($r);
}
return $return;
}
/**
* Render a view and return the result
* @param string $file
* @param string $mime
* @param array $hive
* @param integer $ttl
* @return string
*/
protected function _render($file, $mime = "text/html", array $hive = null, $ttl = 0)
{
return \Helper\View::instance()->render($file, $mime, $hive, $ttl);
}
/**
* View a taskboard
*
* @param \Base $f3
* @param array $params
*/
public function index($f3, $params)
{
$sprint = new \Model\Sprint();
// Load current sprint if no sprint ID is given
if (!intval($params["id"])) {
$localDate = date('Y-m-d', \Helper\View::instance()->utc2local());
$sprint->load(array("? BETWEEN start_date AND end_date", $localDate));
if (!$sprint->id) {
$f3->error(404);
return;
}
}
// Default to showing group tasks
if (empty($params["filter"])) {
$params["filter"] = "groups";
}
// Load the requested sprint
if (!$sprint->id) {
$sprint->load($params["id"]);
if (!$sprint->id) {
$f3->error(404);
return;
}
}
$f3->set("sprint", $sprint);
$f3->set("title", $sprint->name . " " . date('n/j', strtotime($sprint->start_date)) . "-" . date('n/j', strtotime($sprint->end_date)));
$f3->set("menuitem", "backlog");
// Get list of all users in the user's groups
$filter_users = $this->_filterUsers($params);
// Load issue statuses
$status = new \Model\Issue\Status();
$statuses = $status->find(array('taskboard > 0'), array('order' => 'taskboard_sort ASC'));
$mapped_statuses = array();
$visible_status_ids = array();
$column_count = 0;
foreach ($statuses as $s) {
$visible_status_ids[] = $s->id;
$mapped_statuses[$s->id] = $s;
$column_count += $s->taskboard;
}
$visible_status_ids = implode(",", $visible_status_ids);
$f3->set("statuses", $mapped_statuses);
$f3->set("column_count", $column_count);
// Load issue priorities
$priority = new \Model\Issue\Priority();
$f3->set("priorities", $priority->find(null, array("order" => "value DESC"), $f3->get("cache_expire.db")));
// Load project list
$issue = new \Model\Issue\Detail();
// Find all visible tasks
$tasks = $issue->find(array("sprint_id = ? AND type_id != ? AND deleted_date IS NULL AND status IN ({$visible_status_ids})" . (empty($filter_users) ? "" : " AND owner_id IN (" . implode(",", $filter_users) . ")"), $sprint->id, $f3->get("issue_type.project")), array("order" => "priority DESC"));
$task_ids = array();
$parent_ids = array(0);
foreach ($tasks as $task) {
$task_ids[] = $task->id;
if ($task->parent_id) {
$parent_ids[] = $task->parent_id;
}
}
$task_ids_str = implode(",", $task_ids);
$parent_ids_str = implode(",", $parent_ids);
$f3->set("tasks", $task_ids_str);
// Find all visible projects or parent tasks
$projects = $issue->find(array("id IN ({$parent_ids_str}) OR (sprint_id = ? AND type_id = ? AND deleted_date IS NULL" . (empty($filter_users) ? ")" : " AND owner_id IN (" . implode(",", $filter_users) . "))"), $sprint->id, $f3->get("issue_type.project")), array("order" => "owner_id ASC, priority DESC"));
// Sort projects if a filter is given
if (!empty($params["filter"]) && is_numeric($params["filter"])) {
$sortModel = new \Model\Issue\Backlog();
$sortModel->load(array("user_id = ? AND sprint_id = ?", $params["filter"], $sprint->id));
$sortArray = array();
if ($sortModel->id) {
$sortArray = json_decode($sortModel->issues);
usort($projects, function (\Model\Issue $a, \Model\Issue $b) use($sortArray) {
$ka = array_search($a->id, $sortArray);
$kb = array_search($b->id, $sortArray);
if ($ka === false && $kb !== false) {
return -1;
}
if ($ka !== false && $kb === false) {
return 1;
}
if ($ka === $kb) {
return 0;
}
if ($ka > $kb) {
return 1;
}
if ($ka < $kb) {
return -1;
}
});
}
}
// Build multidimensional array of all tasks and projects
$taskboard = array();
foreach ($projects as $project) {
// Build array of statuses to put tasks under
$columns = array();
foreach ($statuses as $status) {
$columns[$status["id"]] = array();
}
// Add current project's tasks
foreach ($tasks as $task) {
if ($task->parent_id == $project->id || $project->id == 0 && (!$task->parent_id || !in_array($task->parent_id, $parent_ids))) {
$columns[$task->status][] = $task;
}
}
// Add hierarchical structure to taskboard array
$taskboard[] = array("project" => $project, "columns" => $columns);
}
$f3->set("taskboard", array_values($taskboard));
$f3->set("filter", $params["filter"]);
// Get user list for select
$users = new \Model\User();
$f3->set("users", $users->getAll());
$f3->set("groups", $users->getAllGroups());
$this->_render("taskboard/index.html");
}
$pluginName = "Plugin\\" . str_replace(" ", "_", ucwords(str_replace("_", " ", $pluginName))) . "\\Base";
$plugin = $pluginName::instance();
$slug = \Web::instance()->slug($plugin->_package());
$plugins[$slug] = $plugin;
if (!$plugin->_installed()) {
try {
$plugin->_install();
} catch (Exception $e) {
$f3->set("error", "Failed to install plugin " . $plugin->_package() . ": " . $e->getMessage());
}
}
try {
$plugin->_load();
} catch (Exception $e) {
$f3->set("error", "Failed to initialize plugin " . $plugin->_package() . ": " . $e->getMessage());
}
}
}
$f3->set("plugins", $plugins);
// register filter
\Helper\View::instance()->filter('parseText', '$this->parseText');
\Helper\View::instance()->filter('formatFilesize', '$this->formatFilesize');
// Set up user session
$user = new Model\User();
$user->loadCurrent();
// Load issue types
$types = new \Model\Issue\Type();
$f3->set("issue_types", $types->find(null, null, $f3->get("cache_expire.db")));
// Run the application
$f3->set("menuitem", false);
$f3->run();
/**
* @param \Base $f3
* @param array $params
* @throws \Exception
*/
public function avatar($f3, $params)
{
// Ensure proper content-type for JPEG images
if ($params["format"] == "jpg") {
$params["format"] = "jpeg";
}
$user = new \Model\User();
$user->load($params["id"]);
if ($user->avatar_filename && is_file("uploads/avatars/" . $user->avatar_filename)) {
// Use local file
$img = new \Image($user->avatar_filename, null, "uploads/avatars/");
$img->resize($params["size"], $params["size"]);
// Render and output image
header("Content-type: image/" . $params["format"]);
$img->render($params["format"]);
} else {
// Send user to Gravatar
$f3->reroute($f3->get("SCHEME") . ":" . \Helper\View::instance()->gravatar($user->email, $params["size"]), true);
}
}
/**
* GET /issues/project/@id
* Project Overview action
*
* @param \Base $f3
* @param array $params
*/
public function project_overview($f3, $params)
{
// Load issue
$project = new \Model\Issue\Detail();
$project->load($params["id"]);
if (!$project->id) {
$f3->error(404);
return;
}
if ($project->type_id != $f3->get("issue_type.project")) {
$f3->error(400, "Issue is not a project.");
return;
}
/**
* Helper function to get a percentage of completed issues and some totals across the entire tree
* @param \Model\Issue $issue
* @var callable $completeCount This function, required for recursive calls
* @return array
*/
$projectStats = function (\Model\Issue &$issue) use(&$projectStats) {
$total = 0;
$complete = 0;
$hoursSpent = 0;
$hoursTotal = 0;
if ($issue->id) {
$total++;
if ($issue->closed_date) {
$complete++;
}
if ($issue->hours_spent > 0) {
$hoursSpent += $issue->hours_spent;
}
if ($issue->hours_total > 0) {
$hoursTotal += $issue->hours_total;
}
foreach ($issue->getChildren() as $child) {
$result = $projectStats($child);
$total += $result["total"];
$complete += $result["complete"];
$hoursSpent += $result["hours_spent"];
$hoursTotal += $result["hours_total"];
}
}
return array("total" => $total, "complete" => $complete, "hours_spent" => $hoursSpent, "hours_total" => $hoursTotal);
};
$f3->set("stats", $projectStats($project));
/**
* Helper function for recursive tree rendering
* @param \Model\Issue $issue
* @var callable $renderTree This function, required for recursive calls
*/
$renderTree = function (\Model\Issue &$issue, $level = 0) use(&$renderTree) {
if ($issue->id) {
$f3 = \Base::instance();
$children = $issue->getChildren();
$hive = array("issue" => $issue, "children" => $children, "dict" => $f3->get("dict"), "BASE" => $f3->get("BASE"), "level" => $level, "issue_type" => $f3->get("issue_type"));
echo \Helper\View::instance()->render("issues/project/tree-item.html", "text/html", $hive);
if ($children) {
foreach ($children as $item) {
$renderTree($item, $level + 1);
}
}
}
};
$f3->set("renderTree", $renderTree);
// Render view
$f3->set("project", $project);
$f3->set("title", $project->type_name . " #" . $project->id . ": " . $project->name . " - " . $f3->get("dict.project_overview"));
$this->_render("issues/project.html");
}
public function single_tree($f3, $params)
{
$this->_requireLogin();
$user = new \Model\User();
$user->load(array("username = ? AND deleted_date IS NULL", $params["username"]));
if ($user->id) {
$f3->set("title", $user->name);
$f3->set("this_user", $user);
// Load assigned issues
$issue = new \Model\Issue\Detail();
$assigned = $issue->find(array("closed_date IS NULL AND deleted_date IS NULL AND owner_id = ?", $user->id));
// Build issue list
$issues = array();
$assigned_ids = array();
$missing_ids = array();
foreach ($assigned as $iss) {
$issues[] = $iss->cast();
$assigned_ids[] = $iss->id;
}
foreach ($issues as $iss) {
if ($iss["parent_id"] && !in_array($iss["parent_id"], $assigned_ids)) {
$missing_ids[] = $iss["parent_id"];
}
}
while (!empty($missing_ids)) {
$parents = $issue->find("id IN (" . implode(",", $missing_ids) . ")");
foreach ($parents as $iss) {
if (($key = array_search($iss->id, $missing_ids)) !== false) {
unset($missing_ids[$key]);
}
$issues[] = $iss->cast();
$assigned_ids[] = $iss->id;
if ($iss->parent_id && !in_array($iss->parent_id, $assigned_ids)) {
$missing_ids[] = $iss->parent_id;
}
}
}
// Convert list to tree
$tree = $this->_buildTree($issues);
/**
* Helper function for recursive tree rendering
* @param Issue $issue
* @var callable $renderTree This function, required for recursive calls
*/
$renderTree = function (&$issue, $level = 0) use(&$renderTree) {
if (!empty($issue['id'])) {
$f3 = \Base::instance();
$hive = array("issue" => $issue, "dict" => $f3->get("dict"), "BASE" => $f3->get("BASE"), "level" => $level, "issue_type" => $f3->get("issue_type"));
echo \Helper\View::instance()->render("issues/project/tree-item.html", "text/html", $hive);
if (!empty($issue['children'])) {
foreach ($issue['children'] as $item) {
$renderTree($item, $level + 1);
}
}
}
};
$f3->set("renderTree", $renderTree);
// Render view
$f3->set("issues", $tree);
$this->_render($f3->get("AJAX") ? "user/single/tree/ajax.html" : "user/single/tree.html");
} else {
$f3->error(404);
}
}
/**
* GET /user/@username
*
* @param \Base $f3
* @param array $params
* @throws \Exception
*/
public function single($f3, $params)
{
$this->_requireLogin();
$user = new \Model\User();
$user->load(array("username = ?", $params["username"]));
if ($user->id && (!$user->deleted_date || $f3->get("user.rank") >= 3)) {
$f3->set("title", $user->name);
$f3->set("this_user", $user);
// Extra arrays required for bulk update
$status = new \Model\Issue\Status();
$f3->set("statuses", $status->find(null, null, $f3->get("cache_expire.db")));
$f3->set("users", $user->getAll());
$f3->set("groups", $user->getAllGroups());
$priority = new \Model\Issue\Priority();
$f3->set("priorities", $priority->find(null, array("order" => "value DESC"), $f3->get("cache_expire.db")));
$type = new \Model\Issue\Type();
$f3->set("types", $type->find(null, null, $f3->get("cache_expire.db")));
$issue = new \Model\Issue\Detail();
$f3->set("created_issues", $issue->paginate(0, 200, array("status_closed = '0' AND deleted_date IS NULL AND author_id = ?", $user->id), array("order" => "priority DESC, due_date DESC")));
$f3->set("assigned_issues", $issue->paginate(0, 200, array("status_closed = '0' AND deleted_date IS NULL AND owner_id = ?", $user->id), array("order" => "priority DESC, due_date DESC")));
$f3->set("overdue_issues", $issue->paginate(0, 200, array("status_closed = '0' AND deleted_date IS NULL AND owner_id = ? AND due_date IS NOT NULL AND due_date < ?", $user->id, date("Y-m-d", \Helper\View::instance()->utc2local())), array("order" => "due_date ASC")));
$this->_render("user/single.html");
} else {
$f3->error(404);
}
}
$f3->set("revision", trim(file_get_contents(".git/refs/heads/master")));
} else {
$f3->set("revision", "");
}
// Load configuration
$f3->config("config-base.ini");
$f3->config("config.ini");
// Load routes
$f3->config("app/routes.ini");
// Set up error handling
$f3->set("ONERROR", function (Base $f3) {
switch ($f3->get("ERROR.code")) {
case 404:
$f3->set("title", "Not Found");
$f3->set("ESCAPE", false);
echo \Helper\View::instance()->render("error/404.html");
break;
case 403:
echo "You do not have access to this page.";
break;
default:
if (ob_get_level()) {
include "app/view/error/inline.html";
} else {
include "app/view/error/500.html";
}
}
});
// Connect to database
$f3->set("db.instance", new DB\SQL("mysql:host=" . $f3->get("db.host") . ";port=" . $f3->get("db.port") . ";dbname=" . $f3->get("db.name"), $f3->get("db.user"), $f3->get("db.pass")));
// Load final configuration
/**
* Generate page for admin panel
*/
public function _admin()
{
echo \Helper\View::instance()->render("stats/admin.html");
}
