/**
* Returns statistics on task data
*
* @param int optional user id
* @return array statistics
*/
public static function getStats($uid = null)
{
if (!is_null($uid)) {
$where = ' AND `assignee_uid` = :uid';
} else {
$where = null;
}
$query = new \TaskerMAN\Core\DBQuery("SELECT\r\n\r\n\t\t\t(\r\n\t\t\t\tSELECT COUNT(*)\r\n\t\t\t\tFROM `tasks`\r\n\t\t\t\tWHERE `status` <> 0\r\n\t\t\t\t{$where}\r\n\t\t\t) AS `total`,\r\n\r\n\t\t\t(\r\n\t\t\t\tSELECT COUNT(*)\r\n\t\t\t\tFROM `tasks`\r\n\t\t\t\tWHERE `tasks`.`status` = 1\r\n\t\t\t\t{$where}\r\n\t\t\t) AS `outstanding`,\r\n\r\n\t\t\t(\r\n\t\t\t\tSELECT COUNT(*)\r\n\t\t\t\tFROM `tasks`\r\n\t\t\t\tWHERE `tasks`.`status` = 1\r\n\t\t\t\tAND `tasks`.`due_by` BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 1 WEEK)\r\n\t\t\t\t{$where}\r\n\t\t\t) AS `due_in_week`,\r\n\r\n\t\t\t(\r\n\t\t\t\tSELECT COUNT(*)\r\n\t\t\t\tFROM `tasks`\r\n\t\t\t\tWHERE `tasks`.`status` = 1\r\n\t\t\t\tAND NOW() > `tasks`.`due_by`\r\n\t\t\t\t{$where}\r\n\t\t\t) AS `overdue`,\r\n\r\n\t\t\t(\r\n\t\t\t\tSELECT COUNT(*)\r\n\t\t\t\tFROM `tasks`\r\n\t\t\t\tWHERE `tasks`.`status` = 2\r\n\t\t\t\t{$where}\r\n\t\t\t) AS `completed`,\r\n\r\n\t\t\t(\r\n\t\t\t\tSELECT COUNT(*)\r\n\t\t\t\tFROM `tasks`\r\n\t\t\t\tWHERE `tasks`.`status` = 2\r\n\t\t\t\tAND `tasks`.`due_by` <= `tasks`.`completed_time`\r\n\t\t\t\t{$where}\r\n\t\t\t) AS `completed_late`,\r\n\r\n\t\t\t(\r\n\t\t\t\tSELECT COUNT(*)\r\n\t\t\t\tFROM `tasks`\r\n\t\t\t\tWHERE `tasks`.`status` = 2\r\n\t\t\t\tAND `tasks`.`due_by` > `tasks`.`completed_time`\r\n\t\t\t\t{$where}\r\n\t\t\t) AS `completed_on_time`,\r\n\r\n\t\t\t(\r\n\t\t\t\tSELECT ROUND(\r\n\t\t\t\t\t\t\tABS(\r\n\t\t\t\t\t\t\t\tAVG(\r\n\t\t\t\t\t\t\t\t\tTIME_TO_SEC(\r\n\t\t\t\t\t\t\t\t\t\t\t\tTIMEDIFF(\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`tasks`.`created_time`, `tasks`.`completed_time`\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t)\r\n\t\t\t\t\t\t\t\t\t\t\t\t)\r\n\t\t\t\t\t\t\t\t\t)\r\n\t\t\t\t\t\t\t\t)\r\n\t\t\t\t\t\t\t)\r\n\t\t\t\tFROM `tasks`\r\n\t\t\t\tWHERE `tasks`.`status` = 2\r\n\t\t\t\t{$where}\r\n\t\t\t) AS `average_completion_time`,\r\n\r\n\t\t\t(\r\n\t\t\t\tSELECT COUNT(*)\r\n\t\t\t\tFROM `users`\r\n\t\t\t) AS `user_count`\r\n\r\n\t\t");
if (!is_null($uid)) {
$query->bindValue(':uid', $uid);
}
$query->execute();
$stats = $query->row();
// Calculate percentage of tasks completed on time
if ($stats['completed'] > 0) {
$stats['average_completion_time'] = \TaskerMAN\WebInterface\DateFormat::timeFormat($stats['average_completion_time'], true);
$stats['completed_on_time_percentage'] = round($stats['completed_on_time'] / $stats['completed'] * 100, 2);
} else {
$stats['average_completion_time'] = 'n/a';
$stats['completed_on_time_percentage'] = 100;
}
$stats['avg_tasks_per_user'] = floor($stats['total'] / $stats['user_count']);
return $stats;
}
/**
* Saves the task object to the database, also triggers buildSteps() if
* this is a new task.
*
* @return boolean
* @throws TaskException
*/
public function save()
{
// Check if task is new. If so, INSERT query is run
if ($this->new_task) {
if (empty($this->temp_steps)) {
// Each task must have at least one step associated with it
throw new TaskException('Task creation requires at least one associated step');
return false;
}
$stmt = new \TaskerMAN\Core\DBQuery("INSERT INTO `tasks`\r\n\t\t\t\t(`created_uid`, `created_time`, `assignee_uid`, `due_by`, `completed_time`, `status`, `title`)\r\n\t\t\t\tVALUES\r\n\t\t\t\t(:created_uid, NOW(), :assignee_uid, :due_by, :completed_time, :status, :title)\r\n\t\t\t");
} else {
$stmt = new \TaskerMAN\Core\DBQuery("UPDATE `tasks` SET\r\n\t\t\t\t`created_uid` = :created_uid,\r\n\t\t\t\t`assignee_uid` = :assignee_uid,\r\n\t\t\t\t`due_by` = :due_by,\r\n\t\t\t\t`completed_time` = :completed_time,\r\n\t\t\t\t`status` = :status,\r\n\t\t\t\t`title`= :title\r\n\r\n\t\t\t\tWHERE `id` = :id\r\n\t\t\t\tLIMIT 1\r\n\t\t\t");
$stmt->bindValue(':id', (int) $this->id, \PDO::PARAM_INT);
}
// Bind variables
$stmt->bindValue(':created_uid', (int) $this->created_uid, \PDO::PARAM_INT);
$stmt->bindValue(':assignee_uid', (int) $this->assignee_uid, \PDO::PARAM_INT);
$stmt->bindValue(':due_by', (string) $this->due_by, \PDO::PARAM_STR);
$stmt->bindValue(':completed_time', (string) $this->completed_time, \PDO::PARAM_STR);
$stmt->bindValue(':status', (int) $this->status, \PDO::PARAM_INT);
$stmt->bindValue(':title', (string) $this->title, \PDO::PARAM_STR);
$stmt->execute();
// If this is a new task, run buildSteps()
if ($this->new_task) {
$this->id = $stmt->lastInsertID();
$this->new_task = false;
$this->buildSteps();
}
}
/**
* Saves any changes to the database
*/
public function save()
{
if ($this->new_step) {
$query = new \TaskerMAN\Core\DBQuery("INSERT INTO `steps`\r\n\t\t\t\t(`task_id`, `title`, `comment`)\r\n\t\t\t\tVALUES\r\n\t\t\t\t(:task_id, :title, :comment)\r\n\t\t\t");
$query->bindValue(':task_id', $this->task_id);
$query->bindValue(':title', $this->title);
$query->bindValue(':comment', $this->comment);
$query->execute();
$this->id = $query->lastInsertID();
} else {
$query = new \TaskerMAN\Core\DBQuery("UPDATE `steps` SET\r\n\t\t\t\t`title` = :title,\r\n\t\t\t\t`comment` = :comment\r\n\r\n\t\t\t\tWHERE `id` = :id\r\n\t\t\t\tLIMIT 1\r\n\t\t\t");
$query->bindValue(':id', $this->id);
$query->bindValue(':title', $this->title);
$query->bindValue(':comment', $this->comment);
$query->execute();
}
}
/**
* Returns total count of users in database
*
* @return int Count
*/
public static function getNumUsers()
{
$conditional = self::buildConditional();
$query = new \TaskerMAN\Core\DBQuery("SELECT\r\n\t\t \tCOUNT(*) AS `count`\r\n\t\t\tFROM `users`\r\n\r\n\t\t\t{$conditional}\r\n\t\t");
// Bind any conditional parameters
if (!is_null($conditional)) {
foreach (self::$search_criteria as $key => $criteria) {
if ($criteria['enabled']) {
$query->bindValue($criteria['parameter'], $criteria['value']);
}
}
}
$query->execute();
$row = $query->row();
return $row['count'];
}
/**
* Validates and sets a user's password
*
* @param int $uid
* @param string $password
* @return boolean
* @throws UserManagementException
*/
public static function changePassword($id, $password)
{
// Validate password
if (!self::validatePassword($password)) {
return false;
}
// Hash password
$password = password_hash($password, PASSWORD_DEFAULT);
// Generate new API Token
$api_token = API::generateAPIToken();
$query = new \TaskerMAN\Core\DBQuery("UPDATE `users` SET \r\n\t\t\t`password` = :password,\r\n\t\t\t`api_token` = :api_token\r\n\r\n\t\t\tWHERE `id` = :id\r\n\t\t\tLIMIT 1\r\n\t\t");
$query->bindValue(':password', $password);
$query->bindValue(':api_token', $api_token);
$query->bindValue(':id', $id);
$query->execute();
return true;
}
