Add cost breakdown for project budget

7 files changed, 142 insertions, 6 deletions

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
@@ -27,6 +27,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
      *
      * @access public
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
@@ -46,6 +46,66 @@ class Budget extends Base
     }
 
     /**
+     * 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
      *
      * @access public
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
@@ -21,6 +21,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
      *
      * @access public
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 @@
+<div class="page-header">
+    <h2><?= t('Budget') ?></h2>
+    <ul>
+        <li><?= $this->a(t('Budget lines'), 'budget', 'create', array('project_id' => $project['id'])) ?></li>
+        <li><?= $this->a(t('Cost breakdown'), 'budget', 'breakdown', array('project_id' => $project['id'])) ?></li>
+    </ul>
+</div>
+
+<?php if ($paginator->isEmpty()): ?>
+    <p class="alert"><?= t('There is nothing to show.') ?></p>
+<?php else: ?>
+    <table class="table-fixed">
+        <tr>
+            <th class="column-20"><?= $paginator->order(t('Task'), 'task_title') ?></th>
+            <th class="column-25"><?= $paginator->order(t('Subtask'), 'subtask_title') ?></th>
+            <th class="column-20"><?= $paginator->order(t('User'), 'username') ?></th>
+            <th class="column-10"><?= t('Cost') ?></th>
+            <th class="column-10"><?= $paginator->order(t('Time spent'), 'time_spent') ?></th>
+            <th class="column-15"><?= $paginator->order(t('Date'), 'start') ?></th>
+        </tr>
+        <?php foreach ($paginator->getCollection() as $record): ?>
+        <tr>
+            <td><?= $this->a($this->e($record['task_title']), 'task', 'show', array('project_id' => $project['id'], 'task_id' => $record['task_id'])) ?></td>
+            <td><?= $this->a($this->e($record['subtask_title']), 'task', 'show', array('project_id' => $project['id'], 'task_id' => $record['task_id'])) ?></td>
+            <td><?= $this->e($record['name'] ?: $record['username']) ?></td>
+            <td><?= n($record['cost']) ?></td>
+            <td><?= n($record['time_spent']).' '.t('hours') ?></td>
+            <td><?= dt('%B %e, %Y', $record['start']) ?></td>
+        </tr>
+        <?php endforeach ?>
+    </table>
+
+    <?= $paginator ?>
+<?php endif ?>
\ 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 @@
     <h2><?= t('Budget') ?></h2>
     <ul>
         <li><?= $this->a(t('Budget lines'), 'budget', 'create', array('project_id' => $project['id'])) ?></li>
-        <li><?= $this->a(t('Burn rate'), 'budget', 'index', array('project_id' => $project['id'])) ?></li>
+        <li><?= $this->a(t('Cost breakdown'), 'budget', 'breakdown', array('project_id' => $project['id'])) ?></li>
     </ul>
 </div>
 
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 @@
     <h2><?= t('Budget') ?></h2>
     <ul>
         <li><?= $this->a(t('Budget lines'), 'budget', 'create', array('project_id' => $project['id'])) ?></li>
-        <li><?= $this->a(t('Burn rate'), 'budget', 'index', array('project_id' => $project['id'])) ?></li>
+        <li><?= $this->a(t('Cost breakdown'), 'budget', 'breakdown', array('project_id' => $project['id'])) ?></li>
     </ul>
 </div>
 
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",