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.
- For Hong kong, who's accounting period ends in April, we go the income and expenses from May to November.
- 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
- 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..
- 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.
- 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....
