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<IEnumerable<User>> GetUsersAsync(string tenant) { using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase()) { var sql = new Sql("SELECT * FROM account.users"); sql.Where("status=@0", true); return await db.SelectAsync<User>(sql).ConfigureAwait(false); } }
public static async Task<IEnumerable<Content>> GetContentsAsync(string tenant) { using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase()) { var sql = new Sql("SELECT * FROM website.contents"); sql.Where("is_homepage=@0", true); return await db.SelectAsync<Content>(sql).ConfigureAwait(false); } }
public static async Task<IEnumerable<MenuAccessPolicy>> GetPolicyAsync(string tenant, int officeId, int userId) { using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase()) { var sql = new Sql("SELECT * FROM auth.menu_access_policy"); sql.Where("office_id=@0", officeId); sql.And("user_id=@0", userId); return await db.SelectAsync<MenuAccessPolicy>(sql).ConfigureAwait(false); } }
public static async Task<IEnumerable<MenuItemView>> GetMenuItemsAsync(string tenant, string menuName) { using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase()) { var sql = new Sql("SELECT * FROM website.menu_item_view"); sql.Where("LOWER(menu_name)=@0", menuName.ToLower()); sql.OrderBy("sort"); return await db.SelectAsync<MenuItemView>(sql).ConfigureAwait(false); } }
public static async Task<Content> GetAsync(string tenant, int contentId) { using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase()) { var sql = new Sql("SELECT * FROM website.contents"); sql.Where("content_id=@0", contentId); var awaiter = await db.SelectAsync<Content>(sql).ConfigureAwait(false); return awaiter.FirstOrDefault(); } }
public static async Task<ConfigurationProfile> GetActiveProfileAsync(string tenant) { using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase()) { var sql = new Sql("SELECT * FROM account.configuration_profiles"); sql.Where("is_active=@0", true); var awaiter = await db.SelectAsync<ConfigurationProfile>(sql).ConfigureAwait(false); return awaiter.FirstOrDefault(); } }
public static async Task<User> GetAsync(string tenant, string email) { using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase()) { var sql = new Sql("SELECT * FROM account.users"); sql.Where("email=@0", email); sql.Limit(db.DatabaseType, 1, 0, "user_id"); var awaiter = await db.SelectAsync<User>(sql).ConfigureAwait(false); return awaiter.FirstOrDefault(); } }
public static async Task<DTO.Configuration> GetDefaultConfigurationAsync(string tenant) { using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase()) { var sql = new Sql("SELECT * FROM website.configurations"); sql.Where("is_default=@0", true); sql.Limit(db.DatabaseType, 1, 0, "configuration_id"); var awaiter = await db.SelectAsync<DTO.Configuration>(sql).ConfigureAwait(false); return awaiter.FirstOrDefault(); } }
public static async Task<IEnumerable<Contact>> GetContactsAsync(string tenant) { using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase()) { var sql = new Sql("SELECT * FROM website.contacts"); sql.Where("status=@0", true); sql.And("deleted=@0", false); sql.OrderBy("sort, contact_id"); return await db.SelectAsync<Contact>(sql).ConfigureAwait(false); } }
public static async Task<int[]> GetGroupPolicyAsync(string tenant, int officeId, int roleId) { using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase()) { var sql = new Sql("SELECT * FROM auth.group_menu_access_policy"); sql.Where("office_id=@0", officeId); sql.And("role_id=@0", roleId); var awaiter = await db.SelectAsync<GroupMenuAccessPolicy>(sql).ConfigureAwait(false); return awaiter.Select(x => x.MenuId).ToArray(); } }
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<SmtpConfig> GetConfigAsync(string tenant) { using(var db = DbProvider.GetDatabase(tenant)) { var sql = new Sql("SELECT * FROM config.smtp_configs"); sql.Where("enabled=@0", true); sql.And("is_default=@0", true); sql.Limit(db.DatabaseType, 1, 0, "smtp_config_id"); var awaiter = await db.SelectAsync<SmtpConfig>(sql).ConfigureAwait(false); return awaiter.FirstOrDefault(); } }
public static async Task<IEnumerable<PublishedContentView>> GetBlogContentsAsync(string tenant, string categoryAlias, int limit, int offset) { using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase()) { var sql = new Sql("SELECT * FROM website.published_content_view"); sql.Where("LOWER(category_alias)=@0", categoryAlias); sql.And("is_blog=@0", true); sql.Limit(db.DatabaseType, limit, offset, "publish_on"); return await db.SelectAsync<PublishedContentView>(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 static async Task<PublishedContentView> GetPublishedAsync(string tenant, string categoryAlias, string alias, bool isBlog) { if (string.IsNullOrWhiteSpace(alias)) { return await GetDefaultAsync(tenant).ConfigureAwait(false); } using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase()) { var sql = new Sql("SELECT * FROM website.published_content_view"); sql.Where("LOWER(alias)=@0", alias.ToLower()); sql.And("LOWER(category_alias)=@0", categoryAlias); sql.And("is_blog=@0", isBlog); var awaiter = await db.SelectAsync<PublishedContentView>(sql).ConfigureAwait(false); return awaiter.FirstOrDefault(); } }
public static async Task<int> CountBlogContentsAsync(string tenant, string categoryAlias) { using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase()) { var sql = new Sql("SELECT COUNT(*) FROM website.published_content_view"); sql.And("is_blog=@0", true); sql.Where("LOWER(category_alias)=@0", categoryAlias); return await db.ScalarAsync<int>(sql).ConfigureAwait(false); } }
public async Task<dynamic> GetNextAsync(object primaryKey) { 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 next entity of \"{this.FullyQualifiedObjectName}\" by \"{this.PrimaryKey}\" with value {primaryKey} was denied to the user with Login ID {this.LoginId}"); throw new UnauthorizedException("Access is denied."); } } //$"SELECT * FROM {this.FullyQualifiedObjectName} WHERE {this.PrimaryKey} > @0 //ORDER BY {this.PrimaryKey} LIMIT 1;"; var sql = new Sql($"SELECT * FROM {this.FullyQualifiedObjectName} WHERE deleted=@0", false); sql.Where($"{this.PrimaryKey} > @0", primaryKey); sql.OrderBy(this.PrimaryKey); 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>> 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 static async Task<IEnumerable<PublishedContentView>> SearchAsync(string tenant, string query) { query = "%" + query.ToLower() + "%"; using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase()) { var sql = new Sql("SELECT * FROM website.published_content_view"); sql.Where("LOWER(title) LIKE @0", query); sql.And("LOWER(alias) LIKE @0", query); sql.And("LOWER(contents) LIKE @0", query); return await db.SelectAsync<PublishedContentView>(sql).ConfigureAwait(false); } }
public static async Task<PublishedContentView> GetDefaultAsync(string tenant) { using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase()) { var sql = new Sql("SELECT * FROM website.published_content_view"); sql.Where("is_homepage=@0", true); sql.Limit(db.DatabaseType, 1, 0, "publish_on"); var awaiter = await db.SelectAsync<PublishedContentView>(sql).ConfigureAwait(false); return awaiter.FirstOrDefault(); } }
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 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<IEnumerable<Filter>> GetFiltersAsync(string tenant, string filterName) { using (var db = DbProvider.GetDatabase(this.Database)) { var sql = new Sql("SELECT * FROM config.filters"); sql.Where("object_name = @0", this.FullyQualifiedObjectName); sql.And("LOWER(filter_name)=@0", filterName.ToLower()); return await db.SelectAsync<Filter>(sql).ConfigureAwait(false); } }