# -*- coding: utf-8 -*-
# 
# application_controller.rb
# Teil des e-PRTR XML-3 Exports
# 
# (c) 2012, 2013, 2014 Matthias Lüttgert, ENDA GmbH & Co KG
# (c) 2014 Marcus Fritsche, ENDA GmbH & Co. KG
#
# This program is free software; you can redistribute it and/or modify it under
# the terms of the GNU General Public License as published by the Free Software
# Foundation; either version 3 of the License, or (at your option) any later
# version.
#
# This program is distributed in the hope that it will be useful, but WITHOUT ANY
# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A
# PARTICULAR PURPOSE. See the GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License along with
# this program; if not, see <http://www.gnu.org/licenses/>.
#
# DEUTSCH:
#
# Dieses Programm ist freie Software. Sie können es unter den Bedingungen der GNU
# General Public License, wie von der Free Software Foundation veröffentlicht,
# weitergeben und/oder modifizieren, entweder gemäß Version 3 der Lizenz oder
# (nach Ihrer Option) jeder späteren Version.
#
# Die Veröffentlichung dieses Programms erfolgt in der Hoffnung, daß es Ihnen von
# Nutzen sein wird, aber OHNE IRGENDEINE GARANTIE, sogar ohne die implizite
# Garantie der MARKTREIFE oder der VERWENDBARKEIT FÜR EINEN BESTIMMTEN
# ZWECK. Details finden Sie in der GNU General Public License.
#
# Sie sollten ein Exemplar der GNU General Public License zusammen mit diesem
# Programm erhalten haben. Falls nicht, siehe <http://www.gnu.org/licenses/>.
#
#
# Purpose
# Not documented
# 
# Changes
# 2012-04-24 mlt: Möglichkeit geschaffen, zu unterdrückende Betriebe durchzulassen



require_relative "db_controller.rb"
require_relative 'entry_en.rb'
require_relative 'betrieb_en.rb'
require_relative 'taet_en.rb'
require_relative 'freis_en.rb'
require_relative 'abw_en.rb'
require_relative 'abf_en.rb'


class ApplicationController
  def run
    begin
      puts "Starte #{Time.now}"
      puts "Öffne db_source:"
      $db_source = DbController.new('config/db_source.yml')
      puts "db_source geöffnet"
      $db_target = DbController.new('config/db_target_en.yml')
      puts "db_target_en geöffnet"
      
      cp_betriebe
      cp_taet
      cp_freis
      cp_abw
      cp_abf

      puts "Schreibe Release-Informationen"
      time = Time.now
      public_rel = $db_target.query_one("SELECT max(revision) from revisions") || 0
      db_rel = $db_source.query_one("SELECT max(release) from releases")
      $db_target.execute("INSERT INTO revisions (revision, db_release, generator_version,release_date, release_time)
    VALUES (#{public_rel.to_i+1},'#{db_rel}','#{APPVERSION}','#{time.strftime("%Y-%m-%d")}','#{time.strftime("%H:%M:%S")}')")

      puts "Fertig um #{Time.now}"
    rescue SystemExit, Interrupt => x
      puts "Abgebrochen vom Nutzer um #{Time.now}"
      raise x
    rescue Exception => rumms
      puts "Unexpected Exc <#{rumms}>"
      print rumms.backtrace.join("\n")
      puts "Abgebrochen um #{Time.now}"
    end
  ensure
    $db_source.close_connection
    $db_target.close_connection
  end

  # Betriebe
  def cp_betriebe
    puts "1/5: handle facilities (Betriebe)"
      $db_target.execute('DROP INDEX IF EXISTS facilities_id_pk')
      $db_target.execute('DROP INDEX IF EXISTS facilities_predecessor_id_pk')
      $db_target.execute('DROP INDEX IF EXISTS facilities_pdcc_id_pk')
      $db_target.execute('DELETE FROM facilities')
      $db_target.execute('DELETE FROM facilities_pdcc')
      copy(%Q^
    SELECT b.id,b.ido,
      coalesce (b.name1,'')||' '||coalesce (b.name2,'') as name,
      b.kennnr,
      b.jahr::integer,
      st_x(b.geo_wgs84) as geo_x,
      st_y(b.geo_wgs84) as geo_y,
      b.strasse as street,
      b.nr as house_number,
      b.plzstr as postcode,
      b.ort as city,
      fs.ltext_en as buland,
      feg.ltext_en as flusseinzugsgebiet,
      coalesce (b.bname1,'')||' '||coalesce (b.bname2,'') as muttergesellschaft,
      b.eigname,
      b.betname,
      b.prodvol,
      b.anlzahl,
      b.betriebh,
      b.prtrinfo,
      n.schl nace_key,
      n.ktext_en nace_text,
      v.p_betrieb_vorgaenger_id
    FROM p_betrieb b
    LEFT JOIN l_bundeslaender fs ON fs.id = b.land_id
    LEFT JOIN public.l_flusseinzugsgebiete feg ON feg.id = b.flezgeb_id
    LEFT JOIN public.l_nace_wirtschaftszweige n on n.id = b.nace_id
    LEFT JOIN public.p_betrieb_vorgaenger v on v.p_betrieb_id = b.id
    ORDER BY jahr ^) do |row|
        Betrieb.new(row,'p_betrieb')
      end
      $db_target.execute('CREATE UNIQUE INDEX facilities_id_pk ON facilities(id)')
      $db_target.execute('CREATE INDEX facilities_predecessor_id_pk ON facilities (predecessor_id)')
      $db_target.execute('CREATE INDEX facilities_pdcc_id_pk ON facilities_pdcc (facility_id)')
    end


  ### Tätigkeiten
  def cp_taet
    puts "2/5: handle activities (Tätigkeiten)"
      $db_target.execute('DROP INDEX IF EXISTS activities_id_pk')
      $db_target.execute('DROP INDEX IF EXISTS activities_pdcc_id_pk')
      $db_target.execute('DROP INDEX IF EXISTS activities_facility_id_pk')
    $db_target.execute('DELETE FROM activities')
    $db_target.execute('DELETE FROM activities_pdcc')
    copy("SELECT t.id,p_betrieb_id, jahr::integer, t.htprtr, prtr.schl as prtr_schl, prtr.ktext_en as prtr_text, ivu.schl as ivu_schl, ivu.ktext_en as ivu_text, bg.name_en as name
      FROM p_taet t
      LEFT JOIN l_taetigkeiten_anhang1_ivu ivu ON ivu.id = t.nrivu_id
      LEFT JOIN l_taetigkeiten_anhang1_eprtr prtr ON prtr.id = t.nrprtr_id
      LEFT JOIN l_prtr_branchengruppen bg ON bg.id = prtr.prtr_branchengruppe_id
      ORDER BY t.htprtr DESC") do |row|
      Taet.new(row,'p_taet')
    end
    $db_target.execute('CREATE UNIQUE INDEX activities_id_pk ON activities (id)')
    $db_target.execute('CREATE INDEX activities_pdcc_id_pk ON activities_pdcc (activity_id)')
    $db_target.execute('CREATE INDEX activities_facility_id_pk ON activities (facility_id)')
  end

  ### Freisetzungen
  def cp_freis
    puts "3/5: handle releases (Freisetzungen)"
    $db_target.execute('DROP INDEX IF EXISTS releases_id_pk')
    $db_target.execute('DROP INDEX IF EXISTS releases_pdcc_id_pk')
    $db_target.execute('DROP INDEX IF EXISTS releases_facility_id_pk')
    $db_target.execute('DELETE FROM releases')
    $db_target.execute('DELETE FROM releases_pdcc')
    copy("select f.id,p_betrieb_id, jahr::integer, kpt.ktext_en as kompartiment, f.jfracht , f.vfracht, sg.ktext_en as stoffgruppe, s.ktext_en as stoffname, s.cas_nr, s.schwellenwert_luft, s.schwellenwert_boden, s.schwellenwert_wasser, bm.ltext_en as bmeth, bv.schl_en as bverf, f.stoffnr_id, f.nbiofracht
from public.p_freis f
left join public.l_kompartimente kpt on f.medium_id = kpt.id
left join public.l_bestimmungsmethoden_ermittlungsarten bm on f.bestim_id = bm.id
left join public.l_bestimmungsverfahren bv on f.bvcode_id = bv.id
left join public.l_stoffe s on f.stoffnr_id=s.id
left join public.l_schadstoffgruppen sg on f.sgruppe_id = sg.id
order by f.medium_id, f.sgruppe_id, f.stoffnr_id") do |row|
      if row['jahr'].to_i < 2009 && row['stoffnr_id'].to_i==3
        row['nbiofracht'] = nil
      end
      Freis.new(row,'p_freis')
    end
    $db_target.execute('CREATE UNIQUE INDEX releases_id_pk ON releases (id)')
    $db_target.execute('CREATE INDEX releases_pdcc_id_pk ON releases_pdcc (release_id)')
    $db_target.execute('CREATE INDEX releases_facility_id_pk ON releases (facility_id)')
  end

  ### Verbringungen mit dem Abwasser
  def cp_abw
    puts "4/5: handle wastewater_transfers (Abwasser)"
      $db_target.execute('DROP INDEX IF EXISTS wastewater_transfers_id_pk')
      $db_target.execute('DROP INDEX IF EXISTS wastewater_transfers_pdcc_id_pk')
      $db_target.execute('DROP INDEX IF EXISTS wastewater_transfers_facility_id_pk')
    $db_target.execute('DELETE FROM wastewater_transfers')
    $db_target.execute('DELETE FROM wastewater_transfers_pdcc')                                                                                                                               #bv.ktext_en as bverf
    copy("select f.id,p_betrieb_id, jahr::integer, f.jfracht, sg.ktext_en as stoffgruppe, s.ktext_en as stoffname, s.cas_nr, s.schwellenwert_wasser as schwellenwert,  bm.ltext_en as bmeth, bv.schl_en as bverf
from public.p_vabw f
left join public.l_bestimmungsmethoden_ermittlungsarten bm on f.bestim_id = bm.id
left join public.l_bestimmungsverfahren bv on f.bvcode_id = bv.id
left join public.l_stoffe s on f.stoffnr_id=s.id
left join public.l_schadstoffgruppen sg on f.sgruppe_id = sg.id
order by f.sgruppe_id, f.stoffnr_id"){|row| Abw.new(row,'p_vabw')}
    $db_target.execute('CREATE UNIQUE INDEX wastewater_transfers_id_pk ON wastewater_transfers (id)')
    $db_target.execute('CREATE INDEX wastewater_transfers_pdcc_id_pk ON wastewater_transfers_pdcc (wastewater_transfer_id)')
    $db_target.execute('CREATE INDEX wastewater_transfers_facility_id_pk ON wastewater_transfers (facility_id)')
  end

  ### Verbringungen mit dem Abfall
  def cp_abf
    puts "5/5: handle waste_transfers (Abfall)"
    $db_target.execute('DROP INDEX IF EXISTS waste_transfers_id_pk')
    $db_target.execute('DROP INDEX IF EXISTS waste_transfers_pdcc_id_pk')
    $db_target.execute('DROP INDEX IF EXISTS waste_transfers_facility_id_pk')
    $db_target.execute('DELETE FROM waste_transfers')
    $db_target.execute('DELETE FROM waste_transfers_pdcc')
    #FIXME: Der Term für verwerter und verwertungsstandort könnte auch null werden, obwohl einige der Daten vorhanden sind.
    # Wurde aber mit aktuellem Stand getestet
    # Getestet werden kann dies mit:
#select f.vbname,f.vbstr,f.vbstrnr,f.vbplz,f.vbort,f.vbsstr,f.vbsstrnr,f.vbsplz,f.vbsort,staat.ktext,*
#from public.p_vabf f
#left join public.l_staaten staat on coalesce(f.vbsstaat_id,f.astaat_id) = staat.id
#
#where ((f.vbsstr is null OR f.vbsstrnr is null OR f.vbsplz is null OR f.vbsort is null OR staat.ktext is null) AND
#(f.vbsstr is not null OR f.vbsstrnr is not null OR f.vbsplz is not null OR f.vbsort is not null OR staat.ktext is not null))
#OR ((f.vbname is null OR f.vbstr is null OR f.vbstrnr is null OR f.vbplz is null OR f.vbort is null) AND
#(f.vbstr is not null OR f.vbstrnr is not null OR f.vbplz is not null OR f.vbort is not null))

    copy("select f.id,p_betrieb_id, jahr::integer, f.menge, f.abfart_id, f.aland_id, dis.ktext_en as disposition, bm.ltext_en as bmeth, bv.schl_en as bverf,
    f.vbname, f.vbstr, f.vbstrnr, f.vbplz, f.vbort, vbstaat.ktext_en as vbstaat,
    f.vbsstr, f.vbsstrnr, f.vbsplz, f.vbsort, vbsstaat.ktext_en as vbsstaat
from public.p_vabf f
left join public.l_bestimmungsmethoden_ermittlungsarten bm on f.bestim_id = bm.id
left join public.l_bestimmungsverfahren_abfall bv on f.bvcode_id = bv.id
left join public.l_staaten vbstaat on f.vbstaat_id = vbstaat.id
left join public.l_staaten vbsstaat on coalesce(f.vbsstaat_id,f.astaat_id) = vbsstaat.id
left join public.l_disposition_abfall dis on f.verbes_id = dis.id
order by abfart_id, aland_id, verbes_id") {|row| Abf.new(row,'p_vabf')}
    $db_target.execute('CREATE UNIQUE INDEX waste_transfers_id_pk ON waste_transfers (id)')
    $db_target.execute('CREATE INDEX waste_transfers_pdcc_id_pk ON waste_transfers_pdcc (waste_transfer_id)')
    $db_target.execute('CREATE INDEX waste_transfers_facility_id_pk ON waste_transfers (facility_id)')
  end

  def copy(sql)
    rs = $db_source.query(sql)

    rs.each do |row|
      begin
#        puts row.to_h
        obj = yield row
        obj.write_out
      rescue SystemExit, Interrupt => x
        raise x
      rescue Exception => rumms
        warn "Fehler bei Eintrag #{row.to_h}"
        warn rumms.message
        warn rumms.backtrace.join("\n")
      end
    end
    ensure
      rs.finish
  end

#  def self.create_table(name, columns)
#    sql = "DROP TABLE IF EXISTS #{name};
#CREATE TABLE #{name} (
#\t#{columns.join(",\n\t")}
#);
#DROP TABLE IF EXISTS #{name}_pdcc;
#CREATE TABLE #{name} (
#    "
#    puts sql
#  end
end

