/**
* 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);
}
