Task age and time spent in one column.

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

1 files changed, 32 insertions, 1 deletions

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)
 {