$tpl->menuitem = MNU_MYPROFILE_PUBLIC;
}
}
// user data and basic statistics
$rs = sql("SELECT `user`.`username`, \n\t\t\t\t\t\t\t\t\t\t`user`.`last_login`, \n\t\t\t\t\t\t\t\t\t\t`user`.`accept_mailing`, \n\t\t\t\t\t\t\t\t\t\t`user`.`pmr_flag`, \n\t\t\t\t\t\t\t\t\t\t`user`.`date_created`, \n\t\t\t\t\t\t\t\t\t\t`user`.`password`, \n\t\t\t\t\t\t\t\t\t\t`user`.`email`, \n\t\t\t\t\t\t\t\t\t\t`user`.`is_active_flag`, \n\t\t\t\t\t\t\t\t\t\t`user`.`latitude`, \n\t\t\t\t\t\t\t\t\t\t`user`.`longitude`,\n\t\t\t\t\t\t\t\t\t\t`user`.`data_license`, \n\t\t\t\t\t\t\t\t\t\tIFNULL(`sys_trans_text`.`text`,`countries`.`name`) AS `country`,\n\t\t\t\t\t\t\t\t\t\t`stat_user`.`hidden`, \n\t\t\t\t\t\t\t\t\t\t`stat_user`.`found`, \n\t\t\t\t\t\t\t\t\t\t`stat_user`.`notfound`, \n\t\t\t\t\t\t\t\t\t\t`stat_user`.`note`, \n\t\t\t\t\t\t\t\t\t\t`stat_user`.`maintenance`,\n\t\t\t\t\t\t\t\t\t\t`user`.`uuid` \n\t\t\t\t\t\t\t FROM `user` \n\t\t\t\t\tLEFT JOIN `stat_user` ON `user`.`user_id`=`stat_user`.`user_id` \n\t\t\t\t\tLEFT JOIN `countries` ON `user`.`country`=`countries`.`short`\n\t\t\t\t\tLEFT JOIN `sys_trans_text` ON `sys_trans_text`.`lang`='&2' AND `sys_trans_text`.`trans_id`=`countries`.`trans_id`\n\t\t\t\t\t\t\tWHERE `user`.`user_id`='&1'", $userid, $opt['template']['locale']);
$record = sql_fetch_array($rs);
sql_free_result($rs);
if ($record === false) {
$tpl->error(ERROR_USER_NOT_EXISTS);
}
$active = sql_value("SELECT COUNT(*) FROM `caches` WHERE `user_id`='&1' AND `status`=1", 0, $userid);
$rs = sql("SELECT IFNULL(`tt`.`text`, `p`.`name`) AS `name`, `u`.`option_value`, `u`.`option_id` AS `option_id`\n\t\t FROM `profile_options` AS `p`\n\t\t LEFT JOIN `user_options` AS `u` ON `p`.`id`=`u`.`option_id`\n\t\t LEFT JOIN `sys_trans` AS `st` ON `st`.`id`=`p`.`trans_id` AND `st`.`text`=`p`.`name`\n\t\t LEFT JOIN `sys_trans_text` AS `tt` ON `st`.`id`=`tt`.`trans_id` AND `tt`.`lang` = '&2'\n\t\t WHERE `u`.`option_visible`=1\n\t\t AND `p`.`internal_use`=0\n\t\t AND `u`.`user_id`='&1'\n\t\t ORDER BY `p`.`option_order`", $userid, $opt['template']['locale']);
$tpl->assign_rs('useroptions', $rs);
sql_free_result($rs);
$user_desc = sql_value("SELECT `description` FROM `user` WHERE `user_id`='&1'", "", $userid);
$user_desc = use_current_protocol_in_html($user_desc);
$tpl->assign('description', $user_desc);
$useropt = new useroptions($userid);
$show_statistics = $useropt->getOptValue(USR_OPT_SHOWSTATS);
$show_oconly81 = $useropt->getOptValue(USR_OPT_OCONLY81);
if ($show_oconly81) {
$tpl->assign('oco81_helplink', helppagelink('oconly81'));
}
if ($show_statistics) {
// detail statistics
$rs = sql("SELECT COUNT(*) AS `anzahl`, `t`.`id`, IFNULL(`tt`.`text`, `t`.`name`) AS `cachetype`\n\t\t\t FROM `caches` AS `c`\n\t\t\t LEFT JOIN `cache_type` AS `t` ON `t`.`id`=`c`.`type`\n\t\t\t LEFT JOIN `sys_trans` AS `st` ON `st`.`id`=`t`.`trans_id` AND `t`.`name`=`st`.`text`\n\t\t\t LEFT JOIN `sys_trans_text` AS `tt` ON `st`.`id`=`tt`.`trans_id` AND `tt`.`lang`='&2'\n\t\t\t LEFT JOIN `cache_status` ON `cache_status`.`id`=`c`.`status`\n\t\t\t WHERE `c`.`user_id`='&1' AND `allow_user_view`='1'\n\t\t\t GROUP BY `t`.`id`\n\t\t\t ORDER BY `anzahl` DESC, `t`.`ordinal` ASC", $userid, $opt['template']['locale']);
$tpl->assign_rs('userstatshidden', $rs);
sql_free_result($rs);
$rs = sql("SELECT COUNT(*) AS `anzahl`, `t`.`id`, IFNULL(`tt`.`text`, `t`.`name`) AS `cachetype`\n\t\t\t FROM `cache_logs` AS `l`\n\t\t\t LEFT JOIN `caches` AS `c` ON `l`.`cache_id`=`c`.`cache_id`\n\t\t\t LEFT JOIN `cache_type` AS `t` ON `t`.`id`=`c`.`type`\n\t\t\t LEFT JOIN `sys_trans` AS `st` ON `st`.`id`=`t`.`trans_id` AND `t`.`name`=`st`.`text`\n\t\t\t LEFT JOIN `sys_trans_text` AS `tt` ON `st`.`id`=`tt`.`trans_id` AND `tt`.`lang`='&2'\n\t\t\t WHERE `l`.`user_id`='&1' AND (`l`.`type`=1 OR `l`.`type`=7)\n\t\t\t GROUP BY `t`.`id`\n\t\t\t ORDER BY `anzahl` DESC, `t`.`ordinal` ASC", $userid, $opt['template']['locale']);
$tpl->assign_rs('userstatsfound', $rs);
sql_free_result($rs);
$digits = 1;
}
} else {
if ($rCache['waylength'] < 50) {
if (round($rCache['waylength'], 1) != round($rCache['waylength'], 0)) {
$digits = 1;
} else {
$digits = 0;
}
} else {
$digits = 0;
}
}
$rCache['waylength'] = sprintf('%.' . $digits . 'f', $rCache['waylength']);
// replace links
$rCache['desc'] = use_current_protocol_in_html($rCache['desc']);
$rCache['adminlog'] = !$rCache['log_allowed'] && $login->admin & ADMIN_USER;
$rs = sql("\n\t\tSELECT `short` `code`, `native_name`, `stt`.`text` AS `name`\n\t\tFROM `languages`\n\t\tJOIN `cache_desc` ON `cache_desc`.`language`=`languages`.`short`\n\t\tLEFT JOIN `sys_trans_text` `stt` ON `stt`.`trans_id`=`languages`.`trans_id` AND `stt`.`lang`='&2'\n\t\tWHERE `cache_desc`.`cache_id`='&1'", $cacheid, $opt['template']['locale']);
$desclanguages = sql_fetch_assoc_table($rs);
if (count($desclanguages) == 1 && $desclanguages[0]['code'] == $opt['template']['locale']) {
$rCache['desclanguages'] = array();
} else {
$rCache['desclanguages'] = $desclanguages;
}
$rCache['sizeName'] = labels::getLabelValue('cache_size', $rCache['size']);
$rCache['statusName'] = labels::getLabelValue('cache_status', $rCache['status']);
$rCache['typeName'] = labels::getLabelValue('cache_type', $rCache['type']);
$rCache['userhasfound'] = false;
if ($login->userid != 0) {
$rCache['userhasfound'] = sql_value("SELECT COUNT(*) FROM `cache_logs` WHERE `cache_id`='&1' AND `user_id`='&2' AND `type` IN (1,7)", 0, $cacheid, $login->userid) > 0;
}
static function getLogsArray($cacheid, $start, $count, $deleted = false)
{
global $login;
// negative or abornally high numbers like 1.0E+15 can crash the LIMIT selection
if ($count <= 0 || $count > 10000) {
return array();
}
//prepare the logs
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("\n\t\t\tSELECT `cache_logs`.`user_id` AS `userid`,\n\t\t\t\t`cache_logs`.`id` AS `id`,\n\t\t\t\t`cache_logs`.`uuid` AS `uuid`,\n\t\t\t\t`cache_logs`.`date` AS `date`,\n\t\t\t\tsubstr(`cache_logs`.`date`,12) AS `time`, /* 00:00:01 = 00:00 logged, 00:00:00 = no time */\n\t\t\t\t`cache_logs`.`type` AS `type`,\n\t\t\t\t`cache_logs`.`oc_team_comment` AS `oc_team_comment`,\n\t\t\t\t`cache_logs`.`text` AS `text`,\n\t\t\t\t`cache_logs`.`text_html` AS `texthtml`,\n\t\t\t\t`cache_logs`.`picture`,\n\t\t\t\t" . $delfields . ",\n\t\t\t\t`user`.`username` AS `username`,\n\t\t\t\tIF(ISNULL(`cache_rating`.`cache_id`), 0, `cache_logs`.`type` IN (1,7)) AS `recommended`\n\t\t\tFROM {$table} AS `cache_logs`\n\t\t\tINNER JOIN `user` ON `user`.`user_id` = `cache_logs`.`user_id`\n\t\t\tLEFT JOIN `cache_rating` ON `cache_logs`.`cache_id`=`cache_rating`.`cache_id` AND `cache_logs`.`user_id`=`cache_rating`.`user_id` AND `cache_logs`.`date`=`cache_rating`.`rating_date`\n\t\t\t" . $addjoin . "\n\t\t\tWHERE `cache_logs`.`cache_id`='&1'\n\t\t\tORDER BY `cache_logs`.`date` DESC, `cache_logs`.`date_created` DESC\n\t\t\tLIMIT &2, &3", $cacheid, $start + 0, $count + 0);
$logs = array();
while ($rLog = sql_fetch_assoc($rsLogs)) {
$pictures = array();
$rsPictures = sql("SELECT `url`, `title`, `uuid`, `id`, `spoiler` FROM `pictures` WHERE `object_id`='&1' AND `object_type`=1", $rLog['id']);
while ($rPicture = sql_fetch_assoc($rsPictures)) {
$pictures[] = $rPicture;
}
sql_free_result($rsPictures);
$rLog['pictures'] = $pictures;
$rLog['text'] = use_current_protocol_in_html($rLog['text']);
$logs[] = $rLog;
}
sql_free_result($rsLogs);
return $logs;
}
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;
}
private function getLists($condition, $prio = 0, $startat = 0, $maxitems = PHP_INT_MAX)
{
global $login;
$login->verify();
$rs = sql("\n\t\t\tSELECT `cache_lists`.`id`, `cache_lists`.`user_id`, `user`.`username`, \n\t\t\t `cache_lists`.`name`, `cache_lists`.`is_public` `visibility`, `cache_lists`.`password`, \n\t\t\t\t\t\t `cache_lists`.`description`, `cache_lists`.`desc_htmledit`,\n\t\t\t `cache_lists`.`user_id`='&1' `own_list`,\n\t\t\t `stat_cache_lists`.`entries`, `stat_cache_lists`.`watchers`,\n\t\t\t `w`.`user_id` IS NOT NULL `watched_by_me`,\n\t\t\t `b`.`user_id` IS NOT NULL `bookmarked`,\n\t\t\t {$prio} `prio`\n\t\t\tFROM `cache_lists`\n\t\t\tLEFT JOIN `stat_cache_lists` ON `stat_cache_lists`.`cache_list_id`=`cache_lists`.`id`\n\t\t\tLEFT JOIN `user` ON `user`.`user_id`=`cache_lists`.`user_id`\n\t\t\tLEFT JOIN `cache_list_watches` `w` ON `w`.`cache_list_id`=`cache_lists`.`id` AND `w`.`user_id`='&1'\n\t\t\tLEFT JOIN `cache_list_bookmarks` `b` ON `b`.`cache_list_id`=`cache_lists`.`id` AND `b`.`user_id`='&1'\n\t\t\tWHERE {$condition}\n\t\t\tORDER BY `prio`,`cache_lists`.`name`\n\t\t\tLIMIT &2,&3", $login->userid, $startat, $maxitems);
$lists = sql_fetch_assoc_table($rs);
foreach ($lists as &$list) {
$list['description_for_display'] = use_current_protocol_in_html($list['description']);
}
return $lists;
}
private static function getLists($condition, $prio = 0, $startat = 0, $maxitems = PHP_INT_MAX, $strip_nagchars = false)
{
global $login;
$login->verify();
$namefield = $strip_nagchars ? 'STRIP_LEADING_NONALNUM(`cache_lists`.`name`)' : '`cache_lists`.`name`';
$rs = sql("SELECT `cache_lists`.`id`, `cache_lists`.`user_id`, `user`.`username`,\n {$namefield} `name`,\n `cache_lists`.`is_public` `visibility`, `cache_lists`.`password`,\n `cache_lists`.`description`, `cache_lists`.`desc_htmledit`,\n `cache_lists`.`user_id`='&1' `own_list`,\n `stat_cache_lists`.`entries`, `stat_cache_lists`.`watchers`,\n `w`.`user_id` IS NOT NULL `watched_by_me`,\n `b`.`user_id` IS NOT NULL `bookmarked`,\n {$prio} `prio`\n FROM `cache_lists`\n LEFT JOIN `stat_cache_lists` ON `stat_cache_lists`.`cache_list_id`=`cache_lists`.`id`\n LEFT JOIN `user` ON `user`.`user_id`=`cache_lists`.`user_id`\n LEFT JOIN `cache_list_watches` `w` ON `w`.`cache_list_id`=`cache_lists`.`id` AND `w`.`user_id`='&1'\n LEFT JOIN `cache_list_bookmarks` `b` ON `b`.`cache_list_id`=`cache_lists`.`id` AND `b`.`user_id`='&1'\n WHERE {$condition}\n ORDER BY `prio`, {$namefield}\n LIMIT &2,&3", $login->userid, $startat, $maxitems);
$lists = sql_fetch_assoc_table($rs);
foreach ($lists as &$list) {
$list['description_for_display'] = use_current_protocol_in_html($list['description']);
}
return $lists;
}
