CRM/Import/DataSource/CSV.php

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