# HG changeset patch
# User Sascha L. Teichmann <sascha.teichmann@intevation.de>
# Date 1342130512 0
# Node ID e06036fdf0c7cf1b2c65a07e555847bc5f546035
# Parent  b52c4b34ec1b697cab4c33d5265eb59bd6a79d3a
SQ relation: add left joins to GSIEBSATZ and SSIEBUNG

flys-artifacts/trunk@4960 c6561f87-3c4e-4783-a992-168aeb5c3f6f

diff -r b52c4b34ec1b -r e06036fdf0c7 flys-artifacts/ChangeLog
--- a/flys-artifacts/ChangeLog	Thu Jul 12 20:29:05 2012 +0000
+++ b/flys-artifacts/ChangeLog	Thu Jul 12 22:01:52 2012 +0000
@@ -1,3 +1,8 @@
+2012-07-12	Sascha L. Teichmann	<sascha.teichmann@intevation.de>
+
+	* src/main/java/de/intevation/flys/artifacts/model/sq/MeasurementFactory.java:
+	  Added the left joins to the GSIEBSATZ and SSIEBUNG tables.
+
 2012-07-12	Sascha L. Teichmann	<sascha.teichmann@intevation.de>
 
 	* src/main/java/de/intevation/flys/artifacts/model/sq/MeasurementFactory.java:
diff -r b52c4b34ec1b -r e06036fdf0c7 flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/sq/MeasurementFactory.java
--- a/flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/sq/MeasurementFactory.java	Thu Jul 12 20:29:05 2012 +0000
+++ b/flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/sq/MeasurementFactory.java	Thu Jul 12 22:01:52 2012 +0000
@@ -21,21 +21,60 @@
     private static final Logger log =
         Logger.getLogger(MeasurementFactory.class);
 
+    public static final String [] GSIEBSATZ = {
+        "SIEB01", "SIEB02", "SIEB03", "SIEB04",
+        "SIEB05", "SIEB06", "SIEB07", "SIEB08",
+        "SIEB09", "SIEB10", "SIEB11", "SIEB13",
+        "SIEB13", "SIEB14", "SIEB15", "SIEB16",
+        "SIEB17", "SIEB18", "SIEB19", "SIEB20",
+        "SIEB21"
+    };
+
+    public static final String [] SSIEBUNG = {
+        "RSIEB01", "RSIEB02", "RSIEB03", "RSIEB04",
+        "RSIEB05", "RSIEB06", "RSIEB07", "RSIEB08",
+        "RSIEB09", "RSIEB10", "RSIEB11", "RSIEB13",
+        "RSIEB13", "RSIEB14", "RSIEB15", "RSIEB16",
+        "RSIEB17", "RSIEB18", "RSIEB19", "RSIEB20",
+        "RSIEB21", "REST"
+    };
+
     public static final String SQL_MEASSURE =
         "SELECT " +
             "m.TSAND   AS TSAND,"   +
             "m.TSCHWEB AS TSCHWEB," +
-            "m.CSCHWEB AS CSCHWEB " +
-            "m.Q       AS Q " +
-            /* TODO: Select more */
+            "m.CSCHWEB AS CSCHWEB," +
+            "m.Q       AS Q," +
+            "%GSIEBSATZ%" +
+            "%SSIEBUNG%" +
         "FROM messung m " +
             "JOIN station   s ON m.stationid   = s.stationid " +
             "JOIN gewaesser g ON s.gewaesserid = g.gewaesserid " +
+            "LEFT JOIN GSIEBSATZ gs ON m.gsiebsatzid = gs.gewaesserid " +
+            "LEFT JOIN SSIEBUNG  ss ON gs.gsiebsatzid = ss.gewaesserid " +
             "WHERE " +
                 "g.name = :river_name AND " +
                 "s.km BETWEEN :location - 0.001 AND :location + 0.001 AND " +
                 "m.datum BETWEEN :from AND :to AND " +
-                "m.Q IS NOT NULL";
+                "m.Q IS NOT NULL"
+        .replace("%GSIEBSATZ%", projection("gs", GSIEBSATZ))
+        .replace("%SSIEBUNG%",  projection("ss", SSIEBUNG));
+
+    private static final String projection(
+        String    prefix,
+        String [] columnNames
+    ) {
+        StringBuilder sb = new StringBuilder();
+        for (String columnName: columnNames) {
+            sb.append(prefix)
+              .append('.')
+              .append(columnName)
+              .append(" AS ")
+              .append(columnName)
+              .append(',');
+        }
+        return sb.toString();
+    }
 
     public static final class MeasurementResultTransformer
     extends                   BasicTransformerAdapter
@@ -73,6 +112,14 @@
             .addScalar("CSCHWEB", StandardBasicTypes.DOUBLE)
             .addScalar("Q",       StandardBasicTypes.DOUBLE);
 
+        for (String siebsatz: GSIEBSATZ) {
+            query.addScalar(siebsatz, StandardBasicTypes.DOUBLE);
+        }
+
+        for (String siebung: SSIEBUNG) {
+            query.addScalar(siebung, StandardBasicTypes.DOUBLE);
+        }
+
         query.setString("river_name", river);
         query.setDouble("location", location);
         query.setDate("from", dateRange.getFrom());