package net.contargo.iris.routedatarevision.persistence;

import net.contargo.iris.routedatarevision.RouteDataRevision;
import net.contargo.iris.terminal.Terminal;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.math.BigDecimal;
import java.math.BigInteger;

import java.util.Date;
import java.util.List;


/**
 * Repository for {@link net.contargo.iris.routedatarevision.RouteDataRevision RouteDataRevision} instances.
 *
 * @author  Tobias Schneider - schneider@synyx.de
 */
public interface RouteDataRevisionRepository extends JpaRepository<RouteDataRevision, Long> {

    @Query(
        value = "SELECT * "
            + "FROM "
            + "    ("
            + "        SELECT *, "
            + "               (6371000 * acos(cos(radians(:latitude)) * cos(radians(latitude)) "
            + "               * cos(radians(longitude) - radians(:longitude)) + sin(radians(:latitude)) "
            + "               * sin(radians(latitude)))) AS distance "
            + "        FROM RouteDataRevision "
            + "        WHERE terminal_id = :terminal AND "
            + "              validFrom <= :date AND "
            + "                 (validTo >= :date OR validTo IS NULL) "
            + "    ) as dis "
            + "WHERE dis.distance <= dis.radius "
            + "ORDER BY dis.distance ASC "
            + "LIMIT 0, 1", nativeQuery = true
    )
    RouteDataRevision findNearest(@Param("terminal") Terminal terminal,
        @Param("latitude") BigDecimal latitude,
        @Param("longitude") BigDecimal longitude,
        @Param("date") Date date);


    @Query("SELECT r FROM RouteDataRevision r WHERE r.terminal.id = ?1")
    List<RouteDataRevision> findByTerminalId(Long id);


    @Query("SELECT r FROM RouteDataRevision r WHERE r.terminal.uniqueId = ?1 and r.latitude = ?2 and longitude = ?3")
    List<RouteDataRevision> findByTerminalAndLatitudeAndLongitude(BigInteger terminalUniqueId, BigDecimal latitude,
        BigDecimal longitude);
}

@@ -608,12 +608,61 @@
 .de%0A
+ * @author  David Schilling - schilling@synyx.de%0A
  */%0A
-
 publ
@@ -917,16 +917,111 @@
  * acos(
+ %22%0A            + %22                 GREATEST(-1, LEAST(1, %22%0A            + %22                     
 cos(radi
@@ -1070,32 +1070,38 @@
 %0A            + %22
+      
                *
@@ -1205,16 +1205,22 @@
         
+      
 * sin(ra
@@ -1235,16 +1235,85 @@
 titude))
+%22%0A            + %22                 ))%22%0A            + %22                
 )) AS di

Fix route revision find nearest calculation