-- ========================================================================================================
--  ALKIS-PostGIS (Full-Schema) - Flurstücke Suchen, die eine Verkettung von mehreren Buchungsstellen haben
-- ========================================================================================================

-- Aufgaben:
-- ---------
-- Flurstücke ermitteln, die 2 bis 4 (oder mehr?) Buchungsstellen haben die jeweils ein "Recht an" einer anderen Buchungsstelle haben.
-- Beispiel:
--   "Wohnungs-/Teiluntererbbaurecht" >an> "Aufgeteiltes Untererbbaurecht WEG" >an> "Erbbaurecht" >an> "Grundstück" >Buchung> Flurstück 
-- In einem WMS diese besonderen Buchungen hervorheben.
-- Die betroffenen Buchungsarten auflisten. Auch einige Einzelfälle selektieren.
-- Dies dient zunächst dazu, um Testfälle für die Weiterentwicklung der Buchauskunft zu finden (Entwicklung). Vielleicht hat auch ein Anwender Verwendung dafür.

-- Ausführung in:
-- Step "postcreate.d" des ALKIS-Importers. Hier also nur Definitionen machen, keine Daten bearbeiten.

--Stand 
--  2017-12-07 Prototyp
--  2018-11-09 Umstellung auf Full-Schema.
--			Die ehem. Tab. "alkis_wertearten" ist jetzt ein riesiger Union-View. Diesen View ersetzen durch die passende neue Tabelle.

-- Views für die Darstellung im WMS
-- =================================

-- 2 beteiligte Buchungsstellen
-- "Erbbaurecht" und ähnliches
-- --------------------------
-- FS >istGebucht> Buchungstelle 1  <an<  Buchungstelle 2
-- --------------------------------------------------------------------
   DROP VIEW IF EXISTS buchungsrechte_2arten;
   DROP VIEW IF EXISTS buchungsrechte_2wms;
CREATE OR REPLACE VIEW buchungsrechte_2wms
AS
 SELECT DISTINCT                    -- Ohne DISTINCT mehrfache Überlagerung
    f.gml_id,                       -- Unique Key
    s2.buchungsart,                 -- verschlüsselt (Nummer)
    f.wkb_geometry
  FROM ax_flurstueck     f          -- Flurstück
  JOIN ax_buchungsstelle s1         -- dienende Buchung
    ON f.istgebucht = s1.gml_id
  JOIN ax_buchungsstelle s2         -- herrschende Buchung
    ON s1.gml_id = ANY(s2.an)       -- hat Recht an
  WHERE f.endet IS NULL AND s1.endet IS NULL AND s2.endet IS NULL;

COMMENT ON VIEW buchungsrechte_2wms IS 'Für WMS-Anzeige von Flurstücken mit Buchungsarten mit 2 beteiligten Buchungsstellen, z.B. Erbbaurecht.';
GRANT SELECT ON TABLE public.buchungsrechte_2wms  TO ms6;


-- 3 beteiligte Buchungsstellen
-- "Untererbbaurecht" und ähnliches
-- --------------------------------
-- FS >istGebucht> Buchungstelle 1  <an<  Buchungstelle 2 <an<  Buchungstelle 3
-- ------------------------------------------------------------------------------------------------------------------
   DROP VIEW IF EXISTS buchungsrechte_3arten;
   DROP VIEW IF EXISTS buchungsrechte_3wms;
CREATE OR REPLACE VIEW buchungsrechte_3wms
AS
 SELECT DISTINCT f.gml_id, s3.buchungsart, f.wkb_geometry
   FROM ax_flurstueck     f  
   JOIN ax_buchungsstelle s1 ON f.istgebucht = s1.gml_id
   JOIN ax_buchungsstelle s2 ON s1.gml_id = ANY(s2.an)    
   JOIN ax_buchungsstelle s3 ON s2.gml_id = ANY(s3.an)  
  WHERE f.endet IS NULL AND s1.endet IS NULL AND s2.endet IS NULL AND s3.endet IS NULL;

COMMENT ON VIEW buchungsrechte_3wms IS 'Für WMS-Anzeige von Flurstücken mit Buchungsarten mit 3 beteiligten Buchungsstellen, z.B. Untererbbaurecht.';
GRANT SELECT ON TABLE public.buchungsrechte_3wms  TO ms6;


-- 4 beteiligte Buchungsstellen
-- Teil-Untererbbaurecht
-- --------------------------------
-- FS >istGebucht> Buchungstelle 1  <an<  Buchungstelle 2 <an<  Buchungstelle 3 <an<  Buchungstelle 4
-- ------------------------------------------------------------------------------------------------------------------
   DROP VIEW IF EXISTS buchungsrechte_4arten;
   DROP VIEW IF EXISTS buchungsrechte_4wms;
CREATE OR REPLACE VIEW buchungsrechte_4wms
AS
 SELECT DISTINCT f.gml_id, s4.buchungsart, f.wkb_geometry
  FROM ax_flurstueck     f  
  JOIN ax_buchungsstelle s1 ON f.istgebucht = s1.gml_id
  JOIN ax_buchungsstelle s2 ON s1.gml_id = ANY(s2.an)    
  JOIN ax_buchungsstelle s3 ON s2.gml_id = ANY(s3.an) 
  JOIN ax_buchungsstelle s4 ON s3.gml_id = ANY(s4.an)  
 WHERE f.endet IS NULL AND s1.endet IS NULL AND s2.endet IS NULL AND s3.endet IS NULL AND s4.endet IS NULL;

COMMENT ON VIEW buchungsrechte_4wms IS 'Für WMS-Anzeige von Flurstücken mit Buchungsarten mit 4 beteiligten Buchungsstellen.';
GRANT SELECT ON TABLE public.buchungsrechte_4wms  TO ms6;


-- Gibts das?  Nein!
-- --------------------------------
-- FS >istGebucht> Buchungstelle 1  <an<  Buchungstelle 2 <an<  Buchungstelle 3 <an<  Buchungstelle 4 <an<  Buchungstelle 5
-- ------------------------------------------------------------------------------------------------------------------
-- 5 beteiligte Buchungsstellen
-- DROP VIEW IF EXISTS buchungsrechte_5wms;
/*
CREATE OR REPLACE VIEW buchungsrechte_5wms
AS
 SELECT DISTINCT f.gml_id, s5.buchungsart, f.wkb_geometry
   FROM ax_flurstueck     f  
   JOIN ax_buchungsstelle s1  ON f.istgebucht = s1.gml_id
   JOIN ax_buchungsstelle s2  ON s1.gml_id = ANY (s2.an)    
   JOIN ax_buchungsstelle s3  ON s2.gml_id = ANY (s3.an) 
   JOIN ax_buchungsstelle s4  ON s3.gml_id = ANY (s4.an)  
   JOIN ax_buchungsstelle s5  ON s4.gml_id = ANY (s5.an)  
  WHERE f.endet IS NULL AND s1.endet IS NULL AND s2.endet IS NULL AND s3.endet IS NULL AND s4.endet IS NULL AND s5.endet IS NULL;

COMMENT ON VIEW buchungsrechte_5wms IS 'Für WMS-Anzeige von Flurstücken mit Buchungsarten mit 5 beteiligten Buchungsstellen.';
GRANT SELECT ON TABLE public.buchungsrechte_5wms  TO ms6;
*/

-- Views für die Modellierung der Classes im WMS
-- =============================================
-- Buchungsart entschlüsseln. Die Treffer der WMS-Views weiter verdichten:

CREATE OR REPLACE VIEW buchungsrechte_2arten AS
 SELECT DISTINCT s.buchungsart, wb.beschreibung AS bu_art
   FROM buchungsrechte_2wms s
   JOIN ax_buchungsart_buchungsstelle wb
     ON s.buchungsart = wb.wert
  ORDER BY s.buchungsart; 
COMMENT ON VIEW buchungsrechte_2arten IS 'Vorkommende Buchungsarten bei der Verkettung von 2 Buchungsstellen.';
-- 1301;"Wohnungs-/Teileigentum"
-- 1302;"Miteigentum Par. 3 Abs. 4 GBO"
-- 2101;"Erbbaurecht"
-- 2201;"Aufgeteiltes Erbbaurecht WEG"

CREATE OR REPLACE VIEW buchungsrechte_3arten AS
 SELECT DISTINCT s.buchungsart, wb.beschreibung AS bu_art
   FROM buchungsrechte_3wms s
   JOIN ax_buchungsart_buchungsstelle wb
     ON s.buchungsart = wb.wert
  ORDER BY buchungsart; 
COMMENT ON VIEW buchungsrechte_3arten IS 'Vorkommende Buchungsarten bei der Verkettung von 3 Buchungsstellen.';
-- 2102;"Untererbbaurecht"
-- 2202;"Aufgeteiltes Untererbbaurecht WEG"
-- 2203;"Aufgeteiltes Recht Par. 3 Abs. 4 GBO"
-- 2301;"Wohnungs-/Teilerbbaurecht"

CREATE OR REPLACE VIEW buchungsrechte_4arten AS
 SELECT DISTINCT s.buchungsart, wb.beschreibung AS bu_art
   FROM buchungsrechte_4wms s
   JOIN ax_buchungsart_buchungsstelle wb
     ON s.buchungsart = wb.wert
  ORDER BY buchungsart; 
COMMENT ON VIEW buchungsrechte_4arten IS 'Vorkommende Buchungsarten bei der Verkettung von 4 Buchungsstellen.';
-- 2302;"Wohnungs-/Teiluntererbbaurecht"
-- 2303;"Erbbaurechtsanteil Par. 3 Abs. 4 GBO"


-- Views um Einzelfälle zu suchen und aufzulisten
-- ==============================================
CREATE OR REPLACE VIEW buchungsrechte_4faelle
AS
 SELECT 
    f.gml_id, f.flurstueckskennzeichen,
    s1.laufendenummer AS bvnr1, s1.buchungsart AS buchunsart_1, w1.beschreibung AS ba1, b1.buchungsblattnummermitbuchstabenerweiterung AS blatt1,
    s2.laufendenummer AS bvnr2, s2.buchungsart AS buchunsart_2, w2.beschreibung AS ba2, b2.buchungsblattnummermitbuchstabenerweiterung AS blatt2,
    s3.laufendenummer AS bvnr3, s3.buchungsart AS buchunsart_3, w3.beschreibung AS ba3, b3.buchungsblattnummermitbuchstabenerweiterung AS blatt3,
    s4.laufendenummer AS bvnr4, s4.buchungsart AS buchunsart_4, w4.beschreibung AS ba4, b4.buchungsblattnummermitbuchstabenerweiterung AS blatt4
  -- Flurstück
  FROM ax_flurstueck     f  
  -- 1. Buchung
  JOIN ax_buchungsstelle s1 ON f.istgebucht = s1.gml_id
--LEFT JOIN alkis_wertearten  w1 ON cast(s1.buchungsart AS character varying)=w1.k AND w1.element='ax_buchungsstelle' AND w1.bezeichnung='buchungsart' 
  LEFT JOIN ax_buchungsart_buchungsstelle w1 ON s1.buchungsart = w1.wert
  JOIN ax_buchungsblatt  b1 ON s1.istbestandteilvon = b1.gml_id
  -- 2. Buchung
  JOIN ax_buchungsstelle s2 ON s1.gml_id = ANY(s2.an)
  LEFT JOIN ax_buchungsart_buchungsstelle w2 ON s2.buchungsart = w2.wert
  JOIN ax_buchungsblatt  b2 ON s2.istbestandteilvon = b2.gml_id
  -- 3. Buchung
  JOIN ax_buchungsstelle s3 ON s2.gml_id = ANY(s3.an)
  LEFT JOIN ax_buchungsart_buchungsstelle w3 ON s3.buchungsart = w3.wert
  JOIN ax_buchungsblatt  b3 ON s3.istbestandteilvon = b3.gml_id
  -- 4. Buchung
  JOIN ax_buchungsstelle s4 ON s3.gml_id = ANY(s4.an)  
  LEFT JOIN ax_buchungsart_buchungsstelle w4 ON s4.buchungsart = w4.wert
  JOIN ax_buchungsblatt  b4 ON s4.istbestandteilvon = b4.gml_id
 -- nur frische Sachen
 WHERE f.endet  IS NULL 
   AND s1.endet IS NULL AND b1.endet IS NULL 
   AND s2.endet IS NULL AND b2.endet IS NULL 
   AND s3.endet IS NULL AND b3.endet IS NULL 
   AND s4.endet IS NULL AND b4.endet IS NULL 
 LIMIT 30;

COMMENT ON VIEW buchungsrechte_4faelle IS 'Für Buchungsarten mit 4 beteiligten Buchungsstellen die einzelnen Kennzeichen tabellarisch auflisten.';


CREATE OR REPLACE VIEW buchungsrechte_3text 
AS
 SELECT 
    a3.beschreibung || ' (' || b3.blattart || ') ' || b3.buchungsblattnummermitbuchstabenerweiterung || ' (lfd. ' || s3.laufendenummer || ') hat ' || w3.beschreibung || ' (' || s3.buchungsart || ') an ' AS stufe3,
    a2.beschreibung || ' (' || b2.blattart || ') ' || b2.buchungsblattnummermitbuchstabenerweiterung || ' (lfd. ' || s2.laufendenummer || ') hat ' || w2.beschreibung || ' (' || s2.buchungsart || ') an ' AS stufe2,
    a1.beschreibung || ' (' || b1.blattart || ') ' || b1.buchungsblattnummermitbuchstabenerweiterung || ' (lfd. ' || s1.laufendenummer || ') hat ' || w1.beschreibung || ' (' || s1.buchungsart || ') mit Buchung ' AS stufe1,
    'Flurstück ' || f.flurstueckskennzeichen || ' (gml_id=' || f.gml_id || ')' AS flurstueck
  -- Flurstück
  FROM ax_flurstueck     f  
  -- 1. Buchung
  JOIN ax_buchungsstelle      s1 ON f.istgebucht = s1.gml_id
  LEFT JOIN ax_buchungsart_buchungsstelle w1 ON s1.buchungsart = w1.wert
  JOIN ax_buchungsblatt       b1 ON s1.istbestandteilvon = b1.gml_id
--LEFT JOIN alkis_wertearten  a1 ON b1.blattart=a1.k AND a1.element='ax_buchungsblatt' AND a1.bezeichnung='blattart' 
  LEFT JOIN ax_blattart_buchungsblatt a1 ON b1.blattart = a1.wert
  -- 2. Buchung
  JOIN ax_buchungsstelle      s2 ON s1.gml_id = ANY(s2.an)
  LEFT JOIN ax_buchungsart_buchungsstelle w2 ON s2.buchungsart = w2.wert
  JOIN ax_buchungsblatt       b2 ON s2.istbestandteilvon = b2.gml_id
  LEFT JOIN ax_blattart_buchungsblatt a2 ON b2.blattart = a2.wert
  -- 3. Buchung
  JOIN ax_buchungsstelle      s3 ON s2.gml_id = ANY(s3.an)
  LEFT JOIN ax_buchungsart_buchungsstelle w3 ON s3.buchungsart = w3.wert
  JOIN ax_buchungsblatt       b3 ON s3.istbestandteilvon = b3.gml_id
  LEFT JOIN ax_blattart_buchungsblatt a3 ON b3.blattart = a3.wert
 -- nur frische Sachen
 WHERE f.endet  IS NULL 
   AND s1.endet IS NULL AND b1.endet IS NULL 
   AND s2.endet IS NULL AND b2.endet IS NULL 
   AND s3.endet IS NULL AND b3.endet IS NULL 
 LIMIT 30;
-- Dauert 25 Sec.
COMMENT ON VIEW buchungsrechte_3text IS 'Für Buchungsarten mit 3 beteiligten Buchungsstellen die einzelnen Kennzeichen als beschreibenden Text auflisten.';


CREATE OR REPLACE VIEW buchungsrechte_4text 
AS
 SELECT 
    a4.beschreibung || ' (' || b4.blattart || ') ' || b4.buchungsblattnummermitbuchstabenerweiterung || ' (lfd. ' || s4.laufendenummer || ') hat ' || w4.beschreibung || ' (' || s4.buchungsart || ') an ' AS stufe4,
    a3.beschreibung || ' (' || b3.blattart || ') ' || b3.buchungsblattnummermitbuchstabenerweiterung || ' (lfd. ' || s3.laufendenummer || ') hat ' || w3.beschreibung || ' (' || s3.buchungsart || ') an ' AS stufe3,
    a2.beschreibung || ' (' || b2.blattart || ') ' || b2.buchungsblattnummermitbuchstabenerweiterung || ' (lfd. ' || s2.laufendenummer || ') hat ' || w2.beschreibung || ' (' || s2.buchungsart || ') an ' AS stufe2,
    a1.beschreibung || ' (' || b1.blattart || ') ' || b1.buchungsblattnummermitbuchstabenerweiterung || ' (lfd. ' || s1.laufendenummer || ') hat ' || w1.beschreibung || ' (' || s1.buchungsart || ') mit Buchung ' AS stufe1,
    'Flurstück ' || f.flurstueckskennzeichen || ' (gml_id=' || f.gml_id || ')' AS flurstueck
  -- Flurstück
  FROM ax_flurstueck     f  
  -- 1. Buchung
  JOIN ax_buchungsstelle      s1 ON f.istgebucht = s1.gml_id
  LEFT JOIN ax_buchungsart_buchungsstelle w1 ON s1.buchungsart = w1.wert
  JOIN ax_buchungsblatt       b1 ON s1.istbestandteilvon = b1.gml_id
  LEFT JOIN ax_blattart_buchungsblatt a1 ON b1.blattart = a1.wert
  -- 2. Buchung
  JOIN ax_buchungsstelle      s2 ON s1.gml_id = ANY(s2.an)
  LEFT JOIN ax_buchungsart_buchungsstelle w2 ON s2.buchungsart = w2.wert
  JOIN ax_buchungsblatt       b2 ON s2.istbestandteilvon = b2.gml_id
  LEFT JOIN ax_blattart_buchungsblatt a2 ON b2.blattart = a2.wert
  -- 3. Buchung
  JOIN ax_buchungsstelle      s3 ON s2.gml_id = ANY(s3.an)
  LEFT JOIN ax_buchungsart_buchungsstelle w3 ON s3.buchungsart = w3.wert
  JOIN ax_buchungsblatt       b3 ON s3.istbestandteilvon = b3.gml_id
  LEFT JOIN ax_blattart_buchungsblatt a3 ON b3.blattart = a3.wert
  -- 4. Buchung
  JOIN ax_buchungsstelle      s4 ON s3.gml_id = ANY(s4.an)  
  LEFT JOIN ax_buchungsart_buchungsstelle w4 ON s4.buchungsart = w4.wert
  JOIN ax_buchungsblatt       b4 ON s4.istbestandteilvon = b4.gml_id
  LEFT JOIN ax_blattart_buchungsblatt a4 ON b4.blattart = a4.wert
 -- nur frische Sachen
 WHERE f.endet  IS NULL 
   AND s1.endet IS NULL AND b1.endet IS NULL 
   AND s2.endet IS NULL AND b2.endet IS NULL 
   AND s3.endet IS NULL AND b3.endet IS NULL 
   AND s4.endet IS NULL AND b4.endet IS NULL 
 LIMIT 30;
-- Dauert 5 Min.!
COMMENT ON VIEW buchungsrechte_4text IS 'Für Buchungsarten mit 4 beteiligten Buchungsstellen die einzelnen Kennzeichen als beschreibenden Text auflisten.';

-- Ende --