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: 
rrenkert@4296:     /** Query to get km and ws for wst_id and column_pos. */
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: 
rrenkert@4296:     /** Query to get name for wst_id and column_pos. */
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@4296:         "       syv.value AS load " +
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@4296:         "       JOIN sediment_yield_vales 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@4296:         "       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@4296:         "       sy.description AS description," +
rrenkert@4296:         "       ti.start_time AS year," +
rrenkert@4296:         "       syv.value AS load" +
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@4296:         "       JOIN sediment_yield_vales 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@4296:         "       AND ti_stop_time IS NOT NULL" +
rrenkert@4296:         "       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: 
rrenkert@4296:     /**
rrenkert@4296:      *
rrenkert@4296:      */
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@4296:             new StaticSedimentLoadCacheKey(river, startKm, endKm, null);
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@4296:             log.debug("Store static sediment loads  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@4296:         Date startDate,
rrenkert@4296:         Date endDate
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@4296:                 startDate,
rrenkert@4296:                 endDate);
rrenkert@4296:         }
rrenkert@4296: 
rrenkert@4296:         StaticSedimentLoadCacheKey key =
rrenkert@4296:             new StaticSedimentLoadCacheKey(river, startKm, endKm, startDate);
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:             getSedimentLoadWithDataUncached(river, type, startKm, endKm, startDate, endDate);
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@4296:         Date sdate,
rrenkert@4296:         Date edate
rrenkert@4296:     ) {
rrenkert@4296:         log.debug("SedimentLoadFactory.getBedHeightUncached");
rrenkert@4296: 
rrenkert@4296:         Session session = SessionHolder.HOLDER.get();
rrenkert@4296:         SQLQuery sqlQuery = null;
rrenkert@4296: 
rrenkert@4296:         Calendar cal = Calendar.getInstance();
rrenkert@4296:         cal.setTime(sdate);
rrenkert@4296:         int year = cal.get(Calendar.YEAR);
rrenkert@4296:         cal.set(year, 1, 1);
rrenkert@4296:         Calendar end = Calendar.getInstance();
rrenkert@4296:         end.set(year, 12, 31);
rrenkert@4296: 
rrenkert@4296:         if (type.equals("single")) {
rrenkert@4296:             sqlQuery = session.createSQLQuery(SQL_SELECT_SINGLES_DATA)
rrenkert@4296:                 .addScalar("description", StandardBasicTypes.STRING)
rrenkert@4296:                 .addScalar("year", 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@4296:             sqlQuery.setDate("begin", cal.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:             if (results.size() != 1) {
rrenkert@4296:                 // should not happen. throw some exception.
rrenkert@4296:                 return new SedimentLoad();
rrenkert@4296:             }
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@4296:             load.addCoarseValues(getValues("coarse", sqlQuery));
rrenkert@4296:             load.addFineMiddleValues(getValues("fine_middle", sqlQuery));
rrenkert@4296:             load.addSandValues(getValues("sand", sqlQuery));
rrenkert@4296:             load.addSuspSandBedValues(getValues("suspended_sediment", sqlQuery));
rrenkert@4296:             load.addSuspSandBedValues(getValues("susp_sand_bed", sqlQuery));
rrenkert@4296:             return load;
rrenkert@4296:         }
rrenkert@4296:         else if (type.equals("epoch")) {
rrenkert@4296:             Calendar send = Calendar.getInstance();
rrenkert@4296:             send.setTime(edate);
rrenkert@4296:             int eyear = send.get(Calendar.YEAR);
rrenkert@4296:             send.set(year, 1, 1);
rrenkert@4296:             Calendar eend = Calendar.getInstance();
rrenkert@4296:             eend.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@4296:             sqlQuery.setDate("sbegin", cal.getTime());
rrenkert@4296:             sqlQuery.setDate("sbegin", end.getTime());
rrenkert@4296:             sqlQuery.setDate("ebegin",send.getTime());
rrenkert@4296:             sqlQuery.setDate("eend", eend.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:             if (results.size() != 1) {
rrenkert@4296:                 // should not happen. throw some exception.
rrenkert@4296:                 return new SedimentLoad();
rrenkert@4296:             }
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@4296:             load.addCoarseValues(getValues("coarse", sqlQuery));
rrenkert@4296:             load.addFineMiddleValues(getValues("fine_middle", sqlQuery));
rrenkert@4296:             load.addSandValues(getValues("sand", sqlQuery));
rrenkert@4296:             load.addSuspSandBedValues(getValues("suspended_sediment", sqlQuery));
rrenkert@4296:             load.addSuspSandBedValues(getValues("susp_sand_bed", sqlQuery));
rrenkert@4296:             return load;
rrenkert@4296:         }
rrenkert@4296:         return new SedimentLoad();
rrenkert@4296:     }
rrenkert@4296: 
rrenkert@4296:     /**
rrenkert@4296:      * 
rrenkert@4296:      */
rrenkert@4296:     protected static TDoubleArrayList getValues (
rrenkert@4296:         String fraction,
rrenkert@4296:         SQLQuery query
rrenkert@4296:     ) {
rrenkert@4296:         query.setString("grain", fraction);
rrenkert@4296:         List<Object[]> results = query.list();
rrenkert@4296:         TDoubleArrayList values = new TDoubleArrayList();
rrenkert@4296:         for (int i = 0; i < results.size(); i++) {
rrenkert@4296:             Object[] row = results.get(i);
rrenkert@4296:             values.add(((Double)row[2]).doubleValue());
rrenkert@4296:         }
rrenkert@4296:         return values;
rrenkert@4296:     }
rrenkert@4296: }