teichmann@5863: /* Copyright (C) 2011, 2012, 2013 by Bundesanstalt für Gewässerkunde
teichmann@5863:  * Software engineering by Intevation GmbH
teichmann@5863:  *
teichmann@5994:  * This file is Free Software under the GNU AGPL (>=v3)
teichmann@5863:  * and comes with ABSOLUTELY NO WARRANTY! Check out the
teichmann@5994:  * 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: 
andre@8543:     public static final String SQL_STATIONS_AT_RANGE =
andre@8543:         "SELECT "+
andre@8543:             "ms.id AS ms_id " +
andre@8543:         "FROM measurement_station ms " +
andre@8543:             "JOIN ranges ra ON ra.id = ms.range_id " +
andre@8543:             "JOIN rivers r ON r.id = ra.river_id " +
andre@8543:             "WHERE r.name = :river AND " +
andre@8543:             "ms.range_id = (SELECT range_id " +
andre@8543:                            "FROM measurement_station " +
andre@8543:                            "WHERE id = :ms_id)";
andre@8543: 
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, " +
tom@8412:             "CASE WHEN r.km_up = 1 AND ra.b IS NOT NULL " +
tom@8412:                 "THEN ra.b " +
tom@8412:                 "ELSE ra.a " +
tom@8412:             "END 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 sq_relation_value sqv ON sqv.sq_relation_id = sq.id " +
tom@8412:             "JOIN measurement_station ms " +
tom@8412:                 "ON sqv.measurement_station_id = ms.id " +
tom@8412:             "JOIN ranges ra ON ra.id = ms.range_id " +
tom@8412:             "JOIN rivers r ON r.id = ra.river_id ";
aheinecke@7236: 
aheinecke@7236:     public static final String STATION_CLAUSE =
rrenkert@5500:         "WHERE " +
rrenkert@5500:             "r.name = :river " +
rrenkert@5500:             "AND ms.id = :ms_id ";
rrenkert@5500: 
aheinecke@7236:     public static final String ID_CLAUSE =
aheinecke@7236:         "WHERE " +
aheinecke@7236:             "sqv.id = :dis_id ";
rrenkert@5500: 
rrenkert@5500:     private StaticSQFactory() {
rrenkert@5500:     }
rrenkert@5500: 
andre@8543:     /** Get SQ relations for a measurement station's location.
andre@8543:      * Returns all SQRelations for the location of the station and
andre@8543:      * not just for the station. E.g. for a "Geschiebemessstelle"
andre@8543:      * and a "Schwebstoffmesstelle" at the same place.*/
andre@8543:     public static StaticSQContainer getSQRelationsForLocation(
andre@8543:         String river,
andre@8543:         int measurementStation
andre@8543:     ) {
andre@8543:         Session session = SessionHolder.HOLDER.get();
andre@8543:         Query query = session.createSQLQuery(SQL_STATIONS_AT_RANGE)
andre@8543:             .setParameter("river", river)
andre@8543:             .setParameter("ms_id", measurementStation);
andre@8543:         /* Take the first container for the station requested. */
andre@8543:         StaticSQContainer retval = getSQRelations(river, measurementStation);
andre@8543: 
andre@8543:         /* And some others */
andre@8543:         List<Integer> list = query.list();
andre@8543:         if (list == null || list.isEmpty()) {
andre@8543:             log.error("Did not even find one measurement station. Broken Query?");
andre@8543:             return retval;
andre@8543:         }
andre@8543: 
andre@8543:         for (Integer stationId: list) {
andre@8543:             log.debug("Collecting SQ Relations for: "+ stationId);
andre@8543:             if (stationId == measurementStation) {
andre@8543:                 /* Skip the same station */
andre@8543:                 continue;
andre@8543:             }
andre@8543: 
andre@8543:             StaticSQContainer additional = getSQRelations(river, stationId);
andre@8543:             if (additional == null || additional.getSQRelations() == null) {
andre@8543:                 continue;
andre@8543:             }
andre@8543: 
andre@8543:             if (retval == null || retval.getSQRelations() == null || retval.getSQRelations().isEmpty()) {
andre@8543:                 /* Old one is empty, just take the new one. */
andre@8543:                 retval = additional;
andre@8543:                 continue;
andre@8543:             }
andre@8543: 
andre@8543:             for (StaticSQRelation rel: additional.getSQRelations()) {
andre@8543:                 /* Check if we already have one for this parameter.
andre@8543:                  * This is highly unlikely in the data scheme of things. */
andre@8543:                 List<StaticSQRelation> old = retval.getRelationsByParameter(
andre@8543:                         rel.getParameter());
andre@8543:                 if (old != null || !old.isEmpty()) {
andre@8543:                     log.warn("Multiple SQ relation Parameters found for different " +
andre@8543:                              "measurement_stations at the same range. This should not happen.");
andre@8543:                     continue;
andre@8543:                 }
andre@8543:                 retval.addSQRelation(rel);
andre@8543:             }
andre@8543: 
andre@8543:         }
andre@8543:         return retval;
andre@8543:     }
andre@8543: 
andre@8543: 
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) {
andre@8543:                 log.debug("Got static sq relations 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: 
aheinecke@7236:     public static StaticSQContainer getDistinctRelation(int id) {
aheinecke@7236:         Session session = SessionHolder.HOLDER.get();
aheinecke@7236: 
aheinecke@7236:         Query query = session.createSQLQuery(SQL_SQ + ID_CLAUSE)
aheinecke@7236:             .addScalar("description")
aheinecke@7236:             .addScalar("start_time")
aheinecke@7236:             .addScalar("stop_time")
aheinecke@7236:             .addScalar("station_name")
aheinecke@7236:             .addScalar("station_km")
aheinecke@7236:             .addScalar("measurement_type")
aheinecke@7236:             .addScalar("parameter")
aheinecke@7236:             .addScalar("a")
aheinecke@7236:             .addScalar("b")
aheinecke@7236:             .addScalar("qmax");
aheinecke@7236: 
aheinecke@7236:         query.setParameter("dis_id", id);
aheinecke@7236: 
aheinecke@7236:         /* This could be done nicer with hibernate */
aheinecke@7236:         List<Object []> list = query.list();
aheinecke@7236:         if (list.isEmpty()) {
aheinecke@7236:             log.debug("Query returened nothing");
aheinecke@7236:             return null;
aheinecke@7236:         }
aheinecke@7236:         Object [] row = list.get(0);
aheinecke@7236: 
aheinecke@7236:         StaticSQContainer sq = new StaticSQContainer();
aheinecke@7236:         sq.setDescription((String)list.get(0)[0]);
aheinecke@7236:         sq.setStationName((String)list.get(0)[3]);
aheinecke@7236:         sq.setKm(((BigDecimal)list.get(0)[4]).doubleValue());
aheinecke@7236: 
aheinecke@7236:         StaticSQRelation relation = new StaticSQRelation();
aheinecke@7236:         relation.setStartTime((Date)row[1]);
aheinecke@7236:         relation.setStopTime((Date)row[2]);
aheinecke@7236:         relation.setType((String)row[5]);
aheinecke@7236:         relation.setParameter((String)row[6]);
aheinecke@7236:         relation.setA(((BigDecimal)row[7]).doubleValue());
aheinecke@7236:         relation.setB(((BigDecimal)row[8]).doubleValue());
aheinecke@7236:         relation.setQmax(((BigDecimal)row[9]).doubleValue());
aheinecke@7236:         sq.addSQRelation(relation);
aheinecke@7236: 
aheinecke@7236:         return sq;
aheinecke@7236:     }
aheinecke@7236: 
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: 
aheinecke@7236:         Query query = session.createSQLQuery(SQL_SQ + STATION_CLAUSE)
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()) {
aheinecke@7236:             log.debug("Query returened empty");
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: }