/**
* create or update records
*
* @param mixed $mysqli
* @param mixed $imp_session
* @param mixed $params
*/
function doImport($mysqli, $imp_session, $params, $mode_output)
{
global $rep_processed, $rep_added, $rep_updated, $rep_skipped, $rep_permission, $wg_id, $rec_visibility;
$addRecDefaults = getDefaultOwnerAndibility(null);
$wg_id = $addRecDefaults[1];
$rec_visibility = $addRecDefaults[2];
//rectype to import
$import_table = $imp_session['import_table'];
$recordType = @$params['sa_rectype'];
$currentSeqIndex = @$params['seq_index'];
$use_sequence = false;
if ($currentSeqIndex >= 0 && @$imp_session['sequence'] && is_array(@$imp_session['sequence'][$currentSeqIndex])) {
$use_sequence = true;
}
$id_field = @$params['recid_field'];
//record ID field is always defined explicitly
$id_field_not_defined = $id_field == null || $id_field == '';
if ($id_field && @$imp_session['indexes_keyfields'][$id_field]) {
//indexes_keyfields not used anymore in new method
$is_mulivalue_index = true;
} else {
$is_mulivalue_index = false;
//so we always have FALSE
}
if (intval($recordType) < 1) {
return "record type not defined";
}
$field_types = array();
// idx => fieldtype ID
$sel_query = array();
$mapping = array();
//get field mapping and selection query from _REQUEST(params)
if (@$params['mapping']) {
//new way
$mapping = @$params['mapping'];
// idx => fieldtype ID
if (is_array($mapping) && count($mapping) > 0) {
foreach ($mapping as $index => $field_type) {
$field_name = "field_" . $index;
array_push($field_types, $field_type);
array_push($sel_query, $field_name);
}
} else {
return 'Mapping is not defined';
}
} else {
foreach ($params as $key => $field_type) {
if (strpos($key, "sa_dt_") === 0 && $field_type) {
//search for values of field selector
//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);
array_push($field_types, $field_type);
$mapping[$index] = $field_type;
}
}
if (count($sel_query) < 1) {
return "mapping not defined";
}
}
//indexes
$recStruc = getRectypeStructures(array($recordType));
$recTypeName = $recStruc[$recordType]['commonFields'][$recStruc['commonNamesToIndex']['rty_Name']];
$idx_name = $recStruc['dtFieldNamesToIndex']['rst_DisplayName'];
$idx_fieldtype = $recStruc['dtFieldNamesToIndex']['dty_Type'];
$idx_term_tree = $recStruc['dtFieldNamesToIndex']['rst_FilteredJsonTermIDTree'];
$idx_term_nosel = $recStruc['dtFieldNamesToIndex']['dty_TermIDTreeNonSelectableIDs'];
$idx_reqtype = $recStruc['dtFieldNamesToIndex']['rst_RequirementType'];
$recordTypeStructure = $recStruc[$recordType]['dtFields'];
//get terms name=>id
$terms_enum = null;
$terms_relation = null;
$select_query = "SELECT " . implode(",", $sel_query) . ($id_field ? ", " . $id_field : "") . ", imp_id " . " FROM " . $import_table;
$ignore_insert = $params['ignore_insert'] == 1;
if ($id_field) {
//index field defined - add to list of columns
$id_field_idx = count($field_types);
//last one
if ($ignore_insert) {
$select_query = $select_query . " WHERE (" . $id_field . ">0) ";
//use records with defined value in index field
}
$select_query = $select_query . " ORDER BY " . $id_field;
} else {
if ($ignore_insert) {
return "id field not defined";
}
//create id field by default - add to import table
$id_fieldname = "ID field for Record type #" . $recordType;
$index = array_search($id_fieldname, $imp_session['columns']);
//find it among existing columns
if ($index !== false) {
//this is existing field
$id_field_def = "field_" . $index;
$imp_session['indexes'][$id_field] = $recordType;
} else {
$field_count = count($imp_session['columns']);
$id_field_def = "field_" . $field_count;
$altquery = "alter table " . $import_table . " add column " . $id_field_def . " int(10) ";
if (!$mysqli->query($altquery)) {
return "SQL error: cannot alter import session table, cannot add new index field: " . $mysqli->error;
}
$imp_session['indexes'][$id_field_def] = $recordType;
array_push($imp_session['columns'], $id_fieldname);
}
}
$res = $mysqli->query($select_query);
if (!$res) {
return "import table is empty";
} else {
$rep_processed = 0;
$rep_added = 0;
$rep_updated = 0;
$rep_skipped = 0;
$rep_permission = 0;
$tot_count = $imp_session['reccount'];
$first_time = true;
$step = ceil($tot_count / 10);
if ($step > 10) {
$step = 10;
} else {
if ($step < 1) {
$step = 1;
}
}
$csv_mvsep = @$params['csv_mvsep'] ? $params['csv_mvsep'] : $imp_session['csv_mvsep'];
$csv_enclosure = @$params['csv_enclosure'] ? $params['csv_enclosure'] : $imp_session['csv_enclosure'];
$previos_recordId = null;
$recordId = null;
$details = array();
$details2 = array();
//to keep original for sa_mode=2 (replace all existing value)
$new_record_ids = array();
$imp_id = null;
$pairs = array();
// for multivalue rec_id => new_rec_id (keep new id if such id already used)
while ($row = $res->fetch_row()) {
//split multivalue index field
$id_field_values = array();
if ($id_field) {
if (@$row[$id_field_idx]) {
//id field defined - detect insert or update
$id_field_values = explode($csv_mvsep, $row[$id_field_idx]);
} else {
if (!$ignore_insert) {
//field value is not defined - add new record
$id_field_values = array(0 => null);
}
}
}
//loop all id values - because on index field may have multivalue ID
foreach ($id_field_values as $idx2 => $recordId_in_import) {
if (@$pairs[$recordId_in_import]) {
//already impported
$recordId_in_import = $pairs[$recordId_in_import];
}
//we already have recordID
if ($recordId_in_import) {
//id field defined - detect insert or update
//@toremove OLD $recordId_in_import = $row[$id_field_idx];
if ($previos_recordId != $recordId_in_import) {
//next record ID
//save data
if ($previos_recordId != null && !$is_mulivalue_index && count($details) > 0) {
//perform action
//$details = retainExisiting($details, $details2, $params, $recordTypeStructure, $idx_reqtype);
//import detail is sorted by rec_id -0 thus it is possible to assign the same recId for several imp_id
doInsertUpdateRecord($recordId, $params, $details, $id_field, $mode_output);
}
$previos_recordId = $recordId_in_import;
//reset
$details = array();
$details2 = array();
if ($recordId_in_import > 0) {
//possible update
// find original record in HDB
$details2 = findOriginalRecord($recordId_in_import);
if (count($details2) == 0) {
//record not found - this is insert with predefined ID
$recordId = -$recordId_in_import;
} else {
// record found - update detail according TO settings
$recordId = $recordId_in_import;
}
} else {
$recordId = null;
//insert for negative
}
}
} else {
//INSERT - if field is not defined - always insert for each line in import data
$recordId = null;
$details = array();
}
//START TO FILL DETAILS ============================
if (@$details['imp_id'] == null) {
$details['imp_id'] = array();
}
array_push($details['imp_id'], end($row));
$lat = null;
$long = null;
foreach ($field_types as $index => $field_type) {
if ($field_type == "url") {
if ($row[$index]) {
$details['recordURL'] = trim($row[$index]);
}
} else {
if ($field_type == "scratchpad") {
if ($row[$index]) {
$details['recordNotes'] = trim($row[$index]);
}
} else {
if (substr($field_type, -strlen("_lat")) === "_lat") {
$field_type = substr($field_type, 0, strlen($field_type) - 4);
$fieldtype_type = "lat";
} else {
if (substr($field_type, -strlen("_long")) === "_long") {
$field_type = substr($field_type, 0, strlen($field_type) - 5);
$fieldtype_type = "long";
} else {
$ft_vals = $recordTypeStructure[$field_type];
//field type description
$fieldtype_type = $ft_vals[$idx_fieldtype];
}
}
if (strpos($row[$index], $csv_mvsep) !== false) {
$values = getMultiValues($row[$index], $csv_enclosure, $csv_mvsep);
// if this is multivalue index field we have to take only current value
if ($is_mulivalue_index && @$imp_session['indexes_keyfields'][$id_field][$sel_query[$index]] && $idx2 < count($values)) {
$values = array($values[$idx2]);
}
} else {
$values = array($row[$index]);
}
foreach ($values as $idx => $r_value) {
$value = null;
$r_value = trim($r_value);
if ($fieldtype_type == "enum" || $fieldtype_type == "relationtype") {
$r_value = trim_lower_accent($r_value);
if ($r_value != "") {
if (ctype_digit($r_value) && isValidTerm($ft_vals[$idx_term_tree], $ft_vals[$idx_term_nosel], $r_value, $field_type)) {
$value = $r_value;
}
if ($value == null) {
$value = isValidTermLabel($ft_vals[$idx_term_tree], $ft_vals[$idx_term_nosel], $r_value, $field_type);
}
}
} else {
if ($fieldtype_type == "resource") {
if (!isValidPointer(null, $r_value, $field_type)) {
$value = null;
} else {
$value = $r_value;
}
} else {
if ($fieldtype_type == "geo") {
//verify WKT
$geoType = null;
//get WKT type
if (strpos($r_value, 'POINT') !== false) {
$geoType = "p";
} else {
if (strpos($r_value, 'LINESTRING') !== false) {
$geoType = "l";
} else {
if (strpos($r_value, 'POLYGON') !== false) {
$geoType = "pl";
}
}
}
if ($geoType) {
$value = $geoType . " " . $r_value;
} else {
$value = null;
}
} else {
if ($fieldtype_type == "lat") {
$lat = $r_value;
} else {
if ($fieldtype_type == "long") {
//WARNING MILTIVALUE IS NOT SUPPORTED
$long = $r_value;
} else {
//double spaces are removed on preprocess stage $value = trim(preg_replace('/([\s])\1+/', ' ', $r_value));
$value = trim($r_value);
if ($value != "") {
if ($fieldtype_type == "date") {
//$value = strtotime($value);
//$value = date('Y-m-d H:i:s', $value);
} else {
//replace \\r to\r \\n to \n
$value = str_replace("\\r", "\r", $value);
$value = str_replace("\\n", "\n", $value);
}
}
}
}
}
}
}
if ($lat && $long) {
$value = "p POINT (" . $long . " " . $lat . ")";
//TODO Where does the 'p' come from? This appears to have been a local invention ...
//reset
$lat = null;
$long = null;
}
if ($value && ($params['sa_upd'] != 1 || !@$details2["t:" . $field_type])) {
//$params['sa_upd']==1 Add new data only if field is empty (new data ignored for non-empty fields)
$details_lc = array();
$details2_lc = array();
/*if($params['sa_upd']==2 && $params['sa_upd2']==1 && !@$details["t:".$field_type]["bd:delete"]){
unset($details["t:".$field_type]["bd:delete"]); //new data is porvided - no need to delete
}else if($params['sa_upd']==2 && $params['sa_upd2']!=1 && !@$details["t:".$field_type]["bd:delete"]){
$details["t:".$field_type]["bd:delete"] = "ups!"; //if new data are provided then remove old data
}*/
if ($params['sa_upd'] == 2) {
$details["t:" . $field_type]["bd:delete"] = "ups!";
//if new data are provided then remove old data
}
if (is_array(@$details["t:" . $field_type])) {
$details_lc = array_map('trim_lower_accent', $details["t:" . $field_type]);
}
if ($params['sa_upd'] != 2 && is_array(@$details2["t:" . $field_type])) {
$details2_lc = array_map('trim_lower_accent', $details2["t:" . $field_type]);
}
if ((!@$details["t:" . $field_type] || array_search(trim_lower_accent($value), $details_lc, true) === false) && (!@$details2["t:" . $field_type] || array_search(trim_lower_accent($value), $details2_lc, true) === false)) {
$cnt = count(@$details["t:" . $field_type]) + 1;
$details["t:" . $field_type][$cnt] = $value;
} else {
}
}
if ($params['sa_upd'] == 2 && $params['sa_upd2'] == 1 && !@$details["t:" . $field_type]["bd:delete"] && !$value && $recordTypeStructure[$field_type][$idx_reqtype] != "required") {
//delete old even if new is not provided
$details["t:" . $field_type]["bd:delete"] = "ups!";
}
}
//$values
//if insert and require field is not defined - skip it
if (!($recordId > 0) && $recordTypeStructure[$field_type][$idx_reqtype] == "required") {
//error_log($field_type.' = '.print_r(@$details["t:".$field_type],true));
//$is_valid_newrecord = false;
//break;
}
}
}
}
//for import data
//END FILL DETAILS =============================
$new_id = null;
// || $recordId==null
//add - update record for 2 cases: idfield not defined, idfield is multivalue
if (($id_field_not_defined || $recordId == null) && count($details) > 0) {
//id field not defined - insert for each line
if (!$ignore_insert) {
$new_id = doInsertUpdateRecord($recordId, $params, $details, $id_field, $mode_output);
$details = array();
}
} else {
if ($is_mulivalue_index) {
//idfield is multivalue (now is is assummed that index field is always multivalue)
//THIS SECTION IS NOT USED
//$details = retainExisiting($details, $details2, $params, $recordTypeStructure, $idx_reqtype);
if (count($details) > 1) {
$new_id = doInsertUpdateRecord($recordId, $params, $details, null, $mode_output);
if ($new_id != null && intval($new_id) > 0) {
array_push($new_record_ids, $new_id);
}
$previos_recordId = null;
if (($recordId == null || $recordId < 0) && intval($new_id) > 0) {
//new records OR predefined id
$pairs[$id_field_values[$idx2]] = $new_id;
}
} else {
if ($recordId > 0) {
array_push($new_record_ids, $recordId);
}
}
}
}
$rep_processed++;
if ($mode_output == 'html' && $rep_processed % $step == 0) {
ob_start();
if ($first_time) {
print '<script type="text/javascript">$("#div-progress").hide();</script>';
$first_time = false;
}
print '<script type="text/javascript">update_counts(' . $rep_processed . ',' . $rep_added . ',' . $rep_updated . ',' . $tot_count . ')</script>' . "\n";
ob_flush();
flush();
}
}
//foreach multivalue index
if ($is_mulivalue_index && is_array($new_record_ids) && count($new_record_ids) > 0) {
//save record ids in import table
updateRecIds($import_table, end($row), $id_field, $new_record_ids, $csv_mvsep);
$new_record_ids = array();
//to save in import table
}
}
//main all recs in import table
$res->close();
if ($id_field && count($details) > 0 && !$is_mulivalue_index && $recordId != null) {
//action for last record
//$details = retainExisiting($details, $details2, $params, $recordTypeStructure, $idx_reqtype);
$new_id = doInsertUpdateRecord($recordId, $params, $details, $id_field, $mode_output);
}
if (!$id_field) {
array_push($imp_session['uniqcnt'], $rep_added);
}
$import_report = array('processed' => $rep_processed, 'inserted' => $rep_added, 'updated' => $rep_updated, 'total' => $tot_count, 'skipped' => $rep_skipped, 'permission' => $rep_permission);
//update counts array
$new_counts = array($rep_updated + $rep_added, $rep_processed, 0, 0);
if ($ignore_insert) {
if ($use_sequence) {
$prev_counts = @$imp_session['sequence'][$currentSeqIndex]['counts'];
} else {
if (@$imp_session['counts'][$recordType]) {
$prev_counts = $imp_session['counts'][$recordType];
}
}
if (is_array($prev_counts) && count($prev_counts) == 4) {
$new_counts[2] = $prev_counts[2];
$new_counts[3] = $prev_counts[3];
}
}
if ($use_sequence) {
$imp_session['sequence'][$currentSeqIndex]['counts'] = $new_counts;
} else {
if (!@$imp_session['counts']) {
$imp_session['counts'] = array();
}
$imp_session['counts'][$recordType] = $new_counts;
}
if ($mode_output != 'array') {
print '<script type="text/javascript">update_counts(' . $rep_processed . ',' . $rep_added . ',' . $rep_updated . ',' . $tot_count . ')</script>' . "\n";
if ($rep_skipped > 0) {
print '<p>' . $rep_skipped . ' rows skipped</p>';
}
}
}
//save mapping into import_sesssion
if ($use_sequence) {
$imp_session['sequence'][$currentSeqIndex]['mapping_flds'] = $mapping;
} else {
//old way
if (!@$imp_session['mapping_flds']) {
$imp_session['mapping_flds'] = array();
}
$imp_session['mapping_flds'][$recordType] = $mapping;
}
$imp_session['import_report'] = $import_report;
return saveSession($mysqli, $imp_session);
}
/**
* put your comment there...
*
* @param mixed $mysqli
* @param mixed $query
* @param mixed $imp_session
* @param mixed $fields_checked - name of field to be verified
* @param mixed $field_idx - index of validation field in query result (to get value)
*/
function validateDateField($query, $imp_session, $fields_checked, $field_idx)
{
global $system;
$mysqli = $system->get_mysqli();
$res = $mysqli->query($query . " LIMIT 5000");
if ($res) {
$wrong_records = array();
while ($row = $res->fetch_row()) {
$is_error = false;
$newvalue = array();
$values = getMultiValues($row[$field_idx], $imp_session['csv_enclosure'], $imp_session['csv_mvsep']);
foreach ($values as $idx => $r_value) {
if ($r_value != null && trim($r_value) != "") {
if (is_numeric($r_value) && intval($r_value)) {
array_push($newvalue, $r_value);
} else {
$date = date_parse($r_value);
if ($date["error_count"] == 0 && checkdate($date["month"], $date["day"], $date["year"])) {
$value = strtotime($r_value);
$value = date('Y-m-d H:i:s', $value);
array_push($newvalue, $value);
} else {
$is_error = true;
array_push($newvalue, "<font color='red'>" . $r_value . "</font>");
}
}
}
}
if ($is_error) {
if ($imp_session['csv_mvsep'] == 'none') {
$row[$field_idx] = count($newvalue) > 0 ? $newvalue[0] : '';
} else {
$row[$field_idx] = implode($imp_session['csv_mvsep'], $newvalue);
}
array_push($wrong_records, $row);
}
}
$res->close();
$cnt_error = count($wrong_records);
if ($cnt_error > 0) {
$error = array();
$error["count_error"] = $cnt_error;
$error["recs_error"] = array_slice($wrong_records, 0, 1000);
$error["field_checked"] = $fields_checked;
$error["err_message"] = "Date values must be in dd-mm-yyyy, mm/dd/yyyy or yyyy-mm-dd formats";
$error["short_messsage"] = "Invalid Dates";
$imp_session['validation']['count_error'] = $imp_session['validation']['count_error'] + $cnt_error;
array_push($imp_session['validation']['error'], $error);
return $imp_session;
}
} else {
$system->addError(HEURIST_DB_ERROR, 'Cannot perform validation query: ' . $query, $mysqli->error);
return false;
}
return null;
}
