#encoding: utf-8

class MessingController < ApplicationController
  before_filter :login_required
  layout "application"

  def index
  end

  def year #erste Seite einer Abfrage
    @preselected_years = ActiveRecord::Base.connection.execute("SELECT DISTINCT jahr FROM k_laender_fuer_aktualisierung").collect{|row| row['jahr'].to_i}
  end

  def bula
    redirect_to :action => :year unless params[:jahr]
    connection = ActiveRecord::Base.connection
    @bulaender = connection.query("SELECT id,ltext FROM l_bundeslaender WHERE id > 0 ORDER BY sortier")
    @preselected_bulas = connection.execute("SELECT DISTINCT jahr, land_id FROM k_laender_fuer_aktualisierung").to_a
  end

  def delete
    @statements = ["delete from k_laender_fuer_aktualisierung;"]
    @years = []
    params[:bula].each do |bula|
      jahr = bula[0..3]
      land_id = bula[4..-1]
      unless (2007..2100) === jahr.to_i && (1..16) === land_id.to_i
        flash[:notice] = "Es wurden unzulässige Werte gewählt."
        redirect_to :action => :year and return false
      end
      @years.push(jahr) unless @years.include?(jahr)
      @statements.push("insert into k_laender_fuer_aktualisierung values ( #{land_id}, #{jahr}, '#{DateTime.now.strftime('%Y-%m-%d %H:%M')}');")
      @statements.push("delete from k_suppressoren where land_id = #{land_id} and jahr = #{jahr}")
    end    
    @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 = []
#    @statements.push(["#{RAILS_ROOT}/lib/sql/40_koordinaten.sql"])
    @statements.push(["#{RAILS_ROOT}/lib/sql/42_mocking_bname_etc.sql"])
    @statements.push(["#{RAILS_ROOT}/lib/sql/44_messing_int_m_oth_m.sql"])
    @statements.push(["#{RAILS_ROOT}/lib/sql/46_messing_ets_ippc_unece-emep.sql"])
    @statements.push(["#{RAILS_ROOT}/lib/sql/48_mocking_bvcode_ids_individuell.sql"])
    @statements.push(["#{RAILS_ROOT}/lib/sql/50_messing_int_m_cen_iso.sql"])
    @statements.push(["#{RAILS_ROOT}/lib/sql/52_mocking_suppression_individuell.sql"])
    @statements.push(["#{RAILS_ROOT}/lib/sql/54_mocking_abfall_individuell.sql"])
    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|
      @statements.push(["#{RAILS_ROOT}/lib/sql/71_gis_accel_delete_create.sql",jahr])
      @statements.push(["#{RAILS_ROOT}/lib/sql/72_gis_accel_insert.sql",jahr])
      role = 'e_prtr_ro'
      txt = '%Q^'+IO.read("#{RAILS_ROOT}/lib/sql/79_grants_special.sql")+'^'
      @statements.push(eval(txt))
      role = 'microstrategy_ro'
      txt = '%Q^'+IO.read("#{RAILS_ROOT}/lib/sql/79_grants_special.sql")+'^'
      @statements.push(eval(txt))
    end
    @statements.push(["#{RAILS_ROOT}/lib/sql/60_betriebe_reinigen.sql"])
    @statements.push(["#{RAILS_ROOT}/lib/sql/69_grants_normal.sql"])
    perform
  end

  private
  def perform
    directory = nil
    connection = ActiveRecord::Base.connection
    @results = []
    @statements.each do |statement|
      puts statement[0]
      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 = "#{RAILS_ROOT}/log/clean_up/#{Time.now.strftime("%Y-%m-%d_%H%M%S")}"
              `mkdir -p #{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


 
end



