Display subtask time tracking in the calendar

4 files changed, 156 insertions, 35 deletions

diff --git a/app/Controller/Calendar.php b/app/Controller/Calendar.php
index 0e749558..1c7ac7c0 100644
--- a/app/Controller/Calendar.php
+++ b/app/Controller/Calendar.php
@@ -41,24 +41,27 @@ class Calendar extends Base
      *
      * @access public
      */
-    public function events()
+    public function project()
     {
-        $this->response->json(
-            $this->taskFilter
-                 ->create()
-                 ->filterByProject($this->request->getIntegerParam('project_id'))
-                 ->filterByCategory($this->request->getIntegerParam('category_id', -1))
-                 ->filterByOwner($this->request->getIntegerParam('owner_id', -1))
-                 ->filterByColumn($this->request->getIntegerParam('column_id', -1))
-                 ->filterBySwimlane($this->request->getIntegerParam('swimlane_id', -1))
-                 ->filterByColor($this->request->getStringParam('color_id'))
-                 ->filterByStatus($this->request->getIntegerParam('is_active', -1))
-                 ->filterByDueDateRange(
-                     $this->request->getStringParam('start'),
-                     $this->request->getStringParam('end')
-                 )
-                 ->toCalendarEvents()
-        );
+        $project_id = $this->request->getIntegerParam('project_id');
+        $start = $this->request->getStringParam('start');
+        $end = $this->request->getStringParam('end');
+
+        $due_tasks = $this->taskFilter
+            ->create()
+            ->filterByProject($project_id)
+            ->filterByCategory($this->request->getIntegerParam('category_id', -1))
+            ->filterByOwner($this->request->getIntegerParam('owner_id', -1))
+            ->filterByColumn($this->request->getIntegerParam('column_id', -1))
+            ->filterBySwimlane($this->request->getIntegerParam('swimlane_id', -1))
+            ->filterByColor($this->request->getStringParam('color_id'))
+            ->filterByStatus($this->request->getIntegerParam('is_active', -1))
+            ->filterByDueDateRange($start, $end)
+            ->toCalendarEvents();
+
+        $subtask_timeslots = $this->subtaskTimeTracking->getProjectCalendarEvents($project_id, $start, $end);
+
+        $this->response->json(array_merge($due_tasks, $subtask_timeslots));
     }
 
     /**
@@ -69,18 +72,19 @@ class Calendar extends Base
     public function user()
     {
         $user_id = $this->request->getIntegerParam('user_id');
+        $start = $this->request->getStringParam('start');
+        $end = $this->request->getStringParam('end');
+
+        $due_tasks = $this->taskFilter
+                          ->create()
+                          ->filterByOwner($user_id)
+                          ->filterByStatus(TaskModel::STATUS_OPEN)
+                          ->filterByDueDateRange($start, $end)
+                          ->toCalendarEvents();
+
+        $subtask_timeslots = $this->subtaskTimeTracking->getUserCalendarEvents($user_id, $start, $end);
 
-        $this->response->json(
-            $this->taskFilter
-                 ->create()
-                 ->filterByOwner($user_id)
-                 ->filterByStatus(TaskModel::STATUS_OPEN)
-                 ->filterByDueDateRange(
-                     $this->request->getStringParam('start'),
-                     $this->request->getStringParam('end')
-                 )
-                 ->toCalendarEvents()
-        );
+        $this->response->json(array_merge($due_tasks, $subtask_timeslots));
     }
 
     /**
diff --git a/app/Model/Acl.php b/app/Model/Acl.php
index e713f2e1..4d4a22a7 100644
--- a/app/Model/Acl.php
+++ b/app/Model/Acl.php
@@ -39,7 +39,7 @@ class Acl extends Base
         'subtask' => '*',
         'task' => '*',
         'tasklink' => '*',
-        'calendar' => array('show', 'events', 'save'),
+        'calendar' => array('show', 'project', 'save'),
     );
 
     /**
diff --git a/app/Model/SubtaskTimeTracking.php b/app/Model/SubtaskTimeTracking.php
index 8093cf80..abd8c8fc 100644
--- a/app/Model/SubtaskTimeTracking.php
+++ b/app/Model/SubtaskTimeTracking.php
@@ -21,7 +21,7 @@ class SubtaskTimeTracking extends Base
      * Get query for user timesheet (pagination)
      *
      * @access public
-     * @param  integer    $user_id       User id
+     * @param  integer    $user_id   User id
      * @return \PicoDb\Table
      */
     public function getUserQuery($user_id)
@@ -36,7 +36,8 @@ class SubtaskTimeTracking extends Base
                         Subtask::TABLE.'.task_id',
                         Subtask::TABLE.'.title AS subtask_title',
                         Task::TABLE.'.title AS task_title',
-                        Task::TABLE.'.project_id'
+                        Task::TABLE.'.project_id',
+                        Task::TABLE.'.color_id'
                     )
                     ->join(Subtask::TABLE, 'id', 'subtask_id')
                     ->join(Task::TABLE, 'id', 'task_id', Subtask::TABLE)
@@ -44,10 +45,10 @@ class SubtaskTimeTracking extends Base
     }
 
     /**
-     * Get query for task (pagination)
+     * Get query for task timesheet (pagination)
      *
      * @access public
-     * @param  integer    $task_id       Task id
+     * @param  integer    $task_id    Task id
      * @return \PicoDb\Table
      */
     public function getTaskQuery($task_id)
@@ -73,6 +74,36 @@ class SubtaskTimeTracking extends Base
     }
 
     /**
+     * Get query for project timesheet (pagination)
+     *
+     * @access public
+     * @param  integer    $project_id   Project id
+     * @return \PicoDb\Table
+     */
+    public function getProjectQuery($project_id)
+    {
+        return $this->db
+                    ->table(self::TABLE)
+                    ->columns(
+                        self::TABLE.'.id',
+                        self::TABLE.'.subtask_id',
+                        self::TABLE.'.end',
+                        self::TABLE.'.start',
+                        self::TABLE.'.user_id',
+                        Subtask::TABLE.'.task_id',
+                        Subtask::TABLE.'.title AS subtask_title',
+                        Task::TABLE.'.project_id',
+                        Task::TABLE.'.color_id',
+                        User::TABLE.'.username',
+                        User::TABLE.'.name AS user_fullname'
+                    )
+                    ->join(Subtask::TABLE, 'id', 'subtask_id')
+                    ->join(Task::TABLE, 'id', 'task_id', Subtask::TABLE)
+                    ->join(User::TABLE, 'id', 'user_id', self::TABLE)
+                    ->eq(Task::TABLE.'.project_id', $project_id);
+    }
+
+    /**
      * Get all recorded time slots for a given user
      *
      * @access public
@@ -88,6 +119,92 @@ class SubtaskTimeTracking extends Base
     }
 
     /**
+     * Get user calendar events
+     *
+     * @access public
+     * @param  integer   $user_id
+     * @param  integer   $start
+     * @param  integer   $end
+     * @return array
+     */
+    public function getUserCalendarEvents($user_id, $start, $end)
+    {
+        $result = $this->getUserQuery($user_id)
+                       ->addCondition($this->getCalendarCondition($start, $end))
+                       ->findAll();
+
+        return $this->toCalendarEvents($result);
+    }
+
+    /**
+     * Get project calendar events
+     *
+     * @access public
+     * @param  integer   $project_id
+     * @param  integer   $start
+     * @param  integer   $end
+     * @return array
+     */
+    public function getProjectCalendarEvents($project_id, $start, $end)
+    {
+        $result = $this->getProjectQuery($project_id)
+                       ->addCondition($this->getCalendarCondition($start, $end))
+                       ->findAll();
+
+        return $this->toCalendarEvents($result);
+    }
+
+    /**
+     * Get time slots that should be displayed in the calendar time range
+     *
+     * @access private
+     * @param  string   $start   ISO8601 start date
+     * @param  string   $end     ISO8601 end date
+     * @return string
+     */
+    private function getCalendarCondition($start, $end)
+    {
+        $start_time = $this->dateParser->getTimestampFromIsoFormat($start);
+        $end_time = $this->dateParser->getTimestampFromIsoFormat($end);
+        $start_column = $this->db->escapeIdentifier('start');
+        $end_column = $this->db->escapeIdentifier('end');
+
+        return "(($start_column >= '$start_time' AND $start_column <= '$end_time') OR ($start_column <= '$start_time' AND $end_column >= '$start_time'))";
+    }
+
+    /**
+     * Convert a record set to calendar events
+     *
+     * @access private
+     * @param  array    $rows
+     * @return array
+     */
+    private function toCalendarEvents(array $rows)
+    {
+        $events = array();
+
+        foreach ($rows as $row) {
+
+            $user = isset($row['username']) ? ' ('.($row['user_fullname'] ?: $row['username']).')' : '';
+
+            $events[] = array(
+                'id' => $row['id'],
+                'subtask_id' => $row['subtask_id'],
+                'title' => t('#%d', $row['task_id']).' '.$row['subtask_title'].$user,
+                'start' => date('Y-m-d\TH:i:s', $row['start']),
+                'end' => date('Y-m-d\TH:i:s', $row['end'] ?: time()),
+                'backgroundColor' => $this->color->getBackgroundColor($row['color_id']),
+                'borderColor' => $this->color->getBorderColor($row['color_id']),
+                'textColor' => 'black',
+                'url' => $this->helper->url('task', 'show', array('task_id' => $row['task_id'], 'project_id' => $row['project_id'])),
+                'editable' => false,
+            );
+        }
+
+        return $events;
+    }
+
+    /**
      * Log start time
      *
      * @access public
@@ -133,7 +250,7 @@ class SubtaskTimeTracking extends Base
      */
     public function updateTaskTimeTracking($task_id)
     {
-        $result = $this->calculateSubtaskTime($task_id); 
+        $result = $this->calculateSubtaskTime($task_id);
 
         if (empty($result['total_spent']) && empty($result['total_estimated'])) {
             return true;
diff --git a/app/Template/calendar/show.php b/app/Template/calendar/show.php
index b258e391..cb5a1109 100644
--- a/app/Template/calendar/show.php
+++ b/app/Template/calendar/show.php
@@ -35,7 +35,7 @@
             <div id="calendar"
                  data-project-id="<?= $project['id'] ?>"
                  data-save-url="<?= $this->u('calendar', 'save', array('project_id' => $project['id'])) ?>"
-                 data-check-url="<?= $this->u('calendar', 'events', array('project_id' => $project['id'])) ?>"
+                 data-check-url="<?= $this->u('calendar', 'project', array('project_id' => $project['id'])) ?>"
                  data-check-interval="<?= $check_interval ?>"
             >
             </div>