Another email from a customer: “Greg, I can’t invoice this client, it keeps coming up blank, why?”

I grab the most recent copy of their database, go through the steps and find out now only is she right, it’s worse than she described. If I pick a random client, the invoice appears correctly AND I can even rebill them. But if I pick the client in question, not only does their current invoice come up blank, when I go to rebill them, the resulting PDF not only shows the invoices for EVERY client, but shows EVERY invoice that client has ever had (fortunately this organization only bills once a year.)

This is a custom app that a local vendor had written for them years ago but has since gone out of business. My customer approached me about 3 years ago to fix a few bugs in their app and since they’ve become a small but reliable source of income. While they call the app “the database” the reality of course is that while there’s a SQL Server database backing the app, most of my work is done actually supporting the app in VB.Net.

I generally don’t consider myself a VB.Net programmer, despite having done a fair amount of work in it for this customer, for an app for the National Cave Rescue Commission (NCRC), and for a large multinational several years ago. I generally prefer DBA work. So why do I do it?

Because it’s fun and because it involves what I call software archeology. I liken my work for this customer and the work for the multinational to what an archeologist does when they find a bunch of potshards: they try to reassemble them and figure out what they were intended to do.

For this customer, often I’m actually not fixing code, I’m drilling into to the code and the database to determine “what did the original developer intend?” “What business assumptions were made in the original design specs?” This means sometimes the customer will email me and say something like, “Greg, when I try to add a member to this group, why does it not work?” And I dig through the code and realize it was never intended that you could add a specific individual to a group. What you do is say that client has the following positions on that that group and then within the client “this person fills this position.” In other words, the original business case as that as a client updated its own individuals, the memberships in groups would reflect that. It’s a fine way of approaching the problem and honestly, works well. Except the current main user of the program was approaching the issue from the opposite direction: a new client had signed up and wanted to have people in specific groups so she went to the groups and tried to add specific people.

So, there was nothing wrong with the code, nor was there anything wrong with the design, just a different approach. But it took me several hours of digging through the undocumented code to determine why she couldn’t do what she wanted and how to go about doing it.

So what’s the deal with the most recent case? Well, it’s not a bug per se, though I’ll probably fix some code to prevent the problem. The issue turns out that their clients are charged based on how many groups or committees they’re members of and if they’re domestic or international clients (and in some cases can be both). There’s code to calculate a discount if they’re an international client and a domestic client and how many committees they’re on. However, the code assumes that the discount only applies up to so many committee memberships. It’s not hardcoded, but more a result of some math that in this specific case instead of returning a discount (even a $0 one) was failing to return any discount because half-way through the SQL calculations it was returning a NULL and of course $discount = $numcomm1-$numcomm2 where one of those is NULL will result in $discount being NULL.

So, technically the code should handle this better, but it was obvious once the pot shards were put together that the original designers and design specs never envisioned this particular combination of memberships (and in fact I think in this case it’s a mistake since in previous years the client was only a domestic client, not both).

It was a fun little mystery and I think I’ve solved the current issue for my customer, but eventually we’ll need to think about how to approach this issue in case it happens again in the future.

Personally, I tend to enjoy these little mysteries of trying to figure out what the code is doing, but more importantly why. It can be insightful.

And now back to some PowerShell code for my largest client that actually involves some real database work.