Mercurial > gemma
view report-templates/data-quality-report.yaml @ 5653:c19d38bfdfcf
Bumped dev version.
author | Sascha Wilde <wilde@sha-bang.de> |
---|---|
date | Tue, 08 Aug 2023 15:06:18 +0200 |
parents | e8aa69040bec |
children |
line wrap: on
line source
actions: # One sheet per CC - type: select statement: > SELECT cc FROM (VALUES ('AT'), ('SK'), ('HU'), ('HR'), ('RS'), ('BG'), ('RO') ) AS t (cc); actions: - type: sheet source: CCTmpl destination: "{{ cc }}" vars: [last_row, column_number, offset] actions: # Header: - type: copy location: [A1, A5] # BN names - type: select statement: > SELECT DISTINCT bottleneck_id, concat(objnam, ' (', bottleneck_id, ')') AS bnnam, hm FROM waterway.dqr_bottleneck_stats WHERE cc = {{ cc }} ORDER BY hm, bottleneck_id; actions: - type: assign name: last_row expr: (row_number ?? 0) + 6 - type: copy location: [A6] destination: A{{ last_row }} - type: copy location: [A7] destination: A{{ (last_row ?? 6) + 1 }} # Gen Months - type: select statement: > SELECT to_char(d, 'Month YYYY') AS month, d::date FROM generate_series( date_trunc('month', now() - interval '1 day' )::date, '2019-10-01'::date, - '1 month'::interval ) d; actions: - type: assign name: column_number expr: column2num("B") + (row_number ?? 0) * 2 - type: copy location: [B4, C5] destination: "{{ coord2cell(column_number, 4) }}" # BN SR-Count - type: select statement: > 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; actions: - type: assign name: last_row expr: (row_number ?? 0) + 6 - type: copy location: [B6, C6] destination: "{{ coord2cell(column_number, last_row) }}" - type: copy location: [B7, C7] destination: "{{ coord2cell(column_number, (last_row ?? 0) + 1) }}" - type: assign name: offset expr: (last_row ?? 0) + 3 #-------------------------------------------------------------------------- # GAUGES # Header: - type: copy location: [A9] destination: A{{ offset }} # Gauges names - type: select statement: > SELECT DISTINCT objname AS gnam, hm FROM waterway.dqr_gauge_stats WHERE cc = {{ cc }} ORDER BY hm, objname; actions: - type: assign name: last_row expr: (row_number ?? 0) + offset + 1 - type: copy location: [A10] destination: A{{ last_row }} - type: copy location: [A11] destination: A{{ (last_row ?? 10) + 1 }} # Gen Months - type: select statement: > SELECT to_char(d, 'Month YYYY') AS month, d::date FROM generate_series( date_trunc('month', now() - interval '1 day' )::date, '2019-10-01'::date, - '1 month'::interval ) d; actions: - type: assign name: column_number expr: column2num("B") + (row_number ?? 0) - type: copy location: [B9] destination: "{{ coord2cell(column_number, offset) }}" # BN SR-Count - type: select statement: > SELECT objname, daynodata FROM waterway.dqr_gauge_stats WHERE cc = {{ cc }} AND month = {{ d }} ORDER BY hm, objname; actions: - type: assign name: last_row expr: (row_number ?? 0) + offset + 1 - type: copy location: [B10] destination: "{{ coord2cell(column_number, last_row) }}" - type: copy location: [B11] destination: "{{ coord2cell(column_number, (last_row ?? 10) + 1) }}"