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.

It starts off simply enough with the following file:

# This little program is used to generate statements to customers.

require 'rubygems'
require 'sequel'

# Setup our database connection
#Sequel.single_threaded = true
DB = Sequel.connect( 'jdbc:odbc:DirectComms_MYOB' )

# Load in our models
require 'models/customer'
require 'models/job'
require 'models/terms'

Customer.with_open_invoices.each do |customer|
  # For each customer, we get a list of their open invoices and process
  # that list to create a statement and warning letters.

  # There are 3 types of invoices:
  # => Open - These show up on the normal statement
  # => Overdue - These show up on the first warning letter
  # => Really Overdue - These show up on the last letter with interest
  # We only have to find the overdue and really overdue invoices because
  # we already have all the open invoices.
  overdue_invoices = []
  really_overdue_invoices = []
  customer_open_invoices = customer.open_invoices
  customer_open_invoices.each do |invoice|
    if invoice.overdue?
      # Overdue invoice, also includes invoices that are really overdue
      if invoice.really_overdue?
        # This invoice is really overdue, so treated differently
        really_overdue_invoices << invoice
      else
        # This invoice is just overdue
        overdue_invoices << invoice
      end
    end
  end # End Customer.open_invoices

  require 'directcom/statement'
  Statement.create( customer_open_invoices,
                    overdue_invoices,
                    really_overdue_invoices )

end

DB.disconnect

The code uses Sequel, which makes working with the horrible fake SQL interface that MYOB provides a little nicer. As usual, I split all the logic up into other models.

Here is my ‘models/customer.rb’ file:

# Customers, this includes both companies and individuals.
require 'models/sale'
class Customer < Sequel::Model
  set_primary_key :CustomerID

  # Return all the customers that currently owe us money
  def self.with_open_invoices
    self.order( :name ).filter( :currentbalance > 0 ).all
  end

  # Return the database with this customers sales
  def invoices
    Sale.order( :invoicenumber ).filter( :cardrecordid => self[:customerid] ).all
  end

  def open_invoices
    Sale.order( :invoicenumber ).filter( :cardrecordid => self[:customerid] ).and( :outstandingbalance > 0 ).all
  end

  # Get the name, MYOB has 3 name fields, this function will
  # return a string of the name as you would expect it on a
  # letter
  def name
    if self[:isindividual] == 'Y'
      # Individual customers have a first and last name
      return "#{self[:firstname]} #{self[:lastname]}"
    else
      # Company
      return self[:lastname]
    end
  end

  # Current A/R balance
  def current_balance
    self[:currentbalance]
  end

  # Terms of payment in words
  def terms_in_words
    Terms.filter( :termsid => self[:termsid] ).all[0][:termsofpaymentid]
  end
end

My sale.rb model:

class Sale < Sequel::Model
  set_primary_key :SaleID

  # This will return invoices that are open
  def self.find_open
    self.filter( :invoicestatusis => 'O' ).all
  end

  # Check the job class for a description
  def customer
    Customer.filter( :customerid => self[:cardrecordid] ).all[0]
  end

  # An invoice is overdue if the current date is greater than the due date
  def overdue?
    if Date.today > self.due_date
      return true
    end
    return false
  end

  # An invoice is really overdue when it's 15 days past the due date
  def really_overdue?
    if ( Date.today - self.due_date ) > 14
      return true
    end
    return false
  end

  def invoice_number
    self[:invoicenumber]
  end

  def purchase_order_number
    self[:customerponumber]
  end

  def created_at
    self[:invoicedate]
  end

  def due_date
    self[:invoicedate] + Terms.filter( :termsid => self[:termsid] ).all[0].balance_due_days
  end

  def outstanding_balance
    self[:outstandingbalance]
  end

  def interest_charge
    return ( Date.today() + 1 - self.due_date ) *
        ( 0.0006575 * self.outstanding_balance )
  end
end

Job.rb is very simple:

class Job < Sequel::Model
  set_primary_key :JobID

  # We cannot use the built in Sequel associations, it seems MYOB is
  # really picky about it's SQL, or something.

  # Query the customer of the job
  # MYOB doesn't support the LIMIT SQL command.
  def customer
    Customer.filter( :customerid => self[:customerid] ).all[0]
  end
end

Finally the terms.rb file:

class Terms < Sequel::Model
  set_primary_key :TermsID

  # Number of days for when the invoice becomes due.
  def balance_due_days
    self[:balanceduedays]
  end
end

The actual statement generation and logic is done in the ‘directcom/statement’ class I wrote up, however that just details how I like statements set up, and with the data provided in the 3 arrays you should be able to write your own code.