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