/**
* TODO odstranit - respektive přepracovat pro import v MySQLDatabase.php
* Funkce pro import CSV souboru
* @param string $filename
* @param DbConnection $dbConnection
* @param string $table
* @param string $encoding
* @param string $delimiter
* @param string $enclosure
* @param string $escapeCharacter
* @param string|null $nullValue=null
* @throws ApplicationException
*/
public function OLD_importCsvFile($filename, DbConnection $dbConnection, &$table, $encoding = 'utf-8', $delimiter = ',', $enclosure = '"', $escapeCharacter = '\\', $nullValue = "")
{
//připravení parametrů pro DB tabulku
$this->changeFileEncoding($filename, $encoding);
$csvColumns = $this->getColsInCSV($filename, $delimiter, $enclosure, $escapeCharacter);
//otevření databáze a vytvoření DB tabulky
$this->databasesFacade->openDatabase($dbConnection);
if (!$this->databasesFacade->createTable($table, $csvColumns)) {
throw new ApplicationException('Table creation failed!');
}
//projití CSV souboru a import dat do DB
$colsNames = array();
foreach ($csvColumns as $column) {
$colsNames[] = $column->name;
}
$colsCount = count($colsNames);
if (@$this->tryDirectFileImport[$dbConnection->type]) {
#region try direct import...
$result = $this->databasesFacade->importCsvFile($table, $colsNames, $this->getFilePath($filename), $delimiter, $enclosure, $escapeCharacter, $nullValue, 1, DatabasesFacade::FIRST_DB);
if ($result) {
return;
} else {
$this->databasesFacade->truncateTable($table);
}
#endregion
}
#region postupný import pomocí samostatných dotazů
$csvFile = CsvImport::openCsv($this->getFilePath($filename));
CsvImport::getRowsFromOpenedCSVFile($csvFile, 1, $delimiter, $enclosure, $escapeCharacter, $nullValue);
//přeskakujeme první řádek
while ($row = CsvImport::getRowsFromOpenedCSVFile($csvFile, 1, $delimiter, $enclosure, $escapeCharacter, $nullValue)) {
if (isset($row[0])) {
$row = $row[0];
//chceme jen jeden řádek
}
$insertArr = array();
for ($i = 0; $i < $colsCount; $i++) {
if (isset($row[$i])) {
$insertArr[$colsNames[$i]] = $row[$i];
} else {
$insertArr[$colsNames[$i]] = null;
}
}
$this->databasesFacade->insertRow($table, $insertArr);
}
CsvImport::closeCsv($csvFile);
#endregion
}
/**
* Formulář pro import CSV souboru
* @return Form
*/
public function createComponentImportCsvForm()
{
$form = new Form();
$form->setTranslator($this->translator);
$tableName = $form->addText('table', 'Table name:')->setAttribute('class', 'normalWidth')->setRequired('Input table name!');
$presenter = $this;
$tableName->addRule(Form::MAX_LENGTH, 'Max length of the table name is %s characters!', 30)->addRule(Form::MIN_LENGTH, 'Min length of the table name is %s characters!', 3)->addRule(Form::PATTERN, 'Table name can contain only letters, numbers and underscore and start with a letter!', '[a-zA-Z0-9_]+')->addRule(function (TextInput $control) use($presenter) {
$formValues = $control->form->getValues(true);
$csvColumnsCount = $presenter->fileImportsFacade->getColsCountInCSV($formValues['file'], $formValues['separator'], $formValues['enclosure'], $formValues['escape']);
$databaseType = $presenter->databasesFacade->prefferedDatabaseType($csvColumnsCount);
$newDatasource = $presenter->datasourcesFacade->prepareNewDatasourceForUser($this->usersFacade->findUser($presenter->user->id), $databaseType);
$presenter->databasesFacade->openDatabase($newDatasource->getDbConnection());
return !$presenter->databasesFacade->checkTableExists($control->value);
}, 'Table with this name already exists!');
$form->addSelect('separator', 'Separator:', array(',' => 'Comma (,)', ';' => 'Semicolon (;)', '|' => 'Vertical line (|)', '\\t' => 'Tab (\\t)'))->setRequired()->setAttribute('class', 'normalWidth');
$form->addSelect('encoding', 'Encoding:', array('utf8' => 'UTF-8', 'cp1250' => 'WIN 1250', 'iso-8859-1' => 'ISO 8859-1'))->setRequired()->setAttribute('class', 'normalWidth');
$file = $form->addHidden('file');
$file->setAttribute('id', 'frm-importCsvForm-file');
$form->addHidden('type');
$form->addText('enclosure', 'Enclosure:', 1, 1)->setDefaultValue('"');
$form->addText('escape', 'Escape:', 1, 1)->setDefaultValue('\\');
$nullValuesArr = CsvImport::getDefaultNullValuesArr();
$defaultNullValue = 'none';
foreach ($nullValuesArr as $value => $text) {
$defaultNullValue = $value;
break;
}
$form->addSelect('nullValue', 'Null values:', array_merge(['none' => '--none--'], $nullValuesArr))->setAttribute('title', 'This value will be imported as missing (null).')->setDefaultValue($defaultNullValue)->setAttribute('class', 'normalWidth');
$form->addSubmit('submit', 'Import data into database...')->onClick[] = function (SubmitButton $submitButton) {
/** @var Form $form */
$form = $submitButton->form;
$values = $form->getValues();
$nullValue = $values->nullValue == 'none' ? null : $values->nullValue;
$user = $this->usersFacade->findUser($this->user->id);
$colsCount = $this->fileImportsFacade->getColsCountInCSV($values->file, $values->separator, $values->enclosure, $values->escape);
$dbType = $this->databasesFacade->prefferedDatabaseType($colsCount);
//připravení připojení k DB
$datasource = $this->datasourcesFacade->prepareNewDatasourceForUser($user, $dbType);
$this->fileImportsFacade->importCsvFile($values->file, $datasource->getDbConnection(), $values->table, $values->encoding, $values->separator, $values->enclosure, $values->escape, $nullValue);
$datasource->name = $values->table;
//uložíme datasource
$this->datasourcesFacade->saveDatasource($datasource);
//smažeme dočasné soubory...
$this->fileImportsFacade->deleteFile($values->file);
$this->redirect('Data:newMinerFromDatasource', array('datasource' => $datasource->datasourceId));
};
$form->addSubmit('storno', 'storno')->setValidationScope([])->onClick[] = function (SubmitButton $button) use($file) {
/** @var DataPresenter $presenter */
$presenter = $button->form->getParent();
$this->fileImportsFacade->deleteFile($file->value);
$presenter->redirect('Data:newMiner');
};
return $form;
}
public function createComponentUploadConfigForm()
{
$form = new Form();
$form->setTranslator($this->translator);
$name = $form->addText('name', 'Datasource:')->setAttribute('title', 'Datasource name')->setRequired('Input datasource name!');
/** @noinspection PhpUndefinedMethodInspection */
$name->addRule(Form::MAX_LENGTH, 'Max length of the table name is %s characters!', 30)->addRule(Form::MIN_LENGTH, 'Min length of the table name is %s characters!', 3)->addRule(Form::PATTERN, 'Datasource name can contain only letters, numbers and underscore and start with a letter!', '[a-zA-Z0-9_]+');
$form->addSelect('separator', 'Separator:', [',' => 'Comma (,)', ';' => 'Semicolon (;)', '|' => 'Vertical line (|)', '\\t' => 'Tab (\\t)'])->setRequired();
$form->addSelect('encoding', 'Encoding:', ['utf8' => 'UTF-8', 'cp1250' => 'WIN 1250', 'iso-8859-1' => 'ISO 8859-1'])->setRequired();
$form->addText('enclosure', 'Enclosure:', 1, 1)->setDefaultValue('"');
$form->addText('escape', 'Escape:', 1, 1)->setDefaultValue('\\');
$nullValuesArr = CsvImport::getDefaultNullValuesArr();
//XXX check
$defaultNullValue = 'none';
foreach ($nullValuesArr as $value => $text) {
$defaultNullValue = $value;
break;
}
$form->addSelect('nullValue', 'Null values:', array_merge(['none' => '--none--'], $nullValuesArr))->setAttribute('title', 'This value will be imported as missing (null).')->setDefaultValue($defaultNullValue);
$form->addSelect('locale', 'Locale:', ['en' => 'en (0.00)', 'cs' => 'cs (0,00)'])->setDefaultValue("en")->setAttribute('title', 'Select, if you use decimal comma or point.');
//přidání submit tlačítek
$form->addSubmit('submit', 'Confirm upload config...')->onClick[] = function () {
//nepodporujeme upload bez javascriptové konfigurace
$this->flashMessage('For file upload using UI, you have to enable the javascript code!', 'error');
$this->redirect('newMiner');
};
return $form;
}
