示例#1
0
文件: submit.php 项目: 4Vs/oc-server3
 /**
  * Publish a new log entry and return log entry uuid. Throws
  * CannotPublishException or BadRequest on errors.
  */
 private static function _call(OkapiRequest $request)
 {
     # Developers! Please notice the fundamental difference between throwing
     # CannotPublishException and standard BadRequest/InvalidParam exceptions!
     # Notice, that this is "_call" method, not the usual "call" (see below
     # for "call").
     $cache_code = $request->get_parameter('cache_code');
     if (!$cache_code) {
         throw new ParamMissing('cache_code');
     }
     $logtype = $request->get_parameter('logtype');
     if (!$logtype) {
         throw new ParamMissing('logtype');
     }
     if (!in_array($logtype, array('Found it', "Didn't find it", 'Comment', 'Will attend', 'Attended'))) {
         throw new InvalidParam('logtype', "'{$logtype}' in not a valid logtype code.");
     }
     $comment = $request->get_parameter('comment');
     if (!$comment) {
         $comment = "";
     }
     $comment_format = $request->get_parameter('comment_format');
     if (!$comment_format) {
         $comment_format = "auto";
     }
     if (!in_array($comment_format, array('auto', 'html', 'plaintext'))) {
         throw new InvalidParam('comment_format', $comment_format);
     }
     $tmp = $request->get_parameter('when');
     if ($tmp) {
         $when = strtotime($tmp);
         if (!$when) {
             throw new InvalidParam('when', "'{$tmp}' is not in a valid format or is not a valid date.");
         }
         if ($when > time() + 5 * 60) {
             throw new CannotPublishException(_("You are trying to publish a log entry with a date in future. " . "Cache log entries are allowed to be published in the past, but NOT in the future."));
         }
     } else {
         $when = time();
     }
     $on_duplicate = $request->get_parameter('on_duplicate');
     if (!$on_duplicate) {
         $on_duplicate = "silent_success";
     }
     if (!in_array($on_duplicate, array('silent_success', 'user_error', 'continue'))) {
         throw new InvalidParam('on_duplicate', "Unknown option: '{$on_duplicate}'.");
     }
     $rating = $request->get_parameter('rating');
     if ($rating !== null && !in_array($rating, array(1, 2, 3, 4, 5))) {
         throw new InvalidParam('rating', "If present, it must be an integer in the 1..5 scale.");
     }
     if ($rating && $logtype != 'Found it' && $logtype != 'Attended') {
         throw new BadRequest("Rating is allowed only for 'Found it' and 'Attended' logtypes.");
     }
     if ($rating !== null && Settings::get('OC_BRANCH') == 'oc.de') {
         # We will remove the rating request and change the success message
         # (which will be returned IF the rest of the query will meet all the
         # requirements).
         self::$success_message .= " " . sprintf(_("However, your cache rating was ignored, because %s does not have a rating system."), Okapi::get_normalized_site_name());
         $rating = null;
     }
     $recommend = $request->get_parameter('recommend');
     if (!$recommend) {
         $recommend = 'false';
     }
     if (!in_array($recommend, array('true', 'false'))) {
         throw new InvalidParam('recommend', "Unknown option: '{$recommend}'.");
     }
     $recommend = $recommend == 'true';
     if ($recommend && $logtype != 'Found it') {
         if ($logtype != 'Attended') {
             throw new BadRequest("Recommending is allowed only for 'Found it' and 'Attended' logs.");
         } else {
             if (Settings::get('OC_BRANCH') == 'oc.pl') {
                 # We will remove the recommendation request and change the success message
                 # (which will be returned IF the rest of the query will meet all the
                 # requirements).
                 self::$success_message .= " " . sprintf(_("However, your cache recommendation was ignored, because %s does not allow recommending event caches."), Okapi::get_normalized_site_name());
                 $recommend = null;
             }
         }
     }
     $needs_maintenance = $request->get_parameter('needs_maintenance');
     if (!$needs_maintenance) {
         $needs_maintenance = 'false';
     }
     if (!in_array($needs_maintenance, array('true', 'false'))) {
         throw new InvalidParam('needs_maintenance', "Unknown option: '{$needs_maintenance}'.");
     }
     $needs_maintenance = $needs_maintenance == 'true';
     if ($needs_maintenance && !Settings::get('SUPPORTS_LOGTYPE_NEEDS_MAINTENANCE')) {
         # If not supported, just ignore it.
         self::$success_message .= " " . sprintf(_("However, your \"needs maintenance\" flag was ignored, because %s does not support this feature."), Okapi::get_normalized_site_name());
         $needs_maintenance = false;
     }
     # Check if cache exists and retrieve cache internal ID (this will throw
     # a proper exception on invalid cache_code). Also, get the user object.
     $cache = OkapiServiceRunner::call('services/caches/geocache', new OkapiInternalRequest($request->consumer, null, array('cache_code' => $cache_code, 'fields' => 'internal_id|status|owner|type|req_passwd')));
     $user = OkapiServiceRunner::call('services/users/by_internal_id', new OkapiInternalRequest($request->consumer, $request->token, array('internal_id' => $request->token->user_id, 'fields' => 'is_admin|uuid|internal_id|caches_found|rcmds_given')));
     # Various integrity checks.
     if ($cache['type'] == 'Event') {
         if (!in_array($logtype, array('Will attend', 'Attended', 'Comment'))) {
             throw new CannotPublishException(_('This cache is an Event cache. You cannot "Find" it (but you can attend it, or comment on it)!'));
         }
     } else {
         if (in_array($logtype, array('Will attend', 'Attended'))) {
             throw new CannotPublishException(_('This cache is NOT an Event cache. You cannot "Attend" it (but you can find it, or comment on it)!'));
         } else {
             if (!in_array($logtype, array('Found it', "Didn't find it", 'Comment'))) {
                 throw new Exception("Unknown log entry - should be documented here.");
             }
         }
     }
     if ($logtype == 'Comment' && strlen(trim($comment)) == 0) {
         throw new CannotPublishException(_("Your have to supply some text for your comment."));
     }
     # Password check.
     if (($logtype == 'Found it' || $logtype == 'Attended') && $cache['req_passwd']) {
         $valid_password = Db::select_value("\n                select logpw\n                from caches\n                where cache_id = '" . mysql_real_escape_string($cache['internal_id']) . "'\n            ");
         $supplied_password = $request->get_parameter('password');
         if (!$supplied_password) {
             throw new CannotPublishException(_("This cache requires a password. You didn't provide one!"));
         }
         if (strtolower($supplied_password) != strtolower($valid_password)) {
             throw new CannotPublishException(_("Invalid password!"));
         }
     }
     # Prepare our comment to be inserted into the database. This may require
     # some reformatting which depends on the current OC installation.
     if (Settings::get('OC_BRANCH') == 'oc.de') {
         # OCDE stores all comments in HTML format, while the 'text_html' field
         # indicates their *original* format as delivered by the user. This
         # allows processing the 'text' field contents without caring about the
         # original format, while still being able to re-create the comment in
         # its original form. It requires us to HTML-encode plaintext comments
         # and to indicate this by setting 'html_text' to FALSE.
         #
         # For user-supplied HTML comments, OCDE requires us to do additional
         # HTML purification prior to the insertion into the database.
         if ($comment_format == 'plaintext') {
             $formatted_comment = htmlspecialchars($comment, ENT_QUOTES);
             $formatted_comment = nl2br($formatted_comment);
             $value_for_text_html_field = 0;
         } else {
             if ($comment_format == 'auto') {
                 # 'Auto' is for backward compatibility. Before the "comment_format"
                 # was introduced, OKAPI used a weird format in between (it allowed
                 # HTML, but applied nl2br too).
                 $formatted_comment = nl2br($comment);
             } else {
                 $formatted_comment = $comment;
             }
             # NOTICE: We are including EXTERNAL OCDE library here! This
             # code does not belong to OKAPI!
             $opt['rootpath'] = $GLOBALS['rootpath'];
             $opt['html_purifier'] = Settings::get('OCDE_HTML_PURIFIER_SETTINGS');
             require_once $GLOBALS['rootpath'] . 'lib2/OcHTMLPurifier.class.php';
             $purifier = new \OcHTMLPurifier($opt);
             $formatted_comment = $purifier->purify($formatted_comment);
             $value_for_text_html_field = 1;
         }
     } else {
         # OCPL is even weirder. It also stores HTML-lized comments in the database
         # (it doesn't really matter if 'text_html' field is set to FALSE). OKAPI must
         # save it in HTML either way. However, escaping plain-text doesn't work!
         # If we put "&lt;b&gt;" in, it still gets converted to "<b>" before display!
         # NONE of this process is documented within OCPL code. OKAPI uses a dirty
         # "hack" to save PLAINTEXT comments (let us hope the hack will remain valid).
         #
         # OCPL doesn't require HTML purification prior to the database insertion.
         # HTML seems to be purified dynamically, before it is displayed.
         if ($comment_format == 'plaintext') {
             $formatted_comment = htmlspecialchars($comment, ENT_QUOTES);
             $formatted_comment = nl2br($formatted_comment);
             $formatted_comment = str_replace("&amp;", "&amp;#38;", $formatted_comment);
             $formatted_comment = str_replace("&lt;", "&amp;#60;", $formatted_comment);
             $formatted_comment = str_replace("&gt;", "&amp;#62;", $formatted_comment);
             $value_for_text_html_field = 0;
             // WRTODO: get rid of
         } elseif ($comment_format == 'auto') {
             $formatted_comment = nl2br($comment);
             $value_for_text_html_field = 1;
         } else {
             $formatted_comment = $comment;
             $value_for_text_html_field = 1;
         }
     }
     unset($comment);
     # Duplicate detection.
     if ($on_duplicate != 'continue') {
         # Attempt to find a log entry made by the same user, for the same cache, with
         # the same date, type, comment, etc. Note, that these are not ALL the fields
         # we could check, but should work ok in most cases. Also note, that we
         # DO NOT guarantee that duplicate detection will succeed. If it doesn't,
         # nothing bad happens (user will just post two similar log entries).
         # Keep this simple!
         $duplicate_uuid = Db::select_value("\n                select uuid\n                from cache_logs\n                where\n                    user_id = '" . mysql_real_escape_string($request->token->user_id) . "'\n                    and cache_id = '" . mysql_real_escape_string($cache['internal_id']) . "'\n                    and type = '" . mysql_real_escape_string(Okapi::logtypename2id($logtype)) . "'\n                    and date = from_unixtime('" . mysql_real_escape_string($when) . "')\n                    and text = '" . mysql_real_escape_string($formatted_comment) . "'\n                    " . (Settings::get('OC_BRANCH') == 'oc.pl' ? "and deleted = 0" : "") . "\n                limit 1\n            ");
         if ($duplicate_uuid != null) {
             if ($on_duplicate == 'silent_success') {
                 # Act as if the log has been submitted successfully.
                 return $duplicate_uuid;
             } elseif ($on_duplicate == 'user_error') {
                 throw new CannotPublishException(_("You have already submitted a log entry with exactly the same contents."));
             }
         }
     }
     # Check if already found it (and make sure the user is not the owner).
     #
     # OCPL forbids logging 'Found it' or "Didn't find" for an already found cache,
     # while OCDE allows all kinds of duplicate logs.
     if (Settings::get('OC_BRANCH') == 'oc.pl' && ($logtype == 'Found it' || $logtype == "Didn't find it")) {
         $has_already_found_it = Db::select_value("\n                select 1\n                from cache_logs\n                where\n                    user_id = '" . mysql_real_escape_string($user['internal_id']) . "'\n                    and cache_id = '" . mysql_real_escape_string($cache['internal_id']) . "'\n                    and type = '" . mysql_real_escape_string(Okapi::logtypename2id("Found it")) . "'\n                    and " . (Settings::get('OC_BRANCH') == 'oc.pl' ? "deleted = 0" : "true") . "\n            ");
         if ($has_already_found_it) {
             throw new CannotPublishException(_("You have already submitted a \"Found it\" log entry once. Now you may submit \"Comments\" only!"));
         }
         if ($user['uuid'] == $cache['owner']['uuid']) {
             throw new CannotPublishException(_("You are the owner of this cache. You may submit \"Comments\" only!"));
         }
     }
     # Check if the user has already rated the cache. BTW: I don't get this one.
     # If we already know, that the cache was NOT found yet, then HOW could the
     # user submit a rating for it? Anyway, I will stick to the procedure
     # found in log.php. On the bright side, it's fail-safe.
     if ($rating) {
         $has_already_rated = Db::select_value("\n                select 1\n                from scores\n                where\n                    user_id = '" . mysql_real_escape_string($user['internal_id']) . "'\n                    and cache_id = '" . mysql_real_escape_string($cache['internal_id']) . "'\n            ");
         if ($has_already_rated) {
             throw new CannotPublishException(_("You have already rated this cache once. Your rating cannot be changed."));
         }
     }
     # If user wants to recommend...
     if ($recommend) {
         # Do the same "fail-safety" check as we did for the rating.
         $already_recommended = Db::select_value("\n                select 1\n                from cache_rating\n                where\n                    user_id = '" . mysql_real_escape_string($user['internal_id']) . "'\n                    and cache_id = '" . mysql_real_escape_string($cache['internal_id']) . "'\n            ");
         if ($already_recommended) {
             throw new CannotPublishException(_("You have already recommended this cache once."));
         }
         # Check the number of recommendations.
         $founds = $user['caches_found'] + 1;
         // +1, because he'll find THIS ONE in a moment, right?
         # Note: caches_found includes event attendance on both, OCDE and OCPL.
         # Though OCPL does not allow recommending events, for each 10 event
         # attendances the user may recommend a non-event cache.
         $rcmds_left = floor($founds / 10.0) - $user['rcmds_given'];
         if ($rcmds_left <= 0) {
             throw new CannotPublishException(_("You don't have any recommendations to give. Find more caches first!"));
         }
     }
     # If user checked the "needs_maintenance" flag, we will shuffle things a little...
     if ($needs_maintenance) {
         # If we're here, then we also know that the "Needs maintenance" log type is supported
         # by this OC site. However, it's a separate log type, so we might have to submit
         # two log types together:
         if ($logtype == 'Comment') {
             # If user submits a "Comment", we'll just change its type to "Needs maintenance".
             # Only one log entry will be issued.
             $logtype = 'Needs maintenance';
             $second_logtype = null;
             $second_formatted_comment = null;
         } elseif ($logtype == 'Found it') {
             # If "Found it", then we'll issue two log entries: one "Found it" with the
             # original comment, and second one "Needs maintenance" with empty comment.
             $second_logtype = 'Needs maintenance';
             $second_formatted_comment = "";
         } elseif ($logtype == "Didn't find it") {
             # If "Didn't find it", then we'll issue two log entries, but this time
             # we'll do this the other way around. The first "Didn't find it" entry
             # will have an empty comment. We will move the comment to the second
             # "Needs maintenance" log entry. (It's okay for this behavior to change
             # in the future, but it seems natural to me.)
             $second_logtype = 'Needs maintenance';
             $second_formatted_comment = $formatted_comment;
             $formatted_comment = "";
         } else {
             if ($logtype == 'Will attend' || $logtype == 'Attended') {
                 # OC branches which know maintenance logs do not allow them on event caches.
                 throw new CannotPublishException(_("Event caches cannot \"need maintenance\"."));
             } else {
                 throw new Exception();
             }
         }
     } else {
         # User didn't check the "Needs maintenance" flag OR "Needs maintenance" log type
         # isn't supported by this server.
         $second_logtype = null;
         $second_formatted_comment = null;
     }
     # Finally! Insert the rows into the log entries table. Update
     # cache stats and user stats.
     $log_uuid = self::insert_log_row($request->consumer->key, $cache['internal_id'], $user['internal_id'], $logtype, $when, $formatted_comment, $value_for_text_html_field);
     self::increment_cache_stats($cache['internal_id'], $when, $logtype);
     self::increment_user_stats($user['internal_id'], $logtype);
     if ($second_logtype != null) {
         # Reminder: This will never be called while SUPPORTS_LOGTYPE_NEEDS_MAINTENANCE is off.
         self::insert_log_row($request->consumer->key, $cache['internal_id'], $user['internal_id'], $second_logtype, $when + 1, $second_formatted_comment, $value_for_text_html_field);
         self::increment_cache_stats($cache['internal_id'], $when + 1, $second_logtype);
         self::increment_user_stats($user['internal_id'], $second_logtype);
     }
     # Save the rating.
     if ($rating) {
         # This code will be called for OCPL branch only. Earlier, we made sure,
         # to set $rating to null, if we're running on OCDE.
         # OCPL has a little strange way of storing cumulative rating. Instead
         # of storing the sum of all ratings, OCPL stores the computed average
         # and update it using multiple floating-point operations. Moreover,
         # the "score" field in the database is on the -3..3 scale (NOT 1..5),
         # and the translation made at retrieval time is DIFFERENT than the
         # one made here (both of them are non-linear). Also, once submitted,
         # the rating can never be changed. It surely feels quite inconsistent,
         # but presumably has some deep logic into it. See also here (Polish):
         # http://wiki.opencaching.pl/index.php/Oceny_skrzynek
         switch ($rating) {
             case 1:
                 $db_score = -2.0;
                 break;
             case 2:
                 $db_score = -0.5;
                 break;
             case 3:
                 $db_score = 0.7;
                 break;
             case 4:
                 $db_score = 1.7;
                 break;
             case 5:
                 $db_score = 3.0;
                 break;
             default:
                 throw new Exception();
         }
         Db::execute("\n                update caches\n                set\n                    score = (score*votes + '" . mysql_real_escape_string($db_score) . "')/(votes + 1),\n                    votes = votes + 1\n                where cache_id = '" . mysql_real_escape_string($cache['internal_id']) . "'\n            ");
         Db::execute("\n                insert into scores (user_id, cache_id, score)\n                values (\n                    '" . mysql_real_escape_string($user['internal_id']) . "',\n                    '" . mysql_real_escape_string($cache['internal_id']) . "',\n                    '" . mysql_real_escape_string($db_score) . "'\n                );\n            ");
     }
     # Save recommendation.
     if ($recommend) {
         if (Db::field_exists('cache_rating', 'rating_date')) {
             Db::execute("\n                    insert into cache_rating (user_id, cache_id, rating_date)\n                    values (\n                        '" . mysql_real_escape_string($user['internal_id']) . "',\n                        '" . mysql_real_escape_string($cache['internal_id']) . "',\n                        from_unixtime('" . mysql_real_escape_string($when) . "')\n                    );\n                ");
         } else {
             Db::execute("\n                    insert into cache_rating (user_id, cache_id)\n                    values (\n                        '" . mysql_real_escape_string($user['internal_id']) . "',\n                        '" . mysql_real_escape_string($cache['internal_id']) . "'\n                    );\n                ");
         }
     }
     # We need to delete the copy of stats-picture for this user. Otherwise,
     # the legacy OC code won't detect that the picture needs to be refreshed.
     $filepath = Okapi::get_var_dir() . '/images/statpics/statpic' . $user['internal_id'] . '.jpg';
     if (file_exists($filepath)) {
         unlink($filepath);
     }
     # Success. Return the uuid.
     return $log_uuid;
 }
示例#2
0
 public static function call(OkapiRequest $request)
 {
     $cache_codes = $request->get_parameter('cache_codes');
     if ($cache_codes === null) {
         throw new ParamMissing('cache_codes');
     }
     if ($cache_codes === "") {
         # Issue 106 requires us to allow empty list of cache codes to be passed into this method.
         # All of the queries below have to be ready for $cache_codes to be empty!
         $cache_codes = array();
     } else {
         $cache_codes = explode("|", $cache_codes);
     }
     if (count($cache_codes) > 500 && !$request->skip_limits) {
         throw new InvalidParam('cache_codes', "Maximum allowed number of referenced " . "caches is 500. You provided " . count($cache_codes) . " cache codes.");
     }
     if (count($cache_codes) != count(array_unique($cache_codes))) {
         throw new InvalidParam('cache_codes', "Duplicate codes detected (make sure each cache is referenced only once).");
     }
     $langpref = $request->get_parameter('langpref');
     if (!$langpref) {
         $langpref = "en";
     }
     $langpref .= "|" . Settings::get('SITELANG');
     $langpref = explode("|", $langpref);
     $fields = $request->get_parameter('fields');
     if (!$fields) {
         $fields = "code|name|location|type|status";
     }
     $fields = explode("|", $fields);
     foreach ($fields as $field) {
         if (!in_array($field, self::$valid_field_names)) {
             throw new InvalidParam('fields', "'{$field}' is not a valid field code.");
         }
     }
     # Some fields need to be temporarily included whenever the "description"
     # or "attribution_note" field are included. That's a little ugly, but
     # helps performance and conforms to the DRY rule.
     $fields_to_remove_later = array();
     if (in_array('description', $fields) || in_array('descriptions', $fields) || in_array('short_description', $fields) || in_array('short_descriptions', $fields) || in_array('hint', $fields) || in_array('hints', $fields) || in_array('hint2', $fields) || in_array('hints2', $fields) || in_array('attribution_note', $fields)) {
         if (!in_array('owner', $fields)) {
             $fields[] = "owner";
             $fields_to_remove_later[] = "owner";
         }
         if (!in_array('internal_id', $fields)) {
             $fields[] = "internal_id";
             $fields_to_remove_later[] = "internal_id";
         }
     }
     $attribution_append = $request->get_parameter('attribution_append');
     if (!$attribution_append) {
         $attribution_append = 'full';
     }
     if (!in_array($attribution_append, array('none', 'static', 'full'))) {
         throw new InvalidParam('attribution_append');
     }
     $log_fields = $request->get_parameter('log_fields');
     if (!$log_fields) {
         $log_fields = "uuid|date|user|type|comment";
     }
     // validation is done on call
     $user_uuid = $request->get_parameter('user_uuid');
     if ($user_uuid != null) {
         $user_id = Db::select_value("select user_id from user where uuid='" . mysql_real_escape_string($user_uuid) . "'");
         if ($user_id == null) {
             throw new InvalidParam('user_uuid', "User not found.");
         }
         if ($request->token != null && $request->token->user_id != $user_id) {
             throw new InvalidParam('user_uuid', "User does not match the Access Token used.");
         }
     } elseif ($user_uuid == null && $request->token != null) {
         $user_id = $request->token->user_id;
     } else {
         $user_id = null;
     }
     $lpc = $request->get_parameter('lpc');
     if ($lpc === null) {
         $lpc = 10;
     }
     if ($lpc == 'all') {
         $lpc = null;
     } else {
         if (!is_numeric($lpc)) {
             throw new InvalidParam('lpc', "Invalid number: '{$lpc}'");
         }
         $lpc = intval($lpc);
         if ($lpc < 0) {
             throw new InvalidParam('lpc', "Must be a positive value.");
         }
     }
     if (in_array('distance', $fields) || in_array('bearing', $fields) || in_array('bearing2', $fields) || in_array('bearing3', $fields)) {
         $tmp = $request->get_parameter('my_location');
         if (!$tmp) {
             throw new BadRequest("When using 'distance' or 'bearing' fields, you have to supply 'my_location' parameter.");
         }
         $parts = explode('|', $tmp);
         if (count($parts) != 2) {
             throw new InvalidParam('my_location', "Expecting 2 pipe-separated parts, got " . count($parts) . ".");
         }
         foreach ($parts as &$part_ref) {
             if (!preg_match("/^-?[0-9]+(\\.?[0-9]*)\$/", $part_ref)) {
                 throw new InvalidParam('my_location', "'{$part_ref}' is not a valid float number.");
             }
             $part_ref = floatval($part_ref);
         }
         list($center_lat, $center_lon) = $parts;
         if ($center_lat > 90 || $center_lat < -90) {
             throw new InvalidParam('current_position', "Latitudes have to be within -90..90 range.");
         }
         if ($center_lon > 180 || $center_lon < -180) {
             throw new InvalidParam('current_position', "Longitudes have to be within -180..180 range.");
         }
     }
     if (Settings::get('OC_BRANCH') == 'oc.de') {
         # DE branch:
         # - Caches do not have ratings.
         # - Total numbers of founds and notfounds are kept in the "stat_caches" table.
         # - search_time and way_length are both round trip values and cannot be null;
         #     0 = not specified
         # - will-attend-count is stored in separate field
         $rs = Db::query("\n                select\n                    c.cache_id, c.name, c.longitude, c.latitude, c.listing_last_modified as last_modified,\n                    c.date_created, c.type, c.status, c.date_hidden, c.size, c.difficulty,\n                    c.terrain, c.wp_oc, c.wp_gc, c.logpw, c.user_id,\n                    if(c.search_time=0, null, c.search_time) as trip_time,\n                    if(c.way_length=0, null, c.way_length) as trip_distance,\n\n                    ifnull(sc.toprating, 0) as topratings,\n                    ifnull(sc.found, 0) as founds,\n                    ifnull(sc.notfound, 0) as notfounds,\n                    ifnull(sc.will_attend, 0) as willattends,\n                    sc.last_found,\n                    0 as votes, 0 as score\n                    -- SEE ALSO OC.PL BRANCH BELOW\n                from\n                    caches c\n                    left join stat_caches as sc on c.cache_id = sc.cache_id\n                where\n                    wp_oc in ('" . implode("','", array_map('mysql_real_escape_string', $cache_codes)) . "')\n                    and status in (1,2,3)\n            ");
     } elseif (Settings::get('OC_BRANCH') == 'oc.pl') {
         # PL branch:
         # - Caches have ratings.
         # - Total numbers of found and notfounds are kept in the "caches" table.
         # - search_time is round trip and way_length one way or both ways (this is different on OCDE!);
         #   both can be null; 0 or null = not specified
         # - will-attend-count is stored in caches.notfounds
         $rs = Db::query("\n                select\n                    c.cache_id, c.name, c.longitude, c.latitude, c.last_modified,\n                    c.date_created, c.type, c.status, c.date_hidden, c.size, c.difficulty,\n                    c.terrain, c.wp_oc, c.wp_gc, c.logpw, c.user_id,\n                    if(c.search_time=0, null, c.search_time) as trip_time,\n                    if(c.way_length=0, null, c.way_length) as trip_distance,\n\n                    c.topratings,\n                    c.founds,\n                    c.notfounds,\n                    c.notfounds as willattends,\n                    c.last_found,\n                    c.votes, c.score\n                    -- SEE ALSO OC.DE BRANCH ABOVE\n                from\n                    caches c\n                where\n                    wp_oc in ('" . implode("','", array_map('mysql_real_escape_string', $cache_codes)) . "')\n                    and c.status in (1,2,3)\n            ");
     }
     $results = new ArrayObject();
     $cacheid2wptcode = array();
     $owner_ids = array();
     while ($row = mysql_fetch_assoc($rs)) {
         $entry = array();
         $cacheid2wptcode[$row['cache_id']] = $row['wp_oc'];
         foreach ($fields as $field) {
             switch ($field) {
                 case 'code':
                     $entry['code'] = $row['wp_oc'];
                     break;
                 case 'gc_code':
                     // OC software allows entering anything here, and that's what users do.
                     // We do a formal verification so that only a valid GC code is returned:
                     if (preg_match('/^\\s*[Gg][Cc][A-Za-z0-9]+\\s*$/', $row['wp_gc'])) {
                         $entry['gc_code'] = strtoupper(trim($row['wp_gc']));
                     } else {
                         $entry['gc_code'] = null;
                     }
                     break;
                 case 'name':
                     $entry['name'] = $row['name'];
                     break;
                 case 'names':
                     $entry['names'] = array(Settings::get('SITELANG') => $row['name']);
                     break;
                     // for the future
                 // for the future
                 case 'location':
                     $entry['location'] = round($row['latitude'], 6) . "|" . round($row['longitude'], 6);
                     break;
                 case 'type':
                     $entry['type'] = Okapi::cache_type_id2name($row['type']);
                     break;
                 case 'status':
                     $entry['status'] = Okapi::cache_status_id2name($row['status']);
                     break;
                 case 'url':
                     $entry['url'] = Settings::get('SITE_URL') . "viewcache.php?wp=" . $row['wp_oc'];
                     break;
                 case 'owner':
                     $owner_ids[$row['wp_oc']] = $row['user_id'];
                     /* continued later */
                     break;
                 case 'distance':
                     $entry['distance'] = (int) Okapi::get_distance($center_lat, $center_lon, $row['latitude'], $row['longitude']);
                     break;
                 case 'bearing':
                     $tmp = Okapi::get_bearing($center_lat, $center_lon, $row['latitude'], $row['longitude']);
                     $entry['bearing'] = $tmp !== null ? (int) (10 * $tmp) / 10.0 : null;
                     break;
                 case 'bearing2':
                     $tmp = Okapi::get_bearing($center_lat, $center_lon, $row['latitude'], $row['longitude']);
                     $entry['bearing2'] = Okapi::bearing_as_two_letters($tmp);
                     break;
                 case 'bearing3':
                     $tmp = Okapi::get_bearing($center_lat, $center_lon, $row['latitude'], $row['longitude']);
                     $entry['bearing3'] = Okapi::bearing_as_three_letters($tmp);
                     break;
                 case 'is_found':
                     /* handled separately */
                     break;
                 case 'is_not_found':
                     /* handled separately */
                     break;
                 case 'is_watched':
                     /* handled separately */
                     break;
                 case 'is_ignored':
                     /* handled separately */
                     break;
                 case 'founds':
                     $entry['founds'] = $row['founds'] + 0;
                     break;
                 case 'notfounds':
                     if ($row['type'] != 6) {
                         # non-event
                         $entry['notfounds'] = $row['notfounds'] + 0;
                     } else {
                         # event
                         $entry['notfounds'] = 0;
                     }
                     break;
                 case 'willattends':
                     if ($row['type'] == 6) {
                         # event
                         $entry['willattends'] = $row['willattends'] + 0;
                     } else {
                         # non-event
                         $entry['willattends'] = 0;
                     }
                     break;
                 case 'size':
                     # Deprecated. Leave it for backward-compatibility. See issue 155.
                     switch (Okapi::cache_sizeid_to_size2($row['size'])) {
                         case 'none':
                             $entry['size'] = null;
                             break;
                         case 'nano':
                             $entry['size'] = 1.0;
                             break;
                             # same as micro
                         # same as micro
                         case 'micro':
                             $entry['size'] = 1.0;
                             break;
                         case 'small':
                             $entry['size'] = 2.0;
                             break;
                         case 'regular':
                             $entry['size'] = 3.0;
                             break;
                         case 'large':
                             $entry['size'] = 4.0;
                             break;
                         case 'xlarge':
                             $entry['size'] = 5.0;
                             break;
                         case 'other':
                             $entry['size'] = null;
                             break;
                             # same as none
                         # same as none
                         default:
                             throw new Exception();
                     }
                     break;
                 case 'size2':
                     $entry['size2'] = Okapi::cache_sizeid_to_size2($row['size']);
                     break;
                 case 'oxsize':
                     $entry['oxsize'] = Okapi::cache_size2_to_oxsize(Okapi::cache_sizeid_to_size2($row['size']));
                     break;
                 case 'difficulty':
                     $entry['difficulty'] = round($row['difficulty'] / 2.0, 1);
                     break;
                 case 'terrain':
                     $entry['terrain'] = round($row['terrain'] / 2.0, 1);
                     break;
                 case 'trip_time':
                     # search time is entered in hours:minutes and converted to decimal hours,
                     # which can produce lots of unneeded decimal places; 2 of them are sufficient here
                     $entry['trip_time'] = $row['trip_time'] === null ? null : round($row['trip_time'], 2);
                     break;
                     break;
                 case 'trip_distance':
                     # way length is entered in km as decimal fraction, but number conversions can
                     # create fake digits which should be stripped; meter precision is sufficient here
                     $entry['trip_distance'] = $row['trip_distance'] === null ? null : round($row['trip_distance'], 3);
                     break;
                     break;
                 case 'rating':
                     if ($row['votes'] < 3) {
                         $entry['rating'] = null;
                     } elseif ($row['score'] >= 2.2) {
                         $entry['rating'] = 5.0;
                     } elseif ($row['score'] >= 1.4) {
                         $entry['rating'] = 4.0;
                     } elseif ($row['score'] >= 0.1) {
                         $entry['rating'] = 3.0;
                     } elseif ($row['score'] >= -1.0) {
                         $entry['rating'] = 2.0;
                     } else {
                         $entry['rating'] = 1.0;
                     }
                     break;
                 case 'rating_votes':
                     $entry['rating_votes'] = $row['votes'] + 0;
                     break;
                 case 'recommendations':
                     $entry['recommendations'] = $row['topratings'] + 0;
                     break;
                 case 'req_passwd':
                     $entry['req_passwd'] = $row['logpw'] ? true : false;
                     break;
                 case 'short_description':
                     /* handled separately */
                     break;
                 case 'short_descriptions':
                     /* handled separately */
                     break;
                 case 'description':
                     /* handled separately */
                     break;
                 case 'descriptions':
                     /* handled separately */
                     break;
                 case 'hint':
                     /* handled separately */
                     break;
                 case 'hints':
                     /* handled separately */
                     break;
                 case 'hint2':
                     /* handled separately */
                     break;
                 case 'hints2':
                     /* handled separately */
                     break;
                 case 'images':
                     /* handled separately */
                     break;
                 case 'preview_image':
                     /* handled separately */
                     break;
                 case 'attr_acodes':
                     /* handled separately */
                     break;
                 case 'attrnames':
                     /* handled separately */
                     break;
                 case 'latest_logs':
                     /* handled separately */
                     break;
                 case 'my_notes':
                     /* handles separately */
                     break;
                 case 'trackables_count':
                     /* handled separately */
                     break;
                 case 'trackables':
                     /* handled separately */
                     break;
                 case 'alt_wpts':
                     /* handled separately */
                     break;
                 case 'country':
                     /* handled separately */
                     break;
                 case 'state':
                     /* handled separately */
                     break;
                 case 'last_found':
                     $entry['last_found'] = $row['last_found'] > '1980' ? date('c', strtotime($row['last_found'])) : null;
                     break;
                 case 'last_modified':
                     $entry['last_modified'] = date('c', strtotime($row['last_modified']));
                     break;
                 case 'date_created':
                     $entry['date_created'] = date('c', strtotime($row['date_created']));
                     break;
                 case 'date_hidden':
                     $entry['date_hidden'] = date('c', strtotime($row['date_hidden']));
                     break;
                 case 'internal_id':
                     $entry['internal_id'] = $row['cache_id'];
                     break;
                 case 'attribution_note':
                     /* handled separately */
                     break;
                 case 'protection_areas':
                     /* handled separately */
                     break;
                 default:
                     throw new Exception("Missing field case: " . $field);
             }
         }
         $results[$row['wp_oc']] = $entry;
     }
     mysql_free_result($rs);
     # owner
     if (in_array('owner', $fields) && count($results) > 0) {
         $rs = Db::query("\n                select user_id, uuid, username\n                from user\n                where user_id in ('" . implode("','", array_map('mysql_real_escape_string', array_values($owner_ids))) . "')\n            ");
         $tmp = array();
         while ($row = mysql_fetch_assoc($rs)) {
             $tmp[$row['user_id']] = $row;
         }
         foreach ($results as $cache_code => &$result_ref) {
             $row = $tmp[$owner_ids[$cache_code]];
             $result_ref['owner'] = array('uuid' => $row['uuid'], 'username' => $row['username'], 'profile_url' => Settings::get('SITE_URL') . "viewprofile.php?userid=" . $row['user_id']);
         }
     }
     # is_found
     if (in_array('is_found', $fields)) {
         if ($user_id == null) {
             throw new BadRequest("Either 'user_uuid' parameter OR Level 3 Authentication is required to access 'is_found' field.");
         }
         $tmp = Db::select_column("\n                select c.wp_oc\n                from\n                    caches c,\n                    cache_logs cl\n                where\n                    c.cache_id = cl.cache_id\n                    and cl.type in (\n                        '" . mysql_real_escape_string(Okapi::logtypename2id("Found it")) . "',\n                        '" . mysql_real_escape_string(Okapi::logtypename2id("Attended")) . "'\n                    )\n                    and cl.user_id = '" . mysql_real_escape_string($user_id) . "'\n                    " . (Settings::get('OC_BRANCH') == 'oc.pl' ? "and cl.deleted = 0" : "") . "\n            ");
         $tmp2 = array();
         foreach ($tmp as $cache_code) {
             $tmp2[$cache_code] = true;
         }
         foreach ($results as $cache_code => &$result_ref) {
             $result_ref['is_found'] = isset($tmp2[$cache_code]);
         }
     }
     # is_not_found
     if (in_array('is_not_found', $fields)) {
         if ($user_id == null) {
             throw new BadRequest("Either 'user_uuid' parameter OR Level 3 Authentication is required to access 'is_not_found' field.");
         }
         $tmp = Db::select_column("\n                select c.wp_oc\n                from\n                    caches c,\n                    cache_logs cl\n                where\n                    c.cache_id = cl.cache_id\n                    and cl.type = '" . mysql_real_escape_string(Okapi::logtypename2id("Didn't find it")) . "'\n                    and cl.user_id = '" . mysql_real_escape_string($user_id) . "'\n                    " . (Settings::get('OC_BRANCH') == 'oc.pl' ? "and cl.deleted = 0" : "") . "\n            ");
         $tmp2 = array();
         foreach ($tmp as $cache_code) {
             $tmp2[$cache_code] = true;
         }
         foreach ($results as $cache_code => &$result_ref) {
             $result_ref['is_not_found'] = isset($tmp2[$cache_code]);
         }
     }
     # is_watched
     if (in_array('is_watched', $fields)) {
         if ($request->token == null) {
             throw new BadRequest("Level 3 Authentication is required to access 'is_watched' field.");
         }
         $tmp = Db::select_column("\n                select c.wp_oc\n                from\n                    caches c,\n                    cache_watches cw\n                where\n                    c.cache_id = cw.cache_id\n                    and cw.user_id = '" . mysql_real_escape_string($request->token->user_id) . "'\n            ");
         $tmp2 = array();
         foreach ($tmp as $cache_code) {
             $tmp2[$cache_code] = true;
         }
         # OCDE caches can also be indirectly watched by watching cache lists:
         if (Settings::get('OC_BRANCH') == 'oc.de') {
             $tmp = Db::select_column("\n                  select c.wp_oc\n                  from\n                      caches c,\n                      cache_list_items cli,\n                      cache_list_watches clw\n                  where\n                      cli.cache_id = c.cache_id\n                      and clw.cache_list_id = cli.cache_list_id\n                      and clw.user_id = '" . mysql_real_escape_string($request->token->user_id) . "'\n              ");
             foreach ($tmp as $cache_code) {
                 $tmp2[$cache_code] = true;
             }
         }
         foreach ($results as $cache_code => &$result_ref) {
             $result_ref['is_watched'] = isset($tmp2[$cache_code]);
         }
     }
     # is_ignored
     if (in_array('is_ignored', $fields)) {
         if ($request->token == null) {
             throw new BadRequest("Level 3 Authentication is required to access 'is_ignored' field.");
         }
         $tmp = Db::select_column("\n                select c.wp_oc\n                from\n                    caches c,\n                    cache_ignore ci\n                where\n                    c.cache_id = ci.cache_id\n                    and ci.user_id = '" . mysql_real_escape_string($request->token->user_id) . "'\n            ");
         $tmp2 = array();
         foreach ($tmp as $cache_code) {
             $tmp2[$cache_code] = true;
         }
         foreach ($results as $cache_code => &$result_ref) {
             $result_ref['is_ignored'] = isset($tmp2[$cache_code]);
         }
     }
     # Descriptions and hints.
     if (in_array('description', $fields) || in_array('descriptions', $fields) || in_array('short_description', $fields) || in_array('short_descriptions', $fields) || in_array('hint', $fields) || in_array('hints', $fields) || in_array('hint2', $fields) || in_array('hints2', $fields)) {
         # At first, we will fill all those fields, even if user requested just one
         # of them. We will chop off the unwanted ones at the end.
         foreach ($results as &$result_ref) {
             $result_ref['short_descriptions'] = new ArrayObject();
             $result_ref['descriptions'] = new ArrayObject();
             $result_ref['hints'] = new ArrayObject();
             $result_ref['hints2'] = new ArrayObject();
         }
         # Get cache descriptions and hints.
         $rs = Db::query("\n                select cache_id, language, `desc`, short_desc, hint\n                from cache_desc\n                where cache_id in ('" . implode("','", array_map('mysql_real_escape_string', array_keys($cacheid2wptcode))) . "')\n            ");
         while ($row = mysql_fetch_assoc($rs)) {
             $cache_code = $cacheid2wptcode[$row['cache_id']];
             // strtolower - ISO 639-1 codes are lowercase
             if ($row['desc']) {
                 /* Note, that the "owner" and "internal_id" fields are automatically included,
                  * whenever the cache description is included. */
                 $tmp = Okapi::fix_oc_html($row['desc']);
                 if ($attribution_append != 'none') {
                     $tmp .= "\n<p><em>" . self::get_cache_attribution_note($row['cache_id'], strtolower($row['language']), $langpref, $results[$cache_code]['owner'], $attribution_append) . "</em></p>";
                 }
                 $results[$cache_code]['descriptions'][strtolower($row['language'])] = $tmp;
             }
             if ($row['short_desc']) {
                 $results[$cache_code]['short_descriptions'][strtolower($row['language'])] = $row['short_desc'];
             }
             if ($row['hint']) {
                 $results[$cache_code]['hints'][strtolower($row['language'])] = $row['hint'];
                 $results[$cache_code]['hints2'][strtolower($row['language'])] = htmlspecialchars_decode(mb_ereg_replace("<br />", "", $row['hint']), ENT_COMPAT);
             }
         }
         foreach ($results as &$result_ref) {
             $result_ref['short_description'] = Okapi::pick_best_language($result_ref['short_descriptions'], $langpref);
             $result_ref['description'] = Okapi::pick_best_language($result_ref['descriptions'], $langpref);
             $result_ref['hint'] = Okapi::pick_best_language($result_ref['hints'], $langpref);
             $result_ref['hint2'] = Okapi::pick_best_language($result_ref['hints2'], $langpref);
         }
         # Remove unwanted fields.
         foreach (array('short_description', 'short_descriptions', 'description', 'descriptions', 'hint', 'hints', 'hint2', 'hints2') as $field) {
             if (!in_array($field, $fields)) {
                 foreach ($results as &$result_ref) {
                     unset($result_ref[$field]);
                 }
             }
         }
     }
     # Images.
     if (in_array('images', $fields) || in_array('preview_image', $fields)) {
         if (in_array('images', $fields)) {
             foreach ($results as &$result_ref) {
                 $result_ref['images'] = array();
             }
         }
         if (in_array('preview_image', $fields)) {
             foreach ($results as &$result_ref) {
                 $result_ref['preview_image'] = null;
             }
         }
         if (Db::field_exists('pictures', 'mappreview')) {
             $preview_field = "mappreview";
         } else {
             $preview_field = "0";
         }
         $sql = "\n                select object_id, uuid, url, title, spoiler, " . $preview_field . " as preview\n                from pictures\n                where\n                    object_id in ('" . implode("','", array_map('mysql_real_escape_string', array_keys($cacheid2wptcode))) . "')\n                    and display = 1\n                    and object_type = 2\n                    and unknown_format = 0\n            ";
         if (Settings::get('OC_BRANCH') == 'oc.pl') {
             // oc.pl installation allows arbitrary order of the geocache's images
             $sql .= "order by object_id, seq, date_created";
         } else {
             $sql .= "order by object_id, date_created";
         }
         $rs = Db::query($sql);
         unset($sql);
         $prev_cache_code = null;
         while ($row = mysql_fetch_assoc($rs)) {
             $cache_code = $cacheid2wptcode[$row['object_id']];
             if ($cache_code != $prev_cache_code) {
                 # Group images together. Images must have unique captions within one cache.
                 self::reset_unique_captions();
                 $prev_cache_code = $cache_code;
             }
             if (Settings::get('OC_BRANCH') == 'oc.de') {
                 $object_type_param = 'type=2&';
             } else {
                 $object_type_param = '';
             }
             $image = array('uuid' => $row['uuid'], 'url' => $row['url'], 'thumb_url' => Settings::get('SITE_URL') . 'thumbs.php?' . $object_type_param . 'uuid=' . $row['uuid'], 'caption' => $row['title'], 'unique_caption' => self::get_unique_caption($row['title']), 'is_spoiler' => $row['spoiler'] ? true : false);
             if (in_array('images', $fields)) {
                 $results[$cache_code]['images'][] = $image;
             }
             if ($row['preview'] != 0 && in_array('preview_image', $fields)) {
                 $results[$cache_code]['preview_image'] = $image;
             }
         }
     }
     # A-codes and attrnames
     if (in_array('attr_acodes', $fields) || in_array('attrnames', $fields)) {
         # Either case, we'll need acodes. If the user didn't want them,
         # remember to remove them later.
         if (!in_array('attr_acodes', $fields)) {
             $fields_to_remove_later[] = 'attr_acodes';
         }
         foreach ($results as &$result_ref) {
             $result_ref['attr_acodes'] = array();
         }
         # Load internal_attr_id => acode mapping.
         require_once $GLOBALS['rootpath'] . 'okapi/services/attrs/attr_helper.inc.php';
         $internal2acode = AttrHelper::get_internal_id_to_acode_mapping();
         $rs = Db::query("\n                select cache_id, attrib_id\n                from caches_attributes\n                where cache_id in ('" . implode("','", array_map('mysql_real_escape_string', array_keys($cacheid2wptcode))) . "')\n            ");
         while ($row = mysql_fetch_assoc($rs)) {
             $cache_code = $cacheid2wptcode[$row['cache_id']];
             $attr_internal_id = $row['attrib_id'];
             if (!isset($internal2acode[$attr_internal_id])) {
                 # Unknown attribute. Ignore.
                 continue;
             }
             $results[$cache_code]['attr_acodes'][] = $internal2acode[$attr_internal_id];
         }
         # Now, each cache object has a list of its acodes. We can get
         # the attrnames now.
         if (in_array('attrnames', $fields)) {
             $acode2bestname = AttrHelper::get_acode_to_name_mapping($langpref);
             foreach ($results as &$result_ref) {
                 $result_ref['attrnames'] = array();
                 foreach ($result_ref['attr_acodes'] as $acode) {
                     $result_ref['attrnames'][] = $acode2bestname[$acode];
                 }
             }
         }
     }
     # Latest log entries.
     if (in_array('latest_logs', $fields)) {
         foreach ($results as &$result_ref) {
             $result_ref['latest_logs'] = array();
         }
         # Get all log IDs with dates. Sort in groups. Filter out latest ones. This is the fastest
         # technique I could think of...
         $rs = Db::query("\n                select cache_id, uuid, date\n                from cache_logs\n                where\n                    cache_id in ('" . implode("','", array_map('mysql_real_escape_string', array_keys($cacheid2wptcode))) . "')\n                    and " . (Settings::get('OC_BRANCH') == 'oc.pl' ? "deleted = 0" : "true") . "\n                order by cache_id, date desc, date_created desc\n            ");
         $loguuids = array();
         $log2cache_map = array();
         if ($lpc !== null) {
             # User wants some of the latest logs.
             $tmp = array();
             while ($row = mysql_fetch_assoc($rs)) {
                 $tmp[$row['cache_id']][] = $row;
             }
             foreach ($tmp as $cache_key => &$rowslist_ref) {
                 usort($rowslist_ref, function ($rowa, $rowb) {
                     # (reverse order by date)
                     return $rowa['date'] < $rowb['date'] ? 1 : ($rowa['date'] == $rowb['date'] ? 0 : -1);
                 });
                 for ($i = 0; $i < min(count($rowslist_ref), $lpc); $i++) {
                     $loguuids[] = $rowslist_ref[$i]['uuid'];
                     $log2cache_map[$rowslist_ref[$i]['uuid']] = $cacheid2wptcode[$rowslist_ref[$i]['cache_id']];
                 }
             }
         } else {
             # User wants ALL logs.
             while ($row = mysql_fetch_assoc($rs)) {
                 $loguuids[] = $row['uuid'];
                 $log2cache_map[$row['uuid']] = $cacheid2wptcode[$row['cache_id']];
             }
         }
         # We need to retrieve logs/entry for each of the $logids. We do this in groups
         # (there is a limit for log uuids passed to logs/entries method).
         try {
             foreach (Okapi::make_groups($loguuids, 500) as $subset) {
                 $entries = OkapiServiceRunner::call("services/logs/entries", new OkapiInternalRequest($request->consumer, $request->token, array('log_uuids' => implode("|", $subset), 'fields' => $log_fields)));
                 foreach ($subset as $log_uuid) {
                     if ($entries[$log_uuid]) {
                         $results[$log2cache_map[$log_uuid]]['latest_logs'][] = $entries[$log_uuid];
                     }
                 }
             }
         } catch (Exception $e) {
             if ($e instanceof InvalidParam && $e->paramName == 'fields') {
                 throw new InvalidParam('log_fields', $e->whats_wrong_about_it);
             } else {
                 /* Something is wrong with OUR code. */
                 throw new Exception($e);
             }
         }
     }
     # My notes
     if (in_array('my_notes', $fields)) {
         if ($request->token == null) {
             throw new BadRequest("Level 3 Authentication is required to access 'my_notes' field.");
         }
         foreach ($results as &$result_ref) {
             $result_ref['my_notes'] = null;
         }
         if (Settings::get('OC_BRANCH') == 'oc.pl') {
             # OCPL uses cache_notes table to store notes.
             $rs = Db::query("\n                    select cache_id, max(date) as date, group_concat(`desc`) as `desc`\n                    from cache_notes\n                    where\n                        cache_id in ('" . implode("','", array_map('mysql_real_escape_string', array_keys($cacheid2wptcode))) . "')\n                        and user_id = '" . mysql_real_escape_string($request->token->user_id) . "'\n                    group by cache_id\n                ");
         } else {
             # OCDE uses coordinates table (with type == 2) to store notes (this is somewhat weird).
             $rs = Db::query("\n                    select cache_id, null as date, group_concat(description) as `desc`\n                    from coordinates\n                    where\n                        type = 2  -- personal note\n                        and cache_id in ('" . implode("','", array_map('mysql_real_escape_string', array_keys($cacheid2wptcode))) . "')\n                        and user_id = '" . mysql_real_escape_string($request->token->user_id) . "'\n                    group by cache_id\n                ");
         }
         while ($row = mysql_fetch_assoc($rs)) {
             # This one is plain-text. We may add my_notes_html for those who want it in HTML.
             $results[$cacheid2wptcode[$row['cache_id']]]['my_notes'] = strip_tags($row['desc']);
         }
     }
     if (in_array('trackables', $fields)) {
         # Currently we support Geokrety only. But this interface should remain
         # compatible. In future, other trackables might be returned the same way.
         $rs = Db::query("\n                select\n                    gkiw.wp as cache_code,\n                    gki.id as gk_id,\n                    gki.name\n                from\n                    gk_item_waypoint gkiw,\n                    gk_item gki\n                where\n                    gkiw.id = gki.id\n                    and gkiw.wp in ('" . implode("','", array_map('mysql_real_escape_string', $cache_codes)) . "')\n            ");
         $trs = array();
         while ($row = mysql_fetch_assoc($rs)) {
             $trs[$row['cache_code']][] = $row;
         }
         foreach ($results as $cache_code => &$result_ref) {
             $result_ref['trackables'] = array();
             if (!isset($trs[$cache_code])) {
                 continue;
             }
             foreach ($trs[$cache_code] as $t) {
                 $result_ref['trackables'][] = array('code' => 'GK' . str_pad(strtoupper(dechex($t['gk_id'])), 4, "0", STR_PAD_LEFT), 'name' => $t['name'], 'url' => 'http://geokrety.org/konkret.php?id=' . $t['gk_id']);
             }
         }
         unset($trs);
     }
     if (in_array('trackables_count', $fields)) {
         if (in_array('trackables', $fields)) {
             # We already got all trackables data, no need to query database again.
             foreach ($results as $cache_code => &$result_ref) {
                 $result_ref['trackables_count'] = count($result_ref['trackables']);
             }
         } else {
             $rs = Db::query("\n                    select wp as cache_code, count(*) as count\n                    from gk_item_waypoint\n                    where wp in ('" . implode("','", array_map('mysql_real_escape_string', $cache_codes)) . "')\n                    group by wp\n                ");
             $tr_counts = new ArrayObject();
             while ($row = mysql_fetch_assoc($rs)) {
                 $tr_counts[$row['cache_code']] = $row['count'];
             }
             foreach ($results as $cache_code => &$result_ref) {
                 if (isset($tr_counts[$cache_code])) {
                     $result_ref['trackables_count'] = $tr_counts[$cache_code] + 0;
                 } else {
                     $result_ref['trackables_count'] = 0;
                 }
             }
             unset($tr_counts);
         }
     }
     # Alternate/Additional waypoints.
     if (in_array('alt_wpts', $fields)) {
         $internal_wpt_type_id2names = array();
         if (Settings::get('OC_BRANCH') == 'oc.de') {
             $rs = Db::query("\n                    select\n                        ct.id,\n                        LOWER(stt.lang) as language,\n                        stt.`text`\n                    from\n                        coordinates_type ct\n                        left join sys_trans_text stt on stt.trans_id = ct.trans_id\n                ");
             while ($row = mysql_fetch_assoc($rs)) {
                 $internal_wpt_type_id2names[$row['id']][$row['language']] = $row['text'];
             }
             mysql_free_result($rs);
         } else {
             $rs = Db::query("\n                    select id, pl, en\n                    from waypoint_type\n                    where id > 0\n                ");
             while ($row = mysql_fetch_assoc($rs)) {
                 $internal_wpt_type_id2names[$row['id']]['pl'] = $row['pl'];
                 $internal_wpt_type_id2names[$row['id']]['en'] = $row['en'];
             }
         }
         foreach ($results as &$result_ref) {
             $result_ref['alt_wpts'] = array();
         }
         $cache_codes_escaped_and_imploded = "'" . implode("','", array_map('mysql_real_escape_string', array_keys($cacheid2wptcode))) . "'";
         if (Settings::get('OC_BRANCH') == 'oc.pl') {
             # OCPL uses 'waypoints' table to store additional waypoints and defines
             # waypoint types in 'waypoint_type' table.
             # OCPL also have a special 'status' field to denote a hidden waypoint
             # (i.e. final location of a multicache). Such hidden waypoints are not
             # exposed by OKAPI.
             $cacheid2waypoints = Db::select_group_by("cache_id", "\n                    select\n                        cache_id, stage, latitude, longitude, `desc`,\n                        type as internal_type_id,\n                        case type\n                            when 3 then 'Flag, Red'\n                            when 4 then 'Circle with X'\n                            when 5 then 'Parking Area'\n                            else 'Flag, Green'\n                        end as sym,\n                        case type\n                            when 1 then 'physical-stage'\n                            when 2 then 'virtual-stage'\n                            when 3 then 'final'\n                            when 4 then 'poi'\n                            when 5 then 'parking'\n                            else 'other'\n                        end as okapi_type\n                    from waypoints\n                    where\n                        cache_id in (" . $cache_codes_escaped_and_imploded . ")\n                        and status = 1\n                    order by cache_id, stage, `desc`\n                ");
         } else {
             # OCDE uses 'coordinates' table (with type=1) to store additional waypoints
             # and defines waypoint types in 'coordinates_type' table.
             # All additional waypoints are public.
             $cacheid2waypoints = Db::select_group_by("cache_id", "\n                    select\n                        cache_id,\n                        false as stage,\n                        latitude, longitude,\n                        description as `desc`,\n                        subtype as internal_type_id,\n                        case subtype\n                            when 1 then 'Parking Area'\n                            when 3 then 'Flag, Blue'\n                            when 4 then 'Circle with X'\n                            when 5 then 'Diamond, Green'\n                            else 'Flag, Green'\n                        end as sym,\n                        case subtype\n                            when 1 then 'parking'\n                            when 2 then 'stage'\n                            when 3 then 'path'\n                            when 4 then 'final'\n                            when 5 then 'poi'\n                            else 'other'\n                        end as okapi_type\n                    from coordinates\n                    where\n                        type = 1\n                        and cache_id in (" . $cache_codes_escaped_and_imploded . ")\n                    order by cache_id, id\n                ");
         }
         foreach ($cacheid2waypoints as $cache_id => $waypoints) {
             $cache_code = $cacheid2wptcode[$cache_id];
             $wpt_format = $cache_code . "-%0" . strlen(count($waypoints)) . "d";
             $index = 0;
             foreach ($waypoints as $row) {
                 if (!isset($internal_wpt_type_id2names[$row['internal_type_id']])) {
                     # Sanity check. Waypoints of undefined type won't be accessible via OKAPI.
                     # See issue 219.
                     continue;
                 }
                 $index++;
                 $results[$cache_code]['alt_wpts'][] = array('name' => sprintf($wpt_format, $index), 'location' => round($row['latitude'], 6) . "|" . round($row['longitude'], 6), 'type' => $row['okapi_type'], 'type_name' => Okapi::pick_best_language($internal_wpt_type_id2names[$row['internal_type_id']], $langpref), 'sym' => $row['sym'], 'description' => ($row['stage'] ? _("Stage") . " " . $row['stage'] . ": " : "") . $row['desc']);
             }
         }
         # Issue #298 - User coordinates implemented in oc.pl
         # Issue #305 - User coordinates implemented in oc.de
         if ($request->token != null) {
             # Query DB for user provided coordinates
             if (Settings::get('OC_BRANCH') == 'oc.pl') {
                 $cacheid2user_coords = Db::select_group_by('cache_id', "\n                        select\n                            cache_id, longitude, latitude\n                        from cache_mod_cords\n                        where\n                            cache_id in ({$cache_codes_escaped_and_imploded})\n                            and user_id = '" . mysql_real_escape_string($request->token->user_id) . "'\n                    ");
             } else {
                 # oc.de
                 $cacheid2user_coords = Db::select_group_by('cache_id', "\n                        select\n                            cache_id, longitude, latitude\n                        from coordinates\n                        where\n                            cache_id in ({$cache_codes_escaped_and_imploded})\n                            and user_id = '" . mysql_real_escape_string($request->token->user_id) . "'\n                            and type = 2\n                            and longitude != 0\n                            and latitude != 0\n                    ");
             }
             foreach ($cacheid2user_coords as $cache_id => $waypoints) {
                 $cache_code = $cacheid2wptcode[$cache_id];
                 foreach ($waypoints as $row) {
                     # there should be only one user waypoint per cache...
                     $results[$cache_code]['alt_wpts'][] = array('name' => $cache_code . '-USER-COORDS', 'location' => round($row['latitude'], 6) . "|" . round($row['longitude'], 6), 'type' => 'user-coords', 'type_name' => _("User location"), 'sym' => 'Block, Green', 'description' => sprintf(_("Your own custom coordinates for the %s geocache"), $cache_code));
                 }
             }
         }
     }
     # Country and/or state.
     if (in_array('country', $fields) || in_array('state', $fields)) {
         $countries = array();
         $states = array();
         if (Settings::get('OC_BRANCH') == 'oc.de') {
             # OCDE:
             #  - cache_location entries are created by a cronjob *after* listing the
             #      caches and may not yet exist.
             #  - The state is in adm2 field.
             #  - caches.country overrides cache_location.code1/adm1. If both differ,
             #      cache_location.adm2 to adm4 is invalid and the state unknown.
             #  - OCDE databases may contain caches with invalid country code.
             #      Such errors must be handled gracefully.
             #  - adm1 should always be ignored. Instead, code1 should be translated
             #      into a country name, depending on langpref.
             # build country code translation table
             $rs = Db::query("\n                    select distinct\n                        c.country,\n                        lower(stt.lang) as language,\n                        stt.`text`\n                    from\n                        caches c\n                        inner join countries on countries.short=c.country\n                        inner join sys_trans_text stt on stt.trans_id = countries.trans_id\n                    where\n                        c.wp_oc in ('" . implode("','", array_map('mysql_real_escape_string', $cache_codes)) . "')\n                ");
             $country_codes2names = array();
             while ($row = mysql_fetch_assoc($rs)) {
                 $country_codes2names[$row['country']][$row['language']] = $row['text'];
             }
             mysql_free_result($rs);
             # get geocache countries and states
             $rs = Db::query("\n                    select\n                        c.wp_oc as cache_code,\n                        c.country as country_code,\n                        ifnull(if(c.country<>cl.code1,'',cl.adm2),'') as state\n                    from\n                        caches c\n                        left join cache_location cl on c.cache_id = cl.cache_id\n                    where\n                        c.wp_oc in ('" . implode("','", array_map('mysql_real_escape_string', $cache_codes)) . "')\n                ");
             while ($row = mysql_fetch_assoc($rs)) {
                 if (!isset($country_codes2names[$row['country_code']])) {
                     $countries[$row['cache_code']] = '';
                 } else {
                     $countries[$row['cache_code']] = Okapi::pick_best_language($country_codes2names[$row['country_code']], $langpref);
                 }
                 $states[$row['cache_code']] = $row['state'];
             }
             mysql_free_result($rs);
         } else {
             # OCPL:
             #  - cache_location data is entered by the user.
             #  - The state is in adm3 field.
             # get geocache countries and states
             $rs = Db::query("\n                    select\n                        c.wp_oc as cache_code,\n                        cl.adm1 as country,\n                        cl.adm3 as state\n                    from\n                        caches c,\n                        cache_location cl\n                    where\n                        c.wp_oc in ('" . implode("','", array_map('mysql_real_escape_string', $cache_codes)) . "')\n                        and c.cache_id = cl.cache_id\n                ");
             while ($row = mysql_fetch_assoc($rs)) {
                 $countries[$row['cache_code']] = $row['country'];
                 $states[$row['cache_code']] = $row['state'];
             }
             mysql_free_result($rs);
         }
         if (in_array('country', $fields)) {
             foreach ($results as $cache_code => &$row_ref) {
                 $row_ref['country'] = isset($countries[$cache_code]) ? $countries[$cache_code] : null;
             }
         }
         if (in_array('state', $fields)) {
             foreach ($results as $cache_code => &$row_ref) {
                 $row_ref['state'] = isset($states[$cache_code]) ? $states[$cache_code] : null;
             }
         }
         unset($countries);
         unset($states);
     }
     # Attribution note
     if (in_array('attribution_note', $fields)) {
         /* Note, that the "owner" and "internal_id" fields are automatically included,
          * whenever the attribution_note is included. */
         foreach ($results as $cache_code => &$result_ref) {
             $result_ref['attribution_note'] = self::get_cache_attribution_note($result_ref['internal_id'], $langpref[0], $langpref, $results[$cache_code]['owner'], 'full');
         }
     }
     # Protection areas
     if (in_array('protection_areas', $fields)) {
         $cache_ids_escaped_and_imploded = "'" . implode("','", array_map('mysql_real_escape_string', array_keys($cacheid2wptcode))) . "'";
         if (Settings::get('OC_BRANCH') == 'oc.de') {
             $rs = Db::query("\n                    select\n                        c.wp_oc as cache_code,\n                        npa_types.name as type,\n                        npa_areas.name as name\n                    from\n                        caches c\n                        inner join cache_npa_areas on cache_npa_areas.cache_id=c.cache_id\n                        inner join npa_areas on cache_npa_areas.npa_id = npa_areas.id\n                        inner join npa_types on npa_areas.type_id = npa_types.id\n                    where\n                        c.cache_id in (" . $cache_ids_escaped_and_imploded . ")\n                    group by npa_areas.type_id, npa_areas.name\n                    order by npa_types.ordinal\n                ");
         } else {
             if (Settings::get('ORIGIN_URL') == 'http://opencaching.pl/' || Settings::get('ORIGIN_URL') == 'http://www.opencaching.nl/') {
                 # Current OCPL table definitions use collation 'latin1' for parkipl
                 # and 'utf8' for np_areas. Union needs identical collations.
                 # To be sure, we convert both to utf8.
                 #
                 # TODO: use DB_CHARSET setting instead of literal 'utf8'
                 $rs = Db::query("\n                    select\n                        c.wp_oc as cache_code,\n                        '" . _('National Park / Landscape') . "' as type,\n                        CONVERT(parkipl.name USING utf8) as name\n                    from\n                        caches c\n                        inner join cache_npa_areas on cache_npa_areas.cache_id=c.cache_id\n                        inner join parkipl on cache_npa_areas.parki_id=parkipl.id\n                    where\n                        c.cache_id in (" . $cache_ids_escaped_and_imploded . ")\n                        and cache_npa_areas.parki_id != 0\n                    union\n                    select\n                        c.wp_oc as cache_code,\n                        'Natura 2000' as type,\n                        CONVERT(npa_areas.sitename USING utf8) as name\n                    from\n                        caches c\n                        inner join cache_npa_areas on cache_npa_areas.cache_id=c.cache_id\n                        inner join npa_areas on cache_npa_areas.npa_id=npa_areas.id\n                    where\n                        c.cache_id in (" . $cache_ids_escaped_and_imploded . ")\n                        and cache_npa_areas.npa_id != 0\n                    ");
             } else {
                 # OC.US and .UK do not have a 'parkipl' table.
                 # OC.US has a 'us_parks' table instead.
                 # Natura 2000 is Europe-only.
                 $rs = null;
             }
         }
         foreach ($results as &$result_ref) {
             $result_ref['protection_areas'] = array();
         }
         if ($rs) {
             while ($row = mysql_fetch_assoc($rs)) {
                 $results[$row['cache_code']]['protection_areas'][] = array('type' => $row['type'], 'name' => $row['name']);
             }
             mysql_free_result($rs);
         }
     }
     # Check which cache codes were not found and mark them with null.
     foreach ($cache_codes as $cache_code) {
         if (!isset($results[$cache_code])) {
             $results[$cache_code] = null;
         }
     }
     if (count($fields_to_remove_later) > 0) {
         # Some of the fields in $results were added only temporarily
         # (the Consumer did not ask for them). We will remove these fields now.
         foreach ($results as &$result_ref) {
             foreach ($fields_to_remove_later as $field) {
                 unset($result_ref[$field]);
             }
         }
     }
     # Order the results in the same order as the input codes were given.
     # This might come in handy for languages which support ordered dictionaries
     # (especially with conjunction with the search_and_retrieve method).
     # See issue#97. PHP dictionaries (assoc arrays) are ordered structures,
     # so we just have to rewrite it (sequentially).
     $ordered_results = new ArrayObject();
     foreach ($cache_codes as $cache_code) {
         $ordered_results[$cache_code] = $results[$cache_code];
     }
     /* Handle OCPL's "access logs" feature. */
     if (Settings::get('OC_BRANCH') == 'oc.pl' && Settings::get('OCPL_ENABLE_GEOCACHE_ACCESS_LOGS')) {
         $cache_ids = array_keys($cacheid2wptcode);
         /* Log this event only if some specific fields were accessed. */
         if (in_array('location', $fields) && count(array_intersect(array('hint', 'hints', 'hint2', 'hints2', 'description', 'descriptions'), $fields)) > 0) {
             require_once $GLOBALS['rootpath'] . 'okapi/lib/ocpl_access_logs.php';
             \okapi\OCPLAccessLogs::log_geocache_access($request, $cache_ids);
         }
     }
     return Okapi::formatted_response($request, $ordered_results);
 }
示例#3
0
 /**
  * Publish a new log entry and return log entry uuid. Throws
  * CannotPublishException or BadRequest on errors.
  */
 private static function _call(OkapiRequest $request)
 {
     # Developers! Please notice the fundamental difference between throwing
     # CannotPublishException and the "standard" BadRequest/InvalidParam
     # exceptions. You're reading the "_call" method now (see below for
     # "call").
     $cache_code = $request->get_parameter('cache_code');
     if (!$cache_code) {
         throw new ParamMissing('cache_code');
     }
     $logtype = $request->get_parameter('logtype');
     if (!$logtype) {
         throw new ParamMissing('logtype');
     }
     if (!in_array($logtype, array('Found it', "Didn't find it", 'Comment', 'Will attend', 'Attended'))) {
         throw new InvalidParam('logtype', "'{$logtype}' in not a valid logtype code.");
     }
     $comment = $request->get_parameter('comment');
     if (!$comment) {
         $comment = "";
     }
     $comment_format = $request->get_parameter('comment_format');
     if (!$comment_format) {
         $comment_format = "auto";
     }
     if (!in_array($comment_format, array('auto', 'html', 'plaintext'))) {
         throw new InvalidParam('comment_format', $comment_format);
     }
     $tmp = $request->get_parameter('when');
     if ($tmp) {
         $when = strtotime($tmp);
         if ($when < 1) {
             throw new InvalidParam('when', "'{$tmp}' is not in a valid format or is not a valid date.");
         }
         if ($when > time() + 5 * 60) {
             throw new CannotPublishException(_("You are trying to publish a log entry with a date in " . "future. Cache log entries are allowed to be published in " . "the past, but NOT in the future."));
         }
     } else {
         $when = time();
     }
     $on_duplicate = $request->get_parameter('on_duplicate');
     if (!$on_duplicate) {
         $on_duplicate = "silent_success";
     }
     if (!in_array($on_duplicate, array('silent_success', 'user_error', 'continue'))) {
         throw new InvalidParam('on_duplicate', "Unknown option: '{$on_duplicate}'.");
     }
     $rating = $request->get_parameter('rating');
     if ($rating !== null && !in_array($rating, array(1, 2, 3, 4, 5))) {
         throw new InvalidParam('rating', "If present, it must be an integer in the 1..5 scale.");
     }
     if ($rating && $logtype != 'Found it' && $logtype != 'Attended') {
         throw new BadRequest("Rating is allowed only for 'Found it' and 'Attended' logtypes.");
     }
     if ($rating !== null && Settings::get('OC_BRANCH') == 'oc.de') {
         # We will remove the rating request and change the success message
         # (which will be returned IF the rest of the query will meet all the
         # requirements).
         self::$success_message .= " " . sprintf(_("However, your cache rating was ignored, because %s does not " . "have a rating system."), Okapi::get_normalized_site_name());
         $rating = null;
     }
     $recommend = $request->get_parameter('recommend');
     if (!$recommend) {
         $recommend = 'false';
     }
     if (!in_array($recommend, array('true', 'false'))) {
         throw new InvalidParam('recommend', "Unknown option: '{$recommend}'.");
     }
     $recommend = $recommend == 'true';
     if ($recommend && $logtype != 'Found it') {
         if ($logtype != 'Attended') {
             throw new BadRequest("Recommending is allowed only for 'Found it' and 'Attended' logs.");
         } else {
             if (Settings::get('OC_BRANCH') == 'oc.pl') {
                 # We will remove the recommendation request and change the success message
                 # (which will be returned IF the rest of the query will meet all the
                 # requirements).
                 self::$success_message .= " " . sprintf(_("However, your cache recommendation was ignored, because " . "%s does not allow recommending event caches."), Okapi::get_normalized_site_name());
                 $recommend = null;
             }
         }
     }
     # We'll parse both 'needs_maintenance' and 'needs_maintenance2' here, but
     # we'll use only the $needs_maintenance2 variable afterwards.
     $needs_maintenance = $request->get_parameter('needs_maintenance');
     $needs_maintenance2 = $request->get_parameter('needs_maintenance2');
     if ($needs_maintenance && $needs_maintenance2) {
         throw new BadRequest("You cannot use both of these parameters at the same time: " . "needs_maintenance and needs_maintenance2.");
     }
     if (!$needs_maintenance2) {
         $needs_maintenance2 = 'null';
     }
     # Parse $needs_maintenance and get rid of it.
     if ($needs_maintenance) {
         if ($needs_maintenance == 'true') {
             $needs_maintenance2 = 'true';
         } else {
             if ($needs_maintenance == 'false') {
                 $needs_maintenance2 = 'null';
             } else {
                 throw new InvalidParam('needs_maintenance', "Unknown option: '{$needs_maintenance}'.");
             }
         }
     }
     unset($needs_maintenance);
     # At this point, $needs_maintenance2 is set exactly as the user intended
     # it to be set.
     if (!in_array($needs_maintenance2, array('null', 'true', 'false'))) {
         throw new InvalidParam('needs_maintenance2', "Unknown option: '{$needs_maintenance2}'.");
     }
     if ($needs_maintenance2 == 'false' && Settings::get('OC_BRANCH') == 'oc.pl') {
         # If not supported, just ignore it.
         self::$success_message .= " " . sprintf(_("However, your \"does not need maintenance\" flag was ignored, because " . "%s does not yet support this feature."), Okapi::get_normalized_site_name());
         $needs_maintenance2 = 'null';
     }
     # Check if cache exists and retrieve cache internal ID (this will throw
     # a proper exception on invalid cache_code). Also, get the user object.
     $cache = OkapiServiceRunner::call('services/caches/geocache', new OkapiInternalRequest($request->consumer, null, array('cache_code' => $cache_code, 'fields' => 'internal_id|status|owner|type|req_passwd')));
     $user = OkapiServiceRunner::call('services/users/by_internal_id', new OkapiInternalRequest($request->consumer, $request->token, array('internal_id' => $request->token->user_id, 'fields' => 'is_admin|uuid|internal_id|caches_found|rcmds_given')));
     # Various integrity checks.
     if ($cache['type'] == 'Event') {
         if (!in_array($logtype, array('Will attend', 'Attended', 'Comment'))) {
             throw new CannotPublishException(_('This cache is an Event cache. You cannot "Find" it (but ' . 'you can attend it, or comment on it)!'));
         }
     } else {
         if (in_array($logtype, array('Will attend', 'Attended'))) {
             throw new CannotPublishException(_('This cache is NOT an Event cache. You cannot "Attend" it ' . '(but you can find it, or comment on it)!'));
         } else {
             if (!in_array($logtype, array('Found it', "Didn't find it", 'Comment'))) {
                 throw new Exception("Unknown log entry - should be documented here.");
             }
         }
     }
     if ($logtype == 'Comment' && strlen(trim($comment)) == 0) {
         throw new CannotPublishException(_("Your have to supply some text for your comment."));
     }
     # Password check.
     if (($logtype == 'Found it' || $logtype == 'Attended') && $cache['req_passwd']) {
         $valid_password = Db::select_value("\n                select logpw\n                from caches\n                where cache_id = '" . Db::escape_string($cache['internal_id']) . "'\n            ");
         $supplied_password = $request->get_parameter('password');
         if (!$supplied_password) {
             throw new CannotPublishException(_("This cache requires a password. You didn't provide one!"));
         }
         if (strtolower($supplied_password) != strtolower($valid_password)) {
             throw new CannotPublishException(_("Invalid password!"));
         }
     }
     # Prepare our comment to be inserted into the database. This may require
     # some reformatting which depends on the current OC installation.
     # OC sites store all comments in HTML format, while the 'text_html' field
     # indicates their *original* format as delivered by the user. This
     # allows processing the 'text' field contents without caring about the
     # original format, while still being able to re-create the comment in
     # its original form.
     if ($comment_format == 'plaintext') {
         # This code is identical to the plaintext processing in OC code,
         # including a space handling bug: Multiple consecutive spaces will
         # get semantically lost in the generated HTML.
         $formatted_comment = htmlspecialchars($comment, ENT_COMPAT);
         $formatted_comment = nl2br($formatted_comment);
         if (Settings::get('OC_BRANCH') == 'oc.de') {
             $value_for_text_html_field = 0;
         } else {
             # 'text_html' = 0 (false) is broken in OCPL code and has been
             # deprecated; OCPL code was changed to always set it to 1 (true).
             # For OKAPI, the value has been changed from 0 to 1 with commit
             # cb7d222, after an email discussion with Harrie Klomp. This is
             # an ID of the appropriate email thread:
             #
             # Message-ID: <*****@*****.**>
             $value_for_text_html_field = 1;
         }
     } elseif ($comment_format == 'auto') {
         # 'Auto' is for backward compatibility. Before the "comment_format"
         # was introduced, OKAPI used a weird format in between (it allowed
         # HTML, but applied nl2br too).
         $formatted_comment = nl2br($comment);
         $value_for_text_html_field = 1;
     } else {
         $formatted_comment = $comment;
         # For user-supplied HTML comments, OC sites require us to do
         # additional HTML purification prior to the insertion into the
         # database.
         if (Settings::get('OC_BRANCH') == 'oc.de') {
             # NOTICE: We are including EXTERNAL OCDE library here! This
             # code does not belong to OKAPI!
             $opt['rootpath'] = $GLOBALS['rootpath'];
             $opt['html_purifier'] = Settings::get('OCDE_HTML_PURIFIER_SETTINGS');
             require_once $GLOBALS['rootpath'] . 'lib2/OcHTMLPurifier.class.php';
             $purifier = new \OcHTMLPurifier($opt);
             $formatted_comment = $purifier->purify($formatted_comment);
         } else {
             # TODO: Add OCPL HTML filtering.
             # See https://github.com/opencaching/okapi/issues/412.
         }
         $value_for_text_html_field = 1;
     }
     if (Settings::get('OC_BRANCH') == 'oc.pl') {
         # The HTML processing in OCPL code is broken. Effectively, it
         # will decode &lt; &gt; and &amp; (and maybe other things?)
         # before display so that text contents may be interpreted as HTML.
         # We work around this by applying a double-encoding for & < >:
         $formatted_comment = str_replace("&amp;", "&amp;#38;", $formatted_comment);
         $formatted_comment = str_replace("&lt;", "&amp;#60;", $formatted_comment);
         $formatted_comment = str_replace("&gt;", "&amp;#62;", $formatted_comment);
         # Note: This problem also exists when submitting logs on OCPL websites.
         # If you e.g. enter "<text>" in the editor, it will get lost.
         # See https://github.com/opencaching/opencaching-pl/issues/469.
     }
     unset($comment);
     # Prevent bug #367. Start the transaction and lock all the rows of this
     # (user, cache) pair. In theory, we want to lock even smaller number of
     # rows here (user, cache, type=1), but this wouldn't work, because there's
     # no index for this.
     #
     # http://stackoverflow.com/questions/17068686/
     Db::execute("start transaction");
     Db::select_column("\n            select 1\n            from cache_logs\n            where\n                user_id = '" . Db::escape_string($request->token->user_id) . "'\n                and cache_id = '" . Db::escape_string($cache['internal_id']) . "'\n            for update\n        ");
     # Duplicate detection.
     if ($on_duplicate != 'continue') {
         # Attempt to find a log entry made by the same user, for the same cache, with
         # the same date, type, comment, etc. Note, that these are not ALL the fields
         # we could check, but should work ok in most cases. Also note, that we
         # DO NOT guarantee that duplicate detection will succeed. If it doesn't,
         # nothing bad happens (user will just post two similar log entries).
         # Keep this simple!
         $duplicate_uuid = Db::select_value("\n                select uuid\n                from cache_logs\n                where\n                    user_id = '" . Db::escape_string($request->token->user_id) . "'\n                    and cache_id = '" . Db::escape_string($cache['internal_id']) . "'\n                    and type = '" . Db::escape_string(Okapi::logtypename2id($logtype)) . "'\n                    and date = from_unixtime('" . Db::escape_string($when) . "')\n                    and text = '" . Db::escape_string($formatted_comment) . "'\n                    " . (Settings::get('OC_BRANCH') == 'oc.pl' ? "and deleted = 0" : "") . "\n                limit 1\n            ");
         if ($duplicate_uuid != null) {
             if ($on_duplicate == 'silent_success') {
                 # Act as if the log has been submitted successfully.
                 return $duplicate_uuid;
             } elseif ($on_duplicate == 'user_error') {
                 throw new CannotPublishException(_("You have already submitted a log entry with exactly " . "the same contents."));
             }
         }
     }
     # Check if already found it (and make sure the user is not the owner).
     #
     # OCPL forbids logging 'Found it' or "Didn't find" for an already found cache,
     # while OCDE allows all kinds of duplicate logs.
     if (Settings::get('OC_BRANCH') == 'oc.pl' && ($logtype == 'Found it' || $logtype == "Didn't find it")) {
         $has_already_found_it = Db::select_value("\n                select 1\n                from cache_logs\n                where\n                    user_id = '" . Db::escape_string($user['internal_id']) . "'\n                    and cache_id = '" . Db::escape_string($cache['internal_id']) . "'\n                    and type = '" . Db::escape_string(Okapi::logtypename2id("Found it")) . "'\n                    and " . (Settings::get('OC_BRANCH') == 'oc.pl' ? "deleted = 0" : "true") . "\n            ");
         if ($has_already_found_it) {
             throw new CannotPublishException(_("You have already submitted a \"Found it\" log entry once. " . "Now you may submit \"Comments\" only!"));
         }
         if ($user['uuid'] == $cache['owner']['uuid']) {
             throw new CannotPublishException(_("You are the owner of this cache. You may submit " . "\"Comments\" only!"));
         }
     }
     # Check if the user has already rated the cache. BTW: I don't get this one.
     # If we already know, that the cache was NOT found yet, then HOW could the
     # user submit a rating for it? Anyway, I will stick to the procedure
     # found in log.php. On the bright side, it's fail-safe.
     if ($rating) {
         $has_already_rated = Db::select_value("\n                select 1\n                from scores\n                where\n                    user_id = '" . Db::escape_string($user['internal_id']) . "'\n                    and cache_id = '" . Db::escape_string($cache['internal_id']) . "'\n            ");
         if ($has_already_rated) {
             throw new CannotPublishException(_("You have already rated this cache once. Your rating " . "cannot be changed."));
         }
     }
     # If user wants to recommend...
     if ($recommend) {
         # Do the same "fail-safety" check as we did for the rating.
         $already_recommended = Db::select_value("\n                select 1\n                from cache_rating\n                where\n                    user_id = '" . Db::escape_string($user['internal_id']) . "'\n                    and cache_id = '" . Db::escape_string($cache['internal_id']) . "'\n            ");
         if ($already_recommended) {
             throw new CannotPublishException(_("You have already recommended this cache once."));
         }
         # Check the number of recommendations.
         $founds = $user['caches_found'] + 1;
         // +1, because he'll find THIS ONE in a moment
         # Note: caches_found includes the number of attended events (both on
         # OCDE and OCPL). OCPL does not allow recommending events, but the
         # number of attended events influences $rcmds_left the same way a
         # normal "Fount it" log does.
         $rcmds_left = floor($founds / 10.0) - $user['rcmds_given'];
         if ($rcmds_left <= 0) {
             throw new CannotPublishException(_("You don't have any recommendations to give. Find more " . "caches first!"));
         }
     }
     # If user checked the "needs_maintenance(2)" flag for OCPL, we will shuffle things
     # a little...
     if (Settings::get('OC_BRANCH') == 'oc.pl' && $needs_maintenance2 == 'true') {
         # If we're here, then we also know that the "Needs maintenance" log
         # type is supported by this OC site. However, it's a separate log
         # type, so we might have to submit two log types together:
         if ($logtype == 'Comment') {
             # If user submits a "Comment", we'll just change its type to
             # "Needs maintenance". Only one log entry will be issued.
             $logtype = 'Needs maintenance';
             $second_logtype = null;
             $second_formatted_comment = null;
         } elseif ($logtype == 'Found it') {
             # If "Found it", then we'll issue two log entries: one "Found
             # it" with the original comment, and second one "Needs
             # maintenance" with empty comment.
             $second_logtype = 'Needs maintenance';
             $second_formatted_comment = "";
         } elseif ($logtype == "Didn't find it") {
             # If "Didn't find it", then we'll issue two log entries, but this time
             # we'll do this the other way around. The first "Didn't find it" entry
             # will have an empty comment. We will move the comment to the second
             # "Needs maintenance" log entry. (It's okay for this behavior to change
             # in the future, but it seems natural to me.)
             $second_logtype = 'Needs maintenance';
             $second_formatted_comment = $formatted_comment;
             $formatted_comment = "";
         } else {
             if ($logtype == 'Will attend' || $logtype == 'Attended') {
                 # OC branches which allow maintenance logs, still don't allow them on
                 # event caches.
                 throw new CannotPublishException(_("Event caches cannot \"need maintenance\"."));
             } else {
                 throw new Exception();
             }
         }
     } else {
         # User didn't check the "Needs maintenance" flag OR "Needs maintenance"
         # log type isn't supported by this server.
         $second_logtype = null;
         $second_formatted_comment = null;
     }
     # Finally! Insert the rows into the log entries table. Update
     # cache stats and user stats.
     $log_uuids = array(self::insert_log_row($request->consumer->key, $cache['internal_id'], $user['internal_id'], $logtype, $when, $formatted_comment, $value_for_text_html_field, $needs_maintenance2));
     self::increment_cache_stats($cache['internal_id'], $when, $logtype);
     self::increment_user_stats($user['internal_id'], $logtype);
     if ($second_logtype != null) {
         # Reminder: This will only be called for OCPL branch.
         $log_uuids[] = self::insert_log_row($request->consumer->key, $cache['internal_id'], $user['internal_id'], $second_logtype, $when + 1, $second_formatted_comment, $value_for_text_html_field, 'null');
         self::increment_cache_stats($cache['internal_id'], $when + 1, $second_logtype);
         self::increment_user_stats($user['internal_id'], $second_logtype);
     }
     # Save the rating.
     if ($rating) {
         # This code will be called for OCPL branch only. Earlier, we made sure,
         # to set $rating to null, if we're running on OCDE.
         # OCPL has a little strange way of storing cumulative rating. Instead
         # of storing the sum of all ratings, OCPL stores the computed average
         # and update it using multiple floating-point operations. Moreover,
         # the "score" field in the database is on the -3..3 scale (NOT 1..5),
         # and the translation made at retrieval time is DIFFERENT than the
         # one made here (both of them are non-linear). Also, once submitted,
         # the rating can never be changed. It surely feels quite inconsistent,
         # but presumably has some deep logic into it. See also here (Polish):
         # http://wiki.opencaching.pl/index.php/Oceny_skrzynek
         switch ($rating) {
             case 1:
                 $db_score = -2.0;
                 break;
             case 2:
                 $db_score = -0.5;
                 break;
             case 3:
                 $db_score = 0.7;
                 break;
             case 4:
                 $db_score = 1.7;
                 break;
             case 5:
                 $db_score = 3.0;
                 break;
             default:
                 throw new Exception();
         }
         Db::execute("\n                update caches\n                set\n                    score = (\n                        score*votes + '" . Db::escape_string($db_score) . "'\n                    ) / (votes + 1),\n                    votes = votes + 1\n                where cache_id = '" . Db::escape_string($cache['internal_id']) . "'\n            ");
         Db::execute("\n                insert into scores (user_id, cache_id, score)\n                values (\n                    '" . Db::escape_string($user['internal_id']) . "',\n                    '" . Db::escape_string($cache['internal_id']) . "',\n                    '" . Db::escape_string($db_score) . "'\n                );\n            ");
     }
     # Save recommendation.
     if ($recommend) {
         if (Db::field_exists('cache_rating', 'rating_date')) {
             Db::execute("\n                    insert into cache_rating (user_id, cache_id, rating_date)\n                    values (\n                        '" . Db::escape_string($user['internal_id']) . "',\n                        '" . Db::escape_string($cache['internal_id']) . "',\n                        from_unixtime('" . Db::escape_string($when) . "')\n                    );\n                ");
         } else {
             Db::execute("\n                    insert into cache_rating (user_id, cache_id)\n                    values (\n                        '" . Db::escape_string($user['internal_id']) . "',\n                        '" . Db::escape_string($cache['internal_id']) . "'\n                    );\n                ");
         }
     }
     # Finalize the transaction.
     Db::execute("commit");
     # We need to delete the copy of stats-picture for this user. Otherwise,
     # the legacy OC code won't detect that the picture needs to be refreshed.
     $filepath = Okapi::get_var_dir() . '/images/statpics/statpic' . $user['internal_id'] . '.jpg';
     if (file_exists($filepath)) {
         unlink($filepath);
     }
     # Success. Return the uuids.
     return $log_uuids;
 }