# HG changeset patch
# User Sascha L. Teichmann <sascha.teichmann@intevation.de>
# Date 1552563875 -3600
# Node ID 386ff766dfcd86198d41f91a14d08b58d4813435
# Parent  f90557a8c96095cf6a66e2907406c8ddcf5d1f0b
Replaced limit/offset filters with from/to as timestamps (format '2006-01-02T15:04:05.000').The output document also contains 'prev' and 'next' fields (if there is any matching data) from the times before and after 'from' and 'to'.

diff -r f90557a8c960 -r 386ff766dfcd pkg/controllers/importqueue.go
--- a/pkg/controllers/importqueue.go	Thu Mar 14 11:31:22 2019 +0100
+++ b/pkg/controllers/importqueue.go	Thu Mar 14 12:44:35 2019 +0100
@@ -14,6 +14,7 @@
 package controllers
 
 import (
+	"context"
 	"database/sql"
 	"encoding/json"
 	"fmt"
@@ -21,6 +22,7 @@
 	"net/http"
 	"strconv"
 	"strings"
+	"time"
 
 	"github.com/gorilla/mux"
 	"github.com/jackc/pgx/pgtype"
@@ -46,7 +48,7 @@
   kind,
   username,
   signer,
-  summary IS NOT NULL,
+  summary IS NOT NULL AS has_summary,
   EXISTS (
     SELECT true FROM warned
 	WHERE warned.id = imports.id
@@ -80,6 +82,14 @@
 
 	deleteImportSQL = `
 DELETE FROM import.imports WHERE id = $1`
+
+	selectBeforeSQL = `
+SELECT enqueued FROM import.imports
+WHERE enqueued < $1 ORDER BY enqueued LIMIT 1`
+
+	selectAfterSQL = `
+SELECT enqueued FROM import.imports
+WHERE enqueued > $1 ORDER BY enqueued LIMIT 1`
 )
 
 func toInt8Array(txt string) *pgtype.Int8Array {
@@ -132,11 +142,12 @@
 ) (*sql.Rows, error) {
 
 	var (
-		stmt   strings.Builder
-		args   []interface{}
-		states *pgtype.TextArray
-		kinds  *pgtype.TextArray
-		ids    *pgtype.Int8Array
+		stmt    strings.Builder
+		args    []interface{}
+		states  *pgtype.TextArray
+		kinds   *pgtype.TextArray
+		ids     *pgtype.Int8Array
+		hasCond bool
 	)
 
 	arg := func(format string, v interface{}) {
@@ -144,6 +155,15 @@
 		args = append(args, v)
 	}
 
+	cond := func(format string, v interface{}) {
+		if hasCond {
+			stmt.WriteString(" AND ")
+		} else {
+			hasCond = true
+		}
+		arg(format, v)
+	}
+
 	var warnings bool
 
 	switch warn := strings.ToLower(req.FormValue("warnings")); warn {
@@ -170,49 +190,74 @@
 		stmt.WriteString(withoutWarningsSQL)
 	}
 
-	if states != nil || kinds != nil || ids != nil {
-		stmt.WriteString(" WHERE ")
-	}
+	stmt.WriteString(" WHERE ")
 
 	if states != nil {
-		arg(" state = ANY($%d) ", states)
-	}
-
-	if states != nil && (kinds != nil || ids != nil) {
-		stmt.WriteString("AND")
+		cond(" state = ANY($%d) ", states)
 	}
 
 	if kinds != nil {
-		arg(" kind = ANY($%d) ", kinds)
-	}
-
-	if (states != nil || kinds != nil) && ids != nil {
-		stmt.WriteString("AND")
+		cond(" kind = ANY($%d) ", kinds)
 	}
 
 	if ids != nil {
-		arg(" id = ANY($%d) ", ids)
+		cond(" id = ANY($%d) ", ids)
+	}
+
+	if from := req.FormValue("from"); from != "" {
+		fromTime, err := time.Parse(models.ImportTimeFormat, from)
+		if err != nil {
+			return nil, err
+		}
+		cond(" enqueued >= $%d ", fromTime)
+	}
+
+	if to := req.FormValue("to"); to != "" {
+		toTime, err := time.Parse(models.ImportTimeFormat, to)
+		if err != nil {
+			return nil, err
+		}
+		cond(" enqueued <= $%d ", toTime)
+	}
+
+	if !hasCond {
+		stmt.WriteString(" TRUE ")
 	}
 
 	stmt.WriteString(" ORDER BY enqueued DESC ")
 
-	if lim := req.FormValue("limit"); lim != "" {
-		limit, err := strconv.ParseInt(lim, 10, 64)
-		if err != nil {
-			return nil, err
-		}
-		arg(" LIMIT $%d ", limit)
+	return conn.QueryContext(req.Context(), stmt.String(), args...)
+}
+
+func enqueued(ctx context.Context, conn *sql.Conn, what, query string) *models.ImportTime {
+	if what == "" {
+		return nil
+	}
+
+	t, err := time.Parse(models.ImportTimeFormat, what)
+	if err != nil {
+		log.Printf("warn: %v\n", err)
+		return nil
 	}
 
-	if ofs := req.FormValue("offset"); ofs != "" {
-		offset, err := strconv.ParseInt(ofs, 10, 64)
-		if err != nil {
-			return nil, err
-		}
-		arg(" OFFSET $%d ", offset)
+	var when time.Time
+	err = conn.QueryRowContext(ctx, query, t).Scan(&when)
+	switch {
+	case err == sql.ErrNoRows:
+		return nil
+	case err != nil:
+		log.Printf("warn: %v\n", err)
+		return nil
 	}
 
-	return conn.QueryContext(req.Context(), stmt.String(), args...)
+	return &models.ImportTime{when}
+}
+func enqueuedBefore(conn *sql.Conn, req *http.Request) *models.ImportTime {
+	return enqueued(req.Context(), conn, req.FormValue("from"), selectBeforeSQL)
+}
+
+func enqueuedAfter(conn *sql.Conn, req *http.Request) *models.ImportTime {
+	return enqueued(req.Context(), conn, req.FormValue("to"), selectAfterSQL)
 }
 
 func listImports(
@@ -258,9 +303,13 @@
 
 	jr = JSONResult{
 		Result: struct {
-			Imports []*models.Import `json:"imports"`
+			Prev    *models.ImportTime `json:"prev,omitempty"`
+			Next    *models.ImportTime `json:"next,omitempty"`
+			Imports []*models.Import   `json:"imports"`
 		}{
 			Imports: imports,
+			Prev:    enqueuedBefore(conn, req),
+			Next:    enqueuedAfter(conn, req),
 		},
 	}
 	return
diff -r f90557a8c960 -r 386ff766dfcd pkg/models/import.go
--- a/pkg/models/import.go	Thu Mar 14 11:31:22 2019 +0100
+++ b/pkg/models/import.go	Thu Mar 14 12:44:35 2019 +0100
@@ -19,6 +19,8 @@
 	"time"
 )
 
+const ImportTimeFormat = "2006-01-02T15:04:05.000"
+
 type (
 	ImportTime struct{ time.Time }
 
@@ -62,7 +64,7 @@
 }
 
 func (it ImportTime) MarshalJSON() ([]byte, error) {
-	return json.Marshal(it.Format("2006-01-02T15:04:05.000"))
+	return json.Marshal(it.Format(ImportTimeFormat))
 }
 
 func (it *ImportTime) Scan(x interface{}) error {