function user_Update($system, $record)
{
if (user_Validate($system, $record)) {
$recID = intval(@$record['ugr_ID']);
$rectype = $record['ugr_Type'];
$is_registration = $rectype == 'user' && $recID < 1;
if ($is_registration && $system->get_system('sys_AllowRegistration') == 0) {
$system->addError(HEURIST_REQUEST_DENIED, 'Registration is not allowed for current database');
} else {
if ($is_registration || $system->is_admin2($recID)) {
//do not allow registration if approvement mail cannot be sent
if ($is_registration) {
if (false && !checkSmtp()) {
$system->addError(HEURIST_SYSTEM_CONFIG, 'Error_Mail_Registration');
return false;
}
//check capture
if (@$_SESSION["captcha_code"] && $_SESSION["captcha_code"] != @$record['ugr_Captcha']) {
$system->addError(HEURIST_UNKNOWN_ERROR, 'Are you a bot? Please enter the correct answer to the challenge question');
return false;
}
if (@$_SESSION["captcha_code"]) {
unset($_SESSION["captcha_code"]);
}
}
if (@$record['ugr_Captcha']) {
unset($record['ugr_Captcha']);
}
$mysqli = $system->get_mysqli();
$res = mysql__select_value($mysqli, "select ugr_ID from sysUGrps where ugr_Name='" . $mysqli->real_escape_string($record['ugr_Enabled']) . "' or ugr_eMail='" . $mysqli->real_escape_string($record['ugr_eMail']) . "'");
if ($res != $recID) {
$system->addError(HEURIST_INVALID_REQUEST, 'The provided name or email already exists');
return false;
}
$is_approvement = false;
//encrypt password
$tmp_password = null;
if ($rectype == 'user') {
if (@$record['ugr_Password'] && $record['ugr_Password'] != '') {
$tmp_password = $record['ugr_Password'];
$record['ugr_Password'] = hash_it($tmp_password);
} else {
unset($record['ugr_Password']);
}
if ($system->get_user_id() < 1) {
//not logged in - always disabled
$record['ugr_Enabled'] = "n";
}
if ("y" == @$record['ugr_Enabled']) {
$is_approvement = user_isApprovement($system, $recID);
}
}
$res = mysql__insertupdate($mysqli, "sysUGrps", "ugr", $record);
if (is_numeric($res) > 0) {
$new_recID = $res;
//actions on complete
if ($rectype == 'user') {
$rv = true;
if ($recID < 1 && $system->get_user_id() < 1) {
$rv = user_EmailAboutNewUser($system, $new_recID);
} else {
if ($recID < 1 || $is_approvement) {
$rv = user_EmailApproval($system, $new_recID, $tmp_password, $is_approvement);
}
}
if (!$rv) {
return false;
}
} else {
if ($recID < 1) {
//this is addition of new group
//add current user as admin for new group
//changeRole($recID, get_user_id(), "admin", null, false, true);
}
}
return $res;
//returns affected record id
} else {
$system->addError(HEURIST_DB_ERROR, 'Cannot update record in database', $res);
}
} else {
$system->addError(HEURIST_REQUEST_DENIED, 'Operation denied. Not enough rights');
}
}
} else {
//$system->addError(HEURIST_INVALID_REQUEST, "All required fields are not defined");
}
return false;
}
/**
* Assign tags to records and bookmark records
*
* @param mixed $system
* @param mixed $record_ids - array of record ids
* @param mixed $tag_ids - array of tag ids
* @param mixed $tag_names - if tag ids are not defined, we use $tag_names to get ids
* @param mixed $ugrID
*
* returns false if error
* or array ('tags_added'=>$tag_count, 'bookmarks_added'=>$bookmarks_added)
*/
function tagsAssign($system, $record_ids, $tag_ids, $tag_names = null, $ugrID = null)
{
$ugrID = $system->is_admin2($ugrID);
if (!$ugrID) {
$system->addError(HEURIST_REQUEST_DENIED);
return false;
} else {
//find tag_ids by tag name
if ($tag_ids == null) {
if ($tag_names == null) {
$system->addError(HEURIST_INVALID_REQUEST, 'Tag name is not defined');
return false;
} else {
$tag_ids = tagGetByName($system, array_filter(explode(',', $tag_names)), true, $ugrID);
}
}
if (!is_array($record_ids) || count($record_ids) < 0) {
$system->addError(HEURIST_INVALID_REQUEST, 'Record ids are not defined');
return false;
}
if (!is_array($tag_ids) || count($tag_ids) < 0) {
$system->addError(HEURIST_INVALID_REQUEST, 'Tags ids either not found or not defined');
return false;
}
$mysqli = $system->get_mysqli();
//assign links
$insert_query = 'insert ignore into usrRecTagLinks (rtl_RecID, rtl_TagID) ' . 'select rec_ID, tag_ID from usrTags, Records ' . ' where rec_ID in (' . implode(',', $record_ids) . ') ' . ' and tag_ID in (' . implode(',', $tag_ids) . ')' . ' and tag_UGrpID = ' . $ugrID;
$res = $mysqli->query($insert_query);
if (!$res) {
$system->addError(HEURIST_DB_ERROR, "Cannot assign tags", $mysqli->error);
return false;
}
$tag_count = $mysqli->affected_rows;
/*$new_rec_ids = mysql__select_column($mysqli,
'select rec_ID from Records '
.' left join usrBookmarks on bkm_recID=rec_ID and bkm_UGrpID='.$ugrID
.' where bkm_ID is null and rec_ID in (' . join(',', $record_ids) . ')');*/
//if $ugrID is not a group - create bookmarks
$bookmarks_added = 0;
if ($ugrID == $system->get_user_id() || mysql__select_value($mysqli, 'select ugr_Type from sysUGrps where ugr_ID =' . $ugrID) == 'user') {
//not bookmarked yet
$query = 'insert into usrBookmarks ' . ' (bkm_UGrpID, bkm_Added, bkm_Modified, bkm_recID)' . ' select ' . $ugrID . ', now(), now(), rec_ID from Records ' . ' left join usrBookmarks on bkm_recID=rec_ID and bkm_UGrpID=' . $ugrID . ' where bkm_ID is null and rec_ID in (' . implode(',', $record_ids) . ')';
//$stmt = $mysqli->query($query);
$res = $mysqli->prepare($query);
if (!$res) {
$system->addError(HEURIST_DB_ERROR, "Cannot add bookmarks", $mysqli->error);
return false;
}
$bookmarks_added = $mysqli->affected_rows;
}
return array('tags_added' => $tag_count, 'bookmarks_added' => $bookmarks_added);
}
}
/*hide: {
effect: "puff",
duration: 500
}*/
}
);
}
//
// Performs inital search: parameters from request or from user preferences
//
function onInitCompleted_PerformSearch(){
<?php
$db_total_records = mysql__select_value($system->get_mysqli(), 'select count(*) from Records');
echo 'var db_total_records=' . ($db_total_records > 0 ? $db_total_records : 0) . ';';
?>
if(window.hWin.HAPI4.sysinfo['layout']=='H4Default'){
//switch to FAP tab if q parameter is defined
if(db_total_records<1){
showTipOfTheDay(false);
}else{
window.hWin.HAPI4.LayoutMgr.putAppOnTopById('FAP');
}
}else if(db_total_records<1){
showTipOfTheDay(false);
}
//perform search in the case that parameter "q" is defined
/**
* Remove detail value for given set of records and detail type and values
*/
public function detailsDelete()
{
if (!$this->_validateParamsAndCounts()) {
return false;
} else {
if (count(@$this->recIDs) == 0) {
return $this->result_data;
}
}
$dtyID = $this->data['dtyID'];
$dtyName = @$this->data['dtyName'] ? "'" . $this->data['dtyName'] . "'" : "id:" . $this->data['dtyID'];
$isDeleteAll = !array_key_exists("sVal", $this->data) || $this->data['sVal'] == '';
$mysqli = $this->system->get_mysqli();
$basetype = mysql__select_value($mysqli, 'select dty_Type from defDetailTypes where dty_ID = ' . $dtyID);
switch ($basetype) {
case "freetext":
case "blocktext":
case "enum":
case "relationtype":
case "float":
case "integer":
case "resource":
case "date":
$searchClause = $isDeleteAll ? '1' : "dtl_Value = \"" . $mysqli->real_escape_string($this->data['sVal']) . "\"";
break;
default:
$this->system->addError(HEURIST_INVALID_REQUEST, "{$basetype} fields are not supported by deletion service");
return false;
}
//get array of required detail types per record type
$rtyRequired = mysql__select_list($mysqli, "defRecStructure", "rst_RecTypeID", "rst_DetailTypeID = {$dtyID} and rst_RecTypeID in (" . implode(",", $this->rtyIDs) . ") and rst_RequirementType='required'");
$undefinedFieldsRecIDs = array();
//value not found
$processedRecIDs = array();
//success
$limittedRecIDs = array();
//it is npt possible to delete requried fields
$sqlErrors = array();
$now = date('Y-m-d H:i:s');
$dtl = array('dtl_Modified' => $now);
$rec_update = array('rec_ID' => 'to-be-filled', 'rec_Modified' => $now);
$baseTag = "~delete field {$dtyName} {$now}";
foreach ($this->recIDs as $recID) {
//get matching detail value for record if there is one
$valuesToBeDeleted = mysql__select_list($mysqli, "recDetails", "dtl_ID", "dtl_RecID = {$recID} and dtl_DetailTypeID = {$dtyID} and {$searchClause}");
if ($valuesToBeDeleted == null && $mysqli->error) {
$sqlErrors[$recID] = $mysqli->error;
continue;
} else {
if ($valuesToBeDeleted == null || count($valuesToBeDeleted) == 0) {
//not found
array_push($undefinedFieldsRecIDs, $recID);
continue;
}
}
//validate if details can be deleted for required fields
if (count($this->rtyIDs) > 1) {
//get rectype for current record
$rectype_ID = mysql__select_value($mysqli, 'select rec_RecTypeID from Records where rec_ID=' . $recID);
} else {
$rectype_ID = $this->rtyIDs[0];
}
if (array_search($rectype_ID, $rtyRequired) !== FALSE) {
//this is required field
if (!$isDeleteAll) {
//find total count
$total_cnt = mysql__select_value($mysqli, "select count() from recDetails where " . " where dtl_RecID = {$recID} and dtl_DetailTypeID = {$dtyID}");
}
if ($isDeleteAll || $total_cnt == count($valuesToBeDeleted)) {
array_push($limittedRecIDs, $recID);
continue;
}
}
//delete the details
$sql = 'delete from recDetails where dtl_ID in (' . implode(',', $valuesToBeDeleted) . ')';
if ($mysqli->query($sql) === TRUE) {
array_push($processedRecIDs, $recID);
//update record edit date
$rec_update['rec_ID'] = $recID;
$ret = mysql__insertupdate($mysqli, 'Records', 'rec', $rec_update);
if (!is_numeric($ret)) {
$sqlErrors[$recID] = 'Cannot update modify date. ' . $ret;
}
} else {
$sqlErrors[$recID] = $mysqli->error;
}
}
//for recors
//assign special system tags
$this->_assignTagsAndReport('processed', $processedRecIDs, $baseTag);
$this->_assignTagsAndReport('undefined', $undefinedFieldsRecIDs, $baseTag);
$this->_assignTagsAndReport('limitted', $limittedRecIDs, $baseTag);
$this->_assignTagsAndReport('errors', $sqlErrors, $baseTag);
return $this->result_data;
}
function validateImport($params)
{
global $system;
//get rectype to import
$rty_ID = @$params['sa_rectype'];
$currentSeqIndex = @$params['seq_index'];
if (intval($rty_ID) < 1) {
$system->addError(HEURIST_INVALID_REQUEST, 'Record type not defined or wrong value');
return false;
}
$imp_session = getImportSession($params['imp_ID']);
if (is_bool($imp_session) && !$imp_session) {
return false;
//error - can not get import session
}
//add result of validation to session
$imp_session['validation'] = array("count_update" => 0, "count_insert" => 0, "count_update_rows" => 0, "count_insert_rows" => 0, "count_error" => 0, "error" => array(), "recs_insert" => array(), "recs_update" => array());
//get rectype to import
$id_field = @$params['recid_field'];
//record ID field is always defined explicitly
$ignore_insert = @$params['ignore_insert'] == 1;
//ignore new records
if (@$imp_session['columns'][substr($id_field, 6)] == null) {
$system->addError(HEURIST_INVALID_REQUEST, 'Identification field is not defined');
return false;
}
$import_table = $imp_session['import_table'];
$cnt_update_rows = 0;
$cnt_insert_rows = 0;
$mapping_params = @$params['mapping'];
$mapping = array();
// fieldtype => fieldname in import table
$sel_query = array();
if (is_array($mapping_params) && count($mapping_params) > 0) {
foreach ($mapping_params as $index => $field_type) {
$field_name = "field_" . $index;
$mapping[$field_type] = $field_name;
$imp_session['validation']['mapped_fields'][$field_name] = $field_type;
//all mapped fields - they will be used in validation query
array_push($sel_query, $field_name);
}
} else {
$system->addError(HEURIST_INVALID_REQUEST, 'Mapping is not defined');
return false;
}
$mysqli = $system->get_mysqli();
$cnt_recs_insert_nonexist_id = 0;
// validate selected record ID field
// in case id field is not created on match step (it is from original set of columns)
// we have to verify that its values are valid
if (FALSE && !@$imp_session['indexes'][$id_field]) {
//find recid with different rectype
$query = "select imp_id, " . implode(",", $sel_query) . ", " . $id_field . " from " . $import_table . " left join Records on rec_ID=" . $id_field . " where rec_RecTypeID<>" . $rty_ID;
// TODO: I'm not sure whether message below has been correctly interpreted
$wrong_records = getWrongRecords($query, $imp_session, "Your input data contain record IDs in the selected ID column for existing records which are not numeric IDs. " . "The import cannot proceed until this is corrected.", "Incorrect record types", $id_field);
if (is_array($wrong_records) && count($wrong_records) > 0) {
$wrong_records['validation']['mapped_fields'][$id_field] = 'id';
$imp_session = $wrong_records;
} else {
if ($wrong_records === false) {
return $wrong_records;
}
}
if (!$ignore_insert) {
//WARNING - it ignores possible multivalue index field
//find record ID that do not exist in HDB - to insert
$query = "select count(imp_id) " . " from " . $import_table . " left join Records on rec_ID=" . $id_field . " where " . $id_field . ">0 and rec_ID is null";
$row = mysql__select_array($mysqli, $query);
if ($row && $row[0] > 0) {
$cnt_recs_insert_nonexist_id = $row[0];
}
}
}
// find records to update
$select_query = "SELECT count(DISTINCT " . $id_field . ") FROM " . $import_table . " left join Records on rec_ID=" . $id_field . " WHERE rec_ID is not null and " . $id_field . ">0";
$cnt = mysql__select_value($mysqli, $select_query);
if ($cnt > 0) {
$imp_session['validation']['count_update'] = $cnt;
$imp_session['validation']['count_update_rows'] = $cnt;
/*
//find first 100 records to preview
$select_query = "SELECT ".$id_field.", imp_id, ".implode(",",$sel_query)
." FROM ".$import_table
." left join Records on rec_ID=".$id_field
." WHERE rec_ID is not null and ".$id_field.">0"
." ORDER BY ".$id_field." LIMIT 5000";
$imp_session['validation']['recs_update'] = mysql__select_all($mysqli, $select_query, false);
*/
$imp_session['validation']['recs_update'] = array();
}
if (!$ignore_insert) {
// find records to insert
$select_query = "SELECT count(DISTINCT " . $id_field . ") FROM " . $import_table . " WHERE " . $id_field . "<0";
//$id_field." is null OR ".
$cnt1 = mysql__select_value($mysqli, $select_query);
$select_query = "SELECT count(*) FROM " . $import_table . ' WHERE ' . $id_field . ' IS NULL';
//$id_field." is null OR ".
$cnt2 = mysql__select_value($mysqli, $select_query);
if ($cnt1 + $cnt2 > 0) {
$imp_session['validation']['count_insert'] = $cnt1 + $cnt2;
$imp_session['validation']['count_insert_rows'] = $cnt1 + $cnt2;
/*find first 100 records to display
$select_query = 'SELECT imp_id, '.implode(',',$sel_query)
.' FROM '.$import_table.' WHERE '.$id_field.'<0 or '.$id_field.' IS NULL LIMIT 5000';
$imp_session['validation']['recs_insert'] = mysql__select_all($mysqli, $select_query, false);
*/
$imp_session['validation']['recs_insert'] = array();
}
}
//additional query for non-existing IDs
if ($cnt_recs_insert_nonexist_id > 0) {
//NOT USED
$imp_session['validation']['count_insert_nonexist_id'] = $cnt_recs_insert_nonexist_id;
$imp_session['validation']['count_insert'] = $imp_session['validation']['count_insert'] + $cnt_recs_insert_nonexist_id;
$imp_session['validation']['count_insert_rows'] = $imp_session['validation']['count_insert'];
/*
$select_query = "SELECT imp_id, ".implode(",",$sel_query)
." FROM ".$import_table
." LEFT JOIN Records on rec_ID=".$id_field
." WHERE ".$id_field.">0 and rec_ID is null LIMIT 5000";
$res = mysql__select_all($mysqli, $select_query, false);
if($res && count($res)>0){
if(@$imp_session['validation']['recs_insert']){
$imp_session['validation']['recs_insert'] = array_merge($imp_session['validation']['recs_insert'], $res);
}else{
$imp_session['validation']['recs_insert'] = $res;
}
}*/
$imp_session['validation']['recs_insert'] = array();
}
// fill array with field in import table to be validated
$recStruc = dbs_GetRectypeStructures($system, $rty_ID, 2);
$recStruc = $recStruc['typedefs'];
$idx_reqtype = $recStruc['dtFieldNamesToIndex']['rst_RequirementType'];
$idx_fieldtype = $recStruc['dtFieldNamesToIndex']['dty_Type'];
$dt_mapping = array();
//mapping to detail type ID
$missed = array();
$missed_ptr = array();
$query_reqs = array();
//fieldnames from import table
$query_reqs_where = array();
//where clause for validation
$query_enum = array();
$query_enum_join = array();
$query_enum_where = array();
$query_res = array();
$query_res_join = array();
$query_res_where = array();
$query_num = array();
$query_num_nam = array();
$query_num_where = array();
$query_date = array();
$query_date_nam = array();
$query_date_where = array();
$numeric_regex = "'^([+-]?[0-9]+\\.*)+'";
// "'^([+-]?[0-9]+\\.?[0-9]*e?[0-9]+)|(0x[0-9A-F]+)$'";
//loop for all fields in record type structure
foreach ($recStruc[$rty_ID]['dtFields'] as $ft_id => $ft_vals) {
//find fields with given field type among mappings
$field_name = @$mapping[$ft_id];
if (!$field_name) {
//???????
$field_name = array_search($rty_ID . "." . $ft_id, $imp_session["mapping"], true);
//from previous session
}
if (!$field_name && $ft_vals[$idx_fieldtype] == "geo") {
//specific mapping for geo fields
//it may be mapped to itself or mapped to two fields - lat and long
$field_name1 = @$mapping[$ft_id . "_lat"];
$field_name2 = @$mapping[$ft_id . "_long"];
if (!$field_name1 && !$field_name2) {
$field_name1 = array_search($rty_ID . "." . $ft_id . "_lat", $imp_session["mapping"], true);
$field_name2 = array_search($rty_ID . "." . $ft_id . "_long", $imp_session["mapping"], true);
}
if ($ft_vals[$idx_reqtype] == "required") {
if (!$field_name1 || !$field_name2) {
array_push($missed, $ft_vals[0]);
} else {
array_push($query_reqs, $field_name1);
array_push($query_reqs, $field_name2);
array_push($query_reqs_where, $field_name1 . " is null or " . $field_name1 . "=''");
array_push($query_reqs_where, $field_name2 . " is null or " . $field_name2 . "=''");
}
}
if ($field_name1 && $field_name2) {
array_push($query_num, $field_name1);
array_push($query_num_where, "(NOT({$field_name1} is null or {$field_name1}='') and NOT({$field_name1} REGEXP " . $numeric_regex . "))");
array_push($query_num, $field_name2);
array_push($query_num_where, "(NOT({$field_name2} is null or {$field_name2}='') and NOT({$field_name2} REGEXP " . $numeric_regex . "))");
}
} else {
if ($ft_vals[$idx_reqtype] == "required") {
if (!$field_name) {
if ($ft_vals[$idx_fieldtype] == "resource") {
array_push($missed_ptr, $ft_vals[0]);
} else {
array_push($missed, $ft_vals[0]);
}
} else {
if ($ft_vals[$idx_fieldtype] == "resource") {
//|| $ft_vals[$idx_fieldtype] == "enum"){
$squery = "not (" . $field_name . ">0)";
} else {
$squery = $field_name . " is null or " . $field_name . "=''";
}
array_push($query_reqs, $field_name);
array_push($query_reqs_where, $squery);
}
}
}
if ($field_name) {
//mapping exists
$dt_mapping[$field_name] = $ft_id;
//$ft_vals[$idx_fieldtype];
if ($ft_vals[$idx_fieldtype] == "enum" || $ft_vals[$idx_fieldtype] == "relationtype") {
array_push($query_enum, $field_name);
$trm1 = "trm" . count($query_enum);
array_push($query_enum_join, " defTerms {$trm1} on {$trm1}.trm_Label={$field_name} ");
array_push($query_enum_where, "(" . $trm1 . ".trm_Label is null and not ({$field_name} is null or {$field_name}=''))");
} else {
if ($ft_vals[$idx_fieldtype] == "resource") {
array_push($query_res, $field_name);
$trm1 = "rec" . count($query_res);
array_push($query_res_join, " Records {$trm1} on {$trm1}.rec_ID={$field_name} ");
array_push($query_res_where, "(" . $trm1 . ".rec_ID is null and not ({$field_name} is null or {$field_name}=''))");
} else {
if ($ft_vals[$idx_fieldtype] == "float" || $ft_vals[$idx_fieldtype] == "integer") {
array_push($query_num, $field_name);
array_push($query_num_where, "(NOT({$field_name} is null or {$field_name}='') and NOT({$field_name} REGEXP " . $numeric_regex . "))");
} else {
if ($ft_vals[$idx_fieldtype] == "date" || $ft_vals[$idx_fieldtype] == "year") {
array_push($query_date, $field_name);
if ($ft_vals[$idx_fieldtype] == "year") {
array_push($query_date_where, "(concat('',{$field_name} * 1) != {$field_name} " . "and not ({$field_name} is null or {$field_name}=''))");
} else {
array_push($query_date_where, "(str_to_date({$field_name}, '%Y-%m-%d %H:%i:%s') is null " . "and str_to_date({$field_name}, '%d/%m/%Y') is null " . "and str_to_date({$field_name}, '%d-%m-%Y') is null " . "and not ({$field_name} is null or {$field_name}=''))");
}
}
}
}
}
}
}
//ignore_required
//1. Verify that all required field are mapped =====================================================
if ((count($missed) > 0 || count($missed_ptr) > 0) && $imp_session['validation']['count_insert'] > 0) {
$error = '';
if (count($missed) > 0) {
$error = 'The following fields are required fields. You will need to map
them to incoming data before you can import new records:<br><br>' . implode(',', $missed);
}
if (count($missed_ptr) > 0) {
$error = $error . '<br>Record pointer fields( ' . implode(',', $missed_ptr) . ' ) require a record identifier value (only shown in the dropdowns in the Identifiers section). This error implies that you have not yet matched and/or imported record types that are specified in previous steps of the import workflow. Please start from the beginning. Please report the error to the Heurist developers if you think you have followed the workflow correctly.';
}
$system->addError(HEURIST_ERROR, $error);
return false;
}
if ($id_field) {
//validate only for defined records IDs
if ($ignore_insert) {
$only_for_specified_id = " (" . $id_field . " > 0) AND ";
} else {
$only_for_specified_id = " (NOT(" . $id_field . " is null OR " . $id_field . "='')) AND ";
}
} else {
$only_for_specified_id = "";
}
//2. In DB: Verify that all required fields have values =============================================
$k = 0;
foreach ($query_reqs as $field) {
$query = "select imp_id, " . implode(",", $sel_query) . " from {$import_table} " . " where " . $only_for_specified_id . "(" . $query_reqs_where[$k] . ")";
// implode(" or ",$query_reqs_where);
$k++;
$wrong_records = getWrongRecords($query, $imp_session, "This field is required - a value must be supplied for every record", "Missing Values", $field);
if (is_array($wrong_records)) {
$cnt = count(@$imp_session['validation']['error']);
//was
$imp_session = $wrong_records;
//remove from array to be inserted - wrong records with missed required field
if (count(@$imp_session['validation']['recs_insert']) > 0) {
$cnt2 = count(@$imp_session['validation']['error']);
//now
if ($cnt2 > $cnt) {
$wrong_recs_ids = $imp_session['validation']['error'][$cnt]['recs_error_ids'];
if (count($wrong_recs_ids) > 0) {
$badrecs = array();
foreach ($imp_session['validation']['recs_insert'] as $idx => $flds) {
if (in_array($flds[0], $wrong_recs_ids)) {
array_push($badrecs, $idx);
}
}
$imp_session['validation']['recs_insert'] = array_diff_key($imp_session['validation']['recs_insert'], array_flip($badrecs));
$imp_session['validation']["count_insert"] = count($imp_session['validation']['recs_insert']);
}
}
}
} else {
if ($wrong_records === false) {
return $wrong_records;
}
}
}
//3. In DB: Verify that enumeration fields have correct values =====================================
if (!@$imp_session['csv_enclosure']) {
$imp_session['csv_enclosure'] = $params['csv_enclosure'];
}
if (!@$imp_session['csv_mvsep']) {
$imp_session['csv_mvsep'] = $params['csv_mvsep'];
}
$hwv = " have incorrect values";
$k = 0;
foreach ($query_enum as $field) {
if (true || in_array(intval(substr($field, 6)), $imp_session['multivals'])) {
//this is multivalue field - perform special validation
$query = "select imp_id, " . implode(",", $sel_query) . " from {$import_table} where " . $only_for_specified_id . " 1";
$idx = array_search($field, $sel_query) + 1;
$wrong_records = validateEnumerations($query, $imp_session, $field, $dt_mapping[$field], $idx, $recStruc, $rty_ID, "Term list values read must match existing terms defined for the field", "Invalid Terms");
} else {
$query = "select imp_id, " . implode(",", $sel_query) . " from {$import_table} left join " . $query_enum_join[$k] . " where " . $only_for_specified_id . "(" . $query_enum_where[$k] . ")";
//implode(" or ",$query_enum_where);
$wrong_records = getWrongRecords($query, $imp_session, "Term list values read must match existing terms defined for the field", "Invalid Terms", $field);
}
$k++;
//if($wrong_records) return $wrong_records;
if (is_array($wrong_records)) {
$imp_session = $wrong_records;
} else {
if ($wrong_records === false) {
return $wrong_records;
}
}
}
//4. In DB: Verify resource fields ==================================================
$k = 0;
foreach ($query_res as $field) {
if (true || in_array(intval(substr($field, 6)), $imp_session['multivals'])) {
//this is multivalue field - perform special validation
$query = "select imp_id, " . implode(",", $sel_query) . " from {$import_table} where " . $only_for_specified_id . " 1";
$idx = array_search($field, $sel_query) + 1;
$wrong_records = validateResourcePointers($query, $imp_session, $field, $dt_mapping[$field], $idx, $recStruc, $rty_ID);
} else {
$query = "select imp_id, " . implode(",", $sel_query) . " from {$import_table} left join " . $query_res_join[$k] . " where " . $only_for_specified_id . "(" . $query_res_where[$k] . ")";
//implode(" or ",$query_res_where);
$wrong_records = getWrongRecords($query, $imp_session, "Record pointer field values must reference an existing record in the database", "Invalid Pointers", $field);
}
$k++;
//"Fields mapped as resources(pointers)".$hwv,
if (is_array($wrong_records)) {
$imp_session = $wrong_records;
} else {
if ($wrong_records === false) {
return $wrong_records;
}
}
}
//5. Verify numeric fields
$k = 0;
foreach ($query_num as $field) {
if (in_array(intval(substr($field, 6)), $imp_session['multivals'])) {
//this is multivalue field - perform special validation
$query = "select imp_id, " . implode(",", $sel_query) . " from {$import_table} where " . $only_for_specified_id . " 1";
$idx = array_search($field, $sel_query) + 1;
$wrong_records = validateNumericField($query, $imp_session, $field, $idx);
} else {
$query = "select imp_id, " . implode(",", $sel_query) . " from {$import_table} " . " where " . $only_for_specified_id . "(" . $query_num_where[$k] . ")";
$wrong_records = getWrongRecords($query, $imp_session, "Numeric fields must be pure numbers, they cannot include alphabetic characters or punctuation", "Invalid Numerics", $field);
}
$k++;
// "Fields mapped as numeric".$hwv,
if (is_array($wrong_records)) {
$imp_session = $wrong_records;
} else {
if ($wrong_records === false) {
return $wrong_records;
}
}
}
//6. Verify datetime fields
$k = 0;
foreach ($query_date as $field) {
if (true || in_array(intval(substr($field, 6)), $imp_session['multivals'])) {
//this is multivalue field - perform special validation
$query = "select imp_id, " . implode(",", $sel_query) . " from {$import_table} where " . $only_for_specified_id . " 1";
$idx = array_search($field, $sel_query) + 1;
$wrong_records = validateDateField($query, $imp_session, $field, $idx);
} else {
$query = "select imp_id, " . implode(",", $sel_query) . " from {$import_table} " . " where " . $only_for_specified_id . "(" . $query_date_where[$k] . ")";
//implode(" or ",$query_date_where);
$wrong_records = getWrongRecords($query, $imp_session, "Date values must be in dd-mm-yyyy, dd/mm/yyyy or yyyy-mm-dd formats", "Invalid Dates", $field);
}
$k++;
//"Fields mapped as date".$hwv,
if (is_array($wrong_records)) {
$imp_session = $wrong_records;
} else {
if ($wrong_records === false) {
return $wrong_records;
}
}
}
//7. TODO Verify geo fields
return $imp_session;
}
$response = $system->getError();
} else {
if (@$_REQUEST['a'] == 'minmax') {
$response = recordSearchMinMax($system, $_REQUEST);
} else {
if (@$_REQUEST['a'] == 'getfacets') {
//returns
$response = recordSearchFacets($system, $_REQUEST);
} else {
if (@$_REQUEST['a'] == 'related') {
$response = recordSearchRelated($system, $_REQUEST['ids']);
} else {
// TODO: temporary (for backward compatibility) should be part of all databases
// Check whether recLinks (relationships cache) table exists and create if not
$isok = true;
$value = mysql__select_value($system->get_mysqli(), "SHOW TABLES LIKE 'recLinks'");
if ($value == null || $value == "") {
include dirname(__FILE__) . '/../dbaccess/utils_db_load_script.php';
// used to execute SQL script
if (!db_script(HEURIST_DBNAME_FULL, dirname(__FILE__) . "/../dbaccess/sqlCreateRecLinks.sql")) {
$system->addError(HEURIST_DB_ERROR, "Cannot execute script sqlCreateRecLinks.sql");
$response = $system->getError();
$isok = false;
}
}
if ($isok) {
$response = recordSearch($system, $_REQUEST);
}
}
}
}
/**
* Return full URL to thumbnail for given record ID
*
* @param mixed $system
* @param mixed $recIDs
*/
function fileGetThumbnailURL($system, $recID)
{
$thumb_url = null;
$query = "select recUploadedFiles.ulf_ObfuscatedFileID" . " from recDetails" . " left join recUploadedFiles on ulf_ID = dtl_UploadedFileID" . " left join defFileExtToMimetype on fxm_Extension = ulf_MimeExt" . " where dtl_RecID = {$recID}" . " and (dtl_UploadedFileID is not null)" . " and (fxm_MimeType like 'image%' or ulf_Parameters like '%mediatype=image%')" . " limit 1";
$fileid = mysql__select_value($system->get_mysqli(), $query);
if ($fileid) {
$thumbfile = 'ulf_' . $fileid . '.png';
if (file_exists(HEURIST_THUMB_DIR . $thumbfile)) {
$thumb_url = HEURIST_THUMB_URL . $thumbfile;
} else {
$thumb_url = HEURIST_BASE_URL . "redirects/file_download.php?db=" . HEURIST_DBNAME . "&thumb=" . $fileid;
}
}
return $thumb_url;
}
function predicateField()
{
global $mysqli;
$p = "rd" . $this->qlevel . ".";
$p = "";
if (intval($this->field_id) > 0) {
//find field type
$this->field_type = mysql__select_value($mysqli, 'select dty_Type from defDetailTypes where dty_ID = ' . $this->field_id);
} else {
$this->field_type = 'freetext';
}
$val = $this->getFieldValue();
if (!$val) {
return null;
}
if ($this->field_id == "title") {
$res = "(r" . $this->qlevel . ".rec_Title " . $val . ")";
} else {
if ($this->field_id == "modified") {
$res = "(r" . $this->qlevel . ".rec_Modified " . $val . ")";
} else {
if ($this->field_type == 'date' && trim($this->value) != '') {
//false && $this->isDateTime()){
$field_name = 'getTemporalDateString(' . $p . 'dtl_Value) ';
//$field_name = 'str_to_date(getTemporalDateString('.$p.'dtl_Value), "%Y-%m-%d %H:%i:%s") ';
} else {
$field_name = $p . "dtl_Value ";
}
//old $res = $p."dtl_DetailTypeID=".$this->field_id." AND ".$p."dtl_Value ".$val;
$res = "exists (select dtl_ID from recDetails " . $p . " where r" . $this->qlevel . ".rec_ID=" . $p . "dtl_RecID AND " . $p . "dtl_DetailTypeID=" . $this->field_id . " AND " . $field_name . $val . ")";
}
}
return array("where" => $res);
///"from"=>"recDetails rd",
}
echo $row[0] . " " . $row[1] . " " . $row[4] . "<br>";
$faims_id = $row[0];
$faims_atype = $row[4];
$faims_time = $row[1];
if (@$rectypeMap[$faims_atype]) {
$rectype = $rectypeMap[$faims_atype];
} else {
print "RECORD TYPE NOT FOUND for Vocabulary " . $faims_atype . "<br />";
continue;
}
$details = array();
//add special detail type 2-589 - reference to original record id
if (isset($dt_SourceRecordID) && $dt_SourceRecordID > 0) {
$details["t:" . $dt_SourceRecordID] = array('0' => $faims_id);
//find the existing record in Heurist database
$recID = mysql__select_value($mysqli, "select dtl_RecID from recDetails where dtL_DetailTypeID={$dt_SourceRecordID} and dtl_Value={$faims_id}");
} else {
$recID = 0;
}
$query2 = "SELECT uuid, ValueTimestamp, VocabID, AttributeID, Measure, FreeText, Certainty FROM AEntValue where uuid=" . $faims_id . " and ValueTimestamp='" . $faims_time . "'";
foreach ($dbfaims->query($query2) as $row2) {
//attr id, freetext, measure, certainity, vocabid
echo "<div style='padding-left:30px'>" . $row2[3] . " " . $row2[5] . " " . $row2[4] . " " . $row2[6] . " " . $row2[2] . "</div>";
//detail type
$key = intval(@$detailMap[$row2[3]]);
if ($key > 0) {
$vocabID = $row2[2];
if ($vocabID) {
//vocabID
if (@$termsMap[$vocabID]) {
$value = $termsMap[$vocabID];
function makeSQL()
{
global $mysqli;
$not = $this->parent->negate ? 'not ' : '';
if ($this->nests) {
//special case nested query for resources
$field_value = '';
$nest_joins = '';
$relation_second_level = '';
$relation_second_level_where = '';
if (true) {
//new test version
$isrelmarker_0 = $this->field_type == "relmarker";
$isrelmarker_1 = false;
for ($i = 0; $i < count($this->nests); ++$i) {
$limbs = $this->nests[$i];
$type_clause = null;
$field_type = null;
for ($j = 0; $j < count($limbs); ++$j) {
$cn = get_class($limbs[$j]->pred);
if ($cn == 'TypePredicate') {
$type_clause = $limbs[$j]->pred->makeSQL(false);
// rec_RecTypeID in (12,14)
} else {
if ($cn == 'FieldPredicate') {
if ($i == 0 && $limbs[$j]->pred->field_type == "relmarker") {
//allowed for i==0 only
$isrelmarker_1 = true;
$relation_second_level = ', recRelationshipsCache rel1';
if ($isrelmarker_0) {
$relation_second_level_where = ' and ((rel1.rrc_TargetRecID=rel0.rrc_SourceRecID and rel1.rrc_SourceRecID=link1.rec_ID) ' . 'or (rel1.rrc_SourceRecID=rel0.rrc_TargetRecID and rel1.rrc_TargetRecID=link1.rec_ID))';
} else {
$relation_second_level_where = ' and ((rel1.rrc_TargetRecID=rel0.rrc_SourceRecID and rel1.rrc_SourceRecID=rd.dtl_Value) ' . 'or (rel1.rrc_SourceRecID=rel0.rrc_TargetRecID and rel1.rrc_TargetRecID=rd.dtl_Value))';
}
} else {
$field_type = $limbs[$j]->pred->get_field_type_clause();
if (strpos($field_type, "like") !== false) {
$field_type = " in (select rdt.dty_ID from defDetailTypes rdt where rdt.dty_Name " . $field_type . " limit 1)";
}
if ($limbs[$j]->pred->value) {
$field_value .= ' and linkdt' . $i . '.dtl_Value ' . $limbs[$j]->pred->get_field_value();
}
}
} else {
if ($cn == 'TitlePredicate') {
$field_value .= ' and link' . $i . '.' . $limbs[$j]->pred->makeSQL(false);
} else {
if ($cn == 'DateModifiedPredicate') {
$field_value .= ' and link' . $i . '.' . $limbs[$j]->pred->makeSQL();
$field_value = str_replace("TOPBIBLIO.", "", $field_value);
}
}
}
}
}
//for predicates
if ($type_clause) {
//record type clause is mandatory
$nest_joins .= ' left join Records link' . $i . ' on link' . $i . '.' . $type_clause;
if ($i == 0) {
if (!$isrelmarker_0) {
$nest_joins .= ' and rd.dtl_Value=link0.rec_ID ';
}
} else {
if (!$isrelmarker_1) {
$nest_joins .= ' and linkdt0.dtl_Value=link1.rec_ID ';
}
}
//$nest_joins .= ' and '.($i==0?'rd.dtl_Value':'linkdt'.($i-1).'.dtl_Value').'=link'.$i.'.rec_ID '; //STRCMP('.($i==0?'rd.dtl_Value':'linkdt'.($i-1).'.dtl_Value').',link'.$i.'.rec_ID)=0
if ($field_type) {
$nest_joins .= ' left join recDetails linkdt' . $i . ' on linkdt' . $i . '.dtl_RecID=link' . $i . '.rec_ID and linkdt' . $i . '.dtl_DetailTypeID ' . $field_type;
}
} else {
return '';
//fail - record type is mandatory for nested queries
}
}
//for nests
if ($isrelmarker_0) {
$resq = $not . 'exists (select rel0.rrc_TargetRecID, rel0.rrc_SourceRecID from recRelationshipsCache rel0 ' . $relation_second_level . $nest_joins . ' where ((rel0.rrc_TargetRecID=TOPBIBLIO.rec_ID and rel0.rrc_SourceRecID=link0.rec_ID)' . ' or (rel0.rrc_SourceRecID=TOPBIBLIO.rec_ID and rel0.rrc_TargetRecID=link0.rec_ID)) ' . $relation_second_level_where . $field_value . ')';
} else {
$rd_type_clause = '';
$rd_type_clause = $this->get_field_type_clause();
if (strpos($rd_type_clause, "like") === false) {
$rd_type_clause = " and rd.dtl_DetailTypeID " . $rd_type_clause;
} else {
$rd_type_clause = " and rd.dtl_DetailTypeID in (select rdt.dty_ID from defDetailTypes rdt where rdt.dty_Name " . $rd_type_clause . " limit 1)";
}
$resq = $not . 'exists (select rd.dtl_ID from recDetails rd ' . $relation_second_level . $nest_joins . ' where rd.dtl_RecID=TOPBIBLIO.rec_ID ' . $relation_second_level_where . $field_value . $rd_type_clause . ')';
}
} else {
//working copy!!!!
for ($i = 0; $i < count($this->nests); ++$i) {
$limbs = $this->nests[$i];
$type_clause = null;
$field_type = null;
for ($j = 0; $j < count($limbs); ++$j) {
$cn = get_class($limbs[$j]->pred);
if ($cn == 'TypePredicate') {
$type_clause = $limbs[$j]->pred->makeSQL(false);
} else {
if ($cn == 'FieldPredicate') {
$field_type = $limbs[$j]->pred->get_field_type_clause();
if (strpos($field_type, "like") !== false) {
$field_type = " in (select rdt.dty_ID from defDetailTypes rdt where rdt.dty_Name " . $field_type . " limit 1)";
}
if ($limbs[$j]->pred->value) {
$field_value .= ' and linkdt' . $i . '.dtl_Value ' . $limbs[$j]->pred->get_field_value();
}
} else {
if ($cn == 'TitlePredicate') {
$field_value .= ' and link' . $i . '.' . $limbs[$j]->pred->makeSQL(false);
} else {
if ($cn == 'DateModifiedPredicate') {
$field_value .= ' and link' . $i . '.' . $limbs[$j]->pred->makeSQL();
$field_value = str_replace("TOPBIBLIO.", "", $field_value);
}
}
}
}
}
//for predicates
if ($type_clause) {
//record type clause is mandatory STRCMP('.($i==0?'rd.dtl_Value':'linkdt'.($i-1).'.dtl_Value').',link'.$i.'.rec_ID)=0
$nest_joins .= ' left join Records link' . $i . ' on ' . ($i == 0 ? 'rd.dtl_Value' : 'linkdt' . ($i - 1) . '.dtl_Value') . '=link' . $i . '.rec_ID and link' . $i . '.' . $type_clause;
if ($field_type) {
$nest_joins .= ' left join recDetails linkdt' . $i . ' on linkdt' . $i . '.dtl_RecID=link' . $i . '.rec_ID and linkdt' . $i . '.dtl_DetailTypeID ' . $field_type;
}
} else {
return '';
//fail - record type is mandatory for nested queries
}
}
//for nests
$rd_type_clause = '';
$rd_type_clause = $this->get_field_type_clause();
if (strpos($rd_type_clause, "like") === false) {
$rd_type_clause = " and rd.dtl_DetailTypeID " . $rd_type_clause;
} else {
$rd_type_clause = " and rd.dtl_DetailTypeID in (select rdt.dty_ID from defDetailTypes rdt where rdt.dty_Name " . $rd_type_clause . " limit 1)";
}
$resq = $not . 'exists (select rd.dtl_ID from recDetails rd ' . $nest_joins . ' where rd.dtl_RecID=TOPBIBLIO.rec_ID ' . $field_value . $rd_type_clause . ')';
}
return $resq;
}
//end special case nested query for resources
if (preg_match('/^\\d+$/', $this->field_type)) {
$dt_query = "select rdt.dty_Type from defDetailTypes rdt where rdt.dty_ID = " . intval($this->field_type);
$this->field_type_value = mysql__select_value($mysqli, $dt_query);
} else {
$this->field_type_value = '';
}
$match_pred = $this->get_field_value();
if (preg_match('/^\\d+(?:,\\d+)+$/', $this->value)) {
$isnumericvalue = false;
$isin = true;
} else {
$isin = false;
$isnumericvalue = is_numeric($this->value);
}
/*
if($isin){
$match_pred_for_term = $match_pred;
}else if($isnumericvalue){
$match_pred_for_term = $match_pred; //" = $match_value";
}else{
$match_pred_for_term = " = trm.trm_ID";
}*/
$timestamp = $isin ? false : true;
//$this->isDateTime();
if ($timestamp) {
$date_match_pred = $this->makeDateClause();
}
if ($this->field_type_value == 'resource') {
//field type is found - search for specific detailtype
return $not . 'exists (select rd.dtl_ID from recDetails rd ' . ' left join Records link on rd.dtl_Value=link.rec_ID ' . ' where rd.dtl_RecID=TOPBIBLIO.rec_ID and rd.dtl_DetailTypeID=' . intval($this->field_type) . ' and ' . ($isnumericvalue ? 'rd.dtl_Value ' : ' link.rec_Title ') . $match_pred . ')';
} else {
if ($this->field_type_value == 'enum' || $this->field_type_value == 'relationtype') {
return $not . 'exists (select rd.dtl_ID from recDetails rd ' . ' where rd.dtl_RecID=TOPBIBLIO.rec_ID ' . ' and rd.dtl_DetailTypeID=' . intval($this->field_type) . ' and rd.dtl_Value ' . $match_pred . ')';
} else {
if ($this->field_type_value == 'date') {
$res = $not . 'exists (select rd.dtl_ID from recDetails rd ' . ' where rd.dtl_RecID=TOPBIBLIO.rec_ID ' . ' and rd.dtl_DetailTypeID=' . intval($this->field_type);
if (trim($this->value) == '') {
$res = $res . " and rd.dtl_Value !='' )";
} else {
$res = $res . ' and getTemporalDateString(rd.dtl_Value) ' . $date_match_pred . ')';
}
return $res;
} else {
if ($this->field_type_value) {
return $not . 'exists (select rd.dtl_ID from recDetails rd ' . ' where rd.dtl_RecID=TOPBIBLIO.rec_ID ' . ' and rd.dtl_DetailTypeID=' . intval($this->field_type) . ' and rd.dtl_Value ' . $match_pred . ')';
} else {
$rd_type_clause = $this->get_field_type_clause();
if (strpos($rd_type_clause, "like") === false) {
//several field type
$rd_type_clause = " and rd.dtl_DetailTypeID " . $rd_type_clause;
} else {
if ($rd_type_clause == 'like "%"') {
//any field type
$rd_type_clause = '';
} else {
$rd_type_clause = " and rdt.dty_Name " . $rd_type_clause;
}
}
return $not . 'exists (select rd.dtl_ID from recDetails rd ' . 'left join defDetailTypes rdt on rdt.dty_ID=rd.dtl_DetailTypeID ' . 'left join Records link on rd.dtl_Value=link.rec_ID ' . 'where rd.dtl_RecID=TOPBIBLIO.rec_ID ' . ' and if(rdt.dty_Type = "resource" AND ' . ($isnumericvalue ? '0' : '1') . ', ' . 'link.rec_Title ' . $match_pred . ', ' . 'if(rdt.dty_Type in ("enum","relationtype"), rd.dtl_Value ' . $match_pred_for_term . ', ' . ($timestamp ? 'if(rdt.dty_Type = "date", ' . 'getTemporalDateString(rd.dtl_Value) ' . $date_match_pred . ', ' . 'rd.dtl_Value ' . $match_pred . ')' : 'rd.dtl_Value ' . $match_pred) . '))' . $rd_type_clause . ')';
}
}
}
}
}
/**
* Save saved searches tree data into sysUGrps
*/
function svsSaveTreeData($system, $data)
{
$mysqli = $system->get_mysqli();
$groups = json_decode($data, true);
$personal_data = array();
$ugrID = $system->get_user_id();
$ugr_groups = $system->get_user_group_ids();
$lastID = null;
foreach ($groups as $id => $treedata) {
if ($id == "bookmark" || $id == "all") {
array_push($personal_data, '"' . $id . '":' . json_encode($treedata));
} else {
if (in_array($id, $ugr_groups)) {
//check date of modification
$res = mysql__insertupdate($mysqli, 'sysUGrps', 'ugr', array('ugr_ID' => $id, 'ugr_NavigationTree' => json_encode($treedata)));
if (!is_int($res)) {
$system->addError(HEURIST_DB_ERROR, 'Cannot update navigation tree (personal) on server sode', $res);
return false;
}
$lastID = $id;
}
}
}
if (count($personal_data) > 0) {
$res = mysql__insertupdate($mysqli, 'sysUGrps', 'ugr', array('ugr_ID' => $ugrID, 'ugr_NavigationTree' => implode(',', $personal_data)));
if (!is_int($res)) {
$system->addError(HEURIST_DB_ERROR, 'Cannot update navigation tree (personal) on server sode', $res);
return false;
}
$lastID = $ugrID;
}
if ($lastID > 0) {
//get modification time
$date = mysql__select_value($mysqli, 'SELECT `ugr_Modified` FROM `sysUGrps` WHERE ugr_ID=' . $lastID);
return $date;
}
$system->addError(HEURIST_INVALID_REQUEST, 'No data provided to update tree on server side');
return false;
}
