* * 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();