/** * Build the sql query to filter the data * * @param object $params plugin params * * @return string query's where statement */ protected function getQuery($params) { $input = $this->app->input; $lookUps = $input->get('radius_lookup' . $this->renderOrder, array(), 'array'); $lookUps = array_filter($lookUps, function ($v) { return (string) $v === '1'; }); $ids = array_keys($lookUps); $ids = ArrayHelper::toInteger($ids); $listModel = new FabrikFEModelList(); $listModel->setId($params->get('radius_lookup_list')); $listModel->setLimits(0, -1); $key = $listModel->getPrimaryKey(); $listModel->setPluginQueryWhere('list.radius_lookup', $key . ' IN (' . implode(',', $ids) . ')'); $data = $listModel->getData(); $distanceField = $params->get('distance_field') . '_raw'; $data = $listModel->getData(); list($latitude, $longitude) = $this->getSearchLatLon(); if (trim($latitude) === '' && trim($longitude) === '') { $input->set('radius_search_active' . $this->renderOrder, array(0)); return; } // Need to unset for multiple radius searches to work unset($this->mapElement); $el = $this->getMapElement(); $el = FabrikString::safeColName($el->getFullName(false, false)); // Crazy sql to get the lat/lon from google map element $latField = "SUBSTRING_INDEX(TRIM(LEADING '(' FROM {$el}), ',', 1)"; $lonField = "SUBSTRING_INDEX(SUBSTRING_INDEX({$el}, ',', -1), ')', 1)"; $query = array(); $unit = $params->get('radius_lookup_unit', 'km'); foreach ($data as $group) { foreach ($group as $row) { $v = $row->{$distanceField}; if ($unit == 'km') { $query[] = "((((acos(sin((" . $latitude . "*pi()/180)) * sin(({$latField} *pi()/180))+cos((" . $latitude . "*pi()/180)) * cos(({$latField} *pi()/180)) * cos(((" . $longitude . "- {$lonField})*pi()/180))))*180/pi())*60*1.1515*1.609344) <= " . $v . ')'; } else { $query[] = "((((acos(sin((" . $latitude . "*pi()/180)) * sin(({$latField} *pi()/180))+cos((" . $latitude . "*pi()/180)) * cos(({$latField} *pi()/180)) * cos(((" . $longitude . "- {$lonField})*pi()/180))))*180/pi())*60*1.1515) <= " . $v . ')'; } } } $query = '(' . implode(' OR ', $query) . ')'; return $query; }