--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/pkg/controllers/bottlenecks.go	Mon Apr 08 16:38:57 2019 +0200
@@ -0,0 +1,287 @@
+// This is Free Software under GNU Affero General Public License v >= 3.0
+// without warranty, see README.md and license for details.
+//
+// SPDX-License-Identifier: AGPL-3.0-or-later
+// License-Filename: LICENSES/AGPL-3.0.txt
+//
+// Copyright (C) 2019 by via donau
+//   – Österreichische Wasserstraßen-Gesellschaft mbH
+// Software engineering by Intevation GmbH
+//
+// Author(s):
+//  * Sascha L. Teichmann <sascha.teichmann@intevation.de>
+
+package controllers
+
+import (
+	"database/sql"
+	"fmt"
+	"log"
+	"net/http"
+	"sort"
+	"strconv"
+	"strings"
+	"time"
+
+	"gemma.intevation.de/gemma/pkg/common"
+	"github.com/gorilla/mux"
+)
+
+const (
+	selectAvailableDepthSQL = `
+WITH data AS (
+  SELECT
+    efa.measure_date,
+    efa.available_depth_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.id
+  WHERE
+    bn.objnam = $1 AND
+    efa.level_of_service = $2 AND
+    efa.measure_type = 'Measured' AND
+    efa.available_depth_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
+`
+
+	selectGaugeLevelsSQL = `
+SELECT
+  grwl.depth_reference,
+  grwl.value
+FROM waterway.gauges_reference_water_levels grwl JOIN 
+     waterway.bottlenecks bns
+	 ON bns.fk_g_fid = grwl.gauge_id
+WHERE bns.objnam = $1 AND (
+  grwl.depth_reference like 'HDC%' OR
+  grwl.depth_reference like 'LDC%' OR
+  grwl.depth_reference like 'MW%'
+)
+`
+)
+
+func bottleneckAvailabilty(
+	_ interface{},
+	req *http.Request,
+	conn *sql.Conn,
+) (jr JSONResult, err error) {
+	bn := mux.Vars(req)["objnam"]
+
+	if bn == "" {
+		err = JSONError{
+			Code:    http.StatusBadRequest,
+			Message: "Missing objnam of bottleneck",
+		}
+		return
+	}
+
+	type referenceValue struct {
+		level int
+		value int
+	}
+
+	ctx := req.Context()
+
+	loadReferenceValues := func() ([]referenceValue, error) {
+		rows, err := conn.QueryContext(ctx, selectGaugeLevelsSQL, bn)
+		if err != nil {
+			return nil, err
+		}
+		defer rows.Close()
+
+		var levels []referenceValue
+
+	loop:
+		for rows.Next() {
+			var what string
+			var value int
+			if err := rows.Scan(&what, &value); err != nil {
+				return nil, err
+			}
+			var level int
+			switch {
+			case strings.HasPrefix(what, "LDC"):
+				level = 0
+			case strings.HasPrefix(what, "MW"):
+				level = 1
+			case strings.HasPrefix(what, "HDC"):
+				level = 2
+			default:
+				return nil, fmt.Errorf("Unexpected reference level type '%s'", what)
+			}
+			for i := range levels {
+				if levels[i].level == level {
+					levels[i].value = value
+					continue loop
+				}
+			}
+			levels = append(levels, referenceValue{level: level, value: value})
+		}
+
+		if err := rows.Err(); err != nil {
+			return nil, err
+		}
+
+		sort.Slice(levels, func(i, j int) bool { return levels[i].level < levels[j].level })
+
+		return levels, nil
+	}
+
+	var refVals []referenceValue
+	if refVals, err = loadReferenceValues(); err != nil {
+		return
+	}
+
+	if len(refVals) == 0 {
+		err = JSONError{
+			Code:    http.StatusNotFound,
+			Message: "No gauge reference values found for bottleneck",
+		}
+	}
+
+	var from, to time.Time
+
+	if f := req.FormValue("from"); f != "" {
+		if from, err = time.Parse(common.TimeFormat, f); err != nil {
+			err = JSONError{
+				Code:    http.StatusBadRequest,
+				Message: fmt.Sprintf("Invalid time format for 'from' field: %v", err),
+			}
+			return
+		}
+		from = from.UTC()
+	} else {
+		from = time.Now().AddDate(-1, 0, 0).UTC()
+	}
+
+	if t := req.FormValue("to"); t != "" {
+		if to, err = time.Parse(common.TimeFormat, t); err != nil {
+			err = JSONError{
+				Code:    http.StatusBadRequest,
+				Message: fmt.Sprintf("Invalid time format for 'from' field: %v", err),
+			}
+			return
+		}
+		to = to.UTC()
+	} else {
+		to = from.AddDate(1, 0, 0).UTC()
+	}
+
+	if to.Before(from) {
+		to, from = from, to
+	}
+
+	log.Printf("info: time interval: (%v - %v)\n", from, to)
+
+	var los int
+	if l := req.FormValue("los"); l != "" {
+		if los, err = strconv.Atoi(l); err != nil {
+			err = JSONError{
+				Code:    http.StatusBadRequest,
+				Message: fmt.Sprintf("Invalid value for field 'los': %v", err),
+			}
+			return
+		}
+	} else {
+		los = 1
+	}
+
+	type measurement struct {
+		when  time.Time
+		depth int
+		level int
+	}
+
+	loadDepthValues := func() ([]measurement, error) {
+
+		rows, err := conn.QueryContext(
+			ctx, selectAvailableDepthSQL, bn, los, from, to)
+		if err != nil {
+			return nil, err
+		}
+		defer rows.Close()
+
+		var ms []measurement
+
+		for rows.Next() {
+			var m measurement
+			if err := rows.Scan(&m.when, &m.depth, &m.level); err != nil {
+				return nil, err
+			}
+			ms = append(ms, m)
+		}
+
+		if err := rows.Err(); err != nil {
+			return nil, err
+		}
+
+		return ms, nil
+	}
+
+	var ms []measurement
+
+	if ms, err = loadDepthValues(); err != nil {
+		return
+	}
+
+	if len(ms) == 0 {
+		err = JSONError{
+			Code:    http.StatusNotFound,
+			Message: "No available fairway depth values found",
+		}
+		return
+	}
+
+	// TODO: Calculate the ranges.
+
+	type outputLevel struct {
+		Level string `json:"level"`
+		Value int    `json:"value"`
+	}
+
+	type output struct {
+		Levels []outputLevel `json:"levels"`
+	}
+
+	out := output{}
+
+	for i := range refVals {
+		var level string
+		switch refVals[i].level {
+		case 0:
+			level = "LDC"
+		case 1:
+			level = "MW"
+		case 2:
+			level = "HDC"
+		}
+		out.Levels = append(out.Levels, outputLevel{
+			Level: level,
+			Value: refVals[i].value,
+		})
+	}
+
+	jr = JSONResult{Result: &out}
+
+	return
+}

--- a/pkg/controllers/routes.go	Mon Apr 08 16:27:37 2019 +0200
+++ b/pkg/controllers/routes.go	Mon Apr 08 16:38:57 2019 +0200
@@ -298,6 +298,9 @@
 		})).Methods(http.MethodPut)
 
 	// Handler to serve data to the client.
+	api.Handle("/data/bottleneck/availability/{objnam}", any(&JSONHandler{
+		Handle: bottleneckAvailabilty,
+	})).Methods(http.MethodGet)
 
 	api.Handle("/data/waterlevels/{gauge}", any(
 		middleware.DBConn(http.HandlerFunc(waterlevels)))).Methods(http.MethodGet)