# -*- coding: utf-8 -*-
#
# xml0_importer.rb  (e-PRTR XML-0 Import)
# (c) ENDA, 2010
#
# by mlt
#
# Rel-1-0-2

class XML0_Importer
  attr :glade

  def initialize(glade)
    @glade = glade
    @filesize = nil
    @file_num_lines = 0
    @bar = @glade['progressbar1']
    self.pulse
    # Einziger Method-Call von außen: import, s.u.
  end

  def pulse
    @bar.pulse_step
    Gtk.main_iteration_do(false)
  end

  def get_connection
    begin
      # connect to the MySQL server
      dbh = DBI.connect("DBI:Pg:e_prtr:pg84.domain.com", "e_prtr", "passwort")
      # get server version string and display it
      row = dbh.select_one("SELECT VERSION()")
      puts "Server version: " + row[0]
    rescue DBI::DatabaseError => e
      puts "An error occurred"
      puts "Error code: #{e.err}"
      puts "Error message: #{e.errstr}"
    ensure
      ## disconnect from server
      #dbh.disconnect if dbh
    end
    return dbh
  end

  def close_connection(dbh)
    dbh.disconnect if dbh
  end

  def import
    puts "Die zu importierende XML-0 Datei wird hier (#{MY_CONSTANT_XML0_PATH}) her genommen."
    puts "Start am/um #{Stnd.now}"
    self.pulse
    root = self.open_import_file
    self.suche_listen(root)
    @bar.fraction = 0.to_f
    puts "Ende am/um #{Stnd.now}"
  end

  def open_import_file
    @filesize = File.stat(MY_CONSTANT_XML0_PATH).size
    self.pulse
    f = File.open(MY_CONSTANT_XML0_PATH)
    @file_num_lines = 0
    f.each {@file_num_lines += 1}
    f.close
    XML.default_line_numbers = true
    @doc = XML::Document.file(MY_CONSTANT_XML0_PATH)
    puts "Zeit: " + Stnd.now + " XML \'Document object erzeugt\'"
    root = @doc.root
    puts "Root element: #{root.name}"
    self.pulse
    return root
  end

  def method_missing( symbol, *remaining )
    puts "You have called #{symbol} with arguments"
    remaining.each do |argument|
      puts "  <#{argument}>"
    end
    puts "We are sorry but THIS HAS TO BE IMPLEMENTED FIRST."
  end

  def XML0_Importer.method_missing( symbol, *remaining )
    puts "You have called #{symbol} with arguments"
    remaining.each do |argument|
      puts "  <#{argument}>"
    end
    puts "We are sorry but THIS HAS TO BE IMPLEMENTED FIRST."
  end

  def suche_listen(root)
    puts "suche_listen"
    i = 0
    method_name = nil
    self.pulse
    root.each_element do |liste|
      i = i + 1
      puts "Listenname #{i}: #{liste.name}"
      if liste.attributes?
        liste.attributes.each do |a|
          print "  Attribut ", a.name, "=\"", a.value, "\"\n"
        end
      end
      method_name = "import_#{liste.name.downcase}"
      case liste.name.downcase
      when 'r1002table', 'r1003table' then self.method(method_name).call(liste)
      else
        puts "Liste #{liste.name} NICHT bearbeitungswürdig\n"
      end
      
      puts "Liste #{liste.name} bearbeitet\n"
    end
  rescue Exception => rumms
    puts "Unexpected Exc <#{rumms}>"
    print rumms.backtrace.join("\n")
    puts "\nWe are sorry but #{method_name} HAS TO BE IMPLEMENTED FIRST (or uncaught exception)."
  end

  def get_item_content(parent_node, child_name, force = nil)
    if node = parent_node.find_first(child_name)
      return node.content
    elsif force == 'force'
      msg = "ERROR! XML Child #{child_name} not found inside #{parent_node.name} at line #{parent_node.line_num}"
      puts msg
      raise ParseException.new, msg
      return nil
    else
      return nil
    end
  end


  def get_behoerden_hash(item)
    behoerde = {}
    behoerde['schl'] = self.get_item_content(item, 'schl')
    behoerde['sortier'] = self.get_item_content(item, 'sortier')
    behoerde['ktext'] = self.get_item_content(item, 'ktext')
    behoerde['ltext'] = self.get_item_content(item, 'ltext', :force)
    bland = self.get_item_content(item, 'bland')
    behoerde['bland_id'] = bland.gsub(/^0/u, '').to_i
    behoerde['bland'] = bland
    behoerde['von'] = (self.get_item_content(item, 'jahrvon')+"0101").to_i
    behoerde['bis'] = (self.get_item_content(item, 'jahrbis')+"1231").to_i
    aend = self.get_item_content(item, 'aenddat')
    behoerde['aend_date'] = Stnd.numerical_date( aend[0,10] )
    behoerde['aend_time'] = Stnd.numerical_time( aend[11,8] )
    behoerde['aend'] = aend
    behoerde['behplz'] = self.get_item_content(item, 'behplz')
    behoerde['behort'] = self.get_item_content(item, 'behort')
    behoerde['behstr'] = self.get_item_content(item, 'behstr')
    behoerde['behnr'] = self.get_item_content(item, 'behnr')
    behoerde['behtel'] = self.get_item_content(item, 'behtel')
    behoerde['behfax'] = self.get_item_content(item, 'behfax', :force)
    behoerde['behemail'] = self.get_item_content(item, 'behemail')
    return behoerde
  end

  def behoerde_vs_datenbank(behoerde, row)
    status = 'unterschiedlich'
    if behoerde['schl'] == row['schl'] &&
        behoerde['sortier'] == row['sortier'] &&
        behoerde['ktext'] == row['ktext'] &&
        behoerde['ltext'] == row['ltext'] &&
        behoerde['bland_id'] == row['bland_id'] &&
        behoerde['aend_date'] == row['date_create'] &&
        behoerde['aend_time'] == row['time_create'] &&
        behoerde['behplz'] == row['behplz'] &&
        behoerde['behort'] == row['behort'] &&
        behoerde['behstr'] == row['behstr'] &&
        behoerde['behnr'] == row['behnr'] &&
        behoerde['behtel'] == row['behtel'] &&
        behoerde['behfax'] == row['behfax'] &&
        behoerde['behemail'] == row['behemail']
      status = 'wertgleich'
      if behoerde['von'] == row['date_from'] &&
          behoerde['bis'] == row['date_to']
        status = 'identisch'
      end
    else
      # puts "schl ist unterschiedlich" if behoerde['schl'] != row['schl']
      # puts "sortier ist unterschiedlich" if behoerde['sortier'] != row['sortier']
      # puts "ktext ist unterschiedlich" if behoerde['ktext'] != row['ktext']
      # puts "ltext ist unterschiedlich" if behoerde['ltext'] != row['ltext']
      # puts "bland_id ist unterschiedlich" if behoerde['bland_id'] != row['bland_id']
      # puts "aend_date ist unterschiedlich" if behoerde['aend_date'] != row['date_create']
      # puts "aend_time ist unterschiedlich" if behoerde['aend_time'] != row['time_create']
      # puts "behplz ist unterschiedlich" if behoerde['behplz'] != row['behplz']
      # puts "behort ist unterschiedlich" if behoerde['behort'] != row['behort']
      # puts "behstr ist unterschiedlich" if behoerde['behstr'] != row['behstr']
      # puts "behnr ist unterschiedlich" if behoerde['behnr'] != row['behnr']
      # puts "behtel ist unterschiedlich" if behoerde['behtel'] != row['behtel']
      # puts "behfax ist unterschiedlich" if behoerde['behfax'] != row['behfax']
      # puts "behemail ist unterschiedlich" if behoerde['behemail'] != row['behemail']
    end
    return status
  end

  def behoerde_neu(dbh, behoerde, j, ido = nil)
    if ido
      ido_col = 'ido, '
      ido_spare = '?, '
    else
      ido_col = nil
      ido_spare = nil
    end
    stmt = "insert into l_behoerden (
        #{ido_col}
        state, date_from, time_from, date_to,
        time_to, date_create, time_create, owner_id,
        creator_id, operation_id, schl, sortier,
        ktext, ltext, behplz, behort,
        behstr, behnr, behtel, behfax,
        behemail, bland_id, process)
      values (
        #{ido_spare}
        0, ?, 0, ?,
        240000, ?, ?, 8,
        8, 3, ?, ?,
        ?, ?, ?, ?,
        ?, ?, ?, ?,
        ?, ?, ?
      ) returning id"
    if ido
      id_row = dbh.select_one(stmt, ido, behoerde['von'], behoerde['bis'],
        behoerde['aend_date'], behoerde['aend_time'], behoerde['schl'], behoerde['sortier'],
        behoerde['ktext'], behoerde['ltext'], behoerde['behplz'], behoerde['behort'],
        behoerde['behstr'], behoerde['behnr'], behoerde['behtel'], behoerde['behfax'],
        behoerde['behemail'], behoerde['bland_id'], j)
    else
      id_row = dbh.select_one(stmt, behoerde['von'], behoerde['bis'],
        behoerde['aend_date'], behoerde['aend_time'], behoerde['schl'], behoerde['sortier'],
        behoerde['ktext'], behoerde['ltext'], behoerde['behplz'], behoerde['behort'],
        behoerde['behstr'], behoerde['behnr'], behoerde['behtel'], behoerde['behfax'],
        behoerde['behemail'], behoerde['bland_id'], j)
    end
    new_id = id_row['id']
    # puts "NEW id = #{new_id}"
    return new_id
  end


  def behoerde_shift_state(dbh, id, ido, date_from)
    # state auf max + 1 setzen:
    stmt = "select max(state)+1 from l_behoerden
      where ido = ? and date_from = ?"
    new_state_row = dbh.select_one(stmt, ido, date_from)
    new_state = new_state_row.by_index(0)
    stmt = "update l_behoerden set state = ? where id = ?"
    dbh.do(stmt, new_state, id)
    return new_state
  end


  def behoerde_copy(dbh, id, j)
    stmt = "insert into l_behoerden (ido, state, date_from, time_from,
        date_to, time_to, date_create, time_create,
        owner_id, creator_id, operation_id, schl,
        sortier, ktext, ltext, behplz,
        behort, behstr, behnr, behtel,
        behfax, behemail, bland_id, process)
    select ido, 0 as state, date_from, time_from,
        date_to, time_to, date_create, time_create,
        owner_id, creator_id, operation_id, schl,
        sortier, ktext, ltext, behplz,
        behort, behstr, behnr, behtel,
        behfax, behemail, bland_id, ? as process
    from l_behoerden where id = #{id}
        returning id"
    id_row = dbh.select_one(stmt, j)
    copy_id = id_row['id']
    # puts "  COPY id = #{copy_id}"
    return copy_id
  end

  # Die Operationen Op. 1 bis Op. 5 sind im ENDA-Wiki beschrieben
  # Sie decken jeweils einen oder mehrere der elf Fälle der Zeitraumüberschneidung ab
  # und definieren das Vorgehen beim Mergen der Zeiträume.
  # Die Fälle der Zeitraumüberschneidungen sind ebenfalls im ENDA-Wiki dokumentiert!

  def behoerde_op1(dbh, behoerde, row, j, new_id = nil)
    # Fall 1, 11
    unless new_id
      new_id = self.behoerde_neu(dbh, behoerde, j, row['ido'])
    end
    return new_id
  end


  def behoerde_op2(dbh, behoerde, row, j, new_id = nil)
    # Fall 2, 7
    self.behoerde_shift_state(dbh, row['id'], row['ido'], row['date_from'])
    copy_id = self.behoerde_copy(dbh, row['id'], j)
    stmt = "update l_behoerden set date_to = ?, time_to = 240000 where id = ?"
    num_rows = dbh.do(stmt, behoerde['von']-8870, copy_id);
    unless new_id
      new_id = self.behoerde_neu(dbh, behoerde, j, row['ido'])
    end
    # Alle p_betrieb, die auf die alte Behörde zeigen, aber in den Zeitraum der neu eingefügten
    # Behörde fallen, bekommen einen aktualisierten Behördenzeiger auf die neue Behörde
    stmt = "update p_betrieb set behnr_id = ? where state = 0 and behnr_id = ? and date_from >= ?"
    num_rows = dbh.do(stmt, new_id, row['id'], behoerde['von']);
    puts "#{num_rows} BETRIEBE per update umgeschlüsselt" if num_rows > 0
    return new_id
  end


  def behoerde_op3(dbh, behoerde, row, j, new_id = nil)
    # Fall 3, 4, 5, 6
    self.behoerde_shift_state(dbh, row['id'], row['ido'], row['date_from'])
    # Nur einfügen, wenn derselbe nicht schon eingefügt wurde:
    unless new_id
      new_id = self.behoerde_neu(dbh, behoerde, j, row['ido'])
    end
    stmt = "update p_betrieb set behnr_id = ? where state = 0 and behnr_id = ?"
    num_rows = dbh.do(stmt, new_id, row['id']);
    puts "#{num_rows} BETRIEBE per update umgeschlüsselt" if num_rows > 0
    return new_id
  end


  def behoerde_op4(dbh, behoerde, row, j)
    # Fall 8
    self.behoerde_shift_state(dbh, row['id'], row['ido'], row['date_from'])

    new_right_copy_id = self.behoerde_copy(dbh, row['id'], j)
    stmt = "update l_behoerden set date_from = ?, time_from = 0 where id = ?"
    num_rows = dbh.do(stmt, behoerde['bis']+8870, new_right_copy_id);

    new_left_copy_id = self.behoerde_copy(dbh, row['id'], j)
    stmt = "update l_behoerden set date_to = ?, time_to = 240000 where id = ?"
    num_rows = dbh.do(stmt, behoerde['von']-8870, new_left_copy_id);

    new_id = self.behoerde_neu(dbh, behoerde, j, row['ido'])

    # Alle p_betrieb, die auf die alte Behörde zeigen, aber in den Zeitraum der neu eingefügten
    # Behörde fallen, bekommen einen aktualisierten Behördenzeiger auf die neue Behörde
    stmt = "update p_betrieb set behnr_id = ? where state = 0 and behnr_id = ? and date_from >= ? and date_to <= ?"
    num_rows = dbh.do(stmt, new_id, row['id'], behoerde['von'], behoerde['bis']);
    puts "#{num_rows} BETRIEBE per update umgeschlüsselt" if num_rows > 0
    return new_id
  end


  def behoerde_op5(dbh, behoerde, row, j, new_id = nil)
    # Fall 9, 10
    self.behoerde_shift_state(dbh, row['id'], row['ido'], row['date_from'])
    copy_id = self.behoerde_copy(dbh, row['id'], j)
    stmt = "update l_behoerden set date_from = ?, time_from = 0 where id = ?"
    num_rows = dbh.do(stmt, behoerde['bis']+8870, copy_id);
    unless new_id
      new_id = self.behoerde_neu(dbh, behoerde, j, row['ido'])
    end
    # Alle p_betrieb, die auf die alte Behörde zeigen, aber in den Zeitraum der neu eingefügten
    # Behörde fallen, bekommen einen aktualisierten Behördenzeiger auf die neue Behörde
    stmt = "update p_betrieb set behnr_id = ? where state = 0 and behnr_id = ? and date_to <= ?"
    num_rows = dbh.do(stmt, new_id, row['id'], behoerde['bis']);
    puts "#{num_rows} BETRIEBE per update umgeschlüsselt" if num_rows > 0
    return new_id
  end


  def import_r1002table(liste)
    # l_behoerden
    dbh = self.get_connection

    stmt = "update l_behoerden set process = null"
    rows = dbh.do(stmt)
    puts "l_behoerden: In #{rows} Zeilen process auf null gesetzt"
    num_exceptions = 0

    j = 0
    liste.each_element do |item|
      @bar.fraction = (item.line_num.to_f/@file_num_lines)
      Gtk.main_iteration_do(false)
      j = j + 1
      # XML-Listenknoten-Kindwerte ablegen und ggf. aufbereiten:
      behoerde = self.get_behoerden_hash(item)
      eintrag_behandelt = false
      new_id = nil

      begin
        # Die Reihenfolge der Behandlungen ist wohl überlegt. Eine Änderung wird vermutlich großen Ärger bereiten.
        # Es kommt darauf, an die Überlappungsfälle zuerst zu behandeln und die nochmalige Erzeugung des Eintrags
        # in den folgenden Fällen zu unterbinden. Dazu dient die new_id, die beim Erzeugen gesetzt wird.

        if !eintrag_behandelt
          # Fall 8, Op. 4
          # date_from < von and date_to > bis
          stmt = "select id, ido, date_from, date_to from l_behoerden
              where state = 0 and schl = ? and bland_id = ? and
              ( date_from < ? and date_to > ? ) and
              process is distinct from ?"
          # puts "Suche Fall 8: <#{stmt}>  - #{schl} - #{bland_id} - #{von} - #{bis}"
          sth = dbh.execute(stmt, behoerde['schl'], behoerde['bland_id'], behoerde['von'], behoerde['bis'], j)
          sth.fetch do |row|
            # puts "  Fall 8: ID #{row["id"]} gefunden."
            new_id = self.behoerde_op4(dbh, behoerde, row, j)
            eintrag_behandelt = true
          end
          sth.finish
        end
        next if new_id

        # Fall 3, 4, 5, 6
        # date_from >= von and date_to <= bis
        stmt = "select * from l_behoerden
              where state = 0 and schl = ? and bland_id = ? and
              ( date_from >= ? and date_to <= ? ) and
              process is distinct from ?"
        # puts "Suche Fall 3, 4, 5, 6: <#{stmt}>  - #{schl} - #{bland_id} - #{von} - #{bis}"
        sth = dbh.execute(stmt, behoerde['schl'], behoerde['bland_id'], behoerde['von'], behoerde['bis'], j)
        sth.fetch do |row|
          # puts "  Fall 3, 4, 5, 6: ID #{row["id"]} gefunden."
          status = self.behoerde_vs_datenbank(behoerde, row)
          case status
          when 'identisch'
            eintrag_behandelt = true
            # puts "  NICHTS zu tun, da Einträge identisch"
          when 'wertgleich', 'unterschiedlich'
            new_id = self.behoerde_op3(dbh, behoerde, row, j, new_id)
          else
            raise Exception,"Hir seyn wol Drachen"
          end
        end
        sth.finish
        next if eintrag_behandelt

        if !eintrag_behandelt
          # Fall 2, 7
          # date_from < von and date_to > von and date_to <= bis
          stmt = "select id, ido, date_from, date_to from l_behoerden
              where state = 0 and schl = ? and bland_id = ? and
              ( date_from < ? and date_to > ? and  date_to <= ? ) and
              process is distinct from ?"
          # puts "Suche Fall 2, 7: <#{stmt}>  - #{schl} - #{bland_id} - #{von} - #{von} - #{bis}"
          sth = dbh.execute(stmt, behoerde['schl'], behoerde['bland_id'], behoerde['von'], behoerde['von'], behoerde['bis'], j)
          sth.fetch do |row|
            # puts "  Fall 2, 7: ID #{row["id"]} gefunden."
            new_id = self.behoerde_op2(dbh, behoerde, row, j, new_id)
            # Es kann sein, dass ein Eintrag zwei alte überlappt, einen rechts, den andern links.
            # Daher sind wir noch nicht fertig! Fall 10 muss noch beachtet werden.
          end
          sth.finish
        end

        if !eintrag_behandelt
          # Fall 9, 10
          # date_from >= von and date_from < bis and date_to > bis
          stmt = "select id, ido, date_from, date_to from l_behoerden
              where state = 0 and schl = ? and bland_id = ? and
              ( date_from >= ? and date_from < ? and date_to > ? ) and
              process is distinct from ?"
          # puts "Suche Fall 9, 10: <#{stmt}>  - #{schl} - #{bland_id} - #{von} - #{bis} - #{bis}"
          sth = dbh.execute(stmt, behoerde['schl'], behoerde['bland_id'], behoerde['von'], behoerde['bis'], behoerde['bis'], j)
          sth.fetch do |row|
            # puts "  Fall 9, 10: ID #{row["id"]} gefunden."
            new_id = self.behoerde_op5(dbh, behoerde, row, j, new_id)
          end
          sth.finish
        end

        unless new_id
          # Fall 1, 11
          # date_from >= bis OR date_to <= von
          stmt = "select id, ido, date_from, date_to from l_behoerden
              where state = 0 and schl = ? and bland_id = ? and
              ( date_from >= ? or date_to <= ? ) and
              process is distinct from ?"
          # puts "Suche Fall 1, 11: <#{stmt}>  - #{schl} - #{bland_id} - #{bis} - #{von}"
          sth = dbh.execute(stmt, behoerde['schl'], behoerde['bland_id'], behoerde['bis'], behoerde['von'], j)
          sth.fetch do |row|
            # puts "  Fall 1, 11: ID #{row["id"]} gefunden."
            new_id = self.behoerde_op1(dbh, behoerde, row, j, new_id)
            break
          end
          sth.finish
        end
        next if new_id
        
=begin
        # Fall: Alter Eintrag nicht vorhanden
        stmt = "select id, ido, date_from, date_to from l_behoerden
              where state = 0 and schl = ? and bland_id = ?"
        # puts "Suche Eintrag: <#{stmt}>  - #{schl} - #{bland_id}"
        num_rows = 0
        sth = dbh.execute(stmt, behoerde['schl'], behoerde['bland_id'])
        sth.fetch do |row|
          puts "ERROR!!! ID #{row["id"]} gefunden."
          num_rows += 1
        end
        sth.finish
        if num_rows == 0
          # puts "    Noch kein Eintrag vorhanden. #{behoerde['schl']} - #{behoerde['bland_id']} - #{behoerde['von']} - #{behoerde['bis']}"
          behoerde_neu(dbh, behoerde, j)
        end
=end
        # Hier gibt es keine alten Einträge mehr, sonst hätte eine der Op. 1-5 gegriffen:
        self.behoerde_neu(dbh, behoerde, j)

      rescue Exception => wumm
        num_exceptions += 1
        puts "ERROR in computation of node #{item.name} at line #{item.line_num}"
        behoerde.each { |k, v| puts "#{k} => #{v}" } if behoerde
        puts "Crashed with <#{wumm}>."
        print wumm.backtrace.join("\n")
      ensure
      end
      
    end
    self.close_connection(dbh)

    puts "Anzahl der Exceptions: #{num_exceptions}"
    puts "\n\n==========================================================================================\n\n"
  end



  def get_gemeinden_hash(item)
    gemeinde = {}
    gemeinde['schl'] = self.get_item_content(item, 'schl')
    gemeinde['sortier'] = self.get_item_content(item, 'sortier')
    gemeinde['ktext'] = self.get_item_content(item, 'ktext')
    gemeinde['ltext'] = self.get_item_content(item, 'ltext', :force)
    bland = self.get_item_content(item, 'bland')
    gemeinde['bland_id'] = bland.gsub(/^0/u, '').to_i
    gemeinde['bland'] = bland
    gemeinde['von'] = (self.get_item_content(item, 'jahrvon')+"0101").to_i
    gemeinde['bis'] = (self.get_item_content(item, 'jahrbis')+"1231").to_i
    aend = self.get_item_content(item, 'aenddat')
    gemeinde['aend_date'] = Stnd.numerical_date( aend[0,10] )
    gemeinde['aend_time'] = Stnd.numerical_time( aend[11,8] )
    gemeinde['aend'] = aend
    return gemeinde
  end

  def gemeinde_vs_datenbank(gemeinde, row)
    status = 'unterschiedlich'
    if gemeinde['schl'] == row['schl'] &&
        gemeinde['sortier'] == row['sortier'] &&
        gemeinde['ktext'] == row['ktext'] &&
        gemeinde['ltext'] == row['ltext'] &&
        gemeinde['bland_id'] == row['bland_id'] &&
        gemeinde['aend_date'] == row['date_create'] &&
        gemeinde['aend_time'] == row['time_create']
      status = 'wertgleich'
      if gemeinde['von'] == row['date_from'] &&
          gemeinde['bis'] == row['date_to']
        status = 'identisch'
      end
    else
      # puts "schl ist unterschiedlich" if gemeinde['schl'] != row['schl']
      # puts "sortier ist unterschiedlich" if gemeinde['sortier'] != row['sortier']
      # puts "ktext ist unterschiedlich" if gemeinde['ktext'] != row['ktext']
      # puts "ltext ist unterschiedlich" if gemeinde['ltext'] != row['ltext']
      # puts "bland_id ist unterschiedlich" if gemeinde['bland_id'] != row['bland_id']
      # puts "aend_date ist unterschiedlich" if gemeinde['aend_date'] != row['date_create']
      # puts "aend_time ist unterschiedlich" if gemeinde['aend_time'] != row['time_create']
    end
    return status
  end

  def gemeinde_neu(dbh, gemeinde, j, ido = nil)
    if ido
      ido_col = 'ido, '
      ido_spare = '?, '
    else
      ido_col = nil
      ido_spare = nil
    end
    stmt = "insert into l_gemeinden (
        #{ido_col}
        state, date_from, time_from, date_to,
        time_to, date_create, time_create, owner_id,
        creator_id, operation_id, schl, sortier,
        ktext, ltext, bland_id, process)
      values (
        #{ido_spare}
        0, ?, 0, ?,
        240000, ?, ?, 8,
        8, 3, ?, ?,
        ?, ?, ?, ?
      ) returning id"
    if ido
      id_row = dbh.select_one(stmt, ido, gemeinde['von'], gemeinde['bis'],
        gemeinde['aend_date'], gemeinde['aend_time'], gemeinde['schl'], gemeinde['sortier'],
        gemeinde['ktext'], gemeinde['ltext'], gemeinde['bland_id'], j)
    else
      id_row = dbh.select_one(stmt, gemeinde['von'], gemeinde['bis'],
        gemeinde['aend_date'], gemeinde['aend_time'], gemeinde['schl'], gemeinde['sortier'],
        gemeinde['ktext'], gemeinde['ltext'], gemeinde['bland_id'], j)
    end
    new_id = id_row['id']
    # puts "NEW id = #{new_id}"
    return new_id
  end


  def gemeinde_shift_state(dbh, id, ido, date_from)
    # state auf max + 1 setzen:
    stmt = "select max(state)+1 from l_gemeinden
      where ido = ? and date_from = ?"
    new_state_row = dbh.select_one(stmt, ido, date_from)
    new_state = new_state_row.by_index(0)
    stmt = "update l_gemeinden set state = ? where id = ?"
    dbh.do(stmt, new_state, id)
    return new_state
  end


  def gemeinde_copy(dbh, id, j)
    stmt = "insert into l_gemeinden (ido, state, date_from, time_from,
        date_to, time_to, date_create, time_create,
        owner_id, creator_id, operation_id, schl,
        sortier, ktext, ltext, bland_id,
        process)
    select ido, 0 as state, date_from, time_from,
        date_to, time_to, date_create, time_create,
        owner_id, creator_id, operation_id, schl,
        sortier, ktext, ltext, bland_id,
        ? as process
    from l_gemeinden where id = #{id}
        returning id"
    id_row = dbh.select_one(stmt, j)
    copy_id = id_row['id']
    # puts "  COPY id = #{copy_id}"
    return copy_id
  end

  # Die Operationen Op. 1 bis Op. 5 sind im ENDA-Wiki beschrieben
  # Sie decken jeweils einen oder mehrere der elf Fälle der Zeitraumüberschneidung ab
  # und definieren das Vorgehen beim Mergen der Zeiträume.
  # Die Fälle der Zeitraumüberschneidungen sind ebenfalls im ENDA-Wiki dokumentiert!

  def gemeinde_op1(dbh, gemeinde, row, j, new_id = nil)
    # Fall 1, 11
    unless new_id
      new_id = self.gemeinde_neu(dbh, gemeinde, j, row['ido'])
    end
    return new_id
  end


  def gemeinde_op2(dbh, gemeinde, row, j, new_id = nil)
    # Fall 2, 7
    self.gemeinde_shift_state(dbh, row['id'], row['ido'], row['date_from'])
    copy_id = self.gemeinde_copy(dbh, row['id'], j)
    stmt = "update l_gemeinden set date_to = ?, time_to = 240000 where id = ?"
    num_rows = dbh.do(stmt, gemeinde['von']-8870, copy_id);
    unless new_id
      new_id = self.gemeinde_neu(dbh, gemeinde, j, row['ido'])
    end
    # Alle p_betrieb, die auf die alte Gemeinde zeigen, aber in den Zeitraum der neu eingefügten
    # Gemeinde fallen, bekommen einen aktualisierten Gemeindezeiger auf die neue Gemeinde
    stmt = "update p_betrieb set gemde_id = ? where state = 0 and gemde_id = ? and date_from >= ?"
    num_rows = dbh.do(stmt, new_id, row['id'], gemeinde['von']);
    puts "#{num_rows} BETRIEBE per update umgeschlüsselt" if num_rows > 0
    return new_id
  end


  def gemeinde_op3(dbh, gemeinde, row, j, new_id = nil)
    # Fall 3, 4, 5, 6
    self.gemeinde_shift_state(dbh, row['id'], row['ido'], row['date_from'])
    # Nur einfügen, wenn dieselbe nicht schon eingefügt wurde:
    unless new_id
      new_id = self.gemeinde_neu(dbh, gemeinde, j, row['ido'])
    end
    stmt = "update p_betrieb set gemde_id = ? where state = 0 and gemde_id = ?"
    num_rows = dbh.do(stmt, new_id, row['id']);
    puts "#{num_rows} BETRIEBE per update umgeschlüsselt" if num_rows > 0
    return new_id
  end


  def gemeinde_op4(dbh, gemeinde, row, j)
    # Fall 8
    self.gemeinde_shift_state(dbh, row['id'], row['ido'], row['date_from'])

    new_right_copy_id = self.gemeinde_copy(dbh, row['id'], j)
    stmt = "update l_gemeinden set date_from = ?, time_from = 0 where id = ?"
    num_rows = dbh.do(stmt, gemeinde['bis']+8870, new_right_copy_id);

    new_left_copy_id = self.gemeinde_copy(dbh, row['id'], j)
    stmt = "update l_gemeinden set date_to = ?, time_to = 240000 where id = ?"
    num_rows = dbh.do(stmt, gemeinde['von']-8870, new_left_copy_id);

    new_id = self.gemeinde_neu(dbh, gemeinde, j, row['ido'])

    # Alle p_betrieb, die auf die alte Gemeinde zeigen, aber in den Zeitraum der neu eingefügten
    # Gemeinde fallen, bekommen einen aktualisierten Gemeindenzeiger auf die neue Gemeinde
    stmt = "update p_betrieb set gemde_id = ? where state = 0 and gemde_id = ? and date_from >= ? and date_to <= ?"
    num_rows = dbh.do(stmt, new_id, row['id'], gemeinde['von'], gemeinde['bis']);
    puts "#{num_rows} BETRIEBE per update umgeschlüsselt" if num_rows > 0
    return new_id
  end


  def gemeinde_op5(dbh, gemeinde, row, j, new_id = nil)
    # Fall 9, 10
    self.gemeinde_shift_state(dbh, row['id'], row['ido'], row['date_from'])
    copy_id = self.gemeinde_copy(dbh, row['id'], j)
    stmt = "update l_gemeinden set date_from = ?, time_from = 0 where id = ?"
    num_rows = dbh.do(stmt, gemeinde['bis']+8870, copy_id);
    unless new_id
      new_id = self.gemeinde_neu(dbh, gemeinde, j, row['ido'])
    end
    # Alle p_betrieb, die auf die alte Gemeinde zeigen, aber in den Zeitraum der neu eingefügten
    # Gemeinde fallen, bekommen einen aktualisierten Gemeindenzeiger auf die neue Gemeinde
    stmt = "update p_betrieb set gemde_id = ? where state = 0 and gemde_id = ? and date_to <= ?"
    num_rows = dbh.do(stmt, new_id, row['id'], gemeinde['bis']);
    puts "#{num_rows} BETRIEBE per update umgeschlüsselt" if num_rows > 0
    return new_id
  end


  def import_r1003table(liste)
    # l_gemeinden
    dbh = self.get_connection

    stmt = "update l_gemeinden set process = null"
    rows = dbh.do(stmt)
    puts "l_gemeinden: In #{rows} Zeilen process auf null gesetzt"
    num_exceptions = 0

    j = 0
    liste.each_element do |item|
      @bar.fraction = (item.line_num.to_f/@file_num_lines)
      Gtk.main_iteration_do(false)
      j = j + 1
      # XML-Listenknoten-Kindwerte ablegen und ggf. aufbereiten:
      gemeinde = self.get_gemeinden_hash(item)
      eintrag_behandelt = false
      new_id = nil

      begin
        # Die Reihenfolge der Behandlungen ist wohl überlegt. Eine Änderung wird vermutlich großen Ärger bereiten.
        # Es kommt darauf, an die Überlappungsfälle zuerst zu behandeln und die nochmalige Erzeugung des Eintrags
        # in den folgenden Fällen zu unterbinden. Dazu dient die new_id, die beim Erzeugen gesetzt wird.

        if !eintrag_behandelt
          # Fall 8, Op. 4
          # date_from < von and date_to > bis
          stmt = "select id, ido, date_from, date_to from l_gemeinden
              where state = 0 and schl = ? and bland_id = ? and
              ( date_from < ? and date_to > ? ) and
              process is distinct from ?"
          # puts "Suche Fall 8: <#{stmt}>  - #{schl} - #{bland_id} - #{von} - #{bis}"
          sth = dbh.execute(stmt, gemeinde['schl'], gemeinde['bland_id'], gemeinde['von'], gemeinde['bis'], j)
          sth.fetch do |row|
            puts "  Fall 8: ID #{row["id"]} gefunden."
            new_id = self.gemeinde_op4(dbh, gemeinde, row, j)
            eintrag_behandelt = true
          end
          sth.finish
        end
        next if new_id

        # Fall 3, 4, 5, 6
        # date_from >= von and date_to <= bis
        stmt = "select * from l_gemeinden
              where state = 0 and schl = ? and bland_id = ? and
              ( date_from >= ? and date_to <= ? ) and
              process is distinct from ?"
        # puts "Suche Fall 3, 4, 5, 6: <#{stmt}>  - #{schl} - #{bland_id} - #{von} - #{bis}"
        sth = dbh.execute(stmt, gemeinde['schl'], gemeinde['bland_id'], gemeinde['von'], gemeinde['bis'], j)
        sth.fetch do |row|
          # puts "  Fall 3, 4, 5, 6: ID #{row["id"]} gefunden."
          status = self.gemeinde_vs_datenbank(gemeinde, row)
          case status
          when 'identisch'
            eintrag_behandelt = true
            # puts "  NICHTS zu tun, da Einträge identisch"
          when 'wertgleich', 'unterschiedlich'
            # puts "  Fall 3, 4, 5, 6: ID #{row["id"]} gefunden: update"
            new_id = self.gemeinde_op3(dbh, gemeinde, row, j, new_id)
          else
            raise Exception,"Hir seyn wol Drachen"
          end
        end
        sth.finish
        next if eintrag_behandelt

        if !eintrag_behandelt
          # Fall 2, 7
          # date_from < von and date_to > von and date_to <= bis
          stmt = "select id, ido, date_from, date_to from l_gemeinden
              where state = 0 and schl = ? and bland_id = ? and
              ( date_from < ? and date_to > ? and  date_to <= ? ) and
              process is distinct from ?"
          # puts "Suche Fall 2, 7: <#{stmt}>  - #{schl} - #{bland_id} - #{von} - #{von} - #{bis}"
          sth = dbh.execute(stmt, gemeinde['schl'], gemeinde['bland_id'], gemeinde['von'], gemeinde['von'], gemeinde['bis'], j)
          sth.fetch do |row|
            # puts "  Fall 2, 7: ID #{row["id"]} gefunden."
            new_id = self.gemeinde_op2(dbh, gemeinde, row, j, new_id)
            # Es kann sein, dass ein Eintrag zwei alte überlappt, einen rechts, den andern links.
            # Daher sind wir noch nicht fertig! Fall 10 muss noch beachtet werden.
          end
          sth.finish
        end

        if !eintrag_behandelt
          # Fall 9, 10
          # date_from >= von and date_from < bis and date_to > bis
          stmt = "select id, ido, date_from, date_to from l_gemeinden
              where state = 0 and schl = ? and bland_id = ? and
              ( date_from >= ? and date_from < ? and date_to > ? ) and
              process is distinct from ?"
          # puts "Suche Fall 9, 10: <#{stmt}>  - #{schl} - #{bland_id} - #{von} - #{bis} - #{bis}"
          sth = dbh.execute(stmt, gemeinde['schl'], gemeinde['bland_id'], gemeinde['von'], gemeinde['bis'], gemeinde['bis'], j)
          sth.fetch do |row|
            # puts "  Fall 9, 10: ID #{row["id"]} gefunden."
            new_id = self.gemeinde_op5(dbh, gemeinde, row, j, new_id)
          end
          sth.finish
        end

        unless new_id
          # Fall 1, 11
          # date_from >= bis OR date_to <= von
          stmt = "select id, ido, date_from, date_to from l_gemeinden
              where state = 0 and schl = ? and bland_id = ? and
              ( date_from >= ? or date_to <= ? ) and
              process is distinct from ?"
          # puts "Suche Fall 1, 11: <#{stmt}>  - #{schl} - #{bland_id} - #{bis} - #{von}"
          sth = dbh.execute(stmt, gemeinde['schl'], gemeinde['bland_id'], gemeinde['bis'], gemeinde['von'], j)
          sth.fetch do |row|
            # puts "  Fall 1, 11: ID #{row["id"]} gefunden."
            new_id = self.gemeinde_op1(dbh, gemeinde, row, j, new_id)
            break
          end
          sth.finish
        end
        next if new_id

        # Fall: Alter Eintrag nicht vorhanden
=begin
        stmt = "select id, ido, date_from, date_to from l_gemeinden
              where state = 0 and schl = ? and bland_id = ?"
        # puts "Suche Eintrag: <#{stmt}>  - #{schl} - #{bland_id}"
        num_rows = 0
        sth = dbh.execute(stmt, gemeinde['schl'], gemeinde['bland_id'])
        sth.fetch do |row|
          puts "ERROR!!! ID #{row["id"]} gefunden."
          num_rows += 1
        end
        sth.finish
        if num_rows == 0
          puts "    Noch kein Eintrag vorhanden. #{gemeinde['schl']} - #{gemeinde['bland_id']} - #{gemeinde['von']} - #{gemeinde['bis']}"
          gemeinde_neu(dbh, gemeinde, j)
        end
=end
        # Hier gibt es keine alten Einträge mehr, sonst hätte eine der Op. 1-5 gegriffen:
        gemeinde_neu(dbh, gemeinde, j)

      rescue Exception => wumm
        num_exceptions += 1
        puts "ERROR in computation of node #{item.name} at line #{item.line_num}"
        gemeinde.each { |k, v| puts "#{k} => #{v}" } if gemeinde
        puts "Crashed with <#{wumm}>."
        print wumm.backtrace.join("\n")
      ensure
      end

    end
    self.close_connection(dbh)
    puts "Anzahl der Exceptions: #{num_exceptions}"
  end


end
