public Task <HttpResponseMessage> UploadImportFile() { logger.Debug("Inside DataActionController, UploadImportFile..."); logger.Debug("starting to process incoming files."); if (!Request.Content.IsMimeMultipartContent()) { throw new HttpResponseException(HttpStatusCode.UnsupportedMediaType); } //string root = System.Web.HttpContext.Current.Server.MapPath("~/uploads"); string root = System.Configuration.ConfigurationManager.AppSettings["UploadsDirectory"] + "\\P\\"; logger.Debug("root location = " + root); string rootUrl = Request.RequestUri.AbsoluteUri.Replace(Request.RequestUri.AbsolutePath, String.Empty); logger.Debug("root Url = " + rootUrl); // Make sure our folder exists DirectoryInfo dirInfo = new DirectoryInfo(@root); if (!dirInfo.Exists) { logger.Debug("Dir does not exist; will create it..."); try { System.IO.Directory.CreateDirectory(root); logger.Debug("Created the dir..."); } catch (IOException ioe) { logger.Debug("Exception: " + ioe.Message + ", " + ioe.InnerException); } } else { logger.Debug("P dir already exists..."); } logger.Debug("saving files to location: " + root); logger.Debug(" and the root url = " + rootUrl); var provider = new MultipartFormDataStreamProvider(root); logger.Debug("provider = " + provider.ToString()); User me = AuthorizationManager.getCurrentUser(); var db = ServicesContext.Current; var task = Request.Content.ReadAsMultipartAsync(provider).ContinueWith(o => { logger.Debug("Inside task part..."); if (o.IsFaulted || o.IsCanceled) { logger.Debug("Error: " + o.Exception.Message); throw new HttpResponseException(Request.CreateErrorResponse(HttpStatusCode.InternalServerError, o.Exception)); } //Look up our project logger.Debug("provider.FormData = " + provider.FormData); Int32 ProjectId = Convert.ToInt32(provider.FormData.Get("ProjectId")); logger.Debug("And we think the projectid === " + ProjectId); Int32 DatasetId = Convert.ToInt32(provider.FormData.Get("DatasetId")); logger.Debug("And we think the DatasetId === " + DatasetId); Project project = db.Projects.Find(ProjectId); logger.Debug("Project.Name = " + project.Name); Dataset dataset = db.Datasets.Find(DatasetId); logger.Debug("dataset.Name = " + dataset.Name); if (project == null) { throw new Exception("Project ID not found: " + ProjectId); } if (!project.isOwnerOrEditor(me)) { throw new Exception("Authorization error: The user trying to import is neither an Owner nor an Editor."); } else { logger.Debug("User authorized = " + me); } //If the project/dataset folder does not exist, create it. string datasetPath = root + project.Id + "\\D\\" + dataset.Id; //DirectoryInfo datasetDirInfo = new DirectoryInfo(@root); DirectoryInfo datasetDirInfo = new DirectoryInfo(datasetPath); if (!datasetDirInfo.Exists) { logger.Debug("Dir does not exist; will create it..."); try { System.IO.Directory.CreateDirectory(datasetPath); logger.Debug("Created the dir..."); } catch (IOException ioe) { logger.Debug("Exception: " + ioe.Message + ", " + ioe.InnerException); } } var newFileName = ""; foreach (MultipartFileData file in provider.FileData) { logger.Debug("Filename = " + file.LocalFileName); logger.Debug("Orig = " + file.Headers.ContentDisposition.FileName); logger.Debug("Name? = " + file.Headers.ContentDisposition.Name); var fileIndex = FileController.getFileIndex(file.Headers.ContentDisposition.Name); //"uploadedfile0" -> 0 var filename = file.Headers.ContentDisposition.FileName; filename = filename.Replace("\"", string.Empty); if (!String.IsNullOrEmpty(filename)) { try { newFileName = FileController.relocateDatasetFile( file.LocalFileName, ProjectId, DatasetId, filename, true); // For importing, we do not want to add the file to the Files table. /* * File newFile = new File(); * newFile.Title = provider.FormData.Get("Title_" + fileIndex); //"Title_1, etc. * newFile.Description = provider.FormData.Get("Description_" + fileIndex); //"Description_1, etc. * newFile.Name = info.Name;//.Headers.ContentDisposition.FileName; * newFile.Link = rootUrl + "/services/uploads/" + ProjectId + "/" + info.Name; //file.LocalFileName; * newFile.Size = (info.Length / 1024).ToString(); //file.Headers.ContentLength.ToString(); * newFile.FileTypeId = FileType.getFileTypeFromFilename(info); * newFile.UserId = me.Id; * logger.Debug(" Adding file " + newFile.Name + " at " + newFile.Link); * * files.Add(newFile); */ } catch (Exception e) { logger.Debug("Error: " + e.ToString()); } } } logger.Debug("Done saving files."); var data = new ImportDataResult(); var info = new FileInfo(newFileName); // Process the file and return all the data! /* Note: According to Colette, if someone tries to upload a file with an odd extension (.lkg, .fld, MCR, BC1, etc.), * while the extension may vary, it will almost always be a ScrewTrap-PITAGIS related file. * Therefore, we are allowing a wide range of variation in the extensions. */ //var regex = new Regex(@"\.(m|r|ur|mc)\d+$"); //var regexNums = new Regex(@"\.(m|r|ur|mc|bc)\d+$"); //var regexChars = new Regex(@"\.(m|r|ur|mc|bc)\D+$"); var regexNums = new Regex(@"\.(m|r|ur|mc|bc|nb)\d+$"); var regexChars = new Regex(@"\.(m|r|ur|mc|bc|nb)\D+$"); var extension = info.Extension.ToLower(); logger.Debug("extension = " + extension); if (extension == ".xls" || extension == ".xlsx") { logger.Debug("Looks like an excel file!"); var reader = new ExcelReader(newFileName); //ExcelReader doesn't support starting on a certain line for column names... we always assume col 1 data.columns = reader.getColumns(); data.rows = reader.getData().First().Table; reader.close(); } else if (extension == ".csv") { logger.Debug("Looks like a csv file!"); var StartOnLine = Convert.ToInt32(provider.FormData.Get("StartOnLine")); //only applicable to T/CSV var reader = new CSVReader(newFileName); data = reader.getImportDataResult(StartOnLine); // we do it all in one. } else if (extension == ".tsv") { logger.Debug("Looks like a tsv file!"); var StartOnLine = Convert.ToInt32(provider.FormData.Get("StartOnLine")); //only applicable to T/CSV var reader = new TSVReader(newFileName); data = reader.getImportDataResult(StartOnLine); // we do it all in one. } //else if (extension == ".lkg" || extension == ".fld" || regex.Match(extension).Success) else if (extension == ".lkg" || extension == ".fld" || regexNums.Match(extension).Success || regexChars.Match(extension).Success) { logger.Debug("Looks like a PITAGIS file!"); var reader = new PitagisReader(newFileName); data = reader.getImportDataResult(); // we do it all in one. } else { logger.Debug("Looks like an unknown file!"); throw new Exception("File type not compatible. We can do Excel (xls/xslx), CSV (csv), TSV (tsv), and PITAGIS (.lkg/.fld/.m01/.r01/.ur1/.mc1)."); } var result = JsonConvert.SerializeObject(data); //TODO: actual error/success message handling //string result = "{\"message\": \"Success\"}"; var resp = new HttpResponseMessage(HttpStatusCode.OK); resp.Content = new StringContent(result, System.Text.Encoding.UTF8, "text/plain"); //to stop IE from being stupid. return(resp); }); return(task); }
public async Task <ImportDataResult> ImportDataAsync(ExternalTestImportRequestArgs requestArgs) { var result = new ImportDataResult(); var xssfwb = new XSSFWorkbook(requestArgs.DataStream); var sheet = xssfwb.GetSheetAt(0); var headerRow = sheet.GetRow(0); var countryRequiredTestTypeIDs = new[] { TestTypes.MARKER_2GB_TEST, TestTypes.DNA_ISOLATION }; var countryRequired = (countryRequiredTestTypeIDs.Contains(requestArgs.TestTypeID)); var validColumns = new List <string> { "Numerical ID", "Sample Name" }; if (countryRequired) { validColumns.Add("Country"); } var headers = new Dictionary <int, string>(); //read headers from excel's first row if (headerRow == null) { result.Errors.Add("Invalid file format. Header information is missing."); return(result); } for (int i = 0; i < headerRow.LastCellNum; i++) { var headerText = headerRow.GetCell(i)?.ToText(); if (!string.IsNullOrWhiteSpace(headerText)) { headers.Add(i, headerText); } } //validate headers if (validColumns.Any(validColumn => !headers.Any(o => o.Value.EqualsIgnoreCase(validColumn)))) { var missingFields = validColumns.Where(x => !headers.Select(y => y.Value).Contains(x, StringComparer.OrdinalIgnoreCase)); result.Errors.Add($"Missing mandatory fields: {string.Join(", ", missingFields) }"); return(result); } #region Preapare TVP var dtRowTVP = new DataTable("TVP_Row"); dtRowTVP.Columns.Add("RowNr", typeof(int)); dtRowTVP.Columns.Add("MaterialKey", typeof(string)); dtRowTVP.Columns.Add("GID"); dtRowTVP.Columns.Add("EntryCode"); var dtColumnsTVP = new DataTable("TVP_Column"); dtColumnsTVP.Columns.Add("ColumnNr", typeof(int)); dtColumnsTVP.Columns.Add("TraitID", typeof(int)); dtColumnsTVP.Columns.Add("ColumnLabel", typeof(string)); dtColumnsTVP.Columns.Add("DataType", typeof(string)); var dtCellTVP = new DataTable("TVP_Cell"); dtCellTVP.Columns.Add("RowID", typeof(int)); dtCellTVP.Columns.Add("ColumnID", typeof(int)); dtCellTVP.Columns.Add("Value", typeof(string)); #endregion foreach (var header in headers.OrderBy(x => x.Key)) { //ignore country column since it is stored in test table. so we are not storing it in column and cell table. var columnName = ""; switch (header.Value.ToLower()) { case "numerical id": columnName = "GID"; break; case "sample name": columnName = "Plant Name"; break; default: columnName = header.Value; break; } if (header.Value.EqualsIgnoreCase("Country")) { continue; } dtColumnsTVP.Rows.Add(header.Key, null, columnName, "NVARCHAR(255)"); } var columns = headers.ToList(); var totalCols = headerRow.LastCellNum; bool breakLoop = false; for (var i = 1; i <= sheet.LastRowNum; i++) { if (breakLoop) { break; } var row = sheet.GetRow(i); if (row != null) { var rowNr = i - 1; var drRow = dtRowTVP.NewRow(); drRow["RowNr"] = rowNr; for (var j = 0; j < columns.Count; j++) { var column = columns[j]; var cell = row.GetCell(column.Key); if (cell != null) { object cValue = string.Empty; switch (cell.CellType) { case (CellType.Unknown | CellType.Formula | CellType.Blank): cValue = cell.ToString(); break; case CellType.Numeric: cValue = cell.NumericCellValue; break; case CellType.String: cValue = cell.StringCellValue; break; case CellType.Boolean: cValue = cell.BooleanCellValue; break; case CellType.Error: cValue = cell.ErrorCellValue; break; default: cValue = string.Empty; break; } var cellValue = cValue.ToText(); if (column.Value.EqualsIgnoreCase("Numerical ID")) { //validate if material key is empty if (string.IsNullOrWhiteSpace(cellValue)) { breakLoop = true; break; } else { drRow["MaterialKey"] = cellValue; } } else if (column.Value.EqualsIgnoreCase("Sample name")) { //validate if plant name is empty if (string.IsNullOrWhiteSpace(cellValue)) { breakLoop = true; break; } } else if (column.Value.EqualsIgnoreCase("Country")) { if (countryRequired) { //validate if country is empty if (string.IsNullOrWhiteSpace(cellValue)) { breakLoop = true; break; } } //only one country code is enough since it will be passed as single data in sp if (string.IsNullOrWhiteSpace(requestArgs.CountryCode)) { requestArgs.CountryCode = cellValue; } } //we don't need to send country information in cell. We already send it for test. if (!column.Value.EqualsIgnoreCase("Country") && !string.IsNullOrWhiteSpace(cellValue)) { //get value for celltvp var drCell = dtCellTVP.NewRow(); drCell["RowID"] = rowNr; drCell["ColumnID"] = j; drCell["Value"] = cellValue; dtCellTVP.Rows.Add(drCell); } } } if (drRow["MaterialKey"] == null || string.IsNullOrWhiteSpace(drRow["MaterialKey"].ToString())) { breakLoop = true; } if (!breakLoop) { dtRowTVP.Rows.Add(drRow); } } } await _externalTestRepository.ImportDataAsync(requestArgs, dtColumnsTVP, dtRowTVP, dtCellTVP); return(result); }