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