monthly_imp_and_play.rb

_pry_.config.print = proc {}
campaigns = Campaign.includes(:customer).all
Time.zone = 'Asia/Tokyo'
start_at = (Time.zone.now - 1.month).beginning_of_month
end_at = (Time.zone.now - 1.month).end_of_month
campaign_data = campaigns.map do |cam|
  imp_value = ReportBase.where(delivery_id: cam.deliveries.map(&:id), report_id: 1, report_at: start_at..end_at).sum(:value) || 0
  play_value = ReportBase.where(delivery_id: cam.deliveries.map(&:id), report_id: 101, report_at: start_at..end_at).sum(:value) || 0
  { customer_name: cam.customer.customer_name, name: cam.name, imp: imp_value, play: play_value }
end

data = campaign_data.group_by { |c| c[:customer_name] }.map do |c|
  imp_sum = 0
  play_sum = 0
  detail = c[1].map do |d|
    imp_sum += d[:imp]
    play_sum += d[:play]
    next if d[:imp].zero?
    [d[:name], d[:imp], d[:play]]
  end.compact.sort_by { |d| d[1] }.reverse
  [c[0], detail, imp_sum, play_sum]
end.sort_by { |c| [c[2], c[3]] }.reverse

File.open('tmp/imp.csv', 'w', encoding: 'SJIS', undef: :replace, invalid: :replace) do |f|
  f.write("アカウント名,プロジェクト名,インプレッション,再生\n")

  data.each do |d|
    f.write("#{d[0]}\n")

    d[1].each do |detail|
      b = ['', detail[0], detail[1], detail[2]].join(',')
      f.write("#{b}\n")
    end

    b = ['小計', '', d[2], d[3]].join(',')
    f.write("#{b}\n")
  end
end