/**
* Load exporter configuration from XLSX file
* @param string $ps_source file path for source XLSX
* @param array $pa_errors call-by-reference array to store and "return" error messages
* @param array $pa_options options
* @return ca_data_exporters BaseModel representation of the new exporter. false/null if there was an error.
*/
public static function loadExporterFromFile($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_excel = PHPExcel_IOFactory::load($ps_source);
$o_sheet = $o_excel->getSheet(0);
$vn_row = 0;
$va_settings = array();
$va_mappings = array();
$va_ids = array();
foreach ($o_sheet->getRowIterator() as $o_row) {
if ($vn_row++ == 0) {
// skip first row (headers)
continue;
}
$vn_row_num = $o_row->getRowIndex();
$o_cell = $o_sheet->getCellByColumnAndRow(0, $vn_row_num);
$vs_mode = (string) $o_cell->getValue();
switch ($vs_mode) {
case 'Mapping':
case 'Constant':
case 'Variable':
case 'RepeatMappings':
$o_id = $o_sheet->getCellByColumnAndRow(1, $o_row->getRowIndex());
$o_parent = $o_sheet->getCellByColumnAndRow(2, $o_row->getRowIndex());
$o_element = $o_sheet->getCellByColumnAndRow(3, $o_row->getRowIndex());
$o_source = $o_sheet->getCellByColumnAndRow(4, $o_row->getRowIndex());
$o_options = $o_sheet->getCellByColumnAndRow(5, $o_row->getRowIndex());
if ($vs_id = trim((string) $o_id->getValue())) {
$va_ids[] = $vs_id;
}
if ($vs_parent_id = trim((string) $o_parent->getValue())) {
if (!in_array($vs_parent_id, $va_ids) && $vs_parent_id != $vs_id) {
$pa_errors[] = _t("Warning: skipped mapping at row %1 because parent id was invalid", $vn_row);
continue 2;
}
}
if (!($vs_element = trim((string) $o_element->getValue()))) {
$pa_errors[] = _t("Warning: skipped mapping at row %1 because element was not defined", $vn_row);
continue 2;
}
$vs_source = trim((string) $o_source->getValue());
if ($vs_mode == 'Constant') {
if (strlen($vs_source) < 1) {
// ignore constant rows without value
continue;
}
$vs_source = "_CONSTANT_:{$vs_source}";
}
if ($vs_mode == 'Variable') {
if (preg_match("/^[A-Za-z0-9\\_\\-]+\$/", $vs_element)) {
$vs_element = "_VARIABLE_:{$vs_element}";
} else {
$pa_errors[] = _t("Variable name %1 is invalid. It should only contain ASCII letters, numbers, hyphens and underscores. The variable was not created.", $vs_element);
continue 2;
}
}
$va_options = null;
if ($vs_options_json = (string) $o_options->getValue()) {
if (is_null($va_options = @json_decode($vs_options_json, true))) {
$pa_errors[] = _t("Warning: options for element %1 are not in proper JSON", $vs_element);
}
}
$va_options['_id'] = (string) $o_id->getValue();
// stash ID for future reference
$vs_key = strlen($vs_id) > 0 ? $vs_id : md5($vn_row);
$va_mapping[$vs_key] = array('parent_id' => $vs_parent_id, 'element' => $vs_element, 'source' => $vs_mode == "RepeatMappings" ? null : $vs_source, 'options' => $va_options);
// allow mapping repetition
if ($vs_mode == 'RepeatMappings') {
if (strlen($vs_source) < 1) {
// ignore repitition rows without value
continue;
}
$va_new_items = array();
$va_mapping_items_to_repeat = explode(',', $vs_source);
foreach ($va_mapping_items_to_repeat as $vs_mapping_item_to_repeat) {
$vs_mapping_item_to_repeat = trim($vs_mapping_item_to_repeat);
if (!is_array($va_mapping[$vs_mapping_item_to_repeat])) {
$pa_errors[] = _t("Couldn't repeat mapping item %1", $vs_mapping_item_to_repeat);
continue;
}
// add item to repeat under current item
$va_new_items[$vs_key . "_:_" . $vs_mapping_item_to_repeat] = $va_mapping[$vs_mapping_item_to_repeat];
$va_new_items[$vs_key . "_:_" . $vs_mapping_item_to_repeat]['parent_id'] = $vs_key;
// Find children of item to repeat (and their children) and add them as well, preserving the hierarchy
// the code below banks on the fact that hierarchy children are always defined AFTER their parents
// in the mapping document.
$va_keys_to_lookup = array($vs_mapping_item_to_repeat);
foreach ($va_mapping as $vs_item_key => $va_item) {
if (in_array($va_item['parent_id'], $va_keys_to_lookup)) {
$va_keys_to_lookup[] = $vs_item_key;
$va_new_items[$vs_key . "_:_" . $vs_item_key] = $va_item;
$va_new_items[$vs_key . "_:_" . $vs_item_key]['parent_id'] = $vs_key . ($va_item['parent_id'] ? "_:_" . $va_item['parent_id'] : "");
}
}
}
$va_mapping = $va_mapping + $va_new_items;
}
break;
case 'Setting':
$o_setting_name = $o_sheet->getCellByColumnAndRow(1, $o_row->getRowIndex());
$o_setting_value = $o_sheet->getCellByColumnAndRow(2, $o_row->getRowIndex());
$va_settings[(string) $o_setting_name->getValue()] = (string) $o_setting_value->getValue();
break;
default:
// if 1st column is empty, skip
continue 2;
break;
}
}
// try to extract replacements from 2nd sheet in file
// PHPExcel will throw an exception if there's no such sheet
try {
$o_sheet = $o_excel->getSheet(1);
$vn_row = 0;
foreach ($o_sheet->getRowIterator() as $o_row) {
if ($vn_row == 0) {
// skip first row (headers)
$vn_row++;
continue;
}
$vn_row_num = $o_row->getRowIndex();
$o_cell = $o_sheet->getCellByColumnAndRow(0, $vn_row_num);
$vs_mapping_num = trim((string) $o_cell->getValue());
if (strlen($vs_mapping_num) < 1) {
continue;
}
$o_search = $o_sheet->getCellByColumnAndRow(1, $o_row->getRowIndex());
$o_replace = $o_sheet->getCellByColumnAndRow(2, $o_row->getRowIndex());
if (!isset($va_mapping[$vs_mapping_num])) {
$pa_errors[] = _t("Warning: Replacement sheet references invalid mapping number '%1'. Ignoring row.", $vs_mapping_num);
continue;
}
$vs_search = (string) $o_search->getValue();
$vs_replace = (string) $o_replace->getValue();
if (!$vs_search) {
$pa_errors[] = _t("Warning: Search must be set for each row in the replacement sheet. Ignoring row for mapping '%1'", $vs_mapping_num);
continue;
}
// look for replacements
foreach ($va_mapping as $vs_k => &$va_v) {
if (preg_match("!\\_\\:\\_" . $vs_mapping_num . "\$!", $vs_k)) {
$va_v['options']['original_values'][] = $vs_search;
$va_v['options']['replacement_values'][] = $vs_replace;
}
}
$va_mapping[$vs_mapping_num]['options']['original_values'][] = $vs_search;
$va_mapping[$vs_mapping_num]['options']['replacement_values'][] = $vs_replace;
$vn_row++;
}
} catch (PHPExcel_Exception $e) {
// noop, because we don't care: mappings without replacements are still valid
}
// Do checks on mapping
if (!$va_settings['code']) {
$pa_errors[] = _t("Error: You must set a code for your mapping!");
return;
}
$o_dm = Datamodel::load();
if (!($t_instance = $o_dm->getInstanceByTableName($va_settings['table']))) {
$pa_errors[] = _t("Error: Mapping target table %1 is invalid!", $va_settings['table']);
return;
}
if (!$va_settings['name']) {
$va_settings['name'] = $va_settings['code'];
}
$t_exporter = new ca_data_exporters();
$t_exporter->setMode(ACCESS_WRITE);
// Remove any existing mapping with this code
if ($t_exporter->load(array('exporter_code' => $va_settings['code']))) {
$t_exporter->delete(true, array('hard' => true));
if ($t_exporter->numErrors()) {
$pa_errors[] = _t("Could not delete existing mapping for %1: %2", $va_settings['code'], join("; ", $t_exporter->getErrors()));
return;
}
}
// Create new mapping
$t_exporter->set('exporter_code', $va_settings['code']);
$t_exporter->set('table_num', $t_instance->tableNum());
$vs_name = $va_settings['name'];
unset($va_settings['code']);
unset($va_settings['table']);
unset($va_settings['name']);
foreach ($va_settings as $vs_k => $vs_v) {
$t_exporter->setSetting($vs_k, $vs_v);
}
$t_exporter->insert();
if ($t_exporter->numErrors()) {
$pa_errors[] = _t("Error creating exporter: %1", join("; ", $t_exporter->getErrors()));
return;
}
$t_exporter->addLabel(array('name' => $vs_name), $vn_locale_id, null, true);
if ($t_exporter->numErrors()) {
$pa_errors[] = _t("Error creating exporter name: %1", join("; ", $t_exporter->getErrors()));
return;
}
$va_id_map = array();
foreach ($va_mapping as $vs_mapping_id => $va_info) {
$va_item_settings = array();
if (is_array($va_info['options'])) {
foreach ($va_info['options'] as $vs_k => $vs_v) {
switch ($vs_k) {
case 'replacement_values':
case 'original_values':
if (sizeof($vs_v) > 0) {
$va_item_settings[$vs_k] = join("\n", $vs_v);
}
break;
default:
$va_item_settings[$vs_k] = $vs_v;
break;
}
}
}
$vn_parent_id = null;
if ($va_info['parent_id']) {
$vn_parent_id = $va_id_map[$va_info['parent_id']];
}
$t_item = $t_exporter->addItem($vn_parent_id, $va_info['element'], $va_info['source'], $va_item_settings);
if ($t_exporter->numErrors()) {
$pa_errors[] = _t("Error adding item to exporter: %1", join("; ", $t_exporter->getErrors()));
return;
}
$va_id_map[$vs_mapping_id] = $t_item->getPrimaryKey();
}
$va_mapping_errors = ca_data_exporters::checkMapping($t_exporter->get('exporter_code'));
if (is_array($va_mapping_errors) && sizeof($va_mapping_errors) > 0) {
$pa_errors = array_merge($pa_errors, $va_mapping_errors);
return false;
}
return $t_exporter;
}
