diff options
Diffstat (limited to 'app/Model/ProjectDailyColumnStatsModel.php')
| -rw-r--r-- | app/Model/ProjectDailyColumnStatsModel.php | 254 |
1 files changed, 254 insertions, 0 deletions
diff --git a/app/Model/ProjectDailyColumnStatsModel.php b/app/Model/ProjectDailyColumnStatsModel.php new file mode 100644 index 00000000..a0f14cf4 --- /dev/null +++ b/app/Model/ProjectDailyColumnStatsModel.php @@ -0,0 +1,254 @@ +<?php + +namespace Kanboard\Model; + +use Kanboard\Core\Base; + +/** + * Project Daily Column Stats + * + * @package Kanboard\Model + * @author Frederic Guillot + */ +class ProjectDailyColumnStatsModel extends Base +{ + /** + * SQL table name + * + * @var string + */ + const TABLE = 'project_daily_column_stats'; + + /** + * 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 + * + * @access public + * @param integer $project_id Project id + * @param string $date Record date (YYYY-MM-DD) + * @return boolean + */ + public function updateTotals($project_id, $date) + { + $this->db->startTransaction(); + $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(); + + return true; + } + + /** + * 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) + { + return $this->db->table(self::TABLE) + ->eq('project_id', $project_id) + ->gte('day', $from) + ->lte('day', $to) + ->findOneColumn('COUNT(DISTINCT day)'); + } + + /** + * 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 getAggregatedMetrics($project_id, $from, $to, $field = 'total') + { + $columns = $this->columnModel->getList($project_id); + $metrics = $this->getMetrics($project_id, $from, $to); + return $this->buildAggregate($metrics, $columns, $field); + } + + /** + * Fetch metrics + * + * @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 getMetrics($project_id, $from, $to) + { + return $this->db->table(self::TABLE) + ->eq('project_id', $project_id) + ->gte('day', $from) + ->lte('day', $to) + ->asc(self::TABLE.'.day') + ->findAll(); + } + + /** + * Build aggregate + * + * @access private + * @param array $metrics + * @param array $columns + * @param string $field + * @return array + */ + private function buildAggregate(array &$metrics, array &$columns, $field) + { + $column_ids = array_keys($columns); + $days = array_unique(array_column($metrics, 'day')); + $rows = array(array_merge(array(e('Date')), array_values($columns))); + + foreach ($days as $day) { + $rows[] = $this->buildRowAggregate($metrics, $column_ids, $day, $field); + } + + 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; + } + + /** + * Find the value in the metrics + * + * @access private + * @param array $metrics + * @param string $day + * @param string $column_id + * @param string $field + * @return integer + */ + private function findValueInMetrics(array &$metrics, $day, $column_id, $field) + { + foreach ($metrics as $metric) { + if ($metric['day'] === $day && $metric['column_id'] == $column_id) { + return (int) $metric[$field]; + } + } + + 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, 'score' => 0); + } + + foreach ($scores as $column_id => $score) { + $columns[$column_id]['score'] = (int) $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(TaskModel::TABLE) + ->columns('column_id', 'SUM(score) AS score') + ->eq('project_id', $project_id) + ->eq('is_active', TaskModel::STATUS_OPEN) + ->notNull('score') + ->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(TaskModel::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->configModel->get('cfd_include_closed_tasks') == 1) { + return array(TaskModel::STATUS_OPEN, TaskModel::STATUS_CLOSED); + } + + return array(TaskModel::STATUS_OPEN); + } +} |