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); }