#encoding: utf-8
require_relative 'db_controller'



def delete
  @statements = ["delete from k_laender_fuer_aktualisierung;"]
  connection = ActiveRecord::Base.connection
  @years = connection.execute("SELECT DISTINCT jahr FROM k_laender_fuer_aktualisierung").to_a.collect { |row| row['jahr']  }
  @years.each do |jahr|
    txt = '%Q^'+IO.read("#{RAILS_ROOT}/lib/sql/21_drop_gis_easy_daten.sql")+'^'
    @statements.push(eval(txt))
  end
  @statements.push(IO.read("#{RAILS_ROOT}/lib/sql/25_delete_landdaten.sql"))

  perform
end

def clean_up
  @statements = []
  # Koordinaten werden jetzt vom Importer erledigt
  @statements.push(["sql/42_mocking_bname_etc.sql"])
  @statements.push(["sql/44_messing_int_m_oth_m.sql"])
  @statements.push(["sql/46_messing_ets_ippc_unece-emep.sql"])
  @statements.push(["sql/48_mocking_bvcode_ids_individuell.sql"])
  @statements.push(["sql/50_messing_int_m_cen_iso.sql"])
  @statements.push(["sql/52_mocking_suppression_individuell.sql"])
  # @statements.push(["sql/54_mocking_abfall_individuell.sql"])
  @statements.push(["sql/69_grants_normal.sql"])
  perform
end

def special_tables
  @statements = []
  connection = ActiveRecord::Base.connection
  years = connection.execute("SELECT DISTINCT jahr FROM p_betrieb ORDER BY jahr").to_a.collect { |row| row['jahr']  }
  years.each do |jahr|
    @statements.push(["sql/71_gis_accel_delete_create.sql",jahr])
    @statements.push(["sql/72_gis_accel_insert.sql",jahr])
    role = 'e_prtr_ro'
    txt = '%Q^'+IO.read("sql/79_grants_special.sql")+'^'
    @statements.push(eval(txt))
    role = 'microstrategy_ro'
    txt = '%Q^'+IO.read("sql/79_grants_special.sql")+'^'
    @statements.push(eval(txt))
    @statements.push(["sql/60_betriebe_reinigen.sql",jahr])
  end
  @statements.push(["sql/69_grants_normal.sql"])
  perform
end

def suppress(filename)
  File.open(filename, "r") do |infile|
    while (line = infile.gets)
      jahr,kennnr,name,grund,bl = line.split("\t")
      next if jahr =~ /Jahr/
      kennnr.chomp!
      name.chomp!
      grund.chomp!
      bl.chomp!
      kennnr.sub!(/^"(.*)"$/,'\1')
      name.sub!(/^"(.*)"$/,'\1')
      grund.sub!(/^"(.*)"$/,'\1')
      bl.sub!(/^"(.*)"$/,'\1')
      am = Time.new.strftime("%Y-%m-%d %H:%M:%S")

      sql = "INSERT INTO k_suppressoren
		(land_id, jahr, am, kennnr, name, grund)
		SELECT fs.id, #{jahr},'#{am}','#{kennnr}','#{name}','#{grund}'
		FROM l_bundeslaender fs
		WHERE fs.ktext = '#{bl}'"
      #~ puts sql
      ActiveRecord::Base.connection.execute(sql)
    end
  end

  @statements = [["sql/52_mocking_suppression_individuell.sql"]]
  perform
end

def suppress_from_errortable(filename)
  entries = {}
  File.open(filename, "r") do |infile|
    while (line = infile.gets)
      next if line.chomp[0] == '#'
      status, typ, typbeschreibung, land, jahr, zp, kennnr, name, fbn, bid,sid = line.split(";")
      next if jahr =~ /Jahr/
      hash = jahr+kennnr
      if entries.has_key?(hash)
        entries[hash][:grund] << fbn
      else
        entries[hash] = {:jahr => jahr, :land => land, :grund => [fbn], :name => name, :kennnr => kennnr}
      end
    end
  end

  @statements = []
  am = Time.new.strftime("%Y-%m-%d %H:%M:%S")
  entries.each do |key, value|
    if value[:grund].length > 1024
      value[:grund] = value[:grund][0..1023]
    end
    puts "Unterdrücke Betrieb #{value[:kennnr]} #{value[:name]} für Land #{value[:land]} vom Jahr #{value[:jahr]}"
    sql = "INSERT INTO k_suppressoren
  (land_id, jahr, am, kennnr, name, grund)
  VALUES (#{value[:land]}, #{value[:jahr]},'#{am}','#{value[:kennnr]}','#{value[:name]}','#{value[:grund].uniq.join(", ")}')"
    puts sql
    @statements << sql
  end

  @statements << ["sql/52_mocking_suppression_individuell.sql"]
  perform
end

private
def perform
  directory = nil
  connection = ActiveRecord::Base.connection
  @results = []
  @statements.each do |statement|
    puts "Führe SQL aus: " + statement.to_s + "\n" 
    if statement.is_a? Array
      filename, sql_pack = case statement.length
      when 1 then [File.basename(statement[0],'.sql'),IO.read(statement[0])]
      when 2 then jahr = statement[1]; [File.basename(statement[0],'.sql'),eval('%Q^'+IO.read(statement[0])+'^')] # jahr is used in eval
      end
    else
      sql_pack = statement
    end
    pack = sql_pack.split(';')
    pack.each_index do |sql_index|
      sql = pack[sql_index]
      begin
        result = connection.execute(sql)
        if filename
          unless directory
            directory = "log/clean_up/#{Time.now.strftime("%Y-%m-%d_%H%M%S")}"
            `mkdir #{directory}`
          end
        end
        if result.num_tuples == 0
          @results.push([sql, result.cmd_tuples])
          if filename
            File.open("#{directory}/#{filename}_value", 'a') {|f| f.write("#{sql} \n Betrifft #{result.cmd_tuples} Zeilen") }
          end
        else
          @results.push([sql, result.to_a])
          if filename
            csv_data = '"'+sql+'"'+"\n"
            csv_data << '"'+result.fields.join(%Q^","^)+'"'+"\n"
            result.each do |row|
              csv_data << '"'+row.to_a.collect{|val| val[1]}.join(%Q^","^)+'"'+"\n"
            end
            csv_data.encode!("ISO-8859-1")
            File.open("#{directory}/#{filename}_table#{sql_index}", 'w') {|f| f.write(csv_data) }
          end
        end
      rescue
        @results.push([sql,{'Fehler: ' => $!.to_s}])
      end
    end
  end
end

def usage
  puts '

USAGE:
ruby lib/main.rb {prepare|correct|create_specials|suppress <filename>|suppress_from_errortable <filename>}

prepare                löscht alle Einträge für alle BL/Jahr-Kombinationen in k_laender_fuer_aktualisierung

correct                führt die vorher als messing bekannten Korrekturskripte (post-import) aus

create_specials        erzeugt die Beschleunigertabellen für den PRTR-Webauftritt (micht jedoch
                       das HTML, das vom P-Modul-Renderer erzeugt werden muss)

suppress <filename>    fügt alle Einträge aus filename (csv-File im Format: Jahr{TAB}Kennnr{TAB}Betriebsname{TAB}Grund{TAB}Land)
                       zu k_suppressoren hinzu und setzt xml3export=0 für betroffene Betriebe

suppress <filename>    fügt alle Einträge aus filename (xml3_errortable.csv des Exports) zu 
                       k_suppressoren hinzu und setzt xml3export=0 für betroffene Betriebe
  
  
  '
end

begin
  case ARGV[0]
  when "correct" then
    clean_up
  when "prepare" then
    delete
  when "create_specials" then
    special_tables
  when "suppress" then
    if ARGV[1].nil?
      usage
    else
      suppress(ARGV[1])
    end
  when "suppress_from_errortable" then
    if ARGV[1].nil?
      usage
    else
      suppress_from_errortable(ARGV[1])
    end
  else usage
  end
rescue Exception => e
  puts e.message
  puts e.backtrace.join("\n")
end

exit
