--- a/flys-artifacts/ChangeLog	Mon Oct 01 23:05:21 2012 +0200
+++ b/flys-artifacts/ChangeLog	Tue Oct 02 00:00:30 2012 +0200
@@ -1,3 +1,9 @@
+2012-10-01	Sascha L. Teichmann	<sascha.teichmann@intevation.de>
+
+	* src/main/java/de/intevation/flys/artifacts/model/sq/MeasurementFactory.java:
+	  Totals are now fetched with separate SQL statement. Fixed join to
+	  GEWAESSER table.
+
 2012-10-01	Sascha L. Teichmann	<sascha.teichmann@intevation.de>
 
 	* 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	Mon Oct 01 23:05:21 2012 +0200
+++ b/flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/sq/MeasurementFactory.java	Tue Oct 02 00:00:30 2012 +0200
@@ -4,18 +4,11 @@
 import java.util.Calendar;
 import java.util.Collections;
 import java.util.Date;
+import java.util.HashMap;
 import java.util.List;
+import java.util.Map;
 import java.util.TreeMap;
 
-import de.intevation.flys.artifacts.model.DateRange;
-
-import de.intevation.flys.backend.SedDBSessionHolder;
-
-import java.util.HashMap;
-import java.util.Map;
-
-import org.apache.log4j.Logger;
-
 import org.hibernate.SQLQuery;
 import org.hibernate.Session;
 
@@ -23,13 +16,31 @@
 
 import org.hibernate.type.StandardBasicTypes;
 
+import de.intevation.flys.artifacts.model.DateRange;
+
+import de.intevation.flys.backend.SedDBSessionHolder;
+
 public class MeasurementFactory
 {
-    private static final Logger log =
-        Logger.getLogger(MeasurementFactory.class);
+    public static final String SQL_TOTALS =
+        "SELECT " +
+            "m.Q_BPEGEL AS Q_BPEGEL,"+
+            "m.TSCHWEB  AS TSCHWEB," +
+            "m.TSAND    AS TSAND" +
+        "FROM MESSUNG m " +
+            "JOIN STATION   s ON m.STATIONID   = s.STATIONID " +
+            "JOIN GEWAESSER r ON s.GEWAESSERID = r.GEWAESSERID " +
+        "WHERE " +
+            "r.NAME = :river_name " +
+            "AND m.Q_BPEGEL IS NOT NULL " +
+            "AND s.KM BETWEEN :location - 0.001 AND :location + 0.001 " +
+            "AND m.DATUM BETWEEN :from AND :to " +
+            "AND m.DATUM IS NOT NULL";
 
     public static final String SQL_FACTIONS =
-        "SELECT m.datum    AS DATUM," +
+        "SELECT " +
+            "m.datum        AS DATUM," +
+            "m.Q_BPEGEL     AS Q_BPEGEL,"+
             "g.GLOTRECHTEID AS GLOTRECHTEID," +
             "gp.LFDNR       AS LFDNR," +
             "g.UFERABST     AS UFERABST," +
@@ -62,11 +73,12 @@
             "gs.RSIEB21 AS RSIEB21, gs.REST    AS REST " +
         "FROM MESSUNG m " +
             "JOIN STATION    s ON m.STATIONID    = s.STATIONID " +
+            "JOIN GEWAESSER  r ON s.GEWAESSERID  = r.GEWAESSERID " +
             "JOIN glotrechte g ON m.MESSUNGID    = g.MESSUNGID " +
             "JOIN gprobe    gp ON g.GLOTRECHTEID = gp.GLOTRECHTEID " +
             "JOIN GSIEBUNG  gs ON g.GLOTRECHTEID = gs.GLOTRECHTEID " +
         "WHERE " +
-            "g.NAME = :river_name " +
+            "r.NAME = :river_name " +
             "AND m.Q_BPEGEL IS NOT NULL " +
             "AND s.KM BETWEEN :location - 0.001 AND :location + 0.001 " +
             "AND m.DATUM BETWEEN :from AND :to " +
@@ -84,7 +96,25 @@
                 "COALESCE(gs.RSIEB17, 0) + COALESCE(gs.RSIEB18, 0) +" +
                 "COALESCE(gs.RSIEB19, 0) + COALESCE(gs.RSIEB20, 0) +" +
                 "COALESCE(gs.RSIEB21, 0) + COALESCE(gs.REST, 0)) > 0 " +
-        "ORDER BY m.DATUM, g.UFERABST, g.GLOTRECHTEID, gp.LFDNR";
+        "ORDER BY " +
+            "m.DATUM, g.UFERABST, g.GLOTRECHTEID, gp.LFDNR";
+
+    public static final BasicTransformerAdapter TOTALS_TRANSFORMER =
+        new BasicTransformerAdapter() {
+			private static final long serialVersionUID = 1L;
+
+			@Override
+            public Object transformTuple(Object [] tuple, String [] aliases) {
+                Map<String, Object> map = new HashMap<String, Object>();
+                for (int i = 0; i < tuple.length; ++i) {
+                    Object value = tuple[i];
+                    if (value != null) {
+                        map.put(aliases[i], value);
+                    }
+                }
+                return new Measurement(map, Collections.<Sieve>emptyList());
+            }
+        };
 
     private static final int index(String s) {
         return Integer.parseInt(s.substring(s.length()-2))-1;
@@ -92,6 +122,7 @@
 
     public static final BasicTransformerAdapter FRACTIONS_TRANSFORMER =
         new BasicTransformerAdapter() {
+			private static final long serialVersionUID = 1L;
 
             @Override
             public Object transformTuple(Object [] tuple, String [] aliases) {
@@ -134,18 +165,55 @@
     private MeasurementFactory() {
     }
 
-    protected static Measurements load(
+    public static Measurements getMeasurements(
+        String    river,
+        double    location,
+        DateRange dateRange
+    ) {
+        Session session = SedDBSessionHolder.HOLDER.get();
+        try {
+            List<Measurement> totals = loadTotals(
+                session, river, location, dateRange);
+
+            List<Measurement> accumulated = loadFractions(
+                session, river, location, dateRange);
+
+            return new Measurements(totals, accumulated);
+        }
+        finally {
+            session.close();
+        }
+    }
+
+    protected static List<Measurement> loadTotals(
         Session   session,
         String    river,
         double    location,
         DateRange dateRange
     ) {
-        boolean debug = log.isDebugEnabled();
+        SQLQuery query = session.createSQLQuery(SQL_TOTALS)
+            .addScalar("Q_BPEGEL", StandardBasicTypes.DOUBLE)
+            .addScalar("TSCHWEB",  StandardBasicTypes.DOUBLE)
+            .addScalar("TSAND",    StandardBasicTypes.DOUBLE);
 
-        if (debug) {
-            log.debug(SQL_FACTIONS);
-        }
+        query.setString("river_name", river);
+        query.setDouble("location", location);
+        query.setDate("from", dateRange.getFrom());
+        query.setDate("to", dateRange.getTo());
 
+        query.setResultTransformer(TOTALS_TRANSFORMER);
+
+        @SuppressWarnings("unchecked")
+        List<Measurement> result = (List<Measurement>)query.list();
+        return result;
+    }
+
+    protected static List<Measurement> loadFractions(
+        Session   session,
+        String    river,
+        double    location,
+        DateRange dateRange
+    ) {
         SQLQuery query = session.createSQLQuery(SQL_FACTIONS)
             .addScalar("Q_BPEGEL",     StandardBasicTypes.DOUBLE)
             .addScalar("DATUM",        StandardBasicTypes.DATE)
@@ -240,7 +308,7 @@
             m.adjustSieves();
         }
 
-        return new Measurements(measuments, separateByDate(accumulated));
+        return separateByDate(accumulated);
     }
 
     protected static List<Measurement> separateByDate(List<Measurement> measurements) {
@@ -384,19 +452,5 @@
 
         return new Measurement(data, accumulatedSieves);
     }
-
-    public static Measurements getMeasurements(
-        String    river,
-        double    location,
-        DateRange dateRange
-    ) {
-        Session session = SedDBSessionHolder.HOLDER.get();
-        try {
-            return load(session, river, location, dateRange);
-        }
-        finally {
-            session.close();
-        }
-    }
 }
 // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf-8 :