Improve class ProjectDailyColumnStats

6 files changed, 427 insertions, 233 deletions

diff --git a/ChangeLog b/ChangeLog
index 1c200c2f..f11edbac 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -11,6 +11,9 @@ Improvements:
 * Add dropdown menu for subtasks, categories, swimlanes, columns, custom filters, task links and groups
 * Add new template hooks
 * Application settings are not cached anymore in the session
+* Move validators to a separate namespace
+* Improve and write unit tests for reports
+* Reduce the number of SQL queries for project daily column stats
 
 Bug fixes:
 
diff --git a/app/Analytic/AverageLeadCycleTimeAnalytic.php b/app/Analytic/AverageLeadCycleTimeAnalytic.php
index 96c803cc..fd85f864 100644
--- a/app/Analytic/AverageLeadCycleTimeAnalytic.php
+++ b/app/Analytic/AverageLeadCycleTimeAnalytic.php
@@ -30,7 +30,9 @@ class AverageLeadCycleTimeAnalytic extends Base
             'avg_cycle_time' => 0,
         );
 
-        foreach ($this->getTasks($project_id) as &$task) {
+        $tasks = $this->getTasks($project_id);
+
+        foreach ($tasks as &$task) {
             $stats['count']++;
             $stats['total_lead_time'] += $this->calculateLeadTime($task);
             $stats['total_cycle_time'] += $this->calculateCycleTime($task);
diff --git a/app/Controller/Analytic.php b/app/Controller/Analytic.php
index 26386029..d203fb8e 100644
--- a/app/Controller/Analytic.php
+++ b/app/Controller/Analytic.php
@@ -35,15 +35,7 @@ class Analytic extends Base
     public function leadAndCycleTime()
     {
         $project = $this->getProject();
-        $values = $this->request->getValues();
-
-        $from = $this->request->getStringParam('from', date('Y-m-d', strtotime('-1week')));
-        $to = $this->request->getStringParam('to', date('Y-m-d'));
-
-        if (! empty($values)) {
-            $from = $values['from'];
-            $to = $values['to'];
-        }
+        list($from, $to) = $this->getDates();
 
         $this->response->html($this->layout('analytic/lead_cycle_time', array(
             'values' => array(
@@ -60,6 +52,32 @@ class Analytic extends Base
     }
 
     /**
+     * Show comparison between actual and estimated hours chart
+     *
+     * @access public
+     */
+    public function compareHours()
+    {
+        $project = $this->getProject();
+        $params = $this->getProjectFilters('analytic', 'compareHours');
+        $query = $this->taskFilter->create()->filterByProject($params['project']['id'])->getQuery();
+
+        $paginator = $this->paginator
+            ->setUrl('analytic', 'compareHours', array('project_id' => $project['id']))
+            ->setMax(30)
+            ->setOrder(TaskModel::TABLE.'.id')
+            ->setQuery($query)
+            ->calculate();
+
+        $this->response->html($this->layout('analytic/compare_hours', array(
+            'project' => $project,
+            'paginator' => $paginator,
+            'metrics' => $this->estimatedTimeComparisonAnalytic->build($project['id']),
+            'title' => t('Compare hours for "%s"', $project['name']),
+        )));
+    }
+
+    /**
      * Show average time spent by column
      *
      * @access public
@@ -138,17 +156,7 @@ class Analytic extends Base
     private function commonAggregateMetrics($template, $column, $title)
     {
         $project = $this->getProject();
-        $values = $this->request->getValues();
-
-        $this->projectDailyColumnStats->updateTotals($project['id'], date('Y-m-d'));
-
-        $from = $this->request->getStringParam('from', date('Y-m-d', strtotime('-1week')));
-        $to = $this->request->getStringParam('to', date('Y-m-d'));
-
-        if (! empty($values)) {
-            $from = $values['from'];
-            $to = $values['to'];
-        }
+        list($from, $to) = $this->getDates();
 
         $display_graph = $this->projectDailyColumnStats->countDays($project['id'], $from, $to) >= 2;
 
@@ -166,29 +174,18 @@ class Analytic extends Base
         )));
     }
 
-    /**
-     * Show comparison between actual and estimated hours chart
-     *
-     * @access public
-     */
-    public function compareHours()
+    private function getDates()
     {
-        $project = $this->getProject();
-        $params = $this->getProjectFilters('analytic', 'compareHours');
-        $query = $this->taskFilter->create()->filterByProject($params['project']['id'])->getQuery();
+        $values = $this->request->getValues();
 
-        $paginator = $this->paginator
-            ->setUrl('analytic', 'compareHours', array('project_id' => $project['id']))
-            ->setMax(30)
-            ->setOrder(TaskModel::TABLE.'.id')
-            ->setQuery($query)
-            ->calculate();
+        $from = $this->request->getStringParam('from', date('Y-m-d', strtotime('-1week')));
+        $to = $this->request->getStringParam('to', date('Y-m-d'));
 
-        $this->response->html($this->layout('analytic/compare_hours', array(
-            'project' => $project,
-            'paginator' => $paginator,
-            'metrics' => $this->estimatedTimeComparisonAnalytic->build($project['id']),
-            'title' => t('Compare hours for "%s"', $project['name']),
-        )));
+        if (! empty($values)) {
+            $from = $values['from'];
+            $to = $values['to'];
+        }
+
+        return array($from, $to);
     }
 }
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);
     }
 }
diff --git a/app/Subscriber/ProjectDailySummarySubscriber.php b/app/Subscriber/ProjectDailySummarySubscriber.php
index bfa6cd42..d9239264 100644
--- a/app/Subscriber/ProjectDailySummarySubscriber.php
+++ b/app/Subscriber/ProjectDailySummarySubscriber.php
@@ -11,11 +11,11 @@ class ProjectDailySummarySubscriber extends \Kanboard\Core\Base implements Event
     public static function getSubscribedEvents()
     {
         return array(
-            Task::EVENT_CREATE => array('execute', 0),
-            Task::EVENT_UPDATE => array('execute', 0),
+            Task::EVENT_CREATE_UPDATE => array('execute', 0),
             Task::EVENT_CLOSE => array('execute', 0),
             Task::EVENT_OPEN => array('execute', 0),
             Task::EVENT_MOVE_COLUMN => array('execute', 0),
+            Task::EVENT_MOVE_SWIMLANE => array('execute', 0),
         );
     }
 
diff --git a/tests/units/Model/ProjectDailyColumnStatsTest.php b/tests/units/Model/ProjectDailyColumnStatsTest.php
index 4c801e02..f8a64054 100644
--- a/tests/units/Model/ProjectDailyColumnStatsTest.php
+++ b/tests/units/Model/ProjectDailyColumnStatsTest.php
@@ -4,6 +4,7 @@ require_once __DIR__.'/../Base.php';
 
 use Kanboard\Model\Project;
 use Kanboard\Model\ProjectDailyColumnStats;
+use Kanboard\Model\Config;
 use Kanboard\Model\Task;
 use Kanboard\Model\TaskCreation;
 use Kanboard\Model\TaskStatus;
@@ -12,79 +13,220 @@ class ProjectDailyColumnStatsTest extends Base
 {
     public function testUpdateTotals()
     {
-        $p = new Project($this->container);
-        $pds = new ProjectDailyColumnStats($this->container);
-        $tc = new TaskCreation($this->container);
-        $ts = new TaskStatus($this->container);
-
-        $this->assertEquals(1, $p->create(array('name' => 'UnitTest')));
-        $this->assertEquals(0, $pds->countDays(1, date('Y-m-d', strtotime('-2days')), date('Y-m-d')));
-
-        for ($i = 0; $i < 10; $i++) {
-            $this->assertNotFalse($tc->create(array('title' => 'Task #'.$i, 'project_id' => 1, 'column_id' => 1)));
-        }
-
-        for ($i = 0; $i < 5; $i++) {
-            $this->assertNotFalse($tc->create(array('title' => 'Task #'.$i, 'project_id' => 1, 'column_id' => 4)));
-        }
-
-        $pds->updateTotals(1, date('Y-m-d', strtotime('-2days')));
-
-        for ($i = 0; $i < 15; $i++) {
-            $this->assertNotFalse($tc->create(array('title' => 'Task #'.$i, 'project_id' => 1, 'column_id' => 3)));
-        }
-
-        for ($i = 0; $i < 25; $i++) {
-            $this->assertNotFalse($tc->create(array('title' => 'Task #'.$i, 'project_id' => 1, 'column_id' => 2)));
-        }
-
-        $pds->updateTotals(1, date('Y-m-d', strtotime('-1 day')));
-
-        $this->assertNotFalse($ts->close(1));
-        $this->assertNotFalse($ts->close(2));
-
-        for ($i = 0; $i < 3; $i++) {
-            $this->assertNotFalse($tc->create(array('title' => 'Task #'.$i, 'project_id' => 1, 'column_id' => 3)));
-        }
-
-        for ($i = 0; $i < 5; $i++) {
-            $this->assertNotFalse($tc->create(array('title' => 'Task #'.$i, 'project_id' => 1, 'column_id' => 2)));
-        }
-
-        for ($i = 0; $i < 4; $i++) {
-            $this->assertNotFalse($tc->create(array('title' => 'Task #'.$i, 'project_id' => 1, 'column_id' => 4)));
-        }
-
-        $pds->updateTotals(1, date('Y-m-d'));
-
-        $this->assertEquals(3, $pds->countDays(1, date('Y-m-d', strtotime('-2days')), date('Y-m-d')));
-        $metrics = $pds->getAggregatedMetrics(1, date('Y-m-d', strtotime('-2days')), date('Y-m-d'));
-
-        $this->assertNotEmpty($metrics);
-        $this->assertEquals(4, count($metrics));
-        $this->assertEquals(5, count($metrics[0]));
-        $this->assertEquals('Date', $metrics[0][0]);
-        $this->assertEquals('Backlog', $metrics[0][1]);
-        $this->assertEquals('Ready', $metrics[0][2]);
-        $this->assertEquals('Work in progress', $metrics[0][3]);
-        $this->assertEquals('Done', $metrics[0][4]);
-
-        $this->assertEquals(date('Y-m-d', strtotime('-2days')), $metrics[1][0]);
-        $this->assertEquals(10, $metrics[1][1]);
-        $this->assertEquals(0, $metrics[1][2]);
-        $this->assertEquals(0, $metrics[1][3]);
-        $this->assertEquals(5, $metrics[1][4]);
-
-        $this->assertEquals(date('Y-m-d', strtotime('-1day')), $metrics[2][0]);
-        $this->assertEquals(10, $metrics[2][1]);
-        $this->assertEquals(25, $metrics[2][2]);
-        $this->assertEquals(15, $metrics[2][3]);
-        $this->assertEquals(5, $metrics[2][4]);
-
-        $this->assertEquals(date('Y-m-d'), $metrics[3][0]);
-        $this->assertEquals(10, $metrics[3][1]);
-        $this->assertEquals(30, $metrics[3][2]);
-        $this->assertEquals(18, $metrics[3][3]);
-        $this->assertEquals(9, $metrics[3][4]);
+        $projectModel = new Project($this->container);
+        $projectDailyColumnStats = new ProjectDailyColumnStats($this->container);
+        $this->assertEquals(1, $projectModel->create(array('name' => 'UnitTest')));
+
+        $this->createTasks(1, 2, 1);
+        $this->createTasks(1, 3, 0);
+
+        $this->createTasks(2, 5, 1);
+        $this->createTasks(2, 8, 1);
+        $this->createTasks(2, 0, 0);
+        $this->createTasks(2, 0, 0);
+
+        $projectDailyColumnStats->updateTotals(1, '2016-01-16');
+
+        $this->createTasks(1, 9, 1);
+        $this->createTasks(1, 7, 0);
+
+        $projectDailyColumnStats->updateTotals(1, '2016-01-16');
+
+        $this->createTasks(3, 0, 1);
+
+        $projectDailyColumnStats->updateTotals(1, '2016-01-17');
+
+        $stats = $this->container['db']->table(ProjectDailyColumnStats::TABLE)
+            ->asc('day')
+            ->asc('column_id')
+            ->columns('day', 'project_id', 'column_id', 'total', 'score')
+            ->findAll();
+
+        $expected = array(
+            array(
+                'day' => '2016-01-16',
+                'project_id' => 1,
+                'column_id' => 1,
+                'total' => 4,
+                'score' => 11,
+            ),
+            array(
+                'day' => '2016-01-16',
+                'project_id' => 1,
+                'column_id' => 2,
+                'total' => 4,
+                'score' => 13,
+            ),
+            array(
+                'day' => '2016-01-17',
+                'project_id' => 1,
+                'column_id' => 1,
+                'total' => 4,
+                'score' => 11,
+            ),
+            array(
+                'day' => '2016-01-17',
+                'project_id' => 1,
+                'column_id' => 2,
+                'total' => 4,
+                'score' => 13,
+            ),
+            array(
+                'day' => '2016-01-17',
+                'project_id' => 1,
+                'column_id' => 3,
+                'total' => 1,
+                'score' => 0,
+            ),
+        );
+
+        $this->assertEquals($expected, $stats);
+    }
+
+    public function testUpdateTotalsWithOnlyOpenTasks()
+    {
+        $configModel = new Config($this->container);
+        $projectModel = new Project($this->container);
+        $projectDailyColumnStats = new ProjectDailyColumnStats($this->container);
+
+        $this->assertEquals(1, $projectModel->create(array('name' => 'UnitTest')));
+        $this->assertTrue($configModel->save(array('cfd_include_closed_tasks' => 0)));
+        $this->container['memoryCache']->flush();
+
+        $this->createTasks(1, 2, 1);
+        $this->createTasks(1, 3, 0);
+
+        $this->createTasks(2, 5, 1);
+        $this->createTasks(2, 8, 1);
+        $this->createTasks(2, 0, 0);
+        $this->createTasks(2, 0, 0);
+
+        $projectDailyColumnStats->updateTotals(1, '2016-01-16');
+
+        $this->createTasks(1, 9, 1);
+        $this->createTasks(1, 7, 0);
+
+        $projectDailyColumnStats->updateTotals(1, '2016-01-16');
+
+        $this->createTasks(3, 0, 1);
+
+        $projectDailyColumnStats->updateTotals(1, '2016-01-17');
+
+        $stats = $this->container['db']->table(ProjectDailyColumnStats::TABLE)
+            ->asc('day')
+            ->asc('column_id')
+            ->columns('day', 'project_id', 'column_id', 'total', 'score')
+            ->findAll();
+
+        $expected = array(
+            array(
+                'day' => '2016-01-16',
+                'project_id' => 1,
+                'column_id' => 1,
+                'total' => 2,
+                'score' => 11,
+            ),
+            array(
+                'day' => '2016-01-16',
+                'project_id' => 1,
+                'column_id' => 2,
+                'total' => 2,
+                'score' => 13,
+            ),
+            array(
+                'day' => '2016-01-17',
+                'project_id' => 1,
+                'column_id' => 1,
+                'total' => 2,
+                'score' => 11,
+            ),
+            array(
+                'day' => '2016-01-17',
+                'project_id' => 1,
+                'column_id' => 2,
+                'total' => 2,
+                'score' => 13,
+            ),
+            array(
+                'day' => '2016-01-17',
+                'project_id' => 1,
+                'column_id' => 3,
+                'total' => 1,
+                'score' => 0,
+            ),
+        );
+
+        $this->assertEquals($expected, $stats);
+    }
+
+    public function testCountDays()
+    {
+        $projectModel = new Project($this->container);
+        $projectDailyColumnStats = new ProjectDailyColumnStats($this->container);
+
+        $this->assertEquals(1, $projectModel->create(array('name' => 'UnitTest')));
+
+        $this->createTasks(1, 2, 1);
+        $projectDailyColumnStats->updateTotals(1, '2016-01-16');
+        $this->assertEquals(1, $projectDailyColumnStats->countDays(1, '2016-01-16', '2016-01-17'));
+
+        $projectDailyColumnStats->updateTotals(1, '2016-01-17');
+        $this->assertEquals(2, $projectDailyColumnStats->countDays(1, '2016-01-16', '2016-01-17'));
+    }
+
+    public function testGetAggregatedMetrics()
+    {
+        $projectModel = new Project($this->container);
+        $projectDailyColumnStats = new ProjectDailyColumnStats($this->container);
+        $this->assertEquals(1, $projectModel->create(array('name' => 'UnitTest')));
+
+        $this->createTasks(1, 2, 1);
+        $this->createTasks(1, 3, 0);
+
+        $this->createTasks(2, 5, 1);
+        $this->createTasks(2, 8, 1);
+        $this->createTasks(2, 0, 0);
+        $this->createTasks(2, 0, 0);
+
+        $projectDailyColumnStats->updateTotals(1, '2016-01-16');
+
+        $this->createTasks(1, 9, 1);
+        $this->createTasks(1, 7, 0);
+
+        $projectDailyColumnStats->updateTotals(1, '2016-01-16');
+
+        $this->createTasks(3, 0, 1);
+
+        $projectDailyColumnStats->updateTotals(1, '2016-01-17');
+
+        $this->createTasks(2, 1, 1);
+        $this->createTasks(3, 1, 1);
+        $this->createTasks(3, 0, 1);
+
+        $projectDailyColumnStats->updateTotals(1, '2016-01-18');
+
+        $expected = array(
+            array('Date', 'Backlog', 'Ready', 'Work in progress', 'Done'),
+            array('2016-01-16', 4, 4, 0, 0),
+            array('2016-01-17', 4, 4, 1, 0),
+            array('2016-01-18', 4, 5, 3, 0),
+        );
+
+        $this->assertEquals($expected, $projectDailyColumnStats->getAggregatedMetrics(1, '2016-01-16', '2016-01-18'));
+
+        $expected = array(
+            array('Date', 'Backlog', 'Ready', 'Work in progress', 'Done'),
+            array('2016-01-16', 11, 13, 0, 0),
+            array('2016-01-17', 11, 13, 0, 0),
+            array('2016-01-18', 11, 14, 1, 0),
+        );
+
+        $this->assertEquals($expected, $projectDailyColumnStats->getAggregatedMetrics(1, '2016-01-16', '2016-01-18', 'score'));
+    }
+
+    private function createTasks($column_id, $score, $is_active)
+    {
+        $taskCreationModel = new TaskCreation($this->container);
+        $this->assertNotFalse($taskCreationModel->create(array('project_id' => 1, 'title' => 'test', 'column_id' => $column_id, 'score' => $score, 'is_active' => $is_active)));
     }
 }