public static async Task <IEnumerable <dynamic> > GetSearchViewAsync(string tenant, int officeId, OrderSearch search)
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
{
var sql = new Sql("SELECT * FROM sales.order_search_view");
sql.Where("value_date BETWEEN @0 AND @1", search.From, search.To);
sql.And("expected_date BETWEEN @0 AND @1", search.ExpectedFrom, search.ExpectedTo);
sql.And("LOWER(order_id) LIKE @0", search.Id.ToSqlLikeExpression().ToLower());
sql.And("LOWER(reference_number) LIKE @0", search.ReferenceNumber.ToSqlLikeExpression().ToLower());
sql.And("LOWER(customer) LIKE @0", search.Customer.ToSqlLikeExpression().ToLower());
sql.And("LOWER(terms) LIKE @0", search.Terms.ToSqlLikeExpression().ToLower());
sql.And("LOWER(memo) LIKE @0", search.Memo.ToSqlLikeExpression().ToLower());
sql.And("LOWER(posted_by) LIKE @0", search.PostedBy.ToSqlLikeExpression().ToLower());
sql.And("LOWER(office) LIKE @0", search.Office.ToSqlLikeExpression().ToLower());
if (search.Amount > 0)
{
sql.And("total_amount = @0", search.Amount);
}
sql.And("office_id IN(SELECT * FROM core.get_office_ids(@0))", officeId);
return(await db.SelectAsync <dynamic>(sql).ConfigureAwait(false));
}
}
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);
sql.And("deleted=@0", false);
return(await db.SelectAsync <MenuAccessPolicy>(sql).ConfigureAwait(false));
}
}
public static async Task <IEnumerable <Account> > GetNonConfidentialAsync(string tenant)
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
{
var sql = new Sql("SELECT * FROM finance.accounts");
sql.Where("deleted = @0", false);
sql.And("confidential = @0", false);
sql.And("is_transaction_node = @0", true);
return(await db.SelectAsync <Account>(sql).ConfigureAwait(false));
}
}
public static async Task <IEnumerable <SalesView> > GetCashSalesViewAsync(string tenant, int userId, DateTime transacitonDate)
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
{
var sql = new Sql("SELECT * FROM sales.sales_view");
sql.Where("tender > 0");
sql.And("verification_status_id > 0");
sql.And("value_date=@0", transacitonDate.Date);
return(await db.SelectAsync <SalesView>(sql).ConfigureAwait(false));
}
}
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 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 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);
sql.And("deleted=@0", false);
var awaiter = await db.SelectAsync <GroupMenuAccessPolicy>(sql).ConfigureAwait(false);
return(awaiter.Select(x => x.MenuId).ToArray());
}
}
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("deleted=@0", false);
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 WithdrawAsync(string tenant, string reason, int userId, long tranId, int officeId)
{
var sql = new Sql("UPDATE finance.transaction_master");
sql.Append("SET");
sql.Append("verification_status_id = -1,");
sql.Append("verified_by_user_id = @0,", userId);
sql.Append("verification_reason = @0,", reason);
sql.Append("last_verified_on = @0", DateTimeOffset.UtcNow);
sql.Where("transaction_master_id = @0", tranId);
sql.And("office_id = @0", officeId);
sql.And("user_id = @0", userId); //Only you can withdraw your transaction
sql.And("verification_status_id IN(0, 1)"); //Only unverified or automatically verified transacitons can be withdrawn.
await Factory.NonQueryAsync(tenant, sql).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);
return((await Factory.GetAsync <dynamic>(this.Database, sql).ConfigureAwait(false)).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 <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 <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);
sql.And("deleted=@0", false);
return(await db.SelectAsync <Content>(sql).ConfigureAwait(false));
}
}
public static async Task <IEnumerable <Item> > GetNonStockableItemsAsync(string tenant)
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
{
var sql = new Sql("SELECT * FROM inventory.items");
sql.Where("deleted=@0", false);
sql.And("maintain_inventory=@0", false);
return(await db.SelectAsync <Item>(sql).ConfigureAwait(false));
}
}
public static async Task <IEnumerable <PublishedContentView> > GetBlogContentsAsync(string tenant, 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.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 <IEnumerable <GroupEntityAccessPolicy> > 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_entity_access_policy");
sql.Where("office_id=@0", officeId);
sql.And("role_id=@0", roleId);
return(await db.SelectAsync <GroupEntityAccessPolicy>(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));
}
}
public static async Task <TransactionVerificationStatusView> GetVerificationStatusAsync(string tenant, long transactinMasterId, int officeId)
{
var sql = new Sql("SELECT * FROM finance.transaction_verification_status_view");
sql.Where("transaction_master_id=@0", transactinMasterId);
sql.And("office_id=@0", officeId);
var awaiter = await Factory.GetAsync <TransactionVerificationStatusView>(tenant, sql).ConfigureAwait(false);
return(awaiter.FirstOrDefault());
}
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 <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);
sql.And("deleted=@0", false);
return(await db.SelectAsync <User>(sql).ConfigureAwait(false));
}
}
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);
sql.And("deleted=@0", false);
var awaiter = await db.SelectAsync <ConfigurationProfile>(sql).ConfigureAwait(false);
return(awaiter.FirstOrDefault());
}
}
public static async Task <User> GetAsync(string tenant, int userId)
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
{
var sql = new Sql("SELECT * FROM account.users");
sql.Where("user_id=@0", userId);
sql.And("deleted=@0", false);
var awaiter = await db.SelectAsync <User>(sql).ConfigureAwait(false);
return(awaiter.FirstOrDefault());
}
}
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.And("deleted=@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));
}
}
public static async Task <Feed> GetFeedAsync(string tenant, long feedId)
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
{
db.CacheResults = false;
var sql = new Sql("SELECT * FROM social.feeds");
sql.Where("deleted = @0", false);
sql.And("feed_id=@0", feedId);
var awatier = await db.SelectAsync <Feed>(sql).ConfigureAwait(false);
return(awatier.FirstOrDefault());
}
}
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.And("deleted=@0", false);
sql.Limit(db.DatabaseType, 1, 0, "configuration_id");
var awaiter = await db.SelectAsync <DTO.Configuration>(sql).ConfigureAwait(false);
return(awaiter.FirstOrDefault());
}
}
public static async Task <List <GiftCardSearchView> > SearchAsync(string tenant, GiftCardSearch query)
{
var sql = new Sql("SELECT * FROM sales.gift_card_search_view");
sql.Where("UPPER(COALESCE(name, '')) LIKE @0", WrapSearchWildcard(query.Name).ToUpper());
sql.And("UPPER(COALESCE(address, '')) LIKE @0", WrapSearchWildcard(query.Address).ToUpper());
sql.And("UPPER(COALESCE(city, '')) LIKE @0", WrapSearchWildcard(query.City).ToUpper());
sql.And("UPPER(COALESCE(state, '')) LIKE @0", WrapSearchWildcard(query.State).ToUpper());
sql.And("UPPER(COALESCE(country, '')) LIKE @0", WrapSearchWildcard(query.Country).ToUpper());
sql.And("UPPER(COALESCE(po_box, '')) LIKE @0", WrapSearchWildcard(query.PoBox).ToUpper());
sql.And("UPPER(COALESCE(zip_code, '')) LIKE @0", WrapSearchWildcard(query.ZipCode).ToUpper());
sql.And("UPPER(COALESCE(phone_numbers, '')) LIKE @0", WrapSearchWildcard(query.Phone).ToUpper());
var awaiter = await Factory.GetAsync <GiftCardSearchView>(tenant, sql).ConfigureAwait(false);
return(awaiter.ToList());
}
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());
try
{
return(await db.SelectAsync <Filter>(sql).ConfigureAwait(false));
}
catch (DbException ex)
{
Log.Error(ex.Message);
throw new DataAccessException(this.Database, ex.Message, ex);
}
}
}
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 async Task <IEnumerable <dynamic> > GetSearchViewAsync(string tenant, int officeId, AdjustmentSearch search)
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
{
var sql = new Sql("SELECT * FROM inventory.adjustment_search_view");
sql.Where("value_date BETWEEN @0 AND @1", search.From, search.To);
sql.And("CAST(tran_id AS varchar(100)) LIKE @0", search.TranId.ToSqlLikeExpression().ToLower());
sql.And("LOWER(tran_code) LIKE @0", search.TranCode.ToSqlLikeExpression().ToLower());
sql.And("LOWER(COALESCE(reference_number, '')) LIKE @0", search.ReferenceNumber.ToSqlLikeExpression().ToLower());
sql.And("LOWER(COALESCE(statement_reference, '')) LIKE @0", search.StatementReference.ToSqlLikeExpression().ToLower());
sql.And("LOWER(posted_by) LIKE @0", search.PostedBy.ToSqlLikeExpression().ToLower());
sql.And("LOWER(office) LIKE @0", search.Office.ToSqlLikeExpression().ToLower());
sql.And("LOWER(COALESCE(status, '')) LIKE @0", search.Status.ToSqlLikeExpression().ToLower());
sql.And("LOWER(COALESCE(verified_by, '')) LIKE @0", search.VerifiedBy.ToSqlLikeExpression().ToLower());
sql.And("LOWER(COALESCE(reason, '')) LIKE @0", search.Reason.ToSqlLikeExpression().ToLower());
if (search.Amount > 0)
{
sql.And("amount = @0", search.Amount);
}
sql.And("office_id IN(SELECT * FROM core.get_office_ids(@0))", officeId);
return(await db.SelectAsync <dynamic>(sql).ConfigureAwait(false));
}
}