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