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: 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: 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@4434: /** 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: felix@4434: /** 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@4370: " syv.value AS load, " + rrenkert@4370: " syv.station AS km " + 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@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@4296: " AND syv.station BETWEEN :startKm AND :endKm"; 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@4546: " ti.stop_time AS endYear " + 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@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@4296: " AND syv.station BETWEEN :startKm AND :endKm"; rrenkert@4296: rrenkert@4296: private SedimentLoadFactory() { rrenkert@4296: } rrenkert@4296: felix@4434: /** felix@4434: * 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@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) { 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: 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@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 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@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: 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@4370: .addScalar("km", StandardBasicTypes.DOUBLE); 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(); 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@4370: getValues("coarse", sqlQuery, load); rrenkert@4370: getValues("fine_middle", sqlQuery, load); rrenkert@4370: getValues("sand", sqlQuery, load); rrenkert@4370: getValues("suspended_sediment", sqlQuery, load); rrenkert@4370: getValues("susp_sand_bed", sqlQuery, load); rrenkert@4370: getValues("susp_sand", sqlQuery, load); teichmann@4736: rrenkert@4296: return load; rrenkert@4296: } rrenkert@4370: else if (type.equals("off_epoch")) { rrenkert@4370: Calendar toStart = Calendar.getInstance(); rrenkert@4546: toStart.set(eyear - 1, 11, 31); rrenkert@4370: Calendar toEnd = Calendar.getInstance(); rrenkert@4546: toEnd.set(eyear, 11, 30); 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@4546: .addScalar("endYear", StandardBasicTypes.DATE); 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()); rrenkert@4370: 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: rrenkert@4296: SedimentLoad load = new SedimentLoad(); rrenkert@4296: Object[] row = results.get(0); rrenkert@4296: load = new SedimentLoad( rrenkert@4296: (String) row[0], rrenkert@4296: (Date) row[1], rrenkert@4546: (Date) row[4], rrenkert@4546: true); rrenkert@4370: TDoubleArrayList kms = new TDoubleArrayList(); rrenkert@4370: for (int i = 0; i < results.size(); i++) { rrenkert@4370: row = results.get(i); rrenkert@4370: kms.add((Double)row[3]); rrenkert@4546: load.setLoadTotal((Double)row[3], (Double)row[2]); rrenkert@4370: } rrenkert@4370: getValues("coarse", sqlQuery, load); rrenkert@4370: getValues("fine_middle", sqlQuery, load); rrenkert@4370: getValues("sand", sqlQuery, load); rrenkert@4370: getValues("suspended_sediment", sqlQuery, load); rrenkert@4370: getValues("susp_sand_bed", sqlQuery, load); rrenkert@4370: getValues("susp_sand", sqlQuery, load); rrenkert@4296: return load; rrenkert@4296: } rrenkert@4296: return new SedimentLoad(); rrenkert@4296: } rrenkert@4296: felix@5662: felix@5789: /** felix@5789: * Run query with grain parameter set to fraction, feed result into felix@5789: * load. 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@5789: * @param load[out] SedimentLoad which to populate with values. felix@5789: */ rrenkert@4370: protected static void getValues ( rrenkert@4296: String fraction, rrenkert@4370: SQLQuery query, rrenkert@4370: SedimentLoad load rrenkert@4296: ) { rrenkert@4296: query.setString("grain", fraction); rrenkert@4296: List results = query.list(); rrenkert@4296: for (int i = 0; i < results.size(); i++) { rrenkert@4296: Object[] row = results.get(i); rrenkert@4370: double km = (Double)row[3]; rrenkert@4370: double v = -1; rrenkert@4370: if (row[2] != null) { rrenkert@4370: v = ((Double)row[2]).doubleValue(); rrenkert@4370: } rrenkert@4370: if (fraction.equals("coarse")) { rrenkert@4370: load.setCoarse(km, v); rrenkert@4370: } rrenkert@4370: else if (fraction.equals("sand")) { rrenkert@4370: load.setSand(km, v); rrenkert@4370: } rrenkert@4370: else if (fraction.equals("fine_middle")) { rrenkert@4370: load.setFineMiddle(km, v); rrenkert@4370: } rrenkert@4370: else if (fraction.equals("suspended_sediment")) { rrenkert@4370: load.setSuspSediment(km, v); rrenkert@4370: } rrenkert@4517: else if (fraction.equals("susp_sand")) { rrenkert@4370: load.setSuspSand(km, v); rrenkert@4370: } rrenkert@4370: else if (fraction.equals("susp_sand_bed")) { rrenkert@4370: load.setSuspSandBed(km, v); rrenkert@4370: } rrenkert@4296: } rrenkert@4296: } rrenkert@4296: } felix@5662: // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf-8 :