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