'1:00 am' do rake "report:dayly_orders"end"/>

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

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

# 基于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

推荐阅读