#encoding: utf-8
# vim: ts=2 sw=2 syntax=ruby et
#
# This is the importer.
#
# Die int_columns[] werden zusammen mit dem Zeileninhalt der CSV-Datei in der Class Method
# Tabelle.verarbeiten (Zeile ≈1780, "int_columns-filling-point")
# gefüllt. Sie enthalten:
#   column_index, uba_key, cell, rory_typ, import_typ, internal_value, column_name
#
# import_typ:
# Die „Typen“ beim Import sind nicht mit den reference_types aus model.columns.reference_type
# zu verwechseln. Man findet sie in int_columns an Position [4] und sie sind:
# 1 - Zahl
# 2 - String
# 3 - föderale Staaten-Identifikation
# 4 - Pointer (id oder „fetchme_later“), manchmal auch Key
# 5 - Pointerarray - NEIN, AUSSCHLIESSLICH successors.
# 6 - federal_state_reference_id; diese wird benutzt, wenn man zwei Objekte in einer Zeile angeben muss.
# 7 - EU-Schlüssel. Der wird i.a. so generiert:
#     DE<AG/TP/DP>_<Bundeslandkennung><bundeslandspezifischer Schlüssel>
# 8 - Pointer-Referenz (id oder „fetchme_later“), manchmal auch Key
#
# Der rory_typ ist der model.columns.reference_type: "Art der Referenz auf eine Fremdtabelle"
#   0 = keine Referenz
#   1 = benutzt foreign_column
#   2 = Standardliste
#   3 = bool (0/1)
#   4 = date
#   5 = Function auf id von Zeile
#   6 = Bundeslaender (je Nutzer beschraenkt)
#   7 = langtext
#   8 = Integer
#   9 = Double
#  10 = Liste mit Bundeslandbeschraenkung
#  11 = Name der Sonderfunktion in surrounding_function
#  12 = Spezialtyp Wassertypen
#  13 = Spezialtyp dp_Anlagenreferenz (mit Bundeslandbschr.)
#
# The import mode is one of the following:
# "append"    : Anfügen (bestehende Werte werden nicht überschrieben)
# "overwrite" : Überschreiben
# "overkill"  : Überschreiben und leere Felder löschen
#
# Structure of @int_lines:
# line_index    : Index der Zeile im Array @lines (== self.lines)
# @zeilennummer : Zeilennummer im Importfile
# int_line      : [ line_index, @zeilennummer ]
# @int_lines    : Array der importierbaren Zeilen im Format [ [line_index, zeilennummer], [spalte,spalte,...] ]
#   The array [spalte, spalte, ...] is most often named int_columns here.
#   The single column spalte of int_columns is an array again and comprises seven elements:
#     * [0] column_index (index of the respective column from the CSV parsed customers table header; at least most often:
#             the rep_code seems to be sorted to the end and gets the index behind the last CSV cell ... wtf?)
#     * [1] uba_key (CSV import label)
#     * [2] cell (value)
#     * [3] rory_typ
#     * [4] typ
#     * [5] internal_value
#     * [6] column_name (in database)


class CsvFile
  include ActiveModel::AttributeAssignment
  attr_accessor :uploaded_file, :zeilen, :messages, :trennzeichen, :zeichensatz, :errors, :zahlenformat
  attr_accessor :num_errors, :num_warnings, :temp_objects, :temp_objects_admin, :tabellen, :mode
  attr_reader :federal_states, :region_codes, :cities, :compliances, :compliance_classes, :usages, :avg_calc_methods
  attr_reader :catchments, :coordinate_systems, :groundwater_bodies, :methods, :object_types, :receiver_types, :water_types
  attr_reader :treatment_types, :water_bodies, :wrrl_sub_units, :river_basin_districts, :failure_reasons
  attr_reader :mh_water_bodies, :mh_groundwater_bodies, :mh_region_codes, :mh_cities
  attr_reader :mh_inspire_ids, :inspire_ids
  include ApplicationHelper

  require 'csv'
  # require 'ruby-prof'

  def initialize(data, user_id, mode, session_id, session)
    @user_id = user_id
    @messages = []
    @errors = []
    if data
      @uploaded_file = data[:uploaded_file]
    end
    @mode = mode
    @num_warnings = 0
    @num_errors = 0
    @tabellen = []
    @zeilen = []
    @session_id = session_id
    @session = session
    # Die temp_objects nehmen für plants und agglos Objekte auf, auf die ev. referenziert wird, die aber in der
    # Datenbank nicht gefunden werden können, da die Einfüge-Operation erst beim commit läuft.
    @temp_objects = {}
    # Und wenn das verwaltende Bundesland ein anderes ist, müssen diese Objekte auch unter der ID
    # des verwaltenden Bundesalandes gefunden werden können. Dazu wird irgendwann dieses Dictionary dienen:
    @temp_objects_admin = {}

    # ACHTUNG: Separater @developer_mode für Tabellenverarbeitung kommt in Tabelle.import weiter hinten!:
    @developer_mode = false

    @federal_states = FederalState.order(:position).to_a
    @region_codes = RegionCode.order(:position).to_a
    @cities = City.order(:position).to_a
    @compliances = Compliance.order(:position).to_a
    @compliance_classes = ComplianceClass.order(:position).to_a
    @usages = Usages.order(:position).to_a
    @avg_calc_methods = AvgCalcMethod.order(:position).to_a
    @catchments = Catchment.order(:position).to_a
    @coordinate_systems = CoordinateSystem.order(:position).to_a
    @groundwater_bodies = GroundwaterBody.order(:position).to_a
    @methods = MethodList.order(:position).to_a
    @object_types = ObjectType.order(:position).to_a
    @receiver_types = ReceiverType.order(:position).to_a
    @water_types = WaterType.order(:position).to_a
    @treatment_types = TreatmentType.order(:position).to_a
    @water_bodies = WaterBody.order(:position).to_a
    @inspire_ids = InspireId.order(:position).to_a
    @wrrl_sub_units = WrrlSubUnit.order(:position).to_a
    @river_basin_districts = RiverBasinDistrict.order(:position).to_a
    @failure_reasons = FailureReason.order(:position).to_a

    # Erzeuge @mh_water_bodies und @mh_groundwater_bodies
    self.create_mlthashes
  end

  # Importiere ein bereits mit process_csv_file vorverarbeitetes CSV in die Datenbank.
  def import
    @messages = []
    @errors = []
    @num_warnings = 0
    @num_errors = 0
    tab_index = 0
    @tabellen.each do |tabelle|
      tabelle.import(tab_index)
      tab_index += 1
    end
  end

  # clean up for marshalling
  def cleanupForSerialization
    @session = nil
    @uploaded_file = nil
    @tabellen.each {|tab| tab.cleanupForSerialization}
  end

  def reinsertSessionAfterDeserialization(s)
    @session = s
    @tabellen.each {|tab| tab.reinsertSessionAfterDeserialization(s)}
  end

  # Bearbeite ein hochgeladenes CSV - dies ist Schritt 1 des Import-„Miniwizards“.
  def process_csv_file
    index = 0
    if @uploaded_file.is_a? String
      ufile = File.new(@uploaded_file, 'r')
    else
      ufile = @uploaded_file.tempfile
    end

    first_line = ufile.gets
    @zeichensatz = Encoding.find(/^"?#Z([^"]+)$/u.match(first_line)[1]).name rescue "ISO-8859-1"
    ufile.rewind
    ufile.each do |zeile|
      zeile.force_encoding(@zeichensatz)
      u8_zeile = zeile.encode("utf-8")
      @zeilen[index] = [index += 1, u8_zeile]
    end
    @trennzeichen = self.trennzeichen_erkennen
    self.zahlenformat_erkennen
    self.kommentare_entfernen
    self.tabellen_finden

    # Typ der Tabellen bestimmen
    @tabellen.each {|tabelle| tabelle.bestimmen}

    # Sortieren nach Importreihenfolge (wg der ggf auftretenden Vorwärtsreferenzen)
    reihenfolge = {
        'Agglomeration' => 0,
        'Anlage' => 1,
        'Agglomeration-Anlage-Zuordnung' => 2,
        'Einleitstelle' => 3,
        'Bundeslandinformation' => 4
    }
    @tabellen.sort! {|a, b|
      aa = reihenfolge[a.object_singular] || 10
      bb = reihenfolge[b.object_singular] || 10
      aa <=> bb
    }

    # „verarbeiten“ sortiert Agglos, Plants und Einleits intern so, dass
    # stillgelegte zuerst verarbeitet werden... nach Stillegedatum. Damit
    # sollten Vorwärtsreferenzen auch dort nicht mehr vorkommen.
    @tabellen.each {|tab| tab.verarbeiten}
  end

  def kommentare_entfernen
    @zeilen.delete_if do |zeile|
      zeile[1] =~ /^#[^DZT].*/ || zeile[1].blank?
    end
  end

  def trennzeichen_erkennen
    @zeilen.each_with_index do |zeile, index|
      if /^"?#T(?<symbol>[^"]).*$/u =~ zeile[1]
        @zeilen.delete_at(index) # das ist gefährlich weil sich dadurch Indizes ändern, aber ruby kann das, und hier breakn wir ja danach
        return symbol unless symbol.empty?
        break
      end
    end
    return ';'
  end

  def zahlenformat_erkennen # soll de oder edv sein
    @zeilen.each_with_index do |zeile, index|
      if /^"?#F(?<format>[^"]+)$/ =~ zeile[1]
        @zahlenformat = format.strip.downcase
        @zeilen.delete_at(index) # das ist gefährlich weil sich dadurch Indizes ändern, aber ruby kann das, und hier breakn wir ja danach
        break
      end
    end
    @zahlenformat = 'de' unless @zahlenformat == 'edv'
  end

  # gehe über das @zeilen-Array, suche Blöcke von zu importierenden Daten (⇨ @tabellen)
  def tabellen_finden
    table_index = 0
    state = "seek"
    d_option = nil
    d_commands = true
    @zeilen.each do |nummer, zeile|
      # Semikolon-Müll am Ende der Zeilen entfernen:
      zeile.gsub!(/(;+)[\r\n]*$/u, '')
      if state == "columns"
        @tabellen[table_index] = Tabelle.new(self, @user_id, @mode, d_option, @session)
        @tabellen[table_index].columns_line = [nummer, zeile]
        state = "data"
        next
      end
      if state == "data"
        if d_option_match = /^"?#D(.).*/ui.match(zeile)
          state = "columns"
          d_option = d_option_match[1].downcase
          table_index = table_index + 1
        else
          @tabellen[table_index].lines.push([nummer, zeile])
        end
      end
      if state == "seek" && d_option_match = /^"?#D(.).*/ui.match(zeile)
        state = "columns"
        d_option = d_option_match[1].downcase
      end
    end

    if @tabellen[0].nil?
      # Vermutlich keine #Dp / #Da-Anweisungen
      state = "columns"
      d_option = "a"
      @zeilen.each do |nummer, zeile|
        if state == "columns"
          @tabellen[table_index] = Tabelle.new(self, @user_id, @mode, d_option, @session)
          @tabellen[table_index].columns_line = [nummer, zeile]
          state = "data"
          next
        end
        if state == "data"
          @tabellen[table_index].lines.push([nummer, zeile])
        end
      end
      d_commands = false
    end

    if @tabellen[0].nil?
      @messages << "<h1>Es wurden keine verarbeitbaren Tabellen in der Datei gefunden.</h1>"
      return nil
    else
      @messages << "Es wurden #{table_index + 1} Tabellen gefunden"
      @messages << "Zeichensatz: <b>#{@zeichensatz}</b>".html_safe
      @messages << "Trennzeichen: <b>#{@trennzeichen}</b>".html_safe
      @messages << "Zahlenformat: <b>#{@zahlenformat}</b>".html_safe
      if !d_commands
        @messages << "Da keine Steuerkommandos angegeben wurden, wird der Datensatz als Gesamtdatensatz (#Da) behandelt."
      end
      @messages << ""
      0.upto(table_index) do |i|
        @messages << "<b>".html_safe << @tabellen[i].csv_columns.html_safe << "</b>".html_safe if @developer_mode
        @tabellen[i].lines.each {|zeile| @messages << zeile.html_safe} if @developer_mode
        @messages << "<br />".html_safe if @developer_mode
      end
      return table_index + 1
    end
  end

  def create_upload_error
    @zeilen = []
    @zeilen.push("<br /><b>Fehler</b><br />".html_safe)
    @zeilen.push("Es muss erst eine existierende Datei ausgewählt werden, um diese hochladen zu können.<br />".html_safe)
  end

  # Wird derzeit nicht benutzt:
  def create_upload_message(messages)
    @zeilen = []
    @zeilen.push("<br /><b>Bericht</b><br />".html_safe)
    messages.each {|message| @zeilen.push(message.html_safe)}
  end

  class Tabelle
    attr_accessor :columns_line, :csv_columns, :type, :gen_columns
    attr_accessor :lines, :all_cells, :gen, :object_singular
    include CsvExportHelper
    include ApplicationHelper

    class ParseException < RuntimeError
    end

    def cleanupForSerialization
      @session = nil
    end

    def reinsertSessionAfterDeserialization(s)
      @session = s
    end

    def initialize (csv_object, user_id, mode, d_option, session)
      @import_eu_keys = false
      @lines = []
      @int_lines = []
      @csv = csv_object
      @tablename = nil
      @object_singular = nil
      @object_plural = nil
      @all_cells = []
      @gen_columns = []
      @ignored_columns = []
      @user_id = user_id
      @mode = mode
      @d_option = d_option
      @session = session
      @have_agg_pl_ref_id = false

      #@reporting_periods = ReportingPeriod.find(:first, :conditions => {:state => 1})
      @reporting_periods = ReportingPeriod.where(state: 1).take
      @date_from = @reporting_periods['date_from']
      @date_to = @reporting_periods['date_to']
      @key_report = @reporting_periods['key_report']

      @federal_states = @csv.federal_states
      @region_codes = @csv.region_codes
      @cities = @csv.cities
      @compliances = @csv.compliances
      @compliance_classes = @csv.compliance_classes
      @usages = @csv.usages

      @avg_calc_methods = @csv.avg_calc_methods
      @catchments = @csv.catchments
      @coordinate_systems = @csv.coordinate_systems
      @groundwater_bodies = @csv.groundwater_bodies
      @methods = @csv.methods
      @object_types = @csv.object_types
      @receiver_types = @csv.receiver_types
      @water_types = @csv.water_types
      @treatment_types = @csv.treatment_types
      @water_bodies = @csv.water_bodies
      @wrrl_sub_units = @csv.wrrl_sub_units
      @river_basin_districts = @csv.river_basin_districts
      @failure_reasons = @csv.failure_reasons

      @mh_water_bodies = @csv.mh_water_bodies
      @mh_groundwater_bodies = @csv.mh_groundwater_bodies
      @mh_inspire_ids = @csv.mh_inspire_ids
      @mh_region_codes = @csv.mh_region_codes
      @mh_cities = @csv.mh_cities

      @federal_state_reference_id = nil
      @federal_state_id = nil
      @federal_state_admin_id = nil
      @line_can_be_imported = true
      @can_be_imported = true
      @zeilennummer = 0
    end

    def import(tab_index)
      @developer_mode = false

      @csv.messages << "<br /><b>Import der Tabelle #{@tablename}</b>".html_safe
      @int_lines.each do |int_line, int_columns|
        line_index, zeilennummer = int_line
        linestring = ""
        int_columns.each do |int|
          column_index, uba_key, ext_val, rory_typ, typ, int_val, column_name = int
          if int_val == "fetchme_later"
            int_val = int_val + "/" + ext_val
          end
          linestring << column_name << "=" << int_val.to_s << ", "
        end
        @csv.messages << "Aus CSV: " << linestring if @developer_mode
      end

      # Hier werden die Haupt-Importroutinen aufgerufen:
      begin
        case @tablename
        when 'federal_state_info' then
          import_federal_state_info
        when 'agglomerations' then
          import_agglomerations
        when 'plants' then
          import_plants
        when 'discharge_points' then
          import_discharge_points
        when 'agglomerations_plants' then
          import_agglo_plants
        end
      rescue ParseException
        @csv.errors << "Beim Import der Tabelle #{@tablename} ist ein fataler Fehler aufgetreten.".html_safe
      rescue Exception => e
        raise e
      end
    end

    # ==============================

    def import_agglo_plants
      # Anspruchsvoll: Was machen wir mit den Agglomeration-Anlagen-Zuordnungen? Bekommen wir immer alle
      # oder manchmal auch nur einen Teil?
      # Die #D-Anweisung!
      # #Da: Für die hier übermittelten Agglomerationen und Anlagen haben wir jeweils alle und löschen für
      # genau diese (hier übermittelten) zuerst mal alle Zuordnungen (und Prozentzahlen)
      # #Dp ist: Wir haben nur einen Teil und fügen daher nur hinzu.
      # Wenn nichts angegeben: Wir haben alle (#Da). Das ist konform zum Handbuch-Anhang "Datenmodell"
      @d_option = "a" if !@d_option
      agg_ids = {}
      pl_ids = {}
      agg_ids_locked = {}
      pl_ids_locked = {}
      index = -1
      @int_lines.each do |int_line, int_columns|
        index += 1
        if !agglo_column = int_columns.detect {|c| c[4] == 4}
          raise ParseException.new
        end
        if !plant_column = int_columns.detect {|c| c[4] == 8}
          # Es fehlt die Pointer-Reference (typ (c[4]) = 8), in diesem Fall also pl_ID
          raise ParseException.new, "Agglo-Plant-Zuordnung: Es fehlt die Pointer-Reference (typ (c[4]) = 8), in diesem Fall also pl_ID"
        end
        if !fed_state_column = int_columns.detect {|c| c[4] == 3}
          raise ParseException.new
        end
        if !fed_state_ref_column = int_columns.detect {|c| c[4] == 6}
          raise ParseException.new
        end
        agglo_column_index = int_columns.index(agglo_column)
        plant_column_index = int_columns.index(plant_column)
        agg_key = agglo_column[2]
        agg_id = agglo_column[5]
        pl_key = plant_column[2]
        pl_id = plant_column[5]
        federal_state_id = fed_state_column[5]
        federal_state_ref_id = fed_state_ref_column[5]
        if agg_id == 'fetchme_later'
          agg_lines, error = self.detect_agglomeration(federal_state_id, agg_key, nil, nil, false) # hier kein EU-Key vorhanden, leider
          if agg_lines
            id = agg_lines[0]['id']
            # @csv.messages << @int_lines[index][1][agglo_column_index].to_s
            @int_lines[index][1][agglo_column_index][5] = id
            # @csv.messages << @int_lines[index][1][agglo_column_index].to_s
            agg_id = id
          else
            msg = "<b>Fehler: Die Agglomeration '#{agg_key}' wurde nicht gefunden.</b>"
            @csv.errors << msg
            return nil, 'replace', nil, agg_key, pl_key
          end
        end
        if pl_id == 'fetchme_later'
          pl_lines, error = self.detect_plant(federal_state_ref_id, pl_key, nil, nil, false) # wir haben hier keinen EU-Key, leider.
          if pl_lines
            id = pl_lines[0]['id']
            # @csv.messages << @int_lines[index][1][plant_column_index].to_s
            @int_lines[index][1][plant_column_index][5] = id
            # @csv.messages << @int_lines[index][1][plant_column_index].to_s
            pl_id = id
          else
            msg = "<b>Fehler: Die Anlage '#{pl_key}' wurde nicht gefunden.</b>"
            @csv.errors << msg
            return nil, 'replace', nil, agg_key, pl_key
          end
        end

        locked = self.detect_lock_agg_pl(agg_id, pl_id, agg_ids_locked, pl_ids_locked)
        unless locked
          # Dieses Gehuddel dient vor allem der Füllung von agg_ids und pl_ids, um die Zuordnungszeilen ggf. löschen zu können:
          # (Wir nutzen aber auch die Ersetzung von 'fetchme_later' durch korrekte ids)
          agg_ids[agg_id] ||= 1
          pl_ids[pl_id] ||= 1
        end
        @csv.messages << "A-P: agg_key = #{agg_key}, agg_id = #{agg_id}, pl_key = #{pl_key}, pl_id = #{pl_id}, locked = #{locked.to_s}" if @developer_mode
      end

      # 2015-10-28, nbw: Loeschen von AGG-KA-Zuordnungen nur noch im Modus 'overkill'
      if @d_option == "a" && @mode == "overkill"
        agg_ids.keys.each do |agg_id|
          statement = "delete from agglomerations_plants where agglomeration_id = ?"
          ActiveRecord::Base.execute_sql(statement, agg_id)
        end
        pl_ids.keys.each do |pl_id|
          statement = "delete from agglomerations_plants where plant_id = ?"
          ActiveRecord::Base.execute_sql(statement, pl_id)
        end
      end

      index = -1
      @int_lines.each do |int_line, int_columns|
        index += 1
        agglo_column = int_columns.detect {|c| c[4] == 4}
        plant_column = int_columns.detect {|c| c[4] == 8}
        fed_state_column = int_columns.detect {|c| c[4] == 3}
        fed_state_ref_column = int_columns.detect {|c| c[4] == 6}
        agg_key = agglo_column[2]
        agg_id = agglo_column[5]
        pl_key = plant_column[2]
        pl_id = plant_column[5]
        if agg_ids_locked[agg_id] && pl_ids_locked[pl_id]
          @csv.messages << "Agglomerations-Anlagen-Verbindung #{agg_key}/#{pl_key} im Modus 'locked' bearbeitet. Beide sind gesperrt."
          next
        elsif agg_ids_locked[agg_id]
          @csv.messages << "Agglomerations-Anlagen-Verbindung #{agg_key}/#{pl_key} im Modus 'locked' bearbeitet. Agglomeration ist freigegeben und daher gesperrt."
          next
        elsif pl_ids_locked[pl_id]
          @csv.messages << "Agglomerations-Anlagen-Verbindung #{agg_key}/#{pl_key} im Modus 'locked' bearbeitet. Anlage ist freigegeben und daher gesperrt."
          next
        end

        federal_state_id = fed_state_column[5]
        federal_state_ref_id = fed_state_ref_column[5]

        statement, statement_mode, id = self.build_agglo_plant_statement(@mode, int_columns, agg_id, pl_id, agg_key, pl_key)
        @csv.messages << agg_key.to_s + " : " + pl_key.to_s + " : " + statement_mode.to_s + " : " + id.to_s + " : " + statement.to_s + " ." if @developer_mode
        if statement
          @csv.messages << "Agglomerations-Anlagen-Verbindung #{agg_key}/#{pl_key} im Modus #{statement_mode} bearbeitet"
        else
          @csv.messages << "Agglomerations-Anlagen-Verbindung #{agg_key}/#{pl_key} im Modus #{statement_mode}: Es mussten keine Werte aktualisiert werden"
        end
      end
    end

    def detect_lock_agg_pl(agg_id, pl_id, agg_ids_locked, pl_ids_locked)
      lock = false
      statement = "SELECT locked FROM agglomerations WHERE id=?"
      zeilen = ActiveRecord::Base.execute_sql(statement, agg_id)
      if zeilen.num_tuples > 0
        if zeilen[0]['locked'].to_i == 1
          lock = true
          agg_ids_locked[agg_id] ||= 1
        end
      end
      statement = "select locked from plants where id = ?"
      zeilen = ActiveRecord::Base.execute_sql(statement, pl_id)
      if zeilen.num_tuples > 0
        if zeilen[0]['locked'].to_i == 1
          lock = true
          pl_ids_locked[pl_id] ||= 1
        end
      end
      return lock
    end

    def build_agglo_plant_statement(mode, int_columns, agg_id, pl_id, agg_key, pl_key)
      id = nil

      # 2015-10-28, nbw: Zuordnungen werden nur noch im Modus 'overkill' geloescht. Wir versuchen
      # also in den beiden anderen Modi 'overwrite' und 'append' vorhandene Beziehungen anzupassen

      if @d_option == "p" || (@mode != "overkill" && @d_option == "a")
        statement = "SELECT * FROM agglomerations_plants WHERE agglomeration_id = ? AND plant_id = ?"
        aps = ActiveRecord::Base.execute_sql(statement, agg_id, pl_id)
        if aps.num_tuples() > 0
          id = aps[0]['id']
          val = aps[0]['agg_percent_plant']
          if @mode == "overwrite" || @mode == "overkill" || val.nil? || val.blank?
            statement = "UPDATE agglomerations_plants SET agg_percent_plant = ? WHERE id = ?"
            percent_column = int_columns.detect {|int| int[6] == 'agg_percent_plant'}
            if percent_column
              ActiveRecord::Base.execute_sql(statement, percent_column[5], id)
              return statement, 'update', id
            end
          end
          return nil, nil, nil
        else
          skind = 'insert'
        end
      else
        skind = 'clearall / insert'
      end

      statement = "INSERT INTO agglomerations_plants (state, date_from, time_from, date_to, time_to, date_create, time_create, " +
          "owner_id, creator_id, operation_id, process, agglomeration_id, plant_id, agg_percent_plant)
        VALUES (0, ?, 0, ?, 240000, ?, ?,?, ?, ?, ?, ?, ?, ?) RETURNING id"
      percent_column = int_columns.detect {|int| int[6] == 'agg_percent_plant'}
      percent = percent_column.nil? ? nil : percent_column[5]
      begin
        ids = ActiveRecord::Base.execute_sql(statement, @date_from, @date_to, self.now_date_number \
            , self.now_time_number, @user_id, @user_id, self.get_operation_id_by_mode(mode), @user_id, agg_id, pl_id, percent)
        id = ids[0]['id']
        return statement, skind, id
      rescue Exception => wumm
        @csv.errors << "Agglomerations-Anlagen-Zuordnung #{agg_key} / #{pl_key} war doppelt. Es wurde die erste eingefügt."
        return statement, 'duplicate suppression', nil
      end
    end

    # ==============================

    def import_federal_state_info
      @int_lines.each do |int_line, int_columns|
        if !federal_column = int_columns.detect {|c| c[4] == 3}
          raise ParseException.new
        end
        fs_id = federal_column[5]
        fs_lines = self.detect_federal_state_info(fs_id)
        if fs_lines && @developer_mode
          # @csv.messages << "DB-Spalten: " << fs_lines.fields.join(", ")
          # fs_lines.each { |cell| print cell, " = ", cell[cell], "\n" }
        end
        statement, statement_mode, id, fs_key = self.build_fs_statement(@mode, int_columns, fs_lines, fs_id)
        @csv.messages << fs_key.to_s + " : " + statement_mode.to_s + " : " + id.to_s + " : " + statement.to_s + " ." if @developer_mode
        if statement
          @csv.messages << "Bundesland-Info #{fs_key} im Modus #{statement_mode} bearbeitet"
        else
          if statement_mode == 'locked'
            @csv.messages << "Bundesland-Info #{fs_key} im Modus #{statement_mode}: Bundesland-Info ist gesperrt, da bereits freigegeben"
          else
            @csv.messages << "Bundesland-Info #{fs_key} im Modus #{statement_mode}: Es mussten keine Werte aktualisiert werden"
          end
        end
      end
    end

    def build_fs_statement(mode, int_columns, fs_lines, fs_id)
      id = nil
      if fs_lines
        locked = fs_lines[0]['locked']
        return nil, 'locked', nil, self.get_federal_key_by_id(fs_id) if locked.to_i == 1
        cols = []
        vals = []
        fs_lines.fields.each do |fieldname|
          val = fs_lines[0][fieldname]
          id = fs_lines[0]['id']
          int_columns.each do |int|
            column_index, uba_key, ext_val, rory_typ, typ, int_val, column_name = int
            if column_name != fieldname
              next
            end
            if @mode == "overwrite" || @mode == "overkill" || val.nil? || val.blank?
              cols << "#{column_name} = ?"
              vals << int_val
            end
          end
        end
        unless cols.empty?
          statement = "update federal_state_info set #{cols.join(', ')} where id = ?"
          ActiveRecord::Base.execute_sql(statement, *vals, id)
          @csv.messages << statement if @developer_mode
          return statement, 'update', id, self.get_federal_key_by_id(fs_id)
        else
          @csv.messages << "update betraf keine Spalten: Nichts zu tun" if @developer_mode
          return nil, 'update', nil, self.get_federal_key_by_id(fs_id)
        end
      else
        @csv.errors << "federal_state_info-Eintrag für #{self.get_federal_key_by_id(fs_id)}, id=#{fs_id} existiert nicht mehr!"
        return nil, 'insert', nil, self.get_federal_key_by_id(fs_id)
      end
    end

    def detect_federal_state_info(federal_state_id)
      statement = "SELECT * FROM federal_state_info " +
          "WHERE federal_state_id = ? AND rep_code = ?"
      zeilen = ActiveRecord::Base.execute_sql(statement, federal_state_id, @key_report)
      if zeilen.num_tuples > 0
        return zeilen
      end
      @csv.messages << "Keine Bundesland-Info mit: " << statement if @developer_mode
      return nil
    end

    # =============================================================

    def import_discharge_points
      @int_lines.each do |int_line, int_columns|
        if !federal_column = int_columns.detect {|c| c[4] == 3}
          raise ParseException.new
        end
        if !dp_key_column = int_columns.detect {|c| c[4] == 4}
          raise ParseException.new
        end
        fs_id = federal_column[5]
        dp_key = dp_key_column[5]
        if federal_ref_column = int_columns.detect {|c| c[4] == 6} # see →types, above
          fs_ref_id = federal_ref_column[5]
        else
          fs_ref_id = fs_id
        end

        # get EU key
        int_columns_eu_key = nil
        if int_columns_eu_key = int_columns.detect {|c| c[4] == 7} # see →types, above
          dp_eu_key = int_columns_eu_key[5] # [5]=internal value, here: value of the EU key.
        else
          dp_eu_key = nil
        end

        # Es kann sein, dass der db_key auf ein anderes Objekt verweist, als der dp_eu_key.
        # Das stellt detect_discharge_point fest und gibt dann einen Fehler mit zurück:
        error = nil
        dp_lines, error = self.detect_discharge_point(fs_id, dp_key, dp_eu_key, int_columns_eu_key, true)
        if !error.nil?
          next
        end
        if dp_lines && @developer_mode
          # @csv.messages << "DB-Spalten: " << dp_lines.fields.join(", ")
          # dp_lines.each { |cell| print cell, " = ", cell[cell], "\n" }
        end
        statement, statement_mode, id, dp_key = self.build_dp_statement(@mode, int_columns, dp_lines, fs_id, fs_ref_id, int_line)
        @csv.messages << dp_key.to_s + " : " + statement_mode.to_s + " : " + id.to_s + " : " + statement.to_s + " ." if @developer_mode
        if statement
          @csv.messages << "Einleitstelle #{dp_key} im Modus #{statement_mode} bearbeitet"
        else
          if statement_mode == 'locked'
            @csv.messages << "Einleitstelle #{dp_key} im Modus #{statement_mode}: Einleitstelle ist gesperrt, da bereits freigegeben"
          elsif statement_mode == 'error'
            @csv.messages << "Einleitstelle #{dp_key}: Der Import dieser Einleitstelle konnte nicht durchgeführt werden"
          else
            @csv.messages << "Einleitstelle #{dp_key} im Modus #{statement_mode}: Es mussten keine Werte aktualisiert werden"
          end
        end
      end
    end

    def check_eu_key(object_type, int_val)
      # object_type is one of 'DP', 'AG', 'PL'
      # if line =~ /.*Stoff.*/ui
      match = int_val.match /DE(..)_(.+)/
      if match.nil?
        return int_val, "Fehler. Syntax des EU-Key für Objekttyp #{object_type} ist ungültig: #{int_val}"
      end
      if match[1] != object_type
        new_eu_key = "DE#{object_type}_#{match[2]}"
        @csv.messages << "#{object_type}-EU-Schlüssel #{int_val} korrigiert zu #{new_eu_key}"
        return new_eu_key
      end
      return int_val, nil
    end

    def build_dp_statement(mode, int_columns, dp_lines, fs_id, fs_ref_id, int_line)
      # Die int_columns[] werden zusammen mit dem Zeileninhalt der CSV-Datei in der Class Method Tabelle.verarbeiten (Zeile ≈1560)
      # gefüllt. Sie enthalten:
      #   column_index, uba_key, cell, rory_typ, typ, internal_value, column_name

      id = nil
      successors_column = nil
      dp_key = nil
      dp_eu_key = nil
      state_column = nil
      zeilennummer = int_line[1]
      if dp_lines    # Es gibt den Datensatz schon
        locked = dp_lines[0]['locked']
        return nil, 'locked', nil, dp_lines[0]['dp_key'] if locked.to_i == 1
        cols = []
        vals = []
        dp_lines.fields.each do |fieldname|
          val = dp_lines[0][fieldname]
          id = dp_lines[0]['id']
          int_columns.each do |int|
            column_index, uba_key, ext_val, rory_typ, typ, int_val, column_name = int
            if column_name == 'dp_key'
              dp_key = int_val
            elsif column_name == 'dp_eu_key'
              dp_eu_key = int_val
            end
          end
        end
        dp_lines.fields.each do |fieldname|
          next if fieldname == 'dp_eu_key' # tlu, KORW-18: eu_keys nicht ändern beim Import
          # next if fieldname == 'agg_pl_ref_id' # tba, 14-04-23: Pseudofeld, nicht tatsächlich INSERTen.
          val = dp_lines[0][fieldname]
          id = dp_lines[0]['id']
          int_columns.each do |int|
            column_index, uba_key, ext_val, rory_typ, typ, int_val, column_name = int
            if column_name != fieldname
              next
            end
            if @mode == "overwrite" || @mode == "overkill" || val.nil? || val.blank?
              if column_name == 'plant_id' && int_val == 'fetchme_later'
                pl_lines, error = self.detect_plant(fs_ref_id, ext_val, nil, nil, false)
                int_val = pl_lines[0]['id']
              elsif column_name == 'plant_id'
                # column_index, uba_key, ext_val, rory_typ, typ, int_val, column_name
                # puts("#03.00: <#{int}>")
                pl_lines, error = self.detect_plant(fs_ref_id, ext_val, nil, nil, false)
                # TODO: Abfangen, dass die Anlage nicht gefunden wird, z. B. weil der EU-Key verwendet wurde:
                if pl_lines.nil?
                  puts "Die Anlage (Land-ID: #{fs_ref_id}, referenzierte pl_id: #{ext_val}) wurde in detect_plants nicht gefunden"
                  @csv.errors << "<b>Fehler (Zeile #{zeilennummer}): Die referenzierte Anlage (Land-ID: #{fs_ref_id}, pl_id: #{ext_val}) wurde nicht gefunden.</b>".html_safe
                  @csv.errors << "<b>Die Einleitstelle #{dp_key} (dp_eu_key: #{dp_eu_key}) kann nicht importiert werden.</b>".html_safe
                  @csv.num_errors = @csv.num_errors + 1
                  return nil, 'error', nil, dp_key
                end
                int_val = pl_lines[0]['id']
                # puts("#01.10: <#{int_val}>")
                # puts("#02.10: <#{pl_lines[0]}>")
              end
              if column_name == 'dp_eu_key' && !@import_eu_keys
                puts "Fataler Fehler droht! Update. Satz gibt es. Nicht akzeptieren, überspringen!"
              else
                cols << "#{column_name} = ?"
                vals << int_val
              end
            else
              # puts "Wenn mode = append, dann hier. Keine cols, keine vals. Weil es den Datensatz schon gibt."
            end
            dp_key = ext_val if column_name == 'dp_key'
          end
        end
        # successors haben hier keine Spalte! Trotzdem könnten sie übermittelt worden sein:
        successors_column = int_columns.detect {|int| int[4] == 5}
        self.insert_dp_successors(mode, id, successors_column, fs_id) if successors_column
        # Unabhängig von den successors: Wenn andere Daten übermittelt wurden, update ausführen:
        unless cols.empty?
          statement = "UPDATE discharge_points SET #{cols.join(', ')} WHERE id=?"
          ActiveRecord::Base.execute_sql(statement, *vals, id)
          @csv.messages << statement if @developer_mode
          return statement, 'update', id, dp_key
        else
          @csv.messages << "update betraf keine Spalten: Nichts zu tun" if @developer_mode
          return nil, 'update', nil, dp_key
        end
      else   # Der Datensatz ist neu
        cols = []
        vals = []
        int_columns.each do |int|
          column_index, uba_key, ext_val, rory_typ, typ, int_val, column_name = int
          if column_name == 'successors'
            successors_column = int
            next
          end
          dp_key = ext_val if column_name == 'dp_key'
          next if column_name == 'bl_code_ref'
          next if column_name == 'void'
          if column_name == 'plant_id' && int_val == 'fetchme_later'
            plant_ids, error = detect_plant(fs_ref_id, ext_val, nil, nil, false)
            int_val = plant_ids[0]['id']
          elsif column_name == 'plant_id'
            # column_index, uba_key, ext_val, rory_typ, typ, int_val, column_name
            pl_lines, error = self.detect_plant(fs_ref_id, ext_val, nil, nil, false)
            if pl_lines
              int_val = pl_lines[0]['id']
            else
              @csv.messages << "Einleitstelle #{dp_key} verweist auf eine derzeit unbekannte Anlage #{int[2]} und wird daher nicht importiert"
              return nil, 'error', id, dp_key
            end
          elsif column_name == 'dp_eu_key' && !@import_eu_keys
            # puts "Fataler Fehler droht."
            # next geht nicht, weil Datensatz neu ist! EU-Key muss geschrieben werden. Aber syntaktisch sauberer EU-Key!
            int_val, error = check_eu_key('DP', int_val)
            if !error.nil?
              @csv.messages << "Einleitstelle #{dp_key} hat einen defekten EU-Key (#{int_val}) und wird daher nicht importiert"
              return nil, 'error', id, dp_key
            end
          end
          if int[3] == 9
            int_val = int_val.to_f
          elsif int[3] == 8
            int_val = int_val.to_i
          end
          state_column = true if uba_key == "dp_state"
          cols << column_name
          vals << int_val
        end
        if !state_column
          cols << 'dp_state_active'
          vals << 1
        end
        statement = "INSERT INTO discharge_points (state, date_from, time_from, date_to, time_to, date_create, time_create,
          owner_id, creator_id, operation_id, process, #{cols.join(', ')})
          VALUES (0, ?, 0, ?, 240000, ?, ?, ?, ?, ?, ?, #{cols.collect {|v| '?'}.join(', ')}) RETURNING id"
        @csv.messages << statement if @developer_mode
        ids = ActiveRecord::Base.execute_sql(statement, @date_from, @date_to, self.now_date_number, self.now_time_number, @user_id \
                , @user_id, self.get_operation_id_by_mode(mode), @user_id, *vals)
        id = ids[0]['id']
        self.insert_dp_successors(mode, id, successors_column, fs_id) if successors_column
        return statement, 'insert', id, dp_key
      end
    end

    def insert_dp_successors(mode, id, successors_column, fs_id)
      # puts successors_column.inspect
      successors = successors_column[5]
      @csv.messages << "\nDischarge Point Successors (id/succ): " + id + " / " + successors.join(", ") if @developer_mode
      if !successors || successors.blank?
        return
      end
      if mode == "append"
        # Testen, ob Predecessors vorhanden sind. Dann machen wir nicht.
        statement = "SELECT id FROM discharge_points_successors WHERE discharge_point_succ_id=?"
        ids = ActiveRecord::Base.execute_sql(statement, id)
        if ids.num_tuples() > 0
          message = "Predecessors gefunden. Mode ist append. ids in discharge_points_successors: "
          ids.each {|line| message << line['id'] << ", "}
          @csv.messages << message[0, message.length - 2] if @developer_mode
          return
        end
      end

      ori_succs = []
      successors_column[2].strip.split(',').each do |key|
        key.strip!
        next if key.length == 0
        ori_succs << key
      end

      index = -1
      successors.each do |successor|
        index += 1
        if successor == 'fetchme_later'
          sid = self.identify_dp_successor_id(fs_id, ori_succs[index])
        else
          sid = successor
        end
        next if !sid
        statement = "SELECT id FROM discharge_points_successors WHERE discharge_point_pre_id = ? AND discharge_point_succ_id = ?"
        ids = ActiveRecord::Base.execute_sql(statement, sid, id)
        next if ids.num_tuples() > 0
        statement = "INSERT INTO discharge_points_successors (state, date_from, time_from, date_to, time_to, date_create, time_create, " +
            "owner_id, creator_id, operation_id, process, discharge_point_pre_id, discharge_point_succ_id) " +
            "VALUES (0, ?, 0, ?, 240000, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING id"
        ids = ActiveRecord::Base.execute_sql(statement, @date_from, @date_to, self.now_date_number, self.now_time_number, \
            @user_id, @user_id, self.get_operation_id_by_mode(mode), @user_id, sid, id)
      end
    end

    def identify_dp_successor_id(fs_id, ori_succ)
      dp_lines, error = detect_discharge_point(fs_id, ori_succ, nil, nil, false)
      if !error.nil?
        return nil
      end
      if dp_lines
        id = dp_lines[0]['id']
        return id
      else
        msg = "<b>Fehler: Bei der Ermittlung der id der Vorgänger-Einleitstelle '#{ori_succ}' wurde der Vorgänger nicht gefunden.</b>"
        @csv.errors << msg
        return nil
      end
    end

    def detect_discharge_point(federal_state_id, dp_key, dp_eu_key, int_columns_eu_key, message = false)
      # bevorzuge den EU-Schlüssel (KORW-43)
      dp_eu_key_provided = (!dp_eu_key.nil? && dp_key != dp_eu_key && !int_columns_eu_key.nil? && !int_columns_eu_key[2].nil?)
      # Wenn der dp_key den Inhalt eines dp_eu_keys hat, ein dp_eu_key aber nicht angegeben ist,
      # interpretiere das so, als wäre ein dp_eu_key angegeben worden:
      if /DEDP_.+/ui =~ dp_key && dp_eu_key.nil?
        dp_eu_key = dp_key
      end
      eu_key_not_found = false
      fs_land_key_not_found = false
      id_by_eu_key = nil
      id_by_fs_and_key = nil
      if dp_eu_key && !int_columns_eu_key.nil? && !int_columns_eu_key[2].nil?
        statement = "SELECT * FROM discharge_points " +
            "WHERE (federal_state_id = ? OR handled_by_federal_state_id = ?) AND dp_eu_key=? AND rep_code=?"
        zeilen_by_eu_key = ActiveRecord::Base.execute_sql(statement, federal_state_id, federal_state_id, dp_eu_key, @key_report)
        if zeilen_by_eu_key.num_tuples > 0
          zeile_by_eu_key = zeilen_by_eu_key[0]
          id_by_eu_key = zeile_by_eu_key['id']
        else
          eu_key_not_found = true
        end
      end
      if dp_key
        statement = "SELECT * FROM discharge_points " +
            "WHERE (federal_state_id = ? OR handled_by_federal_state_id = ?)" +
            "  AND dp_key = ? " +
            "  AND rep_code = ?"
        zeilen_by_fs_and_key = ActiveRecord::Base.execute_sql(statement, federal_state_id, federal_state_id, dp_key, @key_report)
        if zeilen_by_fs_and_key.num_tuples > 0
          zeile_by_fs_and_key = zeilen_by_fs_and_key[0]
          id_by_fs_and_key = zeile_by_fs_and_key['id']
        else
          fs_land_key_not_found = true
        end
      end
      if message
        # Fall  land-key gefunden    eu-key geliefert    eu-key gefunden   Meldung
        #   1        false               false                 -            true
        #   2        false               true                false          true
        #   3        false               true                true           true
        #   4        true                false                 -              -
        #   5        true                true                false          true
        #   6        true                true                true             -
        if fs_land_key_not_found   # Fall 1..3
          if dp_eu_key_provided    # Fall 2 oder 3
            if eu_key_not_found    # Fall 2
              @csv.messages << "Die Einleitstelle mit EU-Schlüssel #{dp_eu_key} oder Land-(#{federal_state_id})-Schlüssel „#{dp_key}“ ist noch nicht in der Datenbank vorhanden."
            else                   # Fall 3
              @csv.messages << "Die Einleitstelle mit Land-(Nr. #{federal_state_id})-Schlüssel „#{dp_key}“ wurde über den EU-Schlüssel #{dp_eu_key} gefunden."
            end
          else                     # Fall 1
            @csv.messages << "Die Einleitstelle mit Land-(Nr. #{federal_state_id})-Schlüssel „#{dp_key}“ ist noch nicht in der Datenbank vorhanden."
          end
        else                       # Fall 4..6, land-key gefunen
          if dp_eu_key_provided    # Fall 5 oder 6
            if eu_key_not_found    # Fall 5
              self.add_warning("Der Einleitstellen-EU-Schlüssel #{dp_eu_key} könnte falsch sein, denn er wurde nicht gefunden, jedoch ist der Land-(#{federal_state_id})-Schlüssel „#{dp_key}“ in der Datenbank vorhanden.".html_safe)
            else                   # Fall 6
              # Keine Meldung; Land-Key und EU-Key geliefert und gefunden
            end
          else                     # Fall 4
            # Keine Meldung; Land-Key gefunden, EU-Key nicht geliefert
          end
        end
      end

      if !id_by_eu_key.nil? && !id_by_fs_and_key.nil? && id_by_eu_key != id_by_fs_and_key
        # EU-Schlüssel und Land-Schlüssel zeigen auf unterschiedliche Objekte
        msg = "<b>Fehler (DP): EU-Schlüssel (#{dp_eu_key}) und Land-(#{federal_state_id})-Schlüssel (#{dp_key}) zeigen auf unterschiedliche Einleitstellen.</b>".html_safe
        @csv.errors << msg
        return nil, msg
      end

      if !zeilen_by_fs_and_key.nil? && zeilen_by_fs_and_key.num_tuples > 0
        return zeilen_by_fs_and_key, nil
      elsif !zeilen_by_eu_key.nil? && zeilen_by_eu_key.num_tuples > 0
        return zeilen_by_eu_key, nil
      end
      @csv.messages << "Keine Einleitstelle mit: " << statement if @developer_mode
      return nil, nil
    end

    # ================================================

    def import_plants
      # @int_lines.each do |line|
      #     line.each do |column|
      #
      #     end
      # end

      @int_lines.each do |int_line, int_columns|
        # puts @int_lines.to_s
        if !federal_column = int_columns.detect {|c| c[4] == 3}
          raise ParseException.new
        end
        if !pl_key_column = int_columns.detect {|c| c[4] == 4}
          raise ParseException.new
        end
        fs_id = federal_column[5]
        pl_key = pl_key_column[5]

        # get EU key
        int_columns_eu_key = nil
        if int_columns_eu_key = int_columns.detect {|c| c[4] == 7} # see →types, above
          pl_eu_key = int_columns_eu_key[5] # [5]=internal value, here: value of the EU key.
        else
          pl_eu_key = nil
        end

        error = nil
        pl_lines, error = self.detect_plant(fs_id, pl_key, pl_eu_key, int_columns_eu_key, true)
        if !error.nil?
          next
        end
        if pl_lines && @developer_mode
          @csv.messages << "DB-Spalten: " << pl_lines.fields.join(", ")
          pl_lines.each {|cell| print cell, " = ", cell[cell], "\n"}
        end

        pl_key_backup = pl_key
        statement, statement_mode, id, pl_key = self.build_pl_statement(@mode, int_columns, pl_lines, fs_id)

        if nil && (!pl_key || pl_key.strip == '')
          puts "<#{statement}>, <#{statement_mode}>, <#{id}>, <#{pl_key}>"
          puts "pl_key fehlt! (War vorher: #{pl_key_backup}"
          i = 1
        end

        @csv.messages << pl_key.to_s + " : " + statement_mode.to_s + " : " + id.to_s + " : " + statement.to_s + " ." if @developer_mode
        if statement
          @csv.messages << "Anlage #{pl_key} (EU: #{pl_eu_key}) im Modus #{statement_mode} bearbeitet"
        else
          if statement_mode == 'locked'
            @csv.messages << "Anlage #{pl_key} (EU: #{pl_eu_key}) im Modus #{statement_mode}: Anlage ist gesperrt, da bereits freigegeben"
          else
            @csv.messages << "Anlage #{pl_key} (EU: #{pl_eu_key}) im Modus #{statement_mode}: Es mussten keine Werte aktualisiert werden"
          end
        end
      end
    end

    def build_pl_statement(mode, int_columns, pl_lines, fs_id)
      id = nil
      successors_column = nil
      state_column = nil
      pl_key = nil
      if pl_lines
        # pl_lines sind
        locked = pl_lines[0]['locked']
        return nil, 'locked', nil, pl_lines[0]['pl_key'] if locked.to_i == 1
        cols = []
        vals = []
        pl_lines.fields.each do |fieldname|
          next if fieldname == 'pl_eu_key' # tlu, KORW-18: eu_keys nicht ändern beim Import
          val = pl_lines[0][fieldname]
          id = pl_lines[0]['id']
          int_columns.each do |int|
            column_index, uba_key, ext_val, rory_typ, typ, int_val, column_name = int
            if column_name != fieldname
              next
            end
            if @mode == "overwrite" || @mode == "overkill" || val.nil? || val.blank?
              cols << "#{column_name} = ?"
              vals << int_val
            end
            pl_key = ext_val if column_name == 'pl_key'
          end
        end
        # successors haben hier keine Spalte! Trotzdem könnten sie übermittelt worden sein:
        successors_column = int_columns.detect {|int| int[4] == 5}
        self.insert_pl_successors(mode, id, successors_column, fs_id) if successors_column
        # Unabhängig von den successors: Wenn andere Daten übermittelt wurden, update ausführen:
        unless cols.empty?

          # [jwe: 2018-04-06, Rechtschreibung mlt 2022-03-15]
          # Wir filtern die pl_eu_key-Spalte raus, damit beim Update keine EU-Keys geändert werden
          # können, weil das nicht passieren soll.
          # Eingebaut, weil teilweise EU-Keys in der Datenbank enthalten sind, die sich vom pl_key dahingehend unterscheiden,
          # dass sie eine führende "0" haben. Diese wird aber an anderer Stelle in diesem Programm, wo die EU Keys
          # generiert werden, nicht mit erzeugt.
          index = cols.find do |val|
            val == "pl_eu_key"
          end

          cols.delete_at(index) if index
          vals.delete_at(index) if index

          statement = "UPDATE plants SET #{cols.join(', ')} WHERE id = ?"
          ActiveRecord::Base.execute_sql(statement, *vals, id)
          @csv.messages << statement if @developer_mode
          return statement, 'update', id, pl_key
        else
          @csv.messages << "update betraf keine Spalten: Nichts zu tun" if @developer_mode
          return nil, 'update', nil, pl_key
        end
      else
        cols = []
        vals = []
        int_columns.each do |int|
          column_index, uba_key, ext_val, rory_typ, typ, int_val, column_name = int
          if column_name == 'successors'
            successors_column = int
            next
          end
          state_column = true if uba_key == "pl_state"
          pl_key = ext_val if column_name == 'pl_key'
          cols << column_name
          vals << int_val
        end
        if !state_column
          statement << 'pl_state_active'
          vals << 1
        end
        statement = "INSERT INTO plants (state, date_from, time_from, date_to, time_to, date_create, time_create,
          owner_id, creator_id, operation_id, process, #{cols.join(', ')})
          VALUES (0, ?, 0, ?, 240000, ?, ?, ?, ?, ?, ?, #{cols.collect {|v| '?'}.join(', ')}) RETURNING id"
        @csv.messages << statement if @developer_mode
        ids = ActiveRecord::Base.execute_sql(statement, @date_from, @date_to, self.now_date_number, self.now_time_number, @user_id, @user_id \
                , self.get_operation_id_by_mode(mode), @user_id, *vals)
        id = ids[0]['id']
        self.insert_pl_successors(mode, id, successors_column, fs_id) if successors_column
        return statement, 'insert', id, pl_key
      end
    end

    def insert_pl_successors(mode, id, successors_column, fs_id)
      # puts successors_column.inspect
      successors = successors_column[5]
      @csv.messages << "\nPlants Successors (id/succ): " + id + " / " + successors.join(", ") if @developer_mode
      if !successors || successors.blank?
        return
      end
      if mode == "append"
        # Testen, ob Predecessors vorhanden sind. Dann machen wir nicht.
        statement = "SELECT id FROM plants_successors WHERE plant_succ_id = ?"
        ids = ActiveRecord::Base.execute_sql(statement, id)
        if ids.num_tuples() > 0
          message = "Predecessors gefunden. Mode ist append. ids in plants_successors: "
          ids.each {|line| message << line['id'] << ", "}
          @csv.messages << message[0, message.length - 2] if @developer_mode
          return
        end
      end

      ori_succs = []
      successors_column[2].strip.split(',').each do |key|
        key.strip!
        next if key.length == 0
        ori_succs << key
      end

      index = -1
      successors.each do |successor|
        index += 1
        if successor == 'fetchme_later'
          sid = self.identify_pl_successor_id(fs_id, ori_succs[index])
        else
          sid = successor
        end
        next if !sid
        statement = "select id from plants_successors where plant_pre_id = ? and plant_succ_id = ?"
        ids = ActiveRecord::Base.execute_sql(statement, sid, id)
        next if ids.num_tuples() > 0
        statement = "INSERT INTO plants_successors (state, date_from, time_from, date_to, time_to, date_create, time_create, " +
            "owner_id, creator_id, operation_id, process, plant_pre_id, plant_succ_id) " +
            "VALUES (0, ?, 0, ?, 240000, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING id"
        ids = ActiveRecord::Base.execute_sql(statement, @date_from, @date_to, self.now_date_number, self.now_time_number, @user_id, @user_id \
              , self.get_operation_id_by_mode(mode), @user_id, sid, id)
      end
    end

    def identify_pl_successor_id(fs_id, ori_succ)
      pl_lines, error = detect_plant(fs_id, ori_succ, nil, nil, false)
      if !error.nil?
        return nil
      end
      if pl_lines
        id = pl_lines[0]['id']
        return id
      else
        msg = "<b>Fehler: Bei der Ermittlung der id der Vorgänger-Anlage '#{ori_succ}' wurde der Vorgänger nicht gefunden.</b>"
        @csv.errors << msg
        return nil
      end
    end

    def detect_plant(federal_state_id, pl_key, pl_eu_key, int_columns_eu_key, message = false)
      # bevorzuge den EU-Schlüssel (KORW-43)
      pl_eu_key_provided = (!pl_eu_key.nil? && pl_key != pl_eu_key && !int_columns_eu_key.nil? && !int_columns_eu_key[2].nil?)
      # Wenn der pl_key den Inhalt eines pl_eu_keys hat, ein pl_eu_key aber nicht angegeben ist,
      # interpretiere das so, als wäre ein pl_eu_key angegeben worden:
      if /DEPL_.+/ui =~ pl_key && pl_eu_key.nil?
        pl_eu_key = pl_key
      end
      eu_key_not_found = false
      fs_land_key_not_found = false
      id_by_eu_key = nil
      id_by_fs_and_key = nil
      if pl_eu_key && !int_columns_eu_key.nil? && !int_columns_eu_key[2].nil?
        statement = "SELECT * FROM plants " +
          "WHERE (federal_state_id = ? OR handled_by_federal_state_id = ?) " +
          "  AND pl_eu_key=?" +
          "  AND rep_code=?"
        zeilen_by_eu_key = ActiveRecord::Base.execute_sql(statement, federal_state_id, federal_state_id, pl_eu_key, @key_report)
        if zeilen_by_eu_key.num_tuples > 0
          zeile_by_eu_key = zeilen_by_eu_key[0]
          id_by_eu_key = zeile_by_eu_key['id']
        else
          eu_key_not_found = true
        end
      end
      if pl_key
        statement = "SELECT * FROM plants " +
          "WHERE (federal_state_id = ? OR handled_by_federal_state_id = ?)" +
          "  AND pl_key=? " +
          "  AND rep_code=?"
        zeilen_by_fs_and_key = ActiveRecord::Base.execute_sql(statement, federal_state_id, federal_state_id, pl_key, @key_report)
        if zeilen_by_fs_and_key.num_tuples > 0
          zeile_by_fs_and_key = zeilen_by_fs_and_key[0]
          id_by_fs_and_key = zeile_by_fs_and_key['id']
        else
          fs_land_key_not_found = true
        end
      end
      if message
        # Fall  land-key gefunden    eu-key geliefert    eu-key gefunden   Meldung
        #   1        false               false                 -            true
        #   2        false               true                false          true
        #   3        false               true                true           true
        #   4        true                false                 -              -
        #   5        true                true                false          true
        #   6        true                true                true             -
        if fs_land_key_not_found   # Fall 1..3
          if pl_eu_key_provided    # Fall 2 oder 3
            if eu_key_not_found    # Fall 2
              @csv.messages << "Die Anlage mit EU-Schlüssel #{pl_eu_key} oder Land-(#{federal_state_id})-Schlüssel „#{pl_key}“ ist noch nicht in der Datenbank vorhanden."
            else                   # Fall 3
              @csv.messages << "Die Anlage mit Land-(Nr. #{federal_state_id})-Schlüssel „#{pl_key}“ wurde über den EU-Schlüssel #{pl_eu_key} gefunden."
            end
          else                     # Fall 1
            @csv.messages << "Die Anlage mit Land-(Nr. #{federal_state_id})-Schlüssel „#{pl_key}“ ist noch nicht in der Datenbank vorhanden."
          end
        else                       # Fall 4..6, land-key gefunen
          if pl_eu_key_provided    # Fall 5 oder 6
            if eu_key_not_found    # Fall 5
              self.add_warning("Der Anlagen-EU-Schlüssel #{pl_eu_key} könnte falsch sein, denn er wurde nicht gefunden, jedoch ist der Land-(#{federal_state_id})-Schlüssel „#{pl_key}“ in der Datenbank vorhanden.".html_safe)
            else                   # Fall 6
              # Keine Meldung; Land-Key und EU-Key geliefert und gefunden
            end
          else                     # Fall 4
            # Keine Meldung; Land-Key gefunden, EU-Key nicht geliefert
          end
        end
      end

      if !id_by_eu_key.nil? && !id_by_fs_and_key.nil? && id_by_eu_key != id_by_fs_and_key
        # EU-Schlüssel und Land-Schlüssel zeigen auf unterschiedliche Objekte
        msg = "<b>Fehler (PL): EU-Schlüssel (#{pl_eu_key}) und Land-(#{federal_state_id})-Schlüssel (#{pl_key}) zeigen auf unterschiedliche Anlagen.</b>".html_safe
        @csv.errors << msg
        return nil, msg
      end

      if !zeilen_by_fs_and_key.nil? && zeilen_by_fs_and_key.num_tuples > 0
        return zeilen_by_fs_and_key, nil
      elsif !zeilen_by_eu_key.nil? && zeilen_by_eu_key.num_tuples > 0
        return zeilen_by_eu_key, nil
      end
      @csv.messages << "Keine Anlage mit: " << statement if @developer_mode
      return nil, nil
    end

    # ================================================

    def import_agglomerations
      zuords = []
      @int_lines.each do |int_line, int_columns|
        if !federal_column = int_columns.detect {|c| c[4] == 3}
          raise ParseException.new
        end
        if !agg_key_column = int_columns.detect {|c| c[4] == 4}
          raise ParseException.new
        end
        fs_id = federal_column[5]
        agg_key = agg_key_column[5]

        # get EU key
        int_columns_eu_key = nil
        if int_columns_eu_key = int_columns.detect {|c| c[4] == 7} # see →types, above
          ag_eu_key = int_columns_eu_key[5] # [5]=internal value, here: value of the EU key.
        else
          ag_eu_key = nil
        end

        agg_lines, error = self.detect_agglomeration(fs_id, agg_key, ag_eu_key, int_columns_eu_key, true)
        if !error.nil?
          next
        end
        if agg_lines && @developer_mode
          # @csv.messages << "DB-Spalten: " << agg_lines.fields.join(", ")
          # agg_lines.each { |cell| print cell, " = ", cell[cell], "\n" }
        end
        statement, statement_mode, id, agg_key = self.build_agg_statement(@mode, int_columns, agg_lines, fs_id)
        @csv.messages << agg_key.to_s + " : " + statement_mode.to_s + " : " + id.to_s + " : " + statement.to_s + " ." if @developer_mode
        # prüfe, ob eine Plant-Zuordnung vorgenommen werden muss
        plant_id = aggc1ka = nil
        int_columns.each do |int|
          column_index, uba_key, ext_val, rory_typ, typ, int_val, column_name = int
          plant_id = int_val if column_name == 'plant_id'
          aggc1ka = int_val if column_name == 'agg_c1_ka'
        end
        if @have_agg_pl_ref_id && (plant_id.nil? || plant_id == '') && @mode == 'overkill'
          # dann müssen die Zuordnungen gelöscht werden.
          zuords << [nil, agg_key, fs_id, nil]
        end
        if statement
          @csv.messages << "Agglomeration #{agg_key} im Modus #{statement_mode} bearbeitet"
          zuords << [plant_id, agg_key, fs_id, aggc1ka] if !plant_id.nil?
        else
          if statement_mode == 'locked'
            @csv.messages << "Agglomeration #{agg_key} im Modus #{statement_mode}: Agglomeration ist gesperrt, da bereits freigegeben"
          else
            @csv.messages << "Agglomeration #{agg_key} im Modus #{statement_mode}: Es mussten keine Werte aktualisiert werden"
            zuords << [plant_id, agg_key, fs_id, aggc1ka] if !plant_id.nil?
          end
        end
      end
      # $stderr.puts "eine connection ist so was: #{ActiveRecord::Base.connection.class}"
      zuords.each do |plant_id, agg_key, fs_id, aggc1ka|
        # Hole die Prozente, wenn sie nicht mit angegeben waren
        if aggc1ka.nil? && !plant_id.nil?
          statement = "SELECT agg_c1_ka FROM agglomerations WHERE agg_key=? AND federal_state_id=?"
          zeilen = ActiveRecord::Base.execute_sql(statement, agg_key, fs_id)
          if zeilen.num_tuples() == 0
            aggc1ka = 100 # weiß nicht ein noch aus...
          else
            zeilen.each {|zeile| aggc1ka = zeile['agg_c1_ka']}
          end
        end
        # Nun geht es mit folgenden Informationen weiter:
        # @mode = overwrite, overkill, append
        # @d_option = p, a ; ist aber egal: wir haben IMMER „alle“ Zuordnungen, weil es nur eine gibt

        # Folgendes passiert:
        # wenn plant_id == nil und @mode == overkill, dann lösche die Zuordnungen und fertig.
        # prüfe, ob es Zuordnungen gibt.
        # wenn keine Zuordnungen existieren, schreibe die hier gegebene hinein und fertig.
        # wenn mode == append, dann fertig (Zuordnung vorhanden und append ⇨ nix tun)
        # lösche Zuordnungen
        # schreibe die hier gegebene hinein

        # ermittle ID der Agglomeration
        agg_id = nil
        zeilen, error = detect_agglomeration(fs_id, agg_key, nil, nil, false)
        if zeilen.nil?
          puts "Fehler in import_agglomerations (#2.1): BuLa #{fs_id}, <#{agg_key}>"
        end
        zeilen.each {|zeile|
          agg_id = zeile['id']
        }
        next if agg_id.nil?

        # wenn plant_id == nil und @mode == overkill ...
        if (plant_id.nil? || plant_id == '') && @mode == 'overkill'
          # ... dann lösche die Zuordnungen und fertig.
          statement = "DELETE FROM agglomerations_plants WHERE agglomeration_id=?"
          ActiveRecord::Base.execute_sql(statement, agg_id)
          next
        end

        # prüfe, ob es Zuordnungen gibt.
        statement = "SELECT COUNT(*) AS wie_viele FROM agglomerations_plants WHERE agglomeration_id=?"
        zeilen = ActiveRecord::Base.execute_sql(statement, agg_id)
        have_zuord = false
        zeilen.each {|zeile| have_zuord = (zeile['wie_viele'].to_i > 0)}

        # wenn keine Zuordnungen existieren...
        if !have_zuord
          # ... schreibe die hier gegebene hinein und fertig.
          statement = "INSERT INTO agglomerations_plants (state, date_from, time_from, date_to, time_to, date_create, time_create,
owner_id, creator_id, operation_id, process, agglomeration_id,plant_id,agg_percent_plant) VALUES
(0, ?, 0, ?, 240000, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
          ActiveRecord::Base.execute_sql(statement, @date_from, @date_to, self.now_date_number, self.now_time_number, @user_id, @user_id \
          , self.get_operation_id_by_mode(@mode), @user_id, agg_id, plant_id, aggc1ka)
          next
        end

        # wenn mode == append, dann fertig (Zuordnung vorhanden und append ⇨ nix tun)
        next if @mode == 'append'

        # lösche Zuordnungen
        statement = "DELETE FROM agglomerations_plants WHERE agglomeration_id=?"
        ActiveRecord::Base.execute_sql(statement, agg_id)

        # schreibe die hier gegebene hinein
        statement = "INSERT INTO agglomerations_plants (state, date_from, time_from, date_to, time_to, date_create, time_create, owner_id,
creator_id, operation_id, process, agglomeration_id,plant_id,agg_percent_plant)
VALUES (0, ?, 0, ?, 240000, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
        ActiveRecord::Base.execute_sql(statement, @date_from, @date_to, self.now_date_number, self.now_time_number, @user_id, @user_id \
            , self.get_operation_id_by_mode(@mode), @user_id, agg_id, plant_id, aggc1ka)
      end
    end

    def build_agg_statement(mode, int_columns, agg_lines, fs_id)
      id = nil
      successors_column = nil
      agg_key = nil
      state_column = nil
      if agg_lines
        locked = agg_lines[0]['locked']
        return nil, 'locked', nil, agg_lines[0]['agg_key'] if locked.to_i == 1
        cols = []
        vals = []
        agg_lines.fields.each do |fieldname|
          next if fieldname == 'agg_eu_key' # tlu, KORW-18: eu_keys nicht ändern beim Import
          next if fieldname == 'agg_pl_ref_id' # tlu, 2014-05-28: Pseudofeld, nicht tatsächlich INSERTen.
          val = agg_lines[0][fieldname]
          id = agg_lines[0]['id']
          int_columns.each do |int|
            column_index, uba_key, ext_val, rory_typ, typ, int_val, column_name = int
            next if column_name == 'plant_id'
            if column_name != fieldname
              next
            end
            if @mode == "overwrite" || @mode == "overkill" || val.nil? || val.blank?
              cols << "#{column_name} = ?"
              vals << int_val
            end
            agg_key = ext_val if column_name == 'agg_key'
          end
        end
        # successors haben hier keine Spalte! Trotzdem könnten sie übermittelt worden sein:
        # int[4] ⇨ typ, typ == 5 ⇨ „Function auf id von Zeile“
        successors_column = int_columns.detect {|int| int[4] == 5}
        self.insert_agg_successors(mode, id, successors_column, fs_id) if successors_column
        # Unabhängig von den successors: Wenn andere Daten übermittelt wurden, update ausführen:
        unless cols.empty?
          statement = "UPDATE agglomerations SET #{cols.join(', ')} WHERE id=?"
          ActiveRecord::Base.execute_sql(statement, *vals, id)
          @csv.messages << statement if @developer_mode
          return statement, 'update', id, agg_key
        else
          @csv.messages << "update betraf keine Spalten: Nichts zu tun" if @developer_mode
          return nil, 'update', nil, agg_key
        end
      else
        cols = []
        vals = []
        int_columns.each do |int|
          column_index, uba_key, ext_val, rory_typ, typ, int_val, column_name = int
          if column_name == 'successors'
            successors_column = int
            next
          end
          next if column_name == 'plant_id'
          # next if column_name == 'rep_code'
          state_column = true if uba_key == "agg_state"
          agg_key = ext_val if column_name == 'agg_key'
          cols << column_name
          vals << int_val
        end
        if !state_column
          cols << 'agg_state_active'
          vals << 1
        end
        statement = "INSERT INTO agglomerations (state, date_from, time_from, date_to, time_to, date_create, time_create,
          owner_id, creator_id, operation_id, process, #{cols.join(', ')})
          VALUES (0, ?, 0, ?, 240000, ?, ?, ?, ?, ?, ?, #{cols.collect {|v| '?'}.join(', ')}) returning id"
        @csv.messages << statement if @developer_mode
        ids = ActiveRecord::Base.execute_sql(statement, @date_from, @date_to, self.now_date_number, self.now_time_number \
            , @user_id, @user_id, self.get_operation_id_by_mode(mode), @user_id, *vals)
        id = ids[0]['id']
        self.insert_agg_successors(mode, id, successors_column, fs_id) if successors_column
        return statement, 'insert', id, agg_key
      end
    end

    def insert_agg_successors(mode, id, successors_column, fs_id)
      # puts successors_column.inspect
      successors = successors_column[5]
      @csv.messages << "\nAgglomeration Successors (id/succ): " + id + " / " + successors.join(", ") if @developer_mode
      if !successors || successors.blank?
        return
      end
      if mode == "append"
        # Testen, ob Predecessors vorhanden sind. Dann machen wir nicht.
        statement = "select id from agglomerations_successors where agglomeration_succ_id = ?"
        ids = ActiveRecord::Base.execute_sql(statement, id)
        if ids.num_tuples() > 0
          message = "Predecessors gefunden. Mode ist append. ids in agglomerations_successors: "
          ids.each {|line| message << line['id'] << ", "}
          @csv.messages << message[0, message.length - 2] if @developer_mode
          return
        end
      end

      ori_succs = []
      successors_column[2].strip.split(',').each do |key|
        key.strip!
        next if key.length == 0
        ori_succs << key
      end

      index = -1
      successors.each do |successor|
        index += 1
        if successor == 'fetchme_later'
          sid = self.identify_agg_successor_id(fs_id, ori_succs[index])
        else
          sid = successor
        end
        next if !sid
        statement = "SELECT id FROM agglomerations_successors WHERE agglomeration_pre_id = ? and agglomeration_succ_id = ?"
        ids = ActiveRecord::Base.execute_sql(statement, sid, id)
        next if ids.num_tuples() > 0
        statement = "insert into agglomerations_successors (state, date_from, time_from, date_to, time_to, date_create, time_create, " +
            "owner_id, creator_id, operation_id, process, agglomeration_pre_id, agglomeration_succ_id) " +
            "values (0, ?, 0, ?, 240000, ?, ?, ?, ?, ?, ?, ?, ?) returning id"
        ids = ActiveRecord::Base.execute_sql(statement, @date_from, @date_to, self.now_date_number, self.now_time_number \
            , @user_id, @user_id, self.get_operation_id_by_mode(mode), @user_id, sid, id)
      end
    end

    def identify_agg_successor_id(fs_id, ori_succ)
      agg_lines, error = self.detect_agglomeration(fs_id, ori_succ, nil, nil, false)
      if agg_lines
        id = agg_lines[0]['id']
        return id
      else
        msg = "<b>Fehler: Bei der Ermittlung der id der Vorgänger-Agglomeration '#{ori_succ}' wurde der Vorgänger nicht gefunden.</b>"
        @csv.errors << msg
        return nil
      end
    end

    def detect_agglomeration(federal_state_id, agg_key, ag_eu_key, int_columns_eu_key, message = false)
      # bevorzuge den EU-Schlüssel (KORW-43)
      ag_eu_key_provided = (!ag_eu_key.nil? && agg_key != ag_eu_key && !int_columns_eu_key.nil? && !int_columns_eu_key[2].nil?)
      # Wenn der agg_key den Inhalt eines ag_eu_keys hat, ein ag_eu_key aber nicht angegeben ist,
      # interpretiere das so, als wäre ein ag_eu_key angegeben worden:
      if /DEAG_.+/ui =~ agg_key && ag_eu_key.nil?
        ag_eu_key = agg_key
      end
      eu_key_not_found = false
      fs_land_key_not_found = false
      id_by_eu_key = nil
      id_by_fs_and_key = nil
      if ag_eu_key && !int_columns_eu_key.nil? && !int_columns_eu_key[2].nil?
        statement = "SELECT * FROM agglomerations " +
            "WHERE (federal_state_id = ? OR handled_by_federal_state_id = ?)" +
            "  AND agg_eu_key = ?" +
            "  AND rep_code = ?"
        zeilen_by_eu_key = ActiveRecord::Base.execute_sql(statement, federal_state_id, federal_state_id, ag_eu_key, @key_report)
        if zeilen_by_eu_key.num_tuples > 0
          zeile_by_eu_key = zeilen_by_eu_key[0]
          id_by_eu_key = zeile_by_eu_key['id']
        else
          eu_key_not_found = true
        end
      end
      if agg_key
        statement = "SELECT * FROM agglomerations " +
            "WHERE (federal_state_id = ? OR handled_by_federal_state_id = ?)" +
            "  AND agg_key = ?" +
            "  AND rep_code = ?"
        zeilen_by_fs_and_key = ActiveRecord::Base.execute_sql(statement, federal_state_id, federal_state_id, agg_key, @key_report)
        if zeilen_by_fs_and_key.num_tuples > 0
          zeile_by_fs_and_key = zeilen_by_fs_and_key[0]
          id_by_fs_and_key = zeile_by_fs_and_key['id']
        else
          fs_land_key_not_found = true
        end
      end
      if message
        # Fall  land-key gefunden    eu-key geliefert    eu-key gefunden   Meldung
        #   1        false               false                 -            true
        #   2        false               true                false          true
        #   3        false               true                true           true
        #   4        true                false                 -              -
        #   5        true                true                false          true
        #   6        true                true                true             -
        if fs_land_key_not_found   # Fall 1..3
          if ag_eu_key_provided    # Fall 2 oder 3
            if eu_key_not_found    # Fall 2
              @csv.messages << "Die Agglomeration mit EU-Schlüssel #{ag_eu_key} oder Land-(#{federal_state_id})-Schlüssel „#{agg_key}“ ist noch nicht in der Datenbank vorhanden."
            else                   # Fall 3
              @csv.messages << "Die Agglomeration mit Land-(Nr. #{federal_state_id})-Schlüssel „#{agg_key}“ wurde über den EU-Schlüssel #{ag_eu_key} gefunden."
            end
          else                     # Fall 1
            @csv.messages << "Die Agglomeration mit Land-(Nr. #{federal_state_id})-Schlüssel „#{agg_key}“ ist noch nicht in der Datenbank vorhanden."
          end
        else                       # Fall 4..6, land-key gefunen
          if ag_eu_key_provided    # Fall 5 oder 6
            if eu_key_not_found    # Fall 5
              self.add_warning("Der Agglomerations-EU-Schlüssel #{ag_eu_key} könnte falsch sein, denn er wurde nicht gefunden, jedoch ist der Land-(#{federal_state_id})-Schlüssel „#{agg_key}“ in der Datenbank vorhanden.".html_safe)
            else                   # Fall 6
              # Keine Meldung; Land-Key und EU-Key geliefert und gefunden
            end
          else                     # Fall 4
            # Keine Meldung; Land-Key gefunden, EU-Key nicht geliefert
          end
        end
      end

      if !id_by_eu_key.nil? && !id_by_fs_and_key.nil? && id_by_eu_key != id_by_fs_and_key
        # EU-Schlüssel und Land-Schlüssel zeigen auf unterschiedliche Objekte
        msg = "<b>Fehler (AGG): EU-Schlüssel (#{ag_eu_key}) und Land-(#{federal_state_id})-Schlüssel (#{agg_key}) zeigen auf unterschiedliche Einleitstellen.</b>".html_safe
        @csv.errors << msg
        return nil, msg
      end

      if !zeilen_by_fs_and_key.nil? && zeilen_by_fs_and_key.num_tuples > 0
        return zeilen_by_fs_and_key, nil
      elsif !zeilen_by_eu_key.nil? && zeilen_by_eu_key.num_tuples > 0
        return zeilen_by_eu_key, nil
      end
      @csv.messages << "Keine Agglomeration mit: " << statement if @developer_mode
      return nil, nil
    end


    # ================================================
    # ================================================
    # ================================================
    # ================================================
    # ================================================
    # ================================================

    def bestimmen()
      @tablename, @object_singular, @object_plural = self.detect_table
    end

    # Phase 1, wir prüfen, ob ein Import klappt und entfernen die Zeilen,
    # die nicht importiert werden können. In dieser Methode wird eine Tabelle bearbeitet und alle
    # Informationen für den späteren Import werden zusammengetragen.
    # Auch die Umsortierung zum Umgang mit Vorwärtsreferenzen passiert hier.
    def verarbeiten()
      # Anfang. Hier gehts los. Start. Entrypoint (für die Tabelle).
      #RubyProf.start
      @tablename, @object_singular, @object_plural = self.detect_table
      return unless @tablename
      puts "#{@tablename} wird verarbeitet. Start #{now}"
      @csv.messages << "Tabelle <b>#{@tablename}</b> detektiert; Datensatzmodus <b>#D#{@d_option}</b>".html_safe
      @csv.messages << @csv_columns.join(";").html_safe if @developer_mode
      # Pass 1 : vorbereiten für Sortierung
      newlines = []
      sortnfo = {}
      fatal = false
      # @lines is an array of lines where each array element is AT THIS POINT an array of two elements:
      # the line number from the import file and the respective line from the import file
      # Each @lines element array will be extended further at the end of the following loop
      @lines.each_index do |line_index|
        # line_index    : Index der Zeile im Array @lines (== self.lines)
        # @zeilennummer : Zeilennummer im Importfile
        # int_line      : [ line_index, @zeilennummer ]
        # @int_lines    : Array der importierbaren Zeilen im Format [ [line_index, zeilennummer], [spalte,spalte,...] ]
        #   The array [spalte, spalte, ...] is most often named int_columns here.
        #   The single column spalte of int_columns is an array again and comprises seven elements:
        #     * [0] column_index (index of the respective column from the CSV parsed customers table header; at least most often:
        #             the rep_code seems to be sorted to the end and gets the index behind the last CSV cell ... wtf?)
        #     * [1] uba_key (CSV import label)
        #     * [2] cell (value)
        #     * [3] rory_typ
        #     * [4] typ
        #     * [5] internal_value
        #     * [6] column_name (in database)
        @federal_state_id = nil
        @federal_state_reference_id = nil
        @federal_state_admin_id = nil
        cells = []
        @zeilennummer, line = @lines[line_index]
        @line_can_be_imported = true
        puts "Zeilennummer #{@zeilennummer}"
        tmp_cells = line.split(@csv.trennzeichen)

        tmp_cells.each_index do |cell_index|
          tmp_cell = tmp_cells[cell_index]
          # stelle sicher dass kein gequotetes Textfeld durch einen column- oder row-seperator zerhackt wird
          num_quotes = tmp_cell.count('"')
          until num_quotes.even?
            if tmp_cells[cell_index + 1]
              #der gequotete Eintrag wurde wegen eines column-seperators zerrissen, und dieser entfernt
              tmp_cell << @csv.trennzeichen
            else
              unless @lines[line_index + 1]
                self.add_error("<b>Fehler (Zeile #{@zeilennummer}): CSV-Datei ist ungültig. Vermutlich wurden Anführungszeichen falsch gesetzt. Import der Tabelle #{@tablename} muss abgebrochen werden.</b>".html_safe)
                fatal = true
                break
              end
              tmp_cells.concat(@lines[line_index + 1][1].split(@csv.trennzeichen))
              @lines.delete_at(line_index + 1)
              # ACHTUNG: das Löschen aus einem array, über den man gerade iteriert, ist riskant
              # => mit der aktuellen ruby implementation (version 1.9.1) funktioniert es aber
            end
            tmp_cell << tmp_cells[cell_index + 1]
            tmp_cells.delete_at(cell_index + 1)
            num_quotes = tmp_cell.count('"')
          end
          tmp_cell.chomp!
          #remove surrounding quotes
          tmp_cell.sub!(/^"(.*)"$/, '\1')
          #unescape quotes (d.h. mach aus doppelten Anführungszeichen einfache)
          tmp_cell.sub!('""', '"')
          cells = tmp_cells
        end

        # get sort relevant stuff
        sort_state = 1
        sort_date = '22220101'
        cells.each_index do |colidx|
          case @csv_columns[colidx]
          when 'agg_state'
            egal1, internal_value, egal2, egal3 = self.get_internal_value(@csv_columns[colidx], cells[colidx], colidx, cells)
            sort_state = internal_value
          when 'ag_date'
            egal1, internal_value, egal2, egal3 = self.get_internal_value(@csv_columns[colidx], cells[colidx], colidx, cells)
            sort_date = internal_value
          when 'pl_state'
            egal1, internal_value, egal2, egal3 = self.get_internal_value(@csv_columns[colidx], cells[colidx], colidx, cells)
            sort_state = internal_value
          when 'pl_cdate'
            egal1, internal_value, egal2, egal3 = self.get_internal_value(@csv_columns[colidx], cells[colidx], colidx, cells)
            sort_date = internal_value
          when 'dp_state'
            egal1, internal_value, egal2, egal3 = self.get_internal_value(@csv_columns[colidx], cells[colidx], colidx, cells)
            sort_state = internal_value
          end
        end
        sort_state = 0 if sort_state == nil
        sortnfo[@zeilennummer] = [sort_state, sort_date]

        # identify bl_code (federal_state_id) and bl_code_ref (if exists)
        # and identify empty lines too
        @have_agg_pl_ref_id = false
        @has_no_content = true
        @csv_columns.each_index do |column_index|
          cell = cells[column_index]
          unless cell.nil? || cell.blank?
            @has_no_content = false
          end
          uba_key = @csv_columns[column_index]
          @have_agg_pl_ref_id = true if uba_key == 'agg_pl_ref_id'
          next unless uba_key =~ /bl_code.*/
          bundesland_id = self.get_bundesland_id(cell, uba_key)
          case uba_key
          when 'bl_code', 'bl_code_agg' then
            # Bundesland des aktuellen Objekts
            @federal_state_id = bundesland_id
          when 'bl_code_ref', 'bl_code_pl' then
            # Abweichendes Bundesland, von diesem Objekt mit diesem Bundesland referenziert
            @federal_state_reference_id = bundesland_id
          when 'bl_code_admin' then
            # Verwaltendes Bundesland. Weicht vom aktuellen (geografisch-politischen) Bundesland ab
            @federal_state_admin_id = bundesland_id
          end
        end
        if @has_no_content
          @csv.messages << "Zeile #{@zeilennummer} ist leer.".html_safe
          @line_can_be_imported = false
          next
        end
        unless (@federal_state_id && @line_can_be_imported)
          if !@federal_state_id
            self.add_error("<b>Fehler (Zeile #{@zeilennummer}): Bundesland (bl_code) kann nicht identifiziert werden. Import der Tabelle #{@tablename} muss abgebrochen werden.</b>".html_safe)
          end
          @lines[line_index] << false << cells << @federal_state_id << @federal_state_reference_id << @federal_state_admin_id << @zeilennummer
          newlines << @lines[line_index]
          next
        end
        unless (self.can_import?(@federal_state_id) || self.can_import?(@federal_state_admin_id))
          self.add_error("<b>Fehler (Zeile #{@zeilennummer}): #{@object_singular} für Land #{self.get_federal_key_by_id(@federal_state_id)} (bl_code) kann nicht importiert werden: Berechtigung fehlt.</b>".html_safe)
          @line_can_be_imported = false
        end

        @lines[line_index] << @line_can_be_imported << cells << @federal_state_id << @federal_state_reference_id << @federal_state_admin_id << @zeilennummer
        newlines << @lines[line_index]
      end #@lines.each_index do |line_index|

      unless fatal
        # sort lines according to disabled state/date
        # [*, agg_state, *, *, *, 0/1, agg_state_active]
        # [*, ag_date, *, *, *, 20081231, 
        # [*, agg_hist, "443322a, 443322b", *, *, [ "id", "id" ], "successors"]
        # [*, pl_state, *, *, *, 0/1, pl_state_active]
        # [4, "pl_hist", "443322p1", 11, 5, ["16159"], "successors"]
        # [14, "pl_cdate", "17.02.1989", 4, 1, "19890217", "pl_cdate"]
        # dp_state_active
        # puts "RAPUNZEL:\n"+newlines.to_s
        @lines = newlines.sort {|a, b|
          # compare disabled/enabled state
          next -1 if sortnfo[a[-1]][0] < sortnfo[b[-1]][0]
          next 1 if sortnfo[a[-1]][0] > sortnfo[b[-1]][0]
          # compare dates
          next sortnfo[a[-1]][1].to_s <=> sortnfo[b[-1]][1].to_s
        }
        sortnfo = nil # free up a few bytes of RAM

        @lines.each_index do |line_index|
          @zeilennummer = @lines[line_index].pop
          @federal_state_admin_id = @lines[line_index].pop
          @federal_state_reference_id = @lines[line_index].pop
          @federal_state_id = @lines[line_index].pop
          cells = @lines[line_index].pop
          @line_can_be_imported = @lines[line_index].pop

          int_line = [line_index, @zeilennummer]
          int_columns = []
          temp_key = nil
          temp_eu_key = nil
          temp_active = nil

          if @federal_state_reference_id.nil?
            @federal_state_reference_id = @federal_state_id
          end
          if @federal_state_admin_id.nil?
            @federal_state_admin_id = @federal_state_id
          end

          if @line_can_be_imported
            # Pre part: traverse all cells (columns) of this line
            # Just to detect temp_key and temp_eu_key:
            temp_object_eu_key = 'unbekannt'
            @csv_columns.each_index do |column_index|
              uba_key = @csv_columns[column_index]
              next if (uba_key != 'agg_eu_key' && uba_key != 'pl_eu_key' && uba_key != 'dp_eu_key')
              cell = cells[column_index]
              master_column = [column_index, uba_key, cell]
              column_name, internal_value, rory_typ, import_typ = self.get_internal_value(uba_key, cell, column_index, cells)
              if (column_name && internal_value) || (column_name && column_name =~ /_state_active/ui && !internal_value) || (column_name && @mode == "overkill")
                if column_name == 'agg_eu_key' || column_name == 'pl_eu_key' || column_name == 'dp_eu_key'
                  next if internal_value.blank?
                  temp_object_eu_key = internal_value
                end
              end
            end

            # Main part: traverse all cells (columns) of this line
            @csv_columns.each_index do |column_index|
              uba_key = @csv_columns[column_index]
              next if @ignored_columns.include?(uba_key)
              cell = cells[column_index]
              master_column = [column_index, uba_key, cell]
              column_name, internal_value, rory_typ, import_typ = self.get_internal_value(uba_key, cell, column_index, cells, temp_object_eu_key)
              # This is evil, this is bad. Gefahr, böse, unsauber, fies. Upredictable behaviour:
              # The rep_code has even in overkill mode to be skipped because we brute force add it again
              # 30 lines further (by call to fill_missing_attributes) as last column:
              if (column_name && internal_value) || (column_name && column_name =~ /_state_active/ui && !internal_value) || (column_name && @mode == "overkill" && column_name != 'rep_code')
                temp_key = internal_value if column_name == 'agg_key' || column_name == 'pl_key' || column_name == 'dp_key'
                if column_name == 'agg_eu_key' || column_name == 'pl_eu_key' || column_name == 'dp_eu_key'
                  next if internal_value.blank?
                  temp_eu_key = internal_value
                end
                master_column << rory_typ << import_typ << internal_value << column_name
                # int_columns-filling-point:
                int_columns << master_column
              end
            end
            @csv.messages << cells.join(";") if @developer_mode
            # At this point there is no additional/duplicated rep_code column at the end
            unless temp_key || @tablename == "federal_state_info" || @tablename == "agglomerations_plants"
              self.add_error("<b>Fehler (Zeile #{@zeilennummer}): Schlüssel kann nicht identifiziert werden." +
                                 " Import der Tabelle #{@tablename} muss abgebrochen werden. Vermutlich liegt eine Fehlinterpretation bezüglich der Information vor," +
                                 " um welche zu importierende Tabelle es sich handelt. Dies kann dadurch geschehen, dass in einer Anlagentabelle eine" +
                                 " Agglomerationsschlüsselspalte angegeben wurde, aber auch im umgekehrten Fall.</b>".html_safe)
              @lines[line_index] << @line_can_be_imported
              fatal = true
              break
            end
          end

          self.check_agglo_plant_keys(int_columns) if @tablename == 'agglomerations_plants'

          if @line_can_be_imported
            int_columns, temp_active = self.fill_missing_attributes(temp_key, temp_eu_key, int_columns)
            if @tablename == 'agglomerations' || @tablename == 'plants' || @tablename == 'discharge_points'
              # puts "temp_objects #{[temp_key, @federal_state_id, @tablename, temp_active].join(", ")}"
              @csv.temp_objects[[temp_key, @federal_state_id, @tablename, temp_active]] ||= 1               # Für das BuLa des Objekts
              @csv.temp_objects_admin[[temp_key, @federal_state_admin_id, @tablename, temp_active]] ||= 1   # Für das verwaltende BuLa
            end
            @int_lines << [int_line, int_columns]
          end
          @lines[line_index] << @line_can_be_imported
        end # foreach @lines
      end # unless fatal
      @csv.messages << ""

      puts "#{@tablename} wurde verarbeitet. Stop #{now}"

      #      result = RubyProf.stop
      #      printer = RubyProf::FlatPrinter.new(result)
      #      printer.print(STDOUT, {})
    end

    # Prüft, ob agg_key und pl_ID vorhanden sind und setzt @line_can_be_imported via add_error auf false, falls nicht
    def check_agglo_plant_keys(int_columns)
      return @line_can_be_imported unless @line_can_be_imported
      agg_key_found = nil
      pl_id_found = nil
      int_columns.each do |col|
        uba_key = col[1]
        case uba_key.downcase
        when 'agg_key'
          agg_key_found = true
        when 'pl_id'
          pl_id_found = true
        end
      end
      self.add_error("Fehler (Zeile #{@zeilennummer}): Der Wert für (agg_key) fehlt.") unless agg_key_found
      self.add_error("Fehler (Zeile #{@zeilennummer}): Der Wert für (pl_ID) fehlt.") unless pl_id_found
    end

    def fill_missing_attributes(temp_key, temp_eu_key, int_columns)
      # Hier wird ggf. der xy_eu_key erzeugt.
      max_col_index = 0
      int_columns.each {|master_column| max_col_index = master_column[0] if max_col_index < master_column[0]}
      unless temp_eu_key && !temp_eu_key.strip.blank?
        case @tablename
        when 'agglomerations'
          agg_eu_key = "DEAG_" + get_federal_key_by_id(@federal_state_id) + temp_key
          int_columns << [max_col_index + 1, 'agg_eu_key', nil, 0, 7, agg_eu_key, 'agg_eu_key']
          max_col_index += 1
        when 'plants'
          pl_eu_key = "DETP_" + get_federal_key_by_id(@federal_state_id) + temp_key
          int_columns << [max_col_index + 1, 'pl_eu_key', nil, 0, 7, pl_eu_key, 'pl_eu_key']
          max_col_index += 1
        when 'discharge_points'
          dp_eu_key = "DEDP_" + get_federal_key_by_id(@federal_state_id) + temp_key
          int_columns << [max_col_index + 1, 'dp_eu_key', nil, 0, 7, dp_eu_key, 'dp_eu_key']
          max_col_index += 1
        end
      end
      int_columns << [max_col_index + 1, 'rep_code', @key_report, 0, 2, @key_report, 'rep_code']
      max_col_index += 1

      # Add or replace ACTIVE or INACTIVE state if neccessary:
      temp_active = nil
      state_active_col_index = int_columns.find_index {|col| col[6] =~ /_state_active/ui}
      unless state_active_col_index
        max_col_index += 1
        temp_active = 1
        # FIXME: sollte hier nicht auch die Spalte hinzugefügt werden? Sonst steht ja nix drin? -tlu
      else
        # Column is delivered. We have to check whether it contains a value:
        col = int_columns[state_active_col_index]
        if col[5]
          temp_active = col[5]
        else
          col[5] = temp_active = 1
        end
      end

      return int_columns, temp_active
    end


    # get_internal_value
    #   returns: column_name, internal_value, rory_typ, typ
    def get_internal_value(uba_key, cell, index, cells, object_eu_key = 'unbekannt')
      if !@gen_columns[index]
        # self.add_error("Fehler (Zeile #{@zeilennummer}): Die Spalte (#{uba_key}) ist unbekannt.")
        return nil, nil
      end
      reference_type = @gen_columns[index]['reference_type'].to_i
      col_name = @gen_columns[index]['col_name']
      return nil, nil, nil, nil if self.evil_rep_code(col_name, cell)
      return col_name, nil if cell.blank?

      case reference_type
      when 0 then # kurztext
        # TODO/DANGER/WARNING/GEFAHR: 2022-03-06 hier die _eu_keys mit aufgenommen.
        # Damals keine Ahnung, was damit alles zerbrach. Jezt quasi? alles repariert:
        if col_name == 'agg_key' || col_name == 'pl_key' || col_name == 'dp_key'
          return col_name, cell.to_s, reference_type, 4
        elsif col_name == 'agg_eu_key' || col_name == 'pl_eu_key' || col_name == 'dp_eu_key'
          return col_name, cell, reference_type, 7
        else
          return col_name, cell, reference_type, 2
        end
      when 1 then # Liste foreign
        return col_name, self.get_id_from_foreign_list(@gen_columns[index], cell, uba_key, object_eu_key), reference_type, 1
      when 2 then # Liste normal
        return col_name, self.get_id_from_list(@gen_columns[index], cell, uba_key, object_eu_key), reference_type, 1
      when 3 then # bool
        bool = self.get_bool(cell)
        unless bool
          self.add_error("Fehler (Zeile #{@zeilennummer}, EU-Key #{object_eu_key}): Der JA/NEIN-Wert '#{cell}' (#{uba_key}) kann nicht interpretiert werden.")
        end
        return col_name, bool, reference_type, 1
      when 4 then # date
        unless self.is_valid_date(cell)
          self.add_error("Fehler (Zeile #{@zeilennummer}, EU-Key #{object_eu_key}): Das Datum '#{cell}' (#{uba_key}) kann nicht interpretiert werden.")
          return col_name, nil, reference_type, 1
        end
        return col_name, self.numerical_date(cell), reference_type, 1
        # reference_type == 5 wird nicht verwendet; Muss nicht geprüft werden. Stattdessen wird 11 verwendet.
      when 6 then # bundesland
        return col_name, self.get_bundesland_id(cell, uba_key), reference_type, 3
      when 7 then # langtext
        return col_name, cell, reference_type, 2
      when 8, 81 then # integer
        munge = cell.strip.gsub(/[_\s]/u, '')
        if @csv.zahlenformat == 'edv'
          unless /^[-+]?\d+(?:\.(?<decimal>\d*))?$/ =~ munge && decimal.to_i == 0
            self.add_error("Fehler (Zeile #{@zeilennummer}, EU-Key #{object_eu_key}): Der Wert '#{cell}' (#{uba_key}) sollte eine EDV-formatierte Gleitkommazahl sein.")
            return col_name, nil, reference_type, 1
          end
          return col_name, munge.to_i, reference_type, 1
        else
          unless is_valid_int_or_empty(munge)
            self.add_error("Fehler (Zeile #{@zeilennummer}, EU-Key #{object_eu_key}): Der Wert '#{cell}' (#{uba_key}) sollte eine Ganzzahl sein.")
            return col_name, nil, reference_type, 1
          end
        end
        return col_name, munge.gsub('.', '').to_i, reference_type, 1
      when 9, 91 then # float
        munge = cell.strip.gsub(/[_\s]/u, '')
        if @csv.zahlenformat == 'edv'
          unless /^[-+]?\d+(?:\.\d*)?$/ =~ munge
            self.add_error("Fehler (Zeile #{@zeilennummer}, EU-Key #{object_eu_key}): Der Wert '#{cell}' (#{uba_key}) sollte eine EDV-formatierte Gleitkommazahl sein.")
            return col_name, nil, reference_type, 1
          end
          return col_name, munge, reference_type, 1
        else
          unless is_valid_float_or_empty(munge)
            self.add_error("Fehler (Zeile #{@zeilennummer}, EU-Key #{object_eu_key}): Der Wert '#{cell}' (#{uba_key}) sollte eine Gleitkommazahl sein.")
            return col_name, nil, reference_type, 1
          end
          return col_name, munge.gsub('.', '').gsub(',', '.'), reference_type, 1
        end
      when 10 then # liste foreign mit bundeslandbeschraenkung
        # id, typ = self.get_id_from_misc_list(@gen_columns[index], cell, uba_key)
        # return col_name, id, reference_type, typ
        # Neu:
        return col_name, self.get_id_from_foreign_list(@gen_columns[index], cell, uba_key, object_eu_key), reference_type, 1
      when 11 then
        # Either federal_state_ids and plant/agglo-ids if agglomerations_plants
        # or predecessors_ids if
        # col_name = predecessors, array[0] = id of current record, array[1] = array of ids of predecessors
        return self.get_joker_ids(@gen_columns[index], cell, uba_key)
      when 12 then # Liste mit Bundesland
        # Sonderbehandlung dp_gwbody_id: Wenn 1 (ENDA_GWK01, "Kein Grundwasserkörper"), dann
        # als nil (nicht geliefert) behandeln. Ursache für die "falsche" Zuordnung des ENDA_GWK01
        # ist, dass UBA damals keine GWKs liefern konnte und wir einen Dummy eingebaut haben.
        if @gen_columns[index]['foreign_table'] == 'l_groundwater_body' and cell.strip.upcase == 'ENDA_GWK01'
          return col_name, nil, reference_type, 1
        end
        return col_name, self.get_id_from_list(@gen_columns[index], cell, uba_key, object_eu_key), reference_type, 1
      when 13 then # liste foreign mit bundeslandbeschraenkung
        id, typ = self.get_id_from_misc_list(@gen_columns[index], cell, uba_key, object_eu_key)
        return col_name, id, reference_type, typ
      end
      return col_name, cell
    end

    def evil_rep_code(rep_code, cell)
      evil = false
      very_evil = false
      if rep_code == 'rep_code'
        evil = true
      else
        return false
      end
      if cell.strip != @key_report
        very_evil = true
        self.add_error("<b>Fehler (Zeile #{@zeilennummer}): Reportperiode (rep_code, hier: \"#{cell}\") " +
                           "muss -- wenn angegeben -- die aktuelle Reportperiode #{@key_report} sein!</b>".html_safe)
      end
      return evil || very_evil
    end

    # get_joker_ids
    #   returns: column_name, internal_value, rory_typ, typ
    def get_joker_ids(ref_values, cell, uba_key)
      function = ref_values['surrounding_function']
      table = ref_values['table_name']
      cell.strip!
      # puts "function _#{function}_, cell _#{cell}_, uba_key _#{uba_key}_"
      case function
      when 'fill_agg_successors' # agg_hist
        col_name = "successors"
        successors_ids = []
        cell.strip.split(',').each do |key|
          key.strip!
          next if key.length == 0
          statement = "SELECT a.id FROM agglomerations a, model.reporting_periods rp " +
              "WHERE a.agg_key = ? AND (a.federal_state_id = ? OR a.handled_by_federal_state_id = ?)" +
              "  AND a.agg_state_active = 0" +
              "  AND a.rep_code = rp.key_report and rp.state = 1"
          zeilen = ActiveRecord::Base.execute_sql(statement, key, @federal_state_id, @federal_state_id)
          if zeilen.num_tuples == 1
            zeilen.each do |zeile|
              id = zeile['id']
              # puts id
              successors_ids << id
            end
          else
            if (@csv.temp_objects[[key, @federal_state_id, 'agglomerations', 0]].nil? &&
                @csv.temp_objects_admin[[key, @federal_state_id, 'agglomerations', 0]].nil?)
              self.add_error("<b>Fehler (Zeile #{@zeilennummer}): Agglomerationsvorgänger #{key} (#{uba_key}) " +
                                 "kann nicht gefunden werden. Entweder existiert er nicht oder er ist noch AKTIV oder " +
                                 "er liegt in einem anderen Bundesland.</b>".html_safe)
            else
              successors_ids << 'fetchme_later'
            end
          end
        end
        return col_name, successors_ids, 11, 5
      when 'fill_pl_successors'
        col_name = "successors"
        successors_ids = []
        cell.strip.split(',').each do |key|
          key.strip!
          next if key.length == 0
          statement = "SELECT a.id FROM plants a, model.reporting_periods rp " +
              "WHERE a.pl_key = ? AND (a.federal_state_id = ? OR a.handled_by_federal_state_id = ?)" +
              "  AND a.pl_state_active = 0" +
              "  AND a.rep_code = rp.key_report and rp.state = 1"
          zeilen = ActiveRecord::Base.execute_sql(statement, key, @federal_state_id, @federal_state_id)
          if zeilen.num_tuples == 1
            zeilen.each do |zeile|
              id = zeile['id']
              # puts id
              successors_ids << id
            end
          else
            if (@csv.temp_objects[[key, @federal_state_id, 'plants', 0]].nil? &&
                @csv.temp_objects_admin[[key, @federal_state_id, 'plants', 0]].nil?)
              self.add_error("<b>Fehler (Zeile #{@zeilennummer}): Anlagenvorgänger #{key} (#{uba_key}) " +
                                 "kann nicht gefunden werden. Entweder existiert er nicht oder er ist noch AKTIV oder " +
                                 "er liegt in einem anderen Bundesland.</b>".html_safe)
            else
              successors_ids << 'fetchme_later'
            end
          end
        end
        return col_name, successors_ids, 11, 5
      when 'fill_dp_successors'
        col_name = "successors"
        successors_ids = []
        cell.strip.split(',').each do |key|
          key.strip!
          next if key.length == 0
          statement = "SELECT a.id FROM discharge_points a, model.reporting_periods rp " +
              "where a.dp_key = ? AND (a.federal_state_id = ? OR a.handled_by_federal_state_id = ?)" +
              "  AND a.dp_state_active = 0" +
              "  AND a.rep_code = rp.key_report and rp.state = 1"
          zeilen = ActiveRecord::Base.execute_sql(statement, key, @federal_state_id, @federal_state_id)
          if zeilen.num_tuples == 1
            zeilen.each do |zeile|
              id = zeile['id']
              # puts id
              successors_ids << id
            end
          else
            if (@csv.temp_objects[[key, @federal_state_id, 'discharge_points', 0]].nil? &&
                @csv.temp_objects_admin[[key, @federal_state_id, 'discharge_points', 0]].nil?)
              self.add_error("<b>Fehler (Zeile #{@zeilennummer}): Einleitstellenvorgänger #{key} (#{uba_key}) kann nicht gefunden werden. Entweder existiert er nicht oder er ist noch AKTIV oder er liegt in einem anderen Bundesland.</b>".html_safe)
            else
              successors_ids << 'fetchme_later'
            end
          end
        end
        return col_name, successors_ids, 11, 5

      when 'fill_agg_plant1'
        # Suche nach dem Bundesland der Agglomeration
        # Hier muss nichts passieren, denn die Hauptschleife hat bereits @federal_state_id und _reference_id gefüllt
        return 'bl_code_agg', @federal_state_id, 11, 3

      when 'fill_agg_plant2'
        # Suche nach der Agglomerations-ID
        statement = "SELECT a.id FROM agglomerations a, model.reporting_periods rp " +
            "WHERE a.agg_key = ? AND (a.federal_state_id = ? OR a.handled_by_federal_state_id = ?)" +
            "  AND a.rep_code = rp.key_report and rp.state = 1"
        zeilen = ActiveRecord::Base.execute_sql(statement, cell, @federal_state_id, @federal_state_id)
        if zeilen.num_tuples == 1
          zeilen.each do |zeile|
            id = zeile['id']
            return 'agglomeration_id', id, 11, 4
          end
        end
        if (@csv.temp_objects[[cell, @federal_state_id, 'agglomerations', 0]].nil? &&
            @csv.temp_objects[[cell, @federal_state_id, 'agglomerations', 1]].nil? &&
            @csv.temp_objects_admin[[cell, @federal_state_id, 'agglomerations', 0]].nil? &&
            @csv.temp_objects_admin[[cell, @federal_state_id, 'agglomerations', 1]].nil?)
          self.add_error("<b>Fehler (Zeile #{@zeilennummer}): Agglomeration #{cell} (#{uba_key}) kann nicht gefunden werden.</b>".html_safe)
          return 'agglomeration_id', nil, 11, 4
        else
          return 'agglomeration_id', 'fetchme_later', 11, 4
        end

      when 'fill_agg_plant3'
        # Suche nach dem Bundesland der Anlage
        return 'bl_code_pl', @federal_state_reference_id, 11, 6

      when 'fill_agg_plant4'
        # Suche nach der Anlagen-ID
        if @federal_state_reference_id.blank?
          self.add_error("<b>Fehler (Zeile #{@zeilennummer}): Anlage #{cell} (#{uba_key}): Interner Fehler: Bundesland-Referenz (@federal_state_reference_id) kann nicht gefunden werden!</b>".html_safe)
          return nil, nil, nil, nil
        end
        statement = "SELECT a.id FROM plants a, model.reporting_periods rp " +
            "WHERE a.pl_key = ? AND (a.federal_state_id = ? OR a.handled_by_federal_state_id = ?)" +
            "  AND a.rep_code = rp.key_report AND rp.state = 1"
        zeilen = ActiveRecord::Base.execute_sql(statement, cell, @federal_state_reference_id, @federal_state_reference_id)
        if zeilen.num_tuples == 1
          zeilen.each do |zeile|
            id = zeile['id']
            return 'plant_id', id, 11, 8
          end
        end
        if (@csv.temp_objects[[cell, @federal_state_id, 'plants', 0]].nil? &&
            @csv.temp_objects[[cell, @federal_state_id, 'plants', 1]].nil? &&
            @csv.temp_objects_admin[[cell, @federal_state_id, 'plants', 0]].nil? &&
            @csv.temp_objects_admin[[cell, @federal_state_id, 'plants', 1]].nil?)
          self.add_error("<b>Fehler (Zeile #{@zeilennummer}): Anlage #{cell} (#{uba_key}) kann nicht gefunden werden.</b>".html_safe)
          return 'plant_id', nil, 11, 8
        else
          return 'plant_id', 'fetchme_later', 11, 8
        end

      when 'fill_plant_reference'
        # Wird nur für uba_key (bl_code_ref) verwendet um @federal_state_reference_id zu füllen
        return 'bl_code_ref', @federal_state_reference_id, 11, 6
      else
        self.add_error("<b>Fehler (Zeile #{@zeilennummer}): Funktion fehlt: get_joker_ids erweitern. " +
                           "Funktion: #{function} auf (#{uba_key}). Import der Tabelle #{@tablename} muss abgebrochen werden.</b>".html_safe)
      end
      return nil, nil, nil, nil
    end

    def get_id_from_misc_list(ref_values, cell, uba_key, object_eu_key)
      return nil, nil if cell.blank?
      cell.strip!
      foreign_table = ref_values['foreign_table']
      foreign_column = ref_values['foreign_column']
      if foreign_table == "plants" && foreign_column == "pl_key"
        # puts ref_values.inspect
        # puts "Cell is _#{cell}_ (this is pl_key, aka pl_id), bl_code (federal_state_reference_id) is #{@federal_state_reference_id}"
        if !@federal_state_reference_id
          self.add_warning("Hinweis (Zeile #{@zeilennummer}): Bundesland Kläranlage fehlt. Nehme Bundesland der Einleitstelle. (Tabelle #{@tablename})".html_safe)
          @federal_state_reference_id = @federal_state_id
        end
        if !@federal_state_id
          self.add_error("<b>Fehler (Zeile #{@zeilennummer}): Bundesland fehlt oder nicht interpretierbar. Import der Tabelle #{@tablename} muss abgebrochen werden!</b>".html_safe)
          return nil, nil
        end
        plant_ref_id = get_plant_id(cell, @federal_state_reference_id)
        return plant_ref_id, 8
      end
      return self.get_id_from_foreign_list(ref_values, cell, uba_key, object_eu_key), 1
    end

    # get_plant_id sucht NUR NOCH IN DER AKTUELLEN REPORTING PERIOD
    def get_plant_id(pl_key, federal_state_id)
      plant_id = nil
      statement = "SELECT p.id FROM plants p, model.reporting_periods rp
          WHERE p.pl_key = ? AND p.federal_state_id = ?
          AND p.rep_code = rp.key_report AND rp.state = 1"
      plant_zeilen = ActiveRecord::Base.execute_sql(statement, pl_key, @federal_state_id)
      if plant_zeilen.num_tuples > 0
        plant_zeilen.each do |plant_zeile|
          plant_id = plant_zeile['id']
          return plant_id
        end
      else
        if (@csv.temp_objects[[pl_key, federal_state_id, 'plants', 0]].nil? &&
            @csv.temp_objects[[pl_key, federal_state_id, 'plants', 1]].nil? &&
            @csv.temp_objects_admin[[pl_key, federal_state_id, 'plants', 0]].nil? &&
            @csv.temp_objects_admin[[pl_key, federal_state_id, 'plants', 1]].nil? )
          self.add_error("<b>Fehler (Zeile #{@zeilennummer}): Anlage #{pl_key} (pl_ID_ref), auf die von Einleitstelle " +
                             "verwiesen wird, kann nicht gefunden werden.</b>".html_safe)
          return nil
        else
          return "fetchme_later"
        end
      end
    end

    def get_id_from_list(ref_values, cell, uba_key, object_eu_key = 'unbekannt')
      foreign_table = ref_values['foreign_table']
      foreign_column = ref_values['foreign_column']

      # column = ref_values[foreign_column]
      # Leider finde ich die Werte der Liste nicht heraus. Habe es eine Stunde probiert.
      # Es ist besser, den []-Operator nicht zu überladen, dann kann man die Strukturen finden.
      # Oder man macht zur Dokumentation ein konkretes Beispiel, wie das Gen-Objekt zu verwenden ist.
      # Da meine schnelle Maschine beim Durchsuchen der gen-columns unter Vollast geht, ist es wohl sowieso
      # keine gute Idee, mit dem Gen-Objekt zu arbeiten.

      # dcell = cell.downcase
      dcell = cell.strip.downcase

      case foreign_table
      when 'l_ntype'
        return self.lookup_list_value(@avg_calc_methods, cell, dcell, uba_key, 'restrictive', object_eu_key)
      when 'l_federal_state'
        return self.lookup_list_value(@federal_states, cell, dcell, uba_key, 'restrictive', object_eu_key)
      when 'l_catchments'
        return self.lookup_list_value(@catchments, cell, dcell, uba_key, 'restrictive', object_eu_key)
      when 'l_coordinate_systems'
        return self.lookup_list_value(@coordinate_systems, cell, dcell, uba_key, 'restrictive', object_eu_key)
      when 'l_groundwater_body'
        return self.lookup_hashed_list_value_with_state(@mh_groundwater_bodies, cell, dcell, uba_key, "permissive", object_eu_key)
        # return self.lookup_list_value_with_state(@groundwater_bodies, cell, dcell, uba_key, "permissive")
      when 'l_meth'
        return self.lookup_meth_list_value(@methods, cell, dcell, uba_key, 'restrictive', object_eu_key)
      when 'l_objecttypes'
        return self.lookup_list_value(@object_types, cell, dcell, uba_key, 'restrictive', object_eu_key)
      when 'l_river_basin_districts'
        return self.lookup_list_value(@river_basin_districts, cell, dcell, uba_key, 'restrictive', object_eu_key)
      when 'l_failure_reasons'
        return self.lookup_list_value(@failure_reasons, cell, dcell, uba_key, 'restrictive', object_eu_key)
      when 'l_waters'
        return self.lookup_list_value(@receiver_types, cell, dcell, uba_key, 'restrictive', object_eu_key)
      when 'l_water_type'
        return self.lookup_list_value(@water_types, cell, dcell, uba_key, 'restrictive', object_eu_key)
      when 'l_fac_type'
        return self.lookup_list_value(@treatment_types, cell, dcell, uba_key, 'restrictive', object_eu_key)
      when 'l_water_body_code'
        return self.lookup_hashed_list_value_with_state(@mh_water_bodies, cell, dcell, uba_key, "permissive", object_eu_key)
        # return self.lookup_list_value_with_state(@water_bodies, cell, dcell, uba_key, "permissive")
      when 'l_wrrl_sub_units'
        return self.lookup_list_value(@wrrl_sub_units, cell, dcell, uba_key, 'restrictive', object_eu_key)
      end
      self.add_error("<b>Fehler: Funktion fehlt: Normale Liste implementieren (#{foreign_table}.#{foreign_column} = #{cell})</b>".html_safe)
      return nil
    end

    def lookup_list_value(active_record_list, cell, dcell, uba_key, lookupmode = 'restrictive', object_eu_key)
      if ct = active_record_list.detect {|ct1| ct1['key'].strip.downcase == dcell || ct1['description'].strip.downcase == dcell}
        return ct['id']
      end
      if lookupmode == "permissive"
        self.add_warning("<b>Hinweis (Zeile #{@zeilennummer}, EU-Key #{object_eu_key}): Listenwert #{cell} kann (#{uba_key}) nicht zugeordnet werden.</b>".html_safe)
      else
        self.add_error("<b>Fehler (Zeile #{@zeilennummer}, EU-Key #{object_eu_key}): Listenwert #{cell} kann (#{uba_key}) nicht zugeordnet werden. Import der Tabelle #{@tablename} muss abgebrochen werden.</b>".html_safe)
      end
      return nil
    end

    def lookup_meth_list_value(active_record_list, cell, dcell, uba_key, lookupmode, object_eu_key)
      if ct = active_record_list.detect {|ct1| ct1['key'].strip.downcase == dcell || ct1['description'].strip.downcase == dcell ||
          ct1['description'].strip.downcase[0, 1] == dcell}
        return ct['id']
      end
      if lookupmode == "permissive"
        self.add_warning("<b>Hinweis (Zeile #{@zeilennummer}, EU-Key #{object_eu_key}): Listenwert #{cell} kann (#{uba_key}) nicht zugeordnet werden.</b>".html_safe)
      else
        self.add_error("<b>Fehler (Zeile #{@zeilennummer}, EU-Key #{object_eu_key}): Listenwert #{cell} kann (#{uba_key}) nicht zugeordnet werden. Import der Tabelle #{@tablename} muss abgebrochen werden.</b>".html_safe)
      end
      return nil
    end

    def lookup_list_value_with_state(active_record_list, cell, dcell, uba_key, lookupmode, object_eu_key)
      # deprecated. use lookup_hashed_list_value_with_state instead.
      if ct = active_record_list.detect {|ct1| (ct1['federal_state_id'].blank? || ct1['federal_state_id'] == @federal_state_id) &&
          (ct1['key'].strip.downcase == dcell || ct1['description'].strip.downcase == dcell || ct1['eu_key'].strip.downcase == dcell)}
        return ct['id']
      end
      if lookupmode == "permissive"
        self.add_warning("<b>Hinweis (Zeile #{@zeilennummer}, EU-Key #{object_eu_key}): Listenwert #{cell} kann (#{uba_key}) nicht zugeordnet werden.</b>".html_safe)
      else
        self.add_error("<b>Fehler (Zeile #{@zeilennummer}, EU-Key #{object_eu_key}): Listenwert #{cell} kann (#{uba_key}) nicht zugeordnet werden. Import der Tabelle #{@tablename} muss abgebrochen werden.</b>".html_safe)
      end
      return nil
    end

    def lookup_hashed_list_value_with_state(mlt_hash, cell, dcell, uba_key, lookupmode, object_eu_key = 'unknown')
      # Wo wird der mlt_hash gefüllt: In create_mlthashes
      # @mh_groundwater_bodies und @mh_water_bodies sind das bislang.
      if mlt_hash[@federal_state_id]
        return mlt_hash[@federal_state_id][0][dcell] if mlt_hash[@federal_state_id][0][dcell]
        return mlt_hash[@federal_state_id][1][dcell] if mlt_hash[@federal_state_id][1][dcell]
        return mlt_hash[@federal_state_id][2][dcell] if mlt_hash[@federal_state_id][2][dcell]
      end

      # TODO: Auch Wasserkörper aus anderen Ländern erlauben zu importieren
      # Wir können die Wasserkörper nicht auf das Bundesland beschränken, da viele Einleistellen
      # in Wasserkörper des Nachbarlandes einleiten. Folglich müssen wir auf allen Bundesländern suchen:

      @federal_states.each do |fs|
        fs_id = fs['id']
        next if mlt_hash[fs_id].nil?
        return mlt_hash[fs_id][0][dcell] if mlt_hash[fs_id][0][dcell]
        return mlt_hash[fs_id][1][dcell] if mlt_hash[fs_id][1][dcell]
        return mlt_hash[fs_id][2][dcell] if mlt_hash[fs_id][2][dcell]
      end

      if lookupmode == "permissive"
        self.add_warning("<b>Hinweis (Zeile #{@zeilennummer}, EU-Key #{object_eu_key}): Listenwert #{cell} kann (#{uba_key}) nicht zugeordnet werden.</b>".html_safe)
      else
        self.add_error("<b>Fehler (Zeile #{@zeilennummer}, EU-Key #{object_eu_key}): Listenwert #{cell} kann (#{uba_key}) nicht zugeordnet werden. Import der Tabelle #{@tablename} muss abgebrochen werden.</b>".html_safe)
      end
      return nil
    end

    def get_id_from_foreign_list(ref_values, cell, uba_key, object_eu_key)
      foreign_table = ref_values['foreign_table']
      foreign_column = ref_values['foreign_column']
      dcell = cell.downcase.strip
      case foreign_table
      when 'l_region_code'
        lookupmode = "restrictive"
        # Der guckt leider weder nach Bundesland noch nach Gültigkeit. Daher wird der 2020-02 ersetzt:
        #   return self.lookup_list_value(@region_codes, cell, dcell, uba_key)
        return @mh_region_codes[@federal_state_id][1][dcell] if @mh_region_codes[@federal_state_id][1][dcell]
        return @mh_region_codes[@federal_state_id][0][dcell] if @mh_region_codes[@federal_state_id][0][dcell]
        # puts "\n##1.5 #{@mh_region_codes[@federal_state_id]}"
        # puts "\n##2.0, nichts für das Bundesland #{@federal_state_id} und Wert #{dcell} gefunden!"

        # Wir können die Regionen nicht auf das Bundesland beschränken, da z.B. Brandenburger
        # Kläranlagen Berliner Abwasser reinigen. Folglich müssen wir auf allen Bundesländern suchen:

        @federal_states.each do |fs|
          fs_id = fs['id']
          next if @mh_region_codes[fs_id].nil?
          return @mh_region_codes[fs_id][1][dcell] if @mh_region_codes[fs_id][1][dcell]
          return @mh_region_codes[fs_id][0][dcell] if @mh_region_codes[fs_id][0][dcell]
        end

        if lookupmode == "permissive"
          self.add_warning("<b>Hinweis (Zeile #{@zeilennummer}, EU-Key #{object_eu_key}): Der Listenwert \"#{cell}\" kann der Spalte \"#{uba_key}\" nicht zugeordnet werden.</b>".html_safe)
        else
          self.add_error("<b>Fehler (Zeile #{@zeilennummer}, EU-Key #{object_eu_key}): Der Listenwert \"#{cell}\" kann der Spalte \"#{uba_key}\" nicht zugeordnet werden.
Der Import sollte aufgrund fehlerhafter Daten in Tabelle #{@tablename} abgebrochen werden.</b>".html_safe)
        end
        return nil
      when 'l_cities'
        # Der guckt leider weder nach Bundesland noch nach Gültigkeit. Daher wird der 2020-03 ersetzt:
        #   return self.lookup_list_value(@cities, cell, dcell, uba_key)
        lookupmode = "restrictive"
        return @mh_cities[@federal_state_id][1][dcell] if @mh_cities[@federal_state_id][1][dcell]
        return @mh_cities[@federal_state_id][0][dcell] if @mh_cities[@federal_state_id][0][dcell]
        # puts "\n##1.6 #{@mh_cities[@federal_state_id]}"
        # puts "\n##1.7, nichts für das Bundesland #{@federal_state_id} und Wert #{dcell} gefunden!"

        # Wir können die Großstädte nicht auf das Bundesland beschränken, da z.B. Brandenburger
        # Kläranlagen Berliner Abwasser reinigen. Folglich müssen wir auf allen Bundesländern suchen:
        @federal_states.each do |fs|
          fs_id = fs['id']
          next if fs_id > 16   # Endistan hat keine Großstädte :-)
          return @mh_cities[fs_id][1][dcell] if @mh_cities[fs_id][1][dcell]
          return @mh_cities[fs_id][0][dcell] if @mh_cities[fs_id][0][dcell]
        end
        if lookupmode == "permissive"
          self.add_warning("<b>Hinweis (Zeile #{@zeilennummer}, EU-Key #{object_eu_key}): Der Listenwert \"#{cell}\" kann der Spalte \"#{uba_key}\" nicht zugeordnet werden.</b>".html_safe)
        else
          self.add_error("<b>Fehler (Zeile #{@zeilennummer}, EU-Key #{object_eu_key}): Der Listenwert \"#{cell}\" kann der Spalte \"#{uba_key}\" nicht zugeordnet werden.
Der Import sollte aufgrund fehlerhafter Daten in Tabelle #{@tablename} abgebrochen werden.</b>".html_safe)
        end
        return nil
      when 'l_compliance'
        return self.lookup_list_value(@compliances, cell, dcell, uba_key, 'restrictive', object_eu_key)
      when 'l_mix_calc'
        return self.lookup_list_value(@compliance_classes, cell, dcell, uba_key, 'restrictive', object_eu_key)
      when 'l_usage'
        return self.lookup_list_value(@usages, cell, dcell, uba_key, 'restrictive', object_eu_key)
      when 'l_inspire_ids'
        lookupmode = "restrictive"
        # @mh_inspire_ids[fsi][0][key] = id                  key hat die inspire_id
        # @mh_inspire_ids[fsi][1][description] = id          Text zur Inspire ID (e.g. "Lausitzer Wasser GmbH & Co. KG")
        # @mh_inspire_ids[fsi][2][eu_key] = id               eu_key hat inspire_id.downcase
        unless @mh_inspire_ids.nil?
          unless @mh_inspire_ids[@federal_state_id].nil?
            unless @mh_inspire_ids[@federal_state_id][0].nil?
              return @mh_inspire_ids[@federal_state_id][0][cell] if @mh_inspire_ids[@federal_state_id][0][cell]
            end
            unless @mh_inspire_ids[@federal_state_id][2].nil?
              return @mh_inspire_ids[@federal_state_id][2][dcell] if @mh_inspire_ids[@federal_state_id][2][dcell]
            end
          else
            puts "Warning: No hash elements for l_inspire_ids and federal_state_id #{@federal_state_id}"
          end
        else
          puts "Error in hash read l_inspire_ids: Hash is nil"
        end
        if lookupmode == "permissive"
          self.add_warning("<b>Hinweis (Zeile #{@zeilennummer}, EU-Key #{object_eu_key}): Der Listenwert \"#{cell}\" kann der Spalte \"#{uba_key}\" nicht zugeordnet werden.</b>".html_safe)
        else
          self.add_error("<b>Fehler (Zeile #{@zeilennummer}, EU-Key #{object_eu_key}): Der Listenwert \"#{cell}\" kann der Spalte \"#{uba_key}\" nicht zugeordnet werden.
Der Import sollte aufgrund fehlerhafter Daten in Tabelle #{@tablename} abgebrochen werden.</b>".html_safe)
        end
        return nil
      end
      self.add_error("<b>Fehler: Funktion fehlt: Foreign List implementieren (#{foreign_table}.#{foreign_column} = #{cell})</b>".html_safe)
      return nil
    end

    def get_bundesland_id(cell, uba_key)
      return nil unless cell
      cell.strip!
      return nil if cell == ''
      dcell = cell.downcase
      if c = @federal_states.detect {|c1| c1['key'].strip.downcase == dcell ||
          c1['description'].strip.downcase == dcell ||
          c1['name'].strip.downcase == dcell ||
          c1['federal_key'].strip.downcase == dcell}
        return c['id']
      end
      self.add_error("<b>Fehler (Zeile #{@zeilennummer}): (#{uba_key}): Bundesland '#{cell}' kann nicht identifiziert werden. Import der Tabelle #{@tablename} muss abgebrochen werden.</b>".html_safe)
      return nil
    end

    def fill_gen_columns(table_name)
      @gen = Generica.new(table_name, @user_id, true)
      @csv_columns.each do |uba_key|
        found = false
        @gen.columns.each do |gen_column|
          if gen_column['uba_key'] == uba_key
            if uba_key =~ /_hist$/u && gen_column['reference_type'].to_i == 5
              next
            end
            if gen_column.is_locked? && uba_key !~ /_eu_key/ && uba_key != 'rep_code'
              @csv.messages << "<b>Hinweis:</b> Das gesperrte Feld #{uba_key} wird beim Import ignoriert."
              @ignored_columns << uba_key
            end
            @gen_columns << gen_column
            found = true
            break
          end
        end
        if !found
          @can_be_imported = false
          @gen_columns << nil
          msg = "<b>Fehler(Zeile #{@zeilennummer}): Spaltennamen: Die Spalte (#{uba_key}) ist unbekannt.</b>"
          @csv.errors << msg
          @csv.num_errors = @csv.num_errors + 1
        end
      end
    end

    def detect_table
      # type is one of [nil, "agglomeration", "plant", "agglomeration_plant", "discharge_point", "federal_state_info"]
      @csv_columns = CSV::parse_line(@columns_line[1], { :col_sep => @csv.trennzeichen}) rescue []
      # @csv_columns.delete_if{|col| col.nil?}
      @zeilennummer = @columns_line[0]
      if bad_col = @csv_columns.find_index {|col| col.nil?}
        @csv.errors << "<b>Fehler: Tabelle hat leeren Spaltennamen in folgender Überschrift (in Zeile #{@zeilennummer}):</b>"
        @csv.errors << "<b>" + @columns_line[1] + "</b>"
        @csv.errors << "<b>Spalte #{bad_col + 1} ist leer. Leere Spaltennamen sind nicht erlaubt.</b>"
        @csv.errors << " "
        @csv.num_errors = @csv.num_errors + 1
        return nil, nil, nil
      end

      @csv_columns.collect! {|col| col.strip.downcase}
      # puts @csv_columns
      if (@csv_columns & ["bl_code_agg", "agg_key", "bl_code_pl", "pl_id"]) &&
          (@csv_columns & ["bl_code_agg", "agg_key", "bl_code_pl", "pl_id"]).length == 4
        table_name = "agglomerations_plants"
        object_singular = "Agglomeration-Anlage-Zuordnung"
        object_plural = "Agglomeration-Anlage-Zuordnungen"
        self.fill_gen_columns(table_name)
        return table_name, object_singular, object_plural
      end
      if (@csv_columns & ["dp_key", "dp_eu_key", "bl_code"]) &&
          (@csv_columns & ["dp_key", "dp_eu_key", "bl_code"]).length >= 2
        table_name = "discharge_points"
        object_singular = "Einleitstelle"
        object_plural = "Einleitstellen"
        self.fill_gen_columns(table_name)
        return table_name, object_singular, object_plural
      end
      if (@csv_columns & ["agg_key", "agg_eu_key", "bl_code"]) &&
          (@csv_columns & ["agg_key", "agg_eu_key", "bl_code"]).length >= 2
        table_name = "agglomerations"
        object_singular = "Agglomeration"
        object_plural = "Agglomerationen"
        self.fill_gen_columns(table_name)
        return table_name, object_singular, object_plural
      end
      if (@csv_columns & ["pl_id", "pl_eu_key", "bl_code"]) &&
          (@csv_columns & ["pl_id", "pl_eu_key", "bl_code"]).length >= 2
        table_name = "plants"
        object_singular = "Anlage"
        object_plural = "Anlagen"
        self.fill_gen_columns(table_name)
        return table_name, object_singular, object_plural
      end
      if (@csv_columns & ["bl_code", "s_sprod", "kka_number"]) &&
          (@csv_columns & ["bl_code", "s_sprod", "kka_number"]).length >= 2
        table_name = "federal_state_info"
        object_singular = "Bundeslandinformation"
        object_plural = "Bundeslandinformationen"
        self.fill_gen_columns(table_name)
        return table_name, object_singular, object_plural
      end
      @csv.errors << "Fehler: Tabelle konnte aus folgender Überschrift (in Zeile #{@zeilennummer}) nicht identifiziert werden:"
      @csv.errors << @columns_line[1]
      @csv.errors << "Eventuell ist ein Tippfehler in einer Spalte aufgetreten."
      @csv.num_errors = @csv.num_errors + 1
      return nil, nil, nil
    end

    def can_import?(fs_id)
      return true if theUser(@session).can_import?(fs_id)
      if federal_state_id = self.get_federal_id_by_key(fs_id)
        return true if theUser(@session).can_import?(federal_state_id)
      end
      return false
    end

    def get_operation_id_by_mode(mode)
      return 3 if mode == "overwrite"
      return 5 if mode == "overkill"
      return 4
    end

    def get_federal_key_by_id(federal_state_id)
      if c = @federal_states.detect {|c1| c1['id'] == federal_state_id}
        return c['key']
      end
      return nil
    end

    def get_federal_id_by_key(federal_key)
      if c = @federal_states.detect {|c1| c1['key'] == federal_key}
        return c['id']
      end
      return nil
    end

    def add_warning(message)
      @csv.errors << message
      @csv.num_warnings = @csv.num_warnings + 1
    end

    def add_error(message)
      @csv.errors << message.html_safe
      @csv.num_errors = @csv.num_errors + 1
      @line_can_be_imported = false
    end

  end
  # end class Tabelle

  def create_mlthashes
    puts "Start creating mlthashes #{now}"
    @mh_water_bodies = Hash.new
    @mh_groundwater_bodies = Hash.new
    @mh_region_codes = Hash.new
    @mh_cities = Hash.new
    @mh_inspire_ids = Hash.new
    @reporting_periods = ReportingPeriod.where(state: 1).take
    @date_from = @reporting_periods['date_from']
    @date_to = @reporting_periods['date_to']

    @cities.each do |city|
      fsi = city['federal_state_id']
      key = city['key'].strip.downcase
      description = city['description'].strip.downcase
      id = city['id']
      # Wenn date_from - date_to nicht Stichtag der Berichtsperiode umschließt, dann Zeile nicht merken, weil ungültig
      next unless @date_to <= city['date_to'] && @date_to >= city['date_from']
      next unless fsi
      next unless key.length >= 4
      if !@mh_cities[fsi]
        # puts "##6.0 fsi (neues Bundesland): #{fsi}"
        @mh_cities[fsi] = [{}, {}]
      end
      # puts "##7.0 @mh_region_codes lernt einen neuen Datensatz: fsi/key/descr. = #{fsi}/#{key}/#{description}"
      @mh_cities[fsi][0][key] = id
      @mh_cities[fsi][1][description] = id
    end

    @region_codes.each do |rc|
      fsi = rc['federal_state_id']
      key = rc['key'].strip.downcase
      description = rc['description'].strip.downcase
      id = rc['id']
      # Wenn date_from - date_to nicht Stichtag der Berichtsperiode umschließt, dann Zeile nicht merken, weil ungültig
      unless @date_to <= rc['date_to'] && @date_to >= rc['date_from']
        next
      end
      unless fsi
        next
      end
      unless key.length >= 4
        next
      end
      next unless @date_to <= rc['date_to'] && @date_to >= rc['date_from']
      if !@mh_region_codes[fsi]
        # puts "##6.0 fsi (neues Bundesland): #{fsi}"
        @mh_region_codes[fsi] = [{}, {}]
      end
      # puts "##7.0 @mh_region_codes lernt einen neuen Datensatz: fsi/key/descr. = #{fsi}/#{key}/#{description}"
      @mh_region_codes[fsi][0][key] = id
      @mh_region_codes[fsi][1][description] = id
    end

    @water_bodies.each do |wb|
      fsi = wb['federal_state_id']
      key = wb['key'].strip.downcase
      eu_key = wb['eu_key'].strip.downcase
      description = wb['description'].strip.downcase
      id = wb['id']
      # Wenn date_from - date_to nicht Stichtag der Berichtsperiode umschließt, dann Zeile nicht merken, weil ungültig
      next unless @date_to <= wb['date_to'] && @date_to >= wb['date_from']
      if !@mh_water_bodies[fsi]
        @mh_water_bodies[fsi] = [{}, {}, {}]
      end
      @mh_water_bodies[fsi][0][key] = id
      @mh_water_bodies[fsi][1][description] = id
      @mh_water_bodies[fsi][2][eu_key] = id
    end

    # Wenn er die @inspire_ids nicht hat, obwohl es eine entsprechende Class inspire_id.rb gibt,
    # dann fehlt im intialize der Class Tabelle: @mh_inspire_ids = @csv.mh_inspire_ids
    @inspire_ids.each do |iid|
      fsi = iid['federal_state_id']
      key = iid['inspire_id'].strip
      eu_key = iid['inspire_id'].strip.downcase
      description = iid['description'].strip
      id = iid['id']
      # Wenn date_from - date_to nicht Stichtag der Berichtsperiode umschließt, dann Zeile nicht merken, weil ungültig
      next unless @date_to <= iid['date_to'] && @date_to >= iid['date_from']
      if !@mh_inspire_ids[fsi]
        @mh_inspire_ids[fsi] = [{}, {}, {}]
      end
      @mh_inspire_ids[fsi][0][key] = id
      @mh_inspire_ids[fsi][1][description] = id
      @mh_inspire_ids[fsi][2][eu_key] = id
    end

    if nil
      @mh_water_bodies.each do |fsi, content|
        puts "@mh_water_bodies[#{fsi}]"
        content[0].each {|key, id| puts "  @mh_water_bodies[#{fsi}][0][#{key}] = #{id}"}
        content[1].each {|des, id| puts "  @mh_water_bodies[#{fsi}][1][#{des}] = #{id}"}
        content[2].each {|eu_key, id| puts "  @mh_water_bodies[#{fsi}][2][#{eu_key}] = #{id}"}
      end
    end

    @groundwater_bodies.each do |wb|
      fsi = wb['federal_state_id']
      key = wb['key'].strip.downcase
      eu_key = wb['eu_key'].strip.downcase
      description = wb['description'].strip.downcase
      id = wb['id']
      # Wenn date_from - date_to nicht Stichtag der Berichtsperiode umschließt, dann Zeile nicht merken, weil ungültig
      next unless @date_to <= wb['date_to'] && @date_to >= wb['date_from']
      if !@mh_groundwater_bodies[fsi]
        @mh_groundwater_bodies[fsi] = [{}, {}, {}]
      end
      @mh_groundwater_bodies[fsi][0][key] = id
      @mh_groundwater_bodies[fsi][1][description] = id
      @mh_groundwater_bodies[fsi][2][eu_key] = id
    end

    if nil
      @mh_groundwater_bodies.each do |fsi, content|
        puts "@mh_groundwater_bodies[#{fsi}]"
        content[0].each {|key, id| puts "  @mh_groundwater_bodies[#{fsi}][0][#{key}] = #{id}"}
        content[1].each {|des, id| puts "  @mh_groundwater_bodies[#{fsi}][1][#{des}] = #{id}"}
        content[2].each {|eu_key, id| puts "  @mh_groundwater_bodies[#{fsi}][2][#{eu_key}] = #{id}"}
      end
    end

    #      if ct = active_record_list.detect { |ct1| (ct1['federal_state_id'].blank? || ct1['federal_state_id'] == @federal_state_id) &&
    #            (ct1['key'].strip.downcase == dcell || ct1['description'].strip.downcase == dcell || ct1['eu_key'].strip.downcase == dcell) }
    #        return ct['id']
    #      end

    puts "Stop  creating mlthashes #{now}"
  end

end
