Improve class ProjectDailyColumnStats

1 files changed, 165 insertions, 115 deletions

diff --git a/app/Model/ProjectDailyColumnStats.php b/app/Model/ProjectDailyColumnStats.php
index 7c89283d..c4a3f28e 100644
--- a/app/Model/ProjectDailyColumnStats.php
+++ b/app/Model/ProjectDailyColumnStats.php
@@ -18,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
@@ -30,48 +30,17 @@ 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);
-
         $this->db->startTransaction();
-
-        $column_ids = $this->db->table(Board::TABLE)->eq('project_id', $project_id)->findAllByColumn('id');
-
-        foreach ($column_ids as $column_id) {
-
-            $exists = $this->db->table(ProjectDailyColumnStats::TABLE)
-                ->eq('project_id', $project_id)
-                ->eq('column_id', $column_id)
-                ->eq('day', $date)
-                ->exists();
-
-            $score = $this->db->table(Task::TABLE)
-                ->eq('project_id', $project_id)
-                ->eq('column_id', $column_id)
-                ->eq('is_active', Task::STATUS_OPEN)
-                ->sum('score');
-
-            $total = $this->db->table(Task::TABLE)
-                ->eq('project_id', $project_id)
-                ->eq('column_id', $column_id)
-                ->in('is_active', $status)
-                ->count();
-
-            if ($exists) {
-                $this->db->table(ProjectDailyColumnStats::TABLE)
-                    ->eq('project_id', $project_id)
-                    ->eq('column_id', $column_id)
-                    ->eq('day', $date)
-                    ->update(array('score' => $score, 'total' => $total));
-
-            } else {
-                $this->db->table(ProjectDailyColumnStats::TABLE)->insert(array(
-                    'day' => $date,
-                    'project_id' => $project_id,
-                    'column_id' => $column_id,
-                    'total' => $total,
-                    'score' => $score,
-                ));
-            }
+        $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();
@@ -90,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->board->getColumnsList($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
@@ -134,72 +98,158 @@ 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;
+    }
+
+    /**
+     * 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;
+    }
 
-            foreach ($column_ids as $column_id) {
-                $row[] = (int) $aggregate[$column_id];
+    /**
+     * Find the value in the metrics
+     *
+     * @access private
+     * @param  array   $metrics
+     * @param  string  $day
+     * @param  string  $column_id
+     * @param  string  $field
+     * @return array
+     */
+    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];
             }
+        }
 
-            $metrics[] = $row;
+        return 0;
+    }
+
+    /**
+     * 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);
+        }
+
+        foreach ($scores as $column_id => $score) {
+            if (isset($columns[$column_id])) {
+                $columns[$column_id]['score'] = $score;
+            } else {
+                $columns[$column_id] = array('score' => $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)
+            ->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);
     }
 }