Mercurial > dive4elements > river

changeset 9160:38e8febd11b6

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added methods to select collision values and collision counts
author mschaefer
date Tue, 19 Jun 2018 14:24:41 +0200
parents 64e56a51db3f
children c537b3038857
files backend/src/main/java/org/dive4elements/river/model/sinfo/CollisionAggregateValue.java backend/src/main/java/org/dive4elements/river/model/sinfo/CollisionValue.java
diffstat 2 files changed, 76 insertions(+), 4 deletions(-) [+]
line wrap: on
line diff
--- 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<CollisionAggregateValue> 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<CollisionAggregateValue> 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<CollisionAggregateValue> values = new ArrayList();
+        final List<Object[]> 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;
+    }
 }
--- 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<CollisionValue> 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<CollisionValue> buildCollisionValueList(final List<Object[]> rows) {
+        final List<CollisionValue> 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;
+    }
 }
http://dive4elements.wald.intevation.org