rails生成excell文件，并上传到FTP服务器

訾雅萍

desc 'hainan upload college'
namespace :hn_upload_college do
task :generate => :environment do
require"net/ftp"
@time = Time.now - 1.month
@year = @time.year.to_s
if @time.month < 10
@month = "0"+@time.month.to_s
else
@month = @time.month.to_s
end
@fday = "0"+@time.beginning_of_month.day.to_s
@lday = @time.end_of_month.day.to_s
filename = @year+@month+"-WLAN-GaoXiao.csv"
local_path = "#{RAILS_ROOT}/tmp/import/#{filename}"
sql = "select i.citycn,i.towncn,d.code_name,w.sampleyear,w.samplemonth,round((sum(RXBytesTotal)+sum(TXBytesTotal))/1000,2) flow
from device_infos i,wlan_ap_perf_aggr_month w,dic_codes d
where i.ap(+) = w.ap
and i.port_type = d.id(+)
and d.code_name = '高校'
and w.sampleyear = to_char(add_months(sysdate,-1),'yyyy')
and w.samplemonth = to_char(add_months(sysdate,-1),'mm')
group by i.citycn,i.towncn,d.code_name,w.sampleyear,w.samplemonth"
con = External.connection()
@aps = con.select_all(sql)
local_file = File.new(local_path, "w")
date = ["报表分析时间范围：".to_iso,@year+"/"+@month+"/"+@fday,@year+"/"+@month+"/"+@lday]
local_file << date.join(",") + "\n"
header = ["地市".to_iso,"郊县".to_iso,"热点类型".to_iso,"年份".to_iso,"月份".to_iso,"无线侧流量(MB)".to_iso]
local_file << header.join(",") + "\n"
@aps.each do |ap|
data=[]
for b in %w[citycn towncn code_name   sampleyear samplemonth flow]
ap["#{b}"] ? a=ap["#{b}"].to_s.to_iso : a=''
data<<a
end
local_file << data.join(",") + "\n"
end
local_file.close
uploadftp(local_path)
end
end
def uploadftp(file)
ftp_config = YAML.load_file("#{RAILS_ROOT}/config/value_analysis.yml").symbolize_keys!
Net::FTP.open(ftp_config[:hn_upload_host], ftp_config[:hn_upload_username], ftp_config[:hn_upload_password]) { |ftp|
ftp.put file, File.join(ftp_config[:hn_upload_remote_path], File.basename(file))
}
end