Add new analytic page: Average time spent into each column

3 files changed, 63 insertions, 11 deletions

diff --git a/app/Model/ProjectAnalytic.php b/app/Model/ProjectAnalytic.php
index a663f921..f4e8af09 100644
--- a/app/Model/ProjectAnalytic.php
+++ b/app/Model/ProjectAnalytic.php
@@ -49,7 +49,7 @@ class ProjectAnalytic extends Base
      * Get users repartition
      *
      * @access public
-     * @param  integer   $project_id    Project id
+     * @param  integer   $project_id
      * @return array
      */
     public function getUserRepartition($project_id)
@@ -87,4 +87,59 @@ class ProjectAnalytic extends Base
 
         return array_values($metrics);
     }
+
+    /**
+     * Get the average time spent into each column
+     *
+     * @access public
+     * @param  integer   $project_id
+     * @return array
+     */
+    public function getAverageTimeSpentByColumn($project_id)
+    {
+        $stats = array();
+        $columns = $this->board->getColumnsList($project_id);
+
+        // Get the time spent of the last move for each tasks
+        $tasks = $this->db
+            ->table(Task::TABLE)
+            ->columns('id', 'date_completed', 'date_moved', 'column_id')
+            ->eq('project_id', $project_id)
+            ->desc('id')
+            ->limit(1000)
+            ->findAll();
+
+        // Init values
+        foreach ($columns as $column_id => $column_title) {
+            $stats[$column_id] = array(
+                'count' => 0,
+                'time_spent' => 0,
+                'average' => 0,
+                'title' => $column_title,
+            );
+        }
+
+        // Get time spent foreach task/column and take into account the last move
+        foreach ($tasks as &$task) {
+            $sums = $this->transition->getTimeSpentByTask($task['id']);
+
+            if (! isset($sums[$task['column_id']])) {
+                $sums[$task['column_id']] = 0;
+            }
+
+            $sums[$task['column_id']] += ($task['date_completed'] ?: time()) - $task['date_moved'];
+
+            foreach ($sums as $column_id => $time_spent) {
+                $stats[$column_id]['count']++;
+                $stats[$column_id]['time_spent'] += $time_spent;
+            }
+        }
+
+        // Calculate average for each column
+        foreach ($columns as $column_id => $column_title) {
+            $stats[$column_id]['average'] = (int) ($stats[$column_id]['time_spent'] / $stats[$column_id]['count']);
+        }
+
+        return $stats;
+    }
 }
diff --git a/app/Model/TaskAnalytic.php b/app/Model/TaskAnalytic.php
index 41579c7d..33a645c1 100644
--- a/app/Model/TaskAnalytic.php
+++ b/app/Model/TaskAnalytic.php
@@ -45,21 +45,18 @@ class TaskAnalytic extends Base
      * @param  array   $task
      * @return array
      */
-    public function getAverageTimeByColumn(array $task)
+    public function getTimeSpentByColumn(array $task)
     {
         $result = array();
         $columns = $this->board->getColumnsList($task['project_id']);
-        $averages = $this->transition->getAverageTimeSpentByTask($task['id']);
+        $sums = $this->transition->getTimeSpentByTask($task['id']);
 
         foreach ($columns as $column_id => $column_title) {
 
-            $time_spent = 0;
+            $time_spent = isset($sums[$column_id]) ? $sums[$column_id] : 0;
 
-            if (empty($averages) && $task['column_id'] == $column_id) {
-                $time_spent = time() - $task['date_creation'];
-            }
-            else {
-                $time_spent = isset($averages[$column_id]) ? $averages[$column_id] : 0;
+            if ($task['column_id'] == $column_id) {
+                $time_spent += ($task['date_completed'] ?: time()) - $task['date_moved'];
             }
 
             $result[] = array(
diff --git a/app/Model/Transition.php b/app/Model/Transition.php
index 959b6aca..ac3fba54 100644
--- a/app/Model/Transition.php
+++ b/app/Model/Transition.php
@@ -39,13 +39,13 @@ class Transition extends Base
     }
 
     /**
-     * Get average time spent by task for each column
+     * Get time spent by task for each column
      *
      * @access public
      * @param  integer  $task_id
      * @return array
      */
-    public function getAverageTimeSpentByTask($task_id)
+    public function getTimeSpentByTask($task_id)
     {
         return $this->db
                     ->hashtable(self::TABLE)