# HG changeset patch
# User Thomas Junk <thomas.junk@intevation.de>
# Date 1687188105 -7200
# Node ID 88fdf143daf1c995879aa4cf3c2d45b207440064
# Parent  6fe970d800ec9676c193f5624eb9882359a7d162
finalize report

diff -r 6fe970d800ec -r 88fdf143daf1 report-templates/data-quality-report.yaml
--- a/report-templates/data-quality-report.yaml	Mon Jun 19 12:46:57 2023 +0200
+++ b/report-templates/data-quality-report.yaml	Mon Jun 19 17:21:45 2023 +0200
@@ -17,9 +17,9 @@
           # BN names
           - type: select
             statement: >
-              SELECT DISTINCT bottleneck_id, objnam AS bnnam, hm
+              SELECT DISTINCT bottleneck_id, concat(bottleneck_id, ' (', objnam, ')') AS bnnam, hm
                 FROM waterway.dqr_bottleneck_stats
-                WHERE cc = {{ cc }} ORDER BY hm, bottleneck_id, objnam;
+                WHERE cc = {{ cc }} ORDER BY hm, bottleneck_id;
             actions:
               - type: assign
                 name: last_row
@@ -49,7 +49,7 @@
               # BN SR-Count
               - type: select
                 statement: >
-                  SELECT concat(bottleneck_id, ' ', objnam), srcnt, fwacnt
+                  SELECT concat(bottleneck_id, ' (', objnam, ')') AS objnam, srcnt, fwacnt
                     FROM waterway.dqr_bottleneck_stats
                     WHERE cc = {{ cc }} AND month = {{ d }}
                     ORDER BY hm, bottleneck_id;
diff -r 6fe970d800ec -r 88fdf143daf1 schema/reports.sql
--- a/schema/reports.sql	Mon Jun 19 12:46:57 2023 +0200
+++ b/schema/reports.sql	Mon Jun 19 17:21:45 2023 +0200
@@ -49,7 +49,7 @@
      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', '[)')
+               WHERE validity @> tstzrange(now() - interval '1 hour', now(), '[)')
                GROUP BY bottleneck_id,cc
                )
   SELECT bn.cc, bn.hm, d.ym AS month, bn.objnam, bn.bottleneck_id,
@@ -68,7 +68,7 @@
          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;;
+    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 6fe970d800ec -r 88fdf143daf1 schema/updates/1470/01.update_report.sql
--- a/schema/updates/1470/01.update_report.sql	Mon Jun 19 12:46:57 2023 +0200
+++ b/schema/updates/1470/01.update_report.sql	Mon Jun 19 17:21:45 2023 +0200
@@ -8,7 +8,7 @@
      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', '[)')
+               WHERE validity @> tstzrange(now() - interval '1 hour', now(), '[)')
                GROUP BY bottleneck_id,cc
                )
   SELECT bn.cc, bn.hm, d.ym AS month, bn.objnam, bn.bottleneck_id,