public static function createLocalCaches($dbcLocCache, $lon, $lat, $distance, $user_id)
{
$max_lat_diff = $distance / 111.12;
$max_lon_diff = $distance * 180 / (abs(sin((90 - $lat) * 3.14159 / 180)) * 6378 * 3.14159);
$sqlstr = 'CREATE TEMPORARY TABLE local_caches ENGINE=MEMORY
SELECT
(' . getSqlDistanceFormula($lon, $lat, $distance, 1) . ') AS `distance`,
`caches`.`cache_id` AS `cache_id`,
`caches`.`wp_oc` AS `wp_oc`,
`caches`.`type` AS `type`,
`caches`.`name` AS `name`,
`caches`.`longitude` AS `longitude`,
`caches`.`latitude` AS `latitude`,
`caches`.`date_hidden` AS `date_hidden`,
`caches`.`date_created` AS `date_created`,
`caches`.`country` AS `country`,
`caches`.`difficulty` AS `difficulty`,
`caches`.`terrain` AS `terrain`,
`caches`.`founds` AS `founds`,
`caches`.`status` AS `status`,
`caches`.`user_id` AS `user_id`
FROM `caches`
WHERE `caches`.`cache_id` NOT IN (SELECT `cache_ignore`.`cache_id` FROM `cache_ignore` WHERE `cache_ignore`.`user_id`=\'' . $user_id . '\')
AND caches.status<>4 AND caches.status<>5
AND caches.status <>6
AND `longitude` > ' . ($lon - $max_lon_diff) . '
AND `longitude` < ' . ($lon + $max_lon_diff) . '
AND `latitude` > ' . ($lat - $max_lat_diff) . '
AND `latitude` < ' . ($lat + $max_lat_diff) . '
HAVING `distance` < ' . $distance;
$dbcLocCache->simpleQuery($sqlstr);
$sqlstr = 'ALTER TABLE local_caches
ADD PRIMARY KEY ( `cache_id` ),
ADD INDEX(`cache_id`),
ADD INDEX (`wp_oc`),
ADD INDEX(`type`),
ADD INDEX(`name`),
ADD INDEX(`user_id`),
ADD INDEX(`date_hidden`),
ADD INDEX(`date_created`)';
$dbcLocCache->simpleQuery($sqlstr);
}
$sql .= getSqlDistanceFormula($lon_rad * 180 / 3.14159, $lat_rad * 180 / 3.14159, 0, $multiplier[$distance_unit]) . ' `distance`, ';
} else {
if ($usr === false) {
$sql .= '0 distance, ';
} else {
//get the users home coords
$rs_coords = sql_slave("SELECT `latitude`, `longitude` FROM `user` WHERE `user_id`='&1'", $usr['userid']);
$record_coords = sql_fetch_array($rs_coords);
if ($record_coords['latitude'] == NULL || $record_coords['longitude'] == NULL || ($record_coords['latitude'] == 0 || $record_coords['longitude'] == 0)) {
$sql .= '0 distance, ';
} else {
//TODO: load from the users-profile
$distance_unit = 'km';
$lon_rad = $record_coords['longitude'] * 3.14159 / 180;
$lat_rad = $record_coords['latitude'] * 3.14159 / 180;
$sql .= getSqlDistanceFormula($record_coords['longitude'], $record_coords['latitude'], 0, $multiplier[$distance_unit]) . ' `distance`, ';
}
mysql_free_result($rs_coords);
}
}
$sAddJoin = '';
$sAddGroupBy = '';
$sAddField = '';
if ($options['sort'] == 'bylastlog') {
$sAddField = ', MAX(`cache_logs`.`date`) AS `lastLog`';
$sAddJoin = ' LEFT JOIN `cache_logs` ON `caches`.`cache_id`=`cache_logs`.`cache_id`';
$sGroupBy = ' GROUP BY `cache_logs`.`cache_id`';
}
$sql .= '`caches`.`cache_id` `cache_id`, `caches`.`status` `status`, `caches`.`type` `type`, `caches`.`size` `size`, `caches`.`longitude` `longitude`, `caches`.`latitude` `latitude`, `caches`.`user_id` `user_id`,
IF(IFNULL(`stat_caches`.`toprating`,0)>3, 4, IFNULL(`stat_caches`.`toprating`, 0)) `ratingvalue`' . $sAddField . ' FROM `caches`
LEFT JOIN `stat_caches` ON `caches`.`cache_id`=`stat_caches`.`cache_id`' . $sAddJoin . ' WHERE `caches`.`cache_id` IN (' . $sqlFilter . ')' . $sGroupBy;
$distance_unit = 'km';
$radius = $distance;
//get the users home coords
$lat = $latitude;
$lon = $longitude;
$lon_rad = $lon * 3.14159 / 180;
$lat_rad = $lat * 3.14159 / 180;
//all target caches are between lat - max_lat_diff and lat + max_lat_diff
$max_lat_diff = $distance / 111.12;
//all target caches are between lon - max_lon_diff and lon + max_lon_diff
//TODO: check!!!
$max_lon_diff = $distance * 180 / (abs(sin((90 - $lat) * 3.14159 / 180)) * 6378 * 3.14159);
sql('DROP TEMPORARY TABLE IF EXISTS local_caches' . $user_id . '');
sql('CREATE TEMPORARY TABLE local_caches' . $user_id . ' ENGINE=MEMORY
SELECT
(' . getSqlDistanceFormula($lon, $lat, $distance, 1) . ') AS `distance`,
`caches`.`cache_id` AS `cache_id`,
`caches`.`wp_oc` AS `wp_oc`,
`caches`.`type` AS `type`,
`caches`.`name` AS `name`,
`caches`.`longitude` AS `longitude`,
`caches`.`latitude` AS `latitude`,
`caches`.`date_hidden` AS `date_hidden`,
`caches`.`date_created` AS `date_created`,
`caches`.`country` AS `country`,
`caches`.`difficulty` AS `difficulty`,
`caches`.`terrain` AS `terrain`,
`caches`.`status` AS `status`,
`caches`.`user_id` AS `user_id`
FROM `caches`
WHERE `caches`.`cache_id` NOT IN (SELECT `cache_ignore`.`cache_id` FROM `cache_ignore` WHERE `cache_ignore`.`user_id`=\'' . $user_id . '\')
}
$distance_unit = 'km';
$radius = $distance;
$lat = $latitude;
$lon = $longitude;
$lon_rad = $lon * 3.14159 / 180;
$lat_rad = $lat * 3.14159 / 180;
//all target caches are between lat - max_lat_diff and lat + max_lat_diff
$max_lat_diff = $distance / 111.12;
//all target caches are between lon - max_lon_diff and lon + max_lon_diff
//TODO: check!!!
$max_lon_diff = $distance * 180 / (abs(sin((90 - $lat) * 3.14159 / 180)) * 6378 * 3.14159);
sql('DROP TEMPORARY TABLE IF EXISTS local_caches' . $user_id . '');
sql('CREATE TEMPORARY TABLE local_caches' . $user_id . ' ENGINE=MEMORY
SELECT
(' . getSqlDistanceFormula($lon, $lat, $distance, $multiplier[$distance_unit]) . ') AS `distance`,
`caches`.`cache_id` AS `cache_id`,
`caches`.`wp_oc` AS `wp_oc`,
`caches`.`type` AS `type`,
`caches`.`name` AS `name`,
`caches`.`longitude` `longitude`,
`caches`.`latitude` `latitude`,
`caches`.`date_hidden` `date_hidden`,
`caches`.`date_created` `date_created`,
`caches`.`country` `country`,
`caches`.`difficulty` `difficulty`,
`caches`.`terrain` `terrain`,
`caches`.`founds` `founds`,
`caches`.`status` `status`,
`caches`.`user_id` `user_id`
FROM `caches`
function getCalcDistanceSqlFormula($modEnabled, $lonFrom, $latFrom, $maxDistance, $distanceMultiplier = 1, $lonField = 'longitude', $latField = 'latitude', $tableName = 'caches', $modLonField = 'longitude', $modLatField = 'latitude', $modTableName = 'cache_mod_cords')
{
if ($modEnabled) {
return getSqlDistanceFormulaForModCoords($lonFrom, $latFrom, $maxDistance, $distanceMultiplier, $lonField, $latField, $tableName, $modLonField, $modLatField, $modTableName);
} else {
return getSqlDistanceFormula($lonFrom, $latFrom, $maxDistance, $distanceMultiplier, $lonField, $latField, $tableName);
}
}
function startXmlSession($sModifiedSince, $bCache, $bCachedesc, $bCachelog, $bUser, $bPicture, $bRemovedObject, $bPictureFromCachelog, $selection)
{
global $rootpath;
// session anlegen
sql('INSERT INTO `xmlsession` (`last_use`, `modified_since`, `date_created`) VALUES (NOW(), \'&1\', NOW())', date('Y-m-d H:i:s', strtotime($sModifiedSince)));
$sessionid = mysql_insert_id();
$recordcount['caches'] = 0;
$recordcount['cachedescs'] = 0;
$recordcount['cachelogs'] = 0;
$recordcount['users'] = 0;
$recordcount['pictures'] = 0;
$recordcount['removedobjects'] = 0;
if ($selection['type'] == 0) {
// ohne selection
if ($bCache == 1) {
sql("INSERT INTO xmlsession_data (`session_id`, `object_type`, `object_id`)\n SELECT &1, 2, `cache_id` FROM `caches` WHERE `last_modified` >= '&2' AND `status`!=5 AND `status`!=6 AND `status`!=4", $sessionid, $sModifiedSince);
$recordcount['caches'] = mysql_affected_rows();
}
if ($bCachedesc == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT &1, 3, `cache_desc`.`id` FROM `cache_desc` INNER JOIN `caches` ON `cache_desc`.`cache_id`=`caches`.`cache_id` WHERE `cache_desc`.`last_modified` >= '&2' AND `caches`.`status`!=5 AND `status`!=6 AND `status`!=4", $sessionid, $sModifiedSince);
$recordcount['cachedescs'] = mysql_affected_rows();
}
if ($bCachelog == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT &1, 1, `cache_logs`.`id` FROM `cache_logs` INNER JOIN `caches` ON `cache_logs`.`cache_id`=`caches`.`cache_id` WHERE `cache_logs`.`last_modified` >= '&2' AND `caches`.`status`!=5 AND `status`!=6 AND `status`!=4 AND `cache_logs`.`deleted`=0", $sessionid, $sModifiedSince);
$recordcount['cachelogs'] = mysql_affected_rows();
}
if ($bUser == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT &1, 4, `user_id` FROM `user` WHERE `last_modified` >= '&2'", $sessionid, $sModifiedSince);
$recordcount['users'] = mysql_affected_rows();
}
if ($bPicture == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT &1, 6, `pictures`.`id` FROM `pictures` INNER JOIN\n `caches` ON `pictures`.`object_type`=2 AND\n `pictures`.`object_id`=`caches`.`cache_id`\n WHERE `pictures`.`last_modified` >= '&2' AND\n `caches`.`status`!=5 AND `status`!=6 AND `status`!=4\n UNION DISTINCT\n SELECT &1, 6, `pictures`.`id` FROM `pictures` INNER JOIN\n `cache_logs` ON `pictures`.`object_type`=1 AND\n `pictures`.`object_id`=`cache_logs`.`id` INNER JOIN\n `caches` ON `cache_logs`.`cache_id`=`caches`.`cache_id`\n WHERE `pictures`.`last_modified` >= '&2' AND\n `caches`.`status`!=5 AND `status`!=6 AND `status`!=4 AND `cache_logs`.`deleted`=0", $sessionid, $sModifiedSince);
$recordcount['pictures'] = mysql_affected_rows();
}
if ($bRemovedObject == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT &1, 7, `id` FROM `removed_objects` WHERE `removed_date` >= '&2'", $sessionid, $sModifiedSince);
$recordcount['removedobjects'] = mysql_affected_rows();
}
} else {
$sqlWhere = '';
$sqlHaving = '';
if ($selection['type'] == 1) {
sql("CREATE TEMPORARY TABLE `tmpxmlSesssionCaches` (`cache_id` int(11), PRIMARY KEY (`cache_id`)) ENGINE=MEMORY\n SELECT DISTINCT `cache_countries`.`cache_id` FROM `caches`, `cache_countries` WHERE `caches`.`cache_id`=`cache_countries`.`cache_id` AND `cache_countries`.`country`='&1' AND `caches`.`status`!=5 AND `status`!=6 AND `status`!=4", $selection['country']);
} else {
if ($selection['type'] == 2) {
require_once $rootpath . 'lib/search.inc.php';
$sql = 'CREATE TEMPORARY TABLE `tmpxmlSesssionCaches` (`cache_id` int(11), `distance` double, KEY (`cache_id`)) ENGINE=MEMORY ';
$sql .= 'SELECT `cache_coordinates`.`cache_id`, ';
$sql .= getSqlDistanceFormula($selection['lon'], $selection['lat'], $selection['distance'], 'cache_coordinates') . ' `distance` ';
$sql .= 'FROM `caches`, `cache_coordinates` WHERE ';
$sql .= '`cache_coordinates`.`cache_id`=`caches`.`cache_id`';
$sql .= ' AND `caches`.`status`!=5 AND `status`!=6 AND `status`!=4';
$sql .= ' AND `cache_coordinates`.`latitude` > ' . getMinLat($selection['lon'], $selection['lat'], $selection['distance']);
$sql .= ' AND `cache_coordinates`.`latitude` < ' . getMaxLat($selection['lon'], $selection['lat'], $selection['distance']);
$sql .= ' AND `cache_coordinates`.`longitude` >' . getMinLon($selection['lon'], $selection['lat'], $selection['distance']);
$sql .= ' AND `cache_coordinates`.`longitude` < ' . getMaxLon($selection['lon'], $selection['lat'], $selection['distance']);
$sql .= ' HAVING `distance` < ' . ($selection['distance'] + 0);
sql($sql);
} else {
if ($selection['type'] == 3) {
sql("CREATE TEMPORARY TABLE `tmpxmlSesssionCaches` (`cache_id` int(11), PRIMARY KEY (`cache_id`)) ENGINE=MEMORY\n SELECT '&1' AS cache_id", $selection['cacheid'] + 0);
}
}
}
if ($bCache == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT DISTINCT &1, 2, `tmpxmlSesssionCaches`.`cache_id` FROM `tmpxmlSesssionCaches`, `caches`\n WHERE `tmpxmlSesssionCaches`.`cache_id`=`caches`.`cache_id` AND `caches`.`last_modified` >= '&2'", $sessionid, $sModifiedSince);
$recordcount['caches'] = mysql_affected_rows();
}
if ($bCachedesc == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT DISTINCT &1, 3, `cache_desc`.`id` FROM `cache_desc`, `tmpxmlSesssionCaches`\n WHERE `cache_desc`.`cache_id`=`tmpxmlSesssionCaches`.`cache_id` AND `cache_desc`.`last_modified` >= '&2'", $sessionid, $sModifiedSince);
$recordcount['cachedescs'] = mysql_affected_rows();
}
if ($bCachelog == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT DISTINCT &1, 1, `cache_logs`.`id` FROM `cache_logs`, `tmpxmlSesssionCaches`\n WHERE `cache_logs`.`deleted`=0 AND `cache_logs`.`cache_id`=`tmpxmlSesssionCaches`.`cache_id` AND `cache_logs`.`last_modified` >= '&2'", $sessionid, $sModifiedSince);
$recordcount['cachelogs'] = mysql_affected_rows();
}
if ($bPicture == 1) {
// cachebilder
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT DISTINCT &1, 6, `pictures`.`id` FROM `pictures`, `tmpxmlSesssionCaches`\n WHERE `pictures`.`object_id`=`tmpxmlSesssionCaches`.`cache_id` AND `pictures`.`object_type`=2 AND\n `pictures`.`last_modified` >= '&2'", $sessionid, $sModifiedSince);
$recordcount['pictures'] = mysql_affected_rows();
// bilder von logs
if ($bPictureFromCachelog == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT DISTINCT &1, 6, `pictures`.id FROM `pictures` , `cache_logs`, `tmpxmlSesssionCaches`\n WHERE `tmpxmlSesssionCaches`.`cache_id`=`cache_logs`.`cache_id` AND `cache_logs`.`deleted`=0 AND\n `pictures`.`object_type`=1 AND `pictures`.`object_id`=`cache_logs`.`id` AND\n `pictures`.`last_modified` >= '&2'", $sessionid, $sModifiedSince);
$recordcount['pictures'] += mysql_affected_rows();
}
}
if ($bRemovedObject == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT DISTINCT &1, 7, `id` FROM `removed_objects` WHERE `removed_date` >= '&2'", $sessionid, $sModifiedSince);
$recordcount['removedobjects'] = mysql_affected_rows();
}
}
sql('UPDATE `xmlsession` SET `caches`=&1, `cachedescs`=&2, `cachelogs`=&3, `users`=&4, `pictures`=&5, `removedobjects`=&6 WHERE `id`=&7 LIMIT 1', $recordcount['caches'], $recordcount['cachedescs'], $recordcount['cachelogs'], $recordcount['users'], $recordcount['pictures'], $recordcount['removedobjects'], $sessionid);
return $sessionid;
}
function startXmlSession($sModifiedSince, $bCache, $bCachedesc, $bCachelog, $bUser, $bPicture, $bRemovedObject, $bPictureFromCachelog, $selection)
{
global $rootpath;
// session anlegen
XDb::xSql('INSERT INTO `xmlsession` (`last_use`, `modified_since`, `date_created`)
VALUES (NOW(), ?, NOW())', date('Y-m-d H:i:s', strtotime($sModifiedSince)));
$sessionid = XDb::xLastInsertId();
$recordcount['caches'] = 0;
$recordcount['cachedescs'] = 0;
$recordcount['cachelogs'] = 0;
$recordcount['users'] = 0;
$recordcount['pictures'] = 0;
$recordcount['removedobjects'] = 0;
if ($selection['type'] == 0) {
// ohne selection
if ($bCache == 1) {
$stmt = XDb::xSql("INSERT INTO xmlsession_data (`session_id`, `object_type`, `object_id`)\n SELECT {$sessionid}, 2, `cache_id` FROM `caches`\n WHERE `last_modified` >= ? AND `status`!=5 AND `status`!=6 AND `status`!=4", $sModifiedSince);
$recordcount['caches'] = XDb::xNumRows($stmt);
}
if ($bCachedesc == 1) {
$stmt = XDb::xSql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT {$sessionid}, 3, `cache_desc`.`id`\n FROM `cache_desc` INNER JOIN `caches` ON `cache_desc`.`cache_id`=`caches`.`cache_id`\n WHERE `cache_desc`.`last_modified` >= ? AND `caches`.`status`!=5\n AND `status`!=6 AND `status`!=4", $sModifiedSince);
$recordcount['cachedescs'] = XDb::xNumRows($stmt);
}
if ($bCachelog == 1) {
$stmt = XDb::xSql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT {$sessionid}, 1, `cache_logs`.`id`\n FROM `cache_logs` INNER JOIN `caches` ON `cache_logs`.`cache_id`=`caches`.`cache_id`\n WHERE `cache_logs`.`last_modified` >= ? AND `caches`.`status`!=5\n AND `status`!=6 AND `status`!=4 AND `cache_logs`.`deleted`=0", $sModifiedSince);
$recordcount['cachelogs'] = XDb::xNumRows($stmt);
}
if ($bUser == 1) {
$stmt = XDb::xSql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT {$sessionid}, 4, `user_id` FROM `user` WHERE `last_modified` >= ? ", $sModifiedSince);
$recordcount['users'] = XDb::xNumRows($stmt);
}
if ($bPicture == 1) {
$stmt = XDb::xSql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT {$sessionid}, 6, `pictures`.`id`\n FROM `pictures`\n INNER JOIN `caches` ON `pictures`.`object_type`=2\n AND `pictures`.`object_id`=`caches`.`cache_id`\n WHERE `pictures`.`last_modified` >= ?\n AND `caches`.`status`!=5 AND `status`!=6 AND `status`!=4\n UNION DISTINCT\n SELECT {$sessionid}, 6, `pictures`.`id`\n FROM `pictures`\n INNER JOIN `cache_logs` ON `pictures`.`object_type`=1\n AND `pictures`.`object_id`=`cache_logs`.`id`\n INNER JOIN `caches` ON `cache_logs`.`cache_id`=`caches`.`cache_id`\n WHERE `pictures`.`last_modified` >= ?\n AND `caches`.`status`!=5 AND `status`!=6 AND `caches`.`status`!=4\n AND `cache_logs`.`deleted`=0", $sModifiedSince, $sModifiedSince);
$recordcount['pictures'] = XDb::xNumRows($stmt);
}
if ($bRemovedObject == 1) {
$stmt = XDb::xSql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT {$sessionid}, 7, `id` FROM `removed_objects`\n WHERE `removed_date` >= ? ", $sModifiedSince);
$recordcount['removedobjects'] = XDb::xNumRows($stmt);
}
} else {
$qWhere = '';
$qHaving = '';
if ($selection['type'] == 1) {
XDb::xSql("CREATE TEMPORARY TABLE `tmpxmlSesssionCaches` (`cache_id` int(11), PRIMARY KEY (`cache_id`)) ENGINE=MEMORY\n SELECT DISTINCT `cache_countries`.`cache_id`\n FROM `caches`, `cache_countries`\n WHERE `caches`.`cache_id`=`cache_countries`.`cache_id` AND `cache_countries`.`country`= ?\n AND `caches`.`status`!=5 AND `status`!=6 AND `status`!=4", $selection['country']);
} else {
if ($selection['type'] == 2) {
require_once $rootpath . 'lib/search.inc.php';
XDb::xSql('CREATE TEMPORARY TABLE `tmpxmlSesssionCaches` (`cache_id` int(11), `distance` double, KEY (`cache_id`)) ENGINE=MEMORY
SELECT `cache_coordinates`.`cache_id`,' . getSqlDistanceFormula($selection['lon'], $selection['lat'], $selection['distance'], 'cache_coordinates') . ' `distance`
FROM `caches`, `cache_coordinates`
WHERE `cache_coordinates`.`cache_id`=`caches`.`cache_id` AND `caches`.`status`!=5
AND `status`!=6 AND `status`!=4
AND `cache_coordinates`.`latitude` > ' . getMinLat($selection['lon'], $selection['lat'], $selection['distance']) . '
AND `cache_coordinates`.`latitude` < ' . getMaxLat($selection['lon'], $selection['lat'], $selection['distance']) . '
AND `cache_coordinates`.`longitude` >' . getMinLon($selection['lon'], $selection['lat'], $selection['distance']) . '
AND `cache_coordinates`.`longitude` < ' . getMaxLon($selection['lon'], $selection['lat'], $selection['distance']) . '
HAVING `distance` < ' . ($selection['distance'] + 0));
} else {
if ($selection['type'] == 3) {
XDb::xSql("CREATE TEMPORARY TABLE `tmpxmlSesssionCaches` (`cache_id` int(11), PRIMARY KEY (`cache_id`)) ENGINE=MEMORY\n SELECT " . $selection['cacheid'] . " AS cache_id");
}
}
}
if ($bCache == 1) {
$stmt = XDb::xSql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT DISTINCT {$sessionid}, 2, `tmpxmlSesssionCaches`.`cache_id`\n FROM `tmpxmlSesssionCaches`, `caches`\n WHERE `tmpxmlSesssionCaches`.`cache_id`=`caches`.`cache_id`\n AND `caches`.`last_modified` >= ? ", $sModifiedSince);
$recordcount['caches'] = XDb::xNumRows($stmt);
}
if ($bCachedesc == 1) {
$stmt = XDb::xSql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT DISTINCT {$sessionid}, 3, `cache_desc`.`id`\n FROM `cache_desc`, `tmpxmlSesssionCaches`\n WHERE `cache_desc`.`cache_id`=`tmpxmlSesssionCaches`.`cache_id`\n AND `cache_desc`.`last_modified` >= ? ", $sModifiedSince);
$recordcount['cachedescs'] = XDb::xNumRows($stmt);
}
if ($bCachelog == 1) {
$stmt = XDb::xSql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT DISTINCT {$sessionid}, 1, `cache_logs`.`id`\n FROM `cache_logs`, `tmpxmlSesssionCaches`\n WHERE `cache_logs`.`deleted`=0\n AND `cache_logs`.`cache_id`=`tmpxmlSesssionCaches`.`cache_id`\n AND `cache_logs`.`last_modified` >= ? ", $sModifiedSince);
$recordcount['cachelogs'] = XDb::xNumRows($stmt);
}
if ($bPicture == 1) {
// cachebilder
$stmt = XDb::xSql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT DISTINCT {$sessionid}, 6, `pictures`.`id`\n FROM `pictures`, `tmpxmlSesssionCaches`\n WHERE `pictures`.`object_id`=`tmpxmlSesssionCaches`.`cache_id`\n AND `pictures`.`object_type`=2\n AND `pictures`.`last_modified` >= ? ", $sModifiedSince);
$recordcount['pictures'] = XDb::xNumRows($stmt);
// bilder von logs
if ($bPictureFromCachelog == 1) {
$stmt = XDb::xSql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT DISTINCT {$sessionid}, 6, `pictures`.id\n FROM `pictures` , `cache_logs`, `tmpxmlSesssionCaches`\n WHERE `tmpxmlSesssionCaches`.`cache_id`=`cache_logs`.`cache_id`\n AND `cache_logs`.`deleted`=0\n AND `pictures`.`object_type`=1\n AND `pictures`.`object_id`=`cache_logs`.`id`\n AND `pictures`.`last_modified` >= ? ", $sModifiedSince);
$recordcount['pictures'] += XDb::xNumRows($stmt);
}
}
if ($bRemovedObject == 1) {
$stmt = XDb::xSql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n SELECT DISTINCT {$sessionid}, 7, `id`\n FROM `removed_objects`\n WHERE `removed_date` >= ? ", $sModifiedSince);
$recordcount['removedobjects'] = XDb::xNumRows($stmt);
}
}
XDb::xSql('UPDATE `xmlsession` SET `caches`= ?, `cachedescs`= ?, `cachelogs`= ?, `users`= ?, `pictures`= ?, `removedobjects`= ?
WHERE `id`= ? LIMIT 1', $recordcount['caches'], $recordcount['cachedescs'], $recordcount['cachelogs'], $recordcount['users'], $recordcount['pictures'], $recordcount['removedobjects'], $sessionid);
return $sessionid;
}
/* begin db connect */
db_connect();
if ($dblink === false) {
echo 'Unable to connect to database';
exit;
}
/* end db connect */
/* begin search index rebuild */
$rsLocations = sql("SELECT `uni`, `lat`, `lon`, `rc`, `cc1`, `adm1` FROM `gns_locations` WHERE `dsg` LIKE 'PPL%'");
while ($rLocations = sql_fetch_array($rsLocations)) {
$minlat = getMinLat($rLocations['lon'], $rLocations['lat'], 10, 1);
$maxlat = getMaxLat($rLocations['lon'], $rLocations['lat'], 10, 1);
$minlon = getMinLon($rLocations['lon'], $rLocations['lat'], 10, 1);
$maxlon = getMaxLon($rLocations['lon'], $rLocations['lat'], 10, 1);
// den nächsgelegenen Ort in den geodb ermitteln
$sql = 'SELECT ' . getSqlDistanceFormula($rLocations['lon'], $rLocations['lat'], 10, 1, 'lon', 'lat', 'geodb_coordinates') . ' `distance`,
`geodb_coordinates`.`loc_id` `loc_id`
FROM `geodb_coordinates`
WHERE `lon` > ' . $minlon . ' AND
`lon` < ' . $maxlon . ' AND
`lat` > ' . $minlat . ' AND
`lat` < ' . $maxlat . '
HAVING `distance` < 10
ORDER BY `distance` ASC
LIMIT 1';
$rs = sql($sql);
if (mysql_num_rows($rs) == 1) {
$r = sql_fetch_array($rs);
mysql_free_result($rs);
$locid = $r['loc_id'];
$admtxt1 = landFromLocid($locid);
set_route_options($route_id, $options);
// get first point of route to calculate distance to cache and sort list by distance
$lon = 0;
$lat = 0;
$s = $database->paramQuery('SELECT `route_points`.`lat`, `route_points`.`lon` FROM `route_points`
WHERE `route_id`=:route_id ORDER BY `route_points`.`point_nr` LIMIT 1', array('route_id' => array('value' => $route_id, 'data_type' => 'integer')));
$record = $database->dbResultFetchOneRowOnly($s);
if ($record) {
$lon = $record['lon'];
$lat = $record['lat'];
}
// yes, I know that this SQL is a little violation of bound parameters concept, but
// first of all, rewrite $qFilter to use PDO is terrible job,
// second, using IN operator with dynamic list is pain in the ass :( - unless we have better
// database wrapper to handle that automatically
$s = $database->simpleQuery("SELECT (" . getSqlDistanceFormula($lon, $lat, 0, 1) . ") `distance`,\n `caches`.`cache_id` `cacheid`,\n `user`.`user_id` `userid`,\n `caches`.`type` `type`,\n `caches`.`name` `cachename`,\n `caches`.`latitude` `latitude`,\n `caches`.`longitude` `longitude`,\n `caches`.`wp_oc` `wp_oc`,\n `user`.`username` `username`,\n `caches`.`date_created` `date_created`,\n `caches`.`date_hidden` `date`,\n `cache_type`.`icon_large` `icon_large`,\n `caches`.`topratings` `topratings`\n FROM `caches`,`user`, `cache_type`\n WHERE `caches`.`wp_oc` IN('" . implode("', '", $caches_list) . "')\n AND `caches`.`user_id`=`user`.`user_id`\n AND `cache_type`.`id`=`caches`.`type`\n AND `caches`.`cache_id` IN (" . $qFilter . ")\n ORDER BY distance");
$ncaches = $database->rowCount($s);
tpl_set_var('number_caches', $ncaches);
if ($ncaches == 0) {
tpl_set_var('list_empty_start', '<!--');
tpl_set_var('list_empty_end', '-->');
tpl_set_var('file_content', '');
} else {
tpl_set_var('list_empty_start', '');
tpl_set_var('list_empty_end', '');
}
$point = "";
$database_inner = OcDb::instance();
$file_content = '';
while ($r = $database->dbResultFetch($s)) {
if (isset($_POST['submit_map'])) {