Mercurial > dive4elements > river
changeset 8416:6399ebc6fce9
Reduce code duplication and repair filter for sediment load-recommendations.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Thu, 16 Oct 2014 11:15:09 +0200 |
| parents | ffbeffe2d266 |
| children | 71144e25a6c9 |
| files | artifacts/doc/conf/meta-data.xml |
| diffstat | 1 files changed, 86 insertions(+), 119 deletions(-) [+] |
line wrap: on
line diff
--- 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 || '<BR>' || - 'Fraktion: ' || gf.lower || ' bis ' || gf.upper || - ' mm<BR>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 || '<BR>' || - 'Fraktion: ' || gf.lower || ' bis ' || gf.upper || - ' mm<BR>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 || '<BR>' || + 'Fraktion: ' || gf.lower || ' bis ' || gf.upper || + ' mm<BR>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 || '<BR>' || + 'Fraktion: ' || gf.lower || ' bis ' || gf.upper || + ' mm<BR>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')) > 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) < - number(dc:date-format('yyyy', $startyear)) - or number($start) > - 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')) > 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) < + number(dc:date-format('yyyy', $startyear)) + or number($start) > + 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">