CRM/Import/DataSource/CSV.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   6  | This work is published under the GNU AGPLv3 license with some      |
   7  | permitted exceptions and without any warranty. For full license    |
   8  | and copyright information, see https://civicrm.org/licensing       |
   9  +--------------------------------------------------------------------+
  10  */
  11
  12 /**
  13  *
  14  * @package CRM
  15  * @copyright CiviCRM LLC https://civicrm.org/licensing
  16  */
  17 class CRM_Import_DataSource_CSV extends CRM_Import_DataSource {
  18   const
  19     NUM_ROWS_TO_INSERT = 100;
  20
  21   /**
  22    * Provides information about the data source.
  23    *
  24    * @return array
  25    *   collection of info about this data source
  26    */
  27   public function getInfo() {
  28     return ['title' => ts('Comma-Separated Values (CSV)')];
  29   }
  30
  31   /**
  32    * Set variables up before form is built.
  33    *
  34    * @param CRM_Core_Form $form
  35    */
  36   public function preProcess(&$form) {
  37   }
  38
  39   /**
  40    * This is function is called by the form object to get the DataSource's form snippet.
  41    *
  42    * It should add all fields necessary to get the data
  43    * uploaded to the temporary table in the DB.
  44    *
  45    * @param CRM_Core_Form $form
  46    *
  47    * @throws \CRM_Core_Exception
  48    */
  49   public function buildQuickForm(&$form) {
  50     $form->add('hidden', 'hidden_dataSource', 'CRM_Import_DataSource_CSV');
  51
  52     $config = CRM_Core_Config::singleton();
  53
  54     $uploadFileSize = CRM_Utils_Number::formatUnitSize($config->maxFileSize . 'm', TRUE);
  55     //Fetch uploadFileSize from php_ini when $config->maxFileSize is set to "no limit".
  56     if (empty($uploadFileSize)) {
  57       $uploadFileSize = CRM_Utils_Number::formatUnitSize(ini_get('upload_max_filesize'), TRUE);
  58     }
  59     $uploadSize = round(($uploadFileSize / (1024 * 1024)), 2);
  60     $form->assign('uploadSize', $uploadSize);
  61     $form->add('File', 'uploadFile', ts('Import Data File'), NULL, TRUE);
  62     $form->setMaxFileSize($uploadFileSize);
  63     $form->addRule('uploadFile', ts('File size should be less than %1 MBytes (%2 bytes)', [
  64       1 => $uploadSize,
  65       2 => $uploadFileSize,
  66     ]), 'maxfilesize', $uploadFileSize);
  67     $form->addRule('uploadFile', ts('Input file must be in CSV format'), 'utf8File');
  68     $form->addRule('uploadFile', ts('A valid file must be uploaded.'), 'uploadedfile');
  69
  70     $form->addElement('checkbox', 'skipColumnHeader', ts('First row contains column headers'));
  71   }
  72
  73   /**
  74    * Process the form submission.
  75    *
  76    * @param array $params
  77    * @param string $db
  78    * @param \CRM_Core_Form $form
  79    *
  80    * @throws \CRM_Core_Exception
  81    */
  82   public function postProcess(&$params, &$db, &$form) {
  83     $file = $params['uploadFile']['name'];
  84     $result = self::_CsvToTable($db,
  85       $file,
  86       CRM_Utils_Array::value('skipColumnHeader', $params, FALSE),
  87       CRM_Utils_Array::value('import_table_name', $params),
  88       CRM_Utils_Array::value('fieldSeparator', $params, ',')
  89     );
  90
  91     $form->set('originalColHeader', CRM_Utils_Array::value('original_col_header', $result));
  92
  93     $table = $result['import_table_name'];
  94     $importJob = new CRM_Contact_Import_ImportJob($table);
  95     $form->set('importTableName', $importJob->getTableName());
  96   }
  97
  98   /**
  99    * Create a table that matches the CSV file and populate it with the file's contents
 100    *
 101    * @param object $db
 102    *   Handle to the database connection.
 103    * @param string $file
 104    *   File name to load.
 105    * @param bool $headers
 106    *   Whether the first row contains headers.
 107    * @param string $tableName
 108    *   Name of table from which data imported.
 109    * @param string $fieldSeparator
 110    *   Character that separates the various columns in the file.
 111    *
 112    * @return array
 113    *   name of the created table
 114    * @throws \CRM_Core_Exception
 115    */
 116   private static function _CsvToTable(
 117     &$db,
 118     $file,
 119     $headers = FALSE,
 120     $tableName = NULL,
 121     $fieldSeparator = ','
 122   ) {
 123     $result = [];
 124     $fd = fopen($file, 'r');
 125     if (!$fd) {
 126       throw new CRM_Core_Exception("Could not read $file");
 127     }
 128     if (filesize($file) == 0) {
 129       throw new CRM_Core_Exception("$file is empty. Please upload a valid file.");
 130     }
 131
 132     // support tab separated
 133     if (strtolower($fieldSeparator) === 'tab' ||
 134       strtolower($fieldSeparator) === '\t'
 135     ) {
 136       $fieldSeparator = "\t";
 137     }
 138
 139     $firstrow = fgetcsv($fd, 0, $fieldSeparator);
 140
 141     // create the column names from the CSV header or as col_0, col_1, etc.
 142     if ($headers) {
 143       //need to get original headers.
 144       $result['original_col_header'] = $firstrow;
 145
 146       $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
 147       $columns = array_map($strtolower, $firstrow);
 148       $columns = str_replace(' ', '_', $columns);
 149       $columns = preg_replace('/[^a-z_]/', '', $columns);
 150
 151       // need to take care of null as well as duplicate col names.
 152       $duplicateColName = FALSE;
 153       if (count($columns) != count(array_unique($columns))) {
 154         $duplicateColName = TRUE;
 155       }
 156
 157       // need to truncate values per mysql field name length limits
 158       // mysql allows 64, but we need to account for appending colKey
 159       // CRM-9079
 160       foreach ($columns as $colKey => & $colName) {
 161         if (strlen($colName) > 58) {
 162           $colName = substr($colName, 0, 58);
 163         }
 164       }
 165
 166       if (in_array('', $columns) || $duplicateColName) {
 167         foreach ($columns as $colKey => & $colName) {
 168           if (!$colName) {
 169             $colName = "col_$colKey";
 170           }
 171           elseif ($duplicateColName) {
 172             $colName .= "_$colKey";
 173           }
 174         }
 175       }
 176
 177       // CRM-4881: we need to quote column names, as they may be MySQL reserved words
 178       foreach ($columns as & $column) {
 179         $column = "`$column`";
 180       }
 181     }
 182     else {
 183       $columns = [];
 184       foreach ($firstrow as $i => $_) {
 185         $columns[] = "col_$i";
 186       }
 187     }
 188
 189     if ($tableName) {
 190       CRM_Core_DAO::executeQuery("DROP TABLE IF EXISTS $tableName");
 191     }
 192     $table = CRM_Utils_SQL_TempTable::build()->setDurable();
 193     $tableName = $table->getName();
 194     CRM_Core_DAO::executeQuery("DROP TABLE IF EXISTS $tableName");
 195     $table->createWithColumns(implode(' text, ', $columns) . ' text');
 196
 197     $numColumns = count($columns);
 198
 199     // the proper approach, but some MySQL installs do not have this enabled
 200     // $load = "LOAD DATA LOCAL INFILE '$file' INTO TABLE $table FIELDS TERMINATED BY '$fieldSeparator' OPTIONALLY ENCLOSED BY '\"'";
 201     // if ($headers) {   $load .= ' IGNORE 1 LINES'; }
 202     // $db->query($load);
 203
 204     // parse the CSV line by line and build one big INSERT (while MySQL-escaping the CSV contents)
 205     if (!$headers) {
 206       rewind($fd);
 207     }
 208
 209     $sql = NULL;
 210     $first = TRUE;
 211     $count = 0;
 212     while ($row = fgetcsv($fd, 0, $fieldSeparator)) {
 213       // skip rows that dont match column count, else we get a sql error
 214       if (count($row) != $numColumns) {
 215         continue;
 216       }
 217       // A blank line will be array(0 => NULL)
 218       if ($row === [NULL]) {
 219         continue;
 220       }
 221
 222       if (!$first) {
 223         $sql .= ', ';
 224       }
 225
 226       $first = FALSE;
 227
 228       // CRM-17859 Trim non-breaking spaces from columns.
 229       $row = array_map(['CRM_Import_DataSource_CSV', 'trimNonBreakingSpaces'], $row);
 230       $row = array_map(['CRM_Core_DAO', 'escapeString'], $row);
 231       $sql .= "('" . implode("', '", $row) . "')";
 232       $count++;
 233
 234       if ($count >= self::NUM_ROWS_TO_INSERT && !empty($sql)) {
 235         CRM_Core_DAO::executeQuery("INSERT IGNORE INTO $tableName VALUES $sql");
 236
 237         $sql = NULL;
 238         $first = TRUE;
 239         $count = 0;
 240       }
 241     }
 242
 243     if (!empty($sql)) {
 244       CRM_Core_DAO::executeQuery("INSERT IGNORE INTO $tableName VALUES $sql");
 245     }
 246
 247     fclose($fd);
 248
 249     //get the import tmp table name.
 250     $result['import_table_name'] = $tableName;
 251
 252     return $result;
 253   }
 254
 255   /**
 256    * Trim non-breaking spaces in a multibyte-safe way.
 257    * See also dev/core#2127 - avoid breaking strings ending in à or any other
 258    * unicode character sharing the same 0xA0 byte as a non-breaking space.
 259    *
 260    * @param string $string
 261    * @return string The trimmed string
 262    */
 263   public static function trimNonBreakingSpaces(string $string): string {
 264     $encoding = mb_detect_encoding($string, NULL, TRUE);
 265     if ($encoding === FALSE) {
 266       // This could mean a couple things. One is that the string is
 267       // ASCII-encoded but contains a non-breaking space, which causes
 268       // php to fail to detect the encoding. So let's just do what we
 269       // did before which works in that situation and is at least no
 270       // worse in other situations.
 271       return trim($string, chr(0xC2) . chr(0xA0));
 272     }
 273     elseif ($encoding !== 'UTF-8') {
 274       $string = mb_convert_encoding($string, 'UTF-8', [$encoding]);
 275     }
 276     return preg_replace("/^(\u{a0})+|(\u{a0})+$/", '', $string);
 277   }
 278
 279 }