function cleanup_mapresult2($slaveId)
{
global $opt;
// cleanup old entries
$rs = sql("SELECT SQL_BUFFER_RESULT `result_id` FROM `map2_result` WHERE DATE_ADD(`date_created`, INTERVAL '&1' SECOND)<NOW()", $opt['map']['maxcacheage']);
while ($r = sql_fetch_assoc($rs)) {
sql("DELETE FROM `map2_result` WHERE `result_id`='&1'", $r['result_id']);
}
sql_free_result($rs);
// now reduce table size? (29 bytes is the average row size)
if (sql_value_slave("SELECT COUNT(*) FROM `map2_data`", 0) > $opt['map']['maxcachesize'] / 29) {
while (sql_value_slave("SELECT COUNT(*) FROM `map2_data`", 0) > $opt['map']['maxcachereducedsize'] / 29) {
$resultId = sql_value("SELECT `result_id` FROM `map2_result` WHERE `slave_id`='&1' ORDER BY `date_lastqueried` DESC LIMIT 1", 0, $slaveId);
if ($resultId == 0) {
return;
}
sql("DELETE FROM `map2_result` WHERE `result_id`='&1'", $resultId);
}
}
$nMinId = sql_value("SELECT MIN(`result_id`) FROM `map2_result`", 0);
if ($nMinId == 0) {
sql("DELETE FROM `map2_data`");
} else {
sql("DELETE FROM `map2_data` WHERE `result_id`<'&1'", $nMinId);
}
}
function getCacheIcon($user_id, $cache_id, $cache_status, $cache_userid, $iconname)
{
$iconname = mb_eregi_replace("cache/", "", $iconname);
// for old cache_type table contents
$iconext = "." . mb_eregi_replace("^.*\\.", "", $iconname);
$iconname = mb_eregi_replace("\\..*", "", $iconname);
// add status
switch ($cache_status) {
case 1:
$iconname .= "-s";
break;
case 2:
$iconname .= "-n";
break;
case 3:
$iconname .= "-a";
break;
case 4:
$iconname .= "-a";
break;
case 5:
$iconname .= "-s";
break;
// fix for RT ticket #3403
// fix for RT ticket #3403
case 6:
$iconname .= "-a";
break;
case 7:
$iconname .= "-a";
break;
}
// mark if (not) found
if ($user_id) {
if ($cache_userid == $user_id) {
$iconname .= "-owner";
} else {
$logtype = sql_value_slave("\n\t\t\t\tSELECT `type`\n\t\t\t\tFROM `cache_logs`\n\t\t\t\tWHERE `cache_id`='&1' AND `user_id`='&2' AND `type` IN (1,2,7)\n\t\t\t\tORDER BY `type`\n\t\t\t\tLIMIT 1", 0, $cache_id, $user_id);
if ($logtype == 1 || $logtype == 7) {
$iconname .= '-found';
} elseif ($logtype == 2) {
$iconname .= '-dnf';
}
}
}
return $iconname . $iconext;
}
function outputUniidSelectionForm($uniSql, $options)
{
global $tpl;
// settings
global $locline, $secondlocationname;
$urlparamString = prepareLocSelectionForm($options);
sql_temp_table_slave('uniids');
sql_slave('CREATE TEMPORARY TABLE &uniids ENGINE=MEMORY ' . $uniSql);
sql_slave('ALTER TABLE &uniids ADD PRIMARY KEY (`uni_id`)');
// locidsite
$locidsite = isset($_REQUEST['locidsite']) ? $_REQUEST['locidsite'] : 0;
if (!is_numeric($locidsite)) {
$locidsite = 0;
}
$count = sql_value_slave('SELECT COUNT(*) FROM &uniids', 0);
$tpl->assign('resultscount', $count);
// create page browser
$pager = new pager('search.php?' . $urlparamString . '&locidsite={offset}');
$pager->make_from_offset($locidsite, ceil($count / 20), 1);
// create locations list
$rs = sql_slave('SELECT `gns_locations`.`rc` `rc`, `gns_locations`.`cc1` `cc1`, `gns_locations`.`admtxt1` `admtxt1`, `gns_locations`.`admtxt2` `admtxt2`, `gns_locations`.`admtxt3` `admtxt3`, `gns_locations`.`admtxt4` `admtxt4`, `gns_locations`.`uni` `uni_id`, `gns_locations`.`lon` `lon`, `gns_locations`.`lat` `lat`, `gns_locations`.`full_name` `full_name`, &uniids.`olduni` `olduni` FROM `gns_locations`, &uniids WHERE &uniids.`uni_id`=`gns_locations`.`uni` ORDER BY `gns_locations`.`full_name` ASC LIMIT ' . $locidsite * 20 . ', 20');
$nr = $locidsite * 20 + 1;
$locations = '';
while ($r = sql_fetch_array($rs)) {
$thislocation = $locline;
$locString = '';
if ($r['admtxt1'] != '') {
if ($locString != '') {
$locString .= ' > ';
}
$locString .= htmlspecialchars($r['admtxt1'], ENT_COMPAT, 'UTF-8');
}
if ($r['admtxt2'] != '') {
if ($locString != '') {
$locString .= ' > ';
}
$locString .= htmlspecialchars($r['admtxt2'], ENT_COMPAT, 'UTF-8');
}
/* if ($r['admtxt3'] != '')
{
if ($locString != '') $locString .= ' > ';
$locString .= htmlspecialchars($r['admtxt3'], ENT_COMPAT, 'UTF-8');
}
*/
if ($r['admtxt4'] != '') {
if ($locString != '') {
$locString .= ' > ';
}
$locString .= htmlspecialchars($r['admtxt4'], ENT_COMPAT, 'UTF-8');
}
$thislocation = mb_ereg_replace('{parentlocations}', $locString, $thislocation);
// koordinaten ermitteln
$coordString = help_latToDegreeStr($r['lat']) . ' ' . help_lonToDegreeStr($r['lon']);
$thislocation = mb_ereg_replace('{coords}', htmlspecialchars($coordString, ENT_COMPAT, 'UTF-8'), $thislocation);
if ($r['olduni'] != 0) {
// der alte name wurde durch den native-wert ersetzt
$thissecloc = $secondlocationname;
$r['olduni'] = $r['olduni'] + 0;
$rsSecLoc = sql_slave('SELECT full_name FROM gns_locations WHERE uni=' . $r['olduni']);
$rSecLoc = sql_fetch_assoc($rsSecLoc);
$thissecloc = mb_ereg_replace('{secondlocationname}', htmlspecialchars($rSecLoc['full_name'], ENT_COMPAT, 'UTF-8'), $thissecloc);
sql_free_result($rsSecLoc);
$thislocation = mb_ereg_replace('{secondlocationname}', $thissecloc, $thislocation);
} else {
$thislocation = mb_ereg_replace('{secondlocationname}', '', $thislocation);
}
$thislocation = mb_ereg_replace('{locationname}', htmlspecialchars($r['full_name'], ENT_COMPAT, 'UTF-8'), $thislocation);
$thislocation = mb_ereg_replace('{urlparams}', $urlparamString . '&locid={locid}', $thislocation);
$thislocation = mb_ereg_replace('{locid}', urlencode($r['uni_id']), $thislocation);
$thislocation = mb_ereg_replace('{nr}', $nr, $thislocation);
$nr++;
$locations .= $thislocation . "\n";
}
sql_free_result($rs);
sql_drop_temp_table_slave('uniids');
$tpl->assign('locations', $locations);
$tpl->display();
exit;
}
function output_searchresult($nResultId, $compact, $nLon1, $nLon2, $nLat1, $nLat2, $cachenames, $smallmap, $showlockedcaches)
{
global $login, $opt, $useragent_msie;
// check if data is available and connect the right slave server
$nSlaveId = sql_value("SELECT `slave_id` FROM `map2_result` WHERE `result_id`='&1' AND DATE_ADD(`date_created`, INTERVAL '&2' SECOND)>NOW()", -2, $nResultId, $opt['map']['maxcacheage']);
if ($nSlaveId == -2) {
echo '<searchresult count="0" available="0">';
echo '</searchresult>';
exit;
}
sql_connect_slave($nSlaveId);
sql("UPDATE `map2_result` SET `request_counter`=`request_counter`+1, `date_lastqueried`=NOW() WHERE `result_id`='&1'", $nResultId);
// execute query and return search result
$nRecordCount = sql_value_slave("SELECT COUNT(*) FROM `map2_data` INNER JOIN `caches` ON `map2_data`.`cache_id`=`caches`.`cache_id` WHERE `map2_data`.`result_id`='&1' AND `caches`.`longitude`>'&2' AND `caches`.`longitude`<'&3' AND `caches`.`latitude`>'&4' AND `caches`.`latitude`<'&5'", 0, $nResultId, $nLon1, $nLon2, $nLat1, $nLat2);
// TODO: SQL_CALC_FOUND_ROWS + $nRecordCount = sql_value_slave("SELECT FOUND_ROWS()", 0);
// determine max. number of records to send
$maxrecords = $opt['map']['maxrecords'] + 0;
if ($login->userid > 0) {
$user_maxrecords = sql_value("SELECT option_value FROM user_options WHERE user_id='&1' AND option_id=8", $opt['map']['maxrecords'] + 0, $login->userid);
if ($user_maxrecords > 0 && (!$useragent_msie || $user_maxrecords < $maxrecords)) {
$maxrecords = min(max($user_maxrecords, $opt['map']['min_maxrecords']), $opt['map']['max_maxrecords']);
}
} else {
$user_maxrecords = 0;
}
if ($smallmap && $user_maxrecords == 0 && $maxrecords > 1000) {
$maxrecords = floor($maxrecords * 0.65);
}
$bMaxRecordReached = $nRecordCount > $maxrecords;
// output data
echo '<searchresult count="' . xmlentities($nRecordCount) . '" available="1"' . ' maxrecordreached="' . ($bMaxRecordReached ? '1' : '0') . '">' . "\n";
if (!$bMaxRecordReached) {
$namequery = $cachenames ? ", `caches`.`name` AS `cachename`" : "";
$rs = sql_slave("SELECT SQL_BUFFER_RESULT \n distinct `caches`.`wp_oc`,\n `caches`.`longitude`, `caches`.`latitude`,\n `caches`.`type`, \n `caches`.`status`>1 AS `inactive`,\n `caches`.`type`=6 AND `caches`.`date_hidden`+INTERVAL 1 DAY < NOW() AS `oldevent`,\n `user`.`user_id`='&6' AS `owned`,\n IF(`found_logs`.`id` IS NULL, 0, 1) AS `found`,\n IF(`found_logs`.`id` IS NULL AND `notfound_logs`.`id` IS NOT NULL, 1, 0) AS `notfound`,\n IF(`caches_attributes`.`attrib_id` IS NULL, 0, 1) AS `oconly`" . $namequery . "\n FROM `map2_data`\n INNER JOIN `caches` ON `map2_data`.`cache_id`=`caches`.`cache_id`\n INNER JOIN `user` ON `user`.`user_id`=`caches`.`user_id`\n LEFT JOIN `cache_logs` `found_logs` ON `found_logs`.`cache_id`=`caches`.`cache_id` AND `found_logs`.`user_id`='&6' AND `found_logs`.`type` IN (1,7)\n LEFT JOIN `cache_logs` `notfound_logs` ON `notfound_logs`.`cache_id`=`caches`.`cache_id` AND `notfound_logs`.`user_id`='&6' AND `notfound_logs`.`type`=2\n LEFT JOIN `caches_attributes` ON `caches_attributes`.`cache_id`=`caches`.`cache_id` AND `caches_attributes`.`attrib_id`=6\n WHERE `map2_data`.`result_id`='&1' AND `caches`.`longitude`>'&2' AND `caches`.`longitude`<'&3' AND `caches`.`latitude`>'&4' AND `caches`.`latitude`<'&5'\n\t AND (`caches`.`status`<>5 OR `caches`.`user_id`='&6') /* hide unpublished caches */\n\t AND `caches`.`status`<>'&7' /* ... and vandalized listings, locked duplicates etc. */\n\t AND `caches`.`status`<>7 /* ... and locked/invisible caches */\n\t\t\t\t\t\t\t\t\t ORDER BY `caches`.`status` DESC, `oconly` AND NOT (`found` OR `notfound`), NOT (`found` OR `notfound`), `caches`.`type`<>4, MD5(`caches`.`name`)\n\t\t\t\t\t\t\t\t\t LIMIT &8", $nResultId, $nLon1, $nLon2, $nLat1, $nLat2, $login->userid, $showlockedcaches ? 0 : 6, $maxrecords);
while ($r = sql_fetch_assoc($rs)) {
$flags = 0;
if ($r['owned']) {
$flags |= 1;
}
if ($r['found']) {
$flags |= 2;
}
if ($r['notfound']) {
$flags |= 4;
}
if ($r['inactive'] || $r['oldevent']) {
$flags |= 8;
}
if ($r['oconly']) {
$flags |= 16;
}
if ($compact) {
echo '<c d="' . xmlentities($r['wp_oc'] . '/' . round($r['longitude'], 5) . '/' . round($r['latitude'], 5) . '/' . $r['type'] . '/' . $flags) . '"' . (isset($r['cachename']) ? ' n="' . xmlentities($r['cachename']) . '"' : '') . ' />';
} else {
echo '<cache wp="' . xmlentities($r['wp_oc']) . '"' . ' lon="' . xmlentities(round($r['longitude'], 5)) . '"' . ' lat="' . xmlentities(round($r['latitude'], 5)) . '"' . ' type="' . xmlentities($r['type']) . '"' . (isset($r['cachename']) ? ' n="' . xmlentities($r['cachename']) . '"' : '') . ' f="' . xmlentities($flags) . '" />' . "\n";
}
}
sql_free_result($rs);
}
echo '</searchresult>';
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";
}
}
./lib/search.ovl.inc.php
-------------------
begin : November 5 2005
copyright : (C) 2005 The OpenCaching Group
forum contact at : http://www.opencaching.com/phpBB2
***************************************************************************/
/***************************************************************************
*
* This program is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation; either version 2 of the License, or
* (at your option) any later version.
*
***************************************************************************/
/****************************************************************************
Unicode Reminder メモ
****************************************************************************/
// result already cached
if (sql_value_slave("SELECT COUNT(*) FROM `mapresult` WHERE `query_id`=" . ($options['queryid'] + 0), 0) > 0) {
echo "READY";
exit;
}
sql_slave("CREATE TEMPORARY TABLE `tmpmapresult` (`query_id` INT UNSIGNED NOT NULL DEFAULT " . ($options['queryid'] + 0) . ", `cache_id` INT UNSIGNED NOT NULL, PRIMARY KEY (`query_id`, `cache_id`)) ENGINE=MEMORY");
sql_slave("INSERT INTO `tmpmapresult` (`cache_id`) " . $sqlFilter);
sql_slave("INSERT INTO `mapresult_data` (`query_id`, `cache_id`) SELECT `query_id`, `cache_id` FROM `tmpmapresult`");
sql_slave("INSERT INTO `mapresult` (`query_id`, `date_created`) VALUES ('&1', NOW())", $options['queryid']);
echo "READY";
exit;
function get_logpics($purpose, $userid = 0, $cacheid = 0)
{
global $login;
$fields = "`pics`.`uuid` AS `pic_uuid`, `pics`.`url` AS `pic_url`,\n\t\t\t `pics`.`title`, `pics`.`date_created`,\n\t\t\t `logs`.`user_id`, `logs`.`cache_id`,\n\t\t\t `logs`.`date` AS `logdate`, `pics`.`date_created` < LEFT(NOW(),4) AS `oldyear`,\n\t\t\t\t\t\t\t\t\t`logs`.`id` AS `logid`, `logs`.`type` AS `logtype`";
$join_logs = "INNER JOIN `cache_logs` `logs` ON `logs`.`id`=`pics`.`object_id`";
$join_caches = "INNER JOIN `caches` ON `caches`.`cache_id`=`logs`.`cache_id`";
$join_cachestatus = "INNER JOIN `cache_status` ON `caches`.`status`=`cache_status`.`id` AND `allow_user_view`=1";
$join_user = "******";
$rs = false;
switch ($purpose) {
case LOGPICS_FOR_STARTPAGE_GALLERY:
// one pic per user and day,
// one pic per cache and day
// no spoilers, no bad data, no invisible or unpublished caches
// The group-by via nested query make this whole thing sufficiently performant.
// Direct group-bys combined with the wheres are awful slow, and no kind of
// index seems to be good enough to speed it up.
// Indexing the for the inner WHERE seems rather useless, as it filters out
// only a few percent of caches. We must rely on fast data caching.
$rs = sql_slave("SELECT {$fields}, `user`.`username`, `pics`.`date_created` AS `picdate`\n\t\t\t\t FROM (SELECT * FROM\n\t\t\t\t (SELECT `uuid`, `url`, `title`, `date_created`, `object_id` FROM `pictures`\n \t\t WHERE `local`=1 AND `display`=1 AND `spoiler`=0 AND `unknown_format`=0\n\t \t AND `object_type`=1\n\t ORDER BY `date_created` DESC\n\t\t LIMIT 240) `piics`\n\t\t\t\t \t\t\t /* 20 times reserve for filtering out user dups, cache dups and invisibles */\n GROUP BY `object_id`, LEFT(`date_created`,10)) `pics` /* max. 1 pic per cache and day */\n {$join_logs}\n {$join_caches}\n {$join_cachestatus}\n {$join_user}\n GROUP BY `user`.`user_id`, LEFT(`pics`.`date_created`,10) /* max. 1 pic per user and day */\n\t ORDER BY `pics`.`date_created` DESC\n\t\t\t\t\t LIMIT 6");
break;
case LOGPICS_FOR_NEWPICS_GALLERY:
// like above, without the "one pic per cache and day" condition
// This saves us one grouped subquery.
$rs = sql_slave("SELECT {$fields}, `user`.`username`, `pics`.`date_created` AS `picdate`\n FROM (SELECT `uuid`, `url`, `title`, `date_created`, `object_id` FROM `pictures`\n\t\t\t\t\t\t\t\t WHERE `local`=1 AND `display`=1 AND `spoiler`=0 AND `unknown_format`=0\n\t\t\t AND `object_type`=1\n\t\t \t ORDER BY `date_created` DESC\n\t\t\t LIMIT 600) `pics`\n\t\t\t /* 10 times reserve for filtering out user dups and invisibles */\n \t {$join_logs}\n \t {$join_caches}\n \t {$join_cachestatus}\n \t {$join_user}\n\t\t\t GROUP BY `user`.`user_id`, LEFT(`pics`.`date_created`,10)\n\t\t\t ORDER BY `date_created` DESC\n\t\t\t\t\t\t\t LIMIT &1", MAX_PICTURES_PER_GALLERY_PAGE);
break;
case LOGPICS_FOR_USER_STAT:
// just count all the logpics of one user
// It's faster, sensible and consistend with cache and log handling to count
// also invisible data here. Actually, it is present, the pic was made and
// uploaded with a log, and it is still visible for the logger himself
// (and hopfully some time for all, independend of the invisible listing!).
$result = sql_value_slave("SELECT COUNT(*)\n\t\t FROM `pictures` `pics`\n\t\t\t {$join_logs}\n\t\t\t\t\t WHERE `pics`.`object_type`=1 AND `logs`.`user_id`='&1'", 0, $userid);
break;
case LOGPICS_FOR_USER_GALLERY:
// all pics of one user, except spoilers and invisibles
$rs = sql("SELECT {$fields}, `logs`.`date` AS `picdate`\n\t\t\t \t FROM `pictures` `pics`\n\t\t {$join_logs}\n\t\t {$join_caches}\n\t\t {$join_cachestatus}\n\t\t WHERE `object_type`=1 AND `logs`.`user_id`='&1' AND NOT `spoiler`\n\t\t\t\t ORDER BY `logs`.`date` DESC", $userid);
break;
case LOGPICS_FOR_MYHOME_GALLERY:
// all picture of one user, with the only exception of zombie pix hanging
// by an old log deletion (we should remove those ...)
$rs = sql("SELECT {$fields}, `logs`.`date` AS `picdate`\n\t FROM `pictures` AS `pics`\n {$join_logs}\n WHERE `object_type`=1 AND `logs`.`user_id`='&1' \n ORDER BY `logs`.`date` DESC", $login->userid);
break;
case LOGPICS_FOR_CACHE_STAT:
// all pictures for a cache except license-replacement pics
// need not to exclude invisible caches, as this is only displayed in listing view
$result = sql_value("SELECT COUNT(*)\n\t\t\t\t FROM `pictures` AS `pics`\n\t\t\t\t {$join_logs}\n\t\t\t\t {$join_user}\n WHERE `object_type`=1 AND `logs`.`cache_id`='&1'\n\t\t\t\t\t\t\t\t\t\t AND NOT (`data_license` IN ('&2','&3'))", 0, $cacheid, NEW_DATA_LICENSE_ACTIVELY_DECLINED, NEW_DATA_LICENSE_PASSIVELY_DECLINED);
break;
case LOGPICS_FOR_CACHE_GALLERY:
// all picture for a cache except license-replacement pics
// for all users except owner: also excluding invisble caches
$rs = sql("SELECT {$fields}, `user`.`username`, `logs`.`date` AS `picdate`\n\t FROM `pictures` AS `pics`\n\t {$join_logs} " . ($userid == $login->userid ? "" : "{$join_caches} {$join_cachestatus}") . "\n\t {$join_user}\n WHERE `object_type`=1 AND `logs`.`cache_id`='&1'\n\t\t\t\t\t\t\t\t\t\t AND NOT (`data_license` IN ('&2','&3'))\n ORDER BY `logs`.`date` DESC", $cacheid, NEW_DATA_LICENSE_ACTIVELY_DECLINED, NEW_DATA_LICENSE_PASSIVELY_DECLINED);
break;
default:
global $tpl;
$tpl->error(ERROR_INVALID_OPERATION);
return null;
}
if ($rs !== false) {
$result = sql_fetch_assoc_table($rs);
foreach ($result as &$logpic) {
$logpic['pic_url'] = use_current_protocol($logpic['pic_url']);
}
}
return $result;
}
function output_cachexml($wp)
{
global $opt, $login;
$rsCache = sql_slave("SELECT `caches`.`name`, `caches`.`wp_oc`, `caches`.`cache_id`, \r\n\t `caches`.`longitude`, `caches`.`latitude`, \r\n\t IF(`caches`.`status`=2, 1, 0) AS `tna`, \r\n\t IFNULL(`trans_status_text`.`text`, `cache_status`.`name`) AS `statustext`,\r\n\t IFNULL(`trans_type_text`.`text`, `cache_type`.`name`) AS `type_text`, `cache_type`.`id` AS `type_id`, \r\n\t IFNULL(`trans_size_text`.`text`, `cache_size`.`name`) AS `size`, \r\n\t `caches`.`difficulty`, `caches`.`terrain`, \r\n\t `caches`.`date_created`, \r\n\t IFNULL(`stat_caches`.`toprating`, 0) AS `toprating`, \r\n\t IF(`caches`.`user_id`='&1', 1, 0) AS `owner`, \r\n\t `user`.`username`, `user`.`user_id`\r\n\t FROM `caches` \r\n\t INNER JOIN `cache_type` ON `caches`.`type`=`cache_type`.`id` \r\n\t INNER JOIN `cache_status` ON `caches`.`status`=`cache_status`.`id` \r\n\t INNER JOIN `user` ON `caches`.`user_id`=`user`.`user_id` \r\n\t INNER JOIN `cache_size` ON `caches`.`size`=`cache_size`.`id` \r\n\t LEFT JOIN `stat_caches` ON `caches`.`cache_id`=`stat_caches`.`cache_id` \r\n\t LEFT JOIN `sys_trans` AS `trans_status` ON `cache_status`.`trans_id`=`trans_status`.`id` AND `cache_status`.`name`=`trans_status`.`text`\r\n\t LEFT JOIN `sys_trans_text` AS `trans_status_text` ON `trans_status`.`id`=`trans_status_text`.`trans_id` AND `trans_status_text`.`lang`='&2'\r\n\t LEFT JOIN `sys_trans` AS `trans_type` ON `cache_type`.`trans_id`=`trans_type`.`id` AND `cache_type`.`name`=`trans_type`.`text`\r\n\t LEFT JOIN `sys_trans_text` AS `trans_type_text` ON `trans_type`.`id`=`trans_type_text`.`trans_id` AND `trans_type_text`.`lang`='&2'\r\n\t LEFT JOIN `sys_trans` AS `trans_size` ON `cache_size`.`trans_id`=`trans_size`.`id` AND `cache_size`.`name`=`trans_size`.`text`\r\n\t LEFT JOIN `sys_trans_text` AS `trans_size_text` ON `trans_size`.`id`=`trans_size_text`.`trans_id` AND `trans_size_text`.`lang`='&2'\r\n\t WHERE `caches`.`wp_oc`='&3' AND \r\n \t\t\t\t\t\t\t\t\t `caches`.`status` IN (1, 2)", $login->userid, $opt['template']['locale'], $wp);
$rCache = sql_fetch_assoc($rsCache);
sql_free_result($rsCache);
if ($rCache === false) {
echo '<caches></caches>';
exit;
}
$nGeokretyCount = sql_value_slave("SELECT COUNT(*) FROM `gk_item_waypoint` WHERE `wp`='&1'", 0, $wp);
$nNotFoundCount = 0;
$nFoundCount = sql_value_slave("SELECT COUNT(*) FROM `cache_logs` WHERE `user_id`='&1' AND `cache_id`='&2' AND `type`=1", 0, $login->userid, $rCache['cache_id']);
if ($nFoundCount == 0) {
$nNotFoundCount = sql_value_slave("SELECT COUNT(*) FROM `cache_logs` WHERE `user_id`='&1' AND `cache_id`='&2' AND `type`=2", 0, $login->userid, $rCache['cache_id']);
}
echo '<caches>' . "\n";
echo ' <cache ';
echo 'name="' . xmlentities($rCache['name']) . '" ';
echo 'wpoc="' . xmlentities($rCache['wp_oc']) . '" ';
echo 'coords="' . $rCache['longitude'] . ',' . $rCache['latitude'] . '" ';
echo 'status_tna="' . xmlentities($rCache['tna']) . '" ';
echo 'status_text="' . xmlentities($rCache['statustext']) . '" ';
echo 'type_id="' . xmlentities($rCache['type_id']) . '" ';
echo 'type_text="' . xmlentities($rCache['type_text']) . '" ';
echo 'size="' . xmlentities($rCache['size']) . '" ';
echo 'difficulty="' . xmlentities($rCache['difficulty'] / 2) . '" ';
echo 'terrain="' . xmlentities($rCache['terrain'] / 2) . '" ';
echo 'listed_since="' . xmlentities(strftime($opt['locale'][$opt['template']['locale']]['format']['date'], strtotime($rCache['date_created']))) . '" ';
echo 'toprating="' . xmlentities($rCache['toprating']) . '" ';
echo 'geokreties="' . xmlentities($nGeokretyCount) . '" ';
echo 'found="' . xmlentities($nFoundCount > 0 ? 1 : 0) . '" ';
echo 'notfound="' . xmlentities($nNotFoundCount > 0 ? 1 : 0) . '" ';
echo 'owner="' . xmlentities($rCache['owner']) . '" ';
echo 'username="******" ';
echo 'userid="' . xmlentities($rCache['user_id']) . '" />' . "\n";
echo '</caches>';
exit;
}
$cachetype_condition = $cachetype ? " AND `caches`.`type` = " . sql_escape($cachetype) : "";
if ($bEvents) {
$cachetype_condition .= " AND `date_hidden` >= curdate()";
}
$date_field = $bEvents ? 'date_hidden' : 'date_created';
$sort_order = $bEvents ? 'ASC' : 'DESC';
$newCaches = array();
$rsNewCaches = sql_slave("SELECT `caches`.`cache_id` `cacheid`, `caches`.`wp_oc` `wpoc`,\n\t\t\t\t\t `caches`.`name` `cachename`, `caches`.`type`, `caches`.`country` `country`,\n\t\t\t\t\t `caches`.`{$date_field}` `date_created`,\n\t\t\t\t\t IFNULL(`sys_trans_text`.`text`,`countries`.`en`) AS `country_name`,\n\t\t\t\t\t `user`.`user_id` `userid`, `user`.`username` `username`,\n\t\t\t\t\t `ca`.`attrib_id` IS NOT NULL AS `oconly`\n\t\t\t\t\t FROM `caches`\n\t\t\tINNER JOIN `user` ON `caches`.`user_id`=`user`.`user_id`\n\t\t\t LEFT JOIN `countries` ON `countries`.`short` = `caches`.`country`\n\t\t\t LEFT JOIN `sys_trans_text` ON `sys_trans_text`.`trans_id` = `countries`.`trans_id`\n\t\t\t AND `sys_trans_text`.`lang` = '" . sql_escape($opt['template']['locale']) . "'\n\t\t\t LEFT JOIN `caches_attributes` `ca` ON `ca`.`cache_id`=`caches`.`cache_id` AND `ca`.`attrib_id`=6\n\t\t\t WHERE `caches`.`status` = 1" . ($country ? " AND `caches`.`country`='" . sql_escape($country) . "'" : "") . $cachetype_condition . "\n\t\t\t ORDER BY `caches`.`{$date_field}` {$sort_order}\n\t\t\t\t LIMIT " . ($startat + 0) . ', ' . ($perpage + 0));
// see also write_newcaches_urls() in sitemap.class.php
while ($rNewCache = sql_fetch_assoc($rsNewCaches)) {
$newCaches[] = $rNewCache;
}
sql_free_result($rsNewCaches);
$tpl->assign('newCaches', $newCaches);
$startat = isset($_REQUEST['startat']) ? $_REQUEST['startat'] + 0 : 0;
$cacheype_par = $cachetype ? "&cachetype={$cachetype}" : "";
if ($country == '') {
$count = sql_value_slave("SELECT COUNT(*) FROM `caches` WHERE `caches`.`status`=1" . $cachetype_condition, 0);
$pager = new pager("newcaches.php?startat={offset}" . $cacheype_par);
} else {
$count = sql_value_slave("SELECT COUNT(*) FROM `caches` WHERE `caches`.`status`=1 AND `caches`.`country`='&1'" . $cachetype_condition, 0, $country);
$pager = new pager("newcaches.php?country=" . $country . "&startat={offset}" . $cacheype_par);
}
$pager->make_from_offset($startat, $count, 100);
$tpl->assign('defaultcountry', $opt['template']['default']['country']);
$tpl->assign('countryCode', $country);
$tpl->assign('countryName', sql_value("SELECT IFNULL(`sys_trans_text`.`text`, `countries`.`name`) \n\t FROM `countries`\n\t LEFT JOIN `sys_trans` ON `countries`.`trans_id`=`sys_trans`.`id`\n\t LEFT JOIN `sys_trans_text` ON `sys_trans`.`id`=`sys_trans_text`.`trans_id` AND `sys_trans_text`.`lang`='&2'\n\t 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\t FROM `countries`\n\t LEFT JOIN `sys_trans` ON `countries`.`trans_id`=`sys_trans`.`id`\n\t LEFT JOIN `sys_trans_text` ON `sys_trans`.`id`=`sys_trans_text`.`trans_id` AND `sys_trans_text`.`lang`='&2'\n\t WHERE `countries`.`short`='&1'", '', $opt['page']['main_country'], $opt['template']['locale']));
$tpl->assign('events', $bEvents);
}
$tpl->display();
$sql .= '`caches`.`name` ASC';
}
}
//startat?
$startat = isset($_REQUEST['startat']) ? $_REQUEST['startat'] : 0;
if (!is_numeric($startat)) {
$startat = 0;
}
if (!is_numeric($caches_per_page)) {
$caches_per_page = 20;
}
$startat = floor($startat / $caches_per_page) * $caches_per_page;
$sql .= ' LIMIT ' . $startat . ', ' . $caches_per_page;
$nRowIndex = 0;
$rs_caches = sql_slave($sql, $sqldebug);
$resultcount = sql_value_slave('SELECT FOUND_ROWS()', 0);
tpl_set_var('results_count', $resultcount);
while ($caches_record = sql_fetch_array($rs_caches)) {
$tmpline = $cache_line;
list($iconname, $inactive) = getCacheIcon($usr['userid'], $caches_record['cache_id'], $caches_record['status'], $caches_record['user_id'], $caches_record['icon_large']);
$tmpline = mb_ereg_replace('{icon_large}', $iconname, $tmpline);
$tmpline = mb_ereg_replace('{cachetype}', htmlspecialchars(t($caches_record['cacheTypeName']), ENT_COMPAT, 'UTF-8'), $tmpline);
// short_desc ermitteln TODO: nicht die erste sondern die richtige wählen
$rsdesc = sql_slave("SELECT `short_desc` FROM `cache_desc` WHERE `cache_id`='&1' LIMIT 1", $caches_record['cache_id']);
$desc_record = sql_fetch_array($rsdesc);
mysql_free_result($rsdesc);
$tmpline = mb_ereg_replace('{short_desc}', htmlspecialchars($desc_record['short_desc'], ENT_COMPAT, 'UTF-8'), $tmpline);
$dDiff = abs(dateDiff('d', $caches_record['date_created'], date('Y-m-d')));
if ($dDiff < $caches_olddays) {
$tmpline = mb_ereg_replace('{new}', $caches_newstring, $tmpline);
} else {
}
if ($options['country'] != '') {
$sql_where[] = '`caches`.`country`=\'' . sql_escape($options['country']) . '\'';
}
if ($options['cachetype'] != '') {
$types = explode(';', $options['cachetype']);
if (count($types) < sql_value_slave("SELECT COUNT(*) FROM `cache_type`", 0)) {
for ($i = 0; $i < count($types); $i++) {
$types[$i] = "'" . sql_escape($types[$i]) . "'";
}
$sql_where[] = '`caches`.`type` IN (' . implode(',', $types) . ')';
}
}
if ($options['cachesize'] != '') {
$sizes = explode(';', $options['cachesize']);
if (count($sizes) < sql_value_slave("SELECT COUNT(*) FROM `cache_size`", 0)) {
for ($i = 0; $i < count($sizes); $i++) {
$sizes[$i] = "'" . sql_escape($sizes[$i]) . "'";
}
$sql_where[] = '`caches`.`size` IN (' . implode(',', $sizes) . ')';
}
}
if ($options['difficultymin'] != 0) {
$sql_where[] = '`caches`.`difficulty`>=\'' . sql_escape($options['difficultymin']) . '\'';
}
if ($options['difficultymax'] != 0) {
$sql_where[] = '`caches`.`difficulty`<=\'' . sql_escape($options['difficultymax']) . '\'';
}
if ($options['terrainmin'] != 0) {
$sql_where[] = '`caches`.`terrain`>=\'' . sql_escape($options['terrainmin']) . '\'';
}
$tpl->assign('extern_news', true);
}
// forum entries
if (file_exists($opt['rootpath'] . 'cache2/phpbb.inc.php')) {
require_once $opt['rootpath'] . 'cache2/phpbb.inc.php';
} else {
$phpbb_topics = array();
}
$tpl->assign('phpbb_topics', $phpbb_topics);
$tpl->assign('phpbb_enabled', $opt['cron']['phpbbtopics']['url'] != '');
$tpl->assign('phpbb_name', $opt['cron']['phpbbtopics']['name']);
$tpl->assign('phpbb_link', $opt['cron']['phpbbtopics']['link']);
// current cache and log-counters
$tpl->assign('count_hiddens', sql_value_slave('SELECT COUNT(*) AS `hiddens` FROM `caches` WHERE `status`=1', 0));
$tpl->assign('count_founds', sql_value_slave('SELECT COUNT(*) AS `founds` FROM `cache_logs` WHERE `type`=1', 0));
$tpl->assign('count_users', sql_value_slave('SELECT COUNT(*) AS `users` FROM (SELECT DISTINCT `user_id` FROM `cache_logs` UNION DISTINCT SELECT DISTINCT `user_id` FROM `caches`) AS `t`', 0));
// new events
$events = array();
$rs = sql_slave("SELECT `user`.`user_id` `user_id`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`user`.`username` `username`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`cache_id` `cache_id`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`name` `name`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`longitude` `longitude`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`latitude` `latitude`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`date_created` `date_created`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`country` `country`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`difficulty` `difficulty`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`terrain` `terrain`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`date_hidden`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`cache_location`.`adm1`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`cache_location`.`adm2`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`cache_location`.`adm3`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`cache_location`.`adm4`\r\n\t\t\t\t\t\t\t\t\t\t\t FROM `caches`\r\n\t\t\t\t\t\t\t\t INNER JOIN `cache_status` ON `caches`.`status`=`cache_status`.`id`\r\n\t\t\t\t\t\t\t\t INNER JOIN `user` ON `user`.`user_id`=`caches`.`user_id`\r\n\t\t\t\t\t\t\t\t\tLEFT JOIN `cache_location` ON `caches`.`cache_id`=`cache_location`.`cache_id`\r\n\t\t\t\t\t\t\t\t\t\t\tWHERE `caches`.`country`='&1' AND \r\n\t\t\t\t\t\t\t\t\t\t\t `caches`.`date_hidden` >= curdate() AND \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`type` = 6 AND \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`cache_status`.`allow_user_view`=1\r\n\t\t\t\t\t\t\t\t\t ORDER BY `date_hidden` ASC LIMIT 0, 10", $sUserCountry);
$tpl->assign_rs('events', $rs);
sql_free_result($rs);
// new caches
$rs = sql_slave("SELECT\t`user`.`user_id` `user_id`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`user`.`username` `username`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`cache_id` `cache_id`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`name` `name`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`longitude` `longitude`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`latitude` `latitude`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`date_created` `date_created`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`country` `country`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`difficulty` `difficulty`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`terrain` `terrain`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`date_hidden`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`type`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`cache_location`.`adm1`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`cache_location`.`adm2`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`cache_location`.`adm3`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`cache_location`.`adm4`\r\n\t\t\t\t\t\t\t\t\t\t\t FROM `caches` \r\n\t\t\t\t\t\t\t\t INNER JOIN `user` ON `user`.`user_id`=`caches`.`user_id` \r\n\t\t\t\t\t\t\t\t\tLEFT JOIN `cache_location` ON `caches`.`cache_id`=`cache_location`.`cache_id`\r\n\t\t\t\t\t\t\t\t\t\t\tWHERE `caches`.`country`='&1' AND \r\n\t\t\t\t\t\t\t\t\t\t\t `caches`.`type` != 6 AND \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`status` = 1\r\n\t\t\t\t\t\t\t\t\t ORDER BY `caches`.`date_created` DESC LIMIT 0, 10", $sUserCountry);
$tpl->assign_rs('newcaches', $rs);
sql_free_result($rs);
$rs = sql_slave("SELECT COUNT(`cache_logs`.`cache_id`) AS `cRatings`, \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t`cache_logs`.`cache_id`, \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tMAX(`cache_logs`.`date`) AS `dLastLog`, \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t`user`.`user_id` AS `user_id`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t`user`.`username` AS `username`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`cache_id` AS `cache_id`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`name` AS `name`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`longitude` AS `longitude`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`latitude` AS `latitude`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`date_created` AS `date_created`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`country` AS `country`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`difficulty` AS `difficulty`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`terrain` AS `terrain`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`date_hidden`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`type`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t`cache_location`.`adm1`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t`cache_location`.`adm2`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t`cache_location`.`adm3`,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t`cache_location`.`adm4`\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t FROM `cache_logs` \r\n\t\t\t\t\t\t\t\t\t\t\t INNER JOIN `cache_rating` ON `cache_logs`.`cache_id`=`cache_rating`.`cache_id` AND \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t`cache_logs`.`user_id`=`cache_rating`.`user_id` \r\n\t\t\t\t\t\t\t\t\t\t\t INNER JOIN `caches` ON `cache_logs`.`cache_id`=`caches`.`cache_id`\r\n\t\t\t\t\t\t\t\t\t\t\t INNER JOIN `user` ON `user`.`user_id`=`caches`.`user_id`\r\n\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `cache_location` ON `caches`.`cache_id`=`cache_location`.`cache_id`\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE `caches`.`country`='&1' AND \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t `cache_logs`.`type`=1 AND \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t`cache_logs`.`date`>DATE_SUB(NOW(), INTERVAL 30 DAY) AND \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`type`!=6 AND \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t`caches`.`status`=1\r\n\t\t\t\t\t\t\t\t\t\t\t\t GROUP BY `cache_logs`.`cache_id` \r\n\t\t\t\t\t\t\t\t\t\t\t\t ORDER BY `cRatings` DESC, \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t`dLastLog` DESC \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tLIMIT 0, 10", $sUserCountry);
$tpl->assign_rs('topratings', $rs);
sql_free_result($rs);
$sUserCountryName = sql_value("SELECT IFNULL(`sys_trans_text`.`text`, `countries`.`name`) \r\n\t\t FROM `countries` \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`='&2'\r\n\t\t WHERE `countries`.`short`='&1'", '', $sUserCountry, $opt['template']['locale']);
$tpl->assign('usercountry', $sUserCountryName);
}
function output_searchresult($nResultId, $nLon1, $nLat1, $nLon2, $nLat2)
{
global $opt;
// check if data is available and connect the right slave server
$nSlaveId = sql_value("SELECT `slave_id` FROM `map2_result` WHERE `result_id`='&1' AND DATE_ADD(`date_created`, INTERVAL '&2' SECOND)>NOW()", -2, $nResultId, $opt['map']['maxcacheage']);
if ($nSlaveId == -2) {
echo '<searchresult count="0" available="0">';
echo '</searchresult>';
exit;
}
sql_connect_slave($nSlaveId);
sql("UPDATE `map2_result` SET `request_counter`=`request_counter`+1, `date_lastqueried`=NOW() WHERE `result_id`='&1'", $nResultId);
// execute query and return search result
$nRecordCount = sql_value_slave("SELECT COUNT(*) FROM `map2_data` INNER JOIN `caches` ON `map2_data`.`cache_id`=`caches`.`cache_id` WHERE `map2_data`.`result_id`='&1' AND `caches`.`longitude`>'&2' AND `caches`.`longitude`<'&3' AND `caches`.`latitude`>'&4' AND `caches`.`latitude`<'&5'", 0, $nResultId, $nLon1, $nLon2, $nLat1, $nLat2);
// TODO: SQL_CALC_FOUND_ROWS + $nRecordCount = sql_value_slave("SELECT FOUND_ROWS()", 0);
$bMaxRecordReached = false;
if ($nRecordCount > $opt['map']['maxrecords']) {
$bMaxRecordReached = true;
}
echo '<searchresult count="' . $nRecordCount . '" available="1" maxrecordreached="' . ($bMaxRecordReached ? '1' : '0') . '">' . "\n";
if ($bMaxRecordReached == false) {
$rs = sql_slave("SELECT SQL_BUFFER_RESULT `caches`.`wp_oc`, `caches`.`longitude`, `caches`.`latitude`, `caches`.`type` FROM `map2_data` INNER JOIN `caches` ON `map2_data`.`cache_id`=`caches`.`cache_id` WHERE `map2_data`.`result_id`='&1' AND `caches`.`longitude`>'&2' AND `caches`.`longitude`<'&3' AND `caches`.`latitude`>'&4' AND `caches`.`latitude`<'&5' LIMIT " . ($opt['map']['maxrecords'] + 0), $nResultId, $nLon1, $nLon2, $nLat1, $nLat2);
while ($r = sql_fetch_assoc($rs)) {
echo '<cache wp="' . xmlentities($r['wp_oc']) . '" lon="' . xmlentities($r['longitude']) . '" lat="' . xmlentities($r['latitude']) . '" type="' . xmlentities($r['type']) . '" />' . "\n";
}
sql_free_result($rs);
}
echo '</searchresult>';
exit;
}
$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");
// Alle Caches für diese Gruppe finden
if ($adm3Group['name'] == null) {
sql_slave("INSERT INTO &topLocationCaches (`cache_id`) SELECT `caches`.`cache_id` FROM `cache_location` INNER JOIN `caches` ON `caches`.`cache_id`=`cache_location`.`cache_id` LEFT JOIN `stat_caches` ON `caches`.`cache_id`=`stat_caches`.`cache_id` WHERE IFNULL(`stat_caches`.`toprating`,0)>0 AND `cache_location`.`adm1`='&1' AND ISNULL(`cache_location`.`adm3`) AND `caches`.`status`=1", $adm1Group['name']);
} else {
sql_slave("INSERT INTO &topLocationCaches (`cache_id`) SELECT `caches`.`cache_id` FROM `cache_location` INNER JOIN `caches` ON `caches`.`cache_id`=`cache_location`.`cache_id` LEFT JOIN `stat_caches` ON `caches`.`cache_id`=`stat_caches`.`cache_id` WHERE IFNULL(`stat_caches`.`toprating`,0)>0 AND `cache_location`.`adm1`='&1' AND `cache_location`.`adm3`='&2' AND `caches`.`status`=1", $adm1Group['name'], $adm3Group['name']);
}
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);
function search_output()
{
global $opt, $tpl, $login;
global $enable_mapdisplay;
global $called_by_search, $called_by_profile_query, $options, $lat_rad, $lon_rad, $distance_unit;
global $startat, $caches_per_page, $sql, $query_userid, $query_name;
$tpl->name = 'search.result.caches';
$tpl->menuitem = MNU_CACHES_SEARCH_RESULT;
$startat = floor($startat / $caches_per_page) * $caches_per_page;
$sql .= ' LIMIT ' . $startat . ', ' . $caches_per_page;
// run SQL query
sql_enable_foundrows();
$rs_caches = sql_slave("SELECT SQL_BUFFER_RESULT SQL_CALC_FOUND_ROWS " . $sql);
$resultcount = sql_value_slave('SELECT FOUND_ROWS()', 0);
sql_foundrows_done();
$tpl->assign('results_count', $resultcount);
$tpl->assign('startat', $startat);
$caches = array();
while ($rCache = sql_fetch_array($rs_caches)) {
// select best-fitting short desc for active language
$rCache['short_desc'] = sql_value_slave("\n SELECT `short_desc`\n FROM `cache_desc`\n WHERE `cache_id`='&1'\n AND `language`='&2'", false, $rCache['cache_id'], $opt['template']['locale']);
if ($rCache['short_desc'] === false) {
$rCache['short_desc'] = sql_value_slave("\n\t\t\t\tSELECT `short_desc`\n\t\t\t\tFROM `cache_desc`\n\t\t\t\tWHERE `cache_id`='&1'\n\t\t\t\tAND `language`='EN'", false, $rCache['cache_id']);
}
if ($rCache['short_desc'] === false) {
$rCache['short_desc'] = sql_value_slave("\n\t\t\t\tSELECT `short_desc`\n\t\t\t\tFROM `cache_desc`\n\t\t\t\tWHERE `cache_id`='&1'\n\t\t\t\tORDER BY `date_created`\n\t\t\t\tLIMIT 1", '', $rCache['cache_id']);
}
$rCache['desclangs'] = mb_split(',', $rCache['desc_languages']);
// decide if the cache is new
$dDiff = dateDiff('d', $rCache['date_created'], date('Y-m-d'));
$rCache['isnew'] = $dDiff <= NEWCACHES_DAYS;
// get last logs
if ($options['sort'] != 'bymylastlog' || !$login->logged_in()) {
$ownlogs = "";
} else {
$ownlogs = " AND `cache_logs`.`user_id`='" . sql_escape($login->userid) . "'";
}
$sql = "\n\t\t\t\tSELECT `cache_logs`.`id`, `cache_logs`.`type`, `cache_logs`.`date`, `log_types`.`icon_small`\n\t\t\t\tFROM `cache_logs`, `log_types`\n\t\t\t\tWHERE `cache_logs`.`cache_id`='" . sql_escape($rCache['cache_id']) . "'\n\t\t\t\t AND `log_types`.`id`=`cache_logs`.`type`" . $ownlogs . "\n\t\t\t\tORDER BY `cache_logs`.`order_date` DESC, `cache_logs`.`date_created` DESC, `cache_logs`.`id` DESC\n\t\t\t\tLIMIT 6";
$rs = sql_slave($sql);
$rCache['logs'] = sql_fetch_assoc_table($rs);
$rCache['firstlog'] = array_shift($rCache['logs']);
// get direction from search coordinate
if ($rCache['distance'] > 0) {
$direction = geomath::calcBearing($lat_rad / 3.14159 * 180, $lon_rad / 3.14159 * 180, $rCache['latitude'], $rCache['longitude']);
$rCache['direction_deg'] = round($direction / 22.5) * 22.5;
$rCache['direction_txt'] = geomath::Bearing2Text($direction, 0, $opt['template']['locale']);
} else {
$rCache['direction_deg'] = false;
}
// other data
$rCache['icon'] = getCacheIcon($login->userid, $rCache['cache_id'], $rCache['status'], $rCache['user_id'], $rCache['icon_large']);
$rCache['redname'] = $rCache['status'] == 5 || $rCache['status'] == 7;
$caches[] = $rCache;
}
mysql_free_result($rs_caches);
$tpl->assign('caches', $caches);
$page = 'search.php?queryid=' . $options['queryid'] . '&startat={offset}&sortby=' . $options['sort'];
if (isset($options['sortorder']) && $options['sortorder']) {
$page .= "&sortorder=" . $options['sortorder'];
}
if (isset($options['creationdate']) && $options['creationdate']) {
$page .= "&creationdate=" . $options['creationdate'];
}
$pager = new pager($page, 2, 9);
$pager->make_from_offset($startat, $resultcount, $caches_per_page);
// downloads
$tpl->assign('queryid', $options['queryid']);
if (isset($query_userid)) {
$tpl->assign('query_name', $query_name);
}
$tpl->assign('startatp1', min($resultcount, $startat + 1));
if ($resultcount - $startat < 500) {
$tpl->assign('endat', $startat + $resultcount - $startat);
} else {
$tpl->assign('endat', $startat + 500);
}
// kompatibilität!
if ($distance_unit == 'sm') {
$tpl->assign('distanceunit', 'mi');
} elseif ($distance_unit == 'nm') {
$tpl->assign('distanceunit', 'sm');
} else {
$tpl->assign('distanceunit', $distance_unit);
}
$tpl->assign('displayownlogs', $options['sort'] == 'bymylastlog');
$tpl->assign('search_headline_caches', $called_by_search);
$tpl->assign('enable_mapdisplay', $enable_mapdisplay);
// sort results by
$tpl->assign('sortby', $options['sort']);
if (isset($options['sortorder'])) {
$tpl->assign('sortorder', $options['sortorder']);
}
if ($options['sort'] == 'bycreated' || isset($options['creationdate'])) {
$tpl->assign('creationdate', true);
}
// cachelist data
if (isset($options['cachelist'])) {
$tpl->assign('cachelist', $options['cachelist']);
$tpl->assign('cachelist_pw', $options['cachelist_pw']);
} else {
$tpl->assign('cachelist', false);
}
// disable "edit options" for internally generated searches
if ($options['searchtype'] == 'bylist') {
$tpl->assign('disable_edit_options', true);
}
$tpl->display();
}
$tpl->assign('phpbb_enabled', false);
$tpl->assign('forum', '');
}
$phpbb_topics = array();
$tpl->assign('phpbb_topics', $phpbb_topics);
// $tpl->assign('phpbb_enabled', ($opt['cron']['phpbbtopics']['url'] != ''));
$tpl->assign('phpbb_name', $opt['cron']['phpbbtopics']['name']);
$tpl->assign('phpbb_link', $opt['cron']['phpbbtopics']['link']);
// current cache and log-counters
$tpl->assign('count_hiddens', number1000(sql_value_slave('SELECT COUNT(*) AS `hiddens` FROM `caches` WHERE `status`=1', 0)));
$tpl->assign('count_founds', number1000(sql_value_slave('SELECT COUNT(*) AS `founds` FROM `cache_logs` WHERE `type`=1', 0)));
$tpl->assign('count_users', number1000(sql_value_slave('SELECT COUNT(*) AS `users` FROM (SELECT DISTINCT `user_id` FROM `cache_logs` UNION DISTINCT SELECT DISTINCT `user_id` FROM `caches`) AS `t`', 0)));
// get newest events
$tpl->assign_rs('events', $getNew->rsForSmarty('event'));
// get total event count for all countries
$tpl->assign('total_events', sql_value_slave("SELECT COUNT(*) FROM `caches` WHERE `type`=6 AND `date_hidden` >= curdate() AND `status`=1", 0));
// get newest caches
$tpl->assign_rs('newcaches', $getNew->rsForSmarty('cache'));
// enable minimap for new caches if url is set
if ($opt['logic']['minimapurl'] != '') {
// get the correct api key for google maps
$gmkey = '';
$sHost = strtolower($_SERVER['HTTP_HOST']);
if (isset($opt['lib']['google']['mapkey'][$sHost])) {
$gmkey = $opt['lib']['google']['mapkey'][$sHost];
}
// build static maps url by inserting api key
$url = $opt['page']['protocol'] . strstr($opt['logic']['minimapurl'], '://');
$url = mb_ereg_replace('{gmkey}', $gmkey, $url);
// put into template
$tpl->assign('minimap_url', $url);
if ($startat > 10 * $perpage) {
$tpl->cache_lifetime = 3600;
} else {
$tpl->cache_lifetime = 300;
}
if (!$tpl->is_cached()) {
require $opt['rootpath'] . 'lib2/logic/cacheIcon.inc.php';
$newCaches = array();
$rsNewCaches = sql_slave('SELECT `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` FROM `caches` INNER JOIN `user` ON `caches`.`user_id`=`user`.`user_id` INNER JOIN `cache_type` ON `caches`.`type`=`cache_type`.`id` INNER JOIN `cache_status` ON `caches`.`status`=`cache_status`.`id` WHERE `cache_status`.`allow_user_view`=1 ORDER BY `caches`.`date_created` DESC LIMIT ' . ($startat + 0) . ', ' . ($perpage + 0));
while ($rNewCache = sql_fetch_assoc($rsNewCaches)) {
$rNewCache['icon_large'] = getSmallCacheIcon($rNewCache['icon_large']);
$newCaches[] = $rNewCache;
}
sql_free_result($rsNewCaches);
$tpl->assign('newCaches', $newCaches);
$count = sql_value_slave('SELECT COUNT(*) `count` FROM `caches`INNER JOIN `cache_status` ON `caches`.`status`=`cache_status`.`id` WHERE `cache_status`.`allow_user_view`=1', 0);
$maxstart = (ceil($count / $perpage) - 1) * $perpage;
if ($startat < 4 * $perpage) {
$firstpage = 0;
$lastpage = 8 * $perpage;
} else {
$firstpage = $startat - 4 * $perpage;
$lastpage = $firstpage + 8 * $perpage;
}
if ($lastpage > $maxstart) {
$lastpage = $maxstart;
}
$tpl->assign('firstpage', $firstpage);
$tpl->assign('lastpage', $lastpage);
$tpl->assign('perpage', $perpage);
$tpl->assign('startat', $startat);
