public HttpResponseMessage Post([FromBody] string projectName)
{
int?projectId = null;
// Using LINQ to SQL to insert excel data into database
using (var fpContext = new OCPSQLEntities())
{
// string query = "Select [CATEGORY], [SUBCATEGORY], [PARAMETER], [RATIO], [LOWER_CI], [UPPER_CI], [COMMENT] from [Sheet1$]";
if (fpContext.SYSTEM_USER.Any(u => u.USER_NAME == User.Identity.Name))
{
var user = fpContext.SYSTEM_USER.SingleOrDefault(u => u.USER_NAME == User.Identity.Name);
// Insert new project data into database
var newProject = new FPTOOLS_PROJECT()
{
USER_ID = user.USER_ID,
FILE_NAME = "",
PROJECT_NAME = projectName ?? "",
};
// Initialize new Plot data into database
var newPlot = new IPORTAL_FP
{
TITLE = "",
SCALE_ID = 1,
FOOTNOTE = "",
XLABEL = "",
FP_STYLE_ID = 1,
RANGE_BOTTOM = 10,
RANGE_TOP = 30,
RANGE_STEP = 3,
};
IPORTAL_FP_ROW newPlotData = new IPORTAL_FP_ROW();
{
newPlotData.CATEGORY = "";
newPlotData.SUBCATEGORY = "";
newPlotData.PARAMETER = "";
newPlotData.RATIO = 0;
newPlotData.LOWER_CI = 1;
newPlotData.UPPER_CI = 10;
newPlotData.COMMENT = "New Plot comment";
newPlotData.FP_ID = newPlot.FP_ID;
newPlot.IPORTAL_FP_ROW.Add(newPlotData);
}
newProject.IPORTAL_FP.Add(newPlot);
fpContext.FPTOOLS_PROJECT.Add(newProject);
fpContext.SaveChanges();
// Retrieve the id of the submitted project
projectId = newProject.PROJECT_ID;
}
else // TODO: error check for no login user
{
throw new HttpResponseException(HttpStatusCode.InternalServerError);
};
}
return(Request.CreateResponse(HttpStatusCode.OK, projectId));
}
public async Task <HttpResponseMessage> Post()
{
int?projectId = null;
// check if the request contains multipart / form-data.
if (!Request.Content.IsMimeMultipartContent())
{
throw new HttpResponseException(HttpStatusCode.UnsupportedMediaType);
}
var provider = new InMemoryMultipartFormDataStreamProvider();
// Read the form data and return an async task.
await Request.Content.ReadAsMultipartAsync(provider);
// Throw exception if the project name is null
var projectName = provider.FormData["projectName"];
if (string.IsNullOrWhiteSpace(projectName) || projectName == "null")
{
throw new Exception();
}
// Read file name and stream
string filename = provider.Contents.FirstOrDefault().Headers.ContentDisposition.FileName.Trim('\"');
var fileStream = new MemoryStream();
provider.Contents.FirstOrDefault().ReadAsStreamAsync().Result.CopyTo(fileStream);
// Using LINQ to SQL to insert excel data into database
using (var fpContext = new OCPSQLEntities())
{
if (fpContext.SYSTEM_USER.Any(u => u.USER_NAME == User.Identity.Name))
{
var user = fpContext.SYSTEM_USER.SingleOrDefault(u => u.USER_NAME == User.Identity.Name);
// Insert new project data into database
var newProject = new FPTOOLS_PROJECT()
{
USER_ID = user.USER_ID,
FILE_NAME = filename,
PROJECT_NAME = projectName ?? "",
};
// Initialize new Plot data into database
var newPlot = new IPORTAL_FP
{
TITLE = "",
SCALE_ID = 1,
FOOTNOTE = "",
XLABEL = "",
FP_STYLE_ID = 1,
RANGE_BOTTOM = 0,
RANGE_TOP = 5,
RANGE_STEP = 0.1,
};
IExcelDataReader excelReader;
// Reading from a binary Excel file ('97-2003 format; *.xls)
if (filename.Split('.').Last().Equals("xls"))
{
excelReader = ExcelReaderFactory.CreateBinaryReader(fileStream);
}
else // Reading from a OpenXml Excel file (2007 format; *.xlsx)
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(fileStream);
}
excelReader.IsFirstRowAsColumnNames = true;
var excelDataset = excelReader.AsDataSet();
string category = null, subcategory = null;
foreach (DataRow row in excelDataset.Tables[0].Rows)
{
// regex to match an alphanumeric character
var re = new Regex(@"\w");
// Update category if present
var rowCategory = row.GetColumnValue(excelReaderExtension.ColumnTypes.CATEGORY);
if (rowCategory != null && re.IsMatch(rowCategory))
{
category = rowCategory;
}
// Update subcategory if present
var rowSubcategory = row.GetColumnValue(excelReaderExtension.ColumnTypes.SUBCATEGORY);
if (rowSubcategory != null && re.IsMatch(rowSubcategory))
{
subcategory = rowSubcategory;
}
// Determine if a parameter is present in the row
var rowParameter = row.GetColumnValue(excelReaderExtension.ColumnTypes.PARAMETER);
if (rowParameter != null && re.IsMatch(rowParameter))
{
// Determine if row values can be parsed as double
double ratio, lowerCI, higherCI;
if (double.TryParse(row.GetColumnValue(excelReaderExtension.ColumnTypes.RATIO), out ratio) &&
double.TryParse(row.GetColumnValue(excelReaderExtension.ColumnTypes.LOWERCI), out lowerCI) &&
double.TryParse(row.GetColumnValue(excelReaderExtension.ColumnTypes.UPPERCI), out higherCI))
{
// Create a new row entity
IPORTAL_FP_ROW FP_RowTable = new IPORTAL_FP_ROW();
FP_RowTable.CATEGORY = category;
FP_RowTable.SUBCATEGORY = subcategory;
FP_RowTable.PARAMETER = rowParameter;
FP_RowTable.RATIO = ratio;
FP_RowTable.LOWER_CI = lowerCI;
FP_RowTable.UPPER_CI = higherCI;
// Save comment only if it contains alphanumeric info
var comment = row.GetColumnValue(excelReaderExtension.ColumnTypes.COMMENT);
FP_RowTable.COMMENT = (comment != null && re.IsMatch(comment)) ? comment : "";
FP_RowTable.FP_ID = newPlot.FP_ID;
newPlot.IPORTAL_FP_ROW.Add(FP_RowTable);
}
}
}
newProject.IPORTAL_FP.Add(newPlot);
fpContext.FPTOOLS_PROJECT.Add(newProject);
fpContext.SaveChanges();
// Retrieve the id of the submitted project
projectId = newProject.PROJECT_ID;
}
else // TODO: error check for no login user
{
throw new HttpResponseException(HttpStatusCode.InternalServerError);
};
}
// return projectId
return(Request.CreateResponse(HttpStatusCode.OK, projectId));
}
