function process_new_cache($notify)
{
global $opt, $debug, $debug_mailto, $rootpath, $translate;
global $maildomain, $mailfrom;
//echo "process_new_cache(".$notify['id'].")\n";
$error = false;
// fetch email template
switch ($notify['type']) {
case notify_new_cache:
// Type: new cache
$mailbody = fetch_email_template('notify_newcache', $notify['recp_lang'], $notify['recp_domain']);
$mailsubject = '[' . $maildomain . '] ' . $translate->t($notify['oconly'] ? 'New OConly cache:' : 'New cache:', '', basename(__FILE__), __LINE__, '', 1, $notify['recp_lang']) . ' ' . $notify['cachename'];
break;
case notify_new_oconly:
// Type: new OConly flag
$mailbody = fetch_email_template('notify_newoconly', $notify['recp_lang'], $notify['recp_domain']);
$mailsubject = '[' . $maildomain . '] ' . $translate->t('Cache was marked as OConly:', '', basename(__FILE__), __LINE__, '', 1, $notify['recp_lang']) . ' ' . $notify['cachename'];
break;
default:
$error = true;
break;
}
if (!$error) {
$mailbody = mb_ereg_replace('{username}', $notify['recpname'], $mailbody);
$mailbody = mb_ereg_replace('{date}', date($opt['locale'][$notify['recp_lang']]['format']['phpdate'], strtotime($notify['date_hidden'])), $mailbody);
$mailbody = mb_ereg_replace('{cacheid}', $notify['cache_id'], $mailbody);
$mailbody = mb_ereg_replace('{wp_oc}', $notify['wp_oc'], $mailbody);
$mailbody = mb_ereg_replace('{user}', $notify['username'], $mailbody);
$mailbody = mb_ereg_replace('{cachename}', $notify['cachename'], $mailbody);
$mailbody = mb_ereg_replace('{distance}', round(geomath::calcDistance($notify['lat1'], $notify['lon1'], $notify['lat2'], $notify['lon2'], 1), 1), $mailbody);
$mailbody = mb_ereg_replace('{unit}', 'km', $mailbody);
$mailbody = mb_ereg_replace('{bearing}', geomath::Bearing2Text(geomath::calcBearing($notify['lat1'], $notify['lon1'], $notify['lat2'], $notify['lon2']), 0, $notify['recp_lang']), $mailbody);
$mailbody = mb_ereg_replace('{cachetype}', get_cachetype_name($notify['cachetype'], $notify['recp_lang']), $mailbody);
$mailbody = mb_ereg_replace('{cachesize}', get_cachesize_name($notify['cachesize'], $notify['recp_lang']), $mailbody);
$mailbody = mb_ereg_replace('{oconly-}', $notify['oconly'] ? $translate->t('OConly-', '', basename(__FILE__), __LINE__, '', 1, $notify['recp_lang']) : '', $mailbody);
/* begin send out everything that has to be sent */
$email_headers = 'From: "' . $mailfrom . '" <' . $mailfrom . '>';
// send email
if ($debug == true) {
$mailadr = $debug_mailto;
} else {
$mailadr = $notify['email'];
}
if (is_existent_maildomain(getToMailDomain($mailadr))) {
mb_send_mail($mailadr, $mailsubject, $mailbody, $email_headers);
}
} else {
echo "Unknown notification type: " . $notify['type'] . "<br />";
}
// logentry($module, $eventid, $userid, $objectid1, $objectid2, $logtext, $details)
logentry('notify_newcache', 8, $notify['recid'], $notify['cache_id'], 0, 'Sending mail to ' . $mailadr, []);
return 0;
}
* Unicode Reminder メモ
*
* Dieses Script berechnet andhand von Geodb-Daten die adm1..adm4-Daten
* für die GNS-DB.
***************************************************************************/
$opt['rootpath'] = '../../';
require_once $opt['rootpath'] . 'lib2/cli.inc.php';
require_once $opt['rootpath'] . 'lib2/search/search.inc.php';
$rsLocations = sql("SELECT `uni`, `lat`, `lon`, `rc`, `cc1`, `adm1` FROM `gns_locations` WHERE `dsg` LIKE 'PPL%'");
while ($rLocations = sql_fetch_array($rsLocations)) {
$minlat = geomath::getMinLat($rLocations['lon'], $rLocations['lat'], 10, 1);
$maxlat = geomath::getMaxLat($rLocations['lon'], $rLocations['lat'], 10, 1);
$minlon = geomath::getMinLon($rLocations['lon'], $rLocations['lat'], 10, 1);
$maxlon = geomath::getMaxLon($rLocations['lon'], $rLocations['lat'], 10, 1);
// den nächsgelegenen Ort in den geodb ermitteln
$sql = "SELECT " . geomath::getSqlDistanceFormula($rLocations['lon'], $rLocations['lat'], 10, 1, 'lon', 'lat', 'geodb_coordinates') . " `distance`,\n `geodb_coordinates`.`loc_id` `loc_id`\n FROM `geodb_coordinates`\n WHERE\n `lon` > '" . sql_escape($minlon) . "' AND `lon` < '" . sql_escape($maxlon) . "' AND\n `lat` > '" . sql_escape($minlat) . "' AND `lat` < '" . sql_escape($maxlat) . "'\n HAVING `distance` < 10\n ORDER BY `distance` ASC\n LIMIT 1";
$rs = sql($sql);
if (mysql_num_rows($rs) == 1) {
$r = sql_fetch_array($rs);
mysql_free_result($rs);
$locid = $r['loc_id'];
$admtxt1 = GeoDb::landFromLocid($locid);
if ($admtxt1 == '0') {
$admtxt1 = '';
}
// bundesland ermitteln
$rsAdm2 = sql("SELECT `full_name`, `short_form`\n FROM `gns_locations`\n WHERE `rc`='&1'\n AND `fc`='A'\n AND `dsg`='ADM1'\n AND `cc1`='&2'\n AND `adm1`='&3'\n AND `nt`='N'\n LIMIT 1", $rLocations['rc'], $rLocations['cc1'], $rLocations['adm1']);
if (mysql_num_rows($rsAdm2) == 1) {
$rAdm2 = sql_fetch_array($rsAdm2);
$admtxt2 = $rAdm2['short_form'];
if ($admtxt2 == '') {
function output_namesearch($sName, $nLat, $nLon, $nResultId)
{
global $login, $opt;
echo '<caches>' . "\n";
$rs = sql_slave("SELECT " . geomath::getSqlDistanceFormula($nLon, $nLat, 0) . " AS `distance`, \n `caches`.`name`, `caches`.`wp_oc` \n FROM `map2_data` \n INNER JOIN `caches` ON `map2_data`.`cache_id`=`caches`.`cache_id`\n INNER JOIN `cache_status` ON `caches`.`status`=`cache_status`.`id`\n WHERE `caches`.`name` LIKE '&1' \n AND (`cache_status`.`allow_user_view`=1 OR `caches`.`user_id`='&3')\n AND `map2_data`.`result_id`='&2'\n\t\t\t\t\t ORDER BY `distance` ASC LIMIT 30", '%' . $sName . '%', $nResultId, $login->userid);
$caches_found = 0;
while ($r = sql_fetch_assoc($rs)) {
echo '<cache name="' . xmlentities($r['name']) . '" wpoc="' . xmlentities($r['wp_oc']) . '" />' . "\n";
++$caches_found;
}
sql_free_result($rs);
if (!$caches_found && preg_match('/^[^\\s[:punct:]]{2,}\\.[^\\s[:punct:]]{2,}\\.[^\\s[:punct:]]{2,}$/', $sName)) {
$result = @file_get_contents('http://api.what3words.com/w3w?key=' . $opt['lib']['w3w']['apikey'] . '&string=' . urlencode($sName));
if ($result) {
$json = json_decode($result, true);
if (!is_null($json['words']) && !is_null($json['position']) && count($json['position']) == 2) {
echo '<coord name="' . xmlentities(implode('.', $json['words'])) . '" latitude="' . xmlentities($json["position"][0]) . '" longitude="' . xmlentities($json["position"][1]) . '" />' . "\n";
}
}
}
echo '</caches>' . "\n";
exit;
}
}
if (isset($lat_rad) && isset($lon_rad)) {
$sql .= geomath::getSqlDistanceFormula($lon_rad * 180 / 3.14159, $lat_rad * 180 / 3.14159, 0, $multiplier[$distance_unit]) . ' `distance`, ';
} else {
if (!$login->logged_in()) {
$sql .= 'NULL distance, ';
} else {
// get the user's home coords
$rs_coords = sql_slave("SELECT `latitude`, `longitude` FROM `user` WHERE `user_id`='&1'", $login->userid);
$record_coords = sql_fetch_array($rs_coords);
if ($record_coords['latitude'] == 0 && $record_coords['longitude'] == 0) {
$sql .= 'NULL distance, ';
} else {
$lon_rad = $record_coords['longitude'] * 3.14159 / 180;
$lat_rad = $record_coords['latitude'] * 3.14159 / 180;
$sql .= geomath::getSqlDistanceFormula($record_coords['longitude'], $record_coords['latitude'], 0, $multiplier[$distance_unit]) . ' `distance`, ';
}
sql_free_result($rs_coords);
}
}
if ($options['sort'] == 'bylastlog' || $options['sort'] == 'bymylastlog') {
$sAddFields .= ', MAX(`cache_logs`.`date`) AS `lastLog`';
$sAddJoin .= ' LEFT JOIN `cache_logs` ON `caches`.`cache_id`=`cache_logs`.`cache_id`';
if ($options['sort'] == 'bymylastlog') {
$sAddJoin .= ' AND `cache_logs`.`user_id`=' . sql_escape($login->userid);
}
$sGroupBy .= ' GROUP BY `caches`.`cache_id`';
}
$sql .= '`caches`.`cache_id`,
`caches`.`status`,
`caches`.`type`,
function startXmlSession($sModifiedSince, $bCache, $bCachedesc, $bCachelog, $bUser, $bPicture, $bRemovedObject, $bPictureFromCachelog, $selection, $sAgent)
{
global $opt, $ocxmlversion;
// session anlegen
sql("INSERT INTO `xmlsession` (`last_use`, `modified_since`, `date_created`, `agent`) VALUES (NOW(), '&1', NOW(), '&2')", date('Y-m-d H:i:s', strtotime($sModifiedSince)), $sAgent);
$sessionid = mysql_insert_id();
$recordcount['caches'] = 0;
$recordcount['cachedescs'] = 0;
$recordcount['cachelogs'] = 0;
$recordcount['users'] = 0;
$recordcount['pictures'] = 0;
$recordcount['removedobjects'] = 0;
if ($selection['type'] == 0) {
// ohne selection
if ($bCache == 1) {
if ($ocxmlversion >= 15) {
// Starting with version 15, we include the 'needs maintenance' and
// 'listing is outdated' flags in the <cache> records.
$wherefield = 'GREATEST(`listing_last_modified`,`flags_last_modified`)';
} else {
if ($ocxmlversion == 14) {
// Starting with version 14, we include listing_last_modified in the
// <caches> records, so this date is relevant for updates
$wherefield = '`listing_last_modified`';
} else {
$wherefield = '`last_modified`';
}
}
sql("INSERT INTO xmlsession_data (`session_id`, `object_type`, `object_id`)\n\t\t\t SELECT &1, 2, `cache_id` FROM `caches` WHERE " . $wherefield . " >= '&2' AND `status`!=5", $sessionid, $sModifiedSince);
$recordcount['caches'] = mysql_affected_rows();
}
if ($bCachedesc == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n\t\t\t SELECT &1, 3, `cache_desc`.`id` FROM `cache_desc` INNER JOIN `caches` ON `cache_desc`.`cache_id`=`caches`.`cache_id` WHERE `cache_desc`.`last_modified` >= '&2' AND `caches`.`status`!=5", $sessionid, $sModifiedSince);
$recordcount['cachedescs'] = mysql_affected_rows();
}
if ($bCachelog == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n\t\t\t SELECT &1, 1, `cache_logs`.`id` FROM `cache_logs` INNER JOIN `caches` ON `cache_logs`.`cache_id`=`caches`.`cache_id` WHERE `cache_logs`.`last_modified` >= '&2' AND `caches`.`status`!=5", $sessionid, $sModifiedSince);
$recordcount['cachelogs'] = mysql_affected_rows();
}
if ($bUser == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n\t\t\t SELECT &1, 4, `user_id` FROM `user` WHERE `last_modified` >= '&2'", $sessionid, $sModifiedSince);
$recordcount['users'] = mysql_affected_rows();
}
if ($bPicture == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n\t\t\t SELECT &1, 6, `pictures`.`id` FROM `pictures` INNER JOIN\n\t\t\t `caches` ON `pictures`.`object_type`=2 AND\n\t\t\t `pictures`.`object_id`=`caches`.`cache_id`\n\t\t\t WHERE `pictures`.`last_modified` >= '&2' AND\n\t\t\t `caches`.`status`!=5\n\t\t\t UNION DISTINCT\n\t\t\t SELECT &1, 6, `pictures`.`id` FROM `pictures` INNER JOIN\n\t\t\t `cache_logs` ON `pictures`.`object_type`=1 AND\n\t\t\t `pictures`.`object_id`=`cache_logs`.`id` INNER JOIN\n\t\t\t `caches` ON `cache_logs`.`cache_id`=`caches`.`cache_id`\n\t\t\t WHERE `pictures`.`last_modified` >= '&2' AND\n\t\t\t `caches`.`status`!=5", $sessionid, $sModifiedSince);
$recordcount['pictures'] = mysql_affected_rows();
}
if ($bRemovedObject == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n\t\t\t SELECT &1, 7, `id` FROM `removed_objects` WHERE `removed_date` >= '&2' AND `type`<>8", $sessionid, $sModifiedSince);
$recordcount['removedobjects'] = mysql_affected_rows();
}
} else {
$sqlWhere = '';
$sqlHaving = '';
if ($selection['type'] == 1) {
sql("CREATE TEMPORARY TABLE `tmpxmlSesssionCaches` (`cache_id` int(11), PRIMARY KEY (`cache_id`)) ENGINE=MEMORY\n\t\t\t SELECT DISTINCT `cache_countries`.`cache_id` FROM `caches`, `cache_countries` WHERE `caches`.`cache_id`=`cache_countries`.`cache_id` AND `cache_countries`.`country`='&1' AND `caches`.`status`!=5", $selection['country']);
} else {
if ($selection['type'] == 2) {
$sql = 'CREATE TEMPORARY TABLE `tmpxmlSesssionCaches` (`cache_id` int(11), `distance` double, KEY (`cache_id`)) ENGINE=MEMORY ';
$sql .= 'SELECT `cache_coordinates`.`cache_id`, ';
$sql .= geomath::getSqlDistanceFormula($selection['lon'], $selection['lat'], $selection['distance'], 1, 'longitude', 'latitude', 'cache_coordinates') . ' `distance` ';
$sql .= 'FROM `caches`, `cache_coordinates` WHERE ';
$sql .= '`cache_coordinates`.`cache_id`=`caches`.`cache_id`';
$sql .= ' AND `caches`.`status`!=5';
$sql .= ' AND `cache_coordinates`.`latitude` > ' . geomath::getMinLat($selection['lon'], $selection['lat'], $selection['distance']);
$sql .= ' AND `cache_coordinates`.`latitude` < ' . geomath::getMaxLat($selection['lon'], $selection['lat'], $selection['distance']);
$sql .= ' AND `cache_coordinates`.`longitude` >' . geomath::getMinLon($selection['lon'], $selection['lat'], $selection['distance']);
$sql .= ' AND `cache_coordinates`.`longitude` < ' . geomath::getMaxLon($selection['lon'], $selection['lat'], $selection['distance']);
$sql .= ' HAVING `distance` < ' . ($selection['distance'] + 0);
sql($sql);
} else {
if ($selection['type'] == 3) {
sql("CREATE TEMPORARY TABLE `tmpxmlSesssionCaches` (`cache_id` int(11), PRIMARY KEY (`cache_id`)) ENGINE=MEMORY\n\t\t\t SELECT `cache_id` FROM `caches` WHERE `cache_id`='&1' AND `status`!=5", $selection['cacheid'] + 0);
}
}
}
if ($bCache == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n\t\t\t SELECT DISTINCT &1, 2, `tmpxmlSesssionCaches`.`cache_id` FROM `tmpxmlSesssionCaches`, `caches`\n\t\t\t WHERE `tmpxmlSesssionCaches`.`cache_id`=`caches`.`cache_id` AND `caches`.`last_modified` >= '&2'", $sessionid, $sModifiedSince);
$recordcount['caches'] = mysql_affected_rows();
}
if ($bCachedesc == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n\t\t\t SELECT DISTINCT &1, 3, `cache_desc`.`id` FROM `cache_desc`, `tmpxmlSesssionCaches`\n\t\t\t WHERE `cache_desc`.`cache_id`=`tmpxmlSesssionCaches`.`cache_id` AND `cache_desc`.`last_modified` >= '&2'", $sessionid, $sModifiedSince);
$recordcount['cachedescs'] = mysql_affected_rows();
}
if ($bCachelog == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n\t\t\t SELECT DISTINCT &1, 1, `cache_logs`.`id` FROM `cache_logs`, `tmpxmlSesssionCaches`\n\t\t\t WHERE `cache_logs`.`cache_id`=`tmpxmlSesssionCaches`.`cache_id` AND `cache_logs`.`last_modified` >= '&2'", $sessionid, $sModifiedSince);
$recordcount['cachelogs'] = mysql_affected_rows();
}
if ($bPicture == 1) {
// cachebilder
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n\t\t\t SELECT DISTINCT &1, 6, `pictures`.`id` FROM `pictures`, `tmpxmlSesssionCaches`\n\t\t\t WHERE `pictures`.`object_id`=`tmpxmlSesssionCaches`.`cache_id` AND `pictures`.`object_type`=2 AND\n\t\t\t `pictures`.`last_modified` >= '&2'", $sessionid, $sModifiedSince);
$recordcount['pictures'] = mysql_affected_rows();
// bilder von logs
if ($bPictureFromCachelog == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n\t\t\t\t SELECT DISTINCT &1, 6, `pictures`.id FROM `pictures` , `cache_logs`, `tmpxmlSesssionCaches`\n\t\t\t\t WHERE `tmpxmlSesssionCaches`.`cache_id`=`cache_logs`.`cache_id` AND\n\t\t\t\t `pictures`.`object_type`=1 AND `pictures`.`object_id`=`cache_logs`.`id` AND\n\t\t\t\t `pictures`.`last_modified` >= '&2'", $sessionid, $sModifiedSince);
$recordcount['pictures'] += mysql_affected_rows();
}
}
if ($bRemovedObject == 1) {
sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n\t\t\t SELECT DISTINCT &1, 7, `id` FROM `removed_objects` WHERE `removed_date` >= '&2'", $sessionid, $sModifiedSince);
$recordcount['removedobjects'] = mysql_affected_rows();
}
}
sql('UPDATE `xmlsession` SET `caches`=&1, `cachedescs`=&2, `cachelogs`=&3, `users`=&4, `pictures`=&5, `removedobjects`=&6 WHERE `id`=&7 LIMIT 1', $recordcount['caches'], $recordcount['cachedescs'], $recordcount['cachelogs'], $recordcount['users'], $recordcount['pictures'], $recordcount['removedobjects'], $sessionid);
return $sessionid;
}
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();
}
function sqlStringbySearchradius($distance, $lat, $lon, $multiplier, $distance_unit)
{
global $sql_select, $sql_from, $sql_innerjoin;
//all target caches are between lat - max_lat_diff and lat + max_lat_diff
$max_lat_diff = $distance / (111.12 * $multiplier[$distance_unit]);
//all target caches are between lon - max_lon_diff and lon + max_lon_diff
//TODO: check!!!
$max_lon_diff = $distance * 180 / (abs(sin((90 - $lat) * 3.14159 / 180)) * 6378 * $multiplier[$distance_unit] * 3.14159);
$lon_rad = $lon * 3.14159 / 180;
$lat_rad = $lat * 3.14159 / 180;
sql_temp_table_slave('result_caches');
$cachesFilter = "CREATE TEMPORARY TABLE &result_caches ENGINE=MEMORY\n SELECT\n (" . geomath::getSqlDistanceFormula($lon, $lat, $distance, $multiplier[$distance_unit]) . ") `distance`,\n `caches`.`cache_id` `cache_id`\n FROM `caches` FORCE INDEX (`latitude`)\n WHERE\n `longitude` > " . ($lon - $max_lon_diff) . "\n AND `longitude` < " . ($lon + $max_lon_diff) . "\n AND `latitude` > " . ($lat - $max_lat_diff) . "\n AND `latitude` < " . ($lat + $max_lat_diff) . "\n HAVING `distance` < " . ($distance + 0);
sql_slave($cachesFilter);
sql_slave("ALTER TABLE &result_caches ADD PRIMARY KEY ( `cache_id` )");
$sql_select[] = '&result_caches.`cache_id`';
$sql_from = '&result_caches';
$sql_innerjoin[] = '`caches` ON `caches`.`cache_id`=&result_caches.`cache_id`';
}
public static function getLogsArray($cacheid, $start, $count, $deleted = false, $protect_old_coords = false)
{
global $login, $translate;
// negative or abornally high numbers like 1.0E+15 can crash the LIMIT statement
if ($count <= 0 || $count > 10000) {
return array();
}
$rsCoords = sql("SELECT `date_created` `date`, `latitude`, `longitude`\n\t\t\t FROM `cache_coordinates`\n\t\t\t WHERE `cache_id`='&1'\n\t\t\t ORDER BY `date_created` DESC", $cacheid);
$coords = sql_fetch_assoc_table($rsCoords);
if ($coords) {
$coords[] = ['date' => '0000-00-00', 'latitude' => $coords[count($coords) - 1]['latitude'], 'longitude' => $coords[count($coords) - 1]['longitude']];
$current_coord = new coordinate($coords[0]['latitude'], $coords[0]['longitude']);
}
if ($deleted && ($login->admin && ADMIN_USER) > 0) {
// admins may view owner-deleted logs
$table = 'cache_logs_archived';
$delfields = 'IFNULL(`u2`.`username`,"") AS `deleted_by_name`, `deletion_date`, "1" AS `deleted`';
$addjoin = 'LEFT JOIN `user` `u2` ON `u2`.`user_id`=`cache_logs`.`deleted_by`';
} else {
$table = 'cache_logs';
$delfields = '"" AS `deleted_by_name`, NULL AS `deletion_date`, "0" AS `deleted`';
$addjoin = '';
}
$rsLogs = sql('SELECT `cache_logs`.`user_id` AS `userid`,
`cache_logs`.`id` AS `id`,
`cache_logs`.`uuid` AS `uuid`,
`cache_logs`.`date` AS `date`,
`cache_logs`.`order_date` AS `order_date`,
`cache_logs`.`entry_last_modified`,
DATEDIFF(`cache_logs`.`entry_last_modified`, `cache_logs`.`date_created`) >= 1 AS `late_modified`,
substr(`cache_logs`.`date`,12) AS `time`, /* 00:00:01 = 00:00 logged, 00:00:00 = no time */
`cache_logs`.`type` AS `type`,
`cache_logs`.`oc_team_comment` AS `oc_team_comment`,
`cache_logs`.`needs_maintenance` AS `needs_maintenance`,
`cache_logs`.`listing_outdated` AS `listing_outdated`,
`cache_logs`.`text` AS `text`,
`cache_logs`.`text_html` AS `texthtml`,
`cache_logs`.`picture`,
' . $delfields . ",\n\t\t\t\t `user`.`username` AS `username`,\n\t\t\t\t IF(ISNULL(`cache_rating`.`cache_id`), 0, `cache_logs`.`type` IN (1,7)) AS `recommended`\n\t\t\t FROM {$table} AS `cache_logs`\n\t\t\t INNER JOIN `user` \n\t\t\t ON `user`.`user_id` = `cache_logs`.`user_id`\n\t\t\t LEFT JOIN `cache_rating` \n\t\t\t ON `cache_logs`.`cache_id`=`cache_rating`.`cache_id` \n\t\t\t AND `cache_logs`.`user_id`=`cache_rating`.`user_id` \n\t\t\t AND `cache_logs`.`date`=`cache_rating`.`rating_date`\n\t\t\t " . $addjoin . "\n\t\t\t WHERE `cache_logs`.`cache_id`='&1'\n\t\t\t ORDER BY `cache_logs`.`order_date` DESC, `cache_logs`.`date_created` DESC, `id` DESC\n\t\t\t LIMIT &2, &3", $cacheid, $start + 0, $count + 0);
$logs = array();
$coordpos = 0;
$coord_changes = false;
while ($rLog = sql_fetch_assoc($rsLogs)) {
$pictures = array();
$rsPictures = sql("SELECT `url`, `title`, `uuid`, `id`, `spoiler`\n\t\t\t\t FROM `pictures`\n\t\t\t\t WHERE `object_id`='&1' \n\t\t\t\t AND `object_type`=1\n\t\t\t\t ORDER BY `seq`", $rLog['id']);
while ($rPicture = sql_fetch_assoc($rsPictures)) {
if (trim($rPicture['title']) == '') {
$rPicture['title'] = $translate->t('Picture', '', '', 0) . ' ' . (count($pictures) + 1);
}
$pictures[] = $rPicture;
}
sql_free_result($rsPictures);
$rLog['pictures'] = $pictures;
$rLog['text'] = use_current_protocol_in_html($rLog['text']);
$newcoord = false;
while ($coordpos < count($coords) && $coords[$coordpos]['date'] > $rLog['order_date']) {
if (!$newcoord) {
$newcoord = $coords[$coordpos];
}
++$coordpos;
}
if ($newcoord) {
$distance = geomath::calcDistance($newcoord['latitude'], $newcoord['longitude'], $coords[$coordpos]['latitude'], $coords[$coordpos]['longitude']);
if (abs($distance) > 0.005) {
$new = new coordinate($newcoord['latitude'], $newcoord['longitude']);
$rLog['newcoord'] = $new->getDecimalMinutes($protect_old_coords && $new != $current_coord);
if ($protect_old_coords) {
$rLog['movedbykm'] = false;
} elseif ($distance <= 1) {
$rLog['movedbym'] = floor($distance * 1000);
} elseif ($distance < 10) {
$rLog['movedbykm'] = sprintf('%1.1f', $distance);
} else {
$rLog['movedbykm'] = round($distance);
}
$coord_changes = true;
}
}
$logs[] = $rLog;
}
sql_free_result($rsLogs);
if ($coord_changes) {
$original = count($coords) - 1;
$lastlogdate = $logs[count($logs) - 1]['order_date'];
while ($original > 0 && $coords[$original - 1]['date'] < $lastlogdate) {
--$original;
}
$coord = new coordinate($coords[$original]['latitude'], $coords[$original]['longitude']);
$logs[] = ['newcoord' => $coord->getDecimalMinutes($protect_old_coords), 'movedby' => false];
}
return $logs;
}
* Dieses Script berechnet andhand von Geodb-Daten die adm1..adm4-Daten
* für die GNS-DB.
***************************************************************************/
$opt['rootpath'] = '../../';
require_once $opt['rootpath'] . 'lib2/cli.inc.php';
require_once $opt['rootpath'] . 'lib2/search/search.inc.php';
require_once $opt['rootpath'] . 'lib2/logic/geomath.class.php';
require_once $opt['rootpath'] . 'lib2/logic/geodb.inc.php';
$rsLocations = sql("SELECT `uni`, `lat`, `lon`, `rc`, `cc1`, `adm1` FROM `gns_locations` WHERE `dsg` LIKE 'PPL%'");
while ($rLocations = sql_fetch_array($rsLocations)) {
$minlat = geomath::getMinLat($rLocations['lon'], $rLocations['lat'], 10, 1);
$maxlat = geomath::getMaxLat($rLocations['lon'], $rLocations['lat'], 10, 1);
$minlon = geomath::getMinLon($rLocations['lon'], $rLocations['lat'], 10, 1);
$maxlon = geomath::getMaxLon($rLocations['lon'], $rLocations['lat'], 10, 1);
// den nächsgelegenen Ort in den geodb ermitteln
$sql = 'SELECT ' . geomath::getSqlDistanceFormula($rLocations['lon'], $rLocations['lat'], 10, 1, 'lon', 'lat', 'geodb_coordinates') . ' `distance`,
`geodb_coordinates`.`loc_id` `loc_id`
FROM `geodb_coordinates`
WHERE `lon` > ' . $minlon . ' AND
`lon` < ' . $maxlon . ' AND
`lat` > ' . $minlat . ' AND
`lat` < ' . $maxlat . '
HAVING `distance` < 10
ORDER BY `distance` ASC
LIMIT 1';
$rs = sql($sql);
if (mysql_num_rows($rs) == 1) {
$r = sql_fetch_array($rs);
mysql_free_result($rs);
$locid = $r['loc_id'];
$admtxt1 = geodb_landFromLocid($locid);
function output_namesearch($sName, $nLat, $nLon, $nResultId)
{
global $login;
$sName = '%' . $sName . '%';
echo '<caches>' . "\n";
$rs = sql_slave("SELECT " . geomath::getSqlDistanceFormula($nLon, $nLat, 0) . " AS `distance`, \r\n `caches`.`name`, `caches`.`wp_oc` \r\n FROM `map2_data` \r\n INNER JOIN `caches` ON `map2_data`.`cache_id`=`caches`.`cache_id`\r\n INNeR JOIN `cache_status` ON `caches`.`status`=`cache_status`.`id`\r\n WHERE `caches`.`name` LIKE '&1' \r\n AND (`cache_status`.`allow_user_view`=1 OR `caches`.`user_id`='&3')\r\n AND `map2_data`.`result_id`='&2'\r\n\t\t\t\t\t ORDER BY `distance` ASC LIMIT 30", $sName, $nResultId, $login->userid);
while ($r = sql_fetch_assoc($rs)) {
echo '<cache name="' . xmlentities($r['name']) . '" wpoc="' . xmlentities($r['wp_oc']) . '" />' . "\n";
}
sql_free_result($rs);
echo '</caches>';
exit;
}
function output_namesearch($name, $lat, $lon)
{
$name = '%' . $name . '%';
echo '<caches>' . "\n";
$rs = sql_slave("SELECT " . geomath::getSqlDistanceFormula($lon, $lat, 0) . " AS `distance`, \r\n `caches`.`name`, `caches`.`wp_oc` \r\n FROM `caches` \r\n WHERE `name` LIKE '&1' \r\n AND `status` IN (1, 2) \r\n\t\t\t\t\t ORDER BY `distance` ASC LIMIT 30", $name);
while ($r = sql_fetch_assoc($rs)) {
echo '<cache name="' . xmlentities($r['name']) . '" wpoc="' . xmlentities($r['wp_oc']) . '" />' . "\n";
}
sql_free_result($rs);
echo '</caches>';
exit;
}
