function validateImport($mysqli, $imp_session, $params)
{
$is_json = $params["action"] == 'step4';
//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());
//get rectype to import
$recordType = @$params['sa_rectype'];
$currentSeqIndex = @$params['seq_index'];
$sequence = null;
if ($currentSeqIndex >= 0 && @$imp_session['sequence'] && is_array(@$imp_session['sequence'][$currentSeqIndex])) {
$sequence = $imp_session['sequence'][$currentSeqIndex];
}
if (intval($recordType) < 1) {
return "record type not defined";
}
$import_table = $imp_session['import_table'];
$id_field = @$params['recid_field'];
//record ID field is always defined explicitly
$ignore_insert = @$params['ignore_insert'] == 1;
//ignore new records
$cnt_update_rows = 0;
$cnt_insert_rows = 0;
//get field mapping and selection query from _REQUEST(params)
if (@$params['mapping']) {
//new way
$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 {
return 'Mapping is not defined';
}
} else {
$mapping = array();
// fieldtype ID => fieldname in import table
$mapped_fields = array();
$sel_query = array();
foreach ($params as $key => $field_type) {
if (strpos($key, "sa_dt_") === 0 && $field_type) {
//get index
$index = substr($key, 6);
$field_name = "field_" . $index;
//all mapped fields - they will be used in validation query
array_push($sel_query, $field_name);
$mapping[$field_type] = $field_name;
$mapped_fields[$field_name] = $field_type;
}
}
if (count($sel_query) < 1) {
return "mapping not defined";
}
$imp_session['validation']['mapped_fields'] = $mapped_fields;
}
//error_log(' validation upd:'.$cnt_update_rows.' to insert '.$cnt_insert_rows);
// calculate the number of records to insert, update and insert with existing ids
// @todo - it has been implemented for non-multivalue indexes only
if (!$id_field) {
//ID field not defined - all records will be inserted
if (!$ignore_insert) {
$imp_session['validation']["count_insert"] = $imp_session['reccount'];
$imp_session['validation']["count_insert_rows"] = $imp_session['reccount'];
//all rows will be imported as new records
$select_query = "SELECT imp_id, " . implode(",", $sel_query) . " FROM " . $import_table . " LIMIT 5000";
$imp_session['validation']['recs_insert'] = mysql__select_array3($mysqli, $select_query, false);
}
} else {
$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 (!@$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<>" . $recordType;
// TPDO: I'm not sure whether message below has been correctly interpreted
$wrong_records = getWrongRecords($mysqli, $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) {
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_array2($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";
$row = mysql__select_array2($mysqli, $select_query);
if ($row) {
if ($row[0] > 0) {
$imp_session['validation']['count_update'] = $row[0];
$imp_session['validation']['count_update_rows'] = $row[0];
//find first 100 records to display
$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_array3($mysqli, $select_query, false);
}
} else {
return "SQL error: Cannot execute query to calculate number of records to be updated!";
}
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 ".
$row = mysql__select_array2($mysqli, $select_query);
$cnt = $row && $row[0] > 0 ? intval($row[0]) : 0;
$select_query = "SELECT count(*) FROM " . $import_table . " WHERE " . $id_field . " IS NULL";
$row = mysql__select_array2($mysqli, $select_query);
$cnt = $cnt + ($row && $row[0] > 0 ? intval($row[0]) : 0);
if ($cnt > 0) {
$imp_session['validation']['count_insert'] = $cnt;
$imp_session['validation']['count_insert_rows'] = $cnt;
//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_array3($mysqli, $select_query, false);
}
}
//additional query for non-existing IDs
if ($cnt_recs_insert_nonexist_id > 0) {
$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_array3($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;
}
}
}
}
//id_field defined
// fill array with field in import table to be validated
$recStruc = getRectypeStructures(array($recordType));
$idx_reqtype = $recStruc['dtFieldNamesToIndex']['rst_RequirementType'];
$idx_fieldtype = $recStruc['dtFieldNamesToIndex']['dty_Type'];
$dt_mapping = array();
//mapping to detail type ID
$missed = 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]+)$'";
if ($sequence) {
$mapping_prev_session = @$sequence['mapping_keys'];
//OR mapping_flds???
} else {
$mapping_prev_session = @$imp_session['mapping'];
}
//loop for all fields in record type structure
foreach ($recStruc[$recordType]['dtFields'] as $ft_id => $ft_vals) {
//find among mappings
$field_name = @$mapping[$ft_id];
if (!$field_name) {
if (is_array($mapping_prev_session)) {
$field_name = array_search($recordType . "." . $ft_id, $mapping_prev_session, 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 && is_array($mapping_prev_session)) {
$field_name1 = array_search($recordType . "." . $ft_id . "_lat", $mapping_prev_session, true);
$field_name2 = array_search($recordType . "." . $ft_id . "_long", $mapping_prev_session, 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) {
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 && $imp_session['validation']['count_insert'] > 0) {
return '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 ($id_field) {
//validate only for defined records IDs
if ($ignore_insert) {
$only_for_specified_id = " (" . $id_field . " > 0) AND ";
} else {
$only_for_specified_id = "";
//otherwise it does not for skip matching " (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($mysqli, $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) {
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($mysqli, $query, $imp_session, $field, $dt_mapping[$field], $idx, $recStruc, $recordType, "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($mysqli, $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) {
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($mysqli, $query, $imp_session, $field, $dt_mapping[$field], $idx, $recStruc, $recordType);
} 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($mysqli, $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) {
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($mysqli, $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($mysqli, $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) {
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($mysqli, $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($mysqli, $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) {
return $wrong_records;
}
}
}
//7. TODO Verify geo fields
return $imp_session;
}
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;
}