import_excel.rb

#!/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