/** * 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; }