From bacd62e260e22e298ed8140573146f7ec62aafa7 Mon Sep 17 00:00:00 2001 From: Eileen McNaughton Date: Fri, 8 Dec 2023 09:23:09 +1300 Subject: [PATCH] Fix duplicate instance of Spreadsheet import class When I revisited this code & prepared for PR I thought I had lost the Spreadsheet class, not realising I put it in the civiimport - this consolidates the 2 versions into the extension. Note that I am on the fence on whether this should be in the extension or with the others, reflected in the fact I put it in 2 different places on 2 different occasions. I got some support for the extension on chat so consolidated there https://chat.civicrm.org/civicrm/pl/xfp4cytepir7jk7axis7aftqye --- CRM/Import/DataSource.php | 4 +- CRM/Import/DataSource/Spreadsheet.php | 166 ------------------ .../Import/DataSource/DataSourceInterface.php | 30 ++++ .../Civi/Import/DataSource/Spreadsheet.php | 89 ++++++++-- .../CRM/Contact/Import/Form/Spreadsheet.tpl | 26 --- 5 files changed, 104 insertions(+), 211 deletions(-) delete mode 100644 CRM/Import/DataSource/Spreadsheet.php delete mode 100644 templates/CRM/Contact/Import/Form/Spreadsheet.tpl diff --git a/CRM/Import/DataSource.php b/CRM/Import/DataSource.php index 8b29ee61e0..f6ddab89f7 100644 --- a/CRM/Import/DataSource.php +++ b/CRM/Import/DataSource.php @@ -39,7 +39,7 @@ abstract class CRM_Import_DataSource implements DataSourceInterface { * * @return CRM_Import_DataSource */ - public function setLimit(int $limit): CRM_Import_DataSource { + public function setLimit(int $limit): DataSourceInterface { $this->limit = $limit; $this->queryResultObject = NULL; return $this; @@ -135,7 +135,7 @@ abstract class CRM_Import_DataSource implements DataSourceInterface { * * @return self */ - public function setStatuses(array $statuses): self { + public function setStatuses(array $statuses): DataSourceInterface { $this->statuses = $statuses; $this->queryResultObject = NULL; return $this; diff --git a/CRM/Import/DataSource/Spreadsheet.php b/CRM/Import/DataSource/Spreadsheet.php deleted file mode 100644 index fe5edf4f73..0000000000 --- a/CRM/Import/DataSource/Spreadsheet.php +++ /dev/null @@ -1,166 +0,0 @@ - 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/Civi/Import/DataSource/DataSourceInterface.php b/Civi/Import/DataSource/DataSourceInterface.php index f29715d6f9..b79ddc04b0 100644 --- a/Civi/Import/DataSource/DataSourceInterface.php +++ b/Civi/Import/DataSource/DataSourceInterface.php @@ -98,4 +98,34 @@ interface DataSourceInterface { */ public function getColumnHeaders(): array; + /** + * @param int $limit + * + * @return self + */ + public function setLimit(int $limit): self; + + /** + * Set the statuses to be retrieved. + * + * @param array $statuses + * + * @return self + */ + public function setStatuses(array $statuses): self; + + /** + * Get rows as an array. + * + * The array has all values. + * + * @param bool $nonAssociative + * Return as a non-associative array? + * + * @return array + * + * @throws \CRM_Core_Exception + */ + public function getRows(bool $nonAssociative = TRUE): array; + } diff --git a/ext/civiimport/Civi/Import/DataSource/Spreadsheet.php b/ext/civiimport/Civi/Import/DataSource/Spreadsheet.php index fc0d7418dc..ad4c89cce6 100644 --- a/ext/civiimport/Civi/Import/DataSource/Spreadsheet.php +++ b/ext/civiimport/Civi/Import/DataSource/Spreadsheet.php @@ -17,8 +17,9 @@ use PhpOffice\PhpSpreadsheet\Reader\Exception as ReaderException; /** * Objects that implement the DataSource interface can be used in CiviCRM imports. */ -class Spreadsheet implements DataSourceInterface { +class Spreadsheet extends \CRM_Import_DataSource implements DataSourceInterface { use DataSourceTrait; + protected const NUM_ROWS_TO_INSERT = 100; /** * Provides information about the data source. @@ -28,8 +29,8 @@ class Spreadsheet implements DataSourceInterface { */ public function getInfo(): array { return [ - 'title' => ts('Spreadsheet'), - 'template' => 'CRM/Import/Form/DataSource/Spreadsheet.tpl', + 'title' => ts('Spreadsheet (xlsx, odt)'), + 'template' => 'CRM/Import/DataSource/Spreadsheet.tpl', ]; } @@ -40,7 +41,7 @@ class Spreadsheet implements DataSourceInterface { * It should add all fields necessary to get the data * uploaded to the temporary table in the DB. * - * @param \CRM_Import_Forms $form + * @param \CRM_Contact_Import_Form_DataSource|\CRM_Import_Form_DataSourceConfig $form * * @throws \CRM_Core_Exception */ @@ -50,14 +51,19 @@ class Spreadsheet implements DataSourceInterface { } $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); - $maxFileSize = (int) \Civi::settings()->get('maxFileSize'); - $form->setMaxFileSize($maxFileSize * 1024 * 1024); + $form->setMaxFileSize($maxFileSizeBytes); $form->addRule('uploadFile', ts('File size should be less than %1 MBytes (%2 bytes)', [ - 1 => \Civi::settings()->get('maxFileSize'), - ]), 'maxfilesize', $maxFileSize * 1024 * 1024); + 1 => $maxFileSizeMegaBytes, + 2 => $maxFileSizeBytes, + ]), 'maxfilesize', $maxFileSizeBytes); $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()); } @@ -107,21 +113,70 @@ class Spreadsheet implements DataSourceInterface { * @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(['']); + $result = $this->uploadToTable(); + $this->updateUserJobDataSource([ + 'table_name' => $result['import_table_name'], + 'column_headers' => $result['column_headers'], + 'number_of_columns' => $result['number_of_columns'], + ]); } catch (ReaderException $e) { throw new \CRM_Core_Exception(ts('Spreadsheet not loaded.') . '' . $e->getMessage()); } + } + + /** + * @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)); + } + $this->addTrackingFieldsToTable($tableName); + + return [ + 'import_table_name' => $tableName, + 'number_of_columns' => $numColumns, + 'column_headers' => $columnHeaders, + ]; } } diff --git a/templates/CRM/Contact/Import/Form/Spreadsheet.tpl b/templates/CRM/Contact/Import/Form/Spreadsheet.tpl deleted file mode 100644 index a532032b9e..0000000000 --- a/templates/CRM/Contact/Import/Form/Spreadsheet.tpl +++ /dev/null @@ -1,26 +0,0 @@ -{* - +--------------------------------------------------------------------+ - | 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}
- -- 2.25.1