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));
}
