# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1413450909 -7200
# Node ID 6399ebc6fce9d137e6c5a12e502967fb33ef5e88
# Parent  ffbeffe2d26698a27d7e4cf12cf0f299c4c2d564
Reduce code duplication and repair filter for sediment load-recommendations.

diff -r ffbeffe2d266 -r 6399ebc6fce9 artifacts/doc/conf/meta-data.xml
--- a/artifacts/doc/conf/meta-data.xml	Wed Oct 15 19:48:20 2014 +0200
+++ b/artifacts/doc/conf/meta-data.xml	Thu Oct 16 11:15:09 2014 +0200
@@ -1654,43 +1654,11 @@
     </dc:macro>
 
     <dc:macro name="sedimentloads">
-      <dc:comment>
-        This is the macro for static datacage "Sedimentloads".
-        Or "Frachten".
-      </dc:comment>
       <sedimentloads>
         <dc:call-macro name="annotations_sediment_load_ls"/>
         <measurement_stations>
           <dc:context>
-            <dc:statement>
-              SELECT DISTINCT
-                  sl.id          AS slid,
-                  slk.kind       AS kind,
-                  ti.start_time  AS startyear,
-                  ti.stop_time   AS endyear,
-                  sq.start_time  AS sqstart,
-                  sq.stop_time   AS sqstop,
-                  gf.name        AS fraction,
-                  sl.description  || '&lt;BR&gt;' ||
-                  'Fraktion: ' || gf.lower || ' bis ' || gf.upper ||
-                  ' mm&lt;BR&gt;Einheit: t/a' AS info
-              FROM     sediment_load sl
-                  JOIN sediment_load_kinds slk ON slk.id = sl.kind
-                  JOIN sediment_load_values slv
-                      ON sl.id = slv.sediment_load_id
-                  JOIN measurement_station ms
-                      ON ms.id = slv.measurement_station_id
-                  JOIN ranges ra ON ms.range_id = ra.id
-                  JOIN rivers r ON ra.river_id = r.id
-                  JOIN time_intervals ti ON sl.time_interval_id = ti.id
-                  LEFT JOIN time_intervals sq ON sl.sq_time_interval_id = sq.id
-                  JOIN grain_fraction gf ON gf.id = sl.grain_fraction_id
-              WHERE   r.id = ${river_id}
-                  AND CASE WHEN r.km_up = 1 AND ra.b IS NOT NULL
-                      THEN ra.b ELSE ra.a END BETWEEN ${fromkm} AND ${tokm}
-              ORDER BY sqstart DESC, sqstop DESC,
-                  fraction, startyear DESC, endyear DESC
-            </dc:statement>
+            <dc:call-macro name="sedimentload_stations_statement"/>
             <dc:call-macro name="sedimentloads-filter">
               <dc:call-macro name="loads">
                 <dc:call-macro name="load"/>
@@ -1700,32 +1668,7 @@
         </measurement_stations>
         <sediment_load_ls>
           <dc:context>
-            <dc:statement>
-              SELECT DISTINCT
-                  sl.id          AS slid,
-                  slk.kind       AS kind,
-                  ti.start_time  AS startyear,
-                  ti.stop_time   AS endyear,
-                  sq.start_time  AS sqstart,
-                  sq.stop_time   AS sqstop,
-                  gf.name        AS fraction,
-                  sl.description  || '&lt;BR&gt;' ||
-                  'Fraktion: ' || gf.lower || ' bis ' || gf.upper ||
-                  ' mm&lt;BR&gt;Einheit: ' || u.name AS info
-              FROM     sediment_load_ls sl
-                  JOIN sediment_load_kinds slk ON slk.id = sl.kind
-                  JOIN rivers r ON sl.river_id = r.id
-                  JOIN sediment_load_ls_values slv
-                      ON sl.id = slv.sediment_load_ls_id
-                  JOIN time_intervals ti ON sl.time_interval_id = ti.id
-                  LEFT JOIN time_intervals sq ON sl.sq_time_interval_id = sq.id
-                  JOIN grain_fraction gf ON gf.id = sl.grain_fraction_id
-                  JOIN units u ON sl.unit_id = u.id
-              WHERE   r.id = ${river_id}
-                  AND slv.station BETWEEN ${fromkm} AND ${tokm}
-              ORDER BY sqstart DESC, sqstop DESC,
-                  fraction, startyear DESC, endyear DESC
-            </dc:statement>
+            <dc:call-macro name="sedimentload_ls_statement"/>
             <dc:call-macro name="sedimentloads-filter">
               <dc:call-macro name="loads">
                 <dc:call-macro name="load_ls"/>
@@ -1736,6 +1679,69 @@
       </sedimentloads>
     </dc:macro>
 
+    <dc:macro name="sedimentload_stations_statement">
+      <dc:statement>
+        SELECT DISTINCT
+            sl.id          AS slid,
+            slk.kind       AS kind,
+            ti.start_time  AS startyear,
+            ti.stop_time   AS endyear,
+            sq.start_time  AS sqstart,
+            sq.stop_time   AS sqstop,
+            sq.id          AS sqid,
+            gf.name        AS fraction,
+            sl.description  || '&lt;BR&gt;' ||
+                'Fraktion: ' || gf.lower || ' bis ' || gf.upper ||
+                ' mm&lt;BR&gt;Einheit: t/a' AS info
+        FROM     sediment_load sl
+            JOIN sediment_load_kinds slk ON slk.id = sl.kind
+            JOIN sediment_load_values slv
+                ON sl.id = slv.sediment_load_id
+            JOIN measurement_station ms
+                ON ms.id = slv.measurement_station_id
+            JOIN ranges ra ON ms.range_id = ra.id
+            JOIN rivers r ON ra.river_id = r.id
+            JOIN time_intervals ti ON sl.time_interval_id = ti.id
+            LEFT JOIN time_intervals sq ON sl.sq_time_interval_id = sq.id
+            JOIN grain_fraction gf ON gf.id = sl.grain_fraction_id
+        WHERE r.id = ${river_id}
+            AND CASE WHEN r.km_up = 1 AND ra.b IS NOT NULL
+            THEN ra.b ELSE ra.a END BETWEEN ${fromkm} AND ${tokm}
+            ORDER BY sqstart DESC, sqstop DESC,
+                fraction, startyear DESC, endyear DESC
+      </dc:statement>
+    </dc:macro>
+
+    <dc:macro name="sedimentload_ls_statement">
+      <dc:statement>
+        SELECT DISTINCT
+            sl.id          AS slid,
+            slk.kind       AS kind,
+            ti.start_time  AS startyear,
+            ti.stop_time   AS endyear,
+            sq.start_time  AS sqstart,
+            sq.stop_time   AS sqstop,
+            sq.id          AS sqid,
+            gf.name        AS fraction,
+            sl.description  || '&lt;BR&gt;' ||
+                'Fraktion: ' || gf.lower || ' bis ' || gf.upper ||
+                ' mm&lt;BR&gt;Einheit: ' || u.name AS info
+        FROM     sediment_load_ls sl
+            JOIN sediment_load_kinds slk ON slk.id = sl.kind
+            JOIN rivers r ON sl.river_id = r.id
+            JOIN sediment_load_ls_values slv
+                ON sl.id = slv.sediment_load_ls_id
+            JOIN time_intervals ti ON sl.time_interval_id = ti.id
+            LEFT JOIN time_intervals sq ON sl.sq_time_interval_id = sq.id
+            JOIN grain_fraction gf ON gf.id = sl.grain_fraction_id
+            JOIN units u ON sl.unit_id = u.id
+        WHERE   r.id = ${river_id}
+            AND slv.station BETWEEN ${fromkm} AND ${tokm}
+        ORDER BY sqstart DESC, sqstop DESC,
+            fraction, startyear DESC, endyear DESC
+      </dc:statement>
+    </dc:macro>
+
     <!-- annotations -->
     <dc:macro name="annotations">
       <annotation factory="annotations" ids="{$river_id}" target_out="{$out}"/>
@@ -3037,51 +3043,31 @@
       </dc:if>
     </dc:macro>
 
-
+    <!-- sediment loads -->
     <dc:macro name="sedimentload_off_epoch_filter">
-        <dc:if test="dc:has-result()">
-          <dc:if test="string-length(dc:get('epochs')) &gt; 0">
-            <dc:variable name="start" type="number"
-                         expr="dc:min-number(dc:find-all('\d{4}', $epochs))"/>
-            <dc:variable name="end" type="number"
-                         expr="dc:max-number(dc:find-all('\d{4}', $epochs))"/>
-          </dc:if>
-          <dc:filter expr="not(number($end) &lt;
-                               number(dc:date-format('yyyy', $startyear))
-                             or number($start) &gt;
-                               number(dc:date-format('yyyy', $endyear)))">
-            <dc:macro-body/>
-          </dc:filter>
+      <dc:if test="dc:has-result()">
+        <dc:variable name="sq_time_id" type="number"
+                     expr="number($sq_ti_id)"/>
+        <dc:if test="string-length(dc:get('epochs')) &gt; 0">
+          <dc:variable name="start" type="number"
+                       expr="dc:min-number(dc:find-all('\d{4}', $epochs))"/>
+          <dc:variable name="end" type="number"
+                       expr="dc:max-number(dc:find-all('\d{4}', $epochs))"/>
         </dc:if>
+        <dc:filter expr="$kind = 'official' and
+                         $sqid = $sq_time_id and
+                         not(number($end) &lt;
+                             number(dc:date-format('yyyy', $startyear))
+                           or number($start) &gt;
+                             number(dc:date-format('yyyy', $endyear)))">
+          <dc:macro-body/>
+        </dc:filter>
+      </dc:if>
     </dc:macro>
 
     <dc:macro name="sedimentload_off_epochs">
-      <dc:variable name="sq_time_id" type="number" expr="number($sq_ti_id)"/>
       <dc:context>
-        <dc:comment> data at measurement stations </dc:comment>
-        <dc:statement>
-          SELECT DISTINCT
-              sl.id               AS slid,
-              slk.kind            AS kind,
-              ti.start_time       AS startyear,
-              ti.stop_time        AS endyear,
-              gf.name             AS fraction
-          FROM     sediment_load sl
-              JOIN sediment_load_kinds slk ON slk.id = sl.kind
-              JOIN sediment_load_values slv
-                  ON sl.id = slv.sediment_load_id
-              JOIN measurement_station ms
-                  ON ms.id = slv.measurement_station_id
-              JOIN ranges ra ON ms.range_id = ra.id
-              JOIN rivers r ON ra.river_id = r.id
-              JOIN time_intervals ti ON sl.time_interval_id = ti.id
-              JOIN grain_fraction gf ON gf.id = sl.grain_fraction_id
-          WHERE r.id = ${river_id}
-              AND sq_time_interval_id = ${sq_time_id}
-              AND CASE WHEN r.km_up = 1 AND ra.b IS NOT NULL
-                  THEN ra.b ELSE ra.a END BETWEEN ${fromkm} AND ${tokm}
-              AND slk.kind = 'official'
-        </dc:statement>
+        <dc:call-macro name="sedimentload_stations_statement"/>
         <dc:call-macro name="sedimentload_off_epoch_filter">
           <dc:call-macro name="loads">
             <dc:call-macro name="load"/>
@@ -3089,27 +3075,7 @@
         </dc:call-macro>
       </dc:context>
       <dc:context>
-        <dc:comment> longitudinal sections </dc:comment>
-        <dc:statement>
-          SELECT DISTINCT
-              sl.id               AS slid,
-              slk.kind            AS kind,
-              ti.start_time       AS startyear,
-              ti.stop_time        AS endyear,
-              sq_time_interval_id AS sqid,
-              gf.name             AS fraction
-          FROM     sediment_load_ls sl
-              JOIN sediment_load_kinds slk ON slk.id = sl.kind
-              JOIN rivers r ON sl.river_id = r.id
-              JOIN sediment_load_ls_values slv
-                  ON sl.id = slv.sediment_load_ls_id
-              JOIN time_intervals ti ON sl.time_interval_id = ti.id
-              JOIN grain_fraction gf ON gf.id = sl.grain_fraction_id
-          WHERE   r.id = ${river_id}
-              AND sq_time_interval_id = ${sq_time_id}
-              AND slv.station BETWEEN ${fromkm} AND ${tokm}
-              AND slk.kind = 'official'
-        </dc:statement>
+        <dc:call-macro name="sedimentload_ls_statement"/>
         <dc:call-macro name="sedimentload_off_epoch_filter">
           <dc:call-macro name="loads">
             <dc:call-macro name="load_ls"/>
@@ -3118,6 +3084,7 @@
       </dc:context>
     </dc:macro>
 
+
     <!-- Common stuff -->
 
     <dc:macro name="km-filtered-wsts">