/// <summary>
/// Finds and returns all expenses in the database that match the given filter. Sorted by Date in ascending order.
/// </summary>
/// <param name="filter">Filter to select expenses by.</param>
/// <returns>All expenses that match the filter, empty list if none are found.</returns>
public List <Expense> FindAll(ExpenseFilter filter)
{
List <Expense> expenses = new List <Expense>();
using (SqliteConnection db = new SqliteConnection(this.CreateConnectionString()))
{
db.Open();
using (SqliteCommand cmd = db.CreateCommand())
{
string cmdText = string.Format(
"SELECT * FROM {0} WHERE ",
DataAccessFactory.EXPENSE_TABLE_NAME);
DataAccessFactory.PrepareFilterConstraintCommand(cmdText, cmd, filter);
using (SqliteDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
Expense e = DataAccessFactory.FromRow(rdr);
expenses.Add(e);
}
}
}
}
return(expenses);
}
/// <summary>
/// Finds the first expense in the database that matches the filter. Sorted by Date in ascending order.
/// </summary>
/// <param name="filter">Filter to select expenses by.</param>
/// <returns>The first expense that matches the filter, otherwise null.</returns>
public Expense FindOne(ExpenseFilter filter)
{
using (SqliteConnection db = new SqliteConnection(this.CreateConnectionString()))
{
db.Open();
using (SqliteCommand cmd = db.CreateCommand())
{
string cmdText = string.Format(
"SELECT * FROM {0} WHERE ",
DataAccessFactory.EXPENSE_TABLE_NAME);
DataAccessFactory.PrepareFilterConstraintCommand(cmdText, cmd, filter);
Console.WriteLine(cmd.CommandText);
using (SqliteDataReader rdr = cmd.ExecuteReader())
{
if (rdr.Read())
{
return(DataAccessFactory.FromRow(rdr));
}
else
{
return(null);
}
}
}
}
}
/// <summary>
/// Checks that the two filters functionally are the same. That is they match the same set of expenses.
/// The name is left out during this comparison.
/// </summary>
/// <param name="f">Filter to compare against.</param>
/// <returns>True if the filters are functionaly equal. False if not.</returns>
public bool FunctionalEquals(ExpenseFilter f)
{
return((this.MinValue == f.MinValue) &&
(this.MaxValue == f.MaxValue) &&
(this.MinDate == f.MinDate) &&
(this.MaxDate == f.MaxDate) &&
this.Place.SetEquals(f.Place) &&
this.Tag.SetEquals(f.Tag) &&
this.Keywords.SetEquals(f.Keywords) &&
ExpenseFilter.MethodEqual(this.Method, f.Method));
}
/// <inheritdoc/>
public override bool Equals(object obj)
{
ExpenseFilter f = (ExpenseFilter)obj;
return((this.Name == f.Name) &&
(this.MinValue == f.MinValue) &&
(this.MaxValue == f.MaxValue) &&
(this.MinDate == f.MinDate) &&
(this.MaxDate == f.MaxDate) &&
this.Place.SetEquals(f.Place) &&
this.Tag.SetEquals(f.Tag) &&
this.Keywords.SetEquals(f.Keywords) &&
ExpenseFilter.MethodEqual(this.Method, f.Method));
}
/// <summary>
/// Initializes a new instance of the <see cref="ExpenseController"/> class.
/// Internal so that it may only be instantiated by backend code. Must be created
/// by the Authenticator.
/// </summary>
/// <param name="user">The user whose expenses the object will be interacting with.</param>
internal ExpenseController(User user)
{
// Setup members
this.CurrentUser = user;
this.ExpenseDataAccess = new DataAccessFactory(this.CurrentUser);
// only place we dont use property because we dont want to refresh table as we are jsut creating it.
this.activeFilter = null;
// If the expense table doesnt yet exist, create it before loading expenses.
if (!this.ExpenseDataAccess.ExpenseTableExists)
{
this.ExpenseDataAccess.CreateNewExpenseTable();
}
// Initially load all expenses into controller
this.currentExpenses = this.ExpenseDataAccess.GetAll();
}
/// <summary>
/// computes union of two filters and returns a new filter.
/// </summary>
/// <param name="f1">First Filter to intersect with.</param>
/// <param name="f2">Second Filter to intersect with.</param>
/// <param name="filterName">Name to be given to the new filter. Defaults to "f1.Name|f2.Name".</param>
/// <returns>ExpenseFilter containing the union.</returns>
public static ExpenseFilter Union(ExpenseFilter f1, ExpenseFilter f2, string filterName = "")
{
if (filterName == string.Empty)
{
filterName = f1.Name + '|' + f2.Name;
}
return(new ExpenseFilter(
name: filterName,
maxValue: new[] { f1.MaxValue, f2.MaxValue }.Max(),
minValue: new[] { f1.MinValue, f2.MinValue }.Min(),
maxDate: new[] { f1.MaxDate, f2.MaxDate }.Max(),
minDate: new[] { f1.MinDate, f2.MinDate }.Min(),
place: new HashSet <string>(f1.Place.Union(f2.Place)),
tag: new HashSet <string>(f1.Tag.Union(f2.Tag)),
keywords: new HashSet <string>(f1.Keywords.Union(f2.Keywords)),
method: ExpenseFilter.MethodEqual(f1.Method, f2.Method) ? f1.Method : null));
}
/// <summary>
/// Summarizes the ExpenseFilters set of expenses in SQL, that can be easily added to a where clause.
/// Sorts by date.
/// </summary>
private static void PrepareFilterConstraintCommand(string prefix, SqliteCommand cmd, ExpenseFilter filter)
{
// TODO: TEST THIS
string outstring = string.Empty;
// add date clause
outstring += "(Date BETWEEN @mindate AND @maxdate) AND ";
cmd.Parameters.Add(new SqliteParameter("@mindate", filter.MinDate));
cmd.Parameters.Add(new SqliteParameter("@maxdate", filter.MaxDate));
// handle value clause
outstring += "(Value BETWEEN @minvalue AND @maxvalue)";
cmd.Parameters.Add(new SqliteParameter("@minvalue", filter.MinValue));
cmd.Parameters.Add(new SqliteParameter("@maxvalue", filter.MaxValue));
// handle place clause
if (filter.Place.Count > 0)
{
int i = 0;
outstring += " AND (Place IN (";
foreach (var place in filter.Place)
{
outstring += "@place" + i;
outstring += (filter.Place.Count - 1 == i) ? string.Empty : ", ";
cmd.Parameters.Add(new SqliteParameter("@place" + i, place));
++i;
}
outstring += "))";
}
// handle tag clause
if (filter.Tag.Count > 0)
{
int i = 0;
outstring += " AND (";
foreach (var tag in filter.Tag)
{
outstring += "(Tag LIKE @tag" + i + ")";
outstring += (filter.Tag.Count - 1 == i) ? string.Empty : " OR ";
cmd.Parameters.Add(new SqliteParameter("@tag" + i, "%" + tag + "%"));
++i;
}
outstring += ")";
}
// handle keyword clause
if (filter.Keywords.Count > 0)
{
int i = 0;
outstring += " AND (";
foreach (var word in filter.Keywords)
{
outstring += "(Notes LIKE @word" + i + ")";
outstring += (filter.Keywords.Count - 1 == i) ? string.Empty : " OR ";
cmd.Parameters.Add(new SqliteParameter("@word" + i, "%" + word + "%"));
++i;
}
outstring += ")";
}
// prepare purchasemethod args
if (filter.Method != null)
{
if (filter.Method is Cash)
{
// This is an undesireable solution - ideally would like a way for derived classes to provide this information themselves,
// but we dont want that functionality available outside of the engine.
Cash c = (Cash)filter.Method;
outstring += " AND (Type = @type) AND (Currency = @currency)";
cmd.Parameters.Add(new SqliteParameter("@type", (int)EpurchaseMethod.CASH));
cmd.Parameters.Add(new SqliteParameter("@currency", c.Currency));
}
else if (filter.Method is Card)
{
Card c = (Card)filter.Method;
outstring += " AND (Type = @type) AND (Debit = @debit) AND (Provider = @provider) AND (Number = @number) AND (Name = @name)";
cmd.Parameters.Add(new SqliteParameter("@type", (int)EpurchaseMethod.CARD));
cmd.Parameters.Add(new SqliteParameter("@debit", c.Debit));
cmd.Parameters.Add(new SqliteParameter("@provider", c.Provider));
cmd.Parameters.Add(new SqliteParameter("@number", c.Number));
cmd.Parameters.Add(new SqliteParameter("@name", c.Name));
}
else if (filter.Method is DirectDeposit)
{
DirectDeposit d = (DirectDeposit)filter.Method;
outstring += " AND (Type = @type) AND (Savings = @savings) AND (Bankname = @bankname) AND (Number = @number) AND (Name = @name)";
cmd.Parameters.Add(new SqliteParameter("@type", (int)EpurchaseMethod.DIRECT_DEPOSIT));
cmd.Parameters.Add(new SqliteParameter("@savings", d.Savings));
cmd.Parameters.Add(new SqliteParameter("@bankname", d.BankName));
cmd.Parameters.Add(new SqliteParameter("@number", d.Number));
cmd.Parameters.Add(new SqliteParameter("@name", d.Name));
}
}
outstring += " ORDER BY Date ASC;";
cmd.CommandText = prefix + outstring;
cmd.Prepare();
}
/// <summary>
/// Inserts the given filter into the users filter table.
/// </summary>
/// <param name="filter">The filter object to be placed in the table.</param>
public void InsertFilter(ExpenseFilter filter)
{
throw new NotImplementedException();
}
/// <summary>
/// computes intersection of two filters and returns a new filter.
/// if the two filters produce a null intersection, null is returned.
/// </summary>
/// <param name="f1">First Filter to intersect with.</param>
/// <param name="f2">Second Filter to intersect with.</param>
/// <returns>ExpenseFilter containing the intersection.</returns>
public static ExpenseFilter Intersection(ExpenseFilter f1, ExpenseFilter f2)
{
// NOTE: Implementation is stretch goal
throw new NotImplementedException();
}
