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) }