--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/artifacts/src/main/java/org/dive4elements/river/artifacts/model/minfo/QualityMeasurementFactory.java	Thu Apr 25 15:23:37 2013 +0200
@@ -0,0 +1,204 @@
+package org.dive4elements.river.artifacts.model.minfo;
+
+import java.util.Date;
+import java.util.HashMap;
+import java.util.Map;
+
+import org.apache.log4j.Logger;
+import org.hibernate.SQLQuery;
+import org.hibernate.Session;
+import org.hibernate.transform.BasicTransformerAdapter;
+import org.hibernate.type.StandardBasicTypes;
+
+import org.dive4elements.river.backend.SedDBSessionHolder;
+
+
+public class QualityMeasurementFactory {
+
+    private static Logger logger = Logger.getLogger(QualityMeasurementFactory.class);
+
+    private static final String SQL_BED_MEASUREMENT =
+        "SELECT st.km       as km," +
+        "       st.datum    as datum," +
+        "       sp.tiefevon as depth1," +
+        "       sp.tiefebis as depth2," +
+        "       sa.d10      as d10," +
+        "       sa.d16      as d16," +
+        "       sa.d20      as d20," +
+        "       sa.d25      as d25," +
+        "       sa.d30      as d30," +
+        "       sa.d40      as d40," +
+        "       sa.d50      as d50," +
+        "       sa.d60      as d60," +
+        "       sa.d70      as d70," +
+        "       sa.d75      as d75," +
+        "       sa.d80      as d80," +
+        "       sa.d84      as d84," +
+        "       sa.d90      as d90," +
+        "       sa.dmin     as dmin," +
+        "       sa.dmax     as dmax " +
+        "FROM sohltest st " +
+        "    JOIN station sn ON sn.stationid = st.stationid " +
+        "    JOIN gewaesser gw ON gw.gewaesserid = sn.gewaesserid " +
+        "    JOIN sohlprobe sp ON sp.sohltestid = st.sohltestid " +
+        "    JOIN siebanalyse sa ON sa.sohlprobeid = sp.sohlprobeid " +
+        "WHERE gw.name = :name AND " +
+        "      st.km IS NOT NULL AND " +
+        "      sp.tiefevon IS NOT NULL AND " +
+        "      sp.tiefebis IS NOT NULL AND " + // TODO: Test if char diameter ist null.
+        "      st.km BETWEEN :from - 0.001 AND :to + 0.001 AND " +
+        "      st.datum BETWEEN :start AND :end";
+
+    private static final String SQL_BEDLOAD_MEASUREMENT =
+        "SELECT m.km    as km," +
+        "       m.datum as datum," +
+        "       m.d10   as d10," +
+        "       m.d16   as d16," +
+        "       m.d20   as d20," +
+        "       m.d25   as d25," +
+        "       m.d30   as d30," +
+        "       m.d40   as d40," +
+        "       m.d50   as d50," +
+        "       m.d60   as d60," +
+        "       m.d70   as d70," +
+        "       m.d75   as d75," +
+        "       m.d80   as d80," +
+        "       m.d84   as d84," +
+        "       m.d90   as d90," +
+        "       m.dmin  as dmin," +
+        "       m.dmax  as dmax " +
+        "FROM messung m" +
+        "    JOIN station sn ON sn.stationid = m.stationid" +
+        "    JOIN gewaesser gw ON gw.gewaesserid = sn.gewaesserid " +
+        "WHERE gw.name = :name AND " +
+        "      m.km IS NOT NULL AND " +
+        "      m.d10 IS NOT NULL AND" + //TODO: Add all other char. diameter.
+        "      m.km BETWEEN :from - 0.001 AND :to + 0.001 AND" +
+        "      m.datum BETWEEN :start AND :end";
+
+    public static final class QualityMeasurementResultTransformer
+    extends BasicTransformerAdapter {
+
+        public static QualityMeasurementResultTransformer INSTANCE = new QualityMeasurementResultTransformer();
+
+        public QualityMeasurementResultTransformer() {
+        }
+
+        @Override
+        public Object transformTuple(Object[] tuple, String[] aliases) {
+            Map<String, Double> map = new HashMap<String, Double>();
+            double km = 0;
+            Date d = null;
+            double depth1 = Double.NaN;
+            double depth2 = Double.NaN;
+            for (int i = 0; i < tuple.length; ++i) {
+                if (tuple[i] != null) {
+                    if (aliases[i].equals("km")) {
+                        km = ((Number) tuple[i]).doubleValue();
+                    }
+                    else if (aliases[i].equals("datum")) {
+                        d = (Date) tuple[i];
+                    }
+                    else if (aliases[i].equals("depth1")) {
+                        depth1 = ((Number) tuple[i]).doubleValue();
+                    }
+                    else if (aliases[i].equals("depth2")) {
+                        depth2 = ((Number) tuple[i]).doubleValue();
+                    }
+                    else {
+                        map.put(aliases[i], ((Double) tuple[i])/1000);
+                    }
+                }
+            }
+            return new QualityMeasurement(km, d, depth1, depth2, map);
+        }
+    } // class BasicTransformerAdapter
+
+    private QualityMeasurementFactory() {
+    }
+
+    protected static QualityMeasurements load(
+        Session session,
+        String river,
+        double from,
+        double to,
+        Date start,
+        Date end,
+        String statement
+    ) {
+        SQLQuery query = session.createSQLQuery(statement)
+            .addScalar("km", StandardBasicTypes.DOUBLE)
+            .addScalar("datum", StandardBasicTypes.DATE)
+            .addScalar("d10", StandardBasicTypes.DOUBLE)
+            .addScalar("d16", StandardBasicTypes.DOUBLE)
+            .addScalar("d20", StandardBasicTypes.DOUBLE)
+            .addScalar("d25", StandardBasicTypes.DOUBLE)
+            .addScalar("d30", StandardBasicTypes.DOUBLE)
+            .addScalar("d40", StandardBasicTypes.DOUBLE)
+            .addScalar("d50", StandardBasicTypes.DOUBLE)
+            .addScalar("d60", StandardBasicTypes.DOUBLE)
+            .addScalar("d70", StandardBasicTypes.DOUBLE)
+            .addScalar("d75", StandardBasicTypes.DOUBLE)
+            .addScalar("d80", StandardBasicTypes.DOUBLE)
+            .addScalar("d84", StandardBasicTypes.DOUBLE)
+            .addScalar("d90", StandardBasicTypes.DOUBLE)
+            .addScalar("dmin", StandardBasicTypes.DOUBLE)
+            .addScalar("dmax", StandardBasicTypes.DOUBLE);
+
+        if (statement.equals(SQL_BED_MEASUREMENT)) {
+            query.addScalar("depth1", StandardBasicTypes.DOUBLE);
+            query.addScalar("depth2", StandardBasicTypes.DOUBLE);
+        }
+
+        query.setString("name", river);
+        query.setDouble("from", from);
+        query.setDouble("to", to);
+        query.setDate("start", start);
+        query.setDate("end", end);
+
+        query.setResultTransformer(
+            QualityMeasurementResultTransformer.INSTANCE);
+
+        return new QualityMeasurements(query.list());
+    }
+
+    public static QualityMeasurements getBedMeasurements(
+        String river,
+        double from,
+        double to,
+        Date start,
+        Date end) {
+        Session session = SedDBSessionHolder.HOLDER.get();
+        try {
+            return load(session, river, from, to, start, end,
+            SQL_BED_MEASUREMENT);
+        }
+        finally {
+            //session.close();
+        }
+    }
+
+    public static QualityMeasurements getBedloadMeasurements(
+        String river,
+        double from,
+        double to,
+        Date start,
+        Date end
+    ) {
+        Session session = SedDBSessionHolder.HOLDER.get();
+        try {
+            return load(
+                session,
+                river,
+                from,
+                to,
+                start,
+                end,
+                SQL_BEDLOAD_MEASUREMENT);
+        }
+        finally {
+            //session.close();
+        }
+    }
+}
+// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :