# HG changeset patch
# User Ingo Weinzierl <ingo.weinzierl@intevation.de>
# Date 1329374866 0
# Node ID 98a350bb91a9619187fcedbc2ed6679ca5aa57fd
# Parent  899ca89f497e23992c6ef8dec14dd77e3a251fb1
Fixed broken HYKs in crosssection charts for river Mosel.

flys-artifacts/trunk@4053 c6561f87-3c4e-4783-a992-168aeb5c3f6f

diff -r 899ca89f497e -r 98a350bb91a9 flys-artifacts/ChangeLog
--- a/flys-artifacts/ChangeLog	Tue Feb 14 16:48:13 2012 +0000
+++ b/flys-artifacts/ChangeLog	Thu Feb 16 06:47:46 2012 +0000
@@ -1,3 +1,8 @@
+2012-02-15  Ingo Weinzierl <ingo@intevation.de>
+
+	* src/main/java/de/intevation/flys/artifacts/model/HYKFactory.java: Fixed
+	  SQL statement to fetch hyk formations for rivers with 'km_up' = 0.
+
 2012-02-14  Sascha L. Teichmann	<sascha.teichmann@intevation.de>
 
 	Another partial fix for flys/issue499: Do the W to Q conversions
diff -r 899ca89f497e -r 98a350bb91a9 flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/HYKFactory.java
--- a/flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/HYKFactory.java	Tue Feb 14 16:48:13 2012 +0000
+++ b/flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/HYKFactory.java	Thu Feb 16 06:47:46 2012 +0000
@@ -14,6 +14,8 @@
 
 import org.hibernate.Query;
 import org.hibernate.Session;
+import org.hibernate.SQLQuery;
+import org.hibernate.type.StandardBasicTypes;
 
 import de.intevation.flys.model.HYK;
 import de.intevation.flys.model.HYKFormation;
@@ -94,7 +96,6 @@
      * @return according zones.
      */
     public static List<Zone> getZonesUncached(int hykid, double km) {
-
         if (logger.isDebugEnabled()) {
             logger.debug("HYKFactory.getZoneUncached " + hykid + " km " + km);
         }
@@ -107,23 +108,11 @@
         rQuery.setParameter("hykid", hykid);
         rQuery.setMaxResults(1);
         HYK hyk = (HYK) rQuery.uniqueResult();
-        BigDecimal flowDir = (hyk.getRiver().getKmUp()) ? new BigDecimal(1) : new BigDecimal(-1);
 
-        // Query HYKFormations in range.
-        Query query = session.createQuery(
-            "from HYKFormation where entry.HYK.id = :hykid " +
-            " and :km between entry.km and " +
-            "                 entry.km + :flowDir * "+
-            "                     cast(distance_vl/1000.0 + 0.001 as big_decimal)" +
-            " order by entry.km asc");
-        query.setParameter("hykid",   hykid);
-        query.setParameter("km",      new BigDecimal(km));
-        query.setParameter("flowDir", flowDir);
-        query.setMaxResults(1);
+        double flowDir = hyk.getRiver().getKmUp() ? 1 : -1;
 
-        List<HYKFormation> forms = query.list();
-
-        List<Zone> zones = new ArrayList<Zone>();
+        List<HYKFormation> forms = getHYKFormations(hykid, km, flowDir);
+        List<Zone>         zones = new ArrayList<Zone>();
 
         // Take the first one.
         if (forms.size() >= 1) {
@@ -140,6 +129,59 @@
         return zones;
     }
 
+
+    protected static List<HYKFormation> getHYKFormations(
+        int        hykid,
+        double     km,
+        double     flowDir
+    ) {
+        Session session = SessionHolder.HOLDER.get();
+
+        String SQL = "SELECT " +
+            "   f.id          AS FID, " +
+            "   f.distance_vl AS DIST, " +
+            "   e.hyk_id      AS HID, " +
+            "   e.km          AS KM " +
+            " FROM hyk_formations f INNER JOIN hyk_entries e " +
+            "   ON e.id = f.hyk_entry_id " +
+            " WHERE e.hyk_id = :hykid " +
+            "   AND :km between " +
+            "     LEAST(e.km, e.km + :flowDir*(f.distance_vl/1000.0+0.001)) " +
+            "   AND " +
+            "     GREATEST(e.km, e.km + :flowDir*(f.distance_vl/1000.0+0.001))";
+
+        SQLQuery sqlQuery = session.createSQLQuery(SQL)
+            .addScalar("FID", StandardBasicTypes.INTEGER)
+            .addScalar("DIST", StandardBasicTypes.DOUBLE)
+            .addScalar("HID", StandardBasicTypes.INTEGER)
+            .addScalar("KM", StandardBasicTypes.DOUBLE);
+
+        sqlQuery.setInteger("hykid", hykid);
+        sqlQuery.setDouble("flowDir", flowDir);
+        sqlQuery.setDouble("km", km);
+
+        logger.debug("HYK SQL: " + sqlQuery.getQueryString());
+
+        List<Object[]> results = sqlQuery.list();
+
+        logger.debug("Found " + results.size() + " HYKFormation IDs in DB.");
+
+        if (results == null || results.isEmpty()) {
+            logger.debug("No HYK found for ID " + hykid + " at km " + km);
+            return new ArrayList<HYKFormation>();
+        }
+
+        Object[] resultSet      = results.get(0);
+        Integer  hykFormationId = (Integer) resultSet[0];
+
+        Query query = session.createQuery("from HYKFormation where id = :id");
+        query.setParameter("id", hykFormationId);
+        query.setMaxResults(1);
+
+        return query.list();
+    }
+
+
     /** Labelled section. */
     public static class Zone implements Serializable {
         /** Lower end of segment. */