Monthly Archives: June 2019

Innotech iComm – it actually has an API

I’ve mentioned before that we run a rather venerable HVAC controller at the church which drives our heating system.

Last weekend, I was bored enough to start digging in to the network protocol it speaks, but before I spent hours slaving over Wireshark, I took a last look around to make sure I hadn’t missed an “official” programmable interface.

Somewhat to my surprise, I had!

iCommX help screen

1999’s idea of the future

Leaving aside a bit of sniggering about how ActiveX hasn’t been “The Way of the Future” since I was half my current age, let’s try firing it up from a sensible programming language (Python)…

import win32com.client

def get_px(block_name, field_name):
    """Constructs the COM object for reading and writing a block
    Block names can be viewed in MAXMon; most of the field names are also visible there if you open (double click) the block"""
    px = win32com.client.Dispatch("icommx.pointx")
    px.ServerAddress = "localhost"
    px.DeviceAddress = "6:1" # connection number, device number
    px.PointAddress = "%s~%s" % (block_name, field_name)
    return px

def get_temps():
    for tzone in ["Outside Air", "Boiler Flow", "Utility Space", "Church Space", "Hall Space", "HWS Cylinder"]:
        px = get_px(tzone, "OUTPUT")
        print(tzone, px.Value)

get_temps()

There are various hoops to jump through to generate the necessary Python COM bindings, which you can Google for. Needless to say, all this COM stuff requires us to be on Windows. Saving this as icp.py, let’s give it a try:

C:\Users\dtn>python icomm-python\icp.py
Outside Air 27.03
Boiler Flow 24.23
Utility Space 20.67
Church Space 21.32
Hall Space 23.91
HWS Cylinder 54.75

Result! (Hot day in Oxford … the boiler is not running, yet the hot water is toasty thanks to our solar panels.) You can also, somewhat scarily, assign to the “Value” field for blocks which are inputs such as override buttons or temperature set points.

The API is a bit limited because there’s no way to enumerate the available blocks – you just have to know (using one of the companion apps like MaxMon) what they are. More disappointingly, I couldn’t find a way to write the schedule on the seven-day timer blocks. However, a bit of thought made me realise this doesn’t matter too much: I can still write to a boolean flag connected to the “override” pin on those blocks, and write an override value into each one – number of minutes until heating next goes on/off.

Long story short, we can finally wire our church heating directly into the bookings calendar and stop having to program it by hand! Of course, we will need a permanently stationed Windows box, but we’re going to need one anyway for some other projects. Exciting times…

Oxford Botley Road consultation response

This is my response to the Oxford City Council consultation on improving Botley Road for cyclists and busses…

Dear Sir/Madam,

I live in Abingdon and commute to work on Osney Mead, just off the Botley Road. As often as possible, I cycle into the city from the south and avoid the Botley Road altogether, but there are also occasions when I am a motorist on it. Additionally, I use it to cycle from the industrial estate into the city centre after 5pm on some evenings.

I note the following:

  • As a cyclist, I would much prefer on-carriageway cycle facilities along the entire length of Botley Road; ideally with physical barriers separating them from the traffic, like those used on Donnington Bridge. Off-carriageway facilities which share the same level as the pavement are rendered useless as pedestrians are routinely unaware of which half of the space they should confine themselves to. The proposed switch to cyclist priority across junctions is welcome, but in my view this is much better achieved by making cyclists first-class users of the road itself.
  • The raised walkways across side roads should be carefully reviewed from a drainage perspective, given Botley Road’s history of severe flooding.
  • The reduction of traffic exiting Ferry Hinksey Road to a single lane is undesirable as both a cyclist and a motorist. A majority of traffic turns left from Ferry Hinksey Road, particularly at the end of the working day, and the additional lane allows cyclists to safely overtake that traffic and turn right towards the city centre. Adding a new pedestrian refuge here seems excessive as the timing of the lights presently allows plenty of time for pedestrians to cross, and in any case there is a controlled crossing a short distance into Ferry Hinksey Road. Reducing traffic exiting the industrial estate to one lane will be more dangerous for cyclists as it will create increased congestion and force them to wait longer and breathe more fumes.

Although it is perhaps wider than the scope of this consultation, I note that the key stated goal of encouraging less car use on Botley Road is unlikely to be achieved without more drastic measures – for example, a congestion charge for non-resident travel inside the Oxford ring road. Alternatively, speaking in my motorist persona, I would be much more likely to use the Park and Ride (so far, I never have in ten years of working on Osney Mead) if it either (a) did not charge for both the parking and riding aspects of the journey or (b) had busses which served the industrial estates off Botley Road rather than requiring a ten minute walk through the rain in addition to the bus journey.

Yours faithfully,

David North

Replacing HallMaster

So, as explained in my last post, we needed a new invoicing system for the church hall. The back-of-a-beer-mat requirements were pretty simple…

  • One-off bookings can be invoiced by a manual process, but the regulars must be sent an invoice by e-mail automatically at the end of each month.
  • One invoice should contain all bookings for a customer in the period covered.
  • Must be possible to see an aggregated summary of which customers owe us how much money
  • Must be easy to track repeating series of bookings and make ad-hoc tweaks

There did not seem to be anything out there to buy (at least, not for sensible money), so I sat down over a couple of weekends and wrote it.

Naturally, I used other systems as much as possible. Back in the day, we used Google Calendar to track the actual bookings, which is a well known interface and worked nicely. Unlike HallMaster, it doesn’t automatically prevent clashing bookings – but we only have one human entering bookings, and this hasn’t been a problem in six months of operation. It also doesn’t guarantee that every calendar event is associated with a known customer – but since most of our invoices go out monthly, we solved that with a weekly nagging e-mail to the staff in question about any calendar events which can’t be matched to a customer or room.

So, with a Google Calendar full of bookings in place, I turned to Django for the next bit. Thanks to the automatic form generation (and a bit of customisation of the automatic admin interface), writing a web app to keep track of customers and their billing contact details was super-quick. Each customer also has a list of “event names” which are the titles under which their bookings appears in Google Calendar. This also allows the use of e.g. “Private Event 42” for customers who don’t want their name visible in the public calendar.

And now to the beating heart of the system: automatic invoicing.

This has to be customer-centric, so the first thing I implemented was a method on the Customer model which could generate an invoice for that customer’s bookings in a given time period. Looking things up in Google Calendar is super-easy: they have a nice, well-documented API including example code which can be copied and pasted for a fast start. Their API also has the crucial feature of forcing the “expansion” of repeating events, i.e. your code doesn’t have to understand repeats; it just gets an event for every repeat.

From there, looking up each room’s hourly cost according to whatever tariff the customer is on is an easy modelling and maths problem. Thankfully we’re not required to charge VAT owing to our size and charitable status, so that complication doesn’t have to be factored in.

The most complicated part turned out to be generating invoices in an acceptable format. My first thought was plain text e-mails, but getting a big table of bookings laid out nicely in one is a challenge, and probably impossible given that many e-mail clients don’t use a monospace font. What’s more, it would look rubbish on a mobile phone, which is where many e-mails get read these days.

Generating PDFs from Python doesn’t seem to be a super-easy solved problem, so in the end, I went for generating invoices as HTML (using a normal Django template) and then turning this in to PDF using wkhtmltopdf (which has some decent Python bindings). This does require the right fonts to be installed on the target system (ttf-mscorefonts-installer on Debian) to avoid invoices coming out in a blocky default font, but other than that blip it seems to be 100% reliable, and doesn’t require an X server.

We send these out attached to a plain text e-mail, which just says how much you owe and refers you to the attachment for details.

Naturally, the invoicing logic is covered by a wide set of automated tests. We do have the odd hirer whose bookings cross multiple days (i.e. span midnight), so I tested that in particular and also the possibility of bookings which span the clocks going forward or backward. Django makes this fairly easy to cope with: store all the times in UTC, which is also what the Google Calendar will give them to you in. Then all you need to do is format them for display to the user in local time (which covers BST when needed).

Automatically issued invoices (recipients redacted)

With that logic in place, it was just a quick custom management command to loop over all customers and invoice them if they are a regular and it’s the first of the month. Set that on a cron job for early morning on the first of the month, sit back, and relax.

Lessons learned

Make it easy to look under the hood. The Django admin interface helps with this, as does the facility I built in to CC all outgoing e-mails to extra recipients. I use this to stick them all in folders under my own inbox so I can refer back to them.

Ensure a failure to invoice one customer doesn’t stop all the others being invoiced. Easy enough with a try/catch, and the customer-centric design makes it easy to issue that one customer’s invoice later with a manual invocation from “manage.py shell” once the problem is fixed. This works well and we’ve had 100% successful invoicing runs for the last five months (out of six since going live).

Make it easy for people to get the payment reference right when paying you via BACS. Our system assigns a customer number to each hirer, and tells them to put just that as their payment reference. There’s no need for an invoice number or dates, and the reference can be the same for all payments, which helps with some online banking systems that make it painful to set a fresh reference for each payment to the same recipient.

  • This works pretty well, and nearly all customers manage to do the right thing. Some insist on quoting an invoice number (which is managable, we can just de-reference that back to the customer), and the odd one or two continue to make stuff up. I have an “upload bank statement” view which allows me to manually assign incoming payments where we haven’t detected the customer automatically. At the moment this is rare enough that I’m letting it slide, but if we ever get an automatic feed in of this information (Monzo and Starling, where are those charity accounts?), we will start cracking down and insisting on the right reference.

Don’t start your invoice numbers with the letter I, people will mis-read it as the number 1. We solved that by switching to “V” instead.

The end result is less than 2,000 lines of code which I’m continuing to evolve and tweak, but overall, this has solved it. We can see exactly who owes us money, split between the one-ofs and the regulars, and being reliably sent a bill on time is helping the vast majority of them to pay up promptly with the right reference. We’re approaching the mid-point of the year and well on track to get back to previously attained levels of hiring income.

The authors of HallMaster might well argue that I haven’t replaced their entire product with two weekends and not much code – and they’d be right, my system is designed to do exactly what we need and not service multiple customers – but I can’t help but feel it’s high time they offered fully automatic invoicing to really save their users some time. It’s not difficult and it makes things feel so much more professional.

Invoicing for the church hall

I did a long stint as treasurer of St Columba’s URC (finally stepping down last year). One of our challenges, which I suspect we have in common with a lot of other churches and community groups, was billing for letting out our premises.

Being as we are, bang in the middle of Oxford, and with a variety of different rooms available, we do our best to give something back to the community by renting them out to other charities and not for profits for a reasonable rate … and of course to profit-making enterprises for a commercially appropriate rate.

In the beginning (pre-2009), all of the invoicing and tracking of who owed what was done by hand, with spreadsheets being e-mailed around. This was very labour intensive, and for a number of years after that, we scaled up our lettings by moving to a largely honour-based system which tracked bookings in a Google Calendar and required people to proactively pay us on time via bank transfer.

This sort-of worked for quite a while, but of course many organisations, especially voluntary ones, do not cope well at paying people money if they haven’t been sent an invoice. We had a few embarrassing situations where we invoiced several years of historical debt and caused problems for the people on the other end of it. The bills were perfectly legit, but they had of course spent the money on other things in the interim.

A few years back, things really went downhill for us, and we lost quite a few customers, partly because of our sketchy billing process. Something had to change.

Naturally, I assumed this would be a solved problem – the business of letting a few rooms out with a calendar and invoicing according to a couple of different tariffs is pretty standard and universal. And on first inspection, HallMaster looked to be just the ticket:

  • Aimed at the voluntary sector, like us
  • Reasonably priced
  • Includes invoicing facilities

We ran with it for about eighteen months, but in the end it didn’t work out as hoped, for a few reasons:

  1. Invoicing is not automatic. This was one of the biggest disappointments to me. Most of our customers are “regulars” who book time each month and should get a bill at the end of the month with 30 day terms, covering all their usage that month. Unfortunately, HallMaster only supports issuing invoices as a manual process.
  2. Invoicing is booking-centric, not customer-centric. In HallMaster, you can only issue an invoice for a given booking, not a given customer. One booking can be a repeating series of events, which covers a lot of cases, but unfortunately some of our biggest customers have a couple of repeating series plus lots of smaller ad-hoc bookings. Getting all these onto one “booking” in HallMaster is tedious and error-prone, but if you don’t do it that way, you have to invoice them all separately.
  3. Who owes what is invoice, not customer-centric. Partly because of the above, the view which shows you who owes money lists invoices, not customers. This also means there is no facility (automated or manual) to send reminders to people who haven’t paid.
  4. Interface feels clunky. Somewhat subjective, but the manner in which the Javascript-heavy interface works is a bit iffy. Sometimes it would fail in certain browsers (e.g. Chrome), and sometimes long (tens of seconds) pauses with no progress spinner would occur. Sometimes it would get stuck if you asked for lots of results on one page.
  5. Took them a while to implement SSL. I complained when we first evaluated it that there was no https support (i.e. login details and personal data were being sent unencrypted over the internet to and from their site), and they didn’t take it as seriously as I felt they should (GDPR et al being a concern). They did implement it eventually, but it should have been there from the start.
  6. Confused the customers. It was difficult to remove HallMaster links from the e-mails it sent out, and some customers got confused by them. Customers were supposed to be able to sign up for free to view their invoice and payment history, but sometimes ended up at pages trying to sell HallMaster to venues, which confused them by talking about costs.
  7. Questionable e-mail behaviour. Their system attempted to send invoice e-mails “from” my e-mail address, e.g. invoices@saintcolumbas.org. Unfortunately, while this may have worked just fine in the 90s, these days the advent of SPF and DKIM mean that many spam filtering systems take a dim view of e-mails claiming a from address which the originating system is not authorized to send mail from. To be fair, they did “fix” this for me by setting all e-mails from our account to come from noreply@hallmaster.co.uk, but the ability to set a proper reply-to address would have been much nicer.
  8. No bank integration. OK, so given that Open Banking had barely been invented when we first tried this, a direct feed would have been asking rather a lot. But a simple option to upload a CSV bank statement and assign incoming payments to customers could have been a time saver over entering every payment by hand.

To be fair to HallMaster, I suspect it would work well for smaller venues or those with simpler usage patterns (and those still accepting cheques), but it didn’t hit the spot for us. In particular, it required too much staff/volunteer time to operate.

So what to do? See my next post for what we did.