Interfacing with Mandala

NB: This is an advanced section intended for software development teams. Most people won’t need to know any of this so don’t be worried if you don’t understand what I’m talking about here.

Sometimes you will want to communicate with the Mandala database, without actually invoking the Mandala application and its GUI. As an example, you might want your property management system to “speak” with Mandala so that you can include spa sales data in your main property management reports. Or perhaps you are a web developer and you would like to implement online gift voucher ordering, or an online booking interface.

Well the good news is that Mandala does provide a web-service based interface, which you can use to access most elements of the Mandala database as-required, and in real time.

In providing this interface Mandala strives to adhere to a RESTful protocol. Hopefully you will know what I mean by that – if not you might need to do a quick bit of Googling! Don’t worry though – it’s not that complicated at all.

What you need

To  allow Mandala to communicate, you will need to install and activate a special Mandala CGI script on a web-server enabled machine somewhere on the same network as your Mandala PostgreSQL database. This takes about 15 minutes so it isn’t too much of a drama. If you are an egalitarian coder you might like to use Apache via a XAMPP package, or if you are more tightly trussed up in the corporate world you will probably end up using Microsoft’s IIS services. Assuming the latter, here are some handy links to help you on your way:

Activating IIS on Microsoft Windows using the Microsoft Web Platform Installer

Setting up CGI support for Windows IIS

Obviously setting this up would be pointless without the magic CGI file. Contact me for this – it’s not part of the standard Mandala distribution package.

The API

Once you have the CGI script installed, testing your communication interface with the Mandala database is now as easy as throwing the following line at your web browser:

http://localhost/cgi/mandala.cgi/?type=booking&limit=10

The first part is just the path to the Mandala CGI script (http:\\localhost\cgi\mandala.cgi\), whilst the URL arguments are used to formulate your http GET query. For the curious, the above query will return in XML format the first 10 bookings for the current date. Depending of course on the state of your database, you will see something like this returned:

In the above case, there was only one appointment (a Remedial Massage) booked for the current date.

Right that’s the basics – time to have a look at the API itself.

GET parameters

Parameter: type

Format: string

Parameter: id

Type: integer

To reference a specific value in any of the types listed above, specific a non-zero id number. Take note that I typically use id=1 as an ‘undefined’ object placeholder. So the data only gets interesting with id > 1 in most cases.

If id = 0, then the date range parameters swing into action.

Parameter: start_date

Type: numeric string (as in a string of numbers!)

You have 3 options when it comes to date format. You may use ddmmyyyy (e.g. 31 Jan 2010 would be 31012010) OR you can use a Julian date OR you can use an offset from the current date.

Unfortunately, there are a number of Julian Day systems in use. I use the one that counts days as integers since Monday 24 November -4713 (Gregorian calendar).

For reference, 17 Feb 2010 = 2455245 as a Julian Day Number. Check here for the algorithm.

When using offsets, a start_date of 0 returns today; a start_date of -10 returns 10 days ago and so on.

Be aware though, that the server date and your date may be different if you and the server live in different time zones. Also, you can’t  offset more than 25 years in the future.

Parameter: end_date

Type: integer

end_date is the opposite book end to start date. Set end_date = start_date to view a particular day. So using offsets, start_date = -1 & end_date = -1 would return yesterday. start_date = -1 & end_date = 1 returns yesterday, today and tomorrow. Default is 0 (today).

Of course end_date also works with the ddmmyyyy format as well as Julian Day Numbers.

Parameter: limit

Type: integer

Limit quantifies the maximum number of records the query will retrieve. The default limit is 50. Limit is ignored in single row queries (as when id > 1).

Parameter: offset

Type: integer

Offset specifies the number of rows to skip before delivering up the query results. The default offset is 0. This parameter is usually used in conjunction with limit to reduce network load for queries that return a large result set. For example, suppose you queried for all the sale items sold in the last 10 years but only wanted to receive 10 results at a time. You do this like this:

http://localhost/cgi/mandala.cgi/?type=sale&start_date=-3650&limit=10&offset=0

This would retrieve the first 10 results. To view the next 10 you would use:

http://localhost/cgi/mandala.cgi/?type=sale&start_date=-3650&limit=10&offset=10

Parameter: format

Type: string

Format defines the method used to deliver the query return result. The default is xml, and the alternative is json.

For example:

http://localhost/cgi/mandala.cgi/?type=sale&limit=10&format=json

would return the first 10 sales made today in json format.

Parameter: links

Type: integer

Links is a helper boolean that, if non 0, provides you with the URI required to view an object referred to in a query return result.

Default is 0 (no links).

For example, if you query for today’s bookings (type=booking) you will be returned a guest_id for each booking. You can use this id to submit another query for the actual data belonging to the guest (name, address etc). But if links is non zero, you will also receive in addition to the id a line looking something like this:

<guest link>http://localhost/cgi/mandala.cgi/?type=guest&id=24&start_date=0&end_date=0&format=xml&offset=0&limit=20&links=1</guest link>

allowing you to directly “drill down” into the data set without having to think about how the correct URI should be formulated.

PUT and POST parameters

Haven’t been written yet actually! But they are easy enough to implement – it is just a matter of letting me know exactly what data you need to write to the database and I’ll expose the interface you need.