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; 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; felix@6374: import java.util.TreeMap; 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; teichmann@5831: import org.dive4elements.river.artifacts.cache.CacheFactory; felix@6374: import org.dive4elements.river.artifacts.model.Range; felix@6374: import org.dive4elements.river.artifacts.model.RiverFactory; teichmann@5831: import org.dive4elements.river.artifacts.model.StaticSedimentLoadCacheKey; teichmann@5831: import org.dive4elements.river.backend.SessionHolder; rrenkert@6392: import org.dive4elements.river.model.MeasurementStation; rrenkert@6392: import org.hibernate.SQLQuery; rrenkert@6392: import org.hibernate.Session; rrenkert@6392: import org.hibernate.type.StandardBasicTypes; rrenkert@4296: felix@6740: felix@5662: /** Pull Sediment Loads out of db. */ 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: felix@6725: // Cache name/keys 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@6725: /** Query to get description and start year of single type felix@6725: * sediment_yields. */ 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@6725: /** Query to get description, name and time range for official felix@6725: * epoch-type sediment yields. */ felix@6725: public static final String SQL_SELECT_OFFEPOCHS = felix@6725: "SELECT DISTINCT " + felix@6725: " ti.start_time AS startYear, " + felix@6725: " ti.stop_time AS end " + felix@6725: " FROM sediment_yield sy " + felix@6725: " JOIN rivers r ON sy.river_id = r.id " + felix@6725: " JOIN sediment_yield_values syv ON sy.id = syv.sediment_yield_id " + felix@6725: " JOIN time_intervals ti ON sy.time_interval_id = ti.id " + felix@6725: " WHERE r.name = :name " + felix@6725: " AND ti.stop_time IS NOT NULL " + felix@6725: " AND syv.station BETWEEN :startKm AND :endKm " + felix@6725: " AND sy.kind = 1"; felix@6725: felix@6725: /** Query to get description, name and time range for epoch-type felix@6725: * sediment yields. */ rrenkert@4296: public static final String SQL_SELECT_EPOCHS = rrenkert@4296: "SELECT DISTINCT " + rrenkert@4296: " sy.description AS description, " + tom@6477: " ti.start_time AS startYear, " + rrenkert@6392: " ti.stop_time AS end, " + rrenkert@6392: " u.name AS unit" + 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@6392: " JOIN units u ON u.id = sy.unit_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: felix@7181: public static final String SQL_SELECT_SINGLES_DATA_BY_ID = felix@7181: "SELECT" + felix@7181: " sy.description AS description, " + felix@7181: " syv.value AS load, " + felix@7181: " syv.station AS km, " + felix@7184: " u.name AS unit, " + felix@7181: " gf.name AS fraction " + felix@7181: " FROM sediment_yield sy " + felix@7181: " JOIN sediment_yield_values syv ON sy.id = syv.sediment_yield_id " + felix@7181: " JOIN units u ON u.id = sy.unit_id" + felix@7181: " JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id " + felix@7181: " WHERE sy.id = :id" + felix@7181: " ORDER BY syv.station"; felix@7181: 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@6392: " syv.station AS km, " + rrenkert@6392: " u.name AS unit " + 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@6392: " JOIN units u ON u.id = sy.unit_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@6392: " AND syv.station BETWEEN :startKm AND :endKm " + rrenkert@6392: " ORDER BY syv.station"; rrenkert@6392: rrenkert@6392: public static final String SQL_SELECT_UNKNOWN_DATA = rrenkert@6392: "SELECT" + rrenkert@6392: " sy.description AS description, " + tom@6477: " ti.start_time AS startYear, " + rrenkert@6392: " ti.stop_time AS end, " + rrenkert@6392: " syv.value AS load, " + rrenkert@6392: " syv.station AS km, " + rrenkert@6392: " u.name AS unit " + rrenkert@6392: " FROM sediment_yield sy " + rrenkert@6392: " JOIN rivers r ON sy.river_id = r.id " + rrenkert@6392: " JOIN time_intervals ti ON sy.time_interval_id = ti.id " + rrenkert@6392: " JOIN sediment_yield_values syv ON sy.id = syv.sediment_yield_id " + rrenkert@6392: " JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id " + rrenkert@6392: " JOIN units u ON u.id = sy.unit_id" + rrenkert@6392: " WHERE r.name = :name " + rrenkert@6392: " AND sy.description = :descr " + rrenkert@6392: " AND gf.name = 'unknown' " + rrenkert@6392: " ORDER BY syv.station"; rrenkert@6392: rrenkert@6392: public static final String SQL_SELECT_UNKNOWN = rrenkert@6392: "SELECT DISTINCT " + rrenkert@6392: " sy.description AS description, " + tom@6477: " ti.start_time AS startYear, " + rrenkert@6392: " ti.stop_time AS end " + rrenkert@6392: "FROM sediment_yield sy " + rrenkert@6392: " JOIN rivers r ON sy.river_id = r.id " + rrenkert@6392: " JOIN time_intervals ti ON sy.time_interval_id = ti.id " + rrenkert@6392: " JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id " + felix@6665: " JOIN units u ON sy.unit_id = u.id " + felix@6665: "WHERE r.name = :river " + felix@6665: " AND gf.name = 'unknown' " + felix@6755: " AND sy.kind = :type " + felix@6665: " AND u.name = :unit"; rrenkert@4296: rrenkert@4296: public static final String SQL_SELECT_EPOCHS_DATA = rrenkert@4296: "SELECT" + rrenkert@4370: " sy.description AS description, " + rrenkert@4517: " ti.start_time AS startYear, " + rrenkert@4370: " syv.value AS load, " + rrenkert@4546: " syv.station AS km," + rrenkert@6392: " ti.stop_time AS endYear, " + rrenkert@6392: " u.name AS unit" + rrenkert@4546: " FROM sediment_yield sy " + rrenkert@4296: " JOIN rivers r ON sy.river_id = r.id " + rrenkert@4546: " JOIN time_intervals ti ON sy.time_interval_id = ti.id " + rrenkert@4546: " JOIN sediment_yield_values syv ON sy.id = syv.sediment_yield_id " + rrenkert@4546: " JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id " + rrenkert@6392: " JOIN units u ON u.id = sy.unit_id " + rrenkert@4546: " WHERE r.name = :name " + rrenkert@4546: " AND ti.start_time BETWEEN :sbegin AND :send " + rrenkert@4546: " AND ti.stop_time BETWEEN :ebegin AND :eend " + rrenkert@4296: " AND gf.name = :grain " + rrenkert@6392: " AND syv.station BETWEEN :startKm AND :endKm " + rrenkert@6392: " ORDER BY syv.station"; rrenkert@4296: rrenkert@4296: private SedimentLoadFactory() { rrenkert@4296: } rrenkert@4296: felix@4434: /** felix@6725: * @param river name of river. felix@6725: * @param type felix@4434: */ 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: felix@6740: /** felix@6740: * Get a sedimentLoad filled with data from db (or cache). felix@6948: * @param type "epoch", "off_epoch" or "single" felix@7028: * @return A Sedimentload filled with values from db or cache. felix@6740: */ felix@5661: 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) { felix@7053: log.debug("Store sediment loads 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: 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 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@6392: false, rrenkert@6392: ""); 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) tom@6477: .addScalar("startYear", 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 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@6392: true, rrenkert@6392: ""); rrenkert@4296: } rrenkert@4296: return loads; rrenkert@4296: } felix@6725: else if (type.equals("off_epoch")) { felix@6725: sqlQuery = session.createSQLQuery(SQL_SELECT_OFFEPOCHS) felix@6725: .addScalar("startYear", StandardBasicTypes.DATE) felix@6725: .addScalar("end", StandardBasicTypes.DATE); felix@6725: sqlQuery.setString("name", river); felix@6725: sqlQuery.setDouble("startKm", startKm); felix@6725: sqlQuery.setDouble("endKm", endKm); felix@6725: List results = sqlQuery.list(); felix@6725: felix@6725: SedimentLoad[] loads = new SedimentLoad[results.size()]; felix@6725: for (int i = 0; i < results.size(); i++) { felix@6725: Object[] row = results.get(i); felix@6725: loads[i] = new SedimentLoad( felix@6725: ((Date) row[0]).toString() + (Date) row[1], felix@6725: (Date) row[0], felix@6725: (Date) row[1], felix@6725: true, felix@6725: ""); felix@6725: } felix@6725: return loads; felix@6725: } felix@6725: else { felix@6725: log.warn("getSedimentLoadsUncached does not understand type " + type); felix@6725: } rrenkert@4296: return new SedimentLoad[0]; rrenkert@4296: } rrenkert@4296: rrenkert@4296: /** felix@7181: * Get a specific sediment load from db. felix@7181: * felix@7181: * @param id the sediment yields id. felix@7181: * felix@7181: * @return according sediment load. felix@7181: */ felix@7181: public static SedimentLoad getSedimentLoadWithDataUncached( felix@7181: String id, felix@7181: String river felix@7181: ) { felix@7181: log.debug("SedimentLoadFactory.getSedimentLoadWithDataUncached / id " + id); felix@7181: Session session = SessionHolder.HOLDER.get(); felix@7181: SQLQuery sqlQuery = null; felix@7181: felix@7181: // Measurement stations: all, for float-stuff, for suspended stuff. felix@7181: // Because they need fast sorted access, use TreeMaps. felix@7181: // They map the starting validity range km to the station itself. felix@7181: List allStations = felix@7181: RiverFactory.getRiver(river).getMeasurementStations(); felix@7181: TreeMap floatStations = felix@7181: new TreeMap(); felix@7181: TreeMap suspStations = felix@7181: new TreeMap(); felix@7181: felix@7181: // From all stations, sort into the two kinds, skip undefined ones. felix@7181: for (MeasurementStation measurementStation: allStations) { felix@7181: if (measurementStation.getMeasurementType() == null || felix@7181: measurementStation.getRange() == null) { felix@7181: continue; felix@7181: } felix@7181: if (measurementStation.getMeasurementType().equals("Schwebstoff")) { felix@7181: suspStations.put( felix@7181: measurementStation.getRange().getA().doubleValue(), felix@7181: measurementStation); felix@7181: } felix@7181: else if (measurementStation.getMeasurementType().equals("Geschiebe")) { felix@7181: floatStations.put( felix@7181: measurementStation.getRange().getA().doubleValue(), felix@7181: measurementStation); felix@7181: } felix@7181: } felix@7181: felix@7181: sqlQuery = session.createSQLQuery(SQL_SELECT_SINGLES_DATA_BY_ID) felix@7181: .addScalar("description", StandardBasicTypes.STRING) felix@7181: .addScalar("load", StandardBasicTypes.DOUBLE) felix@7181: .addScalar("km", StandardBasicTypes.DOUBLE) felix@7181: .addScalar("fraction", StandardBasicTypes.STRING) felix@7181: .addScalar("unit", StandardBasicTypes.STRING); felix@7181: sqlQuery.setInteger("id", Integer.valueOf(id)); felix@7181: felix@7181: List results = sqlQuery.list(); felix@7181: SedimentLoad load = new SedimentLoad(); felix@7181: if (results.isEmpty()) { felix@7181: log.warn("Empty result for year calculation."); felix@7181: } felix@7181: else { felix@7181: Object[] row = results.get(0); felix@7181: load = new SedimentLoad( felix@7181: (String) row[0], //description felix@7181: null,//(Date) row[1], //start felix@7181: null, //end felix@7181: false, //isEpoch felix@7181: (String) row[4]); //unit felix@7181: felix@7181: String fraction = (String) row[3]; felix@7181: felix@7181: TreeMap relevantStations = felix@7184: fraction.equals("suspended_sediment") /* || TODO clarify: fraction.equals("susp_sand") */ felix@7181: ? suspStations felix@7181: : floatStations; felix@7181: felix@7181: for (int i = 0; i < results.size(); i++) { felix@7181: row = results.get(i); felix@7181: double km = (Double) row[2]; felix@7181: Range range = findMeasurementStationRange(relevantStations, km); felix@7181: if (range == null) { felix@7181: log.warn("No measurement station for " + fraction + " km " + km); felix@7181: continue; felix@7181: } felix@7181: felix@7181: double v = -1; felix@7181: felix@7181: if (row[1] != null) { felix@7181: v = ((Double)row[1]).doubleValue(); felix@7181: } felix@7181: felix@7181: setLoadFraction(load, km, v, range, fraction); felix@7181: } felix@7181: felix@7181: } felix@7181: felix@7181: return load; felix@7181: } felix@7181: felix@7181: /** rrenkert@4296: * Get sediment loads from db. felix@7181: * rrenkert@4296: * @param river the river felix@6740: * @param type the sediment load type (year, epoch or off_epoch) felix@7180: * 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: Session session = SessionHolder.HOLDER.get(); rrenkert@4296: SQLQuery sqlQuery = null; rrenkert@4296: felix@6948: // Measurement stations: all, for float-stuff, for suspended stuff. felix@6948: // Because they need fast sorted access, use TreeMaps. felix@6948: // They map the starting validity range km to the station itself. felix@6948: List allStations = felix@6948: RiverFactory.getRiver(river).getMeasurementStations(); felix@6948: TreeMap floatStations = felix@6948: new TreeMap(); felix@6948: TreeMap suspStations = felix@6948: new TreeMap(); felix@6948: felix@6948: // From all stations, sort into the two kinds, skip undefined ones. felix@6374: for (MeasurementStation measurementStation: allStations) { felix@6374: if (measurementStation.getMeasurementType() == null || felix@6374: measurementStation.getRange() == null) { felix@6374: continue; felix@6374: } felix@6374: if (measurementStation.getMeasurementType().equals("Schwebstoff")) { felix@6948: suspStations.put( felix@6948: measurementStation.getRange().getA().doubleValue(), felix@6948: measurementStation); felix@6374: } felix@6374: else if (measurementStation.getMeasurementType().equals("Geschiebe")) { felix@6948: floatStations.put( felix@6948: measurementStation.getRange().getA().doubleValue(), felix@6948: measurementStation); felix@6374: } felix@6374: } felix@6374: felix@6948: // Construct date constraint. rrenkert@4370: Calendar start = Calendar.getInstance(); rrenkert@4546: start.set(syear - 1, 11, 31); rrenkert@4296: Calendar end = Calendar.getInstance(); rrenkert@4546: end.set(syear, 11, 30); 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@6392: .addScalar("km", StandardBasicTypes.DOUBLE) rrenkert@6392: .addScalar("unit", StandardBasicTypes.STRING); 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 results = sqlQuery.list(); rrenkert@4296: SedimentLoad load = new SedimentLoad(); felix@6785: if (results.isEmpty()) { felix@6785: log.warn("Empty result for year calculation."); felix@6785: } felix@6785: else { felix@6785: Object[] row = results.get(0); felix@6785: load = new SedimentLoad( felix@6948: (String) row[0], //description felix@6948: (Date) row[1], //start felix@6948: null, //end felix@6948: false, //isEpoch felix@6948: (String) row[4]); //unit felix@6785: } felix@6785: load = getValues("coarse", sqlQuery, load, floatStations); felix@6785: load = getValues("fine_middle", sqlQuery, load, floatStations); felix@6785: load = getValues("sand", sqlQuery, load, floatStations); felix@6785: load = getValues("suspended_sediment", sqlQuery, load, suspStations); felix@6785: load = getValues("susp_sand_bed", sqlQuery, load, floatStations); felix@6785: load = getValues("susp_sand", sqlQuery, load, floatStations); felix@6785: rrenkert@4296: return load; rrenkert@4296: } rrenkert@4370: else if (type.equals("off_epoch")) { felix@6741: // Set calendars to fetch the epochs by their start and end felix@6741: // dates. rrenkert@4370: Calendar toStart = Calendar.getInstance(); rrenkert@4546: toStart.set(eyear - 1, 11, 31); rrenkert@4370: Calendar toEnd = Calendar.getInstance(); felix@6741: toEnd.set(eyear+1, 00, 01); felix@6741: // Set query parameters. rrenkert@4517: sqlQuery = session.createSQLQuery(SQL_SELECT_EPOCHS_DATA) rrenkert@4296: .addScalar("description", StandardBasicTypes.STRING) rrenkert@4517: .addScalar("startYear", StandardBasicTypes.DATE) rrenkert@4517: .addScalar("load", StandardBasicTypes.DOUBLE) rrenkert@4546: .addScalar("km", StandardBasicTypes.DOUBLE) rrenkert@6392: .addScalar("endYear", StandardBasicTypes.DATE) rrenkert@6392: .addScalar("unit", StandardBasicTypes.STRING); 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@4517: sqlQuery.setDate("send", end.getTime()); felix@6741: sqlQuery.setDate("ebegin", toStart.getTime()); rrenkert@4370: sqlQuery.setDate("eend", toEnd.getTime()); rrenkert@4296: sqlQuery.setString("grain", "total"); rrenkert@4296: rrenkert@4546: List results = null; rrenkert@4546: results = sqlQuery.list(); rrenkert@4296: felix@6741: SedimentLoad load = null; felix@6737: if (results.isEmpty()) { felix@6741: log.warn("No total results for off-epoch"); felix@6737: } felix@6741: else { felix@6741: Object[] row = results.get(0); felix@6741: load = new SedimentLoad( felix@6741: (String) row[0], felix@6741: (Date) row[1], felix@6741: (Date) row[4], felix@6741: true, felix@6741: (String)row[5]); felix@6741: TDoubleArrayList kms = new TDoubleArrayList(); felix@6741: for (int i = 0; i < results.size(); i++) { felix@6741: row = results.get(i); felix@6741: kms.add((Double)row[3]); felix@6741: load.setLoadTotal((Double)row[3], (Double)row[2]); felix@6741: } rrenkert@4370: } felix@6741: load = getValues("coarse", sqlQuery, load, floatStations); felix@6741: load = getValues("fine_middle", sqlQuery, load, floatStations); felix@6741: load = getValues("sand", sqlQuery, load, floatStations); felix@6741: load = getValues("suspended_sediment", sqlQuery, load, suspStations); felix@6741: load = getValues("susp_sand_bed", sqlQuery, load, floatStations); felix@6741: load = getValues("susp_sand", sqlQuery, load, floatStations); rrenkert@4296: return load; rrenkert@4296: } rrenkert@4296: return new SedimentLoad(); rrenkert@4296: } rrenkert@4296: felix@6964: protected static Range findMeasurementStationRange( felix@6964: TreeMap stations, felix@6964: double km felix@6964: ) { felix@6984: MeasurementStation station = stations.get(km); felix@6984: if (station == null) { felix@6964: return null; felix@6964: } felix@6967: felix@6967: double endKm; felix@6967: felix@6984: if (stations.ceilingEntry(km + 0.1d) != null) { felix@6984: MeasurementStation nextStation = stations.ceilingEntry(km + 0.1d).getValue(); felix@6967: endKm = nextStation.getRange().getA().doubleValue(); felix@6966: } felix@6964: else { felix@6966: // TODO end-of-river instead of B. felix@6967: endKm = station.getRange().getB().doubleValue(); felix@6964: } felix@6967: felix@6967: return new Range( felix@6967: station.getRange().getA().doubleValue(), felix@6967: endKm); felix@6964: } felix@5662: felix@5789: /** felix@5789: * Run query with grain parameter set to fraction, feed result into felix@6741: * load. Create load if null. felix@6741: * felix@5789: * @param fraction value to set 'grain' parameter in query to. felix@5789: * @param query query in which to set 'grain' parameter and run. felix@6740: * @param load[out] SedimentLoad which to populate with values. if null felix@6740: * and values are found, return a new load. felix@6741: * @return param load or new load if null. felix@5789: */ felix@6741: protected static SedimentLoad getValues ( rrenkert@4296: String fraction, rrenkert@4370: SQLQuery query, felix@6374: SedimentLoad load, felix@6374: TreeMap stations rrenkert@4296: ) { rrenkert@4296: query.setString("grain", fraction); rrenkert@4296: List results = query.list(); felix@6741: felix@6741: // We have the first results for this query, create new load. felix@6741: if (!results.isEmpty() && load == null) { felix@6741: Object[] row = results.get(0); felix@6741: load = new SedimentLoad( felix@6741: (String) row[0], felix@6741: (Date) row[1], felix@6741: (Date) row[4], felix@6741: true, felix@6741: (String)row[5]); felix@6741: } felix@6741: rrenkert@4296: for (int i = 0; i < results.size(); i++) { rrenkert@4296: Object[] row = results.get(i); rrenkert@4370: double km = (Double)row[3]; felix@6964: Range range = findMeasurementStationRange(stations, km); felix@6964: if (range == null) { felix@6374: log.warn("No measurement station for " + fraction + " km " + km); felix@6544: continue; felix@6374: } rrenkert@4370: double v = -1; felix@7180: rrenkert@4370: if (row[2] != null) { rrenkert@4370: v = ((Double)row[2]).doubleValue(); rrenkert@4370: } felix@7180: felix@7180: setLoadFraction(load, km, v, range, fraction); felix@7180: } felix@7180: felix@7180: if (results.isEmpty()) { felix@7180: log.warn("No " + fraction + " values found."); rrenkert@4296: } felix@6741: felix@6741: return load; rrenkert@4296: } rrenkert@6392: felix@7180: felix@7180: /** Set a fraction value of load to given km, value and range. */ felix@7180: private static void setLoadFraction( felix@7180: SedimentLoad load, double km, double v, Range range, String fraction) { felix@7180: if (fraction.equals("coarse")) { felix@7180: load.setCoarse(km, v, range); felix@7180: } felix@7180: else if (fraction.equals("sand")) { felix@7180: load.setSand(km, v, range); felix@7180: } felix@7180: else if (fraction.equals("fine_middle")) { felix@7180: load.setFineMiddle(km, v, range); felix@7180: } felix@7180: else if (fraction.equals("suspended_sediment")) { felix@7180: load.setSuspSediment(km, v, range); felix@7180: } felix@7180: else if (fraction.equals("susp_sand")) { felix@7180: load.setSuspSand(km, v, range); felix@7180: } felix@7180: else if (fraction.equals("susp_sand_bed")) { felix@7180: load.setSuspSandBed(km, v, range); felix@7180: } felix@7180: else { felix@7180: log.error("Unknown fraction type " + fraction); felix@7180: } felix@7180: } felix@7180: rrenkert@6392: public static SedimentLoad getLoadUnknown( rrenkert@6392: String river, rrenkert@6392: String description rrenkert@6392: ) { rrenkert@6392: log.debug("SedimentLoadFactory.getLoadWithData"); rrenkert@6392: Cache cache = CacheFactory.getCache(LOAD_DATA_CACHE_NAME); rrenkert@6392: rrenkert@6392: if (cache == null) { rrenkert@6392: log.debug("Cache not configured."); rrenkert@6392: return getSedimentLoadUnknownUncached(river, description); rrenkert@6392: } rrenkert@6392: rrenkert@6392: StaticSedimentLoadCacheKey key = rrenkert@6392: new StaticSedimentLoadCacheKey(river, 0d, 0d, 0, 0); rrenkert@6392: rrenkert@6392: Element element = cache.get(key); rrenkert@6392: rrenkert@6392: if (element != null) { rrenkert@6392: log.debug("SedimentLoad found in cache"); rrenkert@6392: return (SedimentLoad)element.getValue(); rrenkert@6392: } rrenkert@6392: rrenkert@6392: SedimentLoad values = getSedimentLoadUnknownUncached(river, description); rrenkert@6392: rrenkert@6392: if (values != null && key != null) { rrenkert@6392: log.debug("Store static bed height values in cache."); rrenkert@6392: element = new Element(key, values); rrenkert@6392: cache.put(element); rrenkert@6392: } rrenkert@6392: return values; rrenkert@6392: } rrenkert@6392: rrenkert@6392: /** rrenkert@6392: * Get sediment loads with fraction 'unknown' from db. rrenkert@6392: * @param river the river rrenkert@6392: * @param type the sediment load type (year or epoch) rrenkert@6392: * @return according sediment loads. rrenkert@6392: */ rrenkert@6392: public static SedimentLoad getSedimentLoadUnknownUncached( rrenkert@6392: String river, rrenkert@6392: String description rrenkert@6392: ) { rrenkert@6392: log.debug("SedimentLoadFactory.getSedimentLoadWithDataUncached"); rrenkert@6392: Session session = SessionHolder.HOLDER.get(); rrenkert@6392: SQLQuery sqlQuery = null; rrenkert@6392: rrenkert@6392: sqlQuery = session.createSQLQuery(SQL_SELECT_UNKNOWN_DATA) rrenkert@6392: .addScalar("description", StandardBasicTypes.STRING) tom@6477: .addScalar("startYear", StandardBasicTypes.DATE) rrenkert@6392: .addScalar("end", StandardBasicTypes.DATE) rrenkert@6392: .addScalar("load", StandardBasicTypes.DOUBLE) rrenkert@6392: .addScalar("km", StandardBasicTypes.DOUBLE) rrenkert@6392: .addScalar("unit", StandardBasicTypes.STRING); rrenkert@6392: sqlQuery.setString("name", river); rrenkert@6392: sqlQuery.setString("descr", description); rrenkert@6392: List results = sqlQuery.list(); rrenkert@6392: SedimentLoad load = new SedimentLoad(); rrenkert@6392: if (results.isEmpty()) { rrenkert@6392: return new SedimentLoad(); rrenkert@6392: } rrenkert@6392: Object[] row = results.get(0); rrenkert@6392: load = new SedimentLoad( rrenkert@6392: (String) row[0], rrenkert@6392: (Date) row[1], rrenkert@6392: (Date) row[2], rrenkert@6392: false, rrenkert@6392: (String)row[5]); rrenkert@6392: rrenkert@6392: for (int i = 0; i < results.size(); i++) { rrenkert@6392: row = results.get(i); rrenkert@6392: SedimentLoadFraction fraction = new SedimentLoadFraction(); rrenkert@6392: fraction.setUnknown((Double)row[3]); rrenkert@6392: load.addKm((Double)row[4], fraction); rrenkert@6392: } rrenkert@6392: return load; rrenkert@6392: } rrenkert@6392: felix@6665: /** felix@6665: * Return sediment loads with 'unknown' fraction type. felix@6785: * @param river Name of the river felix@6665: * @param unit Restrict result set to those of given unit. felix@6755: * @param type Type like year, epoch, off_epoch felix@6665: */ felix@6755: public static SedimentLoad[] getSedimentLoadUnknown( felix@6755: String river, felix@6755: String unit, felix@6755: String type) { rrenkert@6392: Session session = SessionHolder.HOLDER.get(); rrenkert@6392: SQLQuery sqlQuery = session.createSQLQuery(SQL_SELECT_UNKNOWN) rrenkert@6392: .addScalar("description", StandardBasicTypes.STRING) tom@6477: .addScalar("startYear", StandardBasicTypes.DATE) rrenkert@6392: .addScalar("end", StandardBasicTypes.DATE); rrenkert@6392: sqlQuery.setString("river", river); felix@6665: sqlQuery.setString("unit", unit); felix@6755: if (type.equals("off_epoch")) { felix@6755: sqlQuery.setInteger("type", 1); felix@6755: } felix@6755: else { felix@6755: sqlQuery.setInteger("type", 0); felix@6755: } rrenkert@6392: List results = sqlQuery.list(); rrenkert@6392: SedimentLoad[] loads = new SedimentLoad[results.size()]; rrenkert@6392: int counter = 0; rrenkert@6392: for (Object[] row: results) { rrenkert@6392: loads[counter] = new SedimentLoad( rrenkert@6392: (String)row[0], (Date)row[1], (Date)row[2], false, ""); rrenkert@6392: counter++; rrenkert@6392: } rrenkert@6392: return loads; rrenkert@6392: } rrenkert@4296: } felix@5662: // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf-8 :