A ruby script demonstrating metaprogramming

sugarcrm_script.rb

require 'active_support'
require 'sugarcrm'
require 'win32ole'

ATTRIBUTES = [:abbreviated_name, :key, :first_name, :last_name, :assigned_user_name, :duplicate_keys_c, 
              :do_not_call, :email1, :invalid_email_c, :do_not_email_c, :primary_address_street, 
              :primary_address_street_2, :primary_address_street_3, :primary_address_city, 
              :primary_address_state, :primary_address_postalcode, :primary_address_country, 
              :primary_address_invalid_c, :do_not_mail_c, :last_purchase_invoice_number, :last_purchase_date,
              :last_repair_job_number, :last_repair_due_date, :last_repair_entry_point]
module Report
  class Customer
    attr_accessor *ATTRIBUTES
    
    def initialize(*args, &block)
      object_or_attributes = args ? args.first : {}
      attributes = object_or_attributes.respond_to?(:attributes) ? object_or_attributes.attributes : object_or_attributes
      attributes.each{|k,v| self.send("#{k}=", v) if self.respond_to? "#{k}=" }
      
      yield self if block_given?
    end
    
    def append_attributes(attributes)
      (ATTRIBUTES - [:abbreviated_name, :key]).each{|a| send("#{a}=", attributes[a.to_s]) }
    end
    
    def last_purchase=(purchase)
    return unless purchase
      [:invoice_number, :date].each do |a|
        send("last_purchase_#{a.to_s}=", purchase.send(a))
      end
    end
    
    def last_repair=(repair)
    return unless repair
      [:job_number, :due_date, :entry_point].each do |a|
        send("last_repair_#{a.to_s}=", repair.send(a))
      end
    end
    
    def to_excel(ws, row_pointer=nil)
      row_pointer ||= ws.UsedRange.Rows.Count
      row_pointer += 1
      ATTRIBUTES.each_with_index do |a,i|
        value = send(a)
        value = value.strftime("%m/%d/%Y") if [Date, Time, DateTime].include? value.class
        ws.Cells(row_pointer,i+1).Value = value
      end
      row_pointer
    end
  end
end

class WIN32OLE
  def self.get_excel
    begin
      connect('excel.Application')
    rescue WIN32OLERuntimeError => e
      new('excel.Application')
    end
  end

  def get_worksheet(name)
    self.Workbooks.each{|wb|
      wb.Worksheets.each{|ws|
        return ws if ws.name == name
      }
    }
    raise "No open worksheets named '#{name}'."
  end
  
  def get_or_create_worksheet(name)
    begin
      get_worksheet(name)
    rescue
      ws = workbooks.Add.Worksheets(1)
      ws.name = name
      ws
    end
  end
end

def add_headers(ws)
  ATTRIBUTES.each_with_index do |a,i|
    ws.Cells(1,i+1).Value = a.to_s
  end
end

load '../../BI/db_connection_mysql_dev.rb' # connect to database
load '../../BI/db_schema.rb' # load schema for ActiveRecord, with relationships

SugarCRM.connect('http://127.0.0.1/crm','username','password')

excel = WIN32OLE::get_excel
excel.Visible = true
ws_result = excel.get_or_create_worksheet("No purchase in 2 years")
add_headers(ws_result)

query = "SELECT * FROM customers c WHERE "
query += "AND c.id NOT IN (SELECT distinct(customer_id) FROM retail_sales s WHERE s.date > DATE('#{(Date.today - 2.years).strftime("%Y-%m-%d")}'))" # customer hasn't purchased in the last 2 years

row_pointer = 2
Customer.find_by_sql(query).each{|c|
  p "Searching for Contact in SugarCRM"
  fetch_error_count = 0
  r_c = Report::Customer.new(c)
  r_c.last_purchase = RetailSale.find_by_sql("SELECT s.invoice_number, s.date FROM retail_sales s WHERE s.customer_id = '#{c.id}'").first
  r_c.last_repair = Repair.find_by_sql("SELECT r.job_number, r.due_date, r.entry_point FROM repairs r WHERE r.customer_id = '#{c.id}'").first
  contact = nil
  begin
    contact = SugarCRM::Contact.find_by_any_key(c.key)
  rescue Timeout::Error => e
    fetch_error_count += 1
    if
      p e.message
      retry
    else
      raise
    end
  end
  if contact
    r_c.append_attributes(contact.attributes)
  else
    p "Contact with key #{c.key} not found in SugarCRM."
  end
  row_pointer = r_c.to_excel(ws_result, row_pointer)
}