public async static Task Update(FinancialManagementSystem.Models.Profile obj) { using (var db = DbAccess.ConnectionFactory()) { await db.ExecuteAsync(DbAccess.Update <FinancialManagementSystem.Models.Profile>(), obj); } }
public async static Task Update(m.AppAdministrator obj) { using (var db = DbAccess.ConnectionFactory()) { await db.ExecuteAsync(DbAccess.Update <m.AppAdministrator>(), obj); } }
public async static Task <IEnumerable <m.Account> > Get() { using (var db = DbAccess.ConnectionFactory()) { return(await db.QueryAsync <m.Account>(DbAccess.SelectAll <m.Account>())); } }
public async static Task <IEnumerable <FinancialManagementSystem.Models.Profile> > Get() { using (var db = DbAccess.ConnectionFactory()) { return(await db.QueryAsync <FinancialManagementSystem.Models.Profile>(DbAccess.SelectAll <FinancialManagementSystem.Models.Profile>())); } }
public async static Task Insert(m.Account obj) { using (var db = DbAccess.ConnectionFactory()) { await db.ExecuteAsync(DbAccess.Insert <m.Account>(), obj); } }
public async static Task Update(m.Transaction obj) { using (var db = DbAccess.ConnectionFactory()) { await db.ExecuteAsync(DbAccess.Update <m.Transaction>(), obj); } }
public async static Task Insert(FinancialManagementSystem.Models.Account obj) { using (var db = DbAccess.ConnectionFactory()) { await db.ExecuteAsync(DbAccess.Insert <Account>(), obj); } }
public async static Task Update(Category obj) { using (var db = DbAccess.ConnectionFactory()) { await db.ExecuteAsync(DbAccess.Update <Category>(), obj); } }
public async static Task Delete(Guid id) { using (var db = DbAccess.ConnectionFactory()) { await db.ExecuteAsync(DbAccess.Delete <m.AppAdministrator>(), new { AppAdministratorId = id }); } }
public async static Task <FinancialManagementSystem.Models.Profile> Get(Guid id) { using (var db = DbAccess.ConnectionFactory()) { return(await db.QueryFirstOrDefaultAsync <FinancialManagementSystem.Models.Profile>(DbAccess.Select <FinancialManagementSystem.Models.Profile>(), new { ProfileId = id })); } }
public async static Task Delete(Guid id) { using (var db = DbAccess.ConnectionFactory()) { await db.ExecuteAsync(DbAccess.Delete <FinancialManagementSystem.Models.Profile>(), new { ProfileId = id }); } }
public async static Task Delete(Guid id) { using (var db = DbAccess.ConnectionFactory()) { await db.ExecuteAsync(DbAccess.Delete <m.Transaction>(), new { TransactionId = id }); } }
public async static Task <IEnumerable <m.AccountWithBalance> > GetAccountWithBalance(Guid ProfileId) { using (var db = DbAccess.ConnectionFactory()) { return(await db.QueryAsync <m.AccountWithBalance>("SELECT AccountId, AccountName, AccountType, ProfileId, dbo.GetAccountBalance(AccountId) AS Balance FROM dbo.Account WHERE (ProfileId = @ProfileId)", new { ProfileId = ProfileId })); } }
public async static Task <m.Account> Get(Guid id) { using (var db = DbAccess.ConnectionFactory()) { return(await db.QueryFirstOrDefaultAsync <m.Account>(DbAccess.Select <m.Account>(), new { AccountId = id })); } }
public async static Task <m.Profile> Login(m.Profile p) { using (var db = DbAccess.ConnectionFactory()) { string query = DbAccess.SelectAll <m.Profile>() + " WHERE [LoginId]=@LoginId AND [Pwd]=@Pwd"; m.Profile profile = await db.QueryFirstOrDefaultAsync <m.Profile>(query, new { LoginId = p.LoginId, Pwd = p.Pwd }); return(profile); } }
public async static Task <decimal> GetBalance(Guid id) { decimal balance = 0; using (var db = DbAccess.ConnectionFactory()) { await db.QueryAsync("SELECT dbo.GetAccountBalance(@AccountId)", new { AccountId = id }); } return(balance); }
public async static Task <m.Transaction> Get(Guid id) { using (var db = DbAccess.ConnectionFactory()) { m.Transaction transaction = await db.QueryFirstOrDefaultAsync <m.Transaction>(DbAccess.Select <m.Transaction>(), new { TransactionId = id }); transaction.Category = await Category.Get(transaction.CategoryId); transaction.Account = await Account.Get(transaction.AccountId); return(transaction); } }
public async static Task <IEnumerable <m.CategoryTypeTotal> > GetTotalCategoryTypeByProfileId(Guid ProfileId, DateTime _start, DateTime _end) { using (var db = DbAccess.ConnectionFactory()) { IEnumerable <m.CategoryTypeTotal> categoryTotals = await db.QueryAsync <m.CategoryTypeTotal>(@" SELECT SUM(dt.Amount) AS Total, dbo.Category.CategoryType FROM dbo.DailyTransaction AS dt INNER JOIN dbo.Category ON dt.CategoryId = dbo.Category.CategoryId WHERE (dt.AccountId IN (SELECT AccountId FROM dbo.Account WHERE (ProfileId = @ProfileId))) AND (dt.TransactionDate BETWEEN @s AND @e) GROUP BY dbo.Category.CategoryType", new { s = _start, e = _end, ProfileId = ProfileId }); return(categoryTotals); } }
public async static Task <IEnumerable <m.Transaction> > Get() { IEnumerable <m.Category> categories = await Category.Get(); IEnumerable <m.Account> accounts = await Account.Get(); using (var db = DbAccess.ConnectionFactory()) { IEnumerable <m.Transaction> transactions = await db.QueryAsync <m.Transaction>(DbAccess.SelectAll <m.Transaction>()); foreach (var t in transactions) { t.Category = categories.FirstOrDefault(c => c.CategoryId == t.CategoryId); t.Account = accounts.FirstOrDefault(a => a.AccountId == t.AccountId); } return(transactions); } }
public async static Task <IEnumerable <m.DailyTransaction> > GetByProfileId(Guid ProfileId) { IEnumerable <m.Category> categories = await Category.Get(); IEnumerable <m.Account> accounts = await Account.Get(); using (var db = DbAccess.ConnectionFactory()) { IEnumerable <m.DailyTransaction> transactions = await db.QueryAsync <m.DailyTransaction>(DbAccess.SelectAll <m.DailyTransaction>() + " WHERE AccountId IN (SELECT AccountId FROM Account WHERE ProfileId=@ProfileId) ORDER BY TransactionDate", new { ProfileId = ProfileId }); foreach (var t in transactions) { t.Category = categories.FirstOrDefault(c => c.CategoryId == t.CategoryId); t.Account = accounts.FirstOrDefault(a => a.AccountId == t.AccountId); } return(transactions); } }
public async static Task <IEnumerable <m.DailyTransaction> > GetCashBook(m.CashBookFilter filter) { IEnumerable <m.Category> categories = await Category.Get(); List <m.DailyTransaction> dailyTransactions = new List <m.DailyTransaction>(); using (var db = DbAccess.ConnectionFactory()) { decimal?openingBalance = await db.QueryFirstOrDefaultAsync <decimal?>("SELECT dbo.GetAccountBalanceByDate(@AccountId, @Date)", new { AccountId = filter.AccountId, Date = filter.StartDate }); //fetch opening balance dailyTransactions.Add(new m.DailyTransaction() { Particular = "Opening balance", Amount = openingBalance == null ? 0 : (decimal)openingBalance }); //get transactions dailyTransactions.AddRange((await db.QueryAsync <m.DailyTransaction>(DbAccess.SelectAll <m.DailyTransaction>() + " WHERE (AccountId = @AccountId) AND (TransactionDate BETWEEN @StartDate AND @EndDate) ORDER BY TransactionDate ", filter)).ToArray()); foreach (var t in dailyTransactions) { t.Category = categories.FirstOrDefault(c => c.CategoryId == t.CategoryId); if (t.Category?.CategoryType == "Expense") { t.Amount = 0 - t.Amount; } } //calculate closing balance decimal closingBalance = dailyTransactions.Sum(t => t.Amount); dailyTransactions.Add(new m.DailyTransaction() { Particular = "Closing balance", Amount = closingBalance }); return(dailyTransactions); } }