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