annotate pkg/controllers/surveys.go @ 5574:271888ef85bc surveysperbottleneckid

Finalize use of bottleneck id instead of name. Main feaure is the restructuring of the /surveys endpoint. The endpoint now takes queryparameters as qualifiers. /surveys?id={id} returns surveys to a given bottleck {id} /surveys?name={name} & date={date} returns surveys for given {name} of a bottleneck and {date} for a surveydate. This is needed mainly because there is some backwards incompatibility when reviewing sounding results where the summary of a sr import contains only the name instead of the id of the according bottleneck. To bridge this mismatch the survey endpoint could be queried for the sr to review given the surveydate and the name of the bottleneck. Premise is here, that a date and a bottleneck's name is specific enough to identify the correct survey with the correct bottleneck.
author Thomas Junk <thomas.junk@intevation.de>
date Wed, 21 Jul 2021 14:33:35 +0200
parents 3b842e951317
children 6709530f002e
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
1017
a244b18cb916 Added GNU Affero General Public License.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 825
diff changeset
1 // This is Free Software under GNU Affero General Public License v >= 3.0
a244b18cb916 Added GNU Affero General Public License.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 825
diff changeset
2 // without warranty, see README.md and license for details.
a244b18cb916 Added GNU Affero General Public License.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 825
diff changeset
3 //
a244b18cb916 Added GNU Affero General Public License.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 825
diff changeset
4 // SPDX-License-Identifier: AGPL-3.0-or-later
a244b18cb916 Added GNU Affero General Public License.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 825
diff changeset
5 // License-Filename: LICENSES/AGPL-3.0.txt
a244b18cb916 Added GNU Affero General Public License.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 825
diff changeset
6 //
2129
8f6345ad5f13 Extend /surveys/ endpoint to include reference gauge
Bernhard Reiter <bernhard@intevation.de>
parents: 1572
diff changeset
7 // Copyright (C) 2018, 2019 by via donau
1017
a244b18cb916 Added GNU Affero General Public License.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 825
diff changeset
8 // – Österreichische Wasserstraßen-Gesellschaft mbH
a244b18cb916 Added GNU Affero General Public License.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 825
diff changeset
9 // Software engineering by Intevation GmbH
a244b18cb916 Added GNU Affero General Public License.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 825
diff changeset
10 //
a244b18cb916 Added GNU Affero General Public License.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 825
diff changeset
11 // Author(s):
a244b18cb916 Added GNU Affero General Public License.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 825
diff changeset
12 // * Sascha Wilde <sascha.wilde@intevation.de>
1317
5443f5c9154c Added missing authors names in Go files.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1017
diff changeset
13 // * Sascha L. Teichmann <sascha.teichmann@intevation.de>
2129
8f6345ad5f13 Extend /surveys/ endpoint to include reference gauge
Bernhard Reiter <bernhard@intevation.de>
parents: 1572
diff changeset
14 // * Bernhard Reiter <bernhard.reiter@intevation.de>
1017
a244b18cb916 Added GNU Affero General Public License.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 825
diff changeset
15
812
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
16 package controllers
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
17
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
18 import (
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
19 "database/sql"
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
20 "net/http"
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
21
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
22 "gemma.intevation.de/gemma/pkg/models"
4244
4394daeea96a Moved JSONHandler into middleware package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4243
diff changeset
23
4394daeea96a Moved JSONHandler into middleware package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4243
diff changeset
24 mw "gemma.intevation.de/gemma/pkg/middleware"
812
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
25 )
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
26
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
27 const (
5574
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
28 listSurveysByIdSQL = `
4782
fc082c611b8b api/surveys list all existing surveys (no filter by gauge validity)
Sascha Wilde <wilde@intevation.de>
parents: 4244
diff changeset
29 SELECT DISTINCT
1572
056a86b24be2 Made bottleneck primary key an int. Attention: This may break something!
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1332
diff changeset
30 s.bottleneck_id,
2129
8f6345ad5f13 Extend /surveys/ endpoint to include reference gauge
Bernhard Reiter <bernhard@intevation.de>
parents: 1572
diff changeset
31 s.date_info::text,
2146
7267f8168176 Extending endpoint /surveys to return depth_reference
Bernhard Reiter <bernhard@intevation.de>
parents: 2129
diff changeset
32 s.depth_reference,
4891
e68220372832 List surveys even if mandatory base data is missing.
Sascha Wilde <wilde@intevation.de>
parents: 4782
diff changeset
33 COALESCE(g.objname, 'ERROR: MISSING GAUGE') AS gauge_objname,
5405
5e7fca6ef935 When listing surveys tell survey type as well.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4891
diff changeset
34 r.value AS waterlevel_value,
5406
6d73836bc2fb quick fix of typo
Thomas Junk <thomas.junk@intevation.de>
parents: 5405
diff changeset
35 COALESCE(s.surtyp, 'ERROR: MISSING SURVEY TYPE') AS surtype
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2317
diff changeset
36 FROM waterway.bottlenecks AS b
4050
02d5c2e17eb8 Adapted surveys back end to new historization model.
Sascha Wilde <wilde@intevation.de>
parents: 3656
diff changeset
37 JOIN waterway.sounding_results AS s ON b.bottleneck_id = s.bottleneck_id
4891
e68220372832 List surveys even if mandatory base data is missing.
Sascha Wilde <wilde@intevation.de>
parents: 4782
diff changeset
38 LEFT JOIN waterway.gauges AS g
4050
02d5c2e17eb8 Adapted surveys back end to new historization model.
Sascha Wilde <wilde@intevation.de>
parents: 3656
diff changeset
39 ON b.gauge_location = g.location AND s.date_info::timestamptz <@ g.validity
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2317
diff changeset
40 LEFT JOIN waterway.gauges_reference_water_levels AS r
3645
02951a62e8c6 'Historicise' bottlenecks on import
Tom Gottfried <tom@intevation.de>
parents: 3302
diff changeset
41 ON s.depth_reference = r.depth_reference
02951a62e8c6 'Historicise' bottlenecks on import
Tom Gottfried <tom@intevation.de>
parents: 3302
diff changeset
42 AND g.location = r.location AND g.validity = r.validity
5572
3b842e951317 change use from name of bottleneck to its id.
Thomas Junk <thomas.junk@intevation.de>
parents: 5462
diff changeset
43 WHERE b.bottleneck_id = $1`
5574
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
44
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
45 listSurveysByNameDateSQL = `
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
46 SELECT DISTINCT
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
47 s.bottleneck_id,
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
48 s.date_info::text,
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
49 s.depth_reference,
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
50 COALESCE(g.objname, 'ERROR: MISSING GAUGE') AS gauge_objname,
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
51 r.value AS waterlevel_value,
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
52 COALESCE(s.surtyp, 'ERROR: MISSING SURVEY TYPE') AS surtype
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
53 FROM waterway.bottlenecks AS b
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
54 JOIN waterway.sounding_results AS s ON b.bottleneck_id = s.bottleneck_id
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
55 LEFT JOIN waterway.gauges AS g
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
56 ON b.gauge_location = g.location AND s.date_info::timestamptz <@ g.validity
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
57 LEFT JOIN waterway.gauges_reference_water_levels AS r
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
58 ON s.depth_reference = r.depth_reference
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
59 AND g.location = r.location AND g.validity = r.validity
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
60 WHERE b.objnam = $1 and s.date_info = $2`
812
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
61 )
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
62
4244
4394daeea96a Moved JSONHandler into middleware package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4243
diff changeset
63 func listSurveys(req *http.Request) (jr mw.JSONResult, err error) {
812
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
64
5574
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
65 v := req.URL.Query()
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
66 bottleneckName := v.Get("name")
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
67 date := v.Get("date")
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
68 id := v.Get("id")
812
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
69 var rows *sql.Rows
5574
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
70 surveys := []*models.Survey{}
812
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
71
5574
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
72 if date == "" && bottleneckName == "" && id != "" {
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
73 rows, err = mw.JSONConn(req).QueryContext(req.Context(), listSurveysByIdSQL, id)
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
74 } else if date != "" && bottleneckName != "" && id == "" {
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
75 rows, err = mw.JSONConn(req).QueryContext(req.Context(), listSurveysByNameDateSQL, bottleneckName, date)
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
76 } else {
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
77 jr = mw.JSONResult{
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
78 Result: struct {
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
79 Surveys []*models.Survey `json:"surveys"`
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
80 }{surveys},
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
81 }
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
82 return
271888ef85bc Finalize use of bottleneck id instead of name.
Thomas Junk <thomas.junk@intevation.de>
parents: 5572
diff changeset
83 }
812
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
84 if err != nil {
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
85 return
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
86 }
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
87 defer rows.Close()
2149
ed0ee54fc49e Extend /surveys endpoint to include waterlevel_value
Bernhard Reiter <bernhard@intevation.de>
parents: 2146
diff changeset
88 // as we do not use the values here, we could simply the code here
ed0ee54fc49e Extend /surveys endpoint to include waterlevel_value
Bernhard Reiter <bernhard@intevation.de>
parents: 2146
diff changeset
89 // to work without an explicit mdels/surverys.go
ed0ee54fc49e Extend /surveys endpoint to include waterlevel_value
Bernhard Reiter <bernhard@intevation.de>
parents: 2146
diff changeset
90 // (like done in controllers/search.go)
812
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
91 for rows.Next() {
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
92 var survey models.Survey
5405
5e7fca6ef935 When listing surveys tell survey type as well.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4891
diff changeset
93 var surType string
2152
8132434f4093 Surveys controller: Fixed nullable water level.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2149
diff changeset
94 var level sql.NullInt64
812
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
95 if err = rows.Scan(
1332
447db2bce052 Renamed BottleneckId to BottleneckID to make golint happy.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1317
diff changeset
96 &survey.BottleneckID,
812
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
97 &survey.DateInfo,
2146
7267f8168176 Extending endpoint /surveys to return depth_reference
Bernhard Reiter <bernhard@intevation.de>
parents: 2129
diff changeset
98 &survey.DepthReference,
2129
8f6345ad5f13 Extend /surveys/ endpoint to include reference gauge
Bernhard Reiter <bernhard@intevation.de>
parents: 1572
diff changeset
99 &survey.ReferenceGauge,
2152
8132434f4093 Surveys controller: Fixed nullable water level.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2149
diff changeset
100 &level,
5405
5e7fca6ef935 When listing surveys tell survey type as well.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4891
diff changeset
101 &surType,
812
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
102 ); err != nil {
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
103 return
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
104 }
5462
fc01cdaf056d Cosmetics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5459
diff changeset
105 switch {
fc01cdaf056d Cosmetics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5459
diff changeset
106 case level.Valid:
2152
8132434f4093 Surveys controller: Fixed nullable water level.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2149
diff changeset
107 survey.WaterLevelValue = &level.Int64
5462
fc01cdaf056d Cosmetics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5459
diff changeset
108 case survey.DepthReference == "ZPG":
fc01cdaf056d Cosmetics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5459
diff changeset
109 survey.WaterLevelValue = new(int64)
2152
8132434f4093 Surveys controller: Fixed nullable water level.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2149
diff changeset
110 }
5405
5e7fca6ef935 When listing surveys tell survey type as well.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4891
diff changeset
111 survey.SurveyType = models.SurveyType(surType)
2152
8132434f4093 Surveys controller: Fixed nullable water level.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2149
diff changeset
112 surveys = append(surveys, &survey)
812
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
113 }
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
114
825
a5452a001b46 Added missing error check in surveys controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 812
diff changeset
115 if err = rows.Err(); err != nil {
a5452a001b46 Added missing error check in surveys controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 812
diff changeset
116 return
a5452a001b46 Added missing error check in surveys controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 812
diff changeset
117 }
a5452a001b46 Added missing error check in surveys controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 812
diff changeset
118
4244
4394daeea96a Moved JSONHandler into middleware package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4243
diff changeset
119 jr = mw.JSONResult{
812
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
120 Result: struct {
2152
8132434f4093 Surveys controller: Fixed nullable water level.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2149
diff changeset
121 Surveys []*models.Survey `json:"surveys"`
812
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
122 }{surveys},
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
123 }
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
124 return
98a7776100fb Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
125 }