示例#1
0
        public async Task <long> PostAsync(string tenant, SalesReturn model)
        {
            string       connectionString = FrapidDbServer.GetConnectionString(tenant);
            const string sql = @"EXECUTE sales.post_return
                                @TransactionMasterId, @OfficeId, @UserId, @LoginId, 
                                @ValueDate, @BookDate, 
                                @StoreId, @CounterId, @CustomerId, @PriceTypeId,
                                @ReferenceNumber, @StatementReference, 
                                @Details, @TranId OUTPUT
                            ;";

            using (var connection = new SqlConnection(connectionString))
            {
                using (var command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithNullableValue("@TransactionMasterId", model.TransactionMasterId);
                    command.Parameters.AddWithNullableValue("@OfficeId", model.OfficeId);
                    command.Parameters.AddWithNullableValue("@UserId", model.UserId);
                    command.Parameters.AddWithNullableValue("@LoginId", model.LoginId);
                    command.Parameters.AddWithNullableValue("@ValueDate", model.ValueDate);
                    command.Parameters.AddWithNullableValue("@BookDate", model.BookDate);

                    command.Parameters.AddWithNullableValue("@StoreId", model.StoreId);
                    command.Parameters.AddWithNullableValue("@CounterId", model.CounterId);
                    command.Parameters.AddWithNullableValue("@CustomerId", model.CustomerId);
                    command.Parameters.AddWithNullableValue("@PriceTypeId", model.PriceTypeId);

                    command.Parameters.AddWithNullableValue("@ReferenceNumber", model.ReferenceNumber);
                    command.Parameters.AddWithNullableValue("@StatementReference", model.StatementReference);

                    using (var details = new SalesEntry.SqlServer().GetDetails(model.Details))
                    {
                        command.Parameters.AddWithNullableValue("@Details", details, "sales.sales_detail_type");
                    }

                    command.Parameters.Add("@TranId", SqlDbType.BigInt).Direction = ParameterDirection.Output;

                    connection.Open();
                    await command.ExecuteNonQueryAsync().ConfigureAwait(false);

                    return(command.Parameters["@TranId"].Value.To <long>());
                }
            }
        }
示例#2
0
        public static async Task <IEnumerable <Contact> > GetContactsAsync(string tenant, AddressBookQuery query)
        {
            using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
            {
                var sql = new Sql("SELECT * FROM addressbook.contacts");
                sql.Append("WHERE deleted = @0", false);

                if (!string.IsNullOrWhiteSpace(query.Tags))
                {
                    var tags  = query.Tags.Split(',');
                    int index = 0;

                    foreach (string tag in tags)
                    {
                        if (string.IsNullOrWhiteSpace(tag))
                        {
                            continue;
                        }

                        sql.Append(index == 0 ? "AND (" : "OR");

                        sql.Append("LOWER(tags) LIKE LOWER(@0)", "%" + tag.Trim() + "%");
                        index++;
                    }

                    if (index > 0)
                    {
                        sql.Append(")");
                    }
                }

                if (query.PrivateOnly)
                {
                    sql.Append("AND created_by = @0", query.UserId);
                    sql.Append("AND is_private = @0", true);
                }
                else
                {
                    sql.Append("AND (is_private = @0 OR created_by = @1)", false, query.UserId);
                }

                return(await db.SelectAsync <Contact>(sql).ConfigureAwait(false));
            }
        }
示例#3
0
        public async Task <long> PostAsync(string tenant, ViewModels.PurchaseReturn model)
        {
            string connectionString = FrapidDbServer.GetConnectionString(tenant);

            string sql = @"EXECUTE purchase.post_return
                                @TransactionMasterId, @OfficeId, @UserId, @LoginId, @ValueDate, @BookDate, 
                                @StoreId, @CostCenterId, @SupplierId, @PriceTypeId, @ShipperId,
                                @ReferenceNumber, @StatementReference, @Details, @InvoiceDiscount, @TranMasterId OUTPUT;";

            sql = string.Format(sql, new PurchaseEntry.PostgreSQL().GetParametersForDetails(model.Details));

            using (var connection = new SqlConnection(connectionString))
            {
                using (var command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithNullableValue("@TransactionMasterId", model.TransactionMasterId);
                    command.Parameters.AddWithNullableValue("@OfficeId", model.OfficeId);
                    command.Parameters.AddWithNullableValue("@UserId", model.UserId);
                    command.Parameters.AddWithNullableValue("@LoginId", model.LoginId);
                    command.Parameters.AddWithNullableValue("@ValueDate", model.ValueDate);
                    command.Parameters.AddWithNullableValue("@BookDate", model.BookDate);
                    command.Parameters.AddWithNullableValue("@StoreId", model.StoreId);
                    command.Parameters.AddWithNullableValue("@CostCenterId", model.CostCenterId);
                    command.Parameters.AddWithNullableValue("@ReferenceNumber", model.ReferenceNumber);
                    command.Parameters.AddWithNullableValue("@StatementReference", model.StatementReference);
                    command.Parameters.AddWithNullableValue("@SupplierId", model.SupplierId);
                    command.Parameters.AddWithNullableValue("@PriceTypeId", model.PriceTypeId);
                    command.Parameters.AddWithNullableValue("@ShipperId", model.ShipperId);

                    using (var details = PurchaseEntry.SqlServer.GetDetails(model.Details))
                    {
                        command.Parameters.AddWithNullableValue("@Details", details, "purchase.purchase_detail_type");
                    }

                    command.Parameters.AddWithNullableValue("@InvoiceDiscount", model.Discount);
                    command.Parameters.Add("@TranMasterId", SqlDbType.BigInt).Direction = ParameterDirection.Output;

                    connection.Open();
                    await command.ExecuteNonQueryAsync().ConfigureAwait(false);

                    return(command.Parameters["@TranMasterId"].Value.To <long>());
                }
            }
        }
示例#4
0
        public static async Task <EntityView> GetAsync(string database, string primaryKey, string schemaName,
                                                       string tableName)
        {
            var db = FrapidDbServer.GetServer(database);

            string procedure = FrapidDbServer.DefaultSchemaQualify(database, "poco_get_table_function_definition");
            string sql       = db.GetProcedureCommand(procedure, new[] { "@0", "@1" });

            var columns =
                await Factory.GetAsync <EntityColumn>(database, sql, schemaName, tableName).ConfigureAwait(false);

            var meta = new EntityView
            {
                PrimaryKey = primaryKey,
                Columns    = columns
            };

            return(meta);
        }
示例#5
0
        public static async Task <FeedItem> PostAsync(string tenant, Feed model)
        {
            using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
            {
                var feedId = await db.InsertAsync(model).ConfigureAwait(false);

                var sql = new Sql("SELECT feed_id, event_timestamp, formatted_text, created_by, " +
                                  "account.get_name_by_user_id(created_by) AS created_by_name, attachments, " +
                                  "scope, is_public, parent_feed_id");

                sql.Append("FROM social.feeds");
                sql.Where("deleted = @0", false);
                sql.And("feed_id = @0", feedId);

                var awaiter = await db.SelectAsync <FeedItem>(sql).ConfigureAwait(false);

                return(awaiter.FirstOrDefault());
            }
        }
示例#6
0
        public static void ChangePassword(int userId, string newPassword, RemoteUser remoteUser)
        {
            using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(AppUsers.GetTenant())).GetDatabase())
            {
                var user = db.FetchBy <User>(sql => sql.Where(u => u.UserId == userId)).FirstOrDefault();

                if (user != null)
                {
                    user.Password    = newPassword;
                    user.AuditUserId = userId;
                    user.AuditTs     = DateTimeOffset.UtcNow;
                    user.LastBrowser = remoteUser.Browser;
                    user.LastIp      = remoteUser.IpAddress;
                    user.LastSeenOn  = DateTimeOffset.UtcNow;

                    db.Update("account.users", "user_id", user, userId);
                }
            }
        }
示例#7
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())
            {
                return(await db.Query <PublishedContentView>().Where(c => c.Alias.ToLower().Equals(alias.ToLower())
                                                                     &&
                                                                     c.CategoryAlias.ToLower()
                                                                     .Equals(categoryAlias.ToLower()) &&
                                                                     c.IsBlog == isBlog
                                                                     )
                       .FirstOrDefaultAsync().ConfigureAwait(false));
            }
        }
示例#8
0
        public async Task <bool> HasSchemaAsync(string tenant, string database, string schema)
        {
            const string sql = "SELECT COUNT(*) FROM pg_catalog.pg_namespace WHERE nspname=@0;";

            using (
                var db =
                    DbProvider.Get(FrapidDbServer.GetSuperUserConnectionString(tenant, database), tenant).GetDatabase())
            {
                int awaiter = await db.ScalarAsync <int>
                              (
                    sql,
                    new object[]
                {
                    schema
                }).ConfigureAwait(false);

                return(awaiter.Equals(1));
            }
        }
示例#9
0
        public async Task <long> PostAsync(string tenant, ViewModels.PurchaseReturn model)
        {
            string connectionString = FrapidDbServer.GetConnectionString(tenant);

            string sql = @"SELECT * FROM purchase.post_return
                            (
                                @TransactionMasterId::bigint, @OfficeId::integer, @UserId::integer, @LoginId::bigint, 
                                @ValueDate::date, @BookDate::date, 
                                @StoreId::integer, @CostCenterId::integer, @SupplierId::integer, @PriceTypeId::integer, @ShipperId::integer,
                                @ReferenceNumber::national character varying(24), @StatementReference::text, ARRAY[{0}], @InvoiceDiscount
                            );";

            sql = string.Format(sql, new PurchaseEntry.PostgreSQL().GetParametersForDetails(model.Details));

            using (var connection = new NpgsqlConnection(connectionString))
            {
                using (var command = new NpgsqlCommand(sql, connection))
                {
                    command.Parameters.AddWithNullableValue("@TransactionMasterId", model.TransactionMasterId);
                    command.Parameters.AddWithNullableValue("@OfficeId", model.OfficeId);
                    command.Parameters.AddWithNullableValue("@UserId", model.UserId);
                    command.Parameters.AddWithNullableValue("@LoginId", model.LoginId);
                    command.Parameters.AddWithNullableValue("@ValueDate", model.ValueDate);
                    command.Parameters.AddWithNullableValue("@BookDate", model.BookDate);
                    command.Parameters.AddWithNullableValue("@StoreId", model.StoreId);
                    command.Parameters.AddWithNullableValue("@CostCenterId", model.CostCenterId);
                    command.Parameters.AddWithNullableValue("@ReferenceNumber", model.ReferenceNumber);
                    command.Parameters.AddWithNullableValue("@StatementReference", model.StatementReference);
                    command.Parameters.AddWithNullableValue("@SupplierId", model.SupplierId);
                    command.Parameters.AddWithNullableValue("@PriceTypeId", model.PriceTypeId);
                    command.Parameters.AddWithNullableValue("@ShipperId", model.ShipperId);

                    command.Parameters.AddRange(new PurchaseEntry.PostgreSQL().AddParametersForDetails(model.Details).ToArray());

                    command.Parameters.AddWithNullableValue("@InvoiceDiscount", model.Discount);

                    connection.Open();
                    var awaiter = await command.ExecuteScalarAsync().ConfigureAwait(false);

                    return(awaiter.To <long>());
                }
            }
        }
示例#10
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 #{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));
            }

            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);
            }
        }
示例#11
0
        public static async Task ReconcileAsync(string tenant, ReconciliationViewModel model, LoginView meta)
        {
            if (string.IsNullOrWhiteSpace(model.Memo))
            {
                model.Memo = string.Format(I18N.ReconciledByName, meta.Name);
            }

            using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
            {
                string sql = "SELECT * FROM finance.reconcile_account(@0::bigint, @1::integer, @2::date, @3::text);";

                if (db.DatabaseType == DatabaseType.SqlServer)
                {
                    sql = "EXECUTE finance.reconcile_account @0, @1, @2, @3;";
                }

                await db.NonQueryAsync(sql, model.TransactionDetailId, meta.UserId, model.NewBookDate, model.Memo).ConfigureAwait(false);
            }
        }
示例#12
0
        public async Task <bool> HasDbAsync(string tenant, string database)
        {
            const string sql = "SELECT COUNT(*) FROM master.dbo.sysdatabases WHERE name=@0;";

            string connectionString = FrapidDbServer.GetSuperUserConnectionString(tenant, database);

            using (var db = DbProvider.Get(connectionString, tenant).GetDatabase())
            {
                int awaiter = await db.ExecuteScalarAsync <int>
                              (
                    sql,
                    new object[]
                {
                    tenant
                }).ConfigureAwait(false);

                return(awaiter.Equals(1));
            }
        }
示例#13
0
        public async Task RunSqlAsync(string tenant, string database, string fromFile)
        {
            fromFile = fromFile.Replace("{DbServer}", "PostgreSQL");
            if (string.IsNullOrWhiteSpace(fromFile) ||
                File.Exists(fromFile).Equals(false))
            {
                return;
            }

            string sql = File.ReadAllText(fromFile, Encoding.UTF8);

            //PetaPoco/NPoco Escape
            //ORM: Remove this behavior if you change the ORM.
            //sql = sql.Replace("@", "@@");


            string connectionString = FrapidDbServer.GetSuperUserConnectionString(tenant, database);
            await Factory.ExecuteAsync(connectionString, tenant, database, sql).ConfigureAwait(true);
        }
示例#14
0
        public async Task CleanupDbAsync(string tenant, string database)
        {
            string sql = @"DECLARE @sql nvarchar(MAX);
                            DECLARE @queries TABLE(id int identity, query nvarchar(500), done bit DEFAULT(0));
                            DECLARE @id int;
                            DECLARE @query nvarchar(500);


                            INSERT INTO @queries(query)
                            SELECT 
	                            'EXECUTE dbo.drop_schema ''' + sys.schemas.name + ''''+ CHAR(13) AS query
                            FROM sys.schemas
                            WHERE principal_id = 1
                            AND name != 'dbo'
                            ORDER BY schema_id;



                            WHILE(SELECT COUNT(*) FROM @queries WHERE done = 0) > 0
                            BEGIN
                                SELECT TOP 1 
		                            @id = id,
		                            @query = query
	                            FROM @queries 
	                            WHERE done = 0
	
	                            EXECUTE(@query);

                                UPDATE @queries SET done = 1 WHERE id=@id;
                            END;";

            string connectionString = FrapidDbServer.GetSuperUserConnectionString(tenant, database);

            using (var connection = new SqlConnection(connectionString))
            {
                using (var command = new SqlCommand(sql, connection))
                {
                    connection.Open();
                    await command.ExecuteNonQueryAsync().ConfigureAwait(false);
                }
            }
        }
示例#15
0
        public static DataTable GetDataTable(string tenant, string sql, ParameterInfo parameters)
        {
            /**************************************************************************************
             * A Frapid report is a developer-only feature.
             * But, that does not guarantee that there will be no misuse.
             * So, the possible risk factor cannot be ignored altogether in this context.
             * Therefore, a review for defense against possible
             * SQL Injection Attacks is absolutely required here.
             *
             * Please do note that you should connect to Database Server using a login "report_user"
             * which has a read-only access for executing the SQL statements to produce the report.
             *
             * The SQL query is expected to have only the SELECT statement, but there is no
             * absolute and perfect way to parse and determine that the query contained
             * in the report is actually a "SELECT-only" statement.
             *
             * Moreover, the prospective damage could occur due to somebody messing up
             * with the permission of the database user "report_user" which is restricted by default
             * with a read-only access.
             *
             * This could happen on the DB server, where we cannot "believe"
             * that the permissions are perfectly intact.
             *
             * TODO: Investigate more on how this could be done better.
             ***************************************************************************************/

            if (string.IsNullOrWhiteSpace(sql))
            {
                return(null);
            }
            //A separate connection to database using a restricted login is established here.
            string connectionString = FrapidDbServer.GetReportUserConnectionString(tenant, tenant);
            var    site             = TenantConvention.GetSite(tenant);
            string providerName     = site.DbProvider;

            if (providerName == "Npgsql")
            {
                return(GetPostgresDataTable(connectionString, sql, parameters));
            }

            return(GetSqlServerDataTable(connectionString, sql, parameters));
        }
示例#16
0
        public void RunSql(string database, string fromFile)
        {
            fromFile = fromFile.Replace("{DbServer}", "PostgreSQL");
            if (string.IsNullOrWhiteSpace(fromFile) || File.Exists(fromFile).Equals(false))
            {
                return;
            }

            string sql = File.ReadAllText(fromFile, Encoding.UTF8);

            //PetaPoco/NPoco Escape
            //ORM: Remove this behavior if you change the ORM.
            sql = sql.Replace("@", "@@");

            Log.Verbose($"Running SQL {sql}");

            string connectionString = FrapidDbServer.GetSuperUserConnectionString(database);

            Factory.Execute(connectionString, sql);
        }
示例#17
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());
            }
        }
示例#18
0
        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));
        }
示例#19
0
        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));
        }
示例#20
0
        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(Resources.AccessIsDenied);
                }
            }

            long offset = (pageNumber - 1) * Config.GetPageSize(this.Database);

            //"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"), 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);
            }
        }
示例#21
0
        public async Task <long> PostAsync(string tenant, Purchase model)
        {
            string connectionString = FrapidDbServer.GetConnectionString(tenant);

            string sql = @"SELECT * FROM purchase.post_purchase
                            (
                                @OfficeId, @UserId, @LoginId, @ValueDate::date, @BookDate::date, 
                                @CostCenterId, @ReferenceNumber, @StatementReference, 
                                @SupplierId, @PriceTypeId, @ShipperId, @StoreId, ARRAY[{0}], @InvoiceDiscount
                            );";

            sql = string.Format(sql, this.GetParametersForDetails(model.Details));

            using (var connection = new NpgsqlConnection(connectionString))
            {
                using (var command = new NpgsqlCommand(sql, connection))
                {
                    command.Parameters.AddWithNullableValue("@OfficeId", model.OfficeId);
                    command.Parameters.AddWithNullableValue("@UserId", model.UserId);
                    command.Parameters.AddWithNullableValue("@LoginId", model.LoginId);
                    command.Parameters.AddWithNullableValue("@ValueDate", model.ValueDate);
                    command.Parameters.AddWithNullableValue("@BookDate", model.BookDate);
                    command.Parameters.AddWithNullableValue("@CostCenterId", model.CostCenterId);
                    command.Parameters.AddWithNullableValue("@ReferenceNumber", model.ReferenceNumber.Or(""));
                    command.Parameters.AddWithNullableValue("@StatementReference", model.StatementReference.Or(""));
                    command.Parameters.AddWithNullableValue("@SupplierId", model.SupplierId);
                    command.Parameters.AddWithNullableValue("@PriceTypeId", model.PriceTypeId);
                    command.Parameters.AddWithNullableValue("@ShipperId", model.ShipperId);
                    command.Parameters.AddWithNullableValue("@StoreId", model.StoreId);

                    command.Parameters.AddRange(this.AddParametersForDetails(model.Details).ToArray());

                    command.Parameters.AddWithNullableValue("@InvoiceDiscount", model.Discount);

                    connection.Open();
                    var awaiter = await command.ExecuteScalarAsync().ConfigureAwait(false);

                    return(awaiter.To <long>());
                }
            }
        }
示例#22
0
        public static async Task CreateUserAsync(string tenant, int userId, UserInfo model)
        {
            using (var db = DbProvider.Get(FrapidDbServer.GetSuperUserConnectionString(tenant), tenant).GetDatabase())
            {
                string encryptedPassword = EncryptPassword(model.Password);

                var user = new User
                {
                    Email       = model.Email,
                    Password    = encryptedPassword,
                    OfficeId    = model.OfficeId,
                    RoleId      = model.RoleId,
                    Name        = model.Name,
                    Phone       = model.Phone,
                    AuditTs     = DateTimeOffset.UtcNow,
                    AuditUserId = userId
                };

                await db.InsertAsync("account.users", "user_id", true, user).ConfigureAwait(false);
            }
        }
示例#23
0
        public static async Task <Dictionary <string, string> > GetLocalizedResourcesAsync(string tenant)
        {
            const string sql = "SELECT * FROM i18n.localized_resource_view;";

            using (var db = DbProvider.Get(FrapidDbServer.GetMetaConnectionString(tenant), tenant).GetDatabase())
            {
                var dbResources = await db.SelectAsync <dynamic>(sql).ConfigureAwait(false);

                var resources = new Dictionary <string, string>();

                foreach (var resource in dbResources)
                {
                    string key   = resource.Key;
                    string value = resource.Value;

                    resources.Add(key, value);
                }

                return(resources);
            }
        }
示例#24
0
        public async Task RunSqlAsync(string tenant, string database, string fromFile)
        {
            fromFile = fromFile.Replace("{DbServer}", "SQL Server");
            if (string.IsNullOrWhiteSpace(fromFile) || File.Exists(fromFile).Equals(false))
            {
                return;
            }

            string sql = File.ReadAllText(fromFile, Encoding.UTF8);


            InstallerLog.Verbose($"Running file {fromFile}");

            string connectionString = FrapidDbServer.GetSuperUserConnectionString(tenant, database);

            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                await this.RunScriptAsync(connection, sql).ConfigureAwait(false);
            }
        }
示例#25
0
        public async Task <Dictionary <string, string> > GetResourcesAsync(string tenant, Installable app, string path)
        {
            var resources = new Dictionary <string, string>();

            using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
            {
                var sql = new Sql("SELECT i18n_key, name FROM core.apps");
                sql.Where("app_name=@0", app.ApplicationName);

                var columns = await db.SelectAsync <dynamic>(sql).ConfigureAwait(false);

                foreach (var column in columns)
                {
                    string key   = column.I18nKey;
                    string value = column.Name;

                    resources.Add(key, value);
                }
            }
            return(resources);
        }
示例#26
0
        public static Dictionary <string, string> GetLocalizedResources()
        {
            const string sql = "SELECT * FROM i18n.localized_resource_view;";

            using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(MetaDatabase)).GetDatabase())
            {
                var dbResources = db.Query <dynamic>(sql);

                var resources = new Dictionary <string, string>();

                foreach (var resource in dbResources)
                {
                    string key   = resource.Key;
                    string value = resource.Value;

                    resources.Add(key, value);
                }

                return(resources);
            }
        }
示例#27
0
        public static async Task SavePolicyAsync(string tenant, int officeId, int userId, 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.entity_access_policy");
                    sql.Append("WHERE office_id = @0", officeId);
                    sql.Append("AND user_id = @0", userId);

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


                    foreach (var policy in policies)
                    {
                        var poco = new EntityAccessPolicy
                        {
                            EntityName   = policy.EntityName,
                            OfficeId     = officeId,
                            UserId       = userId,
                            AccessTypeId = policy.AccessTypeId,
                            AllowAccess  = policy.AllowAccess
                        };


                        await db.InsertAsync("auth.entity_access_policy", "entity_access_policy_id", true, poco).ConfigureAwait(false);
                    }

                    db.CommitTransaction();
                }
                catch
                {
                    db.RollbackTransaction();
                    throw;
                }
            }
        }
示例#28
0
        public async Task <IEnumerable <CustomField> > GetCustomFieldsAsync(string resourceId)
        {
            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 get custom fields for entity \"{this.FullyQualifiedObjectName}\" was denied to the user with Login ID {this.LoginId}");
                    throw new UnauthorizedException(Resources.AccessIsDenied);
                }
            }

            string sql;

            if (string.IsNullOrWhiteSpace(resourceId))
            {
                sql = $"SELECT * FROM config.custom_field_definition_view WHERE table_name='{this.FullyQualifiedObjectName}' ORDER BY field_order;";
                return(await Factory.GetAsync <CustomField>(this.Database, sql).ConfigureAwait(false));
            }

            sql = FrapidDbServer.GetProcedureCommand
                  (
                this.Database,
                "config.get_custom_field_definition",
                new[]
            {
                "@0",
                "@1"
            });

            return(await Factory.GetAsync <CustomField>(this.Database, sql, this.FullyQualifiedObjectName, resourceId).ConfigureAwait(false));
        }
示例#29
0
        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(Resources.AccessIsDenied);
                }
            }

            //$"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.And($"{this.PrimaryKey} > @0", primaryKey);
            sql.OrderBy(this.PrimaryKey);
            sql.Append(FrapidDbServer.AddOffset(this.Database, "@0"), 0);
            sql.Append(FrapidDbServer.AddLimit(this.Database, "@0"), 1);

            try
            {
                return((await Factory.GetAsync <dynamic>(this.Database, sql).ConfigureAwait(false)).FirstOrDefault());
            }
            catch (DbException ex)
            {
                Log.Error(ex.Message);
                throw new DataAccessException(this.Database, ex.Message, ex);
            }
        }
示例#30
0
        public void RunSql(string database, string fromFile)
        {
            fromFile = fromFile.Replace("{DbServer}", "SQL Server");
            if (string.IsNullOrWhiteSpace(fromFile) || File.Exists(fromFile).Equals(false))
            {
                return;
            }

            string sql = File.ReadAllText(fromFile, Encoding.UTF8);


            Log.Verbose($"Running SQL {sql}");

            string connectionString = FrapidDbServer.GetSuperUserConnectionString(database);

            using (var sqlConnection = new SqlConnection(connectionString))
            {
                var svrConnection = new ServerConnection(sqlConnection);
                var server        = new Server(svrConnection);
                server.ConnectionContext.ExecuteNonQuery(sql);
            }
        }