public async Task <IActionResult> Submit([FromBody] Models.MeasureSubmissionViewModel data)
{
if (User.Identity.IsAuthenticated == false)
{
return(BadRequest(new Models.ApiErrorResult("User must be authenticated to view measures.")));
}
if (data == null)
{
return(BadRequest(new Models.ApiErrorResult("Invalid measure data.")));
}
if (!User.Claims.Any(cl => cl.Type == Identity.Claims.SubmitMeasure_Key))
{
return(BadRequest(new Models.ApiErrorResult("The user does not have permission to submit measures.")));
}
List <string> errors = new List <string>();
if (!ValidateSubmission(data, errors))
{
return(BadRequest(new { errors }));
}
var user = await _modelDB.Users.FindAsync(Guid.Parse(User.Claims.Where(x => x.Type == ClaimTypes.NameIdentifier).Select(x => x.Value).FirstOrDefault()));
MeasurementMeta measure = new MeasurementMeta
{
CommonDataModel = data.CommonDataModel,
DatabaseSystem = data.DatabaseSystem,
DataSource = data.DataSource,
DataSourceID = data.DataSourceID,
DateRangeEnd = data.DateRangeEnd.Value,
DateRangeStart = data.DateRangeStart.Value,
MetricID = data.MetricID.Value,
Network = data.Network,
Organization = data.Organization,
OrganizationID = data.OrganizationID,
RunDate = data.RunDate.Value,
SubmittedByID = user.ID,
SubmittedOn = DateTime.UtcNow,
CommonDataModelVersion = data.CommonDataModelVersion,
ResultsDelimiter = data.ResultsDelimiter,
ResultsTypeID = await _modelDB.MetricResultTypes.Where(rt => rt.Metrics.Any(m => m.ID == data.MetricID.Value)).Select(rt => rt.ID).FirstOrDefaultAsync(),
SupportingResources = data.SupportingResources
};
measure.Measurements = new HashSet <Measurement>(data.Measures.Select(i => new Measurement {
MetadataID = measure.ID, RawValue = i.RawValue, Definition = string.IsNullOrEmpty(i.Definition) ? i.RawValue : i.Definition, Measure = i.Measure.HasValue ? i.Measure.Value : 0f, Total = i.Total
}).ToArray());
_modelDB.MeasurementMeta.Add(measure);
await _modelDB.SaveChangesAsync();
return(Ok());
}
public Models.MeasureSubmissionViewModel Convert(IList <string> errors)
{
var measure = new Models.MeasureSubmissionViewModel();
foreach (var row in GetMetadataRows())
{
var cells = row.Descendants <Cell>().ToArray();
if (cells.Length == 0)
{
continue;
}
//check if the first cell is in column A, if not skip
string cellReference = cells[0].CellReference.Value;
if (!cellReference.StartsWith("A", StringComparison.OrdinalIgnoreCase))
{
continue;
}
string metadataHeader = GetCellValue(cells[0]);
string value = string.Empty;
if (cells.Length > 1)
{
cellReference = cells[1].CellReference.Value;
if (cellReference.StartsWith("B", StringComparison.OrdinalIgnoreCase))
{
value = GetCellValue(cells[1]);
}
}
SetMeasureValue(measure, metadataHeader, value);
}
var measurementRows = GetValuesRows().ToArray();
if (measurementRows.Length > 1)
{
var measurements = new List <Models.MeasurementSubmissionViewModel>();
//going to assume for a valid document that the first four cells of the first row will always have a value
//assume that the cell reference for each header will be A1-D1.
var headers = measurementRows.First().Descendants <Cell>().Take(4).Select(c => GetCellValue(c)).ToArray();
foreach (var row in measurementRows.Skip(1))
{
var cells = row.Descendants <Cell>().ToArray();
var measurement = new Models.MeasurementSubmissionViewModel();
foreach (var cell in cells)
{
switch (cell.CellReference.Value[0].ToStringEx().ToUpper())
{
case "A":
SetMeasurementValue(measurement, headers[0], GetCellValue(cell));
break;
case "B":
SetMeasurementValue(measurement, headers[1], GetCellValue(cell));
break;
case "C":
SetMeasurementValue(measurement, headers[2], GetCellValue(cell));
break;
case "D":
SetMeasurementValue(measurement, headers[3], GetCellValue(cell));
break;
}
}
if (measurement.IsNotNull())
{
measurements.Add(measurement);
}
}
measure.Measures = measurements;
}
return(measure);
}
void SetMeasureValue(Models.MeasureSubmissionViewModel measure, string property, object value)
{
if (value == null)
{
return;
}
Guid id;
switch (property.TrimEnd(' ', '*').ToLower())
{
case "metricid":
if (Guid.TryParse(value.ToStringEx(), out id))
{
measure.MetricID = id;
}
break;
case "organizationid":
if (Guid.TryParse(value.ToStringEx(), out id))
{
measure.OrganizationID = id;
}
break;
case "organization":
measure.Organization = value.ToStringEx();
break;
case "datasourceid":
if (Guid.TryParse(value.ToStringEx(), out id))
{
measure.DataSourceID = id;
}
break;
case "datasource":
measure.DataSource = value.ToStringEx();
break;
case "rundate":
measure.RunDate = ConvertToDate(value);
break;
case "network":
measure.Network = value.ToStringEx();
break;
case "resultstype":
case "datatype":
case "results type":
case "data type":
measure.ResultsType = value.ToStringEx();
break;
}
if (property.StartsWith("common", StringComparison.OrdinalIgnoreCase))
{
if (property.EndsWith("version", StringComparison.OrdinalIgnoreCase))
{
measure.CommonDataModelVersion = value.ToStringEx();
}
else
{
measure.CommonDataModel = value.ToStringEx();
}
}
if (property.Contains("Results Delimiter", StringComparison.OrdinalIgnoreCase))
{
measure.ResultsDelimiter = value.ToStringEx();
}
if (property.StartsWith("database", StringComparison.OrdinalIgnoreCase))
{
measure.DatabaseSystem = value.ToStringEx();
}
if (property.Contains("range start", StringComparison.OrdinalIgnoreCase))
{
measure.DateRangeStart = ConvertToDate(value);
}
if (property.Contains("range end", StringComparison.OrdinalIgnoreCase))
{
measure.DateRangeEnd = ConvertToDate(value);
}
if (property.Contains("supporting resources", StringComparison.OrdinalIgnoreCase))
{
measure.SupportingResources = value.ToStringEx();
}
}
bool ValidateSubmission(Models.MeasureSubmissionViewModel measure, IList <string> errors)
{
if (!measure.MetricID.HasValue)
{
errors.Add("Unable to determine MetricID.");
return(false);
}
else
{
if (!_modelDB.Metrics.Any(m => m.ID == measure.MetricID.Value))
{
errors.Add("Invalid MetricID, metric not found.");
return(false);
}
}
if (string.IsNullOrEmpty(measure.Organization))
{
errors.Add("Missing the Organization name.");
}
if (string.IsNullOrEmpty(measure.DataSource))
{
errors.Add("Missing the DataSource name.");
}
if (!measure.RunDate.HasValue)
{
errors.Add("Missing the Run Date.");
}
if (!measure.DateRangeStart.HasValue)
{
errors.Add("Missing the Date Range Start value.");
}
if (!measure.DateRangeStart.HasValue)
{
errors.Add("Missing the Date Range End value.");
}
if (measure.DateRangeStart.HasValue && measure.DateRangeEnd.HasValue && measure.DateRangeStart.Value > measure.DateRangeEnd.Value)
{
errors.Add("The Date Range Start value should occure before the Date Range End value.");
}
if (string.IsNullOrEmpty(measure.ResultsType))
{
errors.Add("Missing the Results Type value.");
}
else
{
var validResultType = _modelDB.MetricResultTypes.Where(rt => rt.Metrics.Any(m => m.ID == measure.MetricID.Value)).Select(rt => rt.Value).FirstOrDefault();
if (string.IsNullOrEmpty(validResultType))
{
errors.Add("Unable to determine the Results Type for the specified metric.");
}
else if (!validResultType.Equals(measure.ResultsType, StringComparison.OrdinalIgnoreCase))
{
errors.Add("Invalid Results Type value. The expected value of '" + validResultType + "' was expected for the specified Metric.");
}
}
if (measure.Measures == null || measure.Measures.Any() == false)
{
errors.Add("At least one measurement is required.");
return(false);
}
if (measure.Measures.Any(m => string.IsNullOrEmpty(m.RawValue)))
{
errors.Add("All measurements require a value for Raw Value.");
}
if (measure.Measures.Any(m => m.Measure.HasValue == false))
{
errors.Add("All measurements require a value for Measure.");
}
return(errors.Count == 0);
}
public async Task <IActionResult> DownloadMeasureTemplate(Guid metricID, string format)
{
var metric = await _db.Metrics.Where(m => m.ID == metricID).Select(m => new { m.Title, ResultsType = m.ResultsType.Value }).FirstOrDefaultAsync();
if (metric == null)
{
return(NotFound());
}
if (string.Equals("json", format, StringComparison.OrdinalIgnoreCase))
{
var measure = new Models.MeasureSubmissionViewModel {
MetricID = metricID,
ResultsType = metric.ResultsType,
Measures = new HashSet <Models.MeasurementSubmissionViewModel> {
new Models.MeasurementSubmissionViewModel {
Definition = string.Empty, RawValue = string.Empty, Measure = 0, Total = 0
}
}
};
var serializerSettings = new Newtonsoft.Json.JsonSerializerSettings {
Formatting = Formatting.Indented, DateFormatString = "'yyyy-MM-dd'"
};
var result = new FileContentResult(System.Text.Encoding.UTF8.GetBytes(JsonConvert.SerializeObject(measure, serializerSettings)), Microsoft.Net.Http.Headers.MediaTypeHeaderValue.Parse("application/json"))
{
FileDownloadName = CleanFilename("MeasureSubmission_" + metric.Title) + ".json"
};
return(result);
}
else if (string.Equals("xlsx", format, StringComparison.OrdinalIgnoreCase))
{
using (var scope = _serviceProvider.CreateScope())
{
var hostingEnvironment = scope.ServiceProvider.GetService <IWebHostEnvironment>();
System.IO.MemoryStream ms = new System.IO.MemoryStream();
using (var fs = new System.IO.FileStream(System.IO.Path.Combine(hostingEnvironment.WebRootPath, "assets", "MeasureSubmission_template.xlsx"), System.IO.FileMode.Open, System.IO.FileAccess.Read))
{
await fs.CopyToAsync(ms);
await fs.FlushAsync();
}
ms.Position = 0;
using (var document = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(ms, true))
{
var helper = new Utils.MeasuresExcelReader(document);
helper.UpdateMetricInformation(metricID, metric.ResultsType);
helper.Document.Save();
document.Close();
}
ms.Position = 0;
var result = new FileStreamResult(ms, Microsoft.Net.Http.Headers.MediaTypeHeaderValue.Parse("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"))
{
FileDownloadName = CleanFilename("MeasureSubmission_" + metric.Title + ".xlsx")
};
return(result);
}
}
return(BadRequest("Invalid document format, must be either 'json' or 'xlsx'."));
}
public async Task UploadJsonMeasure()
{
var app = new ApplicationFactory();
using (var scope = app.CreateScope())
using (var db = scope.ServiceProvider.GetRequiredService <Model.ModelDataContext>())
{
//get the first count metric
var metric = db.Metrics.Include(m => m.ResultsType).Where(m => m.ResultsType.Value == "Count" && m.Statuses.OrderByDescending(s => s.CreateOn).First().MetricStatusID == Model.MetricStatus.PublishedID).FirstOrDefault();
if (metric == null)
{
Assert.Fail("There is no published metric to create measures for.");
}
var rnd = new Random();
var measureMeta = new Models.MeasureSubmissionViewModel {
MetricID = metric.ID,
ResultsType = metric.ResultsType.Value,
DataSource = "Unit Test DataSource",
Organization = "Unit Test Organization",
Network = "Developerland",
RunDate = DateTime.Now.Date,
DateRangeStart = DateTime.Now.AddYears(Convert.ToInt32(-100 * rnd.NextDouble())).Date,
DateRangeEnd = DateTime.Now.AddMonths(Convert.ToInt32(-12 * rnd.NextDouble())).Date,
SupportingResources = "https://github.com/some-organization/repositoryurl"
};
List <Models.MeasurementSubmissionViewModel> measurements = new List <Models.MeasurementSubmissionViewModel>();
int totalRows = Convert.ToInt32(20 * rnd.NextDouble()) + 1;
for (int i = 0; i < totalRows; i++)
{
measurements.Add(new Models.MeasurementSubmissionViewModel {
RawValue = i.ToString(), Definition = i.ToString(), Measure = Convert.ToSingle(100 * rnd.NextDouble())
});
}
float sum = measurements.Sum(m => m.Measure.Value);
foreach (var measure in measurements)
{
measure.Total = sum;
}
measureMeta.Measures = measurements;
using (var ms = new System.IO.MemoryStream())
{
using (var sw = new System.IO.StreamWriter(ms, Encoding.Default, 1024, true))
using (var jw = new Newtonsoft.Json.JsonTextWriter(sw))
{
var serializer = new Newtonsoft.Json.JsonSerializer();
serializer.DateFormatString = "yyyy'-'MM'-'dd";
serializer.Formatting = Newtonsoft.Json.Formatting.None;
serializer.Serialize(jw, measureMeta);
await jw.FlushAsync();
}
ms.Seek(0, SeekOrigin.Begin);
using (var http = new System.Net.Http.HttpClient())
{
http.DefaultRequestHeaders.Accept.Add(System.Net.Http.Headers.MediaTypeWithQualityHeaderValue.Parse("application/json"));
http.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("Basic", Convert.ToBase64String(Encoding.Default.GetBytes("dqm1:Password1!")));
try
{
var content = new System.Net.Http.StreamContent(ms);
content.Headers.Add("Content-Type", "application/json");
var result = await http.PostAsync("https://localhost:44317/api/measures/submit", content);
if (result.IsSuccessStatusCode)
{
var responseContent = await result.Content.ReadAsStringAsync();
}
else
{
string error = await result.Content.ReadAsStringAsync();
Assert.Fail(error);
}
}
catch (System.Net.WebException webex)
{
using (var reader = new StreamReader(webex.Response.GetResponseStream()))
{
Assert.Fail(await reader.ReadToEndAsync());
}
}
}
}
}
}