*
* Execute search / filtering request for map2.php
* (use caching of the same quries)
***************************************************************************/
if (!$enable_mapdisplay) {
$tpl->error(ERROR_INVALID_OPERATION);
}
$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;
/***************************************************************************
* You can find the license in the docs directory
*
* Unicode Reminder メモ
*
* Display some status information about the server and Opencaching
***************************************************************************/
require './lib2/web.inc.php';
$tpl->name = 'tops';
$tpl->menuitem = MNU_CACHES_TOPS;
$tpl->caching = true;
$tpl->cache_lifetime = 86400;
if (!$tpl->is_cached()) {
sql_temp_table_slave('topLocationCaches');
sql_temp_table_slave('topRatings');
sql_temp_table_slave('topResult');
sql_slave("CREATE TEMPORARY TABLE &topLocationCaches (`cache_id` INT(11) PRIMARY KEY) ENGINE=MEMORY");
sql_slave("CREATE TEMPORARY TABLE &topRatings (`cache_id` INT(11) PRIMARY KEY, `ratings` INT(11)) ENGINE=MEMORY");
sql_slave("CREATE TEMPORARY TABLE &topResult (`idx` INT(11), `cache_id` INT(11) PRIMARY KEY, `ratings` INT(11), `founds` INT(11)) ENGINE=MEMORY");
$tops = array();
$adm1Group = array();
$rsAdm1 = sql_slave('SELECT SQL_BUFFER_RESULT SQL_SMALL_RESULT DISTINCT `adm1`, `code1` FROM `cache_location` WHERE NOT ISNULL(`adm1`) ORDER BY `adm1` ASC');
while ($rAdm1 = sql_fetch_assoc($rsAdm1)) {
$adm1Group['name'] = $rAdm1['adm1'];
$adm3Group = array();
$rsAdm3 = sql_slave("SELECT SQL_BUFFER_RESULT SQL_SMALL_RESULT DISTINCT `adm3` FROM `cache_location` WHERE `code1`='&1' ORDER BY `adm3` ASC", $rAdm1['code1']);
while ($rAdm3 = sql_fetch_assoc($rsAdm3)) {
$adm3Group['name'] = $rAdm3['adm3'];
sql_slave("TRUNCATE TABLE &topLocationCaches");
sql_slave("TRUNCATE TABLE &topRatings");
sql_slave("TRUNCATE TABLE &topResult");
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";
}
}
$tpl->change_country_inpage = true;
if (!$tpl->is_cached()) {
if ($paging) {
sql_enable_foundrows();
}
if ($optimize_for_latest_logs) {
// filtering a large number of logs is very expensive;
// therefore we preselect the newest logs if the main country is selected
sql_temp_table_slave('loglist0');
sql_slave("\n CREATE TEMPORARY TABLE &loglist0\n (SELECT\n `id`, `cache_id`, `user_id`, `date_created`, `date`, `type`,\n `needs_maintenance`, `listing_outdated`, `order_date`\n FROM `cache_logs`\n ORDER BY `date_created` DESC\n LIMIT " . 4 * $logcount . ")");
$fromtable = '`&loglist0`';
} else {
$fromtable = '`cache_logs`';
}
$add_where = str_replace("{fromtable}", $fromtable, $add_where);
sql_temp_table_slave('loglist');
sql_slave("CREATE TEMPORARY TABLE &loglist (`id` INT(11) PRIMARY KEY)\n SELECT " . ($paging ? "SQL_CALC_FOUND_ROWS" : "") . " " . $fromtable . ".`id`\n FROM " . $fromtable . "\n INNER JOIN `caches` ON " . $fromtable . ".`cache_id`=`caches`.`cache_id`\n INNER JOIN `cache_status` ON `caches`.`status`=`cache_status`.`id`\n INNER JOIN `user` ON " . $fromtable . ".`user_id`=`user`.`user_id`\n WHERE `cache_status`.`allow_user_view`=1\n AND `caches`.`country` LIKE '&1'\n AND `caches`.`country`<>'&2'\n " . $add_where . "\n ORDER BY " . str_replace("{fromtable}", $fromtable, $orderByDate) . $fromtable . ".`date_created` DESC\n LIMIT &3, &4", $include_country, $exclude_country, $startat, $logcount);
if ($paging) {
$total_logs = sql_value("SELECT FOUND_ROWS()", 0);
sql_foundrows_done();
$paging = $total_logs > $logcount;
}
if ($newLogsPerCountry) {
$orderByCountry = '`country_name` ASC, ';
} else {
$orderByCountry = '';
}
$rsLogs = sql_slave("SELECT IFNULL(`sys_trans_text`.`text`, `countries`.`name`) AS `country_name`,\n `cache_logs`.`id`,\n `cache_logs`.`date_created`,\n `cache_logs`.`date`,\n `caches`.`name` AS `cachename`,\n `caches`.`wp_oc`,\n `caches`.`country` AS `country`,\n `cache_logs`.`type`,\n `cache_logs`.`oc_team_comment`,\n `cache_logs`.`needs_maintenance`,\n `cache_logs`.`listing_outdated`,\n `cacheloguser`.`user_id`,\n `cacheloguser`.`username`,\n `cache_logs`.`cache_id`,\n `cache_rating`.`rating_date` IS NOT NULL AND `cache_logs`.`type` IN (1,7) AS `recommended`,\n `caches_attributes`.`attrib_id` IS NOT NULL AS `oconly`,\n '' AS `pic_uuid`,\n 0 AS `picshown`,\n (SELECT COUNT(*) FROM `pictures` WHERE `object_type`=1 AND `object_id`=`cache_logs`.`id`) AS `pics`\n FROM &loglist\n INNER JOIN `cache_logs`\n ON &loglist.`id`=`cache_logs`.`id`\n INNER JOIN `caches`\n ON `cache_logs`.`cache_id`=`caches`.`cache_id`\n INNER JOIN `user` AS `cacheloguser`\n ON `cache_logs`.`user_id`=`cacheloguser`.`user_id`\n INNER JOIN `countries`\n ON `caches`.`country`=`countries`.`short`\n LEFT JOIN `sys_trans_text`\n ON `countries`.`trans_id`=`sys_trans_text`.`trans_id`\n AND `sys_trans_text`.`lang`='&1'\n LEFT JOIN `cache_logs_restored`\n ON `cache_logs_restored`.`id`=`cache_logs`.`id`\n LEFT JOIN `cache_rating`\n ON `cache_rating`.`cache_id`=`caches`.`cache_id`\n AND `cache_rating`.`user_id`=`cache_logs`.`user_id`\n AND `cache_rating`.`rating_date`=`cache_logs`.`date`\n LEFT JOIN `caches_attributes`\n ON `caches_attributes`.`cache_id`=`caches`.`cache_id`\n AND `caches_attributes`.`attrib_id`=6\n WHERE IFNULL(`cache_logs_restored`.`restored_by`,0)=0\n ORDER BY " . $orderByCountry . str_replace("{fromtable}", "`cache_logs`", $orderByDate) . "`cache_logs`.`date_created` DESC", $opt['template']['locale']);
$newLogs = array();
$lines_per_pic = 5;
$tpl->assign('lines_per_pic', $lines_per_pic);
<?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();