Mercurial > gemma
changeset 5643:b4ee50490592 DQR adjustments
Correct formatting DQR-Report
| author | Thomas Junk <thomas.junk@intevation.de> |
|---|---|
| date | Wed, 14 Jun 2023 10:08:25 +0200 |
| parents | 467f198815e7 |
| children | f432f578d7e7 |
| files | report-templates/data-quality-report.yaml |
| diffstat | 1 files changed, 119 insertions(+), 120 deletions(-) [+] |
line wrap: on
line diff
--- a/report-templates/data-quality-report.yaml Wed Jun 14 08:46:27 2023 +0200 +++ b/report-templates/data-quality-report.yaml Wed Jun 14 10:08:25 2023 +0200 @@ -1,123 +1,122 @@ 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] + # One sheet per CC + - type: select + statement: > + SELECT cc FROM + (VALUES ('AT'), ('SK'), ('HU'), ('HR'), ('RS'), ('BG'), ('RO') ) + AS t (cc); 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; + - type: sheet + source: CCTmpl + destination: "{{ cc }}" + vars: [last_row, column_number, offset] 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) }}' - + # 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) }}"