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 <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 <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);
}
}
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 <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 <IEnumerable <m.DailyTransaction> > GetByProfileId(Guid ProfileId, DateTime _start, DateTime _end)
{
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 ([TransactionDate] BETWEEN @s AND @e) AND (AccountId IN (SELECT AccountId FROM Account WHERE ProfileId=@ProfileId)) ORDER BY TransactionDate",
new { s = _start, e = _end, 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);
}
}
