Warning Emails in Case of Budget Overrun

Friday, May 30, 2014 by Rainer Stropek

Image source: https://www.flickr.com/photos/dok1/6555922825/, under Creative Commons License

Introduction

Regularly, customers ask us how to send emails when certain things happen in time cockpit (e.g. project runs out of budget, user creates time sheet record for a month that has already been billed, etc.). In this blog post we show how this can be done with a few lines of script code and the following ingredients:

We are going to use our OData Web API to query all projects that have a budget overrun.
For sending nicely formatted emails, we are going to use the Mandrill service. We use this service in lots of projects and are quite happy with it.
We schedule our script in Microsoft Azure. For that, we use Azure's WebJob feature. This would only cost us a few dollars per month.

Please note that the code in this blog article is just to demonstrate the approach. It lacks of many features (e.g. error handling) that would be needed for production.

You are interested in such automatisms for time cockpit but you do not feel comfortable with JavaScript, nodejs, Azure & Co.? Contact us at support@timecockpit.com, we would be more than happy to help.

The Query

New to time cockpit extensibility? If yes, take a look at TCQL, time cockpit's domain-specific query language.

For our example we need to find all projects where the total number of hours is greater than the projects' budget. The following screenshot shows how to develop the query in time cockpit's Administration module (click to enlarge):

The Script

The JavaScript script for querying time cockpit and sending the mails is quite simple. Here is the code including comments describing what is done:

// Get the necessary node modules (needle for http post, mandrill for emails)
var needle = require('needle');
var mandrill = require('mandrill-api/mandrill');

// Query for finding all projects with budget overrun
var tcql = { 
	"query": "From P In Project \
		Where (From T In P.Timesheets Select New With { .TotalHours = Sum(T.DurationInHours) }) > P.BudgetInHours \
		Select New With { \
			P.Code, \
			P.Description, \
			P.BudgetInHours, \
			.TotalEffectiveHours = (From T In P.Timesheets Select New With { .TotalHours = Sum(T.DurationInHours) }) \
		}" };
var dataToSend = JSON.stringify(tcql);

// Send query to time cockpit's OData Web API
needle.post(
	"https://apipreview.timecockpit.com/select", 
	dataToSend,
	{ 
		// Add your time cockpit user/password here
		username: "youruser@yourcompany.com", 
		password: "Y0urP@ssw0rd",
		auth: "basic",
		headers : {
			accept: "application/json",
			"Content-Type": "application/json"
		}
		
	},
	function(err, resp, body) {
		// Create a mandrill client using mandrill's SDK
		var mandrill_client = new mandrill.Mandrill('K4blHXQqhN3I6gZc0FHtpw');
		
		// The email content is driven by a template. Here we set up the
		// merge fields used in the template.
		var mergeVariables = [{
				"name": "PROJECTNAME",
				"content": null // will be filled inside the following loop
			}];
		var template_name = "Project Overrun";
		var message = {
			// In practice you might want to replace the recipient to e.g.
			// the project manager stored in time cockpit's database. For demo
			// purposes we use a static email address here.
			"to": [{ "email": "rainer@timecockpit.com" }],
			"merge": true,
			"global_merge_vars": mergeVariables
		};
		
		// Loop over all projects with budget overrun
		for(var counter = 0; counter < body.value.length; counter++) {
			// Set merge field
			mergeVariables[0].content = body.value[counter].USR_Code;

			// Print a status message
			console.log("Sending message concerning overrun in project " + body.value[counter].USR_Code);
	
			// Send email using Mandrill
			mandrill_client.messages.sendTemplate({
				"template_name": template_name, 
				"template_content": null, 
				"message": message});
		}
	});

The script uses two nodejs modules: needle for sending http requests and the mandrill SDK for sending emails through the Mandrill service. We need to make these two modules available for our script. Therefore we create a package.json file:

{
  "name": "ProjectOverrunSample",
  "version": "0.1.0",
  "dependencies": {
    "mandrill-api": "1.0.x",
	"needle": "0.7.x"
  }
}

That's it. That's all the code you need. You can test this script on your local machine. To make that easier, we created a batch file:

call npm install
call node SendEmail.js

Setting Up Mandrill

As mentioned above, we use the Mandrill service to send emails. It allows us to setup a template for emails and merge it with data from time cockpit (similar to a mail merge in Microsoft Word). The following screenshot shows the sample template that we have created for this demo (click to enlarge). Note the merge field *|PROJECTNAME|*. You can find this merge field name in the script shown above, too.

Scheduling the Script in Azure

In order to let the script run regularly, you could schedule it on a local PC or server. An alternative is to schedule it as a use WebJob in Microsoft Azure. WebJobs are very convenient. You just have to put everything you need (in our case the script, the package.json file and the batch file) in a ZIP file, upload it to Azure and specify the schedule. That's it.

Here are the steps you have to do:

Get an Azure subscription if you do not already have one.
Login to Azure's management portal.
Create a Website
Click on WebJobs and create a new job. You will have to upload the ZIP file mentioned above.
If you want, you can setup a schedule for the WebJob. For debugging purposes you can run it on demand, too (click to enlarge):