/**
* 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);
}
}
