teichmann@5863: /* Copyright (C) 2011, 2012, 2013 by Bundesanstalt für Gewässerkunde
teichmann@5863:  * Software engineering by Intevation GmbH
teichmann@5863:  *
teichmann@5863:  * This file is Free Software under the GNU AGPL (>=v3) 
teichmann@5863:  * and comes with ABSOLUTELY NO WARRANTY! Check out the
teichmann@5863:  * documentation coming with Dive4Elements River for details. 
teichmann@5863:  */
teichmann@5863: 
teichmann@5831: package org.dive4elements.river.artifacts.model.sq;
rrenkert@5500: 
rrenkert@5500: import java.math.BigDecimal;
rrenkert@5500: import java.util.Date;
rrenkert@5500: import java.util.List;
rrenkert@5500: 
rrenkert@5500: import net.sf.ehcache.Cache;
rrenkert@5500: import net.sf.ehcache.Element;
rrenkert@5500: 
rrenkert@5500: import org.apache.log4j.Logger;
rrenkert@5500: import org.hibernate.Query;
rrenkert@5500: import org.hibernate.Session;
rrenkert@5500: 
teichmann@5831: import org.dive4elements.river.artifacts.cache.CacheFactory;
teichmann@5831: import org.dive4elements.river.backend.SessionHolder;
rrenkert@5500: 
rrenkert@5500: 
rrenkert@5500: public class StaticSQFactory
rrenkert@5500: {
rrenkert@5500:     private static final Logger log =
rrenkert@5500:         Logger.getLogger(StaticSQFactory.class);
rrenkert@5500: 
rrenkert@5500:     public static final String SQL_SQ =
rrenkert@5500:         "SELECT " +
rrenkert@5500:             "sq.description AS description,"+
rrenkert@5500:             "ti.start_time  AS start_time," +
rrenkert@5500:             "ti.stop_time    AS stop_time, " +
rrenkert@5500:             "ms.name AS station_name, " +
rrenkert@5500:             "ms.station AS station_km, " +
rrenkert@5500:             "ms.measurement_type AS measurement_type, " +
rrenkert@5500:             "sqv.parameter AS parameter, " +
rrenkert@5500:             "sqv.a AS a, " +
rrenkert@5500:             "sqv.b AS b, " +
rrenkert@5500:             "sqv.qmax AS qmax " +
rrenkert@5500:         "FROM sq_relation sq " +
rrenkert@5500:             "JOIN time_intervals ti ON ti.id   = sq.time_interval_id " +
rrenkert@5500:             "JOIN rivers r ON r.id = sq.river_id " +
rrenkert@5500:             "JOIN sq_relation_value sqv ON sqv.sq_relation_id = sq.id " +
rrenkert@5500:             "JOIN measurement_station ms ON sqv.measurement_station_id = ms.id " +
rrenkert@5500:         "WHERE " +
rrenkert@5500:             "r.name = :river " +
rrenkert@5500:             "AND ms.id = :ms_id ";
rrenkert@5500: 
rrenkert@5500: 
rrenkert@5500:     private StaticSQFactory() {
rrenkert@5500:     }
rrenkert@5500: 
rrenkert@5500:     public static StaticSQContainer getSQRelations(
rrenkert@5500:         String river,
rrenkert@5500:         int measurementStation
rrenkert@5500:     ) {
rrenkert@5500:         Cache cache = CacheFactory.getCache(StaticSQCacheKey.CACHE_NAME);
rrenkert@5500: 
rrenkert@5500:         StaticSQCacheKey cacheKey;
rrenkert@5500: 
rrenkert@5500:         if (cache != null) {
rrenkert@5500:             cacheKey = new StaticSQCacheKey(river, measurementStation);
rrenkert@5500:             Element element = cache.get(cacheKey);
rrenkert@5500:             if (element != null) {
rrenkert@5500:                 log.debug("Got static bedheight values from cache");
rrenkert@5500:                 return (StaticSQContainer)element.getValue();
rrenkert@5500:             }
rrenkert@5500:         }
rrenkert@5500:         else {
rrenkert@5500:             cacheKey = null;
rrenkert@5500:         }
rrenkert@5500: 
rrenkert@5500:         StaticSQContainer values = getUncached(river, measurementStation);
rrenkert@5500: 
rrenkert@5500:         if (values != null && cacheKey != null) {
rrenkert@5500:             log.debug("Store static sq relations in cache.");
rrenkert@5500:             Element element = new Element(cacheKey, values);
rrenkert@5500:             cache.put(element);
rrenkert@5500:         }
rrenkert@5500:         return values;
rrenkert@5500:     }
rrenkert@5500: 
rrenkert@5500:     private static StaticSQContainer getUncached(
rrenkert@5500:         String river,
rrenkert@5500:         int measurementStation
rrenkert@5500:     ) {
rrenkert@5500:         Session session = SessionHolder.HOLDER.get();
rrenkert@5500: 
rrenkert@5500:         Query query = session.createSQLQuery(SQL_SQ)
rrenkert@5500:             .addScalar("description")
rrenkert@5500:             .addScalar("start_time")
rrenkert@5500:             .addScalar("stop_time")
rrenkert@5500:             .addScalar("station_name")
rrenkert@5500:             .addScalar("station_km")
rrenkert@5500:             .addScalar("measurement_type")
rrenkert@5500:             .addScalar("parameter")
rrenkert@5500:             .addScalar("a")
rrenkert@5500:             .addScalar("b")
rrenkert@5500:             .addScalar("qmax");
rrenkert@5500: 
rrenkert@5500:         query.setParameter("river", river);
rrenkert@5500:         query.setParameter("ms_id", measurementStation);
rrenkert@5500: 
rrenkert@5500:         List<Object []> list = query.list();
rrenkert@5500: 
rrenkert@5500:         if (list.isEmpty()) {
rrenkert@5500:             return new StaticSQContainer();
rrenkert@5500:         }
rrenkert@5500: 
rrenkert@5500:         StaticSQContainer sq = new StaticSQContainer();
rrenkert@5500:         sq.setDescription((String)list.get(0)[0]);
rrenkert@5500:         sq.setStationName((String)list.get(0)[3]);
rrenkert@5500:         sq.setKm(((BigDecimal)list.get(0)[4]).doubleValue());
rrenkert@5500: 
rrenkert@5500:         for (Object[] row : list) {
rrenkert@5500:             StaticSQRelation relation = new StaticSQRelation();
rrenkert@5500:             relation.setStartTime((Date)row[1]);
rrenkert@5500:             relation.setStopTime((Date)row[2]);
rrenkert@5500:             relation.setType((String)row[5]);
rrenkert@5500:             relation.setParameter((String)row[6]);
rrenkert@5500:             relation.setA(((BigDecimal)row[7]).doubleValue());
rrenkert@5500:             relation.setB(((BigDecimal)row[8]).doubleValue());
rrenkert@5500:             relation.setQmax(((BigDecimal)row[9]).doubleValue());
rrenkert@5500:             sq.addSQRelation(relation);
rrenkert@5500:         }
rrenkert@5500:         return sq;
rrenkert@5500:     }
rrenkert@5500: }