Archive for the ‘Best Practices’ Category

Using Rails for Batch processes (example: Moneybird export via API and command-line)

Wednesday, March 2nd, 2011

My accountant needs to have my financial data in a somewhat slightly different format than the export my online invoicing application provides. Luckily Moneybird has an api so I set out to write my alternative export.  I googled around for some best practices, and this is the workflow I came up with:

Create a new Rails app

> rails new moneybirdexport
> cd moneybirdexport

Generate models for api access and conversion logic

Our goal is a list of incoming and outgoing invoices – so we generate models with the required fields.

> rails generate model dumped_invoice klant:string, factuurdatum:date, periode:string, factuurnummer:string, bedrag_excl_btw:decimal, btw_percentage:decimal, bedrag_incl_btw:decimal, omschrijving:text, soort:string, betaald:string, datum:date, betaalwijze:string
> rails generate model dumped_incoming_invoice factuurdatum:date, periode:string, factuurnummer:string, bedrag_excl_btw:decimal, btw_bedrag:decimal, bedrag_incl_btw:decimal, omschrijving:text, soort:string, betaald:string, datum:date, betaalwijze:string
> rake db:migrate

The Moneybird API exposes invoice, incoming_invoice and contact. No need to use a generator here, create the app/models/ files contact.rb, incoming_invoice.rb, and invoice.rb, to create the ActiveResources Contact, IncomingInvoice and Invoice – the source is identical apart from the classnames:

class Invoice < ActiveResource::Base
  self.site         = "https://" + Moneybirdexport::MBX_ACCOUNT_NAME + ".moneybird.com"
  self.user         = Moneybirdexport::MBX_USER_NAME
  self.password     = Moneybirdexport::MBX_PASSWORD
  self.timeout = 5
end

Not sure what the best practice is here, but I put per-app configurable values in config/application.rb, so if you use the constant values in the same way I did in the previous lines, you need to define them there as well:

module Moneybirdexport
  class Application < Rails::Application
    # Settings in config/environments/* take precedence over those specified here.
    # ...
  end

  MBX_ACCOUNT_NAME  = "username"
  MBX_USER_NAME     = "someemailaddress@domain.com"
  MBX_PASSWORD      = "secret"
end

You should be able to test the api already on the command-line (YAML::dump gives a well-formatted output of objects):

> rails console
irb(main):> puts YAML::dump Invoice.first
irb(main):> puts YAML::dump IncomingInvoice.first
irb(main):> puts YAML::dump Contact.first

Write the conversion logic

Some fields I need for my export (dumped_invoices and dumped_incoming_invoices) are not attributes of Invoices and IncomingInvoices as Moneybird sees them.  So in the models DumpedInvoice and DumpedIncomingInvoice there’s a method set_attributes_from_invoice that digs out the info from somewhat deeper in the Moneybird model (looks up the related contact name, or fabricates a description from the invoice line items’ description…).  Exactly how this is done is not important, I’m just copy-pasting my code here if you need to do something similar with the Moneybird API:

class DumpedInvoice < ActiveRecord::Base

  # need to include ActionView::helper because I am using the truncate method
  # source: http://stackoverflow.com/questions/489641/using-helpers-in-model-how-do-i-include-helper-dependencies
  include ActionView::Helpers

  def set_attributes_from_invoice(i) # i should be an Invoice ActiveResource
    self.klant            = i.company_name
    self.factuurdatum     = i.invoice_date
    self.periode          = ((i.invoice_date.month + 2)/3).to_s + "e kw"
    self.factuurnummer    = i.invoice_id
    self.bedrag_excl_btw  = i.total_price_excl_tax
    self.bedrag_incl_btw  = i.total_price_incl_tax 

    self.btw_percentage   = 0  if ( self.bedrag_excl_btw == 0)
    self.btw_percentage   = ((self.bedrag_incl_btw - self.bedrag_excl_btw + 0.001)/ self.bedrag_excl_btw * 100).to_i if ( self.bedrag_excl_btw >0) 

    # concatenate the detail description and remove tabs, linebreaks with squish
    concat_details_descr  = i.details.map(&:description).join(" - ").squish
    self.omschrijving     = truncate(sanitize(concat_details_descr), :length=> 100)

    self.betaald          = "Ja" # shortcut - all of them paid by now...
    self.datum            = i.payments.last.payment_date # shortcut - everything was just one payment...
    self.betaalwijze      = i.payments.last.payment_method
    return self
  end

end

class DumpedIncomingInvoice < ActiveRecord::Base

  # need to include ActionView::helper because I am using the truncate method
  # source: http://stackoverflow.com/questions/489641/using-helpers-in-model-how-do-i-include-helper-dependencies
  include ActionView::Helpers

  def set_attributes_from_invoice(i) # i should be an IncomingInvoice ActiveResource
    self.factuurdatum     = i.invoice_date
    self.periode          = ((i.invoice_date.month + 2)/3).to_s + "e kw"
    self.factuurnummer    = i.invoice_id
    self.bedrag_excl_btw  = i.price_incl_tax - i.price_tax
    self.btw_bedrag       = i.price_tax
    self.bedrag_incl_btw  = i.price_incl_tax

    invoice_company       = Contact.find(i.contact_id)
    self.omschrijving     = invoice_company.company_name

    self.betaald          = "Ja" # shortcut - ze zijn idd allemaal betaald...
    self.datum            = i.payments.last.payment_date # shortcut - everything was just one payment...
    self.betaalwijze      = i.payments.last.payment_method
    return self
  end

end

(If this was actual code for an app, I would of course factor out the duplication… ;-) )

Define the actual Batch script as a Rake task

There are several ways to load the Rails libraries, but using a Rake task is probably the best solution and there’s an excellent railscast on Rake. I use the FasterCSV library here because I couldn’t figure out quickly how to use tabs instead of commas as separators with the built-in CSV library.  Don’t forget to include gem "fastercsv" in your Gemfile and run bundle install if you do as well.  I followed the Railscast tutorial step by step to come up with a lib/tasks/export.rake that accepts an optional year parameter so you can run:

rake export:invoices
rake export:incoming_invoices year=2011
rake export:all

namespace :export do

  # Note: you need to add :environment as dependency so the rake "environment"
  # task is called that loads the enviromennt

  desc "exports invoices"
  task :invoices => :environment  do      

    param_year = ENV["year"].to_i
    param_year = 2010 if param_year == 0

    # make sure you have included "gem 'fastercsv'" in your Gemfile and run "bundle install"
    # needs to be tab-separated because data contains commas...
    FasterCSV.open("dumped_invoices.csv", 'wb', :col_sep => "\t") do |csv|
      # write the columns headers
      csv << DumpedInvoice.new.attributes.keys
      #iterate over invoices
      Invoice.all.select{|i| i.invoice_date.year == param_year}.sort_by(&:invoice_date).each do |i|
        csv <<  DumpedInvoice.new.set_attributes_from_invoice(i).attributes.values
      end

    end
  end

  desc "exports incoming invoices"
  task :incoming_invoices => :environment  do      

    param_year = ENV["year"].to_i
    param_year = 2010 if param_year == 0

    # make sure you have included "gem 'fastercsv'" in your Gemfile and run "bundle install"
    # needs to be tab-separated because data contains commas...
    FasterCSV.open("dumped_incoming_invoices.csv", 'wb', :col_sep => "\t") do |csv|
      # write the columns headers
      csv << DumpedIncomingInvoice.new.attributes.keys
      #iterate over invoices
      IncomingInvoice.all.select{|i| i.invoice_date.year == param_year}.sort_by(&:invoice_date).each do |i|
        csv <<  DumpedIncomingInvoice.new.set_attributes_from_invoice(i).attributes.values
      end
    end
  end

  task :all => [:invoices, :incoming_invoices]

end

That’s it!  My first blogpost since I started studying Ruby and Rails again after some dabbling with it back in 2006!

Posted in Best Practices, Language | No Comments »