public async Task ImportDataAsync(ExternalTestImportRequestArgs requestArgs, DataTable dtColumnsTVP, DataTable dtRowTVP, DataTable dtCellTVP) { var p1 = DbContext.CreateOutputParameter("@TestID", DbType.Int32); DbContext.CommandTimeout = 5 * 60; //5 minutes var dtListTVP = new DataTable("TVP_List"); await DbContext.ExecuteNonQueryAsync(DataConstants.PR_IMPORT_EXTERNAL_DATA, CommandType.StoredProcedure, args => { args.Add("@TestID", p1); args.Add("@CropCode", requestArgs.CropCode); args.Add("@BreedingStationCode", requestArgs.BrStationCode); args.Add("@SyncCode", "NL"); args.Add("@CountryCode", requestArgs.CountryCode); args.Add("@TestTypeID", requestArgs.TestTypeID); args.Add("@UserID", userContext.GetContext().FullName); args.Add("@FileTitle", requestArgs.TestName); args.Add("@TestName", requestArgs.TestName); args.Add("@PlannedDate", requestArgs.PlannedDate); args.Add("@MaterialStateID", requestArgs.MaterialStateID); args.Add("@MaterialTypeID", requestArgs.MaterialTypeID); args.Add("@ContainerTypeID", requestArgs.ContainerTypeID); args.Add("@Isolated", requestArgs.Isolated); args.Add("@ExpectedDate", requestArgs.ExpectedDate); args.Add("@Source", requestArgs.Source); args.Add("@ObjectID", null); args.Add("@TVPColumns", dtColumnsTVP); args.Add("@TVPRow", dtRowTVP); args.Add("@TVPCell", dtCellTVP); args.Add("@Cumulate", false); args.Add("@ImportLevel", "PLT"); args.Add("@ExcludeControlPosition", requestArgs.ExcludeControlPosition); args.Add("@BTR", requestArgs.BTR); args.Add("@ResearcherName", requestArgs.ResearcherName); }); requestArgs.TestID = p1.Value.ToInt32(); }
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); }
public async Task <IHttpActionResult> ImportExcel() { if (!Request.Content.IsMimeMultipartContent()) { return(InvalidRequest("Please upload file with enctype='multipart/form-data'.")); } var provider = await Request.Content.ReadAsMultipartAsync(new MultipartFormDataMemoryStreamProvider()); //get file from content var file = provider.Contents.Where((content, idx) => provider.IsStream(idx)).FirstOrDefault(); if (file == null) { return(InvalidRequest("File is either corrupted or invalid.")); } var fileName = file.Headers.ContentDisposition.FileName.Trim('\"'); //this check should be done while creating file on DB because now file must be unique based on CropCode and breeding station code but not per user. //check if file is already exists for user //var fileExists = await fileService.FileExistsAsync(fileName); //if (fileExists) //{ // return InvalidRequest("File already exists."); //} var fs = await file.ReadAsStreamAsync(); //save excel data to database var args = new ExternalTestImportRequestArgs { CropCode = provider.FormData["cropCode"], BrStationCode = provider.FormData["brStationCode"], TestTypeID = provider.FormData["testTypeID"].ToInt32(), PlannedDate = provider.FormData["plannedDate"].ToNDateTime(), MaterialStateID = provider.FormData["materialStateID"].ToInt32(), MaterialTypeID = provider.FormData["materialTypeID"].ToInt32(), ContainerTypeID = provider.FormData["containerTypeID"].ToInt32(), Isolated = provider.FormData["isolated"].ToBoolean(), Source = provider.FormData["source"], BTR = provider.FormData["btr"].ToBoolean(), ResearcherName = provider.FormData["researcherName"], TestName = System.IO.Path.GetFileNameWithoutExtension(fileName), ExpectedDate = provider.FormData["expectedDate"].ToNDateTime(), PageSize = provider.FormData["pageSize"].ToInt32(), ExcludeControlPosition = provider.FormData["excludeControlPosition"].ToBoolean(), DataStream = fs }; if (args.BTR && string.IsNullOrWhiteSpace(args.ResearcherName)) { return(InvalidRequest("Please fill Researcher name for BTR type.")); } var result = await _externalTestService.ImportDataAsync(args); if (!result.Success) { return(Ok(result)); } //get imported data separately var data = await _excelDataService.GetDataAsync(new ExcelDataRequestArgs { TestID = args.TestID, TestTypeID = args.TestTypeID, PageNumber = 1, PageSize = args.PageSize }); //get recently uploaded file details var fileInfo = await _fileService.GetFileAsync(args.TestID); var resp = new { result.Success, args.TestID, data.DataResult, data.Total, data.TotalCount, File = fileInfo }; return(Ok(resp)); }