Christmas is closing in, and during this last month of the year we've been running a company Christmas calendar quiz. The calendar has been a success internally, but also attracted attention from customers and colleagues from other consultant firms (who wants to join in, but are not allowed..)

The purpose of this post is :

• Sharing the technical solution behind the calendar (enable you to create a similar Christmas calendar for your company)
• Showing how much fun we have in Novanet

Concept

The calendar is a geography quiz where each door is an image from somewhere in the world. The users get a score for each door based on guessing the correct location and country.

• The Christmas calendar contains a door for each weekday
• Each door contains an image from somewhere in the world
• The user gets 1 point for guessing the correct location (city/town/mountain), and 1 point for guessing the correct country
• The door is only open in a 24h-period
• The user can deliver several answers, the last submitted answer counts as the given answer

The technical solution

This is a typical MVP solution - a simple architechure for getting the solution up and running as quickly as possible. The Novanet Christmas calendar was made in a couple of evenings, so this is mostly for showing how easy the solution can be.

Therefore, authentication is omitted. The solution currently contains a drop down list of the employees, and relies on fair play from the users. (If the application had external users, authentication would be required.)

The client

The client is hosted as a GitHub page, which is even easier than setting up an Azure web app. AngularJS is chosen as js-framework because of the setup-time. Lodash is used for processing and caching.

The service

The service is a NodeJS app, running Express for API functionality. Node modules are not comitted to GitHub as Azure will install these automatically if defined in the package.json file.

Most of the application logic is placed in the service, in the queries that Seriate executes.

Seriate

The most noteable feature of the service is the use of the Seriate framework. Seriate is a brilliant framework for using Microsoft SQL Server using NodeJS, and it supports Azure. It also supports parameterized queries.

There's no party like a SQL injection party - Seriate

Configuring Seriate to work with Azure is pretty straightforward, but there is one important parameter required for setting up the Azure connection: config.options: { "encrypt": true }

The rest of the configuration look like this (the environment variables are set in the Azure web app):

var sql = require( "seriate" );
var config = {  
    "server": process.env.server,
	"port": 1433,
    "user": process.env.user,
    "password": process.env.password,
    "database": process.env.database,
    "pool": { "max": 5, "min": 1 },
	"options": { "encrypt": true }
};
sql.setDefaultConfig( config );

The database

The database consists of two tables - Images and Answers.

Table: dbo.Image - The actual image for each calendar door

ForDate : Date the image is valid
Location: Correct location
Country: Correct country
Image : Full path to blog storage

CREATE TABLE [dbo].[Image](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ForDate] [datetime] NOT NULL,
	[Location] [varchar](100) NOT NULL,
	[Country] [varchar](100) NOT NULL,
	[Image] [varchar](200) NOT NULL,
PRIMARY KEY CLUSTERED ( [Id] ASC ))

Query for getting current standing

Getting the current standing requires some logic and grouping. This could have been done in the client, but really - a good old fashion database query will gather the required data. Returning ranked and grouped data removes the need of complex client logic, and makes the API request easy to cache.

SELECT RANK() OVER (ORDER BY SUM(D.[Poeng]) DESC) AS [Rank], D.[User], SUM(D.[Poeng]) AS Points 
FROM ( 
	SELECT 	A.[User], 
			CAST(A.[When] AS DATE) AS Dato, 
			SUM(CASE WHEN LocationIsCorrect = 1 THEN 1 ELSE 0 END) + SUM(CASE WHEN CountryIsCorrect = 1 THEN 1 ELSE 0 END) as Poeng  
	FROM dbo.[Answer] A  	
	WHERE A.[When] = (SELECT MAX([When]) FROM dbo.[Answer] WHERE [User] = A.[User] AND CAST([When] AS DATE) = CAST(A.[When] AS DATE)) 
	GROUP BY A.[User], CAST(A.[When] AS DATE) 
) D 
GROUP BY D.[User] 
ORDER BY [Rank], [User]

Query for getting the final answer from each user for a given date

	DECLARE @date DATETIME
	SET @date = '2016-12-05'

	SELECT	[User], [Location], [Country], [When]
	FROM [dbo].[Answer]
	WHERE CAST([When] as DATE) = @date
	AND Id IN ( 
		SELECT MAX(Id) FROM [dbo].[Answer] WHERE CAST([When] AS DATE) = @date GROUP BY [user])

Pro tip: To avoid EXIF/metadata information for the image, use a snipping tool/screenshot to create a new image. Also, some images may require blurring.

The source code (GitHub)

• Client
• Service/API

Notice how little code is required to get the API running.

The result

Running the calendar

As stated previously, the calendar has been a success:

• The participation has been high
• The user feedback has been positive.
• The company Slack channel dedicated to the calendar has been glowing.
• The competitive instincts has been brought to life.
• Google street view has experienced heavy load (?)

Slack summary for a week in December:

Lessons learned:

• Having doors only for weekdays was a smart move (need to rest during weekends).
• Do not use images which are recognized by Google image search or TinEye.
• Slack reminders so that people don't forget to submit their answer.
• Choosing challenging images for the doors is difficult. Images needs to be challenging to guess and should contain small clues.
• Our employees are extremely clever (and MVP googlers).

Warning: Calendar image solving is contagious and may affect work. All efforts should be done after work hours.