Merge pull request #3914 from eileenmcnaughton/CRM-15168

[civicrm-core.git] / CRM / Contact / BAO / ProximityQuery.php

diff --git a/CRM/Contact/BAO/ProximityQuery.php b/CRM/Contact/BAO/ProximityQuery.php
index 87b14ab59174c5dd415ca7b334f9e969fa4ef07b..b5e065df0fa7459126cae5589dd0a9f00ab8562f 100644 (file)
--- a/CRM/Contact/BAO/ProximityQuery.php
+++ b/CRM/Contact/BAO/ProximityQuery.php
@@ -1,9 +1,9 @@
 <?php
 /*
  +--------------------------------------------------------------------+
- | CiviCRM version 4.4                                                |
+ | CiviCRM version 4.5                                                |
  +--------------------------------------------------------------------+
- | Copyright CiviCRM LLC (c) 2004-2013                                |
+ | Copyright CiviCRM LLC (c) 2004-2014                                |
  +--------------------------------------------------------------------+
  | This file is a part of CiviCRM.                                    |
  |                                                                    |
@@ -28,7 +28,7 @@
 /**
  *
  * @package CRM
- * @copyright CiviCRM LLC (c) 2004-2013
+ * @copyright CiviCRM LLC (c) 2004-2014
  * $Id$
  *
  */
@@ -36,6 +36,11 @@ class CRM_Contact_BAO_ProximityQuery {
 
   /**
    * Trigonometry for calculating geographical distances.
+   *
+   * Modification made in: CRM-13904
+   * http://en.wikipedia.org/wiki/Great-circle_distance
+   * http://www.movable-type.co.uk/scripts/latlong.html
+   *
    * All function arguments and return values measure distances in metres
    * and angles in degrees.  The ellipsoid model is from the WGS-84 datum.
    * Ka-Ping Yee, 2003-08-11
@@ -116,24 +121,6 @@ class CRM_Contact_BAO_ProximityQuery {
     return deg2rad($angle) * self::earthRadius($latitude);
   }
 
-  /**
-   * Estimate the earth-surface distance between two locations.
-   */
-  static function earthDistance($longitudeSrc, $latitudeSrc,
-    $longitudeDst, $latitudeDst
-  ) {
-
-    $longSrc = deg2rad($longitudeSrc);
-    $latSrc  = deg2rad($latitudeSrc);
-    $longDst = deg2rad($longitudeDst);
-    $latDst  = deg2rad($latitudeDst);
-
-    $radius = self::earthRadius(($latitudeSrc + $latitudeDst) / 2);
-
-    $cosAngle = cos($latSrc) * cos($latDst) * (cos($longSrc) * cos($longDst) + sin($longSrc) * sin($longDst)) + sin($latSrc) * sin($latDst);
-    return acos($cosAngle) * $radius;
-  }
-
   /**
    * Estimate the min and max longitudes within $distance of a given location.
    */
@@ -198,60 +185,68 @@ class CRM_Contact_BAO_ProximityQuery {
     );
   }
 
-  /*
-     * Returns the SQL fragment needed to add a column called 'distance'
-     * to a query that includes the location table
-     *
-     * @param $longitude
-     * @param $latitude
-     */
-
-  static function earthDistanceSQL($longitude, $latitude) {
-    $long   = deg2rad($longitude);
-    $lat    = deg2rad($latitude);
-    $radius = self::earthRadius($latitude);
-
-    $cosLong = cos($long);
-    $cosLat  = cos($lat);
-    $sinLong = sin($long);
-    $sinLat  = sin($lat);
-
-    return "
-IFNULL( ACOS( $cosLat * COS( RADIANS( $latitude ) ) *
-              ( $cosLong * COS( RADIANS( $longitude ) ) +
-                $sinLong * SIN( RADIANS( $longitude ) ) ) +
-              $sinLat  * SIN( RADIANS( $latitude  ) ) ), 0.00000 ) * $radius
-";
-  }
-
+  /**
+   * @param $latitude
+   * @param $longitude
+   * @param $distance
+   * @param string $tablePrefix
+   *
+   * @return string
+   */
   static function where($latitude, $longitude, $distance, $tablePrefix = 'civicrm_address') {
     self::initialize();
 
     $params = array();
     $clause = array();
 
-    list($minLongitude, $maxLongitude) = self::earthLongitudeRange($longitude,
-      $latitude,
-      $distance
-    );
-    list($minLatitude, $maxLatitude) = self::earthLatitudeRange($longitude,
-      $latitude,
-      $distance
-    );
-
-    $earthDistanceSQL = self::earthDistanceSQL($longitude, $latitude);
+    list($minLongitude, $maxLongitude) =
+      self::earthLongitudeRange($longitude,
+        $latitude,
+        $distance
+      );
+    list($minLatitude, $maxLatitude) =
+      self::earthLatitudeRange(
+        $longitude,
+        $latitude,
+        $distance
+      );
+
+    // DONT consider NAN values (which is returned by rad2deg php function)
+    // for checking BETWEEN geo_code's criteria as it throws obvious 'NAN' field not found DB: Error
+    $geoCodeWhere = array();
+    if (!is_nan($minLatitude)) {
+      $geoCodeWhere[] = "{$tablePrefix}.geo_code_1  >= $minLatitude ";
+    }
+    if (!is_nan($maxLatitude)) {
+      $geoCodeWhere[] = "{$tablePrefix}.geo_code_1  <= $maxLatitude ";
+    }
+    if (!is_nan($minLongitude)) {
+      $geoCodeWhere[] = "{$tablePrefix}.geo_code_2 >= $minLongitude ";
+    }
+    if (!is_nan($maxLongitude)) {
+      $geoCodeWhere[] = "{$tablePrefix}.geo_code_2 <= $maxLongitude ";
+    }
+    $geoCodeWhereClause = implode(' AND ', $geoCodeWhere);
 
     $where = "
-{$tablePrefix}.geo_code_1  >= $minLatitude  AND
-{$tablePrefix}.geo_code_1  <= $maxLatitude  AND
-{$tablePrefix}.geo_code_2 >= $minLongitude AND
-{$tablePrefix}.geo_code_2 <= $maxLongitude AND
-$earthDistanceSQL  <= $distance
+{$geoCodeWhereClause} AND
+ACOS(
+    COS(RADIANS({$tablePrefix}.geo_code_1)) *
+    COS(RADIANS($latitude)) *
+    COS(RADIANS({$tablePrefix}.geo_code_2) - RADIANS($longitude)) +
+    SIN(RADIANS({$tablePrefix}.geo_code_1)) *
+    SIN(RADIANS($latitude))
+  ) * 6378137  <= $distance
 ";
-
     return $where;
   }
 
+  /**
+   * @param $query
+   * @param $values
+   *
+   * @throws Exception
+   */
   static function process(&$query, &$values) {
     list($name, $op, $distance, $grouping, $wildcard) = $values;
 
@@ -294,7 +289,7 @@ $earthDistanceSQL  <= $distance
     if (!isset($proximityAddress['country_id'])) {
       // get it from state if state is present
       if (isset($proximityAddress['state_province_id'])) {
-        $proximityAddress['country_id'] = CRM_Core_PseudoConstant::countryForState($proximityAddress['state_province_id']);
+        $proximityAddress['country_id'] = CRM_Core_PseudoConstant::countryIDForStateID($proximityAddress['state_province_id']);
       }
       elseif (isset($config->defaultContactCountry)) {
         $proximityAddress['country_id'] = $config->defaultContactCountry;
@@ -356,6 +351,9 @@ $earthDistanceSQL  <= $distance
     return;
   }
 
+  /**
+   * @param $input
+   */
   static function fixInputParams(&$input) {
     foreach ($input as $param) {
       if (CRM_Utils_Array::value('0', $param) == 'prox_distance') {