Esempio n. 1
0
        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);
        }
Esempio n. 2
0
        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);
        }
Esempio n. 3
0
File: Sql.cs Progetto: frapid/frapid
        public Sql Append(Sql sql)
        {
            this._parameters.AddRange(sql._parameters);
            this._builder.Append(sql._builder);

            return this;
        }
Esempio n. 4
0
 public static async Task<IEnumerable<PublishedContentView>> GetAllPublishedContentsAsync(string tenant)
 {
     using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
     {
         var sql = new Sql("SELECT * FROM website.published_content_view");
         return await db.SelectAsync<PublishedContentView>(sql).ConfigureAwait(false);
     }
 }
Esempio n. 5
0
 public static async Task<IEnumerable<EntityView>> GetAsync(string tenant)
 {
     using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
     {
         var sql = new Sql("SELECT * FROM auth.entity_view");
         return await db.SelectAsync<EntityView>(sql).ConfigureAwait(false);
     }
 }
Esempio n. 6
0
        public static async Task<IEnumerable<User>> GetUsersAsync(string tenant)
        {
            using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
            {
                var sql = new Sql("SELECT * FROM account.users");
                sql.Where("status=@0", true);

                return await db.SelectAsync<User>(sql).ConfigureAwait(false);
            }
        }
Esempio n. 7
0
        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);
            }
        }
Esempio n. 8
0
        public static async Task<IEnumerable<Content>> GetContentsAsync(string tenant)
        {
            using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
            {
                var sql = new Sql("SELECT * FROM website.contents");
                sql.Where("is_homepage=@0", true);

                return await db.SelectAsync<Content>(sql).ConfigureAwait(false);
            }
        }
Esempio n. 9
0
        public static async Task<Content> GetAsync(string tenant, int contentId)
        {
            using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
            {
                var sql = new Sql("SELECT * FROM website.contents");
                sql.Where("content_id=@0", contentId);

                var awaiter = await db.SelectAsync<Content>(sql).ConfigureAwait(false);
                return awaiter.FirstOrDefault();
            }
        }
Esempio n. 10
0
        public static async Task<ConfigurationProfile> GetActiveProfileAsync(string tenant)
        {
            using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
            {
                var sql = new Sql("SELECT * FROM account.configuration_profiles");
                sql.Where("is_active=@0", true);

                var awaiter = await db.SelectAsync<ConfigurationProfile>(sql).ConfigureAwait(false);
                return awaiter.FirstOrDefault();
            }
        }
Esempio n. 11
0
        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);
            }
        }
Esempio n. 12
0
        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);
            }
        }
Esempio n. 13
0
        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);
            }
        }
Esempio n. 14
0
        public static async Task<DTO.Configuration> GetDefaultConfigurationAsync(string tenant)
        {
            using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
            {
                var sql = new Sql("SELECT * FROM website.configurations");
                sql.Where("is_default=@0", true);
                sql.Limit(db.DatabaseType, 1, 0, "configuration_id");

                var awaiter = await db.SelectAsync<DTO.Configuration>(sql).ConfigureAwait(false);
                return awaiter.FirstOrDefault();
            }
        }
Esempio n. 15
0
        public static async Task<User> GetAsync(string tenant, string email)
        {
            using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
            {
                var sql = new Sql("SELECT * FROM account.users");
                sql.Where("email=@0", email);
                sql.Limit(db.DatabaseType, 1, 0, "user_id");

                var awaiter = await db.SelectAsync<User>(sql).ConfigureAwait(false);
                return awaiter.FirstOrDefault();
            }
        }
Esempio n. 16
0
        public static async Task SetSuccessAsync(string database, long queueId)
        {
            var sql = new Sql("UPDATE config.email_queue SET");

            sql.Append("delivered=@0, ", true);
            sql.Append("delivered_on=" + FrapidDbServer.GetDbTimestampFunction(database));
            sql.Where("queue_id=@0", queueId);

            using (var db = DbProvider.GetDatabase(database))
            {
                await db.NonQueryAsync(sql).ConfigureAwait(false);
            }
        }
Esempio n. 17
0
        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();
            }
        }
Esempio n. 18
0
        public static async Task<IEnumerable<EmailQueue>> GetMailInQueueAsync(string database)
        {
            using (var db = DbProvider.GetDatabase(database))
            {
                var sql = new Sql("SELECT * FROM config.email_queue");
                sql.Where("is_test=@0", false);
                sql.Append("AND delivered=@0", false);
                sql.Append("AND canceled=@0", false);
                sql.Append("AND send_on<=" + FrapidDbServer.GetDbTimestampFunction(database));

                return await db.SelectAsync<EmailQueue>(sql).ConfigureAwait(false);
            }
        }
Esempio n. 19
0
        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();
            }
        }
Esempio n. 20
0
        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);
            }
        }
Esempio n. 21
0
        public static async Task UpdateActivityAsync(string tenant, int userId, string ip, string browser)
        {
            using (var db = DbProvider.GetDatabase(tenant))
            {
                var sql = new Sql("UPDATE account.users SET ");
                sql.Append("last_seen_on = " + FrapidDbServer.GetDbTimestampFunction(tenant));
                sql.Append(",");
                sql.Append("last_ip = @0", ip);
                sql.Append(",");
                sql.Append("last_browser = @0", browser);
                sql.Where("user_id=@0", userId);

                await db.NonQueryAsync(sql).ConfigureAwait(false);
            }
        }
Esempio n. 22
0
        public static async Task ChangePasswordAsync(string tenant, int userId, string newPassword, RemoteUser remoteUser)
        {
            using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
            {
                var sql = new Sql("UPDATE account.users SET");
                sql.Append("password=@0,", newPassword);
                sql.Append("audit_user_id=@0,", userId);
                sql.Append("audit_ts=@0,", DateTimeOffset.UtcNow);
                sql.Append("last_ip=@0,", remoteUser.IpAddress);
                sql.Append("last_seen_on=@0", DateTimeOffset.UtcNow);
                sql.Where("user_id=@0", userId);

                await db.NonQueryAsync(sql).ConfigureAwait(false);
            }
        }
Esempio n. 23
0
        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();
            }
        }
Esempio n. 24
0
        public static async Task SaveGroupPolicyAsync(string tenant, int officeId, int roleId, List<AccessPolicyInfo> policies)
        {
            using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
            {
                try
                {
                    await db.BeginTransactionAsync().ConfigureAwait(false);

                    var sql = new Sql();
                    sql.Append("DELETE FROM auth.group_entity_access_policy");
                    sql.Append("WHERE office_id = @0", officeId);
                    sql.Append("AND role_id = @0", roleId);

                    await db.NonQueryAsync(sql).ConfigureAwait(false);


                    foreach (var policy in policies)
                    {
                        var poco = new GroupEntityAccessPolicy
                        {
                            EntityName = policy.EntityName,
                            OfficeId = officeId,
                            RoleId = roleId,
                            AccessTypeId = policy.AccessTypeId,
                            AllowAccess = policy.AllowAccess
                        };


                        await db.InsertAsync("auth.group_entity_access_policy", "group_entity_access_policy_id", true, poco).ConfigureAwait(false);
                    }

                    db.CommitTransaction();
                }
                catch
                {
                    db.RollbackTransaction();
                    throw;
                }
            }
        }
Esempio n. 25
0
        public async Task<IEnumerable<dynamic>> GetAsync(long[] kanbanIds, 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 \"KanbanDetail\" was denied to the user with Login ID {LoginId}. KanbanId: {KanbanIds}, ResourceIds {ResourceIds}.",
                        this.LoginId, kanbanIds, resourceIds);
                    throw new UnauthorizedException("Access is denied.");
                }
            }


            if (kanbanIds == null ||
                resourceIds == null ||
                !kanbanIds.Any() ||
                !resourceIds.Any())
            {
                return new List<dynamic>();
            }


            var sql = new Sql("SELECT * FROM config.kanban_details WHERE");
            sql.In("kanban_id IN(@0)", kanbanIds);
            sql.Append("AND");
            sql.In("resource_id IN(@0)", resourceIds);

            return await Factory.GetAsync<dynamic>(this.Database, sql).ConfigureAwait(false);
        }
Esempio n. 26
0
        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);
            }
        }
Esempio n. 27
0
        public async Task<object> AddAsync(Dictionary<string, object> item, List<CustomField> customFields,
            bool skipPrimaryKey)
        {
            if (string.IsNullOrWhiteSpace(this.Database))
            {
                return null;
            }

            if (!this.SkipValidation)
            {
                if (!this.Validated)
                {
                    await this.ValidateAsync(AccessTypeEnum.Create, this.LoginId, this.Database, false).ConfigureAwait(false);
                }
                if (!this.HasAccess)
                {
                    Log.Information(
                        $"Access to add entity \"{this.FullyQualifiedObjectName}\" was denied to the user with Login ID {this.LoginId}. {item}");
                    throw new UnauthorizedException("Access is denied.");
                }
            }

            item = this.Crypt(item);

            item["audit_user_id"] = this.UserId;
            item["audit_ts"] = DateTimeOffset.UtcNow;
            item["deleted"] = false;

            using (var db = DbProvider.GetDatabase(this.Database))
            {
                string columns = string.Join
                    (",",
                        skipPrimaryKey
                            ? item.Where(x => !x.Key.ToUnderscoreLowerCase().Equals(this.PrimaryKey))
                                .Select(x => Sanitizer.SanitizeIdentifierName(x.Key).ToUnderscoreLowerCase())
                            : item.Select(x => Sanitizer.SanitizeIdentifierName(x.Key).ToUnderscoreLowerCase()));

                string parameters = string.Join(",",
                    Enumerable.Range(0, skipPrimaryKey ? item.Count - 1 : item.Count).Select(x => "@" + x));

                var arguments = skipPrimaryKey
                    ? item.Where(x => !x.Key.ToUnderscoreLowerCase().Equals(this.PrimaryKey))
                        .Select(x => x.Value).ToArray()
                    : item.Select(x => x.Value).ToArray();

                var sql = new Sql("INSERT INTO " + this.FullyQualifiedObjectName + "(" + columns + ")");
                sql.Append("SELECT " + parameters, arguments);

                sql.Append(FrapidDbServer.AddReturnInsertedKey(this.Database, this.PrimaryKey));

                var primaryKeyValue = await db.ScalarAsync<object>(sql).ConfigureAwait(false);
                await this.AddCustomFieldsAsync(primaryKeyValue, customFields).ConfigureAwait(false);
                return primaryKeyValue;
            }
        }
Esempio n. 28
0
        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);
        }
Esempio n. 29
0
        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);
            }
        }
Esempio n. 30
0
        public static async Task<PublishedContentView> GetDefaultAsync(string tenant)
        {
            using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
            {
                var sql = new Sql("SELECT * FROM website.published_content_view");
                sql.Where("is_homepage=@0", true);
                sql.Limit(db.DatabaseType, 1, 0, "publish_on");

                var awaiter = await db.SelectAsync<PublishedContentView>(sql).ConfigureAwait(false);
                return awaiter.FirstOrDefault();
            }
        }