Mercurial > dive4elements > river
diff backend/src/main/java/org/dive4elements/river/model/sinfo/DailyDischargeValue.java @ 9409:38201f5b0dd9
Changed bundu bzws workflow to stop in case of missing daily discharge values and other minor changes
author | mschaefer |
---|---|
date | Thu, 16 Aug 2018 08:47:41 +0200 |
parents | 34cd4faf43f4 |
children |
line wrap: on
line diff
--- a/backend/src/main/java/org/dive4elements/river/model/sinfo/DailyDischargeValue.java Thu Aug 16 08:44:26 2018 +0200 +++ b/backend/src/main/java/org/dive4elements/river/model/sinfo/DailyDischargeValue.java Thu Aug 16 08:47:41 2018 +0200 @@ -13,6 +13,8 @@ import java.io.Serializable; import java.util.Date; import java.util.List; +import java.util.Map; +import java.util.TreeMap; import javax.persistence.Column; import javax.persistence.Entity; @@ -27,7 +29,9 @@ import org.dive4elements.river.backend.SessionHolder; import org.dive4elements.river.model.Gauge; import org.hibernate.Query; +import org.hibernate.SQLQuery; import org.hibernate.Session; +import org.hibernate.type.StandardBasicTypes; /** * Hibernate binding for the DB table daily_discharge_values @@ -49,9 +53,6 @@ DAY, DISCHARGE; } - public enum MinMax { - min, max - } /***** FIELDS *****/ @@ -65,6 +66,7 @@ private Double discharge; + /***** CONSTRUCTORS *****/ public DailyDischargeValue() { @@ -83,6 +85,7 @@ this(dailyDischarge, day, Double.valueOf(discharge)); } + /***** METHODS *****/ @Id @@ -142,17 +145,62 @@ /** * Selects from the database the daily discharge, puts the first and the last into a List (min, max day) */ - - public static Date getGlobalMinMax(final Gauge gauge, final MinMax minmax) { + public static Date[] getTimePeriod(final Gauge gauge, final Date fromDay, final Date toDay) { final Session session = SessionHolder.HOLDER.get(); - final Query query = session.createQuery( - "SELECT " + minmax.toString() + "(v.day) " + " FROM DailyDischargeValue AS v JOIN v.dailyDischarge AS s" + " WHERE (s.gauge.id=:gaugeid)"); + final Query query = session.createQuery("SELECT MIN(v.day), MAX(v.day)" + + " FROM DailyDischargeValue AS v JOIN v.dailyDischarge AS s" + + " WHERE (s.gauge.id=:gaugeid) AND (v.day BETWEEN :startDate AND :endDate)"); query.setParameter("gaugeid", gauge.getId()); - final List<Date> list = query.list(); - if (list != null && list.size() == 1) { - return list.get(0); + query.setDate("startDate", fromDay); + query.setDate("endDate", toDay); + final List<Object[]> list = query.list(); + if (!list.isEmpty()) { + return new Date[] { (Date) list.get(0)[0], (Date) list.get(0)[1] }; } return null; } + /** + * Fetches a map of minimum+maximum daily discharge dates (or null) by gauge for a list of gauges and a time period + */ + public static Map<Gauge, Date[]> fetchGaugesTimePeriod(final List<Gauge> gauges, final Date fromDay, final Date toDay) { + final Map<Gauge, Date[]> result = new TreeMap<>(); + final Map<Integer, Gauge> gaugelookup = new TreeMap<>(); + String gaugeids = ""; + String sep = ""; + for (final Gauge gauge : gauges) { + gaugeids += sep + gauge.getId().toString(); + sep = ","; + result.put(gauge, null); + gaugelookup.put(gauge.getId(), gauge); + } + final Session session = SessionHolder.HOLDER.get(); + final SQLQuery query = session.createSQLQuery("SELECT g.id AS gauge_id, MIN(v.day) AS min_day, MAX(v.day) AS max_day" + + "FROM (gauges g LEFT JOIN daily_discharge s ON g.id=s.gauge_id)" + + " LEFT JOIN daily_discharge_values v ON s.id=v.daily_discharge_id" + + " WHERE g.id IN (:gaugeids)" + + " AND v.day BETWEEN :fromDay AND :toDay" + + " GROUP BY g.id" + + "UNION SELECT id, NULL, NULL" + + " FROM gauges" + + " WHERE id IN (:gaugeids)" + + " AND id NOT IN " + +" (SELECT s2.gauge_id FROM daily_discharge s2 INNER JOIN daily_discharge_values v2 ON s2.id=v2.daily_discharge_id" + + " WHERE v2.day BETWEEN :fromDay AND :toDay)" + + "ORDER BY 1") + .addScalar("gauge_id", StandardBasicTypes.INTEGER) + .addScalar("min_day", StandardBasicTypes.DATE) + .addScalar("max_day", StandardBasicTypes.DATE); + query.setString("gaugeids", gaugeids); + query.setDate("fromDay", fromDay); + query.setDate("toDay", toDay); + final List<Object[]> rows = query.list(); + if (!rows.isEmpty()) { + // TODO handle nulls + for (int i = 0; i <= rows.size() - 1; i++) + result.put(gaugelookup.get(rows.get(i)[0]), new Date[] { (Date) rows.get(i)[1], (Date) rows.get(i)[2] }); + return result; + } + return new TreeMap<>(); + } }