rrenkert@4296: package de.intevation.flys.artifacts.model.minfo;
rrenkert@4296: 
rrenkert@4296: import gnu.trove.TDoubleArrayList;
rrenkert@4296: 
rrenkert@4296: import java.util.Calendar;
rrenkert@4296: import java.util.Date;
rrenkert@4296: import java.util.List;
rrenkert@4296: 
rrenkert@4296: import net.sf.ehcache.Cache;
rrenkert@4296: import net.sf.ehcache.Element;
rrenkert@4296: 
rrenkert@4296: import org.apache.log4j.Logger;
rrenkert@4296: import org.hibernate.SQLQuery;
rrenkert@4296: import org.hibernate.Session;
rrenkert@4296: import org.hibernate.type.StandardBasicTypes;
rrenkert@4296: 
rrenkert@4296: import de.intevation.flys.artifacts.cache.CacheFactory;
rrenkert@4296: import de.intevation.flys.artifacts.model.StaticSedimentLoadCacheKey;
rrenkert@4296: import de.intevation.flys.backend.SessionHolder;
rrenkert@4296: 
rrenkert@4296: 
rrenkert@4296: public class SedimentLoadFactory
rrenkert@4296: {
rrenkert@4296:     /** Private logger to use here. */
rrenkert@4296:     private static Logger log = Logger.getLogger(SedimentLoadFactory.class);
rrenkert@4296: 
rrenkert@4296:     public static final String LOADS_CACHE_NAME = "sedimentloads";
rrenkert@4296:     public static final String LOAD_DATA_CACHE_NAME = "sedimentload-data";
rrenkert@4296: 
felix@4426:     /** Query to get description and start year, given name and a km range. */
rrenkert@4296:     public static final String SQL_SELECT_SINGLES =
rrenkert@4296:         "SELECT DISTINCT " +
rrenkert@4296:         "       sy.description AS description, " +
rrenkert@4296:         "       ti.start_time AS year " +
rrenkert@4296:         "   FROM     sediment_yield sy " +
rrenkert@4296:         "       JOIN rivers r ON sy.river_id = r.id " +
rrenkert@4296:         "       JOIN sediment_yield_values syv ON sy.id = syv.sediment_yield_id " +
rrenkert@4296:         "       JOIN time_intervals ti ON sy.time_interval_id = ti.id " +
rrenkert@4296:         "   WHERE   r.name = :name " +
rrenkert@4296:         "       AND ti.stop_time IS NULL " +
rrenkert@4296:         "       AND syv.station BETWEEN :startKm AND :endKm";
rrenkert@4296: 
felix@4426:     /** Query to get start time, stop time and description. */
rrenkert@4296:     public static final String SQL_SELECT_EPOCHS =
rrenkert@4296:         "SELECT DISTINCT " +
rrenkert@4296:         "       sy.description AS description, " +
rrenkert@4296:         "       ti.start_time AS start, " +
rrenkert@4296:         "       ti.stop_time AS end " +
rrenkert@4296:         "   FROM     sediment_yield sy " +
rrenkert@4296:         "       JOIN rivers r ON sy.river_id = r.id " +
rrenkert@4296:         "       JOIN sediment_yield_values syv ON sy.id = syv.sediment_yield_id " +
rrenkert@4296:         "       JOIN time_intervals ti ON sy.time_interval_id = ti.id " +
rrenkert@4296:         "   WHERE   r.name = :name " +
rrenkert@4296:         "       AND ti.stop_time IS NOT NULL " +
rrenkert@4296:         "       AND syv.station BETWEEN :startKm AND :endKm";
rrenkert@4296: 
rrenkert@4296:     public static final String SQL_SELECT_SINGLES_DATA =
rrenkert@4296:         "SELECT" +
rrenkert@4296:         "       sy.description AS description, " +
rrenkert@4296:         "       ti.start_time AS year, " +
rrenkert@4370:         "       syv.value AS load, " +
rrenkert@4370:         "       syv.station AS km " +
rrenkert@4296:         "   FROM     sediment_yield sy " +
rrenkert@4296:         "       JOIN rivers r ON sy.river_id = r.id " +
rrenkert@4296:         "       JOIN time_intervals ti ON sy.time_interval_id = ti.id " +
rrenkert@4370:         "       JOIN sediment_yield_values syv ON sy.id = syv.sediment_yield_id " +
rrenkert@4296:         "       JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id " +
rrenkert@4296:         "   WHERE   r.name = :name " +
rrenkert@4296:         "       AND ti.start_time BETWEEN :begin AND :end " +
rrenkert@4370:         "       AND ti.stop_time IS NULL " +
rrenkert@4296:         "       AND gf.name = :grain " +
rrenkert@4296:         "       AND syv.station BETWEEN :startKm AND :endKm";
rrenkert@4296: 
rrenkert@4296:     public static final String SQL_SELECT_EPOCHS_DATA =
rrenkert@4296:         "SELECT" +
rrenkert@4370:         "       sy.description AS description, " +
rrenkert@4370:         "       ti.start_time AS year, " +
rrenkert@4370:         "       syv.value AS load, " +
rrenkert@4370:         "       syv.station AS km " +
rrenkert@4296:         "   FROM     sediment_yield sy" +
rrenkert@4296:         "       JOIN rivers r ON sy.river_id = r.id " +
rrenkert@4296:         "       JOIN time_intervals ti ON sy.time_interval_id = ti.id" +
rrenkert@4370:         "       JOIN sediment_yield_values syv ON sy.id = syv.sediment_yield_id" +
rrenkert@4296:         "       JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id" +
rrenkert@4296:         "   WHERE   r.name = :name" +
rrenkert@4296:         "       AND ti.start_time BETWEEN :sbegin AND :send" +
rrenkert@4370:         "       AND ti.stop_time IS NOT NULL" +
rrenkert@4370:         "       AND ti.stop_time BETWEEN :ebegin AND :eend" +
rrenkert@4296:         "       AND gf.name = :grain " +
rrenkert@4296:         "       AND syv.station BETWEEN :startKm AND :endKm";
rrenkert@4296: 
rrenkert@4296:     private SedimentLoadFactory() {
rrenkert@4296:     }
rrenkert@4296: 
felix@4426: 
rrenkert@4296:     public static SedimentLoad[] getLoads(
rrenkert@4296:         String river,
rrenkert@4296:         String type,
rrenkert@4296:         double startKm,
rrenkert@4296:         double endKm
rrenkert@4296:     ) {
rrenkert@4296:         log.debug("SedimentLoadFactory.getLoads");
rrenkert@4296:         Cache cache = CacheFactory.getCache(LOADS_CACHE_NAME);
rrenkert@4296: 
rrenkert@4296:         if (cache == null) {
rrenkert@4296:             log.debug("Cache not configured.");
rrenkert@4296:             return getSedimentLoadsUncached(river, type, startKm, endKm);
rrenkert@4296:         }
rrenkert@4296: 
rrenkert@4296:         StaticSedimentLoadCacheKey key =
rrenkert@4370:             new StaticSedimentLoadCacheKey(river, startKm, endKm, 0, 0);
rrenkert@4296: 
rrenkert@4296:         Element element = cache.get(key);
rrenkert@4296: 
rrenkert@4296:         if (element != null) {
rrenkert@4296:             log.debug("SedimentLoad found in cache");
rrenkert@4296:             return (SedimentLoad[])element.getValue();
rrenkert@4296:         }
rrenkert@4296: 
rrenkert@4296:         SedimentLoad[] values =
rrenkert@4296:             getSedimentLoadsUncached(river, type, startKm, endKm);
rrenkert@4296: 
rrenkert@4296:         if (values != null && key != null) {
rrenkert@4370:             log.debug("Store static sediment load values in cache.");
rrenkert@4296:             element = new Element(key, values);
rrenkert@4296:             cache.put(element);
rrenkert@4296:         }
rrenkert@4296:         return values;
rrenkert@4296:     }
rrenkert@4296: 
rrenkert@4296:     public static SedimentLoad getLoadwithData(
rrenkert@4296:         String river,
rrenkert@4296:         String type,
rrenkert@4296:         double startKm,
rrenkert@4296:         double endKm,
rrenkert@4370:         int syear,
rrenkert@4370:         int eyear
rrenkert@4296:     ) {
rrenkert@4296:         log.debug("SedimentLoadFactory.getLoadWithData");
rrenkert@4296:         Cache cache = CacheFactory.getCache(LOAD_DATA_CACHE_NAME);
rrenkert@4296: 
rrenkert@4296:         if (cache == null) {
rrenkert@4296:             log.debug("Cache not configured.");
rrenkert@4296:             return getSedimentLoadWithDataUncached(
rrenkert@4296:                 river,
rrenkert@4296:                 type,
rrenkert@4296:                 startKm,
rrenkert@4296:                 endKm,
rrenkert@4370:                 syear,
rrenkert@4370:                 eyear);
rrenkert@4296:         }
rrenkert@4296: 
rrenkert@4296:         StaticSedimentLoadCacheKey key =
rrenkert@4370:             new StaticSedimentLoadCacheKey(river, startKm, endKm, syear, eyear);
rrenkert@4296: 
rrenkert@4296:         Element element = cache.get(key);
rrenkert@4296: 
rrenkert@4296:         if (element != null) {
rrenkert@4296:             log.debug("SedimentLoad found in cache");
rrenkert@4296:             return (SedimentLoad)element.getValue();
rrenkert@4296:         }
rrenkert@4296: 
rrenkert@4370:         SedimentLoad values = getSedimentLoadWithDataUncached(
rrenkert@4370:             river,
rrenkert@4370:             type,
rrenkert@4370:             startKm,
rrenkert@4370:             endKm,
rrenkert@4370:             syear,
rrenkert@4370:             eyear);
rrenkert@4296: 
rrenkert@4296:         if (values != null && key != null) {
rrenkert@4296:             log.debug("Store static bed height values in cache.");
rrenkert@4296:             element = new Element(key, values);
rrenkert@4296:             cache.put(element);
rrenkert@4296:         }
rrenkert@4296:         return values;
rrenkert@4296:     }
rrenkert@4296: 
rrenkert@4296:     /**
rrenkert@4296:      * Get sediment loads from db.
rrenkert@4296:      * @param river the river
rrenkert@4296:      * @param type the sediment load type (year or epoch)
rrenkert@4296:      * @return according sediment loads.
rrenkert@4296:      */
rrenkert@4296:     public static SedimentLoad[] getSedimentLoadsUncached(
rrenkert@4296:         String river,
rrenkert@4296:         String type,
rrenkert@4296:         double startKm,
rrenkert@4296:         double endKm
rrenkert@4296:     ) {
rrenkert@4296:         log.debug("SedimentLoadFactory.getSedimentLoadsUncached");
rrenkert@4296: 
rrenkert@4296:         Session session = SessionHolder.HOLDER.get();
rrenkert@4296:         SQLQuery sqlQuery = null;
rrenkert@4296: 
rrenkert@4296: 
rrenkert@4296:         if (type.equals("single")) {
rrenkert@4296:             sqlQuery = session.createSQLQuery(SQL_SELECT_SINGLES)
rrenkert@4296:                 .addScalar("description", StandardBasicTypes.STRING)
rrenkert@4296:                 .addScalar("year", StandardBasicTypes.DATE);
rrenkert@4296:             sqlQuery.setString("name", river);
rrenkert@4296:             sqlQuery.setDouble("startKm", startKm);
rrenkert@4296:             sqlQuery.setDouble("endKm", endKm);
rrenkert@4296:             List<Object []> results = sqlQuery.list();
rrenkert@4296:             SedimentLoad[] loads = new SedimentLoad[results.size()];
rrenkert@4296:             for (int i = 0; i < results.size(); i++) {
rrenkert@4296:                 Object[] row = results.get(i);
rrenkert@4296:                 loads[i] = new SedimentLoad(
rrenkert@4296:                     (String) row[0],
rrenkert@4296:                     (Date) row[1],
rrenkert@4296:                     null,
rrenkert@4296:                     false);
rrenkert@4296:             }
rrenkert@4296:             return loads;
rrenkert@4296:         }
rrenkert@4296:         else if (type.equals("epoch")) {
rrenkert@4296:             sqlQuery = session.createSQLQuery(SQL_SELECT_EPOCHS)
rrenkert@4296:                 .addScalar("description", StandardBasicTypes.STRING)
rrenkert@4296:                 .addScalar("start", StandardBasicTypes.DATE)
rrenkert@4296:                 .addScalar("end", StandardBasicTypes.DATE);
rrenkert@4296:             sqlQuery.setString("name", river);
rrenkert@4296:             sqlQuery.setDouble("startKm", startKm);
rrenkert@4296:             sqlQuery.setDouble("endKm", endKm);
rrenkert@4296:             List<Object []> results = sqlQuery.list();
rrenkert@4296: 
rrenkert@4296:             SedimentLoad[] loads = new SedimentLoad[results.size()];
rrenkert@4296:             for (int i = 0; i < results.size(); i++) {
rrenkert@4296:                 Object[] row = results.get(i);
rrenkert@4296:                 loads[i] = new SedimentLoad(
rrenkert@4296:                     (String) row[0],
rrenkert@4296:                     (Date) row[1],
rrenkert@4296:                     (Date) row[2],
rrenkert@4296:                     true);
rrenkert@4296:             }
rrenkert@4296:             return loads;
rrenkert@4296:         }
rrenkert@4296:         return new SedimentLoad[0];
rrenkert@4296:     }
rrenkert@4296: 
rrenkert@4296:     /**
rrenkert@4296:      * Get sediment loads from db.
rrenkert@4296:      * @param river the river
rrenkert@4296:      * @param type the sediment load type (year or epoch)
rrenkert@4296:      * @return according sediment loads.
rrenkert@4296:      */
rrenkert@4296:     public static SedimentLoad getSedimentLoadWithDataUncached(
rrenkert@4296:         String river,
rrenkert@4296:         String type,
rrenkert@4296:         double startKm,
rrenkert@4296:         double endKm,
rrenkert@4370:         int syear,
rrenkert@4370:         int eyear
rrenkert@4296:     ) {
rrenkert@4370:         log.debug("SedimentLoadFactory.getSedimentLoadWithDataUncached");
rrenkert@4296: 
rrenkert@4296:         Session session = SessionHolder.HOLDER.get();
rrenkert@4296:         SQLQuery sqlQuery = null;
rrenkert@4296: 
rrenkert@4370:         Calendar start = Calendar.getInstance();
rrenkert@4370:         start.set(syear, 1, 1);
rrenkert@4296:         Calendar end = Calendar.getInstance();
rrenkert@4370:         end.set(syear, 12, 31);
rrenkert@4296: 
rrenkert@4370:         if (type.equals("year") || type.equals("epoch")) {
rrenkert@4296:             sqlQuery = session.createSQLQuery(SQL_SELECT_SINGLES_DATA)
rrenkert@4296:                 .addScalar("description", StandardBasicTypes.STRING)
rrenkert@4296:                 .addScalar("year", StandardBasicTypes.DATE)
rrenkert@4370:                 .addScalar("load", StandardBasicTypes.DOUBLE)
rrenkert@4370:                 .addScalar("km", StandardBasicTypes.DOUBLE);
rrenkert@4296:             sqlQuery.setString("name", river);
rrenkert@4296:             sqlQuery.setDouble("startKm", startKm);
rrenkert@4296:             sqlQuery.setDouble("endKm", endKm);
rrenkert@4370:             sqlQuery.setDate("begin", start.getTime());
rrenkert@4296:             sqlQuery.setDate("end", end.getTime());
rrenkert@4296:             sqlQuery.setString("grain", "total");
rrenkert@4296:             List<Object []> results = sqlQuery.list();
rrenkert@4296:             SedimentLoad load = new SedimentLoad();
rrenkert@4296:             Object[] row = results.get(0);
rrenkert@4296:             load = new SedimentLoad(
rrenkert@4296:                     (String) row[0],
rrenkert@4296:                     (Date) row[1],
rrenkert@4296:                     null,
rrenkert@4296:                     false);
rrenkert@4370:             getValues("coarse", sqlQuery, load);
rrenkert@4370:             getValues("fine_middle", sqlQuery, load);
rrenkert@4370:             getValues("sand", sqlQuery, load);
rrenkert@4370:             getValues("suspended_sediment", sqlQuery, load);
rrenkert@4370:             getValues("susp_sand_bed", sqlQuery, load);
rrenkert@4370:             getValues("susp_sand", sqlQuery, load);
rrenkert@4296:             return load;
rrenkert@4296:         }
rrenkert@4370:         else if (type.equals("off_epoch")) {
rrenkert@4370:             Calendar toStart = Calendar.getInstance();
rrenkert@4370:             toStart.set(eyear, 1, 1);
rrenkert@4370:             Calendar toEnd = Calendar.getInstance();
rrenkert@4370:             toEnd.set(eyear, 12, 31);
rrenkert@4296: 
rrenkert@4296:             sqlQuery = session.createSQLQuery(SQL_SELECT_EPOCHS)
rrenkert@4296:                 .addScalar("description", StandardBasicTypes.STRING)
rrenkert@4296:                 .addScalar("start_time", StandardBasicTypes.DATE)
rrenkert@4296:                 .addScalar("stop_time", StandardBasicTypes.DATE)
rrenkert@4296:                 .addScalar("load", StandardBasicTypes.DOUBLE);
rrenkert@4296:             sqlQuery.setString("name", river);
rrenkert@4296:             sqlQuery.setDouble("startKm", startKm);
rrenkert@4296:             sqlQuery.setDouble("endKm", endKm);
rrenkert@4370:             sqlQuery.setDate("sbegin", start.getTime());
rrenkert@4296:             sqlQuery.setDate("sbegin", end.getTime());
rrenkert@4370:             sqlQuery.setDate("ebegin",toStart.getTime());
rrenkert@4370:             sqlQuery.setDate("eend", toEnd.getTime());
rrenkert@4296:             sqlQuery.setString("grain", "total");
rrenkert@4296: 
rrenkert@4296:             List<Object []> results = sqlQuery.list();
rrenkert@4296: 
rrenkert@4296:             SedimentLoad load = new SedimentLoad();
rrenkert@4296:             Object[] row = results.get(0);
rrenkert@4296:             load = new SedimentLoad(
rrenkert@4296:                     (String) row[0],
rrenkert@4296:                     (Date) row[1],
rrenkert@4296:                     null,
rrenkert@4296:                     false);
rrenkert@4370:             TDoubleArrayList kms = new TDoubleArrayList();
rrenkert@4370:             for (int i = 0; i < results.size(); i++) {
rrenkert@4370:                 row = results.get(i);
rrenkert@4370:                 kms.add((Double)row[3]);
rrenkert@4370:             }
rrenkert@4370:             getValues("coarse", sqlQuery, load);
rrenkert@4370:             getValues("fine_middle", sqlQuery, load);
rrenkert@4370:             getValues("sand", sqlQuery, load);
rrenkert@4370:             getValues("suspended_sediment", sqlQuery, load);
rrenkert@4370:             getValues("susp_sand_bed", sqlQuery, load);
rrenkert@4370:             getValues("susp_sand", sqlQuery, load);
rrenkert@4296:             return load;
rrenkert@4296:         }
rrenkert@4296:         return new SedimentLoad();
rrenkert@4296:     }
rrenkert@4296: 
rrenkert@4296:     /**
rrenkert@4296:      * 
rrenkert@4296:      */
rrenkert@4370:     protected static void getValues (
rrenkert@4296:         String fraction,
rrenkert@4370:         SQLQuery query,
rrenkert@4370:         SedimentLoad load
rrenkert@4296:     ) {
rrenkert@4296:         query.setString("grain", fraction);
rrenkert@4296:         List<Object[]> results = query.list();
rrenkert@4296:         for (int i = 0; i < results.size(); i++) {
rrenkert@4296:             Object[] row = results.get(i);
rrenkert@4370:             double km = (Double)row[3];
rrenkert@4370:             double v = -1;
rrenkert@4370:             if (row[2] != null) {
rrenkert@4370:                 v = ((Double)row[2]).doubleValue();
rrenkert@4370:             }
rrenkert@4370:             if (fraction.equals("coarse")) {
rrenkert@4370:                 load.setCoarse(km, v);
rrenkert@4370:             }
rrenkert@4370:             else if (fraction.equals("sand")) {
rrenkert@4370:                 load.setSand(km, v);
rrenkert@4370:             }
rrenkert@4370:             else if (fraction.equals("fine_middle")) {
rrenkert@4370:                 load.setFineMiddle(km, v);
rrenkert@4370:             }
rrenkert@4370:             else if (fraction.equals("suspended_sediment")) {
rrenkert@4370:                 load.setSuspSediment(km, v);
rrenkert@4370:             }
rrenkert@4370:             else if (fraction.equals("sups_sand")) {
rrenkert@4370:                 load.setSuspSand(km, v);
rrenkert@4370:             }
rrenkert@4370:             else if (fraction.equals("susp_sand_bed")) {
rrenkert@4370:                 load.setSuspSandBed(km, v);
rrenkert@4370:             }
rrenkert@4296:         }
rrenkert@4296:     }
rrenkert@4296: }