#encoding: utf-8
class Generica
  include ActiveModel::ForbiddenAttributesProtection
  include ActiveModel::AttributeAssignment
  attr_accessor :table, :columns, :user, :errors, :reporting_period

  include ApplicationHelper

  #private
  #def generica_params
  #  params.require(:generica).permit(:table, :columns, :user, :errors, :reporting_period)
  #end

  def initialize(main_table, user_id, for_import = false, for_export = false)
    @mlt_repair_test_1 = false
    @references = {}
    @table = main_table
    @user = User.find(user_id)
    @reporting_period = ReportingPeriod.get_current
    @columns = []
    @errors = nil
    head_query = "SELECT *
      FROM model.columns
      WHERE table_name = ? #{'AND visibility = 1' unless (for_import || for_export)}
      ORDER BY order_key"
    zwischenschritt = ActiveRecord::Base.execute_sql(head_query, @table)
    zwischenschritt.to_a.each do |col|
      # puts col, @columns.length
      @columns.push(column_obj = Column.new(col, @columns.length))
      unless col['foreign_table'].blank?
        if @references[col['foreign_table']].nil?
          @references[col['foreign_table']] = Reference.new(column_obj, @user.get_states_for_read)
        else
          @references[col['foreign_table']].add(column_obj)
        end
        column_obj.set_reference(@references[col['foreign_table']])
      end
    end
  end

  def lock
    # Ich hoffe das damit keine Datensätze gelockt werden können die in einem anderen Bundesland liegen, als das
    # für das gerade freigegeben wird.
    #
    # Erweiterung KKA: Nur Objekte locken, die an KOM gehen:
    lock_tables_map = {'agglomerations' => 'ag_com_report',
                       'plants' => 'pl_com_report',
                       'discharge_points' => 'dp_com_report'}

    sql_statement = "UPDATE #{@table} SET locked = 1 WHERE #{@table}.federal_state_id in (#{(@user.get_states_for_write).join(',')})"
    if lock_tables_map.key?(@table)
      sql_statement += " AND #{lock_tables_map[@table]} = 1"
    end
    ActiveRecord::Base.connection.execute(sql_statement)
  end

  def get_required
    return @columns.select do |c|
      c.is_only_required?
    end
  end

  def get_keys
    return @columns.select do |c|
      c.is_key_or_locked?
    end
  end

  def get_ints_and_floats
    return @columns.select do |c|
      ref_type = c['reference_type']
      refs = ['8', '81', '9', '91']
      refs.include?(ref_type)
    end
  end

  def get_csv
    return @columns.select {|c| c.is_csv?}
  end

  #returns all columns if selected is nil or empty
  def columns_for_list(selected = nil)
    if selected.nil? || selected.empty?
      cols = @columns
    else
      cols = selected.collect do |s|
        if s.match(/\d+/)
          @columns[s.to_i]
        else
          @columns.detect do |col|
            col['col_name'] == s
          end
        end
      end
      cols.compact!
    end
    return cols.select {|col| col.is_for_list?}
  end

  def columns_for_csv(selected = nil)
    if selected.nil? || selected.empty?
      cols = @columns
    else
      cols = selected.collect do |s|
        if s.match(/\d+/)
          @columns[s.to_i]
        else
          @columns.detect do |col|
            col['col_name'] == s
          end
        end
      end
    end
    cols.compact!
    return cols.select {|col| col.is_for_list? && !col.column_name_for_csv.nil?}
  end

  def columns_for_form
    @columns.select {|col| col['not_in_form'].to_i == 0}
  end

  #requires a block which method should be used to generate string out of column
  def find(conditions = '', condparams = [], selected_columns = nil, limit = '20', offset = '0', join = true, sort = false)
    return nil if selected_columns.nil?
    really_selected_columns = []
    with_void = false
    for col in selected_columns
      if col['col_name'] != 'void'
        really_selected_columns.push(col)
      else
        with_void = true
      end
    end
    data_query = "SELECT #{@table}.id,
      #{@table}.locked,
      #{@table}.federal_state_id as fed_state_for_perm,
      " + really_selected_columns.collect {|c| yield c}.compact.join(", \n")
    if with_void
      data_query << ", plant_federal_state_ids.key as void "
    end
    from_query = make_from
    join_query = ""
    if join
      @references.each do |key, ref|
        for col in ref.columns
          if col['reference_type'].to_i == 11 and col['col_name'] == 'void'
            join_query += "\n LEFT JOIN l_federal_state plant_federal_state_ids ON plant_federal_state_ids.id = #{col.joined_table_name}.federal_state_id"
          else
            join_query += "\n LEFT JOIN #{ref.table} #{col.joined_table_name} ON #{@table}.#{col['col_name']} = #{col.joined_table_name}.id"
          end
        end
      end
    end

    where_query = make_where(conditions)

    unless limit == :all
      start = (offset.to_i > 0) ? offset.to_i : 0
      amount = (limit.to_i > 0) ? limit.to_i : DataController::STANDARDLISTENLAENGE
      limit_query = "\n LIMIT #{amount} OFFSET #{start}"
    else
      limit_query = ""
    end

    # 2022-01-31, mlt: Sortierung, damit die Kunden sich freuen und damit man Beispielexporte mit wenigen
    # Zeilen produzieren kann, bei denen Agglomerationen, Anlagen und Einleistellen einander entsprechen.
    # (Sonst hat man 10000 Zeilen Salat und muss die zueinander gehörenden Zeilen einzeln herauspicken.)
    order_by = ''
    if sort
      case
      when @table == 'agglomerations'
        puts 'agglomerations'
        order_by = ' ORDER BY agglomerations.agg_eu_key'
      when @table == 'plants'
        puts 'plants'
        order_by = ' ORDER BY plants.pl_eu_key'
      when @table == 'discharge_points'
        puts 'discharge_points'
        order_by = ' ORDER BY discharge_points.dp_eu_key'
      when @table == 'federal_state_info'
        puts 'federal_state_info'
        order_by = ' ORDER BY l_federal_state0.federal_key'
      else
        puts 'default switch'
      end
    end

    data_query << from_query << join_query << where_query << limit_query << order_by

    if condparams == nil || condparams.empty?
      result_set = ActiveRecord::Base.connection.execute(data_query)
    else
      # Die Schreibweise in der Query ist hier i.a.
      #   SELECT * FROM x WHERE id=?
      # und sollte sein
      #   SELECT * FROM x WHERE id=$1
      n = 1
      # Das weiter unten ausgeführte Statement brachte keine Ergebnisse bei condparams.to_s: ["DE2020", "%thal%"]
      # Was war passiert: Die folgende Schleife hat die ? von hinten nach vorne gefunden
      # und somit dem 1. Paramter $2 und dem 2. Parameter $1 gegeben.
      # Das konnte folglich keine Treffer geben.
      # Das war das Problem: while data_query =~ /^((.|\n)*)\?((.|\n)*)$/
      # Die Lösung: Den ersten Sub-Match non-greedy machen, also aus * *? machen:
      while data_query =~ /^((.|\n)*?)\?((.|\n)*)$/
        data_query = "#$1\$#{n}#$3"
        n += 1
      end
      raw_connection = ActiveRecord::Base.connection.raw_connection
      # puts "raw_connection.conninfo: #{raw_connection.conninfo}"
      # puts "raw_connection.methods: #{raw_connection.methods}"
      # puts "data_query: #{data_query}"
      # puts "condparams.to_s: #{condparams.to_s}"
      # The connection knows exec_params ... at least
      result_set = raw_connection.exec_params(data_query, condparams)
      # puts "result_set.check: #{result_set.check}"
      # puts "result_set.result_status: #{result_set.result_status}"
      # puts "result_set.res_status(#{result_set.result_status}): #{result_set.res_status(result_set.result_status)}"
      # puts "result_set.error_message #{result_set.error_message}"

      # Ori: result_set = ActiveRecord::Base.connection.raw_connection.exec_params(data_query, condparams)
      # puts "Length of result_set: #{result_set.cmd_tuples}"
    end

    # result_set.each do |row|
    #   puts row.to_s
    # end

    result_set = result_set.collect {|row| Record.new(row, self, selected_columns)}

    return result_set
  end

  def find_for_csv(conditions = '', condparams = [], column_names_or_pos = nil, limit = '20', offset = '0', sort = TRUE)
    selected_columns = self.columns_for_csv(column_names_or_pos)
    tmp_result_set = find(conditions, condparams, selected_columns, limit, offset, true, sort) {|c| c.column_name_for_csv} #column_name_for_csv might be nil for cross_references, those should be kicked out
    tmp_active = []
    tmp_inactive = []
    tmp_result_set.each do |row|
      tmp_rec = Record.new(row, self, selected_columns)
      tmp_state = tmp_rec['agg_state_active'] || tmp_rec['pl_state_active'] || tmp_rec['dp_state_active']
      if tmp_state.nil? || tmp_state == 'JA'
        tmp_active.push(tmp_rec)
      else
        tmp_inactive.push(tmp_rec)
      end
    end
    result_set = tmp_inactive.concat(tmp_active)
    add_count(conditions)
    return result_set
  end

  #def find_all(conditions = '', column_names_or_pos = nil, limit = '20', offset='0')
  #  selected_columns = self.columns_for_list(column_names_or_pos)
  #  result_set = find(conditions,[],selected_columns,limit,offset,true){|c| c.to_s}
  #                  .collect { |row| Record.new(row, self, selected_columns) }
  #  add_count(conditions)
  #  return result_set
  #end

  # gibt dem array die zusätzliche methode rec_count, die die komplette anzahl der einträge ohne limit wiedergibt
  # wenn du weisst wie das kürzer geht, nur zu
  def add_count(result_set, conditions = '')
    count_query = "SELECT count(id) AS num"
    count_query << make_from
    count_query << make_where(conditions)
    count = ActiveRecord::Base.connection.execute(count_query)[0]['num']
    Generica.add_count_custom(result_set, count)
  end

  def self.add_count_custom(result_set, fixed_count)
    def result_set.rec_count=(count)
      @count = count
    end

    def result_set.rec_count
      return @count
    end

    result_set.rec_count = fixed_count
  end

  def make_from
    return "\n  FROM #{@table}"
  end

  def make_where(conditions = '')
    "\n  WHERE " << self.get_federal_state_where() << conditions
  end

  def all(conditions = '', condition_parameters = [], cols = nil, limit = '20', offset = '0', only_count = false)
    sel_columns = self.columns_for_list(cols)
    data_query = "SELECT #{@table}.id, #{@table}.federal_state_id as fed_state_for_perm, " + sel_columns.join(", \n")
    count_query = "SELECT count(id) as num"
    from_query = "\n  FROM #{@table}"
    join_query = ""
    @references.each do |key, ref|
      for col in ref.columns
        join_query += "\n LEFT JOIN #{ref.table} #{col.joined_table_name} ON #{@table}.#{col['col_name']} = #{col.joined_table_name}.id"
      end
    end

    where_query = "\n  WHERE " << self.get_federal_state_where() << conditions

    unless limit == :all
      start = (offset.to_i > 0) ? offset.to_i : 0
      amount = (limit.to_i > 0) ? limit.to_i : DataController::STANDARDLISTENLAENGE
      limit_query = "\n LIMIT #{amount} OFFSET #{start}"
    else
      limit_query = ""
    end

    order = self.list_sortings
    order_query = ""
    if order.first.is_a? Sorting
      # muss in sel_columns sein, sonst kann man nicht danach Sortieren
      col_dict = sel_columns.collect {|x| x.column_name_for_sorting}
      order.select! {|o| col_dict.include?(o.col_name)}

      # TODO: BuLa Sortierung nach Name und nicht nach ID!
      if (!order.nil? && !order.empty?)
        order_query << order.collect {|o| (o.col_name == "federal_state_id" ? o.table_name + "." : "") + o.col_name + (o.desc ? " DESC" : "")}.inject {|str, o| str << (", " + o)}
      end
    else
      order_query << order.inject {|str, o| str << (", " + o)}
    end

    if (!order_query.nil? && !order_query.empty?)
      order_query = "\n ORDER BY " + order_query
    end

    data_query << from_query << join_query << where_query << order_query << limit_query
    count_query << from_query << where_query

#    puts "test"+condition_parameters.unshift(data_query).inspect

    result_set = only_count ? [] : ActiveRecord::Base.execute_sql(data_query, *condition_parameters).collect {|row| Record.new(row, self, sel_columns)}

# gibt dem array die zusätzliche methode rec_count, die die komplette anzahl der einträge ohne limit wiedergibt
# wenn du weisst wie das kürzer geht, nur zu
    count = ActiveRecord::Base.execute_sql(count_query, *condition_parameters)[0]['num']
    Generica.add_count_custom(result_set, count)

    return result_set, order
  end

  def find_for_edit(id, global = false)
    return nil if id.nil?
    cols = columns_for_form
    base_query = "SELECT id,
                         locked,
                         federal_state_id as fed_state_for_perm,
                         handled_by_federal_state_id as handled_fed_state_for_perm,
    " + cols.collect {|p| p.column_name_for_edit}.join(', ') +
        " FROM #{@table}"
    base_query += "
       WHERE #{@table}.id = ? "
    query_result = ActiveRecord::Base.execute_sql(base_query, id)
    return nil if query_result.num_tuples == 0
    result = Record.new(query_result[0], self, cols)
    unless global
      return nil unless @user.get_states_for_read.include?(result['fed_state_for_perm'].to_i) || @user.get_states_for_read.include?(result['handled_fed_state_for_perm'].to_i)
    end
    return result
  end

  def update(params)
    return false if params[:id].nil?
    new = Record.new(params, self, nil)
    new.validate_update
    if new.errors.empty?
      cols, vals = new.columns_and_values_for_update
      sql_query = "UPDATE #{@table} SET #{cols.join(', ')} WHERE id = ? RETURNING id"
      begin
        result = ActiveRecord::Base.execute_sql(sql_query, *vals, params[:id]).to_a
        if result[0]['id'].to_i > 0 #result[0] existiert immer, da die id in validate_update überprüft wird
          return find_for_edit(result[0]['id'])
        else
          new.errors.push("Der Datensatz konnte nicht gespeichert werden")
        end
      rescue ActiveRecord::StatementInvalid => err
        puts "ERROR in update statement: #{err.to_s}"
        new.errors.push("Beim Ändern (update) der Datenbank ist ein unerwarteter Fehler aufgetreten")
      rescue Exception => err
        puts "ERROR in update: #{err.to_s}"
        new.errors.push("Es ist ein unerwarteter Fehler aufgetreten")
      end
    else
      new.errors.unshift("Der Datensatz wurde auf Grund von Fehlern nicht gespeichert")
    end
    return new.merge_with_db
  end


  def insert(params)
    new = Record.new(params, self, nil)
    new.validate_insert
    if new.errors.empty?
      cols, vals = new.columns_and_values_for_insert
      sql_query = "INSERT INTO #{@table} (#{cols.join(',
      ')}) VALUES (#{vals.collect {|v| '?'}.join(',
      ')}) RETURNING id"
      begin
        result = ActiveRecord::Base.execute_sql(sql_query, *vals).to_a
        if result[0]['id'].to_i > 0
          new = find_for_edit(result[0]['id'])
          new.successes.push("Der Eintrag wurde erfolgreich erstellt")
        else
          new.errors.push("Beim Speichern des neuen Satzes trat ein Fehler auf. Bitte melden Sie den Fehler.")
        end
      rescue ActiveRecord::StatementInvalid => db_error
        if db_error.message.downcase.include?('unique-constraint') or db_error.message.downcase.include?('unique constraint')
          new.errors.push(["Der Eintrag besteht bereits."])
        else
          new.errors.push("Beim Schreiben (INSERT) in die Datenbank ist ein unerwarteter Fehler aufgetreten: #{db_error.message}")
        end
      rescue
        new.errors.push("Es ist ein unerwarteter Fehler aufgetreten")
      end
    end
    return new
  end

  def update_plant_agg_percent(agglomeration_id, plant_id, percent)
    if (percent.to_f <= 0.00 or percent.to_f > 100.0)
      return false
    end
    begin
      sql = 'UPDATE agglomerations_plants SET agg_percent_plant = ? WHERE agglomeration_id = ? AND plant_id = ?'
      ActiveRecord::Base.execute_sql(sql, percent, agglomeration_id, plant_id)
      return true
    rescue Exception => err
      return false
    end
  end

  def insert_plant_to_agglomeration(agglomeration_id, plant_id, percent, plants_controller = false)
    if plants_controller == true
      plant = self.find_for_edit(plant_id)
      agglomerations_gen = Generica.new('agglomerations', @user.id)
      agglomeration = agglomerations_gen.find_for_edit(agglomeration_id)
    else
      agglomeration = self.find_for_edit(agglomeration_id)
      plants_gen = Generica.new('plants', @user.id)
      plant = plants_gen.find_for_edit(plant_id, true)
    end
    if percent.to_f <= 0 || percent.to_f > 100
      return false, "Die Prozentzahl ist ungültig, sie muss echt größer als 0 und kleiner als 100 sein."
    end
    unless plant && agglomeration
      return false, "Die Agglomeration oder Anlage existiert nicht in der Datenbank."
    end
    cells = self.default_built + self.insert_built
    cells.push({:col => 'plant_id', :val => plant.id})
    cells.push({:col => 'agglomeration_id', :val => agglomeration.id})
    cells.push({:col => 'agg_percent_plant', :val => percent.sub(',', '.')})
#    cells.push({:col => 'date_from', :val => '19000101'})
#    cells.push({:col => 'date_to',   :val => '21001231'})
#    cells.push({:col => 'time_from', :val => '0'})
#    cells.push({:col => 'time_to',   :val => '240000'})
    sql_ref = "INSERT INTO agglomerations_plants (#{cols_from_ary(cells)})
                VALUES (#{cells.collect {|v| '?'}.join(',')}) RETURNING id"
    begin
      res = ActiveRecord::Base.execute_sql(sql_ref, *(cells.collect {|v| v[:val]})).to_a
    rescue ActiveRecord::StatementInvalid => db_error
      if db_error.message.downcase.include?('unique-constraint') or db_error.message.downcase.include?('unique constraint')
        begin
          ActiveRecord::Base.execute_sql("UPDATE agglomerations_plants SET agg_percent_plant = ? WHERE plant_id = ? AND agglomeration_id = ? RETURNING id", percent.sub(',', '.'), plant.id, agglomeration.id)
          return true, "Der Eintrag wurde erfolgreich aktualisiert."
        rescue
          return false, "Der Eintrag besteht bereits und konnte nicht aktualisiert werden."
        end
      end
      return false, "Beim schreiben in die Datenbank is ein unerwarteter Fehler aufgetreten"
    rescue Exception => err
      return false, "Es ist ein unerwarteter Fehler aufgetreten: #{err.to_s}"
    end
    if res[0]['id'].to_i > 0
      return true, 'Die Agglomeration wurde der Anlage erfolgreich zugewiesen.' if plants_controller == true
      return true, 'Die Anlage wurde der Agglomeration erfolgreich zugewiesen.'
    else
      return false, "Es ist ein unerwarteter Fehler aufgetreten"
    end
  end

  def delete_plant_from_agglomeration(agglomeration_id, plant_id)
    unless agglomeration_id.to_i > 0 && plant_id.to_i > 0
      return false, "Konnte nicht gelöscht werden, die IDs sind ungültig."
    end
    sql_ref = "DELETE FROM agglomerations_plants
      WHERE agglomeration_id = ?
      AND   plant_id = ?
      RETURNING id"
    res = ActiveRecord::Base.execute_sql(sql_ref, agglomeration_id, plant_id).to_a
    if res.length > 0
      return true
    else
      return false, "Es wurden keine Einträge zum Löschen gefunden."
    end
  end

  def set_plant_of_discharge_point(discharge_point_id, plant_id, plant_controller = false)
    if plant_controller == true
      plant = self.find_for_edit(plant_id)
      discharge_points_gen = Generica.new('discharge_points', @user.id)
      discharge_point = discharge_points_gen.find_for_edit(discharge_point_id)
    else
      discharge_point = self.find_for_edit(discharge_point_id)
      plants_gen = Generica.new('plants', @user.id)
      plant = plants_gen.find_for_edit(plant_id, true)
    end

    unless plant && discharge_point
      return false, "Die Einleitstelle oder Anlage existiert nicht in der Datenbank."
    end

    res = ActiveRecord::Base.execute_sql("UPDATE discharge_points SET plant_id = ? WHERE id = ? RETURNING id", plant.id, discharge_point.id)
    return true, "Einleitstelle der Anlage erfolgreich hinzugefügt" if plant_controller == true
    return true, "Anlage der Einleitstelle erfolgreich aktualisiert"

  end

  def delete_plant_from_discharge_point(discharge_point_id, plant_id, plants_controller = false)
    unless discharge_point_id.to_i > 0 && plant_id.to_i > 0
      return false, "Konnte nicht gelöscht werden, die IDs sind ungültig."
    end
    sql_ref = "UPDATE discharge_points
      SET plant_id = NULL
      WHERE id = ?
      RETURNING id"
    res = ActiveRecord::Base.execute_sql(sql_ref, discharge_point_id).to_a
    if res.length > 0
      return true
    else
      return false, "Es wurden keine Einträge zum Löschen gefunden."
    end
  end

  #auskommentiert weil es meiner Meinung nach nicht mehr benötigt wird
  #  def delete_plants_or_agglomerations(condition)
  #    sql_ref = "DELETE FROM agglomerations_plants
  #      WHERE #{condition}
  #      RETURNING id"
  #    res = ActiveRecord::Base.connection.execute(sql_ref).to_a
  #    return res.length
  #  end


  def delete(id)
    delete_errors = []
    # check permissions on delete
    entry = find_for_edit(id)
    if entry.nil?
      return false, ['Der Eintrag existiert nicht oder Sie haben keine Berechtigung zu dieser Operation']
    end
    case @table
      when 'agglomerations' then
        # check for plants
        unless check_that_none("SELECT id FROM agglomerations_plants WHERE agglomeration_id = ?", id)
          delete_errors.push("Es sind noch Anlagen zugeordnet.<br />
          Bearbeiten Sie bitte die zu entfernende Agglomeration vorher und löschen Sie alle Zuordnungen zu Anlagen.<br />
          Denken Sie bitte daran, auch die entsprechenden Anlagen zu löschen, sollten diese ebenfalls fehlerhaft sein.".html_safe)
        end
      when 'plants' then
        # check for discharge_points
        unless check_that_none("SELECT id FROM discharge_points WHERE plant_id = ?", id)
          delete_errors.push("Es verweisen noch Einleitstellen auf diese Anlage.<br />
          Betroffene Einleitstellen finden Sie in der Tabellendarstellung der Anlagen unter<br />
          Spalte 'zug. Einleitstellen' der zu löschenden Anlage.<br />
          Bearbeiten Sie bitte die Einleitstellen und entfernen Sie dort die Zuordnung(en) zu dieser Anlage.<br />
          Denken Sie bitte daran, auch die entsprechende(n) Einleitstelle(n) zu löschen, sollte(n) diese ebenfalls fehlerhaft sein.".html_safe)
        end
        # check for agglomerations
        unless check_that_none("SELECT id FROM agglomerations_plants WHERE plant_id = ?", id)
          delete_errors.push("Es sind noch Agglomerationen zugeordnet.<br />
          Betroffene Agglomerationen finden Sie in der Spalte 'Agglomerationen'.<br />
          Entfernen Sie bitte vor dem Löschen unter 'Agglomeration bearbeiten' die Zuordnungen zu dieser Anlage.<br />
          Denken Sie bitte daran, die entsprechenden Agglomerationen zu löschen, sollten diese auch fehlerhaft sein.".html_safe)
        end
    end
    # check for prede- and successors
    unless @table == "federal_state_info"
      unless check_that_none("SELECT id FROM #{@table}_successors WHERE #{@table.chop}_succ_id = ?", id)
        delete_errors.push("Es sind noch Vorgänger zugeordnet.<br />
            Entfernen Sie bitte vor dem Löschen unter 'Bearbeiten' alle Zuordnungen zu Vorgängern.".html_safe)
      end
      unless check_that_none("SELECT id FROM #{@table}_successors WHERE #{@table.chop}_pre_id = ?", id)
        delete_errors.push("Es sind noch Nachfolger zugeordnet.<br />
            Betroffene Nachfolger müssten Ihnen bekannt sein. An sich dürften keine Nachfolger für einen neu erstellten Eintrag existieren.<br />
            Entfernen Sie bitte vor dem Löschen unter 'Bearbeiten' der jeweiligen Nachfolger alle Zuordnungen zu dem zu löschenden Vorgängereintrag.<br />
            Denken Sie bitte daran, auch den oder die Nachfolger zu löschen, sollten diese ebenfalls fehlerhaft sein.".html_safe)
      end
    end
    if delete_errors.length == 0
      sql_command = "DELETE FROM #{@table} WHERE id = ? RETURNING id"
      res = ActiveRecord::Base.execute_sql(sql_command, id).to_a
      if res.length > 0
        return true, delete_errors
      else
        return false, delete_errors.push("Es wurden keine Einträge zum Löschen gefunden.")
      end
    else
      return false, delete_errors
    end
  end

  def check_that_none(*sql_query)
    return ActiveRecord::Base.execute_sql(*sql_query).to_a.length == 0
  end

  def insert_predecessor(pre_id, succ_id, prefix = nil)
    prefix ||= @table.chop
    succ = self.find_for_edit(succ_id)
    pre = self.find_for_edit(pre_id, true)
    unless pre && succ
      return false, "Der Vorgänger oder Nachfolger existiert nicht in der Datenbank."
    end
    cells = self.default_built + self.insert_built
    cells.push({:col => prefix + '_pre_id', :val => pre.id})
    cells.push({:col => prefix + '_succ_id', :val => succ.id})
    sql_ref = "INSERT INTO #{@table}_successors (#{cols_from_ary(cells)})
                VALUES (#{cells.collect {|v| '?'}.join(',')}) RETURNING id"
    begin
      res = ActiveRecord::Base.execute_sql(sql_ref, *(cells.collect {|v| v[:val]})).to_a
    rescue ActiveRecord::StatementInvalid => db_error
      if db_error.message.downcase.include?('unique-constraint') or db_error.message.downcase.include?('unique constraint')
        return false, "Der Eintrag besteht bereits."
      end
      return false, "Beim schreiben in die Datenbank is ein unerwarteter Fehler aufgetreten"
    rescue
      return false, "Es ist ein unerwarteter Fehler aufgetreten"
    end
    if res[0]['id'].to_i > 0
      return true
    else
      return false
    end
  end

  def delete_predecessor(pre_id, succ_id, prefix = nil)
    prefix ||= @table.chop
    unless pre_id.to_i > 0 && succ_id.to_i > 0
      return false, "Konnte nicht gelöscht werden, die IDs sind ungültig."
    end
    sql_ref = "DELETE FROM #{@table}_successors
      WHERE #{prefix}_pre_id = ?
      AND   #{prefix}_succ_id = ?
      RETURNING id"
    res = ActiveRecord::Base.execute_sql(sql_ref, pre_id, succ_id).to_a
    if res.length > 0
      return true
    else
      return false, "Es wurden keine Einträge zum Löschen gefunden."
    end
  end

  def cols_from_ary(array)
    array.collect {|e| e[:col]}.join(',
      ')
  end

  def vals_from_ary(array)
    array.collect {|e| e[:val]}.join(',
      ')
  end

  def default_built
    default_pairs = Array.new()
    default_pairs.push({:col => 'state', :val => '0'})
    default_pairs.push({:col => 'owner_id', :val => @user.id.to_s})
    default_pairs.push({:col => 'operation_id', :val => '2'})
    default_pairs.push({:col => 'date_create', :val => current_date_number})
    default_pairs.push({:col => 'time_create', :val => current_time_number})
    #default_pairs.push({:col => 'date_from',   :val => '19000101'})
    #default_pairs.push({:col => 'date_to',     :val => '21001231'})
    #default_pairs.push({:col => 'time_from',   :val => '0'})
    #default_pairs.push({:col => 'time_to',     :val => '240002'})

    return default_pairs
  end

  def insert_built
    ins_pairs = Array.new()
    date_from = @reporting_period.date_from
    date_to = @reporting_period.date_to
    time_from = '0'
    time_to = '240000'
    ins_pairs.push({:col => 'creator_id', :val => @user.id.to_s})
    ins_pairs.push({:col => 'date_from', :val => date_from})
    ins_pairs.push({:col => 'time_from', :val => time_from})
    ins_pairs.push({:col => 'date_to', :val => date_to})
    ins_pairs.push({:col => 'time_to', :val => time_to})
    return ins_pairs
  end

  def get_federal_state_where()
    where = " (#{@table}.federal_state_id in (" + @user.get_states_for_read.join(", ") + ") "

    if %w(agglomerations plants discharge_points).include?(@table)
      where << " or #{@table}.handled_by_federal_state_id in (" + @user.get_states_for_read.join(", ") + ") )"
    else
      where << ")"
    end
    return where
  end

  def get_map_urls(id)
    base_query = "SELECT enda_map_urls(geo_wgs84) as map_urls
                    FROM #{@table}"
    base_query += "
       WHERE #{@table}.id = ? "
    query_result = ActiveRecord::Base.execute_sql(base_query, id)
    return nil if query_result.num_tuples == 0
    return nil if query_result[0]['map_urls'] == nil
    return query_result[0]['map_urls'].html_safe
  end

  def get_prev_report(id)
    sql_statement = "SELECT pl.ido, pl.rep_code
      FROM #{@table} pl
      WHERE pl.id = ? LIMIT 1"
    query_result = ActiveRecord::Base.execute_sql(sql_statement, id)
    return nil if query_result.num_tuples == 0
    ido = query_result[0]['ido']
    rep_code = query_result[0]['rep_code']

    sql_statement = "SELECT pl.id, pl.rep_code
      FROM #{@table} pl
      WHERE pl.rep_code < ?
      AND pl.ido = ?
      ORDER BY pl.rep_code DESC
      LIMIT 1"
    query_result = ActiveRecord::Base.execute_sql(sql_statement, rep_code, ido)
    return nil if query_result.num_tuples == 0
    report = query_result[0]
    return report
  end

  def get_next_report(id)
    sql_statement = "SELECT pl.ido, pl.rep_code
      FROM #{@table} pl
      WHERE pl.id = ? LIMIT 1"
    query_result = ActiveRecord::Base.execute_sql(sql_statement, id)
    return nil if query_result.num_tuples == 0
    ido = query_result[0]['ido']
    rep_code = query_result[0]['rep_code']

    sql_statement = "SELECT pl.id, pl.rep_code
      FROM #{@table} pl
      WHERE pl.rep_code > ?
      AND pl.ido = ?
      ORDER BY pl.rep_code ASC LIMIT 1"
    query_result = ActiveRecord::Base.execute_sql(sql_statement, rep_code, ido)
    return nil if query_result.num_tuples == 0
    report = query_result[0]
    return report
  end

  def list_sortings
    list = @user.sortings.select {|s| s.table_name == @table}
    if list.empty?
      list = case @table
               when 'agglomerations' then
                 ['agglomerations.federal_state_id', 'agglomerations.agg_key']
               when 'plants' then
                 ['plants.federal_state_id', 'plants.pl_key']
               when 'discharge_points' then
                 ['discharge_points.federal_state_id', 'discharge_points.dp_key']
               when 'federal_state_info' then
                 ['federal_state_info.federal_state_id']
             end
    else

    end
    return list
  end

  class Column
    def initialize(values, position)
      @values = values
      @pos = position
    end

    def pos
      return @pos
    end

    def set_reference(reference)
      @reference = reference
    end

    def [](value)
      return @values[value]
    end

    def is_csv?
      !@values['uba_key'].nil?
    end

    def has_select?
      !@values['foreign_table'].nil?
    end

    def is_locked?
      @values['field_mode'].to_i == 3
    end

    def is_required?
      @values['field_mode'].to_i >= 1
    end

    def is_only_required?
      @values['field_mode'].to_i == 1
    end

    def is_key_or_locked?
      @values['field_mode'].to_i >= 2
    end

    def is_bool?
      @values['reference_type'].to_i == 3
    end

    def is_integer?
      [8, 81].include?(@values['reference_type'].to_i)
    end

    def is_double?
      [9, 91].include?(@values['reference_type'].to_i)
    end

    def is_date?
      @values['reference_type'].to_i == 4
    end

    def is_m_n_relation?
      @values['reference_type'].to_i == 5
    end

    def to_s
      return column_name_for_list + ' as ' + @values['col_name']
    end

    def is_for_list?
      return @values['not_in_list'].to_i == 0
    end

    def column_name_for_sorting
      return @values['col_name']
    end

    def column_name_for_edit
      return case @values['reference_type'].to_i
               when 5 then
                 surround('id') + ' as ' + @values['col_name']
               when 4 then
                 'risa_int2de_date(' + @values['col_name'] + ') as ' + @values['col_name']
               else
                 @values['col_name']
             end
    end

    def column_name_for_csv
      returnValue = nil
      case @values['reference_type'].to_i
      when 5 then
        if @values['col_name'] == 'successors'
          returnValue = (successor_schnipsel + ' as successors')
        elsif @values['col_name'] == 'plant_id'
          returnValue = "#{joined_table_name}.#{@values['foreign_column']} as #{@values['col_name']}"
        end
      when 1, 10 then
        returnValue = "#{joined_table_name}.#{@values['foreign_column']} as #{@values['col_name']}"
      when 13 then
        returnValue = "#{joined_table_name}.#{@values['foreign_column']} as #{@values['col_name']}, plant_federal_state_ids.key as plant_fed_state"
      when 4 then
        returnValue = "risa_int2de_date(#{@values['table_name'] + '.' + @values['col_name']}) as #{@values['col_name']}"
      when 3 then
        returnValue = "enda_int2boolstring_de(#{@values['table_name'] + '.' + @values['col_name']}) as #{@values['col_name']}"
      when 2, 6, 12 then
        returnValue = "#{joined_table_name}.key as #{@values['col_name']}"
      else
        if @values['reference_type'].to_i == 11 and @values['col_name'] == 'void'
          returnValue = "plant_federal_state_ids.key as #{@values['col_name']} as bl_code_ref"
        end
        returnValue =@values['table_name'] + '.' + @values['col_name']
      end
      return returnValue
    end

    def column_name_for_list
      return case @values['reference_type'].to_i
               when 5 then
                 surround(@values['table_name'] + '.id')
               when 4 then
                 "risa_int2de_date(#{@values['table_name'] + '.' + @values['col_name']})"
               when 3 then
                 "#{@values['table_name'] + '.' + @values['col_name']}"
               when 2, 12 then
                 "'<span title=\"' || #{joined_table_name}.description || '\">' || #{joined_table_name}.key || '</span>'"
               when 1, 6, 10 then
                 joined_table_name + '.' + @values['foreign_column']
               when 13 then
                 %Q^'<span class="plant_link" onclick="show_form_for(''' || discharge_points.plant_id || ''',''plants'')">' || #{joined_table_name}.pl_key || ' - ' || #{joined_table_name}.pl_name || '</span>'^
               else
                 surround(@values['table_name'] + '.' + @values['col_name'])
             end
    end

    def joined_table_name
      if @values['table_name'] == 'discharge_points' and @values['col_name'] == 'void'
        @values['foreign_table'] + (@reference.pos(self).to_i - 1).to_s
      else
        @values['foreign_table'] + @reference.pos(self)
      end
    end

    def surround(str)
      unless @values['surrounding_function'].nil?
        ergebnis = @values['surrounding_function'] + str + (')' * @values['surrounding_function'].count('('))
        return ergebnis
      end
      return str
    end

    def reference
      @reference
    end

    #na, ist das hard-codiert genug
    def successor_schnipsel
      return case @values['table_name']
               when 'agglomerations' then
                 'risa_succ_agg_csv(agglomerations.id)'
               when 'plants' then
                 'risa_succ_pl_csv(plants.id)'
               when 'discharge_points' then
                 'risa_succ_dp_csv(discharge_points.id)'
             end
    end
  end

  class Reference
    attr_accessor :options, :columns

    def initialize(column, states)
      @name = column['foreign_table']
      @columns = [column]
      table_query = case column['reference_type'].to_i
                      when 2 then
                        "SELECT key, description, id from #{@name} ORDER BY position"
                      when 6 then
                        "SELECT description, description, id, federal_key from #{@name}  ORDER BY position"
                      #when 6 then "SELECT description, description, id, federal_key from #{@name} WHERE id IN (#{states.join(', ')}) ORDER BY position"
                      when 10, 13
                        @options = []
                        return "SELECT #{column['foreign_column']} as title, #{column['foreign_column']}, id, federal_state_id from #{@name} ORDER BY title, federal_state_id"
                      when 12
                        # neu:
                        # @options = []
                        # "SELECT key, description, id from #{@name} where federal_state_id IN (#{states.join(', ')}) ORDER BY key"
                        "SELECT #{column['foreign_column']} as title, #{column['foreign_column']}, id from #{@name} where federal_state_id IN (#{states.join(', ')}) ORDER BY #{column['foreign_column']}"
                      else
                        "SELECT #{column['foreign_column']}, #{column['foreign_column']}, id from #{@name}"
                    end
      #      table_query += " WHERE federal_state_id IN (#{states.join(" , ")}, 50) or federal_state_id is null" if @name == "l_region_code"
      @options = ActiveRecord::Base.connection.query(table_query).to_a
      @options.insert(0, [" ", " ", nil]) unless column.is_key_or_locked?
    end

    def add(column)
      @columns.push(column)
    end

    def table()
      return @name
    end

    def [](value)
      return @options[value]
    end

    def pos(col)
      @columns.index(col).to_s
    end
  end

  class Record
    attr_accessor :columns, :successes
    attr_reader :errors, :values
    include ApplicationHelper

    def initialize(values, gen, cols)
      @gen = gen
      @values = values
      @errors = []
      @columns = cols
      @successes = []
    end

    #only use with same Generica instance and cols
    #changes the instance and returns it
    def merge!(other_record)
      @values.to_h.merge!(other_record.values)
      # TODO: deprecated Warning. possible fix ->
      #@values.to_h.merge!(other_record.values.to_h)
      @errors = @errors + other_record.errors
      return self
    end

    def merge_with_db
      old = @gen.find_for_edit(self.id)
      return nil if old.nil?
      return old.merge!(self)
    end

    def [](value)
      return @values[value] if value.is_a?(String)
      return @values[value['col_name']] if value.is_a?(Generica::Column)
      return @values[@gen.columns[value]['col_name']] if value.is_a?(Integer)
    end

    def id
      return @values['id']
    end

    def locked?
      return @values['locked'].to_i == 1
    end

    def read_only?
      return false unless @values['fed_state_for_perm']
      return !(@gen.user.get_states_for_write.include?(@values['fed_state_for_perm']) ||
          @gen.user.get_states_for_write.include?(@values['handled_fed_state_for_perm']))
    end

    def read_only_or_locked?
      return (locked? || read_only?)
    end

    def columns_and_values_for_insert
      cols = []
      vals = []
      @gen.columns.each do |col|
        unless self[col].nil? || col.is_m_n_relation?
          cols.push(col['col_name'])
          vals.push(sql_value(col))
        end
      end
      if app_pairs = self.application_built
        app_pairs.each do |pair|
          cols.push(pair[:col])
          vals.push(pair[:val])
        end
      end
      if default_pairs = @gen.default_built
        default_pairs.each do |pair|
          cols.push(pair[:col])
          vals.push(pair[:val])
        end
      end

      if insert_pairs = @gen.insert_built
        insert_pairs.each do |pair|
          cols.push(pair[:col])
          vals.push(pair[:val])
        end
      end
      return cols, vals
    end

    def columns_and_values_for_update
      cols = []
      vals = []
      @gen.columns.each do |col|
        unless self[col].nil? || col.is_m_n_relation?
          case col['field_mode'].to_i
            when 0, 1 then
              cols.push("#{col['col_name']} = ?")
              vals.push(sql_value(col));
          end
        end
      end
      if default_pairs = @gen.default_built
        default_pairs.each do |pair|
          cols.push("#{pair[:col]} = ?")
          vals.push(pair[:val])
        end
      end
      return cols, vals
    end

    # Von der Applikation zu füllende Spalten und Werte bereit stellen:
    def application_built
      app_pairs = Array.new()
      # Bundesland-Key suchen. Muss in den eu_xy_key:
      federal_key = FederalState.find(self['federal_state_id']).federal_key rescue ''
      case @gen.table
        when 'agglomerations'
          eu_key = "DEAG_#{federal_key}#{self['agg_key']}"
          app_pairs.push({:col => 'agg_eu_key', :val => eu_key})
        when 'plants'
          eu_key = "DETP_#{federal_key}#{self['pl_key']}"
          app_pairs.push({:col => 'pl_eu_key', :val => eu_key})
        when 'discharge_points'
          eu_key = "DEDP_#{federal_key}#{self['dp_key']}"
          app_pairs.push({:col => 'dp_eu_key', :val => eu_key})
      end

      # ReportingPeriod suchen
      period = @gen.reporting_period.key_report
      app_pairs.push({:col => 'rep_code', :val => period})

      return app_pairs
    end

    def sql_value(col)
      #return 'NULL' if self[col].blank?
      return nil if self[col].blank?
      return case col['reference_type'].to_i
               #when 0, 7 then "'#{self[col]}'"
               when 4 then
                 numerical_date(self[col])
               when 8, 81 then
                 self[col].gsub('.', '').to_i
               when 9, 91 then
                 self[col].gsub('.', '').gsub(',', '.')
               else
                 self[col]
             end
    end

    def validate_insert
      key_fields = @gen.columns.select {|c| c['field_mode'].to_i == 2}
      key_fields.each do |key_field|
        col_name = key_field['col_name']
        value = @values[col_name]
        if value.blank?
          @errors.push([key_field['human_name'], " muss gefüllt werden".html_safe])
        end
      end
      unless @gen.user.get_states_for_write.include?(@values['federal_state_id'].to_i)
        @errors.push(['Eintrag', ' darf von Ihnen nicht für dieses Bundesland erstellt werden'])
        return false
      end
      # Datum (4):
      (@gen.columns.select {|c| c['reference_type'].to_i == 4}).each do |c|
        next if c['col_name'] == "ag_date"
        @errors.push([c['human_name'], " soll eine gültiges Datum sein (z. B. 7.3.1987 oder 1987-03-07)"]) unless self.is_valid_date(self[c])
      end
      (@gen.columns.select {|c| [8, 81].include?(c['reference_type'].to_i)}).each do |c|
        @errors.push([c['human_name'], " soll eine Ganzzahl sein"]) unless self.is_valid_int_or_empty(self[c])
      end
      (@gen.columns.select {|c| [9, 91].include?(c['reference_type'].to_i)}).each do |c|
        @errors.push([c['human_name'], " soll eine Fließkommazahl sein"]) unless self.is_valid_float_or_empty(self[c])
      end
#      (@gen.columns.select{|c| c['col_name'] =~ /.+_state_active$/ui }).each do |c|
#        @errors.push([c['human_name'], " : Muss ausgefüllt werden"]) if @values[c['col_name']].blank?
#      end
    end

    def validate_update
      old = @gen.find_for_edit(self.id)
      if old.nil?
        @errors.push(['Alter Eintrag', ' konnte nicht gefunden werden'])
        return false
      end
      # prüfen, dass das bundesland und andere schlüsselfelder nicht geändert wurde
      (@gen.columns.select {|c| c['field_mode'] == 2}).each do |c|
        if self[c] != nil and self[c] != old[c]
          @errors.push([c['human_name'], " Schlüsselfelder dürfen nicht geändert werden"])
          return false
        end
      end
      if read_only_or_locked?
        @errors.push(['Eintrag', ' darf von Ihnen nicht bearbeitet werden'])
        return false
      end
      (@gen.columns.select {|c| c['reference_type'].to_i == 4}).each do |c|
        next if c['col_name'] == "ag_date"
        @errors.push([c['human_name'], " soll eine gültiges Datum sein (z. B. 7.3.1987 oder 1987-03-07)"]) unless self.is_valid_date(self[c])
      end
      (@gen.columns.select {|c| [8, 81].include?(c['reference_type'].to_i)}).each do |c|
        @errors.push([c['human_name'], " soll eine Ganzzahl sein"]) unless is_valid_int_or_empty(self[c])
      end
      (@gen.columns.select {|c| [9, 91].include?(c['reference_type'].to_i)}).each do |c|
        @errors.push([c['human_name'], " soll eine Fließkommazahl sein"]) unless is_valid_float_or_empty(self[c])
      end
    end


  end
end
