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 #{pageNumber} of the filtered entity \"{this.FullyQualifiedObjectName}\" was denied to the user with Login ID {this.LoginId}. Filters: {filters}.");
throw new UnauthorizedException(Resources.AccessIsDenied);
}
}
long offset = (pageNumber - 1) * Config.GetPageSize(this.Database);
var sql = new Sql($"SELECT * FROM {this.FullyQualifiedObjectName} WHERE 1 = 1");
FilterManager.AddFilters(ref sql, filters);
sql.OrderBy("1");
if (pageNumber > 0)
{
sql.Append(FrapidDbServer.AddOffset(this.Database, "@0"), offset);
sql.Append(FrapidDbServer.AddLimit(this.Database, "@0"), Config.GetPageSize(this.Database));
}
return(await Factory.GetAsync <dynamic>(this.Database, sql).ConfigureAwait(false));
}
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 async Task <long> CountWhereAsync(List <Filter> filters)
{
if (string.IsNullOrWhiteSpace(this.Database))
{
return(0);
}
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 count entity \"{this.FullyQualifiedObjectName}\" was denied to the user with Login ID {this.LoginId}. Filters: {filters}.");
throw new UnauthorizedException(Resources.AccessIsDenied);
}
}
var sql = new Sql($"SELECT COUNT(*) FROM {this.FullyQualifiedObjectName} WHERE 1 = 1");
FilterManager.AddFilters(ref sql, filters);
return(await Factory.ScalarAsync <long>(this.Database, sql).ConfigureAwait(false));
}
public long CountFiltered(string filterName)
{
if (string.IsNullOrWhiteSpace(this.Database))
{
return(0);
}
if (!this.SkipValidation)
{
if (!this.Validated)
{
this.Validate(AccessTypeEnum.Read, this.LoginId, this.Database, false);
}
if (!this.HasAccess)
{
Log.Information(
$"Access to count entity \"{this.FullyQualifiedObjectName}\" was denied to the user with Login ID {this.LoginId}. Filter: {filterName}.");
throw new UnauthorizedException("Access is denied.");
}
}
var filters = this.GetFilters(this.Database, filterName);
var sql = Sql.Builder.Append($"SELECT COUNT(*) FROM {this.FullyQualifiedObjectName} WHERE 1 = 1");
FilterManager.AddFilters(ref sql, filters);
return(Factory.Scalar <long>(this.Database, sql));
}
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 <long> CountFilteredAsync(string filterName)
{
if (string.IsNullOrWhiteSpace(this.Database))
{
return(0);
}
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 count 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);
var sql = Sql.Builder.Append($"SELECT COUNT(*) FROM {this.FullyQualifiedObjectName} WHERE deleted = @0", false);
FilterManager.AddFilters(ref sql, filters.ToList());
return(await Factory.ScalarAsync <long>(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(Resources.AccessIsDenied);
}
}
var filters = await this.GetFiltersAsync(this.Database, filterName).ConfigureAwait(false);
long offset = (pageNumber - 1) * Config.GetPageSize(this.Database);
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"), Config.GetPageSize(this.Database));
}
try
{
return(await Factory.GetAsync <dynamic>(this.Database, sql).ConfigureAwait(false));
}
catch (DbException ex)
{
Log.Error(ex.Message);
throw new DataAccessException(this.Database, ex.Message, ex);
}
}
public IEnumerable <dynamic> GetFiltered(long pageNumber, string filterName)
{
if (string.IsNullOrWhiteSpace(this.Database))
{
return(null);
}
if (!this.SkipValidation)
{
if (!this.Validated)
{
this.Validate(AccessTypeEnum.Read, this.LoginId, this.Database, 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 = this.GetFilters(this.Database, filterName);
long offset = (pageNumber - 1) * 50;
var sql = Sql.Builder.Append($"SELECT * FROM {this.FullyQualifiedObjectName} WHERE 1 = 1");
FilterManager.AddFilters(ref sql, filters);
if (!string.IsNullOrWhiteSpace(this.PrimaryKey))
{
sql.OrderBy(this.PrimaryKey);
}
if (pageNumber > 0)
{
sql.Append(FrapidDbServer.AddOffset("@0"), offset);
sql.Append(FrapidDbServer.AddLimit("@0"), 50);
}
return(Factory.Get <dynamic>(this.Database, sql));
}