public async Task<IEnumerable<dynamic>> GetWhereAsync(long pageNumber, List<Filter> filters)
{
if (string.IsNullOrWhiteSpace(this.Database))
{
return null;
}
if (!this.SkipValidation)
{
if (!this.Validated)
{
await this.ValidateAsync(AccessTypeEnum.Read, this.LoginId, this.Database, false).ConfigureAwait(false);
}
if (!this.HasAccess)
{
Log.Information("Access to Page #{Page} of the filtered entity \"Filter\" was denied to the user with Login ID {LoginId}. Filters: {Filters}.", pageNumber, this.LoginId, filters);
throw new UnauthorizedException("Access is denied.");
}
}
long offset = (pageNumber - 1) * 50;
var sql = new Sql("SELECT * FROM config.filters WHERE 1 = 1");
FilterManager.AddFilters(ref sql, new Filter(), filters);
sql.OrderBy("filter_id");
if (pageNumber > 0)
{
sql.Append(FrapidDbServer.AddOffset(this.Database, "@0"), offset);
sql.Append(FrapidDbServer.AddLimit(this.Database, "@0"), 50);
}
return await Factory.GetAsync<dynamic>(this.Database, sql).ConfigureAwait(false);
}
public static async Task SetSuccessAsync(string database, long queueId)
{
var sql = new Sql("UPDATE config.email_queue SET");
sql.Append("delivered=@0, ", true);
sql.Append("delivered_on=" + FrapidDbServer.GetDbTimestampFunction(database));
sql.Where("queue_id=@0", queueId);
using (var db = DbProvider.GetDatabase(database))
{
await db.NonQueryAsync(sql).ConfigureAwait(false);
}
}
public static async Task<IEnumerable<EmailQueue>> GetMailInQueueAsync(string database)
{
using (var db = DbProvider.GetDatabase(database))
{
var sql = new Sql("SELECT * FROM config.email_queue");
sql.Where("is_test=@0", false);
sql.Append("AND delivered=@0", false);
sql.Append("AND canceled=@0", false);
sql.Append("AND send_on<=" + FrapidDbServer.GetDbTimestampFunction(database));
return await db.SelectAsync<EmailQueue>(sql).ConfigureAwait(false);
}
}
public static async Task ChangePasswordAsync(string tenant, int userId, string newPassword, RemoteUser remoteUser)
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
{
var sql = new Sql("UPDATE account.users SET");
sql.Append("password=@0,", newPassword);
sql.Append("audit_user_id=@0,", userId);
sql.Append("audit_ts=@0,", DateTimeOffset.UtcNow);
sql.Append("last_ip=@0,", remoteUser.IpAddress);
sql.Append("last_seen_on=@0", DateTimeOffset.UtcNow);
sql.Where("user_id=@0", userId);
await db.NonQueryAsync(sql).ConfigureAwait(false);
}
}
public static async Task UpdateActivityAsync(string tenant, int userId, string ip, string browser)
{
using (var db = DbProvider.GetDatabase(tenant))
{
var sql = new Sql("UPDATE account.users SET ");
sql.Append("last_seen_on = " + FrapidDbServer.GetDbTimestampFunction(tenant));
sql.Append(",");
sql.Append("last_ip = @0", ip);
sql.Append(",");
sql.Append("last_browser = @0", browser);
sql.Where("user_id=@0", userId);
await db.NonQueryAsync(sql).ConfigureAwait(false);
}
}
public async Task<IEnumerable<dynamic>> GetAsync(string resource, int userId, object[] resourceIds)
{
if(string.IsNullOrWhiteSpace(this.Database))
{
return null;
}
if(!this.SkipValidation)
{
if(!this.Validated)
{
await this.ValidateAsync(AccessTypeEnum.Read, this.LoginId, this.Database, false).ConfigureAwait(false);
}
if(!this.HasAccess)
{
Log.Information("Access to entity \"FlagView\" was denied to the user with Login ID {LoginId}. Resource: {Resource}, ResourceIds {ResourceIds}.", this.LoginId, resource, resourceIds);
throw new UnauthorizedException("Access is denied.");
}
}
var sql = new Sql("SELECT * FROM config.flag_view");
sql.Where("resource=@0", resource);
sql.And("user_id=@0", userId);
sql.Append("AND");
sql.In("resource_id IN (@0)", resourceIds);
return await Factory.GetAsync<dynamic>(this.Database, sql).ConfigureAwait(false);
}
public static async Task SaveGroupPolicyAsync(string tenant, int officeId, int roleId, List<AccessPolicyInfo> policies)
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
{
try
{
await db.BeginTransactionAsync().ConfigureAwait(false);
var sql = new Sql();
sql.Append("DELETE FROM auth.group_entity_access_policy");
sql.Append("WHERE office_id = @0", officeId);
sql.Append("AND role_id = @0", roleId);
await db.NonQueryAsync(sql).ConfigureAwait(false);
foreach (var policy in policies)
{
var poco = new GroupEntityAccessPolicy
{
EntityName = policy.EntityName,
OfficeId = officeId,
RoleId = roleId,
AccessTypeId = policy.AccessTypeId,
AllowAccess = policy.AllowAccess
};
await db.InsertAsync("auth.group_entity_access_policy", "group_entity_access_policy_id", true, poco).ConfigureAwait(false);
}
db.CommitTransaction();
}
catch
{
db.RollbackTransaction();
throw;
}
}
}
public async Task<IEnumerable<dynamic>> GetAsync(long[] kanbanIds, object[] resourceIds)
{
if (string.IsNullOrWhiteSpace(this.Database))
{
return null;
}
if (!this.SkipValidation)
{
if (!this.Validated)
{
await this.ValidateAsync(AccessTypeEnum.Read, this.LoginId, this.Database, false).ConfigureAwait(false);
}
if (!this.HasAccess)
{
Log.Information(
"Access to entity \"KanbanDetail\" was denied to the user with Login ID {LoginId}. KanbanId: {KanbanIds}, ResourceIds {ResourceIds}.",
this.LoginId, kanbanIds, resourceIds);
throw new UnauthorizedException("Access is denied.");
}
}
if (kanbanIds == null ||
resourceIds == null ||
!kanbanIds.Any() ||
!resourceIds.Any())
{
return new List<dynamic>();
}
var sql = new Sql("SELECT * FROM config.kanban_details WHERE");
sql.In("kanban_id IN(@0)", kanbanIds);
sql.Append("AND");
sql.In("resource_id IN(@0)", resourceIds);
return await Factory.GetAsync<dynamic>(this.Database, sql).ConfigureAwait(false);
}
public async Task<object> AddAsync(Dictionary<string, object> item, List<CustomField> customFields,
bool skipPrimaryKey)
{
if (string.IsNullOrWhiteSpace(this.Database))
{
return null;
}
if (!this.SkipValidation)
{
if (!this.Validated)
{
await this.ValidateAsync(AccessTypeEnum.Create, this.LoginId, this.Database, false).ConfigureAwait(false);
}
if (!this.HasAccess)
{
Log.Information(
$"Access to add entity \"{this.FullyQualifiedObjectName}\" was denied to the user with Login ID {this.LoginId}. {item}");
throw new UnauthorizedException("Access is denied.");
}
}
item = this.Crypt(item);
item["audit_user_id"] = this.UserId;
item["audit_ts"] = DateTimeOffset.UtcNow;
item["deleted"] = false;
using (var db = DbProvider.GetDatabase(this.Database))
{
string columns = string.Join
(",",
skipPrimaryKey
? item.Where(x => !x.Key.ToUnderscoreLowerCase().Equals(this.PrimaryKey))
.Select(x => Sanitizer.SanitizeIdentifierName(x.Key).ToUnderscoreLowerCase())
: item.Select(x => Sanitizer.SanitizeIdentifierName(x.Key).ToUnderscoreLowerCase()));
string parameters = string.Join(",",
Enumerable.Range(0, skipPrimaryKey ? item.Count - 1 : item.Count).Select(x => "@" + x));
var arguments = skipPrimaryKey
? item.Where(x => !x.Key.ToUnderscoreLowerCase().Equals(this.PrimaryKey))
.Select(x => x.Value).ToArray()
: item.Select(x => x.Value).ToArray();
var sql = new Sql("INSERT INTO " + this.FullyQualifiedObjectName + "(" + columns + ")");
sql.Append("SELECT " + parameters, arguments);
sql.Append(FrapidDbServer.AddReturnInsertedKey(this.Database, this.PrimaryKey));
var primaryKeyValue = await db.ScalarAsync<object>(sql).ConfigureAwait(false);
await this.AddCustomFieldsAsync(primaryKeyValue, customFields).ConfigureAwait(false);
return primaryKeyValue;
}
}
public async Task<IEnumerable<dynamic>> GetFilteredAsync(long pageNumber, string filterName)
{
if (string.IsNullOrWhiteSpace(this.Database))
{
return null;
}
if (!this.SkipValidation)
{
if (!this.Validated)
{
await this.ValidateAsync(AccessTypeEnum.Read, this.LoginId, this.Database, false).ConfigureAwait(false);
}
if (!this.HasAccess)
{
Log.Information(
$"Access to Page #{pageNumber} of the filtered entity \"{this.FullyQualifiedObjectName}\" was denied to the user with Login ID {this.LoginId}. Filter: {filterName}.");
throw new UnauthorizedException("Access is denied.");
}
}
var filters = await this.GetFiltersAsync(this.Database, filterName).ConfigureAwait(false);
long offset = (pageNumber - 1)*PageSize;
var sql = new Sql($"SELECT * FROM {this.FullyQualifiedObjectName} WHERE deleted = @0", false);
FilterManager.AddFilters(ref sql, filters.ToList());
if (!string.IsNullOrWhiteSpace(this.PrimaryKey))
{
sql.OrderBy(this.PrimaryKey);
}
if (pageNumber > 0)
{
sql.Append(FrapidDbServer.AddOffset(this.Database, "@0"), offset);
sql.Append(FrapidDbServer.AddLimit(this.Database, "@0"), PageSize);
}
return await Factory.GetAsync<dynamic>(this.Database, sql).ConfigureAwait(false);
}
public async Task<IEnumerable<dynamic>> GetPaginatedResultAsync()
{
if (string.IsNullOrWhiteSpace(this.Database))
{
return null;
}
if (!this.SkipValidation)
{
if (!this.Validated)
{
await this.ValidateAsync(AccessTypeEnum.Read, this.LoginId, this.Database, false).ConfigureAwait(false);
}
if (!this.HasAccess)
{
Log.Information(
$"Access to the first page of the entity \"{this.FullyQualifiedObjectName}\" was denied to the user with Login ID {this.LoginId}.");
throw new UnauthorizedException("Access is denied.");
}
}
var sql = new Sql($"SELECT * FROM {this.FullyQualifiedObjectName} WHERE deleted=@0", false);
sql.OrderBy(this.PrimaryKey);
sql.Append(FrapidDbServer.AddOffset(this.Database, "@0"), 0);
sql.Append(FrapidDbServer.AddLimit(this.Database, "@0"), PageSize);
return await Factory.GetAsync<dynamic>(this.Database, sql).ConfigureAwait(false);
}
public async Task UpdateAsync(Dictionary<string, object> item, object primaryKeyValue,
List<CustomField> customFields)
{
if (string.IsNullOrWhiteSpace(this.Database))
{
return;
}
if (!this.SkipValidation)
{
if (!this.Validated)
{
await this.ValidateAsync(AccessTypeEnum.Edit, this.LoginId, this.Database, false).ConfigureAwait(false);
}
if (!this.HasAccess)
{
Log.Information(
$"Access to edit entity \"{this.FullyQualifiedObjectName}\" with Primary Key {this.PrimaryKey} was denied to the user with Login ID {this.LoginId}.");
throw new UnauthorizedException("Access is denied.");
}
}
item = this.Crypt(item);
item["audit_user_id"] = this.UserId;
item["audit_ts"] = DateTimeOffset.UtcNow;
item["deleted"] = false;
using (var db = DbProvider.GetDatabase(this.Database))
{
var sql = new Sql("UPDATE " + this.FullyQualifiedObjectName + " SET");
int index = 0;
foreach (var prop in item.Where(x => !x.Key.Equals(this.PrimaryKey)))
{
if (index > 0)
{
sql.Append(",");
}
sql.Append(Sanitizer.SanitizeIdentifierName(prop.Key) + "=@0", prop.Value);
index++;
}
sql.Where(this.PrimaryKey + "=@0", primaryKeyValue);
await db.NonQueryAsync(sql).ConfigureAwait(false);
await this.AddCustomFieldsAsync(primaryKeyValue, customFields).ConfigureAwait(false);
}
}
public async Task<List<object>> BulkImportAsync(List<Dictionary<string, object>> items)
{
if (!this.SkipValidation)
{
if (!this.Validated)
{
await this.ValidateAsync(AccessTypeEnum.ImportData, this.LoginId, this.Database, false).ConfigureAwait(false);
}
if (!this.HasAccess)
{
Log.Information(
$"Access to import entity \"{this.FullyQualifiedObjectName}\" was denied to the user with Login ID {this.LoginId}.");
throw new UnauthorizedException("Access is denied.");
}
}
var result = new List<object>();
int line = 0;
using (var db = DbProvider.GetDatabase(this.Database))
{
try
{
await db.BeginTransactionAsync().ConfigureAwait(false);
items = this.Crypt(items);
foreach (var item in items)
{
line++;
item["audit_user_id"] = this.UserId;
item["audit_ts"] = DateTimeOffset.UtcNow;
item["deleted"] = false;
var primaryKeyValue = item[this.PrimaryKey];
if (primaryKeyValue != null)
{
result.Add(primaryKeyValue);
var sql = new Sql("UPDATE " + this.FullyQualifiedObjectName + " SET");
int index = 0;
foreach (var prop in item.Where(x => !x.Key.Equals(this.PrimaryKey)))
{
if (index > 0)
{
sql.Append(",");
}
sql.Append(Sanitizer.SanitizeIdentifierName(prop.Key) + "=@0", prop.Value);
index++;
}
sql.Where(this.PrimaryKey + "=@0", primaryKeyValue);
await db.NonQueryAsync(sql).ConfigureAwait(false);
}
else
{
string columns = string.Join(",",
item.Where(x => !x.Key.Equals(this.PrimaryKey))
.Select(x => Sanitizer.SanitizeIdentifierName(x.Key)));
string parameters = string.Join(",",
Enumerable.Range(0, item.Count - 1).Select(x => "@" + x));
var arguments =
item.Where(x => !x.Key.Equals(this.PrimaryKey)).Select(x => x.Value).ToArray();
var sql = new Sql("INSERT INTO " + this.FullyQualifiedObjectName + "(" + columns + ")");
sql.Append("SELECT " + parameters, arguments);
sql.Append(FrapidDbServer.AddReturnInsertedKey(this.Database, this.PrimaryKey));
result.Add(await db.ScalarAsync<object>(sql).ConfigureAwait(false));
}
}
db.CommitTransaction();
return result;
}
catch (Exception ex)
{
db.RollbackTransaction();
string errorMessage = $"Error on line {line}. {ex.Message} ";
throw new DataAccessException(errorMessage, ex);
}
}
}
public async Task<IEnumerable<dynamic>> GetAsync(object[] primaryKeys)
{
if (string.IsNullOrWhiteSpace(this.Database))
{
return null;
}
if (!this.SkipValidation)
{
if (!this.Validated)
{
await this.ValidateAsync(AccessTypeEnum.Read, this.LoginId, this.Database, false).ConfigureAwait(false);
}
if (!this.HasAccess)
{
Log.Information(
$"Access to entity \"{this.FullyQualifiedObjectName}\" was denied to the user with Login ID {this.LoginId}. Keys: {primaryKeys}.");
throw new UnauthorizedException("Access is denied.");
}
}
var sql = new Sql("SELECT * FROM {this.FullyQualifiedObjectName}");
sql.Where("deleted=@0", false);
sql.Append("AND");
sql.In("\"{this.PrimaryKey}\" IN (@0)", primaryKeys);
return await Factory.GetAsync<dynamic>(this.Database, sql).ConfigureAwait(false);
}
public async Task<dynamic> GetLastAsync()
{
if (string.IsNullOrWhiteSpace(this.Database))
{
return null;
}
if (!this.SkipValidation)
{
if (!this.Validated)
{
await this.ValidateAsync(AccessTypeEnum.Read, this.LoginId, this.Database, false).ConfigureAwait(false);
}
if (!this.HasAccess)
{
Log.Information(
$"Access to the get the last record of entity \"{this.FullyQualifiedObjectName}\" was denied to the user with Login ID {this.LoginId}");
throw new UnauthorizedException("Access is denied.");
}
}
//$"SELECT * FROM {this.FullyQualifiedObjectName}
//ORDER BY {this.PrimaryKey} DESC LIMIT 1;";
var sql = new Sql($"SELECT * FROM {this.FullyQualifiedObjectName} WHERE deleted=@0", false);
sql.Append($"ORDER BY {this.PrimaryKey} DESC");
sql.Append(FrapidDbServer.AddOffset(this.Database, "@0"), 0);
sql.Append(FrapidDbServer.AddLimit(this.Database, "@0"), 1);
return (await Factory.GetAsync<dynamic>(this.Database, sql).ConfigureAwait(false)).FirstOrDefault();
}
public async Task<IEnumerable<dynamic>> GetPaginatedResultAsync(long pageNumber)
{
if (string.IsNullOrWhiteSpace(this.Database))
{
return null;
}
if (!this.SkipValidation)
{
if (!this.Validated)
{
await this.ValidateAsync(AccessTypeEnum.Read, this.LoginId, this.Database, false).ConfigureAwait(false);
}
if (!this.HasAccess)
{
Log.Information($"Access to Page #{pageNumber} of the entity \"{this.FullyQualifiedObjectName}\" was denied to the user with Login ID {this.LoginId}.");
throw new UnauthorizedException("Access is denied.");
}
}
long offset = (pageNumber - 1) * PageSize;
//"SELECT * FROM {this.FullyQualifiedObjectName}
//ORDER BY {this.PrimaryKey} LIMIT PageSize OFFSET @0;";
var sql = new Sql($"SELECT * FROM {this.FullyQualifiedObjectName}");
sql.OrderBy(this.PrimaryKey);
sql.Append(FrapidDbServer.AddOffset(this.Database, "@0"), offset);
sql.Append(FrapidDbServer.AddLimit(this.Database, "@0"), PageSize);
return await Factory.GetAsync<dynamic>(this.Database, sql).ConfigureAwait(false);
}
