# HG changeset patch
# User Thomas Junk <thomas.junk@intevation.de>
# Date 1686725187 -7200
# Node ID 467f198815e7640d80ef8c65875a7af9ad4b9d5d
# Parent  c0425c948c14c8c7d9a450623a5bb0acd3dcacba# Parent  7f9600ab54616a31db571051732d92ee30d5a4b3
Merge with default

diff -r 7f9600ab5461 -r 467f198815e7 schema/reports.sql
--- a/schema/reports.sql	Wed Jun 14 08:45:11 2023 +0200
+++ b/schema/reports.sql	Wed Jun 14 08:46:27 2023 +0200
@@ -46,29 +46,29 @@
               FROM generate_series( '2019-10-01'::date,
                                     now() - interval '1 day',
                                     '1 month'::interval ) ym),
-     bn AS ( SELECT DISTINCT objnam, responsible_country AS cc,
+     bn AS ( SELECT DISTINCT bottleneck_id ,max(objnam) as objnam, responsible_country AS cc,
                     min ((lower(stretch)).hectometre) AS hm
-               FROM waterway.bottlenecks GROUP BY objnam,cc),
-     bid AS (SELECT objnam, array_agg(distinct(bottleneck_id)) AS ids
-               FROM waterway.bottlenecks GROUP BY objnam)
-  SELECT bn.cc, bn.hm, d.ym AS month, bid.objnam,
+               FROM waterway.bottlenecks
+               WHERE validity @> tstzrange(now() - interval '1 hour', now() + interval '1 hour', '[)')
+               GROUP BY bottleneck_id,cc
+               )
+  SELECT bn.cc, bn.hm, d.ym AS month, bn.objnam, bn.bottleneck_id,
          COALESCE(count(distinct(sr.date_info)),0) AS srcnt,
          COALESCE(count(distinct(efa.measure_date)),0) AS fwacnt
-    FROM bn, bid
+    FROM bn
     CROSS JOIN d
     LEFT OUTER JOIN waterway.sounding_results sr
-      ON ARRAY[sr.bottleneck_id] <@ bid.ids
+      ON sr.bottleneck_id = bn.bottleneck_id
          AND d.ym <= sr.date_info
          AND sr.date_info < (d.ym + interval '1 month')
     LEFT OUTER JOIN waterway.fairway_availability fa
-      ON ARRAY[fa.bottleneck_id] <@ bid.ids
+      ON fa.bottleneck_id = bn.bottleneck_id
     LEFT OUTER JOIN waterway.effective_fairway_availability efa
       ON fairway_availability_id = fa.id
          AND d.ym <= efa.measure_date
          AND efa.measure_date < (d.ym + interval '1 month')
          AND efa.measure_type = 'Measured'
-    WHERE bid.objnam = bn.objnam
-    GROUP BY bn.cc, bn.hm, d.ym, bid.objnam;
+    GROUP BY bn.cc, bn.hm, d.ym, bn.bottleneck_id, bn.objnam;;
 
 -- We need a wrapper procedure with owner rights for
 -- the refresh, as (from the PGSQL manual): "REFRESH MATERIALIZED VIEW
diff -r 7f9600ab5461 -r 467f198815e7 schema/updates/1470/01.update_report.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1470/01.update_report.sql	Wed Jun 14 08:46:27 2023 +0200
@@ -0,0 +1,30 @@
+DROP MATERIALIZED VIEW waterway.dqr_bottleneck_stats;
+
+CREATE MATERIALIZED VIEW waterway.dqr_bottleneck_stats AS
+WITH d AS ( SELECT ym::date
+              FROM generate_series( '2019-10-01'::date,
+                                    now() - interval '1 day',
+                                    '1 month'::interval ) ym),
+     bn AS ( SELECT DISTINCT bottleneck_id ,max(objnam) as objnam, responsible_country AS cc,
+                    min ((lower(stretch)).hectometre) AS hm
+               FROM waterway.bottlenecks
+               WHERE validity @> tstzrange(now() - interval '1 hour', now() + interval '1 hour', '[)')
+               GROUP BY bottleneck_id,cc
+               )
+  SELECT bn.cc, bn.hm, d.ym AS month, bn.objnam, bn.bottleneck_id,
+         COALESCE(count(distinct(sr.date_info)),0) AS srcnt,
+         COALESCE(count(distinct(efa.measure_date)),0) AS fwacnt
+    FROM bn
+    CROSS JOIN d
+    LEFT OUTER JOIN waterway.sounding_results sr
+      ON sr.bottleneck_id = bn.bottleneck_id
+         AND d.ym <= sr.date_info
+         AND sr.date_info < (d.ym + interval '1 month')
+    LEFT OUTER JOIN waterway.fairway_availability fa
+      ON fa.bottleneck_id = bn.bottleneck_id
+    LEFT OUTER JOIN waterway.effective_fairway_availability efa
+      ON fairway_availability_id = fa.id
+         AND d.ym <= efa.measure_date
+         AND efa.measure_date < (d.ym + interval '1 month')
+         AND efa.measure_type = 'Measured'
+    GROUP BY bn.cc, bn.hm, d.ym, bn.bottleneck_id, bn.objnam;
\ No newline at end of file
diff -r 7f9600ab5461 -r 467f198815e7 schema/version.sql
--- a/schema/version.sql	Wed Jun 14 08:45:11 2023 +0200
+++ b/schema/version.sql	Wed Jun 14 08:46:27 2023 +0200
@@ -1,1 +1,1 @@
-INSERT INTO gemma_schema_version(version) VALUES (1469);
+INSERT INTO gemma_schema_version(version) VALUES (1470);