} if ($optimize_for_latest_logs) { // filtering a large number of logs is very expensive; // therefore we preselect the newest logs if the main country is selected sql_temp_table_slave('loglist0'); sql_slave("\n CREATE TEMPORARY TABLE &loglist0\n (SELECT\n `id`, `cache_id`, `user_id`, `date_created`, `date`, `type`,\n `needs_maintenance`, `listing_outdated`, `order_date`\n FROM `cache_logs`\n ORDER BY `date_created` DESC\n LIMIT " . 4 * $logcount . ")"); $fromtable = '`&loglist0`'; } else { $fromtable = '`cache_logs`'; } $add_where = str_replace("{fromtable}", $fromtable, $add_where); sql_temp_table_slave('loglist'); sql_slave("CREATE TEMPORARY TABLE &loglist (`id` INT(11) PRIMARY KEY)\n SELECT " . ($paging ? "SQL_CALC_FOUND_ROWS" : "") . " " . $fromtable . ".`id`\n FROM " . $fromtable . "\n INNER JOIN `caches` ON " . $fromtable . ".`cache_id`=`caches`.`cache_id`\n INNER JOIN `cache_status` ON `caches`.`status`=`cache_status`.`id`\n INNER JOIN `user` ON " . $fromtable . ".`user_id`=`user`.`user_id`\n WHERE `cache_status`.`allow_user_view`=1\n AND `caches`.`country` LIKE '&1'\n AND `caches`.`country`<>'&2'\n " . $add_where . "\n ORDER BY " . str_replace("{fromtable}", $fromtable, $orderByDate) . $fromtable . ".`date_created` DESC\n LIMIT &3, &4", $include_country, $exclude_country, $startat, $logcount); if ($paging) { $total_logs = sql_value("SELECT FOUND_ROWS()", 0); sql_foundrows_done(); $paging = $total_logs > $logcount; } if ($newLogsPerCountry) { $orderByCountry = '`country_name` ASC, '; } else { $orderByCountry = ''; } $rsLogs = sql_slave("SELECT IFNULL(`sys_trans_text`.`text`, `countries`.`name`) AS `country_name`,\n `cache_logs`.`id`,\n `cache_logs`.`date_created`,\n `cache_logs`.`date`,\n `caches`.`name` AS `cachename`,\n `caches`.`wp_oc`,\n `caches`.`country` AS `country`,\n `cache_logs`.`type`,\n `cache_logs`.`oc_team_comment`,\n `cache_logs`.`needs_maintenance`,\n `cache_logs`.`listing_outdated`,\n `cacheloguser`.`user_id`,\n `cacheloguser`.`username`,\n `cache_logs`.`cache_id`,\n `cache_rating`.`rating_date` IS NOT NULL AND `cache_logs`.`type` IN (1,7) AS `recommended`,\n `caches_attributes`.`attrib_id` IS NOT NULL AS `oconly`,\n '' AS `pic_uuid`,\n 0 AS `picshown`,\n (SELECT COUNT(*) FROM `pictures` WHERE `object_type`=1 AND `object_id`=`cache_logs`.`id`) AS `pics`\n FROM &loglist\n INNER JOIN `cache_logs`\n ON &loglist.`id`=`cache_logs`.`id`\n INNER JOIN `caches`\n ON `cache_logs`.`cache_id`=`caches`.`cache_id`\n INNER JOIN `user` AS `cacheloguser`\n ON `cache_logs`.`user_id`=`cacheloguser`.`user_id`\n INNER JOIN `countries`\n ON `caches`.`country`=`countries`.`short`\n LEFT JOIN `sys_trans_text`\n ON `countries`.`trans_id`=`sys_trans_text`.`trans_id`\n AND `sys_trans_text`.`lang`='&1'\n LEFT JOIN `cache_logs_restored`\n ON `cache_logs_restored`.`id`=`cache_logs`.`id`\n LEFT JOIN `cache_rating`\n ON `cache_rating`.`cache_id`=`caches`.`cache_id`\n AND `cache_rating`.`user_id`=`cache_logs`.`user_id`\n AND `cache_rating`.`rating_date`=`cache_logs`.`date`\n LEFT JOIN `caches_attributes`\n ON `caches_attributes`.`cache_id`=`caches`.`cache_id`\n AND `caches_attributes`.`attrib_id`=6\n WHERE IFNULL(`cache_logs_restored`.`restored_by`,0)=0\n ORDER BY " . $orderByCountry . str_replace("{fromtable}", "`cache_logs`", $orderByDate) . "`cache_logs`.`date_created` DESC", $opt['template']['locale']); $newLogs = array(); $lines_per_pic = 5; $tpl->assign('lines_per_pic', $lines_per_pic); $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']);
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(); }