-- Release vom 2016-08-05
-- 2016-08-05, mlt: Leerzeichen-de-duplizierung auf den Suchtabellen (a.k.a. Beschleunigertabellen)


insert into betriebe_gis_#{jahr}
select
  b.ido,
  b.id,
  b.kennnr,
  trim(both ' ' from regexp_replace(coalesce (b.name1,'')||' '|| coalesce(b.name2,''), '[ ]+', ' ', 'g')) as "betriebseinrichtung",
  b.strasse,
  b.nr,
  b.plzstr,
  b.ort,
  l_bundeslaender.ltext,
  l_taetigkeiten_anhang1_eprtr.schl,
  l_taetigkeiten_anhang1_eprtr.ktext,
  l_taetigkeiten_anhang1_eprtr.ktext_en,
  l_taetigkeiten_anhang1_eprtr.prtr_branchengruppe_id,
  b.geo_etrs89_utm32,
  b.geo_wgs84
from p_betrieb b
  join l_bundeslaender on b.land_id = l_bundeslaender.id
  left join p_taet
    on b.id = p_taet.p_betrieb_id
    and p_taet.htprtr = 1
  left join l_taetigkeiten_anhang1_eprtr
    on p_taet.nrprtr_id = l_taetigkeiten_anhang1_eprtr.id
  left join betriebe_gis_#{jahr} bgis
    on bgis.p_betrieb_id = b.id
where b.date_from = (#{jahr}*10000+ 101)
  and b.date_to   = (#{jahr}*10000+1231)
  and b.state = 0
  and bgis.p_betrieb_id is null
  and (b.name1 is not null or b.name2 is not null);


insert into betriebe_#{jahr}
select
  b.ido,
  b.id,
  b.kennnr,
  trim(both ' ' from regexp_replace(coalesce (b.name1,'')||' '|| coalesce(b.name2,''), '[ ]+', ' ', 'g')) as "betriebseinrichtung",
  -- coalesce (b.name1,'')||' '|| coalesce(b.name2,'') as "betriebseinrichtung",
  b.strasse,
  b.nr,
  b.plzstr,
  b.ort,
  l_bundeslaender.ltext,
  b.geo_etrs89_utm32
from p_betrieb b
  join l_bundeslaender on b.land_id = l_bundeslaender.id
  left join betriebe_#{jahr} bb
    on bb.p_betrieb_id = b.id
where 
      b.date_from <= (#{jahr}*10000+101)
  and b.date_to   >= (#{jahr}*10000+1231)
  and b.state = 0
  and bb.p_betrieb_id is null
  and (b.name1 is not null or b.name2 is not null);


-- ACHTUNG: Aufgrund des kreuzproduktartigen Inhalts der Tabelle "suchtabelle_<jahr>"
--   können Doppeleinträge NICHT VERHINDERT WERDEN, wenn das Statement mehrfach
--   abläuft!
-- Muss eine Reparatur vorgenommen werden, so sind die Lösch-Statements "GIS-Easy-Tabelle"
--   aus der 21_drop_gis_easy_daten.sql auszuführen und dann die neue Befüllung vorzunehmen.
-- 
-- select I für create betrieb_query Tabelle für schnelle Abfragen
-- Hier: p_betrieb,

insert into suchtabelle_#{jahr}
select
  b.ido                                                 as "betrieb_ido",
  trim(both ' ' from regexp_replace(lower(coalesce (b.name1,'')||' '|| coalesce(b.name2,'')), '[ ]+', ' ', 'g'))
                                                        as "betriebseinrichtung",
  trim(both ' ' from regexp_replace(lower(coalesce (b.bname1,'')||' '|| coalesce(b.bname2,'')), '[ ]+', ' ', 'g'))
                                                        as "muttergesellschaft",
  trim(both ' ' from regexp_replace(lower(coalesce (b.eigname,'')||' '|| coalesce(b.betname,'')), '[ ]+', ' ', 'g'))
                                                        as "eigentuemer_betreiber",
  b.plzstr                                              as "plz",
  trim(both '' from lower(coalesce (b.ort,'') || ' ' || coalesce(l_gemeinden.ktext, '')))
                                                        as "gemeinde",
  b.land_id                                             as "bundesland_schluessel",
  l_bundeslaender.ltext                                 as "bundesland",
  l_flusseinzugsgebiete.schl                            as "flusseinzugsgebiet_schluessel",
  l_flusseinzugsgebiete.ktext                           as "flusseinzugsgebiet",

-- p_taet
  p_taet.htprtr                                         as "haupttaetigkeit_schluessel",
  int2bool(p_taet.htprtr)                               as "haupttaetigkeit",
  l_taetigkeiten_anhang1_eprtr.prtr_branchengruppe_id   as "branchengruppe",
  l_taetigkeiten_anhang1_eprtr.schl                     as "taetigkeit_anhang1_schluessel",
  l_taetigkeiten_anhang1_eprtr.ktext                    as "taetigkeit_anhang1",
  b.nace_id                                             as "nace_schluessel",
  l_nace_wirtschaftszweige.ktext                        as "nace",

-- p_freis
  l_kompartimente.schl                                  as "kompartiment_schluessel",
  l_kompartimente.ktext                                 as "kompartiment",
  l_schadstoffgruppen.schl                              as "schadstoffgruppe_schluessel",
  l_schadstoffgruppen.ktext                             as "schadstoffgruppe",
  l_stoffe.schl                                         as "stoff_schluessel",
  risa_collect_synonyme(l_stoffe.prtr_anhang2_nr, ' ')  as "stoff",
  l_bestimmungsmethoden_ermittlungsarten.ktext          as "bestimmungsmethode",

-- p_vabw (Verbringung Abwasser): Lassen wir hier. Union! Teil II siehe unten.

-- p_vabf (Verbringung Abfall)
  l_disposition_abfall.schl                             as "beseitigung_verwertung_schluessel",
  l_disposition_abfall.ktext                            as "beseitigung_verwertung",
  l_abfalltypen.schl                                    as "gefaehrlich_schluessel",
  l_abfalltypen.ktext                                   as "gefaehrlich",
  l_ia.schl                                             as "inland_ausland_schluessel",
  l_ia.ktext                                            as "inland_ausland",
  p_vabf.vbname                                         as "vbname",
  p_vabf.vbort                                          as "vbort",
  vbstaaten.schl                                        as "vbstaat_schluessel",
  p_vabf.vbsort                                         as "vbsort",
  vbsstaaten.schl                                       as "vbsstaat_schluessel",
  l_bestimmungsmethoden_ermittlungsarten_abfall.ktext   as "bestimmungsmethode_abfall"

from p_betrieb b
  join l_bundeslaender
    on b.land_id = l_bundeslaender.id
  left join l_gemeinden
    on b.gemde_id = l_gemeinden.id
  left join l_flusseinzugsgebiete
    on b.flezgeb_id = l_flusseinzugsgebiete.id
  left join l_nace_wirtschaftszweige
    on b.nace_id = l_nace_wirtschaftszweige.id
  left join l_schwellenwertueberschreitungsarten luft_swuebart
    on b.freisl_id = luft_swuebart.id
  left join l_schwellenwertueberschreitungsarten boden_swuebart
    on b.freisb_id = boden_swuebart.id
  left join l_schwellenwertueberschreitungsarten wasser_swuebart
    on b.freisw_id = wasser_swuebart.id
  left join l_schwellenwertueberschreitungsarten verbw_swuebart
    on b.verbw_id = verbw_swuebart.id
  left join l_schwellenwertueberschreitungsarten verba_swuebart
    on b.verba_id = verba_swuebart.id
  left join l_schwellenwertueberschreitungsarten verbabfgef_swuebart
    on b.verbang_id = verbabfgef_swuebart.id
  left join l_bearbeitungsstatus
    on b.bearb_id = l_bearbeitungsstatus.id
  left join l_pruefstati_geheimhaltung
    on b.pruefg_id = l_pruefstati_geheimhaltung.id
  left join l_pruefstati l_pruefstati_luft
    on b.pruefl_id = l_pruefstati_luft.id
  left join l_pruefstati l_pruefstati_boden
    on b.pruefb_id = l_pruefstati_boden.id
  left join l_pruefstati l_pruefstati_wasser
    on b.pruefw_id = l_pruefstati_wasser.id
  left join l_pruefstati l_pruefstati_abfall
    on b.pruefa_id = l_pruefstati_abfall.id

-- 1:n
  left join p_taet
    on b.id = p_taet.p_betrieb_id
  left join l_taetigkeiten_anhang1_eprtr
    on p_taet.nrprtr_id = l_taetigkeiten_anhang1_eprtr.id

-- 1:n
  left join p_freis
    on b.id = p_freis.p_betrieb_id
  left join l_kompartimente
    on p_freis.medium_id = l_kompartimente.id
  left join l_bestimmungsmethoden_ermittlungsarten
    on p_freis.bestim_id = l_bestimmungsmethoden_ermittlungsarten.id
  left join l_stoffe
    on p_freis.stoffnr_id = l_stoffe.id
  left join l_schadstoffgruppen
    on l_stoffe.sgruppe_id = l_schadstoffgruppen.id

-- 1:n
--  left join p_vabw
--    on b.id = p_vabw.p_betrieb_id

-- 1:n
  left join p_vabf
    on b.id = p_vabf.p_betrieb_id
  left join l_disposition_abfall
    on p_vabf.verbes_id = l_disposition_abfall.id
  left join l_abfalltypen
    on p_vabf.abfart_id = l_abfalltypen.id
  left join l_ia
    on p_vabf.aland_id = l_ia.id
  left join l_staaten vbstaaten
    on coalesce(p_vabf.vbstaat_id,p_vabf.astaat_id) = vbstaaten.id
  left join l_staaten vbsstaaten
    on coalesce(p_vabf.vbsstaat_id,p_vabf.astaat_id) = vbsstaaten.id
  left join l_bestimmungsmethoden_ermittlungsarten l_bestimmungsmethoden_ermittlungsarten_abfall
    on p_vabf.bestim_id = l_bestimmungsmethoden_ermittlungsarten_abfall.id

where 
      b.date_from <= (#{jahr}*10000+101)
  and b.date_to   >= (#{jahr}*10000+1231)
  and b.state = 0
  and (b.name1 is not null or b.name2 is not null)
;


-- ACHTUNG: Aufgrund des kreuzproduktartigen Inhalts der Tabelle "suchtabelle_<jahr>"
--   können Doppeleinträge NICHT VERHINDERT WERDEN, wenn das Statement mehrfach
--   abläuft!
-- Muss eine Reparatur vorgenommen werden, so sind die Lösch-Statements "GIS-Easy-Tabelle"
--   aus der 21_drop_gis_easy_daten.sql auszuführen und dann die neue Befüllung vorzunehmen.
-- 
-- select II für create betrieb_query Tabelle für schnelle Abfragen
-- Hier: p_betrieb, p_taet, p_vabw, p_vabf

insert into suchtabelle_#{jahr}
select
  b.ido                                                 as "betrieb_ido",
  trim(both ' ' from regexp_replace(lower(coalesce (b.name1,'')||' '|| coalesce(b.name2,'')), '[ ]+', ' ', 'g'))
                                                        as "betriebseinrichtung",
  trim(both ' ' from regexp_replace(lower(coalesce (b.bname1,'')||' '|| coalesce(b.bname2,'')), '[ ]+', ' ', 'g'))
                                                        as "muttergesellschaft",
  trim(both ' ' from regexp_replace(lower(coalesce (b.eigname,'')||' '|| coalesce(b.betname,'')), '[ ]+', ' ', 'g'))
                                                        as "eigentuemer_betreiber",
  b.plzstr                                              as "plz",
  trim(both '' from lower(coalesce (b.ort,'') || ' ' || coalesce(l_gemeinden.ktext, '')))
                                                        as "gemeinde",
  b.land_id                                             as "bundesland_schluessel",
  l_bundeslaender.ltext                                 as "bundesland",
  l_flusseinzugsgebiete.schl                            as "flusseinzugsgebiet_schluessel",
  l_flusseinzugsgebiete.ktext                           as "flusseinzugsgebiet",

-- p_taet
  p_taet.htprtr                                         as "haupttaetigkeit_schluessel",
  int2bool(p_taet.htprtr)                               as "haupttaetigkeit",
  l_taetigkeiten_anhang1_eprtr.prtr_branchengruppe_id   as "branchengruppe",
  l_taetigkeiten_anhang1_eprtr.schl                     as "taetigkeit_anhang1_schluessel",
  l_taetigkeiten_anhang1_eprtr.ktext                    as "taetigkeit_anhang1",
  b.nace_id                                             as "nace_schluessel",
  l_nace_wirtschaftszweige.ktext                        as "nace",

-- p_freis: Lassen wir hier. Freisetzungen siehe Teil I.
--  l_kompartimente.schl                                  as "kompartiment_schluessel",
--  l_kompartimente.ktext                                 as "kompartiment",
--  l_schadstoffgruppen.schl as "schadstoffgruppe_schluessel",
--  l_schadstoffgruppen.ktext as "schadstoffgruppe",
--  l_stoffe.schl                                         as "stoff_schluessel",
--  l_stoffe.ktext                                        as "stoff",

-- p_vabw (Verbringung Abwasser)
  'AW'                                                  as "kompartiment_schluessel",
  'Abwasser'                                            as "kompartiment",
  l_schadstoffgruppen.schl                              as "schadstoffgruppe_schluessel",
  l_schadstoffgruppen.ktext                             as "schadstoffgruppe",
  l_stoffe.schl                                         as "stoff_schluessel",
  risa_collect_synonyme(l_stoffe.prtr_anhang2_nr, ' ')  as "stoff",
  l_bestimmungsmethoden_ermittlungsarten.ktext          as "bestimmungsmethode",

-- p_vabf (Verbringung Abfall)
  l_disposition_abfall.schl                             as "beseitigung_verwertung_schluessel",
  l_disposition_abfall.ktext                            as "beseitigung_verwertung",
  l_abfalltypen.schl                                    as "gefaehrlich_schluessel",
  l_abfalltypen.ktext                                   as "gefaehrlich",
  l_ia.schl                                             as "inland_ausland_schluessel",
  l_ia.ktext                                            as "inland_ausland",
  p_vabf.vbname                                         as "vbname",
  p_vabf.vbort                                          as "vbort",
  vbstaaten.schl                                        as "vbstaat_schluessel",
  p_vabf.vbsort                                         as "vbsort",
  vbsstaaten.schl                                       as "vbsstaat_schluessel",
  l_bestimmungsmethoden_ermittlungsarten_abfall.ktext   as "bestimmungsmethode_abfall"

from p_betrieb b
  join l_bundeslaender
    on b.land_id = l_bundeslaender.id
  left join l_gemeinden
    on b.gemde_id = l_gemeinden.id
  left join l_flusseinzugsgebiete
    on b.flezgeb_id = l_flusseinzugsgebiete.id
  left join l_nace_wirtschaftszweige
    on b.nace_id = l_nace_wirtschaftszweige.id
  left join l_schwellenwertueberschreitungsarten luft_swuebart
    on b.freisl_id = luft_swuebart.id
  left join l_schwellenwertueberschreitungsarten boden_swuebart
    on b.freisb_id = boden_swuebart.id
  left join l_schwellenwertueberschreitungsarten wasser_swuebart
    on b.freisw_id = wasser_swuebart.id
  left join l_schwellenwertueberschreitungsarten verbw_swuebart
    on b.verbw_id = verbw_swuebart.id
  left join l_schwellenwertueberschreitungsarten verba_swuebart
    on b.verba_id = verba_swuebart.id
  left join l_schwellenwertueberschreitungsarten verbabfgef_swuebart
    on b.verbang_id = verbabfgef_swuebart.id
  left join l_bearbeitungsstatus
    on b.bearb_id = l_bearbeitungsstatus.id
  left join l_pruefstati_geheimhaltung
    on b.pruefg_id = l_pruefstati_geheimhaltung.id
  left join l_pruefstati l_pruefstati_luft
    on b.pruefl_id = l_pruefstati_luft.id
  left join l_pruefstati l_pruefstati_boden
    on b.pruefb_id = l_pruefstati_boden.id
  left join l_pruefstati l_pruefstati_wasser
    on b.pruefw_id = l_pruefstati_wasser.id
  left join l_pruefstati l_pruefstati_abfall
    on b.pruefa_id = l_pruefstati_abfall.id

-- 1:n
  left join p_taet
    on b.id = p_taet.p_betrieb_id
  left join l_taetigkeiten_anhang1_eprtr
    on p_taet.nrprtr_id = l_taetigkeiten_anhang1_eprtr.id

-- 1:n
--  left join p_freis
--    on b.id = p_freis.p_betrieb_id
--  left join l_kompartimente
--    on p_freis.medium_id = l_kompartimente.id
--  left join l_stoffe
--    on p_freis.stoffnr_id = l_stoffe.id
--  left join l_schadstoffgruppen
--    on l_stoffe.sgruppe_id = l_schadstoffgruppen.id

-- 1:n
  left join p_vabw
    on b.id = p_vabw.p_betrieb_id
  left join l_bestimmungsmethoden_ermittlungsarten
    on p_vabw.bestim_id = l_bestimmungsmethoden_ermittlungsarten.id
  left join l_stoffe
    on p_vabw.stoffnr_id = l_stoffe.id
  left join l_schadstoffgruppen
    on l_stoffe.sgruppe_id = l_schadstoffgruppen.id

-- 1:n
  left join p_vabf
    on b.id = p_vabf.p_betrieb_id
  left join l_disposition_abfall
    on p_vabf.verbes_id = l_disposition_abfall.id
  left join l_abfalltypen
    on p_vabf.abfart_id = l_abfalltypen.id
  left join l_ia
    on p_vabf.aland_id = l_ia.id  
  left join l_staaten vbstaaten
    on coalesce(p_vabf.vbstaat_id,p_vabf.astaat_id) = vbstaaten.id
  left join l_staaten vbsstaaten
    on coalesce(p_vabf.vbsstaat_id,p_vabf.astaat_id) = vbsstaaten.id
  left join l_bestimmungsmethoden_ermittlungsarten l_bestimmungsmethoden_ermittlungsarten_abfall
    on p_vabf.bestim_id = l_bestimmungsmethoden_ermittlungsarten_abfall.id

where 
      b.date_from <= (#{jahr}*10000+101)
  and b.date_to   >= (#{jahr}*10000+1231)
  and b.state = 0
  and (b.name1 is not null or b.name2 is not null)
  and l_schadstoffgruppen.schl is not null
;




--Und hier nochmal (fast) das Gleiche (wie für Freisetzung) um auch die nbiofracht Einträge zu erfassen
insert into suchtabelle_#{jahr}
select
  b.ido                                                 as "betrieb_ido",
  trim(both ' ' from regexp_replace(lower(coalesce (b.name1,'')||' '|| coalesce(b.name2,'')), '[ ]+', ' ', 'g'))
                                                        as "betriebseinrichtung",
  trim(both ' ' from regexp_replace(lower(coalesce (b.bname1,'')||' '|| coalesce(b.bname2,'')), '[ ]+', ' ', 'g'))
                                                        as "muttergesellschaft",
  trim(both ' ' from regexp_replace(lower(coalesce (b.eigname,'')||' '|| coalesce(b.betname,'')), '[ ]+', ' ', 'g'))
                                                        as "eigentuemer_betreiber",
  b.plzstr                                              as "plz",
  trim(both '' from lower(coalesce (b.ort,'') || ' ' || coalesce(l_gemeinden.ktext, '')))
                                                        as "gemeinde",
  b.land_id                                             as "bundesland_schluessel",
  l_bundeslaender.ltext                                 as "bundesland",
  l_flusseinzugsgebiete.schl                            as "flusseinzugsgebiet_schluessel",
  l_flusseinzugsgebiete.ktext                           as "flusseinzugsgebiet",

-- p_taet
  p_taet.htprtr                                         as "haupttaetigkeit_schluessel",
  int2bool(p_taet.htprtr)                               as "haupttaetigkeit",
  l_taetigkeiten_anhang1_eprtr.prtr_branchengruppe_id   as "branchengruppe",
  l_taetigkeiten_anhang1_eprtr.schl                     as "taetigkeit_anhang1_schluessel",
  l_taetigkeiten_anhang1_eprtr.ktext                    as "taetigkeit_anhang1",
  b.nace_id                                             as "nace_schluessel",
  l_nace_wirtschaftszweige.ktext                        as "nace",

-- p_freis
  l_kompartimente.schl                                  as "kompartiment_schluessel",
  l_kompartimente.ktext                                 as "kompartiment",
  l_schadstoffgruppen.schl                              as "schadstoffgruppe_schluessel",
  l_schadstoffgruppen.ktext                             as "schadstoffgruppe",
  l_stoffe.schl                                         as "stoff_schluessel",
  risa_collect_synonyme(l_stoffe.id, ' ')  as "stoff",
  l_bestimmungsmethoden_ermittlungsarten.ktext          as "bestimmungsmethode",

-- p_vabw (Verbringung Abwasser): Lassen wir hier. Union! Teil II siehe unten.

-- p_vabf (Verbringung Abfall)
  l_disposition_abfall.schl                             as "beseitigung_verwertung_schluessel",
  l_disposition_abfall.ktext                            as "beseitigung_verwertung",
  l_abfalltypen.schl                                    as "gefaehrlich_schluessel",
  l_abfalltypen.ktext                                   as "gefaehrlich",
  l_ia.schl                                             as "inland_ausland_schluessel",
  l_ia.ktext                                            as "inland_ausland",
  p_vabf.vbname                                         as "vbname",
  p_vabf.vbort                                          as "vbort",
  vbstaaten.schl                                        as "vbstaat_schluessel",
  p_vabf.vbsort                                         as "vbsort",
  vbsstaaten.schl                                       as "vbsstaat_schluessel",
  l_bestimmungsmethoden_ermittlungsarten_abfall.ktext   as "bestimmungsmethode_abfall"

from p_betrieb b
  join l_bundeslaender
    on b.land_id = l_bundeslaender.id
  left join l_gemeinden
    on b.gemde_id = l_gemeinden.id
  left join l_flusseinzugsgebiete
    on b.flezgeb_id = l_flusseinzugsgebiete.id
  left join l_nace_wirtschaftszweige
    on b.nace_id = l_nace_wirtschaftszweige.id
  left join l_schwellenwertueberschreitungsarten luft_swuebart
    on b.freisl_id = luft_swuebart.id
  left join l_schwellenwertueberschreitungsarten boden_swuebart
    on b.freisb_id = boden_swuebart.id
  left join l_schwellenwertueberschreitungsarten wasser_swuebart
    on b.freisw_id = wasser_swuebart.id
  left join l_schwellenwertueberschreitungsarten verbw_swuebart
    on b.verbw_id = verbw_swuebart.id
  left join l_schwellenwertueberschreitungsarten verba_swuebart
    on b.verba_id = verba_swuebart.id
  left join l_schwellenwertueberschreitungsarten verbabfgef_swuebart
    on b.verbang_id = verbabfgef_swuebart.id
  left join l_bearbeitungsstatus
    on b.bearb_id = l_bearbeitungsstatus.id
  left join l_pruefstati_geheimhaltung
    on b.pruefg_id = l_pruefstati_geheimhaltung.id
  left join l_pruefstati l_pruefstati_luft
    on b.pruefl_id = l_pruefstati_luft.id
  left join l_pruefstati l_pruefstati_boden
    on b.pruefb_id = l_pruefstati_boden.id
  left join l_pruefstati l_pruefstati_wasser
    on b.pruefw_id = l_pruefstati_wasser.id
  left join l_pruefstati l_pruefstati_abfall
    on b.pruefa_id = l_pruefstati_abfall.id

-- 1:n
  left join p_taet
    on b.id = p_taet.p_betrieb_id
  left join l_taetigkeiten_anhang1_eprtr
    on p_taet.nrprtr_id = l_taetigkeiten_anhang1_eprtr.id

-- 1:n
  inner join p_freis
    on b.id = p_freis.p_betrieb_id
    and p_freis.stoffnr_id = 3
    and p_freis.nbiofracht > 0
  left join l_kompartimente
    on p_freis.medium_id = l_kompartimente.id
  left join l_bestimmungsmethoden_ermittlungsarten
    on p_freis.bestim_id = l_bestimmungsmethoden_ermittlungsarten.id
  left join l_stoffe
    on 92 = l_stoffe.id
  left join l_schadstoffgruppen
    on l_stoffe.sgruppe_id = l_schadstoffgruppen.id

-- 1:n
--  left join p_vabw
--    on b.id = p_vabw.p_betrieb_id

-- 1:n
  left join p_vabf
    on b.id = p_vabf.p_betrieb_id
  left join l_disposition_abfall
    on p_vabf.verbes_id = l_disposition_abfall.id
  left join l_abfalltypen
    on p_vabf.abfart_id = l_abfalltypen.id
  left join l_ia
    on p_vabf.aland_id = l_ia.id
  left join l_staaten vbstaaten
    on coalesce(p_vabf.vbstaat_id,p_vabf.astaat_id) = vbstaaten.id
  left join l_staaten vbsstaaten
    on coalesce(p_vabf.vbsstaat_id,p_vabf.astaat_id) = vbsstaaten.id
  left join l_bestimmungsmethoden_ermittlungsarten l_bestimmungsmethoden_ermittlungsarten_abfall
    on p_vabf.bestim_id = l_bestimmungsmethoden_ermittlungsarten_abfall.id

where 
      b.date_from <= (#{jahr}*10000+101)
  and b.date_to   >= (#{jahr}*10000+1231)
  and b.state = 0
  and (b.name1 is not null or b.name2 is not null)
;


--
-- Und weil das alles so schön war, die letzten vierhundert Zeilen, also alle drei Fälle noch mal auf Englisch!
--


-- ACHTUNG: Aufgrund des kreuzproduktartigen Inhalts der Tabelle "suchtabelle_<jahr>"
--   können Doppeleinträge NICHT VERHINDERT WERDEN, wenn das Statement mehrfach
--   abläuft!
-- Muss eine Reparatur vorgenommen werden, so sind die Lösch-Statements "GIS-Easy-Tabelle"
--   aus der 21_drop_gis_easy_daten.sql auszuführen und dann die neue Befüllung vorzunehmen.
-- 
-- select I für create betrieb_query Tabelle für schnelle Abfragen
-- Hier: p_betrieb,

insert into suchtabelle_en_#{jahr}
select
  b.ido                                                 as "betrieb_ido",
  trim(both ' ' from regexp_replace(lower(coalesce (b.name1,'')||' '|| coalesce(b.name2,'')), '[ ]+', ' ', 'g'))
                                                        as "betriebseinrichtung",
  trim(both ' ' from regexp_replace(lower(coalesce (b.bname1,'')||' '|| coalesce(b.bname2,'')), '[ ]+', ' ', 'g'))
                                                        as "muttergesellschaft",
  trim(both ' ' from regexp_replace(lower(coalesce (b.eigname,'')||' '|| coalesce(b.betname,'')), '[ ]+', ' ', 'g'))
                                                        as "eigentuemer_betreiber",
  b.plzstr                                              as "plz",
  trim(both '' from lower(coalesce (b.ort,'') || ' ' || coalesce(l_gemeinden.ktext, '')))
                                                        as "gemeinde",
  b.land_id                                             as "bundesland_schluessel",
  l_bundeslaender.ltext_en                              as "bundesland",
  l_flusseinzugsgebiete.schl                            as "flusseinzugsgebiet_schluessel",
  l_flusseinzugsgebiete.ktext_en                        as "flusseinzugsgebiet",

-- p_taet
  p_taet.htprtr                                         as "haupttaetigkeit_schluessel",
  int2bool(p_taet.htprtr)                               as "haupttaetigkeit",
  l_taetigkeiten_anhang1_eprtr.prtr_branchengruppe_id   as "branchengruppe",
  l_taetigkeiten_anhang1_eprtr.schl                     as "taetigkeit_anhang1_schluessel",
  l_taetigkeiten_anhang1_eprtr.ktext_en                 as "taetigkeit_anhang1",
  b.nace_id                                             as "nace_schluessel",
  l_nace_wirtschaftszweige.ktext_en                     as "nace",

-- p_freis
  l_kompartimente.schl                                  as "kompartiment_schluessel",
  l_kompartimente.ktext_en                              as "kompartiment",
  l_schadstoffgruppen.schl                              as "schadstoffgruppe_schluessel",
  l_schadstoffgruppen.ktext_en                          as "schadstoffgruppe",
  l_stoffe.schl                                         as "stoff_schluessel",
  risa_collect_synonyme_en(l_stoffe.prtr_anhang2_nr, ' ')  as "stoff",
  l_bestimmungsmethoden_ermittlungsarten.ktext_en       as "bestimmungsmethode",

-- p_vabw (Verbringung Abwasser): Lassen wir hier. Union! Teil II siehe unten.

-- p_vabf (Verbringung Abfall)
  l_disposition_abfall.schl                             as "beseitigung_verwertung_schluessel",
  l_disposition_abfall.ktext_en                         as "beseitigung_verwertung",
  l_abfalltypen.schl                                    as "gefaehrlich_schluessel",
  l_abfalltypen.ktext_en                                as "gefaehrlich",
  l_ia.schl                                             as "inland_ausland_schluessel",
  l_ia.ktext_en                                         as "inland_ausland",
  p_vabf.vbname                                         as "vbname",
  p_vabf.vbort                                          as "vbort",
  vbstaaten.schl                                        as "vbstaat_schluessel",
  p_vabf.vbsort                                         as "vbsort",
  vbsstaaten.schl                                       as "vbsstaat_schluessel",
  l_bestimmungsmethoden_ermittlungsarten_abfall.ktext_en  as "bestimmungsmethode_abfall"

from p_betrieb b
  join l_bundeslaender
    on b.land_id = l_bundeslaender.id
  left join l_gemeinden
    on b.gemde_id = l_gemeinden.id
  left join l_flusseinzugsgebiete
    on b.flezgeb_id = l_flusseinzugsgebiete.id
  left join l_nace_wirtschaftszweige
    on b.nace_id = l_nace_wirtschaftszweige.id
  left join l_schwellenwertueberschreitungsarten luft_swuebart
    on b.freisl_id = luft_swuebart.id
  left join l_schwellenwertueberschreitungsarten boden_swuebart
    on b.freisb_id = boden_swuebart.id
  left join l_schwellenwertueberschreitungsarten wasser_swuebart
    on b.freisw_id = wasser_swuebart.id
  left join l_schwellenwertueberschreitungsarten verbw_swuebart
    on b.verbw_id = verbw_swuebart.id
  left join l_schwellenwertueberschreitungsarten verba_swuebart
    on b.verba_id = verba_swuebart.id
  left join l_schwellenwertueberschreitungsarten verbabfgef_swuebart
    on b.verbang_id = verbabfgef_swuebart.id
  left join l_bearbeitungsstatus
    on b.bearb_id = l_bearbeitungsstatus.id
  left join l_pruefstati_geheimhaltung
    on b.pruefg_id = l_pruefstati_geheimhaltung.id
  left join l_pruefstati l_pruefstati_luft
    on b.pruefl_id = l_pruefstati_luft.id
  left join l_pruefstati l_pruefstati_boden
    on b.pruefb_id = l_pruefstati_boden.id
  left join l_pruefstati l_pruefstati_wasser
    on b.pruefw_id = l_pruefstati_wasser.id
  left join l_pruefstati l_pruefstati_abfall
    on b.pruefa_id = l_pruefstati_abfall.id

-- 1:n
  left join p_taet
    on b.id = p_taet.p_betrieb_id
  left join l_taetigkeiten_anhang1_eprtr
    on p_taet.nrprtr_id = l_taetigkeiten_anhang1_eprtr.id

-- 1:n
  left join p_freis
    on b.id = p_freis.p_betrieb_id
  left join l_kompartimente
    on p_freis.medium_id = l_kompartimente.id
  left join l_bestimmungsmethoden_ermittlungsarten
    on p_freis.bestim_id = l_bestimmungsmethoden_ermittlungsarten.id
  left join l_stoffe
    on p_freis.stoffnr_id = l_stoffe.id
  left join l_schadstoffgruppen
    on l_stoffe.sgruppe_id = l_schadstoffgruppen.id

-- 1:n
--  left join p_vabw
--    on b.id = p_vabw.p_betrieb_id

-- 1:n
  left join p_vabf
    on b.id = p_vabf.p_betrieb_id
  left join l_disposition_abfall
    on p_vabf.verbes_id = l_disposition_abfall.id
  left join l_abfalltypen
    on p_vabf.abfart_id = l_abfalltypen.id
  left join l_ia
    on p_vabf.aland_id = l_ia.id
  left join l_staaten vbstaaten
    on coalesce(p_vabf.vbstaat_id,p_vabf.astaat_id) = vbstaaten.id
  left join l_staaten vbsstaaten
    on coalesce(p_vabf.vbsstaat_id,p_vabf.astaat_id) = vbsstaaten.id
  left join l_bestimmungsmethoden_ermittlungsarten l_bestimmungsmethoden_ermittlungsarten_abfall
    on p_vabf.bestim_id = l_bestimmungsmethoden_ermittlungsarten_abfall.id

where 
      b.date_from <= (#{jahr}*10000+101)
  and b.date_to   >= (#{jahr}*10000+1231)
  and b.state = 0
  and (b.name1 is not null or b.name2 is not null)
;


-- ACHTUNG: Aufgrund des kreuzproduktartigen Inhalts der Tabelle "suchtabelle_<jahr>"
--   können Doppeleinträge NICHT VERHINDERT WERDEN, wenn das Statement mehrfach
--   abläuft!
-- Muss eine Reparatur vorgenommen werden, so sind die Lösch-Statements "GIS-Easy-Tabelle"
--   aus der 21_drop_gis_easy_daten.sql auszuführen und dann die neue Befüllung vorzunehmen.
-- 
-- select II für create betrieb_query Tabelle für schnelle Abfragen
-- Hier: p_betrieb, p_taet, p_vabw, p_vabf

insert into suchtabelle_en_#{jahr}
select
  b.ido                                                 as "betrieb_ido",
  trim(both ' ' from regexp_replace(lower(coalesce (b.name1,'')||' '|| coalesce(b.name2,'')), '[ ]+', ' ', 'g'))
                                                        as "betriebseinrichtung",
  trim(both ' ' from regexp_replace(lower(coalesce (b.bname1,'')||' '|| coalesce(b.bname2,'')), '[ ]+', ' ', 'g'))
                                                        as "muttergesellschaft",
  trim(both ' ' from regexp_replace(lower(coalesce (b.eigname,'')||' '|| coalesce(b.betname,'')), '[ ]+', ' ', 'g'))
                                                        as "eigentuemer_betreiber",
  b.plzstr                                              as "plz",
  trim(both '' from lower(coalesce (b.ort,'') || ' ' || coalesce(l_gemeinden.ktext, '')))
                                                        as "gemeinde",
  b.land_id                                             as "bundesland_schluessel",
  l_bundeslaender.ltext_en                              as "bundesland",
  l_flusseinzugsgebiete.schl                            as "flusseinzugsgebiet_schluessel",
  l_flusseinzugsgebiete.ktext_en                        as "flusseinzugsgebiet",

-- p_taet
  p_taet.htprtr                                         as "haupttaetigkeit_schluessel",
  int2bool(p_taet.htprtr)                               as "haupttaetigkeit",
  l_taetigkeiten_anhang1_eprtr.prtr_branchengruppe_id   as "branchengruppe",
  l_taetigkeiten_anhang1_eprtr.schl                     as "taetigkeit_anhang1_schluessel",
  l_taetigkeiten_anhang1_eprtr.ktext_en                 as "taetigkeit_anhang1",
  b.nace_id                                             as "nace_schluessel",
  l_nace_wirtschaftszweige.ktext_en                     as "nace",

-- p_freis: Lassen wir hier. Freisetzungen siehe Teil I.
--  l_kompartimente.schl                                  as "kompartiment_schluessel",
--  l_kompartimente.ktext                                 as "kompartiment",
--  l_schadstoffgruppen.schl as "schadstoffgruppe_schluessel",
--  l_schadstoffgruppen.ktext as "schadstoffgruppe",
--  l_stoffe.schl                                         as "stoff_schluessel",
--  l_stoffe.ktext                                        as "stoff",

-- p_vabw (Verbringung Abwasser)
  'W'                                                   as "kompartiment_schluessel",
  'Wastewater'                                          as "kompartiment",
  l_schadstoffgruppen.schl                              as "schadstoffgruppe_schluessel",
  l_schadstoffgruppen.ktext_en                          as "schadstoffgruppe",
  l_stoffe.schl                                         as "stoff_schluessel",
  risa_collect_synonyme_en(l_stoffe.prtr_anhang2_nr, ' ')  as "stoff",
  l_bestimmungsmethoden_ermittlungsarten.ktext_en       as "bestimmungsmethode",

-- p_vabf (Verbringung Abfall)
  l_disposition_abfall.schl                             as "beseitigung_verwertung_schluessel",
  l_disposition_abfall.ktext_en                         as "beseitigung_verwertung",
  l_abfalltypen.schl                                    as "gefaehrlich_schluessel",
  l_abfalltypen.ktext_en                                as "gefaehrlich",
  l_ia.schl                                             as "inland_ausland_schluessel",
  l_ia.ktext_en                                         as "inland_ausland",
  p_vabf.vbname                                         as "vbname",
  p_vabf.vbort                                          as "vbort",
  vbstaaten.schl                                        as "vbstaat_schluessel",
  p_vabf.vbsort                                         as "vbsort",
  vbsstaaten.schl                                       as "vbsstaat_schluessel",
  l_bestimmungsmethoden_ermittlungsarten_abfall.ktext_en   as "bestimmungsmethode_abfall"

from p_betrieb b
  join l_bundeslaender
    on b.land_id = l_bundeslaender.id
  left join l_gemeinden
    on b.gemde_id = l_gemeinden.id
  left join l_flusseinzugsgebiete
    on b.flezgeb_id = l_flusseinzugsgebiete.id
  left join l_nace_wirtschaftszweige
    on b.nace_id = l_nace_wirtschaftszweige.id
  left join l_schwellenwertueberschreitungsarten luft_swuebart
    on b.freisl_id = luft_swuebart.id
  left join l_schwellenwertueberschreitungsarten boden_swuebart
    on b.freisb_id = boden_swuebart.id
  left join l_schwellenwertueberschreitungsarten wasser_swuebart
    on b.freisw_id = wasser_swuebart.id
  left join l_schwellenwertueberschreitungsarten verbw_swuebart
    on b.verbw_id = verbw_swuebart.id
  left join l_schwellenwertueberschreitungsarten verba_swuebart
    on b.verba_id = verba_swuebart.id
  left join l_schwellenwertueberschreitungsarten verbabfgef_swuebart
    on b.verbang_id = verbabfgef_swuebart.id
  left join l_bearbeitungsstatus
    on b.bearb_id = l_bearbeitungsstatus.id
  left join l_pruefstati_geheimhaltung
    on b.pruefg_id = l_pruefstati_geheimhaltung.id
  left join l_pruefstati l_pruefstati_luft
    on b.pruefl_id = l_pruefstati_luft.id
  left join l_pruefstati l_pruefstati_boden
    on b.pruefb_id = l_pruefstati_boden.id
  left join l_pruefstati l_pruefstati_wasser
    on b.pruefw_id = l_pruefstati_wasser.id
  left join l_pruefstati l_pruefstati_abfall
    on b.pruefa_id = l_pruefstati_abfall.id

-- 1:n
  left join p_taet
    on b.id = p_taet.p_betrieb_id
  left join l_taetigkeiten_anhang1_eprtr
    on p_taet.nrprtr_id = l_taetigkeiten_anhang1_eprtr.id

-- 1:n
--  left join p_freis
--    on b.id = p_freis.p_betrieb_id
--  left join l_kompartimente
--    on p_freis.medium_id = l_kompartimente.id
--  left join l_stoffe
--    on p_freis.stoffnr_id = l_stoffe.id
--  left join l_schadstoffgruppen
--    on l_stoffe.sgruppe_id = l_schadstoffgruppen.id

-- 1:n
  left join p_vabw
    on b.id = p_vabw.p_betrieb_id
  left join l_bestimmungsmethoden_ermittlungsarten
    on p_vabw.bestim_id = l_bestimmungsmethoden_ermittlungsarten.id
  left join l_stoffe
    on p_vabw.stoffnr_id = l_stoffe.id
  left join l_schadstoffgruppen
    on l_stoffe.sgruppe_id = l_schadstoffgruppen.id

-- 1:n
  left join p_vabf
    on b.id = p_vabf.p_betrieb_id
  left join l_disposition_abfall
    on p_vabf.verbes_id = l_disposition_abfall.id
  left join l_abfalltypen
    on p_vabf.abfart_id = l_abfalltypen.id
  left join l_ia
    on p_vabf.aland_id = l_ia.id  
  left join l_staaten vbstaaten
    on coalesce(p_vabf.vbstaat_id,p_vabf.astaat_id) = vbstaaten.id
  left join l_staaten vbsstaaten
    on coalesce(p_vabf.vbsstaat_id,p_vabf.astaat_id) = vbsstaaten.id
  left join l_bestimmungsmethoden_ermittlungsarten l_bestimmungsmethoden_ermittlungsarten_abfall
    on p_vabf.bestim_id = l_bestimmungsmethoden_ermittlungsarten_abfall.id

where 
      b.date_from <= (#{jahr}*10000+101)
  and b.date_to   >= (#{jahr}*10000+1231)
  and b.state = 0
  and (b.name1 is not null or b.name2 is not null)
  and l_schadstoffgruppen.schl is not null
;




--Und hier nochmal (fast) das Gleiche (wie für Freisetzung) um auch die nbiofracht Einträge zu erfassen
insert into suchtabelle_en_#{jahr}
select
  b.ido                                                 as "betrieb_ido",
  trim(both ' ' from regexp_replace(lower(coalesce (b.name1,'')||' '|| coalesce(b.name2,'')), '[ ]+', ' ', 'g'))
                                                        as "betriebseinrichtung",
  trim(both ' ' from regexp_replace(lower(coalesce (b.bname1,'')||' '|| coalesce(b.bname2,'')), '[ ]+', ' ', 'g'))
                                                        as "muttergesellschaft",
  trim(both ' ' from regexp_replace(lower(coalesce (b.eigname,'')||' '|| coalesce(b.betname,'')), '[ ]+', ' ', 'g'))
                                                        as "eigentuemer_betreiber",
  b.plzstr                                              as "plz",
  trim(both '' from lower(coalesce (b.ort,'') || ' ' || coalesce(l_gemeinden.ktext, '')))
                                                        as "gemeinde",
  b.land_id                                             as "bundesland_schluessel",
  l_bundeslaender.ltext_en                              as "bundesland",
  l_flusseinzugsgebiete.schl                            as "flusseinzugsgebiet_schluessel",
  l_flusseinzugsgebiete.ktext_en                        as "flusseinzugsgebiet",

-- p_taet
  p_taet.htprtr                                         as "haupttaetigkeit_schluessel",
  int2bool(p_taet.htprtr)                               as "haupttaetigkeit",
  l_taetigkeiten_anhang1_eprtr.prtr_branchengruppe_id   as "branchengruppe",
  l_taetigkeiten_anhang1_eprtr.schl                     as "taetigkeit_anhang1_schluessel",
  l_taetigkeiten_anhang1_eprtr.ktext_en                 as "taetigkeit_anhang1",
  b.nace_id                                             as "nace_schluessel",
  l_nace_wirtschaftszweige.ktext_en                     as "nace",

-- p_freis
  l_kompartimente.schl                                  as "kompartiment_schluessel",
  l_kompartimente.ktext_en                              as "kompartiment",
  l_schadstoffgruppen.schl                              as "schadstoffgruppe_schluessel",
  l_schadstoffgruppen.ktext_en                          as "schadstoffgruppe",
  l_stoffe.schl                                         as "stoff_schluessel",
  risa_collect_synonyme_en(l_stoffe.id, ' ')               as "stoff",
  l_bestimmungsmethoden_ermittlungsarten.ktext_en       as "bestimmungsmethode",

-- p_vabw (Verbringung Abwasser): Lassen wir hier. Union! Teil II siehe unten.

-- p_vabf (Verbringung Abfall)
  l_disposition_abfall.schl                             as "beseitigung_verwertung_schluessel",
  l_disposition_abfall.ktext_en                         as "beseitigung_verwertung",
  l_abfalltypen.schl                                    as "gefaehrlich_schluessel",
  l_abfalltypen.ktext_en                                as "gefaehrlich",
  l_ia.schl                                             as "inland_ausland_schluessel",
  l_ia.ktext_en                                         as "inland_ausland",
  p_vabf.vbname                                         as "vbname",
  p_vabf.vbort                                          as "vbort",
  vbstaaten.schl                                        as "vbstaat_schluessel",
  p_vabf.vbsort                                         as "vbsort",
  vbsstaaten.schl                                       as "vbsstaat_schluessel",
  l_bestimmungsmethoden_ermittlungsarten_abfall.ktext_en as "bestimmungsmethode_abfall"

from p_betrieb b
  join l_bundeslaender
    on b.land_id = l_bundeslaender.id
  left join l_gemeinden
    on b.gemde_id = l_gemeinden.id
  left join l_flusseinzugsgebiete
    on b.flezgeb_id = l_flusseinzugsgebiete.id
  left join l_nace_wirtschaftszweige
    on b.nace_id = l_nace_wirtschaftszweige.id
  left join l_schwellenwertueberschreitungsarten luft_swuebart
    on b.freisl_id = luft_swuebart.id
  left join l_schwellenwertueberschreitungsarten boden_swuebart
    on b.freisb_id = boden_swuebart.id
  left join l_schwellenwertueberschreitungsarten wasser_swuebart
    on b.freisw_id = wasser_swuebart.id
  left join l_schwellenwertueberschreitungsarten verbw_swuebart
    on b.verbw_id = verbw_swuebart.id
  left join l_schwellenwertueberschreitungsarten verba_swuebart
    on b.verba_id = verba_swuebart.id
  left join l_schwellenwertueberschreitungsarten verbabfgef_swuebart
    on b.verbang_id = verbabfgef_swuebart.id
  left join l_bearbeitungsstatus
    on b.bearb_id = l_bearbeitungsstatus.id
  left join l_pruefstati_geheimhaltung
    on b.pruefg_id = l_pruefstati_geheimhaltung.id
  left join l_pruefstati l_pruefstati_luft
    on b.pruefl_id = l_pruefstati_luft.id
  left join l_pruefstati l_pruefstati_boden
    on b.pruefb_id = l_pruefstati_boden.id
  left join l_pruefstati l_pruefstati_wasser
    on b.pruefw_id = l_pruefstati_wasser.id
  left join l_pruefstati l_pruefstati_abfall
    on b.pruefa_id = l_pruefstati_abfall.id

-- 1:n
  left join p_taet
    on b.id = p_taet.p_betrieb_id
  left join l_taetigkeiten_anhang1_eprtr
    on p_taet.nrprtr_id = l_taetigkeiten_anhang1_eprtr.id

-- 1:n
  inner join p_freis
    on b.id = p_freis.p_betrieb_id
    and p_freis.stoffnr_id = 3
    and p_freis.nbiofracht > 0
  left join l_kompartimente
    on p_freis.medium_id = l_kompartimente.id
  left join l_bestimmungsmethoden_ermittlungsarten
    on p_freis.bestim_id = l_bestimmungsmethoden_ermittlungsarten.id
  left join l_stoffe
    on 92 = l_stoffe.id
  left join l_schadstoffgruppen
    on l_stoffe.sgruppe_id = l_schadstoffgruppen.id

-- 1:n
--  left join p_vabw
--    on b.id = p_vabw.p_betrieb_id

-- 1:n
  left join p_vabf
    on b.id = p_vabf.p_betrieb_id
  left join l_disposition_abfall
    on p_vabf.verbes_id = l_disposition_abfall.id
  left join l_abfalltypen
    on p_vabf.abfart_id = l_abfalltypen.id
  left join l_ia
    on p_vabf.aland_id = l_ia.id
  left join l_staaten vbstaaten
    on coalesce(p_vabf.vbstaat_id,p_vabf.astaat_id) = vbstaaten.id
  left join l_staaten vbsstaaten
    on coalesce(p_vabf.vbsstaat_id,p_vabf.astaat_id) = vbsstaaten.id
  left join l_bestimmungsmethoden_ermittlungsarten l_bestimmungsmethoden_ermittlungsarten_abfall
    on p_vabf.bestim_id = l_bestimmungsmethoden_ermittlungsarten_abfall.id

where 
      b.date_from <= (#{jahr}*10000+101)
  and b.date_to   >= (#{jahr}*10000+1231)
  and b.state = 0
  and (b.name1 is not null or b.name2 is not null)
;

vacuum full analyze;
