From 084272c60ea97f3a835cfccbb3303227d00085e8 Mon Sep 17 00:00:00 2001 From: Frederic Guillot Date: Sun, 15 Mar 2015 17:28:46 -0400 Subject: Add cost breakdown for project budget --- app/Controller/Budget.php | 24 ++++++++++++++++ app/Model/Budget.php | 60 +++++++++++++++++++++++++++++++++++++++ app/Model/HourlyRate.php | 18 ++++++++++++ app/Template/budget/breakdown.php | 34 ++++++++++++++++++++++ app/Template/budget/create.php | 2 +- app/Template/budget/index.php | 2 +- composer.lock | 8 +++--- 7 files changed, 142 insertions(+), 6 deletions(-) create mode 100644 app/Template/budget/breakdown.php diff --git a/app/Controller/Budget.php b/app/Controller/Budget.php index 01090550..b8279f19 100644 --- a/app/Controller/Budget.php +++ b/app/Controller/Budget.php @@ -26,6 +26,30 @@ class Budget extends Base ))); } + /** + * Cost breakdown by users/subtasks/tasks + * + * @access public + */ + public function breakdown() + { + $project = $this->getProject(); + + $paginator = $this->paginator + ->setUrl('budget', 'breakdown', array('project_id' => $project['id'])) + ->setMax(30) + ->setOrder('start') + ->setDirection('DESC') + ->setQuery($this->budget->getBreakdown($project['id'])) + ->calculate(); + + $this->response->html($this->projectLayout('budget/breakdown', array( + 'paginator' => $paginator, + 'project' => $project, + 'title' => t('Budget') + ))); + } + /** * Create budget lines * diff --git a/app/Model/Budget.php b/app/Model/Budget.php index 03a90f7f..827182a3 100644 --- a/app/Model/Budget.php +++ b/app/Model/Budget.php @@ -45,6 +45,66 @@ class Budget extends Base return isset($result['total']) ? (float) $result['total'] : 0; } + /** + * Get breakdown by tasks/subtasks/users + * + * @access public + * @param integer $project_id + * @return \PicoDb\Table + */ + public function getBreakdown($project_id) + { + return $this->db + ->table(SubtaskTimeTracking::TABLE) + ->columns( + SubtaskTimeTracking::TABLE.'.id', + SubtaskTimeTracking::TABLE.'.user_id', + SubtaskTimeTracking::TABLE.'.subtask_id', + SubtaskTimeTracking::TABLE.'.start', + SubtaskTimeTracking::TABLE.'.time_spent', + Subtask::TABLE.'.task_id', + Subtask::TABLE.'.title AS subtask_title', + Task::TABLE.'.title AS task_title', + Task::TABLE.'.project_id', + User::TABLE.'.username', + User::TABLE.'.name' + ) + ->join(Subtask::TABLE, 'id', 'subtask_id') + ->join(Task::TABLE, 'id', 'task_id', Subtask::TABLE) + ->join(User::TABLE, 'id', 'user_id') + ->eq(Task::TABLE.'.project_id', $project_id) + ->filter(array($this, 'applyUserRate')); + } + + /** + * Filter callback to apply the rate according to the effective date + * + * @access public + * @param array $records + * @return array + */ + public function applyUserRate(array $records) + { + $rates = $this->hourlyRate->getAllByProject($records[0]['project_id']); + + foreach ($records as &$record) { + + $hourly_price = 0; + + foreach ($rates as $rate) { + + if ($rate['user_id'] == $record['user_id'] && date('Y-m-d', $rate['date_effective']) <= date('Y-m-d', $record['start'])) { + $hourly_price = $rate['rate']; + break; + } + } + + $record['cost'] = $hourly_price * $record['time_spent']; + } + + return $records; + } + /** * Add a new budget line in the database * diff --git a/app/Model/HourlyRate.php b/app/Model/HourlyRate.php index c2ce3eaf..1550bdae 100644 --- a/app/Model/HourlyRate.php +++ b/app/Model/HourlyRate.php @@ -20,6 +20,24 @@ class HourlyRate extends Base */ const TABLE = 'hourly_rates'; + /** + * Get all user rates for a given project + * + * @access public + * @param integer $project_id + * @return array + */ + public function getAllByProject($project_id) + { + $members = $this->projectPermission->getMembers($project_id); + + if (empty($members)) { + return array(); + } + + return $this->db->table(self::TABLE)->in('user_id', array_keys($members))->desc('date_effective')->findAll(); + } + /** * Get all rates for a given user * diff --git a/app/Template/budget/breakdown.php b/app/Template/budget/breakdown.php new file mode 100644 index 00000000..d4168406 --- /dev/null +++ b/app/Template/budget/breakdown.php @@ -0,0 +1,34 @@ +
+

+ +
+ +isEmpty()): ?> +

+ + + + + + + + + + + getCollection() as $record): ?> + + + + + + + + + +
order(t('Task'), 'task_title') ?>order(t('Subtask'), 'subtask_title') ?>order(t('User'), 'username') ?>order(t('Time spent'), 'time_spent') ?>order(t('Date'), 'start') ?>
a($this->e($record['task_title']), 'task', 'show', array('project_id' => $project['id'], 'task_id' => $record['task_id'])) ?>a($this->e($record['subtask_title']), 'task', 'show', array('project_id' => $project['id'], 'task_id' => $record['task_id'])) ?>e($record['name'] ?: $record['username']) ?>
+ + + \ No newline at end of file diff --git a/app/Template/budget/create.php b/app/Template/budget/create.php index 0ff395c9..5a919ce6 100644 --- a/app/Template/budget/create.php +++ b/app/Template/budget/create.php @@ -2,7 +2,7 @@

diff --git a/app/Template/budget/index.php b/app/Template/budget/index.php index 8bdf1a57..bdeda781 100644 --- a/app/Template/budget/index.php +++ b/app/Template/budget/index.php @@ -2,7 +2,7 @@

diff --git a/composer.lock b/composer.lock index ebaa06fc..3d68e4eb 100644 --- a/composer.lock +++ b/composer.lock @@ -88,12 +88,12 @@ "source": { "type": "git", "url": "https://github.com/fguillot/picoDb.git", - "reference": "da0380575afdfd35a1ce1fa8dc36f366ef577172" + "reference": "d7ef5561d6d76c50717492822813125f9699700a" }, "dist": { "type": "zip", - "url": "https://api.github.com/repos/fguillot/picoDb/zipball/da0380575afdfd35a1ce1fa8dc36f366ef577172", - "reference": "da0380575afdfd35a1ce1fa8dc36f366ef577172", + "url": "https://api.github.com/repos/fguillot/picoDb/zipball/d7ef5561d6d76c50717492822813125f9699700a", + "reference": "d7ef5561d6d76c50717492822813125f9699700a", "shasum": "" }, "require": { @@ -117,7 +117,7 @@ ], "description": "Minimalist database query builder", "homepage": "https://github.com/fguillot/picoDb", - "time": "2015-03-14 23:30:27" + "time": "2015-03-15 21:03:40" }, { "name": "fguillot/simple-validator", -- cgit v1.2.3