protected virtual AbstractDataGrid FileToAbstractGrid(IFormFile file, ParseArguments args)
{
// Determine an appropriate file handler based on the file metadata
FileHandlerBase handler;
if (file.ContentType == "text/csv" || file.FileName.EndsWith(".csv"))
{
handler = new CsvHandler(_localizer);
}
else if (file.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" || file.FileName.EndsWith(".xlsx"))
{
handler = new ExcelHandler(_localizer);
}
else
{
throw new FormatException(_localizer["Error_UnknownFileFormat"]);
}
using (var fileStream = file.OpenReadStream())
{
// Use the handler to unpack the file into an abstract grid and return it
AbstractDataGrid abstractGrid = handler.ToAbstractGrid(fileStream);
return(abstractGrid);
}
}
// Maybe we should move these to ControllerUtilities
protected FileResult AbstractGridToFileResult(AbstractDataGrid abstractFile, string format)
{
// Get abstract grid
FileHandlerBase handler;
string contentType;
if (format == FileFormats.Xlsx)
{
handler = new ExcelHandler(_localizer);
contentType = MimeTypes.Xlsx;
}
else if (format == FileFormats.Csv)
{
handler = new CsvHandler(_localizer);
contentType = MimeTypes.Csv;
}
else
{
throw new FormatException(_localizer["Error_UnknownFileFormat"]);
}
var fileStream = handler.ToFileStream(abstractFile);
fileStream.Seek(0, System.IO.SeekOrigin.Begin);
return(File(fileStream, contentType));
}
private FileResult ToFileResult(AbstractDataGrid abstractFile, string format)
{
// Get abstract grid
FileHandlerBase handler;
string contentType;
if (format == FileFormats.Xlsx)
{
handler = new ExcelHandler(_localizer);
contentType = MimeTypes.Xlsx;
}
else if (format == FileFormats.Csv)
{
handler = new CsvHandler(_localizer);
contentType = MimeTypes.Csv;
}
else
{
throw new FormatException(_localizer["Error_UnknownFileFormat"]);
}
var fileStream = handler.ToFileStream(abstractFile);
return(File(((MemoryStream)fileStream).ToArray(), contentType));
}
protected override AbstractDataGrid GetImportTemplate()
{
// Get the properties of the DTO for Save, excluding Id or EntityState
var custodyType = typeof(CustodyForSave);
var agentType = typeof(AgentForSave);
var custodyProps = custodyType.GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly);
var agentProps = agentType.GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly);
var props = custodyProps.Union(agentProps);
if (ViewId() == ORGANIZATION)
{
// For organizations, some properties are left blank
var exemptProperties = new string[] { nameof(Agent.Title), nameof(Agent.Title2), nameof(Agent.Gender) };
props.Where(p => !exemptProperties.Contains(p.Name));
}
var propsArray = props.ToArray();
// The result that will be returned
var result = new AbstractDataGrid(propsArray.Length, 1);
// Add the header
var header = result[result.AddRow()];
int i = 0;
foreach (var prop in props)
{
var display = _metadataProvider.GetMetadataForProperty(agentType, prop.Name)?.DisplayName ?? prop.Name;
if (display != Constants.Hidden)
{
header[i++] = AbstractDataCell.Cell(display);
}
}
return(result);
}
protected override AbstractDataGrid GetImportTemplate()
{
// Get the properties of the DTO for Save, excluding Id or EntityState
var type = typeof(MeasurementUnitForSave);
var props = type.GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly);
// The result that will be returned
var result = new AbstractDataGrid(props.Length, 1);
// Add the header
var header = result[result.AddRow()];
int i = 0;
foreach (var prop in props)
{
var display = _metadataProvider.GetMetadataForProperty(type, prop.Name)?.DisplayName ?? prop.Name;
if (display != Constants.Hidden)
{
header[i++] = AbstractDataCell.Cell(display);
}
}
return(result);
}
protected override Task <(List <ViewForSave>, Func <string, int?>)> ToDtosForSave(AbstractDataGrid grid, ParseArguments args)
{
throw new NotImplementedException();
}
protected Task <(List <TEntityForSave>, Func <string, int?>)> ToEntitiesForSave(AbstractDataGrid _1, ParseArguments _2)
{
throw new NotImplementedException();
}
protected abstract Task <(List <TDtoForSave>, Func <string, int?>)> ToDtosForSave(AbstractDataGrid grid, ParseArguments args);
protected override async Task <(List <AgentForSave>, Func <string, int?>)> ToDtosForSave(AbstractDataGrid grid, ParseArguments args)
{
// Get the properties of the DTO for Save, excluding Id or EntityState
string mode = args.Mode;
var readType = typeof(Agent);
var custodySaveType = typeof(CustodyForSave);
var agentSaveType = typeof(AgentForSave);
var readProps = readType.GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly)
.ToDictionary(prop => _metadataProvider.GetMetadataForProperty(readType, prop.Name)?.DisplayName ?? prop.Name, StringComparer.InvariantCultureIgnoreCase);
var orgExemptProperties = new string[] { nameof(Agent.Title), nameof(Agent.Title2), nameof(Agent.Gender) };
var saveProps = custodySaveType.GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly)
.Union(agentSaveType.GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly))
.Where(e => ViewId() == INDIVIDUAL || orgExemptProperties.Contains(e.Name)) // Take away
.ToDictionary(prop => _metadataProvider.GetMetadataForProperty(agentSaveType, prop.Name)?.DisplayName ?? prop.Name, StringComparer.InvariantCultureIgnoreCase);
// Maps the index of the grid column to a property on the DtoForSave
var saveColumnMap = new List <(int Index, PropertyInfo Property)>(grid.RowSize);
// Make sure all column header labels are recognizable
// and construct the save column map
var firstRow = grid[0];
for (int c = 0; c < firstRow.Length; c++)
{
var column = firstRow[c];
string headerLabel = column.Content?.ToString();
// So any thing after an empty column is ignored
if (string.IsNullOrWhiteSpace(headerLabel))
{
break;
}
if (saveProps.ContainsKey(headerLabel))
{
var prop = saveProps[headerLabel];
saveColumnMap.Add((c, prop));
}
else if (readProps.ContainsKey(headerLabel))
{
// All good, just ignore
}
else
{
AddRowError(1, _localizer["Error_Column0NotRecognizable", headerLabel]);
}
}
// Milestone 1: columns in the abstract grid mapped
if (!ModelState.IsValid)
{
throw new UnprocessableEntityException(ModelState);
}
// Construct the result using the map generated earlier
List <AgentForSave> result = new List <AgentForSave>(grid.Count - 1);
for (int i = 1; i < grid.Count; i++) // Skip the header
{
var row = grid[i];
// Anything after an empty row is ignored
if (saveColumnMap.All((p) => string.IsNullOrWhiteSpace(row[p.Index].Content?.ToString())))
{
break;
}
var entity = new AgentForSave();
foreach (var(index, prop) in saveColumnMap)
{
var content = row[index].Content;
var propName = _metadataProvider.GetMetadataForProperty(readType, prop.Name).DisplayName;
// Special handling for choice lists
if (content != null)
{
var choiceListAttr = prop.GetCustomAttribute <ChoiceListAttribute>();
if (choiceListAttr != null)
{
List <string> displayNames = choiceListAttr.DisplayNames.Select(e => _localizer[e].Value).ToList();
string stringContent = content.ToString();
var displayNameIndex = displayNames.IndexOf(stringContent);
if (displayNameIndex == -1)
{
string seperator = _localizer[", "];
AddRowError(i + 1, _localizer["Error_Value0IsNotValidFor1AcceptableValuesAre2", stringContent, propName, string.Join(seperator, displayNames)]);
}
else
{
content = choiceListAttr.Choices[displayNameIndex];
}
}
}
// Special handling for DateTime and DateTimeOffset
if (prop.PropertyType.IsDateOrTime())
{
try
{
var date = ParseImportedDateTime(content);
content = date;
if (prop.PropertyType.IsDateTimeOffset())
{
content = AddUserTimeZone(date);
}
}
catch (Exception)
{
AddRowError(i + 1, _localizer["Error_TheValue0IsNotValidFor1Field", content?.ToString(), propName]);
}
}
// Try setting the value and return an error if it doesn't work
try
{
prop.SetValue(entity, content);
}
catch (ArgumentException)
{
AddRowError(i + 1, _localizer["Error_TheValue0IsNotValidFor1Field", content?.ToString(), propName]);
}
}
result.Add(entity);
}
// Milestone 2: DTOs created
if (!ModelState.IsValid)
{
throw new UnprocessableEntityException(ModelState);
}
// Prepare a dictionary of indices in order to construct any validation errors performantly
// "IndexOf" is O(n), this brings it down to O(1)
Dictionary <AgentForSave, int> indicesDic = result.ToIndexDictionary();
// For each entity, set the Id and EntityState depending on import mode
if (mode == "Insert")
{
// For Insert mode, all are marked inserted and all Ids are null
// Any duplicate codes will be handled later in the validation
result.ForEach(e => e.Id = null);
result.ForEach(e => e.EntityState = EntityStates.Inserted);
}
else
{
// For all other modes besides Insert, we need to match the entity codes to Ids by querying the DB
// Load the code Ids from the database
var nonNullCodes = result.Where(e => !string.IsNullOrWhiteSpace(e.Code));
var codesDataTable = DataTable(nonNullCodes.Select(e => new { e.Code }));
var entitiesTvp = new SqlParameter("@Codes", codesDataTable)
{
TypeName = $"dbo.CodeList",
SqlDbType = SqlDbType.Structured
};
string agentType = ViewId();
var idCodesDic = await _db.CodeIds.FromSql(
$@"SELECT c.Code, e.Id FROM @Codes c JOIN [dbo].[Custodies] e ON c.Code = e.Code WHERE e.CustodyType = 'Agent' && e.AgentType == {agentType};"
, entitiesTvp).ToDictionaryAsync(e => e.Code, e => e.Id);
result.ForEach(e =>
{
if (!string.IsNullOrWhiteSpace(e.Code) && idCodesDic.ContainsKey(e.Code))
{
e.Id = idCodesDic[e.Code];
}
else
{
e.Id = null;
}
});
// Make sure no codes are mentioned twice, if we don't do it here, the save validation later will complain
// about duplicated Id, but the error will not be clear since user deals with code while importing from Excel
var duplicateIdGroups = result.Where(e => e.Id != null).GroupBy(e => e.Id.Value).Where(g => g.Count() > 1);
foreach (var duplicateIdGroup in duplicateIdGroups)
{
foreach (var entity in duplicateIdGroup)
{
int index = indicesDic[entity];
AddRowError(index + 2, _localizer["Error_TheCode0IsDuplicated", entity.Code]);
}
}
if (mode == "Merge")
{
// Merge simply inserts codes that are not found, and updates codes that are found
result.ForEach(e =>
{
if (e.Id != null)
{
e.EntityState = EntityStates.Updated;
}
else
{
e.EntityState = EntityStates.Inserted;
}
});
}
else
{
// In the case of update: codes are required, and MUST match database Ids
if (mode == "Update")
{
for (int index = 0; index < result.Count; index++)
{
var entity = result[index];
if (string.IsNullOrWhiteSpace(entity.Code))
{
AddRowError(index + 2, _localizer["Error_CodeIsRequiredForImportModeUpdate"]);
}
else if (entity.Id == null)
{
AddRowError(index + 2, _localizer["Error_TheCode0DoesNotExist", entity.Code]);
}
}
result.ForEach(e => e.EntityState = EntityStates.Updated);
}
else
{
throw new InvalidOperationException("Unknown save mode"); // Developer bug
}
}
}
// Milestone 3: Id and EntityState are set
if (!ModelState.IsValid)
{
throw new UnprocessableEntityException(ModelState);
}
// Function that maps any future validation errors back to specific rows
int?errorKeyMap(string key)
{
int?rowNumber = null;
if (key != null && key.StartsWith("["))
{
var indexStr = key.TrimStart('[').Split(']')[0];
if (int.TryParse(indexStr, out int index))
{
// Add 2:
// 1 for the header in the abstract grid
// 1 for the difference between index and number
rowNumber = index + 2;
}
}
return(rowNumber);
}
return(result, errorKeyMap);
}
protected override AbstractDataGrid DtosToAbstractGrid(GetResponse <Agent> response, ExportArguments args)
{
// Get all the properties without Id and EntityState
var type = typeof(Agent);
var custodySaveProps = typeof(CustodyForSave).GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly);
var agentSaveProps = typeof(AgentForSave).GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly);
var readProps = typeof(Agent).GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly);
var saveProps = custodySaveProps.Union(agentSaveProps);
var props = saveProps.Union(readProps);
if (ViewId() == ORGANIZATION)
{
// For organizations, some properties are left blank
var exemptProperties = new string[] { nameof(Agent.Title), nameof(Agent.Title2), nameof(Agent.Gender) };
props.Where(p => !exemptProperties.Contains(p.Name));
}
var propsArray = props.ToArray();
// The result that will be returned
var result = new AbstractDataGrid(propsArray.Length, response.Data.Count() + 1);
// Add the header
List <PropertyInfo> addedProps = new List <PropertyInfo>(propsArray.Length);
{
var header = result[result.AddRow()];
int i = 0;
foreach (var prop in propsArray)
{
var display = _metadataProvider.GetMetadataForProperty(type, prop.Name)?.DisplayName ?? prop.Name;
if (display != Constants.Hidden)
{
header[i] = AbstractDataCell.Cell(display);
// Add the proper styling
if (prop.PropertyType.IsDateOrTime())
{
var att = prop.GetCustomAttribute <DataTypeAttribute>();
var isDateOnly = att != null && att.DataType == DataType.Date;
header[i].NumberFormat = ExportDateTimeFormat(dateOnly: isDateOnly);
}
addedProps.Add(prop);
i++;
}
}
}
// Add the rows
foreach (var entity in response.Data)
{
var row = result[result.AddRow()];
int i = 0;
foreach (var prop in addedProps)
{
var content = prop.GetValue(entity);
// Special handling for choice lists
var choiceListAttr = prop.GetCustomAttribute <ChoiceListAttribute>();
if (choiceListAttr != null)
{
var choiceIndex = Array.FindIndex(choiceListAttr.Choices, e => e.Equals(content));
if (choiceIndex != -1)
{
string displayName = choiceListAttr.DisplayNames[choiceIndex];
content = _localizer[displayName];
}
}
// Special handling for DateTimeOffset
if (prop.PropertyType.IsDateTimeOffset() && content != null)
{
content = ToExportDateTime((DateTimeOffset)content);
}
row[i] = AbstractDataCell.Cell(content);
i++;
}
}
return(result);
}
protected override AbstractDataGrid DtosToAbstractGrid(GetResponse <MeasurementUnit> response, ExportArguments args)
{
// Get all the properties without Id and EntityState
var type = typeof(MeasurementUnit);
var readProps = typeof(MeasurementUnit).GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly);
var saveProps = typeof(MeasurementUnitForSave).GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly);
var props = saveProps.Union(readProps).ToArray();
// The result that will be returned
var result = new AbstractDataGrid(props.Length, response.Data.Count() + 1);
// Add the header
List <PropertyInfo> addedProps = new List <PropertyInfo>(props.Length);
{
var header = result[result.AddRow()];
int i = 0;
foreach (var prop in props)
{
var display = _metadataProvider.GetMetadataForProperty(type, prop.Name)?.DisplayName ?? prop.Name;
if (display != Constants.Hidden)
{
header[i] = AbstractDataCell.Cell(display);
// Add the proper styling for DateTime and DateTimeOffset
if (prop.PropertyType.IsDateOrTime())
{
var att = prop.GetCustomAttribute <DataTypeAttribute>();
var isDateOnly = att != null && att.DataType == DataType.Date;
header[i].NumberFormat = ExportDateTimeFormat(dateOnly: isDateOnly);
}
addedProps.Add(prop);
i++;
}
}
}
// Add the rows
foreach (var entity in response.Data)
{
var metadata = entity.EntityMetadata;
var row = result[result.AddRow()];
int i = 0;
foreach (var prop in addedProps)
{
metadata.TryGetValue(prop.Name, out FieldMetadata meta);
if (meta == FieldMetadata.Loaded)
{
var content = prop.GetValue(entity);
// Special handling for choice lists
var choiceListAttr = prop.GetCustomAttribute <ChoiceListAttribute>();
if (choiceListAttr != null)
{
var choiceIndex = Array.FindIndex(choiceListAttr.Choices, e => e.Equals(content));
if (choiceIndex != -1)
{
string displayName = choiceListAttr.DisplayNames[choiceIndex];
content = _localizer[displayName];
}
}
// Special handling for DateTimeOffset
if (prop.PropertyType.IsDateTimeOffset() && content != null)
{
content = ToExportDateTime((DateTimeOffset)content);
}
row[i] = AbstractDataCell.Cell(content);
}
else if (meta == FieldMetadata.Restricted)
{
row[i] = AbstractDataCell.Cell(Constants.Restricted);
}
else
{
row[i] = AbstractDataCell.Cell("-");
}
i++;
}
}
return(result);
}
