Пример #1
0
 public async static Task Update(FinancialManagementSystem.Models.Profile obj)
 {
     using (var db = DbAccess.ConnectionFactory())
     {
         await db.ExecuteAsync(DbAccess.Update <FinancialManagementSystem.Models.Profile>(), obj);
     }
 }
Пример #2
0
 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>()));
     }
 }
Пример #4
0
 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);
     }
 }
Пример #7
0
 public async static Task Insert(FinancialManagementSystem.Models.Account obj)
 {
     using (var db = DbAccess.ConnectionFactory())
     {
         await db.ExecuteAsync(DbAccess.Insert <Account>(), obj);
     }
 }
Пример #8
0
 public async static Task Update(Category obj)
 {
     using (var db = DbAccess.ConnectionFactory())
     {
         await db.ExecuteAsync(DbAccess.Update <Category>(), obj);
     }
 }
Пример #9
0
 public async static Task Delete(Guid id)
 {
     using (var db = DbAccess.ConnectionFactory())
     {
         await db.ExecuteAsync(DbAccess.Delete <m.AppAdministrator>(),
                               new { AppAdministratorId = id });
     }
 }
Пример #10
0
 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 }));
     }
 }
Пример #11
0
 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);
            }
        }
Пример #18
0
        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);
            }
        }
Пример #20
0
        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);
            }
        }
Пример #21
0
        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);
            }
        }