diff options
Diffstat (limited to 'app/Model')
| -rw-r--r-- | app/Model/ProjectAnalytic.php | 37 | ||||
| -rw-r--r-- | app/Model/ProjectDailyColumnStats.php (renamed from app/Model/ProjectDailySummary.php) | 42 | ||||
| -rw-r--r-- | app/Model/ProjectDailyStats.php | 72 |
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(); + } +} |