//$currentUser = $system->getCurrentUser();
$data = array();
if (@$_REQUEST['terms']) {
$data["terms"] = dbs_GetTerms($system);
}
if (@$_REQUEST['detailtypes']) {
$ids = $_REQUEST['detailtypes'] == 'all' ? null : $_REQUEST['detailtypes'];
$data["detailtypes"] = dbs_GetDetailTypes($system, $ids, intval(@$_REQUEST['mode']));
}
if (@$_REQUEST['rectypes']) {
$ids = $_REQUEST['rectypes'] == 'all' ? null : $_REQUEST['rectypes'];
$mode = intval(@$_REQUEST['mode']);
if ($mode > 2) {
$data["rectypes"] = dbs_GetRectypeStructureTree($system, $ids, $mode, @$_REQUEST['fieldtypes']);
} else {
$data["rectypes"] = dbs_GetRectypeStructures($system, $ids, $mode);
}
}
$response = array("status" => HEURIST_OK, "data" => $data);
}
/*
if ( extension_loaded('zlib') && (substr_count($_SERVER['HTTP_ACCEPT_ENCODING'], 'gzip')) )
{
ob_start('ob_gzhandler');
}*/
/*
ini_set("zlib.output_compression", 4096);
ini_set("zlib.output_compression_level", 6);
header('Content-type: text/javascript');
print json_encode($response);
*/
function __getRecordTypeTree($system, $recTypeId, $recursion_depth, $mode, $fieldtypes)
{
global $dbs_rtStructs;
$res = array();
$children = array();
//add default fields
if ($mode < 5) {
if ($mode == 3) {
array_push($children, array('key' => 'recID', 'type' => 'integer', 'title' => 'ID', 'code' => $recTypeId . ":id"));
}
array_push($children, array('key' => 'recTitle', 'type' => 'freetext', 'title' => "RecTitle <span style='font-size:0.7em'>(Constructed text)</span>", 'code' => $recTypeId . ":title", 'name' => 'Record title'));
array_push($children, array('key' => 'recModified', 'type' => 'date', 'title' => "Modified <span style='font-size:0.7em'>(Date)</span>", 'code' => $recTypeId . ":modified", 'name' => 'Record modified'));
if ($mode == 3) {
array_push($children, array('key' => 'recURL', 'type' => 'freetext', 'title' => 'URL', 'code' => $recTypeId . ":url"));
array_push($children, array('key' => 'recWootText', 'type' => 'blocktext', 'title' => 'WootText', 'code' => $recTypeId . ":woot"));
}
}
if ($recTypeId && is_numeric($recTypeId)) {
if (!@$dbs_rtStructs['typedefs'][$recTypeId]) {
//this rectype is not loaded yet - load it
$rt0 = dbs_GetRectypeStructures($system, $recTypeId, 1);
if ($rt0) {
//merge with $dbs_rtStructs
$dbs_rtStructs['typedefs'][$recTypeId] = $rt0['typedefs'][$recTypeId];
$dbs_rtStructs['names'][$recTypeId] = $rt0['names'][$recTypeId];
} else {
return null;
}
}
$res['key'] = $recTypeId;
$res['title'] = $dbs_rtStructs['names'][$recTypeId];
$res['type'] = 'rectype';
if (@$dbs_rtStructs['typedefs'][$recTypeId]) {
$details = $dbs_rtStructs['typedefs'][$recTypeId]['dtFields'];
$children_links = array();
foreach ($details as $dtID => $dtValue) {
$res_dt = __getDetailSection($system, $recTypeId, $dtID, $recursion_depth, $mode, $fieldtypes, null);
if ($res_dt) {
if ($res_dt['type'] == 'resource' || $res_dt['type'] == 'relmarker') {
array_push($children_links, $res_dt);
} else {
array_push($children, $res_dt);
}
/*
if(is_array($res_dt) && count($res_dt)==1){
$res["f".$dtID] = $res_dt[0];
}else{
//multi-constrained pointers or simple variable
$res["f".$dtID] = $res_dt;
}
*/
}
}
//for
//add resource and relation at the end of result array
$children = array_merge($children, $children_links);
//find all reverse links and relations
if ($mode == 4) {
//&& $recursion_depth==0){
$reverse_rectypes = __getReverseLinkedRecordTypes($recTypeId);
foreach ($reverse_rectypes as $rtID => $dtID) {
//$dtValue = $dbs_rtStructs['typedefs'][$rtID]['dtFields'][$dtID];
$res_dt = __getDetailSection($system, $rtID, $dtID, $recursion_depth, $mode, $fieldtypes, $recTypeId);
if ($res_dt) {
array_push($children, $res_dt);
}
}
}
}
if ($mode == 3 && $recursion_depth == 0) {
array_push($children, __getRecordTypeTree($system, 'Relationship', $recursion_depth + 1, $mode, $fieldtypes));
}
} else {
if ($recTypeId == "Relationship") {
$res['title'] = "Relationship";
$res['type'] = "relationship";
//add specific Relationship fields
array_push($children, array('key' => 'recRelationType', 'title' => 'RelationType'));
array_push($children, array('key' => 'recRelationNotes', 'title' => 'RelationNotes'));
array_push($children, array('key' => 'recRelationStartDate', 'title' => 'RelationStartDate'));
array_push($children, array('key' => 'recRelationEndDate', 'title' => 'RelationEndDate'));
}
}
$res['children'] = $children;
return $res;
}
/**
* Return record structure for given id and update $recstructures array
*
* @param mixed $system
* @param mixed $recstructures
* @param mixed $rectype
*/
function dbs_GetRectypeStructure($system, &$recstructures, $rectype)
{
if (!@$recstructures[$rectype]) {
//load rectype
$res = dbs_GetRectypeStructures($system, $rectype, 1);
if (!@$recstructures['commonNamesToIndex']) {
$recstructures['commonNamesToIndex'] = $res['typedefs']['commonNamesToIndex'];
$recstructures['dtFieldNamesToIndex'] = $res['typedefs']['dtFieldNamesToIndex'];
}
$recstructures[$rectype] = @$res['typedefs'][$rectype];
}
return @$recstructures[$rectype];
}
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<link rel="stylesheet" href="../../ext/jquery-ui-1.10.2/themes/base/jquery-ui.css" />
<link rel="stylesheet" type="text/css" href="../../style.css">
<script type="text/javascript" src="../../ext/jquery-ui-1.10.2/jquery-1.9.1.js"></script>
<script type="text/javascript" src="../../ext/jquery-ui-1.10.2/ui/jquery-ui.js"></script>
<script type="text/javascript" src="../../core/recordset.js"></script>
<script type="text/javascript" src="../../core/hapi.js"></script>
<script type="text/javascript" src="../../core/utils.js"></script>
<script type="text/javascript">
<?php
//@ todo - load this stuff in hEditing
print "window.hWin.HEURIST4.rectypes = " . json_encode(dbs_GetRectypeStructures($system, null, 2)) . ";\n";
print "window.hWin.HEURIST4.terms = " . json_encode(dbs_GetTerms($system)) . ";\n";
?>
var recordtype;
//localization stub
window.hWin.HR = function(str){
return str;
}
$(document).ready(function() {
if(!window.hWin.HAPI4){
window.hWin.HAPI4 = new hAPI('<?php
echo $_REQUEST['db'];
?>
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;
}
/**
* put your comment there...
*
* @param mixed $system
* @param mixed $params
*
* FOR RULES
* rules - rules queries - to search related records on server side
* getrelrecs (=1) - search relationship records (along with related) on server side
* topids - list of records ids, it is used to compose 'parentquery' parameter to use in rules (@todo - replace with new rules algorithm)
*
* INTERNAL/recursive
* parentquery - sql expression to substiture in rule query
* sql - sql expression to execute (used as recursive parameters to search relationship records)
*
* SEARCH parameters that are used to compose sql expression
* q - query string (old mode) or json array (new mode)
* w (=all|bookmark a|b) - search among all or bookmarked records
* limit - limit for sql query is set explicitely on client side
* offset - offset parameter value for sql query
* s - sort order
*
* OUTPUT parameters
* vo (=h3) - output format in h3 for backward capability (for detail=ids only)
* needall (=1) - by default it returns only first 3000, to return all set it to 1,
* it is set to 1 for server-side rules searches
* publiconly (=1) - ignore current user and returns only public records
*
* detail (former 'f') - ids - only record ids
* header - record header
* timemap - record header + timemap details
* detail - record header + all details
* structure - record header + all details + record type structure (for editing) - NOT USED
*
* CLIENT SIDE
* id - unque id to sync with client side
* source - id of html element that is originator of this search
* qname - original name of saved search (for messaging)
*/
function recordSearch($system, $params)
{
//for error message
$savedSearchName = @$params['qname'] ? "Saved search: " . $params['qname'] . "<br>" : "";
if (!@$params['detail']) {
$params['detail'] = @$params['f'];
//backward capability
}
$istimemap_request = @$params['detail'] == 'timemap';
$istimemap_counter = 0;
//total records with timemap data
$fieldtypes_ids = null;
if ($istimemap_request) {
//get date,year and geo fields from structure
$fieldtypes_ids = dbs_GetDetailTypes($system, array('date', 'year', 'geo'), 3);
if ($fieldtypes_ids == null || count($fieldtypes_ids) == 0) {
$fieldtypes_ids = array(DT_GEO_OBJECT, DT_DATE, DT_START_DATE, DT_END_DATE);
//9,10,11,28';
}
$fieldtypes_ids = implode(',', $fieldtypes_ids);
} else {
if (!in_array(@$params['detail'], array('header', 'timemap', 'detail', 'structure'))) {
//specific set of detail fields
if (is_array($params['detail'])) {
$fieldtypes_ids = $params['detail'];
} else {
$fieldtypes_ids = explode(',', $params['detail']);
}
if (is_array($fieldtypes_ids) && (count($fieldtypes_ids) > 1 || is_numeric($fieldtypes_ids[0]))) {
$fieldtypes_ids = implode(',', $fieldtypes_ids);
$params['detail'] = 'detail';
} else {
$fieldtypes_ids = null;
$params['detail'] = 'ids';
}
}
}
$is_ids_only = 'ids' == $params['detail'];
$return_h3_format = @$params['vo'] == 'h3' && $is_ids_only;
if (null == $system) {
$system = new System();
if (!$system->init(@$_REQUEST['db'])) {
$response = $system->getError();
if ($return_h3_format) {
$response['error'] = $response['message'];
}
return $response;
}
}
$mysqli = $system->get_mysqli();
$currentUser = $system->getCurrentUser();
if ($system->get_user_id() < 1) {
$params['w'] = 'all';
//does not allow to search bookmarks if not logged in
}
if ($is_ids_only) {
$select_clause = 'select SQL_CALC_FOUND_ROWS DISTINCT rec_ID ';
} else {
$select_clause = 'select SQL_CALC_FOUND_ROWS DISTINCT ' . 'bkm_ID,' . 'bkm_UGrpID,' . 'rec_ID,' . 'rec_URL,' . 'rec_RecTypeID,' . 'rec_Title,' . 'rec_OwnerUGrpID,' . 'rec_NonOwnerVisibility,' . 'bkm_PwdReminder ';
/*.'rec_URLLastVerified,'
.'rec_URLErrorMessage,'
.'bkm_PwdReminder ';*/
}
if ($currentUser && @$currentUser['ugr_ID'] > 0) {
$currUserID = $currentUser['ugr_ID'];
} else {
$currUserID = 0;
$params['w'] = 'all';
}
if (@$params['topids']) {
//if topids are defined we use them as starting point for following rule query
// it is used for incremental client side only
//@todo - implement it in different way - substitute topids to query json as predicate ids:
$query_top = array();
if (strcasecmp(@$params['w'], 'B') == 0 || strcasecmp(@$params['w'], 'bookmark') == 0) {
$query_top['from'] = 'FROM usrBookmarks TOPBKMK LEFT JOIN Records TOPBIBLIO ON bkm_recID=rec_ID ';
} else {
$query_top['from'] = 'FROM Records TOPBIBLIO LEFT JOIN usrBookmarks TOPBKMK ON bkm_recID=rec_ID and bkm_UGrpID=' . $currUserID . ' ';
}
$query_top['where'] = "(TOPBIBLIO.rec_ID in (" . $params['topids'] . "))";
$query_top['sort'] = '';
$query_top['limit'] = '';
$query_top['offset'] = '';
$params['parentquery'] = $query_top;
//parentquery parameter is used in get_sql_query_clauses
} else {
if (@$params['rules']) {
//special case - server side operation
// rules - JSON array the same as stored in saved searches table
if (is_array(@$params['rules'])) {
$rules_tree = $params['rules'];
} else {
$rules_tree = json_decode($params['rules'], true);
}
$flat_rules = array();
$flat_rules[0] = array();
//create flat rule array
$rules = _createFlatRule($flat_rules, $rules_tree, 0);
//find result for main query
unset($params['rules']);
if (@$params['limit']) {
unset($params['limit']);
}
if (@$params['offset']) {
unset($params['offset']);
}
if (@$params['vo']) {
unset($params['vo']);
}
$params['needall'] = 1;
//return all records
$resSearch = recordSearch($system, $params);
$keepMainSet = true;
if ($keepMainSet) {
//find main query results
$fin_result = $resSearch;
//main result set
$flat_rules[0]['results'] = $is_ids_only ? $fin_result['data']['records'] : array_keys($fin_result['data']['records']);
//get ids
} else {
//empty main result set
//remove from $fin_result! but keep in $flat_rules[0]['results']?
}
$is_get_relation_records = @$params['getrelrecs'] == 1;
//get all related and relationship records
foreach ($flat_rules as $idx => $rule) {
if ($idx == 0) {
continue;
}
$is_last = @$rule['islast'] == 1;
//create request
$params['q'] = $rule['query'];
$parent_ids = $flat_rules[$rule['parent']]['results'];
//list of record ids of parent resultset
$rule['results'] = array();
//reset
//split by 3000 - search based on parent ids (max 3000)
$k = 0;
while ($k < count($parent_ids)) {
//$need_details2 = $need_details && ($is_get_relation_records || $is_last);
$params3 = $params;
$params3['topids'] = implode(",", array_slice($parent_ids, $k, 3000));
if (!$is_last) {
//($is_get_relation_records ||
//$params3['detail'] = 'ids'; //no need in details for preliminary results ???????
}
$response = recordSearch($system, $params3);
if ($response['status'] == HEURIST_OK) {
//merge with final results
if ($is_ids_only) {
$fin_result['data']['records'] = array_merge_unique($fin_result['data']['records'], $response['data']['records']);
} else {
$fin_result['data']['records'] = mergeRecordSets($fin_result['data']['records'], $response['data']['records']);
$fin_result['data']['order'] = array_merge($fin_result['data']['order'], array_keys($response['data']['records']));
foreach (array_keys($response['data']['records']) as $rt) {
$rectype_id = @$rt['4'];
if ($rectype_id) {
/*if(@$fin_result['data']['rectypes'][$rectype_id]){
$fin_result['data']['rectypes'][$rectype_id]++;
}else{
$fin_result['data']['rectypes'][$rectype_id]=1;
}*/
if (!array_key_exists($rectype_id, $fin_result['data']['rectypes'])) {
$fin_result['data']['rectypes'][$rectype_id] = 1;
}
}
}
}
if (!$is_last) {
//add top ids for next level
$flat_rules[$idx]['results'] = array_merge_unique($flat_rules[$idx]['results'], $is_ids_only ? $response['data']['records'] : array_keys($response['data']['records']));
}
if ($is_get_relation_records && (strpos($params3['q'], "related_to") > 0 || strpos($params3['q'], "relatedfrom") > 0)) {
//find relation records (recType=1)
//create query to search related records
if (strcasecmp(@$params3['w'], 'B') == 0 || strcasecmp(@$params3['w'], 'bookmark') == 0) {
$from = 'FROM usrBookmarks TOPBKMK LEFT JOIN Records TOPBIBLIO ON bkm_recID=rec_ID ';
} else {
$from = 'FROM Records TOPBIBLIO LEFT JOIN usrBookmarks TOPBKMK ON bkm_recID=rec_ID and bkm_UGrpID=' . $currUserID . ' ';
}
if (strpos($params3['q'], "related_to") > 0) {
$fld2 = "rl_SourceID";
$fld1 = "rl_TargetID";
} else {
$fld1 = "rl_SourceID";
$fld2 = "rl_TargetID";
}
$where = "WHERE (TOPBIBLIO.rec_ID in (select rl_RelationID from recLinks where (rl_RelationID is not null) and {$fld1} in (" . $params3['topids'] . ") and {$fld2} in (" . implode(",", $is_ids_only ? $response['data']['records'] : array_keys($response['data']['records'])) . ")))";
$params2 = $params3;
unset($params2['topids']);
unset($params2['q']);
$params2['sql'] = $select_clause . $from . $where;
$response = recordSearch($system, $params2);
//search for relationship records
if ($response['status'] == HEURIST_OK) {
if (!@$fin_result['data']['relationship']) {
$fin_result['data']['relationship'] = array();
}
if ($is_ids_only) {
$fin_result['data']['relationship'] = array_merge_unique($fin_result['data']['relationship'], $response['data']['records']);
} else {
$fin_result['data']['relationship'] = mergeRecordSets($fin_result['data']['relationship'], $response['data']['records']);
}
/*merge with final results
if($is_ids_only){
$fin_result['data']['records'] = array_merge($fin_result['data']['records'], $response['data']['records']);
}else{
$fin_result['data']['records'] = mergeRecordSets($fin_result['data']['records'], $response['data']['records']);
$fin_result['data']['order'] = array_merge($fin_result['data']['order'], array_keys($response['data']['records']));
$fin_result['data']['rectypes'][1] = 1;
}
*/
}
}
//$is_get_relation_records
} else {
//@todo terminate execution and return error
}
$k = $k + 3000;
}
//while chunks
}
//for rules
if ($is_ids_only) {
//$fin_result['data']['records'] = array_unique($fin_result['data']['records']);
}
$fin_result['data']['count'] = count($fin_result['data']['records']);
if ($return_h3_format) {
$fin_result = array("resultCount" => $fin_result['data']['count'], "recordCount" => $fin_result['data']['count'], "recIDs" => implode(",", $fin_result['data']['records']));
}
//@todo - assign if size less than 3000? only
$fin_result['data']['mainset'] = $flat_rules[0]['results'];
return $fin_result;
}
}
//END RULES
$chunk_size = PHP_INT_MAX;
if (@$params['sql']) {
$query = $params['sql'];
} else {
$is_mode_json = false;
if (@$params['q']) {
if (is_array(@$params['q'])) {
$query_json = $params['q'];
//DEBUG error_log('Q='.print_r($params['q'],true));
} else {
$query_json = json_decode(@$params['q'], true);
}
if (is_array($query_json) && count($query_json) > 0) {
$params['q'] = $query_json;
$is_mode_json = true;
}
} else {
return $system->addError(HEURIST_INVALID_REQUEST, $savedSearchName . "Invalid search request. Missed query parameter 'q'");
}
if ($is_mode_json) {
$aquery = get_sql_query_clauses_NEW($mysqli, $params, $currentUser);
} else {
$aquery = get_sql_query_clauses($mysqli, $params, $currentUser);
//!!!! IMPORTANT CALL OR compose_sql_query at once
}
if ($is_ids_only && @$params['needall']) {
$chunk_size = PHP_INT_MAX;
$aquery["limit"] = '';
} else {
$chunk_size = $system->user_GetPreference('search_detail_limit');
//limit for map/timemap output
}
if (!isset($aquery["where"]) || trim($aquery["where"]) === '') {
return $system->addError(HEURIST_DB_ERROR, "Invalid search request; unable to construct valid SQL query", null);
}
$query = $select_clause . $aquery["from"] . " WHERE " . $aquery["where"] . $aquery["sort"] . $aquery["limit"] . $aquery["offset"];
//error_log($is_mode_json.' '.$query);
/* DEBUG
if($params['q']=='doerror'){ //force error
$query ='abracadabra';
}
*/
}
$res = $mysqli->query($query);
if (!$res) {
$response = $system->addError(HEURIST_DB_ERROR, $savedSearchName . 'Search query error', $mysqli->error);
} else {
$fres = $mysqli->query('select found_rows()');
if (!$fres) {
$response = $system->addError(HEURIST_DB_ERROR, $savedSearchName . 'Search query error (retrieving number of records)', $mysqli->error);
} else {
$total_count_rows = $fres->fetch_row();
$total_count_rows = $total_count_rows[0];
$fres->close();
if ($is_ids_only) {
//------------------------ LOAD and RETURN only IDS
$records = array();
while ($row = $res->fetch_row()) {
array_push($records, (int) $row[0]);
}
$res->close();
if (@$params['vo'] == 'h3') {
//output version
$response = array('resultCount' => $total_count_rows, 'recordCount' => count($records), 'recIDs' => implode(',', $records));
} else {
$response = array('status' => HEURIST_OK, 'data' => array('queryid' => @$params['id'], 'count' => $total_count_rows, 'offset' => get_offset($params), 'reccount' => count($records), 'records' => $records));
}
} else {
//----------------------------------
// read all field names
$_flds = $res->fetch_fields();
$fields = array();
foreach ($_flds as $fld) {
array_push($fields, $fld->name);
}
array_push($fields, 'rec_ThumbnailURL');
//array_push($fields, 'rec_Icon'); //last one -icon ID
$rectype_structures = array();
$rectypes = array();
$records = array();
$order = array();
// load all records
while ($row = $res->fetch_row()) {
//3000 maximal allowed chunk
array_push($row, $fieldtypes_ids ? '' : fileGetThumbnailURL($system, $row[2]));
//array_push( $row, $row[4] ); //by default icon if record type ID
$records[$row[2]] = $row;
array_push($order, $row[2]);
if (!@$rectypes[$row[4]]) {
$rectypes[$row[4]] = 1;
}
}
$res->close();
if (($istimemap_request || $params['detail'] == 'detail' || $params['detail'] == 'structure') && count($records) > 0) {
//search for specific details
if (!$fieldtypes_ids && $fieldtypes_ids != '') {
$detail_query = 'select dtl_RecID,' . 'dtl_DetailTypeID,' . 'dtl_Value,' . 'AsWKT(dtl_Geo), 0, 0, 0 ' . 'from recDetails
where dtl_RecID in (' . join(',', array_keys($records)) . ') ' . ' and dtl_DetailTypeID in (' . $fieldtypes_ids . ')';
} else {
$detail_query = 'select dtl_RecID,' . 'dtl_DetailTypeID,' . 'dtl_Value,' . 'AsWKT(dtl_Geo),' . 'dtl_UploadedFileID,' . 'recUploadedFiles.ulf_ObfuscatedFileID,' . 'recUploadedFiles.ulf_Parameters ' . 'from recDetails
left join recUploadedFiles on ulf_ID = dtl_UploadedFileID
where dtl_RecID in (' . join(',', array_keys($records)) . ')';
}
// @todo - we may use getAllRecordDetails
$res_det = $mysqli->query($detail_query);
if (!$res_det) {
$response = $system->addError(HEURIST_DB_ERROR, $savedSearchName . 'Search query error (retrieving details)', $mysqli->error);
return $response;
} else {
while ($row = $res_det->fetch_row()) {
$recID = array_shift($row);
if (!array_key_exists('d', $records[$recID])) {
$records[$recID]['d'] = array();
}
$dtyID = $row[0];
$val = null;
if ($row[2]) {
$val = $row[1] . ' ' . $row[2];
//dtl_Geo @todo convert to JSON
} else {
if ($row[3]) {
$val = array($row[4], $row[5]);
//obfuscated value for fileid
} else {
if (@$row[1]) {
$val = $row[1];
}
}
}
if ($val) {
if (!array_key_exists($dtyID, $records[$recID]['d'])) {
$records[$recID]['d'][$dtyID] = array();
}
array_push($records[$recID]['d'][$dtyID], $val);
}
}
//while
$res_det->close();
///@todo
// 1. optimize loop - include into main detail loop
// 2. exit loop if more than 5000 geo enabled
// 3. return geojson and timeline items
//additional loop for timemap request
//1. exclude records without timemap data
//2. limit to $chunk_size
if ($istimemap_request) {
$tm_records = array();
$order = array();
$rectypes = array();
foreach ($records as $recID => $record) {
if (is_array(@$record['d']) && count($record['d']) > 0) {
//this record is time enabled
if ($istimemap_counter < $chunk_size) {
$tm_records[$recID] = $record;
array_push($order, $recID);
$rectypes[$record[4]] = 1;
}
$istimemap_counter++;
}
}
$records = $tm_records;
$total_count_rows = $istimemap_counter;
}
//$istimemap_request
}
}
//$need_details
$rectypes = array_keys($rectypes);
if ($params['detail'] == 'structure' && count($rectypes) > 0) {
//rarely used in editing.js
//description of recordtype and used detail types
$rectype_structures = dbs_GetRectypeStructures($system, $rectypes, 1);
//no groups
}
//"query"=>$query,
$response = array('status' => HEURIST_OK, 'data' => array('queryid' => @$params['id'], 'count' => $total_count_rows, 'offset' => get_offset($params), 'reccount' => count($records), 'fields' => $fields, 'records' => $records, 'order' => $order, 'rectypes' => $rectypes, 'structures' => $rectype_structures));
if ($fieldtypes_ids) {
$response['data']['fields_detail'] = explode(',', $fieldtypes_ids);
}
}
//$is_ids_only
}
}
return $response;
}