/**
* Get closest node by latitude and longitude
* This function use fast, but not exact algorithm
*
* @param float $latitude
* @param float $longitude
* @param int $type_id
* @param int $cacheMinutes
*/
public function getClosestNode($latitude, $longitude, $type_id = null, $cacheMinutes = null)
{
if (empty($latitude) or empty($longitude)) {
return false;
}
$qb = new QueryBuilder();
$qb->select(new Field('*'))->from(Tbl::get('TBL_TREE'))->where($qb->expr()->equal(new Field('lat'), $latitude))->andWhere($qb->expr()->equal(new Field('lng'), $longitude));
if ($type_id !== null) {
$qb->andWhere($qb->expr()->equal(new Field('type_id'), $type_id));
}
$qb->limit(1);
$this->query->exec($qb->getSQL(), $cacheMinutes);
if ($this->query->countRecords()) {
return $this->query->fetchRecord();
}
// If above query doesn't return record, find in some range of posible latitude and longitude
// As $latitude, $longitude are decimal(8,4) find with 3 digits to the right of the decimal point
$digits_after = 3;
while ($digits_after > -2) {
//2 digits to the left of the decimal point
$pow = pow(10, $digits_after);
$lat_min = $latitude - 1 / 2 / $pow;
$lat_max = $latitude + 1 / 2 / $pow;
$lng_min = $longitude - 1 / 2 / $pow;
$lng_max = $longitude + 1 / 2 / $pow;
$qb = new QueryBuilder();
$qb->select(new Field('*'))->from(Tbl::get('TBL_TREE'))->where($qb->expr()->between(new Field('lat'), $lat_min, $lat_max))->andWhere($qb->expr()->between(new Field('lng'), $lng_min, $lng_max));
$this->query->exec($qb->getSQL());
if ($this->query->countRecords()) {
return $this->query->fetchRecord();
}
$digits_after--;
//decrease accuracy
}
//////////////////////////////////////////////////////
// Appear here only if there is no such record //
// in wgps_tree table with zero precision/accuracy. //
//////////////////////////////////////////////////////
return null;
}
/**
* Generated from @assert limit(10, 1)->text() [==] " LIMIT 1,10".
*
* @covers Kotchasan\Database\QueryBuilder::limit
*/
public function testLimit2()
{
$this->assertEquals(" LIMIT 1,10", $this->object->limit(10, 1)->text());
}
} else {
$total_results = $peregrine->session->getInt('last_query_total_results');
}
// Order by
if (defined('DEFAULT_ORDER_BY') && DEFAULT_ORDER_BY != '') {
$qb->order(DEFAULT_ORDER_BY);
}
$per_page = $peregrine->post->getInt('per_page');
// Try to ensure it's somewhat sensible
if ($per_page <= 0 || $per_page > 10000) {
$per_page = 25;
}
$response = array('results' => false, 'total_results' => $total_results, 'per_page' => $per_page, 'pages' => $total_results > 0 ? ceil($total_results / $per_page) : 0, 'curr_page' => $peregrine->post->getInt('curr_page'), 'sql_hash' => $sql_hash, 'session_hash' => $peregrine->session->getAlnum('sql_conditions_hash'));
// Limit
$offset = ($response['curr_page'] - 1) * $response['per_page'];
$qb->limit($offset, $response['per_page']);
// Merge sql
$sql = $qb->getQuery();
if (defined('WEB_UI_DEBUG') && WEB_UI_DEBUG) {
print $sql;
exit;
}
$date_format = 'Y-m-d H:i:s';
if (defined('DEFAULT_DATE_FORMAT') && DEFAULT_DATE_FORMAT) {
$date_format = DEFAULT_DATE_FORMAT;
}
$statement = $db->query($sql);
$statement->setFetchMode(PDO::FETCH_ASSOC);
if ($statement->rowCount()) {
$results = array();
$blocks = $prism->getItemList();
