Add cumulative flow diagram

23 files changed, 368 insertions, 10 deletions

diff --git a/app/Controller/Analytic.php b/app/Controller/Analytic.php
index 7d112e6a..6c49089b 100644
--- a/app/Controller/Analytic.php
+++ b/app/Controller/Analytic.php
@@ -81,4 +81,48 @@ class Analytic extends Base
             )));
         }
     }
+
+    /**
+     * Show cumulative flow diagram
+     *
+     * @access public
+     */
+    public function cfd()
+    {
+        $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'];
+        }
+
+        if ($this->request->isAjax()) {
+            $this->response->json(array(
+                'columns' => array_values($this->board->getColumnsList($project['id'])),
+                'metrics' => $this->projectDailySummary->getRawMetrics($project['id'], $from, $to),
+                'labels' => array(
+                    'column' => t('Column'),
+                    'day' => t('Date'),
+                    'total' => t('Tasks'),
+                )
+            ));
+        }
+        else {
+            $this->response->html($this->layout('analytic/cfd', array(
+                'values' => array(
+                    'from' => $from,
+                    'to' => $to,
+                ),
+                'display_graph' => $this->projectDailySummary->countDays($project['id'], $from, $to) >= 2,
+                'project' => $project,
+                'date_format' => $this->config->get('application_date_format'),
+                'date_formats' => $this->dateParser->getAvailableFormats(),
+                'title' => t('Cumulative flow diagram for "%s"', $project['name']),
+            )));
+        }
+    }
 }
diff --git a/app/Controller/Base.php b/app/Controller/Base.php
index 5784605c..bd7bd2ee 100644
--- a/app/Controller/Base.php
+++ b/app/Controller/Base.php
@@ -108,7 +108,9 @@ abstract class Base
      */
     public function __destruct()
     {
-        // $this->container['logger']->addDebug(var_export($this->container['db']->getLogMessages(), true));
+        // foreach ($this->container['db']->getLogMessages() as $message) {
+        //     $this->container['logger']->addDebug($message);
+        // }
     }
 
     /**
@@ -173,6 +175,7 @@ abstract class Base
     {
         $models = array(
             'projectActivity', // Order is important
+            'projectDailySummary',
             'action',
             'project',
             'webhook',
diff --git a/app/Event/ProjectDailySummaryListener.php b/app/Event/ProjectDailySummaryListener.php
new file mode 100644
index 00000000..cd593abc
--- /dev/null
+++ b/app/Event/ProjectDailySummaryListener.php
@@ -0,0 +1,28 @@
+<?php
+
+namespace Event;
+
+/**
+ * Project daily summary listener
+ *
+ * @package event
+ * @author  Frederic Guillot
+ */
+class ProjectDailySummaryListener extends Base
+{
+    /**
+     * Execute the action
+     *
+     * @access public
+     * @param  array   $data   Event data dictionary
+     * @return bool            True if the action was executed or false when not executed
+     */
+    public function execute(array $data)
+    {
+        if (isset($data['project_id'])) {
+            return $this->projectDailySummary->updateTotals($data['project_id'], date('Y-m-d'));
+        }
+
+        return false;
+    }
+}
diff --git a/app/Locale/da_DK/translations.php b/app/Locale/da_DK/translations.php
index bc002699..34533e94 100644
--- a/app/Locale/da_DK/translations.php
+++ b/app/Locale/da_DK/translations.php
@@ -592,4 +592,6 @@ return array(
     // 'This value is required' => '',
     // 'This value must be numeric' => '',
     // 'Unable to create this task.' => '',
+    // 'Cumulative flow diagram' => '',
+    // 'Cumulative flow diagram for "%s"' => '',
 );
diff --git a/app/Locale/de_DE/translations.php b/app/Locale/de_DE/translations.php
index b2b02477..bce2c233 100644
--- a/app/Locale/de_DE/translations.php
+++ b/app/Locale/de_DE/translations.php
@@ -592,4 +592,6 @@ return array(
     // 'This value is required' => '',
     // 'This value must be numeric' => '',
     // 'Unable to create this task.' => '',
+    // 'Cumulative flow diagram' => '',
+    // 'Cumulative flow diagram for "%s"' => '',
 );
diff --git a/app/Locale/es_ES/translations.php b/app/Locale/es_ES/translations.php
index a54ccbfb..e26be0a1 100644
--- a/app/Locale/es_ES/translations.php
+++ b/app/Locale/es_ES/translations.php
@@ -592,4 +592,6 @@ return array(
     // 'This value is required' => '',
     // 'This value must be numeric' => '',
     // 'Unable to create this task.' => '',
+    // 'Cumulative flow diagram' => '',
+    // 'Cumulative flow diagram for "%s"' => '',
 );
diff --git a/app/Locale/fi_FI/translations.php b/app/Locale/fi_FI/translations.php
index 5ac8a047..7af506a5 100644
--- a/app/Locale/fi_FI/translations.php
+++ b/app/Locale/fi_FI/translations.php
@@ -592,4 +592,6 @@ return array(
     // 'This value is required' => '',
     // 'This value must be numeric' => '',
     // 'Unable to create this task.' => '',
+    // 'Cumulative flow diagram' => '',
+    // 'Cumulative flow diagram for "%s"' => '',
 );
diff --git a/app/Locale/fr_FR/translations.php b/app/Locale/fr_FR/translations.php
index 4c181917..ace478e2 100644
--- a/app/Locale/fr_FR/translations.php
+++ b/app/Locale/fr_FR/translations.php
@@ -579,7 +579,7 @@ return array(
     'Column removed successfully.' => 'Colonne supprimée avec succès.',
     'Edit Project' => 'Modifier le projet',
     'Github Issue' => 'Ticket Github',
-    'Not enough data to show the graph.' => 'Pas assez de données pour afficher le graphique',
+    'Not enough data to show the graph.' => 'Pas assez de données pour afficher le graphique.',
     'Previous' => 'Précédent',
     'The id must be an integer' => 'L\'id doit être un entier',
     'The project id must be an integer' => 'L\'id du projet doit être un entier',
@@ -592,4 +592,6 @@ return array(
     'This value is required' => 'Cette valeur est obligatoire',
     'This value must be numeric' => 'Cette valeur doit être numérique',
     'Unable to create this task.' => 'Impossible de créer cette tâche',
+    'Cumulative flow diagram' => 'Diagramme de flux cumulé',
+    'Cumulative flow diagram for "%s"' => 'Diagramme de flux cumulé pour « %s »',
 );
diff --git a/app/Locale/it_IT/translations.php b/app/Locale/it_IT/translations.php
index 7b042e3b..6630f7f7 100644
--- a/app/Locale/it_IT/translations.php
+++ b/app/Locale/it_IT/translations.php
@@ -592,4 +592,6 @@ return array(
     // 'This value is required' => '',
     // 'This value must be numeric' => '',
     // 'Unable to create this task.' => '',
+    // 'Cumulative flow diagram' => '',
+    // 'Cumulative flow diagram for "%s"' => '',
 );
diff --git a/app/Locale/ja_JP/translations.php b/app/Locale/ja_JP/translations.php
index 2022a184..0312a402 100644
--- a/app/Locale/ja_JP/translations.php
+++ b/app/Locale/ja_JP/translations.php
@@ -592,4 +592,6 @@ return array(
     // 'This value is required' => '',
     // 'This value must be numeric' => '',
     // 'Unable to create this task.' => '',
+    // 'Cumulative flow diagram' => '',
+    // 'Cumulative flow diagram for "%s"' => '',
 );
diff --git a/app/Locale/pl_PL/translations.php b/app/Locale/pl_PL/translations.php
index 26982848..f48f0615 100644
--- a/app/Locale/pl_PL/translations.php
+++ b/app/Locale/pl_PL/translations.php
@@ -592,4 +592,6 @@ return array(
     // 'This value is required' => '',
     // 'This value must be numeric' => '',
     // 'Unable to create this task.' => '',
+    // 'Cumulative flow diagram' => '',
+    // 'Cumulative flow diagram for "%s"' => '',
 );
diff --git a/app/Locale/pt_BR/translations.php b/app/Locale/pt_BR/translations.php
index 75f39c09..647348b9 100644
--- a/app/Locale/pt_BR/translations.php
+++ b/app/Locale/pt_BR/translations.php
@@ -592,4 +592,6 @@ return array(
     // 'This value is required' => '',
     // 'This value must be numeric' => '',
     // 'Unable to create this task.' => '',
+    // 'Cumulative flow diagram' => '',
+    // 'Cumulative flow diagram for "%s"' => '',
 );
diff --git a/app/Locale/ru_RU/translations.php b/app/Locale/ru_RU/translations.php
index a120d2a9..1d220902 100644
--- a/app/Locale/ru_RU/translations.php
+++ b/app/Locale/ru_RU/translations.php
@@ -592,4 +592,6 @@ return array(
     // 'This value is required' => '',
     // 'This value must be numeric' => '',
     // 'Unable to create this task.' => '',
+    // 'Cumulative flow diagram' => '',
+    // 'Cumulative flow diagram for "%s"' => '',
 );
diff --git a/app/Locale/sv_SE/translations.php b/app/Locale/sv_SE/translations.php
index 93ada787..ca79904a 100644
--- a/app/Locale/sv_SE/translations.php
+++ b/app/Locale/sv_SE/translations.php
@@ -592,4 +592,6 @@ return array(
     // 'This value is required' => '',
     // 'This value must be numeric' => '',
     // 'Unable to create this task.' => '',
+    // 'Cumulative flow diagram' => '',
+    // 'Cumulative flow diagram for "%s"' => '',
 );
diff --git a/app/Locale/th_TH/translations.php b/app/Locale/th_TH/translations.php
index b06f2ac1..f5423c8e 100644
--- a/app/Locale/th_TH/translations.php
+++ b/app/Locale/th_TH/translations.php
@@ -592,4 +592,6 @@ return array(
     // 'This value is required' => '',
     // 'This value must be numeric' => '',
     // 'Unable to create this task.' => '',
+    // 'Cumulative flow diagram' => '',
+    // 'Cumulative flow diagram for "%s"' => '',
 );
diff --git a/app/Locale/zh_CN/translations.php b/app/Locale/zh_CN/translations.php
index a95ac1bc..1da74b13 100644
--- a/app/Locale/zh_CN/translations.php
+++ b/app/Locale/zh_CN/translations.php
@@ -592,4 +592,6 @@ return array(
     // 'This value is required' => '',
     // 'This value must be numeric' => '',
     // 'Unable to create this task.' => '',
+    // 'Cumulative flow diagram' => '',
+    // 'Cumulative flow diagram for "%s"' => '',
 );
diff --git a/app/Model/ProjectDailySummary.php b/app/Model/ProjectDailySummary.php
new file mode 100644
index 00000000..25a58368
--- /dev/null
+++ b/app/Model/ProjectDailySummary.php
@@ -0,0 +1,181 @@
+<?php
+
+namespace Model;
+
+use Core\Template;
+use Event\ProjectDailySummaryListener;
+
+/**
+ * Project daily summary
+ *
+ * @package  model
+ * @author   Frederic Guillot
+ */
+class ProjectDailySummary extends Base
+{
+    /**
+     * SQL table name
+     *
+     * @var string
+     */
+    const TABLE = 'project_daily_summaries';
+
+    /**
+     * Update daily totals for the project
+     *
+     * @access public
+     * @param  integer    $project_id    Project id
+     * @param  string     $date          Record date (YYYY-MM-DD)
+     * @return boolean
+     */
+    public function updateTotals($project_id, $date)
+    {
+        return $this->db->transaction(function($db) use ($project_id, $date) {
+
+            $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(ProjectDailySummary::TABLE)->insert(array(
+                    'day' => $date,
+                    'project_id' => $project_id,
+                    'column_id' => $column_id,
+                    'total' => 0,
+                ));
+
+                $db->table(ProjectDailySummary::TABLE)
+                    ->eq('project_id', $project_id)
+                    ->eq('column_id', $column_id)
+                    ->eq('day', $date)
+                    ->update(array(
+                        'total' => $db->table(Task::TABLE)
+                                      ->eq('project_id', $project_id)
+                                      ->eq('column_id', $column_id)
+                                      ->eq('is_active', Task::STATUS_OPEN)
+                                      ->count()
+                    ));
+            }
+        });
+    }
+
+    /**
+     * Count the number of recorded days for the data range
+     *
+     * @access public
+     * @param  integer    $project_id    Project id
+     * @param  string     $from          Start date (ISO format YYYY-MM-DD)
+     * @param  string     $to            End date
+     * @return integer
+     */
+    public function countDays($project_id, $from, $to)
+    {
+        $rq = $this->db->execute(
+            'SELECT COUNT(DISTINCT day) FROM '.self::TABLE.' WHERE day >= ? AND day <= ?',
+            array($from, $to)
+        );
+
+        return $rq->fetchColumn(0);
+    }
+
+    /**
+     * Get raw metrics for the project within a data range
+     *
+     * @access public
+     * @param  integer    $project_id    Project id
+     * @param  string     $from          Start date (ISO format YYYY-MM-DD)
+     * @param  string     $to            End date
+     * @return array
+     */
+    public function getRawMetrics($project_id, $from, $to)
+    {
+        return $this->db->table(ProjectDailySummary::TABLE)
+                        ->columns(
+                            ProjectDailySummary::TABLE.'.column_id',
+                            ProjectDailySummary::TABLE.'.day',
+                            ProjectDailySummary::TABLE.'.total',
+                            Board::TABLE.'.title AS column_title'
+                        )
+                        ->join(Board::TABLE, 'id', 'column_id')
+                        ->eq(ProjectDailySummary::TABLE.'.project_id', $project_id)
+                        ->gte('day', $from)
+                        ->lte('day', $to)
+                        ->findAll();
+    }
+
+    /**
+     * 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
+     * @return array
+     */
+    public function getAggregatedMetrics($project_id, $from, $to)
+    {
+        $columns = $this->board->getColumnsList($project_id);
+        $column_ids = array_keys($columns);
+        $metrics = array(array(e('Date')) + $columns);
+        $aggregates = array();
+
+        // Fetch metrics for the project
+        $records = $this->db->table(ProjectDailySummary::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']);
+            }
+
+            $aggregates[$record['day']][$record['column_id']] = $record['total'];
+        }
+
+        // Aggregate by row
+        foreach ($aggregates as $aggregate) {
+
+            $row = array($aggregate[0]);
+
+            foreach ($column_ids as $column_id) {
+                $row[] = (int) $aggregate[$column_id];
+            }
+
+            $metrics[] = $row;
+        }
+
+        return $metrics;
+    }
+
+    /**
+     * Attach events to be able to record the metrics
+     *
+     * @access public
+     */
+    public function attachEvents()
+    {
+        $events = array(
+            Task::EVENT_CREATE,
+            Task::EVENT_CLOSE,
+            Task::EVENT_OPEN,
+            Task::EVENT_MOVE_COLUMN,
+        );
+
+        $listener = new ProjectDailySummaryListener($this->container);
+
+        foreach ($events as $event_name) {
+            $this->event->attach($event_name, $listener);
+        }
+    }
+}
diff --git a/app/Model/Task.php b/app/Model/Task.php
index a0090641..25a4f000 100644
--- a/app/Model/Task.php
+++ b/app/Model/Task.php
@@ -309,8 +309,6 @@ class Task extends Base
      */
     private function savePositions($moved_task_id, array $columns)
     {
-        $this->db->startTransaction();
-
         foreach ($columns as $column_id => $column) {
 
             $position = 1;
@@ -336,14 +334,11 @@ class Task extends Base
                 $position++;
 
                 if (! $result) {
-                    $this->db->cancelTransaction();
                     return false;
                 }
             }
         }
 
-        $this->db->closeTransaction();
-
         return true;
     }
 
diff --git a/app/Schema/Mysql.php b/app/Schema/Mysql.php
index 4f74f761..e73f6cf4 100644
--- a/app/Schema/Mysql.php
+++ b/app/Schema/Mysql.php
@@ -5,7 +5,25 @@ namespace Schema;
 use PDO;
 use Core\Security;
 
-const VERSION = 34;
+const VERSION = 35;
+
+function version_35($pdo)
+{
+    $pdo->exec("
+        CREATE TABLE project_daily_summaries (
+            id INT NOT NULL AUTO_INCREMENT,
+            day CHAR(10) NOT NULL,
+            project_id INT NOT NULL,
+            column_id INT NOT NULL,
+            total INT NOT NULL DEFAULT 0,
+            PRIMARY KEY(id),
+            FOREIGN KEY(column_id) REFERENCES columns(id) ON DELETE CASCADE,
+            FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE
+        ) ENGINE=InnoDB CHARSET=utf8
+    ");
+
+    $pdo->exec('CREATE UNIQUE INDEX project_daily_column_stats_idx ON project_daily_summaries(day, project_id, column_id)');
+}
 
 function version_34($pdo)
 {
diff --git a/app/Schema/Postgres.php b/app/Schema/Postgres.php
index f301f3e8..4ec3885c 100644
--- a/app/Schema/Postgres.php
+++ b/app/Schema/Postgres.php
@@ -5,7 +5,24 @@ namespace Schema;
 use PDO;
 use Core\Security;
 
-const VERSION = 15;
+const VERSION = 16;
+
+function version_16($pdo)
+{
+    $pdo->exec("
+        CREATE TABLE project_daily_summaries (
+            id SERIAL PRIMARY KEY,
+            day CHAR(10) NOT NULL,
+            project_id INTEGER NOT NULL,
+            column_id INTEGER NOT NULL,
+            total INTEGER NOT NULL DEFAULT 0,
+            FOREIGN KEY(column_id) REFERENCES columns(id) ON DELETE CASCADE,
+            FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE
+        )
+    ");
+
+    $pdo->exec('CREATE UNIQUE INDEX project_daily_column_stats_idx ON project_daily_summaries(day, project_id, column_id)');
+}
 
 function version_15($pdo)
 {
diff --git a/app/Schema/Sqlite.php b/app/Schema/Sqlite.php
index 8571d924..cdc74465 100644
--- a/app/Schema/Sqlite.php
+++ b/app/Schema/Sqlite.php
@@ -5,7 +5,24 @@ namespace Schema;
 use Core\Security;
 use PDO;
 
-const VERSION = 34;
+const VERSION = 35;
+
+function version_35($pdo)
+{
+    $pdo->exec("
+        CREATE TABLE project_daily_summaries (
+            id INTEGER PRIMARY KEY,
+            day TEXT NOT NULL,
+            project_id INTEGER NOT NULL,
+            column_id INTEGER NOT NULL,
+            total INTEGER NOT NULL DEFAULT 0,
+            FOREIGN KEY(column_id) REFERENCES columns(id) ON DELETE CASCADE,
+            FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE
+        )
+    ");
+
+    $pdo->exec('CREATE UNIQUE INDEX project_daily_column_stats_idx ON project_daily_summaries(day, project_id, column_id)');
+}
 
 function version_34($pdo)
 {
diff --git a/app/Template/analytic/cfd.php b/app/Template/analytic/cfd.php
new file mode 100644
index 00000000..46cee47e
--- /dev/null
+++ b/app/Template/analytic/cfd.php
@@ -0,0 +1,26 @@
+<div class="page-header">
+    <h2><?= t('Cumulative flow diagram') ?></h2>
+</div>
+
+<?php if (! $display_graph): ?>
+    <p class="alert"><?= t('Not enough data to show the graph.') ?></p>
+<?php else: ?>
+    <section id="analytic-cfd">
+        <div id="chart" data-url="<?= Helper\u('analytic', 'cfd', array('project_id' => $project['id'], 'from' => $values['from'], 'to' => $values['to'])) ?>"></div>
+    </section>
+<?php endif ?>
+
+<hr/>
+
+<form method="post" class="form-inline" action="<?= Helper\u('analytic', 'cfd', array('project_id' => $project['id'])) ?>" autocomplete="off">
+
+    <?= Helper\form_csrf() ?>
+
+    <?= Helper\form_label(t('Start Date'), 'from') ?>
+    <?= Helper\form_text('from', $values, array(), array('required', 'placeholder="'.Helper\in_list($date_format, $date_formats).'"'), 'form-date') ?>
+
+    <?= Helper\form_label(t('End Date'), 'to') ?>
+    <?= Helper\form_text('to', $values, array(), array('required', 'placeholder="'.Helper\in_list($date_format, $date_formats).'"'), 'form-date') ?>
+
+    <input type="submit" value="<?= t('Execute') ?>" class="btn btn-blue"/>
+</form>
diff --git a/app/Template/analytic/sidebar.php b/app/Template/analytic/sidebar.php
index dded245a..9b2fd9f6 100644
--- a/app/Template/analytic/sidebar.php
+++ b/app/Template/analytic/sidebar.php
@@ -7,5 +7,8 @@
         <li>
             <?= Helper\a(t('User repartition'), 'analytic', 'users', array('project_id' => $project['id'])) ?>
         </li>
+        <li>
+            <?= Helper\a(t('Cumulative flow diagram'), 'analytic', 'cfd', array('project_id' => $project['id'])) ?>
+        </li>
     </ul>
 </div>
\ No newline at end of file