public void ConfirmSampleFiles()
{
string sampleFolder = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"..\..\..\Measures\HPHCI");
string[] files = Directory.GetFiles(sampleFolder);
foreach (var file in files)
{
Console.WriteLine("Reading file: " + file);
using (SpreadsheetDocument document = SpreadsheetDocument.Open(file, false))
{
var reader = new ASPE.DQM.Utils.MeasuresExcelReader(document);
foreach (var row in reader.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 = reader.GetCellValue(cells[0]);
string value = string.Empty;
if (cells.Length > 1)
{
cellReference = cells[1].CellReference.Value;
if (cellReference.StartsWith("B", StringComparison.OrdinalIgnoreCase))
{
value = reader.GetCellValue(cells[1]);
}
}
Console.WriteLine($"{metadataHeader}: {value}");
}
List <string> errors = new List <string>();
var vm = reader.Convert(errors);
string json = Newtonsoft.Json.JsonConvert.SerializeObject(vm);
Console.WriteLine(json);
document.Close();
}
Console.WriteLine(string.Empty);
}
}
public async Task <IActionResult> Upload(IEnumerable <IFormFile> files, [FromForm] string metaData)
{
if (User.Identity.IsAuthenticated == false)
{
return(BadRequest(new Models.ApiErrorResult("User must be authenticated to view measures.")));
}
if (!Request.ContentType.Contains("multipart/form-data"))
{
return(BadRequest(new Models.ApiErrorResult("Content must be mime multipart.")));
}
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.")));
}
ChunkMetaData metadata = Newtonsoft.Json.JsonConvert.DeserializeObject <ChunkMetaData>(Request.Form["metadata"]);
if (!metadata.FileExtension.EndsWith("xlsx", StringComparison.OrdinalIgnoreCase) && !metadata.FileExtension.EndsWith("json", StringComparison.OrdinalIgnoreCase))
{
return(BadRequest(new Models.ApiErrorResult("Only Excel and json files are valid.")));
}
var user = await _modelDB.Users.FindAsync(Guid.Parse(User.Claims.Where(x => x.Type == ClaimTypes.NameIdentifier).Select(x => x.Value).FirstOrDefault()));
await _fileService.WriteToStreamAsync(files.FirstOrDefault(), metadata);
if (!metadata.IsFinalChunck)
{
return(Ok(new UploadResult(metadata.UploadUid, metadata.IsFinalChunck)));
}
List <string> errors = new List <string>();
string metricName = null;
Guid? metricID = null;
try
{
DQM.Models.MeasureSubmissionViewModel measure = null;
if (metadata.FileExtension.EndsWith("xlsx", StringComparison.OrdinalIgnoreCase))
{
using (var stream = _fileService.ReturnTempFileStream(metadata.UploadUid))
using (var document = SpreadsheetDocument.Open(stream, false))
{
var reader = new ASPE.DQM.Utils.MeasuresExcelReader(document);
measure = reader.Convert(errors);
document.Close();
}
//Can delete the excel file regardless of validation, will be saved as json if successfull
await _fileService.DeleteTempFileChunkAsync(metadata.UploadUid);
if (errors.Count > 0)
{
return(BadRequest(new UploadResult(metadata.UploadUid, true, errors.ToArray())));
}
//validate the submission.
if (ValidateSubmission(measure, errors) == false)
{
return(BadRequest(new UploadResult(metadata.UploadUid, true, errors.ToArray())));
}
//save as json if valid
using (var ms = new System.IO.MemoryStream())
{
using (var sw = new System.IO.StreamWriter(ms, System.Text.Encoding.UTF8, 1024, true))
using (var jw = new Newtonsoft.Json.JsonTextWriter(sw))
{
var serializerSettings = new Newtonsoft.Json.JsonSerializerSettings {
Formatting = Newtonsoft.Json.Formatting.None, DateFormatString = "'yyyy-MM-dd'"
};
var serializer = new Newtonsoft.Json.JsonSerializer();
serializer.DateFormatString = "yyyy'-'MM'-'dd";
serializer.Formatting = Newtonsoft.Json.Formatting.None;
serializer.Serialize(jw, measure);
await jw.FlushAsync();
}
ms.Seek(0, System.IO.SeekOrigin.Begin);
//ms.Position = 0;
await _fileService.WriteToStreamAsync(metadata.UploadUid, 0, ms);
}
}
else
{
//assume json file
using (var stream = _fileService.ReturnTempFileStream(metadata.UploadUid))
using (var sr = new System.IO.StreamReader(stream))
using (var jr = new Newtonsoft.Json.JsonTextReader(sr))
{
var serializer = new Newtonsoft.Json.JsonSerializer();
serializer.DateFormatString = "yyyy'-'MM'-'dd";
measure = serializer.Deserialize <Models.MeasureSubmissionViewModel>(jr);
}
//validate the submission.
if (ValidateSubmission(measure, errors) == false)
{
//upload is invalid, delete the temp file
await _fileService.DeleteTempFileChunkAsync(metadata.UploadUid);
return(BadRequest(new UploadResult(metadata.UploadUid, true, errors.ToArray())));
}
}
metricName = await _modelDB.Metrics.Where(m => m.ID == measure.MetricID.Value).Select(m => m.Title).FirstOrDefaultAsync();
metricID = measure.MetricID;
}catch (Exception ex)
{
_logger.LogError(ex, "Error validating pending measure upload.");
errors.Add(ex.Message);
}
if (errors.Count > 0)
{
return(BadRequest(new UploadResult(metadata.UploadUid, true, errors.ToArray())
{
metricID = metricID, metricName = metricName
}));
}
return(Ok(new UploadResult(metadata.UploadUid, true, metricID, metricName)));
}
public async Task TestUploadingXLSXAndConvert()
{
var app = new ApplicationFactory((IServiceCollection services) =>
{
services.Add(new ServiceDescriptor(typeof(Files.IFileService), typeof(Files.AzureBlobStorageFileService), ServiceLifetime.Transient));
});
using (var scope = app.CreateScope())
{
string sampleFolder = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"..\..\..\Measures\HPHCI");
string[] files = Directory.GetFiles(sampleFolder);
var service = (Files.IFileService)scope.ServiceProvider.GetService(typeof(Files.IFileService));
foreach (var file in files)
{
string identifier = Guid.NewGuid().ToString("D");
Console.WriteLine("Reading file: " + file);
using (var fs = new FileStream(file, FileMode.Open))
{
await service.WriteToStreamAsync(identifier, 0, fs);
}
List <string> errors = new List <string>();
try
{
DQM.Models.MeasureSubmissionViewModel measure = null;
using (var stream = service.ReturnTempFileStream(identifier))
using (var sr = new System.IO.StreamReader(stream))
using (var document = SpreadsheetDocument.Open(stream, false))
{
var reader = new ASPE.DQM.Utils.MeasuresExcelReader(document);
measure = reader.Convert(errors);
document.Close();
}
//Can delete the excel file regardless of validation, will be saved as json if successfull
await service.DeleteTempFileChunkAsync(identifier);
//save as json if valid
using (var ms = new System.IO.MemoryStream())
{
using (var sw = new System.IO.StreamWriter(ms, System.Text.Encoding.UTF8, 1024, true))
using (var jw = new Newtonsoft.Json.JsonTextWriter(sw))
{
var serializerSettings = new Newtonsoft.Json.JsonSerializerSettings {
Formatting = Newtonsoft.Json.Formatting.None, DateFormatString = "'yyyy-MM-dd'"
};
var serializer = new Newtonsoft.Json.JsonSerializer();
serializer.DateFormatString = "yyyy'-'MM'-'dd";
serializer.Formatting = Newtonsoft.Json.Formatting.None;
serializer.Serialize(jw, measure);
await jw.FlushAsync();
}
ms.Seek(0, System.IO.SeekOrigin.Begin);
//ms.Position = 0;
await service.WriteToStreamAsync(identifier, 0, ms);
}
}
catch (Exception ex)
{
Console.WriteLine($"Failed on file {file}, with error: {ex.Message}");
await service.DeleteTempFileChunkAsync(identifier);
//Assert.Fail();
}
try
{
Models.MeasureSubmissionViewModel import;
using (var stream = service.ReturnTempFileStream(identifier))
using (var sr = new System.IO.StreamReader(stream))
using (var jr = new Newtonsoft.Json.JsonTextReader(sr))
{
var serializer = new Newtonsoft.Json.JsonSerializer();
serializer.DateFormatString = "yyyy'-'MM'-'dd";
import = serializer.Deserialize <Models.MeasureSubmissionViewModel>(jr);
}
await service.DeleteTempFileChunkAsync(identifier);
}
catch (Exception ex)
{
Console.WriteLine($"Failed on file {file}, with error: {ex.Message}");
await service.DeleteTempFileChunkAsync(identifier);
//Assert.Fail();
}
}
}
}
public async Task UploadAndReadChunkedExcelDocumentFromBlobStorage()
{
var app = new ApplicationFactory((IServiceCollection services) =>
{
services.Add(new ServiceDescriptor(typeof(Files.IFileService), typeof(Files.AzureBlobStorageFileService), ServiceLifetime.Transient));
});
string sampleFolder = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"..\..\..\Measures\HPHCI");
string[] files = Directory.GetFiles(sampleFolder);
using (var scope = app.CreateScope())
{
var service = (Files.IFileService)scope.ServiceProvider.GetService(typeof(Files.IFileService));
string identifier = Guid.NewGuid().ToString("D");
try
{
string file = files.First();
FileInfo fi = new FileInfo(file);
using (var fs = fi.OpenRead())
{
int bytesToRead = Convert.ToInt32(fs.Length / 2);
var buffer = new byte[bytesToRead];
fs.Read(buffer, 0, bytesToRead);
var ms = new MemoryStream(buffer);
ms.Position = 0;
Console.WriteLine($"Writing excel file: \"{ file }\" to Azure Blob Storage. Total Length:{ fi.Length }. Chunk: 0, length:{ bytesToRead }");
await service.WriteToStreamAsync(identifier, 0, ms);
Console.WriteLine($"Writing excel file: \"{ file }\" to Azure Blob Storage. Total Length:{ fi.Length }. Chunk: 1, length:{ fs.Length - bytesToRead }");
await service.WriteToStreamAsync(identifier, 1, fs);
}
//Console.WriteLine($"Reading excel document: \"{ file }\" from Azure Blob Storage.");
//using (var stream = service.ReturnTempFileStream(identifier))
//{
// string filename = Path.Combine(@"z:\shared\dqmtemp", identifier + ".xlsx");
// using (var tempFS = File.Open(filename, FileMode.OpenOrCreate))
// {
// long bytesRead = 0;
// do
// {
// byte[] buffer = new byte[32];
// bytesRead = stream.Read(buffer, 0, 32);
// if (bytesRead > 0)
// {
// tempFS.Write(buffer, 0, Convert.ToInt32(bytesRead));
// }
// } while (bytesRead > 0);
// tempFS.Flush();
// Console.WriteLine($"File size of downloaded file is:{ new FileInfo(filename).Length }");
// }
//}
List <string> errors = new List <string>();
DQM.Models.MeasureSubmissionViewModel measure = null;
OpenSettings openSettings = new OpenSettings {
AutoSave = false
};
Console.WriteLine($"Reading excel document: \"{ file }\" from Azure Blob Storage.");
using (var stream = service.ReturnTempFileStream(identifier))
using (var ms = new MemoryStream())
{
//long bytesRead = 0;
//do
//{
// byte[] buffer = new byte[32];
// bytesRead = stream.Read(buffer, 0, 32);
// if (bytesRead > 0)
// {
// ms.Write(buffer, 0, Convert.ToInt32(bytesRead));
// }
//} while (bytesRead > 0);
//ms.Position = 0;
Console.WriteLine($"Total length of document reported by Azure Blob Storage:{ stream.Length }");
using (var document = SpreadsheetDocument.Open(stream, false, openSettings))
{
var reader = new ASPE.DQM.Utils.MeasuresExcelReader(document);
measure = reader.Convert(errors);
document.Close();
}
}
}
finally
{
await service.DeleteTempFileChunkAsync(identifier);
}
}
}