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