Merge pull request #1 from fguillot/master

Update from upstream

1 files changed, 168 insertions, 112 deletions

diff --git a/app/Model/ProjectDailyColumnStats.php b/app/Model/ProjectDailyColumnStats.php
index 8ed6137f..2bcc4d55 100644
--- a/app/Model/ProjectDailyColumnStats.php
+++ b/app/Model/ProjectDailyColumnStats.php
@@ -2,8 +2,6 @@
 
 namespace Kanboard\Model;
 
-use PicoDb\Database;
-
 /**
  * Project Daily Column Stats
  *
@@ -20,7 +18,7 @@ class ProjectDailyColumnStats extends Base
     const TABLE = 'project_daily_column_stats';
 
     /**
-     * Update daily totals for the project and foreach column
+     * Update daily totals for the project and for each column
      *
      * "total" is the number open of tasks in the column
      * "score" is the sum of tasks score in the column
@@ -32,42 +30,22 @@ class ProjectDailyColumnStats extends Base
      */
     public function updateTotals($project_id, $date)
     {
-        $status = $this->config->get('cfd_include_closed_tasks') == 1 ? array(Task::STATUS_OPEN, Task::STATUS_CLOSED) : array(Task::STATUS_OPEN);
-
-        return $this->db->transaction(function (Database $db) use ($project_id, $date, $status) {
-
-            $column_ids = $db->table(Board::TABLE)->eq('project_id', $project_id)->findAllByColumn('id');
-
-            foreach ($column_ids as $column_id) {
-
-                // This call will fail if the record already exists
-                // (cross database driver hack for INSERT..ON DUPLICATE KEY UPDATE)
-                $db->table(ProjectDailyColumnStats::TABLE)->insert(array(
-                    'day' => $date,
-                    'project_id' => $project_id,
-                    'column_id' => $column_id,
-                    'total' => 0,
-                    'score' => 0,
-                ));
-
-                $db->table(ProjectDailyColumnStats::TABLE)
-                    ->eq('project_id', $project_id)
-                    ->eq('column_id', $column_id)
-                    ->eq('day', $date)
-                    ->update(array(
-                        'score' => $db->table(Task::TABLE)
-                                      ->eq('project_id', $project_id)
-                                      ->eq('column_id', $column_id)
-                                      ->eq('is_active', Task::STATUS_OPEN)
-                                      ->sum('score'),
-                        'total' => $db->table(Task::TABLE)
-                                      ->eq('project_id', $project_id)
-                                      ->eq('column_id', $column_id)
-                                      ->in('is_active', $status)
-                                      ->count()
-                    ));
-            }
-        });
+        $this->db->startTransaction();
+        $this->db->table(self::TABLE)->eq('project_id', $project_id)->eq('day', $date)->remove();
+
+        foreach ($this->getStatsByColumns($project_id) as $column_id => $column) {
+            $this->db->table(self::TABLE)->insert(array(
+                'day' => $date,
+                'project_id' => $project_id,
+                'column_id' => $column_id,
+                'total' => $column['total'],
+                'score' => $column['score'],
+            ));
+        }
+
+        $this->db->closeTransaction();
+
+        return true;
     }
 
     /**
@@ -81,43 +59,38 @@ class ProjectDailyColumnStats extends Base
      */
     public function countDays($project_id, $from, $to)
     {
-        $rq = $this->db->execute(
-            'SELECT COUNT(DISTINCT day) FROM '.self::TABLE.' WHERE day >= ? AND day <= ? AND project_id=?',
-            array($from, $to, $project_id)
-        );
-
-        return $rq !== false ? $rq->fetchColumn(0) : 0;
+        return $this->db->table(self::TABLE)
+            ->eq('project_id', $project_id)
+            ->gte('day', $from)
+            ->lte('day', $to)
+            ->findOneColumn('COUNT(DISTINCT day)');
     }
 
     /**
-     * Get raw metrics for the project within a data range
+     * Get aggregated metrics for the project within a data range
+     *
+     * [
+     *    ['Date', 'Column1', 'Column2'],
+     *    ['2014-11-16', 2, 5],
+     *    ['2014-11-17', 20, 15],
+     * ]
      *
      * @access public
      * @param  integer    $project_id    Project id
      * @param  string     $from          Start date (ISO format YYYY-MM-DD)
      * @param  string     $to            End date
+     * @param  string     $field         Column to aggregate
      * @return array
      */
-    public function getRawMetrics($project_id, $from, $to)
+    public function getAggregatedMetrics($project_id, $from, $to, $field = 'total')
     {
-        return $this->db->table(ProjectDailyColumnStats::TABLE)
-                        ->columns(
-                            ProjectDailyColumnStats::TABLE.'.column_id',
-                            ProjectDailyColumnStats::TABLE.'.day',
-                            ProjectDailyColumnStats::TABLE.'.total',
-                            ProjectDailyColumnStats::TABLE.'.score',
-                            Board::TABLE.'.title AS column_title'
-                        )
-                        ->join(Board::TABLE, 'id', 'column_id')
-                        ->eq(ProjectDailyColumnStats::TABLE.'.project_id', $project_id)
-                        ->gte('day', $from)
-                        ->lte('day', $to)
-                        ->asc(ProjectDailyColumnStats::TABLE.'.day')
-                        ->findAll();
+        $columns = $this->column->getList($project_id);
+        $metrics = $this->getMetrics($project_id, $from, $to);
+        return $this->buildAggregate($metrics, $columns, $field);
     }
 
     /**
-     * Get raw metrics for the project within a data range grouped by day
+     * Fetch metrics
      *
      * @access public
      * @param  integer    $project_id    Project id
@@ -125,72 +98,155 @@ class ProjectDailyColumnStats extends Base
      * @param  string     $to            End date
      * @return array
      */
-    public function getRawMetricsByDay($project_id, $from, $to)
+    public function getMetrics($project_id, $from, $to)
     {
-        return $this->db->table(ProjectDailyColumnStats::TABLE)
-                        ->columns(
-                            ProjectDailyColumnStats::TABLE.'.day',
-                            'SUM('.ProjectDailyColumnStats::TABLE.'.total) AS total',
-                            'SUM('.ProjectDailyColumnStats::TABLE.'.score) AS score'
-                        )
-                        ->eq(ProjectDailyColumnStats::TABLE.'.project_id', $project_id)
-                        ->gte('day', $from)
-                        ->lte('day', $to)
-                        ->asc(ProjectDailyColumnStats::TABLE.'.day')
-                        ->groupBy(ProjectDailyColumnStats::TABLE.'.day')
-                        ->findAll();
+        return $this->db->table(self::TABLE)
+            ->eq('project_id', $project_id)
+            ->gte('day', $from)
+            ->lte('day', $to)
+            ->asc(self::TABLE.'.day')
+            ->findAll();
     }
 
     /**
-     * Get aggregated metrics for the project within a data range
-     *
-     * [
-     *    ['Date', 'Column1', 'Column2'],
-     *    ['2014-11-16', 2, 5],
-     *    ['2014-11-17', 20, 15],
-     * ]
+     * Build aggregate
      *
-     * @access public
-     * @param  integer    $project_id    Project id
-     * @param  string     $from          Start date (ISO format YYYY-MM-DD)
-     * @param  string     $to            End date
-     * @param  string     $column        Column to aggregate
+     * @access private
+     * @param  array   $metrics
+     * @param  array   $columns
+     * @param  string  $field
      * @return array
      */
-    public function getAggregatedMetrics($project_id, $from, $to, $column = 'total')
+    private function buildAggregate(array &$metrics, array &$columns, $field)
     {
-        $columns = $this->board->getColumnsList($project_id);
         $column_ids = array_keys($columns);
-        $metrics = array(array_merge(array(e('Date')), array_values($columns)));
-        $aggregates = array();
-
-        // Fetch metrics for the project
-        $records = $this->db->table(ProjectDailyColumnStats::TABLE)
-                            ->eq('project_id', $project_id)
-                            ->gte('day', $from)
-                            ->lte('day', $to)
-                            ->findAll();
-
-        // Aggregate by day
-        foreach ($records as $record) {
-            if (! isset($aggregates[$record['day']])) {
-                $aggregates[$record['day']] = array($record['day']);
-            }
+        $days = array_unique(array_column($metrics, 'day'));
+        $rows = array(array_merge(array(e('Date')), array_values($columns)));
 
-            $aggregates[$record['day']][$record['column_id']] = $record[$column];
+        foreach ($days as $day) {
+            $rows[] = $this->buildRowAggregate($metrics, $column_ids, $day, $field);
         }
 
-        // Aggregate by row
-        foreach ($aggregates as $aggregate) {
-            $row = array($aggregate[0]);
+        return $rows;
+    }
 
-            foreach ($column_ids as $column_id) {
-                $row[] = (int) $aggregate[$column_id];
+    /**
+     * Build one row of the aggregate
+     *
+     * @access private
+     * @param  array   $metrics
+     * @param  array   $column_ids
+     * @param  string  $day
+     * @param  string  $field
+     * @return array
+     */
+    private function buildRowAggregate(array &$metrics, array &$column_ids, $day, $field)
+    {
+        $row = array($day);
+
+        foreach ($column_ids as $column_id) {
+            $row[] = $this->findValueInMetrics($metrics, $day, $column_id, $field);
+        }
+
+        return $row;
+    }
+
+    /**
+     * Find the value in the metrics
+     *
+     * @access private
+     * @param  array   $metrics
+     * @param  string  $day
+     * @param  string  $column_id
+     * @param  string  $field
+     * @return integer
+     */
+    private function findValueInMetrics(array &$metrics, $day, $column_id, $field)
+    {
+        foreach ($metrics as $metric) {
+            if ($metric['day'] === $day && $metric['column_id'] == $column_id) {
+                return $metric[$field];
             }
+        }
+
+        return 0;
+    }
 
-            $metrics[] = $row;
+    /**
+     * Get number of tasks and score by columns
+     *
+     * @access private
+     * @param  integer $project_id
+     * @return array
+     */
+    private function getStatsByColumns($project_id)
+    {
+        $totals = $this->getTotalByColumns($project_id);
+        $scores = $this->getScoreByColumns($project_id);
+        $columns = array();
+
+        foreach ($totals as $column_id => $total) {
+            $columns[$column_id] = array('total' => $total, 'score' => 0);
+        }
+
+        foreach ($scores as $column_id => $score) {
+            $columns[$column_id]['score'] = (int) $score;
+        }
+
+        return $columns;
+    }
+
+    /**
+     * Get number of tasks and score by columns
+     *
+     * @access private
+     * @param  integer $project_id
+     * @return array
+     */
+    private function getScoreByColumns($project_id)
+    {
+        $stats = $this->db->table(Task::TABLE)
+            ->columns('column_id', 'SUM(score) AS score')
+            ->eq('project_id', $project_id)
+            ->eq('is_active', Task::STATUS_OPEN)
+            ->notNull('score')
+            ->groupBy('column_id')
+            ->findAll();
+
+        return array_column($stats, 'score', 'column_id');
+    }
+
+    /**
+     * Get number of tasks and score by columns
+     *
+     * @access private
+     * @param  integer $project_id
+     * @return array
+     */
+    private function getTotalByColumns($project_id)
+    {
+        $stats = $this->db->table(Task::TABLE)
+            ->columns('column_id', 'COUNT(*) AS total')
+            ->eq('project_id', $project_id)
+            ->in('is_active', $this->getTaskStatusConfig())
+            ->groupBy('column_id')
+            ->findAll();
+
+        return array_column($stats, 'total', 'column_id');
+    }
+
+    /**
+     * Get task status to use for total calculation
+     *
+     * @access private
+     * @return array
+     */
+    private function getTaskStatusConfig()
+    {
+        if ($this->config->get('cfd_include_closed_tasks') == 1) {
+            return array(Task::STATUS_OPEN, Task::STATUS_CLOSED);
         }
 
-        return $metrics;
+        return array(Task::STATUS_OPEN);
     }
 }