function search_output() { global $sqldebug; /* cacheid name latitude longitude type size difficulty terrain username waypoint */ $sql = ' SELECT &searchtmp.`cache_id` `cacheid`, &searchtmp.`longitude`, &searchtmp.`latitude`, `caches`.`name`, `caches`.`wp_oc`, `caches`.`terrain`, `caches`.`difficulty`, `cache_type`.`short` `typedesc`, `cache_size`.`name` `sizedesc`, `user`.`username` FROM &searchtmp, `caches`, `cache_type`, `cache_size`, `user` WHERE &searchtmp.`cache_id`=`caches`.`cache_id` AND &searchtmp.`type`=`cache_type`.`id` AND &searchtmp.`size`=`cache_size`.`id` AND &searchtmp.`user_id`=`user`.`user_id`'; $rs = sql_slave($sql, $sqldebug); while ($r = sql_fetch_array($rs)) { $lat = sprintf('%07d', $r['latitude'] * 100000); $lon = sprintf('%07d', $r['longitude'] * 100000); $name = convert_string($r['name']); $username = convert_string($r['username']); $type = convert_string($r['typedesc']); $size = convert_string($r['sizedesc']); $difficulty = sprintf('%01.1f', $r['difficulty'] / 2); $terrain = sprintf('%01.1f', $r['terrain'] / 2); $cacheid = convert_string($r['wp_oc']); $line = "{$name} by {$username}, {$type}, {$size}, {$cacheid}"; $record = pack("CLllA*x", 2, 1 + 4 + 4 + 4 + strlen($line) + 1, (int) $lon, (int) $lat, $line); append_output($record); } mysql_free_result($rs); }
function search_output() { $ovlLine = "[Symbol {symbolnr1}]\r\nTyp=6\r\nGroup=1\r\nWidth=20\r\nHeight=20\r\nDir=100\r\nArt=1\r\nCol=3\r\nZoom=1\r\nSize=103\r\nArea=2\r\nXKoord={lon}\r\nYKoord={lat}\r\n[Symbol {symbolnr2}]\r\nTyp=2\r\nGroup=1\r\nCol=3\r\nArea=1\r\nZoom=1\r\nSize=130\r\nFont=1\r\nDir=100\r\nXKoord={lonname}\r\nYKoord={latname}\r\nText={cachename}\r\n"; $ovlFoot = "[Overlay]\r\nSymbols={symbolscount}\r\n"; /* {symbolnr1} {lon} {lat} {symbolnr2} {lonname} {latname} {cachename} {symbolscount} */ $nr = 1; $rs = sql_slave(' SELECT SQL_BUFFER_RESULT &searchtmp.`cache_id` `cacheid`, &searchtmp.`longitude`, &searchtmp.`latitude`, `caches`.`name` FROM &searchtmp, `caches` WHERE &searchtmp.`cache_id`=`caches`.`cache_id`'); while ($r = sql_fetch_array($rs)) { $thisline = $ovlLine; $lat = sprintf('%01.5f', $r['latitude']); $thisline = mb_ereg_replace('{lat}', $lat, $thisline); $thisline = mb_ereg_replace('{latname}', $lat, $thisline); $lon = sprintf('%01.5f', $r['longitude']); $thisline = mb_ereg_replace('{lon}', $lon, $thisline); $thisline = mb_ereg_replace('{lonname}', $lon, $thisline); $thisline = mb_ereg_replace('{cachename}', utf8ToIso88591($r['name']), $thisline); $thisline = mb_ereg_replace('{symbolnr1}', $nr, $thisline); $thisline = mb_ereg_replace('{symbolnr2}', $nr + 1, $thisline); append_output($thisline); $nr += 2; } mysql_free_result($rs); $ovlFoot = mb_ereg_replace('{symbolscount}', $nr - 1, $ovlFoot); append_output($ovlFoot); }
// clear if not oconly if ($caches_record['oconly'] != 1) { $tmp_caches_oconlystring = ''; } // clear if oconly attribute is hidden if ($oconlyhidden == 1) { $tmp_caches_oconlystring = ''; } $tmpline = mb_ereg_replace('{oconly}', $tmp_caches_oconlystring, $tmpline); // das letzte found suchen $sql = 'SELECT `cache_logs`.`id` `id`, `cache_logs`.`type` `type`, `cache_logs`.`date` `date`, `log_types`.`icon_small` `icon_small` FROM `cache_logs`, `log_types` WHERE `cache_logs`.`cache_id`=\'' . sql_escape($caches_record['cache_id']) . '\' AND `log_types`.`id`=`cache_logs`.`type` ORDER BY `cache_logs`.`date` DESC LIMIT 6'; $result = sql_slave($sql); if ($row = sql_fetch_array($result)) { $tmpline = mb_ereg_replace('{logimage1}', icon_log_type($row['icon_small'], "") . '<a href=\'viewlogs.php?cacheid=' . htmlspecialchars($caches_record['cache_id'], ENT_COMPAT, 'UTF-8') . '#' . htmlspecialchars($row['id'], ENT_COMPAT, 'UTF-8') . '\'>{gray_s}' . date($logdateformat, strtotime($row['date'])) . '{gray_e}</a>', $tmpline); $tmpline = mb_ereg_replace('{logdate1}', "", $tmpline); } else { $tmpline = mb_ereg_replace('{logimage1}', "<img src='images/trans.gif' border='0' width='16' height='16' />", $tmpline); $tmpline = mb_ereg_replace('{logdate1}', "--.--.----", $tmpline); } $lastlogs = ""; while ($row = sql_fetch_array($result)) { $lastlogs .= '<a href=\'viewlogs.php?cacheid=' . urlencode($caches_record['cache_id']) . '#' . htmlspecialchars($row['id'], ENT_COMPAT, 'UTF-8') . '\'>' . icon_log_type($row['icon_small'], '') . '</a> '; } $tmpline = mb_ereg_replace('{lastlogs}', $lastlogs, $tmpline); // und jetzt noch die Richtung ... if ($caches_record['distance'] > 0) { $tmpline = mb_ereg_replace('{direction}', Bearing2Text(calcBearing($lat_rad / 3.14159 * 180, $lon_rad / 3.14159 * 180, $caches_record['latitude'], $caches_record['longitude']), 1), $tmpline);
function sql_value_slave($sql, $default) { $rs = sql_slave($sql); if ($r = sql_fetch_row($rs)) { if ($r[0] == null) { return $default; } else { return $r[0]; } } else { return $default; } }
function search_output() { global $opt; global $state_temporarily_na, $state_archived, $state_locked; $server_domain = $opt['page']['domain']; $server_address = $opt['page']['default_absolute_url']; $locHead = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><loc version="1.0" src="' . $server_domain . '">' . "\n"; $locLine = ' <waypoint> <name id="{waypoint}"><![CDATA[{archivedflag}{name} by {username}]]></name> <coord lat="{lat}" lon="{lon}"/> <type>Geocache</type> <link text="Beschreibung">' . $server_address . 'viewcache.php?cacheid={cacheid}</link> </waypoint> '; $locFoot = '</loc>'; append_output($locHead); /* {waypoint} status -> {archivedflag} {name} {username} {lon} {lat} {cacheid} */ $rs = sql_slave(' SELECT SQL_BUFFER_RESULT &searchtmp.`cache_id` `cacheid`, &searchtmp.`longitude`, &searchtmp.`latitude`, `caches`.`name`, `caches`.`status`, `caches`.`wp_oc` `waypoint`, `user`.`username` `username` FROM &searchtmp, `caches`, `user` WHERE &searchtmp.`cache_id`=`caches`.`cache_id` AND &searchtmp.`user_id`=`user`.`user_id`'); while ($r = sql_fetch_array($rs)) { $thisline = $locLine; $lat = sprintf('%01.5f', $r['latitude']); $thisline = mb_ereg_replace('{lat}', $lat, $thisline); $lon = sprintf('%01.5f', $r['longitude']); $thisline = mb_ereg_replace('{lon}', $lon, $thisline); $thisline = mb_ereg_replace('{waypoint}', $r['waypoint'], $thisline); $thisline = mb_ereg_replace('{name}', $r['name'], $thisline); if ($r['status'] == 2 || $r['status'] == 3 || $r['status'] == 6) { if ($r['status'] == 2) { $thisline = mb_ereg_replace('{archivedflag}', $state_temporarily_na . '!, ', $thisline); } elseif ($r['status'] == 3) { $thisline = mb_ereg_replace('{archivedflag}', $state_archived . '!, ', $thisline); } else { $thisline = mb_ereg_replace('{archivedflag}', $state_locked . '!, ', $thisline); } } else { $thisline = mb_ereg_replace('{archivedflag}', '', $thisline); } $thisline = mb_ereg_replace('{username}', $r['username'], $thisline); $thisline = mb_ereg_replace('{cacheid}', $r['cacheid'], $thisline); append_output($thisline); } mysql_free_result($rs); append_output($locFoot); }
if ($rMapCache = sql_fetch_assoc($rsMapCache)) { $resultId = $rMapCache['result_id']; sql("UPDATE `map2_result` SET `shared_counter`=`shared_counter`+1 WHERE `result_id`='" . ($resultId + 0) . "'"); } else { // ensure that query is performed without errors before reserving the result_id sql_temp_table_slave('tmpmapresult'); sql_slave("CREATE TEMPORARY TABLE &tmpmapresult (`cache_id` INT UNSIGNED NOT NULL, PRIMARY KEY (`cache_id`)) ENGINE=MEMORY"); sql_slave("INSERT INTO &tmpmapresult (`cache_id`) " . $sqlFilter); sql("INSERT INTO `map2_result` (`slave_id`, `sqlchecksum`, `sqlquery`, `date_created`, `date_lastqueried`) VALUES ('&1', '&2', '&3', NOW(), NOW())", $db['slave_id'], $sqlchecksum, $cachesFilter . "\n" . $sqlFilter); $resultId = sql_insert_id(); sql_slave("INSERT IGNORE INTO `map2_data` (`result_id`, `cache_id`) SELECT '&1', `cache_id` FROM &tmpmapresult", $resultId); sql_drop_temp_table_slave('tmpmapresult'); } sql_free_result($rsMapCache); if ($map2_bounds) { $rs = sql_slave("SELECT MIN(`latitude`) AS `lat_min`,\n\t\t MAX(`latitude`) AS `lat_max`,\n\t\t MIN(`longitude`) AS `lon_min`,\n\t\t MAX(`longitude`) AS `lon_max`\n FROM `map2_data`, `caches`\n\t WHERE `result_id`='&1'\n\t AND `caches`.`cache_id`=`map2_data`.`cache_id`", $resultId); if (($rBounds = sql_fetch_assoc($rs)) && $rBounds['lat_min'] !== null) { if ($rBounds['lat_min'] == $rBounds['lat_max'] && $rBounds['lon_min'] == $rBounds['lon_max']) { $halfwin = 0.02; $rBounds['lat_min'] -= $halfwin; $rBounds['lat_max'] += $halfwin; $rBounds['lon_min'] -= $halfwin; $rBounds['lon_max'] += $halfwin; } $bounds_param = "&lat_min=" . round($rBounds['lat_min'], 5) . "&lat_max=" . round($rBounds['lat_max'], 5) . '&lon_min=' . round($rBounds['lon_min'], 5) . '&lon_max=' . round($rBounds['lon_max'], 5); } sql_free_result($rs); $tpl->redirect('map2.php?queryid=' . $options['queryid'] . '&resultid=' . $resultId . $bounds_param); } else { echo $resultId; }
function search_output() { global $opt, $translate, $txt_record; global $converted_from_html; global $phpzip, $bUseZip; $txtLine = $txt_record; $txtLogs = "<===================>\n{username} / {date} / {type}\n\n{text}\n"; $rs = sql_slave("SELECT SQL_BUFFER_RESULT\n &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`.`terrain` `terrain`,\n `caches`.`difficulty` `difficulty`,\n `caches`.`desc_languages` `desc_languages`,\n `caches`.`needs_maintenance`,\n `caches`.`listing_outdated`,\n IFNULL(`stt_country`.`text`,`countries`.`en`) AS `country`,\n IFNULL(`stt_size`.`text`,`cache_size`.`name`) `size`,\n IFNULL(`stt_type`.`text`,`cache_type`.`en`) `type`,\n IFNULL(`stt_status`.`text`,`cache_status`.`name`) `status`,\n `user`.`username` `username`,\n `cache_desc`.`desc` `desc`,\n `cache_desc`.`short_desc` `short_desc`,\n `cache_desc`.`language` `desc_language`,\n `cache_desc`.`hint` `hint`,\n `cache_desc`.`desc_html` `html`,\n `user`.`user_id`,\n `user`.`username`,\n `user`.`data_license`\n FROM\n &searchtmp\n INNER JOIN `caches` ON &searchtmp.`cache_id`=`caches`.`cache_id`\n INNER JOIN `cache_desc`\n ON `cache_desc`.`cache_id`=`caches`.`cache_id`\n AND `caches`.`default_desclang`=`cache_desc`.`language`\n INNER JOIN `cache_type` ON `cache_type`.`id`=`caches`.`type`\n INNER JOIN `cache_size` ON `cache_size`.`id`=`caches`.`size`\n INNER JOIN `cache_status` ON `cache_status`.`id`=`caches`.`status`\n INNER JOIN `user` ON `user`.`user_id`=`caches`.`user_id`\n LEFT JOIN `countries` ON `countries`.`short`=`caches`.`country`\n LEFT JOIN `sys_trans_text` `stt_type` ON `stt_type`.`trans_id`=`cache_type`.`trans_id` AND `stt_type`.`lang`='&1'\n LEFT JOIN `sys_trans_text` `stt_size` ON `stt_size`.`trans_id`=`cache_size`.`trans_id` AND `stt_size`.`lang`='&1'\n LEFT JOIN `sys_trans_text` `stt_status` ON `stt_status`.`trans_id`=`cache_status`.`trans_id` AND `stt_status`.`lang`='&1'\n LEFT JOIN `sys_trans_text` `stt_country` ON `stt_country`.`trans_id`=`countries`.`trans_id` AND `stt_country`.`lang`='&1'", $opt['template']['locale']); while ($r = sql_fetch_array($rs)) { if (strlen($r['desc_languages']) > 2) { $r = get_locale_desc($r); } $thisline = $txtLine; $lat = sprintf('%01.5f', $r['latitude']); $thisline = mb_ereg_replace('{lat}', help_latToDegreeStr($lat), $thisline); $lon = sprintf('%01.5f', $r['longitude']); $thisline = mb_ereg_replace('{lon}', help_lonToDegreeStr($lon), $thisline); $time = date('d.m.Y', strtotime($r['date_hidden'])); $thisline = mb_ereg_replace('{time}', $time, $thisline); $thisline = mb_ereg_replace('{waypoint}', $r['waypoint'], $thisline); $thisline = mb_ereg_replace('{cacheid}', $r['cacheid'], $thisline); $thisline = mb_ereg_replace('{cachename}', $r['name'], $thisline); $thisline = mb_ereg_replace('{country}', $r['country'], $thisline); if ($r['hint'] == '') { $thisline = mb_ereg_replace('{hints}', '', $thisline); } else { $thisline = mb_ereg_replace('{hints}', str_rot13_gc(decodeEntities(strip_tags($r['hint']))), $thisline); } $thisline = mb_ereg_replace('{shortdesc}', $r['short_desc'], $thisline); $license = getLicenseDisclaimer($r['user_id'], $r['username'], $r['data_license'], $r['cacheid'], $opt['template']['locale'], true, false, true); if ($license != "") { $license = "\r\n\r\n{$license}"; } if ($r['html'] == 0) { $thisline = mb_ereg_replace('{htmlwarn}', '', $thisline); $thisline = mb_ereg_replace('{desc}', decodeEntities(strip_tags($r['desc'])) . $license, $thisline); } else { $thisline = mb_ereg_replace('{htmlwarn}', " ({$converted_from_html})", $thisline); $thisline = mb_ereg_replace('{desc}', html2txt($r['desc']) . $license, $thisline); } $thisline = mb_ereg_replace('{type}', $r['type'], $thisline); $thisline = mb_ereg_replace('{container}', $r['size'], $thisline); $thisline = mb_ereg_replace('{status}', $r['status'], $thisline); $difficulty = sprintf('%01.1f', $r['difficulty'] / 2); $thisline = mb_ereg_replace('{difficulty}', $difficulty, $thisline); $terrain = sprintf('%01.1f', $r['terrain'] / 2); $thisline = mb_ereg_replace('{terrain}', $terrain, $thisline); $thisline = mb_ereg_replace('{siteurl}', $opt['page']['default_absolute_url'], $thisline); $thisline = mb_ereg_replace('{owner}', $r['username'], $thisline); $flags = array(); if ($r['needs_maintenance'] > 0 || $r['listing_outdated']) { if ($r['needs_maintenance'] > 0) { $flags[] = 'geocache needs maintenance'; } if ($r['listing_outdated'] > 0) { $flags[] = 'description is outdated'; } } else { $flags[] = 'ok'; } foreach ($flags as &$flag) { $flag = $translate->t($flag, '', basename(__FILE__), __LINE__); } $thisline = mb_ereg_replace('{condition}', implode(', ', $flags), $thisline); // logs ermitteln $logentries = ''; $rsLogs = sql_slave("SELECT\n `cache_logs`.`id`,\n `cache_logs`.`text_html`,\n IFNULL(`stt`.`text`, `log_types`.`en`) `type`,\n `cache_logs`.`date`,\n `cache_logs`.`text`,\n `user`.`username`\n FROM `cache_logs`\n JOIN `user` ON `cache_logs`.`user_id`=`user`.`user_id`\n JOIN `log_types` ON `cache_logs`.`type`=`log_types`.`id`\n LEFT JOIN `sys_trans_text` `stt` ON `stt`.`trans_id`=`log_types`.`trans_id` AND `stt`.`lang`='&2'\n WHERE `cache_logs`.`cache_id`=&1\n ORDER BY\n `cache_logs`.`order_date` DESC,\n `cache_logs`.`date_created` DESC,\n `cache_logs`.`id` DESC\n LIMIT 20", $r['cacheid'], $opt['template']['locale']); while ($rLog = sql_fetch_array($rsLogs)) { $thislog = $txtLogs; $thislog = mb_ereg_replace('{id}', $rLog['id'], $thislog); $dateformat = "d.m.Y H:i"; if (substr($rLog['date'], 11) == "00:00:00") { $dateformat = "d.m.Y"; } $thislog = mb_ereg_replace('{date}', date($dateformat, strtotime($rLog['date'])), $thislog); $thislog = mb_ereg_replace('{username}', $rLog['username'], $thislog); $logtype = $rLog['type']; $thislog = mb_ereg_replace('{type}', $logtype, $thislog); if ($rLog['text_html'] == 0) { $thislog = mb_ereg_replace('{text}', decodeEntities(strip_tags($rLog['text'])), $thislog); } else { $thislog = mb_ereg_replace('{text}', html2txt($rLog['text']), $thislog); } $logentries .= $thislog . "\n"; } $thisline = mb_ereg_replace('{logs}', $logentries, $thisline); $thisline = lf2crlf($thisline); if (!$bUseZip) { echo $thisline; } else { $phpzip->add_data($r['waypoint'] . '.txt', $thisline); } } mysql_free_result($rs); }
function outputLocidSelectionForm($locSql, $options) { global $tpl; global $locline, $bgcolor1, $bgcolor2; require_once "lib2/logic/geodb.inc.php"; $urlparamString = prepareLocSelectionForm($options) . '&locid={locid}'; sql_temp_table_slave('locids'); sql_slave('CREATE TEMPORARY TABLE &locids ENGINE=MEMORY ' . $locSql); sql_slave('ALTER TABLE &locids ADD PRIMARY KEY (`loc_id`)'); $rs = sql_slave('SELECT `geodb_textdata`.`loc_id` `loc_id`, `geodb_textdata`.`text_val` `text_val` FROM `geodb_textdata`, &locids WHERE &locids.`loc_id`=`geodb_textdata`.`loc_id` AND `geodb_textdata`.`text_type`=500100000 ORDER BY `text_val`'); $nr = 1; $locations = ''; while ($r = sql_fetch_array($rs)) { $thislocation = $locline; // locationsdings zusammenbauen $locString = ''; $land = geodb_landFromLocid($r['loc_id']); if ($land != '') { $locString .= htmlspecialchars($land, ENT_COMPAT, 'UTF-8'); } $rb = geodb_regierungsbezirkFromLocid($r['loc_id']); if ($rb != '') { $locString .= ' > ' . htmlspecialchars($rb, ENT_COMPAT, 'UTF-8'); } $lk = geodb_landkreisFromLocid($r['loc_id']); if ($lk != '') { $locString .= ' > ' . htmlspecialchars($lk, ENT_COMPAT, 'UTF-8'); } $thislocation = mb_ereg_replace('{parentlocations}', $locString, $thislocation); // koordinaten ermitteln $r['loc_id'] = $r['loc_id'] + 0; $rsCoords = sql_slave('SELECT `lon`, `lat` FROM `geodb_coordinates` WHERE loc_id=' . $r['loc_id'] . ' LIMIT 1'); if ($rCoords = sql_fetch_array($rsCoords)) { $coordString = help_latToDegreeStr($rCoords['lat']) . ' ' . help_lonToDegreeStr($rCoords['lon']); } else { $coordString = '[' . $no_location_coords . ']'; } $thislocation = mb_ereg_replace('{coords}', htmlspecialchars($coordString, ENT_COMPAT, 'UTF-8'), $thislocation); $thislocation = mb_ereg_replace('{locationname}', htmlspecialchars($r['text_val'], ENT_COMPAT, 'UTF-8'), $thislocation); $thislocation = mb_ereg_replace('{urlparams}', $urlparamString, $thislocation); $thislocation = mb_ereg_replace('{locid}', urlencode($r['loc_id']), $thislocation); $thislocation = mb_ereg_replace('{nr}', $nr, $thislocation); $thislocation = mb_ereg_replace('{secondlocationname}', '', $thislocation); if ($nr % 2) { $thislocation = mb_ereg_replace('{bgcolor}', $bgcolor1, $thislocation); } else { $thislocation = mb_ereg_replace('{bgcolor}', $bgcolor2, $thislocation); } $nr++; $locations .= $thislocation . "\n"; } $tpl->assign('locations', $locations); $tpl->assign('resultscount', sql_num_rows($rs)); $tpl->assign('pages', ''); sql_free_result($rs); sql_drop_temp_table_slave('locids'); $tpl->display(); exit; }
// ok, ausgabe ... /* cacheid name lon lat archivedflag type size difficulty terrain username */ $sql = 'SELECT `ov2content`.`cache_id` `cacheid`, `ov2content`.`longitude` `longitude`, `ov2content`.`latitude` `latitude`, `caches`.`date_hidden` `date_hidden`, `caches`.`name` `name`, `caches`.`wp_oc` `wp_oc`, `cache_type`.`short` `typedesc`, `cache_size`.`de` `sizedesc`, `caches`.`terrain` `terrain`, `caches`.`difficulty` `difficulty`, `user`.`username` `username` FROM `ov2content`, `caches`, `cache_type`, `cache_size`, `user` WHERE `ov2content`.`cache_id`=`caches`.`cache_id` AND `ov2content`.`type`=`cache_type`.`id` AND `ov2content`.`size`=`cache_size`.`id` AND `ov2content`.`user_id`=`user`.`user_id`'; $rs = sql_slave($sql, $sqldebug); while ($r = sql_fetch_array($rs)) { $lat = sprintf('%07d', $r['latitude'] * 100000); $lon = sprintf('%07d', $r['longitude'] * 100000); $name = convert_string($r['name']); $username = convert_string($r['username']); $type = convert_string($r['typedesc']); $size = convert_string($r['sizedesc']); $difficulty = sprintf('%01.1f', $r['difficulty'] / 2); $terrain = sprintf('%01.1f', $r['terrain'] / 2); $cacheid = convert_string($r['wp_oc']); $line = "{$name} by {$username}, {$type}, {$size}, {$cacheid}"; $record = pack("CLllA*x", 2, 1 + 4 + 4 + 4 + strlen($line) + 1, (int) $lon, (int) $lat, $line); append_output($record); } mysql_free_result($rs);
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 cleanup_mapresult($slaveId) { // table mapresult sql_slave("DELETE FROM `mapresult` WHERE `date_created`<DATE_SUB(NOW(), INTERVAL 5 MINUTE)"); sql_slave("DELETE `mapresult_data` FROM `mapresult_data` LEFT JOIN `mapresult` ON `mapresult_data`.`query_id`=`mapresult`.`query_id` WHERE ISNULL(`mapresult`.`query_id`)"); }
$tpl->cache_id = $startat . "-" . $country . "-" . $cachetype; 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'; $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); }
<?php /*************************************************************************** * You can find the license in the docs directory * * Unicode Reminder メモ ***************************************************************************/ require './lib2/web.inc.php'; $tpl->name = 'newcaches_withoutown'; $tpl->menuitem = MNU_START_NEWCACHES_WITHOUTOWN; $tpl->caching = true; $tpl->cache_lifetime = 3600; if (!$tpl->is_cached()) { require $opt['rootpath'] . 'lib2/logic/cacheIcon.inc.php'; $newCaches = array(); sql_temp_table_slave('cachelist'); sql_slave("CREATE TEMPORARY TABLE &cachelist (`cache_id` INT(11) PRIMARY KEY) SELECT `cache_id` FROM `caches` INNER JOIN `cache_status` ON `caches`.`status`=`cache_status`.`id` WHERE `cache_status`.`allow_user_view`=1 AND `country`!='DE' ORDER BY `date_created` DESC LIMIT 200"); $rsNewCaches = sql_slave("SELECT IFNULL(`sys_trans_text`.`text`, `countries`.`name`) `country_name`, `caches`.`cache_id` `cacheid`, `caches`.`wp_oc` `wpoc`, `user`.`user_id` `userid`, `caches`.`country` `country`, `caches`.`name` `cachename`, `user`.`username` `username`, `caches`.`date_created` `date_created`, `cache_type`.`icon_large` `icon_large` \r\n\t\t FROM `caches`\r\n\t\t INNER JOIN `user` ON `caches`.`user_id`=`user`.`user_id`\r\n\t\t INNER JOIN `cache_type` ON `caches`.`type`=`cache_type`.`id`\r\n\t\t INNER JOIN &cachelist ON &cachelist.`cache_id`=`caches`.`cache_id`\r\n\t\t INNER JOIN `countries` ON `countries`.`short`=`caches`.`country`\r\n\t\t LEFT JOIN `sys_trans` ON `countries`.`trans_id`=`sys_trans`.`id`\r\n\t\t LEFT JOIN `sys_trans_text` ON `sys_trans`.`id`=`sys_trans_text`.`trans_id` AND `sys_trans_text`.`lang`='&1'\r\n\t\t ORDER BY `country_name`, `caches`.`date_created` DESC LIMIT 200", $opt['template']['locale']); while ($rNewCache = sql_fetch_assoc($rsNewCaches)) { $rNewCache['icon_large'] = getSmallCacheIcon($rNewCache['icon_large']); $newCaches[] = $rNewCache; } sql_free_result($rsNewCaches); sql_drop_temp_table_slave('cachelist'); $tpl->assign('newCaches', $newCaches); } $tpl->display();
$phpzip = new ss_zip('', 6); } // ok, ausgabe starten if ($sqldebug == false) { if ($bUseZip == true) { header("content-type: application/zip"); header('Content-Disposition: attachment; filename=' . $sFilebasename . '.zip'); } else { header("Content-type: application/gpx"); header("Content-Disposition: attachment; filename=" . $sFilebasename . ".gpx"); } } $gpxHead = mb_ereg_replace('{time}', date($gpxTimeFormat, time()), $gpxHead); append_output($gpxHead); // ok, ausgabe ... $rs = sql_slave('SELECT SQL_BUFFER_RESULT `gpxcontent`.`cache_id` `cacheid`, `gpxcontent`.`longitude` `longitude`, `gpxcontent`.`latitude` `latitude`, `gpxcontent`.`state` `state`, `caches`.`wp_oc` `waypoint`, `caches`.`date_hidden` `date_hidden`, `caches`.`name` `name`, `caches`.`country` `country`, `caches`.`terrain` `terrain`, `caches`.`difficulty` `difficulty`, `caches`.`desc_languages` `desc_languages`, `caches`.`size` `size`, `caches`.`type` `type`, `caches`.`status` `status`, `user`.`username` `username`, `caches`.`user_id` `userid`, `cache_desc`.`desc` `desc`, `cache_desc`.`short_desc` `short_desc`, `cache_desc`.`hint` `hint` FROM `gpxcontent`, `caches`, `user`, `cache_desc` WHERE `gpxcontent`.`cache_id`=`caches`.`cache_id` AND `caches`.`cache_id`=`cache_desc`.`cache_id` AND `caches`.`default_desclang`=`cache_desc`.`language` AND `gpxcontent`.`user_id`=`user`.`user_id`'); while ($r = sql_fetch_array($rs)) { $thisline = $gpxLine; $lat = sprintf('%01.5f', $r['latitude']); $thisline = mb_ereg_replace('{lat}', $lat, $thisline); $lon = sprintf('%01.5f', $r['longitude']); $thisline = mb_ereg_replace('{lon}', $lon, $thisline); $time = date($gpxTimeFormat, strtotime($r['date_hidden'])); $thisline = mb_ereg_replace('{time}', $time, $thisline); $thisline = mb_ereg_replace('{waypoint}', $r['waypoint'], $thisline); $thisline = mb_ereg_replace('{cacheid}', $r['cacheid'], $thisline); $thisline = mb_ereg_replace('{cachename}', xmlentities($r['name']), $thisline); $thisline = mb_ereg_replace('{country}', $r['country'], $thisline); $thisline = mb_ereg_replace('{state}', xmlentities($r['state']), $thisline); if ($r['hint'] == '') { $thisline = mb_ereg_replace('{hints}', '', $thisline);
<?php /*************************************************************************** * You can find the license in the docs directory * * Unicode Reminder メモ ***************************************************************************/ require './lib2/web.inc.php'; $tpl->name = 'newlogs'; $tpl->menuitem = MNU_START_NEWLOGS; $tpl->caching = true; $tpl->cache_lifetime = 300; if (!$tpl->is_cached()) { $newLogs = array(); sql_temp_table_slave('loglist'); sql_slave("CREATE TEMPORARY TABLE &loglist (`id` INT(11) PRIMARY KEY) SELECT `cache_logs`.`id` FROM `cache_logs` INNER JOIN `caches` ON `cache_logs`.`cache_id`=`caches`.`cache_id` INNER JOIN `cache_status` ON `caches`.`status`=`cache_status`.`id` WHERE `cache_status`.`allow_user_view`=1 ORDER BY `cache_logs`.`date_created` DESC LIMIT 200"); if ($opt['logic']['new_logs_per_country']) { $sqlOrderBy = '`countries`.`de` ASC, '; } else { $sqlOrderBy = ''; } $rsLogs = sql_slave("SELECT `countries`.`de` AS `country_name`, `cache_logs`.`id`, `cache_logs`.`date_created`, `caches`.`name` AS `cachename`, `caches`.`wp_oc`, `cache_logs`.`type`, `cacheloguser`.`user_id`, `cacheloguser`.`username` FROM &loglist INNER JOIN `cache_logs` ON &loglist.`id`=`cache_logs`.`id` INNER JOIN `caches` ON `cache_logs`.`cache_id`=`caches`.`cache_id` INNER JOIN `user` AS `cacheloguser` ON `cache_logs`.`user_id`=`cacheloguser`.`user_id` INNER JOIN `countries` ON `caches`.`country`=`countries`.`short` ORDER BY " . $sqlOrderBy . "`cache_logs`.`date_created` DESC"); while ($rLog = sql_fetch_assoc($rsLogs)) { $newLogs[] = $rLog; } sql_free_result($rsLogs); sql_drop_temp_table_slave('loglist'); $tpl->assign('newLogs', $newLogs); $tpl->assign('newLogsPerCountry', $opt['logic']['new_logs_per_country']); } $tpl->display();
<?php /*************************************************************************** * For license information see doc/license.txt * * Unicode Reminder メモ ***************************************************************************/ require './lib2/web.inc.php'; $tpl->name = 'newcachesrest'; $tpl->menuitem = MNU_START_NEWCACHES; $tpl->change_country_inpage = true; $tpl->caching = true; $tpl->cache_lifetime = 3600; $country = isset($_REQUEST['usercountry']) ? $_REQUEST['usercountry'] : (isset($_REQUEST['country']) ? $_REQUEST['country'] : $opt['page']['main_country']); if (!$tpl->is_cached()) { require $opt['rootpath'] . 'lib2/logic/cacheIcon.inc.php'; $newCaches = array(); sql_temp_table_slave('cachelist'); sql_slave("CREATE TEMPORARY TABLE &cachelist (`cache_id` INT(11) PRIMARY KEY)\n SELECT `cache_id` \n FROM `caches`\n INNER JOIN `cache_status` ON `caches`.`status`=`cache_status`.`id`\n WHERE `cache_status`.`allow_user_view`=1\n AND `country`!='" . sql_escape($country) . "' \n ORDER BY `date_created` DESC LIMIT 200"); $rsNewCaches = sql_slave("SELECT\n IFNULL(`sys_trans_text`.`text`,\n `countries`.`name`) `country_name`,\n `caches`.`cache_id` `cacheid`,\n `caches`.`wp_oc` `wpoc`,\n `user`.`user_id` `userid`,\n `caches`.`country` `country`,\n `caches`.`name` `cachename`,\n `caches`.`type`,\n `caches`.`country` `country`,\n `user`.`username` `username`,\n `caches`.`date_created` `date_created`,\n `ca`.`attrib_id` IS NOT NULL AS `oconly`\n FROM `caches`\n INNER JOIN `user` ON `caches`.`user_id`=`user`.`user_id`\n INNER JOIN `cache_type` ON `caches`.`type`=`cache_type`.`id`\n INNER JOIN &cachelist ON &cachelist.`cache_id`=`caches`.`cache_id`\n INNER JOIN `countries` ON `countries`.`short`=`caches`.`country`\n LEFT JOIN `sys_trans` ON `countries`.`trans_id`=`sys_trans`.`id`\n LEFT JOIN `sys_trans_text` ON `sys_trans`.`id`=`sys_trans_text`.`trans_id` AND `sys_trans_text`.`lang`='&1'\n LEFT JOIN `caches_attributes` `ca` ON `ca`.`cache_id`=`caches`.`cache_id` AND `ca`.`attrib_id`=6\n WHERE `status` = 1\n ORDER BY `country_name`, `caches`.`date_created` DESC LIMIT 200", $opt['template']['locale']); $tpl->assign_rs('newCaches', $rsNewCaches); sql_free_result($rsNewCaches); sql_drop_temp_table_slave('cachelist'); $tpl->assign('countryCode', $country); $tpl->assign('countryName', sql_value("SELECT IFNULL(`sys_trans_text`.`text`, `countries`.`name`)\n FROM `countries`\n LEFT JOIN `sys_trans` ON `countries`.`trans_id`=`sys_trans`.`id`\n LEFT JOIN `sys_trans_text` ON `sys_trans`.`id`=`sys_trans_text`.`trans_id` AND `sys_trans_text`.`lang`='&2'\n WHERE `countries`.`short`='&1'", '', $country, $opt['template']['locale'])); $tpl->assign('userCountryCode', $login->getUserCountry()); $tpl->assign('userCountryName', sql_value("SELECT IFNULL(`sys_trans_text`.`text`, `countries`.`name`)\n FROM `countries`\n LEFT JOIN `sys_trans` ON `countries`.`trans_id`=`sys_trans`.`id`\n LEFT JOIN `sys_trans_text` ON `sys_trans`.`id`=`sys_trans_text`.`trans_id` AND `sys_trans_text`.`lang`='&2'\n WHERE `countries`.`short`='&1'", '', $login->getUserCountry(), $opt['template']['locale'])); } $tpl->display();
function outputLocidSelectionForm($locSql, $urlparams) { global $tplname, $locline, $stylepath, $bgcolor1, $bgcolor2; require_once $stylepath . '/selectlocid.inc.php'; unset($urlparams['queryid']); unset($urlparams['locid']); $urlparams['searchto'] = 'search' . $urlparams['searchtype']; unset($urlparams['searchtype']); $tplname = 'selectlocid'; // urlparams zusammenbauen $urlparamString = ''; foreach ($urlparams as $name => $param) { // workaround for attribs if (is_array($param)) { $pnew = ''; foreach ($param as $p) { if ($pnew != '') { $pnew .= ';' . $p; } else { $pnew .= $p; } } $param = $pnew; } if ($urlparamString != '') { $urlparamString .= '&' . $name . '=' . urlencode($param); } else { $urlparamString = $name . '=' . urlencode($param); } } $urlparamString .= '&locid={locid}'; sql_slave('CREATE TEMPORARY TABLE `locids` ENGINE=MEMORY ' . $locSql); sql_slave('ALTER TABLE `locids` ADD PRIMARY KEY (`loc_id`)'); $rs = sql_slave('SELECT `geodb_textdata`.`loc_id` `loc_id`, `geodb_textdata`.`text_val` `text_val` FROM `geodb_textdata`, `locids` WHERE `locids`.`loc_id`=`geodb_textdata`.`loc_id` AND `geodb_textdata`.`text_type`=500100000 ORDER BY `text_val`'); $nr = 1; $locations = ''; while ($r = sql_fetch_array($rs)) { $thislocation = $locline; // locationsdings zusammenbauen $locString = ''; $land = landFromLocid($r['loc_id']); if ($land != '') { $locString .= htmlspecialchars($land, ENT_COMPAT, 'UTF-8'); } $rb = regierungsbezirkFromLocid($r['loc_id']); if ($rb != '') { $locString .= ' > ' . htmlspecialchars($rb, ENT_COMPAT, 'UTF-8'); } $lk = landkreisFromLocid($r['loc_id']); if ($lk != '') { $locString .= ' > ' . htmlspecialchars($lk, ENT_COMPAT, 'UTF-8'); } $thislocation = mb_ereg_replace('{parentlocations}', $locString, $thislocation); // koordinaten ermitteln $r['loc_id'] = $r['loc_id'] + 0; $rsCoords = sql_slave('SELECT `lon`, `lat` FROM `geodb_coordinates` WHERE loc_id=' . $r['loc_id'] . ' LIMIT 1'); if ($rCoords = sql_fetch_array($rsCoords)) { $coordString = help_latToDegreeStr($rCoords['lat']) . ' ' . help_lonToDegreeStr($rCoords['lon']); } else { $coordString = '[keine Koordinaten vorhanden]'; } $thislocation = mb_ereg_replace('{coords}', htmlspecialchars($coordString, ENT_COMPAT, 'UTF-8'), $thislocation); $thislocation = mb_ereg_replace('{locationname}', htmlspecialchars($r['text_val'], ENT_COMPAT, 'UTF-8'), $thislocation); $thislocation = mb_ereg_replace('{urlparams}', $urlparamString, $thislocation); $thislocation = mb_ereg_replace('{locid}', urlencode($r['loc_id']), $thislocation); $thislocation = mb_ereg_replace('{nr}', $nr, $thislocation); $thislocation = mb_ereg_replace('{secondlocationname}', '', $thislocation); if ($nr % 2) { $thislocation = mb_ereg_replace('{bgcolor}', $bgcolor1, $thislocation); } else { $thislocation = mb_ereg_replace('{bgcolor}', $bgcolor2, $thislocation); } $nr++; $locations .= $thislocation . "\n"; } tpl_set_var('locations', $locations); tpl_set_var('resultscount', mysql_num_rows($rs)); tpl_set_var('pages', '<< < 1 > >>'); tpl_BuildTemplate(); exit; }
append_output($locHead); // ok, ausgabe ... /* cacheid name lon lat archivedflag type size difficulty terrain username */ $rs = sql_slave('SELECT SQL_BUFFER_RESULT `loccontent`.`cache_id` `cacheid`, `loccontent`.`longitude` `longitude`, `loccontent`.`latitude` `latitude`, `caches`.`date_hidden` `date_hidden`, `caches`.`name` `name`, `caches`.`status` `status`, `caches`.`wp_oc` `waypoint`, `cache_type`.`short` `typedesc`, `cache_size`.`de` `sizedesc`, `caches`.`terrain` `terrain`, `caches`.`difficulty` `difficulty`, `user`.`username` `username` FROM `loccontent`, `caches`, `cache_type`, `cache_size`, `user` WHERE `loccontent`.`cache_id`=`caches`.`cache_id` AND `loccontent`.`type`=`cache_type`.`id` AND `loccontent`.`size`=`cache_size`.`id` AND `loccontent`.`user_id`=`user`.`user_id`'); while ($r = sql_fetch_array($rs)) { $thisline = $locLine; $lat = sprintf('%01.5f', $r['latitude']); $thisline = mb_ereg_replace('{lat}', $lat, $thisline); $lon = sprintf('%01.5f', $r['longitude']); $thisline = mb_ereg_replace('{lon}', $lon, $thisline); $thisline = mb_ereg_replace('{waypoint}', $r['waypoint'], $thisline); $thisline = mb_ereg_replace('{name}', xmlentities($r['name']), $thisline); if ($r['status'] == 2 || $r['status'] == 3) { if ($r['status'] == 2) { $thisline = mb_ereg_replace('{archivedflag}', 'Momentan nicht verfügbar!, ', $thisline); } else { $thisline = mb_ereg_replace('{archivedflag}', 'Archiviert!, ', $thisline); } } else {
$thisline = mb_ereg_replace('{desc}', strip_tags($r['desc']), $thisline); } else { $thisline = mb_ereg_replace('{htmlwarn}', ' (Vorsicht, aus HTML konvertiert)', $thisline); $thisline = mb_ereg_replace('{desc}', html2txt($r['desc']), $thisline); } $thisline = mb_ereg_replace('{type}', $r['type'], $thisline); $thisline = mb_ereg_replace('{container}', $r['size'], $thisline); $thisline = mb_ereg_replace('{status}', $r['status'], $thisline); $difficulty = sprintf('%01.1f', $r['difficulty'] / 2); $thisline = mb_ereg_replace('{difficulty}', $difficulty, $thisline); $terrain = sprintf('%01.1f', $r['terrain'] / 2); $thisline = mb_ereg_replace('{terrain}', $terrain, $thisline); $thisline = mb_ereg_replace('{owner}', $r['username'], $thisline); // logs ermitteln $logentries = ''; $rsLogs = sql_slave("SELECT `cache_logs`.`id`, `cache_logs`.`text_html`, `log_types`.`de` `type`, `cache_logs`.`date`, `cache_logs`.`text`, `user`.`username` FROM `cache_logs`, `user`, `log_types` WHERE `cache_logs`.`user_id`=`user`.`user_id` AND `cache_logs`.`type`=`log_types`.`id` AND `cache_logs`.`cache_id`=&1 ORDER BY `cache_logs`.`date` DESC LIMIT 20", $r['cacheid']); while ($rLog = sql_fetch_array($rsLogs)) { $thislog = $txtLogs; $thislog = mb_ereg_replace('{id}', $rLog['id'], $thislog); $thislog = mb_ereg_replace('{date}', date('d.m.Y', strtotime($rLog['date'])), $thislog); $thislog = mb_ereg_replace('{username}', $rLog['username'], $thislog); $logtype = $rLog['type']; $thislog = mb_ereg_replace('{type}', $logtype, $thislog); if ($rLog['text_html'] == 0) { $thislog = mb_ereg_replace('{text}', $rLog['text'], $thislog); } else { $thislog = mb_ereg_replace('{text}', html2txt($rLog['text']), $thislog); } $logentries .= $thislog . "\n"; } $thisline = mb_ereg_replace('{logs}', $logentries, $thisline);
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; }
private function resetArchiveFlag() { sql("SET @archive_picop=FALSE"); sql("SET @original_picid=0"); sql_slave("SET @archive_picop=FALSE"); sql_slave("SET @original_picid=0"); }
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(); }
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); if (isset($adm1Group['adm3']) && count($adm1Group['adm3']) > 0) { $tops[] = $adm1Group; }
sql_foundrows_done(); $paging = $total_logs > $logcount; } if ($newLogsPerCountry) { $orderByCountry = '`country_name` ASC, '; } else { $orderByCountry = ''; } $rsLogs = sql_slave("SELECT IFNULL(`sys_trans_text`.`text`, `countries`.`name`) AS `country_name`,\n `cache_logs`.`id`,\n `cache_logs`.`date_created`,\n `cache_logs`.`date`,\n `caches`.`name` AS `cachename`,\n `caches`.`wp_oc`,\n `caches`.`country` AS `country`,\n `cache_logs`.`type`,\n `cache_logs`.`oc_team_comment`,\n `cache_logs`.`needs_maintenance`,\n `cache_logs`.`listing_outdated`,\n `cacheloguser`.`user_id`,\n `cacheloguser`.`username`,\n `cache_logs`.`cache_id`,\n `cache_rating`.`rating_date` IS NOT NULL AND `cache_logs`.`type` IN (1,7) AS `recommended`,\n `caches_attributes`.`attrib_id` IS NOT NULL AS `oconly`,\n '' AS `pic_uuid`,\n 0 AS `picshown`,\n (SELECT COUNT(*) FROM `pictures` WHERE `object_type`=1 AND `object_id`=`cache_logs`.`id`) AS `pics`\n FROM &loglist\n INNER JOIN `cache_logs`\n ON &loglist.`id`=`cache_logs`.`id`\n INNER JOIN `caches`\n ON `cache_logs`.`cache_id`=`caches`.`cache_id`\n INNER JOIN `user` AS `cacheloguser`\n ON `cache_logs`.`user_id`=`cacheloguser`.`user_id`\n INNER JOIN `countries`\n ON `caches`.`country`=`countries`.`short`\n LEFT JOIN `sys_trans_text`\n ON `countries`.`trans_id`=`sys_trans_text`.`trans_id`\n AND `sys_trans_text`.`lang`='&1'\n LEFT JOIN `cache_logs_restored`\n ON `cache_logs_restored`.`id`=`cache_logs`.`id`\n LEFT JOIN `cache_rating`\n ON `cache_rating`.`cache_id`=`caches`.`cache_id`\n AND `cache_rating`.`user_id`=`cache_logs`.`user_id`\n AND `cache_rating`.`rating_date`=`cache_logs`.`date`\n LEFT JOIN `caches_attributes`\n ON `caches_attributes`.`cache_id`=`caches`.`cache_id`\n AND `caches_attributes`.`attrib_id`=6\n WHERE IFNULL(`cache_logs_restored`.`restored_by`,0)=0\n ORDER BY " . $orderByCountry . str_replace("{fromtable}", "`cache_logs`", $orderByDate) . "`cache_logs`.`date_created` DESC", $opt['template']['locale']); $newLogs = array(); $lines_per_pic = 5; $tpl->assign('lines_per_pic', $lines_per_pic); $pics = 0; while ($rLog = sql_fetch_assoc($rsLogs)) { if ($pics <= 0 || $pics == $lines_per_pic && count($newLogs) == 1 && !$newLogs[0]['picshow']) { $rsPic = sql_slave("SELECT `uuid`,`url`,`title`\n FROM `pictures`\n WHERE `object_type`=1\n AND `object_id`='&1'\n AND `local`=1\n AND `display`=1\n AND `spoiler`=0\n AND `unknown_format`=0\n LIMIT 1", $rLog['id']); if ($rPic = sql_fetch_assoc($rsPic)) { if (count($newLogs) >= 2) { $newLogs[count($newLogs) - 2]['pic_uuid'] = $rPic['uuid']; $newLogs[count($newLogs) - 2]['pic_url'] = $rPic['url']; $newLogs[count($newLogs) - 2]['title'] = $rPic['title']; $pics = $lines_per_pic; } elseif (count($newLogs) == 1) { $newLogs[0]['pic_uuid'] = $rPic['uuid']; $newLogs[0]['pic_url'] = $rPic['url']; $newLogs[0]['title'] = $rPic['title']; $pics = $lines_per_pic + 1; } else { $rLog['pic_uuid'] = $rPic['uuid']; $rLog['pic_url'] = $rPic['url']; $rLog['title'] = $rPic['title'];
function sql_value_internal($bQuerySlave, $sql, $default) { $args = func_get_args(); unset($args[0]); unset($args[1]); unset($args[2]); /* as an option, you can give as third parameter an array * with all values for the placeholder. The array has to be * with numeric indizes. */ if (isset($args[3]) && is_array($args[3])) { $tmp_args = $args[3]; unset($args); // correct indizes $args = array_merge(array(0), $tmp_args); unset($tmp_args); unset($args[0]); } if ($bQuerySlave == true) { $rs = sql_slave($sql, $args); } else { $rs = sql($sql, $args); } $r = sql_fetch_row($rs); sql_free_result($rs); if ($r) { if ($r[0] == null) { return $default; } else { return $r[0]; } } else { return $default; } }
./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;
if (!$tpl->is_cached()) { // welcome message if (isset($opt['page']['message'])) { $tpl->assign('message', $opt['page']['message']); } else { $tpl->assign('message', $translate->t('You can find everything you need to go Geocaching ...', '', '', 0)); } // pictures $tpl->assign('pictures', LogPics::get(LogPics::FOR_STARTPAGE_GALLERY)); // news entries $tpl->assign('news_onstart', $opt['news']['onstart']); if ($opt['news']['include'] == '') { $news = array(); $rs = sql_slave('SELECT `news`.`date_created` `date`, `news`.`content` `content`, `news_topics`.`name` `topic` FROM `news` INNER JOIN `news_topics` ON (`news`.`topic` = `news_topics`.`id`) WHERE `news`.`display` = 1 ORDER BY `news`.`date_created` DESC LIMIT 0, 6'); $tpl->assign_rs('news', $rs); sql_free_result($rs); $tpl->assign('extern_news', false); } else { /* * changed by bohrsty to fix error in displaying news from blog * requires $opt['news']['count'] in settings for number of blog-items * $opt['news']['include'] needs to be the RSS-URL of the blog * $url = $opt['news']['include']; $url = str_replace('{style}', $opt['template']['style'], $url); $newscontent = read_file($url, $opt['news']['maxsize']); */
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"; } }
/** * ratingRs executes the database statements for type "rating" * * @param array $args numeric array containing the parameter for "sql_slave" * @return object mysql result used by smarty assign_rs */ private function ratingRs($args = null) { // global global $opt; // check $args and set defaults if (is_null($args) || !is_array($args)) { $args = array($this->get_userCountry(), $opt['template']['locale'], 10, $this->ratingDays()); } // execute sql // 2012-08-24 following // optimized by adding rating_date field to cache_rating, so we don't need the log table. return sql_slave("SELECT COUNT(`cache_rating`.`user_id`) AS `cRatings`,\n\t\t\t\t\t\t\t\tMAX(`cache_rating`.`rating_date`) AS `dLastLog`,\n\t\t\t\t\t\t\t\t`user`.`user_id` AS `user_id`,\n\t\t\t\t\t\t\t\t`user`.`username` AS `username`,\n\t\t\t\t\t\t\t\t`caches`.`cache_id` AS `cache_id`,\n\t\t\t\t\t\t\t\t`caches`.`name` AS `name`,\n\t\t\t\t\t\t\t\t`caches`.`type`,\n\t\t\t\t\t\t\t\tIFNULL(`sys_trans_text`.`text`,`countries`.`en`) AS `adm1`,\n\t\t\t\t\t\t\t\tIF(`caches`.`country`=`cache_location`.`code1`,`cache_location`.`adm2`,'') `adm2`,\n\t\t\t\t\t\t\t\tIF(`caches`.`country`=`cache_location`.`code1`,`cache_location`.`adm3`,'') `adm3`,\n\t\t\t\t\t\t\t\tIF(`caches`.`country`=`cache_location`.`code1`,`cache_location`.`adm4`,'') `adm4`,\n\t\t\t\t\t\t\t\t`ca`.`attrib_id` IS NOT NULL AS `oconly`\n\t\t\t\t\t\t\tFROM `cache_rating`\n\t\t\t\t\t\t\t\tINNER JOIN `caches` ON `caches`.`cache_id`=`cache_rating`.`cache_id`\n\t\t\t\t\t\t\t\tINNER JOIN `user` ON `user`.`user_id`=`caches`.`user_id`\n\t\t\t\t\t\t\t\tLEFT JOIN `cache_location` ON `cache_rating`.`cache_id`=`cache_location`.`cache_id`\n\t\t\t\t\t\t\t\tLEFT JOIN `countries` ON `countries`.`short`=`caches`.`country`\n\t\t\t\t\t\t\t\tLEFT JOIN `sys_trans_text` ON `sys_trans_text`.`trans_id`=`countries`.`trans_id` AND `sys_trans_text`.`lang`='&2'\n\t\t\t\t\t\t\t\tLEFT JOIN `caches_attributes` `ca` ON `ca`.`cache_id`=`caches`.`cache_id` AND `ca`.`attrib_id`=6\n\t\t\t\t\t\t\tWHERE `caches`.`country`='&1' AND\n\t\t\t\t\t\t\t\t`cache_rating`.`rating_date`>DATE_SUB(NOW(), INTERVAL &4 DAY) AND\n\t\t\t\t\t\t\t\t`caches`.`type`!=6 AND\n\t\t\t\t\t\t\t\t`caches`.`status`=1\n\t\t\t\t\t\t\tGROUP BY `cache_rating`.`cache_id`\n\t\t\t\t\t\t\tORDER BY `cRatings` DESC,\n\t\t\t\t\t\t\t\t`dLastLog` DESC,\n\t\t\t\t\t\t\t\t`cache_id` DESC\n\t\t\t\t\t\t\tLIMIT 0, &3", $args); }
} // 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); } $tpl->display();