CRM/Import/DataSource/CSV.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.5                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2014                                |
   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 2009.                                       |
  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-2014
  32  * $Id$
  33  *
  34  */
  35 class CRM_Import_DataSource_CSV extends CRM_Import_DataSource {
  36   CONST
  37     NUM_ROWS_TO_INSERT = 100;
  38
  39   /**
  40    * Provides information about the data source
  41    *
  42    * @return array collection of info about this data source
  43    *
  44    * @access public
  45    *
  46    */
  47   function getInfo() {
  48     return array('title' => ts('Comma-Separated Values (CSV)'));
  49   }
  50
  51   /**
  52    * Function to set variables up before form is built
  53    *
  54    * @access public
  55    */
  56   function preProcess(&$form) {}
  57
  58   /**
  59    * This is function is called by the form object to get the DataSource's
  60    * form snippet. It should add all fields necesarry to get the data
  61    * uploaded to the temporary table in the DB.
  62    *
  63    * @param $form
  64    *
  65    * @return void (operates directly on form argument)
  66    * @access public
  67    */
  68   function buildQuickForm(&$form) {
  69     $form->add('hidden', 'hidden_dataSource', 'CRM_Import_DataSource_CSV');
  70
  71     $config = CRM_Core_Config::singleton();
  72
  73     // FIXME: why do we limit the file size to 8 MiB if it's larger in config?
  74     $uploadFileSize = $config->maxImportFileSize >= 8388608 ? 8388608 : $config->maxImportFileSize;
  75     $uploadSize = round(($uploadFileSize / (1024 * 1024)), 2);
  76     $form->assign('uploadSize', $uploadSize);
  77     $form->add('file', 'uploadFile', ts('Import Data File'), 'size=30 maxlength=255', TRUE);
  78
  79     $form->setMaxFileSize($uploadFileSize);
  80     $form->addRule('uploadFile', ts('File size should be less than %1 MBytes (%2 bytes)', array(1 => $uploadSize, 2 => $uploadFileSize)), '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    * Function to process the form
  89    *
  90    * @access public
  91    */
  92   function postProcess(&$params, &$db, &$form) {
  93     $file = $params['uploadFile']['name'];
  94
  95     $result = self::_CsvToTable($db,
  96       $file,
  97       CRM_Utils_Array::value('skipColumnHeader', $params, FALSE),
  98       CRM_Utils_Array::value('import_table_name', $params),
  99       CRM_Utils_Array::value('fieldSeparator', $params, ',')
 100     );
 101
 102     $form->set('originalColHeader', CRM_Utils_Array::value('original_col_header', $result));
 103
 104     $table = $result['import_table_name'];
 105     $importJob = new CRM_Contact_Import_ImportJob($table);
 106     $form->set('importTableName', $importJob->getTableName());
 107   }
 108
 109   /**
 110    * Create a table that matches the CSV file and populate it with the file's contents
 111    *
 112    * @param object $db     handle to the database connection
 113    * @param string $file   file name to load
 114    * @param bool   $headers  whether the first row contains headers
 115    * @param string $table  Name of table from which data imported.
 116    * @param string $fieldSeparator Character that seperates the various columns in the file
 117    *
 118    * @return string  name of the created table
 119    */
 120   private static function _CsvToTable(&$db,
 121     $file,
 122     $headers        = FALSE,
 123     $table          = NULL,
 124     $fieldSeparator = ','
 125   ) {
 126     $result = array();
 127     $fd = fopen($file, 'r');
 128     if (!$fd) {
 129       CRM_Core_Error::fatal("Could not read $file");
 130     }
 131
 132     $config = CRM_Core_Config::singleton();
 133     // support tab separated
 134     if (strtolower($fieldSeparator) == 'tab' ||
 135       strtolower($fieldSeparator) == '\t'
 136     ) {
 137       $fieldSeparator = "\t";
 138     }
 139
 140     $firstrow = fgetcsv($fd, 0, $fieldSeparator);
 141
 142     // create the column names from the CSV header or as col_0, col_1, etc.
 143     if ($headers) {
 144       //need to get original headers.
 145       $result['original_col_header'] = $firstrow;
 146
 147       $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
 148       $columns    = array_map($strtolower, $firstrow);
 149       $columns    = str_replace(' ', '_', $columns);
 150       $columns    = preg_replace('/[^a-z_]/', '', $columns);
 151
 152       // need to take care of null as well as duplicate col names.
 153       $duplicateColName = FALSE;
 154       if (count($columns) != count(array_unique($columns))) {
 155         $duplicateColName = TRUE;
 156       }
 157
 158
 159       // need to truncate values per mysql field name length limits
 160       // mysql allows 64, but we need to account for appending colKey
 161       // CRM-9079
 162       foreach ($columns as $colKey => & $colName) {
 163         if (strlen($colName) > 58) {
 164           $colName = substr($colName, 0, 58);
 165         }
 166       }
 167
 168       if (in_array('', $columns) || $duplicateColName) {
 169         foreach ($columns as $colKey => & $colName) {
 170           if (!$colName) {
 171             $colName = "col_$colKey";
 172           }
 173           elseif ($duplicateColName) {
 174             $colName .= "_$colKey";
 175           }
 176         }
 177       }
 178
 179       // CRM-4881: we need to quote column names, as they may be MySQL reserved words
 180       foreach ($columns as & $column) $column = "`$column`";
 181     }
 182     else {
 183       $columns = array();
 184       foreach ($firstrow as $i => $_) $columns[] = "col_$i";
 185     }
 186
 187     // FIXME: we should regen this table's name if it exists rather than drop it
 188     if (!$table) {
 189       $table = 'civicrm_import_job_' . md5(uniqid(rand(), TRUE));
 190     }
 191
 192     $db->query("DROP TABLE IF EXISTS $table");
 193
 194     $numColumns = count($columns);
 195     $create = "CREATE TABLE $table (" . implode(' text, ', $columns) . " text) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci";
 196     $db->query($create);
 197
 198     // the proper approach, but some MySQL installs do not have this enabled
 199     // $load = "LOAD DATA LOCAL INFILE '$file' INTO TABLE $table FIELDS TERMINATED BY '$fieldSeparator' OPTIONALLY ENCLOSED BY '\"'";
 200     // if ($headers) {   $load .= ' IGNORE 1 LINES'; }
 201     // $db->query($load);
 202
 203     // parse the CSV line by line and build one big INSERT (while MySQL-escaping the CSV contents)
 204     if (!$headers) {
 205       rewind($fd);
 206     }
 207
 208     $sql   = NULL;
 209     $first = TRUE;
 210     $count = 0;
 211     while ($row = fgetcsv($fd, 0, $fieldSeparator)) {
 212       // skip rows that dont match column count, else we get a sql error
 213       if (count($row) != $numColumns) {
 214         continue;
 215       }
 216
 217       if (!$first) {
 218         $sql .= ', ';
 219       }
 220
 221       $first = FALSE;
 222       $row = array_map('civicrm_mysql_real_escape_string', $row);
 223       $sql .= "('" . implode("', '", $row) . "')";
 224       $count++;
 225
 226       if ($count >= self::NUM_ROWS_TO_INSERT && !empty($sql)) {
 227         $sql = "INSERT IGNORE INTO $table VALUES $sql";
 228         $db->query($sql);
 229
 230         $sql   = NULL;
 231         $first = TRUE;
 232         $count = 0;
 233       }
 234     }
 235
 236     if (!empty($sql)) {
 237       $sql = "INSERT IGNORE INTO $table VALUES $sql";
 238       $db->query($sql);
 239     }
 240
 241     fclose($fd);
 242
 243     //get the import tmp table name.
 244     $result['import_table_name'] = $table;
 245
 246     return $result;
 247   }
 248 }
 249
 250 /**
 251  * @param $string
 252  *
 253  * @return string
 254  */
 255 function civicrm_mysql_real_escape_string($string) {
 256   static $dao = NULL;
 257   if (!$dao) {
 258     $dao = new CRM_Core_DAO();
 259   }
 260   return $dao->escape($string);
 261 }
 262