/// <summary>
/// Saves the entities (Insert or Update) into the database after authorization and validation.
/// </summary>
/// <returns>Optionally returns the same entities in their persisted READ form.</returns>
protected virtual async Task <EntitiesResponse <TEntity> > SaveImplAsync(List <TEntityForSave> entities, SaveArguments args)
{
try
{
// Parse arguments
var expand = ExpandExpression.Parse(args?.Expand);
var returnEntities = args?.ReturnEntities ?? false;
// Trim all strings as a preprocessing step
entities.ForEach(e => TrimStringProperties(e));
// This implements field level security
entities = await ApplyUpdatePermissionsMask(entities);
// Start a transaction scope for save since it causes data modifications
using var trx = ControllerUtilities.CreateTransaction(null, GetSaveTransactionOptions());
// Optional preprocessing
await SavePreprocessAsync(entities);
// Validate
// Basic validation that applies to all entities
ControllerUtilities.ValidateUniqueIds(entities, ModelState, _localizer);
// Actual Validation
await SaveValidateAsync(entities);
if (!ModelState.IsValid)
{
throw new UnprocessableEntityException(ModelState);
}
// Save and retrieve Ids
var ids = await SaveExecuteAsync(entities, expand, returnEntities);
// Use the Ids to retrieve the items
EntitiesResponse <TEntity> result = null;
if (returnEntities && ids != null)
{
result = await GetByIdListAsync(ids.ToArray(), expand);
}
await PostProcess(result);
// Commit and return
await OnSaveCompleted();
trx.Complete();
return(result);
}
catch (Exception ex)
{
await OnSaveError(ex);
throw ex;
}
}
public virtual async Task <ActionResult <EntitiesResponse <TDto> > > Save([FromBody] List <TDtoForSave> entities, [FromQuery] SaveArguments args)
{
// Note here we use lists https://docs.microsoft.com/en-us/dotnet/api/system.collections.generic.list-1?view=netcore-2.1
// since the order is symantically relevant for reporting validation errors on the entities
return(await CallAndHandleErrorsAsync(async() =>
{
var result = await SaveImplAsync(entities, args);
return Ok(result);
}));
}
protected override async Task <(List <RoleForQuery>, IQueryable <RoleForQuery>)> PersistAsync(List <RoleForSave> entities, SaveArguments args)
{
// Add created entities
var roleIndices = entities.ToIndexDictionary();
DataTable rolesTable = ControllerUtilities.DataTable(entities, addIndex: true);
var rolesTvp = new SqlParameter("Roles", rolesTable)
{
TypeName = $"dbo.{nameof(RoleForSave)}List",
SqlDbType = SqlDbType.Structured
};
// Filter out permissions that haven't changed for performance
var permissionHeaderIndices = roleIndices.Keys.Select(role => (role.Permissions.Where(e => e.EntityState != null).ToList(), roleIndices[role]));
DataTable permissionsTable = ControllerUtilities.DataTableWithHeaderIndex(permissionHeaderIndices, e => e.EntityState != null);
var permissionsTvp = new SqlParameter("Permissions", permissionsTable)
{
TypeName = $"dbo.{nameof(PermissionForSave)}List",
SqlDbType = SqlDbType.Structured
};
var signatureHeaderIndices = roleIndices.Keys.Select(role => (role.Signatures.Where(e => e.EntityState != null).ToList(), roleIndices[role]));
DataTable signaturesTable = ControllerUtilities.DataTableWithHeaderIndex(signatureHeaderIndices, e => e.EntityState != null);
var signaturesTvp = new SqlParameter("Signatures", signaturesTable)
{
TypeName = $"dbo.{nameof(RequiredSignatureForSave)}List",
SqlDbType = SqlDbType.Structured
};
var memberHeaderIndices = roleIndices.Keys.Select(role => (role.Members, roleIndices[role]));
DataTable membersTable = ControllerUtilities.DataTableWithHeaderIndex(memberHeaderIndices, e => e.EntityState != null);
var membersTvp = new SqlParameter("Members", membersTable)
{
TypeName = $"dbo.{nameof(RoleMembershipForSave)}List",
SqlDbType = SqlDbType.Structured
};
string saveSql = $@"
-- TODO: PermissionsVersion
DECLARE @NewId UNIQUEIDENTIFIER = NEWID();
UPDATE [dbo].[LocalUsers] SET PermissionsVersion = @NewId;
-- Procedure: Roles__Save
DECLARE @IndexedIds [dbo].[IndexedIdList], @PermissionsIndexedIds [dbo].[IndexedIdList];
DECLARE @TenantId int = CONVERT(INT, SESSION_CONTEXT(N'TenantId'));
DECLARE @Now DATETIMEOFFSET(7) = SYSDATETIMEOFFSET();
DECLARE @UserId INT = CONVERT(INT, SESSION_CONTEXT(N'UserId'));
DELETE FROM [dbo].[Permissions]
WHERE [Id] IN (SELECT [Id] FROM @Permissions WHERE [EntityState] = N'Deleted');
DELETE FROM [dbo].[Permissions]
WHERE [Id] IN (SELECT [Id] FROM @Signatures WHERE [EntityState] = N'Deleted');
DELETE FROM [dbo].[RoleMemberships]
WHERE [Id] IN (SELECT [Id] FROM @Members WHERE [EntityState] = N'Deleted');
INSERT INTO @IndexedIds([Index], [Id])
SELECT x.[Index], x.[Id]
FROM
(
MERGE INTO [dbo].[Roles] AS t
USING (
SELECT
[Index], [Id], [Name], [Name2], [IsPublic], [Code]
FROM @Roles
WHERE [EntityState] IN (N'Inserted', N'Updated')
) AS s ON (t.Id = s.Id)
WHEN MATCHED
THEN
UPDATE SET
t.[Name] = s.[Name],
t.[Name2] = s.[Name2],
t.[IsPublic] = s.[IsPublic],
t.[Code] = s.[Code],
t.[ModifiedAt] = @Now,
t.[ModifiedById] = @UserId
WHEN NOT MATCHED THEN
INSERT (
[TenantId], [Name], [Name2], [IsPublic], [Code], [CreatedAt], [CreatedById], [ModifiedAt], [ModifiedById]
)
VALUES (
@TenantId, s.[Name], s.[Name2], s.[IsPublic], s.[Code], @Now, @UserId, @Now, @UserId
)
OUTPUT s.[Index], inserted.[Id]
) As x;
MERGE INTO [dbo].[Permissions] AS t
USING (
SELECT L.[Index], L.[Id], II.[Id] AS [RoleId], [ViewId], [Level], L.[Criteria], L.[Mask], L.[Memo]
FROM @Permissions L
JOIN @IndexedIds II ON L.[HeaderIndex] = II.[Index]
WHERE L.[EntityState] IN (N'Inserted', N'Updated')
UNION
SELECT L.[Index], L.[Id], II.[Id] AS [RoleId], [ViewId], 'Sign' AS [Level], L.[Criteria], NULL as [Mask], L.[Memo]
FROM @Signatures L
JOIN @IndexedIds II ON L.[HeaderIndex] = II.[Index]
WHERE L.[EntityState] IN (N'Inserted', N'Updated')
) AS s ON t.Id = s.Id
WHEN MATCHED THEN
UPDATE SET
t.[ViewId] = s.[ViewId],
t.[Level] = s.[Level],
t.[Criteria] = s.[Criteria],
t.[Mask] = s.[Mask],
t.[Memo] = s.[Memo],
t.[ModifiedAt] = @Now,
t.[ModifiedById] = @UserId
WHEN NOT MATCHED THEN
INSERT ([TenantId], [RoleId], [ViewId], [Level], [Criteria], [Mask], [Memo], [CreatedAt], [CreatedById], [ModifiedAt], [ModifiedById])
VALUES (@TenantId, s.[RoleId], s.[ViewId], s.[Level], s.[Criteria], s.[Mask], s.[Memo], @Now, @UserId, @Now, @UserId);
MERGE INTO [dbo].[RoleMemberships] AS t
USING (
SELECT L.[Index], L.[Id], II.[Id] AS [RoleId], [UserId], [Memo]
FROM @Members L
JOIN @IndexedIds II ON L.[HeaderIndex] = II.[Index]
WHERE L.[EntityState] IN (N'Inserted', N'Updated')
) AS s ON t.Id = s.Id
WHEN MATCHED THEN
UPDATE SET
t.[UserId] = s.[UserId],
t.[RoleId] = s.[RoleId],
t.[Memo] = s.[Memo],
t.[ModifiedAt] = @Now,
t.[ModifiedById] = @UserId
WHEN NOT MATCHED THEN
INSERT ([TenantId], [UserId], [RoleId], [Memo], [CreatedAt], [CreatedById], [ModifiedAt], [ModifiedById])
VALUES (@TenantId, s.[UserId], s.[RoleId], s.[Memo], @Now, @UserId, @Now, @UserId);
";
// Optimization
if (!(args.ReturnEntities ?? false))
{
// IF no returned items are expected, simply execute a non-Query and return an empty list;
await _db.Database.ExecuteSqlCommandAsync(saveSql, rolesTvp, permissionsTvp, signaturesTvp, membersTvp);
return(new List <RoleForQuery>(), null);
}
else
{
// If returned items are expected, append a select statement to the SQL command
saveSql = saveSql += "SELECT * FROM @IndexedIds;";
// Retrieve the map from Indexes to Ids
var indexedIds = await _db.Saving.FromSql(saveSql, rolesTvp, permissionsTvp, signaturesTvp, membersTvp).ToListAsync();
var idsString = string.Join(",", indexedIds.Select(e => e.Id));
var q = _db.VW_Roles.FromSql($"SELECT * FROM [dbo].[VW_Roles] WHERE Id IN (SELECT CONVERT(INT, VALUE) AS Id FROM STRING_SPLIT({idsString}, ','))");
q = Expand(q, args.Expand); // Includes
var savedEntities = await q.ToListAsync();
// SQL Server does not guarantee order, so make sure the result is sorted according to the initial index
Dictionary <int, int> indices = indexedIds.ToDictionary(e => e.Id, e => e.Index);
var sortedSavedEntities = new RoleForQuery[savedEntities.Count];
foreach (var item in savedEntities)
{
int index = indices[item.Id.Value];
sortedSavedEntities[index] = item;
}
// Return the sorted collection
return(sortedSavedEntities.ToList(), q);
}
}
public virtual async Task <ActionResult <EntitiesResponse <TEntity> > > Save([FromBody] List <TEntityForSave> entities, [FromQuery] SaveArguments args)
{
// Note here we use lists https://docs.microsoft.com/en-us/dotnet/api/system.collections.generic.list-1?view=netcore-2.1
// since the order is semantically relevant for reporting validation errors
// Basic sanity check, to prevent null entities
if (entities == null && !ModelState.IsValid)
{
if (ModelState.IsValid)
{
return(BadRequest("Request Body is empty."));
}
else
{
return(UnprocessableEntity(ModelState));
}
}
// Calculate server time at the very beginning for consistency
var serverTime = DateTimeOffset.UtcNow;
// Load the data
var service = GetCrudService();
var result = await service.Save(entities, args);
await OnSuccessfulSave(result);
// Transform it and return the result
if (args?.ReturnEntities ?? false)
{
// Transform the entities as an EntitiesResponse
var response = TransformToEntitiesResponse(result, serverTime, cancellation: default);
// Return the response
return(Ok(response));
}
else
{
// Return 200
return(Ok());
}
}
/// <summary> /// Persists the entities in the database, either creating them or updating them depending on the EntityState /// </summary> protected abstract Task <List <TModel> > PersistAsync(List <TDtoForSave> entities, SaveArguments args);
protected override async Task <List <M.LocalUser> > PersistAsync(List <LocalUserForSave> entities, SaveArguments args)
{
// Make all the emails small case
entities.ForEach(e => e.Email = e.Email.ToLower());
// Get the inserted users
var insertedEntities = entities.Where(e => e.EntityState == EntityStates.Inserted);
// Query the manager DB for matching emails, here I use the CodeList user-defined table type
// of the manager DB, since I only want to pass a list of strings, no need to defined a new type
var insertedEmails = insertedEntities.Select(e => new { Code = e.Email });
var emailsTable = DataTable(insertedEmails);
var emailsTvp = new SqlParameter("Emails", emailsTable)
{
TypeName = $"dbo.MCodeList",
SqlDbType = SqlDbType.Structured
};
var tenantId = new SqlParameter("TenantId", _tenantIdProvider.GetTenantId());
var globalMatches = await _adminDb.GlobalUsersMatches.FromSql($@"
DECLARE @IndexedIds [dbo].[IdList];
-- Insert new users
INSERT INTO @IndexedIds([Id])
SELECT x.[Id]
FROM
(
MERGE INTO [dbo].[GlobalUsers] AS t
USING (
SELECT [Code] as [Email] FROM @Emails
) AS s ON (t.Email = s.Email)
WHEN NOT MATCHED THEN
INSERT ([Email]) VALUES (s.[Email])
OUTPUT inserted.[Id]
) As x;
-- Insert memberships
INSERT INTO [dbo].[TenantMemberships] (UserId, TenantId)
SELECT Id, @TenantId FROM @IndexedIds;
-- Return existing users
SELECT E.[Code] AS [Email],
GU.[ExternalId] AS [ExternalId]
FROM [dbo].[GlobalUsers] GU JOIN @Emails E ON GU.Email = E.Code
WHERE GU.ExternalId IS NOT NULL",
emailsTvp, tenantId).ToDictionaryAsync(e => e.Email);
// Add created entities
var localUsersIndices = entities.ToIndexDictionary();
var localUsersTable = LocalUsersDataTable(entities, globalMatches);
var localUsersTvp = new SqlParameter("LocalUsers", localUsersTable)
{
TypeName = $"dbo.{nameof(LocalUserForSave)}List",
SqlDbType = SqlDbType.Structured
};
// Filter out roles that haven't changed for performance
var rolesHeaderIndices = localUsersIndices.Keys.Select(localUser => (localUser.Roles, HeaderIndex: localUsersIndices[localUser]));
DataTable rolesTable = DataTableWithHeaderIndex(rolesHeaderIndices, e => e.EntityState != null);
var rolesTvp = new SqlParameter("RoleMemberships", rolesTable)
{
TypeName = $"dbo.{nameof(RoleMembershipForSave)}List", SqlDbType = SqlDbType.Structured
};
string saveSql = $@"
-- Procedure: LocalUsers__Save
DECLARE @IndexedIds [dbo].[IndexedIdList];
DECLARE @TenantId int = CONVERT(INT, SESSION_CONTEXT(N'TenantId'));
DECLARE @Now DATETIMEOFFSET(7) = SYSDATETIMEOFFSET();
DECLARE @UserId INT = CONVERT(INT, SESSION_CONTEXT(N'UserId'));
DELETE FROM [dbo].[RoleMemberships]
WHERE [Id] IN (SELECT [Id] FROM @RoleMemberships WHERE [EntityState] = N'Deleted');
INSERT INTO @IndexedIds([Index], [Id])
SELECT x.[Index], x.[Id]
FROM
(
MERGE INTO [dbo].[LocalUsers] AS t
USING (
SELECT
[Index], [Id], [Name], [Name2], [Email], [ExternalId], [AgentId]
FROM @LocalUsers
WHERE [EntityState] IN (N'Inserted', N'Updated')
) AS s ON (t.Id = s.Id)
WHEN MATCHED
THEN
UPDATE SET
t.[Name] = s.[Name],
t.[Name2] = s.[Name2],
-- t.[Email] = s.[Email],
-- t.[ExternalId] = s.[ExternalId],
t.[AgentId] = s.[AgentId],
t.[ModifiedAt] = @Now,
t.[ModifiedById] = @UserId,
t.[PermissionsVersion] = NEWID(), -- in case the permissions have changed
t.[UserSettingsVersion] = NEWID() -- in case the permissions have changed
WHEN NOT MATCHED THEN
INSERT (
[TenantId], [Name], [Name2], [Email], [ExternalId], [AgentId], [CreatedAt], [CreatedById], [ModifiedAt], [ModifiedById]
)
VALUES (
@TenantId, s.[Name], s.[Name2], s.[Email], s.[ExternalId], s.[AgentId], @Now, @UserId, @Now, @UserId
)
OUTPUT s.[Index], inserted.[Id]
) As x;
MERGE INTO [dbo].[RoleMemberships] AS t
USING (
SELECT L.[Index], L.[Id], II.[Id] AS [UserId], [RoleId], [Memo]
FROM @RoleMemberships L
JOIN @IndexedIds II ON L.[HeaderIndex] = II.[Index]
WHERE L.[EntityState] IN (N'Inserted', N'Updated')
) AS s ON t.Id = s.Id
WHEN MATCHED THEN
UPDATE SET
t.[UserId] = s.[UserId],
t.[RoleId] = s.[RoleId],
t.[Memo] = s.[Memo],
t.[ModifiedAt] = @Now,
t.[ModifiedById] = @UserId
WHEN NOT MATCHED THEN
INSERT ([TenantId], [UserId], [RoleId], [Memo], [CreatedAt], [CreatedById], [ModifiedAt], [ModifiedById])
VALUES (@TenantId, s.[UserId], s.[RoleId], s.[Memo], @Now, @UserId, @Now, @UserId);
";
// Prepare the list of users whose profile picture has changed:
var usersWithModifiedImgs = entities.Where(e => e.Image != null);
bool newPictures = usersWithModifiedImgs.Any();
bool returnEntities = (args.ReturnEntities ?? false);
// Optimization
if (!returnEntities && !newPictures)
{
// IF no returned items are expected, simply execute a non-Query and return an empty list;
await _db.Database.ExecuteSqlCommandAsync(saveSql, localUsersTvp, rolesTvp);
return(new List <M.LocalUser>());
}
else
{
// If returned items are expected, append a select statement to the SQL command
saveSql = saveSql += "SELECT * FROM @IndexedIds;";
// Retrieve the map from Indexes to Ids
var indexedIds = await _db.Saving.FromSql(saveSql, localUsersTvp, rolesTvp).ToListAsync();
var idsString = string.Join(",", indexedIds.Select(e => e.Id));
var q = _db.LocalUsers.FromSql($"SELECT * FROM [dbo].[LocalUsers] WHERE Id IN (SELECT CONVERT(INT, VALUE) AS Id FROM STRING_SPLIT({idsString}, ','))");
q = Expand(q, args.Expand); // includes
var savedEntities = await q.AsNoTracking().ToListAsync();
// SQL Server does not guarantee order, so make sure the result is sorted according to the initial index
Dictionary <int, int> indices = indexedIds.ToDictionary(e => e.Id, e => e.Index);
var sortedSavedEntities = new M.LocalUser[savedEntities.Count];
foreach (var item in savedEntities)
{
int index = indices[item.Id];
sortedSavedEntities[index] = item;
}
// The code inside here is not optimized for bulk, we assume for now
// that users will be entering images one at a time
if (newPictures)
{
var entitiesDic = entities.ToIndexDictionary();
// Retrieve blobs to delete
var blobsToDelete = usersWithModifiedImgs.Where(e => e.EntityState == EntityStates.Updated)
.Select(u => sortedSavedEntities[entitiesDic[u]].ImageId).Where(e => e != null).Select(e => BlobName(e)).ToList();
var blobsToSave = new List <(string name, byte[] content)>();
foreach (var user in usersWithModifiedImgs)
{
// Get the Id of the user
int index = entitiesDic[user];
var savedEntity = sortedSavedEntities[entitiesDic[user]];
int id = savedEntity.Id;
if (user.Image.Length == 0)
{
// We simply NULL image Id
await _db.Database.ExecuteSqlCommandAsync($@"UPDATE [dbo].[LocalUsers] SET ImageId = NULL WHERE [Id] = {id}");
savedEntity.ImageId = null;
}
else
{
// We create a new Image Id
string imageId = Guid.NewGuid().ToString();
await _db.Database.ExecuteSqlCommandAsync($@"UPDATE [dbo].[LocalUsers] SET ImageId = {imageId} WHERE [Id] = {id}");
savedEntity.ImageId = imageId;
// We make the image smaller and turn it into JPEG
var imageBytes = user.Image;
using (var image = Image.Load(imageBytes))
{
// Resize to 128x128px
image.Mutate(c => c.Resize(new ResizeOptions
{
// 'Max' mode maintains the aspect ratio and keeps the entire image
Mode = ResizeMode.Max,
Size = new Size(128),
Position = AnchorPositionMode.Center
}));
// some image formats that support transparent regions
// these regions will turn black in JPEG format unless we do this
image.Mutate(c => c.BackgroundColor(Rgba32.White));;
// Save as JPEG
var memoryStream = new MemoryStream();
image.SaveAsJpeg(memoryStream);
imageBytes = memoryStream.ToArray();
// Note: JPEG is the format of choice for photography
// for such pictures it provides better quality at a lower size
// Since these pictures are expected to be mostly photographs
// we save them as JPEGs
}
// Add it to blobs to save
blobsToSave.Add((BlobName(imageId), imageBytes));
}
}
// Delete the blobs retrieved earlier
if (blobsToDelete.Any())
{
await _blobService.DeleteBlobs(blobsToDelete);
}
// Save new blobs if any
if (blobsToSave.Any())
{
await _blobService.SaveBlobs(blobsToSave);
}
// Note: Since the blob service is not a transactional resource it is good to do the blob calls
// near the end to minimize the chance of modifying blobs first only to have the transaction roll back later
}
// Return the saved entities if requested
if (!returnEntities)
{
return(new List <M.LocalUser>());
}
else
{
// Return the sorted collection
return(sortedSavedEntities.ToList());
}
}
}
public Task <ActionResult <SaveGlobalSettingsResponse> > Save([FromBody] GlobalSettingsForSave settingsForSave, [FromQuery] SaveArguments args)
{
// Authorized access (Criteria are not supported here)
// TODO Authorize
//var updatePermissions = await ControllerUtilities.GetPermissions(_db.AbstractPermissions, PermissionLevel.Update, "settings");
//if (!updatePermissions.Any())
//{
// return StatusCode(403);
//}
//try
//{
// // Trim all string fields just in case
// settingsForSave.TrimStringProperties();
// // Validate
// ValidateAndPreprocessSettings(settingsForSave);
// if (!ModelState.IsValid)
// {
// return UnprocessableEntity(ModelState);
// }
// // Persist
// M.GlobalSettings mSettings = await _repo.GlobalSettings.FirstOrDefaultAsync();
// if (mSettings == null)
// {
// // This should never happen
// return BadRequest("Global settings have not been initialized");
// }
// _mapper.Map(settingsForSave, mSettings);
// mSettings.SettingsVersion = Guid.NewGuid(); // prompts clients to refresh
// await _repo.SaveChangesAsync();
// // IF requested, return the updated entity
// if (args.ReturnEntities ?? false)
// {
// // IF requested, return the same response you would get from a GET
// var res = await GetImpl(new GetByIdArguments { Expand = args.Expand });
// var result = new SaveGlobalSettingsResponse
// {
// CollectionName = res.CollectionName,
// Result = res.Result,
// RelatedEntities = res.RelatedEntities,
// SettingsForClient = GetForClientImpl()
// };
// return result;
// }
// else
// {
// return Ok();
// }
//}
//catch (Exception ex)
//{
// _logger.LogError($"Error: {ex.Message} {ex.StackTrace}");
// return BadRequest(ex.Message);
//}
throw new NotImplementedException();
}
public async Task <(TSettings, Versioned <SettingsForClient>)> SaveSettings(TSettingsForSave settingsForSave, SaveArguments args)
{
var updatePermissions = await UserPermissions(Constants.Update, cancellation : default);
if (!updatePermissions.Any())
{
throw new ForbiddenException();
}
// Trim all string fields just in case
settingsForSave.TrimStringProperties();
// Preprocess
await Preprocess(settingsForSave);
// Validate
await SaveValidate(settingsForSave);
ModelState.ThrowIfInvalid();
// Persist
await SaveExecute(settingsForSave, args);
// Update the settings cache
var tenantId = _tenantIdAccessor.GetTenantId();
var settingsForClient = await LoadSettingsForClient(GetRepository(), cancellation : default);
_settingsCache.SetSettings(tenantId, settingsForClient);
// If requested, return the updated entity
if (args.ReturnEntities ?? false)
{
// If requested, return the same response you would get from a GET
var res = await GetSettings(args, cancellation : default);
return(res, settingsForClient);
}
else
{
return(default);
public async Task <ActionResult <SaveSettingsResponse <TSettings> > > Save([FromBody] TSettingsForSave settingsForSave, [FromQuery] SaveArguments args)
{
return(await ControllerUtilities.InvokeActionImpl(async() =>
{
var _service = GetSettingsService();
var(settings, settingsForClient) = await _service.SaveSettings(settingsForSave, args);
var singleton = new TSettings[] { settings };
var relatedEntities = ControllerUtilities.FlattenAndTrim(singleton, cancellation: default);
var result = new SaveSettingsResponse <TSettings>
{
Result = settings,
RelatedEntities = relatedEntities,
SettingsForClient = settingsForClient
};
return Ok(result);
},
_logger));
}
/// <summary>
/// Saves the entities (Insert or Update) into the database after authorization and validation
/// </summary>
/// <returns>Optionally returns the same entities in their persisted READ form</returns>
protected virtual async Task <EntitiesResponse <TDto> > SaveImplAsync(List <TDtoForSave> entities, SaveArguments args)
{
await CheckUpdatePermissions(entities, args);
// Trim all strings as a preprocessing step
entities.ForEach(e => TrimStringProperties(e));
using (var trx = await BeginSaveTransaction())
{
try
{
// Validate
await ValidateAsync(entities);
if (!ModelState.IsValid)
{
throw new UnprocessableEntityException(ModelState);
}
// Save
var(memoryList, query) = await PersistAsync(entities, args);
// Add the metadata
ApplySelectAndAddMetadata(memoryList, args.Expand, null);
// Apply the permission masks (setting restricted fields to null) and adjust the metadata accordingly
if (memoryList != null && memoryList.Any())
{
var permissions = await UserPermissions(PermissionLevel.Read);
var defaultMask = GetDefaultMask();
await ApplyReadPermissionsMask(memoryList, query, permissions, defaultMask);
}
// Flatten related entities and map each to its respective DTO
var relatedEntities = FlattenRelatedEntitiesAndTrim(memoryList, args.Expand);
// Map the primary result to DTOs as well
var resultData = Mapper.Map <List <TDto> >(memoryList);
// Prepare the result in a response object
var result = new EntitiesResponse <TDto>
{
Data = resultData,
RelatedEntities = relatedEntities,
CollectionName = GetCollectionName(typeof(TDto))
};
// Commit and return
trx.Commit();
return(result);
}
catch (Exception ex)
{
// Roll back the transaction
trx.Rollback();
throw ex;
}
}
}
/// <summary> /// Persists the entities in the database, either creating them or updating them depending on the EntityState /// </summary> protected abstract Task <(List <TDtoForQuery>, IQueryable <TDtoForQuery>)> PersistAsync(List <TDtoForSave> entities, SaveArguments args);
protected override async Task <List <M.Agent> > PersistAsync(List <AgentForSave> entities, SaveArguments args)
{
// Some properties are always set to null for organizations
string agentType = ViewId();
if (agentType == ORGANIZATION)
{
entities.ForEach(e =>
{
e.Title = null;
e.Title2 = null;
e.Gender = null;
});
}
// Add created entities
DataTable entitiesTable = DataTable(entities, addIndex: true);
var entitiesTvp = new SqlParameter("Entities", entitiesTable)
{
TypeName = $"dbo.{nameof(AgentForSave)}List",
SqlDbType = SqlDbType.Structured
};
// The agent type
var agentTypeParameter = new SqlParameter("AgentType", agentType);
string saveSql = $@"
-- Procedure: AgentsForSave
SET NOCOUNT ON;
DECLARE @IndexedIds [dbo].[IndexedIdList];
DECLARE @TenantId int = CONVERT(INT, SESSION_CONTEXT(N'TenantId'));
DECLARE @Now DATETIMEOFFSET(7) = SYSDATETIMEOFFSET();
DECLARE @UserId INT = CONVERT(INT, SESSION_CONTEXT(N'UserId'));
-- Deletions
DELETE FROM [dbo].[Custodies]
WHERE [Id] IN (SELECT [Id] FROM @Entities WHERE [EntityState] = N'Deleted');
INSERT INTO @IndexedIds([Index], [Id])
SELECT x.[Index], x.[Id]
FROM
(
MERGE INTO [dbo].[Custodies] AS t
USING (
SELECT [Index], [Id], [Name], [Name2], [Code], [Address], [BirthDateTime], [IsRelated], [TaxIdentificationNumber], [Title], [Title2], [Gender]
FROM @Entities
WHERE [EntityState] IN (N'Inserted', N'Updated')
) AS s ON (t.Id = s.Id)
WHEN MATCHED
THEN
UPDATE SET
t.[Name] = s.[Name],
t.[Name2] = s.[Name2],
t.[Code] = s.[Code],
t.[Address] = s.[Address],
t.[BirthDateTime] = s.[BirthDateTime],
t.[IsRelated] = s.[IsRelated],
t.[TaxIdentificationNumber] = s.[TaxIdentificationNumber],
t.[Title] = s.[Title],
t.[Title2] = s.[Title2],
t.[Gender] = s.[Gender],
t.[ModifiedAt] = @Now,
t.[ModifiedById] = @UserId
WHEN NOT MATCHED THEN
INSERT ([TenantId], [CustodyType], [Name], [Name2], [Code], [Address], [BirthDateTime], [AgentType], [IsRelated], [TaxIdentificationNumber], [Title], [Title2], [Gender], [CreatedAt], [CreatedById], [ModifiedAt], [ModifiedById])
VALUES (@TenantId, 'Agent', s.[Name], s.[Name2], s.[Code], s.[Address], s.[BirthDateTime], @AgentType, s.[IsRelated], s.[TaxIdentificationNumber], s.[Title], [Title2], s.[Gender], @Now, @UserId, @Now, @UserId)
OUTPUT s.[Index], inserted.[Id]
) AS x;
";
// Optimization
if (!(args.ReturnEntities ?? false))
{
// IF no returned items are expected, simply execute a non-Query and return an empty list;
await _db.Database.ExecuteSqlCommandAsync(saveSql, entitiesTvp, agentTypeParameter);
return(new List <M.Agent>());
}
else
{
// If returned items are expected, append a select statement to the SQL command
saveSql = saveSql += "SELECT * FROM @IndexedIds;";
// Retrieve the map from Indexes to Ids
var indexedIds = await _db.Saving.FromSql(saveSql, entitiesTvp, agentTypeParameter).ToListAsync();
//// Load the entities using their Ids
//DataTable idsTable = DataTable(indexedIds.Select(e => new { e.Id }), addIndex: false);
//var idsTvp = new SqlParameter("Ids", idsTable)
//{
// TypeName = $"dbo.IdList",
// SqlDbType = SqlDbType.Structured
//};
// var q = _db.Agents.FromSql("SELECT * FROM dbo.[Custodies] WHERE Id IN (SELECT Id FROM @Ids)", idsTvp);
var ids = indexedIds.Select(e => e.Id);
var q = _db.Agents.Where(e => ids.Contains(e.Id));
q = Expand(q, args.Expand);
var savedEntities = await q.ToListAsync();
// SQL Server does not guarantee order, so make sure the result is sorted according to the initial index
Dictionary <int, int> indices = indexedIds.ToDictionary(e => e.Id, e => e.Index);
var sortedSavedEntities = new M.Agent[savedEntities.Count];
foreach (var item in savedEntities)
{
int index = indices[item.Id];
sortedSavedEntities[index] = item;
}
// Return the sorted collection
return(sortedSavedEntities.ToList());
}
}
public async Task <ActionResult <SaveSettingsResponse> > Save([FromBody] SettingsForSave settingsForSave, [FromQuery] SaveArguments args)
{
// Authorized access (Criteria are not supported here)
var updatePermissions = await _repo.UserPermissions(Constants.Update, "settings");
if (!updatePermissions.Any())
{
return(StatusCode(403));
}
try
{
// Trim all string fields just in case
settingsForSave.TrimStringProperties();
// Validate
ValidateAndPreprocessSettings(settingsForSave);
if (!ModelState.IsValid)
{
return(UnprocessableEntity(ModelState));
}
// Persist
await _repo.Settings__Save(settingsForSave);
// Update the settings cache
var tenantId = _tenantIdAccessor.GetTenantId();
var settingsForClient = await LoadSettingsForClient(_repo);
_settingsCache.SetSettings(tenantId, settingsForClient);
// If requested, return the updated entity
if (args.ReturnEntities ?? false)
{
// If requested, return the same response you would get from a GET
var res = await GetImpl(new GetByIdArguments { Expand = args.Expand });
var result = new SaveSettingsResponse
{
Entities = res.Entities,
Result = res.Result,
SettingsForClient = settingsForClient
};
return(result);
}
else
{
return(Ok());
}
}
catch (Exception ex)
{
_logger.LogError($"Error: {ex.Message} {ex.StackTrace}");
return(BadRequest(ex.Message));
}
}
protected override async Task <List <M.MeasurementUnit> > PersistAsync(List <MeasurementUnitForSave> entities, SaveArguments args)
{
// Add created entities
DataTable entitiesTable = DataTable(entities, addIndex: true);
var entitiesTvp = new SqlParameter("Entities", entitiesTable)
{
TypeName = $"dbo.{nameof(MeasurementUnitForSave)}List",
SqlDbType = SqlDbType.Structured
};
string saveSql = $@"
-- Procedure: MeasurementUnitsSave
SET NOCOUNT ON;
DECLARE @IndexedIds [dbo].[IndexedIdList];
DECLARE @TenantId int = CONVERT(INT, SESSION_CONTEXT(N'TenantId'));
DECLARE @Now DATETIMEOFFSET(7) = SYSDATETIMEOFFSET();
DECLARE @UserId INT = CONVERT(INT, SESSION_CONTEXT(N'UserId'));
INSERT INTO @IndexedIds([Index], [Id])
SELECT x.[Index], x.[Id]
FROM
(
MERGE INTO [dbo].MeasurementUnits AS t
USING (
SELECT [Index], [Id], [Code], [UnitType], [Name], [Name2], [UnitAmount], [BaseAmount]
FROM @Entities
WHERE [EntityState] IN (N'Inserted', N'Updated')
) AS s ON (t.Id = s.Id)
WHEN MATCHED
THEN
UPDATE SET
t.[UnitType] = s.[UnitType],
t.[Name] = s.[Name],
t.[Name2] = s.[Name2],
t.[UnitAmount] = s.[UnitAmount],
t.[BaseAmount] = s.[BaseAmount],
t.[Code] = s.[Code],
t.[ModifiedAt] = @Now,
t.[ModifiedById] = @UserId
WHEN NOT MATCHED THEN
INSERT ([TenantId], [UnitType], [Name], [Name2], [UnitAmount], [BaseAmount], [Code], [CreatedAt], [CreatedById], [ModifiedAt], [ModifiedById])
VALUES (@TenantId, s.[UnitType], s.[Name], s.[Name2], s.[UnitAmount], s.[BaseAmount], s.[Code], @Now, @UserId, @Now, @UserId)
OUTPUT s.[Index], inserted.[Id]
) As x
OPTION(RECOMPILE)
";
// Optimization
if (!(args.ReturnEntities ?? false))
{
// IF no returned items are expected, simply execute a non-Query and return an empty list;
await _db.Database.ExecuteSqlCommandAsync(saveSql, entitiesTvp);
return(new List <M.MeasurementUnit>());
}
else
{
// If returned items are expected, append a select statement to the SQL command
saveSql = saveSql += "SELECT * FROM @IndexedIds;";
// Retrieve the map from Indexes to Ids
var indexedIds = await _db.Saving.FromSql(saveSql, entitiesTvp).ToListAsync();
// Load the entities using their Ids
DataTable idsTable = DataTable(indexedIds.Select(e => new { e.Id }), addIndex: false);
var idsTvp = new SqlParameter("Ids", idsTable)
{
TypeName = $"dbo.IdList",
SqlDbType = SqlDbType.Structured
};
var q = _db.MeasurementUnits.FromSql("SELECT * FROM dbo.[MeasurementUnits] WHERE Id IN (SELECT Id FROM @Ids)", idsTvp);
q = Expand(q, args.Expand);
var savedEntities = await q.ToListAsync();
// SQL Server does not guarantee order, so make sure the result is sorted according to the initial index
Dictionary <int, int> indices = indexedIds.ToDictionary(e => e.Id, e => e.Index);
var sortedSavedEntities = new M.MeasurementUnit[savedEntities.Count];
foreach (var item in savedEntities)
{
int index = indices[item.Id];
sortedSavedEntities[index] = item;
}
// Return the sorted collection
return(sortedSavedEntities.ToList());
}
}
public virtual async Task <ActionResult <EntitiesResponse <TEntity> > > Save([FromBody] List <TEntityForSave> entities, [FromQuery] SaveArguments args)
{
// Note here we use lists https://docs.microsoft.com/en-us/dotnet/api/system.collections.generic.list-1?view=netcore-2.1
// since the order is semantically relevant for reporting validation errors
return(await ControllerUtilities.InvokeActionImpl(async() =>
{
var result = await SaveImplAsync(entities, args);
return Ok(result);
}, _logger));
}
protected override Task <(List <ViewForQuery>, IQueryable <ViewForQuery>)> PersistAsync(List <ViewForSave> entities, SaveArguments args)
{
// TODO
throw new NotImplementedException();
}
protected override Task <List <M.Translation> > PersistAsync(List <TranslationForSave> entities, SaveArguments args)
{
throw new NotImplementedException();
}
/// <summary>
/// Verifies that the user has sufficient permissions to update he list of entities provided, this implementation
/// assumes that the view has permission levels Read and Update only, which most entities
/// </summary>
protected virtual async Task CheckUpdatePermissions(List <TDtoForSave> entities, SaveArguments args)
{
var updatePermissions = await UserPermissions(PermissionLevel.Update);
if (!updatePermissions.Any())
{
// User has no permissions on this table whatsoever, forbid
throw new ForbiddenException();
}
else if (updatePermissions.Any(e => string.IsNullOrWhiteSpace(e.Criteria)))
{
// User has unfiltered update permission on the table => proceed
return;
}
else
{
// User can update items under certain conditions, so we check those conditions here
IEnumerable <string> criteriaList = updatePermissions.Select(e => e.Criteria);
// The parameter on which the expression is based
var eParam = Expression.Parameter(typeof(TModel));
// Prepare the lambda
Expression whereClause = ToORedWhereClause <TModel>(criteriaList, eParam);
var lambda = Expression.Lambda <Func <TModel, bool> >(whereClause, eParam);
/////// Part (1) Permissions must allow manipulating the original data before the update
var existingItems = entities.Where(e => e.EntityState == EntityStates.Updated ||
e.EntityState == EntityStates.Deleted);
if (existingItems.Any())
{
await CheckPermissionsForOld(existingItems.Select(e => e.Id), lambda);
}
/////// Part (2) Permissions must work for the new data after the update, only for the modified properties
var newItems = entities.Where(e => e.EntityState == EntityStates.Inserted ||
e.EntityState == EntityStates.Updated);
if (newItems.Any())
{
await CheckPermissionsForNew(newItems, lambda);
}
}
}
public async Task <ActionResult <SaveSettingsResponse> > Save([FromBody] SettingsForSave settingsForSave, [FromQuery] SaveArguments args)
{
// Authorized access (Criteria are not supported here)
var updatePermissions = await ControllerUtilities.GetPermissions(_db.AbstractPermissions, PermissionLevel.Update, "settings");
if (!updatePermissions.Any())
{
return(StatusCode(403));
}
try
{
// Trim all string fields just in case
settingsForSave.TrimStringProperties();
// Validate
ValidateAndPreprocessSettings(settingsForSave);
if (!ModelState.IsValid)
{
return(UnprocessableEntity(ModelState));
}
// Persist
M.Settings mSettings = await _db.Settings.FirstOrDefaultAsync();
if (mSettings == null)
{
// This should never happen
return(BadRequest("Settings have not been initialized"));
}
_mapper.Map(settingsForSave, mSettings);
mSettings.ModifiedAt = DateTimeOffset.Now;
mSettings.ModifiedById = _tenantInfo.GetCurrentInfo().UserId.Value;
mSettings.SettingsVersion = Guid.NewGuid(); // prompts clients to refresh
await _db.SaveChangesAsync();
// If requested, return the updated entity
if (args.ReturnEntities ?? false)
{
// If requested, return the same response you would get from a GET
var res = await GetImpl(new GetByIdArguments { Expand = args.Expand });
var result = new SaveSettingsResponse
{
CollectionName = res.CollectionName,
Entity = res.Entity,
RelatedEntities = res.RelatedEntities,
SettingsForClient = await GetForClientImpl()
};
return(result);
}
else
{
return(Ok());
}
}
catch (Exception ex)
{
_logger.LogError($"Error: {ex.Message} {ex.StackTrace}");
return(BadRequest(ex.Message));
}
}
/// <summary>
/// Saves the entities (Insert or Update) into the database after authorization and validation
/// </summary>
/// <returns>Optionally returns the same entities in their persisted READ form</returns>
protected virtual async Task <EntitiesResponse <TDto> > SaveImplAsync(List <TDtoForSave> entities, SaveArguments args)
{
// TODO Authorize POST
await CheckUpdatePermissions(entities, args);
// Trim all strings as a preprocessing step
entities.ForEach(e => TrimStringProperties(e));
using (var trx = await BeginSaveTransaction())
{
try
{
// Validate
await ValidateAsync(entities);
if (!ModelState.IsValid)
{
throw new UnprocessableEntityException(ModelState);
}
// Save
var memoryList = await PersistAsync(entities, args);
// Flatten related entities and map each to its respective DTO
var relatedEntities = FlattenRelatedEntities(memoryList, args.Expand);
// Map the primary result to DTOs as well
var resultData = Map(memoryList);
// Prepare the result in a response object
var result = new EntitiesResponse <TDto>
{
Data = resultData,
RelatedEntities = relatedEntities,
CollectionName = GetCollectionName(typeof(TDto))
};
// Commit and return
trx.Commit();
return(result);
}
catch (Exception ex)
{
// Roll back the transaction
trx.Rollback();
throw ex;
}
}
}
protected override Task <List <ViewDefinition> > PersistAsync(List <ViewForSave> entities, SaveArguments args)
{
throw new NotImplementedException();
}
/// <summary>
/// Saves <paramref name="settingsForSave"/> as per the specifications in <paramref name="args"/>
/// after authorization.
/// </summary>
/// <param name="settingsForSave">The settings to save.</param>
/// <param name="args">The specifications of the save operation.</param>
/// <returns>Optionally returns the new READ settings and the new <see cref="SettingsForClient"/>.</returns>
public async Task <(TSettings, Versioned <SettingsForClient>)> SaveSettings(TSettingsForSave settingsForSave, SaveArguments args)
{
await Initialize();
var updatePermissions = await UserPermissions(PermissionActions.Update, cancellation : default);
if (!updatePermissions.Any())
{
throw new ForbiddenException();
}
// Trim all string fields
settingsForSave.StructuralPreprocess();
// Attribute Validation
var meta = _metadataProvider.GetMetadata(TenantId, typeof(TSettingsForSave), null, null);
ValidateEntity(settingsForSave, meta);
// Start the transaction
using var trx = TransactionFactory.ReadCommitted();
// Persist
await SaveExecute(settingsForSave, args);
// If requested, return the updated entity
TSettings res = default;
Versioned <SettingsForClient> newSettingsForClient = default;
if (args.ReturnEntities ?? false)
{
// Get the latest settings for client
newSettingsForClient = await _settingsCache.GetSettings(
tenantId : TenantId,
version : "refresh", // Random string forces a new result from the DB
cancellation : default);
