Mercurial > gemma
view report-templates/data-quality-report.yaml @ 5602:781628cda531
Revert excel support to previous version.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Mon, 08 Aug 2022 15:29:07 +0200 |
parents | a726a92ea5c9 |
children | b4ee50490592 |
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 objnam AS bnnam, hm FROM waterway.dqr_bottleneck_stats WHERE cc = {{ cc }} ORDER BY hm, objnam; 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 objnam, srcnt, fwacnt FROM waterway.dqr_bottleneck_stats WHERE cc = {{ cc }} AND month = {{ d }} ORDER BY hm, objnam; 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) }}'