mschaefer@8971: /* Copyright (C) 2017 by Bundesanstalt für Gewässerkunde mschaefer@8971: * Software engineering by mschaefer@8971: * Björnsen Beratende Ingenieure GmbH mschaefer@8971: * Dr. Schumacher Ingenieurbüro für Wasser und Umwelt mschaefer@8971: * mschaefer@8971: * This file is Free Software under the GNU AGPL (>=v3) mschaefer@8971: * and comes with ABSOLUTELY NO WARRANTY! Check out the mschaefer@8971: * documentation coming with Dive4Elements River for details. mschaefer@8971: */ mschaefer@8971: mschaefer@8971: package org.dive4elements.river.model.sinfo; mschaefer@8971: mschaefer@8971: import java.io.Serializable; mschaefer@8971: import java.util.Date; mschaefer@9392: import java.util.List; mschaefer@9409: import java.util.Map; mschaefer@9409: import java.util.TreeMap; mschaefer@8971: mschaefer@8971: import javax.persistence.Column; mschaefer@8971: import javax.persistence.Entity; mschaefer@8971: import javax.persistence.GeneratedValue; mschaefer@8971: import javax.persistence.GenerationType; mschaefer@8971: import javax.persistence.Id; mschaefer@8971: import javax.persistence.JoinColumn; mschaefer@8971: import javax.persistence.OneToOne; mschaefer@8971: import javax.persistence.SequenceGenerator; mschaefer@8971: import javax.persistence.Table; mschaefer@8971: mschaefer@9392: import org.dive4elements.river.backend.SessionHolder; mschaefer@9392: import org.dive4elements.river.model.Gauge; mschaefer@9392: import org.hibernate.Query; mschaefer@9409: import org.hibernate.SQLQuery; mschaefer@9392: import org.hibernate.Session; mschaefer@9409: import org.hibernate.type.StandardBasicTypes; mschaefer@9392: mschaefer@8971: /** mschaefer@8971: * Hibernate binding for the DB table daily_discharge_values mschaefer@8971: * mschaefer@8971: * @author Matthias Schäfer mschaefer@8971: * mschaefer@8971: */ mschaefer@8971: @Entity mschaefer@8971: @Table(name = "daily_discharge_values") mschaefer@8971: public class DailyDischargeValue implements Serializable { mschaefer@8971: mschaefer@9392: /***** TYPES *****/ mschaefer@9392: mschaefer@9392: /** mschaefer@9392: * Field to use in a query's order-by clause mschaefer@9392: * mschaefer@9392: */ mschaefer@9392: public enum OrderByField { mschaefer@9392: DAY, DISCHARGE; mschaefer@9392: } mschaefer@9392: gernotbelger@9405: mschaefer@8971: /***** FIELDS *****/ mschaefer@8971: mschaefer@8971: private static final long serialVersionUID = -6192738825193230784L; mschaefer@8971: mschaefer@8971: private Integer id; mschaefer@8971: mschaefer@8971: private DailyDischarge dailyDischarge; mschaefer@8971: mschaefer@8971: private Date day; mschaefer@8971: mschaefer@8971: private Double discharge; mschaefer@8971: mschaefer@9409: mschaefer@8971: /***** CONSTRUCTORS *****/ mschaefer@8971: mschaefer@8971: public DailyDischargeValue() { mschaefer@8971: } mschaefer@8971: mschaefer@8971: public DailyDischargeValue(final DailyDischarge dailyDischarge, final Date day, final Double discharge) { mschaefer@8971: this.dailyDischarge = dailyDischarge; mschaefer@8971: this.day = day; mschaefer@8971: this.discharge = discharge; mschaefer@8971: } mschaefer@8971: mschaefer@8971: /** mschaefer@8971: * Parameter constructor with primitive parameter types mschaefer@8971: */ mschaefer@8971: public DailyDischargeValue(final DailyDischarge dailyDischarge, final Date day, final double discharge) { mschaefer@8971: this(dailyDischarge, day, Double.valueOf(discharge)); mschaefer@8971: } mschaefer@8971: mschaefer@9409: mschaefer@8971: /***** METHODS *****/ mschaefer@8971: mschaefer@8971: @Id mschaefer@8971: @SequenceGenerator(name = "SEQUENCE_DAILY_DISCHARGE_VALUE_ID_SEQ", sequenceName = "DAILY_DISCHARGE_VALUES_ID_SEQ", allocationSize = 1) mschaefer@8971: @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SEQUENCE_DAILY_DISCHARGE_VALUE_ID_SEQ") mschaefer@8971: @Column(name = "id") mschaefer@8971: public Integer getId() { mschaefer@8971: return this.id; mschaefer@8971: } mschaefer@8971: mschaefer@8971: public void setId(final Integer id) { mschaefer@8971: this.id = id; mschaefer@8971: } mschaefer@8971: mschaefer@8971: @OneToOne mschaefer@8971: @JoinColumn(name = "daily_discharge_id") mschaefer@8971: public DailyDischarge getDailyDischarge() { mschaefer@8971: return this.dailyDischarge; mschaefer@8971: } mschaefer@8971: mschaefer@8971: public void setDailyDischarge(final DailyDischarge dailyDischarge) { mschaefer@8971: this.dailyDischarge = dailyDischarge; mschaefer@8971: } mschaefer@8971: mschaefer@8971: @Column(name = "discharge") mschaefer@8971: public Double getDischarge() { mschaefer@8971: return this.discharge; mschaefer@8971: } mschaefer@8971: mschaefer@8971: public void setDischarge(final Double discharge) { mschaefer@8971: this.discharge = discharge; mschaefer@8971: } mschaefer@8971: mschaefer@8971: @Column(name = "day") mschaefer@8971: public Date getDay() { mschaefer@8971: return this.day; mschaefer@8971: } mschaefer@8971: mschaefer@8971: public void setDay(final Date day) { mschaefer@8971: this.day = day; mschaefer@8971: } mschaefer@9392: mschaefer@9392: /** mschaefer@9392: * Selects from the database the daily discharge values of a gauge and a date range mschaefer@9392: */ mschaefer@9392: public static List getValues(final Gauge gauge, final Date startDate, final Date endDate, final OrderByField orderBy) { mschaefer@9392: final Session session = SessionHolder.HOLDER.get(); mschaefer@9392: final String orderField = (orderBy == OrderByField.DISCHARGE) ? "discharge" : "day"; gernotbelger@9404: final Query query = session.createQuery("SELECT v" + " FROM DailyDischargeValue AS v JOIN v.dailyDischarge AS s" gernotbelger@9404: + " WHERE (s.gauge.id=:gaugeid) AND (v.day BETWEEN :startDate AND :endDate)" + " ORDER BY " + orderField); mschaefer@9392: query.setParameter("gaugeid", gauge.getId()); mschaefer@9392: query.setParameter("startDate", startDate); mschaefer@9392: query.setParameter("endDate", endDate); mschaefer@9392: return query.list(); mschaefer@9392: } gernotbelger@9404: gernotbelger@9404: /** gernotbelger@9404: * Selects from the database the daily discharge, puts the first and the last into a List (min, max day) gernotbelger@9404: */ mschaefer@9409: public static Date[] getTimePeriod(final Gauge gauge, final Date fromDay, final Date toDay) { gernotbelger@9404: final Session session = SessionHolder.HOLDER.get(); mschaefer@9409: final Query query = session.createQuery("SELECT MIN(v.day), MAX(v.day)" mschaefer@9409: + " FROM DailyDischargeValue AS v JOIN v.dailyDischarge AS s" mschaefer@9409: + " WHERE (s.gauge.id=:gaugeid) AND (v.day BETWEEN :startDate AND :endDate)"); gernotbelger@9404: query.setParameter("gaugeid", gauge.getId()); mschaefer@9409: query.setDate("startDate", fromDay); mschaefer@9409: query.setDate("endDate", toDay); mschaefer@9409: final List list = query.list(); mschaefer@9409: if (!list.isEmpty()) { mschaefer@9409: return new Date[] { (Date) list.get(0)[0], (Date) list.get(0)[1] }; gernotbelger@9405: } gernotbelger@9405: return null; gernotbelger@9404: } gernotbelger@9404: mschaefer@9409: /** mschaefer@9409: * Fetches a map of minimum+maximum daily discharge dates (or null) by gauge for a list of gauges and a time period mschaefer@9409: */ mschaefer@9409: public static Map fetchGaugesTimePeriod(final List gauges, final Date fromDay, final Date toDay) { mschaefer@9409: final Map result = new TreeMap<>(); mschaefer@9409: final Map gaugelookup = new TreeMap<>(); mschaefer@9409: String gaugeids = ""; mschaefer@9409: String sep = ""; mschaefer@9409: for (final Gauge gauge : gauges) { mschaefer@9409: gaugeids += sep + gauge.getId().toString(); mschaefer@9409: sep = ","; mschaefer@9409: result.put(gauge, null); mschaefer@9409: gaugelookup.put(gauge.getId(), gauge); mschaefer@9409: } mschaefer@9409: final Session session = SessionHolder.HOLDER.get(); mschaefer@9409: final SQLQuery query = session.createSQLQuery("SELECT g.id AS gauge_id, MIN(v.day) AS min_day, MAX(v.day) AS max_day" mschaefer@9409: + "FROM (gauges g LEFT JOIN daily_discharge s ON g.id=s.gauge_id)" mschaefer@9409: + " LEFT JOIN daily_discharge_values v ON s.id=v.daily_discharge_id" mschaefer@9409: + " WHERE g.id IN (:gaugeids)" mschaefer@9409: + " AND v.day BETWEEN :fromDay AND :toDay" mschaefer@9409: + " GROUP BY g.id" mschaefer@9409: + "UNION SELECT id, NULL, NULL" mschaefer@9409: + " FROM gauges" mschaefer@9409: + " WHERE id IN (:gaugeids)" mschaefer@9409: + " AND id NOT IN " mschaefer@9409: +" (SELECT s2.gauge_id FROM daily_discharge s2 INNER JOIN daily_discharge_values v2 ON s2.id=v2.daily_discharge_id" mschaefer@9409: + " WHERE v2.day BETWEEN :fromDay AND :toDay)" mschaefer@9409: + "ORDER BY 1") mschaefer@9409: .addScalar("gauge_id", StandardBasicTypes.INTEGER) mschaefer@9409: .addScalar("min_day", StandardBasicTypes.DATE) mschaefer@9409: .addScalar("max_day", StandardBasicTypes.DATE); mschaefer@9409: query.setString("gaugeids", gaugeids); mschaefer@9409: query.setDate("fromDay", fromDay); mschaefer@9409: query.setDate("toDay", toDay); mschaefer@9409: final List rows = query.list(); mschaefer@9409: if (!rows.isEmpty()) { mschaefer@9409: // TODO handle nulls mschaefer@9409: for (int i = 0; i <= rows.size() - 1; i++) mschaefer@9409: result.put(gaugelookup.get(rows.get(i)[0]), new Date[] { (Date) rows.get(i)[1], (Date) rows.get(i)[2] }); mschaefer@9409: return result; mschaefer@9409: } mschaefer@9409: return new TreeMap<>(); mschaefer@9409: } mschaefer@8971: }