-- ====================================================================================================
--  ALKIS-PostGIS  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

-- V i e w s
-- =================================

-- 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 maherfache Überlagerung
    f.gml_id,                       -- Unique Key
    s2.buchungsart,                 -- verschlüsselt (Nummer)
    f.wkb_geometry
 --, wb.v      AS bu_art            -- Buchungsart entschlüsselt  -- Hier nicht!
  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
--JOIN alkis_wertearten wb          -- Entschlüsseln "Buchungsart"
--  ON cast(s2.buchungsart AS character varying) = wb.k 
-- AND wb.element = 'ax_buchungsstelle' AND wb.bezeichnung = 'buchungsart' 
  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;
-- z.B. 69 Flurstücke in Lage in 312 msec.


-- 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;
-- 6 Flurstücke im Kreis Minden-Lübbecke,
-- 3 Flurstücke in Lage in 180 msec. ("DENW17AL0000VffJ" "DENW17AL0000VnuJ" "DENW17AL0000Vwe1")


-- 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.v AS bu_art
   FROM buchungsrechte_2wms s
   JOIN alkis_wertearten wb -- Entschlüsseln "Buchungsart"
     ON cast(s.buchungsart AS character varying) = wb.k 
    AND wb.element = 'ax_buchungsstelle' AND wb.bezeichnung = 'buchungsart' 
  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.v AS bu_art
   FROM buchungsrechte_3wms s
   JOIN alkis_wertearten wb
     ON cast(s.buchungsart AS character varying) = wb.k 
    AND wb.element = 'ax_buchungsstelle' AND wb.bezeichnung = 'buchungsart' 
  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.v AS bu_art
   FROM buchungsrechte_4wms s
   JOIN alkis_wertearten wb
     ON cast(s.buchungsart AS character varying) = wb.k 
    AND wb.element = 'ax_buchungsstelle' AND wb.bezeichnung = 'buchungsart' 
  ORDER BY buchungsart; 
COMMENT ON VIEW buchungsrechte_4arten IS 'Vorkommende Buchungsarten bei der Verkettung von 4 Buchungsstellen.';
-- 2302;"Wohnungs-/Teiluntererbbaurecht"


-- 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.v AS ba1, b1.buchungsblattnummermitbuchstabenerweiterung AS blatt1,
    s2.laufendenummer AS bvnr2, s2.buchungsart AS buchunsart_2, w2.v AS ba2, b2.buchungsblattnummermitbuchstabenerweiterung AS blatt2,
    s3.laufendenummer AS bvnr3, s3.buchungsart AS buchunsart_3, w3.v AS ba3, b3.buchungsblattnummermitbuchstabenerweiterung AS blatt3,
    s4.laufendenummer AS bvnr4, s4.buchungsart AS buchunsart_4, w4.v 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' 
  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 alkis_wertearten  w2 ON cast(s2.buchungsart AS character varying)=w2.k AND w2.element='ax_buchungsstelle' AND w2.bezeichnung='buchungsart' 
  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 alkis_wertearten  w3 ON cast(s3.buchungsart AS character varying)=w3.k AND w3.element='ax_buchungsstelle' AND w3.bezeichnung='buchungsart' 
  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 alkis_wertearten  w4 ON cast(s4.buchungsart AS character varying)=w4.k AND w4.element='ax_buchungsstelle' AND w4.bezeichnung='buchungsart' 
  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 20;

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.v || ' (' || b3.blattart || ') ' || b3.buchungsblattnummermitbuchstabenerweiterung || ' (lfd. ' || s3.laufendenummer || ') hat ' || w3.v || ' (' || s3.buchungsart || ') an ' AS stufe3,
    a2.v || ' (' || b2.blattart || ') ' || b2.buchungsblattnummermitbuchstabenerweiterung || ' (lfd. ' || s2.laufendenummer || ') hat ' || w2.v || ' (' || s2.buchungsart || ') an ' AS stufe2,
    a1.v || ' (' || b1.blattart || ') ' || b1.buchungsblattnummermitbuchstabenerweiterung || ' (lfd. ' || s1.laufendenummer || ') hat ' || w1.v || ' (' || 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 alkis_wertearten  w1 ON cast(s1.buchungsart AS character varying)=w1.k AND w1.element='ax_buchungsstelle' AND w1.bezeichnung='buchungsart' 
  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' 

  -- 2. Buchung
  JOIN ax_buchungsstelle      s2 ON s1.gml_id = ANY(s2.an)
  LEFT JOIN alkis_wertearten  w2 ON cast(s2.buchungsart AS character varying)=w2.k AND w2.element='ax_buchungsstelle' AND w2.bezeichnung='buchungsart' 
  JOIN ax_buchungsblatt       b2 ON s2.istbestandteilvon = b2.gml_id
  LEFT JOIN alkis_wertearten  a2 ON b2.blattart=a2.k AND a2.element='ax_buchungsblatt' AND a2.bezeichnung='blattart' 

  -- 3. Buchung
  JOIN ax_buchungsstelle      s3 ON s2.gml_id = ANY(s3.an)
  LEFT JOIN alkis_wertearten  w3 ON cast(s3.buchungsart AS character varying)=w3.k AND w3.element='ax_buchungsstelle' AND w3.bezeichnung='buchungsart' 
  JOIN ax_buchungsblatt       b3 ON s3.istbestandteilvon = b3.gml_id
  LEFT JOIN alkis_wertearten  a3 ON b3.blattart=a3.k AND a3.element='ax_buchungsblatt' AND a3.bezeichnung='blattart' 

 -- 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 20;
-- Ausführung Stadt ca. 4 Sek.

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.v || ' (' || b4.blattart || ') ' || b4.buchungsblattnummermitbuchstabenerweiterung || ' (lfd. ' || s4.laufendenummer || ') hat ' || w4.v || ' (' || s4.buchungsart || ') an ' AS stufe4,
    a3.v || ' (' || b3.blattart || ') ' || b3.buchungsblattnummermitbuchstabenerweiterung || ' (lfd. ' || s3.laufendenummer || ') hat ' || w3.v || ' (' || s3.buchungsart || ') an ' AS stufe3,
    a2.v || ' (' || b2.blattart || ') ' || b2.buchungsblattnummermitbuchstabenerweiterung || ' (lfd. ' || s2.laufendenummer || ') hat ' || w2.v || ' (' || s2.buchungsart || ') an ' AS stufe2,
    a1.v || ' (' || b1.blattart || ') ' || b1.buchungsblattnummermitbuchstabenerweiterung || ' (lfd. ' || s1.laufendenummer || ') hat ' || w1.v || ' (' || 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 alkis_wertearten  w1 ON cast(s1.buchungsart AS character varying)=w1.k AND w1.element='ax_buchungsstelle' AND w1.bezeichnung='buchungsart' 
  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' 

  -- 2. Buchung
  JOIN ax_buchungsstelle      s2 ON s1.gml_id = ANY(s2.an)
  LEFT JOIN alkis_wertearten  w2 ON cast(s2.buchungsart AS character varying)=w2.k AND w2.element='ax_buchungsstelle' AND w2.bezeichnung='buchungsart' 
  JOIN ax_buchungsblatt       b2 ON s2.istbestandteilvon = b2.gml_id
  LEFT JOIN alkis_wertearten  a2 ON b2.blattart=a2.k AND a2.element='ax_buchungsblatt' AND a2.bezeichnung='blattart' 

  -- 3. Buchung
  JOIN ax_buchungsstelle      s3 ON s2.gml_id = ANY(s3.an)
  LEFT JOIN alkis_wertearten  w3 ON cast(s3.buchungsart AS character varying)=w3.k AND w3.element='ax_buchungsstelle' AND w3.bezeichnung='buchungsart' 
  JOIN ax_buchungsblatt       b3 ON s3.istbestandteilvon = b3.gml_id
  LEFT JOIN alkis_wertearten  a3 ON b3.blattart=a3.k AND a3.element='ax_buchungsblatt' AND a3.bezeichnung='blattart' 

  -- 4. Buchung
  JOIN ax_buchungsstelle      s4 ON s3.gml_id = ANY(s4.an)  
  LEFT JOIN alkis_wertearten  w4 ON cast(s4.buchungsart AS character varying)=w4.k AND w4.element='ax_buchungsstelle' AND w4.bezeichnung='buchungsart' 
  JOIN ax_buchungsblatt       b4 ON s4.istbestandteilvon = b4.gml_id
  LEFT JOIN alkis_wertearten  a4 ON b4.blattart=a4.k AND a4.element='ax_buchungsblatt' AND a4.bezeichnung='blattart' 

 -- 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 20;
-- Ausführung Stadt ca. 2 Minuten. Wieso so lange?

COMMENT ON VIEW buchungsrechte_4text IS 'Für Buchungsarten mit 4 beteiligten Buchungsstellen die einzelnen Kennzeichen als beschreibenden Text auflisten.';

-- Ende --