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