# -*- coding: utf-8 -*-
#
# xml_art15_exporter.rb
# (c) ENDA, 2016, 2010
# 
# by mlt
#
# Rel-1-3-5
# 2016-08-02, mlt: Erweiterung ReceivingAreasSA54 (Art.5.4-Daten)
# 2016-07-26, mlt: Erweiterung Receiving Areas (ERA)
# 2016-06-29, mlt: def check_some_data neu: Vor dem Export prüfen, ob die Tabelle agglomerations_plants
#                  Einträge besitzt, bei denen from_date oder to_date = null ist
# 2016-06-28, mlt: Dummy-Werte für aggC1, aggC2 und aggPercWithoutTreatment, wenn state=2; Konform mit der COM-
#                  Empfehlung und dem XML-Schema wurde für alle 0 als Dummy gesetzt. Etwas sinnvolleres erlaubt
#                  das Schema nicht.
# 2016-01-07, nbw: Fehler im Hinblick auf ANH1 <> EU korrigiert (BOD5, ...)
# 2012-03-28, mlt: Fehlermeldungen durch Unterdrückung bei inaktiven bzw. nicht berichteten Objekten reduziert
#                  Join-Bedingungen für die Listentabellen water body und groundwater body von Datum und state befreit:
#                  Die gehen über id, das muss reichen
# 2010-05-07, mlt: agg_percent_plant mit agg_c1_ka-Anteil gewichtet
# 2010-05-04, mlt: bool-Ausgabe an einer Stelle von Y/N auf 0/1 geändert
# 2010-05-03, mlt: create_agg_plant_correlation: Select only federal_states < 18 (agglos and plants)

class Art15_XML_Exporter
  attr :glade
  attr :dbh

  REP_CODE = "DE_UWWT_2014_1"
  DATE_FROM = "20140101"
  REP_SITUATION_AT = "2014-12-31"

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

  def pulse
    @bar.pulse
    Gtk.main_iteration
  end

  # get_connection bitte selbst von diesem Dummy ableiten, da die Verbindungs-
  # informationen nicht in das öffentliche Repository gelangen dürfen.
  def get_connection_dummy
    begin
      @dbh = DBI.connect("DBI:Pg:mydbname:example_db_url.com", "user", "password")
      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.disconnect if @dbh
    @dbh = nil
  end

  def export
    @bar.pulse_step = 0.07
    @bar.fraction = 0.0
    @bar.text="exporting"
    puts "Die zu exportierende XML Datei wird hier hin geschrieben: #{MY_CONSTANT_XML_PATH}"
    puts "Start am/um #{Stnd.now}"

    self.pulse
    @dbh = self.get_connection
    @oroot = self.open_document

    self.check_some_data
    self.prepare_coord_data
    self.create_document
    self.write_document
    self.close_connection

    @bar.text="done"
    @bar.fraction = 1.0
    puts "Ende am/um #{Stnd.now}"
  end

  def check_some_data
    any_errors = false
    # Prüfen, ob die Tabelle agglomerations_plants Einträge besitzt, bei denen from_date oder to_date = null ist
    stmt = "select * from agglomerations_plants ap
      where state = 0 AND (date_from IS NULL OR date_to IS NULL)"
    rows = dbh.select_all(stmt)
    if rows.length > 0
      any_errors = true
      # TODO: Wenn dem so ist, Fehler melden (Konsole und MessageBox) und die Arbeit verweigern!
      message_string = "✘ (FAIL) Prüfung Tabelle agglomerations_plants auf NULL\n"
      message_string += "in date_from und date_to.\n"
      message_string += "Es gibt #{rows.length} Zeilen in der Tabelle agglomerations_plants,\n"
      message_string += "die entweder im Feld date_from oder im Feld date_to\n"
      message_string += "NULL stehen haben.\n"
      message_string += "\n"
      message_string += "Überflüssig zu sagen, dass dies ein FEHLER ist, der\n"
      message_string += "die Ausgabe von Agglomerationszeigern in den Anlagen\n"
      message_string += "verhindert."
      puts message_string
      dialog = Gtk::MessageDialog.new(@glade['main_window'],
                                      Gtk::Dialog::DESTROY_WITH_PARENT,
                                      Gtk::MessageDialog::ERROR,
                                      Gtk::MessageDialog::BUTTONS_CLOSE,
                                      message_string)
      dialog.run
      dialog.destroy
    else
      puts "✔ (passed) Prüfung Tabelle agglomerations_plants auf NULL in date_from und date_to"
    end

    if any_errors
      puts "Arbeitsverweigerung aufgrund detektierter Fehler. Tschüss."
      Gtk.main_quit
      exit! 0
    end
  end

  def open_document
    @odoc = XML::Document.new()
    @odoc.encoding = XML::Encoding::UTF_8
    @odoc.root = XML::Node.new('UWWTD_Data')
    pulse

    XML::Namespace.new(@odoc.root, 'xs', 'http://www.w3.org/2001/XMLSchema')

    XML::Namespace.new(@odoc.root, 'dd3', 'http://dd.eionet.europa.eu/namespace.jsp?ns_id=3')
    XML::Namespace.new(@odoc.root, 'dd2', 'http://dd.eionet.europa.eu/namespace.jsp?ns_id=2')
    XML::Namespace.new(@odoc.root, 'dd1', 'http://dd.eionet.europa.eu/namespace.jsp?ns_id=1')
    XML::Namespace.new(@odoc.root, 'dd816', 'http://dd.eionet.europa.eu/namespace.jsp?ns_id=816')
    XML::Namespace.new(@odoc.root, 'dd817', 'http://dd.eionet.europa.eu/namespace.jsp?ns_id=817')
    XML::Namespace.new(@odoc.root, 'dd818', 'http://dd.eionet.europa.eu/namespace.jsp?ns_id=818')
    XML::Namespace.new(@odoc.root, 'dd843', 'http://dd.eionet.europa.eu/namespace.jsp?ns_id=843')
    XML::Namespace.new(@odoc.root, 'dd844', 'http://dd.eionet.europa.eu/namespace.jsp?ns_id=844')
    XML::Namespace.new(@odoc.root, 'dd845', 'http://dd.eionet.europa.eu/namespace.jsp?ns_id=845')
    XML::Namespace.new(@odoc.root, 'dd846', 'http://dd.eionet.europa.eu/namespace.jsp?ns_id=846')
    XML::Namespace.new(@odoc.root, 'dd865', 'http://dd.eionet.europa.eu/namespace.jsp?ns_id=865')
    XML::Namespace.new(@odoc.root, 'dd864', 'http://dd.eionet.europa.eu/namespace.jsp?ns_id=864')
    XML::Namespace.new(@odoc.root, 'dd820', 'http://dd.eionet.europa.eu/namespace.jsp?ns_id=820')
    XML::Namespace.new(@odoc.root, 'dd821', 'http://dd.eionet.europa.eu/namespace.jsp?ns_id=821')
    XML::Namespace.new(@odoc.root, 'dd862', 'http://dd.eionet.europa.eu/namespace.jsp?ns_id=862')
    XML::Namespace.new(@odoc.root, 'dd823', 'http://dd.eionet.europa.eu/namespace.jsp?ns_id=823')
    XML::Namespace.new(@odoc.root, 'dd825', 'http://dd.eionet.europa.eu/namespace.jsp?ns_id=825')
    XML::Namespace.new(@odoc.root, 'dd826', 'http://dd.eionet.europa.eu/namespace.jsp?ns_id=826')
    
    # Die Attribute verhindern derzeit eine erfolgreiche Validierung und sind daher auskommentiert:
    # Attribute setzen:
    # @odoc.root['xmlns:xsi'] = 
    # @odoc.root['xmlns:xsd'] = 'http://www.w3.org/2001/XMLSchema'
    # @odoc.root['xsi:noNamespaceSchemaLocation'] = 'http://water.eionet.europa.eu/schemas/dir91271eec/UWWTD2009.xsd'
    return @odoc.root
  end

  def prepare_coord_data
    print "Starte Koordinatenneuberechnung "
    @bar.text="coordinates..."
    coord_stmts = File.read("../sql/koordinatenberechnung.sql")

    # ruby-dbi kann nicht mit mehreren statements in einem Durchlauf -> wir splitten\
    coord_stmts.gsub!(/\n\n+/, "\n")
    stmts = coord_stmts.split(";")

    stmts.each do |stmt|
      if stmt.empty? || stmt == "\n"
        next
      end

      sth = dbh.execute(stmt)
      print "."
      pulse
    end
    puts ""

  end

  def create_document
    @bar.text="create XML..."
    @oroot << report = XML::Node.new('UWWTD_Report')
    xml_date = Time.now.strftime("%Y-%m-%dT%H:%M:%S.000+01:00")
    report['rptDateexported'] = xml_date
    report << create_reporter
    report << period = create_period
    report << contacts = create_contacts
    @dbh = get_connection
    report << create_areas_main
    report << create_areas_parameter
    report << create_areas_sa54
    report << create_agglomerations
    #report << create_big_city_dischargers
    report << create_plants
    report << create_agg_plant_correlation
    report << create_discharge_points
    # Industries haben wir nicht. Das Rumpfelement ist aber Pflicht:
    report << industries = XML::Node.new('Industries')
    report << create_member_state_info

    puts "\nDocument created"
    return @oroot
  end

  def write_document
    @bar.text="validating..."
    @odoc.save(MY_CONSTANT_XML_PATH, :indent => true, :encoding => XML::Encoding::UTF_8)
    valdoc = XML::Document.file(MY_CONSTANT_XML_PATH)
    schema_document = XML::Document.file('../schemata/UWWTD_Art15.xsd')
    schema = XML::Schema.document(schema_document)
    STDOUT.flush
    STDERR.flush

    begin
      result = valdoc.validate_schema(schema) do |message, is_error|
        # Das kann man sich getrost in die Haare reiben, denn validate_schema schmeißt eine Exception...
        if is_error
          puts "ERROR!"
        else
          puts "WARNING!"
        end
        puts "Anfang der Meldung:"
        puts message
        puts "Ende der Meldung."
      end
    rescue Exception => wumm
      pulse
      puts "Exception <#{wumm}>."
      # print wumm.backtrace.join("\n")
      puts
    end
    if result
      puts "Das Dokument ist bzgl. des Schemas perfekt"
    else
      puts "Das Dokument kann noch besser werden"
    end
  end

  def create_reporter
    reporter = XML::Node.new('Reporter')
    reporter << state_key = XML::Node.new('dd816:rptMStateKey')
    state_key << 'DE'
    reporter << culture = XML::Node.new('dd816:rptCulture')
    culture << "de-DE"
    # reporter << type_of_ra = XML::Node.new('dd816:rptFormRA')
    # type_of_ra << "3"
    pulse
    return reporter
  end

  def create_period
    period = XML::Node.new('ReportPeriod')
    period << state_key = XML::Node.new('dd817:rptMStateKey')
    state_key << "DE"
    period << rep_code = XML::Node.new('dd817:repCode')
    rep_code << REP_CODE
    period << rep_version = XML::Node.new('dd817:repVersion')
    rep_version << Time.now.strftime("%Y-%m-%d")
    period << rep_situation_at = XML::Node.new('dd817:repSituationAt')
    rep_situation_at << REP_SITUATION_AT
    pulse
    return period
  end

  def create_contacts
    contacts = XML::Node.new('Contacts')
    contacts << contact = XML::Node.new('Contact')
    contact << repcode = XML::Node.new('dd818:repCode')
    contact << name = XML::Node.new('dd818:conName')
    contact << institution = XML::Node.new('dd818:conInstitution')
    contact << street = XML::Node.new('dd818:conStreet')
    contact << zip = XML::Node.new('dd818:conZIP')
    contact << city = XML::Node.new('dd818:conCity')
    contact << phone = XML::Node.new('dd818:conPhone')
    contact << fax = XML::Node.new('dd818:conFax')
    contact << email = XML::Node.new('dd818:conEmail')
    contact << remarks = XML::Node.new('dd818:conRemarks')
    repcode << REP_CODE
    name << 'Dr. Joachim Heidemeier'
    institution << 'Umweltbundesamt'
    street << 'Wörlitzer Platz 1'
    zip << '06844'
    city << 'Dessau-Roßlau'
    phone << '+49 340 2103-2780'
    fax << '+49 340 2104-2780'
    email << 'joachim.heidemeier@uba.de'
    remarks << 'Fachgruppe II, 2.2 Stoffhaushalt Gewässer'
    pulse
    return contacts
  end

  # Simple Listen-id-zu-Schlüssel-Auflösung (1=C, 2=E, 3=M)
  def get_cem_representation_from_id(method_id)
    return 'c' if method_id == 1
    return 'e' if method_id == 2
    return 'm' if method_id == 3
    raise Exception.new, "ERROR: get_cem_representation_from_number case: <#{method_id}>"
  end

  # Hier wird entschieden, ob die Anlage als C, E oder M eingestuft wird.
  # Da wir die Anlagenzahl übermitteln müssen, müssen wir uns entscheiden, wo wir die
  # Anlage (und ihre Auslegungsgröße ODC) hinzurechnen.
  # Wenn wir nichts haben, ist es geschätzt. So haben wir auch in der Vergangenheit die
  # Kleinkläranlagen behandelt, bei denen das nicht angegeben wird.
  def detect_cem_state(row)
    if row['dp_load_n_incoming_method_id']
      return get_cem_representation_from_id(row['dp_load_n_incoming_method_id'])
    elsif row['dp_load_n_method_id']
      return get_cem_representation_from_id(row['dp_load_n_method_id'])
    elsif row['dp_load_p_incoming_method_id']
      return get_cem_representation_from_id(row['dp_load_p_incoming_method_id'])
    elsif row['dp_load_p_method_id']
      return get_cem_representation_from_id(row['dp_load_p_method_id'])
    else
      return 'e'
    end
  end

  def calculate_overall_data
    pulse
    area_data = {}
    stmt = "SELECT p.id, p.pl_odc,
      dp_load_n_incoming, dp_load_n_incoming_method_id, dp_load_n, dp_load_n_method_id,
      dp_load_p_incoming, dp_load_p_incoming_method_id, dp_load_p, dp_load_p_method_id
      FROM plants p
      WHERE state = 0
        AND date_from = #{DATE_FROM}
        AND federal_state_id < 17
        AND pl_state_active = 1
        AND pl_to_be_reported IS DISTINCT FROM 0"
    sth = dbh.execute(stmt)
    pulse
    counter = 0

    # Initialisierung des Werte-Hashs area_data mit 0:
    cemmis = ['c', 'e', 'm']
    cemmis.each do |cemmi|
      area_data['plants_number_' + cemmi] = 0
      area_data['plants_number_50to1999_' + cemmi] = 0
      area_data['plants_number_2000up_' + cemmi] = 0
      area_data['odc_' + cemmi] = 0
      area_data['n_in_' + cemmi] = 0.0
      area_data['n_out_' + cemmi] = 0.0
      area_data['p_in_' + cemmi] = 0.0
      area_data['p_out_' + cemmi] = 0.0
      area_data['odc_50to1999_' + cemmi] = 0
      area_data['n_in_50to1999_' + cemmi] = 0.0
      area_data['n_out_50to1999_' + cemmi] = 0.0
      area_data['p_in_50to1999_' + cemmi] = 0.0
      area_data['p_out_50to1999_' + cemmi] = 0.0
      area_data['odc_2000up_' + cemmi] = 0
      area_data['n_in_2000up_' + cemmi] = 0.0
      area_data['n_out_2000up_' + cemmi] = 0.0
      area_data['p_in_2000up_' + cemmi] = 0.0
      area_data['p_out_2000up_' + cemmi] = 0.0
      area_data['comment_' + cemmi] = ''
    end

    # Alle Kläranlagen Zu- und Ablauffrachten N und P bei KA >= 2000 EW zusammenzählen (ODC auch):
    sth.fetch do |row|
      counter += 1
      cem_state = detect_cem_state(row)
      area_data['plants_number_' + cem_state] += 1
      area_data['plants_number_2000up_' + cem_state] += 1
      area_data['odc_' + cem_state] += row['pl_odc']
      area_data['n_in_' + cem_state] += row['dp_load_n_incoming'] if row['dp_load_n_incoming']
      area_data['n_out_' + cem_state] += row['dp_load_n'] if row['dp_load_n']
      area_data['p_in_' + cem_state] += row['dp_load_p_incoming'] if row['dp_load_p_incoming']
      area_data['p_out_' + cem_state] += row['dp_load_p'] if row['dp_load_p']
      area_data['odc_2000up_' + cem_state] += row['pl_odc']
      area_data['n_in_2000up_' + cem_state] += row['dp_load_n_incoming'] if row['dp_load_n_incoming']
      area_data['n_out_2000up_' + cem_state] += row['dp_load_n'] if row['dp_load_n']
      area_data['p_in_2000up_' + cem_state] += row['dp_load_p_incoming'] if row['dp_load_p_incoming']
      area_data['p_out_2000up_' + cem_state] += row['dp_load_p'] if row['dp_load_p']
      if counter % 100 == 0
        pulse
        print "."
        STDOUT.flush
      end
    end
    sth.finish

    # Bei den Kleinkläranlagen wissen wir nicht, ob es C, E oder M ist. Bisher wurde es
    # immer der Gruppe E (estimated) zuschlagen, so machen wir es auch weiterhin:
    stmt = "SELECT f.id, f.kka_number, f.kka_cap,
      f.kka_load_n_incoming, f.kka_load_n,
      f.kka_load_p_incoming, f.kka_load_p
      FROM federal_state_info f
      WHERE state = 0
        AND date_from = #{DATE_FROM}
        AND federal_state_id < 17"
    sth = dbh.execute(stmt)
    sth.fetch do |row|
      cem_nil_state = 'e'
      area_data['plants_number_' + cem_nil_state] += row['kka_number']
      area_data['plants_number_50to1999_' + cem_nil_state] += row['kka_number']
      area_data['odc_' + cem_nil_state] += row['kka_cap']
      area_data['n_in_' + cem_nil_state] += row['kka_load_n_incoming']
      area_data['n_out_' + cem_nil_state] += row['kka_load_n']
      area_data['p_in_' + cem_nil_state] += row['kka_load_p_incoming']
      area_data['p_out_' + cem_nil_state] += row['kka_load_p']
      area_data['odc_50to1999_' + cem_nil_state] += row['kka_cap']
      area_data['n_in_50to1999_' + cem_nil_state] += row['kka_load_n_incoming']
      area_data['n_out_50to1999_' + cem_nil_state] += row['kka_load_n']
      area_data['p_in_50to1999_' + cem_nil_state] += row['kka_load_p_incoming']
      area_data['p_out_50to1999_' + cem_nil_state] += row['kka_load_p']
    end
    sth.finish
    pulse

    cemmis.each do |cemmi|
      # Einheit Fracht beim COM ist t, bei uns kg, daher / 1000
      # außerdem Nachkommastellenrauschen entfernen:
      area_data['n_in_' + cemmi] = sprintf("%.4e", area_data['n_in_' + cemmi] / 1000.0).to_f.to_s
      area_data['n_out_' + cemmi] = sprintf("%.4e", area_data['n_out_' + cemmi] / 1000.0).to_f.to_s
      area_data['p_in_' + cemmi] = sprintf("%.4e", area_data['p_in_' + cemmi] / 1000.0).to_f.to_s
      area_data['p_out_' + cemmi] = sprintf("%.4e", area_data['p_out_' + cemmi] / 1000.0).to_f.to_s
      area_data['n_in_50to1999_' + cemmi] = sprintf("%.4e", area_data['n_in_50to1999_' + cemmi] / 1000.0).to_f.to_s
      area_data['n_out_50to1999_' + cemmi] = sprintf("%.4e", area_data['n_out_50to1999_' + cemmi] / 1000.0).to_f.to_s
      area_data['p_in_50to1999_' + cemmi] = sprintf("%.4e", area_data['p_in_50to1999_' + cemmi] / 1000.0).to_f.to_s
      area_data['p_out_50to1999_' + cemmi] = sprintf("%.4e", area_data['p_out_50to1999_' + cemmi] / 1000.0).to_f.to_s
      area_data['n_in_2000up_' + cemmi] = sprintf("%.4e", area_data['n_in_2000up_' + cemmi] / 1000.0).to_f.to_s
      area_data['n_out_2000up_' + cemmi] = sprintf("%.4e", area_data['n_out_2000up_' + cemmi] / 1000.0).to_f.to_s
      area_data['p_in_2000up_' + cemmi] = sprintf("%.4e", area_data['p_in_2000up_' + cemmi] / 1000.0).to_f.to_s
      area_data['p_out_2000up_' + cemmi] = sprintf("%.4e", area_data['p_out_2000up_' + cemmi] / 1000.0).to_f.to_s
    end

    return area_data
  end

  # TODO: (ERA)
  def create_areas_sa54
    @bar.text="areas 5.4..."
    pulse
    # Hauptknoten anlegen:
    sa54 = XML::Node.new('ReceivingAreasSA54')
    # Drei Areas für C/E/M anlegen:
    sa54 << el_calculated = XML::Node.new('ReceivingAreaSA54')   # 1 x calculated-Summen
    sa54 << el_estimated = XML::Node.new('ReceivingAreaSA54')   # 1 x estimated-Summen
    sa54 << el_measured = XML::Node.new('ReceivingAreaSA54')   # 1 x measured-Summen

    # Standardwerte und jeweils C/E/M-Method als Element anlegen:
    el_calculated << repcode_c = XML::Node.new('dd845:repCode')
    el_calculated << code_c = XML::Node.new('dd845:rcaCode')
    el_calculated << method_c = XML::Node.new('dd845:rcaMethod54')
    el_estimated << repcode_e = XML::Node.new('dd845:repCode')
    el_estimated << code_e = XML::Node.new('dd845:rcaCode')
    el_estimated << method_e = XML::Node.new('dd845:rcaMethod54')
    el_measured << repcode_m = XML::Node.new('dd845:repCode')
    el_measured << code_m = XML::Node.new('dd845:rcaCode')
    el_measured << method_m = XML::Node.new('dd845:rcaMethod54')

    # Standard-Elementinhalte schreiben
    repcode_c << REP_CODE
    code_c << "DE"
    method_c << "C"
    repcode_e << REP_CODE
    code_e << "DE"
    method_e << "E"
    repcode_m << REP_CODE
    code_m << "DE"
    method_m << "M"
    # Kopfinfo ist angelegt und gefüllt

    # Rumpfelemente anlegen:
    el_calculated << plants_number_c = XML::Node.new('dd845:rcaPlants54')
    el_calculated << overall_design_capacity_c = XML::Node.new('dd845:rcaPlantsCapacity54')
    el_calculated << n_in_c = XML::Node.new('dd845:rcaNIncoming54')
    el_calculated << n_out_c = XML::Node.new('dd845:rcaNDischarged54')
    el_calculated << p_in_c = XML::Node.new('dd845:rcaPIncoming54')
    el_calculated << p_out_c = XML::Node.new('dd845:rcaPDischarged54')
    el_calculated << comment_c = XML::Node.new_comment()

    el_estimated << plants_number_e = XML::Node.new('dd845:rcaPlants54')
    el_estimated << overall_design_capacity_e = XML::Node.new('dd845:rcaPlantsCapacity54')
    el_estimated << n_in_e = XML::Node.new('dd845:rcaNIncoming54')
    el_estimated << n_out_e = XML::Node.new('dd845:rcaNDischarged54')
    el_estimated << p_in_e = XML::Node.new('dd845:rcaPIncoming54')
    el_estimated << p_out_e = XML::Node.new('dd845:rcaPDischarged54')
    el_estimated << comment_e = XML::Node.new_comment()

    el_measured << plants_number_m = XML::Node.new('dd845:rcaPlants54')
    el_measured << overall_design_capacity_m = XML::Node.new('dd845:rcaPlantsCapacity54')
    el_measured << n_in_m = XML::Node.new('dd845:rcaNIncoming54')
    el_measured << n_out_m = XML::Node.new('dd845:rcaNDischarged54')
    el_measured << p_in_m = XML::Node.new('dd845:rcaPIncoming54')
    el_measured << p_out_m = XML::Node.new('dd845:rcaPDischarged54')
    el_measured << comment_m = XML::Node.new_comment()
    # Rumpfelelemte angelegt.

    # Daten ermitteln:
    area_data = calculate_overall_data

    plants_number_c << area_data['plants_number_c']
    plants_number_e << area_data['plants_number_e']
    plants_number_m << area_data['plants_number_m']
    overall_design_capacity_c << area_data['odc_c']
    overall_design_capacity_e << area_data['odc_e']
    overall_design_capacity_m << area_data['odc_m']
    n_in_c << area_data['n_in_c']
    n_in_e << area_data['n_in_e']
    n_in_m << area_data['n_in_m']
    n_out_c << area_data['n_out_c']
    n_out_e << area_data['n_out_e']
    n_out_m << area_data['n_out_m']
    p_in_c << area_data['p_in_c']
    p_in_e << area_data['p_in_e']
    p_in_m << area_data['p_in_m']
    p_out_c << area_data['p_out_c']
    p_out_e << area_data['p_out_e']
    p_out_m << area_data['p_out_m']
    comment_c << " Only Plants >= 2000 p.e. "
    comment_e << "\nPlant number 50 to 1999 p.e.: " + area_data['plants_number_50to1999_e'].to_s +
        ";\nCap. 50 to 1999 p.e.: " + area_data['odc_50to1999_e'].to_s +
        ";\nLoads Plants 50 to 1999 p.e.:\n  N in = " + area_data['n_in_50to1999_e'].to_s +
        ",\n  N out = " + area_data['n_out_50to1999_e'].to_s +
        ",\n  P in = " + area_data['p_in_50to1999_e'].to_s +
        ",\n  P out = " + area_data['p_out_50to1999_e'].to_s +
        ";\nPlant number >= 2000 p.e.: " + area_data['plants_number_2000up_e'].to_s +
        ";\nCap. >= 2000 p.e.: " + area_data['odc_2000up_e'].to_s +
        ";\nLoads Plants >= 2000 p.e.:\n  N in = " + area_data['n_in_2000up_e'].to_s +
        ",\n  N out = " + area_data['n_out_2000up_e'].to_s +
        ",\n  P in = " + area_data['p_in_2000up_e'].to_s +
        ",\n  P out = " + area_data['p_out_2000up_e'].to_s
    comment_m << " Only Plants >= 2000 p.e. "
    pulse

    return sa54
  end

  def create_areas_parameter
    pars = XML::Node.new('ReceivingAreasSAParameter')

    pars << par_an = XML::Node.new('ReceivingAreaSAParameter')
    par_an << repcode = XML::Node.new('dd844:repCode')
    par_an << code = XML::Node.new('dd844:rcaCode')
    par_an << parcode = XML::Node.new('dd844:rcaParameter')
    par_an << date_designation = XML::Node.new('dd844:rcaDateDesignation')
    repcode << REP_CODE
    code << "DE"
    parcode << "aN"
    date_designation << "1999-01-01"
    
    pars << par_an = XML::Node.new('ReceivingAreaSAParameter')
    par_an << repcode = XML::Node.new('dd844:repCode')
    par_an << code = XML::Node.new('dd844:rcaCode')
    par_an << parcode = XML::Node.new('dd844:rcaParameter')
    par_an << date_designation = XML::Node.new('dd844:rcaDateDesignation')
    repcode << REP_CODE
    # Evtl. hier Ziffernkombi statt DE
    code << "DE"
    parcode << "aP"
    date_designation << "1999-01-01"
    pulse

    return pars
  end

  def create_areas_main
    areas = XML::Node.new('ReceivingAreasSAMain')
    areas << area = XML::Node.new('ReceivingAreaSAMain')
    area << repcode = XML::Node.new('dd843:repCode')
    area << code = XML::Node.new('dd843:rcaCode')
    area << name = XML::Node.new('dd843:rcaName')
    area << envdom = XML::Node.new('dd843:rcaEnvDom')
   
    area << z_type = XML::Node.new('dd843:rcaZtype')
    area << sp_z_typ = XML::Node.new('dd843:rcaSpZTyp')

    # Weitergehende Behandlung für N (true/false):
    area << parameter_n = XML::Node.new('dd843:rcaParameterN')
    # equipped with a more stringent treatment P removal:
    area << parameter_p = XML::Node.new('dd843:rcaParameterP')
    # equipped with a more stringent treatment microbiological removal:
    area << parameter_m = XML::Node.new('dd843:rcaParameterM')
    # All agglomerations of more than 10 000 p.e. discharging in this area have to be equipped
    # with a more stringent treatment different from the previous ones.
    # rcaRemarks can be use to precise the type of treatment.
    area << parameter_o = XML::Node.new('dd843:rcaParameterOther')

    # mit was füllen wir die Typen? vss. tatsächlich A5854 wg.:
    # Art. 5(8) and 5(4) area (entire Member State) (only possible if Article 5.4 applies before or at the reference year of the report)
    z_type << 'UWWCMSA'
    sp_z_typ << 'A5854'

    # Womit füllen wir die vier?
    parameter_n << 'true'
    parameter_p << 'true'
    parameter_m << 'true'
    parameter_o << 'true'

    repcode << REP_CODE
    code << "DE"
    name << "Deutschland"
    envdom << "WATER"

    area << date_art54 = XML::Node.new('dd843:rcaDateArt54')
    date_art54 << "1999-01-01"

    pulse

    return areas
  end

  def fill_agglo(agglo, row)
    agglo << state = XML::Node.new('dd820:aggState')
    active = true
    active_text = "aktiv und berichtet"
    active_flag = 1
    if row['agg_state_active'] == 0
      active = false
      active_text = "inaktiv"
      active_flag = 0
    elsif row['ag_to_be_reported'] == 0
      active = false
      active_text = "aktiv, aber nicht berichtet"
      active_flag = 2
    end
    state << active_flag
    agglo << rep_code  = XML::Node.new('dd820:repCode')
    rep_code << REP_CODE
    agglo << code = XML::Node.new('dd820:aggCode')
    code << row['agg_eu_key']
    agglo << name = XML::Node.new('dd820:aggName')
    name << row['ag_name']
    if row['nuts']
      agglo << nuts = XML::Node.new('dd820:aggNUTS')
      nuts << row['nuts']
    end
    if row['ag_eu_lat']
      agglo << latitude = XML::Node.new('dd820:aggLatitude')
      latitude << sprintf("%.6e", row['ag_eu_lat']).to_f.to_s
    end
    if row['ag_eu_long']
      agglo << longitude = XML::Node.new('dd820:aggLongitude')
      longitude << sprintf("%.6e", row['ag_eu_long']).to_f.to_s
    end
    if row['ag_nload']
      agglo << gen_load = XML::Node.new('dd820:aggGenerated')
      gen_load << row['ag_nload']
    else
      agglo << gen_load = XML::Node.new('dd820:aggGenerated')
      gen_load << "0"
      if active then
        puts "FEHLER: Land #{row['federal_state_id']}, agglo #{row['ag_name']} (#{row['agg_key']}), state = #{active_flag}, hat keine ag_nload! Trage -1 ein."
      end
    end
    if row['pl_city_id']
      agglo << big_city = XML::Node.new('dd820:bigCityID')
      big_city << row['city_key']
    end
    agglo << rem_nload = XML::Node.new('dd820:aggCalculation')
    if row['ag_remark_nload']
      rem_nload << row['ag_remark_nload']
    else
      rem_nload << "Ausbaugröße mal durchschnittlicher Auslastungsgrad im Berichtsjahr"
    end
    agglo << ag_change_nload = XML::Node.new('dd820:aggChanges')
    
    if row['ag_remark_change']
      # Text vorher laden, da er ggf. in ag_change_nload überschrieben werden muss:
      change_remark = row['ag_remark_change']
    end
    if row['ag_change_nload']
      ag_change_nload << row['ag_change_nload']
    else
      case active_flag
      when 0 then
        ag_change_nload << '1'
        change_remark = 'Anlage wurde stillgelegt'
      when 2 then
        ag_change_nload << '1'
        change_remark = 'nominal load kleiner 2.000 EW'
      else
        ag_change_nload << '0'
      end
    end
    poke(agglo, change_remark, 'dd820:aggChangesComment')
    # agglo << ag_rem_change_nload = XML::Node.new('aggChangesComment')
    # ag_rem_change_nload << change_remark
    
    if row['ag_date']
      agglo << ag_date = XML::Node.new('dd820:aggPeriodOver')
      ag_date << Stnd.numdate_to_iso(row['ag_date'])
    end
    if row['agg_c1']
      agglo << agg_c1 = XML::Node.new('dd820:aggC1')
      agg_c1 << row['agg_c1']
      if row['agg_meth_c1_key']
        agglo << agg_meth_c1 = XML::Node.new('dd820:aggMethodC1')
        agg_meth_c1 << row['agg_meth_c1_key']
      end
    else
      # Wenn das Ding state=2 hat, soll hier lt. COM ein dummy-Wert rein:
      # "These fields may be filled with dummy values such as 0"
      # (E-Mail 2016-06-24 08:57, From: UWWTD Helpdesk <uwwtd.helpdesk@eionet.europa.eu>)
      if active_flag == 2
        agglo << agg_c1 = XML::Node.new('dd820:aggC1')
        agg_c1 << "0"
      end
    end
    if row['agg_c2']
      agglo << agg_c2 = XML::Node.new('dd820:aggC2')
      agg_c2 << row['agg_c2']
      if row['agg_meth_c2_key']
        agglo << agg_meth_c2 = XML::Node.new('dd820:aggMethodC2')
        agg_meth_c2 << row['agg_meth_c2_key']
      end
    else
      # Wenn das Ding state=2 hat, soll hier lt. COM ein dummy-Wert rein:
      # "These fields may be filled with dummy values such as 0"
      # (E-Mail 2016-06-24 08:57, From: UWWTD Helpdesk <uwwtd.helpdesk@eionet.europa.eu>)
      if active_flag == 2
        agglo << agg_c2 = XML::Node.new('dd820:aggC2')
        agg_c2 << "0"
      end
    end
    if row['agg_not_collected']
      agglo << agg_not_collected = XML::Node.new('dd820:aggPercWithoutTreatment')
      agg_not_collected << row['agg_not_collected']
      if row['agg_meth_not_coll_key']
        agglo << agg_meth_not_coll_key = XML::Node.new('dd820:aggMethodWithoutTreatment')
        agg_meth_not_coll_key << row['agg_meth_not_coll_key']
      end
    else
      # Wenn das Ding state=2 hat, soll hier lt. COM ein dummy-Wert rein:
      # "These fields may be filled with dummy values such as 0"
      # (E-Mail 2016-06-24 08:57, From: UWWTD Helpdesk <uwwtd.helpdesk@eionet.europa.eu>)
      if active_flag == 2
        agglo << agg_not_collected = XML::Node.new('dd820:aggPercWithoutTreatment')
        agg_not_collected << "0"
      end
    end
    if row['agg_c2_pt']
      agglo << agg_c2_pt = XML::Node.new('dd820:aggPercPrimTreatment')
      agg_c2_pt << row['agg_c2_pt']
    end
    if row['agg_c2_st']
      agglo << agg_c2_st = XML::Node.new('dd820:aggPercSecTreatment')
      agg_c2_st << row['agg_c2_st']
    end
    if row['agg_c2_ft']
      agglo << agg_c2_ft = XML::Node.new('dd820:aggPercStringentTreatment')
      agg_c2_ft << row['agg_c2_ft']
    end
    if row['ag_remark']
      agglo << ag_remark = XML::Node.new('dd820:aggRemarks')
      ag_remark << row['ag_remark']
    end
  end

  def create_agglomerations
    @bar.text="agglomerations..."
    agglos = XML::Node.new('Agglomerations')
    
    stmt = "select a.*, lrc.key as nuts, lci.key as city_key,
        lm1.key as agg_meth_c1_key, lm2.key as agg_meth_c2_key,
        lm3.key as agg_meth_not_coll_key
      from agglomerations a
      left join l_region_code lrc
        on a.agg_nuts_id = lrc.id
        and lrc.state = 0
      left join l_cities lci
        on a.pl_city_id = lci.id
        and lci.state = 0
      left join l_meth lm1
        on a.agg_meth_c1_id = lm1.id
        and lm1.state = 0
      left join l_meth lm2
        on a.agg_meth_c2_id = lm2.id
        and lm2.state = 0
      left join l_meth lm3
        on a.agg_meth_not_coll_id = lm3.id
        and lm3.state = 0
      where a.date_from = #{DATE_FROM} and a.state = 0 and a.federal_state_id < 17
        -- and a.agg_state_active = 1
        -- and a.ag_to_be_reported is distinct from 0
        -- and a.ag_nload > 100000
      order by a.federal_state_id, a.agg_eu_key"
    print "SQL-Statement läuft ... #{Stnd.now}\n"
    sth = dbh.execute(stmt)
    pulse
    print "SQL-Statement fertig    #{Stnd.now}\n"
    counter = 0
    sth.fetch do |row|
      counter += 1
      agglos << agglo = XML::Node.new('Agglomeration')
      fill_agglo(agglo, row)
      if counter % 100 == 0
        pulse
        print "."
        STDOUT.flush
      end
      break if $testrun && counter >= 2
    end
    print "\n"
    puts "\n#{counter} Agglomerationen geschrieben\n"
    return agglos
  end

  def create_big_city_dischargers
    big_disses = XML::Node.new('BigCityDischargers')
    stmt = "select distinct lc.key, lc.description
      from l_cities lc
        inner join agglomerations a
          on lc.id = a.pl_city_id
          and a.state = 0
          and a.date_from = #{DATE_FROM}
          and a.ag_city = 1
      where lc.state = 0
      order by lc.key"
    print "SQL-Statement läuft ... #{Stnd.now}\n"
    sth = dbh.execute(stmt)
    pulse
    print "SQL-Statement fertig    #{Stnd.now}\n"
    counter = 0
    sth.fetch do |row|
      counter += 1
      big_disses << big_dis = XML::Node.new('BigCityDischarger')
      big_dis << city_key = XML::Node.new('bigCityID')
      city_key << row['key']
      big_dis << country_key = XML::Node.new('bigCountryCode')
      country_key << "DE"
      big_dis << city_name = XML::Node.new('bigCity')
      city_name << row['description']
      if counter % 100 == 0
        pulse
        print "."
        STDOUT.flush
      end
      break if $testrun && counter >= 2
    end
    print "\n"
    return big_disses
  end


  # fill_node looks whether the column of row has a content
  # if so, it creates an element inside node and fills it with the content
  def fill_node (node, row, column_name, element_name)
    if row[column_name]
      node << var = XML::Node.new(element_name)
      var << row[column_name]
    end
  end

  # fill_node_bool does it like fill_node and converts numeric boolean
  # values 0/1 to false/true to fulfill COM XML Schema requirements
  def fill_node_bool(node, row, column_name, element_name)
    if row[column_name]
      node << var = XML::Node.new(element_name)
      var << (row[column_name] == 1 ? 'true' : 'false')
    end
  end


  def fill_node_yesno(node, row, column_name, element_name)
    if row[column_name]
      node << var = XML::Node.new(element_name)
      var << (row[column_name] == 1 ? 'Y' : 'N')
    end
  end


  def fill_node_10(node, row, column_name, element_name)
    if row[column_name]
      node << var = XML::Node.new(element_name)
      var << (row[column_name] == 1 ? '1' : '0')
    end
  end


  def fill_node_compliance(node, row, column_name, element_name)
    if row[column_name] && row[column_name] != 3
      value = case row[column_name]
                when 1, 4 then 'P'
                when 2 then 'F'
              # when 4 then 'NR'
                else raise Exception.new, "ERROR: xml_art15_exporter.rb, fill_node_compliance, case: else reached"
              end
      node << var = XML::Node.new(element_name)
      var << value
    end
  end

  # poke takes a content and looks if it is not nil
  # if not nil, it creates an element inside node and fills it with the content
  def poke (node, content, element_name)
    if content
      node << var = XML::Node.new(element_name)
      var << content
    end
  end
  
  
  def fill_plant(p_node, p_row)
    p_node << state = XML::Node.new('dd821:uwwState')
    active = true
    active_text = "aktiv und berichtet"
    active_flag = 1
    if p_row['pl_state_active'] == 0
      active = false
      active_text = "inaktiv"
      active_flag = 0
    elsif p_row['pl_to_be_reported'] == 0
      active = false
      active_text = "aktiv, aber nicht berichtet"
      active_flag = 2
    end
    state << active_flag

    p_node << rep_code = XML::Node.new('dd821:repCode')
    rep_code << REP_CODE

    # TODO: Leider hat die Anwendung offenbar einen Bug, der date_from und date_to der Tabelle
    # agglomerations_plants mit null füllt anstatt ordentich mit 20140101 - 20141231 (z. B.)
    # Dann findet dieses Statement keine Zuordnungen!
    # Lösungen: 1. das bescheuerte date_from aus der where-Clause raus. Wozu überhaupt!
    # Die ID ist genau mit einem Satz verbunden und der kommt aus den Agglomerations, hat
    # also das korrekte from_date.
    stmt = "select agglomeration_id from agglomerations_plants ap
      where state = 0 and date_from = #{DATE_FROM}
        and plant_id = ? "
    rows = dbh.select_all(stmt, p_row['id'])
    if rows.length == 1
      agglo_id = rows[0][0]
      stmt = "select agg_eu_key from agglomerations where id = ?"
      agg_eu_key = dbh.select_one(stmt, agglo_id)[0]
      p_node << agg_eu_key_node = XML::Node.new('dd821:aggCode')
      agg_eu_key_node << agg_eu_key
    end

    p_node << code = XML::Node.new('dd821:uwwCode')
    code << p_row['pl_eu_key']

    p_node << name = XML::Node.new('dd821:uwwName')
    name << p_row['pl_name']

    if p_row['pl_kan_id']
      p_node << objekttyp_kanal_oder_ka = XML::Node.new('dd821:uwwCollectingSystem')
      objekttyp_kanal_oder_ka << (p_row['pl_kan_id'] == 2 ? 'NOTCON' : 'ISCON')
    end
    
    if p_row['pl_cd']
      p_node << closing_date = XML::Node.new('dd821:uwwDateClosing')
      closing_date << Stnd.numdate_to_iso(p_row['pl_cd'])
    end
    
    if p_row['pl_remark_history']
      p_node << remark_history = XML::Node.new('dd821:uwwHistorie')
      remark_history << p_row['pl_remark_history']
    end
    
    if p_row['pl_eu_lat']
      p_node << latitude = XML::Node.new('dd821:uwwLatitude')
      latitude << sprintf("%.6e", p_row['pl_eu_lat']).to_f.to_s
    end

    if p_row['pl_eu_long']
      p_node << longitude = XML::Node.new('dd821:uwwLongitude')
      longitude << sprintf("%.6e", p_row['pl_eu_long']).to_f.to_s
    end

    if p_row['nuts']
      p_node << nuts = XML::Node.new('dd821:uwwNUTS')
      nuts << p_row['nuts']
    end
    
    fill_node(p_node, p_row, 'pl_nload', 'dd821:uwwLoadEnteringUWWTP')
    fill_node(p_node, p_row, 'pl_odc', 'dd821:uwwCapacity')
    if p_row['pl_tt_id']
      tt = p_row['pl_tt_id']
      poke(p_node, (tt >= 1 && tt < 4 ? 'true' : 'false'), 'dd821:uwwPrimaryTreatment')
      poke(p_node, (tt >= 2 && tt < 4 ? 'true' : 'false'), 'dd821:uwwSecondaryTreatment')
      poke(p_node, (tt == 3           ? 'true' : 'false'), 'dd821:uwwOtherTreatment')
    end
    fill_node_bool(p_node, p_row, 'pl_n_tech', 'dd821:uwwNRemoval')
    fill_node_bool(p_node, p_row, 'pl_p_tech', 'dd821:uwwPRemoval')
    fill_node_bool(p_node, p_row, 'pl_uv', 'dd821:uwwUV')
    fill_node_bool(p_node, p_row, 'pl_chlorination', 'dd821:uwwChlorination')
    fill_node_bool(p_node, p_row, 'pl_ozonation', 'dd821:uwwOzonation')
    fill_node_bool(p_node, p_row, 'pl_sand_filtration', 'dd821:uwwSandFiltration')
    fill_node_bool(p_node, p_row, 'pl_micro_filtration', 'dd821:uwwMicroFiltration')
    fill_node_bool(p_node, p_row, 'pl_other', 'dd821:uwwOther')
    fill_node(p_node, p_row, 'pl_other_comment', 'dd821:uwwSpecification')

    #if p_row['pl_bod_anh1_id'] && p_row['pl_bod_anh1_id'] != 3
    #  cmpl = p_row['pl_bod_anh1_id']
    #  poke(p_node, (cmpl == 1 || cmpl == 4 ? 'P' : 'F'), 'uwwBOD5Perf')
    #end

    fill_node_compliance(p_node, p_row, 'pl_codcom_id', 'dd821:uwwBOD5Perf')
    fill_node_compliance(p_node, p_row, 'pl_bodcom_id', 'dd821:uwwCODPerf')
    fill_node_compliance(p_node, p_row, 'pl_ncom_id', 'dd821:uwwNTotPerf')
    fill_node_compliance(p_node, p_row, 'pl_pcom_id', 'dd821:uwwPTotPerf')
    
    # fill_node(p_node, p_row, 'pl_failure', 'uwwOtherPerf')
    
    if p_row['pl_failure_reason_id']
      fail = p_row['pl_failure_reason_id']
      poke(p_node, (fail == 1 ? 'true' : 'false'), 'dd821:uwwBadPerformance')
      poke(p_node, (fail == 2 ? 'true' : 'false'), 'dd821:uwwAccidents')
      poke(p_node, (fail == 3 ? 'true' : 'false'), 'dd821:uwwBadDesign')
      fill_node(p_node, p_row, 'pl_failure_comment', 'dd821:uwwInformation')
    else
      poke(p_node, 'false', 'dd821:uwwBadPerformance')
      poke(p_node, 'false', 'dd821:uwwAccidents')
      poke(p_node, 'false', 'dd821:uwwBadDesign')
    end
    
    if p_row['dp_load_n_incoming_method_id'] && p_row['dp_load_n_incoming']
      load_in_tons = sprintf("%.4e", (p_row['dp_load_n_incoming'] / 1000.0)).to_f.to_s
      case p_row['dp_load_n_incoming_method_id']
      when 3 then poke(p_node, load_in_tons, 'dd821:uwwNIncomingMeasured')
      when 1 then poke(p_node, load_in_tons, 'dd821:uwwNIncomingCalculated')
      when 2 then poke(p_node, load_in_tons, 'dd821:uwwNIncomingEstimated')
      end
    end

    if p_row['dp_load_p_incoming_method_id'] && p_row['dp_load_p_incoming']
      load_in_tons = sprintf("%.4e", (p_row['dp_load_p_incoming'] / 1000.0)).to_f.to_s
      case p_row['dp_load_p_incoming_method_id']
      when 3 then poke(p_node, load_in_tons, 'dd821:uwwPIncomingMeasured')
      when 1 then poke(p_node, load_in_tons, 'dd821:uwwPIncomingCalculated')
      when 2 then poke(p_node, load_in_tons, 'dd821:uwwPIncomingEstimated')
      end
    end

    if p_row['dp_load_n_method_id'] && p_row['dp_load_n']
      load_in_tons = sprintf("%.4e", (p_row['dp_load_n'] / 1000.0)).to_f.to_s
      case p_row['dp_load_n_method_id']
      when 3 then poke(p_node, load_in_tons, 'dd821:uwwNDischargeMeasured')
      when 1 then poke(p_node, load_in_tons, 'dd821:uwwNDischargeCalculated')
      when 2 then poke(p_node, load_in_tons, 'dd821:uwwNDischargeEstimated')
      end
    end

    if p_row['dp_load_p_method_id'] && p_row['dp_load_p']
      load_in_tons = sprintf("%.4e", (p_row['dp_load_p'] / 1000.0)).to_f.to_s
      case p_row['dp_load_p_method_id']
      when 3 then poke(p_node, load_in_tons, 'dd821:uwwPDischargeMeasured')
      when 1 then poke(p_node, load_in_tons, 'dd821:uwwPDischargeCalculated')
      when 2 then poke(p_node, load_in_tons, 'dd821:uwwPDischargeEstimated')
      end
    end

    fill_node(p_node, p_row, 'dp_volume', 'dd821:uwwWasteWaterTreated')
    fill_node(p_node, p_row, 'dp_volume_method_key', 'dd821:uwwMethodWasteWaterTreated')
    fill_node(p_node, p_row, 'pl_remark', 'dd821:uwwRemarks')
    # fill_node(p_node, p_row, 'pl_eper_eu_key', 'dd821:uwwE-PRTRCode')
  end


  def create_plants
    @bar.text="plants..."
    plants = XML::Node.new('UWWTPs')
    
    stmt = "select p.*, lrc.key as nuts, lm1.key as dp_volume_method_key
      from plants p
      left join l_region_code lrc
        on p.pl_nuts_id = lrc.id
        and lrc.state = 0
      left join l_meth lm1
        on p.dp_volume_method_id = lm1.id
        and lm1.state = 0
      where p.date_from = #{DATE_FROM} and p.state = 0 and p.federal_state_id < 17
      order by p.federal_state_id, p.pl_eu_key"
    print "SQL-Statement läuft ... #{Stnd.now}\n"
    sth = dbh.execute(stmt)
    pulse
    print "SQL-Statement fertig    #{Stnd.now}\n"
    counter = 0
    sth.fetch do |row|
      counter += 1
      plants << plant = XML::Node.new('UWWTP')
      fill_plant(plant, row)
      if counter % 10 == 0
        pulse
        if counter % 100 == 0
          print "+"
        else
          print "."
        end
        print "\n" if counter % 1000 == 0
        STDOUT.flush
      end
      break if $testrun && counter >= 2
    end
    print "\n"
    puts "\n#{counter} Plants geschrieben\n"

    return plants
  end
  

  def fill_ap_corr(apc_node, ap_row)
    fill_node(apc_node, ap_row, 'pl_eu_key', 'dd862:aucUwwCode')
    fill_node(apc_node, ap_row, 'pl_name', 'dd862:aucUwwName')
    fill_node(apc_node, ap_row, 'agg_eu_key', 'dd862:aucAggCode')
    fill_node(apc_node, ap_row, 'ag_name', 'dd862:aucAggName')
    # mlt, 2010-05-07: Unterschiedliche Interpretation von aucPercEnteringUWWTP zwischen COM und DE macht es notwendig,
    #   den Anteil der Kläranlage mit der Sammel-und-Reinigungsrate agg_c1_ka zu multiplizieren:
    if ap_row['agg_percent_plant'] && ap_row['agg_c1_ka']
      # auc_perc_entering_plant = sprintf("%.3e", ap_row['agg_percent_plant'] * (ap_row['agg_c1_ka'] / 100.0)).to_f
      # Nee, nun doch nicht. Daten in der Datenbank alle korrigiert ... :
      auc_perc_entering_plant = ap_row['agg_percent_plant']
      poke(apc_node, auc_perc_entering_plant, 'dd862:aucPercEnteringUWWTP')
    end
    if ap_row['agg_percent_plant']
      # Das ist hier etwas heikel, da die Methode eigentlich zum Feld agglomerations.agg_c1_ka gehört.
      # Wir geben das auf jeden Fall nur aus, wenn auch eine Prozentangabe vorhanden ist.
      fill_node(apc_node, ap_row, 'agg_meth_ka_key', 'dd862:aucMethodPercEnteringUWWTP')
    end
    fill_node(apc_node, ap_row, 'agg_auc_c2t', 'dd862:aucPercC2T')
  end


  def create_agg_plant_correlation
    @bar.text="agglo-plant-correlation..."
    ap_corrs = XML::Node.new('UwwtpAgglos')
    stmt = "select p.pl_eu_key, p.pl_name, a.agg_eu_key, a.ag_name, ap.agg_percent_plant,
        a.agg_meth_ka_id, lm.key as agg_meth_ka_key, a.agg_auc_c2t,
        a.agg_c1_ka
      from agglomerations_plants ap
        join agglomerations a
          on ap.agglomeration_id = a.id
          and a.state = 0
          and a.date_from = #{DATE_FROM}
          and a.federal_state_id < 18
        join plants p
          on ap.plant_id = p.id
          and p.state = 0
          and p.date_from = #{DATE_FROM}
          and p.federal_state_id < 18
        left join l_meth lm
          on a.agg_meth_ka_id = lm.id
          and lm.state = 0
      where ap.state = 0 and (ap.date_from = #{DATE_FROM} or ap.date_from is null)
      order by a.federal_state_id, a.agg_eu_key"
    print "SQL-Statement läuft ... #{Stnd.now}\n"
    sth = dbh.execute(stmt)
    pulse
    print "SQL-Statement fertig    #{Stnd.now}\n"
    counter = 0
    sth.fetch do |row|
      counter += 1
      ap_corrs << ap_corr = XML::Node.new('UwwtpAgglo')
    ap_corr << rep_code = XML::Node.new('dd862:repCode')
    rep_code << REP_CODE
      fill_ap_corr(ap_corr, row)
      if counter % 100 == 0
        pulse
        print "."
        STDOUT.flush
      end
      break if $testrun && counter >= 2
    end
    print "\n"
    puts "\n#{counter} Agglomeration-Plant-Correlations geschrieben\n"
    
    return ap_corrs
  end
  

  def fill_dp(dp_node, dp_row)
    dp_node << state = XML::Node.new('dd823:dcpState')
    active = true
    active_text = "aktiv und berichtet"
    active_flag = 1
    if dp_row['dp_state_active'] == 0
      active = false
      active_text = "aktiv und berichtet"
      active_flag = 0
    elsif dp_row['dp_to_be_reported'] == 0
      active = false
      active_text = "aktiv und berichtet"
      active_flag = 2
    end
    state << active_flag
    
    dp_node << rep_code = XML::Node.new('dd823:repCode')
    rep_code << REP_CODE
    
    fill_node( dp_node, dp_row, 'pl_eu_key', 'dd823:uwwCode')
    if !dp_row['pl_eu_key']
      puts "FEHLER: DP #{dp_row['dp_eu_key']} aus Land #{dp_row['federal_state_id']} hat keinen pl_eu_key!!!!!!!!!!!!!"
    end
    
    fill_node( dp_node, dp_row, 'dp_eu_key', 'dd823:dcpCode')
    fill_node( dp_node, dp_row, 'dp_name', 'dd823:dcpName')
    fill_node( dp_node, dp_row, 'nuts', 'dd823:dcpNUTS')

    if dp_row['dp_eu_lat']
      dp_node << latitude = XML::Node.new('dd823:dcpLatitude')
      latitude << sprintf("%.6e", dp_row['dp_eu_lat']).to_f.to_s
    end
    if dp_row['dp_eu_long']
      dp_node << longitude = XML::Node.new('dd823:dcpLongitude')
      longitude << sprintf("%.6e", dp_row['dp_eu_long']).to_f.to_s
    end
    
    # dcpWaterBodyType:
    if dp_row['dp_surface_sp_id']
      # dp.dp_surface_sp_id <--> l_waters.id
      # l_waters hält leider nicht die EU-Schlüssel, daher müssen wir low-level auf der id arbeiten
      wbt_key = case dp_row['dp_surface_sp_id']
                when 1 then 'LF' # Land
                when 2 then 'FW' # Süßwasser
                when 3 then 'ES' # Ästuar
                when 4 then 'CW' # Küste
                else raise Exception.new, "ERROR: xml_art15_exporter.rb, fill_dp, case dp_surface_sp_id: else reached"
                end
      poke(dp_node, wbt_key, 'dd823:dcpWaterBodyType')
      # dcpIrrigation:
      if dp_row['dp_surface_sp_id'] == 1
        if dp_row['dp_surface_land_id']
          irrigation_key = case dp_row['surface_land_key']
                           when 'BEWAESSERUNG' then 'IR' # Irrigation
                           when 'INFILTRATION' then 'IN' # Infiltration
                           when 'SONSTIGE' then 'OT' # Other
                           else raise Exception.new, "ERROR: xml_art15_exporter.rb, fill_dp, case surface_land_key: else reached"
                           end
          poke(dp_node, irrigation_key, 'dd823:dcpIrrigation')
        else
          puts "FEHLER: Land #{dp_row['federal_state_id']}, dp #{dp_row['dp_name']} (#{dp_row['dp_key']}) hat keine dp_surface_land_id, obwohl die Einleitung in Land erfolgt (Versickerung)"
        end
      end
    else
      puts "FEHLER: Land #{dp_row['federal_state_id']}, dp #{dp_row['dp_name']} (#{dp_row['dp_key']}), active = #{active ? "1" : "0"}, hat keine dp_surface_sp_id. dcpWaterBodyType kann nicht gefüllt werden!"
    end
    
    poke(dp_node, 'A54', 'dd823:dcpTypeOfReceivingArea')
    # Evtl. hier Ziffernkombi statt DE
    poke(dp_node, 'DE', 'dd823:rcaCode') # Das ist die ID, die oben für die Receiving Area Deutschland hartcodiert vergeben wurde
    fill_node_10(dp_node, dp_row, 'dp_surface', 'dd823:dcpSurfaceWaters')
    fill_node(dp_node, dp_row, 'water_body_eu_key', 'dd823:dcpWaterbodyID')
    
    # Nur bei Versickerung:
    if dp_row['dp_surface_sp_id'] && dp_row['dp_surface_sp_id'] == 1
      if dp_row['dp_gwbody_id'] && dp_row['groundwater_body_eu_key']
        fill_node(dp_node, dp_row, 'groundwater_body_eu_key', 'dd823:dcpGroundWater')
      else
        if active then
          puts "FEHLER: Land #{dp_row['federal_state_id']}, dp #{dp_row['dp_name']} (#{active_text}, #{dp_row['dp_key']}) hat keine dp_gwbody_id oder referenziert keinen derzeit gültigen Grundwasserkörper, obwohl die Einleitung in Land erfolgt (Versickerung)"
        end
      end
    end

    # dcpReceivingWater : rw_wise_key fehlt

    if dp_row['wrrl_sub_unit_description']
      wrrl_sub_unit_key = /^[0-9_]+/u.match(dp_row['wrrl_sub_unit_description'])
      poke(dp_node, wrrl_sub_unit_key, 'dd823:dcpWFDSubUnit')
    end

    fill_node(dp_node, dp_row, 'river_basin_district_eu_key', 'dd823:dcpWFDRBD')
    fill_node(dp_node, dp_row, 'dp_remark', 'dd823:dcpRemarks')
  end
  

  def create_discharge_points
    @bar.text="discharge points..."
    dps = XML::Node.new('DischargePoints')
    stmt = "select dp.*, p.pl_eu_key, lrc.key as nuts, lus.key as surface_land_key,
        lwb.eu_key as water_body_eu_key, lgwb.eu_key as groundwater_body_eu_key,
        lsu.description as wrrl_sub_unit_description, lrbd.river_basin_district_eu_key
      from discharge_points dp
        left join plants p
          on dp.plant_id = p.id
        left join l_region_code lrc
          on dp.dp_nuts_id = lrc.id
          and lrc.state = 0
        left join l_usage lus
          on dp.dp_surface_land_id = lus.id
          and lus.state = 0
        left join l_water_body_code lwb
          on dp.dp_waterbody_id = lwb.id
        left join l_groundwater_body lgwb
          on dp.dp_gwbody_id = lgwb.id
        left join l_wrrl_sub_units lsu
          on dp.dp_su_id = lsu.id
        left join l_river_basin_districts lrbd
          on dp.rb_rbd_id = lrbd.id
      where dp.state = 0
        and dp.date_from = #{DATE_FROM}
        and dp.federal_state_id < 17
      order by dp.federal_state_id, dp.dp_eu_key"
    print "SQL-Statement läuft ... #{Stnd.now}\n"
    sth = dbh.execute(stmt)
    pulse
    print "SQL-Statement fertig    #{Stnd.now}\n"
    counter = 0
    sth.fetch do |row|
      counter += 1
      dps << dp = XML::Node.new('DischargePoint')
      fill_dp(dp, row)
      if counter % 100 == 0
        pulse
        print "."
        STDOUT.flush
      end
      break if $testrun && counter >= 25
    end
    print "\n"
    puts "\n#{counter} Discharge Points geschrieben\n"
    return dps
  end


  def create_member_state_info
    ms_node = XML::Node.new('MSLevel')
    ms_node << rep_code = XML::Node.new('dd826:repCode')
    rep_code << REP_CODE
    stmt = "select sum(fs.s_prod) as sum_s_prod
      from federal_state_info fs
      where fs.state = 0
        and fs.date_from = #{DATE_FROM}"
    federal_row = @dbh.select_one(stmt)
    sum_s_prod = federal_row['sum_s_prod'].to_i
    poke(ms_node, sum_s_prod, 'dd826:mslSludgeProduction')
    poke(ms_node, '0', 'dd826:mslWWReusePerc')
    puts "\nMember State Info geschrieben\n"
    pulse
    return ms_node
  end
  

end
