seed realm ranking

search.rb

require 'sequel'
require 'CSV'
require 'awesome_print'

houses = [121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 245, 197, 234, 164, 165, 166, 167, 168, 169, 170, 171, 172, 173, 174, 175, 176, 177, 178, 179, 180, 181, 182, 183, 184, 185, 186, 187, 188, 189, 190, 191, 192, 193, 194, 195, 196, 198, 199, 200, 201, 202, 203, 204, 205, 206, 207, 208, 214, 209, 210, 211, 212, 213, 241, 222, 231, 237, 216, 221, 218, 215, 217, 219, 220, 223, 224, 225, 226, 232, 227, 228, 229, 230, 233, 235, 236, 238, 239, 240, 242]


def month(year, month)
  12 * (year - 2014) + month - 1
end

Seed = Struct.new(:seed_id, :factor, :herb_id) do
  attr_accessor :seed_qty, :seed_price
  attr_accessor :herb_price

  def daily_quantities(line)
    1.upto(31).map do |day|
      key = ("qty%2d" % day).to_sym
      line[key] || 0
    end
  end


  def daily_prices(line)
    1.upto(31).map do |day|
      key = ("mktslvr%2d" % day).to_sym
      line[key]
    end.reject(&:nil?)
  end

  def query(dataset, itemid)
    puts dataset.where(item: itemid).sql
    dataset.where(item: itemid).all
  end

  def quantity(lines)
    quantities = lines.map { |l| daily_quantities(l) }.flatten
    quantities.reduce(:+)
  end

  def price(lines)
    prices = lines.map { |l| daily_prices(l) }.flatten
    prices.reduce(:+) / prices.size.to_f / 100.0
  end

  def estimate_herb(dataset)
    line = query(dataset, herb_id)
    @herb_price = price(line)
  end

  def to_s
    "seed: #{seed_id} - qty:#{seed_qty} - price: %0.2f, herb_price %0.2f, worth: %0.2f" %
      [seed_price, herb_price, worthiness]
  end

  def worthiness
    [seed_qty, 50*(30*3/7.0)*1.3].max * (herb_price * factor - seed_price) / 3.0 / 30
  end

  def estimate_seed(dataset)
    lines = query(dataset, seed_id)
    @seed_qty = quantity(lines)
    @seed_price = price(lines)
  end

  def estimate(dataset)
    estimate_seed(dataset)
    estimate_herb(dataset)
  end
end

class HouseScore
  def initialize(houseid, dataset)
    @dataset = dataset.where(house: houseid)
    @seeds = [
      Seed.new(129288, 19, 124105),
      Seed.new(129284, 83, 124101),
      Seed.new(129285, 67, 124102),
      Seed.new(129287, 70, 124104)
    ]
  end

  def score
    @seeds.map do |seed|
      seed.estimate(@dataset)
      seed.worthiness
    end.reduce(:+) / @seeds.size.to_f
  end
end


DB = Sequel.connect('mysql2://newswire.theunderminejournal.com/newsstand')
dataset = DB[:tblItemHistoryMonthly]


def save_score(scores)
  CSV.open("realm-score.csv", "wb") do |csv|
    scores.each do |score|
      csv << score
    end
  end
end

score_array = []

CSV.foreach('realm-score.csv') do |row|
  score_array << [row[0].to_i, row[1]]
end

scores = score_array.to_h
ap scores

september = month(2017, 9)
december = month(2017, 12)
lastMonths = dataset.where(month: september..december)
houses.each do |house|
  if scores[house]
    next
  end
  houseScore = HouseScore.new(house, lastMonths)
  scores[house] = houseScore.score
  save_score(scores.to_a)
end

CSV.open("realm-name-score.csv", "wb") do |csv|
  realms = DB[:tblRealm].where{ population > 500}
  scores.each do |house, score|
    realm = realms.where(house: house).first
    csv << [realm[:name], score]
  end
end