/** * Akce pro dokončení uploadu datasetu (zpracování získaného JSONu, přechod na vytvoření nového datového zdroje) * @param string $uploadConfig * @param string $dataServiceResult * @throws \Exception * @throws \Nette\Utils\JsonException */ public function actionUploadFinish($uploadConfig, $dataServiceResult) { $uploadConfig = Json::decode($uploadConfig, Json::FORCE_ARRAY); $dataServiceResult = Json::decode($dataServiceResult, Json::FORCE_ARRAY); $currentUser = $this->getCurrentUser(); //vytvoření a uložení datasetu if (!empty($dataServiceResult['id']) && !empty($dataServiceResult['type']) && !empty($dataServiceResult['name'])) { $dbDatasource = new DbDatasource(@$dataServiceResult['id'], @$dataServiceResult['name'], @$dataServiceResult['type'], @$dataServiceResult['size']); $datasource = $this->datasourcesFacade->prepareNewDatasourceFromDbDatasource($dbDatasource, $currentUser); $this->datasourcesFacade->saveDatasource($datasource); } else { throw new \Exception('Upload finish failed.'); } //aktualizace názvů datových sloupců if (!empty($uploadConfig['columnNames'])) { $datasourceColumnNames = []; //vytvoříme seznam sloupců foreach ($uploadConfig['dataTypes'] as $i => $dataType) { if ($dataType == DbField::TYPE_NOMINAL || $dataType == DbField::TYPE_NUMERIC) { $datasourceColumnNames[] = $uploadConfig['columnNames'][$i]; } } $this->datasourcesFacade->renameDatasourceColumns($datasource, $datasourceColumnNames, $currentUser); } //odeslání URL pro přesměrování... $this->sendJsonResponse(['state' => 'OK', 'message' => 'Datasource created successfully.', 'redirect' => $this->link('Data:newMinerFromDatasource', ['datasource' => $datasource->datasourceId])]); }
/** * Akce pro import CSV souboru (případně komprimovaného v ZIP archívu) * @SWG\Post( * tags={"Datasources"}, * path="/datasources", * summary="Create new datasource using uploaded file", * consumes={"text/csv"}, * produces={"application/json","application/xml"}, * security={{"apiKey":{}},{"apiKeyHeader":{}}}, * @SWG\Parameter( * name="name", * description="Table name (if empty, will be auto-generated)", * required=false, * type="string", * in="query" * ), * @SWG\Parameter( * name="separator", * description="Columns separator", * required=true, * type="string", * in="query" * ), * @SWG\Parameter( * name="encoding", * description="File encoding", * required=true, * type="string", * in="query", * enum={"utf8","cp1250","iso-8859-1"} * ), * @SWG\Parameter( * name="enclosure", * description="Enclosure character", * required=false, * type="string", * in="query" * ), * @SWG\Parameter( * name="escape", * description="Escape character", * required=false, * type="string", * in="query" * ), * @SWG\Parameter( * name="nullValue", * description="Null value", * required=false, * type="string", * in="query" * ), * @SWG\Parameter( * name="type", * description="Database type", * required=true, * type="string", * enum={"limited","unlimited","mysql"}, * in="query" * ), * @SWG\Parameter( * name="file", * description="CSV file", * required=true, * type="file", * in="formData" * ), * @SWG\Response( * response=200, * description="Datasource details", * @SWG\Schema( * ref="#/definitions/DatasourceWithColumnsResponse" * ) * ), * @SWG\Response( * response=400, * description="Invalid API key supplied", * @SWG\Schema(ref="#/definitions/StatusResponse") * ) * ) * @throws \InvalidArgumentException */ public function actionCreate() { #region move uploaded file /** @var FileUpload $file */ $file = $this->request->files['file']; //detekce typu souboru $fileType = $this->fileImportsFacade->detectFileType($file->getName()); if ($fileType == FileImportsFacade::FILE_TYPE_UNKNOWN) { //jedná se o nepodporovaný typ souboru try { FileSystem::delete($this->fileImportsFacade->getTempFilename()); } catch (\Exception $e) { } throw new \InvalidArgumentException('The uploaded file is not in supported format!'); } //move file $filename = $this->fileImportsFacade->getTempFilename(); $file->move($this->fileImportsFacade->getFilePath($filename)); //pokus o automatickou extrakci souboru if ($fileType == FileImportsFacade::FILE_TYPE_ZIP) { $fileType = $this->fileImportsFacade->tryAutoUnzipFile($filename); if ($fileType != FileImportsFacade::FILE_TYPE_CSV) { try { FileSystem::delete($this->fileImportsFacade->getFilePath($filename)); } catch (\Exception $e) { } throw new \InvalidArgumentException('The uploaded ZIP file has to contain only one CSV file!'); } } #endregion move uploaded file /** @var array $inputData */ $inputData = $this->input->getData(); //prepare default values if (empty($inputData['name'])) { $inputData['name'] = FileImportsFacade::sanitizeFileNameForImport($file->sanitizedName); } else { $inputData['name'] = FileImportsFacade::sanitizeFileNameForImport($inputData['name']); } if (empty($inputData['enclosure'])) { $inputData['enclosure'] = '"'; } if (empty($inputData['escape'])) { $inputData['escape'] = '\\'; } if (empty($inputData['nullValue'])) { $inputData['nullValue'] = ''; } //upload data and prepare datasource $currentUser = $this->getCurrentUser(); $dbDatasource = $this->fileImportsFacade->importCsvFile($filename, $inputData['type'], $currentUser, $inputData['name'], $inputData['encoding'], $inputData['separator'], $inputData['enclosure'], $inputData['escape'], $inputData['nullValue']); $datasource = $this->datasourcesFacade->prepareNewDatasourceFromDbDatasource($dbDatasource, $currentUser); $this->datasourcesFacade->saveDatasource($datasource); //aktualizace informace o datových sloupcích $this->datasourcesFacade->updateDatasourceColumns($datasource, $currentUser); //send response $this->actionRead($datasource->datasourceId); }
/** * 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; }