1<?php 2 3namespace Kanboard\Model; 4 5use DateTime; 6use Kanboard\Core\Base; 7 8/** 9 * Subtask time tracking 10 * 11 * @package Kanboard\Model 12 * @author Frederic Guillot 13 */ 14class SubtaskTimeTrackingModel extends Base 15{ 16 /** 17 * SQL table name 18 * 19 * @var string 20 */ 21 const TABLE = 'subtask_time_tracking'; 22 23 /** 24 * Get query to check if a timer is started for the given user and subtask 25 * 26 * @access public 27 * @param integer $user_id User id 28 * @return string 29 */ 30 public function getTimerQuery($user_id) 31 { 32 return sprintf( 33 "SELECT %s FROM %s WHERE %s='%d' AND %s='0' AND %s=%s LIMIT 1", 34 $this->db->escapeIdentifier('start'), 35 $this->db->escapeIdentifier(self::TABLE), 36 $this->db->escapeIdentifier('user_id'), 37 $user_id, 38 $this->db->escapeIdentifier('end'), 39 $this->db->escapeIdentifier('subtask_id'), 40 SubtaskModel::TABLE.'.id' 41 ); 42 } 43 44 /** 45 * Get query for user timesheet (pagination) 46 * 47 * @access public 48 * @param integer $user_id User id 49 * @return \PicoDb\Table 50 */ 51 public function getUserQuery($user_id) 52 { 53 return $this->db 54 ->table(self::TABLE) 55 ->columns( 56 self::TABLE.'.id', 57 self::TABLE.'.subtask_id', 58 self::TABLE.'.end', 59 self::TABLE.'.start', 60 self::TABLE.'.time_spent', 61 SubtaskModel::TABLE.'.task_id', 62 SubtaskModel::TABLE.'.title AS subtask_title', 63 TaskModel::TABLE.'.title AS task_title', 64 TaskModel::TABLE.'.project_id', 65 TaskModel::TABLE.'.color_id' 66 ) 67 ->join(SubtaskModel::TABLE, 'id', 'subtask_id') 68 ->join(TaskModel::TABLE, 'id', 'task_id', SubtaskModel::TABLE) 69 ->eq(self::TABLE.'.user_id', $user_id); 70 } 71 72 /** 73 * Get query for task timesheet (pagination) 74 * 75 * @access public 76 * @param integer $task_id Task id 77 * @return \PicoDb\Table 78 */ 79 public function getTaskQuery($task_id) 80 { 81 return $this->db 82 ->table(self::TABLE) 83 ->columns( 84 self::TABLE.'.id', 85 self::TABLE.'.subtask_id', 86 self::TABLE.'.end', 87 self::TABLE.'.start', 88 self::TABLE.'.time_spent', 89 self::TABLE.'.user_id', 90 SubtaskModel::TABLE.'.task_id', 91 SubtaskModel::TABLE.'.title AS subtask_title', 92 TaskModel::TABLE.'.project_id', 93 UserModel::TABLE.'.username', 94 UserModel::TABLE.'.name AS user_fullname' 95 ) 96 ->join(SubtaskModel::TABLE, 'id', 'subtask_id') 97 ->join(TaskModel::TABLE, 'id', 'task_id', SubtaskModel::TABLE) 98 ->join(UserModel::TABLE, 'id', 'user_id', self::TABLE) 99 ->eq(TaskModel::TABLE.'.id', $task_id); 100 } 101 102 /** 103 * Get all recorded time slots for a given user 104 * 105 * @access public 106 * @param integer $user_id User id 107 * @return array 108 */ 109 public function getUserTimesheet($user_id) 110 { 111 return $this->db 112 ->table(self::TABLE) 113 ->eq('user_id', $user_id) 114 ->findAll(); 115 } 116 117 /** 118 * Return true if a timer is started for this use and subtask 119 * 120 * @access public 121 * @param integer $subtask_id 122 * @param integer $user_id 123 * @return boolean 124 */ 125 public function hasTimer($subtask_id, $user_id) 126 { 127 return $this->db->table(self::TABLE)->eq('subtask_id', $subtask_id)->eq('user_id', $user_id)->eq('end', 0)->exists(); 128 } 129 130 /** 131 * Start or stop timer according to subtask status 132 * 133 * @access public 134 * @param integer $subtask_id 135 * @param integer $user_id 136 * @param integer $status 137 * @return boolean 138 */ 139 public function toggleTimer($subtask_id, $user_id, $status) 140 { 141 if ($this->configModel->get('subtask_time_tracking') == 1) { 142 if ($status == SubtaskModel::STATUS_INPROGRESS) { 143 return $this->subtaskTimeTrackingModel->logStartTime($subtask_id, $user_id); 144 } elseif ($status == SubtaskModel::STATUS_DONE) { 145 return $this->subtaskTimeTrackingModel->logEndTime($subtask_id, $user_id); 146 } 147 } 148 149 return false; 150 } 151 152 /** 153 * Log start time 154 * 155 * @access public 156 * @param integer $subtask_id 157 * @param integer $user_id 158 * @return boolean 159 */ 160 public function logStartTime($subtask_id, $user_id) 161 { 162 return 163 ! $this->hasTimer($subtask_id, $user_id) && 164 $this->db 165 ->table(self::TABLE) 166 ->insert(array('subtask_id' => $subtask_id, 'user_id' => $user_id, 'start' => time(), 'end' => 0)); 167 } 168 169 /** 170 * Log end time 171 * 172 * @access public 173 * @param integer $subtask_id 174 * @param integer $user_id 175 * @return boolean 176 */ 177 public function logEndTime($subtask_id, $user_id) 178 { 179 $time_spent = $this->getTimeSpent($subtask_id, $user_id); 180 181 if ($time_spent > 0) { 182 $this->updateSubtaskTimeSpent($subtask_id, $time_spent); 183 } 184 185 return $this->db 186 ->table(self::TABLE) 187 ->eq('subtask_id', $subtask_id) 188 ->eq('user_id', $user_id) 189 ->eq('end', 0) 190 ->update(array( 191 'end' => time(), 192 'time_spent' => $time_spent, 193 )); 194 } 195 196 /** 197 * Calculate the time spent when the clock is stopped 198 * 199 * @access public 200 * @param integer $subtask_id 201 * @param integer $user_id 202 * @return float 203 */ 204 public function getTimeSpent($subtask_id, $user_id) 205 { 206 $hook = 'model:subtask-time-tracking:calculate:time-spent'; 207 $start_time = $this->db 208 ->table(self::TABLE) 209 ->eq('subtask_id', $subtask_id) 210 ->eq('user_id', $user_id) 211 ->eq('end', 0) 212 ->findOneColumn('start'); 213 214 if (empty($start_time)) { 215 return 0; 216 } 217 218 $end = new DateTime; 219 $start = new DateTime; 220 $start->setTimestamp($start_time); 221 222 if ($this->hook->exists($hook)) { 223 return $this->hook->first($hook, array( 224 'user_id' => $user_id, 225 'start' => $start, 226 'end' => $end, 227 )); 228 } 229 230 return $this->dateParser->getHours($start, $end); 231 } 232 233 /** 234 * Update subtask time spent 235 * 236 * @access public 237 * @param integer $subtask_id 238 * @param float $time_spent 239 * @return bool 240 */ 241 public function updateSubtaskTimeSpent($subtask_id, $time_spent) 242 { 243 $subtask = $this->subtaskModel->getById($subtask_id); 244 245 return $this->subtaskModel->update(array( 246 'id' => $subtask['id'], 247 'time_spent' => $subtask['time_spent'] + $time_spent, 248 'task_id' => $subtask['task_id'], 249 ), false); 250 } 251 252 /** 253 * Update task time tracking based on subtasks time tracking 254 * 255 * @access public 256 * @param integer $task_id Task id 257 * @return bool 258 */ 259 public function updateTaskTimeTracking($task_id) 260 { 261 $values = $this->calculateSubtaskTime($task_id); 262 263 return $this->db 264 ->table(TaskModel::TABLE) 265 ->eq('id', $task_id) 266 ->update($values); 267 } 268 269 /** 270 * Sum time spent and time estimated for all subtasks 271 * 272 * @access public 273 * @param integer $task_id Task id 274 * @return array 275 */ 276 public function calculateSubtaskTime($task_id) 277 { 278 return $this->db 279 ->table(SubtaskModel::TABLE) 280 ->eq('task_id', $task_id) 281 ->columns( 282 'SUM(time_spent) AS time_spent', 283 'SUM(time_estimated) AS time_estimated' 284 ) 285 ->findOne(); 286 } 287} 288