--
--


-- SET row_security = off;

--
-- Name: WKP_PROD; Type: DATABASE; Schema: -; Owner: -
--

-- CREATE DATABASE "WKP_PROD" WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';


--\connect "oet_data"

-- SET row_security = off;

--
-- Name: aquo_formaten; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA aquo_formaten;


--
-- Name: beheer; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA beheer;


--
-- Name: SCHEMA beheer; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON SCHEMA beheer IS 'Views en functies voor functioneel beheer. Het systeem draait ook zonder deze views.';


--
-- Name: domains; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA domains;


--
-- Name: eu_state_of_environment; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA eu_state_of_environment;


--
-- Name: factsheets; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA factsheets;


--
-- Name: SCHEMA factsheets; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON SCHEMA factsheets IS 'Speciaal schema voor factsheet. Hierin komen alleen de views die door de factsheets worden gebruikt';


--
-- Name: krm; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA krm;


--
-- Name: monprog; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA monprog;


--
-- Name: sgbp_dgrw; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA sgbp_dgrw;


--
-- Name: topology; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA topology;


--
-- Name: wfd_reporting; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA wfd_reporting;


--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';


--
-- Name: adminpack; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS adminpack WITH SCHEMA pg_catalog;


--
-- Name: EXTENSION adminpack; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION adminpack IS 'administrative functions for PostgreSQL';


--
-- Name: postgis; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;


--
-- Name: EXTENSION postgis; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION postgis IS 'PostGIS geometry, geography, and raster spatial types and functions';


--
-- Name: postgis_topology; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS postgis_topology WITH SCHEMA topology;


--
-- Name: EXTENSION postgis_topology; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION postgis_topology IS 'PostGIS topology spatial types and functions';


--
-- Name: tablefunc; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS tablefunc WITH SCHEMA public;


--
-- Name: EXTENSION tablefunc; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION tablefunc IS 'functions that manipulate whole tables, including crosstab';



--
-- Name: RefreshAllMaterializedViews(text, boolean); Type: FUNCTION; Schema: beheer; Owner: -
--

CREATE FUNCTION "RefreshAllMaterializedViews"(_schema text DEFAULT '*'::text, _concurrently boolean DEFAULT false) RETURNS integer
    LANGUAGE plpgsql
    AS $$
DECLARE
    r RECORD;
  BEGIN
    RAISE NOTICE 'Refreshing materialized view(s) in % %', CASE WHEN _schema = '*' THEN ' all schemas' ELSE 'schema "'|| _schema || '"' END, CASE WHEN _concurrently THEN 'concurrently' ELSE '' END;
    IF pg_is_in_recovery() THEN 
      RETURN 1;
    ELSE    
      FOR r IN SELECT schemaname, matviewname FROM pg_matviews WHERE schemaname = _schema OR _schema = '*' 
      LOOP
        RAISE NOTICE 'Refreshing %.%', r.schemaname, r.matviewname;
        EXECUTE 'REFRESH MATERIALIZED VIEW ' || CASE WHEN _concurrently THEN 'CONCURRENTLY ' ELSE '' END || '"' || r.schemaname || '"."' || r.matviewname || '"'; 
      END LOOP;
    END IF;
    RETURN 0;
  END
$$;


--
-- Name: sp_missing_links(); Type: FUNCTION; Schema: beheer; Owner: -
--

CREATE FUNCTION sp_missing_links() RETURNS TABLE(table_schema text, table_name text, id integer, reltab_schema text, reltab_name text, rid integer, table_record text, reltab_record text)
    LANGUAGE plpgsql
    AS $$
BEGIN

--osc Monitoringprogramma/bundel
--observing_capability_observation
--Monprog zonder waarnemingen.
RETURN QUERY SELECT 'public'::text, 'osc'::text, NULL::integer
, 'public'::text, 'oco'::text, NULL::integer
, 'osc - observing_capability zonder waarnemingen oco - observing_capability_observation'::text, NULL::text;
RETURN QUERY SELECT 'public'::text, 'osc'::text, osc.osc_id::integer
, 'public'::text, 'oco'::text, oco.ocbs_id::integer
, osc::text, oco::text
FROM observing_capability osc
LEFT JOIN observing_capability_observation oco
  ON osc.osc_id = oco.osc_id
WHERE oco.ocbs_id IS NULL
ORDER BY osc.osc_id;
--14

--observing_capability_observation
--Waarnemingen zonder monprog
RETURN QUERY SELECT 'public'::text, 'obs'::text, NULL::integer
, 'public'::text, 'oco'::text, NULL::integer
, 'obs - observation zonder waarnemingen oco - observing_capability_observation'::text, NULL::text;
RETURN QUERY SELECT 'public'::text, 'obs'::text, obs.obs_id::integer
, 'public'::text, 'oco'::text, oco.ocbs_id::integer
, obs::text, oco::text
FROM observation obs
LEFT JOIN observing_capability_observation oco
  ON obs.obs_id = oco.obs_id
WHERE oco.ocbs_id IS NULL
ORDER BY obs.obs_id;
--14


--observing_capability_responsible_party
--Monprog zonder uitvoerder.
RETURN QUERY SELECT 'public'::text, 'osc'::text, NULL::integer
, 'public'::text, 'ocrp'::text, NULL::integer
, 'osc - observing_capability zonder uitvoerder ocrp - observing_capability_responsible_party'::text, NULL::text;
RETURN QUERY SELECT 'public'::text, 'osc'::text, osc.osc_id::integer
, 'public'::text, 'ocrp'::text, ocrp.ocrp_id::integer
, osc::text, ocrp::text
FROM observing_capability osc
LEFT JOIN observing_capability_responsible_party ocrp 
  ON osc.osc_id = ocrp.osc_id
WHERE ocrp.ocrp_id IS NULL
ORDER BY osc.osc_id;
--4

--Organisatie/uitvoerder zonder Monprog
RETURN QUERY SELECT 'public'::text, 'ore'::text, NULL::integer
, 'public'::text, 'ocrp'::text, NULL::integer
, 'ore - organisatorisch eenheid zonder monprog ocrp - observing_capability_responsible_party'::text, NULL::text;
RETURN QUERY SELECT 'public'::text, 'ore'::text, ore.ore_id::integer
, 'public'::text, 'ocrp'::text, ocrp.ocrp_id::integer
, ore::text, ocrp::text
FROM organisatorische_eenheid ore
LEFT JOIN observing_capability_responsible_party ocrp 
  ON ore.ore_id = ocrp.ore_id
WHERE ocrp.ocrp_id IS NULL
ORDER BY ore.ore_id;
--4


--observing_capability_sample
--Monprog zonder monster.
RETURN QUERY SELECT 'public'::text, 'osc'::text, NULL::integer
, 'public'::text, 'ocs'::text, NULL::integer
, 'osc - observing_capability zonder monster ocs - observing_capability_sample'::text, NULL::text;
RETURN QUERY SELECT 'public'::text, 'osc'::text, osc.osc_id::integer
, 'public'::text, 'ocs'::text, ocs.ocsa_id::integer
, osc::text, ocs::text
FROM observing_capability osc
LEFT JOIN observing_capability_sample ocs
  ON osc.osc_id = ocs.osc_id
WHERE osc.is_sample = TRUE 
AND ocs.ocsa_id IS NULL
ORDER BY osc.osc_id;
--26

--qem
--quality_element_observation
--Monprog-regel zonder waarnemingen
RETURN QUERY SELECT 'public'::text, 'qem'::text, NULL::integer
, 'public'::text, 'qeo'::text, NULL::integer
, 'qem - quality_element (mp.regel) zonder waarnemingen qeo - quality_element_observation'::text, NULL::text;
RETURN QUERY SELECT 'public'::text, 'qem'::text, qem.qem_id::integer
, 'public'::text, 'qeo'::text, qeo.qeo_id::integer
, qem::text, qeo::text
FROM quality_element qem
LEFT JOIN quality_element_observation qeo
  ON qem.qem_id = qeo.qem_id
WHERE qeo.qeo_id IS NULL
ORDER BY qem.qem_id;
--142 maar dit kan

--quality_element_sample
--Monprog-regel zonder monster
RETURN QUERY SELECT 'public'::text, 'qem'::text, NULL::integer
, 'public'::text, 'qes'::text, NULL::integer
, 'qem - quality_element (mp.regel) zonder monster qes - quality_element_sample'::text, NULL::text;
RETURN QUERY SELECT 'public'::text, 'qem'::text, qem.qem_id::integer
, 'public'::text, 'qes'::text, qes.qes_id::integer
, qem::text, qes::text
FROM quality_element qem
JOIN observing_capability osc
  ON (qem.osc_id = osc.osc_id AND osc.is_sample = TRUE)
LEFT JOIN quality_element_sample qes
  ON qem.qem_id = qes.qem_id
WHERE qes.qes_id IS NULL
ORDER BY qem.qem_id;
--212 maar dit kan

--monprog.quality_element_indicator_descriptor
--Monprog-regel zonder indicator
RETURN QUERY SELECT 'public'::text, 'qem'::text, NULL::integer
, 'monprog'::text, 'qeid'::text, NULL::integer
, 'qem - quality_element (mp.regel) zonder monster qeid - quality_element_indicator_desrciptor'::text, NULL::text;
RETURN QUERY SELECT 'public'::text, 'qem'::text, qem.qem_id::integer
, 'monprog'::text, 'qeid'::text, NULL::integer
, qem::text, qeid::text
FROM quality_element qem
LEFT JOIN monprog.quality_element_indicator_descriptor qeid
  ON qem.qem_id = qeid.qem_id
WHERE qeid.qem_id IS NULL
ORDER BY qem.qem_id;
--0

--monitoring_object_quality_element
--Monprog-regel zonder meetlocatie
RETURN QUERY SELECT 'public'::text, 'qem'::text, NULL::integer
, 'public'::text, 'moqe'::text, NULL::integer
, 'qem - quality_element (mp.regel) zonder meetlocatie moqe - monitoring_object_quality_element'::text, NULL::text;
RETURN QUERY SELECT 'public'::text, 'qem'::text, qem.qem_id::integer
, 'public'::text, 'moqe'::text, moqe.moqe_id::integer
, qem::text, moqe::text
FROM quality_element qem
LEFT JOIN monitoring_object_quality_element moqe
  ON qem.qem_id = moqe.qem_id
WHERE moqe.qem_id IS NULL
ORDER BY qem.qem_id;
--0

--pot
--geen

--opt
--geen

--obj
--object_group_element
--Object zonder groep G
RETURN QUERY SELECT 'public'::text, 'obj'::text, NULL::integer
, 'public'::text, 'ogeG'::text, NULL::integer
, 'obj - object_type zonder groep G oge/ojg - object_group_element (ojg type G)'::text, NULL::text;
RETURN QUERY SELECT 'public'::text, 'obj'::text, obj.obj_id::integer
, 'public'::text, 'ogeG'::text, oge.ojg_id::integer
, obj::text, oge::text
FROM object_type obj
LEFT JOIN object_group_element oge
  ON obj.obj_id = oge.obj_id
LEFT JOIN object_group ojg
  ON (oge.ojg_id = ojg.ojg_id AND ojg.group_type = 'G')
WHERE oge.obj_id IS NULL
ORDER BY obj.obj_id;
--0

--Object zonder groep I
RETURN QUERY SELECT 'public'::text, 'obj'::text, NULL::integer
, 'public'::text, 'ogeI'::text, NULL::integer
, 'obj - object_type zonder groep I oge/ojg - object_group_element (ojg type I)'::text, NULL::text;
RETURN QUERY SELECT 'public'::text, 'obj'::text, obj.obj_id::integer
, 'public'::text, 'ogeI'::text, oge.ojg_id::integer
, obj::text, oge.ojg_id::text
FROM object_type obj
LEFT JOIN object_group_element oge
  ON obj.obj_id = oge.obj_id
LEFT JOIN object_group ojg
  ON (oge.ojg_id = ojg.ojg_id AND ojg.group_type = 'I')
WHERE oge.obj_id IS NULL
ORDER BY obj.obj_id;
--0

--ojg zonder element
RETURN QUERY SELECT 'public'::text, 'ojg'::text, NULL::integer
, 'public'::text, 'oge'::text, NULL::integer
, 'obj - object_group zonder oge - object_group_element (oge)'::text, NULL::text;
RETURN QUERY SELECT 'public'::text, 'ojg'::text, ojg.ojg_id::integer
, 'public'::text, 'oge'::text, oge.ojg_id::integer
, ojg::text, oge::text
FROM object_group ojg
LEFT JOIN object_group_element oge
  ON ojg.ojg_id = oge.ojg_id
WHERE oge.ojg_id IS NULL
ORDER BY ojg.ojg_id;
--0

--chs
--substance_group_element
--Chemische stof zonder groep G
RETURN QUERY SELECT 'public'::text, 'chs'::text, NULL::integer
, 'public'::text, 'ssgeG'::text, NULL::integer
, 'chs - chemische_stof_type zonder groep G ssge/ssg - substance_group_element (type G)'::text, NULL::text;
RETURN QUERY SELECT 'public'::text, 'chs'::text, ssge.chs_id::integer
, 'public'::text, 'ssgeG'::text, ssge.ssge_id::integer
, chs::text, ssge::text
FROM chemische_stof_type chs
LEFT JOIN substance_group_element ssge
  ON chs.chs_id = ssge.chs_id
LEFT JOIN substance_group ssg
  --ssge has wrong column name ssge_id, should be ssg_id without e)
  ON (ssge.ssge_id = ssg.ssg_id AND ssg.group_type = 'G')
WHERE ssge.chs_id IS NULL
ORDER BY chs.chs_id;
--0

--Chemische stof zonder groep I
RETURN QUERY SELECT 'public'::text, 'chs'::text, NULL::integer
, 'public'::text, 'ssgeI'::text, NULL::integer
, 'chs - chemische_stof_type zonder groep I ssge/ssg - substance_group_element (type I)'::text, NULL::text;
RETURN QUERY SELECT 'public'::text, 'chs'::text, ssge.chs_id::integer
, 'public'::text, 'ssgeI'::text, ssge.ssge_id::integer
, chs::text, ssge::text
FROM chemische_stof_type chs
LEFT JOIN substance_group_element ssge
  ON chs.chs_id = ssge.chs_id
LEFT JOIN substance_group ssg
  --ssge has wrong column name ssge_id, should be ssg_id without e)
  ON (ssge.ssge_id = ssg.ssg_id AND ssg.group_type = 'I')
WHERE ssge.chs_id IS NULL
ORDER BY chs.chs_id;
--0

--ssg
--substance_group_element
--groep chemische stof zonder element
RETURN QUERY SELECT 'public'::text, 'ssg'::text, NULL::integer
, 'public'::text, 'ssge'::text, NULL::integer
, 'ssg - substance_group zonder element ssge - substance_group_element'::text, NULL::text;
RETURN QUERY SELECT 'public'::text, 'ssg'::text, ssg.ssg_id::integer
, 'public'::text, 'ssge'::text, ssge.ssge_id::integer
, ssg::text, ssge::text 
FROM substance_group ssg
LEFT JOIN substance_group_element ssge
  --ssge has wrong column name ssge_id, should be ssg_id without e)
  ON ssg.ssg_id = ssge.ssge_id
WHERE ssge.ssge_id IS NULL
ORDER BY ssg.ssg_id;
--0

--txn
--taxa_group_element
--Taxa zonder groep G
RETURN QUERY SELECT 'public'::text, 'txn'::text, NULL::integer
, 'public'::text, 'tgeG'::text, NULL::integer
, 'txn - taxon_type zonder groep G tge/txg - taxa_group_element (type G)'::text, NULL::text;
RETURN QUERY SELECT 'public'::text, 'txn'::text, txn.txn_id::integer
, 'public'::text, 'tgeG'::text, tge.txg_id::integer
, txn::text, tge::text 
FROM taxon_type txn
LEFT JOIN taxa_group_element tge
  ON txn.txn_id = tge.txn_id
LEFT JOIN taxa_group txg
  ON (tge.txg_id = txg.txg_id AND txg.group_type = 'G')
WHERE tge.txn_id IS NULL
ORDER BY txn.txn_id;
--313

--Taxa zonder groep I
RETURN QUERY SELECT 'public'::text, 'txn'::text, NULL::integer
, 'public'::text, 'tgeI'::text, NULL::integer
, 'txn - taxon_type zonder groep I tge/txg - taxa_group_element (type I)'::text, NULL::text;
RETURN QUERY SELECT 'public'::text, 'txn'::text, txn.txn_id::integer
, 'public'::text, 'tgeI'::text, tge.txg_id::integer
, txn::text, tge::text
FROM taxon_type txn
LEFT JOIN taxa_group_element tge
  ON txn.txn_id = tge.txn_id
LEFT JOIN taxa_group txg
  ON (tge.txg_id = txg.txg_id AND txg.group_type = 'I')
WHERE tge.txn_id IS NULL
ORDER BY txn.txn_id;
--313

--txg
--taxa_group_element
--Taxagroep zonder element.
RETURN QUERY SELECT 'public'::text, 'txg'::text, NULL::integer
, 'public'::text, 'tge'::text, NULL::integer
, 'txg - taxon_group zonder element - taxa_group_element'::text, NULL::text;
RETURN QUERY SELECT 'public'::text, 'txg'::text, txg.txg_id::integer
, 'public'::text, 'tge'::text, tge.txg_id::integer
, txg::text, tge::text 
FROM taxa_group txg
LEFT JOIN taxa_group_element tge
  ON txg.txg_id = tge.txg_id
WHERE tge.txg_id IS NULL
ORDER BY txg.txg_id;
--73

--smp
--zie qem

--obs
--zie qem

--mno
--zie qem

--Groepen G waarvan er elementen zijn zonder groep I.
--Groepen I waarvan het element niet in een groep G zit.

--objecten in G-groep, zonder I-groep
RETURN QUERY SELECT 'public'::text, 'ojgG'::text, NULL::integer
, 'public'::text, 'ogeI'::text, NULL::integer
, 'ojgG - object_group (type G) zonder groep I ogeI - object_group_element (type I)'::text, NULL::text;
RETURN QUERY SELECT 'public'::text, 'ojgG'::text, ojgG.ojg_id::integer
, 'public'::text, 'ogeI'::text, ogeI.ojg_id::integer 
, ojgG::text, ogeI::text 
FROM object_group_element as ogeG
JOIN object_group as ojgG              ON (ojgG.ojg_id = ogeG.ojg_id AND ojgG.group_type = 'G')
LEFT JOIN object_group_element as ogeI ON (ogeI.obj_id = ogeG.obj_id)
JOIN object_group ojgI                 ON (ojgI.ojg_id = ogeI.ojg_id AND ojgI.group_type = 'I')
WHERE ogeI.ojg_id IS NULL 
ORDER BY ojgG.ojg_id;
--0

--objecten in I-groep, zonder G-groep. 
--Niet per se onnuttig, ze kunnen ook als I-groep in een opt.
RETURN QUERY SELECT 'public'::text, 'ojgI'::text, NULL::integer
, 'public'::text, 'ogeG'::text, NULL::integer
, 'ojgI - object_group (type I) zonder groep G ogeG - object_group_element (type G)'::text, NULL::text;
RETURN QUERY SELECT 'public'::text, 'ojgI'::text, ojgI.ojg_id::integer
, 'public'::text, 'ogeG'::text, ogeG.ojg_id::integer 
, ojgI::text, ogeG::text
FROM object_group_element as ogeI
JOIN object_group as ojgI              ON (ojgI.ojg_id = ogeI.ojg_id AND ojgI.group_type = 'I')
LEFT JOIN object_group_element as ogeG ON (ogeG.obj_id = ogeI.obj_id)
JOIN object_group ojgG                 ON (ojgG.ojg_id = ogeG.ojg_id AND ojgG.group_type = 'G')
WHERE ogeG.ojg_id IS NULL 
ORDER BY ojgI.ojg_id;
--0

--chemische stoffen in G-groep, zonder I-groep
RETURN QUERY SELECT 'public'::text, 'ssgG'::text, NULL::integer
, 'public'::text, 'ssgeI'::text, NULL::integer
, 'ssgG - substance_group (type G) zonder groep I ssgeI - substance_group_element (type I)'::text, NULL::text;
RETURN QUERY SELECT 'public'::text, 'ssgG'::text, ssgG.ssg_id::integer
, 'public'::text, 'ssgeI'::text, ssgeI.ssge_id::integer
, ssgG::text, ssgeI::text
FROM substance_group_element as ssgeG
JOIN substance_group as ssgG               ON (ssgG.ssg_id = ssgeG.ssge_id AND ssgG.group_type = 'G')
LEFT JOIN substance_group_element as ssgeI ON (ssgeI.chs_id = ssgeG.chs_id)
JOIN substance_group ssgI                  ON (ssgI.ssg_id = ssgeI.ssge_id AND ssgI.group_type = 'I')
--ssge has wrong foreign key ssge_id, should be ssg_id (without e)
WHERE ssgeI.ssge_id IS NULL 
ORDER BY ssgG.ssg_id;
--0

--chemische stoffen in I-groep, zonder G-groep. 
--Niet per se onnuttig, ze kunnen ook als I-groep in een opt.
RETURN QUERY SELECT 'public'::text, 'ssgI'::text, NULL::integer
, 'public'::text, 'ssgeG'::text, NULL::integer
, 'ssgG - substance_group (type G) zonder groep I ssgeI - substance_group_element (type G)'::text, NULL::text;
RETURN QUERY SELECT 'public'::text, 'ssgI'::text, ssgI.ssg_id::integer
, 'public'::text, 'ssgeG'::text, ssgG.ssg_id::integer
, ssgI::text, ssgG::text
FROM substance_group_element as ssgeI
JOIN substance_group as ssgI               ON (ssgI.ssg_id = ssgeI.ssge_id AND ssgI.group_type = 'I')
LEFT JOIN substance_group_element as ssgeG ON (ssgeG.chs_id = ssgeI.chs_id)
JOIN substance_group ssgG                  ON (ssgG.ssg_id = ssgeG.ssge_id AND ssgG.group_type = 'G')
WHERE ssgG.ssg_id IS NULL 
ORDER BY ssgI.ssg_id;
--0

--taxons in G-groep, zonder I-groep
--RETURN QUERY SELECT 'public'::text, 'txgG'::text, NULL::integer, 'public'::text, 'tgeI'::text,
-- 'txgG - taxa_group (type G) zonder groep I tgeI - taxa_group_element (type I)'::text;
--RETURN QUERY SELECT 'public'::text, 'txgG'::text, NULL::integer, 'public'::text, 'tgeI'::text,
-- 'De KRM- en LEW-importfuncties kunnen deze G groepen niet importeren.'::text;
RETURN QUERY SELECT 'public'::text, 'txgG'::text, NULL::integer
, 'public'::text, 'tgeI'::text, NULL::integer
, 'txgG - taxa_group (type G) zonder groep I tgeI - taxa_group_element (type I)'::text, NULL::text;
RETURN QUERY SELECT 'public'::text, 'txgG'::text, txgG.txg_id::integer
, 'public'::text, 'tgeI'::text, tgeI.txg_id::integer
, txgG::text, tgeI::text
FROM taxa_group_element as tgeG
JOIN taxa_group as txgG              ON (txgG.txg_id = tgeG.txg_id AND txgG.group_type = 'G')
LEFT JOIN taxa_group_element as tgeI ON tgeI.txn_id = tgeG.txn_id
JOIN taxa_group txgI                 ON (txgI.txg_id = tgeI.txg_id AND txgI.group_type = 'I')
WHERE txgI.txg_id IS NULL 
ORDER BY txgG.txg_id;
--0

--taxons in I-groep, zonder G-groep. 
--Niet per se onnuttig, taxons kunnen ook als I-groep in een opt.
RETURN QUERY SELECT 'public'::text, 'txgI'::text, NULL::integer
, 'public'::text, 'tgeG'::text, NULL::integer
, 'txgI - taxa_group (type I) zonder groep G tgeG - taxa_group_element (type G)'::text, NULL::text;
RETURN QUERY SELECT 'public'::text, 'txgI'::text, txgI.txg_id::integer
, 'public'::text, 'tgeG'::text, tgeG.txg_id::integer
, txgI::text, txgG::text
FROM taxa_group_element as tgeI
JOIN taxa_group as txgI              ON (txgI.txg_id = tgeI.txg_id AND txgI.group_type = 'I')
LEFT JOIN taxa_group_element as tgeG ON tgeG.txn_id = tgeI.txn_id
JOIN taxa_group txgG                 ON (txgG.txg_id = tgeG.txg_id AND txgG.group_type = 'G')
WHERE txgG.txg_id IS NULL 
ORDER BY txgI.txg_id;
--0


--Groepen G zonder opt
--Groepen I zonder opt
--niet gemaakt -- not implemented

--Groepen G waarvan er elementen zijn met ontbrekende opt's (via hun groep I).
--Groepen I waarvan het element in een groep G zit met ontbrekende opt's.

--controleer of voor een taxa_group type G, er element's zijn met missende hoedanigheid en/of grootheid
--this one is slow
RETURN QUERY SELECT 'public'::text, 'txgG'::text, NULL::integer
, 'public'::text, 'txgI'::text, NULL::integer
, 'taxa_group (type G) met missende opt voor gerelateerde taxa_group I'::text, NULL::text;
RETURN QUERY SELECT 'public'::text, 'txgG'::text, txgG.txg_id::integer
, 'public'::text, 'txgI'::text, txgI.txg_id::integer
, txgG::text, txgI::text
FROM observed_property_type optG
JOIN taxa_group_element as tgeG       ON (tgeG.txg_id = optG.taxa_group_id)
JOIN taxa_group as txgG               ON (txgG.txg_id = optG.taxa_group_id AND txgG.group_type = 'G')
JOIN taxa_group_element as tgeI       ON (tgeI.txn_id = tgeG.txn_id AND tgeI.txg_id <> tgeG.txg_id)
JOIN taxa_group txgI                  ON (txgI.txg_id = tgeI.txg_id AND txgI.group_type = 'I')
LEFT JOIN observed_property_type optI 
  ON (optI.taxa_group_id = txgI.txg_id 
     AND (  (optI.observed_property_type_class IS NULL AND optG.observed_property_type_class IS NULL) 
         OR optI.observed_property_type_class = optG.observed_property_type_class
         )
     AND (  (optI.result_type IS NULL AND optG.result_type IS NULL) 
         OR optI.result_type = optG.result_type
         )
     AND (  (optI.hoedanigheid_id IS NULL AND optG.hoedanigheid_id IS NULL) 
         OR optI.hoedanigheid_id = optG.hoedanigheid_id
         )
     AND (  (optI.quantity_id IS NULL AND optG.quantity_id IS NULL) 
         OR optI.quantity_id = optG.quantity_id
         )
     )
WHERE optI.opt_id IS NULL 
--AND optG.taxa_group_id = 26500
ORDER BY txgG.txg_id;
--1: Benthos Noordzee en Crassostrea gigas

--andersom: I-groepen die onnutte koppelingen hebben: 
--bijbehorende G-groep zonder opt, of met opt met afwijkende grootheid en/of hoedanigheid
--alleen G en I omgewisseld
--Op 18 juni 2016:
--  745 x afwijkende grootheid = 2977 (deze komen in scherm monprog-reg voor onder 2977=AANTL)
--  en 1 x 3076
RETURN QUERY SELECT 'public'::text, 'txgG'::text, NULL::integer
, 'public'::text, 'txgI'::text, NULL::integer
, 'taxa_group (type I) met missende opt voor gerelateerde taxa_group G'::text, NULL::text;
RETURN QUERY SELECT 'public'::text, 'txgG'::text, txgG.txg_id::integer
, 'public'::text, 'txgI'::text, txgI.txg_id::integer
, txgG::text, txgI::text
FROM observed_property_type optI
JOIN taxa_group_element as tgeI      ON (tgeI.txg_id = optI.taxa_group_id)
JOIN taxa_group as txgI              ON (txgI.txg_id = optI.taxa_group_id AND txgI.group_type = 'I')
JOIN taxa_group_element as tgeG      ON (tgeG.txn_id = tgeI.txn_id AND tgeI.txg_id <> tgeG.txg_id)
JOIN taxa_group txgG                 ON (txgG.txg_id = tgeG.txg_id AND txgG.group_type = 'G')
LEFT JOIN observed_property_type optG  
  ON (optG.taxa_group_id = txgG.txg_id 
     AND (  (optI.observed_property_type_class IS NULL AND optG.observed_property_type_class IS NULL) 
         OR optI.observed_property_type_class = optG.observed_property_type_class
         )
     AND (  (optI.result_type IS NULL AND optG.result_type IS NULL) 
         OR optI.result_type = optG.result_type
         )
     AND (  (optI.hoedanigheid_id IS NULL AND optG.hoedanigheid_id IS NULL) 
         OR optI.hoedanigheid_id = optG.hoedanigheid_id
         )
     AND (  (optI.quantity_id IS NULL AND optG.quantity_id IS NULL) 
         OR optI.quantity_id = optG.quantity_id
         )
     )
WHERE optG.opt_id IS NULL 
ORDER BY txgI.txg_id;




END
$$;


--
-- Name: test123(integer, integer); Type: FUNCTION; Schema: beheer; Owner: -
--

CREATE FUNCTION test123(min integer, max integer) RETURNS integer
    LANGUAGE plpgsql
    AS $$
DECLARE
   cnt integer;
BEGIN
    FOR i IN min .. max
    LOOP
      WITH a AS (
           DELETE
           FROM observation obs
           WHERE date_trunc('minute', obs.last_change_time) = make_timestamp(2016, 11, 3, 18, i, 0)
           RETURNING 1
           ) 
      SELECT count(*) INTO cnt FROM a;
      RAISE NOTICE 'minuut %: %', i, cnt;
    END LOOP;
    RETURN 0;
END;
$$;



--
-- Name: aanmaken_observed_property_type_bio(); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION aanmaken_observed_property_type_bio() RETURNS character varying
    LANGUAGE plpgsql
    AS $$
DECLARE
  c_rij cursor FOR SELECT * FROM public._vw_aquo_kit_voor_opt_id ORDER BY tgr_name; 
  v_aantal integer;
  v_teller_totaal integer:= 0;
  v_teller_nieuw integer:= 0;
  v_teller_bestaand integer:= 0;
  v_currval integer;
  v_hoedanigheid hoedanigheid_type.hdh_id%TYPE;
  v_grootheid grootheid_type.grh_id%TYPE;
BEGIN
  FOR r_rij IN c_rij 
  LOOP
    v_teller_totaal :=  v_teller_totaal + 1;
    SELECT ophalen_hoedanigheid_type_id(r_rij.aoi_hoedanigheid_code) INTO v_hoedanigheid;
    SELECT ophalen_grootheid_type_id(r_rij.aoi_grootheid_code) INTO v_grootheid;
    IF v_hoedanigheid IS NULL
    THEN
      RAISE NOTICE 'Hoedanigheid % bestaat niet.',r_rij.aoi_hoedanigheid_code;
    ELSIF v_grootheid IS NULL
    THEN
      RAISE NOTICE 'Grootheid % bestaat niet.',r_rij.aoi_grootheid_code;
    ELSE
      SELECT COUNT(*) INTO v_aantal 
        FROM observed_property_type 
       WHERE taxa_group_id   = r_rij.tgr_txg_id
        AND hoedanigheid_id  = ophalen_hoedanigheid_type_id(r_rij.aoi_hoedanigheid_code)
        AND quantity_id      = ophalen_grootheid_type_id(r_rij.aoi_grootheid_code);  
      IF v_aantal = 0
      THEN
        v_teller_nieuw := v_teller_nieuw + 1;
       --RAISE NOTICE 'Biotaxon % heeft nog geen Observed Property voor hoedanigheid % en grootheid %. Daarom toevoegen.',
       --  	    r_rij.tgr_name,r_rij.aoi_hoedanigheid_code,r_rij.aoi_grootheid_code;
        INSERT INTO public.observed_property_type(opt_id, observed_property_type_class, result_type, hoedanigheid_id, 
						object_group_id, quantity_id, taxa_group_id, substance_group_id, 
						indicator_id, classification_group_id, remarks, d_begin, d_eind,last_changed_date, d_status)
        VALUES (nextval('observed_property_type_opt_id_seq'), 'BioObservation', 'MeasureResult', ophalen_hoedanigheid_type_id(r_rij.aoi_hoedanigheid_code ), 
                NULL, ophalen_grootheid_type_id(r_rij.aoi_grootheid_code), r_rij.tgr_txg_id, NULL, 
	        NULL, NULL, 'Implementatie Biologische gegevens WKP 2016', now(), NULL, now(), 'Gepubliceerd');
        v_currval := currval('observed_property_type_opt_id_seq');
        RAISE NOTICE 'Biotaxon % met txg_id % en hoedanigheid % en grootheid % is toegevoegd met opt_id %.',
		    r_rij.tgr_name,r_rij.tgr_txg_id,r_rij.aoi_hoedanigheid_code,r_rij.aoi_grootheid_code,v_currval;
      ELSE
        v_teller_bestaand := v_teller_bestaand + 1;
        RAISE NOTICE 'Aantal van % gevonden. Biotaxon % heeft reeds Observed Property voor hoedanigheid % en grootheid %.',
		      v_aantal, r_rij.tgr_name, r_rij.aoi_hoedanigheid_code, r_rij.aoi_grootheid_code;
      END IF;
    END IF;
  END LOOP;
  RAISE NOTICE 'Er zijn in totaal % Observed Properties gecheckt waarvan % nieuw en % reeds bestaand.',v_teller_totaal,v_teller_nieuw,v_teller_bestaand;
  RAISE NOTICE 'Er zijn dus % nieuwe Observed Properties toegevoegd.',v_teller_nieuw;
  RAISE NOTICE 'Er zijn dus % reeds bestaande Observed Properties gevonden.',v_teller_bestaand;
   RETURN 'Procedure is klaar';
END;
$$;


--
-- Name: copy_bronnen(); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION copy_bronnen() RETURNS integer
    LANGUAGE plpgsql
    AS $$
DECLARE
hydroobjects RECORD;
bronrecord RECORD;
num integer;
hdoid integer;
BEGIN
num := 0;
FOR hydroobjects IN
-- nu ingesteld om wkrv_id 6 naar wkrv_id 4 te kopieren
select h.inspire_id_local_id, h.hdo_id hdoold, vw.db_id_v4 hdoNew, bron.chs_id, bron.wbpm_id, bron.eenh_id, bron.year, bron.is_significant_wbm, bron.is_significant_factsheet 
from hydro_object h
join bronnen bron on bron.hdo_id = h.hdo_id 
join vw_hydro_object_was_wordt_versies_geometrisch vw on vw.db_id_v6 = h.hdo_id
where h.wkrv_id = 6 AND h.hydro_object_type = 'SurfaceWaterBody'
LOOP
	
	RAISE NOTICE 'hdo_id %', hydroobjects.hdoNew;
	EXECUTE 'INSERT INTO public.bronnen(
		    hdo_id, chs_id, wbpm_id, eenh_id, year, is_significant_wbm, 
		    is_significant_factsheet)
	    VALUES (' || hydroobjects.hdoNew || ' ,' ||  hydroobjects.chs_id ||' ,' || hydroobjects.wbpm_id ||' ,' || hydroobjects.eenh_id ||' ,' ||
		hydroobjects.year ||' ,' || hydroobjects.is_significant_wbm ||' ,' || hydroobjects.is_significant_factsheet || ')';
	num := num + 1;
END LOOP;
RETURN num;
END;
$$;


--
-- Name: count_rows(text, text); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION count_rows(schema text, tablename text) RETURNS integer
    LANGUAGE plpgsql
    AS $$
declare
  result integer;
  query varchar;
begin
  query := 'SELECT count(1) FROM ' || chr(34) || schema || chr(34) || 
  '.' || chr(34) || tablename || chr(34);
  execute query into result;
  return result;
end;
$$;


--
-- Name: fn_fixsequences(); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION fn_fixsequences() RETURNS integer
    LANGUAGE plpgsql
    AS $$
DECLARE
themax BIGINT;
mytables RECORD;
num integer;
BEGIN
num := 0;
FOR mytables IN
 select relname, ns.nspname, a.attname,a.attnum, a.attisdropped,a.atttypid, CASE WHEN c.relname ~ '[[:upper:]]'
THEN pg_get_serial_sequence('"'::text || c.relname || '"'::text, a.attname)
ELSE pg_get_serial_sequence(c.relname, a.attname)
END  as seq
FROM pg_catalog.pg_attribute a
INNER JOIN pg_catalog.pg_class c ON c.oid=a.attrelid
inner join pg_catalog.pg_attrdef d on d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef
LEFT JOIN pg_catalog.pg_namespace ns ON ns.oid = c.relnamespace

 WHERE
   pg_catalog.pg_table_is_visible(c.oid)
  and a.attnum > 0 AND NOT (a.attisdropped and atttypid=20)
  and relname not like 'pg_%'
  and ns.nspname not in ('information_schema', 'pg_catalog', 'topology')
  and c.relkind='r'
  and not CASE WHEN c.relname ~ '[[:upper:]]'
THEN pg_get_serial_sequence('"'::text || c.relname || '"'::text, a.attname)
ELSE pg_get_serial_sequence(c.relname, a.attname)
END is null
LOOP
     EXECUTE 'SELECT MAX('||mytables.attname||') FROM '||mytables.nspname||'."'||mytables.relname||'";' INTO themax;
     IF (themax is null OR themax < 0) THEN
      themax := 0;
     END IF;
     themax := themax +1;
     EXECUTE 'ALTER SEQUENCE ' || mytables.seq || ' RESTART WITH '||themax;
     num := num + 1;
 END LOOP;

 RETURN num;

END;
$$;


--
-- Name: missing_links(); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION missing_links() RETURNS TABLE(table_schema text, table_name text, id integer, reltab_schema text, reltab_name text, table_record text)
    LANGUAGE plpgsql
    AS $$
BEGIN

--osc Monitoringprogramma/bundel
--observing_capability_observation
--Monprog zonder waarnemingen.
RETURN QUERY SELECT 'public'::text, 'osc'::text, NULL::integer, 'public'::text, 'oco'::text
, 'osc - observing_capability (monprog) zonder waarnemingen oco - observing_capability_observation'::text;
RETURN QUERY SELECT 'public'::text, 'osc'::text, osc.osc_id::integer, 'public'::text, 'oco'::text
, osc::text
FROM observing_capability osc
LEFT JOIN observing_capability_observation oco
  ON osc.osc_id = oco.osc_id
WHERE oco.ocbs_id IS NULL
ORDER BY osc.osc_id;
--14

--observing_capability_responsible_party
--Monprog zonder uitvoerder.
RETURN QUERY SELECT 'public'::text, 'osc'::text, NULL::integer, 'public'::text, 'ocrp'::text
, 'osc - observing_capability (monprog) zonder uitvoerder ocrp - observing_capability_responsible_party'::text;
RETURN QUERY SELECT 'public'::text, 'osc'::text, osc.osc_id::integer, 'public'::text, 'ocrp'::text
, osc.*::text
FROM observing_capability osc
LEFT JOIN observing_capability_responsible_party ocrp 
  ON osc.osc_id = ocrp.osc_id
WHERE ocrp.ocrp_id IS NULL
ORDER BY osc.osc_id;
--4

--observing_capability_sample
--Monprog zonder monster.
RETURN QUERY SELECT 'public'::text, 'osc'::text, NULL::integer, 'public'::text, 'ocs'::text,
'osc - observing_capability (monprog) zonder monster ocs - observing_capability_sample'::text;
RETURN QUERY SELECT 'public'::text, 'osc'::text, osc.osc_id::integer, 'public'::text, 'ocs'::text,
osc.*::text
FROM observing_capability osc
LEFT JOIN observing_capability_sample ocs
  ON osc.osc_id = ocs.osc_id
WHERE osc.is_sample = TRUE 
AND ocs.ocsa_id IS NULL
ORDER BY osc.osc_id;
--26

--qem
--quality_element_observation
--Monprog-regel zonder waarnemingen
RETURN QUERY SELECT 'public'::text, 'qem'::text, NULL::integer, 'public'::text, 'qeo'::text,
 'qem - quality_element (mp.regel) zonder waarnemingen qeo - quality_element_observation'::text;
RETURN QUERY SELECT 'public'::text, 'qem'::text, qem.qem_id::integer, 'public'::text, 'qeo'::text,
 qem.*::text
FROM quality_element qem
LEFT JOIN quality_element_observation qeo
  ON qem.qem_id = qeo.qem_id
WHERE qeo.qeo_id IS NULL
ORDER BY qem.qem_id;
--142 maar dit kan

--quality_element_sample
--Monprog-regel zonder monster
RETURN QUERY SELECT 'public'::text, 'qem'::text, NULL::integer, 'public'::text, 'qes'::text,
 'qem - quality_element (mp.regel) zonder monster qes - quality_element_sample'::text;
RETURN QUERY SELECT 'public'::text, 'qem'::text, qem.qem_id::integer, 'public'::text, 'qes'::text,
 qem.*::text
FROM quality_element qem
JOIN observing_capability osc
  ON (qem.osc_id = osc.osc_id AND osc.is_sample = TRUE)
LEFT JOIN quality_element_sample qes
  ON qem.qem_id = qes.qem_id
WHERE qes.qes_id IS NULL
ORDER BY qem.qem_id;
--212 maar dit kan

--monprog.quality_element_indicator_descriptor
--Monprog-regel zonder indicator
RETURN QUERY SELECT 'public'::text, 'qem'::text, NULL::integer, 'monprog'::text, 'qeid'::text,
 'qem - quality_element (mp.regel) zonder monster qeid - quality_element_indicator_desrciptor'::text;
RETURN QUERY SELECT 'public'::text, 'qem'::text, qem.qem_id::integer, 'monprog'::text, 'qeid'::text,
 qem.*::text
FROM quality_element qem
LEFT JOIN monprog.quality_element_indicator_descriptor qeid
  ON qem.qem_id = qeid.qem_id
WHERE qeid.qem_id IS NULL
ORDER BY qem.qem_id;
--0

--monitoring_object_quality_element
--Monprog-regel zonder meetlocatie
RETURN QUERY SELECT 'public'::text, 'qem'::text, NULL::integer, 'public'::text, 'moqe'::text,
 'qem - quality_element (mp.regel) zonder monster moqe - monitoring_object_quality_element'::text;
RETURN QUERY SELECT 'public'::text, 'qem'::text, qem.qem_id::integer, 'public'::text, 'moqe'::text,
 qem.*::text
FROM quality_element qem
LEFT JOIN monitoring_object_quality_element moqe
  ON qem.qem_id = moqe.qem_id
WHERE moqe.qem_id IS NULL
ORDER BY qem.qem_id;
--0

--pot
--geen

--opt
--geen

--obj
--object_group_element
--Object zonder groep G
RETURN QUERY SELECT 'public'::text, 'obj'::text, NULL::integer, 'public'::text, 'ogeG'::text,
 'obj - object_type (object) zonder groep G oge/ojg - object_group_element (ojg type G)'::text;
RETURN QUERY SELECT 'public'::text, 'obj'::text, obj.obj_id::integer, 'public'::text, 'ogeG'::text,
 obj.*::text
FROM object_type obj
LEFT JOIN object_group_element oge
  ON obj.obj_id = oge.obj_id
LEFT JOIN object_group ojg
  ON (oge.ojg_id = ojg.ojg_id AND ojg.group_type = 'G')
WHERE oge.obj_id IS NULL
ORDER BY obj.obj_id;
--0

--Object zonder groep I
RETURN QUERY SELECT 'public'::text, 'obj'::text, NULL::integer, 'public'::text, 'ogeI'::text,
 'obj - object_type (object) zonder groep I oge/ojg - object_group_element (ojg type I)'::text;
RETURN QUERY SELECT 'public'::text, 'obj'::text, obj.obj_id::integer, 'public'::text, 'ogeI'::text,
 obj.*::text
FROM object_type obj
LEFT JOIN object_group_element oge
  ON obj.obj_id = oge.obj_id
LEFT JOIN object_group ojg
  ON (oge.ojg_id = ojg.ojg_id AND ojg.group_type = 'I')
WHERE oge.obj_id IS NULL
ORDER BY obj.obj_id;
--0

--ojg zonder element
RETURN QUERY SELECT 'public'::text, 'ojg'::text, NULL::integer, 'public'::text, 'oge'::text,
 'obj - object_group (object_group) zonder oge - object_group_element (oge)'::text;
RETURN QUERY SELECT 'public'::text, 'ojg'::text, oge.obj_id::integer, 'public'::text, 'ogeI'::text,
 ojg.*::text
FROM object_group ojg
LEFT JOIN object_group_element oge
  ON ojg.ojg_id = oge.ojg_id
WHERE oge.ojg_id IS NULL
ORDER BY ojg.ojg_id;
--0

--chs
--substance_group_element
--Chemische stof zonder groep G
RETURN QUERY SELECT 'public'::text, 'chs'::text, NULL::integer, 'public'::text, 'ssge'::text,
 'chs - chemische_stof_type (chemische stof) zonder groep G ssge/ssg - substance_group_element (type G)'::text;
RETURN QUERY SELECT 'public'::text, 'chs'::text, ssge.chs_id::integer, 'public'::text, 'ssgeG'::text,
 chs.*::text
FROM chemische_stof_type chs
LEFT JOIN substance_group_element ssge
  ON chs.chs_id = ssge.chs_id
LEFT JOIN substance_group ssg
  --ssge has wrong column name ssge_id, should be ssg_id without e)
  ON (ssge.ssge_id = ssg.ssg_id AND ssg.group_type = 'G')
WHERE ssge.chs_id IS NULL
ORDER BY chs.chs_id;
--0

--Chemische stof zonder groep I
RETURN QUERY SELECT 'public'::text, 'chs'::text, NULL::integer, 'public'::text, 'ssge'::text,
 'chs - chemische_stof_type (chemische stof) zonder groep I ssge/ssg - substance_group_element (type I)'::text;
RETURN QUERY SELECT 'public'::text, 'chs'::text, ssge.chs_id::integer, 'public'::text, 'ssgeG'::text,
 chs.*::text
FROM chemische_stof_type chs
LEFT JOIN substance_group_element ssge
  ON chs.chs_id = ssge.chs_id
LEFT JOIN substance_group ssg
  --ssge has wrong column name ssge_id, should be ssg_id without e)
  ON (ssge.ssge_id = ssg.ssg_id AND ssg.group_type = 'I')
WHERE ssge.chs_id IS NULL
ORDER BY chs.chs_id;
--0

--ssg
--substance_group_element
--groep chemische stof zonder element
RETURN QUERY SELECT 'public'::text, 'ssg'::text, NULL::integer, 'public'::text, 'ssge'::text,
 'ssg - substance_group (groep chemische stof) zonder element ssge - substance_group_element'::text;
RETURN QUERY SELECT 'public'::text, 'ssg'::text, ssg.ssg_id::integer, 'public'::text, 'ssge'::text,
 ssg.*::text
FROM substance_group ssg
LEFT JOIN substance_group_element ssge
  --ssge has wrong column name ssge_id, should be ssg_id without e)
  ON ssg.ssg_id = ssge.ssge_id
WHERE ssge.ssge_id IS NULL
ORDER BY ssg.ssg_id;
--0

--txn
--taxa_group_element
--Taxa zonder groep G
RETURN QUERY SELECT 'public'::text, 'txn'::text, NULL::integer, 'public'::text, 'tge'::text,
 'txn - taxon_type (taxon) zonder groep G tge/txg - taxa_group_element (type G)'::text;
RETURN QUERY SELECT 'public'::text, 'txn'::text, txn.txn_id::integer, 'public'::text, 'tge'::text,
 txn.*::text
FROM taxon_type txn
LEFT JOIN taxa_group_element tge
  ON txn.txn_id = tge.txn_id
LEFT JOIN taxa_group txg
  ON (tge.txg_id = txg.txg_id AND txg.group_type = 'G')
WHERE tge.txn_id IS NULL
ORDER BY txn.txn_id;
--313

--Taxa zonder groep I
RETURN QUERY SELECT 'public'::text, 'txn'::text, NULL::integer, 'public'::text, 'tge'::text,
 'txn - taxon_type (taxon) zonder groep I tge/txg - taxa_group_element (type I)'::text;
RETURN QUERY SELECT 'public'::text, 'txn'::text, txn.txn_id::integer, 'public'::text, 'tge'::text,
 txn.*::text
FROM taxon_type txn
LEFT JOIN taxa_group_element tge
  ON txn.txn_id = tge.txn_id
LEFT JOIN taxa_group txg
  ON (tge.txg_id = txg.txg_id AND txg.group_type = 'I')
WHERE tge.txn_id IS NULL
ORDER BY txn.txn_id;
--313

--txg
--taxa_group_element
--Taxagroep zonder element.
RETURN QUERY SELECT 'public'::text, 'txg'::text, NULL::integer, 'public'::text, 'tge'::text,
 'txg - taxon_group (taxon groep) zonder element - taxa_group_element'::text;
RETURN QUERY SELECT 'public'::text, 'txg'::text, txg.txg_id::integer, 'public'::text, 'tge'::text,
 txg.*::text
FROM taxa_group txg
LEFT JOIN taxa_group_element tge
  ON txg.txg_id = tge.txg_id
WHERE tge.txg_id IS NULL
ORDER BY txg.txg_id;
--73

--smp
--zie qem

--obs
--zie qem

--mno
--zie qem

--Groepen G waarvan er elementen zijn zonder groep I.
--Groepen I waarvan het element niet in een groep G zit.

--objecten in G-groep, zonder I-groep
RETURN QUERY SELECT 'public'::text, 'ojgG'::text, NULL::integer, 'public'::text, 'ogeI'::text,
 'ojgG - object_group (type G) zonder groep I ogeI - object_group_element (type I)'::text;
RETURN QUERY SELECT 'public'::text, 'ojgG'::text, ojgG.ojg_id::integer, 'public'::text, 'ogeI'::text,
 ojgG.*::text
FROM object_group_element as ogeG
JOIN object_group as ojgG              ON (ojgG.ojg_id = ogeG.ojg_id AND ojgG.group_type = 'G')
LEFT JOIN object_group_element as ogeI ON (ogeI.obj_id = ogeG.obj_id)
JOIN object_group ojgI                 ON (ojgI.ojg_id = ogeI.ojg_id AND ojgI.group_type = 'I')
WHERE ogeI.ojg_id IS NULL 
ORDER BY ojgG.ojg_id;
--0

--objecten in I-groep, zonder G-groep. 
--Niet per se onnuttig, ze kunnen ook als I-groep in een opt.
RETURN QUERY SELECT 'public'::text, 'ojgI'::text, NULL::integer, 'public'::text, 'ogeG'::text,
 'ojgI - object_group (type I) zonder groep G ogeG - object_group_element (type G)'::text;
RETURN QUERY SELECT 'public'::text, 'ojgI'::text, ojgI.ojg_id::integer, 'public'::text, 'ogeG'::text,
 ojgI.*::text
FROM object_group_element as ogeI
JOIN object_group as ojgI              ON (ojgI.ojg_id = ogeI.ojg_id AND ojgI.group_type = 'I')
LEFT JOIN object_group_element as ogeG ON (ogeG.obj_id = ogeI.obj_id)
JOIN object_group ojgG                 ON (ojgG.ojg_id = ogeG.ojg_id AND ojgG.group_type = 'G')
WHERE ogeG.ojg_id IS NULL 
ORDER BY ojgI.ojg_id;
--0

--chemische stoffen in G-groep, zonder I-groep
RETURN QUERY SELECT 'public'::text, 'ssgG'::text, NULL::integer, 'public'::text, 'ssgeI'::text,
 'ssgG - substance_group (type G) zonder groep I ssgeI - substance_group_element (type I)'::text;
RETURN QUERY SELECT 'public'::text, 'ssgG'::text, ssgG.ssg_id::integer, 'public'::text, 'ssgeI'::text,
 ssgG.*::text
FROM substance_group_element as ssgeG
JOIN substance_group as ssgG               ON (ssgG.ssg_id = ssgeG.ssge_id AND ssgG.group_type = 'G')
LEFT JOIN substance_group_element as ssgeI ON (ssgeI.chs_id = ssgeG.chs_id)
JOIN substance_group ssgI                  ON (ssgI.ssg_id = ssgeI.ssge_id AND ssgI.group_type = 'I')
--ssge has wrong foreign key ssge_id, should be ssg_id (without e)
WHERE ssgeI.ssge_id IS NULL 
ORDER BY ssgG.ssg_id;
--0

--chemische stoffen in I-groep, zonder G-groep. 
--Niet per se onnuttig, ze kunnen ook als I-groep in een opt.
RETURN QUERY SELECT 'public'::text, 'ssgI'::text, NULL::integer, 'public'::text, 'ssgeG'::text,
 'ssgG - substance_group (type G) zonder groep I ssgeI - substance_group_element (type G)'::text;
RETURN QUERY SELECT 'public'::text, 'ssgI'::text, ssgI.ssg_id::integer, 'public'::text, 'ssgeG'::text,
 ssgI.*::text
FROM substance_group_element as ssgeI
JOIN substance_group as ssgI               ON (ssgI.ssg_id = ssgeI.ssge_id AND ssgI.group_type = 'I')
LEFT JOIN substance_group_element as ssgeG ON (ssgeG.chs_id = ssgeI.chs_id)
JOIN substance_group ssgG                  ON (ssgG.ssg_id = ssgeG.ssge_id AND ssgG.group_type = 'G')
WHERE ssgG.ssg_id IS NULL 
ORDER BY ssgI.ssg_id;
--0

--taxons in G-groep, zonder I-groep
--RETURN QUERY SELECT 'public'::text, 'txgG'::text, NULL::integer, 'public'::text, 'tgeI'::text,
-- 'txgG - taxa_group (type G) zonder groep I tgeI - taxa_group_element (type I)'::text;
--RETURN QUERY SELECT 'public'::text, 'txgG'::text, NULL::integer, 'public'::text, 'tgeI'::text,
-- 'De KRM- en LEW-importfuncties kunnen deze G groepen niet importeren.'::text;
RETURN QUERY SELECT 'public'::text, 'txgG'::text, txgG.txg_id::integer, 'public'::text, 'tgeI'::text,
 txgG.*::text
FROM taxa_group_element as tgeG
JOIN taxa_group as txgG              ON (txgG.txg_id = tgeG.txg_id AND txgG.group_type = 'G')
LEFT JOIN taxa_group_element as tgeI ON tgeI.txn_id = tgeG.txn_id
JOIN taxa_group txgI                 ON (txgI.txg_id = tgeI.txg_id AND txgI.group_type = 'I')
WHERE txgI.txg_id IS NULL 
ORDER BY txgG.txg_id;
--0

--taxons in I-groep, zonder G-groep. 
--Niet per se onnuttig, taxons kunnen ook als I-groep in een opt.
RETURN QUERY SELECT 'public'::text, 'txgI'::text, NULL::integer, 'public'::text, 'tgeG'::text,
 'txgI - taxa_group (type I) zonder groep G tgeG - taxa_group_element (type G)'::text;
RETURN QUERY SELECT 'public'::text, 'txgI'::text, txgI.txg_id::integer, 'public'::text, 'tgeG'::text,
 txgI.*::text
FROM taxa_group_element as tgeI
JOIN taxa_group as txgI              ON (txgI.txg_id = tgeI.txg_id AND txgI.group_type = 'I')
LEFT JOIN taxa_group_element as tgeG ON tgeG.txn_id = tgeI.txn_id
JOIN taxa_group txgG                 ON (txgG.txg_id = tgeG.txg_id AND txgG.group_type = 'G')
WHERE txgG.txg_id IS NULL 
ORDER BY txgI.txg_id;
--0


--Groepen G zonder opt
--Groepen I zonder opt
--niet gemaakt -- not implemented

--Groepen G waarvan er elementen zijn met ontbrekende opt's (via hun groep I).
--Groepen I waarvan het element in een groep G zit met ontbrekende opt's.

--controleer of voor een taxa_group type G, er element's zijn met missende hoedanigheid en/of grootheid
--this one is slow
RETURN QUERY SELECT 'public'::text, 'txgG'::text, NULL::integer, 'public'::text, 'txgI'::text,
 'taxa_group (type G) met missende opt voor gerelateerde taxa_group I'::text;
RETURN QUERY SELECT 'public'::text, 'txgG'::text, txgG.txg_id::integer, 'public'::text, 'txgI'::text,
 txgG.*::text
FROM observed_property_type optG
JOIN taxa_group_element as tgeG       ON (tgeG.txg_id = optG.taxa_group_id)
JOIN taxa_group as txgG               ON (txgG.txg_id = optG.taxa_group_id AND txgG.group_type = 'G')
JOIN taxa_group_element as tgeI       ON (tgeI.txn_id = tgeG.txn_id AND tgeI.txg_id <> tgeG.txg_id)
JOIN taxa_group txgI                  ON (txgI.txg_id = tgeI.txg_id AND txgI.group_type = 'I')
LEFT JOIN observed_property_type optI 
  ON (optI.taxa_group_id = txgI.txg_id 
     AND (  (optI.observed_property_type_class IS NULL AND optG.observed_property_type_class IS NULL) 
         OR optI.observed_property_type_class = optG.observed_property_type_class
         )
     AND (  (optI.result_type IS NULL AND optG.result_type IS NULL) 
         OR optI.result_type = optG.result_type
         )
     AND (  (optI.hoedanigheid_id IS NULL AND optG.hoedanigheid_id IS NULL) 
         OR optI.hoedanigheid_id = optG.hoedanigheid_id
         )
     AND (  (optI.quantity_id IS NULL AND optG.quantity_id IS NULL) 
         OR optI.quantity_id = optG.quantity_id
         )
     )
WHERE optI.opt_id IS NULL 
--AND optG.taxa_group_id = 26500
ORDER BY txgG.txg_id;
--1: Benthos Noordzee en Crassostrea gigas

--andersom: I-groepen die onnutte koppelingen hebben: 
--bijbehorende G-groep zonder opt, of met opt met afwijkende grootheid en/of hoedanigheid
--alleen G en I omgewisseld
--Op 18 juni 2016:
--  745 x afwijkende grootheid = 2977 (deze komen in scherm monprog-reg voor onder 2977=AANTL)
--  en 1 x 3076
RETURN QUERY SELECT 'public'::text, 'txgG'::text, NULL::integer, 'public'::text, 'txgI'::text,
 'taxa_group (type G) met missende opt voor gerelateerde taxa_group I'::text;
RETURN QUERY SELECT 'public'::text, 'txgG'::text, txgG.txg_id::integer, 'public'::text, 'txgI'::text,
 txgG.*::text
FROM observed_property_type optI
JOIN taxa_group_element as tgeI      ON (tgeI.txg_id = optI.taxa_group_id)
JOIN taxa_group as txgI              ON (txgI.txg_id = optI.taxa_group_id AND txgI.group_type = 'I')
JOIN taxa_group_element as tgeG      ON (tgeG.txn_id = tgeI.txn_id AND tgeI.txg_id <> tgeG.txg_id)
JOIN taxa_group txgG                 ON (txgG.txg_id = tgeG.txg_id AND txgG.group_type = 'G')
LEFT JOIN observed_property_type optG  
  ON (optG.taxa_group_id = txgG.txg_id 
     AND (  (optI.observed_property_type_class IS NULL AND optG.observed_property_type_class IS NULL) 
         OR optI.observed_property_type_class = optG.observed_property_type_class
         )
     AND (  (optI.result_type IS NULL AND optG.result_type IS NULL) 
         OR optI.result_type = optG.result_type
         )
     AND (  (optI.hoedanigheid_id IS NULL AND optG.hoedanigheid_id IS NULL) 
         OR optI.hoedanigheid_id = optG.hoedanigheid_id
         )
     AND (  (optI.quantity_id IS NULL AND optG.quantity_id IS NULL) 
         OR optI.quantity_id = optG.quantity_id
         )
     )
WHERE optG.opt_id IS NULL 
ORDER BY txgI.txg_id;




END
$$;


--
-- Name: ophalen_foreign_key_tabel(character varying); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION ophalen_foreign_key_tabel(v_constraint_name character varying) RETURNS character varying
    LANGUAGE plpgsql IMMUTABLE
    AS $_$
DECLARE
  v_fk_tabel 		character varying;
  v_lengte_tabel 	integer;
  v_tabelnaam 		character varying;
BEGIN
  SELECT constraint_name, table_name, length(table_name) INTO v_fk_tabel, v_tabelnaam, v_lengte_tabel
    FROM information_schema.key_column_usage 
   WHERE constraint_name = $1;
   --
   IF substr(v_fk_tabel,4,v_lengte_tabel) =  v_tabelnaam AND substr(v_fk_tabel,1,2) =  'fk'
   THEN
     v_fk_tabel:= substr(v_fk_tabel,4 + v_lengte_tabel + 1);
   ELSIF
     substr(v_fk_tabel,1,2) =  'pk'
   THEN
     v_fk_tabel:= '(primary key van '||v_tabelnaam||')';
   ELSE
     v_fk_tabel:= '(fk tabel opzoeken bij basistabel)';
   END IF;
   --
  RETURN v_fk_tabel;
  --
END;
$_$;


--
-- Name: FUNCTION ophalen_foreign_key_tabel(v_constraint_name character varying); Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON FUNCTION ophalen_foreign_key_tabel(v_constraint_name character varying) IS 'Opzoeken van mastertabellen bij een gegeven detailtabel.';


--
-- Name: ophalen_grootheid_type_id(character); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION ophalen_grootheid_type_id(grootheid character) RETURNS integer
    LANGUAGE plpgsql IMMUTABLE
    AS $_$
DECLARE
  v_id integer;
BEGIN
  SELECT grh_id INTO v_id FROM grootheid_type WHERE code = upper($1);
  RETURN v_id;
END;
$_$;


--
-- Name: ophalen_hoedanigheid_type_id(character); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION ophalen_hoedanigheid_type_id(grootheid character) RETURNS integer
    LANGUAGE plpgsql IMMUTABLE
    AS $_$
DECLARE
  v_id integer;
BEGIN
  SELECT hdh_id INTO v_id FROM hoedanigheid_type WHERE code = $1;
  RETURN v_id;
END;
$_$;


--
-- Name: ophalen_taxa_group_id(character); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION ophalen_taxa_group_id(grootheid character) RETURNS integer
    LANGUAGE plpgsql IMMUTABLE
    AS $_$
DECLARE
  v_id integer;
BEGIN
  SELECT txg_id INTO v_id FROM taxa_group WHERE upper(name) = upper($1);
  IF v_id IS NULL 
  THEN  
    RAISE NOTICE 'Er is geen txg_id gevonden voor %.',grootheid;
    RETURN v_id;
  ELSE
    RETURN v_id;
  END IF;
END;
$_$;


--
-- Name: rel_description(character varying, character varying); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION rel_description(p_relname character varying, p_schemaname character varying DEFAULT NULL::character varying) RETURNS text
    LANGUAGE sql
    AS $_$
    SELECT obj_description((CASE 
       WHEN strpos($1, '.') > 0 THEN $1
       WHEN $2 IS NULL THEN 'public.'||$1
       ELSE $2||'.'||$1
            END)::regclass, 'pg_class');
 $_$;


--
-- Name: FUNCTION rel_description(p_relname character varying, p_schemaname character varying); Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON FUNCTION rel_description(p_relname character varying, p_schemaname character varying) IS 'Samengesteld door John hofsté om commentaar bij tabel of view te kunnen vinden.';


--
-- Name: search_columns(text, name[], name[]); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION search_columns(needle text, haystack_tables name[] DEFAULT '{}'::name[], haystack_schema name[] DEFAULT '{public}'::name[]) RETURNS TABLE(schemaname text, tablename text, columnname text, rowctid text)
    LANGUAGE plpgsql
    AS $$ 
begin 
  FOR schemaname,tablename,columnname IN 
      SELECT c.table_schema,c.table_name,c.column_name 
      FROM information_schema.columns c 
      JOIN information_schema.tables t ON 
        (t.table_name=c.table_name AND t.table_schema=c.table_schema) 
      WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}') 
        AND c.table_schema=ANY(haystack_schema) 
        AND t.table_type='BASE TABLE' 
  LOOP 
    EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L', 
       schemaname, 
       tablename, 
       columnname, 
       needle 
    ) INTO rowctid; 
    IF rowctid is not null THEN 
      RETURN NEXT; 
    END IF; 
 END LOOP; 
END; 
$$;


--
-- Name: sp_copy_monprog(integer); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION sp_copy_monprog(cur_osc_id integer) RETURNS integer
    LANGUAGE plpgsql
    AS $$ 
DECLARE
   cpy_osc_id integer;
   cur_qem_id integer;
   cpy_qem_id integer;
BEGIN
   --1. copy the osc
   INSERT INTO observing_capability
   ( --osc_id
     observing_time_begin
   , observing_time_end
   , online_resource
   , process_type_id
   , last_change_time
   , "name"
   , is_sample
   , wkrv_id
   )
   SELECT --osc.osc_id
     osc.observing_time_begin + interval '1 year'
   , osc.observing_time_end + interval '1 year'
   , osc.online_resource
   , osc.process_type_id
   , now()
   , osc."name"
   , osc.is_sample
   , osc.wkrv_id
   FROM observing_capability osc
   WHERE osc.osc_id = cur_osc_id
   RETURNING osc_id INTO cpy_osc_id
   ;

   --1b. copy the osc's ocrp
   INSERT INTO observing_capability_responsible_party
   (--  ocrp_id  serial NOT NULL,
     osc_id,
     ore_id
   )
   SELECT
   --ocrp.ocrp_id
     cpy_osc_id
   , ocrp.ore_id
   FROM observing_capability_responsible_party ocrp 
   WHERE ocrp.osc_id = cur_osc_id;
   
   --here comes the FOR loop 
    FOR cur_qem_id IN
       SELECT qem_id 
       FROM quality_element qem
       WHERE qem.osc_id = cur_osc_id
    LOOP
       --1a/2. copy the qem
       INSERT INTO quality_element
        (  --  qem_id                    serial NOT NULL,
          osc_id,
          opt_id,
          parameter_use_id,
          cycle_value,
          cycle_uom_id,
          cycle_description,
          frequency_value,
          frequency_uom_id,
          reason_deviation_program,
          last_change_time,
          sampling_method_id,
          sampling_device_id,
          deviation_program_jn,
          opt_uom_id,
          wbm_id,
          remark
        )
        SELECT      
        --  qem_id                    serial NOT NULL,
        cpy_osc_id, --  osc_id         integer NOT NULL,
        opt_id,
        parameter_use_id,
        cycle_value,
        cycle_uom_id,
        cycle_description,
        frequency_value,
        frequency_uom_id,
        reason_deviation_program,
        last_change_time,
        sampling_method_id,
        sampling_device_id,
        deviation_program_jn,
        opt_uom_id,
        wbm_id,
        remark
        FROM quality_element
        WHERE qem_id = cur_qem_id
        RETURNING qem_id
        INTO cpy_qem_id
        ;
       --2a/3. copy it's pot's
       --pot has no referencing tables, otherwise we would need another loop
       INSERT INTO monprog.planned_observation_time
       (--pot_id, 
        qem_id, start_date, end_date, noof_samples, samples_bepalingsgrens_id, last_change_time
        )
       SELECT --pot.pot_id,
         cpy_qem_id
       , pot.start_date
       , pot.end_date
       , pot.noof_samples
       , pot.samples_bepalingsgrens_id
       , now() 
       FROM monprog.planned_observation_time pot
       WHERE pot.qem_id = cur_qem_id;

       --2b. copy it's moqe's
       INSERT INTO monitoring_object_quality_element
       (--moqe_id, 
         mno_id
       , qem_id
       )
       SELECT --moqe.moqe_id 
         moqe.mno_id
       , cpy_qem_id
       FROM monitoring_object_quality_element moqe
       WHERE moqe.qem_id = cur_qem_id;
       
       --2c. copy it's qeid's
       INSERT INTO monprog.quality_element_indicator_descriptor
       (qem_id, idd_id, last_change_time)
       SELECT cpy_qem_id, qeid.idd_id , now()
       FROM monprog.quality_element_indicator_descriptor qeid
       WHERE qeid.qem_id = cur_qem_id;
       
    END LOOP;
   RETURN cpy_osc_id; -- return whichever was used.
END; 
$$;


--
-- Name: sp_create_new_wkr_version(integer, integer); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION sp_create_new_wkr_version(old_wkrv_id integer, new_wkrv_id integer) RETURNS boolean
    LANGUAGE sql
    AS $$
	
INSERT INTO public.hydro_object(inspire_id_local_id, inspire_id_namespace, wkrv_id, hydro_object_type, related_hdo_id, last_change_time, fromhdo_id) 
select  ho.inspire_id_local_id, ho.inspire_id_namespace, new_wkrv_id, ho.hydro_object_type, ho.related_hdo_id , LOCALTIMESTAMP,ho.hdo_id 
from hydro_object ho where ho.wkrv_id = old_wkrv_id and ho.hydro_object_type <> 'Watercourse' ;

INSERT INTO related_hydro_object (from_hdo, to_hdo, rrt_id, last_change_time)
select  fromhdo_id,ho.hdo_id,1,LOCALTIMESTAMP
from hydro_object ho where ho.wkrv_id = new_wkrv_id;
	
INSERT INTO public.hydro_object(inspire_id_local_id, inspire_id_namespace, wkrv_id, hydro_object_type, related_hdo_id, last_change_time, fromhdo_id) 
select ho.inspire_id_local_id, ho.inspire_id_namespace, new_wkrv_id, ho.hydro_object_type, rho.to_hdo , LOCALTIMESTAMP,ho.hdo_id from hydro_object ho 
join related_hydro_object rho on ho.related_hdo_id = rho.from_hdo
join hydro_object ho1 on ho1.hdo_id = rho.to_hdo
where ho.wkrv_id = old_wkrv_id and ho.hydro_object_type = 'Watercourse' and rho.rrt_id = 1 and ho1.wkrv_id = new_wkrv_id;

INSERT INTO related_hydro_object (from_hdo, to_hdo, rrt_id, last_change_time)
select  fromhdo_id,ho.hdo_id,1,LOCALTIMESTAMP
from hydro_object ho where ho.wkrv_id = new_wkrv_id and ho.hydro_object_type = 'Watercourse';
	
INSERT INTO related_hydro_object (from_hdo, to_hdo, rrt_id, last_change_time)
select hdo_id, to_hdo, rrt_id,LOCALTIMESTAMP from hydro_object ho 
join related_hydro_object rho on ho.fromhdo_id = rho.from_hdo
where wkrv_id = new_wkrv_id
and rho.rrt_id in (select rrt_id from rel_hdo_role_type where rrt_id not in (1,3,4));

INSERT INTO related_hydro_object (from_hdo, to_hdo, rrt_id, last_change_time)
select from_hdo, hdo_id, rrt_id,LOCALTIMESTAMP from hydro_object ho 
join related_hydro_object rho on ho.fromhdo_id = rho.to_hdo
where wkrv_id = new_wkrv_id
and rho.rrt_id in (select rrt_id from rel_hdo_role_type where rrt_id not in (1,3,4));

INSERT INTO public.hdo_names(hdo_id, name, language, last_change_time)
select ho2.hdo_id, hn.name, hn.language, LOCALTIMESTAMP from hydro_object ho1
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join hdo_names hn on hn.hdo_id = ho1.hdo_id
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1;

-- hdo_identfiers bestaat niet voor andere wkrv_id's dan 4

INSERT INTO public.watercourse(wtc_id, neighbour_id, length, width_lower, width_upper, "geometryLine", last_change_time, "geometrySurface")
select ho2.hdo_id, wc.neighbour_id, wc.length,wc.width_lower, wc.width_upper,wc."geometryLine", LOCALTIMESTAMP, wc."geometrySurface" from hydro_object ho1
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join watercourse wc on wc.wtc_id = ho1.hdo_id
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1;

INSERT INTO public.related_hydro_object(
			from_hdo, to_hdo, rrt_id, last_change_time)
select hdo_id, related_hdo_id,4,LOCALTIMESTAMP from hydro_object where hydro_object_type = 'Watercourse' and wkrv_id= new_wkrv_id;

INSERT INTO public.management_regulation_or_restriction_zone(mrz_id, mrz_type, geometry, environmental_domain_id, mrz_collection_id, specialised_zone_type_id, begin_designation, end_designation, indeterminate_end_position, related_mrz_id, last_change_time)
select ho2.hdo_id, mrz.mrz_type, mrz.geometry,mrz.environmental_domain_id, mrz.mrz_collection_id,mrz.specialised_zone_type_id, mrz.begin_designation, mrz.end_designation, mrz.indeterminate_end_position, mrz.related_mrz_id, LOCALTIMESTAMP from hydro_object ho1
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join management_regulation_or_restriction_zone mrz on mrz.mrz_id = ho1.hdo_id
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1;

INSERT INTO public.river_basin_disrict(rbd_id, area_value, area_uom_id, international_rbd, part_of_district, last_change_time, area_ex_cw_value)
select ho2.hdo_id, rbd.area_value, rbd.area_uom_id,ho3.hdo_id, ho4.hdo_id, LOCALTIMESTAMP, rbd.area_ex_cw_value from hydro_object ho1
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join river_basin_disrict rbd on rbd.rbd_id = ho1.hdo_id
left join hydro_object ho3 on rbd.international_rbd = ho3.fromhdo_id
left join hydro_object ho4 on rbd.part_of_district = ho4.fromhdo_id
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1;

INSERT INTO public.water_body(wbd_id, level_of_detail, water_body_subtype, rbd_id, last_change_time, description, is_article_7, measure_comment, excemption_remarks, doelen_toestand_remarks)
select ho2.hdo_id, wb.level_of_detail, wb.water_body_subtype, ho3.hdo_id,LOCALTIMESTAMP, wb.description, wb.is_article_7, wb.measure_comment, wb.excemption_remarks, wb.doelen_toestand_remarks from hydro_object ho1
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join water_body wb on wb.wbd_id = ho1.hdo_id
join hydro_object ho3 on ho3.fromhdo_id = wb.rbd_id
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1;

INSERT INTO public.surface_water_body(swb_id, is_artificial, sw_typology_id, waterbody_description, is_heaviliy_modified, representative_point, area_value, area_uom_id, large, length_value, length_uom_id, main, other_pressure_description, other_impact_description, last_change_time, sw_target_typology_id, article_43_comment, article_43_litref, article_43_url, article_43b_motivation, significantlychanged, reason_change_old, is_reservoir, is_transboundary)
select ho2.hdo_id, swb.is_artificial, swb.sw_typology_id, swb.waterbody_description, swb.is_heaviliy_modified, swb.representative_point, swb.area_value, swb.area_uom_id, swb.large, swb.length_value, swb.length_uom_id, swb.main, swb.other_pressure_description, swb.other_impact_description, LOCALTIMESTAMP, swb.sw_target_typology_id, swb.article_43_comment, swb.article_43_litref, swb.article_43_url, swb.article_43b_motivation, swb.significantlychanged, swb.reason_change_old, swb.is_reservoir, swb.is_transboundary from hydro_object ho1
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join surface_water_body swb on swb.swb_id = ho1.hdo_id
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1;

INSERT INTO public.ground_water_body(gwb_id, area, centroid, is_out_of_rbd, is_transboundary, average_depth_value, average_depth_uom_id, average_thickness_value, average_thickness_uom_id, capacity_value, capacity_uom_id, depth_range_id, geological_formation_id, vertical_orientation_id, horizon_id, substrate_id, is_layered, is_linked_to_terrestrial_ecosystems, other_pressure_description, other_impact_description, last_change_time, significantlychanged, reason_change_old)
select ho2.hdo_id, gwb.area, gwb.centroid, gwb.is_out_of_rbd, gwb.is_transboundary, gwb.average_depth_value, gwb.average_depth_uom_id, gwb.average_thickness_value, gwb.average_thickness_uom_id, gwb.capacity_value, gwb.capacity_uom_id, gwb.depth_range_id, gwb.geological_formation_id, gwb.vertical_orientation_id, gwb.horizon_id, gwb.substrate_id, gwb.is_layered, gwb.is_linked_to_terrestrial_ecosystems, gwb.other_pressure_description, gwb.other_impact_description, LOCALTIMESTAMP, significantlychanged, reason_change_old from hydro_object ho1
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join ground_water_body gwb on gwb.gwb_id = ho1.hdo_id
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1;





INSERT INTO public.abstraction_point(asp_id, wbd_id, geometry, last_change_time, asp_type)
select ho3.hdo_id, ho2.hdo_id, ap.geometry, LOCALTIMESTAMP,ap.asp_type from hydro_object ho1
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join abstraction_point ap on ap.wbd_id = ho1.hdo_id
join abstraction_point ap1 on ap.asp_id = ap1.asp_id 
join related_hydro_object rho1 on ap1.asp_id = rho1.from_hdo
join hydro_object ho3 on ho3.hdo_id = rho1.to_hdo
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and ho3.wkrv_id = new_wkrv_id and rho.rrt_id = 1;


INSERT INTO public.gw_abstraction_point(gasp_id, company, rewab_code, rewab_id, last_change_time)
select ho2.hdo_id, gap.company, gap.rewab_code, gap.rewab_id, LOCALTIMESTAMP from hydro_object ho1
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join gw_abstraction_point gap on gap.gasp_id = ho1.hdo_id
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1;


INSERT INTO public.sw_abstraction_point(sasp_id, rewab_id, swat_id, waterabstraction_from, last_change_time)
select ho2.hdo_id, gap.rewab_id, gap.swat_id, gap.waterabstraction_from, LOCALTIMESTAMP from hydro_object ho1
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join sw_abstraction_point gap on gap.sasp_id = ho1.hdo_id
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1;

INSERT INTO public.wfd_protected_area(wpa_id, geometry, comment_value_status_prot_area, protected_area_type_id, protected_area_status_id, swb_art7_id, gwb_art7_id, last_change_time)
select ho2.hdo_id, wfd.geometry, wfd.comment_value_status_prot_area, wfd.protected_area_type_id, wfd.protected_area_status_id, wfd.swb_art7_id, wfd.gwb_art7_id, LOCALTIMESTAMP from hydro_object ho1
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join wfd_protected_area wfd on wfd.wpa_id = ho1.hdo_id
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id  and rho.rrt_id = 1;

INSERT INTO public.gwb_pressure(gwb_id, gprs_id, is_significant, last_change_time, remark, function, effect, prr_id, from_gwbp_id)
select ho2.hdo_id, wfd.gprs_id, wfd.is_significant, LOCALTIMESTAMP, wfd.remark, wfd.function, wfd.effect, wfd.prr_id, wfd.gwbp_id from hydro_object ho1
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join gwb_pressure wfd on wfd.gwb_id = ho1.hdo_id
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1;

INSERT INTO public.swb_pressure(swb_id, sprs_id, is_significant, last_change_time, remark, function, effect, from_swbp_id)
select ho2.hdo_id, wfd.sprs_id, wfd.is_significant, LOCALTIMESTAMP, wfd.remark, wfd.function, wfd.effect, wfd.swbp_id from hydro_object ho1
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join swb_pressure wfd on wfd.swb_id = ho1.hdo_id
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1;

INSERT INTO public.wb_prognosis(year, opt_id, cls_id, last_change_time, swb_id)
select wfd.year, wfd.opt_id, wfd.cls_id, LOCALTIMESTAMP, ho2.hdo_id from hydro_object ho1
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join wb_prognosis wfd on wfd.swb_id = ho1.hdo_id
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1;

INSERT INTO public.relation_ore_wbd(ore_id, wbd_id, last_change_time)
select wfd.ore_id, ho2.hdo_id, LOCALTIMESTAMP from hydro_object ho1
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join relation_ore_wbd wfd on wfd.wbd_id = ho1.hdo_id
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1; 


INSERT INTO public.norm(naam_namespace_id, lokale_naam, observed_property, opmerking, 
			uri, defined_at_level_id, norm_groep_id, wkrv_id, last_change_time, from_nrm_id)
SELECT naam_namespace_id, lokale_naam, observed_property, opmerking, 
	   uri, defined_at_level_id, norm_groep_id, new_wkrv_id, last_change_time, nrm_id
FROM public.norm no
where no.wkrv_id = old_wkrv_id;


INSERT INTO public.norm_feature(nrm_id, hdo_id, last_change_time)
select no.nrm_id, ho.hdo_id, LOCALTIMESTAMP from hydro_object ho
join norm_feature nf on nf.hdo_id = ho.fromhdo_id
join norm no on nf.nrm_id = no.from_nrm_id
where ho.wkrv_id = new_wkrv_id and ho.wkrv_id = new_wkrv_id; 


INSERT INTO public.norm_value(nrm_id, value_processing_method, cptm_id, lowerbound_value, 
			lowerbound_uom_id, upperbound_value, upperbound_uom_id, begin_lifespan_version, 
			end_lifespan_version, opmerking, uri, opname_datum, norm_klasse_id, 
			lowerbound_limit_symbol_id, upperbound_limit_symbol_id)
SELECT no.nrm_id, value_processing_method, cptm_id, lowerbound_value, 
	   lowerbound_uom_id, upperbound_value, upperbound_uom_id, begin_lifespan_version, 
	   end_lifespan_version, nv.opmerking, nv.uri, opname_datum, norm_klasse_id, 
	   lowerbound_limit_symbol_id, upperbound_limit_symbol_id
  FROM public.norm_value nv
join norm no on nv.nrm_id = no.from_nrm_id
where no.wkrv_id = new_wkrv_id;


INSERT INTO public.natural_background_levels(gwb_id, opt_id, priority, concentration_value, concentration_uom_id, last_change_time)
select ho2.hdo_id, wfd.opt_id, wfd.priority, wfd.concentration_value, wfd.concentration_uom_id, LOCALTIMESTAMP from hydro_object ho1
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join natural_background_levels wfd on wfd.gwb_id = ho1.hdo_id
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1; 

INSERT INTO public.swb_art43b_excemption(swb_id, a43b_id, last_change_time)
select ho2.hdo_id, wfd.a43b_id, LOCALTIMESTAMP from hydro_object ho1
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join swb_art43b_excemption wfd on wfd.swb_id = ho1.hdo_id
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1; 

INSERT INTO public.bronnen(hdo_id, chs_id, wbpm_id, eenh_id, year, is_significant_wbm, is_significant_factsheet)
select ho2.hdo_id, br.chs_id, br.wbpm_id, br.eenh_id, br.year, br.is_significant_wbm, br.is_significant_factsheet from hydro_object ho1
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join bronnen br on br.hdo_id = ho1.hdo_id
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1;

INSERT INTO public.gwb_conceptual_model(gwb_id, volgnr, omschrijving, afbeelding, extentie, last_change_time)
select ho2.hdo_id, gcm.volgnr, gcm.omschrijving, gcm.afbeelding, gcm.extentie, LOCALTIMESTAMP from hydro_object ho1
join related_hydro_object rho on rho.from_hdo = ho1.hdo_id
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join gwb_conceptual_model gcm on gcm.gwb_id = ho1.hdo_id
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1;


-- Koppelen hydro_objects
INSERT INTO public.relation_gwb_swb(gwb_id, swb_id, relation_type_id, last_change_time)
select ho2.hdo_id, ho3.hdo_id, rgs1.relation_type_id, LOCALTIMESTAMP from hydro_object ho1
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join relation_gwb_swb rgs1 on rgs1.gwb_id = ho1.hdo_id
join relation_gwb_swb rgs2 on rgs1.gwsw_id = rgs2.gwsw_id
join related_hydro_object rho1 on rgs2.swb_id = rho1.from_hdo
join hydro_object ho3 on ho3.hdo_id = rho1.to_hdo
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and ho3.wkrv_id = new_wkrv_id and rho.rrt_id = 1;

INSERT INTO public.waterbody_protected_area_intersection(wbd_id, wpa_id, relation_type, geometry, last_change_time)
select ho2.hdo_id, ho3.hdo_id, wpai1.relation_type, wpai1.geometry,LOCALTIMESTAMP from hydro_object ho1
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join waterbody_protected_area_intersection wpai1 on wpai1.wbd_id = ho1.hdo_id
join waterbody_protected_area_intersection wpai2 on wpai2.wbpa_id = wpai1.wbpa_id
join related_hydro_object rho1 on wpai2.wpa_id = rho1.from_hdo
join hydro_object ho3 on ho3.hdo_id = rho1.to_hdo
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and ho3.wkrv_id = new_wkrv_id and rho.rrt_id = 1 and rho1.rrt_id = 1;

-- maatregelen
INSERT INTO public.river_basin_management_plan(rbd_id, wkrv_id, databases_and_repositories_description, general_supporting_documents, name, summary_of_programmes, summary_preparation_first_update, summary_processes_and_procedures, table_of_contents, pom_general_additional_comments, last_change_time, pom_reference)
select ho2.hdo_id, new_wkrv_id, rbmp.databases_and_repositories_description, rbmp.general_supporting_documents, rbmp.name, rbmp.summary_of_programmes, rbmp.summary_preparation_first_update, rbmp.summary_processes_and_procedures, rbmp.table_of_contents, rbmp.pom_general_additional_comments,LOCALTIMESTAMP ,rbmp.pom_reference from hydro_object ho1
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join river_basin_management_plan rbmp on rbmp.rbd_id = ho1.hdo_id
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1; 

-- de rpv_id laten we staan. Dit is misschien dan wel een verkeerde. Maar deze wordt gebruikt voor de uitvoeringIn en daar ga je altijd voor naar de rpp_id en dan kom je bij de goede uit.
INSERT INTO public.monitored_measure(
			monitored_measure_type, level_of_measure_id, prime_competent_authority_id, 
			derived_from_measure_id, last_change_time, rpv_id, frommtm_id)
select mm.monitored_measure_type, mm.level_of_measure_id, mm.prime_competent_authority_id, mm.derived_from_measure_id,LOCALTIMESTAMP, mm.rpv_id, mm.mtm_id  from hydro_object ho1
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join river_basin_management_plan rbmp on rbmp.rbd_id = ho1.hdo_id
join supp_add_measure_type samt on samt.rbmp_id = rbmp.rbmp_id
join monitored_measure mm on mm.mtm_id = samt.smnl_id
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1;


INSERT INTO public.supp_add_measure_type(
			smnl_id, rbmp_id, namespace_id, local_id, measure_name, measure_description, comment, associated_link_desc, associated_link_uri, is_additional_measure, 
			legislation_ref_id, amount_value, amount_uom_id, initiative_party, 
			smeu_id, last_change_time, initiative_party_ocat_id, "is_N2000_relevant", 
			is_abstraction_relevant, process_info)
select mm.mtm_id, rbmp1.rbmp_id, samt.namespace_id, samt.local_id, samt.measure_name, samt.measure_description, samt.comment, samt.associated_link_desc, samt.associated_link_uri, 
samt.is_additional_measure, samt.legislation_ref_id, samt.amount_value, samt.amount_uom_id, samt.initiative_party, samt.smeu_id, LOCALTIMESTAMP, samt.initiative_party_ocat_id, 
samt."is_N2000_relevant", samt.is_abstraction_relevant, samt.process_info from monitored_measure mm
join supp_add_measure_type samt on samt.smnl_id = mm.frommtm_id
join river_basin_management_plan rbmp on rbmp.rbmp_id = samt.rbmp_id
join related_hydro_object rho on rho.from_hdo = rbmp.rbd_id
join hydro_object ho on ho.hdo_id = rho.to_hdo
join river_basin_management_plan rbmp1 on rbmp1.rbd_id = ho.hdo_id
where mm.frommtm_id is not null and ho.wkrv_id = new_wkrv_id and rho.rrt_id = 1;

update monitored_measure set derived_from_measure_id = frommtm_id;


INSERT INTO public.measure_finance(
			mtm_id, last_change_time, sum, mft_id)
select mm.mtm_id,LOCALTIMESTAMP,mf.sum,mf.mft_id from monitored_measure mm
join measure_finance mf on mf.mtm_id = mm.frommtm_id;

INSERT INTO public.measure_progress(
			mtm_id, mst_id, reason_substanstial_delay, amount_value, last_change_time, 
			year)
select mm.mtm_id,mp.mst_id,mp.reason_substanstial_delay, mp.amount_value, LOCALTIMESTAMP, mp.year from monitored_measure mm
join measure_progress mp on mp.mtm_id = mm.frommtm_id;

INSERT INTO public.measure_progress_completed(mtm_id, year_completed, completed)
select mm.mtm_id, mpc.year_completed, mpc.completed from monitored_measure mm
join measure_progress_completed mpc on mpc.mtm_id = mm.frommtm_id;

INSERT INTO public.monitored_measure_eu_fund(mtm_id, euf_id, last_change_time)
select mm.mtm_id, mp.euf_id, LOCALTIMESTAMP from monitored_measure mm
join monitored_measure_eu_fund mp on mp.mtm_id = mm.frommtm_id;

INSERT INTO public.measure_per_abstraction_point(
			mtm_id, asp_id, last_change_time)
select mm.mtm_id, ho.hdo_id,LOCALTIMESTAMP  from measure_per_abstraction_point mpap
join monitored_measure mm on mm.frommtm_id = mpap.mtm_id
join hydro_object ho on ho.fromhdo_id = mpap.asp_id;


INSERT INTO public.measure_per_man_reg_or_restr_zone(
			mtm_id, mrz_id, last_change_time)
select mm.mtm_id, ho.hdo_id,LOCALTIMESTAMP  from measure_per_man_reg_or_restr_zone mpap
join monitored_measure mm on mm.frommtm_id = mpap.mtm_id
join hydro_object ho on ho.fromhdo_id = mpap.mrz_id;

INSERT INTO public.measure_per_man_protected_area(
			mtm_id, wpa_id, last_change_time)
select mm.mtm_id, ho.hdo_id,LOCALTIMESTAMP  from measure_per_man_protected_area mpap
join monitored_measure mm on mm.frommtm_id = mpap.mtm_id
join hydro_object ho on ho.fromhdo_id = mpap.wpa_id;


-- versie doorzetten / kopieeren oordelen
-----------------

INSERT INTO public.gwb_yearly_status(
			gwb_id, year_of_status, comment_on_chemical_status, comment_on_quantity_status, 
			motivation_4no_chemical_status, motivation_4no_quantity_status, 
			last_change_time, additional_purification, motivation_4no_chemical_trends, 
			exceeds_drinkingwater, from_gys_id)
select ho2.hdo_id, gys.year_of_status, gys.comment_on_chemical_status, gys.comment_on_quantity_status, gys.motivation_4no_chemical_status, gys.motivation_4no_quantity_status, LOCALTIMESTAMP, 
gys.additional_purification, gys.motivation_4no_chemical_trends, gys.exceeds_drinkingwater,gys.gys_id  from hydro_object ho1
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join gwb_yearly_status gys on gys.gwb_id = ho1.hdo_id
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1; 

INSERT INTO public.swb_yearly_status(
			swb_id, year_of_status, comment_on_chemical_status, comment_on_ecological_status, 
			motivation_4no_chemical_status, motivation_4no_ecological_status, 
			last_change_time, additional_purification, exceeds_drinkingwater, from_sys_id)
select ho2.hdo_id, gys.year_of_status, gys.comment_on_chemical_status, gys.comment_on_ecological_status, 
gys.motivation_4no_chemical_status, gys.motivation_4no_ecological_status, LOCALTIMESTAMP, 
gys.additional_purification,  gys.exceeds_drinkingwater, gys.sys_id from hydro_object ho1
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join swb_yearly_status gys on gys.swb_id = ho1.hdo_id
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1; 



INSERT INTO public.observation(
			phenomenon_time_begin, phenomenon_time_end, result_time, 
			valid_time_begin, valid_time_end, observed_property_type, quality_indicator_id, 
			numeric_value_uom_id, value_processing_method_id, limit_symbol_id, 
			description, classified_result_id, remarks, result_nature_id, 
			start_depth, end_depth, obs_parm_parent_id, mno_id, last_change_time, 
			numeric_value, numeric_value_significance, from_obs_id)
select phenomenon_time_begin, phenomenon_time_end, result_time, 
			valid_time_begin, valid_time_end, observed_property_type, quality_indicator_id, 
			numeric_value_uom_id, value_processing_method_id, limit_symbol_id, 
			description, classified_result_id, remarks, result_nature_id, 
			start_depth, end_depth, obs_parm_parent_id, mno_id, LOCALTIMESTAMP, 
			numeric_value, numeric_value_significance, obs_id
from swb_yearly_status sys 
join sw_testing_conclusion stc on sys.from_sys_id = stc.sys_id
join observation obs on obs.obs_id = stc.stcc_id
where sys.from_sys_id is not null; -- we kunnen er vanuit gaan dit alleen de nieuw toegevoegde swb_yearly_status zijn


INSERT INTO public.observation(
			phenomenon_time_begin, phenomenon_time_end, result_time, 
			valid_time_begin, valid_time_end, observed_property_type, quality_indicator_id, 
			numeric_value_uom_id, value_processing_method_id, limit_symbol_id, 
			description, classified_result_id, remarks, result_nature_id, 
			start_depth, end_depth, obs_parm_parent_id, mno_id, last_change_time, 
			numeric_value, numeric_value_significance, from_obs_id)
select phenomenon_time_begin, phenomenon_time_end, result_time, 
			valid_time_begin, valid_time_end, observed_property_type, quality_indicator_id, 
			numeric_value_uom_id, value_processing_method_id, limit_symbol_id, 
			description, classified_result_id, remarks, result_nature_id, 
			start_depth, end_depth, obs_parm_parent_id, mno_id, LOCALTIMESTAMP, 
			numeric_value, numeric_value_significance, obs_id
from gwb_yearly_status gys 
join gw_testing_conclusion gtc on gys.from_gys_id = gtc.gys_id
join observation obs on obs.obs_id = gtc.gtcc_id
where gys.from_gys_id is not null; -- we kunnen er vanuit gaan dit alleen de nieuw toegevoegde gwb_yearly_status zijn

-- insert observed_feature voor opp en grond water
INSERT INTO public.observed_feature(
			hdo_id, obs_id, last_change_time)
select ho1.hdo_id, obs.obs_id, LOCALTIMESTAMP from observed_feature of
join observation obs on of.obs_id = obs.from_obs_id
join hydro_object ho on ho.hdo_id = of.hdo_id
join related_hydro_object rho on rho.from_hdo = ho.hdo_id
join hydro_object ho1 on ho1.hdo_id = rho.to_hdo
where ho.wkrv_id = old_wkrv_id and ho1.wkrv_id = new_wkrv_id and rho.rrt_id =1;

INSERT INTO public.gw_chemical_status(
			gys_id, clst_id, last_change_time, eumg_id, wbpm_id, opt_id, observation_start_date, observation_end_date, clt_id)
select gwb.gys_id, gw.clst_id,LOCALTIMESTAMP, gw.eumg_id, gw.wbpm_id, gw.opt_id, gw.observation_start_date, gw.observation_end_date, gw.clt_id from gw_chemical_status gw
join gwb_yearly_status gwb on gwb.from_gys_id = gw.gys_id;


INSERT INTO public.gw_quantity_status(
			gys_id, gwst_id, last_change_time, wbpm_id, eumg_id, opt_id, clt_id)
select gwb.gys_id, gw.gwst_id,LOCALTIMESTAMP, gw.wbpm_id, gw.eumg_id, gw.opt_id, gw.clt_id from gw_quantity_status gw
join gwb_yearly_status gwb on gwb.from_gys_id = gw.gys_id;

  
INSERT INTO public.gw_testing_conclusion(
			gtcc_id, gys_id, last_change_time, eumg_id)
select obs.obs_id, gwb.gys_id, LOCALTIMESTAMP, gw.eumg_id from gw_testing_conclusion gw
join observation obs on obs.from_obs_id = gw.gtcc_id
join gwb_yearly_status gwb on gwb.from_gys_id = gw.gys_id;

  
INSERT INTO public.sw_testing_conclusion(
			stcc_id, sys_id, last_change_time, eumg_id)
select obs.obs_id, sys.sys_id, LOCALTIMESTAMP, sw.eumg_id from sw_testing_conclusion sw
join observation obs on obs.from_obs_id = sw.stcc_id
join swb_yearly_status sys on sys.from_sys_id = sw.sys_id;

INSERT INTO public.sw_ecological_status(
			sys_id, opt_id, swes_id, last_change_time, eumg_id, wbpm_id, observation_start_date, observation_end_date, clt_id)
select swb.sys_id, sw.opt_id, sw.swes_id, LOCALTIMESTAMP, sw.eumg_id, sw.wbpm_id, sw.observation_start_date, sw.observation_end_date, sw.clt_id from sw_ecological_status sw
join swb_yearly_status swb on swb.from_sys_id = sw.sys_id;

  
INSERT INTO public.sw_chemical_substance_status(
			sys_id, opt_id, swcs_id, last_change_time, eumg_id, wbpm_id, observation_start_date, observation_end_date, clt_id)
select swb.sys_id, sw.opt_id, sw.swcs_id, LOCALTIMESTAMP, sw.eumg_id, sw.wbpm_id, sw.observation_start_date, sw.observation_end_date, sw.clt_id from sw_chemical_substance_status sw
join swb_yearly_status swb on swb.from_sys_id = sw.sys_id;

INSERT INTO public.pollutant_trend(
			gwys_id, opt_id, comment, last_change_time, depth_m, 
			cls_id)
select gys.gys_id,  pt.opt_id, pt.comment,LOCALTIMESTAMP, pt.depth_m, pt.cls_id
 from gwb_yearly_status gys
join pollutant_trend pt on gys.from_gys_id = pt.gwys_id;

-- koppelen aan hydro_objects
INSERT INTO public.gw_abstraction_point_status(
			asp_id, gwsy_id, gwst_id, last_change_time, remarks)
select ho2.hdo_id, gwb.gys_id, gaps.gwst_id, LOCALTIMESTAMP, gaps.remarks from hydro_object ho1
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join gw_abstraction_point_status gaps on gaps.asp_id = ho1.hdo_id
join gwb_yearly_status gwb on gwb.from_gys_id = gaps.gwsy_id
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1; 

  
INSERT INTO public.gw_n2000_status(
			wpa_id, gwsy, cls_id, last_change_time, opt_id, remark)
select ho2.hdo_id, gwb.gys_id, gns.cls_id, LOCALTIMESTAMP, gns.opt_id, gns.remark from hydro_object ho1
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join gw_n2000_status gns on gns.wpa_id = ho1.hdo_id
join gwb_yearly_status gwb on gwb.from_gys_id = gns.gwsy
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1; 

INSERT INTO public.gw_swb_status(
			swb_id, gwsy, cls_id, last_change_time, opt_id, remark)
select ho2.hdo_id, gwb.gys_id, gss.cls_id, LOCALTIMESTAMP, gss.opt_id, gss.remark from hydro_object ho1
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join gw_swb_status gss on gss.swb_id = ho1.hdo_id
join gwb_yearly_status gwb on gwb.from_gys_id = gss.gwsy
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1; 

-- uitzonderingen
INSERT INTO public.swb_exemption(
			swb_id, obex_id, comment, last_change_time, from_swce_id)
select ho2.hdo_id, swbe.obex_id, swbe.comment, LOCALTIMESTAMP, swbe.swce_id from hydro_object ho1
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join swb_exemption swbe on swbe.swb_id = ho1.hdo_id
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1; 

INSERT INTO public.swb_chem_exemp_pollutant(
			swce_id, chs_id, last_change_time)
select swbe.swce_id, scep.chs_id,LOCALTIMESTAMP  from swb_chem_exemp_pollutant scep
join swb_exemption swbe on swbe.from_swce_id = scep.swce_id;

INSERT INTO public.swb_ecol_exemp_qe(
			swce_id, opt_id, last_change_time)
select swbe.swce_id, scep.opt_id,LOCALTIMESTAMP  from swb_ecol_exemp_qe scep
join swb_exemption swbe on swbe.from_swce_id = scep.swce_id;

INSERT INTO public.swb_excemption_mechanism_44(
			swce_id, exme_id, last_change_time)
select swbe.swce_id, scep.exme_id,LOCALTIMESTAMP  from swb_excemption_mechanism_44 scep
join swb_exemption swbe on swbe.from_swce_id = scep.swce_id;



INSERT INTO public.gwb_chemical_exemption(
			gwb_id, obex_id, comment, last_change_time, from_gwce_id)
select ho2.hdo_id, gwbe.obex_id, gwbe.comment, LOCALTIMESTAMP, gwbe.gwce_id from hydro_object ho1
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join gwb_chemical_exemption gwbe on gwbe.gwb_id = ho1.hdo_id
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1;

INSERT INTO public.gwb_chem_exemp_pollutant(
			gwce_id, opt_id, is_causing_exemption, last_change_time)
select gwbe.gwce_id, gcep.opt_id,gcep.is_causing_exemption, LOCALTIMESTAMP  from gwb_chem_exemp_pollutant gcep
join gwb_chemical_exemption gwbe on gwbe.from_gwce_id = gcep.gwce_id;

INSERT INTO public.gwb_quantitative_exemption(
			gwb_id, obex_id, comment, last_change_time)
select ho2.hdo_id, gwbe.obex_id, gwbe.comment, LOCALTIMESTAMP from hydro_object ho1
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join gwb_quantitative_exemption gwbe on gwbe.gwb_id = ho1.hdo_id
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1;



  
INSERT INTO public.swb_measures_43a(
			swb_id, fu43_id, motivation, last_change_time, from_swee_id)
select ho2.hdo_id, sm.fu43_id, sm.motivation, LOCALTIMESTAMP, sm.swee_id from hydro_object ho1
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join swb_measures_43a sm on sm.swb_id = ho1.hdo_id
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1;


INSERT INTO public.sw_measure43_function(
			me43_id, smee_id, last_change_time)
select gcep.me43_id, swbe.swee_id, LOCALTIMESTAMP from sw_measure43_function gcep
join swb_measures_43a swbe on swbe.from_swee_id = gcep.smee_id;

-- Meetpunten
INSERT INTO public.monitoring_object(inspire_id_local_id, inspire_id_namespace_id, wkrv_id,
			monitoring_object_type, name, additional_description, geometry, 
			last_change_time, geometry_etrs89, begin_lifespan_version, end_lifespan_version, 
			primary_geo_column, reason_change, primary_geodetic_reference_id, from_mno_id)
select inspire_id_local_id, inspire_id_namespace_id, new_wkrv_id, 
			monitoring_object_type, name, additional_description, geometry, 
			LOCALTIMESTAMP, geometry_etrs89, begin_lifespan_version, end_lifespan_version, 
			primary_geo_column, reason_change, primary_geodetic_reference_id, mno_id from monitoring_object where wkrv_id = old_wkrv_id;

INSERT INTO public.monitoring_object_other_ids(
			mno_id, hydro_id_local_id, hydro_id_ore_id)
select mo.mno_id, mooi.hydro_id_local_id, mooi.hydro_id_ore_id from monitoring_object_other_ids mooi
join monitoring_object mo on mo.from_mno_id = mooi.mno_id
where mo.wkrv_id = new_wkrv_id;
			
INSERT INTO public.related_monitoring_object(
			from_mno, to_mno, rrt_id, last_change_time)
select from_mno_id, mno_id, 1,LOCALTIMESTAMP from monitoring_object where wkrv_id = new_wkrv_id;

INSERT INTO public.monitoring_feature(
			mnf_id, monitoring_feature_type, pos_accuracy_horizontal_value, 
			pos_accuracy_horizontal_uom_id, pos_accuracy_vertical_value, 
			pos_accuracy_vertical_uom_id, last_change_time)
select  mo.mno_id, mf.monitoring_feature_type, mf.pos_accuracy_horizontal_value, 
			mf.pos_accuracy_horizontal_uom_id, mf.pos_accuracy_vertical_value, 
			mf.pos_accuracy_vertical_uom_id, LOCALTIMESTAMP from monitoring_feature mf
join monitoring_object mo on mo.from_mno_id = mf.mnf_id
where mo.wkrv_id = new_wkrv_id;

INSERT INTO public.environmental_monitoring_facility(
			emf_id,measurement_regime_id, representative_point, is_mobile, 
			result_acquisition_source_id, speciallised_emf_type_id, ligt_in_mrz_id, 
			last_change_time)
select mo.mno_id, emf.measurement_regime_id, emf.representative_point, emf.is_mobile, 
			emf.result_acquisition_source_id, emf.speciallised_emf_type_id, emf.ligt_in_mrz_id, 
			LOCALTIMESTAMP from environmental_monitoring_facility emf 
join monitoring_object mo on mo.from_mno_id = emf.emf_id
where mo.wkrv_id = new_wkrv_id;

select mo.mno_id, ho2.hdo_id, LOCALTIMESTAMP
  FROM public.env_mon_facil_in_mngmt_reg_or_restr_zone emfmr
join environmental_monitoring_facility emf on emf.emf_id = emfmr.emf_id
join monitoring_object mo on mo.from_mno_id = emf.emf_id
join management_regulation_or_restriction_zone mrz on mrz.mrz_id = emfmr.mrz_id
join hydro_object ho1 on ho1.hdo_id = mrz.mrz_id
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1;
			
			
INSERT INTO public.environmental_monitoring_network(emn_id, organization_level_id, last_change_time)
SELECT mo.mno_id, organization_level_id, LOCALTIMESTAMP  FROM public.environmental_monitoring_network emn
JOIN monitoring_object mo on mo.from_mno_id = emn.emn_id
WHERE mo.wkrv_id = new_wkrv_id;

INSERT INTO public.network_facility(
			emf_id, emn_id, linking_time_begin, linking_time_end, last_change_time)
select mo.mno_id, mo1.mno_id, ef.linking_time_begin, ef.linking_time_end, LOCALTIMESTAMP from network_facility ef
join monitoring_object mo on mo.from_mno_id = ef.emf_id
join monitoring_object mo1 on mo1.from_mno_id = ef.emn_id
where mo.wkrv_id = new_wkrv_id and mo1.wkrv_id = new_wkrv_id;


INSERT INTO public.monitored_compartiment(
			mno_id, cptm_id, last_change_time)
select mo.mno_id, mc.cptm_id, LOCALTIMESTAMP from monitored_compartiment mc
join monitoring_object mo on mo.from_mno_id = mc.mno_id
where mo.wkrv_id = new_wkrv_id;

INSERT INTO public.monitoring_station(
			mts_id, monitoring_station_type, number_of_points_in_subsite, 
			subsite_type_id, last_change_time)
select emf.emf_id, ms.monitoring_station_type, ms.number_of_points_in_subsite, 
			ms.subsite_type_id, LOCALTIMESTAMP from monitoring_station ms
join environmental_monitoring_facility emf on emf.emf_id = ms.mts_id
join monitoring_object mo on mo.from_mno_id = emf.emf_id
where mo.wkrv_id = new_wkrv_id;

INSERT INTO public.observing_capability(
			observing_time_begin, observing_time_end, online_resource, 
			process_type_id, last_change_time, name, is_sample, wkrv_id, from_osc_id)
SELECT observing_time_begin, observing_time_end, online_resource, 
	   process_type_id, LOCALTIMESTAMP, name, is_sample, new_wkrv_id, osc_id
FROM public.observing_capability
where wkrv_id = old_wkrv_id;

INSERT INTO public.quality_element(
			osc_id, opt_id, parameter_use_id, cycle_value, cycle_uom_id, 
			cycle_description, frequency_value, frequency_uom_id, reason_deviation_program, 
			last_change_time, sampling_method_id, sampling_device_id, deviation_program_jn, 
			opt_uom_id, wbm_id, remark, from_qem_id)
SELECT oc.osc_id, qe.opt_id, qe.parameter_use_id, qe.cycle_value, qe.cycle_uom_id, 
	   qe.cycle_description, qe.frequency_value, qe.frequency_uom_id, qe.reason_deviation_program, 
	   LOCALTIMESTAMP, qe.sampling_method_id, qe.sampling_device_id, qe.deviation_program_jn, 
	   qe.opt_uom_id, qe.wbm_id, qe.remark, qe.qem_id
FROM public.quality_element qe
JOIN public.observing_capability oc on oc.from_osc_id = qe.osc_id;

INSERT INTO monprog.quality_element_indicator_descriptor(qem_id, idd_id, last_change_time)
SELECT qe.qem_id, qeid.idd_id, LOCALTIMESTAMP FROM monprog.quality_element_indicator_descriptor qeid
JOIN quality_element qe on qe.from_qem_id = qeid.qem_id;

INSERT INTO monprog.planned_observation_time(
			qem_id, start_date, end_date, noof_samples, samples_bepalingsgrens_id, 
			last_change_time)
SELECT qem.qem_id, pot.start_date, pot.end_date, pot.noof_samples, pot.samples_bepalingsgrens_id, LOCALTIMESTAMP
FROM monprog.planned_observation_time pot
JOIN public.quality_element qem on qem.from_qem_id = pot.qem_id;

INSERT INTO public.observing_capability_responsible_party(osc_id, ore_id)
SELECT oc.osc_id, ocrp.ore_id
FROM public.observing_capability_responsible_party ocrp
JOIN observing_capability oc on oc.from_osc_id = ocrp.osc_id;

INSERT INTO public.monitoring_object_quality_element(mno_id, qem_id)
SELECT mo.mno_id, qe.qem_id FROM public.monitoring_object_quality_element moqe
join monitoring_object mo on mo.from_mno_id = moqe.mno_id 
join quality_element qe on qe.from_qem_id = moqe.qem_id;

INSERT INTO public.swb_physical_alteration(swb_id, pat_id)
SELECT ho2.hdo_id, spa.pat_id FROM public.swb_physical_alteration spa
join surface_water_body swb on swb.swb_id = spa.swb_id
join hydro_object ho1 on ho1.hdo_id = swb.swb_id
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1;

INSERT INTO public.planned_feature_monitoring(hdo_id, qem_id, last_change_time, from_pmf_id)
SELECT ho2.hdo_id, qe.qem_id, LOCALTIMESTAMP, pfm.pmf_id
  FROM public.planned_feature_monitoring pfm
JOIN hydro_object ho1 on ho1.hdo_id = pfm.hdo_id
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
join quality_element qe on qe.from_qem_id = pfm.qem_id
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1;
  
INSERT INTO public.program_monitoring_object(
			 object_mno_id, last_change_time)
SELECT mo.mno_id, LOCALTIMESTAMP FROM public.program_monitoring_object pmo
JOIN monitoring_object mo on mo.from_mno_id = pmo.object_mno_id;

INSERT INTO public.projection(
			pmf_id, monitoring_goal_id, last_change_time)
SELECT pfm.pmf_id, pr.monitoring_goal_id, LOCALTIMESTAMP
  FROM public.projection pr
JOIN planned_feature_monitoring pfm on pfm.from_pmf_id = pr.pmf_id;

INSERT INTO public.wb_status_change_motivation(
			wbd_id, scm_id, opt_id, year_initial, year_status, last_change_time)
SELECT ho2.hdo_id, wscm.scm_id, wscm.opt_id, wscm.year_initial, wscm.year_status, LOCALTIMESTAMP
  FROM public.wb_status_change_motivation wscm
join water_body wb on wb.wbd_id = wscm.wbd_id
join hydro_object ho1 on ho1.hdo_id = wb.wbd_id
join related_hydro_object rho on ho1.hdo_id = rho.from_hdo
join hydro_object ho2 on rho.to_hdo = ho2.hdo_id
where ho1.wkrv_id = old_wkrv_id and ho2.wkrv_id = new_wkrv_id and rho.rrt_id = 1;

INSERT INTO public.swb_pressure_impact(swbp_id, impact_id)
SELECT sp.swbp_id, spi.impact_id FROM public.swb_pressure_impact spi
JOIN swb_pressure sp on sp.from_swbp_id = spi.swbp_id; 

INSERT INTO public.gwb_pressure_impact(gwbp_id, impact_id)
SELECT gp.gwbp_id, gpi.impact_id FROM public.gwb_pressure_impact gpi
JOIN gwb_pressure gp on gp.from_gwbp_id = gpi.gwbp_id; 

select true;$$;


--
-- Name: sp_delete_monitoring_object(integer); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION sp_delete_monitoring_object(monitoring_object_id integer) RETURNS integer
    LANGUAGE plpgsql
    AS $$

BEGIN

delete from observation where mno_id = monitoring_object_id;
delete from monitoring_use where gms_id = monitoring_object_id;
delete from monitoring_station where mts_id = monitoring_object_id;
delete from network_facility where emf_id = monitoring_object_id;
delete from environmental_monitoring_facility where emf_id =monitoring_object_id;
delete from monitoring_feature where mnf_id =monitoring_object_id;
delete from monitored_compartiment where mno_id =monitoring_object_id;
delete from related_monitoring_object where from_mno = monitoring_object_id;
delete from related_monitoring_object where to_mno = monitoring_object_id;
delete from monitoring_object where mno_id = monitoring_object_id;


RETURN monitoring_object_id;
END;
$$;


--
-- Name: sp_delete_waterlichaam(integer); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION sp_delete_waterlichaam(hdoid integer) RETURNS integer
    LANGUAGE plpgsql
    AS $$
DECLARE
wb record;
wbn record;
BEGIN
	FOR wb IN select mtm_id from measure_per_man_reg_or_restr_zone where mrz_id = hdoId
	LOOP
		--RAISE NOTICE 'mtm_id: %', wb.mtm_id;
		
	   IF (select count(*) from measure_per_man_reg_or_restr_zone where mtm_id = wb.mtm_id) <= 1 THEN
		--RAISE NOTICE 'true';
		delete from measure_progress where mtm_id = wb.mtm_id;
		delete from measure_progress_completed where mtm_id = wb.mtm_id;
		delete from measure_finance where mtm_id = wb.mtm_id;
		delete from measure_per_man_reg_or_restr_zone where mtm_id = wb.mtm_id;
		delete from supp_add_measure_type where smnl_id = wb.mtm_id;
		delete from measure_per_abstraction_point where mtm_id = wb.mtm_id;
		delete from measure_per_man_protected_area where mtm_id = wb.mtm_id;
		delete from monitored_measure_eu_fund where mtm_id = wb.mtm_id;
		update monitored_measure set derived_from_measure_id = null where derived_from_measure_id = wb.mtm_id;
		delete from monitored_measure where mtm_id = wb.mtm_id;

		ELSE
		delete from measure_per_man_reg_or_restr_zone where mtm_id = wb.mtm_id;
		--RAISE NOTICE 'false';
	   END IF;
	   
	END LOOP;
	

delete from public.related_hydro_object where from_hdo = hdoId;
delete from public.related_hydro_object where to_hdo = hdoId;
delete from gw_swb_status where swb_id = hdoId;

delete from relation_gwb_swb where swb_id = hdoId;
delete from relation_gwb_swb where gwb_id = hdoId;
delete from swb_art43b_excemption where swb_id =hdoId;
delete from swb_excemption_mechanism_44 where swce_id in (select swce_id from swb_exemption where swb_id = hdoId);
delete from swb_chem_exemp_pollutant where swce_id in (select swce_id from swb_exemption where swb_id = hdoId);
delete from swb_ecol_exemp_qe where swce_id in (select swce_id from swb_exemption where swb_id = hdoId);
delete from swb_exemption where swb_id = hdoId;
delete from sw_measure43_function where smee_id in (select swee_id from swb_measures_43a where swb_id = hdoId);
delete from swb_measures_43a where swb_id = hdoId;
delete from swb_impact where swb_id = hdoId;
delete from swb_pressure where swb_id = hdoId;
delete from wb_prognosis where swb_id = hdoId;
delete from chemical_exceedance where sys_id in (select sys_id from swb_yearly_status where swb_id = hdoId);
delete from sw_chemical_substance_status where sys_id in (select sys_id from swb_yearly_status where swb_id = hdoId);
delete from sw_ecological_status where sys_id in (select sys_id from swb_yearly_status where swb_id = hdoId);
delete from sw_test_value where stcc_id in (select stcc_id from sw_testing_conclusion where sys_id in (select sys_id from swb_yearly_status where swb_id = hdoId));
delete from sw_testing_conclusion where sys_id in (select sys_id from swb_yearly_status where swb_id = hdoId);
delete from swb_yearly_status where sys_id in (select sys_id from swb_yearly_status where swb_id = hdoId);
delete from swb_yearly_status where swb_id = hdoId;
delete from surface_water_body where swb_id = hdoId;
delete from gwb_chem_exemp_pollutant where gwce_id in (select gwce_id from gwb_chemical_exemption where gwb_id = hdoId);
delete from gwb_chemical_exemption where gwb_id = hdoId;
delete from gwb_conceptual_model where gwb_id =  hdoId;
delete from gwb_impact where gwb_id = hdoId;
delete from gw_testing_conclusion where gys_id in (select gys_id from gwb_yearly_status where gwb_id = hdoId);
delete from gw_chemical_status where gys_id in (select gys_id from gwb_yearly_status where gwb_id = hdoId);
delete from gw_pollutants where gys_id in (select gys_id from gwb_yearly_status where gwb_id = hdoId);
delete from gw_quantity_status where gys_id in (select gys_id from gwb_yearly_status where gwb_id = hdoId);
delete from gw_testing_conclusion where gys_id in (select gys_id from gwb_yearly_status where gwb_id = hdoId);
delete from gwb_reason4chemical_failure where gys_id in (select gys_id from gwb_yearly_status where gwb_id = hdoId);
delete from gwb_reason4quantity_failure where gys_id in (select gys_id from gwb_yearly_status where gwb_id = hdoId);
delete from gw_n2000_status where gwsy in (select gys_id from gwb_yearly_status where gwb_id = hdoId);
delete from gw_abstraction_point_status where asp_id in (select asp_id from abstraction_point where wbd_id = hdoId);
delete from gw_abstraction_point_status where gwsy_id in (select gys_id from gwb_yearly_status where gwb_id = hdoId);
delete from gw_abstraction_point where gasp_id in (select asp_id from abstraction_point where wbd_id = hdoId);
delete from gw_swb_status where gwsy in (select gys_id from gwb_yearly_status where gwb_id = hdoId);
delete from pollutant_trend where gwys_id in (select gys_id from gwb_yearly_status where gwb_id = hdoId);
delete from gwb_yearly_status where gwb_id = hdoId;
delete from gwb_pressure where gwb_id = hdoId;
delete from measure_per_abstraction_point where asp_id in (select asp_id from abstraction_point where wbd_id = hdoId);
delete from sw_abstraction_point where sasp_id in (select asp_id from abstraction_point where wbd_id = hdoId);
delete from abstraction_point where wbd_id = hdoId;
delete from relation_ore_wbd where wbd_id = hdoId;
delete from waterbody_protected_area_intersection where wbd_id = hdoId;
delete from natural_background_levels where gwb_id = hdoId;
delete from ground_water_body where gwb_id = hdoId;
delete from water_body where wbd_id = hdoId;
delete from management_regulation_or_restriction_zone where mrz_id = hdoId;
delete from monitoring_use where gms_id in (select mts_id from monitoring_station where mts_id in (select emf_id from environmental_monitoring_facility where ligt_in_mrz_id = hdoId));
delete from monitoring_station where mts_id in (select emf_id from environmental_monitoring_facility where ligt_in_mrz_id = hdoId);
delete from wfd_protected_area where wpa_id = hdoId;
delete from environmental_monitoring_facility where ligt_in_mrz_id = hdoId;
delete from volumestroom_type_bronnen where volumestroom_type_bronnen.bronnen_id in (select bronnen.bronnen_id from bronnen where bronnen.hdo_id = hdoId);
delete from bronnen where bronnen.hdo_id = hdoId;
delete from competent_authority_roles where competent_authority_roles.hdo_id = hdoId;
delete from hdo_identfiers where hdo_identfiers.hdo_id = hdoId;
delete from gw_testing_conclusion where gtcc_id in (select gtcc_id from gw_test_value where nrmv_id in (select nrmv_id from norm_value where nrm_id in (select nrm_id from norm_feature where norm_feature.hdo_id = hdoId)));
delete from gw_test_value where nrmv_id in (select nrmv_id from norm_value where nrm_id in (select nrm_id from norm_feature where hdo_id = hdoId));
delete from sw_testing_conclusion where stcc_id in (select stcc_id from sw_test_value where nrmv_id in (select nrmv_id from norm_value where nrm_id in (select nrm_id from norm_feature where norm_feature.hdo_id = hdoId)));
delete from sw_test_value where nrmv_id in (select nrmv_id from norm_value where nrm_id in (select nrm_id from norm_feature where norm_feature.hdo_id = hdoId));

alter table norm add column isdelete bool default false;
alter table norm_feature add column isdelete bool default false;

	FOR wbn IN select nrm_id from norm_feature where norm_feature.hdo_id = hdoId
	LOOP
		--RAISE NOTICE 'wbn.nrm_id: %', wbn.nrm_id;
		IF (select count(*) from norm_feature where norm_feature.nrm_id = wbn.nrm_id) <= 1 THEN
			--RAISE NOTICE 'true';
			delete from norm_value where nrm_id = wbn.nrm_id;
			update norm_feature set isdelete = true where norm_feature.nrm_id = wbn.nrm_id;
			update norm set isdelete = true where norm.nrm_id = wbn.nrm_id;
		END IF;
	END LOOP;

delete from norm_feature where norm_feature.hdo_id = hdoId;
delete from norm_feature where isdelete = true;
delete from norm where isdelete = true;
alter table norm_feature drop column isdelete;
alter table norm drop column isdelete;


delete from observed_feature where observed_feature.hdo_id = hdoId;
delete from planned_feature_monitoring where planned_feature_monitoring.hdo_id = hdoId;
delete from sampled_feature where sampled_feature.hdo_id = hdoId;
delete from hdo_names where hdo_names.hdo_id = hdoId;
delete from watercourse where wtc_id = hdoId;

delete from hydro_object where hdo_id = hdoId;

RETURN hdoId;
END;
$$;


--
-- Name: sp_get_chemischestoffen(integer, integer, integer[], integer[], integer); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION sp_get_chemischestoffen(jaar integer, wbd integer, wbds integer[], jaren integer[], ssg integer) RETURNS TABLE(opt_id integer, css_id integer, cls_id integer, classificatie text, numeric_value double precision, eumg_id integer, eumgcode text, wbpm_id integer, wbpmcode text, code text, chemstoftype text, year_of_status integer, progyear integer, progcls integer, progclsdesc text, scm_id integer)
    LANGUAGE sql
    AS $$

	select opt.opt_id, scss.css_id, cst.cls_id, cst.description as classificatie, obs.numeric_value,scss.eumg_id,  
	eumg.code as eumgcode ,wbpm.wbpm_id, wbpm.code as wbpmcode, css.code,css.naam as chemstoftype, sys.year_of_status, 
	wp.year as progyear, wp.cls_id as progcls, cstwp.description as progclsdesc, wscm.scm_id from 
	(SELECT substance_group_element.chs_id,      substance_group_element.ssge_id, cst.naam     
	FROM substance_group_element join chemische_stof_type cst on cst.chs_id = substance_group_element.chs_id   
	WHERE substance_group_element.ssge_id = ssg) as sub 
	join chemische_stof_type css on css.chs_id = sub.chs_id 
	join substance_group_element sge on sge.chs_id = sub.chs_id 
	join substance_group sg on sge.ssge_id = sg.ssg_id and sg.group_type = 'I' 
	join observed_property_type opt on sge.ssge_id = opt.substance_group_id 
	left join sw_chemical_substance_status scss on scss.opt_id = opt.opt_id 
	left join swb_yearly_status sys on sys.sys_id = scss.sys_id 
	left join sw_testing_conclusion stc on stc.sys_id = sys.sys_id 
	left join observation obs on obs.obs_id = stc.stcc_id and obs.observed_property_type = scss.opt_id 
	left join eu_monitoring_goal_type eumg on eumg.eumg_id = scss.eumg_id 
	left join classification_type cst on cst.cls_id = scss.swcs_id 
	left join waarde_bepalings_methode_type wbpm on wbpm.wbpm_id = scss.wbpm_id 
	left join wb_prognosis wp on wp.opt_id = opt.opt_id and wp.swb_id = wbd
	left join classification_type cstwp on cstwp.cls_id = wp.cls_id 
	left join wb_status_change_motivation wscm on wscm.wbd_id = wbd and wscm.opt_id = opt.opt_id and wscm.year_status = jaar
	where sys.swb_id = ANY(wbds) and sys.year_of_status = ANY(jaren) 
	group by opt.opt_id, scss.css_id, cst.cls_id, cst.description , obs.numeric_value,scss.eumg_id,  eumg.code  ,wbpm.wbpm_id, wbpm.code , css.code,css.naam , sys.year_of_status, wp.year , wp.cls_id , cstwp.description, scss.clt_id, wscm.scm_id

$$;


--
-- Name: sp_get_krm_langjarig_rapport(integer); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION sp_get_krm_langjarig_rapport(maxyear integer) RETURNS TABLE(pot_id integer, end_date double precision, qem_id integer, deviation_program_jn boolean, osc_id integer, idd_id integer, name character varying, mno_id integer, observationsucceed boolean, samplesucceed boolean)
    LANGUAGE plpgsql COST 1
    AS $$
BEGIN
	return query
with monitoringobject as (
	select moqe.qem_id, mo.mno_id, mo.name from monitoring_object mo
	join monitoring_object_quality_element moqe on mo.mno_id = moqe.mno_id
	where monitoring_object_type = 'DataSource'
	)
select pot.pot_id, 
    EXTRACT(YEAR from pot.end_date) as end_date, 
    qe.qem_id, 
    qe.deviation_program_jn, 
    oc.osc_id, 
    id.idd_id,
    id.name, 
    mo.mno_id, 
    oco_grp.is_approved as observationsucceed, 
    ocs_grp.is_approved as samplesucceed
    from domains.indicator_descriptor id
	left join monprog.quality_element_indicator_descriptor qeid on id.idd_id = qeid.idd_id
	left join quality_element qe on qeid.qem_id = qe.qem_id
	left join monprog.planned_observation_time pot on pot.qem_id = qe.qem_id
	left join observing_capability oc on oc.osc_id = qe.osc_id
	left join 
         (select oco.osc_id, oco.is_approved 
         from observing_capability_observation oco
         group by oco.osc_id, oco.is_approved
         ) as oco_grp
         on oco_grp.osc_id = oc.osc_id
	left join 
         (select ocs.osc_id, ocs.is_approved 
         from observing_capability_sample ocs
         group by ocs.osc_id, ocs.is_approved
         ) as ocs_grp
         on ocs_grp.osc_id = oc.osc_id
	left join monitoringobject mo on mo.qem_id = qe.qem_id
where id.part_of_idd_id is not null 
and (id.end_lifespan_version is null 
    or id.end_lifespan_version is not null 
    and id.end_lifespan_version > localtimestamp
    ) 
and EXTRACT(YEAR from pot.end_date) <= maxYear
group by pot.pot_id, 
pot.end_date, 
qe.qem_id, 
qe.deviation_program_jn, 
oc.osc_id, 
id.idd_id,
oco_grp.is_approved,
ocs_grp.is_approved, 
mo.mno_id
order by id.idd_id, oc.osc_id, qe.qem_id;

END
$$;


--
-- Name: sp_get_krm_toeleverancier_rapport(integer, integer); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION sp_get_krm_toeleverancier_rapport(oreid integer, year integer) RETURNS TABLE(pot_id integer, noof_samples integer, end_date double precision, qem_id integer, opt_id integer, meetlocatie integer, deviation_program_jn boolean, reason_deviation_program text, osc_id integer, is_sample boolean, idd_id integer, name character varying, datasource integer, observationsucceed boolean, samplesucceed boolean, ore_id integer, amountobservations bigint, amountsamples bigint)
    LANGUAGE plpgsql COST 1
    AS $$
BEGIN
	return query
	with monitoringobject as (
		select moqe.qem_id, mo.mno_id, mo.name from monitoring_object mo
		join monitoring_object_quality_element moqe on mo.mno_id = moqe.mno_id
		where monitoring_object_type = 'DataSource'
		)
	select pot.pot_id, pot.noof_samples, EXTRACT(YEAR from pot.end_date) as end_date, qe.qem_id, qe.opt_id, moqe.mno_id as meetlocatie, qe.deviation_program_jn, qe.reason_deviation_program, oc.osc_id, oc.is_sample, id.idd_id,id.name, 
		mo.mno_id as datasource, 
        oco_grp.is_approved as observationsucceed, 
        ocs_grp.is_approved as samplesucceed, 
        ocrp.ore_id, 
		(select count(1) from quality_element_observation qeo where qeo.qem_id = qe.qem_id) as amountobservations, (select count(1) from quality_element_sample qes where qes.qem_id = qe.qem_id) as amountsamples
	from domains.indicator_descriptor id
	left join monprog.quality_element_indicator_descriptor qeid on id.idd_id = qeid.idd_id
	left join quality_element qe on qeid.qem_id = qe.qem_id
	left join monitoring_object_quality_element moqe on qe.qem_id = moqe.qem_id
	left join monprog.planned_observation_time pot on pot.qem_id = qe.qem_id
	left join observing_capability oc on oc.osc_id = qe.osc_id
	left join observing_capability_responsible_party ocrp on ocrp.osc_id = oc.osc_id 
--	left join observing_capability_observation oco on oco.osc_id = oc.osc_id
--	left join observing_capability_sample ocs on ocs.osc_id = oc.osc_id
	left join 
         (select oco.osc_id, oco.is_approved 
         from observing_capability_observation oco
         group by oco.osc_id, oco.is_approved
         ) as oco_grp
         on oco_grp.osc_id = oc.osc_id
	left join 
         (select ocs.osc_id, ocs.is_approved 
         from observing_capability_sample ocs
         group by ocs.osc_id, ocs.is_approved
         ) as ocs_grp
         on ocs_grp.osc_id = oc.osc_id	left join monitoringobject mo on mo.qem_id = qe.qem_id
	where id.part_of_idd_id is not null 
    and   (id.end_lifespan_version is null 
          or id.end_lifespan_version is not null 
          and id.end_lifespan_version > localtimestamp
          ) 
    and ocrp.ore_id = oreid 
    and EXTRACT(YEAR from pot.end_date) = year
	group by pot.pot_id, 
    pot.noof_samples, 
    pot.end_date, 
    qe.qem_id, 
    qe.opt_id, 
    moqe.mno_id, 
    qe.deviation_program_jn, 
    oc.osc_id,
    oc.is_sample, 
    id.idd_id, 
    oco_grp.is_approved, 
    ocs_grp.is_approved,  
    mo.mno_id, 
    ocrp.ore_id
	order by id.idd_id, oc.osc_id, qe.qem_id;
END
$$;


--
-- Name: sp_get_thema_overzicht_belastingen(integer, integer[], integer[]); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION sp_get_thema_overzicht_belastingen(jaar integer, rbdids integer[], oreids integer[]) RETURNS TABLE(themaid integer, soort text, regel text)
    LANGUAGE sql
    AS $_$
SELECT themaid "ThemaId", soort "Soort", regel "Regel" FROM
(
	SELECT
	2 themaid, 'informatief'::text soort,  
	format(E'%1$s bevat %2$s significante belastingen voor %3$s waterlichamen.'
	|| E'<ul><li>Van deze %2$s belastingen is %4$s keer een functie en effect opgegeven (%5$s %%)</li></ul>', 
	description, aantal_significant, aantal_waterlichamen, aantal_opgegeven, percentage)::text regel,
	description volgorde,
	0 volgorde1
	FROM 
	(
	       SELECT 
	       rpp.description,
	       COUNT(DISTINCT p.wbp_id) aantal_significant,
	       COUNT(DISTINCT ho.hdo_id) aantal_waterlichamen,
	       COUNT( opgegeven.aantal_opgegeven) aantal_opgegeven,
	       ROUND(COUNT( opgegeven.aantal_opgegeven) * 1.0 / COUNT(DISTINCT p.wbp_id) * 100, 1) percentage
	       FROM (
			SELECT swbp_id wbp_id, swb_id wbd_id, is_significant FROM swb_pressure
			UNION
			SELECT gwbp_id wbp_id, gwb_id wbd_id, is_significant FROM gwb_pressure
	       ) p
	       JOIN (
		       SELECT swbp_id wbp_id, 
		       CASE WHEN char_length("function") > 0  AND char_length(effect) > 0 THEN swb_id ELSE null END aantal_opgegeven
		       FROM swb_pressure
		       UNION
		       SELECT gwbp_id wbp_id, 
		       CASE WHEN char_length("function") > 0  AND char_length(effect) > 0 THEN gwb_id ELSE null END aantal_opgegeven
		       FROM gwb_pressure
	       ) opgegeven ON opgegeven.wbp_id = p.wbp_id
	       JOIN hydro_object ho ON ho.hdo_id = p.wbd_id
	       JOIN report_version rv ON rv.wkrv_id = ho.wkrv_id
	       JOIN reporting_period rpp ON rpp.rpp_id = rv.rpp_id AND (rv.begin_lifespan_version <= now() AND (rv.end_lifespan_version IS NULL OR (rv.end_lifespan_version IS NOT NULL AND rv.end_lifespan_version > now())))
	       JOIN namespace_type wb_nst ON wb_nst.ns_id = ho.inspire_id_namespace
	       JOIN competentauthority_namespacetype ON competentauthority_namespacetype.ns_id = wb_nst.ns_id
	       JOIN competent_authority ON competent_authority.ore_id = competentauthority_namespacetype.ore_id
	       --JOIN oreids ON oreids.ore_id = competent_authority.ore_id AND oreids.aggregratiesoort = $2
	       JOIN water_body ON water_body.wbd_id = ho.hdo_id
	       --JOIN rbdids ON rbdids.rbd_id = water_body.rbd_id AND rbdids.aggregratiesoort = $2		
	       WHERE p.is_significant and competent_authority.ore_id = ANY(oreids) and water_body.rbd_id = ANY (rbdids) AND rpp.leg_ref_id = 1
	       GROUP BY rpp.description
	) x
	UNION
	-- belastingen
	SELECT
	2 themaid, 'informatief'::text soort,  
	format('%1$s bevat %2$s waterlichamen zonder significante belastingen', 
	description, aantal_waterlichamen)::text regel,
	description volgorde,
	1 volgorde1
	FROM 
	(
	       SELECT 
	       rpp.description,
	       COUNT(DISTINCT ho.hdo_id) aantal_waterlichamen
	       FROM (
			SELECT swb_id wbd_id, 
			COUNT(DISTINCT CASE WHEN is_significant THEN swb_id ELSE NULL END) aantal_significant, 
			COUNT(DISTINCT CASE WHEN NOT(is_significant) THEN swb_id ELSE NULL END) aantal_niet_significant
			FROM swb_pressure
			GROUP BY swb_id
			UNION
			SELECT gwb_id wbd_id, 
			COUNT(DISTINCT CASE WHEN is_significant THEN gwb_id ELSE NULL END) aantal_significant, 
			COUNT(DISTINCT CASE WHEN NOT(is_significant) THEN gwb_id ELSE NULL END) aantal_niet_significant
			FROM gwb_pressure
			GROUP BY gwb_id
	       ) p
	       JOIN hydro_object ho ON ho.hdo_id = p.wbd_id
	       JOIN report_version rv ON rv.wkrv_id = ho.wkrv_id
	       JOIN reporting_period rpp ON rpp.rpp_id = rv.rpp_id AND (rv.begin_lifespan_version <= now() AND (rv.end_lifespan_version IS NULL OR (rv.end_lifespan_version IS NOT NULL AND rv.end_lifespan_version > now())))
	       JOIN namespace_type wb_nst ON wb_nst.ns_id = ho.inspire_id_namespace
	       JOIN competentauthority_namespacetype ON competentauthority_namespacetype.ns_id = wb_nst.ns_id
	       JOIN competent_authority ON competent_authority.ore_id = competentauthority_namespacetype.ore_id
	       JOIN water_body ON water_body.wbd_id = ho.hdo_id
	       WHERE p.aantal_niet_significant = 1 and competent_authority.ore_id = ANY(oreids) and water_body.rbd_id = ANY (rbdids) AND rpp.leg_ref_id = 1
	       GROUP BY rpp.description
	) x
	UNION
	-- belastingen
	SELECT
	2 themaid, 'informatief'::text soort,  
	format('%1$s bevat %2$s belastingen die aangemerkt zijn als belangrijk en %3$s belastingen die zijn aangemerkt als zeer belangrijk', 
	description, aantal_belangrijk, aantal_zeer_belangrijk)::text regel,
	description volgorde,
	2 volgorde1
	FROM 
	(
	       SELECT 
	       rpp.description,
	       COUNT(DISTINCT p.gwbp_id) aantal_belastingen,
	       SUM(belangrijk) aantal_belangrijk,
	       SUM(zeer_belangrijk) aantal_zeer_belangrijk
	       FROM gwb_pressure p
	       JOIN (
		       SELECT gwbp_id,
		       CASE WHEN omschrijving = 'Niet belangrijk' THEN 1 ELSE 0 END niet_belangrijk,
		       CASE WHEN omschrijving = 'Belangrijk' THEN 1 ELSE 0 END belangrijk,
		       CASE WHEN omschrijving = 'Zeer belangrijk' THEN 1 ELSE 0 END zeer_belangrijk
		       FROM gwb_pressure
		       JOIN pressure_relevance_type ON pressure_relevance_type.prr_id = gwb_pressure.prr_id
	       ) aantal ON aantal.gwbp_id = p.gwbp_id
	       JOIN hydro_object ho ON ho.hdo_id = p.gwb_id
	       JOIN report_version rv ON rv.wkrv_id = ho.wkrv_id
	       JOIN reporting_period rpp ON rpp.rpp_id = rv.rpp_id AND (rv.begin_lifespan_version <= now() AND (rv.end_lifespan_version IS NULL OR (rv.end_lifespan_version IS NOT NULL AND rv.end_lifespan_version > now())))
	       JOIN namespace_type wb_nst ON wb_nst.ns_id = ho.inspire_id_namespace
	       JOIN competentauthority_namespacetype ON competentauthority_namespacetype.ns_id = wb_nst.ns_id
	       JOIN competent_authority ON competent_authority.ore_id = competentauthority_namespacetype.ore_id
	       JOIN water_body ON water_body.wbd_id = ho.hdo_id
	       WHERE competent_authority.ore_id = ANY(oreids) and water_body.rbd_id = ANY (rbdids) AND rpp.leg_ref_id = 1
	       GROUP BY rpp.description
	) x
) z
ORDER BY jaar, themaid, volgorde, volgorde1, regel;
$_$;


--
-- Name: sp_get_thema_overzicht_maatregelen(integer, integer[], integer[]); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION sp_get_thema_overzicht_maatregelen(jaar integer, rbdids integer[], oreids integer[]) RETURNS TABLE(themaid integer, soort text, regel text)
    LANGUAGE sql
    AS $_$
SELECT themaid "ThemaId", soort "Soort", regel "Regel" FROM
(
	-- maatregelen
	SELECT 
	3 themaid, 'informatief'::text soort,  
	format('%1$s bevat %2$s SGBP-maatregelen met uitvoering in %3$s voor %4$s waterlichamen', 
	description, aantal_maatregelen, substring(uitvoeren_in, char_length(uitvoeren_in)-8), aantal_waterlichamen)::text regel,
	description||uitvoeren_in volgorde,
	0 volgorde1
	FROM 
	(
	       SELECT 
	       rpp.description,
	       rpp1.description uitvoeren_in,
	       COUNT(DISTINCT mm.mtm_id) aantal_maatregelen,
	       COUNT(DISTINCT ho.hdo_id) aantal_waterlichamen
	       FROM monitored_measure mm
	       JOIN report_version rv1 ON rv1.rpv_id = mm.rpv_id
	       JOIN reporting_period rpp1 ON rpp1.rpp_id = rv1.rpp_id
	       JOIN supp_add_measure_type samt ON samt.smnl_id = mm.mtm_id
	       JOIN river_basin_management_plan rbmp ON rbmp.rbmp_id = samt.rbmp_id
	       JOIN report_version rv ON rv.wkrv_id = rbmp.wkrv_id
	       JOIN reporting_period rpp ON rpp.rpp_id = rv.rpp_id AND (rv.begin_lifespan_version <= now() AND (rv.end_lifespan_version IS NULL OR (rv.end_lifespan_version IS NOT NULL AND rv.end_lifespan_version > now())))
	       JOIN namespace_type wb_nst ON wb_nst.ns_id = samt.namespace_id
	       JOIN competentauthority_namespacetype ON competentauthority_namespacetype.ns_id = wb_nst.ns_id
	       JOIN competent_authority ON competent_authority.ore_id = competentauthority_namespacetype.ore_id
	       JOIN measure_per_man_reg_or_restr_zone mz ON mz.mtm_id = mm.mtm_id
	       JOIN hydro_object ho ON ho.hdo_id = mz.mrz_id
	       JOIN water_body ON water_body.wbd_id = ho.hdo_id
	       WHERE samt.is_additional_measure AND rpp1.rpp_id >= 2 AND ho.hydro_object_type IN ('SurfaceWaterBody', 'GroundWaterBody') AND 
			((rpp.rpp_id = 2 AND rpp1.rpp_id = 2) OR (rpp.rpp_id > 2 AND rpp1.rpp_id > 2))
	and competent_authority.ore_id = ANY(oreids) and water_body.rbd_id = ANY(rbdids) AND rpp.leg_ref_id = 1
	       GROUP BY rpp.description, rpp1.rpp_id, rpp1.description
	       

	) x
) z
ORDER BY jaar, themaid, volgorde, volgorde1, regel;
$_$;


--
-- Name: sp_get_thema_overzicht_motiveringstatus(integer, integer[], integer[]); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION sp_get_thema_overzicht_motiveringstatus(jaar integer, rbdids integer[], oreids integer[]) RETURNS TABLE(themaid integer, soort text, regel text)
    LANGUAGE sql
    AS $_$
SELECT themaid "ThemaId", soort "Soort", regel "Regel" FROM
(
	-- motivering status
	SELECT
	5 themaid, 'informatief'::text soort, 
	format(E'%1$s bevat %2$s waterlichamen, waarvan %3$s met een status sterk veranderd.<ul>'
	 || E'<li>Voor %4$s van deze %3$s waterlichamen zijn geschade gebruiksfuncties opgegeven (%5$s%%)</li>'
	 || E'<li>Voor %6$s van deze %3$s waterlichamen zijn redenen opgegeven voor het verwerpen van beschouwde alternatieven (%7$s%%)</li>'
	 || E'<li>Voor %8$s van deze %3$s waterlichamen is een motivering ingevuld voor het verwerpen van beschouwde alternatieven (%9$s%%)</li></ul>', 
	description, aantal_waterlichamen, aantal_waterlichamen_sterk_veranderd, aantal_geschade_gebruiks_functies, percentage_geschade_gebruiks_functies,
	aantal_reden_opgegeven, percentage_reden_opgegeven, aantal_een_motivering_ingevuld, percentage_aantal_een_motivering_ingevuld)::text regel,	
	description volgorde,
	0 volgorde1
	FROM 
	(
		SELECT
		rpp.description,
		COUNT(DISTINCT ho.hdo_id) aantal_waterlichamen,
		COUNT(DISTINCT swb.swb_id)  aantal_waterlichamen_sterk_veranderd,
		COUNT(DISTINCT sm43.swb_id) aantal_geschade_gebruiks_functies,
		CASE WHEN COUNT(DISTINCT swb.swb_id) = 0 THEN 100.0 ELSE ROUND((COUNT(DISTINCT sm43.swb_id) * 1.0 / COUNT(DISTINCT swb.swb_id)) * 100, 1) END  percentage_geschade_gebruiks_functies,
		COUNT(DISTINCT sart43.swb_id) aantal_reden_opgegeven,
		CASE WHEN COUNT(DISTINCT swb.swb_id) = 0 THEN 100.0 ELSE ROUND((COUNT(DISTINCT sart43.swb_id) * 1.0 / COUNT(DISTINCT swb.swb_id)) * 100, 1) END  percentage_reden_opgegeven,
		COUNT(DISTINCT (CASE WHEN char_length(swb.article_43b_motivation) >= 1 THEN swb.swb_id ELSE NULL END)) aantal_een_motivering_ingevuld,
		CASE WHEN COUNT(DISTINCT swb.swb_id) = 0 THEN 100.0 ELSE ROUND(((COUNT(DISTINCT (CASE WHEN char_length(swb.article_43b_motivation) >= 1 THEN swb.swb_id ELSE NULL END))) * 1.0 / COUNT(DISTINCT swb.swb_id)) * 100, 1) END  percentage_aantal_een_motivering_ingevuld
		FROM hydro_object ho
		LEFT JOIN surface_water_body swb ON swb.swb_id = ho.hdo_id AND swb.is_heaviliy_modified
		LEFT JOIN swb_measures_43a sm43 ON sm43.swb_id = swb.swb_id
		LEFT JOIN swb_art43b_excemption sart43 ON sart43.swb_id = swb.swb_id
		JOIN report_version rv ON rv.wkrv_id = ho.wkrv_id
		JOIN reporting_period rpp ON rpp.rpp_id = rv.rpp_id AND (rv.begin_lifespan_version <= now() AND (rv.end_lifespan_version IS NULL OR (rv.end_lifespan_version IS NOT NULL AND rv.end_lifespan_version > now())))
		JOIN namespace_type wb_nst ON wb_nst.ns_id = ho.inspire_id_namespace
		JOIN competentauthority_namespacetype ON competentauthority_namespacetype.ns_id = wb_nst.ns_id
		JOIN competent_authority ON competent_authority.ore_id = competentauthority_namespacetype.ore_id
		JOIN water_body ON water_body.wbd_id = ho.hdo_id
		WHERE ho.hydro_object_type = 'SurfaceWaterBody' and water_body.rbd_id = ANY(rbdids) and competent_authority.ore_id = ANY(oreids) AND rpp.leg_ref_id = 1
		GROUP BY rpp.description		
	) x
) z
ORDER BY jaar, themaid, volgorde, volgorde1, regel;
$_$;


--
-- Name: sp_get_thema_overzicht_toestand(integer, integer[], integer[]); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION sp_get_thema_overzicht_toestand(jaar integer, rbdids integer[], oreids integer[]) RETURNS TABLE(themaid integer, soort text, regel text)
    LANGUAGE sql
    AS $_$
SELECT themaid "ThemaId", soort "Soort", regel "Regel" FROM
(
	-- toestand
	SELECT
	4 themaid, 'informatief'::text soort, 
	format('Op %1$s is de meest recente set toestandsoordelen vastgesteld in de Aquo-kit en ingelezen in het WKP.', 
		to_char(ingelezen_op, 'DD-MM-YYYY'))::text regel,
	'' volgorde,
	0 volgorde1
	FROM 
	(
		SELECT ingelezen_op
		FROM 
		(
			SELECT
			ys.last_change_time::date ingelezen_op
			FROM gwb_yearly_status ys
			JOIN hydro_object ho ON ho.hdo_id = ys.gwb_id
			JOIN namespace_type wb_nst ON wb_nst.ns_id = ho.inspire_id_namespace
			JOIN competentauthority_namespacetype ON competentauthority_namespacetype.ns_id = wb_nst.ns_id
			JOIN competent_authority ON competent_authority.ore_id = competentauthority_namespacetype.ore_id
			JOIN water_body ON water_body.wbd_id = ho.hdo_id
			WHERE ys.year_of_status = jaar and water_body.rbd_id = ANY(rbdids) and competent_authority.ore_id = ANY(oreids)
			UNION
			SELECT
			ys.last_change_time::date ingelezen_op
			FROM sw_testing_conclusion stc
			JOIN swb_yearly_status ys ON ys.sys_id = stc.sys_id
			JOIN hydro_object ho ON ho.hdo_id = ys.swb_id
			JOIN namespace_type wb_nst ON wb_nst.ns_id = ho.inspire_id_namespace
			JOIN competentauthority_namespacetype ON competentauthority_namespacetype.ns_id = wb_nst.ns_id
			JOIN competent_authority ON competent_authority.ore_id = competentauthority_namespacetype.ore_id
			JOIN water_body ON water_body.wbd_id = ho.hdo_id
			WHERE ys.year_of_status = jaar and water_body.rbd_id = ANY(rbdids) and competent_authority.ore_id = ANY(oreids) 
		) s
		ORDER BY ingelezen_op DESC LIMIT 1
	) x
	WHERE 1 = 1
	UNION
	-- toestand
	SELECT
	4 themaid, 'informatief'::text soort, 
	format('Van de %1$s waterlichamen in %2$s is er %3$s keer sprake van wateronttrekking voor menselijke consumptie. Van deze %3$s is<ul>'
	|| E'<li>%4$s keer opgegeven dat er sprake is van een normoverschrijding</li>'
	|| E'<li>%5$s keer opgegeven dat er sprake is van een aanvullende zuiveringsinspanning</li></ul>', 
	aantal_waterlichamen, description, aantal_water_ontrekkingen, aantal_normoverschrijdingen, aantal_aanvullende_zuiverings_inspanningen)::text regel,
	'' volgorde,
	1 volgorde1
	FROM 
	(
		SELECT
		rpp.description,
		COUNT(DISTINCT ho.hdo_id) aantal_waterlichamen,
		COUNT(DISTINCT wb.wbd_id) aantal_water_ontrekkingen,
		SUM(ys.exceeds_drinkingwater::int) aantal_normoverschrijdingen,
		SUM(ys.additional_purification::int) aantal_aanvullende_zuiverings_inspanningen
		FROM hydro_object ho
		LEFT JOIN water_body wb ON wb.wbd_id = ho.hdo_id AND wb.is_article_7
		LEFT JOIN swb_yearly_status ys ON ys.swb_id = ho.hdo_id
		JOIN report_version rv ON rv.wkrv_id = ho.wkrv_id
		JOIN reporting_period rpp ON rpp.rpp_id = rv.rpp_id AND (rv.begin_lifespan_version <= now() AND (rv.end_lifespan_version IS NULL OR (rv.end_lifespan_version IS NOT NULL AND rv.end_lifespan_version > now())))
		JOIN namespace_type wb_nst ON wb_nst.ns_id = ho.inspire_id_namespace
		JOIN competentauthority_namespacetype ON competentauthority_namespacetype.ns_id = wb_nst.ns_id
		JOIN competent_authority ON competent_authority.ore_id = competentauthority_namespacetype.ore_id
		JOIN water_body ON water_body.wbd_id = ho.hdo_id
		WHERE ho.hydro_object_type = 'SurfaceWaterBody' AND ys.year_of_status = jaar AND rpp.rpp_id = 3 /* SGBP2 */ 
		and water_body.rbd_id = ANY(rbdids) and competent_authority.ore_id = ANY(oreids) AND rpp.leg_ref_id = 1
		GROUP BY rpp.description	
		HAVING COUNT(DISTINCT ho.hdo_id) > 0
	) x
	WHERE aantal_water_ontrekkingen > 0
	UNION
	-- toestand
 	SELECT
 	4 themaid, 'status'::text thema, 
 	format('Voor %1$s van de %2$s waterlichamen is een toestandsoordeel aanwezig voor alle relevante biologische kwaliteitselementen (%3$s%%)<img alt="%4$s" title="%4$s" src="[CONTENT_URL]/%4$s.png" />', 
 	aantal_biologische_compleet, aantal_waterlichamen, percentage_biologische_compleet, tos.name)::text regel,
 	'' volgorde,
 	2 volgorde1
 	FROM 
 	(
		SELECT 
		COUNT(DISTINCT ho.hdo_id) aantal_waterlichamen,
		COUNT(DISTINCT iscompleet.compleet) aantal_biologische_compleet,
		ROUND((COUNT(DISTINCT iscompleet.compleet) * 1.0 / COUNT(DISTINCT ho.hdo_id)) * 100, 1)  percentage_biologische_compleet
		FROM hydro_object ho 
		LEFT JOIN
		(
			SELECT 
				surface_water_body.swb_id wbd_id,
				CASE WHEN COUNT(wfd_qe_swtype.opt_id) - COUNT(sw_ecological_status.sys_id) = 0 THEN surface_water_body.swb_id ELSE NULL END compleet
			FROM wfd_qe_swtype
			JOIN sw_typology_type ON sw_typology_type.styp_id = wfd_qe_swtype.swt_id
			JOIN vw_waargenomen_eigenschap ON vw_waargenomen_eigenschap."DB_ID" = wfd_qe_swtype.opt_id
			JOIN (
				SELECT DISTINCT "Waargenomen_Eigenschap_DB_ID" opt_id
				FROM vw_krw_kwaliteitselement
				WHERE "Onderdeel_van_Typering_Code" = 'BIOLT'
			) ke ON ke.opt_id = wfd_qe_swtype.opt_id
			JOIN surface_water_body ON surface_water_body.sw_typology_id = sw_typology_type.styp_id
			LEFT JOIN swb_yearly_status ON swb_yearly_status.swb_id = surface_water_body.swb_id
				AND swb_yearly_status.year_of_status = jaar
			LEFT JOIN sw_ecological_status ON sw_ecological_status.sys_id = swb_yearly_status.sys_id 
				AND sw_ecological_status.opt_id = vw_waargenomen_eigenschap."DB_ID"
			WHERE wfd_qe_swtype.is_applicable
			GROUP BY surface_water_body.swb_id
		) iscompleet ON iscompleet.wbd_id = ho.hdo_id
		JOIN report_version rv ON rv.wkrv_id = ho.wkrv_id
		JOIN reporting_period rpp ON rpp.rpp_id = rv.rpp_id AND (rv.begin_lifespan_version <= now() AND (rv.end_lifespan_version IS NULL OR (rv.end_lifespan_version IS NOT NULL AND rv.end_lifespan_version > now())))
		JOIN namespace_type wb_nst ON wb_nst.ns_id = ho.inspire_id_namespace
		JOIN competentauthority_namespacetype ON competentauthority_namespacetype.ns_id = wb_nst.ns_id
		JOIN competent_authority ON competent_authority.ore_id = competentauthority_namespacetype.ore_id
		JOIN water_body ON water_body.wbd_id = ho.hdo_id
		WHERE ho.hydro_object_type = 'SurfaceWaterBody' AND rpp.rpp_id = 3 /* SGBP2 */
		and water_body.rbd_id = ANY(rbdids) and competent_authority.ore_id = ANY(oreids) AND rpp.leg_ref_id = 1
		HAVING COUNT(DISTINCT ho.hdo_id) > 0
 	) x
	JOIN thema_overzicht_statussen tos ON tos.t_id = 4 AND tos.start_range <= x.percentage_biologische_compleet AND tos.end_range >= x.percentage_biologische_compleet
	UNION
	-- toestand
	SELECT
	4 themaid, 'status'::text thema, 
 	format('Voor %1$s van de %2$s waterlichamen zijn alle benodigde algemeen fysisch-chemische kwaliteitselementen aanwezig (%3$s%%)<img alt="%4$s" title="%4$s" src="[CONTENT_URL]/%4$s.png" />', 
 	aantal_biologische_compleet, aantal_waterlichamen, percentage_biologische_compleet, tos.name)::text regel,
	'' volgorde,
 	3 volgorde1
 	FROM 
 	(
		SELECT 
		COUNT(DISTINCT ho.hdo_id) aantal_waterlichamen,
		COUNT(DISTINCT iscompleet.compleet) aantal_biologische_compleet,
		ROUND((COUNT(DISTINCT iscompleet.compleet) * 1.0 / COUNT(DISTINCT ho.hdo_id)) * 100, 1)  percentage_biologische_compleet
		FROM hydro_object ho 
		LEFT JOIN
		(
			SELECT 
				surface_water_body.swb_id wbd_id,
				CASE WHEN COUNT(DISTINCT wfd_qe_swtype.opt_id) - COUNT(DISTINCT observation.observed_property_type) = 0 THEN surface_water_body.swb_id ELSE NULL END compleet
			FROM wfd_qe_swtype
			JOIN sw_typology_type ON sw_typology_type.styp_id = wfd_qe_swtype.swt_id
			JOIN (
				SELECT DISTINCT "Waargenomen_Eigenschap_DB_ID" opt_id
				FROM vw_krw_kwaliteitselement
				WHERE "Onderdeel_van_Typering_Code" IN ('FYSCHEM', 'NUTRIENT') AND "Tpering_Code" IS NULL
			) ke ON ke.opt_id = wfd_qe_swtype.opt_id
			JOIN surface_water_body ON surface_water_body.sw_typology_id = sw_typology_type.styp_id
			LEFT JOIN swb_yearly_status ON swb_yearly_status.swb_id = surface_water_body.swb_id
				AND swb_yearly_status.year_of_status = jaar
			LEFT JOIN sw_testing_conclusion ON sw_testing_conclusion.sys_id = swb_yearly_status.sys_id
			LEFT JOIN observation ON observation.obs_id = sw_testing_conclusion.stcc_id
				AND observation.observed_property_type = wfd_qe_swtype.opt_id
			WHERE wfd_qe_swtype.is_applicable 
			GROUP BY surface_water_body.swb_id
		) iscompleet ON iscompleet.wbd_id = ho.hdo_id
		JOIN report_version rv ON rv.wkrv_id = ho.wkrv_id
		JOIN reporting_period rpp ON rpp.rpp_id = rv.rpp_id AND (rv.begin_lifespan_version <= now() AND (rv.end_lifespan_version IS NULL OR (rv.end_lifespan_version IS NOT NULL AND rv.end_lifespan_version > now())))
		JOIN namespace_type wb_nst ON wb_nst.ns_id = ho.inspire_id_namespace
		JOIN competentauthority_namespacetype ON competentauthority_namespacetype.ns_id = wb_nst.ns_id
		JOIN competent_authority ON competent_authority.ore_id = competentauthority_namespacetype.ore_id
		JOIN water_body ON water_body.wbd_id = ho.hdo_id
		WHERE ho.hydro_object_type = 'SurfaceWaterBody' AND rpp.rpp_id = 3 /* SGBP2 */
		and water_body.rbd_id = ANY(rbdids) and competent_authority.ore_id = ANY(oreids) AND rpp.leg_ref_id = 1
		HAVING COUNT(DISTINCT ho.hdo_id) > 0
 	) x
	JOIN thema_overzicht_statussen tos ON tos.t_id = 4 AND tos.start_range <= x.percentage_biologische_compleet AND tos.end_range >= x.percentage_biologische_compleet
	UNION
	-- toestand
	SELECT
	4 themaid, 'status'::text thema, 
 	format('Voor %1$s van de %2$s waterlichamen zijn alle toetsen uitgevoerd (%3$s%%)<img alt="%4$s" title="%4$s" src="[CONTENT_URL]/%4$s.png" />', 
 	aantal_toetsen_compleet, aantal_waterlichamen, percentage_toetsen_compleet, tos.name)::text regel,
	'' volgorde,
 	4 volgorde1
 	FROM 
 	(
		SELECT 
		COUNT(DISTINCT ho.hdo_id) aantal_waterlichamen,
		COUNT(DISTINCT iscompleet.compleet) aantal_toetsen_compleet,
		ROUND((COUNT(DISTINCT iscompleet.compleet) * 1.0 / COUNT(DISTINCT ho.hdo_id)) * 100, 1)  percentage_toetsen_compleet
		FROM hydro_object ho 
		LEFT JOIN
		(
			SELECT 
			vw_oordeel_totaal_gwl."Waterlichaam_DB_ID" wbd_id,
			CASE WHEN (SELECT COUNT(vw_waargenomen_eigenschap."DB_ID")
			FROM wfd_qe_gwtype
			JOIN vw_waargenomen_eigenschap ON vw_waargenomen_eigenschap."DB_ID" = wfd_qe_gwtype.opt_id
				AND wfd_qe_gwtype.is_applicable AND wfd_qe_gwtype.is_excemption) - COUNT(vw_waargenomen_eigenschap."DB_ID") = 0
				THEN vw_oordeel_totaal_gwl."Waterlichaam_DB_ID"
				ELSE NULL
			END compleet

			FROM wfd_qe_gwtype
			JOIN vw_waargenomen_eigenschap ON vw_waargenomen_eigenschap."DB_ID" = wfd_qe_gwtype.opt_id
				AND wfd_qe_gwtype.is_applicable AND wfd_qe_gwtype.is_excemption	
			RIGHT OUTER JOIN vw_oordeel_totaal_gwl ON vw_oordeel_totaal_gwl."Rapportagejaar" = jaar
				AND vw_oordeel_totaal_gwl."Waargenomen_Eigenschap_DB_ID" = vw_waargenomen_eigenschap."DB_ID"
			JOIN ground_water_body ON ground_water_body.gwb_id = vw_oordeel_totaal_gwl."Waterlichaam_DB_ID"
			GROUP BY vw_oordeel_totaal_gwl."Waterlichaam_DB_ID"

		) iscompleet ON iscompleet.wbd_id = ho.hdo_id
		JOIN report_version rv ON rv.wkrv_id = ho.wkrv_id
		JOIN reporting_period rpp ON rpp.rpp_id = rv.rpp_id AND (rv.begin_lifespan_version <= now() AND (rv.end_lifespan_version IS NULL OR (rv.end_lifespan_version IS NOT NULL AND rv.end_lifespan_version > now())))
		JOIN namespace_type wb_nst ON wb_nst.ns_id = ho.inspire_id_namespace
		JOIN competentauthority_namespacetype ON competentauthority_namespacetype.ns_id = wb_nst.ns_id
		JOIN competent_authority ON competent_authority.ore_id = competentauthority_namespacetype.ore_id
		JOIN water_body ON water_body.wbd_id = ho.hdo_id
		WHERE ho.hydro_object_type = 'GroundWaterBody' AND rpp.rpp_id = 3 /* SGBP2 */
		and water_body.rbd_id = ANY(rbdids) and competent_authority.ore_id = ANY(oreids) AND rpp.leg_ref_id = 1
		HAVING COUNT(DISTINCT ho.hdo_id) > 0
 	) x
	JOIN thema_overzicht_statussen tos ON tos.t_id = 4 AND tos.start_range <= x.percentage_toetsen_compleet AND tos.end_range >= x.percentage_toetsen_compleet
	UNION
	-- toestand
 	SELECT
 	4 themaid, 'status'::text thema, 
 	format('Voor %1$s van de %2$s waterlichamen is prognose 2021 volledig ingevuld (%3$s%%)<img alt="%4$s" title="%4$s" src="[CONTENT_URL]/%4$s.png" />', 
 	aantal_prognoses_compleet, aantal_waterlichamen, percentage_prognoses_compleet, tos.name)::text regel,
	'' volgorde,
 	5 volgorde1
 	FROM 
 	(
		SELECT 
		COUNT(DISTINCT ho.hdo_id) aantal_waterlichamen,
		COUNT(DISTINCT iscompleet.compleet) aantal_prognoses_compleet,
		ROUND((COUNT(DISTINCT iscompleet.compleet) * 1.0 / (CASE WHEN(COUNT(DISTINCT ho.hdo_id)) = 0 THEN 1 ELSE (COUNT(DISTINCT ho.hdo_id))  END )) * 100, 1)  percentage_prognoses_compleet
		FROM hydro_object ho 
		LEFT JOIN
		(
			SELECT 
				surface_water_body.swb_id wbd_id,
				CASE WHEN COUNT(vw_waargenomen_eigenschap."DB_ID") - COUNT(wb_prognosis.swb_id) = 0 THEN surface_water_body.swb_id ELSE NULL END compleet
			FROM wfd_qe_swtype
			JOIN sw_typology_type ON sw_typology_type.styp_id = wfd_qe_swtype.swt_id
			JOIN vw_waargenomen_eigenschap ON vw_waargenomen_eigenschap."DB_ID" = wfd_qe_swtype.opt_id
				AND vw_waargenomen_eigenschap."DB_ID" NOT IN (4476, 4479, 4480, 4520)		
			JOIN surface_water_body ON surface_water_body.sw_typology_id = sw_typology_type.styp_id
			LEFT JOIN wb_prognosis ON wb_prognosis.swb_id = surface_water_body.swb_id 
				AND wb_prognosis.year = 2021 
				AND wb_prognosis.opt_id = vw_waargenomen_eigenschap."DB_ID"
			WHERE wfd_qe_swtype.is_applicable
			GROUP BY surface_water_body.swb_id

			UNION 

			SELECT 
			wb_prognosis.swb_id wbd_id,
			CASE WHEN (SELECT COUNT(vw_waargenomen_eigenschap."DB_ID")
			FROM wfd_qe_gwtype
			JOIN vw_waargenomen_eigenschap ON vw_waargenomen_eigenschap."DB_ID" = wfd_qe_gwtype.opt_id
				AND wfd_qe_gwtype.is_applicable AND wfd_qe_gwtype.is_excemption) - COUNT(vw_waargenomen_eigenschap."DB_ID") = 0
				THEN wb_prognosis.swb_id
				ELSE NULL
			END compleet

			FROM wfd_qe_gwtype
			JOIN vw_waargenomen_eigenschap ON vw_waargenomen_eigenschap."DB_ID" = wfd_qe_gwtype.opt_id
				AND wfd_qe_gwtype.is_applicable AND wfd_qe_gwtype.is_excemption	
			RIGHT OUTER JOIN wb_prognosis ON wb_prognosis.year = 2021 
				AND wb_prognosis.opt_id = vw_waargenomen_eigenschap."DB_ID"
			JOIN ground_water_body ON ground_water_body.gwb_id = wb_prognosis.swb_id
			GROUP BY wb_prognosis.swb_id

		) iscompleet ON iscompleet.wbd_id = ho.hdo_id
		JOIN report_version rv ON rv.wkrv_id = ho.wkrv_id
		JOIN reporting_period rpp ON rpp.rpp_id = rv.rpp_id AND (rv.begin_lifespan_version <= now() AND (rv.end_lifespan_version IS NULL OR (rv.end_lifespan_version IS NOT NULL AND rv.end_lifespan_version > now())))
		JOIN namespace_type wb_nst ON wb_nst.ns_id = ho.inspire_id_namespace
		JOIN competentauthority_namespacetype ON competentauthority_namespacetype.ns_id = wb_nst.ns_id
		JOIN competent_authority ON competent_authority.ore_id = competentauthority_namespacetype.ore_id
		JOIN water_body ON water_body.wbd_id = ho.hdo_id
		WHERE ho.hydro_object_type IN ('SurfaceWaterBody', 'GroundWaterBody') AND rpp.rpp_id = 3 /* SGBP2 */
		and water_body.rbd_id = ANY(rbdids) and competent_authority.ore_id = ANY(oreids) AND rpp.leg_ref_id = 1
		
 	) x
	JOIN thema_overzicht_statussen tos ON tos.t_id = 4 AND tos.start_range <= x.percentage_prognoses_compleet AND tos.end_range >= x.percentage_prognoses_compleet
) z
ORDER BY jaar, themaid, volgorde, volgorde1, regel;
$_$;


--
-- Name: sp_get_thema_overzicht_uitzonderingsbepalingen(integer, integer[], integer[]); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION sp_get_thema_overzicht_uitzonderingsbepalingen(jaar integer, rbdids integer[], oreids integer[]) RETURNS TABLE(themaid integer, soort text, regel text)
    LANGUAGE sql
    AS $_$
SELECT themaid "ThemaId", soort "Soort", regel "Regel" FROM
(
	-- uitzonderingsbepalingen
	SELECT
	6 themaid, 'informatief'::text soort, 
	format('Voor %1$s van de %2$s waterlichamen in %3$s zijn één of meer uitzonderingsbepalingen ingevoerd. Het betreft:<ul>'
	|| E'<li>%4$s keer artikel 4.4 (fasering)</li>'
	|| E'<li>%5$s keer artikel 4.6 (tijdelijke achteruitgang)</li>'
	|| E'<li>%6$s keer artikel 4.7 (nieuwe ontwikkelingen)</li></ul>', 
	aantal_uitzonderings_bepalingen, aantal_waterlichamen, description, aantal_artikel_44, aantal_artikel_46, aantal_artikel_47)::text regel,
	'' volgorde,
	0 volgorde1 
	FROM 
	(
		SELECT
		rpp.description,
		COUNT(DISTINCT ho.hdo_id) aantal_waterlichamen,
		COUNT(DISTINCT ub.wbd_id) aantal_uitzonderings_bepalingen,
		COUNT(DISTINCT ub.is_artikel_44) aantal_artikel_44,
		COUNT(DISTINCT ub.is_artikel_46) aantal_artikel_46,
		COUNT(DISTINCT ub.is_artikel_47) aantal_artikel_47
		FROM hydro_object ho
		LEFT JOIN
		(  
			SELECT wbd_id, MAX(is_artikel_44) is_artikel_44, MAX(is_artikel_46) is_artikel_46, MAX(is_artikel_47) is_artikel_47
			FROM
			(
				SELECT swb_id wbd_id, 
				CASE WHEN obex_id > 0 AND obex_id <= 3 THEN swb_id ELSE NULL END is_artikel_44,
				CASE WHEN obex_id > 3 AND obex_id <= 6 THEN swb_id ELSE NULL END is_artikel_46,
				CASE WHEN obex_id > 6 THEN swb_id ELSE NULL END is_artikel_47
				FROM swb_exemption
				UNION 
				SELECT gwb_id wbd_id, 
				CASE WHEN obex_id <= 3 THEN gwb_id ELSE NULL END is_artikel_44,
				CASE WHEN obex_id > 3 AND obex_id <= 6 THEN gwb_id ELSE NULL END is_artikel_46,
				CASE WHEN obex_id > 6 THEN gwb_id ELSE NULL END is_artikel_47
				FROM gwb_chemical_exemption
			) a
			JOIN hydro_object ON hydro_object.hdo_id = wbd_id
			GROUP BY wbd_id
		) ub ON ub.wbd_id = ho.hdo_id
		JOIN report_version rv ON rv.wkrv_id = ho.wkrv_id
		JOIN reporting_period rpp ON rpp.rpp_id = rv.rpp_id AND (rv.begin_lifespan_version <= now() OR (rv.end_lifespan_version > now() OR rv.end_lifespan_version = NULL::timestamp without time zone))
		JOIN namespace_type wb_nst ON wb_nst.ns_id = ho.inspire_id_namespace
		JOIN competentauthority_namespacetype ON competentauthority_namespacetype.ns_id = wb_nst.ns_id
		JOIN competent_authority ON competent_authority.ore_id = competentauthority_namespacetype.ore_id
		JOIN water_body ON water_body.wbd_id = ho.hdo_id
		WHERE ho.hydro_object_type IN ('SurfaceWaterBody', 'GroundWaterBody') AND rpp.rpp_id = 3 /* SGBP2 */
		and water_body.rbd_id = ANY(rbdids) and competent_authority.ore_id = ANY(oreids)  AND rpp.leg_ref_id = 1
		GROUP BY rpp.description		
	) x
	UNION
	-- uitzonderingsbepalingen
	SELECT
	6 themaid, 'status'::text soort, 
	format('Op basis van prognose 2021 dienen %1$s kwaliteitselementen opgenomen te worden onder artikel 4.4 (fasering). %2$s van deze %1$s kwaliteitselementen zijn opgenomen in artikel 4.4 (fasering) (%3$s%%).<img alt="%4$s" title="%4$s" src="[CONTENT_URL]/%4$s.png" />',
	aantal_kwaliteitselementen, aantal_kwaliteitselementen_onderbouwd, percentage_kwaliteitselementen_onderbouwd, tos.name)::text regel,
	'' volgorde,
	0 volgorde1
	FROM 
	(
		SELECT 
			COUNT(DISTINCT prognose) aantal_kwaliteitselementen, 
			COUNT(DISTINCT goed) aantal_kwaliteitselementen_onderbouwd,
			ROUND((COUNT(DISTINCT goed) * 1.0 / COUNT(DISTINCT prognose)) * 100, 1) percentage_kwaliteitselementen_onderbouwd
		FROM
		(

			SELECT DISTINCT wb_prognosis.swb_id, wb_prognosis.wbpr_id prognose, CASE WHEN (wb_prognosis.opt_id = u.opt_id OR wb_prognosis.opt_id = k.opt_id) THEN wb_prognosis.wbpr_id ELSE NULL END goed
			FROM wb_prognosis 
			JOIN classification_type ON classification_type.cls_id = wb_prognosis.cls_id
				AND lower(classification_type.description) IN ('matig', 'ontoereikend', 'slecht', 'voldoet niet')
			LEFT JOIN (

				SELECT swb_exemption.swb_id wbd_id, swb_ecol_exemp_qe.opt_id
				FROM swb_exemption
				JOIN swb_ecol_exemp_qe ON swb_ecol_exemp_qe.swce_id = swb_exemption.swce_id
				WHERE swb_exemption.obex_id IN(1,2,3)
				UNION
				SELECT gwb_chemical_exemption.gwb_id wbd_id, gwb_chem_exemp_pollutant.opt_id
				FROM gwb_chemical_exemption
				JOIN gwb_chem_exemp_pollutant ON gwb_chem_exemp_pollutant.gwce_id = gwb_chemical_exemption.gwce_id
				WHERE gwb_chemical_exemption.obex_id IN(1,2,3)

			) u ON u.wbd_id = wb_prognosis.swb_id
			LEFT JOIN (

				SELECT surface_water_body.swb_id wbd_id, part_of_opt_id, opt_id
				FROM surface_water_body
				JOIN wfd_qe_swtype ON wfd_qe_swtype.swt_id = surface_water_body.sw_target_typology_id
				UNION
				SELECT ground_water_body.gwb_id wbd_id, opt_id part_of_opt_id, opt_id
				FROM ground_water_body, wfd_qe_swtype

			) k ON k.wbd_id = wb_prognosis.swb_id AND k.part_of_opt_id = u.opt_id
			WHERE wb_prognosis.year = 2021
		) z
		JOIN hydro_object ho ON ho.hdo_id = z.swb_id
		JOIN report_version rv ON rv.wkrv_id = ho.wkrv_id
		JOIN reporting_period rpp ON rpp.rpp_id = rv.rpp_id AND (rv.begin_lifespan_version <= now() OR (rv.end_lifespan_version > now() OR rv.end_lifespan_version = NULL::timestamp without time zone))
		JOIN namespace_type wb_nst ON wb_nst.ns_id = ho.inspire_id_namespace
		JOIN competentauthority_namespacetype ON competentauthority_namespacetype.ns_id = wb_nst.ns_id
		JOIN competent_authority ON competent_authority.ore_id = competentauthority_namespacetype.ore_id
		JOIN water_body ON water_body.wbd_id = ho.hdo_id
		WHERE ho.hydro_object_type IN ('GroundWaterBody', 'SurfaceWaterBody') AND rpp.rpp_id = 3 /* SGBP2 */
		and water_body.rbd_id = ANY(rbdids) and competent_authority.ore_id = ANY(oreids)  AND rpp.leg_ref_id = 1
		GROUP BY rpp.description	
	) x
	JOIN thema_overzicht_statussen tos ON tos.t_id = 6 AND tos.start_range <= x.percentage_kwaliteitselementen_onderbouwd AND tos.end_range >= x.percentage_kwaliteitselementen_onderbouwd
) z
ORDER BY jaar, themaid, volgorde, volgorde1, regel;
$_$;


--
-- Name: sp_get_thema_overzicht_voortgangmaatregelen(integer, integer[], integer[]); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION sp_get_thema_overzicht_voortgangmaatregelen(jaar integer, rbdids integer[], oreids integer[]) RETURNS TABLE(themaid integer, soort text, regel text)
    LANGUAGE sql
    AS $_$
SELECT themaid "ThemaId", soort "Soort", regel "Regel" FROM
(
	-- Voortgang maatregelen
	SELECT
	3 themaid, 'status'::text soort, 
	format('Voortgang maatregelen %1$s is bijgewerkt (t/m 31-12-%2$s) voor %3$s van de %4$s maatregelen die zijn geclassificeerd met opname in SGBP en uitvoering in periode %5$s (%6$s%%)<img alt="%7$s" title="%7$s" src="[CONTENT_URL]/%7$s.png" />', 
	description, jaar, aantal_bijgewerkte_maatregelen, aantal_maatregelen, substring(uitvoeren_in, char_length(uitvoeren_in)-8), percentage, tos.name)::text regel,
	description volgorde,
	1 volgorde1
	FROM
	(
	       SELECT
	       mp.year jaar, 
	       rpp.description,
	       rpp1.description uitvoeren_in,
	       COUNT(DISTINCT mpc.mtm_id) aantal_bijgewerkte_maatregelen,
	       COUNT(DISTINCT mm.mtm_id) aantal_maatregelen,
	       ROUND((COUNT(DISTINCT mpc.mtm_id) * 1.0 / COUNT(DISTINCT mm.mtm_id)) * 100.0, 1)  percentage
	       FROM monitored_measure mm  
	       JOIN report_version rv1 ON rv1.rpv_id = mm.rpv_id
	       JOIN reporting_period rpp1 ON rpp1.rpp_id = rv1.rpp_id
	       JOIN measure_progress mp ON mp.mtm_id = mm.mtm_id 
	       LEFT JOIN measure_progress_completed mpc ON mpc.mtm_id = mp.mtm_id AND mpc.year_completed = mp.year
	       JOIN supp_add_measure_type samt ON samt.smnl_id = mm.mtm_id
	       JOIN river_basin_management_plan rbmp ON rbmp.rbmp_id = samt.rbmp_id
	       JOIN report_version rv ON rv.wkrv_id = rbmp.wkrv_id
	       JOIN reporting_period rpp ON rpp.rpp_id = rv.rpp_id AND (rv.begin_lifespan_version <= now() AND (rv.end_lifespan_version IS NULL OR (rv.end_lifespan_version IS NOT NULL AND rv.end_lifespan_version > now())))
	       JOIN namespace_type wb_nst ON wb_nst.ns_id = samt.namespace_id
	       JOIN competentauthority_namespacetype ON competentauthority_namespacetype.ns_id = wb_nst.ns_id
	       JOIN competent_authority ON competent_authority.ore_id = competentauthority_namespacetype.ore_id
	       WHERE samt.is_additional_measure AND competent_authority.ore_id = ANY(oreids) AND mp.year = jaar AND rpp.rpp_id = 2 AND rpp1.rpp_id = 2 and rbmp.rbd_id = ANY(rbdids) AND rpp.leg_ref_id = 1
	       GROUP BY mp.year, rpp.description, rpp1.description
	) x
	JOIN thema_overzicht_statussen tos ON tos.t_id = 3 AND tos.start_range <= x.percentage AND tos.end_range >= x.percentage
) z
ORDER BY jaar, themaid, volgorde, volgorde1, regel;
$_$;


--
-- Name: sp_get_thema_overzicht_waterlichamen(integer, integer[], integer[]); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION sp_get_thema_overzicht_waterlichamen(jaar integer, rbdids integer[], oreids integer[]) RETURNS TABLE(themaid integer, soort text, regel text)
    LANGUAGE sql
    AS $_$
SELECT themaid "ThemaId", soort "Soort", regel "Regel" FROM
(
	-- waterlichamen
	SELECT
	1 themaid, 'informatief'::text soort,  
	format('%1$s bevat %2$s waterlichamen', description, aantal_waterlichamen)::text regel,
	description volgorde,
	0 volgorde1
	FROM 
	(
	       SELECT 
	       rpp.description,
	       COUNT(DISTINCT ho.hdo_id) aantal_waterlichamen
	       FROM hydro_object ho 
	       JOIN report_version rv ON rv.wkrv_id = ho.wkrv_id
	       JOIN reporting_period rpp ON rpp.rpp_id = rv.rpp_id AND (rv.begin_lifespan_version <= now() AND (rv.end_lifespan_version IS NULL OR (rv.end_lifespan_version IS NOT NULL AND rv.end_lifespan_version > now())))
	       JOIN namespace_type wb_nst ON wb_nst.ns_id = ho.inspire_id_namespace
	       JOIN competentauthority_namespacetype ON competentauthority_namespacetype.ns_id = wb_nst.ns_id
	       JOIN competent_authority ON competent_authority.ore_id = competentauthority_namespacetype.ore_id
	       JOIN water_body ON water_body.wbd_id = ho.hdo_id
	       WHERE ho.hydro_object_type = 'GroundWaterBody' and competent_authority.ore_id = ANY(oreids) and water_body.rbd_id = ANY(rbdids) AND rpp.leg_ref_id = 1
	       GROUP BY rpp.description
	) x
	UNION 
	-- waterlichamen
	SELECT
	1 themaid, 'informatief'::text soort,  
	format('%1$s bevat %2$s waterlichamen. Van deze waterlichamen zijn er<ul>'
		|| '<li>%3$s natuurlijk, %4$s kunstmatig en %5$s sterk veranderd</li>'
		|| '<li>%6$s R-typen, %7$s M-typen, %8$s O-typen en %9$s K-typen</li></ul>', 
		       description, aantal_waterlichamen, natuurlijk, kunstmatig, sterkveranderd,
		       aantalr, aantalm, aantalo, aantalk)::text regel,
	description volgorde,
	1 volgorde1	       
	FROM 
	(
	       SELECT 
	       rpp.description,
	       COUNT(ho.hdo_id) aantal_waterlichamen,
	       COUNT(DISTINCT status.kunstmatig) kunstmatig,
	       COUNT(DISTINCT status.sterkveranderd) sterkveranderd,
	       COUNT(DISTINCT status.natuurlijk) natuurlijk,
	       COUNT(DISTINCT typen.aantalr) aantalr,
	       COUNT(DISTINCT typen.aantalm) aantalm,
	       COUNT(DISTINCT typen.aantalo) aantalo,
	       COUNT(DISTINCT typen.aantalk) aantalk
	       FROM hydro_object ho 
	       JOIN (
		       SELECT swb_id, 
		       CASE WHEN is_artificial THEN swb_id ELSE NULL END kunstmatig,
		       CASE WHEN is_heaviliy_modified THEN swb_id ELSE NULL END sterkveranderd,
		       CASE WHEN NOT(is_heaviliy_modified) AND NOT(is_artificial) THEN swb_id ELSE NULL END natuurlijk
		       FROM surface_water_body
	       ) status ON status.swb_id = ho.hdo_id
	       JOIN (
		       SELECT swb_id,
		       CASE WHEN category = 'Rivier' THEN swb_id ELSE NULL END aantalr,
		       CASE WHEN category = 'Meer' THEN swb_id ELSE NULL END aantalm,
		       CASE WHEN category = 'Overgangswater' THEN swb_id ELSE NULL END aantalo,
		       CASE WHEN category = 'Kustwater' THEN swb_id ELSE NULL END aantalk
		       FROM 
		       (
			       SELECT swb.swb_id, sct.name category, count(true_swc_id) as aantal
			       FROM surface_water_body swb
			       JOIN sw_typology_type stt ON stt.styp_id = swb.sw_target_typology_id
			       JOIN sw_category_type sct ON sct.swc_id = stt.swc_id
			       GROUP BY swb.swb_id, sct.name
		       ) z
	       ) typen ON typen.swb_id = ho.hdo_id
	       JOIN report_version rv ON rv.wkrv_id = ho.wkrv_id
	       JOIN reporting_period rpp ON rpp.rpp_id = rv.rpp_id AND (rv.begin_lifespan_version <= now() AND (rv.end_lifespan_version IS NULL OR (rv.end_lifespan_version IS NOT NULL AND rv.end_lifespan_version > now())))
	       JOIN namespace_type wb_nst ON wb_nst.ns_id = ho.inspire_id_namespace
	       JOIN competentauthority_namespacetype ON competentauthority_namespacetype.ns_id = wb_nst.ns_id
	       JOIN competent_authority ON competent_authority.ore_id = competentauthority_namespacetype.ore_id
	       JOIN water_body ON water_body.wbd_id = ho.hdo_id
	       WHERE ho.hydro_object_type = 'SurfaceWaterBody' and competent_authority.ore_id = ANY(oreids) and water_body.rbd_id = ANY(rbdids) AND rpp.leg_ref_id = 1
	       GROUP BY rpp.description
	) x
	UNION 
	-- waterlichamen
	SELECT
	1 themaid, 'status'::text soort, 
	format('%1$s bevat %2$s waterlichamen. Van deze waterlichamen is<ul>'
	|| E'<li>%3$s keer een karakterisering ingevuld (%4$s%%)<img alt="%7$s" title="%7$s" src="[CONTENT_URL]/%7$s.png" /></li>'
	|| E'<li>%5$s keer een juiste geometrie ingevuld (%6$s%%)<img alt="%8$s" title="%8$s" src="[CONTENT_URL]/%8$s.png" /></li></ul>', 
		       description, aantal_waterlichamen, aantal_ingevuld, percentage_ingevuld, aantal_geometrie, percentage_geometrie, tos1.name, tos2.name)::text regel,
	description volgorde,
	2 volgorde1
	FROM 
	(
	       SELECT 
	       description,
	       COUNT(hdo_id) aantal_waterlichamen,
	       COUNT(geometry_type) aantal_geometrie,
	       SUM(aantal_ingevuld) aantal_ingevuld,
	       ROUND(SUM(aantal_ingevuld) * 1.0 / COUNT(hdo_id) * 100, 1) percentage_ingevuld,
	       ROUND(COUNT(geometry_type) * 1.0 / COUNT(hdo_id) * 100, 1) percentage_geometrie		
		FROM
		(
		       SELECT DISTINCT rpp.description, ho.hdo_id, g.geometry_type, aantal_ingevuld
		       FROM hydro_object ho 
		       JOIN (
			       SELECT wbd_id, CASE WHEN char_length(description) > 0 THEN 1 ELSE 0 END aantal_ingevuld
			       FROM water_body
		       ) ks ON ks.wbd_id = ho.hdo_id
		       JOIN (
				SELECT swb_id wbd_id,
				CASE WHEN (category = 'Rivier' AND geometrytype(geometry) = 'MULTILINESTRING') OR (category IN ('Meer', 'Overgangswater', 'Kustwater') AND geometrytype(geometry) = 'MULTIPOLYGON')
				THEN swb_id ELSE NULL END geometry_type
				FROM 
				(
				       SELECT swb.swb_id, sct.name category, count(true_swc_id) as aantal
				       FROM surface_water_body swb
				       JOIN sw_typology_type stt ON stt.styp_id = swb.sw_target_typology_id
				       JOIN sw_category_type sct ON sct.swc_id = stt.swc_id
				       GROUP BY swb.swb_id, sct.name
				) z
				LEFT JOIN management_regulation_or_restriction_zone ON management_regulation_or_restriction_zone.mrz_id = z.swb_id
				UNION 
				SELECT gwb_id wbd_id,
				CASE WHEN geometrytype(geometry) IS NOT NULL THEN gwb_id ELSE NULL END geometry_type
				FROM ground_water_body
				LEFT JOIN management_regulation_or_restriction_zone ON management_regulation_or_restriction_zone.mrz_id = ground_water_body.gwb_id
		       ) g ON g.wbd_id = ho.hdo_id
		       JOIN report_version rv ON rv.wkrv_id = ho.wkrv_id
		       JOIN reporting_period rpp ON rpp.rpp_id = rv.rpp_id AND (rv.begin_lifespan_version <= now() AND (rv.end_lifespan_version IS NULL OR (rv.end_lifespan_version IS NOT NULL AND rv.end_lifespan_version > now())))
		       JOIN namespace_type wb_nst ON wb_nst.ns_id = ho.inspire_id_namespace
		       JOIN competentauthority_namespacetype ON competentauthority_namespacetype.ns_id = wb_nst.ns_id
		       JOIN competent_authority ON competent_authority.ore_id = competentauthority_namespacetype.ore_id
		       JOIN water_body ON water_body.wbd_id = ho.hdo_id
		       WHERE ho.hydro_object_type IN ('SurfaceWaterBody', 'GroundWaterBody') and competent_authority.ore_id = ANY(oreids) and water_body.rbd_id = ANY(rbdids) AND rpp.leg_ref_id = 1
		) x2
	       GROUP BY x2.description
	) x
	JOIN thema_overzicht_statussen tos1 ON tos1.t_id = 1 AND tos1.start_range <= x.percentage_ingevuld AND tos1.end_range >= x.percentage_ingevuld
	JOIN thema_overzicht_statussen tos2 ON tos2.t_id = 1 AND tos2.start_range <= x.percentage_geometrie AND tos2.end_range >= x.percentage_geometrie
) z
ORDER BY jaar, themaid, volgorde, volgorde1, regel;
$_$;


--
-- Name: sp_get_toestand_normoverschrijdingen_owm(integer, integer); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION sp_get_toestand_normoverschrijdingen_owm(hdoid integer, rapportagejaar integer) RETURNS TABLE("Stof" text, "Oordeel" text, "GroepsNaam" text)
    LANGUAGE sql
    AS $_$

WITH normoverschrijding AS (
	SELECT 
		vw_oordeel_chemisch_owm."ChemischeStof_Omschrijving" AS "Stof", 
		vw_oordeel_chemisch_owm."Oordeel", 
		vw_chemischestof_groepen."Groepsnaam" AS "GroepsNaam",
		ROW_NUMBER() OVER(PARTITION BY vw_oordeel_chemisch_owm."ChemischeStof_Omschrijving"
					 ORDER BY vw_oordeel_chemisch_owm."Waargenomen_Eigenschap_DB_ID", vw_oordeel_chemisch_owm."Oordeel_Type_Code" DESC, vw_oordeel_chemisch_owm."WaardebepalingsMethode_Code" DESC) AS positie
	FROM public.vw_oordeel_chemisch_owm
	JOIN vw_chemischestof_groepen ON vw_chemischestof_groepen."ChemischeStof_Code" = vw_oordeel_chemisch_owm."ChemischeStof_code"
	WHERE "Waterlichaam_DB_ID" = $1 AND 
	vw_chemischestof_groepen."DB_ID" IN (3155, 3150, 3149, 3148)
	AND vw_oordeel_chemisch_owm."Rapportagejaar" = $2
)
SELECT "Stof", "Oordeel", "GroepsNaam"
FROM normoverschrijding s
WHERE positie = 1 AND "Oordeel" = 'voldoet niet'
ORDER BY 3, 1

$_$;


--
-- Name: sp_get_toestandsoordelen_chemischestoffen(integer, integer, integer, integer); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION sp_get_toestandsoordelen_chemischestoffen(waterlichaam integer, jaar integer, jaar2 integer, rrt integer) RETURNS TABLE(status_db_id integer, waterlichaam_db_id integer, rapportagejaar integer, waargenomen_eigenschap_db_id integer, chemischestof_db_id integer, chemischestof_code text, chemischestof_omschrijving text, oordeel text, oordeel_type_code text, oordeel_type_omschrijving text, waardebepalingsmethode_code text, waardebepalingsmethode_omschrijving text)
    LANGUAGE sql
    AS $$
SELECT swb_yearly_status.sys_id AS "Status_DB_ID", 
    hydro_object.hdo_id AS "Waterlichaam_DB_ID",
    swb_yearly_status.year_of_status AS "Rapportagejaar",
    vw_waargenomen_eigenschap_db_id."DB_ID" AS "Waargenomen_Eigenschap_DB_ID", 
    vw_waargenomen_eigenschap_db_id."ChemischeStof_DB_ID",
    vw_waargenomen_eigenschap_db_id."ChemischeStof_code", 
    vw_waargenomen_eigenschap_db_id."ChemischeStof_Omschrijving", 
    classification_type.description AS "Oordeel", 
    eu_monitoring_goal_type.code AS "Oordeel_Type_Code", 
    eu_monitoring_goal_type.description AS "Oordeel_Type_Omschrijving", 
    waarde_bepalings_methode_type.code AS "WaardebepalingsMethode_Code", 
    waarde_bepalings_methode_type.omschrijving AS "WaardebepalingsMethode_Omschrijving"
  
   FROM swb_yearly_status

   LEFT JOIN hydro_object ON hydro_object.hdo_id = swb_yearly_status.swb_id
   LEFT JOIN sw_chemical_substance_status ON sw_chemical_substance_status.sys_id = swb_yearly_status.sys_id
   LEFT JOIN classification_type ON sw_chemical_substance_status.swcs_id = classification_type.cls_id
   LEFT JOIN vw_waargenomen_eigenschap_db_id ON vw_waargenomen_eigenschap_db_id."DB_ID" = sw_chemical_substance_status.opt_id
   LEFT JOIN waarde_bepalings_methode_type ON waarde_bepalings_methode_type.wbpm_id = sw_chemical_substance_status.wbpm_id
   LEFT JOIN eu_monitoring_goal_type ON eu_monitoring_goal_type.eumg_id = sw_chemical_substance_status.eumg_id
  
   
   LEFT JOIN ( SELECT substance_group_element.chs_id, 
    substance_group_element.ssge_id
   FROM substance_group_element
  WHERE substance_group_element.ssge_id = 2907) ssg ON ssg.chs_id = vw_waargenomen_eigenschap_db_id."ChemischeStof_DB_ID"


  where (hydro_object.hdo_id = waterlichaam 
    OR hydro_object.hdo_id IN (select to_hdo from related_hydro_object where from_hdo = waterlichaam and rrt_id = rrt)) 
  and swb_yearly_status.year_of_status IN (jaar, jaar2) 
    and ssg.ssge_id = 2907 

  order by "ChemischeStof_Omschrijving",
   CASE
      WHEN eu_monitoring_goal_type.eumg_id = 3  THEN 1   -- TT_OM == 3
      WHEN eu_monitoring_goal_type.eumg_id = 1 THEN 2      -- OM=1
      WHEN eu_monitoring_goal_type.eumg_id = 2 THEN 3       -- TT=2
      ELSE 4
    END, 
  CASE
      WHEN waarde_bepalings_methode_type.wbpm_id = 1 THEN 1  -- Auquokit =1
      WHEN waarde_bepalings_methode_type.wbpm_id = 4 THEN 2  -- Voorlopig = 4
      WHEN waarde_bepalings_methode_type.wbpm_id = 2 THEN 3  -- Desk = 2
      ELSE 4
    END 
    ;

$$;


--
-- Name: sp_get_toestandsoordelen_overigestoffen(integer, integer, integer, integer); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION sp_get_toestandsoordelen_overigestoffen(waterlichaam integer, jaar integer, jaar2 integer, rrt integer) RETURNS TABLE(status_db_id integer, waterlichaam_db_id integer, rapportagejaar integer, waargenomen_eigenschap_db_id integer, chemischestof_db_id integer, chemischestof_code text, chemischestof_omschrijving text, oordeel text, oordeel_type_code text, oordeel_type_omschrijving text, waardebepalingsmethode_code text, waardebepalingsmethode_omschrijving text)
    LANGUAGE sql
    AS $$
SELECT swb_yearly_status.sys_id AS "Status_DB_ID", 
    hydro_object.hdo_id AS "Waterlichaam_DB_ID",
    swb_yearly_status.year_of_status AS "Rapportagejaar",
    vw_waargenomen_eigenschap_db_id."DB_ID" AS "Waargenomen_Eigenschap_DB_ID", 
    vw_waargenomen_eigenschap_db_id."ChemischeStof_DB_ID",
    vw_waargenomen_eigenschap_db_id."ChemischeStof_code", 
    vw_waargenomen_eigenschap_db_id."ChemischeStof_Omschrijving", 
    classification_type.description AS "Oordeel", 
    eu_monitoring_goal_type.code AS "Oordeel_Type_Code", 
    eu_monitoring_goal_type.description AS "Oordeel_Type_Omschrijving", 
    waarde_bepalings_methode_type.code AS "WaardebepalingsMethode_Code", 
    waarde_bepalings_methode_type.omschrijving AS "WaardebepalingsMethode_Omschrijving"
  
   FROM swb_yearly_status

   LEFT JOIN hydro_object ON hydro_object.hdo_id = swb_yearly_status.swb_id
   LEFT JOIN sw_chemical_substance_status ON sw_chemical_substance_status.sys_id = swb_yearly_status.sys_id
   LEFT JOIN classification_type ON sw_chemical_substance_status.swcs_id = classification_type.cls_id
   LEFT JOIN vw_waargenomen_eigenschap_db_id ON vw_waargenomen_eigenschap_db_id."DB_ID" = sw_chemical_substance_status.opt_id
   LEFT JOIN waarde_bepalings_methode_type ON waarde_bepalings_methode_type.wbpm_id = sw_chemical_substance_status.wbpm_id
   LEFT JOIN eu_monitoring_goal_type ON eu_monitoring_goal_type.eumg_id = sw_chemical_substance_status.eumg_id
  
   
   LEFT JOIN ( SELECT substance_group_element.chs_id, 
    substance_group_element.ssge_id
   FROM substance_group_element
  WHERE substance_group_element.ssge_id = 2908) ssg ON ssg.chs_id = vw_waargenomen_eigenschap_db_id."ChemischeStof_DB_ID"


  where (hydro_object.hdo_id = waterlichaam 
    OR hydro_object.hdo_id IN (select to_hdo from related_hydro_object where from_hdo = waterlichaam and rrt_id = rrt)) 
  and swb_yearly_status.year_of_status IN (jaar, jaar2) 
    and ssg.ssge_id = 2908 

  order by "ChemischeStof_Omschrijving",
   CASE
      WHEN eu_monitoring_goal_type.eumg_id = 3  THEN 1   -- TT_OM == 3
      WHEN eu_monitoring_goal_type.eumg_id = 1 THEN 2      -- OM=1
      WHEN eu_monitoring_goal_type.eumg_id = 2 THEN 3       -- TT=2
      ELSE 4
    END, 
  CASE
      WHEN waarde_bepalings_methode_type.wbpm_id = 1 THEN 1  -- Auquokit =1
      WHEN waarde_bepalings_methode_type.wbpm_id = 4 THEN 2  -- Voorlopig = 4
      WHEN waarde_bepalings_methode_type.wbpm_id = 2 THEN 3  -- Desk = 2
      ELSE 4
    END 
    ;

$$;


--
-- Name: sp_get_toestandstoetswaarden_chemischestoffen(integer, integer, integer, integer); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION sp_get_toestandstoetswaarden_chemischestoffen(waterlichaam integer, jaar integer, jaar2 integer, rrt integer) RETURNS TABLE(toetswaarde_db_id integer, status_db_id integer, waterlichaam_db_id integer, rapportagejaar integer, waargenomen_eigenschap_db_id integer, chemischestof_db_id integer, chemischestof_code text, chemischestof_omschrijving text, numerieke_waarde double precision, bepalingsgrens text, eenheid_code text, eenheid_omschrijving text, oordeel_type_db_id integer, oordeel_type_code text, oordeel_type_omschrijving text, waardebepalingsmethode_code text, waardebepalingsmethode_omschrijving text)
    LANGUAGE sql
    AS $$
SELECT observation.obs_id AS "Toetswaarde_DB_ID",
 swb_yearly_status.sys_id AS "Status_DB_ID",
    hydro_object.hdo_id AS "Waterlichaam_DB_ID", 
    swb_yearly_status.year_of_status AS "Rapportagejaar", 
    vw_waargenomen_eigenschap_db_id."DB_ID" AS "Waargenomen_Eigenschap_DB_ID", 
     vw_waargenomen_eigenschap_db_id."ChemischeStof_DB_ID",
    vw_waargenomen_eigenschap_db_id."ChemischeStof_code", 
    vw_waargenomen_eigenschap_db_id."ChemischeStof_Omschrijving", 
    observation.numeric_value AS "Numerieke_waarde", 
    bepaling_grens_type.omschrijving AS "Bepalingsgrens", 
    eenheid_type.code AS "Eenheid_Code", 
    eenheid_type.omschrijving AS "Eenheid_Omschrijving", 
    eu_monitoring_goal_type.eumg_id AS "Oordeel_Type_DB_ID", 
    eu_monitoring_goal_type.code AS "Oordeel_Type_Code", 
    eu_monitoring_goal_type.description AS "Oordeel_Type_Omschrijving", 
    waarde_bepalings_methode_type.code AS "Waardebepalingsmethode_Code", 
    waarde_bepalings_methode_type.omschrijving AS "Waardebepalingsmethode_Omschrijving"

   FROM swb_yearly_status
   LEFT JOIN sw_testing_conclusion ON swb_yearly_status.sys_id = sw_testing_conclusion.sys_id
   LEFT JOIN observation ON sw_testing_conclusion.stcc_id = observation.obs_id
   LEFT JOIN vw_waargenomen_eigenschap_db_id ON vw_waargenomen_eigenschap_db_id."DB_ID" = observation.observed_property_type
   LEFT JOIN eenheid_type ON eenheid_type.eenh_id = observation.numeric_value_uom_id
   LEFT JOIN bepaling_grens_type ON bepaling_grens_type.bpg_id = observation.limit_symbol_id
   LEFT JOIN observation_process ON observation_process.obp_id = observation.result_nature_id
   LEFT JOIN waarde_bepalings_methode_type ON waarde_bepalings_methode_type.wbpm_id = observation_process.wbpm_id
   LEFT JOIN hydro_object ON hydro_object.hdo_id = swb_yearly_status.swb_id
   LEFT JOIN eu_monitoring_goal_type ON eu_monitoring_goal_type.eumg_id = sw_testing_conclusion.eumg_id
   LEFT JOIN ( SELECT substance_group_element.chs_id, 
     substance_group_element.ssge_id
     FROM substance_group_element
   WHERE substance_group_element.ssge_id = 2907) ssg ON ssg.chs_id = vw_waargenomen_eigenschap_db_id."ChemischeStof_DB_ID"

where (hydro_object.hdo_id = waterlichaam 
    OR hydro_object.hdo_id IN (select to_hdo from related_hydro_object where from_hdo = waterlichaam and rrt_id = rrt))    -- waterlichaam
  and swb_yearly_status.year_of_status IN (jaar,jaar2) -- rapportagejaar
   and ssg.ssge_id = 2907 
  
  and waarde_bepalings_methode_type.wbpm_id = 1  -- AQUOKIT
  order by "ChemischeStof_Omschrijving",
  CASE
      WHEN eu_monitoring_goal_type.eumg_id = 3  THEN 1   -- TT_OM == 3
      WHEN eu_monitoring_goal_type.eumg_id = 1 THEN 2      -- OM=1
      WHEN eu_monitoring_goal_type.eumg_id = 2 THEN 3       -- TT=2
      ELSE 4
    END
  ;


$$;


--
-- Name: sp_get_toestandstoetswaarden_overigestoffen(integer, integer, integer, integer); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION sp_get_toestandstoetswaarden_overigestoffen(waterlichaam integer, jaar integer, jaar2 integer, rrt integer) RETURNS TABLE(toetswaarde_db_id integer, status_db_id integer, waterlichaam_db_id integer, rapportagejaar integer, waargenomen_eigenschap_db_id integer, chemischestof_db_id integer, chemischestof_code text, chemischestof_omschrijving text, numerieke_waarde double precision, bepalingsgrens text, eenheid_code text, eenheid_omschrijving text, oordeel_type_db_id integer, oordeel_type_code text, oordeel_type_omschrijving text, waardebepalingsmethode_code text, waardebepalingsmethode_omschrijving text)
    LANGUAGE sql
    AS $$
SELECT observation.obs_id AS "Toetswaarde_DB_ID",
 swb_yearly_status.sys_id AS "Status_DB_ID",
    hydro_object.hdo_id AS "Waterlichaam_DB_ID", 
    swb_yearly_status.year_of_status AS "Rapportagejaar", 
    vw_waargenomen_eigenschap_db_id."DB_ID" AS "Waargenomen_Eigenschap_DB_ID", 
     vw_waargenomen_eigenschap_db_id."ChemischeStof_DB_ID",
    vw_waargenomen_eigenschap_db_id."ChemischeStof_code", 
    vw_waargenomen_eigenschap_db_id."ChemischeStof_Omschrijving", 
    observation.numeric_value AS "Numerieke_waarde", 
    bepaling_grens_type.omschrijving AS "Bepalingsgrens", 
    eenheid_type.code AS "Eenheid_Code", 
    eenheid_type.omschrijving AS "Eenheid_Omschrijving", 
    eu_monitoring_goal_type.eumg_id AS "Oordeel_Type_DB_ID", 
    eu_monitoring_goal_type.code AS "Oordeel_Type_Code", 
    eu_monitoring_goal_type.description AS "Oordeel_Type_Omschrijving", 
    waarde_bepalings_methode_type.code AS "Waardebepalingsmethode_Code", 
    waarde_bepalings_methode_type.omschrijving AS "Waardebepalingsmethode_Omschrijving"

   FROM swb_yearly_status
   LEFT JOIN sw_testing_conclusion ON swb_yearly_status.sys_id = sw_testing_conclusion.sys_id
   LEFT JOIN observation ON sw_testing_conclusion.stcc_id = observation.obs_id
   LEFT JOIN vw_waargenomen_eigenschap_db_id ON vw_waargenomen_eigenschap_db_id."DB_ID" = observation.observed_property_type
   LEFT JOIN eenheid_type ON eenheid_type.eenh_id = observation.numeric_value_uom_id
   LEFT JOIN bepaling_grens_type ON bepaling_grens_type.bpg_id = observation.limit_symbol_id
   LEFT JOIN observation_process ON observation_process.obp_id = observation.result_nature_id
   LEFT JOIN waarde_bepalings_methode_type ON waarde_bepalings_methode_type.wbpm_id = observation_process.wbpm_id
   LEFT JOIN hydro_object ON hydro_object.hdo_id = swb_yearly_status.swb_id
   LEFT JOIN eu_monitoring_goal_type ON eu_monitoring_goal_type.eumg_id = sw_testing_conclusion.eumg_id
   LEFT JOIN ( SELECT substance_group_element.chs_id, 
     substance_group_element.ssge_id
     FROM substance_group_element
   WHERE substance_group_element.ssge_id = 2908) ssg ON ssg.chs_id = vw_waargenomen_eigenschap_db_id."ChemischeStof_DB_ID"

where (hydro_object.hdo_id = waterlichaam 
    OR hydro_object.hdo_id IN (select to_hdo from related_hydro_object where from_hdo = waterlichaam and rrt_id = rrt))    -- waterlichaam
  and swb_yearly_status.year_of_status IN (jaar,jaar2) -- rapportagejaar
   and ssg.ssge_id = 2908 
  
  and waarde_bepalings_methode_type.wbpm_id = 1  -- AQUOKIT
  order by "ChemischeStof_Omschrijving",
  CASE
      WHEN eu_monitoring_goal_type.eumg_id = 3  THEN 1   -- TT_OM == 3
      WHEN eu_monitoring_goal_type.eumg_id = 1 THEN 2      -- OM=1
      WHEN eu_monitoring_goal_type.eumg_id = 2 THEN 3       -- TT=2
      ELSE 4
    END
  ;


$$;


--
-- Name: sp_oordeel_chemie_owm_g(text, text, integer, integer, text); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION sp_oordeel_chemie_owm_g(in_geometry_type text, in_stroomgebieddistrict text, in_waterlichaam_versie integer, in_rapportagejaar integer, in_kwaliteitselement text) RETURNS TABLE("Waterlichaam_identificatie" text, "Waterlichaam_Versie" integer, stroomgebieddistrict text, "Rapportagejaar" integer, "Tpering_Code" text, "Grootheid_Code" text, "ChemischeStof_code" text, "Oordeel" text, geometry_type text, geometry geometry, "Unique_Key" text)
    LANGUAGE sql
    AS $$
WITH oordeel AS (
   SELECT DISTINCT ON (sys.sys_id, swcs.opt_id)
    sys.sys_id,
    sys.swb_id,
    sys.year_of_status,
    swcs.opt_id,
    swcs.swcs_id,
    swcs.css_id,
    hdo_swb.hdo_id,
    hdo_swb.wkrv_id,
    hdo_swb.inspire_id_local_id
   FROM ((sw_chemical_substance_status swcs
             JOIN swb_yearly_status sys 
               ON ((swcs.sys_id = sys.sys_id)) AND sys.year_of_status = IN_Rapportagejaar
             )
             JOIN hydro_object hdo_swb  ON ((hdo_swb.hdo_id = sys.swb_id)))
   WHERE (swcs.eumg_id = 3) AND hdo_swb.wkrv_id = IN_Waterlichaam_Versie
   --ORDER BY sys.sys_id, swcs.opt_id, swcs.wbpm_id DESC
   )
, opt AS (
    SELECT observed_property_type.opt_id AS "DB_ID",
        typering_type.code AS "Tpering_Code",
        grootheid_type.code AS "Grootheid_Code",
        chemische_stof_type.code AS "ChemischeStof_code"
    FROM (((observed_property_type
             LEFT JOIN grootheid_type 
               ON ((grootheid_type.grh_id = observed_property_type.quantity_id)))
             LEFT JOIN ( chemische_stof_type
                    JOIN substance_group_element 
                      ON ((chemische_stof_type.chs_id = substance_group_element.chs_id))
                  ) ON ((substance_group_element.ssge_id = observed_property_type.substance_group_id))
          )
          LEFT JOIN typering_type 
            ON ((typering_type.typ_id = observed_property_type.indicator_id)))
    WHERE (  typering_type.code = IN_kwaliteitselement
          OR grootheid_type.code = IN_kwaliteitselement
          OR chemische_stof_type.code = IN_kwaliteitselement
          )
    )
SELECT oordeel.inspire_id_local_id AS "Waterlichaam_identificatie",
        oordeel.wkrv_id AS "Waterlichaam_Versie",
        hdo_rbd.inspire_id_local_id AS stroomgebieddistrict,
        oordeel.year_of_status AS "Rapportagejaar",
        opt."Tpering_Code",
        opt."Grootheid_Code",
        opt."ChemischeStof_code",
        classification_type.description AS "Oordeel",
        geometrytype(mrz.geometry) AS geometry_type,
        mrz.geometry,
        ('CH'::text || oordeel.css_id) AS "Unique_Key"
FROM ((((((oordeel
     JOIN management_regulation_or_restriction_zone mrz
        ON ((oordeel.swb_id = mrz.mrz_id)) AND geometrytype(mrz.geometry) LIKE IN_geometry_type
        )
     JOIN water_body wb ON
        ((wb.wbd_id = mrz.mrz_id)))
     JOIN river_basin_disrict rbd ON
        ((rbd.rbd_id = wb.rbd_id)))
     JOIN hydro_object hdo_rbd ON
        ((rbd.rbd_id = hdo_rbd.hdo_id)) AND hdo_rbd.inspire_id_local_id LIKE IN_stroomgebieddistrict
        )
     JOIN classification_type ON
        ((oordeel.swcs_id = classification_type.cls_id)))
     JOIN opt ON
        ((opt."DB_ID" = oordeel.opt_id)))
$$;


--
-- Name: sp_post_create_new_wkr_version(); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION sp_post_create_new_wkr_version() RETURNS boolean
    LANGUAGE sql
    AS $$
	-- opruimen
ALTER TABLE public.hydro_object
  DROP COLUMN fromhdo_id;
	
ALTER TABLE public.monitored_measure
  DROP COLUMN frommtm_id;
  
ALTER TABLE public.gwb_yearly_status
  DROP COLUMN from_gys_id;

ALTER TABLE public.swb_yearly_status
  DROP COLUMN from_sys_id;
 
ALTER TABLE public.swb_exemption
  DROP COLUMN from_swce_id; 
  
ALTER TABLE public.gwb_chemical_exemption
  DROP COLUMN from_gwce_id;

ALTER TABLE public.swb_measures_43a
  DROP COLUMN from_swee_id;  
  
ALTER TABLE public.monitoring_object
  DROP COLUMN from_mno_id;

ALTER TABLE observation
  DROP COLUMN from_obs_id;  

ALTER TABLE public.norm
  DROP COLUMN from_nrm_id;  
  
ALTER TABLE public.observing_capability
  DROP COLUMN from_osc_id;  
  
ALTER TABLE public.quality_element
  DROP COLUMN from_qem_id;
  
ALTER TABLE public.planned_feature_monitoring
  DROP COLUMN from_pmf_id;
  
ALTER TABLE public.swb_pressure
  DROP COLUMN from_swbp_id;

ALTER TABLE public.gwb_pressure
  DROP COLUMN from_gwbp_id;

select true;$$;


--
-- Name: sp_pre_create_new_wkr_version(); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION sp_pre_create_new_wkr_version() RETURNS boolean
    LANGUAGE sql
    AS $$
	
ALTER TABLE public.hydro_object
  ADD COLUMN fromhdo_id integer;
	
ALTER TABLE public.monitored_measure
  ADD COLUMN frommtm_id integer;
  
ALTER TABLE public.gwb_yearly_status
  ADD COLUMN from_gys_id integer;

ALTER TABLE public.swb_yearly_status
  ADD COLUMN from_sys_id integer;
 
ALTER TABLE public.swb_exemption
  ADD COLUMN from_swce_id integer; 
  
ALTER TABLE public.gwb_chemical_exemption
  ADD COLUMN from_gwce_id integer;

ALTER TABLE public.swb_measures_43a
  ADD COLUMN from_swee_id integer;  
  
ALTER TABLE public.monitoring_object
  ADD COLUMN from_mno_id integer;  

ALTER TABLE public.observation
  ADD COLUMN from_obs_id integer;  

ALTER TABLE public.norm
  ADD COLUMN from_nrm_id integer;  
  
ALTER TABLE public.observing_capability
  ADD COLUMN from_osc_id integer;  

ALTER TABLE public.quality_element
  ADD COLUMN from_qem_id integer;

ALTER TABLE public.planned_feature_monitoring
  ADD COLUMN from_pmf_id integer;

ALTER TABLE public.swb_pressure
  ADD COLUMN from_swbp_id integer;

ALTER TABLE public.gwb_pressure
  ADD COLUMN from_gwbp_id integer;
  
select true;$$;


--
-- Name: sp_remove_samples_and_observations(integer); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION sp_remove_samples_and_observations(databundelid integer) RETURNS integer
    LANGUAGE plpgsql
    AS $$
BEGIN

create table observing_capability_observation_temp as select obs_id from observing_capability_observation where osc_id = databundelId;
delete from observing_capability_observation where osc_id = databundelId;
delete from quality_element_observation where obs_id in (select obs_id from observing_capability_observation_temp);
delete from bio_observation where obs_id in (select obs_id from observing_capability_observation_temp);
delete from observation where obs_id in (select obs_id from observing_capability_observation_temp);

create table observing_capability_sample_temp as select smpl_id from observing_capability_sample where osc_id = databundelId;
delete from observing_capability_sample where osc_id = databundelId;
delete from related_sampling_feature 	where mnf_id_source in (select smpl_id from observing_capability_sample_temp);
delete from quality_element_sample  	where smpl_id in (select smpl_id from observing_capability_sample_temp);
delete from sample  			where smpl_id in (select smpl_id from observing_capability_sample_temp);
delete from monitoring_feature 		where mnf_id 	in (select smpl_id from observing_capability_sample_temp);
delete from monitoring_object  		where mno_id in (select smpl_id from observing_capability_sample_temp);

drop table observing_capability_sample_temp;
drop table observing_capability_observation_temp;

RETURN databundelId;
END;
$$;


--
-- Name: wkp_mno_change_geom_type(); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION wkp_mno_change_geom_type() RETURNS text
    LANGUAGE plpgsql
    AS $$
DECLARE
  def_vw_meetpunten_g varchar;
  def__vw_meetnet_meetpunt_g varchar;
  def_vw_meetnet_meetpunt_g varchar;
  def_vw_tijdwaarden_bestrijdingsmiddelen_csv varchar;
  def_vw_tijdwaarden_csv varchar;
  def_vw_tijdwaarden_mnlso_csv varchar;
  def_vw_meetnetten varchar;
  def_vw_tijdwaarden_g varchar;
  def_vw_ef_inspire varchar;
BEGIN

-- drop depending views
  drop view vw_meetlocatie_waterlichaam;

  drop view vw_download_meetlocaties_krm;

  DROP VIEW public.vw_meetpunt_meetnet_csv;

  drop view vw_meetpunten_csv;

  drop view vw_meetpunten_shape;

  drop view vw_tijdwaarden_bestrijdingsmiddelen_g;

  select view_definition into def__vw_meetnet_meetpunt_g from information_schema.views where table_name = '_vw_meetnet_meetpunt_g';
  drop view _vw_meetnet_meetpunt_g;

  select view_definition into def_vw_meetnet_meetpunt_g from information_schema.views where table_name = 'vw_meetnet_meetpunt_g';
  drop view vw_meetnet_meetpunt_g;

  select view_definition into def_vw_meetpunten_g from information_schema.views where table_name = 'vw_meetpunten_g';
  drop view vw_meetpunten_g;

  select view_definition into def_vw_tijdwaarden_bestrijdingsmiddelen_csv from information_schema.views where table_name = 'vw_tijdwaarden_bestrijdingsmiddelen_csv';
  drop view vw_tijdwaarden_bestrijdingsmiddelen_csv;
  
  select view_definition into def_vw_tijdwaarden_csv from information_schema.views where table_name = 'vw_tijdwaarden_csv';
  drop view vw_tijdwaarden_csv;

  select view_definition into def_vw_tijdwaarden_mnlso_csv from information_schema.views where table_name = 'vw_tijdwaarden_mnlso_csv';
  drop view vw_tijdwaarden_mnlso_csv;

  select view_definition into def_vw_meetnetten from information_schema.views where table_name = 'vw_meetnetten';
  drop view vw_meetnetten;

  select view_definition into def_vw_tijdwaarden_g from information_schema.views where table_name = 'vw_tijdwaarden_g';
  drop view vw_tijdwaarden_g;

  select view_definition into def_vw_ef_inspire from information_schema.views where table_name = 'vw_ef_inspire';
  drop view vw_ef_inspire;

-- change geometry type  
  ALTER TABLE monitoring_object ALTER COLUMN geometry TYPE geometry(Geometry,28992);
  ALTER TABLE monitoring_object ALTER COLUMN geometry_etrs89 TYPE geometry(Geometry,4258);

--recreate depending views

  execute 'create view vw_meetnetten as ' || def_vw_meetnetten;
  ALTER TABLE vw_meetnetten OWNER TO  pgsql;

  execute 'create view vw_meetpunten_g as ' || def_vw_meetpunten_g;
  ALTER TABLE vw_meetpunten_g OWNER TO  pgsql;

  execute 'create view _vw_meetnet_meetpunt_g as ' || def__vw_meetnet_meetpunt_g;
  ALTER TABLE _vw_meetnet_meetpunt_g OWNER TO  pgsql;

  execute 'create view vw_meetnet_meetpunt_g as ' || def_vw_meetnet_meetpunt_g;
  ALTER TABLE vw_meetnet_meetpunt_g OWNER TO  pgsql;

  execute 'create view vw_tijdwaarden_bestrijdingsmiddelen_csv as ' || def_vw_tijdwaarden_bestrijdingsmiddelen_csv;
  ALTER TABLE vw_tijdwaarden_bestrijdingsmiddelen_csv OWNER TO  pgsql;

  execute 'create view vw_tijdwaarden_csv as ' || def_vw_tijdwaarden_csv;
  ALTER TABLE vw_tijdwaarden_csv OWNER TO  pgsql;

  execute 'create view vw_tijdwaarden_mnlso_csv as ' || def_vw_tijdwaarden_mnlso_csv;
  ALTER TABLE vw_tijdwaarden_mnlso_csv OWNER TO  pgsql;

  execute 'create view vw_tijdwaarden_g as ' || def_vw_tijdwaarden_g;
  ALTER TABLE vw_tijdwaarden_g OWNER TO  pgsql;

  execute 'create view vw_ef_inspire as ' || def_vw_ef_inspire;
  ALTER TABLE vw_ef_inspire OWNER TO  pgsql;

  CREATE OR REPLACE VIEW public.vw_meetlocatie_waterlichaam AS 
 SELECT mno.inspire_id_local_id AS "Meetpunt.identificatie", 
    mno.name AS "Meetpunt.omschrijving", 
    st_x(st_transform(mno.geometry, 28992)) AS "Geometriepunt.X_RD", 
    st_y(st_transform(mno.geometry, 28992)) AS "Geometriepunt.Y_RD", 
    hdo.inspire_id_local_id AS "Waterlichaam.identificatie", 
    wln.omschrijving AS "Waterlichaam.omschrijving", 
    ns.code AS "Waterbeheerder.code", 
    ns.descrption AS "Waterbeheerder.omschrijving", 
    wln.stroomgebieddistrict AS "Stroomgebieddistrict.code", 
    mno.wkrv_id AS "Versie.code", mno.last_change_time
   FROM monitoring_object mno
   JOIN wkr_version wkrv ON wkrv.wkrv_id = mno.wkrv_id
   JOIN monitoring_feature mnf ON mnf.mnf_id = mno.mno_id
   JOIN environmental_monitoring_facility emf ON emf.emf_id = mnf.mnf_id
   JOIN management_regulation_or_restriction_zone mrz ON mrz.mrz_id = emf.ligt_in_mrz_id
   JOIN water_body wbd ON wbd.wbd_id = mrz.mrz_id
   JOIN hydro_object hdo ON hdo.hdo_id = mrz.mrz_id
   JOIN vw_waterlichamen wln ON wln.db_id = hdo.hdo_id
   JOIN namespace_type ns ON ns.ns_id = mno.inspire_id_namespace_id
  WHERE ST_GeometryType(mno.geometry) = 'ST_Point'
  ORDER BY wln.waterbeheerder_code, hdo.inspire_id_local_id;

ALTER TABLE public.vw_meetlocatie_waterlichaam
  OWNER TO  pgsql;

CREATE OR REPLACE VIEW public.vw_meetpunten_csv AS 
 SELECT monitoring_object.mno_id AS meetpunt_identificatie_db_id, 
    monitoring_object.inspire_id_local_id AS "Meetpunt.identificatie", 
    monitoring_object.name AS "Meetpunt.omschrijving", 
    namespace_type.code AS "Waterbeheerder.code", 
    namespace_type.descrption AS "Waterbeheerder.omschrijving", 
    st_x(st_transform(monitoring_object.geometry, 28992)) AS "GeometriePunt.X_RD", 
    st_y(st_transform(monitoring_object.geometry, 28992)) AS "GeometriePunt.Y_RD", 
    monitoring_object.wkrv_id, monitoring_object.last_change_time
   FROM monitoring_object
   JOIN monitored_compartiment ON monitored_compartiment.mno_id = monitoring_object.mno_id
   JOIN namespace_type ON namespace_type.ns_id = monitoring_object.inspire_id_namespace_id
   JOIN monitoring_feature ON monitoring_feature.mnf_id = monitoring_object.mno_id
  WHERE ST_GeometryType(monitoring_object.geometry) = 'ST_Point'
  ORDER BY namespace_type.code, monitoring_object.inspire_id_local_id;

ALTER TABLE public.vw_meetpunten_csv
  OWNER TO  pgsql;  

CREATE OR REPLACE VIEW public.vw_meetpunten_shape AS 
 SELECT DISTINCT ON (monitoring_object.inspire_id_local_id) monitoring_object.inspire_id_local_id AS "IDENT", 
    namespace_type.code AS "WBHCODE", namespace_type.descrption AS "WBHNAAM", 
    monitoring_object.name AS "MPNNAAM", 
    monitoring_object.additional_description AS "TOELICHT", 
    monitoring_object.geometry, 
    monitoring_feature.pos_accuracy_horizontal_value AS "PRECISIE", 
    eenheid_type.code AS "PREC_EEN", 
    measurement_regime_type.description AS "REGIME", 
    environmental_monitoring_facility.is_mobile AS "ISMOBIEL", 
    result_acquisition_source_type.description AS "INWINSRT", 
    specialised_emf_type.description AS "TYPESRT", 
    subsite_type.description AS "LOCTYPE", 
    monitoring_station.number_of_points_in_subsite AS "AANTPNT", 
    management_regulation_or_restriction_zone.mrz_id AS "IN_OWM"
   FROM wkr_version
   RIGHT JOIN (namespace_type
   RIGHT JOIN (monitoring_object
   RIGHT JOIN (hydro_object
   JOIN management_regulation_or_restriction_zone ON hydro_object.hdo_id = management_regulation_or_restriction_zone.mrz_id
   JOIN namespace_type namespace_type_1 ON hydro_object.inspire_id_namespace = namespace_type_1.ns_id
   RIGHT JOIN (environmental_monitoring_facility
   JOIN monitoring_feature ON environmental_monitoring_facility.emf_id = monitoring_feature.mnf_id) ON management_regulation_or_restriction_zone.mrz_id = environmental_monitoring_facility.ligt_in_mrz_id) ON monitoring_object.mno_id = monitoring_feature.mnf_id) ON namespace_type.ns_id = monitoring_object.inspire_id_namespace_id) ON wkr_version.wkrv_id = monitoring_object.wkrv_id
   LEFT JOIN eenheid_type ON monitoring_feature.pos_accuracy_horizontal_uom_id = eenheid_type.eenh_id
   LEFT JOIN specialised_emf_type ON environmental_monitoring_facility.speciallised_emf_type_id = specialised_emf_type.semf_id
   LEFT JOIN result_acquisition_source_type ON environmental_monitoring_facility.result_acquisition_source_id = result_acquisition_source_type.ras_id
   LEFT JOIN measurement_regime_type ON environmental_monitoring_facility.measurement_regime_id = measurement_regime_type.mmr_id
   LEFT JOIN (subsite_type
   RIGHT JOIN monitoring_station ON subsite_type.sst_id = monitoring_station.subsite_type_id) ON environmental_monitoring_facility.emf_id = monitoring_station.mts_id
  WHERE ST_GeometryType(monitoring_object.geometry) = 'ST_Point'
  ORDER BY monitoring_object.inspire_id_local_id, monitoring_object.wkrv_id DESC;

ALTER TABLE public.vw_meetpunten_shape
  OWNER TO  pgsql;

CREATE OR REPLACE VIEW public.vw_tijdwaarden_bestrijdingsmiddelen_g AS 
 SELECT observation.obs_id AS db_id, 
    monitoring_object.mno_id AS meetpunt_identificatie_db_id, 
    monitoring_object.inspire_id_local_id AS "Meetpunt.identificatie", 
    monitoring_object.name AS "Meetpunt.omschrijving", 
    st_x(st_transform(monitoring_object.geometry, 28992)) AS "GeometriePunt.X_RD", 
    st_y(st_transform(monitoring_object.geometry, 28992)) AS "GeometriePunt.Y_RD", 
    monitoring_object.geometry, namespace_type_1.code AS "Waterbeheerder.code", 
    namespace_type_1.descrption AS "Waterbeheerder.omschrijving", 
    typering_type.code AS "Typering.code", 
    typering_type.omschrijving AS "Typering.omschrijving", 
    grootheid_type.code AS "Grootheid.code", 
    grootheid_type.omschrijving AS "Grootheid.omschrijving", 
    chemische_stof_type.code AS "Parameter.code", 
    chemische_stof_type.naam AS "Parameter.omschrijving", 
    chemische_stof_type.cas_nr AS "Chemischestof.casnr", 
    object_type.code AS "Object.code", 
    object_type.omschrijving AS "Object.omschrijving", 
    eenheid_type.code AS "Eenheid.code", 
    eenheid_type.omschrijving AS "Eenheid.omschrijving", 
    hoedanigheid_type.code AS "Hoedanigheid.code", 
    hoedanigheid_type.omschrijving AS "Hoedanigheid.omschrijving", 
    compartiment_type.code AS "Compartiment.code", 
    compartiment_type.description AS "Compartiment.omschrijving", 
    waarde_bewerkings_methode_type.code AS "Waardebewerkingsmethode.code", 
    waarde_bewerkings_methode_type.omschrijving AS "Waardebewerkingsmethode.omschrijving", 
    ''::text AS "Waardebepalingsmethode.code", 
    ''::text AS "Waardebepalingsmethode.omschrijving", 
    classification_group_type.code AS "Classificatie.waarde", 
    classification_group_type.description AS "Classificatie.groep", 
    to_char(observation.phenomenon_time_begin, 'YYYY-MM-DD'::text) AS "Begindatum", 
    to_char(observation.phenomenon_time_begin, 'HH24:MM:SS'::text) AS "Begintijd", 
    to_char(observation.phenomenon_time_end, 'YYYY-MM-DD'::text) AS "Einddatum", 
    to_char(observation.phenomenon_time_end, 'HH24:MM:SS'::text) AS "Eindtijd", 
    bepaling_grens_type.omschrijving AS "Limietsymbool", 
    observation.numeric_value AS "Numeriekewaarde", 
    observation.description AS "Alfanumeriekewaarde", 
    kwaliteitsoordeel_type.code AS "Kwaliteitsoordeel.code", 
    kwaliteitsoordeel_type.description AS "Kwaliteitsoordeel.omschrijving", 
    observation.remarks AS "Opmerking", 
    sg."Groepsnaam" AS "SubstanceGroup.omschrijving", 
    sg."Type" AS "SubstanceGroup.type", observation.last_change_time, 
    monitoring_object.wkrv_id
   FROM observed_property_type
   JOIN observation ON observation.observed_property_type = observed_property_type.opt_id
   LEFT JOIN object_group ON object_group.ojg_id = observed_property_type.object_group_id
   LEFT JOIN typering_type ON typering_type.typ_id = observed_property_type.indicator_id
   LEFT JOIN grootheid_type ON observed_property_type.quantity_id = grootheid_type.grh_id
   JOIN monitored_compartiment ON monitored_compartiment.mno_id = observation.mno_id
   JOIN compartiment_type ON monitored_compartiment.cptm_id = compartiment_type.cptm_id
   JOIN (chemische_stof_type
   LEFT JOIN substance_group_element ON chemische_stof_type.chs_id = substance_group_element.chs_id) ON observed_property_type.substance_group_id = substance_group_element.ssge_id
   LEFT JOIN kwaliteitsoordeel_type ON observation.quality_indicator_id = kwaliteitsoordeel_type.kwod_id
   LEFT JOIN classification_group_type ON observation.classified_result_id = classification_group_type.clsg_id
   JOIN eenheid_type ON observation.numeric_value_uom_id = eenheid_type.eenh_id
   LEFT JOIN bepaling_grens_type ON observation.limit_symbol_id = bepaling_grens_type.bpg_id
   JOIN waarde_bewerkings_methode_type ON observation.value_processing_method_id = waarde_bewerkings_methode_type.wbm_id
   LEFT JOIN object_group_element ON object_group.ojg_id = object_group_element.ojg_id
   LEFT JOIN object_type ON object_group_element.obj_id = object_type.obj_id
   JOIN (namespace_type namespace_type_1
   LEFT JOIN monitoring_object ON namespace_type_1.ns_id = monitoring_object.inspire_id_namespace_id) ON observation.mno_id = monitoring_object.mno_id
   LEFT JOIN taxa_group_element ON observed_property_type.taxa_group_id = taxa_group_element.txg_id
   LEFT JOIN taxon_type ON observed_property_type.taxa_group_id = taxa_group_element.txg_id
   JOIN hoedanigheid_type ON hoedanigheid_type.hdh_id = observed_property_type.hoedanigheid_id
   JOIN vw_chemischestof_groepen sg ON sg."ChemischeStof_Code"::text = chemische_stof_type.code::text
  WHERE sg."Groepsnaam"::text = 'Bestrijdingsmiddelen CML'::text AND monitoring_object.wkrv_id = 7 
    AND ST_GeometryType(monitoring_object.geometry) = 'ST_Point'
  ORDER BY namespace_type_1.code, monitoring_object.inspire_id_local_id, chemische_stof_type.code, to_char(observation.phenomenon_time_begin, 'YYYY-MM-DD'::text) DESC;


ALTER TABLE public.vw_tijdwaarden_bestrijdingsmiddelen_g
  OWNER TO  pgsql;
  
  
CREATE OR REPLACE VIEW public.vw_download_meetlocaties_krm AS 
 SELECT mo.geometry, mo.inspire_id_local_id AS mpnident, mo.name AS mpnomsch, 
    mo.inspire_id_namespace_id AS wbhcode, 
    mo.begin_lifespan_version AS startdatum, 
    mo.end_lifespan_version AS einddatum, mo.additional_description AS memo, 
    oe.naam AS externbron, mooi.hydro_id_local_id AS externcode, 
    nf.emn_id AS meetnetid
   FROM monitoring_object mo
   JOIN monitoring_feature mf ON mo.mno_id = mf.mnf_id
   JOIN environmental_monitoring_facility emf ON mo.mno_id = emf.emf_id
   JOIN network_facility nf ON nf.emf_id = emf.emf_id
   LEFT JOIN monitoring_object_other_ids mooi ON mooi.mno_id = mo.mno_id
   LEFT JOIN organisatorische_eenheid oe ON oe.ore_id = mooi.hydro_id_ore_id;

ALTER TABLE public.vw_download_meetlocaties_krm
  OWNER TO  pgsql;

CREATE OR REPLACE VIEW public.vw_meetpunt_meetnet_csv AS 
 SELECT mpc.meetpunt_identificatie_db_id, mpc."Meetpunt.identificatie", 
    mpc."Meetpunt.omschrijving", mpc."GeometriePunt.X_RD", 
    mpc."GeometriePunt.Y_RD", mpc."Waterbeheerder.code", 
    mpc."Waterbeheerder.omschrijving", 
    mmp."Meetnet_Identificatie" AS "Meetnet.identificatie", 
    mmp."Meetnet_Omschrijving" AS "Meetnet.omschrijving", 
    mmp.wkr_versie AS "Versie"
   FROM vw_meetpunten_csv mpc
   JOIN vw_meetnet_meetpunt_g mmp ON mmp."Meetpunt_Identificatie"::text = mpc."Meetpunt.identificatie"::text
  ORDER BY mpc."Waterbeheerder.code", mpc."Meetpunt.identificatie";

ALTER TABLE public.vw_meetpunt_meetnet_csv
  OWNER TO pgsql;


  RETURN 'Success';
END;
$$;




--
-- Name: monitoring_feature; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE monitoring_feature (
    mnf_id integer NOT NULL,
    monitoring_feature_type character varying(50) NOT NULL,
    pos_accuracy_horizontal_value double precision,
    pos_accuracy_horizontal_uom_id integer,
    pos_accuracy_vertical_value double precision,
    pos_accuracy_vertical_uom_id integer,
    last_change_time timestamp without time zone NOT NULL
);


--
-- Name: COLUMN monitoring_feature.monitoring_feature_type; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN monitoring_feature.monitoring_feature_type IS 'Om facility en network te onderscheiden';


--
-- Name: monitoring_feature_positioning_method; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE monitoring_feature_positioning_method (
    mnf_id integer NOT NULL,
    pba_id integer NOT NULL,
    last_change_time timestamp without time zone NOT NULL
);


--
-- Name: monitoring_object; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE monitoring_object (
    mno_id integer NOT NULL,
    inspire_id_local_id character varying(36) NOT NULL,
    inspire_id_namespace_id integer NOT NULL,
    wkrv_id integer NOT NULL,
    monitoring_object_type character varying(50) NOT NULL,
    name character varying(255),
    additional_description text,
    geometry geometry(Geometry,28992),
    last_change_time timestamp without time zone NOT NULL,
    geometry_etrs89 geometry(Geometry,4258),
    begin_lifespan_version timestamp without time zone DEFAULT '1999-12-31 00:00:00'::timestamp without time zone NOT NULL,
    end_lifespan_version timestamp without time zone,
    primary_geo_column character varying(32) DEFAULT 'geometry'::character varying NOT NULL,
    reason_change text,
    primary_geodetic_reference_id integer DEFAULT 3331 NOT NULL,
    CONSTRAINT chk_primary_geo_column CHECK ((((primary_geo_column)::text = 'geometry'::text) OR ((primary_geo_column)::text = 'geometry_etrs89'::text)))
);


--
-- Name: COLUMN monitoring_object.monitoring_object_type; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN monitoring_object.monitoring_object_type IS 'Bepaalt het subtype monitoring_object';


--
-- Name: namespace_type; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE namespace_type (
    ns_id integer NOT NULL,
    code character varying(2) NOT NULL,
    descrption character varying(90),
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying
);


--
-- Name: plaatsbepalingsapparaat_type; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE plaatsbepalingsapparaat_type (
    pba_id integer NOT NULL,
    code character varying(12) NOT NULL,
    description character varying(90) NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying,
    id integer
);


--
-- Name: rel_samp_feat_role_type; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE rel_samp_feat_role_type (
    rsfr_id integer NOT NULL,
    code character varying(12) NOT NULL,
    description character varying(90) NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying
);


--
-- Name: related_sampling_feature; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE related_sampling_feature (
    mnf_id_source integer NOT NULL,
    mnf_id_target integer NOT NULL,
    role_id integer NOT NULL,
    last_change_time timestamp without time zone NOT NULL
);



--
-- Name: vw_imwa_meetpunten_csv; Type: VIEW; Schema: aquo_formaten; Owner: -
--

CREATE VIEW vw_imwa_meetpunten_csv AS
 SELECT monitoring_object.inspire_id_local_id AS "Identificatie",
    ('NL'::text || (namespace_type.code)::text) AS "Namespace",
    monitoring_object.wkrv_id AS "Versie",
    monitoring_object.name AS "Omschrijving",
    round((public.st_x(public.st_transform(monitoring_object.geometry, 28992)))::numeric, 3) AS "GeometriePunt.X_RD",
    round((public.st_y(public.st_transform(monitoring_object.geometry, 28992)))::numeric, 3) AS "GeometriePunt.Y_RD",
    pba.code AS "Plaatsbepaling.Code",
        CASE
            WHEN (pba.code IS NOT NULL) THEN 'www.aquo.nl'::text
            ELSE NULL::text
        END AS "Plaatsbepaling.Codespace",
    'meetpunt'::text AS "MeetobjectSoort.code",
        CASE
            WHEN (mno_to.inspire_id_local_id IS NOT NULL) THEN ((('NL'::text || (ns2.code)::text) || '_'::text) || (mno_to.inspire_id_local_id)::text)
            ELSE NULL::text
        END AS "GerelateerdMeetobject",
        CASE
            WHEN (mno_to.inspire_id_local_id IS NOT NULL) THEN (sf_rol.code)::text
            ELSE NULL::text
        END AS "GerelateerdMeetobjectRol.Id",
    monitoring_object.mno_id AS meetpunt_identificatie_db_id,
    monitoring_object.last_change_time
   FROM ((((((((public.monitoring_object
     LEFT JOIN public.namespace_type ON ((namespace_type.ns_id = monitoring_object.inspire_id_namespace_id)))
     LEFT JOIN public.monitoring_feature ON ((monitoring_feature.mnf_id = monitoring_object.mno_id)))
     LEFT JOIN public.monitoring_feature_positioning_method mfpm ON ((monitoring_feature.mnf_id = mfpm.mnf_id)))
     LEFT JOIN public.plaatsbepalingsapparaat_type pba ON ((pba.pba_id = mfpm.pba_id)))
     LEFT JOIN public.related_sampling_feature rsf ON ((rsf.mnf_id_source = monitoring_object.mno_id)))
     LEFT JOIN public.rel_samp_feat_role_type sf_rol ON ((sf_rol.rsfr_id = rsf.role_id)))
     LEFT JOIN public.monitoring_object mno_to ON ((rsf.mnf_id_target = mno_to.mno_id)))
     LEFT JOIN public.namespace_type ns2 ON ((ns2.ns_id = mno_to.inspire_id_namespace_id)))
  WHERE ((public.st_geometrytype(monitoring_object.geometry) = 'ST_Point'::text) AND ((monitoring_object.monitoring_object_type)::text ~~ 'MonitoringFeature%'::text));



--
-- Name: bemonsteringsmethode; Type: TABLE; Schema: domains; Owner: -
--

CREATE TABLE bemonsteringsmethode (
    bmm_id integer NOT NULL,
    code character varying(50) NOT NULL,
    description character varying(90) NOT NULL,
    groep character varying(32) NOT NULL,
    titel character varying(255) NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying,
    id integer
);


--
-- Name: monsterbewerkingsmethode; Type: TABLE; Schema: domains; Owner: -
--

CREATE TABLE monsterbewerkingsmethode (
    mbm_id integer NOT NULL,
    code character varying(50) NOT NULL,
    description character varying(255) NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying,
    id integer,
    codespace character varying(25),
    last_changed_date timestamp without time zone,
    groep character varying(32) NOT NULL,
    titel character varying(255) NOT NULL
);


--
-- Name: orgaan; Type: TABLE; Schema: domains; Owner: -
--

CREATE TABLE orgaan (
    org_id integer NOT NULL,
    code character varying(5) NOT NULL,
    description character varying(50) NOT NULL,
    groep character varying(50),
    d_begin timestamp without time zone NOT NULL,
    d_eind timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying NOT NULL
);


--
-- Name: veldapparaat; Type: TABLE; Schema: domains; Owner: -
--

CREATE TABLE veldapparaat (
    var_id integer NOT NULL,
    code character varying(2) NOT NULL,
    description character varying(90) NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying,
    id integer,
    codespace character varying(25),
    groep character varying(32) NOT NULL
);



--
-- Name: compartiment_type; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE compartiment_type (
    cptm_id integer NOT NULL,
    code character varying(50) NOT NULL,
    description character varying(50) NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying,
    id integer
);


--
-- Name: eenheid_type; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE eenheid_type (
    eenh_id integer NOT NULL,
    code character varying(50) NOT NULL,
    omschrijving character varying(50) NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying,
    id integer
);


--
-- Name: hoedanigheid_type; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE hoedanigheid_type (
    hdh_id integer NOT NULL,
    code character varying(12) NOT NULL,
    omschrijving character varying(90) NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying,
    id integer
);


--
-- Name: sample; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE sample (
    smpl_id integer NOT NULL,
    name character varying(255),
    material_class_id integer NOT NULL,
    specimen_type_id integer,
    sampling_time timestamp without time zone NOT NULL,
    remarks character varying(255),
    sampling_location geometry(Geometry,28992),
    size_value double precision,
    size_uom_id integer,
    upper_depth_value double precision,
    upper_depth_uom_id integer,
    upper_depth_condition_id integer,
    lower_depth_value double precision,
    lower_depth_uom_id integer,
    lower_depth_condition_id integer,
    start_time timestamp without time zone,
    end_time timestamp without time zone,
    fraction_criterium_id integer,
    organ_id integer,
    biota_criterium_id integer,
    txn_id integer,
    smp_id integer NOT NULL
);


--
-- Name: COLUMN sample.smpl_id; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN sample.smpl_id IS 'is de abstacte relatie tussen monitoring_object';


--
-- Name: sampling_proces; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE sampling_proces (
    smp_id integer NOT NULL,
    nen3610_id_namespace_id integer,
    nen3610_id_local_id text,
    sampling_method_id integer,
    sampling_processing_method_id integer,
    sampling_operator_id integer,
    "time" timestamp without time zone,
    process_operator_id integer,
    var_id integer
);


--
-- Name: taxon_type; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE taxon_type (
    txn_id integer NOT NULL,
    code character varying(90) NOT NULL,
    omschrijving character varying(90) NOT NULL,
    auteur character varying(90),
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying
);



--
-- Name: vw_imwa_monster_csv; Type: VIEW; Schema: aquo_formaten; Owner: -
--

CREATE VIEW vw_imwa_monster_csv AS
 SELECT mno.inspire_id_local_id AS "Identificatie",
    ('NL'::text || (nst.code)::text) AS "Namespace",
    mno.wkrv_id AS "Versie",
    ((('NL'::text || (ns2.code)::text) || '_'::text) || (mno_mpn.inspire_id_local_id)::text) AS "Meetpunt.Identificatie",
    cmp.code AS "Compartiment.Code",
    cmp.description AS "Compartiment.Omschrijving",
    orgaan.code AS "Orgaan.Code",
    orgaan.description AS "Orgaan.Omschrijving",
    taxon_type.omschrijving AS "Organisme.Naam",
    smpl.upper_depth_value AS "BeginDiepte_m",
    smpl.lower_depth_value AS "EindDiepte_m",
        CASE
            WHEN (hdh_ud.hdh_id IS NULL) THEN
            CASE
                WHEN (hdh_ld.hdh_id IS NULL) THEN (NULL::text)::character varying
                ELSE hdh_ld.code
            END
            ELSE hdh_ud.code
        END AS "ReferentieVlak.Code",
    mbm.code AS "Monsterbewerkingsmethode.Code",
    mbm.description AS "Monsterbewerkingsmethode.Omschrijving",
    mbm.codespace AS "Monsterbewerkingsmethode.codespace",
    bmm.code AS "Bemonsteringsmethode.Code",
    bmm.description AS "Bemonsteringsmethode.Omschrijving",
        CASE
            WHEN (bmm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Bemonsteringsmethode.codespace",
        CASE
            WHEN (frac.code IS NULL) THEN biota.code
            ELSE frac.code
        END AS "Monstercriterium.Code",
    var.code AS "veldapparaat.Code",
    var.description AS "veldapparaat.Omschrijving",
    var.codespace AS "veldapparaat.Codespace",
    to_char(smpl.sampling_time, 'YYYY-MM-DD'::text) AS "Monsternemingsdatum",
    to_char(smpl.sampling_time, 'HH24:MM:SS'::text) AS "Monsternemingstijd",
    NULL::integer AS "Tijd_UTCoffset",
    round((public.st_x(public.st_transform(mno.geometry, 28992)))::numeric, 3) AS "GeometriePunt.X",
    round((public.st_y(public.st_transform(mno.geometry, 28992)))::numeric, 3) AS "GeometriePunt.Y",
    'EPSG28992'::text AS "ReferentieHorizontaal.code",
    smpl.remarks AS "Opmerking",
        CASE
            WHEN (mno_smpl.wkrv_id IS NULL) THEN NULL::text
            ELSE ((('NL'::text || (ns3.code)::text) || '_'::text) || (mno_smpl.inspire_id_local_id)::text)
        END AS "GerelateerdMonsterobject",
        CASE
            WHEN (mno_smpl.wkrv_id IS NULL) THEN (NULL::text)::character varying
            ELSE rsfr.code
        END AS "GerelateerdMonsterRol.id",
    smpl.smpl_id AS monster_db_id,
    mno.last_change_time AS laatste_wijziging
   FROM ((((((((((((((((((((((public.sample smpl
     JOIN public.monitoring_object mno ON ((mno.mno_id = smpl.smpl_id)))
     JOIN public.namespace_type nst ON ((mno.inspire_id_namespace_id = nst.ns_id)))
     JOIN public.compartiment_type cmp ON ((smpl.material_class_id = cmp.cptm_id)))
     LEFT JOIN public.hoedanigheid_type hdh_ud ON ((hdh_ud.hdh_id = smpl.upper_depth_condition_id)))
     LEFT JOIN public.hoedanigheid_type hdh_ld ON ((hdh_ld.hdh_id = smpl.lower_depth_condition_id)))
     LEFT JOIN public.taxon_type ON ((smpl.txn_id = taxon_type.txn_id)))
     LEFT JOIN domains.orgaan ON ((orgaan.org_id = smpl.organ_id)))
     LEFT JOIN public.hoedanigheid_type frac ON ((frac.hdh_id = smpl.fraction_criterium_id)))
     LEFT JOIN public.hoedanigheid_type biota ON ((biota.hdh_id = smpl.biota_criterium_id)))
     LEFT JOIN public.eenheid_type eenh_ud ON ((eenh_ud.eenh_id = smpl.upper_depth_uom_id)))
     LEFT JOIN public.eenheid_type eenh_ld ON ((eenh_ld.eenh_id = smpl.lower_depth_uom_id)))
     LEFT JOIN public.sampling_proces smp ON ((smp.smp_id = smpl.smp_id)))
     LEFT JOIN domains.monsterbewerkingsmethode mbm ON ((mbm.mbm_id = smp.sampling_method_id)))
     LEFT JOIN domains.bemonsteringsmethode bmm ON ((bmm.bmm_id = smp.sampling_processing_method_id)))
     LEFT JOIN domains.veldapparaat var ON ((var.var_id = smp.var_id)))
     LEFT JOIN public.related_sampling_feature rel_smpl ON ((smpl.smpl_id = rel_smpl.mnf_id_source)))
     LEFT JOIN public.sample smpl2 ON ((smpl.smpl_id = rel_smpl.mnf_id_target)))
     LEFT JOIN public.monitoring_object mno_mpn ON (((mno_mpn.mno_id = rel_smpl.mnf_id_target) AND ((mno_mpn.monitoring_object_type)::text <> 'Sample'::text))))
     LEFT JOIN public.monitoring_object mno_smpl ON (((smpl2.smpl_id = mno_smpl.mno_id) AND ((mno_smpl.monitoring_object_type)::text = 'Sample'::text))))
     LEFT JOIN public.namespace_type ns2 ON ((ns2.ns_id = mno_mpn.inspire_id_namespace_id)))
     LEFT JOIN public.namespace_type ns3 ON ((ns3.ns_id = mno_smpl.inspire_id_namespace_id)))
     LEFT JOIN public.rel_samp_feat_role_type rsfr ON ((rsfr.rsfr_id = rel_smpl.role_id)));



--
-- Name: meetapparaat; Type: TABLE; Schema: domains; Owner: -
--

CREATE TABLE meetapparaat (
    mar_id integer NOT NULL,
    code character varying(50) NOT NULL,
    description character varying(255) NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying
);



--
-- Name: bio_observation; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE bio_observation (
    obs_id integer NOT NULL,
    levensstadium_id integer,
    lengte_klasse_id integer,
    geslacht_id integer,
    verschijningsvorm_id integer,
    levensvorm_id integer,
    gedrag_id integer
);


--
-- Name: chemische_stof_type; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE chemische_stof_type (
    chs_id integer NOT NULL,
    cas_nr character varying(12) NOT NULL,
    naam character varying(90) NOT NULL,
    code character varying(12) NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying,
    id integer
);


--
-- Name: grootheid_type; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE grootheid_type (
    grh_id integer NOT NULL,
    code character varying(50) NOT NULL,
    omschrijving character varying(90) NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying,
    id integer
);


--
-- Name: hydro_object; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE hydro_object (
    hdo_id integer NOT NULL,
    inspire_id_local_id character varying(36) NOT NULL,
    inspire_id_namespace integer NOT NULL,
    wkrv_id integer NOT NULL,
    hydro_object_type character varying(50) NOT NULL,
    related_hdo_id integer,
    last_change_time timestamp without time zone NOT NULL
);


--
-- Name: COLUMN hydro_object.hydro_object_type; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN hydro_object.hydro_object_type IS 'Enumeratie van subtypen van hydro_objecten.';


--
-- Name: kwaliteitsoordeel_type; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE kwaliteitsoordeel_type (
    kwod_id integer NOT NULL,
    code character varying(2) NOT NULL,
    description character varying(90) NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying,
    id integer
);


--
-- Name: monitored_compartiment; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE monitored_compartiment (
    mno_id integer NOT NULL,
    cptm_id integer NOT NULL,
    last_change_time timestamp without time zone NOT NULL
);


--
-- Name: object_group; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE object_group (
    ojg_id integer NOT NULL,
    name character varying(90) NOT NULL,
    group_type character varying(1) NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying
);


--
-- Name: COLUMN object_group.group_type; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN object_group.group_type IS 'Enumeratie: I=Individual, O=Observation, N=Normal';


--
-- Name: object_group_element; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE object_group_element (
    ojg_id integer NOT NULL,
    obj_id integer NOT NULL
);


--
-- Name: object_type; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE object_type (
    obj_id integer NOT NULL,
    code character varying(12) NOT NULL,
    omschrijving character varying(90) NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying,
    id integer
);


--
-- Name: obs_ctx_role_type; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE obs_ctx_role_type (
    ocr_id integer NOT NULL,
    code character varying(50) NOT NULL,
    description text NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying
);


--
-- Name: obs_process_meetapparaat; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE obs_process_meetapparaat (
    obp_id integer NOT NULL,
    mar_id integer NOT NULL,
    last_change_time timestamp without time zone NOT NULL
);


--
-- Name: observation; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE observation (
    obs_id integer NOT NULL,
    phenomenon_time_begin timestamp without time zone NOT NULL,
    phenomenon_time_end timestamp without time zone,
    result_time timestamp without time zone NOT NULL,
    valid_time_begin timestamp without time zone,
    valid_time_end timestamp without time zone,
    observed_property_type integer NOT NULL,
    quality_indicator_id integer,
    numeric_value_uom_id integer,
    value_processing_method_id integer,
    limit_symbol_id integer,
    description text,
    classified_result_id integer,
    remarks text,
    result_nature_id integer,
    start_depth double precision,
    end_depth double precision,
    obs_parm_parent_id integer,
    mno_id integer,
    last_change_time timestamp without time zone NOT NULL,
    numeric_value double precision,
    numeric_value_significance integer
    , mw_id integer	 -- toegevoegd als onofficiele verwijzing naar dump.meetwaarden tbv vulling bio_observations (kan evt na vulling verwijderd)
);


--
-- Name: observation_context; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE observation_context (
    obs_id_source integer NOT NULL,
    obs_id_target integer NOT NULL,
    role_id integer NOT NULL,
    last_change_time timestamp without time zone NOT NULL
);


--
-- Name: observation_process_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE observation_process_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: observation_process; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE observation_process (
    obp_id integer DEFAULT nextval('observation_process_seq'::regclass) NOT NULL,
    ore_id integer,
    last_change_time timestamp without time zone NOT NULL,
    wbpm_id integer,
    sensing_location_type_id integer,
    sample_pre_treatment_method_id integer,
    sample_destruction_method_id integer,
    certification_id integer,
    analytical_technique_id integer,
    accreditation_org_id integer,
    nen3610_id_namespace_id integer
);


--
-- Name: observed_feature; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE observed_feature (
    hdo_id integer NOT NULL,
    obs_id integer NOT NULL,
    last_change_time timestamp without time zone NOT NULL
);


--
-- Name: observed_property_type; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE observed_property_type (
    opt_id integer NOT NULL,
    observed_property_type_class character varying(50) NOT NULL,
    result_type character varying(50) NOT NULL,
    hoedanigheid_id integer,
    object_group_id integer,
    quantity_id integer,
    taxa_group_id integer,
    substance_group_id integer,
    indicator_id integer,
    classification_group_id integer,
    remarks text,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying
);


--
-- Name: TABLE observed_property_type; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON TABLE observed_property_type IS 'Type van de waargenomen/waar te nemen eigenschap, bedoeld als abstractie/generalisatie van verschillende typen waarnemingen, zoals PhysicalObservation, SpeciesObservation, CalculatedAnalysis, etc';


--
-- Name: COLUMN observed_property_type.result_type; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN observed_property_type.result_type IS 'Type resultaat (enumeratie) bepaalt hoe het resultaat vastgelegd moet worden (measure, analysis, classified, description)';


--
-- Name: substance_group; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE substance_group (
    ssg_id integer NOT NULL,
    name character varying(90) NOT NULL,
    group_type character varying(1) NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying
);


--
-- Name: substance_group_element; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE substance_group_element (
    ssge_id integer NOT NULL,
    chs_id integer NOT NULL
);


--
-- Name: taxa_group; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE taxa_group (
    txg_id integer NOT NULL,
    name character varying(90) NOT NULL,
    group_type character varying(1) NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying
);


--
-- Name: taxa_group_element; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE taxa_group_element (
    txg_id integer NOT NULL,
    txn_id integer NOT NULL
);


--
-- Name: typering_type; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE typering_type (
    typ_id integer NOT NULL,
    code character varying(12) NOT NULL,
    omschrijving character varying(90) NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying,
    id integer
);


--
-- Name: waarde_bepalings_methode_type; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE waarde_bepalings_methode_type (
    wbpm_id integer NOT NULL,
    code character varying(24) NOT NULL,
    omschrijving character varying(90) NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying,
    id integer,
    codespace character varying(10)
);


--
-- Name: waarde_bewerkings_methode_type; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE waarde_bewerkings_methode_type (
    wbm_id integer NOT NULL,
    code character varying(12) NOT NULL,
    omschrijving character varying(90) NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying,
    id integer
);



--
-- Name: vw_imwa_tijdwaarden_csv; Type: VIEW; Schema: aquo_formaten; Owner: -
--

CREATE VIEW vw_imwa_tijdwaarden_csv AS
 WITH bio AS (
         SELECT bio_1.obs_id,
            gedrag.code AS gedrag_code,
            geslacht.code AS geslacht_code,
            lengte.code AS lengte_code,
            levensstadium.code AS levensstadium_code,
            levensvorm.code AS levensvorm_code,
            verschijning.code AS verschijning_code
           FROM ((((((public.bio_observation bio_1
             LEFT JOIN public.hoedanigheid_type gedrag ON (((bio_1.gedrag_id = gedrag.hdh_id) AND (bio_1.gedrag_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type geslacht ON (((bio_1.geslacht_id = geslacht.hdh_id) AND (bio_1.geslacht_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type lengte ON (((bio_1.lengte_klasse_id = lengte.hdh_id) AND (bio_1.lengte_klasse_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type levensstadium ON (((bio_1.levensstadium_id = levensstadium.hdh_id) AND (bio_1.levensstadium_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type levensvorm ON (((bio_1.levensvorm_id = levensvorm.hdh_id) AND (bio_1.levensvorm_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type verschijning ON (((bio_1.verschijningsvorm_id = verschijning.hdh_id) AND (bio_1.verschijningsvorm_id IS NOT NULL))))
          WHERE ((bio_1.gedrag_id IS NOT NULL) AND (bio_1.geslacht_id IS NOT NULL) AND (bio_1.lengte_klasse_id IS NOT NULL) AND (bio_1.levensstadium_id IS NOT NULL) AND (bio_1.levensvorm_id IS NOT NULL) AND (bio_1.verschijningsvorm_id IS NOT NULL))
        ), txg AS (
         SELECT txg.txg_id,
            txt.omschrijving
           FROM ((public.taxa_group txg
             JOIN public.taxa_group_element tge ON ((tge.txg_id = txg.txg_id)))
             JOIN public.taxon_type txt ON ((txt.txn_id = tge.txn_id)))
          WHERE ((txg.group_type)::text = 'I'::text)
        ), chs AS (
         SELECT ssg.ssg_id,
            chs.code,
            chs.naam
           FROM ((public.substance_group ssg
             JOIN public.substance_group_element ssge ON ((ssge.ssge_id = ssg.ssg_id)))
             JOIN public.chemische_stof_type chs ON ((chs.chs_id = ssge.chs_id)))
          WHERE ((ssg.group_type)::text = 'I'::text)
        ), obj AS (
         SELECT ojg.ojg_id,
            obj.code,
            obj.omschrijving
           FROM ((public.object_group ojg
             JOIN public.object_group_element ojge ON ((ojg.ojg_id = ojge.ojg_id)))
             JOIN public.object_type obj ON ((obj.obj_id = ojge.obj_id)))
          WHERE ((ojg.group_type)::text = 'I'::text)
        ), opt AS (
         SELECT opt_1.opt_id,
            grh.code AS grh_code,
            grh.omschrijving AS grh_omschrijving,
            typ.code AS typ_code,
            typ.omschrijving AS typ_omschrijving,
            txg.omschrijving AS txg_omschrijving,
            chs.code AS chs_code,
            chs.naam AS chs_naam,
            obj.code AS obj_code,
            obj.omschrijving AS obj_omschrijving,
            hdh.code AS hdh_code,
            hdh.omschrijving AS hdh_omschrijving
           FROM ((((((public.observed_property_type opt_1
             LEFT JOIN public.grootheid_type grh ON (((grh.grh_id = opt_1.quantity_id) AND (opt_1.quantity_id IS NOT NULL))))
             LEFT JOIN public.typering_type typ ON (((typ.typ_id = opt_1.indicator_id) AND (opt_1.indicator_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type hdh ON (((hdh.hdh_id = opt_1.hoedanigheid_id) AND (opt_1.hoedanigheid_id IS NOT NULL))))
             LEFT JOIN txg ON (((txg.txg_id = opt_1.taxa_group_id) AND (opt_1.taxa_group_id IS NOT NULL))))
             LEFT JOIN chs ON (((chs.ssg_id = opt_1.substance_group_id) AND (opt_1.substance_group_id IS NOT NULL))))
             LEFT JOIN obj ON (((obj.ojg_id = opt_1.object_group_id) AND (opt_1.object_group_id IS NOT NULL))))
        ), obf AS (
         SELECT of.obs_id,
            (('NL'::text || (ns_hdo.code)::text) || (hdo.inspire_id_local_id)::text) AS local_id
           FROM ((public.observed_feature of
             JOIN public.hydro_object hdo ON ((of.hdo_id = hdo.hdo_id)))
             JOIN public.namespace_type ns_hdo ON ((hdo.inspire_id_namespace = ns_hdo.ns_id)))
        ), obp AS (
         SELECT obp_1.obp_id,
            wbpm.code AS wbpm_code,
            mar.code AS mar_code
           FROM (((public.observation_process obp_1
             LEFT JOIN public.waarde_bepalings_methode_type wbpm ON (((wbpm.wbpm_id = obp_1.wbpm_id) AND (obp_1.wbpm_id IS NOT NULL))))
             LEFT JOIN public.obs_process_meetapparaat opm ON (((opm.obp_id = obp_1.obp_id) AND (obp_1.obp_id IS NOT NULL))))
             LEFT JOIN domains.meetapparaat mar ON (((mar.mar_id = opm.mar_id) AND (opm.mar_id IS NOT NULL))))
          WHERE ((obp_1.wbpm_id IS NOT NULL) AND (obp_1.obp_id IS NOT NULL) AND (opm.mar_id IS NOT NULL))
        ), mno AS (
         SELECT mno_1.mno_id,
            mno_1.wkrv_id,
            nst.code AS namespace,
            mno_1.monitoring_object_type,
            ((('NL'::text || (nst.code)::text) || '_AQUO_COMPARTIMENT:'::text) || (compartiment_type.code)::text) AS cmp_code,
            ((('NL'::text || (nst.code)::text) || '_'::text) || (mno_1.inspire_id_local_id)::text) AS local_id
           FROM (((public.monitoring_object mno_1
             LEFT JOIN public.monitored_compartiment ON ((monitored_compartiment.mno_id = mno_1.mno_id)))
             LEFT JOIN public.compartiment_type ON ((monitored_compartiment.cptm_id = compartiment_type.cptm_id)))
             JOIN public.namespace_type nst ON ((nst.ns_id = mno_1.inspire_id_namespace_id)))
        )
 SELECT
        CASE
            WHEN (obf.obs_id IS NOT NULL) THEN obf.local_id
            ELSE
            CASE
                WHEN ((mno.monitoring_object_type)::text = 'Sample'::text) THEN NULL::text
                ELSE mno.cmp_code
            END
        END AS "Geoobject.Identificatie",
        CASE
            WHEN ((mno.monitoring_object_type)::text = 'Sample'::text) THEN mno.local_id
            ELSE NULL::text
        END AS "Monster.Identificatie",
        CASE
            WHEN ((mno.monitoring_object_type)::text = 'Sample'::text) THEN NULL::text
            ELSE mno.local_id
        END AS "Meetpunt.Identificatie",
    obs.obs_id AS "Identificatie",
    ('NL'::text || (mno.namespace)::text) AS "Namespace",
    mno.wkrv_id AS "Versie",
    opt.grh_code AS "Grootheid.code",
    opt.grh_omschrijving AS "Grootheid.omschrijving",
    opt.typ_code AS "Typering.code",
    opt.typ_omschrijving AS "Typering.omschrijving",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN 'Biotaxon'::text
            WHEN (opt.chs_code IS NOT NULL) THEN 'ChemischeStof'::text
            WHEN (opt.obj_code IS NOT NULL) THEN 'Object'::text
            ELSE NULL::text
        END AS "Parameter.Groep",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN (NULL::text)::character varying
            WHEN (opt.chs_code IS NOT NULL) THEN opt.chs_code
            WHEN (opt.obj_code IS NOT NULL) THEN opt.obj_code
            ELSE NULL::character varying
        END AS "Parameter.Code",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN opt.txg_omschrijving
            WHEN (opt.chs_code IS NOT NULL) THEN opt.chs_naam
            WHEN (opt.obj_code IS NOT NULL) THEN opt.obj_omschrijving
            ELSE NULL::character varying
        END AS "Parameter.Omschrijving",
    eenh.code AS "Eenheid.code",
    eenh.omschrijving AS "Eenheid.omschrijving",
    opt.hdh_code AS "Hoedanigheid.code",
    opt.hdh_omschrijving AS "Hoedanigheid.omschrijving",
    wbm.code AS "Waardebewerkigngsmethode.code",
        CASE
            WHEN (wbm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Waardebewerkigngsmethode.codespace",
    obp.wbpm_code AS "Waardebepalingsmethode.code",
        CASE
            WHEN (obp.wbpm_code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Waardebepalingsmethode.codespace",
    to_char(obs.phenomenon_time_begin, 'YYYY-MM-DD'::text) AS "Begindatum",
    to_char(obs.phenomenon_time_begin, 'HH24:MM:SS'::text) AS "Begintijd",
    to_char(obs.phenomenon_time_end, 'YYYY-MM-DD'::text) AS "Einddatum",
    to_char(obs.phenomenon_time_end, 'HH24:MM:SS'::text) AS "Eindtijd",
    NULL::integer AS "Tijd_UTCoffset",
    to_char(obs.result_time, 'YYYY-MM-DD'::text) AS "Resultaatdatum",
    NULL::integer AS "Resultaat_UTCoffset",
    obs.limit_symbol_id AS "Limietsymbool",
    obs.numeric_value AS "Numeriekewaarde",
    obs.description AS "Alfanumeriekewaarde",
    kwaliteitsoordeel_type.code AS "Kwaliteitsoordeel.code",
    kwaliteitsoordeel_type.description AS "Kwaliteitsoordeel.omschrijving",
    obp.mar_code AS "Meetapparaat.code",
        CASE
            WHEN (obp.mar_code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Meetapparaat.codespace",
    bio.levensstadium_code AS "Levensstadium.code",
    bio.lengte_code AS "Lengteklasse.code",
    bio.geslacht_code AS "Geslachtcode",
    bio.verschijning_code AS "Verschijningsvorm.code",
    bio.levensvorm_code AS "Levensvorm.code",
    bio.gedrag_code AS "Gedrag.code",
        CASE
            WHEN (obx.obs_id_target IS NULL) THEN NULL::text
            ELSE ((('NL'::text || (mno.namespace)::text) || '_'::text) || (obx.obs_id_target)::text)
        END AS "GerelateerdObservatieobject",
    ocr.code AS "GerelateerdObservatieobjectRol.id",
    obs.remarks AS "Opmerking",
    obs.observed_property_type AS observedpropertytype_id,
    mno.mno_id AS monitoring_object_db_id,
    obs.last_change_time AS "Tijdstip.geladeninwkp"
   FROM ((((((((((public.observation obs
     LEFT JOIN public.eenheid_type eenh ON ((eenh.eenh_id = obs.numeric_value_uom_id)))
     JOIN mno ON ((mno.mno_id = obs.mno_id)))
     JOIN opt ON ((opt.opt_id = obs.observed_property_type)))
     LEFT JOIN public.kwaliteitsoordeel_type ON ((obs.quality_indicator_id = kwaliteitsoordeel_type.kwod_id)))
     LEFT JOIN obp ON ((obp.obp_id = obs.result_nature_id)))
     LEFT JOIN bio ON ((bio.obs_id = obs.obs_id)))
     LEFT JOIN public.observation_context obx ON ((obx.obs_id_source = obs.obs_id)))
     LEFT JOIN public.obs_ctx_role_type ocr ON ((ocr.ocr_id = obx.role_id)))
     LEFT JOIN public.waarde_bewerkings_methode_type wbm ON ((wbm.wbm_id = obs.value_processing_method_id)))
     LEFT JOIN obf ON ((obs.obs_id = obf.obs_id)));


--
-- Name: vw_umaquo_meetpunten_csv; Type: VIEW; Schema: aquo_formaten; Owner: -
--

CREATE VIEW vw_umaquo_meetpunten_csv AS
 SELECT monitoring_object.mno_id AS meetpunt_identificatie_db_id,
    monitoring_object.inspire_id_local_id AS "Meetpunt.identificatie",
    monitoring_object.name AS "Meetpunt.omschrijving",
    namespace_type.code AS "Waterbeheerder.code",
    namespace_type.descrption AS "Waterbeheerder.omschrijving",
    public.st_x(public.st_transform(monitoring_object.geometry, 28992)) AS "GeometriePunt.X_RD",
    public.st_y(public.st_transform(monitoring_object.geometry, 28992)) AS "GeometriePunt.Y_RD",
    monitoring_object.wkrv_id AS "Versie",
    monitoring_object.last_change_time
   FROM (((public.monitoring_object
     JOIN public.monitored_compartiment ON ((monitored_compartiment.mno_id = monitoring_object.mno_id)))
     JOIN public.namespace_type ON ((namespace_type.ns_id = monitoring_object.inspire_id_namespace_id)))
     JOIN public.monitoring_feature ON ((monitoring_feature.mnf_id = monitoring_object.mno_id)))
  WHERE (public.st_geometrytype(monitoring_object.geometry) = 'ST_Point'::text)
  ORDER BY namespace_type.code, monitoring_object.inspire_id_local_id;



--
-- Name: env_mon_facil_in_mngmt_reg_or_restr_zone; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE env_mon_facil_in_mngmt_reg_or_restr_zone (
    emf_id integer NOT NULL,
    mrz_id integer NOT NULL,
    last_change_time timestamp without time zone NOT NULL
);


--
-- Name: environmental_monitoring_facility; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE environmental_monitoring_facility (
    emf_id integer NOT NULL,
    measurement_regime_id integer NOT NULL,
    representative_point geometry(Point,28992),
    is_mobile boolean DEFAULT false,
    result_acquisition_source_id integer,
    speciallised_emf_type_id integer,
    ligt_in_mrz_id integer,
    last_change_time timestamp without time zone NOT NULL
);


--
-- Name: environmental_monitoring_network; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE environmental_monitoring_network (
    emn_id integer NOT NULL,
    organization_level_id integer NOT NULL,
    last_change_time timestamp without time zone NOT NULL
);


--
-- Name: legislation_level_type; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE legislation_level_type (
    lgl_id integer NOT NULL,
    code character varying(12) NOT NULL,
    description character varying(90) NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying
);


--
-- Name: measurement_regime_type; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE measurement_regime_type (
    mmr_id integer NOT NULL,
    code character varying(12) NOT NULL,
    description character varying(50),
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying
);


--
-- Name: monitoring_station; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE monitoring_station (
    mts_id integer NOT NULL,
    monitoring_station_type character varying(50) NOT NULL,
    number_of_points_in_subsite integer,
    subsite_type_id integer,
    last_change_time timestamp without time zone NOT NULL
);


--
-- Name: network_facility; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE network_facility (
    emf_id integer NOT NULL,
    emn_id integer NOT NULL,
    linking_time_begin timestamp without time zone NOT NULL,
    linking_time_end timestamp without time zone,
    last_change_time timestamp without time zone NOT NULL
);


--
-- Name: result_acquisition_source_type; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE result_acquisition_source_type (
    ras_id integer NOT NULL,
    code character varying(12) NOT NULL,
    description character varying(90),
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying
);


--
-- Name: specialised_emf_type; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE specialised_emf_type (
    semf_id integer NOT NULL,
    code character varying(12) NOT NULL,
    description character varying(90),
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying
);


--
-- Name: TABLE specialised_emf_type; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON TABLE specialised_emf_type IS 'NB: Heeft andere types dan Aquo.TypeMeting';


--
-- Name: subsite_type; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE subsite_type (
    sst_id integer NOT NULL,
    code character varying(12) NOT NULL,
    description character varying(90) NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying
);


--
-- Name: wkr_version; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE wkr_version (
    wkrv_id integer NOT NULL,
    begin_lifespan_version timestamp without time zone NOT NULL,
    end_lifespan_version timestamp without time zone,
    last_change_time timestamp without time zone NOT NULL
);


--
-- Name: vw_meetnetten; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_meetnetten AS
 SELECT monitoring_object.mno_id AS db_id,
    monitoring_object.inspire_id_local_id AS identificatie,
    monitoring_object.wkrv_id AS wkr_versie,
    wkr_version.begin_lifespan_version AS wkr_beginversie,
    wkr_version.end_lifespan_version AS wkr_eindversie,
    monitoring_object.name AS omschrijving,
    monitoring_object.additional_description AS "Toelichting:"
   FROM (wkr_version
     RIGHT JOIN ((legislation_level_type
     JOIN environmental_monitoring_network ON ((legislation_level_type.lgl_id = environmental_monitoring_network.organization_level_id)))
     LEFT JOIN (monitoring_object
     JOIN monitoring_feature ON ((monitoring_object.mno_id = monitoring_feature.mnf_id))) ON ((environmental_monitoring_network.emn_id = monitoring_feature.mnf_id))) ON ((wkr_version.wkrv_id = monitoring_object.wkrv_id)))
  ORDER BY monitoring_object.inspire_id_local_id, monitoring_object.wkrv_id DESC;


--
-- Name: vw_meetpunten_g; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_meetpunten_g AS
 SELECT monitoring_object.mno_id AS db_id,
    monitoring_object.inspire_id_local_id AS identificatie,
    namespace_type.code AS waterbeheerder_code,
    namespace_type.descrption AS waterbeheerder_omschrijving,
    monitoring_object.wkrv_id AS wkr_versie,
    wkr_version.begin_lifespan_version AS wkr_beginversie,
    wkr_version.end_lifespan_version AS wkr_eindversie,
    monitoring_object.name AS omschrijving,
    monitoring_object.additional_description AS toelichting,
    monitoring_object.geometry,
    environmental_monitoring_facility.representative_point AS geometriepunt,
    monitoring_feature.pos_accuracy_horizontal_value AS geometrieprecisie_waarde,
    eenheid_type.code AS eenheid_code,
    eenheid_type.omschrijving AS eenheid_omschrijving,
    measurement_regime_type.code AS meetregime_code,
    measurement_regime_type.description AS meetregime_omschrijving,
    environmental_monitoring_facility.is_mobile AS ismobielelokatie,
    result_acquisition_source_type.code AS inwinbron_code,
    result_acquisition_source_type.description AS inwinbron_omschrijving,
    specialised_emf_type.code AS specifiektype_code,
    specialised_emf_type.description AS specifiektype_omschrijving,
    subsite_type.code AS krwlocatietype_code,
    subsite_type.description AS krwlocatietype_omschrijving,
    monitoring_station.number_of_points_in_subsite AS aantalpunteninkrwlokatie,
    env_mon_facil_in_mngmt_reg_or_restr_zone.mrz_id AS ligtinwaterlichaam_id,
    namespace_type_1.code AS ligtinwaterlichaam_code,
    hydro_object.inspire_id_local_id AS ligtinwaterlichaam_waterbeheerder
   FROM ((((((wkr_version
     RIGHT JOIN (namespace_type
     RIGHT JOIN (monitoring_object
     RIGHT JOIN (((hydro_object
     JOIN env_mon_facil_in_mngmt_reg_or_restr_zone ON ((hydro_object.hdo_id = env_mon_facil_in_mngmt_reg_or_restr_zone.mrz_id)))
     JOIN namespace_type namespace_type_1 ON ((hydro_object.inspire_id_namespace = namespace_type_1.ns_id)))
     RIGHT JOIN (environmental_monitoring_facility
     JOIN monitoring_feature ON ((environmental_monitoring_facility.emf_id = monitoring_feature.mnf_id))) ON ((env_mon_facil_in_mngmt_reg_or_restr_zone.emf_id = environmental_monitoring_facility.emf_id))) ON ((monitoring_object.mno_id = monitoring_feature.mnf_id))) ON ((namespace_type.ns_id = monitoring_object.inspire_id_namespace_id))) ON ((wkr_version.wkrv_id = monitoring_object.wkrv_id)))
     LEFT JOIN eenheid_type ON ((monitoring_feature.pos_accuracy_horizontal_uom_id = eenheid_type.eenh_id)))
     LEFT JOIN specialised_emf_type ON ((environmental_monitoring_facility.speciallised_emf_type_id = specialised_emf_type.semf_id)))
     LEFT JOIN result_acquisition_source_type ON ((environmental_monitoring_facility.result_acquisition_source_id = result_acquisition_source_type.ras_id)))
     LEFT JOIN measurement_regime_type ON ((environmental_monitoring_facility.measurement_regime_id = measurement_regime_type.mmr_id)))
     LEFT JOIN (subsite_type
     RIGHT JOIN monitoring_station ON ((subsite_type.sst_id = monitoring_station.subsite_type_id))) ON ((environmental_monitoring_facility.emf_id = monitoring_station.mts_id)))
  ORDER BY monitoring_object.inspire_id_local_id, monitoring_object.wkrv_id DESC;


--
-- Name: vw_meetnet_meetpunt_g; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_meetnet_meetpunt_g AS
 SELECT ((vw_meetnetten.db_id)::text || (vw_meetpunten_g.db_id)::text) AS uniquekey,
    vw_meetnetten.db_id AS "Meetnet_DB_ID",
    vw_meetnetten.identificatie AS "Meetnet_Identificatie",
    vw_meetnetten.omschrijving AS "Meetnet_Omschrijving",
    vw_meetnetten."Toelichting:" AS "Meetnet_Toelichting",
    vw_meetpunten_g.db_id AS "Meetpunt_DB_ID",
    vw_meetpunten_g.identificatie AS "Meetpunt_Identificatie",
    vw_meetpunten_g.waterbeheerder_code AS "Meetpunt_Waterbeheerdercode",
    vw_meetpunten_g.waterbeheerder_omschrijving AS "Meetpunt_Omschrijving",
    vw_meetpunten_g.wkr_versie,
    vw_meetpunten_g.omschrijving,
    vw_meetpunten_g.toelichting,
    vw_meetpunten_g.geometry,
    vw_meetpunten_g.geometriepunt,
    vw_meetpunten_g.geometrieprecisie_waarde,
    vw_meetpunten_g.eenheid_code,
    vw_meetpunten_g.eenheid_omschrijving,
    vw_meetpunten_g.ismobielelokatie,
    vw_meetpunten_g.meetregime_omschrijving,
    vw_meetpunten_g.meetregime_code,
    vw_meetpunten_g.inwinbron_code,
    vw_meetpunten_g.inwinbron_omschrijving,
    vw_meetpunten_g.specifiektype_code,
    vw_meetpunten_g.specifiektype_omschrijving,
    vw_meetpunten_g.krwlocatietype_code,
    vw_meetpunten_g.krwlocatietype_omschrijving,
    vw_meetpunten_g.aantalpunteninkrwlokatie,
    vw_meetpunten_g.ligtinwaterlichaam_id,
    vw_meetpunten_g.ligtinwaterlichaam_code,
    vw_meetpunten_g.ligtinwaterlichaam_waterbeheerder
   FROM ((network_facility
     LEFT JOIN vw_meetnetten ON ((vw_meetnetten.db_id = network_facility.emn_id)))
     LEFT JOIN vw_meetpunten_g ON ((vw_meetpunten_g.db_id = network_facility.emf_id)))
  ORDER BY vw_meetnetten.identificatie;



--
-- Name: vw_meetpunt_meetnet_csv; Type: VIEW; Schema: aquo_formaten; Owner: -
--

CREATE VIEW vw_meetpunt_meetnet_csv AS
 SELECT DISTINCT ON (mpc."Waterbeheerder.code", mpc."Meetpunt.identificatie") mpc.meetpunt_identificatie_db_id,
    mpc."Meetpunt.identificatie",
    mpc."Meetpunt.omschrijving",
    mpc."GeometriePunt.X_RD",
    mpc."GeometriePunt.Y_RD",
    mpc."Waterbeheerder.code",
    mpc."Waterbeheerder.omschrijving",
    mmp."Meetnet_Identificatie" AS "Meetnet.identificatie",
    mmp."Meetnet_Omschrijving" AS "Meetnet.omschrijving",
    mmp.wkr_versie AS "Versie"
   FROM (vw_umaquo_meetpunten_csv mpc
     JOIN public.vw_meetnet_meetpunt_g mmp ON (((mmp."Meetpunt_Identificatie")::text = (mpc."Meetpunt.identificatie")::text)))
  ORDER BY mpc."Waterbeheerder.code", mpc."Meetpunt.identificatie", mpc.meetpunt_identificatie_db_id DESC;



--
-- Name: bepaling_grens_type; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE bepaling_grens_type (
    bpg_id integer NOT NULL,
    code character varying(50) NOT NULL,
    omschrijving text NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying
);


--
-- Name: classification_group_type; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE classification_group_type (
    clsg_id integer NOT NULL,
    code character varying(50) NOT NULL,
    description text,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying
);


--
-- Name: competent_authority; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE competent_authority (
    ore_id integer NOT NULL,
    namespace_id integer NOT NULL,
    legal_status text NOT NULL,
    legal_status_reference text NOT NULL,
    legal_status_summary text,
    url character varying(255) NOT NULL,
    last_change_time timestamp without time zone NOT NULL,
    name_uk character varying(255)
);


--
-- Name: competentauthority_namespacetype; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE competentauthority_namespacetype (
    ca_ns_id integer NOT NULL,
    ns_id integer NOT NULL,
    ore_id integer NOT NULL,
    last_change_time timestamp without time zone NOT NULL
);


--
-- Name: eu_monitoring_goal_type; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE eu_monitoring_goal_type (
    eumg_id integer NOT NULL,
    code character varying(50) NOT NULL,
    description character varying(50),
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying
);


--
-- Name: hdo_names; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE hdo_names (
    hdo_id integer NOT NULL,
    name character varying(90) NOT NULL,
    language character varying(12) DEFAULT 'dut'::text NOT NULL,
    last_change_time timestamp without time zone NOT NULL
);


--
-- Name: management_regulation_or_restriction_zone; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE management_regulation_or_restriction_zone (
    mrz_id integer NOT NULL,
    mrz_type character varying(50) NOT NULL,
    geometry geometry(Geometry,28992),
    environmental_domain_id integer NOT NULL,
    mrz_collection_id integer NOT NULL,
    specialised_zone_type_id integer NOT NULL,
    begin_designation timestamp without time zone NOT NULL,
    end_designation timestamp without time zone,
    indeterminate_end_position character varying(50),
    related_mrz_id integer,
    last_change_time timestamp without time zone NOT NULL,
    geometry_etrs89 geometry(Geometry,4258)
);


--
-- Name: COLUMN management_regulation_or_restriction_zone.mrz_type; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN management_regulation_or_restriction_zone.mrz_type IS 'mrz_type bepaalt het subtype: SurfaceWaterBody, GroundWaterBody, ProtectedArea, etc';


--
-- Name: organisation_category_type; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE organisation_category_type (
    ocat_id integer NOT NULL,
    code character varying(12) NOT NULL,
    omschrijving character varying(90) NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying,
    id integer
);


--
-- Name: organisatorische_eenheid; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE organisatorische_eenheid (
    ore_id integer NOT NULL,
    organisatie_identificatie character varying(12) NOT NULL,
    datum_ontstaan date NOT NULL,
    datum_opheffing date,
    naam character varying(90) NOT NULL,
    naam_verkort character varying(50),
    omschrijving text,
    toelichting text,
    email_adres character varying(255),
    faxnummer character varying(13),
    telefoonnummer character varying(13),
    organisation_category_id integer NOT NULL,
    last_change_time timestamp without time zone NOT NULL,
    adres character varying(50),
    postcode character varying(7),
    plaatsnaam character varying(50)
);


--
-- Name: river_basin_disrict; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE river_basin_disrict (
    rbd_id integer NOT NULL,
    area_value double precision NOT NULL,
    area_uom_id integer NOT NULL,
    international_rbd integer,
    part_of_district integer,
    last_change_time timestamp without time zone NOT NULL,
    area_ex_cw_value double precision
);


--
-- Name: COLUMN river_basin_disrict.part_of_district; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN river_basin_disrict.part_of_district IS 'pointer from subdistrict to district';


--
-- Name: sw_testing_conclusion; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE sw_testing_conclusion (
    stcc_id integer NOT NULL,
    sys_id integer NOT NULL,
    last_change_time timestamp without time zone NOT NULL,
    eumg_id integer DEFAULT 3 NOT NULL
);


--
-- Name: swb_yearly_status; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE swb_yearly_status (
    sys_id integer NOT NULL,
    swb_id integer NOT NULL,
    year_of_status integer NOT NULL,
    comment_on_chemical_status text,
    comment_on_ecological_status text,
    motivation_4no_chemical_status text,
    motivation_4no_ecological_status text,
    last_change_time timestamp without time zone NOT NULL,
    additional_purification boolean DEFAULT false NOT NULL,
    exceeds_drinkingwater boolean DEFAULT false NOT NULL
);


--
-- Name: vw_competent_authority; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_competent_authority AS
 SELECT organisatorische_eenheid.ore_id AS "DB_ID",
    organisatorische_eenheid.organisatie_identificatie AS "Identificatie",
    organisatorische_eenheid.naam AS "Omschrijving",
    organisatorische_eenheid.naam_verkort AS "Omschrijving_verkort",
    organisatorische_eenheid.omschrijving AS "Toelichting",
    organisatorische_eenheid.toelichting AS "Opmerking",
    organisatorische_eenheid.adres AS "Adres",
    organisatorische_eenheid.postcode AS "Postcode",
    organisatorische_eenheid.plaatsnaam AS "Plaats",
    organisatorische_eenheid.telefoonnummer AS "Telefoon",
    organisatorische_eenheid.faxnummer AS "Fax",
    organisatorische_eenheid.email_adres AS "Email",
    competent_authority.url AS "Website",
    organisatorische_eenheid.datum_ontstaan AS "Ontstaan_begindatum",
    organisatorische_eenheid.datum_opheffing AS "Ontstaan_einddatum",
    competent_authority.legal_status AS "Wettelijke_status",
    competent_authority.legal_status_summary AS "Wettelijke_status_samenvatting",
    competent_authority.legal_status_reference AS "Wettelijke_status_referentie",
    organisation_category_type.code AS "Organisatietype_Code",
    organisation_category_type.omschrijving AS "Organisatietype_Omschrijving",
    competent_authority.namespace_id AS waterbeheerder_db_id
   FROM competent_authority,
    organisatorische_eenheid,
    organisation_category_type
  WHERE ((organisatorische_eenheid.ore_id = competent_authority.ore_id) AND (organisation_category_type.ocat_id = organisatorische_eenheid.organisation_category_id));


--
-- Name: vw_sg_en_dsg_districten; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_sg_en_dsg_districten AS
 SELECT hydro_object.hdo_id AS "DB_ID",
    hydro_object.inspire_id_local_id AS "Identificatie",
    hdo_names.name AS omschrijving,
    river_basin_disrict.area_value AS "Oppervlakte_NumeriekeWaarde",
    eenheid_type.code AS "Oppervlakte_Eenheid_Code",
    eenheid_type.omschrijving AS "Oppervlakte_Eenheid_Omschrijving",
    wkr_version.wkrv_id AS wkr_versie,
    wkr_version.begin_lifespan_version AS wkr_beginversie,
    wkr_version.end_lifespan_version AS wkr_eindversie,
    hydro_object.related_hdo_id AS "Gerelateerd_HydroObject"
   FROM (((((hydro_object
     LEFT JOIN hdo_names ON ((hdo_names.hdo_id = hydro_object.hdo_id)))
     JOIN management_regulation_or_restriction_zone ON ((hydro_object.hdo_id = management_regulation_or_restriction_zone.mrz_id)))
     JOIN river_basin_disrict ON ((hydro_object.hdo_id = river_basin_disrict.rbd_id)))
     JOIN eenheid_type ON ((eenheid_type.eenh_id = river_basin_disrict.area_uom_id)))
     JOIN wkr_version ON ((wkr_version.wkrv_id = hydro_object.wkrv_id)));


--
-- Name: vw_waargenomen_eigenschap; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_waargenomen_eigenschap AS
 SELECT observed_property_type.opt_id AS "DB_ID",
    typering_type.code AS "Tpering_Code",
    typering_type.omschrijving AS "Typering_Omschrijving",
    grootheid_type.code AS "Grootheid_Code",
    grootheid_type.omschrijving AS "Grootheid_Omschrijving",
    chemische_stof_type.code AS "ChemischeStof_code",
    chemische_stof_type.naam AS "ChemischeStof_Omschrijving",
    chemische_stof_type.cas_nr AS "ChemischeStof_CASnr",
    classification_group_type.code AS "ClassificatieType_Code",
    classification_group_type.description AS "ClassificatieType_Omschrijving",
    object_type.omschrijving AS "Object_Omschrijving",
    object_type.code AS "Object_Code",
    taxon_type.code AS "Taxon_Naam",
    taxon_type.auteur AS "Taxon_Auteur",
    hoedanigheid_type.code AS "Hoedanigheid_Code",
    hoedanigheid_type.omschrijving AS "Hoedanigheid_Omschrijving",
    observed_property_type.observed_property_type_class AS "Parameter_Type",
    observed_property_type.result_type AS "Parameter_Resultaat",
    observed_property_type.remarks AS "Opmerkingen"
   FROM (((((((observed_property_type
     LEFT JOIN (object_type
     JOIN object_group_element ON ((object_type.obj_id = object_group_element.obj_id))) ON ((object_group_element.ojg_id = observed_property_type.object_group_id)))
     LEFT JOIN hoedanigheid_type ON ((hoedanigheid_type.hdh_id = observed_property_type.hoedanigheid_id)))
     LEFT JOIN grootheid_type ON ((grootheid_type.grh_id = observed_property_type.quantity_id)))
     LEFT JOIN (taxon_type
     JOIN taxa_group_element ON ((taxon_type.txn_id = taxa_group_element.txn_id))) ON ((taxa_group_element.txg_id = observed_property_type.taxa_group_id)))
     LEFT JOIN (chemische_stof_type
     JOIN substance_group_element ON ((chemische_stof_type.chs_id = substance_group_element.chs_id))) ON ((substance_group_element.ssge_id = observed_property_type.substance_group_id)))
     LEFT JOIN typering_type ON ((typering_type.typ_id = observed_property_type.indicator_id)))
     LEFT JOIN classification_group_type ON ((classification_group_type.clsg_id = observed_property_type.classification_group_id)))
  ORDER BY typering_type.code, grootheid_type.code, chemische_stof_type.code, object_type.code, taxon_type.code, hoedanigheid_type.code;


--
-- Name: vw_toetsresultaat_owm; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_toetsresultaat_owm AS
 SELECT observation.obs_id AS "DB_ID",
    hydro_object.hdo_id AS "Waterlichaam_DB_ID",
    hydro_object.inspire_id_local_id AS "Waterlichaam_Identificatie",
    hydro_object.wkrv_id AS "Waterlichaam_Versie",
    swb_yearly_status.year_of_status AS "Rapportagejaar",
    vw_waargenomen_eigenschap."DB_ID" AS "Waargenomen_Eigenschap_DB_ID",
    vw_waargenomen_eigenschap."Tpering_Code",
    vw_waargenomen_eigenschap."Typering_Omschrijving",
    vw_waargenomen_eigenschap."Grootheid_Code",
    vw_waargenomen_eigenschap."Grootheid_Omschrijving",
    vw_waargenomen_eigenschap."ChemischeStof_code",
    vw_waargenomen_eigenschap."ChemischeStof_Omschrijving",
    vw_waargenomen_eigenschap."ChemischeStof_CASnr",
    waarde_bewerkings_methode_type.code AS "Waardebewerkingsmethode_Code",
    waarde_bewerkings_methode_type.omschrijving AS "Waardebewerkingsmethode_Omschrijving",
    observation.result_time AS "Begintijd_Toetsresultaat",
    observation.phenomenon_time_end AS "Eindtijd_Toetsreeks",
    observation.phenomenon_time_begin AS "Begintijd_Toetsreeks",
    observation.numeric_value AS "Numerieke_waarde",
    observation.numeric_value_significance AS "Numerieke_waarde_Significantie",
    bepaling_grens_type.omschrijving AS "Bepalingsgrens",
    eenheid_type.code AS "Eenheid_Code",
    eenheid_type.omschrijving AS "Eenheid_Omschrijving",
    vw_waargenomen_eigenschap."Hoedanigheid_Code",
    vw_waargenomen_eigenschap."Hoedanigheid_Omschrijving",
    kwaliteitsoordeel_type.code AS "KwaliteitsOordeel_Code",
    kwaliteitsoordeel_type.description AS "KwaliteitsOordeel_Omschrijving",
    eu_monitoring_goal_type.eumg_id AS "Oordeel_Type_DB_ID",
    eu_monitoring_goal_type.code AS "Oordeel_Type_Code",
    eu_monitoring_goal_type.description AS "Oordeel_Type_Omschrijving",
    waarde_bepalings_methode_type.code AS "Waardebepalingsmethode_Code",
    waarde_bepalings_methode_type.omschrijving AS "Waardebepalingsmethode_Omschrijving"
   FROM (((((((((((observation
     LEFT JOIN vw_waargenomen_eigenschap ON ((vw_waargenomen_eigenschap."DB_ID" = observation.observed_property_type)))
     LEFT JOIN waarde_bewerkings_methode_type ON ((waarde_bewerkings_methode_type.wbm_id = observation.value_processing_method_id)))
     LEFT JOIN eenheid_type ON ((eenheid_type.eenh_id = observation.numeric_value_uom_id)))
     LEFT JOIN bepaling_grens_type ON ((bepaling_grens_type.bpg_id = observation.limit_symbol_id)))
     LEFT JOIN kwaliteitsoordeel_type ON ((kwaliteitsoordeel_type.kwod_id = observation.quality_indicator_id)))
     LEFT JOIN observation_process ON ((observation_process.obp_id = observation.result_nature_id)))
     LEFT JOIN waarde_bepalings_methode_type ON ((waarde_bepalings_methode_type.wbpm_id = observation_process.wbpm_id)))
     LEFT JOIN sw_testing_conclusion ON ((sw_testing_conclusion.stcc_id = observation.obs_id)))
     LEFT JOIN swb_yearly_status ON ((swb_yearly_status.sys_id = sw_testing_conclusion.sys_id)))
     LEFT JOIN hydro_object ON ((hydro_object.hdo_id = swb_yearly_status.swb_id)))
     LEFT JOIN eu_monitoring_goal_type ON ((eu_monitoring_goal_type.eumg_id = sw_testing_conclusion.eumg_id)))
  WHERE (swb_yearly_status.year_of_status IS NOT NULL)
  ORDER BY hydro_object.hdo_id, vw_waargenomen_eigenschap."Tpering_Code", vw_waargenomen_eigenschap."Grootheid_Code", vw_waargenomen_eigenschap."ChemischeStof_code", swb_yearly_status.year_of_status;


--
