1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
|
<?php
namespace Kanboard\Model;
use Kanboard\Core\Base;
/**
* Transition
*
* @package model
* @author Frederic Guillot
*/
class Transition extends Base
{
/**
* SQL table name
*
* @var string
*/
const TABLE = 'transitions';
/**
* Save transition event
*
* @access public
* @param integer $user_id
* @param array $task_event
* @return bool
*/
public function save($user_id, array $task_event)
{
$time = time();
return $this->db->table(self::TABLE)->insert(array(
'user_id' => $user_id,
'project_id' => $task_event['project_id'],
'task_id' => $task_event['task_id'],
'src_column_id' => $task_event['src_column_id'],
'dst_column_id' => $task_event['dst_column_id'],
'date' => $time,
'time_spent' => $time - $task_event['date_moved']
));
}
/**
* Get time spent by task for each column
*
* @access public
* @param integer $task_id
* @return array
*/
public function getTimeSpentByTask($task_id)
{
return $this->db
->hashtable(self::TABLE)
->groupBy('src_column_id')
->eq('task_id', $task_id)
->getAll('src_column_id', 'SUM(time_spent) AS time_spent');
}
/**
* Get all transitions by task
*
* @access public
* @param integer $task_id
* @return array
*/
public function getAllByTask($task_id)
{
return $this->db->table(self::TABLE)
->columns(
'src.title as src_column',
'dst.title as dst_column',
User::TABLE.'.name',
User::TABLE.'.username',
self::TABLE.'.user_id',
self::TABLE.'.date',
self::TABLE.'.time_spent'
)
->eq('task_id', $task_id)
->desc('date')
->join(User::TABLE, 'id', 'user_id')
->join(Column::TABLE.' as src', 'id', 'src_column_id', self::TABLE, 'src')
->join(Column::TABLE.' as dst', 'id', 'dst_column_id', self::TABLE, 'dst')
->findAll();
}
/**
* Get all transitions by project
*
* @access public
* @param integer $project_id
* @param mixed $from Start date (timestamp or user formatted date)
* @param mixed $to End date (timestamp or user formatted date)
* @return array
*/
public function getAllByProjectAndDate($project_id, $from, $to)
{
if (! is_numeric($from)) {
$from = $this->dateParser->removeTimeFromTimestamp($this->dateParser->getTimestamp($from));
}
if (! is_numeric($to)) {
$to = $this->dateParser->removeTimeFromTimestamp(strtotime('+1 day', $this->dateParser->getTimestamp($to)));
}
return $this->db->table(self::TABLE)
->columns(
Task::TABLE.'.id',
Task::TABLE.'.title',
'src.title as src_column',
'dst.title as dst_column',
User::TABLE.'.name',
User::TABLE.'.username',
self::TABLE.'.user_id',
self::TABLE.'.date',
self::TABLE.'.time_spent'
)
->gte('date', $from)
->lte('date', $to)
->eq(self::TABLE.'.project_id', $project_id)
->desc('date')
->desc(self::TABLE.'.id')
->join(Task::TABLE, 'id', 'task_id')
->join(User::TABLE, 'id', 'user_id')
->join(Column::TABLE.' as src', 'id', 'src_column_id', self::TABLE, 'src')
->join(Column::TABLE.' as dst', 'id', 'dst_column_id', self::TABLE, 'dst')
->findAll();
}
}
|
