Task age and time spent in one column.

Details: https://github.com/fguillot/kanboard/issues/608

9 files changed, 147 insertions, 5 deletions

diff --git a/app/Model/TaskCreation.php b/app/Model/TaskCreation.php
index 17e5ff70..893cbc43 100644
--- a/app/Model/TaskCreation.php
+++ b/app/Model/TaskCreation.php
@@ -62,6 +62,7 @@ class TaskCreation extends Base
         $values['swimlane_id'] = empty($values['swimlane_id']) ? 0 : $values['swimlane_id'];
         $values['date_creation'] = time();
         $values['date_modification'] = $values['date_creation'];
+        $values['date_moved'] = $values['date_creation'];
         $values['position'] = $this->taskFinder->countByColumnAndSwimlaneId($values['project_id'], $values['column_id'], $values['swimlane_id']) + 1;
     }
 
diff --git a/app/Model/TaskFinder.php b/app/Model/TaskFinder.php
index 4ed196ef..05168593 100644
--- a/app/Model/TaskFinder.php
+++ b/app/Model/TaskFinder.php
@@ -102,6 +102,7 @@ class TaskFinder extends Base
                 'tasks.is_active',
                 'tasks.score',
                 'tasks.category_id',
+            	'tasks.date_moved',
                 'users.username AS assignee_username',
                 'users.name AS assignee_name'
             )
@@ -242,6 +243,7 @@ class TaskFinder extends Base
             tasks.score,
             tasks.category_id,
             tasks.swimlane_id,
+        	tasks.date_moved,
             project_has_categories.name AS category_name,
             projects.name AS project_name,
             columns.title AS column_title,
diff --git a/app/Model/TaskPosition.php b/app/Model/TaskPosition.php
index 2c271de7..9733999b 100644
--- a/app/Model/TaskPosition.php
+++ b/app/Model/TaskPosition.php
@@ -30,7 +30,10 @@ class TaskPosition extends Base
         $result = $this->calculateAndSave($project_id, $task_id, $column_id, $position, $swimlane_id);
 
         if ($result) {
-
+            if ($original_task['column_id'] != $column_id) {
+            	$this->db->table(Task::TABLE)->eq('id', $task_id)->update(array('date_moved' => time(),
+                    ));
+            }
             if ($original_task['swimlane_id'] != $swimlane_id) {
                 $this->calculateAndSave($project_id, 0, $column_id, 1, $original_task['swimlane_id']);
             }
diff --git a/app/Schema/Mysql.php b/app/Schema/Mysql.php
index 9c066625..e4be05b7 100644
--- a/app/Schema/Mysql.php
+++ b/app/Schema/Mysql.php
@@ -5,7 +5,38 @@ namespace Schema;
 use PDO;
 use Core\Security;
 
-const VERSION = 44;
+const VERSION = 45;
+
+function version_45($pdo)
+{
+	$pdo->exec('ALTER TABLE tasks ADD COLUMN date_moved INT DEFAULT 0');
+	
+	/* Update tasks.date_moved from project_activities table if tasks.date_moved = null or 0.
+	 * We take max project_activities.date_creation where event_name in task.create','task.move.column
+	 * since creation date is always less than task moves
+	 */
+	$pdo->exec("UPDATE tasks
+				SET date_moved = (
+			        SELECT md
+                    FROM (
+                      SELECT task_id, max(date_creation) md
+                      FROM project_activities
+                      WHERE event_name IN ('task.create', 'task.move.column')
+                      GROUP BY task_id
+                      ) src
+                    WHERE id = src.task_id
+                    )
+                WHERE (date_moved IS NULL OR date_moved = 0) AND id IN (
+                    SELECT task_id
+                    FROM (
+                      SELECT task_id, max(date_creation) md
+                      FROM project_activities
+                      WHERE event_name IN ('task.create', 'task.move.column')
+                      GROUP BY task_id
+                      ) src
+                    )
+            ");
+}
 
 function version_44($pdo)
 {
diff --git a/app/Schema/Postgres.php b/app/Schema/Postgres.php
index 52130c96..92587273 100644
--- a/app/Schema/Postgres.php
+++ b/app/Schema/Postgres.php
@@ -5,7 +5,38 @@ namespace Schema;
 use PDO;
 use Core\Security;
 
-const VERSION = 25;
+const VERSION = 26;
+
+function version_26($pdo)
+{
+	$pdo->exec('ALTER TABLE tasks ADD COLUMN date_moved INT DEFAULT 0');
+
+	/* Update tasks.date_moved from project_activities table if tasks.date_moved = null or 0.
+	 * We take max project_activities.date_creation where event_name in task.create','task.move.column
+	 * since creation date is always less than task moves
+	*/
+	$pdo->exec("UPDATE tasks
+				SET date_moved = (
+			        SELECT md
+                    FROM (
+                      SELECT task_id, max(date_creation) md
+                      FROM project_activities
+                      WHERE event_name IN ('task.create', 'task.move.column')
+                      GROUP BY task_id
+                      ) src
+                    WHERE id = src.task_id
+                    )
+                WHERE (date_moved IS NULL OR date_moved = 0) AND id IN (
+                    SELECT task_id
+                    FROM (
+                      SELECT task_id, max(date_creation) md
+                      FROM project_activities
+                      WHERE event_name IN ('task.create', 'task.move.column')
+                      GROUP BY task_id
+                      ) src
+                    )
+            ");
+}
 
 function version_25($pdo)
 {
diff --git a/app/Schema/Sqlite.php b/app/Schema/Sqlite.php
index 37483945..7e0668e3 100644
--- a/app/Schema/Sqlite.php
+++ b/app/Schema/Sqlite.php
@@ -5,7 +5,38 @@ namespace Schema;
 use Core\Security;
 use PDO;
 
-const VERSION = 43;
+const VERSION = 44;
+
+function version_44($pdo)
+{
+	$pdo->exec('ALTER TABLE tasks ADD COLUMN date_moved INTEGER DEFAULT 0');
+	
+	/* Update tasks.date_moved from project_activities table if tasks.date_moved = null or 0.
+	* We take max project_activities.date_creation where event_name in task.create','task.move.column
+	* since creation date is always less than task moves
+	*/  
+	$pdo->exec("UPDATE tasks
+				SET date_moved = (
+			        SELECT md
+                    FROM (
+                      SELECT task_id, max(date_creation) md
+                      FROM project_activities
+                      WHERE event_name IN ('task.create', 'task.move.column')
+                      GROUP BY task_id
+                      ) src
+                    WHERE id = src.task_id
+                    )
+                WHERE (date_moved IS NULL OR date_moved = 0) AND id IN (
+                    SELECT task_id
+                    FROM (
+                      SELECT task_id, max(date_creation) md
+                      FROM project_activities
+                      WHERE event_name IN ('task.create', 'task.move.column')
+                      GROUP BY task_id
+                      ) src
+                    )
+            ");
+}
 
 function version_43($pdo)
 {
diff --git a/app/Template/board/task.php b/app/Template/board/task.php
index 41c7ab45..3d3fb269 100644
--- a/app/Template/board/task.php
+++ b/app/Template/board/task.php
@@ -68,7 +68,10 @@
             t('Change assignee')
         ) ?>
     </span>
-
+    
+    <span title="<?= t('Task age in days')?>" class="task-days-age"><?= floor(time()/86400) - floor($task['date_creation']/86400)?>d</span>
+    <span title="<?= t('Days in this column')?>" class="task-days-incolumn"><?= floor(time()/86400) - floor($task['date_moved']/86400)?>d</span>
+    
     <?php if ($task['score']): ?>
         <span class="task-score"><?= $this->e($task['score']) ?></span>
     <?php endif ?>
diff --git a/assets/css/app.css b/assets/css/app.css
index 950d5746..2b8bb521 100644
--- a/assets/css/app.css
+++ b/assets/css/app.css
@@ -1631,6 +1631,26 @@ a.task-board-nobody {
     margin-bottom: 25px;
     padding: 3px;
 }
+
+.task-days-age {
+	font-size: 0.8em; 
+	border: #888 1px solid; 
+	border-radius:2px;
+	padding:1px 4px 1px 2px;
+    border-top-left-radius: 3px;
+    border-bottom-left-radius: 3px;
+}
+
+.task-days-incolumn {
+	font-size: 0.8em; 
+	border: #888 1px solid; 
+    border-left: none;
+    margin-left: -5px;
+	border-radius:2px;
+	padding:1px 2px 1px 4px;
+    border-top-right-radius: 3px;
+    border-bottom-right-radius: 3px;
+}
 /* comments */
 .comment {
     margin-bottom: 20px;
diff --git a/assets/css/src/task.css b/assets/css/src/task.css
index 1a477370..e069b32c 100644
--- a/assets/css/src/task.css
+++ b/assets/css/src/task.css
@@ -205,3 +205,23 @@ a.task-board-nobody {
     margin-bottom: 25px;
     padding: 3px;
 }
+
+.task-days-age {
+	font-size: 0.8em; 
+	border: #888 1px solid; 
+	border-radius:2px;
+	padding:1px 4px 1px 2px;
+    border-top-left-radius: 3px;
+    border-bottom-left-radius: 3px;
+}
+
+.task-days-incolumn {
+	font-size: 0.8em; 
+	border: #888 1px solid; 
+    border-left: none;
+    margin-left: -5px;
+	border-radius:2px;
+	padding:1px 2px 1px 4px;
+    border-top-right-radius: 3px;
+    border-bottom-right-radius: 3px;
+}