--- a/flys-artifacts/ChangeLog	Mon Apr 18 13:23:12 2011 +0000
+++ b/flys-artifacts/ChangeLog	Mon Apr 18 14:40:35 2011 +0000
@@ -1,3 +1,9 @@
+2011-04-18	Sascha L. Teichmann	<sascha.teichmann@intevation.de>
+
+	* src/main/java/de/intevation/flys/artifacts/model/WstValueTable.java:
+	  Fetches w/q value tables from the backend. TODO: Move this
+	  to the backend and use a view.
+
 2011-04-18  Ingo Weinzierl <ingo@intevation.de>
 
 	* src/main/java/de/intevation/flys/artifacts/FLYSArtifact.java: Changed

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/WstValueTable.java	Mon Apr 18 14:40:35 2011 +0000
@@ -0,0 +1,180 @@
+package de.intevation.flys.artifacts.model;
+
+import java.io.Serializable;
+
+import de.intevation.flys.model.River;
+import de.intevation.flys.model.Wst;
+import de.intevation.flys.model.WstColumn;
+
+import de.intevation.flys.backend.SessionHolder;
+
+import java.util.ArrayList;
+import java.util.Comparator;
+import java.util.List;
+import java.util.Collections;
+import java.util.Iterator;
+
+import org.hibernate.Session;
+import org.hibernate.Query;
+import org.hibernate.SQLQuery;
+
+import org.hibernate.type.StandardBasicTypes;
+
+public class WstValueTable
+implements   Serializable
+{
+
+    // TODO: put this into a property file
+    public static final String SQL_POS_WQ = 
+        "SELECT wcv.position AS position,"                           +
+        "       w,"                                                  +
+        "       (SELECT q"                                           +
+        "        FROM   wst_column_q_ranges wcqr"                    +
+        "               JOIN wst_q_ranges wqr"                       +
+        "                 ON wcqr.wst_q_range_id = wqr.id"           +
+        "               JOIN ranges r"                               +
+        "                 ON r.id = wqr.range_id"                    +
+        "        WHERE  wcqr.wst_column_id = wc.id"                  +
+        "               AND wcv.position BETWEEN r.a AND r.b) AS q," +
+        "       wc.position AS column_pos "                          +
+        "FROM   wst_column_values wcv"                               +
+        "       JOIN wst_columns wc"                                 +
+        "         ON wcv.wst_column_id = wc.id"                      +
+        "       JOIN wsts w"                                         +
+        "         ON wc.wst_id = w.id "                              +
+        "WHERE  w.id = :wst_id"                                      +
+        "ORDER  BY wcv.position ASC,"                                +
+        "          wc.position DESC";
+
+    public static class Column
+    implements          Serializable
+    {
+        protected String name;
+
+        public Column() {
+        }
+
+        public Column(String name) {
+            this.name = name;
+        }
+
+        public String getName() {
+            return name;
+        }
+
+        public void setName(String name) {
+            this.name = name;
+        }
+    }
+    // class Column
+
+    public static class Row
+    implements          Serializable
+    {
+        double    km;
+        double [] wq;
+
+        public Row() {
+        }
+
+        public Row(double km, double [] wq) {
+            this.km = km;
+            this.wq = wq;
+        }
+    }
+    // class Row
+
+    protected List<Row> rows;
+
+    protected Column [] columns;
+
+    public WstValueTable() {
+        rows = new ArrayList<Row>();
+    }
+
+    public WstValueTable(Column [] columns) {
+        this();
+        this.columns = columns;
+    }
+
+    public static WstValueTable getTable(River river) {
+        return getTable(river, 0);
+    }
+
+    public static WstValueTable getTable(River river, int kind) {
+
+        Session session = SessionHolder.HOLDER.get();
+
+        Query query = session.createQuery(
+            "from Wst where river=:river and kind=:kind");
+        query.setParameter("river", river);
+        query.setInteger("kind",    kind);
+
+        List<Wst> wsts = query.list();
+
+        if (wsts.isEmpty()) {
+            return null;
+        }
+
+        Wst wst = wsts.get(0);
+
+        // TODO: Do this sorting at database level
+        List<WstColumn> wstColumns = new ArrayList(wst.getColumns());
+        Collections.sort(wstColumns, new Comparator<WstColumn>() {
+            public int compare(WstColumn a, WstColumn b) {
+                int pa = a.getPosition();
+                int pb = b.getPosition();
+                if (pa < pb) return -1;
+                if (pa > pb) return +1;
+                return 0;
+            }
+        });
+
+        Column [] columns = new Column[wstColumns.size()];
+        for (int i = 0; i < columns.length; ++i) {
+            columns[i] = new Column(wstColumns.get(i).getName());
+        }
+
+        // using native SQL here to avoid myriad of small objects.
+        SQLQuery sqlQuery = session.createSQLQuery(SQL_POS_WQ)
+            .addScalar("position",   StandardBasicTypes.DOUBLE)
+            .addScalar("w",          StandardBasicTypes.DOUBLE)
+            .addScalar("q",          StandardBasicTypes.DOUBLE)
+            .addScalar("column_pos", StandardBasicTypes.INTEGER);
+
+        sqlQuery.setInteger("wst_id", wst.getId());
+
+        WstValueTable valueTable = new WstValueTable(columns);
+
+        int lastColumnNo = -1;
+        Row row          = null;
+
+        for (Iterator<Object []> iter = sqlQuery.iterate(); iter.hasNext();) {
+            Object [] result = iter.next();
+            double km    = (Double) result[0];
+            Double w     = (Double) result[1];
+            Double q     = (Double) result[2];
+            int columnNo = (Integer)result[3];
+
+            if (columnNo > lastColumnNo) { // new row
+                if (row != null) {
+                    valueTable.rows.add(row);
+                }
+                row = new Row(km, new double[(columnNo+1) << 1]);
+            }
+            int idx = columnNo << 1;
+
+            row.wq[idx  ] = w != null ? w : Double.NaN;
+            row.wq[idx+1] = q != null ? q : Double.NaN;
+
+            lastColumnNo = columnNo;
+        }
+
+        if (row != null) {
+            valueTable.rows.add(row);
+        }
+
+        return valueTable;
+    }
+}
+// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :