public static async Task <IEnumerable <MenuItemView> > GetMenuItemsAsync(string tenant, string menuName)
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
{
return(await db.Query <MenuItemView>().Where(c => c.MenuName == menuName).OrderBy(c => c.Sort).ToListAsync().ConfigureAwait(false));
}
}
public static User Get(string email)
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(AppUsers.GetTenant())).GetDatabase())
{
return(db.FetchBy <User>(sql => sql.Where(u => u.Email == email)).FirstOrDefault());
}
}
public static async Task <long> PostAsync(string tenant, Order model)
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
{
try
{
await db.BeginTransactionAsync().ConfigureAwait(false);
var awaiter = await db.InsertAsync("sales.orders", "order_id", true, model).ConfigureAwait(false);
long orderId = awaiter.To <long>();
foreach (var detail in model.Details)
{
detail.OrderId = orderId;
await db.InsertAsync("sales.order_details", "order_detail_id", true, detail).ConfigureAwait(false);
}
db.CommitTransaction();
return(orderId);
}
catch
{
db.RollbackTransaction();
throw;
}
}
}
public static void SavePolicy(int officeId, int userId, List <AccessPolicyInfo> policies)
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(AppUsers.GetTenant())).GetDatabase())
{
db.BeginTransaction();
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);
db.Execute(sql);
foreach (var policy in policies)
{
dynamic poco = new ExpandoObject();
poco.entity_name = policy.EntityName;
poco.office_id = officeId;
poco.user_id = userId;
poco.access_type_id = policy.AccessTypeId;
poco.allow_access = policy.AllowAccess;
db.Insert("auth.entity_access_policy", "entity_access_policy_id", true, poco);
}
db.CompleteTransaction();
}
}
public static async Task <IEnumerable <dynamic> > GetSellingPrices(string tenant, int officeId, int customerId)
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
{
var sql = new Sql(@"WITH price_list
AS
(
SELECT * FROM sales.customerwise_selling_prices
WHERE
(
sales.customerwise_selling_prices.customer_id IS NULL
OR sales.customerwise_selling_prices.customer_id = @0
)
)
SELECT
inventory.items.item_id,
inventory.items.item_code,
inventory.items.item_name,
inventory.items.unit_id,
inventory.get_unit_name_by_unit_id(inventory.items.unit_id) AS unit,
COALESCE(price_list.price, sales.get_item_selling_price(@1, inventory.items.item_id, NULL, NULL, inventory.items.unit_id)) AS price,
price_list.is_taxable
FROM inventory.items
LEFT JOIN price_list
ON price_list.item_id = inventory.items.item_id
WHERE inventory.items.allow_sales = @2
AND (price_list.customer_id IS NULL OR price_list.customer_id = @0);", customerId, officeId, true);
return(await db.SelectAsync <dynamic>(sql).ConfigureAwait(false));
}
}
public static IEnumerable <T> Get <T>(string database, string sql)
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(database)).GetDatabase())
{
return(db.Query <T>(sql));
}
}
public static T Scalar <T>(string database, Sql sql)
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(database)).GetDatabase())
{
return(db.ExecuteScalar <T>(sql));
}
}
public static IEnumerable <Content> GetContents()
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(AppUsers.GetTenant())).GetDatabase())
{
return(db.FetchBy <Content>(sql => sql.Where(c => c.IsHomepage)));
}
}
public static IEnumerable <PublishedContentView> GetAllPublishedContents()
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(AppUsers.GetTenant())).GetDatabase())
{
return(db.FetchBy <PublishedContentView>(sql => sql));
}
}
public static async Task <IEnumerable <EntityAccessPolicy> > GetPolicyAsync(string tenant, int officeId, int userId)
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
{
return(await db.Query <EntityAccessPolicy>().Where(x => x.OfficeId.Equals(officeId) && x.UserId.Equals(userId)).ToListAsync().ConfigureAwait(false));
}
}
public static async Task SavePolicyAsync(string tenant, int officeId, int userId, List <AccessPolicyInfo> policies)
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
{
db.BeginTransaction();
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.ExecuteAsync(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.CompleteTransaction();
}
}
public static async Task <long> AddAsync(string tenant, InventoryTransfer model)
{
string connectionString = FrapidDbServer.GetConnectionString(tenant);
string sql = @"SELECT * FROM inventory.post_transfer
(
@OfficeId, @UserId, @LoginId, @ValueDate, @BookDate,
@ReferenceNumber, @StatementReference,
ARRAY[{0}]
);";
sql = string.Format(sql, GetParametersForDetails(model.Details));
using (var connection = new NpgsqlConnection(connectionString))
{
using (var command = new NpgsqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@OfficeId", model.OfficeId);
command.Parameters.AddWithValue("@UserId", model.UserId);
command.Parameters.AddWithValue("@LoginId", model.LoginId);
command.Parameters.AddWithValue("@ValueDate", model.ValueDate);
command.Parameters.AddWithValue("@BookDate", model.BookDate);
command.Parameters.AddWithValue("@ReferenceNumber", model.ReferenceNumber);
command.Parameters.AddWithValue("@StatementReference", model.StatementReference);
command.Parameters.AddRange(AddParametersForDetails(model.Details).ToArray());
connection.Open();
var awaiter = await command.ExecuteScalarAsync().ConfigureAwait(false);
return(awaiter.To <long>());
}
}
}
public static async Task <IEnumerable <Office> > GetOfficesAsync(string tenant)
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
{
return(await db.Query <Office>().OrderBy(x => x.OfficeId).ToListAsync().ConfigureAwait(false));
}
}
public static SmtpConfig GetConfig(string database)
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(database)).GetDatabase())
{
return(db.FetchBy <SmtpConfig>(sql => sql.Where(u => u.Enabled && u.IsDefault)).FirstOrDefault());
}
}
public async Task <long> AddAsync(string tenant, InventoryAdjustment model)
{
string connectionString = FrapidDbServer.GetConnectionString(tenant);
string sql = @"SELECT * FROM inventory.post_adjustment
(
@OfficeId::integer, @UserId::integer, @LoginId::bigint, @StoreId::integer, @ValueDate::date, @BookDate::date,
@ReferenceNumber::national character varying(24), @StatementReference::text,
ARRAY[{0}]
);";
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("@StoreId", model.StoreId);
command.Parameters.AddWithNullableValue("@ValueDate", model.ValueDate);
command.Parameters.AddWithNullableValue("@BookDate", model.BookDate);
command.Parameters.AddWithNullableValue("@ReferenceNumber", model.ReferenceNumber);
command.Parameters.AddWithNullableValue("@StatementReference", model.StatementReference);
command.Parameters.AddRange(this.AddParametersForDetails(model.Details).ToArray());
connection.Open();
var awaiter = await command.ExecuteScalarAsync().ConfigureAwait(false);
return(awaiter.To <long>());
}
}
}
public static int CountBlogContents()
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(AppUsers.GetTenant())).GetDatabase())
{
return(db.FetchBy <PublishedContentView>(sql => sql.Where(x => x.IsBlog)).Count);
}
}
public static ConfigurationProfile GetActiveProfile()
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(AppUsers.GetTenant())).GetDatabase())
{
return(db.FetchBy <ConfigurationProfile>(sql => sql.Where(u => u.IsActive)).FirstOrDefault());
}
}
public static IEnumerable <PublishedContentView> GetBlogContents(int limit, int offset)
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(AppUsers.GetTenant())).GetDatabase())
{
return(db.FetchBy <PublishedContentView>(sql => sql.Where(x => x.IsBlog)).Skip(offset).Take(limit));
}
}
public static T Scalar <T>(string database, string sql, params object[] args)
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(database)).GetDatabase())
{
return(db.ExecuteScalar <T>(sql, args));
}
}
public static Contact GetContact(int contactId)
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(AppUsers.GetTenant())).GetDatabase())
{
return(db.FetchBy <Contact>(sql => sql.Where(c => c.ContactId.Equals(contactId))).FirstOrDefault());
}
}
public static void NonQuery(string database, string sql, params object[] args)
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(database)).GetDatabase())
{
db.Execute(sql, args);
}
}
public async Task <Dictionary <string, string> > GetResourcesAsync(string tenant, Installable app, string path)
{
var resources = new Dictionary <string, string>();
if (string.IsNullOrWhiteSpace(app.DbSchema))
{
return(resources);
}
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
{
var sql = new Sql("SELECT DISTINCT column_name FROM information_schema.columns");
sql.Where("table_schema=@0", app.DbSchema);
var columns = await db.SelectAsync <dynamic>(sql).ConfigureAwait(false);
foreach (var column in columns)
{
string name = column.ColumnName;
string key = name.ToPascalCase();
string value = name.ToSentence().ToTitleCaseSentence();
if (!resources.ContainsKey(key))
{
resources.Add(key, value);
}
}
}
return(resources);
}
public async Task <int> CreateAsync(string tenant, ItemVariantInfo variant)
{
string connectionString = FrapidDbServer.GetConnectionString(tenant);
string sql = "EXECUTE inventory.create_item_variant @VariantOf, @ItemId, @ItemCode, @ItemName, @Variants, @UserId, @VariantId OUTPUT";
using (var connection = new SqlConnection(connectionString))
{
using (var command = new SqlCommand(sql, connection))
{
command.Parameters.AddWithNullableValue("@VariantOf", variant.VariantOf);
command.Parameters.AddWithNullableValue("@ItemId", variant.ItemId);
command.Parameters.AddWithNullableValue("@ItemCode", variant.ItemCode);
command.Parameters.AddWithNullableValue("@ItemName", variant.ItemName);
command.Parameters.AddWithNullableValue("@Variants", "{" + string.Join(",", variant.Variants) + "}");
command.Parameters.AddWithNullableValue("@UserId", variant.UserId);
command.Parameters.Add("@VariantId", SqlDbType.Int).Direction = ParameterDirection.Output;
connection.Open();
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
return(command.Parameters["@VariantId"].Value.To <int>());
}
}
}
public static async Task <DTO.Configuration> GetDefaultConfigurationAsync(string tenant)
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
{
return(await db.Query <DTO.Configuration>().Where(c => c.IsDefault).FirstOrDefaultAsync().ConfigureAwait(false));
}
}
public static async Task SetPriceList(string tenant, int userId, int customerId, IEnumerable <CustomerwiseSellingPrice> pricelist)
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
{
try
{
await db.BeginTransactionAsync().ConfigureAwait(false);
var sql = new Sql("DELETE FROM sales.customerwise_selling_prices");
sql.Where("customer_id = @0", customerId);
await db.NonQueryAsync(sql).ConfigureAwait(false);
foreach (var price in pricelist)
{
price.CustomerId = customerId;
price.AuditUserId = userId;
price.AuditTs = DateTimeOffset.UtcNow;
price.Deleted = false;
await db.InsertAsync(price).ConfigureAwait(false);
}
db.CommitTransaction();
}
catch
{
db.RollbackTransaction();
throw;
}
}
}
public static DTO.Configuration GetDefaultConfiguration()
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(AppUsers.GetTenant())).GetDatabase())
{
return(db.FetchBy <DTO.Configuration>(sql => sql.Where(c => c.IsDefault)).FirstOrDefault());
}
}
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 <List <ItemView> > GetItemsAsync(string tenant)
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
{
return(await db.Query <ItemView>().ToListAsync().ConfigureAwait(false));
}
}
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));
}
}
public Task ListenNonQueryAsync(string tenant, SqlCommand command)
{
if (command == null)
{
return(null);
}
string connectionString = FrapidDbServer.GetConnectionString(tenant);
var task = new Task(delegate
{
try
{
using (var connection = new SqlConnection(connectionString))
{
command.Connection = connection;
connection.InfoMessage += this.Connection_Notice;
connection.Open();
command.ExecuteNonQuery();
}
}
catch (SqlException ex)
{
var e = new EodEventArgs(ex.Message, "error");
var notificationReceived = this.NotificationReceived;
notificationReceived?.Invoke(this, e);
}
});
return(task);
}
