Mercurial > gemma
changeset 5202:fbc79c8459b4 new-fwa
Load water values, too.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Fri, 08 May 2020 15:59:44 +0200 |
parents | 40daecc6f552 |
children | 355195a90298 |
files | pkg/controllers/bottlenecks.go pkg/controllers/fwa.go |
diffstat | 2 files changed, 100 insertions(+), 13 deletions(-) [+] |
line wrap: on
line diff
--- a/pkg/controllers/bottlenecks.go Fri May 08 15:34:28 2020 +0200 +++ b/pkg/controllers/bottlenecks.go Fri May 08 15:59:44 2020 +0200 @@ -92,17 +92,6 @@ ` ) -type ( - availMeasurement struct { - when time.Time - depth int16 - width int16 - value int16 - } - - availMeasurements []availMeasurement -) - // afdRefs are the typical available fairway depth reference values. var afdRefs = []float64{ 230,
--- a/pkg/controllers/fwa.go Fri May 08 15:34:28 2020 +0200 +++ b/pkg/controllers/fwa.go Fri May 08 15:59:44 2020 +0200 @@ -62,6 +62,44 @@ grwl.depth_reference like 'LDC%' AND bns.bottleneck_id = $1 AND grwl.validity && tstzrange($2, $3)` + + selectMeasurementsSQL = ` +WITH data AS ( + SELECT + efa.measure_date, + efa.available_depth_value, + efa.available_width_value, + efa.water_level_value + FROM waterway.effective_fairway_availability efa + JOIN waterway.fairway_availability fa + ON efa.fairway_availability_id = fa.id + JOIN waterway.bottlenecks bn + ON fa.bottleneck_id = bn.bottleneck_id + WHERE + bn.bottleneck_id = $1 AND + efa.level_of_service = $2 AND + efa.measure_type = 'Measured' AND + (efa.available_depth_value IS NOT NULL OR + efa.available_width_value IS NOT NULL) AND + efa.water_level_value IS NOT NULL +), +before AS ( + SELECT * FROM data WHERE measure_date < $3 + ORDER BY measure_date DESC LIMIT 1 +), +inside AS ( + SELECT * FROM data WHERE measure_date BETWEEN $3 AND $4 +), +after AS ( + SELECT * FROM data WHERE measure_date > $4 + ORDER BY measure_date LIMIT 1 +) +SELECT * FROM before +UNION ALL +SELECT * FROM inside +UNION ALL +SELECT * FROM after +ORDER BY measure_date` ) type ( @@ -83,9 +121,19 @@ limitingValidities []limitingValidity + availMeasurement struct { + when time.Time + depth int16 + width int16 + value int16 + } + + availMeasurements []availMeasurement + bottleneck struct { - id string - validities limitingValidities + id string + validities limitingValidities + measurements availMeasurements } ) @@ -103,6 +151,11 @@ return } + los, ok := parseFormInt(rw, req, "los", 1) + if !ok { + return + } + ctx := req.Context() conn := middleware.GetDBConn(req) @@ -141,6 +194,13 @@ http.Error(rw, "cannot load LDCs", http.StatusInternalServerError) return } + // load values + if err := bottlenecks[i].loadValues(ctx, conn, from, to, los); err != nil { + log.Printf("error: %v\n", err) + http.Error(rw, "cannot load LDCs", http.StatusInternalServerError) + return + } + } // TODO: Implement me! @@ -352,5 +412,43 @@ } } } + return rows.Err() +} + +func (bn *bottleneck) loadValues( + ctx context.Context, + conn *sql.Conn, + from, to time.Time, + los int, +) error { + rows, err := conn.QueryContext( + ctx, selectMeasurementsSQL, + bn.id, + from, to, + los) + if err != nil { + return err + } + defer rows.Close() + + var ms availMeasurements + + for rows.Next() { + var m availMeasurement + if err := rows.Scan( + &m.when, + &m.depth, + &m.width, + &m.value, + ); err != nil { + return err + } + m.when = m.when.UTC() + ms = append(ms, m) + } + if err := rows.Err(); err != nil { + return err + } + bn.measurements = ms return nil }