public static async Task<Contact> GetContactAsync(string tenant, int contactId)
{
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.And("contact_id=@0", contactId);
var awaiter = await db.SelectAsync<Contact>(sql).ConfigureAwait(false);
return awaiter.FirstOrDefault();
}
}
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<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<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<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<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<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<int> CountBlogContentsAsync(string tenant)
{
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);
return await db.ScalarAsync<int>(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 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);
}
}
