luminousmonkey.org

Generating Statements for MYOB

Wednesday 17 February 2010, 13:52 WST —

The statements that MYOB generates are inflexible, and in a word, crap. I’ve found that getting timely and informative statements out to customers goes a long way to helping keeping your customers close to their payment terms.

So, in order to automate the process, I wrote up a script that generates statements, and any warning letters I needed to go with them. Keep in mind I’m using JRuby, as it gives me access to JDBC which is far easier to get working with the MYOB ODBC driver than trying to use the Ruby ODBC driver, and it requires no patching to get it to work.

(more…)

MYOB ODBC with Ruby

Monday 10 August 2009, 16:54 WST —

I have previously written on how I was working on automating the invoice system between the sales database I wrote and MYOB.  I do have something written up, however it’s not code that I would want anyone to see, because I know it’s not high quality.

Anyway, the statements MYOB prints, are, in a word, crap. So I wanted to write a system that would take the information out of MYOB and generate my own format of statements for either mailing or e-mailing.

Now the problem with MYOB and Rails, or to be correct, the problem with MYOB and ActiveRecord is that MYOB is not a vaild SQL database.  This problem is compounded by the fact that ActiveRecord expects the database schema to be a specific way (this can be changed). ActiveRecord can be nice to work with, however there are cases where it can be a bit of a pain, and this is one of those.

I’ve had a little play with Sinatra and Sequel and this looked like a good chance to give Sequel a try. The statement generator I’m writing is just something to run, once or twice a month, so it doesn’t need a web interface.

JRuby is currently my Ruby VM of choice, because I’ve found the Java integration very useful for printing and faxing for our internal sales application, so I’ve stuck with that, which, when it comes to MYOB is a good thing.  Why?

As mentioned in my previous post, I had found information on getting MYOB and Rails/Activerecord talking to each other at Pat Allan’s site, Freelancing Gods.  The problem is the Rails ODBC driver doesn’t support MYOB (and I’m sure MYOB doesn’t have a 100% correct implementation), so you have to patch some files to get support.

However, JRuby allows you to use JDBC, so I figured that I could use the Java JDBC to connect to the MYOB ODBC driver.  It works, with no patching involved.

Ok, enough of the length explanation, how does it work?

First, get and install JRuby.

At the moment I’m using JRuby1.3.1, and I download the jruby-bin-1.3.1.zip file.  I’m going to assume you know how to get JRuby installed, and in your command path.

With that done, start up your command shell (I use Windows Powershell), and install the following gems:

• jruby-openssl
• sequel

That’s it! Next, you need to set up your MYOB ODBC datasource, again, I’m going to assume if you’re looking up this information then you know how.  There’s one thing however, if, like me, you’re using the 64bit version of Windows Vista, you need to run:

C:\Windows\SysWOW64\odbcad32.exe

Otherwise it will only list the MS SQL OSBC drivers.

With that done, we can then load up jirb and play with the database. Or you can write the following file and run it.

require 'rubygems'
require 'sequel'

#Setup the database connection
DB = Sequel.connect( 'jdbc:odbc:MYOBTest' )

customer = DB[:customers].all[0]

puts customer[:name]

DB.disconnect

Don’t forget the disconnect, otherwise JRuby will crash when you exit.

If you’re familar with Sequel, you might wonder why I do the bad thing, and go:

DB[:customers].all[0]

rather than:

DB[:customers].first

This problem is that the MYOB ODBC driver will complain about the SQL statement generated, best I can guess is that it doesn’t support the SQL option LIMIT.

Anyway, that’s it for now.

MYOB ODBC

Thursday 19 June 2008, 14:00 WST —

I have a problem at work. Basically, I have to do the accounts, this means I have to type out the sales orders from our web-based/PHP/MySQL application (that I wrote) into MYOB. This chews up my time, what would be cool was if this could happen automatically, or at least as easy as possible for me.

Enter the MYOB ODBC driver. This basically lets you automate importing and exporting into MYOB, you can get MYOB read access for your company file for about $249AUD. However, write access requires that you sign up for the MYOB Developer Program, this costs about $700AUD per year.

Once you’ve signed up, you get a developer pack sent out, which is basically most of the MYOB software, and access to the MYOB developer forums. Now I’ve been using Ruby/Rails for my webapp development recently, so I went googling for some information for getting MYOB working with Ruby/Rails and came across Pat Allan’s site, Freelancing Gods.

Many thanks to Pat for his post, as it’s pretty much the only decent information you can find on the net about getting Ruby/Rails going with MYOB. Since this sort of information is a bit thin on the ground, and I’ve got such a shocking memory, I thought I should blog some of this stuff.

First off, my situation. I work at a two-way radio company Direct Communications, we sell and repair all types of commercial two-way radios. For our sales, repairs and purchase orders, we used to use an Access database, we have since migrated to a webpage based system. At least, we have for the sales and purchase orders, the jobcard still runs under Access, and are accounts are handled in MYOB. The upshot is, like I said, that I have to manually enter in each and every purchase order, sale, and jobcard into MYOB.

Okay, so to get around this, I have to get the sales, etc, into MYOB without me typing the damn things in. For write access, the MYOB ODBC driver depends on the MYOB program, so you need your ruby/rails app running on Windows. So to start with I got the one-click Ruby installer for Windows. Once you’ve installed Ruby, you’ll need a few RubyGems.

More to come…