--- a/artifacts/src/main/java/org/dive4elements/river/artifacts/model/minfo/SedimentLoadFactory.java	Fri Jun 21 15:33:06 2013 +0200
+++ b/artifacts/src/main/java/org/dive4elements/river/artifacts/model/minfo/SedimentLoadFactory.java	Fri Jun 21 15:35:44 2013 +0200
@@ -13,25 +13,21 @@
 import java.util.Calendar;
 import java.util.Date;
 import java.util.List;
-import java.util.Map;
 import java.util.TreeMap;
 
 import net.sf.ehcache.Cache;
 import net.sf.ehcache.Element;
 
 import org.apache.log4j.Logger;
-import org.hibernate.SQLQuery;
-import org.hibernate.Session;
-import org.hibernate.type.StandardBasicTypes;
-
-import org.dive4elements.river.model.MeasurementStation;
-
 import org.dive4elements.river.artifacts.cache.CacheFactory;
-
 import org.dive4elements.river.artifacts.model.Range;
 import org.dive4elements.river.artifacts.model.RiverFactory;
 import org.dive4elements.river.artifacts.model.StaticSedimentLoadCacheKey;
 import org.dive4elements.river.backend.SessionHolder;
+import org.dive4elements.river.model.MeasurementStation;
+import org.hibernate.SQLQuery;
+import org.hibernate.Session;
+import org.hibernate.type.StandardBasicTypes;
 
 /** Pull Sediment Loads out of db. */
 public class SedimentLoadFactory
@@ -60,11 +56,13 @@
         "SELECT DISTINCT " +
         "       sy.description AS description, " +
         "       ti.start_time AS start, " +
-        "       ti.stop_time AS end " +
+        "       ti.stop_time AS end, " +
+        "       u.name AS unit" +
         "   FROM     sediment_yield sy " +
         "       JOIN rivers r ON sy.river_id = r.id " +
         "       JOIN sediment_yield_values syv ON sy.id = syv.sediment_yield_id " +
         "       JOIN time_intervals ti ON sy.time_interval_id = ti.id " +
+        "       JOIN units u ON u.id = sy.unit_id " +
         "   WHERE   r.name = :name " +
         "       AND ti.stop_time IS NOT NULL " +
         "       AND syv.station BETWEEN :startKm AND :endKm";
@@ -74,17 +72,51 @@
         "       sy.description AS description, " +
         "       ti.start_time AS year, " +
         "       syv.value AS load, " +
-        "       syv.station AS km " +
+        "       syv.station AS km, " +
+        "       u.name AS unit " +
         "   FROM     sediment_yield sy " +
         "       JOIN rivers r ON sy.river_id = r.id " +
         "       JOIN time_intervals ti ON sy.time_interval_id = ti.id " +
         "       JOIN sediment_yield_values syv ON sy.id = syv.sediment_yield_id " +
         "       JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id " +
+        "       JOIN units u ON u.id = sy.unit_id" +
         "   WHERE   r.name = :name " +
         "       AND ti.start_time BETWEEN :begin AND :end " +
         "       AND ti.stop_time IS NULL " +
         "       AND gf.name = :grain " +
-        "       AND syv.station BETWEEN :startKm AND :endKm";
+        "       AND syv.station BETWEEN :startKm AND :endKm " +
+        "   ORDER BY syv.station";
+
+    public static final String SQL_SELECT_UNKNOWN_DATA =
+        "SELECT" +
+        "       sy.description AS description, " +
+        "       ti.start_time AS start, " +
+        "       ti.stop_time AS end, " +
+        "       syv.value AS load, " +
+        "       syv.station AS km, " +
+        "       u.name AS unit " +
+        "   FROM     sediment_yield sy " +
+        "       JOIN rivers r ON sy.river_id = r.id " +
+        "       JOIN time_intervals ti ON sy.time_interval_id = ti.id " +
+        "       JOIN sediment_yield_values syv ON sy.id = syv.sediment_yield_id " +
+        "       JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id " +
+        "       JOIN units u ON u.id = sy.unit_id" +
+        "   WHERE   r.name = :name " +
+        "       AND sy.description = :descr " +
+        "       AND gf.name = 'unknown' " +
+        "   ORDER BY syv.station";
+
+    public static final String SQL_SELECT_UNKNOWN =
+        "SELECT DISTINCT " +
+        "    sy.description AS description, " +
+        "    ti.start_time AS start, " +
+        "    ti.stop_time AS end " +
+        "FROM sediment_yield sy " +
+        "    JOIN rivers r ON sy.river_id = r.id " +
+        "    JOIN time_intervals ti ON sy.time_interval_id = ti.id " +
+        "    JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id " +
+        "WHERE r.name = :river" +
+        "    AND gf.name = 'unknown'";
 
     public static final String SQL_SELECT_EPOCHS_DATA =
         "SELECT" +
@@ -92,17 +124,20 @@
         "       ti.start_time AS startYear, " +
         "       syv.value AS load, " +
         "       syv.station AS km," +
-        "       ti.stop_time AS endYear " +
+        "       ti.stop_time AS endYear, " +
+        "       u.name AS unit" +
         "   FROM     sediment_yield sy " +
         "       JOIN rivers r ON sy.river_id = r.id " +
         "       JOIN time_intervals ti ON sy.time_interval_id = ti.id " +
         "       JOIN sediment_yield_values syv ON sy.id = syv.sediment_yield_id " +
         "       JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id " +
+        "       JOIN units u ON u.id = sy.unit_id " +
         "   WHERE   r.name = :name " +
         "       AND ti.start_time BETWEEN :sbegin AND :send " +
         "       AND ti.stop_time BETWEEN :ebegin AND :eend " +
         "       AND gf.name = :grain " +
-        "       AND syv.station BETWEEN :startKm AND :endKm";
+        "       AND syv.station BETWEEN :startKm AND :endKm " +
+        "   ORDER BY syv.station";
 
     private SedimentLoadFactory() {
     }
@@ -225,7 +260,8 @@
                     (String) row[0],
                     (Date) row[1],
                     null,
-                    false);
+                    false,
+                    "");
             }
             return loads;
         }
@@ -246,7 +282,8 @@
                     (String) row[0],
                     (Date) row[1],
                     (Date) row[2],
-                    true);
+                    true,
+                    "");
             }
             return loads;
         }
@@ -297,7 +334,8 @@
                 .addScalar("description", StandardBasicTypes.STRING)
                 .addScalar("year", StandardBasicTypes.DATE)
                 .addScalar("load", StandardBasicTypes.DOUBLE)
-                .addScalar("km", StandardBasicTypes.DOUBLE);
+                .addScalar("km", StandardBasicTypes.DOUBLE)
+                .addScalar("unit", StandardBasicTypes.STRING);
             sqlQuery.setString("name", river);
             sqlQuery.setDouble("startKm", startKm);
             sqlQuery.setDouble("endKm", endKm);
@@ -311,7 +349,8 @@
                     (String) row[0],
                     (Date) row[1],
                     null,
-                    false);
+                    false,
+                    (String) row[4]);
             getValues("coarse", sqlQuery, load, floatStations);
             getValues("fine_middle", sqlQuery, load, floatStations);
             getValues("sand", sqlQuery, load, floatStations);
@@ -331,7 +370,8 @@
                 .addScalar("startYear", StandardBasicTypes.DATE)
                 .addScalar("load", StandardBasicTypes.DOUBLE)
                 .addScalar("km", StandardBasicTypes.DOUBLE)
-                .addScalar("endYear", StandardBasicTypes.DATE);
+                .addScalar("endYear", StandardBasicTypes.DATE)
+                .addScalar("unit", StandardBasicTypes.STRING);
             sqlQuery.setString("name", river);
             sqlQuery.setDouble("startKm", startKm);
             sqlQuery.setDouble("endKm", endKm);
@@ -350,7 +390,8 @@
                     (String) row[0],
                     (Date) row[1],
                     (Date) row[4],
-                    true);
+                    true,
+                    (String)row[5]);
             TDoubleArrayList kms = new TDoubleArrayList();
             for (int i = 0; i < results.size(); i++) {
                 row = results.get(i);
@@ -430,5 +471,100 @@
             }
         }
     }
+
+    public static SedimentLoad getLoadUnknown(
+        String river,
+        String description
+    ) {
+        log.debug("SedimentLoadFactory.getLoadWithData");
+        Cache cache = CacheFactory.getCache(LOAD_DATA_CACHE_NAME);
+
+        if (cache == null) {
+            log.debug("Cache not configured.");
+            return getSedimentLoadUnknownUncached(river, description);
+        }
+
+        StaticSedimentLoadCacheKey key =
+            new StaticSedimentLoadCacheKey(river, 0d, 0d, 0, 0);
+
+        Element element = cache.get(key);
+
+        if (element != null) {
+            log.debug("SedimentLoad found in cache");
+            return (SedimentLoad)element.getValue();
+        }
+
+        SedimentLoad values = getSedimentLoadUnknownUncached(river, description);
+
+        if (values != null && key != null) {
+            log.debug("Store static bed height values in cache.");
+            element = new Element(key, values);
+            cache.put(element);
+        }
+        return values;
+    }
+
+    /**
+     * Get sediment loads with fraction 'unknown' from db.
+     * @param river the river
+     * @param type the sediment load type (year or epoch)
+     * @return according sediment loads.
+     */
+    public static SedimentLoad getSedimentLoadUnknownUncached(
+        String river,
+        String description
+    ) {
+        log.debug("SedimentLoadFactory.getSedimentLoadWithDataUncached");
+        Session session = SessionHolder.HOLDER.get();
+        SQLQuery sqlQuery = null;
+
+        sqlQuery = session.createSQLQuery(SQL_SELECT_UNKNOWN_DATA)
+            .addScalar("description", StandardBasicTypes.STRING)
+            .addScalar("start", StandardBasicTypes.DATE)
+            .addScalar("end", StandardBasicTypes.DATE)
+            .addScalar("load", StandardBasicTypes.DOUBLE)
+            .addScalar("km", StandardBasicTypes.DOUBLE)
+            .addScalar("unit", StandardBasicTypes.STRING);
+        sqlQuery.setString("name", river);
+        sqlQuery.setString("descr", description);
+        List<Object []> results = sqlQuery.list();
+        SedimentLoad load = new SedimentLoad();
+        if (results.isEmpty()) {
+            return new SedimentLoad();
+        }
+        Object[] row = results.get(0);
+        load = new SedimentLoad(
+            (String) row[0],
+            (Date) row[1],
+            (Date) row[2],
+            false,
+            (String)row[5]);
+
+        for (int i = 0; i < results.size(); i++) {
+            row = results.get(i);
+            SedimentLoadFraction fraction = new SedimentLoadFraction();
+            fraction.setUnknown((Double)row[3]);
+            load.addKm((Double)row[4], fraction);
+        }
+        return load;
+    }
+
+    public static SedimentLoad[] getSedimentLoadUnknown(String river) {
+        Session session = SessionHolder.HOLDER.get();
+        SQLQuery sqlQuery = session.createSQLQuery(SQL_SELECT_UNKNOWN)
+            .addScalar("description", StandardBasicTypes.STRING)
+            .addScalar("start", StandardBasicTypes.DATE)
+            .addScalar("end", StandardBasicTypes.DATE);
+        sqlQuery.setString("river", river);
+        List<Object[]> results = sqlQuery.list();
+        SedimentLoad[] loads = new SedimentLoad[results.size()];
+        int counter = 0;
+        for (Object[] row: results) {
+            loads[counter] = new SedimentLoad(
+                (String)row[0], (Date)row[1], (Date)row[2], false, "");
+            counter++;
+        }
+        return loads;
+    }
 }
 // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf-8 :