Published 2012-11-02 00:00:00

If there is one reason I would put down to why I still enjoy coding after all these years, it is the pleasure finding a really challenging problem and being able to solve it quickly and efficiently.

This week saw one of those problems, strange, obtuse, and to most non crazy people would seem to be an odd thing to get excited about. It all started with going through the requirements list on one of our projects.

As I've mentioned before, we are building quite heavily ontop of Xtuple, a Web based GUI using the RooJS Toolkit, most of that part has been just process orientated - add this feature, and it just works. But some of the implementation process has been a challenge.

The company we are working with has 2 main offices (and more comming on line soon), and we have successfully migrated them off of netsuite, which in reality made an absolute mess of their accounts. When migrating to Xtuple (which in essence is a postgresql database, with a seemingly endless number of table triggers, stored procedures and views), we concluded quite early on that setting up a seperate instance for each office would be the best way to go.

Core issues like difference currencies in each office, so the chart of accounts can be reported each year in the local currency to the tax department and auditors, along with the different end-of-year tax reporting made this a simple decission. The otehr issues was that the basic installation of Xtuple does not really support Multiple companies in the Desktop UI (you have to pay for that feature). 

So as we are now live, and just about finished with the rollout issues, we are now on to the more critical things for long term operations - Management reports (and a nice dashboard). 

The issue that raised it's head was that the company needs to see consolidated Balance sheet and Income statements. It did not take to much time to call the internal methods in Xtuple 'financialreport()' and in PHP convert this to JSON so that a master script can call the report in both systems, and merge them together. 

The snag came later when we realized that the resulting report was a little off.. Due to the magic of differeing accounting periods.


How the accounts are stored.

In xtuple there are a number of database tables that deal with transactions 'gltrans' is the main one that stores each individual transaction, and 'trialbal' stores a running total for each account summarized at for each month.

The fun thing here is that at the end of each financial year, both the Expenses and Revenues accounts are zero'ed out, (when you close the year, that balance get's moved to retained earnings). The snag was that if we asked for the income statement in November. We got two very different numbers from each office.

  1. For Hong kong, who's accounting period ends in April, we go the income and expenses from May to November.
  2. For Singapore, who's accounting period ends in September, we got the income and expenses from October to November.

So it looked like Hong Kong was doing a roaring trade, and Signapore was barely breaking even.

But how to solve it.

When I first got this problem it was quite late afternoon, so I guess my brain cells where not fully working. My first ideas where either to 

  1. modify the reporting engine stored procedures - which while could be feasible, however it was not the tidiest piece of code out there, each of the main methods used to generate the reports is around 1500 lines of relatively dense uncommented code. A good guess would be a week at least even at my pace..

  2. rewrite the reporting engine in PHP, again not a small task, we could have cut some corners and not supported all the features, but again at least a weeks work.

  3. run the report a few times using different dates and try and merge the results together. (eg. subtract one result from the other etc.) - I did try mocking a bit of code up for this, but even this looked like it would end up having to hard code workarounds for account totals that where not referenced anywhere.

So at the end of the day I gave up looking at this and headed off to another meeting..

Ding goes the light bulb.

It's one of the problems that niggles at you though, there must be a simpler way to solve this.. And the next morning as I was half awake, and getting annoyed at the though of spending a few days work doing  one of the relatively  mindless tasks above, It dawned on me....

Postgesql Schemas and search_path......

The core crux of the problem was that the trailbal table was zero'ing out the balances, and that the period and yearperiod tables differed in the secondary office.  Why not create a copy of that original table in a different schema, and run the report on that copy.

That was the idea that save the day, and turned a weeks worth of work into a few hours.. In reality, I did not create a copy of the table, I used postgresql views, to make a copy of the trailbal, period and yearperiod tables in a schema called 'timewarp' (I was going to call it 'alternativeuniverse' but timewarp was shorter....

Then once the views have been created all I do on the code that creates the report is do

SET search_path TO timewarp,public
SELECT  * from financialreport(.....) ;

And by magic all the reports look like the accounting year has changed.

After all these years using postgresql on and off, when I first tried it, It drove me mad, compared to mysql, setting up access and permissions to get started can be a touch painfull, edit this file here... etc... But as I've done so much work with Xtuple and postgresql, I've become extremely impressed with the power that it provides.

Not only that, for really really really complicated queries and stored procedures it just blows mysql out of the water most of the time..

Anyway back to more mundane issues, like sending out invoices....

Add Your Comment

Follow us on