Add lead and cycle time for projects

3 files changed, 130 insertions, 21 deletions

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();
+    }
+}