/**
*
*/
public static function loadImporterFromFile($ps_source, &$pa_errors, $pa_options = null)
{
global $g_ui_locale_id;
$vn_locale_id = isset($pa_options['locale_id']) && (int) $pa_options['locale_id'] ? (int) $pa_options['locale_id'] : $g_ui_locale_id;
$pa_errors = array();
$o_log = is_writable($pa_options['logDirectory']) ? new KLogger($pa_options['logDirectory'], $pa_options['logLevel']) : null;
$o_excel = PHPExcel_IOFactory::load($ps_source);
//$o_excel->setActiveSheet(1);
$o_sheet = $o_excel->getActiveSheet();
$vn_row = 0;
$va_settings = array();
$va_rules = array();
$va_environment = array();
$va_mappings = array();
$va_refineries = RefineryManager::getRefineryNames();
$va_refinery_ci_map = array();
foreach ($va_refineries as $vs_refinery) {
$va_refinery_ci_map[strtolower($vs_refinery)] = $vs_refinery;
}
foreach ($o_sheet->getRowIterator() as $o_row) {
if ($vn_row == 0) {
// skip first row
$vn_row++;
continue;
}
//$o_cells = $o_row->getCellIterator();
//$o_cells->setIterateOnlyExistingCells(false);
$vn_row_num = $o_row->getRowIndex();
$o_cell = $o_sheet->getCellByColumnAndRow(0, $vn_row_num);
$vs_mode = (string) $o_cell->getValue();
switch (strtolower($vs_mode)) {
default:
case 'skip':
continue 2;
break;
case 'mapping':
case 'constant':
$o_source = $o_sheet->getCellByColumnAndRow(1, $o_row->getRowIndex());
$o_dest = $o_sheet->getCellByColumnAndRow(2, $o_row->getRowIndex());
$o_group = $o_sheet->getCellByColumnAndRow(3, $o_row->getRowIndex());
$o_options = $o_sheet->getCellByColumnAndRow(4, $o_row->getRowIndex());
$o_refinery = $o_sheet->getCellByColumnAndRow(5, $o_row->getRowIndex());
$o_refinery_options = $o_sheet->getCellByColumnAndRow(6, $o_row->getRowIndex());
$o_orig_values = $o_sheet->getCellByColumnAndRow(7, $o_row->getRowIndex());
$o_replacement_values = $o_sheet->getCellByColumnAndRow(8, $o_row->getRowIndex());
$o_source_desc = $o_sheet->getCellByColumnAndRow(9, $o_row->getRowIndex());
$o_notes = $o_sheet->getCellByColumnAndRow(10, $o_row->getRowIndex());
if (!($vs_group = trim((string) $o_group->getValue()))) {
$vs_group = '_group_' . (string) $o_source->getValue() . "_{$vn_row}";
}
$vs_source = trim((string) $o_source->getValue());
if ($vs_mode == 'Constant') {
$vs_source = "_CONSTANT_:{$vn_row_num}:{$vs_source}";
}
$vs_destination = trim((string) $o_dest->getValue());
if (!$vs_source) {
$pa_errors[] = _t("Warning: skipped mapping at row %1 because source was not defined", $vn_row_num);
if ($o_log) {
$o_log->logWarn(_t("[loadImporterFromFile:%1] Skipped mapping at row %2 because source was not defined", $ps_source, $vn_row_num));
}
continue 2;
}
if (!$vs_destination) {
$pa_errors[] = _t("Warning: skipped mapping at row %1 because destination was not defined", $vn_row_num);
if ($o_log) {
$o_log->logWarn(_t("[loadImporterFromFile:%1] Skipped mapping at row %2 because destination was not defined", $ps_source, $vn_row_num));
}
continue 2;
}
$va_options = null;
if ($vs_options_json = (string) $o_options->getValue()) {
// Test whether the JSON is valid
json_decode($vs_options_json, TRUE);
if (json_last_error()) {
// try encode newlines
$vs_options_json = preg_replace("![\r\n]!", "\\\\n", $vs_options_json);
}
if (is_null($va_options = @json_decode($vs_options_json, true))) {
$pa_errors[] = _t("Warning: invalid options for group %1/source %2.", $vs_group, $vs_source);
if ($o_log) {
$o_log->logWarn(_t("[loadImporterFromFile:%1] Invalid options for group %2/source %3. Options were: %4.", $ps_source, $vs_group, $vs_source, $vs_options_json));
}
}
}
if ($vs_mode == 'Mapping') {
$vs_refinery = $va_refinery_ci_map[strtolower(trim((string) $o_refinery->getValue()))];
$va_refinery_options = null;
if ($vs_refinery && ($vs_refinery_options_json = (string) $o_refinery_options->getValue())) {
if (!in_array($vs_refinery, $va_refineries)) {
$pa_errors[] = _t("Warning: refinery %1 does not exist", $vs_refinery) . "\n";
if ($o_log) {
$o_log->logWarn(_t("[loadImporterFromFile:%1] Invalid options for group %2/source %3", $ps_source, $vs_group, $vs_source));
}
} else {
if (is_null($va_refinery_options = json_decode($vs_refinery_options_json, true))) {
$pa_errors[] = _t("Warning: invalid refinery options for group %1/source %2 = %3", $vs_group, $vs_source, $vs_refinery_options_json);
if ($o_log) {
$o_log->logWarn(_t("[loadImporterFromFile:%1] Invalid refinery options for group %2/source %3 = %4", $ps_source, $vs_group, $vs_source, $vs_refinery_options_json));
}
}
}
}
} else {
// Constants don't use refineries
$vs_refinery = $va_refinery_options = null;
}
$va_original_values = $va_replacement_values = array();
if ($va_options && is_array($va_options) && isset($va_options['transformValuesUsingWorksheet']) && $va_options['transformValuesUsingWorksheet']) {
if ($o_opt_sheet = $o_excel->getSheetByName($va_options['transformValuesUsingWorksheet'])) {
foreach ($o_opt_sheet->getRowIterator() as $o_sheet_row) {
if (!trim($vs_original_value = mb_strtolower((string) $o_opt_sheet->getCellByColumnAndRow(0, $o_sheet_row->getRowIndex())))) {
continue;
}
$vs_replacement_value = trim((string) $o_opt_sheet->getCellByColumnAndRow(1, $o_sheet_row->getRowIndex()));
$va_original_values[] = $vs_original_value;
$va_replacement_values[] = $vs_replacement_value;
}
}
} else {
$va_original_values = preg_split("![\n\r]{1}!", mb_strtolower((string) $o_orig_values->getValue()));
array_walk($va_original_values, function (&$v) {
$v = trim($v);
});
$va_replacement_values = preg_split("![\n\r]{1}!", (string) $o_replacement_values->getValue());
array_walk($va_replacement_values, function (&$v) {
$v = trim($v);
});
}
$va_mapping[$vs_group][$vs_source][] = array('destination' => $vs_destination, 'options' => $va_options, 'refinery' => $vs_refinery, 'refinery_options' => $va_refinery_options, 'source_description' => (string) $o_source_desc->getValue(), 'notes' => (string) $o_notes->getValue(), 'original_values' => $va_original_values, 'replacement_values' => $va_replacement_values);
break;
case 'setting':
$o_setting_name = $o_sheet->getCellByColumnAndRow(1, $o_row->getRowIndex());
$o_setting_value = $o_sheet->getCellByColumnAndRow(2, $o_row->getRowIndex());
switch ($vs_setting_name = (string) $o_setting_name->getValue()) {
case 'inputTypes':
// older mapping worksheets use "inputTypes" instead of the preferred "inputFormats"
// older mapping worksheets use "inputTypes" instead of the preferred "inputFormats"
case 'inputFormats':
$vs_setting_name = 'inputFormats';
// force to preferrened "inputFormats"
$va_settings[$vs_setting_name] = preg_split("![ ]*;[ ]*!", (string) $o_setting_value->getValue());
break;
default:
$va_settings[$vs_setting_name] = (string) $o_setting_value->getValue();
break;
}
break;
case 'rule':
$o_rule_trigger = $o_sheet->getCellByColumnAndRow(1, $o_row->getRowIndex());
$o_rule_action = $o_sheet->getCellByColumnAndRow(2, $o_row->getRowIndex());
$va_actions = preg_split("/[\n\r]+/", (string) $o_rule_action->getValue());
$va_rules[] = array('trigger' => (string) $o_rule_trigger->getValue(), 'actions' => $va_actions);
break;
case 'environment':
$o_source = $o_sheet->getCellByColumnAndRow(1, $o_row->getRowIndex());
$o_env_var = $o_sheet->getCellByColumnAndRow(2, $o_row->getRowIndex());
$o_options = $o_sheet->getCellByColumnAndRow(4, $o_row->getRowIndex());
$va_options = array();
if ($vs_options_json = (string) $o_options->getValue()) {
json_decode($vs_options_json, TRUE);
if (json_last_error()) {
// try encode newlines
$vs_options_json = preg_replace("![\r\n]!", "\\\\n", $vs_options_json);
}
if (is_null($va_options = @json_decode($vs_options_json, true))) {
$pa_errors[] = _t("Warning: invalid options for environment %1.", (string) $o_source->getValue());
if ($o_log) {
$o_log->logWarn(_t("[loadImporterFromFile:environment %1] Invalid options for environment value %1. Options were: %2.", (string) $o_source->getValue(), $vs_options_json));
}
}
}
$va_environment[] = array('name' => (string) $o_env_var->getValue(), 'value' => (string) $o_source->getValue(), 'options' => $va_options);
break;
}
$vn_row++;
}
// Do checks on mapping
if (!$va_settings['code']) {
$pa_errors[] = _t("You must set a code for your mapping!");
if ($o_log) {
$o_log->logError(_t("[loadImporterFromFile:%1] You must set a code for your mapping!", $ps_source));
}
return;
}
// If no formats then default to everything
if (!isset($va_settings['inputFormats']) || !is_array($va_settings['inputFormats']) || !sizeof($va_settings['inputFormats'])) {
$va_settings['inputFormats'] = array_values(ca_data_importers::getAvailableInputFormats());
}
$o_dm = Datamodel::load();
if (!($t_instance = $o_dm->getInstanceByTableName($va_settings['table']))) {
$pa_errors[] = _t("Mapping target table %1 is invalid\n", $va_settings['table']);
if ($o_log) {
$o_log->logError(_t("[loadImporterFromFile:%1] Mapping target table %2 is invalid\n", $ps_source, $va_settings['table']));
}
return;
}
if (!$va_settings['name']) {
$va_settings['name'] = $va_settings['code'];
}
//print_R($va_settings);
//print_R($va_mapping);
$t_importer = new ca_data_importers();
$t_importer->setMode(ACCESS_WRITE);
// Remove any existing mapping
if ($t_importer->load(array('importer_code' => $va_settings['code']))) {
$t_importer->delete(true, array('hard' => true));
if ($t_importer->numErrors()) {
$pa_errors[] = _t("Could not delete existing mapping for %1: %2", $va_settings['code'], join("; ", $t_importer->getErrors())) . "\n";
if ($o_log) {
$o_log->logError(_t("[loadImporterFromFile:%1] Could not delete existing mapping for %2: %3", $ps_source, $va_settings['code'], join("; ", $t_importer->getErrors())));
}
return null;
}
}
// Create new mapping
$t_importer->set('importer_code', $va_settings['code']);
$t_importer->set('table_num', $t_instance->tableNum());
$t_importer->set('rules', array('rules' => $va_rules, 'environment' => $va_environment));
unset($va_settings['code']);
unset($va_settings['table']);
foreach ($va_settings as $vs_k => $vs_v) {
$t_importer->setSetting($vs_k, $vs_v);
}
$t_importer->insert();
if ($t_importer->numErrors()) {
$pa_errors[] = _t("Error creating mapping: %1", join("; ", $t_importer->getErrors())) . "\n";
if ($o_log) {
$o_log->logError(_t("[loadImporterFromFile:%1] Error creating mapping: %2", $ps_source, join("; ", $t_importer->getErrors())));
}
return null;
}
$t_importer->addLabel(array('name' => $va_settings['name']), $vn_locale_id, null, true);
if ($t_importer->numErrors()) {
$pa_errors[] = _t("Error creating mapping name: %1", join("; ", $t_importer->getErrors())) . "\n";
if ($o_log) {
$o_log->logError(_t("[loadImporterFromFile:%1] Error creating mapping: %2", $ps_source, join("; ", $t_importer->getErrors())));
}
return null;
}
foreach ($va_mapping as $vs_group => $va_mappings_for_group) {
$vs_group_dest = ca_data_importers::_getGroupDestinationFromItems($va_mappings_for_group);
if (!$vs_group_dest) {
$va_item = array_shift(array_shift($va_mappings_for_group));
$pa_errors[] = _t("Skipped items for %1 because no common grouping could be found", $va_item['destination']) . "\n";
if ($o_log) {
$o_log->logWarn(_t("[loadImporterFromFile:%1] Skipped items for %2 because no common grouping could be found", $ps_source, $va_item['destination']));
}
continue;
}
$t_group = $t_importer->addGroup($vs_group, $vs_group_dest, array(), array('returnInstance' => true));
// Add items
foreach ($va_mappings_for_group as $vs_source => $va_mappings_for_source) {
foreach ($va_mappings_for_source as $va_row) {
$va_item_settings = array();
$va_item_settings['refineries'] = array($va_row['refinery']);
$va_item_settings['original_values'] = $va_row['original_values'];
$va_item_settings['replacement_values'] = $va_row['replacement_values'];
if (is_array($va_row['options'])) {
foreach ($va_row['options'] as $vs_k => $vs_v) {
$va_item_settings[$vs_k] = $vs_v;
}
}
if (is_array($va_row['refinery_options'])) {
foreach ($va_row['refinery_options'] as $vs_k => $vs_v) {
$va_item_settings[$va_row['refinery'] . '_' . $vs_k] = $vs_v;
}
}
$t_group->addItem($vs_source, $va_row['destination'], $va_item_settings, array('returnInstance' => true));
}
}
}
if (sizeof($pa_errors)) {
foreach ($pa_errors as $vs_error) {
$t_importer->postError(1100, $vs_error, 'ca_data_importers::loadImporterFromFile');
}
}
return $t_importer;
}
