public async Task <List <Bill> > GetBillsDateRangeForAccount(DateTime start, DateTime end, string selectedAccount) { var agendaBills = new List <Bill>(); var acctId = await GetBankAccountID(selectedAccount); await Task.Run(() => { try { using (var conn = new ShortConnection(connectionString)) { var query = $@"SELECT * FROM Bill WHERE AccountID = @AccountId AND Date BETWEEN @Start AND @End"; var cmd = conn.CreateCommand(query); cmd.Bind("@AccountId", acctId); cmd.Bind("@Start", start); cmd.Bind("@End", end); agendaBills = cmd.ExecuteQuery <Bill>(); } } catch (Exception ex) { Debug.WriteLine(ex.Message); } }); agendaBills = await AttachAccounts(agendaBills); return(agendaBills); }
public async Task <List <Bill> > GetBillsForPayee(string payee) { var bills = new List <Bill>(); await Task.Run(() => { try { using (var conn = new ShortConnection(connectionString)) { var query = $@"SELECT * FROM Bill WHERE Payee = @Payee"; var cmd = conn.CreateCommand(query); cmd.Bind("@Payee", payee); bills = cmd.ExecuteQuery <Bill>(); } } catch (Exception ex) { Debug.WriteLine(ex.Message); } }); bills = await AttachAccounts(bills); return(bills); }
public Task <Balance> GetLatestBalance(int id, DateTime date) { //var acctId = 0; var bals = new List <Balance>(); //await Task.Run(() => //{ try { using (var conn = new ShortConnection(connectionString)) { var query = $@"SELECT * FROM Balance WHERE AccountID = @AccountID ORDER BY Timestamp DESC LIMIT 1";// Nickname = @Nickname"; var cmd = conn.CreateCommand(query); cmd.Bind("@AccountID", id); bals = cmd.ExecuteQuery <Balance>(); //bals = conn.Query<Balance>(query); // = cmd.ExecuteQuery<BankAccount>(); //distinctBills = conn.Query<Bill>(@"SELECT DISTINCT Payee FROM Bill ORDER BY Payee"); } //(substr(Timestamp, 7, 4) || substr(Timestamp, 4, 2) || substr(Timestamp, 1, 2)) } catch (Exception ex) { Debug.WriteLine(ex.Message); } //}); return(Task.FromResult(bals.Single())); //return acctId; }
public async Task <int> GetBankAccountID(string name) { //var acctId = 0; var accts = new List <BankAccount>(); await Task.Run(() => { try { using (var conn = new ShortConnection(connectionString)) { var query = $@"SELECT * FROM BankAccount WHERE Nickname = @Nickname"; var cmd = conn.CreateCommand(query); cmd.Bind("@Nickname", name); accts = cmd.ExecuteQuery <BankAccount>(); } } catch (Exception ex) { Debug.WriteLine(ex.Message); } }); return(accts.FirstOrDefault().AccountID); //return acctId; }
public async Task <List <Balance> > GetBalancesForAccount(int acctId) { var balances = new List <Balance>(); await Task.Run(() => { try { using (var conn = new ShortConnection(connectionString)) { var query = $@"SELECT * FROM Balance WHERE AccountId = @AccountId"; var cmd = conn.CreateCommand(query); cmd.Bind("@AccountId", acctId); balances = cmd.ExecuteQuery <Balance>(); } } catch (Exception ex) { Debug.WriteLine(ex.Message); } }); foreach (var item in balances) { item.AccountID = acctId; } //balances = await AttachAccounts(balances); return(balances); }
public async Task <List <Bill> > GetUnpaidAndFutureBills(DateTime start, DateTime end) { var agendaBills = new List <Bill>(); await Task.Run(() => { try { using (var conn = new ShortConnection(connectionString)) { var query = $@"SELECT * FROM Bill WHERE IsPaid = @IsPaid AND Date < @Start OR Date BETWEEN @Start AND @End"; var cmd = conn.CreateCommand(query); cmd.Bind("@IsPaid", false); cmd.Bind("@Start", start); cmd.Bind("@End", end); agendaBills = cmd.ExecuteQuery <Bill>(); } } catch (Exception ex) { Debug.WriteLine(ex.Message); } }); agendaBills = await AttachAccounts(agendaBills); return(agendaBills); }
public async Task Initialize() { //File.Delete(Constants.DatabasePath); using (var connection = new ShortConnection(connectionString)) { connection.CreateTable <BankAccount>(CreateFlags.None); connection.CreateTable <Bill>(CreateFlags.None); connection.CreateTable <Balance>(CreateFlags.None); } await UpdateBankAccountNames(); await UpdatePayeeNames(); }
public async Task <int> DeleteBillsForPayee(string payee) { var bills = await GetBillsForPayee(payee); await Task.Run(() => { using (var conn = new ShortConnection(connectionString)) { foreach (var item in bills) { conn.Delete(item); } } }); return(0); }
public async Task <List <BankAccount> > GetBankAccounts() { var accts = new List <BankAccount>(); await Task.Run(() => { try { using (var conn = new ShortConnection(connectionString)) { accts = conn.Query <BankAccount>(@"SELECT * FROM BankAccount"); } } catch (Exception ex) { Debug.WriteLine(ex.Message); } }); return(accts); }
public async Task <int> InsertBill(Bill bill) { var added = 0; await Task.Run(() => { try { using (var conn = new ShortConnection(connectionString)) { added = conn.Insert(bill); } } catch (Exception ex) { Debug.WriteLine(ex.Message); } }); return(added); }
public async Task <List <Bill> > GetBills() { var bills = new List <Bill>(); await Task.Run(() => { try { using (var conn = new ShortConnection(connectionString)) { bills = conn.Query <Bill>(@"SELECT * FROM Bill"); } } catch (Exception ex) { Debug.WriteLine(ex.Message); } }); return(bills); }
public async Task <int> DeleteBill(Bill bill) { var deleted = 0; await Task.Run(() => { try { using (var conn = new ShortConnection(connectionString)) { deleted = conn.Delete(bill); } } catch (Exception ex) { Debug.WriteLine(ex.Message); } }); return(deleted); }
public async Task <int> UpdateBalance(Balance balance) { var updated = 0; await Task.Run(() => { try { using (var conn = new ShortConnection(connectionString)) { updated = conn.Update(balance); } } catch (Exception ex) { Debug.WriteLine(ex.Message); } }); return(updated); }
public async Task <int> UpdateBills(IEnumerable <Bill> bills) { var updated = 0; await Task.Run(() => { try { using (var conn = new ShortConnection(connectionString)) { updated = conn.UpdateAll(bills); } } catch (Exception ex) { Debug.WriteLine(ex.Message); } }); return(updated); }
public async Task <List <string> > GetBillPayees() { var distinctPayees = new List <string>(); var distinctBills = new List <Bill>(); await Task.Run(() => { try { using (var conn = new ShortConnection(connectionString)) { distinctBills = conn.Query <Bill>(@"SELECT DISTINCT Payee FROM Bill ORDER BY Payee"); } var tmp = distinctBills.Select(x => x.Payee); distinctPayees = tmp.ToList(); } catch (Exception ex) { Debug.WriteLine(ex.Message); } }); return(distinctPayees); }
public async Task <Bill> GetBill(int id) { Bill b = await Task.Run(() => { try { using (var conn = new ShortConnection(connectionString)) { var query = $@"SELECT * FROM Bill WHERE ID = @ID"; var cmd = conn.CreateCommand(query); cmd.Bind("@ID", id); return(cmd.ExecuteQuery <Bill>().Single()); } } catch (Exception ex) { Debug.WriteLine(ex.Message); return(null); } }); return(b); }
public async Task <BankAccount> GetBankAccount(int id) { BankAccount ba = await Task.Run(() => { try { using (var conn = new ShortConnection(connectionString)) { var query = $@"SELECT * FROM BankAccount WHERE AccountID = @AccountId"; var cmd = conn.CreateCommand(query); cmd.Bind("@AccountId", id); return(cmd.ExecuteQuery <BankAccount>().Single()); } } catch (Exception ex) { Debug.WriteLine(ex.Message); return(null); } }); return(ba); }