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.minfo; raimund@3755: raimund@3755: import java.util.Date; raimund@3755: import java.util.HashMap; raimund@3755: import java.util.Map; raimund@3755: tom@9726: import org.apache.logging.log4j.Logger; tom@9726: import org.apache.logging.log4j.LogManager; raimund@3755: import org.hibernate.SQLQuery; raimund@3755: import org.hibernate.Session; raimund@3755: import org.hibernate.transform.BasicTransformerAdapter; raimund@3755: import org.hibernate.type.StandardBasicTypes; raimund@3755: teichmann@5831: import org.dive4elements.river.backend.SedDBSessionHolder; andre@8684: import org.dive4elements.river.artifacts.model.RiverFactory; raimund@3755: raimund@3758: raimund@3755: public class QualityMeasurementFactory { raimund@3755: tom@9726: private static Logger log = LogManager.getLogger( tom@8856: QualityMeasurementFactory.class); raimund@3769: raimund@3758: private static final String SQL_BED_MEASUREMENT = tom@8787: "SELECT dat.km as km," + tom@8787: " dat.datum as datum," + raimund@3769: " sp.tiefevon as depth1," + raimund@3769: " sp.tiefebis as depth2," + raimund@3758: " sa.d10 as d10," + felix@7630: " sa.dm as dm," + raimund@3758: " sa.d16 as d16," + raimund@3758: " sa.d20 as d20," + raimund@3758: " sa.d25 as d25," + raimund@3758: " sa.d30 as d30," + raimund@3758: " sa.d40 as d40," + raimund@3758: " sa.d50 as d50," + raimund@3758: " sa.d60 as d60," + raimund@3758: " sa.d70 as d70," + raimund@3758: " sa.d75 as d75," + raimund@3758: " sa.d80 as d80," + raimund@3758: " sa.d84 as d84," + raimund@3758: " sa.d90 as d90," + raimund@3758: " sa.dmin as dmin," + raimund@3758: " sa.dmax as dmax " + tom@8787: "FROM sohltest dat " + tom@8787: " JOIN station sn ON sn.stationid = dat.stationid " + raimund@3769: " JOIN gewaesser gw ON gw.gewaesserid = sn.gewaesserid " + tom@8787: " JOIN sohlprobe sp ON sp.sohltestid = dat.sohltestid " + raimund@3758: " JOIN siebanalyse sa ON sa.sohlprobeid = sp.sohlprobeid " + raimund@3755: "WHERE gw.name = :name AND " + tom@8787: " dat.km IS NOT NULL AND " + raimund@3769: " sp.tiefevon IS NOT NULL AND " + tom@8856: " sp.tiefebis IS NOT NULL AND " + tom@8787: " dat.km BETWEEN :from - 0.001 AND :to + 0.001 "; tom@8856: // TODO: Test if char diameter ist null. raimund@3755: raimund@3758: private static final String SQL_BEDLOAD_MEASUREMENT = tom@8787: "SELECT dat.km as km," + tom@8787: " dat.datum as datum," + tom@8787: " dat.dm as dm," + tom@8787: " dat.d10 as d10," + tom@8787: " dat.d16 as d16," + tom@8787: " dat.d20 as d20," + tom@8787: " dat.d25 as d25," + tom@8787: " dat.d30 as d30," + tom@8787: " dat.d40 as d40," + tom@8787: " dat.d50 as d50," + tom@8787: " dat.d60 as d60," + tom@8787: " dat.d70 as d70," + tom@8787: " dat.d75 as d75," + tom@8787: " dat.d80 as d80," + tom@8787: " dat.d84 as d84," + tom@8787: " dat.d90 as d90," + tom@8787: " dat.dmin as dmin," + tom@8787: " dat.dmax as dmax " + tom@8787: "FROM messung dat" + tom@8787: " JOIN station sn ON sn.stationid = dat.stationid" + raimund@3758: " JOIN gewaesser gw ON gw.gewaesserid = sn.gewaesserid " + raimund@3756: "WHERE gw.name = :name AND " + tom@8787: " dat.km IS NOT NULL AND " + tom@8787: " dat.d10 IS NOT NULL AND" + //TODO: Add all other char. diameter. tom@8787: " dat.km BETWEEN :from - 0.001 AND :to + 0.001 "; tom@8787: tom@8787: private static final String SQL_WHERE_DATE = tom@8787: "AND dat.datum BETWEEN :start AND :end "; tom@8787: tom@8787: private static final String SQL_ORDER_BY = "ORDER BY dat.km"; tom@8787: raimund@3756: felix@6767: /** Transform query result into objects, use INSTANCE singleton. */ raimund@3755: public static final class QualityMeasurementResultTransformer raimund@3758: extends BasicTransformerAdapter { raimund@3758: felix@6767: // Make a singleton felix@6767: public static QualityMeasurementResultTransformer INSTANCE = felix@6767: new QualityMeasurementResultTransformer(); raimund@3755: felix@6767: private QualityMeasurementResultTransformer() { raimund@3755: } raimund@3755: felix@6781: /** tuples is a row. */ raimund@3755: @Override raimund@3758: public Object transformTuple(Object[] tuple, String[] aliases) { raimund@3755: Map map = new HashMap(); raimund@3755: double km = 0; raimund@3755: Date d = null; raimund@3769: double depth1 = Double.NaN; raimund@3769: double depth2 = Double.NaN; raimund@3755: for (int i = 0; i < tuple.length; ++i) { raimund@3755: if (tuple[i] != null) { raimund@3755: if (aliases[i].equals("km")) { raimund@3758: km = ((Number) tuple[i]).doubleValue(); raimund@3755: } raimund@3769: else if (aliases[i].equals("datum")) { raimund@3758: d = (Date) tuple[i]; raimund@3758: } raimund@3758: else if (aliases[i].equals("depth1")) { raimund@3758: depth1 = ((Number) tuple[i]).doubleValue(); raimund@3758: } raimund@3758: else if (aliases[i].equals("depth2")) { raimund@3758: depth2 = ((Number) tuple[i]).doubleValue(); raimund@3755: } raimund@3755: else { raimund@3769: map.put(aliases[i], ((Double) tuple[i])/1000); raimund@3755: } raimund@3755: } raimund@3755: } raimund@3758: return new QualityMeasurement(km, d, depth1, depth2, map); raimund@3755: } raimund@3755: } // class BasicTransformerAdapter raimund@3755: raimund@3755: private QualityMeasurementFactory() { raimund@3755: } raimund@3755: tom@8787: private static SQLQuery baseQuery( raimund@3755: Session session, raimund@3755: String river, raimund@3755: double from, raimund@3755: double to, raimund@3756: String statement raimund@3755: ) { raimund@3756: SQLQuery query = session.createSQLQuery(statement) raimund@3758: .addScalar("km", StandardBasicTypes.DOUBLE) raimund@3769: .addScalar("datum", StandardBasicTypes.DATE) felix@7630: .addScalar("dm", StandardBasicTypes.DOUBLE) raimund@3758: .addScalar("d10", StandardBasicTypes.DOUBLE) raimund@3758: .addScalar("d16", StandardBasicTypes.DOUBLE) raimund@3758: .addScalar("d20", StandardBasicTypes.DOUBLE) raimund@3758: .addScalar("d25", StandardBasicTypes.DOUBLE) raimund@3758: .addScalar("d30", StandardBasicTypes.DOUBLE) raimund@3758: .addScalar("d40", StandardBasicTypes.DOUBLE) raimund@3758: .addScalar("d50", StandardBasicTypes.DOUBLE) raimund@3758: .addScalar("d60", StandardBasicTypes.DOUBLE) raimund@3758: .addScalar("d70", StandardBasicTypes.DOUBLE) raimund@3758: .addScalar("d75", StandardBasicTypes.DOUBLE) raimund@3758: .addScalar("d80", StandardBasicTypes.DOUBLE) raimund@3758: .addScalar("d84", StandardBasicTypes.DOUBLE) raimund@3758: .addScalar("d90", StandardBasicTypes.DOUBLE) raimund@3758: .addScalar("dmin", StandardBasicTypes.DOUBLE) raimund@3758: .addScalar("dmax", StandardBasicTypes.DOUBLE); raimund@3758: tom@8787: if (statement.startsWith(SQL_BED_MEASUREMENT)) { raimund@3758: query.addScalar("depth1", StandardBasicTypes.DOUBLE); raimund@3758: query.addScalar("depth2", StandardBasicTypes.DOUBLE); raimund@3758: } tom@8787: andre@8684: String seddbRiver = RiverFactory.getRiver(river).nameForSeddb(); raimund@3755: andre@8684: query.setString("name", seddbRiver); raimund@3755: query.setDouble("from", from); raimund@3758: query.setDouble("to", to); raimund@3755: raimund@3755: query.setResultTransformer( raimund@3755: QualityMeasurementResultTransformer.INSTANCE); raimund@3755: tom@8787: return query; tom@8787: } tom@8787: tom@8787: protected static QualityMeasurements load( tom@8787: Session session, tom@8787: String river, tom@8787: double from, tom@8787: double to, tom@8787: String statement tom@8787: ) { tom@8787: SQLQuery query = baseQuery(session, river, from, to, statement); tom@8787: raimund@3755: return new QualityMeasurements(query.list()); raimund@3755: } raimund@3755: tom@8787: protected static QualityMeasurements load( tom@8787: Session session, tom@8787: String river, tom@8787: double from, tom@8787: double to, tom@8787: Date start, tom@8787: Date end, tom@8787: String statement tom@8787: ) { tom@8787: SQLQuery query = baseQuery(session, river, from, to, statement); tom@8787: tom@8787: query.setDate("start", start); tom@8787: query.setDate("end", end); tom@8787: tom@8787: return new QualityMeasurements(query.list()); tom@8787: } tom@8787: tom@8787: tom@8787: public static QualityMeasurements getBedMeasurements( tom@8787: String river, tom@8787: double from, tom@8787: double to tom@8787: ) { tom@8787: Session session = SedDBSessionHolder.HOLDER.get(); tom@8787: return load(session, river, from, to, tom@8787: SQL_BED_MEASUREMENT + SQL_ORDER_BY); tom@8787: } tom@8787: tom@8787: public static QualityMeasurements getBedloadMeasurements( tom@8787: String river, tom@8787: double from, tom@8787: double to tom@8787: ) { tom@8787: Session session = SedDBSessionHolder.HOLDER.get(); tom@8787: return load(session, river, from, to, tom@8787: SQL_BEDLOAD_MEASUREMENT + SQL_ORDER_BY); tom@8787: } tom@8787: raimund@3756: public static QualityMeasurements getBedMeasurements( raimund@3755: String river, raimund@3755: double from, raimund@3755: double to, raimund@3755: Date start, tom@8787: Date end tom@8787: ) { raimund@3755: Session session = SedDBSessionHolder.HOLDER.get(); tom@8787: return load(session, river, from, to, start, end, tom@8787: SQL_BED_MEASUREMENT + SQL_WHERE_DATE + SQL_ORDER_BY); raimund@3756: } raimund@3756: raimund@3756: public static QualityMeasurements getBedloadMeasurements( raimund@3756: String river, raimund@3756: double from, raimund@3756: double to, raimund@3756: Date start, raimund@3756: Date end raimund@3758: ) { raimund@3756: Session session = SedDBSessionHolder.HOLDER.get(); tom@8787: return load( tom@8787: session, tom@8787: river, tom@8787: from, tom@8787: to, tom@8787: start, tom@8787: end, tom@8787: SQL_BEDLOAD_MEASUREMENT + SQL_WHERE_DATE + SQL_ORDER_BY); raimund@3755: } felix@4807: } felix@4807: // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :