From 4a01628cfa19ac305b5734e8ea36713f363b8d91 Mon Sep 17 00:00:00 2001 From: Eileen McNaughton Date: Wed, 20 Apr 2022 18:18:38 +1200 Subject: [PATCH] [REF] [Import] Clean up on map fields & preview templates in Contact import This provides a bit of sanity to the MapTable.tpl which is included from MapField and Preview - the focus being around getting rid of all the section tags which rely on Count being assigned and getting back to for-eaching the relevant arrays --- CRM/Contact/Import/Form/DataSource.php | 1 - CRM/Contact/Import/Form/MapField.php | 48 +++---- CRM/Contact/Import/Form/Preview.php | 4 +- CRM/Contact/Import/Parser/Contact.php | 21 ++- CRM/Import/DataSource.php | 127 ++++++++++++++++++ CRM/Import/DataSource/CSV.php | 12 +- CRM/Import/DataSource/SQL.php | 22 ++- CRM/Import/Forms.php | 48 +++++++ .../CRM/Contact/Import/Form/MapField.tpl | 2 +- .../CRM/Contact/Import/Form/MapTable.tpl | 54 ++++---- .../CRM/Contact/Import/Form/MapFieldTest.php | 56 ++++---- 11 files changed, 286 insertions(+), 109 deletions(-) diff --git a/CRM/Contact/Import/Form/DataSource.php b/CRM/Contact/Import/Form/DataSource.php index d4626437d4..d956721ad7 100644 --- a/CRM/Contact/Import/Form/DataSource.php +++ b/CRM/Contact/Import/Form/DataSource.php @@ -246,7 +246,6 @@ class CRM_Contact_Import_Form_DataSource extends CRM_Import_Forms { $dao = new CRM_Core_DAO(); $db = $dao->getDatabaseConnection(); $dataSource->postProcess($this->_params, $db, $this); - $this->updateUserJobMetadata('DataSource', $dataSource->getDataSourceMetadata()); } /** diff --git a/CRM/Contact/Import/Form/MapField.php b/CRM/Contact/Import/Form/MapField.php index 5471108423..acd0ce187f 100644 --- a/CRM/Contact/Import/Form/MapField.php +++ b/CRM/Contact/Import/Form/MapField.php @@ -75,10 +75,12 @@ class CRM_Contact_Import_Form_MapField extends CRM_Import_Form_MapField { /** * Set variables up before form is built. + * + * @throws \API_Exception + * @throws \CRM_Core_Exception + * @throws \Civi\API\Exception\UnauthorizedException */ public function preProcess() { - $dataSource = $this->get('dataSource'); - $skipColumnHeader = $this->get('skipColumnHeader'); $this->_mapperFields = $this->get('fields'); $this->_importTableName = $this->get('importTableName'); $this->_onDuplicate = $this->get('onDuplicate'); @@ -142,35 +144,16 @@ class CRM_Contact_Import_Form_MapField extends CRM_Import_Form_MapField { $this->assign('highlightedFields', $highlightedFields); $this->_formattedFieldNames[$contactType] = $this->_mapperFields = array_merge($this->_mapperFields, $formattedFieldNames); - $columnNames = []; - //get original col headers from csv if present. - if ($dataSource == 'CRM_Import_DataSource_CSV' && $skipColumnHeader) { - $columnNames = $this->get('originalColHeader'); - } - else { - // get the field names from the temp. DB table - $columnsQuery = "SHOW FIELDS FROM $this->_importTableName - WHERE Field NOT LIKE '\_%'"; - $columnsResult = CRM_Core_DAO::executeQuery($columnsQuery); - while ($columnsResult->fetch()) { - $columnNames[] = $columnsResult->Field; - } - } - - $showColNames = TRUE; - if ($dataSource === 'CRM_Import_DataSource_CSV' && !$skipColumnHeader) { - $showColNames = FALSE; - } - $this->assign('showColNames', $showColNames); + $columnNames = $this->getColumnHeaders(); + $this->assign('showColNames', !empty($columnNames)); - $this->_columnCount = count($columnNames); + $this->_columnCount = $this->getNumberOfColumns(); $this->_columnNames = $columnNames; - $this->assign('columnNames', $columnNames); + $this->assign('columnNames', $this->getColumnHeaders()); //$this->_columnCount = $this->get( 'columnCount' ); $this->assign('columnCount', $this->_columnCount); - $this->_dataValues = $this->get('dataValues'); + $this->_dataValues = array_values($this->getDataRows(2)); $this->assign('dataValues', $this->_dataValues); - $this->assign('rowDisplayCount', 2); } /** @@ -243,7 +226,7 @@ class CRM_Contact_Import_Form_MapField extends CRM_Import_Form_MapField { foreach ($mapperKeys as $key) { // check if there is a _a_b or _b_a in the key if (strpos($key, '_a_b') || strpos($key, '_b_a')) { - list($id, $first, $second) = explode('_', $key); + [$id, $first, $second] = explode('_', $key); } else { $id = $first = $second = NULL; @@ -559,7 +542,7 @@ class CRM_Contact_Import_Form_MapField extends CRM_Import_Form_MapField { } //relationship contact mapper info. - list($id, $first, $second) = CRM_Utils_System::explode('_', $fldName, 3); + [$id, $first, $second] = CRM_Utils_System::explode('_', $fldName, 3); if (($first === 'a' && $second === 'b') || ($first === 'b' && $second === 'a') ) { @@ -700,7 +683,7 @@ class CRM_Contact_Import_Form_MapField extends CRM_Import_Form_MapField { } $mappingID = NULL; - for ($i = 0; $i < $this->_columnCount; $i++) { + foreach (array_keys($this->getColumnHeaders()) as $i) { $mappingID = $this->saveMappingField($mapperKeys, $saveMapping, $cType, $i, $mapper, $parserParameters); } $this->set('savedMapping', $mappingID); @@ -712,15 +695,14 @@ class CRM_Contact_Import_Form_MapField extends CRM_Import_Form_MapField { $parserParameters['relatedContactPhoneType'], $parserParameters['relatedContactImProvider'], $parserParameters['mapperWebsiteType'], $parserParameters['relatedContactWebsiteType'] ); + $parser->setUserJobID($this->getUserJobID()); - $primaryKeyName = $this->get('primaryKeyName'); - $statusFieldName = $this->get('statusFieldName'); $parser->run($this->_importTableName, $mapper, CRM_Import_Parser::MODE_PREVIEW, $this->get('contactType'), - $primaryKeyName, - $statusFieldName, + '_id', + '_status', $this->_onDuplicate, NULL, NULL, FALSE, CRM_Contact_Import_Parser_Contact::DEFAULT_TIMEOUT, diff --git a/CRM/Contact/Import/Form/Preview.php b/CRM/Contact/Import/Form/Preview.php index 9a15ceb606..02816642ab 100644 --- a/CRM/Contact/Import/Form/Preview.php +++ b/CRM/Contact/Import/Form/Preview.php @@ -31,8 +31,6 @@ class CRM_Contact_Import_Form_Preview extends CRM_Import_Form_Preview { * Set variables up before form is built. */ public function preProcess() { - //get the data from the session - $dataValues = $this->get('dataValues'); $mapper = $this->get('mapper'); $invalidRowCount = $this->get('invalidRowCount'); $conflictRowCount = $this->get('conflictRowCount'); @@ -82,7 +80,6 @@ class CRM_Contact_Import_Form_Preview extends CRM_Import_Form_Preview { 'locations', 'phones', 'ims', - 'dataValues', 'columnCount', 'totalRowCount', 'validRowCount', @@ -103,6 +100,7 @@ class CRM_Contact_Import_Form_Preview extends CRM_Import_Form_Preview { foreach ($properties as $property) { $this->assign($property, $this->get($property)); } + $this->assign('dataValues', $this->getDataRows(2)); $this->setStatusUrl(); diff --git a/CRM/Contact/Import/Parser/Contact.php b/CRM/Contact/Import/Parser/Contact.php index b9a80bf4d3..384d0923d7 100644 --- a/CRM/Contact/Import/Parser/Contact.php +++ b/CRM/Contact/Import/Parser/Contact.php @@ -184,17 +184,16 @@ class CRM_Contact_Import_Parser_Contact extends CRM_Import_Parser { $this->_mapperRelatedContactWebsiteType = $mapperRelatedContactWebsiteType; // get IM service provider type id for related contact $this->_mapperRelatedContactImProvider = &$mapperRelatedContactImProvider; + $this->setFieldMetadata(); + foreach ($this->getImportableFieldsMetadata() as $name => $field) { + $this->addField($name, $field['title'], CRM_Utils_Array::value('type', $field), CRM_Utils_Array::value('headerPattern', $field), CRM_Utils_Array::value('dataPattern', $field), CRM_Utils_Array::value('hasLocationType', $field)); + } } /** * The initializer code, called before processing. */ public function init() { - $this->setFieldMetadata(); - foreach ($this->getImportableFieldsMetadata() as $name => $field) { - $this->addField($name, $field['title'], CRM_Utils_Array::value('type', $field), CRM_Utils_Array::value('headerPattern', $field), CRM_Utils_Array::value('dataPattern', $field), CRM_Utils_Array::value('hasLocationType', $field)); - } - $this->_newContacts = []; $this->setActiveFields($this->_mapperKeys); @@ -2598,9 +2597,17 @@ class CRM_Contact_Import_Parser_Contact extends CRM_Import_Parser { $this->_conflicts = []; $this->_unparsedAddresses = []; + // Transitional support for deprecating table_name (and other fields) + // form input - the goal is to load them from userJob - but eventually + // we will just load the datasource object and this code will not know the + // table name. + if (!$tableName && $this->userJobID) { + $tableName = $this->getUserJob()['metadata']['DataSource']['table_name']; + } + $this->_tableName = $tableName; - $this->_primaryKeyName = $primaryKeyName; - $this->_statusFieldName = $statusFieldName; + $this->_primaryKeyName = '_id'; + $this->_statusFieldName = '_status'; if ($mode == self::MODE_MAPFIELD) { $this->_rows = []; diff --git a/CRM/Import/DataSource.php b/CRM/Import/DataSource.php index c5a4a44cef..1fce11a312 100644 --- a/CRM/Import/DataSource.php +++ b/CRM/Import/DataSource.php @@ -95,6 +95,83 @@ abstract class CRM_Import_DataSource { return $this->userJob; } + /** + * Get submitted value. + * + * Get a value submitted on the form. + * + * @return mixed + * + * @throws \API_Exception + */ + protected function getSubmittedValue(string $valueName) { + return $this->getUserJob()['metadata']['submitted_values'][$valueName]; + } + + /** + * Get rows as an array. + * + * The array has all values. + * + * @param int $limit + * @param int $offset + * + * @return array + * + * @throws \API_Exception + * @throws \CRM_Core_Exception + */ + public function getRows(int $limit = 0, int $offset = 0) { + $query = 'SELECT * FROM ' . $this->getTableName(); + if ($limit) { + $query .= ' LIMIT ' . $limit . ($offset ? (' OFFSET ' . $offset) : NULL); + } + $rows = []; + $result = CRM_Core_DAO::executeQuery($query); + while ($result->fetch()) { + $values = $result->toArray(); + /* trim whitespace around the values */ + foreach ($values as $k => $v) { + $values[$k] = trim($v, " \t\r\n"); + } + // Historically we expect a non-associative array... + $rows[] = array_values($values); + } + return $rows; + } + + /** + * Get an array of column headers, if any. + * + * Null is returned when there are none - ie because a csv file does not + * have an initial header row. + * + * This is presented to the user in the MapField screen so + * that can see what fields they are mapping. + * + * @return array + * @throws \API_Exception + */ + public function getColumnHeaders(): array { + return $this->getUserJob()['metadata']['DataSource']['column_headers']; + } + + /** + * Get an array of column headers, if any. + * + * Null is returned when there are none - ie because a csv file does not + * have an initial header row. + * + * This is presented to the user in the MapField screen so + * that can see what fields they are mapping. + * + * @return int + * @throws \API_Exception + */ + public function getNumberOfColumns(): int { + return $this->getUserJob()['metadata']['DataSource']['number_of_columns']; + } + /** * Generated metadata relating to the the datasource. * @@ -112,12 +189,43 @@ abstract class CRM_Import_DataSource { protected $dataSourceMetadata = []; /** + * Get metadata about the datasource. + * * @return array + * + * @throws \API_Exception */ public function getDataSourceMetadata(): array { + if (!$this->dataSourceMetadata && $this->getUserJobID()) { + $this->dataSourceMetadata = $this->getUserJob()['metadata']['DataSource']; + } + return $this->dataSourceMetadata; } + /** + * Get the table name for the datajob. + * + * @return string|null + * + * @throws \API_Exception + * @throws \CRM_Core_Exception + */ + protected function getTableName(): ?string { + // The old name is still stored... + $tableName = $this->getDataSourceMetadata()['table_name']; + if (!$tableName) { + return NULL; + } + if (strpos($tableName, 'civicrm_tmp_') !== 0 + || !CRM_Utils_Rule::alphanumeric($tableName)) { + // The table name is generated and stored by code, not users so it + // should be safe - but a check seems prudent all the same. + throw new CRM_Core_Exception('Table cannot be deleted'); + } + return $tableName; + } + /** * Get the fields declared for this datasource. * @@ -173,4 +281,23 @@ abstract class CRM_Import_DataSource { return empty($info['permissions']) || CRM_Core_Permission::check($info['permissions']); } + /** + * @param string $key + * @param array $data + * + * @throws \API_Exception + * @throws \Civi\API\Exception\UnauthorizedException + */ + protected function updateUserJobMetadata(string $key, array $data): void { + $metaData = array_merge( + $this->getUserJob()['metadata'], + [$key => $data] + ); + UserJob::update(FALSE) + ->addWhere('id', '=', $this->getUserJobID()) + ->setValues(['metadata' => $metaData]) + ->execute(); + $this->userJob['metadata'] = $metaData; + } + } diff --git a/CRM/Import/DataSource/CSV.php b/CRM/Import/DataSource/CSV.php index c91ca15a88..a21a4f01f6 100644 --- a/CRM/Import/DataSource/CSV.php +++ b/CRM/Import/DataSource/CSV.php @@ -84,23 +84,26 @@ class CRM_Import_DataSource_CSV extends CRM_Import_DataSource { * @param string $db * @param \CRM_Core_Form $form * + * @throws \API_Exception * @throws \CRM_Core_Exception */ public function postProcess(&$params, &$db, &$form) { $file = $params['uploadFile']['name']; + $firstRowIsColumnHeader = $params['skipColumnHeader'] ?? FALSE; $result = self::_CsvToTable( $file, - CRM_Utils_Array::value('skipColumnHeader', $params, FALSE), + $firstRowIsColumnHeader, CRM_Utils_Array::value('import_table_name', $params), CRM_Utils_Array::value('fieldSeparator', $params, ',') ); $form->set('originalColHeader', CRM_Utils_Array::value('column_headers', $result)); $form->set('importTableName', $result['import_table_name']); - $this->dataSourceMetadata = [ + $this->updateUserJobMetadata('DataSource', [ 'table_name' => $result['import_table_name'], - 'column_headers' => $result['column_headers'] ?? NULL, - ]; + 'column_headers' => $firstRowIsColumnHeader ? $result['column_headers'] : [], + 'number_of_columns' => $result['number_of_columns'], + ]); } /** @@ -253,6 +256,7 @@ class CRM_Import_DataSource_CSV extends CRM_Import_DataSource { //get the import tmp table name. $result['import_table_name'] = $tableName; + $result['number_of_columns'] = $numColumns; return $result; } diff --git a/CRM/Import/DataSource/SQL.php b/CRM/Import/DataSource/SQL.php index 2e712f4317..3d123ce6a9 100644 --- a/CRM/Import/DataSource/SQL.php +++ b/CRM/Import/DataSource/SQL.php @@ -71,14 +71,14 @@ class CRM_Import_DataSource_SQL extends CRM_Import_DataSource { $errors = []; // Makeshift query validation (case-insensitive regex matching on word boundaries) - $forbidden = ['ALTER', 'CREATE', 'DELETE', 'DESCRIBE', 'DROP', 'SHOW', 'UPDATE', 'information_schema']; + $forbidden = ['ALTER', 'CREATE', 'DELETE', 'DESCRIBE', 'DROP', 'SHOW', 'UPDATE', 'REPLACE', 'information_schema']; foreach ($forbidden as $pattern) { if (preg_match("/\\b$pattern\\b/i", $fields['sqlQuery'])) { $errors['sqlQuery'] = ts('The query contains the forbidden %1 command.', [1 => $pattern]); } } - return $errors ? $errors : TRUE; + return $errors ?: TRUE; } /** @@ -88,7 +88,9 @@ class CRM_Import_DataSource_SQL extends CRM_Import_DataSource { * @param string $db * @param \CRM_Core_Form $form * + * @throws \API_Exception * @throws \CRM_Core_Exception + * @throws \Civi\API\Exception\UnauthorizedException */ public function postProcess(&$params, &$db, &$form) { $importJob = new CRM_Contact_Import_ImportJob( @@ -97,9 +99,21 @@ class CRM_Import_DataSource_SQL extends CRM_Import_DataSource { ); $form->set('importTableName', $importJob->getTableName()); - $this->dataSourceMetadata = [ + // Get the names of the fields to be imported. Any fields starting with an + // underscore are considered to be internal to the import process) + $columnsResult = CRM_Core_DAO::executeQuery( + 'SHOW FIELDS FROM ' . $importJob->getTableName() . " + WHERE Field NOT LIKE '\_%'"); + + $columnNames = []; + while ($columnsResult->fetch()) { + $columnNames[] = $columnsResult->Field; + } + $this->updateUserJobMetadata('DataSource', [ 'table_name' => $importJob->getTableName(), - ]; + 'column_headers' => $columnNames, + 'number_of_columns' => count($columnNames), + ]); } } diff --git a/CRM/Import/Forms.php b/CRM/Import/Forms.php index be62a5200e..b1fc3a4d66 100644 --- a/CRM/Import/Forms.php +++ b/CRM/Import/Forms.php @@ -333,4 +333,52 @@ class CRM_Import_Forms extends CRM_Core_Form { $this->userJob['metadata'] = $metaData; } + /** + * Get column headers for the datasource or empty array if none apply. + * + * This would be the first row of a csv or the fields in an sql query. + * + * If the csv does not have a header row it will be empty. + * + * @return array + * + * @throws \API_Exception + * @throws \CRM_Core_Exception + */ + protected function getColumnHeaders(): array { + return $this->getDataSourceObject()->getColumnHeaders(); + } + + /** + * Get the number of importable columns in the data source. + * + * @return int + * + * @throws \API_Exception + * @throws \CRM_Core_Exception + */ + protected function getNumberOfColumns(): int { + return $this->getDataSourceObject()->getNumberOfColumns(); + } + + /** + * Get x data rows from the datasource. + * + * At this stage we are fetching from what has been stored in the form + * during `postProcess` on the DataSource form. + * + * In the future we will use the dataSource object, likely + * supporting offset as well. + * + * @param int $limit + * + * @return array + * + * @throws \CRM_Core_Exception + * @throws \API_Exception + */ + protected function getDataRows(int $limit): array { + return $this->getDataSourceObject()->getRows($limit); + } + } diff --git a/templates/CRM/Contact/Import/Form/MapField.tpl b/templates/CRM/Contact/Import/Form/MapField.tpl index 388b2f77e2..a5f888946d 100644 --- a/templates/CRM/Contact/Import/Form/MapField.tpl +++ b/templates/CRM/Contact/Import/Form/MapField.tpl @@ -19,7 +19,7 @@
{include file="CRM/common/formButtons.tpl" location="top"}
{* Table for mapping data to CRM fields *} - {include file="CRM/Contact/Import/Form/MapTable.tpl} + {include file="CRM/Contact/Import/Form/MapTable.tpl" mapper=$form.mapper}