Add lead and cycle time for projects

15 files changed, 287 insertions, 38 deletions

diff --git a/app/Console/ProjectDailySummaryExport.php b/app/Console/ProjectDailyColumnStatsExport.php
index 07841d52..b9830662 100644
--- a/app/Console/ProjectDailySummaryExport.php
+++ b/app/Console/ProjectDailyColumnStatsExport.php
@@ -7,13 +7,13 @@ use Symfony\Component\Console\Input\InputArgument;
 use Symfony\Component\Console\Input\InputInterface;
 use Symfony\Component\Console\Output\OutputInterface;
 
-class ProjectDailySummaryExport extends Base
+class ProjectDailyColumnStatsExport extends Base
 {
     protected function configure()
     {
         $this
-            ->setName('export:daily-project-summary')
-            ->setDescription('Daily project summary CSV export (number of tasks per column and per day)')
+            ->setName('export:daily-project-column-stats')
+            ->setDescription('Daily project column stats CSV export (number of tasks per column and per day)')
             ->addArgument('project_id', InputArgument::REQUIRED, 'Project id')
             ->addArgument('start_date', InputArgument::REQUIRED, 'Start date (YYYY-MM-DD)')
             ->addArgument('end_date', InputArgument::REQUIRED, 'End date (YYYY-MM-DD)');
@@ -21,7 +21,7 @@ class ProjectDailySummaryExport extends Base
 
     protected function execute(InputInterface $input, OutputInterface $output)
     {
-        $data = $this->projectDailySummary->getAggregatedMetrics(
+        $data = $this->projectDailyColumnStats->getAggregatedMetrics(
             $input->getArgument('project_id'),
             $input->getArgument('start_date'),
             $input->getArgument('end_date')
diff --git a/app/Console/ProjectDailySummaryCalculation.php b/app/Console/ProjectDailyStatsCalculation.php
index b2ada1b6..4b77c556 100644
--- a/app/Console/ProjectDailySummaryCalculation.php
+++ b/app/Console/ProjectDailyStatsCalculation.php
@@ -6,13 +6,13 @@ use Model\Project;
 use Symfony\Component\Console\Input\InputInterface;
 use Symfony\Component\Console\Output\OutputInterface;
 
-class ProjectDailySummaryCalculation extends Base
+class ProjectDailyStatsCalculation extends Base
 {
     protected function configure()
     {
         $this
-            ->setName('projects:daily-summary')
-            ->setDescription('Calculate daily summary data for all projects');
+            ->setName('projects:daily-stats')
+            ->setDescription('Calculate daily statistics for all projects');
     }
 
     protected function execute(InputInterface $input, OutputInterface $output)
@@ -21,7 +21,8 @@ class ProjectDailySummaryCalculation extends Base
 
         foreach ($projects as $project) {
             $output->writeln('Run calculation for '.$project['name']);
-            $this->projectDailySummary->updateTotals($project['id'], date('Y-m-d'));
+            $this->projectDailyColumnStats->updateTotals($project['id'], date('Y-m-d'));
+            $this->projectDailyStats->updateTotals($project['id'], date('Y-m-d'));
         }
     }
 }
diff --git a/app/Controller/Analytic.php b/app/Controller/Analytic.php
index 010fda09..ca2146ed 100644
--- a/app/Controller/Analytic.php
+++ b/app/Controller/Analytic.php
@@ -27,6 +27,40 @@ class Analytic extends Base
     }
 
     /**
+     * Show average Lead and Cycle time
+     *
+     * @access public
+     */
+    public function leadAndCycleTime()
+    {
+        $project = $this->getProject();
+        $values = $this->request->getValues();
+
+        $this->projectDailyStats->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'];
+        }
+
+        $this->response->html($this->layout('analytic/lead_cycle_time', array(
+            'values' => array(
+                'from' => $from,
+                'to' => $to,
+            ),
+            'project' => $project,
+            'average' => $this->projectAnalytic->getAverageLeadAndCycleTime($project['id']),
+            'metrics' => $this->projectDailyStats->getRawMetrics($project['id'], $from, $to),
+            'date_format' => $this->config->get('application_date_format'),
+            'date_formats' => $this->dateParser->getAvailableFormats(),
+            'title' => t('Lead and Cycle time for "%s"', $project['name']),
+        )));
+    }
+
+    /**
      * Show average time spent by column
      *
      * @access public
@@ -104,6 +138,8 @@ class Analytic extends Base
         $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'));
 
@@ -112,7 +148,7 @@ class Analytic extends Base
             $to = $values['to'];
         }
 
-        $display_graph = $this->projectDailySummary->countDays($project['id'], $from, $to) >= 2;
+        $display_graph = $this->projectDailyColumnStats->countDays($project['id'], $from, $to) >= 2;
 
         $this->response->html($this->layout($template, array(
             'values' => array(
@@ -120,7 +156,7 @@ class Analytic extends Base
                 'to' => $to,
             ),
             'display_graph' => $display_graph,
-            'metrics' => $display_graph ? $this->projectDailySummary->getAggregatedMetrics($project['id'], $from, $to, $column) : array(),
+            'metrics' => $display_graph ? $this->projectDailyColumnStats->getAggregatedMetrics($project['id'], $from, $to, $column) : array(),
             'project' => $project,
             'date_format' => $this->config->get('application_date_format'),
             'date_formats' => $this->dateParser->getAvailableFormats(),
diff --git a/app/Controller/Export.php b/app/Controller/Export.php
index 117fb5ee..8b558c0a 100644
--- a/app/Controller/Export.php
+++ b/app/Controller/Export.php
@@ -70,7 +70,7 @@ class Export extends Base
      */
     public function summary()
     {
-        $this->common('projectDailySummary', 'getAggregatedMetrics', t('Summary'), 'summary', t('Daily project summary export'));
+        $this->common('ProjectDailyColumnStats', 'getAggregatedMetrics', t('Summary'), 'summary', t('Daily project summary export'));
     }
 
     /**
diff --git a/app/Core/Base.php b/app/Core/Base.php
index d4d7faa3..14466d5c 100644
--- a/app/Core/Base.php
+++ b/app/Core/Base.php
@@ -48,7 +48,8 @@ use Pimple\Container;
  * @property \Model\ProjectActivity        $projectActivity
  * @property \Model\ProjectAnalytic        $projectAnalytic
  * @property \Model\ProjectDuplication     $projectDuplication
- * @property \Model\ProjectDailySummary    $projectDailySummary
+ * @property \Model\ProjectDailyColumnStats    $projectDailyColumnStats
+ * @property \Model\ProjectDailyStats      $projectDailyStats
  * @property \Model\ProjectIntegration     $projectIntegration
  * @property \Model\ProjectPermission      $projectPermission
  * @property \Model\Subtask                $subtask
diff --git a/app/Model/ProjectAnalytic.php b/app/Model/ProjectAnalytic.php
index f4e8af09..1ee8a405 100644
--- a/app/Model/ProjectAnalytic.php
+++ b/app/Model/ProjectAnalytic.php
@@ -89,6 +89,43 @@ class ProjectAnalytic extends Base
     }
 
     /**
+     * Get the average lead and cycle time
+     *
+     * @access public
+     * @param  integer   $project_id
+     * @return array
+     */
+    public function getAverageLeadAndCycleTime($project_id)
+    {
+        $stats = array(
+            'count' => 0,
+            'total_lead_time' => 0,
+            'total_cycle_time' => 0,
+            'avg_lead_time' => 0,
+            'avg_cycle_time' => 0,
+        );
+
+        $tasks = $this->db
+            ->table(Task::TABLE)
+            ->columns('date_completed', 'date_creation', 'date_started')
+            ->eq('project_id', $project_id)
+            ->desc('id')
+            ->limit(1000)
+            ->findAll();
+
+        foreach ($tasks as &$task) {
+            $stats['count']++;
+            $stats['total_lead_time'] += ($task['date_completed'] ?: time()) - $task['date_creation'];
+            $stats['total_cycle_time'] += empty($task['date_started']) ? 0 : ($task['date_completed'] ?: time()) - $task['date_started'];
+        }
+
+        $stats['avg_lead_time'] = (int) ($stats['total_lead_time'] / $stats['count']);
+        $stats['avg_cycle_time'] = (int) ($stats['total_cycle_time'] / $stats['count']);
+
+        return $stats;
+    }
+
+    /**
      * Get the average time spent into each column
      *
      * @access public
diff --git a/app/Model/ProjectDailySummary.php b/app/Model/ProjectDailyColumnStats.php
index 04dc5629..26e9d8b7 100644
--- a/app/Model/ProjectDailySummary.php
+++ b/app/Model/ProjectDailyColumnStats.php
@@ -3,22 +3,22 @@
 namespace Model;
 
 /**
- * Project daily summary
+ * Project Daily Column Stats
  *
  * @package  model
  * @author   Frederic Guillot
  */
-class ProjectDailySummary extends Base
+class ProjectDailyColumnStats extends Base
 {
     /**
      * SQL table name
      *
      * @var string
      */
-    const TABLE = 'project_daily_summaries';
+    const TABLE = 'project_daily_column_stats';
 
     /**
-     * Update daily totals for the project
+     * Update daily totals for the project and foreach column
      *
      * "total" is the number open of tasks in the column
      * "score" is the sum of tasks score in the column
@@ -38,7 +38,7 @@ class ProjectDailySummary extends Base
 
                 // 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(
+                $db->table(ProjectDailyColumnStats::TABLE)->insert(array(
                     'day' => $date,
                     'project_id' => $project_id,
                     'column_id' => $column_id,
@@ -46,7 +46,7 @@ class ProjectDailySummary extends Base
                     'score' => 0,
                 ));
 
-                $db->table(ProjectDailySummary::TABLE)
+                $db->table(ProjectDailyColumnStats::TABLE)
                     ->eq('project_id', $project_id)
                     ->eq('column_id', $column_id)
                     ->eq('day', $date)
@@ -95,19 +95,19 @@ class ProjectDailySummary extends Base
      */
     public function getRawMetrics($project_id, $from, $to)
     {
-        return $this->db->table(ProjectDailySummary::TABLE)
+        return $this->db->table(ProjectDailyColumnStats::TABLE)
                         ->columns(
-                            ProjectDailySummary::TABLE.'.column_id',
-                            ProjectDailySummary::TABLE.'.day',
-                            ProjectDailySummary::TABLE.'.total',
-                            ProjectDailySummary::TABLE.'.score',
+                            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(ProjectDailySummary::TABLE.'.project_id', $project_id)
+                        ->eq(ProjectDailyColumnStats::TABLE.'.project_id', $project_id)
                         ->gte('day', $from)
                         ->lte('day', $to)
-                        ->asc(ProjectDailySummary::TABLE.'.day')
+                        ->asc(ProjectDailyColumnStats::TABLE.'.day')
                         ->findAll();
     }
 
@@ -122,17 +122,17 @@ class ProjectDailySummary extends Base
      */
     public function getRawMetricsByDay($project_id, $from, $to)
     {
-        return $this->db->table(ProjectDailySummary::TABLE)
+        return $this->db->table(ProjectDailyColumnStats::TABLE)
                         ->columns(
-                            ProjectDailySummary::TABLE.'.day',
-                            'SUM('.ProjectDailySummary::TABLE.'.total) AS total',
-                            'SUM('.ProjectDailySummary::TABLE.'.score) AS score'
+                            ProjectDailyColumnStats::TABLE.'.day',
+                            'SUM('.ProjectDailyColumnStats::TABLE.'.total) AS total',
+                            'SUM('.ProjectDailyColumnStats::TABLE.'.score) AS score'
                         )
-                        ->eq(ProjectDailySummary::TABLE.'.project_id', $project_id)
+                        ->eq(ProjectDailyColumnStats::TABLE.'.project_id', $project_id)
                         ->gte('day', $from)
                         ->lte('day', $to)
-                        ->asc(ProjectDailySummary::TABLE.'.day')
-                        ->groupBy(ProjectDailySummary::TABLE.'.day')
+                        ->asc(ProjectDailyColumnStats::TABLE.'.day')
+                        ->groupBy(ProjectDailyColumnStats::TABLE.'.day')
                         ->findAll();
     }
 
@@ -160,7 +160,7 @@ class ProjectDailySummary extends Base
         $aggregates = array();
 
         // Fetch metrics for the project
-        $records = $this->db->table(ProjectDailySummary::TABLE)
+        $records = $this->db->table(ProjectDailyColumnStats::TABLE)
                             ->eq('project_id', $project_id)
                             ->gte('day', $from)
                             ->lte('day', $to)
diff --git a/app/Model/ProjectDailyStats.php b/app/Model/ProjectDailyStats.php
new file mode 100644
index 00000000..56a51730
--- /dev/null
+++ b/app/Model/ProjectDailyStats.php
@@ -0,0 +1,72 @@
+<?php
+
+namespace Model;
+
+/**
+ * Project Daily Stats
+ *
+ * @package  model
+ * @author   Frederic Guillot
+ */
+class ProjectDailyStats extends Base
+{
+    /**
+     * SQL table name
+     *
+     * @var string
+     */
+    const TABLE = 'project_daily_stats';
+
+    /**
+     * 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)
+    {
+        $lead_cycle_time = $this->projectAnalytic->getAverageLeadAndCycleTime($project_id);
+
+        return $this->db->transaction(function($db) use ($project_id, $date, $lead_cycle_time) {
+
+            // This call will fail if the record already exists
+            // (cross database driver hack for INSERT..ON DUPLICATE KEY UPDATE)
+            $db->table(ProjectDailyStats::TABLE)->insert(array(
+                'day' => $date,
+                'project_id' => $project_id,
+                'avg_lead_time' => 0,
+                'avg_cycle_time' => 0,
+            ));
+
+            $db->table(ProjectDailyStats::TABLE)
+                ->eq('project_id', $project_id)
+                ->eq('day', $date)
+                ->update(array(
+                    'avg_lead_time' => $lead_cycle_time['avg_lead_time'],
+                    'avg_cycle_time' => $lead_cycle_time['avg_cycle_time'],
+                ));
+        });
+    }
+
+    /**
+     * 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(self::TABLE)
+                        ->columns('day', 'avg_lead_time', 'avg_cycle_time')
+                        ->eq(self::TABLE.'.project_id', $project_id)
+                        ->gte('day', $from)
+                        ->lte('day', $to)
+                        ->asc(self::TABLE.'.day')
+                        ->findAll();
+    }
+}
diff --git a/app/Schema/Mysql.php b/app/Schema/Mysql.php
index 37ef637b..769a7425 100644
--- a/app/Schema/Mysql.php
+++ b/app/Schema/Mysql.php
@@ -6,7 +6,26 @@ use PDO;
 use Core\Security;
 use Model\Link;
 
-const VERSION = 78;
+const VERSION = 79;
+
+function version_79($pdo)
+{
+    $pdo->exec("
+        CREATE TABLE project_daily_stats (
+            id INT NOT NULL AUTO_INCREMENT,
+            day CHAR(10) NOT NULL,
+            project_id INT NOT NULL,
+            avg_lead_time INT NOT NULL DEFAULT 0,
+            avg_cycle_time INT NOT NULL DEFAULT 0,
+            PRIMARY KEY(id),
+            FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE
+        ) ENGINE=InnoDB CHARSET=utf8
+    ");
+
+    $pdo->exec('CREATE UNIQUE INDEX project_daily_stats_idx ON project_daily_stats(day, project_id)');
+
+    $pdo->exec('RENAME TABLE project_daily_summaries TO project_daily_column_stats');
+}
 
 function version_78($pdo)
 {
diff --git a/app/Schema/Postgres.php b/app/Schema/Postgres.php
index 62f22baa..fca87766 100644
--- a/app/Schema/Postgres.php
+++ b/app/Schema/Postgres.php
@@ -6,7 +6,25 @@ use PDO;
 use Core\Security;
 use Model\Link;
 
-const VERSION = 58;
+const VERSION = 59;
+
+function version_59($pdo)
+{
+    $pdo->exec("
+        CREATE TABLE project_daily_stats (
+            id SERIAL PRIMARY KEY,
+            day CHAR(10) NOT NULL,
+            project_id INTEGER NOT NULL,
+            avg_lead_time INTEGER NOT NULL DEFAULT 0,
+            avg_cycle_time INTEGER NOT NULL DEFAULT 0,
+            FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE
+        )
+    ");
+
+    $pdo->exec('CREATE UNIQUE INDEX project_daily_stats_idx ON project_daily_stats(day, project_id)');
+
+    $pdo->exec('ALTER TABLE project_daily_summaries RENAME TO project_daily_column_stats');
+}
 
 function version_58($pdo)
 {
diff --git a/app/Schema/Sqlite.php b/app/Schema/Sqlite.php
index 10899b54..9981e72f 100644
--- a/app/Schema/Sqlite.php
+++ b/app/Schema/Sqlite.php
@@ -6,7 +6,25 @@ use Core\Security;
 use PDO;
 use Model\Link;
 
-const VERSION = 74;
+const VERSION = 75;
+
+function version_75($pdo)
+{
+    $pdo->exec("
+        CREATE TABLE project_daily_stats (
+            id INTEGER PRIMARY KEY,
+            day TEXT NOT NULL,
+            project_id INTEGER NOT NULL,
+            avg_lead_time INTEGER NOT NULL DEFAULT 0,
+            avg_cycle_time INTEGER NOT NULL DEFAULT 0,
+            FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE
+        )
+    ");
+
+    $pdo->exec('CREATE UNIQUE INDEX project_daily_stats_idx ON project_daily_stats(day, project_id)');
+
+    $pdo->exec('ALTER TABLE project_daily_summaries RENAME TO project_daily_column_stats');
+}
 
 function version_74($pdo)
 {
diff --git a/app/ServiceProvider/ClassProvider.php b/app/ServiceProvider/ClassProvider.php
index 609f5824..8c55457d 100644
--- a/app/ServiceProvider/ClassProvider.php
+++ b/app/ServiceProvider/ClassProvider.php
@@ -33,7 +33,8 @@ class ClassProvider implements ServiceProviderInterface
             'ProjectActivity',
             'ProjectAnalytic',
             'ProjectDuplication',
-            'ProjectDailySummary',
+            'ProjectDailyColumnStats',
+            'ProjectDailyStats',
             'ProjectIntegration',
             'ProjectPermission',
             'Subtask',
diff --git a/app/Subscriber/ProjectDailySummarySubscriber.php b/app/Subscriber/ProjectDailySummarySubscriber.php
index 9e4f15b0..db180dea 100644
--- a/app/Subscriber/ProjectDailySummarySubscriber.php
+++ b/app/Subscriber/ProjectDailySummarySubscriber.php
@@ -22,7 +22,8 @@ class ProjectDailySummarySubscriber extends \Core\Base implements EventSubscribe
     public function execute(TaskEvent $event)
     {
         if (isset($event['project_id'])) {
-            $this->projectDailySummary->updateTotals($event['project_id'], date('Y-m-d'));
+            $this->projectDailyColumnStats->updateTotals($event['project_id'], date('Y-m-d'));
+            $this->projectDailyStats->updateTotals($event['project_id'], date('Y-m-d'));
         }
     }
 }
diff --git a/app/Template/analytic/lead_cycle_time.php b/app/Template/analytic/lead_cycle_time.php
new file mode 100644
index 00000000..d96bdcb8
--- /dev/null
+++ b/app/Template/analytic/lead_cycle_time.php
@@ -0,0 +1,42 @@
+<div class="page-header">
+    <h2><?= t('Average Lead and Cycle time') ?></h2>
+</div>
+
+<div class="listing">
+    <ul>
+        <li><?= t('Average lead time: ').'<strong>'.$this->dt->duration($average['avg_lead_time']) ?></strong></li>
+        <li><?= t('Average cycle time: ').'<strong>'.$this->dt->duration($average['avg_cycle_time']) ?></strong></li>
+    </ul>
+</div>
+
+<?php if (empty($metrics)): ?>
+    <p class="alert"><?= t('Not enough data to show the graph.') ?></p>
+<?php else: ?>
+    <section id="analytic-lead-cycle-time">
+
+        <div id="chart" data-metrics='<?= json_encode($metrics) ?>' data-label-cycle="<?= t('Cycle Time') ?>" data-label-lead="<?= t('Lead Time') ?>"></div>
+
+        <form method="post" class="form-inline" action="<?= $this->url->href('analytic', 'leadAndCycleTime', array('project_id' => $project['id'])) ?>" autocomplete="off">
+
+            <?= $this->form->csrf() ?>
+
+            <div class="form-inline-group">
+                <?= $this->form->label(t('Start Date'), 'from') ?>
+                <?= $this->form->text('from', $values, array(), array('required', 'placeholder="'.$this->text->in($date_format, $date_formats).'"'), 'form-date') ?>
+            </div>
+
+            <div class="form-inline-group">
+                <?= $this->form->label(t('End Date'), 'to') ?>
+                <?= $this->form->text('to', $values, array(), array('required', 'placeholder="'.$this->text->in($date_format, $date_formats).'"'), 'form-date') ?>
+            </div>
+
+            <div class="form-inline-group">
+                <input type="submit" value="<?= t('Execute') ?>" class="btn btn-blue"/>
+            </div>
+        </form>
+
+        <p class="alert alert-info">
+            <?= t('This chart show the average lead and cycle time for the last %d tasks over the time.', 1000) ?>
+        </p>
+    </section>
+<?php endif ?>
diff --git a/app/Template/analytic/sidebar.php b/app/Template/analytic/sidebar.php
index 1ffce710..de03bdf8 100644
--- a/app/Template/analytic/sidebar.php
+++ b/app/Template/analytic/sidebar.php
@@ -16,5 +16,8 @@
         <li>
             <?= $this->url->link(t('Average time into each column'), 'analytic', 'averageTimeByColumn', array('project_id' => $project['id'])) ?>
         </li>
+        <li>
+            <?= $this->url->link(t('Lead and cycle time'), 'analytic', 'leadAndCycleTime', array('project_id' => $project['id'])) ?>
+        </li>
     </ul>
 </div>
\ No newline at end of file