public IEnumerable <Project> Get()
{
string userName = HttpContext.Current.User.Identity.Name;
// Using LINQ to SQL
using (var fpContext = new OCPSQLEntities())
{
var user = fpContext.SYSTEM_USER
.Where(u => u.USER_NAME == userName)
.SingleOrDefault();
if (user == null)
{
user = new SYSTEM_USER {
USER_NAME = userName
};
fpContext.SYSTEM_USER.Add(user);
fpContext.SaveChanges();
}
var userId = user.USER_ID;
// Retrieve project list from database
var existProjects = fpContext.FPTOOLS_PROJECT
.Where(i => i.USER_ID == userId)
.Select(p => new Project
{
Id = p.PROJECT_ID,
ProjectName = p.PROJECT_NAME,
FileName = p.FILE_NAME,
PlotNumbers = p.IPORTAL_FP.Count(k => k.PROJECT_ID == p.PROJECT_ID)
}).ToList();
return(existProjects);
}
}
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 DataTable Get(int fileId)
{
var filepath = "";
using (var db = new OCPSQLEntities())
{
filepath = db.IPORTAL_FILE.Find(fileId).SERVER_PATH;
}
return(GetFile(filepath));
}
public Project Get(int projectId)
{
string userName = HttpContext.Current.User.Identity.Name;
// Using LINQ to SQL, work with the database context, Retrieve project list from database
using (var fpContext = new OCPSQLEntities())
{
var userId = fpContext.SYSTEM_USER
.Where(u => u.USER_NAME == HttpContext.Current.User.Identity.Name)
.SingleOrDefault().USER_ID; // TODO : error checking for null user
// Retrieve project list from database
var selectedProject = fpContext.FPTOOLS_PROJECT
.Where(i => i.USER_ID == userId && i.PROJECT_ID == projectId)
.Select(p => new Project
{
Id = p.PROJECT_ID,
ProjectName = p.PROJECT_NAME,
FileName = p.FILE_NAME,
PlotNumbers = p.IPORTAL_FP.Count(k => k.PROJECT_ID == projectId),
Plots = p.IPORTAL_FP.Where(i => i.PROJECT_ID == projectId)
.ToList().Select(t => new Plot()
{
Id = t.FP_ID,
Settings = new PlotSettings
{
DrugName = t.DRUGNAME,
Title = t.TITLE,
FootNote = t.FOOTNOTE,
Xlabel = t.XLABEL,
RangeBottom = t.RANGE_BOTTOM,
RangeTop = t.RANGE_TOP,
RangeStep = (double)t.RANGE_STEP,
Style = t.FP_STYLE_ID,
Scale = t.SCALE_ID
},
Rows = t.IPORTAL_FP_ROW.Where(r => r.FP_ID == t.FP_ID)
.ToList().Select(w => new PlotData()
{
Category = w.CATEGORY,
SubCategory = w.SUBCATEGORY,
Parameter = w.PARAMETER,
Comment = w.COMMENT,
Ratio = w.RATIO, // (double?)w.RATIO.Value ?? (double?) null,
Lower_CI = w.LOWER_CI, //(double?)w.LOWER_CI.Value ?? (double?)null,
Upper_CI = w.UPPER_CI, //(double?)w.UPPER_CI.Value ?? (double?)null,
}).ToList()
}).ToList()
}).FirstOrDefault();
return(selectedProject);
}
}
public HttpResponseMessage Delete(int id)
// public string DELETE (int projectId)
{
//try
//{
//using (var context = new PrincipalContext(ContextType.Domain))
//{
// var principal = UserPrincipal.FindByIdentity(context, User.Identity.Name);
//}
// string userName = HttpContext.Current.User.Identity.Name;
string projectName = "projectName";
// Using LINQ to SQL, work with the database context, Retrieve project list from database
using (var fpContext = new OCPSQLEntities())
{
var userId = fpContext.SYSTEM_USER
.Where(u => u.USER_NAME == HttpContext.Current.User.Identity.Name)
.SingleOrDefault().USER_ID; // TODO : error checking for null user
// Retrieve project list from database
var selectedProject = (from p in fpContext.FPTOOLS_PROJECT
//Retrieve User Id & Project Id from database
where (p.PROJECT_ID == id && p.USER_ID == userId)
//Temp Test Run Demo: TODO : fix Anonymous user
//where (p.PROJECT_ID == id && p.USER_ID == 4)
select p).FirstOrDefault();
projectName = selectedProject.PROJECT_NAME;
fpContext.FPTOOLS_PROJECT.Attach(selectedProject);
fpContext.FPTOOLS_PROJECT.Remove(selectedProject);
fpContext.SaveChanges();
}
// return projectName;
// return new HttpResponseMessage(HttpStatusCode.NoContent);
return(Request.CreateResponse(HttpStatusCode.OK, projectName));
//}
//catch (System.Exception e)
//{
// return Request.CreateErrorResponse(HttpStatusCode.InternalServerError, e);
//}
}
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));
}
public string Run(string jsonPlot)
{
JavaScriptSerializer jss = new JavaScriptSerializer();
Plot revisedPlot = jss.Deserialize <Plot>(jsonPlot);
// Use the iportal database context
using (OCPSQLEntities db = new OCPSQLEntities())
{
// For each project, update the plot data in the database
//FPTOOLS_PROJECT project = new FPTOOLS_PROJECT();
var plot = new IPORTAL_FP();
// retrieve plot data from the database
plot = db.IPORTAL_FP.SingleOrDefault(p => p.FP_ID == revisedPlot.Id);
// if the plot does not exist return error TODO improve
if (plot == null)
{
throw new Exception();
}
// Update plot setting data into database
plot.DRUGNAME = revisedPlot.Settings.DrugName;
plot.TITLE = revisedPlot.Settings.Title;
plot.RANGE_BOTTOM = (double)revisedPlot.Settings.RangeBottom;
plot.RANGE_TOP = (double)revisedPlot.Settings.RangeTop;
plot.RANGE_STEP = (double)revisedPlot.Settings.RangeStep;
plot.XLABEL = revisedPlot.Settings.Xlabel;
plot.FOOTNOTE = revisedPlot.Settings.FootNote;
plot.SCALE_ID = revisedPlot.Settings.Scale;
plot.FP_STYLE_ID = revisedPlot.Settings.Style;
db.SaveChanges();
}
// Run the analysis code
var filename = "Forest_plot_" + revisedPlot.Id;
try
{
SasClientObject.RunJob("GenForestPlot",
(new { IdName = "FP_ID", IdVal = revisedPlot.Id, PlotId = "Forest_plot" }));
// new SasGenericClient.RunOptions {
// AbortExisting = true, RunId = "GenForestPlot" + revisedPlot.Id
// });
}
catch (Exception ex)
{
return("");
}
// Download the plot as a base64 encoded string
try
{
var result = new HttpResponseMessage(HttpStatusCode.OK);
var stream = new FileStream(ForestPlotConfig.OutputFolder + filename + ".png", FileMode.Open);
byte[] filebytes = new byte[stream.Length];
stream.Read(filebytes, 0, Convert.ToInt32(stream.Length));
var encodedImage = Convert.ToBase64String(filebytes);
return("data:image/png;base64," + encodedImage);
}
catch (Exception ex)
{
return("");
}
}
