Add new analytic page: Average time spent into each column

9 files changed, 128 insertions, 20 deletions

diff --git a/app/Controller/Analytic.php b/app/Controller/Analytic.php
index 2413ba92..010fda09 100644
--- a/app/Controller/Analytic.php
+++ b/app/Controller/Analytic.php
@@ -3,7 +3,7 @@
 namespace Controller;
 
 /**
- * Project Anaytic controller
+ * Project Analytic controller
  *
  * @package  controller
  * @author   Frederic Guillot
@@ -27,6 +27,22 @@ class Analytic extends Base
     }
 
     /**
+     * Show average time spent by column
+     *
+     * @access public
+     */
+    public function averageTimeByColumn()
+    {
+        $project = $this->getProject();
+
+        $this->response->html($this->layout('analytic/avg_time_columns', array(
+            'project' => $project,
+            'metrics' => $this->projectAnalytic->getAverageTimeSpentByColumn($project['id']),
+            'title' => t('Average time spent into each column for "%s"', $project['name']),
+        )));
+    }
+
+    /**
      * Show tasks distribution graph
      *
      * @access public
diff --git a/app/Controller/Task.php b/app/Controller/Task.php
index b6e4845f..2d4f783a 100644
--- a/app/Controller/Task.php
+++ b/app/Controller/Task.php
@@ -101,7 +101,7 @@ class Task extends Base
             'task' => $task,
             'lead_time' => $this->taskAnalytic->getLeadTime($task),
             'cycle_time' => $this->taskAnalytic->getCycleTime($task),
-            'column_averages' => $this->taskAnalytic->getAverageTimeByColumn($task),
+            'time_spent_columns' => $this->taskAnalytic->getTimeSpentByColumn($task),
         )));
     }
 
diff --git a/app/Helper/Dt.php b/app/Helper/Dt.php
index be595605..b338fdc8 100644
--- a/app/Helper/Dt.php
+++ b/app/Helper/Dt.php
@@ -21,6 +21,10 @@ class Dt extends \Core\Base
      */
     public function duration($seconds)
     {
+        if ($seconds == 0) {
+            return 0;
+        }
+
         $dtF = new DateTime("@0");
         $dtT = new DateTime("@$seconds");
         return $dtF->diff($dtT)->format('%a days, %h hours, %i minutes and %s seconds');
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)
diff --git a/app/Template/analytic/avg_time_columns.php b/app/Template/analytic/avg_time_columns.php
new file mode 100644
index 00000000..e74e7950
--- /dev/null
+++ b/app/Template/analytic/avg_time_columns.php
@@ -0,0 +1,29 @@
+<div class="page-header">
+    <h2><?= t('Average time spent into each column') ?></h2>
+</div>
+
+<?php if (empty($metrics)): ?>
+    <p class="alert"><?= t('Not enough data to show the graph.') ?></p>
+<?php else: ?>
+    <section id="analytic-avg-time-column">
+
+        <div id="chart" data-metrics='<?= json_encode($metrics) ?>' data-label="<?= t('Average time spent') ?>"></div>
+
+        <table class="table-stripped">
+        <tr>
+            <th><?= t('Column') ?></th>
+            <th><?= t('Average time spent') ?></th>
+        </tr>
+        <?php foreach ($metrics as $column): ?>
+        <tr>
+            <td><?= $this->e($column['title']) ?></td>
+            <td><?= $this->dt->duration($column['average']) ?></td>
+        </tr>
+        <?php endforeach ?>
+        </table>
+
+        <p class="alert alert-info">
+            <?= t('This chart show the average time spent into each column for the last %d tasks.', 1000) ?>
+        </p>
+    </section>
+<?php endif ?>
diff --git a/app/Template/analytic/sidebar.php b/app/Template/analytic/sidebar.php
index 2d1a7c96..1ffce710 100644
--- a/app/Template/analytic/sidebar.php
+++ b/app/Template/analytic/sidebar.php
@@ -13,5 +13,8 @@
         <li>
             <?= $this->url->link(t('Burndown chart'), 'analytic', 'burndown', array('project_id' => $project['id'])) ?>
         </li>
+        <li>
+            <?= $this->url->link(t('Average time into each column'), 'analytic', 'averageTimeByColumn', array('project_id' => $project['id'])) ?>
+        </li>
     </ul>
 </div>
\ No newline at end of file
diff --git a/app/Template/task/analytics.php b/app/Template/task/analytics.php
index dbee0e9c..3b1d2855 100644
--- a/app/Template/task/analytics.php
+++ b/app/Template/task/analytics.php
@@ -9,13 +9,14 @@
     </ul>
 </div>
 
-<h3><?= t('Average time spent for each column') ?></h3>
+<h3 id="analytic-task-time-column"><?= t('Time spent into each column') ?></h3>
+<div id="chart" data-metrics='<?= json_encode($time_spent_columns) ?>' data-label="<?= t('Time spent') ?>"></div>
 <table class="table-stripped">
     <tr>
         <th><?= t('Column') ?></th>
-        <th><?= t('Average time spent') ?></th>
+        <th><?= t('Time spent') ?></th>
     </tr>
-    <?php foreach ($column_averages as $column): ?>
+    <?php foreach ($time_spent_columns as $column): ?>
     <tr>
         <td><?= $this->e($column['title']) ?></td>
         <td><?= $this->dt->duration($column['time_spent']) ?></td>
@@ -25,8 +26,11 @@
 
 <div class="alert alert-info">
     <ul>
-        <li><?= t('The lead time is the time between the task creation and the completion.') ?></li>
-        <li><?= t('The cycle time is the time between the start date and the completion.') ?></li>
-        <li><?= t('If the task is not closed the current time is used.') ?></li>
+        <li><?= t('The lead time is the duration between the task creation and the completion.') ?></li>
+        <li><?= t('The cycle time is the duration between the start date and the completion.') ?></li>
+        <li><?= t('If the task is not closed the current time is used instead of the completion date.') ?></li>
     </ul>
-</div>
\ No newline at end of file
+</div>
+
+<?= $this->asset->js('assets/js/vendor/d3.v3.min.js') ?>
+<?= $this->asset->js('assets/js/vendor/c3.min.js') ?>
\ No newline at end of file