require 'active_record'
require 'spreadsheet'

ActiveRecord::Base.establish_connection(
  adapter:  'mysql2', # or 'postgresql' or 'sqlite3' or 'oracle_enhanced'
  host:     'localhost',
  database: 'moulagen',
  username: 'root',
  password: ''
)

class Moulage < ActiveRecord::Base
  self.table_name = "moulagen"
end

book = Spreadsheet::Workbook.new
sheet = book.create_worksheet

guys = {}
guys.default_proc = proc do |hash, key|
  hash[key] = {
    type: "",
    count: 0,
    ids: []
  }
end

Moulage.find_each do |m|
  str = m["Hersteller_bzw._Künstler"]
  # seperate by comma
  begin
    mouleur, arzt = str.split('Arzt:')
    mouleur = mouleur.gsub("Mouleur: ", "")
    
    arzt.strip!
    mouleur.strip!
    
    guys[arzt][:count] += 1
    guys[arzt][:ids].push(m[:id])
    guys[arzt][:type] = "Arzt"
    
    guys[mouleur][:count] += 1
    guys[mouleur][:ids].push(m[:id])
    guys[mouleur][:type] = "Mouleur"
  rescue
    puts "Fehlerhaftes Format bei #{m.id}: \"#{str}\""
  end
end

guys = guys.to_a.sort do |x, y|
  typemap = { "Arzt" => 100000, "Mouleur" => 0 }
  xval = typemap[x[1][:type]] + x[1][:count] if x[1]
  yval = typemap[y[1][:type]] + y[1][:count] if y[1]
  yval <=> xval
end


index = 0
guys.each do |name, vals|
  row = sheet.row(index)
  row.push vals[:type]
  row.push name
  row.push vals[:count]
  row.push vals[:ids].to_s.gsub("[", "").gsub("]", "")
  # TODO bereits vorhanden?
  
  index += 1
end

book.write 'out.xls'

puts "#{guys.length} Zeilen in .xls Datei exportiert"