/**
* 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;
}
