/** * Hook into the task scheduler. Runs a query to find all comments and verification status updates that need * to be notified back to the recorder of a record. */ function notify_verifications_and_comments_scheduled_task($last_run_date) { if (!$last_run_date) { // first run, so get all records changed in last day. Query will automatically gradually pick up the rest. $last_run_date = date('Y-m-d', time() - 60 * 60 * 24 * 50); } try { $db = new Database(); $notifications = postgreSQL::selectVerificationAndCommentNotifications($last_run_date, $db); foreach ($notifications as $notification) { $vd = array($notification->date_start, $notification->date_end, $notification->date_type); $date = vague_date::vague_date_to_string($vd); if (empty($notification->comment)) { switch ($notification->record_status) { case 'V': $action = 'verified'; break; case 'R': $action = 'rejected'; break; case 'D': $action = 'marked dubious'; break; case 'S': $action = 'emailed for checking'; break; } $comment = 'The record of ' . $notification->taxon . ' at ' . $notification->public_entered_sref . " on {$date} was {$action}."; } else { if ($notification->auto_generated === 't') { $comment = 'An automated check using the <a target="_blank" href="http://www.nbn.org.uk/Tools-Resources/Recording-Resources/NBN-Record-Cleaner.aspx" target="_blank">' . 'NBN Record Cleaner</a> rules has highlighted your record of ' . $notification->taxon . ' at ' . $notification->public_entered_sref . ' on ' . $date; $comment .= $notification->generated_by === 'data_cleaner_identification_difficulty' ? ' as being of a species for which identification is not always trivial. <br/><em>' : '. The following information was given: <br/><em>'; } elseif ($notification->verified_on > $last_run_date and $notification->record_status !== 'I' and $notification->record_status !== 'T' and $notification->record_status !== 'C') { $comment = 'Your record of ' . $notification->taxon . ' at ' . $notification->public_entered_sref . ' on ' . $date . ' was examined by an expert.<br/>"'; } elseif ($notification->record_owner === 't') { $comment = 'A comment was added to your record of ' . $notification->taxon . ' at ' . $notification->public_entered_sref . ' on ' . $date . '.<br/>"'; } else { $comment = 'A reply was added to the record of ' . $notification->taxon . ' at ' . $notification->public_entered_sref . ' on ' . $date . ' which you\'ve previously commented on.<br/>"'; } $comment .= $notification->comment; if ($notification->auto_generated === 't') { // a difficult ID record is not necessarily important... $thing = $notification->generated_by === 'data_cleaner_identification_difficulty' ? 'identification' : 'important record'; $comment .= "</em><br/>You may be contacted by an expert to confirm this {$thing} so if you can supply any more information or photographs it would be useful."; } else { $comment .= '"<br/>'; } } $theNotificationToInsert = array('source' => 'Verifications and comments', 'source_type' => $notification->source_type, 'data' => json_encode(array('username' => $notification->username, 'occurrence_id' => $notification->id, 'comment' => $comment, 'taxon' => $notification->taxon, 'date' => $date, 'entered_sref' => $notification->public_entered_sref, 'auto_generated' => $notification->auto_generated, 'record_status' => $notification->record_status, 'updated_on' => $notification->updated_on)), 'linked_id' => $notification->id, 'user_id' => $notification->notify_user_id, 'digest_mode' => 'N', 'source_detail' => $notification->source_detail); $db->insert('notifications', $theNotificationToInsert); } echo count($notifications) . ' notifications generated<br/>'; } catch (Exception $e) { echo $e->getMessage(); } }
protected function check_record_access($entity, $id, $website_id, $sharing = false) { // if $id is null, then we have a new record, so no need to check if we have access to the record if (is_null($id)) { return true; } $table = inflector::plural($entity); $viewname = 'list_' . $table; if (!$this->db) { $this->db = new Database(); } $fields = postgreSQL::list_fields($viewname, $this->db); if (empty($fields)) { Kohana::log('info', $viewname . ' not present so cannot access entity'); throw new EntityAccessError('Access to entity ' . $entity . ' not available via requested view.', 1003); } $this->db->from("{$viewname} as record"); $this->db->where(array('record.id' => $id)); if (!in_array($entity, $this->allow_full_access)) { if (array_key_exists('website_id', $fields)) { // check if a request for shared data is being made. Also check this is valid to prevent injection. if ($sharing && preg_match('/[reporting|peer_review|verification|data_flow|moderation]/', $sharing)) { // request specifies the sharing mode (i.e. the task being performed, such as verification, moderation). So // we can use this to work out access to other website data. $this->db->join('index_websites_website_agreements as iwwa', array('iwwa.from_website_id' => 'record.website_id', 'iwwa.receive_for_' . $sharing . "='t'" => ''), NULL, 'LEFT'); $this->db->where('record.website_id IS NULL'); $this->db->orwhere('iwwa.to_website_id', $this->website_id); } else { $this->db->in('record.website_id', array(null, $this->website_id)); } } elseif (!$this->in_warehouse) { Kohana::log('info', $viewname . ' does not have a website_id - access denied'); throw new EntityAccessError('No access to entity ' . $entity . ' allowed.', 1004); } } $number_rec = $this->db->count_records(); return $number_rec > 0 ? true : false; }
public static function internal_wkt_to_sref($wkt, $sref_system, $precision = null, $output = null, $metresAccuracy = null) { $system = strtolower($sref_system); if (is_numeric($system)) { $srid = $system; } else { self::validateSystemClass($system); $systems = self::system_metadata(); $srid = $systems[$system]['srid']; } $transformedWkt = postgreSQL::transformWkt($wkt, kohana::config('sref_notations.internal_srid'), $srid); if (is_numeric($system)) { // NB the handed in precision is ignored, and the rounding is determined by the system in use if (array_key_exists($system, kohana::config('sref_notations.lat_long_systems'))) { return self::point_to_lat_long($transformedWkt, $system, $output); } else { return self::point_to_x_y($transformedWkt, $system); } } else { return call_user_func("{$system}::wkt_to_sref", $transformedWkt, $precision, $output, $metresAccuracy); } }
/** * Handles any index rebuild requirements as a result of new or updated records, e.g. in * samples or occurrences. Also handles joining of occurrence_associations to the * correct records */ private function postProcess() { if (class_exists('cache_builder')) { if (!empty(self::$changedRecords['insert']['occurrence'])) { cache_builder::insert($this->db, 'occurrences', self::$changedRecords['insert']['occurrence']); } if (!empty(self::$changedRecords['update']['occurrence'])) { cache_builder::update($this->db, 'occurrences', self::$changedRecords['update']['occurrence']); } if (!empty(self::$changedRecords['delete']['occurrence'])) { cache_builder::delete($this->db, 'occurrences', self::$changedRecords['delete']['occurrence']); } $samples = array(); if (!empty(self::$changedRecords['insert']['sample'])) { $samples = self::$changedRecords['insert']['sample']; } if (!empty(self::$changedRecords['update']['sample'])) { $samples += self::$changedRecords['update']['sample']; } if (!empty($samples)) { postgreSQL::insertMapSquaresForSamples($samples, 1000, $this->db); postgreSQL::insertMapSquaresForSamples($samples, 2000, $this->db); postgreSQL::insertMapSquaresForSamples($samples, 10000, $this->db); } else { // might be directly inserting an occurrence. No need to do this if inserting a sample, as the above code does the // occurrences in bulk. $occurrences = array(); if (!empty(self::$changedRecords['insert']['occurrence'])) { $occurrences = self::$changedRecords['insert']['occurrence']; } if (!empty(self::$changedRecords['update']['occurrence'])) { $occurrences += self::$changedRecords['update']['occurrence']; } if (!empty($occurrences)) { postgreSQL::insertMapSquaresForOccurrences($occurrences, 1000, $this->db); postgreSQL::insertMapSquaresForOccurrences($occurrences, 2000, $this->db); postgreSQL::insertMapSquaresForOccurrences($occurrences, 10000, $this->db); } } } if (!empty(self::$changedRecords['insert']['occurrence_association'])) { // We've got some associations between occurrences that could not have the to_occurrence_id // foreign key filled in yet, since the occurrence referred to did not exist at the time of // saving foreach (Occurrence_association_Model::$to_occurrence_id_pointers as $associationId => $pointer) { if (!empty($this->dynamicRowIdReferences["occurrence:{$pointer}"])) { $this->db->from('occurrence_associations')->set('to_occurrence_id', $this->dynamicRowIdReferences["occurrence:{$pointer}"])->where('id', $associationId)->update(); } } } }
/** * Handles any index rebuild requirements as a result of new or updated records, e.g. in * samples or occurrences. */ private function postProcess() { if (class_exists('cache_builder')) { if (!empty(self::$changedRecords['insert']['occurrence'])) { cache_builder::insert($this->db, 'occurrences', self::$changedRecords['insert']['occurrence']); } if (!empty(self::$changedRecords['update']['occurrence'])) { cache_builder::update($this->db, 'occurrences', self::$changedRecords['update']['occurrence']); } if (!empty(self::$changedRecords['delete']['occurrence'])) { cache_builder::delete($this->db, 'occurrences', self::$changedRecords['delete']['occurrence']); } $samples = array(); if (!empty(self::$changedRecords['insert']['sample'])) { $samples = self::$changedRecords['insert']['sample']; } if (!empty(self::$changedRecords['update']['sample'])) { $samples += self::$changedRecords['update']['sample']; } if (!empty($samples)) { postgreSQL::insertMapSquaresForSamples($samples, 1000, $this->db); postgreSQL::insertMapSquaresForSamples($samples, 2000, $this->db); postgreSQL::insertMapSquaresForSamples($samples, 10000, $this->db); } else { // might be directly inserting an occurrence. No need to do this if inserting a sample, as the above code does the // occurrences in bulk. $occurrences = array(); if (!empty(self::$changedRecords['insert']['occurrence'])) { $occurrences = self::$changedRecords['insert']['occurrence']; } if (!empty(self::$changedRecords['update']['occurrence'])) { $occurrences += self::$changedRecords['update']['occurrence']; } if (!empty($occurrences)) { postgreSQL::insertMapSquaresForOccurrences($occurrences, 1000, $this->db); postgreSQL::insertMapSquaresForOccurrences($occurrences, 2000, $this->db); postgreSQL::insertMapSquaresForOccurrences($occurrences, 10000, $this->db); } } } }
/** * Hook into the task scheduler. Runs a query to find all comments and verification status updates that need * to be notified back to the recorder of a record. * @param string $last_run_date Date & time that this module was last run. * @throws \Kohana_Database_Exception */ function notify_verifications_and_comments_scheduled_task($last_run_date) { if (!$last_run_date) { // first run, so get all records changed in last day. Query will automatically gradually pick up the rest. $last_run_date = date('Y-m-d', time() - 60 * 60 * 24 * 50); } $db = new Database(); $notifications = postgreSQL::selectVerificationAndCommentNotifications($last_run_date, $db); foreach ($notifications as $notification) { $vd = array($notification->date_start, $notification->date_end, $notification->date_type); $date = vague_date::vague_date_to_string($vd); if (empty($notification->comment)) { switch ($notification->record_status . (empty($notification->record_substatus) ? '' : $notification->record_substatus)) { case 'V': $action = 'accepted'; break; case 'V1': $action = 'accepted as correct'; break; case 'V2': $action = 'accepted as correct'; break; case 'C3': $action = 'plausible'; break; case 'D': $action = 'queried'; break; case 'R': $action = 'not accepted'; break; case 'R4': $action = 'not accepted as unable to verify'; break; case 'R5': $action = 'not accepted as incorrect'; break; default: $action = 'amended'; } $comment = "The record of {$notification->taxon} at {$notification->public_entered_sref} on {$date} was {$action}."; } else { if ($notification->auto_generated === 't' && substr($notification->generated_by, 0, 12) === 'data_cleaner' && $notification->record_owner === 't') { $comment = "The following message was attached to your record of {$notification->taxon} at {$notification->public_entered_sref} on {$date} " . "when it was checked using the <a target=\"_blank\" href=\"http://www.nbn.org.uk/Tools-Resources/Recording-Resources/NBN-Record-Cleaner.aspx\" target=\"_blank\">" . "NBN Record Cleaner</a>. This does not mean the record is incorrect or is being disputed; the information below is merely a flag against the record that " . "might provide useful information for recording and verification purposes."; } elseif ($notification->verified_on > $last_run_date && $notification->record_status !== 'I' && $notification->record_status !== 'T' && $notification->record_status !== 'C') { if ($notification->record_owner === 't') { $comment = "Your record of {$notification->taxon} at {$notification->public_entered_sref} on {$date} was examined by an expert."; } else { $comment = "A record of {$notification->taxon} at {$notification->public_entered_sref} on {$date} which you'd previously commented on was examined by an expert."; } } elseif ($notification->record_owner === 't') { $comment = "A comment was added to your record of {$notification->taxon} at {$notification->public_entered_sref} on {$date}."; } else { $comment = "A reply was added to the record of {$notification->taxon} at {$notification->public_entered_sref} on {$date} which you've previously commented on."; } $comment .= "<br/><em>{$notification->comment}</em>"; } $theNotificationToInsert = array('source' => 'Verifications and comments', 'source_type' => $notification->source_type, 'data' => json_encode(array('username' => $notification->username, 'occurrence_id' => $notification->id, 'comment' => $comment, 'taxon' => $notification->taxon, 'date' => $date, 'entered_sref' => $notification->public_entered_sref, 'auto_generated' => $notification->auto_generated, 'record_status' => $notification->record_status, 'record_substatus' => $notification->record_substatus, 'updated_on' => $notification->updated_on)), 'linked_id' => $notification->id, 'user_id' => $notification->notify_user_id, 'digest_mode' => 'N', 'source_detail' => $notification->source_detail); $db->insert('notifications', $theNotificationToInsert); } echo count($notifications) . ' notifications generated<br/>'; }
/** * Helper method that takes a list of user identifiers such as email addresses and returns the appropriate user ID * from the warehouse, which can then be used in subsequent calls to save the data. Takes the * following parameters in the $request (which is a merge of $_GET or $_POST data) in addition to a nonce and auth_token for a write operation:<ul> * <li><strong>identifiers</strong/><br/> * Required. A JSON encoded array of identifiers known for the user. Each array entry is an object * with a type property (e.g. twitter, openid) and identifier property (e.g. twitter account). An identifier of type * email must be provided in case a new user account has to be created on the warehouse.</li> * <li><strong>surname</strong/><br/> * Required. Surname of the user, enabling a new user account to be created on the warehouse.</li> * <li><strong>first_name</strong/><br/> * Optional. First name of the user, enabling a new user account to be created on the warehouse.</li> * <li><strong>cms_user_id</strong/><br/> * Optional. User ID from the client website's login system. Allows existing records to be linked to the created account when migrating from a * CMS user ID based authentication to Easy Login based authentication.</li> * <li><strong>warehouse_user_id</strong/><br/> * Optional. Where a user ID is already known but a new identifier is being provided (e.g. an email switch), provide the warehouse user ID.</li> * <li><strong>force</strong/><br/> * Optional. Only relevant after a request has returned an array of several possible matches. Set to * merge or split to define the action.</li> * <li><strong>users_to_merge</strong/><br/> * If force=merge, then this parameter can be optionally used to limit the list of users in the merge operation. * Pass a JSON encoded array of user IDs.</li> * <li><strong>attribute_values</strong> * Optional list of custom attribute values for the person which have been modified on the client website * and should be synchronised into the warehouse person record. The custom attributes must already exist * on the warehouse and have a matching caption, as well as being marked as synchronisable or the attribute * values will be ignored. Provide this as a JSON object with the properties being the caption of the * attribute and the values being the values to change. * </li> * <li><strong>shares_to_prevent</strong> * If the user has opted out of allowing their records to be shared with other * websites, the sharing tasks which they have opted out of should be passed as a comma separated list * here. Valid sharing tasks are: reporting, peer_review, verification, data_flow, moderation. They * will then be stored against the user account. </li> * </ul> * @return JSON JSON object containing the following properties: * userId - If a single user account has been identified then returns the Indicia user ID for the existing * or newly created account. Otherwise not returned. * attrs - If a single user account has been identifed then returns a list of captions and values for the * attributes to update on the client account. * possibleMatches - If a list of possible users has been identified then this property includes a list of people that * match from the warehouse - each with the user ID, website ID and website title they are * members of. If this happens then the client must ask the user to confirm that they * are the same person as the users of this website and if so, the response is sent back with a force=merge * parameter to force the merge of the people. If they are the same person as only some of the other users, * then use users_to_merge to supply an array of the user IDs that should be merged. Alternatively, if * force=split is passed through then the best fit user ID is returned and no merge operation occurs. * error - Error string if an error occurred. */ public static function get_user_id($request, $websiteId) { if (!array_key_exists('identifiers', $request)) { throw new exception('Error: missing identifiers parameter'); } $identifiers = json_decode($request['identifiers']); if (!is_array($identifiers)) { throw new Exception('Error: identifiers parameter not of correct format'); } if (empty($request['surname'])) { throw new exception('Call to get_user_id requires a surname in the GET or POST data.'); } $userPersonObj = new stdClass(); $userPersonObj->db = new Database(); if (!empty($request['warehouse_user_id'])) { $userId = $request['warehouse_user_id']; $qry = $userPersonObj->db->select('person_id')->from('users')->where(array('id' => $userId))->get()->result_array(false); if (!isset($qry[0])) { throw new exception("Error: unknown warehouse_user_id ({$userId})"); } $userPersonObj->person_id = $qry[0]['person_id']; } else { $existingUsers = array(); // work through the list of identifiers and find the users for the ones we already know about, // plus find the list of identifiers we've not seen before. // email is a special identifier used to create person. $email = null; foreach ($identifiers as $identifier) { // store the email address, since this is always required to create a person if ($identifier->type === 'email') { $email = $identifier->identifier; // The query to find an existing user is slightly different for emails, since the // email can be in the user identifier list or the person record $joinType = 'LEFT'; } else { $joinType = 'INNER'; } $userPersonObj->db->select('DISTINCT u.id as user_id, u.person_id')->from('users as u')->join('people as p', 'p.id', 'u.person_id')->join('user_identifiers as um', 'um.user_id', 'u.id', $joinType)->join('termlists_terms as tlt1', 'tlt1.id', 'um.type_id', $joinType)->join('termlists_terms as tlt2', 'tlt2.meaning_id', 'tlt1.meaning_id', $joinType)->join('terms as t', 't.id', 'tlt2.term_id', $joinType)->where(array('u.deleted' => 'f', 'p.deleted' => 'f')); $ident = pg_escape_string($identifier->identifier); $type = pg_escape_string($identifier->type); if ($identifier->type === 'email') { // Filter to find either the user identifier or the email in the person record $userPersonObj->db->where("(um.identifier='{$ident}' OR p.email_address='{$ident}')"); $userPersonObj->db->where("(t.term='{$type}' OR p.email_address='{$ident}')"); } else { $userPersonObj->db->where("um.identifier='{$ident}'"); $userPersonObj->db->where("t.term='{$type}'"); } if (isset($request['users_to_merge'])) { $usersToMerge = json_decode($request['users_to_merge']); $userPersonObj->db->in('user_id', $usersToMerge); } $r = $userPersonObj->db->get()->result_array(true); foreach ($r as $existingUser) { // create a placeholder for the known user we just found if (!isset($existingUsers[$existingUser->user_id])) { $existingUsers[$existingUser->user_id] = array(); } // add the identifier detail to this known user $existingUsers[$existingUser->user_id][] = array('identifier' => $identifier->identifier, 'type' => $identifier->type, 'person_id' => $existingUser->person_id); } } if ($email === null) { throw new exception('Call to get_user_id requires an email address in the list of provided identifiers.'); } // Now we have a list of the existing users that match this identifier. If there are none, we // can create a new user and attach to the current website. If there is one, then we can // just return it. If more than one, then we have a resolution task since it probably // means 2 user records refer to the same physical person, or someone is sharing their // identifiers! if (count($existingUsers) === 0) { $userId = self::createUser($email, $userPersonObj); } elseif (count($existingUsers) === 1) { // single, known user associated with these identifiers $keys = array_keys($existingUsers); $userId = array_pop($keys); $userPersonObj->person_id = $existingUsers[$userId][0]['person_id']; } if (!isset($userId)) { $resolution = self::resolveMultipleUsers($identifiers, $existingUsers, $userPersonObj); // response could be a list of possible users to match against, or a single user ID. if (isset($resolution['possibleMatches'])) { return $resolution; } else { $userId = $resolution['userId']; $userPersonObj->person_id = $existingUsers[$userId][0]['person_id']; } } } self::storeIdentifiers($userId, $identifiers, $userPersonObj, $websiteId); self::associateWebsite($userId, $userPersonObj, $websiteId); self::storeSharingPreferences($userId, $userPersonObj); $attrs = self::getAttributes($userPersonObj, $websiteId); self::storeCustomAttributes($userId, $attrs, $userPersonObj); // Convert the attributes to update in the client website account into an array // of captions & values $attrsToReturn = array(); foreach ($attrs as $attr) { $attrsToReturn[$attr['caption']] = $attr['value']; } // If allocating a new user ID, then update the created_by_id for all records that were created by this cms_user_id. This // takes ownership of the records. if (empty($request['warehouse_user_id']) && !empty($request['cms_user_id'])) { postgreSQL::setOccurrenceCreatorByCmsUser($websiteId, $userId, $request['cms_user_id'], $userPersonObj->db); } return array('userId' => $userId, 'attrs' => $attrsToReturn); }