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<IEnumerable<Office>> GetOfficesAsync(string tenant) { using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase()) { var sql = new Sql("SELECT * FROM core.offices"); sql.OrderBy("office_id"); return await db.SelectAsync<Office>(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<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 async Task<IEnumerable<DisplayField>> GetLookupFieldsAsync(List<Filter> filters) { if (string.IsNullOrWhiteSpace(this.Database)) { return new List<DisplayField>(); } 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 get display field for entity \"{this.FullyQualifiedObjectName}\" was denied to the user with Login ID {this.LoginId}", this.LoginId); throw new UnauthorizedException("Access is denied."); } } var sql = new Sql($"SELECT {this.LookupField} AS \"key\", {this.NameColumn} as \"value\" FROM {this.FullyQualifiedObjectName} WHERE deleted=@0 ", false); FilterManager.AddFilters(ref sql, filters); sql.OrderBy("1"); return await Factory.GetAsync<DisplayField>(this.Database, sql).ConfigureAwait(false); }
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<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>> 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); }