--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/StaticSedimentLoadCacheKey.java	Mon Oct 29 12:14:50 2012 +0100
@@ -0,0 +1,48 @@
+package de.intevation.flys.artifacts.model;
+
+import java.util.Date;
+
+import org.apache.commons.lang.builder.HashCodeBuilder;
+
+
+public class StaticSedimentLoadCacheKey
+{
+    public static final String CACHE_NAME = "sedimentload-value-table-static";
+
+    private String river;
+    private double startKm;
+    private double endKm;
+    private Date date;
+
+    public StaticSedimentLoadCacheKey(
+        String river,
+        double startKm,
+        double endKm,
+        Date date
+    ) {
+        this.river = river;
+        this.startKm = startKm;
+        this.endKm = endKm;
+        this.date = date;
+    }
+
+    public int hashCode() {
+        HashCodeBuilder builder = new HashCodeBuilder();
+        builder.append(river);
+        builder.append(startKm);
+        builder.append(endKm);
+        builder.append(date);
+        return builder.toHashCode();
+    }
+
+    public boolean equals(Object other) {
+        if (!(other instanceof StaticBedHeightCacheKey)) {
+            return false;
+        }
+        StaticSedimentLoadCacheKey o = (StaticSedimentLoadCacheKey) other;
+        return this.river == o.river &&
+            this.startKm == o.startKm &&
+            this.endKm == o.endKm &&
+            this.date == o.date;
+    }
+}

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/minfo/SedimentLoad.java	Mon Oct 29 12:14:50 2012 +0100
@@ -0,0 +1,135 @@
+package de.intevation.flys.artifacts.model.minfo;
+
+import gnu.trove.TDoubleArrayList;
+
+import java.util.Date;
+
+import org.apache.log4j.Logger;
+
+import de.intevation.flys.artifacts.model.NamedObjectImpl;
+
+
+public class SedimentLoad
+extends NamedObjectImpl
+{
+    private static Logger log = Logger.getLogger(BedHeight.class);
+
+    protected String description;
+    protected Date start;
+    protected Date end;
+    protected boolean isEpoch;
+
+    protected TDoubleArrayList sand_values;
+    protected TDoubleArrayList fine_middle_values;
+    protected TDoubleArrayList coarse_values;
+    protected TDoubleArrayList susp_sediment_values;
+    protected TDoubleArrayList susp_sand_bed_values;
+
+
+    public SedimentLoad() {
+    }
+
+    public SedimentLoad(
+        String description,
+        Date start,
+        Date end,
+        boolean isEpoch
+    ) {
+        this.description = description;
+        this.start = start;
+        this.end = end;
+        this.isEpoch = isEpoch;
+    }
+
+    public String getDescription() {
+        return description;
+    }
+
+    public void setDescription(String description) {
+        this.description = description;
+    }
+
+    public Date getStart() {
+        return start;
+    }
+
+    public void setStart(Date start) {
+        this.start = start;
+    }
+
+    public Date getEnd() {
+        return end;
+    }
+
+    public void setEnd(Date end) {
+        this.end = end;
+    }
+
+    public boolean isEpoch() {
+        return isEpoch;
+    }
+
+    public void setEpoch(boolean isEpoch) {
+        this.isEpoch = isEpoch;
+    }
+
+    public void addSandValue(double value) {
+        this.sand_values.add(value);
+    }
+
+    public void addSandValues(TDoubleArrayList values) {
+        this.sand_values.add(values.toNativeArray());
+    }
+
+    public TDoubleArrayList getSandValues() {
+        return this.sand_values;
+    }
+
+    public void addFineMiddleValue(double value) {
+        this.fine_middle_values.add(value);
+    }
+
+    public void addFineMiddleValues(TDoubleArrayList values) {
+        this.fine_middle_values.add(values.toNativeArray());
+    }
+
+    public TDoubleArrayList getFineMiddleValues() {
+        return this.fine_middle_values;
+    }
+
+    public void addCoarseValue(double value) {
+        this.coarse_values.add(value);
+    }
+
+    public void addCoarseValues(TDoubleArrayList values) {
+        this.coarse_values.add(values.toNativeArray());
+    }
+
+    public TDoubleArrayList getCoarseValues() {
+        return this.coarse_values;
+    }
+
+    public void addSuspSedimentValue(double value) {
+        this.susp_sediment_values.add(value);
+    }
+
+    public void addSuspSedimentValues(TDoubleArrayList values) {
+        this.susp_sediment_values.add(values.toNativeArray());
+    }
+
+   public TDoubleArrayList getSuspSedimentValues() {
+        return this.susp_sediment_values;
+    }
+
+    public void addSuspSandBedValue(double value) {
+        this.susp_sand_bed_values.add(value);
+    }
+
+    public void addSuspSandBedValues(TDoubleArrayList values) {
+        this.susp_sand_bed_values.add(values.toNativeArray());
+    }
+
+    public TDoubleArrayList getSuspSandBedValues() {
+        return this.susp_sand_bed_values;
+    }
+}

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/minfo/SedimentLoadFactory.java	Mon Oct 29 12:14:50 2012 +0100
@@ -0,0 +1,352 @@
+package de.intevation.flys.artifacts.model.minfo;
+
+import gnu.trove.TDoubleArrayList;
+
+import java.util.Calendar;
+import java.util.Date;
+import java.util.List;
+
+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 de.intevation.flys.artifacts.cache.CacheFactory;
+import de.intevation.flys.artifacts.model.StaticSedimentLoadCacheKey;
+import de.intevation.flys.backend.SessionHolder;
+
+
+public class SedimentLoadFactory
+{
+    /** Private logger to use here. */
+    private static Logger log = Logger.getLogger(SedimentLoadFactory.class);
+
+    public static final String LOADS_CACHE_NAME = "sedimentloads";
+    public static final String LOAD_DATA_CACHE_NAME = "sedimentload-data";
+
+    /** Query to get km and ws for wst_id and column_pos. */
+    public static final String SQL_SELECT_SINGLES =
+        "SELECT DISTINCT " +
+        "       sy.description AS description, " +
+        "       ti.start_time AS year " +
+        "   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 " +
+        "   WHERE   r.name = :name " +
+        "       AND ti.stop_time IS NULL " +
+        "       AND syv.station BETWEEN :startKm AND :endKm";
+
+    /** Query to get name for wst_id and column_pos. */
+    public static final String SQL_SELECT_EPOCHS =
+        "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 sediment_yield_values syv ON sy.id = syv.sediment_yield_id " +
+        "       JOIN time_intervals ti ON sy.time_interval_id = ti.id " +
+        "   WHERE   r.name = :name " +
+        "       AND ti.stop_time IS NOT NULL " +
+        "       AND syv.station BETWEEN :startKm AND :endKm";
+
+    public static final String SQL_SELECT_SINGLES_DATA =
+        "SELECT" +
+        "       sy.description AS description, " +
+        "       ti.start_time AS year, " +
+        "       syv.value AS load " +
+        "   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_vales syv ON sy.id = syv.sediment_yield_id " +
+        "       JOIN grain_fraction gf ON sy.grain_fraction_id = gf.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";
+
+    public static final String SQL_SELECT_EPOCHS_DATA =
+        "SELECT" +
+        "       sy.description AS description," +
+        "       ti.start_time AS year," +
+        "       syv.value AS load" +
+        "   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_vales syv ON sy.id = syv.sediment_yield_id" +
+        "       JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id" +
+        "   WHERE   r.name = :name" +
+        "       AND ti.start_time BETWEEN :sbegin AND :send" +
+        "       AND ti_stop_time IS NOT NULL" +
+        "       AND ti_stop_time BETWEEN :ebegin AND :eend" +
+        "       AND gf.name = :grain " +
+        "       AND syv.station BETWEEN :startKm AND :endKm";
+
+    private SedimentLoadFactory() {
+    }
+
+    /**
+     *
+     */
+    public static SedimentLoad[] getLoads(
+        String river,
+        String type,
+        double startKm,
+        double endKm
+    ) {
+        log.debug("SedimentLoadFactory.getLoads");
+        Cache cache = CacheFactory.getCache(LOADS_CACHE_NAME);
+
+        if (cache == null) {
+            log.debug("Cache not configured.");
+            return getSedimentLoadsUncached(river, type, startKm, endKm);
+        }
+
+        StaticSedimentLoadCacheKey key =
+            new StaticSedimentLoadCacheKey(river, startKm, endKm, null);
+
+        Element element = cache.get(key);
+
+        if (element != null) {
+            log.debug("SedimentLoad found in cache");
+            return (SedimentLoad[])element.getValue();
+        }
+
+        SedimentLoad[] values =
+            getSedimentLoadsUncached(river, type, startKm, endKm);
+
+        if (values != null && key != null) {
+            log.debug("Store static sediment loads  values in cache.");
+            element = new Element(key, values);
+            cache.put(element);
+        }
+        return values;
+    }
+
+    public static SedimentLoad getLoadwithData(
+        String river,
+        String type,
+        double startKm,
+        double endKm,
+        Date startDate,
+        Date endDate
+    ) {
+        log.debug("SedimentLoadFactory.getLoadWithData");
+        Cache cache = CacheFactory.getCache(LOAD_DATA_CACHE_NAME);
+
+        if (cache == null) {
+            log.debug("Cache not configured.");
+            return getSedimentLoadWithDataUncached(
+                river,
+                type,
+                startKm,
+                endKm,
+                startDate,
+                endDate);
+        }
+
+        StaticSedimentLoadCacheKey key =
+            new StaticSedimentLoadCacheKey(river, startKm, endKm, startDate);
+
+        Element element = cache.get(key);
+
+        if (element != null) {
+            log.debug("SedimentLoad found in cache");
+            return (SedimentLoad)element.getValue();
+        }
+
+        SedimentLoad values =
+            getSedimentLoadWithDataUncached(river, type, startKm, endKm, startDate, endDate);
+
+        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 from db.
+     * @param river the river
+     * @param type the sediment load type (year or epoch)
+     * @return according sediment loads.
+     */
+    public static SedimentLoad[] getSedimentLoadsUncached(
+        String river,
+        String type,
+        double startKm,
+        double endKm
+    ) {
+        log.debug("SedimentLoadFactory.getSedimentLoadsUncached");
+
+        Session session = SessionHolder.HOLDER.get();
+        SQLQuery sqlQuery = null;
+
+
+        if (type.equals("single")) {
+            sqlQuery = session.createSQLQuery(SQL_SELECT_SINGLES)
+                .addScalar("description", StandardBasicTypes.STRING)
+                .addScalar("year", StandardBasicTypes.DATE);
+            sqlQuery.setString("name", river);
+            sqlQuery.setDouble("startKm", startKm);
+            sqlQuery.setDouble("endKm", endKm);
+            List<Object []> results = sqlQuery.list();
+            SedimentLoad[] loads = new SedimentLoad[results.size()];
+            for (int i = 0; i < results.size(); i++) {
+                Object[] row = results.get(i);
+                loads[i] = new SedimentLoad(
+                    (String) row[0],
+                    (Date) row[1],
+                    null,
+                    false);
+            }
+            return loads;
+        }
+        else if (type.equals("epoch")) {
+            sqlQuery = session.createSQLQuery(SQL_SELECT_EPOCHS)
+                .addScalar("description", StandardBasicTypes.STRING)
+                .addScalar("start", StandardBasicTypes.DATE)
+                .addScalar("end", StandardBasicTypes.DATE);
+            sqlQuery.setString("name", river);
+            sqlQuery.setDouble("startKm", startKm);
+            sqlQuery.setDouble("endKm", endKm);
+            List<Object []> results = sqlQuery.list();
+
+            SedimentLoad[] loads = new SedimentLoad[results.size()];
+            for (int i = 0; i < results.size(); i++) {
+                Object[] row = results.get(i);
+                loads[i] = new SedimentLoad(
+                    (String) row[0],
+                    (Date) row[1],
+                    (Date) row[2],
+                    true);
+            }
+            return loads;
+        }
+        return new SedimentLoad[0];
+    }
+
+    /**
+     * Get sediment loads from db.
+     * @param river the river
+     * @param type the sediment load type (year or epoch)
+     * @return according sediment loads.
+     */
+    public static SedimentLoad getSedimentLoadWithDataUncached(
+        String river,
+        String type,
+        double startKm,
+        double endKm,
+        Date sdate,
+        Date edate
+    ) {
+        log.debug("SedimentLoadFactory.getBedHeightUncached");
+
+        Session session = SessionHolder.HOLDER.get();
+        SQLQuery sqlQuery = null;
+
+        Calendar cal = Calendar.getInstance();
+        cal.setTime(sdate);
+        int year = cal.get(Calendar.YEAR);
+        cal.set(year, 1, 1);
+        Calendar end = Calendar.getInstance();
+        end.set(year, 12, 31);
+
+        if (type.equals("single")) {
+            sqlQuery = session.createSQLQuery(SQL_SELECT_SINGLES_DATA)
+                .addScalar("description", StandardBasicTypes.STRING)
+                .addScalar("year", StandardBasicTypes.DATE)
+                .addScalar("load", StandardBasicTypes.DOUBLE);
+            sqlQuery.setString("name", river);
+            sqlQuery.setDouble("startKm", startKm);
+            sqlQuery.setDouble("endKm", endKm);
+            sqlQuery.setDate("begin", cal.getTime());
+            sqlQuery.setDate("end", end.getTime());
+            sqlQuery.setString("grain", "total");
+            List<Object []> results = sqlQuery.list();
+            SedimentLoad load = new SedimentLoad();
+            if (results.size() != 1) {
+                // should not happen. throw some exception.
+                return new SedimentLoad();
+            }
+            Object[] row = results.get(0);
+            load = new SedimentLoad(
+                    (String) row[0],
+                    (Date) row[1],
+                    null,
+                    false);
+            load.addCoarseValues(getValues("coarse", sqlQuery));
+            load.addFineMiddleValues(getValues("fine_middle", sqlQuery));
+            load.addSandValues(getValues("sand", sqlQuery));
+            load.addSuspSandBedValues(getValues("suspended_sediment", sqlQuery));
+            load.addSuspSandBedValues(getValues("susp_sand_bed", sqlQuery));
+            return load;
+        }
+        else if (type.equals("epoch")) {
+            Calendar send = Calendar.getInstance();
+            send.setTime(edate);
+            int eyear = send.get(Calendar.YEAR);
+            send.set(year, 1, 1);
+            Calendar eend = Calendar.getInstance();
+            eend.set(eyear, 12, 31);
+
+            sqlQuery = session.createSQLQuery(SQL_SELECT_EPOCHS)
+                .addScalar("description", StandardBasicTypes.STRING)
+                .addScalar("start_time", StandardBasicTypes.DATE)
+                .addScalar("stop_time", StandardBasicTypes.DATE)
+                .addScalar("load", StandardBasicTypes.DOUBLE);
+            sqlQuery.setString("name", river);
+            sqlQuery.setDouble("startKm", startKm);
+            sqlQuery.setDouble("endKm", endKm);
+            sqlQuery.setDate("sbegin", cal.getTime());
+            sqlQuery.setDate("sbegin", end.getTime());
+            sqlQuery.setDate("ebegin",send.getTime());
+            sqlQuery.setDate("eend", eend.getTime());
+            sqlQuery.setString("grain", "total");
+
+            List<Object []> results = sqlQuery.list();
+
+            SedimentLoad load = new SedimentLoad();
+            if (results.size() != 1) {
+                // should not happen. throw some exception.
+                return new SedimentLoad();
+            }
+            Object[] row = results.get(0);
+            load = new SedimentLoad(
+                    (String) row[0],
+                    (Date) row[1],
+                    null,
+                    false);
+            load.addCoarseValues(getValues("coarse", sqlQuery));
+            load.addFineMiddleValues(getValues("fine_middle", sqlQuery));
+            load.addSandValues(getValues("sand", sqlQuery));
+            load.addSuspSandBedValues(getValues("suspended_sediment", sqlQuery));
+            load.addSuspSandBedValues(getValues("susp_sand_bed", sqlQuery));
+            return load;
+        }
+        return new SedimentLoad();
+    }
+
+    /**
+     * 
+     */
+    protected static TDoubleArrayList getValues (
+        String fraction,
+        SQLQuery query
+    ) {
+        query.setString("grain", fraction);
+        List<Object[]> results = query.list();
+        TDoubleArrayList values = new TDoubleArrayList();
+        for (int i = 0; i < results.size(); i++) {
+            Object[] row = results.get(i);
+            values.add(((Double)row[2]).doubleValue());
+        }
+        return values;
+    }
+}