633k

A challenge was presented to me with my most recent project. Setup a script to run nightly (by a cronjob) to retrieve records for a past days orders. With these orders, generate an Excel spreadsheet followed by an email with this spreadsheet attached. I accomplished this with a lot of trial and error and I’m going to present the code for which you can do the same when/if you need to. Info was scarce on the web on how to do exactly what I nedeed to do, so hopefully this will enlighten some lucky folks out there. Ha! As always, this probably can be improved quite a bit, I’m just glad to have it done and working! (Client happy..)

Keep in mind that you need the spreadsheet-excel gem. Install it via:

gem install spreadsheet-excel

Here goes:

#!/usr/bin/env ruby

# ABSTRACT
# This file is to be executed by a cron script that is setup on a local server

# PROCESS
# 1. Load Rails environment and make connection to database
# 2. Get list of operators, get orders which have an agent that belongs to that partner to operator
# 3. With that list of orders, create the Excel spreadsheet
# 4. Mail spreadsheet to operator (CC to mycablehome)
# 5. Create record in Leads table with a reference to the spreadsheet

RAILS_ENV = 'production'

require File.dirname(__FILE__) + '/../config/environment'
require "spreadsheet/excel"

@operators = Operator.find(:all, :select => 'id, site, name, system_city, report_email_address_to, report_email_address_cc')
@operators.each do |operator|
  @orders = Order.find(:all, 
    :conditions => [ "operator_id = ? AND created_at #{(1.day.ago.to_date..Date.today).to_s(:db)}", operator.id ])

  file = "#{1.day.ago.to_date}_#{operator.site}_#{operator.system_city.underscore.downcase}_sales_leads.xls"
  workbook = Spreadsheet::Excel.new("#{RAILS_ROOT}/public/reports/#{file}")

  worksheet = workbook.add_worksheet("#{operator.site} Sales Leads for #{1.day.ago.to_date}")
  worksheet.write(0, 0, "Customer name")
  worksheet.write(0, 1, "Email")
  worksheet.write(0, 2, "Service street address")
  worksheet.write(0, 3, "City")
  worksheet.write(0, 4, "State")
  worksheet.write(0, 5, "Zip")
  worksheet.write(0, 6, "Home phone")
  worksheet.write(0, 7, "Daytime phone")
  worksheet.write(0, 8, "Cable Package")
  worksheet.write(0, 9, "Services")
  worksheet.write(0, 10, "Num. of TV's")
  worksheet.write(0, 11, "Num. dig A/O")
  worksheet.write(0, 12, "Num. HD/DVR")
  worksheet.write(0, 13, "HD only")
  worksheet.write(0, 14, "Installation date")
  worksheet.write(0, 15, "Installation time")
  worksheet.write(0, 16, "Alternate installation date")
  worksheet.write(0, 17, "Alternate installation time")
  worksheet.write(0, 18, "Order num")
  worksheet.write(0, 19, "Comments")

  row = 1
  @orders.each do |order|
    worksheet.write(row, 0, "#{order.customer.first_name} #{order.customer.last_name}")
    worksheet.write(row, 1, "#{order.customer.email}")
    worksheet.write(row, 2, "#{order.customer.addresses.first.address}")
    worksheet.write(row, 3, "#{order.customer.addresses.first.city}")
    worksheet.write(row, 4, "#{order.customer.addresses.first.state}")
    worksheet.write(row, 5, "#{order.customer.addresses.first.zip}")
    worksheet.write(row, 6, "#{order.customer.home_phone}")
    worksheet.write(row, 7, "#{order.customer.daytime_phone}")
    unless order.package.nil?
    worksheet.write(row, 8, "#{order.package.name}")
    worksheet.write(row, 9, "#{order.package.services.collect { |s| "#{s.name}  " } }")
    worksheet.write(row, 10, "#{order.tv_sets}")
    worksheet.write(row, 11, "#{order.digital_boxes}")
    worksheet.write(row, 12, "#{order.dvr_boxes}")
    worksheet.write(row, 13, "#{order.own_hdtv_set}")
    worksheet.write(row, 14, "#{order.operator_order.installation_date}")
    worksheet.write(row, 15, "#{order.operator_order.installation_time}")
    worksheet.write(row, 16, "#{order.operator_order.alternate_installation_date}")
    worksheet.write(row, 17, "#{order.operator_order.alternate_installation_time}")
    end
    worksheet.write(row, 18, "#{order.id}")
    worksheet.write(row, 19, "#{order.operator_order.special_instructions}") unless order.package.nil?
    row += 1
  end

  workbook.close
  
  OrderMailer.deliver_sales_leads(operator.report_email_address_to, operator.report_email_address_cc, operator.site, operator.system_city)
end

Hopefully this will get you started.

Labels: programming, ruby on rails

// posted by Wes Rogers @ 14:00