# HG changeset patch
# User Sascha L. Teichmann <sascha.teichmann@intevation.de>
# Date 1572001430 -7200
# Node ID 1fef4679b07aaab069e4119b28e3c4adddc3ee27
# Parent  6f3730196ebb0cd029bfc208bd87bf295fa2d518
Added an endpoint GET api/imports/export to export imports.

diff -r 6f3730196ebb -r 1fef4679b07a pkg/controllers/importqueue.go
--- a/pkg/controllers/importqueue.go	Fri Oct 25 11:13:31 2019 +0200
+++ b/pkg/controllers/importqueue.go	Fri Oct 25 13:03:50 2019 +0200
@@ -16,6 +16,7 @@
 import (
 	"context"
 	"database/sql"
+	"encoding/csv"
 	"encoding/json"
 	"fmt"
 	"log"
@@ -57,6 +58,23 @@
 FROM import.imports
 WHERE
 `
+	selectExportSQL = `
+SELECT
+  imports.id AS id,
+  state::varchar,
+  enqueued,
+  changed,
+  kind,
+  username,
+  (SELECT country FROM users.list_users lu
+    WHERE lu.username = import.imports.username) AS country,
+  signer,
+  EXISTS(SELECT 1 FROM import.import_logs
+    WHERE kind = 'warn'::log_type and import_id = imports.id) AS has_warnings,
+  data
+FROM import.imports
+WHERE
+`
 	selectEnqueuedSQL = `
 SELECT enqueued FROM import.imports
 WHERE
@@ -89,7 +107,7 @@
 	args []interface{}
 }
 
-func buildFilters(req *http.Request) (*filledStmt, *filledStmt, *filledStmt, error) {
+func buildFilters(projection string, req *http.Request) (*filledStmt, *filledStmt, *filledStmt, error) {
 
 	var l, a, b filterAnd
 
@@ -175,12 +193,16 @@
 
 	var counting bool
 
-	switch count := strings.ToLower(req.FormValue("count")); count {
-	case "1", "t", "true":
-		counting = true
-		fl.stmt.WriteString(selectImportsCountSQL)
-	default:
-		fl.stmt.WriteString(selectImportsSQL)
+	if projection != "" {
+		fl.stmt.WriteString(projection)
+	} else {
+		switch count := strings.ToLower(req.FormValue("count")); count {
+		case "1", "t", "true":
+			counting = true
+			fl.stmt.WriteString(selectImportsCountSQL)
+		default:
+			fl.stmt.WriteString(selectImportsSQL)
+		}
 	}
 
 	if len(l) == 0 {
@@ -231,11 +253,131 @@
 	return &models.ImportTime{Time: when.UTC()}
 }
 
+func exportImports(rw http.ResponseWriter, req *http.Request) {
+
+	list, _, _, err := buildFilters(selectExportSQL, req)
+	if err != nil {
+		http.Error(rw, "error: "+err.Error(), http.StatusBadRequest)
+		return
+	}
+
+	rw.Header().Add("Content-Type", "text/csv")
+	out := csv.NewWriter(rw)
+
+	record := []string{
+		"#id",
+		"#kind",
+		"#enqueued",
+		"#changed",
+		"#user",
+		"#country",
+		"#signer",
+		"#state",
+		"#warnings",
+		"#source",
+	}
+
+	if err := out.Write(record); err != nil {
+		// Too late for HTTP status message.
+		log.Printf("error: %v\n", err)
+		return
+	}
+
+	conn := mw.GetDBConn(req)
+	ctx := req.Context()
+	var rows *sql.Rows
+	if rows, err = conn.QueryContext(ctx, list.stmt.String(), list.args...); err != nil {
+		log.Printf("error: %v\n", err)
+		return
+	}
+	defer rows.Close()
+
+	stringString := func(s sql.NullString) string {
+		if s.Valid {
+			return s.String
+		}
+		return ""
+	}
+
+	// Extract some meta infos from the import.
+	type Description interface {
+		Description() (string, error)
+	}
+
+	for rows.Next() {
+		var (
+			id          int64
+			state       string
+			enqueued    time.Time
+			changed     time.Time
+			kind        string
+			user        string
+			country     string
+			signer      sql.NullString
+			warnings    bool
+			data        string
+			description string
+		)
+		if err = rows.Scan(
+			&id,
+			&state,
+			&enqueued,
+			&changed,
+			&kind,
+			&user,
+			&country,
+			&signer,
+			&warnings,
+			&data,
+		); err != nil {
+			return
+		}
+
+		// Do some introspection on the job to be more verbose.
+		if jc := imports.FindJobCreator(imports.JobKind(kind)); jc != nil {
+			job := jc.Create()
+			if err := common.FromJSONString(data, job); err != nil {
+				log.Printf("error: %v\n", err)
+			} else if desc, ok := job.(Description); ok {
+				if description, err = desc.Description(); err != nil {
+					log.Printf("error: %v\n", err)
+				}
+			}
+		}
+
+		record[0] = strconv.FormatInt(id, 10)
+		record[1] = kind
+		record[2] = enqueued.UTC().Format(common.TimeFormat)
+		record[3] = changed.UTC().Format(common.TimeFormat)
+		record[4] = user
+		record[5] = country
+		record[6] = stringString(signer)
+		record[7] = state
+		record[8] = strconv.FormatBool(warnings)
+		record[9] = strings.Replace(description, ",", "|", -1)
+
+		if err := out.Write(record); err != nil {
+			log.Printf("error: %v\n", err)
+			return
+		}
+	}
+
+	out.Flush()
+	if err := out.Error(); err != nil {
+		log.Printf("error: %v\n", err)
+	}
+
+	if err = rows.Err(); err != nil {
+		log.Printf("error: %v\n", err)
+		return
+	}
+}
+
 func listImports(req *http.Request) (jr mw.JSONResult, err error) {
 
 	var list, before, after *filledStmt
 
-	if list, before, after, err = buildFilters(req); err != nil {
+	if list, before, after, err = buildFilters("", req); err != nil {
 		return
 	}
 
diff -r 6f3730196ebb -r 1fef4679b07a pkg/controllers/routes.go
--- a/pkg/controllers/routes.go	Fri Oct 25 11:13:31 2019 +0200
+++ b/pkg/controllers/routes.go	Fri Oct 25 13:03:50 2019 +0200
@@ -290,6 +290,9 @@
 		Handle: listImports,
 	})).Methods(http.MethodGet)
 
+	api.Handle("/imports/export", sysAdmin(
+		mw.DBConn(http.HandlerFunc(exportImports)))).Methods(http.MethodGet)
+
 	api.Handle("/imports/{id:[0-9]+}", waterwayAdmin(&mw.JSONHandler{
 		Handle: importLogs,
 	})).Methods(http.MethodGet)
diff -r 6f3730196ebb -r 1fef4679b07a pkg/imports/sr.go
--- a/pkg/imports/sr.go	Fri Oct 25 11:13:31 2019 +0200
+++ b/pkg/imports/sr.go	Fri Oct 25 13:03:50 2019 +0200
@@ -215,6 +215,22 @@
 `
 )
 
+func (sr *SoundingResult) Description() (string, error) {
+
+	var descs []string
+
+	if sr.Bottleneck != nil {
+		descs = append(descs, *sr.Bottleneck)
+	}
+	if sr.Date != nil {
+		descs = append(descs, (*sr).Date.Format(common.DateFormat))
+	}
+	if sr.NegateZ != nil && *sr.NegateZ {
+		descs = append(descs, "negateZ")
+	}
+	return strings.Join(descs, "|"), nil
+}
+
 func (sr *SoundingResult) singleBeam() bool {
 	return sr.SingleBeam != nil && *sr.SingleBeam
 }