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....

Comments

good
The hilarious moments are very few because life is the name of seriousness. The comical or funny people try their best to make the moments so bright. This work is not easy because you have to utilize your all senses.
#0 - assignment services ( Link) on 2017-11-10 15:19:09 Delete Comment
Great Information
If Students are find some wearing service and get information’s.This is main concept of all the writing services. this also one of the most famous online service .It is given a too many tips for students. So you will find this writing service get a lot of information’s
#1 - i need an assignment ( Link) on 2018-02-15 20:20:22 Delete Comment
Benefits of Protein
Hi! This is my first visit to your blog! We are a team of volunteers and new initiatives in the same niche. Blog gave us useful information to work. You have done an amazing job!
#2 - Benefits of Protein ( Link) on 2018-03-27 04:32:41 Delete Comment
Magnesium
Hi there, I found your blog via Google while searching for such kinda informative post and your post looks very interesting for me
#3 - Magnesium ( Link) on 2018-03-27 04:42:59 Delete Comment
Celery
This was incredibly an exquisite implementation of your ideas
#4 - Celery ( Link) on 2018-03-27 07:02:16 Delete Comment
mechanics of materials 10th edition solutions
I have been searching to find a comfort or effective procedure to complete this process and I think this is the most suitable way to do it effectively.
Peach Juice
I think this is an informative post and it is very useful and knowledgeable. therefore, I would like to thank you for the efforts you have made in writing this article.
#6 - Peach Juice ( Link) on 2018-03-29 18:42:46 Delete Comment
Benefits of Kale
It's late finding this act. At least, it's a thing to be familiar with that there are such events exist. I agree with your Blog and I will be back to inspect it more in the future so please keep up your act.
#7 - Benefits of Kale ( Link) on 2018-03-29 21:00:00 Delete Comment
Molasses
It is the intent to provide valuable information and best practices, including an understanding of the regulatory process.
#8 - Molasses ( Link) on 2018-03-31 00:01:55 Delete Comment
Onion
I have recently started a blog, the info you provide on this site has helped me greatly. Thanks for all of your time & work
#9 - Onion ( Link) on 2018-03-31 04:43:53 Delete Comment
Tomatoes
When your website or blog goes live for the first time, it is exciting. That is until you realize no one but you and your.
Natural Food
You have done a amazing job with you website
#11 - Natural Food ( Link) on 2018-04-02 03:44:18 Delete Comment
Essential Oils
I really loved reading your blog. It was very well authored and easy to understand. Unlike other blogs I have read which are really not that good.Thanks alot!
#12 - Essential Oils ( Link) on 2018-04-06 13:31:11 Delete Comment
Herbs
You have done a amazing job with you website
#13 - Herbs ( Link) on 2018-04-07 20:15:29 Delete Comment
Relationship Guide
Very good points you wrote here..Great stuff...I think you've made some truly interesting points.Keep up the good work.
#14 - Relationship Guide ( Link) on 2018-04-10 03:37:50 Delete Comment
His Secret Obsession
Great post I would like to thank you for the efforts you have made in writing this interesting and knowledgeable article.
#15 - His Secret Obsession ( Link) on 2018-04-14 02:48:08 Delete Comment
Sushi
so happy to find good place to many here in the post, the writing is just great, thanks for the post.
#16 - Sushi ( Link) on 2018-04-28 16:08:17 Delete Comment
Cheese
Regular visits listed here are the easiest method to appreciate your energy, which is why why I am going to the website everyday, searching for new, interesting info. Many, thank you!
#17 - Cheese ( Link) on 2018-05-06 21:26:13 Delete Comment
Magnesium
I am always searching online for articles that can help me. There is obviously a lot to know about this. I think you made some good points in Features also. Keep working, great job
#18 - Magnesium ( Link) on 2018-05-09 02:06:41 Delete Comment
Turmeric
A great website with interesting and unique material what else would you need.
#19 - Turmeric ( Link) on 2018-05-09 19:25:58 Delete Comment
APK Logic
Visit APK Logic and enjoy many cool games.
#20 - Juan Hodge ( Link) on 2018-05-22 21:13:36 Delete Comment

Add Your Comment

Follow us on