# 基于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("--0 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=> "123@qq.com" , :cc =>"123@qq.com" ,:subject => "售订单统计")
end
# 生成报表
class SqlToXlsx
def self.order_dayly_report
# 定义SQL
sql = <<-SQL
-------
SQL
# 解析SQL
columns = {订单日期 客户订单编号 收货联系人电话 }
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