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