Contents


Track time spent on projects with IBM Cloud, Part 2

Give your time-tracking app the ability to record the time spent per project

Retrieve and aggregate time entries to generate reports of hours worked

Comments

Content series:

This content is part # of # in the series: Track time spent on projects with IBM Cloud, Part 2

Stay tuned for additional content in this series.

This content is part of the series:Track time spent on projects with IBM Cloud, Part 2

Stay tuned for additional content in this series.

In Part 1, I explained the concept of a web-based time tracking tool for professionals that bill on an hourly basis. I walked you through building the basic skeleton of the application and showed you the business logic for adding, listing, and editing customer projects. I also explained how to launch a MySQL database instance on IBM Cloud and connect it with the tool.

In this second, concluding segment, I complete the application by adding the ability to record the time spent per project. I show you how to retrieve and aggregate time entries to generate reports of hours worked per project, both online and in a structured format suitable for download and further processing. Finally, I walk you through the steps to upload and deploy the application on IBM Cloud, so that it's online and available in the cloud 24/7.

Get the sample code on GitHubTry the demo

1

Add time entries

The first step is to add a form for users to input the time spent on a project. This form should include fields for the number of hours spent, the date on which the time was spent, and a brief description of the work done. Create this form at $APP_ROOT/views/entries/save.phtml by using the base template shown in Part 1 and placing the code below in the content area:

<?php if (!isset($_POST['submit'])): ?>
  <form method="post" 
    action="<?php echo $data['router']->pathFor('entries-save'); ?>">
    <div class="form-group">
      <label for="pid">Project</label>
      <select class="form-control" id="pid" name="pid">
      <?php foreach ($data['projects'] as $project): ?>
        <option value="<?php echo htmlspecialchars($project['id'], ENT_COMPAT, 'UTF-8'); ?>">
          <?php echo htmlspecialchars($project['name'], ENT_COMPAT, 'UTF-8'); ?>
        </option>
      <?php endforeach; ?>
      </select>
    </div>
    <div class="form-group">
      <label for="date">Date</label>
      <input type="date" class="form-control" id="date" name="date">
    </div>
    <div class="form-group">
      <label for="hours">Hours spent</label>
      <input type="text" class="form-control" id="hours" name="hours">
    </div>
    <div class="form-group">
      <label for="comment">Comment</label>
      <input type="text" class="form-control" id="comment" name="comment">
    </div>
    <div class="form-group">
      <button type="submit" name="submit" class="btn btn-success">Save</button>
    </div>
  </form>  
<?php else: ?>
  <div class="alert alert-success" role="alert">
    <strong>Success!</strong> The time entry was successfully created or updated.
  </div>
<?php endif; ?>

This form contains four fields: a drop-down project selector and input fields for date, hours, and description. Notice that the date field is specified as type date and not text; in compatible browsers, this will inform the browser to display a date picker to make date entry easier for the user. Here's what the form looks like:

Add entry form
Add entry form

Next, add the corresponding code to render and process the form in $APP_ROOT/public/index.php:

<?php
use \Psr\Http\Message\ServerRequestInterface as Request;
use \Psr\Http\Message\ResponseInterface as Response;
 
// ...

// time entry form renderer
$app->get('/entries/save', function (Request $request, Response $response, $args) {
  // query for all project records
  $projects = $this->db->query("SELECT * FROM projects");
  $response = $this->view->render($response, 'entries/save.phtml', [
    'router' => $this->router, 'projects' => $projects
  ]);
  return $response;
})->setName('entries-save');

// time entry form processor
$app->post('/entries/save[/{id}]', function (Request $request, Response $response, $args) {
  // get configuration
  $config = $this->get('settings');
  // get input values
  $params = $request->getParams();
  // check input
  $pid = filter_var($params['pid'], FILTER_SANITIZE_NUMBER_INT);
  if (!(filter_var($pid, FILTER_VALIDATE_INT))) {
    throw new Exception('ERROR: Project is not valid');
  }
  $hours = filter_var($params['hours'], FILTER_SANITIZE_NUMBER_FLOAT, 
    FILTER_FLAG_ALLOW_FRACTION);
  if (!(filter_var($hours, FILTER_VALIDATE_FLOAT))) {
    throw new Exception('ERROR: Time value is not a valid number');
  }
  $comment = filter_var($params['comment'], FILTER_SANITIZE_STRING);
  if (empty($comment)) {
    throw new Exception('ERROR: Comment is not valid');
  }  
  $date = $params['date'];
  if (!($date == date('Y-m-d', strtotime($date)))) {
    throw new Exception('ERROR: Date is not valid');
  }
  // save record
  if (!$this->db->query("INSERT INTO entries (pid, hours, comment, date) 
    VALUES ('$pid', '$hours', '$comment', '$date')")) {
    throw new Exception('Failed to save record: ' . $this->db->error);
  }
  $response = $this->view->render($response, 'entries/save.phtml', [
    'router' => $this->router
  ]);
  return $response;
});

// ...
 
$app->run();

The listing above contains two handlers:

  • The first handler (GET requests) is responsible for rendering the form. It performs a SELECT query on the MySQL database to retrieve a list of projects. This list is then passed to the view template, where it is used to populate the project list selector.
  • The second handler (POST requests) is triggered when the form is submitted. Similar to the project addition/modification routine you already saw in Part 1, it begins by sanitizing and validating the form input to ensure, among other things, that the date provided is a valid date and that the project ID and number of hours are in the correct format. Assuming all tests pass, this handler generates and executes an INSERT query to add the new time entry to the entries table in the MySQL database, and returns control to the user via a success notification.
Result of adding a time entry
Result of adding a time entry
2

List and aggregate time entries

As you continue recording the time spent on your various projects, you're quickly going to start wishing for a way to see the total time spent per project, both to analyze the effort you're putting in and to ensure that you're not exceeding your client's budget.

It's easy enough to add a handler that lists and aggregates the time entries for a specific project by executing a SELECT query on the database, as shown here:

<?php
use \Psr\Http\Message\ServerRequestInterface as Request;
use \Psr\Http\Message\ResponseInterface as Response;
 
// ...

// time entry list controller
$app->get('/entries/{id:[0-9]+}', function (Request $request, Response $response, $args) {
  $id = filter_var($args['id'], FILTER_SANITIZE_NUMBER_INT);
  // query for project name
  $projectResult = $this->db->query("SELECT * FROM projects WHERE id = '$id'");
  $project = $projectResult->fetch_object();
  // query for all time entries
  $entries = $this->db->query("SELECT * FROM entries 
    WHERE pid = '$id' ORDER BY date ASC");
  $response = $this->view->render($response, 'entries/list.phtml', [
    'router' => $this->router, 'entries' => $entries, 'project' => $project
  ]);
  return $response;
})->setName('entries-list');

// ...
 
$app->run();

This handler accepts GET requests for the /entries/ID URL, where ID refers to the project ID. On receipt of such a request, the handler first checks the projects table to ensure that the ID provided refers to a valid project. Assuming it does, the handler then uses the same project ID to return a list of all associated time entries from the entries table, ordered chronologically, and passes this list to the template.

The action then shifts to the $APP_ROOT/views/entries/list.phtml template, which is responsible for displaying the time entries retrieved from the database in an easy-to-read-and-print format. This template also takes care of aggregating the entries to arrive at a total "time spent" number. Here's the code:

<h2>
  Project: <?php echo htmlspecialchars($data['project']->name, 
    ENT_COMPAT, 'UTF-8'); ?>
  <a role="button" class="btn btn-success" 
    href="<?php echo $data['router']->pathFor('entries-list', 
    array('id' => htmlspecialchars($data['project']->id, ENT_COMPAT, 'UTF-8'),
    'download' => true)); ?>">Download data</a>
</h2>
<?php $count = 1; ?>
<?php $total = 0; ?>
<?php if (count($data['entries'])): ?>
<table class="table table-border table-responsive-md">
  <thead>
    <tr>
      <th>#</th>
      <th>Date</th>
      <th>Hours</th>
      <th>Comment</th>
      <th></th>
    </tr>
  </thead>
  <tbody>
  <?php foreach ($data['entries'] as $entry): ?>
  <tr>
    <td><?php echo $count; ?></td>
    <td><?php echo htmlspecialchars($entry['date'], ENT_COMPAT, 'UTF-8'); ?>
      </td>
    <td><?php echo htmlspecialchars($entry['hours'], ENT_COMPAT, 'UTF-8'); ?>
      </td>
    <td><?php echo htmlspecialchars($entry['comment'], ENT_COMPAT, 'UTF-8'); ?>
      </td>
    <td><a role="button" class="btn btn-danger" 
      href="<?php echo $data['router']->pathFor('entries-delete', 
      array('id' => htmlspecialchars($entry['id'], ENT_COMPAT, 'UTF-8'))); ?>">
      Delete entry</a></td>
  </tr>
  <?php $total += $entry['hours']; ?>
  <?php $count++; ?>
  <?php endforeach; ?>
  <tr>
    <td></td>
    <td><strong>TOTAL</strong></td>
    <td><strong><?php echo $total; ?></strong></td>
    <td colspan="2"></td>
  </tr>
  </tbody>
</table>
<?php endif; ?>

The template simply iterates over the list of time entries, displaying them in separate rows of a table. It maintains an ongoing count of the time spent and prints the total in the last row of the table. Here's an example of what the output looks like:

Time report
Time report

You will notice that each entry in the table includes a link to delete the corresponding record, and the entry ID is included in the link as a URL parameter. Let's look at that next.

3

Delete time entries

Each time entry in the list shown in the previous section includes a deletion link. This operation is handled by the /entries/delete handler, which looks like this:

<?php
use \Psr\Http\Message\ServerRequestInterface as Request;
use \Psr\Http\Message\ResponseInterface as Response;
 
// ...
// time entry deletion controller
$app->get('/entries/delete/{id}', function (Request $request, Response $response, $args) {
  $id = filter_var($args['id'], FILTER_SANITIZE_NUMBER_INT);
  if (!$this->db->query("DELETE FROM entries WHERE id = '$id'")) {
    throw new Exception('Failed to delete record.');
  }
  return $response->withHeader('Location', $this->router->pathFor('projects-list'));
})->setName('entries-delete');

// ...
 
$app->run();

Nothing too complex here. The handler accepts a time entry ID and executes a DELETE query for the corresponding record on the database. The user is redirected to the project list page following successful deletion.

Part 1 briefly mentioned project deletion, which is handled by the /projects/delete handler. Project deletion is similar to time entry deletion, with one additional step. Since projects and time entries are linked by a foreign key in the database, you must delete all time entries for a project before you can delete the project itself. Here's what that code looks like:

<?php
use \Psr\Http\Message\ServerRequestInterface as Request;
use \Psr\Http\Message\ResponseInterface as Response;
 
// ...

// project deletion controller
$app->get('/projects/delete/{id}', function (Request $request, Response $response, $args) {
  $id = filter_var($args['id'], FILTER_SANITIZE_NUMBER_INT);
  if (!$this->db->query("DELETE FROM entries WHERE pid = '$id'")) {
    throw new Exception('Failed to delete records.');
  }
  if (!$this->db->query("DELETE FROM projects WHERE id = '$id'")) {
    throw new Exception('Failed to delete record.');
  }
  return $response->withHeader('Location', $this->router->pathFor('projects-list'));
})->setName('projects-delete');

// ...
 
$app->run();
4

Export time reports

In Step 2, you saw the business logic display a summary report of the time worked on a project. Usually, this is not enough: There are many cases where you would want this same report in a structured, downloadable format, perhaps to email to a client or to perform more complex calculations.

Since most of the hard work of retrieving and aggregating the individual time entries is already done by the /entries/ID handler, you can leverage this same handler to produce a CSV-formatted file instead of HTML output. Reusing existing code in this manner is a good example of the DRY ("Don't Repeat Yourself") principle, and also saves you some time.

Update the existing /entries/ID handler to accept an additional download argument which triggers a CSV file download instead of the normal HTML output, as shown below:

<?php
use \Psr\Http\Message\ServerRequestInterface as Request;
use \Psr\Http\Message\ResponseInterface as Response;
 
// ...

// time entry list controller
$app->get('/entries/{id:[0-9]+}[/{download}]', function (Request $request, Response $response, $args) {
  $id = filter_var($args['id'], FILTER_SANITIZE_NUMBER_INT);
  // query for project name
  $projectResult = $this->db->query("SELECT * FROM projects WHERE id = '$id'");
  $project = $projectResult->fetch_object();
  // query for all time entries
  $entries = $this->db->query("SELECT * FROM entries WHERE pid = '$id' 
    ORDER BY date ASC");
  if (isset($args['download'])) {
    $response = $response->withHeader('Content-type', 'text/csv')
                         ->withHeader('Content-Disposition', 
                          'attachment; filename="' . $id .'.csv"')
                         ->withHeader('Expires', '@0')
                         ->withHeader('Cache-Control', 'must-revalidate')
                         ->withHeader('Pragma', 'public');
    $stream = fopen('php://memory', 'r+');
    fwrite($stream, $this->view->fetch('entries/list.csv', [
      'entries' => $entries
    ]));
    return $response->withBody(new \Slim\Http\Stream($stream));                         
  } else {
    $response = $this->view->render($response, 'entries/list.phtml', [
      'router' => $this->router, 'entries' => $entries, 'project' => $project
    ]);
    return $response;
  }
})->setName('entries-list');

// ...
 
$app->run();

This revised version of the handler now serves two purposes: A request without the download flag triggers the same HTML response as before, while a request with the download flag creates a custom Response object with all the headers necessary to trigger a browser download prompt.

In this latter case, the code generates a new PHP stream object and writes the list of entries to it in CSV format using a new CSV template. Here's what that template, located at $APP_ROOT/views/entries/list.csv, looks like:

<?php
$count = 1;
foreach ($data['entries'] as $entry) {
  printf(
    "%d,%s,%.2f,%s" . PHP_EOL, 
    $count, 
    htmlspecialchars($entry['date'], ENT_COMPAT, 'UTF-8'),
    htmlspecialchars($entry['hours'], ENT_COMPAT, 'UTF-8'),
    htmlspecialchars($entry['comment'], ENT_COMPAT, 'UTF-8')
  );
  $count++;
}

The resulting output stream is then transmitted to the browser using the custom Response object. The user can download and save this output stream as a CSV file on the client system, and then open it in any spreadsheet tool or CSV-capable editor. Here's an example of one such CSV file:

CSV output example
CSV output example
5

Deploy to IBM Cloud

In order to connect to the ClearDB MySQL instance, the PHP application needs the database host name, username, password, and database name. You have already specified this information in the application configuration file. However, as an alternative, you can connect the database instance to the application and import these credentials directly from the IBM Cloud environment.

  1. If you wish to use this approach, add the following code to the $APP_ROOT/public/index.php script, before the lines that initialize the database connection:
    <?php
    use \Psr\Http\Message\ServerRequestInterface as Request;
    use \Psr\Http\Message\ResponseInterface as Response;
     
    // autoload files
    require '../vendor/autoload.php';
    require '../config.php';
    
    // if VCAP_SERVICES environment available
    // overwrite local credentials with environment credentials
    if ($services = getenv("VCAP_SERVICES")) {
      $services_json = json_decode($services, true);
      $config['settings']['db']['hostname'] = 
        $services_json['cleardb'][0]['credentials']['hostname'];
      $config['settings']['db']['username'] = 
        $services_json['cleardb'][0]['credentials']['username'];
      $config['settings']['db']['password'] = 
        $services_json['cleardb'][0]['credentials']['password'];
      $config['settings']['db']['name'] = 
        $services_json['cleardb'][0]['credentials']['name'];
    }
    
    //...
  2. At this point, the application is complete, so all that's left is to deploy it to IBM Cloud. First, create the application manifest file, remembering to use a unique host and application name by appending a random string to it (such as your initials).
    ---
    applications:
    - name: timetracker-[initials]
    memory: 256M
    instances: 1
    host: timetracker-[initials]
    buildpack: https://github.com/cloudfoundry/php-buildpack.git
    stack: cflinuxfs2
  3. You must also configure the build pack to use the public/ directory of the application as the web server directory. Create a $APP_ROOT/.bp-config/options.json file with the following content:
    {
        "WEB_SERVER": "httpd",
        "WEBDIR": "public",
        "PHP_VERSION": "{PHP_70_LATEST}"
    }
  4. You must also load the MySQL PHP extension. Create a $APP_ROOT/.bp-config/php/php.ini.d/php.ini file with the following content:
    extension=mysqli.so
  5. Now, go ahead and push the application to IBM Cloud:
    shell> cf api https://api.ng.bluemix.net
    shell> cf login
    shell> cf push
  6. If you choose to import the database credentials from the IBM Cloud environment, bind the ClearDB instance that you initialized earlier to the application as shown below. Remember to use the correct ID for the service instance to ensure that the right instance is bound to the application. You can obtain the service ID from the IBM Cloud dashboard.
    shell> cf bind-service timetracker-[initials] "ClearDB Managed MySQL Database-[id]"
    shell> cf restage timetracker-[initials]

You should now be able to browse to the application at http://timetracker-[initials].mybluemix.net and see the welcome page. In case you don't see it, you can check the debug log.

Conclusion

The goal of this article has been to show you how to build a useful application that can run entirely online, using a cloud database and cloud hosting infrastructure. IBM Cloud's ClearDB database service, coupled with the PHP CloudFoundry buildpack and some framework glue, makes it easy to build database-backed PHP web applications without your having to worry about infrastructure security and scalability.

If you'd like to learn more about the services and tools discussed in this article, start by trying out the live demo of the application. Remember that this is a public demo, so you should be careful not to upload confidential or sensitive information to it. Then, download the code from its GitHub repository, take a closer look to see how it all fits together, and start modifying it to meet your own requirements. Happy coding!


Downloadable resources


Related topics


Comments

Sign in or register to add and subscribe to comments.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Cloud computing
ArticleID=1060152
ArticleTitle=Track time spent on projects with IBM Cloud, Part 2: Give your time-tracking app the ability to record the time spent per project
publish-date=04242018