public async Task <ExcelDataResult> ExportExcelDataToDBAsync(ImportDataRequestArgs requestArgs)
{
var tcs = new TaskCompletionSource <ExcelDataResult>();
try
{
var result = new ExcelDataResult();
var xssfwb = new XSSFWorkbook(requestArgs.DataStream);
var sheet = xssfwb.GetSheetAt(0);
var screeningNr = sheet.GetRow(0);
var headerText = sheet.GetRow(1);
var stylesTable = xssfwb.GetStylesSource();
/*var cell = sheet.GetRow(1).GetCell(4);
* var stylesTable = xssfwb.GetStylesSource();
* var name = stylesTable.GetCellStyleName(cell);
*/
if (screeningNr == null || headerText == null)
{
result.Errors.Add("Invalid Excel format.");
result.Success = false;
return(result);
}
var cropRow = sheet.GetRow(3);
var crop = string.Empty;
var syncCode = string.Empty;
var breedingStation = string.Empty;
if (cropRow == null)
{
result.Success = false;
result.Errors.Add("No Crop Record Found.");
return(result);
}
for (int i = 0; i < headerText.Cells.Count; i++)
{
if (headerText.GetCell(i).ToText().ToLower() == "crop" ||
headerText.GetCell(i).ToText().ToLower() == "cropcode")
{
crop = cropRow.GetCell(i)?.ToString();
}
if (headerText.GetCell(i).ToText().ToLower().Trim() == "brstation")
{
breedingStation = cropRow.GetCell(i)?.ToString();
}
if (!string.IsNullOrWhiteSpace(crop) && !string.IsNullOrWhiteSpace(breedingStation))
{
break;
}
}
if (string.IsNullOrWhiteSpace(crop))
{
result.Success = false;
result.Errors.Add("No Crop Record Found.");
return(result);
}
if (string.IsNullOrWhiteSpace(breedingStation))
{
result.Success = false;
result.Errors.Add("No breeding station found.");
return(result);
}
var dtRowTVP = new DataTable("TVP_Row");
var dtColumnsTVP = new DataTable("TVP_Column");
var dtCellTVP = new DataTable("TVP_Cell");
var dtListTVP = new DataTable("TVP_List");
//prepare list tvp which is only needed for importing data from phenome
dtListTVP.Columns.Add("RowID");
dtListTVP.Columns.Add("GID");
dtListTVP.Columns.Add("EntryCode");
//prepare columns
PrepareColumnTableTVP(dtColumnsTVP, headerText, stylesTable, screeningNr);
var coldata = dtColumnsTVP.AsEnumerable().Select(x => new
{
ColName = x.IsNull("TraitID") ? x["ColumnLabel"] : x["TraitID"]
}).AsEnumerable().Select(x => x.ColName).GroupBy(g => g).Select(x => new
{
ColVal = x.Key,
Count = x.Count()
}).Where(x => x.Count > 1);
if (coldata.Any())
{
var keylist = coldata.Select(x => x.ColVal.ToText()).ToList();
var key = keylist.Truncate();
result.Errors.Add($"Duplicate Column {key}");
}
result.Success = !result.Errors.Any();
if (!result.Success)
{
return(result);
}
//this validation is removed as per client request, and query is adjusted on returning multiple traitname for same determination ID.
//var columnvalidations = await ValidateColumnsForUniqueDeterminations(dtColumnsTVP, crop, requestArgs.Source);
//if (columnvalidations.Tables[0].Rows.Count > 0)
//{
// result.Success = false;
// foreach (DataRow _value in columnvalidations.Tables[0].Rows)
// {
// result.Errors.Add("Multiple determination applied for Trait " + _value["Traits"]);
// }
// return result;
//}
var lastRow = sheet.LastRowNum + 1; //this is because RowNum starts from 0 not from 1.
if (lastRow - 2 > ConfigurationManager.AppSettings["App:MaxNoOfRecords"].ToInt64()) //first two rows data are meta data and should be ignored while importing it so value 2 is subtracted.
{
result.Errors.Add("Cannot import excel file having rows greater than " + ConfigurationManager.AppSettings["App:MaxNoOfRecords"]);
result.Success = false;
return(result);
}
//Prepare rows and cell values
PrepareRowAndCellTVP(dtRowTVP, dtCellTVP, headerText, stylesTable, sheet, result);
var data = dtRowTVP.AsEnumerable().Select(x => x.Field <string>("MaterialKey"))
.GroupBy(g => g)
.Select(
x => new
{
MaterialKey = x.Key,
Count = x.Count()
});
if (data.Any(x => x.MaterialKey.ToText() == string.Empty))
{
result.Errors.Add("Material Key cannot be null or empty");
}
var material = data.FirstOrDefault();
if (material != null)
{
syncCode = material.MaterialKey?.Substring(0, 2);
}
if (string.IsNullOrWhiteSpace(syncCode))
{
result.Errors.Add("SyncCode cannot be null or empty");
}
var keys = data.Where(x => x.Count > 1);
if (keys.Any())
{
var keylist = keys.Select(x => x.MaterialKey).ToList();
var key = keylist.Truncate();
result.Errors.Add($"Duplicate Material key {key}");
}
result.Success = !result.Errors.Any();
if (result.Success)
{
await ImportDataAsync(crop, breedingStation, syncCode, string.Empty, requestArgs, dtColumnsTVP, dtRowTVP, dtCellTVP, dtListTVP);
tcs.SetResult(await GetDataAsync(requestArgs));
}
else
{
tcs.SetResult(result);
}
}
catch (Exception ex)
{
tcs.SetException(ex);
}
return(await tcs.Task);
}
public async Task <ExcelDataResult> ExportExcelDataToDBAsync(ImportDataRequestArgs args)
{
return(await repository.ExportExcelDataToDBAsync(args));
}
public async Task ImportDataAsync(string crop, string breedingStation, string syncCode, string countryCode, ImportDataRequestArgs requestArgs,
DataTable dtColumnsTVP, DataTable dtRowTVP, DataTable dtCellTVP, DataTable dtListTVP)
{
var p1 = DbContext.CreateOutputParameter("@TestID", DbType.Int32);
DbContext.CommandTimeout = 5 * 60; //5 minutes
await DbContext.ExecuteNonQueryAsync(DataConstants.PR_INSERT_EXCEL_DATA,
CommandType.StoredProcedure, args1 =>
{
args1.Add("@CropCode", crop);
args1.Add("@BreedingStationCode", breedingStation);
args1.Add("@SyncCode", syncCode);
args1.Add("@CountryCode", countryCode);
args1.Add("@TestTypeID", requestArgs.TestTypeID);
args1.Add("@UserID", userContext.GetContext().FullName);
args1.Add("@FileTitle", requestArgs.FilePath);
args1.Add("@TVPColumns", dtColumnsTVP);
args1.Add("@TVPRow", dtRowTVP);
args1.Add("@TVPCell", dtCellTVP);
args1.Add("@TestID", p1);
args1.Add("@PlannedDate", requestArgs.PlannedDate);
args1.Add("@MaterialStateID", requestArgs.MaterialStateID);
args1.Add("@MaterialTypeID", requestArgs.MaterialTypeID);
args1.Add("@ContainerTypeID", requestArgs.ContainerTypeID);
args1.Add("@Isolated", requestArgs.Isolated);
args1.Add("@Source", requestArgs.Source);
args1.Add("@TestName", requestArgs.TestName);
args1.Add("@ObjectID", requestArgs.ObjectID);
args1.Add("@ExpectedDate", requestArgs.ExpectedDate);
args1.Add("@Cumulate", requestArgs.Cumulate);
args1.Add("@ImportLevel", requestArgs.ImportLevel);
args1.Add("@TVPList", dtListTVP);
args1.Add("@ExcludeControlPosition", requestArgs.ExcludeControlPosition);
args1.Add("@SiteID", requestArgs.SiteID);
args1.Add("@FileID", requestArgs.FileID);
args1.Add("@BTR", requestArgs.BTR);
args1.Add("@ResearcherName", requestArgs.ResearcherName);
});
requestArgs.TestID = p1.Value.ToInt32();
}
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 stream = await file.ReadAsStreamAsync();
//save excel data to database
var args = new ImportDataRequestArgs
{
TestTypeID = provider.FormData["testTypeID"].ToInt32(),
PageNumber = provider.FormData["pageNumber"].ToInt32(),
PageSize = provider.FormData["pageSize"].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"],
FilePath = fileName,
TestName = System.IO.Path.GetFileNameWithoutExtension(fileName),
DataStream = stream,
ExpectedDate = provider.FormData["expectedDate"].ToNDateTime(),
Cumulate = provider.FormData["cumulate"].ToBoolean(),
ExcludeControlPosition = provider.FormData["excludeControlPosition"].ToBoolean()
};
var data = await excelDataService.ExportExcelDataToDBAsync(args);
//get recently uploaded file details
var fileInfo = await fileService.GetFileAsync(args.TestID);
var result = new
{
data.Success,
data.Errors,
data.Total,
data.DataResult,
args.TestID,
File = fileInfo
};
return(Ok(result));
}
