function cache_locate($lat, $lon)
{
$max_distance = 5;
$rsCache = sql_slave("SELECT " . geomath::getSqlDistanceFormula($lon, $lat, $max_distance) . " AS `distance`, \r\n\t `caches`.`wp_oc`\r\n\t FROM `caches` \r\n\t WHERE `caches`.`latitude`>'&1' AND \r\n \t\t\t\t\t\t\t\t\t\t `caches`.`latitude`<'&2' AND \r\n\t\t\t\t\t\t\t\t\t\t `caches`.`longitude`>'&3' AND \r\n\t\t\t\t\t\t\t\t\t\t `caches`.`longitude`<'&4' AND\r\n\t\t\t\t\t\t\t\t\t\t `caches`.`status` IN (1, 2)\r\n\t\t\t\t\t ORDER BY `distance` ASC LIMIT 1", geomath::getMinLat($lon, $lat, $max_distance), geomath::getMaxLat($lon, $lat, $max_distance), geomath::getMinLon($lon, $lat, $max_distance), geomath::getMaxLon($lon, $lat, $max_distance));
$rCache = sql_fetch_assoc($rsCache);
sql_free_result($rsCache);
if ($rCache === false) {
echo '<caches></caches>';
exit;
}
output_cachexml($rCache['wp_oc']);
}
/***************************************************************************
* For license information see doc/license.txt
*
* Unicode Reminder メモ
*
* Dieses Script berechnet andhand von Geodb-Daten die adm1..adm4-Daten
* für die GNS-DB.
***************************************************************************/
$opt['rootpath'] = '../../';
require_once $opt['rootpath'] . 'lib2/cli.inc.php';
require_once $opt['rootpath'] . 'lib2/search/search.inc.php';
$rsLocations = sql("SELECT `uni`, `lat`, `lon`, `rc`, `cc1`, `adm1` FROM `gns_locations` WHERE `dsg` LIKE 'PPL%'");
while ($rLocations = sql_fetch_array($rsLocations)) {
$minlat = geomath::getMinLat($rLocations['lon'], $rLocations['lat'], 10, 1);
$maxlat = geomath::getMaxLat($rLocations['lon'], $rLocations['lat'], 10, 1);
$minlon = geomath::getMinLon($rLocations['lon'], $rLocations['lat'], 10, 1);
$maxlon = geomath::getMaxLon($rLocations['lon'], $rLocations['lat'], 10, 1);
// den nächsgelegenen Ort in den geodb ermitteln
$sql = "SELECT " . geomath::getSqlDistanceFormula($rLocations['lon'], $rLocations['lat'], 10, 1, 'lon', 'lat', 'geodb_coordinates') . " `distance`,\n `geodb_coordinates`.`loc_id` `loc_id`\n FROM `geodb_coordinates`\n WHERE\n `lon` > '" . sql_escape($minlon) . "' AND `lon` < '" . sql_escape($maxlon) . "' AND\n `lat` > '" . sql_escape($minlat) . "' AND `lat` < '" . sql_escape($maxlat) . "'\n HAVING `distance` < 10\n ORDER BY `distance` ASC\n 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 = GeoDb::landFromLocid($locid);
if ($admtxt1 == '0') {
$admtxt1 = '';
}
// bundesland ermitteln
$rsAdm2 = sql("SELECT `full_name`, `short_form`\n FROM `gns_locations`\n WHERE `rc`='&1'\n AND `fc`='A'\n AND `dsg`='ADM1'\n AND `cc1`='&2'\n AND `adm1`='&3'\n AND `nt`='N'\n LIMIT 1", $rLocations['rc'], $rLocations['cc1'], $rLocations['adm1']);
if (mysql_num_rows($rsAdm2) == 1) {
function cache_locate($nLat, $nLon, $nDistance)
{
global $login;
$rsCache = sql_slave("SELECT " . geomath::getSqlDistanceFormula($nLon, $nLat, $nDistance) . " AS `distance`, \n\t `caches`.`wp_oc`\n\t FROM `caches` \n\t INNER JOIN `cache_status` ON `caches`.`status`=`cache_status`.`id`\n\t WHERE `caches`.`latitude`>'&1' AND \n \t\t\t\t\t\t\t\t\t\t `caches`.`latitude`<'&2' AND \n\t\t\t\t\t\t\t\t\t\t `caches`.`longitude`>'&3' AND \n\t\t\t\t\t\t\t\t\t\t `caches`.`longitude`<'&4' AND\n\t\t\t\t\t\t\t\t\t\t (`cache_status`.`allow_user_view`=1 OR `caches`.`user_id`='&1')\n\t\t\t\t\t ORDER BY `distance` ASC LIMIT 1", geomath::getMinLat($nLon, $nLat, $nDistance), geomath::getMaxLat($nLon, $nLat, $nDistance), geomath::getMinLon($nLon, $nLat, $nDistance), geomath::getMaxLon($nLon, $nLat, $nDistance), $login->userid);
$rCache = sql_fetch_assoc($rsCache);
sql_free_result($rsCache);
if ($rCache === false) {
echo '<caches></caches>';
exit;
}
output_cachexml($rCache['wp_oc']);
}
function startXmlSession($sModifiedSince, $bCache, $bCachedesc, $bCachelog, $bUser, $bPicture, $bRemovedObject, $bPictureFromCachelog, $selection, $sAgent)
{
global $opt, $ocxmlversion;
// session anlegen
sql("INSERT INTO `xmlsession` (`last_use`, `modified_since`, `date_created`, `agent`) VALUES (NOW(), '&1', NOW(), '&2')", date('Y-m-d H:i:s', strtotime($sModifiedSince)), $sAgent);
$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) {
if ($ocxmlversion >= 15) {
// Starting with version 15, we include the 'needs maintenance' and
// 'listing is outdated' flags in the <cache> records.
$wherefield = 'GREATEST(`listing_last_modified`,`flags_last_modified`)';
} else {
if ($ocxmlversion == 14) {
// Starting with version 14, we include listing_last_modified in the
// <caches> records, so this date is relevant for updates
$wherefield = '`listing_last_modified`';
} else {
$wherefield = '`last_modified`';
}
}
sql("INSERT INTO xmlsession_data (`session_id`, `object_type`, `object_id`)\n\t\t\t SELECT &1, 2, `cache_id` FROM `caches` WHERE " . $wherefield . " >= '&2' AND `status`!=5", $sessionid, $sModifiedSince);
$recordcount['caches'] = mysql_affected_rows();
}
if ($bCachedesc == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n\t\t\t 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", $sessionid, $sModifiedSince);
$recordcount['cachedescs'] = mysql_affected_rows();
}
if ($bCachelog == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n\t\t\t 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", $sessionid, $sModifiedSince);
$recordcount['cachelogs'] = mysql_affected_rows();
}
if ($bUser == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n\t\t\t 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\t\t\t SELECT &1, 6, `pictures`.`id` FROM `pictures` INNER JOIN\n\t\t\t `caches` ON `pictures`.`object_type`=2 AND\n\t\t\t `pictures`.`object_id`=`caches`.`cache_id`\n\t\t\t WHERE `pictures`.`last_modified` >= '&2' AND\n\t\t\t `caches`.`status`!=5\n\t\t\t UNION DISTINCT\n\t\t\t SELECT &1, 6, `pictures`.`id` FROM `pictures` INNER JOIN\n\t\t\t `cache_logs` ON `pictures`.`object_type`=1 AND\n\t\t\t `pictures`.`object_id`=`cache_logs`.`id` INNER JOIN\n\t\t\t `caches` ON `cache_logs`.`cache_id`=`caches`.`cache_id`\n\t\t\t WHERE `pictures`.`last_modified` >= '&2' AND\n\t\t\t `caches`.`status`!=5", $sessionid, $sModifiedSince);
$recordcount['pictures'] = mysql_affected_rows();
}
if ($bRemovedObject == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n\t\t\t SELECT &1, 7, `id` FROM `removed_objects` WHERE `removed_date` >= '&2' AND `type`<>8", $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\t\t\t 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", $selection['country']);
} else {
if ($selection['type'] == 2) {
$sql = 'CREATE TEMPORARY TABLE `tmpxmlSesssionCaches` (`cache_id` int(11), `distance` double, KEY (`cache_id`)) ENGINE=MEMORY ';
$sql .= 'SELECT `cache_coordinates`.`cache_id`, ';
$sql .= geomath::getSqlDistanceFormula($selection['lon'], $selection['lat'], $selection['distance'], 1, 'longitude', 'latitude', 'cache_coordinates') . ' `distance` ';
$sql .= 'FROM `caches`, `cache_coordinates` WHERE ';
$sql .= '`cache_coordinates`.`cache_id`=`caches`.`cache_id`';
$sql .= ' AND `caches`.`status`!=5';
$sql .= ' AND `cache_coordinates`.`latitude` > ' . geomath::getMinLat($selection['lon'], $selection['lat'], $selection['distance']);
$sql .= ' AND `cache_coordinates`.`latitude` < ' . geomath::getMaxLat($selection['lon'], $selection['lat'], $selection['distance']);
$sql .= ' AND `cache_coordinates`.`longitude` >' . geomath::getMinLon($selection['lon'], $selection['lat'], $selection['distance']);
$sql .= ' AND `cache_coordinates`.`longitude` < ' . geomath::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\t\t\t SELECT `cache_id` FROM `caches` WHERE `cache_id`='&1' AND `status`!=5", $selection['cacheid'] + 0);
}
}
}
if ($bCache == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n\t\t\t SELECT DISTINCT &1, 2, `tmpxmlSesssionCaches`.`cache_id` FROM `tmpxmlSesssionCaches`, `caches`\n\t\t\t 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\t\t\t SELECT DISTINCT &1, 3, `cache_desc`.`id` FROM `cache_desc`, `tmpxmlSesssionCaches`\n\t\t\t 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\t\t\t SELECT DISTINCT &1, 1, `cache_logs`.`id` FROM `cache_logs`, `tmpxmlSesssionCaches`\n\t\t\t WHERE `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\t\t\t SELECT DISTINCT &1, 6, `pictures`.`id` FROM `pictures`, `tmpxmlSesssionCaches`\n\t\t\t WHERE `pictures`.`object_id`=`tmpxmlSesssionCaches`.`cache_id` AND `pictures`.`object_type`=2 AND\n\t\t\t `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\t\t\t\t SELECT DISTINCT &1, 6, `pictures`.id FROM `pictures` , `cache_logs`, `tmpxmlSesssionCaches`\n\t\t\t\t WHERE `tmpxmlSesssionCaches`.`cache_id`=`cache_logs`.`cache_id` AND\n\t\t\t\t `pictures`.`object_type`=1 AND `pictures`.`object_id`=`cache_logs`.`id` AND\n\t\t\t\t `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\t\t\t 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;
}
