Mercurial > gemma
changeset 2826:f7ae108c2838
longterm waterlevels statistics: Generate a comment line with the interval of years.
| author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
|---|---|
| date | Wed, 27 Mar 2019 13:43:41 +0100 |
| parents | d6c90d2c8c8d |
| children | fb7cc59f5e12 |
| files | pkg/controllers/gauges.go |
| diffstat | 1 files changed, 65 insertions(+), 7 deletions(-) [+] |
line wrap: on
line diff
--- a/pkg/controllers/gauges.go Wed Mar 27 12:36:15 2019 +0100 +++ b/pkg/controllers/gauges.go Wed Mar 27 13:43:41 2019 +0100 @@ -25,6 +25,7 @@ "time" "github.com/gorilla/mux" + "github.com/jackc/pgx/pgtype" "gonum.org/v1/gonum/stat" "gemma.intevation.de/gemma/pkg/common" @@ -66,15 +67,23 @@ WHERE ` - selectAllWaterlevelsMeasuredSQL = ` -WITH g AS ( - SELECT ( + selectAllWaterlevelsMeasuredRangeSQL = ` +SELECT + min(measure_date), + max(measure_date) +FROM waterway.gauge_measurements WHERE + fk_gauge_id = ( $1::char(2), $2::char(3), $3::char(5), $4::char(5), - $5::int)::isrs loc -) + $5::int + )::isrs + AND NOT predicted + AND staging_done +` + + selectAllWaterlevelsMeasuredSQL = ` SELECT extract(day from measure_date)::varchar || ':' || extract(month from measure_date)::varchar AS day_month, @@ -84,8 +93,16 @@ avg(water_level) AS mean, min(water_level) AS min, max(water_level) AS max -FROM waterway.gauge_measurements, g -WHERE fk_gauge_id = g.loc AND NOT predicted AND staging_done +FROM waterway.gauge_measurements WHERE + fk_gauge_id = ( + $1::char(2), + $2::char(3), + $3::char(5), + $4::char(5), + $5::int + )::isrs + AND NOT predicted + AND staging_done GROUP BY extract(day from measure_date)::varchar || ':' || extract(month from measure_date)::varchar; ` @@ -235,6 +252,29 @@ ctx := req.Context() + var begin, end pgtype.Timestamp + + err = conn.QueryRowContext( + ctx, + selectAllWaterlevelsMeasuredRangeSQL, + isrs.CountryCode, + isrs.LoCode, + isrs.FairwaySection, + isrs.Orc, + isrs.Hectometre, + ).Scan(&begin, &end) + + switch { + case err == sql.ErrNoRows || begin.Status != pgtype.Present || end.Status != pgtype.Present: + http.NotFound(rw, req) + return + case err != nil: + http.Error( + rw, fmt.Sprintf("error: %v", err), + http.StatusInternalServerError) + return + } + rows, err := conn.QueryContext( ctx, selectAllWaterlevelsMeasuredSQL, @@ -312,6 +352,24 @@ out := csv.NewWriter(rw) record := []string{ + fmt.Sprintf("#interval: %d-%d", + begin.Time.UTC().Year(), + end.Time.UTC().Year()), + "", + "", + "", + "", + "", + "", + } + + if err := out.Write(record); err != nil { + log.Printf("error: %v\n", err) + // Too late for an HTTP error code. + return + } + + record = []string{ "#date", "#min", "#max",