From 40a0240ee1bc2a4a900422dedd7131dfa69b7590 Mon Sep 17 00:00:00 2001 From: Eileen McNaughton Date: Wed, 8 Nov 2023 17:47:41 +1300 Subject: [PATCH] Add support for spreadsheet format in imports --- CRM/Contact/Import/Parser/Contact.php | 23 +- CRM/Import/DataSource.php | 191 +----------- CRM/Import/DataSource/CSV.php | 92 +----- CRM/Import/DataSource/SQL.php | 10 +- CRM/Import/DataSource/Spreadsheet.php | 166 ++++++++++ CRM/Import/Form/DataSourceConfig.php | 4 +- CRM/Import/Forms.php | 10 +- CRM/Utils/Number.php | 18 +- .../Import/DataSource/DataSourceInterface.php | 101 ++++++ Civi/Import/DataSource/DataSourceTrait.php | 289 ++++++++++++++++++ .../Civi/Import/DataSource/Spreadsheet.php | 127 ++++++++ .../CRM/Import/DataSource/Spreadsheet.tpl | 27 ++ .../CRM/Contact/Import/Form/Spreadsheet.tpl | 26 ++ .../CRM/Contact/Import/Parser/ContactTest.php | 4 + 14 files changed, 793 insertions(+), 295 deletions(-) create mode 100644 CRM/Import/DataSource/Spreadsheet.php create mode 100644 Civi/Import/DataSource/DataSourceInterface.php create mode 100644 Civi/Import/DataSource/DataSourceTrait.php create mode 100644 ext/civiimport/Civi/Import/DataSource/Spreadsheet.php create mode 100644 ext/civiimport/templates/CRM/Import/DataSource/Spreadsheet.tpl create mode 100644 templates/CRM/Contact/Import/Form/Spreadsheet.tpl diff --git a/CRM/Contact/Import/Parser/Contact.php b/CRM/Contact/Import/Parser/Contact.php index 2b38376013..7e350cbd3e 100644 --- a/CRM/Contact/Import/Parser/Contact.php +++ b/CRM/Contact/Import/Parser/Contact.php @@ -913,22 +913,21 @@ class CRM_Contact_Import_Parser_Contact extends CRM_Import_Parser { } } else { - if (is_array($params[$key]) ?? FALSE) { - foreach ($params[$key] as $innerKey => $value) { - if ($modeFill) { - $getValue = CRM_Utils_Array::retrieveValueRecursive($contact, $key); - if (isset($getValue)) { - foreach ($getValue as $cnt => $values) { - if ((!empty($getValue[$cnt]['location_type_id']) && !empty($params[$key][$innerKey]['location_type_id'])) && $getValue[$cnt]['location_type_id'] == $params[$key][$innerKey]['location_type_id']) { - unset($params[$key][$innerKey]); - } + + foreach ($value as $innerKey => $locationValues) { + if ($modeFill) { + $getValue = CRM_Utils_Array::retrieveValueRecursive($contact, $key); + if (isset($getValue)) { + foreach ($getValue as $cnt => $values) { + if ((!empty($getValue[$cnt]['location_type_id']) && !empty($params[$key][$innerKey]['location_type_id'])) && $getValue[$cnt]['location_type_id'] == $params[$key][$innerKey]['location_type_id']) { + unset($params[$key][$innerKey]); } } } } - if (count($params[$key]) == 0) { - unset($params[$key]); - } + } + if (count($params[$key]) == 0) { + unset($params[$key]); } } } diff --git a/CRM/Import/DataSource.php b/CRM/Import/DataSource.php index b44933274a..8b29ee61e0 100644 --- a/CRM/Import/DataSource.php +++ b/CRM/Import/DataSource.php @@ -15,14 +15,15 @@ * @copyright CiviCRM LLC https://civicrm.org/licensing */ -use Civi\Api4\UserJob; +use Civi\Import\DataSource\DataSourceInterface; +use Civi\Import\DataSource\DataSourceTrait; /** * This class defines the DataSource interface but must be subclassed to be * useful. */ -abstract class CRM_Import_DataSource { - +abstract class CRM_Import_DataSource implements DataSourceInterface { + use DataSourceTrait; /** * @var \CRM_Core_DAO */ @@ -140,17 +141,6 @@ abstract class CRM_Import_DataSource { return $this; } - /** - * Class constructor. - * - * @param int|null $userJobID - */ - public function __construct(int $userJobID = NULL) { - if ($userJobID) { - $this->setUserJobID($userJobID); - } - } - /** * Form fields declared for this datasource. * @@ -158,73 +148,6 @@ abstract class CRM_Import_DataSource { */ protected $submittableFields = []; - /** - * User job id. - * - * This is the primary key of the civicrm_user_job table which is used to - * track the import. - * - * @var int - */ - protected $userJobID; - - /** - * @return int|null - */ - public function getUserJobID(): ?int { - return $this->userJobID; - } - - /** - * Set user job ID. - * - * @param int $userJobID - */ - public function setUserJobID(int $userJobID): void { - $this->userJobID = $userJobID; - } - - /** - * User job details. - * - * This is the relevant row from civicrm_user_job. - * - * @var array - */ - protected $userJob; - - /** - * Get User Job. - * - * API call to retrieve the userJob row. - * - * @return array - * - * @throws \CRM_Core_Exception - */ - protected function getUserJob(): array { - if (!$this->userJob) { - $this->userJob = UserJob::get() - ->addWhere('id', '=', $this->getUserJobID()) - ->execute() - ->first(); - } - return $this->userJob; - } - - /** - * Get submitted value. - * - * Get a value submitted on the form. - * - * @return mixed - * - * @throws \CRM_Core_Exception - */ - protected function getSubmittedValue(string $valueName) { - return $this->getUserJob()['metadata']['submitted_values'][$valueName]; - } - /** * Get rows as an array. * @@ -286,22 +209,6 @@ abstract class CRM_Import_DataSource { return CRM_Core_DAO::singleValueQuery($query); } - /** - * 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 \CRM_Core_Exception - */ - public function getColumnHeaders(): array { - return $this->getUserJob()['metadata']['DataSource']['column_headers']; - } - /** * Get the field names of the fields holding data in the import tracking table. * @@ -394,96 +301,6 @@ abstract class CRM_Import_DataSource { return $this->submittableFields; } - /** - * Provides information about the data source. - * - * @return array - * Description of this data source, including: - * - title: string, translated, required - * - permissions: array, optional - * - */ - abstract public function getInfo(); - - /** - * This is function is called by the form object to get the DataSource's form snippet. - * - * It should add all fields necessary to get the data uploaded to the temporary table in the DB. - * - * @param CRM_Core_Form $form - */ - abstract public function buildQuickForm(&$form); - - /** - * Initialize the datasource, based on the submitted values stored in the user job. - */ - public function initialize(): void { - - } - - /** - * Determine if the current user has access to this data source. - * - * @return bool - */ - public function checkPermission() { - $info = $this->getInfo(); - return empty($info['permissions']) || CRM_Core_Permission::check($info['permissions']); - } - - /** - * @param string $key - * @param array $data - * - * @throws \CRM_Core_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; - } - - /** - * Purge any datasource related assets when the datasource is dropped. - * - * This is the datasource's chance to delete any tables etc that it created - * which will now not be used. - * - * @param array $newParams - * If the dataSource is being updated to another variant of the same - * class (eg. the csv upload was set to no column headers and they - * have resubmitted WITH skipColumnHeader (first row is a header) then - * the dataSource is still CSV and the params for the new instance - * are passed in. When changing from csv to SQL (for example) newParams is - * empty. - * - * @return array - * The details to update the DataSource key in the userJob metadata to. - * Generally and empty array but it the datasource decided (for example) - * that the table it created earlier is still consistent with the new params - * then it might decided not to drop the table and would want to retain - * some metadata. - * - * @throws \CRM_Core_Exception - * - * @noinspection PhpUnusedParameterInspection - */ - public function purge(array $newParams = []) :array { - // The old name is still stored... - $oldTableName = $this->getTableName(); - if ($oldTableName) { - CRM_Core_DAO::executeQuery('DROP TABLE IF EXISTS ' . $oldTableName); - } - return []; - } - /** * Add a status columns to the import table. * diff --git a/CRM/Import/DataSource/CSV.php b/CRM/Import/DataSource/CSV.php index ff5481f710..28b9d79d51 100644 --- a/CRM/Import/DataSource/CSV.php +++ b/CRM/Import/DataSource/CSV.php @@ -15,8 +15,7 @@ * @copyright CiviCRM LLC https://civicrm.org/licensing */ class CRM_Import_DataSource_CSV extends CRM_Import_DataSource { - const - NUM_ROWS_TO_INSERT = 100; + private const NUM_ROWS_TO_INSERT = 100; /** * Form fields declared for this datasource. @@ -32,7 +31,10 @@ class CRM_Import_DataSource_CSV extends CRM_Import_DataSource { * collection of info about this data source */ public function getInfo(): array { - return ['title' => ts('Comma-Separated Values (CSV)')]; + return [ + 'title' => ts('Comma-Separated Values (CSV)'), + 'template' => 'CRM/Contact/Import/Form/CSV.tpl', + ]; } /** @@ -42,10 +44,8 @@ class CRM_Import_DataSource_CSV extends CRM_Import_DataSource { * uploaded to the temporary table in the DB. * * @param CRM_Contact_Import_Form_DataSource|\CRM_Import_Form_DataSourceConfig $form - * - * @throws \CRM_Core_Exception */ - public function buildQuickForm(&$form) { + public function buildQuickForm(\CRM_Import_Forms $form): void { $form->add('hidden', 'hidden_dataSource', 'CRM_Import_DataSource_CSV'); $maxFileSizeMegaBytes = CRM_Utils_File::getMaxFileSize(); @@ -77,7 +77,7 @@ class CRM_Import_DataSource_CSV extends CRM_Import_DataSource { ); $this->addTrackingFieldsToTable($result['import_table_name']); - $this->updateUserJobMetadata('DataSource', [ + $this->updateUserJobDataSource([ 'table_name' => $result['import_table_name'], 'column_headers' => $result['column_headers'], 'number_of_columns' => $result['number_of_columns'], @@ -127,17 +127,11 @@ class CRM_Import_DataSource_CSV extends CRM_Import_DataSource { $columns = $this->getColumnNamesFromHeaders($firstrow); } else { - $columns = []; - foreach ($firstrow as $i => $_) { - $columns[] = "column_$i"; - } + $columns = $this->getColumnNamesForUnnamedColumns($firstrow); $result['column_headers'] = $columns; } - $table = CRM_Utils_SQL_TempTable::build()->setDurable(); - $tableName = $table->getName(); - CRM_Core_DAO::executeQuery("DROP TABLE IF EXISTS $tableName"); - $table->createWithColumns(implode(' text, ', $columns) . ' text'); + $tableName = $this->createTempTableFromColumns($columns); $numColumns = count($columns); @@ -171,7 +165,7 @@ class CRM_Import_DataSource_CSV extends CRM_Import_DataSource { $first = FALSE; // CRM-17859 Trim non-breaking spaces from columns. - $row = array_map(['CRM_Import_DataSource_CSV', 'trimNonBreakingSpaces'], $row); + $row = array_map([__CLASS__, 'trimNonBreakingSpaces'], $row); $row = array_map(['CRM_Core_DAO', 'escapeString'], $row); $sql .= "('" . implode("', '", $row) . "')"; $count++; @@ -197,30 +191,6 @@ class CRM_Import_DataSource_CSV extends CRM_Import_DataSource { return $result; } - /** - * Trim non-breaking spaces in a multibyte-safe way. - * See also dev/core#2127 - avoid breaking strings ending in à or any other - * unicode character sharing the same 0xA0 byte as a non-breaking space. - * - * @param string $string - * @return string The trimmed string - */ - public static function trimNonBreakingSpaces(string $string): string { - $encoding = mb_detect_encoding($string, NULL, TRUE); - if ($encoding === FALSE) { - // This could mean a couple things. One is that the string is - // ASCII-encoded but contains a non-breaking space, which causes - // php to fail to detect the encoding. So let's just do what we - // did before which works in that situation and is at least no - // worse in other situations. - return trim($string, chr(0xC2) . chr(0xA0)); - } - elseif ($encoding !== 'UTF-8') { - $string = mb_convert_encoding($string, 'UTF-8', [$encoding]); - } - return preg_replace("/^(\u{a0})+|(\u{a0})+$/", '', $string); - } - /** * Get default values for csv dataSource fields. * @@ -230,49 +200,9 @@ class CRM_Import_DataSource_CSV extends CRM_Import_DataSource { return [ 'fieldSeparator' => CRM_Core_Config::singleton()->fieldSeparator, 'skipColumnHeader' => 1, + 'template' => 'CRM/Contact/Import/Form/CSV.tpl', ]; } - /** - * Get the column names from the headers, turning them into something sql-suitable. - * - * @param $headers - * @return array - */ - private function getColumnNamesFromHeaders($headers): array { - $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower'; - $columns = array_map($strtolower, $headers); - $columns = array_map('trim', $columns); - $columns = str_replace(' ', '_', $columns); - $columns = preg_replace('/[^a-z_]/', '', $columns); - - // need to truncate values per mysql field name length limits - // mysql allows 64, but we need to account for appending colKey - // CRM-9079 - foreach ($columns as &$columnName) { - if (strlen($columnName) > 58) { - $columnName = substr($columnName, 0, 58); - } - } - - $hasNonUniqueColumnNames = count($columns) !== count(array_unique($columns)); - if ($hasNonUniqueColumnNames || in_array('', $columns, TRUE)) { - foreach ($columns as $colKey => & $colName) { - if (!$colName) { - $colName = "col_$colKey"; - } - elseif ($hasNonUniqueColumnNames) { - $colName .= "_$colKey"; - } - } - } - - // CRM-4881: we need to quote column names, as they may be MySQL reserved words - foreach ($columns as & $column) { - $column = "`$column`"; - } - return $columns; - } - } diff --git a/CRM/Import/DataSource/SQL.php b/CRM/Import/DataSource/SQL.php index ad6ac14ea0..6ee1c7ce3b 100644 --- a/CRM/Import/DataSource/SQL.php +++ b/CRM/Import/DataSource/SQL.php @@ -33,6 +33,7 @@ class CRM_Import_DataSource_SQL extends CRM_Import_DataSource { return [ 'title' => ts('SQL Query'), 'permissions' => ['import SQL datasource'], + 'template' => 'CRM/Contact/Import/Form/SQL.tpl', ]; } @@ -41,12 +42,9 @@ class CRM_Import_DataSource_SQL extends CRM_Import_DataSource { * form snippet. It should add all fields necesarry to get the data * uploaded to the temporary table in the DB. * - * @param CRM_Core_Form $form - * - * @return void - * (operates directly on form argument) + * @param CRM_Import_Forms $form */ - public function buildQuickForm(&$form) { + public function buildQuickForm(CRM_Import_Forms $form): void { $form->add('hidden', 'hidden_dataSource', 'CRM_Import_DataSource_SQL'); $form->add('textarea', 'sqlQuery', ts('Specify SQL Query'), ['rows' => 10, 'cols' => 45], TRUE); $form->addFormRule(['CRM_Import_DataSource_SQL', 'formRule'], $form); @@ -103,7 +101,7 @@ class CRM_Import_DataSource_SQL extends CRM_Import_DataSource { } $this->addTrackingFieldsToTable($tableName); - $this->updateUserJobMetadata('DataSource', [ + $this->updateUserJobDataSource([ 'table_name' => $tableName, 'column_headers' => $columnNames, 'number_of_columns' => count($columnNames), diff --git a/CRM/Import/DataSource/Spreadsheet.php b/CRM/Import/DataSource/Spreadsheet.php new file mode 100644 index 0000000000..fe5edf4f73 --- /dev/null +++ b/CRM/Import/DataSource/Spreadsheet.php @@ -0,0 +1,166 @@ + ts('Spreadsheet (xlsx, odt)'), + 'template' => 'CRM/Contact/Import/Form/Spreadsheet.tpl', + ]; + } + + /** + * This is function is called by the form object to get the DataSource's form snippet. + * + * It should add all fields necessary to get the data + * uploaded to the temporary table in the DB. + * + * @param CRM_Contact_Import_Form_DataSource|\CRM_Import_Form_DataSourceConfig $form + */ + public function buildQuickForm(\CRM_Import_Forms $form): void { + $form->add('hidden', 'hidden_dataSource', 'CRM_Import_DataSource_Spreadsheet'); + $form->addElement('checkbox', 'isFirstRowHeader', ts('First row contains column headers')); + + $maxFileSizeMegaBytes = CRM_Utils_File::getMaxFileSize(); + $maxFileSizeBytes = $maxFileSizeMegaBytes * 1024 * 1024; + $form->assign('uploadSize', $maxFileSizeMegaBytes); + $form->add('File', 'uploadFile', ts('Import Data File'), NULL, TRUE); + $form->setMaxFileSize($maxFileSizeBytes); + $form->addRule('uploadFile', ts('File size should be less than %1 MBytes (%2 bytes)', [ + 1 => $maxFileSizeMegaBytes, + 2 => $maxFileSizeBytes, + ]), 'maxfilesize', $maxFileSizeBytes); + $form->addFormRule([__CLASS__, 'validateUploadedFile']); + $form->setDataSourceDefaults($this->getDefaultValues()); + } + + /** + * Initialize the datasource, based on the submitted values stored in the user job. + * + * @throws \CRM_Core_Exception + */ + public function initialize(): void { + $result = $this->uploadToTable(); + $this->addTrackingFieldsToTable($result['import_table_name']); + + $this->updateUserJobDataSource([ + 'table_name' => $result['import_table_name'], + 'column_headers' => $result['column_headers'], + 'number_of_columns' => $result['number_of_columns'], + ]); + } + + /** + * @throws \CRM_Core_Exception + * @throws \Civi\Core\Exception\DBQueryException + * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception + */ + private function uploadToTable(): array { + + $file_type = IOFactory::identify($this->getSubmittedValue('uploadFile')['name']); + $objReader = IOFactory::createReader($file_type); + $objReader->setReadDataOnly(TRUE); + + $objPHPExcel = $objReader->load($this->getSubmittedValue('uploadFile')['name']); + $dataRows = $objPHPExcel->getActiveSheet()->toArray(NULL, TRUE, TRUE, TRUE); + + // Remove the header + if ($this->getSubmittedValue('isFirstRowHeader')) { + $headers = array_values(array_shift($dataRows)); + $columnHeaders = $headers; + $columns = $this->getColumnNamesFromHeaders($headers); + } + else { + $columns = $this->getColumnNamesForUnnamedColumns(array_values($dataRows[1])); + $columnHeaders = $columns; + } + + $tableName = $this->createTempTableFromColumns($columns); + $numColumns = count($columns); + // Re-key data using the headers + $sql = []; + foreach ($dataRows as $row) { + // CRM-17859 Trim non-breaking spaces from columns. + $row = array_map([__CLASS__, 'trimNonBreakingSpaces'], $row); + $row = array_map(['CRM_Core_DAO', 'escapeString'], $row); + $sql[] = "('" . implode("', '", $row) . "')"; + + if (count($sql) >= self::NUM_ROWS_TO_INSERT) { + CRM_Core_DAO::executeQuery("INSERT IGNORE INTO $tableName VALUES " . implode(', ', $sql)); + $sql = []; + } + } + + if (!empty($sql)) { + CRM_Core_DAO::executeQuery("INSERT IGNORE INTO $tableName VALUES " . implode(', ', $sql)); + } + + return [ + 'import_table_name' => $tableName, + 'number_of_columns' => $numColumns, + 'column_headers' => $columnHeaders, + ]; + } + + /** + * Get default values for csv dataSource fields. + * + * @return array + */ + public function getDefaultValues(): array { + return [ + 'isFirstRowHeader' => 1, + 'template' => 'CRM/Contact/Import/Form/Spreadsheet.tpl', + ]; + } + + /** + * Validate the file type of the uploaded file. + * + * @param array $fields + * @param array $files + * + * @return array + */ + public static function validateUploadedFile(array $fields, $files): array { + $file = $files['uploadFile']; + $tmp_file = $file['tmp_name']; + $file_type = IOFactory::identify($tmp_file); + $errors = []; + if (!in_array($file_type, ['Xlsx', 'Ods'])) { + $errors['uploadFile'] = ts('The file must be of type ODS (LibreOffice), or XLSX (Excel).'); + } + return $errors; + } + +} diff --git a/CRM/Import/Form/DataSourceConfig.php b/CRM/Import/Form/DataSourceConfig.php index b8bd425ba4..36109b7fb3 100644 --- a/CRM/Import/Form/DataSourceConfig.php +++ b/CRM/Import/Form/DataSourceConfig.php @@ -45,9 +45,7 @@ class CRM_Import_Form_DataSourceConfig extends CRM_Import_Forms { * @throws \CRM_Core_Exception */ public function preProcess(): void { - $dataSourcePath = explode('_', $this->getDataSourceClassName()); - $templateFile = 'CRM/Contact/Import/Form/' . $dataSourcePath[3] . '.tpl'; - $this->assign('dataSourceFormTemplateFile', $templateFile ?? NULL); + $this->assign('dataSourceFormTemplateFile', $this->getDataSourceObject()->getInfo()['template']); if (CRM_Utils_Request::retrieveValue('user_job_id', 'Integer')) { $this->setUserJobID(CRM_Utils_Request::retrieveValue('user_job_id', 'Integer')); } diff --git a/CRM/Import/Forms.php b/CRM/Import/Forms.php index 4535fd2121..f794fb662e 100644 --- a/CRM/Import/Forms.php +++ b/CRM/Import/Forms.php @@ -17,6 +17,8 @@ use Civi\Api4\Mapping; use Civi\Api4\UserJob; +use Civi\Core\ClassScanner; +use Civi\Import\DataSource\DataSourceInterface; use League\Csv\Writer; /** @@ -269,7 +271,8 @@ class CRM_Import_Forms extends CRM_Core_Form { */ protected function getDataSources(): array { $dataSources = []; - foreach (['CRM_Import_DataSource_SQL', 'CRM_Import_DataSource_CSV'] as $dataSourceClass) { + $classes = ClassScanner::get(['interface' => DataSourceInterface::class]); + foreach ($classes as $dataSourceClass) { $object = new $dataSourceClass(); if ($object->checkPermission()) { $dataSources[$dataSourceClass] = $object->getInfo()['title']; @@ -388,11 +391,10 @@ class CRM_Import_Forms extends CRM_Core_Form { /** * Get the relevant datasource object. * - * @return \CRM_Import_DataSource|null - * + * @return \Civi\Import\DataSource\DataSourceInterface|null * @throws \CRM_Core_Exception */ - protected function getDataSourceObject(): ?CRM_Import_DataSource { + protected function getDataSourceObject(): ?DataSourceInterface { $className = $this->getDataSourceClassName(); if ($className) { return new $className($this->getUserJobID()); diff --git a/CRM/Utils/Number.php b/CRM/Utils/Number.php index e8212c8e31..2edf8c3a60 100644 --- a/CRM/Utils/Number.php +++ b/CRM/Utils/Number.php @@ -30,7 +30,7 @@ class CRM_Utils_Number { * @link https://dev.mysql.com/doc/refman/5.1/en/fixed-point-types.html */ public static function createRandomDecimal($precision) { - list ($sigFigs, $decFigs) = $precision; + [$sigFigs, $decFigs] = $precision; $rand = rand(0, pow(10, $sigFigs) - 1); return $rand / pow(10, $decFigs); } @@ -47,7 +47,7 @@ class CRM_Utils_Number { * @link https://dev.mysql.com/doc/refman/5.1/en/fixed-point-types.html */ public static function createTruncatedDecimal($keyValue, $precision) { - list ($sigFigs, $decFigs) = $precision; + [$sigFigs, $decFigs] = $precision; $sign = ($keyValue < 0) ? '-1' : 1; // ex: -123.456 ==> 123456 $val = str_replace('.', '', abs($keyValue)); @@ -90,6 +90,20 @@ class CRM_Utils_Number { } } + /** + * Get the maximum size permitted for a file upload. + * + * @return float + */ + public static function getMaximumFileUploadSize(): float { + $uploadFileSize = \CRM_Utils_Number::formatUnitSize(\Civi::settings()->get('maxFileSize') . 'm', TRUE); + //Fetch uploadFileSize from php_ini when $config->maxFileSize is set to "no limit". + if (empty($uploadFileSize)) { + $uploadFileSize = \CRM_Utils_Number::formatUnitSize(ini_get('upload_max_filesize'), TRUE); + } + return round(($uploadFileSize / (1024 * 1024)), 2); + } + /** * Format number for display according to the current or supplied locale. * diff --git a/Civi/Import/DataSource/DataSourceInterface.php b/Civi/Import/DataSource/DataSourceInterface.php new file mode 100644 index 0000000000..f29715d6f9 --- /dev/null +++ b/Civi/Import/DataSource/DataSourceInterface.php @@ -0,0 +1,101 @@ +submitted_values. + * + * @return array + */ + public function getSubmittableFields(): array; + + /** + * Initialize the datasource, based on the submitted values stored in the user job. + * + * Generally this will include transferring the data to a database table. + * + * @throws \CRM_Core_Exception + */ + public function initialize(): void; + + /** + * Purge any datasource related assets when the datasource is dropped. + * + * This is the datasource's chance to delete any tables etc that it created + * which will now not be used. + * + * @param array $newParams + * If the dataSource is being updated to another variant of the same + * class (eg. the csv upload was set to no column headers and they + * have resubmitted WITH skipColumnHeader (first row is a header) then + * the dataSource is still CSV and the params for the new instance + * are passed in. When changing from csv to SQL (for example) newParams is + * empty. + * + * @return array + * The details to update the DataSource key in the userJob metadata to. + * Generally and empty array but it the datasource decided (for example) + * that the table it created earlier is still consistent with the new params + * then it might decided not to drop the table and would want to retain + * some metadata. + * + * @throws \CRM_Core_Exception + */ + public function purge(array $newParams = []) :array; + + /** + * Get an array of column headers, if any. + * + * This is presented to the user in the MapField screen so + * that can see what fields they are mapping. + * + * @return array + * @throws \CRM_Core_Exception + */ + public function getColumnHeaders(): array; + +} diff --git a/Civi/Import/DataSource/DataSourceTrait.php b/Civi/Import/DataSource/DataSourceTrait.php new file mode 100644 index 0000000000..2fa8b142fe --- /dev/null +++ b/Civi/Import/DataSource/DataSourceTrait.php @@ -0,0 +1,289 @@ +setUserJobID($userJobID); + } + } + + /** + * Get the ID of the user job being acted on. + * + * @return int|null + */ + public function getUserJobID(): ?int { + return $this->userJobID; + } + + /** + * Set user job ID. + * + * @param int $userJobID + */ + public function setUserJobID(int $userJobID): void { + $this->userJobID = $userJobID; + } + + /** + * Determine if the current user has access to this data source. + * + * @return bool + */ + public function checkPermission(): bool { + $info = $this->getInfo(); + return empty($info['permissions']) || \CRM_Core_Permission::check($info['permissions']); + } + + /** + * Purge any datasource related assets when the datasource is dropped. + * + * This is the datasource's chance to delete any tables etc that it created + * which will now not be used. + * + * @param array $newParams + * If the dataSource is being updated to another variant of the same + * class (eg. the csv upload was set to no column headers and they + * have resubmitted WITH skipColumnHeader (first row is a header) then + * the dataSource is still CSV and the params for the new instance + * are passed in. When changing from csv to SQL (for example) newParams is + * empty. + * + * @return array + * The details to update the DataSource key in the userJob metadata to. + * Generally and empty array but it the datasource decided (for example) + * that the table it created earlier is still consistent with the new params + * then it might decided not to drop the table and would want to retain + * some metadata. + * + * @throws \CRM_Core_Exception + */ + public function purge(array $newParams = []) :array { + // The old name is still stored... + $oldTableName = $this->getTableName(); + if ($oldTableName) { + \CRM_Core_DAO::executeQuery('DROP TABLE IF EXISTS ' . $oldTableName); + } + return []; + } + + /** + * Update the data stored in the User Job about the Data Source. + * + * @param array $data + * + * @throws \CRM_Core_Exception + * @throws \Civi\API\Exception\UnauthorizedException + */ + protected function updateUserJobDataSource(array $data): void { + $this->updateUserJobMetadata('DataSource', $data); + } + + /** + * Update the UserJob Metadata. + * + * @param string $key + * @param array $data + * + * @throws \CRM_Core_Exception + * @throws \Civi\API\Exception\UnauthorizedException + */ + private 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; + } + + /** + * Get an array of column headers, if any. + * + * This is presented to the user in the MapField screen so + * that can see what fields they are mapping. + * + * @return array + * @throws \CRM_Core_Exception + */ + public function getColumnHeaders(): array { + return $this->getUserJob()['metadata']['DataSource']['column_headers']; + } + + /** + * Get User Job. + * + * API call to retrieve the userJob row. + * + * @return array + * + * @throws \CRM_Core_Exception + */ + protected function getUserJob(): array { + if (!$this->userJob) { + $this->userJob = UserJob::get() + ->addWhere('id', '=', $this->getUserJobID()) + ->execute() + ->first(); + } + return $this->userJob; + } + + /** + * Get submitted value. + * + * Get a value submitted on the form. + * + * @return mixed + * + * @throws \CRM_Core_Exception + */ + protected function getSubmittedValue(string $valueName) { + return $this->getUserJob()['metadata']['submitted_values'][$valueName]; + } + + /** + * Get column names from the headers - munging to lower case etc. + * + * @param array $headers + * + * @return array + */ + protected function getColumnNamesFromHeaders(array $headers): array { + $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower'; + $columns = array_map($strtolower, $headers); + $columns = array_map('trim', $columns); + $columns = str_replace(' ', '_', $columns); + $columns = preg_replace('/[^a-z_]/', '', $columns); + + // need to truncate values per mysql field name length limits + // mysql allows 64, but we need to account for appending colKey + // CRM-9079 + foreach ($columns as &$colName) { + if (strlen($colName) > 58) { + $colName = substr($colName, 0, 58); + } + } + $hasDuplicateColumnName = count($columns) !== count(array_unique($columns)); + if ($hasDuplicateColumnName || in_array('', $columns, TRUE)) { + foreach ($columns as $colKey => & $colName) { + if (!$colName) { + $colName = "col_$colKey"; + } + elseif ($hasDuplicateColumnName) { + $colName .= "_$colKey"; + } + } + } + + // CRM-4881: we need to quote column names, as they may be MySQL reserved words + foreach ($columns as & $column) { + $column = "`$column`"; + } + return $columns; + } + + /** + * Get suitable column names for when no header row is in use. + * + * The result is an array like 'column_1', column_2'. SQL columns + * cannot start with a number. + * + * @param array $row + * + * @return array + */ + protected function getColumnNamesForUnnamedColumns(array $row): array { + $columns = []; + foreach ($row as $i => $_) { + $columns[] = "column_$i"; + } + return $columns; + } + + /** + * + * @param array $columns + * + * @return string + * Temp table name. + * + * @throws \Civi\Core\Exception\DBQueryException + */ + protected function createTempTableFromColumns(array $columns): string { + $table = \CRM_Utils_SQL_TempTable::build()->setDurable(); + $tableName = $table->getName(); + \CRM_Core_DAO::executeQuery("DROP TABLE IF EXISTS $tableName"); + $table->createWithColumns(implode(' text, ', $columns) . ' text'); + return $tableName; + } + + /** + * Trim non-breaking spaces in a multibyte-safe way. + * See also dev/core#2127 - avoid breaking strings ending in à or any other + * unicode character sharing the same 0xA0 byte as a non-breaking space. + * + * @param string $string + * @return string The trimmed string + */ + public static function trimNonBreakingSpaces(string $string): string { + $encoding = mb_detect_encoding($string, NULL, TRUE); + if ($encoding === FALSE) { + // This could mean a couple things. One is that the string is + // ASCII-encoded but contains a non-breaking space, which causes + // php to fail to detect the encoding. So let's just do what we + // did before which works in that situation and is at least no + // worse in other situations. + return trim($string, chr(0xC2) . chr(0xA0)); + } + if ($encoding !== 'UTF-8') { + $string = mb_convert_encoding($string, 'UTF-8', [$encoding]); + } + return preg_replace("/^(\u{a0})+|(\u{a0})+$/", '', $string); + } + +} diff --git a/ext/civiimport/Civi/Import/DataSource/Spreadsheet.php b/ext/civiimport/Civi/Import/DataSource/Spreadsheet.php new file mode 100644 index 0000000000..fc0d7418dc --- /dev/null +++ b/ext/civiimport/Civi/Import/DataSource/Spreadsheet.php @@ -0,0 +1,127 @@ + ts('Spreadsheet'), + 'template' => 'CRM/Import/Form/DataSource/Spreadsheet.tpl', + ]; + } + + /** + * This is function is called by the form object to get the DataSource's form + * snippet. + * + * It should add all fields necessary to get the data + * uploaded to the temporary table in the DB. + * + * @param \CRM_Import_Forms $form + * + * @throws \CRM_Core_Exception + */ + public function buildQuickForm(\CRM_Import_Forms $form): void { + if (\CRM_Utils_Request::retrieveValue('user_job_id', 'Integer')) { + $this->setUserJobID(\CRM_Utils_Request::retrieveValue('user_job_id', 'Integer')); + } + $form->add('hidden', 'hidden_dataSource', 'CRM_Import_DataSource_Spreadsheet'); + $form->addElement('checkbox', 'isFirstRowHeader', ts('First row contains column headers')); + $form->add('File', 'uploadFile', ts('Import Data File'), NULL, TRUE); + $maxFileSize = (int) \Civi::settings()->get('maxFileSize'); + $form->setMaxFileSize($maxFileSize * 1024 * 1024); + $form->addRule('uploadFile', ts('File size should be less than %1 MBytes (%2 bytes)', [ + 1 => \Civi::settings()->get('maxFileSize'), + ]), 'maxfilesize', $maxFileSize * 1024 * 1024); + $form->registerRule('spreadsheet', 'callback', 'isValidSpreadsheet', __CLASS__); + $form->addRule('uploadFile', ts('The file must be of type ODS (LibreOffice), XLSX (Excel).'), 'spreadsheet'); + $form->setDataSourceDefaults($this->getDefaultValues()); + } + + /** + * Is the value in the uploaded file field a valid spreadsheet. + * + * @param array $file + * + * @return bool + * + * @noinspection PhpUnused + */ + public static function isValidSpreadsheet(array $file): bool { + $file_type = IOFactory::identify($file['tmp_name']); + return in_array($file_type, ['Xlsx', 'Ods']); + } + + /** + * Get default values for excel dataSource fields. + * + * @return array + */ + public function getDefaultValues(): array { + return [ + 'isFirstRowHeader' => 1, + ]; + } + + /** + * Get array array of field names that may be submitted for this data source. + * + * The quick form for the datasource is added by ajax - meaning that QuickForm + * does not see them as part of the form. However, any fields listed in this array + * will be taken from the `$_POST` and stored to the UserJob under the DataSource key. + * + * @return array + */ + public function getSubmittableFields(): array { + return ['isFirstRowHeader', 'uploadFile']; + } + + /** + * Initialize the datasource, based on the submitted values stored in the user job. + * + * Generally this will include transferring the data to a database table. + * + * @throws \CRM_Core_Exception + */ + public function initialize(): void { + $file = $this->getSubmittedValue('uploadFile')['name']; + $file_type = IOFactory::identify($file); + try { + $objReader = IOFactory::createReader($file_type); + $objReader->setReadDataOnly(TRUE); + $objPHPExcel = $objReader->load($file); + $dataRows = $objPHPExcel->getActiveSheet()->toArray(NULL, TRUE, TRUE, TRUE); + $columnNames = $this->getSubmittedValue('isFirstRowHeader') ? $this->getColumnNamesFromHeaders($dataRows[0]) : $this->getColumnNamesForUnnamedColumns($dataRows[0]); + $this->createTempTableFromColumns($columnNames); + $this->updateUserJobDataSource(['']); + } + catch (ReaderException $e) { + throw new \CRM_Core_Exception(ts('Spreadsheet not loaded.') . '' . $e->getMessage()); + } + + } + +} diff --git a/ext/civiimport/templates/CRM/Import/DataSource/Spreadsheet.tpl b/ext/civiimport/templates/CRM/Import/DataSource/Spreadsheet.tpl new file mode 100644 index 0000000000..11f3659407 --- /dev/null +++ b/ext/civiimport/templates/CRM/Import/DataSource/Spreadsheet.tpl @@ -0,0 +1,27 @@ +{* + +--------------------------------------------------------------------+ + | Copyright CiviCRM LLC. All rights reserved. | + | | + | This work is published under the GNU AGPLv3 license with some | + | permitted exceptions and without any warranty. For full license | + | and copyright information, see https://civicrm.org/licensing | + +--------------------------------------------------------------------+ +*} +

{ts}Upload Spreadsheet{/ts}

+ + + + + + + + + + +
{$form.uploadFile.label}{$form.uploadFile.html}
+
+ {ts}The file must be of type ODS (LibreOffice), XLSX (Excel) or CSV.{/ts}
+ {ts 1=$uploadSize}Maximum Upload File Size: %1 MB{/ts} +
+
{$form.isFirstRowHeader.html} {$form.isFirstRowHeader.label}
+ diff --git a/templates/CRM/Contact/Import/Form/Spreadsheet.tpl b/templates/CRM/Contact/Import/Form/Spreadsheet.tpl new file mode 100644 index 0000000000..a532032b9e --- /dev/null +++ b/templates/CRM/Contact/Import/Form/Spreadsheet.tpl @@ -0,0 +1,26 @@ +{* + +--------------------------------------------------------------------+ + | Copyright CiviCRM LLC. All rights reserved. | + | | + | This work is published under the GNU AGPLv3 license with some | + | permitted exceptions and without any warranty. For full license | + | and copyright information, see https://civicrm.org/licensing | + +--------------------------------------------------------------------+ +*} +

{ts}Upload Spreadsheet{/ts}

+ + + + + + + + + +
{$form.uploadFile.label}{$form.uploadFile.html}
+
+ {ts}File format must be an excel or open office spreadsheet.{/ts}
+ {ts 1=$uploadSize}Maximum Upload File Size: %1 MB{/ts} +
+
{$form.isFirstRowHeader.html} {$form.isFirstRowHeader.label}
+ diff --git a/tests/phpunit/CRM/Contact/Import/Parser/ContactTest.php b/tests/phpunit/CRM/Contact/Import/Parser/ContactTest.php index 44c49dd8b0..0246d6f41e 100644 --- a/tests/phpunit/CRM/Contact/Import/Parser/ContactTest.php +++ b/tests/phpunit/CRM/Contact/Import/Parser/ContactTest.php @@ -774,6 +774,10 @@ class CRM_Contact_Import_Parser_ContactTest extends CiviUnitTestCase { $this->assertEquals('Update', $address['custom_' . $ids['custom_field_id']]); } + public function testAddressWithID() { + [$contactValues] = $this->setUpBaseContact(); + } + /** * Test gender works when you specify the label. * -- 2.25.1