Calculate the time spent based on the timetable

16 files changed, 397 insertions, 68 deletions

diff --git a/app/Controller/Base.php b/app/Controller/Base.php
index ec202c06..a4e94343 100644
--- a/app/Controller/Base.php
+++ b/app/Controller/Base.php
@@ -67,7 +67,6 @@ use Symfony\Component\EventDispatcher\Event;
  * @property \Model\CommentHistory         $commentHistory
  * @property \Model\SubtaskHistory         $subtaskHistory
  * @property \Model\SubtaskTimeTracking    $subtaskTimeTracking
- * @property \Model\TimeTracking           $timeTracking
  * @property \Model\User                   $user
  * @property \Model\UserSession            $userSession
  * @property \Model\Webhook                $webhook
diff --git a/app/Controller/Config.php b/app/Controller/Config.php
index 01c7ad53..bee897be 100644
--- a/app/Controller/Config.php
+++ b/app/Controller/Config.php
@@ -38,7 +38,11 @@ class Config extends Base
     {
         if ($this->request->isPost()) {
 
-            $values = $this->request->getValues() + array('subtask_restriction' => 0, 'subtask_time_tracking' => 0);
+            $values =  $this->request->getValues();
+
+            if ($redirect === 'board') {
+                $values += array('subtask_restriction' => 0, 'subtask_time_tracking' => 0);
+            }
 
             if ($this->config->save($values)) {
                 $this->config->reload();
diff --git a/app/Model/Base.php b/app/Model/Base.php
index 8a90e286..0217aae3 100644
--- a/app/Model/Base.php
+++ b/app/Model/Base.php
@@ -43,7 +43,7 @@ use Pimple\Container;
  * @property \Model\TaskLink               $taskLink
  * @property \Model\TaskPosition           $taskPosition
  * @property \Model\TaskValidator          $taskValidator
- * @property \Model\TimeTracking           $timeTracking
+ * @property \Model\Timetable              $timetable
  * @property \Model\SubtaskTimeTracking    $subtaskTimeTracking
  * @property \Model\User                   $user
  * @property \Model\UserSession            $userSession
diff --git a/app/Model/DateParser.php b/app/Model/DateParser.php
index 8a4d3edd..a0d10a36 100644
--- a/app/Model/DateParser.php
+++ b/app/Model/DateParser.php
@@ -13,6 +13,47 @@ use DateTime;
 class DateParser extends Base
 {
     /**
+     * Return true if the date is within the date range
+     *
+     * @access public
+     * @param  DateTime  $date
+     * @param  DateTime  $start
+     * @param  DateTime  $end
+     * @return boolean
+     */
+    public function withinDateRange(DateTime $date, DateTime $start, DateTime $end)
+    {
+        return $date >= $start && $date <= $end;
+    }
+
+    /**
+     * Get the total number of hours between 2 datetime objects
+     * Minutes are rounded to the nearest quarter
+     *
+     * @access public
+     * @param  DateTime $d1
+     * @param  DateTime $d2
+     * @return float
+     */
+    public function getHours(DateTime $d1, DateTime $d2)
+    {
+        $seconds = $this->getRoundedSeconds(abs($d1->getTimestamp() - $d2->getTimestamp()));
+        return round($seconds / 3600, 2);
+    }
+
+    /**
+     * Round the timestamp to the nearest quarter
+     *
+     * @access public
+     * @param  integer    $seconds   Timestamp
+     * @return integer
+     */
+    public function getRoundedSeconds($seconds)
+    {
+        return (int) round($seconds / (15 * 60)) * (15 * 60);
+    }
+
+    /**
      * Return a timestamp if the given date format is correct otherwise return 0
      *
      * @access public
diff --git a/app/Model/SubtaskTimeTracking.php b/app/Model/SubtaskTimeTracking.php
index 8b197c46..ab0d7c54 100644
--- a/app/Model/SubtaskTimeTracking.php
+++ b/app/Model/SubtaskTimeTracking.php
@@ -2,6 +2,8 @@
 
 namespace Model;
 
+use DateTime;
+
 /**
  * Subtask timesheet
  *
@@ -33,6 +35,7 @@ class SubtaskTimeTracking extends Base
                         self::TABLE.'.subtask_id',
                         self::TABLE.'.end',
                         self::TABLE.'.start',
+                        self::TABLE.'.time_spent',
                         Subtask::TABLE.'.task_id',
                         Subtask::TABLE.'.title AS subtask_title',
                         Task::TABLE.'.title AS task_title',
@@ -60,6 +63,7 @@ class SubtaskTimeTracking extends Base
                         self::TABLE.'.subtask_id',
                         self::TABLE.'.end',
                         self::TABLE.'.start',
+                        self::TABLE.'.time_spent',
                         self::TABLE.'.user_id',
                         Subtask::TABLE.'.task_id',
                         Subtask::TABLE.'.title AS subtask_title',
@@ -89,6 +93,7 @@ class SubtaskTimeTracking extends Base
                         self::TABLE.'.subtask_id',
                         self::TABLE.'.end',
                         self::TABLE.'.start',
+                        self::TABLE.'.time_spent',
                         self::TABLE.'.user_id',
                         Subtask::TABLE.'.task_id',
                         Subtask::TABLE.'.title AS subtask_title',
@@ -235,7 +240,11 @@ class SubtaskTimeTracking extends Base
      */
     public function logEndTime($subtask_id, $user_id)
     {
-        $this->updateSubtaskTimeSpent($subtask_id, $user_id);
+        $time_spent = $this->getTimeSpent($subtask_id, $user_id);
+
+        if ($time_spent > 0) {
+            $this->updateSubtaskTimeSpent($subtask_id, $time_spent);
+        }
 
         return $this->db
                     ->table(self::TABLE)
@@ -243,11 +252,60 @@ class SubtaskTimeTracking extends Base
                     ->eq('user_id', $user_id)
                     ->eq('end', 0)
                     ->update(array(
-                        'end' => time()
+                        'end' => time(),
+                        'time_spent' => $time_spent,
                     ));
     }
 
     /**
+     * Calculate the time spent when the clock is stopped
+     *
+     * @access public
+     * @param  integer   $subtask_id
+     * @param  integer   $user_id
+     * @return float
+     */
+    public function getTimeSpent($subtask_id, $user_id)
+    {
+        $start_time = $this->db
+                           ->table(self::TABLE)
+                           ->eq('subtask_id', $subtask_id)
+                           ->eq('user_id', $user_id)
+                           ->eq('end', 0)
+                           ->findOneColumn('start');
+
+        if ($start_time) {
+
+            $start = new DateTime;
+            $start->setTimestamp($start_time);
+
+            return $this->timetable->calculateEffectiveDuration($user_id, $start, new DateTime);
+        }
+
+        return 0;
+    }
+
+    /**
+     * Update subtask time spent
+     *
+     * @access public
+     * @param  integer   $subtask_id
+     * @param  float     $time_spent
+     * @return bool
+     */
+    public function updateSubtaskTimeSpent($subtask_id, $time_spent)
+    {
+        $subtask = $this->subtask->getById($subtask_id);
+
+        // Fire the event subtask.update
+        return $this->subtask->update(array(
+            'id' => $subtask['id'],
+            'time_spent' => $subtask['time_spent'] + $time_spent,
+            'task_id' => $subtask['task_id'],
+       ));
+    }
+
+    /**
      * Update task time tracking based on subtasks time tracking
      *
      * @access public
@@ -289,31 +347,4 @@ class SubtaskTimeTracking extends Base
                     )
                     ->findOne();
     }
-
-    /**
-     * Update subtask time spent based on the punch clock table
-     *
-     * @access public
-     * @param  integer   $subtask_id
-     * @param  integer   $user_id
-     * @return bool
-     */
-    public function updateSubtaskTimeSpent($subtask_id, $user_id)
-    {
-        $start_time = $this->db
-                           ->table(self::TABLE)
-                           ->eq('subtask_id', $subtask_id)
-                           ->eq('user_id', $user_id)
-                           ->eq('end', 0)
-                           ->findOneColumn('start');
-
-        $subtask = $this->subtask->getById($subtask_id);
-
-        return $start_time &&
-               $this->subtask->update(array(      // Fire the event subtask.update
-                    'id' => $subtask['id'],
-                    'time_spent' => $subtask['time_spent'] + round((time() - $start_time) / 3600, 1),
-                    'task_id' => $subtask['task_id'],
-               ));
-    }
 }
diff --git a/app/Model/Timetable.php b/app/Model/Timetable.php
index eb37cefd..205c2c20 100644
--- a/app/Model/Timetable.php
+++ b/app/Model/Timetable.php
@@ -25,6 +25,94 @@ class Timetable extends Base
     private $timeoff;
 
     /**
+     * Calculate effective worked hours by taking into consideration the timetable
+     *
+     * @access public
+     * @param  integer     $user_id
+     * @param  \DateTime   $start
+     * @param  \DateTime   $end
+     * @return float
+     */
+    public function calculateEffectiveDuration($user_id, DateTime $start, DateTime $end)
+    {
+        $end_timetable = clone($end);
+        $end_timetable->setTime(23, 59);
+
+        $timetable = $this->calculate($user_id, $start, $end_timetable);
+        $found_start = false;
+        $hours = 0;
+
+        // The user has no timetable
+        if (empty($this->week)) {
+            return $this->dateParser->getHours($start, $end);
+        }
+
+        foreach ($timetable as $slot) {
+
+            $isStartSlot = $this->dateParser->withinDateRange($start, $slot[0], $slot[1]);
+            $isEndSlot = $this->dateParser->withinDateRange($end, $slot[0], $slot[1]);
+
+            // Start and end are within the same time slot
+            if ($isStartSlot && $isEndSlot) {
+                return $this->dateParser->getHours($start, $end);
+            }
+
+            // We found the start slot
+            if (! $found_start && $isStartSlot) {
+                $found_start = true;
+                $hours = $this->dateParser->getHours($start, $slot[1]);
+            }
+            else if ($found_start) {
+
+                // We found the end slot
+                if ($isEndSlot) {
+                    $hours += $this->dateParser->getHours($slot[0], $end);
+                    break;
+                }
+                else {
+
+                    // Sum hours of the intermediate time slots
+                    $hours += $this->dateParser->getHours($slot[0], $slot[1]);
+                }
+            }
+        }
+
+        // The start date was not found in regular hours so we get the nearest time slot
+        if (! empty($timetable) && ! $found_start) {
+            $slot = $this->findClosestTimeSlot($start, $timetable);
+
+            if ($start < $slot[0]) {
+                return $this->calculateEffectiveDuration($user_id, $slot[0], $end);
+            }
+        }
+
+        return $hours;
+    }
+
+    /**
+     * Find the nearest time slot
+     *
+     * @access public
+     * @param  DateTime  $date
+     * @param  array     $timetable
+     * @return array
+     */
+    public function findClosestTimeSlot(DateTime $date, array $timetable)
+    {
+        $values = array();
+
+        foreach ($timetable as $slot) {
+            $t1 = abs($slot[0]->getTimestamp() - $date->getTimestamp());
+            $t2 = abs($slot[1]->getTimestamp() - $date->getTimestamp());
+
+            $values[] = min($t1, $t2);
+        }
+
+        asort($values);
+        return $timetable[key($values)];
+    }
+
+    /**
      * Get the timetable for a user for a given date range
      *
      * @access public
diff --git a/app/Schema/Mysql.php b/app/Schema/Mysql.php
index 03868748..b4db10a4 100644
--- a/app/Schema/Mysql.php
+++ b/app/Schema/Mysql.php
@@ -6,7 +6,12 @@ use PDO;
 use Core\Security;
 use Model\Link;
 
-const VERSION = 52;
+const VERSION = 53;
+
+function version_53($pdo)
+{
+    $pdo->exec("ALTER TABLE subtask_time_tracking ADD COLUMN time_spent FLOAT DEFAULT 0");
+}
 
 function version_52($pdo)
 {
diff --git a/app/Schema/Postgres.php b/app/Schema/Postgres.php
index 124aec76..331205fa 100644
--- a/app/Schema/Postgres.php
+++ b/app/Schema/Postgres.php
@@ -6,7 +6,12 @@ use PDO;
 use Core\Security;
 use Model\Link;
 
-const VERSION = 33;
+const VERSION = 34;
+
+function version_34($pdo)
+{
+    $pdo->exec("ALTER TABLE subtask_time_tracking ADD COLUMN time_spent REAL DEFAULT 0");
+}
 
 function version_33($pdo)
 {
diff --git a/app/Schema/Sqlite.php b/app/Schema/Sqlite.php
index 818ed78d..551e186e 100644
--- a/app/Schema/Sqlite.php
+++ b/app/Schema/Sqlite.php
@@ -6,7 +6,12 @@ use Core\Security;
 use PDO;
 use Model\Link;
 
-const VERSION = 51;
+const VERSION = 52;
+
+function version_52($pdo)
+{
+    $pdo->exec("ALTER TABLE subtask_time_tracking ADD COLUMN time_spent REAL DEFAULT 0");
+}
 
 function version_51($pdo)
 {
diff --git a/app/ServiceProvider/ClassProvider.php b/app/ServiceProvider/ClassProvider.php
index 6f597a5c..fc71ebf9 100644
--- a/app/ServiceProvider/ClassProvider.php
+++ b/app/ServiceProvider/ClassProvider.php
@@ -55,7 +55,6 @@ class ClassProvider implements ServiceProviderInterface
             'TimetableWeek',
             'TimetableOff',
             'TimetableExtra',
-            'TimeTracking',
             'User',
             'UserSession',
             'Webhook',
diff --git a/app/Template/task/time_tracking.php b/app/Template/task/time_tracking.php
index 1dea0f0b..55d33e5e 100644
--- a/app/Template/task/time_tracking.php
+++ b/app/Template/task/time_tracking.php
@@ -6,10 +6,11 @@
 <?php else: ?>
     <table class="table-fixed">
         <tr>
-            <th class="column-20"><?= $subtask_paginator->order(t('User'), 'username') ?></th>
-            <th class="column-30"><?= $subtask_paginator->order(t('Subtask'), 'subtask_title') ?></th>
-            <th><?= $subtask_paginator->order(t('Start'), 'start') ?></th>
-            <th><?= $subtask_paginator->order(t('End'), 'end') ?></th>
+            <th class="column-15"><?= $subtask_paginator->order(t('User'), 'username') ?></th>
+            <th><?= $subtask_paginator->order(t('Subtask'), 'subtask_title') ?></th>
+            <th class="column-20"><?= $subtask_paginator->order(t('Start'), 'start') ?></th>
+            <th class="column-20"><?= $subtask_paginator->order(t('End'), 'end') ?></th>
+            <th class="column-10"><?= $subtask_paginator->order(t('Time spent'), 'time_spent') ?></th>
         </tr>
         <?php foreach ($subtask_paginator->getCollection() as $record): ?>
         <tr>
@@ -17,6 +18,7 @@
             <td><?= t($record['subtask_title']) ?></td>
             <td><?= dt('%B %e, %Y at %k:%M %p', $record['start']) ?></td>
             <td><?= dt('%B %e, %Y at %k:%M %p', $record['end']) ?></td>
+            <td><?= n($record['time_spent']).' '.t('hours') ?></td>
         </tr>
         <?php endforeach ?>
     </table>
diff --git a/app/Template/user/timesheet.php b/app/Template/user/timesheet.php
index 4f052006..3ae84df0 100644
--- a/app/Template/user/timesheet.php
+++ b/app/Template/user/timesheet.php
@@ -8,10 +8,11 @@
 <?php else: ?>
     <table class="table-fixed">
         <tr>
-            <th class="column-20"><?= $subtask_paginator->order(t('Task'), 'task_title') ?></th>
-            <th class="column-20"><?= $subtask_paginator->order(t('Subtask'), 'subtask_title') ?></th>
-            <th><?= $subtask_paginator->order(t('Start'), 'start') ?></th>
-            <th><?= $subtask_paginator->order(t('End'), 'end') ?></th>
+            <th class="column-25"><?= $subtask_paginator->order(t('Task'), 'task_title') ?></th>
+            <th class="column-25"><?= $subtask_paginator->order(t('Subtask'), 'subtask_title') ?></th>
+            <th class="column-20"><?= $subtask_paginator->order(t('Start'), 'start') ?></th>
+            <th class="column-20"><?= $subtask_paginator->order(t('End'), 'end') ?></th>
+            <th class="column-10"><?= $subtask_paginator->order(t('Time spent'), 'time_spent') ?></th>
         </tr>
         <?php foreach ($subtask_paginator->getCollection() as $record): ?>
         <tr>
@@ -19,6 +20,7 @@
             <td><?= $this->a($this->e($record['subtask_title']), 'task', 'show', array('project_id' => $record['project_id'], 'task_id' => $record['task_id'])) ?></td>
             <td><?= dt('%B %e, %Y at %k:%M %p', $record['start']) ?></td>
             <td><?= dt('%B %e, %Y at %k:%M %p', $record['end']) ?></td>
+            <td><?= n($record['time_spent']).' '.t('hours') ?></td>
         </tr>
         <?php endforeach ?>
     </table>
diff --git a/composer.lock b/composer.lock
index ef61d8cc..ebaa06fc 100644
--- a/composer.lock
+++ b/composer.lock
@@ -88,12 +88,12 @@
             "source": {
                 "type": "git",
                 "url": "https://github.com/fguillot/picoDb.git",
-                "reference": "3bc7a6ccdaaa675bc90610f7fe8c1dd6044d2a9e"
+                "reference": "da0380575afdfd35a1ce1fa8dc36f366ef577172"
             },
             "dist": {
                 "type": "zip",
-                "url": "https://api.github.com/repos/fguillot/picoDb/zipball/3bc7a6ccdaaa675bc90610f7fe8c1dd6044d2a9e",
-                "reference": "3bc7a6ccdaaa675bc90610f7fe8c1dd6044d2a9e",
+                "url": "https://api.github.com/repos/fguillot/picoDb/zipball/da0380575afdfd35a1ce1fa8dc36f366ef577172",
+                "reference": "da0380575afdfd35a1ce1fa8dc36f366ef577172",
                 "shasum": ""
             },
             "require": {
@@ -117,7 +117,7 @@
             ],
             "description": "Minimalist database query builder",
             "homepage": "https://github.com/fguillot/picoDb",
-            "time": "2015-03-06 02:33:25"
+            "time": "2015-03-14 23:30:27"
         },
         {
             "name": "fguillot/simple-validator",
@@ -341,16 +341,16 @@
         },
         {
             "name": "swiftmailer/swiftmailer",
-            "version": "v5.3.1",
+            "version": "v5.4.0",
             "source": {
                 "type": "git",
                 "url": "https://github.com/swiftmailer/swiftmailer.git",
-                "reference": "c5f963e7f9d6f6438fda4f22d5cc2db296ec621a"
+                "reference": "31454f258f10329ae7c48763eb898a75c39e0a9f"
             },
             "dist": {
                 "type": "zip",
-                "url": "https://api.github.com/repos/swiftmailer/swiftmailer/zipball/c5f963e7f9d6f6438fda4f22d5cc2db296ec621a",
-                "reference": "c5f963e7f9d6f6438fda4f22d5cc2db296ec621a",
+                "url": "https://api.github.com/repos/swiftmailer/swiftmailer/zipball/31454f258f10329ae7c48763eb898a75c39e0a9f",
+                "reference": "31454f258f10329ae7c48763eb898a75c39e0a9f",
                 "shasum": ""
             },
             "require": {
@@ -362,7 +362,7 @@
             "type": "library",
             "extra": {
                 "branch-alias": {
-                    "dev-master": "5.3-dev"
+                    "dev-master": "5.4-dev"
                 }
             },
             "autoload": {
@@ -389,7 +389,7 @@
                 "mail",
                 "mailer"
             ],
-            "time": "2014-12-05 14:17:14"
+            "time": "2015-03-14 06:06:39"
         },
         {
             "name": "symfony/console",
diff --git a/tests/units/DateParserTest.php b/tests/units/DateParserTest.php
index 5828fc48..9403063b 100644
--- a/tests/units/DateParserTest.php
+++ b/tests/units/DateParserTest.php
@@ -6,6 +6,34 @@ use Model\DateParser;
 
 class DateParserTest extends Base
 {
+    public function testDateRange()
+    {
+        $d = new DateParser($this->container);
+
+        $this->assertTrue($d->withinDateRange(new DateTime('2015-03-14 15:30:00'), new DateTime('2015-03-14 15:00:00'), new DateTime('2015-03-14 16:00:00')));
+        $this->assertFalse($d->withinDateRange(new DateTime('2015-03-14 15:30:00'), new DateTime('2015-03-14 16:00:00'), new DateTime('2015-03-14 17:00:00')));
+    }
+
+    public function testRoundSeconds()
+    {
+        $d = new DateParser($this->container);
+        $this->assertEquals('16:30', date('H:i', $d->getRoundedSeconds(strtotime('16:28'))));
+        $this->assertEquals('16:00', date('H:i', $d->getRoundedSeconds(strtotime('16:02'))));
+        $this->assertEquals('16:15', date('H:i', $d->getRoundedSeconds(strtotime('16:14'))));
+        $this->assertEquals('17:00', date('H:i', $d->getRoundedSeconds(strtotime('16:58'))));
+    }
+
+    public function testGetHours()
+    {
+        $d = new DateParser($this->container);
+
+        $this->assertEquals(1, $d->getHours(new DateTime('2015-03-14 15:00:00'), new DateTime('2015-03-14 16:00:00')));
+        $this->assertEquals(2.5, $d->getHours(new DateTime('2015-03-14 15:00:00'), new DateTime('2015-03-14 17:30:00')));
+        $this->assertEquals(2.75, $d->getHours(new DateTime('2015-03-14 15:00:00'), new DateTime('2015-03-14 17:45:00')));
+        $this->assertEquals(3, $d->getHours(new DateTime('2015-03-14 14:57:00'), new DateTime('2015-03-14 17:58:00')));
+        $this->assertEquals(3, $d->getHours(new DateTime('2015-03-14 14:57:00'), new DateTime('2015-03-14 11:58:00')));
+    }
+
     public function testValidDate()
     {
         $d = new DateParser($this->container);
diff --git a/tests/units/SubtaskTimeTrackingTest.php b/tests/units/SubtaskTimeTrackingTest.php
index 90650e42..e15e60da 100644
--- a/tests/units/SubtaskTimeTrackingTest.php
+++ b/tests/units/SubtaskTimeTrackingTest.php
@@ -176,38 +176,35 @@ class SubtaskTimeTrackingTest extends Base
         $this->assertEquals(7, $s->create(array('title' => 'subtask #7', 'task_id' => 2)));
         $this->assertEquals(8, $s->create(array('title' => 'subtask #8', 'task_id' => 2)));
 
-        // Create a couple of time slots
-        $now = time();
-
         // Slot start before and finish inside the calendar time range
-        $this->container['db']->table(SubtaskTimeTracking::TABLE)->insert(array('user_id' => 1, 'subtask_id' => 1, 'start' => $now - 86400, 'end' => $now + 3600));
+        $this->container['db']->table(SubtaskTimeTracking::TABLE)->insert(array('user_id' => 1, 'subtask_id' => 1, 'start' => strtotime('-1 day'), 'end' => strtotime('+1 hour')));
 
         // Slot start inside time range and finish after the time range
-        $this->container['db']->table(SubtaskTimeTracking::TABLE)->insert(array('user_id' => 1, 'subtask_id' => 2, 'start' => $now + 3600, 'end' => $now + 2*86400));
+        $this->container['db']->table(SubtaskTimeTracking::TABLE)->insert(array('user_id' => 1, 'subtask_id' => 2, 'start' => strtotime('+1 hour'), 'end' => strtotime('+2 days')));
 
         // Start before time range and finish inside time range
-        $this->container['db']->table(SubtaskTimeTracking::TABLE)->insert(array('user_id' => 1, 'subtask_id' => 3, 'start' => $now - 86400, 'end' => $now + 1.5*86400));
+        $this->container['db']->table(SubtaskTimeTracking::TABLE)->insert(array('user_id' => 1, 'subtask_id' => 3, 'start' => strtotime('-1 day'), 'end' => strtotime('+1.5 days')));
 
         // Start and finish inside time range
-        $this->container['db']->table(SubtaskTimeTracking::TABLE)->insert(array('user_id' => 1, 'subtask_id' => 4, 'start' => $now + 3600, 'end' => $now + 2*3600));
+        $this->container['db']->table(SubtaskTimeTracking::TABLE)->insert(array('user_id' => 1, 'subtask_id' => 4, 'start' => strtotime('+1 hour'), 'end' => strtotime('+2 hours')));
 
         // Start and finish after the time range
-        $this->container['db']->table(SubtaskTimeTracking::TABLE)->insert(array('user_id' => 1, 'subtask_id' => 5, 'start' => $now + 2*86400, 'end' => $now + 3*86400));
+        $this->container['db']->table(SubtaskTimeTracking::TABLE)->insert(array('user_id' => 1, 'subtask_id' => 5, 'start' => strtotime('+2 days'), 'end' => strtotime('+3 days')));
 
         // Start and finish before the time range
-        $this->container['db']->table(SubtaskTimeTracking::TABLE)->insert(array('user_id' => 1, 'subtask_id' => 6, 'start' => $now - 2*86400, 'end' => $now - 86400));
+        $this->container['db']->table(SubtaskTimeTracking::TABLE)->insert(array('user_id' => 1, 'subtask_id' => 6, 'start' => strtotime('-2 days'), 'end' => strtotime('-1 day')));
 
         // Start before time range and not finished
-        $this->container['db']->table(SubtaskTimeTracking::TABLE)->insert(array('user_id' => 1, 'subtask_id' => 7, 'start' => $now - 86400));
+        $this->container['db']->table(SubtaskTimeTracking::TABLE)->insert(array('user_id' => 1, 'subtask_id' => 7, 'start' => strtotime('-1 day')));
 
         // Start inside time range and not finish
-        $this->container['db']->table(SubtaskTimeTracking::TABLE)->insert(array('user_id' => 1, 'subtask_id' => 8, 'start' => $now + 3200));
+        $this->container['db']->table(SubtaskTimeTracking::TABLE)->insert(array('user_id' => 1, 'subtask_id' => 8, 'start' => strtotime('+3200 seconds')));
 
         $timesheet = $st->getUserTimesheet(1);
         $this->assertNotEmpty($timesheet);
         $this->assertCount(8, $timesheet);
 
-        $events = $st->getUserCalendarEvents(1, date('Y-m-d', $now), date('Y-m-d', $now + 86400));
+        $events = $st->getUserCalendarEvents(1, date('Y-m-d'), date('Y-m-d', strtotime('+2 day')));
         $this->assertNotEmpty($events);
         $this->assertCount(6, $events);
         $this->assertEquals(1, $events[0]['subtask_id']);
@@ -217,14 +214,14 @@ class SubtaskTimeTrackingTest extends Base
         $this->assertEquals(7, $events[4]['subtask_id']);
         $this->assertEquals(8, $events[5]['subtask_id']);
 
-        $events = $st->getProjectCalendarEvents(1, date('Y-m-d', $now), date('Y-m-d', $now + 86400));
+        $events = $st->getProjectCalendarEvents(1, date('Y-m-d'), date('Y-m-d', strtotime('+2 days')));
         $this->assertNotEmpty($events);
         $this->assertCount(3, $events);
         $this->assertEquals(1, $events[0]['subtask_id']);
         $this->assertEquals(2, $events[1]['subtask_id']);
         $this->assertEquals(3, $events[2]['subtask_id']);
 
-        $events = $st->getProjectCalendarEvents(2, date('Y-m-d', $now), date('Y-m-d', $now + 86400));
+        $events = $st->getProjectCalendarEvents(2, date('Y-m-d'), date('Y-m-d', strtotime('+2 days')));
         $this->assertNotEmpty($events);
         $this->assertCount(3, $events);
         $this->assertEquals(4, $events[0]['subtask_id']);
diff --git a/tests/units/TimetableTest.php b/tests/units/TimetableTest.php
index 4b4dcc83..c88371f9 100644
--- a/tests/units/TimetableTest.php
+++ b/tests/units/TimetableTest.php
@@ -127,4 +127,127 @@ class TimetableTest extends Base
         $this->assertEquals($friday->format('Y-m-d').' 13:00', $timetable[4][0]->format('Y-m-d H:i'));
         $this->assertEquals($friday->format('Y-m-d').' 17:00', $timetable[4][1]->format('Y-m-d H:i'));
     }
+
+    public function testClosestTimeSlot()
+    {
+        $w = new TimetableWeek($this->container);
+        $t = new Timetable($this->container);
+
+        $this->assertNotFalse($w->create(1, 1, '09:30', '12:00'));
+        $this->assertNotFalse($w->create(1, 1, '13:00', '17:00'));
+        $this->assertNotFalse($w->create(1, 2, '09:30', '12:00'));
+        $this->assertNotFalse($w->create(1, 2, '13:00', '17:00'));
+
+        $monday = new DateTime('next Monday');
+        $tuesday = new DateTime('next Tuesday');
+
+        $timetable = $t->calculate(1, new DateTime('next Monday'), new DateTime('next Monday + 6 days'));
+        $this->assertNotEmpty($timetable);
+        $this->assertCount(4, $timetable);
+
+        // Start to work before timetable
+        $date = new DateTime('next Monday');
+        $date->setTime(5, 02);
+
+        $slot = $t->findClosestTimeSlot($date, $timetable);
+        $this->assertNotEmpty($slot);
+        $this->assertEquals($monday->format('Y-m-d').' 09:30', $slot[0]->format('Y-m-d H:i'));
+        $this->assertEquals($monday->format('Y-m-d').' 12:00', $slot[1]->format('Y-m-d H:i'));
+
+        // Start to work at the end of the timeslot
+        $date = new DateTime('next Monday');
+        $date->setTime(12, 02);
+
+        $slot = $t->findClosestTimeSlot($date, $timetable);
+        $this->assertNotEmpty($slot);
+        $this->assertEquals($monday->format('Y-m-d').' 09:30', $slot[0]->format('Y-m-d H:i'));
+        $this->assertEquals($monday->format('Y-m-d').' 12:00', $slot[1]->format('Y-m-d H:i'));
+
+        // Start to work at lunch time
+        $date = new DateTime('next Monday');
+        $date->setTime(12, 32);
+
+        $slot = $t->findClosestTimeSlot($date, $timetable);
+        $this->assertNotEmpty($slot);
+        $this->assertEquals($monday->format('Y-m-d').' 13:00', $slot[0]->format('Y-m-d H:i'));
+        $this->assertEquals($monday->format('Y-m-d').' 17:00', $slot[1]->format('Y-m-d H:i'));
+
+        // Start to work early in the morning
+        $date = new DateTime('next Tuesday');
+        $date->setTime(8, 02);
+
+        $slot = $t->findClosestTimeSlot($date, $timetable);
+        $this->assertNotEmpty($slot);
+        $this->assertEquals($tuesday->format('Y-m-d').' 09:30', $slot[0]->format('Y-m-d H:i'));
+        $this->assertEquals($tuesday->format('Y-m-d').' 12:00', $slot[1]->format('Y-m-d H:i'));
+    }
+
+    public function testCalculateDuration()
+    {
+        $w = new TimetableWeek($this->container);
+        $t = new Timetable($this->container);
+
+        $this->assertNotFalse($w->create(1, 1, '09:30', '12:00'));
+        $this->assertNotFalse($w->create(1, 1, '13:00', '17:00'));
+        $this->assertNotFalse($w->create(1, 2, '09:30', '12:00'));
+        $this->assertNotFalse($w->create(1, 2, '13:00', '17:00'));
+
+        // Different day
+        $start = new DateTime('next Monday');
+        $start->setTime(16, 02);
+
+        $end = new DateTime('next Tuesday');
+        $end->setTime(10, 03);
+
+        $this->assertEquals(1.5, $t->calculateEffectiveDuration(1, $start, $end));
+
+        // Same time slot
+        $start = new DateTime('next Monday');
+        $start->setTime(16, 02);
+
+        $end = new DateTime('next Monday');
+        $end->setTime(17, 03);
+
+        $this->assertEquals(1, $t->calculateEffectiveDuration(1, $start, $end));
+
+        // Intermediate time slot
+        $start = new DateTime('next Monday');
+        $start->setTime(10, 02);
+
+        $end = new DateTime('next Tuesday');
+        $end->setTime(16, 03);
+
+        $this->assertEquals(11.5, $t->calculateEffectiveDuration(1, $start, $end));
+
+        // Different day
+        $start = new DateTime('next Monday');
+        $start->setTime(9, 02);
+
+        $end = new DateTime('next Tuesday');
+        $end->setTime(10, 03);
+
+        $this->assertEquals(7, $t->calculateEffectiveDuration(1, $start, $end));
+
+        // Start before first time slot
+        $start = new DateTime('next Monday');
+        $start->setTime(5, 32);
+
+        $end = new DateTime('next Tuesday');
+        $end->setTime(11, 17);
+
+        $this->assertEquals(8.25, $t->calculateEffectiveDuration(1, $start, $end));
+    }
+
+    public function testCalculateDurationWithEmptyTimetable()
+    {
+        $t = new Timetable($this->container);
+
+        $start = new DateTime('next Monday');
+        $start->setTime(16, 02);
+
+        $end = new DateTime('next Monday');
+        $end->setTime(17, 03);
+
+        $this->assertEquals(1, $t->calculateEffectiveDuration(1, $start, $end));
+    }
 }