-- Copyright 2010, Intevation GmbH, Germany, <info@intevation.de>
--
-- This file is part of FGSK.
--
-- FGSK is free software: you can redistribute it and/or modify it under
-- the terms of the GNU General Public License as published by the
-- Free Software Foundation, either version 2 of the License, or (at your
-- option) any later version.
--
-- FGSK is distributed in the hope that it will be useful, but WITHOUT
-- ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
-- FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public
-- License for more details.
--
-- You should have received a copy of the GNU General Public
-- License along with FGSK. If not, see <http://www.gnu.org/licenses/>.
--
-- FGSK has been developed on behalf of the
-- Landesamt fuer Umwelt und Geologie Mecklenburg Vorpommern (LUNG-MV)
-- by Intevation GmbH. 
-- biota Institut fuer oekologische Forschung und Planung GmbH has provided
-- the functional background.
--
-- Authors:
-- Tim Englich <tim.englich@intevation.de>
-- Ingo Weinzierl <ingo.weinzierl@intevation.de>

CREATE OR REPLACE VIEW v_bewertung_gesamt AS 
SELECT ka.id, 
       ROUND(ka.punktzahl_gesamt,2) AS pktges,
       ka.punktzahl_gesamt AS pktgesorig,
       ROUND(ka.punktzahl_sohle,2) AS pktsohle,
       ROUND(ka.punktzahl_ufer,2) AS pktufer,
       ROUND(ka.punktzahl_land,2) AS pktland,
       ka.gewaesser_kennzahl AS gewkennz,
       ka.gewaesser_abschnitt AS gewabsch,
       gw.gewaesser_name AS gewname,
       ka.the_geom,
       le.summe_punktzahl AS lauf_pkt,
       le.anzahl_kriterien AS lauf_anz,
       lp.summe_punktzahl AS laeng_pkt,
       lp.anzahl_kriterien AS laeng_anz,
       sst.summe_punktzahl AS sohls_pkt,
       sst.anzahl_kriterien AS sohls_anz,
       qp.summe_punktzahl AS quer_pkt,
       qp.anzahl_kriterien AS quer_anz,
       ust.summe_punktzahl AS ufers_pkt,
       ust.anzahl_kriterien AS ufers_anz,
       gu.summe_punktzahl AS gewae_pkt,
       gu.anzahl_kriterien AS gewae_anz
FROM kartierabschnitt ka
     LEFT OUTER JOIN laufentwicklung le ON (ka.id = le.kartierabschnitt_id)
     LEFT OUTER JOIN laengsprofil lp ON (ka.id = lp.kartierabschnitt_id)
     LEFT OUTER JOIN sohlenstruktur sst ON (ka.id = sst.kartierabschnitt_id)
     LEFT OUTER JOIN querprofil qp ON (ka.id = qp.kartierabschnitt_id)
     LEFT OUTER JOIN uferstruktur ust ON (ka.id = ust.kartierabschnitt_id)
     LEFT OUTER JOIN gewaesserumfeld gu ON (ka.id = gu.kartierabschnitt_id)
     LEFT OUTER JOIN gewaesser gw ON (ka.gewaesser_kennzahl = gw.gewaesser_kennzahl);
