A script for processing iTunes finance reports and importing to FreeAgent

process_finance_report.rb

#!/usr/bin/env ruby

### REQUIRED GEMS: restclient, activesupport, crack, mash, money, fastercsv

### USAGE
# fetch_finance_reports finance_report_1 finance_report_2 ... finance_report_x

### BEGIN CUSTOMIZATION

# I like to store this in a separate file in my home folder, ~/.freeagent
# if you want to do this, comment out the block below

ENV['FA_COMPANY']  = 'yourfreeagentsubdomain'
ENV['FA_USERNAME'] = 'yourfreeagentemail'
ENV['FA_PASSWORD'] = 'yourfreeagentpassword'

# and uncomment the following line
# load('~/.freeagent')

YOUR_APP_NAME = 'Squeemote' # change this to your own app

# replace these with the IDs of your own FreeAgent contacts for each Apple entity
FREEAGENT_ITUNES_CONTACTS = {
  'AU' => 27492,
  'CA' => 12710,
  'EU' => 12561,
  'GB' => 12561,
  'US' => 12709,
  'WW' => 12709
}

### END CUSTOMIZATION

### FREEAGENT API WRAPPER

require 'rubygems'
require 'restclient'
require 'crack'
require 'mash'
require 'active_support/all'

RestClient::Resource.class_eval do
  def root
    self.class.new(URI.parse(url).merge('/').to_s, options)
  end
end

module FreeAgent
  class Company
    def initialize(domain, username, password)
      @resource = RestClient::Resource.new(
        "https://#{domain}.freeagentcentral.com",
        :user => username, :password => password
      )
    end
    
    def invoices
      @invoices ||= Collection.new(@resource['/invoices'], :entity => :invoice)
    end
    
    def contacts
      @contacts ||= Collection.new(@resource['/contacts'], :entity => :contact)
    end
    
    def expenses(user_id, options={})
      options.assert_valid_keys(:view, :from, :to)
      options.reverse_merge!(:view => 'recent')
      
      if options[:from] && options[:to]
        options[:view] = "#{options[:from].strftime('%Y-%m-%d')}_#{options[:to].strftime('%Y-%m-%d')}"
      end
      
      Collection.new(@resource["/users/#{user_id}/expenses?view=#{options[:view]}"], :entity => :expense)
    end
  end
  
  class Collection
    def initialize(resource, options={})
      @resource = resource
      @entity = options.delete(:entity)
    end
    
    def url
      @resource.url
    end
    
    def find(id)
      entity_for_id(id).reload
    end
    
    def find_all
      case (response = @resource.get).code
      when 200
        if entities = Crack::XML.parse(response.body)[@entity.to_s.pluralize]
          entities.map do |attributes|
            entity_for_id(attributes['id'], attributes)
          end
        else
          []
        end
      end
    end
    
    def create(attributes)
      payload = attributes.to_xml(:root => @entity.to_s )
      case (response = @resource.post(payload, 
        :content_type => 'application/xml', :accept => 'application/xml')).code
      when 201
        resource_path = URI.parse(response.headers[:location]).path
        Entity.new(@resource.root[resource_path], @entity)
      end
    end
    
    def update(id, attributes)
      entity_for_id(id).update(attributes, headers)
    end
    
    def destroy(id)
      entity_for_id(id).destroy
    end
    
    private
    
    def entity_for_id(id, attributes={})
      Entity.new(@resource["/#{id}"], @entity, attributes)
    end
  end
  
  class Entity
    attr_reader :attributes
    
    def initialize(resource, entity, attributes = {})
      @resource, @entity = resource, entity
      @attributes = attributes.to_mash
    end
    
    def url
      @resource.url
    end
    
    def collection(path, entity)
      Collection.new(@resource[path], :entity => entity)
    end
    
    def reload
      returning(self) do
        @attributes = Crack::XML.parse(@resource.get)[@entity.to_s].to_mash
      end
    end
    
    def update(attributes = {})
      @resource.put(attributes.to_xml(:root => @entity.to_s.downcase),
        :content_type =>'application/xml', :accept => 'application/xml')
    end
    
    def destroy
      @resource.delete
    end
    
    private
    
    def method_missing(*args)
      @attributes.send(*args)
    end
  end
end

### ITUNES FINANCIAL REPORT WRAPPER

require 'fastercsv'
require 'money'

module ITunes
  class FinancialReport
    attr_reader :territory, :currency
    attr_reader :start_date, :end_date
    attr_reader :total_sales, :partner_share
    attr_reader :filename
    attr_reader :month, :year
  
    def initialize(path_to_csv)
      extract_data_from_csv_name(path_to_csv)
      parse_data(path_to_csv)
    end
  
    def total_share
      @partner_share * @total_sales
    end
  
    private
  
    def extract_data_from_csv_name(csv)
      match = csv.match(/(\w{8})_(\d{2})(\d{2})_(\w{2}).txt/)
      @filename = match[0]
      @month = match[2].to_i
      @year = match[3].to_i
      @territory = match[4]
    end
  
    def parse_data(csv_file)
      table = FasterCSV.read(csv_file, 
        :headers => true, 
        :col_sep => "\t", 
        :converters => :numeric
      )
      extract_shared_data_from_row(table[0])
      @total_sales = table.by_row.inject(0) do |sum, row|
        sale_or_return = row['Sales or Return']
        if %w{S R}.include?(sale_or_return)
          sum + row['Quantity']
        else
          sum
        end
      end
    end
  
    def extract_shared_data_from_row(row)
      @start_date     = Date.parse(row['Start Date'])
      @end_date       = Date.parse(row['End Date'])
      @currency       = row['Partner Share Currency']
      @partner_share  = Money.new(row['Partner Share'].to_f * 100, @currency)
    end
  end
end

### ACTUAL PROCESSING SCRIPT

RestClient.proxy = ENV['http_proxy']

csv_files = ARGV.map { |relative_path| File.join(Dir.pwd, relative_path) }.select { |csv| File.exist?(csv) }
if csv_files.empty?
  puts "Specify a valid path to at least one financial report file."
  exit 1
end

freeagent = FreeAgent::Company.new(ENV['FA_COMPANY'], ENV['FA_USERNAME'], ENV["FA_PASSWORD"])
reports = csv_files.map { |csv| ITunes::FinancialReport.new(csv) }

last_invoice_reference = freeagent.invoices.find_all.last.reference
invoice_code, invoice_number = *last_invoice_reference.split("-")
next_invoice_reference = "#{invoice_code}-#{'%04d' % (invoice_number.to_i + 1)}"

begin
  invoice = freeagent.invoices.create(
    :reference  => next_invoice_reference,
    :contact_id => FREEAGENT_ITUNES_CONTACTS[reports[0].territory],
    :dated_on   => Date.today.to_time,
    :payment_terms_in_days => 30,
    :currency   => reports[0].currency
  )
  reports.each do |report|
    invoice.collection('/invoice_items', :invoice_item).create(
      :item_type      => 'Products',
      :quantity       => report.total_sales,
      :price          => report.partner_share,
      :sales_tax_rate => 0,
      :description    => %{
        #{YOUR_APP_NAME} units, #{report.start_date.to_formatted_s(:short)} - #{report.end_date.to_formatted_s(:short)}, #{report.territory}
        iTunes financial report total: #{report.total_share} #{report.currency}
      }.strip
    )
  end
rescue RestClient::RequestFailed => e
  puts "Error: #{e.response.body}"
  exit 1
rescue StandardError => e
  puts "Error: #{e}"
  invoice.destroy if invoice
end

Information about this can be found on my blog.