# HG changeset patch # User mschaefer # Date 1529411081 -7200 # Node ID 38e8febd11b624b20556ea169768cb69885271ce # Parent 64e56a51db3fe08d828c0a9a32a140662a94bb0e Added methods to select collision values and collision counts diff -r 64e56a51db3f -r 38e8febd11b6 backend/src/main/java/org/dive4elements/river/model/sinfo/CollisionAggregateValue.java --- a/backend/src/main/java/org/dive4elements/river/model/sinfo/CollisionAggregateValue.java Tue Jun 19 14:23:15 2018 +0200 +++ b/backend/src/main/java/org/dive4elements/river/model/sinfo/CollisionAggregateValue.java Tue Jun 19 14:24:41 2018 +0200 @@ -16,6 +16,8 @@ import java.util.List; import org.dive4elements.river.backend.SessionHolder; +import org.dive4elements.river.backend.utils.DateUtil; +import org.dive4elements.river.model.River; import org.hibernate.SQLQuery; import org.hibernate.Session; import org.hibernate.type.StandardBasicTypes; @@ -96,7 +98,7 @@ } /** - * Selects the collision aggregate values of a data series in a km range from the database + * Selects from the database the collision aggregate values of a data series in a km range */ public static List getValuesByKm(final Collision parent, final double kmLo, final double kmHi) { final Session session = SessionHolder.HOLDER.get(); @@ -106,9 +108,11 @@ + " FROM collision_values v INNER JOIN collision s ON v.collision_id=s.id" + " WHERE (collision_id=:parent)" + " AND (station BETWEEN (:kmLo - 0.0001) AND (:kmHi + 0.0001))" - + " GROUP BY FLOOR(station+0.4999)").addScalar("station", StandardBasicTypes.DOUBLE) - .addScalar("count", StandardBasicTypes.INTEGER).addScalar("date_min", StandardBasicTypes.DATE) - .addScalar("date_max", StandardBasicTypes.DATE).addScalar("gauge_w", StandardBasicTypes.DOUBLE); + + " GROUP BY FLOOR(station+0.4999)" + + " ORDER BY FLOOR(station+0.4999)") + .addScalar("station", StandardBasicTypes.DOUBLE).addScalar("count", StandardBasicTypes.INTEGER) + .addScalar("date_min", StandardBasicTypes.DATE).addScalar("date_max", StandardBasicTypes.DATE) + .addScalar("gauge_w", StandardBasicTypes.DOUBLE); query.setInteger("parent", parent.getId()); query.setDouble("kmLo", new Double(kmLo)); query.setDouble("kmHi", new Double(kmHi)); @@ -122,4 +126,37 @@ } return values; } + + /** + * Selects from the database the collision aggregate values of a km range of a river and a range of years + */ + public static List getValuesByKm(final River river, final double kmLo, final double kmHi, final int fromYear, final int toYear) { + final Session session = SessionHolder.HOLDER.get(); + final SQLQuery query = session.createSQLQuery("SELECT FLOOR(station+0.4999) AS station, COUNT(*) AS count," + + " MIN(event_date) AS date_min, MAX(event_date) AS date_max," + + " AVG(gauge_w) AS gauge_w" + + " FROM collision_values v INNER JOIN collision s ON v.collision_id=s.id" + + " WHERE (s.river_id=:river_id)" + + " AND (station BETWEEN (:kmLo - 0.0001) AND (:kmHi + 0.0001))" + + " AND (event_date BETWEEN :fromDate AND :toDate)" + + " GROUP BY FLOOR(station+0.4999)" + + " ORDER BY FLOOR(station+0.4999)") + .addScalar("station", StandardBasicTypes.DOUBLE).addScalar("count", StandardBasicTypes.INTEGER) + .addScalar("date_min", StandardBasicTypes.DATE).addScalar("date_max", StandardBasicTypes.DATE) + .addScalar("gauge_w", StandardBasicTypes.DOUBLE); + query.setInteger("river_id", river.getId()); + query.setDouble("kmLo", new Double(kmLo)); + query.setDouble("kmHi", new Double(kmHi)); + query.setDate("fromDate", DateUtil.getStartDateFromYear(fromYear)); + query.setDate("toDate", DateUtil.getEndDateFromYear(toYear)); + final List values = new ArrayList(); + final List rows = query.list(); + if (rows != null) { + for (int i = 0; i <= rows.size() - 1; i++) { + values.add(new CollisionAggregateValue(null, (double) rows.get(i)[0], (Date) rows.get(i)[2], (Date) rows.get(i)[3], + (int) rows.get(i)[1], (double) rows.get(i)[4])); + } + } + return values; + } } diff -r 64e56a51db3f -r 38e8febd11b6 backend/src/main/java/org/dive4elements/river/model/sinfo/CollisionValue.java --- a/backend/src/main/java/org/dive4elements/river/model/sinfo/CollisionValue.java Tue Jun 19 14:23:15 2018 +0200 +++ b/backend/src/main/java/org/dive4elements/river/model/sinfo/CollisionValue.java Tue Jun 19 14:24:41 2018 +0200 @@ -11,6 +11,7 @@ package org.dive4elements.river.model.sinfo; import java.io.Serializable; +import java.util.ArrayList; import java.util.Date; import java.util.List; @@ -25,8 +26,11 @@ import javax.persistence.Table; import org.dive4elements.river.backend.SessionHolder; +import org.dive4elements.river.model.River; import org.hibernate.Query; +import org.hibernate.SQLQuery; import org.hibernate.Session; +import org.hibernate.type.StandardBasicTypes; /** @@ -164,4 +168,35 @@ query.setParameter("kmHi", new Double(kmHi)); return query.list(); } + /** + * Selects from the database the collision values of a km range of a river and a date range + */ + public static List getValues(final River river, final double kmLo, final double kmHi, final Date fromDate, final Date toDate) { + final Session session = SessionHolder.HOLDER.get(); + final SQLQuery query = session.createSQLQuery("SELECT station, event_date, gauge_w, gauge_name, ct.name AS collisiontype" + + " FROM collision_values v INNER JOIN collision s ON v.collision_id=s.id" + + " INNER JOIN collision_type ct ON v.collision_type_id=ct.id" + + " WHERE (s.river_id=:river_id)" + + " AND (station BETWEEN (:kmLo - 0.0001) AND (:kmHi + 0.0001))" + + " AND (event_date BETWEEN :fromDate AND :toDate)" + + " ORDER BY station, event_date") + .addScalar("station", StandardBasicTypes.DOUBLE) + .addScalar("event_date", StandardBasicTypes.DATE) + .addScalar("gauge_w", StandardBasicTypes.DOUBLE) + .addScalar("gauge_name", StandardBasicTypes.STRING) + .addScalar("collisiontype", StandardBasicTypes.STRING); + query.setInteger("river_id", river.getId()); + query.setDouble("kmLo", new Double(kmLo)); + query.setDouble("kmHi", new Double(kmHi)); + query.setDate("fromDate", fromDate); + query.setDate("toDate", toDate); + return buildCollisionValueList(query.list()); + } + + private static List buildCollisionValueList(final List rows) { + final List values = new ArrayList<>(); + for (int i = 0; i <= rows.size() - 1; i++) + values.add(new CollisionValue(null, (double) rows.get(i)[0], null, (Date) rows.get(i)[1], (String) rows.get(i)[3], (double) rows.get(i)[2])); + return values; + } }