/// <summary>
/// Constructs SQL query format for expense in the form: '(cols) VALUES (vals)'. If the expense has a valid id that will also be included.
/// The command is constructed so that it is parameterized. Defending from SQLInjection attacks.
/// </summary>
/// <param name="prefix">The command to perform such as 'INSERT INTO tablename '.</param>
/// <param name="cmd">The command to populate with parameters and command text.</param>
/// <param name="expense">Expense object with populated fields.</param>
private static void PrepareColValueCommand(string prefix, SqliteCommand cmd, Expense expense)
{
string cols = string.Empty;
string vals = string.Empty;
if (expense.Id > -1)
{
cols += "(Id, Value, Date, Place, Tag, Notes, ";
vals += "VALUES (@id, @value, @date, @place, @tag, @notes, ";
cmd.Parameters.Add(new SqliteParameter("@id", expense.Id));
cmd.Parameters.Add(new SqliteParameter("@value", expense.Value));
cmd.Parameters.Add(new SqliteParameter("@date", expense.Date));
cmd.Parameters.Add(new SqliteParameter("@place", expense.Place));
cmd.Parameters.Add(new SqliteParameter("@tag", string.Join(":", expense.Tag)));
cmd.Parameters.Add(new SqliteParameter("@notes", expense.Notes));
}
else
{
cols += "(Value, Date, Place, Tag, Notes, ";
vals += "VALUES (@value, @date, @place, @tag, @notes, ";
cmd.Parameters.Add(new SqliteParameter("@value", expense.Value));
cmd.Parameters.Add(new SqliteParameter("@date", expense.Date));
cmd.Parameters.Add(new SqliteParameter("@place", expense.Place));
cmd.Parameters.Add(new SqliteParameter("@tag", string.Join(":", expense.Tag)));
cmd.Parameters.Add(new SqliteParameter("@notes", expense.Notes));
}
if (expense.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)expense.Method;
cols += "Type, Currency) ";
vals += "@type, @currency);";
cmd.Parameters.Add(new SqliteParameter("@type", (int)EpurchaseMethod.CASH));
cmd.Parameters.Add(new SqliteParameter("@currency", c.Currency));
}
else if (expense.Method is Card)
{
Card c = (Card)expense.Method;
cols += "Type, Debit, Provider, Number, Name) ";
vals += "@type, @debit, @provider, @number, @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 (expense.Method is DirectDeposit)
{
DirectDeposit d = (DirectDeposit)expense.Method;
cols += "Type, Savings, Bankname, Number, Name) ";
vals += "@type, @savings, @bankname, @number, @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));
}
cmd.CommandText = prefix + cols + vals;
cmd.Prepare();
}
/// <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();
}
/// <inheritdoc/>
public override bool Equals(object obj)
{
Cash c = (Cash)obj;
return(this.Currency == c.Currency);
}