/** * Import data into table from CSV file. * * @todo Add support for propel advanced columns (array). * * @throws Exception */ public function showImport() { $modelClass = $_GET['table']; $tableMap = PropelQuery::from($modelClass)->getTableMap(); $columnOptions = Curry_Array::objectsToArray($tableMap->getColumns(), 'getName', 'getPhpName'); $pks = array(); foreach ($tableMap->getColumns() as $column) { if ($column->isPrimaryKey()) { $pks[] = $column->getName(); } } $form = new Curry_Form(array('method' => 'post', 'action' => url('', $_GET), 'elements' => array('file' => array('file', array('label' => 'CSV File', 'valueDisabled' => true)), 'mode' => array('select', array('label' => 'Mode', 'multiOptions' => array(self::IMPORT_REPLACE => 'Replace', self::IMPORT_APPEND => 'Append', self::IMPORT_UPDATE => 'Update', self::IMPORT_UPDATE_OR_INSERT => 'Update or insert'))), 'skip_first' => array('checkbox', array('label' => 'Skip first line', 'value' => true)), 'columns' => array('text', array('label' => 'Columns in file', 'value' => join(',', array_keys($columnOptions)))), 'use_columns' => array('multiselect', array('label' => 'Columns to use', 'multiOptions' => $columnOptions, 'value' => array_keys($columnOptions), 'size' => min(10, count($columnOptions)))), 'delimiter' => array('text', array('label' => 'Delimiter', 'value' => ',')), 'enclosure' => array('text', array('label' => 'Enclosure', 'value' => '"')), 'escape' => array('text', array('label' => 'Escape', 'value' => '\\')), 'null_value' => array('text', array('label' => 'Null', 'value' => 'Ø')), 'submit' => array('submit', array('label' => 'Import'))))); $fields = array_slice(array_keys($form->getElements()), 2, -1); $form->addDisplayGroup($fields, 'advanced', array('legend' => 'Advanced options', 'class' => 'advanced', 'order' => 2)); $this->addMainContent('<h2>Import: ' . htmlspecialchars($modelClass) . '</h2>'); if (isPost() && $form->isValid($_POST)) { $mode = $form->mode->getValue(); $skipFirst = $form->skip_first->getValue(); $columns = explode(',', $form->columns->getValue()); $useColumns = $form->use_columns->getValue(); $delimiter = $form->delimiter->getValue(); $enclosure = $form->enclosure->getValue(); $escape = $form->escape->getValue(); $nullValue = $form->null_value->getValue(); if (!$form->file->isUploaded()) { throw new Exception('Error when uploading file.'); } // Check for non-existent columns $nonExistent = array_filter(array_diff($columns, array_keys($columnOptions))); if (count($nonExistent)) { throw new Exception('Unknown column in column list: ' . join(', ', $nonExistent)); } // Open csv file $fileInfo = $form->file->getFileInfo(); $fp = fopen($fileInfo['file']['tmp_name'], "r"); if (!$fp) { throw new Exception('Unable to open file'); } // Wrap in transaction $deleted = 0; $updated = 0; $inserted = 0; $con = Propel::getConnection(PropelQuery::from($modelClass)->getDbName()); $con->beginTransaction(); try { // Replace will empty the table if ($mode === self::IMPORT_REPLACE) { $deleted = PropelQuery::from($modelClass)->deleteAll(); } // Read csv lines while (($data = fgetcsv($fp, 0, $delimiter, $enclosure, $escape)) !== false) { if (count($data) !== count($columns)) { throw new Exception('Invalid column count ' . count($data) . ', expected ' . count($columns)); } if ($skipFirst) { $skipFirst = false; continue; } $data = array_combine($columns, $data); $pkData = array(); // Check for null values and collect primary key foreach ($data as $k => $v) { if ($v === $nullValue) { $data[$k] = $v = null; } if (in_array($k, $pks)) { $pkData[$k] = $v; } } $obj = null; if ($mode === self::IMPORT_UPDATE || $mode === self::IMPORT_UPDATE_OR_INSERT) { // attempt to find existing object using pk if (count($pkData) === count($pks)) { $obj = new $modelClass(); $obj->fromArray($pkData, BasePeer::TYPE_FIELDNAME); $obj = PropelQuery::from($modelClass)->findPk($obj->getPrimaryKey()); } if (!$obj && $mode === self::IMPORT_UPDATE_OR_INSERT) { // not found, create new $obj = new $modelClass(); } } else { // REPLACE, APPEND $obj = new $modelClass(); } // Remove unused columns foreach ($data as $k => $v) { if (!in_array($k, $useColumns)) { unset($data[$k]); } } if ($obj) { // Unset primary key columns in data when appending if ($mode === self::IMPORT_APPEND) { foreach ($pks as $pk) { if (array_key_exists($pk, $data)) { unset($data[$pk]); } } } $obj->fromArray($data, BasePeer::TYPE_FIELDNAME); if ($obj->isNew()) { // allows insert of custom primary key BasePeer::doInsert($obj->buildCriteria(), $con); ++$inserted; } else { $updated += $obj->save(); } } } $con->commit(); } catch (Exception $e) { $con->rollBack(); throw $e; } if ($deleted) { $this->addMessage('Deleted: ' . $deleted); } if ($inserted) { $this->addMessage('Inserted: ' . $inserted); } if ($updated) { $this->addMessage('Updated: ' . $updated); } $this->addMessage('All done.', self::MSG_SUCCESS); } else { $this->addMainContent($form); } }