}
$sqlchecksum = sprintf('%u', crc32($cachesFilter . "\n" . $sqlFilter));
// check if query was already executed within the cache period
$rsMapCache = sql("SELECT `result_id` FROM `map2_result` WHERE `sqlchecksum`='&1' AND DATE_ADD(`date_created`, INTERVAL '&2' SECOND)>NOW() AND `sqlquery`='&3'", $sqlchecksum, $opt['map']['maxcacheage'], $sqlFilter);
if ($rMapCache = sql_fetch_assoc($rsMapCache)) {
$resultId = $rMapCache['result_id'];
sql("UPDATE `map2_result` SET `shared_counter`=`shared_counter`+1 WHERE `result_id`='" . ($resultId + 0) . "'");
} else {
// ensure that query is performed without errors before reserving the result_id
sql_temp_table_slave('tmpmapresult');
sql_slave("CREATE TEMPORARY TABLE &tmpmapresult (`cache_id` INT UNSIGNED NOT NULL, PRIMARY KEY (`cache_id`)) ENGINE=MEMORY");
sql_slave("INSERT INTO &tmpmapresult (`cache_id`) " . $sqlFilter);
sql("INSERT INTO `map2_result` (`slave_id`, `sqlchecksum`, `sqlquery`, `date_created`, `date_lastqueried`) VALUES ('&1', '&2', '&3', NOW(), NOW())", $db['slave_id'], $sqlchecksum, $cachesFilter . "\n" . $sqlFilter);
$resultId = sql_insert_id();
sql_slave("INSERT IGNORE INTO `map2_data` (`result_id`, `cache_id`) SELECT '&1', `cache_id` FROM &tmpmapresult", $resultId);
sql_drop_temp_table_slave('tmpmapresult');
}
sql_free_result($rsMapCache);
if ($map2_bounds) {
$rs = sql_slave("SELECT MIN(`latitude`) AS `lat_min`,\n\t\t MAX(`latitude`) AS `lat_max`,\n\t\t MIN(`longitude`) AS `lon_min`,\n\t\t MAX(`longitude`) AS `lon_max`\n FROM `map2_data`, `caches`\n\t WHERE `result_id`='&1'\n\t AND `caches`.`cache_id`=`map2_data`.`cache_id`", $resultId);
if (($rBounds = sql_fetch_assoc($rs)) && $rBounds['lat_min'] !== null) {
if ($rBounds['lat_min'] == $rBounds['lat_max'] && $rBounds['lon_min'] == $rBounds['lon_max']) {
$halfwin = 0.02;
$rBounds['lat_min'] -= $halfwin;
$rBounds['lat_max'] += $halfwin;
$rBounds['lon_min'] -= $halfwin;
$rBounds['lon_max'] += $halfwin;
}
$bounds_param = "&lat_min=" . round($rBounds['lat_min'], 5) . "&lat_max=" . round($rBounds['lat_max'], 5) . '&lon_min=' . round($rBounds['lon_min'], 5) . '&lon_max=' . round($rBounds['lon_max'], 5);
}
sql_free_result($rs);
sql_slave("INSERT INTO &topRatings (`cache_id`, `ratings`) SELECT `cache_rating`.`cache_id`, COUNT(`cache_rating`.`cache_id`) AS `ratings` FROM `cache_rating` INNER JOIN &topLocationCaches ON `cache_rating`.`cache_id`=&topLocationCaches.`cache_id` INNER JOIN `caches` ON `cache_rating`.`cache_id`=`caches`.`cache_id` WHERE `cache_rating`.`user_id`!=`caches`.`user_id` GROUP BY `cache_rating`.`cache_id`");
sql_slave("INSERT INTO &topResult (`idx`, `cache_id`, `ratings`, `founds`) \n\t\t\t\t SELECT SQL_SMALL_RESULT (&topRatings.`ratings`+1)*(&topRatings.`ratings`+1)/(IFNULL(`stat_caches`.`found`, 0)/10+1)*100 AS `idx`, \n\t\t\t\t &topRatings.`cache_id`,\n\t\t\t\t &topRatings.`ratings`, \n\t\t\t\t IFNULL(`stat_caches`.`found`, 0) AS founds\n\t\t\t\t FROM &topRatings\n\t\t\t\t INNER JOIN `caches` ON &topRatings.`cache_id`=`caches`.`cache_id`\n\t\t\t\t LEFT JOIN `stat_caches` ON `stat_caches`.`cache_id`=`caches`.`cache_id`\n\t\t\t\t ORDER BY `idx` DESC LIMIT 15");
if (sql_value_slave("SELECT COUNT(*) FROM &topResult", 0) > 10) {
$min_idx = sql_value_slave("SELECT `idx` FROM &topResult ORDER BY idx DESC LIMIT 9, 1", 0);
sql_slave("DELETE FROM &topResult WHERE `idx`<'&1'", $min_idx);
}
$rsCaches = sql_slave("SELECT SQL_BUFFER_RESULT &topResult.`idx`, \n\t\t\t\t &topResult.`ratings`, \n\t\t\t\t IFNULL(`stat_caches`.`found`, 0) AS `founds`, \n\t\t\t\t &topResult.`founds` AS `foundAfterRating`, \n\t\t\t\t &topResult.`cache_id`, \n\t\t\t\t `caches`.`name`, \n\t\t\t\t `caches`.`wp_oc` AS `wpoc`, \n\t\t\t\t `user`.`username`,\n\t\t\t\t `user`.`user_id` AS `userid`\n\t\t\t\t FROM &topResult\n\t\t\t\t INNER JOIN `caches` ON &topResult.`cache_id`=`caches`.`cache_id` \n\t\t\t\t INNER JOIN `user` ON `caches`.`user_id`=`user`.`user_id` \n\t\t\t\t LEFT JOIN `stat_caches` ON `stat_caches`.`cache_id`=`caches`.`cache_id`\n\t\t\t\t ORDER BY `idx` DESC");
$items = array();
while ($rCaches = sql_fetch_assoc($rsCaches)) {
$items[] = $rCaches;
}
sql_free_result($rsCaches);
$adm3Group['items'] = $items;
if (count($adm3Group['items']) > 0) {
$adm1Group['adm3'][] = $adm3Group;
}
$adm3Group = array();
}
sql_free_result($rsAdm3);
if (isset($adm1Group['adm3']) && count($adm1Group['adm3']) > 0) {
$tops[] = $adm1Group;
}
$adm1Group = array();
}
sql_free_result($rsAdm1);
sql_drop_temp_table_slave('topLocationCaches');
sql_drop_temp_table_slave('topRatings');
sql_drop_temp_table_slave('topResult');
$tpl->assign('tops', $tops);
}
$tpl->display();
function outputLocidSelectionForm($locSql, $options)
{
global $tpl;
global $locline, $bgcolor1, $bgcolor2;
require_once "lib2/logic/geodb.inc.php";
$urlparamString = prepareLocSelectionForm($options) . '&locid={locid}';
sql_temp_table_slave('locids');
sql_slave('CREATE TEMPORARY TABLE &locids ENGINE=MEMORY ' . $locSql);
sql_slave('ALTER TABLE &locids ADD PRIMARY KEY (`loc_id`)');
$rs = sql_slave('SELECT `geodb_textdata`.`loc_id` `loc_id`, `geodb_textdata`.`text_val` `text_val` FROM `geodb_textdata`, &locids WHERE &locids.`loc_id`=`geodb_textdata`.`loc_id` AND `geodb_textdata`.`text_type`=500100000 ORDER BY `text_val`');
$nr = 1;
$locations = '';
while ($r = sql_fetch_array($rs)) {
$thislocation = $locline;
// locationsdings zusammenbauen
$locString = '';
$land = geodb_landFromLocid($r['loc_id']);
if ($land != '') {
$locString .= htmlspecialchars($land, ENT_COMPAT, 'UTF-8');
}
$rb = geodb_regierungsbezirkFromLocid($r['loc_id']);
if ($rb != '') {
$locString .= ' > ' . htmlspecialchars($rb, ENT_COMPAT, 'UTF-8');
}
$lk = geodb_landkreisFromLocid($r['loc_id']);
if ($lk != '') {
$locString .= ' > ' . htmlspecialchars($lk, ENT_COMPAT, 'UTF-8');
}
$thislocation = mb_ereg_replace('{parentlocations}', $locString, $thislocation);
// koordinaten ermitteln
$r['loc_id'] = $r['loc_id'] + 0;
$rsCoords = sql_slave('SELECT `lon`, `lat` FROM `geodb_coordinates` WHERE loc_id=' . $r['loc_id'] . ' LIMIT 1');
if ($rCoords = sql_fetch_array($rsCoords)) {
$coordString = help_latToDegreeStr($rCoords['lat']) . ' ' . help_lonToDegreeStr($rCoords['lon']);
} else {
$coordString = '[' . $no_location_coords . ']';
}
$thislocation = mb_ereg_replace('{coords}', htmlspecialchars($coordString, ENT_COMPAT, 'UTF-8'), $thislocation);
$thislocation = mb_ereg_replace('{locationname}', htmlspecialchars($r['text_val'], ENT_COMPAT, 'UTF-8'), $thislocation);
$thislocation = mb_ereg_replace('{urlparams}', $urlparamString, $thislocation);
$thislocation = mb_ereg_replace('{locid}', urlencode($r['loc_id']), $thislocation);
$thislocation = mb_ereg_replace('{nr}', $nr, $thislocation);
$thislocation = mb_ereg_replace('{secondlocationname}', '', $thislocation);
if ($nr % 2) {
$thislocation = mb_ereg_replace('{bgcolor}', $bgcolor1, $thislocation);
} else {
$thislocation = mb_ereg_replace('{bgcolor}', $bgcolor2, $thislocation);
}
$nr++;
$locations .= $thislocation . "\n";
}
$tpl->assign('locations', $locations);
$tpl->assign('resultscount', sql_num_rows($rs));
$tpl->assign('pages', '');
sql_free_result($rs);
sql_drop_temp_table_slave('locids');
$tpl->display();
exit;
}
function search_output()
{
global $db, $opt;
global $distance_unit, $startat, $count, $sql, $sqlLimit;
$encoding = 'UTF-8';
$xmlLine = "\t<cache>\n\t\t<name><![CDATA[{cachename}]]></name>\n\t\t<owner id=\"{ownerid}\"><![CDATA[{owner}]]></owner>\n\t\t<id>{cacheid}</id>\n\t\t<waypoint>{waypoint}</waypoint>\n\t\t<hidden>{time}</hidden>\n\t\t<status id=\"{statusid}\">{status}</status>\n\t\t<lon value=\"{lonvalue}\">{lon}</lon>\n\t\t<lat value=\"{latvalue}\">{lat}</lat>\n\t\t<distance unit=\"" . $distance_unit . "\">{distance}</distance>\n\t\t<type id=\"{typeid}\">{type}</type>\n\t\t<difficulty>{difficulty}</difficulty>\n\t\t<terrain>{terrain}</terrain>\n\t\t<size id=\"{sizeid}\">{container}</size>\n\t\t<country id=\"{countryid}\">{country}</country>\n\t\t<link><![CDATA[" . $opt['page']['default_absolute_url'] . "viewcache.php?wp={waypoint}]]></link>\n\t\t<desc><![CDATA[{shortdesc}]]></desc>\n\t\t<hints><![CDATA[{hints}]]></hints>\n\t</cache>\n";
// create temporary table
sql_temp_table_slave('searchtmp');
sql_slave('CREATE TEMPORARY TABLE &searchtmp SELECT SQL_BUFFER_RESULT SQL_CALC_FOUND_ROWS ' . $sql . $sqlLimit);
$resultcount = sql_value_slave('SELECT FOUND_ROWS()', 0);
$rsCount = sql_slave('SELECT COUNT(*) `count` FROM &searchtmp');
$rCount = sql_fetch_array($rsCount);
mysql_free_result($rsCount);
// start output
if (!$db['debug']) {
header("Content-type: application/xml; charset=" . $encoding);
//header("Content-Disposition: attachment; filename=" . $sFilebasename . ".txt");
echo "<?xml version=\"1.0\" encoding=\"" . $encoding . "\"?>\n";
echo "<result>\n";
echo "\t<docinfo>\n";
echo "\t\t<results>" . $rCount['count'] . "</results>\n";
echo "\t\t<startat>" . $startat . "</startat>\n";
echo "\t\t<perpage>" . $count . "</perpage>\n";
echo "\t\t<total>" . $resultcount . "</total>\n";
echo "\t</docinfo>\n";
}
$rs = sql_slave("SELECT &searchtmp.`cache_id` `cacheid`,\n &searchtmp.`longitude` `longitude`,\n &searchtmp.`latitude` `latitude`,\n `caches`.`wp_oc` `waypoint`,\n `caches`.`date_hidden` `date_hidden`,\n `caches`.`name` `name`,\n `caches`.`country` `countrycode`,\n `caches`.`terrain` `terrain`,\n `caches`.`difficulty` `difficulty`,\n `caches`.`desc_languages` `desc_languages`,\n `cache_size`.`name` `size`,\n `cache_size`.`id` `size_id`,\n `cache_type`.`name` `type`,\n `cache_type`.`id` `type_id`,\n `cache_status`.`name` `status`,\n `cache_status`.`id` `status_id`,\n `user`.`username` `username`,\n `user`.`user_id` `user_id`,\n `cache_desc`.`desc` `desc`,\n `cache_desc`.`short_desc` `short_desc`,\n `cache_desc`.`hint` `hint`,\n `cache_desc`.`desc_html` `html`,\n &searchtmp.`distance` `distance`,\n `sys_trans_text`.`text` `country`\n FROM &searchtmp\n INNER JOIN `caches` ON &searchtmp.`cache_id`=`caches`.`cache_id`\n INNER JOIN `user` ON &searchtmp.`user_id`=`user`.`user_id`\n INNER JOIN `cache_desc`\n ON `caches`.`cache_id`=`cache_desc`.`cache_id` \n AND `caches`.`default_desclang`=`cache_desc`.`language`\n INNER JOIN `cache_type` ON `caches`.`type`=`cache_type`.`id`\n INNER JOIN `cache_status` ON `caches`.`status`=`cache_status`.`id`\n INNER JOIN `cache_size` ON `caches`.`size`=`cache_size`.`id`\n LEFT JOIN `countries` ON `countries`.`short`=`caches`.`country`\n LEFT JOIN `sys_trans_text` ON `sys_trans_text`.`trans_id`=`countries`.`trans_id`\n AND `sys_trans_text`.`lang`='&1'", $opt['template']['locale']);
while ($r = sql_fetch_array($rs)) {
if (strlen($r['desc_languages']) > 2) {
$r = get_locale_desc($r);
}
$thisline = $xmlLine;
$lat = sprintf('%01.5f', $r['latitude']);
$thisline = str_replace('{lat}', help_latToDegreeStr($lat), $thisline);
$thisline = str_replace('{latvalue}', $lat, $thisline);
$lon = sprintf('%01.5f', $r['longitude']);
$thisline = str_replace('{lon}', help_lonToDegreeStr($lon), $thisline);
$thisline = str_replace('{lonvalue}', $lon, $thisline);
$time = date('d.m.Y', strtotime($r['date_hidden']));
$thisline = str_replace('{time}', $time, $thisline);
$thisline = str_replace('{waypoint}', $r['waypoint'], $thisline);
$thisline = str_replace('{cacheid}', $r['cacheid'], $thisline);
$thisline = str_replace('{cachename}', filterevilchars($r['name']), $thisline);
$thisline = str_replace('{country}', text_xmlentities($r['country']), $thisline);
$thisline = str_replace('{countryid}', $r['country'], $thisline);
if ($r['hint'] == '') {
$thisline = str_replace('{hints}', '', $thisline);
} else {
$thisline = str_replace('{hints}', str_rot13_gc(decodeEntities(filterevilchars(strip_tags($r['hint'])))), $thisline);
}
$thisline = str_replace('{shortdesc}', filterevilchars($r['short_desc']), $thisline);
if ($r['html'] == 0) {
$thisline = str_replace('{htmlwarn}', '', $thisline);
$thisline = str_replace('{desc}', filterevilchars(strip_tags($r['desc'])), $thisline);
} else {
$thisline = str_replace('{htmlwarn}', ' (Text converted from HTML)', $thisline);
$thisline = str_replace('{desc}', html2txt(filterevilchars($r['desc'])), $thisline);
}
$thisline = str_replace('{type}', $r['type'], $thisline);
$thisline = str_replace('{typeid}', $r['type_id'], $thisline);
$thisline = str_replace('{container}', $r['size'], $thisline);
$thisline = str_replace('{sizeid}', $r['size_id'], $thisline);
$thisline = str_replace('{status}', $r['status'], $thisline);
$thisline = str_replace('{statusid}', $r['status_id'], $thisline);
$difficulty = sprintf('%01.1f', $r['difficulty'] / 2);
$thisline = str_replace('{difficulty}', $difficulty, $thisline);
$terrain = sprintf('%01.1f', $r['terrain'] / 2);
$thisline = str_replace('{terrain}', $terrain, $thisline);
$thisline = str_replace('{owner}', filterevilchars($r['username']), $thisline);
$thisline = str_replace('{ownerid}', filterevilchars($r['user_id']), $thisline);
$thisline = str_replace('{distance}', text_xmlentities(sprintf("%01.1f", $r['distance'])), $thisline);
$thisline = lf2crlf($thisline);
if (!$db['debug']) {
echo $thisline;
}
}
mysql_free_result($rs);
sql_drop_temp_table_slave('searchtmp');
if (!$db['debug']) {
echo "</result>\n";
}
}
sql_free_result($rsPic);
}
$pics--;
$rLog['first'] = false;
if (isset($caches_logged)) {
if (!isset($caches_logged[$rLog['cache_id']])) {
$caches_logged[$rLog['cache_id']] = true;
$rLog['first'] = true;
}
}
$newLogs[] = $rLog;
}
sql_free_result($rsLogs);
sql_drop_temp_table_slave('loglist');
if ($optimize_for_latest_logs) {
sql_drop_temp_table_slave('loglist0');
}
$tpl->assign('newLogs', $newLogs);
$tpl->assign('addpiclines', max($pics - 1, 0));
$tpl->assign('newLogsPerCountry', $newLogsPerCountry);
$tpl->assign('logselection', $logselection);
$tpl->assign('countryCode', $country);
$tpl->assign('countryName', sql_value("SELECT IFNULL(`sys_trans_text`.`text`, `countries`.`name`)\n FROM `countries`\n LEFT JOIN `sys_trans` ON `countries`.`trans_id`=`sys_trans`.`id`\n LEFT JOIN `sys_trans_text` ON `sys_trans`.`id`=`sys_trans_text`.`trans_id` AND `sys_trans_text`.`lang`='&2'\n WHERE `countries`.`short`='&1'", '', $country ? $country : $login->getUserCountry(), $opt['template']['locale']));
$tpl->assign('mainCountryName', sql_value("SELECT IFNULL(`sys_trans_text`.`text`, `countries`.`name`)\n FROM `countries`\n LEFT JOIN `sys_trans` ON `countries`.`trans_id`=`sys_trans`.`id`\n LEFT JOIN `sys_trans_text` ON `sys_trans`.`id`=`sys_trans_text`.`trans_id` AND `sys_trans_text`.`lang`='&2'\n WHERE `countries`.`short`='&1'", '', $opt['page']['main_country'], $opt['template']['locale']));
$tpl->assign('paging', $paging);
if ($paging) {
$pager = new pager($_SERVER["SCRIPT_NAME"] . '?startat={offset}' . $urlparams);
$pager->make_from_offset($startat, $total_logs, $logcount);
}
}
$tpl->display();
<?php
/***************************************************************************
* You can find the license in the docs directory
*
* Unicode Reminder メモ
***************************************************************************/
require './lib2/web.inc.php';
$tpl->name = 'newcaches_withoutown';
$tpl->menuitem = MNU_START_NEWCACHES_WITHOUTOWN;
$tpl->caching = true;
$tpl->cache_lifetime = 3600;
if (!$tpl->is_cached()) {
require $opt['rootpath'] . 'lib2/logic/cacheIcon.inc.php';
$newCaches = array();
sql_temp_table_slave('cachelist');
sql_slave("CREATE TEMPORARY TABLE &cachelist (`cache_id` INT(11) PRIMARY KEY) SELECT `cache_id` FROM `caches` INNER JOIN `cache_status` ON `caches`.`status`=`cache_status`.`id` WHERE `cache_status`.`allow_user_view`=1 AND `country`!='DE' ORDER BY `date_created` DESC LIMIT 200");
$rsNewCaches = sql_slave("SELECT IFNULL(`sys_trans_text`.`text`, `countries`.`name`) `country_name`, `caches`.`cache_id` `cacheid`, `caches`.`wp_oc` `wpoc`, `user`.`user_id` `userid`, `caches`.`country` `country`, `caches`.`name` `cachename`, `user`.`username` `username`, `caches`.`date_created` `date_created`, `cache_type`.`icon_large` `icon_large` \r\n\t\t FROM `caches`\r\n\t\t INNER JOIN `user` ON `caches`.`user_id`=`user`.`user_id`\r\n\t\t INNER JOIN `cache_type` ON `caches`.`type`=`cache_type`.`id`\r\n\t\t INNER JOIN &cachelist ON &cachelist.`cache_id`=`caches`.`cache_id`\r\n\t\t INNER JOIN `countries` ON `countries`.`short`=`caches`.`country`\r\n\t\t LEFT JOIN `sys_trans` ON `countries`.`trans_id`=`sys_trans`.`id`\r\n\t\t LEFT JOIN `sys_trans_text` ON `sys_trans`.`id`=`sys_trans_text`.`trans_id` AND `sys_trans_text`.`lang`='&1'\r\n\t\t ORDER BY `country_name`, `caches`.`date_created` DESC LIMIT 200", $opt['template']['locale']);
while ($rNewCache = sql_fetch_assoc($rsNewCaches)) {
$rNewCache['icon_large'] = getSmallCacheIcon($rNewCache['icon_large']);
$newCaches[] = $rNewCache;
}
sql_free_result($rsNewCaches);
sql_drop_temp_table_slave('cachelist');
$tpl->assign('newCaches', $newCaches);
}
$tpl->display();
