Task age and time spent in one column.

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

3 files changed, 96 insertions, 3 deletions

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