Example #1
0
/**
* 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);
}
Example #2
0
/**
* 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 $dt_id - mapped detail type ID
* @param mixed $field_idx - index of validation field in query result (to get value)
* @param mixed $recStruc - record type structure
*/
function validateResourcePointers($query, $imp_session, $fields_checked, $dt_id, $field_idx, $recStruc, $recordType)
{
    global $system;
    $mysqli = $system->get_mysqli();
    $dt_def = $recStruc[$recordType]['dtFields'][$dt_id];
    $idx_pointer_types = $recStruc['dtFieldNamesToIndex']['rst_PtrFilteredIDs'];
    $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) {
                $r_value2 = trim($r_value);
                if ($r_value2 != "") {
                    if (!isValidPointer($dt_def[$idx_pointer_types], $r_value2, $dt_id)) {
                        //not found
                        $is_error = true;
                        array_push($newvalue, "<font color='red'>" . $r_value . "</font>");
                    } else {
                        array_push($newvalue, $r_value);
                    }
                }
            }
            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"] = "Record pointer fields must reference an existing record of valid type in the database";
            $error["short_messsage"] = "Invalid Pointers";
            $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;
}