# HG changeset patch
# User Bernhard Reiter <bernhard@intevation.de>
# Date 1549621565 -3600
# Node ID ed0ee54fc49e6bdfb45824827a4e0b1680956a03
# Parent  4057a1f15174d713299f52d9e95ab333a15dd4c2
Extend /surveys endpoint to include waterlevel_value

 * Change SQL to use the same strategy as used in schema/gemma.sql
   for bottlenecks_geoserver to determine the waterlevel_value.
 * Extend go controler to deal with the situation where the value is not
   there. This is still suboptimal as this creates a json object for
   the NullInt64 value.

diff -r 4057a1f15174 -r ed0ee54fc49e pkg/controllers/surveys.go
--- a/pkg/controllers/surveys.go	Fri Feb 08 11:03:12 2019 +0100
+++ b/pkg/controllers/surveys.go	Fri Feb 08 11:26:05 2019 +0100
@@ -24,18 +24,32 @@
 )
 
 const (
+	// Use the same logic as in schema/gemma.sql to find out
+	// which is the reference gauge and which from its values is meant.
+	// The LEFT for the JOIN is necessary because we still want to list
+	// the surveys even if the corresponding value cannot be found because the
+	// depth_reference and reference_water_level strings do not match.
+	//
+	// As this information could be vary from survey entry to survey,
+	// in theory even for the same bottleneck
+	// it seems to be the right place to return it from the /surveys endpoint.
 	listSurveysSQL = `
 SELECT
   s.bottleneck_id,
   s.date_info::text,
   s.depth_reference,
-  bg.objname AS gauge_objname
+  bg.objname AS gauge_objname,
+  r.value AS waterlevel_value
 FROM
-  ( SELECT * FROM waterway.bottlenecks AS b, waterway.gauges AS g
-    WHERE b.fk_g_fid = g.location
-  ) AS bg
-  JOIN waterway.sounding_results AS s
-ON bg.id = s.bottleneck_id
+  (
+	( SELECT * FROM waterway.bottlenecks AS b, waterway.gauges AS g
+		WHERE b.fk_g_fid = g.location
+	) AS bg
+	JOIN waterway.sounding_results AS s
+	ON bg.id = s.bottleneck_id
+  )
+LEFT JOIN waterway.gauges_reference_water_levels AS r
+ON s.depth_reference = r.reference_water_level AND bg.location = r.gauge_id
 WHERE bg.objnam=$1`
 )
 
@@ -57,6 +71,9 @@
 
 	surveys := []models.Survey{}
 
+	// as we do not use the values here, we could simply the code here
+	// to work without an explicit mdels/surverys.go
+	// (like done in controllers/search.go)
 	for rows.Next() {
 		var survey models.Survey
 		if err = rows.Scan(
@@ -64,6 +81,7 @@
 			&survey.DateInfo,
 			&survey.DepthReference,
 			&survey.ReferenceGauge,
+			&survey.WaterLevelValue,
 		); err != nil {
 			return
 		}
diff -r 4057a1f15174 -r ed0ee54fc49e pkg/models/surveys.go
--- a/pkg/models/surveys.go	Fri Feb 08 11:03:12 2019 +0100
+++ b/pkg/models/surveys.go	Fri Feb 08 11:26:05 2019 +0100
@@ -13,11 +13,16 @@
 
 package models
 
+import (
+	"database/sql"
+)
+
 type (
 	Survey struct {
-		BottleneckID   string `json:"bottleneck_id"`
-		DateInfo       string `json:"date_info"`
-		DepthReference string `json:"depth_reference"`
-		ReferenceGauge string `json:"gauge_objname"`
+		BottleneckID    string        `json:"bottleneck_id"`
+		DateInfo        string        `json:"date_info"`
+		DepthReference  string        `json:"depth_reference"`
+		ReferenceGauge  string        `json:"gauge_objname"`
+		WaterLevelValue sql.NullInt64 `json:"waterlevel_value"`
 	}
 )