定时跑sql生成xlsx文件发邮件给客户

# 基于Gem whenever的定时脚本
 
set :output, "/log/cron_log.log"
 
every 1.day, :at => '1:00 am' do
  rake "report:dayly_orders"
end
 
 
# rake任务
 
namespace :report do
  task dayly_orders: :environment do
    
    search_time = (Time.now - 1.days).strftime("%Y-%m-%d 00:00:00")
    # 发送邮件
    ReportMailer.dayly_report_mail(search_time).deliver
 
  end
end
# 邮件设置

def dayly_report_mail(search_time)
    attachments['订单统计'<< '-' << search_time.to_s << '.xlsx'] = SqlToXlsx.order_dayly_report
    attachments['产品日报'<< '-' << search_time.to_s << '.xlsx'] = SqlToXlsx.product_dayly_report
    mail(:to=> "[e-mail]" , :cc =>"[e-mail]" ,:subject => "售订单统计")
end
 
# 生成报表
 
class SqlToXlsx
 
def self.order_dayly_report
 # 定义SQL
   sql = <<-SQL
         -------
   SQL
   # 解析SQL
    columns = %w{订单日期 客户订单编号 收货联系人电话 } 
    reports = execute_sql(sql)
    # 生成EXCELL
    to_xlsx(columns,reports)
end
 
def self.execute_sql(sql)
 #执行报表sql查询
 cullent_attributes = ActiveRecord::Base.connection.execute(sql)
end
 
def self.to_xlsx(columns,reports,search_time)
 # 生成EXCELL
   file = Spreadsheet::Workbook.new
 
   list = file.create_worksheet :name =>Time.now
   list.row(0).concat columns
 
   reports.each_with_index { |report, i|
          list.row(i+1).concat report
   }
 
   xls_report = StringIO.new 
   file.write xls_report  
   xls_report.string 
end
 
end